sql基本命令

--------------------------------------------------------SQL基本命令开始-------------------------------------------------------

--切换数据库
use master
go 

---------------------------------------数据库操作-----------------------------------------------------------------
--删除数据库
--drop DataBase MySchool

-- 创建数据库
create database MySchool
on
(
   name="MySchool",        --文件的逻辑名
   filename="d:\database\MySchool.mdf",        --文件的保存路径，database文件夹要先存在
   size=3,        --文件的初始大小
   maxsize=100,        --文件的最大值
   filegrowth=1        --文件的增长比例，还可以filegrowth=10%
)
--日志文件
log on
(
   name="MySchool_log",     --文件的逻辑名
   filename="d:\database\MySchool_ldf.ldf",     --文件的保存路径，database文件夹要先存在
   size=3,           --文件的初始大小
   maxsize=100,      --文件的最大值
   filegrowth=10%    --文件的增长比例，还可以filegrowth=10%

)
go
--修改数据库
--alter database MySchool


--------------------------------------------数据库操作结束--------------------------------------------------------------------



--------------------------------------------表操作----------------------------------------------------------------------------

--注意，当删除表时，如果表上有外键约束，应注意删除顺序，还有就是级联删除，不再累赘
--切换数据库
use MySchool
go
--删除表
--drop table Class
--创建表 Class
create table Class
(
  cId int identity(1,1) primary key,--设置cId为自动增长，初始值为1，增长量为1,并且为主键
  cName nvarchar(10) not null,
  cDescription nvarchar(200)
)
go
--删除表Student
--drop table Student
--创建表
create table Student
(
   sId int identity(1,1) primary key,--设置sId为自动增长，初始值为1，增长量为1,并且为主键
   sName nvarchar(10) not null,--设置姓名不能为空
   sSex  nchar(1)  ,
   sAge int,-- check(sAge>18 and sAge<150),
   sBirthday datetime,
   sClassId int not null,
   sNo decimal(18,0)--身份证号
)
go 




-------------------------表结构的修改-------------
--增加列
alter table Student
add  sNo decimal(18,0)

--修改表 时要加上 column
alter table student
alter column sName nvarchar(8)

alter table student
alter column sName nvarchar(10) not null


--删除列 时  要加column
alter table Student
drop column sNo

--删除表Score
--drop table Score
--创建表 Score
create table Score
(
  sId  int identity(1,1) primary key,
  studentId int not null,
  english float,
  math float
)
go


--插入数据到 Class,可以把列直接拖到()中
insert into Class (cName)values('高一一班')
insert into Class (cName, cDescription)values('高一二班','快班')
--insert into Class values('高一三班') --省略列名时，必须插入所有数据，自动增长列除外
insert into Class values('高一三班','中班')
--查询数据
select  * from Class

--插入数据到 Student
--insert into Student(sName, sSex, sAge, sBirthday, sClassId) values('tom','男',22,1993-02-12,1);
--错误，日期必须加引号
insert into Student(sName, sSex, sAge, sBirthday, sClassId) values('tom','男',22,'1993-02-12',1);
insert into Student(sName, sSex, sAge, sBirthday, sClassId) values('jack','男',22,'1993-02-12',1);

--修改数据
update Student set sAge=19,sSex='女'
update Student set sSex='男' where sId=1
update Student set sAge=sAge+1

--查询数据
select * from Student
--删除数据
delete from Student
delete from Student where sName='tom'

--清空表中的数据，连自动编号也一起清掉
truncate table Score
truncate table Student
truncate table Class

--------------------------------------------表操作结束--------------------------------------------------------------------





----------------------------------------------约束开始---------------------------------------------------------------------


--自动增长
--下面的写法太麻烦
--alter table Student
--drop column sId
--alter table Student
--add  sId int identity(1,1)

--alter table Score
--drop column sId
--alter table Score
--add  sId int identity(1,1)
--
--alter table Class
--drop column cId
--alter table Class
--add  cId int identity(1,1)

--非空约束-----修改表的约束
alter table Student
alter column sName nvarchar(10) not null

------------------------给字段增加约束------------------------

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

alter table Class
add constraint PK_Class_cId primary key(cId)

alter table Score
add constraint PK_Score_cId primary key(sId)
--唯一约束
alter table Student
add constraint UQ_Student_sNo unique(sNo)--身份证号唯一
--默认约束
alter table Student
add constraint DF_Student_sSex default('男') for sSex --注意是 for sSex 

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

-------------------外键约束----------------------------------------

--Score表中的外键是student表中的sId，student表中的外键是class表中的主键cId

--------注意 以下建立外键约束的顺序不能反，否则会出错

alter table Score
add constraint FK_Score_studentId foreign key (studentId) references Student(sId) on delete cascade on update cascade

alter table Student
add constraint FK_Student_sClassId foreign key (sClassId) references Class(cId) on delete cascade on update cascade --级联删除的实现当删除班级号时,所在班级的所有学生的信息都被删除.



--级联删除
--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 唯一
use master
go
create table Teacher
(
 tId int identity(1,1),
 tSex nchar(1),
 tAge int,
 tName nvarchar(10)
)
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)
---------------------------------------练习结束结束----------------------------------------------------------------------

----------------------------------------约束的简单写法-------------------------------------------------------------
--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

-------------------------------------约束的简单写法结束---------------------------------------------------------------




--------------------------------------插入测试数据开始---------------------------------------------------------------

insert into Class (cName,cDescription) values ('高一一班','快班')
insert into Class (cName,cDescription) values ('高一二班','中班')
insert into Class (cName,cDescription) values ('高一三班','慢班')
insert into Class (cName,cDescription) values ('高二一班','快班')
insert into Class (cName,cDescription) values ('高二二班','中班')
insert into Class (cName,cDescription) values ('高二三班','慢班')

insert into Student (sClassId,sName,sAge,sSex,sNo,sBirthday) values (1,'刘备',20,'男',123456789012345678,'1987-5-6')
insert into Student (sClassId,sName,sAge,sSex,sNo,sBirthday) values (1,'关羽',19,'男',123456789012345552,'1988-8-6')
insert into Student (sClassId,sName,sAge,sSex,sNo,sBirthday) values (1,'张两飞',19,'男',123456789068745672,'1989-5-19')
insert into Student (sClassId,sName,sAge,sSex,sNo,sBirthday) values (4,'曹操',22,'男',123456789012345673,'1985-12-6')
insert into Student (sClassId,sName,sAge,sSex,sNo,sBirthday) values (4,'夏侯惇',22,'男',123456789012345674,'1985-3-6')
insert into Student (sClassId,sName,sAge,sSex,sNo,sBirthday) values (4,'华佗',50,'男',123456789012356574,'1957-1-16')
insert into Student (sClassId,sName,sAge,sSex,sNo,sBirthday) values (4,'甄姬',52,'女',12345678901234565,'1989-8-8')

insert into Score (studentId,english) values(1,90)
insert into Score (studentId,english) values(2,90)
insert into Score (studentId,english) values(3,59)
insert into Score (studentId,english) values(4,100)
insert into Score (studentId,english) values(5,60)
insert into Score (studentId,english) values(8,0)
insert into Score (studentId,english) values(7,80)
---------------------------------------插入测试数据结束-------------------------------------------------------





--------------------------------------数据的检索----------------------------------------------------------------

------------------------------------简单的数据检索----------------------------------------------------------------
select * from Student
select * from Score
select * from Class
---------只检索需要的列 
select sName from student
select sName as '姓名',sAge as '年龄' from student
----------使用where检索符合条件的数据
select sName  '姓名' from student where sSex='男'
---------检索不与任何表关联的数据
select 1 
select getdate() as '当前日期'
---------Top 获取前几条数据

select top 2 * from student
select top 2 sName from student
-----使用百分号,不够一,进一,没有舍
select top 20 percent *  from student

---------Distinct 去除重复数据,是针对所有被查询的列而言,不是单个列
select distinct * from student
select distinct sName from student

---------------------------------聚合函数--------------------------------------------

select max(sId) from student
select min(sId)from student
select avg(math) from score-----不会计算值为null 的列
select sum(math) from score
select count(*) from score
select sum(math)/count(*) from score

select max(sBirthday),min(sBirthday) from student where sSex='男'



select * from Student
select * from Score
select * from Class

-----------------------------带条件的查询---------------------------------------------

--Select …from…where 
--查询没有及格的学生的学号
select studentId from score where english<60
--查询年龄在20-30岁之间的男学生
select sName from student where sAge>=20 and sAge<=30
--Between…and …在之间 
--查询年龄在20-30岁之间的男学生
select sName from student where sAge  between 20 and 30
--查询班级id为1，2，3的所有学生
select sId,sName from student where sId=1 or sId=2 or sId=3
select sId,sName from student where sId in(1,2,3)

---------------------------模糊查询---------------------------------------------------------

----查询所有姓张的同学
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 '_[飞两]%'-----  []匹配[]内的一个字符


---------------------------------------NULL值处理---------------------------------------------

-----null表示“不知道”，而不是表示没有
select null+1    ---结果是null，因为“不知道”加1的结果还是“不知道”。

select * from score where math =null---错误写法
select * from score where math is null
select * from score where math is not null

---------------------------------------order by asc ,desc 数值排序，默认升序asc---------------------------------------

--按照年龄升序排序所有学生信息的列表
select * from student order by sAge asc
--按照英语成绩从大到小排序，如果英语成绩相同则按照数学成绩从大到小排序 
select * from score order by english desc,math desc
--ORDER BY子句要放到WHERE子句之后 
select * from score 
where english>80
order by english desc,math desc

---------------------------------------group by 数据分组------------------------------------------

--按照班级进行分组统计各个班级的人数
select cName,count(*)from class
group by cName

--GROUP BY子句必须放到WHERE语句的之后 
select sSex,count(*) from student
where sAge>18
group by sSex

--聚合函数不能出现在where子句中
select sSex,count(*) from student
where sAge>18 and avg(sAge)>19
group by sSex


--没有出现在GROUP BY子句中的列是不能放到SELECT语句后的列名列表中的 （聚合函数中除外）

--select sSex,count(*), avg(sAge),sName from student --错误，sName不能放select在列表中
select sSex,count(*), avg(sAge) from student
where sAge>18
group by sSex

----------------------------having 对分组后的内容进行条件选择-------------------------------------------

--在Where中不能使用聚合函数，Having中可以，Having要位于Group By之后,而且having中的列要在select中存在
 --错误,sAge列在select中不存在,而且它也不是聚合函数
--select sSex,count(*), avg(sAge) from student
--group by sSex
--having sAge>18 and avg(sAge)>19

select sSex,count(*), avg(sAge) from student
group by sSex
having avg(sAge)>19



--------练习-------------
--求男生和女生分别有多少人
select sSex,count(*) from student
group by sSex

--每个班中的男同学的平均年龄
--select * from student
select count(*) , avg(sAge),sClassId from student
where sSex='男'
group by sClassId
--求平均年龄小于22的那些班
select sClassId from student
group by sClassId
having avg(sAge)<22

-----------------------------------------union 联合结果集---------------------------------------------

--列的个数一致，类型一致（兼容）
--排序  去除重复数据
select * from student
select * from teacher
select sId ,sSex,sName from student union select tId,tSex ,tName from teacher

--union all
--直接连接两个结果集，不排序，不去除重复，效率高
select sId ,sSex,sName from student union all select tId,tSex ,tName from teacher

--要求在一个表格中查询出学生的英语最高成绩、最低成绩、平均成绩
select max(english),min(english),avg(english) from score
select '最高成绩',max(english)from score union all select '最低成绩',min(english)from score union all select'平均成绩', avg(english) from score
--查询每位老师的信息，包括姓名、工资，并且在最后一行加上平均工资和最高工资
--alter table teacher
--add tSalary int
select tName,tSalary from teacher 
union all select '平均工资',avg(tSalary) from teacher
union all select '总工资',sum(tSalary) from teacher

-------------------------------一次插入多条数据----------------------------------------------
insert into score 
select 1,66,88 union
select 2,85,78 union
select 3,36,98 

--把现有表的数据插入到新表（表不能存在）
select * into newStudent from student
select * from newStudent
truncate table newStudent
--把现有表的数据复制到一个已存在的表
---要先把设置的自动增长列干掉
insert into newStudent select  sName, sSex, sAge, sBirthday, sClassId, sNo from student


----------------------------------------------------数据检索结束---------------------------------------------------------------


------------------------------------------------------------sql中的函数----------------------------------------------------------

-------------------------------------字符串处理函数----------------------------------------------------------
select len(3455)
select len(getdate())
--LOWER() 、UPPER () ：转小写、大写
select lower('ADGBAddf')
select upper('ADGBAddf')
--LTRIM()：字符串左侧的空格去掉 
select ltrim('    adga     ')
--RTRIM () ：字符串右侧的空格去掉 
select rtrim('    adga     ')+'123'
--去除左右空格方法
--LTRIM(RTRIM('         bb        '))
select '456'+ltrim(rtrim('   dag    ')+'12')
--LEFT()、RIGHT()  截取取字符串
select left('2344',1)
select right('2344',1)
--SUBSTRING(string,start_position,length)
select substring('23444',1,2)---注意起始位置从一开始，和c#不同


----------------------------------------------------日期函数--------------------------------------------------------

--GETDATE() ：取得当前日期时间 
select getdate()
--DATEADD (datepart , number, date )，计算增加以后的日期。参数date为待计算的日期；参数number为增量；参数datepart为计量单位，可选值见帮助
select dateadd(day,1,getdate())
select dateadd(year,1,getdate())
select dateadd(month,1,getdate())
select dateadd(hour,1,getdate())

--获取年月日
select year(getdate())
select month(getdate())
select day(getdate())
--DATEDIFF ( datepart , startdate , enddate ) ：计算两个日期之间的差额。 datepart 为计量单位，可取值参考DateAdd。
select datediff(day,'2013-06-10',getdate())

--DATEPART (datepart,date)：返回一个日期的特定部分 
select datepart(year,getdate())
--datename datepart
select datename(year,getdate()) --字符串
select datepart(year,getdate()) --整数

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

--CAST ( expression AS data_type)
select '平均分数' + cast(123 as varchar(10))--解决了字符串和整数连接的问题
select cast(34.89 as int)--没有进位
select cast (round(89.6,0) as int) --有进位
select cast(78.98534 as decimal(10,2))--有进位，decimal的第一个参数是最大有效位，第二个参数是小数位数
select cast(avg(english) as decimal(4,2)) from score---解决了小数点位数过长的问题，
--CONVERT ( data_type, expression) 
select convert(int,56.87)--没有进位
select convert(decimal(4,2),56.86)--有进位

select convert(varchar(30),getdate(),20)--2013-06-15 14:01:39
select convert(varchar(20),getdate(),21)
select convert(varchar(30),getdate(),23)--国内标准年月日2013-06-15
select convert(varchar(10),getdate(),108)--国内标准时分秒14:00:32

select convert(varchar(10),getdate(),20)--通过截取字符串获得国内标准年月日

    ----------------------空值处理函数------------------------------------
--ISNULL(expression,value) ：如果expression不为空则返回expression，否则返回value。
--select * from score
select studentId,isnull(math,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------对于计算平均值很有用


------------------------------------------------练习-----------------------------------------------------

---------------练习1 未完成-------------------------------------------------------------
创建 MyCompany数据库

部门表 department
    dId
    dName
员工表 employee
    eId
    eName
    eSex
    eAge
    eSalary
    eDepId

建库  建表  建约束  添加测试数据    更新 和删除



1、查询所有员工
2、查询工资超过2000快钱的员工
3、查询最新进来的5个员工
4、查询员工的平均工资，平均工资
5、查询总共有多少员工
6、查询每个部门有多少员工
7、查询每个部门的平均工资
8、查询每个部门男员工的平均工资
9、查询平均工资超过2000的那些部门
10、查询员工工资（姓名，工资）的同时，同一结果集显示平均工资和最高工资
11、查询名字里包含'定,月'的这些员工，并对结果按照年龄排序

-------------------------------练习1结束---------------------------------------------------------------------------

---------------练习2未完成-------------------------------------------------------------

--创建一张表，记录电话呼叫员的工作流水，记录呼叫员编号、对方号码、通话开始时间、通话结束时间。
--建表、插数据等最后都自己写SQL语句。
--drop table CallRecords
--use MySchool
--go
--create table CallRecords
--(
--   cId int identity(1,1) primary key,
--   cNo int not null,
--   cCllNumber varchar(6),
--   cStartTime datetime,
--   cEndTime   datetime
--)
use MySchool
go
CREATE TABLE [CallRecords]
(
    [Id] [int] NOT NULL identity(1,1),
    [CallerNumber] [nvarchar](50), --三位数字
    [TelNum] [varchar](50),
    [StartDateTime] [datetime] NULL,
    [EndDateTime] [datetime] NULL  --结束时间要大于开始时间,默认当前时间
)

--主键约束
alter table [CallRecords]
add constraint PK_CallRecords primary key(id)

--检查约束
alter table [CallRecords]
add constraint CK_CallRecords check(CallerNumber like '[0-9][0-9][0-9]')

alter table [CallRecords]
add constraint CK_CallRecords_EndDateTime check(EndDateTime > StartDateTime)

--默认约束
alter table [CallRecords]
add constraint DF_CallRecords default(getdate()) for EndDateTime



INSERT [dbo].[CallRecords] ([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('001', '0208888888', CAST(0x00009DAF00A4CB80 AS DateTime), CAST(0x00009DAF00A62E94 AS DateTime));
INSERT [dbo].[CallRecords] ([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('001', '0208888888', CAST(0x00009DB000D63BC0 AS DateTime), CAST(0x00009DB000D68DC8 AS DateTime));
INSERT [dbo].[CallRecords] ([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('001', '89898989', CAST(0x00009DB000E85C60 AS DateTime), CAST(0x00009DB000E92F50 AS DateTime));
INSERT [dbo].[CallRecords] ([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('002', '98987676', CAST(0x00009DB2015BB7A0 AS DateTime), CAST(0x00009DB2015C4DA0 AS DateTime));
INSERT [dbo].[CallRecords] ([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('002', '02188839389', CAST(0x00009DA4014C9C70 AS DateTime), CAST(0x00009DA4014E0308 AS DateTime));
INSERT [dbo].[CallRecords] ([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('001', '767676766', CAST(0x00009DB400DAA0C0 AS DateTime), CAST(0x00009DB400DD5FE0 AS DateTime));
INSERT [dbo].[CallRecords] ([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('003', '0227864656', CAST(0x00009DB200B9AB40 AS DateTime), CAST(0x00009DB200B9FC1C AS DateTime));
INSERT [dbo].[CallRecords] ([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('003', '676765777', CAST(0x00009DB8014042B8 AS DateTime), CAST(0x00009DB80141804C AS DateTime));
INSERT [dbo].[CallRecords] ([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('001', '89977653', CAST(0x00009D9A00FB9898 AS DateTime), CAST(0x00009D9A00FE6118 AS DateTime));
INSERT [dbo].[CallRecords] ([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('004', '400400400', CAST(0x00009D9A00FB9898 AS DateTime), CAST(0x00009D9A00FE6118 AS DateTime));


--要求：
--输出所有数据中通话时间最长的5条记录。orderby datediff
select top 5 datediff(second,[StartDateTime],[EndDateTime]), Id, CallerNumber, TelNum, StartDateTime, EndDateTime from CallRecords
order by datediff(second,[StartDateTime],[EndDateTime])desc
--输出所有数据中拨打长途号码（对方号码以0开头）的总时长。like、sum
--输出本月通话总时长最多的前三个呼叫员的编号。
--
--输出本月拨打电话次数最多的前三个呼叫员的编号.group by,count(*)



---------------练习2结束------------------------------------------------------------



----------------------------------------------sql基本命令结束---------------------------------------------------------------------








----------------------------------------------sql高级命令开始------------------------------------------------------------

----创建示例数据库demo
create database demo
use demo
go
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后面的返回值类型必须一致

--case end第一种用法，注意 字段level在case 后，when后面跟的是一个常量
select name,
            case level
                when 1 then '菜鸟'
                when 2 then '骨灰'
                else '大神'
            end as '级别'
from [user]

use myschool
go
select * from score
--case end第二种用法，相当于多重if语句，注意case后面没有字段，when后面为一个表达式
select studentid,
            case
                when english>=90 then '优秀'
                when english>=80 then '良好'
                when english>=60 then '及格'
                else '不及格'
            end as '成绩等级'
from score

----------练习1--------表中有A B C三列,用SQL语句实现：当A列大于B列时选择A列否则选择B列，当B列大于C列时选择B列否则选择C列。
select 
        case
            when A>B then A
            else B
        end as 'AB列中的大值',
        case 
            when B>C then B
            else C
        end as 'BC列中的大值'
from t

--------练习2------

--        单号   金额
--        Rk1     10
--        Rk2     20
--        Rk3     -30
--        Rk4     -10
--        将上面的表输出为如下的格式：
--        单号   收入   支出
--        Rk1     10     0
--        Rk2     20     0
--        Rk3      0     30
--        Rk4      0     10
use demo
go
--成绩示例表
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 0
                 else (0-amount)
             end as '支出'
from test
                 
       
-----------练习3----------

--        有一张表student0，记录学生成绩
--        name       subject    result
--        张三        语文        80
--        张三        数学        90
--        张三        物理        85
--        李四        语文        85
--        李四        数学        92
--        李四        物理        NULL

--        要求输出下面的格式：
--        name       语文         数学         物理
--        ---------- ----------- ----------- -----------
--        李四        85          92          0
--        张三        80          90          85

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


-------------------------------------------------子查询----------------------------------------------------------


--将一个查询语句做为一个结果集供其他SQL语句使用，就像使用普通的表一样，被当作结果集的查询语句被称为子查询

select * from student
select * from(select * from student where sage>20)as t

--要求在一个表格中查询出学生的英语最高成绩、最低成绩、平均成绩
select(select max(english) from score) as maxenglish,(select min(english) from score) as minenglish,(select avg(english) from score) as avgenglish
--只有返回且仅返回一行、一列数据的子查询才能当成单值子查询
--select(select * from score)--错误
--查询高二二班的所有学生
select * from student where sClassId in(select cId from class where cName='高二二班')

--查询高一一班和高二一班的所有学生

select * from student where sClassId in(select cId from class where cName='高二二班' or cName='高二一班')

--查询刘关张的成绩
select * from score where studentid in(select sid from student where sname='关羽' or sname='刘备' or sname='张飞')

--快速实现删除多个学生
delete from student where where sname='关羽' or sname='刘备' or sname='张飞'

--数据库中分页的实现

--取最近插入的1-3个学生
select top 3 * from student 
where sid not in
(select top 0 sid from student order by sid desc)
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

--取最近插入的第(3n-2)-3n个学生
select top 3 * from student
where sid not in
(select top 3(n-1) sid from student order by sid desc)
order by sid desc

----------------------row_number()------sql 2005中的分页---------------------------------------------


----row_number()over (order by sid) as num
select * from (select  row_number()over (order by sid) as num,* from student )as t
where num between 1 and 3

select * from (select  row_number()over (order by sid) as num,* from student )as t
where num between 4and 6

select * from (select  row_number()over (order by sid) as num,* from student )as t
where num between 7 and 9

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

-------------------------表连接------------------------------------------------
--交叉连接，全连接
select * from student
cross join class

--左外连接
select * from student
left join class on sclassid=cid

--右外连接
select * from student
right join class on sclassid=cid

---内连接，等值连接
select * from student
inner join class  on sclassid=cid

--同上效果的写法，也是经常的写法
select * from student,class
where sclassid=cid

--查询所有学生的姓名、年龄及所在班级
select sname ,sage,cname from student
inner join class on sclassid=cid

--查询年龄超过20岁的学生的姓名、年龄及所在班级
select sname,sage,cname from student,class
where  sclassid=cid and sage>20

--查询学生姓名、年龄、班级及成绩
select sname,sage,cname,english from student
inner join class on sclassid=cid
inner join score on student.sid=studentid

--查询所有学生(参加及未参加考试的都算)及成绩

select * from student
left join score on student.sid=studentid

--查询所有及格的学生姓名、年龄及成绩
select sname,sage,english from student
inner join score on student.sid=studentid
where english>60

--查询所有参加考试的(分数不为null)学生姓名、年龄及成绩,班级

select sname,sage,english,cname from student
inner join class on sclassid=cid
inner join score on student.sid=studentid
where english is not null

--查询所有学生(参加和未参加考试)的学生姓名、年龄、成绩，如果没有参加考试显示缺考,如果小于60分显示不及格

select sname,sage,
                  case                     
                    when english>=60 then cast( english as nvarchar(10))
                    when english<60 then '不及格'
                    when english is null then '缺考'
                  end
 from student
 inner join score on student.sid=studentid

-----------------------------------视图--------------------------------------------------------
-----系统视图
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 @ssex nvarchar(10),@sname nvarchar(10)
--set @ssex='男' ---只能为一个变量赋值
--select @sname='刘备',@ssex='男' ---可为多个变量赋值
select @sname=sname from student where sid=1 ----可以把sql查询的结果赋给变量

select @sname,@ssex

print @sname  -----只能输出一个值

--全局变量

---全局变量是系统定义的，只读的

select @@version

select @@error

select @@identity

select @@LANGUAGE

select @@servername

select @@rowcount


------------------if--else 条件判断---------------------

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

declare @avg float
select @avg=avg(english) from score
if(@avg>=60)
    begin
     select top 3 * from score order by sid desc
    end
else
    begin
     select top 3 * from score order by  sid asc
    end


--------------while-----------

--如果不及格的人超过半数(考试题出难了)，则给每个人增加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


--把所有未及格的人的成绩都加及格
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


------------------------------事务--------------------------------------------

--转账问题：
--    假定钱从A转到B，至少需要两步：
--        A的资金减少
--        然后B的资金相应增加      

------示例表
use demo
go
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


----指访问并可能更新数据库中各种数据项的一个程序执行单元(unit)--也就是由多个sql语句组成，必须作为一个整体执行
--这些sql语句作为一个整体一起向系统提交，要么都执行、要么都不执行 


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


--查看结果。 
SELECT * FROM bank



----------------存储过程----------------------------------

--存储过程---就像数据库中运行方法(函数)

--系统存储过程

EXEC sp_databases
EXEC  sp_renamedb 'Northwind','Northwind1'
EXEC sp_tables
EXEC sp_columns stuInfo  
EXEC sp_help stuInfo
EXEC sp_helpconstraint stuInfo
EXEC sp_helpindex stuMarks
EXEC sp_helptext 'view_stuInfo_stuMarks' 
EXEC sp_stored_procedures  

---分离数据库
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 proc 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

---执行存储过程
execute usp_upGrade


use MySchool
go
select * from score

--带参数的存储过程
--带默认值参数

-------找出英语成绩不及格的人数

if exists(select * from sys.objects where name='usp_GetFailEnglishNum')
drop procedure usp_GetFailEnglishNum

go

create proc usp_GetFailEnglishNum
@failEnglish float=60 ----带默认值的 输入型参数
as
   declare @failcount int
   select @failcount=count(*) from score where english<@failEnglish
   print @failcount

--执行存储过程
execute usp_GetFailEnglishNum----使用默认值

execute usp_GetFailEnglishNum 50----自己赋值
execute usp_GetFailEnglishNum  @failEnglish=50----另一种赋值方法

select * from score


-----存储过程输出值 return
if exists(select * from sys.objects where name='usp_GetFailEnglishNum')
drop procedure usp_GetFailEnglishNum

go

create proc usp_GetFailEnglishNum
@failEnglish float=60 ----带默认值的 输入型参数
as
   declare @failcount int,@count int
   select @failcount=count(*) from score where english<@failEnglish
   print @failcount
   select @count=count(*) from score 
   return  @count

--调用有返回值的存储过程
declare @count int
execute @count=usp_GetFailEnglishNum
print @count


------带输出参数的存储过程的创建

if exists(select * from sys.objects where name='usp_GetFailEnglishNum')
drop procedure usp_GetFailEnglishNum

go

create proc usp_GetFailEnglishNum
@failEnglish float=60,----带默认值的 输入型参数
@countNum int output    -----输出型参数
as
   declare @failcount int,@count int
   select @failcount=count(*) from score where english<@failEnglish
   print @failcount
   select @count=count(*) from score 
   set @countNum= @count  --给输出型参数赋值

--调用有返回值的存储过程
declare @count int
execute usp_GetFailEnglishNum 60, @count output
print @count

------------------分页存储过程---------------------

---要输入要显示的页码和每页显示的条数。输出总共的页码数
use MySchool
go
if exists(select * from sys.objects where name='usp_GetPageData')
drop procedure usp_GetPageData
go

create proc usp_GetPageData

@pageCount int output,----输出型参数 总共的页码数目
@pageIndex int =1 ,  ---输入型参数，当前要显示的页码
@pageSize int =20   ----输入型参数，每页显示的记录数目

as
  declare @count int -----设置变量用于接收总的记录数，
  select * from(select row_number() over(order by sid) as num ,* from student) as t
  where num between @pageSize*@pageIndex-@pageSize+1 and @pageSize*@pageIndex
  order by sid desc
  select @count=count(*) from student

   ---求总共有多少页
  set @pageCount=ceiling((@count/convert(float,@pageSize))) --select ceiling(7/3.0)

--执行存储过程

declare @pageCount int
execute usp_GetPageData @pageCount output,2,3
select @pageCount as '总页码数目'

select * from student


  
--------------实现登陆的存储过程---------不是自己写的----------------

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




-----------------------------触发器------------------------------------------------------


--触发器是一种特殊类型的存储过程
--一般的存储过程通过存储过程名称被直接调用，而触发器主要是通过事件进行触发而被执行
--常见的触发器有三种：分别应用于Insert , Update , Delete 事件 

use MySchool
go
if exists(select * from sys.objects where name='tr_insertStudent')
drop trigger tr_insertStudent
go

create trigger tr_insertStudent

on  score for insert

as
  begin
        declare @stuId int,@sid int
        select @sid=sid,@stuId=studentId from inserted  ---从插入的临时表中获取数据
        if not exists(select * from student where sid=@stuId)
        delete from score where sid=@sid
        else
        print '插入成功'
  end

select * from student
select * from score
insert into score values(9,33,66)