子查询/连接查询/视图/权限/设置密码

Posted on Posted in php
Tips: 本文创建于2014年4月28日,已超过 2 年,内容或图片可能已经失效!

目录

子查询

标量子查询
select * from students where score=(select max(score) from students);

from子查询
要给子查询返回结果加别名,它相当于一个临时表!
select * from (select * from students where gender='female') as s where classname='php';

列子查询,使用in
select * from classinfo
where classid in
(select distinct(classid) from studentinfo where classid is not null);

表子查询
select * from students
where
(score,classname)
in
(select max(score), classname from students group by classname);

行子查询
select * from students
where (score,classname) in
(select max(score),classname from students
group by classname having classname='php'
);

子查询对表数据更新的影响
Where型子查询使用的表不能够在主查询语句中更新。
解决办法:
把子查询放到另一个子查询中作为from的数据源(临时表)就可以进行更新操作了!
update students set score=400
where score=(select * from
(select max(score) from students) as t);

Some和all
Some理解成随便哪一个匹配上
All 全部匹配上
In 和 =some(子查询) 是一个意思
select * from classinfo
where classid=some(select distinct(classid) from studentinfo where classid is not null);
Not in 相当于!=all
select * from classinfo where classid !=all(select distinct(classid) from studentinfo
where classid is not null
);

连接查询

内连接/交叉链接
交叉链接就是不加on条件
select * from classinfo as c
inner join studentinfo as s
on s.classid=c.classid;

外连接
左外连接,以左表为主
右表中和左表不匹配的数据,不会出现在结果中。
如果和右表多条数据匹配成功,最终返回的数据可能会多于左表记录数。
如果连接条件在右表中找不到对应数据,用NULL补齐。
select * from studentinfo as s
left join classinfo as c
on s.classid=c.classid;

右外连接,右表为主
如果右表中的数据,在左表没有找到对应,则用NULL补齐。
左表中如果有不满足连接条件的数据,不会出现在返回的结果中。
select s.studentname as name,s.gender,c.*
from studentinfo as s
right join classinfo as c
on s.classid=c.classid;

链接条件
on表达式
select * from studentinfo as s
left join classinfo as c
on s.classid=c.classid;
using(连接字段)
select * from studentinfo as s
left join classinfo as c
using(classid);

自然连接,natural
自动判断连接条件进行匹配
它分成:
Natural join
Natural left join 相当于left join using(连接字段)
Natural right join

select * from studentinfo as s
natural left join classinfo as c;

练习
球队表

ID球队名称
1恒大
2国安
3申花

比赛信息表

ID主队ID客队ID比赛结果(主:客)
1122:1
2131:3
3213:4
4232:0
5310:5
6323:1

写一个程序,显示比赛结果
主队名 比分 客队名
恒大 2:1 国安

[code lang="mysql"]
create table team (
id int unsigned auto_increment primary key,
name varchar(10) unique key
);
insert into team values
(null, '恒大'),
(null, '国安'),
(null, '申花');

create table `match` (
id int unsigned auto_increment primary key,
hid int unsigned,
gid int unsigned,
result varchar(10)
);
insert into `match` values
(null, 1, 2, '2:1'),
(null, 1, 3, '1:3'),
(null, 2, 1, '3:4'),
(null, 2, 3, '2:0'),
(null, 3, 1, '0:5'),
(null, 3, 2, '3:1');

select t1.name as hname,m.result,t2.name as gname
from `match` as m
left join team as t1
on m.hid=t1.id
left join team as t2
on m.gid=t2.id;
[/code]

视图

创建视图
create view suclass as
select * from classinfo where classid in
(select distinct(classid) from studentinfo where classid is not null);

使用视图
select * from stuclass;

查看视图
show create view stuclass\G
show tables;
使用show table status where comment=’view’可以查看到当前库中的视图有哪些

利用show full tables查看库中表和视图有哪些

修改视图
alter view stuclass as
select * from classinfo
where classid in
(select distinct(classid) from studentinfo
where classid is not null);

删除视图
drop view stuclass;

视图的算法
我们在使用视图时,其实就是调用执行SQL查询语句,但是系统有几种执行方式。
视图支持方式:
Merge 合并算法,将视图的语句与外部使用视图的语句合并后一起执行。
Temptable 临时表,将视图的语句先执行完,结果保存到一张临时表中
Undefined 未定义,由系统来决定怎么处理

完整的创建视图语句格式:
Create algorithm = merge|temptable view 视图名称 as 查询语句

用户权限管理

创建用户
create user admin identified by '123456';

查看用户
可以从系统表mysql.user中查看到用户的权限情况
首先创建一个用户后,就会在mysql.user中增加一个用户记录

全局权限,保存在mysql.user表中
数据库级权限,保存在mysql.db表中
表级权限,保存在mysql.tables_priv表中
字段给权限,保存在mysql.columns_priv表中

还有另外一个辅助表:mysql.db,对登陆主机有影响,不参与权限

删除用户
drop user admin;

授权命令
grant select on db1.* to 'admin'@'%'
identified by '123456';
select * from user where user='admin'\G
追加一个update权限
grant update on db1.* to 'admin'@'%';

删除授权
revoke update on db.* from admin;

设置密码

set password for admin=password('123456');
改自己的密码
set password=password('123456');