MySQL数据库和表的创建与管理

数据库和表的创立与管理1.项目知识要点知识能力目标学时3.1任务1创建与管理数据库3.1.1创建数据库3.1.2管理数据库3.2任务2创建与管理数据库表3.2.1创建数据库表3.2.2管理数据库表3.3任务3图形界面工具创建数据库和表3.3.1图形界面工具简介3.3.2图形界面工具创建数据库3.4综合实例建立Petstore数据库表3.4.1Petstore数据库表结构分析3.4.2创建Petstore数据库与表【情境】：创建Petstore数据库【职业岗位】：数据库管理员、程序员【典型工作任务】：学会创建与管理数据库【技能要求】：1、学会命令行方式创建与管理数据库2、学会图形界面创建与管理数据库【知识要求】：1、数据库结构2、数据库操作的SQL语句2项目实训三创建“员工管理系统”数据库2工程知识要点与目标2.任务一创立与管理数据库数据的物理模型：即指数据的存储结构，如对数据库物理文件、索引文件的组织方式、文件的存取路径，内存的管理，等。物理模型不仅与数据库管理系统有关，还和操作系统甚至硬件有关，物理模型对用户是不可见的。按关系模型组织的数据表达方式简洁、直观，插入、删除、修改操作方便，而按层次、网状模型组织的数据表达方式复杂，插入、删除、修改操作复杂。因此，关系模型得到了广泛应用，MySQL是一个典型的支持关系数据模型的数据库管理系统。3.MySQL数据库模型在数据库效劳器中可以存储多个数据库文件，所以建立数据库时要设定数据库的文件名，每个数据库有惟一的数据库文件名作为与其它数据库区别的标识。数据库文件：数据库是由相关数据表组成，一个数据库包括多个数据表。数据库文件用于记录数据库中数据表构成的信息。数据库只能由得到授权的用户访问，这样保证了数据库的平安。成绩数据库xscj网络图书库bookstore雇员管理库bookstore……….库MySQL服务器4.数据库数据库和表数据库可以看成是一个存储数据对象的容器，这些数据对象包括表、视图、触发器、存储过程等，其中，表是最根本的数据对象，用以存放数据库的数据的。必须首先创立数据库，然后才能创立数据库的数据对象。MySQL可以采用两种方式创立、操作数据库和数据对象〔1〕命令行方式〔2〕图形界面工具方式数据表视图存储过程DELIMITER$$CREATEPROCEDUREDELETE_STUDENT(INXHCHAR(6))BEGIN

5.创立数据库MySQL安装后，系统自动地创立information_scema和MySQL数据库，MySQL把有关数据库的信息存储在这两个数据库中。如果删除了这些数据库，MySQL就不能正常工作。对于用户的数据，需要创立新的数据库来存放。

语法格式：CREATE{DATABASE|SCHEMA}[IFNOTEXISTS]数据库名[[DEFAULT]CHARACTERSET字符集名|[DEFAULT]COLLATE校对规那么名]说明：语句中“[]〞内为可选项。{|}表示二选一。创立数据库6.命令行方式创立数据库在SQL语言创立数据库命令createdatabase中，如果省略语句中“[]〞中的所有可选项，其结构形式如下：mysql>createdatabasename;name表示被创立数据库名，数据库名必须符合以下规那么：数据库名必须唯一；名称内不能含有“/〞及“.〞等非法字符；最大不能超过64字节。【例3.1】创立一个名为Bookstore的数据库。CREATEDATABASEBookstore;7.创立数据库时使用IFNOTEXISTS如果在效劳器已有test1数据库的情况下，再创立一个名为test1的数据库。因为MySQL不允许两个数据库使用相同的名字，所以会出错。使用IFNOTEXISTS从句可以不显示错误信息，例如：createdatabaseIFNOTEXISTStest1;为了表达问题简单，在以后的例如中单独描述命令而不需要界面结果时，在命令前省略“mysql>〞提示符。注意：在MySQL中，每一条SQL语句都以“;〞作为结束标志。8.因为MySQL效劳器中有多个数据库，可以使用USE命令可指定当前数据库。语法格式：USEdb_name;说明：这个语句也可以用来从一个数据库“跳转〞到另一个数据库，在用CREATEDATABASE语句创立了数据库之后，该数据库不会自动成为当前数据库，需要用这条USE语句来指定。【例】将数据库test1设置为当前数据库。Usetest1use命令后直接跟数据库名，然后按下回车键即完成设定过程使用这个命令之后，所有SQL命令都会应用于该数据库，这是几个少数不需要分号结尾的命令。翻开数据库9.MySQL中的字符集和校对规那么字符集是一套符号和编码。校对规那么是在字符集内用于比较字符的一套规那么。ASCII字符集中四个字母：‘A’、‘B’、‘a’、‘b’，每个字母赋予一个数值：‘A’=65，‘B’=66，‘a’=113，‘b’=114。字母‘A’是一个符号，数字65是‘A’的编码，字母和它们的编码组合在一起是一个字符集。假设我们希望比较两个字符串的值：‘A’和‘b’。比较的最简单的方法是查找编码：‘A’为65，‘b’为114。因为65小于114，我们可以说‘A’小于‘b’。我们做的仅仅是在我们的字符集上应用了一个校对规那么。10.MySQL字符集和校对规那么应用MySQL支持30多种字符集的70多种校对规那么。在同一台效劳器、同一个数据库或甚至在同一个表中使用不同字符集或校对规那么来混合字符串。两个不同的字符集不能有相同的校对规那么。每个字符集有一个默认校对规那么。例如，latin1默认校对规那么是latin1_swedish_ci，而gb2312默认校对规那么是gb2312_chinese_ci。ASCII码字符集：使用7位二进制数来表示所有的大写和小写字母，数字0到9、标点符号，以及在美式英语中使用的特殊控制字符GB2312是中国国家标准的简体中文字符集。GB2312收录简化汉字及一般符号、序号、数字、拉丁字母、日文假名、希腊字母、俄文字母、汉语拼音符号、汉语注音字母，共7445个图形字符。【例3.2】创立一个名为Bookstore的数据库，采用字符集gb2312和校对规那么gb2312_chinese_ci。CREATEDATABASEBookstore

DEFAULTCHARACTERSETgb2312COLLATEgb2312_chinese_ci;11.修改数据库数据库创立后，如果需要修改数据库的参数，可以使用ALTERDATABASE命令。语法格式：ALTER{DATABASE|SCHEMA}[数据库名][[DEFAULT]CHARACTERSET字符集名|[DEFAULT]COLLATE校对规那么名]

【例3.3】修改数据库Pet的默认字符集为latin1，校对规那么为latin1_swedish_ci。ALTERDATABASEPetDEFAULTCHARACTERSETlatin1DEFAULTCOLLATElatin1_swedish_ci;12.删除和显示数据库已经创立的数据库需要删除，使用DROPDATABASE命令。语法格式：DROPDATABASE[IFEXISTS]数据库名修改数据库test2命令如下：DROPDATABASEtest2;显示效劳器中已建立的数据库，使用SHOWDATABASES命令。语法格式：SHOWDATABASES；13.任务二

数据库表的操作

图书编号书名出版时间单价数量…ISBN7115126836/tp.4235跟我学网页设计2009-09-1023.5012ISBN730205701x/tp.3361网页编程技术2008-10-0154.1523ISBN7301063423/tp.0731数据库应用技术2007-06-1033.2331图书目录表的表结构分析字段名图书编号书名出版时间单价数量…字段值的表示方法用20个字符编号用40个字符表示yyyy-mm-dd表示用带有2位小数的5位数字表示用5位整数表示数据类型Char(20)Varchar(40)dateFloat(5)Int(5)14.数据类型〔1〕数值类型MySQL支持所有标准SQL数值数据类型。严格数值数据类型：INTEGER或INT，SMALLINT，DECIMAL或DEC，NUMERIC近似数值数据类型：FLOAT，REAL，PRECISIONMySQL支持选择在该类型关键字后面的括号内指定整数值的显示宽度(例如，INT(4))。对于浮点列类型，在MySQL中单精度值使用4个字节，双精度值使用8个字节。MySQL允许使用FLOAT(M,D)或REAL(M,D)或DOUBLEPRECISION(M,D)格式。“(M,D)〞表示该值一共显示M位整数，其中D位位于小数点后面。例如，定义为FLOAT(7,4)的一个列可以显示为-999.9999。MySQL保存值时进行四舍五入，因此如果在FLOAT(7,4)列内插入999.00009，近似结果是999.0001。15.字符串类型-CHAR〔2〕字符串类型：字符串类型的数据主要是由字母、汉字、数字符号、特殊符号构成的数据对象。按照字符个数多少的不同分为以下几类：CHAR：列的长度固定为创立表时声明的长度。长度可以为从0到255的任何值。当保存CHAR值时，在它们的右边填充空格以到达指定的长度。例如,在学生情况表中，如果设定“namechar(8)〞，表示“name〞是8个字符长度的字符串，可以做赋值引用即：name=｀张三｀，这里name的值包括8个字符，其中2个汉字〔按照4个字符处理〕和4个空格。name右侧会有假设干个空格占位。这样浪费了磁盘的存储空间。16.字符串类型-VARCHARVARCHAR：列中的值为可变长字符串。长度可以指定为0到65,535之间的值。(VARCHAR的最大有效长度由最大行大小和使用的字符集确定。整体最大长度是65,532字节〕。同CHAR比照，VARCHAR值保存时只保存需要的字符数。

例如,在学生情况表中，如果设定“namevarchar(8)〞，可以做赋值引用即：name=｀张三｀，这里姓名的值包括5个字符，其中2个汉字〔按照4个字符处理〕和1个空格。name右侧只有一个空格占位。17.BLOB和TEXT类型BLOB：二进制字符串(字节字符串)。BLOB列没有字符集，并且排序和比较基于列值字节的数值值。这种类型数据用于存储声音、视频、图像等数据。例如，图书数据处理中的图书封面、会员照片可以设定成为blob类型。TEXT：非二进制字符串(字符字符串)。TEXT列有一个字符集，并且根据字符集的校对规那么对值进行排序和比较。在实际应用中像个人履历、奖惩情况、职业说明、内容简介等设定为text的数据类型。例如，图书数据处理中的内容简介可以设定为text类型。BLOB和TEXT列不能有默认值。BLOB或TEXT对象的最大大小由其类型确定，但在客户端和效劳器之间实际可以传递的最大值由可用内存数量和通信缓存区大小确定。你可以通过更改max_allowed_packet变量的值更改消息缓存区的大小，但必须同时修改效劳器和客户端程序。18.日期和时间类型〔3〕日期和时间类型date：表示日期，输入数据的格式是：yyyy-mm-dd。支持的范围是'1000-01-01'到'9999-12-31'。time：表示时间，输入数据的格式是：hh:mm:ss。TIME值的范围可以从'-838:59:59'到'838:59:59'。小时局部会因此大的原因是TIME类型不仅可以用于表示一天的时间(必须小于24小时)，还可能为某个事件过去的时间或两个事件之间的时间间隔(可以大于24小时，或者甚至为负)。datetime:表示日期时间，格式是：yyyy-mm-ddhh:mm:ss。支持的范围为'1000-01-0100:00:00'到'9999-12-3123:59:59'。例如，在图书销售信息管理中注册时间、订购时间可以设定成为datetime类型。19.创立数据库表为数据库创立数据表，可以使用SQL命令createtable来完成。此语句的完整语法是相当复杂的，因为存在很多的可选子句，但在实际中此语句的应用相当简单。其根本结构形式为：createtabletbl_name(create_definition,...)tbl_name为要创立的表名，括号内为表的字段，多个字段用逗号隔开。20.CREATETABLE命令创立表使用CREATETABLE命令。语法格式：CREATETABLE[IFNOTEXISTS]表名〔列名数据类型[NOTNULL|NULL][DEFAULT列默认值]…〕ENGINE=存储引擎 CREATETABLE命令的根本格式：CREATETABLEtbl_name〔列名1数据类型1[NOTNULL|NULL],列名2数据类型2[NOTNULL|NULL],…..〕

21.案例分析-创立学生根本情况表字段名字段类型宽度说明图书编号char20字符,非NULL，主键书名varchar40字符,非NULL出版时间date20日期,非NULL单价Float(5,2)两位小数，非NULL数量int

3位整数,非NULL…图书目录表如下，在数据库Bookstore中创立图书目录表Book。数据表结构分析：图书编号书名出版时间单价数量…ISBN7115126836/tp.4235网页设计2009-09-1023.5012ISBN730205701x/tp.3361网页编程技术2008-10-0154.1523ISBN7301063423/tp.0731数据库技术2007-06-1033.233122.案例分析-创立XS表命令CREATETABLEbook(

图书编号char(20)NOTNULLPRIMARYKEY,

图书类别varchar(20)NOTNULLDEFAULT'计算机',

书名varchar(40)NOTNULL,

作者char(10)NOTNULL,

出版社varchar(20)NOTNULL,

出版时间dateNOTNULL,

单价float(5)NOTNULL,

数量int(5),

折扣float(3),

封面图片blob)ENGINE=InnoDB;命令代码：23.课堂练习创立实训3中的YGGL数据库，并在该数据库中创立员工信息表Employees，请写出相关命令24.修改表ALTERTABLE用于更改原有表的结构。例如，可以增加或删减列，创立或取消索引，更改原有列的类型，重新命名列或表，还可以更改表的评注和表的类型。语法格式：ALTER[IGNORE]TABLE表名ADD[COLUMN]列名[FIRST|AFTER列名]/*添加列*/ |ALTER[COLUMN]列名{SETDEFAULT默认值|DROPDEFAULT}/*修改默认值*/|CHANGE[COLUMN]旧列名列定义 /*对列重命名*/[FIRST|AFTER列名] |MODIFY[COLUMN]列定义[FIRST|AFTER列名]/*修改列类型*/|DROP[COLUMN]列名 /*删除列*/|RENAME[TO]新表名 /*重命名该表*/25.修改表举例

【例3.5】假设已经在数据库Bookstore中创立了表book，表中存在“书名〞列。在表book中增加“浏览次数〞列并将表中的“书名〞列删除。ALTERTABLEbookADD浏览次数tinyintNULL,DROPCOLUMN书名;【例3.6】假设数据库Bookstore中已经存在table1表，将table1表重命名为student。ALTERTABLEtable1RENAMETOstudent;

修改表名除了上面的ALTERTABLE命令，还可以直接用RENAMETABLE语句来更改表的名字。语法格式：RENAMETABLE旧表名1TO新表名1[,旧表名2TO新表名2]...【例3.7】假设数据库BookStore中已经存在table2表和table3表，将table2表重命名为orders，table3表重命名为orderlist。RENAMETABLEtable2TOorders,Table3TOorderlist;26.复制表语法格式：CREATETABLE[IFNOTEXISTS]新表名[LIKE参照表名]|[AS(select语句)] 使用LIKE关键字创立一个与old_table_name表相同结构的新表，列名、数据类型、空指定和索引也将复制，但是表的内容不会复制，因此创立的新表是一个空表。使用AS关键字可以复制表的内容，但索引和完整性约束是不会复制的。【例3.8】假设数据库BookStore中有一个表Book，创立Book表的一个名为book_copy1的拷贝。CREATETABLEbook_copy1LIKEBook;【例3.9】创立表Book的一个名为book_copy2的拷贝，并且复制其内容。CREATETABLEbook_copy2AS(SELECT*FROMBook);27.删除表需要删除一个表时可以使用DROPTABLE语句。语法格式：

DROPTABLE[IFEXISTS]表名1[,表名2

]...这个命令将表的描述、表的完整性约束、索引及和表相关的权限等都全部删除。【例3.10】删除表test。

USEXSCJDROPTABLEIFEXISTStest;28.显示数据表信息〔1〕显示数据表文件名语法格式：SHOWTABLES【例3.11】显示Bookstore数据库建立的数据表文件:USEBookstoreSHOWTABLES;〔2〕显示数据表结构语法格式：{DESCRIBE|DESC}表名[列名|通配符]DESC是DESCRIBE的简写，二者用法相同。【例3.12】用DESCRIBE语句查看Book表的列的信息。USEBookstoreDESCRIBEBook;【例3.13】查看Book表图书编号列的信息。USEBookstoreDESCBook图书编号;29.课堂练习1、对departments表增加一列负责人〔manager)2、对employees表增加一列照片〔photo)，用以存放雇员照片，该字段可以为空。3、对salary表增加序号列(No)，用以对每笔工资收入支出进行编号。4、将salary表的income列改为整数型，6位4、复制departments表的结构到bm表5、显示gygl数据库中的所有表6、显示departments表的结构7、删除bm表30.31任务三图形界面工具创立数据库和表NavicatforMySQL：1.连接MySQL效劳器NavicatforMySQL是一个桌面版MySQL数据库管理和开发工具，和微软SQLServer的管理器很像，易学易用，很受大家的欢送。在上图窗口输入连接名〔任取〕，在“密码(p)：〞输入密码，单击“确定〞按钮，登录到数据库效劳器中。31