(中文)第二次：数据库基本管理和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（查询）,SQL SERVER中的数据类型(p70),数据库相关文件,相关文件 ： 主数据文件： 一个数据库有且只有一个 .mdf -主数据库文件 辅助数据文件: 根据需要自由选择，当数据库很大时，可以选择多个 .ndf -辅数据库文件 日志文件: 用于恢复数据库所需要的事务日志信息 .ldf -日志数据库文件,1.整数型,Bigint:8个字节，范围：-263263-1 精度:19 Int :4个字节，范围：-231231-1精度：10 Smallint:2个字节，范围：-215215-1精度：5 Tinyint :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）。,语法格式：create database 数据库名 on primary( Name=逻辑名， Filename=文件的存放路径， Size=数据文件的初始大小， Maxsize=数据文件的最大大小， Filegrowth=文件的增长方式 ) Log on( Name=逻辑名， Filename=文件的存放路径， Size=数据文件的初始大小， Maxsize=数据文件的最大大小， Filegrowth=文件的增长方式 )go,用Sql语句创建数据库,创建一个名为student的数据库，其初始值大小为5MB，最大大小为50MB，允许数据库自动增长，增长方式是按10%比例增长；日志文件初始为2MB，最大可增长到5MB，按1MB增长。,创建一个student数据库案例1,create database student on (name=xscj_data, filename=e:n207006xscj.mdf, size=5MB, maxsize=50MB, filegrowth=10%) log on (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增长。,Create database tt On Primary (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%) Log on (name=tt_log1 ,filename=e:datatt_log1.ldf,size=50MB,maxsize=100MB,filegrowth=10MB) go,修改数据库,利用ALTER DATABASE命令可对对数据库进行一下修改: 修改数据库名称; 数据文件,日志文件的增加或删除; 数据文件,日志文件的大小和增长方式;,修改数据库名,语法：alter database 原数据库名 modify name=新数据库名 【例】将数据库xscj2的名字改为xs alter database xscj2 modify name= xs,查询数据库信息:sp_helpdb 数据库名,添加辅助文件,alter database 数据库名 add file ( name=逻辑名, filename=文件存放的路径, size=初始大小， maxsixe=最大大小, filegrowth=增长方式 ),修改数据库大小及增长方式等,语法： alter database 数据库名 modify file ( name=逻辑名, size=修改后的大小. ) /用于修改MDF,NDF,LDF文件大小,修改后的大小必须大于原大小.若超过原maxsize,则maxsize会更新为修改后的大小. 说明:属性name必须有,其他属性只是在要修改的时候才出现,删除数据库,语法： Drop database 数据库名 【例】删除数据库xscj2 drop database xscj2,文件的删除,删除文件: ALTER DATABASE 数据库名 REMOVE FILE 文件名 注意:不能删除主数据文件和主日志文件,表的定义,表是sql中最重要的数据对象，它是用来存储和操作数据的一种逻辑结构. 表结构：每个表都具有一定的结构，即组成表的各列名称及数据类型。 记录：每个表包含若干行，每一行称为一个记录。 字段：每个记录由若干个数据项组成，将组成记录的数据项称为字段 关键字： 在学生表中，若不加以限制，每个记录有可能相同，但学号字段的值一定不同，则通过学号将数据区分出来。,表的基本特性,创建表时必须指定表名、列名、以及数据类型。对给定的表来说，列名必须唯一，但是，同一个数据库中不同表的列的名称可以相同。必须为每一列都指定数据类型。 以下是在sql server中创建表的极限： 1)每个数据库可以有20亿个表 2)每个表有1024个列 3)每个列可以有8064字节,表的标识列,数据库设计理论的基本原则是：每一个表应该有一个可以唯一标识一条纪录的关键字，它可以是一列，也可以是一列的集合,创建数据库表,1.用企业管理器创建表 2.用SQL语句创建表,用SQL语句创建表,use 数据库名 create table 表名 ( 字段1 数据类型 identity (标示种子，递增量) primary key, 字段2 数据类型, 字段3 数据类型, 字段4 数据类型, 字段n 数据类型 ) GO 注：n最大可等于1024,使用create创建表(例3),现在已经创建了数据库XSCJ，现在该数据库中需创建学生情况表XS，创建表XS的T-SQL语句如下： use xscj create table xs ( 学 号 int identity(1,1), 姓 名 varchar(20), 专 业 名 varchar(20), 性 别 varchar(2), 出 生 时 间 datetime, 总 学 分 int, 备 注 text ) 分析：首先使用USE XSCJ将数据库XSCJ指定为当前数据库，然后使用CREATE TABLE语句在数据库XSCJ中创建表XS。,表的修改,对已存在的表的修改包括: 修改表名 增加列 删除列（注意:删除一个列以前,必须先删除基于该列的索引和约束!而且,被删除的列是不能恢复的） 修改列的属性(列名,数据类型,是否为空值),修改表名,语法结构: Exec sp_rename 原表名,新表名 注意:对于已经存在的表,当表名改变后,与此相关的某些对象,如视图,以及通过表名与表相关的存储过程将无效.故一般不要修改表名!,添加,删除列,语法： 在表中添加一列 use 数据库名 alter table 表名 add 字段名 数据类型 go 删除表中的一列 use 数据库名 alter table 表名 drop column 字段名, go,修改列的属性,语法结构： 修改数据类型： Alter table 表名 Alter column 列名 新数据类型 注意：具有text,ntext,image,timestamp数据类型的列，计算列，全局标识列不能修改,修改数据类型或长度,语法： 修改字符数据类型的长度（varchar） use 数据库名 alter table 表名 alter column 字段名 char(修改后的长度) go 改变字段的数据类型 use 数据库名 alter table xs alter column 字段名 修改后的数据类型 go,修改列名：sp_rename exec sp_rename 表名.列名,修改列名,删除表,语法： drop table 表名 【例】删除xs表： drop table xs,第二节课目标,掌握数据操作语言 （插入，更新，删除，查询）,数据操作语言DML,数据操纵语言（DML）主要语句及功能,1.使用insert语句向表插入数据,往表中插入数据： insert into 表名(字段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语句： use xscj update xs set 备注=”三好生” where学号=001110 【例】将xs表中的所有学生的总学分都增加10。 update xs set 总学分 = 总学分+10 说明：若UPDATE语句中未使用WHERE子句限定范围，UPDATE语句 将更新表中的所有行。,3.使用删除语句delete删除记录,语法格式： 从表中删除一条记录： delete from 表名 where 条件 删除表中所有记录，保存表的架构： detele from 表名,语法格式： truncate table 表名 删除记录语句truncate： 删除表中的所有行。 truncate table在功能上与不带WHERE 子句的 delete 语句相同：二者均删除表中的全部行。但 truncate table比delete 速度快，且使用的系统和事务日志资源少。 【例】：删除xs表里所有记录 truncate table xs,4.使用删除语句truncate删除表,【例】查询学生表（xs）中的所有信息 所有列用 * 代替 select * from xs 当在select语句指定列的位置上使用*号时，表示选择表的所有列。,5.Select基本语句,【例】查询课程表课程号、课程名、学分 多列用逗号“，”分隔符 select 课程号,课程名, 学分 from kc,【例】从学生表(xs)中查询所有学生的学号 单列 select 学号 from xs,基本语法： select 列名 from 表名,带条件的查询,【例】查询xs表中姓名为李明的相关信息 用where指明条件 select * from xs where 姓名=李明,【例】查询xs表中出生日期在1978-3-26以后的学生 select 姓名,出生日期 from xs where 出生日期1989-1-3,注:字符型或日期型的数据要用单引号引起来,And ,or,【例】查询xs表中学号为1101且姓名为李明的相关信息 select * from xs where 姓名=李明 and 学号=1101,【例】查询xs表中姓名为李明或者学号为1101的相关信息 select * from xs where 姓名=李明 or 学号=1101,betweenand 在范围之内,【例】查找总学分为60和70之间的学生所有信息 select * from xs where 总学分 between 60 and 70 -(大于60小于70),【例】查找出生日期不在1980-8-26到1979-1-29的学生姓名 select 姓名,出生日期 from xs where出生日期 not between 1980-8-26 and 1979-1- 29 * 注意时间的用法,在使用where 子句进行查询时若条件表达式中出现若干条件相同的情况就会使表达式显得冗长不便于用户使用这时可用in 关键字来简化,【例】查找分数为70和80的所有学生信息 use xscj select * from xs where 总学分 in(70,80),In =or,order by排序:asc升；desc降,【例】查找学生的总学分以升序排列，出生日期以降序排列的学生姓名和学号 use xscj select 姓名,出生日期,总学分, 学号 from xs order by 总学分 asc ,出生日期 desc *以第一列为主序，再在第一列相等的基础上再对第二列排序；asc 默认，可省略,Distinct关键字,Distinct:从返回的结果数据集合中删除重复的行 【例】查询xscj数据库中xs表中的总学分,但是不能有重复的 use xscj select distinct 总学分 from xs order by 总学分 go,TOP关键字,top:关键字用于指定只返回前面一定数量的数据. top n :表示返回最前面的n行. top n percent:用百分比表示返回的行数 【例】查询在xscj库中的xs表中总学分最高的前5项的学生姓名 use xscj select top 5 总学分, 姓名 from xs order by 总学分 asc go,Top. with ties 指定返回并列前N条记录,use northwind select top 5 with ties orderid, productid, quantity from order details order by quantity desc go * with ties必须与order by连用,like子句与通配符配合使用.Sqlserver提供种通配符 1.%:表示任意字符 2. _:表示单个任意字符 3. :表示方括号里列出的任意一个字符 4.:表示任意一个没有在方括号里列出的字符,使用like子句进行模糊查询,基本语法: select 字段名 from 目标表 where 字段名 like 条件,【例】查找学生姓名以王开头的所有