MySQL笔记 合并查询 外连接 约束

-- 合并查询 union all以及union
-- union all就是将两个查询结果合并，不会去重
select ename,sal,job from emp where sal>2500; -- 5条记录
select ename,sal,job from emp where job = 'MANAGER' -- 3条记录

select ename,sal,job from emp where sal>2500 
union all
select ename,sal,job from emp where job = 'MANAGER' 

select ename,sal,job from emp where sal>2500 
union
select ename,sal,job from emp where job = 'MANAGER' 

-- 外连接 以往对多张表进行查询，对笛卡尔集用条件进行过滤，显示出所有匹配上的记录，匹配不上的则不进行显示，外连接则会会显示出所有的记录，不管有没有匹配上
-- 左外连接和右外连接
-- 左外连接，左边的表完全显示
-- 右外连接，右边的表完全显示
-- 列出部门名称和这些部门的员工信息（名字和工作），同时列出没有员工的部门
select dept.deptno,emp.ename,emp.job,emp.empno from emp right join dept on emp.deptno=dept.deptno

-- 约束
-- 1、主键
-- 在一张表中最多只能有一个主键但可以是复合主键，主键不能重复也不能为null
-- 直接在字段名指定：字段名 primary key
-- 在表后面写primary(字段1，字段2，。。。。)
-- primary key = not null + unique

create table t1
(
id int primary key,
name varchar(10),
email varchar(25)
);
desc t1;
insert into t1(id,name,email) values(1,'tom','tom@163.com'),(2,'jack','jack@163.com')
select * from t1;
alter table t1 modify id varchar(10) not null default '';
-- 删除主键
alter table t1 drop primary key;
-- 添加主键
alter table t1 add primary key(name);

-- 2、unique不允许这一列出现重复值，如果没有指定not null，则unique字段可以出现多个null，一张表也可以出现多个unique字段

create table t2
(
id int unique,
name varchar(25),
email varchar(25)
);
alter table t2 add unique(name);
desc t2;
insert into t2 values(1,'tom','tom@163.com');
insert into t2 values(2,'tom','tom@163.com');-- 插入失败
select * from t2;
-- 3、外键
-- 用于定义主表从表的之间的关系，外键约束要定义在从表上，主表则必须具有主键约束或者unique约束，当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null

create table my_class
(
id int primary key,
name varchar(255) not null default ''
);
create table my_stu
(
id int primary key,
name varchar(255) not null default '',
class_id int,
foreign key(class_id) references my_class(id)
);
desc my_stu;
INSERT INTO my_class VALUES(100, 'java'), (200, 'web');
INSERT INTO my_class VALUES(300, 'php');
INSERT INTO my_stu VALUES(1, 'tom', 100);
INSERT INTO my_stu VALUES(2, 'jack', 200);
INSERT INTO my_stu VALUES(3, 'hsp', 300);
INSERT INTO my_stu VALUES(4, 'mary', 400); -- 这里会失败...因为400班级不存在
INSERT INTO my_stu VALUES(5, 'king', NULL);
delete from my_class where id=100; -- 删除失败
delete from my_stu where class_id=100;
delete from my_class where id=100; -- 删除成功

-- 4、check MySQL8.0以前前不支持check，只做语法校验但不会生效，oracle、sql server支持，8.0以后添加了check约束
-- 非生成列和生成列允许被添加到表达式，但包含auto_increase的列不允许被加入
-- 字面量和确定性的内置函数以及操作符允许添加到表达式，确定性的含义是：同样的数据不同用户多次调用的结果是一样的
-- 存储函数和用户自定义函数不被允许
-- 可以通过触发器来实现check的功能
-- 查看客户端的版本
SHOW VARIABLES LIKE 'version';
create table t3
(
id int primary key,
name varchar(25),
sex varchar(6) check(sex in('man','woman')),
sal double check(sal>100 and sal<2000)
);
insert into t3 values(1,'tom','m',1100)  -- Check constraint 't3_chk_2' is violated.
select * from t3;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98