盘点MySQL数据库的数据类型、库和表常见操作、索引、视图、函数等知识点

前言

在日常开发中，存储数据的最常用的方式便是数据库了，其中最为著名的便是MySQL数据库，因它简便易于上手而且可扩展性强大，跨平台使得它广为使用。上一篇文章，我们讲到了它的安装，今天我们就来具体聊聊它的这篇文章分为11个部分，分别包括MySQL数据库的数据类型、库和表常见操作、索引、视图、函数、游标、触发器、存储过程、事务、备份与还原、用户账号、其它等知识点。

一、mysql 数据类型

1.数值类型

7MySQL支持所有标准SQL数值数据类型。

这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。

关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。

BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。

作为SQL标准的扩展，MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

2.日期和时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

TIMESTAMP类型有专有的自动更新特性，将在后面描述。

3.字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

CHAR和VARCHAR类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

BINARY和VARBINARY类类似于CHAR和VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

BLOB是一个二进制大对象，可以容纳可变数量的数据。有4种BLOB类型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。

有4种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这些对应4种BLOB类型，有相同的最大长度和存储需求。

Enum('fds','fsa','fasf') ：枚举类型

set(val1,val2,val3)：集合类型

二、库表操作

1.数据库操作

1. 数据库:         SHOW DATABASES; 
2. 创建数据库:       CREATE DATABASE IF NOT EXISTS people;           
3. 切换数据库:       USE people; 
4. 删除数据库:       DROP DATABASE IF EXISTS people; 
5. 查看当前数据库库信息:        SHOW CREATE DATABASE people; 
6. 修改数据库的选项信息:       ALTER DATABASE people; 

2.数据表操作

1. 显示数据库里所有数据表的信息:       SHOW TABLE STATUS FROM people; 
2. 显示全部数据表:     SHOW TABLES;  
3. 单张表：show tables from df    
4. 清空数据表:      TRUNCATE df; 
5. 表检测:      CHECK TABLE df;   
6. 表优化:       OPTIMIZE TABLE df; 
7. 表修复:    REPAIR TABLE df; 
8. 表分析:      ANALYZE TABLE df; 
9. 分析表 键状态是否正确:     ANALYZE TABLE orders; 
10. 检查表是否存在错误:  check TABLE orders,orderitems QUICK;# QUICK只进行快速扫描 
11. 优化表OPTIMIZE TABLE,消除删除和更新造成的磁盘碎片,从而减少空间的浪费:OPTIMIZE TABLE orders; 
12. 查询表结构:   DESC df;DESCRIBE df; EXPLAIN df;SHOW COLUMNS FROM df; 
13. 复制表:   CREATE TABLE de LIKE df; SELECT * INTO  IN 'hw' FROM df; 
14. 修改表名:   RENAME TABLE de TO people.dh;（可将表移动到另一个数据库） 
15. 修改表字段:   ALTER TABLE df ADD/DROP/CHANGE 
16.   
17. 拼接字段:SELECT CONCAT(us,'(',tim,')') FROM df ORDER BY us ASC;result=>us(tim) 
18.  
19. 添加主键约束：alter TABLE 表名 ADD CONSTRAINT 主键 （形如：PK_表名） PRIMARY KEY 表名(主键字段); 
20. 添加外键约束：alter TABLE 从表 ADD CONSTRAINT 外键（形如：FK_从表_主表） FOREIGN KEY 从表(外键字段) REFERENCES 主表(主键字段); 
21. 删除主键约束：alter TABLE 表名 DROP PRIMARY KEY; 
22. 删除外键约束：alter TABLE 表名 DROP FOREIGN KEY 外键（区分大小写）; 
23.  
24. -- 添加外键约束 
25. CREATE TABLE stu(sid INT PRIMARY KEY,NAME VARCHAR(50) NOT NULL); 
26. -- 添加外键约束方式一 
27. CREATE TABLE score1(score DOUBLE,sid INT,CONSTRAINT fk_stu_score1_sid FOREIGN KEY(sid) REFERENCES stu(sid)); 
28. -- 添加外键约束方式二(若表已存在，可用这种) 
29. CREATE TABLE score1(score DOUBLE,sid INT); 
30. ALTER TABLE score1 ADD CONSTRAINT fk_sid FOREIGN KEY(sid) REFERENCES stu(sid) 

三、索引

1. CREATE UNIQUE INDEX qw ON df(us); #创建不重复索引 
2. ALTER TABLE df ADD UNIQUE INDEX wq(id); #添加索引 
3. SHOW INDEX FROM df;#检索索引 
4. DROP INDEX qw ON people.df; #删除索引 
5. ALTER TABLE df DROP INDEX wq; #删除索引 

四、视图

1. CREATE VIEW shitu AS SELECT us FROM df; 创建视图 
2. ALTER VIEW shitu AS SELECT us FROM df WHERE us='gf'; 修改视图 
3. SELECT * FROM shitu; 查看视图结果 
4. DROP VIEW IF EXISTS shitu;删除视图 

五、函数

1. 文本处理函数 
2. ASCII(CHAR)返回字符的ASCII码值 
3. BIT_LENGTH(str)返回字符串的比特长度 
4. CONCAT(s1,s2...,sn)将s1,s2...,sn连接成字符串 
5. CONCAT_WS(sep,s1,s2...,sn)将s1,s2...,sn连接成字符串，并用sep字符间隔 
6. INSERT(str,X,Y,INSTR) 将字符串str从第x位置开始，y个字符长的子串替换为字符串instr，返回结果 
7. FIND_IN_SET(str,LIST)分析逗号分隔的list列表，如果发现str，返回str在list中的位置 
8. LCASE(str)或LOWER(str) 返回将字符串str中所有字符改变为小写后的结果 
9. LEFT(str,X)返回字符串str中最左边的x个字符 
10. LENGTH(s)返回字符串str中的字符数 
11. LOCATE(str)  找出str串的一个子串 
12. LOWER(str)  将str串转换为小写 
13. LTRIM(str) 从字符串str中切掉开头的空格 
14. POSITION(SUBSTR,str) 返回子串substr在字符串str中第一次出现的位置 
15. QUOTE(str) 用反斜杠转义str中的单引号 
16. REPEAT(str,srchstr,rplcstr)返回字符串str重复x次的结果 
17. REVERSE(str) 返回颠倒字符串str的结果 
18. RIGHT(str,X) 返回字符串str中最右边的x个字符 
19. RTRIM(str) 返回字符串str尾部的空格 
20. SOUNDEX(str)  返回str串的SOUNDEX值 
21. STRCMP(s1,s2)比较字符串s1和s2 
22. SUBSTRING()  返回子串的字符 
23. TRIM(str)去除字符串首部和尾部的所有空格 
24. UCASE(str)或UPPER(str) 返回将字符串str中所有字符转变为大写后的结果 
25.  
26.  
27. 日期和时间处理函数 
28. ADDDATE()  增加一个日期（天、周等） 
29. ADDTIME()  增加一个时间（时、分等） 
30. CURDATE()或CURRENT_DATE() 返回当前的日期 
31. CURTIME()或CURRENT_TIME() 返回当前的时间 
32. DATE()  返回日期时间的日期部分 
33. DATE_ADD(DATE,INTERVAL INT keyword)返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化),如：SELECTDATE_ADD(CURRENT_DATE,INTERVAL 6 MONTH); 
34. DATE_FORMAT(DATE,fmt)  依照指定的fmt格式格式化日期date值 
35. DATE_SUB(DATE,INTERVAL INT keyword)返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化),如：SELECTDATE_SUB(CURRENT_DATE,INTERVAL 6 MONTH); 
36. DAY()  返回一个日期的天数部分 
37. DAYOFWEEK(DATE)   返回date所代表的一星期中的第几天(1~7) 
38. DAYOFMONTH(DATE)  返回date是一个月的第几天(1~31) 
39. DAYOFYEAR(DATE)   返回date是一年的第几天(1~366) 
40. DAYNAME(DATE)   返回date的星期名，如：SELECT DAYNAME(CURRENT_DATE); 
41. FROM_UNIXTIME(ts,fmt)  根据指定的fmt格式，格式化UNIX时间戳ts 
42. HOUR(TIME)   返回time的小时值(0~23) 
43. MINUTE(TIME)   返回time的分钟值(0~59) 
44. MONTH(DATE)   返回date的月份值(1~12) 
45. MONTHNAME(DATE)   返回date的月份名，如：SELECT MONTHNAME(CURRENT_DATE); 
46. NOW()    返回当前的日期和时间 
47. QUARTER(DATE)   返回date在一年中的季度(1~4)，如SELECT QUARTER(CURRENT_DATE); 
48. SECOND()  返回一个时间的秒部分 
49. TIME()  返回一个日期时间的时间部分 
50. WEEK(DATE)   返回日期date为一年中第几周(0~53) 
51. YEAR(DATE)   返回日期date的年份(1000~9999) 
52.  
53. 获取当前系统时间： 
54. SELECT FROM_UNIXTIME(UNIX_TIMESTAMP()); 
55. SELECT EXTRACT(YEAR_MONTH FROM CURRENT_DATE); 
56. SELECT EXTRACT(DAY_SECOND FROM CURRENT_DATE); 
57. SELECT EXTRACT(HOUR_MINUTE FROM CURRENT_DATE); 
58.  
59. 返回两个日期值之间的差值(月数)： 
60. SELECT PERIOD_DIFF(200302,199802); 
61.  
62. 在Mysql中计算年龄： 
63. SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(tim)),'%Y')+0 AS us FROM df; 
64.  
65.  
66.  
67. 常用数值处理函数 
68. ABS(X)   返回x的绝对值 
69. BIN(X)   返回x的二进制（OCT返回八进制，HEX返回十六进制） 
70. CEILING(X)   返回大于x的最小整数值 
71. COS(X)  返回角度x的余弦 
72. EXP(X)   返回值e（自然对数的底）的x次方 
73. FLOOR(X)   返回小于x的最大整数值 
74. GREATEST(x1,x2,...,xn)返回集合中最大的值 
75. LEAST(x1,x2,...,xn)  返回集合中最小的值 
76. LN(X)  返回x的自然对数 
77. LOG(X,Y)返回x的以y为底的对数 
78. MOD(X,Y)返回x/y的模（余数） 
79. PI()返回pi的值（圆周率） 
80. RAND()返回０到１内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。 
81. ROUND(X,Y)返回参数x的四舍五入的有y位小数的值 
82. SIGN(X) 返回代表数字x的符号的值 
83. SIN(X)  返回角度x的正弦 
84. SQRT(X) 返回一个数的平方根 
85. TAN(X)  返回角度x的正切 
86. TRUNCATE(X,Y) 返回数字x截短为y位小数的结果 
87.  
88.  
89. 聚合函数(常用于GROUP BY从句的SELECT查询中) 
90. AVG(col)返回指定列的平均值 
91. COUNT(col)返回指定列中非NULL值的个数 
92. MIN(col)返回指定列的最小值 
93. MAX(col)返回指定列的最大值 
94. SUM(col)返回指定列的所有值之和 
95. GROUP_CONCAT(col) 返回由属于一组的列值连接组合而成的结果 
96.  
97.  
98. 加密函数 
99. AES_ENCRYPT(str,KEY)  返回用密钥key对字符串str利用高级加密标准算法加密后的结果，调用AES_ENCRYPT的结果是一个二进制字符串，以BLOB类型存储 
100. AES_DECRYPT(str,KEY)  返回用密钥key对字符串str利用高级加密标准算法解密后的结果 
101. DECODE(str,KEY)   使用key作为密钥解密加密字符串str 
102. ENCRYPT(str,salt)   使用UNIXcrypt()函数，用关键词salt(一个可以惟一确定口令的字符串，就像钥匙一样)加密字符串str 
103. ENCODE(str,KEY)   使用key作为密钥加密字符串str，调用ENCODE()的结果是一个二进制字符串，它以BLOB类型存储 
104. MD5()    计算字符串str的MD5校验和 
105. PASSWORD(str)   返回字符串str的加密版本，这个加密过程是不可逆转的，和UNIX密码加密过程使用不同的算法。 
106. SHA()    计算字符串str的安全散列算法(SHA)校验和 
107. SELECT ENCRYPT('root','salt'); 
108. SELECT ENCODE('xufeng','key'); 
109. SELECT DECODE(ENCODE('xufeng','key'),'key');#加解密放在一起 
110. SELECT AES_ENCRYPT('root','key'); 
111. SELECT AES_DECRYPT(AES_ENCRYPT('root','key'),'key'); 
112. SELECT MD5('123456'); 
113. SELECT SHA('123456'); 
114.  
115. 格式化函数DATE_FORMAT(DATE,fmt)  依照字符串fmt格式化日期date值 
116. FORMAT(X,Y)   把x格式化为以逗号隔开的数字序列，y是结果的小数位数 
117. INET_ATON(ip)   返回IP地址的数字表示 
118. INET_NTOA(num)   返回数字所代表的IP地址 
119. TIME_FORMAT(TIME,fmt)  依照字符串fmt格式化时间time值 
120. SELECT FORMAT(34234.34323432,3); 
121. SELECT DATE_FORMAT(NOW(),'%W,%D %M %Y %r'); 
122. SELECT DATE_FORMAT(NOW(),'%Y-%m-%d'); 
123. SELECT DATE_FORMAT(19990330,'%Y-%m-%d'); 
124. SELECT DATE_FORMAT(NOW(),'%h:%i %p'); 
125. SELECT INET_ATON('10.122.89.47'); 
126. SELECT INET_NTOA(175790383); 
127.  
128.  
129. 类型转化函数 
130. CAST() 类型有：BINARY,CHAR,DATE,TIME,DATETIME,SIGNED,UNSIGNED 
131. SELECT CAST(NOW() AS SIGNED INTEGER),CURDATE()+0; 
132. SELECT 'f'=BINARY 'F','f'=CAST('F' AS BINARY); 
133.  
134.  
135. 系统信息函数 
136. DATABASE()   返回当前数据库名 
137. BENCHMARK(COUNT,expr)  将表达式expr重复运行count次 
138. CONNECTION_ID()   返回当前客户的连接ID 
139. FOUND_ROWS()   返回最后一个SELECT查询进行检索的总行数 
140. USER()或SYSTEM_USER()  返回当前登陆用户名 
141. VERSION()   返回MySQL服务器的版本 
142. SELECT DATABASE(),VERSION(),USER(); 
143. SELECTBENCHMARK(9999999,LOG(RAND()*PI())); 

六、游标

1. 创建、打开、关闭游标 # 定义名为ordernumbers的游标，检索所有订单 
2. DELIMITER // 
3. CREATE PROCEDURE processorders() 
4. BEGIN 
5.     -- decalre the cursor 声明游标  
6.     declare ordernumbers CURSOR 
7.     FOR 
8.     SELECT order_num FROM orders; 
9.      
10.     -- open the cursor 打开游标 
11.     open ordernumbers; 
12.     -- close the cursor 关闭游标 
13.     close ordernumbers; 
14. END // 
15. DELIMITER ; 
16.  
17. -- 使用游标数据  
18. # 例1：检索 当前行 的order_num列，对数据不做实际处理 
19. DELIMITER // 
20. CREATE PROCEDURE processorders() 
21. BEGIN 
22.  
23.     -- declare local variables 声明局部变量 
24.     DECLARE o INT; 
25.      
26.     -- decalre the cursor 声明游标  
27.     declare ordernumbers CURSOR 
28.     FOR 
29.     SELECT order_num FROM orders; 
30.      
31.     -- open the cursor 打开游标 
32.     open ordernumbers; 
33.      
34.     -- get order number 获得订单号  
35.     FETCH ordernumbers INTO o; 
36.     /*fetch检索 当前行 的order_num列（将自动从第一行开始）到一个名为o的局部声明变量中。 
37.     对检索出的数据不做任何处理。*/ 
38.          
39.     -- close the cursor 关闭游标 
40.     close ordernumbers; 
41.  
42. END // 
43. DELIMITER ; 
44.  
45. # 例2：循环检索数据，从第一行到最后一行，对数据不做实际处理 
46. DELIMITER // 
47. CREATE PROCEDURE processorders() 
48. BEGIN 
49.     -- declare local variables 声明局部变量 
50.     DECLARE done BOOLEAN DEFAULT 0; 
51.     DECLARE o INT; 
52.     
53.     -- decalre the cursor 声明游标  
54.     declare ordernumbers CURSOR 
55.     FOR 
56.     SELECT order_num FROM orders; 
57.     
58.     -- declare continue handler 
59.     DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done =1; 
60.     -- SQLSTATE '02000'是一个未找到条件，当REPEAT由于没有更多的行供循环而不能继续时，出现这个条件。 
61.      
62.     -- open the cursor 打开游标 
63.     open ordernumbers; 
64.      
65.     -- loop through all rows 遍历所有行  
66.     REPEAT 
67.      
68.     -- get order number 获得订单号  
69.     FETCH ordernumbers INTO o; 
70.     -- FETCH在REPEAT内，因此它反复执行直到done为真 
71.      
72.     -- end of loop 
73.     UNTIL done END REPEAT; 
74.      
75.     -- close the cursor 关闭游标 
76.     close ordernumbers; 
77.  
78. END // 
79. DELIMITER ; 
80.  
81.  
82. # 例3：循环检索数据，从第一行到最后一行，对取出的数据进行某种实际的处理 
83. DELIMITER // 
84. CREATE PROCEDURE processorders() 
85. BEGIN 
86.     -- declare local variables 声明局部变量  
87.     DECLARE done BOOLEAN DEFAULT 0; 
88.     DECLARE o INT; 
89.     DECLARE t DECIMAL(8,2); 
90.      
91.     -- declare the cursor 声明游标 
92.     DECLARE ordernumbers CURSOR 
93.     FOR 
94.     SELECT order_num FROM orders; 
95.      
96.     -- declare continue handler 
97.     DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; 
98.      
99.     -- create a table to store the results 新建表以保存数据 
100.     CREATE TABLE IF NOT EXISTS ordertotals 
101.     (order_num INT,total DECIMAL(8,2)); 
102.      
103.     -- open the cursor 打开游标 
104.     OPEN ordernumbers; 
105.      
106.     -- loop through all rows 遍历所有行 
107.     REPEAT 
108.      
109.     -- get order number 获取订单号 
110.     FETCH ordernumbers INTO o; 
111.      
112.     -- get the total for this order 计算订单金额 
113.     CALL ordertotal(o,1,t);  # 参见23章代码，已创建可使用 
114.      
115.     -- insert order and total into ordertotals 将订单号、金额插入表ordertotals内 
116.     INSERT INTO ordertotals(order_num,total) VALUES(o,t); 
117.      
118.     -- end of loop 
119.     UNTIL done END REPEAT; 
120.      
121.     -- close the cursor 关闭游标 
122.     close ordernumbers; 
123.  
124. END //  
125. DELIMITER ; 
126. # 调用存储过程 precessorders() 
127. CALL processorders(); 
128. # 输出结果 
129. SELECT * FROM ordertotals; 

七、触发器

1. １、MySQL触发器的创建语法： 
2. CREATE[DEFINER = { 'user' | CURRENT_USER }] 
3. TRIGGER trigger_name 
4. trigger_time trigger_event 
5. ON table_name 
6. FOR EACH ROW 
7. [trigger_order] 
8. trigger_body 
9. 2、MySQL创建语法中的关键词解释： 
10. 字段                      含义                                               可能的值 
11. DEFINER=            可选参数，指定创建者，                               DEFINER='root@%' 
12.               默认为当前登录用户（CURRENT_USER）； 
13.               该触发器将以此参数指定的用户执行，                       DEFINER=CURRENT_USER 
14.                   所以需要考虑权限问题； 
15.  
16. trigger_name   触发器名称，最好由表名+触发事件关键词+触发时间关键词组成； 
17.   
18. trigger_time     触发时间，在某个事件之前还是之后；BEFORE、AFTER 
19.  
20.                    INSERT：插入操作触发器，INSERT、LOAD DATA、REPLACE时触发； 
21.                    UPDATE：更新操作触发器，UPDATE操作时触发； 
22. trigger_event  触发事件，如插入时触发、删除时触发；DELETE：删除操作触发器，DELETE、REPLACE操作时触发； 
23.                    INSERT、UPDATE、DELETE 
24. table_name          触发操作时间的表名； 
25.   
26.     可选参数，如果定义了多个具有相同触发事件和触法时间的触发器时（ 
27.     如：BEFORE UPDATE），默认触发顺序与触发器的创建顺序一致，可以 
28. trigger_order   使用此参数来改变它们触发顺序。mysql 5.7.2起开始支持此参数。 
29.     FOLLOWS：当前创建触发器在现有触发器之后激活；FOLLOWS、PRECEDES 
30.     PRECEDES：当前创建触发器在现有触发器之前激活； 
31.      
32.      
33. trigger_body     触发执行的SQL语句内容，一般以begin开头，end结尾            BEGIN .. END 
34.      触发执行语句内容（trigger_body）中的OLD，NEW 
35. 触发执行语句内容（trigger_body）中的OLD，NEW：在trigger_body中， 
36. 我们可以使用NEW表示将要插入的新行（相当于MS SQL的INSERTED）， 
37. OLD表示将要删除的旧行（相当于MS SQL的DELETED）。 
38. 通过OLD，NEW中获取它们的字段内容，方便在触发操作中使用， 
39. 下面是对应事件是否支持OLD、NEW的对应关系： 
40. 事件     OLD   NEW 
41. INSERT    ×     √ 
42. DELETE    √     × 
43. UPDATE    √     √ 
44.  
45. 由于UPDATE相当于删除旧行（OLD），然后插入新行（NEW），所以UPDATE同时支持OLD、NEW； 
46. DELIMITER $ 
47. ... --触发器创建语句； 
48. $   --提交创建语句； 
49. DELIMITER ; 
50.  
51.  
52. select * FROM information_schema.triggers; 
53. SHOW TRIGGERS; #查看触发器 
54. -- 通过information_schema.triggers表查看触发器： 
55. select * FROM information_schema.triggers; 
56. -- mysql 查看当前数据库的触发器 
57. SHOW TRIGGERS; 
58. -- mysql 查看指定数据库"people"的触发器 
59. SHOW TRIGGERS FROM people; 
60.  
61. 创建测试表 
62. DROP TABLE IF EXISTS tb; 
63. CREATE TABLE IF NOT EXISTS tb(id INT,username CHAR(10),pass VARCHAR(20),ct INT); 
64. CREATE TABLE IF NOT EXISTS bt(fid INT,username CHAR(10),pass VARCHAR(20),ct INT); 
65. 创建触发器 
66. DELIMITER $ 
67. #drop trigger if exists df_names$ 删除前先判断触发器是否存在 
68. CREATE  DEFINER =CURRENT_USER 
69. TRIGGER df_names 
70. BEFORE INSERT ON tb 
71. AFTER UPDATE ON bt 
72. FOR EACH ROW 
73. BEGIN 
74.    #set new.ct=new.id*5; 
75.    #SET @ct=12; 变量 
76.    #SET @pass='hjfd'; 
77.    IF old.type=1 THEN 
78.   UPDATE bt SET ct=old.ct WHERE fid=old.id; 
79.    ELSE IF old.type=2 THEN 
80.   UPDATE bt SET pass=old.pass WHERE fid=old.id; 
81. END$ 
82. DELIMITER; 
83.  
84. 测试 
85. INSERT INTO tb(id) VALUES(4); 
86. SELECT *FROM tb; 

八、存储过程

1. -- 创建存储过程  
2. # 返回产品平均价格的存储过程 
3. DELIMITER // 
4. CREATE PROCEDURE productpricing() 
5. BEGIN 
6.     select AVG(prod_price) AS priceaverage FROM products; 
7. END // 
8. DELIMITER ; 
9. # 调用上述存储过程  
10. CALL productpricing(); 
11.  
12. -- 删除存储过程,请注意:没有使用后面的()，只给出存储过程名。 
13. DROP PROCEDURE productpricing; 
14.  
15. -- 使用参数 out 
16. # 重新定义存储过程productpricing 
17. DELIMITER // 
18. CREATE PROCEDURE productpricing(OUT pl DECIMAL(8,2), OUT ph DECIMAL(8,2), OUT pa DECIMAL(8,2)) 
19. BEGIN 
20.     select MIN(prod_price) INTO pl FROM products; 
21.     SELECT MAX(prod_price) INTO ph FROM products; 
22.     select AVG(prod_price) INTO pa FROM products; 
23. END // 
24. DELIMITER ; 
25.  
26. # 为调用上述存储过程，必须指定3个变量名 
27. CALL productpricing(@pricelow,@pricehigh,@priceaverage); 
28. # 显示检索出的产品平均价格 
29. SELECT @priceaverage; 
30. # 获得3个值 
31. SELECT @pricehigh,@pricelow,@priceaverage; 
32.  
33. -- 使用参数 in 和 out 
34. # 使用IN和OUT参数,存储过程ordertotal接受订单号并返回该订单的合计 
35. DELIMITER // 
36. CREATE PROCEDURE ordertotal( 
37.     in onumber INT,                   # onumber定义为IN，因为订单号被传入存储过程 
38.     OUT ototal DECIMAL(8,2)            # ototal为OUT，因为要从存储过程返回合计 
39. ) 
40. BEGIN 
41.     select SUM(item_price*quantity) FROM orderitems  
42.     WHERE order_num = onumber 
43.     INTO ototal; 
44. END // 
45. DELIMITER ; 
46. # 给ordertotal传递两个参数； 
47. # 第一个参数为订单号，第二个参数为包含计算出来的合计的变量名 
48. CALL ordertotal(20005,@total); 
49. # 显示此合计 
50. SELECT @total; 
51. # 得到另一个订单的合计显示 
52. CALL ordertotal(20009,@total); 
53. SELECT @total; 
54.  
55. -- 建立智能存储过程  
56. # 获得与以前一样的订单合计，但只针对某些顾客对合计增加营业税 
57.  
58. -- Name:ordertotal 
59. -- Parameters: onumber = order number 
60. --                taxable = 0 if not taxable, 1 if taxable 
61. --                ototal  = order total variable 
62. DELIMITER // 
63. CREATE PROCEDURE ordertotal( 
64.     in onumber INT, 
65.     in taxable BOOLEAN, 
66.     OUT ototal DECIMAL(8,2) 
67. ) COMMENT 'obtain order total, optionally adding tax' 
68. BEGIN 
69.     -- declare variable for total 定义局部变量total 
70.     DECLARE total DECIMAL(8,2); 
71.     -- declare tax percentage 定义局部变量税率  
72.     DECLARE taxrate INT DEFAULT 6; 
73.     -- get the order total 获得订单合计 
74.     SELECT SUM(item_price * quantity) 
75.     FROM orderitems 
76.     WHERE order_num = onumber INTO total; 
77.     -- is this taxable? 是否要增加营业税？  
78.     if taxable THEN 
79.         -- Yes,so add taxrate to the total 给订单合计增加税率 
80.         select total+(total/100*taxrate) INTO total; 
81.     end IF; 
82.     -- and finally,save to out variable 最后，传递给输出变量  
83.     SELECT total INTO ototal; 
84. END // 
85. DELIMITER ; 
86. # 调用上述存储过程，不加税  
87. CALL ordertotal(20005,0,@total); 
88. SELECT @total; 
89. # 调用上述存储过程，加税  
90. CALL ordertotal(20005,1,@total); 
91. SELECT @total; 
92.  
93. # 显示用来创建一个存储过程的CREATE语句 
94. SHOW CREATE PROCEDURE ordertotal; 
95.  
96. # 获得包括何时、由谁创建等详细信息的存储过程列表 
97. # 该语句列出所有存储过程 
98. SHOW PROCEDURE STATUS; 
99. # 过滤模式  
100. SHOW PROCEDURE STATUS LIKE 'ordertotal'; 

九、事务

1. SET AUTOCOMMIT=off ;禁用或启用事务的自动提交模式  off ON 
2. SET SESSION AUTOCOMMIT = OFF；禁用或启用事务的session自动提交模式 off ON 
3. SHOW VARIABLES LIKE '%auto%'; -- 查看变量状态 
4.  
5. 执行DML语句是其实就是开启一个事务 
6. 只能回滚insert、delete和update语句 
7. 对于create、drop、alter这些无法回滚事务只对DML有效果 
8. rollback，或者commit后事务就结束了 
9. 自动提交模式用于决定新事务如何及何时启动 
10. START TRANSACTION; 启用自动提交模式下显式地启动事务 
11. COMMIT和ROLLBACK;  禁用自动提交模式显式地提交或回滚 
12. -- 事务 transaction 指一组sql语句 
13. -- 回退 rollback 指撤销指定sql语句的过程 
14. -- 提交 commit 指将未存储的sql语句结果写入数据库表 
15. -- 保留点 savepoint 指事务处理中设置的临时占位符，可以对它发布回退（与回退整个事务处理不同） 
16.  
17. -- 控制事务处理 
18. # 开始事务及回退  
19. SELECT * FROM ordertotals;   # 查看ordertotals表显示不为空 
20. START TRANSACTION;           # 开始事务处理  
21. DELETE FROM ordertotals;     # 删除ordertotals表中所有行 
22. SELECT * FROM ordertotals;   # 查看ordertotals表显示 为空 
23. ROLLBACK;                     # rollback语句回退  
24. SELECT * FROM ordertotals;   # rollback后，再次查看ordertotals表显示不为空 
25.  
26. # commit 提交  
27. START TRANSACTION; 
28. DELETE FROM orderitems WHERE order_num = 20010; 
29. DELETE FROM orders WHERE order_num = 20010; 
30. COMMIT;   # 仅在上述两条语句不出错时写出更改  
31.  
32. # savepoint 保留点  
33. # 创建保留点 
34. SAVEPOINT delete1; 
35. # 回退到保留点  
36. ROLLBACK TO delete1; 
37. # 释放保留点  
38. RELEASE SAVEPOINT delete1; 
39.  
40. -- 更改默认的提交行为  
41. SET autocommit = 0;  # 设置autocommit为0（假）指示MySQL不自动提交更改 

十、备份与还原

1. 备份一个数据库:mysqldump -u root -p --opt people> df.txt  #--opt优化执行速度 
2. 备份两个数据库:mysqldump -u root -p --opt --databases people hw > all.txt 
3. 备份全部数据库:mysqldump -u root -p --opt --all-DATABASES > all.txt 
4. 恢复数据库:mysqldump -u root -p --opt --databases people hw < all.txt 
5. 1. 导出一张表 
6. mysqldump -u用户名 -p密码 库名 表名 > 文件名(D:/a.sql) 
7. 2. 导出多张表 
8. mysqldump -u用户名 -p密码 库名 表1 表2 表3 > 文件名(D:/a.sql) 
9. 3. 导出所有表 
10. mysqldump -u用户名 -p密码 库名 > 文件名(D:/a.sql) 
11. 4. 导出一个库 
12. mysqldump -u用户名 -p密码 --lock-ALL-TABLES --database 库名 > 文件名(D:/a.sql) 
13. 可以-w携带WHERE条件 

十一、用户账号

1. 创建账户:CREATE USER IF NOT EXISTS 'hw'@'localhost' IDENTIFIED BY '5201314'; #创建用户hw,密码5201314 
2.  
3. 给该用户授予所有权限并可授权给其它用户:GRANT ALL PRIVILEGES ON people.df TO 'hw'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION; 
4.  
5. 重命名用户名: RENAME USER 'hw' TO 'gh'; 必须将localhost改为% 
6. 如果希望该用户能够在任何机器上登陆mysql，则将localhost改为 "%"  
7. 授权给其它用户 WITH GRANT OPTION 
8.  privileges包括： 
9.     alter：修改数据库的表 
10.     create：创建新的数据库或表 
11.     delete：删除表数据 
12.     drop：删除数据库/表 
13.     index：创建/删除索引 
14.     insert：添加表数据 
15.     select：查询表数据 
16.     update：更新表数据 
17.     all：允许任何操作 
18.     usage：只允许登录 
19.  
20. 刷新权限,使新创建的用户能够使用:    FLUSH PRIVILEGES;  
21.                    
22. 收回用户权限: REVOKE ALL PRIVILEGES ON people.df FROM 'root'@'localhost'; 
23.        
24. 删除用户:    DROP USER IF EXISTS 'hw'@'localhost'; 
25.  
26. 设置指定用户的密码:SET PASSWORD FOR'hw'@'localhost' = PASSWORD('123321');  
27.         UPDATE USER SET PASSWORD =  PASSWORD('123321') WHERE USER = 'hw'; 
28.  
29.              
30. 设置密码:    SET PASSWORD = PASSWORD('123321'); 
31.         

十二、其他

1. SHOW STATUS;显示广泛的服务器状态信息 
2. SHOW PROCEDURE STATUS; 
3. SHOW GRANTS;显示授予用户的安全权限 
4. SHOW ERRORS;显示服务器的错误信息 
5. SHOW WARNINGS;显示服务器的警告信息 
6. SHOW PROCESSLIST;显示哪些线程正在运行 
7. SHOW VARIABLES;显示系统变量信息 
8. SELECT DATABASE(); 查看当前数据库 
9. SELECT NOW(), USER(), VERSION():显示当前时间、用户名、数据库版本 
10. SHOW ENGINES 引擎名 {LOGS|STATUS}:显示存储引擎的日志和状态信息 
11. SHOW VARIABLES LIKE 'character%'; SHOW VARIABLES LIKE 'collation%'; 确定所用系统的字符集和校对 
12. SHOW VARIABLES LIKE 'character_set_client%';        客户端向服务器发送数据时使用的编码 
13. SHOW VARIABLES LIKE 'character_set_results%';       服务器端将结果返回给客户端所使用的编码 
14. SHOW VARIABLES LIKE 'character_set_connection%';    连接层编码 
15. SHOW CHARACTER SET;查看所支持的字符集完整列表 
16. SHOW COLLATION;查看所支持校对的完整列表,以及它们适用的字符集 
17.  
18. SET character_set_client = gbk;    
19. SET character_set_results = gbk; 
20. SET character_set_connection = gbk; 
21. SET NAMES GBK;  -- 相当于完成以上三个设置 
22.  
23. 创建window服务:sc CREATE mysql binPath= mysqld_bin_path 
24. 数据文件目录:DATA DIRECTORY='目录' 
25. 索引文件目录:INDEX DIRECTORY = '目录' 

十三、总结

这篇文章主要讲解了MySQL数据库的数据类型、库和表常见操作、索引、视图、函数、游标、触发器、存储过程、事务、备份与还原、用户账号、其它等知识点，希望对大家的学习有帮助。

原文地址：https://mp.weixin.qq.com/s/gqrTl4RIese3nFNZELWSjw