文章目录
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
外连接
- 左连接 left join 或者 left outer join
- 右连接 right join 或者 right outer join
- 完全外连接 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),就不可以返回。
事务开启:
- 修改默认提交 set autocommit=0;
- begin;
- start transaction;
事务手动提交:
commit
事务手动回滚:
rollback;
20. 事务的隔离性
- read uncommited; 读未提交的
- read commited; 读已经提交的
- repeatable read; 可以重复读
- serializable; 串行化
查看隔离级别
修改隔离级别
(1)read uncommitted (读未提交的)
出现脏读现象
一个事务读到另外一个事务没有提交的数据 实际开发不允许脏读。
(2)read committed (读已经提交的)
一个事务读到另外一个事务已经提交的数据。
出现不可重复读现象
(3)repeatable read (可重复读)
事务a和事务b同时操作一张表,事务a提交的数据不能被事务b读到,但实际操作的内容存在,出现幻读。
出现幻读现象。
(4)serializable (串行化)
当表被一个事务操作时,其他事务里面的写操作不可以进行,进入排队状态(串行化),等到操作表的事务提交后另一个事务的写操作才可以进行。 前提是在没有等待超时的情况下。
隔离级别越高,性能越差
二.学习使用可视化工具操作数据库
三.如何在编程语言中操作数据库