SQL高级语句

1、连接查询

• 交叉连接：返回所有数据行的笛卡尔积

方法一

select * from department cross join employee;

方法二

select employee,name,department.dname from department,employee where 
department.did = employee.did;

• 内连接：使用比较运算符对两个表数据进行比较，包含符合条件的数据

方法一

select employee,name,department.dname from department join employee on 
department.did = employee.did order by age desc;

方法二

select employee,name,department.dname from department,employee where 
department.did = employee.did;

自连接（特殊的内连接）

select p1.* from employee as pl join employee as p2 on p1.did = p2.did 
where pz.name = '王红';

• 外连接：分为左连接和右连接，包括左表\右表\两个表中的所有数据

左连接(左表满足条件的连接右表的）

select department.did,department.dname,employee.name from department 
left join employee on department.did = employee.did;

右连接(右表满足条件的连接左表的）

select department.did,department.dname,employee.name from department 
right join employee on department.did = employee.did;

2、子查询

可使用in,exists,any,all,比较运算符,只返回true\false,返回true外层查询才执行
“带in”：

select * from department where did in(select did from employee where age = 20);

select * from department where did not in(select did from employee where age = 20);

“带exists”：

select * from department where exists(select did from employee where age = 20);

“带any”：

select * from department where did > any(select did from employee);

“带all”：

select * from department where did > all(select did from employee );

“带运算符”：

select * from department where did = (select did from employee );

3、事务及储存过程

1）开启事务 start transaction;
2）SQL语句 …………………………..;
3）提交事务 commit;
4）取消事务 rollback;
事务四个特性：
1）原子性
2）一致性
3）隔离性
4）持久性
问题：
脏读、不可重复读、幻读
解决：
设置隔离级别 1)读未提交 2)读提交”防止脏读” 3)可充分读 4)可串行化”防止不可重复读\幻读”

演示脏读：一事务读取另一事务未提交数据
b账户

start transaction;
select * from account;

a账户

start transaction;
update account set money=money-100 where name='a';
update account set money=money+100 where name='b';

b账户

select * from account;
commit;

a账户

rollback;

演示不可重复性：在一个事务内重复读取别的线程已提交的数据
b账户

start transaction;
select * from account;

a账户

update account set money=money-100 where name='a';
select * from account;

b账户

select * from account;
commit;

演示幻读：一个事物内两次查询的数据条数不一致（其他事务插入，记录数增加）
a账户

set session transaction isolation level read committed;

b账户

start transaction;
select * from account;

a账户

select * from account;
insert into account(name,money) values('c',1000);

b账户

select * from account;
commit;

4、创建存储过程Proc()

delimiter //
creat procedure proc()
begin
    select * from student;
end //
delimiter;

调用存储过程

call proc;

删过程

drop proc;

5、光标的使用
1）声明（光标名cursor_student，表名student）

declare cursor_student cursor for select * from student;

2）打开光标

open cursor_student;

3）操作光标

fetch cursor_student into s_name,s_gender;
select s_name,s_gender;

4）关闭光标

close cursor_student;

5、视图

优点：简化查询语句；安全性；逻辑数据独立性
1）创建视图

create view stu_view as select math,chinese,math+chinese from student;

2）在多个基本表上创建视图

create view stu_class(id,name,class) as select student.sid,student.name,stuinfo.calss
from studnet,stuinfo where student.sid = stuinfo.sid;

3）查看视图

desc stu_view;

或

show table status like stu_view\G;

或

show create view stu_view\G;

4）修改视图

create or replace view stu_view as select * from student;

或

alter view my_view as select chinese from student;

5）更新视图

update stu_view set chinese = 100;

或

insert into student values(4,'Lily',100,100);

或

delete from stu_view where math = 70;

6）删除视图

drop view if exists my_view;

6、数据库备份

备份单个数据库：

mysqldump -u root -p  --database chapter08>C:/backup/chapter08.sql;

备份多个数据库：

mysqldump -u root -p  --databases chapter08 chapter09>C:/backup/file.sql;

备份所有数据库：

mysqldump -u root -p --all-databases>C:/backup/pp.sql;

7、数据的还原

drop database chapter08;
show databases;
create database chapter08;
mysql -u root -p chapter08>C:/backup/chapter08.sql;
select * from student;

8、创建用户

分为超级用户（root用户）和普通用户
eg:用grant创建用户，用户名user1，主机名localhost，密码123，授予用户对chapter08.student表的查询权限

grant select on chapter08.student to 'user1'@'localhost' identified by '123';

验证是否创建成功

use mysql;
select host,user,password from user;

9、删除用户

drop user 'user1'@'localhost';

10、修改用户密码

修改root用户密码

set password = password('新密码');

roo用户修改普通用户密码

set password for 'username'@'hostname'=password('新密码');

普通用户修改密码

set password =password('新密码');

11、对新用户授予权限

eg:创建用户user4，密码123，对数据库有insert,select权限，使用with grant option

grant insert ,select on *.* to 'user4'@'localhost' identified by '123'
with grant option;

12、收回权限

revoke insert on *.* from 'user4'@'localhost';

13、收回所有权限

revoke all privileges, grant option from 'user4'@'localhost';