盘点MySQL数据库的数据类型、库和表常见操作、索引、视图、函数等知识点
在日常开发中，存储数据的最常用的方式便是数据库了，其中最为著名的便是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。下面的表显示了需要的每个整数类型的存储和范围。
类型大小范围（有符号）范围（无符号）用途TINYINT1 字节(-128，127)(0，255)小整数值SMALLINT2 字节(-32 768，32 767)(0，65 535)大整数值MEDIUMINT3 字节(-8 388 608，8 388 607)(0，16 777 215)大整数值INT或INTEGER4 字节(-2 147 483 648，2 147 483 647)(0，4 294 967 295)大整数值BIGINT8 字节(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)(0，18 446 744 073 709 551 615)极大整数值FLOAT4 字节(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)0，(1.175 494 351 E-38，3.402 823 466 E+38)单精度 浮点数值DOUBLE8 字节(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)双精度 浮点数值DECIMAL对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2依赖于M和D的值2.日期和时间类型
表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。
TIMESTAMP类型有专有的自动更新特性，将在后面描述。
类型大小 (*字节)**范围格式用途DATE31000-01-01/9999-12-31YYYY-MM-DD日期值TIME3'-838:59:59'/'838:59:59'HH:MM:SS时间值或持续时间YEAR11901/2155YYYY年份值DATETIME81000-01-01 00:00:00/9999-12-31 23:59:59YYYY-MM-DD HH:MM:SS混合日期和时间值TIMESTAMP41970-01-01 00:00:00/2037 年某时YYYYMMDD HHMMSS混合日期和时间值，时间戳3.字符串类型
字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。
类型大小用途CHAR0-255字节定长字符串VARCHAR0-65535 字节变长字符串TINYBLOB0-255字节不超过 255 个字符的二进制字符串TINYTEXT0-255字节短文本字符串BLOB0-65 535字节二进制形式的长文本数据TEXT0-65 535字节长文本数据MEDIUMBLOB0-16 777 215字节二进制形式的中等长度文本数据MEDIUMTEXT0-16 777 215字节中等长度文本数据LONGBLOB0-4 294 967 295字节二进制形式的极大文本数据LONGTEXT0-4 294 967 295字节极大文本数据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.数据库操作
数据库: SHOW DATABASES;
创建数据库: CREATE DATABASE IF NOT EXISTS people;
切换数据库: USE people;
删除数据库: DROP DATABASE IF EXISTS people;
查看当前数据库库信息: SHOW CREATE DATABASE people;
修改数据库的选项信息: ALTER DATABASE people;
2.数据表操作
显示数据库里所有数据表的信息: SHOW TABLE STATUS FROM people;
显示全部数据表: SHOW TABLES;
单张表：show tables from df
清空数据表: TRUNCATE df;
表检测: CHECK TABLE df;
表优化: OPTIMIZE TABLE df;
表修复: REPAIR TABLE df;
表分析: ANALYZE TABLE df;
分析表 键状态是否正确: ANALYZE TABLE orders;
检查表是否存在错误:check TABLE orders,orderitems QUICK;# QUICK只进行快速扫描
优化表OPTIMIZE TABLE,消除删除和更新造成的磁盘碎片,从而减少空间的浪费:OPTIMIZE TABLE orders;
查询表结构: DESC df;DESCRIBE df; EXPLAIN df;SHOW COLUMNS FROM df;
复制表: CREATE TABLE de LIKE df; SELECT * INTOIN 'hw' FROM df;
修改表名: RENAME TABLE de TO people.dh;（可将表移动到另一个数据库）
修改表字段: ALTER TABLE df ADD/DROP/CHANGE
拼接字段:SELECT CONCAT(us,'(',tim,')') FROM df ORDER BY us ASC;result=>us(tim)
添加主键约束：alter TABLE 表名 ADD CONSTRAINT 主键 （形如：PK_表名） PRIMARY KEY 表名(主键字段);
添加外键约束：alter TABLE 从表 ADD CONSTRAINT 外键（形如：FK_从表_主表） FOREIGN KEY 从表(外键字段) REFERENCES 主表(主键字段);
删除主键约束：alter TABLE 表名 DROP PRIMARY KEY;
删除外键约束：alter TABLE 表名 DROP FOREIGN KEY 外键（区分大小写）;
-- 添加外键约束
CREATE TABLE stu(sid INT PRIMARY KEY,NAME VARCHAR(50) NOT NULL);
-- 添加外键约束方式一
CREATE TABLE score1(score DOUBLE,sid INT,CONSTRAINT fk_stu_score1_sid FOREIGN KEY(sid) REFERENCES stu(sid));
-- 添加外键约束方式二(若表已存在，可用这种)
CREATE TABLE score1(score DOUBLE,sid INT);
ALTER TABLE score1 ADD CONSTRAINT fk_sid FOREIGN KEY(sid) REFERENCES stu(sid)
三、索引
CREATE UNIQUE INDEX qw ON df(us); #创建不重复索引
ALTER TABLE df ADD UNIQUE INDEX wq(id); #添加索引
SHOW INDEX FROM df;#检索索引
DROP INDEX qw ON people.df; #删除索引
ALTER TABLE df DROP INDEX wq; #删除索引
四、视图
CREATE VIEW shitu AS SELECT us FROM df; 创建视图
ALTER VIEW shitu AS SELECT us FROM df WHERE us='gf'; 修改视图
SELECT * FROM shitu; 查看视图结果
DROP VIEW IF EXISTS shitu;删除视图
五、函数
文本处理函数
ASCII(CHAR)返回字符的ASCII码值
BIT_LENGTH(str)返回字符串的比特长度
CONCAT(s1,s2...,sn)将s1,s2...,sn连接成字符串
CONCAT_WS(sep,s1,s2...,sn)将s1,s2...,sn连接成字符串，并用sep字符间隔
INSERT(str,X,Y,INSTR) 将字符串str从第x位置开始，y个字符长的子串替换为字符串instr，返回结果
FIND_IN_SET(str,LIST)分析逗号分隔的list列表，如果发现str，返回str在list中的位置
LCASE(str)或LOWER(str) 返回将字符串str中所有字符改变为小写后的结果
LEFT(str,X)返回字符串str中最左边的x个字符
LENGTH(s)返回字符串str中的字符数
LOCATE(str)找出str串的一个子串
LOWER(str)将str串转换为小写
LTRIM(str) 从字符串str中切掉开头的空格
POSITION(SUBSTR,str) 返回子串substr在字符串str中第一次出现的位置
QUOTE(str) 用反斜杠转义str中的单引号
REPEAT(str,srchstr,rplcstr)返回字符串str重复x次的结果
REVERSE(str) 返回颠倒字符串str的结果
RIGHT(str,X) 返回字符串str中最右边的x个字符
RTRIM(str) 返回字符串str尾部的空格
SOUNDEX(str)返回str串的SOUNDEX值
STRCMP(s1,s2)比较字符串s1和s2
SUBSTRING()返回子串的字符
TRIM(str)去除字符串首部和尾部的所有空格
UCASE(str)或UPPER(str) 返回将字符串str中所有字符转变为大写后的结果
日期和时间处理函数
ADDDATE()增加一个日期（天、周等）
ADDTIME()增加一个时间（时、分等）
CURDATE()或CURRENT_DATE() 返回当前的日期
CURTIME()或CURRENT_TIME() 返回当前的时间
DATE()返回日期时间的日期部分
DATE_ADD(DATE,INTERVAL INT keyword)返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化),如：SELECTDATE_ADD(CURRENT_DATE,INTERVAL 6 MONTH);
DATE_FORMAT(DATE,fmt)依照指定的fmt格式格式化日期date值
DATE_SUB(DATE,INTERVAL INT keyword)返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化),如：SELECTDATE_SUB(CURRENT_DATE,INTERVAL 6 MONTH);
DAY()返回一个日期的天数部分
DAYOFWEEK(DATE) 返回date所代表的一星期中的第几天(1~7)
DAYOFMONTH(DATE)返回date是一个月的第几天(1~31)
DAYOFYEAR(DATE) 返回date是一年的第几天(1~366)
DAYNAME(DATE) 返回date的星期名，如：SELECT DAYNAME(CURRENT_DATE);
FROM_UNIXTIME(ts,fmt)根据指定的fmt格式，格式化UNIX时间戳ts
HOUR(TIME) 返回time的小时值(0~23)
MINUTE(TIME) 返回time的分钟值(0~59)
MONTH(DATE) 返回date的月份值(1~12)
MONTHNAME(DATE) 返回date的月份名，如：SELECT MONTHNAME(CURRENT_DATE);
NOW() 返回当前的日期和时间
QUARTER(DATE) 返回date在一年中的季度(1~4)，如SELECT QUARTER(CURRENT_DATE);
SECOND()返回一个时间的秒部分
TIME()返回一个日期时间的时间部分
WEEK(DATE) 返回日期date为一年中第几周(0~53)
YEAR(DATE) 返回日期date的年份(1000~9999)
获取当前系统时间：
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP());
SELECT EXTRACT(YEAR_MONTH FROM CURRENT_DATE);
SELECT EXTRACT(DAY_SECOND FROM CURRENT_DATE);
SELECT EXTRACT(HOUR_MINUTE FROM CURRENT_DATE);
返回两个日期值之间的差值(月数)：
SELECT PERIOD_DIFF(200302,199802);
在Mysql中计算年龄：
SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(tim)),'%Y')+0 AS us FROM df;
常用数值处理函数
ABS(X) 返回x的绝对值
BIN(X) 返回x的二进制（OCT返回八进制，HEX返回十六进制）
CEILING(X) 返回大于x的最小整数值
COS(X)返回角度x的余弦
EXP(X) 返回值e（自然对数的底）的x次方
FLOOR(X) 返回小于x的最大整数值
GREATEST(x1,x2,...,xn)返回集合中最大的值
LEAST(x1,x2,...,xn)返回集合中最小的值
LN(X)返回x的自然对数
LOG(X,Y)返回x的以y为底的对数
MOD(X,Y)返回x/y的模（余数）
PI()返回pi的值（圆周率）
RAND()返回０到１内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。
ROUND(X,Y)返回参数x的四舍五入的有y位小数的值
SIGN(X) 返回代表数字x的符号的值
SIN(X)返回角度x的正弦
SQRT(X) 返回一个数的平方根
TAN(X)返回角度x的正切
TRUNCATE(X,Y) 返回数字x截短为y位小数的结果
聚合函数(常用于GROUP BY从句的SELECT查询中)
AVG(col)返回指定列的平均值
COUNT(col)返回指定列中非NULL值的个数
MIN(col)返回指定列的最小值
MAX(col)返回指定列的最大值
SUM(col)返回指定列的所有值之和
GROUP_CONCAT(col) 返回由属于一组的列值连接组合而成的结果
加密函数
AES_ENCRYPT(str,KEY)返回用密钥key对字符串str利用高级加密标准算法加密后的结果，调用AES_ENCRYPT的结果是一个二进制字符串，以BLOB类型存储
AES_DECRYPT(str,KEY)返回用密钥key对字符串str利用高级加密标准算法解密后的结果
DECODE(str,KEY) 使用key作为密钥解密加密字符串str
ENCRYPT(str,salt) 使用UNIXcrypt()函数，用关键词salt(一个可以惟一确定口令的字符串，就像钥匙一样)加密字符串str
ENCODE(str,KEY) 使用key作为密钥加密字符串str，调用ENCODE()的结果是一个二进制字符串，它以BLOB类型存储
MD5() 计算字符串str的MD5校验和
PASSWORD(str) 返回字符串str的加密版本，这个加密过程是不可逆转的，和UNIX密码加密过程使用不同的算法。
SHA() 计算字符串str的安全散列算法(SHA)校验和
SELECT ENCRYPT('root','salt');
SELECT ENCODE('xufeng','key');
SELECT DECODE(ENCODE('xufeng','key'),'key');#加解密放在一起
SELECT AES_ENCRYPT('root','key');
SELECT AES_DECRYPT(AES_ENCRYPT('root','key'),'key');
SELECT MD5('123456');
SELECT SHA('123456');
格式化函数DATE_FORMAT(DATE,fmt)依照字符串fmt格式化日期date值
FORMAT(X,Y) 把x格式化为以逗号隔开的数字序列，y是结果的小数位数
INET_ATON(ip) 返回IP地址的数字表示
INET_NTOA(num) 返回数字所代表的IP地址
TIME_FORMAT(TIME,fmt)依照字符串fmt格式化时间time值
SELECT FORMAT(34234.34323432,3);
SELECT DATE_FORMAT(NOW(),'%W,%D %M %Y %r');
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d');
SELECT DATE_FORMAT(19990330,'%Y-%m-%d');
SELECT DATE_FORMAT(NOW(),'%h:%i %p');
SELECT INET_ATON('10.122.89.47');
SELECT INET_NTOA(175790383);
类型转化函数
CAST() 类型有：BINARY,CHAR,DATE,TIME,DATETIME,SIGNED,UNSIGNED
SELECT CAST(NOW() AS SIGNED INTEGER),CURDATE()+0;
SELECT 'f'=BINARY 'F','f'=CAST('F' AS BINARY);
系统信息函数
DATABASE() 返回当前数据库名
BENCHMARK(COUNT,expr)将表达式expr重复运行count次
CONNECTION_ID() 返回当前客户的连接ID
FOUND_ROWS() 返回最后一个SELECT查询进行检索的总行数
USER()或SYSTEM_USER()返回当前登陆用户名
VERSION() 返回MySQL服务器的版本
SELECT DATABASE(),VERSION(),USER();
SELECTBENCHMARK(9999999,LOG(RAND()*PI()));
六、游标
创建、打开、关闭游标 # 定义名为ordernumbers的游标，检索所有订单
DELIMITER //
CREATE PROCEDURE processorders()
BEGIN
-- decalre the cursor 声明游标
declare ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
-- open the cursor 打开游标
open ordernumbers;
-- close the cursor 关闭游标
close ordernumbers;
END //
DELIMITER ;
-- 使用游标数据
# 例1：检索 当前行 的order_num列，对数据不做实际处理
DELIMITER //
CREATE PROCEDURE processorders()
BEGIN
-- declare local variables 声明局部变量
DECLARE o INT;
-- decalre the cursor 声明游标
declare ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
-- open the cursor 打开游标
open ordernumbers;
-- get order number 获得订单号
FETCH ordernumbers INTO o;
/*fetch检索 当前行 的order_num列（将自动从第一行开始）到一个名为o的局部声明变量中。
对检索出的数据不做任何处理。*/
-- close the cursor 关闭游标
close ordernumbers;
END //
DELIMITER ;
# 例2：循环检索数据，从第一行到最后一行，对数据不做实际处理
DELIMITER //
CREATE PROCEDURE processorders()
BEGIN
-- declare local variables 声明局部变量
DECLARE done BOOLEAN DEFAULT 0;
DECLARE o INT;
-- decalre the cursor 声明游标
declare ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
-- declare continue handler
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done =1;
-- SQLSTATE '02000'是一个未找到条件，当REPEAT由于没有更多的行供循环而不能继续时，出现这个条件。
-- open the cursor 打开游标
open ordernumbers;
-- loop through all rows 遍历所有行
REPEAT
-- get order number 获得订单号
FETCH ordernumbers INTO o;
-- FETCH在REPEAT内，因此它反复执行直到done为真
-- end of loop
UNTIL done END REPEAT;
-- close the cursor 关闭游标
close ordernumbers;
END //
DELIMITER ;
# 例3：循环检索数据，从第一行到最后一行，对取出的数据进行某种实际的处理
DELIMITER //
CREATE PROCEDURE processorders()
BEGIN
-- declare local variables 声明局部变量
DECLARE done BOOLEAN DEFAULT 0;
DECLARE o INT;
DECLARE t DECIMAL(8,2);
-- declare the cursor 声明游标
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
-- declare continue handler
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
-- create a table to store the results 新建表以保存数据
CREATE TABLE IF NOT EXISTS ordertotals
(order_num INT,total DECIMAL(8,2));
-- open the cursor 打开游标
OPEN ordernumbers;
-- loop through all rows 遍历所有行
REPEAT
-- get order number 获取订单号
FETCH ordernumbers INTO o;
-- get the total for this order 计算订单金额
CALL ordertotal(o,1,t);# 参见23章代码，已创建可使用
-- insert order and total into ordertotals 将订单号、金额插入表ordertotals内
INSERT INTO ordertotals(order_num,total) VALUES(o,t);
-- end of loop
UNTIL done END REPEAT;
-- close the cursor 关闭游标
close ordernumbers;
END //
DELIMITER ;
# 调用存储过程 precessorders()
CALL processorders();
# 输出结果
SELECT * FROM ordertotals;
七、触发器
１、MySQL触发器的创建语法：
CREATE
TRIGGER trigger_name
trigger_time trigger_event
ON table_name
FOR EACH ROW
trigger_body
2、MySQL创建语法中的关键词解释：
字段 含义 可能的值
DEFINER= 可选参数，指定创建者， DEFINER='root@%'
默认为当前登录用户（CURRENT_USER）；
该触发器将以此参数指定的用户执行， DEFINER=CURRENT_USER
所以需要考虑权限问题；
trigger_name 触发器名称，最好由表名+触发事件关键词+触发时间关键词组成；
trigger_time 触发时间，在某个事件之前还是之后；BEFORE、AFTER
INSERT：插入操作触发器，INSERT、LOAD DATA、REPLACE时触发；
UPDATE：更新操作触发器，UPDATE操作时触发；
trigger_event触发事件，如插入时触发、删除时触发；DELETE：删除操作触发器，DELETE、REPLACE操作时触发；
INSERT、UPDATE、DELETE
table_name 触发操作时间的表名；
可选参数，如果定义了多个具有相同触发事件和触法时间的触发器时（
如：BEFORE UPDATE），默认触发顺序与触发器的创建顺序一致，可以
trigger_order 使用此参数来改变它们触发顺序。mysql 5.7.2起开始支持此参数。
FOLLOWS：当前创建触发器在现有触发器之后激活；FOLLOWS、PRECEDES
PRECEDES：当前创建触发器在现有触发器之前激活；
trigger_body 触发执行的SQL语句内容，一般以begin开头，end结尾 BEGIN .. END
触发执行语句内容（trigger_body）中的OLD，NEW
触发执行语句内容（trigger_body）中的OLD，NEW：在trigger_body中，
我们可以使用NEW表示将要插入的新行（相当于MS SQL的INSERTED），
OLD表示将要删除的旧行（相当于MS SQL的DELETED）。
通过OLD，NEW中获取它们的字段内容，方便在触发操作中使用，
下面是对应事件是否支持OLD、NEW的对应关系：
事件 OLD NEW
INSERT × √
DELETE √ ×
UPDATE √ √
由于UPDATE相当于删除旧行（OLD），然后插入新行（NEW），所以UPDATE同时支持OLD、NEW；
DELIMITER $
... --触发器创建语句；
$ --提交创建语句；
DELIMITER ;
select * FROM information_schema.triggers;
SHOW TRIGGERS; #查看触发器
-- 通过information_schema.triggers表查看触发器：
select * FROM information_schema.triggers;
-- mysql 查看当前数据库的触发器
SHOW TRIGGERS;
-- mysql 查看指定数据库"people"的触发器
SHOW TRIGGERS FROM people;
创建测试表
DROP TABLE IF EXISTS tb;
CREATE TABLE IF NOT EXISTS tb(id INT,username CHAR(10),pass VARCHAR(20),ct INT);
CREATE TABLE IF NOT EXISTS bt(fid INT,username CHAR(10),pass VARCHAR(20),ct INT);
创建触发器
DELIMITER $
#drop trigger if exists df_names$ 删除前先判断触发器是否存在
CREATEDEFINER =CURRENT_USER
TRIGGER df_names
BEFORE INSERT ON tb
AFTER UPDATE ON bt
FOR EACH ROW
BEGIN
#set new.ct=new.id*5;
#SET @ct=12; 变量
#SET @pass='hjfd';
IF old.type=1 THEN
UPDATE bt SET ct=old.ct WHERE fid=old.id;
ELSE IF old.type=2 THEN
UPDATE bt SET pass=old.pass WHERE fid=old.id;
END$
DELIMITER;
测试
INSERT INTO tb(id) VALUES(4);
SELECT *FROM tb;
八、存储过程
-- 创建存储过程
# 返回产品平均价格的存储过程
DELIMITER //
CREATE PROCEDURE productpricing()
BEGIN
select AVG(prod_price) AS priceaverage FROM products;
END //
DELIMITER ;
# 调用上述存储过程
CALL productpricing();
-- 删除存储过程,请注意:没有使用后面的()，只给出存储过程名。
DROP PROCEDURE productpricing;
-- 使用参数 out
# 重新定义存储过程productpricing
DELIMITER //
CREATE PROCEDURE productpricing(OUT pl DECIMAL(8,2), OUT ph DECIMAL(8,2), OUT pa DECIMAL(8,2))
BEGIN
select MIN(prod_price) INTO pl FROM products;
SELECT MAX(prod_price) INTO ph FROM products;
select AVG(prod_price) INTO pa FROM products;
END //
DELIMITER ;
# 为调用上述存储过程，必须指定3个变量名
CALL productpricing(@pricelow,@pricehigh,@priceaverage);
# 显示检索出的产品平均价格
SELECT @priceaverage;
# 获得3个值
SELECT @pricehigh,@pricelow,@priceaverage;
-- 使用参数 in 和 out
# 使用IN和OUT参数,存储过程ordertotal接受订单号并返回该订单的合计
DELIMITER //
CREATE PROCEDURE ordertotal(
in onumber INT, # onumber定义为IN，因为订单号被传入存储过程
OUT ototal DECIMAL(8,2) # ototal为OUT，因为要从存储过程返回合计
)
BEGIN
select SUM(item_price*quantity) FROM orderitems
WHERE order_num = onumber
INTO ototal;
END //
DELIMITER ;
# 给ordertotal传递两个参数；
# 第一个参数为订单号，第二个参数为包含计算出来的合计的变量名
CALL ordertotal(20005,@total);
# 显示此合计
SELECT @total;
# 得到另一个订单的合计显示
CALL ordertotal(20009,@total);
SELECT @total;
-- 建立智能存储过程
# 获得与以前一样的订单合计，但只针对某些顾客对合计增加营业税
-- Name:ordertotal
-- Parameters: onumber = order number
-- taxable = 0 if not taxable, 1 if taxable
-- ototal= order total variable
DELIMITER //
CREATE PROCEDURE ordertotal(
in onumber INT,
in taxable BOOLEAN,
OUT ototal DECIMAL(8,2)
) COMMENT 'obtain order total, optionally adding tax'
BEGIN
-- declare variable for total 定义局部变量total
DECLARE total DECIMAL(8,2);
-- declare tax percentage 定义局部变量税率
DECLARE taxrate INT DEFAULT 6;
-- get the order total 获得订单合计
SELECT SUM(item_price * quantity)
FROM orderitems
WHERE order_num = onumber INTO total;
-- is this taxable? 是否要增加营业税？
if taxable THEN
-- Yes,so add taxrate to the total 给订单合计增加税率
select total+(total/100*taxrate) INTO total;
end IF;
-- and finally,save to out variable 最后，传递给输出变量
SELECT total INTO ototal;
END //
DELIMITER ;
# 调用上述存储过程，不加税
CALL ordertotal(20005,0,@total);
SELECT @total;
# 调用上述存储过程，加税
CALL ordertotal(20005,1,@total);
SELECT @total;
# 显示用来创建一个存储过程的CREATE语句
SHOW CREATE PROCEDURE ordertotal;
# 获得包括何时、由谁创建等详细信息的存储过程列表
# 该语句列出所有存储过程
SHOW PROCEDURE STATUS;
# 过滤模式
SHOW PROCEDURE STATUS LIKE 'ordertotal';
九、事务
SET AUTOCOMMIT=off ;禁用或启用事务的自动提交模式off ON
SET SESSION AUTOCOMMIT = OFF；禁用或启用事务的session自动提交模式 off ON
SHOW VARIABLES LIKE '%auto%'; -- 查看变量状态
执行DML语句是其实就是开启一个事务
只能回滚insert、delete和update语句
对于create、drop、alter这些无法回滚事务只对DML有效果
rollback，或者commit后事务就结束了
自动提交模式用于决定新事务如何及何时启动
START TRANSACTION; 启用自动提交模式下显式地启动事务
COMMIT和ROLLBACK;禁用自动提交模式显式地提交或回滚
-- 事务 transaction 指一组sql语句
-- 回退 rollback 指撤销指定sql语句的过程
-- 提交 commit 指将未存储的sql语句结果写入数据库表
-- 保留点 savepoint 指事务处理中设置的临时占位符，可以对它发布回退（与回退整个事务处理不同）
-- 控制事务处理
# 开始事务及回退
SELECT * FROM ordertotals; # 查看ordertotals表显示不为空
START TRANSACTION; # 开始事务处理
DELETE FROM ordertotals; # 删除ordertotals表中所有行
SELECT * FROM ordertotals; # 查看ordertotals表显示 为空
ROLLBACK; # rollback语句回退
SELECT * FROM ordertotals; # rollback后，再次查看ordertotals表显示不为空
# commit 提交
START TRANSACTION;
DELETE FROM orderitems WHERE order_num = 20010;
DELETE FROM orders WHERE order_num = 20010;
COMMIT; # 仅在上述两条语句不出错时写出更改
# savepoint 保留点
# 创建保留点
SAVEPOINT delete1;
# 回退到保留点
ROLLBACK TO delete1;
# 释放保留点
RELEASE SAVEPOINT delete1;
-- 更改默认的提交行为
SET autocommit = 0;# 设置autocommit为0（假）指示MySQL不自动提交更改
十、备份与还原
备份一个数据库:mysqldump -u root -p --opt people> df.txt#--opt优化执行速度
备份两个数据库:mysqldump -u root -p --opt --databases people hw > all.txt
备份全部数据库:mysqldump -u root -p --opt --all-DATABASES > all.txt
恢复数据库:mysqldump -u root -p --opt --databases people hw < all.txt
1. 导出一张表
mysqldump -u用户名 -p密码 库名 表名 > 文件名(D:/a.sql)
2. 导出多张表
mysqldump -u用户名 -p密码 库名 表1 表2 表3 > 文件名(D:/a.sql)
3. 导出所有表
mysqldump -u用户名 -p密码 库名 > 文件名(D:/a.sql)
4. 导出一个库
mysqldump -u用户名 -p密码 --lock-ALL-TABLES --database 库名 > 文件名(D:/a.sql)
可以-w携带WHERE条件
十一、用户账号
创建账户:CREATE USER IF NOT EXISTS 'hw'@'localhost' IDENTIFIED BY '5201314'; #创建用户hw,密码5201314
给该用户授予所有权限并可授权给其它用户:GRANT ALL PRIVILEGES ON people.df TO 'hw'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;
重命名用户名: RENAME USER 'hw' TO 'gh'; 必须将localhost改为%
如果希望该用户能够在任何机器上登陆mysql，则将localhost改为 "%"
授权给其它用户 WITH GRANT OPTION
privileges包括：
alter：修改数据库的表
create：创建新的数据库或表
delete：删除表数据
drop：删除数据库/表
index：创建/删除索引
insert：添加表数据
select：查询表数据
update：更新表数据
all：允许任何操作
usage：只允许登录
刷新权限,使新创建的用户能够使用: FLUSH PRIVILEGES;
收回用户权限: REVOKE ALL PRIVILEGES ON people.df FROM 'root'@'localhost';
删除用户: DROP USER IF EXISTS 'hw'@'localhost';
设置指定用户的密码:SET PASSWORD FOR'hw'@'localhost' = PASSWORD('123321');
UPDATE USER SET PASSWORD =PASSWORD('123321') WHERE USER = 'hw';
设置密码: SET PASSWORD = PASSWORD('123321');
十二、其他
SHOW STATUS;显示广泛的服务器状态信息
SHOW PROCEDURE STATUS;
SHOW GRANTS;显示授予用户的安全权限
SHOW ERRORS;显示服务器的错误信息
SHOW WARNINGS;显示服务器的警告信息
SHOW PROCESSLIST;显示哪些线程正在运行
SHOW VARIABLES;显示系统变量信息
SELECT DATABASE(); 查看当前数据库
SELECT NOW(), USER(), VERSION():显示当前时间、用户名、数据库版本
SHOW ENGINES 引擎名 {LOGS|STATUS}:显示存储引擎的日志和状态信息
SHOW VARIABLES LIKE 'character%'; SHOW VARIABLES LIKE 'collation%'; 确定所用系统的字符集和校对
SHOW VARIABLES LIKE 'character_set_client%'; 客户端向服务器发送数据时使用的编码
SHOW VARIABLES LIKE 'character_set_results%'; 服务器端将结果返回给客户端所使用的编码
SHOW VARIABLES LIKE 'character_set_connection%'; 连接层编码
SHOW CHARACTER SET;查看所支持的字符集完整列表
SHOW COLLATION;查看所支持校对的完整列表,以及它们适用的字符集
SET character_set_client = gbk;
SET character_set_results = gbk;
SET character_set_connection = gbk;
SET NAMES GBK;-- 相当于完成以上三个设置
创建window服务:sc CREATE mysql binPath= mysqld_bin_path
数据文件目录:DATA DIRECTORY='目录'
索引文件目录:INDEX DIRECTORY = '目录'
十三、总结
这篇文章主要讲解了MySQL数据库的数据类型、库和表常见操作、索引、视图、函数、游标、触发器、存储过程、事务、备份与还原、用户账号、其它等知识点，希望对大家的学习有帮助。
原文地址：https://mp.weixin.qq.com/s/gqrTl4RIese3nFNZELWSjw
http://www.zzvips.com/article/194819.html
页:
[1]