mysql排序，约束

最新推荐文章于 2023-06-08 08:37:04 发布

爱菊的IT

最新推荐文章于 2023-06-08 08:37:04 发布

阅读量368

点赞数

本文链接：https://blog.csdn.net/weixin_45240352/article/details/103428265

版权

今日内容

1. DQL:查询语句
	1. 排序查询   order by 字段名 asc 升序   desc 降序
	2. 聚合函数   sum 求和  avg  求平均数   max 求最大值  min 求最小值  count 求个数
	3. 分组查询   group by 字段名 having 过滤  通常结合聚合函数一同使用
	4. 分页查询   limit(0,5)   limit(5,5)      limit(3)查询前3条数据

2. 约束    外键约束   主键  外键  非空  唯一  默认
3. 多表之间的关系  
4. 备份和还原

DQL:查询语句

1. 排序查询
	* 语法：order by 子句
		* order by 排序字段1 排序方式1 ，  排序字段2 排序方式2...

	* 排序方式：
		* ASC：升序，默认的。
		* DESC：降序。

	* 注意：
		* 如果有多个排序条件，则当前边的条件值一样时，才会判断第二条件。


2. 聚合函数：将一列数据作为一个整体，进行纵向的计算。
	1. count：计算个数
		1. 一般选择非空的列：主键
		2. count(*)
	2. max：计算最大值
	3. min：计算最小值
	4. sum：计算和
	5. avg：计算平均值
	

	* 注意：聚合函数的计算，排除null值。
		解决方案：
			1. 选择不包含非空的列进行计算
			2. IFNULL函数

3. 分组查询:
	1. 语法：group by 分组字段；
	2. 注意：
		1. 分组之后查询的字段：分组字段、聚合函数
		2. where 和 having 的区别？
			1. where 在分组之前进行限定，如果不满足条件，则不参与分组。having在分组之后进行限定，如果不满足结果，则不会被查询出来
			2. where 后不可以跟聚合函数，having可以进行聚合函数的判断。

		-- 按照性别分组。分别查询男、女同学的数学平均分
		select sex ,avg(math) from student group by sex;
		
		-- 按照性别分组。分别查询男、女同学的数学平均分,人数
		select sex ,avg(math),count(id) from student group by sex;
		
		--  按照性别分组。分别查询男、女同学的平均分,人数 要求：分数低于70分的人，不参与分组
		分析：		
		SELECT sex,AVG(math+english) FROM student   WHERE  math >70 GROUP  BY sex;
		
		--  按照性别分组。分别查询男、女同学的平均分,人数 要求：分数低于70分的人，不参与分组,分组之后。人数要大于2个人		
		
		select sex ,avg(math),count(id) from student  where math>70 group by sex having count(id)>2;

		
4. 分页查询
	1. 语法：limit 开始的索引,每页查询的条数;  limit 0,1
	2. 公式：开始的索引 = （当前的页码 - 1） * 每页显示的条数

	3. limit 是一个MySQL"方言"

	4. limit 后面参数  第一个索引，第二个偏移量

约束

* 概念： 对表中的数据进行限定，保证数据的正确性、有效性和完整性。	
* 分类：
	1. 主键约束：primary key   保证以该列字段查询，查出唯一数据
	2. 非空约束：not null      保证该列数据不能为null
	3. 唯一约束：unique        保证该列数据的唯一性
	4. 外键约束：foreign key   设计多表，从表的数据，依赖于主表；
	5. 默认约束：default 1     如果该列数据不存数据，那么有一个默认值；  timestamp如果添加数据时，为null，那么默认当前系统时间

* 非空约束：not null，某一列的值不能为null   
	1. 创建表时添加约束
		CREATE TABLE stu(
			id INT,
			NAME VARCHAR(20) NOT NULL -- name为非空
		);
	2. 创建表完后，添加非空约束
		格式：ALTER TABLE 表名 MODIFY 列名  数据类型  约束 ；
		ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;

	3. 删除name的非空约束
		ALTER TABLE stu MODIFY NAME VARCHAR(20);
		
		null不会占用空间，空字符会占用空间。
* 唯一约束：unique，某一列的值不能重复
	1. 注意：
		* 唯一约束可以有NULL值，但是只能有一条记录为null
	2. 在创建表时，添加唯一约束
		CREATE TABLE stu(
			id INT,
			phone_number VARCHAR(20) UNIQUE -- 手机号
		);
	3. 删除唯一约束
		ALTER TABLE stu DROP INDEX phone_number;
	4. 在表创建完后，添加唯一约束
		ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;

* 主键约束：primary key。
	1. 注意：
		1. 含义：非空且唯一
		2. 一张表只能有一个字段为主键
		3. 主键就是表中记录的唯一标识

	2. 在创建表时，添加主键约束
		create table stu(
			id int primary key auto_increment,-- 给id添加主键约束
			name varchar(20)
		);

	3. 删除主键
		-- 错误 alter table stu modify id int ;
		ALTER TABLE stu DROP PRIMARY KEY;

	4. 创建完表后，添加主键
		ALTER TABLE stu MODIFY id INT PRIMARY KEY;

	5. 自动增长：
		1.  概念：如果某一列是数值类型的，使用 auto_increment 可以来完成值得自动增长

		2. 在创建表时，添加主键约束，并且完成主键自增长
		create table stu(
			id int primary key auto_increment,-- 给id添加主键约束
			name varchar(20)
		);

		
		3. 删除自动增长
		ALTER TABLE stu MODIFY id INT;
		4. 添加自动增长
		ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;

测试脚本
1、冗余
2、给员工指定不存在的部门ID

* 外键约束：foreign key,让表于表产生关系，从而保证数据的正确性。
		概念：在从表中与主表主键对应的那一列。
		主表：一的一方。用来约束其他的表。
		从表：多的一方。被别其他表约束。
	1. 在创建表时，可以添加外键
		* 语法：
			create table 表名(
				....
				外键列
				constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
			);

	2. 删除外键
		ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

	3. 创建表之后，添加外键
		ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);
	
	
	4. 级联操作
		1. 添加级联操作
			语法：ALTER TABLE 表名 ADD CONSTRAINT 外键名称 
					FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE  ;
		2. 分类：
			1. 级联更新：ON UPDATE CASCADE 
			2. 级联删除：ON DELETE CASCADE

数据库的设计

1. 多表之间的关系
	1. 分类：
		1. 一对一(了解)：   
			* 如：人和身份证
			* 分析：一个人只有一个身份证，一个身份证只能对应一个人
		2. 一对多(多对一)：
			* 如：部门和员工
			* 分析：一个部门有多个员工，一个员工只能对应一个部门
		3. 多对多：
			* 如：学生和课程
			* 分析：一个学生可以选择很多门课程，一个课程也可以被很多学生选择
	2. 实现关系：
		1. 一对多(多对一)：
			* 如：部门和员工
			* 实现方式：在多的一方建立外键，指向一的一方的主键。
		2. 多对多：
			* 如：学生和课程
			* 实现方式：多对多关系实现需要借助第三张中间表。
			中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键
		3. 一对一(了解)：
			* 如：人和身份证
			* 实现方式：一对一关系实现，可以在任意一方添加唯一外键指向另一方的主键。

	3. 案例  
		产品 product  分类 category  用户user   角色role
		* 分类和产品是一对多的关系。
		分类表有cid 和cname两个属性。其中cid是int类型，主键，自动增长。cname是varchar类型，约束为非空。
		产品表有pid、panme、price三个属性。其中pid是int类型，主键，自动增长。
		pname是varchar类型，price是int类型。定义一个外键cid，指向分类表的主键。
		
		* 用户和角色是多对多关系。
		用户表有uid、uname、age、address四个属性。其中uid是int类型，主键，自动增长。
		uname是varchar类型，约束为非空。age是int类型，address是varchar类型。
		角色表有rid、rname、details三个属性。其中rid是int类型，主键，自动增长。
		rname是varchar类型，约束为非空。details是varchar类型。
		中间表user_role有两个字段，uid,cid，二者作为联合主键，uid作为外键指向用户表的主键，cid作为外键指向角色表的主键。
		CREATE TABLE category (  -- 创建分类表
			cid INT PRIMARY KEY AUTO_INCREMENT,
			cname VARCHAR(20) NOT NULL
		);
		CREATE TABLE product (   -- 创建产品表
			pid INT PRIMARY KEY AUTO_INCREMENT,
			pname VARCHAR(20),
			price INT,
			cid INT,
			CONSTRAINT p_c_fk FOREIGN KEY (cid) REFERENCES category(cid)	
		);  
		CREATE TABLE USER(    -- 创建用户表
			uid INT PRIMARY KEY AUTO_INCREMENT,
			uname VARCHAR(20) NOT NULL,
			age INT ,
			address VARCHAR(50)
		);
		CREATE TABLE role(    --  创建角色表
			rid INT PRIMARY KEY AUTO_INCREMENT,
			rname VARCHAR(20) NOT NULL,
			details VARCHAR(100)
		);
		CREATE TABLE user_role(		--  创建用户角色表
			uid INT,
			rid INT,
			PRIMARY KEY (uid,rid),
			CONSTRAINT u_r_c_fk FOREIGN KEY(uid) REFERENCES USER(uid),
			CONSTRAINT r_u_c_fk FOREIGN KEY(rid) REFERENCES role(rid)
		)

数据库的备份和还原

1. 命令行：
	* 语法：
		在未登录的情况下，在dos窗口执行以下
		* 备份： mysqldump -u用户名 -p密码 数据库名称 > 保存的路径
		* 还原：
			1. 登录数据库
			2. 创建数据库
			3. 使用数据库
			4. 执行文件。source 文件路径
2. 图形化工具：

今日练习：
1、CREATE TABLE student(
sname VARCHAR(20),
age INT,
address VARCHAR(50),
sex VARCHAR(1),
money INT,
ssubject VARCHAR(50)
);

		INSERT INTO student (sname,age,address,sex,money,ssubject)VALUES 
		('帅地瓜',18,'山西省大同市','女',15000,'java'),
		('肖丁丁',19,'山东省青岛市','男',10500,'java'),
		('盖小二',17,'山东省淄博市','男',15000,'java'),
		('杜老大',23,'山东省济南市','女',9000,'php'),
		('菜菜',24,'河北省石家庄','女',12000,'php'),
		('张庄稼',32,'辽宁省大连市','男',9200,'php'),
		('唐伯虎',32,'安徽省合肥市','男',10200,'java'),
		('大白',45,'陕西省西安市','女',17500,'java');

– 1.写出统计java学科薪资在10000以上的学生人数（sql语句）
select ssubject, count(*) as ‘人数’ from student where ssubject = ‘java’ and money >10000;
– 2.写出查询籍贯是山东省并且年龄在18岁以上的学生姓名（sql语句）
select address ,sname, age from student where address like ‘山东%’ and age >18;
– 3.写出计算javaEE的平均薪资是多少（sql语句）
select avg(money) ‘平均工资’ from student where ssubject =‘java’;
– 4.写出按照薪资降序对所有学员进行排序（sql语句）
select * from student order by money desc;
– 5.写出计算出所有女同学的平均工资（sql语句）
select sex ,avg(money) from student group by sex having sex=‘女’;
– 6.写出超过10000的同学的平均薪资（sql语句）
select avg(money) from student where money >10000;
– 7.写出男、女同学的平均分，人数。按性别进行分组，低于10000的同学不参与分组，并且分组之后人数要大于2.
select sex ,avg(money),count(sex) from student where money>1000 group by sex having count(sex)>2;