Mysql数据库

一、五大约束

1.1 主键约束

特点：非空 唯一 一张表只能有一个主键

1.2 唯一约束

1.关键字：unique
2.特点：唯一  不重复 可以为空
3.主键约束与唯一约束的区别
	A.主键约束一张表只能由一个   唯一约束一张表可以有多个
	B.主键约束不能为空   		唯一约束可以为空
	C.唯一约束可以设置为联合唯一约束（多个列组成唯一约束）
4.唯一约束创建的两种方法：
	A.创建表时候创建唯一约束
	B.创建表之后添加约束					

第一种方法：

CREATE DATABASE day09;
USE day09;

#创建表时候创建唯一约束
CREATE TABLE p1(
	pid INT(11) PRIMARY KEY AUTO_INCREMENT,
	pname VARCHAR(20) UNIQUE
);

第二种方法

#添加唯一约束、
ALTER TABLE p2 ADD UNIQUE(pname);
语法：alter table 表名 add constraint 唯一约束的名称 unique（列名）
#设置唯一约束的时候设置唯一约束的名称
ALTER TABLE p2 ADD CONSTRAINT u_name UNIQUE(pname);
语法：alter table 表名 drop index 唯一约束名（列名）
#删除唯一约束
ALTER TABLE p2 DROP INDEX pname;

联合唯一约束

#联合唯一约束
CREATE TABLE p3 (
	pid INT (11) PRIMARY KEY AUTO_INCREMENT,
	 pname VARCHAR(20),
	 page INT(10),
	 UNIQUE(pname,page)
); 
#验证联合唯一约束
INSERT INTO p3(pname,page) VALUES("张三",15);

验证：

1.3 非空约束

1.关键字；not null
2.说明；在插入数据的时候必须插入数据，不能为空
3.在创建表的时候进行设置

#创建表的时候设置非空
CREATE TABLE p4 (
	pid INT(11) PRIMARY KEY AUTO_INCREMENT,
	pname VARCHAR(20) NOT NULL
	
);

1.4 默认约束

1.关键字：default
2.在创建表的时候进行设置
3.作用：用于设置列的默认值
4.说明：
	A.指定列设置默认值后，为插入数据，默认列显示默认值
	B.在插入数据后，插入default，也是默认值
	C.只有在插入不是默认值时候，才会替换掉默认值
	 

#创建表设置默认值
CREATE TABLE p5(
	pid INT(11) PRIMARY KEY AUTO_INCREMENT,
	pname VARCHAR(20) DEFAULT "张三"
);

CREATE TABLE p6(
	pid INT(11) PRIMARY KEY AUTO_INCREMENT,
	pname VARCHAR(20) DEFAULT '张三'
);

在设置默认值时候，使用单引号和双引号都可以

二、表与表之间的联系

2.1简介

1.mysql是关系型数据库，表与表之间存在联系
2.表与表之间的关系：一对多 、多对一 、多对多
3.创建表之后添加外键

2.2 一对一的关系

#创建丈夫表
CREATE TABLE man(
	`mid` INT(11) PRIMARY KEY AUTO_INCREMENT,
	mname VARCHAR(20) 
	
);

#创建妻子表
CREATE TABLE women(
	wid INT(11) PRIMARY KEY AUTO_INCREMENT,
	wname VARCHAR(20),
	`mid` INT(11)
); 

语法：alter table 主表 add constraint 外键名 foreign key(`列名`) reference 副表名(`列名`)
#添加外键约束
ALTER TABLE women ADD CONSTRAINT fk_mid FOREIGN KEY(`mid`) REFERENCES man(`mid`);

注意点：

• 设置外键的 ` 这个不是单引号，而是1旁边的那个
• 设置外键一般都会有一个主表,相关联的外键所在的表为从表（子表），另外一个为主表

外键图

​​​​​​​​

定义外键的规则：

• 主表必须存在于数据库里面，或者主表正在创建。如果是后者，主表和从表是同一个表，这种表为自参照表，这个结构为自参照结构
• 外键中的数据类型必须与主键里面的一致
• 主表里面必须定义主键

2.3 一对多的关系

#创建部门表
CREATE TABLE dept(
	did INT(11) PRIMARY KEY AUTO_INCREMENT,
	dname VARCHAR(20)
);

#创建员工表
CREATE TABLE emp(
	eid INT(11) PRIMARY KEY AUTO_INCREMENT,
	ename VARCHAR(20),
	did INT(11)
);
#添加外键关系
ALTER TABLE emp ADD CONSTRAINT fk_did FOREIGN KEY (did) REFERENCES dept(did);

效果图

2.4 删除外键

• 先删除外键外键

  语法：alter table 表名 drop foreign key 外键名（列名）
  例子：ALTER TABLE emp DROP FOREIGN KEY fk_eid
  

• 删除外键的索引

2.5 多对多的关系

表设计–使用中间表来进行设计

 
#创建演员表
CREATE TABLE actor (
	aid INT(11) PRIMARY KEY AUTO_INCREMENT,
	aname VARCHAR(20)
);

#创建角色表
CREATE TABLE role (
	rid INT(11) PRIMARY KEY AUTO_INCREMENT,
	rname VARCHAR(20)
);

#创建中间表
CREATE TABLE a_r(
	a_r_id INT(11) PRIMARY KEY AUTO_INCREMENT,
	aid INT(11),
	rid INT(11)
);

#添加外键
#演员表和中间表
ALTER TABLE a_r ADD CONSTRAINT fk_aid FOREIGN KEY (aid) REFERENCES actor(aid);
#角色表和中间表
ALTER TABLE a_r ADD CONSTRAINT fk_rid FOREIGN KEY (rid) REFERENCES role(rid);

效果图

四、 三大泛式

1.三大范式：用来约束数据库的，保证数据的完整性，以及正确性
2.三大范式：
	A.第一大范式:表中的每一列都是最小的原子单位,不能在进行拆分
	B.第二大范式：每一张表里面只能描述一种事物
	C.在遵从前面的两个范式的基础上，不能出现局部依赖，只能与主键建立依赖关系

第一大范式：

第二大范式：

第三大范式：

五、多表查询

5.1 素材

创建表

#创建学生表
CREATE TABLE student(
	pid INT(11) PRIMARY KEY AUTO_INCREMENT,
	pname VARCHAR(20),
	page INT(5),
	psex CHAR(2) ,
	sid int(11)
	
);
#创建学科表
CREATE TABLE `subject`(
	sid INT(11) PRIMARY KEY AUTO_INCREMENT,
	sname VARCHAR(20),
	sGPA INT(2)
);

添加数据：

#学生表插入数据
INSERT INTO student(pname,page,psex,sid) 
VALUES("罗志祥",25,'男',1),("林伟翔",22,'男',1),
("林伟翔",22,'男',2),("刘佳",15,'女',2),
("薛之谦",25,'男',2),("徐至琦",25,'女',3),
("张三",18,'男',3),("欧巴",35,'女',4);

#学科表插入数据
INSERT INTO `subject`(sname,sGPA ) VALUES("java",4),("c语言",3),("瑜伽",2),("网球",2);

5.2 表结构

5.3 非等值查询

1.语法：select * from 表1，表2
2.案例：
	#查询学生以及学生的学科
	select * from 表1，表2
	SELECT stu.*,sub.*  FROM student stu,`subject` sub;
3.问题：
	出现了笛卡尔乘积的现象

5.4 连接查询

5.4.1 显示内连接

1.关键字：inner join 这个来进行连接 这就是显示内连接
2.语法：select * from 左表 inner join 右表 on 关联条件
		#查询所有学生信息和学科名字
		SELECT stu.*,sub.sname,sub.sGPA FROM student stu INNER JOIN `subject` sub  ON stu.sid=sub.sid;
		

取值范围：

5.4.2 隐式外连接

1.关键字：inner join 来进行查询连接
2.语法：select * from 左表 inner join 右表  where 关联条件
3.案例：
	#查询所有学生信息和学科名字（隐式外连接）
	SELECT * FROM student stu INNER JOIN `subject` sub WHERE stu.sid= sub.sid;

5.5 左连接

1.语法：select * from 左表 left join 右表 on 匹配规则
2.匹配规则：以左表为基准，右表一一匹配，如果匹配成功，两行进行显示，匹配没有成功，左表依然显示，右表设置			为null
3.案例：
	#不管这么学科是否有这个学生，查询所有的学科的信息
	SELECT sub.*,stu.* FROM `subject` sub LEFT JOIN student stu ON sub.sid = stu.sid;

5.6 右外连接

语法：select * from 左表 right join 右表 on 匹配条件
2.匹配规则：以右表为基准，左表一一匹配，如果匹配成功，两行进行显示，匹配没有成功，右表依然显示，左表设置			为null
3.案例：
	#不管是否有这门学科，查询所有的学生的信息
	SELECT * FROM `subject` sub RIGHT JOIN student stu ON sub.sid=stu.sid;

取值部分

5.7 子查询

1.概念：一个完成的查询语句嵌套另外的一个查询语句
2.两中体现：
	A.一个查询的结果当作另外一个查询语句的查询条件0
	B.一个查询语句的结果当作当前的查询语句的列来使用
3.第一种体现：
	#查询java学课下的所有的学生
	A.查询所有的学生 	select * from student  
	B.查询java学科		select * from ·subject· sub where sub.sname=“java”；
	select * from student stu where stu.sid in(select sub.sid from 	`subject` sub where 		sub.sname=“java”)
4.第二种体现：
	#查询所有的学生以及选的学科
	A.查询所有的学生信息		select * from student  
	B.查询学科  			select * from `subject` 
	C.两个的关联：sid
	SELECT stu.*,(SELECT sub.sname FROM `subject` sub WHERE stu.sid=sub.sid) as sname FROM 				student stu;

六、多表的查询

6.1 备份与还原

6.1.1 第一种方法 使用dos窗口来进行备份与还原

1.备份：
	指令：mysqldump -u用户名 -p密码  数据库的库名  >  备份存放的地址
2.还原
	指令：mysql -u用户名 -p密码 数据库名 <  备份文件存放的地址

备份：

还原：

6.1.2第二种方法 可视化工具

备份：

还原：

6.2 素材

6.3 表结构

![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4wi5ARSF-1648266393814)(image-20220326113256000-16482655769707.png)]](https://img-blog.csdnimg.cn/dcec5c28310748db8af8dd46ffad47dc.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5LiA5Liq6IOW5bCP55m9,size_20,color_FFFFFF,t_70,g_se,x_16

6.4 多表查询

#查询参加了考试的同学信息（学号、学生姓名、科目编号、分数）
 SELECT stu.StudentNo,stu.StudentName,r.SubjectNo,r.StudentResult
  FROM student stu 
  INNER JOIN result r
   ON stu.StudentNo=r.StudentNo;
   
#查询参加了考试的同学信息（学号、学生姓名、科目名称、分数）
SELECT stu.StudentNo,stu.StudentName,sub.SubjectName,r.StudentResult 
FROM student stu 
INNER JOIN result r ON stu.StudentNo=r.StudentNo 
INNER JOIN `subject` sub ON r.SubjectNo=sub.SubjectNo;


#查询出了所有同学，不考试的也查出来(应该使用左连接) 关键字 left join on 匹配规则
SELECT stu.StudentNo,stu.StudentName,r.StudentResult 
FROM student stu 
LEFT JOIN result r ON stu.StudentNo=r.StudentNo;

#查一下缺考的同学（没有成绩）
SELECT stu.StudentNo,stu.StudentName,r.StudentResult 
FROM student stu 
LEFT JOIN result r ON stu.StudentNo=r.StudentNo 
WHERE r.StudentResult IS NULL;

#思考题:查询参加了考试的同学信息（学号、学生姓名、科目名、分数）
SELECT stu.StudentNo,stu.StudentName,sub.SubjectName,r.StudentResult 
FROM student stu 
INNER JOIN result r ON stu.StudentNo=r.StudentNo
INNER JOIN `subject` sub ON r.SubjectNo= sub.SubjectNo;

# 查询《数据库结构-1》的所有考试结果，并按成绩由高到低排列(升序asc 降序desc)
SELECT sub.SubjectName,r.StudentResult  
FROM result r 
INNER JOIN `subject` sub ON r.SubjectNo=sub.SubjectNo 
WHERE sub.SubjectName="数据库结构-1"
ORDER BY r.StudentResult DESC;

#查询所有课程《数据库结构-2》的考试成绩，并按照由高到低的显示，同时把该成绩对应的学生的学号、姓名打印出来
SELECT stu.StudentNo,stu.StudentName,r.StudentResult
 FROM student stu 
 INNER JOIN result r ON stu.StudentNo=r.StudentNo
 INNER JOIN `subject` sub ON r.SubjectNo =sub.SubjectNo
 WHERE sub.SubjectName="数据库结构-2"
 ORDER BY r.StudentResult DESC;

 #查询课程为《高等数学-2》且分数不小于80分的学生的学号和姓名
 SELECT stu.StudentNo,stu.StudentName,r.StudentResult
 FROM student stu 
 INNER JOIN result r ON stu.StudentNo=r.StudentNo
 INNER JOIN `subject` sub ON r.SubjectNo=sub.SubjectNo
 WHERE sub.SubjectName="高等数学-2" AND r.StudentResult >=80; 
 
#案例 ：查询《C语言-1》的前5名学生成绩信息(需要进行排序：关键字：order by 升序(asc) 降序(desc))
SELECT stu.StudentNo,stu.StudentName,r.StudentResult
FROM student stu 
INNER JOIN result r ON stu.StudentNo = r.StudentNo
INNER JOIN `subject` sub ON r.SubjectNo = sub.SubjectNo
WHERE sub.SubjectName="C语言-1"
ORDER BY r.StudentResult DESC
LIMIT 0,5;

#查询课程为《JAVA第一学年》成绩前10名分数大于80的学生信息（学号，姓名，课程名，分数）
 SELECT stu.StudentNo,stu.StudentName,sub.SubjectName,r.StudentResult
 FROM student stu 
 INNER JOIN result r ON stu.StudentNo=r.StudentNo
 INNER JOIN `subject` sub ON r.SubjectNo = sub.SubjectNo
 WHERE sub.SubjectName="JAVA第一学年" AND r.StudentResult>80
 ORDER BY r.StudentResult DESC 
 LIMIT 0,10;
 
#按照不同的课程，分别算出其平均分、最高分和最低分，对于低于60分平均分的不予显示(使用左连接 需要进行分组，之后进行过滤)
SELECT sub.SubjectName AS "课程名称",
ROUND(AVG(r.StudentResult),2) AS "平均分",
MAX(r.StudentResult) AS "最高分",
MIN(r.StudentResult) AS "最低分" 
FROM `subject` sub 
LEFT JOIN result r ON sub.SubjectNo=r.SubjectNo
GROUP BY sub.SubjectName
HAVING AVG(r.StudentResult)>=60;