MySQL数据库（基础篇二）

一、简单查询

1.1 数据的备份还原

• 导出：先对数据库进行备份，然后提取备份文件中的sql语句
• 导入：
  • 方法一：创建和原始数据同名的数据，然后执行从备份文件中导出的sql语句【此方法不要修改sql语句】
  • 方法二：在数据库中直接运行导出的sql语句【此方法往往需要先修改数据库文件】

1.2 简单查询

MySQL的查询操作

• 单表查询：也就是从一个表中找数据，也叫做 简单查询
• 多表查询：联合多个表查询数据，也叫做 链接查询

1.2.1 查询的格式

select 查询的内容 from 表名 where 条件

where后面的条件的写法

>                                大于

>=                              大于等于

<                                小于

<=                              小于等于

<>                              不等于

!=                               不等于

between...and...        判断范围

is null                         是空

is not null                   不是空

like                             模糊查询

in                                判断某个字段是否在给定的集合中

1.2.2 通配符%

• 表示 任意多个任意字符
• 可以表示0个、1个或者是多个任意字符

案例：查看students表

1、age大于20

        select * from students where age>20

2、age 20-30

        select * from students where age between 20 and 30

3、name是空

        select * from students name is null

4、name包含q

        select * from students where name like "%q%"

5、age 是3 或5 或7

        select * from students where age in (3,5,7)

1.2.3 排序查询格式

select 查询内容 from 表名 where 条件 order by 字段名

案例：

1、将所有学生按照年龄排序

        select * from students order by age

2、将所有学生按照年龄排序，并且是升序

        select * from students order by age asc

3、将所有学生安装年龄排序，并且是降序

        select * from students order by age desc

4、显示所有学生信息，按照年龄和班级id排序，升序显示

        select * from students order by age,classid asc

1.2.4 指定输出几条记录：

• 默认输出所有符合条件的记录
• limit 数字

格式：select 查询内容 from 表名 where 条件 order by 字段名 limit 数字

1.2.5 聚合函数

• sum()        求和
• avg()         求平均值
• max()        求最大值
• min()         求最小值
• count()       统计记录条数

案例：

1、计算所有学生的年龄和

        select sum(avg) from students

1.2.6 去重：去除重复的字段

关键字：distinct

案例：

1、将年龄小于50的用户，安装年龄排序，并且去除重复的字段

select distinct age from students where age<50 order by age

1.2.7 分组

group by 字段

分组后加条件

having 

完整的查询格式

select * from 表名 where 条件 order by 字段 limit 数字 group by 字段 having 条件

二、查询进阶

2.1 链接查询

将两张或者多张表联合起来进行查询，这就是链接查询【join】，链接查询又分为：

• 交叉链接
  • 表中的每一行，分别和其他表的每一行，组成一个新的行
  • 新表的行数是两个表的行数相乘，列数是两个表的列数相加
• 自然连接
  • 将多个表中，符合条件进行链接
• 外连接
  • 左外连接
  • 右外链接

2.2 交叉链接的结果

• 得到笛卡尔积表
• 这个表中有大量的无用数据

select * from 表1，表2；

笛卡尔积表，行数是两个表的行数相乘，列数是两个表的列数相加

2.3 自然链接的结果

• 在交叉链接的基础上，通过加条件，可以得到自然链接

select * from 表1,表2 wheere 表1.字段=表2.字段

2.4 字段别名

• 给字段取一个新的名字
• 显示结果中，字段名会直接用新的名字显示出来

select 字段名 as 新名字 from 表名

2.5 表别名

select 字段 from 表名 as 新名

2.6 使用自然链接存在的问题：

• 会导致数据丢失
• 解决方法：用左外链接、右外链接
  • 左表：前面的
  • 右表：后面的

2.7 左外链接

• 显示结果以左表为准
• 也就是说，左表中的数据会全部出现，右表中的数据，有就显示，没有就显示为空

select * from 左表 left join 右表 on 条件

2.8 右外链接

• 显示结果以右表为准
• 右表中的数据会全部出现，左表中的数据，有就显示，没有就显示为空

select * from 左表 right join 右表 on 条件

三、外键约束

外键约束是数据库中用于维护数据完整性的一种重要机制。

作用：

• 维护两个表之间的关联关系，确保数据的一致性和完整性。

概念：

• 主键：是用于在一个表中唯一标识一个字段
• 外键：外键是另外一个表的主键，通过使用外键，可以将两个表关联起来

表与表之间的关系的类型：

• 一对一
• 一对多
• 多对多

案例：

# 创建表tb2
create table tb2(
classid int primary key,    # 第一个字段是classid，是主键
name char(30)               # 第二个字段是name
)

# 创建表tb1
create table tb1(
id int primary key,         # 第一个字段是id，是主键
name char(30),              # 第二个字段是name
classid int,                # 第三个字段是classid，是整数
foreign key(classid) references tb2(classid);    # 将classid这个字段转换成外键，指向表tb2的classid这个字段

这个外键约束，可以确保在插入或更新tb1表中的数据时，classid字段的值必须在tb2表的classid字段中存在。这样可以保证数据的一致性和完整性，避免出现无效的外键值。

四、视图

视图：view

• 视图可以认为是一张表
• 这个表是存在内存中的虚表，断电就会消失

作用：

• 通过视图，可以让用户只能访问数据库中的一部分数据（给用户授权）

格式：

        create view 视图名 as select 语句

案例：给用户user3进行授权，让用户只可以访问id小于5的数据

# 第一步：创建视图，视图中包含的是id小于5的数据
create view v1 as select * from students where stuid<5;

# 第二步：查看当前系统中的表，会发现多一个叫做v1的表，这个表其实就是视图
show tables；

# 第三步：给用户user3授权，让user3仅仅可以访问视图中的数据
# 授权的格式：grant 权限 on 库名.表名 to 用户名@主机 identified by "密码";
grant all on hellodb.v1 to "user3"@"localhost" identfied by "123";

# 刷新授权表
flush privileges

五、事务

简介：

• 将多个sql语句，组合成一个整体，这个整体就是一个事务，事务中的所有语句要么都执行，要么都不执行
• 事务中的语句，如果只有部分执行成功，那么就需要将这个事务进行回滚操作
  • 回滚：将数据恢复到执行操作之前的状态
• 通过使用事务，可以保证数据的一致性

事务的操作：

• 提交事务
• 回滚事务

提交事务：

• 让事务中多个语句，真正的生效
• 如果使用了事务，那么在事务提交之前，其他用户是看不到事务中的操作的

回滚事务：

• 恢复到执行语句之前的状态

操作的命令格式：

begin           启动事务

commit        提交事务，提交之后，事务自动结束

rollback       回滚事务，回滚之后，事务自动结束

注意：

• 一般情况下，在MySQL客户端中，每个sql语句，都是一个单独的事务，而且事务会自动提交
• 但是，在后期的python中，需要手动去提交/回滚事务的

六、扩展命令

6.1 union

作用：将两个sql语句执行结果连接起来一起显示

格式：【sql语句1】 union 【sql语句2】

6.2 concat （拼接）

作用：将两个字段输出到一块

格式：select concat(字段名,字段名) from 表名

6.3 length （返回长度）

作用：返回查询结果长度

格式：select length(字段名) from 表名

6.4 group_concat（拼接字符串）

作用：输出到一行

格式：select group_concat(字段名,拼接的字符) from 表名