查询数值型数据:
SELECT * FROM tb_name WHERE sum > 100;
查询谓词:>,=,<,<>,!=,!>,!<,>=,<=,in(字段值) ,not in(字段值)
查询字符串
SELECT * FROM tb_stu WHERE sname = '小刘'
SELECT * FROM tb_stu WHERE sname like '刘%'
SELECT * FROM tb_stu WHERE sname like '%程序员'
SELECT * FROM tb_stu WHERE sname like '%PHP%'
查询日期型数据
SELECT * FROM tb_stu WHERE date = '2011-04-08'
注:不同数据库对日期型数据存在差异: :
(1)MySQL:SELECT * from tb_name WHERE birthday = '2011-04-08'
(2)SQL Server:SELECT * from tb_name WHERE birthday = '2011-04-08'
(3)Access:SELECT * from tb_name WHERE birthday = #2011-04-08#
查询逻辑型数据 and or
select * from tb_student where sname like '%李%' and age in(18,20) and sex ='男'
逻辑运算符:and or
查询前0到10条记录
SELECT * FROM tb_name LIMIT 0,10;
limit语句与其他语句,如order by等语句联合使用,会使用SQL语句千变万化,使程序非常灵活
查询最后后10条记录
SELECT * FROM tb_stu ORDER BY id ASC LIMIT 10
查询指定范围的数据 between and
SELECT 要查找的字段 FROM 表名 WHERE 字段名 BETWEEN 初始值 AND 终止值
SELECT * FROM tb_stu WHERE age BETWEEN 0 AND 18
查询结果去重
SELECT DISTINCT 字段名 FROM 表名
select distinct(sex) from student
注:字段不能用*代替
查询条数
//年龄等于19的学生总数
SELECT count(*) FROM student WHERE age = '19'
排序
SELECT 字段名 FROM tb_stu WHERE 条件 ORDER BY 字段 DESC 降序
SELECT 字段名 FROM tb_stu WHERE 条件 ORDER BY 字段 ASC 升序
select * from sutdent order by age asc;
//先根据时间降序再根据价格升序排序
SELECT * FROM tb_name ORDER BY datetime DESC,price ASC
注:对字段进行排序时若不指定排序方式,则默认为ASC升序
having 的使用
#一般having配合group by分组 使用,先分组聚合后再判断
//查平均分大于等于60的学生姓名和平均分
select sname,avg(s_score) from tb_student group by sname having avg(s_score)>=60
对统计结果进行排序
函数SUM(字段名) 可实现对字段的求和
SELECT name,SUM(price) as sumprice FROM tb_price GROUP BY name order by sumprice desc
分组 group by
SELECT studentid,SUM(s_score) AS sscore,date FROM student GROUP BY studentid ORDER BY sscore DESC
注:当分组语句group by排序语句order by同时出现在SQL语句中时,要将分组语句group by 写在排序语句order by的前面,否则会出现错误
多表查询
SELECT a.sname,avg(b.s_score) as avgPrice,sum(b.s_score) as sumPrice FROM student a ,score b where a.studentid=b.studentid group by b.studentid order by avgPrice;
多表查询--内联 inner join on
SELECT a.sname,avg(b.s_score) as avgPrice,sum(b.s_score) as sumPrice FROM student a inner join score b on a.studentid=b.studentid group by b.studentid order by avgPrice;