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;

 

阅读更多
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/weixin_38500325/article/details/81592026
个人分类: Database(数据库)
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭