一、SQL基础语句

1.与数据库有关的SQL语句

1.1创建数据库

create database 数据库名     （SQL不区分大小写，单行注释--  多行注释/**/）

1.2使用数据库

use  数据库名  

1.3删除数据库

drop database 数据库名（注意删除数据库时，不能同时使用这个数据库）

1.4查看数据库

exec sp_Helpdb 数据库名   （查看结果）

select* from 数据库名 （*代表全部，也可以改成数据表中的列名）

1.5重命名数据库

exec sp_renamedb '原名字'  ，‘新名字’

1.6数据库备份

backup database 数据库名 to disk='路径.bak'

1.7还原数据库

restore database 数据库名 from disk='路径.bak' with replace

1.8分离数据库

exec sp_datach 数据库名

2.与数据表有关的SQL语句

2.1创建表

create table 数据表名

(

字段名1  数据类型1，

字段名2  数据类型2.

)

例如：create table [User]    (注意：User在SQL中是关键字所有需要加[  ])

(

UserId int,

UserName nvarchar(50)

)

2.2查看表

exec sp_help 数据表名     查看表结构

select  *   from  数据表名   

2.3重命名数据表

exec sp_rename '旧表名' ，’新表名‘

2.4修改字段名

exec sp_rename '旧字段名’，‘新字段名’

2.5修改指定字段名的数据类型

alter table 数据库名 alter column 字段名 新数据类型

2.6增加字段

alter table 数据库名 add  字段名 数据类型

2.7删除字段

alter table 数据库名 drop column 字段名

2.8删除数据表

drop table 数据表名

3.SQL其他语句

count（）聚合函数，group by 分组，进行筛选时不能用where，要用having
例如：select Age ,count(*) as‘个数’from Student group by Age having coungt(*)>=2
分组时一定不要用*，*代表所有，后期其他内容不能放到聚合函数或group by中

as后面的是别名，as可以省略，别名不能当作筛选条件
distinct  不重复的
count聚合函数不去重，但是可以在括号内写入distinct进行去重

between....and.....相当于>=  <=
print相当于Console.WriteLine
定义变量：declare  @abc int
赋值两种方法：1、select @abc=123
2、set @abc=123    (推荐)
判断条件
if  @abc=123
begin
   print(成立)
end
else
    print(不成立）
end

3.1添加数据

insert into 表名(列名1,列名2,列名n) values(列名1的值,列名2的值,列名n的值)   单条添加

例如：insert into BW_Student(Name,Age) values('张三',22)

例如：insert into BW_Student(Name,Age) values('张三',22),('王五',23),('张二',24)

insert into 表名 values(值1,值2,值3,值4,值5)    完整添加

例如：insert into BW_Student values('张三',22,1),('王五',23,0),('张二',24,1)

注意：在使用此格式时，values()中的值必须与表中的所有字段建立一一对应关系，自增字段除外。

3.2修改数据

update 表名 set 字段名1=值1,字段名2=值2,字段值n=值n

例如：update BW_Student set Name='小明'

update 表名 set 字段名1=值1,字段名2=值2,字段值n=值n where 条件表达式

例如：update BW_Student set Name='李小朋' where Age=22

3.3删除数据

delete from 表名

delete from 表名 where 条件表达式

例如：delete from BW_Student where Age=22

二、SQL数据类型

整数数据类型：tinyint(byte字节型)、smallint(short短整型)、int、bigint（long长整型）

1. Tinyint占1个字节的存储空间，相当于C#中的byte字节型。
2. Smallint占2个字节的存储空间：smallint类型只能用来存储整数，范围为-2^15 (-32,768) 到 2^15-1 (32,767)。
3. Int占4个字节的存储空间：int是最常用的整数类型，范围是-2^31 (-2,147,483,648) 到 2^31-1 (2,147,483,647)内的所有整数。
4. Bigint占8个字节的存储空间：能存储更大的整数，范围为：-2^63到 2^63-1内的所有整数。

字符数据类型：

固定长度的字符串：char(×)  nchar(×),

x表示指定的长度，能存储的最大字符数

即char(5)最大存储5个字节，未储存满，剩余空间浪费，固定存储空间为5

可变长度的字符串：

varchar(m)  nvarchar(m)

存储空间不满时，剩余空间释放，存储空间为实际数据长度+2个字符开销（长度开销）

Nchar(m)和Nvarchar(m)可以不用区分字母和汉字，n表示每一个实际占用空间，m表示个数。实际开辟空间=n*m.字母字符占1个字节，汉字2个字节。

浮点型数据类型：

Real 单精度浮点型，相当于C#中的float，4个字节

Float双精度浮点型，相当于C#中的double，8个字节

精准数据类型：

Decimal  Numberic 都是2-17个字节，功能一样

格式：decimal（18，2）18是总长度，2是小数位数，小数点不计入总长度。例如：3213.23==decimal（6，2）

货币数据类型：

Money  8个字节 精度始终为小数后4位

Smallmoney 4个字节 精度始终为小数后4位

日期时间型：

Datetime(日期时间，8字节)date(日期3字节)time(时间5字节)

Getdate()获取当前时间相当于DateTime.Now

convert（date,getdate()）将日期时间转化为日期

convert（time,getdate()）将日期时间转化为时间

全球唯一标识数据类型：uniqueidentifier

通过newid()获取

Guid newID=Guid.NewGuid（）；C#中的全球唯一标识符

三、关联表

多表之间的关联：1。外联接，2。内联接  3。交叉连接 cross join 4。自连接

-- 1。外联接：
-- a.左联接  left outer join 省略left join  用的最多（1:N或N:1）
-- 以左表为主，左表有的都有，右表有部分（和左表交叉的部分）

select * from Teacher
left outer join Depart on Teacher.DeptId = Depart.DepartId

select 
    T.TeacherId,T.TeacherName,T.DeptId,D.DepartName,
    T.Status,T.CreateUserId,U.Account as CreateUserName,
    T.CreateTime,
    T.LastUpdateUserId,U1.Account as LastUpdateUserName,
    T.LastUpdateTime
from Teacher as T
left join Depart D on T.DeptId = D.DepartId
left join [User] U on T.CreateUserId = U.UserId
left join [User] U1 on T.LastUpdateUserId = U1.UserId

-- b.右联接  right outer join 省略right join
-- 以右表为主，右表有的都有，左表有部分（和右表交叉的部分）
select * from Depart D
left join College C on D.CollegeId = C.CollegeId

select * from Depart D
right join College C on  D.CollegeId = C.CollegeId

-- c.全联接　full outer join 省略full join
select * from Depart D
full join College C on  D.CollegeId = C.CollegeId

-- 2。内联接（连接）inner join

select * from Depart D
inner join College C on  D.CollegeId = C.CollegeId

--3。交叉连接
select * from Depart D
cross join College C 

-- 4。自连接
--a.自连接为单个表取不同的别名，通过别名来连接；
--b.自连接可以用于其它连接；
--b.自连接可以看作交叉连接、内连接、外连接等连接的一个特例;
select 
  U1.UserId,U1.Account,U1.Password,U1.Type,U1.Logo,U1.Status,
  U1.CreateUserId,U2.Account CreateUserName,U1.CreateTime,
  U1.LastUpdateUserId, U3.Account LastUpdateUserName,U1.LastUpdateTime
from [User] U1
left join [User] U2 on U1.CreateUserId=U2.UserId
left join [User] U3 on U1.LastUpdateUserId=U3.UserId

-- union all不是联接查询，而是集合操作，用于纵向合并两个或多个查询的结果集。
select * from [User]
union all
select * from [User]
 

四、视图

--视图概念？视图的特点？
--SQL Server 视图就是个虚拟表，看着像表但实际不存数据，内容靠查询语句动态生成。
--特点：
--不占空间：只存定义，数据还在原表里，基表变视图也跟着变，索引视图除外。

--操作有限：和表相比，操作受限，能查数据，部分能改，但太复杂的视图不让改，比如带统计的。
--一般不让从视图中删除，增加数据，应该对视图需要的真实表进行删除，增加数据，视图中数据就变化了。

--动态生成：每次查视图都重新跑查询，保证数据是最新的。

-- 判断视图是否存在，存在先删除，再创建
if esists

(
    SELECT * FROM sys.views WHERE name = 'ViewDept'

) 
    drop view ViewDept   --删除视图
go

--创建视图
create view ViewDept
as
    -- 视图中只有查询语句
    -- 只对一个表做一个简单“封装”，意义不大
    --select * from Depart
    -- 对多个表中的结果集做一个“封装”，产生一个虚拟表，才有意义。
    --case...when...
    select 
        D.DeptId,D.DepartName,D.CollegeId,C.CollegeName,
        D.Status,
        --case D.Status  when 0 then '正常'  when 1 then '删除' else '未知' end as StatusText,
        case 
        when D.Status=0 then '正常' 
        when D.Status=1 then '删除'
        else '未知'
        end 
        as StatusText,
        D.CreateUserId,U1.Account CreateUserName,
        D.CreateTime,
        D.LastUpdateUserId,U2.Account LastUpdateUserName,
        D.LastUpdateTime
    from Depart D
    left join College C on D.CollegeId=C.CollegeId
    left join [User] U1 on D.CreateUserId=U1.UserId
    left join [User] U2 on D.LastUpdateUserId=U2.UserId

go

--使用视图（一般只对视图查询，不做增删改）
select * from ViewDept 
where DeptId<=3
order by DeptId desc

五、索引

--索引：为了查询表速度快，而定义的一种数据结构。

if exists(
    select * from sysindexes 
    where id=object_id('Depart') and 
    name='IX_DepartName'
)
    drop index IX_DepartName on Depart;
go

create  index IX_DepartName on Depart (DepartName);

--按索引查询，性能高，将来数据量比较大，索引优势才能体现，少量看不效果。
select * from Depart 
where DepartName  like '%系%'
 

六、存储过程

--SQL Server 存储过程是一组预编译的SQL语句集合。
--大白话：在存储过程中封装一堆业务逻辑（SQL语句）
-- 判断存储过程是否存在
IF exists(
    SELECT * FROM sysobjects 
    WHERE id=object_id(N'dsh_add_depart') 
    and xtype='P'
)
    drop procedure dsh_add_depart
go

-- 定义存储过程，dsh_add_depart存储过程名称，自定义存储过程避开  #，##，sp_
create proc  dsh_add_depart
    --存储过程的参数，参数名称要求和变量名称一致，必须以@开头，每一个参数结束英文逗号分割，最后一个参数英文逗号必须省略。
    --如下定义的参数都【输入参数】，参数也可以有默认值，默认值并不是必须的。
    @deptName varchar(50) = 'dsh',
    @collegeId int = 1,
    @createUserId int = 1
as
begin
    print('这是一个简单的存储过程');
    -- 将来业务逻辑更很杂，代码量更多，需要编写更多的T-SQL
    insert into Depart(DepartName,CollegeId,CreateUserId) 
    values(@deptName,@collegeId,@createUserId)
end

--如何调用存储过程  execute执行
execute dsh_add_depart
exec dsh_add_depart '部门1',1,2

--带输出参数及返回值的存储过程
IF exists(
    SELECT * FROM sysobjects 
    WHERE id=object_id(N'dsh_output') 
    and xtype='P'
)
    drop procedure dsh_output
go

create proc dsh_output
    @inValue1 int=100, -- 带默认值的输入参数
    --输出参数，输出参数可以有多个，且可以有默认值，但默认值无效。
    @outValue1 int=200 output,
    @outValue2 varchar(50) output
as
begin
    print('aaaa');
    -- 一般输出参数，在存储过程中要赋值
    set @outValue1=300
    select @outValue2='hello sql'
    --return '返回值' -- 存储过程中的返回值，必须是int类型
    return 2;
end
go

--调用带输出参数的存储过程
-- 定义两个变量，用来接收输出参数的值
declare @ov1 int
declare @ov2 varchar(50)
--定义变量@returnValue，用来接收返回值
declare @returnValue int;
exec @returnValue = dsh_output 300,@ov1 output,@ov2 output
--打印输出参数的值
print @ov1
print @ov2
print @returnValue

--练习存储过程
IF exists(
    SELECT * FROM sysobjects 
    WHERE id=object_id(N'dsh_add_dept') 
    and xtype='P'
)
    drop procedure dsh_add_dept
go

create proc dsh_add_dept
    @deptName varchar(50),
    @collegeId int,
    @createUserId int
as
begin
    --if @deptName is null
    --begin
    --    print('');
    --    throw 10000, '部门名称不能为null', 1;
    --end

    --print('定义一个存储，插入成功返回0，插入失败返回1');

    -- 捕获异常
    begin try
        print('begin try用来捕获异常');
        insert into Depart(DepartName,CollegeId,CreateUserId)
        values(@deptName,@collegeId,@createUserId)

        --delete from Depart where DeptId = 10000;
        ----如果前一个 Transact-SQL 语句执行没有错误，则返回 0
        --if @@ERROR=0
        --begin
        --   print('上一行语句没有错误');
        --end

        -- @@ROWCOUNT内置常量，表示SQL执行后影响的行数
        if @@ROWCOUNT>0
            return 0;
        else 
            return 1;
    end try
    begin catch
        throw 10000, '部门名称，学校编号，创建人编号参数有误', 1;
        return 1;
    end catch
end
go

--调用存储过程
declare @rv int; --用来接收返回值
exec @rv=dsh_add_dept '部门2',2,1
print @rv;