2 MySQL基础
2.1 SQL分类
数据库语句不区分大小写,推荐使用大写字母
分类 | 全称 | 说明 |
---|---|---|
DDL | Data Definition Language | 数据定义语言,用来定义数据库对象,数据库,表,字段 |
DML | Data Manipulation Language | 数据操作语言,对数据库表中的数据进行增删改 |
DQL | Data Query Language | 数据查询语言,查询数据表中的数据 |
DCL | Data Control Language | 数据控制语言,用来创建数据库用户、控制数据库访问权限 |
2.2 DDL
1.DDL数据库操作
--查询所有数据库
show databases;
--查询当前数据库
select datebase();
--创建数据库
create database [if not exists] 数据库名 [default charset 字符集] [collate 排序规则];
--删除
drop database [if exists] 数据库名;
--使用
use 数据库名;
2.DDL表操作
--查询当前数据库所有的表
show tables;
--查询表结构
desc 表名;
--查询指定表的建表语句
show create table 表名;
--添加字段
alter table 表名 add 字段名 类型(长度) [comment 注释][约束];
--修改数据类型
alter table 表名 modify 字段名 新数据类型(长度);
--修改字段名和字段类型
alter table 表名 change 旧字段名 新字段名 类型(长度)[comment 注释][约束];
--删除字段
alter table 表名 drop 字段名;
--修改表名
alter table 表名 rename to 新表名;
--删除表
drop table[if exists] 表名;
--删除指定表,并重新创建该表,相当于删除该表,又重新创建了该表
truncate table 表名;
2.3 DML
--insert
--1.指定字段添加值
insert into 表名(字段1,字段2,...) values(值1,值2,...);
--2.全部字段添加值
insert into 表名 values(值1,值2,...);
--3.批量添加数据
insert into 表名(字段1,字段2,...) values(值1,值2,...)(值1,值2,...)(值1,值2,...)...;
insert into 表名 values(值1,值2,...)(值1,值2,...)(值1,值2,...)...;
--upadte
--1.修改数据
update 表名 set 字段1=值1,字段2=值2,... [where 条件];
--delete
--1.删除数据
delete from 表名 [where 条件];
2.4 DQL
select
字段列表
from
表名列表
where
条件
group by
分组字段列表
having
分组条件列表
order by
排序字段列表
limit
分页参数
2.4.1 基础查询
--查询多个字段
select 字段1,字段2,字段3,... from 表名;
--设置别名
select 字段1 [as 别名1],字段2 [as 字段2],字段3 [as 字段3],... from 表名;
--去重
select distinct 字段列表 from 表名;
2.4.2 条件查询
语法:
select 字段列表 from 表名 where 条件列表;
条件:
比较运算符 | 功能 |
---|---|
> | 大于 |
>= | 大于等于 |
< | 小于 |
<= | 小于等于 |
= | 等于 |
!= 或 <> | 不等于 |
between…and… | 在某个范围之内,含最小值和最大值 |
in(…) | 在in之后的列值,多选一 |
like 占位符 | 模糊匹配:_匹配单个字符,%匹配任意字符 |
is null | 是null |
逻辑运算符 | 功能 |
---|---|
and 或 && | 并且 |
or 或 || | 或者 |
not 或 ! | 非 |
2.4.3 聚合函数
count:统计、max:最大值、min:最小值、avg:平均值、sum:求和
将表的一列数据作为整体,进行纵向运算
2.4.4 分组查询
select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];
查询结果一般是:分组字段和聚合函数
2.4.5 排序算法
select 字段列表 from 表名 order by 字段1 排序方式1,字段2 排序方式2;
排序方式:
- ASC:升序(默认值)
- DESC:降序
2.4.6 分页查询
select 字段列表 from 表名 limit 起始索引,查询记录数;
注:
- 起始索引从零开始,起始索引=(查询页码-1)* 每页显示记录数
- 分页查询是数据库的方言,不同数据库实现不同,mysql使用的是limit实现的
- 若果查询第一页数据,起始索引可以省略,直接简写为 limit 10
SQL执行顺序:
from——on——join——where——group by——having——select——distinct——order by——limit
2.5 DCL
2.5.1 用户管理
数据库名为 msql 的数据库中的 user 表,存放的是用户信息
--创建用户,% 表示任意主机可以访问 当为localhost时,只允许本机访问
create user 'username'@'%' identified by 'password';
--修改用户
alter user 'username'@'%' identified with mysql_native_password by 'newpassword';
--删除用户
drop user 'uername'@'%';
2.5.2 权限控制
MySQL常用的权限
权限 | 说明 |
---|---|
all,all privileges | 所有权限 |
select | 查询数据权限 |
insert | 插入数据权限 |
update | 修改数据权限 |
delete | 删除数据权限 |
alter | 修改表权限 |
drop | 删除数据库/表/视图权限 |
create | 创建数据库/表权限 |
--查询权限
show grands for '用户名'@'主机名';
--授予权限
grand 权限列表 on 数据库名.表名 to '用户名'@'主机名';
--撤销权限
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
2.6 函数
2.6.1 字符串函数
常用的字符串函数
函数 | 功能 |
---|---|
concat(s1,s2,…sn) | 字符串拼接 |
lower(str) | 将str转小写 |
upper(str) | 将str转大写 |
Lpad(str,n,pad) | 左填充,用字符串pad对str左边进行填充,达到n个字符串长度 |
rpad(str,n,pad) | 右填充,参考左填充 |
trim(str) | 去掉str头部和尾部的空格 |
substring(str,startIndex,len) | 返回字符串str从start位置起的len长度的字符串 |
2.6.2 数值函数
函数 | 功能 |
---|---|
ceil(x) | 向上取整 |
floor(x) | 向下取整 |
mod(x,y) | 返回x/y的模 |
rand() | 返回0~1内的随机数 |
round(x,y) | 求x四舍五入的值,保留以为小数 |
2.6.3 日期函数
函数 | 功能 |
---|---|
curdate() | 返回当前日期 |
curtime() | 返回当前时间 |
now() | 返回当前日期和时间 |
year() | 获取指定date的年份 |
month() | 获取指定date的月份 |
day() | 获取指定date的日期 |
date_add(date,interval expr type) | 返回一个日期/时间值加上一个时间间隔expr后的时间值 |
datediff(date1,date2) | 返回起始时间date1和结束时间date2之间的天数 |
2.6.4 流程控制函数
在SQL语句中实现条件筛选
函数 | 功能 |
---|---|
if(value,t,f) | 如果value为true,则返回t,否则返回f |
ifnull(value1,value2) | 如果value1不为空,返回value1,否则返回value2 |
case when [v1] then [res1] … else [default] end | 如果v1为true,返回res1,… 否则返回default默认值 |
case [expr] when [v1] then [res1] … else [default] end | 如果expr的值等于v1,返回res1,…否则返回default默认值 |
2.7 约束
-
概念:约束是作用于表中字段的规则,用于限制存储在表中的数据
-
目的:保证数据库中数据的正确性,有效性和完整性
-
分类:
约束 描述 关键字 非空约束 限制该字段不能为null not null 唯一约束 保证该字段的所有数据都是唯一、不重复的 unique 主键约束 主键是一行数据的唯一标识,要求非空且唯一 primary key 默认约束 保存数据时,如果未指定该字段的值,则采用默认值 default 检查约束(8.0.16) 保证字段值满足某一个条件 check 外键约束 用来让两张表的数据之间建立连接,保证数据的一致性和完整性 foreign key
2.8 数据类型
数值类型
类型 | 大小 | 有符号(signed)范围 | 无符号(unsigned)范围 | 描述 |
---|---|---|---|---|
tinyint | 1 byte | (-128,+127) | (0,255) | 小整数值 |
smallint | 2 byte | (-32768,+32767) | (0,65535) | 大整数值 |
mediumint | 3 byte | (-8388608,+8388607) | … | 大整数值 |
int/integer | 4 byte | … | … | 大整数值 |
bigint | 8 byte | … | … | 大整数值 |
float | 4 byte | … | … | 单精度浮点数 |
double | 8 byte | … | … | 双精度浮点数 |
decimal | 依赖于M(精度)和D(标度)的值 | 依赖于M(精度)和D(标度)的值 | 小数值(精确定点数) |
字符串类型
类型 | 大小 | 描述 |
---|---|---|
char | 0-255 bytes | 定长字符串 |
varchar | 0-65535 bytes | 变长字符串 |
tinyblob | 0-255 bytes | 不超过255个字符的二进制数据 |
tinyttext | 0-255 bytes | 短文本字符串 |
blob | 0-65535 bytes | 二进制形式的长文本数据 |
text | 0-65535 bytes | 长文本数据 |
mediumblob | … | 二进制形式的中等长度文本数据 |
mediumtext | … | 中等长度文本数据 |
longblob | … | 二进制形式的极大文本数据 |
longtext | … | 极大文本数据 |
日期时间类型
类型 | 大小 | 范围 | 格式 | 描述 |
---|---|---|---|---|
date | 3 | 1000-01-01 至 9999-12-31 | yyyy-mm-dd | 日期值 |
time | 3 | -838:59:59 至 838:59:59 | hh:mm:ss | 时间值或持续时间 |
year | 1 | 1901 至 2155 | yyyy | 年份值 |
datetime | 8 | 1000-01-01 00:00:00 至 9999-12-31 00:00:00 | yyyy-mm-dd hh:mm:ss | 混合日期时间值 |
timestamp | 4 | 1970-01-01 00:00:01 至 2038-01-19 03:14:07 | yyyy-mm-dd hh:mm:ss | 混合日期时间值,时间戳 |
2.9 多表查询
2.9.1 多表关系
- 一对一:可以将表进行拆分,一张存放基础字段,一张存放详情字段,提高查询效率,在任一方键入(逻辑)外键,关联
- 一对多(多对一):再多的一方建立(逻辑)外键
- 多对多:建立中间表,至少建立两个(逻辑)外键,分别关联两张表的主键
2.9.2 多表查询概述
多表查询分类:
- 连接查询:
- 内连接:相当于A∩B
- 外连接:
- 左外连接:查询左表所有数据,以及两张表交集部分
- 右外连接:查询右表所有数据,以及两张表交集部分
- 自连接:当前表与自身的连接查询,自连接必须使用表别名
- 子查询
2.9.3 内连接
隐式内连接:
select 字段列表 from 表1,表2 where 条件...;
显示内连接:
select 字段列表 from 表1 [inner] join 表2 on 连接条件...;
2.9.4 外连接
左外连接:
select 字段列表 from 表1 left [outer] join 表2 on 条件...;
右外连接:
select 字段列表 from 表1 right [outer] join 表2 on 条件...;
2.9.5 自连接
select 字段列表 from 表A A join 表A B on 条件...;
自连接也可以是左连接和右连接
2.9.5 联合查询
- union all 可以把多次查询的结果合并起来,形成一个新的结果集,若多次产生的结果集有重复,那么最终的结果集会有重复数据
- 而 union 不会产生重复数据
- 也就是说 union all 会将全部的数据直接连接合并在一起,union 会对合并之后的数据去重
select 字段列表 from 表1...;
union [all]
select 字段列表 from 表2...;
对于联合查询的多张表的数据列必须保证一致,字段类型也要保持一致
2.9.6 子查询(嵌套查询)
select 字段列表 from 表1 where column1 = (select column1 from 表2);
子查询外部语句可以是 insert、update、delete、select 的任意一个
根据子查询结果不同可以分为:
- 标量子查询:子查询结果为单个值
- 列子查询:子查询结果为一列
- 行子查询:子查询结果为一行
- 表子查询:子查询结果为多行多列
根据子查询位置分为:where之后、from之后、select之后
标量子查询
select 字段列表 from 表1 where column1 = (select column1 from 表2);
常用操作符:=、<>、<、>、<=、>=;
列子查询
select 字段列表 from 表1 where column1 in (select column1 from 表2);
常用操作符:in、not in、any、some、all
行子查询
子查询中查出来的结果是具体的数据
select 字段列表 from 表1 where (column1,column2)=(select column1,column2 from 表2);
常用操作符:=、<>、in、not in
表子查询
子查询中查出来的结果是一张临时表
select 字段列表 from 表1 where (column1,column2)=(select column1,column2 from 表2);
常用操作符:in
2.10 事务
2.10.1 事务的操作
事务是一组操作的集合,他是一个不可分割的工作单位,事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求;即这些操作要么同时成功,要么同时失败。
-- 设置手动提交任务
set @@autocommit = 0;
-- 设置自动提交任务
set @@autocommit = 1;
start transaction; -- 开始事务
commit; -- 提交任务
rollback; -- 回滚任务
2.10.2 事务的四大特性
- 原子性(Atomicity):事务是不可分割的最小单元,要么全部成功,要么全部失败
- 一致性(Consistency):事务完成时,必须使所有的数据都保持一致
- 隔离性(Isolation):数据库系统提供隔离机制,保证事务在不受外部并发操作影响的独立环境下运行
- 持久性(Durability):事务一旦提交或回滚,他对数据库中的数据改变就是永久的。
2.10.3 并发事务问题
- 脏读:一个事务读到另外一个事务还没有提交的数据
-
不可重复度:一个事务先后读取到同一条记录,但两次读取的数据不同,称之为不可重复读
-
幻读:一个事务按照条件查询数据时,没有对应的数据行,但是在插入数据时,又发现这行数据已经存在了
2.10.4 事务的隔离级别
隔离级别 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|
read uncommitted | 未解决 | 未解决 | 未解决 |
read committed | 解决 | 未解决 | 未解决 |
repeatable read(mysql默认) | 解决 | 解决 | 未解决 |
serializable | 解决 | 解决 | 解决 |
查看事务隔离级别
-- sql高版本
select @@transaction_isolation;
-- sql低版本
select @@tx_isolation;
设置事务隔离级别
-- 设置事务的隔离级别
set [session | gloabal] transaction isolation level {read uncommitted | read committed | repeatable read | serializable};
set session transaction isolation level read uncommitted;
set session transaction isolation level repeatable read;
事务的隔离级别越高,数据越安全,但性能越低