mysql进阶操作总结。

JAVA ZMAS 42℃ 0评论

# 多表查询:
基本语法:
select * from emp,dept

笛卡尔积:
集合A(a,b) B(1,2,3) ,两个集合的所有组合情况为 a1,a2,a3,b1,b2,b3

* 多表查询中,我们要消除笛卡尔积现象(即消除)

## 多表查询的分类:
### 1. 内连接: 会查询出两个表中所有满足条件的数据
隐式内连接
概念: 通过查询条件消除了笛卡尔积现象
SELECT * FROM emp,dept WHERE emp.dept_id = dept.id
— 规范写法
SELECT
emp.`name`, — 员工的名字
emp.`salary`, — 员工的工资
dept.`name` — 部门的名字
FROM
emp,dept
WHERE
emp.`dept_id` = dept.`id`

— 规范写法 起别名
SELECT
t1.`name`, — 员工的名字
t1.`salary`, — 员工的工资
t2.`name` — 部门的名字
FROM
emp t1,dept t2
WHERE
t1.`dept_id` = t2.`id`

小技巧:
1. 一定要知道在哪几个表中查
2. 找到表和表之间的条件关联
3. 确认显示的字段

显式内连接
select 字段 from 表1 [inner] join 表2 on 关联条件

select
t1.`name`, — 员工的名字
t1.`salary`, — 员工的工资
t2.`name` — 部门的名字
from
emp t1
inner join
dept t2 on t1.`dept_id`=t2.`id`

### 2. 外连接
左外连接: 查询出左表所有的数据,以及右表的所有关联数据
语法: select 字段 from 左表 left [outer] join 右表 on 条件

select
emp.`name`, — 员工的名字
emp.`salary`, — 员工的工资
dept.`name` — 部门的名字
from
emp
left join
dept
on emp.`dept_id` = dept.`id`

右外连接: 查询出右表所有的数据,以及左表的所有关联数据
语法: select 字段 from 左表 right [outer] join 右表 on 条件
SELECT
emp.`name`, — 员工的名字
emp.`salary`, — 员工的工资
dept.`name` — 部门的名字
FROM
emp
RIGHT JOIN
dept
ON emp.`dept_id` = dept.`id`

### 3. 子查询
概念: 我们在查询当中会使用一些嵌套查询,嵌套查询我们称为子查询。

— 查询 工资最高的那个人的所有信息
— 1.查询出最高工资
SELECT MAX(salary) FROM emp; — 12000
— 2.根据最高工资查询出信息
SELECT * FROM emp WHERE salary=12000;
— 通过子查询的方式查询
SELECT * FROM emp WHERE salary=(SELECT MAX(salary) FROM emp);

注意: 1.子查询要加上括号
2.关注子查询的查询结果

子查询的不同情况:
1.子查询的结果是单行单列:
* 这种子查询我们可以直接使用作为条件,及配合各种运算符

如: 查询最小工资
查询工资 > 平均工资 百分之20所有员工信息
— 查询工资 > 平均工资 百分之20所有员工信息
SELECT * FROM emp WHERE salary > ((SELECT AVG(salary) FROM emp)*1.2);

2.子查询结果是多行单列的
* 这种子查询我们可以通过in的关键字来判断

— 查询部门为 开发部 和 市场部的所有员工信息
SELECT * FROM emp where dept_id in
(select id from dept where name=’开发部’ or name=’市场部’)

3.子查询的结果是多行多列的
* 这种子查询 我们可以把它当做一个虚拟的表来使用,但一定要加别名

— 查询入职日期在 2011-11-11日之后入职的员工所有信息及部门信息
— 查询入职日期在 2011-11-11日之后入职的员工所有信息及部门信息
select * from emp where join_date > ‘2011-11-11’
select
*
from
dept t1,(SELECT * FROM emp WHERE join_date > ‘2011-11-11’) t2
where
t1.`id` = t2.dept_id
多表查询练习:
见: 多表查询练习.sql

数据库的事务:
事务(transaction): 如果一个包含多个步骤的操作,那么这些操作要么全部成功,要么全部失败

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

事务提交的两种方式:
自动提交事务
mysql默认自动提交事务

DML语句执行时,mysql都会帮我们开启一个事务,自动提交这个事务

手动提交事务
oracle默认手动提交事务

查看事务提交方式:
select @@autocommit; — 1代表当前为自动提交事务 0代表手动提交事务
修改事务提交方式:
set autocommit=0;

事务的隔离级别:

1. * 事务的四大基本特性(ACID)
原子性(Atomicity): 一系列的操作,要么全部成功,要么全部失败
一致性(Consistency): 事务操作前后,数据总量不变
隔离性(Isolation): 事务间的操作应该是独立的
持久性(Durability): 事务完成后,数据必须持久化

2. * 数据库并发操作会产生的问题。
1.脏读
一个事务操作读取另一个事务中没有提交的数据

2.不可重复读
在同一个事务中,两次读取数据的结果内容不一致

3.幻读
一个事务操作表中的数据时,
另一个事务对表的数据做了添加或者修改的操作,
造成两次读取数量不一致
3. 上面的并发问题可以通过事务隔离级别解决。
* 隔离级别:
1.读未提交 read uncommitted
并发问题:脏读 不可重复读 幻读
2.读已提交 read committed (oracle默认隔离级别)
并发问题: 不可重复读 幻读
3.可重复读 repeatable-read (mysql默认隔离级别)
并发问题: 幻读
4.串行化 Serializable
解决所有并发问题

注意: 事务级别从小到大安全性越来越高,但性能会越来越差

查看隔离级别:
select @@tx_isolation;
更改隔离级别
set global transaction isolation level 隔离级别

DCL主要作用: 管理用户,管理权限

管理用户:
查询用户:use mysql数据库
select * from user;
主机名: localhost 只允许本机用户访问
IP 只允许固定IP访问
% 代表用户可以从任意IP地址访问

添加用户:
create user ‘用户名’@’主机名’ identified by ‘密码’

修改密码:
update user set password = password(‘新的密码’) where user=’用户名’
set password for ‘用户名’@’主机号’ = password(‘新密码’);

删除用户:
drop user ‘用户名’@’主机号’

管理权限:
查看权限:
show grants for ‘用户名’@’主机号’
授予权限:
语法: grant 权限列表 on 数据库.表 to ‘用户名’@’主机号’

权限列表: Select,insert,update,alter
全部权限: All

数据库:指定用户授权的数据库
全部数据库: *

表: 指定用户授权的表
全部的表:*

撤销权限:
语法: revoke 权限列表 on 数据库.表 from ‘用户名’@’主机号’

转载请注明:ZMAS‘S blog » mysql进阶操作总结。

喜欢 (2)
发表我的评论
取消评论
表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址