【数据库基础】02_数据库基础练习

部门表

#部门表
create table t_department(
did int primary key auto_increment,
dname varchar(100) not null, 
description varchar(200), 
manager_id int
);
insert into t_department(dname,description) values
('教学部','技术培训'), 
('咨询部','课程咨询服务');

工作表

#工作表
create table `t_job` (
`job_id` int(11) primary key auto_increment, 
`job_name` varchar(100) default null, 
`description` varchar(200) default null
);

insert into t_job values
(null,'JavaSE 讲师','Java 基础'), 
(null,'Web 讲师','Web 基础'), 
(null,'JavaEE 框架','框架讲解'), 
(null,'课程顾问','课程咨询');

雇员表

#雇员表
create table t_employee(
eid int primary key auto_increment, 
ename varchar(100) not null, 
gender char not null default '男', 
card_id char(18) unique, tel char(11),
job_id int, 
`mid` int, #领导编号
birthday date, 
hiredate date, #入职日期
address varchar(100), 
dept_id int, 
foreign key (dept_id) references t_department(did), #雇员表 dept_id 外键绑定 t_department 中 did
foreign key (job_id) 
references t_job(job_id)
);

insert into
`t_employee`(`eid`,`ename`,`gender`,`card_id`,`tel`,`job_id`,`mid`,`birthday`,`hiredate`,`address`,`dept_id`)
values (1,'孙红雷','男','123456789012345678','12345678901',1,null,'1990-01-01','2015-01-01','白庙',1),
(2,'张亮','男','123456789012345677','12345678902',2,null,'1990-01-02','2015-01-02','天通苑北',1), 
(3,'鹿晗','男','123456789012345676','12345678903',3,null,'1990-01-03','2015-01-03','北苑',1), 
(4,'邓超','男','123456789012345675','12345678904',2,null,'1990-01-04','2015-01-04','和谐家园',1), 
(5,'孙俪','女','123456789012345674','12345678905',3,null,'1990-01-05','2015-01-05','霍营',1), 
(6,'Angelababy','女','123456789012345673','12345678906',4,null,'1990-01-06','2015-01-06','回龙观',2),
(7,'林俊杰','男','123456789012345679','12345678907',1,null,'1993-01-01','2015-01-07','国贸',1);

薪资表

#薪资表
create table t_salary(
eid int primary key, 
basic_salary decimal(10,2), #底薪
performance_salary decimal(10,2), #绩效
commission_pct decimal(10,2), #提成
deduct_wages decimal(10,2), #扣薪
foreign key (eid) references t_employee(eid) );#外键 eid 依附于表 t_employee 的 eid

insert into
`t_salary`(`eid`,`basic_salary`,`performance_salary`,`commission_pct`,`deduct_wages`)
values (1,'12000.00','6000.00','0.40','0.00'), 
(2,'9000.00','5000.00','0.20',null), 
(3,'11000.00','8000.00',null,null), 
(4,'13000.00','5000.00',null,null), 
(5,'8000.00','8000.00','0.30',null), 
(6,'15000.00','6000.00',null,null),
(7,'666.66','666.66',null,null);

修改数据

语法

UPDATE 表名称 SET 字段名 1 = 值 1, 字段名 2=值 2,...... 【WHERE 条件】;
UPDATE 表 1，表 2,...... SET 表 1.字段名 1 = 值 1, 表 1.字段名 2=值 2,表 2.字段 1 = 值 1, 表 2.字段 2=值 2...... 【WHERE 条件】;

说明

1、如果不写 where 条件，会修改所有行

2、值可以是常量值、表达式、函数

3、可以同时更新多张表

如果两个表没有建立外键，但逻辑上有外键关系时：

4、如果修改从表外键字段的数据，要注意查看主表参照字段的值是否存在

5、如果修改主表的被参考的字段的值，要注意查看从表的外键是否有依赖该值，如果有
（1）如果外键是 on update RESTRICT 或 on update NO ACTION，那么要先处理从表的数据，才能修改
（2）如果外键是 on update SET NULL 或 on update CASCADE，那么直接修改，从表的外键字段会自动处理

练习

1、修改所有人的基本工资，涨薪 5%（不加where，修改所有）

update t_salary set basic_salary=basic_salary*1.05;

2、修改"孙俪"的手机号码为"13709098765"，生日为"1982-09-26"

update t_employee set tel=13709098765,birthday='1982-09-26' where ename='孙俪';

3、修改"邓超"的入职日期为今天（使用curdate()，当前年月日）

update t_employee set hiredate=curdate() where ename='邓超';

4、修改"咨询部"的主管 id 为 6

update t_department set manager_id = 6 where did = 2;
#or
update t_department set manager_id = 7 where dname = '咨询部';

5、修改"教学部"的主管 id 为 1

update t_department set manager_id =1 where did = 1;

6、修改"教学部"的主管 id 为"孙红雷"的编号（涉及到到两个表的情况，有外键需要将外键条件写上）

update t_department,t_employee 
set t_department.`manager_id` = t_employee.`eid`
where t_employee.`dept_id` = t_department.`did`
t_employee.`ename` = '孙红雷' and
t_department.`dname` = '教学部';

7、修改所有员工的领导编号为该员工所在部门的主管编号

update t_employee te,t_department td 
set te.`mid` = td.`manager_id`
where te.`dept_id` = td.`did`;

8、修改教学部的主管编号，以及该部门所有员工的领导编号为"邓超"的编号

#1.修改教学部的主管编号为邓超
update t_department td,t_employee te
set td.`manager_id` = te.`eid`
where td.`dname` = '教学部' 
and td.`did` = te.`dept_id` 
and te.`ename` = '邓超'

#2.修改该部门所有员工的领导编号为"邓超"的编号
update t_employee,t_department
set t_employee.mid = t_department.manager_id
where t_employee.dept_id = t_department.did
and t_department.`dname` = '教学部' ;

删除数据

语法

delete from 表名 【where 条件】;
delete 表 1，表 2,....... from 表 1，表 2，...... 

说明

1、如果不加 where 条件，表示删除整张表的数据，表结构保留。

delete from 表名;删除整张表的数据还可以使用truncate表名;

区别：

• truncate 相当于删除表再重建一张同名结构的表，操作后得到一张全新表，而 delete 是在原有表中删除数据。如果
  决定清空一张表的数据，truncate 速度更快一些。
• truncate 语句不能回滚

2、如果删除主表的记录，要注意查看从表的外键是否有依赖该行的值，如果有
（1）如果外键是 on delete RESTRICT 或 on delete NO ACTION，那么要先处理从表的数据，才能删除
（2）如果外键是 on delete SET NULL 或 on delete CASCADE，那么删除时从表的对应记录也会被置空或跟着删除

3、可以一次删除多个表的数据
例如：两个表没有建立外键，但逻辑上有外键关系，也可以通过删除多个表的数据来实现级联删除

练习

1、删除学号为 9 的学生信息

DELETE FROM t_stu WHERE sid = 9;

2、删除所有“教学部”的员工信息和薪资信息和“教学部”部门信息。(注意：前提是没有外键或外键是 on delete cascade)

DELETE t_employee,t_department,t_salary
FROM t_employee,t_department,t_salary
WHERE t_department.`dname` ='教学部' 
AND t_employee.`dept_id`=t_department.`did`
AND t_employee.`eid` = t_salary.eid

查询数据

语法

SELECT 查询列表
FROM 表名或视图列表
【WHERE 条件表达式】
【GROUP BY 字段名 【HAVING 条件表达式】】
【ORDER BY 字段 【ASC|DESC】】
【LIMIT m,n】;

例如：

#查询表中的所有行所有列
#使用*表示，查询所有字段，即查询所有行
select * from t_stu;
#查询部分字段，查询部分列
select sname,major from t_stu;
#查询所有列，部分行
select * from t_stu where major = 'JavaEE';
#查询部分行，部分列
select sname,major from t_stu where major = 'JavaEE';

说明

1、如果 SELECT 后面是*，那么表示查询所有字段
2、SELECT 后面的查询列表，可以是表中的字段，常量值，表达式，函数
3、查询的结果是一个虚拟的表
4、select 语句，可以包含 5 种子句：依次是 where、 group by、having、 order by、limit 必须照这个顺序。

去重 distinct

1、查询员工表的部门编号（去重）

select distinct dept_id from t_employee;

2、统计员工表中员工有几个部门（聚合+去重）

select count(distinct dept_id) from t_employee;

算术运算符

+，-，*，/(div)，%(mod)

1、筛选出 eid 是偶数的员工

select * from t_employee where eid % 2 = 0;
select * from t_employee where eid mod 2 = 0;

2、查看每天的基本工资值，每个月按 22 天算

select eid,basic_salary/22 as "日薪" from t_salary;
#div 也表示除，但是只保留整数部分
select eid,basic_salary div 12 as "日薪" from t_salary;

3、关于+；在 MySQL 中+只表示数值相加，如果遇到非数值类型，先尝试转成数值，如果转失败，就按 0 计算。

select eid+ename from t_employee;#ename无效
select eid+birthday from t_employee;#全部转换成数值

MySQL 中字符串拼接要使用字符串函数实现concat()

select concat(eid,":",ename) as result from t_employee;

比较运算符

=，>, <，>=, <=， !=(不等于<>)，<=>（安全等于）

1、查询基本工资不为10000的eid

select eid,basic_salary from t_salary where basic_salary != 10000;
select eid,basic_salary from t_salary where basic_salary <> 10000;

2、查询 basic_salary=10000，注意在 Java 中比较是==

select eid,basic_salary from t_salary where basic_salary = 10000;

3、查询 commission_pct 等于 0.40

select eid,commission_pct from t_salary where commission_pct = 0.40;
select eid,commission_pct from t_salary where commission_pct <=> 0.40;

4、查询 commission_pct 等于 NULL

select eid,commission_pct from t_salary where commission_pct is null;
select eid,commission_pct from t_salary where commission_pct <=> null;

5、查询 commission_pct 不等于 NULL

select eid,commission_pct from t_salary where commission_pct is not null;
select eid,commission_pct from t_salary where not commission_pct <=> null;

逻辑运算符

与&&，或||，非!
与 AND，或 OR

1、查询性别男，并且在 90 以前出生的员工

select * from t_employee where gender='男' and birthday<'1990-01-01';

2、查询职位编号 job_id 是 1 或 2 的员工

select * from t_employee where job_id =1 or job_id = 2;

3、查询基本薪资是在 9000-12000 之间的员工编号和基本薪资

select eid,basic_salary from t_salary where basic_salary >=9000 and basic_salary<=12000;

4、查询基本薪资不在 9000-12000 之间的员工编号和基本薪资(可以使用not_and)

select eid,basic_salary from t_salary where not (basic_salary >=9000 and basic_salary<=12000);
#or
select eid,basic_salary from t_salary where basic_salary <9000 or basic_salary>12000;

范围和集合

between … and … 和 not between … an
in(集合) 和 not in(…）

1、查询基本薪资是在 9000-12000 之间的员工编号和基本薪资

select eid,basic_salary from t_salary where basic_salary between 9000 and 12000;

2、查询基本薪资是在 9000-12000 之间的员工编号和基本薪资

select eid,basic_salary from t_salary where basic_salary between 9000 and 12000;

3、查询 eid 是 1,3,5 的基本工资

select eid,basic_salary from t_salary where eid in (1,3,5);

模糊查询

like 和 通配符 一起使用
like _ 匹配单个字符
like % 匹配任意个字符

1、查询名字中有’超’字的员工信息

select * from t_employee where ename like '%超%';

2、查询姓邓的员工信息

select * from t_employee where ename like '邓%';

3、查询姓邓，名字就一个字的员工信息

select * from t_employee where ename like '邓_';

4、查询邓超的信息

select * from t_employee where ename like '邓超';
#or
select * from t_employee where ename = '邓超';

NULL 值判断与计算处理

关于 null 值计算，所有运算符遇到 null 都是 null
函数：IFNULL(表达式,用什么值代替)

1、查询奖金百分比不为空的员工编号

select eid,commission_pct from t_salary where commission_pct is not null;

2、查询奖金百分比为空的员工编号

select eid,commission_pct from t_salary where commission_pct is null;

3、计算实际的薪资： basic_salary + salary * 奖金百分比

select eid,basic_salary + performance_salary *(1+ commission_pct) from t_salary;#错误的

函数：IFNULL(表达式,用什么值代替)

select eid,(basic_salary + performance_salary *(1+ ifnull(commission_pct,0))) as real_salary from t_salary;

4、查询奖金百分比为空的员工编号（<=> 安全等于）

select eid,commission_pct from t_salary where commission_pct <=> null;

位运算符

>> << & | ~ ^（异或）

SELECT 2^3,2&3,2|3,2>>3,2<<3,~3;

关联查询

作用

从 2 张或多张表中,取出有关联的数据. 关联查询一共有几种情况：

• 内连接：INNER JOIN 、CROSS JOIN
• 外连接：左外连接（LEFT OUTER JOIN）、右外连接（RIGHT OUTER JOIN）、全外连接（FULL OUTER JOIN）
• 自连接：当 table1 和 table2 本质上是同一张表，只是用取别名的方式虚拟成两张表以代表不同的意义

说明

（1）连接 n 个表，至少需要 n-1 个连接条件。 例如：连接三个表，至少需要两个连接条件。
（2）当两个关联查询的表如果有字段名字相同，并且要查询中涉及该关联字段，那么需要使用表名前缀加以区分。
（3）当如果表名比较长时，可以给表取别名，简化 SQL 语句

笛卡尔积

定义：将两（或多）个表的所有行进行组合，连接后的行数为两（或多）个表的乘积数. 在 MySQL 中如下情况会出现笛卡尔积，主要是因为缺少关联条件或者关联条件不准确。

注：外连接必须写关联条件，否则报语法错误。

1、查询员工姓名和所在部门名称

#笛卡尔积
SELECT ename,dname FROM t_employee,t_department;
SELECT ename,dname FROM t_employee INNER JOIN t_department;
SELECT ename,dname FROM t_employee CROSS JOIN t_department;
SELECT ename,dname FROM t_employee JOIN t_department;

关联条件

表连接的约束条件可以有三种方式：WHERE, ON, USING

• WHERE：适用于所有关联查询。
• ON：只能和 JOIN 一起使用，只能写关联条件。虽然关联条件可以并到 WHERE 中和其他条件一起写，但分开写可读性更好。
• USING：只能和 JOIN 一起使用，而且要求两个关联字段在关联表中名称一致，而且只能表示关联字段值相等

内连接（INNER JOIN）

有两种，显式的和隐式的，返回连接表中符合连接条件和查询条件的数据行

隐式：

SELECT [cols_list] from 表 1,表 2

显式：

SELECT [cols_list] from 表 1 INNER JOIN 表 2 ON [关联条件] where [其他筛选条件]
SELECT [cols_list] from 表 1 CROSS JOIN 表 2 ON [关联条件] where [其他筛选条件]
SELECT [cols_list] from 表 1 JOIN 表 

select <select_list> 
from table_A  A inner join table_B B 
on A.Key = B.Key;

练习：

1、查询员工姓名与基本工资

• 隐式内连接：笛卡尔积

select te.`ename`,td.`dname` 
from t_employee te,t_department td
where te.`dept_id`=td.`did`

• 显式内连接：把关联条件写在 on 后面，只能和 JOIN 一起使用

select ename,dname 
from t_employee inner join t_department 
on t_employee.dept_id=t_department.did;

select ename,dname 
from t_employee cross join t_department
on t_employee.dept_id=t_department.did;

select ename,dname 
from t_employee join t_department 
on t_employee.dept_id=t_department.did;

2、查询员工姓名与基本工资

把关联字段写在 using()中，只能和 JOIN 一起使用。

而且两个表中的关联字段必须名称相同，而且只能表示=

select ename,basic_salary 
from t_employee inner join t_salary using(eid);

3、查询员工姓名，基本工资，部门名称

n 张表关联，需要 n-1 个关联条件。

• 隐式内连接

select ename,basic_salary,dname 
from t_employee,t_department,t_salary
where t_employee.dept_id=t_department.did 
and t_employee.eid=t_salary.eid;

• 显式内连接

select ename,basic_salary,dname 
from t_employee inner join t_salary inner join t_department
on t_employee.`dept_id` = t_department.`did` 
and t_employee.`eid` = t_salary.`eid`;

外连接（OUTER JOIN）

外连接分为：

• 左外连接（LEFT OUTER JOIN），简称左连接（LEFT JOIN）

返回左表中的所有行，如果左表中行在右表中没有匹配行，则结果中右表中的列返回空值。

select<select_list> 
from table_A left join table_B 
on table_A = table_B;

练习：

1、查询所有部门信息以及该部门员工信息

select  did,dname,eid,ename
from t_department td left join t_employee te
on td.`did` = te.`dept_id`

2、查询所有员工信息，以及员工的部门信息

select eid,ename,did,dname
from t_employee left outer join t_department
on t_employee.dept_id = t_department.did ;

返回左边中行在右表中没有匹配行的记录

select<select_list> 
from table_A left join table_B 
on table_A = table_B;
where B.Key is null

练习：

1、查询部门信息，仅保留没有员工的部门信息

select  did,dname,eid,ename
from t_department td leftjoin t_employee te
on td.`did` = te.`dept_id`
where te.`dept_id` is null;

2、查询员工信息，仅保留没有分配部门的员工

select eid,ename,did,dname
from t_employee left outer join t_department
on t_employee.dept_id = t_department.did
where t_employee.dept_id is null;

• 右外连接（RIGHT OUTER JOIN），简称右连接（RIGHT JOIN）

恰与左连接相反，返回右表中的所有行，如果右表中行在左表中没有匹配行，则结果中左表中的列返回空值。

select<select_list> 
from table_A right join table_B 
on table_A = table_B;

练习：

1、查询所有部门信息以及该部门员工信息

select did,dname,eid,ename
from t_employee right join t_departmen
on t_department.did = t_employee.dept_id;

2、查询所有员工信息，以及员工的部门信息

select eid,ename,did,dname
from t_department right  join t_employee
on t_employee.dept_id = t_department.did ;

返回右表中在左表没有匹配行的记录。

select<select_list> 
from table_A right join table_B 
on table_A = table_B
where A.Key is null;

练习：

1、查询部门信息，仅保留没有员工的部门信息

select did,dname,eid,ename
from t_employee right outer join t_departmen
on t_department.did = t_employee.dept_id
where t_employee.dept_id is null;

2、查询员工信息，仅保留没有分配部门的员工

select eid,ename,did,dname
from t_department right outer join t_employee
on t_employee.dept_id = t_department.did
where t_employee.dept_id is null;

• 全外连接（FULL OUTER JOIN），简称全连接（FULL JOIN）。
  

mysql 不支持 FULL JOIN，但是可以用 left join union right join 代替。

select <select_list> 
from table_A  A full outer join table_B B 
on A.Key = B.Key;

练习：

1、查询所有部门信息和员工信息

select did,dname,eid,ename
from t_department left outer join t_employee
on t_department.did = t_employee.dept_id
union
select did,dname,eid,ename
from t_department right outer join t_employee
on t_department.did 

select <select_list> 
from table_A  A full outer join table_B B 
on A.Key = B.Key
where A.Key is null or B.Key is null;

练习：

1、查询所有没有员工的部门和没有分配部门的员工

select did,dname,eid,ename
from t_department left outer join t_employee
on t_department.did = t_employee.dept_id
where t_employee.dept_id is null
union
select did,dname,eid,ename
from t_employee left outer join t_department
on t_department.did = t_employee.dept_id
where t_employee.dept_id is null

自连接

当 table1 和 table2 本质上是同一张表，只是用取别名的方式虚拟成两张表以代表不同的意义。然后两个表再进行内连接，外连接等查询。

练习：

1、查询员工姓名以及领导姓名，仅显示有领导的员工

select emp.ename,mgr.ename
from t_employee as emp, t_employee as mgr
where emp.mid = mgr.eid;

2、查询员工姓名以及领导姓名，仅显示有领导的员工

select emp.ename,mgr.ename
from t_employee as emp inner join t_employee as mgr
on emp.mid = mgr.eid;

3、查询所有员工姓名及其领导姓名

select emp.ename,mgr.ename
from t_employee as emp left join t_employee as mgr
on emp.mid = mgr.eid;

select 的 5 个子句

where 条件查询

从原表中的记录中进行筛选。

group by 分组查询

很多情况下，用户都需要进行一些汇总操作，比如统计整个公司的人数或者统计每一个部门的人数等。

聚合函数

• AVG(【DISTINCT】 expr) 返回 expr 的平均值
• COUNT（【DISTINCT】 expr）返回 expr 的非 NULL 值的数目
• MIN(【DISTINCT】 expr)返回 expr 的最小值
• MAX(【DISTINCT】 expr)返回 expr 的最大值
• SUM（【DISTINCT】 expr）返回 expr 的总和

练习：

1、统计公司人员的平均底薪

select avg(distinct(basic_salary)) from t_salary;

2、统计员工总人数

select count(*) from t_employee;#count(*)统计的是记录数

3、统计员工表的员工所在部门数

select count(dept_id) from t_employee;#结果7 统计的是非 NULL 值
select count(distinct dept_id) from t_employee;#结果2 统计的是非 NULL 值，并且去重

4、查询最低基本工资值

select min(basic_salary) from t_salary;

5、查询最高基本工资值

select max(basic_salary) from t_salary;

6、查询最高基本工资值

select max(basic_salary) from t_salary;

7、查询最高基本工资与最低基本工资的差值

select max(basic_salary)-min(basic_salary) from t_salary;

8、查询基本工资总和

select sum(basic_salary) from t_salary;

group by + 聚合函数

1、统计每个部门的人数

select dept_id,count(*) 
from t_employee  
group by dept_id

2、统计每个部门的平均基本工资

select dept_id,avg(t_salary.`basic_salary`) 
from t_employee,t_salary
where t_employee.`eid`=t_salary.`eid`
group by dept_id;

3、统计每个部门的年龄最大者

select dept_id,min(birthday) 
from t_employee group by dept_id;

4、统计每个部门基本工资最高者

select te.`dept_id`,max(ts.`basic_salary`)
from t_employee te,t_salary ts
where te.`eid` = ts.`eid`
group by te.`dept_id`;

5、统计每个部门基本工资之和

select te.`dept_id`,sum(ts.`basic_salary`)
from t_employee te,t_salary ts
where te.`eid` = ts.`eid`
group by te.`dept_id`;

注意：

用 count(*)、count(1)，谁好呢?

其实,对于 myisam 引擎的表，没有区别的。 这种引擎内部有一计数器在维护着行数。Innodb 的表，用 count(*)直接读行数，效率很低，因为 innodb 真的要去数一遍。

关于 mysql 的 group by 的特殊：

在 SELECT 列表中所有未包含在组函数中的列都应该是包含在 GROUP BY 子句中的，换句话说，SELECT 列表
中最好不要出现 GROUP BY 子句中没有的列。

having 筛选

having 与 where 类似，可筛选数据。

having 与 where 不同点

• where 针对表中的列发挥作用，查询数据；having 针对查询结果中的列发挥作用，筛选数据；
• where 后面不能写分组函数，而 having 后面可以使用分组函数；
• having 只用于 group by 分组统计语句。

1、按照部门统计员工人数，仅显示部门人数少于 3 人

select dept_id,count(*) as c
from t_employee
group by dept_id
having c<3

2、查询每个部门的平均工资，并且仅显示平均工资高于 10000

select emp.dept_id,avg(s.basic_salary ) as avg_salary
from t_employee as emp,t_salary as s
where emp.eid = s.eid and dept_id is not null
group by emp.dept_id
having avg_salary >10000;

order by 排序

按一个或多个字段对查询结果进行排序。

用法：order by col1,col2,col3…
说明：先按 col1 排序如果 col1 相同就按照 col2 排序，依次类推col1，col2，col3 可以是 select 后面的字段也可以不是

• 默认是升序，也可以在字段后面加 asc 显示说明是升序，desc 为降序
  例如：

order by click_count desc;

如果两个字段排序不一样
例如：

order by 字段 1 asc ,字段 2 desc;

• order by 后面除了跟 1 个或多个字段，还可以写表达式，函数，别名等

1、查询员工基本工资，按照基本工资升序排列，如果工资相同，按照 eid 升序排列

select t_employee.eid,basic_salary from t_employee inner join t_salary
on t_employee.eid = t_salary.eid
order by basic_salary,eid;

2、查询员工基本工资，按照基本工资降序排列，如果工资相同，按照 eid 排列

select t_employee.eid,basic_salary from t_employee inner join t_salary
on t_employee.eid = t_salary.eid
order by basic_salary desc,eid;

3、统计每个部门的平均基本工资，并按照平均工资降序排列（表达式）

select emp.dept_id,avg(s.basic_salary)
from t_employee as emp,t_salary as s
where emp.eid = s.eid
group by emp.dept_id
order by avg(s.basic_salary) desc;

limit 分页

limit m,n

m 表示从下标为 m 的记录开始查询，第一条记录下标为 0，n 表示取出 n 条出来，如果从 m 开始不够 n 条了，就有几条取几条。

m=(page-1)*n，（page 页码，n 表示每页显示的条数）

• 如果第一页 limit 0,n
• 如果第二页 limit n,n
  依次类推，得出公式 limit (page-1)*n , n

练习：

1、查询员工信息，每页显示 5 条，第二页

select * from t_employee limit 5,5;

2、统计每个部门的平均基本工资，并显示前三名

select emp.dept_id,avg(s.basic_salary)
from t_employee as emp,t_salary as s
where emp.eid = s.eid
group by emp.dept_id
order by avg(s.basic_salary) desc
limit 0,3;

子查询

某些情况下，当进行一个查询时，需要的条件或数据要用另外一个 select 语句的结果，这个时候，就要用到子查询。

where 型子查询

where 型子查询即把内层 sql 语句查询的结果作为外层 sql 查询的条件。

• 子查询要包含在括号内。
• 建议将子查询放在比较条件的右侧。
• 单行操作符对应单行子查询，多行操作符对应多行子查询。
• 单行操作符 右边子查询必须返回的是单个值，单行比较运算符（=,>,>=,<,<=,<>）
• 多行操作符 右边子查询可以返回多行，但必须是单列，ALL, ANY,IN 其中，ALL 和 ANY 运算符必须与单行比较运算符（=,>,>=,<,<=,<>）结合使用。

IN：等于任何一个。

==ALL：和子查询返回的所有值比较。==例如：sal>ALL(1,2,3)等价于 sal>1 && sal>2 && sal>3，即大于所有。

==ANY：和子查询返回的任意一个值比较。==例如：sal>ANY(1,2,3)等价于 sal>1 or sal>2 or sal>3，即大于任意一个就可以。

EXISTS：判断子查询是否有返回结果（不关心具体行数和内容），如果返回则为 TRUE，否则为 FALSE

练习：

1、查询比“孙红雷”的工资高的员工编号

select * from t_salary
where basic_salary > 
(select basic_salary 
from t_employee inner join t_salary 
on t_employee.eid=t_salary.eid 
where t_employee.ename='孙红雷');

2、查询和孙红雷，Angelababy在同一个部门的员工

select * from t_employee
where dept_id in(select dept_id from t_employee where ename='孙红雷' or ename = 'Angelababy');

select * from t_employee
where dept_id = any(select dept_id from t_employee where ename='孙红雷' or ename = 'Angelababy');

3、查询全公司工资最高的员工编号，基本工资

select eid,basic_salary from t_salary
where basic_salary = (select max(basic_salary) from t_salary);

select eid,basic_salary from t_salary
where basic_salary >= all(select basic_salary from t_salary);

from 型子查

from 型子查询即把内层 sql 语句查询的结果作为临时表供外层 sql 语句再次查询。

思路：两表内关联再与子表内关联

select t_employee.`eid`,t_salary.`basic_salary`
from t_employee inner join t_salary 
inner join (select te.`dept_id`,avg(ts.`basic_salary` ) as avg_salary from t_employee te,t_salary ts where te.`dept_id` =ts.`eid` group by te.`dept_id`) as temp 
on t_employee.`eid` = t_salary.`eid`  
and t_employee.`dept_id` = temp.`dept_id`
where t_salary.`basic_salary`>temp.`avg_salary`

exists 型子查询

1、查询部门信息，该部门必须有员工

判断子查询是否有返回结果

select * from t_department
where exists (select * from t_employee where t_employee.dept_id = t_department.did);

单行函数

MySQL 数据库提供了很多函数包括：

• 数学函数；
• 字符串函数；
• 日期和时间函数；
• 条件判断函数；流程控制函数；
• 系统信息函数；
• 加密函数；
• 格式化