一、多表查询



1.笛卡尔积

  • 概念:有两个集合A,B,取这两个集合的所有组成情况。
  • 要完成多表查询需要消除无用的数据。

2.多表查询的分类

  • 内连接查询

1.隐式内连接:使用where 条件消除无用的数据

如:查询所有学生的成绩信息

select * from student s1,teacher c where s.c_id =c.id;

如:查询学生表的姓名、年龄、性和查询班级表的课程名,分数

select

name,

age,

gender

t_name

score

from

student s,

class c

where

s.id = c.id;

 

2.显示内连接

语法:select 字段列表 from 表名1 [inner] join 表名2 on 条件;

如:查询所有学生的成绩信息

select * from student s join teacher c on s.c_id=c.id;

 

3.内连接查询注意事项

从哪些表中查询数据

条件是什么

查询哪些字段

  •  
  • 外连接查询

分类:

左外连接 :查询的是左表中所有数据以及其交集的部分

语法:select 字段列表 from 表1 left [outer] join 表2 on 条件;

右外连接 :查询的是右表中所有数据以及其交集的部分

语法:select 字段列表 from 表1 right[outer] join 表2 on 条件;

如:小明是因故没有参加考试,现查询所有学生的学习成绩

select * from student s left join teacher on s.t_id = t.id;


  • 子查询:查询中嵌套查询,称嵌套查询为子查询

如查询工资最高的员工信息

select * from emp where salary = (select max(salary) from emp);

 

不同情况:

查询的结果是单行单列的:子查询可以作为条件,使用运算符去判断,>、<、=、<=、>=

如:查询员工工资小于平均工资的员工信息

select * from emp where salary < (select avg(salary) from emp);

 

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

如:查询财务部和市场部的所有员工信息

select * from emp where dep_id in (select id where name = '市场部' or '财务部');

 

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

如:查询员工入职年份是2012之后的员工信息和部门信息

select * from dept t1 ,(select * from emp where date >'2012') t2,where t1.id = t2.dept_id;

  •  

二、DCL:管理用户,授权



1.管理用户

  • 添加用户:

create user '用户名' @ '主机名' identified by '密码';


  • 删除用户

drop user '用户名' @ '主机名';

主机名可以是 localhost 和 %

localhost代表本机访问

%代表除本机外其他主机也可以远程访问


  • 查询用户

use mysql;

select * from user;


  • 修改用户密码

第一种:update user set password = password('新密码') where user = '用户名';

第二种:set password for '用户名' @ '主机名' = password('新密码');

忘记修改的密码!!!

用管理员身份运行cmd,输入net stop mysql; --停止服务

使用无验证方式启动服务:mysqld --skip -grant-tables;

打开一个新命令窗口,输入mysql回车

依次执行:

use mysql;

update user set password = password('新密码') where user = 'root';

关闭所有命令窗口

打开任务管理器,手动结束mysqld.exe进程

启动mysql服务,使用新密码登录即可


  • 授权

查询权限:show grants for '用户名' @ '主机名';

授予权限:

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

grant all on . to '用户名' @ '主机名'; --授予用户所有权限

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

  •  

三、事务



1.概念:如果一个包含多个步骤的业务操作,被事务管理;那么这些操作要么同时全都成功,要么同时全都失败。

2.操作:

  • 开启事务:start transaction;
  • 回滚:rollback;
  • 提交:commit;

USE test; SELECT * FROM account; -- 开启事务 START TRANSACTION; -- 执行操作 UPDATE account SET money = money-500 WHERE id = 1;

-- 出错了。。。。

UPDATE account SET mone =money + 500 WHERE id = 2;

-- 没有出错,提交事务,持久化数据 COMMIT;

-- 回滚事务 ROLLBACK;


  • MySQL数据库中事务默认自动提交

事务提交的两种方式:

自动提交:一天DML语句自动提交一次事务。

手动提交:需要先开启事务,再提交事务。

Oracle数据库是手动提交事务

修改事务提交的方式:

查看事务的默认提交方式:select @@autocommit; -- 1表示自动提交,0表示手动提交

修改默认提交方式:set @@autocommit = 1或0;

  •  

3.事务的四大特征:

  • 原子性:是不可分割的最小操作单位,要么同时成功,要么同时失败。
  • 持久性:当事务执行回滚或提交操作后,数据库会持久化的保存数据。
  • 隔离性:多个事务之间,相互独立。
  • 一致性:事务操作前后,数据总量不变。

 

4.事务的隔离级别:

脏读:一个事务读取到另一个事务中未提交的数据

幻读:一个事务操作表中的所有记录,另一个事务添加了一条记录,则第一个事务查询不到自己的修改。

不可重复读(虚读):在同一个事务中,两次读取到的数据不一样。

  • read uncommitted:读未提交
    产生的问题:脏读、幻读、不可重复读
  • read committed:读已提交 --Oracle默认级别
    产生的问题:幻读、不可重复读
  • repeatable read:可重复读 --MySQL默认级别
    产生的问题:幻读
  • serializable read:串行化
    可以解决所有问题

注意:隔离级别从小到大越来越高,但效率越来越低。

查询数据库隔离级别:select @@tx_isolation;

修改数据库隔离级别:set global transaction isolation lever 级别字符串; --重启后生效