1. index相关
create index indexname on tablename(columnname);
show index fron tablename;
alert table tablename add index(columnname); //此时indexname=columnname
alert table tablename add index indexname(columnname);
drop index indexname on tablename
2. 外键相关
create table tablename(
id int ,
cid int,
index indexid(cid),
(constraint fk_1) foreign key (cid) reference othertable(column)on delete,inport cascade);
ALTER TABLE yourtablename
ADD [CONSTRAINT 外键名] FOREIGN KEY [id] (index_col_name, ...)
REFERENCES tbl_name (index_col_name, ...)
[ON DELETE {CASCADE | SET NULL | NO ACTION | RESTRICT}]
[ON UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT}]
删除外键:
alter table tablename drop foreign key fk_1;
添加外键:
alter table tablename add constraint fk_1 foreign key (parent_id) references parent(id) on update restrict on delete set null;
1. CASCADE: 从父表中删除或更新对应的行,同时自动的删除或更新自表中匹配的行。ON DELETE CANSCADE和ON UPDATE CANSCADE都被InnoDB所支持。
2. SET NULL: 从父表中删除或更新对应的行,同时将子表中的外键列设为空。注意,这些在外键列没有被设为NOT NULL时才有效。ON DELETE SET NULL和ON UPDATE SET SET NULL都被InnoDB所支持。
3. NO ACTION: InnoDB拒绝删除或者更新父表。
4. RESTRICT: 拒绝删除或者更新父表。指定RESTRICT(或者NO ACTION)和忽略ON DELETE或者ON UPDATE选项的效果是一样的。
5. SET DEFAULT: InnoDB目前不支持。
show create table tablename;
3.order by
select * from tablename where () order by columnname;(columna不必出现在查询的列表中)
多个排序 order by column1,column2;
order by column1 desc,column2 asc;
4. group by
select * from tablename group by columnaname;//group的列可以不再select中
select * from tablename group by columnanme order by columnname. //order 在group 之后
having 是group 的扩展,用来过滤分组的;
where > 聚合函数 > having
5.distinct
select distinct column from tablename;
select distinct column,column2 from tablename;
6.in
select * from user where id in(1,2);
select * from user where id in(1,2) order by field(id,2,1);
7.case when
select name, case
when name>'t' then 'w'
else 't' end
from user;
select name, case
when name>'t' then 'w'
else 't' end hello //hello是列名
from user;
8. exists
CREATE TABLE if not exists child(id INT, parent_id INT,
INDEX par_ind (parent_id),
FOREIGN KEY (parent_id) REFERENCES parent(id)
ON DELETE CASCADE
);
drop table if exists child;