SQLServer基础语句使用

--设置当前数据为master数据库，创建数据时要往系统数据库master中写入信息
use master
go
--判断数据库student是否存在，如果存在就先删除它
if exists(select * from sysdatabases where name='student')
drop database student
go

--创建数据库  日志文件、数据文件
create database student --其实最简单，就只写这一句就能创建成功了！
on primary
(
name='student',
filename='D:\student.mdf',
size=3MB,
filegrowth=10%
)
log on
(
name='student_log',
filename='D:\student.ldf',
size=1MB,
filegrowth=10%
)
go

--创建学生信息表
use student --创建表之前一定先设置当前数据库
go
--创建之前先判断表是否存在，如果存在就先删除它
if exists(select * from sysobjects where name='stuInfo')
drop table stuInfo
go
create table stuInfo
(
stuID int not null,--学号
stuName char(10) not null,--姓名
stuSex char(2) not null,--性别
stuBirth datetime not null--出生日期
)
go



--创建约束
alter table stuinfo
add constraint PK_stuid primary key(stuid)

alter table stuinfo
add constraint CK_stusex check(stusex='男' or stusex='女')

alter table stuinfo
add constraint DK_stusex default('男') for stusex
go

--插入数据
delete from stuInfo
insert into stuInfo(stuID,stuName,stuSex,stuBirth)
select 1101,'张三','男','1989-2-28' union
select 1102,'李四','女','1990-3-8'  union
select 1103,'王五','女','1997-9-10'
go


--创建课程信息表
use student
go
if exists(select * from sysobjects where name='courseInfo')
drop table courseInfo
go
create table courseInfo
(
couID int not null,--课程号
couName char(20) not null,--课程名称
couScore int not null--学分
)
go

--创建约束
alter table courseInfo
add constraint PK_couID primary key(couID)

alter table courseInfo
add constraint DK_couScore default(1) for couScore

alter table courseInfo
add constraint CK_couScore check(couScore>=1 and couScore<=5)
go

--插入数据
delete from courseInfo
insert into courseInfo(couID,couName,couScore)
select 2201,'计算机基础',3 union
select 2202,'html网页设计',4 union
select 2203,'java',5


--创建学生成绩表
use student
go
if exists(select * from sysobjects where name='scoreInfo')
drop table scoreInfo
go
create table scoreInfo
(
ExamNo int not null,--考号
stuid int not null,--学号
couID int not null,--课程号
score int not null--分数
)
go

--创建约束
alter table scoreInfo
add constraint PK_examNo primary key(examNo,couid)

alter table scoreInfo
add constraint DK_score default(0) for score

alter table scoreInfo
add constraint CK_score check(score>=0 and score<=100)

alter table scoreInfo
add constraint FK_stuid foreign key(stuid) references stuinfo(stuid)

alter table scoreInfo
add constraint FK_couid foreign key(couid) references courseInfo(couid)
go



--插入数据
delete from scoreInfo
insert into scoreInfo(ExamNo,stuid,couID,score)
select 3301,1101,2201,100 union
select 3301,1101,2202,90 union
select 3301,1101,2203,80 union

select 3302,1102,2201,100 union
select 3302,1102,2202,90 union
select 3302,1102,2203,50 union

select 3303,1103,2201,100 union
select 3303,1103,2202,70 
go


--插入表模式匹配符练习表 likeTable
if exists(select * from sysobjects where name='likeTable')
drop table likeTable
go

create table likeTable
(
id int identity(8801,1),
name char(20) 
)
go
--向likeTable中插入数据
insert into likeTable(name)
select 'McBadden' union
select 'Ringer' union
select 'Stinger' union
select 'Bennet' union
select 'Green' union
select 'Chery1' union
select 'Shery1' union
select 'Carsen' union
select 'KarSen' union
select 'Carson' union
select 'MacFeather'

-----------------------------第一部分单表查询----------------------------------
--1.查询数据“性别-学员姓名”
select stusex+'-'+stuname as '性别-学员姓名'
from stuinfo
--2.分别取出stuinfo表中的前4条记录和前40%的记录
select top 4 * from stuinfo
select top 40 percent * from stuinfo
--3.查询stuinfo表中所有男生的信息
select * from stuinfo where stusex='男' 
--4.查询stuinfo表除‘王五’以外的所有学员信息
select * from stuinfo where stuname <>'王五'
--5.查询stuinfo表中，学号在1102~1103之间的学员信息(between ... and...)
select * from stuinfo where stuid between 1102 and 1103
--6.查询stuinfo表中，学号不在1102~1103之间的学员信息(between ... and...)
select * from stuinfo where stuid not between 1102 and 1103
--7.查询stuinfo表中，学号为1101，1102，1103的学员信息(in)
select * from stuinfo where stuid in (1101,1102,1103)
--8.查询stuinfo表中，学号不为1101，1102，1103的学号信息(not in)
select * from stuinfo where stuid not in (1101,1102,1103)
--9.查询scoreInfo表中，学号为1101的学员的计算机基础成绩
select * from scoreInfo where stuid=1101 and couid='2201'
--10.使用逻辑运算符（not and or）查询stuinfo表中，学为1101,1102,1103 的学员信息
select * from stuinfo
where stuid=1101 
or stuid=1102
or stuid=1103
--11.搜索likeTable表中name列以字母Mc开头的所有字符串
select * from liketable where name like 'mc%'
--12.搜索likeTable表中name列以字母inger结尾的的所有字符串
select * from liketable where name like '%inger'
--13.搜索likeTable表中name列任意位置包含字母en的所有字符串
select * from liketable where name like '%en%'
--14.搜索likeTable表中name列以字母hery1的结尾的所有6个字母的字符串
select * from liketable where name like '_hery1'
--15.搜索likeTable表中name列第一个字母是C或K,接下来是ars,第五个字母是e或o,第6个字母是n单词
select * from liketable where name like '[ck]ars[eo]n'
--16.搜索likeTable表中name列以字母inger结尾，以M~Z中的任何单个字母开头的所有字符串
select * from liketable where name like '[m-z]%inger'
--17.搜索likeTable表中name列以字母M开头，并且第二个字母不是c的所有字符串
select * from liketable where name like 'm[^c]%' 
--18.查询scoreInfo表中‘计算机基础’成绩，并按照降序排列
select * from scoreinfo where couid='2201'
order by score desc 
--19.查询scoreInfo表中‘计算机基础’成绩，并按照降序排列，成绩相同的按照学号的降序排列
select * from scoreinfo where couid='2201'
order by score desc, stuId desc
--20.查询scoreInfo表中java成绩前三名的数据
select top 3 * from scoreinfo
where couid='2203'
order by score desc
--21.查询计算机基础考试成绩的总和
select sum(score) as 计算机成绩总和 from scoreinfo where couid='2201'
--22.查询每个同学计算机基础和java成绩的和
select stuId as 学号,sum(score) as 成绩和 from scoreinfo group by stuId
--23.查询java考试成绩的平均分
select avg(score) as java平均分 from scoreinfo where couid='2203'
--24.查询每个同学计算机基础和java成绩的平均分
select stuid as 学号,avg(score) as 平均分 from scoreinfo group by stuid
--25.查询scoreInfo表中，所有成绩中，最高分和最低分
select max(score)as 最高分, min(score) as 最低分
from scoreinfo
--26.查询scoreInfo表中，计算机基础成绩的最高分
select max(score) as 最高分 from scoreinfo where couid='2201'
--27.查询scoreInfo表中保存了多少个科目的考试成绩
select count(distinct couid) 科目总数 from scoreinfo
--28.分别统计参加每个科目考试的人数
select couid as 科目, count(*) as 人数
from scoreinfo group by couid
--29.显示平均分大于75分的科目名称
select couid as 科目, avg(score) as 平均分
from scoreinfo group by couid
having avg(score)>75
--30.显示计算机基础和java的和大于160分的同学学号
select stuid as 同学学号, sum(score) as 总和
from scoreinfo
where couid=2201 or couid=2203 
group by stuid
having sum(score)>160


-----------------------------第二部分多表查询----------------------------------
--31.查询scoreInfo表中考试科目名称带有字符"算"的科目名称，并消除重复数据
select distinct couid from scoreinfo
where couid like '%算%'
--32,显示学员信息与其对应的学员成绩（内联接）
select * from stuinfo inner join scoreinfo
on stuinfo.stuid=scoreinfo.stuid
--34.查询考试分数大于或等于85分的学员信息并显示相关科目和分数（内联接，给表加别名）
select s1.stuid,stuname,stusex,stubirth
from stuinfo s1 inner join scoreinfo s2
on s1.stuid=s2.stuid
where s2.score>=85
--35.左外联接，查询成绩，会显示没有参加考试的人的名字
select s1.stuid,stuname,stusex,stubirth
from stuinfo s1 left outer join scoreinfo s2
on s1.stuid=s2.stuid
--37.对stuinfo,courseInfo,scoreInfo三个表 进行交叉联连（cross join）
select count(*) stuinfo记录 from stuinfo
select count(*) courseinfo记录 from courseinfo
select count(*) scoreinfo记录 from scoreinfo

select count(*) 交叉连接记录 from stuinfo cross join courseinfo cross join scoreinfo
--38.查询scoreInfo表中分数大于或等于90分 和 scoreInfo表中stuid等于1101的记录（去掉重复的）
select examno,stuid,couid,score from scoreinfo
where score>=90
union
select examno,stuid,couid,score from scoreinfo
where stuid=1101
--39.查询scoreInfo表中分数大于或等于90分 和 stuid等于1101的两个结果集中都有的记录 （要求使用intersect）
select examno,stuid,couid,score from scoreinfo
where score>=90
intersect
select examno,stuid,couid,score from scoreinfo
where stuid=1101
--40.查询scoreInfo表中查询大于或等于90分 但是学号不1101记录 （要求使用except）
select examno,stuid,couid,score from scoreinfo
where score>=90
except
select examno,stuid,couid,score from scoreinfo
where stuid=1101


-----------------------------第三部分日期、字符串函数----------------------------------

--41.显示所有同学的生日(取出生日期中的月日,格式为:月-日)
select *,right(convert(char(10),getdate(),120),5) as 生日 from stuinfo

--42.显示所有同学的年龄

select *,datediff(yy,stubirth,getdate(),5) as 年龄 from stuinfo

--43.显示今天过生日的同学的学号，姓名，性别，生日，年龄
select stuid as 学号,stuname as 姓名,stusex as 性别,
right(convert(char(10),getdate(),120),5) as 生日,
datediff(yy,stubirth,getdate()) 年龄
from stuinfo 
where right(convert(char(10),stuBirth,120),5)=right(convert(char(10),getdate(),120),5)