【www.quanqiunao.cn--数据库安全】
一、SQL 基础知识
1、DDL(数据定义语言)
1)创建数据表
--创建数据表
create table Test(Id int not null, Age char(20));
--创建数据表
create table T_Person1(Id int not null,
Name nvarchar(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 NickName nvarchar(50) null;
--修改表结构,删除字段
Alter table T_Person Drop NickName;
3)删除数据表
--删除数据表
Drop table T_Person;
--删除数据表
drop table test
4)创建索引
Create [Unique] Index <索引名> on <基本表名>(<列明序列>);
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字段改为30
update 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 or Age=50
3)删除语句
delete from T_Person1
--删除表中全部数据
delete from T_Person1 where Name="Tom"
--根据条件删除数据
4)查询语句
查询语句非常强大,几乎可以查任意东西!
-----------------
---- 数据检索 -----
-----------------
--查询不与任何表关联的数据.
SELECT 1+1; --简单运算
select 1+2 as 结果
SELECT newid();--查询一个GUID字符创
select GETDATE() as 日期 --查询日期
--可以查询SQLServer版本
select @@VERSION as SQLServer版本
--一次查询多个
select 1+1 结果, GETDATE() as 日期, @@VERSION as 版本, NEWID() as 编号
--简单的数据查询.HelloWorld级别
SELECT * FROM T_Employee;
--只查询需要的列.
SELECT FNumber FROM T_Employee;
--给列取别名.As关键字
SELECT FNumber AS 编号, FName AS 姓名 FROM T_Employee;
--使用 WHERE 查询符合条件的记录.
SELECT FName FROM T_Employee WHERE FSalary<5000;
--对表记录进行排序,默认排序规则是ASC
SELECT * FROM T_Employee ORDER BY FAge ASC,FSalary DESC;
--ORDER BY 子句要放在 WHERE 子句之后.
SELECT * FROM T_Employee WHERE FAge>23 ORDER BY FAge DESC,FSalary DESC;
--WHERE 中可以使用的逻辑运算符:or、and、not、<、>、=、>=、<=、!=、<>等.
--模糊匹配,首字母未知.
SELECT * FROM T_Employee WHERE FName LIKE "_arry";
--模糊匹配,前后多个字符未知.
SELECT * FROM T_Employee WHERE FName LIKE "%n%";
--NULL 表示"不知道",有 NULL 参与的运算结果一般都为 NULL.
--查询数据是否为 NULL,不能用 = 、!= 或 <>,要用IS关键字
SELECT * FROM T_Employee WHERE FName IS NULL;
SELECT * FROM T_Employee WHERE FName IS NOT NULL;
--查询在某个范围内的数据,IN 表示包含于,IN后面是一个集合
SELECT * FROM T_Employee WHERE FAge IN (23, 25, 28);
--下面两条查询语句等价。
SELECT * FROM T_Employee WHERE FAge>=23 AND FAge<=30;
SELECT * FROM T_Employee WHERE FAge BETWEEN 23 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_Employee
where FSalary<5000
--可更改显示列名的关键字as,as—起别名
select FName as 姓名,FAge as 年龄,FSalary as 薪水 from T_Employee
二、SQL Server 中的数据类型
1、精确数字类型
bigint
int
smallint
tinyint
bit
money
smallmoney
2、字符型数据类型,MS建议用VarChar(max)代替Text
Char
VarChar
Text
3、近似数字类型
Decimal
Numeric
Real
Float
4、Unicode字符串类型
Nchar
NvarChar
Ntext
5、二进制数据类型,MS建议VarBinary(Max)代替Image数据类型,max=231-1
Binary(n) 存储固定长度的二进制数据
VarBinary(n) 存储可变长度的二进制数据,范围在n~(1,8000)
Image 存储图像信息
6、日期和时间类型,数据范围不同,精确地不同
DateTime
SmallDateTime
7、特殊用途数据类型
Cursor
Sql-variant
Table
TimeStamp
UniqueIdentifier
XML
三、SQL中的内置函数
--------------------------------------
----- 数据汇总-聚合函数 ---------
--------------------------------------
--查询T_Employee表中数据条数
select COUNT(*) from T_Employee
--查询工资最高的人
select MAX(FSalary) as Top1 from T_Employee
--查询工资最低的人
select Min(FSalary) as Bottom1 from T_Employee
--查询工资的平均水平
select Avg(FSalary) as 平均水平 from T_Employee
--所有工资的和
select SUM(FSalary) as 总工资 from T_Employee
--查询工资大于5K的员工总数
select COUNT(*) as total from T_Employee
where FSalary>5000
------------------------------
----- 数据排序 -------
------------------------------
--按年龄排序升序,默认是升序
select * from T_Employee
order by FAge ASC
--多个条件排序,先什么,后什么,在前一个条件相同的情况下,根据后一个条件进行排列
--where在order by之前
select * from T_Employee
order by FAge ASC, FSalary DESC
------------------------------
----- 模糊匹配 -------
------------------------------
--通配符查询
--1.单字符通配符_
--2.多字符通配符%
--以DEV开头的任意个字符串
select * from T_Employee
where FNumber like "DEV%"
--以一个字符开头,om结尾的字符串
select * from T_Employee
where FName like "_om"
--检索姓名中包含m的字符
select * from T_Employee
where FName like "%m%"
------------------------------
----- 空值处理 -------
------------------------------
--null表示不知道,不是没有值
--null和其他值计算结果是null
select null+1
--查询名字是null的数据
select * from T_Employee
where FName is null
--查询名字不为空null的数据
select * from T_Employee
where FName is not null
--年龄是23,25,28中的员工
select * from T_Employee
where FAge=23 or FAge=25 or FAge=28
--或者用in 集合查询
--年龄是23,25,28中的员工
select * from T_Employee
where FAge in (23,25,28)
--年龄在20到25之间的员工信息
select * from T_Employee
where FAge>20 and FAge<25
--年龄在20到25之间的员工信息,包含25
select * from T_Employee
where FAge between 20 and 25
------------------------------
----- 数据分组 -------
------------------------------
Select FAge,COUNT(*) from T_Employee
group by FAge
--1.根据年龄进行分组
--2.再取出分组后的年龄的个数
--注意:没有出现在group by 子句中的字段,不能出现在select语句后的列名列表中(聚合函数除外)
--group by 必须出现在where后面
Select FAge,AVG(FSalary),COUNT(*) from T_Employee
group by FAge
--错误用法
Select FAge,FName,COUNT(*) from T_Employee
group by FAge
--加上where的group by 子句
--group by 必须出现在where后面
Select FAge,AVG(FSalary),COUNT(*) from T_Employee
where FAge>=25
group by FAge
--Having不能包含查不到的字段,只能包含聚合函数和本次查询有关的字段
select FAge,COUNT(*) from T_Employee
group by FAge
Having COUNT(*)>1
select FAge,COUNT(*) from T_Employee
where FSalary>2500
group by FAge
--HAVING 子句中的列 "T_Employee.FSalary" 无效,因为该列没有包含在聚合函数或 GROUP BY 子句中
--Having是对分组后信息的过滤,能用的列和select中能有的列是一样的。
--因此,having不能代替where
select FAge,COUNT(*) from T_Employee
group by FAge
Having FSalary>2500
------------------------------
----- 确定结果集行数 -------
------------------------------
--取出所有员工的信息,根据工资降序排列
select * from T_Employee
order by FSalary DESC
--取出前三名员工的信息,根据工资降序排列
select top 3 * from T_Employee
order by FSalary DESC
--根据工资取出排名在6-8的员工信息,按工资降排列
select top 3 * from T_Employee
where FNumber not in
(select top 5 FNumber from T_Employee order by FSalary DESC)
order by 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,FSubCompany
from T_Employee
--以上两个例子结合起来比较,Distinct针对的是整行进行比较的
select Distinct FDepartment,FSubCompany
from 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_TempEmployee
union
select FName,Fage from T_Employee
select FNumber, FName,Fage,FDepartment from T_Employee
union
select FIdCardNumber,FName,Fage,"临时工,无部门" from T_TempEmployee
---Union All:不合并重复数据
--Union:合并重复数据
select FName,FAge from T_Employee
union all
select FName,FAge from T_TempEmployee
select FAge from T_Employee
union
select FAge from T_TempEmployee
--注意:Union因为要进行重复值扫描,所以效率低,因此如果不是确定要合并重复,那么就用Union all
--例子:报名
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
--查询每位正式员工的信息,包括工号,工资,并且在最后一行加上员工工资额合计
select FNumber,FSalary from T_Employee
union all
select "工资额合计",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):返回一个日期的特定部分,比如年月日,时分秒等.
/*
值 缩 写(Sql Server) (Access 和 ASP) 说明
Year Yy yyyy 年 1753 ~ 9999
Quarter Qq q 季 1 ~ 4
Month Mm m 月 1 ~ 12
Day of year Dy y 一年的日数,一年中的第几日 1-366
Day Dd d 日, 1-31
Weekday Dw w 一周的日数,一周中的第几日 1-7
Week Wk ww 周,一年中的第几周 0 ~ 51
Hour Hh h 时0 ~ 23
Minute Mi n 分钟0 ~ 59
Second Ss s 秒 0 ~ 59
Millisecond Ms - 毫秒 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()) as 工龄 from T_Employee
--取出每一年入职员工的个数V1
select DATEDIFF(year,FInDate,getdate()),COUNT(*)
from T_Employee
group by DATEDIFF(year,FInDate,getdate())
--取出每一年入职员工的个数V2
select DATEPART(YEAR,FInDate), COUNT(*)
from T_Employee
group by 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(expression as data_type)
--CONVERT(data_type,expression)
select CAST("123" as int),CAST("2010-09-08" as datetime),
CONVERT(datetime,"2010-09-08"),CONVERT(varchar(20),123)
--5.空值处理函数isNull
--ISNULL(expression,value)
select ISNULL(FName,"佚名") as 姓名 from T_Employee
--6.CASE函数用法:
--1.单值判断:相当于switch.case
--CASE expression
--WHEN value1 then returnvalue1
--WHEN value2 then returnvalue2
--WHEN value3 then returnvalue3
--ELSE default_return_value
--END
--判断客户类型
select FName,
(
case FLevel
when 1 then "普通客户"
when 2 then "会员"
when 3 then "VIP"
else "未知客户类型"
End
) as 客户类型
from T_Customer
--收入水平查询
select FName,
(
case
when 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
(
case
when a > b then a else b
end
),
(
case
when b>c then b else c
end
)
from T
---------------------------------------
select FNumber,
(
case
when FAmount>0 then FAmount
else 0
end
) as 收入,
(
case
when FAmount<0 then ABS(FAmount)
else 0
end
) as 支出
from T
-----------------------------------------
--球队比赛那个题
--有一张表T_Scroes,记录比赛成绩:
--Date Name Scroe
--2008-8-8 拜仁 胜
--2008-8-9 奇才 胜
--2008-8-8 湖人 胜
--2008-8-10 拜仁 负
--2008-8-8 拜仁 负
--2008-8-12 奇才 胜
--要求输出下面格式:
--Name 胜 负
--拜仁 1 2
--湖人 1 0
--奇才 2 0
--注意:在中文字符串前加 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 AS DateTime), N"拜仁", N"胜");
INSERT [T_Scores] ([Date], [Name], [Score]) VALUES (CAST(0x00009AF300000000 AS DateTime), N"奇才", N"胜");
INSERT [T_Scores] ([Date], [Name], [Score]) VALUES (CAST(0x00009AF300000000 AS DateTime), N"湖人", N"胜");
INSERT [T_Scores] ([Date], [Name], [Score]) VALUES (CAST(0x00009AF400000000 AS DateTime), N"拜仁", N"负");
INSERT [T_Scores] ([Date], [Name], [Score]) VALUES (CAST(0x00009AF200000000 AS DateTime), N"拜仁", N"负");
INSERT [T_Scores] ([Date], [Name], [Score]) VALUES (CAST(0x00009AF600000000 AS DateTime), N"奇才", N"胜");
select * from T_Scores
--列出第一个表格
--统计每支队伍的胜负情况
select Name,
(
case Score
when N"胜" then 1
else 0
end
) as 胜,
(
case Score
when N"负" then 1
else 0
end
) as 负
from T_Scores
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
--根据每个队的胜负判断出胜负的场数
--题5) 创建一张表,记录电话呼叫员的工作流水,记录呼叫员编号,对方号码,通话开始时间,通话结束时间,。
--创建一张表T_Callers,记录电话呼叫员的工作流水,记录呼叫员编号、对方号码、通话开始时间、通话结束时间。建表、插数据等最后都自己写SQL语句。
--要求:
-- 1) 输出所有数据中通话时间最长的5条记录。
-- 2) 输出所有数据中拨打长途号码(对方号码以0开头)的总时长。
-- 3) 输出本月通话总时长最多的前三个呼叫员的编号。
-- 4) 输出本月拨打电话次数最多的前三个呼叫员的编号。
-- 5) 输出所有数据的拨号流水,并且在最后一行添加总呼叫时长。
-- 记录呼叫员编号、对方号码、通话时长
-- ......
-- 汇总[市内号码总时长][长途号码总时长]
--Id CallerNumber TellNumber StartDateTime EndDateTime
--1 001 02088888888 2010-7-10 10:01 2010-7-10 10:05
--2 001 02088888888 2010-7-11 13:41 2010-7-11 13:52
--3 001 89898989 2010-7-11 14:42 2010-7-11 14:49
--4 002 02188368981 2010-7-13 21:04 2010-7-13 21:18
--5 002 76767676 2010-6-29 20:15 2010-6-29 20:30
--6 001 02288878243 2010-7-15 13:40 2010-7-15 13:56
--7 003 67254686 2010-7-13 11:06 2010-7-13 11:19
--8 003 86231445 2010-6-19 19:19 2010-6-19 19:25
--9 001 87422368 2010-6-19 19:25 2010-6-19 19:36
--10 004 40045862245 2010-6-19 19:50 2010-6-19 19:59
-- 创建表
create table T_CallRecords(
id int not null,
CallerNumber varchar(3),
TellNumber varchar(13),
StartDateTIme datetime,
EndDateTime datetime,
Primary key(Id)
);
--插入数据
insert into T_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTIme)
values(1,"001","02088888888","2010-7-10 10:01","2010-7-10 10:05");
INSERT INTO T_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)
VALUES (2,"002","02088888888", "2010-7-11 13:41","2010-7-11 13:52");
INSERT INTO T_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)
VALUES (3,"003","89898989", "2010-7-11 14:42", "2010-7-11 14:49");
INSERT INTO T_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)
VALUES (4,"004","02188368981", "2010-7-13 21:04", "2010-7-13 21:18");
INSERT INTO T_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)
VALUES (5,"005","76767676", "2010-6-29 20:15", "2010-6-29 20:30");
INSERT INTO T_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)
VALUES (6,"006","02288878243", "2010-7-15 13:40", "2010-7-15 13:56");
INSERT INTO T_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)
VALUES (7,"007","67254686", "2010-7-13 11:06", "2010-7-13 11:19");
INSERT INTO T_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)
VALUES (8,"008","86231445", "2010-6-19 19:19", "2010-6-19 19:25");
INSERT INTO T_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)
VALUES (9,"009","87422368", "2010-6-19 19:25", "2010-6-19 19:36");
INSERT INTO T_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)
VALUES (10,"010","40045862245", "2010-6-19 19:50", "2010-6-19 19:59");
--修改呼叫员编号
UPDATE T_CallRecords SET CallerNumber="001" WHERE Id IN (1,2,3,6,9);
UPDATE T_CallRecords SET CallerNumber="002" WHERE Id IN (4,5);
UPDATE T_CallRecords SET CallerNumber="003" WHERE Id IN (7,8);
UPDATE T_CallRecords SET CallerNumber="004" WHERE Id=10;
--数据汇总
select * from T_CallRecords
--题 1): 输出所有数据中通话时间最长的5条记录。
--@计算通话时间;
--@按通话时间降序排列;
--@取前5条记录。
select top 5 CallerNumber,DATEDIFF(SECOND,StartDateTime,EndDateTime) as 总时长
from T_CallRecords
order by DATEDIFF(SECOND,StartDateTime,EndDateTime) DESC
--题 2):输出所有数据中拨打长途号码(对方号码以0开头)的总时长
--@查询拨打长途号码的记录;
--@计算各拨打长途号码的通话时长;
--@对各拨打长途号码的通话时长进行求和。
select SUM(DATEDIFF(SECOND,StartDateTime,EndDateTime)) as 总时长 from T_CallRecords
where TellNumber like "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 3 CallerNumber from T_CallRecords
where datediff(month,StartDateTime,getdate())=12--一年前的
group by CallerNumber
order by SUM(DATEDIFF(SECOND,StartDateTime,EndDateTime)) DESC
--题 4) 输出本月拨打电话次数最多的前三个呼叫员的编号.
--@按呼叫员编号进行分组;
--@计算个呼叫员拨打电话的次数;
--@按呼叫员拨打电话的次数进行降序排序;
--@查询前3条记录中呼叫员的编号。
select top 3 CallerNumber,count(*)
from T_CallRecords
where datediff(month,StartDateTime,getdate())=12--一年前的
group by CallerNumber
order by count(*) DESC
--题5) 输出所有数据的拨号流水,并且在最后一行添加总呼叫时长:
-- 记录呼叫员编号、对方号码、通话时长
-- ......
-- 汇总[市内号码总时长][长途号码总时长]
--@计算每条记录中通话时长;
--@查询包含不加 0 号码,即市内号码的记录;
--@计算市内号码通话总时长;
--@查询包含加 0 号码,即长途号码的记录;
--@计算长途号码通话总时长;
--@联合查询。
select "汇总" as 汇总,
convert(varchar(20),
sum((
case
when TellNumber not like "0%" then datediff(second,StartDateTime,EndDateTime)
else 0
end
))) as 市内通话,
sum((
case
when TellNumber like "0%" then datediff(second,StartDateTime,EndDateTime)
else 0
end
)) as 长途通话
from T_CallRecords
union all
select CallerNumber,TellNumber,datediff(second,StartDateTime,EndDateTime) as 通话时长
from T_CallRecords
--客户和订单表的练习
--建立一个客户表
create table T_Customers(
id int not null,
name nvarchar(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,
billno nvarchar(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.name,c.age
from T_Orders as o join T_Customers as c on o.customerid=c.id
--查询订单号,顾客名字,顾客年龄
select o.billno,c.name,c.age
from T_Orders as o join T_Customers as c on o.customerid=c.id
where c.age>15
--显示年龄大于15岁的顾客姓名、年龄和订单号
select o.billno,c.name,c.age
from T_Orders as o join T_Customers as c on o.customerid=c.id
where c.age>(select avg(age) from T_Customers)
--显示年龄大于平均年龄的顾客姓名、年龄和订单号
--子查询练习
--新建一个数据库,名为BookShop
Create database BookShop
--创建4张表
create table T_Reader(FId INT NOT NULL,FName varchar(50),FYearOfBirth INT,FCity varchar(50),FProvince varchar(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 T_Book(FId,FName,FYearPublished,FCategoryId) values(1,"About J2EE",2005,4);
insert into T_Book(FId,FName,FYearPublished,FCategoryId) values(2,"Learning Hibernate",2003,4);
insert into T_Book(FId,FName,FYearPublished,FCategoryId) values(3,"Tow Cites",1999,1);
insert into T_Book(FId,FName,FYearPublished,FCategoryId) values(4,"Jane Eyre",2001,1);
insert into T_Book(FId,FName,FYearPublished,FCategoryId) values(5,"Oliver Twist",2002,1);
insert into T_Book(FId,FName,FYearPublished,FCategoryId) values(6,"History of China",1982,2);
insert into T_Book(FId,FName,FYearPublished,FCategoryId) values(7,"History of England",1860,2);
insert into T_Book(FId,FName,FYearPublished,FCategoryId) values(8,"History of America",1700,2);
insert into T_Book(FId,FName,FYearPublished,FCategoryId) values(9,"History of The Vorld",2008,2);
insert into T_Book(FId,FName,FYearPublished,FCategoryId) values(10,"Atom",1930,3);
insert into T_Book(FId,FName,FYearPublished,FCategoryId) values(11,"RELATIVITY",1945,3);
insert into T_Book(FId,FName,FYearPublished,FCategoryId) values(12,"Computer",1970,3);
insert into T_Book(FId,FName,FYearPublished,FCategoryId) values(13,"Astronomy",1971,3);
insert into T_Book(FId,FName,FYearPublished,FCategoryId) values(14,"How To singing",1771,5);
insert into T_Book(FId,FName,FYearPublished,FCategoryId) values(15,"DaoDeJing",2001,6);
insert into T_Book(FId,FName,FYearPublished,FCategoryId) values(16,"Obedience to Au",1995,6);
insert into T_ReaderFavorite(FCategoryId,FReaderId) values(1,1);
insert into T_ReaderFavorite(FCategoryId,FReaderId) values(5,2);
insert into T_ReaderFavorite(FCategoryId,FReaderId) values(2,3);
insert into T_ReaderFavorite(FCategoryId,FReaderId) values(3,4);
insert into T_ReaderFavorite(FCategoryId,FReaderId) values(5,5);
insert into T_ReaderFavorite(FCategoryId,FReaderId) values(1,6);
insert into T_ReaderFavorite(FCategoryId,FReaderId) values(1,7);
insert into T_ReaderFavorite(FCategoryId,FReaderId) values(4,8);
insert into T_ReaderFavorite(FCategoryId,FReaderId) values(6,9);
insert into T_ReaderFavorite(FCategoryId,FReaderId) values(5,10);
insert into T_ReaderFavorite(FCategoryId,FReaderId) values(2,11);
insert into T_ReaderFavorite(FCategoryId,FReaderId) values(2,12);
insert into T_ReaderFavorite(FCategoryId,FReaderId) values(1,12);
insert into T_ReaderFavorite(FCategoryId,FReaderId) values(3,1);
insert into T_ReaderFavorite(FCategoryId,FReaderId) values(1,3);
insert into T_ReaderFavorite(FCategoryId,FReaderId) values(4,4);
select * from T_Book
select * from T_Category
select * from T_Reader
select * from T_ReaderFavorite
--并列查询
select 1 as f1,2,(select MIN(FYearPublished) from T_Book),
(select MAX(FYearPublished) from T_Book) as f4
--查询入会日期在2001或者2003年的读者信息
select * from T_Reader
where FYearOfJoin in (2001,2003)
--与between...and不同
select * from T_Reader
where FYearOfJoin between 2001 and 2003
--查询有书出版的年份入会的读者信息
select * from T_Reader
where FYearOfJoin in
(
select FYearPublished from T_Book
)
--SQL Server 2005之后的版本内置函数:ROW_NUMBER(),称为开窗函数,可以进行分页等操作。
select ROW_NUMBER() over(order by FSalary DESC) as Row_Num,
FNumber,FName,FSalary,FAge from T_Employee
--特别注意,开窗函数row_number()只能用于select或order by 子句中,不能用于where子句中
--查询第3行到第5行的数据
select * from
(
select ROW_NUMBER() over(order by FSalary DESC) as Row_Num,
FNumber,FName,FSalary,FAge from T_Employee
) as e1
where e1.Row_Num>=3 and e1.Row_Num<=5
四、SQL其他概念
--索引
1、什么是索引?优缺点是什么?
索引是对数据库表中一列或多列的值进行排序的一种单独的、物理的数据库结构。
优点:
1) 大大加快数据的检索速度;
2) 创建唯一性索引,保证数据库表中每一行数据的唯一性;
3) 加速表和表之间的连接;
4) 在使用分组和排序子句进行数据检索时,可以显著减少查询中分组和排序的时间。
缺点:
1) 索引需要占物理空间;
2) 当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,降低了数据的维护速度。
--创建索引,在列上点击右键,写一个名称,选定列即可。
2、业务主键和逻辑主键
业务主键是使用有业务意义的字段做主键,比如身份证号,银行账号等;
逻辑主键是使用没有任何业务意义的字段做主键。因为很难保证业务主键不会重复(身份证号重复)、不会变化(账号升位),因此推荐使用逻辑主键。
3、SQL Server 两种常用的主键数据类型
1) int(或 bigint) + 标识列(又称自动增长字段)
用标识列实现字段自增可以避免并发等问题,不要开发人员控制自增。用标识列的字段在Insert的时候不用指定主键的值。
优点:占用空间小、无需开发人员干预、易读;
缺点:效率低,数据导入导出的时候很痛苦。
设置:"修改表"->选定主键->"列属性"->"标识规范"选择"是"
2) uniqueidentifier(又称GUID、UUID)
GUID算法是一种可以产生唯一表示的高效算法,它使用网卡MAC、地址、纳秒级时间、芯片ID码等算出来的,这样保证每次生成的GUID永远不会重复,无论是同一计算机还是不同计算机。在公元3400年前产生的GUID与任何其他产生过的GUID都不相同。
SQL Server中生成GUID的函数newid()。
优点:效率高、数据导入导出方便;
缺点:占用空间大、不易读。
业界主流倾向于使用GUID。
写在后面:看着洋洋洒洒的一大篇,除了一些常识性的东西和涉及到数据库的增删改查之外,其实没什么新鲜东西,但判断一个程序员水平的高低不仅是做过多么大的一个项目,更是对基础知识的掌握程度。