数据库2

Posted on | In |
Words count in article: 2.6k | Reading time ≈ 13
数据库基本原理、MySQL

数据库的三大设计范式

第一范式：数据表中的所有字段都是不可分割的原子值

  1. 字段值还可以继续拆分的就不满足第一范式
  2. 范式，设计的越详细，对某些实际操作可能更好，但是不一定

第二范式：

  1. 必须满足第一范式的要求
  2. 除主键外的每一列都必须完全依赖于主键
  3. 如果出现不完全依赖，只可能发生在联合主键的情况下
  4. 如果不满足，则拆表，直到实现完全依赖于主键

第三范式：

  1. 必须满足第二范式
  2. 除了主键列之外，其他列之间不能有传递依赖关系

查询训练

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
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
create table student(
student_num varchar(20) primary key,
student_name varchar(20) not null,
student_sex varchar(10) not null,
student_birthday datetime,
class varchar(20)
);

create table teacher(
teacher_num varchar(20) primary key,
teacher_name varchar(20) not null,
teacher_sex varchar(20) not null,
teacher_birthday varchar(20),
prof varchar(20) not null,
depart varchar(20) not null
);

create table course(
course_num varchar(20) primary key,
course_name varchar(20) not null,
teacher_num varchar(20) not null,
foreign key(teacher_num) references teacher(teacher_num)
);

create table score(
student_num varchar(20) primary key,
course_num varchar(20) not null,
degree decimal,
foreign key(student_num) references student(student_num),
foreign key(course_num) references course(course_num)
);


insert into student values('101', 'aaa', 'f', '1996-01-01', '101');
insert into student values('201', 'bbb', 'f', '1995-06-01', '202');
insert into student values('301', 'ccc', 'f', '1994-04-01', '301');
insert into student values('111', 'ddd', 'f', '1996-01-01', '102');
insert into student values('112', 'eee', 'f', '1996-01-01', '101');
insert into student values('201', 'fff', 'f', '1995-07-31', '202');
insert into student values('113', 'ggg', 'f', '1996-11-21', '103');
insert into student values('114', 'hhh', 'f', '1996-01-21', '103');
insert into student values('311', 'iii', 'f', '1994-06-08', '302');
insert into student values('123', 'jjj', 'f', '1996-11-01', '101');
insert into student values('211', 'kkk', 'f', '1995-04-21', '201');

insert into teacher values('990101', 'huanglaoshi', 'f', '1972-01-21', 'jiaoshou', 'cs');
insert into teacher values('080101', 'zhanglaoshi', 'm', '1991-06-08', 'fujiaoshou', 'es');
insert into teacher values('050101', 'xulaoshi', 'f', '1987-11-01', 'fujiaoshou', 'cs');
insert into teacher values('030101', 'liulaoshi', 'm', '1986-04-21', 'fujiaoshou', 'es');

insert into course values('1-101', 'cscc', '990101');
insert into course values('2-201', 'dsjkl', '080101');
insert into course values('3-301', 'sdqku', '050101');
insert into course values('4-401', 'asdhk', '030101');

insert into score values('101', '1-101', '98');
insert into score values('201', '1-101', '89');
insert into score values('301', '1-101', '78');
insert into score values('111', '1-101', '94');
insert into score values('112', '2-201', '68');
insert into score values('201', '2-201', '87');
insert into score values('113', '2-201', '87');
insert into score values('114', '3-301', '92');
insert into score values('311', '3-301', '94');
insert into score values('123', '4-401', '93');
insert into score values('211', '4-401', '96');

//查询部分列
select student_name,student_sex from student;

//剔除重复信息
select distinct depart from teacher;

//查询区间
select * from score where degree between 60 and 80;
select * from score where degree > 60 and degree < 80;
select student_num,degree from score where degree between 70 and 90;

//或的关系的查询
select * from score where degree in(98, 96);
select * from student where class = "101" or student_sex = "f";

//并且关系and
select * from student where class = "101" and student_sex = "f";

//降序查询
select * from student order by class desc;

//升序查询
select * from student order by class asc;

select * from score order by course_num asc, degree desc;

//统计人数
select count(*) from student where class = "101";

//查询最高分的学生学号和课程号
select student_num, course_num from score where degree = (select max(degree) from score);
//排序做法：limit 第一个数字表示从第几个开始，第二个表示有几个
select student_num, course_num, degree from score order by degree desc limit 0,1; 

//查询每门课的平均成绩avg
select avg(degree) from score where course_num = "1-101";
select course_num,avg(degree) from score group by course_num;

//score表中至少有2名学生选修并且以3开头的课程的平均分数
select course_num, avg(degree), count(*) from score group by course_num having count(*)>=2 and course_num like '1%';

//查询在不同表内的信息
select student_name, course_num, degree from student,score where student.student_num = score.student_num;
select student_num, course_name, degree from course, score where course.course_num = score.course_num;

//三个表
select student_name, course_name, degree from student, course,score where student.student_num = score.student_num and course.course_num = score.course_num;

//查询某个班每门课的平均分

select course_num, avg(degree) from score where student_num in (select student_num from student where class = '101') group by course_num;

//
select * from score where degree > (select degree from score where student_num = '112' and course_num = '2-201') and course_num = '2-201';

//查询出生年相同
select year(student_birthday) from student where student_number in (101, 201);
select student_num, student_name, student_birthday from student
where year(student_birthday) in (select year(student_birthday) from student where student_num in (101, 201));

//
select teacher_num from teacher where teacher_name = 'liulaoshi';

select * from score where course_num = (select course_num from course where teacher_num = (select teacher_num from teacher where teacher_name = 'liulaoshi'))select count(*) from student where class = "101";

//
select course_num from score group by course having count(*) > 5;
select teacher_num from course where course_num in (select course_num from score group by course having count(*) > 5);
select * from teacher where teacher_num = 
(select teacher_num from course where course_num in 
(select course_num from score group by course_num having count(*) > 3));

//有85分以上的成绩的课程的course_num

select course_num from score where degree > 85;

//
select * from score where course_num in (select course_num from teacher where depart = 'cs');

//一个系中独有的职称的老师
select * from teacher where depart = 'cs' and prof not in (select prof from teacher where depart = 'es')
union
select * from teacher where depart = 'es' and prof not in (select prof from teacher where depart = 'cs');

//大于某个人
select * from score where degree > (select degree from score where course_num = '1-101' and student_num = '201') and course_num = '1-101';
//大于任意一个人
select * from score where degree > any (select degree from score where course_num = '1-101') and course_num = '1-101' order by degree desc;

//大于所有人
select * from score where course_num = '1-101' and degree > all (select degree from score where course_num = '2-201');

//联合两个表格，使用别名统一名字
select teacher_name as name, teacher_sex as sex, teacher_birthday as brithday from teacher union select student_name, student_sex, student_birthday from student;

//查询成绩比平均成绩低的同学
select * from score where degree < (select avg(degree) from score);
select * from score a where degree < (select avg(degree) from score b where a.course_num = b.course_num);

//
select * from teacher where teacher_num in (select teacher_num from course);

//至少有两名男生的班号
select class from student where (select count(*) from student where student_sex = 'f') > 4;
select class from student where student_sex = 'f' group by class having count(*) > 2;

//
select * from student where student_name no like 'a%';

//年龄
select student_name, year(now()) - year(student_birthday) as 'old' from student;

//最大值 最小值
select max(student_birthday) as 'max', min(student_birthday) as 'min' from student;

//按照班号
select * from student order by class desc, student_birthday;

//查询男教师及其所上的课程
select * from course where teacher_num in (select teacher_num from teacher where teacher_sex = 'm');

//
select student_name from student where student_sex = (select student_sex from student where student_name = 'aaa');
select student_name from student where student_sex = (select student_sex from student where student_name = 'aaa') and class = (select class from student where student_name = 'aaa');

select * from student where student_sex = 'f' and 
select * from score 
where course_num = (select course_num from course where course_name = 'cscc') 
and student_num in (select student_num from student where student_sex = 'f' );

//建立grade表
create table grade(
low int(3),
up int(3),
grade char(1)
);

insert into grade values(90, 100, 'A');
insert into grade values(80, 89, 'B');
insert into grade values(70, 79, 'C');
insert into grade values(60, 69, 'D');
insert into grade values(0, 59, 'E');

//查询
select student_num, course_num, grade from score, grade where degree between low and up;

SQL的四种连接查询

  1. 内连接
  • inner join 或者 join
  1. 外连接
  • 左连接left join或者left outer join
  • 右连接right join或者right outer join
  1. 完全外连接 full join或者full outer join
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
// 创建两个表
create table person(
id int,
name varchar(20),
cardID int
);

create table card(
id int,
name varchar(20)
);

insert into card values(1, 'fanka');
insert into card values(2, 'nonghang');
insert into card values(3, 'gongshang');
insert into card values(4, 'youzheng');
insert into card values(5, 'jianhang');

insert into person values(1, 'zhangsan', 1);
insert into person values(2, 'wangwu', 3);
insert into person values(3, 'zhaoliu', 6);

insert into person values(1, 'zhangsan', 1);
insert into person values(2, 'wangwu', 1);
insert into person values(3, 'zhaoliu', 1);
  1. inner join 查询
    select * from person inner join card on person.cardId = card.id;
  • 内联查询，就是两张表中的数据通过某个字段相对查询出相关的记录数据
  1. left join左外连接
    select * from person left join card on person.cardId = card.id;
  • 把左边表的数据都取出来，右边表的数据如果有则相等，如果没有就补NULL
  1. right join右外连接，与左外连接相似

  2. full join全外链接，MySQL不支持，两个表的并集

    1
    2
    3
    select * from person left join card on person.cardId = card.id
    union
    select * from person right join card on person.cardId = card.id;

  3. 不用常见外键，通过相同的字段查询交集并集等

MySQL事务

  1. 是最小的不可分割的工作单元，事务能够保证业务的完整性
  2. MySQL默认开启事务select @@autocommit;
  3. 执行sql语句时，效果会立即体现，不能回滚
  4. 关闭事务，可以回滚，但是手动提交commit;后，仍然不能回滚

  5. begin;或者start transaction;都可以手动开启一个事务

    1
    2
    3
    4
    5
    begin;
    update user set money = money - 100 where name = 'aaa';
    update user set money = money + 100 where name = 'bbb';
    commit;
    //无法回滚

  6. 事务的四大特征ACID

  • 原子性，事务是最小的单位，不可再次分割
  • 一致性，同一事务中必须保证同时成功或者同时失败
  • 隔离性，两个事务之间具有隔离性
  • 持久性，事务一旦提交，就不可以回滚
  1. 事务的隔离性，隔离级别越高，性能越差
  • read uncommited; 读未提交的，事务a做操作，没有提交，但b可以读，脏读
  • read committed; 读已提交的，事务a做操作必须已经提交，b才可以读到，不可重复读
  • repeatable read; 可重复读 MySQL默认，幻读
  • serializable; 串行化，可重复读，禁止写入操作，必须等待其他事务结束，性能特差
  1. 查看MySQL事务隔离级别
  • 系统级别select @@global.transaction_isolation;
  • 会话级别select @@transaction_isolation;
  1. 修改隔离级别set global transaction islotion level read uncommited;
WhitneyLu wechat
Contact me by scanning my public WeChat QR code
0%