MySQL数据库（分组和分页，约束）_mysqlsql先分组还是先分页查询

作者：数据灵魂 | 2024-01-30 20:48:40

踩

mysqlsql先分组还是先分页查询

1.分组查询

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


-- 比如我按部分编号分组  比如有三个部门 然后我求每个部门的平均工资  那展示出来的数据应该有三条
-- 一般聚合函数会配合分组来用
 
SELECT deptno AS 部门编号,AVG(sal) AS 平均工资 FROM emp GROUP BY deptno;
 
 
-- 查询每个部门多少人
 
SELECT deptno AS 部门编号,COUNT(*) AS 部门人数 FROM emp GROUP BY deptno;
 
 
-- 找一个合理的字段来分组 根据性别来分 
 
-- 按照工资高低来分 大于1500的一组，小于1500的 一组
 
SELECT COUNT(*) AS 人数 FROM emp GROUP BY sal>=1500;
 
 
-- 1.例如查询 每个部门的部门编号 以及每个部门工资大于1500的人数
 
-- 我们在分组之前，也进行一个条件的筛选，符合条件的参与分组，不符合条件的不参与分组
 
SELECT deptno AS 部门编号,COUNT(*) AS 部门人数 FROM emp WHERE sal>1500 GROUP BY deptno;
 
-- 分组之后还可以进行排序
SELECT deptno AS 部门编号,COUNT(*) AS 部门人数 FROM emp WHERE sal>1500 GROUP BY deptno ORDER BY 部门人数;
 
 
-- 2.例如 我要查询 各个部门平均工资 大于2000 的部门
-- 我们需要对分组之后，产生的结果集进行再次筛选 使用 having
 
SELECT deptno AS 部门编号,AVG(sal) AS 平均工资 FROM emp GROUP BY deptno HAVING 平均工资>2000 ORDER BY 平均工资;
 
 
-- 3. 例如我要查询 各个部门  员工工资大于1500 的平均工资  并且平均工资 大于2000的部门
 
-- 分组之前也进行条件的筛选 员工工资大于1500 参与分组 分完组之后还要筛选   平均工资 大于2000的部门
 
SELECT deptno AS 部门编号,AVG(sal) AS 平均工资 FROM emp WHERE sal>1500 GROUP BY deptno HAVING 平均工资>2000;
 
 
-- having和where的区别？
-- * where：在分组之前对条件进行限定。不满足条件，就不会参与分组
-- * having：在分组之后，对结果集的筛选

2.分页查询

limit
* limit 0,5 开始的记录索引, 每一页显示的条数索引从0开始
开始的记录索引 = (页码-1)*每一页显示的条数


-- 分页 LIMIT
 
SELECT * FROM emp;
 
-- 查询第一页的数据，每页展示3条
SELECT * FROM emp LIMIT 0,3;
 
 
-- 查询第二页的数据，每页展示3条
 
SELECT * FROM emp LIMIT 3,3;
 
 
-- 查询第三页的数据，每页展示3条
 
SELECT * FROM emp LIMIT 6,3;
 
-- 查询第四页的数据，每页展示3条
 
SELECT * FROM emp LIMIT 9,3;
 
-- 查询第五页的数据，每页展示3条
 
SELECT * FROM emp LIMIT 12,3;
 
 
起始索引=(页码-1)*每页的条数
 
 
-- 查询第一页的数据，每页展示3条
SELECT * FROM emp WHERE sal>1500 LIMIT 0,3;
 
SELECT * FROM emp WHERE sal>1500 ORDER BY sal DESC LIMIT 0,3;

3.子查询和定义变量

-- 查询最高工资的员工姓名

SELECT ename,MAX(sal) FROM emp; -- 这个查出不对，没有条件

-- 用子查查询

SELECT ename,sal FROM emp WHERE sal=(SELECT MAX(sal) FROM emp);

-- 使用一个变量，来保存一下这个最高工资，注意赋值的语法使用 := 来赋值
SELECT ename,sal,@gz:=MAX(sal) AS 最高工资 FROM emp;
-- 查看变量的值
select @gz;


-- 查询最高工资
SELECT MAX(sal) FROM emp;
 
 
-- 查询最高工资的员工姓名
 
-- 用子查询:一条主查询的条件的值，来自于另一条子查询
 
SELECT ename,sal FROM emp WHERE sal=(SELECT MAX(sal) FROM emp);
 
-- 方式2：
 
SELECT ename,sal,MAX(sal) FROM emp WHERE sal=5000;
 
-- 定义一个变量，保存一下聚合函数的结果，使用这个变量作为条件
 
 
SELECT ename,sal,@gz:=MAX(sal) AS 最高工资 FROM emp;
 
SELECT @gz;
 
 
 
-- 变量的使用
 
-- 使用一个变量，来保存一下这个最高工资，  注意赋值的语法 使用 := 来赋值
SELECT @gz:=MAX(sal) AS 最高工资 FROM emp;
-- 查看变量的值
SELECT @gz;
 
SELECT * FROM emp WHERE sal=@gz;

4.case when then end 语句


-- -- 给不同职位的员工加不同的工资，使用 case when then end 语句来完成
 
 
SELECT ename,job,sal AS 原来的工资,
 
CASE job
 
WHEN 'PRESIDENT' THEN sal+1000
WHEN 'MANAGER' THEN sal+800
ELSE sal+300
END AS 涨后的工资
FROM emp;

5.约束

作用:
       为了保证数据的有效性和完整性

   mysql中常用的约束:
           主键约束(primary key)
           自增长约束 auto_incrment 加在整数型的字段配和主键约束来使用
           唯一约束(unique)
           非空约束(not null)
           外键约束(foreign key)
           sex ENUM('男','女') -- 把一个字段的数据类型设置为枚举类型也可以起到一种约束的效果
           非负约束 UNSIGNED
           例如：这个数据类型 TINYINT 能表示的范围 -128---127
  我添加了这个约束 TINYINT UNSIGNED 范围就是 0----255 也就是没有了负数值

6.主键约束

主键约束:被修饰过的字段唯一非空
注意:一张表只能有一个主键,这个主键可以包含多个字段
建议：我们也强烈的建议，建表的时候，要有一个主键，一般建议建表的时候，给个 id 整数型字段，把这个id字段作为主键，

删除主键约束:分两种情况
           情况1: 这个字段,只有主键约束,分两步来删除主键约束
           第一步: alter table 表名 drop primary key; -- 这样只删除了唯一,他还有个非空约束,所以得再删除非空约束
           第二步: alter table [表名] modify [列名] varchar(20) null; --修改字段名还为原来的字段加上null即可
           情况2: 这个字段,是一个int类型字段,既有主键约束,又有自增长约束,那么得先删除自增长约束,在删除主键约束
           第一步:删除自增长约束,其实就是修改自增长字段名和数据类型还为原来的字段名和类型
               alter table 表名 change 字段名字段名数据类型; --删除自增长约束
第二步:删除主键约束
               alter table 表名 drop primary key;
           第三步:删除非空约束
           ALTER TABLE test3 MODIFY sid INT NULL; -- 就是修改字段值可以为null


-- 添加主键约束的语法
-- 方式1 建表的时候，给某个字段添加主键约束
CREATE TABLE test(
	id INT, 
	username VARCHAR(20) PRIMARY KEY  -- 给这个字段添加主键约束
);
 
 
INSERT INTO test VALUES(1,'tom');-- 成功
 
INSERT INTO test VALUES(2,'tom');-- 失败
 
INSERT INTO test VALUES(2,'jery');-- 成功
 
INSERT INTO test VALUES(2,NULL); -- 失败
 
 
 
-- 方式2 建表的时候，给某个字段添加主键约束
CREATE TABLE test2(
	id INT,
	username VARCHAR(20),
	PRIMARY KEY(id) -- 在这里指定主键字段
);
 
 
 
INSERT INTO test2 VALUES(1,'tom');-- 成功
 
INSERT INTO test2 VALUES(1,'tom2');-- 失败
 
 
 
 
-- 方式3 表建好之后，再添加主键约束
CREATE TABLE test3(
	id INT,
	username VARCHAR(20)
	
);
 
-- 采用修改表的方式，来添加主键
 
ALTER TABLE test3 ADD PRIMARY KEY(username);
 
 
 
INSERT INTO test3 VALUES(1,'tom');-- 成功
 
INSERT INTO test3 VALUES(2,'tom');-- 失败
 
 
-- 联合主键：把多个字段作为一个整体，来添加主键约束
 
CREATE TABLE test4(
	id INT,
	username VARCHAR(20)
	
);
-- 把 id 和  username 看做一个整体，添加联合主键
ALTER TABLE test4 ADD PRIMARY KEY(id,username);
 
 
INSERT INTO test4 VALUES(1,'tom');-- 成功
 
INSERT INTO test4 VALUES(1,'jery');-- 成功
 
INSERT INTO test4 VALUES(1,NULL);-- 失败
 
 
INSERT INTO test4 VALUES(NULL,'tom');-- 失败
 
 
-- 我们也强烈的建议，建表的时候，要有一个主键，一般建议建表的时候，给个 id 整数型字段，把这个id字段作为主键，

7.唯一约束

-- 唯一约束特点：值不能重复
-- 注意：唯一约束，对null值不起作用。


CREATE TABLE test5(
	id INT,
	username VARCHAR(20) UNIQUE -- 添加唯一约束
	
);
 
 
INSERT INTO test5 VALUES(1,'tom');-- 成功
 
 
 
CREATE TABLE test6(
	id INT,
	username VARCHAR(20),
	UNIQUE(id) -- 唯一约束
	
);
 
INSERT INTO test6 VALUES(1,'tom');-- 成功
 
INSERT INTO test6 VALUES(1,'jery');
 
 
CREATE TABLE test7(
	id INT,
	username VARCHAR(20)
	
	
);
 
ALTER TABLE test7 ADD UNIQUE(username);
 
INSERT INTO test7 VALUES(1,'tom');-- 成功
 
INSERT INTO test7 VALUES(1,NULL);-- 成功
 
 
INSERT INTO test7 VALUES(1,NULL);-- 成功

8.非空约束

特点:被修饰过的字段非空


CREATE TABLE test8(
	id INT,
	username VARCHAR(20) NOT NULL  -- 非空约束
	
);
 
 
INSERT INTO test8 VALUES(1,NULL);-- 成功
 
-- UNIQUE NOT NULL, 组合起来，给表中多个字段加，但他不等同于 主键约束，主键约束一个表中只能有一个。
CREATE TABLE test9(
	id INT UNIQUE NOT NULL,
	username VARCHAR(20) UNIQUE NOT NULL  -- 唯一 非空约束
	
);

9.枚举类型

-- 枚举类型可以起到约束的作用


 
CREATE TABLE test91 (
  username VARCHAR (20) PRIMARY KEY,  
   age INT UNIQUE NOT NULL, -- 唯一约束
   sex ENUM('男','女')  -- 字段的数据类型是枚举类型，可以起到约束 的作用
);
 
 
 
 
INSERT INTO test91 VALUES('aaa',10,'男');
 
INSERT INTO test91 VALUES('bbb',20,'女');
 
INSERT INTO test91 VALUES('ccc',30,'妖');

10.非负约束


UNSIGNED
 
INSERT INTO test92 VALUES('ccc',-1);
 
CREATE TABLE test93 (
  username VARCHAR (20) PRIMARY KEY,  
   age TINYINT UNSIGNED -- 非负约束 
 
);
 
TINYINT 1个字节  -128---127
TINYINT UNSIGNED  0---255
 
 
INSERT INTO test93 VALUES('ccc',1);
 
INSERT INTO test93 VALUES('ddd',255);

11.自增长约束

-- 自增长约束：一般用在整数类型字段，配合主键约束一起使用

-- 以后我们会给每张表都设计一个id字段，这个id字段设置为主键自增长


CREATE TABLE test94 (
   id INT PRIMARY KEY AUTO_INCREMENT,   -- 主键自增长
  username VARCHAR (20),
   age TINYINT UNSIGNED -- 非负约束 
 
);
 
 
INSERT INTO test94(username,age) VALUES('ddd',255);
 
INSERT INTO test94(username,age) VALUES('bbb',255);

12.实体与实体之间的关系

-- 实体：把现实的事物-----跟表对应起来

学生:-------------student表

       学号 sid

       姓名 sname
       性别 sex
       年龄 age
       住址 address

-- java中 class Student------   student表

       学号 sid

       姓名 sname
       性别 sex
       年龄 age
       住址 address


表：就可以看做实体


实体和实体之间存在一些对应关系

一对一：夫妻关系一个人只能有一个身份证号
一对多：用户表订单表一个用户可以对应多个订单。
多对多：老师和学生一个老师可以教很多学生，一个学生也可以被多个老师教

一个订单里面可以有多个商品一个商品也可以属于多个订单

商城项目：
分析出来有哪些实体：用户，商品订单

class USER ----- user 用户表
class ShangPin ---- 商品表
class orders ----- 订单表

13.ER图

ER图可以描述实体于实体之间的关系
   实体：用矩形表示
   属性：用椭圆表示
   关系：用菱形表示

ER图
在ER图中有如下四个成分：
矩形框：表示实体，在框中记入实体名。
菱形框：表示联系，在框中记入联系名。
椭圆形框：表示实体或联系的属性，将属性名记入框中。对于主属性名，则在其名称下划一下划线。
连线：实体与属性之间；实体与联系之间；联系与属性之间用直线相连，并在直线上标注联系的类型。
（对于一对一联系，要在两个实体连线方向各写1；
对于一对多联系，要在一的一方写1，多的一方写N；对于多对多关系，则要在两个实体连线方向各写N,M。)

14.外键约束

-- 外键约束：是为了保证数据的有效性和完整性。

-- 添加外键约束的目的，是为了保证数据的有效性和完整性。
-- 我们在多表一方，添加外键约束去关联主表一方的主键。
-- 添加了外键约束后有如下特点
-- 1. 主表一方不能删除，多表一方还在引用的数据
-- 2. 多表一方，不能添加，主表没有描述的数据。


-- 创建用户表
	CREATE  TABLE USER(
		id INT PRIMARY KEY AUTO_INCREMENT,
		username VARCHAR(20)
	);
	
	-- 创建订单表
	CREATE  TABLE orders(
		id INT PRIMARY KEY AUTO_INCREMENT,
		totalprice DOUBLE,
		user_id INT   -- 这个字段的意思是 这个订单属于哪个用户，也就是说同个这个字段让表和表之前存在对应关系
	);


 -- 通过修改表来添加外键约束
    -- alter table 多表名称 add foreign key(外键名称) references 一表名称(主键);
    
   ALTER TABLE orders ADD FOREIGN KEY(user_id) REFERENCES USER(id);
   
   
   
   CREATE TABLE zhu(
    zid INT PRIMARY KEY AUTO_INCREMENT,  -- 主键
    zname VARCHAR(20)
 
);
 
CREATE TABLE cong(
    zid INT PRIMARY KEY AUTO_INCREMENT,
    zscore INT,
    zzid INT, -- 外键
 -- 方式2：建表的时候就加上了外键约束
    FOREIGN KEY(zzid) REFERENCES zhu(zid)
);

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/article/detail/49385