sql server--summary[1]

create table student
(
 sno char(9) primary key,--列级完整性约束，sno是主码
 sname char(30) unique,--唯一
 ssex char(2),
 sage int,
 sdept char(20)
)
create table course
(
 cno char(4)primary key,--列级完整性，cno是主码
 cname char(40) not null,--列级完整性
 cpno char(4),
 ccredit int,
 foreign key (cpno) references course(cno)--表级完整性约束条件，cpno是外码，被参照表是course,被参照列是cno

)
create table sc
(
 sno char(9),
 cno char(4),
 grade int,
 primary key(sno,cno),--主码有两个属性构成，必须作为表级完整性进行定义
 foreign key(sno) references student(sno),--表级完整性约束条件，sno是外码，被参照表是student
 foreign key(cno) references course(cno)--表级完整性约束条件，cno是外码，被参照表是course
)

alter table student
add email varchar(30) check(email like '%_@%_')


--drop table student cascade
/*
select [*|dictinct] <查的属性>
from 表名，---
where 条件
group by 列名
having 条件表达式
order by 列名2 [asc|dexc]
*/

--1.查询所有学生的姓名，学号
select sno,sname
from student

--2.查询全体学生的姓名，学号和所在系
select sname,sno,sdept
from student

--3.查询全体学生所有信息
select *
from student
/*
就等于 select sno,sname,ssex,sage,sdept from student
*/
--4.查询经过计算的值
select sname,2020-sage
from student 
--或者
select sname,DATENAME(year,GETDATE())-sage 出生年月
from student

--5.还可以是字符窜，函数

--消除重复的行
select distinct sno
from student
--6.查询所有信息学院的学生消息
select *
from student
where sdept='xxxy'


--(1)比较大小
--查询年龄在20岁以下的学生姓名和年龄
select sname,sage
from student
where aage<20

--查询考试不及格
select sno
from sc
where grade<60


---（2）确定范围 between and /not between and
--1.查询年龄在20-23岁的姓名，年龄，系别
select sname,sage,sdept
from student
where sgae between 20 and 23
--不再20-23: where sage not between 20 and 23

--(3)确定集合 in,not in
--1.查找信息学院，外国语，体育学院的学生
select *
from student
where sdept in('xxxy','wgyxy','tyxy')
--不是在这三个学院则where sdept not in('xxxy','wgyxy','tyxy')

--(4)字符匹配 [not]like '<匹配窜>'[escape'<换码字符>']
--匹配符:% 和  _
--%（百分号）代表任意长度（长度可以为0）的字符窜，列如a%b表示以a开头以b结尾的任意长度的字符窜如acb,addgb,ab都满足
--_(下横线)代表任意单个字符，例如a_b代表以a开头以b结尾的长度为3的任意字符窜，如acb,afb都满足
--1.查找学号为'18446221'的学生
select *
from student
where sno like'18446221'--或者写成sno='18446221'

--2.查询所有姓刘的学生的姓名，学号和性别
select sname,sno,sage
from student
where sname like '刘%'

--3.查询名字中姓‘欧阳’且为3个汉字的学生的姓名
select sname
from student
where sname like '欧阳_'

--4.查询名字第二个字为‘宇’的学生姓名和学号
select sname,sno
from student
where sname like '_宇%'

--5.查询所有不姓‘秦’的学生，学号和姓名
select sno,sname
from student
where sname not like '秦%'

--如果要查询的字符窜含有通配符%和_，则需要 escape<换码字符>来进行转义
--6.查询db_design课程的课程号和学分
select cno,ccredit
from course
where cname like 'db\_design' escape'\'

--7.查询以‘db_’开头,且倒数第三个字符为i的课程的详细信息
select *
from student
where cname like 'db\_%i_ _' escape'\'



--(5)涉及空值的查询:is null/is not null 
--1.查找缺少成绩的学生的学号和对应的课程号
select sno,cno
from sc
where grade is null

--2查找所有有成绩的学生的学号和对应的课程号
select sno,cno
from sc
where grade is not null


--(6)多重条件查询 and/or and优先级高于or
--1.查询计算机系年龄在20岁以下的学生姓名
select sname
from student
where sdept='计算机系' and sage<20

2,接上order by

--order by字句
--用户可以用order by字句对查询结果按照一个或多个属性
--的asc(升序)或desc(降序)，默认为升序
--1.查询选修了3号课程的学生的学号以及成绩，
--查询结果按分数的降序排列
select sno,grade
from sc
where cno='3'
order by grade desc

--2.查询全体学生情况，
--查询结果按所在系的系号升序排列，
--同一系中的学生按年龄降序排列
select *
from student
order by sdept asc,sage desc

3.聚集函数

--聚集函数
/*
count(*) 统计元组的个数
count(<列名>)统计一列中值的个数
sum(<列名>)计算一列值的总和
avg(<列名>)计算一列值的平均值
max(<列名>)计算一列值的最大值
min(<列名>)计算一列值的最小值
 */
 --1.查询学生总人数
 select count(*)
 from student
 
 --2.查询选修了课程的总人数
 select count(distinct sno)
 from sc

 --3.计算选修1号课程的学生平均成绩
 select avg(grade)
 from sc
 where cno='1'

 --4.查询选修1号课程的学生最高分数
 select max(grade)
 from student 
 where cno='1'

 --5.查询学生’18446221’选修课程的总学分
 select sum(ccredit)
 from course,sc
 where course.cno=sc.cno and sno='18446221'
   

4.group by 字句

 --group by字句
   --group by字句将查询结果按某一列或多列的值分组，值相等的
   一组。
   --对查询结果分组的目的是为了细化聚集函数的作用对象，
   如果未对查询结果分组，聚集函数将作用于整个
   --查询结果，分组后聚集函数会作用于每一组，
   每一组都有一个函数值

   --1.求各个课程及其对应的选课人数
   select cno,count(sno)
   from sc
   group by cno

   --2.查询了选修三门以上课程的学生学号
   select sno
   from sc
   group by sno
   having count(*)>3 --having作用于组，从中选择组的条件，
   只要满足元组个数>3，这个组才会被选

   --3.查询平均成绩大于或等于90分的学生学号和平均成绩
   select sno,avg(grade)
   from sc
   group by sno
   having avg(grade)>=90

5.连接

 --(1)等值连接：
 where [<表名1>.]<列名1><比较运算符> [<表名2>.]<列名2>
   --1.查询每个学生及其选修课程的情况
   select student.*,sc.*
   from student,sc
   where student.sno=sc.sno

   --(2)自然连接：把目标中重复的属性列去掉
   select student.sno,sname,ssex,sage,sdept,cno,grade
   from student,sc
   where student.sno=sc.sno


--1.查询选修了2号课程且成绩在90分以上的所有学生的学号和姓名
select student.sno,sname
from student,sc
where student.sno=sc.sno and cno>2 and grade>90

--自身连接：一个表与其自身进行连接
--1.查询每一门课的间接选修课
--给course表取2个别名，一个是first，一个是second
select first.cno,second.cpno
from course first,course second
where first.cpno=second.cno

--(3)外连接
--若某个学生没有选课，连接后仍要吧student的悬浮元祖保存在结果关系中，而在sc的属性为NULL，
select student.sno,sname,ssex,sage,sdept,cno,grade 
from student left outer join sc on(student.sno=sc.sno)  --左外连接

--（4）多表连接
--1.3个表，查询每个学生的学号，姓名，选修的课程名和成绩
select student.sno,sname,cname,grade
from student,sc,course
where student.sno=sc.sno and sc.cno=course.cno

嵌套查询

--嵌套查询
select sname
from student
where sno in
(
 select sno
 from sc
 where cno='2'
)
--1.查询与‘秦宇欣’在同一个系的学生
select sno,sname,sdept
from student
where sdept in
(
 select sdept
 from student
 where sname='秦宇欣'
(

--或者用自身连接
select s1.sno,s1.sname,s1.sdept
from student s1,student s2
where s1.sdept=s3.sdept and s2.sname='秦宇欣'

--2,查询选修了课程名为‘database’的学生学号和姓名
select sno,sname
from student
where sno in
(
 select sno
 from sc
 where cno in
 (
  select cno
  from course
  where cname='database'
 )
)
--或用连接查询
select student.sno,sname
from student,sc,course
where student.sno=sc.sno and sc.cno=course.cno and course.cname='database'

数据更新

--1.插入数据
insert into <表名>(<属性列>--) values(--)
--将一个新学生元组插入student表
insert into student values('18446221','秦宇欣',‘男’,'18','xxxy')

--2.插入子查询
insert into <表名>[<属性列>---]
子查询
--对于每一个系，求学生的平均成绩，并把结果存入数据库
create table dept_age
(sdept char(15)
  age int
)
insert into dept_age(sdept,age)
select sdept,avg(sage)
from student
group by sdept



修改数据
update <表名>
set <列名>=<表达式>---
[where<条件>]
1.将学生18446221的年龄改为22岁
update student
set sage=22
where sno='18446221'
2.将所有学生的年龄+1
update student
set sgae=sage+!

3.将计算机系的所有学生的成绩置0
update sc
set grade=0
where sno in
(
 select sno
 from student
 where sdept='计算机系'
)



删除
delete 
from <表名>
[where <条件>] 
--1.删除学号为18446221的学生信息
delete 
from student
where sno='18446221'
--2.删除所有学生的选课记录
delete 
from sc
--3.删除计算机系的所有学生的选课记录
delete from sc
where sno in
(
select sno
from student
where sdept='计算机系'
)

create table s
(
 sno varchar(8) primary key,--主键
 sname varchar(10) not null,--不重名唯一
 ssex varchar(2) check(ssex='男' or ssex='女'),--取男或女
 sage int check(sage between 16 and 35),--取值【16-35】
 sdept varchar(10) default('信息学院'),--默认信息学院
)

create table c
(
  cno varchar(10) primary key,--非空主键
  cname varchar(15) not null,--非空
  cpno varchar(10), --foreign key(cpno) references c(cno),添加外键
  ccredit int check(ccredit between 1 and 8),--取值【1,8】
)
drop table c
create table sc
(
 sno varchar(8) not null,
 cno varchar(10) not null,
 grade int check(grade between 0 and 100),--取值【0,100】
 primary key(sno,cno),
 foreign key(sno) references s(sno),
 foreign key(cno) references c(cno)
)
insert s values('18446221','秦宇欣','女',18,'信息学院')
insert s values('18446223','施大大','女',19,'信息学院')
insert s values('18446201','陈超','男',19,'信息学院')
insert s values('18446202','陈代顺','男',18,'信息学院')
insert s values('18446203','陈嘉容','女',19,'信息学院')
insert s values('18446211','李芳','女',20,'信息学院')
insert s values('18116203','小骄傲','女',19,'外国语学院')
insert s values('18236203','小自豪','男',19,'体育学院')
insert s values('18436203','秦小易','女',19,'数学学院')

insert  c values('1','datavbase','2',2)
insert c values('2','data structure','1',2)
insert c values('3','math','2',3)
insert c values('4','english','2',1)


insert sc values('18446221','1',90)
insert sc values('18446223','2',80)
insert sc values('18446201','2',89)
insert sc values('18446221','2',100)
insert sc values('18446202','3',20)
insert sc values('18446202','2',40)
insert sc values('18436203','1',23)
select * from s

--1.查询所有学生的学号和姓名
select sno,sname
from s

--2.查询‘18446221’学生的姓名和出生年份，结果表属性分别命名为‘姓名’，‘出生年份’
select sname '姓名',(DATENAME(year,GETDATE())-sage) '出生年月'
from s
where sno ='18446221'
 
-- 3.查询信息学院年龄不在19和22之间的学生学号，姓名和年龄
 select sno,sname,sage
 from s
 where sdept='信息学院' and sage not between  19 and 22


 --4.查询所有名字含有‘陈’的学生学号和姓名
 select sno,sname
 from s
 where sname like '%陈%'

 --5.查询名字超过2个字的学生学号和姓名
 select sno,sname
 from s
 where len(sname)>2  --len()求长度
 
 --6.修改所在系的格式，将“**学院”改成“**DEPT”
 --第一种方法（但是学院前不是2个字也可以）
 select sdept =replace(sdept,'学院','DEPT')
 from s
--第二种方法（只能是学院前有2个字的，否则会覆盖第三个字）
select sdept =stuff(sdept,3,4,'DEPT')
from s


--7.查询所有信息学院，数学学院，体育学院的学生信息
select *
from s
where sdept in ('信息学院','数学学院','体育学院')
 
 --8.查询没有先行课的课程号和课程名
 select cno,cname
 from c
 where cpno is null --is null代表为空，没有
 
 --9.查询所有选了课的学生学号
 
 --10.查询成绩最高的3条学生选课记录
select top 3 *
from sc
order by grade desc

--11.找出最大年龄：
select top 1 *
from s
order by sage desc

--12.统计信息学院的人数
select count(*) '人数'--count()求总数
from s
where sdept='信息学院'

--13.统计各系学生的人数，结果按升序排列
select count(*) as '各系学生人数'
from s
group by sdept
order by 各系学生人数 

--14.按系统计各系学生的平均年龄，结果按降序排列：
--select [all | disinct] <目标列表达式>[,<目标列表达式>] 
--from <表名或视图名>[,<表名或视图名>]------ | (select语句）[as <别名>]
--[where <条件表达式>]
--[ group by <列名> [having<条件表达式> ]
--[order by <列名> [asc/desc]];

select AVG(sage) '各系学生的平均年龄'
from s
group by sage
--order by avg(sage) desc 或者写成
order by '各系学生的平均年龄' desc

--15.查询总分最高的前4个同学的学号和总分成绩(?)
select top 3 sno '学号',sum(grade) '总分成绩'
from sc
group by sno

--16.统计每个学生的平均成绩，每门课的平均成绩
select sno,cno,AVG(grade)
from sc
group by sno,cno
 --17.找出平均成绩在85分以上的学生，结果按照平均成绩的升序排列
 select sno,AVG(grade) '平均成绩'
 from sc
 group by sno
 having AVG(grade)>85--分组条件的筛选
 order by '平均成绩'

 --18.查询每个学生的姓名以及选修课程号和成绩
 select sname,cno,grade
 from s,sc
 where s.sno=sc.sno

 --19.查询了选修课程名含有“data”的课程的学生学号，姓名和课程名--查3个表
 select s.sno,s.sname,cname
 from s,c,sc
 where c.cname like'%data%'and s.sno=sc.sno and c.cno=sc.cno

 --20.查询考试成绩不及格（包含没有成绩）的学生的学号
 select sno
 from sc
 where grade<60 or grade is null 
 --这样查出来的18446202的同学两门不及格，则会显示两个18446202,所以：
  select distinct sno
 from sc
 where grade<60 or grade is null 

 --21查询考试成绩不及格（包含没有成绩）的学生的姓名
 select distinct s.sname
 from s,sc
 where (grade<60 or grade is null) and s.sno=sc.sno 
 
 --22.统计每位同学选修课程的门数，学分及其平均成绩
 select  sno,count(sc.cno) '选修课程门数',sum(ccredit) '学分',AVG(grade) '平均成绩'
 from sc,c
 where sc.cno=c.cno
 group by sno
 
 --23.查询与秦宇欣在同一个系学习的学生学号和姓名
 select sno,sname
 from s
 where sdept in
 (
  select sdept
  from s
  where sname='秦宇欣'
 )

 --24.查询选修了‘1’和‘2’课程的学生学号和姓名
 select  sno,sname
 from s
 where sno in
 (
  select sno
  from sc
  where cno='1' )and sno in (select sno from sc where cno=2)
 
 --25。查询选修了“”或“”号课程的学生学号和姓名
 select distinct sno,sname
 from s
 where sno in
 (select sno from sc where cno='1' or cno='2' or(cno ='1' and cno='2'))

 --26.查询没有选修课程的学生的基本信息
 select *
 from s
 where sno not in
 (
  select sno 
  from sc
 )
 --27.查询选修了1门以上课程的学生学号
 select sno
 from sc
 group by sno
 having count(*)>1

 --28.查询选修了全部课程的学生学号：
 select sno 
 from sc
 where sno not in
 (select sno 
 from sc 
 where cno is not null
 )

 --29.查询选修课程成绩至少有一门在80分以上的学生学号
 select distinct sno
 from sc
 where sno in
 (
  select sno 
  from sc
  where grade >80 
 ) 

 --30.查询所有选修课程成绩在80分以上的学生学号
 select sno
 from sc
 where sno not in
 (
 select sno
 from sc

 where grade <=80 and grade is not null 
 )
 --31.查询选修课程成绩平均成绩在80分以上的学生学号
 select distinct sno,AVG(grade)'平均成绩'
 from sc
 group by sno
 having avg(grade)>80

 select sdept ,AVG(sage)
from s
group by  sdept



insert into 表名 values()

delete from 
表名
where 条件

drop table  


select * 
from 

update 表名
set  要改变的