mysql数据库笔记

b站视频原址
https://www.bilibili.com/video/av39807944?p=56

mysql

是一种关系型数据库

一.学习使用终端操作数据库

1. 如何登录数据库服务器

mysql -uroot -p123456

2. 如何查询数据库中所有数据库

mysql-> show databases;

±-------------------+
| Database |
±-------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
±-------------------+
4 rows in set (0.00 sec)

3. 如何选中某一个数据库进行操作

4. 如何退出数据库

5. 如何在数据库服务器中创建自己的数据库

6. 如何在数据库服务器中查看自己的数据库的数据表

7. 如何在数据库服务器中创建自己的数据库的数据表

8. 查看数据表的数据详情

9. 添加数据表的数据记录

10. mysql常用数据类型

数值

日期/时间

字符串

11. 数据类型选择

日期 按照格式
数值和字符串按照大小

12. 删除表中数据

13. 修改表中数据

== 总结 ==

14.mysql建表的约束条件

（1）主键约束
他能够唯一确定一张表的一条记录，也就是我们通过给某个字段添加约束，就可以使得该字段不重复且不为空。

primary key 唯一确认一条记录 （不能重复 不能为空）

联合主键 加起来不重复就可以

（2）自增约束
不初始化id 每次插入一个相同name后，id会自动增加 不会重复

若创建表的时候，忘记创建主键约束应该如何操作？

修改表结构，添加主键。

删除主键。

修改主键。

（3）外键约束
涉及到两个表：父表，子表

班级表（主表）

学生表（副表） 添加外键约束

不能向学生表中添加外键约束中不存在的班级号

不能删除班级表中的已经被学生表引用外键约束的数据。

（4）唯一约束
约束修饰的字段的值不可以重复。
unique
与主键约束的区别就是 没有自增约束，不会自己创建id并且自增，值为NULL

后续添加唯一约束

创建时添加唯一约束

添加时两个键在一起不重复就可以。

如何删除唯一约束。

modify 添加约束


（5）非空约束
修饰的字段不能为空 NULL。

唯一约束+非空约束=主键约束（不包括自增）

（6）默认约束
当插入字段值的时候如果没有传值，就使用默认值。

15.数据库的三大设计范式

（1）第一范式
1NF
数据表中的所有字段都是不可分割的原子值。

字段值还可以继续拆分的，就不满足第一范式。

下述表满足第一范式。

范式设计的越详细，对于某些操作可能更好，但不一定都是好处

（2）第二范式
2NF


除主键以外的其他列，只依赖于主键的部分字段，不满足第二范式。

拆表拆成三个表后，满足第二范式。

（3）第三范式
3NF
必须先满足第二范式，除开主键列的其他列之间不能有传递依赖关系。

上述表中consumer_phone 与 order_id和consumer_id都有依赖关系 ，不满足第三范式。

下述满足第三范式。

16.mysql查询练习 面试常问

建表初始化
查询练习
1.

2.
3. distinct排重

**4.**查询区间 between…and… 或直接运算符比较

5. 表示同一字段或者关系的查询 where…in…

6. 表示不同字段的或者关系的查询 or

**7.**升序降序查询 默认升序排列 （asc 升序 desc 降序）



8. 不同字段一个升序一个降序

9. 统计总数
10. 寻找最高分的学号和课程号 子查询 或者 排序

排序的做法 limit 0,1第一个数字表示从第几个数据开始，第二个数字表示取出来几条


11. avg（）查询平均值 查询每门课的平均成绩

group by分组之后可以得到所有的平均值

12. 查询表中至少有两名学生的课并且以3开头的课的平均分数。

like表示模糊查询 ‘3%’ %可以是任意值

13. 查询分数大于70，小于90的sno列。

**14.**查询所有学生的sname,cno,degree列。（多个表查询并替换）


where student.sno=score.sno 可以将学号相同的字段替换

15. 查询所有学生的sno、cname和degree列。 （需要course 和 score 两个表 通过两个表的交集进行匹配）

16. 查询所有学生的sname，cname，degree列。 （三个表）
sname->student cname->course degree->score


17. 查询95031 班学生每门课的平均分。

分为三个部分：
（1）先找到在95031班内的学生学号

（2）找到所有在班级95031内的学生的成绩记录（in 表示或）

（3）根据课程号分组（group by cno 并且计算avg（）平均分数 ）

**18.**查询选修3-105课程的成绩高于8号同学成绩的所有同学的记录。
分为两步：
（1）查询8号同学3-105的记录

（2）查询选修3-105课程的成绩高于8号同学成绩的所有同学的记录

**19.**查询成绩高于学号8 课程号3-105的所有记录

20. 查询和学号8、1的同学同年出生的所有学生的sno、sname和sbirthday列。

（1）找到学号8和1同学的生日 使用year（…）内置函数

（2）查询和学号8、1的同学同年出生的所有学生的sno、sname和sbirthday列。

21. 查询伽罗老师任课学生的所有成绩。
（1）第一步找到伽罗的tno

（2）第二步找到tno对应的课程表cno

（3）第三步寻找score中cno等于上述的所有学生

22. 查询选修某课程同学人数多于3人的教师姓名。
可能存在多种课程人数大于3的情况，所以用in

（1）根据cno分组并且查询选课记录大于3人课程号

（2）根据cno找到该任课教师的tno

（3）得到任课教师姓名

**23.**查询95033和95031班全体学生的记录。

**24.**查询存在有85分以上成绩的课程cno

**25.**查询出计算机系教师所教课程的成绩表。
（1）查询计算机系老师的tno

（2）根据tno查询course的课程cno

（3）根据cno查询成绩表

26. 查询电子电子系与自动化系不同职称的教师的tname和prof。

union 求并集

27. 查询选修课程为3-105且成绩至少高于选修课程6-455的同学的cno、sno和degree，并按照degree从高到低排序。

至少高于的意思是 大于其中至少一个

（1）查询选修6-455同学的情况

（2） 查询选修3-105的情况且degree>any(…) any表示其中至少任意一个并降序

28. 查询编号3-105 且成绩高于6-455课程的同学的cno sno degree

与27的区别是：27是至少大于一个就可以，而这一题要大于所有，使用all

29. 查询所有教师和同学的name sex birthday

union 两个语句并集 as取别名，只需要第一个语句取别名即可
**30.**查询所有女教师 和女同学的name sex birthday

**31.**查询成绩比该课程平均成绩低的同学的成绩表。

（1）计算每个课程的平均分（group by）

表不能同时一边计算平均值一边比较，需要将a表复制为b表，b表计算平均值 a表表示所有值 同时比较的时候两个表的cno要一致
（2）复制临时表score_tmp来计算平均值 create temporary table b select * from a;

（3）比较score表所有值与score_tmp表计算的平均值

32. 查询所有任课教师的tname和depart
课程表里面安排了课程表示老师有课


33. 查询至少有两名男生的班号

34. 查询student表中不姓王的记录
模糊查询 like % not like

**35.**查询student中每个学生的姓名和年龄

年龄=当前年份减去出生年份

（1）查询当前年份以及个学生的出生年份


（2） 查询所有学生的年龄 as 别名

36. 查询student表中最大和最小的sbirthday日期值
max min

37. 以班号和年龄从大到小的顺序查询student全部记录

**38.**查询男教师及其所上的教程

39. 查询最高分同学的sno cno degree


**40.**查询和 孙漂亮 同性别的同学的sname

**41.**查询和 孙漂亮 同性别并且同班的同学

42. 查询所有选修‘高等数学’的女同学的成绩表。
（1）查询所有女同学的sno

（2） 查询高等数学的课程号

（3）查询选修高等数学且是女同学的成绩表

43.

17. SQL的四种连接查询

内连接
inner join 或者 join
外连接

1. 左连接 left join 或者 left outer join
2. 右连接 right join 或者 right outer join
3. 完全外连接 full join 或者 full outer join

创建两个表：



（1）inner join 查询（内连接 内联查询，其实就是两张表的数据，通过某个字段相对，查询出记录数据）


（2）left join（左外连接 会把左边表里的数据全部取出来，而右边表的数据如果有相等的显示出来，没有的补NULL）


（3）right join(右外连接 会把右边表里的数据全部取出来，而左边表的数据如果有相等的显示出来，没有的补NULL)


（4） full join(全外连接 )

mysql不支持full join 只能通过union 将左连接和右连接并起来

18. mysql的事务

事务其实是一个最小的不可分割的工作单元，事务能够保证一个业务的完整性。

mysql中如何控制事务？
mysql 默认开启事务（自动提交）。

事务回滚：撤销sql语句执行结果 rollback； 通过设置mysql事务自动提交为false


手动提交 commit 无法回滚

自动提交 @@autocommit=1 set autocommit=1 手动提交 commit 事务回滚 rollback;

begin 或者 start transaction 都可以帮助我们手动开启一个事务。

此时进行操作之后可以回滚，

但commit之后就不能rollback；

19. 事务的四大特征

ACID（atomicity 、consistency、isolation、durability）
A：原子性 事务是最小的单位，不可以在分割
C：一致性 事务要求同一事务中的sql语句，必须保证同时成功或者同时失败。
I：隔离性 事务1和事务2之间具有隔离性。
D: 持久性 事务一旦结束（commit，rollback），就不可以返回。

事务开启：

1. 修改默认提交 set autocommit=0；
2. begin；
3. start transaction；

事务手动提交：
commit

事务手动回滚：
rollback；

20. 事务的隔离性

1. read uncommited; 读未提交的
2. read commited; 读已经提交的
3. repeatable read; 可以重复读
4. serializable; 串行化

查看隔离级别

修改隔离级别

（1）read uncommitted （读未提交的）

出现脏读现象
一个事务读到另外一个事务没有提交的数据 实际开发不允许脏读。

（2）read committed （读已经提交的）

一个事务读到另外一个事务已经提交的数据。

出现不可重复读现象

（3）repeatable read （可重复读）
事务a和事务b同时操作一张表，事务a提交的数据不能被事务b读到，但实际操作的内容存在，出现幻读。

出现幻读现象。

（4）serializable （串行化）
当表被一个事务操作时，其他事务里面的写操作不可以进行，进入排队状态（串行化），等到操作表的事务提交后另一个事务的写操作才可以进行。 前提是在没有等待超时的情况下。

隔离级别越高，性能越差

二.学习使用可视化工具操作数据库

三.如何在编程语言中操作数据库