数据库第四章1数据库定义语言课件

1、数据库原理及应用Database Theory and Application第4章 关系数据库标准语言SQL学习目标： 熟悉SQL语言中各种语句的语法，熟悉SQL数据定义语言（DDL）语句；掌握SQL语言中数据查询、数据操纵语言、数据控制语言的详细语法，并能深刻理解、综合应用，以便为今后深层次的学习打下更加坚实的基础。教学内容4.1 SQL语言概述4.2 数据定义语言4.3 数据查询语言4.4 数据更新4.5 SQL的数据控制功能4.6 视图4.1 SQL语言概述 SQL是Structure Query Language的缩写，即结构化查询语言，是一种介于关系代数与关系演算之间的语言。它是一

2、种用来与关系数据库管理系统通信的标准计算机语言。 SQL语言主要由数据查询、数据操纵、数据定义和数据控制四个方面功能组成。是一个通用的、功能极强的关系数据库语言。 目前已成为关系数据库的标准语言。各种关系数据库必须支持SQL-92标准。 4.1 SQL语言概述4.1.1 SQL的发展4.1.2 SQL语言的特点4.1.3 SQL的基本概念4.1.1 SQL的发展SQL语言是1974年由Boyce和Chamberlin提出,1975年至1979年IBM公司San Jose Research Laboratory研制的关系数据库管理系统原型系统System R实现了这种语言。1986 年由美国国家

3、标准局(ANSI)公布，所以也称SQL86标准，1987年国际标准化组织(ISO) 也通过了这一标准，作为关系数据库的标准语言。 1989年ISO第二次公布了SQL标准(SQL89标准)目前新的SQL标准是1992年制定的SQL92国际标准，在1993年获得通过，简称SQL92。 4.1.1 SQL的发展在SQL2基础上，增加了许多新特征，产生了SQL3标准，表示第三代SQL语言，在1999年提出，名字改成SQL：1999 。 大多数数据库均用SQL作为共同的数据存取语言和标准接口，使不同数据库系统之间的互操作有了共同的基础。但各厂家又在SQL标准的基础上进行扩充，形成自己的语言。 大多数数据

4、库开发商支持SQL92,但并不是百分之百符合这个标准。目前市场上存在一些不同风格的SQL,因为每一个RDBMS开发商都试图扩展这个标准，以增强产品的功能，提高产品的吸引力，因此各个DBMS产品在实现标准SQL语言时也各有差别，读者在使用某个DBMS产品时，应仔细阅读系统提供的有关手册。4.1.1 SQL的发展标准 大致页数 发布日期SQL/86 1986.10SQL/89(FIPS 127-1) 120页 1989年SQL/92 622页 1992年SQL99 1700页 1999年SQL2003 2003年4.1.2 SQL语言的特点综合统一集数据定义语言（DDL），数据操纵语言（DML），

5、数据控制语言（DCL）功能于一体。可以独立完成数据库生命周期中的全部活动：定义关系模式，插入数据，建立数据库； 对数据库中的数据进行查询和更新； 数据库重构和维护 数据库安全性、完整性控制等用户数据库投入运行后，可根据需要随时逐步修改模式，不影响数据的运行。数据操作符统一4.1.2 SQL语言的特点高度非过程化非关系数据模型的数据操纵语言“面向过程”，必须制定存取路径SQL只要提出“做什么”，无须了解存取路径。存取路径的选择以及SQL的操作过程由系统自动完成。4.1.2 SQL语言的特点面向集合的操作方式非关系数据模型采用面向记录的操作方式，操作对象是一条记录SQL采用集合操作方式 操作对象、

6、查找结果可以是元组的集合一次插入、删除、更新操作的对象可以是元组的集合4.1.2 SQL语言的特点以同一种语法结构提供两种使用方式SQL是独立的语言能够独立地用于联机交互的使用方式SQL又是嵌入式语言SQL能够嵌入到高级语言（例如C，C+，Java）程序中，供程序员设计程序时使用4.1.2 SQL语言的特点语言简洁，易学易用 SQL功能极强，完成核心功能只用了9个动词。4.1.2 SQL语言的的基本概念SQL视图2视图1基本表2基本表1基本表3基本表4存储文件2存储文件1外模式模 式内模式SQL支持关系数据库三级模式结构4.1.2 SQL语言的的基本概念 基本表本身独立存在的表SQL中一个关系

7、就对应一个基本表一个(或多个)基本表对应一个存储文件一个表可以带若干索引 存储文件逻辑结构组成了关系数据库的内模式物理结构是任意的，对用户透明 视图从一个或几个基本表导出的表数据库中只存放视图的定义而不存放视图对应的数据视图是一个虚表用户可以在视图上再定义视图4.1.2 SQL语言的的基本概念 数据定义功能 通过DDL（DDL Data Definition Language）语言来实现。可用来支持创建、删除、修改数据库对象（如数据库、表、索引、视图等）等。定义关系数据库的模式、外模式、内模式。常用DDL语句包含的动词有：CREATE、ALTER、 DROP三个。 数据操纵功能 数据操纵功能通

8、过DML（DATA Mainpulation Language）语言来实现，广义的DML包括数据查询和数据更新两种语句，数据查询指对数据库中的数据进行查询、统计、排序、分组、检索等操作。数据更新指对数据的插入、删除、修改等操作。狭义的DML指数据更新。 数据控制功能 数据库的数据控制功能指数据的安全性、完整性和事务控制功能。SQL语言中主要通过（GRANT、REVOKE）几个动词实现安全性中的权限控制，称之为数据控制语言DCL(Data Control Language）。4.2 数据定义语言4.2.1 定义基本表4.2.2 完整性约束的实现4.2.3 索引的定义与维护示例说明学生-课程模式

9、S-T 学生 Student(Sno,Sname,Ssex,Sage,Sdept)课程表Course(Cno,Cname,Cpno,Ccredit)学生选课表SC(Sno,Cno,Grade) 示例说明Student表示例说明Course表示例说明SC表4.2 数据定义语言 SQL的数据定义功能: 数据库定义、表定义、视图和索引的定义 定义数据库(1)定义数据库 例1定义一个学生-课程数据库 CREATE DATABASE STUDENT;定义一个数据库student 在student数据库中可以定义该数据库包含的对象，例如基本表、视图、索引等。定义数据库数据库命名规则：1、不能与其他数据库重

10、名；2、名称由任意字母、数字、_、$组成，可以使用上述任意字符开头，但不能使用单独的数字，否则造成与数值混淆；3、名字最长64个字符，别名最多256个字符；4、不能使用MySQL关键字作为数据库名、表名；5、默认情况下，Windows下数据库名、表名大小写不敏感，而Linux下数据库名、表名大小写敏感。为了便于数据库在平台间进行移植，建议用小写定义数据库名和表名。(2)删除数据库DROP DATABASE 数据库名；删除数据库的同时把该数据库中所有的数据库对象全部删除注意：当该数据库中没有任何下属的对象时才能执行，否则会将数据库中所有对象（包含数据）全部删除。例2 DROP database

11、student； 删除数据库student 同时该数据库中定义的表也被删除定义数据库(3)查看数据库Show databases;(4)选择数据库use 数据库名；在对MySQL表进行操作之前，必须使用use语句选择数据库，才可在指定数据库对表进行操作定义数据库MySQL存储引擎类型MySQL中的数据用各种不同的技术存储在文件或内存中。这种不同的技术以及配套的相关功能在MySQL中称为存储引擎（也称作表类型）。Show engines;Show enginesgShow enginesG 有序显示结果Show variables like have%Show variables like %s

12、torage_engine% 查看默认存储引擎My.ini 可以修改默认存储引擎InnoDB default给MySQL表提供事务、回滚、崩溃修复能力、多版本并发控制的事务安全。支持自动增长列 auto_increment，自增列不能为空，值唯一，必须是主键。支持外键 foreign key创建表存在.frm文件中，数据和索引存储在innodb_data_home_dir和innodb_data_file_path定义的表空间中。优势在于提供良好的事务管理、崩溃修复能力和并发控制。缺点是读写效率稍差，占用空间相对较大。MEMORY在内存中创建表，且数据存储在内存中，有利于数据快速处理，提高整个

13、表处理效率。表的大小受限制，取决于两个参数max_rows和max_heap_table_size。每个基于MEMORY存储引擎的表实际对应一个磁盘文件。该文件的文件名和表名相同，类型是frm类型。该文件只存储表的结构，其数据文件也存在内存中。服务器需要足够的内存维持MEMORY存储引擎的表使用，如果表不需要使用，可以释放内存，甚至可以删除不需要的表。MyISAM基于ISAM存储引擎发展，存储成三个文件。文件名和表名相同，扩展名包括frm文件存储表的结构，myd文件存储数据,myi文件存储索引。优势占用空间小，处理速度快。缺点是不支持事务的完整性和并发性。如何选择存储引擎支持事务处理、外键选择

14、innoDB插入数据快，空间和内存使用比较低，主要插入新数据和读出记录可以选择MyISAM需要数据处理快，对表大小要求不是很严格可以选择MEMORY4.2.1 基本表的定义 使用CREATE TABLE 语句，用户可以创建一个表结构，还可以为某个域设定缺省值。定义基本表 CREATE TABLE ( 列级完整性约束条件 ， 列级完整性约束条件. , );语法说明表名：所要定义的基本表的名字列名：表由一个或多个属性(列)组成。建表时通常需要定义列信息及每列所使用的数据类型，列名在表内必须为唯一的，一个表至少包含一个列。数据类型：定义表的各个列（属性）时需指明其数据类型和长度，不同的数据库管理系统

15、支持的数据类型不完全相同，应根据实际使用的DBMS来确定。列级完整性约束条件：只应用到一个列的完整性约束条件表级完整性约束条件：应用到多个列的完整性约束条件。4.2.1 基本表的定义 MySQL支持的数据类型：数字类型、字符串类型、日期和时间类型。数字类型MySQL支持所有的ANSI/ISO SQL 92数字类型。这些类型包括准确数字的数据类型还包括近似数字的数据类型。系统提供的数据类型数字数据整型数据：存储整数系统提供的数据类型数字数据小数数据：包含存储在最小有效数上的数据数字类型选择原则：1、选择最小的可用类型，如果数值不超过127，tinyint比int强；2、完全都是数字的可以选择整数

16、类型；3、浮点类型用于可能具有小数部分的数，例如货物单价、网上购物交付金额等。系统提供的数据类型字符数据字符型数据用于存储字符串，字符串中可包括字母、数字和其他特殊符号（如#、&等）。在输入字符串时，需将串中的符号用单引号或双引号括起来，如abc、AbcCde。char PK varchar创建表时指定其最大长度，字符串类型（M）CHAR类型长度是固定的，创建表时指定，长度可以是0-255的任意值。VARCHAR类型长度是可变的，创建表时指定其最大长度（0-65535），实际占用空间为字符串实际长度加1，因为字符串的结束标识符占用1个字节，有效节约系统空间。123 Char类型将数字后空格自动

17、删除，varchar会将最后的空格保留。TextTEXT只能保存字符数据，tinytext、text、mediumtext、longtext根据需求选取既能满足需要又节约空间的类型enum枚举类型，以列表的形式指定取值范围。基本格式：属性名 enum(value1,value2,valuen)，其中属性名参数指定字段名称，值末尾空格会被系统直接删除。Enum类型的值只能取列表中一个元素，其取值列表中最多有65535个值，每个值都有一个顺序排列的编号，mysql中存入的是这个编号，而不是列表中的值。Enum类型加上not null，默认值为取值列表第一个元素，如果不加not null，可以插入n

18、ull，且null为默认值。set属性名 set(value1,value2,valuen)，其中属性名参数指定字段名称，值末尾空格会被系统直接删除。Set类型的值可以取列表中一个元素或者多个元素的组合。取多个元素时，不同元素之间用逗号隔开。Set类型的值最多只能是64个元素构成的组合。列表中每个值都有一个顺序排列的编号，mysql中存入的是这个编号，而不是列表中的值。插入记录时，set字段元素顺序无关紧要，存入mysql数据库后，数据库系统会自动按照定义时顺序显示。字符类型选择原则1、速度考虑，要选择固定的列，可以使用char类型；2、空间考虑，使用动态的列，可以使用varchar类型；3、

19、将列中的内容限制在一种选择，可使用enum类型；4、允许在一个列中有多于一个的条目，使用set类型；5、搜索内容不区分大小写，使用text类型；6、搜索内容区分大小写，使用blob类型。系统提供的数据类型日期和时间数据YEAR使用一个字节表示年份，YYYY（1901-2155），输入格式字符串YYYY或者数字YYYY使用2位字符串表示。00到69转换为2000到2069，70到99转换为1970到1999。输入00和0效果是一样的，是2000。使用2位数字表示。1-69转换为2001-2069，70-99转换为1970-1999，注意2位字符串和2位数字是不一样的。插入0，是0000。 TIM

20、E 使用3个字节表示时间HH:MM:SSD HH:MM:SS D表示天数，范围是0-34。保存时，小时的值等于D*24+HH。输入2 11:30:50，TIME类型会转换为59:30:50。输入时可以不严格按照这个格式，可以是HH:MM:SS、 D HH:MM、 D HH、 SS等。例如输入30，Time类型会转换为00:00:30。HHMMSS格式的字符串或者数值。输入值是345454或345454，TIME类型转换为34:54:54；输入0或0，TIME类型转换为0000:00:00。Current_time、now()获取系统当前时间。 DATE使用4个字节表示日期，YYYY-MM-DD

21、 范围1000-01-01 9999-12-31YYYY-MM-DD或YYYYMMDD格式的字符串表示MySQL还支持一些不严格的语法格式，任何标点都可以用做间隔符。例如YYYY/MM/DD、YYYYMMDD。YY-MM-DD或者YYMMDD格式的字符串表示。YY取值00到69转换为2000到2069，70到99转换为1970到1999。YYYYMMDD或YYMMDD表示。YY取值00到69转换为2000到2069，70到99转换为1970到1999。注意输入0，是0000-00-00。Current_date或now()输入系统当前时间。DATETIME使用8个字节表示日期和时间。YYYY-

22、MM-DD HH:MM:SSYYYY-MM-DD HH:MM:SS或者YYYYMMDDHHMMSS格式字符串表示，范围1000-01-01 00:00:00 -9999-12-31 23:59:59。MySQL还支持一些不严格的语法格式，任何标点都可以用做间隔符。例如YYYY/MM/DD HH*MM*SS,这与DATE类型相同，与TIME类型不同，TIME类型只能用：分割。YY-MM-DD HH:MM:SS或者YYMMDDHHMMSS格式字符串表示。YY取值00到69转换为2000到2069，70到99转换为1970到1999。YYYYMMDDHHMMSS或YYMMDDHHMMSS等数字格式，

23、注意输入0，是0000-00-00 00:00:00。Now()获取系统当前时间。TIMESTAMP使用4个字节表示日期和时间，1970-01-01 08:00:012038-01-19 11:14:07YYYY-MM-DD HH:MM:SSTIMESTAMP类型范围比DATETIME小，根据时区显示时间使用current_timestamp、now()获取系统当前时间插入为空或者null时，显示系统当前时间系统提供的数据类型（续）二进制数据Binary pk varbinaryBinary长度是固定的，创建表时指定，不足的长度由“0”补全。Varbinary 长度是可变的，创建表时指定其最大

24、长度。bit创建表时指定最大长度，bit(m)，m指定该二进制的最大字节长度为m，m最大值64。Bit(4)存储0-15（1111）。查询bit类型数据时，使用bin（字段名+0）将值转换为二进制显示。blob特殊二进制类型，保存数据量很大的二进制数据。Tinyblob、blob、mediumblob、longblob。bolb类型与text类型类似，不同在于blob类型用于存储二进制数据。Blob类型数据是根据二进制编码进行比较和排序，text是根据文本模式进行比较和排序。选择数据类型的指导原则整数类型和浮点数类型：区别在于能否表达小数，double精度比float精度高，如果需要精确到小数

25、点后10位以上，选择double类型。浮点数和定点数类型：浮点数进行四舍五入时不会报警，定点数会报警。未指定精度情况下，浮点数和定点数有默认精度。Float和double保存实际精度，decimal默认整数位10，小数位0。对精度要求较高选择定点数。Char和varchar：区别在于长度是否可变。Varchar长度变化比较大的最好选择varchar，char类型占用空间大，但处理速度快。对应长度变化不大且速度要求高的选择char类型。时间和日期类型：只记录年份，选择Year类型。Time只存储时间，Date只记录日期。如果记录日期和时间可以选择Datetime和timestamp，dateti

26、me类型范围更大，timestamp时间是根据时区显示的。Enum和set：enum最多可以有65535个成员，set最多64个成员，两者取值只能在成员列表中选择，enum只能选一个，set可以选多个。Text和blob类型：text存储字符数据，blob存储二进制数据，存文本text，存图片等二进制数据blob。完整性约束Primary keyForeign keyNot nullUniqueAuto_incrementdefaultPrimary key主键是表的一个特殊字段，可以唯一标识表的每条信息。主键目的是为了快速查找表中某一个信息。主键必须是唯一的，表中任意两条记录的主键字段值不能

27、相同；主键值是非空值。主键可以是单一的字段，也可以是多个字段的组合。Foreign key外键是表的一个特殊字段。设置外键原则，必须依赖于数据库已存在的父表的主键，外键可以为空值。建立该表与其父表的关联关系。Constraint 外键别名 foreign key(属性1.1，属性1.2，.属性1.n) references 表名（属性2.1，属性2.2，.属性2.n）Not null属性名 数据类型 not null字段的值不能有空值，保证所有记录该字段都有值unique属性名 数据类型 unique所有记录中该字段的值不能重复出现Auto_increment用于为表插入的新记录自动生成唯一的

28、id，一个表只能有一个字段使用该约束，必须为主键的一部分。约束的字段可以是任何整数类型，默认从1开始自增。属性名 数据类型 Auto_incrementDefault 属性名 数据类型 default 默认值创建表时指定该字段的默认值，当插入一条新记录时没有为该字段赋值，系统自动为这个字段插入默认值4.2.1 基本表的定义Student表的建立例1建立学生表Student 由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成，每个属性根据所指的内容分别采用相应字符或数值数据类型。学号是主码，姓名取值唯一。 CREATE TABLE Student ( Sno

29、CHAR(9) PRIMARY KEY, /* 列级完整性约束条件*/ Sname CHAR(20) UNIQUE, /* Sname取唯一值*/ Ssex CHAR(2), Sage SMALLINT, Sdept CHAR(20) )； 主码4.2.1 基本表的定义Course表的建立 例2建立课程表Course，由课程号（Cno）、课程名（Cname）、先修课号（Cpno）、学分（Ccredit）四个属性组成，每个属性根据所指的内容分别采用相应字符或数值数据类型。 CREATE TABLE Course ( Cno CHAR(4) PRIMARY KEY, Cname CHAR(40),

30、 Cpno CHAR(4) , Ccredit SMALLINT, FOREIGN KEY (Cpno) REFERENCES Course(cno) ); 先修课 Cpno是外码 被参照表是Course被参照列是Cno4.2.1 基本表的定义SC表的建立例3建立“学生选课”表 SC，由学号（Sno）、课程号(Cno)、成绩（Grade）三个属性组成。 CREATE TABLE SC (Sno CHAR(9)， Cno CHAR(4)， Grade SMALLINT， PRIMARY KEY (Sno，Cno)， /* 主码由两个属性构成，必须作为表级完整性进行定义*/ FOREIGN KEY

31、 (Sno) REFERENCES Student(Sno)， /* 表级完整性约束条件，Sno是外码，被参照表是Student */ FOREIGN KEY (Cno) REFERENCES Course(Cno) /* 表级完整性约束条件， Cno是外码，被参照表是Course*/);查看表结构Describe/desc tablename; Show create table tablename;查看表的详细定义Show create table tablenameG修改基本表修改数据库中已存在表的定义。修改表比重新定义表简单，不需要重新加载数据，不影响正在进行的服务。Alter tab

32、le修改表名、修改字段数据类型、修改字段名、增加字段、删除字段、修改字段的排列位置、更改默认存储引擎、删除表的外键约束等。修改表修改表名表名可以在一个数据库中唯一确定一张表。Alter table oldtablename rename to newtablename;修改字段的数据类型Alter table tablename modify 属性名 数据类型;修改字段名字段名在一张表中唯一确定一个字段。Alter table tablename change 旧属性名 新属性名 新数据类型;修改表增加字段Alter table tablename add 属性名1 数据类型 完整性约束 fi

33、rst|after 属性名2; -不设置位置，默认加最后位置删除字段Alter table tablename drop 属性名修改字段的排列位置创建表时，字段在表中排列位置已经确定。Alter table tablename modify 属性名1 数据类型 first|after 属性名2；-修改属性名1的位置修改表修改数据引擎Alter table tablename engine=存储引擎名;删除表的外键约束Alter table tablename drop foreign key 外键别名;删除表的主键约束Alter table tablename drop primary key

34、;删除表Drop table (if exists) tablename;删除被其他表关联的父表-删除外键、删除表4.2.2 完整性约束的实现关系数据库的完整性主要包括实体完整性、参照完整性和用户定义的完整性三种类型。建表的同时通常还可以定义与该表有关的完整性约束条件，这些完整性约束条件被存入系统的数据字典中，当用户操作表中数据时由DBMS自动检查该操作是否违背这些完整性约束条件。如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，称表级约束；如果完整性约束条件只涉及到表中一个列，则称列级约束；一般约束既可以定义在列级也可以定义在表级。完整性约束条件即可以由RDBMS内部自动命名，也

35、可以由用户明确给出约束的名称。建议建立约束时指定名称，方便以后引用。在表建立后，也可以添加相应的约束，如果表中已有数据，添加约束时，要保证表中数据不能违背新添加的约束，否则添加约束失败。4.2.2 完整性约束的实现例1中建立的Student表中，其中学号属性为主键（Primary Key），该完整性约束由用户自己定义约束名为PK_Sno。实现语句如下： ALTER TABLE Student ADD CONSTRAINT PK_Sno PRIMARY KEY(Sno);例2中建立的Course表中，其中课程号属性为主键（Primary Key），该完整性约束由用户自己定义约束名为PK_Cno，

36、先修课号（Cpno）必需是存在的课程号或者为空，所以可以建成外键约束，该完整性约束由用户自己定义约束名为FK_Cpno。具体实现语句为： ALTER TABLE Course ADD CONSTRAINT PK_Cno PRIMARY KEY(Cno); ALTER TABLE Course ADD CONSTRAINT FK_Cpno FOREIGN KEY (Cpno) REFERENCES Course(Cno); 4.2.2 完整性约束的实现例3中建立的SC表上，（Sno，Cno）联合做主键。Sno和Cno单独做外键。如果建立表的过程中Student表Sno列已经建成主键，Course

37、表的Cno已经建成主键则可以通过下列语句直接建表时实现题目要求。如果SC表已经建立，可以通过下面语句实现题目要求：ALTER TABLE SC ADD CONSTRAINT PK_SC PRIMARY KEY(SNO,CNO);ALTER TABLE SC ADD CONSTRAINT FK_Sno FOREIGN KEY(Sno) REFERENCES Student(Sno);ALTER TABLE SC ADD CONSTRAINT FK_Cno FOREIGN KEY(Cno) REFERENCES Course(Cno); 需要保证Student(Sno)和Course(Cno)已经

38、建成主键。练习建立一个供应商、零件数据库。其中“供应商”表S（Sno，Sname，Status，City）分别表示:供应商代码、供应商名、供应商状态、供应商所在城市。“零件”表P（Pno，Pname，Color，Weight，City），表示零件号、零件名、颜色、重量及产地。其中，数据库要满足如下要求:(1)供应商代码不能为空，且值是唯一的，供应商的名也是唯一的。(2)零件号不能为空，且值是唯一的。零件名不能为空(3)一个供应商可以供应多个零件，而一个零件可以由多个供应商供应。分析:根据题意供应商和零件分别要建立一个关系模式。供应商和零件之间是一个多对多的联系，在关系数据库中，多对多联系必须生

39、成一个关系模式，而该模式的码是该联系两端实体的码加上联系的属性构成的，若该联系名为SP，那么关系模式为SP(Sno，Pno，Qty)，其中Qty表示零件的数量。根据上述分析，用SQL建立一个供应商、零件数据库如下:练习建立供应商、零件数据库CREATE TABLE S( Sno CHAR(5) NOT NULL UNIQUE, Sname CHAR(30) UNIQUE, Status CHAR(8), City CHAR(20), PRIMARY KEY（Sno）; 练习CREATE TABLE P( Pno CHAR(6), Pname CHAR(30) NOT NULL, Color C

40、HAR(8), Weight NUMERIC(6,2), City CHAR(20), PRIMARY KEY(Pno);练习 CREATE TABLE SP (Sno CHAR(5), Pno CHAR(6), Qty NUMERIC(9), PRIMARY KEY(Sno,Pno), FOREIGN KEY(Sno) REFERENCES S(Sno), FOREIGN KEY(Pno) REFERENCES P(Pno);练习practice makes perfect索引索引是一种特殊的数据库结构，可以快速查询数据库表中特定记录。索引是提高数据库性能的重要方式。在Mysql中所有数据类

41、型都可以被索引。Mysql索引包括普通索引、惟一性索引、全文索引、单列索引、多列索引和空间索引。索引的含义和特点索引由数据库表中一列或多列组合而成，作用提高对表中数据的查询速度。索引优点：可以提高检索数据的速度，对于有依赖关系的父表和子表之间的联合查询，可以提高查询速度；使用分组和排序子句进行数据查询时，节省查询中分组和排序时间。缺点：创建和维护索引需要耗费时间，耗费时间数量随数据量的增加而增加；索引需要占用物理空间；增加、删除、修改数据时，要动态维护索引。索引的分类普通索引创建普通索引时，不附加任何限制条件。可以创建在任何数据类型上，其值是否唯一和非空由字段自身的完整性约束条件决定。建立索引

42、后，查询时可以通过索引进行查询。惟一性索引使用unique参数可以设置索引为惟一性索引。在创建惟一性索引时，限制该索引的值必须是唯一的。通过惟一性索引可以更快速的确定某条记录。主键就是一种特殊惟一性索引。索引的分类全文索引使用fulltext参数可以设置索引为全文索引。全文索引只能创建在char、varchar、text类型字段上，查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度。单列索引在表中单个字段上创建索引。单列索引只根据该字段进行索引。单列索引可以是普通索引也可以是惟一性索引、全文索引，只要保证索引只对应一个字段即可。索引的分类多列索引在表的多个字段创建一个索引。该索引

43、指向创建时对应的多个字段，可以通过这几个字段进行查询。只有查询条件使用这些字段中第一个字段时，索引才会被使用。空间索引使用spatial参数可以设置索引为空间索引，空间索引只创建在空间数据类型上，可以提高系统获取空间数据的效率。空间数据类型包括geometry、point、linestring、polygon等。目前只有myisam存储引擎支持空间索引。这类索引很少使用。索引设计原则惟一性索引的值是惟一的，可以更快速通过索引确定某条记录。为经常需要排序、分组、联合操作的字段建立索引为常作为查询条件的字段建立索引限制索引的数目索引数目不是越多越好，每个索引都占用磁盘空间，索引越多，需要磁盘空间越

44、多。修改表时，对索引的重构和更新很麻烦。越多的索引，更新表浪费更多的时间。尽量使用数据量少的索引索引值很长，查询速度会受到影响。索引设计原则尽量使用前缀来索引如果索引字段值很长，最好使用值前缀来索引。删除不再使用或很少使用的索引表中数据被大量更新或者数据使用方式改变后，原有一些索引不再被使用。数据库管理员应当定期找到这些索引，删除，减少索引对更新操作的影响。如何创建索引创建表时创建索引在存在的表上创建索引使用alter table创建索引创建表时创建索引Create table tablename(属性名 数据类型 完整性约束，属性名 数据类型 完整性约束 Unique|fulltext|sp

45、atial index key 别名 (属性名1(长度)asc|desc)；别名可选参数：给创建索引取新名称其中参数属性名指索引对应字段名称长度：索引长度，必须字符类型才可以使用Ascdesc 排序方式创建普通索引Create table index1(Id int,Name varchar(20),Sex Boolean,Index(id);Show create table index1G创建唯一索引Create table index2(Id int unique,Name varchar(20),Unique Index index2_id(id asc);创建全文索引Create t

46、able index3(Id int,Info varchar(20),Fulltext index index3_info(info) engine=myisam;创建单列索引Create table index4(Id int,Subject varchar(30),Index index4_st(subject(10); -使用前10个字符进行检索创建多列索引Create table index5(Id int,Name varchar(20),Sex char(4),Index index5_ns(name,sex);Explain select * from index5 where

47、 name=a and sex=man;Explain select * from index5 where name=a;Explain select * from index5 where sex=man;创建空间索引Create table index6(Id int,Space geometry not null,Spatial index index6_sp(space) engine =myisam;在存在的表上创建索引Create unique|fulltext|spatial index 索引名 on 表名 (属性名 (长度)asc|desc);Create index index7_id on example0(id);Desc