MySQL语句进阶记录（二）

MySQL语句练习记录（二）

1、创建表时导入数据

SQL230 创建一个actor_name表

# 可以在创建表的时候，执行查询语句，查询的结果将按照字段导入数据表中
create table if not exists actor_name(
    first_name varchar(45) not null comment "名字",
    last_name varchar(45) not null comment "姓氏"
)
select first_name,last_name from actor;

2、创建索引

SQL231 对first_name创建唯一索引uniq_idx_firstname

# 1、该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
ALTER TABLE tbl_name ADD PRIMARY KEY (col_list);
# 2、这条语句创建索引的值必须是唯一的。
ALTER TABLE tbl_name ADD UNIQUE index_name (col_list);
# 3、添加普通索引，索引值可出现多次。
ALTER TABLE tbl_name ADD INDEX index_name (col_list);
# 4、该语句指定了索引为 FULLTEXT ，用于全文索引。
ALTER TABLE tbl_name ADD FULLTEXT index_name (col_list);
# 5、删除索引
DROP INDEX index_name ON tbl_name; 
// 或者
ALTER TABLE tbl_name DROP INDEX index_name；
ALTER TABLE tbl_name DROP PRIMARY KEY;

以上内容摘抄自MySQL中创建四种不同类型索引的方法

3、创建视图

SQL232 针对actor表创建视图actor_name_view

# 直接在视图名的后面用小括号创建视图中的字段名
create view actor_name_view (first_name_v,last_name_v) as
select first_name ,last_name from actor
# 在select后面对列重命名为视图的字段名
create view actor_name_view as
select first_name as first_name_v
       ,last_name as last_name_v
from actor

以上内容摘抄自【Mysql】针对actor表创建视图actor_name

4、强制使用索引

有些情况下会没有走索引，那么这时可以强制让其使用索引，进而加快查询速度。

针对上面的salaries表emp_no字段创建索引idx_emp_no

# 强制走索引主要的语句：force index(索引名)
select
	*
from
	emp_no
force index(idx_emp_no)
where
	emp_no = '10005';

5、创建触发器

触发器可以做很多东西，比如插入数据的时候可以自定义id或者做一些业务上的操作。

构造一个触发器audit_log

/*
	创建触发器
	CREATE TRIGGER 触发器名称
	BEFORE|AFTER INSERT|UPDATE|DELETE	六种触发情况
	ON 表名
	FOR EACH ROW
	BEGIN
		触发器要执行的功能;
	END
*/
-- 创建INSERT型触发器。用于对account表新增数据进行日志的记录
CREATE TRIGGER account_insert
AFTER INSERT
ON account
FOR EACH ROW
BEGIN
	INSERT INTO account_log VALUES (NULL,'INSERT',NOW(),new.id,CONCAT('插入后{id=',new.id,',name=',new.name,',money=',new.money,'}'));
END



# 设置主键为uuid
CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    TRIGGER `ssm`.`id_trigger` 	-- 触发器名称
    BEFORE INSERT 		-- 触发器被触发的时机
    ON `ssm`.`product` 		-- 触发器所作用的表名称
    FOR EACH ROW BEGIN
	SET new.id=REPLACE(UUID(),'-',''); -- 触发器执行的逻辑
    END

# 本题答案
create trigger audit_log
after insert
on employees_test
for each row
begin 
    insert into audit value(new.id,new.name);
end

6、MySQL不允许使用一张表查询出来的数据去更新同一张表

删除emp_no重复的记录，只保留最小的id对应的记录。

# 错误写法
delete from titles_test WHERE id not in
(
    select min(id) from titles_test group by emp_no
);
# 正确写法，将子查询出来的数据重新命名再使用
delete from titles_test where id not in
(
	select * from (
    	select min(id) from titles_test group by emp_no
    )	t
)