- 数据库就是以一个以某种有组织的方式存储的数据集合
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(全文索引)
网友评论