多表查询，事务，DCL

多表查询

select *from a,b; -- 产生笛卡儿积

       笛卡儿积概念：

       要完成多表查询需要消除笛卡儿积产生的无用数据。

 

多表查询分类：

       内连接，外连接，子查询

 

-- 创建班级表

CREATE TABLE class(

       id INT PRIMARY KEY AUTO_INCREMENT,

       cname VARCHAR(20)

       );

 

INSERT INTO class(cname) VALUES('一年级'),('二年级'),('三年级');

 

-- 创建学生表

CREATE TABLE student(

       id INT PRIMARY KEY AUTO_INCREMENT,

       NAME VARCHAR(10), -- 姓名

       gender CHAR(1), -- 性别

       age INT , -- 年龄

       class_id INT, -- 班级编号

       FOREIGN KEY(class_id) REFERENCES class(id)   -- 外键

       );

      

INSERT INTO student(NAME,gender,age,class_id) VALUES

('小明','男', 12, 1);

INSERT INTO student(NAME,gender,age,class_id) VALUES

('小李','男', 14, 3),

('小丽','女', 13, 2),

('小白','女', 12, 1),

('小黑','女', 14, 3),

('小灰','男', 13, 2),

('小红','女', 15, 3);

 

INSERT INTO student(NAME,gender,age,class_id) VALUES

('小紫','男', 12, NULL);

 

INSERT INTO class(cname)VALUES(NULL);

 

-- 笛卡儿积

SELECT *FROM student,class;

 

 

-- 隐式内连接

SELECT *FROM student,class WHERE student.`class_id` = class.id;

 

-- 显式内连接 inner join ...on,inner可以省略

SELECT *FROM student INNER JOIN class ON student.`class_id` = class.id;

 

-- 查询小明的，性别，年龄,班级所有信息

SELECT s.`name`,s.`gender`,s.`age`,c.cname

FROM class c,student s

WHERE c.id = s.`class_id` AND s.`name` = '小明';

 

-- 左外连接left join ...on 查询的是左表所有数据以及其交集部分

SELECT *FROM student s LEFT JOIN class c

ON c.id = s.`class_id`;

 

-- 右外连接right join ...on 查询的是右表所有数据以及其交集部分

SELECT *FROM student s RIGHT JOIN class c

ON c.id = s.`class_id`;

 

-- 子查询

 

-- 查询三年级有哪些人

-- 通过两个语句查询

SELECT id FROM class WHERE class.cname = '三年级';  -- 执行完数据class_id为3

SELECT NAME FROM student WHERE class_id = 3;

-- 通过一个语句查询

SELECT NAME FROM student WHERE class_id=(SELECT id FROM class WHERE class.cname = '三年级');

 

/*

       子查询：一个查询的结果做为另一个查询的条件

        有查询的嵌套，内部的查询称为子查询

        子查询要使用括号

*/

 

子查询不同情况：

-- 子查询结果式单行单列的， 子查询可以作为条件，使用运算符去判断。 运算符： > >= < <= =

-- 查询学生年级是三年级的人

SELECT NAME FROM student WHERE class_id=(SELECT id FROM class WHERE class.cname = '三年级');

 

-- 子查询结果是多行单列的， 子查询可以作为条件，使用运算符in来判断

-- 查询二三年级的学生名

SELECT * FROM student WHERE class_id IN(SELECT id FROM class WHERE class.cname = '二年级' OR class.cname = '三年级');

 

-- 子查询结果是多行多列的，子查询可以作为一张虚拟表参与查询

-- 查询每个年级年龄大于12的人数，并且人数大于2的年级

SELECT *FROM class c,(SELECT *FROM student WHERE age > 12)t1 WHERE c.id = t1.class_id;

 

 

事务

概念：被事务管理的操作要么同时成功要么同时失败

操作事务：

       start transaction 开启

       rollback 回滚

       commit 提交

      

修改自动提交方式： select @@autocommit； set @@autocommit = 0；-- 手动提交

 

事务四大特征：

1. 原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败。

2. 持久性：当事务提交或回滚后，数据库会持久化的保存数据。

3. 隔离性：多个事务之间。相互独立。

4. 一致性：事务操作前后，数据总量不变

事务隔离级别介绍

       问题：脏读，不可重复读，幻读

隔离级别：

1. read uncommitted：读未提交

       产生的问题：脏读、不可重复读、幻读

2. read committed：读已提交 （Oracle）

       产生的问题：不可重复读、幻读

3. repeatable read：可重复读 （MySQL默认）

       产生的问题：幻读

4. serializable：串行化

       可以解决所有的问题

 

 

 DCL：管理用户，授权

1管理用户

       添加用户：CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

       删除用户：语法：DROP USER '用户名'@'主机名';

       修改用户密码：

UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名';

SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码');

       查询用户

USE myql;

SELECT * FROM USER;

2权限管理

查询权限SHOW GRANTS FOR '用户名'@'主机名';

授予权限grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';

撤销权限revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';

 

更多资源请扫描并关注公众号