(中文)第二次：数据库基本管理和T-SQL语句.ppt

数据库基本管理和T-SQL语句,第一节目标,了解T-SQL语句分类掌握用CREATE语句创建数据库和表掌握用ALTER语句修改数据库和表,T-SQL语言分类,数据控制语言(DCL)1.Revoke（撤消）2.deny（拒绝）3.grant(同意、授权）,数据定义语言(DDL):1.create(创建)2.alter(修改)3.drop(删除)(针对对象),数据操作语言(DML)1.insert(插入)2.update(更新)3.delete(删除)(针对对象的内容)数据查询语言(DQL)1.select（查询）,SQLSERVER中的数据类型(p70),数据库相关文件,相关文件：主数据文件：一个数据库有且只有一个.mdf-主数据库文件辅助数据文件:根据需要自由选择，当数据库很大时，可以选择多个.ndf-辅数据库文件日志文件:用于恢复数据库所需要的事务日志信息.ldf-日志数据库文件,1.整数型,Bigint:8个字节，范围：-263263-1精度:19Int:4个字节，范围：-231231-1精度：10Smallint:2个字节，范围：-215215-1精度：5Tinyint:1个字节，范围：0255精度：3,2.浮点数据类型,numeric：mumeric(5,3)共5位数，其中3位小数decimal：decimal(15,5)共15位数，其中5位小数可用在含Identity关键字的列Real：可以存储正的或者负的十进制数值，最大可以有7位精确位数。Float：可精确到第15位小数，其范围从-1.79E-308到1.79E+308。,3.货币数据类型,Money：-263263-1，精确小数后4位；Smallmoney：-231231-1精确小数后4位；,5.字符数据类型,Char：其定义形式为char（n），每个字符和符号占用一个字节的存储空间。Varchar：其定义形式为varchar（n）。用char数据类型可以存储长达255个字符的可变长度字符串。Nchar：其定义形式为nchar（n）。Nvarchar：其定义形式为nvarchar（n）。,语法格式：createdatabase数据库名onprimary(Name=逻辑名，Filename=文件的存放路径，Size=数据文件的初始大小，Maxsize=数据文件的最大大小，Filegrowth=文件的增长方式)Logon(Name=逻辑名，Filename=文件的存放路径，Size=数据文件的初始大小，Maxsize=数据文件的最大大小，Filegrowth=文件的增长方式)go,用Sql语句创建数据库,创建一个名为student的数据库，其初始值大小为5MB，最大大小为50MB，允许数据库自动增长，增长方式是按10%比例增长；日志文件初始为2MB，最大可增长到5MB，按1MB增长。,创建一个student数据库案例1,createdatabasestudenton(name=xscj_data,filename=e:n207006xscj.mdf,size=5MB,maxsize=50MB,filegrowth=10%)logon(name=xscj_Log,filename=e:n207006xscj_Log.ldf,size=2MB,maxsize=5MB,filegrowth=1MB),创建一个student数据库,创建一个数据库案例2,创建一个名为TT的数据库，它有3个数据文件，其中主数据文件为100MB，最大大小为200MB，按20MB增长；1个辅数据文件为20MB，最大大小不限，按10%增长。它还有1个日志文件，大小为50MB，最大大小为100MB，按10MB增长。,CreatedatabasettOnPrimary(name=tt_data1,filename=c:datatt_data1.mdf,size=100MB,maxsize=200MB,filegrowth=20MB),(name=tt_data2,filename=c:datatt_data2.ndf,size=20MB,maxsize=unlimited,filegrowth=10%)Logon(name=tt_log1,filename=e:datatt_log1.ldf,size=50MB,maxsize=100MB,filegrowth=10MB)go,修改数据库,利用ALTERDATABASE命令可对对数据库进行一下修改:修改数据库名称;数据文件,日志文件的增加或删除;数据文件,日志文件的大小和增长方式;,修改数据库名,语法：alterdatabase原数据库名modifyname=新数据库名【例】将数据库xscj2的名字改为xsalterdatabasexscj2modifyname=xs,查询数据库信息:sp_helpdb数据库名,添加辅助文件,alterdatabase数据库名addfile(name=逻辑名,filename=文件存放的路径,size=初始大小，maxsixe=最大大小,filegrowth=增长方式),修改数据库大小及增长方式等,语法：alterdatabase数据库名modifyfile(name=逻辑名,size=修改后的大小.)/用于修改MDF,NDF,LDF文件大小,修改后的大小必须大于原大小.若超过原maxsize,则maxsize会更新为修改后的大小.说明:属性name必须有,其他属性只是在要修改的时候才出现,删除数据库,语法：Dropdatabase数据库名【例】删除数据库xscj2dropdatabasexscj2,文件的删除,删除文件:ALTERDATABASE数据库名REMOVEFILE文件名注意:不能删除主数据文件和主日志文件,表的定义,表是sql中最重要的数据对象，它是用来存储和操作数据的一种逻辑结构.表结构：每个表都具有一定的结构，即组成表的各列名称及数据类型。记录：每个表包含若干行，每一行称为一个记录。字段：每个记录由若干个数据项组成，将组成记录的数据项称为字段关键字：在学生表中，若不加以限制，每个记录有可能相同，但学号字段的值一定不同，则通过学号将数据区分出来。,表的基本特性,创建表时必须指定表名、列名、以及数据类型。对给定的表来说，列名必须唯一，但是，同一个数据库中不同表的列的名称可以相同。必须为每一列都指定数据类型。以下是在sqlserver中创建表的极限：1)每个数据库可以有20亿个表2)每个表有1024个列3)每个列可以有8064字节,表的标识列,数据库设计理论的基本原则是：每一个表应该有一个可以唯一标识一条纪录的关键字，它可以是一列，也可以是一列的集合,创建数据库表,1.用企业管理器创建表2.用SQL语句创建表,用SQL语句创建表,use数据库名createtable表名(字段1数据类型identity(标示种子，递增量)primarykey,字段2数据类型,字段3数据类型,字段4数据类型,字段n数据类型)GO注：n最大可等于1024,使用create创建表(例3),现在已经创建了数据库XSCJ，现在该数据库中需创建学生情况表XS，创建表XS的T-SQL语句如下：usexscjcreatetablexs(学号intidentity(1,1),姓名varchar(20),专业名varchar(20),性别varchar(2),出生时间datetime,总学分int,备注text)分析：首先使用USEXSCJ将数据库XSCJ指定为当前数据库，然后使用CREATETABLE语句在数据库XSCJ中创建表XS。,表的修改,对已存在的表的修改包括:修改表名增加列删除列（注意:删除一个列以前,必须先删除基于该列的索引和约束!而且,被删除的列是不能恢复的）修改列的属性(列名,数据类型,是否为空值),修改表名,语法结构:Execsp_rename原表名,新表名注意:对于已经存在的表,当表名改变后,与此相关的某些对象,如视图,以及通过表名与表相关的存储过程将无效.故一般不要修改表名!,添加,删除列,语法：在表中添加一列use数据库名altertable表名add字段名数据类型go删除表中的一列use数据库名altertable表名dropcolumn字段名,go,修改列的属性,语法结构：修改数据类型：Altertable表名Altercolumn列名新数据类型注意：具有text,ntext,image,timestamp数据类型的列，计算列，全局标识列不能修改,修改数据类型或长度,语法：修改字符数据类型的长度（varchar）use数据库名altertable表名altercolumn字段名char(修改后的长度)go改变字段的数据类型use数据库名altertablexsaltercolumn字段名修改后的数据类型go,修改列名：sp_renameexecsp_rename表名.列名,修改列名,删除表,语法：droptable表名【例】删除xs表：droptablexs,第二节课目标,掌握数据操作语言（插入，更新，删除，查询）,数据操作语言DML,数据操纵语言（DML）主要语句及功能,1.使用insert语句向表插入数据,往表中插入数据：insertinto表名(字段1,字段2,字段3,.字段n)values(值1,值2,值3,.值n)注：n最大可等于1024注意：从源表搜索出来的要插入到目标表中的字段，要与目标表中已经存在的字段在数目和数据类型上要一致,2.使用update修改记录,语法格式：use数据库update表名set字段1=修改后的值，字段2=修改后的值.where条件注：一次可以修改多列,使用update修改记录,【例】将xscj数据库的xs表中学号为001110的学生的备注列值改为“三好生”，使用如下的T-SQL语句：usexscjupdatexsset备注=”三好生”where学号=001110【例】将xs表中的所有学生的总学分都增加10。updatexsset总学分=总学分+10说明：若UPDATE语句中未使用WHERE子句限定范围，UPDATE语句将更新表中的所有行。,3.使用删除语句delete删除记录,语法格式：从表中删除一条记录：deletefrom表名where条件删除表中所有记录，保存表的架构：detelefrom表名,语法格式：truncatetable表名删除记录语句truncate：删除表中的所有行。truncatetable在功能上与不带WHERE子句的delete语句相同：二者均删除表中的全部行。但truncatetable比delete速度快，且使用的系统和事务日志资源少。【例】：删除xs表里所有记录truncatetablexs,4.使用删除语句truncate删除表,【例】查询学生表（xs）中的所有信息所有列用*代替select*fromxs当在select语句指定列的位置上使用*号时，表示选择表的所有列。,5.Select基本语句,【例】查询课程表课程号、课程名、学分多列用逗号“，”分隔符select课程号,课程名,学分fromkc,【例】从学生表(xs)中查询所有学生的学号单列select学号fromxs,基本语法：select列名from表名,带条件的查询,【例】查询xs表中姓名为李明的相关信息用where指明条件select*fromxswhere姓名=李明,【例】查询xs表中出生日期在1978-3-26以后的学生select姓名,出生日期fromxswhere出生日期1989-1-3,注:字符型或日期型的数据要用单引号引起来,And,or,【例】查询xs表中学号为1101且姓名为李明的相关信息select*fromxswhere姓名=李明and学号=1101,【例】查询xs表中姓名为李明或者学号为1101的相关信息select*fromxswhere姓名=李明or学号=1101,betweenand在范围之内,【例】查找总学分为60和70之间的学生所有信息select*fromxswhere总学分between60and70-(大于60小于70),【例】查找出生日期不在1980-8-26到1979-1-29的学生姓名select姓名,出生日期fromxswhere出生日期notbetween1980-8-26and1979-1-29*注意时间的用法,在使用where子句进行查询时若条件表达式中出现若干条件相同的情况就会使表达式显得冗长不便于用户使用这时可用in关键字来简化,【例】查找分数为70和80的所有学生信息usexscjselect*fromxswhere总学分in(70,80),In=or,orderby排序:asc升；desc降,【例】查找学生的总学分以升序排列，出生日期以降序排列的学生姓名和学号usexscjselect姓名,出生日期,总学分,学号fromxsorderby总学分asc,出生日期desc*以第一列为主序，再在第一列相等的基础上再对第二列排序；asc默认，可省略,Distinct关键字,Distinct:从返回的结果数据集合中删除重复的行【例】查询xscj数据库中xs表中的总学分,但是不能有重复的usexscjselectdistinct总学分fromxsorderby总学分go,TOP关键字,top:关键字用于指定只返回前面一定数量的数据.topn:表示返回最前面的n行.topnpercent:用百分比表示返回的行数【例】查询在xscj库中的xs表中总学分最高的前5项的学生姓名usexscjselecttop5总学分,姓名fromxsorderby总学分ascgo,Top.withties指定返回并列前N条记录,usenorthwindselecttop5withtiesorderid,productid,quantityfromorderdetailsorderbyquantitydescgo*withties必须与orderby连用,like子句与通配符配合使用.Sqlserver提供种通配符1.%:表示任意字符2._:表示单个任意字符3.:表示方括号里列出的任意一个字符4.:表示任意一个没有在方括号里列出的字符,使用like子句进行模糊查询,基本语法:select字段名from目标