前要：结尾彩蛋
目录
一.视图
1.视图概述
视图是由数据库中的一个表或多个表导出的虚拟表，是一种虚拟存在的表，方便用户对数据的操作。
视图是一个虚拟表，是从数据库中一个或多个表中导出来的表，其内容由查询定义。
同真实表一样，视图包含一系列带有名称的列和行数据
数据库中只存放了视图的定义，而并没有存放视图中的数据。这些数据存放在原来的表中。
使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。
一旦表中的数据发生改变，显示在视图中的数据也会发生改变。
使用视图的原因
安全原因，视图可以隐藏一些数据，例如，员工信息表，可以用视图只显示姓名、工龄、地址，而不显示社会保险号和工资数等
另一个原因是可使复杂的查询易于理解和使用。
2.创建视图
1)语法格式
CREATE [OR REPLACE] [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}] VIEW 视图名[(属性清单)] AS SELECT语句 [WITH [CASCADED|LOCAL] CHECK OPTION];REPLACE：替换现有视图
ALGORITHM：可选项，表示视图选择的算法。
属性清单：可选项，指定视图中各个属性的名词，默认情况下与SELECT语句中的查询的属性相同。
SELECT语句：表示一个完整的查询语句，将查询记录导入视图中。
WITH CHECK OPTION：可选项，表示更新视图时要保证在该视图的权限范围之内。
2)视图示例
mysql> create view emp_sal_view -> as -> select name, date, basic+bonus as total -> from employees as e -> inner join salary as s -> on e.employee_id=s.employee_id; Query OK, 0 rows affected (0.00 sec) mysql> select * from emp_sal_view where year(date)=2020 and month(date)=12;创建包含员工名、email和部门名的视图
mysql> use nsd2021; mysql> create view emp_view -> as -> select name, email, dept_name -> from employees as e -> inner join departments as d -> on e.dept_id=d.dept_id; Query OK, 0 rows affected (0.01 sec) # 查询视图中数据 mysql> select * from emp_view; mysql> select * from emp_view where dept_name='运维部'; +-----------+--------------------+-----------+ | name | email | dept_name | +-----------+--------------------+-----------+ | 廖娜 | liaona@tarena.com | 运维部 | | 窦红梅 | douhongmei@tedu.cn | 运维部 | | 聂想 | niexiang@tedu.cn | 运维部 | | 陈阳 | chenyang@tedu.cn | 运维部 | | 戴璐 | dailu@tedu.cn | 运维部 | | 陈斌 | chenbin@tarena.com | 运维部 | +-----------+--------------------+-----------+ 6 rows in set (0.00 sec)
3.修改视图
1)语法格式
方式一：
mysql> alter view emp_sal_view -> as -> select name, date, basic, bonus, basic+bonus as total -> from employees as e -> inner join salary as s -> on e.employee_id=s.employee_id; Query OK, 0 rows affected (0.01 sec) mysql> select * from emp_sal_view where year(date)=2020 and month(date)=12;与创建视图完全一样
CREATE [OR REPLACE] [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}] VIEW 视图名[(属性清单)] AS SELECT语句 [WITH [CASCADED|LOCAL] CHECK OPTION];示例：
mysql> create or replace view emp_view -> as -> select name, email, d.dept_id, dept_name -> from employees as e -> inner join departments as d -> on e.dept_id=d.dept_id; mysql> select * from emp_view;方式二
ALTER VIEW 视图名 AS 查询语句
4.查看视图
语法：
SHOW TABLES DESC 视图
5.删除视图
语法
DROP VIEW 视图1, 视图2, ...示例：
mysql> drop view emp_view, emp_sal_view; Query OK, 0 rows affected (0.00 sec)
二.变量
1.变量分类
mysql变量可分为两大类：
系统变量：由系统提供，不是由用户定义的。包括全局变量、会话变量
用户自定义变量：用 户定义的变量。包括用户变量、局部变量
1)局部变量
只能用在begin/end语句块中，比如存储过程中的begin/end语句块。
2)用户变量
用户变量不用提前声明，在用的时候直接用“@变量名”使用就可以了。
3)会话变量
服务器为每个连接的客户端维护一系列会话变量
其作用域仅限于当前连接，即每个连接中的会话变量是独立的
4)全局变量
影响服务器整体操作，作用于所有会话
当服务启动时，它将所有全局变量初始化为默认值
更改全局变量，必须具有super权限
其作用域为server的整个生命周期，服务重启消失
2.使用系统变量
1)查看变量
查看所有系统变量
mysql> show global variables; # 查看所有全局变量 mysql> show session variables; # 查看当前会话变量查看满足条件的部分变量
mysql> show global variables like '%char%'; # 不指定global的话，默认为会话变量查看某个系统变量
# 变量结构为@@变量名、@@global.变量名、@@session.变量名 mysql> select @@tx_isolation; # 默认为会话变量 +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+ 1 row in set (0.00 sec) mysql> select @@global.character_set_system; +-------------------------------+ | @@global.character_set_system | +-------------------------------+ | utf8 | +-------------------------------+ 1 row in set (0.00 sec) mysql> select @@session.tx_isolation; +------------------------+ | @@session.tx_isolation | +------------------------+ | REPEATABLE-READ | +------------------------+ 1 row in set (0.00 sec)
2)变量赋值
为系统变量赋值
set global|session 系统变量名=值 或 set @@global|session.系统变量名=值示例：
mysql> set @@global.autocommit=0; Query OK, 0 rows affected (0.00 sec) mysql> select @@global.autocommit; +---------------------+ | @@global.autocommit | +---------------------+ | 0 | +---------------------+ 1 row in set (0.00 sec)
3.使用用户变量
作用域
仅对当前会话有效，同于会话变量作用域
使用步骤
声明并初始化
SET @用户变量=值 或 SET @用户变量:=值 或 SELECT @用户变量:=值赋值
SET @用户变量=值 或 SET @用户变量:=值 或 SELECT @用户变量:=值 或 SELECT 字段 INTO @用户变量 FROM 表使用
SELECT @变量示例：
mysql> set @user='tom'; Query OK, 0 rows affected (0.00 sec) mysql> select count(*) from employees into @count; Query OK, 1 row affected (0.00 sec) mysql> select @user; +-------+ | @user | +-------+ | tom | +-------+ 1 row in set (0.00 sec) mysql> select @count; +--------+ | @count | +--------+ | 133 | +--------+ 1 row in set (0.00 sec)
4.使用局部变量
作用域
仅在定义它的BEGIN/END中有效
使用步骤
声明
DECLARE 变量 类型 DECLARE 变量 类型 DEFAULT 值赋值
SET 局部变量=值 或 SET 局部变量:=值 或 SELECT 局部变量:=值 或 SELECT 字段 INTO 局部变量 FROM 表使用
SELECT 局部变量
三.存储过程
1.存储过程的概述
存储过程是可编程的函数，在数据库中创建并保存，可以由一组SQL语句和控制结构组成。
提高了代码的重用性
减少了编译次数并减少了和数据库的连接次数，提高了效率
2.使用存储过程
1）创建存储过程
语法：
CREATE PROCEDURE 存储过程名(参数列表) BEGIN 一组合法的sql语句; END存储过程前后最好使用
DELIMITER //参数列表包含三部分：
参数模式
IN：需要调用者传值，与Python函数的参数作用类似
OUT：该参数可以作为输入。与Python函数的返回值类似
INOUT：既可以作为输入又可以作为输出
参数名
参数类型
分隔符
MySQL默认以";"为分隔符，如果没有声明分割符，则编译器会把存储过程当成SQL语句进行处理，因此编译过程会报错
所以要事先用“DELIMITER //”声明当前段分隔符，让编译器把两个"//"之间的内容当做存储过程的代码，不会执行这些代码
通过“DELIMITER ;”把分隔符还原。
2）调用存储过程
语法
CALL 存储过程(实参列表)存储过程示例
空参列表
mysql> drop database if exists mydb; mysql> create database if not exists mydb default charset utf8mb4; mysql> use mydb; mysql> create table departments like nsd2021.departments; mysql> create procedure dep_pro() -> begin -> insert into departments values -> (1, '人事部'), (2, '财务部'); -> end // Query OK, 0 rows affected (0.00 sec) mysql> call dep_pro() // Query OK, 2 rows affected (0.00 sec) mysql> select * from departments // +---------+-----------+ | dept_id | dept_name | +---------+-----------+ | 1 | 人事部 | | 2 | 财务部 | +---------+-----------+ 2 rows in set (0.00 sec) mysql> delimiter ; mysql> select * from departments;使用IN参数
mysql> use nsd2021; mysql> delimiter // mysql> create procedure empcount_pro(IN dept_no int) -> begin -> select dept_id, count(*) from employees -> where dept_id=dept_no -> group by dept_id; -> end // Query OK, 0 rows affected (0.00 sec) mysql> call empcount_pro(1)// +---------+----------+ | dept_id | count(*) | +---------+----------+ | 1 | 8 | +---------+----------+ 1 row in set (0.00 sec) mysql> delimiter ;使用OUT参数
mysql> use nsd2021; mysql> delimiter // mysql> create procedure empemail_pro(IN emp_name varchar(10), OUT mail varchar(25)) -> begin -> select email into mail -> from employees -> where name=emp_name; -> end// Query OK, 0 rows affected (0.00 sec) mysql> call empemail_pro('刘倩', @m)// Query OK, 1 row affected (0.00 sec) mysql> select @m// +--------------------+ | @m | +--------------------+ | liuqian@tarena.com | +--------------------+ 1 row in set (0.00 sec) mysql> delimiter ;使用INOUT参数
mysql> delimiter // mysql> create procedure myadd(INOUT i int) -> begin -> set i=i+100; -> end // Query OK, 0 rows affected (0.00 sec) mysql> delimiter ; mysql> set @n=8; Query OK, 0 rows affected (0.00 sec) mysql> call myadd(@n); Query OK, 0 rows affected (0.00 sec) mysql> select @n; +------+ | @n | +------+ | 108 | +------+ 1 row in set (0.00 sec)
3.查看存储过程
mysql> select name from mysql.proc where db='nsd2021'; +--------------+ | name | +--------------+ | empcount_pro | | empemail_pro | | myadd | +--------------+ 3 rows in set (0.00 sec) mysql> show create procedure empemail_pro \G
4.删除存储过程
mysql> drop procedure myadd; Query OK, 0 rows affected (0.00 sec)
四.流程控制结构
顺序结构：自上向下执行
分支结构：从多条路径中选择一条路径执行
循环结构：满足某种条件，反复执行一段代码
1.分支结构
1)if语句
语法：
IF 条件 THEN 语句; END IF; IF 条件 THEN 语句1; ELSE 语句2; END IF; IF 条件1 THEN 语句1; ELSEIF 条件2 THEN 语句2; ELSE 语句3; END IF;示例：
mysql> use nsd2021; mysql> delimiter // mysql> create procedure deptype_pro(IN no int, OUT dept_type varchar(5)) -> begin -> declare name varchar(5); -> select dept_name into name from departments -> where dept_id=no; -> if name='运维部' then -> set dept_type='技术部'; -> elseif name='开发部' then -> set dept_type='技术部'; -> elseif name='测试部' then -> set dept_type='技术部'; -> else -> set dept_type='非技术部'; -> end if; -> end// Query OK, 0 rows affected (0.00 sec) mysql> call deptype_pro(1, @t)// Query OK, 1 row affected (0.00 sec) mysql> select @t// +--------------+ | @t | +--------------+ | 非技术部 | +--------------+ 1 row in set (0.00 sec) mysql> call deptype_pro(3, @t1)// Query OK, 1 row affected (0.00 sec) mysql> select @t1// +-----------+ | @t1 | +-----------+ | 技术部 | +-----------+ 1 row in set (0.00 sec) mysql> delimiter ;
2)case语句
语法：
CASE 变量|表达式|字段 WHEN 判断的值1 THEN 返回值1; WHEN 判断的值2 THEN 返回值2; ... ... ELSE 返回值n; END CASE;示例
mysql> delimiter // mysql> create procedure deptype_pro2(IN no int, OUT dept_type varchar(5)) -> begin -> declare name varchar(5); -> select dept_name into name from departments -> where dept_id=no; -> case name -> when '运维部' then set dept_type='技术部'; -> when '开发部' then set dept_type='技术部'; -> when '测试部' then set dept_type='技术部'; -> else set dept_type='非技术部'; -> end case; -> end// mysql> call deptype_pro2(1, @tt)// Query OK, 1 row affected (0.00 sec) mysql> select @tt// +--------------+ | @tt | +--------------+ | 非技术部 | +--------------+ 1 row in set (0.00 sec) mysql> call deptype_pro2(3, @tt2)// Query OK, 1 row affected (0.00 sec) mysql> select @tt2// +-----------+ | @tt2 | +-----------+ | 技术部 | +-----------+ 1 row in set (0.00 sec) mysql> delimiter ;
2.循环结构
1)while循环
可能一次不执行
语法：
[标签:]WHILE 循环条件 DO 循环体; END WHILE [标签];示例：
mysql> use nsd2021; mysql> delimiter // mysql> create procedure while_pro(IN i int) -> begin -> declare j int default 1; -> while j<i do -> insert into departments(dept_name) values('hr'); -> set j=j+1; -> end while; -> end // Query OK, 0 rows affected (0.00 sec) mysql> call while_pro(3)// Query OK, 1 row affected (0.00 sec) mysql> delimiter ;使用LEAVE结束循环。此处LEAVE相当于其他语言的break
mysql> delimiter // mysql> create procedure while_pro2(IN i int) -> begin -> declare j int default 1; -> a:while j<i do -> insert into departments(dept_name) values('hr'); -> if j>=2 then -> leave a; -> end if; -> set j=j+1; -> end while a; -> end // Query OK, 0 rows affected (0.00 sec) mysql> call while_pro2(10)// Query OK, 1 row affected (0.00 sec) mysql> delimiter ;
使用ITERATE跳过本次循环。此处的ITERATE相当于其他整语言的continue
mysql> delimiter // mysql> create procedure while_pro3(IN i int) -> begin -> declare j int default 0; -> a:while j<i do -> set j=j+1; -> if mod(j, 2)=0 then -> iterate a; -> end if; -> insert into departments(dept_name) values(concat('hr', j)); -> end while a; -> end // Query OK, 0 rows affected (0.00 sec) mysql> call while_pro3(10)// Query OK, 1 row affected (0.00 sec) mysql> delimiter ;
2)loop循环
没有条件的死循环
语法：
[标签:]LOOP 循环体; END LOOP [标签]示例：
mysql> delimiter // mysql> create procedure loop_pro() -> begin -> declare i int default 0; -> a:loop -> set i=i+1; -> if i>5 then leave a; -> end if; -> insert into departments(dept_name) values(concat('hr1', i)); -> end loop a; -> end // Query OK, 0 rows affected (0.00 sec) mysql> call loop_pro()// Query OK, 1 row affected (0.00 sec) mysql> delimiter ;
3)repeat循环
至少循环一次
语法：
[标签:]REPEAT 循环体; UNTIL 循环结束条件 END REPEAT [标签]示例：
mysql> delimiter // mysql> create procedure repeat_pro(IN i int) -> begin -> declare j int default 1; -> a:repeat -> set j=j+1; -> insert into departments(dept_name) values('sales'); -> until j>i -> end repeat a; -> end // Query OK, 0 rows affected (0.00 sec) mysql> call repeat_pro(1)// Query OK, 1 row affected (0.00 sec) mysql> delimiter ;
附：通过python3的pymysql模块操作数据库
import pymysql conn = pymysql.connect( host='192.168.1.11', user='root', password='NSD2021@tedu.cn', db='mydb', charset='utf8mb4' ) cur = conn.cursor() insert1 = 'insert into departments(dept_name) values(%s)' for dep in ('da', 'db', 'dc', 'dd'): cur.execute(insert1, (dep,)) conn.commit() cur.close() conn.close()