mysql设置多个表的查询权限 mysql中进行多表联查方式

 一.创建外键

#测试数据表
# 教师表，主表
CREATE TABLE teacher(

id INT PRIMARY KEY AUTO_INCREMENT,
    name varchar(20),
    age INT
)ENGINE=InnoDB DEFAULT CHARSET=UTF8;

#测试数据：

INSERT INTO teacher values
                            (1,'范冰冰',22),
                            (2,'周杰棍',30),
                            (3,'双杰伦',35),
                            (4,'梁朝伟',25);

--------------------------------分割线----------------------------
#学生表,子表

CREATE TABLE student(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name varchar(20),
    age INT,
    teacher_id INT,         # 外键要和关联的主键的数据类型一致
    FOREIGN KEY (teacher_id) REFERENCES teacher(id)  # 添加外键
)ENGINE=InnoDB DEFAULT CHARSET=UTF8;

#测试数据：

INSERT INTO student values
                            (1,'张三',22, 1),
                            (2,'李四',30,2),
                            (4,'王五',29,3),
                            (5,'韩信',61,4),
                            (6,'刘备',55,4),
                            (7,'曹操',21,2),
                            (8,'爱丽丝',32,1),
                            (9,'赵6',22,2);

--------------------------------分割线----------------------------

SELECT * FROM student INNER JOIN teacher ON student.teacher_id=teacher.id

    

 

--------------------------------分割线----------------------------

 增加外键和删除外键：

增加:ALTER TABLE student ADD CONSTRAINT key_name(名字) FOREIGN KEY (teacher_id) REFERENCES teacher(id)
　　删除：ALTER TABLE student DROP FOREIGN KEY key_name

 二.InnoDB数据库引擎的ON语句

InnoDB支持的常用方式：
　　1.cascade方式：在父表上update或delete记录时，同步update/delete掉子表的匹配记录
　　FOREIGN KEY (teacher_id) REFERENCES teacher(id) ON DELETE CASCADE  #  如果父级表中的记录删除了，则子表中相对应的记录也会自动删除

　　2.set null方式 在父表update或delete记录时，将子表上的相对于的列设为null
　　FOREIGN KEY (teacher_id) REFERENCES teacher(id) ON DELETE SET NULL

#外键约束对子表的作用：在父表中找不到选键，则不允许在子表上进行 insert/update
#外键约束对父表的作用：

在父表上进行update/delete以更新或删除在子表中有一条或多条对应匹配行的候选键时,父表的行为取决于：在定义子表的外键时指定的on update/on delete子句

 

 三.多表连接查询

#创建主测试表

CREATE TABLE role(
       role_id int,
       role_name varchar(100)
      )ENGINE=InnoDB DEFAULT CHARSET=utf8

#测试数据：

INSERT INTO role VALUES (301,'战士'),
                           (302,'法师'),
                           (303,'刺客'),
                           (304,'ADC');
                           (306,'打野')

----------------------------分割线----------------------------------

#创建子测试表

CREATE TABLE hero
              ( h_id INT auto_increment PRIMARY KEY NOT NULL,
                h_name VARCHAR ( 50 ),
                age INT,
                role_id INT )ENGINE=InnoDB DEFAULT CHARSET=utf8;

#测试数据：

INSERT INTO hero(h_name,age,role_id) VALUES ('盖伦',22,301),
                                              ('流浪法师',23,302),
                                              ('布隆',22,303),
                                              ('熔岩巨兽',25,301),
                                              ('伊泽瑞尔',24,304),
                                              ('锤石',26,305),
                                              ('琴女',28,305),
                                              ('狂战士',27,301),
                                              ('稻草人',29,302),
                                              ('诡术妖姬',22,303);

 

•  内连接

SELECT * FROM hero inner JOIN role on hero.role_id=role.role_id;#笛卡尔积中筛选结果

     

 

 

•  外连接

1.左连接：在内链接的基础上增加左边有但右边没有的结果

SELECT * FROM hero LEFT JOIN role on hero.role_id=role.role_id;

    

 

----------------------------分割线----------------------------------

　2.右连接：在内连接的基础上增加右边有但左边没有的结果

　SELECT * FROM hero RIGHT JOIN role on hero.role_id=role.role_id;

   

----------------------------分割线----------------------------------

 3.全外连接：在内连接的基础上增加  左边有右边没有 和 右边有左边没有 的结果

　　

SELECT * FROM hero RIGHT JOIN role ON hero.role_id=role.role_id
UNION
SELECT * FROM hero LEFT JOIN role ON hero.role_id=role.role_id;

   

##mysql不支持全外连接的，可以用以上的方式间接实现

 

 四：多表复合条件连接查询

SELECT role.role_name FROM hero,role WHERE hero.age >= 29 AND role.role_id=hero.role_id # 笛卡尔积中筛选年龄大于或等于29岁的英雄的分类

SELECT role.role_name FROM hero INNER JOIN role WHERE hero.age >= 29 AND role.role_id=hero.role_id #内连接查询

 

 五：子查询

#子查询是将一条完整的查询语句嵌套在另外一条查询语句里
#内层查询语句的查询结果，可以为外层查询语句提供查询条件
#子查询用于为主查询返回其所需数据，或者对检索数据进行进一步的限制。
#子查询可以在 SELECT、INSERT、UPDATE 和 DELETE 语句中，同 =、<、>、>=、<=、IN、BETWEEN 等运算符一起使用。

• 子查询必须括在圆括号中。
• 子查询的 SELECT 子句中只能有一个列，除非主查询中有多个列，用于与子查询选中的列相比较。
• 子查询不能使用 ORDER BY，不过主查询可以。在子查询中，GROUP BY 可以起到同 ORDER BY 相同的作用。
• 返回多行数据的子查询只能同多值操作符一起使用，比如 IN 操作符。
• SELECT 列表中不能包含任何对 BLOB、ARRAY、CLOB 或者 NCLOB 类型值的引用。
• 子查询不能直接用在集合函数中。
• BETWEEN 操作符不能同子查询一起使用，但是 BETWEEN 操作符可以用在子查询中。

　#SELECT * from hero WHERE hero.role_id in (SELECT role_id FROM role)