修改数据表

1.添加单列
ALTER TABLE tabl_name ADD [COLUMN] col_name colimn_definition [FIRST | AFTER col_name]
ALTER table users ADD address VARCHAR(200) NULL AFTER pid;
2.添加多列
ALTER TABLE tab_name ADD[COLUMN] (coi_name column_definition…..)
ALTER table users ADD (email VARCHAR(200) NULL ,phone VARCHAR(11) NOT NULL)
3.删除列
ALTER TABLE tab_name DROP[COLUMN] col_name
ALTER TABLE users DROP phone;
4.添加主键约束
ALTER TABLE tbl_name ADD[CONSTRAINT[symbol]] PRIMARY KEY index_type
ALTER TABLE users ADD CONSTRAINT pk_users_id PRIMARY KEY (id);
5.添加唯一约束
ALTER TABLE tbl_name ADD[CONSTRAINT[symbol]] UNIQUE [INDEX|KEY][index_name] index_type
ALTER TABLE users ADD UNIQUE (username);
6.添加外键约束
ALTER TABLE tbl_name ADD [CONSTRAINT[symbol]] FOREIGN KEY [index_col_name] reference_definition
ALTER TABLE users ADD FOREIGN KEY (pid) REFERENCES tb2(id);
#tb2是主表 pid是含有外键的从表
7.删除主键约束
ALTER TABLE tbl_name DROP PRIMARY KEY
8.删除唯一约束
ALTER TABLE tbl_name DROP {INDEX|KEY} index_name
9.删除外键约束
ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol
10.修改列定义
ALTER TABLE tbl_name MODIFY[COLUMN] col_name column_definition [FIRST|AFTER col_name]
alter table users MODIFY email LONGTEXT;
11.修改列名称
ALTER TABLE tbl_name CHANGE[COLUMN] ald_col_name new_col_name column_definition [FIRST|AFTER col_name]
alter table users CHANGE email newemail VARCHAR(30);
12.数据表更名
ALTER TABLE tbl_name RENAME[TO|AS] new_tbl_name;
13.插入记录
INSERT [INTO] tbl_name [(col_name,…)] {VALUES|VALUE} ({exper|DEFAULT},…),(…),(…)
INSERT [INTO] tbl_name SET col_name={expr|DEFAULT},…
14.更新记录
UPDATE [LOW_PRIORITY][IGNORE] table_reference SET col_name1={expr|DEFAULT}[,col_name2={expr2|DEFAULT}] …[WHERE where_condition]
UPDATE users set age=age+5;
15.删除记录
DELETE FROM tbl_name [WHERE where_condition]
16.查找记录
SELECT select_sxpr[,select_expr …]
[
FROM tab_reference
[WHERE where_condition]
[GROUP BY{col_name|position} [ASC|DESC],…]
[HAVING where_condition]
[ORDER BY {col_name|expr|position}[ASC|DESC],…]
[LIMIT {[offset,]row_count|roe_count OFFSET pffset}]
]