MySql必知必会整理的读书笔记

2017-06-23  本文已影响51人  capo

MySql主键规则

-拼接: 将值联接到一起构成单个值
select repairshop_name, CONCAT(repairshop_name,repairshop_code), repairshop_code from t_ops_insuredorder;
将两个字段的值连接在一起

关于MySql中的函数

如果你决定使用函数,应该保证做好代码注释,以便以后你（或其他人）能确切地知道编写SQL代码的含义
MySql中实现支持一下函数

SELECT created_at,repairshop_name from t_ops_insuredorder where DATE(created_at) = '2016-12-19'
针对日期处理查询条件函数

Mysql中的聚集函数

运行在组上,计算和返回单个值的函数
AVG() 返回某列的平均值
COUNT() 返回某列的行数

MAX() 返回某列的最大值
MIN() 返回某列的最小值
SUM() 返回某列值之和

MySql中的分组函数

分组允许把数据分为对个逻辑组,以便能对每个组进行计算

过滤分组

对分组进行过滤使用 HAVING

group by不能给分组的数据进行排序,所以必须是用 order by

SELECT order_num,SUM(quantity*item_price)
AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING SUM(quantity*item_price) >= 50

SELECT子句顺序

子句 说明 是否必须使用
SELECT 要返回的列或表达式 是
FROM 从中检索数据的表 仅在从表选择数据时使用
WHERE 行级过滤 否
GROUP BY 分组说明 仅在按组计算聚集时使用
HAVING 组级过滤 否
ORDER BY 输出排序顺序 否
LIMIT 要检索的行数 否

子查询

SELECT
    *
FROM
    vg_user_detail
WHERE
    user_id IN (
        SELECT
            COUNT(user_id) user_id
        FROM
            vg_user_info
        GROUP BY
            tenant_id
        ORDER BY
            user_id
    )

使用子查询时候必须保证SELECT语句具有与WHERE子句中相同数目的列

作为计算字段使用子查询

SELECT department_id,
(SELECT COUNT(user_id) 
FROM vg_user_info
WHERE vg_user_detail.user_id = vg_user_info.user_id
) userId 
from vg_user_detail
GROUP BY department_id
HAVING department_id > 0

注意 在子查询中from使用了完全限定列名避免歧义

联结表

两种写法: 关于等值联结

SELECT vi.user_id,vi.user_name,vd.staff_name
from vg_user_info vi,vg_user_detail vd
where vi.user_id = vd.user_id

SELECT vf.user_id,vf.user_name,vd.staff_name
from vg_user_info vf JOIN vg_user_detail vd
ON vf.user_id = vd.user_id
内联结
SELECT vf.user_id,vf.user_name,vd.staff_name
from vg_user_info vf INNER JOIN vg_user_detail vd
on vf.user_id = vd.user_id
外部联结

左联结

SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2 
ON table_name1.column_name=table_name2.column_name

LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行。
显示左边所有的行,右边没有的显示null

有联结

RIGHT JOIN
显示右边所有的行,左边没有的显示null

组合查询

适用场景

SELECT position_name,user_id
from vg_user_info vf where tenant_id = -1
UNION 
SELECT position_name,user_id
from vg_user_info where position_name = '人力资源经理'

注意事项:

union查询结果集中自动去掉重复的行
如果想取得所有的行则用 UNION ALL

**全文本索引只在 MyISAM数据引擎中使用

SELECT *
from vg_user_info vf 
WHERE MATCH(user_id) AGAINST ("abc")

match匹配列, AGSINST搜索含有abc的列

视图

**视图为虚拟的表. 它们包含的不是数据而是根据需要检索数据的查询。视图提供了一种MySql的SELECT 语句层次的封装，可用来简化数据的处理以及重新格式化数据或保护基础数据

存储过程

就是为了以后的使用而保存的一条或多条MySql语句的集合。可将其视为批文件,虽然他们的作用不仅限于批处理

CALL productpricing(@pricelow,@pricehigh,@priceaverage);

  CREATE PROCEDURE productpricing(
     OUT p1 DECIMAL(8,2),
     OUT ph DECIMAL(8,2),
     OUT pa DECIMAL(8,2)
 
  )
 BEGIN 
  SELECT Min(prod_price)
  INTO p1
  from products
  SELECT Mac(prod_price)
  from products
  SELECT Avg(prod_price)
  INTO pa
  from products;
 END

关键字 OUT 指出相应的参数用来从存储过程传出一个值(返回给调用者).
MySql支持 IN(传递给存储过程)、OUT(对存储过程传入和传出)类型的参数

为调用此存储过程必须使用3个变量

CALL producttpricing(
                    @pricelow,
                    @pricehigh
                    @priceaverage
 );

为了显示值

 select @priceaverage;

下面这个例子使用 IN和OUT参数。
ordertotal接受订单号并返回该订单的合计

 CREATE PROCEDURE ordertotal(
     IN onnumber INT,
     OUT otatal DECIMAL(8,2)
                            )
 BEGIN
     SELECT Sum(item_price*quantity)
     FROM orderitems
     WHERE order_num = onnumber
     INTO ototal;
     END;

分析:
onnumber定义为IN，因为订单号被传入存储过程。otatal定位为OUT,因为要从存储过程返回合计。SELECT 语句使用这两个参数,WHERE子句使用onumber选择正确的行,INTO使用ototal存储计算出来的合计

为调用这个新存储过程,可以使用一下语句
CALL ordertotal (20005,@total)
必须给ordertotal传递两个参数:第一个参数为订单号，第二个参数为包含计算出来合计的变量名。
为了显示次合计
select @total

为了得到一个订单的合计显示,需要再次调用存储过程,然后重新显示变量。

CALL ordertotal(20009,@total)
SELECT @total

游标

应用场景：
有时需要在检索出来的行中前进或后退一行或多行。
游标主要用于交互式应用,其中用户需要滚动屏幕上的数据,并对数据进行浏览或作出更改
游标只能用于存储过程(和函数)

关于游标的注意事项:

创建游标
CREATE PROCEDURE processorders()
BEGIN 
   DECLARE ordernumbers CURSOR
   FOR 
   SELECT odernum FROM orders;
   END;

打开或关闭游标
游标使用 OPEN CURSOR语句来打开

隐含关闭 如果不明确关闭游标,MySql将会在到达END语句时自动关闭它

例子:

CREATE procedure processorders()
BEGIN
   -- 定义一个游标
   DECLARE ordernumbers CURSOR
   FOR 
   SELECT irder_num FROM orders;
   
   --打开游标
   OPEN ordernumbers
   
   --检索游标
   FETCH ordernumbers INTO o;
   
   --关闭游标
   CLOSE ordernumbers;
   
   END;

触发器

应用场景: 如果你想要某条语句(或某些语句)在事件发生时自动执行
在每个表发生更改时自动处理

创建触发器需要提供的信息:

保持每个触发器的名称在每个表中的唯一

CREATE TRIGGER newproduct AFTER INSERT ON
products FOR EACH ROW SELECT 'Product added';

创建一个名为 newproduct的触发器 在INSERT语句成功执行后执行。这个触发器还指定 FOR EACH ROW 因此代码对每个差入行执行

触发器仅仅支持表 且每个表最多支持6个触发器

CREATE TRIGGER neworder AFTER INSERT ON
orders
FOR EACH ROW SELECT NEW.order_num;

生成一个neworder触发器,再插入一个新的订单并保存到order表时,MySql生成一个新的订单号并保存到order_num取得这个值并返回它

事务管理

例子:

  SELECT * from ordertotals;
  START TRANSACTION;
  DELETE FROM ordertotals;
  ROLLBACK;
  SELECT * FROM ordertotals;

使用保留点
SAVEPOINT delete1;

更改默认的提交行为
SET commint 0;

管理用户

CREATE USER ben IDENTIFIED BY 'p@$$wOrd';

重命名一个表

RENAME USER ben TO bforta;

删除一个表

DROP USER bforta

授予权限

GRANT SELECT ON crashcourse.* TO beforta;

表示: 用户bforta对crashcourse数据库中的所有数据具有只读访问权限

取消用户权限

REVOKE SELECT ON crashcourse.* FRO M beforta

两个命令在几个层次上的控制

备份数据

数据库维护

查看日志文件

改善性能

上一篇下一篇

猜你喜欢

热点阅读