选择数据库
USE database;
创建表
CREATE TABLE [IF NOT EXISTS] table_name ( column_name data_type, ... );
查看表
SHOW TABLES [FROM db_name] [LIKE 'pattern' | WHERE expr];
查看表结构
SHOW COLUMNS FROM tbl_name;
插入记录
INSERT [INTO] tbl_name [(col_name, ...)] VALUES(val, ...);
- 字段是否可以为空:
NULL or NOT NULL
- 自增:
AUTO_INCREMENT
- 主键:
PRIMARY KEY
- 唯一性:
UNIQUE KEY
- 默认值:
DEFAULT
- 外键:
FOREIGN KEY (column_name) REFERENCES tbl_name (column_name) [ON DELETE rule];
- CASCADE: 从父表删除或更新自动删除或更新更新子表中的行
- SET NULL: 从父表更新或删除,自动设置子表NULL,必须子表没有NOT NULL
- RESTRICT: 拒绝对父表更新或删除
- NO ACTION: 拒绝对父表更新或删除
- 字段是否可以为空:
查询记录
SELECT expr, ... FROM tbl_name;
查看索引
SHOW INDEXS FROM tbl_name[\G];
修改数据表
添加单列
ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name];
添加多列
ALTER TABLE tbl_name ADD [COLUMN] (col_name column_definition, ...);
删除列
ALTER TABLE tbl_name DROP [COLUMN] col_name;
修改列的操作可以同时进行多个,用逗号隔开。
添加主键约束
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name, ...);
添加唯一约束
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name, ...);
添加外键约束
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name, ...) reference_definition;
添加或删除默认约束
ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT};
删除主键约束
ALTER TABLE tbl_name DROP PRIMARY KEY;
删除唯一约束
ALTER TABLE tbl_name DROP {INDEX|KEY} index_name;
删除外键约束
ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;
修改列定义
ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name];
修改列名称
ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name];
修改数据表
ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name; RENAME TABLE tbl_name TO new_tbl_name [, tbl_name2 TO new_tbl_name2] ...;
mysql常用命令(三)
最新推荐文章于 2024-08-23 09:39:07 发布