mysql数据库及表的管理

MySQL中字符大小写:

1、SQL关键字及函数名不区分字符大小写;

2、数据库、表及视图名称的大小区分与否取决于低层OS及FS

3、存储过程、存储函数及事件调度器的名字不区分大小写,但触发器区分;

4、表别名区分大不写;

5、对字段中的数据,如果字段类型为Binary类型,则区分大小写;非Binary不区分大小写;

数据库:

CREATE {DATABASE|SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] [CHARACTER SET=''] [DEFAULT] [COLLATE='']

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

        ALTER {DATABASE | SCHEMA} db_name

        UPGRADE DATA DIRECTORY NAME(用途:升级数据库后,升级数据库字典,用处不多)

ALTER {DATABASE|SCHEMA} db_name [DEFAULT] [CHARACTER SET=''] [DEFAULT] [COLLATE='']

          

          (SHOW WARNINGS;显示警告信息)

          改变数据库名称:1、备份数据库,2、删除数据库,3、重建新数据库,把原表复制回来

表:

表创建:第一种方式

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

   (create_definition,…)

   [table_options]

   (create_definition,…):

    字段的定义:字段名、类型和类型修饰符

    键、约束或索引:

    PRIMARY KEY, UNIQUE KEY, FOREIGN KEY, CHECK

    {INDEX|KEY} 

   [table_options]

    ENGINE [=] engine_name

    mysql> SHOW ENGINES;

    AUTO_INCREMENT [=] value

    [DEFAULT] CHARACTER SET [=] charset_name

    [DEFAULT] COLLATE [=] collation_name

    COMMENT [=] 'string'

    DELAY_KEY_WRITE [=] {0 | 1}对提高性能有帮助

    ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}

    TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}]

例子:

创建mydb数据库,创建t1表并创建两个字段Name(50字符,非空),Age(TINYINT,无符号,非空),这两个字段一起作为主键。

mysql> CREATE DATABASE mydb;
Query OK, 1 row affected (0.01 sec)
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.02 sec)
mysql> USE mydb;
Database changed
mysql> CREATE TABLE t1 (Name VARCHAR(50) NOT NULL,Age TINYINT UNSIGNED NOT NULL,PRIMARY     KEY(Name,Age));
Query OK, 0 rows affected (0.10 sec)
mysql> DESC t1;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| Name  | varchar(50)         | NO   | PRI | NULL    |       |
| Age   | tinyint(3) unsigned | NO   | PRI | NULL    |       |
+-------+---------------------+------+-----+---------+-------+
2 rows in set (0.07 sec)

SHOW ENGINES;显示引擎,DEFAULT的为默认存储引擎

mysql> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.00 sec)

或者

mysql> SHOW TABLE STATUS LIKE 't1'\G;
*************************** 1. row ***************************
           Name: t1
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 10485760
 Auto_increment: NULL
    Create_time: 2015-05-31 17:42:45
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

手动指定存储引擎

mysql> CREATE TABLE t1 (Name VARCHAR(50) NOT NULL,Age TINYINT UNSIGNED NOT NULL,PRIMARY KEY(Name,Age)) ENGINE=MyISAM;
Query OK, 0 rows affected (0.02 sec)
mysql> SHOW TABLE STATUS LIKE 't1'\G;
*************************** 1. row ***************************
           Name: t1
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 281474976710655
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2015-05-31 17:59:01
    Update_time: 2015-05-31 17:59:01
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

MyISAM表,每表有三个文件,都位于数据库目录中:

    tb_name.frm: 表结构定义

    tb_name.MYD: 数据文件

    tb_name.MYI: 索引文件

    InnoDB表,有两种存储方式

    1、默认:每表有一个独立文件和一个多表共享的文件

    tb_name.frm: 表结构的定义,位于数据库目录中;

    ibdata#: 共享的表空间文件,默认位于数据目录(datadir指向的目录)中;

    2、独立的表空间:(建议)

    tb_name.frm: 每表有一个表结构文件

    tb_name.ibd: 一个独有的表空间文件

mysql> SHOW GLOBAL VARIABLES LIKE 'INNODB%'
    -> ;
+---------------------------------+------------------------+
| Variable_name                   | Value                  |
+---------------------------------+------------------------+
| innodb_adaptive_flushing        | ON                     |
| innodb_adaptive_hash_index      | ON                     |
| innodb_additional_mem_pool_size | 8388608                |
| innodb_autoextend_increment     | 8                      |
| innodb_autoinc_lock_mode        | 1                      |
| innodb_buffer_pool_instances    | 1                      |
| innodb_buffer_pool_size         | 134217728              |
| innodb_change_buffering         | all                    |
| innodb_checksums                | ON                     |
| innodb_commit_concurrency       | 0                      |
| innodb_concurrency_tickets      | 500                    |
| innodb_data_file_path           | ibdata1:10M:autoextend |
| innodb_data_home_dir            |                        |
| innodb_doublewrite              | ON                     |
| innodb_fast_shutdown            | 1                      |
| innodb_file_format              | Antelope               |
| innodb_file_format_check        | ON                     |
| innodb_file_format_max          | Antelope               |
| innodb_file_per_table           | OFF                    |启用这一项即可实现第二种方法
| innodb_flush_log_at_trx_commit  | 1                      |
| innodb_flush_method             |                        |
| innodb_force_load_corrupted     | OFF                    |
| innodb_force_recovery           | 0                      |
| innodb_io_capacity              | 200                    |
| innodb_large_prefix             | OFF                    |
| innodb_lock_wait_timeout        | 50                     |
| innodb_locks_unsafe_for_binlog  | OFF                    |
| innodb_log_buffer_size          | 8388608                |
| innodb_log_file_size            | 5242880                |
| innodb_log_files_in_group       | 2                      |
| innodb_log_group_home_dir       | ./                     |
| innodb_max_dirty_pages_pct      | 75                     |
| innodb_max_purge_lag            | 0                      |
| innodb_mirrored_log_groups      | 1                      |
| innodb_old_blocks_pct           | 37                     |
| innodb_old_blocks_time          | 0                      |
| innodb_open_files               | 300                    |
| innodb_print_all_deadlocks      | OFF                    |
| innodb_purge_batch_size         | 20                     |
| innodb_purge_threads            | 0                      |
| innodb_random_read_ahead        | OFF                    |
| innodb_read_ahead_threshold     | 56                     |
| innodb_read_io_threads          | 4                      |
| innodb_replication_delay        | 0                      |
| innodb_rollback_on_timeout      | OFF                    |
| innodb_rollback_segments        | 128                    |
| innodb_spin_wait_delay          | 6                      |
| innodb_stats_method             | nulls_equal            |
| innodb_stats_on_metadata        | ON                     |
| innodb_stats_sample_pages       | 8                      |
| innodb_strict_mode              | OFF                    |
| innodb_support_xa               | ON                     |
| innodb_sync_spin_loops          | 30                     |
| innodb_table_locks              | ON                     |
| innodb_thread_concurrency       | 0                      |
| innodb_thread_sleep_delay       | 10000                  |
| innodb_use_native_aio           | OFF                    |
| innodb_use_sys_malloc           | ON                     |
| innodb_version                  | 5.5.33                 |
| innodb_write_io_threads         | 4                      |
+---------------------------------+------------------------+
60 rows in set (0.02 sec)
mysql> SET GLOBAL innodb_file_per_table=ON;因为是全局变量,所以要这样设置,并重登陆mysql才行。
Query OK, 0 rows affected (0.01 sec)

想永久有效,要修改配置文件

vim /etc/my.cnf

mysqld中加入 innodb_file_per_table = ON

表创建:第二种方式(复制表数据)数据属性会丢失(不推荐)

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

   [(create_definition,…)]

   [table_options]

   select_statement

mysql> CREATE TABLE t2 SELECT * FROM t1;
Query OK, 1 row affected (0.14 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> DESC t1;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| Name  | varchar(50)         | NO   | PRI | NULL    |       |
| Age   | tinyint(3) unsigned | NO   | PRI | NULL    |       |
+-------+---------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> DESC t2;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| Name  | varchar(50)         | NO   |     | NULL    |       |
| Age   | tinyint(3) unsigned | NO   |     | NULL    |       |
+-------+---------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM t1;
+------+-----+
| Name | Age |
+------+-----+
| tom  |  23 |
+------+-----+
1 row in set (0.00 sec)
mysql> SELECT * FROM t2;
+------+-----+
| Name | Age |
+------+-----+
| tom  |  23 |
+------+-----+
1 row in set (0.00 sec)

表创建:第三种方式(复制表结构,基于某表创建空表)

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

    { LIKE old_tbl_name | (LIKE old_tbl_name) }

mysql> CREATE TABLE t3 LIKE t1;
Query OK, 0 rows affected (0.05 sec)
mysql> SELECT * FROM t1;
+------+-----+
| Name | Age |
+------+-----+
| tom  |  23 |
+------+-----+
1 row in set (0.00 sec)
mysql> SELECT * FROM t3;
Empty set (0.00 sec)
mysql> DESC t1;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| Name  | varchar(50)         | NO   | PRI | NULL    |       |
| Age   | tinyint(3) unsigned | NO   | PRI | NULL    |       |
+-------+---------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> DESC t3;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| Name  | varchar(50)         | NO   | PRI | NULL    |       |
| Age   | tinyint(3) unsigned | NO   | PRI | NULL    |       |
+-------+---------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

先使用第三种方式复制表结构,再用第二种方式复制数据,这样才完整;

表删除:

    DROP [TEMPORARY] TABLE [IF EXISTS]

   tbl_name [, tbl_name] …

   [RESTRICT | CASCADE]

CASCADE:级联删除:删除一张表A,但C表也依赖A,这时删除A的同时也删除C。

表修改:

ALTER TABLE tbl_name

    [alter_specification [, alter_specification] …]

修改字段定义:

    插入新字段

mysql> ALTER TABLE t1 ADD ID INT UNSIGNED NOT NULL;
Query OK, 1 row affected (0.06 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> DESC t1;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| Name  | varchar(50)         | NO   | PRI | NULL    |       |
| Age   | tinyint(3) unsigned | NO   | PRI | NULL    |       |
| ID    | int(10) unsigned    | NO   |     | NULL    |       |
+-------+---------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> ALTER TABLE t1 ADD Gender ENUM('M','F') NOT NULL DEFAULT 'M' AFTER Name;
Query OK, 1 row affected (0.06 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> DESC t1;
+--------+---------------------+------+-----+---------+-------+
| Field  | Type                | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| Name   | varchar(50)         | NO   | PRI | NULL    |       |
| Gender | enum('M','F')       | NO   |     | M       |       |
| Age    | tinyint(3) unsigned | NO   | PRI | NULL    |       |
| ID     | int(10) unsigned    | NO   |     | NULL    |       |
+--------+---------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

    删除字段

    DROP [COLUMN] col_name

mysql> ALTER TABLE t1 DROP Age;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> DESC t1;
+--------+------------------+------+-----+---------+-------+
| Field  | Type             | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+-------+
| Name   | varchar(50)      | NO   | PRI | NULL    |       |
| Gender | enum('M','F')    | NO   |     | M       |       |
| ID     | int(10) unsigned | NO   |     | NULL    |       |
+--------+------------------+------+-----+---------+-------+
3 rows in set (0.02 sec)

    修改字段

    修改字段名称

    CHANGE [COLUMN] old_col_name new_col_name column_definition

        [FIRST|AFTER col_name]

mysql> DESC t1;
+--------+------------------+------+-----+---------+-------+
| Field  | Type             | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+-------+
| Name   | varchar(50)      | NO   | PRI | NULL    |       |
| ID     | int(10) unsigned | NO   |     | NULL    |       |
| Gender | enum('M','F')    | NO   |     | NULL    |       |
+--------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> ALTER TABLE t1 CHANGE Name Stuname varchar(50) NOT NULL;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> DESC t1;
+---------+------------------+------+-----+---------+-------+
| Field   | Type             | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+-------+
| Stuname | varchar(50)      | NO   | PRI | NULL    |       |
| ID      | int(10) unsigned | NO   |     | NULL    |       |
| Gender  | enum('M','F')    | NO   |     | NULL    |       |
+---------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

    修改字段类型及属性等

    MODIFY [COLUMN] col_name column_definition

        [FIRST | AFTER col_name]

mysql> DESC t1;
+--------+------------------+------+-----+---------+-------+
| Field  | Type             | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+-------+
| Name   | varchar(50)      | NO   | PRI | NULL    |       |
| Gender | enum('M','F')    | NO   |     | M       |       |
| ID     | int(10) unsigned | NO   |     | NULL    |       |
+--------+------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> ALTER TABLE t1 MODIFY Gender ENUM('M','F') NOT NULL AFTER ID;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> DESC t1;
+--------+------------------+------+-----+---------+-------+
| Field  | Type             | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+-------+
| Name   | varchar(50)      | NO   | PRI | NULL    |       |
| ID     | int(10) unsigned | NO   |     | NULL    |       |
| Gender | enum('M','F')    | NO   |     | NULL    |       |
+--------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

    修改约束、键或索引:

mysql> ALTER TABLE t1 ADD INDEX(Stuname);
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SHOW INDEXES FROM t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t1    |          0 | PRIMARY  |            1 | Stuname     | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
| t1    |          1 | Stuname  |            1 | Stuname     | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.12 sec)

删除索引

mysql> ALTER TABLE t1 DROP INDEX Stuname;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> SHOW INDEXES FROM t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t1    |          0 | PRIMARY  |            1 | Stuname     | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.01 sec)

    表改名:

    RENAME [TO|AS] new_tbl_name

mysql> ALTER TABLE t1 RENAME TO t8;
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW TABLES;
+----------------+
| Tables_in_mydb |
+----------------+
| t2             |
| t3             |
| t8             |
+----------------+
3 rows in set (0.00 sec)

或直接使用RENAME改名

RENAME TABLE tbl_name TO new_tbl_name

    [, tbl_name2 TO new_tbl_name2] …

mysql> RENAME TABLE t8 TO t1;
Query OK, 0 rows affected (0.03 sec)
mysql> SHOW TABLES;
+----------------+
| Tables_in_mydb |
+----------------+
| t1             |
| t2             |
| t3             |
+----------------+
3 rows in set (0.02 sec)
    mysql> RENAME TABLE old_name TO new_name;

改变存储引擎:

mysql> SHOW TABLE STATUS LIKE 't1'\G;
*************************** 1. row ***************************
           Name: t1
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 1
 Avg_row_length: 20
    Data_length: 20
Max_data_length: 281474976710655
   Index_length: 2048
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2015-06-01 06:04:40
    Update_time: 2015-06-01 06:04:40
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.01 sec)
ERROR: 
No query specified
mysql> ALTER TABLE t1 ENGINE=INNODB;
Query OK, 1 row affected (0.16 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> SHOW TABLE STATUS LIKE 't1'\G;
*************************** 1. row ***************************
           Name: t1
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 1
 Avg_row_length: 16384
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2015-06-01 06:15:20
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)
ERROR: 
No query specified

    指定排序标准的字段:

    ORDER BY col_name [, col_name] …

    转换字符集及排序规则:

    CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]

    表选项修改:

    [table_options]

    ENGINE [=] engine_name

    mysql> SHOW ENGINES;

    AUTO_INCREMENT [=] value

    [DEFAULT] CHARACTER SET [=] charset_name

    [DEFAULT] COLLATE [=] collation_name

    COMMENT [=] 'string'

    DELAY_KEY_WRITE [=] {0 | 1}

    ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}

    TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}]

练习题:

新建如下表(包括结构和内容):

ID    Name          Age     Gender     Course

1     Ling Huchong   24      Male       Hamogong

2     Huang Rong    19      Female     Chilian Shenzhang

3     Lu Wushaung   18      Female     Jiuyang Shenggong

4     Zhu Ziliu     52      Male       Pixie Jianfa

5     Chen Jialuo   22      Male       Xianglong Shiba Zhang

6  Ou Yangfeng   70      Male       Shenxiang Bannuo Gong

1、新增字段:

Class 字段定义自行选择;放置于Name字段后;

2、将ID字段名称修改为TID;

3、将Age字段放置最后;

mysql> CREATE TABLE t4 (ID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,NAME CHAR(30) NOT NULL,GENDER ENUM('M','F') DEFAULT 'M' NOT NULL,COURSE CHAR(50) NOT NULL);
Query OK, 0 rows affected (0.08 sec)
mysql> DESC t4;
+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| ID     | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| NAME   | char(30)         | NO   |     | NULL    |                |
| GENDER | enum('M','F')    | NO   |     | M       |                |
| COURSE | char(50)         | NO   |     | NULL    |                |
+--------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> ALTER TABLE t4 ADD AGE TINYINT UNSIGNED NOT NULL AFTER NAME;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> DESC t4;
+--------+---------------------+------+-----+---------+----------------+
| Field  | Type                | Null | Key | Default | Extra          |
+--------+---------------------+------+-----+---------+----------------+
| ID     | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| NAME   | char(30)            | NO   |     | NULL    |                |
| AGE    | tinyint(3) unsigned | NO   |     | NULL    |                |
| GENDER | enum('M','F')       | NO   |     | M       |                |
| COURSE | char(50)            | NO   |     | NULL    |                |
+--------+---------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> INSERT INTO t4 (NAME,AGE,GENDER,COURSE) VALUE('Ling Huchong',24,'M','Huashanpai'),('Huang Rong',19,'F','Chilian Shenzhang')
    -> ;
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> INSERT INTO t4 (NAME,AGE,GENDER,COURSE) VALUE('Lu Wushang',18,'F','Jiuyang Shengong'),('Zhu ziliu',52,'M','Pixie Jianfa'),('Chen Jialuo',22,'M','Xianglong Shiba Zhang'),('Ou Yangfeng',70,'M','Shenxiang bannuo gong');
Query OK, 4 rows affected (0.04 sec)
Records: 4  Duplicates: 0  Warnings: 0
mysql> SELECT * FROM t4;
+----+--------------+-----+--------+-----------------------+
| ID | NAME         | AGE | GENDER | COURSE                |
+----+--------------+-----+--------+-----------------------+
|  1 | Ling Huchong |  24 | M      | Huashanpai            |
|  2 | Huang Rong   |  19 | F      | Chilian Shenzhang     |
|  3 | Lu Wushang   |  18 | F      | Jiuyang Shengong      |
|  4 | Zhu ziliu    |  52 | M      | Pixie Jianfa          |
|  5 | Chen Jialuo  |  22 | M      | Xianglong Shiba Zhang |
|  6 | Ou Yangfeng  |  70 | M      | Shenxiang bannuo gong |
+----+--------------+-----+--------+-----------------------+
6 rows in set (0.02 sec)
mysql> DESC t4;
+--------+---------------------+------+-----+---------+----------------+
| Field  | Type                | Null | Key | Default | Extra          |
+--------+---------------------+------+-----+---------+----------------+
| ID     | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| NAME   | char(30)            | NO   |     | NULL    |                |
| AGE    | tinyint(3) unsigned | NO   |     | NULL    |                |
| GENDER | enum('M','F')       | NO   |     | M       |                |
| COURSE | char(50)            | NO   |     | NULL    |                |
+--------+---------------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
mysql> ALTER TABLE t4 ADD CLASS CHAR(40) NOT NULL AFTER NAME;
Query OK, 6 rows affected (0.10 sec)
Records: 6  Duplicates: 0  Warnings: 0
mysql> DESC t4;
+--------+---------------------+------+-----+---------+----------------+
| Field  | Type                | Null | Key | Default | Extra          |
+--------+---------------------+------+-----+---------+----------------+
| ID     | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| NAME   | char(30)            | NO   |     | NULL    |                |
| CLASS  | char(40)            | NO   |     | NULL    |                |
| AGE    | tinyint(3) unsigned | NO   |     | NULL    |                |
| GENDER | enum('M','F')       | NO   |     | M       |                |
| COURSE | char(50)            | NO   |     | NULL    |                |
+--------+---------------------+------+-----+---------+----------------+
6 rows in set (0.02 sec)
mysql> ALTER TABLE t4 CHANGE ID TID INT UNSIGNED NOT NULL AUTO_INCREMENT;
Query OK, 6 rows affected (0.11 sec)
Records: 6  Duplicates: 0  Warnings: 0
mysql> DESC t4;
+--------+---------------------+------+-----+---------+----------------+
| Field  | Type                | Null | Key | Default | Extra          |
+--------+---------------------+------+-----+---------+----------------+
| TID    | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| NAME   | char(30)            | NO   |     | NULL    |                |
| CLASS  | char(40)            | NO   |     | NULL    |                |
| AGE    | tinyint(3) unsigned | NO   |     | NULL    |                |
| GENDER | enum('M','F')       | NO   |     | M       |                |
| COURSE | char(50)            | NO   |     | NULL    |                |
+--------+---------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
mysql> ALTER TABLE t4 MODIFY AGE TINYINT UNSIGNED NOT NULL AFTER COURSE;
Query OK, 6 rows affected (0.07 sec)
Records: 6  Duplicates: 0  Warnings: 0
mysql> DESC t4;
+--------+---------------------+------+-----+---------+----------------+
| Field  | Type                | Null | Key | Default | Extra          |
+--------+---------------------+------+-----+---------+----------------+
| TID    | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| NAME   | char(30)            | NO   |     | NULL    |                |
| CLASS  | char(40)            | NO   |     | NULL    |                |
| GENDER | enum('M','F')       | NO   |     | M       |                |
| COURSE | char(50)            | NO   |     | NULL    |                |
| AGE    | tinyint(3) unsigned | NO   |     | NULL    |                |
+--------+---------------------+------+-----+---------+----------------+
6 rows in set (0.02 sec)

原创文章,作者:黑白子,如若转载,请注明出处:http://www.178linux.com/4924

评论列表(2条)

  • stanley
    stanley 2015-06-01 19:16

    暴风雨来的有些猛烈呢,文章伊始要有些过渡或介绍就好多了,另外,标签会添加文章seo命中率哦

    • 黑白子
      黑白子 2015-06-01 21:18

      @stanley好的。