SQL基础温故而知新

最新推荐文章于 2024-09-21 10:25:52 发布

zhang_professor

最新推荐文章于 2024-09-21 10:25:52 发布

阅读量671

点赞数

文章标签： sql

本文链接：https://blog.csdn.net/zhang_professor/article/details/38440809

版权

/*
多行注释
*/
--创建数据库
/*
create database 数据库名称
on
(
name='', --逻辑名称
filename='', --物理名称
size= ,   --初始大小
maxsize= , --最大值
filegrowth=    --文件增长率
)
log on
(
name='', --逻辑名称
filename='', --物理名称
size= ,   --初始大小
maxsize= , --最大值
filegrowth=    --文件增长率
)
*/
--只有一个主数据文件
create database testDB
on
(
name = 'testDB',
filename = 'd:\testDB.mdf',
size = 3mb ,
maxsize = unlimited , --无限增长
filegrowth = 10%
)
go
--一个主数据文件，一个日志文件
create database t1
on
(
name = 't1',
filename = 'd:\t1.mdf',
size = 3mb ,
maxsize = 10 ,
filegrowth = 1
)
log on
(
name = 't1_log',
filename = 'd:\t1.ldf'
)
go
--一个主数据文件，一个次要数据文件，一个日志文件
create database t2
on
(
name = 't2',
filename = 'e:\t2.mdf',
size = 5mb,
maxsize = 10mb,
filegrowth = 1
),
(
name = 't2_1',
filename = 'e:\t2.ndf',
size = 3mb,
maxsize = 10mb,
filegrowth = 10%
)
log on
(
name = 't2_log',
filename = 'e:\t2.ldf'
)
go   --批处理结束的标志

create database t3

--删除数据库

drop database t2

--查看所有数据库信息
exec sp_helpdb

--查看testDB数据库信息
exec sp_helpdb testDB

--判断数据库是否存在
if exists(select * from sysdatabases where name = 'testDB')
print '存在'
else
print '不存在'

use master
if exists(select * from sysdatabases where name = 'testDB')
drop database testDB
create database testDB

on
(
name = 'testDB',
filename='e:\sofware engineer\review\SQL\0709\taobao.mdf',
size = 5mb,
maxsize = unlimited ,
filegrowth = 10%
)
log on
(
name = 'testDB_log',
filename='e:\sofware engineer\review\SQL\0709\taobao.ndf',
size = 5mb,
maxsize = unlimited ,
filegrowth = 10%
)
go

/*
create table 表名
(
字段数据类型说明,
字段数据类型说明,
...
字段数据类型说明
)*/
/*
char(6):固定长度(6个字节)。
varchar(6):可变长度(6个字节)。
nvarchar(6):6个字符（每个字符按照2个字节存储）
*/

use testDB
if exists(select * from sysobjects where name = 'tbl_grade')
drop table tbl_grade
--年级表
create table tbl_grade
(
gid int identity(1,1) primary key,
gname varchar(20) not null
)
go
if exists(select * from sysobjects where name = 'tbl_class')
drop table tbl_class
--班级表
create table tbl_class
(
cid int identity primary key,
cname varchar(20) not null,
gid int foreign key references tbl_grade(gid)
)
go

if exists(select * from sysobjects where name = 'tbl_student')
drop table tbl_student
--学生表
create table tbl_student
(
stuNo int identity(1000,1) primary key,
stuName varchar(20) not null,
stuAge int check (stuAge between 18 and 60),
stuEmail varchar(50) check(stuEmail like '%@%'),
stuSex char(2) check(stuSex='男' or stuSex='女'),
stuID char(18) check(len(stuID)=18) unique,
stuAddress varchar(50) default('地址不详'),
cid int foreign key references tbl_class(cid)
)
go
if exists(select * from sysobjects where name = 'tbl_course')
drop table tbl_course
--课程表
create table tbl_course
(
courseId int identity primary key,
courseName varchar(50) not null
)
go

--成绩表
create table tbl_score
(
id int identity primary key,
courseId int foreign key references tbl_course(courseId),
stuNo int foreign key references tbl_student(stuNo),
score int check(score between 0 and 100)
)
go
select * from tbl_score
--约束：
--主键约束：primary key 非空且唯一
----主键约束可以修改，但是修改后的值不能有重复的。
--非空：not null
-----区别 ''
--外键：foreign key
-----和主表的关系，主表中的数据可以不全部出现在从表中；但是如果从表出现的数据，必须在主表中出现。
-----删除表时，对于有主外键关系的两张表，要先删除从表，然后再删除主表。
-----删除表中数据时，对于有主外键关系的表，要先删除从表中相应的数据，然后再删除主表的数据。
--检查：check
-----符合检查表达式
--默认：default
-----对于有默认约束的字段，如果不插入数据，该字段的值为默认值。
--唯一：unique
-----允许有一个null值

drop table test
create table test
(
a int not null , --主键
b varchar(20) not null, --非空
c char(2),
d varchar(20),
e varchar(20),
f varchar(20)
)

create table test1
(
ff varchar(20) primary key
)

--查看某张表的相关信息
exec sp_help test

--修改：alter
/*
添加约束语法
alter table 表名
add constraint 约束名称约束类型说明
*/
--给test表添加主键约束
alter table test
add constraint PK_a primary key (a)
--给test表添加唯一约束
alter table test
add constraint UQ_c unique (c)

--添加检查约束
alter table test
add constraint CK_d check(len(d)=20)

--默认约束
alter table test
add constraint DF_e default('不详') for e

--外键约束
alter table test
add constraint FK_f foreign key (f) references test1(ff)

--增加
--insert [into] 表名[(字段名)] values (值)
---标识列不需要显示插入数据
---插入数据时，如果向表中全部字段插入数据，字段名可以省略
---当向表中部分字段插入数据时，非空字段不可以省略
---插入数据时，字段的个数、类型、顺序必须和值列表一致
---插入数据时，必须符合约束要求
---当向有默认约束的字段插入数据时，可以通过default来代替默认值

---2 将一张表的数据重新复制到一张不存在的表中
/*
select 列名
into 新表名 --不存在
from 旧表名
*/

select stuname,stuid,stuage
into newTable1
from tbl_student

select * from newTable1
---3 将一张表的数据插入到一张已经存在的表中
/*
insert [into] 表名[(字段名)] --存在
select 列名
from 旧表名
*/
insert into newTable1
select stuname,stuid,stuage
from tbl_student

--修改：
update 表名 set 字段1=值1[,字段2=值2] [where 条件]
---标识列不能更新
---当省略where条件，相当于修改表中全部数据
---如果没有符合where条件的数据在修改时，不会报错，显示0行受影响
---在修改数据时，必须符合约束要求、数据类型

select * from tbl_student

--将班级编号为null的信息，班级编号修改为4
update tbl_student set cid=4 where cid is null

--将邮箱不为null的学生年龄+1岁
update tbl_student set stuAge = stuAge+1 where stuEmail is not null

--将年龄在20岁以上并且行为为男的同学年龄-1岁
update tbl_student set stuAge = stuAge-1 where stuAge>20 and stuSex='男'

--删除：
delete [from] 表名 [where 条件]
----当删除表中全部数据，where条件可以省略
----当没有符合条件的数据在删除时，不会报错，显示0行受影响
----在删除有主外键关系的数据时，先删除从表中的相应数据，然后再删除主表的数据
----对于有标识列的表，在删除数据时，标识列的值会继续增加

select * from newTable1

delete from newTable1 where stuname='张三' or stuname = '李四'

delete newTable1

--查看邮箱不为null的学生信息
select * from tbl_student where stuEmail is not null

--查看班级编号大于2的所有学生信息
select * from tbl_student where cid > 2

--模糊查询:like
----通配符：[1-9] [19] [^1-4] _ %
--查看邮箱中包含163的所有学生信息
select * from tbl_student where stuEmail like '%163%'

select * from tbl_course
--查看课程表中课程名称带有java或者课程名称中以C开头的所有课程
select * from tbl_course where courseName like '%java%' or courseName like 'C%'

--查看学生表中身份证号第二位是2的学生信息
select * from tbl_student where stuid like '_2%'

--查看学生表中身份证号第二位是2到5，第三位是3或者4的学生信息
select * from tbl_student where stuID like '_[2-5][34]%'

--排序
--按照学生的年龄对学生降序排列
select * from tbl_student order by stuAge desc

--按照学生的班级升序排列学生信息
select * from tbl_student order by cid

--按照学生姓名降序排列
select * from tbl_student order by stuName desc

--分组group by
---聚合函数：max() min() sum() avg() count()

--每个班级学生人数
select 人数=COUNT(*),cid from tbl_student group by cid

--男同学和女同学的人数
select COUNT(*),stusex from tbl_student group by stuSex

--统计每个地址的男女同学的数量
select COUNT(*) as 人数,stusex,stuaddress from tbl_student group by stusex,stuaddress

--查看同一个地址中人数大于2个的信息
select COUNT(*),stuaddress from tbl_student group by stuAddress
having COUNT(*)>2

--查看每一个班级的最大年龄和最小年龄
select MAX(stuage) 最大年龄,MIN(stuage) as 最小年龄,cid from tbl_student
group by cid

--表连接查询
--内连接：inner join 两张表中同时存在的记录
/*
select 表1.列名,表2.列名,表3.列名 ...
from 表1 inner join 表2
on 表1.列 = 表2.列 --主外键
inner join 表3
on 表2.列 =表3.列

--外连接：
----左：left join 左表的数据会全部显示，如果右表没有匹配，用null显示
----右：right join
----全：full join = 左 + 右

--班级名称，年级名称
select cid,cname,gname from tbl_grade,tbl_class where tbl_grade.gid = tbl_class.gid

select cid,cname,gname from tbl_grade inner join tbl_class
on tbl_grade.gid = tbl_class.gid

--学生姓名，性别，地址和班级名称
select stuname,stusex,stuaddress,cname
from tbl_class inner join tbl_student
on tbl_class.cid = tbl_student.cid

--显示学生姓名和成绩
select tbl_student.stuNo,stuname,score,courseId from tbl_student inner join tbl_score
on tbl_student.stuNo = tbl_score.stuNo

--显示课程名称和成绩
select tbl_score.courseId,coursename,score
from tbl_score inner join tbl_course
on tbl_score.courseId = tbl_course.courseId

--课程名称，学生姓名，成绩
select stuname ,courseName ,score
from tbl_student inner join tbl_score
on tbl_student.stuNo=tbl_score.stuNo
inner join tbl_course
on tbl_course.courseId = tbl_score.courseId

--班级名称，学生姓名，成绩,课程名称,年级名称
select gname,cname,stuname,score,coursename
from tbl_grade inner join tbl_class
on tbl_grade.gid = tbl_class.gid
inner join tbl_student
on tbl_class.cid = tbl_student.cid
inner join tbl_score
on tbl_score.stuNo = tbl_student.stuNo
inner join tbl_course
on tbl_score.courseId = tbl_course.courseId

--1 显示参加html考试的学生的姓名和成绩
select courseName,stuName,score from
tbl_course inner join tbl_score on
tbl_score.courseId=tbl_course.courseId
inner join tbl_student on tbl_student.stuNo=tbl_score.stuNo where courseName = 'html'

--2 显示张三的所有考试成绩，考试科目
select stuname,score,coursename
from tbl_course inner join tbl_score
on tbl_score.courseId= tbl_course.courseId inner join tbl_student
on tbl_score.stuNo=tbl_student.stuNo
where stuName = '张三'

--3 显示1206A班所有学生的考试成绩，姓名，科目
select stuname,score,coursename,cname from tbl_student
inner join tbl_score on tbl_score.stuNo=tbl_student.stuNo
inner join tbl_course on tbl_score.courseId=tbl_course.courseId
inner join tbl_class on tbl_class.cid=tbl_student.cid
where cname='1206A'

--4 查看专业阶段的所有班级名称和学生姓名
select stuname,gname,cname from tbl_student
inner join tbl_class on tbl_student.cid=tbl_class.cid
inner join tbl_grade on tbl_class.gid=tbl_grade.gid where gname='专业阶段'

--5 查看每门课称的平均分和课程名称
select courseName, AVG(score)
from tbl_score inner join tbl_course
on tbl_score.courseId=tbl_course.courseId
group by tbl_course.courseName

--6 查看学生姓名和最终考试成绩（所有考试的平均分）
select stuname,AVG(score)
from tbl_student inner join tbl_score
on tbl_score.stuNo = tbl_student.stuNo
group by stuname

--7 查看学生姓名，课程名称和最终考试成绩
select stuname,courseName,AVG(score)
from tbl_student inner join tbl_score
on tbl_score.stuNo = tbl_student.stuNo
inner join tbl_course
on tbl_course.courseId=tbl_score.courseId
group by stuname,courseName

--8 查看参加过2次考试的学生姓名
select stuname,COUNT(*) from tbl_student inner join tbl_score
on tbl_score.stuNo=tbl_student.stuNo
group by stuName
having COUNT(*)=2

--9 查看平均分最高的课程名称
select top 1 AVG(score),tbl_course.courseName
from tbl_score inner join tbl_course
on tbl_course.courseId=tbl_score.courseId
group by tbl_course.courseName
order by avg(score) desc

--10 查看每个班级的平均分并降序显示
select cname,平均分=AVG(score)
from tbl_student inner join tbl_score on tbl_student.stuNo = tbl_score.stuNo
inner join tbl_class on tbl_class.cid = tbl_student.cid
group by cname
order by AVG(score) desc

--11查看每个阶段的平均分
select gname,AVG(score) 平均分
from tbl_grade inner join tbl_class on tbl_grade.gid = tbl_class.gid
inner join tbl_student on tbl_student.cid = tbl_class.cid
inner join tbl_score on tbl_score.stuNo =tbl_student.stuNo
group by gname

--12 查看没有参加考试的学生姓名
select score,stuname from
tbl_score right join tbl_student
on tbl_score.stuNo=tbl_student.stuNo
where score is null

select stuname from tbl_student where stuNo not in(select stuNo from tbl_score)

--13 查看没有被考过试的课程名称
select courseName from tbl_course where courseId not in(select courseid from tbl_score)

select courseName,score from tbl_score right join tbl_course
on tbl_score.courseId = tbl_course.courseId
where score is null

--班级名称，年级名称
select * from tbl_grade,tbl_class where tbl_grade.gid = tbl_class.gid

select cid,cname,gname from tbl_grade inner join tbl_class
on tbl_grade.gid = tbl_class.gid

--学生姓名，性别，地址和班级名称
select stuname,stusex,stuaddress,cname
from tbl_class inner join tbl_student
on tbl_class.cid = tbl_student.cid

--显示学生姓名和成绩
select tbl_student.stuNo,stuname,score,courseId from tbl_student inner join tbl_score
on tbl_student.stuNo = tbl_score.stuNo

--显示课程名称和成绩
select tbl_score.courseId,coursename,score
from tbl_score inner join tbl_course
on tbl_score.courseId = tbl_course.courseId

--班级名称，学生姓名，成绩,课程名称,年级名称
select gname,cname,stuname,score,coursename
from tbl_class inner join tbl_student
on tbl_class.cid = tbl_student.cid
inner join tbl_score
on tbl_score.stuNo = tbl_student.stuNo
inner join tbl_course
on tbl_score.courseId = tbl_course.courseId
inner join tbl_grade
on tbl_grade.gid = tbl_class.gid

--显示参加html考试的学生的姓名和成绩
select stuname,courseName,score from
tbl_student inner join tbl_score on
tbl_score.stuNo = tbl_student.stuNo
inner join tbl_course on
tbl_score.courseId = tbl_course.courseId
where courseName='HTML'
--显示张三的所有考试成绩，考试科目
select stuname,score,courseName from
tbl_student inner join tbl_score on
tbl_score.stuNo = tbl_student.stuNo
inner join tbl_course on
tbl_score.courseId = tbl_course.courseId
where stuname='张三'

--显示1206A班所有学生的考试成绩，姓名，科目
select cname,stuname,score,courseName from
tbl_class inner join tbl_student
on tbl_class.cid = tbl_student.cid
inner join tbl_score on
tbl_score.stuNo = tbl_student.stuNo
inner join tbl_course on
tbl_score.courseId = tbl_course.courseId
where cname='1206A'
--查看专业阶段的所有班级名称和学生姓名
select gname,cname,stuname from
tbl_grade inner join tbl_class
on tbl_grade.gid = tbl_class.gid
inner join tbl_student
on tbl_class.cid=tbl_student.cid
where gname='专业阶段'
--查看每门课称的平均分和课程名称
select courseName,AVG(score)from
tbl_course inner join tbl_score
on tbl_course.courseId=tbl_score.courseId
group by courseName

--查看学生姓名和最终考试成绩（所有考试的平均分）
select stuname,AVG(score)from
tbl_student inner join tbl_score on
tbl_score.stuNo = tbl_student.stuNo
group by stuname
--查看学生姓名，课程名称和最终考试成绩
select stuname,courseName,AVG(score)from
tbl_student inner join tbl_score on
tbl_score.stuNo = tbl_student.stuNo
inner join tbl_course on
tbl_score.courseId = tbl_course.courseId
group by stuname,courseName
--查看参加过2次考试的学生姓名
select stuname,count(*)as 考试次数 from
tbl_student inner join tbl_score on
tbl_score.stuNo = tbl_student.stuNo
group by stuname having count(*)>=2
--查看平均分最高的课程名称
select top 1 courseName,AVG(score)from
tbl_course inner join tbl_score
on tbl_course.courseId=tbl_score.courseId
group by courseName order by courseName desc
--查看每个班级的平均分并降序显示
select cname,AVG(score)from
tbl_class inner join tbl_student on
tbl_class.cid=tbl_student.cid
inner join tbl_score on
tbl_score.stuNo = tbl_student.stuNo
group by cname order by AVG(score) desc
--查看每个阶段的平均分
select gname,AVG(score)from
tbl_grade inner join tbl_class
on tbl_grade.gid = tbl_class.gid
inner join tbl_student
on tbl_class.cid=tbl_student.cid
inner join tbl_score on
tbl_score.stuNo = tbl_student.stuNo
inner join tbl_course on
tbl_score.courseId = tbl_course.courseId
group by gname
--查看没有参加考试的学生姓名
select stuName from tbl_student where stuName not in
(select stuName from
tbl_student inner join tbl_score on
tbl_score.stuNo = tbl_student.stuNo
group by stuName)
--查看没有被考过试的课程名称
select courseName from tbl_course where courseName not in
(select courseName from
tbl_course inner join tbl_score on
tbl_score.courseId = tbl_course.courseId
group by courseName)

--嵌套查询
-- = ：如果子查询的结果唯一，可以用“=”
--查看和张三在同一个班级的学生信息
select * from tbl_student where cid =
(select cid from tbl_student where stuName = '张三') --张三的班级编号

-- in:当子查询的结果不唯一时，可以用"in"
--查看参加考试的学生信息
select * from tbl_student where stuNo in
(select stuno from tbl_score)

-- not in:没参加考试的学生信息
select * from tbl_student where stuNo not in
(select stuNo from tbl_score)

select * from tbl_student
select * from tbl_score

--考试及格的同学信息
select * from tbl_student where stuNo in
(select stuNo from tbl_score where score>=60)

--没有不及格的同学信息
select * from tbl_student where stuNo in
(select stuNo from tbl_score where score < 60)

--所有科目均不及格或者没有考试的同学信息
select * from tbl_student where stuNo not in
(select stuNo from tbl_score where score>=60)

------------------------------------------
-- 分页
select * from tbl_student
--每次显示三条记录
-- 一
select top 3 * from tbl_student
--二
select top 3 * from tbl_student where stuNo not in
(select top (3*1) stuNo from tbl_student) --将第一页的记录去掉

--三
select top 3 * from tbl_student where stuNo not in
(select top (3*2) stuNo from tbl_student) --将前两页的记录去掉

--所有的嵌套查询都可以用表连接进行替换，但是不是所有表连接都能用嵌套查询替换
select * from tbl_grade --6
select * from tbl_class --9

select * from tbl_grade cross join tbl_class --54

select * from tbl_grade inner join tbl_class
on tbl_grade.gid = tbl_class.gid --9

select * from tbl_grade inner join tbl_class
on tbl_grade.gid <> tbl_class.gid --54-9=45

--合并结果集:1 列的数量必须一致 2 列的类型必须相同
select gid,gname from tbl_grade
union
select cid,cname from tbl_class

--函数
----日期函数：
---getdate():获得当前时间
select GETDATE()
--dateadd():在制定时间上进行添加时间 dateadd（要增加的日期部分,要增加的数值，相应的时间）
select DATEADD(yy,3,getdate())--三年后

--datediff():计算两个日期之间的时间差
select DATEDIFF(dd,getdate(),'2012-08-01')
select DATEDIFF(hour,getdate(),'2012-08-01')

--datepart():获得日期的部分
select DATEPART(yy,getdate())

select * from tbl_student
--根据学生年龄，判断学生的出生年份
select 姓名=stuname,年龄=stuage,
出生年份=datepart(yy,DATEADD(yy,-stuAge,getdate()))
from tbl_student

--数学函数:
--floor():向下取整，小于或等于当前数的最大整数
select FLOOR(12.49) --12
select FLOOR(-12.49) -- -13
--ceiling()：向上取整，大于或等于当前数的最小整数
select CEILING(12.1) --13
select CEILING(-12.1) -- -12

--abs():绝对值
select ABS(0)
select ABS(-1)
select ABS(1)

--round():四舍五入
select ROUND(11.5,0) --12.0
select ROUND(11.4,0) --11.0
select ROUND(11.49,1) --11.50

select ROUND(-11.5,0) -- -12.0
select ROUND(-11.4,0) -- -11.0
select ROUND(-11.49,1) -- -11.50

--rand():随机数
select RAND()

--字符串函数
--len():长度
select LEN(stuname) from tbl_student

--left():从左边开始获得字符
select LEFT('abcdef',2)
--right()
select right('abcdef',2)
--获得学生的姓氏（不考虑复姓的情况）
select 姓氏=left(stuname,1) from tbl_student

--ltrim() :去掉字符串左端的空格
select '1'+LTRIM(' aaaa ')+'1'
--rtrim()
select '1'+RTRIM(' aaaa ')+'1'

--去掉字符串两端空格
select '1'+RTRIM(LTRIM(' aaaa '))+'1'

--substring():从1开始
select SUBSTRING('abcdefg',1,4)
--获得名字
select 姓=left(stuname,1),名=SUBSTRING(stuname,2,len(stuname)) from tbl_student

select LEFT(gname,1),substring(gname,2,LEN(gname)) from tbl_grade

--replace():字符串替换
select REPLACE('abcdefg0o0o','0','o')

select replace(GETDATE(),'-','/')

--reverse():反转
select REVERSE(stuname) from tbl_student

--lower()
select LOWER('aasSAFesda')

--upper()
select upper('aasSAFesda')

--类型转换函数
--cast():cast(要转换的值 as 目标类型)
select 'a'+cast(1 as CHAR(1))

--convert():convert(目标类型，要转换的值)
select 'a'+CONVERT(char(1),1)

--将编号和姓名连接起来
select stuno,stuname from tbl_student
select cast(stuno as CHAR(4))+'__' + stuname from tbl_student

select convert(CHAR(4),stuno) +'__' + stuname from tbl_student

--T-SQL编程
--变量：
----局部变量：@ 先声明，再赋值
----全局变量：@@ 系统定义，只读

select 'a'+1
--@@error:最后一次执行sql语句的错误号
select @@ERROR

--声明变量
declare @num int
--赋值
set @num = 5 --select
print @num

--统计班级的学生人数
declare @sum int
select @sum=COUNT(*) from tbl_student
print @sum

declare @sum1 int
select @sum1=(select COUNT(*) from tbl_student)
print @sum1

--统计班级中男女同学的差
declare @nan int,@nv int
select @nan = COUNT(*) from tbl_student where stuSex ='男'
select @nv = (select COUNT(*) from tbl_student where stuSex = '女')
print '男女同学的人数差'+cast((@nan - @nv) as varchar(2))

go
--if-else
--统计班级中男女同学的差
declare @nan int,@nv int
select @nan = COUNT(*) from tbl_student where stuSex ='男'
select @nv = (select COUNT(*) from tbl_student where stuSex = '女')
if(@nan>@nv)
print '和尚班'
else
print '尼姑班'
go

--视图：view 虚拟表,视图中的数据全部来源自基表（源表）.视图可以是一张表的部分字段，也可以是多张表的多个字段。
----可以对视图进行增删改查的操作,但是相当于操作基表的数据
/*
create view 视图名称
as
select 语句
go
*/

use testDB
go
create view v1
as
select stuNo,stuName from tbl_student
go

select * from v1 where stuname like '张_'

update v1 set stuname = '张三丰' where stuno = 1000

delete from v1 where stuno =1010

insert into v1 values('a')

select * from tbl_student

create view v2
as
select stuname,cname,gname from tbl_student inner join tbl_class
on tbl_student.cid = tbl_class.cid inner join tbl_grade on tbl_class.gid=tbl_grade.gid

select * from v2 where gname = '专业阶段'

--两次对表A查询效率较低
select top 10 * from Us where ID not in (select top 30 ID from Us)

--外层查询没有对表A查询，效率大有提高
select top 10 * from (select top 40 * from Us order by ID) as t order by t.ID desc

--ROW_NUMBER()函数效率更高，sqlserver2005以及以上版本中才可以使用
select * from (select ROW_NUMBER() over(order by ID) as 'sequence',Us.* from Us ) as t where t.sequence between 31 and 40

select Student_name, Math=
case when Math<60 then '不及格'
else case when Math>=60 and Math <75 then '及格'
    else case when Math >= 75 and Math < 85 then '良好'
      else case when Math >=85 then '优秀'
        else '未考试'
          end
          end
          end
          end
     , Chinese=
case when Chinese<60 then '不及格'
else case when Chinese>=60 and Chinese <75 then '及格'
    else case when Chinese >= 75 and Chinese < 85 then '良好'
      else case when Chinese >=85 then '优秀'
        else '未考试'
          end
          end
          end
          end
from score

create database baiwei

use baiwei

create table Us
(
id int primary key identity,
name varchar(20) not null,
passwords varchar(20) not null,
datatime datetime
)

--drop table Users

declare @i bigint =1
--声明变量
declare @str varchar(2000)
declare @curr int
declare @prev int
declare @datetime datetime
declare @count int
--给变量赋值
set @str='赵,钱,孙,李,周,吴,郑,王,冯,陈,楮,卫,蒋,沈,韩,杨,朱,秦,尤,许,何,吕,施,张,孔,曹,严,华,金,魏,陶,姜,戚,谢,邹,喻,柏,水,窦,章,云,苏,潘,葛,奚,范,彭,郎,鲁,韦,昌,马,苗,凤,花,方,俞,任,袁,柳,酆,鲍,史,唐,费,廉,岑,薛,雷,贺,倪,汤,滕,殷,罗,毕,郝,邬,安,常乐,于,时,傅,皮,卞,齐,康,伍余,元,卜顾,孟平黄,和,穆,萧,尹,姚,邵,湛,汪,祁,毛,禹,狄,米,贝,明臧,计,伏,成,戴,谈,宋,茅庞,熊,纪,舒,屈,项,祝,董,梁,杜,阮,蓝,闽,席,季,麻,强,贾,路,娄,危江,童,颜,郭,梅,盛,林,刁,锺,徐,丘,骆,高,夏,蔡,田,樊,胡,凌,霍,虞,万,支,柯,昝,管,卢,莫,经,房,裘,缪,干,解,应,宗,丁,宣,贲,邓,郁,单,杭,洪,包,诸,左,石,崔,吉,钮,龚,程,嵇,邢,滑,裴陆,荣,翁,荀,羊,於,惠,甄,麹,家,封,芮,羿,储,靳,汲,邴,糜,松井,段,富,巫,乌,焦,巴,弓,牧,隗,山,谷,车,侯,宓,蓬,全,郗,班,仰,秋,仲,伊,宫,宁,仇,栾,暴,甘,斜,厉,戎,祖,武,符,刘,景,詹,束,龙,叶,幸,司,韶,郜,黎,蓟,薄,印,宿,白,怀,蒲,邰,从,鄂,索,咸,籍,赖,卓,蔺,屠,蒙,池,乔,阴,郁,胥,能,苍,双,闻,莘,党,翟,谭,贡,劳,逄,姬,申,扶,堵,冉,宰,郦,雍,郤,璩,桑,桂,濮,牛,寿,通,边,扈,燕,冀,郏,浦,尚,农,温,别,庄,晏,柴,瞿,阎,充,慕,连,茹,习,宦,艾,鱼,容,向,古,易,慎,戈,廖,庾,终,暨,居,衡,步,都,耿,满,弘,匡,国,文,寇,广,禄,阙,东,欧,殳,沃,利,蔚,越,夔,隆,师,巩,厍,聂,晁,勾,敖,融,冷,訾,辛,阚,那,简,饶,空,曾,毋,沙,乜,养,鞠,须,丰,巢,关,蒯,相,查,后,荆,红,游,竺,权,逑,盖,益,桓,公,万,俟,司马,上官,欧阳,夏侯,诸葛 ,闻人,东方,赫,连,皇甫,尉迟,公羊,澹台,公冶,宗政,濮阳,淳于,单于,太,叔,申,屠,公孙,仲孙,轩辕,令狐,锺,离,宇文,长孙,慕容,鲜,于闾,丘,司徒,司空,丌官,司,寇,仉,督,子车,颛,孙,端木,巫,马,公西,漆雕,乐正,壤驷,公良,拓拔,夹谷,宰,父,谷,梁,晋,楚,阎,法,汝,鄢,涂,钦,段,干,百里,东郭,南门,呼延,归,海,羊,舌,微,生,岳,帅,缑,亢,况,后,有,琴,梁丘,左丘,东门,西门,商,牟,佘,佴,伯,赏,南宫,墨,哈,谯,笪,年,爱,阳,佟'
set @curr=1
set @prev=1
set @count = len(@str)
set @datetime = GETDATE()

--开始事务
begin Transaction
   begin Try

----------------------------------
while @prev < @count
begin
set @curr=charindex(',',@str,@prev)
if @curr>@prev
--------------------------------
while(@i<= 10000)
     begin

   insert into [baiwei].[dbo].[Us]
           ([name]
           ,[Passwords]
           ,[datatime])


        values(
         rtrim(substring(@str,@prev,@curr-@prev))+ CONVERT(char(50), @i),
         -- 'zhang'+CONVERT(char(50), @i),
           CONVERT(char(50), @i)
           ,@datetime)

       set @i=@i + 1


     end

------------------------------------------------
else
begin
while(@i<= 1000)
     begin

INSERT INTO [baiwei].[dbo].[Users]
           ([User_name]
           ,[User_Password])
        values(
         rtrim(substring(@str,@prev,len(@str)-@prev+1)) + CONVERT(char(50), @i),
         -- 'zhang'+CONVERT(char(50), @i),
           CONVERT(char(50), @i))

       set @i=@i + 1
      end
--事务提交
break
end
set @i=1
set @prev=@curr+1
end
-----------------------------------
    commit Tran

   end Try
   begin Catch
      --事务回滚
    rollback Tran

    declare @sTemp varchar(1000)
    select @sTemp=ERROR_MESSAGE()
    raiserror(@sTemp,16,1)

   end Catch

select * from Us where name='赵1'
select * from Us where name like '赵%'

use baiwei
go
create nonclustered index [index_UserInfo] on [dbo].[Us]
(
name ASC
)

drop index [index_UserInfo] on [dbo].[Us]

存储过程分页
USE bawei
GO

/****** Object: StoredProcedure [dbo].[UP_GetRecordByPage] Script Date: 01/01/2007 00:50:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create procedure drop_proc
@tblName      varchar(255),       -- 表名
@fldName      varchar(255),       -- 主键字段名
@OrderfldName   varchar(255)='',   -- 排序字段
@PageSize     int = 10,           -- 页尺寸
@PageIndex    int = 1,            -- 页码
@OrderType    bit = 1,            -- 设置排序类型, 非值则降序(0为desc降序 1为asc升序)
@IsReCount    bit = 0,            -- 返回记录总数, 非值则返回(1为返回)
@strWhere     varchar(1000) = '' -- 查询条件(注意: 不要加where)( and Price>2000)
AS

declare @strSQL   varchar(6000)       -- 主语句
declare @strTmp   varchar(100)        -- 临时变量
declare @strOrder varchar(400)        -- 排序类型

--判断你是否提供了，排序字段
if(@OrderfldName!='')
begin
if(@OrderType!=0)
begin
  --拼接字符串
  set @strOrder=' order by ['+@OrderfldName+'] asc'
end
else
begin
  set @strOrder=' order by ['+@OrderfldName+'] desc'
end
end
else
begin
set @strOrder=' '
end

--是否求记录数
if(@IsReCount!=0)
begin
--是否有条件
if(@strWhere!='')
begin
  set @strTmp='select Count(1) from ['+@tblName+'] where 1=1 '+ @strWhere
end
else
  set @strTmp='select Count(1) from ['+@tblName+'] where 1=1 '
end
else
--求第10到第20条数据
set @strTmp=' '
if(@IsReCount=0)
begin
set @strSQl='Select top '+str(@PageSize)+' * from ['+@tblName+'] where ['
    +@fldName+'] not in (select top '+str((@PageIndex-1)*@PageSize)+' ['
    +@fldName+'] from ['+@tblName+'] where 1=1 '
    + @strWhere+@strOrder+') '+@strWhere+@strOrder+';'
end
else
begin
set @strSQl = @strTmp
end