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.www58058.com/4924
暴風雨來的有些猛烈呢，文章伊始要有些過渡或介紹就好多了，另外，標簽會添加文章seo命中率哦
@stanley：好的。