sql 中的一些基础知识

触发器

ALTER trigger [dbo].[updateMoney]
on [dbo].[ldjl]
for insert
as

 

2 事物

--事务
declare @sumError int
set @sumError = 0
begin tran --transaction
update bank set balance = balance + 1000 where cId = '0002'
set @sumError = @sumError + @@error
update bank set balance = balance - 1000 where cid='0001'
set @sumError = @sumError + @@error
--事务执行成功 提交事务
if(@sumError = 0)
commit tran
--事务执行失败，回滚
else
rollback tran

 

1

 

 

SQL中的一个分割字符串函数

ALTER功能[DBO] [f_split]（@Ç为varchar（2000），@拆分为varchar（2））  
返回@ T台（COL为varchar（20），ID整数标识）  
作为  
    开始         ，而（CHARINDEX（@分裂，@Ç ）<> 0）           开始             插入@ T（COL）的值（子串（@ C，1，CHARINDEX（@分裂，@ C）-1））             SET @ C =东西（@ C，1，CHARINDEX（@分裂，@三），''）           结束         插入@ T（COL）的值（@ C）         返回       结束
   

---选择@ US1 =山坳从f_split（'288810 / 555555/9999/66666/2222/1141/589/111111'，'/'）其中id = 1

--drop database MySchool
create database MySchool
on
(
    name='MySchool',
    filename='c:\database\MySchool.mdf',
    size=3,
    filegrowth=10%,
    maxsize=100
)
log on
(
    name='MySchool_log',
    filename='c:\database\MySchool_log.ldf',
    size=3,
    filegrowth=1,
    maxsize=10
)
go
--修改
--alter database

--切换数据库
use MySchool
go
--创建表
create table [Class]
(
    --identity(1,1)自动编号，第一个参数种子，第二个参数增长量
    --primary key设置主键
    --not null该字段不能为空
    [cId] int identity(1,1) primary key,
    [cName] nvarchar(10) not null,
    [cDescription] nvarchar(200)
)
go
--drop table [Student]
create table [Student]
(
    [sId] int identity(1,1),
    [sName] nvarchar(10) not null,
    [sAge] int null,
    [sNo] decimal(18,0),
    [sBirthday] datetime,
    [sClassId] int not null
)
go
--修改表   添加列
alter table Student
add sSex nchar(1)


--drop table score
create table Score
(
    sId int identity(1,1),
    studentId int not null,   --学生id，外键
    english float,
    math float
)
go
--drop table score
create table teacher
(
    tId int identity(1,1) primary key,
    tName nvarchar(50) not null,
    tSex nchar(1),
    tAge int,
    tSalary money
)
go



--插入数据
insert into [Class](cName,[cDescription]) values('高一一班','快班')

insert [Class] values('高一二班','中班')

--错误  当省略列明，要求必须输入所有列的值
--insert [Class] values('高一三班')

insert into [Class](cName) values('高一三班')

select * from [class]

--日期必须加单引号
INSERT into [Student](sSex,sName, sAge, sNo, sBirthday, sClassId)
values('狗','张三',-18,12222211,'1982-1-1',1)

INSERT into [Student](sSex,sName, sAge, sNo, sBirthday, sClassId)
values('男','李四',18,122222,'1982-1-1',1)
INSERT into [Student](sSEx,sName, sAge, sNo, sBirthday, sClassId)
values('男','王五',18,122222,'1982-1-1',1)

select * from student


--修改数据
update student set sSex='男'

update Student set sSex='狗',sAge=20

update Student set sClassId=2 where sName='王五'


update STudent set sClassId=10
where sAge = 50 or  (sAge >=19 and sAge<=20)


update Student set sAge = sAge + 1
select * from student


select * from score

update score set english = english / 2



--删除数据
select * from student

delete from student
delete from student where sName='张飞'
delete from student where sId=23

truncate table student


--增删改练习
--插入 几条老师信息 和成绩
--练习1：给刘备的英语成绩加10分
select * from score
update score set english = english + 10 where studentId=1
--练习2：考试题偏难，所有人的成绩加5分
update score set english = english + 5
--练习3：所有女学生的年龄加-1
select * from student
update student set sAge = sAge + 1 where sSex='女'

--删除工资大于2000的老师
delete from teacher where tSalary > 2000

--删除所有老师
delete from teacher
--删除数据时候  把自增长列的值还原成种子
truncate table teacher



--约束

--主键约束
alter table Student
add constraint PK_Student primary key(sId)

--唯一约束
alter table Student
add constraint UQ_Student_sNo unique(sNo)

--默认约束
alter table Student
add constraint DF_Student_sSex default('男') for sSex


insert into student(sName,sAge,sClassId) values('123',18,1)
insert into student(sName,sClassId,sNo) values('123',1,2)

insert into student(sName,sAge,sSex,sClassId,sNo) values('123',default,default,5,5)
select * from student


--检查约束
alter table Student
add constraint CK_Student_sSex check (sSex='男' or sSex='女')

alter table Student
add constraint CK_Student_sAge check (sAge >=20)

alter table Student
add constraint CK_Student_date check (sIntime>sBirthday)


insert into student(sName,sAge,sClassId,sNo) values('123',16,1,10)

--删除约束
alter table Student
drop constraint CK_Student_sAge


--外键约束
alter table Student
drop constraint FK_Student_sClassId

alter table Student
add constraint FK_Student_sClassId foreign key (sClassId) references Class(cId)
--级联删除
--on delete cascade on update cascade


--往子表加数据，外键的值必须在主表中出现
insert into student(sName,sNo,sClassId) values('abc',111,100)
--删除主表中数据的时候，必须先删除子表中对应的数据
delete from class where cId = 1
select * from class
select * from student



--约束练习
--Teacher表中
--tSex  控制只能是男 女，默认男
--tAge  在30-40之间  默认30
--tName 唯一
alter table Teacher
add constraint CK_Teacher_tSex check(tSex='男' or tSex='女'),
constraint DF_Teacher_tSex default ('男') for tSex,
constraint CK_Teacher_tAge check(tAge>=30 and tAge <=40),
constraint DF_Teacher_tAge default (30) for tAge,
constraint UQ_Teacher_tName unique (tName)

--Score表中
--studentId 是外键    先要把Student表中的sId设置为主键
alter table Score
add constraint FK_Score_studentId foreign key (studentId) references Student(sId)

----在学生表中删除有成绩的学生
----成绩表中添加 学生表中没有的学生


--drop table Student0
create table Student0
(
    sId int identity(1,1) primary key,
    sName nvarchar(10) not null,
    sAge int constraint CK_Student0_sAge check(sAge >= 18) constraint DF_Student0_sAge default(18),
    sClassId int constraint FK_Student0_sClassId foreign key (sClassId) references Class(cId)
)


alter table Student0
add sSex nchar(1)

alter table Student0
drop column sSex


２
create database Company
go
use company
go
create table department
(
    dId int identity(1,1) primary key,
    dName nvarchar(50)
)
create table employee
(
    eId int identity(1,1) primary key,
    eName nvarchar(50) not null,
    eSex nchar(1),
    eAge int,
    eSalary money,
    eDepId int not null
)

alter table employee
add constraint FK_employee foreign key (eDepId) references department(dId)
--级联删除
--on delete cascade


insert into department values('财务部')
insert into department values('开发部')
insert into department values('后勤部')

insert into employee(eName,eSex,eAge,eSalary,eDepId)
values ('淡定哥','男',18,3000,2)
insert into employee(eName,eSex,eAge,eSalary,eDepId)
values ('小月月','女',20,4000,1)
insert into employee(eName,eSex,eAge,eSalary,eDepId)
values ('马户','男',18,3500,1)
insert into employee(eName,eSex,eAge,eSalary,eDepId)
values ('凤姐','女',21,2500,2)
insert into employee(eName,eSex,eAge,eSalary,eDepId)
values ('凤,姐','女',21,2500,2)


--1、查询所有员工
--2、查询工资超过2000快钱的员工
--3、查询最新进来的5个员工
--4、查询员工的平均工资，最高工资
--5、查询总共有多少员工
--6、查询每个部门有多少员工
--7、查询每个部门的平均工资
    select avg(eSalary),eDepId from employee
    group by eDepId
--8、查询每个部门男员工的平均工资
    select avg(eSalary),eDepId from employee
    where eSex = '男'
    group by eDepId
--9、查询平均工资超过2000的那些部门
    select avg(eSalary),eDepId from employee
    group by eDepId
    having avg(eSalary)   > 2000

３
--字符串函数
select len('213')

select len(sName),sName from student

select lower('adfCE')
select upper('asdf23')

select '    abc   '
select ltrim('  abc  ')
select rtrim('  abc  ') + '123'
select ltrim(rtrim('   abc   ')) + '123'

--
select left('张三',1)
--ip1551616414

select right('ip1551616414',len('ip1551616414')-2) + 1
--起始位置从1开始
select substring('ip1551616414',1,2)

--查询班级内所有学生的姓
select distinct left(sName,1) from student



--日期函数
select getdate()

select dateadd(day,3,getdate())

select dateadd(month,-1,getdate())

select dateadd(year,-1,getdate())

select dateadd(quarter,1,getdate());
select dateadd(week,1,getdate());


select year(getdate())
select month(getdate())
select day(getdate())

--当前月的销售记录
select * from xsjl
where month(date) = month(getdate()) and year(date) = year(getdate())

--datediff
select datediff(day,getdate(),'2012-12-21')
select datediff(second,getdate(),'2012-12-21')

--求当月销售记录
select * from xsjl
where datediff(month,date,getdate())=0

--统计不同生日年份的学生个数
select count(*),year(sBirthday)
from student
group by year(sBirthday)

--求每个年龄有多少个学生
select DateDiff(year,sBirthday,getdate())
,count(*) from student Group by DateDiff(year,sBirthday,getdate())


--datename datepart
select datename(year,getdate()) --字符串
select datepart(year,getdate()) --整数

--一年中的第几天
select datename(dayofyear,getdate())
--一年中的第几周
select datename(week,getdate())

select datename(weekday,getdate())
select datepart(weekday,getdate())



--类型转换
--问题
select '平均分数' + 123
select avg(english) from score
select * from student


select '平均分数' + cast(23.56 as varchar(5))
select cast(right(sNo,3) as int)+1 from student


--不4舍5入
select cast (89.6 as int)
--4舍5入
select cast (round(89.6,0) as int)

select cast(89.239345 as decimal(10,2))
select cast(avg(english) as decimal(10,2)) from score

--convert
select convert(int,89.63934)
select convert(decimal(10,2),89.64789)
select convert(decimal(10,2),avg(english)) from score


select convert(varchar(30),getdate(),20)
select convert(varchar(20),getdate(),21)
select convert(varchar(30),getdate(),23)

select convert(varchar(10),getdate(),108)

select convert(varchar(10),getdate(),20)
select sId, sName, sAge, sNo, convert(varchar(10),sBirthday,20), sClassId, sSex, convert(varchar(10),sIntime,108), sPhone
from student


--isnull
select * from score

select studentId,isnull(english,0)
from score

select avg(english) from score
select sum(english)/count(sId) from score




select cast(avg(isnull(english,0)) as decimal(10,2)) from score


--输出所有数据中通话时间最长的5条记录。orderby datediff
--输出所有数据中拨打长途号码（对方号码以0开头）的总时长。like、sum
--输出本月通话总时长最多的前三个呼叫员的编号。
--
--输出本月拨打电话次数最多的前三个呼叫员的编号.group by,count(*)
４
use MySchool


--简单查询
select * from student

select sId, sName, sAge, sNo, sBirthday, sClassId, sSex, sIntime from student

select sName from student

--给列改名
select sName as '姓名',sAge as '年龄' from student
select sName '姓名',sAge '年龄' from student
select '姓名'=sName,'年龄'=sAge from student

select sName as '姓名',sAge as '年龄',sSex as '性别'
from student where sSEx='女'

select sName as '姓名',sAge as '年龄',sSex as '性别'
from student where sAge > 20

select 2*3
select getdate()


--Top Distinct
select top 2 sName,sAge,sSex from student
order by sAge

--百分之10个学生   如果有小数位数，直接进1
select top 10 percent sName,sAge,sSex
from student

select distinct * from student

select distinct sName from student
select distinct sName,sSex,sAge from student


--聚合函数max min avg sum count
select max(english) from score
select min(english) from score
--null值不参加avg的运算
select avg(english) from score
select sum(english)/count(*) from score

select sum(english) from score
select count(sId) from student

select count(*) from student where sSex='女'

select max(english) as '最大值',min(english) as '最小值',avg(english) as '平均值'
from score


select max(sBirthday),min(sBirthday) from student

select * from student
select * from score

--带条件查询
select studentId from score where english >= 60

select sName,sAge,sSex from student
where sAge >=20 and sAge<=30 and sSex='男'

--between....and ....
select sName,sAge,sSex from student
where sAge between 20 and 30 and sSex='男'

select * from student
where sBirthday between '1988-1-1' and '1989-12-31'

select * from student
select * from score where english between 80 and 90
--in (1,2,3)
select * from student
where sClassId = 1 or sClassId=4 or sClassId = 3

select * from student
where sClassId in (1,2,4)

--模糊查询

select left('123',1)
select * from student where left(sName,1)='张'

select * from student where sName like '张%'
select * from student where sName like '%亮%'

select * from student where sName like '张__'

select * from student where sName like '张[飞亮]%'

alter table student
add constraint CK_Student_sPhone check (sPhone like '[0-9][0-9][0-9][0-9][0-9][0-9]')


insert into Student (sClassId,sName,sAge,sSex,sNo,sBirthday,sPhone) values (4,'诸葛亮',20,'男',22315678801234561,'1989-8-8','123456')

--null
select null + 123
select * from student where sPhone is null
select * from score where english is null
select * from score where english  is not null


--order by
select top 2 * from student
order by sAge desc

select * from student
order by sName desc

select * from score
order by english asc,math desc

select * from student
where sSex='男'
order by sAge desc


--group by sClassId
--分组就是把sClassId相同值的那些行，合并为一行
--当有“每个”的含义时候使用分组

--每个班有多少个学生

--第一个问题  使用group by后，select之后的列必须出现在group by子句中或者聚合函数中
select count(*),sClassId from student
group by sClassId

select count(sId) as '个数',sClassId from student
where sSex='男'
group by sClassId

select count(sId),sClassId,avg(sAge)
from student
group by sClassId

--聚合函数不能出现在where子句中
--错误
select count(sId),sClassId,avg(sAge)
from student
where avg(sAge) > 20
group by sClassId

--求平均年龄超过20岁的那些班
--having 对分组过后的数据进行筛选（和where的作用不用）
select sClassId,avg(sAge)
from student
group by sClassId
having avg(sAge) > 20

--求人数超过5个的那些班
select sClassId,count(sId)
from student
group by sClassId
having count(sId) > 5

--分组练习
--求男生和女生分别有多少人
select count(sId),sSex from student
group by sSex
--求每个班有多少男生
select count(sId),sClassId from student
where sSex='男'
group by sClassid
--每个班中的男同学的平均年龄
select count(sId),sClassId,avg(sAge) from student
where sSex='男'
group by sClassid

--求平均年龄小于22的那些班
select count(sId),sClassId,avg(sAge) from student
group by sClassid
having avg(sAge) < 22


--union
--列的个数一致，类型一致
--排序  去除重复数据
select tName,tSex,-1 from teacher union
select sName,sSex,sClassId from student

--union all
--直接连接两个结果集
select tName,tSex from teacher union all
select sName,sSex from student

--要求在一个表格中查询出学生的英语最高成绩、最低成绩、平均成绩
select '最高成绩',max(english) from score union all
select '最低成绩',min(english) from score union all
select '平均成绩',avg(english) from score


--查询每位老师的信息，包括姓名、工资，并且在最后加上平均工资和最高工资
select tName,tSalary from teacher union all
select '平均工资',avg(tSalary) from teacher union all
select '最高工资',max(tSalary) from teacher

--一次插入多条数据
insert into Score(studentId,english,math)
select 1,80,100 union
select 2,60,80 union
select 3,50,59 union
select 4,66,89 union
select 5,59,100

select * from score

--把现有表中的数据插入到新表中(newStudent表不能存在)
--去除表中重复数据
select * into newStudent from student
select * from newStudent

--把现有表的数据复制到一个已存在的表
truncate table newStudent
insert into newStudent select sName, sAge, sNo, sBirthday, sClassId, sSex, sIntime, sPhone from student


truncate table score
truncate table student
truncate table class







５
 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


case when then end

CREATE TABLE student0 (name nvarchar(10),subject nvarchar(10),result int)
INSERT INTO student0 VALUES ('王','语文',null)
INSERT INTO student0 VALUES ('王','数学',90)
INSERT INTO student0 VALUES ('王','物理',85)
INSERT INTO student0 VALUES ('yang','语文',85)
INSERT INTO student0 VALUES ('yang','数学',92)
INSERT INTO student0 VALUES ('yang','物理',null)

select * from student0
truncate table student0
select name as '姓名',
    sum(case subject --case  判断的是列名吗
        when  '语文' then result else 0 --when 列的值是多少的时候 then 返回一个值  else 否则 就 怎么样 最后 end 结束
    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

delete from score where studentId in
(select sId from student where sName in ('刘备','关羽','张飞'))


  --查询高一一班 高二一班所有的学生
--子查询返回的值不止一个。当子查询跟随在=、!=、<、<=、>、>= 之后
--子查询跟在比较运算符之后，要求子查询只返回一个值
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 ('刘备','关羽','张飞'))

select * from student

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


  --最近入学的个学生
select top 3 * from student
order by sId desc

--查询第到个学生
select top 3 * from student
where sId not in (select top 3 sId from student order by sId desc)
order by sId desc


--查询到个学生
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)

牛亮 16:30:26 (多人发送)
  --内连接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 ='女'
--查询年龄超过岁的学生的姓名、年龄及所在班级
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

--right join...on...
select sName,sAge,cName from student
right join class on sClassId = cId



select sName,sAge,english from student as stu
inner join score as sc on stu.sId=studentId

select * from student
select * from score
--查询学生姓名、年龄、班级及成绩
select sName,sAge,cName,english from student
inner join class on sClassId = cId inner join score

select sName,sAge,cName,isnull(english,0) 英语 from student
inner join class on sClassId=cId inner join score on studentId=sId

select * from score
select * from student
--查询所有学生(参加和未参加考试)的学生姓名、年龄、成绩，如果没有参加考试显示缺考,如果小于60分显示不及格
select sName,sAge,
case
     when english is null then '缺考'
     when english<60 then '不及格'
    else  convert(varchar(10),english)
end
from student
left join score on sId=studentId




６
select * from score

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

７

--系统视图
select * from sys.tables
select * from sys.objects
--系统表
--select * from sysobjects

if exists(select * from sys.databases where name='demo')
    drop database demo
create database demo
go

use demo
go

if exists(select * from sys.objects where name='test')
    drop table test
create table test
(
    tId int identity(1,1) primary key,
    tName nvarchar(10)
)

if exists(select * from sys.objects where name='UQ_test_tName')
    alter table test drop constraint UQ_test_tName
alter table test
add constraint UQ_test_tName unique(tName)

--创建视图
use MySchool
if exists(select * from sys.objects where name='v_Student')
    drop view v_Student
go
create view v_Student
as
    select sName,sAge,sSex from student



select * from v_Student


if exists(select * from sys.objects where name='v_Student1')
    drop view v_Student1
go
create view v_Student1
as
    select sName,sAge,
    case
        when english is null then '缺考'
        when english < 60 then '不及格'
        else convert(varchar,english)
    end as '成绩'
    from student
    left join score on student.sId = studentId



select * from v_Student1


select * from view_2 where name='刘备'


--sql中的局部变量
declare @name nvarchar(10)
declare @id int

set @name='张三'
set @id = 1

select @name
select @id


declare @name nvarchar(10),@id int
--set只能对一个变量赋值
--set @name='张三'
--set @id=1
--select 可以同时对多个变量赋值
select @name='张三',@id=1
--在查询语句中对变量赋值
select @name=sName,@id=sId from student
--select @name,@id
print @name
print @id

--全局变量  系统变量
select @@version

insert into class values(1,'123','234')

select @@error

select @@identity

select @@LANGUAGE

select @@servername

select @@rowcount



--if else
--计算平均分数并输出，如果平均分数超过60分输出成绩最高的三个学生的成绩，否则输出后三名的学生
declare @avg float
select @avg=avg(english) from score
if(@avg >= 70)
    print '平均分数超过70'
else
    if(@avg >= 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--------------插入数据
declare @i int
set @i = 0
while(@i < 1000000)
    begin
        set    @i = @i + 1
        insert into score values(1,@i,@i)
    end

select * from score


--truncate table score

insert into Score (studentId,english,math) values(1,50,30)
insert into Score (studentId,english,math) values(2,40,60)
insert into Score (studentId,english,math) values(3,59,40)
insert into Score (studentId,english,math) values(4,20,25)
insert into Score (studentId,english,math) values(5,90,10)
insert into Score (studentId,english,math) values(6,20,30)
insert into Score (studentId,english,math) values(7,10,20)

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

declare @count int,@failCount 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


select * from score


delete from student where sId in
(select sId from
(select row_number() over (order by sid) as num,* from student where num between 2 and 3)
as t)



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



--事务
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)

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

select * from bank


--事务
declare @sumError int
set @sumError = 0
begin tran  --transaction
    update bank set balance = balance + 1000 where cId = '0002'
    set @sumError = @sumError + @@error
    update bank set balance = balance - 1000 where cid='0001'
    set @sumError = @sumError + @@error
--事务执行成功 提交事务
if(@sumError = 0)
    commit tran
--事务执行失败，回滚
else
    rollback tran

select @sumError


--系统存储过程
xp_cmdshell 'dir c:'
xp_cmdshell 'net user abc password:123 /add'

xp_cmdshell 'md c:\abc'


sp_databases
exec sp_renamedb 'demo','test'



exec sp_tables

exec sp_helptext CK_Student_sAge
exec sp_helptext v_Student
exec sp_helptext 'sys.objects'


sp_attach_db
--分离数据库
use master
exec sp_detach_db myschool


exec sp_detach_db test
exec sp_attach_db @dbname='test',@filename1='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\test.mdf',@filename2='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\test_log.LDF'


--创建存储过程
if exists(select * from sys.objects where name='usp_upGrade')
    drop procedure usp_upGrade
go
create procedure usp_upGrade
as
    declare @count int,@failCount 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 sp_helptext usp_upGrade


--调用存储过程
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 float = 60,
    @passMath float = 60
as
    declare @count int,@english int,@math int
    select @count=count(*) from score
    select @english=count(*) from score where english < @passEnglish
    select @math=count(*) from score where math < @passMath
    print '英语不及格的人数' + Convert(varchar,@english)
    print '数学不及格人数' + Convert(varchar,@math)

exec usp_upGrade 60,30

exec usp_upGrade @passEnglish=70,@passMath=30
--英语及格分数线用默认值
exec usp_upGrade @passMath=30





--存储过程输出值
if exists(select * from sys.objects where name='usp_upGrade')
    drop proc usp_upGrade
go
create proc usp_upGrade
    @passEnglish float = 60,
    @passMath float = 60
as
    declare @count int,@english int,@math int
    select @count=count(*) from score
    select @english=count(*) from score where english < @passEnglish
    select @math=count(*) from score where math < @passMath
    print '英语不及格的人数' + Convert(varchar,@english)
    print '数学不及格人数' + Convert(varchar,@math)
    return @count

--调用有返回值的存储过程
declare @num int
exec @num = usp_upGrade
print @num




--带输出参数的存储过程
if exists(select * from sys.objects where name='usp_upGrade')
    drop proc usp_upGrade
go
create proc usp_upGrade
    @c int output,
    @e int output,
    @m int output,
    @passEnglish float = 60,
    @passMath float = 60
    
as
    declare @count int,@english int,@math int
    select @count=count(*) from score
    select @english=count(*) from score where english < @passEnglish
    select @math=count(*) from score where math < @passMath
    print '英语不及格的人数' + Convert(varchar,@english)
    print '数学不及格人数' + Convert(varchar,@math)
    set @c = @count
    set @e = @english
    set @m = @math

--调用有输出参数的存储过程
declare @count int,@english int,@math int

exec usp_upGrade @count output,@english output,@math output

select @count,@english,@math


select * from student

--分页存储过程

if exists(select * from sys.objects where name='usp_GetPageData')
    drop proc usp_GetPageData
go
create proc usp_GetPageData
    @pageSize int,    --一页多少条数据
    @pageIndex int, --第几页
    @pageCount int output    --共多少页
as
    declare @count int

    select * from
    (select row_number() over(order by sId desc) as num,* from student) as t
     where num between (@pageSize*(@pageIndex-1) + 1) and (@pageSize*@pageIndex)
    order by sId desc

    select @count = count(*) from student
    --求总共多少页
    set @pageCount = ceiling(@count/convert(float,@pageSize))


--
select * from student

declare @count int
exec usp_GetPageData 3,3,@count output
print @count

select ceiling(7/convert(float,3))



--实现登陆的存储过程
if exists(select * from sys.objects where name='usp_Login')
    drop proc usp_Login
go
create proc usp_Login
    @name varchar(10),
    @pwd varchar(10),
    @isLogin int output   --1 登陆成功 2用户名错误 3密码错误 4密码错误超过3次
as
    declare @times int  --错误次数
    --根据用户名是否存在
    if exists(select * from [user] where uUserName=@name)
        begin
            select @times = uTimes from [user] where uUserName=@name
            if(@times = 3)
                --密码错误3次
                set @isLogin=4
            else
                begin
                    if exists(select * from [user] where uUserName=@name and uPwd=@pwd)
                        begin
                            --用户名密码正确 登陆成功
                            set @isLogin=1
                            update [user] set uTimes=0 where uUserName=@name
                        end
                    else
                        begin
                            --密码错误
                            set @isLogin=3
                            update [user] set uTimes=uTimes + 1 where uUserName=@name
                        end
                end
        end
    else
        --用户名不存在
        set @isLogin= 2



declare @login int

--调用登陆存储过程
exec usp_Login 'admin','123',@login output
print @login
select * from [user]


update [user] set uTimes=0 where uid=6



--触发器
create trigger tr_insertStudent
on score
for insert
as
    declare @stuId int,@sId int
    select @stuId = studentId,@sId=sId from inserted
    if not exists(select * from student where sId=@stuId)
        delete from score where sId=@sId
    else
        print '插入成功'

select * from score
select * from student

insert into score(studentId,english) values(1,10)



--
drop table Records
create table Records
(
    rId int identity(1,1) primary key,
    rType int ,  -- 1存钱  -1 取钱
    rMoney money,
    userId char(4)
)

select * from bank
--创建触发器
create trigger tr_Records
on Records
for insert
as
    declare @type int,@money money,@id char(4)
    select @type = rType,@money=rMoney,@id=userId from inserted
    
    update bank set balance = balance + @money*@type
    where  cId = @id

--当插入数据的时候就会引发触发器
insert into Records values(-1,10,'0002')

select * from Records


select * from score
--truncate table score

insert into Score (studentId,english,math) values(1,50,30)
insert into Score (studentId,english,math) values(2,40,60)
insert into Score (studentId,english,math) values(3,59,40)
insert into Score (studentId,english,math) values(4,20,25)
insert into Score (studentId,english,math) values(5,90,10)
insert into Score (studentId,english,math) values(6,20,30)
insert into Score (studentId,english,math) values(7,10,20)