MySQL编程
MySQL 使用标准 SQL 检索和处理数据，体积小、开源、免费，易于快速部署。正是因为这些特点，使得其在互联网行业，特别是 Web 应用方面使用相当广泛。至今最新的版本已到 8.0。
一 基本操作
MySQL 和 SQL Server 不同，它没有官方的图形化操作管理软件，它主要通过命令行的方式操作。
本文主要介绍 MySQL 编程相关的知识，安装和部署不在本文讲解范围，对于 MySQL 的安装和部署，请自行查阅官方文档或其他资料。
1，基本命令
系统级-登录和退出
1 net start mysql80(服务名) 2 net stop mysql80 3 /* 启动 mysql 服务，不同版本的 MySQL 服务名默认不一致，但安装的时候可以自己指定 */ 4 mysql -h 主机地址 -u 用户名 －p 用户密码 5 /* 登录 MySQL 服务器，如果是在本地登录可以省略主机地址 */ 6 exit 7 /* 退出登录 */
数据库-常用命令
1 show databases;-- 查看所有数据库的列表 2 use dbname;-- 选择数据库 3 show tables;-- 查看当前库所有表的列表 4 show columns from tablename;-- 查看某个表的所有字段信息 5 describe tablename;-- 另一种查看表结构的方式 6 show status;-- 查看服务器状态信息 7 show create database/table dbname/tbname;-- 查看创建数据库/表的语句 8 show grants;-- 查看当前用户权限列表 9 show errors/warnings;-- 查看服务器错误和警告
2，SQL基础语法
MySQL 采用标准 SQL 语法，他们同样分为 DDL、DQL、DML、DCL。
详情可以移步我的《SQL入门，就这么简单》。
3，注释
MySQL 使用 # 标记单行注释，/* */ 标记多行注释。
1 #这是单行注释 2 /* 3 这是多行注释 4 */ 5 -- 这也是单行注释
使用双横线注释时，双横线后面至少需要一个空格。
4，语句结束
由于 MySQL 使用命令行的方式操作数据库，所以对于每一条命令，我们必须明确的指定结束符。MySQL 默认的语句结束符是分号";"。
1 语句 1; 2 语句 2; 3 delimiter $ 4 /* 通过 delimiter 指定新的结束标识 */ 5 语句3$ 6 语句4$
比如，我们在创建存储过程或函数时，函数或存储过程内部有多条语句，但创建过程本身也是一条语句，为了避免冲突，所以在创建存储过程和函数时一般会临时修改语句结束符。不要忘记在创建完后再把结束符改为默认的分号哦。
5，约束
MySQL 中对约束进行了分类：表约束和列约束，语法稍有差异。
1 create table myTable( 2 col1 type[AUTO_INCREMENT], 3 col2 type, 4 col3 type, 5 col4 type, 6 col5 type NOT NULL, 7 col6 type DEFAULT 'something', 8 PRIMARY KEY (col1), 9 FOREIGN KEY (col2) REFERENCES mytable1(somecol), 10 UNIQUE(col3), 11 CHECK(col4<200) 12 ); 13 /*AUTO_INCREMENT 设置主键自增，相似的，MsSQL 使用IDENTITY(1,1) 设置自增*/
列约束只有 NOT NULL 和 DEFAULT，其他的均属于表约束。
6，查询相关
HAVING 关键字对聚合后的数据进行再筛选。WHERE 关键字是对原始数据的筛选。
1 select a.dep,avg(a.sal) as 平均工资 from salary a 2 left join department b 3 on a.dep=b.dep 4 where b.sta_num>10 5 having avg(sal)>10000; 6 /* 连接查询部门和部门平均工资，部门人数在10以上，且平均工资大于10000 */
LIMIT 关键字，可以用来返回指定条数的记录，常用来做分页查询（MsSQL 使用 top 关键字）。limit 需要指定两个 int 类型的参数，分别表示从哪条记录开始（第一条数据索引为0），提取多少条记录。当从第一条数据开始时，第一个参数可以省略。
1 /* 基础用法：返回指定数量记录 */ 2 select * from department order by sta_num limit 10; 3 /* 查询人数最少的10各部门情况 */
1 /* 分页查询 */ 2 SET @num:=10;#定义用户变量，稍后会详细讲解；每页需要查询的记录数 3 SET @page:=2;#需要查询第几页 4 SET @index=(@page-1)*@num;#开始索引 5 PREPARE mysql FROM "select * from country limit ?,?";#由于 limit 不支持使用变量，所以必须先使用 prepare 预编译 SQL 语句，？ 标识变量 6 EXECUTE mysql USING @index,@num;#使用 execute 执行预编译的语句，并使用 using 传递参数，顺序必须和预编译时一致
子查询又称嵌套查询，是指一个 SELECT 语句的查询结果被嵌套在另一个语句之中，子查询既可以放在 FROM 关键字后面，也可以放在 WHERE 后面。外部的语句可以是 SELECT、INSERT、DELETE、UPDATE。
1 select * from ( 2 selec * from tableA where col1>( 3 select min(col1) from tableB 4 ) 5 )a;
7，自定义排序
我们知道，order by 关键字可以实现数据的排序，添加 asc 或 desc 可以决定正序还是倒序，但这种排序方式是基于 ASCⅡ 或日期进行的，实际的业务需求可能比这复杂的多，这时候我们往往需要自定义排序，MySQL 的 field() 函数可以实现。
1 select * from tableA 2 order by field(col_name,'val1','val2','val3',...)
上述例子实现了 col_name 列按照指定的值顺序展示数据，需要注意的是，用来指定顺序的值应该是指定列中存在的值。
那些没有被指定顺序的值将在后面按默认顺序展示，如果还需要根据其他列排序，通过逗号分隔即可。
二 函数
1，字符函数
| 函数 | 功能 | 例子 | 说明 |
| concat() | 连接多个字符串 | concat('a','b') | 连接‘a’和‘b’，返回新的字符串‘ab’ |
| substr() | 截取字符串 | substr('abcde',1,3) | 在字符串‘abcde’中从第1个字符开始截取3个字符，并返回 |
| lower()/upper() | 字符串大小写转换 | lower('abD') | 把字符串‘abD’都转换成小写，并返回 |
| replace() | 替换指定字符 | replace('abcda','a','x') | 把字符串‘abcda’中的‘a’都替换成‘x’，并返回新的字符串 |
| length() | 计算字符串长度 | length('abcde') | 返回字符串‘abcde’的长度 |
| trim() | 去掉前后空格 | trim(' abc ') | 返回去掉了前后空格的新字符串 |
| lpad()/rpad() | 左右填充指定字符 | lpad('abc',10,'-') | 用‘-’填充字符串‘abc’至长度为10 |
| instr() | 子串第一次出现的索引 | instr('abcde','c') | 返回在‘abcde’中第一次出现‘c’的索引 |
2，数学函数
| 函数 | 功能 | 例子 | 说明 |
| ceil() | 向上取整 | ceil(1.1) | 2 |
| floor() | 向下取整 | ceil(1.9) | 1 |
| round() | 四舍五入 | round(2.5) | 3 |
| mod() | 取模 | mod(21,4) | 1 |
| truncate() | 截断小数 | truncate(1.258,2) | 1.25（多余的小数位直接截断） |
| rand() | 返回随机数 | rand() | 随机数[0,1) |
| format() | 截断位数 | format(1.258,2) | 1.26（最后一位四舍五入得来) |
3，日期函数
| 函数 | 功能 | 例子 | 说明 |
| now() | 返回当前日期时间 | now() | 2020-05-15 16:27:59 |
| year()/month()/day() | 返回年/月/日 | year(now()) | 2020 |
| hour()/minute()/secend() | 返回时/分/秒 | hour(now()) | 16 |
| curtime() | 返回时间部分 | curtime(now()) | 16:27:59 |
| curdate() | 返回日期部分 | curdate(now()) | 2020-05-15 |
| date_format() | 格式化日期 | date_format(now(),'%Y/%m/%d') | 2020/05/15 |
| str_to_date() | 把字符串表示的日期转换成日期格式 | str_to_date('2020-05-15 00:00:00','%Y%m%d') | 2020/05/15 |
| datediff() | 返回两个日期之间的天数差 | datediff(now(),'2020-01-01') | 135 |
4，聚合函数
| 函数 | 功能 | 例子 | 说明 |
| max() | 返回最大值 | max(列名) | 返回一列的最大值 |
| min() | 返回最小值 | min(列名) | 返回一列的最小值 |
| avg() | 返回平均值 | avg(列名) | 返回一列的平均值 |
| sum() | 返回和 | sum(列名) | 返回一列的和 |
| count() | 返回数量 | count(列名) | 返回一列的数量（不计算NULL） |
5，其他函数
A：IFNULL()
1 /*MySQL*/ 2 select IFNULL(NULL,1) 3 /*如果第一个参数是 NULL ，则返回第二个参数，否则返回第一个*/ 4 /*MsSQL*/ 5 select ISNULL(NULL,1) 6 /*如果第一个参数是 NULL，则返回第二个参数，否则原样返回*/
B：IF()
1 IF(expr1,expr2,expr3) 2 /*如果 expr1 的值为 true，则返回 expr2 的值，否则返回 expr3 的值。*/
C：其他
1 SELECT DATABASE() 2 SELECT VERSION() 3 SELECT USER() 4 /* 分别返回当前连接数据库，当前数据库版本，当前用户 */
三 编程对象
1，视图
A：创建
1 use world; 2 create view v_1 3 as 4 select * from country limit 10; 5 /* mysql8.0 自带一个 world 库*/
B：删除
1 drop view v_name;
C：修改
1 /*方式一：*/ 2 alter view v_name 3 as 4 语句; 5 /*方式二：*/ 6 create or replace view v_name 7 as 8 语句;
2，变量
MySQL 中变量分为 4 种：系统变量，会话变量，用户变量，局部变量。
A：系统变量
1 SHOW GLOBAL VARIABLES; 2 /* 查看系统变量。当服务启动时，它将所有系统变量初始化为默认值。要想更改全局变量，必须具有super权限。 */
B：会话变量
1 SHOW SESSION VARIABLES; 2 /* 查看会话变量，只作用于当前连接，查看指定系统或会话变量使用：select @@+变量名 */
C：用户变量
1 set @name='张三'; 2 set @age:=12; 3 select @sex='男'; 4 select @var:=max(col) from table 5 /* 用户变量直接赋值即可，使用 select 赋值时只能使用 :=，这是为了让系统避免混淆赋值和等于操作，推荐都使用 := */
D：局部变量
1 1 declare age int default 0; 2 2 set age:=12; 3 3 select @age:=18; 4 4 select max(col) into age from table; 5 5 /* declare 语句专门用于定义局部变量，可以使用 default 来指定默认值，直接赋值时，使用 set，则不需要用 @ 标记变量，使用 select 则需要。如果使用查询赋值，则需要使用 select into.
局部变量只能在 BENGIN...END 语句块中使用，比如下面将要讲到的存储过程或函数中。如果如上例中那样直接运行将报错。
3，存储过程
A：创建
1 create procedure proc_name(参数列表) 2 begin 3 语句块; 4 end
参数包含三个部分：参数模式，参数名，参数类型。
参数模式也分为三种：IN（仅用来像存储过程输入），OUT（仅用来像存储过程外部输出），INOUT（即可以用来输入也可以用来输出）。
存储过程也可以没有参数，括号内留空即可。
B：调用
1 call proc_name(实参列表);
1 DELIMITER $ 2 CREATE PROCEDURE p_1(IN cname VARCHAR(20),OUT cregion VARCHAR(20)) 3 BEGIN 4 SELECT region INTO cregion FROM country 5 WHERE country.name=cname; 6 END$ 7 /*修改默认结束符后，这种语法只能在命令行模式使用，图形化界面会报错（SQLyog，navicat等） 8 这里使用的依然是 MySQL8.0 自带的 world 库*/
9 CALL('Aruba',@cregion);
10 SELECT @cregion;
C：删除
1 drop procedure proc_name;
存储过程是一组预编译的 SQL 语句，使用存储过程，可以提高代码的重用性，简化操作，因为已经预编译好了 SQL 代码，执行时减少了预编译环节，可以提高执行效率，一般用来对表数据进行增删改。
4，自定义函数
A：创建
1 DELIMITER $ 2 CREATE FUNCTION func_name(参数列表) 3 RETURNS 返回类型 4 BEGIN 5 函数体 6 END$
函数的参数和存储过程不同，只需要指定参数名和参数类型。并且单独使用关键字 RETURNS 指定返回值类型。由于函数必须返回值，所以函数内部必须使用 RETURN 关键字。RETURN 建议放在函数末尾，因为它会阻断后面语句的执行。
1 delimiter $ 2 create function f_1(num1 float,num2 float) 3 returns float 4 begin 5 declare sum float default 0; 6 set sum:=num1+num2; 7 return sum; 8 end$ 9 select f_1(1.1,2.2)$
MySQL 不支持存储过程和函数的修改，如果需要修改，必须删除原来的，并新建同名的过程名或函数名。
1 create percedure|function if not exists name 2 ... 3 /* 创建之前先判断是否已存在，免得报错，删除时也应该检测是否不存在 */
5，事务
MySQL 中事务分为两类：显示事务和隐式事务。
隐式事务不用显式的指定开始和结束标记。比如 INSERT、UPDATE、DELETE语句都数据隐式事务，他们不需要指定开始和结束，执行就开启并自动提交。
显式事务是指有显式指定开始和结束标记的事务，这类事务一般由用户创建。由于系统默认是开启了事务自动提交功能的，所以在创建显式事务时我们必须先手动的关闭自动提交功能。
1 set autocommit=0; 2 select @@autocommit; 3 /*0为关闭，1为开启，该设置只在本次会话生效*/
语法：
1 start transaction; 2 /*开启显式事务*/ 3 语句1； 4 savepoint point_name; 5 /*设置事务断点，不是必须的*/ 6 语句2； 7 ... 8 rollback to point_name; 9 /*回滚到指定断点，该断点之前的将不会被回滚*/ 10 commit; 11 /*提交事务，所有的操作被提交到数据库，源数据被修改*/
如果没有设置事务断点，那么回滚将对所有事务中的语句生效，当然 rollback 后也不需要指定断点名称了。提交和回滚不一定会同时存在。
事务一般是结合应用程序来使用的，直接在 MySQL 中无法测试。比如，在应用程序中，用户输入的信息要被提交到数据库，当用户输入完毕后，点击提交，我们可以设置等待时间，并提示用户是否确认提交。如果在等待时间结束之后用户无任何操作，或用户点击确认，那么我们直接提交事务，如果用户点击了取消，那么我们可以回滚事务。
需要注意的是，MySQL 默认配置了许多不同的存储引擎（又称表类型），不同的存储引擎使用了不同的存储技术，它们在插入、读取、修改、事务等方面各有优劣，你可以为不同的表或库配置不同的存储引擎。存储引擎不在本文的讲解范围，这里专门提到它是想指明另一个问题：并不是所有的 MySQL 存储引擎都支持事务，只有 InnoDB 引擎支持。所以，如果你希望在 MySQL 中使用事务，请为其配置 InnoDB 存储引擎。
四 流程控制
1，选择
A：CASE 结构
和其他编程语言一样，MySQL 中的 case 语句也是用来实现多分支结构的。不同的是，MySQL 中的 case 不仅可以用来做等值判断，还可以用来实现类似多重 IF 的逻辑。
等值判断：
1 case 表达式或字段 2 when 用来比较的值1 then 返回的值1或语句1; 3 when 用来比较的值2 then 返回的值2或语句2; 4 ... 5 else 返回的值n或语句n; 6 end case; 7 /* case 后面直接跟需要被做等值判断的表达式或表字段 */
区间判断：
1 case 2 when 判断条件1 then 返回的值1或语句1; 3 when 判断条件2 then 返回的值2或语句2; 4 ... 5 else 返回的值n或语句n; 6 end case; 7 /* case 后面不跟任何值或表达式，在 when 后通过条件表达式来判断所属情况 */
如果把 case 结构作为表达式，那么它可以被嵌套在其他语句中（then 后不需要分号），用在任何地方。如果作为独立的语句使用，那么必须在 begin...end 中。
B：IF 结构
IF 结构主要用来实现多重分支的结构，IF 结构只能包含在 BEGIN...END 中使用，不能作为独表达式使用，如果想作为表达式，可以使用 IF() 函数，但它只能实现两个分支。
1 if 判断条件1 2 then 语句1; 3 elseif 判断条件2 4 then 语句2; 5 ... 6 else7 then 语句n; 8 end if; 9 /* 与其他编程语言不同，这里的 elseif 中间没有空格 */
2，循环
MySQL 中的循环控制语句有两个：iterate（结束本次循环，执行下一次循环，类似其他语言中的 continue），leave（结束整个循环，类似 break）。
另外，循环控制语句必须指明当前退出的是哪个循环。所以，如果你要使用它们精准控制循环执行，那么你需要在循环语句最开始指明当前循环的别称。当然，这不是必须的。
A：while 循环
1 [循环名称:]while 循环条件 2 do 3 循环体; 4 end while [循环名称]; 5 /* 通过判断循环条件来决定是否执行循环体 */
B：repeat 循环
1 [循环名称:]repeat 2 循环体; 3 until 循环条件 4 end repeat [循环名称]; 5 /* 先执行一次循环，再检查循环条件 */
C：loop 循环
1 [循环名称:]loop 2 循环条件; 3 end loop [循环名称]; 4 /* 没有循环条件，只能通过循环控制语句结束 */
MySQL 中的循环语句都只能包含在 begin...end 中使用，所以一般在存储过程和函数中使用。
