MySQL 数据表增删改查

创建数据表

（最后一个不+，）

CREATE TABLE [IF NOT EXISTS] table_name(

column_name DATATYPE [UNSIGNED] [AUTO_INCREMENT] [PRIMARY KEY]

[ NULL | NOT NULL],

...

);

• UTO_INCREMENT 自动编号：必须和主键一起用，起始为1，每次+1

 

eg.：test表pid，参照user表中的id

CREATE TABLE test (

id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

username VARCHAR(20) NOT NULL UNIQUE KEY,

age TINYINT UNSIGNED NULL,

sex ENUM('W','M',N) DEFAULT 'N',

pid BIGINT,

FOREIGN KEY (pid) REFERENCES user (id) ON DELETE CASCADE

);

修改数据表

ALTER TABLE tb_name RENAME [TO | AS ] new_tb_name;

RENAME TABLE tb_name TO new_tb_name

[,tb_name2 TO new_tb_name2]...;

添加单列

ALTER TABLE tb_name ADD [COLUMN] col_name

column_definition [ FIRST | AFTER col_name ] ;

eg.：

ALTER TABLE test ADD COLUMN password

VARCHAR(20) NOT NULL AFTER;

添加多列

ALTER TABLE tb_name ADD [COLUMN]

( col_name column_definition ,...);

eg.：

ALTER TABLE user ADD COLUMN

( age SMALLINT ,

address VARCHAR(32)

);

删除

删除单列

ALTER TABLE tb_name DROP [COLUMN] col_name;

eg.：

ALTER TABLE user DROP COLUMN password;

删除多列 

ALTER TABLE tb_name DROP COLUMN col_name1,
                    DROP COLUMN col_name2,
                    ...
                    ;

 eg.：

ALTER TABLE user DROP COLUMN sex,
                 DROP COLUMN age;

修改列定义

ALTER TABLE tb_name MODIFY [COLUMN] col_name

column_definition [FIRST | AFTER col_name]

修改列名称

ALTER TABLE tb_name CHANGE [COLUMN] old_col_name

new_col_name column_definition [FIRST | AFTER col_name]

查看数据表

查看数据库中存在的数据表

SHOW TABLES [FROM db_name ]

[ LIKE 'pattern' | WHERE expr ]

eg.：

SHOW TABLES FROM test;

查看数据表结构

SHOW COLUMNS FROM tb_name;

eg.：

SHOW COLUMNS FROM test;