MySQL系列：（3）MySQL加强

2023-01-31 02:01:47 mysql 系列

DDL数据定义语言：create / drop / alter

DML数据操作语句：insert / delete /update / truncate

DQL数据查询语言：select / show

2.1、什么是数据约束

对表中的列值数据进行约束

2.2、默认值

作用：当用户对使用默认值的字段不插入值的时候，就使用默认值。

注意：对默认值字段可以插入null。

CREATE TABLE T_Persons(
	Id INT,
	NAME NVARCHAR(20),
	Gender NVARCHAR(2) DEFAULT '男'
)

2.3、非空

作用：限制字段必须赋值

注意：1）非空字符必须赋值；2）非空字符不能赋null。

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）主表的参考字段通常为主键！

3）添加数据：先添加主表，再添加副表

4）修改数据：先修改副表，再修改主表

5）删除数据：先删除副表，再删除主表

2.8、级联操作

级联修改： ON UPDATE CASCADE

级联删除： ON DELETE CASCADE

注意：级联操作必须在外键基础上使用

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.1、三大范式

设计原则：建议设计的表尽量遵守三大范式。

第一范式：要求表的每个字段必须是不可分割的独立单元。

如果在T_Persons表中的name字段中存储“李东华|李叶蛾”，就会违反第一范式。

如果在T_Persons表中的name字段中存储“李东华”，在oldname字段中存储“李叶蛾”，就会符合第一范式。

第二范式：在第一范式的基础上，要求每张表只表达一个意思。表的每个字段都和表的主键有依赖。

第三范式：在第二范式基础，要求每张表的主键之外的其他字段都只能和主键有直接决定依赖关系。

按照我个人理解：

第一范式，是对“列”进行约束，每个字段是不可分割的独立单元；

第二范式，是对“表”进行约束，每张表只表达一个意思；

第三范式，是对“表与表之间的关系”进行约束，副表只能包含主表的primary key。

3.2、Mysql变量

mysql变量包括：全局变量、会话变量、局部变量

全局变量（内置变量）：mysql数据库内置的变量（所有连接都起作用）

查看所有全局变量： show variables

查看某个全局变量： select @@变量名

修改全局变量： set 变量名=新值

全局变量中的两个值：character_set_client和character_set_results。可以查询SHOW VARIABLES LIKE 'character_%';

character_set_client: mysql服务器的接收数据的编码

character_set_results：mysql服务器输出数据的编码

会话变量：只存在于当前客户端与数据库服务器端的一次连接当中。如果连接断开，那么会话变量全部丢失！

定义会话变量: set @变量=值

查看会话变量： select @变量

局部变量：在存储过程中使用的变量就叫局部变量。只要存储过程执行完毕，局部变量就丢失！！

4.1、什么是存储过程

存储过程，是带有逻辑的sql语句。

之前的sql没有条件判断，没有循环；存储过程带上流程控制语句（if while）。

4.2、存储过程特点

1）执行效率非常快！存储过程是在数据库的服务器端执行的！！！

2）移植性很差！不同数据库的存储过程是不能移植。

4.3、存储过程语法

语法：

删除存储过程：DROP PROCEDURE 存储过程名称;

参数：

IN：表示输入参数，可以携带数据带存储过程中

OUT：表示输出参数，可以从存储过程中返回结果

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;

当操作了某张表时，希望同时触发一些动作/行为，可以使用触发器完成！！

语法：

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 $

MySQL数据库的所有用户都存储在mysql.user表内

其中，root用户拥有所有权限（可以干任何事情）；

而权限账户，只拥有部分权限（CURD）例如，只能操作某个数据库的某张表

1）如何修改mysql用户的密码？

UPDATE USER SET PASSWord=PASSWORD('root') WHERE USER='root';

在mysql中，密码password是经md5加密的(单向加密)

USE mysql;
SELECT * FROM USER;

SELECT PASSWORD('root'); -- *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B

2）分配权限账户

GRANT 权限 ON 数据库名.表名 TO '用户名'@'IP地址' IDENTIFIED BY '密码';

如果IP地址处用“%”，则表示本机和远程都可以。

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.1、备份

mysqldump -u root -p testdb > c:/bak.sql

7.2、恢复

mysql -u root -p testdb < c:/bak.sql

注意:不需要登录

相关文章