MySQL数据库知识点归纳

最新推荐文章于 2024-04-28 17:43:31 发布

programmer-xq

最新推荐文章于 2024-04-28 17:43:31 发布

阅读量236

点赞数 4

文章标签：数据库 mysql

本文链接：https://blog.csdn.net/2301_77886316/article/details/131057209

版权

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 表名

下面通过几个案例来查看以下聚合函数的用法：

创建学生表：

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 count(*) from students
where c_id=1;

查询所有班级人数：

select sum(c_number) from class;

查询每个班的平均人数：

select avg(c_number) from class;

查询班级人数最多的班级：

select max(c_id) from class;

查询班级人数最少的班级：

select min(c_id) from class;

单行处理函数

lower	转换小写
upper	转换大写
substr	取子串（substr(被截取的字符串,起始下标,截取的长度)）
length	取长度
trim	去空格
str_to_date	将字符串转换成日期
date_format	格式化日期
format	设置千分位
round	四舍五入
rand()	生成随机数
ifnull	可以将null转换成一个具体值
concat	字符串拼接
case…when…then…when…then…else…end	当什么时候怎么做

lower

lower，转换小写，upper效果与其相反
查询员工姓名并将其转为小写

select lower(ename) from emp;

substr

substr 取子串（substr(被截取的字符串,起始下标,截取的长度)）
查询员工的姓名但只截取其姓名的第一位进行显示
当我们使用下标从0开始时，所截去的字符串为空，说明在数据库中，起始的下标地址为1

select substr(ename,0,1) as ename from emp;
select substr(ename,1,1) as ename from emp;

length

length 取长度
查询员工姓名并使用length函数取其长度

select length(ename) as ename from emp;

concat

concat 字符串拼接
查询员工编号及姓名并将其拼接到一起，组成一个字段

select concat(empno,ename) as result from emp;

trim

trim 去除字符串前后空白

select * from emp where ename='  king ';            查不出数据
select * from emp where ename=trim('  king ');    去除前后空格后可以查出数据

为表和字段取别名

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

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.索引：

1. 为什么要加索引？

一般的应用系统，读写比例在10:1左右，而且插入和一般的更新操作很少出现性能问题，遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，所以查询语句的优化显然是重中之重。

在数据量和访问量不大的情况下，MySQL的访问时非常快速的，是否加索引对访问影响不大。但是当数据和访问量剧增的时候，就会发现MySQL变慢，甚至down掉，这就必须考虑优化SQL了。给数据库建立正确合理的索引，是MySQL优化的一个重要手段。

索引的目的在于提高查询效率，可以类比字典，如果要查"mysql"这个单词，我们肯定需要定位到m字母，然后从前往后找到字母y，再找到剩下的"sql"。如果没有索引，那么你可能需要把所有单词看一遍才能找到你想要的。出了词典，生活中随处可见索引的例子，如火车站的车次表、图书的目录等。它们的原理都是一样的，通过不断的缩小想要获取数据的范围来筛选出想要的结果，同时把随机的时间变成顺序的时间，也就是我们总是通过一种查找方法来锁定数据。

在创建索引时，需要考虑哪些列会用于SQL查询，然后为这些列创建一个或多个索引。事实上，索引也是一种表，保存着主键或索引字段，以及一个能将每个记录只想实际表的指针（非聚簇索引和聚簇索引，一个在索引中保存的是字段值的地址，一个保存的就是字段的值）。数据库用户是看不到索引的，它们只是用来加速查询的。数据库搜索引擎使用索引来快速定位记录。

INSERT 和 UPDATE 语句在拥有索引的表中执行会花费更多的时间，而 select 语句却会执行得更快，这是因为在进行插入和更新时，数据库也需要插入或更新索引值。

2. 索引的创建

索引的类型：

UNIQUE (唯一索引)：不可能出现相同的值，可以有NULL值；
INDEX (普通索引)：允许出现相同的索引内容；
PRIMARY KEY (主键索引)：不允许出现相同的值；
FULLTEXT INDEX (全文索引)：可以针对值中的某个单词，但效率确实不敢恭维；
组合索引：实质上是将多个字段建到一个索引里，列值的组合必须唯一

创建索引：

CREATE INDEX index_name ON table_name (column_name(length));

添加索引:

ALTER TABLE 表名 ADD 索引类型(unique, primary key, fulltext, index) [索引名] (字段名)；

应用于数据表创建后添加。

3.索引的删除

drop index index_name on table_name;  -- 普通索引和唯一索引可用此方法删除，主键索引不能用此方法删除

alter table table_name drop index index_name; -- 主键索引不能用此方法删除

alter table table_name drop primary key; -- 可以删除主键索引，前提是主键没有自增 auto_increment，如果有自增将无法删除，需先删除自增

4.索引的缺点

索引虽然好处很多，但过多的索引可能带来相反的问题，索引也是有缺点的：

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行 INSERT、UPDATE 和 DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件；
建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在给大表上建多种组合索引，索引文件会膨胀很大.

索引只是提高效率的一个方式，如果MySQL有大数据量的表，就要花时间研究建立最优的索引，或优化查询语句。

6.数据库的高级操作：

数据库备份与还原：

备份单个数据库：

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

programmer-xq

关注

4
点赞
踩
2

收藏

觉得还不错? 一键收藏
2
评论
MySQL数据库知识点归纳

show create view 视图名。
复制链接

扫一扫

MySQL数据库知识点归纳

MySQL数据库知识点归纳

1.数据库操作

这是最基本的数据库创建：

在这个基础上我们还能为它添加上数据库的属性，

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

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

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

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

查看所有数据库：

查看创建好的数据库：

修改数据库：

删除数据库：

2.数据表操作

数据表的创建：

示例：创建学生表：

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

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

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

字段属性：

操作数据表：

查看数据表：

修改数据表:

修改表名:

修改字段名:

修改字段类型:

添加字段:

删除字段:

修改字段排列位置:

删除数据表:

数据表的约束：

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

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

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

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

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

3.数据表的增删改查

增删改查又被称为CRUD：

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

数据的增加操作：

数据的删除操作：

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

数据的修改操作：

数据的查询操作：

简单查询语法：

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

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

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

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

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

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

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

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

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

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

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

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

高级查询-聚合函数

单行处理函数

lower

substr

length

concat

trim

为表和字段取别名

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

select * from 表名 [as] 别名；

为字段取别名

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

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

复杂查询-多表查询

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

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

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

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

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

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

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

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

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

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