mysql学习笔记-数据查询语言DQL

DQL:Data QueryLanguage 数据查询语言标准语法
一、基础查询
语法：select 查询列表 from 表名;

1.查询字段
特点
（1）查询列表可以是：表中的字段，常量值，表达式，函数
（2）查询的结果是一个虚拟的表格
例：
（1）查询表中的单个字段
SELECT last_name FROM employees;
（2）查询表中的多个字段
SELECT last_name ,salary,emailFROM employees;
（3）查询表中的全部字段
SELECT employee_id,first_name,last_name,email,phone_number,job_id,salary,commission_pct,manager_id,department_id,hiredateFROM employees;
或
SELECT * FROM employees; （后者好像效率较低，因为需要先统计识别所有字段）

2.查询常量值
例：
（1）SELECT 100;
（2）SELECT ‘john’;

3.查询表达式
例：
（1）SELECT 100*98;
（2）SELECT 100%98;

4.查询函数
例：
（1）SELECT VERSION();

5.起别名
作用：
1.便于理解
2.避免查询字段出现重名
例：
方式一：用AS
（1）SELECT 100%98 AS 结果
（2）SELECT last_name ASln,first_name AS fn FROM employees
方式二：用空格
（1）SELECT last_name ln,first_name fn FROM employees
附：使得别名中可以携带空格
查询salary，显示结果out put
SELECT salary AS ‘out put’ FROM employees

6.去重
例：
SELECT DISTINCT department_id FROM employees
附：SELECT DISTINCT a ,b FROM other处理 （1,0）（1,1）（0,0）（0,1）结果为全部保留

7.+号的作用
只有运算符功能
两个操作数皆为数值型，做加法
若一方为字符型，尝试将字符型转换为数值
转换成功，做加法
转换失败，字符型转换为0，做加法
若有一方为null，结果为null
例：
（1）SELECT 100+90;
（2）SELECT ‘100’+90;
（3）SELECT ‘john’+90;
（4）SELECT NULL+90;

8.连接字符串
例：
SELECT CONCAT(‘a’,‘b’,‘c’) AS result

9.显示表结构
例：
DESC departments;

10.如果为空
IFNULL(列名,值)

二、条件查询
语法：select 查询列表 from 表名 where 条件;

1.按条件表达式筛选
条件运算符： > , < , = , != , <> , >= , <=
例：
SELECT * FROM employees WHERE salary >12000;
SELECT last_name,department_id FROM employees WHERE department_id<>90;

2.按逻辑表达式筛选
条件运算符：&& ，|| ， ！， and ， or ，not
例：
SELECT * FROM employees WHERE NOT(department_id>=90 AND department_id<=110) OR salary>15000;

3.模糊查询
（1）like
特点：
1.一般与通配符搭配使用
通配符：
%——任意多个字符
——任意单个字符
匹配字符串中的特别字符（如 '’）用 ‘’ 转义（如_,\）
可以用ESCAPE定义转义字符
'%%'的模糊匹配不能匹配到null——一个%号也可以模糊匹配
例：
SELECT * FROM employees WHERE last_name LIKE ‘%a%’;
SELECT last_name,salary FROM employees WHERE last_name LIKE ‘__n_l%’;
SELECT last_name FROM employees WHERE last_name LIKE ‘_%’;
SELECT last_name FROM employees WHERE last_name LIKE 'KaTeX parse error: Expected group after '_' at position 1: _̲%' ESCAPE '’;
（2）between and
可以提高语句的简洁度
包括临界值（>=和<=》）
两个临界值不可调换顺序
例：
SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 120;
（3）in
判断字段的值是否属于in列表中的某一项
使用in提高语句的简洁度
in列表的值类型必须一致或兼容
例：
SELECT last_name,job_id FROM employees WHERE job_id IN (‘AD_PRES’,‘AD_VP’,‘IT_PROG’);
（4）is null
=或><不能用于判断null值
IS NULL和 IS NOT NULL可以
例：
SELECT last_name,commission_pct FROM employees WHERE commission_pct IS NULL;
SELECT last_name,commission_pct FROM employees WHERE commission_pct IS NOT NULL;
（5）安全等与 <=>
例：
SELECT last_name,commission_pct FROM employees WHERE commission_pct <=> NULL
（6）is null对比<=>
is null：仅能判断null，可读性较高
<=>：既可以判断null值，又可以判断普通的数量，可读性较低

三、排序查询
语法：select 查询列表 from 表名 where 条件 order by 排序列表 asc或desc;
特点：
1.asc代表的是升序，desc代表的是降序，默认顺序是升序
2.可以按列名，表达式，别名，函数（如length()），多个字段（放前面的字段优先排序）排序
3.一般放在查询语句末尾，limit子句除外
例：
SELECT CONCAT(last_name,first_name) name , salary12(1+IFNULL(commission_pct,0)) YearSalary FROM employees ORDER BY YearSalary DESC , name ;
SELECT CONCAT(last_name,first_name) name ,salary FROM employees WHERE salary NOT BETWEEN 8000 AND 17000 ORDER BY salary DESC;
SELECT * FROM employees WHERE email LIKE (’%e%’) ORDER BY LENGTH(email) DESC , department_id ASC;

四、常见函数
概念：将一组逻辑语句封装在方法体中，对外暴露方法名
好处：1.隐藏了实现细节
2.提高代码的重用性
调用：select 函数名() from 表;
分类：
1.单行函数
如：concat、length、ifnull等
2.分组函数
功能：做统计使用，又称为统计函数、聚合函数、组函数

单行函数
1.字符函数
（1）length()
一个中文三个字节
例：
SELECT LENGTH(‘john’);
（2）concat()
拼接字符串
例：
UPPER(‘john’)
（3）upper、lower
转大写小写
（4）substr、substring
裁剪字符串
例：
SELECT SUBSTR(‘abcdefg’,3);
SELECT SUBSTR(‘abcdefg’,1,3);
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),’_’,LOWER(SUBSTR(last_name,2))) FROM employees;
（5）instr
返回字符串第一次出现位置，找不到返回0
SELECT INSTR(‘abcdefgh’,‘cd’);
（6）trim
去掉前后的多余字符，默认为空格
例：
SELECT TRIM(’ abc ‘);
SELECT TRIM(‘aa’ FROM ‘aaaaabaaacaaaa’);
（7）lpad（rpad）
用指定的字符左（右）填充到指定长度
若初始字符串过长，裁剪右部
例：
SELECT LPAD(‘abc’,10,’*’);
（8）replace替换
全部找到的相同字符串都替换
例：
SELECT REPLACE(‘abc’,‘a’,‘ddd’);

2.数学函数
（1）round
四舍五入
例：
SELECT ROUND(1.65);
SELECT ROUND(1.657,2);
（2）ceil（floor）
向上（下）取整，返回大于等于（小于等于）该参数的最小（大）整数
例：
SELECT CEIL(1.657);
SELECT FLOOR(1.657);
（3）truncate
截断
例：
SELECT TRUNCATE(1.23456,1);
（4）mod
取余
例：
SELECT MOD(10,3);
SELECT 10%3;

3.日期函数
（1）now
返回当前系统日期+时间
SELECT NOW();
（2）curdate
返回当前系统日期,不包括时间
SELECT CURDATE();
（3）curtime
返回当前系统时间,不包括日期
SELECT CURTIME();
（4）year、month、monthname、day、hour、minute、second等
获取指定的部分：年、月、日、小时、分钟、秒
SELECT YEAR(NOW());
SELECT YEAR(‘1998-1-1’);
SELECT YEAR(hiredate) FROM employees;
SELECT MONTH(NOW());
SELECT MONTHNAME(NOW());
（5）str_to_date
将日期格式的字符转换成指定格式的日期
SELECT STR_TO_DATE(‘9-13-1999’,’%m-%d-%Y’);
（6）date_format
将日期转换成字符
SELECT DATE_FORMAT(‘2018/6/6’,’%Y年%m月%d日’);
（7）格式符
1.%Y 四位的年份
2.%y 二位的年份
3.%m 月份（01,02…12）
4.%c 月份（1,2…12）
5.%d 日（01…）
6.%H 小时（24小时制）
7.%h 小时（12小时制）
8.%i 分钟（00,01…59）
9.%s 秒（00,01…59）
（8）datediff
获取两个日期之间相差的天数

4.其它函数
SELECT VERSION();
SELECT DATABASE();
SELECT USER();
SELECT MD5(‘zs’);

5.流程控制函数
（1）if函数
SELECT IF(10<5,‘对’,‘错’)
SELECT last_name,commission_pct,IF(commission_pct IS NULL , ‘没奖金呵呵’ , ‘有奖金嘻嘻’) 备注 FROM employees;
（2）case函数
方式一：switch case
例：
SELECT salary 原始工资,department_id,
CASE department_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary
END AS 新工资
FROM employees;
方式二：多重if
例：
SELECT salary 工资,
CASE department_id
WHEN salary>20000 THEN ‘A’
WHEN salary>15000 THEN ‘B’
WHEN salary>10000 THEN ‘C’
ELSE ‘D’
END AS 工资级别
FROM employees;

分组函数
分类：
sum求和、avg平均值、max最大值、min最小值、count计算个数
特点：
1.sum、avg一般用于处理数值型
max、min、count可以处理任何类型
2.以上分组函数都忽略null值
3.可以跟distinct搭配实现去重
4.一般用COUNT(*)统计行数
5.和分组函数一同查询的字段要求是group by后的字段

1.简单应用
SELECT SUM(salary) 和,AVG(salary) 平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary) 个数 FROM employees;
2.参数支持哪些类型
略
3.验证是否忽略null值
略
4.和distinct搭配
SELECT SUM(DISTINCT salary),SUM(salary) FROM employees
5.count函数
SELECT COUNT() FROM employees;
SELECT COUNT(1) FROM employees;
SELECT COUNT(salary) FROM employees;
效率：
MYISAM存储引擎下，COUNT()的效率高
INNODB存储引擎下，COUNT(1)和COUNT(*)效率差不多，比COUNT(字段高一些)

五、分组查询
语法：select 分组函数，列（会出现在group by的后面） from 表名 【where 条件】 group by 分组的列表【order by 子句】
特点：1.分组查询中的筛选条件分为两类
数据源 位置 关键字
分组前筛选 原始表 group by子句的前面 where
分组后筛选 分组后的结果集 group by子句的后面 having
分组函数做条件必定在having子句中
能用分组前筛选的就用分组前筛选
2.group by子句支持单个字段分组，多个字段分组（字段分组之间用逗号隔开且没有顺序要求），表达式或函数
3.也可以添加排序（排序在最后）

1.简单分组查询：
SELECT MAX(salary), job_id FROM employees GROUP BY job_id;
2.添加分组后筛选条件
SELECT COUNT(),department_id FROM employees GROUP BY department_id HAVING COUNT()>2;
3.按多个字段分组
4.添加排序

六、连接查询（又称多表查询）
当查询的字段来自于多个表时，就会用到连接查询
笛卡尔乘积现象：表1m行，，表2n行，结果m*n行
发生原因：没有有效的连接条件
如何避免：添加有效的连接条件
分类：
按年代分类：
sql92标准：仅支持内连接
sql99标准（推荐）：支持内连接，外连接和交叉连接
按功能分类：
内连接：
等值连接
非等值连接
自连接
外连接：
左外连接
右外连接
全外连接
交叉连接
1.sql92标准
（1）等值连接
1.多表等值连接的结果为多表的交集部分
2.n表连接，至少需要n-1个连接条件
3.多表的顺序没有要求
实例：
1.为表取别名
SELECT last_name ln ,department_name dn
FROM employees e,departments d
WHERE e.department_id=d.department_id
2.加筛选
3.加分组
4.加排序
5.三表连接
（2）非等值连接
使用between
（3）自连接

2.sql99标准
语法：
select 查询列表
from 表1名
【连接类型】join 表2名
on 连接条件
【where 】
【group by 】
【having】
【order by】;
分类：
内连接：inner
外连接：（outer可省略）
左外：left 【outer】
右外：right 【outer】
全外：full 【outer】
交叉连接：cross
（1）内连接
select 查询列表
from 表1名
inner join 表2名
on 连接条件
1.等值连接
SELECT last_name,department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
2.非等值连接
SELECT salary,grade_level
FROM employees e
JOIN job_grades g ON e.salary
BETWEEN g.lowest_sal AND g.highest_sal;
3.自连接
SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m ON e.manager_id=m.employee_id
（2）外连接
应用场景：用于查询一个表中有，另一个没有的记录
特点：
1、外连接的查询结果为主表中的所有记录
如果从表中有和它匹配的，则显示匹配的值
如果没有匹配的，则显示null
外连接查询结果=内连接结果+主表中有而从表没有的记录
2、左外连接：left join左边是主表
右外连接：right join右边是主表
全外连接
交叉连接
3、左外和右外交换两个表的顺序，效果相同
例子：
SELECT b.name,bo.*
FROM beauty b
LEFT OUTER JOIN boys bo ON b.boyfriend_id=bo.id;
全外链接：mysql不支持
FULL OUTER JOIN
交叉链接：
SELECT b.,bo.
FROM beauty b
CROSS JOIN boys bo ;

sql92对比sql99：
功能：sql99支持较多
可读性：sql99实现连接条件和筛选条件的分类离，

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

分类：
按子查询出现的位置：
select 后面：
仅仅支持标量子查询
from 后面：
仅仅支持表子查询
where或having 后面：
标量子查询（单行）
列子查询（多行）
行子查询
exists后面 （相关子查询）
表子查询
按结果集的行列数不同：
标量子查询（结果集只有一行一列）
列子查询（结果集只有多行一列）
行子查询（结果集只有一行多列）
表子查询（结果集一般为多行多列）

1.where或having 后面：
标量子查询（单行）
列子查询（多行）
行子查询（多列多行）
特点：
（1）子查询一般放在小括号内
（2）子查询一般放在条件的右侧
（3）
标量子查询：一般搭配着单行操作符使用

< >= <= = <>
·列子查询：一般搭配着多行操作符使用
in、any/some、all
（4）子查询的执行优先于主查询

例：1.标量子查询
SELECT * FROM employees
WHEREsalary > (
SELECT salary FROM employees
WHERE last_name=‘Abel’
);
2.列子查询

SELECT * FROM employees
WHEREsalary IN(
SELECT salary FROM employees
WHERE last_name=‘Abel’
);
3.行子查询（多列多行）
SELECT * FROM employees
WHERE (employee_id,salary)=(
SELECT MIN(employee_id),MAX(salary)
FROM employees
)

2.select 后面：
SELECT d.,(
SELECT COUNT() FROM employeese
WHERE d.department_id=e.department_id
)个数
FROM departments d

3.from后面：
SELECT ag_dep.*,g.grade_level
FROM(
SELECT AVG(salary) ag,department_id FROM employees
GROUP BY department_id
)ag_dep
INNER JOIN job_grades g ON ag_dep.ag
BETWEEN lowest_sal AND highest_sal;

4.exists后面（相关子查询）
结果是0或1
SELECT EXISTS(SELECT * FROM employees)

八、分页查询
应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求
语法：
select 查询列表
from 表
【
】
limit 【offset】size;
offset:要显示条目的起始索引
size：要显示的条目个数

九、联合查询
union：将多条查询结果合并

语法：
语句1
union
语句2

应用场景：
查询结果来自多表，但信息一致

特点：
查询列数一致
查询列类型、顺序最好一致
默认去重，如果用union则可包含重复项