数据库和表的创建与管理ppt课件.ppt

数据库和表的创建与管理 1 项目知识要点与目标 2 任务一创建与管理数据库 数据的物理模型 即指数据的存储结构 如对数据库物理文件 索引文件的组织方式 文件的存取路径 内存的管理 等 物理模型不仅与数据库管理系统有关 还和操作系统甚至硬件有关 物理模型对用户是不可见的 按关系模型组织的数据表达方式简洁 直观 插入 删除 修改操作方便 而按层次 网状模型组织的数据表达方式复杂 插入 删除 修改操作复杂 因此 关系模型得到了广泛应用 MySQL是一个典型的支持关系数据模型的数据库管理系统 3 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 CREATEDATABASEBookstoreDEFAULTCHARACTERSETgb2312COLLATEgb2312 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 任务二数据库表的操作 图书目录表的表结构分析 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 字符串类型 VARCHAR VARCHAR 列中的值为可变长字符串 长度可以指定为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 案例分析 创建学生基本情况表 图书目录表如下 在数据库Bookstore中创建图书目录表Book 数据表结构分析 22 案例分析 创建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的管理器很像 易学易用 很受大家的欢迎 在上图窗口输入连接