mySQL--高级操作

数据约束

数据约束：对用户操作表的数据进行约束。

常用约束：

1、默认值 -- default

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

注意：带有默认值的字段可以插入null和非null的数据。

示例：

CREATE TABLE student(
    id INT,
    NAME VARCHAR(20),
    address VARCHAR(20) DEFAULT '济南'  -- 默认值
)

2、非空 -- not null

作用：限制字段必须赋非null值。

示例：

CREATE TABLE student(
    id INT,
    NAME VARCHAR(20),
    gender VARCHAR(2) NOT NULL -- 非空
)

3、唯一 --unique

作用：限制字段不能设置重复的值。

注意：该字段可以插入null并且可以插入多个null。

示例：

CREATE TABLE student(
    id INT UNIQUE, -- 唯一
    NAME VARCHAR(20)
)

4、主键 --primary key

作用：等价于唯一+非空

注意：

　　1）通常情况下，每张表会设置一个主键字段，用于标记表中的每条记录的唯一性。

　　2）建议不要选择表的包含业务含义的字段作为主键，建议给每张表独立设计一个非业务含义的id字段。

示例：

CREATE TABLE student(
    id INT PRIMARY KEY, -- 主键
    NAME VARCHAR(20)
)

5、自增长 -- auto_increment

作用：自动递增。

示例：

CREATE TABLE student(
    id INT(4) ZEROFILL PRIMARY KEY AUTO_INCREMENT, -- 自增长，从0开始  ZEROFILL 零填充
    NAME VARCHAR(20)
)

注意：自增长的字段在插入数据的时候可以不赋值,服务器自动添加上，也可以自己添加。

INSERT INTO student(NAME) VALUES('张三');
INSERT INTO student(NAME) VALUES('李四');
INSERT INTO student(NAME) VALUES('王五');
INSERT INTO teacher VALUES(3,'张三',10)

6、外键

作用：约束两种表的数据。

示例：

-- 部门表（主表）
CREATE TABLE dept(
    id INT PRIMARY KEY,
    deptName VARCHAR(20)
)

-- 修改员工表（副表/从表）
CREATE TABLE employee(
    id INT PRIMARY KEY,
    empName VARCHAR(20),
    deptId INT,-- 把部门名称改为部门ID
    -- 声明一个外键约束
    CONSTRAINT emlyee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id)
    --           外键名称                  外键               参考表(参考字段)
)

注意：

　　1）被约束的表成为副表（employee），约束别人的表成为主表（dept），外键设置在副表上。

　　2）主表的参考字段通常为主键。

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

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

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

7、级联操作 -- on update/delete cascade

作用：在使用了外键的基础上，要操作主表的数据，必须要先操作副表的数据。为了解决这个麻烦，就采用级联操作来完成。

示例：

CREATE TABLE employee(
    id INT PRIMARY KEY,
    empName VARCHAR(20),
    deptId INT,-- 把部门名称改为部门ID
    -- 声明一个外键约束
    CONSTRAINT employee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id) ON UPDATE CASCADE ON DELETE CASCADE  -- ON CASCADE UPDATE ：级联修改
    --           外键名称                  外键               参考表(参考字段)
)

注意：

　　1）更新级联和删除级联可以根据不同的业务分开使用。

　　2）级联操作必须在外键的基础上使用。

数据库设计

数据库的设计应该遵守三大范式。

第一范式：要求表的每个字段必须是不可分割的独立单元。（即每个字段必须只能代表一种含义）

第二范式：在第一范式的基础上，要求每张表只能表达一个意思，表的每个字段都和表的主键有依赖。（即每张表尽量只代表一种业务实体逻辑）。

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

注意：

　　三大范式能解决了数据混乱和数据冗余高的问题。设计数据库的尽量遵守，但是在实际开发中可能为了查询的效率可以不完全遵守三大范式。

关联多表查询

1、笛卡尔积查询（交叉查询）

SELECT empName,deptName FROM employee,dept;

如果不设置条件，查询两个表中的字段时就会产生笛卡尔积现象。例如：employee表中有4条数据，dept表中有4条数据，查询出来的结果就会有有 4 * 4 = 16条结果。

2、内连接查询 -- 查询出来的数据是完全符合条件的结果

第一种语法：直接“=”连接

SELECT empName,deptName       -- 2）确定哪些哪些字段
    FROM employee,dept    -- 1）确定查询哪些表
    WHERE employee.deptId=dept.id  -- 3）表与表之间连接条件

第二种语法：表1 inner join 表2 on 条件；

SELECT empName,deptName
    FROM employee
    INNER JOIN dept
    ON employee.deptId=dept.id;

使用别名查询：

SELECT e.empName,d.deptName
    FROM employee e
    INNER JOIN dept d
    ON e.deptId=d.id;

3、外连接查询 -- 表1 left/right outer join 表2 on 条件

外连接分为两种：左外连接和右外连接。

左[外]连接查询： 使用左边表的数据去匹配右边表的数据，如果符合连接条件的结果则显示，如果不符合连接条件则显示null

右[外]连接查询: 使用右边表的数据去匹配左边表的数据，如果符合连接条件的结果则显示，如果不符合连接条件则显示null

语法示例：

左外连接

SELECT d.deptName,e.empName
    FROM dept d
    LEFT OUTER JOIN employee e
    ON d.id=e.deptId;

右外连接

SELECT d.deptName,e.empName
    FROM employee e
    RIGHT OUTER JOIN dept d
    ON d.id=e.deptId;

存储过程

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

特点：

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

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

语法：

-- 创建存储过程
DELIMITER $       -- 声明存储过程的结束符
CREATE PROCEDURE pro_test()           --存储过程名称(参数列表)
BEGIN             -- 开始
    -- 可以写多个sql语句;          -- sql语句+流程控制
    SELECT * FROM employee;
END $            -- 结束 结束符

-- 执行存储过程
CALL pro_test();          -- CALL 存储过程名称(参数);

存储过程中的参数列表类型：

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

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

INOUT：表示输入输出参数，即可以输入功能，也可以输出功能。

1、带参数的存储过程

1.1 输入参数

--  带有输入参数的存储过程
-- 需求：传入一个员工的id，查询员工信息
DELIMITER $
CREATE PROCEDURE pro_findById(IN eid INT)  -- IN: 输入参数
BEGIN
    SELECT * FROM employee WHERE id=eid;
END $ 

-- 调用
CALL pro_findById(4);

1.2 输出参数

--  带有输出参数的存储过程
DELIMITER $
CREATE PROCEDURE pro_testOut(OUT str VARCHAR(20))  -- OUT：输出参数
BEGIN
        -- 给参数赋值
    SET str='helljava';
END $

 

2、删除存储过程

DROP PROCEDURE pro_testOut;

3、mySQL中的变量

--  全局变量（内置变量）：mysql数据库内置的变量 （所有连接都起作用）
        -- 查看所有全局变量： show variables
        -- 查看某个全局变量： select @@变量名
        -- 修改全局变量： set 变量名=新值
        -- character_set_client: mysql服务器的接收数据的编码
        -- character_set_results：mysql服务器输出数据的编码

--  会话变量： 只存在于当前客户端与数据库服务器端的一次连接当中。如果连接断开，那么会话变量全部丢失！
        -- 定义会话变量: set @变量=值
        -- 查看会话变量： select @变量

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

示例：

定义一个会话变量name, 2)使用name会话变量接收存储过程的返回值

CALL pro_testOut(@NAME);
-- 查看变量值
SELECT @NAME;

触发器

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

示例：

-- 需求： 当向员工表插入一条记录时，希望mysql自动同时往日志表插入数据
-- 创建触发器(添加)
CREATE TRIGGER tri_empAdd AFTER INSERT ON employee FOR EACH ROW    -- 当往员工表插入一条记录时
     INSERT INTO test_log(content) VALUES('员工表插入了一条记录');

-- 创建触发器(修改)
CREATE TRIGGER tri_empUpd AFTER UPDATE ON employee FOR EACH ROW    -- 当往员工表修改一条记录时
     INSERT INTO test_log(content) VALUES('员工表修改了一条记录');

-- 创建触发器(删除)
CREATE TRIGGER tri_empDel AFTER DELETE ON employee FOR EACH ROW    -- 当往员工表删除一条记录时
     INSERT INTO test_log(content) VALUES('员工表删除了一条记录');

mySQL权限

mysql的用户信息存放在mySQL数据库中的user表中。

1、用户密码：mysql中的密码是用MD5加密后的数据。数据库中有password（）函数，对数据进行md5加密。

例如：

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

修改密码：

5.6版本之前的方式：

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

5.7版本之后的方式：

update USER set authentication_string=PASSWORD('123456') where user='root';

2、分配权限账户：

GRANT SELECT ON day16.employee TO 'eric'@'localhost' IDENTIFIED BY '123456';
GRANT DELETE ON day16.employee TO 'eric'@'localhost' IDENTIFIED BY '123456';

注意：分配给eric用户对day16数据库中的employee表有查询和删除操作。eric用户的密码是123456。