1 --------------------------------------------------------SQL基本命令开始------------------------------------------------------- 2 3 --切换数据库 4 use master 5 go 6 7 ---------------------------------------数据库操作----------------------------------------------------------------- 8 --删除数据库 9 --drop DataBase MySchool 10 11 -- 创建数据库 12 create database MySchool 13 on 14 ( 15 name="MySchool", --文件的逻辑名 16 filename="d:databaseMySchool.mdf", --文件的保存路径，database文件夹要先存在 17 size=3, --文件的初始大小 18 maxsize=100, --文件的最大值 19 filegrowth=1 --文件的增长比例，还可以filegrowth=10% 20 ) 21 --日志文件 22 log on 23 ( 24 name="MySchool_log", --文件的逻辑名 25 filename="d:databaseMySchool_ldf.ldf", --文件的保存路径，database文件夹要先存在 26 size=3, --文件的初始大小 27 maxsize=100, --文件的最大值 28 filegrowth=10% --文件的增长比例，还可以filegrowth=10% 29 30 ) 31 go 32 --修改数据库 33 --alter database MySchool 34 35 36 --------------------------------------------数据库操作结束-------------------------------------------------------------------- 37 38 39 40 --------------------------------------------表操作---------------------------------------------------------------------------- 41 42 --注意，当删除表时，如果表上有外键约束，应注意删除顺序，还有就是级联删除，不再累赘 43 --切换数据库 44 use MySchool 45 go 46 --删除表 47 --drop table Class 48 --创建表 Class 49 create table Class 50 ( 51 cId int identity(1,1) primary key,--设置cId为自动增长，初始值为1，增长量为1,并且为主键 52 cName nvarchar(10) not null, 53 cDescription nvarchar(200) 54 ) 55 go 56 --删除表Student 57 --drop table Student 58 --创建表 59 create table Student 60 ( 61 sId int identity(1,1) primary key,--设置sId为自动增长，初始值为1，增长量为1,并且为主键 62 sName nvarchar(10) not null,--设置姓名不能为空 63 sSex nchar(1) , 64 sAge int,-- check(sAge>18 and sAge<150), 65 sBirthday datetime, 66 sClassId int not null, 67 sNo decimal(18,0)--身份证号 68 ) 69 go 70 71 72 73 74 -------------------------表结构的修改------------- 75 --增加列 76 alter table Student 77 add sNo decimal(18,0) 78 79 --修改表 时要加上 column 80 alter table student 81 alter column sName nvarchar(8) 82 83 alter table student 84 alter column sName nvarchar(10) not null 85 86 87 --删除列 时 要加column 88 alter table Student 89 drop column sNo 90 91 --删除表Score 92 --drop table Score 93 --创建表 Score 94 create table Score 95 ( 96 sId int identity(1,1) primary key, 97 studentId int not null, 98 english float, 99 math float 100 ) 101 go 102 103 104 --插入数据到 Class,可以把列直接拖到()中 105 insert into Class (cName)values('高一一班') 106 insert into Class (cName, cDescription)values('高一二班','快班') 107 --insert into Class values('高一三班') --省略列名时，必须插入所有数据，自动增长列除外 108 insert into Class values('高一三班','中班') 109 --查询数据 110 select * from Class 111 112 --插入数据到 Student 113 --insert into Student(sName, sSex, sAge, sBirthday, sClassId) values('tom','男',22,1993-02-12,1); 114 --错误，日期必须加引号 115 insert into Student(sName, sSex, sAge, sBirthday, sClassId) values('tom','男',22,'1993-02-12',1); 116 insert into Student(sName, sSex, sAge, sBirthday, sClassId) values('jack','男',22,'1993-02-12',1); 117 118 --修改数据 119 update Student set sAge=19,sSex='女' 120 update Student set sSex='男' where sId=1 121 update Student set sAge=sAge+1 122 123 --查询数据 124 select * from Student 125 --删除数据 126 delete from Student 127 delete from Student where sName='tom' 128 129 --清空表中的数据，连自动编号也一起清掉 130 truncate table Score 131 truncate table Student 132 truncate table Class 133 134 --------------------------------------------表操作结束-------------------------------------------------------------------- 135 136 137 138 139 140 ----------------------------------------------约束开始--------------------------------------------------------------------- 141 142 143 --自动增长 144 --下面的写法太麻烦 145 --alter table Student 146 --drop column sId 147 --alter table Student 148 --add sId int identity(1,1) 149 150 --alter table Score 151 --drop column sId 152 --alter table Score 153 --add sId int identity(1,1) 154 -- 155 --alter table Class 156 --drop column cId 157 --alter table Class 158 --add cId int identity(1,1) 159 160 --非空约束-----修改表的约束 161 alter table Student 162 alter column sName nvarchar(10) not null 163 164 ------------------------给字段增加约束------------------------ 165 166 --主键约束 167 alter table Student 168 add constraint PK_Student_sId primary key(sId) 169 170 alter table Class 171 add constraint PK_Class_cId primary key(cId) 172 173 alter table Score 174 add constraint PK_Score_cId primary key(sId) 175 --唯一约束 176 alter table Student 177 add constraint UQ_Student_sNo unique(sNo)--身份证号唯一 178 --默认约束 179 alter table Student 180 add constraint DF_Student_sSex default('男') for sSex --注意是 for sSex 181 182 --检查约束 183 alter table Student 184 add constraint CK_Student_sSex check(sSex='男' or sSex='女') 185 186 -------------------外键约束---------------------------------------- 187 188 --Score表中的外键是student表中的sId，student表中的外键是class表中的主键cId 189 190 --------注意 以下建立外键约束的顺序不能反，否则会出错 191 192 alter table Score 193 add constraint FK_Score_studentId foreign key (studentId) references Student(sId) on delete cascade on update cascade 194 195 alter table Student 196 add constraint FK_Student_sClassId foreign key (sClassId) references Class(cId) on delete cascade on update cascade --级联删除的实现当删除班级号时,所在班级的所有学生的信息都被删除. 197 198 199 200 --级联删除 201 --on delete cascade 202 -- on update cascade 203 204 --往子表加数据，外键的值必须在主表中出现 205 insert into student(sName,sNo,sClassId) values('abc',111,100) 206 --删除主表中数据的时候，将删除子表中外键对应的数据 207 delete from class where cId = 1 208 select * from class 209 select * from student 210 -------------------------------------------------约束结束---------------------------------------------------------- 211 212 ------------------------------------------------约束练习开始-------------------------------------------------------- 213 --Teacher表中 214 --tSex 控制只能是男 女，默认男 215 --tAge 在30-40之间 默认30 216 --tName 唯一 217 use master 218 go 219 create table Teacher 220 ( 221 tId int identity(1,1), 222 tSex nchar(1), 223 tAge int, 224 tName nvarchar(10) 225 ) 226 alter table Teacher 227 add constraint CK_Teacher_tSex check(tSex='男' or tSex='女') , 228 constraint DF_Teacher_tSex default ('男') for tSex, 229 constraint CK_Teacher_tAge check(tAge>=30 and tAge<=40) , 230 constraint DF_Teacher_tAge default (30) for tAge, 231 constraint UQ_Teacher_tName unique(tName) 232 ---------------------------------------练习结束结束---------------------------------------------------------------------- 233 234 ----------------------------------------约束的简单写法------------------------------------------------------------- 235 --drop table Student0 236 create table Student0 237 ( 238 sId int identity(1,1) primary key, 239 sName nvarchar(10) not null, 240 sAge int constraint CK_Student0_sAge check(sAge >= 18) constraint DF_Student0_sAge default(18), 241 sClassId int constraint FK_Student0_sClassId foreign key (sClassId) references Class(cId) 242 ) 243 244 245 alter table Student0 246 add sSex nchar(1) 247 248 alter table Student0 249 drop column sSex 250 251 -------------------------------------约束的简单写法结束--------------------------------------------------------------- 252 253 254 255 256 --------------------------------------插入测试数据开始--------------------------------------------------------------- 257 258 insert into Class (cName,cDescription) values ('高一一班','快班') 259 insert into Class (cName,cDescription) values ('高一二班','中班') 260 insert into Class (cName,cDescription) values ('高一三班','慢班') 261 insert into Class (cName,cDescription) values ('高二一班','快班') 262 insert into Class (cName,cDescription) values ('高二二班','中班') 263 insert into Class (cName,cDescription) values ('高二三班','慢班') 264 265 insert into Student (sClassId,sName,sAge,sSex,sNo,sBirthday) values (1,'刘备',20,'男',123456789012345678,'1987-5-6') 266 insert into Student (sClassId,sName,sAge,sSex,sNo,sBirthday) values (1,'关羽',19,'男',123456789012345552,'1988-8-6') 267 insert into Student (sClassId,sName,sAge,sSex,sNo,sBirthday) values (1,'张两飞',19,'男',123456789068745672,'1989-5-19') 268 insert into Student (sClassId,sName,sAge,sSex,sNo,sBirthday) values (4,'曹操',22,'男',123456789012345673,'1985-12-6') 269 insert into Student (sClassId,sName,sAge,sSex,sNo,sBirthday) values (4,'夏侯惇',22,'男',123456789012345674,'1985-3-6') 270 insert into Student (sClassId,sName,sAge,sSex,sNo,sBirthday) values (4,'华佗',50,'男',123456789012356574,'1957-1-16') 271 insert into Student (sClassId,sName,sAge,sSex,sNo,sBirthday) values (4,'甄姬',52,'女',12345678901234565,'1989-8-8') 272 273 insert into Score (studentId,english) values(1,90) 274 insert into Score (studentId,english) values(2,90) 275 insert into Score (studentId,english) values(3,59) 276 insert into Score (studentId,english) values(4,100) 277 insert into Score (studentId,english) values(5,60) 278 insert into Score (studentId,english) values(8,0) 279 insert into Score (studentId,english) values(7,80) 280 ---------------------------------------插入测试数据结束------------------------------------------------------- 281 282 283 284 285 286 --------------------------------------数据的检索---------------------------------------------------------------- 287 288 ------------------------------------简单的数据检索---------------------------------------------------------------- 289 select * from Student 290 select * from Score 291 select * from Class 292 ---------只检索需要的列 293 select sName from student 294 select sName as '姓名',sAge as '年龄' from student 295 ----------使用where检索符合条件的数据 296 select sName '姓名' from student where sSex='男' 297 ---------检索不与任何表关联的数据 298 select 1 299 select getdate() as '当前日期' 300 ---------Top 获取前几条数据 301 302 select top 2 * from student 303 select top 2 sName from student 304 -----使用百分号,不够一,进一,没有舍 305 select top 20 percent * from student 306 307 ---------Distinct 去除重复数据,是针对所有被查询的列而言,不是单个列 308 select distinct * from student 309 select distinct sName from student 310 311 ---------------------------------聚合函数-------------------------------------------- 312 313 select max(sId) from student 314 select min(sId)from student 315 select avg(math) from score-----不会计算值为null 的列 316 select sum(math) from score 317 select count(*) from score 318 select sum(math)/count(*) from score 319 320 select max(sBirthday),min(sBirthday) from student where sSex='男' 321 322 323 324 select * from Student 325 select * from Score 326 select * from Class 327 328 -----------------------------带条件的查询--------------------------------------------- 329 330 --Select …from…where 331 --查询没有及格的学生的学号 332 select studentId from score where english<60 333 --查询年龄在20-30岁之间的男学生 334 select sName from student where sAge>=20 and sAge<=30 335 --Between…and …在之间 336 --查询年龄在20-30岁之间的男学生 337 select sName from student where sAge between 20 and 30 338 --查询班级id为1，2，3的所有学生 339 select sId,sName from student where sId=1 or sId=2 or sId=3 340 select sId,sName from student where sId in(1,2,3) 341 342 ---------------------------模糊查询--------------------------------------------------------- 343 344 ----查询所有姓张的同学 345 select * from student where left(sname,1)='张'---局限性太大 346 select * from student where sName like '张%'---%匹配零个或多个任意字符 347 select * from student where sName like '张_'----- _匹配任意的一个字符 348 select * from student where sName like '_[两]%'----- []匹配[]内的一个字符 349 select * from student where sName like '_[飞两]%'----- []匹配[]内的一个字符 350 351 352 ---------------------------------------NULL值处理--------------------------------------------- 353 354 -----null表示“不知道”，而不是表示没有 355 select null+1 ---结果是null，因为“不知道”加1的结果还是“不知道”。 356 357 select * from score where math =null---错误写法 358 select * from score where math is null 359 select * from score where math is not null 360 361 ---------------------------------------order by asc ,desc 数值排序，默认升序asc--------------------------------------- 362 363 --按照年龄升序排序所有学生信息的列表 364 select * from student order by sAge asc 365 --按照英语成绩从大到小排序，如果英语成绩相同则按照数学成绩从大到小排序 366 select * from score order by english desc,math desc 367 --ORDER BY子句要放到WHERE子句之后 368 select * from score 369 where english>80 370 order by english desc,math desc 371 372 ---------------------------------------group by 数据分组------------------------------------------ 373 374 --按照班级进行分组统计各个班级的人数 375 select cName,count(*)from class 376 group by cName 377 378 --GROUP BY子句必须放到WHERE语句的之后 379 select sSex,count(*) from student 380 where sAge>18 381 group by sSex 382 383 --聚合函数不能出现在where子句中 384 select sSex,count(*) from student 385 where sAge>18 and avg(sAge)>19 386 group by sSex 387 388 389 --没有出现在GROUP BY子句中的列是不能放到SELECT语句后的列名列表中的 （聚合函数中除外） 390 391 --select sSex,count(*), avg(sAge),sName from student --错误，sName不能放select在列表中 392 select sSex,count(*), avg(sAge) from student 393 where sAge>18 394 group by sSex 395 396 ----------------------------having 对分组后的内容进行条件选择------------------------------------------- 397 398 --在Where中不能使用聚合函数，Having中可以，Having要位于Group By之后,而且having中的列要在select中存在 399 --错误,sAge列在select中不存在,而且它也不是聚合函数 400 --select sSex,count(*), avg(sAge) from student 401 --group by sSex 402 --having sAge>18 and avg(sAge)>19 403 404 select sSex,count(*), avg(sAge) from student 405 group by sSex 406 having avg(sAge)>19 407 408 409 410 --------练习------------- 411 --求男生和女生分别有多少人 412 select sSex,count(*) from student 413 group by sSex 414 415 --每个班中的男同学的平均年龄 416 --select * from student 417 select count(*) , avg(sAge),sClassId from student 418 where sSex='男' 419 group by sClassId 420 --求平均年龄小于22的那些班 421 select sClassId from student 422 group by sClassId 423 having avg(sAge)<22 424 425 -----------------------------------------union 联合结果集--------------------------------------------- 426 427 --列的个数一致，类型一致（兼容） 428 --排序 去除重复数据 429 select * from student 430 select * from teacher 431 select sId ,sSex,sName from student union select tId,tSex ,tName from teacher 432 433 --union all 434 --直接连接两个结果集，不排序，不去除重复，效率高 435 select sId ,sSex,sName from student union all select tId,tSex ,tName from teacher 436 437 --要求在一个表格中查询出学生的英语最高成绩、最低成绩、平均成绩 438 select max(english),min(english),avg(english) from score 439 select '最高成绩',max(english)from score union all select '最低成绩',min(english)from score union all select'平均成绩', avg(english) from score 440 --查询每位老师的信息，包括姓名、工资，并且在最后一行加上平均工资和最高工资 441 --alter table teacher 442 --add tSalary int 443 select tName,tSalary from teacher 444 union all select '平均工资',avg(tSalary) from teacher 445 union all select '总工资',sum(tSalary) from teacher 446 447 -------------------------------一次插入多条数据---------------------------------------------- 448 insert into score 449 select 1,66,88 union 450 select 2,85,78 union 451 select 3,36,98 452 453 --把现有表的数据插入到新表（表不能存在） 454 select * into newStudent from student 455 select * from newStudent 456 truncate table newStudent 457 --把现有表的数据复制到一个已存在的表 458 ---要先把设置的自动增长列干掉 459 insert into newStudent select sName, sSex, sAge, sBirthday, sClassId, sNo from student 460 461 462 ----------------------------------------------------数据检索结束--------------------------------------------------------------- 463 464 465 ------------------------------------------------------------sql中的函数---------------------------------------------------------- 466 467 -------------------------------------字符串处理函数---------------------------------------------------------- 468 select len(3455) 469 select len(getdate()) 470 --LOWER() 、UPPER () ：转小写、大写 471 select lower('ADGBAddf') 472 select upper('ADGBAddf') 473 --LTRIM()：字符串左侧的空格去掉 474 select ltrim(' adga ') 475 --RTRIM () ：字符串右侧的空格去掉 476 select rtrim(' adga ')+'123' 477 --去除左右空格方法 478 --LTRIM(RTRIM(' bb ')) 479 select '456'+ltrim(rtrim(' dag ')+'12') 480 --LEFT()、RIGHT() 截取取字符串 481 select left('2344',1) 482 select right('2344',1) 483 --SUBSTRING(string,start_position,length) 484 select substring('23444',1,2)---注意起始位置从一开始，和c#不同 485 486 487 ----------------------------------------------------日期函数-------------------------------------------------------- 488 489 --GETDATE() ：取得当前日期时间 490 select getdate() 491 --DATEADD (datepart , number, date )，计算增加以后的日期。参数date为待计算的日期；参数number为增量；参数datepart为计量单位，可选值见帮助 492 select dateadd(day,1,getdate()) 493 select dateadd(year,1,getdate()) 494 select dateadd(month,1,getdate()) 495 select dateadd(hour,1,getdate()) 496 497 --获取年月日 498 select year(getdate()) 499 select month(getdate()) 500 select day(getdate()) 501 --DATEDIFF ( datepart , startdate , enddate ) ：计算两个日期之间的差额。 datepart 为计量单位，可取值参考DateAdd。 502 select datediff(day,'2013-06-10',getdate()) 503 504 --DATEPART (datepart,date)：返回一个日期的特定部分 505 select datepart(year,getdate()) 506 --datename datepart 507 select datename(year,getdate()) --字符串 508 select datepart(year,getdate()) --整数 509 510 ----------------------------------------------类型转换函数-------------------------------------- 511 --问题 512 --select '平均分数' + 123 513 --select avg(english) from score 514 --select * from student 515 516 --CAST ( expression AS data_type) 517 select '平均分数' + cast(123 as varchar(10))--解决了字符串和整数连接的问题 518 select cast(34.89 as int)--没有进位 519 select cast (round(89.6,0) as int) --有进位 520 select cast(78.98534 as decimal(10,2))--有进位，decimal的第一个参数是最大有效位，第二个参数是小数位数 521 select cast(avg(english) as decimal(4,2)) from score---解决了小数点位数过长的问题， 522 --CONVERT ( data_type, expression) 523 select convert(int,56.87)--没有进位 524 select convert(decimal(4,2),56.86)--有进位 525 526 select convert(varchar(30),getdate(),20)--2013-06-15 14:01:39 527 select convert(varchar(20),getdate(),21) 528 select convert(varchar(30),getdate(),23)--国内标准年月日2013-06-15 529 select convert(varchar(10),getdate(),108)--国内标准时分秒14:00:32 530 531 select convert(varchar(10),getdate(),20)--通过截取字符串获得国内标准年月日 532 533 ----------------------空值处理函数------------------------------------ 534 --ISNULL(expression,value) ：如果expression不为空则返回expression，否则返回value。 535 --select * from score 536 select studentId,isnull(math,0) from score 537 538 select avg(english) from score 539 select sum(english)/count(sId) from score 540 select cast(avg(isnull(english,0)) as decimal(10,2)) from score------对于计算平均值很有用 541 542 543 ------------------------------------------------练习----------------------------------------------------- 544 545 ---------------练习1 未完成------------------------------------------------------------- 546 创建 MyCompany数据库 547 548 部门表 department 549 dId 550 dName 551 员工表 employee 552 eId 553 eName 554 eSex 555 eAge 556 eSalary 557 eDepId 558 559 建库 建表 建约束 添加测试数据 更新 和删除 560 561 562 563 1、查询所有员工 564 2、查询工资超过2000快钱的员工 565 3、查询最新进来的5个员工 566 4、查询员工的平均工资，平均工资 567 5、查询总共有多少员工 568 6、查询每个部门有多少员工 569 7、查询每个部门的平均工资 570 8、查询每个部门男员工的平均工资 571 9、查询平均工资超过2000的那些部门 572 10、查询员工工资（姓名，工资）的同时，同一结果集显示平均工资和最高工资 573 11、查询名字里包含'定,月'的这些员工，并对结果按照年龄排序 574 575 -------------------------------练习1结束--------------------------------------------------------------------------- 576 577 ---------------练习2未完成------------------------------------------------------------- 578 579 --创建一张表，记录电话呼叫员的工作流水，记录呼叫员编号、对方号码、通话开始时间、通话结束时间。 580 --建表、插数据等最后都自己写SQL语句。 581 --drop table CallRecords 582 --use MySchool 583 --go 584 --create table CallRecords 585 --( 586 -- cId int identity(1,1) primary key, 587 -- cNo int not null, 588 -- cCllNumber varchar(6), 589 -- cStartTime datetime, 590 -- cEndTime datetime 591 --) 592 use MySchool 593 go 594 CREATE TABLE [CallRecords] 595 ( 596 [Id] [int] NOT NULL identity(1,1), 597 [CallerNumber] [nvarchar](50), --三位数字 598 [TelNum] [varchar](50), 599 [StartDateTime] [datetime] NULL, 600 [EndDateTime] [datetime] NULL --结束时间要大于开始时间,默认当前时间 601 ) 602 603 --主键约束 604 alter table [CallRecords] 605 add constraint PK_CallRecords primary key(id) 606 607 --检查约束 608 alter table [CallRecords] 609 add constraint CK_CallRecords check(CallerNumber like '[0-9][0-9][0-9]') 610 611 alter table [CallRecords] 612 add constraint CK_CallRecords_EndDateTime check(EndDateTime > StartDateTime) 613 614 --默认约束 615 alter table [CallRecords] 616 add constraint DF_CallRecords default(getdate()) for EndDateTime 617 618 619 620 INSERT [dbo].[CallRecords] ([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('001', '0208888888', CAST(0x00009DAF00A4CB80 AS DateTime), CAST(0x00009DAF00A62E94 AS DateTime)); 621 INSERT [dbo].[CallRecords] ([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('001', '0208888888', CAST(0x00009DB000D63BC0 AS DateTime), CAST(0x00009DB000D68DC8 AS DateTime)); 622 INSERT [dbo].[CallRecords] ([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('001', '89898989', CAST(0x00009DB000E85C60 AS DateTime), CAST(0x00009DB000E92F50 AS DateTime)); 623 INSERT [dbo].[CallRecords] ([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('002', '98987676', CAST(0x00009DB2015BB7A0 AS DateTime), CAST(0x00009DB2015C4DA0 AS DateTime)); 624 INSERT [dbo].[CallRecords] ([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('002', '02188839389', CAST(0x00009DA4014C9C70 AS DateTime), CAST(0x00009DA4014E0308 AS DateTime)); 625 INSERT [dbo].[CallRecords] ([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('001', '767676766', CAST(0x00009DB400DAA0C0 AS DateTime), CAST(0x00009DB400DD5FE0 AS DateTime)); 626 INSERT [dbo].[CallRecords] ([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('003', '0227864656', CAST(0x00009DB200B9AB40 AS DateTime), CAST(0x00009DB200B9FC1C AS DateTime)); 627 INSERT [dbo].[CallRecords] ([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('003', '676765777', CAST(0x00009DB8014042B8 AS DateTime), CAST(0x00009DB80141804C AS DateTime)); 628 INSERT [dbo].[CallRecords] ([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('001', '89977653', CAST(0x00009D9A00FB9898 AS DateTime), CAST(0x00009D9A00FE6118 AS DateTime)); 629 INSERT [dbo].[CallRecords] ([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES ('004', '400400400', CAST(0x00009D9A00FB9898 AS DateTime), CAST(0x00009D9A00FE6118 AS DateTime)); 630 631 632 --要求： 633 --输出所有数据中通话时间最长的5条记录。orderby datediff 634 select top 5 datediff(second,[StartDateTime],[EndDateTime]), Id, CallerNumber, TelNum, StartDateTime, EndDateTime from CallRecords 635 order by datediff(second,[StartDateTime],[EndDateTime])desc 636 --输出所有数据中拨打长途号码（对方号码以0开头）的总时长。like、sum 637 --输出本月通话总时长最多的前三个呼叫员的编号。 638 -- 639 --输出本月拨打电话次数最多的前三个呼叫员的编号.group by,count(*) 640 641 642 643 ---------------练习2结束------------------------------------------------------------ 644 645 646 647 ----------------------------------------------sql基本命令结束--------------------------------------------------------------------- 648 649 650 651 652 653 654 655 656 ----------------------------------------------sql高级命令开始------------------------------------------------------------ 657 658 ----创建示例数据库demo 659 create database demo 660 use demo 661 go 662 create table [user] 663 ( 664 [uId] int identity(1,1) primary key, 665 [name] varchar(50), 666 [level] int --1骨灰2大虾3菜鸟 667 ) 668 insert into [user] (name,level) values('犀利哥',1) 669 insert into [user] (name,level) values('小月月',2) 670 insert into [user] (name,level) values('芙蓉姐姐',3) 671 672 673 ----------case end 相当于switch case 674 675 -----------then后面的返回值类型必须一致 676 677 --case end第一种用法，注意 字段level在case 后，when后面跟的是一个常量 678 select name, 679 case level 680 when 1 then '菜鸟' 681 when 2 then '骨灰' 682 else '大神' 683 end as '级别' 684 from [user] 685 686 use myschool 687 go 688 select * from score 689 --case end第二种用法，相当于多重if语句，注意case后面没有字段，when后面为一个表达式 690 select studentid, 691 case 692 when english>=90 then '优秀' 693 when english>=80 then '良好' 694 when english>=60 then '及格' 695 else '不及格' 696 end as '成绩等级' 697 from score 698 699 ----------练习1--------表中有A B C三列,用SQL语句实现：当A列大于B列时选择A列否则选择B列，当B列大于C列时选择B列否则选择C列。 700 select 701 case 702 when A>B then A 703 else B 704 end as 'AB列中的大值', 705 case 706 when B>C then B 707 else C 708 end as 'BC列中的大值' 709 from t 710 711 --------练习2------ 712 713 -- 单号 金额 714 -- Rk1 10 715 -- Rk2 20 716 -- Rk3 -30 717 -- Rk4 -10 718 -- 将上面的表输出为如下的格式： 719 -- 单号 收入 支出 720 -- Rk1 10 0 721 -- Rk2 20 0 722 -- Rk3 0 30 723 -- Rk4 0 10 724 use demo 725 go 726 --成绩示例表 727 create table test 728 ( 729 number varchar(10), 730 amount int 731 ) 732 insert into test(number,amount) values('RK1',10) 733 insert into test(number,amount) values('RK2',20) 734 insert into test(number,amount) values('RK3',-30) 735 insert into test(number,amount) values('RK4',-10) 736 737 select * from test 738 739 select number, 740 case 741 when amount>0 then amount 742 else 0 743 end as '收人', 744 case 745 when amount >0 then 0 746 else (0-amount) 747 end as '支出' 748 from test 749 750 751 -----------练习3---------- 752 753 -- 有一张表student0，记录学生成绩 754 -- name subject result 755 -- 张三 语文 80 756 -- 张三 数学 90 757 -- 张三 物理 85 758 -- 李四 语文 85 759 -- 李四 数学 92 760 -- 李四 物理 NULL 761 762 -- 要求输出下面的格式： 763 -- name 语文 数学 物理 764 -- ---------- ----------- ----------- ----------- 765 -- 李四 85 92 0 766 -- 张三 80 90 85 767 768 use demo 769 ---示例表 770 CREATE TABLE student0 (name nvarchar(10),subject nvarchar(10),result int) 771 INSERT INTO student0 VALUES ('张三','语文',80) 772 INSERT INTO student0 VALUES ('张三','数学',90) 773 INSERT INTO student0 VALUES ('张三','物理',85) 774 INSERT INTO student0 VALUES ('李四','语文',85) 775 INSERT INTO student0 VALUES ('李四','数学',92) 776 INSERT INTO student0 VALUES ('李四','物理',null) 777 778 select [name], 779 isnull(sum(case 780 when subject='语文' then result 781 end ),0)as '语文', 782 isnull(sum(case 783 when subject='数学' then result 784 end),0) as '数学', 785 isnull(sum(case 786 when subject='物理' then result 787 end ),0)as '物理' 788 from student0 789 group by [name] 790 791 792 -------------------------------------------------子查询---------------------------------------------------------- 793 794 795 --将一个查询语句做为一个结果集供其他SQL语句使用，就像使用普通的表一样，被当作结果集的查询语句被称为子查询 796 797 select * from student 798 select * from(select * from student where sage>20)as t 799 800 --要求在一个表格中查询出学生的英语最高成绩、最低成绩、平均成绩 801 select(select max(english) from score) as maxenglish,(select min(english) from score) as minenglish,(select avg(english) from score) as avgenglish 802 --只有返回且仅返回一行、一列数据的子查询才能当成单值子查询 803 --select(select * from score)--错误 804 --查询高二二班的所有学生 805 select * from student where sClassId in(select cId from class where cName='高二二班') 806 807 --查询高一一班和高二一班的所有学生 808 809 select * from student where sClassId in(select cId from class where cName='高二二班' or cName='高二一班') 810 811 --查询刘关张的成绩 812 select * from score where studentid in(select sid from student where sname='关羽' or sname='刘备' or sname='张飞') 813 814 --快速实现删除多个学生 815 delete from student where where sname='关羽' or sname='刘备' or sname='张飞' 816 817 --数据库中分页的实现 818 819 --取最近插入的1-3个学生 820 select top 3 * from student 821 where sid not in 822 (select top 0 sid from student order by sid desc) 823 order by sid desc 824 --取最近插入的第4-6个学生 825 select top 3 * from student 826 where sid not in 827 (select top 3 sid from student order by sid desc) 828 order by sid desc 829 830 --取最近插入的第7-9个学生 831 select top 3 * from student 832 where sid not in 833 (select top 6 sid from student order by sid desc) 834 order by sid desc 835 836 --取最近插入的第(3n-2)-3n个学生 837 select top 3 * from student 838 where sid not in 839 (select top 3(n-1) sid from student order by sid desc) 840 order by sid desc 841 842 ----------------------row_number()------sql 2005中的分页--------------------------------------------- 843 844 845 ----row_number()over (order by sid) as num 846 select * from (select row_number()over (order by sid) as num,* from student )as t 847 where num between 1 and 3 848 849 select * from (select row_number()over (order by sid) as num,* from student )as t 850 where num between 4and 6 851 852 select * from (select row_number()over (order by sid) as num,* from student )as t 853 where num between 7 and 9 854 855 select * from (select row_number()over (order by sid) as num,* from student )as t 856 where num between (3n-2) and 3n 857 858 -------------------------表连接------------------------------------------------ 859 --交叉连接，全连接 860 select * from student 861 cross join class 862 863 --左外连接 864 select * from student 865 left join class on sclassid=cid 866 867 --右外连接 868 select * from student 869 right join class on sclassid=cid 870 871 ---内连接，等值连接 872 select * from student 873 inner join class on sclassid=cid 874 875 --同上效果的写法，也是经常的写法 876 select * from student,class 877 where sclassid=cid 878 879 --查询所有学生的姓名、年龄及所在班级 880 select sname ,sage,cname from student 881 inner join class on sclassid=cid 882 883 --查询年龄超过20岁的学生的姓名、年龄及所在班级 884 select sname,sage,cname from student,class 885 where sclassid=cid and sage>20 886 887 --查询学生姓名、年龄、班级及成绩 888 select sname,sage,cname,english from student 889 inner join class on sclassid=cid 890 inner join score on student.sid=studentid 891 892 --查询所有学生(参加及未参加考试的都算)及成绩 893 894 select * from student 895 left join score on student.sid=studentid 896 897 --查询所有及格的学生姓名、年龄及成绩 898 select sname,sage,english from student 899 inner join score on student.sid=studentid 900 where english>60 901 902 --查询所有参加考试的(分数不为null)学生姓名、年龄及成绩,班级 903 904 select sname,sage,english,cname from student 905 inner join class on sclassid=cid 906 inner join score on student.sid=studentid 907 where english is not null 908 909 --查询所有学生(参加和未参加考试)的学生姓名、年龄、成绩，如果没有参加考试显示缺考,如果小于60分显示不及格 910 911 select sname,sage, 912 case 913 when english>=60 then cast( english as nvarchar(10)) 914 when english<60 then '不及格' 915 when english is null then '缺考' 916 end 917 from student 918 inner join score on student.sid=studentid 919 920 -----------------------------------视图-------------------------------------------------------- 921 -----系统视图 922 select * from sys.tables 923 select * from sys.objects 924 ----系统表 925 select * from sysobjects 926 927 928 --利用系统视图去做一些操作 929 if exists(select * from sys.databases where name='demo') 930 drop database demo 931 create database demo 932 go 933 use demo 934 go 935 if exists(select * from sys.objects where name='test') 936 drop table test 937 create table test 938 ( 939 tId int identity(1,1) primary key, 940 tName nvarchar(10) 941 ) 942 943 if exists(select * from sys.objects where name='UQ_test_tName') 944 alter table test drop constraint UQ_test_tName 945 alter table test 946 add constraint UQ_test_tName unique(tName) 947 948 949 -------创建视图 950 use MySchool 951 if exists(select * from sys.objects where name='v_Student') 952 drop view v_Student 953 go 954 create view v_Student 955 as 956 select sName,sAge,sSex from student 957 958 959 960 select * from v_Student 961 962 963 if exists(select * from sys.objects where name='v_Student1') 964 drop view v_Student1 965 go 966 create view v_Student1 967 as 968 select sName,sAge, 969 case 970 when english is null then '缺考' 971 when english < 60 then '不及格' 972 else convert(varchar,english) 973 end as '成绩' 974 from student 975 left join score on student.sId = studentId 976 977 978 979 select * from v_Student1 980 981 982 select * from view_2 where name='刘备' 983 984 985 -------sql中的局部变量 986 987 declare @ssex nvarchar(10),@sname nvarchar(10) 988 --set @ssex='男' ---只能为一个变量赋值 989 --select @sname='刘备',@ssex='男' ---可为多个变量赋值 990 select @sname=sname from student where sid=1 ----可以把sql查询的结果赋给变量 991 992 select @sname,@ssex 993 994 print @sname -----只能输出一个值 995 996 --全局变量 997 998 ---全局变量是系统定义的，只读的 999 1000 select @@version 1001 1002 select @@error 1003 1004 select @@identity 1005 1006 select @@LANGUAGE 1007 1008 select @@servername 1009 1010 select @@rowcount 1011 1012 1013 ------------------if--else 条件判断--------------------- 1014 1015 --计算平均分数并输出，如果平均分数超过60分输出成绩最高的三个学生的成绩，否则输出后三名的学生 1016 1017 declare @avg float 1018 select @avg=avg(english) from score 1019 if(@avg>=60) 1020 begin 1021 select top 3 * from score order by sid desc 1022 end 1023 else 1024 begin 1025 select top 3 * from score order by sid asc 1026 end 1027 1028 1029 --------------while----------- 1030 1031 --如果不及格的人超过半数(考试题出难了)，则给每个人增加2分 1032 declare @count int,@failcount int 1033 select @count=count(*) from score 1034 select @failcount=count(*) from score where english<60 1035 1036 while(@failcount>@count/2) 1037 begin 1038 update score set english=english+2 1039 select @failcount=count(*) from score where english<60 1040 end 1041 update score set english=100 where english>100 1042 1043 select * from score 1044 1045 1046 --把所有未及格的人的成绩都加及格 1047 declare @count int,@i int 1048 select @count=count(*) from score where english < 60 1049 set @i = 0 1050 while(@count > 0) 1051 begin 1052 set @i = @i + 1 1053 update score set english = english + 2 1054 select @count=count(*) from score where english < 60 1055 end 1056 update score set english = 100 where english > 100 1057 select * from score 1058 select @i 1059 1060 1061 ------------------------------事务-------------------------------------------- 1062 1063 --转账问题： 1064 -- 假定钱从A转到B，至少需要两步： 1065 -- A的资金减少 1066 -- 然后B的资金相应增加 1067 1068 ------示例表 1069 use demo 1070 go 1071 create table bank 1072 ( 1073 cId char(4) primary key, 1074 balance money, --余额 1075 ) 1076 1077 alter table bank 1078 add constraint CH_balance check(balance >=10) 1079 1080 go 1081 --delete from bank 1082 insert into bank values('0001',1000) 1083 insert into bank values('0002',10) 1084 1085 update bank set balance=balance-1000 where cid='0001' 1086 update bank set balance=balance + 1000 where cid='0002' 1087 --查看结果。 1088 SELECT * FROM bank 1089 1090 1091 ----指访问并可能更新数据库中各种数据项的一个程序执行单元(unit)--也就是由多个sql语句组成，必须作为一个整体执行 1092 --这些sql语句作为一个整体一起向系统提交，要么都执行、要么都不执行 1093 1094 1095 --事务 1096 declare @sumError int 1097 set @sumError = 0 1098 begin tran --transaction 1099 update bank set balance = balance + 1000 where cId = '0002' 1100 set @sumError = @sumError + @@error 1101 update bank set balance = balance - 1000 where cid='0001' 1102 set @sumError = @sumError + @@error 1103 --事务执行成功 提交事务 1104 if(@sumError = 0) 1105 commit tran 1106 --事务执行失败，回滚 1107 else 1108 rollback tran 1109 1110 select @sumError 1111 1112 1113 --查看结果。 1114 SELECT * FROM bank 1115 1116 1117 1118 ----------------存储过程---------------------------------- 1119 1120 --存储过程---就像数据库中运行方法(函数) 1121 1122 --系统存储过程 1123 1124 EXEC sp_databases 1125 EXEC sp_renamedb 'Northwind','Northwind1' 1126 EXEC sp_tables 1127 EXEC sp_columns stuInfo 1128 EXEC sp_help stuInfo 1129 EXEC sp_helpconstraint stuInfo 1130 EXEC sp_helpindex stuMarks 1131 EXEC sp_helptext 'view_stuInfo_stuMarks' 1132 EXEC sp_stored_procedures 1133 1134 ---分离数据库 1135 exec sp_detach_db test 1136 --附加数据库 1137 exec sp_attach_db @dbname='test',@filename1='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData est.mdf',@filename2='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData est_log.LDF' 1138 1139 1140 -----创建存储过程 1141 1142 -----逐步提分，使及格人数大于一半 1143 1144 if exists(select * from sys.objects where name='usp_upGrade') 1145 drop procedure usp_upGrade 1146 1147 go 1148 1149 create proc usp_upGrade 1150 --这里可以放置输入输出型参数 1151 as 1152 declare @count int,@failcount int 1153 select @count=count(*) from score 1154 select @failcount=count(*) from score where english<60 1155 while(@failcount>@count/2) 1156 begin 1157 update score set english=english+2 1158 select @failcount=count(*) from score where english<60 1159 end 1160 update score set english=100 where english>100 1161 1162 ---执行存储过程 1163 execute usp_upGrade 1164 1165 1166 use MySchool 1167 go 1168 select * from score 1169 1170 --带参数的存储过程 1171 --带默认值参数 1172 1173 -------找出英语成绩不及格的人数 1174 1175 if exists(select * from sys.objects where name='usp_GetFailEnglishNum') 1176 drop procedure usp_GetFailEnglishNum 1177 1178 go 1179 1180 create proc usp_GetFailEnglishNum 1181 @failEnglish float=60 ----带默认值的 输入型参数 1182 as 1183 declare @failcount int 1184 select @failcount=count(*) from score where english<@failEnglish 1185 print @failcount 1186 1187 --执行存储过程 1188 execute usp_GetFailEnglishNum----使用默认值 1189 1190 execute usp_GetFailEnglishNum 50----自己赋值 1191 execute usp_GetFailEnglishNum @failEnglish=50----另一种赋值方法 1192 1193 select * from score 1194 1195 1196 -----存储过程输出值 return 1197 if exists(select * from sys.objects where name='usp_GetFailEnglishNum') 1198 drop procedure usp_GetFailEnglishNum 1199 1200 go 1201 1202 create proc usp_GetFailEnglishNum 1203 @failEnglish float=60 ----带默认值的 输入型参数 1204 as 1205 declare @failcount int,@count int 1206 select @failcount=count(*) from score where english<@failEnglish 1207 print @failcount 1208 select @count=count(*) from score 1209 return @count 1210 1211 --调用有返回值的存储过程 1212 declare @count int 1213 execute @count=usp_GetFailEnglishNum 1214 print @count 1215 1216 1217 ------带输出参数的存储过程的创建 1218 1219 if exists(select * from sys.objects where name='usp_GetFailEnglishNum') 1220 drop procedure usp_GetFailEnglishNum 1221 1222 go 1223 1224 create proc usp_GetFailEnglishNum 1225 @failEnglish float=60,----带默认值的 输入型参数 1226 @countNum int output -----输出型参数 1227 as 1228 declare @failcount int,@count int 1229 select @failcount=count(*) from score where english<@failEnglish 1230 print @failcount 1231 select @count=count(*) from score 1232 set @countNum= @count --给输出型参数赋值 1233 1234 --调用有返回值的存储过程 1235 declare @count int 1236 execute usp_GetFailEnglishNum 60, @count output 1237 print @count 1238 1239 ------------------分页存储过程--------------------- 1240 1241 ---要输入要显示的页码和每页显示的条数。输出总共的页码数 1242 use MySchool 1243 go 1244 if exists(select * from sys.objects where name='usp_GetPageData') 1245 drop procedure usp_GetPageData 1246 go 1247 1248 create proc usp_GetPageData 1249 1250 @pageCount int output,----输出型参数 总共的页码数目 1251 @pageIndex int =1 , ---输入型参数，当前要显示的页码 1252 @pageSize int =20 ----输入型参数，每页显示的记录数目 1253 1254 as 1255 declare @count int -----设置变量用于接收总的记录数， 1256 select * from(select row_number() over(order by sid) as num ,* from student) as t 1257 where num between @pageSize*@pageIndex-@pageSize+1 and @pageSize*@pageIndex 1258 order by sid desc 1259 select @count=count(*) from student 1260 1261 ---求总共有多少页 1262 set @pageCount=ceiling((@count/convert(float,@pageSize))) --select ceiling(7/3.0) 1263 1264 --执行存储过程 1265 1266 declare @pageCount int 1267 execute usp_GetPageData @pageCount output,2,3 1268 select @pageCount as '总页码数目' 1269 1270 select * from student 1271 1272 1273 1274 --------------实现登陆的存储过程---------不是自己写的---------------- 1275 1276 if exists(select * from sys.objects where name='usp_Login') 1277 drop proc usp_Login 1278 go 1279 create proc usp_Login 1280 @name varchar(10), 1281 @pwd varchar(10), 1282 @isLogin int output --1 登陆成功 2用户名错误 3密码错误 4密码错误超过3次 1283 as 1284 declare @times int --错误次数 1285 --根据用户名是否存在 1286 if exists(select * from [user] where uUserName=@name) 1287 begin 1288 select @times = uTimes from [user] where uUserName=@name 1289 if(@times = 3) 1290 --密码错误3次 1291 set @isLogin=4 1292 else 1293 begin 1294 if exists(select * from [user] where uUserName=@name and uPwd=@pwd) 1295 begin 1296 --用户名密码正确 登陆成功 1297 set @isLogin=1 1298 update [user] set uTimes=0 where uUserName=@name 1299 end 1300 else 1301 begin 1302 --密码错误 1303 set @isLogin=3 1304 update [user] set uTimes=uTimes + 1 where uUserName=@name 1305 end 1306 end 1307 end 1308 else 1309 --用户名不存在 1310 set @isLogin= 2 1311 1312 1313 1314 1315 -----------------------------触发器------------------------------------------------------ 1316 1317 1318 --触发器是一种特殊类型的存储过程 1319 --一般的存储过程通过存储过程名称被直接调用，而触发器主要是通过事件进行触发而被执行 1320 --常见的触发器有三种：分别应用于Insert , Update , Delete 事件 1321 1322 use MySchool 1323 go 1324 if exists(select * from sys.objects where name='tr_insertStudent') 1325 drop trigger tr_insertStudent 1326 go 1327 1328 create trigger tr_insertStudent 1329 1330 on score for insert 1331 1332 as 1333 begin 1334 declare @stuId int,@sid int 1335 select @sid=sid,@stuId=studentId from inserted ---从插入的临时表中获取数据 1336 if not exists(select * from student where sid=@stuId) 1337 delete from score where sid=@sid 1338 else 1339 print '插入成功' 1340 end 1341 1342 select * from student 1343 select * from score 1344 insert into score values(9,33,66)