条件查询 集合函数 分组语句 模糊查询 子查询 表的连接
1) 条件查询
between a and b , in(a,b,c), not exists, is null , like '%_' , or , and, any , all
select id,name from student; 查询id,name
select id,name 姓名 from student; 起别名
select id ,name 姓名,age from student where age>20; 年龄大于20的学生
select id ,name 姓名,age from student where age>20 and sex='男' ; 年龄大于20的男学生
select id ,name 姓名,age from student where age>20 or sex='男' 年龄大于20,或者是男的学生
select name from student where age between 20 and 25; 年龄在20-25之间的学生姓名
select naem,age from student where age not between 20 and 25;不在20-25岁的学生的姓名和年龄
select name from student where name like '张%'; 查询姓张的人(模糊查询)
select name,age from student where age in(20,23,25,28); 在()范围内查询
select name,age from student where age is not null; 年龄非空的学生
2) 集合函数
select avg(age) from student; 年龄平均值
select max(age) from student; 年龄最大值
select min(age) from student; 年龄最小值
select sum(age) from student; 年龄总值
select count(id) from student; 学生的个数
select distinct sex from student; distinct去掉重复的记录
select all sex from student; all为查询的默认条件
3) 分组语句
select count(id) from student group by sex; 通过性别进行分组
select count(id) num,sex from student group by sex; 通过性别进行分组 (num,sex为别名)
select count(id) num,sex from student where age>20 group by sex; 年龄大于20的学生通过性别进行分组 (num,sex为别名)
select count(id) num,sex,name from student where age>20 group by sex,name;
select max(age) from student group by sex; 通过性别分组找出每组年龄最大的
4) 子查询(嵌套查询)
select max(age) from student where sex='男';
select name from student where age=(select max(age) from student where sex='男');
找出比李四年龄小的学生的姓名
select age from student wher name='李四';
select name from student where age<(select age from student wher name='李四') and sex='女';
5) 表的连接
select s.name sname, c.name cname age from student s,class c where s.cid=c.id;
select s.name c.id from student s full outer join class c on s.cid=c.id;