学习第十五天

DQL

 

 

 

 

 

基础查询

select name,age from stu;

查询名字和年龄

DISTINCT

去除重复信息

select * from stu;

 

 

select name,math as 数学成绩， engilsh as 英语成绩 from stu;

查询姓名英语数学成绩

 

 

 

 

条件查询

 

 

 

select * from stu where age>20;

 

 

select * from stu where age>=20;

 

 

 

select * from stu where age>=20 && age <= 30;

select * from stu where age>=20 and age <= 30;

select * from stu where age BETWEEN 20 and 30;

 

 

 

 

 

 

select * from stu where heire_date BETWEEN '1998-09-01'and '1999-09-01';

 

 

 

 

 

 

select * from stu where age != 18;

select * from stu where age <> 18;

都表示不等于

 

select * from stu where age= 18 || age = 20 || age = 22;

select * from stu where age= 18 or age = 20 or age = 22;

select * from stu where age in (18,20,22)

 

 

 

注意:null值的比较不能使用= != 。需要使用is 或者is not select * from stu where english = null; --不行的 select * from stu where english is null;

 

 

 

模糊查询

select * from stu where name like '马%'；

 

 

select * from stu where name like '-马%'；

 

 

select * from stu where name like '%德%'；

 

 

排序查询

 

 

select * from stu ORDER BY age ;

 

 

select * from stu order by math desc;

 

 

 

select * from stu order by math desc,english asc;

 

 

 

分组查询

聚合函数

 

 

 

 

select count (id) from stu ;

 

 

select max(math) from stu;

 

 

 

select sum (math) from stu;

 

 

select avg (math) from stu;

 

 

 

 

 

 

 

select sex,avg(math) from stu group by sex;

 

 

select sex,avg(math) , count(*) from stu group by sex;

 

 

 

select sex,avg(math) , count(*) from stu where math > 70 group by sex;

 

 

select sex,avg(math) , count(*) from stu where math > 70 group by sex having count ()>2;

 

 

分页查询

 

 

select * from stu limit 0 , 3;