Lanms Blog

Mysql数据查询语言
Publish: 2019/3/29   

DQL - 数据查询语言

关键字 SELECT / as / from /( = <> < > )/ is null / is not null/ distinct (去重)/ order by...[asc desc] /group by ... having / where (条件) /inner join... on / left outer join ... on / right outer join ... on/ full outer join ... on / limit (偏移量),(查询条数) / limit offset (查询条数), (偏移量)

low_priority 低优先级 (比如在建表后插入数 (insert into)时 , 用降低优先级的形式最后插入数据)

concat(a, ":" b) 将属性 a , b两个字符串属性拼接输出

查多张表时,不写连接条件会查到笛卡尔积

筛选

聚合函数

分组查询/筛选


select cid avg(score) from tb_sc group by cid having avg(score)>60;

select cid if(ssex, '男', '女') as 性别, count(*) as 人数 from tb_student
where saddr is not null group by ssex order by ssex desc
order by sbirth desc;  -- 排序  desc 降序排序 asc是默认排序 

子查询


select sname from tb_student
where stuid in
(
select sid from tb_sc
group by sid having count(sid) > 2;
) and sex=1;  -- 写在右边(后面)的条件先执行

连接查询

  1. 自然连接查询
  2. 内/ 左外/ 右外/ 全 连接查询

自然连接查询

内连接

`sql lite

– 自然连接写法
select sname, avgScore from tb_student t1,
(select sid, avg(score) as avgScore from tb_sc
group by sid) t2 where stuid=sid;

– 内连接写法
select sname, avgScore from tb_student t1
inner join (select sid, avg(score) as avgScore
group by sid) t2
on stuid=sid;




#### 左外连接查询

- 查询语句`left outer join ... on `
- 自然连接条件变为  `stuid=sid(+)  ` 也表示左外连接  ` MySQL`是不支持这种语法的
- 左表不满足筛选条件的的记录也会查询出来 ,会将**`null `**也会查找出来
- `left outer join ... on`  

```mysql

select sname, sCount as sCount 
from tb_student t1
left outer join
(select sid, count(sid) as sCount from tb_sc
group by sid) t2 on t1.stuid=t2.sid;
王大锤    4
骆昊    3
杨飘飘    null
张三丰    2

右外连接

全外连接

分页查询


select sname, cname, score from tb_sc
inner join tb_student on sid=stuid
inner join tb_course on cid=courseid
order by score desc  -- 排序在分页前
limit 5;   -- --  默认偏移量是0  只5条数据
limit 0, 5;  -- 前面是查询开始的偏移量,后面是查询的条数
limit 5 offset 0; -- 前面是查询的条数, 后面是查询的开始偏移量

代码下载–coding代码仓库



← 建造者模式 原型模式 →

Powered by Hexo, Theme designs by @hpcslag.
Style-Framework Tocas-UI designs by @yamioldmel