mysql 书籍推荐 简书_MySql必知必会整理的读书笔记

数据库就是以一个以某种有组织的方式存储的数据集合

MySql主键规则

不更新主键的值

不重用主键列的值

不在主键列中使用可能会更改的值

SHOW TABLES; 获得一个数据库内的表的列表

如果使用 DISTINCT 关键字,它必须直接放在列名的前面

LIMIT 5 表示MySql返回不多于5行

LIMIT 5,5 指示MySql返回从行5开始的5行

第一个数为开始位置,第二个数为要检索的行数

为了按多个列排序,只要指定列名,列名之间用逗号分开即可。

在多个列上进行降序 如果想在多个列上进行降序,必须对每个列指定DESC关键字

OR 操作符指示MySql 检索匹配任一条件的行

SQL像多数语言一样在处理OR操作符前,优先处理AND操作符

在where子句中使用圆括号任何时候使用具有AND和OR操作符的WHERE子句,都应该使用圆括号明确的分组操作符。不要过分依赖默认计算次序,即使它确实是你想要的东西也是如此。

in的操作符一般比OR操作符更快

in 的最大优点是可以包含其他的SELECT语句,使得能够更动态的建立WHERE字句

IN WHERE 字句用来指定要匹配值的清单的关键字,功能与OR相当

模糊查询 %anvil%表示匹配任何位置包含文本anvil的值,而不论它之前或之后出现什么字符。

_ 下划线只匹配单个字符而不是多个字符

通配符搜索的处理一般要比前面讨论的其他搜索所花的时间更长

在确实需要使用通配符时,除非绝对有必要,否则不要把它们用在搜索模式的开始处。把通配符置于搜索模式的开始处,搜索起来最慢

REGEXP 正则表达式

SELECT from products WHERE prod_name REGEXP '.000' ORDER BY prod_name

匹配任意一个含义000的字符

LIKE 与 REGEXP区别

LIKE匹配整个列.如果被匹配的文本在列值中出现,LIKE将不会找到它,相应的行也不会被返回(除非使用通配符)。而REGEXP在列值内进行匹配,如果被匹配的文本在列值中出现，REGEXP将会找到它,相应的行将被返回

MySql正则表达式不区分大小写(3.23.4),为了区分大小写可使用BINARY关键字

正则表达式进行 OR匹配

select prod_name from where prod_name REGEXP '1000|2000' ORDER BY prod_name

表示匹配其中之一,因此1000和2000都匹配返回

[123] 表示匹配 1或者2或者3,这是or的升级版

[^123]匹配除含有 1或者2或者3的

-拼接: 将值联接到一起构成单个值

select repairshop_name, CONCAT(repairshop_name,repairshop_code), repairshop_code from t_ops_insuredorder;

将两个字段的值连接在一起

select也可以用来进行计算

SELECT 3*2 将返回6

关于MySql中的函数

如果你决定使用函数,应该保证做好代码注释,以便以后你(或其他人)能确切地知道编写SQL代码的含义

MySql中实现支持一下函数

使用处理文本串(如删除或填充值,转换值为大写或者小写)的文本函数

用于在数值数据上进行算术操作(如返回绝对值,进行代数)的数值函数

用于处理日期和时间值并从这些值中提取特定成分(例如,返回两个日期之差,检查日期有效性)的日期时间函数

DATE_FORMAt(date,format)时间按格式转化函数

SELECT created_at,repairshop_name from t_ops_insuredorder where DATE(created_at) = '2016-12-19'

针对日期处理查询条件函数

返回DBMS正使用的特殊信息(如返回用户登录信息,检查版本细节)的系统函数

Mysql中的聚集函数

运行在组上,计算和返回单个值的函数

AVG() 返回某列的平均值

COUNT() 返回某列的行数

使用 count(*)对表中行的数目进行计数,不管表中包含的是空值(null)还是非空值

使用COUNT(column)对特定列中具有值的行进行计算,忽略NULL值

MAX() 返回某列的最大值

MIN() 返回某列的最小值

SUM() 返回某列值之和

MySql中的分组函数

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

GROUP BY 子句可以包含任意数目的列。这使得能对分组进行嵌套,为数据分组提供更详细的控制

gruop by子句中列出的每个列必须是检索列或有效的表达式(但是不能是聚集函数),如果在SELECT中使用表达式,则必须在GROUP BY子句中给出

如果分组中有NULL值,则 NULL将作为一个分组返回。如果列中有多行NULL值,则将他们分为一组

gruop by 必须出现在 where子句后,order by子句之前

过滤分组

对分组进行过滤使用 HAVING

HAVING支持所有的WHERE操作符

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使用了完全限定列名避免歧义

联结表

两种写法: 关于等值联结

1 .

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

2 .

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查询结果集中自动去掉重复的行

如果想取得所有的行则用 UNION ALL

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

SELECT *

from vg_user_info vf

WHERE MATCH(user_id) AGAINST ("abc")

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

视图

创建视图 view

CREATE VIEW 视图名 AS 子查询虚表

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

存储过程

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

执行存储过程

MySql 执行存储过程称为调用,使用 CALL命令

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

创建存储过程

CREATE PROCEDURE productpricing() BEGIN SELECT Avg(prod_price) AS price average FROM products; END;

删除存储过程

DROP PROCEDURE productpricing;

变量

内存中一个特定的位置,用来存储临时数据。

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

游标

应用场景：

有时需要在检索出来的行中前进或后退一行或多行。

游标主要用于交互式应用,其中用户需要滚动屏幕上的数据,并对数据进行浏览或作出更改

游标只能用于存储过程(和函数)

关于游标的注意事项:

在使用游标前,必须先声明(定义)它。

这个过程实际上没有检索数据,它只是定义要使用的SELECT 语句

一旦声明后,必须打开游标以供使用。这个过程用前面定义的SELECT 语句把数据检索出来

对于填有数据的游标,根据需要去除(检索)各行

在结束游标使用时,必须关闭游标

创建游标

CREATE PROCEDURE processorders()

BEGIN

DECLARE ordernumbers CURSOR

FOR

SELECT odernum FROM orders;

END;

打开或关闭游标

游标使用 OPEN CURSOR语句来打开

打开:

OPEN ordernumbers;

关闭

CLOSE ordernumbers;

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

例子:

CREATE procedure processorders()

BEGIN

-- 定义一个游标

DECLARE ordernumbers CURSOR

FOR

SELECT irder_num FROM orders;

--打开游标

OPEN ordernumbers

--检索游标

FETCH ordernumbers INTO o;

--关闭游标

CLOSE ordernumbers;

END;

触发器

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

在每个表发生更改时自动处理

DELETE

UPDATE

INSERT

操作可使用触发器

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

唯一的触发器名;

触发器关联的表

触发器应该响应的活动(DELETE、INSERT或UPDATE);

触发器何时执行

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

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;

管理用户

MySql中有一张 user表 用户管理所有的用户

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

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

整个服务器,使用GRANT ALL和 REVOKE ALL;

整个数据库,使用ON database;

特定的表,使用ON database.table

特定的列, 特定的存储过程。

备份数据

数据库维护

ANALYZE TABLE orders;

CHECK TABLE 快速发现和修复问题

查看日志文件

错误日志: data目录下 hostname.err

查询日志：hostname.log

二进制日志：hostname-bin

缓慢查询日志: hostname-slow.log

改善性能

查看当前设置 SHOW VARIABLES

SHOW STATUS

查看当前所有线程ID和活动时间 SHOW PROCESS LIST

你的SELECT语句中有一系列复杂的OR条件吗？通过使用多条SELECT 语句和连接它们的UNION语句

LIKE很慢,一般来说,最好使用FULLTEXT(全文索引)