DQL(Data Query Language 数据查询语言)

1. 基础查询

语法：

select 查询列表 from 表名；
  1. 查询列表：表的字段、常量、表达式、函数
  2. 查询结果是虚拟表格
#查询单字段
SELECT name FROM stu;
#查询多字段
SELECT id,name FROM stu;
#查询常量
SELECT 100;
SELECT 'json';
#查询别名,联表查询也可对表用别名
SELECT 100 AS “常量”;
#去重
SELECT DISTINCT class FROM stu;
#查询两字段拼接(+运算符)
SELECT CONCAT(last_name,first_name) AS "姓名" from stu;

2. 条件查询

语法：

select 查询列表 

    from 表名

    where 筛选条件；

分类：

  1. 条件表达式。条件运算符：

    • >
    • <
    • =
    • !=
    • <>
    • >=
    • <=

  2. 逻辑表达式(用于连接多个条件筛选)。逻辑运算符：

    • &&

    • ||

    • !

    • and

    • or

    • not

  3. 模糊查询：

    • like
    • between and (包含临界值)
    • in
    • is null
    • is not null
SELECT * FROM stu WHERE id > 100;
SELECT * FROM stu WHERE id <> 100;
SELECT * FROM stu WHERE id > 100 and id < 200;
SELECT * FROM stu WHERE name LIKE '%李%';# %通配符  _任意单个字符
SELECT * FROM stu WHERE name LIKE '\%李\%'';# 转义
SELECT * FROM stu WHERE name LIKE '李$_' ESCAPE '$';# 自定义转义符为$
SELECT * FROM stu WHERE name IN('zhangsan','lisi');
SELECT * FROM stu WHERE name IS NULL; # = <> 不能判断null值 <=>安全等于可以判断null

3. 排序查询

语法：

select 查询列表 

    from 表名

    where 筛选条件

    order by 排序列表【asc | desc】；

asc：升序（默认） desc：降序

SELECT * FROM stu WHERE id > 100 ORDER BY name DESC;
SELECT * FROM stu WHERE id > 100 ORDER BY name ASC;
SELECT * FROM stu WHERE id > 100 ORDER BY name;
#按别名排序
SELECT *, mouth_cost*12*(1+ IFNULL(other_cost,0)) AS cost FROM stu ORDER BY cost;
#按函数排序
SELECT * ,LENGTH(name) AS "长度" FROM stu ORDER BY LENGTH(name);
#多个字段排序
SELECT * FROM stu  ORDER BY name ASC,mouth_cost DESC;

4. 常见函数

  1. 单行函数

    • 字符函数

      • LENGTH：获取参数值字节个数

      • CONCAT：字符串拼接

      • UPPER：转大写字母

      • LOWER：转小写字母

      • SUBSTR/SUBSTRING：字符串截取，索引从1开始

      • INSTR：返回子串第一次出现索引，没有返回0

      • TRIM：去掉前后空格。也可指定去掉前后某参数 TRIM('a' FROM 'aaaaHELLOaaaa')

      • LPAD: 用指定的字符与长度左填充

      • RPAD: 用指定的字符与长度右填充

      • REPLACE: 替换

        SELECT LENGTH('hello world'); #11
SELECT LENGTH('你好');#6
SELECT CONCAT('hello',' ','woeld');#hello world
SELECT UPPER('hello world'); #HELLO WORLD
SELECT LOWER('HELLO WORLD'); #hello world
SELECT SUBSTR('hello world',6);#world
SELECT SUBSTRING('hello world',6);#world
SELECT SUBSTR('hello world',1,4);#hell
SELECT INSTR('hello world','wo');#7
SELECT INSTR('hello world','wao');#0
SELECT TRIM('    hello world     ');#hello world
SELECT TRIM('a' FROM 'aaaahello worldaaaaaaa');#hello world
SELECT LPAD('hello',10,'*');#*****hello
SELECT RPAD('hello',10,'*');#hello*****
SELECT REPLACE('aabbcc','aa','dd');#ddbbcc

  • 数学函数

    • ROUND：四舍五入

    • CEIL：向上取整

    • FLOOR：向下取整

    • TRUNCATE：截断

    • MOD：取余

      SELECT ROUND(1.2);#1
SELECT ROUND(1.5);#2
SELECT ROUND(1.2365,2);#1.24
SELECT CEIL(1.2);#2
SELECT FLOOR(1.5);#2
SELECT TRUNCATE(1.2399,2);#1.23
SELECT MOD(5,2);#1

  • 日期函数

    • NOW：返回当前系统日期+时间

    • CURDATE：返回当前系统日期

    • CURTIME：返回当前系统时间

    • YEAR MONTH DAY HOUR MINUTE SECOND ：年月日时分秒

    • STR_TO_DATE：字符串转日期

    • DATE_FORMAT： 日期转字符串

    • DATEDIFF：日期差

      SELECT NOW();#2021-08-18 14:31:41
SELECT CURDATE();#2021-08-18
SELECT CURTIME();#14:31:41
SELECT YEAR(NOW());#2021
SELECT MONTH(NOW());#8
SELECT MONTHNAME(NOW());#August
SELECT DAY(NOW());#18
SELECT HOUR(NOW());#14
SELECT MINUTE(NOW());#31
SELECT SECOND(NOW());#41
SELECT STR_TO_DATE('2021-1-2','%Y-%c-%d');#2021-01-02
SELECT DATE_FORMAT('2021-01-02','%Y年%c月%d日');#2021年1月02日
SELECT DATEDIFF('2020-2-3','2020-2-1');#2

  • 其他函数

    • VERSION：查询版本号

    • DATABASE：查询数据库

    • USER：查询用户

      SELECT VERSION();#5.6.31
SELECT DATABASE();#mysql
SELECT USER();#root@192.168.164.1

  • 流程控制函数

    • if函数

    • case函数

      SELECT IF(2 > 1,'2>1 is true','2>1 is false');#2>1 is true
/*
CASE 语法一
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
...
else 要显示的值n或语句n;
end

CASE 语法二
case 
when 条件1 then 要显示的值1或语句1;
when 条件2 then 要显示的值2或语句2;
...
else 要显示的值n或语句n;
end
*/
SELECT class_id,name,
CASE class_id
WHEN 1 THEN score + 1
WHEN 2 THEN score + 2
ELSE score
END AS '最终成绩'
FROM stu

SELECT class_id,name,
CASE 
WHEN score > 90 THEN 'A'
WHEN score > 60 THEN 'c'
ELSE 'd'
END AS '最终成绩'
FROM stu

  1. 分组函数（统计函数、聚合函数、组函数）（多行数据对应一行输出）

    • SUM：求和，处理数值型，null不参与运算

    • AVG：平均值，处理数值型，null不参与运算

    • MAX：最大值，处理任何数值，null不参与运算

    • MIN：最小值，处理任何数值，null不参与运算

    • COUNT：个数，处理任何数值，null不参与运算

    SELECT SUM(salary) FROM employees; 
SELECT AVG(salary) FROM employees; 
SELECT MAX(salary) FROM employees; 
SELECT MIN(salary) FROM employees; 
SELECT COUNT(salary) FROM employees; 
SELECT COUNT(DISTINCT salary) FROM employees;

    特点：

     * 可以和DISTINCT 配合使用
 * null不参与运算
 * COUNT(*)  MYISAM 效率高，INNODB下和COUNT(1)效率差不多
 * 和分组函数一同查询的字段要求是GROUP BY 后的字段

    5. 分组查询

    语法：

    select 分组函数，列（要求出现在group by的后面）

     from 表名

 【where 分组前筛选条件】

 group by 分组列表/函数分组

 【HAVING  分组后筛选条件】

 【order by 排序列表【asc | desc】】；

    #查询各个部门的平均工资
SELECT AVG(salary),department_id 
FROM employees
ORDER BY department_id;

#查询各个部门的平均工资，且平均工资大于1000
#HAVING对分组后进行条件筛选
SELECT AVG(salary),department_id 
FROM employees
HAVING AVG(salary) > 10000
ORDER BY department_id;

    链表查询

    sql99语法：

     select 查询列表

 from 表名1  别名

 join  表名2  别名

 on  连接条件

 【where 筛选条件】

 【group by 分组列表/函数分组】

 【HAVING  分组后筛选条件】

 【order by 排序列表【asc | desc】】；

    内连接：inner 
左外连接：left 【outer】
右外连接：right 【outer】
全外：full 【outer】
交叉：cross

    1. 内连接

      • 等值连接

        #sql92
SELECT name,department_name
FROM employees e,department d #笛卡尔积
WHERE e.department_id = d.id;

#sql99
SELECT name,department_name
FROM employees e
INNER JOIN department d
ON e.department_id = d.id;

      • 非等值连接

        #sql92
#查询员工工资和工资级别
SELECT salary,grade_level
FROM employees e,job_grade g
WHERE e.salary BETWEEN g.lowest_sal and g.Highest_sal;

#sql99
#查询员工工资和工资级别
SELECT salary,grade_level
FROM employees e
INNER JOIN job_grade g
ON e.salary BETWEEN g.lowest_sal and g.Highest_sal;

      • 自连接

        #sql92
# 查询员工和领导姓名
SELECT e.name,m.name
FROM employees eemployees m
WHERE e.manager_id = m.id;

#sql99
# 查询员工和领导姓名
SELECT e.name,m.name
FROM employees e
INNER JOIN employees m
ON e.manager_id = m.id;

    2. 外连接(sql99)

      特点：

      外连接的查询结果是为主表中的所有记录

           如果从表中有和它匹配的，则显示匹配的值

     如果从表中没有和它匹配的，则显示null

     左右外连接查询结果=内连接查询结果+主表中有而从表没有的记录

     全连接查询结果=内连接查询结果+表1中有但表2没有的记录+表2中有但表1没有的记录

      左外连接，left join左边的是主表

      右外连接，right join右边的是主表

      左外和右外交换两个表的顺序，可以实现同样的效果

      • 左外连接

        #查询哪个部门没有员工
SELECT d.*,e.employee_id
FROM departments d
LEFT JOIN employees e
ON d.department_id = e.department_id
WHERE e.department_id IS NULL;

      • 右外连接

        #查询哪个部门没有员工
SELECT d.*,e.employee_id
FROM employees e
RIGHT JOIN departments d
ON d.department_id = e.department_id
WHERE e.department_id IS NULL;

      • 全外连接

    3. 交叉连接（笛卡尔积）

总结：

SQL连接查询

7.子查询

含义：出现在其他语句中的select语句，称为子查询或内查询。外部的查询语句，称为主查询或外查询

特点：子查询放在小括号内

            子查询一般放在条件的右侧

            标量子查询，一般搭配着单行操作符使用

列子查询，一般搭配着多行操作符使用(in any/some all)

SELECT * 
FROM employees
WHERE salary > (
    SELECT salary
    FROM employees
    WHERE last_name = 'Abel'
);

8.分页查询

#查询第11-15条
SELECT * 
FROM employees
LIMIT 10,5;

9.联合查询

union 含义：将多条查询结果合并为一个结果

特点：
  • 要求多条件查询语句的查询列数是一致
  • 要求多条件查询语句的查询的每一列的类型和顺序最好一致
  • union关键字默认去重，如果使用union all 可以包含重复项
SELECT *  FROM employees WHERE id='1'
UNION
SELECT *  FROM employees WHERE id='2'
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 194,491评论 5赞 459
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 81,856评论 2赞 371
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 141,745评论 0赞 319
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 52,196评论 1赞 263
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 61,073评论 4赞 355
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 46,112评论 1赞 272
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 36,531评论 3赞 381
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 35,215评论 0赞 253
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 39,485评论 1赞 290
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 34,578评论 2赞 309
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 36,356评论 1赞 326
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 32,215评论 3赞 312
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 37,583评论 3赞 299
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 28,898评论 0赞 17
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 30,174评论 1赞 250
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 41,497评论 2赞 341
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 40,697评论 2赞 335

推荐阅读更多精彩内容