主键有两种选用策略:业务主键和逻辑主键.
业务主键是使用有业务意义的字段做主键,比如身份证号,银行账号.
逻辑主键是使用没有任何业务意义的字段做主键.因为很难保证业务主键不会重复,推荐用逻辑主键
(2)表间关联,外键(ForeignKey)
(3)SQLSever的管理
bit位,相当于bool类型,0,1
char(10) 字符 长度10 不足用空格填充
int整型
bigint 超大整型
nvarchar(MAX) 最大,无限大,可能有中文信息,日文
varchar() 无中文信息 不会用空格填充 var(variable)
datatime 日期
--注释内容
关键字,表名,变量名对大小写是不敏感的
CREATE TABLE TABLE_NAME(ID int NOT NULL,NAME nvarchar(50),AGE int NULL)
INSERT INTO TableName(Column1, Column2, ..., ColumnN) VALUES(Column1Value, Column2Value, ..., ColumnNValue)
DELETE FROM TableName WHERE whereCondition
UPDATE TableName SET Column1 = Column1Value, Column2=Column2Value, ..., ColumnN=ColumnNValue WHERE whereCondition
SELECT * FROM TableName (WHERE whereCondition)
常用主键类型,int,bigint,uniqueidentifier(又称GUID,UUID)
guid算法使用mac地址,地址,纳秒级时间,芯片id码等算出来,每次guid永远不会重复
.net中生成guid的方法,Guid.NewGuid(),返回Guid类型 SQL中newid(),比如select newid()
(*)int自增字段的优点:占用空间小,无需开发人员干预,易读;缺点: 效率低,数据导入导出的时候很痛苦
(*)Guid的优点:效率高,数据导入导出方便; 缺点:占用空间太大,不容易读懂(慢慢成为主流)
可以给字段设置默认值,如果guid类型主键的默认设为newid(),就会自动生成,很少这么干
中文前面加N
insert into Person2(Id,Name,Age)values(newid(),'tom',30)
update Person1 set NickName=N'青年人' where Age>=20
<>不等于
(5)表删除
删除表中全部数据
DELETE FROM TABLE_NAME WHERE
select COUNT(*) FROM Person1
select MAX(Age) FROM Person1
select MIN(Age) FROM Person1
select avg(Age) FROM Person1
select sum(Age) FROM Person1
select COUNT(*) FROM Person1 Where
select * from Person1 order by Age (ASC升序 DESC降序) ascend descend
select * from Person1 order by Age DESC,Number ASC;按照age降序,number升序
order by 要放在where句子后面
单字符通配符“ _”,它匹配单个出现的字符, LIKE '_erry' 任意第一个字符
多字符通配符“ %”,它匹配任意长度字符,0到多个 LIKE '%n%' 包含n的所有字符
(4)sql中的null的理解
select * from Person1
where Name is null 或者 where Name is not null
(5)多值匹配
select * from Person1
where Age=13 or Age=15 or Age=30
使用where Age in(13,15,30) 简单
where Age>13 and Age <15 清晰
(where Age between 13 and 15) 比较少用
(6)数据分组
Group by 放在 where 后面
select Age as 年龄,count(*) as 人数 from Person1
group by Age
根据年龄进行分组,
聚合函数不应出现在where中
having出现在group by之后
select Age as 年龄,count(*) as 人数 from Person1
group by Age
Having count(*) > 1
order by 人数 ASC
注意先后顺序
Having是对分组后信息的过滤,能用的列和select中能用的列是一样
(8)Top 限制结果集的行数
select top 3 * from ... 前三行
select top 3 * from Person1
where Number Not In (select top 5 Number from Person1 order by Age DESC)
order by Age DESC
显示第6条以下的前3条行
SQL2005后增加了Row_Number函数简化实现,后面会讲
(9) Distincct 去掉重复数据
select DISTINCT Age as 年龄 from Person1
select Age,NickName from Person1
select DISTINCT Age,NickName from Person1
DISTINCT针对整行重复,不是一个字段的重复, 这里的指的是你检索出来的行,如果Age,NickName两个字段,就是这样的两个字段的行,其实实际也很符合,如果nickname不一样,不显示就失去数据了
案例1
select '正式工最高年龄',MAX(Age) FROM Person1
union all
select '正式工最低年龄',MAX(Age) FROM Person1
union all
select '临时工最高年龄',MAX(Age) FROM Person2
union all
select '临时工最高年龄',MAX(Age) FROM Person2
案例2
查询正式工的信息,包括工号,工资,最后一行加上所有员工的工资合计
select FNumber,FSalary FROM T_Employee
union all
select '工资合计',sum(Fsalary)
ABS():求绝对值
CEILING():舍入到最大整数 3.33->4, -3.6->-4
FlOOR() :舍入到最小整数
ROUND(): 四舍五入 有两个参数 ROUND(半径,小数精度)
(12)字符串函数
LEN() : 求字符串长度 LEN('abc') : 3 ??字符串右侧的空格SQL中似乎不识别,比如len('abd ')=3
LTRIM():字符串左侧的空格去掉
RLTRIM() :字符串右侧的空格去掉
SUBSTRING() : 取子字符串, 三个参数, SUBSTRING(主字符串,起始,长度),从1开始计数,substring('123',1,1)结果是'1'
(13)日期函数
GETDATE() : 取得当前时间
DATEADD(datepart,number,date) : 计算增加以后的日期number是日期增量
quarter qq,q 季度
month mm,m 月份
dayofyear dy,y 当年度的第几天
day dd,d 日
week wk,ww 当年度的第几周
weekday dw,w 星期几
hour hh 小时
minute mi,n 分
second ss,s 秒
millisecond ms 毫秒
select getdate() as 今天
,DateAdd(day,-1,getdate()) as 前天
,DateAdd(hh,-1,getdate()) as 前一个小时
,DateAdd(mm,-1,getdate()) as 前一个月
例1:select DateDiff(hh,getdate(),DateAdd(hh,1,getdate())) 结果为1
例2:select DateDiff(year,FinDate,getdate()),count(*) as 入职年数 from T_Employee
group by DateDiff(year,FinDate,getdate())
按照入职的年数进行分组
DatePart(datepart,date) : 返回一个日期的特定部分
例1:select DatePart(year,getdate()),DatePart(month,getdate())
例2:select DatePart(year,FInDate),count(*) from T_Employee
group by DatePart(year,FInDate)
CAST(expression AS data_type)
CONVERT(data_type,expression)
select cast('123' as int )+1,cast('2008-09-09' as datetime),
convert(int,'333'),convert(datetime,'2008-09-09')
select DatePart(year, cast('2008-09-09' as datetime)) : 确保类型正确
ISNULL(expression,value): 如果 expression不为空,返回expression,否则返回value
例子:select ISNULL(Name,'佚名') from Person1
Case i
when value1 then returnvalue1
when value2 then returnvalue2
...
例子1:
select FName,
(
Case FLevel
when 1 then '普通客户 '
when 2 then '会员 '
when 3 then 'VIP'
else ' 未知类型'
end
) as 客户类型
from T_Customer
例子2 :
select FName,
(
case
when Fsalary< 2000 then '低收入'
when Fsalary>= 2000 and Fsalary <5000 then '中等收入 '
when Fsalary>= 5000 then '高收入'
end
) as 收入水平
from T_Customer
五.课后练习(*)
select 单号 ,
(
case
when 金额 >0 then 金额
else 0
end
) as 收入,
(
case
when 金额 <0 then ABS( 金额)
else 0
end
) as 支出
from T_1
练习二
2008/8/8 拜仁 胜
2008/8/9 奇才 胜
2008/8/9 湖人 胜
2008/8/10 拜仁 负
2008/8/8 拜仁 负
2008/8/12 奇才 胜
显示成
拜仁 1 2
湖人 1 0
奇才 2 0
下面是SQL语句:
CREATE TABLE [T_Scores]( [Date] [datetime] NULL, [Name] [nvarchar] (50) NULL, [Score] [nvarchar] (50) NULL ); INSERT [T_Scores] ( [Date],[Name] ,[Score]) VALUES(CAST ('2008-08-08' as datetime),N '拜仁' ,N' 胜') INSERT [T_Scores] ( [Date],[Name] ,[Score]) VALUES(CAST ('2008-08-09' as datetime),N '奇才' ,N' 胜') INSERT [T_Scores] ( [Date],[Name] ,[Score]) VALUES(CAST ('2008-08-09' as datetime),N '湖人' ,N' 胜') INSERT [T_Scores] ( [Date],[Name] ,[Score]) VALUES(CAST ('2008-08-10' as datetime),N '拜仁' ,N' 负') INSERT [T_Scores] ( [Date],[Name] ,[Score]) VALUES(CAST ('2008-08-08' as datetime),N '拜仁' ,N' 负') INSERT [T_Scores] ( [Date],[Name] ,[Score]) VALUES(CAST ('2008-08-12' as datetime),N '奇才' ,N' 胜') SELECT Name , SUM( CASE Score WHEN ' 胜' THEN 1 ELSE 0 END ) AS 胜, SUM( CASE Score WHEN ' 负' THEN 1 ELSE 0 END ) AS 负 FROM T_Scores GROUP BY Name
练习3
创建一张表,记录电话呼叫员的工作流水,记录呼叫员的编号,对方号码,通话开始时间,通话结束时间.
建表,插数据都自己写SQL语句
要求:输出所有数据中通话时间最长的5条记录.
输出所有数据中拨打长途号码(对方号码以0开头)的总时长
输出本月通话总时长最多的前三个呼叫员的编号
输出本月拨打号码次数最多的前三个呼叫员的编号
输出所有数据的拨号流水,并且在最后的一行添加总呼叫次数
-记录呼叫员编号,对方号码,通话时长
-...
-汇总[市内号码总时长][长途号码总时长]
SQL的代码:
CREATE TABLE [T_PhoneWork] ( [id] int identity (1, 1) primary key , [WorkNum] [nvarchar] (50) NOT NULL, [PhoneNum] [nvarchar] (20) NULL, [StartTime] [datetime] NULL, [EndTime] [dateTime] NULL ); INSERT INTO [T_PhoneWork]([WorkNum], [PhoneNum],[StartTime] ,[EndTime]) VALUES('100001' ,'13888888888', CAST('2013-1-1 7:10:10' AS datetime),CAST ('2013-1-1 7:20:10' AS datetime)) INSERT INTO [T_PhoneWork]([WorkNum], [PhoneNum],[StartTime] ,[EndTime]) VALUES('100002' ,'13887888788', CAST('2013-1-1 8:10:10' AS datetime),CAST ('2013-1-1 8:30:10' AS datetime)) INSERT INTO [T_PhoneWork]([WorkNum], [PhoneNum],[StartTime] ,[EndTime]) VALUES('100003' ,'13888688868', CAST('2013-1-1 7:20:10' AS datetime),CAST ('2013-1-1 8:20:10' AS datetime)) INSERT INTO [T_PhoneWork]([WorkNum], [PhoneNum],[StartTime] ,[EndTime]) VALUES('100004' ,'13838883888', CAST('2013-1-1 8:10:10' AS datetime),CAST ('2013-1-1 10:20:10' AS datetime)) INSERT INTO [T_PhoneWork]([WorkNum], [PhoneNum],[StartTime] ,[EndTime]) VALUES('100001' ,'13844888888', CAST('2013-1-1 10:10:10' AS datetime),CAST ('2013-1-1 12:20:10' AS datetime)) INSERT INTO [T_PhoneWork]([WorkNum], [PhoneNum],[StartTime] ,[EndTime]) VALUES('100002' ,'013888885888', CAST('2013-1-1 13:16:10' AS datetime),CAST ('2013-1-1 14:20:10' AS datetime)) INSERT INTO [T_PhoneWork]([WorkNum], [PhoneNum],[StartTime] ,[EndTime]) VALUES('100003' ,'13888588888', CAST('2013-1-1 14:10:10' AS datetime),CAST ('2013-1-1 16:20:10' AS datetime)) INSERT INTO [T_PhoneWork]([WorkNum], [PhoneNum],[StartTime] ,[EndTime]) VALUES('100001' ,'013868886888', CAST('2013-1-1 12:10:10' AS datetime),CAST ('2013-1-1 17:20:10' AS datetime)) INSERT INTO [T_PhoneWork]([WorkNum], [PhoneNum],[StartTime] ,[EndTime]) VALUES('100004' ,'13788878877', CAST('2013-1-1 14:10:10' AS datetime),CAST ('2013-1-1 16:20:10' AS datetime)) INSERT INTO [T_PhoneWork]([WorkNum], [PhoneNum],[StartTime] ,[EndTime]) VALUES('100001' ,'13787878877', CAST('2013-1-1 16:10:10' AS datetime),CAST ('2013-1-1 18:20:10' AS datetime)) INSERT INTO [T_PhoneWork]([WorkNum], [PhoneNum],[StartTime] ,[EndTime]) VALUES('100002' ,'13787878877', CAST('2013-1-1 12:10:10' AS datetime),CAST ('2013-1-1 14:20:10' AS datetime)) INSERT INTO [T_PhoneWork]([WorkNum], [PhoneNum],[StartTime] ,[EndTime]) VALUES('100003' ,'13788078877', CAST('2013-1-1 16:10:10' AS datetime),CAST ('2013-1-1 17:20:10' AS datetime)) INSERT INTO [T_PhoneWork]([WorkNum], [PhoneNum],[StartTime] ,[EndTime]) VALUES('100005' ,'13799078877', CAST('2013-5-1 12:10:10' AS datetime),CAST ('2013-5-2 22:20:10' AS datetime)) CREATE TABLE [T_PhoneWork] ( [id] int identity (1, 1) primary key , [WorkNum] [nvarchar] (50) NOT NULL, [PhoneNum] [nvarchar] (20) NULL, [StartTime] [datetime] NULL, [EndTime] [dateTime] NULL ); INSERT INTO [T_PhoneWork]([WorkNum], [PhoneNum],[StartTime] ,[EndTime]) VALUES('001' ,'020888888', CAST('2010-7-10 10:00:00' AS datetime),CAST ('2010-7-10 10:05:03' AS datetime)) INSERT INTO [T_PhoneWork]([WorkNum], [PhoneNum],[StartTime] ,[EndTime]) VALUES('001' ,'020888888', CAST('2010-7-11 13:00:00' AS datetime),CAST ('2010-7-11 13:01:10' AS datetime)) INSERT INTO [T_PhoneWork]([WorkNum], [PhoneNum],[StartTime] ,[EndTime]) VALUES('001' ,'8934343434', CAST('2010-7-11 14:06:00' AS datetime),CAST ('2010-7-11 14:09:00' AS datetime)) INSERT INTO [T_PhoneWork]([WorkNum], [PhoneNum],[StartTime] ,[EndTime]) VALUES('002' ,'9837434777', CAST('2010-7-13 21:06:00' AS datetime),CAST ('2010-7-13 21:08:08' AS datetime)) INSERT INTO [T_PhoneWork]([WorkNum], [PhoneNum],[StartTime] ,[EndTime]) VALUES('002' ,'0213434343', CAST('2010-6-29 20:11:00' AS datetime),CAST ('2010-6-29 20:16:06' AS datetime)) INSERT INTO [T_PhoneWork]([WorkNum], [PhoneNum],[StartTime] ,[EndTime]) VALUES('001' ,'787878778', CAST('2010-7-15 13:16:00' AS datetime),CAST ('2010-7-15 13:26:00' AS datetime)) INSERT INTO [T_PhoneWork]([WorkNum], [PhoneNum],[StartTime] ,[EndTime]) VALUES('003' ,'0334343444', CAST('2010-7-13 11:16:00' AS datetime),CAST ('2010-7-13 11:17:09' AS datetime)) INSERT INTO [T_PhoneWork]([WorkNum], [PhoneNum],[StartTime] ,[EndTime]) VALUES('003' ,'676777333', CAST('2010-7-19 19:26:02' AS datetime),CAST ('2010-7-19 19:30:33' AS datetime)) INSERT INTO [T_PhoneWork]([WorkNum], [PhoneNum],[StartTime] ,[EndTime]) VALUES('001' ,'89923434333', CAST('2010-6-19 15:16:02' AS datetime),CAST ('2010-6-19 15:26:10' AS datetime)) INSERT INTO [T_PhoneWork]([WorkNum], [PhoneNum],[StartTime] ,[EndTime]) VALUES('004' ,'400400400', CAST('2010-6-19 15:16:02' AS datetime),CAST ('2010-6-19 15:26:10' AS datetime))
1.输出所有数据中通话时间最长的5条记录. SELECT TOP 5 *FROM T_PhoneWork ORDER BY (DateDiff (s, StartTime,EndTime )) DESC 2.输出所有数据中拨打长途号码(对方号码以0开头)的总时长 SELECT SUM ( DateDiff(s ,StartTime, EndTime) ) AS 长途总时长 FROM T_PhoneWork WHERE PhoneNum LIKE '0%' 3.输出本月通话总时长最多的前三个呼叫员的编号 SELECT TOP 3 WorkNum,count (*) FROM T_PhoneWork WHERE DateDiff (month, StartTime,getdate ()) = 0 group by WorkNum order by sum( datediff(s ,StartTime, EndTime)) DESC SELECT TOP 3 WorkNum FROM T_PhoneWork WHERE DateDiff (month, StartTime,'2010-7-2 9:12:00' ) = 0 group by WorkNum order by sum( datediff(s ,StartTime, EndTime)) DESC 4.输出本月拨打号码次数最多的前三个呼叫员的编号 select top 3 WorkNum from T_PhoneWork WHERE DateDiff (month, StartTime,getdate ()) = 0 group by WorkNum order by count(*) DESC
5.注意,union的两个原则,类型要相容,这个容易犯错,谨记 输出所有数据的拨号流水,并且在最后的一行添加总呼叫次数 -记录呼叫员编号,对方号码,通话时长 -... -汇总[市内号码总时长][长途号码总时长] select WorkNum as 呼叫员编号,PhoneNum as 对方号码 ,DateDiff( s,StartTime ,EndTime) as 通话时长 from T_PhoneWork union all select ' 汇总', cast( sum( CASE substring (PhoneNum, 1,1 ) when '0' then 0 else DateDiff (s, StartTime,EndTime ) end ) as nvarchar( 50)), cast( sum( CASE substring (PhoneNum, 1,1 ) when '0' then DateDiff(s ,StartTime, EndTime) else 0 end ) as nvarchar( 50)) from T_PhoneWork 参考做法: select WorkNum as 呼叫员编号,PhoneNum as 对方号码 ,DateDiff( s,StartTime ,EndTime) as 通话时长 from T_PhoneWork union all select ' 汇总', cast( sum( CASE when PhoneNum not like '0%' then DateDiff( s,StartTime ,EndTime) else 0 end ) as nvarchar( 50)), cast( sum( CASE when PhoneNum like '0%' then DateDiff (s, StartTime,EndTime ) else 0 end ) as nvarchar( 50)) from T_PhoneWork
30.索引
全表扫描:对数据进行检索(select)效率最差的是全表扫描,就是一条条的找
如果没有目录,查汉语字典就要一页页的翻,而有了目录只要查目录即可.为了提高检索速度,可以为经常进行检索的列添加索引,相当于创建目录
建立索引优点:检索速度快
缺点:占磁盘空间,降低编辑表的操作,如insert,update
(*)即使创建了索引,仍然有可能全表扫描,比如like,函数,类型转换等
31.表连接jion 主流不增加外键
有客户表 和 订单表 ,关联查询 [table1] as [别名] join [table2] as [别名] on [约束条件]
select o . BillNo , p . Name , p . Age
32.子查询
将一个查询语句作为一个结果,作为一个结果集供其他SQL语句使用,就像使用普通的表一样,被当做结果集的查询语句被称为子查询.
左右可以使用表的地方几乎都可以使用子查询来代替
select * from
(select * from Person1) as p1
单值作为子查询 一行一列,单值查询
单列多行的数据可看做一个数据集,单列多行的子查询
例子
select * from T_Reader where FYearOfJoin IN( 2001,2003 ) select * from T_Reader where FYearOfJoin IN (select FYearPublished from T_Book) --查询书记出版那年加入协会的人的信息 限制结果集 . 返回第行到第行的数据 select * from ( select ROW_NUMBER() OVER( ORDER BY Age ASC) AS rownum , Number ,Name, Age,NickName From Person1 ) as e1 where e1. rownum>=3 and e1. rownum<=5 经典的应用是: 分页的操作 select * from ( select ROW_NUMBER() over(order by k1 .TimeDiff DESC ) as rownum,k1 .WorkNum, k1.TimeDiff as d from ( select WorkNum, sum(datediff (second, StartTime,EndTime )) as TimeDiff from T_PhoneWorkTest group by WorkNum )as k1 ) as e1 where e1. rownum >= 2 and e1. rownum <=3