1.select 语句基本结构(注意顺序)
select 数据列表 [into]新表
[from 表(或视图、子查询)]
[where 查询条件]
[group by 分组表达式]
[having 查询条件]
[order by 排序条件[ASC | DESC]]
2. select 语句
获取简单的数据
select stuNO 学号,stuName 姓名,sex 性别 from T_Student
select * from T_Student
如果命名有空格的,需要把整个命名用方括号"[ ]"(有时可以用单引号)括起来
select stuNO [学 号],stuName '姓 名',sex '性;别 ' from T_Student
显示常量、函数、表达式(以九九乘法表为例)
--定义三个局部变量
declare @i int,@j int,@temp varchar(200)
--初始化值
set @i = 1
set @temp = ''
while @i <= 9 --使用while循环
begin
set @j = 1 --初始化j
while @j <= @i--循环遍历列数
begin
--CAST一种数据类型转换为另一种数据类型 CHAR(9)制表符
set @temp += CAST(@j as varchar(10)) + '*' + CAST(@i as varchar(10)) + '=' + CAST(@i*@j as varchar(10)) + CHAR(9)
set @j +=1;
end
print @temp --输出
set @temp = ''
set @i += 1
end
3.where 条件 语句
查询条件为年龄大于等于30的
select * from T_Test
where T_Test.age>=30
查询条件为年龄不是大于等于30的
select * from T_Test
where NOT(T_Test.age>=30)
查询条件为年龄不是不大于20的
select * from T_Test
where NOT(T_Test.age!>20)
查询成绩在70-90分之间的数据
select * from T_Test
where score between 70 and 90
查询成绩不在70-90分之间的数据
select * from T_Test
where score not between 70 and 90
查询无成绩的学生
select stuName
from T_Test
where score is null
精准查询
select stuNO,stuName,sex,age
from T_Test
where stuName in('刘备','孙尚香')
模糊查询(%表示所有,_表示单个字符)
select * from T_Test
or (stuName like '刘_') and (stuName like '刘__')
4.order by 排序 ASC 升序(默认)DESC 降序
查询成绩在70-90的并且根据成绩排序(升序)
select * from T_Test
where score between 70 and 90
order by score
姓名升序,成绩倒序
select * from T_Test
where score between 70 and 90
order by stuName ASC,score desc
猜猜这个和上面语句的结果一样吗?
select * from T_Test
where score between 70 and 90
order by score desc,stuName ASC
5.group by 字句 需要和COUNT(*)函数一起使用
计算性别人数(COUNT(*)集合函数,可写成COUNT(1)或COUNT(sex))
select sex as 性别, COUNT(1) as 人数
from T_Test
group by sex
order by 性别 DESC
查询男女平均成绩
select sex 性别,AVG(score) 平均成绩
from T_Test
group by sex
6.HAVING 字句
指定组或聚合的检索条件,功能与WHERE类似 ,HAVING 与 WHERE 的区别:WHERE 过滤行,HAVING过滤分组
过滤性别大于4的人数
select sex 性别,COUNT(*) 人数
from T_Test
group by sex
having COUNT(*) > 4 --having 要与COUNT(*)联系
7.使用TOP表达式(可以返回某一数量的行或者某一百分比的行)
显示表中前四行
select top 4 * from T_Test
显示男女各前30%的行
select top 30 percent * from T_Test
where sex = '男'
select top 30 percent * from T_Test
where sex = '女'
显示最后两行(并且为升序)
select top 3 * from
(select top 2 * from T_Test order by stuNo desc)a
order by stuNO
显示任意行数(2为需要显示的行数,5为选定的区域)
select top 2 * from
(select top 5 * from T_Test order by stuNO desc)b
order by stuNO
8.select into 子句 (可以动态创建新表)
创建表T_Tmp,把T_Test表的数据复制到新表中
select * into T_Tmp
from T_Test
选取表中部分数据创建新表
select stuName 姓名,birthday 年龄
into T_Tmp
from T_Test
select * from T_Tmp