MySQL数据库任务驱动式教程（第3版）项目四.pptx

《MySQL数据库任务驱动式教程（第3版）项目四.pptx》由会员分享，可在线阅读，更多相关《MySQL数据库任务驱动式教程（第3版）项目四.pptx（69页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、1 项目四 建库、建表与数据表管理项目四建库、建表与数据表管理MySQL数据库任务驱动式教程（第3版）（微课版）人民邮电出版社北京2 项目四 建库、建表与数据表管理任务7 创建数据库和表【任务背景】S学校要建立一个教学管理系统。根据需求分析，要求创建学生信息、课程、成绩、院系单位、教师和讲授等数据表来存储数据。接下来，要创建数据库，设计数据表的结构，并初始化相关表数据。【任务要求】本任务将学习创建和管理数据库、创建和管理表、表数据操作，以及对JSON数据类型的使用的基本方法和技巧。在任务实施过程中，要特别注意表的规范化，要注意数据类型的正确选择，还要注意数据库和数据表字符集的统一问题3 项目四

2、 建库、建表与数据表管理创建与管理数据库使用CREATE DATABASE或CREATE SCHEMA命令可以创建数据库。其语法结构如下。创建库nCREATE DATABASE|SCHEMA IF NOT EXISTS DB_NAME DEFAULT CHARACTER SET charset_name|DEFAULT COLLATE collation_name【任务7.1】创建数据库JXGL。mysqlCREATE DATABASE JXGL;【任务7.2】创建数据库CPXS，并指定字符集为GB 2312。mysqlCREATE DATABASE CPXS DEFAULT CHARACTE

3、R SET gb2312 COLLATE gb2312_chinese_ci;4 项目四 建库、建表与数据表管理创建与管理数据库n用SHOW DATABASES命令查看库，运行结果如图7.1所示。n创建数据库并不表示选定并使用它，要选定或使用所创建的库，必须执行明确的操作。为了使JXGL成为当前的数据库，使用如下命令。nmysqlUse JXGL;查看库图7.1查看库运行结果5 项目四 建库、建表与数据表管理创建与管理数据库n数据库创建后，如果需要修改数据库的参数，可以使用ALTER DATABASE命令。n语法格式如下。nALTER DATABASE|SCHEMA db_name DEFAU

4、LT CHARACTER SET charset_name|DEFAULT COLLATE collation_name修改库【任务7.3】将JXGL数据库的字符集修改为GB 2312，校对原则修改为gb2312_chinese_ci。mysqlALTER DATABASE JXGL DEFAULT CHARACTER SET gb2312 COLLATE gb2312_chinese_ci6 项目四 建库、建表与数据表管理创建与管理数据库n已经创建的数据库，如果需要删除，可使用DROP DATABASE命令。n语法格式如下。nDROP DATABASE IF EXISTS db_name删除

5、库【任务7.4】删除JXGL库。mysql DROP DATABASE JXGL;特别要注意的是，删除了数据库，数据库里的所有表也同时被删除。最好先对数据库做好备份，再执行删除操作。7 项目四 建库、建表与数据表管理创建与管理表表决定了数据库的结构，是用来存放数据的。一个库需要什么表，各数据库表中有什么样的列，是要合理设计的。创建表的语法格式如下。CREATE TEMPORARY TABLE IF NOT EXISTS table_name(column_definition,.|index_definition)table_option select_statement;创建表8 项目四 建

6、库、建表与数据表管理创建与管理表与本书配套的教学示例数据库为学生管理系统（JXGL），在这个库中要设计6张表：students（学生信息表）、course（课程表）、score（成绩表）、departments（院系单位表）、teachers（教师表）和teach（讲授表）。各表的结构见表7.17.6。字段名数据类型长度是否空值是否主键/外键默认值备注s_no定长字符型CHAR6否主键学号s_name定长字符型CHAR6否姓名sexENUM(男,女)2是男性别birthday日期型DATE否出生日期d_no定长字符型Char6否外键系别address变长字符型VARCHAR20否家庭地址pho

7、ne变长字符型VARCHAR20否联系电话photo二进制BLOB是照片表7.1students9 项目四 建库、建表与数据表管理创建与管理表表7.2course字段名数据类型长度是否空值是否主键/外键默认值备注c_no定长字符型CHAR4否主键课程号c_name定长字符型CHAR10否课程名hours小整数型TINYINT3否学时credit小整数型TINYINT3否学分type变长字符型VARCHAR5否必修类型表7.3score字段名数据类型长度是否空值是否主键/外键默认值备注s_no定长字符型CHAR8否主键、外键学号c_no定长字符型CHAR4否主键、外键课程号report浮点数FL

8、OAT3，1否0成绩10 项目四 建库、建表与数据表管理创建与管理表表7.4departments表7.5teachers字段名数据类型长度是否空值是否主键/外键默认值备注d_no定长字符型CHAR8否主键系别d_name定长字符型CHAR4否院系名称字段名数据类型长度是否空值是否主键/外键默认值备注t_no定长字符型CHAR8否主键教师编号t_name定长字符型CHAR4否教师姓名d_no定长字符型CHAR4否外键系别表7.6teach字段名数据类型长度是否空值是否主键/外键默认值备注t_no定长字符型CHAR8否主键、外键教师编号c_no定长字符型CHAR4否主键、外键课程编号11 项目四

9、 建库、建表与数据表管理创建与管理表【任务7.5】创建表students。mysql CREATE TABLE IF NOT EXISTS students(s_no CHAR(6)NOT NULL COMMENT学号,s_name CHAR(6)NOT NULL COMMENT姓名,sex ENUM(男,女)DEFAULT 男 COMMENT 性别,birthday DATE NOT NULL COMMENT出生日期,d_no VARCHAR(6)NOT NULL COMMENT系别,address VARCHAR(20)NOT NULL COMMENT家庭地址,phone VARCHAR(

10、20)NOT NULL COMMENT联系电话,photo BLOB COMMENT照片,PRIMARY KEY(s_no)ENGINE=InnoDB DEFAULT CHARSET=gb2312;【任务7.6】创建表course。mysql CREATE TABLE IF NOT EXISTS course(c_no CHAR(4)NOT NULL,c_name CHAR(10)NOT NULL,hours INT(3)NOT NULL,credit INT(3)NOT NULL,type EMUN(必修课,选修)DEFAULT必修,PRIMARY KEY(c_no)ENGINE=InnoD

11、B DEFAULT CHARSET=gb2312;12 项目四 建库、建表与数据表管理创建与管理表【任务7.7】创建表score。mysql CREATE TABLE IF NOT EXISTS score(s_no CHAR(8)NOT NULL,c_no CHAR(4)NOT NULL,report FLOAT(3,1)DEFAULT 0,PRIMARY KEY(s_no,c_no)ENGINE=InnoDB DEFAULT CHARSET=gb2312;【任务7.8】创建表departments。mysql CREATE TABLE IF NOT EXISTS departments(d

12、_no CHAR(8)NOT NULL COMMENT 系别,d_name CHAR(4)NOT NULL COMMENT 院系名称,PRIMARY KEY(d_no)ENGINE=InnoDB DEFAULT CHARSET=gb2312;【任务7.9】创建表teachers。mysql CREATE TABLE IF NOT EXISTS teachers(t_no CHAR(8)NOT NULL COMMENT 教师编号,t_name CHAR(4)NOT NULL COMMENT教师姓名,d_no CHAR(4)NOT NULL COMMENT 系别,PRIMARY KEY(t_no)

13、ENGINE=InnoDB DEFAULT CHARSET=gb2312;13 项目四 建库、建表与数据表管理创建与管理表【任务7.10】创建表teach。mysql CREATE TABLE IF NOT EXISTS teach(t_no VARCHAR(8)NOT NULL,c_no VARCHAR(4)NOT NULL,KEY t_no(t_no),KEY c_no(c_no)ENGINE=InnoDB DEFAULT CHARSET=gb2312;【任务7.11】在CPXS库中，创建进货单表，进货ID是自动增量，将进货单价字段的精度设置为8，小数位设置为2位，进货时间默认为当前时间。

14、mysqlUSE CPXSmysqlCREATE TABLE进货单(进货ID TINYINT NOT NULL AUTO_INCREMENT,商品ID VARCHAR(10)NOT NULL,进货单价FLOAT(8,2)NOT NULL,数量 INT NOT NULL,进货时间DATE TIME NOT NULL DEFAULT NOW(),进货员工ID CHAR(6)NOT NULL,PRIMARY KEY(进货ID);14 项目四 建库、建表与数据表管理创建与管理表进货单表结构见表7.7。字段名数据类型长度AUTO_INCREMENT是否空值是否主键进货IDTINYINT5是否是商品IDV

15、ARCHAR10否进货单价FLOAT8,2否数量INT否进货时间TIMESTAMP否进货员工IDCHAR6否15 项目四 建库、建表与数据表管理创建与管理表创建数据表后，可用SHOW TABLES查询已创建的表的情况。mysql SHOW TABLES;运行结果如图7.2所示。查看表图7.2运行结果16 项目四 建库、建表与数据表管理创建与管理表ALTER TABLE用于更改原有表的结构，例如，增加或删减列、重新命名列或表，以及修改默认字符集。语法格式如下。修改表ALTER IGNORE TABLE tbl_namealter_specification,alter_specification

16、.alter_specification:ADD COLUMN column_definition FIRST|AFTER col_name /添加字段|ALTER COLUMN col_name SET DEFAULT literal|DROP DEFAULT/修改字段默认值|CHANGE COLUMN old_col_name column_definition /重命名字段FIRST|AFTER col_name|MODIFY COLUMN column_definition FIRST|AFTER col_name /修改字段数据类型|DROP COLUMN col_name/删除列|

17、RENAME TO new_tbl_name/对表重命名|ORDER BY col_name/按字段排序|CONVERT TO CHARACTER SET charset_name COLLATE collation_name/将字符集转换为二进制|DEFAULT CHARACTER SET charset_name COLLATE collation_name /修改表的默认字符集17 项目四 建库、建表与数据表管理创建与管理表mysql ALTER TABLE studentsADD speciality VARCHAR(5)NOT NULL AFTER department;【任务7.1

18、2】在students表的d_no列后面增加一列speciality。mysqlALTER TABLE studentsADD入学日期DATE NOT NULL DEFAULT 2014-9-1 AFTER birthday;【任务7.13】在students表的birthday列后增加一列“入学日期”，并定义其默认值为2014-9-1。mysql ALTER TABLE students CHANGE sexchar(2)NOT NULL DEFAULT 女;【任务7.14】修改students表的sex列的默认值为“女”。mysqlALTER TABLE students ALTER 入学

19、日期DROP DEFAULT;【任务7.15】删除students表的入学日期列的默认值。mysql ALTER TABLE students rename to学生表;【任务7.16】将students表重命名为学生表。ALTER TABLE course DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;【任务7.17】修改course表的字符集为UTF-8。18 项目四 建库、建表与数据表管理创建与管理表可以通过CREATE TABLE命令复制表的结构和数据。语法格式如下。复制表CREATE TEMPORARY TABLE IF NOT

20、 EXISTS tbl_name()LIKE old_tbl_name|AS(select_statement);mysql CREATE TABLE students1 LIKE students;【任务7.18】创建students表的附表students1。mysql DESC students1;【任务7.19】用命令查看students1表的结构。运行结果如图7.3所示。图7.3【任务7.19】运行结果mysql CREATE TABLE students_COPY AS SELECT *FROM students;可用SELECT *FROM students_COPY查看stud

21、ents_COPY是否与表students的数据一致。【任务7.20】复制students表的结构和数据，名为students_copy。19 项目四 建库、建表与数据表管理创建与管理表如果已存在的表不合适或不需要了，可以用DROP TABLE命令删除。语法格式如下。删除表mysql DROP TEMPORARY TABLE IF EXISTS tbl_name,tbl_name.mysql DROP TABLE students1;【任务7.21】删除students1表。ntb1_name：要被删除的表名。nIF EXISTS：避免要删除的表不存在时出现错误信息。20 项目四 建库、建表与

22、数据表管理表数据操作使用INSERT INTO、REPLACE INTO语句。语法格式如下。插入数据01OPTIONINSERTREPLACE INTO tbl_name(col_name,.)VALUES(expr|DEFAULT,.),(.),.|SET col_name=expr|DEFAULT,.【任务7.22】向students表中插入两行数据(132001,李平,男,1992-02-01,D001,上海市南京路1234号,021-345478,NULL)、(132002,张三峰,男,1992-04-01,D001,广州市沿江路58号,020-345498,NULL)。mysql I

23、NSERT INTO students VALUES(132001,李平,男,1992-02-01,D001,上海市南京路1234号,021-345478,NULL),(132002,张三峰,男,1992-04-01,D001,广州市沿江路58号,020-345498,NULL)；21 项目四 建库、建表与数据表管理表数据操作【任务7.23】用INSERT INTO语句向students表中插入数据。mysql INSERT INTO STUDENTS(s_no,s_name,sex)VALUES(132001,李小平,男);由于students表中已经有132001学生的记录，因此将出现主键

24、冲突错误，如下图所示。【任务7.24】假设有一课程表与course表的结构相同，现将course表的数据插入课程表中。mysql INSERT INTO 课程表SELECT*FROM course;查看课程表的数据。mysql SELECT*FROM课程表;运行结果如右图所示。用REPLACE INTO语句则可以直接插入新数据，而不会出现错误信息。mysql REPLACE INTO students(s_no,s_name,sex)VALUES(132001,李小平,男);22 项目四 建库、建表与数据表管理表数据操作用LOAD DATA语句将数据插入数据库表02OPTION【任务7.25】

25、创建一个名为“课程表”的表，假设课程表的数据已放在“D:course.txt”中，现将course.txt的数据插入课程表。mysql LOAD DATA LOCAL INFILED:course.txtINTO TABLE课程表CHARACTER SET gb2312;图片数据的插入03OPTIONMySQL支持图片的存储，图片一般可以以路径的形式来存储，因此插入图片采用直接插入图片的存储路径的方式。当然，也可以直接插入图片本身，使用LOAD_FILE()函数即可。【任务7.26】向students表中插入一行数据：122110，程明，男，1991-02-01，D001，北京路123号，02

26、066635425，picture.jpg。其中，照片路径为“D:IMAGE picture.jpg”。使用如下语句可以插入图片路径/直接存储图片本身。mysql INSERT INTO students VALUES(122110,程明,男,1991-02-01,D001,北京路123号,02066635425,D:IMAGEpicture.jpg/LOAD_FILE(D:IMAGEpicture.jpg);23 项目四 建库、建表与数据表管理表数据操作用UPDATESET命令对表中的数据进行修改，可以修改一个表的数据，也可以修改多个表的数据。修改单个表的数据，语法格式如下。修改数据UPDA

27、TE tbl_nameSET col_name 1=,col_name 2=expr2.WHERE子句ORDER BY子句LIMIT子句【任务7.27】将学号为122001的学生的A001课程成绩修改为80分。mysql UPDATE SCORE SET REPORT=80 WHERE S_NO=122001ANDC.NO=A001;【任务7.28】将A202课程的类型修改为专业基础课。mysql UPDATE COURSE SET TYPE=专业基础课 WHERE C_NO=A202;【任务7.29】将A001课程成绩乘以1.2，转为120分制计。mysql UPDATE SCORE SET

28、 REPORT=REPORT*1.2 WHERE C_NO=A001;24 项目四 建库、建表与数据表管理表数据操作从单个表中删除数据，语法格式如下。删除数据DELETE LOW_PRIORITY QUICK IGNORE FROM tbl_nameWHERE子句ORDER BY子句LIMIT row_count【任务7.30】删除女生记录。mysql DELETE FROM students WHERE SEX=女;【任务7.31】删除B001课程不及格的成绩记录。mysql DELETE FROM score WHERE C_NO=B001 AND REPORT DELETE FROM s

29、core;【任务7.33】删除score表中分数最低的3行记录。mysql DELETE FROM score ORDER BY REPORT LIMIT 3;25 项目四 建库、建表与数据表管理对JSON数据类型的使用从MySQL 5.7开始，MySQL支持对JSON(JavaScript Object Notation)数据类型的使用。JSON是一种轻量级的数据交换格式，易于阅读和编写。对象在JavaScript中是使用花括号包裹起来的内容，数据结构为 key1:value1,key2:value2,.的键值对结构。在面向对象的语言中，key为对象的属性，value为对应的值。键名可以使用

30、整数和字符串来表示。值的类型可以是任意类型。【任务7.34】创建包含json字段的表。mysqlCREATE TABLE tab_json(id INT(20)NOT NULL AUTO_INCREMENT,DATA JSON DEFAULT NULL,PRIMARY KEY(id);【任务7.35】向表中插入数据。mysqlinsert into tab_json(data)values(Tel:132223232444,name:david,address:Beijing);mysqlinsert into tab_json(data)values(Tel:13390989765,name

31、:Mike,address:Guangzhou);查看数据。运行结果如图7.6所示。mysqlselect*from tab_json;图7.6 查看JSON数据运行结果26 项目四 建库、建表与数据表管理计算字段的使用计算字段（Generated Column）是从MySQL 5.7开始引入的新特性。所谓计算字段，就是数据库中这一列由其他列计算而得。【任务7.36】知道直角三角形的两条直角边，求直角三角形的面积。很明显，面积可以通过两条直角边计算而得，这时候就可以在数据库中只存放直角边，面积用计算字段表示，如下所示。mysqlCREATE TABLE triangle(sidea DOUBL

32、E,sideb DOUBLE,area DOUBLE AS(sidea*sideb/2);mysqlINSERT INTO triangle(sidea,sideb)values(3,4);SELECT area;mysqlCREATE TABLE t(id INT auto_increment NOT NULL,c1 INT,c2 INT,c3 INT as(c1*c2),PRIMARY KEY(id);mysqlINSERT INTO t(c1,c2)values(3,4);SELECT c3;27 项目四 建库、建表与数据表管理项目实践 查看数据库系统中已存在的数据库。查看该数据库系统支

33、持的存储引擎的类型。（1）登录JXGL数据库。（2）创建TEST数据库，创建一个STUDENTS表，有姓名、性别和兴趣爱好字段。要求性别字段单选(男/女)，兴趣爱好字段多选，可选（篮球,足球,音乐,电影,看书,画画,摄影）。向STUDENTS表插入数据：(李明,男,足球,音乐,电影)、(张君,女,篮球,足球,电影)。再次查看数据库系统中已经存在的数据库，确保TEST数据库已经存在。复制STUDENTS表的结构和数据，成为附表STUDENTS1。删除STUDENTS表数据。删除TEST数据库。再次查看数据库系统中已经存在的数据库，确保TEST数据库已经删除。28 项目四 建库、建表与数据表管理项

34、目实践 请写出创建这3张表的SQL语句。用INSERT INTO语句一次性向Departments表插入所有数据，数据见表7.11。以文本文件的方式将数据装入Employees表，文件存储在“D:MYSQL Employees.txt”。（3）创建人事管理数据库RSGL，该数据库有3张表，分别是Employees、Departments和Salary表，表结构见表7.87.10。字段名类型长度默认值是否空值是否主键备注E_IDVARCHAR8否是员工编号E_nameVARCHAR8是员工姓名sexVARCHAR2男是性别professionalVARCHAR6职称politicalVARCHA

35、R8政治面貌eductionVARCHAR8学历birthDATE出生日期marryVARCHAR8婚姻状态GZ_timeDATE参加工作时间D_IDVARCHAR5是外键与Departments关联BZVARCHAR2是是否为编内人员29 项目四 建库、建表与数据表管理项目实践字段名类型长度默认值是否空值是否主键备注D_IDTINYINT5否是部门编号D_nameVARCHAR10否部门名称字段名类型长度默认值是否空值是否主键备注E_IDTINYINT5否外键与Employees关联monthDATE月份JIB_INFLOAT6,2基本工资JIX_INFLOAT6,2绩效工资JINT_INF

36、LOAT6,2津贴补贴GJ_OUTFLOAT6,2代扣公积金TAX_OUTFLOAT6,2扣税QT_OUTFLOAT6,2其他扣款表7.9Departments表7.10Salary30 项目四 建库、建表与数据表管理项目实践表7.9Departments部门编号部门名称A001办公室A002人事处A003宣传部A004教务处A005科技处A006后勤处B001信息学院B002艺术学院B003外语学院B004金融学院B005建筑学院31 项目四 建库、建表与数据表管理习题一、单项选择题1在MySQL中，通常使用()语句来指定一个已有数据库作为当前工作数据库。AUSINGBUSEDCUSESDU

37、SE2下列SQL语句中，创建关系表的是()。AALTERBCREATE CUPDATEDINSERT 3INSERT命令的功能是()。A在表头插入一条记录B在表尾插入一条记录C在表中指定位置插入一条记录D在表中指定位置插入若干条记录4创建表时，不允许某列为空可以使用()。ANOT NULLBNO NULL CNOT BLANKDNO BLANK5从学生（STUDENTS）表的姓名（NAME）字段中查找姓“张”的学生。可以使用如下代码：select*from STUDENTS where()。ANAME=张*BNAME=%张%CNAME LIKE 张%DNAME LIKE 张*32 项目四 建库

38、、建表与数据表管理习题6要快速清空一个表，可以使用如下语句()。ATRUNCATE TABLE BDELETE TABLE CDROP TABLEDCLEAR TABLE 二、填空题1在MySQL中，通常使用_值来表示一个列没有值或缺值的情形。2在CREATE TABLE语句中，通常使用_关键字来指定主键。3在MySQL中，可以使用INSERT或_语句，向数据库中已有的表插入一行或多行元组数据。4在MySQL中，可以使用_语句或_语句删除表中的一行或多行数据。5在MySQL中，可以使用_语句来修改、更新一个表或多个表中的数据。三、编程与应用题1请使用MySQL命令行客户端在MySQL中创建一个

39、名为“db_test”的数据库。2请使用MySQL命令行客户端在数据库bookdb中，创建一个网络留言板系统中用于描述网络留言内容的数据表contentinfo，该表的结构如下。33 项目四 建库、建表与数据表管理习题字段名数据类型长度是否空值默认值是否主键备注idINT8否主键访客编号usernameVARCHAR8访客姓名SubjectVARCHAR200是留言标题contentVARCHAR1000是留言内容replyVARCHAR1000是回复内容faceVARCHAR50是头像图标文件emailVARCHAR50是电子邮件posttimeTIMESTAMPCURRENT_TIMEST

40、AMP创建日期和时间34 项目四 建库、建表与数据表管理习题3请使用INSERT语句向数据库bookdb的表contentinfo中插入一行描述下列留言信息的数据。访客编号由系统自动生成；访客姓名为“探险者”；留言标题为“SUV论坛专栏”；留言内容为“我喜欢的SUV是”；电子邮件为“E”；留言创建日期和时间为系统当前时间。4请使用UPDATE语句将数据库bookdb的表contentinfo中访客姓名为“探险者”的留言内容修改为“我最喜欢的国产SUV”。5请使用DELETE语句将数据库bookdb的表contentinfo中访客姓名为“探险者”的用户在2014-5-1的留言信息删除。四、简答题

41、1请分别解释AUTO_INCREMENT、默认值和NULL的用途。2请简述INSERT语句与REPLACE语句的区别。3请简述DELETE语句与TRUNCATE语句的区别。35 项目四 建库、建表与数据表管理任务8 创建和管理索引【任务背景】由于数据库在执行一条SQL语句的时候，默认的方式是根据搜索条件进行全表扫描，因此遇到匹配条件的就加入搜索结果集合。当进行涉及多个表连接，包括许多搜索条件（例如大小比较、Like匹配等），而且表数据量特别大的查询时，在没有索引的情况下，MySQL需要执行的扫描行数会很大，速度也会很慢。【任务要求】本任务将从认识索引、索引的分类及索引的设计原则等方面着手，介绍

42、创建和管理索引的方法。特别要注意的是，索引并不是越多越好，要正确认识索引的重要性和设计原则，创建合适的索引。36 项目四 建库、建表与数据表管理认识索引索引是一种特殊的数据库结构，可以用来快速查询数据库表中的特定记录。在MySQL中，所有的数据类型都可以被索引。MySQL中的B-Tree索引的物理文件大多是以平衡树的结构来存储的，也就是所有实际需要的数据都存放于树的叶子节点，而且到任何一个叶子节点的最短路径的长度都是完全相同的，所以它被称为B-Tree索引。MySQL Hash索引相对于B-Tree索引，检索效率要高得多。虽然Hash索引效率高，但是Hash索引由于本身的特殊性也带来了很多限制

43、和弊端，主要表现在以下方面。MySQL Hash索引仅能满足“=”“IN”“”查询，不能使用范围查询。MySQL Hash索引无法被用来避免数据的排序操作。MySQL Hash索引不能利用部分索引键查询。MySQL Hash索引在任何时候都不能避免表扫描。MySQL Hash索引在遇到大量Hash值相等的情况时，性能并不一定就会比B-Tree索引好。37 项目四 建库、建表与数据表管理认识索引普通索引01普通索引的关键字 是 INDEX，这是最基本的索引，没有任何限制。唯一性索引02唯一性索引的关键字是UNIQUE。UNIQUE索引与普通索引类似，但是它的列的值必须唯一，允许有空值。如果是组合

44、索引，则列值的组合必须唯一。在一张表上可以创建多个UNIQUE索引。主键索引03主键索引是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引，也可通过修改表的方法增加主键索引，但一张表只能有一个主键索引。全文索引04全文索引只能对CHAR、VARCHAR和TEXT类型的列创建索引，并且只能 在 MyISAM表中 创 建。在MySQL默认情况下，这种索引对于中文作用不大。空间索引05空间索引只能对空间列创建索引，并 且 只 能 在MyISAM表 中 创建。本书不讨论。另外，按索引创建在一列还是多列上，又可以将索引分为单列索引和多列索引（复合索引）。索引的分类38 项目四 建库、

45、建表与数据表管理认识索引在主键上创建索引。在InnoDB中，如果通过主键来访问数据，效率是非常高的。为经常需要排序、分组和联合操作的字段创建索引，即那些将用于JOIN、WHERE判断和ORDER BY排序的字段。为经常作为查询条件的字段创建索引，如用于JOIN、WHERE判断的字段。尽量不要对数据库中某个含有大量重复的值的字段创建索引，如“性别”字段。在这样的字段上创建索引将不会有什么帮助，相反，还有可能降低数据库的性能。限制索引的数目。尽量使用数据量小的索引。尽量使用前缀来创建索引。删除不再使用或者很少使用的索引。索引的设计原则39 项目四 建库、建表与数据表管理索引的创建创建表的时候可以直

46、接创建索引，这种方式简单、方便。语法格式如下。创建表时创建索引CREATE TEMPORARY TABLE IF NOT EXISTS tbl_name (column_definition,.|index_definition)table_option select_statement;其中，index_definition为索引项。CONSTRAINT symbolPRIMARY KEY index_type(index_col_name,.)|INDEX|KEY index_name index_type(index_col_name,.)|CONSTRAINT symbol UNIQU

47、E INDEX index_name index_type(index_col_name,.)|FULLTEXT|SPATIAL INDEX index_name(index_col_name,.)|CONSTRAINT symbol FOREIGN KEY index_name(index_col_name,.)reference_definition40 项目四 建库、建表与数据表管理索引的创建【任务8.1】创建student2表，s_no为主键索引，s_name为唯一性索引。在address列上前5位字符创建索引。mysql CREATE TABLE IF NOT EXISTS stud

48、ent2(s_no CHAR(4)NOT NULL COMMENT 学号,s_name CHAR(4)DEFAULT NULL COMMENT 姓名,sex CHAR(2)DEFAULT 男 COMMENT 性别,birthday DATE DEFAULT NULL COMMENT 出生日期,d_no CHAR(4)DEFAULT NULL COMMENT 所在系部,address VARCHAR(20)DEFAULT NULL COMMENT 家庭地址,phone VARCHAR(12)DEFAULT NULL COMMENT 联系电话,photo BLOB COMMENT 照片,PRIMA

49、RY KEY(s_no),UNIQUE index name_index(s_name),INDEX ad_index(address(5)ENGINE=InnoDB DEFAULT CHARSET=gb2312;41 项目四 建库、建表与数据表管理索引的创建如果表已建好，可以使用CREATE INDEX语句创建索引。语法格式如下。用CREATE INDEX语句创建索引CREATE UNIQUE|FULLTEXT|SPATIAL INDEX index_nameUSING index_typeON tbl_name(index_col_name,.)其中，index_col_name的语法格式

50、如下。col_name(length)ASC|DESC42 项目四 建库、建表与数据表管理索引的创建创建普通索引01OPTION【任务8.2】为便于按地址进行查询，为students表的address列上的前6个字符创建一个升序索引address_index。mysql CREATE INDEX address_index ON students(address(6)ASC);创建唯一性索引02OPTION学生的姓名、课程表的课程名、部门表的部门名、商品表的商品名之类的字段，一般情况下可创建一个唯一性索引。【任务8.4】为course表的c_name字段创建一个唯一性索引c_name_inde