1 DQL(数据查询语言)
1.1 基本的查询语句
select * from table;#获取table的所有内容
select语句返回筛选出来的元组集合。表现形式为表格,由表头和值组成。表头默认为查询的指定的,可通过as来改变。值为计算结果或查询结果。
如:
select 1+1;
结果为:
2
as关键字
select field1 as fd1,field2 as fd2 from table;#获取table的field1并命名为fd1,获取table的field2并命名为fd2
select field "fd" from table;#as fd 和 "fd"等价
distinct关键字
SELECT DISTINCT field FROM table;#获取table的field并去重
表达式
SELECT field ,score2+score1 as score FROM table;
null参与运算结果认为null
着重号
解决列名或表名为关键字的情况
SELECT * FROM `ORDER`;#order为关键字
1.2 where语句
select * from table where 条件;#逐个元组进行条件判断
包括算数,比较,逻辑和位运算符。
算数运算
规则 | 表达式 | 结果 |
---|---|---|
字符串会转化为数值,无法转化为0 | 100+‘1’/100+‘a’ | 101/100 |
null值参与运算为null,除以0为null | 1+null | null |
比较运算符
运算符 | 说明 | 表达式 | 结果 |
---|---|---|---|
基础运算符(>,<,=,!=等等) | 同算数运算 | ||
<=>,is null ,is not null | 可以与null比较 | field <=> null | 1 |
least(),greatest() | 最值运算 | least(1,2,3)/greatest(1,2,3) | 1/3 |
between A and B | 区间运算,A<B | 1 between 1 and 3 | 1 |
in,not in | 判断元素是否在集合内 | 1 in (1,2,3)1 | 1 |
like | 模糊查询,规则: 1. %为匹配任意长度且任意字符 2. _为匹配单个长度且任意字符同正则的’.' \为转义字符,用法同c语言中的 | ‘a’ like ‘%a%’ | 1 |
regexp,rlike | 正则表达式:^,*2,.,[],$ | ‘a’ rlike ‘^a*’ | 1 |
逻辑运算符
运算符 | 示例 |
---|---|
not 或 ! | not 1 |
and 或 && | 1 and 1 |
or 或 || | 0 or 1 |
xor | 1 xor 1 |
位运算符
运算符 | 描述 |
---|---|
>> | 右移 |
<< | 左移 |
| | 按位或 |
& | 按位与 |
^ | 异或 |
~ | 按位取反 |
1.3 order by 语句
select * from table order by field1 [asc,desc],field2 [asc,desc],...;#最终结果按照field1属性优先排列,默认升序asc,field1相同时按照field2排列
注意:
- mysql中order by语句排序算法是快排和归并排序。
SELECT * FROM employees ORDER BY 常量i;#表示按照第i列排序
排序依据也可以不在本表中,但必须与本表的相关字段一一对应。(相关子查询实现)
1.4 limit 语句
分页:在已选出的结果中从偏移量开始再选出指定数量的元组。(偏移量从0开始)
#分页操作
SELECT * FROM table LIMIT 1*20,20;#在已选出的结果中获取从第21(偏移量+1)行开始的连续20个元组
SELECT * FROM table LIMIT 20 offset 1*20;#效果相同
1.5 group by 语句
特点:
-
分组
-
group by 后面出现的字段,select后可以出现,但没有出现的,select不可以,除非为聚合函数
如:SELECT salary,job_id FROM employees GROUP BY job_id;//字段错误
having及之后执行的语句限制同2,如order by,having,select。除非与字段一一对应3
#应用场景:获取每个类的统计情况
SELECT AVG(salary),job_id FROM employees GROUP BY job_id;#获取不同工作的平均工资
#可多元分组
SELECT AVG(salary),job_id,department_id FROM employees GROUP BY job_id,department_id;#获取不同部门不同工作的平均薪资
1.6 having 语句
作用:过滤分组。
特点:
having 条件
举例:
条件 | 举例 |
---|---|
聚合函数 | SELECT department_id,AVG(salary) FROM employees GROUP BY department_id HAVING AVG(salary)>8000; |
筛选分组6 | SELECT department_id,AVG(salary) FROM employees GROUP BY department_id HAVING department_id in (1,2,3) ; |
1.7 语句执行顺序
form语句 --> where 语句 --> group by语句 -->having语句 -->select语句 -->distinct语句 --> order by语句 --> limit 语句
1.8 多表查询
1.8.1 基础操作
三表查询为两个表先进行笛卡尔积,之后的结果再与第三个表进行笛卡尔积。where对最后生成的表起作用。多表查询同理。
笛卡尔积
select table1.field from table1,table2;
注意点 | 例子 |
---|---|
1.属性应为table.field。不指定表,属性应为特有的。 | table1.field |
2.别名,一旦起别名便不能用原来的表名 | table as tb |
1.8.2 连接操作
等值连接
应用场景:关联多个表的目标列
#表的某一列要符合要关联表的目标列符合的相等条件
select * from table1,table2 WHERE table1.列名 = table2.列名;#法1
select * from table1 inner join table2 on table1.列名 = table2.列名;#法2
多表等值连接可以看为逐个进行等值连接。
非等值连接
应用场景:分类
#表的某一列要符合要关联表的目标列符合的范围条件
SELECT e.name,e.salary,j.level FROM employees e, job_grades j WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;#查询工资的等级
SELECT e.name,e.salary,j.level FROM employees e inner join job_grades j on e.salary BETWEEN j.lowest_sal AND j.highest_sal;
自连接
应用场景:层级关系
SELECT e1.employee_id e1.last_name e2.employee_id e2.last_name FROM employees e1 ,employees e2 WHERE e1.manager_id = e2.employee_id;#查询员工及其上司的姓名和id
SELECT e1.employee_id e1.last_name e2.employee_id e2.last_name FROM employees e1 inner join employees e2 on e1.manager_id = e2.employee_id;
1.8.3 内连接和外连接
内连接 :结果集中不包含不包括不符合链接条件的元组(默认)。
table1 inner join table2 on 条件
外连接:结果集中包含不包括不符合链接条件7的元组,不符合的为null。包括左外连接、右外连接、满外连接。
左外连接:
应用场景:获取全部元组的关联信息,匹配不上设置为null
table1 left join table2 on 条件#
例子:
表a:
表b:
SELECT a.`name` , b.loc FROM a LEFT JOIN b ON a.id = B.id;
右外连接:
左外连接的逆向操作
table2 right join table1 on 条件#等价于table1 left join table2 on 条件
满外连接:
MySQL没有直接语句
满外连接=左外连接结果集U右外连接结果集 - 左外连接结果集∩右外连接结果集
外连接七种集合状态
关键: union ,左右连接,is null
集合操作 | 说明 |
---|---|
A left join B on A.field = B.field | A |
A right join B on A.field = B.field | B |
A inner join B on A.field = B.field | A ∩ B |
A union B | A∪B |
A left join B on A.field = B.field where A.field is null | A - A ∩ B |
A right join B on A.field = B.field where B.field is null | B - A ∩ B |
A - A ∩ B union B - A ∩ B | (A - A ∩ B)∪(B - A ∩ B) |
1.8.4 集合操作
集合操作 | 说明 |
---|---|
union | 合并会去重 |
union all | 合并不去重 |
多表查询逻辑
- 查看几张表并选择连接方式
- 根据条件对连接后的表在进行where等语句的编写和扩充
1.9 函数
9.1.1 内置函数
1.9.1.1 单行函数
特点:一次作用一行。
数值函数
函数 | 举例 |
---|---|
基本函数 | pi(),rand() |
三角函数 | sin(),asin() |
角度和弧度之间的互换 | radians(),degree() |
指数和对数 | pow(),exp(),ln() |
进制转换 | hex(),oct(),bin() |
字符串函数
函数 | 说明 |
---|---|
ascii() | 返回ascii码 |
length() | 字节长度 |
char_length() | 字符长度 |
concat() | 拼接 |
日期和时间函数
流程控制函数
函数 | 说明 |
---|---|
if(条件,a,b) | 等价于条件运算符 |
ifnull(a,b) | 如果a不为null返回a,否则返回b |
case when 条件1 then 结果 when 条件2 then 结果2 else 结果3 end | 相当于if … else if … else … |
case expr when 值1 then 结果 when 值2 then 结果2 else 结果3 end | 相当于switch…case |
加密和解密函数
函数 | 说明 |
---|---|
sha(str) | sha加密 |
md5(str) | md5加密 |
mysql信息函数
获取mysql的相关信息,如版本,使用的数据库等
1.9.1.2 聚合函数
特点:
- 对多行起作用。
- 不可嵌套使用聚合函数
可以单行函数联合使用。如COUNT(IFNULL(salary,0))
函数 | 说明 |
---|---|
AVG(field) | 一列均值 |
SUM(field) | 一列和 |
MIN(field) | 最小 |
MAX(field) | 最大 |
COUNT(*),COUNT(field)8,COUNT(常数) | 计元组个数 |
1.9.2 自定义函数
1.10 子查询
术语:外查询,内查询
适用范围:适用于大多数语句。
联用对象 | 说明 |
---|---|
select | 改值:select中子查询大多与函数联用,对值进行再加工。 |
from | 作为数据来源表 |
where | 过滤数据 |
having | 过滤组 |
order by | 按照非本表属性排序。 |
子查询逻辑:
- 判断是否可以使用子查询
- 判断使用在哪一个语句中
- 有内查询到内查询思考
1.10.1 分类
按条目数分类
单行子查询:子查询返回单元组
多行子查询:子查询返回多元组
按相关和不相关分类
相关子查询:内查询和外查询有联系
不相关子查询:内查询和外查询无联系
1.10.2 单行子查询
操作符:基础比较运算符
SELECT * FROM employees e1 WHERE e1.salary > ( SELECT e2.salary FROM employees e2 WHERE e2.employee_id = 149);#查询工资大于149号员工的员工信息
元组中有多个列:使用and进行拼接或是元组相等
SELECT * FROM employees WHERE (manager_id,department_id) = ( SELECT manager_id ,department_id FROM employees WHERE employee_id = 141);#元组方式判断与141号员工相同manager_id,department_id员工的信息。
SELECT * FROM employees WHERE manager_id = ( SELECT manager_id FROM employees WHERE employee_id = 141 ) AND department_id = (SELECT department_id FROM employees WHERE employee_id = 141);#and方式判断与141号员工相同manager_id,department_id员工的信息。
作用范围举例
SELECT employee_id,last_name,IF(department_id = (SELECT department_id FROM departments WHERE location_id = 1800),'Canada','USA') FROM employees;
SELECT department_id,MIN(salary) FROM employees GROUP BY department_id HAVING MIN(salary) > ( SELECT MIN(salary) FROM employees WHERE department_id = 50 );
1.10.3 多行子查询
操作符
操作符 | 说明 | 例子 | 值 |
---|---|---|---|
in | 是否在集合里 | 1 in (1,2,3) | 1 |
any | 与单行子查询运算符联用,表示集合中的任意一个 | 1 < any (1,2,3) | 1 |
all | 与单行子查询运算符联用,表示集合的全部 | 1 < all (1,2,3) | 0 |
some | 同any | 1 < some (1,2,3) | 1 |
举例
SELECT department_id FROM employees GROUP BY department_id HAVING AVG(salary) <= all (SELECT AVG(salary) FROM employees GROUP BY department_id);#查询平均工资最低的部门id,注意:聚合函数不可嵌套
null值问题
描述:当子表中有null值,null参与运算,结果不准确,所以要过滤null。
举例
SELECT department_id FROM employees GROUP BY department_id HAVING AVG(salary) <= all (SELECT AVG(salary) where salary is not null FROM employees GROUP BY department_id );#查询平均工资最低的部门id,去除null值
1.10.4 相关子查询
举例
SELECT last_name,salary,department_id FROM employees e1 WHERE salary > ( SELECT AVG(salary) FROM employees WHERE department_id = e1.department_id);#获取工资大于所属部门平均工资的员工信息
SELECT * FROM employees e ORDER BY (SELECT department_name FROM departments WHERE department_id = e.department_id);#查询员工信息,按照department_name查,department_name不在员工表中
应用
- 获取分组中的元组
( ... WHERE a = e.a );#获取分组中的元组方法。where语句可以获取分组中的全部元组。
如:
#查询部门的部门号,其中不包括job_id是"ST_CLERK"的部门号
SELECT department_id FROM employees e GROUP BY department_id HAVING 'ST_CLERK' NOT IN ( SELECT job_id FROM employees WHERE department_id = e.department_id);
#查询各部门中工资比本部门平均工资高的员工的员工号,姓名和工资(相关子查询)
SELECT employee_id ,last_name,salary FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);
1.10.5 EXISTS 关键字
exists (子查询)
作用:判断子查询是否为空。如果子查询为非空返回1。
a in (b,c,d)可以代替 a = b or a = c or a = d,比较用=,而不是== ↩︎
正则中的*使用必须在字符后面,如a*,这点与%不同 ↩︎
如jobs表中,job_id和 job_name一一对应(等价)。SELECT job_name FROM jobs GROUP BY job_id; ↩︎
where不能与聚合函数联用,聚合函数需要在组上工作,即分组之后 ↩︎
可以不和group by同时使用,即整个表为一个组,没有意义。结果要么为整个表处理后的数据,要么为null ↩︎
建议where 语句写,时间少 ↩︎
本表相应元组对应的属性null ↩︎
count(field),是计算field中部位null的元组数,聚合函数对field为null的会进行过滤。count函数是逐行判断和记录的。 ↩︎