第2章 数据库和表 - 复件

1、第2章 数据库和表2.1 命令行方式创建数据库和表命令行方式创建数据库和表2.2 界面创建数据库和表界面创建数据库和表2.3 有关表结构有关表结构2.1 命令行方式创建数据库和表2.1.1 创建数据库创建数据库1. 创建数据库CREATE DATABASE | SCHEMA IF NOT EXISTS db_namecreate_specification , create_specification .其中create_specification: DEFAULT CHARACTER SET charset_name| DEFAULT COLLATE collation_name说明：语句中

2、“ ”内为可选项。 db_name：数据库名。在文件系统中，MySQL的数据存储区将以目录方式表示MySQL数据库。因此，命令中的数据库名字必须符合操作系统文件夹命名规则。值得注意的是：在MySQL中是不区分大小写的。 IF NOT EXISTS：在建数据库前进行判断，只有该数据库目前尚不存在时才执行CREATE DATABASE操作。用此选项可以避免出现数据库已经存在而再新建的错误。2.1.1 创建数据库 DEFAULT：指定默认值。 CHARACTER SET：指定数据库字符集（Charset），charset_name为字符集名称。 COLLATE：指定字符集的校对规则，collatio

3、n_name为校对规则名称（字符集和校对规则的概念见附录D）。【例2.1】 创建一个名为test1的数据库。MySQL不允许两个数据库使用相同的名字，使用IF NOT EXISTS从句可以不显示错误信息，例如：2.1.1 创建数据库指定当前数据库USE db_name;2. 修改数据库数据库创建后，如果需要修改数据库的参数，可以使用ALTER DATABASE命令。语法格式：ALTER DATABASE | SCHEMA db_name alter_specification , alter_specification .其中alter_specification：DEFAULT CHARAC

4、TER SET charset_name | DEFAULT COLLATE collation_name说明：ALTER DATABASE用于更改数据库的全局特性，这些特性储存在数据库目录中的db.opt文件中。用户必须有对数据库进行修改的权限，才可以使用 ALTER DATABASE。修改数据库的选项与创建数据库相同，功能不再重复说明。如果语句中数据库名称忽略，则修改当前（默认）数据库。2.1.1 创建数据库【例2.2】 修改数据库mydata（假设mydata已经创建）的默认字符集和校对规则。ALTER DATABASE mydataDEFAULT CHARACTER SET gb231

5、2DEFAULT COLLATE gb2312_chinese_ci;3. 删除数据库已经创建的数据库需要删除，使用DROP DATABASE命令。语法格式：DROP DATABASE IF EXISTS db_name其中，db_name是要删除的数据库名。可以使用IF EXISTS子句以避免删除不存在的数据库时出现的MySQL错误信息。示例如下：注意：这个命令必须小心使用，因为它将删除指定的整个数据库，该数据库的所有表（包括其中的数据）也将永久删除。2.1.2 创建表创建表使用CREATE TABLE命令。语法格式：CREATE TEMPORARY TABLE IF NOT EXISTS

6、tbl_name ( column_definition , . | index_definition ) table_option select_statement;说明： TEMPORARY：该关键字表示用CREATE命令新建的表为临时表。不加该关键字创建的表通常称为持久表，在数据库中持久表一旦创建将一直存在，多个用户或者多个应用程序可以同时使用持久表。有时候需要临时存放数据，例如，临时存储复杂的SELECT语句的结果。此后，可能要重复地使用这个结果，但这个结果又不需要永久保存。这时，可以使用临时表。用户可以像操作持久表一样操作临时表。只不过临时表的生命周期较短，而且只能对创建它的用户可见

7、，当断开与该数据库的连接时，MySQL会自动删除它们。 IF NOT EXISTS：在建表前加上一个判断，只有该表目前尚不存在时才执行CREATE TABLE操作。用此选项可以避免出现表已经存在无法再新建的错误。 table_name：要创建的表的表名。该表名必须符合标志符规则，如果有MySQL保留字必须用单引号括起来。 column_definition：列定义，包括列名、数据类型，可能还有一个空值声明和一个完整性约束。 2.1.2 创建表 index_definition：表索引项定义，主要定义表的索引、主键、外键等，具体定义将在第5章中讨论。 table_option：用于描述表的选项。

8、 select_statement：可以在CREATE TABLE语句的末尾添加一个SELECT语句，在一个表的基础上创建表。列定义column_definition格式如下：col_name type NOT NULL | NULL DEFAULT default_value AUTO_INCREMENT UNIQUE KEY | PRIMARY KEY COMMENT string reference_definition说明：说明： col_name：表中列的名字。列名必须符合标志符规则，长度不能超过64个字符，而且在表中要唯一。如果有MySQL保留字必须用单引号括起来。 type：列的

9、数据类型，有的数据类型需要指明长度n，并用括号括起，MySQL支持的数据类型在附录C中介绍。 AUTO_INCREMENT：设置自增属性，只有整型列才能设置此属性。当插入NULL值或0到一个AUTO_INCREMENT列中时，列被设置为value+1，在这里value是此前表中该列的最大值。AUTO_INCREMENT顺序从1开始。每个表只能有一个AUTO_INCREMENT列，并且它必须被索引。2.1.2 创建表 NOT NULL | NULL：指定该列是否允许为空。如果不指定，则默认为NULL。 DEFAULT default_value：为列指定默认值，默认值必须为一个常数。其中，BLO

10、B和TEXT列不能被赋予默认值。如果没有为列指定默认值，MySQL自动地分配一个。如果列可以取NULL值，默认值就是NULL。如果列被声明为NOT NULL，默认值取决于列类型：（1）对于没有声明AUTO_INCREMENT属性的数字类型，默认值是0。对于一个AUTO_INCREMENT列，默认值是在顺序中的下一个值。（2）对于除TIMESTAMP以外的日期和时间类型，默认值是该类型适当的“零”值。对于表中第一个TIMESTAMP列，默认值是当前的日期和时间。（3）对于除ENUM的字符串类型，默认值是空字符串。对于ENUM，默认值是第一个枚举值。 UNIQUE KEY | PRIMARY KE

11、Y：PRIMARY KEY和UNIQUE KEY都表示字段中的值是唯一的。PRIMARY KEY表示设置为主键，一个表只能定义一个主键，主键一定要为NOT NULL。 COMMENT string：对于列的描述，string是描述的内容。 reference_definition：指定参照的表和列，具体定义在第5章参照完整性中介绍。type定义如下：2.1.2 创建表说明：以上是各种数据类型的定义格式，值得注意的是，在字符数据类型和数值数据类型之后，MySQL允许指定一个数据类型选项用来改变数据类型的属性和功能。对于字符数据类型，MySQL支持两种数据类型选项：CHARACTER SET和CO

12、LLATE。如果要区分字符的大小写情况，可以在字符类型后面加上BINGARY。对于除BIT以外的数值数据类型，MySQL允许添加一个或多个数据类型选项。UNSIGNED：不允许负值。ZEROFILL：当插入的值长度小于字段设定的长度时，剩余部分用0填补。spatial_type是空间类型数据，本书不讨论。表选项table_option定义如下：2.1.2 创建表ENGINE | TYPE = engine_name/*存储引擎*/| AUTO_INCREMENT = value/*初始值*/| AVG_ROW_LENGTH = value/*表的平均行长度*/| DEFAULT CHARACT

13、ER SET charset_name COLLATE collation_name/*默认字符集和校对*/| CHECKSUM = 0 | 1/*设置为1表示求校验和*/| COMMENT = string/*注释*/| CONNECTION = connect_string/*连接字符串*/| MAX_ROWS = value/*行的最大数*/| MIN_ROWS = value/*列的最小数*/| PACK_KEYS = 0 | 1 | DEFAULT| PASSWORD = string/*对.frm文件加密*/| DELAY_KEY_WRITE = 0 | 1/*对关键字的更新*/|

14、 ROW_FORMAT = DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT/*定义各行应如何储存*/| UNION = (tbl_name,tbl_name.)/*表示哪个表应该合并*/| INSERT_METHOD = NO | FIRST | LAST /*是否执行INSERT语句*/| DATA DIRECTORY = absolute path to directory/*数据文件的路径*/| INDEX DIRECTORY = absolute path to directory/*索引的路径*/MYSQL的基本数据类型数据类型数

15、据类型描述描述字节字节推荐使用推荐使用SMALLINT整数，从-32000到+320002存储相对比较小的整数。如：年龄、数量INT整数，从-2000000000到+20000000004存储中等整数。如：距离BIGINT不能用SMALLIT或INT描述的越大整数。8存储超大的整数。如：科学/数学数据FLOAT单精度浮点型数据4存储小数数据。如：测量，温度DOUBLE双精度浮点型数据8需要双精度存储的小数数据MYSQL的基本数据类型数据类型数据类型描述描述字节字节推荐使用推荐使用CHAR固定长度的字符串特定字符串长（最高255字符）存储通常包含预定义字符串的变量。如：邮编VARCHAE具有最大

16、限制的可变长度的字符串变量（最高255字符）存储不同长度的字符串值。如：名字，密码TEXT没有最大长度限制的可变长度存储大型文本数据。如：备注，产品描述BLOB二进制字符串实际字符串长度存储二进制数据。如：图片，附件，二进制文档MYSQL的基本数据类型数据类型数据类型描述描述字节字节推荐使用推荐使用DATE以yyyy-mm-dd格式的日期3存储日期。如：生日TIME以hh:mm:ss格式的时间3存储时间或时间间隔。如：报警声，两时间之间的间隔DATETIME以yyyy-mm-ddhh:mm:ss格式结合日期和时间8存储包含日期和时间的数据。如：提醒的人，事件2.1.2 创建表说明：表中大多数的

17、选项涉及的是表数据如何存储及存储在何处。多数情况下，不必指定表选项。ENGINE选项是定义表的存储引擎。【例2.3】 设已经创建了数据库XSCJ，在该数据库中创建学生情况表XS。USE XSCJCREATE TABLE XS( 学号 char(6) NOT NULL PRIMARY KEY, 姓名 char(8) NOT NULL , 专业名 char(10) NOT NULL , 性别 tinyint(1) NOT NULL DEFAULT 1 , 出生日期date NOT NULL , 总学分 tinyint NULL , 照片 blob NULL, 备注 text NULL ) ENGI

18、NE=InnoDB;在上面的例子里，每个字段都包含附加约束或修饰符，这些可以用来增加对所输入数据的约束。“PRIMARY KEY”表示将“学号”字段定义为主键。“DEFAULT 1”表示“性别”的默认值为1。“ENGINE=InnoDB”表示采用的存储引擎是InnoDB，InnoDB是MySQL在Windows平台默认的存储引擎，所以“ENGINE=InnoDB”可以省略。存储引擎MySQL中的数据用各种不同的技术存储在文件（或者内存）中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善

19、你的应用的整体功能。存储引擎MyISAM： Mysql的默认数据库，最为常用。拥有较高的插入，查询速度，但不支持事务。InnoDB ：事务型数据库的首选引擎，支持ACID事务，支持行级锁定。BDB： 源自Berkeley DB，事务型数据库的另一种选择，支持COMMIT和ROLLBACK等其他事务特性。Memory ：所有数据置于内存的存储引擎，拥有极高的插入，更新和查询效率。但是会占用和数据量成正比的内存空间。并且其内容会在Mysql重新启动时丢失。show variables like %storage_engine%;2.1.2 创建表2. 修改表ALTER TABLE用于更改原有表的结

20、构。例如，可以增加或删减列，创建或取消索引，更改原有列的类型，重新命名列或表，还可以更改表的评注和表的类型。语法格式：ALTER IGNORE TABLE tbl_name alter_specification , alter_specification .alter_specification: ADD COLUMN column_definition FIRST | AFTER col_name /*添加列*/ | ALTER COLUMN col_name SET DEFAULT literal | DROP DEFAULT/*修改默认值*/ | CHANGE COLUMN old_c

21、ol_name column_definition /*对列重命名*/ FIRST|AFTER col_name | MODIFY COLUMN column_definition FIRST | AFTER col_name /*修改列类型*/ | DROP COLUMN col_name/*删除列*/ | RENAME TO new_tbl_name/*重命名该表*/ | ORDER BY col_name/*排序*/ | CONVERT TO CHARACTER SET charset_name COLLATE collation_name /*将字符集转换为 二进制*/ | DEFAU

22、LT CHARACTER SET charset_name COLLATE collation_name/*修改默认字符集*/ | table_options | 列或表中索引项的增、删、改（见第5章索引部分）2.1.2 创建表 ADDCOLUMN子句：向表中增加新列。例如，在表t1中增加新的一列a：ALTER TABLE t1 ADD COLUMN a TINYINT NULL ; CHANGE COLUMN子句：修改列的名称。重命名时，需给定旧的和新的列名称和列当前的类型，如，要把一个INTEGER列的名称从a变更到b：ALTER TABLE t1 CHANGE a b INTEGER;

23、MODIFY COLUMN子句：修改指定列的类型。例如，要把一个列的数据类型改为BIGINT：ALTER TABLE t1 MODIFY b BIGINT NOT NULL;2.1.2 创建表注意，若表中该列所存数据的数据类型与将要修改的列的类型冲突，则发生错误。例如，原来CHAR类型的列要修改成INT类型，而原来列值中有字符型数据“a”，则无法修改。可以在一个ALTER TABLE语句里写入多个ADD、ALTER、DROP和CHANGE子句，中间用逗号分开。这是MySQL相对于标准SQL的扩展。在标准SQL中，每个ALTER TABLE语句中每个子句只允许使用一次。【例2.4】 假设已经在数

24、据库XSCJ中创建了表XS，表中存在“姓名”列。在表XS中增加“奖学金等级”列并将表中的“姓名”列删除。USE XSCJALTER TABLE XS ADD 奖学金等级 tinyint NULL , DROP COLUMN 姓名 ;2.1.2 创建表3. 修改表名除了上面的ALTER TABLE命令，还可以直接用RENAME TABLE语句来更改表的名字。语法格式：RENAME TABLE tbl_name TO new_tbl_name , tbl_name2 TO new_tbl_name2 .【例2.5】 假设数据库XSCJ中已经存在table1表和table2表，将table1表重命名

25、为student，table2表重命名为course。USE XSCJRENAME TABLE table1 TO student, table2 TO course;2.1.2 创建表4. 复制表语法格式：CREATE TEMPORARY TABLE IF NOT EXISTS tbl_name ( ) LIKE old_tbl_name | AS (select_statement);【例2.6】 假设数据库XSCJ中有一个表test，创建test表的一个名为test_copy1的拷贝。CREATE TABLE test_copy1 LIKE test;【例2.7】 创建表test的一个名

26、为test_copy2的拷贝，并且复制其内容。CREATE TABLE test_copy2 AS (SELECT * FROM test);2.1.2 创建表5. 删除表语法格式：DROP TEMPORARY TABLE IF EXISTS tbl_name , tbl_name .其中，tb1_name：要被删除的表名。IF EXISTS：避免要删除的表不存在时出现错误信息。这个命令将表的描述、表的完整性约束、索引及和表相关的权限等都全部删除。【例2.8】 删除表test。USE XSCJDROP TABLE IF EXISTS test ;2.2 界面创建数据库和表2.2.1 创建数据库

27、创建数据库创建数据库的必须是系统管理员，或者拥有用户级别的CREATE权限。在安装MySQL的过程中已经创建了系统管理员，名为root，假设密码为123456。【例2.9】 创建一个学生成绩数据库XSCJ（假设在这之前已经将XSCJ数据库删除）。创建XSCJ数据库步骤如下：（1）“开始”“程序”“MySQL”“MySQLAdministrator”，如图2.1所示，Server host为“localhost”，Username为“root”，输入密码，单击“OK”按钮。 主机名 用户名 密码 图2.1 登录MySQL Administrator2.2.1 创建数据库（2）在MySQL Adm

28、inistrator窗口中展开“Catalogs”选项栏，出现如图2.2所示的数据库列表，在任意一个数据库名上右击鼠标，选择“Create New Schema”子选项。 Catalogs 选项栏 数据库列表 创建新表 图2.2 选择创建数据库选项2.2.1 创建数据库（3）出现如图2.3所示的界面，输入需要创建的数据库名XSCJ，单击“OK”按钮。这样数据库XSCJ就创建成功了。图2.3 输入数据库名2.2.2 创建、删除和修改表 在数据库中创建表，步骤如下：（1）在MySQL Administrator窗口中展开Catalogs选项栏，鼠标单击指定数据库，在右方的快捷选单上选择“Creat

29、e New Table”子菜单，或者直接单击“Create Table”按钮。（2）在弹出的“MySQL Table Editor”窗口中填写表名（这里以KC表为例），在“Columns and Indices”子选框中填写表的各列及数据类型，如图2.4所示，单击“Apply Changes”按钮。在弹出的“Confirm Table Editor”对话框中单击“Execute”按钮，即可成功创建表KC。说明：创建完KC表之后，使用相同的方法创建附录A中的其他两个表，这里不再重复说明。2.2.2 创建、删除和修改 输入表名 列名 列类型 图2.4 创建表的各列及数据类型2.2.2 创建、删除和

30、修改 如果要在数据库中删除表，步骤如下：（1）MySQL Administrator窗口中展开“Catalogs”栏，选择数据库（如XSCJ），在右方的快捷选单上右击KC表，选择“Drop Table”子菜单，如图2.5所示。（2）在弹出对话框中单击“Drop Table（s）”按钮，即删除了表KC，这里不做删除。图2.5 删除表2.2.2 创建、删除和修改 在数据库中修改表，步骤如下：（1）在MySQL Administrator窗口中展开Catalogs，选择数据库（如XSCJ），在右方的快捷选单上右击KC表，选择“Edit Table”子菜单，图略。（2）在弹出的“MySQL Table