Mysql知识点汇总

muyeyou

已于 2024-03-23 10:55:19 修改

阅读量393

点赞数 10

文章标签： mysql 学习数据库 database

于 2024-01-07 16:18:00 首次发布

本文链接：https://blog.csdn.net/muyeyou/article/details/135440058

版权

1.关系型数据库

关系型数据库就是由多张相互连接的二维表组成的数据库。（通过表存储数据的数据库）

2.SQL语句

2.1 SQL语句的分类

sql语句分为DDL(数据定义语言，用来定义数据库对象例如-数据库，表，字段),DML(数据库操作语言，用来对数据库表中的数据进行怎删改查),DQL(数据库查询语言，用来查询数据库表的记录),DCL(数据库控制语言，用来创建数据库用户，控制数据库的访问权限)四类。

2.2 DDL语句

DDL(数据定义语言，用来定义数据库对象例如-数据库，表，字段)

DDL——数据库

2.2.1.查询所有数据库

show databases;

2.2.2.查询当前正在使用数据库是哪一个数据库

select database();

2.2.3.创建数据库（下面中括号里的条件都不是必须的，根据实际情况进行添加）

create database [if not exists] 数据库名 [default charset 字符集][collate 排序规则]

2.2.4.删除数据库

drop database [if exists] 数据库名称；

2.2.5.使用指定数据库

use 数据库名称；

DDL——表结构——查询

下面6，7，8操作前提是首先进行到数据库里面，就是先执行上面的操作5

2.2.6 .查询当前数据库所有表

show tables;

2.2.7. 查询表结构

desc 表名；

2.2.8.查询指定表的建表语句

show create table 表名；

DDL——表结构——创建

2.2.9.创建表

create table 表名（

字段1 字段1类型[comment 字段1注释],

字段2 字段2类型[comment 字段2注释],

字段3 字段3类型[comment 字段3注释],

.............

字段n 字段n类型[comment 字段n注释]

）[comment 表注释];

DDL——表操作——数据类型

数据类型主要分为三类：数值类型，字符串类型，日期时间类型。

DDL——表操作——修改

2.2.10.添加字段

alter table 表名 add 字段名类型(长度)[comment 注释][约束]；

2.2.11.修改指定字段的数据类型

alter table 表名 modify 字段名新数据类型(长度)；

2.2.12.修改字段名和字段类型

alter table 表名 change 旧字段名新字段名类型(长度)[comment 注释][约束]；

2.2.13.删除字段

alter table 表名 drop 字段名；

2.2.14.修改表名

alter table 表名 rename to 新表名；

2.2.15.删除表

drop table[if exists]表名;

2.2.16.删除指定表,并重新创建该表

truncate table 表名；

注意：在进行15，16删除表操作时，表中的全部数据都会被删除掉。

2.3 DML语句

DML(数据操作语言，用来对数据库中表中的数据进行增删改操作，与DDL区别可以理解为DDL是操作数据结构，DML是操作数据的。)

2.3.1.给指定字段添加数据

insert into 表名(字段1，字段2，.....)values(值1，值2，.......)；

2.3.2.给全部字段添加数据

insert into 表名 values(值1，值2，....)；

2.3.3.批量添加数据

insert into 表名(字段1，字段2，.....)values(值1，值2，.....)，(值1，值2)，（值1，值2）；

insert into 表名 values(值1，值2，.....)，(值1，值2)，（值1，值2）；

注意事项：

插入数据时后，指定字段的顺序和值的顺序是一一对应的。

字符串和日期类型的数据应该包含在引号中。

插入的数据大小，应该在字段的规定范围内。

2.3.4.修改数据

update 表名 set 字段1=值1，字段2=值2，.......[where 条件]；（如果不加修改条件，默认会修改整张表的所有数据）

2.3.5.删除数据

delete from 表名 [where 条件]；（不加删除条件，默认会删除表中所有数据）。

2.4.DQL语句

DQL（数据查询语言，说白了一句话就是，用来查询数据库中表的记录）

DOL-语法

select 字段列表

from 表名列表

where 条件列表

group by 分组字段列表

having 分组后条件查询

order by 排序字段列表

limit 分页参数

1.基本查询

2.条件查询(where)

3.聚合函数(count，max，min，avg，sum)

4.分组查询(GROUP BY)

5.排序查询(ORDER BY)

6.分页查询(LIMIT)

2.4.1.查询多个字段

select 字段1，字段2，字段3......from 表名；

select * from 表名；（在实际开发中，尽量不要写*，可以把全部字段都写出来，原因1.不直观.2影响效率）

2.4.2.设置别名

select 字段1[as 别名1]，字段2[as 别名2].......from 表名；（as可以省略）

2.4.3.去除重复记录

select distinct 字段列表 from 表名；

2.4.4.条件查询

select 字段列表 from 表名 where 条件；

条件包括比较运算和逻辑运算

注意：用between....and....时需要注意，between后跟的是最小值，and后跟的是最大值

2.4.5.聚合函数

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

2.常见的聚合函数：

注意：使用聚合函数的时候，所有的null值是不参与聚合函数运算的。

3.例子：(函数后面括号里填写的是表的字段名称)

统计企业员工id总数量

select count(id) from emp；

2.4.5.分组查询

select 字段列表 from表名 [where 条件] group by 分组字段名 [having 分组后过滤条件]；

where和having区别

1.where是分组之前进行过滤,不满足where条件,不参与分组;而having是分组之后对结果进行过滤。

2.where不能对聚合函数进行判断，而having可以进行判断。

例子：

根据性别分组，统计男性员工和女性员工的平均年龄

select gender,count(*) from tb_user group by gender;

查询年龄小于45的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址

select workaddress，count(*) address_count from emp where age<45 group by workadress having address_count >=3；

注意事项：

1.执行顺序：where>聚合函数>having

2.分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。

2.4.6 排序查询

select 字段列表 from 表名 order by字段1 排序方式1，字段2 排序方式2；

排序方式：

asc:升序（默认值）

desc:降序

注意：如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。

2.4.7.分页查询（limit）

select 字段列表 from表名 limit 起始索引，查询记录数；

注意：

起始索引从0开始，起始索引=（查询页码-1）*每页显示记录数

分页查询是数据库的方言，不同的数据库有不同的实现，mysql中是limit

如果查询的是第一页数据，起始索引可以省略，直接简写为limit10。

2.4.8.DQL的执行顺序

注意，这里说的是执行顺序，和上面讲的书写顺序是不一样的，在执行sql语句的时候，第一步首先会执行from语句找到对应的表。第二布执行where语句，找到查询条件。第三步执行group by以及having来找到分组以及分组之后的条件。第四步执行select语句，决定返回那些字段。第五步执行order by 和limit(分页查询)。执行顺序图如下：

在这里，可以通过查询语句来证明上述的执行顺序。

例如：

select e.name，e.age，from emp e where e.age>15 order by age asc；

上述sql语句在执行的时候没有报错，说明先执行from 语句，把emp表的名称替换成e,所以可以成功执行后面的语句。

2.5.DCL语句

DCL：主要用来管理数据库用户，控制数据库的访问权限。

2.5.1.查询用户

在mysql中，用户信息，用户所拥有的权限信息都是存放在系统数据库mysql中的user表中，因此，我们可以先进行系统数据库mysql中，然后把用户信息查询出来。

use mysql；

select * from user；

2.5.2 创建用户

create user '用户名'@'主机名' identified by '密码'；

例子：

1.创建用户itcast,只能够在当前主机localhost访问，密码123456；

create user 'itcast'@'localhost' identified by '123456'；

2.创建用户itcast1,可以在任意主机访问该数据库，密码123456；

create user 'itcast1'@'%' identified by '123456'；

2.5.3 修改用户密码

alter user '用户名'@'主机名' identified with mysql_native_password（mysql的加密方式） BY '新密码'；

例子

1.修改用户itcast 的访问密码1234；

alter user 'itcast'@'%' identified with mysql_native_password by '1234'；

2.5.4 删除用户

drop user '用户名'@'主机名'；

DCL-权限控制

1.查询权限

show grants for '用户名'@'主机名'；

2.授予权限

grant 权限列表 on 数据库名.表名 to '用户名'@'主机名'；

3.撤销权限

revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名'；

注意：

1.多个权限之间，使用逗号分隔。

2.授权时，数据库和表名可以使用*进行通配，代表所有。

3.函数

函数就是一段可以直接被另一段程序调用的程序和代码。（大部分函数已经内置在mysql中，我们只需要根据实际情况进行调用就行）

3.1字符串函数

常见的字符串函数

select lower('Hello')；

3.2 数值函数

常见的数值函数

例子

通过数据库的函数，生成一个六位数的随机验证码。

select ；lpad(round(rand()*1000000，0)，6，'0')；

3.3 日期函数

常见的日期函数

3.4 流程函数

流程函数可以在sql语句中实现条件筛选，从而提高语句的效率。

常见的流程函数如下：

4.约束

1.概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。

2.目的：保证数据库中数据的正确性，有效性和完整性。

3.分类

一个字段可以对应多个约束

4.1外键约束

外键约束是让两张表的数据之间建立连接，从而保证数据的一致性和完整性。

子表：具有外键的表称为子表，外键所关联的表称为父表。

4.2创建外键语法

1.在创建表结构的时候创建外键

create table 表名（

字段名数据类型，

...........

[constraint][外键名称] foreign key(外键字段名) references 主表(主表列名)

）；

2.在修改表结构的时候创建外键

alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(主表列名)；

4.3 删除外键

alter table 表名 drop foreign key 外键名称；

4.4 外键约束

外键约束主要是为了保证数据的一致性和完整性。

删除/更新

前两种行为是默认的。

添加约束

alter table 表名 add constraint 外键名称 foreign key(外键字段) references 主表名(主表字段名) on update cascade on delete cascade；

5.多表查询

由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：

一对多（多对一）

多对多

一对一

5.1 多表查询

多表查询就是从多张表中查询数据。

5.2 多表查询分类

连接查询分为内连接和外连接

内连接：相当于查询A，B交集部分数据。

内连接分为隐式内连接和显示内连接。

隐士内连接查询语法：

select 字段列表 from 表1，表2 where 条件.....；

显示内连接查询语法：

select 字段列表 from 表1 [inner] join 表2 on 连接条件....；

外连接：左外连接：查询左表所有数据，当然也包括两张表交集数据。

左外连接语法：

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

相当于查询表1所有的数据，同时也包含表1和表2交集部分的数据。

右外连接：查询右表所有数据，当然也包括两表交集数据。

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

相当于查询表2所有的数据，同时也包含表1和表2交集部分的数据。

自连接：当前表与自身的连接查询，自连接必须使用表别名。

select 字段列表 from 表A 别名A join 表A 别名B on 条件......；

自连接查询，可以是内连接查询，也可以是外连接查询。自连接查询时候必须给表起别名。

联合查询：（union，union all）（union all是将多次的查询结果直接合并，不能够自动去重，而union是将两次查询结果合并后再去重）（联合查询多张表返回的字段列数和类型都要保持一致）

对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。

select 字段列表 from表A

union[all]

select 字段列表 from 表B.....；

子查询：

sql语句中嵌套select语句，称为嵌套查询，又称为子查询。

select * from t1 where column1=(select column1 from t2)；

子查询外部语句可以是insert/update/delete/select中的任何一个。

根据子查询结果不同，可以分为：

1.标量子查询（子查询结果为单个值就是一行一列，可以是数字，字符串，日期等）

2.列子查询（子查询结果为一列,可以是多行）

3.行子查询（子查询结果为一行，可以是多列）

select * from emp where (salary,managerid) = (select salary, managerid from emp
where name = '张无忌')；

4.表子查询（子查询结果为多行多列）

select * from emp where (job,salary) in ( select job, salary from emp where name =
'鹿杖客' or name = '宋远桥' )；

根据子查询位置，分为：where之后，from之后，select之后。

联系：

#2.查询员工的姓名，年龄，职位，部门信息(显式内连接)
#表：emp,dept
#连接条件：emp.dept_id=dept.id
#查询条件：e.age<30
select e.name,e.age,e.salary,e.job,d.name from emp e inner join dept d on e.dept_id = d.id where e.age<30;

6.事务

事务是一组操作的集合，它是一个不可分隔的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

6.1事务操作

6.1.1 查看/设置事务提交方式

查看事务提交反式（1为自动提交，0为手动提交）

select @@autocommit；

设置系统提交方式

set @@autocommit=0；

提交事务

commit；

回滚事务

rollback；

开启事务

start transaction 或 begin（执行这句话表明我们已经开始手动控制提交事务）

提交事务

commit

回滚事务

rollback

6.2 事务的四大特性

1.原子性（atomicity）事务是不可分隔的最小操作单元，要么全部成功，要么全部失败。

2.一致性(consistency) 事务完成时，必须使所有的数据都保持一致的状态。

3.隔离性（Isolation）数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。

4.持久性（Durability）事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

6.3 并发事务

并发事务问题

脏读：一个事务读到另外一个事务还没有提交的数据。

不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称为不可重复读。

幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了"幻影"。

6.4 事务的隔离级别

√：会出现。❌：不会出现

read uncommitted:性能最高，但是数据安全性最低

serializable:性能最低，但是数据安全性最高。

查看事务隔离级别

select @@TRANSACTION_ISOLATION；

设置事务隔离级别

set [session][global] transaction isolation level {read uncommitted | read committed | repeatable read | serializable}(session:代表仅针对当前客户端窗口有效。global:针对所有的客户端窗口有效)

注意：事务的隔离级别越高，数据越安全，但是性能越低。

7.存储引擎

存储引擎即使存储结构，建立索引，更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以，存储引擎也可以被称为表类型。

1.指定存储引擎：

create table 表名(

字段1 字段1类型[comment 字段1注释]

..............)engine=innodb[comment 表注释]

默认的存储引擎是innodb

2.查看当前数据库支持的存储引擎

show engines；

7.1 innoDB存储引擎

存储引擎特点

InnoDB

InnoDB是一种兼顾高可高性和高性能的通用存储引擎，在MYSQL 5.5 之后，InnoDB是默认的mysql存储引擎。

特点：

DML：操作遵循ACID模型，支持事务。（ACID指的是事务的四大特性：原子性，一致性，隔离性，持久性）

行级锁，提高并发访问性能。

支持外键约束，保证数据完整性和正确性。

innoDB文件特性：

innoDB逻辑结构：

7.2 MyISAM存储引擎

特点：

不支持事务，不支持外键

支持表锁，不支持行锁

访问速度快

文件特性：

8.索引

suoyin（index）是帮助MySQL高效获取数据的数据结构（有序）。

索引的优势和劣势：

优点总结：提高查询效率，提高排序效率。

劣势总结：

8.1索引结构

平常所说的索引，如果没有特别指明，都是指B+树组织结构的索引。

B-tree（多路平衡查找树）

B+Tree（所有的元素都会出现在叶子结点）

Hash索引

思考题：

为什么InnoDB存储引擎选择使用B+tree索引结构？

相对于二叉树，层级更少，搜索效率更高

8.2 索引分类

思考题

8.3 索引语法

8.3.1 创建索引

create [unique|fulltext](如果不加，默认是常规索引) index index_name on table_name(index_col_name（表中对应的字段名，就是选定那一张表中的哪一个字段创建索引），...（这里省略号意思是一个索引是可以关联多个字段的）)；

8.3.2 查看索引

show index from table_name；

8.3.3 删除索引

rop index index_name on table_name；

8.4 案例

#查看当前表所包含的索引
show index from tb_user;
#name字段为姓名字段，该字段的值可能会重复，为该字段创建索引。
create index idx_user_name on tb_user(name);
#phone手机号字段的值，是非空，且唯一的，为该字段创建唯一索引。
create unique index idx_user_phone on tb_user(phone);
#为profession、age、status创建联合索引。
create index idx_user_pro_age_sta on tb_user(profession,age,status);
#为email建立合适的索引来提升查询效率。
create index idx_user_email on tb_user(email);
#删除索引
drop index idx_user_email on tb_user;

8.5 SQL性能分析

8.5.1.sql执行频率

根据具体的访问频次可以确定要优化的方向。

这里再执行一次查询语句，然后再查看访问频次。

8.5.2.慢查询日志

根据8.5.1sql执行频次只能知道查询频次（不同情况也有可能是删除，修改等等）的频次比较高，但是无法知道具体是哪一个查询/修改/...语句的执行频次比较高，所以这里需要用到慢查询日志，来定位sql语句。

#可以通过一条语句查看当前慢查询日志的状态

show variables like 'slow_query_log';

如果要开启慢查询日志，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：
# 开启MySQL慢日志查询开关
slow_query_log=1
# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2

配置完毕之后，通过以下指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息

#重启mysql服务指令

systemcrl restart mysqld;

#慢查询日志存放目录
/var/lib/mysql/localhost-slow.log。
然后，再次查看开关情况，慢查询日志就已经打开了。

8.5.3 profile详情

show profiles能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过hava_profiling参数，能够看到当前MySQL是否支持profile操作：

#查看当前数据库是否支持profile操作

select @@hava_profiling;

返回结果为YES，表名支持。

查看profiling是否开启。

返回结果是0，表明此时是关闭的状态。

默认profiling是关闭的，可以通过set语句在session/global级别开启profiling。

set profiling=1;

-- 查看每一条SQL的耗时基本情况
show profiles;
-- 查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
-- 查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;