文章对应视频教程，B站搜索MySQL 基础+高级篇- 数据库 -sql -尚硅谷
目录
MySQL——基础介绍
常见命令
- 查看mysql版本
# 方式一:
mysql > select version(); # 登录到mysql服务端
# 方式二:
mysql --version; # 没有登录到mysql服务端
# 方式三:
mysql --V; # 没有登录到mysql服务端
- 常见操作命令
show databases; # 查看所有数据库
use [库名]; # 打开指定数据库
show tables; # 查看当前所有表
show tables from [库名]; # 查看其他库的所有表
# 简单创建一个表
create table [表名]（
[列名] [具体类型]
[列名] [具体类型]
# ...
）
语法规范
- 不区分大小写，但是建议关键字大写，表名、列名小写。
- 每条命令用分号结束。
- 每条命令根据需要，可以缩进或者换行。
- 注释：单行注释 ( # 注释文字) ，单行注释 （ -- 注释文字）， 多行注释 （ /* 注释文字 */）。
语言分类
- 数据定义语言DDL（Data Definition Language），关于库和表的定义等语句。
- 数据操纵语言DML（Data Manipulation Language），通过它可以实现对数据库的基本操作，增删改语句。
- 数据查询语言DQL（Data Query Language），数据库查询语句，基础查询、条件、排序、常见函数、分组、连接、子查询等。
- 数据控制语言DCL（Data Control Language），用来设置或更改数据库用户或角色权限的语句。
- 事务控制语言TCL（Transaction Control Language），事务和事务处理控制语句。
MySQL——创建数据库表
*.sql脚本，https://download.csdn.net/download/Timor61/12615938。
MySQL——数据查询语言DQL
基础查询
# 基础查询
/*
语法： select 基础列表 from 表名;
特点： 基础列表可以是：表字段、常量值、表达式、函数
查询结果为一个虚拟表格
*/
# 使用该数据库
USE myemployees;
# 查询单个字段（查询员工编号）
SELECT `employee_id` FROM employees;
# 查询多个字段（查询员工姓名）
SELECT `first_name`,`last_name` FROM employees;
# 查询全部字段（查询员工姓名）
# * 代表所有字段，返回数据格式与原表格式对应
SELECT * FROM employees;
# 查询常量值、表达式、函数
SELECT 100;
SELECT "yuehan";
SELECT 100*99;
SELECT VERSION(); # 显示数据库版本
# 起别名 ( 便于理解 )
# SELECT 字段 AS[可省略,换空格] 别名 FROM employees;
SELECT `employee_id` AS eId FROM employees;
# 去重 DISTINCT
SELECT DISTINCT `employee_id` AS eId FROM employees;
# + 号问题:
# mysql中 + 仅能作为 运算符 而不能作为 连接符
# + 号左右均为数值型对其进行加法运算，如果一方为字符型则试图转换为数值型，如果转换失败则为0,如果一方为null，则结果为nulll;
# CONCAT(str1,str2,...) 可拼接函数
SELECT CONCAT(first_name,last_name) AS e_name FROM employees;
# 判断是否为空
# IFNULL(expr1,expr2) 最后为返回值
SELECT IFNULL(commission_pct,0) AS "奖金率",commission_pct FROM employees;条件查询
# 条件查询
/*
语法： select 基础列表 from 表名 where 筛选条件;
分类: 按条件表达式筛选
条件运算符：> < = != <> >= <=
按逻辑表达式筛选
逻辑运算符：
&& 或 and ：两个条件 均为true
|| 或 or: 两个条件 一个为true
！ 或 not: 条件取反，本身false则返回true
模糊查询
关键字：
like：% 任意多个字符，包含 0 个字符, _ 任意单个字符,
当遇到查询中包含关键字可使用\进行转义或在查找符号前 +任意字符 候用 ESCAPE '$'声明该字符为转义标识。
between and： 查询包括范围边界。
in：判断某字段的值是否属于in列表中的某一项，值类型统一、但里面不支持like通配符查询
is null： = 不能判断null ，采用该方式可以判断是否为 NULL,安全等于 <=>也可以，但是理解性不太友好。
*/
# 使用该数据库
USE myemployees;
# 按条件表达式筛选
# (薪资大于 1.2*10000的员工)
SELECT * FROM employees WHERE salary > 1.2*10000;
# (查询部门编号不等于90的员工名和部门编号)
SELECT last_name,department_id FROM employees WHERE department_id != 90;
# 等价与上面
# SELECT last_name,department_id FROM employees WHERE department_id <> 90;
# 按逻辑表达式筛选
# (工资在 1*10000 ~ 1.2*10000 之间的员工名和工资)
SELECT last_name,salary FROM employees WHERE salary >=1*10000 AND salary <= 1.2*10000;
# (部门编号不在 90~ 110 之间,或者薪资大于 1.5 * 10000 的员工名和部门编号)
SELECT last_name,department_id,salary FROM employees WHERE department_id<90 OR department_id>110 OR salary >1.5 *10000;
# 模糊查询
# % 任意多个字符,包含 0 个字符 _ 任意单个字符
# (查询员工名 包含字符 a 的员工信息)
SELECT * FROM employees WHERE last_name LIKE '%abc%';
# (查询员工名 包含字符 第三个字符为 a 的员工信息)
SELECT * FROM employees WHERE last_name LIKE '__a%';
# 特殊 名字包含关键字符 (查询员工名 包含字符 第二个字符为 _ 的员工信息)
# SELECT * FROM employees WHERE last_name LIKE '_\_%'; # 转义
SELECT * FROM employees WHERE last_name LIKE '_$_%' ESCAPE '$';# 声明 $为转义标识
# BETWEEN AND (查询员工编号在90 ~ 110 之间的员工信息)
SELECT * FROM employees WHERE employee_id BETWEEN 90 AND 110;
# IN (查询指定类型 ['IT_PROG','AD_VP','AD_PRES'] 的工种编号的员工和他的工种编号)
SELECT * FROM employees WHERE job_id IN('IT_PROG','AD_VP','AD_PRES');
# IS NULL (查询没有奖金的员工名 和 奖金率 )
SELECT last_name,commission_pct FROM employees WHERE commission_pct IS NULL;
# 安全等于 <=>
# SELECT last_name,commission_pct FROM employees WHERE commission_pct <=> NULL;
排序查询
# 排序查询
/*
语法： select 基础列表 from 表名 [where 筛选条件] order by 排序列表 [asc|desc];
特点： order by 支持单个字段、多个字段、达式、函数、别名，一般放在最后出limit以外。
*/
# 按工资从高到低排列，查询员工信息
SELECT * FROM employees ORDER BY salary DESC;
SELECT * FROM employees ORDER BY salary ASC; # asc为默认
# 按入职先后，查询部门编号大于 90 的 员工信息
SELECT * FROM employees WHERE department_id > 90 ORDER BY hiredate ASC;
# 按年薪从高到低 显示员工信息 年薪 = 薪资+ 奖金率
SELECT *,salary* 12*(1+ IFNULL(commission_pct,0)) AS "年薪"
FROM employees ORDER BY "年薪" DESC;
# 按姓名长度显示员工姓名和工资
SELECT LENGTH(last_name) AS "名字长度",last_name,salary
FROM employees ORDER BY LENGTH(last_name) DESC;
# 多字段排序 ( 先按照工资升序，再按照员工编号降序)
SELECT * FROM employees ORDER BY salary ASC,employee_id DESC;常见函数
结构：函数名称(expr1、expr2、...)
分类：
- 单行函数：字符函数、数学函数、日期函数、其他函数、流程控制函数。
- 分组函数：sum 求和、avg 平均数、max min最大最小、count 计算个数等。
# 单行函数
# 字符函数
# 1. length 求长度
SELECT LENGTH('join');
SELECT LENGTH('张二'); # 一个汉字占用 3字节
SHOW VARIABLES LIKE '%char%';
# 2. concat 拼接字段
SELECT CONCAT(first_name,last_name) AS "姓名" FROM employees;
# 3. upper、lower 转为大小写
SELECT UPPER('join');
SELECT LOWER('JOIN');
# 4. substr 截取字符串 (索引从1开始)
SELECT SUBSTR('我名叫屁颠是只小企鹅',2) AS "输出";
SELECT SUBSTR('我名叫屁颠是只小企鹅',2,2) AS "输出";
# 5. instr(str,substr) 返回子串第一次出现位置,找不到返回 0
SELECT INSTR('我名叫屁颠是只小企鹅','小企鹅') AS "输出";
# 6. trim 除去空格或指定字符
SELECT TRIM('小企鹅' FROM '我名叫屁颠是只小企鹅') AS "输出";
# 7.lpad 用指定字符左填充达到指定长度
SELECT LPAD('小企鹅',10,'***') AS "输出";# *******小企鹅
# 8.replace 替换
SELECT REPLACE('我名叫屁颠是只小企鹅','小企鹅','小鳄鱼') AS "输出"; #我名叫屁颠是只小鳄鱼
# 数学函数
# 1. round 四舍五入
SELECT ROUND(1.45); # 1
SELECT ROUND(1.49456,2); # 1.49
# 2. ceil 向上取整
SELECT CEIL(1.45); # 2
# 3. floor 向下取整
SELECT FLOOR(1.45); # 1
# 4. truncate 截断
SELECT TRUNCATE(1.66999,2); # 1.66
# 5. mod 取余 a-a/b*b
SELECT MOD(9,2); # 1
# 日期函数
# now 返回当前系统日期 + 时间
SELECT NOW(); # 2020-07-15 21:52:27
# curdate 返回当前系统日期 + 不包含时间
SELECT CURDATE(); # 2020-07-15
# curtime 返返回当前时间 + 不包含系统日期
SELECT CURTIME(); # 21:52:27
# 获取指定部分 [年月日时分秒]
SELECT YEAR(NOW()) AS "year"; # 2020
SELECT MONTH(NOW()) AS "mouth"; # 7
SELECT MONTHNAME(NOW()) AS "mouth_name"; # July
# str_to_date 将日期格式字符转换成指定格式的日期
SELECT STR_TO_DATE('2020-07-15','%Y-%c-%d') AS "res"; # 2020-07-15
# 查询入职日期 为1992-04-03 的员工信息
SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3 1992','%c-%d %Y');
# date_format 将日期转换成字符
SELECT DATE_FORMAT(NOW(),'%Y年-%m月-%d日') AS "res";# 2020年-07月-15日
# 返回日期之间差值
SELECT DATEDIFF('2020-10-30','2020-10-1'); # 29天
# 其他函数
SELECT VERSION(); # 数据库版本号 5.7.25
SELECT DATABASE(); # 当前数据库 myemployees
SELECT USER(); # 当前用户 root@localhost
# 查询员工姓名，返回是否有奖金
SELECT last_name,commission_pct,IF(commission_pct IS NULL,'无','有') AS "备注" FROM employees;
# case 函数： switch case
/*
结构
case 表达式
when 常量1 显示语句;
when 常量2 显示语句;
when 常量3 显示语句;
else 都不匹配
end
案例：
查询员工工资，要求
部门号= 30,显示工资1.1倍;
部门号= 40,显示工资1.2倍;
部门号= 50,显示工资1.3倍;
其他部门,显示原工资;
*/
SELECT
salary AS "原始工资",
department_id,
CASE department_id
WHEN 30 THEN salary * 1.1
WHEN 40 THENsalary * 1.2
WHEN 50 THENsalary * 1.3
ELSE salary
END AS "工资"
FROM
employees;
# 分组函数
/*
常用函数：sum 求和、avg 平均数、max min最大最小、count 计算个数
特点：sum avg 一般用于处理数值类型
max min count 处理任何类型
以上分组均忽略null
*/
SELECT SUM(salary) AS "和",AVG(salary) AS "平均",MIN(salary) AS "最小",MAX(salary) AS "最大",COUNT(salary) AS "个数" FROM employees;
# 经常使用 count(*) 统计行数
# count(1) 相当于在原表增加1列且值为1,统一1个个数
# 效率问题：MYISAM存储引擎下 count(*) 推荐
# INNODB存储引擎下 count(*),count(1)差不多,前者比后者高一些
SELECT COUNT(*) FROM employees;
# 分组查询
/*
select 分组函数,列 from 表 [where 条件] group by 分组列表 [order by 子句];
特点： 分组查询分2种：(优先考虑分组前筛选)
数据源 位置 关键字
分组前筛选 原始表 group by前 where
分组后筛选 分组后结果集 group by后 having
支持单个多个字段(多个之间无顺序要求)、表达式或函数。
*/
# 查询每个工种的最高工资
SELECT MAX(salary),job_id FROM employees GROUP BY job_id;
# 查询邮箱中包含a字符，每个部门的平均工资
SELECT AVG(salary),department_id FROM employees WHERE email LIKE "%a%" GROUP BY department_id;
# 查询每个部门的员工个数 并且 > 2
SELECT COUNT(*),department_id FROM employees GROUP BY department_id HAVING COUNT(*) > 2;
# 多字段分组
# 查询每个部门每个工种的平均工资
SELECT AVG(salary),job_id,department_id FROM employees GROUP BY job_id,department_id;
连接查询
# 连接查询
/*
含义：多表查询，查询的字段来自于多个表
分类：按功能分类
内链接：等值连接、非等值连接、自链接
外连接：左外连接、右外连接、全外连接
交叉连接
*/
USE myemployees;
# 1. 等值连接 (查询员工名和对应部门名)
# 为表起别名： 提高语言简洁度、区分多个重名的字段,若起了别名则查询字段就不能使用原表名去限定
SELECT last_name,department_name FROM employees AS es,departments AS ds WHERE es.department_id = ds.department_id;
# 查询每个城市的部门个数
SELECT COUNT(*),city FROM locations AS ls,departments AS ds WHERE ls.location_id=ds.location_id GROUP BY city;
# 非等值连接 [= 换为 其他! != < >等]
# 自连接 [ 自己 与 自己相连 ]
# 查询员工名和领导名
SELECT es.employee_id,es.last_name,ms.employee_id,ms.last_name FROM employees AS es,employees AS ms WHERE es.manager_id=ms.employee_id;# sql99语法
/*
语法：select 列表 from 表1 别名 [连接类型] join 表名2 别名 on 连接条件
[where 筛选条件] [group by 分组] [having 帅选条件] [order by 排序列表]
分类：内连接： inner join
外连接：左外连接 left join、右外连接 right join、全外连接 full join
交叉连接：cross
*/
# 内连接（从结果表中删除与其他被连接表中没有匹配行的所有行）
# 查询员工名、部门名
SELECT e.last_name,d.department_name FROM employees e INNER JOIN departments d ON e.department_id = d.department_id;
# 查询名字包含 e 的员工名和工种名
SELECT e.last_name,j.job_title FROM employees e INNER JOIN jobs j ON e.job_id = j.job_id WHERE e.last_name LIKE "%e%";
# 外连接 （主表从表的交集部分 + 主表有从表没有,主表字段显示为null）
# left 左为主表 right 右为主表
# 查询哪个部门没有员工
SELECT d.department_id,d.department_name,e.last_name,e.department_id as "e_de_id" FROM departments d LEFT OUTER JOIN employees e ON d.department_id = e.department_id WHERE e.department_id IS NULL;
# 全外连接 (左表就是全部显示[左连接] + 左表和右表都不做限制，所有的记录都显示，两表不足的地方均为NULL )
# 交叉连接 （笛卡尔乘积）子查询
# 子查询
/*
含义：出现在其他语句中的select语句，称为子查询或内查询
内部嵌套其他select语句的查询，称为主查询或外查询
分类：按子查询出现的位置：
select 后面：仅支持标量子查询
from 后面：支持表子查询
where或having后面：支持标量子查询、列子查询、行子查询 【*】
exists后面：支持表子查询
按结果集的行列数不同：标量子查询（结果集1行1列）、列子查询（1列多行）、行子查询（一行多列）、表子查询（多行多列）
*/
# where或having后面
# 标量子查询
# 谁的工资比 abel 高
SELECT * FROM employees WHERE salary>(
# 1行1列
SELECT salary FROM employees WHERE last_name = 'Abel');
# 返回job_id 与141 员工相同工资比 143 员工多的员工的姓名、job、工资
SELECT last_name,job_id,salary FROM employees
WHERE job_id = (
SELECT job_id FROM employees WHERE employee_id = 141)
AND salary> (
SELECT salary FROM employees WHERE employee_id = 143);
# 列子查询 (一列多行 因此 常跟在 in/not in 、any | some 、all)
SELECT * FROM employees WHERE job_id IN (SELECT job_id FROM jobs);
# 返回location_id是1400 / 1700 部门中所有员工姓名
SELECT last_name FROM employees WHERE department_id IN(
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700)
);
# 行子查询（结果为1行多列 或多行多列）
# 查询员工编号最小并且工资最高的员工信息
SELECT employee_id,last_name,salary FROM employees WHERE (employee_id,salary) = (
SELECT MIN(employee_id),MAX(salary) FROM employees
);
# select 后面
# 查询每个部门的员工个数
SELECT d.*,(
SELECT COUNT(*) FROM employees e WHERE e.department_id = d.department_id
) FROM departments d;
# from 后面
# 查询每个部门的平均工资和部门名称
SELECT avg_s.salary,d.department_name FROM (
SELECT AVG(salary) as salary,department_id FROM employees GROUP BY department_id
) avg_s LEFT JOIN departments d ON avg_s.department_id = d.department_id;
# exists 后面
# 查询有员工的部门名
SELECT department_name FROM departments d WHERE EXISTS(
SELECT * FROM employees e WHERE d.department_id = e.department_id
);分页查询
# 分页查询
/*
语法： select 列表 from 表1 【
[join type] join 表2 on 连接条件
where 筛选条件
group by 分组字段
having 分组筛选条件
order by 排序
】limit offset,size;
offset： 索引开始(0),页码。
size：显示个数。
*/
# 查询前20条
SELECT * FROM employees LIMIT 0,20;
# 查询11条 - 25条
SELECT * FROM employees LIMIT 10,15;
# 查询有奖金员工信息,并且工资较高前10条
SELECT * FROM employees WHERE commission_pct IS NOT NULL ORDER BY salary DESC LIMIT 0,10;联合查询
# 联合查询
/*
union 合并，将多条语句查询结果合并成一个结果表
语法：语句1 union 语句2
应用：来自多个表，且之间没有连接关系，但查询的信息结果必须一致的。
*/
# 查询部门编号> 90 或邮箱包含 a 的员工信息
SELECT * FROM employees WHERE department_id> 90
UNION
SELECT * FROM employees WHERE email LIKE "%a%";MySQL——数据操纵语言DML
包括基础的增加、修改、删除。
# DML语言
/*
插入
insert into 表(列名,...) values(值1,...);
写法：列数和值个数必须一致
可以省略列名，默认所有列，而且与表顺序一致
列顺序可以调换
不可以为null的列，必须插入值
插入值的类型需要与列类型保持一致或者兼容
自增可以使用default来默认
insert into 表 set 列名= 值,列名= 值.... ;
*/
USE myemployees;
# 方式一
INSERT INTO locations(location_id,street_address,postal_code,city,state_province,country_id) VALUES(DEFAULT,'china',"036900","shanxi",NULL,"IT");
# 方式二
INSERT INTO departments SET department_name="ca",manager_id=200,location_id=1700;
# 比较
# 方式一支持插入多行，方式二不支持
# 方式一支持子查询,方式二不支持/*
修改
1. 修改单表
语法：update 表 set 列=值,列=值,.... where 筛选条件
2. 修改多表
update 表1 别名,表2 别名 set 列=值,列=值,.... where 筛选条件 and 筛选条件
update 表1 别名 [join type] join 表2 别名 set 列=值,列=值,... where 筛选条件 and 筛选条件
*/
# 修改单表
UPDATE departments SET department_name="cz" WHERE department_id=271;
/*
删除
方式一
1. 单表删除
语法：delete from 表 where 筛选条件
2. 多表删除
语法：
delete 表1别名,表2别名 from 表1 别名,表2 别名 set 列=值,列=值,.... where 筛选条件 and 筛选条件
delete 表1别名,表2别名 from 表1 别名 [join type] join 表2 别名 set 列=值,列=值,.... where 筛选条件 and 筛选条件
方式二
truncate 全部删除
语法：truncate table 表
*/
# 删除单表
DELETE FROM departments WHERE department_id=271;
# 清空全部
truncate table departments;
# 区别
# 1. delete 可以加条件 ，truncate 不可以
# 2. delete 删除对于有自增字段，下次还是从断点处开始，truncate 从0开始
# 3. 全部删除 建议 truncate
# 4. delete 支持事务删除回滚，truncate不支持事务删除回滚MySQL——数据定义语言DDL
库和表的管理
创建create、alter、drop。
# DDL数据定义语言
/*
库和表管理：创建create、修改alter、删除drop
库的管理
1. 创建数据库
语法：create database [ IF NOT exists ] 库名;
2. 修改数据库
语法：rename database 原库名 To 库名; // 可能导致数据库数据丢失
修改某些属性：alter database book character set gbk;
3. 删除数据库
语法：drop database [ if not exists ] 库名;
*/
# 创建数据库
CREATE DATABASE IF NOT EXISTS TempBook;
# 修改数据库
RENAME DATABASE TempBook To TempBo;
# 修改字符集
ALTER DATABASE book CHARACTER SET gbk;
# 删除数据库
DROP DATABASE IF NOT EXISTS tempbook;/*
库和表管理：创建create、修改alter、删除drop
表的管理
1.创建表
语法：create table 表(
列名 类型(长度) [约束],
列名 类型(长度) [约束],
列名 类型(长度) [约束],
.....
)
表的修改
1. 修改列名 alter table 表 change [column] 原列名 新列名 类型;
2. 修改类型、约束 alter table 表 modify column 原列名 类型;
3. 添加新列 alter table 表 add column 新列名 类型;
4. 删除列 alter table 表 drop column 列名;
5. 修改表名 alter table 表 rename to 新表名;
表的删除
drop table 表名;
表的复制
1.复制表结构：create table 复制表 like 被复制表;
2.复制表结构+ 数据：create table 复制表 select * from 被复制表;
2.复制表结构+ 部分数据：create table 复制表 select [列] from 被复制表 where 筛选条件;
*/
# 创建表
USE tempbook;
CREATE TABLE book(
id INT,# 编号
bName VARCHAR(20), # 书名
price DOUBLE, # 价格
auhtorId INT, # 作者id
publishDate DATETIME # 出版日期
);
DESC book; # 查看
CREATE TABLE author(
id INT,# 编号
aName VARCHAR(20) # 作者名
);
DESC author; # 查看
# 修改列名
ALTER TABLE book CHANGE COLUMN publishDate pDate DATETIME;
# 修改类型
ALTER TABLE book MODIFY COLUMN pDate TIMESTAMP;
# 添加新列
ALTER TABLE book ADD COLUMN annual DOUBLE;
# 删除列
ALTER TABLE book DROP COLUMN annual;
# 修改表名
ALTER TABLE book RENAME TO book_new;
# 删除表名
DROP TABLE IF NOT EXISTS book_new;
# 显示
SHOW TABLES;
# 复制表结构
CREATE TABLE copy_book LIKE book;
# 复制表结构+ 数据
CREATE TABLE copy_all_data_book SELECT * FROM book;
# 复制表结构+ 部分数据
CREATE TABLE copy_part_data_book SELECT id,bName FROM book where id=1;数据类型
整型：
| tinyint | 很小的整数 | 8位二进制 | 1字节整数 |
| smallint | 小的整数 | 16位二进制 | 2字节整数 |
| mediumint | 中等大小的整数 | 24位二进制 | 3字节整数 |
| int | 普通大小的整数 | 32位二进制 | 4字节整数 |
| bigint | 大的整数 | 64位二进制 | 8字节整数 |
- 均可加 UNSIGNED（unsigned 表示无符号）ZEROFILL （零填充）
- 均可加长度限制 通常情况没有意义，不会限制值的合法范围
小数类型：
| float | 单精度浮点数 |
| double | 双精度浮点数 |
| decrimal | 压缩严格的定点数 |
- decrimal 可以存储比bigint 还大的数据，能存储精确的小数
- float 和 double 有取值范围
日期类型： year、time、date、datetime、timestamp
- 尽量使用 timestamp 空间效率高于datetime
枚举类型：将不重复的数据存储到一起，内部存储为整数，因此避免使用数字作为枚举常量，容易混乱。
文本，二进制类型：
| char | 0~255 |
| varchar | 0~65535 |
| tinyblob | 0~255字节 |
| blob | 0~65535字节 |
| mediumblob | 0~167772150字节 |
| longblob | 0~4294967295字节 |
| tinytext | 0~255字节 |
| text | 0~65535字节 |
| mediumtext | 0~167772150字节 |
| longtext | 0~4294967295字节 |
| varbinary(M) | 0~M个字节的变长字节字符串 |
| binary(M) | 0~M个字节的定长字节字符串 |
- varchar 可以存储变长字符串 比定长类型更省空间，使用额外的1~2字节存储字符串长度，当存储内容超过指定长度，则内容会被截取。
- char 是定长的 根据定义的字符串长度分配足够空间，对于内容不足，会使用空格进行填充，适合存储短的字符串，或者所存储的大量内容长度近乎一致，同样超过指定长度也会被截取。
使用策略 ：
- 对于经常变更数据而言，char比varchar好，char不会产生碎片，对于短的列，char也在存储上更有效率，使用时要注意分配的空间，长的列排序时候会消耗很多内存。
- 避免使用text/blob 类型，查询时会开辟临时表，导致性能开销大。
表的约束
# 常见约束
/*
含义：一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠
分类：
not null：非空，该字段值不可以为空
default: 用于保证该字段有默认值
primary key：主键，用于保证该字段值具有唯一性，并且非空
unique：唯一，用于保证该字段值具有唯一性，可以空
check：检查约束[mysql中不支持]
foreign key：外键，用于限制两个表的关系，用于保证该字段的值必须来自主表的关联列的值
使用：
一般在创建表和修改表时候
约束添加分类：
列级约束 [都可以,但是 外键约束无效果 ]
表级约束 [除了非空、默认，其他都支持]
*/
# 创建表添加列约束 [语法：列名 类型 约束, 只支持：默认、非空、主键、唯一]
DROP TABLE IF EXISTS student;
DROP TABLE IF EXISTS major;
CREATE TABLE major(
id INT PRIMARY key,# 编号
mName VARCHAR(20) # 课程名
);
CREATE TABLE student(
id INT PRIMARY KEY,# 学生编号
stuName VARCHAR(20) NOT NULL,# 姓名 非空
gender CHAR(1) CHECK(gender='男' OR gender ='女'),# 性别 检查(此处具体查看不支持)
seat INT UNIQUE,# 座位号 唯一
age INT DEFAULT 18, # 年龄 默认
majorId INT REFERENCES major(id) #主修课 外键(此处具体查看表会发现未设置成功)
);
DESC major;
DESC student;
SHOW INDEX FROM student;
# 添加表级约束 [语法：在各个字段下 [constraint 约束名] 类型(列名)]
DROP TABLE IF EXISTS student;
CREATE TABLE student(
id INT,# 学生编号
stuName VARCHAR(20),# 姓名
gender CHAR(1),# 性别
seat INT,# 座位号
age INT, # 年龄
majorId INT, #主修课
CONSTRAINT pk PRIMARY KEY(id),# 主键
UNIQUE(seat),# 唯一
CHECK(gender='男' OR gender ='女'),# 检查
FOREIGN KEY(majorId) REFERENCES major(id) # 外键(可以设置成功)
);
# 通用写法
DROP TABLE IF EXISTS student;
CREATE TABLE student(
id INT PRIMARY KEY,# 学生编号
stuName VARCHAR(20) NOT NULL,# 姓名 非空
gender CHAR(1) CHECK(gender='男' OR gender ='女'),# 性别 检查(此处具体查看不支持)
seat INT UNIQUE,# 座位号 唯一
age INT DEFAULT 18, # 年龄 默认
majorId INT, #主修课
CONSTRAINT fk_stu_major FOREIGN KEY(majorId) REFERENCES major(id) # 外键(此处查看表会发现设置成功)
);
# 添加约束
/*
添加列级约束
alter table 表 MODIFY column 字段 字段类型 约束;
添加表记约束
alter table 表 add [constraint 约束名] 约束类型(字段名) [外键引用];
*/
# 添加非空约束
ALTER TABLE student MODIFY COLUMN stuName VARCHAR(20) NOT NULL;
# 添加外键
ALTER TABLE student ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorId) REFERENCES major(id);
# 添加唯一
# 1. 添加表级约束
ALTER TABLE student ADD UNIQUE(seat);
# 2. 添加列级约束
ALTER TABLE student MODIFY COLUMN seat INT UNIQUE;# 删除约束
# 1.删除非空约束
ALTER TABLE student MODIFY COLUMN stuName VARCHAR(20) NULL;
# 2.删除默认约束
ALTER TABLE student MODIFY COLUMN age INT;
# 3.删除主键
ALTER TABLE student DROP PRIMARY KEY;
# 4.删除唯一
ALTER TABLE student DROP INDEX seat;
# 5.删除外键
ALTER TABLE student DROP FOREIGN KEY fk_stu_major;# 仅仅删除外键,索引仍在
SHOW INDEX FROM student;
# 删除索引
ALTER TABLE student DROP INDEX fk_stu_major;
SHOW INDEX FROM student;
主键与唯一的区别
| 唯一性 | 是否为空 | 是否允许多个存在 | 是否允许组合存在 | |
| 主键 | √ | x | 至多1个 | √，但不推荐 |
| 索引 | √ | √ | 可以多个 | √，但不推荐 |
外键要求
- 要求在从表设置外键关系
- 从表外键列和主表关联列保持类型一致或兼容，名称无所谓
- 主表中的关联列必须是一个key(要保证唯一)
- 插入数据，先插入主表，再插入从表
- 删除数据，先删除从表，在删除主表
MySQL——事务控制语言TCL
事务概念
事务：一个或一组sql语句组成一个执行单元，之间相互依赖，做为一个整体要么全部执行，反之，发生错误，事务将回滚。
存储引擎：
- 存储技术的方式 ，SHOW ENGINES 查看支持的存储引擎。
- 一般 innodb,myisam,memory。innodb支持事务，其他两种不支持。
事务的ACID特性：
- 原子性A: 是指事务是一个不可分割的单位，要么都发生要么都不发生。
- 一致性C: 事务必须使数据库从一个一致性状态到另一个一致性状态。
- 隔离性I：一个事务的执行不能被其他事务干扰，一个事务的内部操作以及使用的数据对并发的其他事务是隔离的，并发事务之间不可互相干扰。
- 持久性D：一个事务一旦提交，就是永久性的。
事务的创建：
- 隐式事务：事务没有明显的开启和结束的标记[insert update delete等]。
- 显示事务：必须将自动提交功能关闭，用 【开启事务】事务语句【结束事务】。
SHOW ENGINES;# 查看支持的存储引擎
SHOW VARIABLES LIKE "autocommit"; # 查看 自动提交事务 是否开启
set autocommit = 0; # 关闭自动提交事务
SHOW VARIABLES LIKE "autocommit"; # 再次查看
# ========== 事务创建案例 ===========
USE tempbook;
# 关闭自动提交事务
set autocommit = 0;
start TRANSACTION;
# 编写事务语句 [...]
SELECT * FROM book;
INSERT INTO book VALUES(3,"wang_3",33,1,NOW());
INSERT INTO book VALUES(4,"wang_4",44,1,NOW());
# 结束事务 [事务执行失败 需要回滚]
COMMIT;# 提交事务
ROLLBACK; # 回滚事务
# 查看结果
SELECT * FROM book;数据库的隔离级别
对于同时运行的多个事务，当这些事务访问数据库中相同数据时，如果没有设置有效的隔离机制，就会导致各种并发问题：
- 脏读：对于两个事务T1、T2，T2正在更新数据但还没有被提交，就被T1所读取，之后T2回滚，T1读取到的就是无效错误的。
- 不可重复读：对于两个事务T1、T2，T2、T1正在读取一个字段，之后T2更新了字段但是T1又再次读取，值就不同了。
- 幻读：对于两个事务T1、T2，T1从一个表读取或修改一个字段，然后T2插入了一些数据行，如果T1再去读取这个表就会发现有多余数据行也被改变。
一个事务与其他事务隔离的程度称之为隔离级别，数据库规定了多种隔离级别，每种隔离级别所针对不同，隔离级别越高，数据一致性越好，但并发能力减弱，具体mysql隔离级别如下：
| 隔离级别 | 描述 |
| READ UNCOMMITED(读未提交数据) | 允许事务读取未被其他事务提交的变更。（脏读、不可重复读、幻读都出现） |
| READ COMMITTED(读已提交数据) | 只允许事务读取已经被其他事务提交的变更。（避免脏读，但幻读和不可重复读仍从在） |
| REPEATABLE READ(可重复读) | 确保事务可以多次从一个字段中读取相同的值，在这个事务持续期间，禁止其他事务对这个字段进行更新。（避免脏读、不可重复读，但幻读仍存在） |
| SERIALIZABLE(串行化) | 确保事务可以从一个表中读取相同行，在这个事务执行期间，对该表的增删改操作均被禁止，所有并发问题均可避免，但是性能低下。 |
READ UNCOMMITED(读未提交数据) 下，脏读出现、不可重复读出现：
READ COMMITTED(读已提交数据) 下，不可重复读出现：
REPEATABLE READ(可重复读) 下，脏读、不可重复读避免，幻读出现：
回滚savepoint事务
当需要回滚事务时，借助 【savepoint 保存点】，来通过 【rollback to 保存点】恢复。
# 开始事务
# 关闭自动提交事务
SET autocommit=0;
START TRANSACTION;
# savepoint演示
DELETE FROM book WHERE id=4; # 被删除
SAVEPOINT a;# 设置保存点
DELETE FROM book WHERE id=5; # 不被删除
# 提交事务
ROLLBACK TO a; # 回滚到 rol_1 号点;
# 查看表
select * from book;视图
# 视图
/*
含义：一张虚拟表，行列的数据来自定义视图的查询中使用的表，
在使用视图时动态生成，只保存sql逻辑，不保存查询结果。
场景：多个地方用到同样的查询
该查询结果使用的sql比较复杂,创建视图简化sql操作，保护数据，提高安全性。
语法：
视图创建：
create view 视图名
as
查询语句;
视图修改：
方式一
create or replace view 视图名
as
查询语句;
方式二
alter view 视图名
as
查询语句;
删除视图:
drop view 视图名,视图名,....;
查看视图:
desc 视图名;
show create view 视图名;
*/
USE myemployees;
# 1.查询有相中包含a字符的员工名、部门名和工种信息
CREATE VIEW view_ldj
AS
SELECT e.last_name,d.department_id,j.job_title FROM employees e JOIN departments d ON e.department_id=d.department_id
JOIN jobs j ON e.job_id =j.job_id;
# 使用
SELECT * FROM view_ldj;
SELECT * FROM view_ldj WHERE last_name LIKE "%a%";
# 修改视图
CREATE OR REPLACE VIEW view_ldj
AS
SELECT e.last_name,j.job_title FROM employees e JOIN jobs j ON e.job_id =j.job_id;
SELECT * FROM view_ldj;
ALTER VIEW view_ldj
AS
SELECT e.last_name,d.department_id FROM employees e JOIN departments d ON e.department_id=d.department_id;
SELECT * FROM view_ldj;
# 查看视图
DESC view_ldj;
SHOW CREATE VIEW view_ldj;
# 删除视图
DROP VIEW view_ldj;/*
视图更新：
1. 对视图做增删改操作也会对原表产生影响，因此视图一般不允许这类操作，为此加权限[只读]
2. 当然并非所有视图更新都会原表改动，具备一下特点则不会更新：
① 包含关键字sql语句：分组函数、distinct、group by、having、union、union all
② 常量视图
③ select中包含子查询、join、from 一个不能更新的视图、where子句的子查询引用了from子句中的表
总结：对于一系列限定操作的视图均不能更新
*/
USE myemployees;
# 创建视图
CREATE OR REPLACE VIEW my_v1
AS
SELECT last_name,email AS "annual_salary" FROM employees;
# 查看视图是否创建
SELECT * FROM my_v1;
# 插入
INSERT INTO my_v1 VALUES('wang','wang@qq.com');
# 查看视图 和 原表 均有数据插入 [同样 删改 也会对原表进行改动] [因此]
SELECT * FROM my_v1;
SELECT * FROM employees;
# 特殊条件下创建的视图 = 略变量
# 变量
/*
系统变量：[属于服务器层面，系统提供]
全局变量：作用范围比较大，服务器每次启动将分配初始值，针对所有连接都有效，但是不能跨重启(重启后消失,如要保证重启仍在需要配置文件)
会话变量：针对当前会话有效,更换连接并不会同步
自定义变量：[用户自己定义的变量，并非系统提供]
用户变量：
作用域：针对当前会话有效,begin end内/外有效。
局部变量：
作用域：仅仅在begin end内有效,且只能放在begin end 中的第一句话位置。
*/
# GLOBAL|SESSION 均不加 默认 session级别
SHOW GLOBAL VARIABLES;# 全局变量
SHOW SESSION VARIABLES;# 会话变量
SHOW GLOBAL VARIABLES LIKE "%auto%";# 查看满足条件的部分系统变量[SHOW [global|session] VARIABLES LIKE "%char%";]
SELECT @@global.autocommit; # 查看指定的某个系统变量[SELECT @@[global|session].变量名;]
SET GLOBAL autocommit=0;# 设置系统变量[SET [global|session] 变量名=值;]
SET @@GLOBAL.autocommit=1;# 设置系统变量[SET @@[global|session].变量名=值;]
# 自定义变量——用户变量
# 声明并且初始化[SET @变量名=值; 或者 SET @变量名:=值;]
SET @wang_a=1;
SELECT @wang_a;
# 赋值
# 方式一 [SET @变量名=值; 或者 SET @变量名:=值;]
# 方式二 [SELECT 字段 INTO 变量名 FROM 表;]
SELECT COUNT(*) INTO @count FROM employees;
SELECT @count;
# 自定义变量——局部变量
# ================此处必须卸载 begin endO(要写在存储过程/方法中) 语句块中 =================
# 声明 [DECLARE 变量名 类型;或DECLARE 变量名 类型 DEFAULT 值;]
DECLARE hello VARCHAR DEFAULT "hello";
DECLARE world VARCHAR DEFAULT "world";
DECLARE out_val VARCHAR;
SET out_val = hello + world;
SELECT out_val;
# 赋值
# 方式一 [SET @变量名=值; 或者 SET @变量名:=值;]
# 方式二 [SELECT 字段 INTO 变量名 FROM 表;]
# ================此处必须卸载 begin end 语句块中 =================存储过程和函数
存储过程概念
存储过程：一组预先编译好的sql语句集合
提高代码重用性、简化操作、减少编译次数和连接次数提高效率。
1.创建语法
CREATE PROCEDURE 存储过程[参数列表]
BEGIN
存储过程(一组SQL集合)
END
参数列表：包含 参数模式， 参数名 ， 参数类型
参数模式：
IN 该参数为输入参数
OUT 该参数为输出参数,有返回值
INOUT 该参数既可以做为输入也可以输出
存储过程结尾：delimiter 重新设置 [delimiter 结束标记]
2.调用语法
CALL 存储过程名 (实参列表)存储过程案例
空参存储过程
# 空参的存储过程
# delimiter $ # 此处修改结束标记后 需修改结束标记
CREATE PROCEDURE myp1()
BEGIN
INSERT INTO book VALUES(6,"wang_6",66,1,NOW()),(7,"wang_7",77,1,NOW()),(7,"wang_7",77,1,NOW());
END;
# END $ #结束标记修改情况下
# 调用
CALL myp1();
# CALL myp1()$ #结束标记修改情况下带in模式的存储过程
USE tempbook;
# 带in模式的存储过程
# delimiter $ # 此处修改结束标记后 需修改结束标记
# 查询指定id和书籍名的信息
CREATE PROCEDURE myp2(IN bName VARCHAR(255),IN id INT)
BEGIN
SELECT * FROM book b where b.bName=bName AND b.id=id;
END;
# END $ #结束标记修改情况下
# 调用
CALL myp2("wang_6",6);
# CALL myp2()$ #结束标记修改情况下带out模式的存储过程
USE tempbook;
# 带in模式的存储过程
# delimiter $ # 此处修改结束标记后 需修改结束标记
# 查询指定id的作者名
CREATE PROCEDURE myp3(OUT bName VARCHAR(255),IN id INT)
BEGIN
SELECT b.bName INTO bName FROM book b WHERE b.id=id;
END;
# END $ #结束标记修改情况下
# 调用
CALL myp3(@bName,6);
SELECT @bName;
# CALL myp3()$ #结束标记修改情况下
# 查询指定id的书籍名和价格
CREATE PROCEDURE myp4(OUT bName VARCHAR(255),OUT price DOUBLE,IN id INT)
BEGIN
SELECT b.bName,b.price INTO bName,price FROM book b WHERE b.id=id;
END;
CALL myp4(@bName,@price,6);
SELECT @bName,@price;带inout模式的存储过程
USE tempbook;
# 带inout模式的存储过程
# delimiter $ # 此处修改结束标记后 需修改结束标记
# 传入a b 返回2倍
CREATE PROCEDURE myp5(INOUT a INT,INOUT b INT)
BEGIN
SET a=a * 2;
SET b=b * 2;
END;
# END $ #结束标记修改情况下
# 调用
SET @a=10;
SET @b=5;
CALL myp5(@a,@b);
SELECT @a,@b;存储过程删除、查看、修改
# 删除存储过程(一次只能删除一个)[DROP PROCEDURE 存储过程名;]
DROP PROCEDURE myp4;
# 查看存储过程 [SHOW CREATE PROCEDURE 存储过程名;]
SHOW CREATE PROCEDURE myp1;
# 修改存储过程 => 删除 + 新建函数概念
# 函数
/*
区别于存储过程： 有且仅有一个返回
1.创建语法：
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
函数体
END
参数列表：参数名 参数类型
函数体：最后为 return 语句 ( 可省略建议加上)
函数结尾：delimiter 重新设置 [delimiter 结束标记]
2.调用语法
SELECT 函数名(参数列表)
*/函数案例
无参有返回值
USE tempbook;
# 无参有返回
# 返回书籍总数
CREATE FUNCTION myf1() RETURNS INT
BEGIN
DECLARE c INT DEFAULT 0; # 定义变量
SELECT COUNT(*) INTO c FROM book; # 赋值
RETURN c;
END;
SELECT myf1();有参有返回
USE tempbook;
# 有参有返回
# 返回指定书籍id的价格
CREATE FUNCTION myf2(id INT) RETURNS DOUBLE
BEGIN
SET @price = 0; # 定义用户变量 任何位置都可以
SELECT b.price INTO @price FROM book b where b.id=id; # 赋值
RETURN @price;
END;
SELECT myf2(7);
SELECT @price; # 用户变量不受begin end约束函数删除、查看、修改
USE tempbook;
# 查看函数
SHOW CREATE FUNCTION myf2;
# 删除函数
DROP FUNCTION myf2;流程控制结构
分支结构概念
/*
1.IF函数
语法：if(expr1,expr2,expr3)
执行顺序 expr1成立则返回epxr2否则返回expr3
2.case结构 [做为表达式 begin end内/外均可，做为独立语句只能位于begin end中]
实现等值判断
语法：case 表达式|变量|字段
when 判断值 then 返回值/语句;
when 判断值 then 返回值/语句;
...
else 返回值/语句;
end case;
实现区间判断
语法：case
when 条件1 then 返回值/语句;
when 条件2 then 返回值/语句;
...
else 返回值/语句;
end case;
3.if结构(区别于if函数,仅用于 bengin end中)
语法：if 条件1 then 语句;
elseif 条件1 then 语句;
elseif 条件2 then 语句;
....
else 语句;
end if;
*/分支结构案例
if函数
# if函数
SELECT IF(1!=2,"YES","NO") AS res;case做为独立语句
# case 做为独立语句
USE tempbook;
CREATE PROCEDURE myp6(IN price DOUBLE)
BEGIN
CASE
WHEN price<=33 THEN SELECT "A";
WHEN price>33 && price<=66 THEN SELECT "B";
ELSE SELECT "C";
END CASE;
END;
# 调用
CALL myp6(22);
CALL myp6(55);
CALL myp6(88);if结构
# if结构
CREATE FUNCTION myf6(price INT) RETURNS CHAR
BEGIN
IF price<=33 THEN RETURN 'A';
ELSEIF price>33 && price<=66 THEN RETURN 'B';
ELSE RETURN 'C';
END IF;
END;
# 调用
SELECT myf6(22);
SELECT myf6(55);
SELECT myf6(88);循环结构概念
# 循环结构
/*
分类：
while：
语法：[名字标签,搭配循环控制] while 循环条件 do
循环体
end while [名字标签,搭配循环控制];
loop:(可模拟死循环)
语法：[名字标签,搭配循环控制] loop
循环体
end loop [名字标签,搭配循环控制];
repeat:(类似do-while)
语法：[名字标签,搭配循环控制] repeat
循环体
until 结束循环条件
end repeat [名字标签,搭配循环控制];
循环控制：
iterate 类似continue 继续，结束本次循环，继续下一次
leave 类似break 跳出，结束当前所在循环
*/循环结构案例
# while使用
USE tempbook;
# 根据设置次数 批量插入
CREATE PROCEDURE my_while1(IN ins_count INT)
BEGIN
DECLARE i INT DEFAULT 0;#定义循环变量
WHILE i<=ins_count DO
INSERT INTO author(id,aName) VALUES(i,CONCAT('ahthor_',i));
SET i = i+1;
END WHILE;
END;
CALL my_while1(10);
# 带 leave 跳出控制的 while使用
CREATE PROCEDURE my_while2(IN ins_count INT)
BEGIN
DECLARE i INT DEFAULT 0;#定义循环变量
a:WHILE i<=ins_count DO
IF i<5 THEN INSERT INTO author(id,aName) VALUES(i,CONCAT('ahthor_',i));
ELSE LEAVE a;
END IF;
SET i = i+1;
END WHILE a;
END;
CALL my_while2(10);
1415
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
