MySQL数据库知识点归纳

MySQL数据库知识点归纳

1.数据库操作

这是最基本的数据库创建，在这个基础上我们还能为它添加上数据库的属性：

create database 数据库名;

比如为它设置utf-8字符集：

create database 数据库名 character set 'utf-8';

又比如为它加上utf-8编码的排序规则：

create database 数据库名 character set 'utf8mb4' collate 'utf8mb4_0900_ai_ci';

创建好数据库后我们就能操作自己的数据库了

操作数据库的方式有以下几种：

查看所有数据库：

show databases；

查看创建好的数据库：

show create database 库名；

修改数据库：

alter database 库名 default character set 编码方式 collate (要修改的)编码方式_bin

删除数据库：

drop database 库名；

2.数据表操作

数据表的创建：

create table 数据表名(
	`字段名` 数据类型 [属性] [索引] [注释]
)[表类型] [字符集设置] [注释];

示例：创建学生表：

create table students(
	id int primary key auto_increment,
	s_name char(10) not null,
	s_sex  char(1) not null default 男,
	s_age  int not null
)；

• 学生表中的 int，char等属于MySQL数据库的数据类型

• auto_increment，not null，default等属于MySQL数据库的字段属性

MySQL数据库的数据类型大致分为以下几类：

• int–整数值
  • tinyint–小整数值
  • bigint–大整数值
• float–单精度浮点数
• double–双精度浮点数
• char–定长字符串
• varchar–变长字符串
• date–日期值
• time–时间值

字段属性：

• auto_increment–整数值自动增长
• not null–字段不能为空
• default–字段默认值

操作数据表：

查看数据表：

show create table 表名（查看表名称，编码，定义语句）
desc 表名（查看字段信息）

修改数据表:

修改表名:

alter table 旧表名 rename 新表名；

修改字段名:

alter table 表名 change 旧字段名 新字段名 数据类型；

修改字段类型:

alter table 表名 modify 字段名 数据类型；

添加字段:

alter table 表名 add 新字段名 数据类型 [约束条件] [first|after 已存在字段名]；

删除字段:

alter table 表名 drop 字段名；

修改字段排列位置:

alter table 表名 modify 字段名1 数据类型 first|after 字段名2

删除数据表:

drop 表名；

数据表的约束：

• SQL 约束用于规定表中的数据规则。

• 如果存在违反约束的数据行为，行为会被约束终止。

• 约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）

创建学生表：

create table students(
	id int primary key auto_increment,//学号
	s_name char(10) not null,//学生姓名
	s_sex  char(1) not null default 男,//学生性别
	s_age  int not null, CHECK (VALUE > 3 and VALUE < 7 )//学生年龄
    s_idnumber int unique，//身份证号码
    s_address varchar(30),//学生住址
    c_id int,//班级编号
    foreign key(c_id) references class(c_id)//创建外键，将班级编号和班级表的id建立外键关系
);

创建班级表：

create table class(
	id int primary key auto_increment,
	c_name char(10) not null
);

以上的示例中用到了如下约束：

• NOT NULL - 指示某列不能存储 NULL 值。
• UNIQUE - 保证某列的每行必须有唯一的值。
• PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
• FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
• CHECK - 保证列中的值符合指定的条件。
• DEFAULT - 规定没有给列赋值时的默认值。

这些约束可以规范插入数据时的行为，更好的管理数据。

3.数据表的增删改查

增删改查又被称为CRUD：

CRUD是指在做计算处理时的增加(Create)、读取查询(Retrieve)、更新(Update)和删除(Delete)几个单词的首字母简写。主要被用在描述软件系统中DataBase或者持久层的基本操作功能。

数据的增加操作：

insert into 数据表名(字段名1，字段名2) values(值1，值2)

数据的删除操作：

delete from 数据表名 where 条件

注意：如果没有条件将会删除整张表的数据，但不会删除表结构；

数据的修改操作：

update 数据表名 set 字段名1=值1，字段名2=值2 where 条件

数据的查询操作：

select 查询的字段 from 数据表名 where 条件

简单查询语法：

select [distinct] *|字段名1，字段名2…
from 表名
[where 条件表达式]
[group by 字段名 [having 条件表达式2]]
[order by 字段名 [asc|desc]]
[limit [offset] 记录数]

distinct:可选参数，用于剔除查询结果中重复的数据。

group by：可选参数，用于对查询结果指定字段进行分组。

having ：用于对分组结果进行过滤。

order by：用于对查询结果指定字段进行排序。排序方式有asc【升序】和desc【降序】控制。

limit：用于限制查询结果显示的数量。后面跟两个参数，offset表示偏移量，记录数表示返回的结果条数。

where 条件查询：

• in 集合 表示：指定字段是否在指定集合中，满足结果把该字段所在的记录查询出来。

  如：select * from student where id in {1,2，3}；字段id是否在集合123中，结果把id为1，2，3的记录查询出来。

• between and 表示：某个字段的值是否在指定范围内。

  如：select * from student where id between 2 and 5；查询id范围在2到5内的记录。

• like 表示：判断两个字符串=相等的意思，有时候是对字符串进行模糊查询的意思。

  如：select * from student where 字段名 [not] like ‘匹配字符串’;

• % 表示通配符：匹配任意长度的字符串。

  如：select * from student where name [not] like “张%”; 查询学生表名字有张开头的记录。

• _ 表示单个通配符：只能匹配一个字符，如要匹配多个需要添加多个_ 且不能有空格。

  如：select * from student where name [not] like “张_”; 查询学生表名字有张开头的两位数记录。

• \ 表示转义字符：如果要匹配百分号或下划线就需要\进行转义。

• or 表示：不同于and ，只需满足其中一个条件即可。注意：and的优先级高于or。

高级查询-聚合函数

函数名称	说明
count（）	返回某列的行数 ，用来统计记录的条数，select count（*）from 表名
sum（）	返回某列值的和，用于求某个字段所有值的总数，select sum(字段名) from 表名
avg（）	返回某列的平均值，用于求某个字段所有值的平均值，select avg(字段名) from 表名
max（）	返回某列的最大值，用于求某个字段所有值的最大值，select max(字段名) from 表名
min（）	返回某列的最小值，用于求某个字段所有值的最小值，select min(字段名) from 表名

为表和字段取别名

在查询表操作时，如果表名很长使用起来不方便时可以为表取个别名，用这个别名代替表名

select * from 表名 [as] 别名；

为字段取别名

select 字段名 [as] 别名 from 表名；

取别名也可用空格将表名和字段名隔开然后起别名，这样也会非常方便，但是不利于阅读；

复杂查询-多表查询

多表查询用于两表，三表甚至多表进行查询数据，功能强大，使用率高；

我们可以通过一个案例来理解多表查询：

创建学生表：

create table students(
	id int primary key auto_increment,//学号
	s_name char(10) not null,//学生姓名
	s_sex  char(1) not null default 男,//学生性别
	s_age  int not null, CHECK (VALUE > 3 and VALUE < 7 )//学生年龄
    s_idnumber int unique，//身份证号码
    s_address varchar(30),//学生住址
    c_id int,//班级编号
    foreign key(c_id) references class(c_id)//创建外键，将班级编号和班级表的id建立外键关系
);

创建班级表：

create table class(
	id int primary key auto_increment,//班级编号
	c_name char(10) not null，//班级名称
    c_number not null//班级人数
);

查询学号为1的学生所属班级的人数：

select c.c_number from students s
[inner]join class c
on s.c_id=c.id
where s.id=1;

通过这个查询语句就能够将两张数据表的内容一同查询出来了，效果非常好。

我们可以看出这个查询语句的格式为：

select 字段名 from 数据表名 别名
[inner]join 数据表名 别名
on 连接条件
where 条件；

inner join 为内连接，on为连接条件，inner能够省略，join默认为内连接

除了内连接外，还有外连接。

外连接又分为左外连接和右外连接，我们又将其简称为左连接和右连接。

下面我们通过一个案例来理解外连接的作用：

创建学生表：

create table students(
	id int primary key auto_increment,//学号
	s_name char(10) not null,//学生姓名
	s_sex  char(1) not null default 男,//学生性别
	s_age  int not null, CHECK (VALUE > 3 and VALUE < 7 )//学生年龄
    s_idnumber int unique，//身份证号码
    s_address varchar(30),//学生住址
    c_id int,//班级编号
    foreign key(c_id) references class(c_id)//创建外键，将班级编号和班级表的id建立外键关系
);

创建班级表：

create table class(
	id int primary key auto_increment,//班级编号
	c_name char(10) not null，//班级名称
    c_number not null//班级人数
);

查询班级编号=2的班级所有学生信息：

select s.* from students s
left join class c
on s.c_id=c.id
where c.id=2

左连接能将学生表的所有数据查出来，并将班级表符合条件的数据查出来。

当然右连接也是这个作用，并且能与左连接互换。

左右连接互换：

select s.* from class c
right join students s
on s.c_id=c.id
where c.id=2

这样就将左右连接互换啦，只是语法不同，但作用相同。

数据表中还有一种复杂查询：子查询

子查询指查询语句嵌套在另一个查询语句内部中的查询。

在查询结果时，先执行子查询中的语句作为外层查询的过滤条件

下面我们还是通过一个案例来理解子查询：

创建学生表：

create table students(
	id int primary key auto_increment,//学号
	s_name char(10) not null,//学生姓名
	s_sex  char(1) not null default 男,//学生性别
	s_age  int not null, CHECK (VALUE > 3 and VALUE < 7 )//学生年龄
    s_idnumber int unique，//身份证号码
    s_address varchar(30),//学生住址
    c_id int,//班级编号
    foreign key(c_id) references class(c_id)//创建外键，将班级编号和班级表的id建立外键关系
);

创建班级表：

create table class(
	id int primary key auto_increment,//班级编号
	c_name char(10) not null，//班级名称
    c_number not null//班级人数
);

查询学号=1234和学号=2345的学生的班级信息

select * from class
where id in
(select c_id from students
where id=1234 or id=2345);

子查询与多表查询类似，也能将多个表的数据查询出来。

3.事务

1、事务管理
保证同一个事务的操作具有同步性。
严格遵守4个特性：
原子性 ：一个事务必须视为不可分割的最小工作单元。
一致性：事务将数据库的一种状态转变成下一种状态。
持久性：一旦提交永久保存到数据库。
隔离性：也称并发控制，可串行化，锁等，多用户访问时，为每个用户开启一个事务，互不干扰。
开启事务：start transaction；
提交事务：commit；提交事务后，数据库数据才发生改变。
回滚事务：rollback；不想提交事务可以取消相关事务（也称回滚）
2、事物的隔离级别
1、read uncommitted（读未提交）事务中最低级别，可以读取未提交的数据，也称脏读。危险少用。
2、read commited（读提交）大多数数据库默认隔离级别，只能读取已经提交的内容。但无法避免重复读和幻读。
3、repeatable read（可重复读）Mysql默认事务隔离级别。可避免不可重复度和脏读的问题，确保同一个事务的多个实例在并发读取数据时，会看到同样的数据。
4、serializable（可串行化）事务的最高隔离级别。他它对强行对事务进行排序，使之不会发生冲突，实际上是在每个读的数据行前加个锁，这种级别可能导致大量的超时现象和锁竞争。
脏读：指事务读取了另一个事务未提交的数据。
不可重复度：事务中两次查询的数据不一致，原因是在查询过程中其他事务做了更新操作。
幻读：一次事务查询中数据的条数不一致。
可串行化：在每个读的数据行前加上锁，使之不可相互冲突，因此会造成大量超时现象。

4.视图

视图是从一个表或多个表导出来的表，是一张虚拟的表，并且表结构和数据都依赖基本表。
它不仅可以看到不仅能看到存放在基本表中的数据，并且还可以像操作基本表中的数据一样对视图存放的数据进行增删改查。
优点：简化查询语句、安全性、逻辑数据的独立性

创建视图：

create view 视图名称 [（属性清单）)]
as 一个完整的查询语句

我们通过一个小案例来理解视图

创建学生表：

create table students(
	id int primary key auto_increment,//学号
	s_name char(10) not null,//学生姓名
	s_sex  char(1) not null default 男,//学生性别
	s_age  int not null, CHECK (VALUE > 3 and VALUE < 7 )//学生年龄
    s_idnumber int unique，//身份证号码
    s_address varchar(30),//学生住址
    c_id int,//班级编号
    foreign key(c_id) references class(c_id)//创建外键，将班级编号和班级表的id建立外键关系
);

创建班级表：

create table class(
	id int primary key auto_increment,//班级编号
	c_name char(10) not null，//班级名称
    c_number not null//班级人数
);

创建一个名为view_student_all的视图存储班级编号为1的班级所有学生信息：

create view view_student_all as
select s.* from student s
join class c
on s.c_id=c.id
where c.id=1;

这个视图存储了班级编号为1的班级所有学生信息，我们也能通过操作视图来修改表中的数据；

查看视图：

show view

可以使用 describe 视图名；查看视图的所有字段信息，简写 desc 视图名；

匹配查看的视图名称：

show table status like '视图名称'

查看视图时的定义语句和字符编码：

show create view 视图名

修改视图：

create or replace view 视图名 as SQL语句

alter view 视图名 as SQL语句

更新视图：

update 视图名 set 字段名=值

insert into 表名 value（值）

delete from 表名 where 字段名=值

删除视图：

drop view 视图名1，视图名2；

5.数据库的高级操作：

数据库备份与还原：

备份单个数据库：

mysqldump -u用户名 -p密码 数据库名称 [数据库表名1，数据库表名2...] >存放路径sql文件名称.sql

mysqldump -u用户名 -p密码 --all-database>存放路径sql文件名称.sql

备份多个数据库：

mysqldump -u用户名 -p密码 --database 数据库名称 [数据库表名1，数据库表名2...] >存放路径sql文件名称.sql

数据的还原：

mysqldump -u用户名 -p密码 [数据库名称] <存放路径sql文件名称.sql