MySQL基础教程2-DQL(select查询)

select查询

一、group分组与统计函数

理解: 根据某一指定属性对前面select之后的运算进行分组执行
例如: select age,avg(id) from user group by age;
      解释:分别求出各年龄(age)段的id平均值

本小节知识清单:

• max()
• count()
• avg()
• min()
• sum()
• round()

select max(age) from user;          /* 获得所有用户最大的年龄 */
select count(age) from user;        /* 获得所有用户的数量 */
select avg(age) from user;          /* 求出所有用户的年龄平均值 */
select min(age) from user;          /* 获得所有用户最小的年龄 */
select sum(age) from user;          /* 求出所有用户的年龄之和 */
select sum(id*age) from user;       /*求出所有用户的 id*age 值的和*/

round()函数

1. round(x,d)  ，x指要处理的数，d是指保留几位小数
这里有个值得注意的地方是，d可以是负数，这时是指定小数点左边的d位整数位为0,同时小数位均为0；
2、round(x)  ,其实就是round(x,0),也就是默认d为0；
下面是几个实例
1. 查询: select round(1123.26723,2);
     结果：1123.27
2、查询: select round(1123.26723,1);
     结果: 1123.3
3、查询: select round(1123.26723,0);
     结果：1123
4、查询: select round(1123.26723,-1);
     结果: 1120
5、查询: select round(1123.26723,-2);
     结果：1100
6、查询: select round(1123.26723);
     结果：1123

根据年龄分组计算id的平均值

select age,avg(id) from user group by age;

效果:

查询每个年龄下的用户的数量

select age,count(*) from user group by age;

效果:

查询每个年龄下 id 最高的用户

select age,max(id) from user group by age;

二、having筛选

(1).查询age-id>15的用户

select name,age-id from user where (age-id)>15;
select name,(age-id) as demo from user where demo>15;        /* 错误 */
select name,(age-id) as demo from user where 1 having demo>15;   /* 正确 优化 */
注意：博主在使用index作为替代变量时执行语句会出错，可能index为MySQL保留字，望读者注意

(2).where-having-group综合练习题

有如下表及数据

name	subject	score
张三	数学	90
张三	语文	50
张三	地理	40
李四	语文	55
张三	数学	45
张三	数学	30

要求: 查出两门及两门以上不及格者的平均成绩:

select name,sum(score<60) as gk,avg(score) as pj
     -> from  result group by name
     -> having gk>=2;

三、order by排序

• 降序: desc
• 增序: asc [默认asc]
• 多列排序: 逗号隔开，依次添加条件即可

/* 将用户按照age增序排序 */
select * from user order by age;
/* 将用户按照age进行降序排序 */
select * from user order by age desc;
/* 先按照age进行增序排序，再为分组好的age序列根据id进行降序排序；*/
select * from user order by age asc,id desc;

四、limit语法

找出年龄前三名的同学
select * from user order by age limit 0,3;
0: 偏移量。默认是0，可以省略
3: 取值个数

• **LIMIT**可以用来实现分页功能
• 但是数据太大的时候limit表现的并不是很完美,这里链接以下掘金里的一个文章，有兴趣的读者可以去看一下: MySQL之LIMIT性能问题

练习:
利用where,group by,having，order by,limit 其中几种的组合指令查询每个age段里id最大的用户
注意: 如果选择组合使用这几种，那么他们的顺序必然是固定的，不可颠倒

/* 利用子查询进行查找，具体子查询详情在---where型子查询---中再讲 */
select age,id,name from user where id in (select max(id) from user group by age);

五、where型子查询

查出年龄最大的用户:

/* 排序法(浪费资源) */
select name,age from user order by age limit 0,1;
/* 下面一条是博主尝试的一个****错误的思路****，无法执行 */
select id,name max(age) as max from user where 1 having age=max;
/* 利用子查询(正确、高效) */
select name,age from user where age=(select max(age) from user);

注意: 子查询语句只能有一个返回结果，有多个返回结果不然会报错。

六、from型子查询

理解: select 语句返回的结果也是一张表，大胆的把select的结果当作另一个select语句所要查询的表即可

查询年龄为19中id最大的用户:

select id,name,age from (select * from user where age=19) as tmp order by id desc limit 0,1;

查询出编号为19的商品所在的栏目的名称:
注意: 此表中只包含栏目的编号，并不包含栏目的名称，栏目的编号与名称对应关系在另外一张表中.可以用**子查询或下面的连接查询**

/* 子查询 */
select name from xxx1 where index=(select index from xxx2 where id =19);
xxx1: 栏目的编号与名称对应关系表
xxx2: 商品表

/* 连接查询 */
略

七、exists型子查询

理解: 如果子查询有结果，主查询就继续根据结果去查询；如果子查询没有结果，主查询就不继续根据此结果查询

**用exists型子查询，查出所有里面有商品的栏目

select * from category 
     where exists (select * from goods where goods.cat_id=category.cat_id)
/* c此语句会将category表中的所有 cat_id 与进行查询 */

八、新手1+ N查询(反例)

$sql = 'select cat_id,shop_price from goods where shop_price>2000';
$rs = mysql_query($sql);

$data = array();
while() {
     $row...
}

$data = array(7条商品);

foreach($data in $goods) {
     $sql = 'select cat_name from category where cat_id = $goods['cat_id']';
}

• 即: 一条语句—>N条查询
• 这种写法是非常低级的写法
• 可以用内联查询解决这种问题

九、内联查询

语法:

select xxxx from
     -> table1 inner join table2 on table1.xx=table2.xx;

有这样两张表:(假如说boy与girl表中hid匹配上的用户就是搭档关系)

要求: 将这两张表hid相同的人(name)组合起来(输出所有搭档组合)

select boy.hid,boy.bname,girl.hid,boy.gname
     -> from
     -> boy inner join girl 
     -> on boy.hid=girl.hid;

输出结果:

十、左连接及右连接查询

• 理解:还是上面的例子。输出所有boy，有搭档girl匹配显示上，没有搭档就显示为NULL
• 左连接即以左边数据为准查询右边数据，查不到补NULL

(1).左连接:

select boy.hid,boy.bname,girl.hid,boy.gname
     -> from
     -> boy left join girl
     -> on boy.hid=girl.hid;

左连接查询结果:

(2).右连接

select boy.hid,boy.bname,girl.hid,boy.gname
     -> from
     -> boy right join girl
     -> on boy.hid=girl.hid;

右连接查询结果:

(3).练习:查询所有商品的商品名，栏目名，价格
注: 栏目名在另外一张表中，因此要进行连接查询

select goods_id,cat_name,goods.cat_id,shop_price
     -> from
     -> goods left join category on goods.cat_id=category.cat_id;

(4).练习:查询所有第四栏目下所有商品的商品名，栏目名，价格

select goods_id,cat_name,goods.cat_id,shop_price
     -> from
     -> goods left join category on goods.cat_id=category.cat_id
     -> where goods.cat_id=4;

(5).一道面试题
有如下两张表:

• Match的hosteamID与guestTeamID都与Team中的teamID关联
• 题目: 查出2006-6-1到2006-7-1之间举行的所有比赛，并且用以下形式列出:
  拜仁 2：0 不来梅 2006-6-21

select mid,t1.tname as hname,mres,t2.tname as gteam,matime
     -> from
     -> m inner join t as t1 on m.hid=t1.tid inner join t as t2 on m.gid=t2.tid
     -> where matime between '2006-06-01' and '2006-07-01';

查询结果:

十一、union查询

使用场景: 2条语句，各自的where条件非常复杂，可以简化成简单条件，再union

例:找出年龄(age)为18和20的用户

select name,age from user where age=18
     -> union
     -> select name,age from user where age=20;

• union语句必须满足一个条件:各语句取出的列数相同
• 列名称不一定要相等，列名称会使用第一条sql的列名为准
• 注意: 使用union时，完全相等的行将会被合并。
并且: 合并是比较耗时的操作。使用'union all'避免合并
  union的子句中，不用谢order by。sql语句合并后得到的结果可以oeder by。

例题:
有如下两张表，将两张表中的数num加起来。

select id,sum(num)
     -> from
     -> (select * from a
     -> union all
     -> select * from b) as tmp
     -> group by id;

十二、查询练习

user表中的数据(练习用):

1.基础查询 where 的练习

查询满足以下条件的商品:

1.1.主键为 3 的用户

select * from user where id=3;

1.2.年龄不是 19 的用户

1. select name,age from user where age!=19;
2. select name,age from user where age<>19;

1.3.年龄大于19的用户

select name,age from user where age>19;

1.4.年龄低于或等于19的用户

select name,age from user where age<=19;

1.5.年龄为19或20的用户(不许用or)

/*  1. select * from user where age=19 or age=20; */
2. select * from user where age=19 || age=20;
3. select * from user where age in (19,20);

1.6.找出18<=年龄<=19的用户(不许用and)

/* 1. select name,age from user where age>=18 and age<=19; */
2. select name,age from user where age between 18 and 19;

1.7.找出年龄不是18且不是19的用户(and或not in分别实现)

1. select name,age from user where age!=18 and age!=19;
2. select name,age from user where age not in (18,19);

1.8.找出年龄大于17且小于19，或者年龄大于20小于22的用户

select name,age from user where (age>17 and age<19) or (age>20 and age<22);

1.9.找出年龄为19岁并且id<2或>4,并且name为’zk’的用户

select * from user 
     where age=19 and (id<2 or id>4) and name='zk';
/* 括号是必要的，因为不加括号就会使and先执行 */

1.10.找出name为’zk’的用户

(注意:不存在’zk’在这个用户，但是他有两个儿子。此处假设有father这个表项)

select * from user where name in ('zk1','zk2');

1.11.找出name以’w’开头的用户

1. select name,age from user where name like 'w%';

/* 匹配以name'w'开头并且后面有两个任以字符的用户 */
2. select name,age from user where name like 'w__';   

1.12.找出年龄在18到20之间，并且id>3,name以’z’开头的用户

1. select name,age from user where age>18 and age<20 and id>3 and name like 'z%';
2. select name,age from user where age between 18 and 20 and id>3 and name like 'z%';

2.一道面试题

有如下表(girl)和数组
把num处于[20，29]之间的数值改为20
把num处于[30，39]之间的数值改为30，

num

3

12

15

25

23

29

34

37

32

update girl set num=floor(num/10)*10 where num between 20,30;

3.练习题

把user表中用户名为’慕容xxxx’的用户，改为’诸葛xxxx’
提示: 大胆的把列看成变量，参与运算，甚至调用函数来处理。
substring(), concat()

update user set name=concat('诸葛',substring(name,3)) where name like '慕容____';