小学数学人教2011课标版一年级绘本教学找规律.doc

-SQL基本命令开始-切换数据库use mastergo -数据库操作-删除数据库-drop DataBase MySchool- 创建数据库create database MySchoolon( name=MySchool, -文件的逻辑名 filename=d:databaseMySchool.mdf, -文件的保存路径，database文件夹要先存在 size=3, -文件的初始大小 maxsize=100, -文件的最大值 filegrowth=1 -文件的增长比例，还可以filegrowth=10%)-日志文件log on( name=MySchool_log, -文件的逻辑名 filename=d:databaseMySchool_ldf.ldf, -文件的保存路径，database文件夹要先存在 size=3, -文件的初始大小 maxsize=100, -文件的最大值 filegrowth=10% -文件的增长比例，还可以filegrowth=10%)go-修改数据库-alter database MySchool-数据库操作结束-表操作-注意，当删除表时，如果表上有外键约束，应注意删除顺序，还有就是级联删除，不再累赘-切换数据库use MySchoolgo-删除表-drop table Class-创建表 Classcreate table Class( cId int identity(1,1) primary key,-设置cId为自动增长，初始值为1，增长量为1,并且为主键 cName nvarchar(10) not null, cDescription nvarchar(200)go-删除表Student-drop table Student-创建表create table Student( sId int identity(1,1) primary key,-设置sId为自动增长，初始值为1，增长量为1,并且为主键 sName nvarchar(10) not null,-设置姓名不能为空 sSex nchar(1) , sAge int,- check(sAge18 and sAge=30 and tAge= 18) constraint DF_Student0_sAge default(18), sClassId int constraint FK_Student0_sClassId foreign key (sClassId) references Class(cId)alter table Student0add sSex nchar(1)alter table Student0drop column sSex-约束的简单写法结束-插入测试数据开始-insert into Class (cName,cDescription) values (高一一班,快班)insert into Class (cName,cDescription) values (高一二班,中班)insert into Class (cName,cDescription) values (高一三班,慢班)insert into Class (cName,cDescription) values (高二一班,快班)insert into Class (cName,cDescription) values (高二二班,中班)insert into Class (cName,cDescription) values (高二三班,慢班)insert into Student (sClassId,sName,sAge,sSex,sNo,sBirthday) values (1,刘备,20,男,123456789012345678,1987-5-6)insert into Student (sClassId,sName,sAge,sSex,sNo,sBirthday) values (1,关羽,19,男,123456789012345552,1988-8-6)insert into Student (sClassId,sName,sAge,sSex,sNo,sBirthday) values (1,张两飞,19,男,123456789068745672,1989-5-19)insert into Student (sClassId,sName,sAge,sSex,sNo,sBirthday) values (4,曹操,22,男,123456789012345673,1985-12-6)insert into Student (sClassId,sName,sAge,sSex,sNo,sBirthday) values (4,夏侯惇,22,男,123456789012345674,1985-3-6)insert into Student (sClassId,sName,sAge,sSex,sNo,sBirthday) values (4,华佗,50,男,123456789012356574,1957-1-16)insert into Student (sClassId,sName,sAge,sSex,sNo,sBirthday) values (4,甄姬,52,女,12345678901234565,1989-8-8)insert into Score (studentId,english) values(1,90)insert into Score (studentId,english) values(2,90)insert into Score (studentId,english) values(3,59)insert into Score (studentId,english) values(4,100)insert into Score (studentId,english) values(5,60)insert into Score (studentId,english) values(8,0)insert into Score (studentId,english) values(7,80)-插入测试数据结束-数据的检索-简单的数据检索-select * from Studentselect * from Scoreselect * from Class-只检索需要的列 select sName from studentselect sName as 姓名,sAge as 年龄 from student-使用where检索符合条件的数据select sName 姓名 from student where sSex=男-检索不与任何表关联的数据select 1 select getdate() as 当前日期-Top 获取前几条数据select top 2 * from studentselect top 2 sName from student-使用百分号,不够一,进一,没有舍select top 20 percent * from student-Distinct 去除重复数据,是针对所有被查询的列而言,不是单个列select distinct * from studentselect distinct sName from student-聚合函数-select max(sId) from studentselect min(sId)from studentselect avg(math) from score-不会计算值为null 的列select sum(math) from scoreselect count(*) from scoreselect sum(math)/count(*) from scoreselect max(sBirthday),min(sBirthday) from student where sSex=男select * from Studentselect * from Scoreselect * from Class-带条件的查询-Select fromwhere -查询没有及格的学生的学号select studentId from score where english=20 and sAge80order by english desc,math desc-group by 数据分组-按照班级进行分组统计各个班级的人数select cName,count(*)from classgroup by cName-GROUP BY子句必须放到WHERE语句的之后 select sSex,count(*) from studentwhere sAge18group by sSex-聚合函数不能出现在where子句中select sSex,count(*) from studentwhere sAge18 and avg(sAge)19group by sSex-没有出现在GROUP BY子句中的列是不能放到SELECT语句后的列名列表中的 （聚合函数中除外）-select sSex,count(*), avg(sAge),sName from student -错误，sName不能放select在列表中select sSex,count(*), avg(sAge) from studentwhere sAge18group by sSex-having 对分组后的内容进行条件选择-在Where中不能使用聚合函数，Having中可以，Having要位于Group By之后,而且having中的列要在select中存在 -错误,sAge列在select中不存在,而且它也不是聚合函数-select sSex,count(*), avg(sAge) from student-group by sSex-having sAge18 and avg(sAge)19select sSex,count(*), avg(sAge) from studentgroup by sSexhaving avg(sAge)19-练习-求男生和女生分别有多少人select sSex,count(*) from studentgroup by sSex-每个班中的男同学的平均年龄-select * from studentselect count(*) , avg(sAge),sClassId from studentwhere sSex=男group by sClassId-求平均年龄小于22的那些班select sClassId from studentgroup by sClassIdhaving avg(sAge) StartDateTime)-默认约束alter table CallRecordsadd constraint DF_CallRecords default(getdate() for EndDateTimeINSERT dbo.CallRecords (CallerNumber, TelNum, StartDateTime, EndDateTime) VALUES (001, 0208888888, CAST(0x00009DAF00A4CB80 AS DateTime), CAST(0x00009DAF00A62E94 AS DateTime);INSERT dbo.CallRecords (CallerNumber, TelNum, StartDateTime, EndDateTime) VALUES (001, 0208888888, CAST(0x00009DB000D63BC0 AS DateTime), CAST(0x00009DB000D68DC8 AS DateTime);INSERT dbo.CallRecords (CallerNumber, TelNum, StartDateTime, EndDateTime) VALUES (001, 89898989, CAST(0x00009DB000E85C60 AS DateTime), CAST(0x00009DB000E92F50 AS DateTime);INSERT dbo.CallRecords (CallerNumber, TelNum, StartDateTime, EndDateTime) VALUES (002, 98987676, CAST(0x00009DB2015BB7A0 AS DateTime), CAST(0x00009DB2015C4DA0 AS DateTime);INSERT dbo.CallRecords (CallerNumber, TelNum, StartDateTime, EndDateTime) VALUES (002, 02188839389, CAST(0x00009DA4014C9C70 AS DateTime), CAST(0x00009DA4014E0308 AS DateTime);INSERT dbo.CallRecords (CallerNumber, TelNum, StartDateTime, EndDateTime) VALUES