一,select语句的结构
select [distinct] select_list from table_name
[where search_condition]
[group by group_by_expression]
[having search_condition]
[order by order_expression [asc|desc]]
1.删除结果中相同的行时用关键字:distinct
语法:select column1,column2,... from table_name order by colunm n
order by 字句一定要放在所有字句的最后
select name,no,sal from student order by sal
默认是按照升序拍的,降序用关键字desc
eg: select name,number,sorc from student order sorc 3 desc,2
eg:select TNAME,AGE,TSEX from teacher where AGE between 30 and 50
5.运算符IN,AND,OR 可以进行与和或查询,也可以混可使用
eg:select name,sal,number from student where name='王菲' or sal='7000' order by sal
select name,number,sal,scor student name in('王菲','许戈辉','华仔',)
(not in)
6.like 运算符
7.函数和聚合函数
upper(bookname)转换成大写,lower(bookname)转换成小写。
rtrim(name)去空格
ABS,求和SUM,MAX,AVG等
count(*),计算表中行的总是,即使是表中的数据为NULL,也被计入在内。
count(column),计算column列包含的行的数目,如果该列某行为NULL,则不计算在内。
eg:查询年纪最大的学生的名字,性别,学号
select name,sex,number from student age=(select MAX(age) from teacher)
eg:查询所有年龄高于平均年龄的学生
select * from student where age>=(select AVG(age) from student) order by age
8.GROUP BY字句创建分组
语法: select column,SUM(column1.2.3)
from table group by column
GROUP BY 字句依据column列的数据进行分组的,比如说部门,学科,性别等来分组。
结果为:
结果为:
select [distinct] select_list from table_name
[where search_condition]
[group by group_by_expression]
[having search_condition]
[order by order_expression [asc|desc]]
1.删除结果中相同的行时用关键字:distinct
select distinct name,number,sal from stuent;
去除name,number,sal相同的记录
语法:select column1,column2,... from table_name order by colunm n
order by 字句一定要放在所有字句的最后
select name,no,sal from student order by sal
默认是按照升序拍的,降序用关键字desc
eg: select name,number,sorc from student order sorc 3 desc,2
意思是sorc按降序排,number按照升序排
eg:select TNAME,AGE,TSEX from teacher where AGE between 30 and 50
eg:select TNAME,AGE,TSEX,DANME from teacher where DANME between '计算机' and '生物'
5.运算符IN,AND,OR 可以进行与和或查询,也可以混可使用
eg:select name,sal,number from student where name='王菲' or sal='7000' order by sal
select name,number,sal,scor student name in('王菲','许戈辉','华仔',)
(not in)
6.like 运算符
% | 包含零个或多个任意字符串 | Like ‘%123%’ |
_ | 任何单个字符 | While 学号 ‘123_0 |
[] | 指定范围[a_f] | Like ‘0[25]%’,即021,051 |
[^] | 不在这个指定范围[a_f]内 | Like’0[26]%’,03,04,05 |
7.函数和聚合函数
upper(bookname)转换成大写,lower(bookname)转换成小写。
rtrim(name)去空格
ABS,求和SUM,MAX,AVG等
count(*),计算表中行的总是,即使是表中的数据为NULL,也被计入在内。
count(column),计算column列包含的行的数目,如果该列某行为NULL,则不计算在内。
eg:查询年纪最大的学生的名字,性别,学号
select name,sex,number from student age=(select MAX(age) from teacher)
eg:查询所有年龄高于平均年龄的学生
select * from student where age>=(select AVG(age) from student) order by age
8.GROUP BY字句创建分组
语法: select column,SUM(column1.2.3)
from table group by column
GROUP BY 字句依据column列的数据进行分组的,比如说部门,学科,性别等来分组。
eg: 如图person表中的数据
1.查出每个班级数学和英语成绩的平均分
select avg(math) as 'math_v', avg(english) as 'english_v' ,class from person group by class
结果为:
2.并查出数学平均成绩大于80,英语平均成绩大于90的班级
select avg(math) as 'math_v', avg(english) as 'english_v' ,class
from person group by class
having avg(math)>80 and avg(english)>90
结果为:
having 是对分组后的结果集再进行筛选,不能筛选到具体的记录,只能对组进行筛选。