1.查询所有列数据 | SELECT * FROM studen | |
2.查询指定列 | SELECT id,NAME,gender FROM student; | |
3.查询时指定别名(as) | SELECT id AS '编号',NAME AS '姓名' FROM student as s; | |
4.查询时添加常量列 | SELECT id,NAME,gender,age,'二年级' AS '年级' FROM student; | |
5.查询时合并列 | SELECT id,NAME,(servlet+jsp) AS '总成绩' FROM student; 注意:合并列只能合并数值类型的字段 | |
6.查询时去除重复记录DISTINCT | SELECT DISTINCT area FROM student; SELECT DISTINCT(area) FROM student; | |
7.条件查询 | 1.逻辑条件: and(与) or(或) SELECT * FROM student WHERE id=2 AND NAME='李四';--查询id为2,且姓名为李四的学生 SELECT * FROM student WHERE id=2 OR NAME='张三';--查询id为2,或姓名为张三的学生
2.比较条件: > < >= <= = <>(不等于) between and (等价于>= 且 <=) 包前包后 SELECT * FROM student WHERE jsp>=75 AND jsp<=90;查询jsp成绩大于等于75,且小于等于90分的学生 SELECT * FROM student WHERE jsp BETWEEN 75 AND 90; SELECT * FROM student WHERE gender<>'男';
3.判空条件(null 空字符串): is null / is not null / ='' / <>'' null:表示没有值 空字符串:有值的! SELECT * FROM student WHERE address IS NULL --判断null SELECT * FROM student WHERE address=''; 判断空字符串 SELECT * FROM student WHERE address IS NULL OR address=''; -- (包括null和空字符串) SELECT * FROM student WHERE address IS NOT NULL AND address<>'';不为空的,包括不是null 也不是"
4.模糊条件: like % : 表示任意个字符 _ : 表示一个字符 SELECT * FROM student WHERE NAME LIKE '李%';查询姓‘李’的学生 SELECT * FROM student WHERE NAME LIKE '李_';-查询姓‘李’,且姓名只有两个字的学生 | |
8.聚合查询使(使用聚合函数的查询) | sum() avg() max() min() count() SELECT SUM(servlet) AS 'servlet的总成绩' FROM student;查询学生的servlet的总成绩 (sum() :求和函数) SELECT AVG(servlet) AS 'servlet的平均分' FROM student;查询学生的servlet的平均分 SELECT MAX(servlet) AS '最高分' FROM student;查询当前servlet最高分 SELECT MIN(servlet) AS '最低分' FROM student;查询最低分 SELECT COUNT(*) FROM student; 注意:count()函数统计的数量不包含null的数据 使用count统计表的记录数,要使用不包含null值的字段 | |
9.分页查询(limit 起始行,查询几行) | limit 起始行,查询几行--最后一页不够行数,就有几行就显示几行 从第几行开始=(当前页-1)*每页显示多少条 SELECT * FROM student LIMIT (当前页-1)*每页显示多少条,每页显示多少条; SELECT * FROM student LIMIT 0,2;第1页的数据 SELECT * FROM student LIMIT 2,2;第2页的数据 SELECT * FROM student LIMIT 4,2;第3页的数据 | |
10.查询排序 | 查询排序(order by ) 语法 :order by 字段 asc/desc asc: 顺序,正序。数值:递增,字母:自然顺序(a-z) desc: 倒序,反序。数值:递减,字母:自然反序(z-a)
SELECT * FROM student ;默认情况下,按照插入记录顺序排序 SELECT * FROM student ORDER BY id ASC;正序 SELECT * FROM student ORDER BY id DESC;-- 反序
多个条件排序 SELECT * FROM student ORDER BY servlet ASC,jsp DESC;按照servlet正序,按照jsp的倒序 | |
11.分组查询(group by) | 1)把学生按照性别分组(GROUP BY gender) 2) 统计每组的人数(COUNT(*)) SELECT gender,COUNT(*) FROM student GROUP BY gender; | |
12.分组查询后筛选 | 分组之前条件使用where关键字,分组之前条件使用having关键字 1) 查询男女的人数 2)筛选出人数大于2的记录(having) SELECT gender,COUNT(*) FROM student WHERE jsp>=75 GROUP BY gender HAVING COUNT(*)>2; |