MySQL必知必会笔记

最新推荐文章于 2024-05-16 11:09:42 发布

d.r.young

最新推荐文章于 2024-05-16 11:09:42 发布

阅读量510

点赞数 1

分类专栏： SQL 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/dearyangjie/article/details/77449002

版权

SQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

附录C 语法

符号 |, xx_a | xx_b：几个选择中的一个
符号 [], [like this]: 方括号中的关键字或子句是可选择的
..

第三章使用MySQL

连接
主机名；端口；用户名；用户口令。参见28章
数据库，表
USE xx_database;
SHOW tables;

SHOW COLUMNS FROM xx_table;
– or
DESCRIBE xx_table;

help SHOW;
SHOW CREATE DATABASE xx_database;
SHOW CREATE TABLE xx_table;
SHOW GRANTS;
SHOW STATUS;
SHOW ERRORS;
SHOW WARNINGS；

第四/五章检索/排序数据 SELECT, ORDER BY, LIMIT

SELECT xx,xx FROM xx_table;
SELECT 选择什么 FROM 从什么地方选
SELECT DISTINCT xx FROM xx_table
行号从0开始
SELECT xx FROM xx_table LIMIT x
SELECT xx FROM xx_table LIMIT x, y
– or
SELECT xx FROM xx_table LIMIT x OFFSET y
SELECT xx_table.xx FROM xx_database.xx_table
排序 ORDER BY
SELECT xx FROM xx_table ORDER BY xx;

– 选取最大 ORDER BY 与 LIMIT 组合
SELECT xx, xx, FROM xx_table ORDER BY xx DESC LIMIT 1

第六/七章过滤数据 WHERE

MySQL在执行匹配时默认不区分大小写，Abc = ABC = abc

空值查询
SELECT xx,.. FROM xx_table WHERE xx IS NULL;
过滤条件
等于=，不等于<>或!=,..
组合WHERE子句
AND； WHERE cnd1 AND cnd2；

OR；
NOTE: 优先处理(cond) -> AND -> OR

IN (x,x); 包含端点，可替代OR；
（还可以包括其他SELECT语句，能够动态的建立WHERE子句）

NOT；

各种组合，构建高级查询过滤

第八/九章通配符，正则表达式

通配符，wildcard：用来匹配值的一部分特殊字符，具有特殊含义的字符，通配！！
搜索模式，search pattern：由字面值、通配符或两者组合构成
LIKE 指示后面跟的搜索模式利用通配符而不是直接相等匹配进行比较

百分号 % （代表搜索模式中给定位置的0个，1个或多个字符）
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE ‘jet%’;
NOTE：不能匹配NULL值
下划线 _ （只匹配单个字符，而非多个字符）
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE ‘_et%’；
NOTES: 处理时间相对较长
（1）不要过度使用，尽量使用其他操作符
（2）把通配符至于搜索模式开始处，搜索起来最慢！！
（3）注意通配符的位置！

正则表达式：用来匹配文本的特殊的串（字符集合）
例：从文本文件中提取电话号码；查找名字中间有数字的所有文件；在一个文本块中找到所有重复的单词；
替换一个页面中的所有URL为这写URL的实际HTML链接；

正则表达式用正则表达式语言来建立，尤其特殊的语法和指令；
正则表达式的作用是匹配文本，将一个模式（正则表达式）与一个文本串进行比较；

检索列prod_name包含文本1000的所有行：
SELECT prod_name FROM products WHERE prod_name REGEXP ‘1000’;

等价于

SELECT prod_name FROM products WHERE prod_name LIKE ‘%1000%’;
检索列prod_name包含文本1000或2000的所有行：
SELECT prod_name FROM products WHERE prod_name REGEXP ‘1000|2000’;
匹配任一单一字符，[字符集合]（另一种形式的OR语句）
SELECT prod_name FROM products WHERE prod_name REGEXP ‘[123] Ton’;
–or
SELECT prod_name FROM products WHERE prod_name REGEXP ‘[1|2|3] Ton’;
匹配范围，[0123456789] or [0-9]; [a-z]
SELECT prod_name FROM products WHERE prod_name REGEXP ‘[1-5] Ton’;
SELECT prod_name FROM products WHERE prod_name REGEXP BINARY ‘[1-5] ton’ # 添加BINARY指定大小写匹配
匹配特殊字符，用\为前导，\-表示查找-，\.表示查找., \\表示查找,（即转义字符）
找到包含.字符的值：
SELECT vend_name FROM vendors WHERE vend_name REGEXP ‘\.’;
匹配字符类
预定义字符集，称为字符类character class

[:alnum:] – 任意字母和数字[a-zA-Z0-9]
[:alpha:] – 任意字符[a-zA-Z]
[:blank:] – 空格和制表[\t]
[:cntrl:] – ASCII控制字符（ASCII 0到31，和127）
[:digit:] – 任意数字[0-9]
[:graph:] – 任意可打印字符（与[:print:]相同），但不包括空格
[:lower:] – 任意小写字母[a-z]
[:print:] – 任意可打印字符
[:punct:] – 不在[:alnum:]和[:cntrl:]中的任意字符，即非字母、数字、控制字符
[:space:] – 包括空格在内的任意空白字符，[\f\n\r\t\v]
[:upper:] – 任意大写字母，[A-Z]
[:xdigit:] – 任意十六进制数字，[a-fA-F0-9]
匹配多个实例
重复元字符：
- – 0个或多个匹配
- – 1个或多个匹配（等于{1,}）
  ？ – 0个或1个匹配（等于{0, 1}
  {n} – 指定数目的匹配
  {n,} – 不少于指定数目的匹配
  {n, m} – 匹配数目的范围（m不超过255）
定位符
NOTE：以上1-7都是匹配一个串中任意位置的文本；

定位元字符：
^ – 文本的开始；在集合中如[^0-9]，^表示否定，即非0-9数字
$ – 文本的结尾
[[:<:]] – 词的开始
[[:>:]] – 词的结尾
例如：找出以一个数（包括以小数点开始的数）开始的所有产品
SELECT prod_name FROM products WHERE prod_name REGEXP ‘^[0-9\.]’

REGEXP 与 LIKE 的区别：
（1）REGEXP 匹配子串，可通过定位符定位；LIKE匹配整个串
（2）通过定位符^开始每个表达式，用$结束每个表达式，REGEXP的功能与LIKE一样

测试正则表达式：SELECT ‘hello’ REGEXP ‘[0-9]’

第十章创建计算字段

存储在表中的数据一般不是应用程序所要求的格式；
我们可以直接从数据库中检索出转换、计算或格式化过的数据，以减少客户机应用程序或报告程序中再格式化.

计算字段是运行时在SELECT语句内创建的.

拼接字段
如两个column的拼接
函数Concat(x,x,..),拼接多个列；也有DBMS使用+或||来实现拼接

SELECT Concat(vend_name, ‘(‘, vend_country, ‘)’) FROM vendors;
删除数据多余空格
函数RTrim(x)右侧； LTrim(x)左侧
SELECT Concat(RTrim(vend_name),’(‘,vend_country,’)’) FROM vendors;
使用别名
为创建的计算字段命名： Concat(x,x) AS name
SELECT Concat(vend_name,’-‘,vend_country) AS vend_title FROM vendors;
执行算术计算
SELECT quantity*item_price AS expanded_price FROM orderitems WHERE order_num = 20005;

算术操作符：+，-，*，/

测试算术操作：SELECT 3*2;
SELECT Trim(‘abc’);
SELECT Now() AS ‘Time’; # 用AS起name

第十一章使用数据处理函数

函数功能：
（1）用于处理文本串的文本函数。（删除、填充、大小写转换等）
（2）用于在数值数据上进行算术操作。（返回绝对值、进行代数运算等）
（3）用于处理日期和时间值，并从这些值中提取特定成分的日期和时间函数。（两个日期差、日期的有效性）
（4）返回DBMS正在使用的特殊信息的系统函数。（返回用户登录信息、检查版本细节）

tips：
用help xx获取详细参考；
用select xx()进行测试；

文本处理函数
Left(x,len) – 返回串左边的字符（长度为len）
Right(x,len)
Length(x) – 返回串的长度
Locate(x,sub_x) – 找出串的一个子串
SubString(x, from, to) – 返回字串的字符
Lower(x)
Upper(x)
LTrim(x)
RTrim(x)
Soundex(x) – 读音（用于发音匹配）

SELECT cust_name, cust_contact FROM customers WHERE Soundex(cust_contact) = Soundex(‘Y Lie’);
日期和时间处理函数
日期和时间采用相应的数据类型和特殊的格式存储，以便可以快速和有效的排序或过滤，节省物理存储空间.
一般，应用程序不使用用来存储日期和时间的格式，因此日期和时间函数总是被用来读取、统计和处理这些函数.

常用日期和时间处理函数：
AddDate() – 增加一个日期（天，周等）
AddTime() – 增加一个时间（时，分等）
CurDate() – 返回当前日期
CurTime() – 返回当前时间
Date() – 返回日期时间的日期部分
DateDiff() – 计算两个日期之差
Date_Add() – 日期运算函数
Date_Format() – 返回一个格式化的日期或时间串
Day() – 返回一个日期的天数部分
DayOfWeek() – 返回日期对应的星期几
Hour() – 返回一个时间的小时部分
Minute() – 返回一个时间的分钟部分
Second() – 返回一个时间的秒部分
Month() – 返回一个日期的月部分
Now() – 返回当前日期和时间
Time() – 返回一个日期时间的时间部分
Year() – 返回一个日期的年份部分

日期首选格式： yyyy-mm-dd; 如2005-09-01

检索某日期下的数据：
SELECT cust_id, order_num FROM orders WHERE Date(order_date) = ‘2005-09-01’;

检索某月或日期范围内的数据：
SELECT cust_id, order_num FROM orders WHERE Year(order_date) = 2005 AND Month(order_date) = 9;
– or
SELECT cust_id, order_num FROM orders WHERE date(order_date) BETWEEN ‘2005-09-01’ AND ‘2005-09-30’;
数值处理函数
代数、三角函数、几何运算等

常用数值处理函数：
abs(); cos(); exp(); mod()（取余）; Pi(); Rand(); Sin(); Sqrt(); Tan();

第十二章汇总数据

聚集函数：汇总数据. 返回实际表数据是对时间和处理资源的一种浪费。获取汇总信息！！
（1）确定表中行数（或者满足某个条件或包含某个特定值得行数）
（2）获得表中行组的和
（3）找出表列（或所有行或某些特定的行）的最大值，最小值和平均值

聚集函数：
AVG()       --      返回某列的平均值，忽略NULL
COUNT()     --      返回某列的行数，忽略NULL；COUNT(*)包括NULL
MAX()       --      返回某列的最大值，最大日期，忽略NULL
MIN()       --      返回某列的最小值，最小日期，忽略NULL
SUM()       --      返回某列值之和，忽略NULL

可与WHERE条件句组合使用，获取列种满足条件的行的聚合计数；

以上5个聚集函数都可以：
1）对所有的行执行计算，指定ALL参数或不给参数（ALL为默认）
2）只包含不同的值，指定DISTINCT参数
e.g.
SELECT AVG(DISTINCT prod_price) AS avg_price FROM products WHERE vend_id = 1003;

组合聚集函数，如
SELECT COUNT(*) AS num_items,
    MIN(prod_price) AS price_min,
    MAX(prod_price) AS price_max,
    AVG(prod_price) AS price_avg
FROM products;

第十三章分组数据 GROUP BY; HAVING

到目前为止，所有的计算都是在表的所有数据或匹配特定的WHERE子句的数据上进行的。
例如：
SELECT COUNT(*) AS num_prods FROM products WHERE vend_id = 1003;

创建分组

过滤分组 HAVING （与WHERE句法相同）
区别：WHERE针对行，HAVING针对各个分组
      WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤；
      WHERE排除的行不包括在分组中，从而影响HAVING子句中基于这些值过滤掉的分组；

分组和排序

第十四章使用子查询

子查询：嵌套在其他查询中的查询；嵌套SELECT
子查询测试和调试：
step 1: 建立和测试最内层查询
step 2: 用硬编码数据建立和测试外层查询，并且仅在确认其正常后才嵌入子查询
重复

第十五章联结表

在数据检索查询的执行中联结join表；

关系表：
关系表的设计就是要保证把信息分解成多个表，一类数据一个表；
各表通过某些常用的值（即关系设计中的关系互相关联）

外键：
外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系
可伸缩性：能够适应不断增加的工作量而不失败

联结：
联结是一种机制，用来在一条SELECT语句中关联表，因此也称为联结；
联结在运行时关联表中正确的行；

创建联结基本规则：
首先列出所有表，然后定义表之间的关系；

使用WHERE子句创建联结：
SELECT vend_name, prod_name, prod_price FROM vendors WHERE vendors.vend_id = products.vend_id;
使用INNER JOIN .. ON ..:
SELECT vend_name, prod_name, prod_price FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id;

第十六章高级联结

    自联结：
    替代从相同表中检索数据时使用的子查询语句，一般较子查询快
    SELECT p1.prod_id, p1.prod_name FROM products AS p1, products AS p2 WHERE p1.vend_id=p2.vend_id
    AND p2.prod_id='DTNTR';
    （查询生产产品'DTNTR'的供应商生产的所有产品）

    自然联结：
    无论何时对表进行联结，应该至少有一列出现在不止一个表中（被联结的列）；
    标准的联结返回所有数据，甚至相同的列多次出现；

    外部联结：
    xx_left RIGHT OUTER JOIN xx_right ON xx_left.key=xx_right.key: 保留xx_right.key列的所有行；
    xx_left LEFT OUTER JOIN xx_right ON xx_left.key=xx_right.key: 保留xx_left.key列的所有行；

    而INNER JOIN .. ON: 求交集


    带聚集函数的联结：
    - 先联结；再分组；再查询.

    使用联结tips：进行测试

第十七章组合查询

组合查询：利用UNION操作符将多条SELECT语句组合成一个结果集
两种基本情况：
（1）在单个查询中从不同的表返回类似结构的数据；
（2）对单个表执行多个查询，按单个查询返回数据；

UNION几乎总是完成与多个WHERE条件相同的工作；

UNION规则：
- UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔；
- UNION中的每个查询必须包含相同的列、表达式或聚集函数（但各个列不需要以相同的次序列出）
- 列数据类型必须兼容

包含或取消重复的行：
UNION默认去掉重复的行；UNION ALL 可以包含重复的行；

排序： 只能包含一条ORDER BY语句，置于最后一条SELECT语句后；

第十八章全文本搜索

数据库引擎：MYISAM；Innodb；数据结构：B+tree, B-tree, hash tables, heaps
参考：http://www.2cto.com/database/201503/385669.html
Database engine（wiki）
A database engine (or storage engine) is the underlying software component that a database management system (DBMS) uses to create, read, update and delete (CRUD) data from a database.
Most DBMS include their own API that allows the user to interact with their underlying engine without going through the user interface of the DBMS.

Innodb: InnoDB became a product of Oracle Corporation after its acquisition of Innobase Oy in October 2005.
MySIAM: ISAM is acronym of Indexed Sequential Access Method

进行全文本搜索：（需要定义FULLTEXT索引）
Match(): 指定被搜索的列
Against(): 指定要使用的搜索表达式
扩展查询 Against(xx WITH QUERY EXPANSION)

布尔文本搜索：
- 要匹配的词；
- 要排斥的词（如果某行包含这个词，则不反回改行，即使它包含其他指定的词也是如此）
- 排列提示（指定某些词比其他词更重要，更重要的词等级更高）
- 表达式分组；

Match(xx_col) Against('xx boolen' IN BOOlEAN MODE)
布尔操作符
+       --      包含，词必须存在
-       --      排除，词必须不出现
>       --      包含，而且增加等级值
<       --      包含，且减少等级值
()      --      把词组成子表达式（允许这些子表达式作为一个组被包含、排除、排列等）
~      --      取消一个词的排序值
*       --      词尾的通配符
""      --      定义一个短语（与单个词的列表不一样，它匹配整个短语以便包含或排除这个短语）

第十九章插入数据

INSERT，使用方式：
- 插入完整的行
- 插入行的一部分
- 插入多行
- 插入某些查询的结果

插入完整的行；
INSERT INTO xx_table VALUES(xx,xx,..);  # 一般不这样使用
INSERT INTO xx_table(col1, col2,...) VALUES(col1_xx, col2_xx, ...); -- 指定列顺序
省略列：
a. 该列定义为允许NULL值（无值或空值）
b. 在表定义中给出默认值。这表示如果不给出值，将使用默认值

如果检索是最重要的则可以：
INSERT LOW_PRIORITY INTO ..

插入多个行：
-- 多个INSERT语句，每条语句用一个分号结束；
-- 或者单个INSERT语句有多组值：INSERT INTO xx_table(col1, col2,...) VALUES(col1_xx, col2_xx, ...),(col1_xx, col2_xx, ...);
    （可以提高性能）

插入检索出的数据：
INSERT .. SELECT ..
INSERT xx_table(col1, col2, ..) SELECT col1, col2, .. FROM yy_table;
(将yy_table中检索出来的列插入到xx_table中,不要求列名匹配，采用位置匹配；
因此，SELECT中的第一列（不管其列名）将用来填充表列中指定的第一列，第二列将用来填充表列中指定的第二列..)

第二十章更新和删除数据

更新数据UPDATE：
- 更新表中特定的行；
- 更新表中所有行；
note：不要省略WHERE子句，一不注意，就会更新表中所有行！！！

UPDATE语句：
UPDATE 待更新的表 SET 列名=新值,.. WHERE 需要更新的行及过滤条件

设置某列为NULL：
UPDATE customers SET cust_email = NULL WHERE cust_id = 10003;

删除数据：
- 从表中删除特定的行；
- 从表中删除所有行；
note：不要省略WHERE子句，一不注意，就会更新表中所有行！！！

DELETE FROM xx_table WHERE cust_id = 10003(满足条件的行)；

TRUNCATE TABLE xx_table; # 删除所有行（实际是删除原来的表，并重新穿件一个表）

tips：在更新和删除操作前，对WHERE条件子句，使用SELECT语句进行测试，以保证正确！！

MySQL没有撤销按钮，需要小心谨慎处理！！

第二十一章创建和操纵表

创表方法：
- 使用具有交互式创建和管理工具（内部采用了MySQL语句）
- 直接用MySQL语句操纵

CREATE TABLE customers
(
    cust_id         int         NOT NULL AUTO_INCREMENT,  # 自动增量
    cust_name       char(50)    NOT NULL,
    cust_address    char(50)    NULL,
    cust_city       char(50)    NULL,
    cust_state      char(5)     NULL DEFAULT 'USA',  # 给定默认值
    cust_zip        char(10)    NULL,
    cust_email      char(255)   NULL,
    PRIMARY KEY (cust_id)     # 指定主键
) ENGINE=InnoDB;

主键
主键值必须唯一；
表中每个行必须具有唯一的主键值；
如果主键使用单个列，则它的值必须唯一；    PRIMARY KEY (xx)
如果使用多个列，则这些列的组合值必须唯一；PRIMARY KEY (xx,yy)

主键中只能使用不允许NULL值得列，允许NULL值的列不能作为唯一标识；

使用AUTO_INCREMENT:
AUTO_INCREMENT 告诉MySQL，本列每当增加一行时自动增量；
每次执行一个INSERT操作时，MySQL自动对该列增量，给该列赋予下一个可用的值；
每个表只允许一个AUTO_INCREMENT列，而且它必须被索引（如，通过使它成为主键）

事务处理：
事务处理用于有效记录某机构感兴趣的业务活动（称为事务）的数据处理（例如销售、供货的定购或货币传输）
事务处理（TRANSACTION）是由一个或多个SQL语句序列结合在一起所形成的一个逻辑处理单元。
事务处理中的每个语句都是完成整个任务的一部分工作，所有的语句组织在一起能够完成某一特定的任务。


更新表ALTER TABLE（增加列，删除列，添加外键等）
ALTER TABLE vendors ADD vend_phone CHAR(20); # 给表增加一列
ALTER TABLE vendors DROP COLUMN vend_phone; # 删除一列

定义外键：
ALTER TABLE orders
ADD CONSTANT fk_orders_customers FOREIGN KEY (cust_id)
REFERENCES customers (cust_id)

删除表：
DROP TABLE customers2;

重命名表：
RENAME TABLE customers2 TO customers1;

第二十二章使用视图

视图
视图是虚拟的表；
视图只包含使用时动态检索数据的查询
（我的理解：中间变量）

第二十三章使用存储过程

存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合；可将其视为批文件；
存储过程实际上是一种函数；

使用存储过程
（1）执行存储过程
即调用，CALL：CALL接受存储过程的名字以及需要传递给它的任意参数
CALL productpricing(@pricelow, @pricehigh, @priceaverage);
（执行名为productpricing的存储过程，其计算并返回产品的最低、最高、和平均价格）

（2）创建存储过程
CREATE PROCEDURE productpricing()
BEGIN
    SELECT AVG(prod_price) AS priceaverage
    FROM products;
END;

（3）删除存储过程
DROP PROCEDURE productpricing;

（4）使用参数 
一般，存储过程并不显示结果，而是把结果返回给指定的变量；

变量variable：内存中一个特定的位置，用来临时存储数据；

创建：
CREATE PROCEDURE productpricing(
            OUT pl DECIMAL(8,2),
            OUT ph DECIMAL(8,2),
            OUT pa DECIMAL(8,2)
            )
BEGIN
    SELECT MIN(prod_price) INTO pl FROM products;
    SELECT MAX(prod_price) INTO ph FROM products;
    SELECT AVG(prod_price) INTO pa FROM products;
END;

调用：
CALL productpricing(@pricelow, @pricehigh, @priceaverage);

显示：
SELECT @priceaverage;
SELECT @pricelow, @pricehigh, @priceaverage;


建立智能存储过程：
订单合计：指定订单onnumber的合计，由otatal返回；
CREATE PROCEDURE ordertotal(
    IN onumber INT,
    OUT ototal DECIMAL(8,2)
    )
BEGIN
    SELECT SUM(item_price*quantity)
    FROM orderitems WHERE order_num = onnumber
    INTO ototal;
END;

CALL ordertotal(20005, @total);  # 调用
SELECT @total;  # 显示


若得到订单合计后对合计增加营业税，且只针对你所在州的那些顾客？
- 获得合计；
- 把营业税有条件地添加到合计；
- 返回合计（带或不带税）

完整的存储过程：
-- Name: ordertotal
-- Parameters: onumber = order number
--              taxable = 0 if not taxable, 1 if taxable
--              ototal = order total varible

CREATE PROCEDURE ordertotal(
    IN onumber INT,
    IN taxable BOOLEAN,
    OUT ototal DECIMAL(8,2)
) COMMENT 'Obtain order total, optionally adding tax'
BEGIN
    -- Declare varible for total
    DECLARE total DECIMAL(8,2);
    -- Declare tax percentage
    DECLARE taxrate INT DEFAULT 6;

    -- Get the order total
    SELECT SUM(item_price*quantity) FROM orderitems WHERE order_num = onnumber INTO total;

    -- Is this taxable?
    IF taxable THEN
        -- Yes, so add taxrate to the total
        SELECT total+(total/100*taxrate) INTO total;
    END IF;

    -- And finally, save to out varible
    SELECT total INTO ototal;

END;

第二十四章使用游标

游标: 是一个存储在MySQL服务器上的数据库查询，它是被SELECT语句检索出来的结果集；
    在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据；
    可以在检索出来的行中前进或后退一行或多行；
    游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或做出更改；

使用游标：
- 声明定义游标
- 

创建游标
DECLARE 

CREATE PROCEDURE processorders()
BEGIN
    -- DECLARE语句用来定义和命名游标
    DECLARE ordernumbers CURSOR  
    FOR 
    SELECT order_num FROM orders;
END;

打开和关闭游标
OPEN CURSOR

OPEN ordernumbers;

CLOSE ordernumbers;

e.g.声明、打开和关闭一个游标：
CREATE PROCEDURE processorders()
BEGIN
    -- Declare the cursor
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;

    -- Open the cursor
    OPEN ordernumbers;

    -- Close the cursor
    CLOSE ordernumbers;

END;

使用游标数据：
FETCH

下面存储过程中，FETCH用来检索当前行的order_num列（将自动从第一行开始）到一个名为o的局部声明变量中，
对检索出的数据不做任何处理：
CREATE PROCEDURE processorders()
BEGIN
    -- Declare local varibles
    DECLARE o INT;

    -- Declare the cursor
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;

    -- Open the cursor
    OPEN ordernumbers;

    -- Get order number
    FETCH ordernumbers INTO o;

    -- Close the cursor
    CLOSE ordernumbers;

END;

循环检索数据，从第一行到最后一行：
CREATE PROCEDURE processorders()
BEGIN
    -- Declare local varibles
    DECLARE done BOOLEAN DEFAULT 0;
    DECLARE o INT;

    -- Declare the cursor
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;

    -- Declare continue handler
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

    -- Open the cursor
    OPEN ordernumbers;

    -- Loop through all rows
    REPEAT

       -- Get order number
       FETCH ordernumbers INTO o;

    -- End of loop
    UNTIL done END REPEAT;

    -- Close the cursor
    CLOSE ordernumbers;

END;
其中，DECLEAR语句存在一定的顺序；用DECLARE语句定义的局部变量必须在定义任意游标或句柄之前；
而句柄必须在游标之后定义；

CONTINUE HANDLER，是在条件出现时被执行的代码，本例中当SQLSTATE '02000'出现时，SET done=1.
该条件是一个未找到条件，当REPEAT由于没有更多的行供循环而不能继续时，出现这个条件。

你可以在循环内放入任意需要的处理！

CREATE PROCEDURE processorders()
BEGIN
    -- Declare local varibles
    DECLARE done BOOLEAN DEFAULT 0;
    DECLARE o INT;
    DECLARE t DECIMAL(8,2);

    -- Declare the cursor
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;

    -- Declare continue handler
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

    -- Create a table to store the results
    CREATE TABLE IF NOT EXISTS ordertotals(
                                    order_num  INT,
                                    total DECIMAL(8,2)
                                    );
    -- Open the cursor 
    OPEN ordernumbers;

    -- Loop through all rows
    REPEAT
        -- Get order number
        FETCH ordernumbers INTO o;

        -- Get the total for this order
        CALL ordertotal(o, 1, t);

        -- Insert order and total into ordertotals
        INSERT INTO ordertotals(order_num, total) VALUES(o, t);

    -- End of loop
    UNTIL done END REPEAT;

    -- Close the cursor
    CLOSE ordernumbers;

END;

（存储过程、游标、逐行处理以及存储过程调用其它存储过程的完整的工作样例）

第二十五章使用触发器

让某些语句在事件发生时自动执行；
触发器是MySQL响应以下任意语句而自动执行的一条MySQL语句或位于BEGIN和END语句之间的一组语句：
- DELETE
- INSERT
- UPDATE
每个表最多支持6个触发器（每条INSERT、UPDATE、DELETE的之前和之后）

创建触发器：
CREATE TRIGGER newproduct AFTER INSERT ON products FOR EACH ROW SELECT 'Product added';

使用触发器：
INSERT触发器

DELETE触发器

CREATE TABLE archive_orders
(
    order_num    INT NOT NULL AUTO_INCREMENT,
    order_date   datetime NOT NULL,
    cust_id      INT NOT NULL,
    PRIMARY KEY (order_num)     # 指定主键
) ENGINE=InnoDB;

CREATE TRIGGER deleteorder BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
    INSERT INTO archive_orders(order_num, order_date, cust_id)
    VALUES(OLD.order_num, OLD.order_date, OLD.cust_id);
END;

第二十六章管理事务处理

    事务处理是一种机制，用来管理必须成批执行的MySQL操作，以保证数据库不包含不完整的操作结果；
    利用事务处理，可以保证一组操作不会中途停止，它们或者作为整体执行，或者完全不执行；
    如果没有错误发生，整组语句提交给数据库表；如果发生错误，则进行回退以恢复数据库到某个以知切安全的状态；

- 事务（transaction）指一组SQL语句；
- 回退（rollback）指撤销指定SQL语句的过程；
- 提交（commit）指将未存储的SQl语句结果写入数据库表；
- 保留点（savepoint）指事务处理设置中设置的临时占位符，可以对其发布回退

控制事务处理
管理事务处理的关键在于将SQL语句组分解为逻辑块，并明确规定数据何时应该回退，何时不应该回退；
START TRANSACTION

使用ROLLBACK

第二十七章全球化和本地化

字符集和校对顺序
- 字符集为字母和符号的集合；
- 编码为某个字符集成员的内部表示；
- 校对为规定字符如何比较的指令；

第二十八章安全管理

访问控制和用户管理

第二十九章数据库维护

备份数据

第三十章改善性能

数据库管理员把他们生命中的相当一部分时间花在了调整、试验以改善DBMS性能之上；
性能不良的数据库（以及数据库查询）通常是最常见的祸因 for 滞缓和性能问题；

- 硬件/专用服务器
- ..
- 总有不止一种方法编写同一条SELECT语句；应该试验 联结 并 子查询 等方式，找出最佳的方法；
- 使用EXPLAIN语句让MySQL解释它如何执行一条SELECT语句；
- 存储过程执行得比一条一条地执行其中的各条MySQL语句快
- 使用正确的数据类型
- 不要检索比需求还要多的数据，不要使用SELECT * （除非你真正需要每个列！）
- 有的操作（如INSERT）支持一个可选的DELAYED关键字，如果使用它，将把控制立即返回给调用程序，
    并且一旦有可能就实际执行该操作
- 在导入数据时，应该关闭自动提交；
- 必须索引数据库以改善数据检索的性能；如果一个简单 的WHERE字句返回结果所花的时间太长，
    则可以断定其中使用的列（或几个列）就是需要索引的对象
- 你的SELECT语句中有一系列复杂的OR条件吗？通过使用多条SELECT语句和连接他们的UNION语句，性能则能改善
- 索引改善数据检索的性能，但损害数据插入、删除和更新的性能；索引可根据需要添加和删除
- 数据库是不断变化的实体。一组优化良好的表一会儿后可能就面目全非了。由于表的使用和内容的更改，理想的优化和配置也会改变
------
- 最重要的规则是，每条规则在某些条件下都会被打破!

d.r.young

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
MySQL必知必会笔记

MySQL 语法符号 |, xx_a | xx_b：几个选择中的一个符号 [], [like this]: 方括号中的关键字或子句是可选择的..第三章使用MySQL连接主机名；端口；用户名；用户口令。参见28章数据库，表 USE xx_database; SHOW tables;SHOW COLUMNS FROM xx_table; – or DESCRIBE xx_tabl
复制链接

扫一扫