mysql数据库系统复习--b站视频

转自：https://www.jianshu.com/writer#/notebooks/41524309/notes/73440255

 

参考：https://www.bilibili.com/video/BV1S4411u74E?p=3   //b站视频

1）登录别人的mysql：

mysql -h127.0.0.1 -uroot -p123456

2）显示数据库、注释

show databases;  -- （带空格）显示数据库 #显示数据库

3）4类SQL：DDL、DML、DQL、DCL不知道是啥，先不管

4）CRUD   

create database db1 if not exists db1 character set gdk;  #带字符集创建

5）show create database inspectionrobot； #可以查看使用的字符集

修改字符集：alter database db3 set character set utf8

删除数据库：drop database db3 if exists db3

6）use database;             -- 使用某数据库

select databases();           --当前正在使用的数据库，这些有什么用？

7）显示表show tables;     -- 上面use database之后才能show tables！！！

desc table; --查看表结构

创建表：create table student（ 

    age int,

    name varchar(32) --没有逗号

）;

8）数据类型；（int就不用了吧）

score double(5, 2); --999.99

日期时间：实际只能定义成这样：date_time timestamp not null default now(),

date：yyyy-MM-dd

datatime：上面的还加上HH:mm:ss

timestamp：不赋值或赋值为null，默认使用系统时间

大文本：用TEXT

二进制图片：BINARY（M）

9）表的修改

修改表名：alter table student rename to stu；

也可以修改表的字符集

添加列：alter table stu add gender varchar(10);

删除列：alter table stu drop gender;

改列名：alter table stu change gender sex varchar(20);

如上只改类型，change用modify

9)图形化工具SQLyog

*****************************************************************************

前面的DDL，接下来是DML，非常重要：

1）INSERT INTO B1_0(pointNum, x, y) VALUES(0,0,3800); -- 除了数字，其它的都需要用单引号或双引号，例如date

2）delete from stu where id=1；

注意：如果不加条件，删除所有记录！！

delete from stu；     --效率较低

TRUNCAT table stu; -- 删除表后再创建一模一样的空表 == drop table + create table

3）update stu set name = 'asdf', age = 10 where ....

*****************************************************************************

接下來是DQL：查詢表中的記錄，关键！！

select * from * where * group by * order by * limit 分页限定

select name, age from student; -- 一般让写全

1）去掉重复 select distance address from stu;

2）计算字段math和english字段的和

select name，math, english, math+english from stu; -- 如果某个english为null，加起来的值都为null

修改为：select name，math, english, math+ifnull（english，0） as 总分 from stu;

as给字段名起别面

3）条件运算符，不等于：<>、!=，等于：一个=号

between...and：between 20 and 30； 包含20和30

in：select.... where age in(18, 20, 22);

is null：表中的null要用is null来判断！！！

与或非：

like：模糊查询，比如查询姓“马”的学生

_：单个任意字符

%：多个任意字符

select * from stu where name like ’马%‘；

select * from stu where name like ’___‘；-- 三个名字的人

4）排序查询：order by 排序字段1 排序方式1， 排序字段2 排序方式2

asc升序、desc降序；第一排序一样，就会按照第二排序

5）聚合函数？将一列数据作为一个整体，进行纵向的计算

count, max, min, sum, avg, -- 只有5个？会排除非空的数据库单元，怎么办

select COUNT(name) from stu;  

select COUNT(if null（name）) from stu;  --把null包含进来了

select COUNT(id) from stu; -- 一般用主键队列来计算，或id改成*，但不推荐

sum：会排除null进行计算，和前面的select math + english，当english为null时不一样

6）分组查询？比如男同学和女同学的平均分是多少?

分组之后查询的字段：只能写分组字段、聚合函数；

select sex，avg（math），count（id） from student group by sex；

如果分数低于70

select sex，avg（math），count（id） from student where math > 70 group by sex；

如果这个组<=2，这个组不显示

select sex，avg（math），count（id） from student where math > 70 group by sex having count（id）> 2

where和having的区别：

a：where在分组之前限定，如果不满足条件则不参与分组；having在分组之后限定

b：where后不跟聚合函数，having之后可以跟！！！！

7）分页查询？百度查找就是分页，可以结合web

limit 开始的索引，每页查询的条数

8条数据库记录，每页显示3条

select * from student limit 0，3；-- 第一页，从0开始，查3条记录

select * from student limit 3，3；-- 第二页，从3开始，查3条记录

公式：开始的索引 = （当前页码  - 1）* 每页显示的条数

分页在其它数据库中可能不是limit

*****************************************************************************

约束：4种，对表中的数据进行限定，从而保证数据的正确、有效、完整性

比如：名字不能为null

1）主键约束 primary key

删除主键：alter table drop primary key

如果加了自动增长，insert时，主键可以insert为null。如果上一行主键时4，下一样insert时设为10，那么也会被加进去

2）非空约束：name varchar（20） not null，

去掉非空约束：alter table stu modify name varchar(20)

3）唯一约束 unique，但可以有多个null

phone_number varchar（20）unique，

删除：alter table drop index phone_number --和非空删除方法不一样

添加：alter table stu modify phone_number varchar(20) unique

4）外键约束？表和表之间的关系（看到第34课，数据冗余）

 

拆分成员工表和部门表，员工表和部门表如何关联？

 

 

对应成了部门编号，出现的问题：

A：如果把“1 研发部 广州”删除了，员工表出问题了，这个问题怎么解决？用外键约束解决！！！语法很长

 

 

以上是原表，改成：

 

在最后加上CONSTRAINT，其中emp_dept_fk（emp表和dept表的外键）是自己写的，注意点：先创建dep表！！！

现象：

删除dep部门表的时候就会提示错误！！！

在emp表中添加不存在的编号为5的部门时也会报错！！！

B：如何删除外键，

ALTER TABLE emp DROP FOREIGN KEY emp_dept_fk

C：及创建表之后如何再添加外键

ALTER TABLE emp ADD CONSTRAINT.....同上图片

D：级联操作（外键可以为NULL，但不可以不存在！！名称为什么叫级联更新？）

问题：修改研发部的序号1为5，保存报错！

目的：改成5之后，emp表中的1也都改成5

方法1：可以把emp表中的所有1先改成NULL，再修改dep表的1为5，再修改emp所有NULL为5

方法2：架构设计器查看：35课3分58秒

 

 

写代码时加上后面这个就行了。

还可以级联删除，在上图后面再加上ON DELETE CASCADE即可，所有相关的所有数据都会删除！！谨慎使用

*****************************************************************************

数据库的设计：

1）多表之间的关系：

A:

1对1：人和身份证号。了解，现实开发中存在少。

1对多（多对1）：

部门和员工：一个部门多个员工，一个员工一个部门。

多对多：

学生和课程：

B：实现关系：

 

多对多时，用以上方法就不合适，总不可能dept_id写上多个部门id吧，怎么办？解决：使用一张中间表！！注意联合主键概念。

 

1对1，外键唯一。一般情况合成一张表就行了。

 

第40课案例：表格上面写上解释，注意：下面这个外键没有名字？

 

这面这个表有个char类型，及默认值，前面都没提到过！！

 

下面是多对多中间表的例子：40课，注意联合主键，

 

下面就是各表之间的关系：

 

2）范式：设计的规范。需要遵循的规范

六种范式！！！！范式越高，数据冗余越小。一般遵循前三种方式基本就OK了。先介绍，看下面的例子理解。

1NF：每列都是不可分割的原子数据项

2NF：在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）

无法理解，要先理解几个概念：

    1.函数依赖：好累，42课，10分25秒

    学号->姓名：姓名依赖于学号，学号可以唯一确定姓名

    学号+课程名称->确定分数：学号+课程名称为属性组

    2 完全函数依赖

    学号+课程名称->确定分数

    3 部分函数依赖

    学号+课程名称->姓名（只需学号就行，课程名称多余，所以称为部分依赖。这个有什么用？）

    4 传递函数依赖：

    A->B, B->C，C传递函数依赖于A  学号->系名->系主任

    5 码：

    如果一个表中，某属性或属性组被所有属性所依赖，那么称为该表的码

    例：学号不是码，通过学号不可以唯一确定分数

        主属性：码属性组中的所有属性（fuck，直接用码属性不就行了，搞那么多概念干吗？）

        非主属性：其它

        学号+课程名称->分数，但是学号可以决定其它的系名、系主任等，所以学号+课程名称对于系名来说是部分依赖，如何消除？

 

    解决：把“学号+课程名称->分数”变成一张表，“学号+课程名称”是主属性；删除课程名称和分数的另一张表，学号是主属性

    但是还是存在两个问题！！（2NF就是要让非主属性完全依赖于主码，不能有部分依赖）

3NF：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

学号->系名->系主任，解决：增加系表

 

*******************下面这部分是怎么回事？********************************************

下面表格是否符合3范式？

系：不符合第一范式

 

存在的其它问题：

 

*******************上面这部分是怎么回事？********************************************

*****************************************************************************

数据库的备份和还原：

1 命令行方式

备份：mysqldump -uroot -p123456 db1 > d://a.sql   // 备份的是整个数据库

还原：先create database db1， 再用source a.sql就行了！！！

2 图形化工具方式：44课6分钟

*****************************************************************************

多表查询 46课

*****************************************************************************

事务

*****************************************************************************

DCL

*****************************************************************************

*****************************************************************************

*****************************************************************************