MySQL查漏补缺加复习

最新推荐文章于 2022-12-13 17:52:10 发布

哦吼~

最新推荐文章于 2022-12-13 17:52:10 发布

阅读量242

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/qq_42727102/article/details/102520106

版权

数据库专栏收录该内容

9 篇文章 0 订阅

订阅专栏

MySQL（DOS下）

查询所有数据库的名称：show databases;

查询某个数据库的字符集(查看某个数据库的创建)：show create database 数据库名称;

DDL：数据定义语言，用来定义数据库对象：数据库，表，列等。关键字：create、drop、alter

数据库：

1.创建数据库：create databases 数据库名称;

create databases if not exists 数据库名称;

create databases 数据库名称 character set 字符编码集;

2.修改数据库的字符集：alter database 数据库名称 character set 字符集名称;

3.删除数据库：drop database 数据库名称;

drop database if exists 数据库名称;

4.查询当前正在使用的数据库名称：select database();

5.使用数据库:use 数据库名称;

表：

1.创建表：create table(

列名1 数据类型1 [约束],

列名2 数据类型2 [约束],

列名n 数据类型n [约束]

);

复制表：create table 表名 like 被复制的表名;

2.删除表：drop table 表名;

drop table if exists 表名;

3.修改表的表名：alter table 表名 rename to 新的表名；

修改表的字符集：alter table 表名 character set 字符集的名称；

添加一列：alter table 表名 add 列名数据类型 [约束]；

修改列名称：alter table 表名 change 旧列名新列名数据类型 [约束]；

修改列类型：alter table 表名 modify 旧列名新数据类型；

删除列：alter table 表名 drop 列名；

4.查询某个数据库中所有表的名称：show tables;

查询表结构：desc 表名;

5.数据库的备份和还原

备份：mysqldump -u用户名 -p密码数据库的名称 > 保存的路径

还原：

登录数据库
创建数据库
使用数据库
执行文件。 source 文件路径

DML：数据操作语言，用来对数据库中表的数据进行增删改。关键字：insert、delete、update等

1.添加数据：insert into 表名（列名1，列名2，…） values（值1，值2，…）；

其他见我的MySQL其他博客

2.删除数据：delete from 表名 [where 条件]；不加条件的话数据全部删除，有多少条记录就会执行多少次删除操作，，效率低下，所以用下面的方式

truncate table 表名；删除全部数据，然后再创建一个一模一样的空表

3.修改数据：update 表名 set 列名1=值1，列名2=值2，…[where 条件]; 不加条件的话，表中所有记录全部修改

4.排序查询：order by 子句

order by 排序字段1，排序方式1，排序字段2，排序方式2… 当前边的条件值相等时，按照后边的条件排

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

count（列名称）：计算个数

max（列名称）：计算最大值

min（列名称）：计算最小值

sum（列名称）：计算和

avg（列名称）：计算平均值

聚合函数的计算会排除null值

解决：①选择不包含非空的列进行计算

②IFNULL函数 IFNULL(列名，0) 当这个列的值为null的话就用0替换

6.分组查询：group by 分组字段；

注意：分组之后查询的字段：分组字段、聚合函数其它字段可以加但无意义

where和having的区别？

where在分组之前进行限定，如果不满足条件，则不参与分组。having在分组之后进行限定，如果不满足结果，则不会被查询出来。
where后不可以跟聚合函数，having可以进行聚合函数判断

7.分页查询：limit 开始的索引，每页查询的条数;

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

DQL：数据查询语言，用来查询数据库中表的记录（数据）。关键字：select，where等

1.查询表中数据：select * from 表名；//查询所有数据

去除重复的结果集：select distinct 列字段 from 表名；

2.模糊查询：like：占位符：_：单个任意字符

%：多个任意字符

3.唯一约束：（值不能重复）unique 删除时：alter table 表名 drop index 列字段名；使用简单的alter 。。。modify不行唯一约束限定的列的值可以有多个null

4.主键约束：primary key 删除时：alter table 表名 drop primary key; 使用简单的alter。。。modify不行

5.自动增长：insert into 表名 values（null，xxx）；

6.外键约束：foreign key 让表与表产生关系，保证数据的正确性

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0OGlKLnl-1570862239345)(C:\Users\xuan\AppData\Roaming\Typora\typora-user-images\1570809471671.png)]$

删除外键：alter table 表名 drop foreign key 外键名称；

添加外键：alter table 表名 add constraint…；

7.级联更新：当被关联表修改时，对应的关联表的对应列的值也自动修改

级联删除：当被关联表对应行删除时，对应的关联表的相应行也被删除

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8dOsO02f-1570862239346)(C:\Users\xuan\AppData\Roaming\Typora\typora-user-images\1570810836603.png)]$

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-PSEVq140-1570862239346)(C:\Users\xuan\AppData\Roaming\Typora\typora-user-images\1570810592710.png)]$

8.一对多的关系：实现方式：在多的一方建立外键，指向一方的主键

9.多对多的关系：实现方式：需要借助第三张中间表。中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键。

10.一对一的关系：实现方式：可以在任意一方添加唯一（unique）外键指向另一方的主键

其他见我的MySQL其他博客

DCL：数据库控制语言，用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT，REVOKE等

管理用户：

添加用户:create USER ‘用户名’@‘主机名’ identified by ‘密码’；
删除用户:drop user ‘用户名’@‘主机名’;
修改用户密码：update user set password = password(‘新密码’) where user = ‘用户名’;

set password for ‘用户名’@‘主机名’ = password(‘新密码’);
- 在mysql中，忘记了root用户的密码怎么办？
  - cmd------>net stop mysql 停止mysql服务（管理员身份运行）
  - 使用无验证方式启动mysql服务：mysqld --skip-grant-tables
  - 新打开一个dos窗口输入mysql直接进入
  - 修改密码
  - 打开widows任务管理器手动结束掉mysqld.exe
  - 打开mysql服务用新的密码重新登录
查询用户：select * from USER；

通配符：%表示可以在任意主机使用用户登录数据库

授权：

查询权限：show grants for ‘用户名’@‘主机名’;
授予权限：grant 权限列表 on 数据库.表名 to ‘用户名’@‘主机名’; 权限列表为：select，update等等

通配符：所有权限的通配符：ALL

所有表：* 比如上面的就可以写为： .
撤销权限：revoke 权限列表 on 数据库名.表名 from ‘用户名’@‘主机名’;

三大范式：

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DBxPhTqD-1570862239346)(C:\Users\xuan\AppData\Roaming\Typora\typora-user-images\1570847663837.png)]$

第一范式： $[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bqR2uL2y-1570862239347)(C:\Users\xuan\AppData\Roaming\Typora\typora-user-images\1570847680227.png)]$

每一列都是不可分割的原子数据项

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

函数依赖：A---->B，如果通过A属性（属性组）的值，可以确定唯一B属性的值。则称B依赖于A

如：学号—>姓名（学号，课程名称）---->分数

完全函数依赖：A---->B，如果A是一个属性组，则B属性值的确定需要依赖于A属性组中所有的属性值

例如：（学号，课程名称）---->分数
部分函数依赖：A---->B，如果A是一个属性组，则B属性值的确定只需要依赖于A属性组中某一些值即可。

例如：（学号，课程名称）—>姓名
传递函数依赖：A----->B，B---->C，如果通过A属性（属性组）的值，可以确定唯一B属性的值，再通过B属性（属性组）的值，可以确定唯一C的值，则称C传递函数依赖于A

例如：学号—>系名，系名—>系主任
码：如果在一张表中，一个属性或属性组，被其他所有属性所完全函数依赖，则称这个属性（属性组）为该表的码

例如：该表中的码为：（学号，课程名称）

主属性：码属性组中的所有属性

非主属性：除过码属性组的属性

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8u177ynI-1570862239347)(C:\Users\xuan\AppData\Roaming\Typora\typora-user-images\1570848897818.png)]$

**第三范式：**在2NF基础上，任何非主属性不依赖于其他非主属性（在2NF基础上消除传递函数依赖）

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DsVtaUF0-1570862239348)(C:\Users\xuan\AppData\Roaming\Typora\typora-user-images\1570849274658.png)]$

巴斯-科德范式（BCNF）：在3NF基础上，消除主属性对码的部分函数依赖和传递函数依赖。对于BCNF，在主码的任何一个真子集都不能决定于非主属性。

多表查询：

笛卡尔积：有两个集合A,B，取这两个集合的所有组成情况。即表A有5条数据，表B有3条数据，则多表查询笛卡尔积为3乘5为15条数据。

要完成多表查询，要消除无用的数据。下面三种方式：

多表查询的分类：

内连接查询：

隐式内连接：使用where条件消除无用的数据

显示内连接：select 字段列表 from 表名1 [inner] join 表名2 on 条件；

基本思维逻辑：

从哪些表中查询数据、条件是什么、查询哪些字段

外连接查询：

左外连接：查询的是左表所有记录以及与右表的交集部分（即满足条件的记录）

select 字段列表 from 表1 left [outer] join 表2 on 条件；

右外连接：select 字段列表 from 表1 right [outer] join 表2 on 条件；

子查询：查询中嵌套查询，称嵌套查询为子查询

子查询的不同情况：

子查询的结果是单行单列的：子查询可以作为条件，使用运算符去判断。运算符：> >= < <= =
子查询的结果是多行单列的：子查询可以作为条件，使用运算符in来判断
子查询的结果是多行多列的：子查询可以作为一张虚拟表参与查询

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kAvvvibr-1570862239348)(C:\Users\xuan\AppData\Roaming\Typora\typora-user-images\1570852555375.png)]$

练习： $[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VSyqraAX-1570862239348)(C:\Users\xuan\AppData\Roaming\Typora\typora-user-images\1570854353627.png)]$

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rXqhfGMJ-1570862239349)(C:\Users\xuan\AppData\Roaming\Typora\typora-user-images\1570854395636.png)]$

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3UBJ93xY-1570862239349)(C:\Users\xuan\AppData\Roaming\Typora\typora-user-images\1570854411421.png)]$

**事务：**如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lQQuHQ5E-1570862239350)(C:\Users\xuan\AppData\Roaming\Typora\typora-user-images\1570854618416.png)]$

1.开启事务：start transaction；

2.回滚事务：rollback；

3.提交事务：commit；

MySQL数据库中事务默认自动提交：一条DML（增删改）语句会自动提交一次事务。

修改事务的默认提交方式：

查看事务的默认提交方式：select @@autocommit； 1代表自动提交 0代表手动提交

修改：set [@@]autocommit = 0；

事务的四大特征：

原子性：是不可分隔的最小操作单位，要么同时成功，要么同时失败
持久性：当事务提交或回滚后，数据库会持久化的保存数据。
隔离性：多个事务之间相互独立。
一致性：事务操作的前后，数据总量不变。

**事务的隔离级别：**多个事务之间隔离的，相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题。

存在的问题：

脏读：一个事务，读取到另一个事务中没有提交的数据。
不可重复读（虚读）：在同一个事务中，两次读取到的数据不一样。
幻读：一个事务操作（DML）数据表中的所有记录，另一个事物添加了一条数据，则第一个事务查询不到自己的修改。

隔离级别：

read uncommitted：读未提交产生问题：脏读、不可重复读、幻读
read committed：读已提交产生问题：不可重复读，幻读（Oracle默认）
repeatable read：可重复读产生问题：幻读（MySQL默认）
serializable：串行化可以解决所有问题（相当于锁表）
注意：隔离级别从小到大安全性越来越高，但是效率越来越低

数据库查询隔离级别：select @@tx_isolation;

设置数据库隔离级别：set global transaction isolation level 级别字符串(比如：read committed);