Mysql基础
MySql概述
几个名词
学习mysql之前,先弄清几个名词的意思。
名称 | 简介 | 简称 |
---|---|---|
数据库 | 存储数据的仓库,数据是有组织的进行存储 | DataBase(DB) |
数据库管理系统 | 操纵和管理数据库的大型软件 | DataBase Management System(DBMS) |
SQL | 操作关系型数据库的编程语言,定义了一套操作关系型数据库统一标准 | Structured Query Language(SQL) |
常见的数据库管理系统:Oracle、Mysql、SQLServer、DB2。
SQL
DDL
数据定义语言,用来定义数据库对象。
数据库对象:数据库,表,字段
数据库操作
-- 查询所有数据库
show databases;
-- 查询当前数据库
select database();
-- 创建
-- create database [if not exists] 数据库名 [default charset 字符集][collate 排序规则]
create database if not exists landzDB charset=utf8mb4 collate=utf8mb4_general_ci;
-- 删除
-- drop database [if exists] 数据库名;
drop database if exists landzDB;
-- 使用
-- use 数据库名;
use landzDB;
/**
collate 类型
utf8_general_ci 不区分大小写,这个你在注册用户名和邮箱的时候就要使用。
utf8_general_cs 区分大小写,如果用户名和邮箱用这个 就会造成不良后果
utf8_bin:字符串每个字符串用二进制数据编译存储。 区分大小写,而且可以存二进制的内容
utf8_general_ci校对速度快,但准确度稍差。
utf8_unicode_ci准确度高,但校对速度稍慢。
*/
表操作
-- 查询当前数据库所有的表
show tables;
-- 查询表结构
desc 表名;
-- 查询指定的建表语句
show create table 表名;
-- 创建表
create table 表名(
字段1 字段1类型[comment 字段1注释],
字段2 字段2类型[comment 字段2注释],
字段3 字段3类型[comment 字段3注释],
字段4 字段4类型[comment 字段4注释],
...
字段n 字段n类型[comment 字段n注释]
)[comment 表注释];
-- 创建表 标识无符号有符号
create table tb_user(
id int unsigned comment '用户id'
)
-- 查询字段注释
select COLUMN_NAME,COLUMN_COMMENT,COLUMN_KEY
from
INFORMATION_SCHEMA.Columns
where
table_name = 'tb_emp'
and table_schema='landzDB'
datetime 和 timestamp的区别
https://www.jb51.net/article/223960.htm
思考题:char和varchar的区别?
char和varchar的区别char 长度固定,插入性能好,但是费空间。
varchar 长度不固定,每次都要计算长度,性能差,但是省空间。
-- 修改数据类型
alter table 表名 modify 字段名 新数据类型;
-- 修改字段名和字段类型
alter table 表名 change 旧字段名 新字段名 类型 [comment 注释][约束];
-- 添加字段
alter table 表名 add 字段名 类型 [comment 注释][约束];
-- 删除字段
alter table 表名 drop 字段名;
-- 修改表名
alter table 表名 rename to 新表名;
-- 删除表
drop table [if exists] 表名;
-- 删除并重建
truncate table 表名;
DML
数据操作语言,用来对数据库中的数据进行增删改。
添加数据
-- 给指定字段添加数据
insert into 表名(字段1,字段2,...) values(值1,值2,...);
-- 给全部字段添加数据
insert into 表名 values(值1,值2,...);
-- 批量添加数据
insert into 表名 (字段1,字段2,...) values(值1,值2,...),(值1,值2,...),(值1,值2,...);
insert into 表名 values(值1,值2,...),(值1,值2,...),(值1,值2,...);
1、插入数据时,指定的字段顺序需要与值得顺序是一一对应的
2、字符串和日期类型需要用’'包裹
3、插入数据的长度要在字段规定的范围内
修改数据
--修改数据
update 表名 set 字段1 = 值1, 字段2 = 值2 [where 条件];
删除数据
--删除数据
delete from 表名 [where 条件];
/**
要有where 否则删除整个表中的数据
**/
要有where 否则删除整个表中的数据
DQL
数据查询语言,用来查询数据库中的记录。
/**
select
字段列表
from
表名列表
where
条件列表
group by
分组字段列表
having
分组后条件列表
order by
排序字段列表
limit
分页参数
**/
-- 条件列表
>,>=,<,<=,=,!=或<>,between 0 and 100,in (1,2,3),like _(单个字符)%(匹配任意字符)
,is null
-- 逻辑运算符
and 或 &&,or 或 ||, not 或 !
-- 聚合函数
count,max,min,avg,sum
-- eg.
select sum(age) from tb_person;
--分组查询
select 字段列表 from 表名 where 条件 group by 分组字段 having 分组后的过滤条件
/**
执行顺序:where> 聚合函数> having
分组之后,查询的字段一般为聚合函数和分组字段,查询其他字段无任何意义
**/
执行顺序:where> 聚合函数> having
分组之后,查询的字段一般为聚合函数和分组字段,查询其他字段无任何意义
练习题:表emp,字段有 workaddress(工作地点),gender(性别),age(年龄),name(姓名)。
1、统计男性员工和女性员工的平均年龄
2、查询年龄小于45且员工数量大于等于3的工作地址
--1、
select gender,avg(age) from emp group by gender;
--2、
select workaddress,count(*) address_count from emp where age <45 group by workaddress having address_count >=3;
select 字段列表 from 表名 where 条件 order by 字段1 排序方式,字段2 排序方式;
-- asc 升序(默认)
-- desc 降序
如果多字段排序,当第一个字段相同时,才会根据第二个字段进行排序
select 字段列表 from 表名 where 条件 limit 起始索引,查询记录条数;
1、索引从0开始,起始索引 = (页码-1)*每页显示记录数
2、分页是数据库的方言,不同的数据库有不同的实现,mysql中使用limit
3、如果查询是第一页的数据,起始索引可以省略,如limit 10;
执行顺序
-- 编写顺序
select..from..where..group by.. having.. order by.. limit..
-- 执行顺序
from.. where.. group by...having..select..order by.. limit..
DCL
数据控制语言,用来创建数据库用户、控制数据库的访问权限。
1、用户管理
-- 查询用户
use mysql;
select * from user;
-- 创建用户
create user ’用户名’@‘主机名‘ identified by '密码';
-- 修改用户密码
alter user ’用户名’@‘主机名‘ identified with mysql_native_password by '密码';
-- 删除用户
create user ’用户名’@‘主机名‘;
2、权限控制
grant 权限列表 on 数据库名.表名 to ’用户名’@‘主机名‘;
revoke 权限列表 on 数据库名.表名 to ’用户名’@‘主机名‘;
函数
字符串函数
函数 | 功能 |
---|---|
CONCAT(s1,s2,…Sn) | 字符串拼接,将S1,S2…Sn拼接成一个字符串 |
LOWER(str) | 将字符串str全部转成小写 |
UPPER(str) | 将字符串str全部转成大写 |
LPAD(str,n,pad) | 左填充,用字符串pad对str的左边进行填充,达到n个字符串长度 |
RPAD(str,n,pad) | 右填充,用字符串pad对str的右边进行填充,达到n个字符串长度 |
TRIM(str) | 去掉字符串str头部和尾部的空格 |
SUBSTRING(str,start,len) | 返回从字符串str从start位置起的len个长度的字符串 |
-- emp 表中,企业工号 workno ,统一5位数,不足5位,前面补0。例如:1号员工,工号应为00001。
update emp set workno = lpad(workno,5,'0');
数值函数
函数 | 功能 |
---|---|
CEIL(x) | 向上取整 |
FLOOR(x) | 向下取整 |
MOD(x,y) | 返回x/y的模 |
RAND() | 返回0~1内的随机数 |
ROUND(x,y) | 返回参数x的四舍五入的值,保留y位小数 |
日期函数
函数 | 功能 |
---|---|
CURDATE() | 返回当前日期 |
CURTIME() | 返回当前时间 |
NOW() | 返回当前日期和时间 |
YEAR(date) | 获取指定date的年份 |
MONTH(date) | 获取指定date的月份 |
DAY(date) | 获取指定date的日期 |
DATE_ADD(date,INTERVAL expr type) | 返回一个日期/时间加上一个时间间隔expre后的时间值 |
DATEDIFF(date1,date2) | 返回起始时间date1和结束时间date2之间的天数 |
流程函数
函数 | 功能 |
---|---|
IF(value,t,f) | 如果value为true,返回t,否则返回f |
IFNULL(value1,value2) | 如果value1不为空,返回value1,否则返回value2 |
CASE WHEN [val1] THEN [res1] …ELSE[default] END | 如果val1为true,返回res1,否则返回default的值 |
CASE[expr] WHEN [val1] THEN [res1] …ELSE[default] END | 如果expr的值等于val1,返回res1,否则返回default的值 |
练习题:查询emp表中的员工姓名name和工作地址address分级(北京/上海–>一线城市,其他–>二线城市)
select name,(case address when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end)as '工作地址'
from emp;
练习题:查询score表中学生姓名和数学math,英语english,语文chinese的成绩等级(85及以上优秀,60包含~85 及格,60以下不及格)
select name,
(case when math>=85 then '优秀' when math>=60 then '及格' else '不及格' end)'数学',
(case when english>=85 then '优秀' when english>=60 then '及格' else '不及格' end)'英语',
(case when chinese>=85 then '优秀' when chinese>=60 then '及格' else '不及格' end)'语文'
from score;
约束
概述
1、概念:约束是作用于表中字段的规则,用于限制存储在表中的数据。
2、目的:保证数据库中数据的正确性,有效性,完整性
约束分类
约束 | 作用 | 关键字 |
---|---|---|
非空约束 | 限制数据不能为NULL | NOT NULL |
唯一约束 | 保证数据唯一,不重复 | UNIQUE |
主键约束 | 行数据的唯一表示,非空且唯一 | PRIMARY KEY |
默认约束 | 保存数据时,如果没有指定数据的值,采用默认值 | DEFAULT |
检查约束 | 保证字段值满足某个条件 | CHECK |
外键约束 | 用来让两张表之间建立连接,保证数据的一致性和完整性。 | FOREIGN KEY |
-- 约束列子
create table tb_user(
id int auto_increment primary key comment 'id 唯一标示',
name varchar(10) not null unique comment '姓名',
age int check(age>=0&&age<=120) comment '年龄',
status char(1) default 1 comment '状态',
gender char(1) comment '性别',
org_id int comment '组织id',
constraint fk_user_org_id foreign key(org_id) references tb_dept(id)
);
-- 建表后,添加外键
alter table tb_user add constraint fk_user_org_id foreign key(org_id) references tb_dept(id);
-- 删除外键
alter table tb_user drop foreign key fk_user_org_id;
外键删除更新行为
alter table tb_emp add constraint fk_emp_org_id foreign key(org_id) references tb_org(id) on update set null;
--
alter table tb_emp
add constraint fk_emp_org_id foreign key (org_id) references tb_org (id) on update set null on delete set null;
多表
多表关系
一对多(多对一)
eg. 部门与员工
关系:一个部门多个员工,一个员工一个部门
实现:在多的一方建立外键,指向一的一方的主键
本例中:
员工表建立外键,指向部门表的主键。
多对多
eg.学生和课程
关系:一个学生可以选多门课程,一个课程可以被多个学生选择。
实现:通过创建中间表,中间表中包含两个外键,来关联两个主键。
本例中:创建选课表(中间表),包含学生id外键和课程id外键,分别指向学生表的主键和课程表的主键。
一对一
eg.商品和详情
关系:一个商品只能有一个详情
实现:在任意一方加入外键,指向另一方的主键,并且外键设置唯一约束。
多表查询
内连接
查询两个表交集的部分
-- 隐式内连接
select 字段列表 from 表1,表2 where 条件;
-- 显示外连接
select 字段列表 from 表1 [inner] join 表2 on 连接条件;
外连接
查询的某一个表和两表交集的部分
-- 左外连接,取左表和两表相交的数据
select 字段列表 from 表1 left join 表2 on 连接条件;
-- 右外连接,取右表和两表相交的数据
select 字段列表 from 表1 right join 表2 on 连接条件;
自连接
自己连自己
-- 自连接
select 字段列表 from 表1 别名1 join 表1 别名2 on 条件;
练习题:查询emp中,员工和所属领导的名字
表结构:id,name,managerid。
select a.name '员工',b.name '领导' from emp a join emp b on a.managerid = b.id;
联合查询
把两次查询的结果合并,形成一个新的结果集
select 字段列表 from 表1
union[all]
select 字段列表 from 表2;
两个字段列表列数必须一致,否则无法使用union。
union all 会把数据全部合并到一起,union 会去重。
练习:将研发部的员工和年龄大于32岁的员工全部查询出来
select * from tb_emp where org_id=3
union all
select * from tb_emp where age>32;
子查询
sql 语句嵌套 select 语句就叫做嵌套查询,又叫子查询。
标量子查询
-- 查询员工表中比 李明 大的员工,假设姓名不重复
select * from emp where age > (select age from emp where name = '李明');
列子查询
-- 查询员工表中比 李明小红都大 的员工,假设姓名不重复
select * from emp where age > all(select age from emp where name in ('李明','小红'));
行子查询
-- 查询员工emp表中年龄和直属领导和李明相同的员工
select * from emp where (age,manager_id) = (select age,manager_id from emp where name ='李明');
表子查询
-- 查询员工emp表中年龄和直属领导和李明、李红相同的员工
select * from emp where (age,manager_id) in (select age,manager_id from emp where name in('李明','李红'));
事务
简介
事务是一组操作的集合,它是一个不可分割的工作单位,事务会把所有操作作为一个整体一起向系统提交或撤销操作的请求,即这些操作要么同时成功,要么同时失败。
-- 查看事务是否开启,1 开启 0 关闭 mysql默认是开启事务的。
select @@autocommit;
-- 事务提交操作
-- 方式1
-- 1、关闭自动事务
set @@autocommit = 0;
-- 提交
commit;
-- 回滚
rollback;
-- 方式2
-- 1、开启事务
start[begin] transaction;
-- 提交
commit;
-- 回滚
rollback;
ACID
特性 | 描述 |
---|---|
原子性(atomicity) | 事务是不可分割的最小单元,要么全成功,要么全失败。 |
一致性(consistency) | 事务完成时,必须保证所有数据保持一致状态 |
隔离性(isolation) | 数据库系统提供隔离机制,保证事务在不受外部并发操作影响的独立环境下运行 |
永久性(durability) | 事务一旦提交或回滚,对数据库中数据的改变是永久的 |
并发事务问题
问题 | 描述 |
---|---|
脏读 | 一个事务读到另一个事务还没有提交的数据 |
不可重复的 | 一个事务先后读取同一条数据,但两次读取的数据不同 |
幻读 | 一个事务在按照条件查询时没有找到数据行,但在插入数据时,又发现这行数据已经存在了,好像出现了幻影 |
事务的隔离级别
-- 查看隔离级别
select @@transaction_isolation;
-- 设置隔离级别
set [session|global] transaction isolation level [read uncommitted|read committed|repeatable read|serializable]
存储引擎
mysql体系结构
连接层
最上层是一些客户端和链接服务,主要完成一些类似于连接处理、授权认证、及相关的安全方案。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
服务层
第二层架构主要完成大多数的核心服务功能,如sql接口,并完成缓存的查询,sql的分析和优化,部分内置函数的执行。所有跨存储引擎功能也在这一层实现,如过程、函数等。
引擎层
存储引擎真正的负责了mysql中数据的存储和提取,服务器通过api和存储引擎进行通信。不同的存储引擎具有不同的功能,这样我们可以根据自己的需要,来选取合适的存储引擎。
存储层
主要是将数据存储在文件系统至上,并完成与存储引擎的交互。
存储引擎简介
存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的,而不是基于库的,所以存储引擎也可被成为表类型。
-- 1、创建表的时候,指定存储引擎
create table 表名(
字段1...,
字段2...,
字段3...
)engine = innodb
-- 2、查看当前数据库支持的存储引擎
show engines;
存储引擎特点
InnoDB
介绍
InnoDB是一种兼顾高可靠性和高性能的通用存储引擎,在mysql 5.5之后,InnoDB是默认的mysql存储引擎。
特点
DML操作遵循ACID模型,支持事务;
行级锁,提高并发访问的性能;
支持外键FOREIGN KEY 约束,保证数据的完整性和正确性;
文件
xxx.idb:xxx代表表名,innoDB引擎的每张表都会对应这样一个表空间文件,存储该表的表结构(frm,sdi)、数据和索引。
逻辑存储结构
MyISAM
介绍
myISAM是mysql早期的默认存储引擎。
特点
不支持事务,不支持外键;
支持表锁,不支持行锁;
访问速度快;
文件
xxx.sdi:存储表结构信息
xxx.MYD:存储数据
xxx.MYI:存储索引
Memory
介绍
Memory引擎的表数据是存储在内存中的,由于硬件问题、或断点问题的影响,只能将这些表作为临时表或缓存使用。
特点
内存存放
hash索引
文件
xxx.sdi:存储表结构的信息
存储引擎选择
索引
索引概述
索引是帮助mysql高效获取数据的数据结构。
下图中如果查询年龄为18的数据,在没有索引的情况下需要进行全表扫描进行查找需要对比查找5次才能找到目标数据,效率比较低。
如果给年龄age建立索引,如果使用平衡二叉树这种数据结构建立索引,我们可以得到一个这样的二叉树。
这样找到age为18的数据,需要对比3次即可找到目标数据,大大提高了效率,但是mysql不是用平衡二叉树作为索引结构的。
索引结构
mysql的索引是在存储引擎层实现的,不同的存储引擎有不同的结构,主要包含一下几种:
mysql中大部分引擎都支持B+树索引。为什么是B+树,不是二叉查找树、avl树、红黑树呢?
首先二叉查找树在顺序情况下会严重失去平衡,从而降低查找的效率。
avl树和红黑树虽然是自平衡的二叉查找树,但是归根结底是二叉树,在数据量大的时候,树的高度会非常高(层级较深),影响检索效率。
B-Tree(多路平衡查找树)
B+Tree
mysql索引数据结构对经典的B+Tree进行了优化。在原来B+Tree的基础上,增加了一个指向相邻叶子节点的链表指针,就形成了带有有顺序指针的B+Tree,提高区间访问的性能。
B+Tree的索引存在页上,一页16k,所以可以存储更多的索引,降低层级,效率高。
Hash索引
特点:
1、hash索引只能用于对等比较(=,in),不支持范围查询(between,>,<)
2、无法完成排序操作
3、查询效率高,通常只需要一次检索就可以
mysql中,支持hash索引的是memory引擎,而innodb中具有自适应hash功能,hash索引是存储引擎根据b+tree索引在指定条件下自动构建的。
思考:为什么InnoDB存储引擎选择使用B+Tree索引结构,而不是红黑数和B树
1、红黑树是二叉树,在大数据量的时候,深度较深,搜索效率低,B+树层级更少,搜索效率高。
2、B树所有节点都会存放行数据,在数据量大的时候,一页存放的key减少,导致层级加深影响搜索效率
索引分类
在InnoDB中,根据索引的形式,可以分为以下两种
聚集索引的选取规则
1、如果存在主键,主键索引就是聚集索引。
2、如果不存在主键,将使用第一个唯一(UNIQUE)索引作为聚集索引。
3、 如果没有主键、或者没有唯一索引,则Inno会自动生成一个rowid作为隐藏的聚集索引。
-- name 建立了二级索引
select * from user where name = 'Arm';
回表查询
思考:以下哪个SQL语句执行效率高?为什么?(id 为主键,name字段有索引)
select * from user where id = 10;
select * from user where name = 'Arm';
索引语法
-- 创建索引
create [UNIQUE|FULLTEXT] index index_name on table_name(index_col_name,...);
-- 查看索引
show index from table_name;
-- 删除索引
drop index index_name on table_name;
练习
-- 给name创建索引
create index idx_tb_emp_name on tb_temp(name);
-- gender 唯一,为该字段创建唯一索引
create unique index idx_tb_emp_gender on tb_emp(gender);
-- 为 城市和 manger_id 创建联合索引。
create index idx_tb_emp_wa_mangerid on tb_emp(workaddress,manager_id)
sql性能分析工具
-- sql执行频率
show [session|global] status;
show global status like 'Com_______';
-- 查看慢查询日志
show variables like 'slow_query_log';
-- profile 详情,查看sql的时间耗费到哪里了
select @@have_profiling; -- 是否支持
select @@profiling; -- 是否开启
set profiling = 1; -- 开启
show profiles; -- 查看所有sql语句的基本耗时情况
show profile for query query_id; -- 查看指定query_id的sql语句的耗时
show profile cpu for query query_id; -- 查看指定query_id的sql语句对cpu的占用
-- 查看执行计划
explain select 语句