查询表中前三条数据
select * from student limit 3;
查询表中从第二条数据开始,向后数3条
select * from student limit 1,3;
查询成绩中及格的前三人
select id,grade from score where grade>=60 limit 3;
平均年龄
select avg(age) as 平均年龄 from student;
最大年龄
select max(age) as 最大年龄 from student;
年龄总和
select sum(age) as 年龄总和 from student;
最小年龄
select min(age) as 最大年龄 from student;
统计总人数
select count(*) as 总人数 from student;
查询姓张的人的编号和姓名
select id,name from student where name like '张%';
查询年龄在18-20之间的学生姓名和年龄
select name,age from student where age between 18 and 20;
select name,age from student where age>=18 and age<=20;
查询21、20、19岁的学生姓名和年龄
select name,age from student where age in(19,20,21);
查询年龄为空的人
select name,age from student where age is null;
查询年龄不为空的人
select name,age from student where age is not null;
查询学生的姓名和成绩(笛卡尔积)
select i.name,s.grade from student i,score s on i.id=s.sid;
查询学生的姓名和成绩(内连接)
select i.name,s.grade from student i inner join score s on i.id=s.sid;
查询学生的姓名和成绩(左外连接)
select i.name,s.grade from score s left join student i on i.id=s.sid;(只显示有成绩的)
查询学生的姓名和成绩(右外连接)
select i.name,s.grade from student i right join score s on i.id=s.sid;(只显示有成绩的)
查询有成绩的学生姓名和成绩
select i.name,s.grade from student i inner join score s on i.id=s.sid;
查询没有成绩的学生姓名和成绩
select i.name,s.grade from student i inner join score s on i.id=s.sid where s.grade is null;