DQL语法整理笔记

目录

1.什么是DQL

2.基础查询

       2.1 基础查询语法

       2.2 查询表中单个字段

       2.3 查询表中多个字段

       2.4 查询表中所有字段

       2.5 查询常量值

       2.6 查询表达式

       2.7 查询函数

3.条件查询

       3.1 条件查询语法及分类

       3.2 按照条件表达式筛选

       3.3 按照逻辑表达式筛选

       3.4 模糊查询 like

       3.5 模糊查询 between and

       3.6 模糊查询 in

       3.7 模糊查询 is null

4.排序查询

       4.1 排序查询语法

       4.2 按照一个排序条件倒排

       4.3 按照两个排序条件倒排

5.单行函数

       5.1 单行函数分类

       5.2 字符函数

       5.3 数学函数

       5.4 日期函数

       5.5 流程控制函数语法

              5.5.1 流程控制函数示例

       5.6 其他函数

6.分组函数

          6.1 SUM：求和

          6.2 AVG：平均值

          6.3 MAX：最大值

          6.4 MIN：最小值

          6.5 COUNT：统计个数

7.分组查询

       7.1 分组查询语法

       7.2 按照单个字段分组

       7.3 按照多个字段分组

8.连接查询

       8.1 连接查询语法

       8.2 内连接

              8.2.1 等值连接

              8.2.2 非等值连接

              8.2.3 自连接

       8.3 外连接

              8.3.1 左外连接

              8.3.2 右外连接

              8.3.3 全外连接（MySql不支持全外连接）

9.子查询

       9.1 子查询分类

       9.2 WHERE或HAVING后面子查询的特点

       9.3 WHERE后面的标量子查询

       9.4 WHERE后面的列子查询（一列多行）

       9.5 WHERE后面的行子查询（一行多列）

       9.6 SELECT后面的标量子查询

       9.7 FROM后面的表子查询

       9.8 EXISTS后面的子查询

10.分页查询

       10.1 分页查询语法

       10.2 LIMIT

       10.3 原生分页SQL

11.联合查询

       11.1 联合查询语法

       11.2 UNION

       11.3 UNION和UNION ALL异同点

---

1.什么是DQL

DQL：Data Query Language 数据查询语言

标准语法：

       SELECT

              select_list

       FROM

              table_source

       [ WHERE 查询条件]

       [ GROUP BY 分组条件]

       [ HAVING 筛选条件]

       [ ORDERBY 排序方式 [ ASC | DESC ] ]

测试数据来源：https://zhuanlan.zhihu.com/p/43289968

-- 建表
-- 学生表
CREATE TABLE `Student`(
`s_id` VARCHAR(20),
`s_name` VARCHAR(20) NOT NULL DEFAULT '',
`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(`s_id`)
);
-- 课程表
CREATE TABLE `Course`(
`c_id` VARCHAR(20),
`c_name` VARCHAR(20) NOT NULL DEFAULT '',
`t_id` VARCHAR(20) NOT NULL,
PRIMARY KEY(`c_id`)
);
-- 教师表
CREATE TABLE `Teacher`(
`t_id` VARCHAR(20),
`t_name` VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(`t_id`)
);
-- 成绩表
CREATE TABLE `Score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
);
-- 机构表
CREATE TABLE `t_org_info`  (
  `ORG_ID` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `ORG_CODE` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `ORG_NAME` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `ORG_ADDRESS` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `OFFICE_TEL` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `LINK_MAN` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `ORG_TYPE` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `OLD_ORG_TYPE` bigint(20) NULL DEFAULT NULL,
  `ORG_POST_CODE` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `PARENT_CODE` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `BRANCH_CODE` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `MAX_BOOKING_NUM` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `HAS_CHILD` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `PROVINCE_CODE` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `PROVINCE_NAME` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `CITY_NAME` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `AREA_NAME` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`ORG_ID`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 10012 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- 插入学生表测试数据
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
-- 课程表测试数据
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

-- 教师表测试数据
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

-- 成绩表测试数据
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);
-- 机构表插入数据
INSERT INTO `t_org_info` VALUES (216, '14956', '测试商业银行安州区支行', '绵阳市安州区五路口158号', '18180891222', 'XXX', '3', NULL, '622655', '14913', '104674000243', NULL, NULL, '510000', '四川省', '成都市', '青羊区');
INSERT INTO `t_org_info` VALUES (10001, '00023', '测试商业银行四川省分行总行', '四川省成都市青羊区人民中路二段35号', '86403208', 'XXX', '1', NULL, '7865734', '0', '104674000231', NULL, '1', '510000', '四川省', '成都市', '金牛区');
INSERT INTO `t_org_info` VALUES (10002, '15368', '测试商业银行总行营业部', '四川省成都市青羊区人民中路二段35号', '15982315300', 'XXX', '2', NULL, '7443384', '00023', '104651003017', NULL, '1', '510000', '四川省', '成都市', '金牛区');
INSERT INTO `t_org_info` VALUES (10003, '14901', '测试商业银行营业部', '四川省白塔区提督街１０８号中石化大楼１楼２楼.', '86664581/86257350', 'XXX', '3', NULL, '0837788/6974874', '15368', '104651005218', NULL, '1', '510000', '四川省', '成都市', '白塔区');
INSERT INTO `t_org_info` VALUES (10005, '14902', '测试商业银行市蜀西路支行', '四川省成都市青羊区实业街7号(省委办公厅宿舍楼)', '13558608065', 'XXX', '2', NULL, '6540585', '00023', '104651087611', NULL, NULL, '510000', '四川省', '成都市', '青羊区');
INSERT INTO `t_org_info` VALUES (10006, '15381', '测试商业银行蜀西路支行（全辖汇总）', '成都市青羊区提督街１０８号中石化大楼１楼、２楼', '15982840891/86664147', 'XXX', '3', NULL, '0806110', '14902', '104651087555', NULL, NULL, '510000', '四川省', '成都市', '东坡区');
INSERT INTO `t_org_info` VALUES (10011, '14913', '测试商业银行绵阳分行（全辖汇总）', '四川省成都市高新区天府大道中段1366号', '86056673/18180906271,62038662/13668143066', 'XXX', '2', NULL, '3949649/7560665', '00023', '104651087444', NULL, '1', '510000', '四川省', '成都市', '青羊区');

2.基础查询

       2.1 基础查询语法

SELECT 
	查询列表 
FROM 
	表名;

       2.2 查询表中单个字段

SELECT
	t_id 
FROM
	teacher;

       2.3 查询表中多个字段

SELECT 
    t_id,
    t_name 
FROM 
    teacher;

       2.4 查询表中所有字段

SELECT 
    *
FROM 
    teacher;

       2.5 查询常量值

SELECT 100;
SELECT 'tom';

       2.6 查询表达式

SELECT 100%98;

       2.7 查询函数

SELECT VERSION();

3.条件查询

       3.1 条件查询语法及分类

SELECT 
	查询列表 
FROM 
	表名
WHERE
	筛选条件;

条件查询可分为三类：

 特点：

       1）按条件表达式筛选

              条件运算符：>、 <、=、!=、<>、>=、<=

       2）按逻辑表达式筛选（用于连接条件表达式）

              逻辑运算符：and、or、not 

       3）模糊查询

              like、between and、in、is null

       3.2 按照条件表达式筛选

SELECT
	t_id,
	t_name 
FROM
	teacher 
WHERE
	t_id > 2

       3.3 按照逻辑表达式筛选

SELECT
	t_id,
	t_name 
FROM
	teacher 
WHERE
	t_id = 2
AND 
	t_name = '李四'

       3.4 模糊查询 like

 特点：

       % 任意多个字符，包含0个字符

       _ 任意单个字符

SELECT
	t_id,
	t_name 
FROM
	teacher 
WHERE
	t_name 
like '李%'

SELECT
	t_id,
	t_name 
FROM
	teacher 
WHERE
	t_name 
like '李_'

       3.5 模糊查询 between and

SELECT
	t_id,
	t_name 
FROM
	teacher 
WHERE
	t_id BETWEEN 1 AND 2

       3.6 模糊查询 in

SELECT
	t_id,
	t_name 
FROM
	teacher 
WHERE
	t_id in(1,2) 

       3.7 模糊查询 is null

SELECT
	t_id,
	t_name 
FROM
	teacher 
WHERE
	t_id IS NULL

4.排序查询

       4.1 排序查询语法

SELECT 
    查询列表
FROM 
    表
【WHERE 筛选条件】
ORDER BY 排序列表 【ASC|DESC】

 特点：

       1）ASC：升序（默认）  DESC：降序

       2）order by子句汇总可以支持单个字段、多个字段、表达式、函数、别名

       3）order by子句一般是放在查询语句的最后面，limit子句除外

       4.2 按照一个排序条件倒排

SELECT
	t_id,
	t_name 
FROM
	teacher 
WHERE
	t_id IS NOT NULL 
ORDER BY
	t_id DESC

       4.3 按照两个排序条件倒排

SELECT
	t_id,
	t_name 
FROM
	teacher 
WHERE
	t_id IS NOT NULL 
ORDER BY
	t_id,
	t_name DESC

5.单行函数

       5.1 单行函数分类

              1）字符函数

              2）数字函数

              3）日期函数

              4）流程控制函数

              5）其他函数

       5.2 字符函数

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

SELECT LENGTH('tom')	

        CONCAT：拼接字符串

SELECT CONCAT(t_id,'_',t_name) FROM teacher

        UPPER、LOWER：大小写转换

SELECT UPPER('tom');
SELECT LOWER('TOM');

         SUBSTE、SUBSTRING：截取字符串，从索引处截取指定长度的字符

SELECT SUBSTR('TOM',1,2)

         INSTR：用于返回子串在整串字符中的起始索引，相当于java中的String.indexof

SELECT INSTR('TOMM','TOM');

         TRIM：去掉字符串中指定字符

SELECT TRIM('a' FROM 'aaaaTOMaaaa');

         LPAD：用指定字符实现左填充指定长度，中间数字代表字符串总长度，填充数目=输入的字符串总长度 - 字符串长度

SELECT LPAD('TOM',10,'*');

         RPAD：用指定字符实现右填充指定长度，中间数字代表字符串总长度，填充数目=输入的字符串总长度 - 字符串长度

SELECT RPAD('TOM',10,'*');

          REPLACE：替换指定字符串内容

SELECT REPLACE('TOM','M','N');

       5.3 数学函数

         ROUND：四舍五入

SELECT ROUND(-1.45);

         CEIL：向上取整，返回>=该参数的最小整数

SELECT CEIL(-1.45);

         FLOOR：向下取整，返回<=该参数的最大整数

SELECT FLOOR(-1.45);

         TRUNCATE：截断

SELECT TRUNCATE(1.69999,1);

          MOD：取余

SELECT MOD(10,3);

       5.4 日期函数

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

SELECT NOW();

          CURDATE：返回当前系统日期，不包含时间

SELECT CURDATE();

          CURTIME：返回当前时间，不包含日期

SELECT CURTIME();

          STR_TO_DATE：将日期格式的字符转换成指定格式的日期

SELECT STR_TO_DATE('9-13-1999','%m-%d-%Y');

          DATE_FORMAT：将日期转换成字符

SELECT DATE_FORMAT('1999/6/13','%Y年%m月%d日');

       5.5 流程控制函数语法

          IF：判断条件是否满足，满足输出第一个字符，反之输出第二个字符，类似于Java中三目运算符

SELECT IF(10>5,'大','小');

           CASE：相当于Java中的switch case

语法：

case 要判断的表达式

when 常量1 then 要显示的值1或者语句1；

when 常量2 then 要显示的值2或者语句2；

...

else 要显示的值n或者语句n；

end

              5.5.1 流程控制函数示例

SELECT
	t_id,
	t_name,
CASE
	t_id 
	WHEN 01 THEN 1 
	WHEN 02 THEN 2 
	ELSE 3 
END 
FROM
	teacher

       5.6 其他函数

          VERSION：返回当前mysql版本号

SELECT VERSION();

          DATABASE：返回当前使用的数据库

SELECT DATABASE();

          USER：返回当前登录用户

SELECT USER();

6.分组函数

          6.1 SUM：求和

SELECT
	SUM( t_id ) 
FROM
	teacher

          6.2 AVG：平均值

SELECT
	AVG( t_id ) 
FROM
	teacher

          6.3 MAX：最大值

SELECT
	MAX( t_id ) 
FROM
	teacher

          6.4 MIN：最小值

SELECT
	MIN( t_id ) 
FROM
	teacher

          6.5 COUNT：统计个数

SELECT
	COUNT( t_id ) 
FROM
	teacher

7.分组查询

       7.1 分组查询语法

SELECT 
    分组函数，列（要求出现在group by的后面）
FROM 
    表
【WHERE 
    筛选条件】
GROUP BY 
    分组列表
【ORDER BY 
    子句】

分组查询中的筛选条件分为两类：

1）分组前筛选：

       数据源：原始表

       位置：group by子句前面

       关键字：where

2）分组后筛选：

       数据源：分组后的结果集

       位置：group by子句后面

       关键字：having

注：查询列表是特殊的，要求是分组函数和group by后出现的字段（如果select不写分组列默认按照主键分组）；分组函数做条件放在having子句中

       7.2 按照单个字段分组

SELECT
	count(*),
	t_id 
FROM
	teacher 
WHERE 
	t_id = 01 OR t_id = 02
GROUP BY
	t_id
HAVING 
    count(*) = 1
ORDER BY t_id DESC

       7.3 按照多个字段分组

SELECT
	count(*),
	t_id,
	t_name 
FROM
	teacher 
GROUP BY
	t_id,
	t_name 
HAVING
	count(*) = 1

8.连接查询

       8.1 连接查询语法

SELECT 
    查询列表
FROM 
    表1 别名 【连接类型】
JOIN 
    表2 别名
ON    
    连接条件
【WHERE 筛选条件】
【GROUP BY 分组】
【HAVING 筛选列表】
【ORDER BY 排序列表】

分类：

       内连接：inner

       外连接：

              左外连接：left

              右外连接：right

              全外连接：full

       8.2 内连接

内连接又分为三类：

       1）等值连接

       2）非等值连接

       3）自连接

              8.2.1 等值连接

SELECT 
	stu.s_name,sc.c_id,sc.s_score
FROM 
	student stu
INNER JOIN 
	score sc
ON
	stu.s_id = sc.s_id

              8.2.2 非等值连接

SELECT 
	stu.s_name,sc.s_score
FROM 
	student stu
INNER JOIN 
	score sc
ON
	stu.s_id BETWEEN sc.c_id AND sc.s_score

              8.2.3 自连接

SELECT 
	org1.org_name 当前机构名称,org2.org_name 上级机构名称
FROM
	t_org_info org1
INNER JOIN
	t_org_info org2
ON 
	org1.PARENT_CODE = org2.ORG_CODE

       8.3 外连接

特点：

       1）外连接的查询结果为主表中的所有记录，如果从表中有和主表匹配的值，则显示匹配的值，否则显示null

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

       3）左外连接，LEFT JOIN左边的是主表；右外连接，RIGHT JOIN右边的是主表

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

       5）全外连接 = 内连接的结果 + 左表中有但右表没有的 + 右表中有但左表中没有的

注：MySql不支持全外连接

              8.3.1 左外连接

SELECT 
	stu.s_name,sc.s_score
FROM 
	student stu
LEFT JOIN 
	score sc
ON
	stu.s_id = sc.s_id

              8.3.2 右外连接

SELECT 
	stu.s_name,sc.s_score
FROM 
	score sc
RIGHT JOIN 
	student stu
ON
	stu.s_id = sc.s_id

              8.3.3 全外连接（MySql不支持全外连接）

SELECT 
	stu.*,sc.*
FROM 
	score sc
FULL JOIN 
	student stu
ON
	stu.s_id = sc.s_id

9.子查询

       9.1 子查询分类

按结果集的行列数不同

       标量子查询：结果集只有一行一列

       列子查询：结果集只有一列多行

       行子查询：结果集只有一行多列

       表子查询：结果集一般为多行多列

按子查询出现的位置

       SELECT后面：仅仅支持标量子查询

       FROM后面：支持表子查询

       WHERE或HAVING后面：标量子查询、列子查询、行子查询

       EXISTS后面：表子查询

       9.2 WHERE或HAVING后面子查询的特点

       1）子查询放在小括号内

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

       3）标量子查询，一般搭配着单行操作符（>、<、>=、<=、=、<>）使用

       4）列子查询一般搭配着多行操作符（IN、ANY、ALL）使用

       9.3 WHERE后面的标量子查询

SELECT
	* 
FROM
	teacher 
WHERE
	t_id = ( SELECT MAX( t_id ) FROM teacher )

       9.4 WHERE后面的列子查询（一列多行）

SELECT
	* 
FROM
	teacher 
WHERE
	t_id in ( SELECT  t_id  FROM teacher )

注：WHERE后面接列子查询需要使用多行比较操作符，如IN、NOT IN、ANY、SOME、ALL等 

       9.5 WHERE后面的行子查询（一行多列）

SELECT
	* 
FROM
	teacher 
WHERE
	(t_id,t_name) = ( SELECT  *  FROM teacher WHERE t_id = '03')

       9.6 SELECT后面的标量子查询

SELECT
	( SELECT MAX( t_id ) FROM teacher )

       9.7 FROM后面的表子查询

SELECT
	a.count 
FROM
	( SELECT COUNT( t_id ) count, t_id FROM teacher GROUP BY t_id ) a

       9.8 EXISTS后面的子查询

SELECT EXISTS (SELECT * FROM teacher WHERE t_id = 01)

10.分页查询

       10.1 分页查询语法

SELECT 
    查询列表
FROM 
    表
【JOIN 表2 
ON 连接条件
WHERE 筛选条件
GROUP BY 分组字段
HAVING 分组后的筛选条件
ORDER BY 排序字段】
LIMIT OFFSET,SIZE;

OFFSET:要显示条目的起始索引
SIZE:要显示的条目个数

       10.2 LIMIT

SELECT
	* 
FROM
	teacher 
LIMIT 0,1;

       10.3 原生分页SQL

SELECT
	* 
FROM
	table 
LIMIT (page - 1) * size,size;

start：页码
limit：每页显示的条数

11.联合查询

       11.1 联合查询语法

查询语句1
UNION
查询语句2
UNION
...

应用场景：要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时

       11.2 UNION

SELECT tea.t_name FROM teacher tea
UNION
SELECT stu.s_name FROM student stu

       11.3 UNION和UNION ALL异同点

相同点：

       都是对两个结果集进行并集操作

不同点：

       UNION：不包括重复行，同时进行默认规则的排序

       UNION ALL：包括重复行，不进行排序