搜索
第一章：--创建数据库createdatabasemydbon(name=mydb_Data,filename='D:\DBFile\mydb_Data.mdf',size=2MB,maxsize=10MB,filegrowth=1MB)logon(name=mydb_Log,filename='D:\DBFile\mydb_Log.ldf',size=1MB,maxsize=10MB,filegrowth=10%)--查看数据库信息execsp_helpdbmydbusemydb--修改信息alterdatabasemydbmodifyfile(name=mydb_Data,size=3MB)--修改数据库名称alterdatabasefirstdbmodifyname=seconddbexecsp_renamedb'seconddb','firstdb'--删除数据库dropdatabasefirstdbcreatetablettt(idint)第二章：--打开自己的数据库usemydb--创建简单表createtabletb1(sidint,snamevarchar(20),sexchar(2))select*fromtb1--自动获取列值的表--default约束createtabletb2(sidint,snamevarchar(20),sexchar(2)default'男')select*fromtb2insertintotb2values(1,'张三','男')insertintotb2(sid,sname)values(2,'李四')insertintotb2values(3,'王五',null)--自动编号createtabletb3(sidintidentity(1000,10),snamevarchar(20))select*fromtb3insertintotb3values('张三')insertintotb3values('李四')insertintotb3values('王五')deletefromtb3wheresid=1020insertintotb3values('赵六')--主键约束createtablestudents(snochar(4)primarykey,snamevarchar(20)notnull,ageint)select*fromstudentsinsertintostudentsvalues('s001','张三',21)insertintostudentsvalues('s001','张三',21)--errorinsertintostudents(sname,age)values('张三',21)--errorinsertintostudents(sno,age)values('s002',22)--errorinsertintostudentsvalues('s002','李四',21)insertintostudents(sno,sname)values('s003','王五')--课程表createtablecourse(cnoint,cnamevarchar(30),constraintpk_courseprimarykey(cno))select*fromcourseinsertintocoursevalues(1,'C#基础')insertintocoursevalues(1,'SQLServer')insertintocoursevalues(2,'SQLServer')insertintocourse(cname)values('WindowsForm')--成绩表(学号，课程号，成绩)createtablescore(snochar(4)referencesstudents(sno),cnoint,gradeint,Constraintfk_courseforeignkey(cno)referencescourse(cno),Constraintpk_scoreprimarykey(sno,cno))select*fromstudentsselect*fromcourseselect*fromscoreinsertintoscorevalues('ssss',3,76)--errorinsertintoscorevalues('s001',3,76)--errorinsertintoscorevalues('s001',1,76)insertintoscorevalues('s002',1,76)insertintoscorevalues('s002',null,75)--唯一性约束createtabletb4(tidintunique,tnamevarchar(10))select*fromtb4insertintotb4values(1,'sa')insertintotb4values(1,'sa')--errorinsertintotb4values(null,'pa')insertintotb4values('','da')--check检查约束createtabletest(sidintidentity(1,1)primarykey,snamevarchar(20),markintcheck(mark=0andmark=100),zipcodechar(6),constraintck_zipcheck(zipcodelike'[0-9][0-9][0-9][0-9][0-9][0-9]'))select*fromtestinsertintotestvalues('张三',-33,'a12435')--errorinsertintotestvalues('张三',33,'a12435')--errorinsertintotestvalues('张三',33,'712435')insertintotest(sname)values('李四')--test添加列agealtertabletestaddageintcheck(age=18andage=30)insertintotest(sname,age)values('李四',31)--score表添加检查约束altertablescoreaddConstraintck_scorecheck(grade=0andgrade=100)--删除test表中age列altertabletestdropcolumnage--先删除约束altertabletestdropCK__test__age__29572725execsp_columnstest--修改test表中sname的长度altertabletestaltercolumnsnamevarchar(30)--修改列名execsp_rename'test.sname','姓名','column'--删除testdroptabletestselect*fromtb4insertintotb4(tname,tid)values('ss',3)insertintotb4values(2,'dd')insertintotb4(tid)values(4)insertintotb4select5,'aa'select6,'cc'updatetb4settname='ww'updatetb4settid=10,tname='sa'wheretid=0deletefromtb4wheretid=10deletefromtb4第三章：--指定列名selectsname,sno,classfromstu_info--显示所有的列select*fromstu_info--显示年龄列(使用表达式)selectsname,sno,sex,2011-birthfromstu_info--列别名selectsnameas姓名,年龄=2011-birth,class班级fromstu_info--统计人数selectcount(class)总人数fromstu_infoselectcount(*)总人数fromstu_infoselect*fromgradeselectcount(id)fromgrade--统计有成绩的学生人数selectcount(distinctsno)fromgrade--统计成绩列的总和selectsum(mark)总成绩fromgrade--统计最高分selectmax(mark)fromgradeselectmax(sname)fromstu_infousenorthwindselect*fromordersselectmax(orderdate)fromordersusestudentselect*fromgradeselectcount(distinctcno)fromgrade--最低成绩selectmin(mark)fromgrade--求平均分selectavg(mark)fromgradeselectsum(mark)/count(*)fromgradeselectcount(sno),snofromgrade--errorselectcount(*)成绩个数,总分=sum(mark),最高分=max(mark),min(mark)最低分,平均成绩=avg(mark)fromgradeselect*fromgradewheremark=60select*fromstu_infowheresname='胡锦涛'selectsno,sname,2011-birth年龄fromstu_infowhere2011-birth23select*fromgradewheremarkbetween60and80select*fromgradewheremarknotbetween60and80usenorthwindselect*fromorderswhereorderdatebetween'1996-7-1'and'1996-7-31'usestudentselect*fromgradewheremarkin(60,70,80,90,100)select*fromstu_infowheredeptin('计算机系','经管系')selectsno,sname,2011-birthfromstu_infowhere2011-birthin(24,26)select*fromstu_infowheresnamelike'胡%'select*fromstu_infowheresnamelike'胡_'select*fromstu_infowheresnamelike'胡__'select*fromstu_infowheresnamelike'%胡%'select*fromstu_infowhereclasslike'0[5,6]%'select*fromcoursewherecnamelike'DB$_%'ESCAPE'$'select*fromstu_infoWHEREclassisnotnull--60-80分的成绩select*fromgradewheremark=60andmark=80--计算机系，经管系的学生select*fromstu_infowheredept='计算机系'ordept='经管系'select*fromstu_infowheredept='经管系'orsex='女'select*fromstu_infowheredept='经管系'andsex='女'ordept='计算机系'select*fromstu_infowheredept='经管系'and(sex='女'ordept='计算机系')select*fromstu_infowheredept='计算机系'ordept='经管系'andsex='女'select*fromstu_infowhere(dept='计算机系'ord