Mysql_知识总结03（级联操作+多表查询【左外连接查询*重点掌握】+数据库的存储过程）

-- 1、 级联操作(CASCADE:级联修改或者级联删除操作)
DROP TABLE employee ;
CREATE TABLE employee(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20),
    deptId INT,
    -- 添加外键约束
    -- 外键基础上添加级联修改
    -- on update cascade
    -- on delete cascade
    CONSTRAINT employee_dept_fk FOREIGN KEY (deptId) REFERENCES dept(id) ON UPDATE CASCADE ON DELETE CASCADE
 --     声明         外键名称    外键    被约束的字段  关联     部门表中id的字段
);

DROP TABLE dept ;

-- 部门表(主表,约束别人的表)
CREATE TABLE dept(
    id INT PRIMARY KEY AUTO_INCREMENT ,
    NAME VARCHAR(20) -- 部门名称
);
INSERT INTO employee (NAME,deptId) VALUES('王五',2) ;
INSERT INTO employee (NAME,deptId) VALUES('陈六',2) ;



-- 修改数据:
-- 添加了级联操作,修改了主表数据
UPDATE dept SET id = 2 WHERE id = 1 ;

SELECT * FROM employee ;
SELECT * FROM dept ;

-- 删除主表数据;级联删除操作
DELETE FROM dept WHERE id = 2 ;


-- 常遇到字段类型:
-- 1)char(20) vs varchar(20)
-- char(20):是一个固定长度的字符串,存储字符串内容,一定是20个字符串
-- varchar(20):可变的字符串长度,实际存储的时候是根据当前实际的字符串长度

DROP TABLE test ;
-- 2) int 和int(4)
-- int:默认的长度11位,再存储数值类型的时候,存储实际长度
-- int(4):固定长度
CREATE TABLE test(
    id1 INT ,-- 实际长度
    id2 INT(4) ZEROFILL  -- 零0填充
);

INSERT INTO test VALUES(1,4) ;
SELECT * FROM test ;


-- date vs datetime timestamp
-- date :日期
-- datetime:日期+时间
-- timestamp :时间戳 (修改或者更新表中数据的时候,显示当前系统时间的数据)
CREATE TABLE test2(
    ds1 DATE ,
    ds2 DATETIME ,
    ds3 TIMESTAMP
);

INSERT INTO test2(ds1,ds2) VALUE('2018-2-25 15:00:30','2018-2-25-15:00:30');

UPDATE test2 SET ds2 = '2018-2-26-15:00:45' ; -- 更新数据--时间戳这个timestamp就会起作用!


SELECT * FROM test2;


-- 多表查询
-- 两个或者两个以上的表进行数据的查询
-- 需求:查询员工及其对应的部门

SELECT * FROM employee ;
SELECT * FROM dept ;

INSERT INTO dept(NAME) VALUES('软件开发部') ;
INSERT INTO dept(NAME) VALUES('软件维护部') ;
INSERT INTO dept(NAME) VALUES('秘书部') ;


INSERT INTO employee (NAME,deptId) VALUES('张三',2) ;
INSERT INTO employee (NAME,deptId) VALUES('李四',3) ;
INSERT INTO employee (NAME,deptId) VALUES('王五',2) ;
INSERT INTO employee (NAME,deptId) VALUES('陈六',3) ;

-- 需求实现
-- -- 有问题 (笛卡尔乘积查询:出现这种情况的原因,查询的时候没有足够的连接条件)
SELECT e.NAME,d.NAME  FROM employee e,dept d;

-- 多表查询的一个步骤:
-- 1)查询哪些表(员工表和部门表)  2)查询这些表中的哪些字段(员工名称和部门名称)  3)他们之间有连接条件(规则:表的数量-1)

-- 内连接条件查询 (用的最多)
SELECT e.NAME,d.NAME
    FROM employee e,dept d
    -- 连接条件
    WHERE e.deptId = d.id ;
    

-- 另一种语法
SELECT e.NAME,d.NAME
    FROM employee e
    INNER JOIN dept d
    ON e.deptId = d.id ;
    
    
-- 多表查询的另一种方式:左外连接查询
-- 需求:查询部门表及对应员工名称

-- 左表示部门表,左表的数据要全部显示,右边员工表,如果右表-->员工表的数据当满足条件条件的时候,要显示该数据
-- 并且 当没有连接条件的时候,也要显示数据:null
-- 预期结果:
    -- 软件开发部     张三
    -- 软件维护部    李四
    -- 软件开发部   王五
    -- 软件维护部    陈六
    -- 秘书部    null
    
-- select d.NAME,e.NAME
--     from dept d,employee e
--     where d.id=e.deptId ;
    

-- 左外连接查询:(开发中也用的比较多)  -- 右外连接查询:right outer join
SELECT d.NAME,e.NAME
    FROM dept d -- 先根据左表(部门表)
    LEFT OUTER JOIN employee e      -- 连接员工表(右表)
    ON d.id=e.deptId ; -- 连接条件


-- 自连接查询(数据库中的某个表连接自己) (通过左外连接查询用的比较多!)
-- 给员工表中添加一个字段:boosId
ALTER TABLE employee ADD boosId INT ;

-- 查询员工表的员工以及对应的上司
-- 预期结果:
    -- 员工        上司
    -- 张三        null
    -- 李四        张三
    -- 王五        李四
    -- 陈六        王五
SELECT b.NAME AS '员工' ,e.NAME AS '上司'
    FROM employee b -- 自己去给当前员工表指定一个别名 (员工表中的要显示员工姓名的数据)
    LEFT OUTER JOIN employee e
    ON e.id=b.boosId ;


SELECT * FROM employee;



-- 数据库的存储过程
-- 定义一个结束符号:delimiter $    
DELIMITER $
CREATE PROCEDURE testByPro()
BEGIN
    -- 书写sql语句
    SELECT * FROM  dept ;

END $

-- 调用存储过程:
CALL testByPro() ;

-- 带有输入参数的存储过程
-- 需求:插入员工id,查询对应的员工
DELIMITER $
CREATE PROCEDURE pro_testByIn(IN eid INT) -- 存储过程名称(形式参数:参数类型+参数名称+字段类型)
BEGIN
    -- sql语句
    SELECT * FROM employee WHERE id = eid ;
END $

-- 调用
CALL pro_testByIn(4) ;  -- call 存储过程名称(实际参数) ;

-- 带有输出参数的存储过程 (输出参数)
DELIMITER $
CREATE PROCEDURE pro_testByOut(OUT n VARCHAR(20))
BEGIN
    -- 在存储过程中:局部变量 修改局部变量 set 数据
    SET n = '输出参数' ; -- 修改局部变量
END $

-- 数据库中变量
-- 分为三类:
-- 1)全局变量 :当前mysql一启动,mysql中就有一些全局变量,常驻内存的!
-- 全局变量331条数据:show variables: 显示当前mysql数据库的全局变量
--  mysql> show variables like 'character%';模糊条件查询跟字符集相关变量
-- character_set_client  :mysql接收客户端数据的编码格式
-- character_set_results :mysql客户端将数据以哪种编码格式展示出来

-- 如何查看全局变量:
    -- select @@变量名;
    -- 修改全局变量:set @@变量名;
    -- mysql> insert into test3 values(1,'张三') ;
    -- ERROR 1366 (HY000): Incorrect string value: '\xD5\xC5\xC8\xFD' for column 'name'

-- 2)会话变量:(实际数据库存储过程中,应用是会话变量最多)
-- 会话变量在登录mysql客户端设置一些会话变量,一旦客户端关闭,会话变量就失效了!(会话变量再一次登录中有效)
-- 查看会话变量:select @变量名;
-- 修改会话变量:set @变量名;

-- 3)局部变量
-- 在存储过程中定义的变量:
    -- 修改局部变量:set 变量名;
    -- 查看局部变量:select 变量;
    -- 定义局部变量:declare 变量名;


-- select @name ;

-- 调用带有输出参数的存储过程
CALL pro_testByOut(@n) ;-- 带出来一个会话变量
-- 执行了存储过程,@n已经数据了
-- 查询会话变量
SELECT @n;

-- 带有输入输出参数的存储过程(inout 后面参数名称:既可以携带到存储过程中,也可以通过会话变量查看)
DELIMITER $
CREATE PROCEDURE testByInOut(INOUT n VARCHAR(20))
BEGIN
    -- 先查看局部变量
    SELECT n ;
    -- 修改变量
    SET n = '500' ;
END $

-- 给n初始值
SET @n='100' ;
CALL testByInOut(@n) ;

-- 查看现在n的值
SELECT @n;

-- 带有条件判断语句的存储过程
-- 需求:输入1,输出'星期一',输入2,输出'星期二',输入3,输出'星期三',否则,'错误参数'
-- java-->if else语句

-- if (条件){
    -- 输出
-- }else if(条件){
--     输出
-- }
DELIMITER $
CREATE PROCEDURE testByIf(IN n INT ,OUT str VARCHAR(20))
BEGIN
     IF n=1 THEN
         SET str = '星期一' ;
    ELSEIF n=2 THEN
         SET str ='星期二' ;
     ELSEIF n=3 THEN
        SET str ='星期三';
     ELSE
        SET str ='错误参数' ;
     END IF;
END $

-- 调用存储过程
CALL testByIf(5,@str) ;
-- 查看
SELECT @str ;

-- 带有循环语句的存储过程
-- 插入一个num值,求1到num的和
DELIMITER $
CREATE PROCEDURE testByWhile(IN num INT ,OUT score INT)
BEGIN
    -- int result = 0
    -- for(int i = 1 ; i <=100 ;  i ++){
    --         result += i ;
    -- }
    -- 定义局部变量
    DECLARE result INT DEFAULT 0 ;
    DECLARE i INT DEFAULT 1 ;
    WHILE i<= num DO
        SET result = result + i;       -- 修改局部变量
        SET i = i + 1;      -- 修改局部变量
    END WHILE ;
    SET score = result ; -- 相当于java方法中return语句,将score携带出存储过程
        
END $

-- 调用存储过程
CALL  testByWhile(200,@score);
SELECT @score ;

-- 携带数据库中的数据给输出参数(into)
-- 需求:传入员工id,查询对应的员工,输出员工的姓名
DELIMITER $
CREATE PROCEDURE testByInTo(IN eid INT,OUT sname VARCHAR(20))
BEGIN
    SELECT NAME INTO sname FROM employee WHERE id =eid ;
END $

-- 调用
CALL testByInTo(3,@sname) ;
SELECT @sname;