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

sql课后作业1

--SQL语言作业：
--以学生-课程数据库中Student、SC、Course三个表为数据源，写相应的SQL命令。
--1、以Student表为数据源，查询4月份出生的学生信息（所有列）。
select month(getdate())
select * from student where month(birthday)=4  --birthday like '____-04%)
--2、以Student表为数据源，查询学号与年级不符的学生学号、姓名、专业和年级。其中学号的前4位为年级。
select * from student where left(sno,4)!=left(grade,4)
select sno,sname,specialty,grade  from student where substring(sno,1,4)!=substring(grade,1,4)
--3、以Student表为数据源，查询入学年龄少于等于18岁的学生学号、姓名和年龄。
select sno 学号,sname 姓名,year(getdate())-year(birthday) as 年龄 from student
  where year(en_time)-year(birthday)<=18

--4、以Student表为数据源，查询学生姓名以“玲”字结尾的学生学号、姓名及性别。
select sno 学号,sname 姓名,ssex 性别 from student where sname like '%玲'
select sno 学号,sname 姓名,ssex 性别 from student where sname like '__玲' or sname like '_玲'
--5、以Course表为数据源，若1个学分需要20学时，查询学分和学时不符的课程信息。
select * from course where  classhour!=credit*20
--6、以Course表为数据源，查询课程名为“数据结构”和“操作系统”的课程信息。分别用IN和OR实现。
select * from course where cname in('数据结构','操作系统')
--7、以SC为数据源，查询70分段的成绩信息。
select * from sc where score >=70 and score<80
select * from sc where score between 70 and 79

select * from sc order by score desc

多表查询

--多表查询
select * from student
select * from sc
--积
select * 
from student,sc
--内部连接
select * 
from student,sc
where student.sno=sc.sno 

select student.sno,sname,cno,score  
from student,sc
where student.sno=sc.sno 

select s.sno,sname,cno,score  
from student s,sc
where s.sno=sc.sno 

--三个表连接
select s.sno,sname,cname,score  
from student s,sc,course c
where s.sno=sc.sno and sc.cno=c.cno

--两个表连接inner join
select s.sno,sname,cno,score  
from student s inner join sc 
  on  s.sno=sc.sno 

--三个表连接inner join
select s.sno,sname,cname,score  
from (student s inner join sc 
  on  s.sno=sc.sno ) inner join course c
  on sc.cno=c.cno
 
 select * from student 

 --左外连接
 select s.sno,sname,cno,score  
from student s left outer join sc 
  on  s.sno=sc.sno 
  --右外连接
 select s.sno,sname,c.cno,score  
from (student s left outer join sc 
  on  s.sno=sc.sno ) right join course c
  on sc.cno=c.cno

--自身连接
select c1.cno,c1.cname,c2.cname as 先修课
from course c1,course c2
where c1.cpno=c2.cno

--查询同名
select * from student s1,student s2
where s1.sname=s2.sname and s1.sno<>s2.sno
--子查询 
  select sno,score 
  from 
  (select s.sno,sname,cname,score  
  from (student s inner join sc 
  on  s.sno=sc.sno ) inner join course c
  on sc.cno=c.cno) as t
  --查询比王一的平均分高的成绩信息
  
  select * from sc where score >
  (select avg(score) as aver  
  from student s inner join sc 
  on  s.sno=sc.sno 
   where sname ='王一')
   --比所有成绩都高的

     select * from sc where score >all
  (select score
  from student s inner join sc 
  on  s.sno=sc.sno 
   where sname ='王一')
  

  select * from sc where score >some
  (select score
  from student s inner join sc 
  on  s.sno=sc.sno 
   where sname ='王一')