SQLServer数据库基本对象操作和管理.ppt

第3章SQL Server 数据库基本对象操作和管理,3.1 数据库,在SQL Server2005中创数据库方法有两种，使用SQL Server Manager和T-SQL(Create Datebase)创建数据库。当新数据库创建之后，系统自动把model数据库中的系统表复制到新数据库中。下面描述在SQL Server2005中用两种不同方法创建数据库的内容。,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中。,2 .Server Manager创建数据库,下面描述用Server Manager创建数据库步骤。 第一步， 登录SQL Server 2005数据库引擎后，图3.1所示SQL Server数据库管理图面。,SQL Server数据库管理界面,在选择数据库菜单中右击鼠标，出现图3.2所示图面，选择新建数据库功能,第二步 指定数据库名称Material_Data 在进入新建数据库，图3.3所示图面中，在数据库名称输入框输入数据库名称Material_Data,指定数据库名称,图3.3数据库名称输入图面,指定数据库所有者,第三步 指定数据库所有者 可选择默认所有者。选择数据库所有者右边按钮，进入数据库所有者指定方法，步骤图3.4-3.7所示,图3.6 指定数据库所者-3,图3.7 指定数据库所者-4,指定主文件与日志文件初始大小,第四步 指定主文件与日志文件初始大小，图3.8所示。主文件10MB，日志文件3MB。,图3.8指定主文件与日志文件初始大小,指定主文件与日志文件自助增长方式与最大文件大小,第五步 指定主文件与日志文件自助增长方式与最大文件大小，在图3.8中，选择自动增长栏中右边按扭，出现图3.9所示窗口。在3.9所示窗口中可指定文件自动增长方式与最大文件大小。,图3.9 指定主文件与日志文件自助增长方式最大文件大小,指定主文件与日志文件目录路径,第六步 指定主文件与日志文件目录路径，在图3.8的路径栏中，选择与主文件、日志文件对应的右边按钮，分别进入主文件目录路径与日志文件目录路径指定窗口，图3.10与图3.11所示。,3T-SQL(Create Datebase)创建数据库,下面描述用T-SQL(Create Datebase)创建数据库。 (1) 选择新建查询命令，进入T-SQL命令编辑窗口，图3.12所示。,(2)在图3.12数据库下拉列表框中选择master数据库，图3.13所示。,(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),其中， PRIMARY 指定主文件(扩展名为.dmf) FILENAME 指定文件名与路径 SIZE 指定数据或日志文件大小 MAXSIZE 指定文件能增长到的最大长度 FILEGROWTH 指定文件增长量,(4)在图3.13中，选择执行功能菜单，执行结果图3.14所示，数据库创建成功,(5)当新数据库创建之后，系统自动把model数据库中的系统表复制到新数据库中，新建成功的Material_Data出现在数据库列表中，图3.15所示。,3.2表,3.2.1表管理设计与创建 假如有如下五个业务表,表3.1 Department,表3.2 Product,表3.3planning,表3.4Manager,表3.5 Orderbill,现对以上五个表的表名、字段名、字段类型、字段长度、主键及外键设计如下，表3.6-3.10所示：,表3.6 Department,表3.7 Product,表3.8 planning,表3.9Manager,表3.10 Orderbill,现试图在3.1中建立的数据库Material_Data中，创建以上5个表。在SQL Server2005中, 可用下述方法在己建立的数据库中创建及修改表。 第一步，进入Material_Data数据库中新建表功能，图3.16所示。,1使用SQL Server Manager创建表,填写表中列名、数据类型及是否为空，设置键及表存盘。 (1)Department a.填写Department表中列名、数据类型，图3.17所示。,第二步,b.设置表Department中主键，图3.18-3.19所示。,c.存盘及表命名，图3.20-图3.22所示。,图3.20输入表命名Table_1,图3.21 输入表命名Department,(3) planning a.填写表planning中列名、数据类型，图3.29所示。,3.2表,b.存盘及表命名，图3.30-3.32所示。,图3.30输入表命名Table_1,图3.31 输入表命名Planning,c. 按图3.33-3.38所示设置deptNo外键,图3.34设置deptNo外键-使用“添加“按钮,图3.35设置deptNo外键-确定“标识“名称,图3.36置deptNo外键-选择主键表与外键表,图3.37deptNo外键-选择主键表与外键表字段,d.设置productNo外键，图3.39-3.43所示。,图3.40设置productNo外键-使用“添加“按钮,图3.41设置productNo外键-确定标“名称”FK_planning_Product,图3.42设置productNo外键-确定主表、外键表等相关表中字段,图3.43设置productNo外键完成返回Management Studio窗口,2使用SQL Server Manager 设置表的属性,在属性框中，可修改表的属性，如可对前面设计的各表的列名，类型等进行修改，如图3.44所示。,3使用SQL Server Manager修改表的结构,使用SQL Server Manager增加、删除列 （1）增加列，图3.45所示。,（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 ，)；,（1）创建表department。,SQLQuery.sql程序设计如下： CREATE TABLE department (deptNo char (10) NOT NULL PRIMARY KEY, deptName varchar(50) NOT NULL),（2）创建表product。,SQLQuery2.sql程序设计如下： CREATE TABLE product (productNo char(10) NOT NULL PRIMARY KEY, productName varchar(50) NOT NULL ),（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),（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),创建表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.2 向表中插入数据,表创建完成后，可以用命令INSERT INTO向表里填充记录。 语法是： INSERT INTO table_name(name_Of_attr_1 ，name_Of_attr_2，) VALUES(val_attr_1，val_attr 2，)；,1.INSERT语句,实例3.1 把表3.11中数据输入数据库Material_Data中Department 表中。,表3.11Department,INSERT INTO department (deptNo,deptName) VALUES (0001, 生产管理部) INSERT INTO department (deptNO,deptName) VALUES (0002, 销售管理部),2使用SELECT子句插入数据,语法形式： INSERT table_name SELECT column_list FROM table_list WHERE serach_conditions 注意： 参考表可以相同或不同； 要插入数据的表必须已存在； 要插入数据的表必须和SELECT子句结果集兼容；,建立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 使用UPDATE修改表中数据,Update(更新数据) 命令 要修改记录中的一个或者多个属性的值，使用UPDATE命令。语法是； UPDATE table name SET name_Of_attr_1=value-1 ，name of_attr_k=value_k WHERE condition；,实例,把department表中“销售部”改为“市场部”。程序设计如下： UPDATE department SET deptName=市场部 WHERE deptName=销售管理部,删除一条记录,要从一个表中删除一条记录，使用DELETE FROM命令。语法是： DELETE FROM table_name WHERE condition 实例 删除department表部门名为市场部的部门。 DELETE FROM department WHERE deptName=市场部,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,实例,在表Product中增加一个新列Price(money类型，允许为空)。程序设计如下： USE Material_Data ALTER TABLE Product ADD Price money NULL GO,实例,在表Product中删除列Price。程序设计如下： USE Material_Data ALTER TABLE Product DROP COLUMN Price GO,3.3 Index索引,索引： 一种物理结构，提供一种以一列或多列的值为基础的迅速查找表中的行的机制 索引类型 聚簇索引：行的物理顺序与索引顺序一致 非聚簇索引：逻辑顺序 唯一性索引：创建索引的列上不包括重复数据 SQL Server对索引的限制 每表一个聚簇索引 每表多个非聚簇索(249),3.3.1可视化创建索引,1.选择要创建索引的列，选择“索引/键（I）”，图3.62所示。,图3.62选择要创建索引的列,2.在弹出的“索引/键”对话框中，点击“添加”按钮，图3.63所示。,3.在“标识”栏下的“（名称）”里修改新增索引的名称为：IX_ProductName。图3.64所示。,图3.64 修改新增索引的名称,4. 在“常规”栏下的“列”里选择新增索引要绑定的列及排序方式（升序或降序）,图3.65 选择新增索引要绑定的列及排序方式,图3.66 选择新增索引要绑定的列及排序方式,5.点击“确定”完成排序,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 ,实例,为表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,实例,为表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.1 检索表中列技术,1检索全部列 检索pub数据库中jobs表中全部信息。 SELECT * FROM jobs 2重新对列排序 结果集中列的顺序由SELECT关键字后面列名的顺序确定。 SELECT job_desc, job_id FROM jobs,3.4.1 检索表中列技术,3使用文字串技术 可在SELECT关键字后面增加文字说明。 SELECT job_desc, its id is , job_id FROM jobs 4改变列标题技术 可在SELECT关键字后面，对每列表头增加描述字说明。 SELECT Descrbition= job_desc, Identify Number= job_id FROM jobs 将jobs表中的job_desc、job_id两列表头增加描述字说明。这样列表显示的表头将是Descrbition（对应job_desc列）、Identify Number（对应job_id列）。,3.4.2 操纵数据技术,使用算术运算符：+, -, *, /。可以用在各种数字列上。 对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.3 选择行技术,语法格式： SELECT slect_list FROM table_list WHERE search_conditions 1比较技术 search_conditions比较运算符有： 等于 大于 小于 = 小于或者等于 不大于 ! 不小于 !,实例,查询pubs数据库employee 表中pub_id不等于0877，列名为pub_id, emp_id, fname的行。 Select pub_id, emp_id, fname From employee Where pub_id 0877 查询pubs数据库authors表中state等于 CA，列名为au_lname, city, state的行。 Select au_lname, city, state From authors Where state = CA,2范围技术,BETWEEN 指定搜索的范围。 语法： test_expression NOT BETWEEN begin_expression AND end_expression 从pubs 数据库titles表中检索年销售额在2000至2200之间的书籍。 Select title_id, ytd_sales From titles Where ytd_sales Between 2000 and 2200,3逻辑运算技术,AND|ORNOT指定搜索的逻辑运算结果。 语法： SELECT select_list FROM table_list WHERE NOT expression AND|ORNOT expression,实例,从pubs 数据库authors表中检索au_lname=white 或 au_lname=green 或 state = CA的作者。 Select * From authors Where au_lname=white OR au_lname=green OR state = CA,4汇总数据技术,1)合计函数 AVG 数据表达式的平均值 COUNT 某个表达式中数据值的数量 COUNT(*) 所选择行的数量 MAX 表达式中最大值 MIN 表达式中最小值 SUN 数据表式的总和,实例,从pubs 数据库titles表中检索price平均值。 Select avg(price) From titles 从pubs 数据库titles表中检索price种类数，语句如下： Select count(price) From titles 从pubs 数据库titles表中检索记录数，语句如下： Select count(*) From titles,(2) GROUP BY子句,对查询结果分组后，合计（聚集）函数将分别作用于每个组 按指定的一列或多列值分组，值相等的为一组,(2) GROUP BY子句,求各个课程号及相应的选课人数。 SELECT Cno，COUNT(Sno) FROM SC GROUP BY Cno；,视图及其应用,视图综述 视图的操作 视图定义信息查询 案例中的视图应用,一、视图综述,1.视图的基本概念 视图是一个虚拟表，从一个或多个表中导出（视图也可以从视图中导出），其内容由查询语句定义生成。从表面上看，视图和真实表一样，具有结构和数据，包含一系列带有名称的列和行数据。实质上，表是视图的基础，数据库中只存储了视图定义，而不存放视图所对应的数据，视图所对应的数据仍存放在视图所引用的基表中。,一、视图综述,2. 视图的作用 1) 视图可以集中数据，满足不同用户对数据的不同要求。 2) 视图可以简化复杂查询的结构，从而方便用户对数据的操作。 3) 视图能够对数据提供安全保护。 4) 便于组织数据导出。,二、视图的操作,创建视图 修改视图 使用视图 删除视图,创建视图时应该注意以下：,创建者必须拥有创建视图的权限，对视图中引用的基表或视图有许可权。 只能在当前数据库中创建视图，如果使用分布式查询，视图所引用的基表和视图可以存在于其它数据库或它服务器中。 在一个视图中最多引用1024列，视图中记录的行数限制由基表中记录数目决定。 视图的名称必须遵循标识符的定义规则，对于每个用户来说也必须唯一，而且视图名称不允许与该用户拥有的表重名。,视图中列的名称一般继承其基表中列的名称，如果视 图中某一列是算术表达式、函数、常量或者来自多个表的列名相同，必须要为视图中的列定义名称。 可以将视图创建在其它视图上，SQL Server 2000中 允许32层的视图嵌套。 不能将规则、默认绑定在视图上。 不能在临时表上创建视图，也不能创建临时视图。,创建视图时应该注意以下:,创建视图,1.使用企业管理器创建视图 2.使用CREATE VIEW语句创建视图,例：创建一个包括学生学号、姓名及所选课程的课程名以及成绩的视图（视图名：成绩单）。,1.使用企业管理器创建视图,创建过程中的图解：,属性,图 视图设计器,1.使用企业管理器创建视图,创建步骤如下： 1）在企业管理器，打开“视图设计器”。 2）单击工具栏上的“添加表”按钮，将表添加到视图设计器中。 3）根据新建视图的需要，从表中选择视图引用的列。 4）在网格窗格中的“准则”栏中设置过滤记录的条件。 5）设置视图的其它属性。 6）在视图设计器窗口中，检查SQL语法。 7）最后，单击“保存”按钮，为视图指定名称。关闭“视图设计器”。,图 “添加表”对话框,1.使用企业管理器创建视图,图 选择表列,2.使用CREATE VIEW语句创建视图,语法格式如下： CREATE VIEW . . view_name ( column ,.n ) AS select_statement WITH CHECK OPTION ,2.使用CREATE VIEW语句创建视图,例：以学生表（S表）为基础建立一个视图，其名称为“学生名单”，其包含列为学生表中的学号和姓名。 程序清单如下：,CREATE VIEW 学生名单 AS SELECT SNO,SNAME FROM S,从单个表派生的视图:,2.使用CREATE VIEW语句创建视图,例：以选课表(S_C表)为基础建立一个视图，其名称为“数据库成绩”，其包含列为选课表中的所有列，筛选记录条件为“课程编号 = 001”。 程序清单如下： WITH CHECK OPTION ,CREATE VIEW 数据库成绩 AS SELECT * FROM S_C WHERE CNO = 001,2.使用CREATE VIEW语句创建视图,例：给出有北京仓库订购单的北京供应商的名称。 WITH CHECK OPTION ,从多个表派生的视图:,CREATE VIEW BJ_订购单 AS,SELECT 供应商名 FROM 供应商 WHERE 地址=北京 AND 供应商号 IN (SELECT 供应商号 FROM 订购单 WHERE 职工号 IN (SELECT 职工号 FROM 职工 WHERE 仓库号 IN (SELECT 仓库号 FROM 仓库 WHERE 城市=北京) ) ),例：列出每个职工经手的具有最高总金额的订购单信息。,订购单1,2.使用CREATE VIEW语句创建视图,2.使用CREATE VIEW语句创建视图,CREATE VIEW V_MAX AS SELECT 职工号,MAX(总金额) AS 最大总金额 FROM 订购单1 GROUP BY 职工号,SELECT 订购单1.职工号,订购单号,供应商号,订购日期,总金额 FROM 订购单1,V_MAX WHERE 订购单1.职工号=V_MAX.职工号 AND 订购单1.总金额=V_MAX.最大总金额,视图中的虚列:,或：CREATE VIEW V_SAL1 AS SELECT 职工号,工资 as 月工资,工资*12 as 年工资 FROM 职工,2.使用CREATE VIEW语句创建视图,CREATE VIEW V_SAL(职工号,月工资,年工资) AS SELECT 职工号,工资,工资*12 FROM 职工,例：定义一个视图，包含职工号、月工资、年工资。,修改视图,1.使用企业管理器 2.使用ALTER VIEW语句 其语法格式如下： ALTER VIEW view_name ( column ,.n ) AS select_statement,ALTER VIEW 学生名单 AS SELECT SNAME FROM S,修改视图,例：将视图“学生名单”修改为只包含列为学生表中的姓名。 程序清单如下： WITH CHECK OPTION ,使用视图,1.通过视图查询数据信息 例： 查询“数据库成绩”视图中全部的记录。,SELECT * FROM 数据库成绩,使用视图,2.通过视图修改数据 在使用视图修改数据时，要注意下列一些事项：,不能在一个语句中对多个基表使用数据修改语句。如果要修改由两个或两个以上基表得到的视图，必须进行多次修改，每次修改只能影响一个基表。 对于基表中需更新而又不允许空值的所有列，它们的值在 INSERT 语句或 DEFAULT 定义中指定。这将确保基表中所有需要值的列都可以获取值。,不能修改那些通过计算得到结果的列。 在视图定义中使用了 WITH CHECK OPTION 子句，则所有在视图上执行的数据修改语句都必须符合定义视图的 SELECT 语句中所设定的条件。 在基表的列中修改的数据必须符合对这些列的约束条件，如是否为空、约束、DEFAULT 定义等。,使用视图,使用视图,例：向“数据库成绩”视图中插入一条新的选课记录。 学号为0008, 课程号为001,成绩为90 。,INSERT INTO 数据库成绩 VALUES(0008, 001,90) GO,使用视图,例：将“数据库成绩”视图中学生0003的成绩提高10%。,UPDATE 数据库成绩 SET SCORE=SCORE*1.1 WHERE SNO=0003,INSERT INTO 学生名单 VALUES (0011,孟欢),使用视图,例：向学生名单视图中插入一条新记录；学号：0011，姓名：孟欢。,使用视图,CREATE VIEW S_S_VIEW(SNO,SAVG) AS SELECT SNO,AVG(SCORE) FROM S_C GROUP BY SNO UPDATE S_S_VIEW SET SAVG=90 WHERE SNO=0001,例：,功能？,正确？,使用视图,例：删除“数据库成绩”视图中学号为“0002”的同学记录。,DELETE FROM 数据库成绩 WHERE SNO= 0002 GO,删除视图,1.使用企业管理器删除视图 2.使用DROP VIEW语句 语法格式如下： DROP VIEW view ,.n ,例： 删除视图“数据库成绩”。,DROP VIEW 数据库成绩,案例中的视图应用,1）建立计算机系（系部代码 = 01 ）学生视图，该视图包含学生的学号、姓名和入学时间信息。 2）建立学生表中全体学生年龄的视图该视图包含学生的学号、姓名和年龄信息。,涉及的表： 学生（学号，姓名，性别，出生日期，入学时间，系部代码）,3）建立选修“高等数学”的学生的视图，该视图包含学生的学号和姓名信息。 4）建立没有获得学分（成绩60）的学生的视图，该视图包含学生的学号、姓名、课程名和成绩信息。,涉及的表： 学生（学号，姓名，性别） 课程（课程号，课程名） 选修（学号，课程号，成绩）,案例中的视图应用,案例中的视图应用,建立计算机系（系部代码 = 01 ）学生视图，该视图包含学生的学号、姓名和入学时间信息。,CREATE VIEW V_计算机系学生 AS SELECT 学号, 姓名, 入学时间 FROM 学生 WHERE 系部代码 = 01,涉及的表：学生（学号，姓名，性别，出生日期，入学时间，系部代码）,案例中的视图应用,建立学生表中全体学生年龄的视图该视图包含学生的学号、姓名和年龄信息。,CREATE VIEW V_学生年龄 AS SELECT 学号,姓名,YEAR(GETDATE()-YEAR(出生日期) AS 年龄 FROM 学生,涉及的表：学生（学号，姓名，性别，出生日期，入学时间，系部代码）,案例中的视图应用,建立选修“高等数学”的学生的视图，该视图包含学生的学号和姓名信息。,CREATE VIEW V_选修高数 AS SELECT 学生.学号,学生.姓名 FROM 学生,课程 ,选修 WHERE 课程.课程号 =选修.课程号 AND 选修.学号 = 学生.学号 AND 课程.课程名 = 高等数学,涉及的表： 学生（学号，姓名，性别） 课程（课程号，课程名） 选修（学号，课程号，成绩）,案例中的视图应用,建立没有获得学分（成绩60）的学生的视图，该视图包含学生的学号、姓名、课程名和成绩信息。,CREATE VIEW V_未获学分 AS SELECT A.学号,A.姓名,C.课程名,B.成绩 FROM 学生 A JOIN 选修 B ON A.学号=B.学号 JOIN 课程 C ON B.课程号=C.课程号 WHERE B.成绩 60,涉及的表： 学生（学号，姓名，性别） 课程（课程号，课程名） 选修（学号，课程号，成绩）,