JavaWebDay03_多表查询(重点),数据库事务(重点),DCL管理用户

JavaWebDay03

课程大纲

 1、多表查询 ----> 难点
 2、数据库事务 --> 面试重点(概念,三个步骤,四个特征)
 3、DCL管理用户 --> 了解

第一章 多表查询

1、笛卡尔积

A.语法: 
	SELECT * FROM 表名称1,表名称2;
	
B.查询出来的数据条目数:
	a.计算公式
		表名称1的条目数x表名称2的条目数 
	b.例如: 
		表1有4条,表2里面9条. 查询的结果是 4*9 = 36 条

2、内连接查询

A.隐式内连接 语法
	SELECT * FROM 表1,表2 WHERE 表1.列1 = 表2.列2;  -- 标准写法
	SELECT * FROM 表1 别名1,表2 别名2 WHERE 别名1.列1 = 别名2.列2;  -- 别名的写法 
	
B.显式内连接 语法
	SELECT * FROM 表1 INNER JOIN 表2 ON 表1.列1 = 表2.列2; -- 完整格式
	SELECT * FROM 表1 JOIN 表2 ON  表1.列1 = 表2.列2; -- 省略INNER的格式
	
C.内连接查询的结果
	a.特点:
		两个表当中交集的部分.取数据多的。
		表1,有8条数据,6条数据在交集当中.
		表2,有20条数据,10条数据在交集当中.
		那么采用内连接查询的结果是 10 条数据。
	b.两个表内连接查询的时候,
		表1有10条数据,表2有5条数据.请问.最多有多少条数据,最少有多少条数据?
		最多10条,最少0条。

3、外连接查询

A.左外连接
	a.语法格式
		SELECT * FROM 表1 LEFT OUTER JOIN 表2 ON 表1.列1 = 表2.列2; -- 完整格式
		SELECT * FROM 表1 LEFT JOIN 表2 ON  表1.列1 = 表2.列2; -- 省略OUTER的格式
	b.结果特点
		左表的全部内容+左右表的交集。(JOIN的左边就是左表,JOIN的右边就是右表) 
		
B.右外连接
	a.语法格式
		SELECT * FROM 表1 RIGHT OUTER JOIN 表2 ON 表1.列1 = 表2.列2; -- 完整格式
		SELECT * FROM 表1 RIGHT JOIN 表2 ON  表1.列1 = 表2.列2; -- 省略OUTER的格式
	b.结果特点
		右表的全部内容+左右表的交集。(JOIN的左边就是左表,JOIN的右边就是右表) 
		
C.思考问题
	两个表左外连接查询的时候,
		表1有5条数据,表2有10条数据.请问.最多有多少条数据,最少有多少条数据?
		左表是 表1,右表是 表2
		最多14条,最少5条。

4、子查询

A.概念

子查询,也可以理解为"嵌套查询".一个查询语句的结果是下一个查询语句的条件.

B.子查询的情况

a.单行单列,主要采用> < >= <= = <> 判断
	案例: 查询最高工资的员工信息
	原始做法:
		-- 查询到最高的员工工资
		SELECT MAX(gongzi) FROM yuangong;  
		-- 查询到的最高工资作为条件.再查询他信息
		SELECT * FROM yuangong WHERE gongzi = 查询到的结果;  
	子查询的情况	
		SELECT * FROM yuangong WHERE gongzi = (SELECT MAX(gongzi) FROM yuangong);
		
b.多行单列,主要采用的 IN(选项1,选项2,选项3)
	案例:查询财务部和市场部的员工信息
	原始做法:
		-- 查询到财务部和市场部的ID
		SELECT id FROM yuangong WHERE name IN ('财务部','市场部');
		-- 通过查询到的结果作为条件,查询满足条件的所有员工信息
		SELECT * FROM yuangong WHERE id = 查询到的id结果; 
	子查询的情况
		SELECT * FROM yuangong WHERE id IN (SELECT id FROM 
		yuangong WHERE name IN ('财务部','市场部')); 
		
c.多行多列,作为虚拟表,进行的连接查询.
	案例: 查询入职日期是2011-11-11日之后的员工信息和部门信息
	子查询的情况
		SELECT * FROM bumen t1 ,(SELECT * FROM yuangong WHERE 
		yuangong.join_date > '2011-11-11') t2 WHERE t1.id = t2.bumen_id;
	采用普通内连接
		SELECT * FROM bumen t1 , yuangong t2  WHERE  t1.id = t2.bumen_id 
		AND t2.join_date > '2011-11-11';

5、多表查询的分析思路:

（通用性思路） 后期建议: 下去之后，多表的练习需要多练习。可以在网络上面搜索相关的题目。

A. 观察

观察需要查询的字段，来自于哪些表。确定要查询的有几张表，这些表会定义在查询语句的 FROM 后面

B.确定多表的类型

a. 内连接查询（一般情况下都会是内连接查询）
b. 外连接查询（有"所有的"和"全部的"这种字眼出现）
c. 子查询(在不能够一步到位的情况，考虑子查询)"第一次查询的结果是下一次查询的条件"

C. 连接条件

确定连接条件的数目:
	如果涉及到的是 2 张表， 连接条件有 1 个
	如果涉及到的是 3 张表， 连接条件有 2 个
	如果涉及到的是 4 张表， 连接条件有 3 个
连接条件的数目 = 涉及到表的数目 - 1

D. 查询的结果

采用 表名称 或者 表的别名，点出字段名称，写在 SELECT 语句 和 FROM 语句 之间。

E. 小技巧

对于子查询而言，可以给查询的 虚拟表 设置别名，查询的结果 也设置别名。
在外面的查询当中可以使用 表别名.字段别名	

​

第二章 数据库事务

1、事务的概念

在多个操作的SQL语句当中,要么同时成功执行,要么同时失败执行。

2、操作步骤:

A.开启事务	START TRANSACTION
B.回滚(出问题了,需要回到开启事务之前)     ROLLBACK
C.提交(正常执行,将改变的数据提交结束事务) COMMIT

3、查看修改事务提交方式

A.查看事务的提交方式
		SELECT @@autocommit;
	
B.修改事务的提交方式
		SET @@autocommit = 0;  -- 0表示手动提交,1表示自动提交

4、事务的四大特征

A.原子性: 在事务的多个SQL语句当中,不可以分割开来
B.持久性: 发生回滚和提交的时候,数据持久的保存在硬盘上面
C.隔离性: 尽可能的多个事务之间没有影响
D.一致性: 事务前后的总量不能改变

5、隔离级别

A.存在问题
	a.脏读：事务X读取到事务Y,未提交的数据.
	b.虚读(不可重复读)：在同一个事务当中,两次读取的数据不一样。
	c.幻读：事务X在操作数据库当中所有的记录,事务Y在添加一条新的记录。事务X读取不到自己的修改
	
B.对应的级别
	a. READ UNCOMMITED 读未提交 (出现"脏读","虚读","幻读")
	b. READ COMMITED  读提交 (出现"虚读","幻读")  ---> Oracle 默认采用
	c. REPEATABLE READ 可重复读 (出现"幻读") ---> MySQL 默认采用
	d. SERIALIZABLE 串行化 (可以解决所有问题,效率低)
	
C.设置和查看隔离级别
	a.查看隔离级别
		SELECT @@TX_ISOLATION;
	b.设置隔离级别
		SET GLOBAL TRANSACTION ISOLATION LEVEL 级别字符串;

第三章 DCL管理用户

1、创建用户(账号密码)

语法: CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';  -- 主机名 本机 localhost 任意主机 %

2、删除用户(账号)

语法: DROP USER '用户名'@'主机名';

3、修改用户密码

语法1: UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名';
语法2: SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码');

4、查询用户(账号密码)

语法: USE mysql;  SELECT * FROM user;  -- 先要使用mysql自己的数据库,然后查询user表

5、忘记了超级管理员用户密码之后的解决方案:

a. cmd 输入 net stop mysql  -- 停止mysql服务 需要管理员运行该cmd
b. 使用无验证方式启动mysql服务： mysqld --skip-grant-tables
c. 打开新的cmd窗口,直接输入mysql命令，敲回车。就可以登录成功
d. use mysql;
e. update user set password = password('你的新密码') where user = 'root';
f. 关闭两个窗口
g. 打开任务管理器，手动结束mysqld.exe 的进程
h. 启动mysql服务
i. 使用新密码登录。

6、数据库权限

A.查询权限
	语法: SHOW GRANTS FOR '用户名'@'主机名';
	
B.授予权限
	语法: GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';  -- 标准格式
	语法: GRANT ALL ON *.* TO 'zhangsan'@'localhost';  -- 给张三本机用户授予所有的权限
	
C.撤销权限
	语法: REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
	案例: REVOKE UPDATE ON db3.`account` FROM 'lisi'@'%';-- 撤销李四所有机器上的 UPDATE 权限