SQL Study Notes
第一次发表，希望大家给出意见
里面的例子 建表数据都在里面 数据库mdf文件下载地址：http://u.115.com/file/bh59f4dt#SQL_MDF+.rar
此篇文章 为学习笔记，如有什么错误问题可以提出来。
1 create table T_Employee (FNumber Varchar(20),FName varchar(20),FAge int,FSalary NUMERIC(10,2),PRIMARY KEY (FNumber));
2 insert T_Employee(FNumber,FName,FAge,FSalary) values('DEV001','TOM',25,6333);
3 insert T_Employee(FNumber,FName,FAge,FSalary) values('DEV002','JIM',35,2353);
4 insert T_Employee(FNumber,FName,FAge,FSalary) values('DEV003','HERO',25,3333);
5 insert T_Employee(FNumber,FName,FAge,FSalary) values('DEV004','CAM',29,4433);
6 insert T_Employee(FNumber,FName,FAge,FSalary) values('DEV005','HENRY',21,8933);
7 insert T_Employee(FNumber,FAge,FSalary) values('DEV006',26,9933);
1 SELECT * FROM T_Employee
2 SELECT FAge,FName,FSalary FROM T_Employee
3 SELECT FAge as 年龄,FName as 姓名,FSalary as 工资 FROM T_Employee -- as 是起个别名
4 SELECT * FROM T_Employee
5 where FSalary>5000 -- where 筛选条件
6
7 SELECT NEWID() -- 获取GUID 的函数
8 SELECT @@VERSION -- 获取数据库的版本
9
10 SELECT 1+1 AS 加法 ,GETDATE() as 日期,NEWID() as 编号
1 ------------------------------------------------聚合函数
2 select COUNT(*) from T_Employee; -- 计算数据条数
3 select MAX(FSalary) from T_Employee -- 聚合函数
4 select MIN(FSalary) from T_Employee -- 聚合函数
5 select AVG(FSalary) from T_Employee -- 聚合函数
6 select SUM(FSalary) from T_Employee -- 聚合函数
1 ------------------------------------------------数据排序
2
3 select * from T_Employee
4 order by FAge DESC -- order by ...DESC降序
5
6 select * from T_Employee
7 order by FAge ASC -- order by ...ASC 升序
8
9 select * from T_Employee
10 order by FAge ASC,FSalary desc -- age 升序排列age相同的时候salary降序
1 ------------------------------------------------通配符查询--模糊匹配
2 select * from T_Employee
3 where FName like '%m' -- % 多字符
4
5 select * from T_Employee
6 where FName like '%I%' -- % 多字符
7
8 select * from T_Employee
9 where FAge like '2_' -- 2 单字符
10
11 select * from T_Employee
12 where FName like '_A_' -- 2 单字符
13
14 select * from T_Employee
15 where FAge=21 or FAge=35 or FAge=25 -- 多值匹配
16
17 select * from T_Employee
18 where FAge in (23,21,35,25) -- 多值匹配
19
20 select * from T_Employee
21 where FAge>21 and FAge<35 -- 多值匹配
22
23 select * from T_Employee
24 where FAge between 20 and 30 -- 多值匹配
1 ------------------------------------------------NULL 的处理
2
3 select null+'sd' --null不知道+ 任何东西都是null
4 select null+1
5 select 'sd'+'s'
6
7 select * from T_Employee
8 where FName=null --什么都没有
9
10 select * from T_Employee
11 where FName is null --有了
12
13 select * from T_Employee
14 where FName is not null --有了
1 ------------------------------------------------数据分组(group by)
2 select FAge,COUNT (*) from T_Employee
3 group by FAge
4
5 select FAge,max(FSalary),COUNT (*) from T_Employee
6 group by FAge
7
8 ------------------------------------------------聚合不应出现在WHERE 子句中
9 select FAge,max(FSalary),COUNT (*) from T_Employee
10 where COUNT (*)>1
11 group by FAge
12 ----应该使用having语句
13 select FAge,max(FSalary),COUNT (*) from T_Employee
14 group by FAge
15 having COUNT (*)=1 --having是对分组后的信息的过滤，能用的列和select中能用的列是一样的。
16 --不能用having 代替where
17 --下面两个例子
18
19
20 select FAge,COUNT (*) from T_Employee
21 where FSalary >2000 --可以
22 group by FAge
23
24 select FAge,COUNT (*) from T_Employee --不行
25 group by FAge
26 having FSalary >2000 --HAVING 子句中的列'T_Employee.FSalary' 无效，因为该列没有包含在聚合函数或GROUP BY 子句中
1 ------------------------------------------------限制结果集范围(top)
2
3 select top 3 * from T_Employee ----找出前三名
4 order by FSalary desc
5
6 ---------------------------(子查询)-例子(按工资排序，降序，取出-5的数据)
7 select top 2 *from T_Employee
8 where FNumber not in (select top 3 FNumber from T_Employee order by FSalary desc)
9 order by FSalary desc
1 ------------------------------------------------抑制重复数据(distinct)
2 select FAge from T_Employee ---消除重复的
3 select distinct FAge from T_Employee ---当有多的字段的时候 消除的是各个字段比较都相同的消除
4 -------------------------------------------------------------------------
1 ------------------------------------------------联合结果集(unoin all)
2 -- 新建个表 做联合查询
3 create table T_TempEmployee (FId Varchar(20),FName varchar(20),FAge int,PRIMARY KEY (FId));
4 insert T_TempEmployee(FId,FName,FAge) values('DEV009','TOMs',21);
5 insert T_TempEmployee(FId,FName,FAge) values('DEV008','JIMes',19);
6 insert T_TempEmployee(FId,FName,FAge) values('DEV007','HERO',23);
7 insert T_TempEmployee(FId,FName,FAge) values('DEV006','Cat',22);
8 insert T_TempEmployee(FId,FName,FAge) values('DEV010','HENRY',21);
9 insert T_TempEmployee(FId,FAge) values('DEV0011',26);
10
11 --注意：上下的列数要相同 而且数据类型要相容
12 select FName ,FAge,'暂无工号' from T_TempEmployee
13 union all ------加上all 就不会去掉重复的行 建议加上all 避免数据丢失
14 select FName ,FAge,FNumber from T_Employee
15
16 -------------------------------报表例子
17 select '正式员工最高年龄',MAX(FAge) from T_Employee
18 UNION ALL
19 select '正式员工最小年龄',MIN(FAge) from T_Employee
20 UNION ALL
21 select '临时工最大年龄',MAX(FAge) from T_TempEmployee
22 UNION ALL
23 select '临时工最小年龄',MIN(FAge) from T_TempEmployee
24
25 -------------------------------报表例子
26 select FNumber as 工号,FSalary as 工资 from T_Employee
27 union all
28 select '合计工资',SUM (FSalary)from T_Employee
1 ------------------------------------------------数据库函数
2
3 ----1.数字函数
4 select ABS(-3) --绝对值
5 select CEILING(-3.62) --舍入到最大整数
6 select FLOOR(-3.62) --舍入到最小整数
7 select ROUND(-3.62,0) --四舍五入，有两个参数 后面的为精度
8
9 ----2.字符串函数
10 select LEN ('abc') --字符串长度
11 select upper('abc') --转换成大写
12 select LOWER('ABC') --转换成小写
13 select RTRIM (' bb ') --去掉右边空格
14 select LTRIM (' bb ') --去掉左边空格
15 select LTRIM(RTRIM (' bb ')) --去掉左右空格
16 select LEN(LTRIM(RTRIM (' bb '))) --验证去掉左右空格
17 select SUBSTRING ('abcdefg',2,3) --截取子字符串
18 select FName,SUBSTRING (FName,1,2) from T_Employee
19
20 ----3.日期函数
21 select GETDATE () --取得当前日期
22 select DATEADD (DAY,8,GETDATE()) --day 是模式加day还是year 还是hh 8是多少
23 --第三个为要处理的时间
24 select DATEDIFF(HH,GETDATE (),dateadd(day,-3,getdate()))--两个时间的差值GETDATE ()起始
25 select DATEPART(DAY,GETDATE()) --获取日期的一部分
26
27 ----4. 类型转换函数(cast ，convert)
28 select CAST('132'as int),cast('2008-08-08' as datetime),--写存储过程的时候经常用到CAST
29 convert(datetime,'2009-02-02'),convert(varchar(50),123)
30 select DATEPART(YEAR ,CAST ('2008-09-09' as datetime))
31
32 ----5.空值处理函数(isnull)
33 --isnull(expression,value):如果expression不为空则返回expression否则返回value
34 select ISNULL(FName ,'佚名') from T_Employee
35
36 ----6.CASE函数
37 ----先建个表
38 create table T_Customer (FName varchar(50),FLeve int)
39 insert T_Customer(FName,FLeve) values('tom',1)
40 insert T_Customer(FName,FLeve) values('cat',2)
41 insert T_Customer(FName,FLeve) values('james',3)
42 insert T_Customer(FName,FLeve) values('henry',1)
43 insert T_Customer(FName,FLeve) values('mm',2)
44
45 select FName,
46 (
47 case FLeve
48 when 1 then '普通会员'
49 when 2 then '会员'
50 when 3 then 'VIP'
51 else '未知客户类型'
52 end
53 ) as '客户类型'
54 from T_Customer
55 -----别忘了 END....
56
57 select FName,
58 (
59 CASE
60 WHEN FSalary <3000 THEN '低收入'
61 WHEN FSalary >=3000 AND FSalary <7000 THEN '中等收入'
62 ELSE '高收入'
63 END
64 ) as '收入水平'
65 FROM T_Employee
66
67 --------------------------------------例子
68 create table T_Scores (Date datetime,Name varchar(50),Score varchar(25))
69 insert T_Scores(Date,Name,Score) values(2008-8-8,N'拜仁',N'胜')
70 insert T_Scores(Date,Name,Score) values(2008-8-8,N'奇才',N'胜')
71 insert T_Scores(Date,Name,Score) values(2008-8-8,N'湖人',N'胜')
72 insert T_Scores(Date,Name,Score) values(2008-8-8,N'拜仁',N'负')
73 insert T_Scores(Date,Name,Score) values(2008-8-8,N'湖人',N'胜')
74
75 select Name,COUNT(Score)as N'胜'
76 from T_Scores
77 where Score=N'胜'
78 group by Name
79
80 select Name,
81 (
82 case Score
83 when N'胜' then 1
84 else 0
85 end
86 )as 胜,
87 (
88 case Score
89 when N'负' then 1
90 else 0
91 end
92 )as 负
93 from T_Scores
94
95 select Name,
96 SUM(case Score
97 when N'胜' then 1
98 else 0
99 end)as 胜,
100 SUM(case Score --------------好好看这个例子
101 when N'负' then 1
102 else 0
103 end)as 负
104 from T_Scores
105 group by Name
1 ----------------------------------再来一个例子 呼叫中心的例子
2 create table T_CallRecords(Id int identity(1,1),CallerNumber varchar(50),TelNum varchar(50),StartDateTime datetime,EndDateTime datetime)
3 insert T_CallRecords(CallerNumber,TelNum,StartDateTime,EndDateTime) values('001','0208888888','2010-7-10 10:01:23','2010-7-10 10:03:23')
4 insert T_CallRecords(CallerNumber,TelNum,StartDateTime,EndDateTime) values('002','354658888','2010-6-19 13:01:23','2010-6-19 13:09:23')
5 insert T_CallRecords(CallerNumber,TelNum,StartDateTime,EndDateTime) values('003','56569788','2010-7-11 14:01:23','2010-7-11 14:06:23')
6 insert T_CallRecords(CallerNumber,TelNum,StartDateTime,EndDateTime) values('001','56658888','2010-7-11 19:01:23','2010-7-11 19:04:23')
7 insert T_CallRecords(CallerNumber,TelNum,StartDateTime,EndDateTime) values('003','064888888','2010-7-10 12:01:23','2010-7-10 12:05:23')
8 insert T_CallRecords(CallerNumber,TelNum,StartDateTime,EndDateTime) values('003','0878788888','2010-6-10 16:01:23','2010-6-10 16:08:23')
9 insert T_CallRecords(CallerNumber,TelNum,StartDateTime,EndDateTime) values('002','889167979','2010-7-12 13:01:23','2010-7-12 13:09:23')
10 insert T_CallRecords(CallerNumber,TelNum,StartDateTime,EndDateTime) values('001','0289567','2010-7-11 19:01:23','2010-7-11 19:07:23')
11 insert T_CallRecords(CallerNumber,TelNum,StartDateTime,EndDateTime) values('002','0892361','2010-6-10 10:01:23','2010-6-10 10:06:23')
12 insert T_CallRecords(CallerNumber,TelNum,StartDateTime,EndDateTime) values('002','2721888','2010-7-16 14:01:23','2010-7-16 14:08:23')
13 insert T_CallRecords(CallerNumber,TelNum,StartDateTime,EndDateTime) values('002','03212358','2010-6-10 11:01:23','2010-6-10 11:04:23')
14 insert T_CallRecords(CallerNumber,TelNum,StartDateTime,EndDateTime) values('003','1254875221','2010-6-19 9:01:23','2010-6-19 9:11:23')
15
16 --所有数据中通话时间最长的条记录
17 select DATEDIFF(SECOND,StartDateTime,EndDateTime) from T_CallRecords
18
19 select top 5 * from T_CallRecords
20 order by DATEDIFF(SECOND,StartDateTime,EndDateTime) desc
21
22 --输出素有数据中拨打长途号码(对方号码以开头)的总时长。提示：like 、sum
23 select * from T_CallRecords
24 where TelNum like '0%'
25
26 select SUM(DATEDIFF (SECOND,StartDateTime,EndDateTime)) from T_CallRecords
27 where TelNum like '0%'
28
29 --输出本月通话总时长最多的前三个呼叫员得编号。提示：datediff(month....),sum,order by
30
31 select DATEDIFF(MONTH,CONVERT (datetime,'2011-6-1'),CONVERT(datetime,'2011-7-5')),
32 DATEDIFF(MONTH,CONVERT (datetime,'2011-6-29'),CONVERT(datetime,'2011-7-1'))
33 --说明无论多近的两个月都相差一个月
34
35 select CallerNumber,TelNum,StartDateTime,DATEDIFF(MONTH ,StartDateTime ,'2010-7-30 10:10:10')
36 from T_CallRecords --当月。
37
38 select* from T_CallRecords
39 where DATEDIFF(MONTH ,StartDateTime ,'2010-7-30 10:10:10')=0 --取出当月的记录
40
41 select top 3 CallerNumber ,sum(DATEDIFF(SECOND,StartDateTime,EndDateTime)) from T_CallRecords
42 where DATEDIFF(MONTH ,StartDateTime ,'2010-7-30 10:10:10')=0
43 group by CallerNumber
44 order by SUM(DATEDIFF(SECOND,StartDateTime,EndDateTime)) desc
45
46 --输出本月拨打电话次数最多的前三个呼叫员的编号提示：group by,count(*)
47 select top 3 CallerNumber,COUNT(*) from T_CallRecords
48 where DATEDIFF(MONTH ,StartDateTime ,'2010-7-30 10:10:10')=0
49 group by CallerNumber
50 order by COUNT (*) desc
51
52 --汇总[市内号码总时长][长途号码总时长]
53 select CallerNumber,TelNum,DATEDIFF(SECOND,StartDateTime,EndDateTime)
54 from T_CallRecords
55
56 union all
57
58 select '汇总',
59 sum(
60 (case
61 when TelNum not like '0%' then DATEDIFF(SECOND,StartDateTime,EndDateTime)
62 else 0
63 end
64 ))as 市内总时长,
65 sum(
66 (case
67 when TelNum like '0%' then DATEDIFF(SECOND,StartDateTime,EndDateTime)
68 else 0
69 end
70 ))as 长途总时长
71 from T_CallRecords
1 ------------------------------------------------表连接(Join)
2
3 --排序规则的后半部份即后缀 含义：
4 -- _BIN 二进制排序
5 -- _CI(CS) 是否区分大小写，CI不区分，CS区分
6 -- _AI(AS) 是否区分重音，AI不区分，AS区分
7 -- _KI(KS) 是否区分假名类型,KI不区分，KS区分
8 -- _WI(WS) 是否区分宽度 WI不区分，WS区分 区分大小写:如果想让比较将大写字母和小写字母视为不等，请选择该选项。
9 --------COLLATE主要用于对字符进行排序，经常出现在表的创建语句中
10 --------Chinese_PRC_指针对大陆简体字UNICODE的排序规则
11 create table [T_Customers](
12 [ID] [INT] NOT NULL,
13 [NAME] [nvarchar](50) collate Chinese_PRC_CI_AS NULL,
14 [Age] [int] null
15 );
16 insert [T_Customers] ([ID],[NAME],[Age]) values (1,N'Tom',15);
17 insert [T_Customers] ([ID],[NAME],[Age]) values (2,N'Cat',20);
18 insert [T_Customers] ([ID],[NAME],[Age]) values (3,N'James',25);
19 insert [T_Customers] ([ID],[NAME],[Age]) values (4,N'Hero',18);
20 insert [T_Customers] ([ID],[NAME],[Age]) values (5,N'Kobe',13);
21
22 create table [T_Orders](
23 [Id] [int] not null,
24 [BillNo] [varchar](50) collate Chinese_PRC_CI_AS NULL,
25 [CustomerId] [int] null
26 );
27 insert T_Orders ([Id],[BillNo],[CustomerId]) values (1,N'001',1)
28 insert T_Orders ([Id],[BillNo],[CustomerId]) values (2,N'002',1)
29 insert T_Orders ([Id],[BillNo],[CustomerId]) values (3,N'003',3)
30 insert T_Orders ([Id],[BillNo],[CustomerId]) values (4,N'004',2)
31 insert T_Orders ([Id],[BillNo],[CustomerId]) values (5,N'005',2)
32 insert T_Orders ([Id],[BillNo],[CustomerId]) values (6,N'006',4)
33 insert T_Orders ([Id],[BillNo],[CustomerId]) values (7,N'007',5)
34 insert T_Orders ([Id],[BillNo],[CustomerId]) values (8,N'008',5)
35 insert T_Orders ([Id],[BillNo],[CustomerId]) values (9,N'009',2)
36
37 -------start
38
39 select * from T_Orders
40
41 select o.BillNo,c.NAME,c.Age
42 from T_Orders as o join T_Customers as c on o.CustomerId=c.ID
43 --表也可以有别名
44
45 --要求显示所有年龄大于15岁的顾客购买的订单号、客户姓名、年龄
46 select o.BillNo,c.NAME,c.Age
47 from T_Customers as c join T_Orders as o on c.ID=o.CustomerId
48 where c.Age>15
49
50 --要求显示年龄大于平均年龄的顾客购买的订单
51 select o.BillNo
52 from T_Orders as o join T_Customers as c on o.CustomerId= c.ID
53 where c.Age>(select AVG (Age) from T_Customers)
1 ----------------------------------------*子查询
2 select * from
3 (
4 select *from T_Customers ----子查询当作一个表处理
5 )as c
6
7 ---单值子查询 只有一行一列
8 select 'F1',MIN(Age),(select MAX (Age)from T_Customers)from T_Customers
9
10 ---多行一列的子查询
11 select Name from T_Customers
12 where Age in (select Age from T_Customers where Age>=20)
13
14 -- 输出降序排列的 2-4 的数据 不适用top
15
16 select NAME,Age
17 from(
18 select ROW_NUMBER() over(order by age desc) as rownum,
19 NAME,Age from T_Customers)as e
20 where e.rownum >=2 and e.rownum <=4
欢迎大家来支持我，我将会把一路走来的东西，都发表成文章供大家参看。
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
