SQLServer数据库基本对象.ppt

第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,x19433,x21467,ASP,Java,C+,HouseNo,3093,5313,6012,Trigger,Stored Procedure UpdatePhone,3.1 数据库,1.定义数据库 一般来说，为了创建数据库首先应定义数据库，定义数据库的内容包括： (1)数据库名称， 如命名数据名称为Material_Data。 (2)数据库大小，如主文件(. mdf)大小为10MB，日志文件大小为3MB。 (3)数据库将驻留在哪一个文件中，如数据库将驻留在C:Program FilesMicrosoft SQL ServerMSSQLdatamaterial_Data.MDF中。 即，创建Material_Data数据库, 主文件(. 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 数据库,第二步 指定数据库名称Material_Data 在进入新建数据库，图3.3所示图面中，在数据库名称输入框输入数据库名称Material_Data,3.1 数据库,图3.3数据库名称输入图面,3.1 数据库,第三步 指定数据库所有者 可选择默认所有者。选择数据库所有者右边按钮，进入数据库所有者指定方法，步骤图3.4-3.7所示,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，日志文件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 日志文件目录路径,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语句。 CREATE DATABASE Material_Data1 ON 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, 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 新建成功的Material_Data,3.2表,3.2.1表管理设计与创建 假如有如下五个业务表,见表3.1-3表5。,表3.1 Department,表3.2 Product,3.2表,表3.3planning,表3.4Manager,表3.5 Orderbill,3.2表,现对以上五个表的表名、字段名、字段类型、字段长度、主键及外键设计如下，表3.6-3.10所示：,表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 Server2005中, 可用下述方法在己建立的数据库中创建及修改表。 1使用SQL Server Manager创建表 第一步，进入Material_Data数据库中新建表功能，图3.16所示。,3.2表,图3.16 Material_Data数据库中新建表,3.2表,第二步，填写表中列名、数据类型及是否为空，设置键及表存盘。 (1)Department a.填写Department表中列名、数据类型，图3.17所示。,3.2表,图3.17填写表中列名、数据类型,3.2表,b.设置表Department中主键，图3.18-3.19所示。,图3.18设置表Department中主键,3.2表,图3.19设置表Department中主键,3.2表,c.存盘及表命名，图3.20-图3.22所示。,图3.20输入表命名Table_1,图3.21 输入表命名Department,3.2表,图3.22表Department存盘,3.2表,(2）Product a.填写Product表中列名、数据类型，图3.23所示。,图3.23填写Product表中列名和数据类型,3.2表,b.设置主键，图3.25-3.26所示。,图3.24设置Product表主键,3.2表,图3.25设置Product表主键结果,3.2表,c.存盘及表命名，图3.26-3.28所示。,图3.26存盘及表命名Table_1,图3.27输入表命名Product,3.2表,图3.28 Product表存盘结果,3.2表,(3) planning a.填写表planning中列名、数据类型，图3.29所示。,图3.29填写表planning中列名和数据类型,3.2表,b.存盘及表命名，图3.30-3.32所示。,图3.30输入表命名Table_1,图3.31 输入表命名Planning,3.2表,图3.32 Planning表存盘结果,3.2表,c. 按图3.33-3.38所示设置deptNo外键,图3.33 打开deptNo外键的下拉菜单,3.2表,图3.34设置deptNo外键-使用“添加“按钮,3.2表,图3.35设置deptNo外键-确定“标识“名称,3.2表,图3.36置deptNo外键-选择主键表与外键表,3.2表,图3.37deptNo外键-选择主键表与外键表字段,3.2表,图3.38 deptNo外键完成返回Management Studio窗口,3.2表,d.设置productNo外键，图3.39-3.43所示。,图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 Server 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 ，nsme _of_attr_2 type_Of_attr_2 ，)；,3.2表,现再用CREATE TABLE在3.1中建立的数据库Material_Data中，创建前面用使用SQL Server Manager创建表的五个表。创建前先删除已经建立的表，否则会出现重复建表错误。 可使用SQL Server Manager删除表（注意依赖关系）。图3.47-3.50说明了删除表Planning的过程所示。,3.2表,图3.47 使用SQL Server Manager删除表菜单,3.2表,图3.48 使用SQL Server Manager删除对象窗口,3.2表,图3.49 被删除表Planning的依赖关系,3.2表,图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, deptName varchar(50) NOT NULL),3.2表,（2）创建表product。 SQLQuery2.sql程序设计如下： CREATE TABLE product (productNo char(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_deptNo foreign key (deptNo) references department(deptNo), productNo char(10) NOT NULL constraint FK_plan_productNo foreign key (productNo) references product(productNo), planoutput numeric(10) NULL),3.2表,（4）创建表Manager。SQLQuery4.sql程序设计如下： CREATE TABLE Manager (ManagerNo char(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 char(10) NOT NULL PRIMARY KEY, managerNo char(10)NOT NULL constraint FK_Orderbill_managerNo foreign 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，) VALUES(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,managerName,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,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 NULL PRIMARY KEY, deptName varchar(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表中“销售部”改为“市场部”。程序设计如下： 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表,3.2.5使用Transact_SQL修改表结构 修改表结构语句的基本语法格式为： ALTER TABLE table_name ALTER COLUMN column_name new_data_type (precision,scale) NULL|NOT NULL |ADD,n |DROPCONSTRAINT constraint_name |COLUMN column,n,3.2表,实例3.5 在表Product中增加一个新列Price(money类型，允许为空)。程序设计如下： USE Material_Data ALTER TABLE Product ADD Price money NULL GO,3.2表,例3.6在表Product中删除列Price。程序设计如下： USE Material_Data ALTER TABLE Product DROP COLUMN Price GO,3.3 Index索引,3.3.1可视化创建索引 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.66所示。,图3.65 选择新增索引要绑定的列及排序方式,3.3 Index索引,图3.66 选择新增索引要绑定的列及排序方式,3.3 Index索引,5.点击“确定”完成排序,3.3 Index索引,3.3.2使用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.3 Index索引,实例3.7 为表Product的productNane字段建立升序索引： USE Material_Data IF EXISTS(SELECT NAME FROM sysindexes WHERE name=IX_ProductName) DROP INDEX Product.IX_ProductName GO USE Material_Data CREATE INDEX IX_ProductName ON Product(productName) GO,3.3 Index索引,例3.8 为表Product的productNane、productNo两字段建立索引： USE Material_Data IF EXISTS(SELECT NAME FROM sysindexes WHERE name=IX_PName_PNo) DROP INDEX Product.IX_PName_PNo GO USE Material_Data CREATE INDEX IX_PName_PNo ON Product(productName,productNo) GO,3.4 SQL Server 数据查询与表现,在SQL Server2005中方法有两种查询数据库数据方法，使用SQL Server Manager和T-SQL创建查询。SQL里面最常用的命令是select(查询)语句，用于检索数据。,3.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 jobs 4改变列标题技术 可在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 jobs 4改变列标题技术 可在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列进行如下数据运算：price+2、price 2、price * 2及price / 2。对应这些运算的SELECT语句如下： SELECT title, price, price+2 FROM titles SELECT title, price, price 2 FROM titles SELECT title, price, (price * 2) FROM titles SELECT title, price, (price / 2) FROM titles,3.4 SQL Server 数据查询与表现,3.4.3 选择行技术 语法格式： SELECT slect_list FROM table_list WHERE search_conditions 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 a