盘点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。下面的表显示了需要的每个整数类型的存储和范围。
类型
大小
范围（有符号）
范围（无符号）
用途
| TINYINT | 1 字节 | (-128，127) | (0，255) | 小整数值 |
| SMALLINT | 2 字节 | (-32 768，32 767) | (0，65 535) | 大整数值 |
| MEDIUMINT | 3 字节 | (-8 388 608，8 388 607) | (0，16 777 215) | 大整数值 |
| INT或INTEGER | 4 字节 | (-2 147 483 648，2 147 483 647) | (0，4 294 967 295) | 大整数值 |
| BIGINT | 8 字节 | (-9 233 372 036 854 775 808，9 223 372 036 854 775 807) | (0，18 446 744 073 709 551 615) | 极大整数值 |
| FLOAT | 4 字节 | (-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) | 单精度 浮点数值 |
| DOUBLE | 8 字节 | (-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的值 | 依赖于M和D的值 | 小数值 |
2.日期和时间类型
表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。
TIMESTAMP类型有专有的自动更新特性，将在后面描述。
类型
大小 (*字节)**
范围
格式
用途
| 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/2037 年某时 | YYYYMMDD HHMMSS | 混合日期和时间值，时间戳 |
3.字符串类型
字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。
类型
大小
用途
| CHAR | 0-255字节 | 定长字符串 |
| VARCHAR | 0-65535 字节 | 变长字符串 |
| TINYBLOB | 0-255字节 | 不超过 255 个字符的二进制字符串 |
| TINYTEXT | 0-255字节 | 短文本字符串 |
| BLOB | 0-65 535字节 | 二进制形式的长文本数据 |
| TEXT | 0-65 535字节 | 长文本数据 |
| MEDIUMBLOB | 0-16 777 215字节 | 二进制形式的中等长度文本数据 |
| MEDIUMTEXT | 0-16 777 215字节 | 中等长度文本数据 |
| LONGBLOB | 0-4 294 967 295字节 | 二进制形式的极大文本数据 |
| LONGTEXT | 0-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 * INTO IN '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[DEFINER = { 'user' | CURRENT_USER }]
TRIGGER trigger_name
trigger_time trigger_event
ON table_name
FOR EACH ROW
[trigger_order]
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$ 删除前先判断触发器是否存在
CREATE DEFINER =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数据库的数据类型、库和表常见操作、索引、视图、函数、游标、触发器、存储过程、事务、备份与还原、用户账号、其它等知识点，希望对大家的学习有帮助。
学习资料见知识星球。
以上就是今天要分享的技巧，你学会了吗？若有什么问题，欢迎在下方留言。
快来试试吧，小琥 my21ke007。获取 1000个免费 Excel模板福利！
更多技巧， www.excelbook.cn
欢迎 加入 零售创新 知识星球，知识星球主要以数据分析、报告分享、数据工具讨论为主；
1、价值上万元的专业的PPT报告模板。
2、专业案例分析和解读笔记。
3、实用的Excel、Word、PPT技巧。
4、VIP讨论群，共享资源。
5、优惠的会员商品。
6、一次付费只需99元，即可下载本站文章涉及的文件和软件。
共有 0 条评论