MySQL基础 - 4-1、数据表操作 - 《MySQL（待整理）》

创建数据表
修改表的字符编码

创建数据表

CREATE TABLE [IF NOT EXISTS] table_name(column_name data_type,…);

mysql> create table if not exists tb1(
    -> username varchar(20),
    -> age tinyint unsigned,
    -> salary float(8,2) unsigned
    -> );
Query OK, 0 rows affected (0.04 sec)

定义数据表的字符编码 ``` mysql> create table tb12( -> id int primary key -> )ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.01 sec)

mysql> show create table tb12; +———-+—————————————————————————————————————————————————————+ | Table | Create Table | +———-+—————————————————————————————————————————————————————+ | tb12 | CREATE TABLE tb12 ( id int(11) NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +———-+—————————————————————————————————————————————————————+ 1 row in set (0.00 sec)

<a name="EgwSQ"></a>
### 复制表

- 只会复制表结构，数据并没有
> create table bookcopy like book;


<a name="rBCRp"></a>
### 查看数据表

- 查看表列表
> SHOW TABLES [ FROM mysql ];

mysql> show tables; +———————+ | Tables_in_t1 | +———————+ | tb1 | +———————+ 1 row in set (0.00 sec)


- 查看表结构，两种方式都行
> SHOW COLUMNS FROM tb_name;
> desc tb_name;
> show create table tb_name;

<a name="RFVgO"></a>
### 删除表
> drop table [if exists] table_name;

mysql> drop table tb1; Query OK, 0 rows affected (0.05 sec)

mysql> drop table tb1; ERROR 1051 (42S02): Unknown table ‘t1.tb1’ mysql> drop table if exists tb1; Query OK, 0 rows affected, 1 warning (0.00 sec)

<a name="St5Ji"></a>
###  修改数据表名

- 修改数据表名称
> ALTER TABLE tb_name **RENAME** [TO|AS] new_tal_name;
RENAME TABLE tb1_name TO new tb1_name [,tb2_name TO new tb2_name ];> <br />

- 例子
> ALTER TABLE users2 RENAME users3;
> RENAME TABLE users3 TO users4;

mysql> ALTER TABLE users2 RENAME users3; Query OK, 0 rows affected (0.04 sec)

mysql> RENAME TABLE users3 TO users4; Query OK, 0 rows affected (0.05 sec)

修改表的字符编码

alter table book character set utf8;