MYSQL中字符大小写：
1、SQL关键字及函数名不区分字符大小写
2、数据库、表及视图名称的大小区分与否取决于底层OS及FS
3、存储过程、存储函数及时间调度器的名字不区分大小写，但触发器区分
4、表别名区分大小写
5、对字段中的数据，如果字段类型为Binary类型，则区分大小写，非Binary不区分大小写
数据库：
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] [CHARACTER SET=''] [DEFAULT][COLLATE=' '] [DEFAULT] [COLLATE=' ']
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
ALTER {DATABASE|SCHEMA} db_name [DEFAULT] [CHARACTER SET=' '] [DEFAULT] [COLLATE=' ']
表：
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 [=] character_name
[DEFAULT] COLLATE [=] collation_name
COMMENT [=] 'string'
DELAY_KEY_WRITE [=] {0 | 1}
ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
MyISAM表，每表有三个文件，都位于数据库目录中：
tb_name.frm: 表结构定义
tb_name.MYD: 数据文件
tn_name.MYI: 索引文件
InnoDB表，有两种存储方式
1、默认：每表有一个独立文件和一个多表共享文件
tb_name.frm: 表结构的定义，位于数据库目录中：
ibdata#: 共享的表空间文件，默认位于数据目录(datadir指向的目录)中：
2、独立的表空间：
tb_name.frm: 每表有一个表结构文件
tb_name.ibd: 一个独立的表空间文件
注意：使用独立的表空间要修改主配置文件/etc/my.cnf，把innodb_file_per_table = ON 加入到配置文件中。
表创建： 第二种方式（复制表数据）
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
[table_options]
select_statement
表创建： 第三种方式（复制表结构）
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
{ LIKE old_tbl_name | (LIKE old_tbl_name) }
表删除：
DROP [TEMPORARY] TABLE [IF EXISTS]
tbl_name [, tbl_name] ...
[RESTRICT | CASCADE]
表修改：
ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name
[alter_specification [, alter_specification] ...]
修改字段定义：
插入新字段
删除字段
修改字段
修改字段名称
CHANGE [COLUMN] old_col_name new_col_name column_definition
[FIRST|AFTER col_name]
修改字段类型及属性等
MODIFY [COLUMN] col_name column_definition
[FIRST | AFTER col_name]
修改约束、键或索引
表改名：
RENAME [TO|AS] new_tbl_name
RENAME TABLE old_name TO new_name;
制定排序标准的字段：
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}]
建表示例：
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| NULL |
+------------+
1 row in set (0.00 sec)
mysql> use mydb;
Database changed
mysql> CREATE TABLE t3 (Name VARCHAR(50) NOT NULL, Age TINYINT UNSIGNED NOT NULL,PRIMARY KEY(Name,Age));
Query OK, 0 rows affected (0.05 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)
查看存储引擎：
mysql> SHOW ENGINES;
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |
| InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
5 rows in set (0.01 sec)
查看刚创建t3表的存储引擎：
mysql> SHOW TABLE STATUS LIKE 't3'\G
*************************** 1. row ***************************
Name: t3
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: 2017-02-17 06:33:32
Update_time: 2017-02-17 06:33:32
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
示例： 创建表T1
mysql> CREATE TABLE T1(Name VARCHAR(30) NOT NULL,ID INT(10) UNSIGNED NOT NULL,PRIMARY KEY(ID));
Query OK, 0 rows affected (0.06 sec)
mysql> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| T1 |
| t3 |
+----------------+
2 rows in set (0.00 sec)
mysql> desc T1;
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| Name | varchar(30) | NO | | NULL | |
| ID | int(10) unsigned | NO | PRI | NULL | |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
参照旧表创建新表：
mysql> CREATE TABLE t6 LIKE T1;
Query OK, 0 rows affected (0.03 sec)
mysql> DESC t6;
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| Name | varchar(30) | NO | | NULL | |
| ID | int(10) unsigned | NO | PRI | NULL | |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
插入字段Age：
mysql> ALTER TABLE t6 ADD Age TINYINT UNSIGNED NOT NULL;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t6;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| Name | varchar(30) | NO | | NULL | |
| ID | int(10) unsigned | NO | PRI | NULL | |
| Age | tinyint(3) unsigned | NO | | NULL | |
+-------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
插入字段Gender在Name后面
mysql> ALTER TABLE t6 ADD Gender ENUM('M','F') NOT NULL DEFAULT 'M' AFTER Name;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t6;
+--------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| Name | varchar(30) | NO | | NULL | |
| Gender | enum('M','F') | NO | | M | |
| ID | int(10) unsigned | NO | PRI | NULL | |
| Age | tinyint(3) unsigned | NO | | NULL | |
+--------+---------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
删除Age字段：
mysql> ALTER TABLE t6 DROP Age;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t6;
+--------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+-------+
| Name | varchar(30) | NO | | NULL | |
| Gender | enum('M','F') | NO | | M | |
| ID | int(10) unsigned | NO | PRI | NULL | |
+--------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
修改字段Gender：
mysql> desc t6;
+--------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+-------+
| Name | varchar(30) | NO | | NULL | |
| Gender | enum('M','F') | NO | | M | |
| ID | int(10) unsigned | NO | PRI | NULL | |
+--------+------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> ALTER TABLE t6 MODIFY Gender ENUM('M','F') NOT NULL AFTER ID;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t6;
+--------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+-------+
| Name | varchar(30) | NO | | NULL | |
| ID | int(10) unsigned | NO | PRI | NULL | |
| Gender | enum('M','F') | NO | | NULL | |
+--------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
修改字段名称Name为Student：
mysql> ALTER TABLE t6 CHANGE Name Student varchar(30) NOT NULL;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t6;
+---------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+-------+
| Student | varchar(30) | NO | | NULL | |
| ID | int(10) unsigned | NO | PRI | NULL | |
| Gender | enum('M','F') | NO | | NULL | |
+---------+------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
创建索引:
mysql> ALTER TABLE t6 ADD INDEX (Student);
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
查看索引：
mysql> SHOW INDEXES FROM t6;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| t6 | 0 | PRIMARY | 1 | ID | A | 0 | NULL | NULL | | BTREE | |
| t6 | 1 | Student | 1 | Student | A | NULL | NULL | NULL | | BTREE | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)
删除索引：
mysql> ALTER TABLE t6 DROP INDEX Student;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
查看索引：
mysql> SHOW INDEXES FROM t6;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| t6 | 0 | PRIMARY | 1 | ID | A | 0 | NULL | NULL | | BTREE | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)
表改名：（把名称t6改为名称t7）
mysql> ALTER TABLE t6 RENAME TO t7;
Query OK, 0 rows affected (0.01 sec)
查看所有的表：
mysql> SHOW TABLES;
+----------------+
| Tables_in_mydb |
+----------------+
| T1 |
| t3 |
| t7 |
+----------------+
3 rows in set (0.00 sec)
查看并修改表的存储引擎：
mysql> SHOW TABLE STATUS LIKE 't7'\G
*************************** 1. row ***************************
Name: t7
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: 2017-02-18 08:36:14
Update_time: 2017-02-18 08:36:14
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
mysql> ALTER TABLE t7 ENGINE=InnoDB;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW TABLE STATUS LIKE 't7'\G
*************************** 1. row ***************************
Name: t7
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: 4194304
Auto_increment: NULL
Create_time: 2017-02-18 08:53:30
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
阅读(1174) | 评论(0) | 转发(0) |
