01_sqlserver脚本模板

我们工作上会使用到有了这个模版可以让工作更加便捷

--1.查询 SELECT

--查询所有

SELECT * FROM dbo.TSalary

--指定字段查询

SELECT EmployeeID,CurrentYear,CurrentMonth FROM dbo.TSalary

--指定字段放在前面，查询所有

SELECT EmployeeID,CurrentYear,CurrentMonth, * FROM dbo.TSalary

--条件查询 =

SELECT * FROM dbo.TSalary WHERE CurrentYear = '2014'

--条件查询 in

SELECT * FROM dbo.TSalary WHERE CurrentYear IN( '2014','2013')

--模糊查询 like

SELECT * FROM dbo.TSalary WHERE EmployeeSalary LIKE '6%'

--合并查询 union-去重,2个表的字段必须一致

SELECT * FROM dbo.TSalary WHERE EmployeeSalary LIKE '%2%'

UNION

SELECT * FROM dbo.TSalary WHERE EmployeeSalary LIKE '6%'

ORDER BY EmployeeSalary

--合并查询 union all-不去重,2个表的字段必须一致

SELECT * FROM dbo.TSalary WHERE EmployeeSalary LIKE '%2%'

UNION ALL

SELECT * FROM dbo.TSalary WHERE EmployeeSalary LIKE '6%'

ORDER BY EmployeeSalary

--字段指定别名

SELECT EmployeeID AS ID,CurrentYear AS 年,CurrentMonth 月, * FROM dbo.TSalary

--查询排序（默认ASC，升序）

SELECT * FROM dbo.TSalary ORDER BY EmployeeID

SELECT * FROM dbo.TSalary ORDER BY EmployeeID DESC

--2.更新 UPDATE

UPDATE TABLE SET 新值 WHERE 条件

--3.删除 DELETE

DELETE FROM 表  WHERE 条件

--4.top关键字

SELECT TOP 2  * FROM dbo.TSalary

--5.Group by和Having

-- 先查看每个员工的工资-分组列一定要在显示列中体现

SELECT EmployeeID, SUM(EmployeeSalary) FROM dbo.TSalary GROUP BY EmployeeID

-- 按员工汇总工资，即返回每人的工资总和

-- SUM()是求和函数，将该字段的值求和并返回结果

SELECT CurrentMonth, SUM(EmployeeSalary) FROM dbo.TSalary GROUP BY CurrentMonth

-- 同样想知道每个月一共为员工发了多少工资

SELECT CurrentYear,CurrentMonth, SUM(EmployeeSalary) 月度工资总额 FROM dbo.TSalary GROUP BY CurrentYear,CurrentMonth

-- 查询每个员工的平均工资

-- AVG()是求均值函数，将该字段的值返回平均值

SELECT EmployeeID, AVG(EmployeeSalary) 平均工资 FROM dbo.TSalary GROUP BY EmployeeID

-- 查询每个员工的工资总和并且只显示那些工资总和大于20000的记录

-- 分组条件用 Having 子句，类似与非分组情况下的where

SELECT EmployeeID, SUM(EmployeeSalary) 平均工资 FROM dbo.TSalary GROUP BY EmployeeID HAVING SUM(EmployeeSalary)>20000

--6.Distinct关键字去重

SELECT  DISTINCT CurrentYear,CurrentMonth FROM dbo.TSalary ORDER BY CurrentYear,CurrentMonth

--等价于

SELECT  CurrentYear,CurrentMonth FROM dbo.TSalary GROUP BY CurrentYear,CurrentMonth

--7.范围查询between...and...

SELECT  * FROM dbo.TSalary  WHERE EmployeeSalary BETWEEN 7000 AND 10000

--8.日期查询

--返回当前系统日期和时间。

SELECT GETDATE()

--返回两个指定日期间的时间间隔数目。(例如天数)

SELECT DATEDIFF(month,'2017-01-01',GETDATE())

--向指定日期加上一段时间，返回新的datetime值。

SELECT DATEADD(MONTH,3,GETDATE())

--返回年  月  日

SELECT YEAR(GETDATE()),MONTH(GETDATE()),DAY(GETDATE())

--9.随机数以及四舍五入

--返回一个0-1的随机数，且进行四舍五入保留2位小数

SELECT ROUND(RAND(),2)

--10.类型转换

SELECT CONVERT(VARCHAR(100),GETDATE(),110)

SELECT CAST(GETDATE() AS VARCHAR(100))

SELECT EmployeeName+'【'+CAST(EmployeeID AS VARCHAR(100))+'】' AS '姓名+学号' FROM dbo.TEmployee

--11.case when语句

SELECT  EmployeeID ,

        CurrentYear ,

        CurrentMonth ,

        EmployeeSalary ,

        ProvideTime ,

        CASE WHEN ProvideTime < '2012-12-01' THEN '老员工'

    WHEN ProvideTime > '2014-01-01' THEN '新员工'

            ELSE '普通员工'

        END AS 员工类型

FROM    dbo.TSalary

--12.将查询结果保存到一张新表 SELECT * into 新表 FROM  表

SELECT  EmployeeID ,

        CurrentYear ,

        CurrentMonth ,

        EmployeeSalary ,

        ProvideTime ,

        CASE WHEN ProvideTime < '2012-12-01' THEN '老员工'

    WHEN ProvideTime > '2014-01-01' THEN '新员工'

            ELSE '普通员工'

        END AS 员工类型

INTO TSalary_bak

FROM    dbo.TSalary

SELECT *FROM  TSalary_bak

--13.多表连接查询

--1301.内连接（where x=y  与表 A inner join 表B on A.X=B.X  效果一样 ）

SELECT *FROM  TSalary

SELECT *FROM  TEmployee

SELECT * FROM  TSalary ts ,TEmployee te WHERE ts.EmployeeID=te.EmployeeID

SELECT * FROM  TSalary ts ,TEmployee te WHERE te.EmployeeID=ts.EmployeeID

--等同于下面

SELECT * FROM  TSalary ts INNER JOIN TEmployee te  ON ts.EmployeeID=te.EmployeeID

--1302.左连接(左边数据全部显示，右侧没有与左侧匹配的则全部显示null)

SELECT * FROM  TSalary ts LEFT JOIN TEmployee te  ON ts.EmployeeID=te.EmployeeID

--1303.右连接(右边数据全部显示，左侧没有与左侧匹配的则全部显示null)

SELECT * FROM  TSalary ts RIGHT JOIN TEmployee te  ON ts.EmployeeID=te.EmployeeID

--1304.全连接(返回左右2侧全部数据，左侧有右侧没的右侧显示null，右侧有左侧没有的左侧显示null),ISNULL判断为空，CAST类型转换

SELECT  * FROM TSalary ts FULL OUTER JOIN TEmployee te ON ts.EmployeeID = te.EmployeeID

SELECT  ISNULL(CAST(ts.EmployeeID AS VARCHAR(100)), '不存在') 工号 ,

        ISNULL(ts.EmployeeSalary, 0) 薪水 ,

        ISNULL(te.EmployeeName, '不存在') 员工

FROM    TSalary ts

        FULL OUTER JOIN TEmployee te ON ts.EmployeeID = te.EmployeeID

SELECT  * FROM TSalary ts FULL OUTER JOIN TEmployee te ON ts.EmployeeID = te.EmployeeID WHERE ts.EmployeeID IS NULL

SELECT  * FROM TSalary ts FULL OUTER JOIN TEmployee te ON ts.EmployeeID = te.EmployeeID WHERE ts.EmployeeID IS NOT NULL

--*1305.自连接(组织架构表-自己连接自己)

SELECT ta.StruName,tb.StruName FROM  TCorpStructure ta INNER JOIN dbo.TCorpStructure tb ON ta.StruID=tb.ParentStruID WHERE ta.StruID='TotalCompany'

--14.子查询

---查询员工的id、工资和姓名

SELECT  EmployeeID,EmployeeSalary,ISNULL((SELECT EmployeeName FROM TEmployee te WHERE te.EmployeeID=ts.EmployeeID),'空') AS 员工姓名 FROM TSalary ts

---查询工资最高的员工姓名

----标量子查询(=):此项用等于只能在子查询有一个返回结果

SELECT *FROM dbo.TEmployee WHERE EmployeeID=(SELECT TOP 1 EmployeeID FROM TSalary ORDER BY  EmployeeSalary DESC)

----多值子查询(IN)，独立子查询:可以单独执行此项，用IN可用在在子查询有N个返回结果

SELECT *FROM dbo.TEmployee WHERE EmployeeID IN (SELECT TOP 1 EmployeeID FROM TSalary ORDER BY  EmployeeSalary DESC)

---查询存在工资的员工

----*EXISTS相关子查询，依赖外部查询，不能单独查询

SELECT * FROM dbo.TEmployee WHERE EXISTS(SELECT * FROM dbo.TSalary WHERE dbo.TEmployee.EmployeeID=dbo.TSalary.EmployeeID)

SELECT * FROM dbo.TEmployee WHERE NOT EXISTS(SELECT * FROM dbo.TSalary WHERE dbo.TEmployee.EmployeeID=dbo.TSalary.EmployeeID)

--14.流程控制语句

---while(begin end  语句块)

DECLARE @i INT

SET @i=1

WHILE @i<10

BEGIN

PRINT @i

SET @i=@i+1

END

---while(计算偶数的和)

DECLARE @i INT,@sum INT

SET @i=1

SET @sum=0

WHILE @i<=10

BEGIN

-----输出1-10的数

BEGIN

PRINT @i

SET @i=@i+1

END

-----计算1-10的偶数和

BEGIN

IF @i%2=0

SET @sum=@sum+@i

END

END

PRINT @sum

---Case when then

DECLARE @x INT

SET @x=22

DECLARE @s VARCHAR(1000)

SET @s=

CASE @x

WHEN 1 THEN 'NO.01'

WHEN 2 THEN 'NO.02'

WHEN 3 THEN 'NO.03'

ELSE 'NO.0X'

END

SELECT @s

--15.视图

---优点：限定查询，用户不同只能看到部分数据；容易维护；

---缺点：大型表、复杂关系表查询慢；视图中不要嵌套视图、函数

IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id=OBJECT_ID('VEmployeeStyle'))

DROP VIEW VEmployeeStyle

GO

CREATE VIEW VEmployeeStyle

AS

SELECT  EmployeeID ,

        CurrentYear ,

        CurrentMonth ,

        EmployeeSalary ,

        ProvideTime ,

        CASE WHEN ProvideTime < '2012-12-01' THEN '老员工'

    WHEN ProvideTime > '2014-01-01' THEN '新员工'

            ELSE '普通员工'

        END AS 员工类型

FROM    dbo.TSalary

GO

SELECT *FROM VEmployeeStyle

--16.函数

---表值函数：返回TABLE数据类型

--对于表值函数：

--1.所有的传入参数前都必须加@

--2.create后的返回，关键字是returns，而不是return。

--3.returns后面的跟的不是变量，而是返回表table。

--4.as后面直接用return 返回结果表。

IF EXISTS(select * from dbo.sysobjects where id = object_id('F3_GetSalary'))

DROP FUNCTION F3_GetSalary

GO

CREATE FUNCTION F3_GetSalary(@EmployeeID INT )

RETURNS TABLE

AS

RETURN

    ( SELECT    *

      FROM      TSalary

      WHERE    EmployeeID = @EmployeeID

    )

GO

SELECT *FROM F3_GetSalary(2)  --返回的表需要使用select * from 函数

GO

---*标量函数：使用RETURN语句返回单个数据值

--对于标量函数：

--1.所有的传入参数前都必须加@

--2.create后的返回，关键字是returns，而不是return。

--3.returns后面的跟的不是变量，而是返回值的类型，如：int，char等。

--4.在begin/end语句块中，是return。

IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id=OBJECT_ID('F3_GetMax'))

DROP FUNCTION F3_GetMax

GO

CREATE FUNCTION F3_GetMax(@a INT,@b INT)

RETURNS INT

AS

BEGIN

DECLARE @max INT;

IF @a>@b

BEGIN

  SET @max=@a;

END

ELSE

BEGIN

  SET @max=@b;

END

RETURN @max

END

GO

DECLARE @m INT

SET @m = EmployeeDB.dbo.F3_GetMax(1,2) --返回的值需要使用select 函数

SELECT @m

--或者

SELECT EmployeeDB.dbo.F3_GetMax(1,2) --返回的值需要使用select 函数

--17.存储过程

--存储过程和用户自定义函数的区别如下：

--1.存储过程支持输出参数，向调用者返回值。用户定义函数只能通过返回值返回数据。

--2.存储过程可以作为一个独立的主体被执行，而用户定义函数可以出现在SELECT语句中。

--3.存储过程功能比较复杂，而用户定义函数通常都具有比较明确的、有针对性的功能。

-- 显示有关数据库对象相关性的信息

EXEC sp_depends  'dbo.TEmployee'

-- 显示帮助

EXEC sp_help 'dbo.F3_GetMax'

-- 显示帮助

EXEC sp_helpdb 'dbo.EmployeeDB'

-- 显示具体脚本

EXEC sp_helptext 'dbo.F3_GetMax'

-- 显示表使用空间

EXEC sp_spaceused 'dbo.TEmployee'

--创建不带参数的存储过程

IF OBJECT_ID('SP3_getGetEmployees') IS NOT NULL

    DROP PROCEDURE SP3_getGetEmployees;

GO

CREATE PROCEDURE SP3_getGetEmployees

AS

BEGIN

  SELECT  EmployeeID ,

        CurrentYear ,

        CurrentMonth ,

        EmployeeSalary ,

        ProvideTime ,

        CASE WHEN ProvideTime < '2012-12-01' THEN '老员工'

    WHEN ProvideTime > '2014-01-01' THEN '新员工'

            ELSE '普通员工'

        END AS 员工类型

  FROM    dbo.TSalary

END

EXEC dbo.SP3_getGetEmployees

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

IF OBJECT_ID('SP3_getGetEmployeesId') IS NOT NULL

DROP PROCEDURE SP3_getGetEmployeesid

GO

CREATE PROCEDURE SP3_getGetEmployeesid

(

@EmployeeID INT

)

AS

BEGIN

-- 不向客户端发送消息，禁用它们能够减少网络流量。

  SET NOCOUNT ON

  SELECT  EmployeeID ,

        CurrentYear ,

        CurrentMonth ,

        EmployeeSalary ,

        ProvideTime ,

        CASE WHEN ProvideTime < '2012-12-01' THEN '老员工'

    WHEN ProvideTime > '2014-01-01' THEN '新员工'

            ELSE '普通员工'

        END AS 员工类型

  FROM    dbo.TSalary WHERE EmployeeID=@EmployeeID

END

EXEC SP3_getGetEmployeesId @EmployeeID=1

EXEC SP3_getGetEmployeesId 1