Mysql 《MySQL必知必会》
一、检索数据(第四章)
1. 避免使用Distinct方法去重,该方法对比一行字段的所有值,影响效率
2. 慎用子查询:
- 2.1 在where后面增加一次查询
- 2.2 在where前有多少条数据,就增加多少次查询
二、联合查询(第十六章)
1. Mysql复杂语句执行顺序
select customers.cust_name,
customers.cust_id,
COUNT(orders.order_num) AS num_ord
from customers INNER JOIN orders ON
customers.cust_id = orders.cust_id
GROUP BY customers.cust_id
- 先加载From后面的表
- 然后执行内连接子语句(不用连接则执行Where的子语句)
- 执行分组
- 查询select后面的每一组对应字段(过滤)
三、全文检索(第十八章)
1. 全文检索使用方法
- 首先在创建表的时候,需要用FULLTEXT 指定哪个字段可以进行全文检索
- 在where后面使用Match(字段名) Against(搜索内容)
举例: SELECT note_text FROM produce_notes WHERE Match(note_text) Against('rabbit')
- 拓展查询:
1. 在Against搜索内容后面增加 WITH QUERY EXPANSION,可查询相关联的其他语句
(先查询出rabbit所在行,再根据rabbit所在行的单词,查询其他语句)
SELECT note_text
FROM produce_notes
WHERE Match(note_text) Against('rabbit' WITH QUERY EXPANSION)
2. 在Against搜索内容后面增加 IN BOOLEAN MODE,可根据匹配次中的操作符等
语义匹配、返回对应数据,具体参照全文本布尔操作符表
SELECT note_text
FROM produce_notes
WHERE Match(note_text) Against('rabbit -rope*' IN BOOLEAN MODE)
注:文中内容表示,查询所有包含rabbit,但不返回以rope开始的单词所在的行,和
以rope开始的行
四、插入数据
数据插入
- 使用 INSERT LOW_PRIORITY INTO(同样可适用update、delete) ,通常insert操作是比较慢的,如果使用该语法,可以降低insert的优先级,先执行后面语句
- 插入数据,尽量使用单条Insert,插入多条数据,可以优化性能
- INSERT SELECT—— insert语句后面跟着Select可以将查询到的数据直接插入,但是注意字段位置一定要保证一致,字段名可以不一样
- ** select last_insert_id()可以获取最后一个插入的值
五、更新、删除数据
1. 更新
- 通常更新多条数据出错后,会回滚操作,在update后面加ignore,则不会回滚
2. 删除
- 如果删除所有数据,使用truncate table会更快(删除原来的表,再创建)
六、视图
1. 使用
- 创建:create view 视图名 as 紧跟查询语句
- 修改:视图不存储数据,每次调用时会调用view里面的查询,一般不改
- 删除:drop view 视图名
七、存储过程
1. 使用
- 创建:
Example One:
delimiter //
create procedure 过程名()
begin
select * from tb_user;
end //
delimiter ;
注意:一般sql图形化软件都会直接解析“;”为结束符,嵌套情况下回出现问题
本例中先将 / / 替换为结束符,end存储结束,
查询语句以“;”存储进数据库,再恢复结束符,可以解决该问题
Example Two:
create procedure productpricing(
OUT pl DECIMAL(8,2), // OUT 代表返回结果,DECIMAL是十进制
OUT ph DECIMAL(8,2), // 本例中分别返回最低、最高、平均价格
OUT pa DECIMAL(8,2),
)
BEGIN
SELECT Min(prod_price)
INTO pl // 通过 INTO 关键字将检索到的值赋值给变量
FROM products;
SELECT Max(rpod_price)
INTO ph
FROM products;
SELECT Avg(prod_price)
INTO pa
FROM products;
END;
IN : 传递给存储过程
OUT:从存储过程传出
INOUT:对存储过程传入和传出
DECLARE:定义游标
DECLARE total DECIMAL(8,2)
DECLARE taxrate INT DEFAULT 6;
- 调用 ———— CALL 过程名()
// 首先调用方法,获取变量
CALL productpricing(@pricelow,
@pricehigh,
@priceaverage
)
// 然后查询对应变量的值
select @priceaverage
- 删除 ———— DROP PROCEDURE 过程名
- 查询 ———— show create procedure ordertotal;
八、游标
1. 概述
游标用来动态的处理数据,前移一行或者后移一行数据(MYSQL的游标必须依赖于存储过程)
2. 使用
// 创建
CREATE PROCEDURE oricessorders()
BEGIN
DECLARE ordernumbers CURSOR FOR
SELECT order_num FROM orders;
// 使用/关闭游标(游标会在到达END时,自动关闭)
OPEN ordernumbers;
FETCH ordernumber;
CLOSE ordernumbers;
END;
九、触发器
1. 使用
1.1 创建
CREATE TRIGGER newproduct // 定义触发器名称
AFTER INSERT ON products // 定义product表插入后触发事件
FOR EACH ROW SELECT 'Product added';// 为每行执行打印
1.2 删除
DROP TRIGGER newproduct;
2. 注意事项
- 前置触发器报错后,无法执行sql和后置
- SQL报错,无法执行后置
- 一个表最多只能设置6个触发器