MySQL添加列的方法:
(1) 添加单列:ALTER TABLE table_name ADD [column] column_name column_definition [FIRST|AFTER] ; 不使用小括号,单列可以设置添加的位置
(2) 添加多列:ALTER TABLE table_name ADD [column] (column_name column_definition,...); 要使用小括号,多列不可以设置添加的位置。
MySQL删除列的方法:
ALTER TABLE table_name DROP [column] column_name; 删除多列,列之间用逗号分开。删除的同时要增加列也是可以的,要用逗号分开。
修改列定义:
alter table table_name modify column_name column_definition [first|after column_name];
修改列名称及列定义:
alter table table_name change old_column_name new_column_name column_definition [first|after column_name];
修改表名称:
alter table table_name rename [to|as] new_table_name;
MySQL添加约束的方法:
(1) 添加主键约束:ALTER TABLE table_name ADD PRIMARY KEY(column_name);
(2) 添加外键约束:ALTER TABLE table_name ADD FOREIGN KEY(column_name,...) REFERENCES
table_name(column_name);
(3) 添加唯一约束:ALTER TABLE table_name ADD UNIQUE KEY(column_name,...);
(4)添加默认约束:
ALTER TABLE table_name ALTER column_name SET DEFAULT literal;
MySQL删除约束的方法:
(1) 删除主键约束:ALTER TABLE table_name DROP PRIMARY KEY;
(2) 删除唯一约束:ALTER TABLE table_name DROP {INDEX|KEY} index_name;
(2) 删除外键约束: ALTER TABLE table_name DROP FOREIGN KEY fk_name;
条件查询:
简单条件where子句:
select column_name,... from table_name where where_condition;
分组查询group by:
select {column_name [...]} from table_name where where_condition group by {column_name|position}[[ASC|DESC],...];
设置分组条件HAVING:
注:使用HAVING时,HAVING后面可以跟聚合函数或字段,如果跟的是字段,则字段必须出现在当前的select语句当中。
select {column_name [...]} from table_name where where_condition group by {column_name|position} [[ASC|DESC],...] HAVING where_condition;
对查询结果进行排序order by:
select {column_name [...]} from table_name [where where_condition group by {column_name|position} [[ASC|DESC],...] HAVING where_condition] order by {column_name|expr|position..} [ASC|DESC...];
限制查询结果返回的数量limit:
select column_name from table_name [where where_condition] limit {[off_set],row_count|row_count OFFSET offset};