数据类型
数值类型
- TINYINT 1bytes 小整数值
- SMALLINT 2bytes 大整数值
- MEDIUMINT 3bytes 大整数值
- INT或INTEGER 4bytes 大整数值
- BIGINT 8bytes 极大整数值
- FLOAT 4bytes 单精度浮点数值
- DOUBLE 8bytes 双精度浮点数值
字符串类型
- CHAR 0-255 bytes 定长字符串
- VARCHAR 0-65535 bytes 变长字符串
- TINYBLOB 0-255 bytes 不超过255个字符的二进制数据
- TINYTEXT 0-255 bytes 短文本字符串
- BLOB 0-65 535 bytes 二进制形式的长文本数据
- TEXT 0-65 535 bytes 长文本数据
- MEDIUMBLOB 0-16 777 215 bytes 二进制形式的中等长度文本数据
- MEDIUMTEXT 0-16 777 215 bytes 中等长度文本数据
- LONGBLOB 0-4 294 967 295 bytes 二进制形式的极大文本数据
- LONGTEXT 0-4 294 967 295 bytes 极大文本数据
日期时间类型
| 类型 | 大小 | 范围 | 格式 | 描述 |
|---|---|---|---|---|
| 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 23：59：59 | 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 | 混合日期和时间值，时间戳 |
DDL 定义操作
数据库操作
查询
SHOW DATABASES;
SHOW DATABASE();
创建
CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];
删除
DROP DATABASE [IF EXISTS] 数据库名;
使用
USE 数据库名;
表操作
查询当前数据库所有表
SHOW TABLES;
查询表结构
DESC 表名;
查询指定表的建表语句
SHOW CREATE TABLE 表名;
创建
CREATE TABLE 表名(
字段1 字段1类型 [COMMENT 字段1注释],
字段n 字段n类型 [COMMENT 字段n注释]
) [COMMENT 表注释];
修改
添加字段
ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束];
修改数据类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);
修改字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名 类型(长度) [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);
INSERT INTO 表名 VALUES (值1，值2)，(值1，值2);
删
DELETE FROM 表名 [WHERE 条件];
改
UPDATE 表名 SET 字段名1=值1，字段名2=值2，…[WHERE 条件];
DQL 数据查询
SELECT 字段列表
FROM 表名列表
WHERE 条件列表
GROUP BY 分组字段列表
HAVING 分组后条件列表
ORDER BY 排序字段列表
LIMIT 分页参数
基本查询
查询多个字段别名
SELECT 字段 [AS 别名] FROM 表名
SELECT * FROM 表名
去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;
条件查询
SELECT 字段列表 FROM 表名 WHERE 条件列表;
条件：
- BETWEEN … AND … 在某个范围之内[]
- IN(…) 在in之后的列表中的值，多选一
- LIKE 占位符 模糊匹配(__匹配单个字符，%匹配任意个字符)
- IS NULL 是NULL
聚合函数
将一列数据作为整体进行纵向计算
- count max min avg sum
SELECT 聚合函数(字段列表) FROM 表名;
分组查询
SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];
where和having不同
- 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。
- 判断条件不同：where不能对聚合函数进行判断，having可以
排序查询
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1，字段2 排序方式2;
排序方式
- ASC：升序（默认）
- DESC：降序
分页查询
SELECT 字段列表 FROM 表名 LIMIT 起始索引，查询记录数;
DCL权限控制
管理用户
查询用户
USE mysql;
SELECT * FROM user;
创建用户
CREATE USER ‘用户名‘@’主机名’ IDENTIFIED BY ‘密码’;
修改用户密码
ALTER USER ‘用户名‘@’主机名’ IDENTIFIED WITH mysql_native_password BY ‘新密码’;
删除用户
DROP USER ‘用户名‘@’主机名’;
权限控制
查询权限：
SHOW GRANTS FOR ‘用户名‘@’主机名’;
授予权限:
GRANT 权限列表 ON 数据库名.表名 TO ’用户名‘@’主机名‘；
撤销权限：
REVOKE 权限列表 ON 数据库名.表名 FROM ’用户名‘@’主机名‘；
函数
函数指一段可以直接被另一段程序调用的程序或代码
字符串函数
| 函数 | 功能 |
|---|---|
| CONCAT(S1,S2,…Sn) | 字符串拼接 |
| LOWER(str) | 转小写 |
| UPPER(str) | 转大写 |
| LPAD(str,n,pad) | 左填充 |
| RPAD(str,n,pad) | 右填充 |
| TRIM(str) | 去空格 |
| SUBSTRING(str,start,len) | 裁剪返回 |
数值函数
- 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)：返回一个日期/时间值加上一个时间间隔expr后的时间值
- datediff(date1,date2)：返回起始时间date1和结束时间date2之间的天数
流程函数
- if(value,t,f)：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默认值
约束
普通约束
概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据
目的：保证数据库中数据的正确、有效性和完整性
约束 描述 关键字 非空约束 限制该字段的数据不能为null NOT NULL 唯一约束 保证该字段的所有数据都是唯一、不重复 UNIQUE 主键约束 主键是一行数据的唯一标识，要求非空且唯一 PRIMARY KEY 默认约束 保存数据时，如果未指定该字段的值，则采用默认值 DEFAULT 检查约束 保证字段值满足某一个条件 CHECHK 外键约束 用来让两张表的数据之间建立连接，保证数据的一致性和完整性 FOREIGN KEY auto_increment：自动增长
外键约束
外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性
添加外键
create table 表名( 字段名 数据类型, [constrain] [外键名称] foreign key(外键字段名) references 主表(主表列名) ); alter table 表名 add constrain 外键名称 foreign key(外键字段名) references 主表(主表列表);删除外键
alter table 表名 drop foreign key 外键名称;
删除更新
- no action / restrict：拥有外键不予更新
- cascade：级联
- set null：拥有外键，外键值取null
- set default：父表变更，外键列设置为默认值(Innodb不支持)
alter table 表名 add constrain 外键名称 foreign key (外键字段) references 主表名(主表字段名) on update cascade on delete cascade;
多表查询
- 连接查询
- 内连接：相当于查询A，B交集部分数据
- 外连接：
- 左外连接：查询左表以及交集数据
- 右外连接：查询右表以及交集数据
- 自连接：当前表与自身的连接查询，自连接必须使用表别名
- 子查询
连接查询
内连接
隐式：
select 字段列表 from 表1,表2 where 条件...;显式：
select 字段列表 from 表1 [inner] join 表2 on 连接条件...;
外连接
左/右外连接
select 字段列表 from 表1 left [outer] join 表2 on 条件;查询包含左右表以及交集数据
自连接
select 字段列表 from 表1 别名1 join 表1 别名2 on 条件...;自连接查询，可以是内连接也可以是外连接
联合查询
对于union查询，就是把多次查询结果结合起来，形成一个新的查询结果集
select 字段列表 from 表1 union [all] select 字段列表 from 表2;
- 对于联合查询的多张表的列数必须保持一致，字段类型也需保持一致
- union all 会将全部的数据直接合并一起，union会对合并的数据去重
子查询
- 概念：SQL语句中嵌套SELECT语句，又称为嵌套查询、
select * from t1 where column1= (select column1 from t2);子查询外部的语句可以是增删改查任何一个
- 根据子查询结果不同，分为：
- 标量子查询（查询结果为单个值）
- 列子查询（查询结果为一列）
- 行子查询（查询结果为一行）
- 表字查询（查询结果为多行多列）
事务
事务是一组操作集合，不可分割的工作单位，事务会把所有操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败
事务操作
查看/设置事务提交方式
```sql
select @@autocommit;
set @@autocommit=0;- 提交事务 - ```sql commit;回滚事务
```sql
rollback;- 开启事务 - ```sql start transaction 或 begin;
事务四大特性
- 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败
- 一致性（Consistency）：事务完成时，必须所有的数据都保持一致状态
- 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
- 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的
并发事务问题
- 脏读：一个事务读到另一个事务还没有提交的数据
- 不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读
- 幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现该行数据已存在，好像出现幻影
事务隔离级别
隔离级别 脏读 不可重复读 幻读 Read uncommitted √ √ √ Read committed × √ √ Repeatable Read(默认) × × √ Serializable × × × 查看事务隔离级别
select @@transaction_isolation;设置事务隔离级别
set [session|global] transaction isolation level ...;
可以留下你的信息哦(去Github_issues)😀😀😀
GitHub Issues