--综合举例
--1.统计男女生选修平面设计课程的人数，结果显示性别和人数
select b.sex,count(*) as num from relation a,student b
where a.stu_no=b.stu_no and a.c_no in(select c_no from course where c_name='平面设计')
group by b.sex

--2.统计各个班别选修平面设计课程的人数，结果显示班别和人数
select b.class_type,count(*) as num from relation a,student b
where a.stu_no=b.stu_no and a.c_no in(select c_no from course where c_name='平面设计')
group by b.class_type
 
--3.统计各个姓选修平面设计课程的人数，结果显示姓和人数
select b.name like '_%',count(*) as num from relation a,student b
where a.stu_no=b.stu_no and a.c_no in(select c_no from course where c_name='平面设计')
group by b.name like '_%'
--4.统计各个城市选修平面设计课程的人数，结果显示城市和人数
select b.city,count(*) as num from relation a,student b
where a.stu_no=b.stu_no and a.c_no in(select c_no from course where c_name='平面设计')
group by b.city
00000000000000000000000000
--综合举例
--1.统计选修了c_002课程的学生编号
c_001
c_002
c_003
c_004
c_002
c_004
c_002
c_001
c_004
c_005
c_006
c_002
c_005
select stu_no from relation where c_no='c_002'
 --统计选修了c_002课程的学生基本信息
 select * from student
 where stu_no in(select stu_no from relation where c_no='c_002')
 --统计选修了c_002课程的学生姓名和总分
 --1.
 select a.name,sum(b.mark) as summark
 from student a,relation b where a.stu_no=b.stu_no
 and a.stu_no in(select stu_no from relation where c_no='c_002')
 group by a.name 
 --2.
 select a.name,b.summark
 from student a,
 (select stu_no,sum(mark) as summark from relation group by stu_no)b
 where a.stu_no=b.stu_no and a.stu_no in(select stu_no from relation where c_no='c_002')
 
 
 --姓名和总分
 --1.
 select a.name,sum(b.mark) as summark
 from student a,relation b where a.stu_no=b.stu_no
 group by a.name 
 --2.
 select a.name,b.summark
 from student a,
 (select stu_no,sum(mark) as summark from relation group by stu_no)b
 where a.stu_no=b.stu_no
 
 
--2.统计至少选修了c_002课程的学生编号1236(同1)
c_001
c_002
c_003
c_004
c_002
c_004
c_002
c_001
c_004
c_005
c_006
c_002
c_005
--3.统计只选修了c_002课程的学生编号
select stu_no from relation where c_no='c_002' 
intersect
select stu_no from relation group by stu_no having count(*)=1
或
select stu_no from relation where c_no='c_002' 
and stu_no in(select stu_no from relation group by stu_no having count(*)=1)
或
select stu_no from relation
where stu_no in(select stu_no from relation where c_no='c_002')
group by stu_no having count(*)=1
 
 

 --选修了c_002课程的学生编号
 select stu_no from relation where c_no='c_002' 
 --选修了1门课程的学生编号
 select stu_no from relation group by stu_no having count(*)=1
 
 select stu_no,count(*) as num from relation group by stu_no
 having count(*)=1

 --统计只选修了c_002课程的学生基本信息
 select * from student where stu_no in(
 select stu_no from relation where c_no='c_002' 
 intersect
 select stu_no from relation group by stu_no having count(*)=1
 )
 --统计只选修了c_002课程的学生姓名和总分
 
 --1.
 select a.name,sum(b.mark) as summark
 from student a,relation b where a.stu_no=b.stu_no
 and a.stu_no in(
  select stu_no from relation where c_no='c_002' 
  and stu_no in(select stu_no from relation group by stu_no having count(*)=1)
 )
 group by a.name 
 --2.
 select a.name,b.summark
 from student a,
 (select stu_no,sum(mark) as summark from relation group by stu_no)b
 where a.stu_no=b.stu_no and a.stu_no in(
  select stu_no from relation
  where stu_no in(select stu_no from relation where c_no='c_002')
  group by stu_no having count(*)=1
 )
--3.统计只选修了c_002课程的学生编号
--4.统计没有选修了c_002课程的学生编号
--5.统计选修了c_002以及c_004课程的学生编号
--6.统计至少选修了c_002以及c_004课程的学生编号
--7.统计只选修了c_002以及c_004课程的学生编号
--8.统计没有选修了c_002以及c_004课程的学生编号
--9.用一条ｓｑｌ语句统计各个城市的总人数以及姓张的人数
--10.用一条ｓｑｌ语句统计各门课程及格率
课程名称 选修总人数 及格人数 及格率
数据库  20   20   100%
网页设计 12   6   50%
C语言  0   0   没人选修
英语  8   0   0%
...
000000000000000000000000
--4.统计没有选修了c_002课程的学生编号
--select stu_no from relation where c_no!='c_002'
--
--select * from relation where c_no!='c_002'
select stu_no from relation
except
select stu_no from relation where c_no='c_002'

select distinct stu_no from relation where stu_no not in(
select stu_no from relation where c_no='c_002')
 --统计没有选修了c_002课程的学生基本信息
 select * from student where stu_no in(
  select distinct stu_no from relation where stu_no not in(
  select stu_no from relation where c_no='c_002') 
 )
 --统计没有选修了c_002课程的学生姓名和总分
 --1.
 select a.name,sum(b.mark) as summark from student a, relation b
 where a.stu_no=b.stu_no
 and a.stu_no in(
  select stu_no from relation
  except
  select stu_no from relation where c_no='c_002'
 )
 group by a.name
 --2.
 select a.name,b.summark
 from student a,
 (select stu_no,sum(mark) as summark from relation group by stu_no) b
 where a.stu_no=b.stu_no
 and a.stu_no in(
  select distinct stu_no from relation where stu_no not in(
  select stu_no from relation where c_no='c_002')
 )
 
--5.统计选修了c_002以及c_004课程的学生编号
/*error
--select stu_no from relation where c_no='c_002' and c_no='c_004'
--select stu_no from relation where c_no='c_002' or c_no='c_004'
--select stu_no from relation where c_no in('c_002','c_004')
*/
select stu_no from relation where c_no='c_002'
intersect
select stu_no from relation where c_no='c_004'
或
select stu_no from relation where c_no='c_002'
and stu_no in(select stu_no from relation where c_no='c_004')

--6.统计至少选修了c_002以及c_004课程的学生编号(同5)
--7.统计只选修了c_002以及c_004课程的学生编号
(select stu_no from relation where c_no='c_002'
intersect
select stu_no from relation where c_no='c_004'
)
intersect
select stu_no from relation  group by stu_no having count(*)=2
或

select stu_no from relation where c_no='c_002'
and stu_no in(select stu_no from relation where c_no='c_004')
and stu_no in(select stu_no from relation  group by stu_no having count(*)=2)
 
--选修了c_002以及c_004课程的学生编号
select stu_no from relation where c_no='c_002'
intersect
select stu_no from relation where c_no='c_004'
--选修了2门课的学生编号
select stu_no from relation  group by stu_no having count(*)=2

--select * from relation where stu_no in('200104010','200802005')
--8.统计没有选修了c_002以及c_004课程的学生编号
select stu_no from relation where stu_no not in(select stu_no from relation where c_no='c_002')
and stu_no in(
select stu_no from relation where stu_no not in(select stu_no from relation where c_no='c_004'))
--select * from relation where stu_no in('200507004')

--9.用一条ｓｑｌ语句统计各个城市的总人数以及姓张的人数
select a.city as 城市名称,a.num as 总人数,b.num as 姓张的人数
from
(select city,count(*) as num from student group by city)a ,
(select city,count(*) as num from student where name like '张%' group by all city)b
where a.city=b.city
    

--统计各个城市的总人数
select city,count(*) as num from student group by city
--姓张的人数
select city,count(*) as num from student where name like '张%' group by all city

 --用一条ｓｑｌ语句统计各个城市的总人数以及姓张的人数以及姓王的人数
select a.city as 城市名称,a.num as 总人数,b.num as 姓张的人数,c.num as 姓王的人数
from
(select city,count(*) as num from student group by city)a ,
(select city,count(*) as num from student where name like '张%' group by all city)b,
(select city,count(*) as num from student where name like '王%' group by all city)c
where a.city=b.city and a.city=c.city
 

--10.用一条ｓｑｌ语句统计各门课程及格率
课程名称 选修总人数 及格人数 及格率
数据库  20   20   100%
网页设计 12   6   50%
C语言  0   0   没人选修
英语  8   0   0%
...
00000000000000000000
--10.用一条ｓｑｌ语句统计各门课程及格率
课程名称 选修总人数 及格人数 及格率
数据库  20   20   100%
网页设计 12   6   50%
C语言  0   0   没人选修
英语  8   0   0%
...
select c.c_name as 课程名称,c.num as 选修总人数,d.num as 及格人数,
case c.num
 when 0 then '没人选修'
 else cast(round(cast(d.num as float)/cast(c.num as float)*100,2) as varchar(10))+'%'
end as 及格率
from
(
select a.c_name,isnull(b.num,0) as num
from course a left outer join
(select c_no,count(*) as num from relation group by c_no) b
on a.c_no=b.c_no
)c,
(
select a.c_name,isnull(b.num,0) as num
from course a left outer join
(select c_no,count(*) as num from relation where mark>=60 group by all c_no) b
on a.c_no=b.c_no
)d
where c.c_name=d.c_name
 
 

课程名称 选修总人数 及格人数
数据库  20   20   
网页设计 12   6   
C语言  0   0   
英语  8   0   
select c.c_name as 课程名称,c.num as 选修总人数,d.num as 及格人数
from
(
select a.c_name,isnull(b.num,0) as num
from course a left outer join
(select c_no,count(*) as num from relation group by c_no) b
on a.c_no=b.c_no
)c,
(
select a.c_name,isnull(b.num,0) as num
from course a left outer join
(select c_no,count(*) as num from relation where mark>=60 group by all c_no) b
on a.c_no=b.c_no
)d
where c.c_name=d.c_name
 
 
课程名称 选修总人数
数据库  20     
网页设计 12     
C语言  0      
英语  8      
--先分组再连接
select a.c_name,isnull(b.num,0) as num
from course a left outer join
(select c_no,count(*) as num from relation group by c_no) b
on a.c_no=b.c_no
 
课程编号 选修总人数
c_001  20     
c_002  12     
c_003  0      
c_004  8      
select c_no,count(*) as num from relation group by c_no
select * from course
 
 
 
 
课程名称 及格人数
数据库  20   
网页设计 6   
C语言  0   
英语  0   
select a.c_name,isnull(b.num,0) as num
from course a left outer join
(select c_no,count(*) as num from relation where mark>=60 group by all c_no) b
on a.c_no=b.c_no
00000000000000000000000000000
--null值替换函数:
isnull(字段/变量,替换的值)
如果字段或者变量为null值就换成新值,新值的类型必须和字段变量的类型一致
 
case 字段
when 值1 then 结果1
when 值2 then 结果2
...
else 结果n
end

select name,sex,city from student
select name,
case sex
 when '男' then 'boy'
 when '女' then 'girl'
end as sex
,city from student

select name,
case sex
 when '男' then 'boy'
 else 'girl'
end as sex
,city from student

int
print 5.0/2.0

print 2/5
print 2.0/5.0

real
float
numeric
decimal
cast(字段/变量 as 新类型)
print round(3.1415926,4)
00000000000000000000000000000
INSERT 语句进一步分析：
1)insert into 表名[（字段列表）] values(对应字段列表的结果) (单行插入)
2)insert into 表名 default values(单行插入)
3)insert into 表名 子查询语句(批量插入)
 
--1)insert into 表名[（字段列表）] values(对应字段列表的结果) (单行插入)
select * from course
insert into course(c_no,c_name,teacher) values('c_016','AAAA','王老师')

insert into course values('c_017','BBBB','赵老师')
--2)insert into 表名 default values(单行插入)
                     (默认值)
create table test
(
 id int identity,
 name varchar(20) default '无名',
 city varchar(20) default '没有写'
)

insert into test values('张三','北京')
insert into test values('李四','上海')

select * from test order by id asc

insert into test default values (只有在设置默认值的情况下该语法生效)
drop table test
--//
declare @n int
set @n=1
while (@n<=100000)
begin
 insert into test default values 
 set @n=@n+1
end

select * from test

3)insert into 表名 子查询语句(批量插入)
select * from test
delete from test
select * from test
insert into test select name,city from student
insert into test(name,city) select name,city from student
 
--删除没有参加考试的学生信息
delete from student where stu_no not in(select stu_no from relation)
--查询没有参加考试的学生信息
select * from student where stu_no not in(select stu_no from relation)
drop table 表名 : 物理删除表,完成后表结构也被删除了.
truncate table 表名 :清空表中的所有记录,效率高,不能添加where条件，是固定语法,完成表结构依然存在
delete from 表名:清空表中的所有记录,可以添加where条件,完成表结构依然存在

truncate table test
select * from test
000000000000000000000000000000
--视图创建语法
create  view 视图名称
[with  encryption ]  --加密
as
select语句

create  view 视图名称
as
select语句

--北京地区学生信息的视图
create view beijingView
as
select * from student where city='北京'

select * from beijingView

create view rateView
as
select c.c_name as 课程名称,c.num as 选修总人数,d.num as 及格人数,
case c.num
 when 0 then '没人选修'
 else cast(round(cast(d.num as float)/cast(c.num as float)*100,2) as varchar(10))+'%'
end as 及格率
from
(
select a.c_name,isnull(b.num,0) as num
from course a left outer join
(select c_no,count(*) as num from relation group by c_no) b
on a.c_no=b.c_no
)c,
(
select a.c_name,isnull(b.num,0) as num
from course a left outer join
(select c_no,count(*) as num from relation where mark>=60 group by all c_no) b
on a.c_no=b.c_no
)d
where c.c_name=d.c_name

--//
select * from rateView
--//
select c.c_name as 课程名称,c.num as 选修总人数,d.num as 及格人数,case c.num  when 0 then '没人选修' else cast(round(cast(d.num as float)/cast(c.num as float)*100,2) as varchar(10))+'%' end as 及格率 from  ( select a.c_name,isnull(b.num,0) as num from course a left outer join  (select c_no,count(*) as num from relation group by c_no) b on a.c_no=b.c_no )c, ( select a.c_name,isnull(b.num,0) as num from course a left outer join  (select c_no,count(*) as num from relation where mark>=60 group by all c_no) b on a.c_no=b.c_no )d where c.c_name=d.c_name 
 
--修改alter
alter view beijingView
as
select id,name,sex,city,class_type from student where city='北京'
alter view beijingView(编号,姓名,性别,城市,班级类别)
as
select id,name,sex,city,class_type from student where city='北京'

alter view beijingView
as
select id 编号,name 姓名,sex 性别,city 城市,class_type 班级类别 from student where city='北京'

--//

alter view beijingView
with  encryption
as
select id 编号,name 姓名,sex 性别,city 城市,class_type 班级类别 from student where city='北京'
 
select * from beijingView
 

--删除视图语法
drop view 视图名称1,视图名称2,...

drop view rateView

select * from rateView

--直接简化查询语法
--间接简化查询语法
select * from  student
select * from  course
select * from  relation
create view fullView
as
select a.*,b.*,c.mark
from student a,course b,relation c
where a.stu_no=c.stu_no and b.c_no=c.c_no

select * from fullView

--姓名和总分
select name,sum(mark) as summark from fullView
group by name

--课程名称和平均分和选修人数
select c_name,avg(mark) as avgmark,count(*) as num from fullView
group by c_name
 
create table ssss
(
  id    int   identity
 city varchar(20) default '没有写',
    class_type    varchar(20),
    birthday    datetime
)

create table test
(
 id int identity,
 name varchar(20) default '无名',
 city varchar(20) default '没有写'
)
create table asdf1
(
     id   int identity,
 name varchar(20) default '无名',
 city varchar(20) default '没有写'
)
create table kkkk
(
   id   int identity,
   name  varchar(20)  default '无名'
   city  varchar(20) default '没有写'
)

drop table test
 
 
 
 
 
000000000000000000000000
 
CREATE  [UNIQUE]  [CLUSTERED  |  NONCLUSTERED]
INDEX  index_name
ON   table_name(column1)
[WITH  DROP_EXISTING]

--给学生表的city字段创建一个唯一性的聚集索引
create unique clustered index IX_student_city on student(city)

--给学生表的city字段创建一个唯一性的非聚集索引
create unique nonclustered index IX_student_city on student(city)

--给学生表的city字段创建一个非唯一性的非聚集索引
create nonclustered index IX_student_city on student(city)
 
truncate table ss
--删除
drop index 表名.索引名
drop index student.IX_student_city
00000000000000000000000000000000000000000000
-流程 控制:
-- BEGIN …END 程序块标识，类似于C语言的{  }
-- IF…ELSE 判断语句 
-- CASE…END 类似于C语言的switch( )语句
-- WHILE 循环语句

--1.条件结构
if (条件)
begin
 语句块1
end
else
begin
 语句块2
end
 
declare @n int
set @n=54
if (@n>0)
print '+++'
else
print 'not +++'
 
declare @n int
set @n=54
if (@n>0)
begin
print '正数'
print '+++'
end
else
begin
print '不是正数'
print 'not +++'
end
 
--判断学生表中是否存在姓张的
if exists(select * from student where left(name,1)='张')
print '存在'
else
print '不存在'
 
declare @count int
if exists(select * from student where left(name,1)='张')
begin
print '存在'
select @count=count(*) from student where left(name,1)='张'
print '有'+cast(@count as varchar(10))+'人'
end
else
print '不存在'
 

declare @count int
if exists(select * from student where left(name,1)='aa')
begin
print '存在'
select @count=count(*) from student where left(name,1)='aa'
print '有'+cast(@count as varchar(10))+'人'
end
else
print '不存在'
 
 
 

--2.选择结构
--case语法
一般用于查询语句select，基本语法格式有两种形式如下：
格式一:
case 字段
 when 结果1 then 改变后结果1
 when 结果2 then 改变后结果2
 ...
 when 结果n then 改变后结果n
 else  改变后结果n+1
end
格式二：
case
 when 表达式1 then 改变后结果1
 when 表达式2 then 改变后结果2
 ...
 when 表达式n then 改变后结果n
 else  改变后结果n+1
end

select name,sex,city from student

select name,
case sex
 when '男' then 'boy'
 when '女' then 'gril'
end as sex
,city from student

select name,
case sex
 when '男' then 'boy'
 else 'gril'
end as sex
,city from student

select name,
case
 when sex='男' then 'boy'
 when sex='女' then 'girl'
end as sex
,city from student
 
select * from student

<10000 低
>=10000  <15000 中
>=15000 高
select name,
case
 when fee<10000 then '低'
 when fee>=10000 and fee<15000 then '中'
 when fee>=15000 then '高'
end as pj
from student
 
select
case
 when fee<10000 then '低'
 when fee>=10000 and fee<15000 then '中'
 when fee>=15000 then '高'
end as pj,count(*) as num
from student
group by case
 when fee<10000 then '低'
 when fee>=10000 and fee<15000 then '中'
 when fee>=15000 then '高'
end

select * from relation
 
--mark
<60 不及格
>=60  <80 及格
>=80  <90 良好
>=90  <=100 优秀

统计各个等级的人数
select
case
 when mark<60 then  '不及格'
 when mark>=60 and mark<80 then  '及格'
 when mark>=80 and mark<90 then '良好'
 when mark>=90 and mark<=100 then '优秀'
end as pj,count(*) as num
from relation
group by case
 when mark<60 then  '不及格'
 when mark>=60 and mark<80 then  '及格'
 when mark>=80 and mark<90 then '良好'
 when mark>=90 and mark<=100 then '优秀'
end

--3.循环
while (条件)
begin
 循环代码
end

declare @n int
set @n=1
while (@n<=100)
begin
 print @n
 set @n=@n+1
end
--1+2+3+...+100
declare @n int
declare @sum int
set @sum=0
set @n=1
while (@n<=100)
begin
 set @sum=@sum+@n
 set @n=@n+1
end
print @sum
--1+3+5+...+99
declare @n int
declare @sum int
set @sum=0
set @n=1
while (@n<=100)
begin
 set @sum=@sum+@n
 set @n=@n+2
end
print @sum
--2+4+6+...+100
declare @n int
declare @sum int
set @sum=0
set @n=2
while (@n<=100)
begin
 set @sum=@sum+@n
 set @n=@n+2
end
print @sum
 
 
 
 
 
------------
--1+3+5+...+99   @sum1
--2+4+6+...+100  @sum2
--1+2+3+...+100
declare @n int
declare @sum1 int,@sum2 int
set @sum1=0
set @sum2=0
set @n=1
while (@n<=100)
begin
 if (@n % 2=1) set @sum1=@sum1+@n else set @sum2=@sum2+@n
 set @n=@n+1
end
print @sum1
print @sum2
print @sum1+@sum2

--if case while
过程和函数
 

补充：
--使用存储过程完成信息的添加 insert
select * from test
delete from test
insert into test
select top 3 name,sex,city from student order by age asc

--使用存储过程完成信息的添加 insert_test
create procedure insert_test
(
 @name varchar(50),
 @sex  char(2),
 @city  varchar(50)
)
as
begin
insert into test(name,sex,city) values(@name,@sex,@city)
if (@@rowcount=1)
print '添加成功'
else
print '添加失败'
end

print @@rowcount
select * from test

execute insert_test 'aa','bb','cc'
execute insert_test @name='张三',@city='武汉',@sex='男'
 
0000000000000000000000000000
 
--1.返回数值的用户自定义函数（标量函数）
语法格式：
create  function 函数名称
(
 参数1 类型,
 参数2 类型,
 ...
)
returns  data_type --代表函数返回值类型
[with  encryption]
as
begin
   function_body
   return  expression --函数返回值
end
--用户自定义函数可以接受零个或多个输入参数，
--函数的返回值可以是一个数值，也可以是一个表，
--用户自定义函数不支持输出参数
/*
f(n)=1+2+3+...+n
f(1)=1
f(2)=3
f(3)=6
...
*/
create function f(@n int)
returns int
as
begin
 declare @sum int
 declare @i int
 set @i=1
 set @sum=0
 while (@i< =@n )
 begin
  set @sum=@sum+@i
  set @i=@i+1
 end
 return @sum
end
 
--标量函数调用必须指定所有用户dbo
select dbo.f(100)
print dbo.f(100)
select dbo.f(10)
print dbo.f(10)
select dbo.f(100),* from student
--通过姓名获取总分
create function myfun001(@name varchar(20))
returns float
as
begin
 declare @sum float
 set @sum=0
 if exists(select * from student where name=@name )
 begin
  if exists(select * from relation where stu_no in(select stu_no from student where name=@name ))
  begin
   select @sum=sum(mark) from relation where stu_no in(select stu_no from student where name=@name )   
  end
  else
  begin 
   set @sum=-2
  end
 end
 else
 begin
  set @sum=-1
 end
 return @sum
end

--姓名不存在-1
--姓名存在但没有参加过考试-2
--存在总分
--select dbo.myfun001('张三')
declare @s float
set @s=dbo.myfun001('张三')
if (@s=-1)
print '查无此人'
else if (@s=-2)
print '张三没有参加过任何课程考试'
else
print '张三总分:'+cast(@s as varchar(20))
 
select name,round(dbo.myfun001(name),0) as zongfensex,city from student
order by 2 desc
--通过课程名称获取平均分
 
 

--2.内联（单语句）的返回表的用户自定义函数
语法如下：
create  function  function_name
(@parameter_name   data_type  [=default]
returns  table
[with  encryption]
as
return  (select_statement)

--city
create function myfun003(@city varchar(20))
returns table
as
return
select * from student where city=@city

select * from myfun002('北京')
select * from dbo.myfun002('北京')
 
--修改alter

alter function myfun002(@city varchar(20))
returns table
with  encryption
as
return
select name,sex,city,class_type from student where city=@city

--删除drop
drop function 函数名称1,函数名称2,...
drop function myfun002
 
00000000000000000000000000000