SQL数据库基础知识

上传人：翰*** IP属地：广西上传时间：2023-11-14 格式：DOC 页数：37 大小：174.50KB 积分：12 举报 版权申诉

已阅读5页，还剩32页未读，继续免费阅读

版权说明：本文档由用户提供并上传，收益归属内容提供方，若内容存在侵权，请进行举报或认领

文档简介

一、SQL

基础知识1、DDL（数据定义语言）1）创建数据表--创建数据表create

table

Test(Id

int

not

null,

Age

char(20))；

--创建数据表create

table

T_Person1(Id

int

not

null,Namenvarchar(50),Age

int

null)；

--创建表，添加外键Create

table

T_Students(StudentNo

char(4),CourseNo

char(4),Score

int,Primary

key(StudentNo),Foreign

key(CourseNo)

References

T_Course(CourseNo));

2)修改表结构--修改表结构，添加字段Alter

table

T_Person

add

NickNamenvarchar(50)

null;

--修改表结构，删除字段Alter

table

T_Person

Drop

NickName;

3)删除数据表--删除数据表Drop

table

T_Person;

--删除数据表drop

table

test4)创建索引Create

[Unique]

Index

<索引名>

<基本表名>(<列明序列>);

2、DML（数据操纵语言）1)插入语句insert

into

T_Person1(Id,Name,Age)

values(1,'Vicky',20)--插入一条据数，字段和值必须前后对应insert

into

T_Preson1(Id,Name,Age)

values(2,'Tom',19)insert

into

T_Person1(Id,Name,Age)

values(4,'Jim',19)insert

into

T_Person1(Id,Name,Age)

values(5,'Green',20)insert

into

T_Person1(Id,Name,Age)

values(6,'Hanmeimei',21)insert

into

T_Person1(Id,Name,Age)

values(7,'Lilei',22)insert

into

T_Person1(Id,Name,Age)

values(8,'Sky',23)

insert

into

T_Person1(Id,Name,Age)

values(newid(),'Tom',19)

2)更新语句--修改列，把所有的age字段改为30update

T_Person1

set

age=30

--把所有的Age字段和Name字段设置为...update

T_Person1

set

Age=50,Name='Lucy'

update

T_Person1

set

Name='Frankie'

where

Age=30

update

T_Person1

set

Name=N'中文字符'

where

Age=20--中文字符前面最好加上N，以防出现乱码

update

T_Person1

set

Name=N'成年人'

where

Age=30

Age=50

3)删除语句delete

from

T_Person1--删除表中全部数据

delete

from

T_Person1

where

Name='Tom'--根据条件删除数据

4)查询语句查询语句非常强大，几乎可以查任意东西！---------------------

数据检索

------------------------查询不与任何表关联的数据.SELECT

1+1;

--简单运算

select

1+2

结果

SELECT

newid();--查询一个GUID字符创

select

GETDATE()

日期

--查询日期

--可以查询SQLServer版本select

@@VERSION

SQLServer版本

--一次查询多个select

1+1

结果,

GETDATE()

日期,

@@VERSION

版本,

NEWID()

编号

--简单的数据查询.HelloWorld级别SELECT

FROM

T_Employee;

--只查询需要的列.SELECT

FNumber

FROM

T_Employee;

--给列取别名.As关键字SELECT

FNumber

编号,

FName

姓名

FROM

T_Employee;

--使用

WHERE

查询符合条件的记录.SELECT

FName

FROM

T_Employee

WHERE

FSalary<5000;

--对表记录进行排序，默认排序规则是ASCSELECT

FROM

T_Employee

ORDER

FAge

ASC,FSalary

DESC;

--ORDERBY

子句要放在

WHERE

子句之后.SELECT

FROM

T_Employee

WHERE

FAge>23

ORDER

FAge

DESC,FSalary

DESC;

--WHERE

中可以使用的逻辑运算符：or、and、not、<、>、=、>=、<=、!=、<>等.

--模糊匹配，首字母未知.SELECT

FROM

T_Employee

WHERE

FName

'_arry';

--模糊匹配，前后多个字符未知.SELECT

FROM

T_Employee

WHERE

FName

'%n%';

--NULL

表示"不知道"，有

NULL

参与的运算结果一般都为

NULL.

--查询数据是否为

NULL，不能用

、!=

或

<>，要用IS关键字SELECT

FROM

T_Employee

WHERE

FName

NULL;SELECT

FROM

T_Employee

WHERE

FName

NOT

NULL;

--查询在某个范围内的数据，IN

表示包含于，IN后面是一个集合SELECT

FROM

T_Employee

WHERE

FAge

(23,

25,

28);

--下面两条查询语句等价。SELECT

FROM

T_Employee

WHERE

FAge>=23

AND

FAge<=30;SELECT

FROM

T_Employee

WHERE

FAge

BETWEEN

AND

30;

----创建一张Employee表，以下几个Demo中会用的这张表中的数据----在SQL管理器中执行下面的SQL语句，在T_Employee表中进行练习create

table

T_Employee(FNumber

varchar(20),FName

varchar(20),FAge

int,FSalary

Numeric(10,2),primary

key

(FNumber))

insert

into

T_Employee(FNumber,FName,FAge,FSalary)

values('DEV001','Tom',25,8300)insert

into

T_Employee(FNumber,FName,FAge,FSalary)

values('DEV002','Jerry',28,2300.83)insert

into

T_Employee(FNumber,FName,FAge,FSalary)

values('SALES001','Lucy',25,5000)insert

into

T_Employee(FNumber,FName,FAge,FSalary)

values('SALES002','Lily',25,6200)insert

into

T_Employee(FNumber,FName,FAge,FSalary)

values('SALES003','Vicky',25,1200)insert

into

T_Employee(FNumber,FName,FAge,FSalary)

values('HR001','James',23,2200.88)insert

into

T_Employee(FNumber,FName,FAge,FSalary)

values('HR002','Tom',25,5100.36)insert

into

T_Employee(FNumber,FName,FAge,FSalary)

values('IT001','Tom',28,3900)insert

into

T_Employee(FNumber,FAge,FSalary)

values('IT002',25,3800)

--开始对T_Employee表进行各种操作--检索所有字段select

from

T_Employee

--只检索特定字段select

FName,FAge

from

T_Employee

--带过滤条件的检索select

from

T_Employeewhere

FSalary<5000

--可更改显示列名的关键字as，as—起别名select

FName

姓名,FAge

年龄,FSalary

薪水

from

T_Employee

二、SQL

Server

中的数据类型1、精确数字类型bigintintsmallinttinyintbitmoneysmallmoney2、字符型数据类型，MS建议用VarChar(max)代替TextCharVarCharText3、近似数字类型DecimalNumericRealFloat4、Unicode字符串类型NcharNvarCharNtext5、二进制数据类型,MS建议VarBinary(Max)代替Image数据类型，max=231-1Binary(n)

存储固定长度的二进制数据VarBinary(n)

存储可变长度的二进制数据，范围在n~(1,8000)Image

存储图像信息6、日期和时间类型,数据范围不同，精确地不同DateTimeSmallDateTime7、特殊用途数据类型CursorSql-variantTableTimeStampUniqueIdentifierXML

三、SQL中的内置函数

-------------------------------------------

数据汇总-聚合函数

-------------------------------------------------查询T_Employee表中数据条数select

COUNT(*)

from

T_Employee

--查询工资最高的人select

MAX(FSalary)

Top1

from

T_Employee

--查询工资最低的人select

Min(FSalary)

Bottom1

from

T_Employee

--查询工资的平均水平select

Avg(FSalary)

平均水平

from

T_Employee

--所有工资的和select

SUM(FSalary)

总工资

from

T_Employee

--查询工资大于5K的员工总数select

COUNT(*)

total

from

T_Employeewhere

FSalary>5000

-----------------------------------

数据排序

---------------------------------------按年龄排序升序,默认是升序select

from

T_Employeeorder

FAge

ASC

--多个条件排序,先什么,后什么,在前一个条件相同的情况下,根据后一个条件进行排列--where在orderby之前select

from

T_Employeeorder

FAge

ASC,

FSalary

DESC

-----------------------------------

模糊匹配

---------------------------------------通配符查询--1.单字符通配符_--2.多字符通配符%--以DEV开头的任意个字符串select

from

T_Employeewhere

FNumber

'DEV%'

--以一个字符开头,om结尾的字符串select

from

T_Employeewhere

FName

'_om'

--检索姓名中包含m的字符select

from

T_Employeewhere

FName

'%m%'

-----------------------------------

空值处理

---------------------------------------null表示不知道,不是没有值--null和其他值计算结果是nullselect

null+1

--查询名字是null的数据select

from

T_Employeewhere

FName

null

--查询名字不为空null的数据select

from

T_Employeewhere

FName

not

null

--年龄是23,25,28中的员工select

from

T_Employeewhere

FAge=23

FAge=25

FAge=28

--或者用in

集合查询--年龄是23,25,28中的员工select

from

T_Employeewhere

FAge

(23,25,28)

--年龄在20到25之间的员工信息select

from

T_Employeewhere

FAge>20

and

FAge<25

--年龄在20到25之间的员工信息,包含25select

from

T_Employeewhere

FAge

between

and

-----------------------------------

数据分组

-------------------------------------Select

FAge,COUNT(*)

from

T_Employeegroup

FAge--1.根据年龄进行分组--2.再取出分组后的年龄的个数

--注意:没有出现在groupby

子句中的字段,不能出现在select语句后的列名列表中(聚合函数除外)--groupby

必须出现在where后面Select

FAge,AVG(FSalary),COUNT(*)

from

T_Employeegroup

FAge

--错误用法Select

FAge,FName,COUNT(*)

from

T_Employeegroup

FAge

--加上where的groupby

子句--groupby

必须出现在where后面Select

FAge,AVG(FSalary),COUNT(*)

from

T_Employeewhere

FAge>=25group

FAge

--Having不能包含查不到的字段,只能包含聚合函数和本次查询有关的字段select

FAge,COUNT(*)

from

T_Employeegroup

FAgeHaving

COUNT(*)>1

select

FAge,COUNT(*)

from

T_Employeewhere

FSalary>2500group

FAge

--HAVING

子句中的列

'T_Employee.FSalary'

无效，因为该列没有包含在聚合函数或

GROUPBY

子句中--Having是对分组后信息的过滤，能用的列和select中能有的列是一样的。--因此，having不能代替whereselect

FAge,COUNT(*)

from

T_Employeegroup

FAgeHaving

FSalary>2500

-----------------------------------

确定结果集行数

---------------------------------------取出所有员工的信息,根据工资降序排列select

from

T_Employeeorder

FSalary

DESC

--取出前三名员工的信息,根据工资降序排列select

top

from

T_Employeeorder

FSalary

DESC

--根据工资取出排名在6-8的员工信息,按工资降排列select

top

from

T_Employeewhere

FNumber

not

in(select

top

FNumber

from

T_Employee

order

FSalary

DESC)order

FSalary

DESC

---修改数据表，添加字段，更新字段的值等操作。alter

table

T_Employee

add

FSubCompany

varchar(20)alter

table

T_Employee

add

FDepartment

varchar(20)update

T_Employee

set

FSubCompany='Beijing',FDepartment='Development'where

FNumber='DEV001';update

T_Employee

set

FSubCompany='ShenZhen',FDepartment='Development'where

FNumber='DEV002';update

T_Employee

set

FSubCompany='Beijing',FDepartment='HumanResource'where

FNumber='HR001';update

T_Employee

set

FSubCompany='Beijing',FDepartment='HumanResource'where

FNumber='HR002';update

T_Employee

set

FSubCompany='Beijing',FDepartment='InfoTech'where

FNumber='IT001';update

T_Employee

set

FSubCompany='ShenZhen',FDepartment='InfoTech'where

FNumber='IT002'update

T_Employee

set

FSubCompany='Beijing',FDepartment='Sales'where

FNumber='SALES001';update

T_Employee

set

FSubCompany='Beijing',FDepartment='Sales'where

FNumber='SALES002';update

T_Employee

set

FSubCompany='ShenZhen',FDepartment='Sales'where

FNumber='SALES003';

select

from

T_Employee

------------------------------------

去掉重复数据

--------------------------------------所有员工的部门信息select

Distinct

FDepartment

from

T_Employee;

select

FDepartment,FSubCompanyfrom

T_Employee

--以上两个例子结合起来比较，Distinct针对的是整行进行比较的select

Distinct

FDepartment,FSubCompanyfrom

T_Employee

-----------------------------------

联合结果集Union----------------------------------------创建一个测试表T_TempEmployee，并插入数据Create

Table

T_TempEmployee(FIdCardNumber

varchar(20),FName

varchar(20),FAge

int,Primary

key(FIdCardNumber));insert

into

T_TempEmployee(FIdCardNumber,FName,FAge)

values('1234567890121','Sarani',33);insert

into

T_TempEmployee(FIdCardNumber,FName,FAge)

values('1234567890122','Tom',26);insert

into

T_TempEmployee(FIdCardNumber,FName,FAge)

values('1234567890123','Yamaha',38);insert

into

T_TempEmployee(FIdCardNumber,FName,FAge)

values('1234567890124','Tina',36);insert

into

T_TempEmployee(FIdCardNumber,FName,FAge)

values('1234567890125','Konkaya',29);insert

into

T_TempEmployee(FIdCardNumber,FName,FAge)

values('1234567890126','Foortia',29);

select

from

T_TempEmployee

--Union关键字，联合2个结果--把2个查询结果结合为1个查询结果--要求:上下2个查询语句的字段(个数,名字,类型相容)必须一致select

FName,Fage

from

T_TempEmployeeunionselect

FName,Fage

from

T_Employee

select

FNumber,

FName,Fage,FDepartment

from

T_Employeeunionselect

FIdCardNumber,FName,Fage,'临时工，无部门'

from

T_TempEmployee

---UnionAll:不合并重复数据--Union：合并重复数据select

FName,FAge

from

T_Employeeunion

allselect

FName,FAge

from

T_TempEmployee

select

FAge

from

T_Employeeunionselect

FAge

from

T_TempEmployee

--注意:Union因为要进行重复值扫描,所以效率低,因此如果不是确定要合并重复,那么就用Unionall

--例子:报名select

'正式员工最高年龄',MAX(FAge)

from

T_Employeeunion

allselect

'正式员工最低年龄',MIN(FAge)

from

T_Employeeunion

allselect

'临时工最高年龄',MAX(FAge)

from

T_TempEmployeeunion

allselect

'临时工最低年龄',MIN(FAge)

from

T_TempEmployee

--查询每位正式员工的信息,包括工号,工资,并且在最后一行加上员工工资额合计select

FNumber,FSalary

from

T_Employeeunion

allselect

'工资额合计',SUM(FSalary)

from

T_Employee

-----------------------------------SQL其他内置函数

------------------------------------

--1.数学函数

--ABS():求绝对值--CEILING():舍入到最大整数--FLOOR():舍入到最小整数--ROUND():四舍五入

select

ABS(-3)

select

CEILING(3.33)

select

CEILING(-3.61)

select

FLOOR(2.98)

select

FLOOR(-3.61)

select

ROUND(-3.61,1)--第二个参数是精度，小数点后的位数

select

ROUND(-3.61,0)

select

ROUND(3.1415926,3)

--2.字符串函数--LEN():计算字符串长度--LOWER(),UPPER():转大小写--LTRIM():去掉字符串左侧的空格--RTRIM():去掉字符串右侧的空格--SUBSTRING(string,start_positoin,length):

--索引从1开始

select

SUBSTRING('abc111',2,3)--结果是bc1

select

FName,

SUBSTRING(FName,2,2)

from

T_Employee

select

LEN('abc')

--结果是3

select

FName,

LEN(FName)

from

T_Employee

--没有可以同时既去掉左边空格、又去掉右边空格的TRIM()内置函数，所以先左后右的进行TRim，当然，你也可以先右后左select

LTRIM('abc'),RTRIM('abc'),LEN(LTRIM(RTRIM('abc')))

--3.日期函数--GETDATE():获取当前日期时间

--DATEADD(datepart,numbre,date):计算增加以后的日期，--参数date为待计算的日期；参数number为增量；参数datepart为计量单位,时间间隔单位；

--DATEDIFF(datepart,startdate,enddate):计算2个日期之间的差额

--DATEPART(datepart,date):返回一个日期的特定部分,比如年月日,时分秒等.

/*值

缩

写（SqlServer）

(Access

和

ASP)

说明YearYyyyyy

年

1753~9999QuarterQqq

季

1~4MonthMmm

月

1~12DayofyearDyy

一年的日数,一年中的第几日

1-366DayDdd

日，

1-31WeekdayDww

一周的日数，一周中的第几日

1-7WeekWkww

周，一年中的第几周

0~51HourHhh

时0~23MinuteMin

分钟0~59SecondSss

秒

0~59MillisecondMs-

毫秒

0~999*/

select

DATEADD(DAY,3,getdate())

select

DATEADD(MONTH,-3,getdate())

select

DATEADD(HOUR,8,getdate())

select

DATEDIFF(YEAR,'1989-05-01',GETDATE())

select

DATEDIFF(HH,GETDATE(),DATEADD(DAY,-3,GETDATE()))

--查询员工的工龄,年为单位

select

FName,FInDate,DATEDIFF(year,FInDate,getdate())

工龄

from

T_Employee

--取出每一年入职员工的个数V1select

DATEDIFF(year,FInDate,getdate()),COUNT(*)from

T_Employeegroup

DATEDIFF(year,FInDate,getdate())

--取出每一年入职员工的个数V2select

DATEPART(YEAR,FInDate),

COUNT(*)from

T_Employeegroup

DATEPART(YEAR,FInDate)

select

DATEPART(YEAR,GETDATE())

select

DATEPART(MONTH,GETDATE())

select

DATEPART(DAY,GETDATE())

select

DATEPART(HH,GETDATE())

select

DATEPART(MINUTE,GETDATE())

select

DATEPART(SECOND,GETDATE())

--4.类型转换函数--CAST(expressionasdata_type)--CONVERT(data_type,expression)

select

CAST('123'

int),CAST('2010-09-08'

datetime),CONVERT(datetime,'2010-09-08'),CONVERT(varchar(20),123)

--5.空值处理函数isNull--ISNULL(expression,value)select

ISNULL(FName,'佚名')

姓名

from

T_Employee

--6.CASE函数用法：--1.单值判断：相当于switch.case--CASEexpression--WHENvalue1thenreturnvalue1--WHENvalue2thenreturnvalue2--WHENvalue3thenreturnvalue3--ELSEdefault_return_value--END

--判断客户类型

select

FName,(case

FLevelwhen

then

'普通客户'when

then

'会员'when

then

'VIP'else

'未知客户类型'End)

客户类型from

T_Customer

--收入水平查询select

FName,(casewhen

FSalary

2000

then

'低收入'when

FSalary

2000

and

FSalary

<=5000

then

'中等收入'else

'高收入'end)as

收入水平from

T_Employee

--这里有一道关于CASE用法的面试题--表T中有ABC三列，用SQL语句实现:当A列大于B列时选择A列，否则选择B列；--当B列大于C列时选择B列，否则选择C列。select(casewhen

then

else

bend),(casewhen

b>c

then

else

cend)from

---------------------------------------select

FNumber,(casewhen

FAmount>0

then

FAmountelse

0end)

收入,(casewhen

FAmount<0

then

ABS(FAmount)else

0end)

支出from

T-----------------------------------------

--球队比赛那个题--有一张表T_Scroes，记录比赛成绩：

--DateNameScroe--2008-8-8

拜仁

胜--2008-8-9

奇才

胜--2008-8-8

湖人

胜--2008-8-10

拜仁

负--2008-8-8

拜仁

负--2008-8-12

奇才

胜

--要求输出下面格式：--Name

胜

负--拜仁

12--湖人

10--奇才

--注意：在中文字符串前加

N，比如

N'胜'create

table

T_Scores([Date]

datetime

null

collate[Name]

nvarchar(50))CREATE

TABLE

[T_Scores](

[Date]

[datetime]

NULL,[Name]

[nvarchar](50)

COLLATE

Chinese_PRC_CI_AS

NULL,[Score]

[nvarchar](50)

COLLATE

Chinese_PRC_CI_AS

NULL);INSERT

[T_Scores]

([Date],

[Name],

[Score])

VALUES

(CAST(0x00009AF200000000

DateTime),

N'拜仁',

N'胜');INSERT

[T_Scores]

([Date],

[Name],

[Score])

VALUES

(CAST(0x00009AF300000000

DateTime),

N'奇才',

N'胜');INSERT

[T_Scores]

([Date],

[Name],

[Score])

VALUES

(CAST(0x00009AF300000000

DateTime),

N'湖人',

N'胜');INSERT

[T_Scores]

([Date],

[Name],

[Score])

VALUES

(CAST(0x00009AF400000000

DateTime),

N'拜仁',

N'负');INSERT

[T_Scores]

([Date],

[Name],

[Score])

VALUES

(CAST(0x00009AF200000000

DateTime),

N'拜仁',

N'负');INSERT

[T_Scores]

([Date],

[Name],

[Score])

VALUES

(CAST(0x00009AF600000000

DateTime),

N'奇才',

N'胜');

select

from

T_Scores

--列出第一个表格--统计每支队伍的胜负情况select

Name,(case

Scorewhen

N'胜'

then

1else

0end)

胜,(case

Scorewhen

N'负'

then

1else

0end)

负from

T_Scores

select

Name,sum(case

Scorewhen

N'胜'

then

1else

0end)

胜,sum(case

Scorewhen

N'负'

then

1else

0end)

负from

T_Scoresgroup

Name--根据每个队的胜负判断出胜负的场数

--题5)

创建一张表，记录电话呼叫员的工作流水，记录呼叫员编号，对方号码，通话开始时间，通话结束时间，。--创建一张表T_Callers，记录电话呼叫员的工作流水，记录呼叫员编号、对方号码、通话开始时间、通话结束时间。建表、插数据等最后都自己写SQL语句。--要求：--1)

输出所有数据中通话时间最长的5条记录。--2)

输出所有数据中拨打长途号码（对方号码以0开头）的总时长。--3)

输出本月通话总时长最多的前三个呼叫员的编号。--4)

输出本月拨打电话次数最多的前三个呼叫员的编号。--5)

输出所有数据的拨号流水，并且在最后一行添加总呼叫时长。--

记录呼叫员编号、对方号码、通话时长--......--

汇总[市内号码总时长][长途号码总时长]

--IdCallerNumberTellNumberStartDateTimeEndDateTime--1001020888888882010-7-1010:012010-7-1010:05--2001020888888882010-7-1113:412010-7-1113:52--3001898989892010-7-1114:422010-7-1114:49--4002021883689812010-7-1321:042010-7-1321:18--5002767676762010-6-2920:152010-6-2920:30--6001022888782432010-7-1513:402010-7-1513:56--7003672546862010-7-1311:062010-7-1311:19--8003862314452010-6-1919:192010-6-1919:25--9001874223682010-6-1919:252010-6-1919:36--10004400458622452010-6-1919:502010-6-1919:59

创建表create

table

T_CallRecords(id

int

not

null,CallerNumber

varchar(3),TellNumber

varchar(13),StartDateTImedatetime,EndDateTimedatetime,Primary

key(Id));

--插入数据insert

into

T_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTIme)values(1,'001','02088888888','2010-7-1010:01','2010-7-1010:05');INSERT

INTO

T_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)VALUES

(2,'002','02088888888',

'2010-7-1113:41','2010-7-1113:52');INSERT

INTO

T_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)VALUES

(3,'003','89898989',

'2010-7-1114:42',

'2010-7-1114:49');INSERT

INTO

T_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)VALUES

(4,'004','02188368981',

'2010-7-1321:04',

'2010-7-1321:18');INSERT

INTO

T_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)VALUES

(5,'005','76767676',

'2010-6-2920:15',

'2010-6-2920:30');INSERT

INTO

T_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)VALUES

(6,'006','02288878243',

'2010-7-1513:40',

'2010-7-1513:56');INSERT

INTO

T_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)VALUES

(7,'007','67254686',

'2010-7-1311:06',

'2010-7-1311:19');INSERT

INTO

T_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)VALUES

(8,'008','86231445',

'2010-6-1919:19',

'2010-6-1919:25');INSERT

INTO

T_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)VALUES

(9,'009','87422368',

'2010-6-1919:25',

'2010-6-1919:36');INSERT

INTO

T_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)VALUES

(10,'010','40045862245',

'2010-6-1919:50',

'2010-6-1919:59');

--修改呼叫员编号UPDATE

T_CallRecords

SET

CallerNumber='001'

WHERE

(1,2,3,6,9);UPDATE

T_CallRecords

SET

CallerNumber='002'

WHERE

(4,5);UPDATE

T_CallRecords

SET

CallerNumber='003'

WHERE

(7,8);UPDATE

T_CallRecords

SET

CallerNumber='004'

WHERE

Id=10;

--数据汇总select

from

T_CallRecords

--题

1)：

输出所有数据中通话时间最长的5条记录。--@计算通话时间；--@按通话时间降序排列；--@取前5条记录。select

top

CallerNumber,DATEDIFF(SECOND,StartDateTime,EndDateTime)

总时长from

T_CallRecordsorder

DATEDIFF(SECOND,StartDateTime,EndDateTime)

DESC

--题

2)：输出所有数据中拨打长途号码（对方号码以0开头）的总时长--@查询拨打长途号码的记录；--@计算各拨打长途号码的通话时长；--@对各拨打长途号码的通话时长进行求和。select

SUM(DATEDIFF(SECOND,StartDateTime,EndDateTime))

总时长

from

T_CallRecordswhere

TellNumber

'0%'

--题

3)：输出本月通话总时长最多的前三个呼叫员的编号。--@按呼叫员编号进行分组；--@计算各呼叫员通话总时长；--@按通话总时长进行降序排列；--@查询前3条记录中呼叫员的编号。select

datediff(month,convert(datetime,'2010-06-01'),convert(datetime,'2010-07-22'))--测试

select

CallerNumber,TellNumber,datediff(month,StartDateTime,EndDateTime)from

T_CallRecords

select

top

CallerNumber

from

T_CallRecordswhere

datediff(month,StartDateTime,getdate())=12--一年前的group

CallerNumberorder

SUM(DATEDIFF(SECOND,StartDateTime,EndDateTime))

DESC

--题

输出本月拨打电话次数最多的前三个呼叫员的编号.--@按呼叫员编号进行分组；--@计算个呼叫员拨打电话的次数；--@按呼叫员拨打电话的次数进行降序排序；--@查询前3条记录中呼叫员的编号。select

top

CallerNumber,count(*)from

T_CallRecordswhere

datediff(month,StartDateTime,getdate())=12--一年前的group

CallerNumberorder

count(*)

DESC

--题5)

输出所有数据的拨号流水，并且在最后一行添加总呼叫时长：

记录呼叫员编号、对方号码、通话时长--......--

汇总[市内号码总时长][长途号码总时长]

--@计算每条记录中通话时长；--@查询包含不加

号码，即市内号码的记录；--@计算市内号码通话总时长；--@查询包含加

号码，即长途号码的记录；--@计算长途号码通话总时长；--@联合查询。select

'汇总'

汇总,convert(varchar(20),sum((casewhen

TellNumber

not

'0%'

then

datediff(second,StartDateTime,EndDateTime)else

0end)))

市内通话,sum((casewhen

TellNumber

'0%'

then

datediff(second,StartDateTime,EndDateTime)else

0end))

长途通话from

T_CallRecordsunion

allselect

CallerNumber,TellNumber,datediff(second,StartDateTime,EndDateTime)

通话时长from

T_CallRecords

--客户和订单表的练习--建立一个客户表create

table

T_Customers(id

int

not

null,namenvarchar(50)

collate

chinese_prc_ci_as

null,age

int

null);insert

T_Customers(id,name,age)

values(1,N'tom',10);insert

T_Customers(id,name,age)

values(2,N'jerry',15);insert

T_Customers(id,name,age)

values(3,N'john',22);insert

T_Customers(id,name,age)

values(4,N'lily',18);insert

T_Customers(id,name,age)

values(5,N'lucy',18);

select

from

T_Customers

--建立一个销售单表create

table

T_Orders(id

int

not

null,billnonvarchar(50)

collate

chinese_prc_ci_as

null,customerid

int

null);

insert

T_Orders(id,billno,customerid)values(1,N'001',1)insert

T_Orders(id,billno,customerid)values(2,N'002',1)insert

T_Orders(id,billno,customerid)values(3,N'003',3)insert

T_Orders(id,billno,customerid)values(4,N'004',2)insert

T_Orders(id,billno,customerid)values(5,N'005',2)insert

T_Orders(id,billno,customerid)values(6,N'006',5)insert

T_Orders(id,billno,customerid)values(7,N'007',4)insert

T_Orders(id,billno,customerid)values(8,N'008',5)

select

from

T_Orders

select

o.billno,,c.agefrom

T_Orders

join

T_Customers

o.customerid=c.id--查询订单号,顾客名字,顾客年龄

select

o.billno,,c.agefrom

T_Orders

join

T_Customers

o.customerid=c.idwhere

c.age>15--显示年龄大于15岁的顾客姓名、年龄和订单号

select

o.billno,,c.agefrom

T_Orders

join

T_Customers

o.customerid=c.idwhere

c.age>(select

avg(age)

from

T_Customers)--显示年龄大于平均年龄的顾客姓名、年龄和订单号

--子查询练习--新建一个数据库，名为BookShopCreate

databaseBookShop

--创建4张表create

table

T_Reader(FId

INT

NOT

NULL,FName

varchar(50),FYearOfBirth

INT,FCity

varchar(50),FProvincevarchar(50),FYearOfJoin

INT);create

table

T_Book(FId

int

not

null,FName

varchar(50),FYearPublished

int,FCategoryId

int);create

table

T_Category(FId

int

not

null,FName

varchar(50));create

table

T_ReaderFavorite(FCategoryId

int,FReaderId

int);

--分别为4张表插入数据insert

into

T_Category(FId,FName)

values(1,'Story');insert

into

T_Category(FId,FName)

values(2,'History');insert

into

T_Category(FId,FName)

values(3,'Theory');insert

into

T_Category(FId,FName)

values(4,'Technology');insert

into

T_Category(FId,FName)

values(5,'Art');insert

into

T_Category(FId,FName)

values(6,'Philosophy');

insert

into

T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin)values(1,'Tom',1979,'TangShan','Hebei',2003);insert

into

T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin)values(2,'Sam',1981,'LangFang','Hebei',2001);insert

into

T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin)values(3,'Jerry',1966,'DongGuan','GuangDong',1995);insert

into

T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin)values(4,'Lily',1972,'JiaXing','ZheJiang',2005);insert

into

T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin)values(5,'Marry',1985,'BeiJing','BeiJing',1999);insert

into

T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin)values(6,'Kelly',1977,'ZhuZhou','HuNan',1995);insert

into

T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin)values(7,'Tim',1982,'YongZhou','HuNan',2001);insert

into

T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin)values(8,'King',1979,'JiNan','ShanDong',1997);insert

into

T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin)values(11,'John',1979,'QingDao','ShanDong',2003);insert

into

T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin)values(9,'Lucy',1978,'LuoYang','HeNan',1996);insert

into

T_Reader(FId,FName,FYearOfBirth,FCity,FProvince,FyearOfJoin)values(10,'July',1983,'ZhuMaDian','HeNan',1999);insert

into

T_Reader(FId,FName,FYearOfBirth,FCity,fProvince,FyearOfJoin)values(12,'Fige',1981,'JinCheng','ShanXi',2003);

insert

into