MYSQL——约束、多表查询、子查询、事务简介

一、约束

     1、约束的概念

约束主要用来限制加在表中的数据，实现数据库中数据的正确性、完整性、有效性。

     2、约束的种类

1. 主键约束（primary key）：主键是一行数据的表示，例如学号、省份证号等。是唯一、而且非空的。
2. 唯一约束（unique）：保证一列数据中没有重复数据。
3. 非空约束（not null）：这一列数据不能有空值。
4. 默认约束（default）：没有输入数据时，可以默认一个数据。
5. 外键约束（foreign key）：保持多张表之间的关系，让多张表间创建联系，保证数据的完整性。

     3、举例

     

1. 主键

   -- 演示主键(唯一不能重复)
   insert into student (Sno,Sname,Ssex,Sage,Sdept)
   values (1,'赵武','男',19,'IS');
   

2. 唯一

	-- 演示唯一约束(唯一不能重复)
	insert into student (Sno,Sname,Ssex,Sage,Sdept)
	values (6,'李勇','男',19,'IS');

3. 非空约束

 -- 演示非空约束
 insert into student (Sno,Sname,Ssex,Sage,Sdept)
 values (7,'王涛',null,19,'IS');

4. 默认约束

	-- 演示默认约束，没有输入默认补上
	insert into student (Sno,Sname,Ssex,Sdept)
	values (8,'王玉','女','CS');

5. 外键约束
   语法：

   建表时添加外键：
   constraint 外键名 foreign key(从表外键) references 主表(主表外键)；
   建表完成后添加外键：
   alter table 表名 add constraint 外键名 foreign key(从表外键) references 主表(主表外键)；
   删除外键：
   alter table 主表名 drop foreign key 外键名；
   

在Navicat Premium 里面可以通过点击对象——》选择对应表名——》右键选择逆向表到模型查看外键是否添加成功，表之间由这样的线连接起来就添加成功了。

要删除主表的对应信息需要先把从表的信息删除才能实现。

     4、例子代码

alter table student rename to stu;

select * from student;
-- 使用数据库
use dbcsdn;

-- 创建表
create table student(
	Sno int(20) primary key auto_increment,
	Sname varchar(10) not null unique,
	Ssex varchar(4) not null,
	Sage int(4) default 18,
	Sdept varchar(10)
);

-- 添加数据
insert into student(Sname,Ssex,Sage,Sdept) 
	values
	('李勇','男',20,'CS'),
	('刘晨','女',19,'CS'),
	('王敏','女',18,'MA'),
	('张立','男',19,'IS');
	
-- 演示主键(唯一不能重复)
insert into student (Sno,Sname,Ssex,Sage,Sdept)
values (1,'赵武','男',19,'IS');

-- 演示唯一约束(唯一不能重复)
insert into student (Sno,Sname,Ssex,Sage,Sdept)
values (6,'李勇','男',19,'IS');

-- 演示非空约束
insert into student (Sno,Sname,Ssex,Sage,Sdept)
values (7,'王涛',null,19,'IS');

-- 演示默认约束
insert into student (Sno,Sname,Ssex,Sdept)
values (8,'王玉','女','CS');

-- 新建一个成绩表，将该表与学生表用外键fk_grade_student连接起来
create table gender(
	Sno int(20) primary key auto_increment,
	score int(4) default 0,
	chinese int(4) default 0,
	math int (4) default 0,
	english int (4) default 0,
	
	constraint fk_gender_student foreign key(Sno) references student(Sno)
	);
	
alter table gender rename to grade;
select * from grade;

二、多表查询

     1、内连接查询

1. 隐式内连接
   语法

   select 字段 from 表名1，表名2 where 条件；
   -- 隐式内连接
   select * from student,grade 
   where student.Sno = grade.Sno;
   

2. 显式内连接

   语法：
   select 字段名 from 表名1 inner join 表名2 on 条件；（inner可以省略不写）
   -- 显示内链接
   select * from student inner join grade on
   student.Sno = grade.Sno;
   

     2、外连接查询

1. 左外连接：查询左边表全部信息和交集部分

   	语法：
   	select 字段名 from 表1 left join 表2 on 条件；
   	-- 左外连接
   	select * from student left join grade on
   	student.Sno = grade.Sno;
   

2. 右外连接：查询右边表全部信息和交集部分

	语法：
	select 字段名 from 表1 right join 表2 on 条件；
	-- 右外连接
	select * from student right join grade on
	student.Sno = grade.Sno;

     3、例子代码

insert into grade(score,chinese,math,english) values
(289,89,67,96),
(259,45,69,94),
(264,45,98,76),
(236,87,67,93),
(198,56,89,65),
(162,78,69,45),
(255,66,79,68),
(267,98,96,70);

select * from grade;

-- 多表查询
-- 隐式内连接
select * from student,grade 
where student.Sno = grade.Sno;

-- 显示内链接
select * from student inner join grade on
student.Sno = grade.Sno;

-- 左外连接
select * from student left join grade on
student.Sno = grade.Sno;

-- 右外连接
select * from student right join grade on
student.Sno = grade.Sno;

三、子查询

子查询就是查询里面包含查询。

     1、单行单列

子查询可以用运算符连接的查询。

语法：
select 字段名 from 表名 where 字段名 =（子查询）；
-- 查询总分大于id为8的同学的名字
SELECT	
	student.Sname
FROM	
	student,	grade 
WHERE	
	score > ( SELECT score FROM grade WHERE Sno = 8 );

     2、单行多列

子查询可以用in 表示的查询。

	语法：
		select 字段名 from 表名 where 字段名  in（子查询）；
	-- 单行多列-- 查询和id为1和2一样年龄的同学的名字
	SELECT DISTINCT
		student.Sname 
	FROM
	   student,	grade
	 WHERE	
	 Sage IN (	SELECT Sage FROM	student WHERE	Sno IN ( 1, 2 ));

     3、多行多列

子查询可以成为一张虚拟表的查询

	语法：
	select * from（子查询），表2 from where 条件；

     4、例子代码

-- 子查询，单行单列
-- 查询总分大于id为8的同学的名字
SELECT
	student.Sname
FROM
	student,grade 
WHERE
	score > ( SELECT score FROM grade WHERE Sno = 8 );
	
-- 单行多列
-- 查询和id为1和2一样年龄的同学的名字
SELECT DISTINCT
	student.Sname 
FROM
	student,
	grade 
WHERE
	Sage IN (
	SELECT
		Sage 
	FROM
		student 
	WHERE
	Sno IN ( 1, 2 ));

四、事务

     1、事务四大特征

1. 原子性：事务是最小的可操作单位，要么同时成功，要么同时失败。
2. 一致性：事务完成后，必须保持所有数据保持一致状态。
3. 隔离性：各个事物间，操作的可见性，可见性越高，隔离性越低。
4. 持久性：事务一旦回滚或者提交，它对数据的改变是永久的。

     2、举例

select * from grade where Sno = '8';
-- 开启事务
begin;
-- 将学号为8的同学数学加4分，语文减4分
update grade set math = math+4 where Sno = 8;
update grade set chinese = chinese-4 where Sno = 8;
-- 回滚事务
rollback;
-- 提交事务
commit;

     3、注意事项

1. 开启事务之后如果没有提交，数据库里面的数据是没有被改变的。
2. 发现操作事务就用回滚事务，它可以回到开始操作之前。
3. 提交事务之后，数据库里面的数据被永久改变无法回滚。