SQL数据库（分页查询、DCL 数据控制语言、通用函数、约束、多表查询）

分页查询

语法

select 字段列表 from 表名 limit 起始索引,查询记录数（每页显示的条数）

注意

起始索引 从0开始， 起始索引 = （查询的页码-1）* 每页显示的记录数
1–5—0
2-5–5
3–5—10
分页查询数据数据库的方言，不同的数据库有不同的实现，mysql中用的就是limit
如果查询的是第一页数据，起始索引可以省略 直接简写为 limit 10;

SELECT * FROM emp LIMIT 10;
-- 查询第2页的数据 每页显示10条记录
SELECT * FROM emp LIMIT 10,10;

练习

-- 查询年龄为20，21，22，23岁的员工信息
SELECT * from emp where age in(20,21,22,23);
-- 查询性别为男，并且年龄在20-40岁（含）以内的姓名为三个字的员工信息
select * from emp where age BETWEEN 20 and 40 and gender = '男' and name like
'___';
-- 统计员工表中，年龄小于60岁的男性员工和女性员工的人数
select gender, COUNT(*) from emp where age < 60 GROUP BY gender;
-- 查询所有年龄小于等于35岁员工的姓名和年龄，并且查询结果按照年龄升序排序，如果年龄相同，则按照入职
时间的降序排序
select `name`,age from emp where age <= 35 ORDER BY age ,entrydate desc;
-- 查询性别为男，且年龄在20-40之间的前5个员工信息，对查询结果进行排序按照年龄升序排序，如果年龄相
同，则按照入职时间的降序排序，
select * from emp where gender ='男' and age>=20 AND age <= 40 order by age
,entrydate DESC LIMIT 5;

DCL数据库控制语言

作用：用来管理数据库用户，控制数据库的访问权限

管理用户

1. 查询所有用户

 -- 查询mysql库中的user表
select * FROM mysql.user;

host表示当前用户访问的主机，localhost 仅代表当前能够在本机访问，是不可以远程访问的，User表示访
问数据库的用户名，在mysql中需要通过host和user唯一的去标识一个用户

2. 创建用户

create user '用户名'@'主机名' IDENTIFIED BY '密码';
CREATE USER 'sxjzit'@'localhost' IDENTIFIED BY '123456';

3. 修改用户密码

-- 修改用户的密码
ALTER USER 'sxjzit'@'localhost'IDENTIFIED WITH mysql_native_password BY'654321';

4. 删除用户

-- 删除用户
drop USER 'sxj

注意：
在mysql中需要通过用户名@主机名的方式，唯一的标识一个用户.
主机名可以使用通配符%。

-- create 用户admin，可以在任意主机访问该数据库密码也是admin
create user 'admin'@'%'IDENTIFIED by 'admin';

权限控制

查询权限

1 查询权限

-- 查询某一个用户的权限
SHOW GRANTS FOR 'root'@'localhost';

授予权限

grant 权限列表 on 数据库名.表名 TO 用户名'@'主机名';

撤销权限

REVOKE 权限列表 on 数据库名.表名 from 用户名'@'主机名';

注意：
多个权限之间 使用逗号分隔
授权时 可以使用通配符* 表示数据库名和表名 代表所有

-- 查询admin@% 用户的权限
SHOW GRANTS FOR 'admin'@'%';
-- 授予admin@% 用户 emp库所有表的所有操作权限
grant ALL ON emp.* TO 'admin'@'%';
-- 撤销admin@% 用户 emp库的所有权限
REVOKE ALL on emp.* FROM 'admin'@'%';

通用函数

函数是指一段可以直接被另一段程序调用的程序或代码。 mysql中 函数主要分为一下四类：字符串函数 数值函数 日期函数 流程函数

字符串函数

函数名	功能
CONCAT(s1,s2,s3…sn)	字符串拼接，将s1，s2，。。。sn拼接成一个字符串
LOWER（str）	将字符串str全部转换为小写
UPPER（str）	将字符串str全部转换为大写
LPAD(str,n,pad)	左填充，用字符串pad对str的左边进行填充，达到n个字符长度
RPAD(str,n,pad)	右填充，用字符串pad对str的右边进行填充，达到n个字符长度
TRIM(str)	去掉字符串的头部和尾部的空格
SUBSTRING（str,start,len）	返回字符串str从strat位置开始的len个长度的字符串

-- 字符串拼接
select CONCAT('hello','Mysql');
-- 全部转换为小写
select lower('MYSQL');
-- 全部转换为大写
select UPPER('mysql');
-- 左填充
select LPAD('01',5,'0');
-- 右填充
select RPAD('01',5,'_');
-- 去除空格
select TRIM(' Hello mysql ');
-- 截取子字符串 索引从1开始
select SUBSTRING('Hello mysql',1,5);




-- 由于业务变更，企业员工的工号，统一为5位数，目前不足5为的全部在前面补0.
update emp SET workno = lpad(workno,5,'0');

数值函数

函数名	功能
CEIL（x）	向上取整
FLOOP（x）	向下取整
MOD（x,y）	返回x/y的模
RAND （）	返回0-1内的随机数
ROUND（x,y）	求参数x的四舍五入的值，保留y位小数

-- CEIL(x) 向上取整
SELECT ceil(1.1);
-- floor 向下取整
select FLOOR(1.9);
-- MOD(N,M) 去模
select MOD(7,4);
-- RAND() 随机数
select RAND();
-- RAND() 四舍五入
select ROUND(2.325,2);

案例：通过数据库的函数，生成一个六位数的随机验证码
通过rand可以生成一个0-1的随机数 ，可以在的基础上乘以100000 然后舍弃小数部分，如果长度不足6位，
补0

-- 案例：通过数据库的函数，生成一个六位数的随机验证码
-- 通过rand可以生成一个0-1的随机数 ，可以在的基础上乘以100000 然后舍弃小数部分，如果长度不足6
位，补0
SELECT lpad(ROUND(RAND() * 100000,0),6,0);

日期函数

函数	功能
CURDATE()	返回当前日期
CURTIME()	返回当前时间
NOW（）	返回当前日期和时间
YEAR（date）	返回指定日期的年份
MONTH（date）	返回指定日期的月份
DAY(date)	返回指定日期的天
DATE_ADD(data,INTERVAL expr unit))	返回一个日期/时间加上一个时间间隔expr后的时间值
DEEDIFF（date1，date2）	返回起始时间date1和结束时间date2之间的天数

-- | CURDATE() | 返回当前日期 |
select CURDATE();
-- | CURTIME() | 返回当前时间
SELECT CURTIME();
-- | NOW（） | 返回当前日期和时间
SELECT NOW();
-- | YEAR（date） | 返回指定日期的年份
select YEAR(now());
-- | MONTH（date） | 返回指定日期的月份
select MONTH(now());
-- | DAY(date) | 返回指定日期的天
select DAY(now()) ;
-- | DATE_ADD(data,expr) | 返回一个日期/时间加上一个时间间隔expr后的时间值 |
select DATE_ADD(now(),INTERVAL 70 DAY);
-- | DEEDIFF（date1，date2） | 返回起始时间date1和结束时间date2之间的天数 |
select DATEDIFF(now(),'2021-10-01');
-- 查询所有员工的入职天数 并根据入职天数倒序排序
select `name`,DATEDIFF(CURDATE(),entrydate) AS 'entrydays' from emp ORDER BY
entrydays desc;
-- 查询world库中的country表中 每个国家的独立天数。

流程函数

函数	功能
if(value,t,f)	如果value是真，返回t，否则返回f
ifnull(value1，value2)	如果value1不为空，则返回value1，否则返回value2
case when[var1] then [res1] … else [default] end	如果var1 是真，返回res1，否则返回default
case[expr] when[var1] then [res1] … else[default] end	如果expr等于var1 返回res1 否则返回default

举个栗子：

select IF(false,'OK','ERROR');
-- IFNULL(expr1,expr2)
select IFNULL('ok','default');
select IFNULL(null,'default');
select IFNULL('','default');
-- 查询emp表中员工的姓名和工作地址（北京/上海----一线城市 其他---二线城市）
select `name`,(CASE workaddress
WHEN '北京' THEN'一线城市'
WHEN '上海' THEN '一线城市'
ELSE '二线城市'
END)as '工作地址'
from emp;

create table score(
id int ,
`name` VARCHAR(20),
math int,
english int,
chinese int
);
insert into score VALUES(1,'TOM', 67,88,95),(2,'Rose', 23,66,99),
(3,'jack',56,98,76);
-- 请查询每个学生的成绩，分别判断每门课的成绩状况，>=85 优秀 >=60 及格 <60 不及格
select id,`name` ,
(case when math >= 85 then '优秀' when math >= 60 THEN '及格' ELSE '不及格' end) AS '数
学' ,
(case when english >= 85 then '优秀' when english >= 60 THEN '及格' ELSE '不及格' end)
AS '英语' ,
(case when chinese >= 85 then '优秀' when chinese >= 60 THEN '及格' ELSE '不及格' end)
AS '语文'
FROM SCORE;

约束

约束是作用与表中字段的规则，限制存储在表中的数据。
目的：保证数据库中的数据的完整性，正确性和有效性。

约束时作用在字段上的，可以在创建表/修改表的时候添加约束
案例需求：根据需求，完成表结构的创建 需求如下：

create TABLE tb_user(
id int auto_increment PRIMARY KEY ,
`name` VARCHAR(10) NOT NULL UNIQUE,
age int CHECK(age > 0 && age <=120),
`status` char(1) DEFAULT('1'),
gender char(1)
);

insert into tb_user(name,age,status,gender) VALUES('tom',19,'1','男');
insert into tb_user(name,age,status,gender) VALUES('lucy',25,'0','女');
insert into tb_user(name,age,gender) VALUES('admin',25,'女');

外键约束

让两张表之间建立连接，从而保证数据的一致性和完整性

-- 创建一个部门表
CREATE TABLE dept(
id int auto_increment PRIMARY KEY,
`name` VARCHAR(50) not null
);
insert into dept (name) VALUES('研发部'),('市场部'),('财务部'),('人事部'),('后勤部');
select * from dept;

添加外键

create table 表名(
字段名 数据类型,
[constraint 外键名称] foreign key (外键字段) References 主表(主表的列明)
)

alter table 表名 add constraint 外键名称 foreign key(外键字段) References 主表(主表的
列明)

给emp增加外键约束。

ALTER TABLE emp ADD CONSTRAINT fk_emp_dept_id FOREIGN key (deptno) REFERENCES
dept(id);

当我们在emp表中执行删除的时候 是可以正常删除员工的，但是在部门表中，删除有员工存在的部门的时候是不能删除的。

删除和更新行为

添加外键之后，在删除父表的时候 产生约束行为，我们就称为删除/更新行为。

行为	说明
no action	当在父表中删除/更新对应的记录的时候，首先检查记录是否有对应的外键，如果有，则不允许删除/更新 默认行为
restrict	当在父表中删除/更新对应的记录的时候，首先检查记录是否有对应的外键，如果有，则不允许删除/更新 默认行为
cascade	当在父表中删除/更新对应的记录的时候，首先检查记录是否有对应的外键，如果有，也会删除和更新外键在子表中的记录
set null	当在父表中删除/更新对应的记录的时候 ，首先检查记录是否有对应的外键，如果有，则设置子表中该外键值为null
set default	父表有变更时，子表将外键列设置成一个默认的值

具体的语法：

alter table 表名 add constraint 外键名称 foreign key(外键字段) References 主表(主表的列
明)on update cascade on delete cascade;

当删除/更新行为的策略为cascade时，删除主表dept表中部门，在emp表中对应的部门下的员工也会被删
除。
当删除/更新行为的策略为cascade时，修改主表dept表部门id的时候，在emp表中对应的部门下的员工的部
门编号也会更新为新的部门编号。

删除外键

语法

alter table 表名 drop foreign key 外键名称。

-- 删除emp表的外键fk_emp_dept_id
alter TABLE emp drop foreign key fk_emp_dept_id;

多表查询

表与表之间的关系：

• 一对多（多对一）
• 多对多
• 一对一

一对多

部门和员工的关系
关系：一个部门对应多个员工，一个员工对应一个部门
实现：在多的一方建立外键，指向一的一方的主键

多对多

学生和课程之间的关系
关系：一个学生可以修多门课程。一门课程可以被多个学生选择
实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

create TABLE student(
id int auto_increment PRIMARY KEY,
`name` VARCHAR(10),
`NO` VARCHAR(10)
); -- 学生表
CREATE TABLE course(
id int auto_increment PRIMARY KEY,
`name` VARCHAR(10)
);-- 课程表
create TABLE student_course(
id int auto_increment PRIMARY KEY,
studentid int not null,
courseid int not null,
constraint fk_course_id FOREIGN KEY(courseid) REFERENCES course(id),
constraint fk_student_id FOREIGN KEY(studentid) REFERENCES student(id)
);-- 中间表 维护学生和课程之间的对应关系

一对一

用户和用户详情
关系：一对一关系 多用于单表拆分，将一张表的基础字段放在一张表中，其他的详情字段放在另一张表中，
提升查询的效率
实现：在任意一方加入外键，关联另一方的主键，并设置外键为唯一的。

多表关联查询

在进行多表关联查询之前 先要准备数据

-- 准备数据
create table dept(
id int auto_increment comment 'ID' primary key,
name varchar(50) not null comment '部门名称'
)comment '部门表';
create table emp(
id int auto_increment comment 'ID' primary key,
name varchar(50) not null comment '姓名',
age int comment '年龄',
job varchar(20) comment '职位',
salary int comment '薪资',
entrydate date comment '入职时间',
managerid int comment '直属领导ID',
dept_id int comment '部门ID'
)comment '员工表';
-- 添加外键
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references
dept(id);
INSERT INTO dept (id, name) VALUES (1, '研发部'), (2, '市场部'),(3, '财务部'), (4, '销售
部'), (5, '总经办'), (6, '人事部');
INSERT INTO emp (id, name, age, job,salary, entrydate, managerid, dept_id) VALUES
(1, '金庸', 66, '总裁',20000, '2000-01-01', null,5),
(2, '张无忌', 20, '项目经理',12500, '2005-12-05', 1,1),
(3, '杨逍', 33, '开发', 8400,'2000-11-03', 2,1),
(4, '韦一笑', 48, '开发',11000, '2002-02-05', 2,1),
(5, '常遇春', 43, '开发',10500, '2004-09-07', 3,1),
(6, '小昭', 19, '程序员鼓励师',6600, '2004-10-12', 2,1),
(7, '灭绝', 60, '财务总监',8500, '2002-09-12', 1,3),
(8, '周芷若', 19, '会计',48000, '2006-06-02', 7,3),
(9, '丁敏君', 23, '出纳',5250, '2009-05-13', 7,3),
(10, '赵敏', 20, '市场部总监',12500, '2004-10-12', 1,2),
(11, '鹿杖客', 56, '职员',3750, '2006-10-03', 10,2),
(12, '鹤笔翁', 19, '职员',3750, '2007-05-09', 10,2),
(13, '方东白', 19, '职员',5500, '2009-02-12', 10,2),
(14, '张三丰', 88, '销售总监',14000, '2004-10-12', 1,4),
(15, '俞莲舟', 38, '销售',4600, '2004-10-12', 14,4),
(16, '宋远桥', 40, '销售',4600, '2004-10-12', 14,4),
(17, '陈友谅', 42, null,2000, '2011-10-12', 1,null);

执行多张表的查询：

select * from emp,dept; -- 17 * 6 = 102

这102条记录时emp中的17条记录和dept表中的6条记录的组合 ，这种组合现象称之为 笛卡尔积。
笛卡尔积：数学中，指的是集合A和集合B的所有的组合情况。
而在多表查询中，我们需要做的是消除笛卡尔积，只保留两站表关联部分你的数据。
在sql中，如何消除笛卡尔积呢？只需要给多表查询加上一个连接条件（外键）即可。

select * from emp ,dept where emp.dept_id = dept.id;

查询结果为16条记录，原因在于id=17的记录 dpet_id = null 所以在多表查询时，根据连接条件并没有查询
到他的关联的部门。

多表查询的分类

连接查询：

• 内连接：相当于查询A，B交集部分的数据。
• 外连接：
• 左外连接：查询的 时候 左表为主表，结果集中包含了左表的全部数据，以及两张表之间的交集部分。如果在右表（从表）中没有与主表关联的数据，则以null填充。
• 右外连接：查询的 时候 右表为主表，结果集中包含了右表的全部数据，以及两张表之间的交集部分。如果在左表（从表）中没有与主表关联的数据，则以null填充。
• 自连接：当前表与自身的连接查询，自连接必须使用表的别名。 子查询

练习

创建personl数据库

CREATE DATABASE `personnel` DEFAULT CHARACTER SET utf8mb4;
USE `personnel`;
-- ----------------------------
-- Table structure for dept
-- ----------------------------
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
`deptno` int(4) NOT NULL,
`dname` varchar(20) DEFAULT NULL,
`loc` varchar(50) DEFAULT NULL,
PRIMARY KEY (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ----------------------------
-- Records of dept
-- ----------------------------
INSERT INTO `dept` VALUES ('10', 'ACCOUNTING', 'NEW YORK');
INSERT INTO `dept` VALUES ('20', 'RESEARCH', 'DALLAS');
INSERT INTO `dept` VALUES ('30', 'SALES', 'CHICAGO');
INSERT INTO `dept` VALUES ('40', 'OPERATIONS', 'BOSTON');
-- ----------------------------
-- Table structure for emp
-- ----------------------------
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
`empno` int(4) NOT NULL,
`ename` varchar(20) DEFAULT NULL,
`job` varchar(20) DEFAULT NULL,
`mgr` varchar(20) DEFAULT NULL,
`hiredate` date DEFAULT NULL,
`sal` double(7,2) DEFAULT NULL,
`comm` double(7,2) DEFAULT NULL,
`deptno` int(4) DEFAULT NULL,
PRIMARY KEY (`empno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ----------------------------
-- Records of emp
-- ----------------------------
INSERT INTO `emp` VALUES ('7369', 'SMITH', 'CLERK', '7902', '1980-12-17', '800.00',
null, '20');
INSERT INTO `emp` VALUES ('7499', 'ALLEN', 'SALESMAN', '7698', '1981-02-20',
'1600.00', '300.00', '30');
INSERT INTO `emp` VALUES ('7521', 'WARD', 'SALESMAN', '7698', '1981-02-22',
'1250.00', '500.00', '30');
INSERT INTO `emp` VALUES ('7566', 'JONES', 'MANAGER', '7839', '1981-04-02',
'2975.00', null, '20');
INSERT INTO `emp` VALUES ('7654', 'MARTIN', 'SALESMAN', '7698', '1981-09-28',
'1250.00', '1400.00', '30');
INSERT INTO `emp` VALUES ('7698', 'BLAKE', 'MANAGER', '7839', '1981-05-01',
'2850.00', null, '30');
INSERT INTO `emp` VALUES ('7782', 'CLARK', 'MANAGER', '7839', '1981-06-09',
'2450.00', null, '10');
INSERT INTO `emp` VALUES ('7788', 'SCOTT', 'ANALYST', '7566', '1987-06-13',
'3000.00', null, '20');
INSERT INTO `emp` VALUES ('7839', 'KING', 'PRESIDENT', null, '1981-11-17',
'5000.00', null, '10');
INSERT INTO `emp` VALUES ('7844', 'TURNER', 'SALESMAN', '7698', '1981-09-08',
'1500.00', '0.00', '30');
INSERT INTO `emp` VALUES ('7876', 'ADAMS', 'CLERK', '7788', '1987-06-13', '1100.00',
null, '20');
INSERT INTO `emp` VALUES ('7900', 'JAMES', 'CLERK', '7698', '1981-12-03', '950.00',
null, '30');
INSERT INTO `emp` VALUES ('7902', 'FORD', 'ANALYST', '7566', '1981-12-03',
'3000.00', null, '20');
INSERT INTO `emp` VALUES ('7934', 'MILLER', 'CLERK', '7782', '1982-01-23',
'1300.00', null, '10');
-- ----------------------------
-- Table structure for salgrade
-- ----------------------------
DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade` (
`grade` int(4) DEFAULT NULL,
`losal` double(7,2) DEFAULT NULL,
`hisal` double(7,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ----------------------------
-- Records of salgrade
-- ----------------------------
INSERT INTO `salgrade` VALUES ('1', '700.00', '1200.00');
INSERT INTO `salgrade` VALUES ('2', '1201.00', '1400.00');
INSERT INTO `salgrade` VALUES ('3', '1401.00', '2000.00');
INSERT INTO `salgrade` VALUES ('4', '2001.00', '3000.00');
INSERT INTO `salgrade` VALUES ('5', '3001.00', '9999.00');

完成下列问题

1. 根据 emp 表中的信息查询所有员工的工作 job 信息；
2. 根据 emp 表中的信息查询所有员工的姓名 ename 和工资 sal 翻倍后的金额；
3. 根据 emp 表中的信息查询所有员工的工号 empno ，姓名 ename 和部门编号 deptno 三个属性，所
   查询的结果中 ename 和 deptno 要求分别显示成 姓名和部门编号。
4. 根据 emp 表中的信息查询不属于部门编号 deptno 为 20 的所有员工信息
5. 根据 emp 表中的信息查询所有工资 sal 大于 2000 的员工的全部信息
6. 根据 emp 表中的信息查询所有职位为销售的员工信息，只取三条
7. 根据 emp 表中的信息查询所有员工信息并按部门编号升序工资降序排列
8. 根据 emp 表中的信息查询部分总人数大于 4 人的部门编号和部门总人数
9. 根据 emp 表中的信息使用 LIMIT 查询部门编号为 10 且工资最高的员工编号、姓名、部门编号、职位
   信息

答案

 -- 1. 根据 emp 表中的信息查询所有员工的工作 job 信息；
 select empno,`ename`,job from emp;
-- 2. 根据 emp 表中的信息查询所有员工的姓名 ename 和工资 sal 翻倍后的金额；
SELECT ename, sal * 2 as doubled_sal FROM emp;
-- 3. 根据 emp 表中的信息查询所有员工的工号 empno ，姓名 ename 和部门编号 deptno 三个属性，所查询的结果中 ename 和 deptno 要求分别显示成 姓名和部门编号。
select empno,`ename` AS 姓名,deptno as 部门编号 from emp;
-- 4. 根据 emp 表中的信息查询不属于部门编号 deptno 为 20 的所有员工信息
select * from emp WHERE deptno !=20;
-- 5. 根据 emp 表中的信息查询所有工资 sal 大于 2000 的员工的全部信息
select * from emp WHERE sal>2000;
-- 6. 根据 emp 表中的信息查询所有职位为销售的员工信息，只取三条
SELECT * FROM emp WHERE job = 'SALESMAN' LIMIT 3;
-- 7. 根据 emp 表中的信息查询所有员工信息并按部门编号升序工资降序排列
SELECT * from emp order by deptno DESC;
-- 8. 根据 emp 表中的信息查询部分总人数大于 4 人的部门编号和部门总人数
SELECT deptno, COUNT(*) AS total
FROM emp
GROUP BY deptno
HAVING COUNT(*) > 4;
-- 9. 根据 emp 表中的信息使用 LIMIT 查询部门编号为 10 且工资最高的员工编号、姓名、部门编号、职位信息
SELECT empno, ename, deptno, job
FROM emp
WHERE deptno = 10
ORDER BY sal DESC
LIMIT 1;