MySQL之DQL语句

MySQL之DQL语句

DQL是MySQL的数据查询语言

查询所有列

 SELECT * FROM 表名;

查询指定列

 SELECT 列1 [, 列2, ... 列N] FROM 表名;

完全重复的记录只一次

当查询结果中的多行记录一模一样时，只显示一行。一般查询所有列时很少会有这种情况，但只查询一列（或几列）时，这总可能就大了！

 SELECT DISTINCT * | 列1 [, 列2, ... 列N] FROM 表名;
 -- 查询员工表的工资，如果存在相同的工资只显示一次
 SELECT DISTINCT sal FROM emp;

列运算：数量类型的列可以做加、减、乘、除运算

 SELECT sal*1.5 FROM emp;
 SELECT sal+comm FROM emp;

字符串类型可以做连续运算

 SELECT CONCAT('$', sal) FROM emp;

转换NULL值

有时需要把NULL转换成其它值，例如com+1000时，如果com列存在NULL值，那么NULL+1000还是NULL，而我们这时希望把NULL当前0来运算。

 /*IFNULL(comm, 0)：如果comm中存在NULL值，那么当成0来运算*/
 SELECT IFNULL(comm, 0)+1000 FROM emp;
 /*如果student_name列为null，则返回'没有名字'*/
 select ifnull(student_name,'没有名字') from student_table;
 /*如果student_name列等于'张三'，则返回null*/
 select nullif(student_name,'张三') from student_table;
 /*如果student_name列为null，则返回'没有名字'，否则返回'有名字'*/
 select if(isnull(student_name),'没有名字','有名字') from student_table;

给列起别名

你也许已经注意到了，当使用列运算后，查询出的结果集中的列名称很不好看，这时我们需要给列名起个别名，这样在结果集中列名就显示别名了

-- 其中AS可以省略
SELECT IFNULL(comm, 0)+1000 AS 奖金 FROM emp;

条件控制（条件查询）

与UPDATE和DELETE语句一样，SELECT语句也可以使用WHERE子句来控制记录

SELECT empno,ename,sal,comm FROM emp WHERE sal > 10000 AND comm IS NOT NULL;
SELECT empno,ename,sal FROM emp WHERE sal BETWEEN 20000 AND 30000;
SELECT empno,ename,job FROM emp WHERE job IN ('经理', '董事长');

模糊查询

-- 模糊查询需要使用运算符：LIKE，其中_匹配一个任意字符，注意，只匹配一个字符而不是多个。下面语句查询的是姓张，名字由两个字组成的员工
SELECT * FROM emp WHERE ename LIKE '张_';
-- 姓名由3个字组成的员工
SELECT * FROM emp WHERE ename LIKE '___'; 
-- 其中%匹配0~N个任意字符，所以上面语句查询的是姓张的所有员工
SELECT * FROM emp WHERE ename LIKE '张%';
-- 千万不要认为上面语句是在查询姓名中间带有阿字的员工，因为%匹配0~N个字符，所以姓名以阿开头和结尾的员工也都会查询到
SELECT * FROM emp WHERE ename LIKE '%阿%';
-- 这个条件等同与不存在，但如果姓名为NULL的查询不出来
SELECT * FROM emp WHERE ename LIKE '%';

排序

 -- 按sal排序，升序,其中ASC是可以省略的
 SELECT * FROM emp ORDER BY sal ASC;
 -- 按comm排序，降序,其中DESC不能省略
SELECT * FROM emp ORDER BY comm DESC;
-- 使用sal升序排，如果sal相同时，使用comm的降序排
SELECT * FROM emp ORDER BY sal ASC, comm DESC;

聚合函数：聚合函数用来做某列的纵向运算

• COUNT

-- 计算emp表中所有列都不为NULL的记录的行数
SELECT COUNT(*) FROM emp;
-- 计算emp表中comm列不为NULL的记录的行数
SELECT COUNT(comm) FROM emp;

• MAX

 -- 查询最高工资
 SELECT MAX(sal) FROM emp;

• MIN

-- 查询最低工资
SELECT MIN(sal) FROM emp;

• SUM

 -- 查询工资合
 SELECT SUM(sal) FROM emp;

• AVG

-- 查询平均工资
SELECT AVG(sal) FROM emp;

分组查询 分组查询是把记录使用某一列进行分组，然后查询组信息。

-- 使用deptno分组，查询部门编号和每个部门的记录数
SELECT deptno, COUNT(*) FROM emp GROUP BY deptno;
-- 使用job分组，查询每种工作的最高工资
SELECT job, MAX(SAL) FROM emp GROUP BY job;
-- 以部门分组，查询每组记录数。条件为记录数大于3,组条件只能是分组字段或分组函数
SELECT deptno, COUNT(*) FROM emp GROUP BY deptno HAVING COUNT(*) > 3;

limit子句(方言)： LIMIT用来限定查询结果的起始行，以及总行数。

-- 查询起始行为第5行，一共查询3行记录，其中4表示从第5行开始，其中3表示一共查询3    行。即第5、6、7行记录。
SELECT * FROM emp LIMIT 4, 3;
-- 一页的记录数：10行，查询第3页（公式：(当前页-1) * 每页记录数）
select * from emp limit 20, 10；

补充

-- 查询teacher_table表中teacher_name列的字符长度
select char_length(teacher_name) from teacher_table;
-- 计算teacher_name列的字符长度的sin值
select sin(char_length(teacher_name)) from teacher_table;
-- 计算1.57的sin值，约等于1
select sin(1.57);
-- 为指定日期添加一定的时间,在这种用法下interval是关键字，需要一个数值，还需要一个单位
select date_add('1998-01-02',interval 2 month);
-- 这种用法更简单
select adddate('1998-01-02',3);
-- 获取当前日期
select curdate();
-- 获取当前时间
select curtime();
-- 获取某个字符通过MD5加密后的数据
select md5('testing');
-- 如果java_teacher为1，则返回'java老师'，为2则返回'Ruby老师'，否则返回'其他老师'
select student_name, case java_teacher
when 1 then 'java老师'
when 2 then 'Ruby老师'
else '其他老师'
end
from student_table;

多表查询

多表查询分类

• 合并结果集(了解)
• 连接查询
• 子查询

合并结果集

• 要求被合并的表中，列的类型和列数相同
• UNION，去除重复行
• UNION ALL，不去除重复行

连接查询

• 内连接
• 外连接：左外连接、右外连接、全外连接(MySQL不支持)
• 自然连接（属于一种简化方式）：参考资料https://blog.csdn.net/JCY1009015337/article/details/53024912

1、内连接

-- 方言
SELECT * FROM 表1 别名1, 表2 别名2 WHERE 别名1.xx=别名2.xx
-- 标准
SELECT * FROM 表1 别名1 INNER JOIN 表2 别名2 ON 别名1.xx=别名2.xx
-- 自然
SELECT * FROM 表1 别名1 NATURAL JOIN 表2 别名2
-- 内连接查询出的所有记录都满足条件

2、外连接

• 左外

SELECT * FROM 表1 别名1 LEFT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx

左表记录无论是否满足条件都会查询出来，而右表只有满足条件才能出来。左表中不满足条件的记录，右表部分都为NULL

• 左外自然

SELECT * FROM 表1 别名1 NATURAL LEFT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx

• 右外

SELECT * FROM 表1 别名1 RIGHT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx

右表记录无论是否满足条件都会查询出来，而左表只有满足条件才能出来。右表不满足条件的记录，其左表部分都为NULL

• 右外自然

SELECT * FROM 表1 别名1 NATURAL RIGHT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx

• 全链接：可以使用UNION来完成全链接

子查询

查询中有查询（查看select关键字的个数！）

1、子查询出现的位置：

* where后作为条件存在
* from后作为表存在(多行多列)

2、子查询条件

• 单行单列

SELECT * FROM 表1 别名1 WHERE 列1 [=、>、<、>=、<=、!=] (SELECT 列 FROM 表2 别名2 WHERE 条件)

• 多行单列

SELECT * FROM 表1 别名1 WHERE 列1 [IN, ALL, ANY] (SELECT 列 FROM 表2 别名2 WHERE 条件)

• 单行多列

SELECT * FROM 表1 别名1 WHERE (列1,列2) IN (SELECT 列1, 列2 FROM 表2 别名2 WHERE 条件)

• 多行多列

SELECT * FROM 表1 别名1 , (SELECT ....) 别名2 WHERE 条件

多表查询时的条件顺序

• where
• group by
• having
• order by
• limit