SQLServer数据库基本对象.ppt

《SQLServer数据库基本对象.ppt》由会员分享，可在线阅读，更多相关《SQLServer数据库基本对象.ppt（113页珍藏版）》请在三一办公上搜索。

1、第3章SQL Server 数据库基本对象操作和管理,刘翔,3.1 数据库,在SQL Server2005中创数据库方法有两种，使用SQL Server Manager和T-SQL(Create Datebase)创建数据库。当新数据库创建之后，系统自动把model数据库中的系统表复制到新数据库中。下面描述在SQL Server2005中用两种不同方法创建数据库的内容。,Database Objects,EmpNum,LastName,FirstName,Extension,Borrow,10191,10192,10202,gong,Gao,Li,Jerry,Greg,x19891,x1943

2、3,x21467,ASP,Java,C+,HouseNo,3093,5313,6012,Trigger,StoredProcedureUpdatePhone,3.1 数据库,1.定义数据库 一般来说，为了创建数据库首先应定义数据库，定义数据库的内容包括：(1)数据库名称，如命名数据名称为Material_Data。(2)数据库大小，如主文件(.mdf)大小为10MB，日志文件大小为3MB。(3)数据库将驻留在哪一个文件中，如数据库将驻留在C:Program FilesMicrosoft SQL ServerMSSQLdatamaterial_Data.MDF中。即，创建Material_Dat

3、a数据库,主文件(.mdf)大小为10MB，日志文件大小为3MB。数据库将驻留在C:Program FilesMicrosoft SQL ServerMSSQLdatamaterial_Data.MDF中。,3.1 数据库,2.Server Manager创建数据库 下面描述用Server Manager创建数据库步骤。第一步，登录SQL Server 2005数据库引擎后，图3.1所示SQL Server数据库管理图面。,3.1 数据库,图3.1SQL Server数据库管理,3.1 数据库,在选择数据库菜单中右击鼠标，出现图3.2所示图面，选择新建数据库功能,图3.2选择新建数据库,3.1

4、 数据库,第二步 指定数据库名称Material_Data 在进入新建数据库，图3.3所示图面中，在数据库名称输入框输入数据库名称Material_Data,3.1 数据库,图3.3数据库名称输入图面,3.1 数据库,第三步 指定数据库所有者 可选择默认所有者。选择数据库所有者右边按钮，进入数据库所有者指定方法，步骤图所示,3.1 数据库,图3.4 指定数据库所者-1,3.1 数据库,图3.5 指定数据库所者-2,3.1 数据库,图3.6 指定数据库所者-3,3.1 数据库,图3.7 指定数据库所者-4,3.1 数据库,第四步 指定主文件与日志文件初始大小，图3.8所示。主文件10MB，日志文

5、件3MB。,图3.8指定主文件与日志文件初始大小,3.1 数据库,第五步 指定主文件与日志文件自助增长方式与最大文件大小，在图3.8中，选择自动增长栏中右边按扭，出现图3.9所示窗口。在3.9所示窗口中可指定文件自动增长方式与最大文件大小。,3.1 数据库,图3.9 指定主文件与日志文件自助增长方式最大文件大小,3.1 数据库,第六步 指定主文件与日志文件目录路径，在图3.8的路径栏中，选择与主文件、日志文件对应的右边按钮，分别进入主文件目录路径与日志文件目录路径指定窗口，图3.10与图3.11所示。,3.1 数据库,图3.10 指定主文件目录路径,3.1 数据库,图3.11 日志文件目录路径

6、,3.1 数据库,第七步 选择确定按钮，完成数据创建 3T-SQL(Create Datebase)创建数据库 下面描述用T-SQL(Create Datebase)创建数据库。(1)在SQL Server Management Studio中选择新建查询命令，进入T-SQL命令编辑窗口，图3.12所示。,3.1 数据库,图3.12 T-SQL命令编辑窗口,3.1 数据库,(2)在图3.12数据库下拉列表框中选择master数据库，图3.13所示。,图3.13数据库下拉列表框中选择master数据库,3.1 数据库,(3)在图3.11 的T-SQL命令编辑窗口，输入如下的T-SQL语句。CRE

7、ATE DATABASE Material_Data1ON PRIMARY(NAME=Material_Data1,FILENAME=C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatamaterial_Data1.MDF,SIZE=10MB,MAXSIZE=15MB,FILEGROWTH=20%)LOG ON(NAME=Material_Log1,FILENAME=C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatamaterial_Log1.LDF,SIZE=3MB,MAXSIZE=5MB,

8、FILEGROWTH=1MB),3.1 数据库,其中，PRIMARY 指定主文件(扩展名为.dmf)FILENAME 指定文件名与路径SIZE 指定数据或日志文件大小MAXSIZE 指定文件能增长到的最大长度FILEGROWTH 指定文件增长量,3.1 数据库,(4)在图3.13中，选择执行功能菜单，执行结果图3.14所示，数据库创建成功,3.1 数据库,图3.14 数据库创建成功,3.1 数据库,(5)当新数据库创建之后，系统自动把model数据库中的系统表复制到新数据库中，新建成功的Material_Data出现在数据库列表中，图3.15所示。,3.1 数据库,图3.15 新建成功的Mat

9、erial_Data,3.2表,表管理设计与创建 假如有如下五个业务表,见表3.1-3表5。,表3.1 Department,表3.2 Product,3.2表,表3.3planning,表3.4Manager,表3.5 Orderbill,3.2表,现对以上五个表的表名、字段名、字段类型、字段长度、主键及外键设计如下，表所示：,表3.6 Department,3.2表,表3.7 Product,表3.8 planning,3.2表,表3.9Manager,表3.10 Orderbill,3.2表,现试图在3.1中建立的数据库Material_Data中，创建以上5个表。在SQL Server

10、2005中,可用下述方法在己建立的数据库中创建及修改表。1使用SQL Server Manager创建表 第一步，进入Material_Data数据库中新建表功能，图3.16所示。,3.2表,图3.16 Material_Data数据库中新建表,3.2表,第二步，填写表中列名、数据类型及是否为空，设置键及表存盘。(1)Departmenta.填写Department表中列名、数据类型，图3.17所示。,3.2表,图3.17填写表中列名、数据类型,3.2表,b.设置表Department中主键，图所示。,图3.18设置表Department中主键,3.2表,图3.19设置表Department中

11、主键,3.2表,c.存盘及表命名，图3.20-图3.22所示。,图3.20输入表命名Table_1,图3.21 输入表命名Department,3.2表,图3.22表Department存盘,3.2表,(2）Producta.填写Product表中列名、数据类型，图3.23所示。,图3.23填写Product表中列名和数据类型,3.2表,b.设置主键，图所示。,图3.24设置Product表主键,3.2表,图3.25设置Product表主键结果,3.2表,c.存盘及表命名，图所示。,图3.26存盘及表命名Table_1,图3.27输入表命名Product,3.2表,图3.28 Product表

12、存盘结果,3.2表,(3)planninga.填写表planning中列名、数据类型，图3.29所示。,图3.29填写表planning中列名和数据类型,3.2表,b.存盘及表命名，图所示。,图3.30输入表命名Table_1,图3.31 输入表命名Planning,3.2表,图3.32 Planning表存盘结果,3.2表,c.按图所示设置deptNo外键,图3.33 打开deptNo外键的下拉菜单,3.2表,图3.34设置deptNo外键-使用“添加“按钮,3.2表,图3.35设置deptNo外键-确定“标识“名称,3.2表,图3.36置deptNo外键-选择主键表与外键表,3.2表,图3

13、.37deptNo外键-选择主键表与外键表字段,3.2表,图3.38 deptNo外键完成返回Management Studio窗口,3.2表,d.设置productNo外键，图所示。,图3.39设置productNo外键,3.2表,图3.40设置productNo外键-使用“添加“按钮,3.2表,图3.41设置productNo外键-确定标“名称”FK_planning_Product,3.2表,图3.42设置productNo外键-确定主表、外键表等相关表中字段,3.2表,图3.43设置productNo外键完成返回Management Studio窗口,3.2表,2使用SQL Serve

14、r Manager 设置表的属性 在属性框中，可修改表的属性，如可对前面设计的各表的列名，类型等进行修改，如图3.44所示。,3.2表,图3.44在属性框中修改表属性,3.2表,3使用SQL Server Manager修改表的结构（增加、删除列）。（1）增加列，图3.45所示。,3.2表,图3.45 增加列操作,3.2表,（2）删除列,图3.46 删除列操作,3.2表,4使用T-SQL语言创建表 创建表的SQL语句是CREATE TABLE。CREATE TABLE命令的语法如下：CREATE TABLE table_name(name_Of_attr_1 type_Of_attr_l，ns

15、me _of_attr_2 type_Of_attr_2，)；,3.2表,现再用CREATE TABLE在3.1中建立的数据库Material_Data中，创建前面用使用SQL Server Manager创建表的五个表。创建前先删除已经建立的表，否则会出现重复建表错误。可使用SQL Server Manager删除表（注意依赖关系）。图说明了删除表Planning的过程所示。,3.2表,图3.47 使用SQL Server Manager删除表菜单,3.2表,图3.48 使用SQL Server Manager删除对象窗口,3.2表,图3.49 被删除表Planning的依赖关系,3.2表,

16、图3.50删除表Planning后的结果,3.2表,可用使用DROP TABLE命令删除表(包括该表存储的所有记录)。DROP TABLE table name；如下是用T-SQL语句创建前面五个表的程序。,3.2表,（1）创建表department。SQLQuery.sql程序设计如下：CREATE TABLE department(deptNo char(10)NOT NULL PRIMARY KEY,deptNamevarchar(50)NOT NULL),3.2表,（2）创建表product。SQLQuery2.sql程序设计如下：CREATE TABLE product(produc

17、tNochar(10)NOT NULL PRIMARY KEY,productName varchar(50)NOT NULL),3.2表,（3）创建表planning。SQLQuery3.sql程序设计如下：CREATE TABLE planning(deptNo char(10)NOT NULL constraint FK_plan_deptNoforeign key(deptNo)references department(deptNo),productNochar(10)NOT NULLconstraint FK_plan_productNoforeign key(productNo)

18、references product(productNo),planoutputnumeric(10)NULL),3.2表,（4）创建表Manager。SQLQuery4.sql程序设计如下：CREATE TABLE Manager(ManagerNochar(10)NOT NULL PRIMARY KEY,ManagerName varchar(50)NOT NULL,Sex char(2)NULL,Wage numeric(6)NULL),3.2表,创建表Orderbill。SQLQuery5.sql程序设计如下：CREATE TABLE Orderbill(OrderbillNo cha

19、r(10)NOT NULLPRIMARY KEY,managerNo char(10)NOT NULLconstraint FK_Orderbill_managerNoforeign key(managerNo)references manager(managerNo),OrderDate Datetime NULL,OrderMoney numeric(10,2)NULL),3.2表,3.2.2 向表中插入数据 表创建完成后，可以用命令INSERT INTO向表里填充记录。语法是：INSERT INTO table_name(name_Of_attr_1，name_Of_attr_2，)VA

20、LUES(val_attr_1，val_attr 2，)；,3.2表,1.INSERT语句 实例3.1 把表3.11中数据输入数据库Material_Data中Department 表中。,表3.11Department,3.2表,INSERT INTO department(deptNo,deptName)VALUES(0001,生产管理部)INSERT INTO department(deptNO,deptName)VALUES(0002,销售管理部),3.2表,向Manager表中插入5个管理人员数据，请参见如下程序：INSERT INTO manager(managerNo,manag

21、erName,sex,wage)VALUES(001,李明,男,2000)INSERT INTO manager(managerNo,managerName,sex,wage)VALUES(002,王永,男,1000)INSERT INTO manager(managerNo,managerName,sex,wage)VALUES(003,刘可敬,男,1000)INSERT INTO manager(managerNo,managerName,sex,wage)VALUES(004,李小芳,女,1000)INSERT INTO manager(managerNo,managerName,sex

22、,wage)VALUES(005,李芳,女,1600),3.2表,2使用SELECT子句插入数据 语法形式：INSERT table_name SELECT column_list FROM table_list WHERE serach_conditions 注意：参考表可以相同或不同；要插入数据的表必须已存在；要插入数据的表必须和SELECT子句结果集兼容；,3.2表,实例3.2 建立department临时表department1,并把department中数据输入department1。程序设计如下：CREATE TABLE department1(deptNo char(10)NOT

23、 NULL PRIMARY KEY,deptNamevarchar(50)NOT NULL)INSERT department1 SELECT deptNO,deptName FROM department,3.2表,3.2.3 使用UPDATE修改表中数据 Update(更新数据)命令 要修改记录中的一个或者多个属性的值，使用UPDATE命令。语法是；UPDATE table name SET name_Of_attr_1=value-1，name of_attr_k=value_k WHERE condition；,3.2表,实例3.3把department表中“销售部”改为“市场部”。程

24、序设计如下：UPDATE department SET deptName=市场部 WHERE deptName=销售管理部,3.2表,要从一个表中删除一条记录，使用DELETEF ROM命令。语法是：DELETE FROM table_name WHERE condition 实例3.4 删除department表部门名为市场部的部门。DELETE FROM department WHERE deptName=市场部,3.2表,使用Transact_SQL修改表结构修改表结构语句的基本语法格式为：ALTER TABLE table_nameALTER COLUMN column_name ne

25、w_data_type(precision,scale)NULL|NOT NULL|ADD,n|DROPCONSTRAINT constraint_name|COLUMN column,n,3.2表,实例3.5 在表Product中增加一个新列Price(money类型，允许为空)。程序设计如下：USE Material_DataALTER TABLE ProductADD Price money NULLGO,3.2表,例3.6在表Product中删除列Price。程序设计如下：USE Material_DataALTER TABLE ProductDROP COLUMN PriceGO,3

26、.3 Index索引,可视化创建索引1.选择要创建索引的列，选择“索引/键（I）”，图3.62所示。,3.3 Index索引,图3.62选择要创建索引的列,3.3 Index索引,2.在弹出的“索引/键”对话框中，点击“添加”按钮，图3.63所示。,图3.63点击“添加”按钮,3.3 Index索引,3.在“标识”栏下的“（名称）”里修改新增索引的名称为：IX_ProductName。图3.64所示。,图3.64 修改新增索引的名称,3.3 Index索引,4.在“常规”栏下的“列”里选择新增索引要绑定的列及排序方式（升序或降序），图所示。,图3.65 选择新增索引要绑定的列及排序方式,3.3

27、 Index索引,图3.66 选择新增索引要绑定的列及排序方式,3.3 Index索引,5.点击“确定”完成排序,3.3 Index索引,使用Transact_SQL创建索引语法格式如下：CREATE UNIQUE CLUSTERED|NONCLUSTERED INDEX index_name ON table|view(columnASC|DESC,n)WITH,n ON filegroup:=PAD_INDEX|FILLFACTOR=fillfactor|IGNORE_DUP_KEY|DROP_EXISTING|STATISTICS_NORECOMPUTE|SORT_IN_TEMPDB,3

28、.3 Index索引,实例3.7 为表Product的productNane字段建立升序索引：USE Material_DataIF EXISTS(SELECT NAME FROM sysindexes WHERE name=IX_ProductName)DROP INDEX Product.IX_ProductNameGOUSE Material_DataCREATE INDEX IX_ProductName ON Product(productName)GO,3.3 Index索引,例3.8 为表Product的productNane、productNo两字段建立索引：USE Materi

29、al_DataIF EXISTS(SELECT NAME FROM sysindexes WHERE name=IX_PName_PNo)DROP INDEX Product.IX_PName_PNoGOUSE Material_DataCREATE INDEX IX_PName_PNo ON Product(productName,productNo)GO,3.4 SQL Server 数据查询与表现,在SQL Server2005中方法有两种查询数据库数据方法，使用SQL Server Manager和T-SQL创建查询。SQL里面最常用的命令是select(查询)语句，用于检索数据。,3

30、.4 SQL Server 数据查询与表现,3.4.1 检索表中列技术1一个检索实例 实例3.9检索pub数据库(系统中学习用的示例示数据库)中jobs表中全部信息。SELECT*FROM jobs 功能：*表示检索全部列。2重新对列排序技术 结果集中列的顺序由SELECT关键字后面列名的顺序确定。实例3.10 SELECT job_desc,job_id FROM jobs,3.4 SQL Server 数据查询与表现,3使用文字串技术 可在SELECT关键字后面增加文字说明。实例11 SELECT job_desc,its id is,job_id FROM jobs4改变列标题技术 可在

31、SELECT关键字后面，对每列表头增加描述字说明。实例12 通过如下语句，将jobs表中的job_desc、job_id两列表头增加描述字说明。这样列表显示的表头将是Descrbition（对应job_desc列）、Identify Number（对应job_id列）。SELECT Descrbition=job_desc,Identify Number=job_id FROM jobs,3.4 SQL Server 数据查询与表现,3使用文字串技术 可在SELECT关键字后面增加文字说明。实例11 SELECT job_desc,its id is,job_id FROM jobs4改变列标

32、题技术 可在SELECT关键字后面，对每列表头增加描述字说明。实例12 通过如下语句，将jobs表中的job_desc、job_id两列表头增加描述字说明。这样列表显示的表头将是Descrbition（对应job_desc列）、Identify Number（对应job_id列）。SELECT Descrbition=job_desc,Identify Number=job_id FROM jobs,3.4 SQL Server 数据查询与表现,3.4.2 操纵数据技术使用算术运算符：+,-,*,/。可以用在各种数字列上。实例3.13 对pub数据库titles表中的price列进行如下数据运

33、算：price+2、price 2、price*2及price/2。对应这些运算的SELECT语句如下：SELECT title,price,price+2 FROM titlesSELECT title,price,price 2 FROM titlesSELECT title,price,(price*2)FROM titlesSELECT title,price,(price/2)FROM titles,3.4 SQL Server 数据查询与表现,3.4.3 选择行技术语法格式：SELECT slect_list FROM table_list WHERE search_conditi

34、ons 1比较技术 search_conditions比较运算符有：等于 大于 小于=小于或者等于 不大于!不小于!,3.4 SQL Server 数据查询与表现,实例3.14 查询pubs数据库employee 表中pub_id不等于0877，列名为pub_id,emp_id,fname的行。Select pub_id,emp_id,fname From employee Where pub_id 0877 实例3.15 查询pubs数据库authors表中state等于 CA，列名为au_lname,city,state的行。Select au_lname,city,state From

35、authors Where state=CA,3.4 SQL Server 数据查询与表现,2范围技术 BETWEEN 指定搜索的范围。语法：test_expression NOT BETWEEN begin_expression AND end_expression 实例.16 从pubs 数据库titles表中检索年销售额在2000至2200之间的书籍。Select title_id,ytd_sales From titles Where ytd_sales Between 2000 and 2200,3.4 SQL Server 数据查询与表现,3逻辑运算技术AND|ORNOT指定搜索的

36、逻辑运算结果。语法：NOT expression AND|ORNOT expression SELECT select_list FROM table_list WHERE NOT expression AND|ORNOT expression,3.4 SQL Server 数据查询与表现,实例3.17 从pubs 数据库authors表中检索au_lname=white 或 au_lname=green 或 state=CA的作者。Select*From authors Where au_lname=white OR au_lname=green OR state=CA,3.4 SQL Se

37、rver 数据查询与表现,4汇总数据技术 1)合计函数 AVG 数据表达式的平均值 COUNT 某个表达式中数据值的数量 COUNT(*)所选择行的数量 MAX 表达式中最大值 MIN 表达式中最小值 SUN 数据表式的总和,3.4 SQL Server 数据查询与表现,实例3.18 从pubs 数据库titles表中检索price平均值。(pubs为SQL Server 2000中样本数据库,可将其附加到SQL Server 2005中)Select avg(price)From titles 从pubs 数据库titles表中检索price种类数，语句如下：Select count(price)From titles 从pubs 数据库titles表中检索记录数，语句如下：Select count(*)From titles,3.4 SQL Server 数据查询与表现,2）GROUP BY子句 实例3.19 从pubs 数据库sales表中，查询书的代号和销量，并依据书的代号进行分组。Select title_id,copies_sold=sum(qty)From sales GROUP BY title_id,