-
批量插入数据,不使用replace操作
inset ignore into user(id,name) values(1,'zhang'),(2,'mysql');
-
创建一个表,从其他表中填充数据,
t1
为要插入数据的表,t2
为获取数据的表- 两张表字段一致
insert into t1 select * from t2;
t1
与t2
的部分字段一致insert into t1 select field1,field2 from t2;
insert into t1 (field1,field2) select field1,field2 from t2;
- 从多个表查数据,t1插入数据从t2和t3获取
insert into t1 select t3.*,t2.* from t2 left join t3 ON t2.id = t3.id;
- 两张表字段一致
-
对于已经创建的表,创建索引
- 唯一索引:
alter table user add unique uniq_idx_name (name);;
- 普通索引:
alter table user add index idx_mobile (mobile);;
- 全文索引:
alter table user add fulltext fulltext_description (description);
- 主键索引:
alter table user add primary key pk_id (id);
- 唯一索引:
-
创建视图
create view user_view (v_id,v_name) as select id,name from user;
-
强制使用索引
select * from user force(idx_name)
-
对于已经创建完成的表,新添加一列
- 末尾添加:
alter table user add column create_at datetime not null;
- 开头添加:
alter table user add column create_at datetime not null first;
- 指定字段之后添加:
alter table user add column create_at datetime not null after name;
- 末尾添加:
-
删除列
alter table user drop create_at;
-
修改表名
alter table user rename users;
-
字段名修改
alter table user change name names varchar(20);
-
添加外键
alter table <数据库表名> add constraint <外键名> foreign key(列名) references <主表名>(列名)
alter table user add constraint fk_user_id foreign key (uid) references student(sid);
-
删除外键
alter table <表名> drop foreign key <外键约束名>
-
触发器
-
# 创建 create trigger trigger_name trigger_time trigger_event on table_name for each row begin #触发器内容主体每行以;结束 end; # 例子 create trigger audit_log after insert on employees_test for each row begin INSERT INTO audit VALUES (new.id,new.name); end; # trigger_name:触发器名称 # trigger_time:触发时机,after|before # trigger_name:触发事件,update|delete|insert # table_name:触发事件操作表的名称 # for each row:表示任何一条记录上的操作满足触发事件都会触发该触发器 # 查看触发器 show triggers; # 查看创建触发器语句 show create trigger trigger_name; # 删除触发器 delete trigger trigger_name; # MySQL 中定义了 NEW 和 OLD 两个临时表,用来表示触发器的所在表中,触发了触发器的哪一行数据,来引用触发器中发生变化的记录内容 # 在 INSERT 型触发器中,NEW 用来拦截并保存将要(BEFORE)或已经(AFTER)插入的新数据; # 在 UPDATE 型触发器中,OLD用来拦截并保存将要或已经被修改的原数据,NEW 用来拦截并保存将要或已经修改为的新数据。 # 在 DELETE 型触发器中,OLD 用来拦截并保存将要或已经被删除的原数据。
-
-
根据class删除重复记录,保留最小id对应记录
delete from user where id not in (select * from (select min(id) from user group by class) c) ;
-
将id=5以及name=‘zhang’的记录替换为id=5以及name=‘mysql’
update user set name=replace(name,'zhang','mysql') where id = 5;
replace into user(id,name) values(5,'mysql');
insert into user(id,name) values(5,'mysql') on duplicate key update name='mysql';
-
多表的更新
update user u join role r on u.id=r.uid set nickname='管理员' where create_at='2222-02-22';
update user set nickname='管理员' where create_at='2222-02-22' and id in (select uid from role);
-
查询字符串中逗号出现的次数
select id,length(string)-length(replace(string,",","")) from strings;
-
按照nickname最后两个字符升序输出
select nickname from user order by right(nickname,2) asc;
-
按照class汇总,将属于同一class的stu用逗号连接
select id,group_concat(uid) from grade group by class;
-
使用
exists
关键字查找未分配班级的学生select name from user u where not exists (select uid from class c where c.uid=u.id);
-
获取有奖金员工的信息
-
select e.emp_no,e.first_name,e.last_name,eb.btype,s.salary, case eb.btype when 1 then s.salary*0.1 when 2 then s.salary*0.2 else s.salary*0.3 end bonus from emp_bonus eb,salaries s,employees e where e.emp_no=eb.emp_no and s.emp_no=e.emp_no and s.to_date='9999-01-01'
-