MySQL学习教程第2章.ppt

《MySQL学习教程第2章.ppt》由会员分享，可在线阅读，更多相关《MySQL学习教程第2章.ppt（45页珍藏版）》请在三一办公上搜索。

1、网络数据库技术应用,第二章 数据库和表,2.1 命令方式创建数据库和表,安装完后，系统自动创建information_schema数据库和mysql数据库，如果删除这些数据库将MySQL将无法正常工作。可用show databases来查看已有的数据库对数据库的操作主要有三种：创建 使用 删除,2.1.1 创建数据库,使用CREATE DATABASE或CREATE SCHEMA命令可以创建数据库。语法格式：CREATE DATABASE|SCHEMA IF NOT EXISTS db_name create_specification,create_specification.其中creat

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

3、：指定默认值。CHARACTER SET：指定数据库字符集，charset_name为字符集名称。COLLATE：指定字符集的校对规则，collation_name为校对规则名称。,【例1】创建一个名为test1的数据库。,MySQL不允许两个数据库使用相同的名字，使用IF NOT EXISTS从句可以不显示错误信息。,创建了数据库之后使用USE命令可指定当前数据库。语法格式：USE db_name;说明：这个语句也可以用来从一个数据库“跳转”到另一个数据库，在用CREATE DATABASE语句创建了数据库之后，该数据库不会自动成为当前数据库，需要用这条USE语句来指定。注意：在MySQL中

4、，每一条SQL语句都以“;”作为结束标志。,修改数据库,数据库创建后，如果需要修改数据库的参数，可以使用ALTER DATABASE命令。语法格式：ALTER DATABASE|SCHEMA db_name alter_specification,alter_specification.说明：ALTER DATABASE用于更改数据库的全局特性，这些特性储存在数据库目录中的db.opt文件中。用户必须有对数据库进行修改的权限，才可以使用 ALTER DATABASE。修改数据库的选项与创建数据库相同，功能不再重复说明。如果语句中数据库名称忽略，则修改当前（默认）数据库。,【例2】修改数据库my

5、data（假设mydata已经创建）的默认字符集和校对规则。ALTER DATABASE mydataDEFAULT CHARACTER SET gb2312DEFAULT COLLATE gb2312_chinese_ci;,删除数据库,已经创建的数据库需要删除，使用DROP DATABASE命令。语法格式：DROP DATABASE IF EXISTS db_name其中，db_name是要删除的数据库名。可以使用IF EXISTS子句以避免删除不存在的数据库时出现的MySQL错误信息。,2.1.2 表操作,表的操作主要有三种：创建表结构 修改表结构修改表名复制表 删除表表结构和表表结构只

6、是表的基本结构，不包括表中数据；表指表结构和表中数据。,创建表结构,创建表使用CREATE TABLE命令。语法格式：CREATE TEMPORARY TABLE IF NOT EXISTS 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：列定义，包括列名、数据类型，可能还有一个空值声明和一个完整性约束。,

8、创建表结构,index_definition：表索引项定义，主要定义表的索引、主键、外键等。select_statement：可以在CREATE TABLE语句的末尾添加一个SELECT语句，在一个表的基础上创建表。table_option：用于描述表的选项。表选项table_option定义如下：说明：表中大多数的选项涉及的是表数据如何存储及存储在何处。多数情况下，不必指定表选项。ENGINE选项是定义表的存储引擎。,ENGINE|TYPE=engine_name/*存储引擎*/|AUTO_INCREMENT=value/*初始值*/|AVG_ROW_LENGTH=value/*表的平均行长

9、度*/|DEFAULT CHARACTER 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/*对关键字的更新*/|ROW_FORMAT=DE

10、FAULT|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/*索引的路径*/,创建表结构,列定义column_definition格式如下：col_name type NOT

11、NULL|NULL DEFAULT default_value AUTO_INCREMENT UNIQUE KEY|PRIMARY KEY COMMENT string reference_definition说明：col_name：表中列的名字。列名必须符合标志符规则，长度不能超过64个字符，而且在表中要唯一。如果有MySQL保留字必须用单引号括起来。type：列的数据类型，有的数据类型需要指明长度n，并用括号括起。,创建表结构,AUTO_INCREMENT：设置自增属性，只有整型列才能设置此属性。当插入NULL值或0到一个AUTO_INCREMENT列中时，列被设置为value+1，在这里

12、value是此前表中该列的最大值。AUTO_INCREMENT顺序从1开始。每个表只能有一个AUTO_INCREMENT列，并且它必须被索引。NOT NULL|NULL：指定该列是否允许为空。如果不指定，则默认为NULL。,创建表结构,DEFAULT default_value：为列指定默认值，默认值必须为一个常数。其中，BLOB和TEXT列不能被赋予默认值。如果没有为列指定默认值，MySQL自动地分配一个。如果列可以取NULL值，默认值就是NULL。如果列被声明为NOT NULL，默认值取决于列类型：（1）对于没有声明AUTO_INCREMENT属性的数字类型，默认值是0。对于一个AUTO_

13、INCREMENT列，默认值是在顺序中的下一个值。（2）对于除TIMESTAMP以外的日期和时间类型，默认值是该类型适当的“零”值。对于表中第一个TIMESTAMP列，默认值是当前的日期和时间。（3）对于除ENUM的字符串类型，默认值是空字符串。对于ENUM，默认值是第一个枚举值。,创建表结构,UNIQUE KEY|PRIMARY KEY：PRIMARY KEY和UNIQUE KEY都表示字段中的值是唯一的。PRIMARY KEY表示设置为主键，一个表只能定义一个主键，主键一定要为NOT NULL。COMMENT string：对于列的描述，string是描述的内容。reference_def

14、inition：指定参照的表和列。,创建表结构,值得注意的是，在字符数据类型和数值数据类型之后，MySQL允许指定一个数据类型选项用来改变数据类型的属性和功能。对于字符数据类型，MySQL支持两种数据类型选项：CHARACTER SET和COLLATE。如果要区分字符的大小写情况，可以在字符类型后面加上BINGARY。对于除BIT以外的数值数据类型，MySQL允许添加一个或多个数据类型选项。UNSIGNED：不允许负值。ZEROFILL：当插入的值长度小于字段设定的长度时，剩余部分用0填补。spatial_type是空间类型数据。,【例3】设已经创建了数据库XSCJ，在该数据库中创建学生情况表

15、XS。USE XSCJ；CREATE 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)ENGINE=InnoDB;在上面的例子里，每个字段都包含附加约束或修饰符，这些可以用来增加对所输入数据的约束。“PRIMARY KEY”表示将“学号”字段定义为主键。“DEFAULT 1”表示“性别”的默认值为1。

16、“ENGINE=InnoDB”表示采用的存储引擎是InnoDB，InnoDB是MySQL在Windows平台默认的存储引擎，所以“ENGINE=InnoDB”可以省略。,修改表结构,ALTER TABLE用于更改原有表的结构。例如，可以增加或删减列，创建或取消索引，更改原有列的类型，重新命名列或表，还可以更改表的评注和表的类型。,ALTER TABLE tbl_nameADD COLUMN column_definition FIRST|AFTER col_name|ALTER COLUMN col_name SET DEFAULT literal|DROP DEFAULT|CHANGE CO

17、LUMN old_col_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/*将字符集转换为进制*/|DEFAULT CHARACTER SET c

18、harset_name COLLATE collation_name/*修改默认字符集*/|table_options,说明：,ADDCOLUMN子句：向表中增加新列。例如，在表t1中增加新的一列a：ALTER TABLE t1 ADD COLUMN a TINYINT NULL;FIRST|AFTER col_name：表示在某列的前或后添加，不指定则添加到最后。ALTER COLUMN子句：修改表中指定列的默认值。CHANGE COLUMN子句：修改列的名称。重命名时，需给定旧的和新的列名称和列当前的类型，old_col_name表示旧的列名。column_definition中定义新的列

19、名和当前数据类型。例如，要把一个INTEGER列的名称从a变更到b：ALTER TABLE t1 CHANGE a b INTEGER;,说明：,MODIFY COLUMN子句：修改指定列的类型。例如，要把一个列的数据类型改为BIGINT：ALTER TABLE t1 MODIFY b BIGINT NOT NULL;注意，若表中该列所存数据的数据类型与将要修改的列的类型冲突，则发生错误。例如，原来CHAR类型的列要修改成INT类型，而原来列值中有字符型数据“a”，则无法修改。DROP子句：从表中删除列或约束。RENAME子句：修改该表的表名，new_tbl_name是新表名。例如，将表a改名

20、为b：ALTER TABLE a RENAME TO b;,ORDER BY子句：用于在创建新表时，让各行按一定的顺序排列。注意，在插入和删除后，表不会仍保持此顺序。在对表进行了大的改动后，通过使用此选项，可以提高查询效率。table_options：修改表选项，具体定义与CREATE TABLE语句中一样。可以在一个ALTER TABLE语句里写入多个ADD、ALTER、DROP和CHANGE子句，中间用逗号分开。这是MySQL相对于标准SQL的扩展。在标准SQL中，每个ALTER TABLE语句中每个子句只允许使用一次。,【例4】假设已经在数据库XSCJ中创建了表XS，表中存在“姓名”列。

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

22、e1表重命名为student，table2表重命名为course。USE XSCJ；RENAME TABLE table1 TO student,table2 TO course;,复制表,语法格式：CREATE TEMPORARY TABLE IF NOT EXISTS tbl_name()LIKE old_tbl_name|AS(select_statement);说明：使用LIKE关键字创建一个与old_table_name表相同结构的新表，列名、数据类型、空指定和索引也将复制，但是表的内容不会复制，因此创建的新表是一个空表。使用AS关键字可以复制表的内容，但索引和完整性约束是不会复制的

23、。select_statement表示一个表达式，例如，可以是一条SELECT语句。,复制表,【例6】假设数据库XSCJ中有一个表test，创建test表的一个名为test_copy1的拷贝。CREATE TABLE test_copy1 LIKE test;【例7】创建表test的一个名为test_copy2的拷贝，并且复制其内容。CREATE TABLE test_copy2 AS(SELECT*FROM test);,删除表,需要删除一个表时可以使用DROP TABLE语句。语法格式：DROP TEMPORARY TABLE IF EXISTS tbl_name,tbl_name.其中，

24、tb1_name：要被删除的表名。IF EXISTS：避免要删除的表不存在时出现错误信息。这个命令将表的描述、表的完整性约束、索引及和表相关的权限等都全部删除。【例8】删除表test。USE XSCJ；DROP TABLE IF EXISTS test;,2.2 界面创建数据库和表,2.2.1 创建数据库创建数据库的必须是系统管理员，或者拥有用户级别的CREATE权限。在安装MySQL的过程中已经创建了系统管理员，名为root，假设密码为123456。【例2.9】创建一个学生成绩数据库XSCJ（假设在这之前已经将XSCJ数据库删除）。创建XSCJ数据库步骤如下：（1）“开始”“程序”“MySQ

25、L”“MySQLAdministrator”，如图2.1所示，Server host为“localhost”，Username为“root”，输入密码，单击“OK”按钮。,图2.1 登录MySQL Administrator,2.2.1 创建数据库,（2）在MySQL Administrator窗口中展开“Catalogs”选项栏，出现如图2.2所示的数据库列表，在任意一个数据库名上右击鼠标，选择“Create New Schema”子选项。,图2.2 选择创建数据库选项,2.2.1 创建数据库,（3）出现如图2.3所示的界面，输入需要创建的数据库名XSCJ，单击“OK”按钮。这样数据库XSC

26、J就创建成功了。,图2.3 输入数据库名,2.2.2 创建、删除和修改表,在数据库中创建表，步骤如下：（1）在MySQL Administrator窗口中展开Catalogs选项栏，鼠标单击指定数据库，在右方的快捷选单上选择“Create New Table”子菜单，或者直接单击“Create Table”按钮。（2）在弹出的“MySQL Table Editor”窗口中填写表名（这里以KC表为例），在“Columns and Indices”子选框中填写表的各列及数据类型，如图2.4所示，单击“Apply Changes”按钮。在弹出的“Confirm Table Editor”对话框中单击

27、“Execute”按钮，即可成功创建表KC。说明：创建完KC表之后，使用相同的方法创建附录A中的其他两个表，这里不再重复说明。,2.2.2 创建、删除和修改,图2.4 创建表的各列及数据类型,2.2.2 创建、删除和修改,如果要在数据库中删除表，步骤如下：（1）MySQL Administrator窗口中展开“Catalogs”栏，选择数据库（如XSCJ），在右方的快捷选单上右击KC表，选择“Drop Table”子菜单，如图2.5所示。（2）在弹出对话框中单击“Drop Table（s）”按钮，即删除了表KC，这里不做删除。,图2.5 删除表,2.2.2 创建、删除和修改,在数据库中修改表，

28、步骤如下：（1）在MySQL Administrator窗口中展开Catalogs，选择数据库（如XSCJ），在右方的快捷选单上右击KC表，选择“Edit Table”子菜单，图略。（2）在弹出的“MySQL Table Editor”窗口中即可修改表的结构，修改完后单击“Apply Changes”按钮。在弹出的“Confirm Table Editor”对话框中单击“Execute”按钮，即可修改表XS1。注意：由于MySQL Administrator管理工具还存在一些问题，当列的属性为NOT NULL时，修改列名可能会出错。,2.3 有关表结构,2.3.1 空值和列的identity（

29、标志）属性下面着重对空值和列的identity（标志）属性进行说明。1.空值(NULL)概念空值通常表示未知、不可用或将在以后添加的数据。若一个列允许为空值，则向表中 输入记录值时可不为该列给出具体值；而一个列若不允许为空值，则在输入时必须给出该列的具体值。注意：表的关键字不允许为空值。空值不能与数值数据0或字符类型的空字符混为一谈。任意两个空值都不相等。,2.3 有关表结构,2.列的IDENTITY(标志）属性对任何表都可创建包含系统所生成序号值的一个标志列，该序号值唯一标志表中的一列，可以作为键值。每个表只能有一个列设置为标志属性，该列只能是decimal、int、numeric、smal

30、lint、bigint 或 tinyint 数据类型。定义标志属性时，可指定其种子（即起始）值、增量值，二者的默认值均为 1。系统自动更新标志列值，标志列不允许空值。,2.3.2 MySQL隐含地改变列类型,在下列情况下，MySQL隐含地改变在一个CREATE TABLE语句给出的一个列类型（这也可能在ALTER TABLE语句上出现）。（1）长度小于4的varchar被改变为char。（2）如果在一个表中的任何列有可变长度，结果使整个行是变长的。因此，如果一张表包含任何变长的列（varchar、text或Blob），所有大于3个字符的char列被改变为varchar列。这在任何方面都不影响用户如何使用列。在MySQL中这种改变可以节省空间并且使表操作更快捷。,2.3.2 MySQL隐含地改变列类型,（3）timestamp的显示尺寸必须是偶数且在214的范围内。如果指定0显示尺寸或比 14大，尺寸被强制为14。从113范围内的奇数值尺寸被强制为下一个更大的偶数。（4）不能在一个timestamp列里面存储一个NULL，将它设为NULL默认为当前的日期和时间。如果想要知道MySQL是否使用了除指定的以外的一种列类型，在创建表之后，使用一个DESCRIBE语句即可。DESCRIBE语句在节介绍。,下课了。,追求,休息一会儿。,