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条）
暴风雨来的有些猛烈呢，文章伊始要有些过渡或介绍就好多了，另外，标签会添加文章seo命中率哦
@stanley：好的。