MYSQL:查询语句
1.连接查询
(1) 内连接:查询结果必须满足条件
格式: select ... from 表1 inner join 表2 on 连接条件 ...
(2)外连接:查询出的结果存在不满足条件的可能
左外连接:先查询出左表,然后查询右表中满足条件的显示出来,不满足条件的显示NULL
select ... from 表1 left (outer) join 表2 on 连接条件... // outer 可省略
右外连接:先把右表中所有记录都查询出来,然后左表 满足条件的显示,不满足显示NULL
select ... from 表1 right (outer) join 表2 on 连接条件...
(3)全连接: 在内连接的基础上,还包含两个表中所有不符合条件的数据行,并在其中的左表、和右表列填写NULL
select ... from 表1 full join 表2 on 连接条件 (Mysql不支持)
注意:
1) 内连接是将两张表中所有符合连接条件的数据列入结果,不符合连接条件的结果中没有
2) 如果连接的两表中有同名的列,列前面要加表名(或表别名)来区分(否则会报歧义错误)
3) inner join ... on 的写法是符合SQL-92标准写法,其实还有一种内连接的写法: 格式: select ... from 表1, 表2 where 连接条件; 4)左外和右外与表的先后次序有关,而内连接与表的先后次序无关
2.多表查询:
select ... from 表1
inner join 表2 on 连接条件
inner join 表3 on 连接条件
....;
例子
select * from student a inner join sc b on a.sid = b.sid
inner join course c on b.cid = c.cid
inner join teacher d on c.tid = d.tid
order by a.sid, b.cid;
注意:
1)多表查询中,只能使用 同一种 连接方式,不能多种连接方式嵌套使用 2)性能上:连接的表越多,性能越低, 可以把连接查询变成分多次查询
自连接: 同一个表自己和自己相连接,经常用于树状结构的数据表示
例: 陕西省
西安
雁塔区
高新区
咸阳
宝鸡
表结构:表名 :country
id name parent_id
1 陕西省 null
2 西安 1
3 雁塔区 2
查询语句: select c1.id,c1.name, c2.parent_id from country c1 left join country c2 on c1.id=c2.parent_id;
3.子查询:
把某一个 select 的结果 当作是另一个 select 的一个值,或者一张表继续进行查询
1)把一个select的结果当作另一个select 的值:
例:
(1)select max(sal) from emp; // 5000
(2)select * from emp where sal = (select max(sal) from emp); // 当作一个值
把select max(sal) from emp当成了一个值,代入到主查询当中,代入时需要在子查询的两边加()
2)把一个select的结果当作另一个select的表
例:
(1) 先查询平均工资
(select deptno,avg(sal) from emp group by deptno) a
(2) 再把子查询看做临时表,与其它表做表连接
select * from (子查询(1)) a inner join dept b on a.deptno=b.deptno;
事务(TCL):指一个事务内,多条sql语句是作为一个整体执行的。
一个事务内的多条sql是作为一个原子操作,不可以被分割 要么都成功,要么都不成功。
1)start transaction (begin): 开启事务
2)commit: 提交事务(让对数据的更改操作生效)
3)rollback: 回滚事务 (让对数据的更改撤销,返回到未更改之前)
注意: 事务内所有更改,在结束之前,对于其它用户来讲都是不可见的。
事务commit提交时,这些更改才会真正生效,其它用户才能看到你的更改。
事务执行中如果出现意外情况,这时候可以执行rollback,可以撤销事务内所有更改,恢复到事务开始的时刻
commit 和rollback都意味着事务结束
事务的四个特点 (ACID):
A:(Atomic)原子性: 指事务内多条sql是作为一个整体执行
C:(Consistency)一致性: 事务开始前后,整个数据的状态应当一致
I: (Isolation)隔离性:指事务的隔离级别(未提交读,提交读,可重复读,序列化读)
隔离级别不同会出现的几种现象:
1) 脏读(读取到了未提交的数据)
例:
客户1 客户2
1 号账户余额 10000.0
begin;
update 1 号账户余额50000.0
select 1 号账户余额 50000.0 脏读
rollback;
select 1 号账户余额 50000.0(本应该是 10000.0)
2)提交读 : 在同一个事务中,对于同一份数据读取到的结果不一致 (事务提交之后其他用户才可查询该数据) 避免脏读现象, 将隔离级别升级为提交读
查询到的肯定是别人提交后的结果,提交读下不会有脏读, 但会有不可重复读现象:
例:
客户1 更新 客户2查询
1 号账户余额 10000.0
begin;
select ... 10000.0
begin;
update 1 号账户余额50000.0;
commit;
select ... 50000.0
commit;
3)为了避免不可重复和脏读的现象,可以将隔离级别升级可重复读(mysql默认隔离级别)
4) 幻读 : 在同一个事务中,同一个查询多次返回的结果不一致
(可以将隔离级别提高为序列化读,即可避免幻读现象)
例:
客户1 新增 客户2查询
原始记录是10条
begin begin;
查询个数 10
insert 1
commit;
查询个数 11
commit;
5) mysql的【可重复读】隔离级别三种现象都可以避免
D:(Durability)持久性: 事务中做的更改必须在事务结束后永久生效
DCL:数据控制语言
grant : 给用户授权
格式:
grant all on 数据库中的库名.* to ‘用户名’@‘localhost’ ;
例: grant all on test.* to 'user1'@'localhost' ;
all:授予该用户 增删改查 的全部权限
test.* : 指定该用户可以对 该库(test)中的所有表进行操作
grant select on test.studnent to 'user1'@'localhost'
只给用户(user1)对该库(test)中的 student 表 进行 查询(select)权限
revoke: 收回之前给用户的授权
格式:
revoke all on 数据库中的库名.* from ‘user1’@'localhost' ;
收回对该用户(user1)授予的所有权限