mysql实现年龄大于17小于30,MySQL基础教程2-DQL(select查询)

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

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

select查询

理解: 根据某一指定属性对前面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;

(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;

降序: 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;

找出年龄前三名的同学

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);

查出年龄最大的用户:

/* 排序法(浪费资源) */

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);

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

理解: 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型子查询，查出所有里面有商品的栏目

select * from category

where exists (select * from goods where goods.cat_id=category.cat_id)

/* c此语句会将category表中的所有 cat_id 与进行查询 */

$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';

查询结果:

使用场景: 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

32update 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 '慕容____';

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