《结构化查询语言》PPT课件.ppt

第三章结构化查询语言 学习创建数据库结构的基本SQL语句学习将数据添加到数据库的基本SQL语句学习单表的基本SQL查询语句学习多表关联的SQL查询语句使用SQL语句修改与删除数据库表中数据使用SQL语句修改与删除数据库表和约束学习使用基本SQL语句创建和使用视图 学习目标 一 基本概念 3 1SQL简介 SQL StructuredQueryLanguage 结构化查询语言 它是一种对数据库进行访问与操作的数据子语言 它嵌入到编程语言 如JAVA C 等 中执行或在DBMS工具中执行 二 SQL发展 20世纪70年代由IBM公司开发1986ANSI的数据库委员会批准了SQL作为关系型数据库语言的美国标准 1987年国际标准化组织 ISO 将其采纳为国际标准SQL86 1989年更新成为国际标准SQL 891992年更新成为国际标准SQL 92 或称SQL2 1999年更新成为国际标准SQL 99 或称SQL3 2008年更新成为国际标准SQL 2008 三 SQL应用情况 绝大多数主流的关系型数据库管理系统采用了SQL语言标准OracleSybaseDB2MicrosoftSQLServerAccessMySql 其中一些数据库管理系统对SQL语句进行了再开发和扩展 如Sybase MicrosoftSQLServer将操作语言扩展为Transaction SQL Oracle将操作语言扩展为PL SQL 四 SQL能做什么 SQL可从数据库取回数据SQL可在数据库中插入新的纪录SQL可更新数据库中的数据SQL可从数据库删除记录SQL可创建新数据库SQL可在数据库中创建新表SQL可在数据库中创建存储过程SQL可在数据库中创建视图SQL可以设置表 存储过程和视图的权限 五 SQL语言语句类别 1 数据操纵作语言 数据操纵语言 DataManipulationLanguage DML 是SQL语言中用于查询和增删修改表数据的语句 主要语句如下 SELECT 从数据库表中获取数据UPDATE 更新数据库表中的数据DELETE 从数据库表中删除数据INSERT 向数据库表中插入数据 2 数据定义语言 数据定义语言 DataDefinitionLanguage DDL 是SQL语言中用于创建或删除表 也可以定义索引 键 规定表之间的链接 以及施加表间的约束 主要语句如下 CREATEDATABASE 创建新数据库ALTERDATABASE 修改数据库CREATETABLE 创建新表ALTERTABLE 变更 改变 数据库表DROPTABLE 删除表CREATEINDEX 创建索引 搜索键 DROPINDEX 删除索引 六 SQL语言程序 创建数据库表CUSTOMERCOURSEENROLLMENT DDL 插入数据库表CUSTOMER DML 七 示例数据库 部门 DEPARTMENT 雇员 EMPLOYEE 项目 PROJECT 任务 ASSIGNMENT 工程项目管理 部门 DEPARTMENT 表结构 1 数据库表结构 雇员 EMPLOYEE 表结构 项目 PROJECT 表结构 任务 ASSIGNMENT 表结构 参照完整性约束 DEPARTMENT表 EMPLOYEE表 2 数据库表联系 参照完整性约束 DEPARTMENT表结构 PROJECT表结构 参照完整性约束 参照完整性约束 PROJECT表结构 ASSIGNMENT表结构 参照完整性约束 PROJECT表数据 ASSIGNMENT表数据 3 业务规则 如果要删除EMPLOYEE中的某个行 而该行连接到ASSIGNMENT 则禁止对EMPLOYEE行执行删除操作如果删除了PROJECT中的某个行 则ASSIGNMENT中连接到这个已删除的PROJECT行的所有行也将随之被删除 如果删除了EMPLOYEE表中的某个行 例如一个雇员已调离 就必须有人接替这个雇员工作 应用程序需要在删除原雇员前 把该雇员的任务重新分配给另一个雇员 如果删除了PROJECT表中的某个行 就取消这个项目 因此不必维护这个项目的相关记录 业务规则含义 一 SQL语言DDL语句组成 数据定义语言 DataDefinitionLanguage DDL 是SQL语言中用于创建或删除表 也可以定义索引 键 规定表之间的链接 以及施加表间的约束 主要语句如下 CREATEDATABASE 创建新数据库ALTERDATABASE 修改数据库CREATETABLE 创建新表ALTERTABLE 变更 改变 数据库表DROPTABLE 删除表CREATEINDEX 创建索引 搜索键 DROPINDEX 删除索引 3 2用于数据定义的SQL语句 二 CREATETABLE语句 1 语句基本格式 CREATETABLENewTableName ColumnNameDataTypeOptionalConstraint ColumnNameDataTypeOptionalConstraint ColumnNameDataTypeOptionalConstraint 列名 表名 列类型 列约束 例 雇员数据库表EMPLOYEE创建 CREATETABLEEMPLOYEE EmployeeNumberInt FirstNameChar 30 LastNameChar 30 DepartmentChar 35 PhoneChar 12 EmailVarChar 100 2 列约束条件 CREATETABLENewTableName ColumnNameDataTypeOptionalConstraint ColumnNameDataTypeOptionalConstraint ColumnNameDataTypeOptionalConstraint 列约束子句PRIMARYKEY 主键NOTNULL 非空值NULL 空值UNIQUE 值唯一CHECK 有效性检查DEFAULT 缺省值 CREATETABLEDEPARTMENT DepartmentNameChar 35 PRIMARYKEY BudgetCodeChar 30 NOTNULL OfficeNumberChar 15 NOTNULL PhoneChar 12 NOTNULL 例1 部门数据库表DEPARTMENT创建 DBMS执行SQL语句 SQLServer执行sql语句 CREATETABLEEMPLOYEE EmployeeNumberIntPRIMARYKEYIDENTITY 1 1 FirstNameChar 30 NOTNULL LastNameChar 30 NOTNULL DepartmentChar 35 NOTNULLDEFAULT 人力资源 PhoneChar 12 NULL EmailVarChar 100 NOTNULLUNIQUE 例2 雇员数据库表EMPLOYEE创建 CREATETABLEPROJECT ProjectIDIntPRIMARYKEY NameChar 30 NOTNULL DepartmentChar 35 CHECK DepartmentIN 财务 销售 人事 MaxHoursNumeric 6 2 NOTNULLDEFAULT100 StartDateDateTimeNULL EndDateDateTimeNULL 练习 项目数据库表PROJECT创建 三 SQL语言的数据类型 1 基本数据类型 字符 CHAR VARCHAR TEXT整数 SMALLINT INTEGER浮点数 NUMBER n d FLOAT n d 日期 DATE DATATIME货币 MONEY 2 不同数据库所支持数据类型 见P98页 图3 5 a 所示SQLServer2008的数据类型见P99页 图3 5 b 所示ORACLEDatabase11g的数据类型见P99页 图3 5 c 所示MySQL的数据类型 四 使用表约束定义主键 CREATETABLEDEPARTMENT DepartmentNameChar 35 NOTNULL BudgetCodeChar 30 NOTNULL OfficeNumberChar 15 NOTNULL PhoneChar 12 NOTNULL CONSTRAINTDEPARTMENT PKPRIMARYKEY DepartmentName 使用表约束定义主键的优点 便于定义复合主键可命名主键约束便于定义代理键 例1 项目数据库表PROJECT创建 CREATETABLEPROJECT ProjectIDIntNOTNULLAUTO INCREMENT NameChar 30 NOTNULL DepartmentChar 35 NOTNULL MaxHoursNumeric 6 2 NOTNULLDEFAULT100 StartDateDateTimeNULL EndDateDateTimeNULL CONSTRAINTPROJECT PKPRIMARYKEY ProjectID MySQL执行sql语句 例2 任务分配数据库表ASSIGNMENT创建 CREATETABLEASSIGNMENT ProjectIDIntNOTNULL EmployeeNumberIntNOTNULL HoursWorkedNumeric 6 2 NULL CONSTRAINTASSIGNMENT PKPRIMARYKEY ProjectID EmployeeNumber 五 使用表约束定义外键 参照完整性约束 DEPARTMENT表 EMPLOYEE表 CREATETABLEEMPLOYEE EmployeeNumberIntNOTNULLAUTO INCREMENT FirstNameChar 30 NOTNULL LastNameChar 30 NOTNULL DepartmentChar 35 NOTNULLDEFAULT 人力资源 PhoneChar 12 NULL EmailVarChar 100 NOTNULLUNIQUE CONSTRAINTEMPLOYEE PKPRIMARYKEY EmployeeNumber CONSTRAINTEMP DEPART FKFOREIGNKEY Department REFERENCESDEPARTMENT DepartmentName ONUPDATECASCADE DEPARTMENT表结构 PROJECT表结构 参照完整性约束 编写实现下表关联的参照完整性约束SQL语句 CREATETABLEPROJECT ProjectIDIntNOTNULLAUTO INCREMENT NameChar 30 NOTNULL DepartmentChar 35 NOTNULL MaxHoursNumeric 6 2 NOTNULLDEFAULT100 StartDateDateTimeNULL EndDateDateTimeNULL CONSTRAINTPROJECT PKPRIMARYKEY ProjectID CONSTRAINTPROJ DEPART PKFOREIGNKEY Department REFERENCESDEPARTMENT DepartmentName ONUPDATECASCADE PROJECT表结构 ASSIGNMENT表结构 参照完整性约束 编写实现下表关联的参照完整性约束 ASSIGNMENT表结构 参照完整性约束 EMPLOYEE表 CREATETABLEASSIGNMENT ProjectIDIntNOTNULL EmployeeNumberIntNOTNULL HoursWorkedNumeric 6 2 NULL CONSTRAINTASSIGNMENT PKPRIMARYKEY ProjectID EmployeeNumber CONSTRAINTASSIGN PROJ FKFOREIGNKEY ProjectID REFERENCESPROJECT ProjectID ONUPDATENOACTIONONDELETECASCADE CONSTRAINTASSIGN EMP FKFOREIGNKEY EmployeeNumber REFERENCESEMPLOYEE EmployeeNumber ONUPDATENOACTIONONDELETENOCASCADE 一 SQL的数据操纵语句 3 3插入关系数据的SQL语句 SELECT 从数据库表中获取数据UPDATE 更新数据库表中的数据DELETE 从数据库表中删除数据INSERT 向数据库表中插入数据 二 SQL的插入数据语句 1 语句基本格式 INSERTINTOTableNameVALUES 列值 列值 列值 例1 部门数据库表DEPARTMENT插入记录数据 INSERTINTODEPARTMENTVALUES Administration BC 100 10 BLDG01 300 360 285 8100 说明 如果DBMS提供了代理键功能 则在插入具有代理键的数据库表时 该项不需要放入值 例2 项目数据库表PROJECT插入记录数据 因ProjectID是代理键 插入数据语句为 INSERTINTOPROJECTVALUES 2005Q3ProductPlan Marketing 135 00 10 MAY 05 15 JUN 05 例3 如果缺少某些列 那么插入语句应该列出提供数据的列名 INSERTINTOPROJECT Name Department MaxHours StartDate VALUES 2005Q4PortfolioAnalysis Finance 140 00 2005 10 05 例4 部门数据库表DEPARTMENT插入多条记录数据 INSERTINTODEPARTMENTVALUES Administration BC 100 10 BLDG01 300 360 285 8100 INSERTINTODEPARTMENTVALUES Legal BC 200 10 BLDG01 200 360 285 8200 INSERTINTODEPARTMENTVALUES Accounting BC 300 10 BLDG01 100 360 285 8300 INSERTINTODEPARTMENTVALUES Finance BC 400 10 BLDG01 140 360 285 8400 INSERTINTODEPARTMENTVALUES HumanResources BC 500 10 BLDG01 180 360 285 8500 INSERTINTODEPARTMENTVALUES Production BC 600 10 BLDG02 100 360 287 8600 INSERTINTODEPARTMENTVALUES Marketing BC 700 10 BLDG02 200 360 287 8700 INSERTINTODEPARTMENTVALUES InfoSystems BC 800 10 BLDG02 270 360 287 8800 说明 为了使INSERT语句起作用 必须为所有NOTNULL列提供值 一 SQL关系查询语句 3 4SQL关系查询语句 1 语句基本格式 SELECTColumnNamesFROMTableNameWHERESomeConditionExits 2 从单个表读取指定列 例从PROJECT表中查询读取Name Department MaxHours三列数据 SQLServer查询操作结果 SELECTName Department MaxHoursFROMPROJECT DBC e03 Ch03 SQLQuery01 SQL查询语句如下 MYSQL查询操作结果 例 从项目数据库表RPOJECT提取Department列 SELECTDepartmentFROMPROJECT DBC e03 Ch03 SQLQuery03 其结果为 说明 SQLSELECT语句执行后的结果也应该是一个关系 为了使读取的数据删除重复行 可以在查询语句中加入DISTINCT关键字 SELECTDISTINCTDepartmentFROMPROJECT DBC e03 Ch03 SQLQuery04 其结果为 3 从单个表读取指定行 例从PROJECT表中查询读取与Finance部门资助的项目列表 PROJECT表中查询的SQLSELECT语句如下 SELECTProjectID Name Department MaxHours StartDate EndDateFROMPROJECTWHEREDepartment Finance DBC e03 Ch03 SQLQuery05 说明 在SELECT关键字后使用 可以提取所有列 SELECT FROMPROJECTWHEREDepartment Finance DBC e03 Ch03 SQLQuery06 说明 在WHERE子句中放置更多条件 可以提取指定范围数据 SELECT FROMPROJECTWHEREDepartment Finance ANDMaxHours 135 DBC e03 Ch03 SQLQuery08 4 从单个表读取指定行和指定列 例1从EMPLOYEE表中查询读取Accounting部门所有雇员的FirstName LastName Phone和Department值 SELECTFirstName LastName Phone DepartmentFROMEMPLOYEEWHEREDepartment Accounting DBC e03 Ch03 SQLQuery09 例2从EMPLOYEE表中查询 使用OR关键字读取在Accounting部门工作或电话号码为360 285 8310的雇员 SELECTFirstName LastName Phone DepartmentFROMEMPLOYEEWHEREDepartment Accounting ORPhone 360 285 8410 Ch03 SQLQuery11 例3从EMPLOYEE表中查询 使用IN关键字读取Accounting Finance Marketing部门工作的雇员 SELECTFirstName LastName Phone DepartmentFROMEMPLOYEEWHEREDepartmentIN Accounting Finance Marketing Ch03 SQLQuery12 例4从EMPLOYEE表中查询 使用NOTIN关键字读取不在Accounting Finance Marketing部门工作的雇员 SELECTFirstName LastName Phone DepartmentFROMEMPLOYEEWHEREDepartmentNOTIN Accounting Finance Marketing Ch03 SQLQuery13 5 在WHERE子句中指定范围 使用通配符 例1从EMPLOYEE表中查询 使用BETWEEN指定范围 SELECTFirstName LastName Phone DepartmentFROMEMPLOYEEWHEREEmployeeNumberBETWEEN2AND5 Ch03 SQLQuery14 SELECTFirstName LastName Phone DepartmentFROMEMPLOYEEWHEREEmployeeNumberBETWEEN2AND5 等同于如下查询 SELECTFirstName LastName Phone DepartmentFROMEMPLOYEEWHEREEmployeeNumber 2ANDEmployeeNumber 5 Ch03 SQLQuery14 b 例2从PROJECT表中查询 使用LIKE和通配符 指定范围 SELECT FROMPROJECTWHERENameLIKE 2005Q PortfolioAnalysis Ch03 SQLQuery15 例3从EMPLOYEE表中查询 使用LIKE和 代替一个或任意多个字符 SELECT FROMEMPLOYEEWHEREPhoneLIKE 360 287 Ch03 SQLQuery17ACCESS中使用 和 例4从EMPLOYEE表中查询 使用ISNULL关键词在WHERE子句中查找电话号码含有Null的行 SELECTFirstName LastName Phone DepartmentFROMEMPLOYEEWHEREPhoneISNULL Ch03 SQLQuery19 6 对结果进行排序 例1从EMPLOYEE表中查询 使用ORDERBY关键词对结果行进行排序 SELECTFirstName LastName Phone DepartmentFROMEMPLOYEEORDERBYDepartment Ch03 SQLQuery20 在SELECT查询语句返回的结果中 行的顺序是任意的 如果需要结果集排序 可以在SELECT语句中加入ORDERBY关键字 升序排序 例2从EMPLOYEE表中查询 使用DESC关键词对结果集进行降序排序 SELECTFirstName LastName Phone DepartmentFROMEMPLOYEEORDERBYDepartmentDESC Ch03 SQLQuery21 默认情况 在SELECT的ORDERBY查询结果 是升序排序 如果需要结果集降序排序 可以加入关键字DESC改变 降序排序 例3从EMPLOYEE表中查询 首先对Department列降序排序 然后再对LastName列按升序排序 SELECTFirstName LastName Phone DepartmentFROMEMPLOYEEORDERBYDepartmentDESC LastNameASC Ch03 SQLQuery22 如果需要结果集按多个列排序 可以分别加入关键字ASC或DESC改变 多列排序 7 SQL内置函数和计算 例1从PROJECT表中查询 使用COUNT函数统计项目数量 SELECTCOUNT FROMPROJECT Ch03 SQLQuery23 SQL允许基于表中的数据计算 SQL包含5个内置函数 COUNT SUM AVG MAX MIN 这些函数都针对SELECT语句的结果集进行操作 例2从PROJECT表中查询 使用COUNT函数统计行数时 加入自定义列名 SELECTCOUNT ASNumberofProjectsFROMPROJECT Ch03 SQLQuery24 注意 上面结果没有列名 可以使用AS关键字赋予列名 例3从PROJECT表中查询 使用MIN MAX SUM函数分别给出最小项目工时 最大项目工时 总工时统计值 SELECTMIN MaxHours ASMinmumMaxHours MAX MaxHours ASMaximumMaxHours SUM MaxHours ASTotalMaxHoursFROMPROJECTWHEREProjectID 1200 Ch03 SQLQuery27 例4从PROJECT表中查询 分别给出每个项目的最大成本 假定雇员的工资是18 5美元 每工时 SELECTProjectID Name MaxHours 18 5 MaxHours ASMaxProjectCostFROMPROJECT Ch03 SQLQuery28 注意 除了后面将提到的GROUPBY语句外 列的名称是不允许和内置函数一起混合使用 以下语句不规范 SELECTMaxHours SUM MaxHours FROMPROJECTWHEREProjectID 1200 Ch03 SQLQuery29 DBMS产品在使用内置函数的方式也不一样 一般来说 内置函数是不能用于WHERE子句中的 以下语句不规范 SELECTProjectID MaxHoursFROMPROJECTWHEREMaxHours AVG MaxHours Ch03 SQLQuery30 8 SQL内置函数和分组 例1从EMPLOYEE表中查询 使用GROUPBY关键词统计每个部门的人数 SELECTDepartment Count ASNumberofEmployeeFROMEMPLOYEEGROUPBYDepartment Ch03 SQLQuery31 在SQL中 可以使用GROUPBY关键字对结果集按照公共值进行分组统计查询 统计查询结果 例2 从EMPLOYEE表中查询 使用GROUPBY关键词统计每个部门的人数 但只想获取有两个以上的成员组 操作语句 SELECTDepartment Count ASNumberofEmployeeFROMEMPLOYEEGROUPBYDepartmentHAVINGCOUNT 1 Ch03 SQLQuery32 说明 可以对形成的组使用HAVING子句应用某些条件 从而进一步限制所得结果 运行结果 例3 从EMPLOYEE表中查询 同时使用WHERE子句和HAVING子句条件查询EmployeeNumber小于或等于6的雇员 然后形成分组 最后应用HAVING条件 进行统计 操作语句 SELECTDepartment Count ASNumberofEmployeeFROMEMPLOYEEWHEREEmployeeNumber1 Ch03 SQLQuery33 说明 在GROUPBY语句中 可以同时使用WHERE子句和HAVING子句进行分组条件统计 运行结果 9 使用子查询处理多个表 例1想知道在某个任务中 工作时间超过50小时的所有雇员姓名 雇员名称存储在EMPLOYEE表中 但他们的工作时间则存储在ASSIGNMENT表中 SELECTFirstName LastNameFROMEMPLOYEEWHEREEmployeeNumberIN SELECTDISTINCTEmployeeNumberFROMASSIGNMENTWHEREHoursWorked 50 Ch03 SQLQuery36 有时候 需要从多个表进行查询 才能获得所需信息 运行结果 例2 查询Accounting部门的单个项目中 工作时间超过40小时的雇员姓名 操作语句 SELECTFirstName LastNameFROMEMPLOYEEWHEREEmployeeNumberIN SELECTDISTINCTEmployeeNumberFROMASSIGNMENTWHEREHoursWorked 40ANDProjectIDIN SELECTProjectIDFROMPROJECTWHEREDepartment Accounting Ch03 SQLQuery39 说明 子查询通过嵌套可以扩展到3层以上 运行结果 10 使用连接查询多个表 例1从EMPLOYEE表和ASSIGNMENT表关联查询 得到雇员任务工时列表 SELECTFirstName LastName HoursWorkedFROMEMPLOYEE ASSIGNMENTWHEREEMPLOYEE EmployeeNumber ASSIGNMENT EmployeeNumber Ch03 SQLQuery40 在处理多个表时 子查询只有在结果来自一个表的情况下才有用 但如果需要显示两个或多个表中的数据 就不能使用子查询 而需要采用连接操作 运行结果 例2 关联查询EMPLOYEE表和ASSIGNMENT表 得到雇员任务工时列表结果集 然后对结果集按雇员分组 统计他们的工作时间 操作语句 SELECTFirstName LastName SUM HoursWorked ASTotalHoursWorkedFROMEMPLOYEEASE ASSIGNMENTASAWHEREE EmployeeNumber A EmployeeNumberGROUPBYFirstName LastName Ch03 SQLQuery41 说明 连接本身其实也是一个表 同样可以采用SELECT语句操作 还可使用AS关键字将别名赋予表名 简化语句 运行结果 例3 关联查询EMPLOYEE表 PROJECT表和ASSIGNMENT表 得到雇员任务工时列表及其所在项目名称 操作语句 SELECTName FirstName LastName HoursWorkedFROMEMPLOYEEASE PROJECTASP ASSIGNMENTASAWHEREE EmployeeNumber A EmployeeNumberANDP ProjectID A ProjectIDORDERBYP ProjectID A EmployeeNumber Ch03 SQLQuery43 说明 前面给出的案例是两表连接实现查询 同样也可以多表关联实现查询 运行结果 11 SQLJOIN ON语法 在SELECT查询语句中 也可使用专门的连接JOIN ON关键字实现前面的连接操作 SELECTFirstName LastName HoursWorkedFROMEMPLOYEE ASSIGNMENTWHEREEMPLOYEE EmployeeNumber ASSIGNMENT EmployeeNumberORDERBYEMPLOYEE EmployeeNumber ProjectID Ch03 SQLQuery41 例1从EMPLOYEE表和ASSIGNMENT表关联查询 得到雇员任务时间列表 上述操作也可使用JOIN ON关键字实现 SELECTFirstName LastName HoursWorkedFROMEMPLOYEEJOINASSIGNMENTONEMPLOYEE EmployeeNumber ASSIGNMENT EmployeeNumberORDERBYEMPLOYEE EmployeeNumber ProjectID Ch03 SQLQuery44 运行结果 说明 同样 连接JOIN ON关键字也可用于多表关联操作 例2从EMPLOYEE表 PROJECT表和ASSIGNMENT表关联查询 得到雇员任务时间列表和项目名 上述操作也可使用JOIN ON关键字实现 操作语句 SELECTName FirstName LastName HoursWorkedFROMEMPLOYEEASE PROJECTASP ASSIGNMENTASAWHEREE EmployeeNumber A EmployeeNumberANDP ProjectID A ProjectIDORDERBYP ProjectID A EmployeeNumber Ch03 SQLQuery43 SELECTName FirstName LastName HoursWorkedFROMEMPLOYEEASEJOINASSIGNMENTASAONE EmployeeNumber A EmployeeNumberJOINPROJECTASPONA ProjectID P ProjectIDORDERBYP ProjectID A EmployeeNumber Ch03 SQLQuery45 运行结果 12 外部连接 针对前面的三表连接例子 若在PROJECT表中新增一条记录数据 如下语句完成操作 前节介绍的连接方式在SELECT查询语句称为内部连接 在一些特殊情况下 如关联表中一些行不匹配 部分数据就会丢失 INSERTINTOPROJECT Name Department MaxHours StartDate VALUES 2005Q4TaxPreparation Accounting 175 0 2005 12 10 Ch03 SQLQuery46 A 使用如下查询 查看ROJECT表中数据 SELECT FROMPROJECT 运行结果 当我们再次使用前面连接EMPLOYEE ASSIGNMENT PROJECT查询 SELECTName FirstName LastName HoursWorkedFROMEMPLOYEEASEJOINASSIGNMENTASAONE EmployeeNumber A EmployeeNumberJOINPROJECTASPONA ProjectID P ProjectIDORDERBYP ProjectID A EmployeeNumber Ch03 SQLQuery45 运行结果 其结果 与没有在PROJECT表中新增记录前一样 其原因是ASSIGNMENT表中不存在ProjectID为1500的匹配值 即内连接存在可能的数据丢失 上面问题可以通过使用外连接 LEFTJOIN或RIGHTJOIN 解决 SELECTName EmployeeNumber HoursWorkedFROMPROJECTLEFTJOINASSIGNMENTONPROJECT ProjectID ASSIGNMENT ProjectID Ch03 SQLQuery48 运行结果 一 修改数据 3 5修改和删除关系数据的SQL语句 1 语句基本格式 UPDATETableNameSETColumnName 列值WHERESomeCondition 例EMPLOYEE表当前数据如下 将该表中雇员编号为 11 的电话号码设置为 360 287 8810 操作语句 UPDATEEMPLOYEESETPhone 360 287 8810 WHEREEmployeeNumber 11 然后 再执行如下操作语句 SELECT FROMEMPLOYEE 运行结果 注意 在使用UPDATE语句时 不能忘记使用WHERE条件 否则将更新表中该列所有值 注意 UPDATE语句可以同时更新多个列的数据 操作语句 例 雇员表EMPLOYEE中5号员工从会计部门调入到财务部门 并且电话也变化为新部门电话 UPDATEEMPLOYEESETDepartment Finance Phone 360 285 8420 WHEREEmployeeNumber 5 然后 再执行如下操作语句 SELECT FROMEMPLOYEE 运行结果 二 删除数据 语句基本格式 DELETEFROMTableNameWHERESomeCondition 例1 从PROJECT表中删除 Marketing 部门的数据 DELETEFROMPROJECTWHEREDepartment Marketing 注意 1 在使用DELETE语句时 不但将PROJECT表中数据删除 也会级联删除ASSIGNMENT表中的相关数据 2 若忘记使用WHERE条件 否则将删除PROJECT表和ASSIGNMENT表中所有值 例2 从DEPARTMENT表中删除 Marketing 部门的数据 DELETEFROMDEPARTMENTWHEREDepartment Marketing 注意 若该表与其它表存在参照完整性约束 则不能删除 除非所依赖的子表已经删除了相关数据 一 DROPTABLE和ALTERTABLE语句 3 6修改和删除表和约束的SQL语句 1 DROPTABLE语句 语句基本格式 DROPTABLETableName 注意 语句将删除该表的所有数据及其结构 例 如果希望删除EMPLOYEE表及其数据 可以使用如下语句 DROPTABLEEMPLOYEE 注意 如果某表包含了参照完整性约束 直接执行DROPTABLE语句 将会报错 说明 删除EMPLOYEE表前 必须先删除ASSIGNMENT表或至少删除外键约束ASSIGN EMP FK 才能成功 2 ALTERTABLE语句 语句基本格式 ALTERTABLEConstraint ALTERTABLE语句可以用于添加 修改 删除列和约束 例 如果希望删除ASSIGNMENT表中的外键约束 可以使用如下语句 ALTERTABLEASSIGNMENTDROPCONSTRAINTASSIGN EMP FK 二 CHECK约束 在ALTTABLE语句中 可以添加CHECK关键字实现约束添加 例 如果希望在定义PROJECT表时 规定StartDate必须早于EndDate 则可通过如下语句实现 ALTERTABLEPROJECTADDCONSTRAINTPROJECT Check DatesCHECK StartDate EndDate 三 ALTERTABLE添加或删除列 在ALTTABLE语句中 可以添加或删除列 例 如果希望在PROJECT表中添加一列CurrentTotalHours 以跟踪在某项目上工作的实际小时数 则可通过如下语句实现 ALTERTABLEPROJECTADDCurrentTotalHoursNumeric 8 2 NULL 运行结果 例 如果希望在PROJECT表中删除CurrentTotalHours列 则可通过如下语句实现 ALTERTABLEPROJECTDROPCOLUMNCurrentTotalHours 运行结果 一 什么是视图 3 7SQL视图 视图 是一种通过其它表或视图构建的虚拟表 它本身没有自己的数据 而是使用了存储在表或其它视图中的数据 二 视图SQL定义 基本语句 CREATEVIEWViewNameASSELECTStatement 例1 定义一个关于EMPLOYEE表的名为EmployeePhoneView视图 CREATEVIEWEmployeePhoneViewASSELECTFirstName LastName PhoneASEmployeePhoneFROMEMPLOYEE 一但创建了视图之后 该试图就可以像表一样用于SELECT语句的FROM子句中 SELECT FROMEmployeePhoneViewORDERBYLastName 运行结果 三 SQL视图用途 1 隐藏列或行 例1 如果只希望提供部分不涉及敏感数据的部门信息给客户 可采用如下视图实现 CREATEVIEWBasicDepartmentDataViewASSELECTDepartmentName PhoneASDepartmentPhoneFROMDEPARTMENT 视图可以用来隐藏结果集的行或列 以达到阻止显示一些敏感数据 运行结果 运行结果 例2 如果只希望Marketing部门的项目数据显示在视图中 可采用如下视图实现 CREATEVIEWMarketingDepartmentProjectViewASSELECTProjectID NameASProjectName MaxHours StartDate EndDateFROMPROJECTWHEREDepartment Marketing 使用SELECT语句对该视图查询 SELECT FROMMarketingDepartmentProjectViewORDERBYProjectID 运行结果 运行结果 不需要输入计算表达式 就能显示出被计算列的结果 CREATEVIEWProjectHoursToDateViewASSELECTPROJECT ProjectID NameASProjectName MaxHoursASProjectMaxHours SUM HoursWorked ASProjectHoursWorkedToDateFROMPROJECT ASSIGNMENTWHEREPROJECT ProjectID ASSIGNMENT ProjectIDGROUPBYPROJECT ProjectID 2 显示被计算列的结果 例1 如果希望给出每个项目实际工时与最大工时比较 可采用如下视图实现 使用SELECT语句对该视图查询 SELECT FROMProjectHoursToDateViewORDERBYPROJECT ProjectID 运行结果 将复杂的SQL语句隐藏 给用户提供简单的操作语句 3 使用视图隐藏复杂的SQL语法 例1 如果希望给出哪些雇员分派到哪个项目 该雇员为项目实际工作时间 SELECTName FirstName LastName HoursWorkedFROMEMPLOYEEASEJOINASSIGNMENTASAONE EmployeeNumber A EmployeeNumberJOINPROJECTASPONA ProjectID P ProjectIDGROUPBYP ProjectID A EmployeeNumber 为简化访问操作 可以将前面复杂的SQL语句定义为视图隐藏起来 给用户提供简单的操作语句 CREATEVIEWEmployeeProjectHoursWorkedViewASSELECTName FirstName LastName HoursWorkedFROMEMPLOYEEASEJOINASSIGNMENTASAONE EmployeeNumber A EmployeeNumberJOINPROJECTASPONA ProjectID P ProjectIDGROUPBYP ProjectID A EmployeeNumber 使用SELECT语句对该视图查询 SELECT FROMEmployeeProjectHoursWorkedView 运行结果 在SQL语句中 不能将计算或内置函数作为WHERE子句的一部分 然而 可以通过构建计算变量视图解决 4 分层堆积计算和内置函数 例1 如果希望给出哪些项目超期 可以通过如下方式解决 第一步 定义ProjectHoursToDateView CREATEVIEWProjectHoursToDateViewASSELECTPROJECT ProjectID NameASProjectName MaxHoursASProjectMaxHours SUM HoursWorked ASProjectHoursWorkedToDateFROMPROJECT ASSIGNMENTWHEREPROJECT ProjectID ASSIGNMENT ProjectIDGROUPBYPROJECT ProjectID 第二步 在ProjectHoursToDateView视图中查询超期项目 SELECTProjectID ProjectName ProjectMaxHours ProjectHoursWorkedToDateFROMProjectHoursToDateViewWHEREProjectHoursWorkedToDate ProjectMaxHoursORDERBYProjectID 3 8Access项目实践 SQL查询 结合汽车经销商数据库WMCRM mdb实例 使用Access操作SQL查询学习如何使用AccessSQL学习通过使用SQL和QueryByExample在单表和多表中运行查询学习如何手工设置AccessSQL不支持的表和关系 一