SQL基础操作培训

1 SQL 语句的常用操作
1.1 创建数据库
举例：
CREATE DATABASE Test  --数据库名称
ON
 (
name=test_data, --逻辑名称
filename='d:\test_data.mdf', --物理名称
size=3mb, --初始大小
maxsize=15mb, --上限
filegrowth=1mb -- 每次增长1Mb
)
LOG ON
(
name=test_log,
filename='d:\test_log.ldf',
size=3mb,
maxsize=15mb,
filegrowth=1mb
)
1.2 创建表
举例：
Create Table Customer
(
 CustID int IDENTITY(1,1) PRIMARY KEY NOT NULL,
 CustName nvarchar(50) NOT NULL,
 City nvarchar(20) NOT NULL DEFAULT '暂无',
 Phone nvarchar (50) NULL,
 Address nvarchar (200) NULL
)
1.3 添加数据
方法1举例：
 INSERT INTO Customer(CustName,Phone,Address)
VALUES ('张三','18123556554','北京')

DECLARE @i INT
SET @i=0
WHILE @i<10
BEGIN
 INSERT INTO Customer(CustName,Phone,Address)
 VALUES ('李四'+convert(nvarchar(2),@i),'18123556554','北京')
 SET @i = @i+1
END
 方法2举例：可以复制表结构和数据，但不能复制主键，约束，索引等。
SELECT CustID,CustName,Phone,City
INTO Customer2
FROM Customer
1.4 添加修改删除表列
添加列举例：
ALTER TABLE Customer ADD CustNo int Null, CustAge int NULL
修改列举例：
  ALTER TABLE Customer ALTER COLUMN CustAge bigint NULL
 删除列举例：
  ALTER TABLE Customer DROP COLUMN CustAge
1.5 修改数据
方法举例：
 UPDATE Customer SET CustName = '王五' Where CustID = 12
1.6 删除数据
方法1举例：
 DELETE Customer WHERE CUSTID>15
方法2举例：删除全部数据没有WHERE子句，速度快
 TRUNCATE TABLE CustomerTemp
 TRUNCATE不能触发任何Delete触发器，DELETE对每条记录的删除均需要记录日志，TRUNCATE全部删除，速度比DELETE快，占资源少，并且只记录页删除的日志
1.7 删除表
举例：
 DROP TABLE Customer2
1.8 查询数据
举例：
 SELECT TOP 100 * FROM Customer WITH(NOLOCK)
WHERE City = '暂无' ORDER BY CustName 
大数据量大的时侯，没有加nolock有可能会锁表，加上了nolock可能会读取未提交的事务或一组在读取中间回滚的页面，有可能发生脏读的风险。
分组查询举例：
 UPDATE Customer SET SumCustNo = 1
SELECT Address,SUM(CustNo) AS SumCustNo
FROM Customer
GROUP BY Address
Having COUNT(*)>1
一般都和聚和函数使用，如果没有用聚合函数的话用DISTINCT来代替速度快。
1.9 SQL分类　　
DDL—数据定义语言(CREATE，ALTER，DROP，DECLARE)
DML—数据操纵语言(SELECT，DELETE，UPDATE，INSERT)
DCL—数据控制语言(GRANT，REVOKE，COMMIT，ROLLBACK)
1.10 SQL执行顺序
5>SELECT 5-1>列,5-2>DISTINCT,5-3>TOP
1>FROM 表
2>WHERE 条件
3>GROUP BY 列
4>HAVING 对组筛选条件
6>ORDER BY 列 排序
2 SQL SERVER的关联查询
CREATE TABLE CustomerRoom
(
 RoomID int identity(1,1) primary key not null,
 RoomSign nvarchar(50) not null,
 CustID int,
 RoomType nvarchar(50)
)
insert into CustomerRoom(RoomSign,CustID,RoomType)
select '1-1-1',1,'住宅'
union all
select '1-1-2',32,'商铺'
union all
select '1-1-3',34,'公寓'
union all
select '1-1-4',41,'写字楼'
union all
select '1-1-5',0,'写字楼'
2.1 内关联连接INNER JOIN
内关联连接查询结果只显示满足的条件。
举例：
 SELECT *
FROM Customer AS a
INNER JOIN CustomerRoom AS b
ON a.CustID = b.CustID
 和如下语句得到的信息是一样的：
 SELECT a.*,b.RoomSign, b.RoomType
FROM Customer AS a, CustomerRoom AS b
WHERE a.CustID = b.CustID
2.2 左外关联连接 LEFT JOIN(即LEFT JOIN)
左外关联连接结果显示左表所有记录，右表符合JOIN条件的信息显示，不符合的置空。
举例：
SELECT *
FROM Customer AS a
LEFT JOIN CustomerRoom AS b
ON a.CustID = b.CustID
2.3  右外关联连接 RIGHT OUTER JOIN(即RIGHT JOIN)
右外关联连接和左关联连接正好相反，结果显示右表所有记录，左表符合JOIN条件的信息显示，不符合的置空。
举例：
SELECT *
FROM Customer AS a
RIGHT JOIN CustomerRoom AS b
ON a.CustID = b.CustID
2.4 完全外关联连接 FULL OUTER JOIN(即FULL JOIN)
完全外关联连接结果显示左右两表的全部记录，符合join条件的信息显示，不符合的置空。
SELECT *
FROM Customer AS a
FULL JOIN CustomerRoom AS b
ON a.CustID = b.CustID
2.5 合并行关联连接 UNION，UNION ALL
SELECT *
INTO Customer_temp
FROM Customer
WHERE CUSTID = 1
Ø 合并行关联连接 UNION结果满足条件的行
举例：
SELECT * FROM Customer
UNION
SELECT * FROM Customer_temp
Ø 合并行关联连接 UNION ALL结果合并两个表中的所有行
举例：
SELECT * FROM Customer
UNION ALL
SELECT * FROM Customer_temp
3 SQL SERVER的子查询
子查询是指将一条SQL Sever语句嵌入到另一条SQL Sever语句中。数据库引擎将子查询做为虚表执行查询操作，提供了一种进一步有效的方式来表示WHERE子句中的条件。子查询就是在SELECT,INSERT,UPDATE,DELETE中都可以使用。
3.1 子查询=
举例：子查询中返回1个值
SELECT * FROM Customer WHERE CustID = (SELECT MAX(CustID) FROM CustomerRoom)
3.2 子查询IN
举例：子查询中返回多个值
SELECT * FROM Customer WHERE CustID IN (SELECT CustID FROM CustomerRoom) 或IN（1,2,3）
3.3 子查询EXISTS
举例：
SELECT * FROM Customer AS a WHERE EXISTS (SELECT 1 FROM CustomerRoom WHERE CustID = a.CustID)
注：EXISTS除了子查询外还可以有其它的用途：
举例：
IF EXISTS(SELECT 1 FROM Customer WHERE CustID = 1)
BEGIN
 PRINT '存在'
END
4 SQL SERVER的视图
4.1 普通视图
普通视图是一个包含了一个或多个表的数据列的虚拟表。通常情况下，它仅仅是存储了查询的对象，一个视图可以当作一个表，可以用于存储过程、JOIN、用户自定义函数等等。
举例：
 CREATE VIEW View_Customer_Filter
 AS
  SELECT a.*,b.RoomSign,b.RoomType
FROM Customer AS a, CustomerRoom b
  WHERE a.CustID = b.CustID

4.2 索引视图
当创建了索引视图之后，视图的结果就会在创建时物化并以物理方式存储在数据库中，减少了运行大表上的复杂查询的开销。缺点是耗费硬盘空间存放，在DML语句如增删改的时候，索引创建会有开销，因为这些一旦执行，索引必须随时更新。
举例：
  CREATE VIEW View_Customer_TableFilter
WITH SCHEMABINDING
AS
SELECT a.CustID,a.CustName,a.City,a.Phone,a.Address,b.RoomSign,b.RoomType
FROM dbo.Customer AS a,dbo.CustomerRoom b
WHERE a.CustID = b.CustID
CREATE UNIQUE CLUSTERED INDEX PK_Index_Customer ON View_Customer_TableFilter(CustID)
5 SQL SERVER的函数，临时表，表变量
5.1 函数
5.1.1 内部函数
Ø 字符串函数
常见的有：substring(截取字符串),left(左截取),right(右截取),upper(转换成大写),lower(转换成小写),space(增加空格), ltrim,rtrim,trim(去掉空格),charindex(查找字符串位置),reverse(反转字符),replace(替换)
Ø 数学函数
  常见的有：abs(绝对值),floor(返回最小整数)，round(四舍五入),rand(随机数)
Ø 聚合函数
  常见的有：avg(取平均数),max(取最大数),min(最小数),sum(求和)，count(数量)
Ø 日期、时间函数
常见的有：getdate(返回日期),datepart(取日期一部份),datediff(日期差),dateadd(日期加数值),convert(转换)
5.1.2 自定义函数
Ø 标量值函数（即 RETURNS 子句指定一种标量数据类型）
举例：
  CREATE FUNCTION fn_Formatstring(@str varchar(50),@len int)
RETURNS varchar(50)
BEGIN
  DECLARE @return varchar(50)
  IF(LEN(@str)<=@len)
SET @return = REPLACE(SPACE(@len-LEN(@str)),space(1),'0')+@str
  ELSE
     SET @return = LEFT(@str,@len)
  RETURN @return
END

Ø 表值函数（即 RETURNS 子句指定 TABLE类型）
举例：
CREATE FUNCTION fn_split(@SourceSql varchar(8000),@StrSeprate varchar(10))
RETURNS @temp TABLE(sub varchar(1000))
AS
BEGIN
DECLARE @i int
SET @SourceSql=rtrim(ltrim(@SourceSql))
SET @i=charindex(@StrSeprate,@SourceSql)
WHILE @i>=1
BEGIN
insert @temp values(left(@SourceSql,@i-1))
SET @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
SET @i=charindex(@StrSeprate,@SourceSql)
END
IF @SourceSql<>'\'
INSERT @temp VALUES(@SourceSql)
RETURN
END

SELECT * FROM dbo.fn_split(‘XXXX-DDDDD-YYYY-ZZZ’,’-’)
5.2 临时表
表名前使用一个#号临时表是局部的。使用两个#号临时表是全局的，在断开连接后sql会自动删除临时表。对于本地临时表 (#开头的)其他会话无法访问数据与表结构，对于全局临时表 (##开头的)其他会话可以访问数据与表结构。可在临时表上建索引，临时表除了名称前多了#号外，其他操作与普通表完全一样。
举例：
CREATE TABLE #a
(
id int,
name varchar(50)
)
INSERT INTO #a(id,name) VALUES(1,'123')

SELECT * FROM #a
DROP TABLE #a
5.3 表变量
表变量可像常规表那样使用用于，小数据量的处理时占优，一般用在存储过程中，调用存储过程完就结束。
举例：
DECLARE @Customer TABLE (IID int,CustName varchar(50),Address varchar(50))

INSERT INTO @Customer(IID,CustName,Address)
VALUES (1,'张三','成都成都')

SELECT * FROM @Customer
6 SQL SERVER的存储过程与事务
使数据处理参数化，对经常使用的一系列SQL进行封装，使其成为一个存储过程的整体，在每次执行时只要更换执行参数即可。当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。存储过程可以重复使用,可减少数据库开发人员的工作量。安全性高可设定只有某此用户才具有对指定存储过程的使用权。
事务是一种机制、是一种操作序列，它包含了一组数据库操作命令，这组命令要么全部执行，要么全部不执行。因此事务是一个不可分割的工作逻辑单元。在数据库系统上执行并发操作时事务是作为最小的控制单元来使用的。
举例：
 --1
  CREATE PROC Proc_Customer_WithIDFilter
(
   @CustID int,
   @CustName nvarchar(50) output,
   @City nvarchar(50) output
)
AS
select @CustName = CustName,@City = City
 from Customer
 where CustID = @CustID

declare @CustName nvarchar(50),@City nvarchar(50)
exec Proc_Customer_WithIDFilter 1,@CustName output,@City output
print @CustName
print @ City
 --2
 alter table Customer2 add num int
 INSERT INTO Customer2(CustName,City,Phone,num)
 VALUES
 (@CustName,@City,2222222,'xxx')
 
  CREATE PROC Proc_Customer_Insert
(
   @CustName nvarchar(50),
   @City nvarchar(50),
   @Phone nvarchar(50),
   @Address nvarchar(100)
)
AS
begin tran --开始执行事务
  INSERT INTO Customer(CustName,City,Phone,Address)
  VALUES
  (@CustName,@City,@Phone,@Address)
  
  INSERT INTO Customer2(CustName,City,Phone)
  VALUES
  (@CustName,@City,@Phone)
if @@error<>0 --判断如果两条语句有任何一条出现错误
begin rollback tran --开始执行事务的回滚，恢复的执行开始之前状态
return 0
end
else   --如何两条都执行成功
begin commit tran --执行这个事务的操作
return 1
end
EXEC Proc_Customer_Insert ‘张三’,’成都’,’23432432’,’天府广场’
7 SQL SERVER的游标
游标属于行级操作消耗很大,SQL查询是基于数据集的所以一般查询能用数据集就用数据集别用游标,数据量大是性能杀手。

举例：
delete Customer
DECLARE @CustName nvarchar(50) ,@City nvarchar(50),@CustID int,@comp1 nvarchar(50),@comp2 nvarchar(50)
insert into customer(custname,city)
select '张三','北京'
union all
select '张三','四川'
union all
select '李四','云南'
union all
select '李四','云南'
union all
select '王五','河南'
union all
select '王五','河北'

DECLARE @CustName nvarchar(50) ,@City nvarchar(50),@CustID int,@comp1 nvarchar(50),@comp2 nvarchar(50)
SET @comp1 = '0'
SET @comp2 = '0'

DECLARE Customer_cursor CURSOR FOR --声明游标
SELECT CustID,CustName,City from Customer
ORDER BY CustName
OPEN Customer_cursor –打开游标
FETCH NEXT FROM Customer_cursor into @CustID,@CustName,@City --提取数据
WHILE @@FETCH_STATUS = 0
BEGIN
 IF(@comp1 = @CustName and @comp2 !=@City)
 BEGIN
  UPDATE Customer set CustNo = 1 WHERE CustID = @CustID
 END
 SET @comp1 = @CustName
 SET @comp2 = @City
 FETCH NEXT FROM Customer_cursor into @CustID,@CustName,@City --提取数据
END
CLOSE Customer_cursor --关闭游标
DEALLOCATE Customer_cursor
8 SQL SERVER的触发器
触发器是一种特殊的存储过程﹐它不能被显式地调用﹐而是在往表中插入记录﹑更新记录或者删除记录时被自动地激活。
Ø 触发器格式：
CREATE TRIGGER triggerName ON 表名
after--after(操作完成后执行触发器)|instead of(操作完成前执行触发器,且不执行新增修改删除操作,叫替换操作表示不执行之后)
UPDATE--UPDATE/INSERT/DELETE
AS
BEGIN
--触发器代码
END
Ø 特殊表:inserted(包含新数据新增修改),deleted(包含旧数据删除)
Ø 存储过程和触发器区别：存储过程可以有参数，手动调用。触发器不能有参，自动调用。
CREATE TABLE [dbo].[Customer_BACK] (
 [CustID] [int] NOT NULL ,
 [CustName] [nvarchar] (50) NOT NULL ,
 [City] [nvarchar] (20) NOT NULL ,
 [Phone] [nvarchar] (50) NULL ,
 [Address] [nvarchar] (200) NULL ,
 [CustNo] [int] NULL
) ON [PRIMARY]
GO
新增触发器：
CREATE TRIGGER tg_Customer_insert ON Customer
after
INSERT
AS
BEGIN
--select * from inserted
insert into Customer_BACK
select * from inserted
END

select * from Customer_BACK
select * from Customer

INSERT INTO Customer(CustName,City,Phone)
Values
('李12','四川','成都')

Ø 删除触发器:
CREATE TRIGGER tg_Customer_delete ON Customer
after
DELETE
AS
BEGIN
--select * from deleted
insert into Customer_BACK
select * from deleted
END
select * from Customer
select * from Customer_back
delete Customer where custid = 66

Ø 修改触发器:
CREATE TRIGGER tg_Customer_update ON Customer
after
UPDATE
AS
BEGIN
--select * from deleted(修改前旧数据)
--select * from inserted(修改后新数据)
insert into Customer_BACK
select * from inserted
END

update Customer set address = 'xxxx'
where custid = 69
9 SQL SERVER的导入导出
9.1 数据库向导进行导入导出
打开数据库，选择表或数据库存点击右键在全部任务里选择导入/导出数据，然后分别选择源数据源和目的数据源及文件，进行导入和导出。
9.2 BULK INTO导入数据
举例：
CREATE TABLE xxdd
(
  IMSI nvarchar(1000),
  ReportTime nvarchar(100),
  CellID nvarchar(100),
  RabInfo nvarchar(100),
  IsPeriodMR nvarchar(100),
  EventID nvarchar(100)
)

truncate table xxdd
bulk insert xxdd from 'D:\Keep.csv'
with 
(FIELDTERMINATOR =',',  --> 指定分隔符  
ROWTERMINATOR ='\n')
9.3 BCP工具导入导出大数据
Ø 通过queryout命令完成导出文件
EXEC master..xp_cmdshell 'bcp "SELECT TOP 20 * FROM LTE_SZ_BETA_After.dbo.Mr" queryout D:\datatest\xxdd4.xls -c -U"sa" -P"zhaowei303mltl"'
Ø 通过out命令完成导出文件
EXEC master..xp_cmdshell 'bcp LTE_SZ_BETA_After.dbo.Mr out D:\datatest\xxdd4.xls -c -U"sa" -P"zhaowei303mltl"'
Ø 通过in命令完成导出文件
EXEC master..xp_cmdshell 'bcp LTE_SZ_BETA_After.dbo.Mr in D:\datatest\xxdd4.xls -c -U"sa" -P"zhaowei303mltl"'

9.4 OPENROWSET导入数据
select * from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE=D:\xxdd.xls',xxdd$)
10 SQL SERVER的常用操作
10.1 SQL SERVER的作业和维护计划
Ø 作业就相当于windows的“任务计划”，作业是由 SQL Server 代理程序按顺序执行的一系列指定的操作。作业可以执行更广泛的活动，包括运行 Transact-SQL脚本、命令行应用程序和 Microsoft® ActiveX® 脚本。可以创建作业来执行经常重复和可调度的任务。
Ø 数据库维护计划向导可以用来帮助您设置核心维护任务，这些任务对于确保数据库的良好执行、定期备份以防系统失败，以及检查一致性方面很有必要。数据库维护计划向导将创建作业。在SQL SERVER 2000以上的版本中维护计划除了进行完全备份以外，还可以进行详细的事务备份，日志备份，索引重组和索引重建的任务。
Ø 执行SQL SERVER的维护计划和作业的前提是运行SQL Server Agent服务。
10.2 SQL SERVER的备份还原，收缩，分离，附加
10.2.1 SQL SERVER的备份
1、打开企业管理器，展开"数据库"文件夹，右击数据库，指向"所有任务"子菜单，然后单击"备份数据库"命令。
2、在"SQL Server数据库"框中，单击"添加"，选择路径输入要备份的文件名，最后确定完成数据库的备份工作。
10.2.2 SQL SERVER的还原
1、打开企业管理器，展开"数据库"文件夹，右击数据库，指向"所有任务"子菜单，然后单击"还原数据库"命令。
2、在"还原为数据库"框中，单击"从设备",点击"选择设备"，选择要还原的备份数据库文件，在选项按钮上边选择在现有数据库上强制还原，最后确定完成数据库的还原工作。
10.2.3 SQL SERVER的收缩
一般情况下，SQL数据库的收缩并不能很大程度上减小数据库大小，其主要作用是收缩日志大小，应当定期进行此操作以免数据库日志过大。
1、打开企业管理器，展开"数据库"文件夹，右击数据库，指向"所有任务"子菜单，然后单击"收缩数据库"命令。
2、在"收缩数据库"框中，单击"文件"，分别选择数据库文件和数据库日志文件，选择收缩文件至调整为合适的大小，直接点确定。
10.2.4 SQL SERVER的分离
1、打开企业管理器，展开"数据库"文件夹，右击数据库，指向"所有任务"子菜单，然后单击"分离数据库"命令。
2、在"分离数据库"框中，如果有连接的话点击使用本数据库连接的清除，再点击确定,这样就能方便移动。
10.2.5 SQL SERVER的附加
1、打开企业管理器，展开"数据库"文件夹，右击数据库，指向"所有任务"子菜单，然后单击"附加数据库"命令。
2、在"附加数据库"框中，选择要附加的数据库，再点击确定，完成数据库附加。
11 SQL总结
11.1 通过SELECT INTO 添加自动增长列
select IDENTITY(int,1,1) as id,aa,bb,cc,dd
into ccc
from aaa1
本行数据比较上行数据
方法1:
create table aaa1
( aa int,
  bb int,
  cc int,
  dd int)
insert into aaa1 values(1,1,1,10)
insert into aaa1 values(1,2,2,40)
insert into aaa1 values(1,3,13,50)
insert into aaa1 values(1,5,15,30)
insert into aaa1 values(2,3,2,20)
insert into aaa1 values(2,5,5,60)
-- 更新
select aa,bb,cc,dd,
         (select count(1) from aaa1 b
          where b.aa=a.aa and b.bb<=a.bb) 'rn'
  into #t
  from aaa1 a

update x
 set x.dd=case when y.rn is null then x.dd
               when (x.cc-y.cc)<5 then y.dd
               else x.dd end
 from #t x
 left join #t y on x.aa=y.aa and x.rn=y.rn+1

update a
 set a.dd=b.dd
 from aaa1 a
 inner join #t b on a.aa=b.aa and a.bb=b.bb and a.cc=b.cc
select * from aaa1
/*
aa          bb          cc          dd
----------- ----------- ----------- -----------
1           1           1           10
1           2           2           10
1           3           13          50
1           5           15          50
2           3           2           20
2           5           5           20

(6 row(s) affected)
*/
drop table #t
方法2:
--drop table aaa1

create table aaa1
(
aa int,
bb int,
cc int,
dd int
)
go
insert into aaa1 values(1,1,1,10)
insert into aaa1 values(1,2,2,40)
insert into aaa1 values(1,3,13,50)
insert into aaa1 values(1,5,15,30)
insert into aaa1 values(2,3,2,20)
insert into aaa1 values(2,5,5,60)

--更新数据
update aaa1
set dd =
(
select --aa,bb,cc,
       case when (select top 1 a2.cc
                  from aaa1 a2
      where a1.aa = a2.aa
         and a1.bb > a2.bb
      order by bb desc) is null
     then dd
         when cc - (select top 1 a2.cc
                       from aaa1 a2
           where a1.aa = a2.aa
              and a1.bb > a2.bb
           order by bb desc) < 5
     then (select top 1 a2.dd
                       from aaa1 a2
           where a1.aa = a2.aa
              and a1.bb > a2.bb
           order by bb desc)
       else dd
  end as dd
from aaa1 a1
where a1.aa = aaa1.aa and
      a1.bb = aaa1.bb and
      a1.cc = aaa1.cc
)

--查询数据，发现字段dd已经更新
select *
from aaa1
/*
aa bb cc dd
1 1 1 10
1 2 2 10
1 3 13 50
1 5 15 50
2 3 2 20
2 5 5 20
*/