我们工作上会使用到有了这个模版可以让工作更加便捷
–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
© 版权声明
文章版权归作者所有，未经允许请勿转载。
相关文章
暂无评论...
