创建数据库:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name
如:
mysql> CREATE SCHEMA IF NOT EXISTS users CHARACTER SET 'gbk' COLLATE 'gbk_chinese_ci';
也可以:
mysql> CREATE DATABASE users;
删除数据库:
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
创建表:
方法1:直接定义一张空表
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_options]
[partition_options]
例:
***************************************
创建表:
mysql> CREATE TABLE NAME_TB1(NAME_ID INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, NAME CHAR(30));
向该表插入数据:
mysql> INSERT INTO NAME_TB1 (NAME) VALUES ('zhangsan'),('lisi'),('wangwu');
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
查看该表数据:
mysql> SELECT * FROM NAME_TB1;
+---------+----------+
| NAME_ID | NAME |
+---------+----------+
| 1 | zhangsan |
| 2 | lisi |
| 3 | wangwu |
+---------+----------+
3 rows in set (0.00 sec)
查看表索引:
mysql> SHOW INDEXES FROM NAME_TB1;
***************************************
方法2:从其他表中查询数据,并以之创建新表
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
[table_options]
[partition_options]
select_statement
例:
**************************************
创建表:
mysql> CREATE TABLE test_name SELECT * FROM NAME_TB1 WHERE NAME_ID <=2;
Query OK, 2 rows affected (0.48 sec)
Records: 2 Duplicates: 0 Warnings: 0
查看表内容:
mysql> SELECT * FROM test_name;
+---------+----------+
| NAME_ID | NAME |
+---------+----------+
| 1 | zhangsan |
| 2 | lisi |
+---------+----------+
2 rows in set (0.01 sec)
查看对比两个表结构:
mysql> DESC test_name;
+---------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+-------+
| NAME_ID | int(10) unsigned | NO | | 0 | |
| NAME | char(30) | YES | | NULL | |
+---------+------------------+------+-----+---------+-------+
2 rows in set (0.18 sec)mysql> DESC NAME_TB1;
+---------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+----------------+
| NAME_ID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| NAME | char(30) | YES | | NULL | |
+---------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
**************************************
方法3:以其他表为模板创建一张空表:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
{ LIKE old_tbl_name | (LIKE old_tbl_name) }
例:
**************************************
创建表:
mysql> CREATE TABLE NAME_TB2 LIKE NAME_TB1;
mysql> DESC NAME_TB2;
+---------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+----------------+
| NAME_ID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| NAME | char(30) | YES | | NULL | |
+---------+------------------+------+-----+---------+----------------+
2 rows in set (0.05 sec)
**************************************
修改表定义:
ALTER [IGNORE] TABLE tbl_name
[alter_specification [, alter_specification] ...]
[partition_options]
-
添加、删除、修改字段:
- 如为name_tb1表添加一个age字段:
mysql> alter table name_tb1 add age int UNSIGNED not null; ##此处对字段的定义同创建表
mysql> desc name_tb1;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | char(20) | YES | | NULL | |
| age | int(10) unsigned | NO | | NULL | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.02 sec)
删除name_tb1表中的name字段:
mysql> alter table name_tb1 drop name;
Query OK, 0 rows affected (1.72 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> desc name_tb1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| age | int(11) | NO | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.08 sec)
修改name_tb1表中的age为name,并调整字段类型为char:
mysql> alter table name_tb1 CHANGE age name char(20);
Query OK, 0 rows affected (1.25 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> desc name_tb1;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | char(20) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.16 sec)
如果仅需修改字段属性,使用:
mysql> alter table name_tb1 col_name column_definition;
添加、删除索引:
查看当前索引:
mysql> show indexes from name_tb1;
Empty set (0.00 sec)##当前索引为空
添加索引:
如:为表name_tb1添加唯一键索引,字段为id
mysql> alter table name_tb1 add unique key (id);
Query OK, 0 rows affected (1.27 sec)
Records: 0 Duplicates: 0 Warnings: 0
删除与删除修改字段类似,不在举例!
使用create index创建索引:
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
[index_type]
ON tbl_name (index_col_name,...)
[algorithm_option | lock_option] ..
如:
mysql> CREATE INDEX name_on_usertb1 ON user_tb1 (name);
Query OK, 0 rows affected (1.41 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> SHOW INDEXES FROM user_tb1;
+----------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user_tb1 | 0 | id | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
| user_tb1 | 1 | name_on_usertb1 | 1 | name | A | 0 | NULL | NULL | YES | BTREE | | |
+----------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
修改表名:
方法一:
mysql> show tables ;
+------------------+
| Tables_in_testdb |
+------------------+
| name_tb1 |
| name_tb2 |
+------------------+
2 rows in set (0.01 sec)##将name_tb1修改为use_tb1
mysql> ALTER TABLE name_tb1 RENAME TO user_tb1;
Query OK, 0 rows affected (0.10 sec)mysql> show tables ;
+------------------+
| Tables_in_testdb |
+------------------+
| name_tb2 |
| user_tb1 |
+------------------+
2 rows in set (0.00 sec)
方法二:
mysql> RENAME TABLE name_tb2 TO test_user;
Query OK, 0 rows affected (0.15 sec)mysql> show tables ;
+------------------+
| Tables_in_testdb |
+------------------+
| test_user |
| user_tb1 |
+------------------+
2 rows in set (0.00 sec)