SQL 语法速记

----------------------------------DML（数据操作语言）----------------------------------
--insert into 表名(列名列表) values(值列表);
insert into Students(SName,SSex,Age) values('张某某',default,16);
--insert into Table1(SName,SSex,Age) select SName,SSex,Age from Students;
--select identity(int,1,1),列名 into 新表 from 原始表
--insert 表(列) select 值 union
---------获取当前更新的标识列(得到自动增长字段的主键值)---------
--insert into 表名(列名列表) values(值列表) select @@identity;
--insert into 表名(列名列表) output inserted.Id values(值列表);
--update 表名 set 列名=更新值 where 条件
--delete from 表名 where 条件
--truncate table 表名 --(只删除表中的所有行，但表的结构、列、约束、索引等不会被改动；不能用于删除有外键约束引用的表)

----------------------------------DQL（数据查询语言）----------------------------------
--select 列名 from 表名 
--where 条件 
--group by 列名 
--having 筛选条件 
--order by 列名 asc或desc

----------------------------------删除姓名、年龄重复的记录（常考）---------------------------------- 
Id  name  age  salary 
   yzk    80  1000 
   yzk    80  2000 
   tom    20  20000 
   tom    20  20000 
   im     20  20000 

--取得不重复的数据
select * from Persons 
where Id in 
(
    SELECT MAX(Id) AS Expr1 
    FROM Persons
    GROUP BY Name,Age 
) 

--select distinct Name,Age from Persons

--根据姓名、年龄分组，取出每组的Id最大值，然后将Id最大值之外的排除。

--删除重复的数据：
delete from Persons 
where Id not in 
(
    SELECT MAX(Id) AS Expr1
    FROM Persons 
    GROUP BY Name,Age 
)

----------------------------------DDL（数据定义语言,建表、建库等语句。）----------------------------------
--创建文件夹
exec xp_cmdshell 'mdir d:\MyDB'
use master
go
if exists(select * from sysdatabases where name='MySchool')
    drop database MySchool
create database MySchool
on primary
(
    --括号一定是圆括号
    name='MySchool_data',            --数据库名称
    filename='d:\MySchool_data.mdf',--物理文件名
    size=3mb,                        --初始大小
    maxsize=10mb,                    --最大大小
    filegrowth=15%                --主文件增长率
)
log on
(
    name='MySchool_log',            --日志文件名
    filename='d:\MySchool_log.ldf',    --日志物理文件名
    size=1mb,                        --初始大小
    maxsize=4mb,                    --最大大小
    filegrowth=1mb
)
go                                    --和后续的SQL语句分隔开

use MySchool
go
if exists(select * from sysobjects where name='Student')
    drop table Student
create table Student    --创建学生信息表
(
    sId int identity(1,1) not null primary key,--自动编号
    sClassId int not null,            --班级外键
    sName nvarchar(50) not null,
    sAge int not null,
    sNo numeric(18,0),
    sSex char(2) not null,
    sEmail varchar(50)
)

----------------------------------约束----------------------------------
--主键约束(PK) primary key constraint 唯一且不为空
--非空约束 Not NULL 列不能为空
--唯一约束 (UQ)unique constraint 唯一，允许为空，但只能出现一次
--默认约束 (DF)default constraint 默认值
--检查约束 (CK)check constraint 范围以及格式限制
--外键约束 (FK)foreign key constraint 表关系

--添加主键约束
alter table Score
add constraint  PK_Score primary key(sId)
--添加唯一约束
alter table student
add constraint UQ_student unique(sNo)
--非空约束,为EmpName增加一个非空约束
alter table Employees 
alter column EmpName varchar(50) not null
--添加默认约束
alter table student
add constraint DF_student default('男') for sSex
--添加检查约束
alter table student
add constraint CK_student check(sAge >=18 and sAge <=100) 
--添加外键约束（主键表Class 外键表student）
--alter table 外键表
--add constraint 约束名
--foreign key(外键列) references 主键表(主键列)
alter table student
add constraint FK_student
foreign key(sClassId) references Class(cId) --外键student表中的sClassId来references引用主键表中的cid
--设置主外键关系的级联删除
alter table Employees 
add constraint FK_Employees_Department_DepId
foreign key(DepId) references Department(DepId)
on delete CASCADE --设置级联删除
--on update cascade --设置级联更新

--删除约束
alter table student
drop constraint FK_student

--手动删除一列(删除EmpAddress列)
alter table Employees drop column EmpAddress
--手动增加一列(增加一列EmpAddr varchar(500))
alter table Employees add EmpAddr11 varchar(500)
--手动修改一下EmpEmail的数据类型(varchar(200))
alter table Employees alter column EmpAddr varchar(1000)

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

--判断数据库名称和表名称是否存在
use master
go
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
---------------索引视图---------------
create view vw_ix_T1
WITH SCHEMABINDING 
as
select autoid,uname,usrId from dbo.T1
create unique clustered index ix_vw_t1 on vw_ix_T1(autoId)

----------------------------------索引----------------------------------
--系统表
select * from sysindexes
--系统视图
select * from sys.indexes
use MySchool
go
if exists(select name from sysindexes where name='ix_StedentName')
    drop index 表名.ix_StudentName
--唯一索引、聚集索引|非聚集索引
create [unique] [clustered | nonclustered] index 索引名
on 表(列) with fillfactor=数字
--指定按索引查询
selec * from Student with(index=ix_StedentName) where StudentName like '李%'

----------------------------------局部变量_必须以标记@作为前缀，先声明再赋值----------------------------------
--DECLARE @变量名  数据类型
declare @name nvarchar(10)
declare @id int
--赋值：SET @变量名=值 --set用于普通的赋值；SELECT @变量名=值  --用于从表中查询数据并赋值，可以一次给多个变量赋值
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 以表格的方式输出,可以同时输出多个变量
--PRINT 以文本的方式输出，一次只能输出一个变量的值
select @name,@id
print @name
print @id

----------------------------------全局变量（系统变量）必须以标记@@作为前缀----------------------------------
select @@error        --最后一个T-SQL错误的错误号
insert into class values(1,'123','234')
select @@identity    --最后一次插入的标识值
select @@servername    --本地服务器的名称
select @@version    --SQL Server的版本信息
select @@LANGUAGE    --当前使用的语言的名称
select @@TRANCOUNT    --当前连接打开的事务数
select @@rowcount    --受上一个SQL语句影响的行数

print  'SQLServer的版本'+@@VERSION 
print  '服务器名称: '+@@SERVERNAME
print '最后一次放生的错误号'+convert(varchar(5),@@ERROR)
print @@identity

----------------------------------条件结构----------------------------------
--if else语句
IF(条件表达式)
  BEGIN --相当于C#里的{
    语句1
  END --相当于C#里的}
ELSE
 BEGIN
    语句2
  END
--计算平均分数并输出，如果平均分数超过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语句
WHILE(条件表达式)
  BEGIN --相当于C#里的{
    语句
    BREAK
  END --相当于C#里的}

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

----------------------------------事务(Transaction)----------------------------------
--开始事务：BEGIN TRANSACTION
--事务提交：COMMIT TRANSACTION
--事务回滚：ROLLBACK TRANSACTION
declare @sumError int
set @sumError = 0
--设置事务回滚到原点
SET XACT_ABORT on
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

----------------------------------存储过程----------------------------------
--系统存储过程
    --由系统定义，存放在master数据库中
    --名称以“sp_”开头或”xp_”开头
--自定义存储过程
    --由用户在自己的数据库中创建的存储过程
sp_databases    --列出服务器上的所有数据库。
sp_helpdb        --报告有关指定数据库或所有数据库的信息
sp_renamedb        --更改数据库的名称
sp_tables        --返回当前环境下可查询的对象的列表
sp_columns        --回某个表列的信息
sp_help            --查看某个表的所有信息
sp_helpconstraint    --查看某个表的约束
sp_helpindex        --查看某个表的索引
sp_stored_procedures--列出当前环境中的所有存储过程。
sp_password            --添加或修改登录帐户的密码。
sp_helptext            --显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本。
--通过xp_cmdshell执行DOS命令
xp_cmdshell 'dir c:'    --查询c盘目录
xp_cmdshell 'net user abc password:123 /add'    --创建WindowXP用户
xp_cmdshell 'md c:\abc'    --创建文件夹
exec sp_databases                --列出当前系统中的数据库
exec sp_renamedb 'demo','test'    --改变数据库名称
exec sp_tables                    --查看当前数据库中可查询对象的列表
exec sp_help Student            --查看表Student的所有信息
exec sp_helpconstraint Student    --查看表Student的约束
exec sp_helptext CK_Student_sAge
exec sp_helptext v_Student        --查看视图的语句文本
exec sp_helptext 'sys.objects'
--附加数据库
sp_attach_db
exec sp_attach_db @dbname='test',@filename1='C:\test.mdf',@filename2='C:\test_log.LDF'
--分离数据库
use master
exec sp_detach_db test

--创建存储过程
定义存储过程的语法
    CREATE  PROC[EDURE]  存储过程名 
    @参数1  数据类型 = 默认值 OUTPUT,
    @参数n  数据类型 = 默认值 OUTPUT
    AS
      SQL语句
--执行存储过程
EXEC  过程名  [参数]

--创建带默认值参数的存储过程
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)
go
exec sp_helptext usp_upGrade
--调用存储过程
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 top @pageSize * from tb_ProduceOrder
where Id not in (select top @pageSize*(@pageIndex-1) Id from tb_ProduceOrder)

--分页存储过程
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))

--执行分页存储过程
declare @count int
exec usp_GetPageData 3,3,@count output
print @count
select ceiling(7/convert(float,3))

----------------------------------触发器----------------------------------
CREATE TRIGGER triggerName ON 表名 
{after|for(for与after都表示after触发器)|instead of}
 UPDATE|INSERT|DELETE
AS 
begin 
…
end 

--创建触发器
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 '插入成功'

--当插入数据的时候就会引发触发器
insert into score(studentId,english) values(1,10)