sql语句

条件查询   集合函数   分组语句   模糊查询   子查询    表的连接

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;