MySQL（分组，分页，约束，级联删除级联更新，多表查询）_级联查询 分页问题

1.分组查询

（1）概述

• 分组查询：group by ,一般配合聚合函数使用查出的数据才有意义。
• 查询的字段：1.分组字段本身 2.聚合函数

（2）案例演示

案例演示所用的表

CREATE TABLE `emp` (
  `empno` int(11) DEFAULT NULL,
  `ename` varchar(50) DEFAULT NULL,
  `job` varchar(50) DEFAULT NULL,
  `mgr` int(11) DEFAULT NULL,
  `hiredate` date DEFAULT NULL,
  `sal` decimal(7,2) DEFAULT NULL,
  `comm` decimal(7,2) DEFAULT NULL,
  `deptno` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1
2
3
4
5
6
7
8
9
10

-- 分组emp表中
-- 查询每个部门有多少人
SELECT deptno AS 部门编号,COUNT(*) AS 部门人数 FROM emp GROUP BY deptno;
-- 查询每个部门最高的工资
SELECT deptno AS 部门编号,MAX(sal) AS 最高工资 FROM emp GROUP BY deptno
-- 查询每个部门工资大于1500的人数
SELECT deptno AS 部门编号 ,COUNT(*) AS 人数 FROM emp WHERE sal>1500 GROUP BY deptno;
-- 各部门平均工资大于2000的部门 having可以对查询后的结果进行排序
SELECT deptno AS 部门编号,AVG(sal) AS 平均工资 FROM emp GROUP BY deptno HAVING AVG(sal)>2000;
SELECT deptno AS 部门编号,AVG(sal) AS pj FROM emp GROUP BY deptno HAVING pj>2000;
-- 查询 各个部门  员工工资大于1500 的平均工资  并且平均工资 大于2000的部门
SELECT deptno AS 部门编号,AVG(sal) AS pj FROM emp WHERE sal>1500 GROUP BY deptno HAVING pj>2000;
1
2
3
4
5
6
7
8
9
10
11
12

2.分页查询

（1）语法

limit 起始索引,每页的索引数
起始索引=（页码-1）*每页的条数 
1
2

（2）案例演示

-- 分为三页 每页三条数据
SELECT *FROM emp LIMIT 0,3;
SELECT *FROM emp LIMIT 3,3;
SELECT *FROM emp LIMIT 6,3;
1
2
3
4

3.约束

（1）概述

数据库中的约束，对某些字段，添加一些限定，来限制一些数据操作。为了保证数据的完整性和有效性。主键约束 primary key

（2）常用约束

• 非空约束 not null
• 唯一约束UNIQUE
• 自增涨约束 AUTO_INCREMENT
• 非负数约束 UNSIGNED
• 外键约束

（3）案例演示

-- 添加主键约束
CREATE TABLE test1(
     tid INT PRIMARY KEY,-- 创建的时候添加主键
     tname VARCHAR(5) NOT NULL -- 非空约束
);
CREATE TABLE test2(
     tid INT,
     tname VARCHAR(5) UNIQUE -- 唯一约束不允许重复值（但null值不起作用）
);
ALTER TABLE test2 ADD PRIMARY KEY(tid);-- 修改表来添加主键

-- 自增长约束 般给整数型的字段添加 比如int类型 还得配合主键一块来用，叫做主键自增长
CREATE TABLE test3(
      tid INT AUTO_INCREMENT PRIMARY KEY,-- 主键自增长
      tname VARCHAR(5) UNIQUE NOT NULL 
);      
-- 主键自增长，你不给值，或者给成null 这个主键会自己自增默认从1开始
INSERT INTO test3 VALUES(NULL,'张三');
INSERT INTO test3 VALUES(NULL,'lisi');     
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

4.级联删除和级联更新

（1）概述

在我们开发中，把一方的这张表，叫做主表或者说一表。把多方这张表叫做 从表或者说多表，那么为了描述一表和多表之间的一个关联关系，通常会在多表一方。添加一个外键，去关联主表一方的主键，那么两张表之间就会有相互的制衡。

CREATE TABLE zhu(
      sid INT PRIMARY KEY AUTO_INCREMENT,
      sname VARCHAR(5),
      sex CHAR(2)
 )      
CREATE TABLE cong(
      cid INT PRIMARY KEY AUTO_INCREMENT,
      score INT,
      ssid INT ,
      FOREIGN KEY (ssid) REFERENCES zhu(sid)
) 
1
2
3
4
5
6
7
8
9
10
11

我们两个表之间有个约束之后，删除一个表中的数据或者修改就会非常麻烦，所以就有了级联删除和级联更新。

（2）案例演示

CREATE TABLE zhu(
      sid INT PRIMARY KEY AUTO_INCREMENT,
      sname VARCHAR(5),
      sex CHAR(2)
 )      
CREATE TABLE cong(
      cid INT PRIMARY KEY AUTO_INCREMENT,
      score INT,
      ssid INT 
)     
ALTER TABLE cong ADD FOREIGN KEY (ssid) REFERENCES zhu(sid) ON DELETE CASCADE ON UPDATE CASCADE;
-- 删除或修改主表中的数据之后，从表中的数据也会删除或修改 
1
2
3
4
5
6
7
8
9
10
11
12

5.多表查询

多对多关系的处理

我们有一个实例：一个订单可以包含多个商品，而一个商品也可以属于多个订单，很明显订单表和商品表之间是多对多的关系。

CREATE TABLE USER(
     uid INT PRIMARY KEY AUTO_INCREMENT,
     uname VARCHAR(6)
);
CREATE TABLE orders(
     oid INT PRIMARY KEY AUTO_INCREMENT,
     oprice INT,
     ouid INT -- 外键
);
ALTER TABLE orders ADD FOREIGN KEY(ouid) REFERENCES USER(uid);-- 用户表与订单表建立联系

CREATE TABLE product(
     pid INT PRIMARY KEY AUTO_INCREMENT,
     pname VARCHAR(10),
     pprice DOUBLE
);
 -- 使用一个中间表 让订单表与商品表时间的关系变为一对多
 CREATE TABLE middle(
      oid INT,-- 外键
      pid INT -- 外键
 )     
 ALTER TABLE middle ADD FOREIGN KEY(oid) REFERENCES orders(oid);
 ALTER TABLE middle ADD FOREIGN KEY(pid) REFERENCES product(pid);     
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

（1）内连接查询

-- 隐式内连接
-- 查询用户信息和订单信息
SELECT u.uid,u.uname,o.oid,o.oprice,o.ouid FROM USER u,orders o WHERE u.uid=o.`ouid`; -- 给表起别名 as可以省略不写
-- 显示内连接
-- 查询用户的所有信息
SELECT u.uid,u.uname,o.oid,o.oprice,p.pid,p.pname,p.pprice FROM USER u INNER JOIN orders o ON
 u.`uid`=o.`ouid`INNER JOIN middle m ON  o.`oid`=m.`oid` INNER JOIN product p ON p.`pid`=m.`pid`;
1
2
3
4
5
6
7

内连接的特点：不符合条件的数据是不展示的。

（2）外连接查询

要查询所有人的订单，要求所有用户展示出来，如果这个用户，没有对应的订单，那么定义就以null展示

 --  左外连接
 -- left 左边的表中的数据是全部要展示的，那么在left右边的表中没有找到对应信息，就以null展示
SELECT u.uid,u.uname,o.oid,o.oprice,o.ouid FROM USER u LEFT OUTER JOIN orders o ON 
o.`ouid`=u.`uid`;
-- 右外连接 
-- right右边的这张表的数据会全部展示出来，如果在左边的表中没有找到对应的就以null展示
SELECT u.uid,u.uname,o.oid,o.oprice,o.ouid FROM USER u RIGHT OUTER JOIN orders o ON 
o.`ouid`=u.`uid`;
1
2
3
4
5
6
7
8

（3）子查询

概述：有的时候我们的查询，他一步完成不了 ，需要几步来查询，我们就可以使用子查询，我们有一个主要的查询，这个主要的查询所需要的条件，来自另一个子查询。

-- 查询用户为张三的订单的详情
-- 第一步：在user表中查询出张三的id 第二步：拿着张三的id再去orders表中查询订单
SELECT orders.* FROM orders WHERE orders.`ouid`=(SELECT user.`uid` FROM USER WHERE
 user.`uname`='张三')
1
2
3
4

（4）自查询

你要查的信息，他都在一张表里面，我们可以把这张表看做两张表

-- 我要查询emp表中 员工姓名 所对应的 上司姓名
CREATE TABLE `emp` (
  `empno` INT(11) DEFAULT NULL,
  `ename` VARCHAR(50) DEFAULT NULL,
  `job` VARCHAR(50) DEFAULT NULL,
  `mgr` INT(11) DEFAULT NULL,
  `hiredate` DATE DEFAULT NULL,
  `sal` DECIMAL(7,2) DEFAULT NULL,
  `comm` DECIMAL(7,2) DEFAULT NULL,
  `deptno` INT(11) DEFAULT NULL
) 
-- 我们假设有两张表一张员工表,一张上司表,如果员工的上司号mgr=上司的员工号 就表示这个员工是另外一个员工的上司
SELECT e1.empno,e1.mgr,e1.ename,e2.empno,e2.ename FROM emp e1,emp e2 WHERE e1.mgr=e2.empno;
1
2
3
4
5
6
7
8
9
10
11
12
13