MYSQL
MYSQL基本概念及介绍
- 查用语言分为四种DQL；DML；DDL；TCL.以上下文逐一展开详述。
- 视图
- 变量、存储过程和函数
- 流程控制：分支和循环
数据库常见概念
- DB：【database】 数据库，存储数据的容器,创建：‘create database 数据库名;’。
- DBMS：【Database Management System】数据库管理系统，又称为数据库软件或数据库产品，用于创建或管理DB，我们常说的数据库指的就是数据库管理系统，如mysql。
- SQL：【Structured Query Language】结构化查询语言，用于和数据库通信的语言，不是某个数据库软件特有的，而是几乎所有的主流数据库软件通用的语言。
数据库存储数据特点
- 数据存放到表中，表存放到库中。
- 每个库有多张表，表名不能重复。
- 表中有一个或多个列，或称为字段,相当于java中“属性”，行相当于Java中对象。
MYSQL背景和优点
- 前身属于瑞典的MySQL AB公司，08年被sun公司收购，09年sun被oracle收购。
- 优点：①开源、免费、成本低；②性能高、移植性也好（多平台）③体积小便于安装。
MYSQL服务启动、停止与登录、退出
服务启动、停止
- 命令行 net start mysql[+版本号]/net stop mysql[+版本号]
- 我的电脑 -> 管理 -> 服务
服务登录、退出
- mysql【-h 主机名-P 端口号】-u 用户名 -p【密码】 小写-p后面不能有空格
- 退出：exit或ctrl+c（不建议）
DQL(Data Query Language)数据查询语言
一般语法及执行顺序如下:
select 查询列表 ⑦ #要查询的列表
from 表1 别名 ① #原始表
连接类型 join 表2 ② #连接表
on 连接条件 ③ #连接条件
where 筛选 ④ #分组前筛选
group by 分组列表 ⑤ #分组依据
having 筛选 ⑥ #分组后筛选
order by排序列表 ⑧ #排序
limit 起始条目索引，条目数; ⑨ #分页查询
一、基本查询
基本语法 select 查询列表 【from 表】；结果是个虚表；类似打印操作。
-
运算符，加 ‘+’，select 1 + ‘5’; →6
减 ‘-’，select 5 - 1; →4
乘 ‘*’，select 5 * ‘2’;→10
除 ‘/’，select ‘5’ / 2;→2.5
商 ‘div’，select ‘5’ div 2; →2
余 ‘mod或%’，select 5 % 2 , 5 mod 2 ;→1,1
注意：如果传入的值为文本，在运算时会自动转换成数字，如以上数字加了引号并不影响结果；如果无法转化为数字，如’abc’，则按照0来带入计算，select ‘2’+‘a’；结果为2，另外，如果其中任意一个值为null时，结果为null，如select 2 + null;。 -
比较运算符，逻辑运算符等返回值为布尔值（0或1）,此处不再详述。以及运算符优先级
可以点击这里查看 -
正则表达式：返回0或1，如SELECT ‘x’ REGEXP ‘[^abc]’;→ 0
命令 说明 ^ 在字符的开启处进行匹配 $ 在字符的末尾处进行匹配 . 匹配任何字符（包括回车和新行） [….] 匹配括号内的任意单个字符 [m-n] 匹配m到n之间的任意单个字符，例如[0-9],[a-z],[A-Z] [^…] 不能匹配括号内的任意单个字符 a* 匹配0个或多个a,包括空,可以作为占位符使用 a+ 匹配一个或多个a,不包括空 a? 匹配一个或0个a a1| a2 匹配a1或a2 a{m} 匹配m个a a{m,} 匹配m个或者更多个a a{m,n} 匹配m到n个a a{,n} 匹配0到n个a (….) 将()中元素组成单一元素，例如 (do)* 意思是匹配0个多或多个do -
查询表
qq表如下：qq game 10000 a 10000 b 10000 c 20000 c 20000 d -
select * from qq；#查询全部字段qq game 10000 a 10000 b 10000 c 20000 c 20000 d -
select distinct qq from qq；#查询某个字段并去重qq 10000 20000 -
使用函数的查询： select qq , group_concat(game separator '_') game from qq；#组合文本qq game 10000 a_b_c 20000 c_d mysql函数详细列表及简单示例请点这里 涉及分组函数的之后有分组查询专门的小节。
-
起别名：as|空格，如select distinct qq as 号码 from qq;或select distinct qq 号码 from qq;
号码 10000 20000
-
二、条件查询
基本语法：select 查询列表 from 表名 where 筛选条件（不能是分组函数）
-
条件（比较）运算符
> ，< ，=， 不等于（<>， !=）， >= ，<= ，<=>安全等于。如：select * from QQ where qq > 10000;qq game 20000 c 20000 d -
逻辑运算符
&&（and） 、|| （or）、 ! （not） 非。 如：select * from qq where qq > 10000 **and** game = 'c';qq game 20000 c -
模糊查询
like:一般搭配通配符使用，可以判断字符型或数值型
通配符：%任意多个字符，_任意单个字符，如：select * from qq where qq like '2%'; 匹配以2开头的任意字符，支持数字匹配。qq game 20000 c 20000 d in：后接一个列表或子查询列表，如：
select * from game in ( 'c' , 'd' ) ; #查找game为c或d的所有行。qq game 10000 c 20000 c 20000 d is null / is not null：判断是否为空值；
先在qq表中插入一行含null值数据，再查找条件为null的行。insert into qq values ( 30000 , null ) , ( 40000 , null ); select * from qq where game <=> null； select * from qq where game is null；结果与上一句等价。qq game 30000 null 40000 null 比较安全等于 <=> 和 is null的区别， <=> 可以直接判断数值型，is null不可以。
三、排序查询
-
基本语法：
select 要查询列表 from 表 【where 筛选条件】 order by 需要排序的列表 【asc/desc】；#默认为asc升序. -
排序列表支持：单个或多个字段、函数、表达式、别名。
如：select * from qq where qq < 30000 order by qq , game desc;qq game 10000 c 10000 b 10000 a 20000 d 20000 c
四、分组查询
基本语法：语句执行顺序如右。group by是对条件查询的结果再进行分组。
select 分组函数，分组后的字段 ⑥
from 表 ①
【where 筛选条件】 ②
group by 分组的字段 ③
【having 分组后的筛选】 ④
【order by 排序列表】 ⑦
-
分组函数：
max() 最大值 min() 最小值 sum() 和 avg() 平均值 count() 计算个数注意函数和括号之间不能有空格，并且以上分组函数计算时均 忽略null值 如，
select count(game) from qq;返回值为 5，两行空值未计入其中体会一下count()函数的区别：结果都是7。
select count(*) from qq; # '*' →速度最快 select count(1) from qq; # 任意常数 →较慢，INnoDB引擎下与count(*)相近 select count(0) from qq; # 常数0 →较慢，INnoDB引擎下与count(*)相近 select count('a') from qq; #任意字符串 →较慢，INnoDB引擎下与count(*)相近 select count(qq) from qq; #要查询的目标字符串 →速度最慢 -
分组前筛选where和分组后筛选having的区别：
使用关键字 筛选的表 语句中位置 分组前筛选 where 原始表 group by的前面 分组后筛选 having 分组后的结果 group by 的后面 例如：查询qq表中qq不等于30000且游戏个数大于1的信息。
select qq , count(*) as 计数 from qq where qq != 30000 group by qq desc #排序也可以在group by 字段名 后直接写升序(asc)或降序(desc) having count(*) >1;qq 计数 20000 2 10000 3 一般来讲，能用分组前筛选的，尽量使用分组前筛选，提高效率。
五、连接查询
| 类型 | 连接条件 | SQL92语法 | SQL99语法 |
|---|---|---|---|
| 内连接 | 等值 | √ | √ |
| 非等值 | √ | √ | |
| 自连接 | √ | √ | |
| 外连接 | 左外 | mysql不支持 | √ |
| 右外 | mysql不支持 | √ | |
| 全外 | mysql不支持 | mysql不支持 | |
| 交叉连接 | 无 | 笛卡尔乘积 | 笛卡尔乘积 |
注：笛卡尔乘积指，在无有效连接条件下，一个有M行数据的表和一个有N行的表交叉连接后得到的表行数是它们的乘积M*N，M行逐行对应另一表N行，而列左右拼接。
Ⅰ.SQL92语法
-
新建一个表 qq1，用来保存上文group_concat后的表。表内容如下：
id game_concat 10000 a_b_c 20000 c_d d
-
等值连接
select 查询列表 from 表1 as a,表2 as b where a.keya=b.keyb #等值的含义在这里，两表的键写作相等。 ... #筛选，分组，排序等例如：通过qq表查找qq1表中的game_concat字段，如下
select distinct qq , game_concat from qq1,qq where qq.qq=qq1.id and qq=10000;qq game_concat 10000 a_b_c -
非等值连接
连接mysql自带的help_topic表来反向操作qq表的game_concat实现分词。可以用”
select help_topic_id from mysql.help_topic; “查看到help_topic_id是一个从0开始的自增列。select qq , substring_index( substring_index(game_concat , '_' , help_topic_id+1 ), '_' , -1) as game from qq1 , mysql.help_topic where help_topic_id < (length(game_concat)- #非等值连接 length(replace(game_concat,'_',''))+1); ##此处+1是因为分隔符个数比分割后字符串少一位。如下就得到分词后我们开头的qq表
qq game 10000 c 10000 b 10000 a 20000 d 20000 c 为帮助理解下面对分词函数做一解释：
SELECT SUBSTRING_INDEX(‘192,168,8,203’,’,’,1);#取出192 SELECT SUBSTRING_INDEX(‘192,168,8,203’,’,’,2);#取出192,168 SELECT SUBSTRING_INDEX(‘192,168,8,203’,’,’,-1);#取出203,规则类似python字符串切片 SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(‘192,168,8,203’,’,’,2),’,’,-1);#取出第二个数168。 -
交叉连接：笛卡尔乘积
select * from qq,qq1;可见行数为7*2，列左右拼接。qq game qq game_concat 10000 a 10000 a_b_c 10000 a 20000 c_d 10000 b 10000 a_b_c 10000 b 20000 c_d 10000 c 10000 a_b_c 10000 c 20000 c_d 20000 c 10000 a_b_c 20000 c 20000 c_d 20000 d 10000 a_b_c 20000 d 20000 c_d 30000 null 10000 a_b_c 30000 null 20000 c_d 40000 null 10000 a_b_c 40000 null 20000 c_d
Ⅱ.SQL99语法 （引入 join on 的用法）
内连接
-
等值连接
select 查询列表 from 表1 别名 【inner】 join 表2 别名 on 连接条件 where 筛选条件 ... 如上sql92的等值连接可以写作： select distinct qq , game_concat from qq join qq1 # inner 省略默认为内连接（取交集） on qq.qq=qq1.id where qq=10000; -
非等值连接
还是如上sql92的非等值连接可写作： select qq , substring_index( substring_index(game_concat , '_' , help_topic_id+1 ), '_' , -1) as game from qq1 join mysql.help_topic on # 将此处的连接方式换成l了 ...join ..on help_topic_id < (length(game_concat)- #非等值连接 length(replace(game_concat,'_',''))+1); -
交叉连接
select * from qq join qq1;省去连接条件即为交叉连接。
外连接
-
用集合的方式表示几种连接效果：
注：全外连接在MYSQL中并不支持，但在(如Oracle,SQL server等)中支持，写法一致。 -
left join 左边的为主表，right join 右边为主表，full join 两边都是主表。如果从表的键和主表的键可以匹配，将显示主表所有行并对应从表匹配行，如果从表没有匹配，则显示为null。如：
select qq.*,qq1.game_concat 从表 from qq left join qq1 on qq.qq=qq1.qq ;qq game 从表 10000 a a_b_c 10000 b a_b_c 10000 c a_b_c 20000 c c_d 20000 d c_d 30000 null null 40000 null null 另外，外连接也能非等值连接，只要满足场景需求。比如在上述内连接分词案例的关键词 join 前加上 left 其结果不变。
-
交叉连接-笛卡尔乘积
如上述交叉连接情况在sql99语法中写作： select * from qq cross join qq1; 结果一致。
六、子查询（子查询的语句能单独执行，一般放在括弧中）
-
按子查询结果集的行列数分为如下4类：
- 标量子查询（子查询结果只有一行一列）
- 列子查询（子查询结果只有一列多行）
- 行子查询（子查询结果有一行多列）
- 表子查询（子查询结果一般为多行多列）
为了方便说明问题引入如下qq_user表：
insert into qq_user values
(1,‘Tom’,‘男’,22,10000,6666),
(2,‘K_ing’,‘男’,31,20000,8888),
(3,‘Lisa’,‘女’,24,30000,7777),
(4,‘罗小黑’,‘男’,10,40000,null);
-
select 后的子查询——只能是常量子查询。
如查找Tom工资和平均工资的差额： select (select salary from qq_user where name = 'Tom') - (select avg(salary)from qq_user) as 平均差额;
PS：无论select 后面的子查询是什么样的，子查询的结果必须为单行单列。 -
from 后的子查询——4种都支持，但一定要起别名。
如查找age>18的男性的姓名及其工资： select name , salary from (select * from qq_user where age>18) as a #这里必须起别名 where gender='男';
PS：因为子查询的结果为一个虚表，故4种都支持，但一定要给子查询结果起别名。 -
where或having后面——除了表子查询的其他三种都支持。
标量子查询：如查询工资大于平均工资的人员信息。 select * from qq_user where salary > (select avg(salary) from qq_user )id name gender age qq salary 2 K_ing 男 31 20000 8888 列子查询：如查询有玩游戏的人员信息。 select * from qq_user where name in (select name from qq_user , qq where qq_user.qq=qq.qq and game is not null);id name gender age qq salary 1 Tom 男 22 10000 6666 2 K_ing 男 31 20000 8888 行子查询：如查询玩游戏个数最多的且工资salary最低的人员是否存在。 select * from qq_user , qq1 where qq1.qq=qq_user.qq and (length(game_concat),name)= #这里多个条件来确定结果行，子查询结果并为一行 ((select max(length(game_concat)) from qq1), (select name from qq_user group by name having salary=min(salary)));;
注：where 和 having后括号内放入查询条件列表，后面子查询结果需要按顺序依次对应，并且每一个子查询结果必须为一行一列的标量，然后才可以拼为一行，比如把第一个子查询的 max 函数去掉，就会报1242错误，即“子查询返回超过一行”。 -
exists后面——使用表连接，返回存在对应关系的行。一般用其他三种子查询可代替。
如查找有工资的qq用户玩哪些游戏： select * from qq where exists (select * from qq_user where salary is not null and qq.qq=qq_user.qq); #这里的连接条件不可丢。
注：通俗来说是两表连接后，如果原表中的行在子查询结果表有对应的连接，则返回原表这一行。exists()本身作为函数可以单独使用，返回值为0或1。
七、分页查询
- 语法形式 limit [offset,] size;
-
两个参数：
offset要显示条目的起始索引（默认从0开始）
size 要显示的条目个数 -
特点：
①limit语句放在查询语句的最后。
②公式： 先定义好要显示的页数page，每页的条数size，结合前端实现分页展示。select 查询列表 from 表 limit (page-1)*size,size; 如，size=10，当page=1时显示第一页的十条，page=2时显示第二页的十条......案例：求工资第二的人是谁。
select * from qq_user order by salary desc limit 1,1;
注：分页查询在Oracle和SQL server中各有不同，三者区别此处不展开比较。
-
八、联合查询
-
语法：
查询语句1 union [all] #省略 all 默认会去重。 查询语句2 union [all] ... -
特点：
1、可用来将一条比较复杂的查询语句拆分成多条语句。 2、要求多条查询语句的查询列数必须一致。 3、要求多条查询语句的查询的各列类型、顺序最好一致。
DML(Data Manipulation Language)数据操作语言
- 插入：insert into
- 修改：update
- 删除：delete
一、insert
-
insert into 表名(列名,…) values(值1,…)…;
Ⅰ.列名可以省略，默认所有列，也可以是非空的部分列，且列的顺序和插入时列的顺序一致。 Ⅱ.如果语句中有列名，则传入的值得个数必须和列得个数一致。 Ⅲ.插入的值的类型要与列的类型一致或兼容。 -
insert into 表名 set 字段1=值1,字段2=值2,…;
逐行插入，字段顺序无关紧要，但要注意有非空约束的字段必须要传入值。 -
insert into 表名 select…
后接select子查询，要求子查询的列数和原表列数一致。类型一致，注意错位不报错。 如：create table qq2 like qq; #先创建一个表qq2和qq表结构一样 insert into qq2 select salary ,id from qq_user; #故意传入列名不一致但类型兼容的子查询
虽然不会报错但实际意义是错的！
二、update 修改
-
修改单表：update 表名 set … where …;
如，修改qq表中QQ号为30000的人玩的游戏为'e'。 update qq set game='e' where qq=30000;
-
修改多表:update 表1 left|right|inner join 表2 on 表1.key=表2.key set … where …;
如，将罗小黑的工资改为30，且qq表中它的游戏改为'f'. update qq_user left join qq on qq_user.qq=qq.qq set salary=30 ,game='f' where name='罗小黑’;查看两表结果如下：
注：1175安全更新模式报错解决办法：SET SQL_SAFE_UPDATES = 0;
三、delete 删除
-
单表删除：delete from 表名 where…
-
多表删除：delete from 表1 left|right|inner join 表2 on 表1.key=表2.key where…
-
清空一个表所有内容：truncate table 表名;
区别： ① delete 可以加where条件删除，truncate不能； ② truncate 效率稍快； ③ delete 有返回值，truncate 没有返回值； ④ truncate 删除不支持回滚，delete支持回滚； ⑤ 若用delete删除自增列，再次插入数据，自增列的值从断点开始， 若用truncate删除自增列，再次插入数据，自增列的值从1开始。
DDL(Data Definition Language)数据定义语言
一、库
- 创建库
create database [if not exists] 库名[ character set 字符集名]; - 修改库
alter database 库名 character set 字符集名; - 删除库
drop database [if exists] 库名;
二、表
-
创建表
create table 表名(
列名1 列的类型 [(长度) 约束],
…
); -
修改表
①修改列名
ALTER TABLE 表名 CHANGE [COLUMN] 旧列名 新列名 数据类型;
②修改列的类型或约束
ALTER TABLE 表名 MODIFY [COLUMN] 列名 数据类型 [约束]；
③添加新列
ALTER TABLE author ADD [COLUMN] 列名 数据类型 [约束];
④删除列
ALTER TABLE 表名 DROP [COLUMN] 列名;
⑤修改表名
ALTER TABLE 原表 RENAME TO 新表名; -
删除表
drop table [if exists] 库名; -
表的复制
①仅复制表结构：CREATE TABLE 新表名 LIKE 目标表名;
②复制表结构和数据（子查询）：CREATE TABLE 新表名 select…子查询;
③复制部分字段：
CREATE TABLE 新表名 SELECT 要复制的字段 FROM 原表 [where 1]；
where后面‘1’表示同时传入子查询数据(默认)，‘0’表示只复制子查询结果的表结构。CREATE TABLE qq_copy SELECT name,age,qq FROM qq_user where 1;where 1时：
where 0时：
三、数据类型
- 数值型
- 日期和时间型
- 字符型
四、约束（用于保证表中的数据的准确和可靠性的一种限制）
- NOT NULL：非空，该字段的值必填。
- UNIQUE：唯一，该字段的值不可重复，注意只能有一个null。
- DEFAULT：默认，该字段的值不主动插入情况下为默认值。
CHECK：检查，mysql不支持。- PRIMARY KEY：主键，该字段的值不可重复并且非空，但不同于unique+not null。
- FOREIGN KEY：外键，该字段的值（从表）引用了另外的表（主表）的字段。
1、要求在从表设置外键关系
2、从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
3、主表的关联列必须是一个key（一般是主键或唯一）
4、插入数据时，先插入主表，再插入从表,删除数据时，先删除从表，再删除主表
注：可以通过SHOW INDEX FROM 表名;查看一个表的索引和哪些字段为key。
-
创建表时添加约束：
总结区别：
-
修改表时添加或修改约束：
①列级约束：(除了外键)
alter table 表名 modify column 字段名 字段类型 新约束;
②表级约束：(主键、唯一、外键、[检查])，此处不支持修改/删除。
alter table 表名 add 【constraint 约束名】 约束类型(字段名) 【外键的引用】; -
删除约束（具有index属性的三种约束）
①删除主键：alter table 表名 drop primary key;
②删除唯一：alter table 表名 drop index 列名;
③删除外键：alter table 表名 drop foreign key [创建时自定的约束名];
其他约束可以用修改表的方式在字段类型后不加约束的方式删除。
TCL(Transaction Control Language)事务控制语言
- 用来管理 insert,update,delete 语句
-
说明：事务处理可以用来维护数据库的完整性，保证成组的 SQL 语句要么全部执行，要么全部不执行。在 MySQL 中只有使用了 Innodb 引擎的数据库或表才支持事务。
-
ACID特性：
①、原子性（Atomicity，或称不可分割性）：一个事务是不可再分割的整体，要么都执行要么都不执行②、一致性（Consistency）：
一个事务可以使数据从一个一致状态切换到另外一个一致的状态③、隔离性（Isolation，又称独立性）：
数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个 事务并发执行时由于交叉执行而导致数据的不一致。事务隔离级别为： 读未提交（Read uncommitted）; 读提交（read committed）; 可重复读（repeatable read）; 串行化（Serializable）;④、持久性（Durability）:
事务一旦提交，对数据的修改是永久的，即便系统故障也不会丢失。 -
事务的使用
隐式（自动）事务：没有明显的开启和结束，本身就是一条事务可以自动提交，比如insert、update、delete。隐式没啥好说的。
显式事务：具有明显的开启和结束。
①开始显式事务begin或 set autocommit=0;设置自动提交为0.②insert,update,delete 语句
③结束事务提交：commit; 回滚：rollback; 回滚到指定的地方： savepoint 保存点名; #可在事务语句组中任意完整语句之间添加。 rollback to 保存点名; #可回滚到设置的保存点，可将事务打散而不用每次都回滚整个事务 release savepoint 保存点名; #删除一个事务的保存点，没有指定的保存点时，会抛出一个异常； -
事务并发带来的问题及解决办法
①脏读：当前事务读取了其他事务还未提交的数据，读到的是其他事务“更新”的数据。
②不可重复读：一个事务多次读取，结果不一样。
③幻读：当前事务读取了其他事务还未提交的数据，读到的是其他事务“插入”的数据。
解决办法：设置隔离级别set session(会话)|global(全局) transaction isolation level 隔离级别； 四种隔离级别及用途如下：
注：×表示不能解决，√表示可以可以解决。
视图-views
-
含义：是一个虚拟表，它的数据来自于表，通过执行时动态生成，不占用实际内存。
-
用途：
①简化sql语句。
②提高了sql的重用性。
③保护基表的数据，按权限展示可供展示数据，提高了安全性。 -
创建：
create view 视图名 as 查询语句; -
修改：
方式一：create or replace view 视图名 #or replace 表示替换原来的视图 as 查询语句; 方式二：alter view 视图名 #和修改表的方式一样。 as 查询语句; -
查看：
DESC myv3; SHOW CREATE VIEW myv3; #和查看表一致，查看视图的创建语句。 -
删除：drop view 视图1，视图2,…;
-
更新原表：因为视图主要用来查看，如果视图和原表失去一对一关系则不可更新原表。
·聚合函数（SUM(), MIN(), MAX(), COUNT()等） --聚合后失去一对一关系 ·DISTINCT --去重后失去一对一关系 ·GROUP BY --分组后失去一对一关系 ·HAVING --having在分组后 ·UNION或UNION ALL --联合查询无序，也失去一对一关系 ·位于选择列表中的子查询 --联合查询无序，也失去一对一关系 ·Join --特定结构，视图无法更新原表 ·FROM子句中的不可更新视图 ·WHERE子句中的子查询，引用FROM子句中的表。 --特定结构，视图无法更新原表 ·仅引用文字值（在该情况下，没有要更新的基本表）。 --常量视图无法更新 ·使用临时表（TEMPTABLE）总会使视图成为不可更新的。 --特定结构，视图无法更新原表 -
插入原表：如果满足可更新条件，如果需要通过视图插入原表，还需满足如下条件：
·视图的列名不得有重复。 ·视图必须包含没有默认值的基表中的所有列。 ·视图列必须是简单的列引用而不是导出列（计算字段）。如，列1/列2。 -
视图和表的比较
变量、存储过程和函数
一、变量（variables）
系统变量：由系统定义，属于服务器层面。
-
全局变量：针对于所有会话（连接）有效，但不能跨重启服务使用。
①查看所有全局变量 SHOW GLOBAL VARIABLES; ②查看满足条件的部分系统变量 SHOW GLOBAL VARIABLES LIKE '%char%'; ③查看指定的系统变量的值 select @@global.系统变量名; ④为某个系统变量赋值 方式一：set global 系统变量名=值; 如：SET GLOBAL autocommit=0; 方式二：set @@global.系统变量名=值; 如：SET @@global.autocommit=0; -
会话变量：仅针对于当前会话（连接）有效。
①查看所有会话变量 SHOW SESSION VARIABLES; ②查看满足条件的部分会话变量 SHOW SESSION VARIABLES LIKE '%char%'; ③查看指定的会话变量的值 SELECT @@会话变量名; SELECT @@[session.]会话变量名; ④为某个会话变量赋值 方式一：set [session] 会话变量名=值; 如：set autocommit=1; 方式二：set @@[session.]会话变量名=值; 如：set @@autocommit=1;
自定义变量：
-
用户变量：针对于当前会话（连接）有效，作用域同于会话变量。
①声明，赋值，更新： set @变量名:=值; #无需declare语法声明，而是直接赋值，':='写作'='也可。 select @变量名:=值; #会给该变量赋值，同时输出变量的“结果集” select xx into @变量名； #赋值，并不输出“结果集”。 ②使用变量：select @变量名; -
局部变量：仅仅在定义它的begin end块（即编程环境）中有效。
①声明：declare 变量名 类型 【default 值】; #begin end 语句块中首句 ②赋值或更新 set 变量名=值; set 变量名:=值; select xx into 变量名 from 表; ③使用变量：select 变量名;
二、存储过程（Stored procedure）
-
创建 ★
DELIMITER $ create procedure 存储过程名(参数模式 参数名 参数类型) begin #如果存储过程体仅仅只有一句话，begin end可以省略. 存储过程体（每条语句';'结尾，故需要重新定义结束符，即'$'.） end $ DELIMITER ; #还原结束符参数模式：
- in：该参数可以作为输入，也就是该参数需要调用方传入值
- out：该参数可以作为输出，也就是该参数可以作为返回值
- inout：该参数既作为输入又作为输出，也就是该参数既需要传入值，又可以返回值
-
调用
call 存储过程名(实参列表) 举例： 调用无形参模式; call sp1(); #执行输出sp1结果 调用in模式的参数： call sp1（实参）; #带入实参执行sp1并输出结果 调用out模式的参数： set @name; #定义用于接收输出结果的变量 call sp1(@name); #执行sp1并将结果传入@name select @name; #select查看变量@name的值 调用inout模式的参数： set @name=值; #定义用于接收输出结果的变量并必须赋值 call sp1(@name); #带入@name的值执行sp1并将结果传入@name select @name; #select查看变量@name的值 -
查看创建语句
show create procedure 存储过程名; -
删除
drop procedure 存储过程名;
三、函数（Function）
-
创建
DELIMITER $ create function 函数名(形参列表) returns 返回值的类型 begin 函数体 end $ DELIMITER ; #还原结束符注意：
- 参数列表中每个参数包含两部分：参数名 参数类型
- 函数体：一定要有return语句，否则会报错，如果return语句没有放在函数体的最后也不报错，但不建议。
-
调用
select 函数名(实参列表); #使用场景和方法与开始提到的内置函数方法一致。 -
查看
show create function 函数名; -
删除
drop function 函数名；
流程控制语句
一、分支结构
-
IF函数
IF(判断条件，真值，假值)
双分支结构，可嵌套多分支，类似excel中的IF函数。如：将qq_user表中的salary按照大于8000为A，7000-7999为B，小于7000为C分栏。 DELIMITER $ CREATE FUNCTION f1(salary int) RETURNS char BEGIN declare a char ; set a = if(salary>=8000, 'A',if(salary>=7000,'B','C')); return a; END $ DELIMITER ; select *, f1(salary) as 等级 from qq_user; #查看结果
-
case … when … then … else … end case：
case 表达式或字段 when 值1 then 语句1; #缺点就是只能实现等值判断 when 值2 then 语句2； .. else 语句n; end [case]; 如，找出罗小黑并标记为妖精。 DELIMITER $ CREATE FUNCTION f2(qq_name char(8)) RETURNS char(8) BEGIN declare a char(8) ; case qq_name when '罗小黑' then set a='妖精'; else set a='人'; end case; return a; END $ DELIMITER ; select *, f2(name) as 等级 from qq_user; #查看结果
-
case when … then … else … end case
case when 条件1 then 语句1; when 条件2 then 语句2； .. else 语句n; end [case]; 如，将未成年人玩游戏的情况标记为家长监护，成年人标记未注意休息。 DELIMITER $ CREATE FUNCTION f3(age int) RETURNS char(8) BEGIN declare a char(8) ; case when age<18 then set a= '家长监护'; else set a='注意休息'; end case; return a; END $ DELIMITER ; select qq.*,qq_user.name, f3(age) as 提醒 from qq,qq_user where qq.qq=qq_user.qq; #查看结果
☆注：以上三种可以放在任何位置：
如果放在begin end 外面，作为表达式结合着其他语句使用
如果放在begin end 里面，一般作为独立的语句使用 -
只能放在begin end 里面的IF多分支结构：
if 条件1 then 语句1; elseif 条件2 then 语句2; ... else 语句n; end if; 如，为qq表游戏a和b分类为RPG，c和d分类为SG，e和f分类为FPS。 DELIMITER $ CREATE FUNCTION f4(game char(8)) RETURNS char(8) BEGIN declare a char(8) ; if game in ('a','b') then set a= 'RPG'; elseif game in('c','d') then set a='SG'; else set a='FPS'; end if; return a; END $ DELIMITER ; select *,f4(game) as 分类 from book.qq; #查看结果
二、循环结构—只能放在begin end中
- 循环控制语句
- leave：类似于break，用于跳出所在的循环
- iterate：类似于continue，用于结束本次循环，继续下一次
-
【名称:】while 循环条件 do
循环体
end while 【名称】;如，输入一个正整数m，输出从1到m的和并显示加法算式，例：m=3，输出 6=3+2+1 ： DELIMITER $$ CREATE FUNCTION f5(num int) RETURNS char(200) BEGIN declare a int default 1 ; declare b char(2) default '+'; declare c char(200) default ''; declare d int default 0 ; my1:while a<=num do #循环名称为my1 select concat(a,b,c) into c; set d=d+a; set a=a+1; end while my1 ; return concat(d,'=',substring_index(c,'+',num)); #去掉多余拼接的一个'+'. END $$ DELIMITER ; select f5(5); #调用
PS：while先判断，后执行。 -
【名称:】loop
循环体;
end loop 【名称】;上述函数可以写成： DELIMITER $$ CREATE FUNCTION f6(num int) RETURNS char(200) BEGIN declare a int default 1 ; declare b char(2) default '+'; declare c char(200) default ''; declare d int default 0 ; my2:loop select concat(a,b,c) into c; set d=d+a; set a=a+1; if a>num then leave my2; #leave和iterate后要加循环名称 else iterate my2; end if; #if最后的end if 记得不能漏加 end loop my2 ; return concat(d,'=',substring_index(c,'+',num)); END $$ DELIMITER ;PS:loop一般用来模拟简单的死循环
-
【名称:】repeat
循环体
until 结束条件
end repeat 【名称】;DELIMITER $$ CREATE FUNCTION f7(num int) RETURNS char(200) BEGIN declare a int default 1 ; declare b char(2) default '+'; declare c char(200) default ''; declare d int default 0 ; my2:repeat select concat(a,b,c) into c; set d=d+a; set a=a+1; until a>num #until 结束条件语句后不能加';' end repeat my2 ; return concat(d,'=',substring_index(c,'+',num)); END $$ DELIMITER ;PS：repeat先执行后判断，循环至少执行一次。
完
最后附上几个本人参考的网址：
2019-09-13 中秋！给自己的中秋大礼包(ง •_•)ง
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
