MySQL整理
DDL（数据定义语言）
一.库的管理
1.创建库
create table 库名
2.库修改字符集
alter database 库名 character set 你要设置的字符集新类型
3.库的删除
drop database if exists 库名
二.表的管理
1.表的创建
列的约束 长度为可选写入
create table 表名 (
列名 列的类型 （长度） 列的约束，
.......
列名 列的类型 （长度） 列的约束
)
2.表的删除
drop table 表名
3.表的修改
#改名
alter table 原表名 rename to 新表名；
#修改列
alter table 原表名 change 原列名 新列名 新类型 新长度；
#新增列
alter table 原表名 add 新列名 新类型 新长度；
#删除列
alter table 原表名 drop 原列名；
4.复制表
| 复制表 | 语句 |
|---|---|
| 仅复制表结构 | create table 表名 like 你要复制的表名； |
| 复制表结构+数据 | create table 表名 select * from 你要复制的表名； |
| 只复制部分数据 | create table 表名 select（你要复制的列名）from 你要复制的表名 where 条件； |
| 只复制某些列 | create table 表名 select（你要复制的列名）from 你要复制的表名 where 0； |
常见的数据类型
数值型
整数 tinyint smallint mediumint int4 bigint
小数 float double decimal numeric
字符串
char字符串 varchar可变长字符串 variable可变的
binary二进制 varbinary可变二进制 blob二进制大文本 text正常字符大文本
日期/时间
date日期 time时间 datetime日期&时间 timestamp时间戳
DML（数据操作语言）
一.插入
举例：
insert into 表名 （列名，…）values（值1，…）； （推荐使用）
或
insert into 表名 select(值1,…) union select(值1,…) 插入多个可 union合并
1）列名可省略，默认所有列的顺序和表中顺序一致
2）可为空，可不插入值或用null填充。但设置不可为空的字段时，必须插入值
3）支持插入多行
二.修改
1.举例语句：
修改单表：update 表名 set 列=新值，列=新值 … where 条件
修改多表：update表1 别名 inner 或left或right join 表2 别名 on 连接条件 set 列=新值…where 条件
三.删除
1.delete语句：
删除单表：delete from 表名 where 筛选条件
删除多表：delete from 表1 别名，表2 别名 from 表1 别名 inner或left或right join 表2 别名 on 连接条件 where筛选条件
2.truncate语句
truncate table 表名
区别
| 能否加where条件 | 效率 | 删除自增长列的表后插入数据 | 能否回滚 | |
|---|---|---|---|---|
| delete | 能 | 比truncate低 | 数据从1开始 | 否 |
| truncate | 否 | 比delete高 | 从上次断点开始 | 能 |
DQL（数据查询语言）
一.基础查询
示例
| SQL语句 | 作用 | 备注 |
|---|---|---|
| select 字段名 from 表名; | 查询单个字段 | |
| select 字段名，字段名 from 表名; | 查询多个字段 | |
| select * from 表名; | 查询所有字段 | |
| select 常量值; | 查询常量 | 字符型和日期型必须用单引号，数值型不需要 |
| select 函数名(实参列表); | 查询函数 | |
| select 100/1234; | 查询表达式 | |
| as或空格 | 起别名 | |
| select distinct 字段名 from 表名; | 去重 | |
| select 数值+数值 | 直接运算 | |
| select 字符+数值 | 先将字符转换成数值，成功，继续运算；否则转换成0，再做运算 | |
| select null+值; | 结果都为null |
二.条件查询
select 查询列表 from 表名 where 筛选条件
1.筛选条件的分类（竖着看）
| 简单条件运算符 | 逻辑运算符 | 模糊查询 | 其他 |
|---|---|---|---|
| < 小于 | && and | like 搭配通配符判断字符型或数值型 | between and |
| > 大于 | || or | %为任意多个字符 _为任意单个字符 | in |
| = 等于 | ! not | is null /is not null：用于判断null值 | |
| <> 不等于 | |||
| != 不等于 | |||
| >= 大于等于 | |||
| <= 小于等于 | |||
| <==> 安全等于 |
补充：
isnull函数 功能：判断某字段或表达式是否为null，如果是，则返回1，否则返回0
ifnull函数 功能：判断某字段或表达式是否为null，如果为null 返回指定的值，否则返回原本的值
2.两者都可判断是否为空，区别如下
| 普通类型数值 | null | 可读性 | |
|---|---|---|---|
| is null | × | √ | √ |
| <=> | √ | √ | × |
三.排序查询
select 查询列表 from 表 where 筛选条件 order by 排序列表 （asc或desc）
特点
1）asc 为升序，desc为降序，如果不写默认升序
2）排序列表 支持 单个字段、多个字段、函数、表达式、别名
3）order by的位置一般放在查询语句的最后（除limit语句之外）
四.常用函数
select 函数名(实参列表);
1.字符函数
| 函数 | 含义 |
|---|---|
| concat | 连接 |
| substr | 截取子串 |
| upper | 变大写 |
| lower | 变小写 |
| replace | 替换 |
| length | 获取字节长度 |
| trim | 去前后空格 |
| lpad | 左填充 |
| rpad | 右填充 |
| instr | 获取子串第一次出现的索引 |
2.数学函数
| 函数 | 含义 |
|---|---|
| ceil | 向上取整， 返回大于等于该参数最小整数 |
| round | 四舍五入 |
| mod | 取余 ，mod（a，b)就是a-[(a/b)*b] |
| floor | 向下取整，小于等于该参数的最大整数 |
| truncate | 截断 |
| rand | 获取随机数，返回0-1之间的小数 |
3.日期函数
| 函数 | 含义 |
|---|---|
| now | 返回当前系统日期+时间 |
| year | 返回年 |
| month | 返回月 |
| day | 返回日 |
| date_format | 将日期转换成字符 |
| curdate | 返回当前日期 |
| curtime | 返回当前时间 |
| str_to_date | 将字符转换成日期 |
| hour | 小时 |
| minute | 分 |
| second | 秒 |
| datediff | 返回两个日期相差的天数 |
| monthname | 以英文形式返回月 |
日期字符装换格式
| 格式符 | 功能 |
|---|---|
| %Y | 四位的年份 |
| %y | 2位的年份 |
| %m | 月份（01-12） |
| %c | 月份（1-12） |
| %d | 日（01–30或31或29或28） |
| %H | 小时（24小时制） |
| %h | 小时（12小时制） |
| %i | 分钟（00-59） |
| %s | 秒（00-59） |
4.其他函数
| 函数 | 含义 |
|---|---|
| version | 当前数据库服务器的版本 |
| database | 当前打开的数据库 |
| user | 当前用户 |
| password(‘字符’) | 返回该字符的密码形式 |
| md5(‘字符’) | 返回该字符的md5加密形式 |
5.流程控制函数
1)
#如果条件表达式成立，返回表达式1，否则返回表达式2
if(条件表达式，表达式1，表达式2)
2)case情况1
case 变量或表达式或字段
when 常量1 then 值1
when 常量2 then 值2
...
else 值n
end
3) case情况2
case
when 条件1 then 值1
when 条件2 then 值2
...
else 值n
end
6.分组函数（聚合函数）
举例：select max(字段) from 表名;
| 函数 | 含义 |
|---|---|
| max | 最大值 |
| min | 最小值 |
| sum | 和 |
| avg | 平均值 |
| count | 计算个数 |
支持的类型
1）sum和avg一般用于处理数值型，max、min、count可以处理任何数据类型
2）以上分组函数都忽略null，除了count(*)
3）都可以搭配distinct使用，实现去重的统计。举例：select sum(distinct 字段) from 表;
count函数支持类型：
count(字段)，统计该字段非空值的个数
count(*)，统计结果集的行数
count（常量值），一般放1
效率上：
MyISAM存储引擎，count(*)最高
InnoDB存储引擎，count(*)与count(1)>count(字段)
建议使用 count(*)
五.分组查询
举例：select 查询字段，分组函数 from 表 group by 分组字段
1）可以按单个字段分组，可以按多个字段分组，字段之间用逗号隔开
2）与分组函数一同查询的字段最好是分组后的字段
3）支持排序，排序放在最后
4）having后支持别名，where后不支持
5）分组筛选
| 针对的表 | 位置 | 关键字 | |
|---|---|---|---|
| 分组前筛选 | 原始表 | group by 前面 | where |
| 分组后筛选 | 分组后结果 | group by后面 | having |
六.多表连接查询
当查询中涉及多个表字段，使用多表连接
笛卡尔乘积现象：如果连接条件省略或无效则会出现
1.外连接
举例：select * from A left/right [outer] join B on 连接条件
外连接查询结果为主表所有记录，显示从表匹配的值，没有则为null
左外连接 left左边为主表，右外连接 right join 右边为主表
2.内连接
举例：select * from A inner join B on 连接条件
等值连接
举例：select A.列，B.列 from A,B where 等值连接条件
等值连接的结果等于多个表的交集 ，n表连接至少需要n-1个连接条件
多个表不分主次
非等值连接
where后是非等值的连接条件
自连接
举例：select A.列，B.列 from A join B on A.列 =B.列
七.子查询（嵌套）
一条查询语句中嵌套另一条完整的select语句，被嵌套的select语句称为子查询或内查询
在外面的查询语句称为主查询或外查询
子查询优于主查询执行，主查询使用子查询的执行结果
按子查询出现的位置：
可放在select后面 ：仅支持（1）
from后面：仅支持（4）
where 或having后面：支持（1）（2）（3）
exists（相关子查询）后面：仅支持（4）
按结果集的行列数不同：
1）标量 子查询（结果集一行一列）
2）列 子查询（结果集一列多行）
3）行 子查询（结果集一行多列）
4）表 子查询（结果集多行多列）
子查询根据查询结果行数不同分类
| 分类 | 结果集 | 搭配 | 备注 |
|---|---|---|---|
| 单行 子查询 | 一行 | > < = <> >= <= | 非法使用子查询（1）结果为一组值（2）结果为空 |
| 多行 子查询 | 多行 | some,any,all,in,not in | in：属于子查询结果中任意一个就行，some与any：满足子查询结果其中一个即可，all：满足子查询结果所有才可以。any与all可用其他查询（min或max）代替 |
八.分页查询
举例：select * from 表 where 条件 group by分组字段 order by排序字段 limit offset,size
offset：起始条目索引从0开始
size：要显示的条目个数
1）limit字句放在查询语句最后
2）每页显示条目数 sizePerPage
要显示的页数page
公式：select * from 表 limit （page-1）*sizePerPage，sizePerPage
九.联合查询
举例：select 字段|常量|表达式|函数 from 表 where 条件 union all
union（结合，合并，并集）
多条查询语句的查询列数必须一致，列的类型几乎相同，拼接后显示的列名是前一个子集默认的列名
| 区别 | 备注 | |
|---|---|---|
| union | 去重 | 性能较慢，如果产生重复元素记录的是第一次出现的那一行 |
| union all | 不去重 | 不做任何处理，性能较快，建议使用 |
列的约束
1.主键约束(Primary Key)
1）主键列在表格中只能存在一份(一个表格中只有一个列可以设置为主键约束)
2）主键约束要求当前列值不能为null ，不能重复
3）通常用来做唯一的标识 快速寻找当前这一行记录
举例：
添加主键约束：
alter table 表名字 add constraint pk_表 primary key(列);
alter table 表名字 add primary key(列);
设置主键自动增长：
alter table 表名字 modify 列 类型 长度 auto_increment;
alter table 表名字 auto_increment = 值;
删除主键约束：
alter table 表名字 drop primary key;
删除主键约束后：
唯一的特性被取消了 但是非空的特性还在
自己手动修改非空的特性
alter table 表名字 modify 列 类型 长度 null;
alter table 表名字 change 列 列 类型 长度 null;
2.唯一约束(Unique Key)
1）在表格中可以添加多个列的唯一约束
2）当前列的值不能重复 可以为null
添加唯一约束：
alter table 表名字 add constraint uk_表 unique [key] (列);
alter table 表名字 add unique [key] (列);—>约束名默认就是列名
删除唯一约束：
alter table 表名字 drop index uk_表;
alter table 表名字 drop index 列名;
3.非空约束
1）可以在表格中添加多个列的约束
2）当前列的值不能为null
添加非空约束：
alter table 表名字 modify 列 类型 长度 not null default 值;
alter table 表名字 change 列 列 类型 长度 not null default 值;
删除非空约束：
alter table 表名字 modify 列 类型 长度 null;
alter table 表名字 change 列 列 类型 长度 null;
4.外键约束(Foreign Key)
1）用于限制两个表的关系，从表的字段值引用了主表的某字段值
2）外键列和主表的被引用列要求类型一致，意义一样，名称无要求
3）主表的被引用列要求是一个key（一般就是主键）
4）插入数据，先插入主表，删除数据，先删除从表
添加外键约束:
alter table 表名字 add constraint fk_当前表_关联表 foreign key(列) references 另一个表(列);
删除外键约束:
alter table 表名字 drop foreign key 约束名字;
注意:通过上述语句其实已经将外键约束删掉了,自动在当前表格内添加一个新的key,需要再次手动将这个生成的key删掉 外键约束才真的删除干净
alter table 表名字 drop key 约束名字;
alter table student add foreign key(列) references 另一个表(列);
5.检查约束(Check)
列在存值的时候做一个细致的检查 范围是否合理
alter table 表名 add constraint 约束名 check(条件);
DCL（数据控制语言）
grant赋予 remove回收
1.管理员root账号
管理员可以操作其他普通用户的权限,通过root账号查看mysql数据库中的user表格,记录着所有的用户信息
2.创建一个新的用户
create user '用户名'@'IP' identified by '密码';
用户被创建成功啦(只有一个默认的权限 Usage 只允许登录 不允许做其他事情)
3.给新用户赋予权限
grant 权限 on 数据库名.表 to '用户名'@'IP';
#赋予权限之后最好做一个刷新
flush privileges;
4.使用新用户登录成功后， 回收用户的权限
revoke 权限 on 数据库名.表名 from '用户名'@'IP';
5.修改用户密码
update user表 set authentication_string = password('xxx') where user = 'xxx'; #这里填自己用户密码
6.删除用户
drop user '用户名'@'IP';
#常用的mysql权限如下：
#数据库/数据表/数据列权限：
Create #建立新的数据库或数据表
Alter #修改已存在的数据表(例如增加/删除列)
Drop #删除数据表或数据库
Insert #增加表的记录
Delete #删除表的记录
Update #修改表中已存在的记录
Select #显示/搜索表的记录
-----------------------------------
References #允许创建外键
Index #建立或删除索引
Create View #允许创建视图
Create Routine #允许创建存储过程和包
Execute #允许执行存储过程和包
Trigger #允许操作触发器
Create User #允许更改、创建、删除、重命名用户和收回所有权限
#全局管理MySQL用户权限：
Grant Option #允许向其他用户授予或移除权限
Show View #允许执行SHOW CREATE VIEW语句
Show Databases #允许账户执行SHOW DATABASE语句来查看数据库
Lock Table #允许执行LOCK TABLES语句来锁定表
File #在MySQL服务器上读写文件
Process #显示或杀死属于其它用户的服务线程
Reload #重载访问控制表，刷新日志等
ShutDown #关闭MySQL服务
#特别的权限：
All #允许做任何事(和root一样)
Usage #只允许登录，其它什么也不允许做
TPL（事务处理语言）
1.事务的本质可以理解为 多线程并发操作同一张表格，可能带来安全问题
2.事务的四大性（ACID）：
A:Atomicity---->原子性：
一个事务是不可再分割的整体，要么都执行要么都不执行
C:Consistency—>一致性：
一个事务可以使数据从一个一致状态切换到另外一个一致的状态
I:Isolation---->隔离性----->(事务隔离级别)：
多个用户并发访问数据库时，一个用户操作数据库，另一个用户不能有所干扰
多个用户之间的数据事务操作要互相隔离
D:Durability—>持久性：
一个用户操作数据的事务一旦被提交(缓存–>文件)，他对数据库底层真实的改变是永久性的 不可返回
3.事务控制语句：
BEGIN 或 START TRANSACTION 显式地开启一个事务；
COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；
ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；
RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
ROLLBACK TO identifier 把事务回滚到标记点；
SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。
4.MYSQL 事务处理主要有两种方法：
1）用 BEGIN, ROLLBACK, COMMIT来实现
- BEGIN 开始一个事务
- ROLLBACK 事务回滚
- COMMIT 事务确认
2）直接用 SET 来改变 MySQL 的自动提交模式:
- SET AUTOCOMMIT=0 禁止自动提交
- SET AUTOCOMMIT=1 开启自动提交
5.事务的隔离级别
事务的隔离性可能会产生多线程并发操作同一数据库表格 会带来安全隐患
1）脏读：一个事务读取了其他事务还没有提交的数据，读到的是其他事务“更新”的数据
2）不可重复读：一个事务多次读取，结果不一样
3）幻读：一个事务读取了其他事务还没有提交的数据，只是读到的是 其他事务“插入”的数据
通过设置隔离级别来解决问题
| 脏读 | 不可重复读 | 幻读 | |
|---|---|---|---|
| Serializable 串行化 | √ | √ | √ |
| Repeatable Read 可重复读 | √ | √ | × |
| Read Committed 读已提交 | √ | × | × |
| Read UnCommitted 读未提交 | × | × | × |
Mysql使用提供默认隔离级别 Repeatable Read 可重复读
#修改数据库隔离级别
set session transaction isolation level xxx;
#查看级别
select @@tx_isolation;
变量
系统变量：
全局变量
会话变量
自定义变量：
用户变量
局部变量
系统变量
说明：变量由系统定义，不是用户定义，属于服务器层面
注意：全局变量需要添加global关键字，会话变量需要添加session关键字，如果不写，默认会话级
查看所有系统变量
show global或session variables;
查看满足条件的部分系统变量
show global或session variables like ‘%char%’;
查看指定的系统变量的值
select @@ global或session 系统变量名;
为某个系统变量赋值
方式一：
set global或session 系统变量名=值;
方式二：
set @@ global或session 系统变量名=值;
全局变量
作用域：针对于所有会话（连接）有效，但不能跨重启，服务器启动时，从配置文件加载
查看所有全局变量
SHOW GLOBAL VARIABLES;
查看满足条件的部分系统变量
SHOW GLOBAL VARIABLES LIKE ‘%char%’;
查看指定的系统变量的值
SELECT @@global.autocommit;
为某个系统变量赋值
SET @@global.autocommit=0;
SET GLOBAL autocommit=0;
会话变量
作用域：针对于当前会话（连接）有效，在新建连接时，从全局变量中拷贝出一份，也是临时变量
查看所有会话变量
SHOW SESSION VARIABLES;
查看满足条件的部分会话变量
SHOW SESSION VARIABLES LIKE ‘%char%’;
查看指定的会话变量的值
SELECT @@autocommit;
SELECT @@session.tx_isolation;
为某个会话变量赋值
SET @@session.tx_isolation=‘read-uncommitted’;
SET SESSION tx_isolation=‘read-committed’;
自定义变量
说明：变量由用户自定义，而不是系统提供的
使用步骤：
1）声明
2）赋值
3）使用（查看、比较、运算等）
用户变量
作用域：针对于当前会话（连接）有效，作用域同于会话变量
赋值操作符：=或:=
声明并初始化
SET @变量名=值;
SET @变量名:=值;
SELECT @变量名:=值;
赋值（更新变量的值）
方式一：
SET @变量名=值;
SET @变量名:=值;
SELECT @变量名:=值;
方式二：
SELECT 字段 INTO @变量名
FROM 表;
使用（查看变量的值）
SELECT @变量名;
局部变量
作用域：仅仅在定义它的begin end块中有效
应用在 begin end中的第一句话
声明
DECLARE 变量名 类型;
DECLARE 变量名 类型 DEFAULT 值;
赋值（更新变量的值）
方式一：
SET 局部变量名=值;
SET 局部变量名:=值;
SELECT 局部变量名:=值;
方式二：
SELECT 字段 INTO 具备变量名
FROM 表;
使用（查看变量的值）
SELECT 局部变量名;
案例：声明两个变量，求和并打印
用户变量
SET @m=1;
SET @n=1;
SET @sum=@m+@n;
SELECT @sum;
局部变量
DECLARE m INT DEFAULT 1;
DECLARE n INT DEFAULT 1;
DECLARE SUM INT;
SET SUM=m+n;
SELECT SUM;
用户变量和局部变量的对比
| 作用域 | 定义位置 | 语法 | |
|---|---|---|---|
| 用户变量 | 当前会话 | 会话的任何地方 | 加@符号，不用指定类型 |
| 局部变量 | 定义它的BEGIN END中 | BEGIN END的第一句话 | 一般不用加@,需要指定类型 |
363
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
