第12章数据库操作基础

1、第12章数据库操作基础数据库操作基础第12章数据库操作基础目标n关系数据库nSQL语言第12章数据库操作基础关系数据库的结构第12章数据库操作基础一、关系数据模型的数据结构 n在用户观点下，关系模型中数据的逻辑结构是一张二维表，它由行和列组成。学 号姓 名年 龄性 别系 名年 级王小明19女社会学2005黄大鹏20男商品学2005张文斌18女法律2005属性属性元组元组第12章数据库操作基础关系数据模型的数据结构n关系（Relation）一个关系对应通常说的一张表n元组（Tuple）表中的一行即为一个元组n属性（Attribute） 表中的一列即为一个属性，给每一个属性起一个名称即属性名n主码

2、（Key）表中的某个属性组，它可以唯一确定一个元组第12章数据库操作基础关系数据模型的数据结构n域（Domain）属性的取值范围n分量元组中的一个属性值n关系模式对关系的描述关系名（属性1，属性2，属性n）第12章数据库操作基础关系数据模型的数据结构n关系必须是规范化的，满足一定的规范条件关系必须是规范化的，满足一定的规范条件最基本的规范条件：关系的每一个分量必须是一个不可分的数据项, 不允许表中还有表 职工号姓名职 称工 资扣 除实 发基 本津 贴职务房 租水 电86051陈 平讲 师13051200501601122283图图1.27 一个工资表一个工资表(表中有表表中有表)实例实例 第1

3、2章数据库操作基础关系数据模型的数据结构(术语对比)关系术语一般表格的术语关系名表名关系模式表头（表格的描述）关系（一张）二维表元组记录或行属性列属性名列名属性值列值分量一条记录中的一个列值非规范关系表中有表（大表中嵌有小表）第12章数据库操作基础二、关系数据模型的操纵与完整性约束n数据操作是集合操作，操作对象和操作结果都是关系,都必须满足关系的完整性约束条件n查询n插入n删除n更新n数据操作是集合操作，操作对象和操作结果都是关系，即若干元组的集合n存取路径对用户隐蔽，用户只要指出“干什么”，不必详细说明“怎么干”第12章数据库操作基础关系数据模型的操纵与完整性约束n关系的完整性约束条件 n实

4、体完整性规定基本关系的所有主属性都不能取空值n参照完整性规定基本关系的主属性可以取空值或目标关系中已经存在的值n用户定义的完整性针对某一具体关系数据库的约束条件反映某一具体应用所涉及的数据必须满足的语义要求第12章数据库操作基础三、关系数据模型的存储结构n实体及实体间的联系都用表来表示n表以文件形式存储n有的DBMS一个表对应一个操作系统文件n有的DBMS自己设计文件结构第12章数据库操作基础SQL语言nSQL特点nSQL分类nMysql中常用的数据类型nSQL主要语句语法n从Mysql中导入和导出数据库结构第12章数据库操作基础SQL概述nSQLStructured Query Langua

5、genSQL的功能：数据定义、操纵和控制。nSQL的特点n综合统一n高度非过程化n面向集合的操作方式n语言简捷，易学易用nSQL语言支持关系数据库三级模式结构第12章数据库操作基础n1SQL（Structured Query Language）全称是结构化查询语言，是一种关系数据库语言，提供数据的定义、查询、更新和控制等功能。功能强大、能够完成各种数据库操作。n2SQL语言不是一个应用程序开发语言，它只提供对数据库的操作能力，不能完成屏幕控制、菜单管理、报表生成等功能。SQL特点第12章数据库操作基础SQL特点n3有利于各种数据库之间交换数据、有利于程序的移植、有利于实现程序和数据间的独立性；

6、有利于实施标准化；n4书写简单、易学易用。 第12章数据库操作基础n1、数据定义语言（DDL：Data Definition Language）n创建、修改或删除数据库中各种对象，包括表、视图、索引等。n2、查询语言（QL：Query Language）n按照指定的组合、条件表达式或排序检索已存在的数据库中的数据，但并不改变数据库中数据。SQL分类第12章数据库操作基础SQL分类n3 、 数 据 操 纵 语 言 （ D M L ： D a t a Manipulation Language ）n对已经存在的数据库进行记录的插入、删除、修改等操作第12章数据库操作基础n4、数据控制语言（DCL：

7、Data Control Language）n用来授予或收回访问数据库的某种特权、控制数据操纵事务的发生时间及效果、对数据库进行监视n注意：在书写各种SQL 命令时，命令中所涉及的标点符号，如括号、逗号、分号、圆点（英文句号）等都应是英文半角，如果写成中文全角符号，则会在执行命令时出错。 SQL分类第12章数据库操作基础n数值类型 (例如: Float,Int,Double 等)n日期和时间类型 (例如: Date,Time等)nString类型 (例如: Char,Varchar)Mysql常用的数据类型：第12章数据库操作基础nMySQL支持所有标准SQL数值数据类型。n这些类型包括严格数

8、值数据类型(INTEGER (Integer)、SMALLINT (SmallInt) 等n以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。n关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。数值类型第12章数据库操作基础数值类型n作为SQL标准的扩展，MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT 第12章数据库操作基础第12章数据库操作基础nFloat (同义real) 小(单精度)浮点数。 允许的值是-3.402823466E+38到-1.175494351E-38、0和1.175494351E-38到3

9、.402823466E+38。n Double(同义DOUBLE PRECISION) 普通大小(双精度)浮点数。允许的值是-1.7976931348623157E+308到-2.22514E-308、0和2.22514E-308到 1.7976931348623157E+308第12章数据库操作基础日期和时间类型n表示时间值的DATE和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。每个时间类型有一个有效值范围和一个“零”值，当指定不合法的MySQL不能表示的值时使用“零”值。TIMESTAMP类型有专有的自动更新特性 第12章数据库操作基础日期和时间类型-Da

10、tenDATEn日期。支持的范围为1000-01-01到9999-12-31。MySQL以YYYY-MM-DD格式显示DATE值，但允许使用字符串或数字为DATE列分配值。第12章数据库操作基础日期和时间类型-Datetimen DATETIMEn日期和时间的组合。支持的范围是1000-01-01 00:00:00到9999-12-31 23:59:59。MySQL以YYYY-MM-DD HH:MM:SS格式显示DATETIME值，但允许使用字符串或数字为DATETIME列分配值。第12章数据库操作基础日期和时间类型-TIMESTAMPn TIMESTAMP (Mysql 4.1版本以下）n时

11、间戳。范围是1970-01-01 00:00:00到2037年。nTIMESTAMP列用于INSERT或UPDATE操作时记录日期和时间。如果你不分配一个值，表中的第一个TIMESTAMP列自动设置为最近操作的日期和时间。也可以通过分配一个NULL值，将TIMESTAMP列设置为当前的日期和时间。nTIMESTAMP值返回后显示为YYYY-MM-DD HH:MM:SS格式的字符串，显示宽度固定为19个字符第12章数据库操作基础日期和时间类型-Timen TIMEn时间。范围是-838:59:59到838:59:59。MySQL以HH:MM:SS格式显示TIME值，但允许使用字符串或数字为TIM

12、E列分配值。第12章数据库操作基础日期和时间类型-YearnYEAR(2|4)n两位或四位格式的年。默认是四位格式。在四位格式中，允许的值是1901到2155和0000。在两位格式中，允许的值是70到69，表示从1970年到2069年。MySQL以YYYY 格式显示YEAR值，但允许使用字符串或数字为YEAR列分配值。第12章数据库操作基础日期和时间类型n下面的表显示了各类“零”值的格式。请注意如果启用NO_ZERO_DATE SQL模式，使用这些值会产生警告。第12章数据库操作基础可以使用任何常见格式指定DATETIME、DATE和TIMESTAMP值：n YYYY-MM-DD HH:MM:

13、SS或YY-MM-DD HH:MM:SS格式的字符串。允许“不严格”语法：任何标点符都可以用做日期部分或时间部分之间的间割符。例如，98-12-31 11:30:45、98.12.31 11+30+45、98/12/31 11*30*45和981231 113045是等价的。第12章数据库操作基础n YYYYMMDDHHMMSS或YYMMDDHHMMSS格式的没有间割符的字符串，假定字符串对于日期类型是有意义的。例如，19970523091528和970523091528被解释为1997-05-23 09:15:28，但971122129015是不合法的(它有一个没有意义的分钟部分)，将变为0

14、000-00-00 00:00:00。第12章数据库操作基础nYYYYMMDDHHMMSS或YYMMDDHHMMSS格式的数字，假定数字对于日期类型是有意义的。例如，19830905132800和830905132800被解释为 1983-09-05 13:28:00。 第12章数据库操作基础n YYYY-MM-DD或YY-MM-DD格式的字符串。这里也允许使用“不严格的”语法。例如，98-12-31、98.12.31、98/12/31和981231是等价的。nYYYYMMDD或YYMMDD格式的没有间割符的字符串，假定字符串对于日期类型是有意义的。例如，19970523和970523被解释为

15、 1997-05-23，但971332是不合法的(它有一个没有意义的月和日部分)，将变为0000-00-00。 第12章数据库操作基础n YYYYMMDD或YYMMDD格式的数字，假定数字对于日期类型是有意义的。例如，和830905被解释为1983-09-05。第12章数据库操作基础n无效DATETIME、DATE或者TIMESTAMP值被转换为相应类型的“零”值(0000-00-00 00:00:00、0000-00-00或者000)。第12章数据库操作基础String类型n字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM等。 第12章数据

16、库操作基础String类型- CHAR和VARCHAR类型 n CHAR和VARCHAR类型声明的长度表示你想要保存的最大字符数。例如，CHAR(30)可以占用30个字符。 nCHAR列的长度固定为创建表时声明的长度。最大长度为 8,000 个字符。 n VARCHAR列中的值为可变长字符串。最大长度为 8,000 个字符。 第12章数据库操作基础n下面的表显示了将各种字符串值保存到CHAR(4)和VARCHAR(4)列后的结果，说明了CHAR和VARCHAR之间的差别：第12章数据库操作基础String类型- BINARY和VARBINARY类型 nBINARY和VARBINARY类类似于C

17、HAR和VARCHAR n它们包含字节字节字符串而不是字符字符串。 nBINARY和VARBINARY允许的最大长度一样，如同CHAR和VARCHAR，不同的是BINARY和VARBINARY的长度是字节长度而不是字符长度。第12章数据库操作基础String类型-BLOB和TEXT类型 n可以容纳可变数量的数据 nBLOB 列被视为二进制字符串(字节字符串)。TEXT列被视为非二进制字符串(字符字符串)。 nText数据类型的列可用于存储大于8kb的字符。第12章数据库操作基础String类型-ENUM类型 nENUM是一个字符串对象，其值来自表创建时在列规定中显式枚举的一列值。 nENUM(

18、value1,value2,.)nENUM列最多可以有65,535个截然不同的值.第12章数据库操作基础SQL主要语句语法n MySQL实用工具语句（DESCRIBE语法 ，USE语法）n数据定义语句（创建，删除，修改数据库和表）n数据操作语句 （对表中的数据进行查询，修改，删除等操作）第12章数据库操作基础SQL主要语句语法MySQL实用工具语句nUSE语法 nUSE db_name nUSE db_name语句可以通告MySQL把db_name数据库作为默认（当前）数据库使用，用于后续语句。该数据库保持为默认数据库，直到语段的结尾，或者直到发布一个不同的USE语句：第12章数据库操作基础S

19、QL主要语句语法MySQL实用工具语句nDESCRIBE语法（获取有关列的信息） DESCRIBE可以提供有关表中各列的信息。也就是创建的表中各个属性的名字和数据类型等信息 第12章数据库操作基础SQL主要语句语法数据定义语句nCREATE DATABASE语法nCREATE TABLE语法 nCREATE INDEX语法 nDROP DATABASE语法 nDROP INDEX语法 nDROP TABLE语法 nALTER TABLE语法 nRENAME TABLE语法 第12章数据库操作基础CREATE DATABASE语法nCREATE DATABASE用于创建数据库，并进行命名。如果要

20、使用CREATE DATABASE，您需要获得数据库CREATE权限。CREATE DATABASE db_name ；例如：Create Database test1;第12章数据库操作基础CREATE TABLE语法nCREATE TABLE ( 列级完整性约束条件 , 列级完整性约束条件) ,)； 第12章数据库操作基础n注：列级约束条件有两个任选项：nNOT NULL，表示此列不得置NULL，在其后还可加UNIQUE任选项，表示列值不得重复。nDEFAULT，当此列的值空缺时，填以缺省值例如：stu_id char(20) not null,price float default 0.

21、01 第12章数据库操作基础表级完整性约束条件n主关键字约束指定表的一列或几列的组合的值在表中具有惟一性，即能惟一地指定一行记录。n每个表中只能有一列被指定为主关键字，且IMAGE 和TEXT 类型的列不能被指定为主关键字，也不允许指定主关键字列有NULL 属性。 语法如下：第12章数据库操作基础nCONSTRAINT constraint_namePRIMARY KEY （column_name）n各参数说明如下：constraint_name指定约束的名称约束的名称。在数据库中应是惟一的。如果不指定，则系统会自动生成一个约束名。 column_name指定组成主关键字的列名。第12章数据库

22、操作基础nCreate table products( id char(20) not null, price float default 0.01, constraint primary key (id) );第12章数据库操作基础n定义外关键字约束的语法如下：CONSTRAINT FOREIGN KEY （column_name1, column_name2,column_name16）REFERENCES ref_table （ref_column1,ref_column2, ref_column16 ） ON DELETE CASCADE | NO ACTION ON UPDATE C

23、ASCADE | NO ACTION n各参数说明如下： REFERENCES指定要建立关联的表的信息。 ref_table指定要建立关联的表的名称。 ref_column指定要建立关联的表中的相关列的名称。 第12章数据库操作基础nON DELETE NO ACTION|RESTRICT指定在删除表中数据时，对关联表所做的相关操作。在子表中有数据行与父表中的对应数据行相关联的情况下， NO ACTION 是缺省值. RESTRICT：凡被基表引用的主键，不得删除nON UPDATE NO ACTION|RESTRICT 指定在更新表中数据时，对关联表所做的相关操作。在子表中有数据行与父表中的

24、对应数据行相关联的情况下, NO ACTION 是缺省值. RESTRICT：凡被基表引用的主键，不得删除第12章数据库操作基础ncreate table orders(order_id char(8),p_id char(8),p_name char(10) ,constraint primary key (order_id) ,foreign key(p_id) references products(p_id)on delete restrict) ;第12章数据库操作基础CREATE INDEX语法nCREATE INDEX index_name ON tbl_name (index_

25、col_name,.) 注： index_col_name: col_name (length) ASC | DESC 例如：create index stu_age on student(age ASC);第12章数据库操作基础DROP DATABASE语法nDROP DATABASE db_name 删除已有的数据库第12章数据库操作基础DROP INDEX语法nDROP INDEX index_name ON tbl_name n删除表中的索引第12章数据库操作基础DROP TABLE语法 n删除表nDrop table table_name;第12章数据库操作基础ALTER TABLE

26、语法nALTER TABLE tbl_name ADD完整性约束 DROP列名 MODIFY；nADD用于增加新列和新的完整性约束条件，新增加的列一律为空值。(index, primary key等）nDROP用于删除指定的完整性约束条件（index,primary key, foreign key)nMODIFY用于修改原有的列定义第12章数据库操作基础nAlter table aa add primary key(id);nAlter table aa add name char(20);nAlter table aa drop primary key;nAlter table aa mo

27、dify id char(100) not null;第12章数据库操作基础RENAME TABLE语法nRENAME TABLE tbl_name TO new_tbl_name 对一个表重命名例如：rename table student to ss;第12章数据库操作基础创建，删除视图(view)n创建视图语法：Create view ( ,.) As select查询语句n删除视图语法：Drop view 视图名第12章数据库操作基础nCreate view stu_test(id,name) As select stu_id,name from student;nDrop view

28、stu_test;第12章数据库操作基础SQL主要语句语法-数据操作语句nSelectnUpdatenInsertnDelete第12章数据库操作基础Select查询语句nSELECT ALL|DISTINCT , FROM, WHERE GROUP BY HAVING ORDER BYASC|DESC；第12章数据库操作基础Select查询语句nSELECT指明需要查询的项目，一般是列名，也可以是表达式，查询表中未存储但可导出的结果。nWHERE用来说明查询的条件nDISTINCT任选项用来消除查询结果的重复项nGROUP用来将结果按列名1的值进行分组，该属性列值相等的元组为一组nORDER将结果表按列名2的值升序或降序排序第12章数据库操作基础where查询满足条件的元组（where 子句）n比较大小n确定范围（