T-sql 基础（全）

--检查约束，选中，右键，CHECK约束,添加,标志起名
--表达式，EmpAge>=0 and EmpAge<=150

--唯一约束
--选中,右键,索引,键
--添加, 类型唯一键
--列 字段名
--标志，UQ- +字段名

--默认约束

--下面属性
--默认值或绑定

--清除表数据
--truncate table dbo.tblStudent

----约束长度
--LEN（长度）=11

--datetime 默认约束
--('2012-07-10')
--需要设置字符串类型

--获取当前时间
--GETDATE()

--在默认约束里写上、GETDATE()

--死锁问题，编辑和设计要分开，不然会出现死锁。

--T-SQL 实现约束
--1.创建表的时候就创建约束
create table T_CreateConstraint
(
id int primary key,--主键约束
name nvarchar(10) unique, --唯一约束
age int check(age>=0 and age<=100),--检查约束
jiondate datetime default(getdate()) --默认约束
)

--2.创建表以后修改表创建约束
create table T_CreateConstraint2
(
id int ,--not null,
name nvarchar(10) ,
age int ,
jiondate datetime
)
--凡是修改表语法为alter table 表名
--添加检查约束
alter table T_CreateConstraint2
add constraint
CK_CreateConstraint2_age
check
(
age>=0
and age<=100
)
--添加主键约束
alter table T_CreateConstraint2
add constraint
PK_CreateConstraint2_id
primary key(id)

--添加唯一约束
alter table T_CreateConstraint2
add constraint
UQ_CreateConstraint2_name
unique(name)

--添加默认约束

alter table T_CreateConstraint2
add constraint
DF_CreateConstraint2_joinDate
default (getdate()) for jionDate

--alter table T_CreateConstraint2
--add constraint
--后面的约束用逗号隔开

--如何查看已有约束
--select * from sys.objects

--if(OBJECT_ID('对象名','对象类型'))is not null

--外键
1.用设计器
任何地方点右键
关系
表和列规范

--删除主键表里面的数据时要保证外键表没有对这个数据的引用
2.用T-SQL
先创建主键表
create table MainTable
(
Fid int primary key,
name nvarchar(10)
)
--创建外键表
create table ForTable
(
id int primary key,
Fid int foreign key references MainTable(Fid)
)

--创建表以后修改添加外键约束
create table MainTable1
(
Fid int primary key,
name nvarchar(10)
)
create table ForTabe1
(
id int primary key,
Fid int
)

alter table ForTabe1
add constraint
FK_ForTable1_MainTable1_Fid
foreign key(Fid) references MainTable1(Fid)

create table MyTable
(
id int identity(1,1) primary key
name nvarchar(10)
)
--查询
select * from dbo.班级表
--需要对数据进行检索
select * from dbo.班级表 where
--添加列的别名
--1.列as 别名
--2.列 别名（不建议用）
--3.别名=列

--select有一个特殊功能，就是显示数据 很重要，就是在select里面加上一行，这行并不会改变数据库的原始数据

top 获取前几条数据，放在列前面，常与order by一起使用
select top (3)

--print（打印）也可以显示数据只是显示的地方不同，是显示在（消息）里。
--添加某个类型的排序，使用order by+字段名
group by将某个字段分组
--group by中有的字段，才能出现在select语句中
--group by对数据进行分组，但是应该注意分组的数据才允许select检索
--group by应写在from后面，但写在order by 的前面，否则会出差
--在后面标注asc和desc表示升序和降序

--distinct去除重复的数据
select distinct * from 表名
--使用distinct子句有个注意事项，distinct是对整个结果集进行重复处理，不是针对某一列
--针对某一列去除重复，索引数据
--select distinct（需要去除的字段），其他字段 from 表名
select * from dbo.学生表 order by 年龄 desc

--聚合函数
--返回一个数字，这个数往往是sum、avg、max、min、count检索出来的数
--求和sum、求平均avg、求最大max最小min、求总数sount
--聚合函数使用常常与分组一起使用：
语法：group by 字段
--将员工姓名输出出来，并显示出现的次数
select
EMPname 员工姓名，COUNT(EMPname) from dbo.tblEMPloyee group by EMPname
--注意：在处理聚合数据的时候，不计入null空值数据，在SQL Server 中null表示未知

--范围条件和集合条件

--使用between and表示条件范围 可以用or将范围条件连接起来
--字段 between 数值1 and 数值2 范围的精度高
--in（数值）

--模糊查询 有两个
--_单个字符
--%零个或多个字符
--使用like（像）
--匹配有歧义的字符的时候要使用[]将其括起来
--在方括号中[^]也可以表示否定，就是表示不出现这个元素的 两种： name like '[^赵]%' name not like '赵%'

--简单总结一个条件一些内容
--在SLQ中，使用三值逻辑（true，false，unknow）
-->,<,>=,<=,<>,SQL server中提供的:（!=）（!>）（!<）
--由于null的存在必须对其处理
查询所有生日为null的姓名->select Fname from T_studentFbirthday is null
--判断null只能使用is或in not isnull is not null --函数isnull（字段名，替换值）
查询时专门为空值的字段显示处理 select is null（字段，123） from 表名
--空字符串与null含义不同，空字符串依旧有内容 处理空字符串可以使用len()函数和is null

--在查出的数据中二次筛选having
where是进入数据库的第一次筛选，而having是第二次筛选，where后面不能跟聚合函数 有select中的数据进行筛选，需要放在having子句里面

--将多张表合并成为一张表union就叫联合结果集
--union 就是将多个结果集合并成一个结果集
--查询语句1 union 查询语句2
--union会自动合并重复的数据、而union all会保留重复数据，一般都是使用union all，效率会高点
--联合需要注意类型一致 cast()函数可以实现数据的类型转换
select cast(1234 as varchar(4)),'123'
union
select 'xixi','123'
convert()函数也可以实现类型转换的功能
select convert(varchar(4),1234),'hahaha'
union
select 'xixi','hahaha'

************************
字符串函数：
pId, pTypeId, pName, pCellPhone, pHomePhone

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)

select * from [user]

--1 case end
select uid,name,
case level
when 1 then '骨灰'
when 2 then '大虾'
when 3 then '菜鸟'
else '0'
end as level
from [user]

--2 case end
select studentId,
case
when english between 90 and 100 then '优'
when english between 80 and 89 then '良'
when english between 70 and 79 then '中'
when english between 60 and 69 then '可'
else '差'
end as '成绩'
from Score

--练习1 3列变两列
select a,b,c from biao

select
case
when A>b then a
else b
end,
case
when b>c then b
else c
end

from biao

--练习2 2列变3列
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 * from test

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

--练习3

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,
sum(case subject
when '语文' then result
end) as '语文',
sum(case subject
when '数学' then result
end) as '数学',
isnull(sum(case subject
when '物理' then result
end),0) as '物理'
from student0
group by name

---------------------
--子查询 嵌套子查询
select * from
(select sId,sName from student where sage<20) as t

--
select (select MAX(english) from Score) as '最高成绩',
(select MIN(english) from Score) as '最低成绩',
(select Convert(numeric(10,2),AVG(english)) from Score) as '平均成绩'

select '姓名',(select MAX(sAge) from student)

--查询高二一班所有的学生
select * from student
select * from Class
--查找高二一班所有的学生
select * from student where sClassId =
(select cid from Class where cName='高二一班')

--查找高一一班 高二一班 高三一班所有的学生
--子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后，或子查询用作表达式时，这种情况是不允许的。
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 ('刘备','关羽','张飞'))

--删除刘关张的成绩
delete from Score where studentId in
(select sId from student where sName in ('刘备','关羽','张飞'))

select * from Score

-----子查询 相关子查询
--平均年龄小于23的班级的最大年龄和最小年龄

select MAX(sage),MIN(sage),AVG(sAge) from student as s1
where 23 > (select AVG(sAge) from student as s2 where s1.sClassId = s2.sClassId)

select MAX(sage),MIN(sage),AVG(sAge) from student
group by sClassId having AVG(sAge) < 23

 

--分页
select * from student

--第一页的三条
select top 3 * from student
where sId not in (select top 0 sId from student)

--第二页
select top 3 * from student
where sId not in (select top 3 sId from student)
--第三页
select top 3 * from student
where sId not in (select top 6 sId from student)

select * from student
--排序，子查询的排序顺序应该和外面查询排序顺序一致
select top 3 * from student
where sId not in (select top (3*(2-1)) sid from student order by sAge)
order by sage desc

 

--计算总页数
select ceiling(COUNT(*)/3.0) from student

--row_number

--第一页
select * from
(select *,ROW_NUMBER() over(order by sid desc) as num from student) as t
where num between 1 and 3
order by sId desc
--第二页
select * from
(select *,ROW_NUMBER() over(order by sid desc) as num from student) as t
where num between 4 and 6
order by sId desc
--第三页
select * from
(select *,ROW_NUMBER() over(order by sid desc) as num from student) as t
where num between 7 and 9
order by sId desc

 

--第n页的数据 每页3条数据
select * from
(select *,ROW_NUMBER() over(order by sid desc) as num from student) as t
where num between (n-1)*3+1 and n*3
order by sId desc

 

--表连接
--cross join 交叉连接
select * from student cross join Class

--inner join..on .. 内连接

--案例1：查询所有学生的姓名、年龄及所在班级
select sName,sAge,cName from student inner join Class
on student.sClassId = Class.cId

--案例2：查询年龄超过20岁的学生的姓名、年龄及所在班级
select sName,sAge,cname from student inner join Class
on student.sClassId=Class.cId
where sAge > 20
order by sAge

--案例3：查询学生姓名、年龄、班级及成绩
select sName,sAge,cName,english from student inner join Class
on student.sClassId = Class.cId
inner join Score on score.studentId=student.sId

select * from Score

--案例4：查询所有学生(参加及未参加考试的都算)及成绩
select sName,sAge,isnull(english,0) from Score right join student
on student.sId = Score.studentId

--练习1：查询所有及格的学生姓名、年龄及成绩
select sName,sAge,english from student inner join Score
on student.sId=Score.studentId
where english>=60

--练习2：查询所有参加考试的(分数不为null)学生姓名、年龄及成绩
select sName,sAge,math from student inner join Score
on student.sId=Score.studentId
where math is not null
--练习3：查询所有学生(参加和未参加考试)的学生姓名、年龄、成绩，如果没有参加考试显示缺考,如果小于60分显示不及格
select sname,sage,
case
when math is null then '缺考'
when math < 60 then '不及格'
else CONVERT(varchar(5),math)
end
from student left join Score
on student.sId=Score.studentId

 

 

----视图

--系统视图
select * from INFORMATION_SCHEMA.TABLES

select * from INFORMATION_SCHEMA.COLUMNS

select * from sys.databases

select * from sys.objects

use master
if exists(select * from sys.databases where name='test')
drop database test
go
create database test
go
use test
go
if exists(select * from sys.objects where name='tt')
drop table tt
create table tt
(
id int
)
go
if exists(select * from sys.objects where name='UQ_id')
alter table tt drop constraint UQ_id
go
alter table tt
add constraint UQ_id unique (id)

--用户自定义视图
use MySchool

select * from v_student

if exists(select * from sys.objects where name='v_Student0')
drop view v_student0
go
create view v_Student0
as
select sName,sAge from student where sAge > 20

 

create view v_Score
as
select sname,sage,
case
when math is null then '缺考'
when math < 60 then '不及格'
else CONVERT(varchar(5),math)
end as 'math'
from student left join Score
on student.sId=Score.studentId

select * from v_Score

 

--局部变量
--1声明变量
declare @name nvarchar(30)
declare @age int

declare @name nvarchar(30),@age int

--2给变量赋值

--set 只能给一个变量赋值
--set @name='张三'
--set @age = 18

--select 可以同时给多个变量赋值
select @name='张三',@age=18
--在查询语句中对变量赋值
select @age=AVG(sAge) from student
select @age=sage from student where sName=@name

--3输出变量的值
--print 一次只能输出一个变量的值，输出到消息窗口
print @name
print @age

select @name,@age
--select @age

--全局变量（系统变量）
select @@SERVERNAME
select @@VERSION
select @@LANGUAGE

--上一次 最大编号
insert into Class (cName,cDescription) values ('高二三班11','慢班')
select @@IDENTITY

--上一次 错误号
insert into Class (cName,cDescription) values ('高二三班11','慢班')
select @@ERROR
select * from Class
--上一次 受影响的行数
insert into Class (cName,cDescription) values ('高二三班11','慢班')
select @@ROWCOUNT

 

--if else

--计算平均分数并输出，如果平均分数超过60分输出成绩最高的三个学生的成绩，否则输出后三名的学生

declare @score float
select @score=avg(english) from Score
select '平均成绩：'+ CONVERT(varchar, @score)

if(@score >= 60)
begin
print '前三名'
select top 3 * from Score order by english desc
end
else
begin
print '后三名'
select top 3 * from Score order by english asc
end

--循环 while
--如果不及格的人超过半数(考试题出难了)，则给每个人增加2分

declare @count int,@failCount int,@i int
set @i = 0
select @count=COUNT(*) from Score
select @failCount=COUNT(*) from Score where english<60
--不及格的人数超过总人数的一班。循环加分
while(@failCount > @count/2)
begin
update Score set english = english + 2
--加分后，重新计算不及格人数
select @failCount=COUNT(*) from Score where english<60
set @i = @i + 1
end
update Score set english = 100 where english >100

select @i

select * from Score

truncate table score
insert into Score (studentId,english,math) values(1,10,87)
insert into Score (studentId,english,math) values(2,20,90)
insert into Score (studentId,english,math) values(3,59,60)
insert into Score (studentId,english,math) values(4,100,50)
insert into Score (studentId,english,math) values(5,30,100)
insert into Score (studentId,english,math) values(6,0,null)
insert into Score (studentId,english,math) values(7,20,null)

--把所有未及格的人的成绩都加及格
declare @i int
set @i = 0
while(1=1)
begin
set @i = @i + 1
update Score set english = english + 2
declare @fail int
select @fail = COUNT(*) from Score where english<60
if(@fail = 0)
break
end
update Score set english = 100 where english>100

select @i

select * from Score

 

--事务
create table bank
(
cId char(4) primary key,
balance money, --余额
)

alter table bank
add constraint CH_balance check(balance >=10)

go
--delete from bank
insert into bank values('0001',1000)
insert into bank values('0002',10)
go

 

select * from bank

update bank set balance=balance - 1000 where cId='0001'
update bank set balance=balance + 1000 where cId='0002'

 

--事务
declare @sumError int

set @sumError = 0

-- begin transaction
begin tran
update bank set balance=balance - 1000 where cId='0001'
set @sumError = @sumError + @@ERROR
update bank set balance=balance + 1000 where cId='0002'
set @sumError = @sumError + @@ERROR

if(@sumError = 0)
--执行成功，提交事务
commit tran
else
--执行失败，回滚事务
rollback tran

select * from bank

 

--存储过程
--store procedure

exec sp_databases
exec sp_helpdb myschool
exec sp_tables

exec sp_helptext sp_adddatatype

--附加数据库
exec sp_attach_db @dbname='DataMng',@filename1='c:\DataManager_data.mdf',@filename2='c:\DataManager_log.ldf'
--分离数据库
exec sp_detach_db 'DataManager'

 

--自定义存储过程
--大部分学生不及格，提分，直到一半学生及格为止

--不带参数的存储过程
if exists(select * from sys.objects where name='usp_upGrade')
drop proc usp_upGrade
go
create proc usp_upGrade
as
declare @count int,@failCount int,@i int
select @count=COUNT(*) from Score
select @failCount=COUNT(*) from Score where english<60
--不及格的人数超过总人数的一班。循环加分
while(@failCount > @count/2)
begin
update Score set english = english + 2
--加分后，重新计算不及格人数
select @failCount=COUNT(*) from Score where english<60
end
update Score set english = 100 where english >100
go

exec usp_upGrade
select * from Score

--带参数的存储过程
--考试题出难了，降低及格分数线
if exists(select * from sys.objects where name='usp_upGrade')
drop proc usp_upGrade
go
create proc usp_upGrade
@passEnglish int,@passMath int
as
declare @countEn int ,@countMath int
select @countEn=COUNT(*) from Score where english<@passEnglish
select @countMath=COUNT(*) from Score where math<@passMath
select '英语不及格人数',@countEn
select '数学不及格人数',@countMath

exec usp_upGrade 60,60

--带默认值参数的存储过程
if exists(select * from sys.objects where name='usp_upGrade')
drop proc usp_upGrade
go
create proc usp_upGrade
@passEnglish int=60,@passMath int =60
as
declare @countEn int ,@countMath int
select @countEn=COUNT(*) from Score where english<@passEnglish
select @countMath=COUNT(*) from Score where math<@passMath
select '英语不及格人数',@countEn
select '数学不及格人数',@countMath

exec usp_upGrade
exec usp_upGrade 70,70
--英语及格分数线70 数学默认60
exec usp_upGrade 70
--英语及格分数线60 数学默认70
exec usp_upGrade 60,70
--指明给某个参数赋值
exec usp_upGrade @passMath=70
--
exec usp_upGrade @passEnglish=70,@passMath=70

--带输出参数的存储过程
if exists(select * from sys.objects where name='usp_upGrade')
drop proc usp_upGrade
go
create proc usp_upGrade

@countEn int output, --英语不及格人数
@countMath int output, --数学不及格人数
@passEnglish int=60,
@passMath int =60
as
select @countEn=COUNT(*) from Score where english<@passEnglish
select @countMath=COUNT(*) from Score where math<@passMath

declare @en int,@math int
exec usp_upGrade @countEn= @en output,@countMath=@math output
select @en
select @math

declare @en int,@math int
exec usp_upGrade @en output,@math output
select @en
select @math

--带返回值的存储过程
if exists(select * from sys.objects where name='usp_upGrade')
drop proc usp_upGrade
go
create proc usp_upGrade
@countEn int output, --英语不及格人数
@countMath int output, --数学不及格人数
@passEnglish int=60,
@passMath int =60
as
select @countEn=COUNT(*) from Score where english<@passEnglish
select @countMath=COUNT(*) from Score where math<@passMath
declare @count int
select @count = COUNT(*) from Score
return @count

declare @en int,@math int,@count int
exec @count = usp_upGrade @en output,@math output
select @en
select @math
select @count

select * from user where uloginname='' and upwd=''

 

--分页存储过程
create proc use_pro
@pageindex int,
@pagesize int,
@pagecount int output
as
begin
declare @num int
select @num= COUNT(*) from Student
set @pagecount=CEILING(@num*1.0/@pagesize)
select * from
(select * ,ROW_NUMBER() over(order by sid) as num from Student) as t
where num between (@pageindex-1)*@pagesize+1 and @pageindex*@pagesize
order by sid
end

declare @n int
exec use_pro 2,3,@n output