一、基本概念(查询语句)
①基本语句
1、“select * from 表名;”,—可查询表中全部数据;
2、“select 字段名 from 表名;”,—可查询表中指定字段的数据;
3、“select distinct 字段名 from 表名;”,—可对表中数据进行去重查询。
4、“select 字段名 from 表名 where 查询条件;”,—可根据条件查询表中指定字段的数据;
②条件查询
1)比较运算符:>, <, >=, <=, =, !=, <>
查询大于18岁的信息
1 2 | select * from students where age>18; select id, name ,gender from students where age>18; |
查询小于18岁的信息
1 | select * from students where age<18; |
查询年龄为18岁的所有学生的名字
1 | select * from students where age=18; |
2)逻辑运算符:and, or, not
–18到28之间的学生信息
1 | select * from students where age>18_and age<28: |
–18岁以上的女性
1 2 | select * from students where age>18 and gender= "女" ; select * from students where age>18 and gender=2; |
–18以上或者身高查过180(包含)以上
1 | select * from students where age>18 or height>=180; |
不在18岁以上的女性这个范围内的信息
1 | select * from students where not (age>18 and gender=2); |
年龄不是小于或者等于18并且是女性
1 | select * from students where ( not age<=18) and gender=2; |
3)模糊查询:like, rlike
% 替换1个或者多个
_ 替换1个
查询姓名中 以“小”开始的名字
1 2 | select name from students where name = "小" ; select name from students where name like "小%" ; |
查询姓名中有“小”所有的名字
1 | select name from students whece name like "%小%" ; |
查询有2个字的名字
1 | select name from students where name like "__" ; |
查询有3个字的名字
1 | select name from students where name like "__" ; |
查询至少有2个字的名字 select name from
1 | students where name like "__%" ; |
rlike正则
查询以周开始的姓名
1 | select name from students where name rlike "^周.*" ; |
查询以周开始、伦结尾的姓名
1 | select name from students where name rlike "^周.*伦$" ; |
4)范围查询:in,not in,between…and,not between…and
查询年龄为18、34的姓名
1 2 | select name , age from students where age=18 or age=34; select name ,age from students where age in (18,34); |
not in不非连续的范围之内
年龄不是 18、34岁之间的信息
1 | select name ,age from students where age not in (18,34); |
between … and …表示在一个连续的范围内
查询年龄在18到34之间的的信息
1 | select name ,age from students where age between 18 and 34; |
not between … and …表示不在一个连续的范围内
查询年龄不在在18到34之间的的信息
1 | select * from students where age not between 18 and 34; |
空判断
判空 is null
查询身高为空的信息
1 | select * from students where height is null / NULL / Null ; |
判非空is not null
1 | select * from students where height is not null ; |
排序:order_by
–查询年龄在18到34岁之间的男性,按照年龄从小到大排序
1 2 3 | select * from students where (age between 18 and 34) and gender=1; select * from students where (age between 18 and 34) and gender=1 order by age; select * from students where (age between 18 and 34) and gender=1 order by age asc ; |
查询年龄在18到34岁之间的女性,身高从高到矮排序
1 | select * from students where (age between18 and 34) and gender=2 order by height desc ; |
order by多个字段
查询年龄在18到34岁之间的女性,身高从高到矮排序,如果身高相同的情况下按照年龄从小到大排序
1 | select * from students where (age between 18 and 34) and gender=2 order by height desc ,age asc ; |
查询年龄在18到34岁之间的女性,身高从高到矮排序,如果身高相同的情况下按照年龄从小到大排序,如果年龄也相同那么按照id从大到小排序
1 | select * from students where (age between 18 and 34) and gender=2 order by height desc ,age asc , id desc ; |
按照年龄从小到大、身高从高到矮的排序
1 | select * from students order by age asc ,height desc ; |
分组:group_by, group_concat():查询内容, having
where :是对整个数据表信息的判断;
having:是对于分组后的数据进行判断
–group by
按照性别分组,查询所有的性别
1 | select gender from students group by gender; |
–计算每种性别中的人数
1 | select gender, count (*) from students group by gender; |
where是在group by 前面
–计算男性的人数
1 | select count (*) from students where gender= '男' ; |
–group_concat(…)
查询同种性别中的姓名
1 | select gender,group_concat( name ) from students group by gender; |
having :having是在group by后面
查询平均年龄超过30岁的性别,以及姓名
1 | select gender , avg (age) from students group by gender having avg (age) > 30; |
查询每种性别中的人数多于2个的信息
1 | select gender, count (*) from students group by gender having count (*) > 2; |
– 查询每组性别的平均年龄
1 | select gender, avg (age) from students group by gender; |
分页: limit
1 2 3 | limit start, count (start:表示从哪─个开始; count :表示数量) 即limit(第N页-1)*每个的个数,每页的个数; limit在使用的时候,要放在最后面. |
限制查询出来的数据个数
1 | select * from students where gender=1 limit 2; |
查询前5个数据
1 | select * from students limit 0,5; |
查询id6-10(包含)的书序
1 | select * from students limit 5,5; |
每页显示2个,第1个页面
1 | select * from students limit 0,2; |
每页显示2个,第2个页面
1 | select * from students limit 2,2; |
每页显示2个,第3个页面
1 | select * from students limit 4,2; |
每页显示2个,第4个页面
1 | select * from students limit 6,2; |
每页显示2个,显示第6页的信息,按照年龄从小到大排序
1 | select * from students order by age asc limit 10,2; |
– 如果重新排序了,那么会显示第一页
1 | select * from students where gender=2 order by height des limit 0,2; |
5)聚合函数:count(), max(), min(), sum(), avg(), round()
聚合函数
-总数-- count
-查询男性有多少人,女性有多少人
1 2 3 | select count (*) from students where gender=1; select count (*) as 男性人数 from students where gender=1; select count (*) as 女性人数 from students where gender=2; |
-最大值-最小值
– max --min
一查询最大的年龄
1 | select max (age) from students; |
–查询女性的最高身高
1 | select max (height) from students where gender=2; |
-求和
–sum
-计算所有人的年龄总和
1 | select sum ( age) from students; |
–平均值
– avg
–计算平均年龄
1 | select avg (age) from students; |
–计算平均年龄
1 | select sum ( age) / count (* ) from students; |
–四舍五入round ( 123.23 ,_1)保留1位小数
–计算所有人的平均年龄,保留2位小数
1 | select round ( sum (age)/ count (*),2) from students; select round ( sum (age)/ count (*),3) from students ; |
–计算男性的平均身高保留2位小数
1 | select round( avg (height),2) from students where gender=1; select name ,round( avg (height),2) from students where gender=1; |
6)连接查询 :inner join, left join, right join
inner join
select … from 表 A inner join表B;
1 | select * from students inner join classes; |
查询有能够对应班级的学生以及班级信息
1 | select * from students inner join classes on students.cls_id=classes.id; |
按照要求显示姓名、班级
1 2 | select students.*, classes. name from students inner join classes on students.cls_id=classes.id; select students. name ,classes. name from students inner join classes on students.cls_id=classes.id; |
给数据表起名字
1 | select s. name ,c. name from students as s inner join classes as c on s.cls_id=c.id; |
查询有能够对应班级的学生以及班级信息,显示学生的所有信息,只显示班级名称
1 | select s.*,c. name from students as s inner join classes as c on s.cls_id=c.id; |
在以上的查询中,将班级姓名显示在第1列
1 | select c. name ,s.* from students as s inner join classes as c on s.cls_id=c.id; |
查询有能够对应班级的学生以及班级信息,按照班级进行排序
select c.xxx s.xxx from student as s inner join clssses as c on … order by …;
1 | select c. name ,s.* from students as s inner join classes as c on s.cls_id=c.id order by c. name ; |
当时同一个班级的时候,按照学生的id进行从小到大排序
1 | select c. name ,s.* from students as s inner join classes as c on s.cls_id=c.id order by c. name ,s.id; |
left join
查询每位学生对应的班级信息
1 | select * from students as s left join classes as c on s.cls_id=c.id; |
查询没有对应班级信息的学生
– select … from xxx as s left join xxx as c on… where …
– select … from xxx as s left join xxx as c on… . … having …
1 2 | select * from students as s left join classes as c on s.cls_id=c.id having c.id is null ; select * from students as s left join classes as c on s.cls_id=c.id where c.id is null ; |
left join是按照左边的表为基准和右边的表进行查询,查到就显示,查不到就显示为null
补充
查询所有字段:select * from 表名;
查询指定字段:select 列1,列2,... from 表名;
使用 as 给字段起别名: select 字段 as 名字.... from 表名;
查询某个表的某个字段:select 表名.字段 .... from表名;
可以通过 as 给表起别名: select 别名.字段 .... from 表名 as 别名;
消除重复行: distinct 字段
注意:WHERE子句中是不能用聚集函数作为条件表达式的!
二、总结
1、普通查询
(1)命令:select * from <表名>;
(2)命令:select <要查询的字段> from <表名>;
2、去重查询(distinct)
命令:select distinct <要查询的字段> from <表名>
3、排序查询(order by)
升序:asc
降序:desc
降序排列命令:select <要查询的字段名> from <表名> order by <要查询的字段名> desc
不加desc一般默认为升序排列
4、分组查询(group by)
命令:select <按什么分的组>, Sum(score) from <表名> group by <按什么分的组>
假设现在又有一个学生成绩表(result)。要求查询一个学生的总成绩。我们根据学号将他们分为了不同的组。
命令:
1 2 3 | select id, Sum (score) from result group by id; |
现在有两个表学生表(stu)和成绩表(result)。
5.等值查询
当连接运算符为“=”时,为等值连接查询。
现在要查询年龄小于20岁学生的不及格成绩。
1 2 3 | select stu.id,score from stu,result where stu.id = result.id and age < 20 and score < 60; |
等值查询效率太低
6.外连接查询
①语法
1 2 3 | select f1,f2,f3,.... from table1 left / right outer join table2 on 条件; |
②左外连接查询,例如
1 2 3 4 5 6 | select a.id,score from ( select id,age from stu where age < 20) a (过滤左表信息) left join ( select id, score from result where score < 60) b (过滤右表信息) on a.id = b.id; |
左外连接就是左表过滤的结果必须全部存在。如果存在左表中过滤出来的数据,右表没有匹配上,这样的话右表就会出现NULL;
③右外连接查询,例如
1 2 3 4 5 6 | select a.id,score from ( select id,age from stu where age < 20) a (过滤左表信息) right join ( select id, score from result where score < 60) b (过滤右表信息) on a.id = b.id; |
右外连接就是左表过滤的结果必须全部存在
7.内连接查询
①语法
1 2 3 | select f1,f2,f3,.... from table1 inter join table2 on 条件; |
②例如
1 2 3 4 5 6 | select a.id,score from ( select id,age from stu where age < 20) a (过滤左表信息) inner join ( select id, score from result where score < 60) b (过滤右表信息) on a.id = b.id; |
8.合并查询
在图书表(t_book)和图书类别表(t_bookType)中
①.union
使用union关键字是,数据库系统会将所有的查询结果合并到一起,然后去掉相同的记录;
1 2 3 4 5 | select id from t_book union select id from t_bookType; |
②.union all
使用union all,不会去除掉重复的记录;
1 2 3 4 5 | select id from t_book union all select id from t_bookType; |