DML操作及简单查询

LIMIT子句：
limit子句:设置位置偏移量和行数
1、limit 3 表示前三行；
2、limit 1,3 表示从第二行开始的三行，即2-4；
计数
1、select AVG(age) as 平均年龄 from 表名;
2、select SUM(age) as 年龄总和 from 表名;
3、select MAX(age) as 最大年龄 from 表名;
4、select MIN(age) as 最小年龄 from 表名;
5、select COUNT(age) as 总人数 from 表名;
查询
1、查询比李四年龄大的学生
select name,age from jiaoshi where age>(select age from jiaoshi where name=‘李四’);
2、查询和李四年龄不一样大的学生
select name,age from jiaoshi where age<>(select age from jiaoshi where name=‘李四’);
select name,age from jiaoshi where age!=(select age from jiaoshi where name=‘李四’);
3、查询年龄15---- 19的学生
select name,age from jiaoshi where age>=15 and age<=19;
select name,age from jiaoshi where age between 15 and 19;
4、查询年龄15和19的学生
select name,age from jiaoshi where age in(15,19);
5、模糊查询姓张的学生(%:任意长度的任意字符)
select name from jiaoshi where name like ‘张%’;
6、查询年龄不为空的学生
select name,age from jiaoshi where age is not null;
表连接
笛卡尔积：
select j.name ,g.grade from jiaoshi as j,grade as g where j.id=g.sid;
1、查询学生姓名和成绩(内连接)
select j.name ,g.grade from jiaoshi as j inner join grade g on j.id=g.sid;
2、查询学生姓名和成绩(左外连接)
select j.name ,g.grade from jiaoshi as j left join grade g on j.id=g.sid;
3、查询学生姓名和成绩(右外连接)
select j.name ,g.grade from jiaoshi as j right join grade g on j.id=g.sid;
4、查询未参加考试学生姓名和成绩
select j.name ,g.grade from jiaoshi as j inner join grade g on j.id=g.sid where g.grade is null;
左连接则是以左边表格为主，右表格对应的没有数据也显示为空，右连接相反，如果左表格多出的部分不显示。