数据库和表

Ø 2.1.1  创建数据库

1.  创建数据库

使用CREATE DATABASE或CREATE SCHEMA命令可以创建数据库。

语法格式：

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name

[create_specification [, create_specification] ...]

其中create_specification:

 [DEFAULT] CHARACTER SET charset_name

| [DEFAULT] COLLATE collation_name

说明：语句中“[ ]”内为可选项。

●  db_name：数据库名。在文件系统中，MySQL的数据存储区将以目录方式表示MySQL数据库。因此，命令中的数据库名字必须符合操作系统文件夹命名规则。值得注意的是：在MySQL中是不区分大小写的。

●IF NOT EXISTS：在建数据库前进行判断，只有该数据库目前尚不存在时才执行CREATE DATABASE操作。用此选项可以避免出现数据库已经存在而再新建的错误。

●  DEFAULT：指定默认值。

● CHARACTER SET：指定数据库字符集（Charset），charset_name为字符集名称。

● COLLATE：指定字符集的校对规则，collation_name为校对规则名称（字符集和校对规则的概念见附录D）。

eg: 创建一个名为test1的数据库

mysql>create database test1;

 

创建了数据库之后使用USE命令可指定当前数据库。

语法格式：

USE  db_name;

说明：这个语句也可以用来从一个数据库“跳转”到另一个数据库，在用CREATE DATABASE语句创建了数据库之后，该数据库不会自动成为当前数据库，需要用这条USE语句来指定。

注意：在MySQL中，每一条SQL语句都以“;”作为结束标志。

2.  修改数据库

数据库创建后，如果需要修改数据库的参数，可以使用ALTER  DATABASE命令。

语法格式：

ALTER {DATABASE | SCHEMA} [db_name]

    alter_specification [, alter_specification] ...

其中alter_specification：

[DEFAULT] CHARACTER SET charset_name

    | [DEFAULT] COLLATE collation_name

说明：

ALTER DATABASE用于更改数据库的全局特性，这些特性储存在数据库目录中的db.opt文件中。用户必须有对数据库进行修改的权限，才可以使用 ALTER DATABASE。修改数据库的选项与创建数据库相同，功能不再重复说明。如果语句中数据库名称忽略，则修改当前（默认）数据库。

 

例: 修改数据库mydata（假设mydata已经创建）的默认字符集和校对规则。

ALTER DATABASE mydata

DEFAULT CHARACTER SET gb2312

DEFAULT COLLATE  gb2312_chinese_ci;

3.  删除数据库

已经创建的数据库需要删除，使用DROP DATABASE命令。

语法格式：

DROP DATABASE  [IF EXISTS] db_name

创建表

创建表使用CREATE TABLE命令。

语法格式：

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

    [ ( [column_definition] , ... | [index_definition] ) ]

    [table_option] [select_statement];

说明：

●  TEMPORARY：该关键字表示用CREATE命令新建的表为临时表。不加该关键字创建的表通常称为持久表，在数据库中持久表一旦创建将一直存在，多个用户或者多个应用程序可以同时使用持久表。有时候需要临时存放数据，例如，临时存储复杂的SELECT语句的结果。此后，可能要重复地使用这个结果，但这个结果又不需要永久保存。这时，可以使用临时表。用户可以像操作持久表一样操作临时表。只不过临时表的生命周期较短，而且只能对创建它的用户可见，当断开与该数据库的连接时，MySQL会自动删除它们。

●  IF NOT EXISTS：在建表前加上一个判断，只有该表目前尚不存在时才执行CREATE TABLE操作。用此选项可以避免出现表已经存在无法再新建的错误。

●  table_name：要创建的表的表名。该表名必须符合标志符规则，如果有MySQL保留字必须用单引号括起来。

●  column_definition：列定义，包括列名、数据类型，可能还有一个空值声明和一个完整性约束。

●  index_definition：表索引项定义，主要定义表的索引、主键、外键等，具体定义将在第5章中讨论。

●  table_option：用于描述表的选项。

●  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：列的数据类型，有的数据类型需要指明长度n，并用括号括起，MySQL支持的数据类型在附录C中介绍。

●  AUTO_INCREMENT：设置自增属性，只有整型列才能设置此属性。当插入NULL值或0到一个AUTO_INCREMENT列中时，列被设置为value+1，在这里value是此前表中该列的最大值。AUTO_INCREMENT顺序从1开始。每个表只能有一个AUTO_INCREMENT列，并且它必须被索引

 

{ENGINE | TYPE} = engine_name  /*存储引擎*/

| AUTO_INCREMENT = value  /*初始值*/

| AVG_ROW_LENGTH = value  /*表的平均行长度*/

| [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 = {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'  /*索引的路径*/

 

说明：表中大多数的选项涉及的是表数据如何存储及存储在何处。多数情况下，不必指定表选项。ENGINE选项是定义表的存储引擎，具体在附录E中介绍。

【例 ：设已经创建了数据库XSCJ，在该数据库中创建学生情况表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。“ENGINE=InnoDB”表示采用的存储引擎是InnoDB，InnoDB是MySQL在Windows平台默认的存储引擎，所以“ENGINE=InnoDB”可以省略

 

2.  修改表

ALTER TABLE用于更改原有表的结构。例如，可以增加或删减列，创建或取消索引，更改原有列的类型，重新命名列或表，还可以更改表的评注和表的类型。

语法格式：

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_col_namecolumn_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 charset_name [COLLATE collation_name]  /*修改默认字符集*/

  | table_options

  | 列或表中索引项的增、删、改（见第5章索引部分）

说明：

●   tb1_name：表名。

●  col_name：指定的列名。

●  IGNORE：是MySQL相对于标准SQL的扩展。若在修改后的新表中存在重复关键字，如果没有指定IGNORE，当重复关键字错误发生时操作失败。如果指定了IGNORE，则对于有重复关键字的行只使用第一行，其他有冲突的行被删除。

●  column_definition：定义列的数据类型和属性，具体内容在CREATE TABLE的语法中已做说明。

●  ADD[COLUMN]子句：向表中增加新列。例如，在表t1中增加新的一列a：

ALTER  TABLE  t1  ADD  COLUMN  a  TINYINT  NULL ;

●  FIRST | AFTER col_name：表示在某列的前或后添加，不指定则添加到最后。

●  ALTER [COLUMN]子句：修改表中指定列的默认值。

●  CHANGE [COLUMN]子句：修改列的名称。重命名时，需给定旧的和新的列名称和列当前的类型，old_col_name表示旧的列名。column_definition中定义新的列名和当前数据类型。例如，要把一个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改名为b：

ALTER  TABLE  a  RENAME TO  b ;

●  ORDER BY子句：用于在创建新表时，让各行按一定的顺序排列。注意，在插入和删除后，表不会仍保持此顺序。在对表进行了大的改动后，通过使用此选项，可以提高查询效率。在有些情况下，如果表按列排序，对于MySQL来说，排序可能会更简单。ORDER BY子句会在第4章中具体介绍。

●  table_options：修改表选项，具体定义与CREATE TABLE语句中一样。

可以在一个ALTER TABLE语句里写入多个ADD、ALTER、DROP和CHANGE子句，中间用逗号分开。这是MySQL相对于标准SQL的扩展。在标准SQL中，每个ALTER TABLE语句中每个子句只允许使用一次。

【例】 假设已经在数据库XSCJ中创建了表XS，表中存在“姓名”列。在表XS中增加“奖学金等级”列并将表中的“姓名”列删除。

USE XSCJ

ALTER TABLE XS

    ADD 奖学金等级 tinyint NULL ,

    DROP COLUMN 姓名 ;

3.  修改表名

除了上面的ALTER TABLE命令，还可以直接用RENAME TABLE语句来更改表的名字。

语法格式：

RENAME TABLE tbl_name TO new_tbl_name

              [ , tbl_name2 TO new_tbl_name2] ...

说明：

●   tbl_name：修改之前的表名。

●  new_tbl_name：修改之后的表名。

【例】 假设数据库XSCJ中已经存在table1表和table2表，将table1表重命名为student，table2表重命名为course。

USE XSCJ

RENAME TABLE table1 TO student,

                table2 TO course;

4.  复制表

语法格式：

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

     [ ( ) LIKE old_tbl_name [ ] ]

     | [AS (select_statement)]  ;

说明：

使用LIKE关键字创建一个与old_table_name表相同结构的新表，列名、数据类型、空指定和索引也将复制，但是表的内容不会复制，因此创建的新表是一个空表。使用AS关键字可以复制表的内容，但索引和完整性约束是不会复制的。select_statement表示一个表达式，例如，可以是一条SELECT语句。

【例】 假设数据库XSCJ中有一个表test，创建test表的一个名为test_copy1的拷贝。

CREATE TABLE test_copy1 LIKE test;

例 创建表test的一个名为test_copy2的拷贝，并且复制其内容。

CREATE TABLE  test_copy2  AS

     (SELECT * FROM test);

5.  删除表

需要删除一个表时可以使用DROP TABLE语句。

语法格式：

DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ...

其中，

tb1_name：要被删除的表名。

IF EXISTS：避免要删除的表不存在时出现错误信息。

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

例    删除表test。

USE XSCJ

DROP  TABLE  IF  EXISTS  test ;