MySQL数据库（分组和分页，约束）

最新推荐文章于 2023-05-23 07:45:00 发布

可儿可爱

最新推荐文章于 2023-05-23 07:45:00 发布

阅读量887

点赞数

文章标签：数据库 mysql sql

本文链接：https://blog.csdn.net/weixin_62520626/article/details/123022113

版权

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)
);

可儿可爱

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL数据库（分组和分页，约束）

1.分组查询group by 一般配合聚合函数使用查出的数据才有意义* 查询的字段：1.分组字段本身2.聚合函数-- 比如我按部分编号分组比如有三个部门然后我求每个部门的平均工资那展示出来的数据应该有三条-- 一般聚合函数会配合分组来用SELECT deptno AS 部门编号,AVG(sal) AS 平均工资 FROM emp GROUP BY deptno;-- 查询每个部门多少人SELECT deptno AS 部门编号,COUNT(*) AS ..
复制链接

扫一扫