[大数据]SQL-查询语句

--打开数据库
use student
go

--查询所有列
Select *
From student 

--查询部分列
select sno,sname,ssex,birthday
from student 

select grade,sno,sname,ssex,birthday
from student 

--给列取个别名
select sno as 学号,sname 姓名,性别=ssex,birthday
from student 

--获得当前时间
select getdate()
select year(getdate())


--计算列
select sno as 学号,sname 姓名,性别=ssex,年龄=year(getdate())-year(birthday)
from student 

select A=3+7
from student

--去掉重复的行
select all  specialty  --所有的行
from student 

select distinct  specialty  --去掉重复的行
from student 

--取前多少记录
select top 3 *
from student

select top 3 * --取前三名
from SC
order by score desc

select top 3 with ties * --取前三名
from SC
order by score desc

select top 30 percent * --取前30%
from SC
order by score desc

--比较运算符
select * from student where grade='2020级'

select * from student where grade='2020级' and ssex='女'

select * from sc where score>=80

--区间运算符Between And

select * from student where birthday between '2001-1-1' and  '2001-12-31'

--等价于

select * from student where birthday>='2001-1-1' and  birthday<='2001-12-31'

select * from student where not birthday between '2001-1-1' and  '2001-12-31'
--等价于
select * from student where birthday<'2001-1-1' or  birthday>'2001-12-31'

--查询成绩80分段

select * from sc where score between 80 and 89
--等价于
select * from sc where score>=80 and score<=89

--集合运算符IN (  )
--查询计算机和网络安全专业   ('计算机','网络安全')
select * from student where specialty in('计算机','网络安全')
--等价于
select * from student where specialty ='计算机' or specialty ='网络安全'

--查询非计算机和非网络安全专业  not in  ('计算机','网络安全')
select * from student where specialty not  in('计算机','网络安全')
--等价于
select * from student where specialty <>'计算机' and specialty <>'网络安全'

--模糊运算符 Like '% _' %任意字符，_单个字符，[]单个指定的字符 
--查询所有姓张的
select * from student where sname like '张%'

--查询 第二字是玲 
select * from student where sname like '_玲%'
--查询 含玲 
select * from student where sname like '%玲%'

--查询所有姓张和王的
select * from student where sname like '[张王]%'
--查询所有不是姓张和王的
select * from student where sname like '[^张王]%'

--查询姓名中含有_下划线
select * from student where sname like '%\_%' escape '\'

--空值NULL 无意义
--查询暂无成绩的记录
select * from Sc where score is null
--有成绩
select * from Sc where score is not null