文档章节

《MySQL必知必会》读书笔记(四) 22~30章 完结篇

一万
 一万
发布于 2016/02/08 21:22
字数 2937
阅读 339
收藏 28

1、视图

视图不包含表中应该有的任何列或数据,它包含的是一个SQL查询。在视图创建之后,可以用于表基本相同的方式利用它们。可以对视图执行SELECT操作,过滤和排序数据,将视图联结到其他视图或表,甚至能添加和更新数据。

视图仅仅是用来查看存储在别处的数据的一种设施。视图本身不包含数据,因此它们返回的数据是从其他表中检索出来的。在添加或更改这些表中的数据时,视图将返回改变过的数据。

因为视图不包含数据,所以每次使用视图时,都必须处理查询执行时所需的任一个检索。如果用多个联结和过滤创建了复杂的视图或者嵌套了视图,性能可能下降得很厉害。

创建和使用视图的一下常见的规则和限制:


  • 与表一样,视图必须唯一命名(不能给视图取与别的视图或表相同的名字);
  • 可以创建的视图数目没有限制;
  • 创建视图需要有足够的权限;
  • 视图可以嵌套;
  • ORDER BY可以用在视图中,但如果从该视图检索数据的SELECT语句中也含有ORDER BY,那么该视图中的ORDER BY将被覆盖;
  • 视图不能索引,也不能有关联的触发器或默认值;
  • 视图可以和表一起使用。

创建视图:


CREATE VIEW productcustomers AS SELECT cust_name, cust_contact, prod_id FROM customers, orders, orderitems WHERE customers.cust_id = orders.cust_id;

使用SHOW CREATE VIEW viewname 查看创建视图的语句。

删除视图:DROP VIEW viewname;

更新视图时,可以先用DROP再用CREATE,也可以直接用CREATE OR REPLACE VIEW。如果不存在创建,如果存在则更新。

可以对视图使用INSERT、UPDATE和DELETE。更新一个视图将更新其基表。如果视图定义中有以下操作,则不能对视图使用上述三个语句:

  • 分组(GROUP BY 和 HAVING);
  • 联结;
  • 子查询;
  • 并;
  • 聚集函数(Min()、Count()、Sum()等);
  • DISTINCT;
  • 导出(计算)列。

一般应该将视图用于检索,而不用于更新。

2、存储过程

是为以后的使用而保存的一条或多条MySQL语句的集合。

执行存储过程:

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



其中括号中的是参数,因为他们是MySQL变量,所有MySQL变量都以@开头。

创建存储过程:

CREATE PROCEDURE productpricing()
BEGIN
     MySQL语句
END;



MySQL命令行创建存储过程应注意:

    默认的MySQL语句分隔符为;,MySQL命令行会解释存储过程内的;,这样它们最终不会成为存储过程的成分,这会使存储过程中的SQL出现语法错误。解决办法是临时更改命令行程序的语句分隔符,例如用//作为新的语句结束分隔符:

DELIMITER //



 这样存储过程体内的;仍然保持不动,并且正确的传递给数据库引擎。最后可使用

DELIMITER ;

恢复原来的语句分隔符。

出\以外,任何字符都可以用作语句分隔符。

删除存储过程:

DROP PROCEDURE productpricing;



存储过程不使用()。

当过程存在想删除它时可使用DROP PROCEDURE IF EXISTS;

使用参数:

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;



每个参数必须具有指定的类型,关键字OUT指出相应的参数用来从存储过程传出一个值。IN表示传递给存储过程、INOUT表示对存储过程传入和传出。通过INTO关键字将SELECT结果保存到相应的变量。 不能通过一个参数返回多个行和列。

存储过程代码位于BEGIN和END语句内,代码前面放置--表示注释,用DECLARE语句定义局部变量,也支持可选的默认值。IF条件语句,条件后需跟THEN。还支持ELSEIF和ELSE子句,ELSEIF后需跟THEN。

用SHOW CREATE PROCEDURE 过程名;语句查看创建存储过程的CREATE语句。SHOW PROCESSDURE STATUS LIKE ... 查看存储过程列名。可以在CREATE PROCESSDURE 过程名后添加COMMENT值,在SHOW PROCEDURE STATUS的结果中显示。

3、游标

是一个存储在MySQL服务器上的数据库查询,它不是一条SELECT语句,而是被该语句检索出来的结果集。在存储了游标之后,应用程序可以根据需要滚动或浏览其中的数据。MySQL游标只能用于存储过程。

使用游标的步骤:

  • 声明,这个过程实际上没有检索数据,它只是定义要使用的SELECT语句;
  • 打开游标,这个过程用前面定义的SELECT语句把数据实际检索出来;
  • 根据需要取出检索的各行;
  • 关闭游标。

声明游标:

DECLARE ordernumbers CURSOR 
FOR
SELECT order_num FROM orders;



打开、关闭游标

OPEN ordernumbers; 
CLOSE ordernumbers;

在一个游标关闭后,如果没有重新打开则不能使用它。但是,使用声明过的游标不需要再次声明,用OPEN语句打开即可。如果不明确关闭游标,MySQL将会在到达END语句时自动关闭它。

在一个游标被打开后,可以使用FETCH语句分别访问它的每一行。FETCH指定检索什么数据,检索出来的数据存储在什么地方,它还向前移动游标中的内部行指针,使下一条FECTH语句检索下一行。FETCH 列 INTO 变量。

用DECALRE语句定义的局部变量必须在定义任意游标或句柄之前,句柄必须在游标之后定义。

4、触发器

是MySQL响应DELETE、UPDATE、INSERT语句而自动执行的一条MySQL语句。其他MySQL语句不支持触发器。

在创建触发器时需要给出4条信息:

  • 唯一的触发器名;
  • 触发器关联的表;
  • 触发器应该响应的活动(DELETE、INSERT或UPDATE);
  • 触发器何时执行(处理之前或之后)。

在MySQL5中,触发器名必须在每个表中唯一,不是每个数据库唯一。以后的版本可能会使命名规则更为严格。

触发器用CREATE TRIGGER语句创建:

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



触发器按每个表每个事件每次定义,每个表每个事件只允许有一个触发器。因此每个表最多支持六个触发器(三个语句之前和之后)。单一触发器不能与多个事件或多个表关联。

只有表才支持触发器,视图不支持。

如果BEFORE触发器失败,则MySQL将不执行请求的操作。如果BEFORE触发器或语句本身失败,MySQL将不执行AFTER触发器。

使用DROP TRIGGER语句删除触发器。

INSERT触发器:

  • 在INSERT触发器代码内,可以引用一个名为NEW的虚拟表,访问被插入的行;
  • 在BEFORE INSERT触发器中,NEW中的值也可以被更新(运行更改被插入的值);
  • 对于AUTO_INCREMENT列,NEW在INSERT执行之前包含0,在INSERT执行之后包含新的自动生成值。

CREATE TRIGGER neworder AFTER INSERT ON orders 
FOR EACH ROW SELECT NEW.order_num;
DELETE触发器:

  • 在DELETE触发器代码内,可以应用一个名为OLD的虚拟表。访问被删除的行;
  • OLD中的值全部都是只读的,不能更新。

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;



使用BEGIN END语句块使触发器能容纳多条SQL语句。

UPDATE触发器:

  • 在UPDATE触发器代码中,你可以引用一个名为OLD的虚拟表访问以前的值,引用一个名为NEW的虚拟表访问新更新的值;
  • 在BEFORE UPDATE触发器中,NEW中的值允许 被更新;
  • OLD中的值全部都是只能,不能被更新。

5、事务处理

事务处理用来管理INSERT、UPDATE和DELETE语句,不能回退SELECT语句、CREATE或DROP操作。

用START TRANSACTION;标记事务开始;

使用COMMIT提交;

ROLLBACK关键字回退;

当COMMIT或ROLLBACK语句执行后,事务会自动关闭。

使用SAVEPOINT 保留点名;创建保留点

ROLLBACK TO 保留点名;回退保留点。

保留点在事务处理完成后自动释放,也可以用RELEASE SAVEPOINT明确的释放保留点。

MySQL行为是自动提交所有更改,即执行立即生效,为指示MySQL不自动提交更改,需使用下面语句:

SET autocommit=0;



autocommit标志是针对连接而不是服务器的。

6、国际化

字符集是字符和符号的集合;

编码是某个字符集成员的内部表示;

校对是规定字符如果进行比较的指定。

查看所支持的字符集完整列表:SHOW CHARACTER SET;

查看所支持校对的完整列表:SHOW COLLATION;

许多校对会出现两次,一次区分大小写(由_cs表示),一次不区分大小写(由_ci表示)。

为确定所用的字符集和校对,可以使用以下语句:

SHOW VARIABLES LIKE 'character%';

SHOW VARIABLES LIKE 'collation%';

为了给表指定字符集和校对,可使用带子句的CREATE TABLE:

CREATE TABLE mytable
(
  column1 INT,
  column2 VARCHAR(10)
) DEFAULT CHARACTER SET hebrew
  COLLATE hebrew_general_ci;



MySQL用以下方式确定字符集和校对:

  • 如果指定CHARACTER SET和COLLATE,则使用这些值;
  • 如果只指定CHARACTER SET,则使用此字符集及其默认的校对;
  • 如果既不指定CHARACTER SET,也不指定COLLATE,则同时使用数据库默认。

还允许对每个列设置字符集和校对。

CREATE TABLE mytable
(
  column1 INT,
  column2 VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_general_ci
)
校对在对用ORDER BY子句检索出来的数据排序时起重要的作用。如果需要用与创建表时不同的校对顺序排序,可以在SELECT语句自身中进行:

SELECT * FROM customers
ORDER BY lastname, firstname COLLATE latin1_general_cs;



上面的SELECT语句演示了在通常不区分大小写的表上区分大小写搜索的一种技术,反过来用也是可以的。

串可以在字符集之间进行转化,为此使用Cast()或Convert()函数。

7、安全管理

查看用户:

USE mysql;
SELECT user FROM user;



创建用户账号:

CREATE USER ben IDENTIFIED BY PASSWARD('...');



删除用户账号:

DROP USER bforta



查看访问权限

SHOW GRANTS FOR bforta;



添加权限,使用GRANT语句,要求至少给出:

  • 要授予的权限;
  • 被授予访问权限的数据库或表;
  • 用户名。

GRANT SELECT ON crashcourse.* TO bforta;



撤销权限:

REVOKE SELECT ON crashcourse.* FROM bforta;
更改口令:

SET PASSWORD FOR bforta = Password('...');



8、备份数据

几种解决方案:

  • 使用命令行程序mysqldump转存所有数据库内容到外部文件;
  • 使用命令行程序mysqlhotcopy从一个数据库复制所有数据;
  • 使用MySQL的BACKUP TABLE或SELECT INTO OUTFILE转存所有数据到某个外部文件。

为了保证所有数据被写到磁盘,可能需要在进行备份前使用FLUSH TABLES语句刷新未写数据。

9、数据库维护

ANALYZE TABLE 检查表键是否正确。

CHECK TABLE检查表的问题。

10、日志文件

错误日志,名为hostname.err,默认在data目录中;

查询日志,名为hostname.log,默认在data目录中;

二进制日志,记录更新过的数据,名为hostname.bin,默认在data目录中;

缓慢查询日志,记录执行缓慢的查询,名为hostname-slow.log,位于data目录中。





© 著作权归作者所有

一万
粉丝 30
博文 102
码字总数 173386
作品 0
朝阳
程序员
私信 提问
MySQL快速入门

继恶补了C++基础之后,又来恶补MySQL了,花了将近一天的时间啃完了<MySQL必知必会>这本书,整理了有点糙的读书笔记。 1.SHOW语句的使用: 2.SELECT查询语句: 3.LIMIT子句限定显示结果行数:...

waffle930
2016/10/04
96
0
SQL必知必会读书笔记

半小时学会SQL 本篇文章是 SQL 必知必会 的读书笔记,SQL必知必会的英文名叫做 Sams Teach Yourself in 10 Minutes。但是,我肯定是不能够在10分钟就能学会本书所有涉及到的sql,所以就起个名...

shanyue
2017/10/24
0
0
《MySQL必知必会》笔记2

2016/2/26-27 第二十一章创建和操纵表 创建表 CREATE TABLE语句 IF NOT EXISTS:查看表名是否存在,并且仅在表名不存在时创建它。 NULL列和NOT NULL列。(NULL为默认设置) PRIMARY KEY:定义...

fxdhdu
2016/03/01
41
0
《MySQL必知必会》笔记1

2016/2/21 数据库:保存有组织的数据的容器(通常是一个文件或一组文件) 数据库管理系统(DBMS):用于访问数据库。 表:某种特定类型数据的结构化清单。相同的数据库中表的名字是唯一的 模...

fxdhdu
2016/03/01
19
0
干货 "数据分析必看书籍"

今日分享:数据分析该看哪些书 小编今天逛了知乎以及与数据分析相关的网站,挑选了十本搜索度较高的书籍(评论区有干货福利),在此推荐给大家。 针对数据分析能力的高低,主要按下面三个阶段...

数据那些事儿
2017/12/02
0
0

没有更多内容

加载失败,请刷新页面

加载更多

Eureka应用注册与集群数据同步源码解析

在之前的EurekaClient自动装配及启动流程解析一文中我们提到过,在构造DiscoveryClient类时,会把自身注册到服务端,本文就来分析一下这个注册流程 客户端发起注册 boolean register() t...

Java学习录
34分钟前
10
0
Java描述设计模式(15):责任链模式

本文源码:GitHub·点这里 || GitEE·点这里 一、生活场景描述 1、请假审批流程 公司常见的请假审批流程:请假天数 当 day<=3 天,项目经理审批当 3<day<=5 天,部门经理审批当 day>5 天...

知了一笑
44分钟前
8
0
总结:数组与链表

1、内存申请:数组在内存上是连续的空间;链表,内存地址上可以是不连续的。 2、查询速度:数组可以随机访问,链表必须顺序访问,即从首个元素开始遍历,逐个查找,所以数组查询很快。 3、写入...

浮躁的码农
53分钟前
6
0
HashMap源码分析

read

V丶zxw
今天
5
0
Python字符串或JSON字符串转字典dict、列表list

有3种方法 1、使用ast模块 >>> import ast>>> s = '["test",1]'>>> ast.literal_eval(s)['test',1]>>> s = '{"test":1}'>>> ast.literal_eval(s){'test': 1} 2、eval函数,这个......

编程老陆
今天
8
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部