SQL 语句

发布时间:2014-10-22 12:13:42编辑:www.fx114.net 分享查询网我要评论
本篇文章主要介绍了"SQL 语句",主要涉及到SQL 语句方面的内容,对于SQL 语句感兴趣的同学可以参考一下。

如今各个公司对程序员的数据库知识尤其是SQL语句的掌握程度有很高的要求,作为一名光荣的程序员,不会玩儿SQL语句走在街上根本不好意思和人打招呼!好了,废话不多说。 sql server常用的两种主键数据类型 int(或bigint)+标识列(又称自动增长字段),另外一种就是uniqueidentifier数据类型(又称Guid  UUID) GUID在SQL server里的使用就是直接newid就可以了 insert into T_us (id,userName,[password])values(newid,'tom',123456) Guid在c#里的调用方法就是Guid.NewGuid();   sql表是可以设置默认值的。如果id不是为int类型主键,而是guid类型主键,那么我们可以给id设一个默认值“newid()”到时候就会自动生成了。但是一般我们很少这么干。 use sales 指向sales的数据库 select 姓名 ,datediff(year,出生时间,getdate()) as '年龄' from xs  --查询年龄datediff(year,出生时间,getdate())函数就是获取你的年龄,例如:36 alter table usertable  altercolumn userpawsnchar(20)--修改表列的属性 一、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); --------约束 alter  table tableName --为一个表创建约束 add  constraint   Pk_tableName  --约束名(约束名一般是以Pk开头 也就是主键的缩写,Primary Key) primary key (学号)  --为学号列添加约束   alter   table tableName  --删除约束 drop  Pk_tableName   select * from  sys.key_constraints --查询所以表的约束 --创建表,添加外键 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 column 列名   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%' --查询姓名以王和李开头的学生 select*fromxs where姓名like'王%'or姓名like'李%' ------------------------------ ----- 空值处理 ------- ------------------------------ --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   --年龄在35到36之间的学生信息 (表中没有学生的年龄,只有出生时间"1988-10-12 00:00:00",所以这个时候就必须吧学生的年龄计算出来) datediff(year,出生时间,getdate()) 就取得了学生的实际年龄 比如说36 ,有了所有学生的实际年龄。这个时候只要between 35 and 36 就可以啦。 select* fromxswhere datediff(year,出生时间,getdate())between 35 and 36   ------------------------------ ----- 数据分组 ------- ------------------------------ 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,Primarykey(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 实现方法:我将每个队(Name)的Score中的值用数字标识,在胜的一栏里将胜用1来替代,负用0来替代,在负的一栏里 负用1替代,胜用0替代。然后查询每个队(Name)的 Score中值的和(sum).最后对Name进行分组 select Name, SUM(胜)as'胜',SUM(负) as'负' from( select Name ,(case Score when '胜' then 1 else 0 end)as '胜',(case Score when '负' then 1 else 0 end)as'负' from T_Scores) as t1 group by Name --注意:在中文字符串前加 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),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 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。 


上一篇:记住密码和自动登录
下一篇:[硬件技术] 教你如何挑选主板:电脑主板质量好坏的鉴别方法

相关文章

关键词: SQL 语句

相关评论

本站评论功能暂时取消,后续此功能例行通知。

一、不得利用本站危害国家安全、泄露国家秘密,不得侵犯国家社会集体的和公民的合法权益,不得利用本站制作、复制和传播不法有害信息!

二、互相尊重,对自己的言论和行为负责。

好贷网好贷款