--打开数据库
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 ='王一')