MySQL系列：（3）MySQL加强
- 2020 年 1 月 10 日
- 笔记
1、SQL语句分类
DDL数据定义语言：create / drop / alter
DML数据操作语句：insert / delete /update / truncate
2、数据约束
2.1、什么是数据约束
2.2、默认值
作用： 当用户对使用默认值的字段不插入值的时候，就使用默认值。
CREATE TABLE T_Persons( Id INT, NAME NVARCHAR(20), Gender NVARCHAR(2) DEFAULT '男' )
2.3、非空
CREATE TABLE T_Persons( Id INT NOT NULL, NAME NVARCHAR(20), Gender NVARCHAR(2) )
2.4、唯一
注意：1）唯一字段可以插入null；2）唯一字段可以插入多个null
CREATE TABLE T_Persons( Id INT UNIQUE, NAME NVARCHAR(20), Gender NVARCHAR(2) )
2.5、主键
1）通常情况下，每张表都会设置一个主键字段。用于标记表中的每条记录的唯一性。
2）建议不要选择表的包含业务含义的字段作为主键，建议给每张表独立设计一个非业务含义的id字段。
CREATE TABLE T_Persons( Id INT PRIMARY KEY, NAME NVARCHAR(20), Gender NVARCHAR(2) )
2.6、自增长
CREATE TABLE T_Persons( Id INT AUTO_INCREMENT, NAME NVARCHAR(20), Gender NVARCHAR(2) ) ------------------------------------------------------------ CREATE TABLE T_Persons( Id INT(4) ZEROFILL PRIMARY KEY AUTO_INCREMENT,-- 自增长，从0开始 ZEROFILL 零填充 NAME NVARCHAR(20), Gender NVARCHAR(2) ) DELETE FROM T_Persons; -- 不能影响自增长约束 TRUNCATE TABLE T_Persons;-- 可以影响自增长约束
2.7、外键
语法：CONSTRAINT 外键名 FOREIGN KEY(副表的外键字段) REFERENCES 主表(主表的主键)
-- 部门表（主表） CREATE TABLE T_Department( Id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20) ) -- 员工表（副表/从表） CREATE TABLE T_Employee( Id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(10), DepartmentId INT, CONSTRAINT employee_department_fk FOREIGN KEY(DepartmentId) REFERENCES T_Department(Id) )
1）被约束的表称为副表，约束别人的表称为主表，外键设置在副表上的！！！
2.8、级联操作
CREATE TABLE T_Employee( Id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(10), DepartmentId INT, CONSTRAINT employee_department_fk FOREIGN KEY(DepartmentId) REFERENCES T_Department(Id) ON UPDATE CASCADE ON DELETE CASCADE )
3、数据库三大范式和MySQL变量
3.1、三大范式
如果在T_Persons表中的name字段中存储“李东华|李叶蛾”，就会违反第一范式。
如果在T_Persons表中的name字段中存储“李东华”，在oldname字段中存储“李叶蛾”，就会符合第一范式。
第二范式： 在第一范式的基础上，要求每张表只表达一个意思。表的每个字段都和表的主键有依赖。
第三范式： 在第二范式基础，要求每张表的主键之外的其他字段都只能和主键有直接决定依赖关系。
第一范式，是对“列”进行约束，每个字段是不可分割的独立单元；
第三范式，是对“表与表之间的关系”进行约束，副表只能包含主表的primary key。
3.2、MySQL变量
全局变量（内置变量）：mysql数据库内置的变量 （所有连接都起作用）
全局变量中的两个值：character_set_client和character_set_results。可以查询SHOW VARIABLES LIKE 'character_%';
character_set_client: mysql服务器的接收数据的编码
character_set_results：mysql服务器输出数据的编码
会话变量： 只存在于当前客户端与数据库服务器端的一次连接当中。如果连接断开，那么会话变量全部丢失！
局部变量： 在存储过程中使用的变量就叫局部变量。只要存储过程执行完毕，局部变量就丢失！！
4、存储过程
4.1、什么是存储过程
之前的sql没有条件判断，没有循环；存储过程带上流程控制语句（if while）。
4.2、存储过程特点
1）执行效率非常快！存储过程是在数据库的服务器端执行的！！！
4.3、存储过程语法
INOUT： 表示输入输出参数，既可以输入功能，也可以输出功能
4.3.1、不带参数的存储过程
-- 创建存储过程 DELIMITER $ -- 声明存储过程的结束符 CREATE PROCEDURE sp_findAll() BEGIN SELECT * FROM T_Persons; END $ -- 执行存储过程 CALL sp_findAll(); -- CALL 存储过程名称(参数);
4.3.2、带有输入参数的存储过程
-- 创建存储过程 DELIMITER $ CREATE PROCEDURE sp_findById(IN pid INT) BEGIN SELECT * FROM T_Persons WHERE Id=pid; END $ -- 执行存储过程 CALL sp_findById(2);
4.3.3、带有输出参数的存储过程
-- 创建存储过程 DELIMITER $ CREATE PROCEDURE sp_findCount(OUT str VARCHAR(20),OUT num INT) BEGIN SET str = 'hello world'; SELECT COUNT(Id) INTO num FROM T_Persons; END $ -- 执行存储过程 CALL sp_findCount(@str,@num); -- 查看存储过程的OUT类型的结果 SELECT @str,@num;
4.3.4、带有输入输出参数的存储过程
-- 创建存储过程 DELIMITER $ CREATE PROCEDURE sp_testInOut(INOUT n INT) BEGIN SELECT n; SET n = 500; END $ -- 执行存储过程 SET @n = 10; CALL sp_testInOut(@n); SELECT @n;
4.3.5、带有条件判断的存储过程
-- 创建存储过程 DELIMITER $ CREATE PROCEDURE sp_testIf(IN num INT,OUT str VARCHAR(20)) BEGIN IF num = 1 THEN SET str = '星期一'; -- 注意用SET进行赋值 ELSEIF num = 2 THEN SET str = '星期二'; ELSEIF num = 3 THEN SET str = '星期三'; ELSE SET str = '输入错误！'; END IF;-- 注意这里有分号结尾 END $ -- 执行存储过程 CALL sp_testIf(4,@str); SELECT @str;
4.3.6、带有循环功能的存储过程
-- 创建存储过程 DELIMITER $ CREATE PROCEDURE sp_testWhile(IN num INT,OUT result INT) BEGIN DECLARE i INT DEFAULT 1; DECLARE iResult INT DEFAULT 0; WHILE i <= num DO SET iResult = iResult + i; SET i = i + 1; END WHILE; SET result = iResult; END $ -- 执行存储过程 CALL sp_testWhile(100,@result); SELECT @result;
4.3.7、使用查询的结果赋值给变量（INTO）
-- 创建存储过程 DELIMITER $ CREATE PROCEDURE sp_testINTO(IN pid INT,OUT vname VARCHAR(20)) BEGIN SELECT NAME INTO vname FROM T_Persons WHERE Id=pid; END $ -- 执行存储过程 CALL sp_testINTO(2,@vname); SELECT @vname;
5、触发器
当操作了某张表时，希望同时触发一些动作/行为，可以使用触发器完成！！
DELIMITER $ CREATE TRIGGER 触发器的名字 AFTER INSERT/UPDATE/DELETE ON 表名 FOR EACH ROW BEGIN -- SQL语句 END $
CREATE TABLE T_Logs( Id INT PRIMARY KEY AUTO_INCREMENT, Content VARCHAR(20) ) DELIMITER $ CREATE TRIGGER tri_Add AFTER INSERT ON T_Persons FOR EACH ROW BEGIN INSERT INTO T_Logs(Content) VALUES('插入了一条数据'); END $
6、MySQL用户权限
而权限账户，只拥有部分权限（CURD）例如，只能操作某个数据库的某张表
UPDATE USER SET PASSWORD=PASSWORD('root') WHERE USER='root';
在mysql中，密码password是经md5加密的(单向加密)
USE mysql; SELECT * FROM USER; SELECT PASSWORD('root'); -- *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B
GRANT 权限 ON 数据库名.表名 TO '用户名'@'IP地址' IDENTIFIED BY '密码';
GRANT SELECT ON testdb.T_Persons TO 'rk'@'localhost' IDENTIFIED BY '123456'; GRANT DELETE ON testdb.T_Persons TO 'rk'@'localhost' IDENTIFIED BY '123456';
7、备份和恢复
7.1、备份
mysqldump -u root -p testdb > c:/bak.sql
7.2、恢复
mysql -u root -p testdb < c:/bak.sql
