数据库概述:
DBMS(databasemanagement system)数据库管理系统
SQLServer数据库MSSQLServer
Catalog分类(又叫数据库DataBase、表空间Tablespace)
不同的类的数据应该放到不同的数据库中
便于对各个Catalog进行个性化管理
避免命名冲突
安全性更高
Table表
不同类型的资料放到不同的格子中将这种区域叫做表table
不同的表根据放的数据不同进行空间的优化
Column列field字段
SQLSever管理:
创建数据库,创建表,设置主键
SQLServer2008ManagementStudio中:编辑200行。05中:打开表。
常用字段类型:bit(可选值0、1)、datetime、int、varchar、nvarchar(
可能含有中文用nvarchar)
Nvarchar(50)、Nvarchar(MAX)
varchar、nvarchar 和char(n)的区别: char(n)不足长度n的部分用空格
填充。Var:Variable,可变的。
1、主键primkey
主键是数据行的唯一标识,不会重复的列才能当主键,一个表可以没有主键但是会非常难以处理,因此没有特殊的理由表都要设定主键
一般两种数据类型可以设置主键
SQLServer中两种常用的主键数据类型:
int(或bigint)+标识列(又称自动增长字段);
uniqueidentifier(又称Guid、UUID)
用标识列实现字段自增可以避免并发等问题,不要开发人员控制自增。
用标识列的字段在Insert的时候不用指定主键的值。将字段的“是标识列”设置为“是”即可,一个表只能有一个标识列。
SQLServer中生成GUID的函数newid()
.Net中生成Guid的方法:Guid.NewGuid(),返回是Guid类型。
(*)Int自增字段的优点:占用空间小、无需开发人员干预、易读;
缺点:效率低;数据导入导出的时候很痛苦。
(*)Guid的优点:效率高、数据导入导出方便;
缺点:占用空间大、不易读。
业界主流倾向于使用Guid。
表间关联、外键(ForeignKey)
2、SQL语句入门
SQL语句是和DBMS“交谈”专用的语句,不同DBMS都认SQL语法。
SQL语句中字符串用单引号。
SQL语句是大小写不敏感的,不敏感指的是SQL关键字(包括表明,列名等),字符串值(‘’内部的字符)还是大小写敏感的
创建表、删除表不仅可以手工完成,还可以执行SQL语句完成,在自动化部署、数据导入中用的很多
CREATE TABLE T_Person(Id int NOT NULL,Name nvarchar(50),Age int NULL)
Drop table T_Person1
简单的Insert语句。
INSERT INTOT_Person(Id,Name,Age)VALUES(1,'Jim',20)
(*) SQL主要分DDL(数据定义语言)和DML(数据操作语言)两类。
Create Table、Drop Table、AlterTable等属于DDL,Select、Insert、Update、Delete等属于DML
3、数据插入(字段)
Insert语句可以省略表名后的列名,但是不推荐。
如果插入的行中有些字段的值不确定,那么Insert的时候不指定那些列即可。
可以给字段默认值,如Guid类型主键的默认值设定为newid()就会自动生成。
主键:insert intoPerson3(Name,Age)values('lily',38);
insert into Person4(Id,Name,Age)values(newid(),'tom',30);
4、数据更新
更新一个列:UPDATET_Person SET Age=30
更新多个列:UPDATE T_Person Set Age=30,Name=‘tom’
更新一部分数据:UPDATE T_Person Set Age=30 where Name=‘tom’,
用where语句表示只更新Name是’tom’的行,
注意SQL中等于判断用单个=,而不是==。
Where中还可以使用复杂的逻辑判断
UPDATE T_Person SetAge=30 where Name=‘tom’orAge<25,
or相当于C#中的||(或者)
update Person1 setNickName=N'二十岁'where (Age>20 and Age<30) or(Age=80)
Where中可以使用的其他逻辑运算符:or、and、not、<、>、>=、<=、!=(或<>)等
5、数据删除
删除表中全部数据:DELETEFROM T_Person。
Delete只是删除数据,表还在,和DropTable不同。
Delete 也可以带where子句来删除一部分数据:
DELETE FROMT_Person WHERE FAge > 20
5、数据检索(过滤语句)
执行备注中的代码创建测试数据表。
简单的数据检索 :SELECT * FROM T_Employee
只检索需要的列 :SELECT FNumber FROM T_Employee 、
SELECT FName,FAge FROM T_Employee
列别名:SELECT FNumber AS 编号,FName AS 姓名,FAge ASAge111 FROM T_Employee
使用where检索符合条件的数据:
SELECT FName FROM T_EmployeeWHERE FSalary<5000。
还可以检索不与任何表关联的数据:select 1+1;select newid(); select getdate();
创建表并赋值
CREAT TABLET_Employee
(Fnumber VARCHAR(20),FNameVARCHAR(20),FAge INT,FSalary NUMBERIC(10,2),PRIMARY KEY(Fnumber));
INSERT INTO T_Employee(FNumber,FName,FAge,FSlary)
VALUES(‘DEV002’,’Jerry’,28,2300)
INSERT INTO T_Employee(FNumber,FName,FAge,FSlary)
VALUES(‘SELES001’,’John’,23,5000)
INSERT INTOT_Employee(FNumber,FName,FAge,FSlary )
VALUES(‘SELES002’,’Kerry’,28,6200)
INSERT INTOT_Employee(FNumber,FName,FAge,FSlary )
VALUES(‘SELES003’,’Stone’,22,1200)
INSERT INTO T_Employee(FNumber,FName,FAge,FSlary)
VALUES(‘HR001,’Jane’,23,2200)
INSERT INTOT_Employee(FNumber,FName,FAge,FSlary )
VALUES(‘HR002,’Tina,25,5200)
INSERT INTOT_Employee(FNumber,FName,FAge,FSlary )
VALUES(‘IT001’,’Smith’,28,3900)
INSERT INTOT_Employee(FNumber ,FAge,FSlary )
VALUES(‘IT002’,27,2800)
6、数据汇总
聚合函数:对数据进行统计的函数
SQL聚合函数:MAX(最大值)、MIN(最小值)、AVG(平均值)、SUM (和)、COUNT(数量)
Select count(*)from T_Employee
Select max(Fslary)from T_Employee
Select min(Fslary)from T_Employee
Select avg(Fslary)from T_Employee
Select sum(Fslary)from T_Employee
大于25岁的员工的最高工资 :
SELECTMAX(FSalary) FROM T_Employee WHERE FAge>25
最低工资和最高工资:
SELECT MIN(FSalary),MAX(FSalary) FROM T_Employee
工资高于5000的人数
Select count(*)from T_Employee
WHERE FSalary >=5000
7、数据排序
ORDER BY子句位于SELECT语句的末尾,它允许指定按照一个列或者多个列进行排序,还可以指定排序方式是升序(从小到大排列,ASC),还是降序(从大到小排列,DESC)。
按照年龄升序排序所有员工信息的列表:
SELECT *FROMT_Employee
ORDER BY FAge ASC
按照年龄从大到小排序,如果年龄相同则按照工资从大到小排序 :
SELECT * FROMT_Employee
ORDER BY FAgeDESC,FSalary DESC
ORDER BY子句要放到WHERE子句之后 :
SELECT *FROMT_Employee
WHERE FAge>23
ORDER BY Fage DESC,FSalaryDESC
8、通配符过滤
通配符过滤使用LIKE :
单字符匹配的通配符为半角下划线“_”,它匹配单个出现的字符。
以任意字符开头,剩余部分为“erry” :
SELECT * FROMT_Employee
WHERE FName LIKE '_erry'
多字符匹配的通配符为半角百分号“%”,它匹配任意次数(零或多个)出现的任意字符。
“k%”匹配以“k”开头、任意长度的字符串。
检索姓名中包含字母“n”的员工信息 :
SELECT * FROMT_Employee
WHERE FName LIKE '%n%'
9、空值处理:
数据库中,一个列如果没有指定值,那么值就为null,
这个null和C#中的null,数据库中的null表示“不知道”,而不是表示没有。
因此select null+1结果是null,因为“不知道”加1的结果还是“不知道”。
SELECT * FROMT_Employee WHERE FNAME=null ;
SELECT * FROMT_Employee WHERE FNAME!=null ;
都没有任何返回结果,因为数据库也“不知道”。
SQL中使用is null、isnot null来进行空值判断:
SELECT * FROMT_Employee WHERE FNAME is null ;
SELECT * FROMT_Employee WHERE FNAME is not null ;
10、多值匹配
SELECTFAge,FNumber,FName FROM T_Employee
WHERE FAge IN (23,25,28)
SELECT * FROM T_Employee
WHERE FAge in (21,22,23,24,25,28)
范围值:SELECT * FROM T_Employee
WHERE FAGE>=23AND FAGE <=27 ;
SELECT * FROM T_Employee
WHERE FAGE BETWEEN23 AND 27
11、数据分组
按照年龄进行分组统计各个年龄段的人数:
SELECTFAge,Count(*) FROM T_Employee
GROUP BY Fage
GROUP BY子句必须放到WHERE语句的之后
没有出现在GROUP BY子句中的列是不能放到SELECT语句后的列名列表中的 (聚合函数中除外)
错误:SELECT FAge,FSalary FROM T_Employee GROUPBY FAge
正确:SELECT FAge,AVG(FSalary) FROM T_EmployeeGROUP BY FAge
12、Having语句
在Where中不能使用聚合函数,必须使用Having,Having要位于GroupBy之后,
SELECTFAge,COUNT(*) AS 人数 FROM T_Employee
GROUP BY Fage
HAVINGCOUNT(*)>1
注意Having中不能使用未参与分组的列,Having不能替代where。作用不一样,Having是对组进行过滤。
SelectFAge,count(*) from T_Employee
Wherecount(*)>1
Group by FAge //错误,聚合函数不应出现在where子句中
Select FAge,count(*)from T_Employee
Group by FAge
Havingcount(*)>1
SelectFAge,count(*) from T_Employee
WhereFsalary>2000
Group by FAge
SelectFAge,count(*) from T_Employee
Group by FAge
HavingFalary>2000 //having 是对分组后的信息的过滤,能用的列和select中能用的列是一样的
注意:书写风格:换行!!
13、限制结果集行数
select top 5 *from T_Employee order by FSalary Desc
(*)检索按照工资从高到低排序检索从第六名开始一共三个人的信息 :
SELECT top 3 * FROMT_Employee
WHERE FNumberNOT IN
(SELECT TOP 5FNumber FROM T_Employee ORDER BY FSalary DESC)
ORDER BY FSalaryDESC
嵌套查询!将操作得到的列表作为新列表
SQLServer2005后增加了Row_Number函数简化实现。
14、去掉数据重复
SELECT DISTINCT 列名称 FROM 表名称
执行备注中的SQL语句SELECTFDepartment FROM T_Employee
→SELECT DISTINCT FDepartment FROM T_Employee
DISTINCT是对整个结果集进行数据重复处理的,而不是针对每一个列,因此下面的语句并不会只保留Fdepartment进行重复值处理:
SELECT DISTINCT FDepartment,FSubCompanyFROM T_Employee
只有完全重复的才除去不是完全重复不去掉
15、联合结果集
简单的结果集联合:
SELECTFNumber,FName,FAge FROM T_Employee
UNION
SELECTFIdCardNumber,FName,FAge FROM T_TempEmployee
SELECTFNumber,FName,FAge,FDepartmentFROM T_Employee
UNION
SELECT FIdCardNumber,FName,FAge,‘临时工,无部门' FROM T_TempEmployee
基本的原则:每个结果集必须有相同的列数;每个结果集的列必须类型相容。
上下列数目和类型都相同才能构成连接union
没有要补足,凑格式
16、Union all
SELECT FName FROMT_Employee
UNION
SELECT FName FROMT_TempEmployee。
UNION合并两个查询结果集,并且将其中完全重复的数据行合并为一条
SELECT FName FROMT_Employee
UNION ALL
SELECT FName FROMT_TempEmployee
Union会把相同的数据重复掉
Union all 则保持所有数据
Union因为要进行重复值扫描,所以效率低,因此如果不是确定要合并重复行,那么就用UNIONALL
17、例子:
案例1
要求查询员工的最低年龄和最高年龄,临时工和正式员工
要分别查询
SELECT '正式员工最高年龄',MAX(FAge) FROM T_Employee
UNION ALL
SELECT '正式员工最低年龄',MIN(FAge) FROM T_Employee
UNION ALL
SELECT '临时工最高年龄',MAX(FAge) FROM T_TempEmployee
UNION ALL
SELECT '临时工最低年龄',MIN(FAge) FROM T_TempEmployee
案例2
查询每位正式员工的信息,包括工号、工资,并且在最后一行加上所有员工工资额合计。
SELECTFNumber,FSalary FROM T_Employee
UNION ALL
SELECT '工资合计',SUM(FSalary) FROM T_Employee
凑格式
18、数字函数(*)
ABS() :求绝对值。
CEILING():舍入到最大整数 。3.33将被舍入为4、2.89将被舍入为3、-3.61将被舍入为-3。Ceiling→天花板
FLOOR():舍入到最小整数。3.33将被舍入为3、2.89将被舍入为2、-3.61将被舍入为-4。Floor→地板。
ROUND():四舍五入。舍入到“离我半径最近的数”。
Round→“半径”。Round(3.1425,2)。
Ceiling floorround还可以有精度
19、字符串函数(*)
LEN() :计算字符串长度
LOWER() 、UPPER () :转小写、大写
LTRIM():字符串左侧的空格去掉
RTRIM () :字符串右侧的空格去掉
LTRIM(RTRIM(' bb '))
SUBSTRING(string,start_position,length)
参数string为主字符串,start_position为子字符串在主字符串中的起始位置,length为子字符串的最大长度。
SELECT SUBSTRING('abcdef111',2,3)
c#中的函数SUBSTRING在数据库中依旧可以使用
select len(‘abd’)
select Fname len(Fname)from T_Employee
20、日期函数
GETDATE() :取得当前日期时间
DATEADD (datepart, number, date ),计算增加以后的日期。参数date为待计算的日期;参数number为增量;参数datepart为计量单位,可选值见备注。DATEADD(DAY,3,date) 为计算日期date的3天后的日期,而
DATEADD(MONTH ,-8,date)为计算日期date的8个月之前的日期
DATEDIFF (datepart , startdate , enddate ) :计算两个日期之间的差额。
参数datepart 为计量单位,可取值参考DateAdd。
DATEPART (datepart,date):返回一个日期的特定部分
统计不同工龄的员工的个数:
Select DateDiff(year,FInDate,getdate()),count(*) from T_Employee
group by DateDiff(year,FInDate,getdate())
统计员工的入职年份个数:
Select DatePart(year,FInDate),count(*)from T_Employee
group byDatePart(year,FInDate)
备注:
datepart为计量单位,可选值如下:
日期部分 缩写 代表意思
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 毫秒
21、类型转换函数
CAST()函数语法如下:
CAST (<expression> AS <data_ type>[ length ])
CONVERT()函数语法如下:
CONVERT (<data_ type>[ length ], <expression> [, style[编号]])
SELECT FIdNumber,
RIGHT(FIdNumber,3) as 后三位,
RIGHT(FIdNumber,3)+1 as 后三位加1,
CAST(RIGHT(FIdNumber,3) AS char(3)) as 后三位的字符形式,
CAST(RIGHT(FIdNumber,3) AS char(3))+1 as 后三位的字符形式加1,
CAST(RIGHT(FIdNumber,3) AS char(3))+'1' as 后三位的字符形式加1,
CAST(RIGHT(FIdNumber,3) AS INTEGER) as 后三位的整数形式,
CAST(RIGHT(FIdNumber,3) AS INTEGER)+1 as 后三位加1
FROM T_Person
结果:
Select cast (‘123’as int ),
cast(‘2008-08-08’as datetime),
Convert (datetime,’2009-09-09’),
convert(varchar(50),123)
Selectdatepart(year,cast(‘2008-08-08’as datetime))
22、值处理函数
ISNULL(expression,value):
如果expression不为空则返回expression,否则返回value。
SELECTISNULL(FName,'佚名') as 姓名 FROMT_Employee
流控函数:Case when和switchcase差不多一样
23、CASE函数用法
单值判断,相当于switch case
CASE expression
WHEN value1 THENreturnvalue1
WHEN value2 THENreturnvalue2
WHEN value3 THENreturnvalue3
ELSEdefaultreturnvalue
END
例子
SELECT FName,
(
CASE FLevel
WHEN 1 THEN 'VIP客户'
WHEN 2 THEN '高级客户'
WHEN 3 THEN '普通客户'
ELSE '客户类型错误'
END
) as 客户类型
FROM T_Customer
24、练习:
练习1
表中有A B C三列,用SQL语句实现:当A列大于B列时选择A列否则选择B列,当B列大于C列时选择B列否则选择C列。
解答:
select
(
Case
when a>b then aelse b
end
),
(
case
when b>c then belse c
end ) from table
练习2
单号 金额
Rk1 10
Rk2 20
Rk3 -30
Rk4 -10
将上面的表输出为如下的格式:
单号 收入 支出
Rk1 10 0
Rk2 20 0
Rk3 0 30
Rk4 0 10
解答:
CREAT TABLE T_入库单(单号 varCHAR(20),金额 INT)
INSERT INTO T_入库单(单号,金额 )VALUES(‘Rk1’,10)
INSERT INTO T_入库单(单号,金额 )VALUES(‘Rk2’,20)
INSERT INTO T_入库单(单号,金额 )VALUES(‘Rk3’,-30)
INSERT INTO T_入库单(单号,金额 )VALUES(‘Rk4’,-10)
Select 单号,
(
Case
When 金额>0 then 金额else 0
End
) as收入,
(
Case
When 金额<0 then abs(金额)
else 0
End
)as 支出
From T_入库单
练习3
有一张表T_Scores,记录比赛成绩
Date Name Score
2008-8-8 拜仁 胜
2008-8-9 奇才 胜
2008-8-9 湖人 胜
2008-8-10 拜仁 负
2008-8-8 拜仁 负
2008-8-12 奇才 胜
要求输出下面的格式:
Name 胜负
拜仁 1 2
湖人 1 0
奇才 2 0
注意:在中文字符串前加N,比如N’胜’
解答:
CREAT TABLE T_Scores
(Date datetime,Name nvarchar(50), Score nvarchar(50));
不是当前日期?时间设置用?直接输入‘2008-8-8’日期即可
INSERT INTO T_ Scores(Date,Name,Score) VALUES(‘2008-8-8’,N‘拜仁’,N’胜’)
INSERT INTO T_ Scores(Date,Name,Score) VALUES(‘2008-8-9’ ,N‘奇才’,N’胜’)
INSERT INTO T_ Scores(Date,Name,Score) VALUES(‘2008-8-9’ ,N‘湖人’,N’胜’)
INSERT INTO T_ Scores(Date,Name,Score) VALUES(‘2008-8-10’ ,N‘拜仁’,N’负’)
INSERT INTO T_ Scores(Date,Name,Score) VALUES(‘2008-8-8’ ,N‘拜仁’,N’负’)
INSERT INTO T_ Scores(Date,Name,Score) VALUES(‘2008-8-12’ ,N‘奇才’,N’胜’)
Select name ,
Sum (
Case score
When N’胜’ then 1
Else 0
End
)as 胜,
Sum (
Case score
When N’负’ then 1
Else 0
End
)as 负
From T_Scores
group by Name
练习4
创建一张表,记录电话呼叫员的工作流水,记录呼叫员编号、对方号码、通话开
始时间、通话结束时间。建表、插数据等最后都自己写SQL语句。
要求:
1、输出所有数据中通话时间最长的5条记录。orderbydatediff
2、输出所有数据中拨打长途号码(对方号码以0开头)的总时长。like、sum
3、输出本月通话总时长最多的前三个呼叫员的编号。
datediff(month....),sum,order by
4、输出本月拨打电话次数最多的前三个呼叫员的编号.group by,count(*)
5、输出所有数据的拨号流水,并且在最后一行添加总呼叫时长
呼叫员编号、对方号码、通话时长
.......
汇总 [市内号码总时长][长途号码总时长]
解答:
创建表:
create tableT_CallRecords(
Id int not nullidentity(1,1),
CallerNumber varchar(3),
TelNum varchar(13),
StartDateTime datetime,
EndDateTime datetime,
Primary key(Id));
插入数据:
insert intoT_CallRecords(CallerNumber,TelNum,StartDateTime,EndDateTIme)
values('001','02088888888','2011-12-10 10:01','2011-12-10 10:05');
insert into T_CallRecords(CallerNumber,TelNum,StartDateTime,EndDateTime)
values ('001','02088888888', '2011-12-11 13:41','2011-12-11 13:52');
insert into T_CallRecords(CallerNumber,TelNum,StartDateTime,EndDateTime)
values ('001','89898989', '2011-12-11 14:42', '2011-12-11 14:49');
insert into T_CallRecords(CallerNumber,TelNum,StartDateTime,EndDateTime)
values ('002','02188368981', '2011-12-13 21:04', '2011-12-13 21:18');
insert into T_CallRecords(CallerNumber,TelNum,StartDateTime,EndDateTime)
values ('002','76767676', '2011-11-29 20:15', '2011-11-29 20:30');
insert into T_CallRecords(CallerNumber,TelNum,StartDateTime,EndDateTime)
values ('001','02288878243', '2011-12-15 13:40', '2011-12-15 13:56');
insert into T_CallRecords(CallerNumber,TelNum,StartDateTime,EndDateTime)
values ('003','67254686', '2011-12-13 11:06', '2011-12-13 11:19');
insert into T_CallRecords(CallerNumber,TelNum,StartDateTime,EndDateTime)
values ('003','86231445', '2011-11-19 19:19', '2011-11-19 19:25');
insert into T_CallRecords(CallerNumber,TelNum,StartDateTime,EndDateTime)
values ('001','87422368', '2011-11-19 19:25', '2011-11-19 19:36');
insert into T_CallRecords(CallerNumber,TelNum,StartDateTime,EndDateTime)
values ('004','40045862245', '2011-11-19 19:50', '2011-11-19 19:59');
查看数据:
select * fromT_CallRecords
1) 输出所有数据中通话时间最长的5条记录:
select top 5* ,datediff(second,StartDateTime,EndDateTime) as [通话时长/秒]
from T_CallRecords
order by datediff(second,StartDateTime,EndDateTime) desc
2) 输出所有数据中拨打长途号码(对方号码以0开头)的总时长:
selectsum(datediff(second,StartDateTime,EndDateTime)) as 通话总时长
from T_CallRecords
where TelNum like '0%'
3) 输出本月通话总时长最多的前三个呼叫员的编号:
select top 3CallerNumber from T_CallRecords
where datediff(month,StartDateTime,getdate())=0
group by CallerNumber
order by sum(datediff(second,StartDateTime,EndDateTime)) desc
4) 输出本月拨打电话次数最多的前三个呼叫员的编号:
select top 3CallerNumber,count(*)
from T_CallRecords
where datediff(month,StartDateTime,getdate())=0
group by CallerNumber
order by count(*) desc
5) 输出所有数据的拨号流水,并且在最后一行添加总呼叫时长:
selectCallerNumber as 呼叫员编号,TelNum as 对方号码,datediff(second,StartDateTime,EndDateTime) as 通话时长
from T_CallRecords
UNION ALL
select '','市内通话总时长',
sum((
case
when TelNum not like '0%' then datediff(second,StartDateTime,EndDateTime)
else 0
end
))
from T_CallRecords
UNION ALL
select '','长途通话总时长',
sum((
case
when TelNum like '0%' then datediff(second,StartDateTime,EndDateTime)
else 0
end
))
from T_CallRecords
UNION ALL
select '','汇总',
sum(datediff(second,StartDateTime,EndDateTime))
from T_CallRecords
25、索引Index
全表扫描:对数据进行检索(select)效率最差的是全表扫描,就是一条条的找。
如果没有目录,查汉语字典就要一页页的翻,而有了目录只要查询目录即可。为了提高检索的速度,可以为经常进行检索的列添加索引,相当于创建目录。
创建索引的方式,在表设计器中点击右键,选择“索引/键”→添加→在列中选择索引包含的列。
使用索引能提高查询效率,但是索引也是占据空间的,而且添加、更新、删除数据的时候也需要同步更新索引,因此会降低Insert、Update、Delete的速度。
只在经常检索的字段上(Where)创建索引。
(*)即使创建了索引,仍然有可能全表扫描,比如like、函数、类型转换等。
26、表连接Join(表间关联)
有客户表(T_Customers)和订单表(T_Orders)两个表,客户表字段为:Id、Name、Age,订单表字段为:Id、BillNo、CustomerId,订单表通过CustomerId关联客户表。
CREAT TABLET_Customer (id int,name nvarchar(50),age int);
Insert intoT_Customer (id,name,age)values(1,N’tom’,10)
Insert intoT_Customer (id,name,age)values(2,N’jerry’,15)
Insert intoT_Customer (id,name,age)values(3,N’john’,22)
Insert intoT_Customer (id,name,age)values(4,N’lily’,18)
Insert intoT_Customer (id,name,age)values(5,N’lucy’,18)
CREAT TABLET_order (id int,billno nvarchar(50),customerid int);
Insert into T_ order(id,name, customerid)values(1,N’001’,1)
Insert into T_ order(id,name, customerid)values(2,N’002’,1)
Insert into T_ order(id,name, customerid)values(3,N’003’,3)
Insert into T_ order(id,name, customerid)values(4,N’004’,2)
Insert into T_ order(id,name, customerid)values(5,N’005’,2)
Insert into T_ order(id,name, customerid)values(6,N’006’,5)
Insert into T_ order(id,name, customerid)values(7,N’007’,4)
Insert into T_ order(id,name, customerid)values(8,N’008’,5)
select o.BillNo,c.Name,c.Age
from T_Orders as o join T_Customers as c ono.CustomerId=c.Id
显示CustomerId=Id的T_Orders 的billno和T_Customers的name和age内容
as 别名(一般都起个别名)
注意:CustomerId在这里是名义上的外键,现在只是将他的值想像为外键关联关系,实际上没有建立外键关联指向T_Customers
现在开发主流的都不建外键了
join是和哪个表连接,on后是连接的关系是什么。
要求显示所有年龄大于15岁的顾客购买的订单号、客户姓名、客户年龄。
elect o.BillNo,c.Name,c.Age
from T_Orders as o join T_Customers as c ono.CustomerId=c.Id
where c.age>15
要求显示年龄大于平均年龄的顾客购买的订单
elect o.BillNo,c.Name,c.Age
from T_Orders as o join T_Customers as c ono.CustomerId=c.Id
wherec.age>(select avg(age) from T_Customers)
(*)Inner Join、Left Join、Right Join
27、子查询
将一个查询语句做为一个结果集供其他SQL语句使用,就像使用普通的表一样,被当作结果集的查询语句被称为子查询。所有可以使用表的地方几乎都可以使用子查询来代替。
SELECT *FROM T_ReaderFavorite WHERE FCategoryId=(SELECT FId FROM T_Category WHEREFName='Story')
SELECT *FROM (SELECT * FROM T2 where FAge<30)
单值做为子查询:SELECT 1 AS f1,2,(SELECT MIN(FYearPublished) FROMT_Book),(SELECT MAX(FYearPublished) FROM T_Book) AS f4
只有返回且仅返回一行、一列数据的子查询才能当成单值子查询。
下面的是错误的:
SELECT 1 ASf1,2,(SELECT FYearPublished FROMT_Book)
SELECT * FROMT_ReaderFavorite WHERE FCategoryId=(SELECT FId FROM T_Category WHEREFName='Story')
子查询
如果子查询是单列多行的子查询,这样的子查询的结果集其实是一个集合。
SELECT * FROMT_Reader
WHERE FYearOfJoinIN
(
selectFYearPublished FROM T_Book
)
SELECTROW_NUMBER() OVER(ORDER BY FSalary DESC) AS rownum,
FNumber,FName,FSalary,FAgeFROM T_Employee
Where ROW_NUMBER()OVER(ORDER BY FSalary DESC) >=3 and
ROW_NUMBER()OVER(ORDER BY FSalary DESC)<=5
//错误!
开窗函数只能在select和orderby子句中
限制结果集。返回第3行到第5行的数据(ROW_NUMBER 不能用在where子句中,所以将带行号的执行结果作为子查询,就可以将结果当成表一样用了):
SELECT * FROM
(
SELECTROW_NUMBER() OVER(ORDER BY FSalary DESC) AS rownum,
FNumber,FName,FSalary,FAgeFROM T_Employee
) AS a //当成新表
WHEREa.rownum>=3 AND a.rownum<=5
详情请查看: