查看列

查看特定列

select 姓名 from 学生表

select 姓名,性别,家庭住址 from 学生表

查看所有列

select * from 学生表

使用计算列

求和

select sum(price) from fruit

求个数

select count(price) from fruit

求价格提升

select price*1.05 from fruit

求库存数量

select (进货数量-销售数量）as 库存数量 from fruit

使用distinct

作用于单列

select distinct 班级 from student

作用于多列

select distinct 班级,姓名 from student
#distinct 作用覆盖班级+姓名，是根据班级+姓名两个字段同时去重。并且distinct必须放在开头

使用where字句

select 列名称 from 表名称 where 条件表达式

关系运算符

select * from sc where 成绩>60

select 姓名,性别,班级 from student where left(姓名,1)='赵'

select 姓名,性别,班级 from student where year(出生日期)>1990

select sname from student where sdept !='CS'

逻辑关系符

select * from sc where not 成绩>=60

select * from student where 性别='男' and 班级='4'

select * from course where 考试类型='机考' or 学分=3

范围运算符

select name,dept,age from student where age between 20 and 23

select name,dept,age from student where age not between 20 and 23

列表运算符

select name,sex from student where dept in('IS','MA','CS')

select name,sex from student where dept not in('IS','MA','CS')

模糊匹配运算符

【not】like + ‘%’：表示0或多个字符

select name,no,sex from student where name like ‘刘%’

select name,no,sex from student where name not like ‘刘%’

select name,no,sex from student where name  like ‘%刚’

select name,no,sex from student where name  like ‘%小%’

【not】like + ‘_’：表示单个字符，一般用来限制字符长度

select name,no,sex from student where name  like ‘_小刚’
#以小刚结尾的三个字学生

select name,no,sex from student where name  like ‘_阳%’
#第二个字为阳的学生

【not】like + [ ]：表示范围内的单个字符

select * from user where name like '[张王李]三'
#找出所有叫张三李三王三的人的信息

select * from user where name like '老[1-9]'
#找出老1 老2 老3.....老9
#如果[]内有一系列字符(01234，abcdef)可以略写为[0-4],[a-f]

select * from persons where city like '[ALN]%'
#找出居住城市是以A 或者L或者 N开头的人的信息

【not】like + [^ ]：表示不在范围内的单个字符

select * from user where name like '[^张王李]三'
#找出不叫张三,王三，李三的使用者信息

select * from user where name like '老[^0-4]'
#找出不在老1-老4的使用者信息

空值运算符：is null / is not null

select sno,sco from student where grade is null
#查找缺少成绩学生的学号以及课程号

select sno,sco from student where grade is not null
#查找有成绩学生的学号以及课程号

多重条件查询

1.and优先级高于or
2.可以用括号改变优先级

select name from student where dept='CS' and age<20

select name,sex from student where dept='IS' or dept='CS' or dept='MA'

使用order by排序

对单个字段排序

asc（可省略）,desc

select no,grade from sc where cno='3' order by grade desc

select * from student order by 出生日期 desc

对两个字段同时排序

select * from sc order by 学号,成绩 desc
#查询学生信息，学号升序排列，学号相同按照成绩降序排列

对空值字段进行排序

使用TOP关键字

返回确定的记录数目

select top 20 name from student

select top 20 * from student

返回指定百分比的记录数

select top 10 percent 姓名,出生日期 from student

select top 10 percent * from student 

top关键字结合where语句

select top 20 * from student where age>23

top关键字结合order by语句

select top 20 * from student order by age desc

select top 10 title,adddatetime from news order by adddatetime desc

select top 8 title,hits from news order by hits desc