第四章结构化查询语言

第四章结构化查询语言——SQL第1页共12页第四章结构化查询语言——SQLSQL概述数据的查询是最基本的操作。SQL语言是结构化查询语言的缩写，是一种标准的关系数据库查询语言。它用于对关系数据库中的数据进行存储、查询、更新等操作。一、SQL的历史与特点StructuredQueryLanguage结构化查询语言1974年提出，称为SEQUEL语言；75-79年，由IBM公司修改成SEQUEL2语言——目前的SQL语言86年，ANSI（美国国家标准协会）确定SQL为关系型数据库系统的工业标准，命名为SQL-8692年，SQL-92为目前最新版本的SQL语言99年，出版SQL3绝大多数流行的关系型数据库管理系统，如Oracle,Sybase,MicrosoftSQLServer,Access等都采用了SQL语言标准，但又都会有自己的一些扩充。二、SQL有两种使用方法：1、与用户交互的方式联机使用——交互式SQL；适用于非计算机专业人员，即最终用户即时查询。——称为自含型2、作为子语言嵌入到其他程序设计语言中使用。——宿主型SQL，适合于程序设计人员用高级语言编写应用程序。三、SQL的体系结构：1、SQL的体系结构：视图——全局模式——内模式外模式——视图（View）；全局模式称为模式，或叫数据库，由基本表组成；内模式由系统根据数据库模式自动实现，不需要用户过问。2、SQL术语基本表：每个关系又叫做基本表或表（Table），元组——行（Row），关系中的属性称为字段（Field）——列(Column)；视图：从一个或几个基本表或其它视图导出来的表。是个虚表，有自己独立的结构定义，但没有独立的数据存在，它的数据来源于基本表。四、SQL的主要功能是实现数据库查询，其他功能：DDL数据定义功能（DataDefinitionLanguage）：用于定义数据的结构，包括定义基本表、定义视图、定义索引三个部分。能够实现数据库的三级体系结构DML数据操纵(DataManipularionLanguage)：包括对基本表和视图的数据的操作。它的操作对象是元组的集合，其结果也是元组的集合。SQL是一各高度非过程化的面向集合的语言。DCL数据控制（DataControlLanguage）：用于定义数据库用户的权限，以保证系统的安全性。一般由DBA来使用。五、SQL特点：综合统一、高度非过程化、集合操作、自含与宿主、三级体系4。1数据库模式的建立和删除一、建立数据库模式第四章结构化查询语言——SQL第2页共12页1、语句格式：CREATE{SCHEMA|DATABASE}数据库名[AUTHORIZATION]所有者名2、语句说明：（1）功能：建立一个空数据库，缺省状态下，该数据库为创建者自己所有。加上授权语句，还可以将此数据库权交给其他人。（2）语句关键字不区分大小写。（3）括号内为必需项3、例P：73二、删除数据库模式1、语句格式：DROP{SCHEMA|DATABASE}数据库名2、语句说明：（1）删除已存在的数据库，包括其中所有对象。3、例P：744。2SQL语句对表的操作一、建立表结构：1、语句格式：CREATETABLE[数据库名.所有者名.]基本表名(列定义,….[,表级完整性控制,…])2、语句说明：（1）功能：在当前或给定的数据库中建立一个基本表结构（关系）。（2）表名自定，要符合命名规则。（3）列定义一般出现多个，即一个表的属性有多个。列定义至少包括列名列数据类型两项，必要性可以有该列的完整性控制说明。3、SQL中常用的数据类型二进制Binary(n)N+4字节N为1~8000这间的值，但最后用检索输出的是二进制表示二进数据长度基本相同时可以使用文本Text实际大小最大可存储231-1存储文本数据图形image实际大小最大可存储231照片、图、画整型Int4个字节-231~（231-1）内所有正负整数数存储可直接运算的数值Smallint2个字节-215~（215-1）内所有正负整数数Tinyint1个字节0~255范围的所有正整数浮点数据类型Real4个字节精确到7位小数存储十进制小数。采用只入不舍的方式存储。Float*8个字节精确到15位小数Decimal*Numeric实际存储空间Decimal（p,s）,p表示总位数，s表示小数点后的位数。字符型Char（n）N字节若输入的字符少于n,以空格填满。若超长，则会截掉。存储字符1-8000个日期与时间型Datetime8个字节（精度3.33ms）MMDDYYYYhh:mmAM/PM1753.1.1~9999.12.3123:59:59存储日期和时间的结合体,引用时用单引号Smalldatetime4个字节1900.1.1~2079.6.6,精确度1分钟第四章结构化查询语言——SQL第3页共12页货币型Money8个字节精确度为万分之一用于存储货币位型Bit1个字节只能输入0与1，非此值当为1常用作逻辑变量表真假A、只有字符与二进制数据类型需要指定长度，其他如整型、日期时间、浮点数据类型，定义时不用指定长度，也就是长度是默认值。B、不同的数据库系统支持的数据类型各不相同。在ACCESS中使用SQL的CreateTable语句，数据类型会做自动转换。4、列完整性约束：有六种（1）定义主键约束：PRIMARYKEY（2）默认值约束：DEFAULT常量表达式（3）单值约束（唯一性约束）：UNIQUE，即在该列上的所有取值必须互不相同，用在某一列不是主键但其值又是唯一的列上。（4）外码约束：references父表名(主码)（5）检查约束：check（逻辑表达式），用于对该列的取值做限制。（6）非空/空值约束：notnull/null，专明此列的值是否可以为空，默认是可空值。5、示例（1）createtabletu(总编号Char(6),分类号Char(8),书名Char(16),日期Datetime)注意：表名与括号之间用空格分开，字段名与类型之间也必须有空格，各字段之间用逗号分开，可以一行多列或一行列。系统默认字段长度可以省略。（2）CREATETABLE商品基本信息表(货号char(8),货名char(20),进口bit,品牌char(10),单价money);（3）CREATETABLE商品基本信息表(货号char(8)PRIMARYKEY,货名char(20)unique,进口bit,品牌char(10),单价moneycheck(单价100),供应商号char(10)references供应商表(供应商号));注：ACCESS不支持DEFAULT、check，其他都可以。6、表级完整性约束：有四种（1）主键约束:PRIMARYKEY(列名，。。。)（2）外部键约束：foreignkey(列名)references父表名(参照主码)（3）单值约束：UNIQE（列名。。。）（4）检查约束：CHECK（逻辑表达式）表级约束基本上与列级约束相同，但在写法上有一些差别，另外，表级约束可以一次对多个列定义。例：CREATETABLE商品基本信息表(货号char(8),货名char(20),进口bit,品牌char(10),单价moneycheck(单价500),供应商号char(10),PRIMARYKEY(货号,货名),第四章结构化查询语言——SQL第4页共12页foreignkey(供应商号)references供应商表(供应商号)UNIQUE（货名）);注意：外码必须与被参照关系中对应的主码具有完全相同的数据类型。每个约束都会由系统自动产生一个约束名。课本例P：76——77二、修改表结构：修改基本表是指对已经定义的基本表增加新的属性名（列）或删除，扩展关系模式1、语句格式：ALTERTABLE[数据库名.所有者名基本表名{ADD列定义,….|ADD表级完整性约束,….|DROPCOLUMN列名,…|DROP约束名,….}2、语句说明：（1）功能：可以增加也可以删除列或完整性约束（2）新增的列不能规定为空值；删除列前要先删除与该列有关的约束，一次增加或删除多个列时，只用一个ADD或DROP。3、altertabletuadd单价money,总价money;给图书表增加单价与总价列；alterabletudrop单价,总价;删除单价与总价三、删除基本表1、语句格式：DROPTABLE[数据库名.所有者名基本表名2、语句说明：（1）功能：删除一个表。该表的所有内容全部都删除。（2）如果该表与其他有关系，应该先删除关系才能删除表。3、例：DROPTABLETU四、表内容的操作：1、插入记录（数据）（1）单行插入记录语句格式：P：78insertinto表名[列名]values(各列值，用逗号分开)例：建立tu表createtabletu(总编号Char(6),分类号Char(8),书名Char(16),日期Datetime)在表中插入一条记录：insertintotu(总编号,分类号,书名,日期)values('0101','TP-1','计算机应用基础','1991-01-01')也可省略为：insertintotuvalues('0101','TP-1','计算机应用基础','1991-01-01')还可能只写入部分列的值：insertintotu(总编号,书名,日期)values('0102','计算机应用','1991-01-01')注意：对应列的值数据类型也要对应书写格式。如字符、日期要用‘’。（2）插入多行记录：可以利用SELECT语句从另一表中获取多条记录并加入到另一表中。语句格式：insert[into]表名[列名]SELECT子句说明：注意两表的字段应该一致。例：P：79第四章结构化查询语言——SQL第5页共12页2、修改记录（1）语句格式：P：80UPDATE表名SET列名=表达式[WHERE逻辑表达式]（2）功能：修改某些列的值，可按条件修改。例：图书表TU，设做如下操作：A、altertabletuadd库存量int,单价money,总价moneyB、insertintotu(总编号，库存量,单价)values(‘02001’,2,25)C、updatetuset总价=单价*库存量D、updatetuset书名=‘操作系统‘where总编号=‘02001’E、updatetuset分类号=‘TP34’where书名=‘操作系统‘例：课本P：80（1）、（2）——从其他表中获取数据。3、删除记录（1）语句格式P：81deletefrom表名where逻辑表达式（2）功能：删除一个表中满足条件的行（3）例：deletefromtuwhere库存量isnull——删除库存量为空的记录。例：课本P：81——82练习与作业：1、用create语句建立教师任课表（课程ID，课程名称，系（部门）ID，教师ID，学分，学时，考试类型）并作完整性约束（可用列完整性也可用表完整性）课程ID为主键，考试类型默认值为“闭卷”，系（部门）ID是系（部门）表的外码，教师ID是教师情况表的外码。2、说明下面语句的功能：(1)createtableemlpoyee(first_nchar(9)notnullprimarykey,last_nchar(8),ageint,citychar(20));(2)altertableemlpoyeeaddaddresschar(30),sexchar(2)default‘男’;4。4视图的建立视图：依赖基本表而产生的虚表，可以是一个几个基本表的映象。视图的建立与删除不影响基本表，但视图内记录的更新会影响基本的内容。视图能够增加数据库的安全性，因为视图的使用可以授权。一、建立视图：1、语句格式P：82createview视图名(列名表)asselect列名表from基本表名2、功能：当前数据库中根据SELECT子句的查询结果建立一个视图，视图包括结