MySQL总结（DQL）

基础查询(6个子句)->子查询(非关联\关联)->
表间关联查询(内连接\外连接)

数据查询语言:DQL

(Data Query Language)实现数据查询操作
select:查询数据————查询是重点(基础查询->子查询->表间关联查询)

from(指定表)->where(记录过滤)->group by(分组)->
having(分组后过滤)->select(结果集)->order by(排序)

4)空值处理函数
ifnull(e1,e2):如果e1为空则取e2代替

6)连接操作concat(a,b,c…)
//查询员工信息，要求将员工姓名和职位连接在一起

select empno,concat(ename,ifnull(position,'No Position')) message from emp_xu;
1

7)去重
distinct:只能跟在select后面
//查询有哪些职位

select distinct position from emp_xu;
1

9)介于两者之间between 低值 and 高值，肯定形式:[低值,高值]

10)in(列表项):判断等于列表项中任意一项

select ename,position
from emp_xu
where position in("Analyst","Manager");
1
2
3

a.使用in时列表项中的空值不影响结果
b.空值不能用等于(=)或不等于(!=)跟任何数据(本身)
进行比较

11)模糊查询使用like
占位符 _表示一个字符 %表示0到多个字符

//查询员工姓名中包含’张’字的员工信息

select ename 
from emp_xu 
where ename like '%张%';
1
2
3

//查询职位中第2个字符为’a’的员工姓名和职位

select ename,position
from emp_xu
where position like '_a%';//全表扫描
1
2
3

12)空值判断
判断空值
肯定形式:is null
否定形式:is not null
空值不能用等于和不等于跟任何数据进行比较

select ename,bonus
from emp_xu
where bonus is null;
1
2
3

13)否定形式
//查询哪些人有奖金

select ename,bonus
from emp_xu
where bonus is not null;
1
2
3

14)not in使用（不能有空值）
not in(列表项):判断不等于所有项(同时满足)

select ename,deptno
from emp_xu
where deptno not in(20,30); 
1
2
3

总结:
in(列表项):判断等于任意一项,列表项中空值没有影响
not in(列表项):判断不等于所有项,列表项中有空值
影响结果(列表项中空值必须去掉!!!)

基础查询_函数

date_format(时间,格式):格式化时间数据
//测试

select date_format(now(),'%X-%m-%d %H:%i:%s');
1

基础查询——排序

//查询员工姓名和薪水，要求薪水从低到高进行排序

select ename,salary
from emp_xu
order by salary;//升序 asc可以省略（desc降序）
1
2
3

基础查询——分组

select deptno,max(salary),min(salary)
from emp_xu
where deptno is not null
group by deptno;//分组之后同一组中部门号相同的
1
2
3
4

having:对分组之后的数据再进行过滤

---

高级查询–子查询

非关联子查询执行过程:[理解->记住!!!]
先执行嵌套子查询(独立语句、单独执行、仅执行一次)->
返回查询结果作为主查询的条件->再执行主查询

select ename,salary
from emp_xu
where salary=(
select max(salary) from emp_xu
);
1
2
3
4
5

//查询每个部门的最高薪水的员工信息（多值多列）
多值:比较符选择
多列:比较规则要相同(部门号等于部门号，薪水等于最大薪水)

select ename,deptno,salary
from emp_xu
where (deptno,salary) in(
select deptno,max(salary)
from emp_xu
where deptno is not null
group by deptno
);
1
2
3
4
5
6
7
8

13)查询哪些员工的薪水是本部门的平均薪水值
分析:

select ename,salary,deptno
from emp_xu
where (deptno,salary) in(
select deptno,avg(ifnull(salary,0))
from emp_xu
where deptno is not null
group by deptno
);//多值多列
1
2
3
4
5
6
7
8

子查询——关联查询

//查询哪些员工的薪水比本部门的平均薪水值低
分析:
部门号相等(=)，薪水小于(<)平均薪水,
两者的比较规则不一致，非关联子查询实现不了。

SELECT deptno,ename
FROM emp_xu e
WHERE salary<(
SELECT AVG(salary) FROM emp_xu WHERE deptno=e.deptno);
1
2
3
4

关联子查询执行过程:
先执行主查询(传递数据)->执行嵌套子查询(依赖数据)
->返回查询结果->再执行主查询
嵌套子查询执行多次!!

2)查询哪些人有下属
分析:找领导->满足员工号在leader字段中

exists用于判断有没有数据返回，如果满足关系则有
数据返回，不满足则没有数据返回。

exists不关心嵌套子查询返回结果，嵌套子查询中select
后面写什么都可以，通常直接用1表示。

select empno,ename
from emp_xu e1
where exists(
select 1 from emp_xu
where leader=e1.empno
);
1
2
3
4
5
6

三、分页查询

重要!!!
limit:限制查询记录数
语法:
select 字段 from 表 limit 数量;
select 字段 from 表 limit 每页开始下标,数量;
ps:下标表示从0开始计算

pageSize:每页的记录数(数量)
page:第几页
int beginIndex=(page-1)*pageSize;

//分页查询核心SQL
select 字段 from 表 limit beginIndex,pageSize;

四、表间关联查询(多表联合查询)

内连接

语法:[]可选项
表1 [inner] join 表2 on 条件
1
2

//查询员工姓名和其部门的名字
分析:
结果集 ename dname

select ename,d.deptno,dname
from emp_xu e join dept_xu d 
on e.deptno=d.deptno;//d.deptno指明哪个表的
1
2
3

ps:内连接的结果集表示是在两个表中都能找到的匹配记录

说明:
表1 join 表2 on 条件
a.表1为驱动表，表2为匹配表
b.执行过程
遍历驱动表在匹配表中找匹配记录，匹配上的记录保留，
匹配不上的记录被丢掉。
c.等值连接(条件用等号)中驱动表和匹配表可以
互换不影响结果

外连接

严格区分哪个表是驱动表
语法:
//左外连接:左边的表为驱动表
表1 left [outer] join 表2 on 条件
//右外连接:右边的表为驱动表
表1 right [outer] join 表2 on 条件

外连接特点:
外连接的结果集是驱动表中所有数据。匹配上的记录
保留，匹配不上的记录匹配一行空记录。

//查询员工的姓名和其部门的名字，要求没有部门的员工
也要被查询出来
分析:
查询全部员工->外连接->员工表作为驱动表

select ename,ifnull(dname,'No Dept') dname
from emp_xu e left join dept_xu d 
on e.deptno=d.deptno;
1
2
3

组合查询

union会自动去重\union all不去重

select ename,salary
from emp_xu
where deptno=10
union all
select ename,salary
from emp_xu
where salary>6000;
1
2
3
4
5
6
7

约束类型

1)主键约束
主键约束 primary key 简称PK
不能重复+不能为空
一张表中只能有一个主键
mysql中支持主键自增：
primary key auto_increment

create table stu_xu(
id int primary key auto_increment,
name varchar(10)
);
1
2
3
4

2)非空约束
非空约束:not null 简称NN
只有列级定义

create table stu_xu(
id int primary key auto_increment,
name varchar(10) not null
);
1
2
3
4

3)唯一约束
唯一约束:unique 简称UK

create table stu_xu(
id int primary key auto_increment,
name varchar(10) not null,
email varchar(20) unique
);
1
2
3
4
5

4)外键约束
外键约束:foreign key 简称FK
外键约束定义在两张表的两个字段上，用于保证两个字段
之间的关系。

如果表A的主键是表B中的字段，则该字段称为表B的外键。
表A为主表，表B为从表
A表 B表
部门表 员工表
deptno(主键) empno(主键)
deptno(外键)

//创建部门表

create table temp_dept(
deptno int primary key auto_increment,
dname varchar(10) not null
);
1
2
3
4

//创建员工表

create table temp_emp(
empno int primary key auto_increment,
ename varchar(10) not null,
deptno int,
constraint temp_emp_deptno_fk foreign key(deptno)
references temp_dept(deptno) 
);  
1
2
3
4
5
6
7