SQL基础学习笔记(一)

今天晚上才开的博客,先试试看这东西好用不.下面的内容都很基础,两年前的笔记,以前都一直把这些东西存在U盘上,让大家见笑了.

1. 员工表中有员工ID,部门,工资,求各部门的最高工资的员工

DECLARE @t TABLE(员工ID INT, 部门ID INT, 薪水 decimal(10,2))  --定义一个变量表,相当于临时表

INSERT INTO @t
SELECT 1,1,50 UNION ALL
SELECT 2,1,60 UNION ALL
SELECT 3,1,70 UNION ALL
SELECT 4,2,30 UNION ALL
SELECT 5,2,10

SELECT * FROM @t A WHERE NOT EXISTS (SELECT * FROM @t B WHERE A.部门ID=B.部门ID
 AND A.薪水<B.薪水)

2.分隔符

SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE "$Employee Data"
(
 "^First Name"   varchar(25) NOT NULL,
 "^Last Name"   varchar(25) NOT NULL,
 "^Dept ID"   int
)
-- INSERT statements go here.当quoted_identifier设为 on时可以用""作分隔符,''分隔字符串
--为off时只能用[]作分隔符,""和''分隔字符串
SET QUOTED_IDENTIFIER OFF
GO
CREATE TABLE [^$Employee Data]
(
 [^First Name]   varchar(25) NOT NULL,
 [^Last Name]   varchar(25) NOT NULL,
 [^Dept ID]   int
)
-- INSERT statements go here.

3. 约束键

Declare @A table(学生ID int,课程 varchar(50),成绩 int check(成绩<100))
insert into @A
select 1,'语文',99 union all
select 2,'数学',98
select * from @A

4. col_Length(表名,列名)返回列长度和DataLength(列名)返回数据长度

SELECT COL_LENGTH('Employees', 'LastName') AS Col_Length,
   DATALENGTH(LastName) AS DataLength
FROM Employees
WHERE EmployeeID > 6

5. 字符串函数

SELECT x = SUBSTRING('abcdef', 2, 3)

x
----------
bcd

6. 字符串位置charindex('字符串',列名)不能用通配符,patindex()可以用通配符
Declare @A table(学生ID int,课程 varchar(50),成绩 int check(成绩<100))
insert into @A
select 1,'大学语文',99 union all
select 2,'数学',98
select 课程,charindex('语',课程) as 位置 from @A where charindex('语',课程)!=0
select 课程,patindex('%语%',课程) as 位置 from @A where patindex('%语%',课程)!=0

7. 字符串替换插入

如果开始位置或长度的值是负数，或者如果开始位置大于第一个字符串的长度，将返回空字符串。如果要删除的长度大于第一个字符串的长度，将删除到第一个字符串中的第一个字符。STUFF ( character_expression, start, length, character_expression )

SELECT STUFF('abc', 2, 10, 'xyz')
下面是结果集：

----
axyz

8. 生成随机数

SELECT RAND(159784)   --调用相同的种子,他将生成相同的随机数
SELECT RAND(159784)
SELECT RAND(159784)
--常用随机数生成方法,可见他生成的随机数还是有较大的可能性会重复
SELECT RAND( (DATEPART(mm, GETDATE()) * 100000 )
           + (DATEPART(ss, GETDATE()) * 1000 )
           + DATEPART(ms, GETDATE()) )

9. 获取日期名称

SELECT DATEPART(month,'1998-8-5')  --返回8
SELECT DATENAME(month,'1998-8-5')  --返回08
select datepart(year,getdate())
select datepart(Day,getdate())
select datepart(hour,getdate())

10. 日期的加减

SELECT DATEDIFF(day,'1985-4-30', '2008-6-3')  --返回8435天
SELECT DATEAdd(day,3, '2008-6-3')       --返回2008-6-6

11. 转换函数convert和cast

SELECT CAST(title AS char(50)), ytd_sales
FROM titles
WHERE type = 'trad_cook'

SELECT CONVERT(char(50), title), ytd_sales
FROM titles
WHERE type = 'trad_cook'

SELECT CONVERT(char(12), GETDATE(), 3)   --返回03/06/08 只有convert()才可以用样式
SELECT CONVERT(char(12), GETDATE(), 103)   --返回03/06/2008 102返回2008.06.03

12.字符串运算表达式

select cast(学生ID as nvarchar(50))+'的'+课程+'成绩'+'是'+cast(成绩 as nvarchar(50))+'分' as 返回内容 from @a  --这里的非字符型都要用cast()转换,,返回:1的语文成绩是99分

13.变量和循环的使用

declare @A table(员工ID int,姓名 char(16),部门 varchar(20),薪水 int)
insert into @A
select 1,'小张','网络部',2500 union all
select 2,'小王','技术部',2000

declare @m int
set @m=0
while(@m<10)
begin
insert into @A
select @m,'','',0
set @m=@m+1
end

select * from @A

14. 存储过程

CREATE PROCEDURE SampleProcedure
  @EmployeeIDParm INT,
         @MaxQuantity INT OUTPUT
AS
DECLARE @ErrorSave INT
SET @ErrorSave = 0

SELECT FirstName, LastName, Title
FROM Employees
WHERE EmployeeID = @EmployeeIDParm

-- Save any nonzero @@ERROR value.
IF (@@ERROR <> 0)
   SET @ErrorSave = @@ERROR

-- Set a value in the output parameter.
SELECT @MaxQuantity = MAX(Quantity)
FROM [Order Details]

IF (@@ERROR <> 0)
   SET @ErrorSave = @@ERROR

-- Returns 0 if neither SELECT statement had
-- an error, otherwise returns the last error.
RETURN @ErrorSave
GO

执行存储过程的 Transact-SQL 批处理或存储过程可以将返回代码检索至整型变量：

DECLARE @ReturnStatus INT
DECLARE @MaxQtyVariable INT
EXECUTE @ReturnStatus = SampleProcedure @EmployeeIDParm = 9,
         @MaxQtyVariable = @MaxQuantity OUTPUT

-- Show the values returned.
PRINT ' '
PRINT 'Return code = ' + CAST(@ReturnStatus AS CHAR(10))
PRINT 'Maximum Quantity = ' + CAST(@MaxQtyVariable AS CHAR(10))
GO

调用存储过程的应用程序可以将返回代码所对应的参数标记与整型变量绑定。

15. 使用 WAITFOR

下面示例使用 DELAY 关键字指定在执行 SELECT 语句之前等待两秒：

WAITFOR DELAY '00:00:02'
SELECT EmployeeID FROM Northwind.dbo.Employees

下面示例使用 TIME 关键字指定在 10 P.M 以后对指定数据库 pubs 进行检查，以确保所有页的分配和使用正确：

   WAITFOR TIME '22:00'
   DBCC CHECKALLOC

16. 使用 CASE

 --替换值的类型要与原类型一样
select case 姓名 when '' then '无此人' else 姓名 end as 姓名 from @A
select case 薪水 when 0 then 100 else 薪水 end as 薪水 from @A

17. 使用 sp_executesql和 Exec

--使用Exec必须将所有参数值转换为字符或 Unicode 并使其成为 Transact-SQL 字符串的一部分,Unicode 字符串时，请在搜索字符串之前加字符 N
DECLARE @IntVariable INT
DECLARE @SQLString NVARCHAR(500)
/* Build and execute a string with one parameter value. */
SET @IntVariable = 35
SET @SQLString = N'SELECT * FROM pubs.dbo.employee WHERE job_lvl = ' +
                 CAST(@IntVariable AS NVARCHAR(10)) 
EXEC(@SQLString)

--完成上面同样的功能,但SQLString只生成一次,可以动态的改变参数
DECLARE @IntVariable INT
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)

SET @SQLString =N'SELECT * FROM pubs.dbo.employee WHERE job_lvl = @level'
--只所以要这么写是因为过程需要参数为 'ntext/nchar/nvarchar' 类型。这里是数据类型转换!
SET @ParmDefinition = N'@level tinyint'  
SET @IntVariable = 35
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @level = @IntVariable

SET @IntVariable = 32
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @level = @IntVariable

18. set RowCount设定返回结果集的记录条数,与top功能相似,但top只能用于单句,set rowcount可以用于多句

set rowcount 10  --返回10条
select distinct 员工ID,姓名,部门,薪水 from @A order by 员工ID desc
set rowcount 0   --关闭设置

19. 联接语句的使用

DECLARE @A TABLE(员工ID INT,姓名 char(16), 部门 varchar(50), 薪水 decimal(10,2))  --定义一个变量表,相当于临时表

INSERT INTO @A
SELECT 1,'小张','网络部',2500 UNION ALL
SELECT 2,'小李','市场部',3000 UNION ALL
SELECT 3,'小王','网络部',3000 UNION ALL
SELECT 4,'小周','市场部',2500 UNION ALL
SELECT 5,'新文秘','临时工',1000

declare @B table(员工ID int,地址 nvarchar(50),电话 char(13))

insert into @B
select 1,'小张的地址','小张的电话' union all
select 2,'小李的地址','小李的电话' union all
select 3,'小王的地址','小王的电话' union all
select 4,'小周的地址','小周的电话' union all
select 6,'前文秘的地址','前文秘的电话'

select A.员工ID,A.姓名,B.电话 from @A A,@B B where A.员工ID=B.员工ID
select A.员工ID,A.姓名,B.电话 from @A A left join @B B on A.员工ID=B.员工ID --左联接右表可能为空,B.电话可能为空
select A.员工ID,A.姓名,B.电话 from @A A right join @B B on A.员工ID=B.员工ID --右联接左表可能为空,A.员工ID、A.姓名可能为空
select A.员工ID,A.姓名,B.电话 from @A A full join @B B on A.员工ID=B.员工ID --完整外部联接左右表都可能为空
select A.员工ID,A.姓名,B.电话 from @A A inner join @B B on A.员工ID=B.员工ID --和第一条一样的效果,不会产生空字段

--交叉联接,无where时记录数是两表记录的乘积,加where时与inner join一样

select A.员工ID,A.姓名,B.电话 from @A A cross join @B B where A.员工ID=B.员工ID 

20. where和from 中指联接

--在where中*=相当于right jion,=*相当于left jion
--下面语句在联接的同时设定条件,返回的记录数以联接产生的为准,B.地址<>'小周的地址'的记录会以空值填充
select * from @A As A,@B AS B where A.员工ID*=B.员工ID and B.地址='小周的地址'
select * from @A A left join @B B on A.员工ID=B.员工ID and B.地址='小周的地址'

--下面语句在联接后,选定条件所以B.地址<>'小周的地址'的记录不会返回

select * from @A A left join @B B on A.员工ID=B.员工ID where B.地址='小周的地址'
select * from @A A,@B B where A.员工ID=B.员工ID and B.地址='小周的地址'   --没有用联接实现与上一句一样的效果

21. 使用 HAVING 子句选择行

--having与where 的功以有相似,只是where在group by之前作用,不能用聚合函数;而having 在group by 之后作用可以用聚合函数
--建议能放在where中的条件尽量放在where中以减少不必要的记录分组
USE pubs
SELECT pub_id, total = SUM(ytd_sales)
FROM titles
GROUP BY pub_id
HAVING SUM(ytd_sales) > 40000  --返回所有销售额超过40000的产品ID和销售总量

22. GROUP BY 子句和空值

SELECT royalty, AVG(price * 2) AS AveragePrice
FROM pubs.dbo.titles
GROUP BY royalty

--因为royalty有一行或几行为null所以返回值会有一行,要消除这一行可以用以下方法

SELECT royalty, AVG(price * 2) AS AveragePrice
FROM pubs.dbo.titles
WHERE royalty IS NOT NULL
GROUP BY royalty

23. 使用 UNION 运算符组合多个结果

 SELECT * FROM TableB
 UNION
 SELECT * FROM TableC  --这会消除重复的记录

 SELECT * FROM TableB
 UNION ALL
 SELECT * FROM TableC  --这会返回所有记录,包括重复记录

如果使用 UNION 运算符，那么单独的 SELECT 语句不能包含其自己的 ORDER BY 或 COMPUTE 子句。只能在最后一个 SELECT 语句的后面使用一个 ORDER BY 或 COMPUTE 子句；该子句适用于最终的组合结果集。GROUP BY 和 HAVING 子句只能在单独的 SELECT 语句中指定。

24. 用 ANY、SOME 或 ALL 修改的比较运算符

以 > 比较运算符为例，>ALL 表示大于每一个值；换句话说，大于最大值。例如，>ALL (1, 2, 3) 表示大于 3。>ANY 表示至少大于一个值，也就是大于最小值。因此 >ANY (1, 2, 3) 表示大于 1。尽管一些使用 EXISTS 表示的查询不能以任何其它方法表示，但所有使用 IN 或由 ANY 或 ALL 修改的比较运算符的查询都可以通过 EXISTS 表示;如=any相当于exists...

select * from @A where 薪水>all(2500,3000)

25. 个人心得

凡是SQL语句里有两个表的相关联的,都要注意其关联了条件,如果这两个引有表是同一张表,则必指定其别名,并指定其关联的条件,如:

select 姓名 from @A A where exists(select * from @A B where B.薪水>2500 and A.薪水=B.薪水) --只返回工资>2500的

select 姓名 from @A where exists(select * from @A where 薪水>2500)  --会返回所有行

select max(姓名),max(薪水) from @A group by 部门   --这样写是错的,因为同时用两个聚合函数是行值已被打乱,姓名最大值:MAX(工资)

25. 用于替代表达式的子查询

在 SELECT、UPDATE、INSERT 和 DELETE 语句中任何可以使用表达式的地方都可以使用子查询来替代。

USE pubs
SELECT title, price,
(SELECT AVG(price) FROM titles) AS average,
price-(SELECT AVG(price) FROM titles) AS difference
FROM titles
WHERE type='popular_comp'

26.相关子查询

子查询是重复执行的，为外部查询可能选择的每一行均执行一次。

USE pubs
SELECT au_lname, au_fname     
FROM authors       
WHERE 100 IN       
   (SELECT royaltyper
   FROM titleauthor
   WHERE titleauthor.au_ID = authors.au_id)

下面准确说明如何评估该查询：SQL Server 考虑 authors 表中的每一行是否都包括在结果中，方法是将每一行的值都代入内部查询中。例如，如果 SQL Server 首先检查 Cheryl Carson 行，那么变量 authors.au_id 将取值 238-95-7766，SQL Server 将该值代入到内部查询中。

USE pubs
SELECT royaltyper
FROM titleauthor
WHERE au_id = '238-95-7766'

结果为 100，所以外部查询评估为：

USE pubs
SELECT au_lname, au_fname
FROM authors
WHERE 100 IN (100)

由于这是真的，因此 Cheryl Carson 行包括在结果中。对 Abraham Bennet 行运行相同的过程，会发现该行没有包括在结果中。

27. 使用 CASE 处理条件数据

USE pubs
SELECT
   CASE type
      WHEN 'popular_comp' THEN 'Popular Computing'
      WHEN 'mod_cook' THEN 'Modern Cooking'
      WHEN 'business' THEN 'Business'
      WHEN 'psychology' THEN 'Psychology'
      WHEN 'trad_cook' THEN 'Traditional Cooking'
      ELSE 'Not yet categorized'
   END AS Category,
CONVERT(varchar(30), title) AS "Shortened Title",
price AS Price
FROM titles
WHERE price IS NOT NULL
ORDER BY 1

28. 用 CUBE和ROLLUP 汇总数据

例如，一个简单的表 Inventory 中包含：

Item                 Color                Quantity                  
-------------------- -------------------- --------------------------
Table                Blue                 124                       
Table                Red                  223                       
Chair                Blue                 101                       
Chair                Red                  210     

SELECT Item, Color, SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH CUBE --只能在group by 里指定

下面是结果集：

Item                 Color                QtySum                    
-------------------- -------------------- --------------------------
Chair                Blue                 101.00                    
Chair                Red                  210.00                    
Chair                (null)               311.00                    
Table                Blue                 124.00                    
Table                Red                  223.00                    
Table                (null)               347.00                    
(null)               (null)               658.00                    
(null)               Blue                 225.00                    
(null)               Red                  433.00                    

包含带有许多维度的 CUBE 的 SELECT 语句可能生成很大的结果集，因为这些语句会为所有维度中值的所有组合生成行。这些大结果集包含的数据可能过多而不易于阅读和理解。这个问题有一种解决办法是将 SELECT 语句放在视图中：

CREATE VIEW InvCube AS
SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
            ELSE ISNULL(Item, 'UNKNOWN')
       END AS Item,
       CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
            ELSE ISNULL(Color, 'UNKNOWN')
       END AS Color,
       SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH CUBE

然后即可用该视图来只查询您感兴趣的维度值：

SELECT *
FROM InvCube
WHERE Item = 'Chair'
  AND Color = 'ALL'

29、自定义函数的使用

//实现同一列的值按ID号相同合并
create function getstr(@content varchar(10))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+rtrim(Rolename) from A where UserID=1
select @str=right(@str,len(@str)-1)
return @str
end
go

select UserID,dbo.getstr(UserID) RoleName from A group by UserID

Create table A(UserID INT,RoleName char(16))
INSERT INTO A
SELECT 1,'普通用户' UNION ALL
select 1,'管理员用户' union all
select 1,'VIP用户' union all
select 2,'管理员用户' union all
select 2,'VIP用户'

30、在查询时没有统计工作的最好就用子查询而不用聚合函数，子查询比聚合效率高且聚合用hash可能会装不下大量数据。

31、取得某个表的行数(单表时有效)

N是nuicode,indid:0 = 堆,1 = 聚集索引,> 1 = 非聚集索引;所以当小于二时才是没有重复的正确统计值

SELECT * FROM SYSINDEXES where OBJECT_ID(N'表名')=id and indid < 2

32、行转列

declare @A table(name varchar(16),subject varchar(50),result decimal(4,1))
insert into @A
select '张三','语文',80 union all
select '张三','数学',90 union all
select '张三','物理',85 union all
select '李四','语文',85 union all
select '李四','数学',92 union all
select '李四','物理',82

//这是后面生成的语句的效果，执行变量语句时，里面不能是变量表，所以只能拷出来测试
select name,sum(case subject when '数学' then Result end) [数学],
sum(case subject when '物理' then Result end) [物理],
sum(case subject when '语文' then Result end) [语文]
from @A group by name

declare @sql varchar(4000)
set @sql = 'select name'
select @sql = @sql + ',sum(case subject when '''+subject+''' then Result end) ['+subject+']'
from (select distinct subject from @A) as a
select @sql = @sql+' group by name'
exec(@sql)

33. 如何取得一个数据表的所有列名
select name from syscolumns where id=object_id('表名')

34.视图就是存于数据库中的一段语句,当有A,B,C三个视图时C依赖B,B依赖A,当B修改后,C与B的关系自动丢失,B与A的关系继存在
create view vCustomersB
as
select * from vCustomersA

alter view vCustomersB
as
select * from vCustomersA

修改过后还必须刷新视图
exec sp_refreshview 'vCustomersB'

36.使用RAW模式查询
SELECT ProductModelID, Name FROM Production.ProductModel WHERE ProductModelID=122 FOR XML RAW ('ProductModel'), ELEMENTS XSINIL,root('myTable')

返回

<myTable xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<ProductModel>
  <ProductModelID>122</ProductModelID>
  <Name>All-Purpose Bike Stand</Name>
</ProductModel>
</myTable>

38,将exec的执行结果放到变量表中
insert into @varTable exec (@execStr)

 39,update 完全写法

update tableA set a1='周周测试' from tableA a inner join table B on a.id=b.id