MySQL常用命令

soulballad约 7821 字大约 26 分钟

声明：本文档以 MySQL 5.7 版本为例，MySQL其他版本或其他数据库不完全适用。

MySQL命令

1. 基础知识

1.1 常用命令

作用	命令	样例
mysql登录命令	mysql -h ip -P 端口 -u 用户名 -p	mysql -h localhost -P 3306 -u root -p
查看数据库版本(未登录)	mysql --version 或 mysql -V	mysql -V
查看数据库版本(已登录)	select version()	select version()
显示所有数据库	show databases;	show databases;
进入指定的库	use 库名;	use seata;
显示当前库中所有的表	show tables;	show tables;
查看其他库中所有的表	show tables from 库名	show tables from seata;
查看表的创建语句	show create table 表名	show create table seata;
查看表结构	desc 表名;	desc seata;
查看当前所在库	select database();	select database();
查看当前mysql支持的存储引擎	show engines;	show engines;
查看系统变量及其值	show variables;	show variables;
查看某个系统变量	show variables like ‘变量名’	show variables like 'wait_timeout';

1.2 数据类型

1.2.1 整数类型

类型	字节数	有符号值范围	无符号值范围
`tinyint[(n)] [unsigned]`	1	[-2^7,2^7-1]	[0,2^8-1]
`smallint[(n)] [unsigned]`	2	[-2^15,2^15-1]	[0,2^16-1]
`mediumint[(n)] [unsigned]`	3	[-2^23,2^23-1]	[0,2^24-1]
`int[(n)] [unsigned]`	4	[-2^31,2^31-1]	[0,2^32-1]
`bigint[(n)] [unsigned]`	8	[-2^63,2^63-1]	[0,2^64-1]

上面[]包含的内容是可选的，默认是有符号类型的，无符号的需要在类型后面跟上unsigned

1.2.2 浮点类型

类型	字节大小	范围（有符号）	范围（无符号）	用途
float[(m,d)]	4	(-3.402823466E+38,<br/>3.402823466351E+38)	[0,3.402823466E+38)	单精度浮点数值
double[(m,d)]	8	(-1.7976931348623157E+308,<br/>1.7976931348623157E+308)	[0,1.797693134862315 7E+308)	双精度浮点数值
decimal[(m,d)]	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

float数值类型用于表示单精度浮点数值，而double数值类型用于表示双精度浮点数值，float和double都是浮点型，而decimal是定点型。

浮点型和定点型可以用类型名称后加（M，D）来表示，M表示该值的总共长度，D表示小数点后面的长度，M和D又称为精度和标度。

float和double在不指定精度时，默认会按照实际的精度来显示，而DECIMAL在不指定精度时，默认整数为10，小数为0。

1.2.3 日期类型

类型	字节大小	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	‘-838:59:59’/‘838:59:59’	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

1.2.4 字符串类型

类型	范围	存储所需字节	说明
char(M)	[0,m]，m的范围[0,2^8-1]	m	定产字符串
varchar(M)	[0,m]，m的范围[0,2^16-1]	m	0-65535 字节
tinyblob	0-255(2^8-1)字节	L+1	不超过 255 个字符的二进制字符串
blob	0-65535(2^16-1)字节	L+2	二进制形式的长文本数据
mediumblob	0-16777215(2^24-1)字节	L+3	二进制形式的中等长度文本数据
longblob	0-4294967295(2^32-1)字节	L+4	二进制形式的极大文本数据
tinytext	0-255(2^8-1)字节	L+1	短文本字符串
text	0-65535(2^16-1)字节	L+2	长文本数据
mediumtext	0-16777215(2^24-1)字节	L+3	中等长度文本数据
longtext	0-4294967295(2^32-1)字节	L+4	极大文本数据

char类型占用固定长度，如果存放的数据为固定长度的建议使用char类型，如：手机号码、身份证等固定长度的信息。

表格中的L表示存储的数据本身占用的字节，L 以外所需的额外字节为存放该值的长度所需的字节数。

MySQL 通过存储值的内容及其长度来处理可变长度的值，这些额外的字节是无符号整数。

请注意，可变长类型的最大长度、此类型所需的额外字节数以及占用相同字节数的无符号整数之间的对应关系：

1.2.5 数据类型选择的一些建议

选小不选大：一般情况下选择可以正确存储数据的最小数据类型，越小的数据类型通常更快，占用磁盘，内存和CPU缓存更小。
简单就好：简单的数据类型的操作通常需要更少的CPU周期，例如：整型比字符操作代价要小得多，因为字符集和校对规则(排序规则)使字符比整型比较更加复杂。
尽量避免NULL：尽量制定列为NOT NULL，除非真的需要NULL类型的值，有NULL的列值会使得索引、索引统计和值比较更加复杂。
浮点类型的建议统一选择decimal
记录时间的建议使用int或者bigint类型，将时间转换为时间戳格式，如将时间转换为秒、毫秒，进行存储，方便走索引

1.3 用户及权限管理

作用	命令	说明
查看mysql中所有用户	select user	使用数据查询
创建用户	create user	无密码的用户，没有指定主机 create user test1
		此用户只能登陆本机的mysql create user 'test2'@'localhost' identified by '123';
		test3可以从任何机器连接到mysql服务器 create user
		test4可以从192.168.11段的机器连接mysql create user 'test4'@'192.168.11.%' identified by '123';
修改密码	SET PASSWORD FOR	通过管理员修改密码
	create user 用户名[@主机名] [identified by ‘密码’]; set password = password('密码');	创建用户后修改
	use mysql; update user set authentication_string = password('321') where user = 'test1' and host = '%'; flush privileges;	通过修改mysql.user表修改密码
给用户授权	grant privileges ON database	给test1授权可以操作所有库所有权限，相当于dba grant all on . to 'test1'@‘%’;
		test1可以对seata库中所有的表执行select grant select on seata.* to 'test1'@'%';
		test1可以对seata库中所有的表执行select、update grant select,update on seata.* to 'test1'@'%';
		test1用户只能查询mysql.user表的user,host字段 grant select(user,host) on mysql.user to 'test1'@'localhost';
查看用户有哪些权限	show grants for ‘用户名’[@’主机’]	show grants
撤销用户的权限	revoke privileges ON database	revoke select
删除用户	drop user ‘用户名’[@‘主机’]	drop的方式删除用户之后，用户下次登录就会起效。 drop user test1@localhost;
删除用户	delete from user where user='用户名' and host='主机'; flush privileges;	通过删除mysql.user表数据的方式删除

1.4 DDL常见操作

作用	命令	样例
创建库	create database [if not exists] 库名;	create database seata;
删除库	drop databases [if exists] 库名;	drop database if exists seata;

创建表	create table 表名( 字段名1 类型[(宽度)] [约束条件] [comment '字段说明'], 字段名2 类型[(宽度)] [约束条件] [comment '字段说明'], 字段名3 类型[(宽度)] [约束条件] [comment '字段说明'] )[表的一些设置];	create table test( a int not null AUTO_INCREMENT PRIMARY KEY comment '字段a', b int not null comment '字段b', unique key(b) );
删除表	drop table [if exists] 表名;
修改表名	alter table 表名 rename [to] 新表名;
表设置备注	alter table 表名 comment '备注信息';
复制表结构	create table 表名 like 被复制的表名;	create table test2 like test;
复制表结构+数据	create table 表名 [as] select 字段,... from 被复制的表 [where 条件];	create table test3 as select * from test;

添加列	alter table 表名 add column 列名类型 [列约束];	alter table test add column c int not null default 0 comment '字段c';
modify修改列	alter table 表名 modify column 列名新类型 [约束];	modify不能修改列名，change可以修改列名
change修改列	alter table 表名 change column 列名新列名新类型 [约束];	alter table test change column c d varchar(10) not null default '' comment '字段d';
删除列	alter table 表名 drop column 列名;	alter table test drop column d;

1.5 DML常见操作

作用	命令	样例
插入操作
单条插入方式1	insert into 表名[(字段,字段)] values (值,值);	值和字段需要一一对应
单条插入方式2	insert into 表名 set 字段 = 值,字段 = 值;	不常见
批量插入方式1	insert into 表名 [(字段,字段)] values (值,值),(值,值),(值,值);	insert into test2 values (100,101,102),(200,201,202),(300,301,302),(400,401,402);
批量插入方式2	insert into 表1 [(字段,字段)] select语句;	向test1中插入数据 insert into test1 (a,b) select 1,1 union all select 2,2 union all select 2,2; 向test1插入数据，数据来源于test2表 insert into test1 (a,b) select c2,c3 from test2 where c1>=200;
数据更新
单表更新	update 表名 [[as] 别名] set [别名.]字段 = 值,[别名.]字段 = 值 [where条件];	update test1 as t set t.a = 3;
多表更新	update 表1 [[as] 别名1], 表名2 [[as] 别名2] set [别名.]字段 = 值,[别名.]字段 = 值 [where条件]	update test1 t1,test2 t2 set t1.a = 2 ,t1.b = 2, t2.c1 = 10 where t1.a = t2.c1;
删除数据操作
delete单表删除	delete [别名] from 表名 [[as] 别名] [where条件];	delete t1 from test1 t1 where t1.a>100;
多表删除	delete [别名1,别名2] from 表1 [[as] 别名1],表2 [[as] 别名2] [where条件];	delete t1 from test1 t1,test2 t2 where t1.a=t2.c2; delete t2,t1 from test1 t1,test2 t2 where t1.a=t2.c2;
truncate删除	truncate 表名;

1.6 NULL字段的坑

NULL作为布尔值的时候，不为1也不为0
任何值和NULL使用运算符（>、<、>=、<=、!=、<>）或者（in、not in、any/some、all），返回值都为NULL
当IN和NULL比较时，无法查询出为NULL的记录
当NOT IN 后面有NULL值时，不论什么情况下，整个sql的查询结果都为空
判断是否为空只能用IS NULL、IS NOT NULL
count(字段)无法统计字段为NULL的值，count(*)可以统计值为null的行
当字段为主键的时候，字段会自动设置为not null
NULL导致的坑让人防不胜防，强烈建议创建字段的时候字段不允许为NULL，给个默认值

1.7 变量

2. select查询

2.1 基础查询

作用	命令	样例
查询常量	select 常量值1,常量值2,常量值3;	select 1,'b';
查询表达式	select 表达式;	select 1+2,3*10,10/3;
查询函数	select 函数;	select mod(10,4),isnull(null),ifnull(null,'参数为空时返回值');
查询指定的字段	select 字段1,字段2,字段3 from 表名;	select a,b from test1;
查询所有列	select * from 表名;	select * from test1;
列别名	select 列 [as] 别名 from 表;	select a "列1",b "列2" from test1;
表别名	select 别名.字段,别名.* from 表名 [as] 别名;	select t.a,t.b from test1 as t;

2.2 条件/排序/分页/分组/关联

作用	命令	样例
条件查询	select 列名 from 表名 where 列运算符值	select 列名 from 表名 where 列 = 值;
排序查询	select 字段名 from 表名 order by 字段1 [asc\|desc],字段2 [asc\|desc];	SELECT id 编号,birth 出生日期,year(birth) 出生年份,name 姓名 from student ORDER BY year(birth) asc,id asc;
分页查询	select 列 from 表 limit [offset,] count;	select * from test1 order by b asc,a desc limit 6,2;
分组查询	SELECT column, group_function,... FROM table [WHERE condition] GROUP BY group_by_expression [HAVING group_condition];	SELECT user_id uid, COUNT(id) ucount FROM t_order t WHERE t.the_year = 2018 GROUP BY user_id HAVING count(id)>=2 ORDER BY ucount DESC LIMIT 1;
内连接	select 字段 from 表1 inner join 表2 on 连接条件;	select t1.emp_name,t2.team_name from t_employee t1 inner join t_team t2 on t1.team_id = t2.id;
	select 字段 from 表1 join 表2 on 连接条件;	select t1.emp_name,t2.team_name from t_employee t1 join t_team t2 on t1.team_id = t2.id;
	select 字段 from 表1, 表2 [where 关联条件];	select t1.emp_name,t2.team_name from t_employee t1, t_team t2 where t1.team_id = t2.id;
左连接	select 列 from 主表 left join 从表 on 连接条件;	select t1.emp_name, t2.team_name from t_employee t1 left join t_team t2 on t1.team_id = t2.id;
右连接	select 列 from 从表 right join 主表 on 连接条件;	select t2.team_name, t1.emp_name from t_team t2 right join t_employee t1 on t1.team_id = t2.id;

条件查询注意事项

like中的%可以匹配一个到多个任意的字符，_可以匹配任意一个字符
空值查询需要使用IS NULL或者IS NOT NULL，其他查询运算符对NULL值无效
建议创建表的时候，尽量设置表的字段不能为空，给字段设置一个默认值
<=>（安全等于）玩玩可以，建议少使用

2.3 常用函数

2.3.1 数值型函数

函数名称	作用
abs	求绝对值
sqrt	求二次方根
mod	求余数
ceil 和 ceiling	两个函数功能相同，都是返回不小于参数的最小整数，即向上取整
floor	向下取整，返回值转化为一个BIGINT
rand	生成一个0~1之间的随机数，传入整数参数是，用来产生重复序列
round	对所传参数进行四舍五入
sign	返回参数的符号
pow 和 power	两个函数的功能相同，都是所传参数的次方的结果值
sin	求正弦值
asin	求反正弦值，与函数 SIN 互为反函数
cos	求余弦值
acos	求反余弦值，与函数 COS 互为反函数
tan	求正切值
atan	求反正切值，与函数 TAN 互为反函数
cot	求余切值

2.3.2 字符串函数

函数名称	作用
length	计算字符串长度函数，返回字符串的字节长度
concat	合并字符串函数，返回结果为连接参数产生的字符串，参数可以使一个或多个
insert	替换字符串函数
lower	将字符串中的字母转换为小写
upper	将字符串中的字母转换为大写
left	从左侧字截取符串，返回字符串左边的若干个字符
right	从右侧字截取符串，返回字符串右边的若干个字符
trim	删除字符串左右两侧的空格
replace	字符串替换函数，返回替换后的新字符串
substr 和 substring	截取字符串，返回从指定位置开始的指定长度的字符换
reverse	字符串反转（逆序）函数，返回与原始字符串顺序相反的字符串

2.3.3 日期和时间函数

函数名称	作用
curdate 和 current_date	两个函数作用相同，返回当前系统的日期值
curtime 和 current_time	两个函数作用相同，返回当前系统的时间值
now 和 sysdate	两个函数作用相同，返回当前系统的日期和时间值
unix_timestamp	获取UNIX时间戳函数，返回一个以 UNIX 时间戳为基础的无符号整数
from_unixtime	将 UNIX 时间戳转换为时间格式，与UNIX_TIMESTAMP互为反函数
month	获取指定日期中的月份
monthname	获取指定日期中的月份英文名称
dayname	获取指定曰期对应的星期几的英文名称
dayofweek	获取指定日期是一周中是第几天，返回值范围是1~7,1=周日
week	获取指定日期是一年中的第几周，返回值的范围是否为 0〜52 或 1〜53
dayofyear	获取指定曰期是一年中的第几天，返回值范围是1~366
dayofmonth	获取指定日期是一个月中是第几天，返回值范围是1~31
year	获取年份，返回值范围是 1970〜2069
time_to_sec	将时间参数转换为秒数
sec_to_time	将秒数转换为时间，与TIME_TO_SEC 互为反函数
date_add 和 adddate	两个函数功能相同，都是向日期添加指定的时间间隔
date_sub 和 subdate	两个函数功能相同，都是向日期减去指定的时间间隔
addtime	时间加法运算，在原始时间上添加指定的时间
subtime	时间减法运算，在原始时间上减去指定的时间
datediff	获取两个日期之间间隔，返回参数 1 减去参数 2 的值
date_format	格式化指定的日期，根据参数返回指定格式的值
weekday	获取指定日期在一周内的对应的工作日索引

2.3.4 聚合函数

函数名称	作用
max	查询指定列的最大值
min	查询指定列的最小值
count	统计查询结果的行数
sum	求和，返回指定列的总和
avg	求平均值，返回指定列数据的平均值

2.3.5 流程控制函数

函数名称	作用
if	判断，流程控制
ifnull	判断是否为空
case	搜索语句

2.3.6 其他函数

函数名称	作用
version	数据库版本号
database	当前的数据库
user	当前连接用户
password	返回字符串密码形式
md5	返回字符串的md5数据

2.4 子查询

2.4.1 select后面的子查询

SELECT
  a.*,
  (SELECT count(*)
   FROM employees b
   WHERE b.department_id = a.department_id) AS 员工个数
FROM departments a;

2.4.2 from后面的子查询

-- 将上面2个结果连接查询，筛选条件:平均工资 between lowest_sal and highest_sal;
SELECT
  t1.department_id,
  sa AS '平均工资',
  t2.grade_level
FROM (SELECT
        department_id,
        avg(a.salary) sa
      FROM employees a
      GROUP BY a.department_id) t1, job_grades t2
WHERE
  t1.sa BETWEEN t2.lowest_sal AND t2.highest_sal;

2.4.3 where和having后面的子查询

一般子查询

/*返回job_id与141号员工相同，salary比143号员工多的员工、姓名、job_id和工资*/
SELECT
  a.last_name 姓名,
  a.job_id,
  a.salary    工资
FROM employees a
WHERE a.job_id = (SELECT job_id
                  FROM employees
                  WHERE employee_id = 141)
      AND
      a.salary > (SELECT salary
                  FROM employees
                  WHERE employee_id = 143);

子查询+分组函数

/*查询最低工资大于50号部门最低工资的部门id和其最低工资【having】*/
SELECT
  min(a.salary) minsalary,
  department_id
FROM employees a
GROUP BY a.department_id
HAVING min(a.salary) > (SELECT min(salary)
                        FROM employees
                        WHERE department_id = 50);

列子查询(子查询结果集一列多行)

/*返回location_id是1400或1700的部门中的所有员工姓名*/
/*②查询员工姓名，要求部门是①列表中的某一个*/
SELECT a.last_name
FROM employees a
WHERE a.department_id IN (SELECT DISTINCT department_id
                          FROM departments
                          WHERE location_id IN (1400, 1700));
/*方式2：使用any实现*/
SELECT a.last_name
FROM employees a
WHERE a.department_id = ANY (SELECT DISTINCT department_id
                             FROM departments
                             WHERE location_id IN (1400, 1700));
/*拓展，下面与not in等价*/
SELECT a.last_name
FROM employees a
WHERE a.department_id <> ALL (SELECT DISTINCT department_id
                             FROM departments
                             WHERE location_id IN (1400, 1700));

行子查询（子查询结果集一行多列）

/*查询员工编号最小并且工资最高的员工信息，3种方式。*/
/*③方式1：查询员工信息*/
SELECT *
FROM employees a
WHERE a.employee_id = (SELECT min(employee_id)
                       FROM employees)
      AND salary = (SELECT max(salary)
                    FROM employees);
/*方式2*/
SELECT *
FROM employees a
WHERE (a.employee_id, a.salary) = (SELECT
                                     min(employee_id),
                                     max(salary)
                                   FROM employees);
/*方式3*/
SELECT *
FROM employees a
WHERE (a.employee_id, a.salary) in (SELECT
                                     min(employee_id),
                                     max(salary)
                                   FROM employees);

exists查询（也叫做相关子查询）

/*查询没有员工的部门*/
/*exists入门案例*/
SELECT exists(SELECT employee_id
              FROM employees
              WHERE salary = 300000) AS 'exists返回1或者0';
/*查询所有员工部门名*/
SELECT department_name
FROM departments a
WHERE exists(SELECT 1
             FROM employees b
             WHERE a.department_id = b.department_id);
/*使用in实现*/
SELECT department_name
FROM departments a
WHERE a.department_id IN (SELECT department_id
                          FROM employees);

NULL的大坑

/*使用not in的方式查询没有员工的部门，如下：*/
SELECT *
FROM departments a
WHERE a.department_id NOT IN (SELECT department_id
                              FROM employees b);

not in的情况下，子查询中列的值为NULL的时候，外查询的结果为空。

建议：建表是，列不允许为空。

3. 存储过程和自定义函数

3.1 存储过程

一组预编译好的sql语句集合，理解成批处理语句。

好处：

提高代码的重用性
简化操作
减少编译次数并且减少和数据库服务器连接的次数，提高了效率。

3.1.1 创建存储过程

create procedure 存储过程名([参数模式] 参数名 参数类型)
begin    
	存储过程体
end

参数模式有3种：
in：该参数可以作为输入，也就是该参数需要调用方传入值。
out：该参数可以作为输出，也就是说该参数可以作为返回值。
inout：该参数既可以作为输入也可以作为输出，也就是说该参数需要在调用的时候传入值，又可以作为返回值。
参数模式默认为IN。
一个存储过程可以有多个输入、多个输出、多个输入输出参数。

3.1.2 调用存储过程

call 存储过程名称(参数列表);

注意：调用存储过程关键字是call。

3.1.3 删除存储过程

drop procedure [if exists] 存储过程名称;

存储过程只能一个个删除，不能批量删除。
if exists：表示存储过程存在的情况下删除。

3.1.4 修改存储过程

存储过程不能修改，若涉及到修改的，可以先删除，然后重建。

3.1.5 查看存储过程

show create procedure 存储过程名称;

可以查看存储过程详细创建语句。

3.1.6 示例

创建存储过程：

/*如果存储过程存在则删除*/
DROP PROCEDURE IF EXISTS proc3;
/*设置结束符为$*/
DELIMITER $
/*创建存储过程proc3*/
CREATE PROCEDURE proc3(id int,age int,in name varchar(16),out user_count int,out max_id INT)
  BEGIN
    INSERT INTO t_user VALUES (id,age,name);
    /*查询出t_user表的记录，放入user_count中,max_id用来存储t_user中最小的id*/
    SELECT COUNT(*),max(id) into user_count,max_id from t_user;
  END $
/*将结束符置为;*/
DELIMITER ;

调用存储过程：

/*创建了3个自定义变量*/
SELECT @id:=4,@age:=55,@name:='郭富城';
/*调用存储过程*/
CALL proc3(@id,@age,@name,@user_count,@max_id);

3.2 自定义函数

一组预编译好的sql语句集合，理解成批处理语句。类似于java中的方法，但是必须有返回值

3.2.1 创建函数

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

参数是可选的。
返回值是必须的。

3.2.2 调用函数

select 函数名(实参列表);

3.2.3 删除函数

drop function [if exists] 函数名;

3.2.4 查看函数详细

show create function 函数名;

3.2.5 示例

创建函数：

/*删除函数*/
DROP FUNCTION IF EXISTS get_user_id;
/*设置结束符为$*/
DELIMITER $
/*创建函数*/
CREATE FUNCTION get_user_id(v_name VARCHAR(16))
  returns INT
  BEGIN
    DECLARE r_id int;
    SELECT id INTO r_id FROM t_user WHERE name = v_name;
    return r_id;
  END $
/*设置结束符为;*/
DELIMITER ;

运行看效果：

mysql> SELECT get_user_id(name) from t_user;
+-------------------+
| get_user_id(name) |
+-------------------+
|                 1 |
|                 2 |
|                 3 |
|                 4 |
+-------------------+
4 rows in set (0.00 sec)

3.3 存储过程和函数的区别

存储过程的关键字为procedure，返回值可以有多个，调用时用call，一般用于执行比较复杂的的过程体、更新、创建等语句。

函数的关键字为function，返回值必须有一个，调用用select，一般用于查询单个值并返回。

	存储过程	函数
返回值	可以有0个或者多个	必须有一个
关键字	procedure	function
调用方式	call	select

3.4 游标

3.4.1 游标定义

游标（Cursor）是处理数据的一种方法，为了查看或者处理结果集中的数据，游标提供了在结果集中一次一行遍历数据的能力。

游标只能在存储过程和函数中使用。

3.4.2 游标的作用

如sql：

select a,b from test1;

上面这个查询返回了test1中的数据，如果我们想对这些数据进行遍历处理，此时我们就可以使用游标来进行操作。

游标相当于一个指针，这个指针指向select的第一行数据，可以通过移动指针来遍历后面的数据。

3.4.3 游标的使用步骤

声明游标：这个过程只是创建了一个游标，需要指定这个游标需要遍历的select查询，声明游标时并不会去执行这个sql。

打开游标：打开游标的时候，会执行游标对应的select语句。

遍历数据：使用游标循环遍历select结果中每一行数据，然后进行处理。

关闭游标：游标使用完之后一定要关闭。

3.4.4 游标语法

3.4.4.1 声明游标

DECLARE 游标名称 CURSOR FOR 查询语句;

一个begin end中只能声明一个游标。

3.4.4.2 打开游标

open 游标名称;

3.4.4.3 遍历游标

fetch 游标名称 into 变量列表;

取出当前行的结果，将结果放在对应的变量中，并将游标指针指向下一行的数据。
当调用fetch的时候，会获取当前行的数据，如果当前行无数据，会引发mysql内部的NOT FOUND错误。

3.4.4.4 关闭游标

close 游标名称;

游标使用完毕之后一定要关闭。

3.4.5 单游标示例

写一个函数，计算test1表中a、b字段所有的和。

创建函数：

/*删除函数*/
DROP FUNCTION IF EXISTS fun1;
/*声明结束符为$*/
DELIMITER $
/*创建函数*/
CREATE FUNCTION fun1(v_max_a int)
  RETURNS int
  BEGIN
    /*用于保存结果*/
    DECLARE v_total int DEFAULT 0;
    /*创建一个变量，用来保存当前行中a的值*/
    DECLARE v_a int DEFAULT 0;
    /*创建一个变量，用来保存当前行中b的值*/
    DECLARE v_b int DEFAULT 0;
    /*创建游标结束标志变量*/
    DECLARE v_done int DEFAULT FALSE;
    /*创建游标*/
    DECLARE cur_test1 CURSOR FOR SELECT a,b from test1 where a<=v_max_a;
    /*设置游标结束时v_done的值为true，可以v_done来判断游标是否结束了*/
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done=TRUE;
    /*设置v_total初始值*/
    SET v_total = 0;
    /*打开游标*/
    OPEN cur_test1;
    /*使用Loop循环遍历游标*/
    a:LOOP
      /*先获取当前行的数据，然后将当前行的数据放入v_a,v_b中，如果当前行无数据，v_done会被置为true*/
      FETCH cur_test1 INTO v_a, v_b;
      /*通过v_done来判断游标是否结束了，退出循环*/
      if v_done THEN
        LEAVE a;
      END IF;
      /*对v_total值累加处理*/
      SET v_total = v_total + v_a + v_b;
    END LOOP;
    /*关闭游标*/
    CLOSE cur_test1;
    /*返回结果*/
    RETURN v_total;
  END $
/*结束符置为;*/
DELIMITER ;

上面语句执行过程中可能有问题，解决方式如下。
错误信息：Mysql 创建函数出现This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA
mysql的设置默认是不允许创建函数，需要开启对应的配置。

3.4.6 嵌套游标

写个存储过程，遍历test2、test3，将test2中的a字段和test3中的b字段任意组合，插入到test1表中。

创建存储过程：

/*删除存储过程*/
DROP PROCEDURE IF EXISTS proc1;
/*声明结束符为$*/
DELIMITER $
/*创建存储过程*/
CREATE PROCEDURE proc1()
  BEGIN
    /*创建一个变量，用来保存当前行中a的值*/
    DECLARE v_a int DEFAULT 0;
    /*创建游标结束标志变量*/
    DECLARE v_done1 int DEFAULT FALSE;
    /*创建游标*/
    DECLARE cur_test1 CURSOR FOR SELECT a FROM test2;
    /*设置游标结束时v_done1的值为true，可以v_done1来判断游标cur_test1是否结束了*/
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done1=TRUE;
    /*打开游标*/
    OPEN cur_test1;
    /*使用Loop循环遍历游标*/
    a:LOOP
      FETCH cur_test1 INTO v_a;
      /*通过v_done1来判断游标是否结束了，退出循环*/
      if v_done1 THEN
        LEAVE a;
      END IF;
      BEGIN
        /*创建一个变量，用来保存当前行中b的值*/
        DECLARE v_b int DEFAULT 0;
        /*创建游标结束标志变量*/
        DECLARE v_done2 int DEFAULT FALSE;
        /*创建游标*/
        DECLARE cur_test2 CURSOR FOR SELECT b FROM test3;
        /*设置游标结束时v_done1的值为true，可以v_done1来判断游标cur_test2是否结束了*/
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done2=TRUE;
        /*打开游标*/
        OPEN cur_test2;
        /*使用Loop循环遍历游标*/
        b:LOOP
          FETCH cur_test2 INTO v_b;
          /*通过v_done1来判断游标是否结束了，退出循环*/
          if v_done2 THEN
            LEAVE b;
          END IF;
          /*将v_a、v_b插入test1表中*/
          INSERT INTO test1 VALUES (v_a,v_b);
        END LOOP b;
        /*关闭cur_test2游标*/
        CLOSE cur_test2;
      END;
    END LOOP;
    /*关闭游标cur_test1*/
    CLOSE cur_test1;
  END $
/*结束符置为;*/
DELIMITER ;

MySQL常用命令

# MySQL命令

# 1. 基础知识

# 1.1 常用命令

# 1.2 数据类型

# 1.2.1 整数类型

# 1.2.2 浮点类型

# 1.2.3 日期类型

# 1.2.4 字符串类型

# 1.2.5 数据类型选择的一些建议

# 1.3 用户及权限管理

# 1.4 DDL常见操作

# 1.5 DML常见操作

# 1.6 NULL字段的坑

# 1.7 变量

# 2. select查询

# 2.1 基础查询

# 2.2 条件/排序/分页/分组/关联

# 2.3 常用函数

# 2.3.1 数值型函数

# 2.3.2 字符串函数

# 2.3.3 日期和时间函数

# 2.3.4 聚合函数

# 2.3.5 流程控制函数

# 2.3.6 其他函数

# 2.4 子查询

# 2.4.1 select后面的子查询

# 2.4.2 from后面的子查询

# 2.4.3 where和having后面的子查询

# 3. 存储过程和自定义函数

# 3.1 存储过程

# 3.1.1 创建存储过程

# 3.1.2 调用存储过程

# 3.1.3 删除存储过程

# 3.1.4 修改存储过程

# 3.1.5 查看存储过程

# 3.1.6 示例

# 3.2 自定义函数

# 3.2.1 创建函数

# 3.2.2 调用函数

# 3.2.3 删除函数

# 3.2.4 查看函数详细

# 3.2.5 示例

# 3.3 存储过程和函数的区别

# 3.4 游标

# 3.4.1 游标定义

# 3.4.2 游标的作用

# 3.4.3 游标的使用步骤

# 3.4.4 游标语法

# 3.4.4.1 声明游标

# 3.4.4.2 打开游标

# 3.4.4.3 遍历游标

# 3.4.4.4 关闭游标

# 3.4.5 单游标示例

# 3.4.6 嵌套游标