转载自:http://blog.csdn.net/chaojishuaigeli/article/details/8165802
- create
database demo - use
demo -
- create
table [user] - (
-
[uId] int identity(1,1) primary key, -
[name] varchar(50), -
[level] int --1骨灰2大虾3菜鸟 - )
- insert
into [user] (name,level) values('犀利哥',1) - insert
into [user] (name,level) values('小月月',2) - insert
into [user] (name,level) values('芙蓉姐姐',3) -
-
-
- --case
end 相当于switch case - --then后面的返回值类型必须一致
- select
[name], -
case [level] -
when 1 then '骨灰' -
when 2 then '大虾' -
when 3 then '菜鸟' -
end as '等级' - from
[user] -
- use
MySchool - select
* from score - --case
end第二种用法,相当于多重if语句 - select
studentId, -
case -
when english >=90 then '优' -
when english >=80 and english <90 then '良' -
when english >=70 and english < 80 then '中' -
when english >= 60 and english < 70 then '可' -
else '差' -
end as '成绩' - from
score - order
by english -
-
- --表中有A
B C三列,用SQL语句实现:当A列大于B列时选择A列否则选择B列,当B列大于C列时选择B列否则选择C列。 - select
-
case -
when a>b then a -
else b -
end, -
case -
when b>c then b -
else c -
end - from
T -
-
- --练习2
- create
table test - (
-
number varchar(10), -
amount int - )
- insert
into test(number,amount) values('RK1',10) - insert
into test(number,amount) values('RK2',20) - insert
into test(number,amount) values('RK3',-30) - insert
into test(number,amount) values('RK4',-10) -
- select
number, -
case -
when amount > 0 then amount -
else 0 -
end as '收入', -
case -
when amount < 0 then abs(amount) -
else 0 -
end as '支出' - from
test -
-
- --有一张表student0,记录学生成绩
- use
demo - CREATE
TABLE student0 (name nvarchar(10),subject nvarchar(10),result int) - INSERT
INTO student0 VALUES ('张三','语文',80) - INSERT
INTO student0 VALUES ('张三','数学',90) - INSERT
INTO student0 VALUES ('张三','物理',85) - INSERT
INTO student0 VALUES ('李四','语文',85) - INSERT
INTO student0 VALUES ('李四','数学',92) - INSERT
INTO student0 VALUES ('李四','物理',null) -
- select
* from student0 -
- select
[name], -
isnull(sum(case subject -
when '语文' then result -
end),0) as '语文', -
isnull(sum(case subject -
when '数学' then result -
end),0) as '数学', -
isnull(sum(case subject -
when '物理' then result -
end),0) as '物理' - from
student0 - group
by [name] -
-
-
- --子查询
- use
myschool - select
sName from (select * from student) as t -
-
-
- select
1,(select sum(english) from score) as '和',(select avg(sAge) from student) as '平均年龄' -
- --查询高一一班所有的学生
- select
* from student where sClassId = - (select
cId from class where cName='高一一班') -
- --查询高一一班
高二一班 所有的学生 - --子查询返回的值不止一个。当子查询跟随在
=、!=、<、<=、>、>= 之后 - --子查询跟在比较运算符之后,要求子查询只返回一个值
- select
* from student where sClassId = - (select
cId from class where cName in ('高一一班','高二一班')) -
-
- select
* from student where sClassId in - (select
cId from class where cName in ('高一一班','高二一班')) -
-
- --查询刘关张的成绩
- select
* from score where studentId in - (select
sId from student where sName in ('刘备123','关羽','张飞')) -
- select
* from student -
- --删除刘关张
- delete
from score where studentId in - (select
sId from student where sName in ('刘备123','关羽','张飞')) -
-
- --实现分页
- --最近入学的3个学生
- select
top 3 * from student - order
by sId desc -
- --查询第4到6个学生
- select
top 3 * from student - where
sId not in (select top 3 sId from student order by sId desc) - order
by sId desc -
-
- --查询7到9个学生
- select
top 3 * from student - where
sId not in (select top 6 sId from student order by sId desc) - order
by sId desc -
- --查询第n页的学生
- select
top 5 * from student - where
sId not in (select top (5*(2-1)) sId from student order by sId desc) - order
by sId desc -
-
- select
* from student -
-
- --sql
2005中的分页 -
- select
* from - (select
row_number() over(order by sId desc) as num,* from student) as t - where
num between 1 and 3 -
-
- select
* from - (select
row_number() over(order by sId desc) as num,* from student) as t - where
num between 4 and 6 -
- select
* from - (select
row_number() over(order by sId desc) as num,* from student) as t - where
num between 7 and 9 -
-
- select
* from - (select
row_number() over(order by sId desc) as num,* from student) as t - where
num between 3*(3-1) + 1 and 3*3 -
-
-
- --表连接
- --交叉连接cross
join - select
* from student - cross
join class -
-
- --内连接inner
join...on... - select
* from student - inner
join class on sClassId=cId -
- select
* from class -
- --查询所有学生的姓名、年龄及所在班级
- select
sName,sAge,cName,sSex from student - inner
join class on sClassId = cId - where
sSex ='女' - --查询年龄超过20岁的学生的姓名、年龄及所在班级
- select
sName,sAge,cName from class - inner
join student on sClassId = cId - where
sAge > 20 -
- --外连接
- --left
join...on... - select
sName,sAge,cName from class - left
join student on sClassId = cId