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;
8.5.4 explain执行计划
explain 或者 desc命令获取 mysql 如何执行 select 语句的信息,包括在 select 语句执行过程中表如何连接和连接的顺序。
语法:
-- 直接在select语句之前加上关键字 explain / desc
explain select 字段列表 from 表名 where 条件 ;
8.5.5 索引使用
在学习索引使用之前,首先进行索引效率的验证,来看索引是否会提高效率。
在未进行建立索引执行,执行下列sql语句,查看sql的耗时。
select * from tb_sku where sn='100000003145001';
可以看到,执行这一句sql语句所耗时间为4.41秒。
下面为字段sn创建索引(创建索引就是构建一种数据结构,这里是B+树数据结构)
create index idx_sku_sn on tb_sku(sn);
建立索引后,查询相同的语句,耗时仅0.01秒。
8.5.6 索引使用原则
最左前缀法则
如果索引联合了多列(联合索引),要遵循最左前缀法则,最左前缀法则指的是查询从索引的最左列开始,并且不跳过索引中的列,如果跳过某一列,索引将部分失效(后面的字段索引失效)。(因此,在实际查询时候,必须要满足最左边的列字段要存在,如果最左边的列不存在,则会跳过整个联合索引)