关闭

黑马程序员-SQL入门

标签: insertsqlserverdate数据库nullsql
564人阅读 评论(0) 收藏 举报
分类:


                     ----------------------   Windows Phone 7手机开发.Net培训    期待与您交流! ----------------------

 


数据库概述:

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



                     ----------------------   Windows Phone 7手机开发.Net培训    期待与您交流! ----------------------

 

                                     详情请查看:http://edu.csdn.net/heima



0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:14488次
    • 积分:341
    • 等级:
    • 排名:千里之外
    • 原创:17篇
    • 转载:11篇
    • 译文:1篇
    • 评论:0条
    文章存档