mysql 基础

该笔记 是观看【尚硅谷MySQL数据库】教学视频 所记录的笔记 （2021-4-30 毕）

常用命令

DQL

• DQL : data query language

基础查询

• 语法：select 查询列表 from 表名;

• 特点：

  • 查询列表可以是：表中的字段、常量值、表达式、函数
  • 查询的结果可以是一个虚拟表格;

• 实例

  # 1.查询表中多个字段
  SELECT last_name,salary,email FROM employees;
  
  # 2.查询表中的所有字段
  SELECT * FROM employees;
  
  # 3.查询函数 + 别名
  SELECT VERSION() as v;
  
  # 4.查询表达式 
  SELECT 100%98;
  
  # 5.去重
  SELECT DISTINCT salary FROM employees;
  
  # 6.字段拼接
  SELECT `first_name` `last_name`  AS `name` FROM `employees`;  # error
  SELECT CONCAT(`first_name`,`last_name`)  AS `name` FROM `employees`; # true
  

条件查询

• 按条件表达式筛选

  • 条件运算符:> < = != <> >= <= <=>安全等于

• 按逻辑表达式筛选

  • 逻辑运算符:&& || | and or not

  • && <===> and

  • || <===> or

  • ! <===> not

• 模糊查询
  like:一般搭配通配符使用，可以判断字符型或数值型
  通配符：%任意多个字符，_任意单个字符
  like、between and、in、is null

# 1.查询工资>12000的员工信息 
SELECT * FROM employees WHERE salary>12000;

# 2. 查询工资在10000到20000之间的员工名 # &&
SELECT last_name FROM employees WHERE salary>=10000 AND salary<=20000;

# 3. 查询员工名中包含字符a的员工信息  # like + %
SELECT * FROM employees WHERE last_name LIKE '%a%';

# 4:查询员工名中第三个字符为b，第五个字符为a的员工名和工资 # like + _ + %
SELECT last_name,salary FROM employees WHERE last_name LIKE '__b_a%';

# 5. 查询员工编号在100到120之间的员工信息 # BETWEEN + AND  <==[100,120]
SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 120;

# 6. 查询员工的工种编号是IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号  # in 
SELECT last_name,job_id FROM employees WHERE job_id IN('IT_PROG','AD_PRES','AD_VP');

# 7. 查询没有奖金的员工名
SELECT last_name FROM employees WHERE commission_pct IS NULL;  # true
SELECT last_name FROM employees WHERE commission_pct == NULL;  # false

排序查询

引入：select * from employees;

语法：select 查询列表 from 表 【where 筛选条件】 order by

特点：

• asc：代表的是升序（默认），desc代表降序

• order by子句：支持单个字段、多个字段、表达式、函数、别名

• order by子句：一般放在查询语句的最后面,limit子句除外

# 1. 查询员工信息,要求工资从高到低排序
SELECT * FROM employees ORDER BY salary DESC;

# 2. 按姓名的长度显示员工的姓名和工资【按别名排序】
SELECT LENGTH(last_name) len,last_name,salary
FROM employees
ORDER BY len DESC;

# 3. 查询员工共信息,要求按工资排序，再按员工编号排序【按多个字段排序】
SELECT * FROM employees
ORDER BY salary ASC,employee_id DESC;

分组查询

• 根据某一字段（部门号，班级…）进行分组
• 语句：group by
• where 子句 在 group by 之前 【如果要对分组的数据进行筛选-having】

# 1. 查询邮箱中包含a字符的，每个部门的平均工资
SELECT AVG(`salary`) , `department_id`
FROM  `employees`
WHERE `email` LIKE '%a%' AND `department_id` IS NOT NULL
GROUP BY `department_id`

# 2. 查询 部门中员工个数大于10人的部门  --- having （分组后的筛选）
SELECT COUNT(*), `department_id`
FROM `employees`
GROUP BY `department_id`
HAVING COUNT(*) > 10;

# 3. 每个部门每个工种的员工的平均工资  -- 多字段分组（与顺序无关）
SELECT AVG(salary) ,job_id, department_id
FROM `employees`
GROUP BY department_id , job_id
# 或
SELECT AVG(salary) ,job_id, department_id
FROM `employees`
GROUP BY job_id,department_id

连接查询

• 用于处理：查询的字段（数据）来源与多个表的情况
• 分类
  • 按年份分类
    • sql 192 标准 【只支持内连接】
    • sql 199 标签 【支持：内连接 + 外连接（左外+右外） + 交叉连接】 - 【推荐使用】
  • 按功能分类
    • 内连接
      • 等值连接 - 交集
      • 非等值连接
      • 自连接
    • 外连接
      • 左外连接
      • 右外连接
      • 全外连接
    • 交叉连接

内连接

等值连接

• 多表等值连接的结果为多表的交集部分
• n表连接，至少需要n-1个连接条件
• 多表的顺序没有要求

# 1. 查询员工名和对应的部门名 (内连接 - 等值连接)
SELECT last_name,department_name 
FROM employees,departments
WHERE employees.`department_id`=departments.`department_id`;

# 2. #查询员工名、工种号、工种名 (内连接 - 等值连接  + 表别名)
SELECT e.last_name,e.job_id,j.job_title
FROM employees  e,jobs j
WHERE e.`job_id`=j.`job_id`;

#3.查询城市名中第二个字符为o的部门名和城市名 (内连接 - 等值连接  + 表别名 + 条件【and连接】)
SELECT department_name,city
FROM departments d,locations l
WHERE d.`location_id` = l.`location_id`
AND city LIKE '_o%';


#3. 查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资  （内连接 - 等值连接 + ...）
SELECT department_name,d.`manager_id`,MIN(salary)
FROM departments d,employees e
WHERE d.`department_id`=e.`department_id`
AND commission_pct IS NOT NULL
GROUP BY department_name,d.`manager_id`;

非等值连接

# 1. 查询员工各工资级别的个数 和 各级别的平均工资
SELECT grade_level,ROUND(AVG(salary),2),COUNT(*)
FROM employees e,job_grades g
WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`
GROUP BY grade_level
ORDER BY grade_level;

自连接

• 表与自身连接

# 1. 查询 员工名和上级的名称
SELECT e.employee_id,e.last_name,m.employee_id manager_id,m.last_name
FROM employees e,employees m
WHERE e.`manager_id`=m.`employee_id`;

sql99标准-写法

# 1. 内连接  （inner 可以省略）
	select 查询列表
    from 表1 别名
    inner join 表2 别名
    on 连接条件;

# 2. 查询员工名、部门名 （等值连接）
	SELECT last_name,department_name 
    FROM departments d
    JOIN  employees e
    ON e.`department_id` = d.`department_id`;
    
# 3.  查询部门数大于3的城市名和部门名，各个数
    SELECT city ,COUNT(1) num
    FROM `departments` d
    INNER JOIN `locations` l
    ON d.`location_id`=l.`location_id`
    GROUP BY city
    HAVING COUNT(*)>3;
    
# 4. 查询员工名，部门名，工种名 （多表连接-3表）
    SELECT last_name , department_name,job_title
    FROM `employees` e
    INNER JOIN `departments` d  ON e.`department_id` = d.`department_id`
    INNER JOIN `jobs` j ON j.`job_id` = e.`job_id`
    ORDER BY department_name DESC

外连接

• sql99 标准支持 (支持：左/右外, 全外连接 - 不支持)
• 应用场景： 一个表中 【有数据】，而另一个表中【没有记录】
  • 主表中有数据，而从表中没有数据----->显示（主表的数据都会显示，没有的显示null）
  • 最后显示的数据为：主表所有数据(（没有匹配的为null）+ 主表并从表
• 分类
  • 左外连接 - 左边的为主表 （left join）
  • 右外连接 - 右表的为主表 (right join)

#1.  查询没有员工的部门  (左外)
    SELECT d.`department_name` 
    FROM `departments` d
    LEFT OUTER JOIN `employees`  e
    ON e.`department_id`=d.`department_id`
    WHERE e.`last_name` IS NULL
    GROUP BY `department_name`
    
 #2.  查询没有员工的部门  (右外)
    SELECT d.`department_name` 
    FROM `employees`  e
    RIGHT OUTER JOIN  `departments` d
    ON e.`department_id`=d.`department_id`
    WHERE e.`last_name` IS NULL
    GROUP BY `department_name`    

交叉连接

• cross join - 笛卡尔乘积 （sql99 语法方式）

SELECT d.`department_name` 
FROM `employees`  e
CROSS  JOIN  `departments` d

子查询

• 又称之为：内查询

• define：出现在其他语句中的 select 语句

• 分类

  • 特点： 小括号包起来（select xxxx）
  • 位置
    • select 之后： 仅支持 标量子查询
    • from 之后 : 支持 表子查询
    • where/having 之后 :
      • 支持 标量子查询【单行子查询 (*)】 + 列子查询【多行子查询（*) 】+ 行子查询 【*】
    • exists 之后 : 支持 表子查询
  • 功能
    • 标量子查询 ，或称之为单行子查询（结果集只有 1行1列）
    • 列子查询 ， 或称之为 多行子查询（结果集只有 1列多行）
    • 行子查询 (结果集 一行多列)
    • 表子查询 （结果集为【多行多列】）

where 之后

• 支持
  • 标量子查询 【*】
  • 列子查询‘ 【*】 （常搭配多行操作符-in / any/ all / some）
  • 行子查询

# 1. 标量子查询
	
 # 查询谁的工资比 Abel 高
SELECT * FROM `employees` 
WHERE `salary` > (
	SELECT salary 
	FROM  `employees` 
	WHERE `last_name`='Abel'
)

# 查询工资最少的员工的last_name, job_id, salary
SELECT last_name, job_id, salary
FROM `employees`
WHERE salary =(
	SELECT  MIN(salary) 
	FROM  `employees`
)


# 2. 列子查询  
	// any| some ===>  和 子查询结果集中的某一个值比较
	// all 		 ===>  和 子查询结果集的所有比较
	
# 查询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)
)

# 查询其他g工种中比job_id为‘IT_PROG’ 部门任意工资低的员工的姓名，job_id,salary

SELECT last_name,job_id,salary
FROM `employees`
WHERE salary < ANY(
	SELECT salary 
	FROM `employees`
	WHERE `job_id`='IT_PROG'
) AND job_id <> 'IT_PROG'
 # 或则
SELECT last_name,job_id,salary
FROM `employees`
WHERE salary < (
	SELECT MAX(salary) 
	FROM `employees`
	WHERE `job_id`='IT_PROG'
) AND job_id <> 'IT_PROG'

 
 # 3. 行子查询  （不常用）

select 之后

• 子查询： 为 标量子查询 （一行一列）

# 查询每个部门的员工个数
SELECT d.* ,(
	SELECT COUNT(*) 
	FROM `employees`
	WHERE `department_id`=d.`department_id`
) num
FROM `departments` d

from 之后

• 支持： 标量子查询，行子查询，列子查询

# 1. 查询每个部门的平均工资的工资等级
SELECT `grade_level`, ROUND(avgsalary,2) ,`department_id`
FROM `job_grades` j
INNER JOIN (
	SELECT AVG(salary) avgsalary, `department_id`
	FROM  `employees`
	GROUP BY `department_id`
) e
ON  e.avgsalary BETWEEN j.`lowest_sal` AND `highest_sal`

exists 之后

• 称之为：相关子查询 （用的不多）
• exists ： 判断后面的子查询有没有值 （有–>1 ,没有 0）
• 执行流程：先执行主查询，在执行子查询（再根据子查询结果筛选）

# 查询有员工的部门名
SELECT `department_name`
FROM `departments` d
WHERE  EXISTS (
    SELECT   * 
    FROM  `employees` e
    WHERE e.`department_id`= d.`department_id`
)
# 或 
SELECT `department_name`
FROM `departments` d
WHERE  d.department_id IN (
	SELECT DISTINCT department_id
	FROM `employees` e
) 

分页查询

• 关键字： limit （起始索引从0 开始）

• 特点：limit 语句始终放在最后

• 格式 （size : 要显示的条数 ， offset 默认为0）

  select 查询列表
  from 表
  [join xxxx on xxxx
  where 筛选条件
  group by 分组字段
  having  分组后的筛选条件
  order by  排序字段]
  limite offset,size;
  

联合查询

• 关键字：union
• 作用：将 多个 查询结果合并 为 一个结果
• 应用场景：多个表没有关联的字段，且 查询的信息（相同的字段数）是 一致的
• 说明：
  • 最后的查询结果的字段为第一个查询的字段列表

#1. 查询部门编号大于90 或 邮箱中有a的员工信息
SELECT * FROM `employees` WHERE `employee_id` > 90 
UNION 
SELECT * FROM `employees` WHERE `email` LIKE '%a%';

# 或

SELECT * FROM `employees` WHERE `email` LIKE '%a%' OR employee_id>90;


#2. 查询的信息不一致  （error - 报错）
SELECT `first_name`,`email` FROM `employees` WHERE `employee_id` > 90 
UNION 
SELECT `first_name` FROM `employees` WHERE `email` LIKE '%a%';


#3. 查询的信息一致  , 语义不对应（warn） - 不会报错（无意义）
SELECT `first_name`,`salary` FROM `employees` WHERE `employee_id` > 90 
UNION 
SELECT `first_name` , `last_name` FROM `employees` WHERE `email` LIKE '%a%';

DML

• DML: data manage language

插入

• 关键字： insert

• 说明：

  • 要插入的列名必须与对应的值相对应
  • 可以为空的字段，可以插入null
  • 可以为空的字段, 可以不用在列名（省略）
  • 所有列===> 省略 (列名,...)

• 语法

  # 经典写法 (方式1， 支持批量插入/子查询【推荐】)
  insert into 表名(列名,...) values(值1,...)
  
  # set方式 (方式2 ， 支不持批量插入/子查询【不推荐】 )
  insert into 表名 set 列名=值,列名=值,....
  
  # 批量插入
  insert into 表名(列名,...) values(值1,...),(值1,...),(值1,...),
  

# 批量插入
INSERT INTO `departments`(`department_name`,`manager_id`,`location_id`)
VALUES('Coe',NULL,1700),('Wec',NULL,1700),('Xne',103,1500)

修改

• 关键字：update

• 语法

  # 修改单表记录
  update 表名
  set 列=新值，列=新值，...
  [where 筛选条件]
  
  # 修改多表的记录  (sql92 方式)
  update 表1 别名，表2 别名
  set 列=值，...
  where 连接条件
  and 筛选条件;
  
  
  # 修改多表的记录  (sql99 方式)
  update 表1 别名
  inner | left | right join 表2 别名
  on 连接条件
  set 列=值，...
  where 筛选条件;
  

删除

• 关键字：delete

• 语法

  • delete 方式
  • truncate 方式
    • 会重置自增列的值， 不能回滚， 没有返回值（0行受影响）

  #1. 单表的删除
  delete from 表名 where 筛选条件
  
  
  #2. 多表的删除 【sql92 方式】
  delete 表1的别名 | 表2 别名 | 表1别名，表2别名
  from 表1 别名， 表2 别名
  where 连接条件
  and 筛选条件
  
  
  #3. 多表的删除 【sql99 方式】
  delete 表1的别名 | 表2 别名 | 表1别名，表2别名
  from 表1 别名
  inner | left | right join  表2 别名
  on 连接条件
  where 筛选条件
  
  
  #4. truncate 语句 （清空，不能添加 where）
  truncate table 表1  #（清空表1）   等价与：delete from 表1
  

DDL

• DDL : data define language (数据定义语言)
• 作用：库/表的管理

库的管理

# 1. 创建数据库
create databases [if not exists] 库名

# 2. 更改库的字符集
alter databases 库名 character set  gbk | utf8 |...

# 3. 删除库
drop databases [if exists] 库名

# 4. 库重命名（不推荐使用）：rename databases 原库名 to newName 

表的管理

表创建

# 1. 创建表
create table 表名(
	列名 列的类型[(长度) 约束],
	列名 列的类型[(长度) 约束],
	....
	列名 列的类型[(长度) 约束]
)
# 实例 1
CREATE TABLE `job_grades` (
  `grade_level` varchar(3) DEFAULT NULL,
  `lowest_sal` int DEFAULT NULL,
  `highest_sal` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gb2312

# 实例 2
CREATE TABLE IF NOT EXISTS girl(
	`girl_id`  INT PRIMARY KEY AUTO_INCREMENT,
	`age` INT ,
	`email` VARCHAR(25) DEFAULT NULL
);

表修改

# 1. 表的列名
alter table 表名 change [column] 原列名 新列名 类型[ 约束]
ALTER TABLE girl CHANGE COLUMN age girl_age int

# 2 添加新列
alter table 表名 add [column] 列名 类型[ 约束]

# 3 删除列
alter table 表名 drop [column] 列名

# 4. 修改表名
alter table 表名 rename to 新的表名

表删除/复制

# 删除表
drop table [if exists 表名]

# 复制表（仅仅复制结构）
create table 新的表名 like 目标表


# 复制表（结构 + 数据）
create table 新的表名 
select * from 目标表

# 复制表（表的部分结构 + 数据）
create table 新的表名 
select 字段1，字段2,...
from 目标表
[where 筛选条件]


# 复制表（表的部分结构）
create table 新的表名 
select 字段1，字段2,...
from 目标表
where 1=2 # false 没数据

数据类型

• 分类
  • 数值型
    • 整数 / 小数（定点数| 浮点数）
  • 字符型
    • char / varchar / text / blob
  • 日期型

数值型

整型	字节	范围
tinyint	1	[-128,127] / [0，255]
smallint	2	[-32768,32767] / [0,65535]
mediunint	3
int	4
bigint	8

• 特点
  • 默认为有符号类型 （无符号：unsigned）
  • 插入的值超过了 该类型的 范围时：out of range 异常
  • 长度的作用：
    • 需要搭配 zerofill 使用（0填充 - 无符号）
    • 例如 int(7) ， tiny(11)
    • 含义：长度代表显示的最大宽度，如果位数不够则zerofill

小数

浮点型类型	字节	范围
float(M,D)	4
double(M,D)	8

定点型	字节	范围
dec(M,D)	M+2
decimal(M,D)	M+2

• 说明
  • 定点型用于对精确的高的需求
  • M / D
    • M ： 总长度（整数部分 + 小数部分） 【超过M ，不会报错-会插入临界值max】
    • D : 小数点的位数
    • M/D 可以省略
      • 浮点型：
        • M/D 省略后，会根据插入的值来决定精度（没有精度，动态的）
      • 定点型
        • M/D 省略后 ， 默认M=10, D=0

字符型

• 较短的文本：char , varchar
• 较长的文本： text ， blob(较大的二进制)

字符型	最多的字符数	描述
char(M)	M	M为0~255之间 ， 默认M=1
varchar(M)	M	M为0~65535之间

• char/ varchar
  • char 为固定长度， 消耗空间
  • varchar 可变长度 节约空间（会消耗部分性能）

枚举型

• 关键字： enum

• 说明

  • 不区分大小写
  • 列表成员为 1~255 ： 则需要1个字节存储
  • 列表成员为 255~65535： 需要2个字节存储

  create table tab_enum(
  	c1 enum('a','b','c')
  )
  
  insert into tab_enum values('a')  # success  --> a
  insert into tab_enum values('B')  # success  --> b
  insert into tab_enum values('e')  # error
  

set型

• 关键字：set

• 和枚举类型类似，但不同的是，set类型可以选取多个成员（enum只能选取一个）

  • 成员数：1~8 1字节
  • 成员数：9~16 2 字节
  • 成员数：17~24 3字节

  CREATE TABLE tab_set(
  	c1 SET('a','b','c','d')
  )
  
  INSERT INTO tab_set VALUES('a');     # success  --> a
  INSERT INTO tab_set VALUES('B');     # success  --> b
  INSERT INTO tab_set VALUES('A,b');   # success  --> a,b
  INSERT INTO tab_set VALUES('A,a');   # success  --> a
  

日期型

日期型	字节	最小值	最大值
date	4	1000-01-01	9999-12-31
datetime	8	1000-01-01 00:00:00	9999-12-31 xxx
timestamp	4	19700101080001	2038
time	3	-838:59:59	838:59:59
year	1	1901	2155

• timestamp/ datetime
  • timestamp 支持时间范围小 ， 占用空间小
  • timestamp 和实际时区相关 【推荐使用】

常见约束

• 作用： 保证数据的一致性（可靠性，准确性）

• 6大约束

  约束	描述信息	举例
  not null	非空约束	用户名，学号
  default	默认约束	性别
  primary key	主键约束（非空+唯一）	编号
  unique key	唯一约束（可以为空）	座位号
  check	检测约束 MYSQL-不支持
  foreign key	外键约束 （用于约束两个表的关系-）

  # 行级约束
  CREATE TABLE major(
  	id INT PRIMARY KEY,
  	majorname VARCHAR(20)
  	
  )
  # 表级约束  【CONSTRAINT 约束名 xx】
  CREATE TABLE stuinfo(
  	id INT,
  	studname VARCHAR(20),
  	gender CHAR(1),
  	seat INT,
  	age INT,
  	majorid INT,
  	CONSTRAINT pk PRIMARY KEY(id),
  	CONSTRAINT uq UNIQUE(seat),
  	CONSTRAINT ck CHECK(gender='男' OR gender='女'),
  	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES  major(id)
  )
  

  修改约束

  • 行级约束：alter table 表名 modify column 字段名 字段类型 新的约束
  • 表级约束：alter table 表名 add [constraint 约束名] 约束类型（字段名） [外键的引用]

TCL

• TCL : transaction controll language

事务处理

• 查看mysql支持的存储引擎：SHOW ENGINES

  • innodb ：支持事务
  • myisam / memory ：不支持事务

• 事务的属性 （ACID）

  • Atomicity : 原子性 （事务操作要么都发生-执行，要么都不发生-不执行）
  • Consitency: 一致性 （数据前后的状态一致）
  • Isolation : 隔离性 （事务之间相互隔离 - 跟隔离级别有关）
  • Durability : 持久性 （事务一旦提交，数据库的改变就是永久性的）

• 事务的创建

  • 隐式事务：事务没有明显的开启和结束的标记

    • 比如：update , delete , insert
    • 查看状态（默认开启：ON）：SHOW VARIABLES LIKE '%autoc%'

  • 显示事务

    • 需要禁用事务自动提交（当前会话有效）： set autocommit=0

    • 使用

      set autocommit=0; 
      start transaction; # 可选
      insert/update/delete xxxx1
      insert/update/delete xxxx2
      ...
      commit #1 提交事务
      rollback #2 回滚事务-取消上述操作  （1 or 2）
      

隔离级别

• 作用： 并发问题的处理策略
• 并发问题
  • 脏读：读取了另一个事务T的修改后的数据但事务T没有提交事务.（出现了update操作）
  • 不可重复度：在一个事务中 多次读取同一数据的结果不一致
  • 幻读：在一个事务中，多次读取同一表的数据, 该表的前后数据行数不同.（出现了delete/insert）
• 隔离级别
  • Repeatable Read (可重复读) ： 事务可以多次从一个字段中读取相同的值。在这个事务期间，禁止其他事务对这个字段进行跟新操作（可以避免 - 脏读 + 不可重复读，当依然会出现幻读）
  • Read Uncommitted (读未提交数据)：允许事务读取未被其他事务提交的更改操作。（都不能避免）
  • SeRializable (串行化/序列化) ： 事务持续期间，禁止其他事务对该表执行插入/更新/删除操作 （可以避免所有的并发问题，但效率低下）
  • Read commited (读已提交数据) ： 只允许事务读取已经被其他事务提交的操作。（可以避免脏读，但无法i避免 可重复度+ 幻读）
• Orcale 支持的事务隔离级别：Read commited（default），Serializable
• Mysql 支持的事务隔离级别 : Repeatable Read（default）

# 设置 当前mySql连接的隔离级别 (read committed)
set transaction isolation level read committed

# 设置 当前mySql全局的连接的隔离级别 (read committed)
set global transaction isolation level read committed

其他

视图

• 视图：虚拟表，操作和普通表一样
• 说明：
  • 视图是动态生成的，只保存的sql的逻辑，没有保存数据。
  • 应用场景：
    • 需要频繁的使用的查询结果
    • 复杂的sql语句查询
  • 作用
    • 保护数据，提供安全性
    • 重用sql

创建视图

# 语法
create view 视图名
as
查询语句;

# 查询姓名中包含a字符的员工名，部门名，工种信息
create view myv1
as 
SELECT e.`last_name`,d.`department_name`,j.`job_title`
FROM `employees` e
JOIN `departments` d ON  d.`department_id`=e.`department_id`
JOIN `jobs` j ON j.`job_id`= e.`job_id`;


SELECT * 
FROM myv1
WHERE last_name LIKE '%a%'

视图的修改

# 方式一
create or replace view 
as
查询语句

# 方式二
alter view 视图名 
as 
查询语句

删除视图

#(批量)删除视图
drop view 视图名1，视图2...

视图数据更新

# 视图数据的 增删改 会修改原表的数据 （不安全-insecurity）
# 一般不会给视图添加 修改权限

变量

系统变量

• 分类
  • 全局变量
  • 会话变量

#  说明
1. 系统变量 由 系统提供
2. 使用
	① 查看: show [global/session(默认)] variables [like '%xxx%'];
	② 查看指定的某个系统变量的值： select @@[global/session(默认)]. 系统变量名
	③ 赋值：set [global/session（默认）] 变量名=值  /  set [@@global/@@session（默认）].变量名=值

自定义变量

# 用户自定义变量
1. 用户变量
	作用域：当前会话
	使用范围：如何地方 （~弱类型）
	使用：
        ① 声明/更新 用户变量：set @用户变量名=值 / set @用户变量名:=值   /  select @用户变量名:=值
        ② 更新方式2：select 字段 into @用户变量名 from 表
        ③ 查看： select @用户变量名;
    实例:
    	SET @user_self = 'xming'        # 创建并赋值
        SELECT @user_self:=18101020321  # 更新
        SELECT @user_self;              # 查看   
    
2. 局部变量
	作用域：仅仅在定义他的begin and 中有效  （~强类型）
	使用范围：begin end 中(第一句话中)
    使用：
		① 声明/赋初值：declare 变量名 类型 [default 值] ;
    	② 赋值：set 变量名=值 / set 变量名:=值 / select @变量名:=值  / select 字段 into 变量名 from 表 (注意类型统一)
    	③ 查看：select 变量名;    	

存储过程

• 含义：一组预先编译好的SQL语句的集合 （类似于JAVA中的函数）

• 作用：

  • 提高性能 （减少的SQL的编译次数）
  • 简化操作

• 使用

# 1. 创建语法
create procedure 存储过程名(参数列表)
begin
	SQL 语句
end

#---------说明----------
1. 参数列表:
	① 参数列表包含3部分 （参数模式，参数名，参数类型）  /  举例：in stuname varchar(10)
	② 参数模式：in | out | inout     (in：该参数作为输入，out：该参数作为输出-返回值，inout：该参数作为输出/入)
	③ 当 【存储过程 中 SQL 语句】只有一句 可以忽略 begin-end
	④ 存储过程 中 每条 SQL 语句的结束必须加分号
	⑤ 存储过程的结尾 可以使用 delimiter 重新设置   / 举例：  delimiter $

# 2. 调用
call 存储过程名(实参列表);


# 3. 删除 （一次只能删除一个存储过程）
	drop procedure 存储过程名

# 4. 查看 存储过程信息
	show create  procedure 存储过程名

# 5. 查看所有的存储过程
	 SHOW PROCEDURE STATUS;
	 
# 6. （使用）举例
	
	1. 插入到girl 表中3条记录 （空参列表）
		①存储过程
            delimiter $
            create procedure intogirl()
            begin
                insert into girl 
                values(null,14,'12312@qq.com'),(null,34,'64322@qq.com'),(null,24,'8212@qq.com');
            end $
        ②调用
            call intogirl()$
     2. 根据员工id，查询其所属部门的信息 （in 参数模式 的参数列表）
    	 ①存储过程
     	    delimiter $
            create procedure departmentInfo(in employee_id int)
            begin
               	SELECT d.* 
                FROM `departments` d
                RIGHT JOIN `employees` e
                ON d.`department_id`=e.`department_id`
                WHERE e.`employee_id`= employee_id;
		    end $
		    ②调用
		    	call departmentInfo(100)$  或 call departmentinfo(100)$   
	3. 用户登录存储过程 （in + 局部变量）
		①存储过程
			DELIMITER $
            CREATE PROCEDURE userlogin(IN username VARCHAR(20),IN pwd VARCHAR(40))
            BEGIN
                DECLARE result INT DEFAULT 0;  # 局部变量声明 + 默认值 （必须在第一排）
                SELECT COUNT(*) INTO result    # 赋值
                FROM sys_user u
                WHERE u.username=username 
                AND u.password=pwd;
                SELECT IF(result >0,'success','fail'); # 使用  
            END $
            
		②调用
			 call userlogin('root','123')$
             
     4. 根据员工id，返回其所属部门的部门名 （in  + out 参数模式 的参数列表）
     	② 存储过程
            DELIMITER $
            CREATE PROCEDURE departmentName(IN employee_id INT, OUT res VARCHAR(20))
            BEGIN
                SELECT d.`department_name` INTO  res
                FROM `departments` d
                RIGHT JOIN `employees` e
                ON d.`department_id`=e.`department_id`
                WHERE e.`employee_id`= employee_id;
            END $ 
        ② 调用
        	 方式一：
        	 	call departmentName(100,@demo)$ 
        	 	查看结果： select @demo$
        	 方式二
        	 	set @demores=null$
        	 	call departmentName(100,@demores)$
       			查看结果： select @demores$
       			
         
     5. 根据员工id，返回其所属部门的部门名 和部门编号id （in  + out 参数模式 的参数列表） 
     	 ① 存储过程
     	 	DELIMITER $
            CREATE PROCEDURE departmentNameandid(
            	IN employee_id INT, 
            	OUT depname VARCHAR(20), 
            	OUT depid INT 
            )
            BEGIN
                SELECT d.`department_name`,d.`department_id` INTO  depname,depid
                FROM `departments` d
                RIGHT JOIN `employees` e
                ON d.`department_id`=e.`department_id`
                WHERE e.`employee_id`= employee_id;
            END $ 
            
       6， 传入两个值（a，b），返回a，b的两倍  (inout 参数模式)
       		① 存储过程
       			# 说明：INOUT参数模式的参数，调用时的实参为用户变量-对应类型（不能是值）
                DELIMITER $
                CREATE PROCEDURE doubletwe(INOUT a INT,INOUT b INT)
                BEGIN
                    SET a = a*2;
                    SET b = b*2;
                END $
            ② 调用
            	1> 定义用户变量： set @aa=1，@bb=2 $
            	2> 调用 ： call doubletwe(@aa,@bb) $
            ③ 查看
            	select @aa,@bb$

函数

• 函数于存储过程的区别

  • 存储过程可以有一个或多个或没有返回值 （适合做 批量的增删改操作）

  • 函数有且仅有一个返回值

函数的创建

create function 函数名(参数列表) returns 返回值类型
begin
	函数体
end

# 参数列表说明（包含两部分<参数名，参数类型>）
# 函数体必须有return 语句
# 使用 delimiter语句设置结束标记

函数的调用

• select 函数名（参数列表）
• 注意：mysql的设置默认是不允许创建函数 ==修改=> SET GLOBAL log_bin_trust_function_creators = 1;

查看函数信息

• show create function 函数名

删除函数

• drop function 函数名

使用

# 1. 返回公司的员工个数 
	
    DELIMITER $
    CREATE FUNCTION countemp() RETURNS INT
    BEGIN 
        DECLARE num INT DEFAULT 0;
        SELECT COUNT(*) INTO num 
        FROM `employees`;
        RETURN num;
    END $
    SELECT countmp() $ # 调用函数
    
# 2. 创建一个求和函数
	DELIMITER $
    CREATE FUNCTION addmy( a FLOAT, b FLOAT) RETURNS FLOAT
    BEGIN 
        DECLARE num FLOAT DEFAULT 0;
        SET num = a+b;
        RETURN num;
        
    END $
    SELECT addmy(1,2) $ # 调用函数

流程控制结构

• 分类： 顺序结构 | 分支结构 | 循环结构

分支结构

• if 函数

  • 语法

  if(表达式1,表达式2,表达式3)
  
  /*  说明：
  		如果表达式1 为 true ==返回==> 表达式2  / 否则返回表达式3
  		类似于三元运算符 表达式1?表达式2:表达式3
  */
  

• case 结构

  • 语法

  # 用法一 （用于等值判断）：
  case 变量|表达式|字段
      when 要判断的值 then 要返回的值1
      when 要判断的值 then 要返回的值2
      [else  要返回的值n]
  end case;
  
  # 用法二（用于区间判断）：
  case 
      when 要判断的条件1 then 要返回的值1
      when 要判断的条件2 then 要返回的值2
      [else  要返回的值n]
  end case;
  
  /* 说明：
  	case 既可以作为 表达式，  可以放在	任何地方 。
  	又可以作为独立语句使用， 此时只能在begin-end中
  */
  
  
  # 举例 ： 根据传入的分数，显示对应的等级。
  DELIMITER $
  CREATE PROCEDURE grade_score(IN score FLOAT)
  BEGIN	
  	CASE 
  		WHEN  score >=90 AND score <100 THEN  SELECT 'A';
  		WHEN score >=80 THEN SELECT 'B';
  		WHEN score >=60 THEN SELECT 'C';
  		ELSE SELECT 'D';
  	END CASE;
  END $
  

• if 结构

  • 语法

  if 条件1 then 语句1;
  elseif 条件2 then 语句2;
  ...
  [else 语句n]
  end if;
  
  # 案例：根据传入的分数，返回对应的等级。
  DELIMITER $
  CREATE FUNCTION grade_score(score FLOAT) RETURNS CHAR
  BEGIN
  	IF score >=90 AND score <=100 THEN RETURN 'A';
  	ELSEIF score >=80 THEN RETURN 'B';
  	ELSEIF score>60 THEN RETURN 'C';
  	ELSE RETURN 'D';
  	END IF;
  END $
   
   SELECT grade_score(72)$
  

循环结构

• 分类 ： while | loop | repeat

• 循环控制： iterate （类似于 continue）, leave （类似于 break）

• 语法

  # while 语法
  [标签:] while 循环条件 do
  	循环体
  end while [标签];
  
  # loop 语法  (死循环-配合leave使用)
  [标签:] loop
  	循环体
  end loop [标签];
  
  # repeat 语法 (类似于 ~  do-while)
  [标签:] repeat
  	循环体
  until 结束循环的条件
  end repeat [标签];
  

• 使用

  # 1. 批量插入
  	DELIMITER $
      CREATE PROCEDURE insert_batch(IN times INT)
      BEGIN
          DECLARE i INT DEFAULT times;
          a:WHILE i>0 DO
              INSERT INTO girl
              VALUES(NULL,times+100,CONCAT(i,'-xxx@qq.com'));
              SET i = i-1;
          END WHILE a;
      END $
  
      CALL insert_batch(10)$
  	SELECT * FROM girl
  
  
  
  

常用函数

• 分类

  • 单行函数

    根据参数（参数类型）细分: 
    
    - 字符函数： length，concat,upper,lower,substr, insert....
    - 数学函数： max，min,round,ceil,floor,truncate，mod,...
    - 日期函数：	now，year,day,date_format,str_to_date...
    - 其他函数：	version，database，user
    - 流程控制函数：if，case....
    

  • 聚合函数

    - 又称为：分组函数，统计函数
    - 聚合函数： sum, avg， max , min， count , ....
    

concat

concat 函数
	功能：拼接字符
	说明：如果参数中存在null，返回值为 NULL.
	使用：concat(str1,str2,...)
	类型：单行函数
	实例：
		1. SELECT CONCAT(`first_name`,"-",`last_name`)  AS `name` FROM `employees`
		2. SELECT CONCAT('1 ', 1 , " ",1) // 1 1 1

concat_ws 函数
	功能：拼接字符
	类型：单行函数
	使用：concat_ws(separator,str1,str2,...)
	说明：separator 为分隔符, 能够自动忽略null， separator为null==>返回null
	实例：
		1. SELECT CONCAT_WS("-",1,2,3) // 1-2-3
		2. SELECT CONCAT_WS("-",NULL,2,3)  // 2-3  （自动忽略null）
		3. SELECT CONCAT_WS(null,1,2,3)  // null
		4. SELECT CONCAT_WS("-",'1 ',' ',' 3')  // 1 - - 3

IFNULL

ifnull 函数
	功能：判断参数是否为空
	类型：单行函数
	使用：ifnull(arg,default) // arg 为带判断的数据，default表示arg为null时返回的值，否则返回arg
	实例：
		1. SELECT IFNULL(6,2) // 6
		1. SELECT IFNULL(null,2) // 2

upper/lower

upper/lower 函数
	功能：将str参数转为大写/小写
	类型：单行函数
	使用：upper(str) / lower(str)
	实例：
		1. SELECT UPPER("我的ee") //我的EE
		2. SELECT LOWER('A%123') //a%123

SUBSTR

substr 函数
	功能：字符串分割
	类型：单行函数
	使用：substr(str,index,[length])
	说明：索引index在MySQL中从1开始的， length 表示截取的【字符】长度（可以省略）
	实例：
		1. SELECT SUBSTR('12345',2) //2345
		2. SELECT SUBSTR('12345',2,3) // 234
		3. SELECT SUBSTR('你我他它她',2,3) //我他它

INSERTxxxx

insert 函数
	功能：插入指定字符串
	类型：单行函数
	使用：inser(str,index,[length])
	说明：索引index在MySQL中从1开始的， length 表示截取的【字符】长度（可以省略）
	实例：
		1. SELECT SUBSTR('12345',2) //2345
		2. SELECT SUBSTR('12345',2,3) // 234
		3. SELECT SUBSTR('你我他它她',2,3) //我他它

INSTR

instr 函数
	功能：返回子串第一次出现的索引位置（找不到返回0）
	类型：单行函数
	使用：instr(str,str2)
	说明：索引index在MySQL中从1开始的
	实例：
		1. SELECT INSTR('你我他它她','他') // 3
		2. SELECT INSTR('12aaa','她') // 0

trim

trim 函数
	功能：去除 字符串两边的空白字符
	类型：单行函数
	使用：trim(str) / trim(char from str)
	说明：char from str +> 去除 字符串（str）中 前后的 指定的字符（char，也可以是字符串）
	实例：
		1. SELECT TRIM(' 1 23 ') //1 23
		2. SELECT TRIM('x' FROM 'xxx1 23 1xx') // 1 23 1
		3. SELECT TRIM('xx' FROM 'xxx1 23 1xxx') // x1 23 1x

LPAD/RPAD

lpad/rpad 函数
	功能：左/右填充指定字符到指定的长度
	类型：单行函数
	使用：lpad(str,len,char) / rpad(str,len,char) 
	说明：字符（char，也可以是字符串）,len +>str的目标长度
	实例：
        1. SELECT LPAD('12',5,'*')  // ***12
        2. SELECT LPAD('12',5,'()') // ()(12

REPLACE

replace 函数
	功能：替换字符串中的资指定子串
	使用：place(str,orstr,newstr)
	实例：
		SELECT REPLACE('abcabc','a','e') //ebcebc (全部)

ROUND

round 函数
	功能：四舍五入 + 保留指定的位数
	使用：round(num), round(num, n)
	说明：num表示一个小数，n表示小数位数
	实例：
		1. SELECT ROUND(1.5) //2
		2. SELECT ROUND(123.567,2) //123.57  （四舍五入）

TRUNCATE

truncate 函数
	功能：截断 （不会进行四舍五入）
	使用：truncate(num, n)
	实例：
		1. SELECT TRUNCATE(123,2);		//123
		2. SELECT TRUNCATE(123.567,2);  //123.56 （直接截断）

NOW

now 函数
	功能：返回当前系统的时间+日期
	使用： now()  // 2021-04-13 15:36:49
	
curdate 函数
	功能：返回当前系统日期（不包含时间）
	使用：curdate() // 2021-04-12
	
curtime 函数
	功能：返回当前系统的时间（不包括日期）
	使用：curtime()  // 15:37:51

year/month/day... 函数
	功能：获取当前系统的年份
	使用：year(xx)
	实例：
		1. SELECT YEAR(NOW())  // 2021
		2. SELECT YEAR('2020-02-20')  // 2020

str_to_date 函数
	功能：将日期格式的字符串转为指定的日期
	使用：str_to_data(str,format)
	说明：str +> 字符串， format +> 日期格式
		日期格式（format）：
			1. 年：{Y：4为年份,y2为年份} 
			2. 月：{m：月份（01，02...）,c：月份（1，2..）}
			3. 日：{d：日（01，02，03）}
			4. 小时：{H：24小时制，h：12小时制}
			5. 分钟：{i：分钟（00，01，02，03.。.59）}
			6. 秒：{s: 秒（00，01，02.。。）}
	实例：
		1. SELECT STR_TO_DATE('2020-02-01 16','%Y-%m-%d %H') // 2020-02-01 16:00:00

date_format 函数
	功能：将日期转为指定日期格式的字符串
	使用：date_format(str,format)
	案例：
		1. SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日') // 2021年04月13日
		
datediff
	功能：获取两个日期相差的天数
	使用：datediff('xxxx-xx-xx','xxxx-xx-xx')   // 前者 > 后者
	案例：
		1. SELECT DATEDIFF(NOW(),'1999-01-03')
	

CASE

if 函数
	功能：流程控制-- if
	使用：if(condition,valtrue,valfalse)
	案例：
		1. SELECT IF(10<5,'大','小'); // 小

case 函数
	功能：流程控制-- switch
	实例：
		1. 使用一 ： switch case 的效果
		 	（1） 
                SELECT 23 AS val,
                CASE 23 
                WHEN 1 THEN 2
                WHEN 2 THEN 3
                WHEN 3 THEN 52
                END AS test;
            （2）
                SELECT salary 原始工资,department_id,
                CASE department_id
                WHEN 30 THEN salary*1.1
                WHEN 40 THEN salary*1.2
                WHEN 50 THEN salary*1.3
                ELSE salary
                END AS 新工资
                FROM employees;
		2. 使用二： 类似于多重else-if
			(1)
				SELECT salary,
                CASE # 这里没有【要判断的变量或表达式】
                WHEN salary>20000 THEN 'A'
                WHEN salary>15000 THEN 'B'
                WHEN salary>10000 THEN 'C'
                ELSE 'D'
                END AS 工资等级
                FROM employees;

聚合函数

• 一般用于统计使用

• sum和avg一般用于处理数值型 （自动忽略 null）

• max、min、count可以处理任何数据类型 （自动忽略 null）

• count函数

  • count(字段)：统计该字段非空值的个数
  • count(*):统计结果集的行数

# 案例1
	SELECT 
		SUM(salary) 和, ROUND(AVG(salary),2) 平均,
		MAX(salary) 最高,MIN(salary) 最低,COUNT(salary) 个数 
	FROM employees;
# 案例2 DISTINCT
	SELECT COUNT(DISTINCT salary) FROM employees;

注意事项

+运算符

mysql中的+号:
	1. 只能作为运算符 (没有字符串拼接的功能)
	2. 字符型 + 数值：试图将字符型数值->数值型，如果转换成功,则继续做加法运算 否则: 将字符型数值转换成0
	3.  null + ? : 只要其中一方为null,则结果肯定为null.
	
select 100+90; 两个操作数都为数值型,做加法运算
select '123+90';  //  '123+90'
select 'john'+90; // 90   <== 0+90
select '12'+90; // 102   <== 12+90
select null+0; // null <==只要其中一方为null,则结果肯定为null.

条件查询

条件查询的执行顺序问题：
	基本语句结构： select xx(字段列表-1)x from xx(表-2)x where xx(条件-3)x
	执行顺序： 2-3-1
	说明：
		2.先找到有指定表
		3.根据条件进行筛选
		1.输出指定的数据

is null

1. =或<>不能用于判断null值
2. is null 或 is not null 可以判断null值

#案例1: 查询没有奖金的员工名
SELECT last_name FROM employees WHERE commission_pct IS NULL;

length 函数

length 函数
	1. 获取字符串的【字节】长度与当前的字符集相关

#案例
SELECT LENGTH("12aaa我的")  // 11 （1 1 1 1 1 3 3）

count 函数

1. count(*) 与 count(xx) 与 count(1) 区别
	count(*) : 查询总行数（查询每行中每个字段都不为空null的行数）
	count(xx): 查询指定字段中不为空的个数
	count(1) : 查询总行数（本质是在表上追加了一个值为1的字段-一列，然后统计值为1的行数）, count(n)等价
	
	效率：
		MYISAM 存储引擎： count(*) > other
		INNODB 存储引擎： count(*) = count(1) > count(xx)

having 与 where

having :
	用于分组后筛选 - 数据源是【分组后的结果集】
	分组函数作为筛选时，一定放在having 子句中

where : 
	用于分组前筛选 - 数据源是【源数据】

性能：
	优先使用分组前筛选（where）

主键与唯一键

约束类型	唯一性	是否允许为空	个数	是否允许组合
主键	T	F	最多一个主键	T（不推荐）
唯一键	T	最多只能有一个null	可以多个	T

外键的特点

• 在从表添加外键约束
• 从表的外键列与主表的关联字段的类型要求一致或兼容。
• 主表的关联字段必须是一个key（主键，唯一键）
• 插入数据时，先插入主表的数据， 再添加从表的数据。
• 删除数据时，先删除从表的数据，在删除主表的数据。

自增(标识列)

• 标识列必须时一个key（主键，唯一键）
• 一个表最多只能有一个标识列
• 标识列的类型必须为 数值型（int float …）
• 标识列的操作
  • 查看标识列的信息 ： SHOW VARIABLES LIKE '%auto_incr%'
  • 设置步长(n)：set auto_increment_increment=n

delete与truncate

• 在事务中

  • truncate 不支持 回滚

  • delete 支持

    SELECT * FROM  `tab_enum`;
    
    # delete 数据回滚，数据没有没被清空
    SET autocommit =0;
    START TRANSACTION;
    DELETE FROM `tab_enum`;
    ROLLBACK;
    
    
    # truncate 数据没回滚，数据被清空
    SET autocommit =0;
    START TRANSACTION;
    TRUNCATE TABLE `tab_enum`;
    ROLLBACK;
    
    SELECT * FROM  `tab_enum`;
    

• truncate 会重置自增列的值 （delete 不会）

• truncate 没有返回值 / delete 有返回值（多少行受影响）