SQL
DDL
数据库操作
查询所有数据库
SHOW DATABASES;
查询当前数据库
SHOW DATABASE();
创建数据库
CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 默认字符集] [COLLATE 排序规则]
-- [] 内的为可选操作 IF NOT EXISTS 用于判断数据库名是否存在 不存在则创建
删除数据库
DROP DATABASE [IF EXISTS] 数据库名;
切换数据库
USE 数据库名;
表操作-查询-创建
查询当前数据库所有表
SHOW TABLES;
查询表结构
DESC 表名;
查询指定表的建表语句
SHOW CREATE TABLE 表名;
创建表
[] 里面为可选操作 最后一个字段后面没有,号
CREATE TABLE 表名(
字段1 字段1类型[COMMENT 字段1注释],
字段2 字段2类型[COMMENT 字段2注释],
字段3 字段3类型[COMMENT 字段3注释]
)[COMMENT 表注释];
表操作-数据类型
数值类型
age TINYINT UNSIGNED --指定数据没有负数
score DOUBLE(4,1) --指定数值长度为4为，小数点后为1位
| 类型 | 大小 | 范围 | 描述 |
|---|---|---|---|
| TINYINT | 1byte | 0~255,-128~127 | 小整数值 |
| SMAIIINT | 2byte | 0~65535 | 大整数值 |
| MEDIUMINT | 3byte | 0~1677215 | 大整数值 |
| INT或INTEGER | 4byte | 大整数值 | |
| BIGINT | 8byte | 超大整数值 | |
| FLOAT | 4byte | 单精度浮点数 | |
| DOUBLE | 8byte | 双精度浮点数 | |
| DECIMAL | 小数值 | ||
| 字符串类型 |
char(10) --定义字符串长度 存储空间固定 未使用空间用空格填充
varchar(10) --根据内容计算空间
-- char性能比varchar高
| 类型 | 大小 | 描述 |
|---|---|---|
| byte | 0-255bytes | 定长字符串 |
| varchar | 0-65535 | 变长字符串 |
| TINYBLOB | 0-255 | 不超过255字节的二进制数据 |
| TINYTEXT | 0-255 | 短文本字符串 |
| BLOG | 0-65535 | 二进制形式的长文本数据 |
| TEXT | 0-65535 | 长文本数据 |
| MEDIUMBLOB | 0-16777215 | |
| MEDIUMTEXT | 0-16777215 | |
| LONGBLOB | 0-4294967295 | |
| LONGTEXT | 0-4294967295 |
日期时间类型
| 类型 | 大小 | 格式 | 描述 |
|---|---|---|---|
| DATE | 3 | yyyy-mm-dd | 日期 |
| TIME | 3 | hh-mm-ss | 时间 |
| YEAR | 1 | yyyy | 年份 |
| DATETIME | 8 | yyyy-mm-dd hh-mm-ss | 日期时间 |
| TIMESTAMP | 4 | yyyy-mm-dd hh-mm-ss | 日期时间 时间戳 |
表操作-修改
添加字段
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 修改数据 UPDATE 删除数据 DELETE
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,...);
DML-修改数据
UPDATE 表名 SET 字段名1 = 值1 , 字段名2 = 值2,...[WHERE 条件];
DML-删除数据
DELETE语句不能删除某一个字段的值
DELETE FROM 表名 [WHERE 条件];
DQL语句
查询关键字:SELECT
SELECT
字段列表
FROM
表名列表
WHERE
条件列表
GROUP BY
分组字段列表
HAVING
分组后条件列表
ORDER BY
排序字段列表
LIMIT
分页参数
DQL-基本查询
查询多个字段
SELECT 字段1,字段2,字段3,... FROM 表名;
-- 查询返回所有地段
SELECT * FROM 表名;
设置别名
SELECT 字段1 [AS 别名1],字段2 [AS 字段2],... FROM 表名;
-- 设置别名也可以不用 AS 关键字 直接在字段名后面空格跟别名即可
去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;
DQL-条件查询
语法
SELECT 字段列表 FROM 表名 WHERE 条件列表;
条件
| 运算符 | 功能 |
|---|---|
| > | 大于 |
| >= | 大于等于 |
| < | 小于 |
| <= | 小于等于 |
| = | 等于 |
| <>或!= | 不等于 |
| BENTWEEN ...AND ... | 在某个范围之内(含最小,最大值) |
| IN(...) | 在IN之后列表中的值，多选一 |
| LIKE占位符 | 模糊匹配(_ 匹配单个字符, % 匹配任意多字符) |
| IS NULL | 是NULL |
| AND或&& | 并且(多个条件成立) |
| OR或 | |
| NOT或! | 非 不是 |
DQL-聚合函数
作用于表的某一列，null值不参与聚合函数计算 常见聚合函数
| 函数 | 功能 |
|---|---|
| count | 统计数量 |
| max | 最大值 |
| min | 最小值 |
| avg | 平均值 |
| sum | 求和 |
| 语法 |
SELECT 聚合函数(字段列表) FROM 表名 [WHERE 条件];
DQL-分组查询
WHERE 和 HAVING区别 执行时机不同；WHERE是分组之前过滤，不满足WHERE条件，不参与分组；而HAVING是对分组后的结果进行过滤 判断条件不同：WHERE不能对聚合函数进行分组，而HAVING可以 语法
SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];
DQL-排序查询
排序方式
| 关键字 | 描述 |
|---|---|
| ASC | 升序(默认值) |
| DESC | 降序 |
如果多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序 语法
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1,字段2 排序方式2;
DQL-分页查询
起始索引从0开始，起始索引=（查询页码-1）*每一页的记录数 分页查询是数据库的方言，不同的数据库有不同的实现 如果查询的是第一页数据，起始索引可以省略，直接简写为LIMIT 10 语法
SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;
DQL-执行顺序
SELECT --4
字段列表
FROM --1
表名列表
WHERE --2
条件列表
GROUP BY --3
分组字段列表
HAVING
分组后条件列表
ORDER BY --5
排序字段列表
LIMIT --6
分页参数
DCL
管理数据库用户、控制数据库的访问权限
DCL-管理用户
查询用户
USE mysql;
SELECT * FROM user;
创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
修改用户密码
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';
删除用户
DROP USER '用户名'@'主机名';
DCL-权限控制
| 权限 | 说明 |
|---|---|
| ALL,ALL PRIVILEGES | 所有权限 |
| SELECT | 查询数据 |
| INSERT | 插入数据 |
| UPDATE | 更新数据 |
| DELETE | 删除数据 |
| ALTER | 修改表 |
| DROP | 删除数据库、表、视图 |
| CREATE | 建立数据库、表 |
多个权限之间，使用,号分隔 授权和撤销权限时，数据库名和表名都可以使用*通配符，表示所有 查询权限
SHOW GRANTS FOR '用户名'@'主机名';
授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
函数
字符串函数
| 函数 | 功能 |
|---|---|
| CONCAT(S1,S2,...) | 字符串拼接，将传入的多个字符串拼接成一个 |
| LOWER(value) | 将字符串value全部转为小写 |
| UPPER(value) | 将字符串value全部转为大写 |
| IPAD(value,n,pad) | 左填充，用字符串pad对value的左边进行填充，达到n个字符长度 |
| RPAD(value,n,pad) | 右填充，用字符串pad对value的右边进行填充，达到n个字符长度 |
| TRIM(value) | 去除字符串头部和尾部的空格 |
| SUBSTRING(value,index,len) | 返回字符串value从index下标开始len长度的字符串 |
| 示例 |
--将员工表中工号调整至5位数，不足的用0填充
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) | 返回date日期/时间加上expr后的值 |
| 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 | 如果val为true返回res1，...否则返回default默认值 |
| CASE [expr] WHEN [val1] THEN [res1] ... ELSE [default] END | 如果expr的值等于val1，返回res1，...否则返回default默认值 |
约束
所用与表中字段上的规则，用于限制存储在表中的数据 保证数据库中数据的正确、有效性和完整性 约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束
| 约束 | 描述 | 关键字 |
|---|---|---|
| 非空约束 | 限制该字段的数据不能为null | NOT NULL |
| 唯一约束 | 保证该字段的所有数据都是唯一的、不重复的 | UNIQUE |
| 主键约束 | 主键是一行数据的唯一标识，要求非空且唯一 | PRIMARY KEY |
| 默认约束 | 保存数据时，如果未指定该字段的值，则采用默认值 | DEFAULT |
| 检查约束 | 保证字段值满足某一条件 | CHECK |
| 外键约束 | 用来让两张表的数据之间建立连接，保证数据的一致性和完整性 | FOREIGN KEY |
外键约束
具有外键的表称为子表，外键关联的表是父表 添加外键
CREATE TABLE 表名(
字段名 数据类型,
...
[CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名)
);
--创建完成后添加外键
ALTER TBALE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名);
删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
删除/更新行为
| 行为 | 说明 |
|---|---|
| NO ACTION | 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。(与RESTRICT一致) |
| RESTRICT | |
| CASCADE | 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则也删除/更新外键在子表中的记录 |
| SET NULL | 当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null |
| SET DEFAULT | 父表有更改时，子表将外键列设置成一个默认的值(lnnodb不支持) |
ALTER TBALE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名) ON UPDATE CASCADE ON DELETE CASCADE;
多表查询
多表关系
一对多 多对多 一对一 三种多表关系一对多
一个部门对应多个员工 一个员工对应一个部门 在多的一方建立外键，指向一的一方的主键
多对多
一个学生可以玄修多个课程 一门课程也可以被多个学生选择 建立第三方中间表 至少包含两个外键 分别关联两张表的主键
一对一
用户和用户详情 多用于多表拆分 将基础字段放在一张表中 其他详情字段放在另一张表中 在任意一张表加入外键 关联另一方的主键 并设置外键是唯一的
连接查询
从多张表中查询数据笛卡尔积：是指在数学中，两个集合A集合和B集合的所有组合情况（在多表查询时，要消除无效的笛卡尔积）内连接：相当于查询A、B交集部分数据外连接：
左外连接：查询左表所有数据，以及交集部分数据 右外连接：查询右表所有数据，以及交集部分数据
自连接：当前表与自身的连接查询，自连接必须使用表别名
内连接
隐式内连接
SELECT 字段列表 FROM 表1,表2 WHERE 条件...;
显式内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件...;
外连接
左外连接
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件 ...;
--相当于查询表1(左表)的所有数据 包含 和表2交集部分的数据
右外连接
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件 ...;
--相当于查询表1(左表)的所有数据 包含 和表2交集部分的数据
自连接
自连接查询 可以是内连接查询也可以是外连接查询
SELECT 字段列表 FROM 表1 别名1 JOIN 表1 别名2 ON 条件...;
联合查询-union,union-all
对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集 多张表的字段数和字段类型要保持一致才能合并 不然会报错 union all会将所有数据直接合并，union会在合并后进行去重处理
SELECT 字段列表 FROM 表1 ...
UNION [ALL]
SELECT 字段列表 FROM 表2 ...;
-- 不加ALL关键字会进行去重处理
子查询
在SQL语句中嵌套SELECT语句，称为嵌套查询，也称子查询 子查询外部的语句可以是INSERT/UPDATE/DELETE/SELECT的任何一个 根据子查询结果不同，可以分为： 标量子查询（子查询结果为单个值） 列子查询（子查询结果为一列） 行子查询（子查询结果为一行） 表子查询（子查询结果为多行多列） 根据子查询的位置，分为:WHERE之后、FROM之后、SELECT之后
SELECT 字段列表 FROM 表名 WHERE 条件语句 = (SELECT 字段列表 FROM 表名);
标量子查询
子查询返回的结果是单个值(数字、字符串、日期等)
列子查询
子查询返回的结果是一列（可以是多行），常用的操作符:IN、NOT IN、ANY、SOME、ALL
| 操作符 | 描述 |
|---|---|
| IN | 在指定的结合范围内，多选一 |
| NOT IN | 不在指定的集合范围内 |
| ANY | 子查询返回列表中，有任意一个满足即可 |
| SOME | 与ANY等同 |
| ALL | 子查询返回列表的所有值都必须满足 |
行子查询
SELECT 字段列表 FROM 表名 WHERE (条件字段1,条件字段2) = (SELECT 字段1,字段2 FROM 表名 条件列表);
表子查询
子查询返回的结果是多行多列，常用的操作符 IN
事务
事务是一组操作的集合，是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，这些操作要么同时成功，要么同时失败。
事务操作
查看/设置事务提交方式
SELECT @@autocommit;
SET @@autocommit = 0; -- 1为自动提交 0为手动提交
提交事务
COMMIT;
回滚事务
ROLLBACK;
开启事务
START TRANSACTION 或 BEGIN;
-- 提交事务
COMMIT;
-- 回滚事务
ROLLBACK;
事务四大特性
原子性；事务是不可分割的最小操作单元，要么全部成功要么全部失败 一致性：事务完成时，必须使所有的数据都保持一致状态 隔离性：数据库系统提供的隔离机制，保证事务在不受外部并发影响的独立环境下运行 持久性：事务一旦提交或回滚，它对数据库中数据的改变是永久的
并发事务问题
| 问题 | 描述 |
|---|---|
| 脏读 | 一个事务读到另外一个事务还没有提交的数据 |
| 不可重复读 | 一个事务先后读取同一条记录，但两次读取的数据不同 |
| 幻读 | 一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在 |
事务隔离级别
安全性越高性能越差
隔离级别 脏读 不可重复读 幻读 Read uncommitted √ √ √ Read committed x √ √ Repeatable Read(默认) x x √ Serializable x x x 查看事务隔离级别
SELECT @@TRANSACTION_ISOLATION;
设置事务隔离级别
SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL [隔离级别];
0 条评论