MySQL
文章目录
- MySQL
- 环境配置
- 数据库相关概念
- 数据库
- 数据表
- 数据记录
- SQL
- 分类
- 通用语法
- 方言
- 注意事项
- 表的创建
- 字符串类型必须指定长度
- 数据新增
- 数据修改
- 数据删除
- 数据的查询
- 条件查询
- 聚合函数
- 排序查询
- 分组查询
- where和having的区别?
- 分页查询
- MySQL
- 约束
- 单表约束
- 多表关系
- 一对一
- 一对多
- 多对多
- 多表查询
- 连接查询
- 交叉连接
- 内连接
- 外连接
- 子查询(sql嵌套)
- 自关联
- 视图
- 存储过程(了解)
- 输入参数
- 输出参数
- 存储函数和存储过程的区别?
- 触发器(了解)
- 事务
- 手动管理事务
- 事务提交方式
- 手动提交
- 自动提交
- 事务开启的时机
- 手动开启
- 自动开启
- 事务的四大特征
- 原子性
- 一致性
- 隔离性
- 持久性
- 隔离级别
- 脏读:一个事务读取到了另一个事务未提交的数据。
- 不可重复读:一个事务读取到了另一个事务已经提交的修改。
- 幻读/虚读:一个事务读取到了另一个事务已经提交的新增或删除操作。
- MySQL的引擎(了解)
- 索引(重点)
- 索引的作用:提升查询数据的效率。
- 语法
- 数据结构
- 最左匹配原则
- mysql建索引的原则(mysql优化)
- 锁(理解)
- InooDB的共享锁:
- InnoDB的排他锁:
- MyISAM的读锁(了解)
- MyISAM的写锁(了解)
- 乐观锁及悲观锁
是一个DBMS,数据库管理系统。
关系型数据库:数据按照不同的类型分表存储,数据表之间可以有一些关联关系。
环境配置
mysql的默认端口号为3306.
mysql登录命令: mysql -u用户名 -p密码。-p后不写密码直接回车,进入密码输入框。
数据库相关概念
数据库
存储和管理数据的仓库。
在MySQL中,可以创建多个数据库。数据库本质就是一个目录。
数据表
在一个数据库中,可以创建多张表。数据表本质就是文件。
数据记录
在表中,一行代表一个记录。记录就是文件中的数据。
实际开发中,一个实体类一般对应一张表。
一个实体类的实例对象就是表中的一行记录。
一个数据库一般对应一个web项目。但是从技术角度,一个web项目也可以对应多个数据库。
如果我们想要操作这些数据,必须通过MySQL的dbms系统进行操作。使用的语言就是SQL。
SQL
结构化的查询语言,用于操作数据库。
学习的要点:多写多练,不断提升熟练度。
分类
DDL:数据定义语言。定义数据库的结构、数据表的结构。
DML:数据操作语言。操作数据的增删改。
DQL:数据查询语言。用于查询数据。
DCL:数据控制语言。一般是DBA(数据库管理员)使用的。
通用语法
- 不区分大小写,推荐使用小写。
- 所有的sql语句必须以分号结尾。
- 注释:
通用的:–空格 注释内容
多行注释:/*注释内容*/
MYSQL的方言:# 注释内容
方言
在sql中,有标准语法。使用的比较多的是sql99标准。
在每个数据库中,都会在sql99标准的基础上进行语法的扩充。这些扩充的语法并不通用,仅在某个数据库可以使用,我们称之为方言。
注意事项
mysql中的字符集在使用时最好使用utf8mb4.
表的创建
表名尽量不要使用关键字,如果要使用,可以用反引号引起来。``
create table 表名(
列名 列的类型(列的长度),
...
);
列的类型:
int
double:可以写成double(10,3) 。这种写法代表浮点数一共长度为10,其中小数位置是3位。
date:日期,年月日
time:时间,时分秒
datetime:年月日时分秒都有的时间,可以为null。
timestamp:时间戳,年月日时分秒都有的时间。时间戳必须有值,不能为null。如果不给它设置值或者赋值为null,它会自动把当前系统时间填充进去。
字符串类型必须指定长度
char:固定长度的字符串。指定为多长就是多长,不足的部分自动补空格。
varchar:可变长度的字符串。指定的长度是最大长度,实际长度为存值的长度。
数据新增
insert into 表名 列的列表 values 值的列表…
数据修改
update 表名 set 列名=值,列2=值2… where 条件列表
数据删除
delete from 表名 where 条件列表
truncate关键字也能完成表数据的删除。和delete from 表名不加条件效果看上去差不多。
区别:delete会把符合条件的数据一条一条的逐行删除,效率比较低。是一个DML语句。
truncate是把表直接删除,然后重新建一张一模一样的表。相当于drop table再create table。效率比较高。是一个DDL语句。
数据的查询
select 列的列表 from 表名
在列的列表之前加上DISTINCT关键字,可以取出结果中的重复值。
在sql中,跟null做任何运算,得到的值都是null。
ifnull函数,作用为当指定的列值为null时,函数的返回值替换成替代值。否则返回值为原值。
有两个参数:
第一个参数代表列名
第二个参数代表替代值。
别名:每个列都可以通过as关键字起别名,as关键字可以省略。
条件查询
不等于: != <>
等于:=
in 从指定的列表中多选一
null值判断:null值不能使用 = 或者!=判断的。必须使用is null或者is not null。
模糊查询:关键字like。指定的值中的通配符:_代表一个任意字符,不能没有。%代表0个或多个任意字符。
如果直接写 like “小米”,相当于写 = “小米”。
聚合函数
聚合函数进行计算时,会自动忽略所有的null值。
count():可以写的形式:count(*) count(id) count(0),推荐使用count(*),统计所有列,只要有一列不为null,就会增加一。
max():最大值
min():最小值
sum():求和
avg():求平均值
注意:聚合函数会把多个列的值通过某种运算得到一个值。所以属于多列聚合为一列的操作。那么在查询时,得出的结果是一个整体。不能出现个体的信息。
使用子查询(sql嵌套)完成需求,查询最大价格的产品
select name,price from product where price = (select max(price) from product);
排序查询
关键字:order by 列名 排序方式
排序方式:升序asc,默认值。 降序desc。
如果涉及到多个列的排序,优先按照前面的列排序;如果列的值相同,则再按照后面的列排序。
分组查询
一般配合聚合函数一起使用。
关键字:group by 分组依据,可以指定多个列。
SELECT brand,name,SUM(price) FROM product GROUP BY brand,name;
where和having的区别?
- where是分组之前添加过滤条件,having是分组之后添加过滤条件。
- where不支持聚合函数,having支持聚合函数。
分页查询
关键字:limit 此关键字是mysql的方言。
使用方式:limit 参数1 , 参数2
参数1:开始的索引值,从0开始。
参数2:要查询的数据的条数。
limit 0,m 可以省略0,简写为limit m
实际分页查询时,经常根据页码数计算开始索引。
页码数===> 开始索引?
1 0-9
2 10-19
3 20-29
开始索引 = (页码数-1)* 页面中数据的条数,即等差数列
总结单表查询关键字顺序:select distinct … from … where … group by … having… order by …limit…
MySQL
约束
单表约束
- 主键约束:非空的,唯一的。一般用于作为数据的唯一标识。通常和业务分离。
主键在一张表中最多只能有一个,通常情况下表最好加入主键。
主键可以由多列组成,它还是一个主键。这种主键叫联合主键(复合主键)。
关键字:primary key
复合主键:
create table student(
sid int ,
cid int,
score double,
primary key (sid,cid) -- 复合主键的指定方式
);
自动增长:配合数字类型的主键使用。如果自增的列没有指定值,也就是赋值为null,则自动进行增长赋值。值不会使用已经用过的值,自增也是基于已使用的所有值的最大值自增。
关键字:auto_increment
- 唯一约束
当前列不能重复,一张表中可以加多个唯一约束。
唯一约束底层实现使用的唯一索引。
唯一约束并不是非空,所以可以指定null值,而且null值可以重复。
关键字:unique。 - 非空约束
当前列不能为null。
关键字:not null
多表约束
外键约束
外键:从我们的从表建立外键指向主表的主键,保证他们这两列的对应关系。
关键字:
constraint [约束名称] foreign key (从表中外键列的名称) references 主表的表名(主表的主键名)
alter table 表名 add constraint [约束名称] foreign key (从表中外键列的名称) references 主表的表名(主表的主键名)
外键列可以为null
多表关系
一对一
建表原则:
- 唯一外键
在任意一张表中添加外键指向另一张表的主键,然后把外键添加上唯一约束。 - 主键对应
人为使用逻辑控制,两张表对应数据的主键设置为相同的值。 - 合并表,一般一对一的表关系,可以把两张表合并成一张。
一对多
建表原则:一的一方是主表,多的一方是从表。在多的一方建立外键指向一的一方的主键。
多对多
建表原则:需要新建一张中间表,至少要有两个列,分别作为原来两张表的外键指向原来两张表的主键。
注:
- 中间表不是一定只有两个列,根据实际需求可以添加其他列。
- 中间表一般不单独建立主键,而是使用两个外键创建联合主键。
多表查询
连接查询
交叉连接
select * from user cross join category;
select * from user, category;
cross join:查询的结果为笛卡尔积。这种结果是所有组合关系的全排列,一般没有实际业务意义。
如果需要无中生有查询数据,可能会涉及到笛卡尔积。
大多数情况下,如果结果中有笛卡尔积的出现,意味着sql语句条件不完整,导致查询结果有错误。
内连接
查询的是多张表的交集部分。
语法:
- 显式内连接
明确的指定内连接的关键字编写sql
SELECT 列名列表 FROM 表名1 [INNER] JOIN 表名2 ON 关联条件 [INNER] JOIN 表名3 ON 关联条件... ;
- 隐式内连接
把内连接的关键字省略掉编写sql
select 列名列表 from 表名1,表名2,... where 关联条件1 and 关联条件2....
在关联查询时,如果多张表有明确的主外键关系,那么当关联查询时,首先要指定的就是主外键条件。否则会出现笛卡尔积。
外连接
查询的是多张表的交集部分及某一侧的全部数据。
左外连接、右外连接
此处的左和右是说的查询语句中外连接关键字两侧写的表名,写在左边的是左表,写在右边的是右表。左外连接的左表的数据是全的,右外连接的右表的数据是全的。左外连接和右外连接是可以互相转化的。
子查询(sql嵌套)
在sql语句中嵌套sql语句使用的编写方式。
sql是非过程性语言,每一句都是独立的。如果要使用其他sql执行的结果,只能使用sql嵌套。
sql嵌套本身也只是写了一句sql语句。
按照结果类型分类:
单行单列:把结果当成一个值使用即可。
多行单列:把结果当成一个单列集合使用,通常和in关键字一起使用。
多行多列:把结果当成一张表使用。
按照使用的位置分类:
where子句子查询:子句在where处,当成条件使用。
from子句子查询:把子句的结果当表使用。
select子句子查询:在select后的列表中嵌套sql,把查询的结果当条件使用。
-- select子句子查询的案例,以下两种写法效果相同
select id,number,(select name from user where id = uid) name from orderlist;
select o.id,o.number,u.name from orderlist o left join user u on o.uid = u.id;
自关联
一张表中,通过某些字段自己跟自己表中的其他数据有关联关系。这种关联关系叫做自关联。
查询时,要把一张表看成多张表对待。一定要分清楚主从关系。
多表查询的套路:
- 先明确所需要的数据来自于哪些表—确定from子句
- 梳理这些表中是否包含主外键关系,如果有,先指定主外键。—where子句的主外键确定
- 查看需求,分析是否有额外条件。— 确定where子句的其他条件
- 筛选最终结果中我们关心的列—确定select子句的列表
视图
定义:在数据库中的一张虚拟表,一般使用比较复杂的sql查询语句生成。视图中仅存储了查询的sql语句,并不会存储真实数据。
作用:
- 能够把一些非常常用的复杂sql缓存起来,供后期使用,简化开发流程。
- 能够屏蔽一些不想公开的数据,仅提供想暴露的数据出去。
- 一般会把视图做成只读视图,防止某些数据被修改。
语法
create view 视图名称 as 查询语句;
存储过程(了解)
sql本身是非过程性的语言,存储过程的语法就弥补了这项缺点。添加了过程性的语法元素。
存储过程没有返回值,但是可以通过输出参数模拟返回数据的效果。
输入参数
要求调用存储过程时,从外界传入到内部使用的参数。
输出参数
在调用存储过程时,指定该参数,参数可以在存储过程内进行赋值,然后携带到存储过程外。
输出参数指定时,一般使用会话变量,也就是开头时一个@符号的变量,这种变量在当前会话有效。
存储函数和存储过程的区别?
存储函数有返回值,而存储过程没有返回值。
存储函数可以在select子句中使用,类似于聚合函数。
触发器(了解)
定义:当关心的数据表中发生了增删改操作时,会自动触发触发器逻辑的执行。触发器可以在增删改之前或之后触发。
作用:数据完整性保证、日志记录、数据校验。
CREATE TRIGGER 触发器名称
BEFORE|AFTER INSERT|UPDATE|DELETE
ON 表名
FOR EACH ROW
BEGIN
触发器要执行的功能;
END$
BEFORE|AFTER:触发器为前置触发还是后置触发。前置触发意味着操作还没有生效之前先执行触发器,后置触发是操作完成之后再执行触发器。
INSERT|UPDATE|DELETE:哪个操作会触发当前触发器。
FOR EACH ROW:是否行级触发。如果此参数指定,则代表是行级触发器。如果一条语句会影响多条记录,则触发器会给每一行记录触发一次。
在执行的代码块中,使用new和old关键字可以获取新数据和旧数据。new和old代表的就是一行记录,可以使用.属性的方式获取其中的值。
事务
要求:掌握事务的定义及意义,和事务中隔离级别导致的一些问题。
定义:在一个业务中,多条sql语句组合成一个整体,这个整体要同时成功或同时失败,此时我们需要使用事务对这一组sql操作进行管理。
手动管理事务
命令
start transaction; -- 开启事务.开启事务之后,在事务终止之前,所做的所有操作都是临时性的。
-- 执行一组sql操作
rollback;-- 回滚。把数据库的状态恢复到开启事务的时候的状态,就是还原。
commit;-- 提交。把在事务中操作的所有内容真正写入数据库文件持久化保存。
-- 如果在事务没有提交或者回滚之前,会话中止了,事务会自动中止,事务中的操作自动回滚。
一个事务只能被终止一次。
事务提交方式
手动提交
执行commit指令
自动提交
MySQL数据库默认就是自动提交,Oracle数据库默认是手动提交。
事务开启的时机
手动开启
start transaction;
自动开启
如果当前我们没有处在一个事务中,当我们执行任意一条sql语句时,MySQL会自动开启一个事务。
在默认的自动提交状态下,每执行一条sql语句,都会先开启一个事务,执行完毕后自动提交此事务。
如果要管理一组sql,让它们同时成功和失败,必须把它们放到同一个事务中。
事务的四大特征
原子性
强调事务的不可分割,是一个整体。
一致性
事务操作前后,数据库要保持一致性不变。
隔离性
在事务并发的时候,事务之间理论上不应该互相影响。
实际操作中,考虑到效率问题,我们一般会设置不同的隔离级别。控制事务并发之间的影响。
持久性
当一个事务结束时,它造成的影响就会持久化存储到数据库文件中。
隔离级别
指的是事务之间隔离的程度,隔离的级别越高,并发时事务之间的影响就越低,但是执行效率也会随之降低。
read uncommitted:未提交读。可能引发脏读、不可重复读、幻读/虚读。
read committed:已提交读。可能引发不可重复读、幻读/虚读。Oracle的默认隔离级别
repeatable read :可重复读。可能引发幻读/虚读。MySQL的默认隔离级别
serializable:串行化。可以解决所有并发问题。
脏读:一个事务读取到了另一个事务未提交的数据。
不可重复读:一个事务读取到了另一个事务已经提交的修改。
幻读/虚读:一个事务读取到了另一个事务已经提交的新增或删除操作。
MySQL的引擎(了解)
mysql处理数据,包括存储、管理等各种技术的合体称之为引擎。
InnoDB:支持事务、支持外键、有表锁和行锁。
占据的存储空间大。默认的引擎。
MyISAM:不支持事务,不支持外键,有表锁。
效率高,业务简单的时候适用。
MEMORY:不支持事务,不支持外键,有表锁。数据存储在内存中,数据是临时的,断电丢失。一般用作缓存。
存储引擎是设置在表层级上的。
索引(重点)
使用特定的数据结构,把数据库中的数据进行重新排列存储。使用特定算法,可以在较短时间内稳定的获取我们预期的数据。
索引是建立在列上的。
索引的作用:提升查询数据的效率。
语法
create index 索引名称 on 表名(列的列表);
数据结构
B-Tree
每个磁盘块中保存了两个数据节点和三个指针。根据数据的区间,决定三个指针指向的后续磁盘块,分别为小于最小数据的,在两个数据之间的和大于最大数据的。根据算法可以减少2/3磁盘IO的次数。
缺点:每次读取磁盘块会额外读取出所有的数据,即使是用不上的数据。
B+Tree
非叶子节点中不存储数据,仅保持索引的key值及指针。通过算法最终找到叶子节点,才能获取到数据。这样就节省了遍历过程中获取数据的次数,从而提升效率。
叶子节点是按照链表的结构进行了排列,我们可以非常迅速的根据头和尾节点进行范围查询。
最左匹配原则
联合索引,匹配规则,是从最左侧的列进行匹配,如果有部分能匹配也会命中;但是如果最左侧列不能匹配,则不能命中索引。
mysql建索引的原则(mysql优化)
- 查询多,增删改少的大表建索引。
- 索引的列中的值越离散越好。
- 索引一定要建在常作为查询条件的列上,而不要滥用。
锁(理解)
作用:在并发的情况下解决共享资源抢占冲突的问题。
InooDB的共享锁:
语法:select语句 lock in share mode;
特性
- 共享锁加上之后,其他事务数据将只能读取,不能修改
- 共享锁可以加多个,多个锁都释放掉之后才能修改
- 默认情况下,索引列加锁时是行锁,非索引列加锁时是表锁。
- 加锁操作在事务中进行,当事务结束后,自动释放当前事务中的共享锁。
InnoDB的排他锁:
语法:select语句 for update;
特性
- 排他锁加上之后,其他事务数据将不能加锁进行查询,也不能修改。普通查询(不加锁的查询是可以的)
- 排他锁加上之后,其他事务不能添加任意的锁。
- 默认情况下,索引列加锁时是行锁,非索引列加锁时是表锁。
- 加锁操作在事务中进行,当事务结束后,自动释放当前事务中的排他锁。
MyISAM的读锁(了解)
语法:lock table 表名 read;
特性:
MyISAM的锁是表级锁。
加上锁之后,所有连接都只能读取数据。
解锁:unlock tables;
MyISAM的写锁(了解)
语法:lock table 表名 write;
特性:
MyISAM的锁是表级锁。
加上锁之后,其他连接都不能进行读写操作。
解锁:unlock tables;
乐观锁及悲观锁
悲观锁是使用技术手段加锁的机制保证操作并发时的安全。
乐观锁不会加锁,而是使用逻辑手段尝试操作数据,如果真的在操作过程中因为其他线程操作导致数据发送改变,则进行重试直到成功为止。
如果增删改非常少,查询非常多,此时加锁冲突的概率不大,可以使用乐观锁减少系统资源的占用以提升性能。