数据库备忘录

1、插入时判断重复数据，忽略或更新

insert ignore into tablename values(),();
replace into tablename values(),();

2、创建索引

create unique index u_idx_name on actor(name);

3、创建视图

create view actor_view(first_name_v) as select first_name from actor;

4、查询时强制使用索引

select * from employee indexed by idx_emp_no where emp_no = 1005;//当查询编号为1005的记录时强制使用索引

5、创建触发器

create trigger audit_log after insert on employee
begin 
insert into audit values(NEW.ID,NEW.NAME);
end;

6、删除某个字段重复的记录，仅保留id最小的那一条

delete from titles_test where id not in (select min(id) from titles_test group by emp_no)

7、修改表名

alter table titles_test rename to titles_2017

Provide feedback