MySQL表常用操作

来源：互联网发布：淘宝退货原因有哪些编辑：程序博客网时间：2024/06/05 18:23

查看表的字段

mysql> desc users;

+-------------------+--------------+------+-----+---------+----------------+

+-------------------+--------------+------+-----+---------+----------------+

| user_id | int(11) | NO | PRI | NULL | auto_increment |

| sex | varchar(45) | YES | | NULL | |

| isOpening | tinyint(4) | YES | | 0 | |

| activeMobiletelno | int(11) | YES | | NULL | |

| activeEmail | int(11) | YES | | NULL | |

| signStatus | int(11) | YES | | 0 | |

+-------------------+--------------+------+-----+---------+----------------+

13 rows in set

查看表的详细信息

mysql>SHOW CREATE TABLE tbname;

mysql> show create table users \G;

*************************** 1. row ***************************

Table: users

Create Table: CREATE TABLE `users` (

`user_id` int(11) NOT NULL AUTO_INCREMENT,

`username` varchar(45) DEFAULT NULL COMMENT '登录名',

`password` varchar(45) DEFAULT NULL COMMENT '登陆密码',

`enabled` tinyint(4) DEFAULT NULL COMMENT '是否启用',

`email` varchar(145) DEFAULT NULL COMMENT '邮箱',

`create_date` datetime DEFAULT NULL COMMENT '用户创建日期',

`last_login` datetime DEFAULT NULL COMMENT '上次登陆日期',

`sex` varchar(45) DEFAULT NULL COMMENT '性别',

`isOpening` tinyint(4) DEFAULT '0' COMMENT '是否开户(1，开户，0,未开户)',

`mobiletelno` varchar(45) DEFAULT NULL COMMENT '手机号码',

`activeMobiletelno` int(11) DEFAULT NULL COMMENT '是否激活手机号（0 未激活 ;1已激活）',

`activeEmail` int(11) DEFAULT NULL COMMENT '是否激活email （0 未激活 ;1已激活）',

`signStatus` int(11) DEFAULT '0' COMMENT '用户开户进度开户状态 1.未完善信息2.完善个人信息成功,3.绑定银行卡成功,4,验证银行卡成功',

`old` int(2) DEFAULT NULL,

PRIMARY KEY (`user_id`)

) ENGINE=InnoDB AUTO_INCREMENT=114 DEFAULT CHARSET=utf8 COMMENT='用户信息表'

1 row in set (0.02 sec)

添加字段

mysql>ALTER TABLE tbname ADD field_name field_type [first | after 已有字段之后];

mysql> alter table users add age int(4) unsigned default 0 not null;

Database changed

Records: 42 Duplicates: 0 Warnings: 0

mysql> alter table users add new_id int(11) not null;

first | after 已有字段之后：指定字段在该表中的位置

删除字段

mysql>ALTER TABLE tbname DROP field_name;

mysql>alter table users drop new_id;

Database changed

Records: 42 Duplicates: 0 Warnings: 0

修改字段类型

mysql>ALTER TABLE tbname CHANGE old_field_name new_field_name field_type;

mysql>ALTER TABLE tbname MODIFY old_field_name new_field_name field_type;

mysql> alter table users change age old int(2);

Database changed

Records: 42 Duplicates: 0 Warnings: 0

mysql> alter table users change age old int(2) default 0;

Database changed

Records: 42 Duplicates: 0 Warnings: 0

修改字段的排列位置

mysql>ALTER TABLE tbname MODIFY column1 field_type first | after 已有字段之后;

mysql> alter table order_test modify tra_date datetime after sal;

Query OK, 10 rows affected

Records: 10 Duplicates: 0 Warnings: 0

重命名表

mysql>ALTER TABLE old_tbname rename new_tablename;

mysql>RENAME TABLE old_tbname to new_tablename;

mysql> alter table user_fund_relation rename user_fund;

Database changed

mysql> rename table user_fund to user_fund_relation;

Database changed

删除表

mysql>DROP TABLE tbname IF EXISTS;

mysql> drop table user_fund;

更改表的存储引擎

mysql>ALTER TABLE tbname ENGINE=InnoDB|MyISAM|MEMORY;

查看主键

mysql>SHOW CREATE TABLE tbname;

mysql>SHOW COLUMNS FROM tbname;

mysql>SHOW COLUMNS IN tbname;

mysql>SELECT t.TABLE_NAME, t.CONSTRAINT_TYPE,c.COLUMN_NAME, c.ORDINAL_POSITION

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS t,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS c
WHERE
t.TABLE_NAME = c.TABLE_NAME
AND t.TABLE_NAME = 'TBNAME' --此处表名大写
AND t.CONSTRAINT_TYPE = 'PRIMARY KEY';

添加主键

mysql>ALTER TABLE tbname ADD PRIMARY KEY(column1,...)

删除主键

mysql>ALTER TABLE tbname DROP PRIMARY KEY pk_name;

查看索引

mysql> SHOW INDEX FROM tbname;

创建索引

mysql> ALTER TABLE tbname ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name](colname...) [ASC|DESC];

mysql> CREATE [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] ON tbname (column1..) [ASC|DESC]

删除索引

mysql> ALTER TABLE tbname DROP INDEX index_name;

mysql> DROP INDEX index_name ON tbname;

0 0