---＞＞＞MySQL常用命令&关键点

查询命令

• 启动和关闭MySQL服务 

net stop mysql
net start mysql

• 查看数据库   

mysql> SHOW DATABASES

• 查看系统所支持的引擎类型

 mysql> show eigines

• 查看数据表结构

（1）mysql> DESCRIBE  table_name 或 mysql> DESC table_name

（2）mysql> SHOW CREATE TABLE table_name

• 查看数据表信息

mysql> SELECT * FROM <表名>;

mysql> SELECT SQL_NO_CACHE * FROM tb1; --- 不使用查询缓冲区

mysql> SELECT <列名1>,<列名2>,...<列名n> FROM <表名>;

• 清理查询缓冲区碎片

mysql> FLUSH QUERY CACHE;

• 条件查询

mysql> SELECT <字段名> FROM <表名> WHERE <查询条件>;
mysql> SELECT <字段名> FROM <表名> WHERE <查询字段> IN <集合范围> ORDER BY <字段名>;  （带IN关键字的查询，并根据字段对结果进行排序）
mysql> SELECT <字段名> FROM <表名> WHERE <字段名> BETWEEN <值1> AND <值2>; （带between...and关键字的查询）
mysql> SELECT <字段名> FROM <表名> WHERE <字段名> LIKE <匹配条件>; （带like关键字的查询）
例  SELECT f_id,f_name FROM fruits WHERE f_name LIKE 'g%' ----查询以g开头
例  SELECT f_id,f_name FROM fruits WHERE f_name LIKE '%g%'  ---- 查询包含g
例  SELECT f_id,f_name FROM fruits WHERE f_name LIKE 'g%y'  ----查询以g开头，y结尾
mysql> SELECT <字段名> FROM <表名> WHERE <字段名> IS NULL; (查询空值)
mysql> SELECT <字段名> FROM <表名> WHERE <字段名> IS NOT NULL; (查询非空值)
mysql> SELECT <字段名> FROM <表名> WHERE <条件1> AND <条件2>;（带and关键字的查询）
mysql> SELECT <字段名> FROM <表名> WHERE <条件1> OR <条件2>; （带or关键字的查询）
SELECT DISTINCT <字段名> FROM <表名>; （查询结果不重复）
mysql> SELECT <字段名> FROM <表名> ORDER BY <字段名>; （根据单列对结果进行排序）
mysql> SELECT <字段名> FROM <表名> ORDER BY <字段名1>,<字段名2> ; （根据多列对结果进行排序，先用字段1，然后再用字段2）
mysql> SELECT <字段名> FROM <表名> ORDER BY <字段名> DESC; （指定降序排序顺序）
mysql> SELECT <字段名> FROM <表名> ORDER BY <字段名1> DESC,<字段名2> ; （指定根据第一列降序，第二列升序的顺序对结果进行排序）

• 分组查询

mysql> SELECT <字段名> FROM <表名> GROUP BY <字段名>;

mysql> SELECT <字段名> FROM <表名> GROUP BY <字段名> HAVING <过滤条件>;
例  mysql> SELECT s_id,GROUP_CONCAT(f_name) AS Names FROM fruits GROUP BY s_id HAVING COUNT(f_name)>1

mysql> SELECT <字段名> FROM <表名> GROUP BY <字段名> WITH ROLLUP; （分组之后计算总和）

mysql> SELECT <字段名> FROM <表名> GROUP BY <字段名1>，<字段名2>;（多字段分组，即先根据字段1分组，字段1相同之后再根据字段2分组）

mysql> select o_num,sum(quantity*item_price) as ordertotal
    -> from orderitems
    -> group by o_num
    -> having sum(quantity*item_price)>=100
    -> order by ordertotal

 mysql> SELECT <字段名> FROM <表名> LIMIT [位置偏移量] 行数

mysql> select * from fruits limit 4;  ------显示fruits表查询结果的前4行

 mysql> select * from fruits limit 4,3;  ------ 显示fruits表查询结果从第5行开始的3行

• 使用聚合函数查询 

 mysql> select count(*) from tmp11;  ----- 显示总行数

mysql> select count(b) from tmp11; ------ 显示含有b的字段的总行数

mysql> select o_num,sum(quantity) as iter_total from order_items group by o_num; --- 根据o_num进行分组，并计算每组的quantity总和

 mysql> select avg(f_price) as avg_price from fruits where s_id=103; ----查询所有s_id为103的组的f_price平均值

mysql> select s_id,avg(f_price) as avg_price from fruits group by s_id;-----根据s_id进行分组，并计算每组的f_price平均值

mysql> select max(f_price) as max_price from fruits; --- 返回表中f_price的最大值

mysql> select max(f_price) ad max_price from fruits group by s_id;-----根据s_id进行分组，并找到每一组的f_price最大值

min函数使用同max函数

• 连接查询

内连接查询

mysql> select suppliers.s_id,s_name,f_name,f_price from fruits,suppliers where fruits.s_id=suppliers.s_id;

等同于

mysql> select suppliers.s_id,s_name,f_name,f_price from fruits INNER JOIN suppliers ON fruits.s_id=suppliers.s_id;

外连接查询

mysql> select suppliers.s_id,s_name,f_name,f_price from fruits LEFT OUTER JOIN suppliers ON fruits.s_id=suppliers.s_id;（以左表为主，如果左表中的某一行在右表中没有匹配，则右表返回NULL）

mysql> select suppliers.s_id,s_name,f_name,f_price from fruits RIGHT OUTER JOIN suppliers ON fruits.s_id=suppliers.s_id;（以右表为主，如果右表中的某一行在左表中没有匹配，则左表返回NULL）

复合条件查询

mysql> select customers.c_id,orders.o_num
    -> from customers inner join orders
    -> on customers.c_id=orders.c_id and customers.c_id=10001;

mysql> select customers.c_id,orders.o_num
    -> from customers inner join orders
    -> on customers.c_id=orders.c_id
    -> order by orders.o_num

• 子查询

带ANY或SOME关键字的子查询（ANY和SOME同义）

mysql> SELECT num1 FROM tbl1 WHERE num1 > ANY(SELECT num2 FROM tbl2);-----只要num1大于num2中的任一值，即符合条件

带ALL 关键字的子查询

mysql> SELECT num1 FROM tbl1 WHERE num1 > ALL(SELECT num2 FROM tbl2);-----num1需要大于num2中的所有值才符合条件

带EXISTS 关键字的子查询

mysql> SELECT * FROM fruits WHERE EXISTS (SELECT s_name FROM suppliers WHERE s_id=107);  ----- 首先查询suppliers表中是否有s_id=107的数据，如果存在，则查询表fruits中的记录

mysql> SELECT * FROM fruits WHERE f_price>10.20 AND EXISTS  (SELECT s_name FROM suppliers WHERE s_id=107);  ----- EXISTS关键字和条件表达式一起使用

mysql> SELECT * FROM fruits WHERE NOT EXISTS (SELECT s_name FROM suppliers WHERE s_id=107);-----判断条件相反

带IN关键字的子查询

mysql>SELECT c_id FROM orders WHERE o_num IN (SELECT o_num FROM orderitems WHERE f_id='c0'); -----在orderitems表中查询f_id为c0的o_num值，并在orders表中查询具有这些值的c_id

mysql>SELECT c_id FROM orders WHERE o_num NOT IN (SELECT o_num FROM orderitems WHERE f_id='c0'); -----意义相反

带比较运算符的子查询

mysql> SELECT s_id,f_name FROM fruits WHERE s_id=(SELECT s1.s_id FROM suppliers AS s1 WHERE s1.city='shanghai'); ----- 先在suppliers表中查询city为shanghai的所有s_id，再在fruits表中查询s_id等于这些值的s_id和f_name

mysql> SELECT s_id,f_name FROM fruits WHERE s_id<>(SELECT s1.s_id FROM suppliers AS s1 WHERE s1.city='shanghai');----- 语义相反

• 合并查询结果

mysql> SELECT s_id,f_name,f_price
    -> FROM fruits
    -> WHERE f_price<9.0
    -> UNION ALL
    -> SELECT s_id,f_name,f_price
    -> FROM fruits
    -> WHERE s_id IN(101,103);

•  为表和字段取别名

为表取别名

mysql> SELECT * FROM order AS o WHERE o.o_num=30001;

mysql> SELECT c.c_id,o.o_num FROM customers AS c LEFT OUTER JOIN order AS o ON c.c_id=o.c_Id;

为字段取别名

mysql> SELECT f1.f_name AS fruit_name, f1.f_price AS fruit_price FROM fruit AS f1 WHERE f1.f_price<8;

mysql> SELECT CONCAT(TRIM(s_name),'(',TRIM(s_city),')') AS suppliers_title FROM suppliers ORDER BY s_name;

• 使用正则表达式查询

 查询以特定字符或字符串开头的记录

mysql> SELECT * FROM fruits WHERE f_name REGEXP '^b';  -----查询以b开头的记录

mysql> SELECT * FROM fruits WHERE f_name REGEXP '^be'; -----查询以be开头的记录

查询以特定字符或字符串结尾的记录

mysql> SELECT * FROM fruits WHERE f_name REGEXP 'y$';   -----查询以y结尾

mysql> SELECT * FROM fruits WHERE f_name REGEXP 'rry$';   -----查询以rry结尾

 用符号“.”来替代字符串中的任意一个字符

mysql> SELECT * FROM fruits WHERE f_name REGEXP 'a.g';  -----  查询f_name中包含a和g且之间只有一个字母的记录

使用“*”和“+”来匹配多个字符

mysql> select name from tb3 where name regexp '^ba*'; -----以b开头，b后面匹配a任意多次

mysql> select name from tb3 where name regexp '^ba+';-----以b开头，b后面匹配a至少一次

星号*匹配前面的字符任意多次，包括0次，加号+匹配前面的字符至少一次

 匹配指定字符串

mysql> select name from tb3 where name regexp 'on'; -----查询name中包含'on'的记录

mysql> select name from tb3 where name regexp 'on|ap'; -----查询name中包含'on'或者'ap'的记录

 匹配指定字符中的任意一个

mysql> select name from tb3 where name regexp '[ot]'; ----- 查询name中包含字母o或者t的记录

mysql> select name from tb3 where name regexp '[456]';  -----查询name中包含4、5或者6的记录

匹配指定字符以外的字符 

mysql> select name from tb3 where name regexp '[^a-e1-2]';  -----查询name中不包含字母a~e和数字1~2的记录

使用{n,}或{m,n}来指定字符串连续出现的次数

mysql> select name from tb3 where name regexp 'x{2,}'; -----查询name字段中x至少出现两次的记录

mysql> select name from tb3 where name regexp 'x{1,3}'; -----查询name字段中x至少出现1次，至多出现3次的记录

• 查看警告信息

 mysql> SHOW WARNINGS;

• 查看当前MySQL的版本号

 mysql> SELECT VERSION();

• 查看当前用户的连接数

 mysql> SELECT CONNECTION_ID(); 

• 输出当前（全部）用户信息

  mysql> SHOW PROCESSLIST   

 mysql> ​​​​​​SHOW FULL PROCESSLIST

• 查看用户

 mysql> SELECT USER()  CURRENT_USER()  SYSTEM_USER()

• 不同进制转换的函数

 mysql> SELECT CONV(n,from_base, to_base);

• IP地址与数字相互转换的函数  

mysql>  SELECT INET_ATON('10.10.10.10') ——IP地址转整数

mysql>  SELECT INET_NTOA() ——整数转IP地址

• 改变字符集函数

 mysql> select charset(convert('string' using latin1));
+-----------------------------------------+
| charset(convert('string' using latin1)) |
+-----------------------------------------+
| latin1                                  |
+-----------------------------------------+

•  改变数据类型的函数

 mysql> select CAST(100 AS CHAR(2)),CONVERT('2010-10-01 12:12:12',TIME);
+----------------------+-------------------------------------+
| CAST(100 AS CHAR(2)) | CONVERT('2010-10-01 12:12:12',TIME) |
+----------------------+-------------------------------------+
| 10                   | 12:12:12                            |
+----------------------+-------------------------------------+
1 row in set, 1 warning (0.02 sec)

• 创建视图

在单表上创建视图

mysql> CREATE VIEW view_t AS SELECT quantity,price,quantity*price FROM tb;

mysql> CREATE VIEW view_t2 (qty,price,total) AS SELECT quantity,price,quantity*price FROM tb;

在多表上创建视图

mysql> CREATE VIEW stu_glass (id,name,glass) AS SELECT  student.s_id,student.name,stu_info.glass FROM student,stu_info WHERE student.s_id=stu_info.s_id;

• 查看视图

使用DESCRIBE语句查看视图基本信息

mysql> DESCRIBE <视图名>；

使用SHOW TABLE STATUS 查看视图基本信息

mysql> SHOW TABLE STATUS LIKE '视图名';

mysql> SHOW TABLE STATUS LIKE 'my_view';

使用SHOW CREATE VIEW 查看视图详细信息

mysql> SHOW CREATE VIEW <视图名>;

在views表中查看视图详细信息

mysql> SELECT * FROM information_schema.views;

• 修改视图

使用CREATE OR REPLACE VIEW 语句修改视图

mysql> CREATE OR REPLACE VIEW view_t AS SELECT * FROM tb;

使用ALTER语句修改视图

mysql> ALTER VIEW view_t AS SELECT * FROM tb;

• 更新视图

使用update语句更新视图 

mysql> UPDATE view_t SET quantity=5;

使用INSERT语句在基本表t中插入一条记录

mysql> INSERT INTO t VALUES (3,5);

使用DELETE语句删除视图中的一条记录

mysql> DELETE FROM view_t WHERE price=5;

• 删除视图

mysql> DROP VIEW [IF EXISTS] <视图名>;

• 创建触发器

创建只有一个执行语句的触发器

mysql> CREATE TRIGGER <trigger_name> <trigger_time> <trigger_event> ON <table_name> FOR EACH ROW <trigger_stmt>;

trigger_name:触发器名称

trigger_time：触发时机，可以指定为BEFORE 或者 AFTER

trigger_event:标识触发事件，包括INSERT UPDATE 和DELETE

table_name:标识要添加触发器的表名

trigger_stmt：触发器执行语句

mysql> CREATE TABLE account(acct_num INT, amount DECIMAL(10,2));
mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account FOR EACH ROW SET @sum=@sum+NEW.amount;
mysql> SET @sum=0;
mysql> INSERT INTO account VALUES(1,1.00),(2,2.00);
mysql> SELECT @sum;

创建有多个执行语句的触发器

mysql> CREATE TRIGGER <trigger_name> <trigger_time> <trigger_event> ON <table_name> 
FOR EACH ROW 
BEGIN
<语句执行列表>
END

•  查看触发器

 SHOW TRIGGERS语句查看触发器信息

mysql> SHOW TRIGGERS;

在triggers表中查看触发器信息

mysql> SELECT * FROM information_schema.triggers WHERE <condition>;

mysql> SELECT * FROM information_schema.triggers WHERE TRIGGER_NAME='mytrigger';

• 删除触发器(相同的表，对应于相同事件只能创建一个触发器)

mysql> DROP TRIGGER [数据库名].<trigger_name>;

mysql> DROP TRIGGER test.ins;

• 锁定数据表并刷新磁盘

mysql>  FLUSH TABLES WITH READ LOCK;

修改命令 

• 创建数据库 

mysql> CREATE DATABASE database_name; 

• 删除数据库 

mysql> DROP DATABASE database_name; 

• 切换当前使用数据库的命令 

mysql> USE database_name; 

• 创建数据表 

mysql> CREATE TABLE table_name

• 基于一个已有的表创建数据表（只会创建相同的schema，数据不会复制）

mysql> create table t3 like t2;

• 定义单字段主键约束

 （1）定义列的同时指定主键 

（2）定义完所有列之后指定主键

•  定义多字段联合主键

• 定义外键约束

• 使用非空约束 

• 使用唯一性约束

 （1）在定义完列之后直接指定唯一约束

（2） 在定义完所有列之后指定唯一约束

• 使用默认约束 

• 设置表的属性自动增加（必须是主键）

•  修改表名命令

mysql>  ALTER TABLE old_name RENAME new_name;

• 修改字段的数据类型 

mysql> ALTER TABLE <表名> modify <字段名>  <数据类型>;

ALTER TABLE tbname modify name VARCHAR(20);

• 修改字段名  

mysql> ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型> ;

• 添加字段

mysql> ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] [FIRST | AFTER 已存在字段名];

ALTER TABLE tb_dep1 ADD column1 VARCHAR(12) NOT NULL;

ALTER TABLE tb_dep1 ADD column1 VARCHAR(12) FIRST;  ----在表的第一列添加一个字段

ALTER TABLE tb_dep1 ADD column1 VARCHAR(12) AFTER name;-----在表的指定列之后添加一个字段

• 删除字段

mysql> ALTER TABLE <表名> DROP <字段名>; 

• 修改字段的排列位置

mysql> ALTER TABLE <表名> MODIFY <字段1> <数据类型> FIRST|AFTER <字段2>;

 ALTER TABLE tb_dep1 MODIFY column1 VARCHAR(12) FIRST; 

ALTER TABLE tb_dep1 MODIFY column1 VARCHAR(12) AFTER column2;

• 更改表的存储引擎

mysql> ALTER TABLE <表名> ENGINE=<更改后的存储引擎名>; 

• 删除表的外键约束

 mysql> ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>;

• 删除没有被关联的表 

mysql> DROP TABLE [IF EXISTS] 表1,表2,...表n 

•  删除表中所有数据

mysql> DELETE FROM <表名>;

• 向表中插入时间的命令

 mysql> INSERT INTO <表名> VALUES (CURRENT_TIME);  -----当前时间

 mysql> INSERT INTO <表名> VALUES (CURRENT_DATE); -----当前日期

 mysql> INSERT INTO <表名> VALUES (NOW());  ----- 当前日期和时间

• 设置数据库时区命令

  mysql> SET time_zone='+10.00'  -----修改当前时区为东10区

•  显示enum类型对应的数字值的方法

 mysql>  SELECT enm+0 FROM tmp2;

• 生成一个含有enum类型的table并插入值

mysql> create table tmp10 (score int, level enum('excellent','good', 'bad'));

mysql> insert into tmp10 values (70,'good'),(80,2),(50,3),(90,1);

•  生成一个含有set的table并插入值

 mysql> create table tmp11(s set('a','b','c','d'));

mysql> insert into tmp11 values ('a'),('c,b,a'),('a,a,a');

 

 (set会自动排序、去重)

•  在table中存一个位字段类型并查看其二进制

 mysql> create table tmp12(b BIT(4) );

mysql> insert into tmp12 values(7);

• 插入数据

为表的所有字段插入数据

mysql> INSERT INTO person(id,name,age,info) VALUES (1,'green',21,'lawyer');----自定义字段对应顺序

mysql> INSERT INTO person VALUES (1,'green',21,'lawyer');------values顺序必须与表的定义顺序相同

为表的指定字段插入数据

 mysql> INSERT INTO person(name,age,info) VALUES ('green',21,'lawyer'); -----如果某些字段没有指定插入值，则会插入该字段指定的默认值

同时插入多条记录

 mysql> INSERT INTO <表名> (列名) VALUES (值1),(值2),...(值n);

将查询结果插入到表中

 mysql> INSERT INTO person(id,name,age,info) SELECT id,name,age,info FROM person_old;

• 更新数据

mysql> UPDATE person SET age=15,name='Liming' WHERE id=11;  -----更新person表中id为11的一行，将age改为15，name改为Liming

mysql> UPDATE person SET info='student' WHERE age BETWEEN 19 AND 22;

• 删除数据

 mysql> DELETE FROM person WHERE id=11;

mysql> DELETE FROM person WHERE age BETWEEN 19 AND 22;

mysql> DELETE FROM person; -----删除person表的所有记录

索引

MySQL支持的索引存储类型：BTREE和HASH

 MyISAM和InnoDB只支持BTREE索引，MEMORY/HEAP存储引擎支持HASH和BTREE索引

• 创建索引

 创建普通索引

 mysql> CREATE TABLE mytable
    -> (
    -> bookid INT NOT NULL,
    -> year_publication YEAR NOT NULL,
    -> INDEX(year_publication)
    -> );

使用EXPLAIN语句查看索引是否正在使用：

mysql> EXPLAIN SELECT * FROM mytable WHERE year_publication=1990;

创建唯一索引

 mysql> CREATE TABLE mytable
    -> (
    -> bookid INT NOT NULL,
    -> year_publication YEAR NOT NULL,
    -> UNIQUE INDEX(year_publication)
    -> );

创建单列索引

 单列索引就是以数据表中某一个字段创建的索引，一个数据表中可以创建多个单列索引

mysql> CREATE TABLE t2
    -> (
    -> id INT NOT NULL,
    -> name CHAR(50) DEFAULT NULL,
    -> INDEX SingleIdx(name(20))
    -> );

创建组合索引

 mysql> CREATE TABLE t2
    -> (
    -> id INT NOT NULL,
    -> name CHAR(50) DEFAULT NULL,
    -> age INT NOT NULL,
    -> info VARCHAR(255),
    -> INDEX MultiIdx(id,name,age(100))
    -> );

 组合索引遵从“最左前缀”原则

创建全文索引

 只有MyISAM存储引擎支持FULLTEXT索引，并且只为CHAR VARCHAR 和TEXT列创建索引，索引总是对整个列进行，不支持局部（前缀）索引，适合于大型数据集

mysql> CREATE TABLE t2
    -> (
    -> id INT NOT NULL,
    -> name CHAR(50) DEFAULT NULL,
    -> age INT NOT NULL,
    -> info VARCHAR(255),
    -> FULLTEXT INDEX FullTxtIdx(info)
    -> )ENGINE=MyISAM;  -----使用全文索引必须指定数据库引擎为MyISAM，否则会出错

创建空间索引

 空间索引必须在MyISAM表中建立，且空间类型的字段必须非空

mysql> CREATE TABLE t5
    -> (
    -> g GEOMETRY NOT NULL,
    -> SPATIAL INDEX spatIdx(g)
    -> ) ENGINE=MyISAM;

•  在已经存在的表上创建索引

 查看指定表中的索引

 mysql> SHOW INDEX FROM t5;

使用ALTER TABLE语句创建索引

mysql> ALTER TABLE t2 ADD INDEX nameIdx(name(30));

使用CREATE INDEX 创建索引

mysql> CREATE INDEX nameIndex ON t2(name);

mysql> CREATE UNIQUE INDEX nameUnqIndex ON t2(name);

mysql> CREATE INDEX nameMultiIndex ON t2(id,name);

• 删除索引

 使用ALTER TABLE 删除索引

mysql> ALTER TABLE <表名> DROP INDEX <索引名>;

mysql> ALTER TABLE t2 DROP INDEX nameIndex;

使用DROP INDEX 语句删除索引 

 mysql> DROP INDEX <索引名> ON <表名>;

mysql> DROP INDEX nameUnqIndex ON t2;

 存储过程和函数

•  创建存储过程

 mysql> DELIMITER //
mysql> CREATE PROCEDURE Proc()
    -> BEGIN
    -> SELECT * FROM t2;
    -> END //

mysql> CALL Proc();  -----调用

DELIMITER作用是替换结束符的字符

mysql> CREATE PROCEDURE CountProc(OUT para INT)
    -> BEGIN
    -> SELECT COUNT(*) INTO para FROM t2;
    -> END //

 mysql> CALL CountProc(@par);  -----调用

mysql> SELECT @par;

• 创建存储函数

 mysql> DELIMITER //
mysql> CREATE FUNCTION myfunc()
    -> RETURNS CHAR(50)
    -> RETURN (SELECT name from t2 WHERE name IS NOT NULL);
    -> //

mysql> SELECT myfunc();  -----调用函数

• 变量的使用

 定义变量

 mysql> DECLARE <变量名> <类型> [DEFAULT value];

mysql> DECLARE mypara INT DEFAULT 100;

为变量赋值

 mysql> SET <变量名>=<值>;

mysql> SET mypara=10;

mysql> SELECT <列名> INTO <变量名>

mysql> DECLARE fruitname CHAR(50);
    -> DECLARE fruitprice DECIMAL(8,2);
    -> SELECT f_name,f_price INTO fruitname,fruitprice
    -> FROM fruits WHERE f_id='a1';

•   定义条件和处理程序

 定义条件

 定义处理程序

 

• 光标的使用 (mysql中，光标只能用在函数和存储过程中)

声明光标

DECLARE <cursor_name> CURSOR FOR <select_statement>;

DECLARE cursor_fruit CURSOR FOR SELECT f_name,f_price FROM fruits;

打开光标

OPEN <cursor_name>

使用光标

 FETCH <cursor_name> INTO <var_name>;

FETCH cursor_fruit INTO fruit_name,fruit_price;

关闭光标

CLOSE <cursor_name>

•  流程控制的使用

 IF语句

IF val IS NULL
    THEN SELECT 'val is NULL';
    ELSE SELECT 'val is not NULL';
END IF;

CASE语句 

CASE val
    WHEN 1 THEN SELECT 'val is 1';
    WHEN 2 THEN SELECT 'val is 2';
    ELSE SELECT 'val is not 1 or 2';
END CASE;


CASE
    WHEN val IS NULL THEN SELECT 'val is NULL';
    WHEN val<0 THEN SELECT 'val<0';
    WHEN val>0 THEN SELECT 'val>0';
    ELSE SELECT 'val is 0';
END CASE;

LOOP语句 

DECLARE id INT DEFAULT 0;
add_loop: LOOP
SET id=id+1;
   IF id>10 THEN  LEAVE add_loop;
   END IF;
END LOOP add_loop;

LEAVE语句

add_num: LOOP
SET @count=@count+1;
    IF @count=50 THEN LEAVE add_num;
END LOOP add_num;

 ITERATE语句（类似C++ continue）

CREATE PROCEDURE doiterate()
BEGIN
DECLARE p1 INT DEFAULT 0;
my_loop: LOOP
    SET p1=p1+1;
    IF p1<10 THEN ITERATE my_loop;
    ELSEIF p1>20 THEN LEAVE my_loop;
    END IF;
    SELECT 'p1 is between 10 and 20'
END LOOP my_loop;
END

REPEAT语句

DECLARE id INT DEFAULT 0;
REPEAT 
SET id=id+1;
UNTIL id>=10;
END REPEAT;

WHILE语句

DECLARE id INT DEFAULT 0;
WHILE id<10 DO
SET id=id+1;
END WHILE

• 调用存储过程

mysql> CREATE PROCEDURE CountProc(IN sid INT,OUT num INT)
    -> BEGIN
    -> SELECT count(*) INTO num FROM tb1 WHERE s_id=sid;
    -> END;
调用：
mysql> CALL CountProc(100,@num);
mysql> SELECT @num;

• 调用存储函数

mysql> CREATE FUNCTION CountFunc(IN sid INT)
    -> RETURNS INT
    -> RETURN (SELECT COUNT(*) FROM tb1 WHERE s_id=sid);
调用：
mysql> SELECT CountFunc(100);

• 查看存储过程和函数

使用SHOW STATUS 查看存储过程和函数状态 

mysql> SHOW [PROCEDURE | FUNCTION] STATUS [LIKE 'pattern']

mysql> SHOW PROCEDURE STATUS LIKE 'C%' \G

使用SHOW CREATE语句查看存储过程和函数定义 

mysql> SHOW CREATE [PROCEDURE | FUNCTION] sp_name;

mysql> SHOW CREATE FUNCTION dbname.myfunc\G 

从information_schema.Routines中查看存储过程和函数 

mysql> SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME='sp_name';

mysql> SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME='myFunc' AND ROUTINE_TYPE='FUNCTION'\G

• 修改存储过程和函数 

> ALTER PROCEDURE CountProc
> MODIFIES SQL DATA
> SQL SECURITY INVOKER  ------ 修改存储过程CountProc的定义，将读写权限改为MODIFIES SQL DATA，并指明调用者可以执行

> ALTER FUNCTION CountProc
> READS SQL DATA
> COMMENT 'FIND NAME';  ----- 修改存储函数CountProc的定义，将读写权限改为READS SQL DATA，并加上注释信息 'FIND NAME'

•  删除存储过程和函数

 DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

DROP PROCEDURE CountProc;
DROP FUNCTION CountProc;

设置存储过程参数为中文

CREATE PROCEDURE useinfo(IN u_name VARCHAR(50) character set gbk, OUT age INT)

用户和权限管理

•  权限表

 user表

 记录允许连接到服务器的账号信息，里面的权限是全局级的。包含用户列、权限列、安全列和资源控制列

db表和host表

 db表中存储了用户对某个数据库的操作权限，决定了用户能从哪个主机存取哪个数据库。

host表中存储了某个主机对数据库的操作权限。配合db表对给定主机上数据库级操作权限做更细致的控制

tables_priv表和columns_priv表

tables_priv表用来对表设置操作权限，columns_priv表用来对某一列设置权限

 procs_priv表

procs_priv表可以对存储过程和存储函数设置操作权限

•  账户管理

•  登录和退出mysql服务器 

mysql -h localhost -u root -p test ---使用root登录到本地mysql服务器的test库中
mysql -h localhost -u root -p mysql -e "DESC person;"---使用root登录到本地mysql服务器的mysql库中，并执行DESC person语句

• 新建普通用户

   1、使用CREATE USER语句创建新用户

 mysql> CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass'; -----用户名是Jeffrey，密码是mypass，主机名是localhost

mysql> CREATE USER 'jeffrey'@'localhost' IDENTIFIED WITH  my_auth_plugin;---使用插件设定密码

2、使用 GRANT 语句创建新用户（使用create新建的用户没有任何权限，需要用grant赋予权限，而使用grant语句新建的用户可以直接赋予权限）

 mysql> GRANT SELECT,UPDATE ON *.* TO 'testUser'@'localhost' IDENTIFIED BY 'testpwd';

3、直接操作mysql用户表

mysql> INSERT INTO mysql.user (host,user,password,[privilege list]) VALUES ('host', 'username', PASSWORD('password'), privilegevaluelist);

• 查看用户权限

 mysql> SELECT host,user,select_priv,update_priv FROM mysql.user WHERE USER='testUser';

• 删除普通用户

  使用DROP USER 语句删除用户

 mysql> DROP USER 'user'@'localhost'; --- 删除用户user在本地登录的权限

mysql> DROP USER ---删除来自所有授权表的账户权限记录

DROP USER不能关闭已打开的用户会话，直到当前会话被关闭之后才会生效

使用DELETE语句删除用户

 mysql>DELETE FROM mysql.user WHERE host='hostname' AND user='username';

•  root用户修改自己的密码

 使用mysqladmin命令在命令行指定新密码 

mysqladmin -u username -h localhost -p password 'new password'

mysqladmin -u root -p password 'newpwd'

修改mysql数据库的user表

mysql> UPDATE mysql.user SET password=password('newpwd') WHERE user='root' AND host='localhost';

mysql> FLUSH PRIVILEGES; ---重新加载权限，刷新密码

使用set语句修改root用户的密码(普通用户也可以登录后这样修改自己的密码)

（先以root账户登录）mysql> SET PASSWORD=password('newpasssword'); ---新密码必须使用password函数加密

• root用户修改普通用户的密码

 使用set语句修改普通用户的密码

 mysql> SET PASSWORD FOR 'user'@'host' =PASSWORD('somepassword');

使用UPDATE语句修改普通用户的密码

mysql>  UPDATE mysql.user SET password=password('newpassword') WHERE user='username' AND host='hostname';

  使用GRANT语句修改普通用户密码

 mysql> GRANT USAGE ON *.* TO 'testuser'@'localhost' IDENTIFIED BY 'newpassword';

• root用户密码丢失的解决办法

C:\Users\ekailix>net stop mysql
The MySQL service is stopping.
The MySQL service was stopped successfully.

C:\Users\ekailix>mysqld --skip-grant-tables

(打开另一个cmd窗口)
C:\Users\ekailix>mysql -u root
(此时已经以root账号登陆了，可以用修改密码)

mysql> FLUSH PRIVILEGES;

• 授权

 1、全局层级（mysql.user）

mysql> GRANT ALL ON *.* TO 'username'@'hostname'; ---授予全局所有权限
mysql> REVOKE ALL ON *.* FROM 'username'@'hostname'; ---撤销全局所有权限

mysql> GRANT UPDATE ON *.* TO 'username'@'hostname'; ---授予全局更新权限
mysql> REVOKE UPDATE ON *.* FROM 'username'@'hostname'; ---撤销全局更新权限

 2、数据库层级(mysql.db)

mysql> GRANT ALL ON <dbname> TO 'username'@'hostname'; ---授予数据库所有权限
mysql> REVOKE ALL ON <dbname> FROM 'username'@'hostname'; ---撤销数据库所有权限

mysql> GRANT UPDATE ON <dbname> TO 'username'@'hostname'; ---授予数据库更新权限
mysql> REVOKE UPDATE ON <dbname> FROM 'username'@'hostname'; ---撤销数据库更新权限

  3、表层级(mysql.tables_priv)

mysql> GRANT ALL ON <dbname.tablename> TO 'username'@'hostname'; ---授予表所有权限
mysql> REVOKE ALL ON <dbname.tablename> FROM 'username'@'hostname'; ---撤销表所有权限

mysql> GRANT UPDATE ON <dbname.tablename> TO 'username'@'hostname'; ---授予表更新权限
mysql> REVOKE UPDATE ON <dbname.tablename> FROM 'username'@'hostname'; ---撤销表更新权限

  4、列层级(mysql.columns_priv)

 5、子程序层级(mysql.procs_priv) 

 mysql> GRANT SELECT,INSERT ON *.* TO 'user'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;

•   查看权限

mysql> SHOW GRANTS FOR 'username'@'hostname';
mysql> SELECT prrivilege_list FROM mysql.user WHERE user='username' AND host='hostname';
prrivilege_list 为想要查看权限的字段，可以为select_priv,insert_priv等

•  访问控制

 连接核实阶段-->>请求核实阶段

  user  -->>  db  -->> tables_priv -->> columns_priv

• 查看是否有匿名用户 

mysql> SELECT  * FROM user WHERE user='';

数据备份与恢复 

•  数据备份

•  使用mysqldump命令备份 

C:\>mysqldump -u root -p mydb > C:/mydb.sql  ---使用mysqldump备份单个数据库中的所有表,这个命令只会导出mydb下的所有表，但不会导出mydb本身，如果要导出创建mydb的语句本身，则需要指定--databases参数

C:\>mysqldump -u root -p mydb t2 > C:/mydb_t2.sql ---使用mysqldump备份数据库中的某个表

C:\>mysqldump -u root -p --databases mydb1 mydb2 > C:/mydb1_mydb2.sql --- 使用mysqldump备份多个数据库

C:\>mysqldump -u root -p --all-databases > C:/alldb.sql ---使用mysqldump备份系统中所有数据库

• 使用mysqlhotcopy工具快速备份

mysqlhotcopy是备份数据库或单个表最快的途径，但只能运行在数据库目录所在的机器上，且只能备份MyISAM类型的表
mysqlhotcopy -u root -p mydb /usr/backup ---使用mysqlhotcopy备份mydb数据库到/usr/backup目录下
要想执行mysqlhotcopy，必须可以访问备份的表文件，具有SELECT和RELOAD权限
一般要先LOCK TABLES,再FLUSH TABLES;--写入磁盘

•  数据恢复

• 使用mysql命令恢复 

C:\> mysql -u root -p mydb < C:\mydb.sql ---使用mysql命令将C:/mydb.sql文件中的备份导入到mydb数据库中，导入之前首先要创建mydb数据库，否则导入会失败

如果以root账户登入数据库，还可以使用source命令导入
mysql> use mydb;
mysql> SOURCE C:\mydb.sql

•  mysqlhotcopy快速恢复

•  数据库迁移

 相同版本的mysql数据库之间的迁移 

mysqldump -h www.abc.com -uroot -ppassword dbname | mysql -h www.bcd.com -uroot -ppassword
将www.abc.com主机上的dbname数据库迁移到www.bcd.com主机上
| 表示管道
如果要迁移全部数据库，可以使用--all-databases参数

不同版本的mysql数据库之间的迁移 

 不同数据库之间的迁移

•  表的导出

• SELECT ... INTO OUTFILE 命令 

SELECT ... INTO OUTFILE 适用于本机
不同机器执行： C:\>mysql -uroot -p -e "select * from mydb.t2" > C:\1.txt

mysql> SELECT * FROM mydb.t2 INTO OUTFILE "C:\mydb_t2.txt";

mysql> SELECT * FROM mydb.t2 INTO OUTFILE "C:\mydb_t2.txt"
    -> FIELDS
    -> TERMINATED BY ','
    -> ENCLOSED BY '\"'
    -> ESCAPED BY '\''
    -> LINES
    -> TERMINATED BY '\r\n'


mysql> SELECT * FROM mydb.t2 INTO OUTFILE "C:\mydb_t2.txt"
    -> LINES
    -> STARTING BY '>'
    -> TERMINATED BY '<end>\r\n'

•  使用mysqldump命令导出文本文件

C:\>mysqldump -T C:\ mydb -uroot -p123456 
---使用mysqldump将mydb数据库导出到C:\目录下，
导出文件会包含mydb.sql和mydb.txt,其中mydb.sql中包含创建mydb的CREATE语句，
mydbmydb.txt包含其中数据

C:\>mysqldump -T C:\ test person -uroot -p --fields-terminated-by=, --fields-optionally-enclosed-by\" --fields-escaped-by? --lines-terminated-by=<end>
---导出test数据库中的person表

• 使用mysql命令导出文本文件

C:\>mysql -u <username> -p <password> --execute="SELECT语句" <dbname> > C:\1.txt

C:\>mysql -uroot -p --execute="select * from t2;" mydb > C:\1.txt

C:\>mysql -uroot -p --execute="select * from mydb.t2;"> C:\1.txt

指定结果显示格式，如果字段很多一行显示不完，则用--vertical参数分为多行显示
C:\>mysql -uroot -p --vertical --execute="select * from mydb.t2;"> C:\1.txt

--html参数：导出为.html文件
C:\>mysql -uroot -p --html --execute="select * from mydb.t2;"> C:\1.html

--xml参数：导出为.xml文件
C:\>mysql -uroot -p --xml --execute="select * from mydb.t2;"> C:\1.xml

• 表的导入

•  使用LOAD DATA INFILE方式导入文本文件

将C:\1.txt中的数据导入到test.person表
mysql> LOAD DATA INFILE 'C:\1.txt' INTO TABLE test.person; 

mysql> LOAD DATA INFILE 'C:\1.txt' INTO TABLE test.person
    -> FIELDS
    -> TERRMINATED BY ','
    -> ENCLOSED BY '\"'
    -> ESCAPED BY '\''
    -> LINES
    -> TERMINATED BY '\r\n'

• 使用MySQLimport命令导入文本文件

C:\>mysqlimport -uroot -p test C:\1.txt --fields-terminated-by=,
 --fields-optionally-enclosed-by=\" --fields-escaped-by=? 
--lines-terminated-by=\r\n

MySQL日志

•  MySQL日志分类

 错误日志、查询日志、二进制日志、慢查询日志

• 刷新日志命令

mysql> flush logs;

C:\Users\ekailix>mysqladmin -uroot -p flush-logs

C:\Users\ekailix>mysqladmin -uroot -p refresh

•  二进制日志

 主要记录MySQL数据库的变化，以一种有效的格式，并且是事务安全的方式包含更新日志中可用的所有信息。包含更新了数据库的语句的信息，不包含没有修改任何数据的语句。使用二进制日志的主要目的是最大可能地恢复数据库。

启动和设置二进制日志

在my.ini中设置
[mysqld]
log-bin [=path/[filename]]
expire_logs-days = 10
max_binlog_size = 100M


log-bin="D:/mysql/log/binlog"

 查询日志设置

mysql> show variables like 'log_%';

查看二进制日志

 当mysql创建二进制日志文件时，首先创建一个以“filename”为名称，以“.index”为后缀的文件，再创建一个以“filename”为名称，以“.000001”为后缀的文件，当mysql服务器重新启动一次，以.000001为后缀的文件就会增加一个，并且后缀名+1递增，如果日志长度超过max_binlog_size，也会新增一个文件。

mysql> SHOW BINARY LOGS; ---登陆之后命令查看

C:\>mysqlbinlog D:/mysql/log/binlog.000001 ---使用mysqlbinlog工具查看

 删除二进制日志 

1、使用RESET MASTER删除，所有二进制日志将被删除，mysql将会重新创建二进制日志，新的日志扩展名会从.000001开始编号
mysql> RESET MASTER;

2、使用PURGE MASTER LOGS语句删除指定日志文件
 2.1.第一种方法指定文件名，执行该命令将删除文件名编号比指定文件名编号小的所有日志
mysql> PURGE MASTER LOGS TO "binlog.000003";

 2.2.第二种方法指定日期，执行该命令将删除指定日期以前的所有文件
mysql> PURGE MASTER LOGS BEFORE '20200530';

  使用二进制日志恢复数据库

mysqlbinlog [option] filename | mysql -uusername -ppassword
option：可选选项，比较重要的两对参数是--start-date和--stop-date
（指定恢复数据库的起始时间点和结束时间点）,
--start-position和--stop-position（指定恢复数据库的开始位置和结束位置）
 
mysqlbinlog --stop-date="2021-10-13 11:04:00" D:\mysql\log\binlog\binlog.000008 | mysql -uroot -p123456 （使用mysqlbinlog恢复MySQL数据库到2021-10-13 11:04:00的状态）

暂时停止二进制日志功能

mysql> SET sql_log_bin=0; ---暂停记录二进制日志

mysql> SET sql_log_bin=1; ---开始记录二进制日志

• 错误日志

 mysql会将启动和停止数据库信息以及一些错误信息记录到错误日志文件中。文件名默认为hostname.err，执行了flush logs会重新加载错误日志

启动和设置错误日志

my.ini  my.cnf
[mysqld]
log-error=[path/[filename]]
path为日志文件所在的目录路径
filename为文件名，修改配置之后，需要重启mysql服务以生效

查看错误日志

mysql> SHOW VARIABLES LIKE 'log_error';

删除错误日志

 flush logs在重新加载日志的时候，如果日志不存在，则会自动创建
mysqladmin -u root -p flush-logs

或者登陆mysql执行：
mysql> FLUSH LOGS;

• 通用查询日志

 启动和设置通用查询日志

通用查询日志是日志目录下后缀名为*.log的日志
[mysqld]
log[=path/[filaname]]

删除通用查询日志

直接到目录下删除.log
建立新日志
C:\Users\ekailix>mysqladmin -uroot -p flush-logs

•  慢查询日志

 慢查询日志是记录查询时长超过指定时间的日志，主要用于记录查询时长较长的查询语句。通过慢查询日志可以找出执行效率较低的语句并优化。

启动和设置慢查询日志

在my.ini 或 my.cnf中设置：
[mysqld]
log-slow-qureies[=path/[filename]]
long_query_time=n  //long_query_time指定记录阈值，单位是秒，
如果没有指定，则默认时间是10s

 删除慢查询日志

 mysqladmin -uroot -p flush-logs重新生成日志文件或登录后执行flush logs命令

性能优化 

•  查看MySQL数据库的性能参数

mysql> SHOW STATUS LIKE 'value';
value是要查询的参数值，常用参数如下：
Connections--连接mysql服务器的次数
Uptime--MySQL服务器的上线时间
Slow_queries--慢查询的次数
Com_select--查询操作的次数
Com_insert--插入操作的次数
Com_update--更新操作的次数
Com_delete--删除操作的次数

• 分析查询语句

mysql> EXPLAIN SELECT * FROM <name>;
mysql> DESCRIBE SELECT * FROM <name>;
mysql> DESC SELECT * FROM <name>;

•  索引查询规则

1、使用LIKE关键字的查询语句
在使用LIKE关键字进行查询的语句中，如果匹配字符串的第一个字符为‘%’，
则索引不会起作用。只有‘%’不在第一位时，索引才会起作用
mysql> SELECT * FROM table WHERE f_name LIKE '%x' --索引不起作用
mysql> SELECT * FROM table WHERE f_name LIKE 'x%' --索引起作用

2、使用多列索引的查询语句
对于多列索引，只有查询条件中使用了这些字段中的第一个字段时，索引才会被使用
mysql> CREATE INDEX my_index ON fruits(f_id, f_price);
mysql> SELECT * FROM fruits WHERE f_id=12;  --使用索引
mysql> SELECT * FROM fruits WHERE f_price=12;  --不使用索引

3、使用OR关键字的查询语句
查询语句的查询条件中只有or关键字，且or前后两个条件中的列都是索引时，
查询中才使用索引，否则不使用

4、优化子查询
执行子查询时，MySQL要先为内层查询结果建立一个临时表，然后外层查询从
临时表中查询结果，查询完毕再撤销这些临时表，可以使用连接（JOIN）查询来代替
子查询，则不需要创建临时表，效率可以提升。

• 优化数据库结构

1、将字段很多的表分解成多个表
对于字段很多的表，如果有些字段使用频率很低，可以将这些字段分离出来成为新表

2、增加中间表
可以将经常需要联合查询的字段插入到中间表，将原来的联合查询改为对中间表的查询

3、增加冗余字段

• MyISAM引擎的表的常见优化方法 

1、禁用索引
插入记录时，mysql会根据索引为新插入的数据建立索引，如果数据量大则会效率降低
可以在插入之前禁用索引，插入之后再开启索引
mysql> ALTER TABLE <表名> DISABLE KEYS;
mysql> ALTER TABLE <表名> ENABLE KEYS;


2、禁用唯一性检查
插入记录时，mysql会进行唯一性检查，如果数据量大则会效率降低
可以在插入之前禁用唯一性检查，插入之后再开启
mysql> SET UNIQUE_CHECKS=0;
mysql> SET UNIQUE_CHECKS=1;

3、使用批量插入
mysql> INSERT INTO <table> VALUES(),(),()
效率高于
mysql> INSERT INTO <table> VALUES();
mysql> INSERT INTO <table> VALUES();
mysql> INSERT INTO <table> VALUES();

4、使用LOAD DATA INFILE 批量导入
LOAD DATA INFILE效率高于INSERT

•  InnoDB引擎的表的常见优化方法

1、禁用唯一性检查
mysql> SET UNIQUE_CHECKS=0;
mysql> SET UNIQUE_CHECKS=1;

2、禁用外键检查
插入数据之前禁用外键检查，插入之后再打开
mysql> SET FOREIGN_KEY_CHECKS=0;
mysql> SET FOREIGN_KEY_CHECKS=1;

3、禁止自动提交
插入数据之前禁止自动提交，插入之后恢复
mysql> SET autocommit=0;
mysql> SET autocommit=1;

• 分析表、检查表和优化表

1、分析表
分析表过程中，数据库系统会为表加一个只读锁，期间只能读取不能更新和插入
可以分析InnoDB MyISAM和BDB类型的表
mysql> ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE <表名>

2、检查表
执行过程中会给表加上只读锁，可以检查InnoDB和MyISAM类型的表是否存在错误
对于MyISAM的表还会更新关键字统计数据
mysql> CHECK TABLE <表名> [option]
option=(QUICK | FAST | MEDIUM | EXTENDED | CHANGED)

3、优化表
该语句对InnoDB和MyISAM类型的表都有效，但只能优化表中的VARCHAR BLOB 或TEXT
类型的字段，可以消除删除和更新表所造成的文件碎片，也会给表加上只读锁
mysql> OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE <表名>
LOCAL和NO_WRITE_TO_BINLOG含义相同：指定不写入二进制日志

• 如何使用查询缓冲区

查询缓冲区可以提高查询的速度，但只适用于查询语句比较多，
更新语句比较少的情况，可以在my.ini或my.cnf中配置
[mysqld]
query_cache_size=512M
query_cache_type=1

query_cache_size:插叙缓冲区的大小
query_cache_type： 1-开启 0-关闭

只有在查询语句中包含SQL_NO_CACHE关键字时才不会使用查询缓冲区

mysql> FLUSH QUERY CACHE; ---刷新缓冲区，清理查询缓冲区中的碎片