sql server 基础知识点总结

数据库操作

设置当前操作数据库

USE [数据库名]
GO

创建数据库

CREATE DATABASE [新建数据库名]
ON  --数据文件
(
    NAME='WebShop_data',  --数据库逻辑名称
    FILENAME='E:\data\WebShop_data.mdf',   --数据库物理名称
    SIZE=5,    --初始大小，单位MB
    MAXSIZE=100,      --增长上线，单位MB
    FILEGROWTH=2  --增长量，单位MB、%
   
)
LOG ON --日志文件
(
    NAME='WebShop_log',
    FILENAME='E:\data\WebShop_log.ldf',
    SIZE=2,
    MAXSIZE=500,
    FILEGROWTH=10%
)

修改数据库

ALTER DATABASE [要修改的数据库名（逻辑名称）]
ADD LOG FILE  --添加一个日志文件
(
    NAME='WebShop_log1',
    FILENAME='E:\data\WebShop_log1.ldf'
)
ALTER DATABASE [要修改的数据库名]
MODIFY FILE   --修改文件（数据文件、日志文件）
(
    NAME='WebShop_data', --NAME='WebShop_log'
    MAXSIZE=500   --修改上限
)

删除数据库

USE master
GO
DROP DATABASE WebShop

数据类型

SELECT（查询）

查询语法

SELECT [表的列名]
FROM [表名];

WHERE（条件）

SELECT [表的列名]
FROM [表名];
WHERE [列名]=[数据信]

LIKE（相似）运算符

WHERE 【列名】 LIKE ‘数据%’    --该列以‘数据’开头的项都会查找到

IN（匹配）运算符

WHERE 【列名】 LIKE ‘数据%’    --该列以‘数据’开头的项都会查找到

介于之间BETWEEN运算符

WHERE 【列名】 BETWEEN 【数据1】 AND 【数据2】

ORDER BY（排序规则）

USE WebShop
GO
SELECT * FROM Goods
ORDER BY [列名1] DESC, [列名2] ASC;<span style="font-family: Arial, Helvetica, sans-serif; background-color: rgb(255, 255, 255);"> </span>

DESC:降序排列，默认是升序排列（ASC）

如果有WHERE语句，ORDER BY 语句应该放在其后面。

GROUP BY（聚合信息）

SELECT g_Discount,SUM(g_Price)
FROM Goods
WHERE g_Discount IN(0.9,0.8)
GROUP BY [需要聚合的组]（列名）

说明：

         使用GROUP BY 子句时，必须要使用聚合函数，

聚合函数

聚合函数一般和GROUP BY 子句配合使用

SUM（求和）

AVG（平均值）

MIN（最小值）/MAX（最大值）

COUNT(*)

查询中返回的行数

除了COUNT(*)，其他聚合函数都会忽略null值，不加以计算

COUNT 可以和GROUP BY 子句联合使用

SELECT g_Discount,COUNT(*)
FROM Goods
GROUP BY g_Discount

HAVING

注意：

在group by 之前筛选用where，筛选的是分组前的数据

在group by 之后筛选用having，筛选的是分组后的数据

HAVING子句仅用于带有GROUP BY子句的查询中

SELECT g_Discount,COUNT(*)
FROM Goods
GROUP BY g_Discount

DISTINCT

去除重复行

SELECT DISTINCT c_Postcode FROM Customers

也可以在聚合函数中使用

SELECT COUNT(DISTINCTc_Postcode) FROM Customers

TOP

查询前5行的的数据

</pre><pre name="code" class="sql">select top 5 * from goods

查询前百分之50的数据

select top 50 percent * from goods

ISNULL

查询，判断如果是null值，则替换为其他

select isnull(name,'sdsdsdsds')from ren

解释：判断如果name字段为空，则替换为sdsdsdsds，

cast

查询时将列的数据类型转化成其他类型，可以和isnull一起使用

select ISNULL(CAST(gonghaoasvarchar(30)),'sdsd')from ren

解释：将gonghao字段的数据类型转换成varchar(30)类型，在判断gonghao类型是否为空，为空则替换为sdsd，

select语句的执行顺序

5)select

1)from 表

2)where 条件

3)group by 列

4)having 筛选条件

6)order by 列

union

将两个表的查询结果放在一个结果集中，

但是两个表的字段类型必须一致

实例：

select *from Goods
union all –-不加all会去掉重复行，加all显示所有数据
select * from Goods –-可以是不同的表

case

case语句有点类似于C#中的if else语句

实例1：区间用法

select
    g_ID,
    g_Name,
    等级=
    case --类似于if else分段
        when g_Price<=2000 then'便宜'
        when g_Price<=4000 then'一般'
        else '奢侈'
    end
from goods

实例2：等值用法

select
    g_ID,
    g_Name,
    等级=
    case g_Price
        when 1500 then '便宜'
        when 2500 then '一般'
        else '奢侈'
    end
from goods

复制表

select top 5 * into newgoods from goods

exists

--exists中包含一个查询语句的查询结果,有结果就返回true,没结果就返回false
if(exists(select*fromGoods))
begin
    print('true执行这里');
end
else
begin
    print('false执行这里');
end

row_number()

给查询的结果添加一列排序编号

select * ,ROW_NUMBER()over(orderbyg_id desc)as gnumber from goods

分页

查询某一页的数据

实例1：查询第二页的数据（假设每页5条数据）

先将第一页之后的数据查询出来，再取查询到的第一页的数据就是第二页了

这是sql server 2000时的做法

select top 5*from goods
where g_IDnotin
(
    select top （2*5） g_ID fromgoods
)

实例2：

sql server 2005 做法

查询第二页的数据

select *from
(
    select * ,ROW_NUMBER() over(orderbyg_id desc) as gnumber from goods
) astb
where tb.gnumber between 1*5+1 and 2*5

注意：

over关键字和聚合函数一起使用的时候，可以

让聚合函数对每一条数据都计算一次

子查询

独立子查询

意思就是两个查询之间没有相互引用

实例1：将一个表的查询结果集作为另一个表的查询数据源

select *from
    (selectg_ID,g_Name,g_Pricefromgoods) as g –-引用的结果集必须
用括号括起来，还要起别名才行
where g_Name like '三星%'

实例2：将一个查询结果当做另一个查询的where判断条件

当查询结果为多条的时候，用in关键字

select *fromgoods
where t_id in(select t_IDfromGoods where g_Discount=0.8andg_Number=10)

相关子查询

两个查询之间有引用关系

临时表

在创建表时表名前面加一个#号，表示局部临时表，加##号，表示全局临时表

创建的临时表可以在tempdb数据库中看到，并不是保存在自己的数据库中，

局部临时表

只在当前会话中有效，不能跨连接访问，断开连接后消失

实例：

create table #test
(
    id  int identity
)

全局临时表

所有连接都可以访问，服务重启后消失

实例：

create table ##test2
(
    id  int identity
)

字符串

len():计算字符串长度（字符的个数）

lower(),upper():转小写，大写

ltrim(),rtrim():去掉左，又的空格

，，，，，，

INSERT（添加）

语法：

简洁版

注意：into关键字可选，没有实际用处

一次性插入多行

insert intoUsers
select 31,'qwe','普通','8669966'union --去掉重复
select 54,'sdf','超级','dfgvsdr'unionall --不去重复
select 65,'fdg','haha','hfydfhh'

向自动编号列插入数据

--打开向自动编号插入数据的权限
set identity_insert users on
--关闭向自动编号插入数据的权限
set identity_insert users off

DROP语句

删除数据库

drop database lianxi

联接（join）

内部联接（inner join）

语法：

SELECT<select list>

FROM<first_table(第一个表名)>

<join_type（连接类型）><second_table（第二个表名）>

                   [ON <join_condition>（连接条件）]

<join_type（连接类型）><second_table（第三个表名）>

                   [ON <join_condition>（连接条件）]

。。。。。。

内部联接是一个排他的联接，即会排出不匹配的所有信息（与where语句有些相似）

实例：

select *
from Orders as o
inner join OrderDetails as od  --内部连接
on o.o_ID=od.o_ID

inner join(内部链接)为默认连接，所以inner关键字可以不使用

select *
from Orders as o
join OrderDetails as od  --内部连接
on o.o_ID=od.o_ID

外部联接（outer join）

语法：

select <select list>
from <第一个表名>
<left | right> [outer]join<第二个表名>
    on <连接条件>

注意：

使用left时，右边的表的列与左边不匹配时，会插入一个null值，

使用right时，左边的表的列与右边不匹配时，也会插入一个null值。

Outer关键字可以省略

完全联接（full join）

交叉联接（cross join）

CREATE语句

create database

语法：

实例：

use master
go
create database <databasename>

完整

CREATE DATABASE [新建数据库名]
ON  --数据文件
(
    NAME='WebShop_data',  --数据库逻辑名称
    FILENAME='E:\data\WebShop_data.mdf',   --数据库物理名称
    SIZE=5,    --初始大小，单位MB
    MAXSIZE=100,      --增长上线，单位MB
    FILEGROWTH=2  --增长量，单位MB、%
   
)
LOG ON --日志文件
(
    NAME='WebShop_log',
    FILENAME='E:\data\WebShop_log.ldf',
    SIZE=2,
    MAXSIZE=500,
    FILEGROWTH=10%
)

create table

语法：

实例：

create table Customers
(
    CustomerNo    int           identity   not null,
    CustomerName  varchar(30)                notnull,
    Address1      varchar(30)                notnull,
    Address2      varchar(30)                notnull,
    City          varchar(30)                notnull,
    State         char(20)                notnull,
    Zip           varchar(10)                notnull,
    Contact           varchar(25)                notnull,
    Phone         varchar(15)                notnull,
    FedIDNo           varchar(9)              notnull,
    DateInSystem  smalldatetime            not null,
)
Employees
create table Employees
(
                                --设置种子是1，增量是1
    EmployeeID    int        int identity(1,1)notnull,
    firstName     varchar(25)         notnull,
    MiddleInitial char(1)             notnull,
    lastName      varchar(25)         notnull,
    Titly         varchar(25)         notnull,
    SSN           varchar(11)         notnull,
    Salary        money             not null,
    PriorSalary       money             not null,
    lastRaise AS Salary-PriorSalary,
    HireDate      date              not null,
    TerminationDate   date              null,
    ManagerEmpID  int               not null,
    Department    varchar(25)         notnull
)

IDENTITY关键字

将某一列设置为标示符列，可以设置种子和增量

默认种子是1，增量是1

ALTER语句

alter database

实例：

修改数据库大小

alter database Accounting
    modify file
    (
    name='Accounting',
    size=100mb
    )

alter table

实例：

向Employees表中添加新列

Alter table Employees
    add
       PreviousEmployer varchar(30) null

添加多列，并添加约束

alter table Employees
    add
       DateOfBirth       datetime null,
       lastRaiseDate datetime not null
           default '2008-01-01'--设置默认值(不是"默认值"约束)

删除一个表中的列

alter table Employees
    drop columnhaha

修改字段数据类型

alter table mployees
    alter column firstName
    varchar(30)

DROP

其语法对于主要的SQL server对象（表，视图，存储过程及触发器等）来说都是一样的

使用时要小心，改命令没有“你确定吗”这样的提示

语法：

drop <objecttype> <object name>[, ...n]

实例：

可以同时删除两个表

drop table Customers,Employees

删除整个数据库

drop database Accounting

truncate

清空表中所有数据，比delete效率高很多，但不能添加where子句，只能一次性清空整个表

truncate table users_bf

约束

域约束

域约束处理一个或多个列

实体约束

实体约束都是关心与每一行的

参照完整性约束

某列的值必须与其他列的匹配

键约束

主键约束（PRIMATY KEY）

实例:

方法一、在建表的时候设置

USE WebShop
GO
create tablehuoying
(
    hy_id  int        not null primary key,   
    hy_name    varchar(30)  notnull
);

方法二、在已存在的表中创建主键约束

alter table huoying
add constraint PK_hy_id
primary key (hy_id);

外键约束（FOREIGN KEY）

在引用表(定义外键的表)与被引用表(外键引用的表)之间创建依赖关系

外键创建之后,外键所在列的值必须与被引用列的值相匹配,或者外键列为null

被引用列必须是主键或者唯一约束

主键和外键可以是同一列

语法：

<colum nname> <data type> <nullability>
foreign key references <tablename>(<columnname>)
    [on delete{cascade|no action|set null|set defaule}]
    [on update{cascade|no action|set null|set defaule}]

实例

方法一、在创建表时创建外键约束。

create tablerenhu
(
    r_id       int        not null
       primary key,
    r_name     varchar(30)  notnull,
    r_paibie   varchar(30)  notnull,
    h_id       int        not null
       foreign key references huoyin(h_id)      
)

注意：

和主键不同，每个表中的外键数目不限制唯一性，在每个表中可以有0~253个外键。唯一的限制是一个给定的列只能引用一个外键。然而，一个外键可以涉及多个列。一个给定的被外键引用的列也可以被很多表引用。

方法二、在已存在的表中添加一个外键

alter tablerenwu
    add constraint FK_h_id
    foreign key (h_id)references huoyin(h_id)

自引用

自引用:外键引用表和被引用表是同一个表

在创建之前必须要先有一行数据

实例:

方法一,在已创建表中修改

alter tablegoods
    add constraint FK_g_id
    foreign key (t_id)references goods(g_id)

方法二,创建表时添加

create tablexinbiao
(
    x_id   int    not null
       primary key,
    b_id   int    not null
       references xinbiao(x_id)
)

这时可以省略FOREIGN KEY语句

唯一约束（UNIQUE）（替换键）

每个表中主键只能有一个，但可以有多个唯一约束

唯一约束列只能有一个null值

实例：

方法一，在创建表时添加

create table xinbiao1
(
    x_id   int    not null
       primary key,
    b_id   int    not null
       unique  --设置唯一约束
)

方法二，在已存在表中添加

alter table Employees
    add constraint AK_name
    unique(e_Name)

CHECK约束

它不限于一个特定的列，它可以和一个列关联，也可以和表关联，

可以使用WHERE子句一样的约束来定义check约束。

实例：

方法一，在已存在表中添加约束

alter table Customers
    add constraint CN_customers_C_Gender
    check
    (c_Genderin('男','女'));

DEFAULT约束

设置默认值，但又不同于“默认值”

实例：

方法一，在创建表时添加约束

create table Shippers
(
    shipperID     int identity   notnull
       primary key,
    ShipperName       varchar(30)      notnull,
    DateInSystem  smalldatetime not null
       default getdate() --设置约束默认值为系统当前日期
);

方法二，在已存在的表中添加约束

alter table Customers
    add constraint CN_c_Birth
    default getdate() for c_Birth

禁用约束

不能禁用主键约束和唯一约束

创建约束时忽略无效数据

实例：

alter table customers
    with nocheck
    add constraint CN_c_Postcode1
    check
    (c_Postcodelike '([0-9][0-9][0-9])')

临时禁用已存在的约束

alter table customers
    nocheck
    constraint CN_c_Postcode1

再次启用约束

alter table customers
    check
    constraint CN_c_Postcode1

优缺点

索引

索引分为

聚集索引（聚簇索引）

         当数据实际的存储顺序与索引的顺序一致的时候就叫聚集索引

非聚集索引（非聚簇索引）

         当索引中数据的顺序与数据实际的顺序不一致的时候，就叫非聚集索引

视图(view)

语法：

CREATE VIEW <view name>
AS
<SELECT statement>

更加复杂的语法：

CREATE VIEW [schema_name].<view name>  [(<column name list>)]
[WITH [ENCRYPTION] [, SCHEMABINDING] [,VIEW_METADATA]]
AS
    <SELECTstatement>
    WITH CHECK OPTION

注意：视图本身并不存储数据，只是存储查询语句，

实例：

创建视图

create view vw_goods
as
    select * from goods
    where g_Price>4000;

修改视图

alter viewv w_goods
as
    select * from goods
    where g_Price<4000;

删除视图

drop view vw_goods

注意：

         视图中不能直接使用order by 子句，除非指定了 TOP，FOR XML语句

脚本和批处理

USE

设置当前操作的数据库

GO

GO语句最好自成一行（只有注释可以在同一行）

用来分隔批处理

declare（定义变量）

实例：

declare @myInt int=3;   --声明一个int型变量
set @myInt=5;    --使用set关键字修改变量的值
select @myInt=8; --使用select关键字修改变量的值
select @myInt    --使用select关键字查询变量的值

注意：

简单变量复制用set

基于查询进行变量赋值时，用select

实例：

declare @myInt int;
select @myInt=MAX(g_Price)fromgoods; --将查询结果赋值给变量
select @myInt

系统函数

sqlcmd

流程控制语句

if…else

注意：

if @myvar=null，这样写是错误的

正确的是这样的if @myvar is null

实例:

declare @myint int=9;
if(@myint>8)
    begin             --要执行多个语句就用begin...end
       print '343545'
       print '345345'
    end
else
    print 'asdsad'

case

case语句有点类似于C#中的if else语句

实例1：区间用法

select
    g_ID,
    g_Name,
    等级=
    case --类似于if else分段
        when g_Price<=2000then'便宜'
        when g_Price<=4000then'一般'
        else '奢侈'
    end
from goods

实例2：等值用法

select
    g_ID,
    g_Name,
    等级=
    case g_Price
        when 1500 then '便宜'
        when 2500 then '一般'
        else '奢侈'
    end
from goods

while

实例：计算100以内的奇数的和

declare @sum int=0;
declare @i int=1;
while @i<=100
    begin
       if @i%2<>0
           begin
              set @sum=@sum+@i
           end
       set @i=@i+1      
    end
print @sum

注意：

         continue:跳过本次循环

事务

一系列的操作就叫事务
准备：
create tablebank
(
    cid    varchar(30),
    balance int ,
    check (balance>=10)
)
 
insert into bank values('0001',1000)
insert into bank values('0002',10)
 
select *frombank

--============================================================================
正题：
--打开事务
--begin transaction
--可以简写
begin tran
declare @sum int = 0;--创建一个变量记录错误信息
update bank set balance=balance-1000 where cid='0001'
set @sum=@sum+@@ERROR--每执行一句就记录一次错误信息
update bank set balance=balance+1000 where cid='0002'
set @sum=@sum+@@ERROR
if @sum<>0 --有错误@sum>0
begin
    rollback --有错误就回滚
end
else
begin
    commit   --没错误就提交事务
end

通过捕获异常处理

begin tran--打开事务
begin try --捕获异常
    update bank set balance=balance-1000 where cid='0001'  
    update bank set balance=balance+1000 where cid='0002'  
    commit --提交
end try
begin catch--处理异常
    rollback --回滚
end catch

存储过程

特点：

         有点类似与函数，速度相对比较快，因为是提前编译好的，省去了编译的步奏

实例：

create proc spEmployees
as
    select * from Employees

注意：

使用DROP和CREATE，ALTER和PROC结果几乎一样，只是后者保留了相关权限和依赖关系，前者则没有保留

删除存储过程

drop proc spEmployees

参数

名称，数据类型，默认值，方向

注意：

         如果参数不提供默认值，则假定参数是必须的，就是说，在调用存储过程时，没有默认值的参数必须给定值，否则会报错

实例：

先创建一个存储过程，不给定默认值

create proc spgoods
    @name nvarchar(20)
as
    select g_ID,g_name,t_id from goods
    where g_Name like @name+'%';

当执行存储过程时，如果不给定参数

exec spgoods

会报异常

消息201，级别16，状态4，过程spgoods，第0行

过程或函数'spgoods' 需要参数'@name'，但未提供该参数。

使用参数后

exec spgoods'三星'

正常查询到了

g_ID   g_name                                            t_id

------ ------------------------------------------------------

010002 三星SGH-P520                                         01

010003 三星SGH-F210                                         01

010004 三星SGH-C178                                         01

010005 三星SGH-T509                                         01

010006 三星SGH-C408                                         01

 

(5 行受影响)

带参数的存储过程

新建存储过程

alter proc sp_sum
@n1 int,
@n2 int=200
as
    begin
       print @n1+@n2
    end

调用存储过程

exec sp_sum10

结果

210

修改存储过程

alter proc sp_sum
@n1 int=100,
@n2 int
as
    begin
       print @n1+@n2
    end

注意：

不能这样调用了

exec sp_sum10

结果

消息201，级别16，状态4，过程sp_sum，第0行

过程或函数'sp_sum'需要参数'@n2'，但未提供该参数。

可以显示指定参数

exec sp_sum @n2=100

带输出参数的存储过程

先修改存储过程

alter proc sp_sum
@n1 int,
@n2 int,
@sum int output–-类似与C#中的out参数
as
    begin
       print @n1+@n2
    end

执行

declare @sum int
exec sp_sum 100,200,@sum output --这里也要用output
print @sum

用户登录

--先创建一个用户表
create table users
(
    id  int identity(1,1),
    uid varchar(30),
    pwd varchar(30)
)
--添加一个用户
insert into users
(uid,pwd)
values
('qwe','qwe')
 
 
select *fromusers;
 
 
 
--创建存储过程,验证用户登录
create proc ulogin
(
    @uid varchar(30),
    @pwd varchar(30),
    @tag bit output
)
as
begin
    declare @count int
    set @count=(
    select COUNT(*) fromusers
    where uid=@uid and pwd=@pwd)
    if @count>0
       set @tag=1
    else
       set @tag=0
end
 
 
--调用存储过程,验证用户登录
declare @tag bit
exec ulogin'qwe','qwe',@tagoutput
print @tag

触发器

触发器分为

DML触发器：针对数据本身（如，insert，delete，update）

DDL触发器：针对数据库里的对象（如，alter，create，drop）

DML触发器

语法：

create trigger triggerName on 表名
after(for)(for和after都表示after触发器)|insert of
(update|insert | delete)
as
begin
    ......
end

After触发器

1、  在语句执行完毕之后触发

2、  按语句触发，而不是所影响的行数，无论所影响为多少行，只触发一次

3、  只能建立在常规上，不能建立在视图和零时表上

4、  可以递归触发，最高可达32级

5、  update（列），在update语句触发时，判断某列是否被更新，返回bool值

实例：

创建触发器

create trigger tri_goods_insert_after
on goods after insert  --这里可以是多个（insert，delete，update）
as
begin
    declare @id int
    select @id=g_id from inserted –-这是一个系统临时表
   
    print @id
end

解释：

         当上面这个触发器创建好之后，只要向goods中执行insert（插入）操作，就会将你刚插入的记录的g_id打印出来，

实例2：

创建触发器

create trigger tri_goods_delete_after
on goods after delete
as
begin
    insert into goods_bf2
    select * from deleted   
end

解释：

         当删除goods中的一条记录时，会将那条记录备份到goods_bf2表中

instead of触发器

1、  用来替换原本的操作

2、  不会递归触发

3、  可以在约束被检查之前触发

4、  可以建在表和视图之上

注意：

         尽量避免在触发器中执行好事操作，因为触发器会与sql语句认为在同一个事务中（事务不结束，就无法释放锁，别人就不能操作这个表）

各种符号说明

*

代表任意字符或字符串

%

代表任意字符串

_

代表单个字符

系统存储过程

语法前面加上exec 输入sp语法时可以智能联想

exec sp_help<table name>

它可以查看表结构的信息

exec sp_helpconstraint <table name>

查看表中约束的详细信息

exec sp_helpindex<table name>

查看表的索引

exec sp_helpdb<database name>

查看数据库的信息，省略name可以查看所有数据库的信息

exec sp_databases

查看当前可使用的数据库

exec sp_helpfile

查看当前数据库中的所有文件信息，可以指定文件名

exec sp_helpfilegroup

查看当前数据库中的所有文件组信息，可以指定文件组

exec sp_renamedblianxi,lianxi1

数据库重命名

exec sp_dependsEmployees

查看表的相关性

exec sp_helptextsp_help

查看存储过程的源代码

 

exec sp_tables

查看当前数据库的表信息

exec sp_columns'goods'

查看指定表的列信息

备份

备份表

--将users表备份到users_bf表

--users_bf表必须不存在

select * into users_bf fromusers

遇到的一些错误

1、  错误：无法绑定由多个部分组成的标示符

原因：给表取了别名，但是并没有在所有地方都使用别名（混合使用），所以出错

解决：要么不使用别名，要么一直使用