创建数据表
CREATE TABLE [IF NOT EXISTS] table_name(
column_name data_type,
....
)
eg:
CREATE TABLE tb1(
username VARCHAR(20),
age TINYINT UNSGINED,
salary FLOAT(8, 2) UNSIGNED
);
删除数据表
DROP TABLE table_name
查看数据表
SHOW TABLES [FROM db_name]
查看数据表结构
SHOW COLUMNS FROM table_name
或者 DESC table_name
插入记录
INSERT [INTO] table_name [(col_name,...)] VALUES(val,...)
Tip:省略掉列名和字段名时,必须为所有字段赋值
eg:INSERT tb1 VALUES('Tom', 25, 7856.15); //全部字段赋值
INSERT tb1(username, salary) VALUES('John', 4500.00); //指定字段赋值
记录查找
SELECT expr,...FROM table_name
eg:SELECT * FROM tb1; //显示表数据
SELECT COLUMNS FROM tb1; //显示表结构
显示索引
SHOW INDEX FROM table_name
修改表名
ALTER TABLE old_table_name RENAME [TO] new_table_name
增加字段
ALTER TABLE table_name ADD col_name(属性名) col_definition(数据类型) [FIRST | AFTER] col_name
增加多字段
ALTER TABLE table_name ADD (col_name col_definition, ...)
删除字段
ALTER TABLE table_name DROP col_name
修改字段的数据类型
ALTER TABLE table_name MODIFY col_name col_definition
修改字段的名字
ALTER TABLE table_name CHANGE old_col_name new_col_name old_col_definition
同时修改字段的名字和属性
ALTER TABLE table_name CHANGE old_col_name new_col_name new_col_definition
修改字段的顺序
ALTER TABLE table_name MODIFY col_name1 col_definition FIRST | AFTER col_name2 new_col_definition
添加主键约束
ALTER TABLE table_name ADD [CONSTRAINT[symbol]] PRIMARY KEY [index_type](index_col_name, ...)
eg: ALTER TABLE tb2 ADD PRIMARY KEY (username);
删除主键约束
ALTER TABLE table_name DROP PRIMARY KEY
eg: ALTER TABLE table_name DROP PRIMRY KEY;
添加唯一约束
ALTER TABLE table_name ADD [CONSTRAINT[symbol]] UNIQUE [INDEX | KEY][index_name] [index_type] (index_col_name, ...)
eg: ALTER TABLE tb2 ADD UNIQUE (id);
删除唯一约束
ALTER TABLE table_name DROP {INDEX | KEY} index_name
eg: ALTER TABLE tb2 DROP INDEX id;
添加外键约束
ALTER TABLE table_name ADD [CONSTRAINT[symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition
添加/删除默认约束
ALTER TABLE table_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
转载于:https://my.oschina.net/FtBeat/blog/358808