MySQL必知必会

MySQL必知必会

• 联结的使用, 子查询, 正则表达式和基于全文本的搜索, 存储过程, 游标, 触发器, 表约束.

了解SQL

数据库基础

• 电子邮件地址薄里查找名字时, 因特网搜索站点上进行搜索, 验证名字和密码, 都会用到数据库.
• 数据库是一个以某种有组织的方式存储的数据集合.
  • 把数据库想象成一个文件柜, 这个文件柜存放数据的物理位置, 不管数据是以什么形式存在以及如何组织的.
• 数据库(database)保存有组织的数据的容器(通常是一个文件或一组文件).
• 在数据库领域中, 保存相关资料的特定文件称为表(table): 一种结构化的文件, 可以用来存储某种特定类型的数据.
  • 表可以存储顾客清单, 产品目录, 信息清单等.
  • 存储在表中的数据是一种类型的数据或一个清单.
  • 数据库中的每个表都有一个名字, 用来标识自己, 此名字是唯一的, 这表示数据库中没有其他表具有相同的名字.
    • 数据库名与表名结合.
• 模式(schema): 关于数据库和表的布局及特性的信息, (可以存什么样的数据, 数据如何分解, 各部分信息如何命名等).
• 表由列组成, 列中存储着表中某部分的信息.
  • 列(column): 表中的一个字段, 所有表都是由一个或多个列组成的.
  • 数据库中的每个列都有相应的数据类型, 数据类型(datatype)定义列可以存储的数据种类.
  • 行(row): 表中的数据是按行存储, 所保存的每个记录存储在自己的行内, 表中的一个记录. 比如一行代表一个顾客.
    • 数据库记录(record).
  • 主键(primary key): 一列, 其值能够唯一区分表中每个行. 唯一标识表中每行的这个列(或这组列)称为主键.
    • 主键用来表示特定的一行.
    • 任意两行都不具有相同的主键值;
    • 每个行都必须具有一个主键值(主键列不允许NULL值).

什么是SQL

• SQL(sequel)是结构化查询语言(Structured Query Language)的缩写, SQL是一种专门用来与数据库通信的语言.
• SQL有如下的优点:
  • SQL不是某个特定数据库供应商专有的语言.
  • SQL简单易学(它的语句都是由描述性很强的英语单词组成, 而且这些单词数目不多).
  • SQL尽管看上去很, 但它实际上是一种强有力的语言, 灵活使用其语言元素, 可以进行非常复杂和高级的数据库操作.

MySQL简介

DBMS(data base manage system)数据库管理系统

• 基于共享文件系统的DBMS;
• 基于客户机-服务器的DBMS;
  • MySQL, Oracle以及Microsoft SQL Server等数据库是基于客户机-服务器的数据库.
  • 服务器部分是负责所有数据访问和处理的一个软件 --- 运行在称为数据库服务器的计算机上.
    • 数据添加, 数据删除, 数据更新在服务器软件上完成.
    • 服务器根据需要过滤, 丢弃和排序数据.
  • 客户即是与用户打交道的软件.
• MySQL是一个客户机—服务器DBMS,因此,为了使用MySQL,需要有一个客户机,即你需要用来与MySQL打交道(给MySQL提供要执行的命令)的一个应用.
• 每个MySQL安装都有一个名为 mysql 的简单命令行实用程序.
  • 命名用;或\g结束, 仅按Enter不执行命令.
  • help或\h获得帮助.
  • 输入quit或exit退出命令行实用程序.
• MySQL Administrator(MySQL管理器)是一个图形交互客户机, 用来简化MySQL服务器的管理.
• MySQL Query Browser为一个图形交互客户机,用来编写和执行MySQL命令.

使用MySQL

• 在执行命令之前登录到DBMS, 登录名可以与网络登录名不相同, MySQL在内部保存自己的用户列表, 并且把每个用户与各种权限关联起来.
• 为了连接到MySQL, 需要以下信息:
  • 主机名(计算机名) --- 如果连接到本地MySQL服务器, 为localhost;
  • 端口(如果使用默认端口3306之外的端口);
  • 一个合法的用户名.
  • 用户口令(如果需要).
• 使用一个USE关键字选择数据库; --- USE语句并不返回任何结果.
• 使用SHOW命令显示数据库/表/列/用户/权限等信息.
  • SHOW DATABASES --- 返回数据库的一个列表.
  • SHOW TABLES --- 返回当前选择的数据库内可用表的列表.
  • SHOW COLUMNS FROM customers --- 用来显示表列, 需要给定一个表名.
    • DESCRIBE语句是SHOW COLUMNS FROM的一种快捷方式.
  • SHOW STATUS --- 用于显示广泛的服务器状态信息.
  • SHOW CREATE DATABASE和SHOW CREATE TABLE --- 分别用来显示创建特定数据库或表的MySQL语句.
  • SHOW GRANTS --- 用来显示授予用户的安全权限.
  • SHOW ERRORS和SHOW WARNINGS --- 用来显示服务器错误或讲稿消息.
  • HELP SHOW --- 显示SHOW语句.

检索数据

• SELECT语句从表中检索一个或多个数据列.
  • 想选择什么, 以及从什么地方选择.
• SELECT prod_name --- 从products表中检索一个名为prod_name的列. 返回的数据可能是乱序的.
  • 用分号;表示一条MySQL语句的结束.
  • SQL语句不区分大小写, SELECT与select是相同的.
    • 关键字使用大写, 所有列和表名使用小写.
• 检索多个列: SELECT关键字中给出多个逗号分隔;
  • SELECT prod_id, prod_name, prod_price FROM products;.
• SELECT * FROM products --- 检索所有列.
• DISTINCT关键字返回不同的值.
  • SELECT DISTINCT vend_id FROM products --- 检索不同id的行, 相同的id会被忽略.
• LIMIT 限制返回结果.
  • SELECT prod_name FROM products LIMIT 5; --- 返回值不多于5行.
  • SELECT prod_name FROM products LIMIT 5,5; --- 返回值从第5行开始的下5行. 第一个数是开始的位置, 第二个数是要检索的行数.
  • 第一行是行0. 行数不够只返回能返回的行数.
  • LIMIT 4 OFFSET 3 --- 从行3开始取4行, 与LIMIT 3 4一个意思.
• 使用完全限定的表名:
  • SELECT products.prod_name FROM products; --- 玩玩限定列名.
  • 表名也可以是完全限定的, SELECT products.prod_name FROM crashcourse.products;. (假定products位于crashcourse数据库中).

排序检索数据

• ORDER BY根据需要排序检索出数据.
• 检索出的数据一般将以它在底层表中出现的顺序显示. --- 最初添加到表中的数据顺序.
• 关系数据库设计理论认为, 如果不明确规定排序顺序, 则不应该假定检索出的数据的顺序有意义.
• SELECT prod_name FROM products ORDER BY prod_name; --- 以名字的进行排序.
  • ORDER BY 子句中使用的列将是为显示所选择的列. 用非检索的列排序数据是完全合法的.
• 按多个列进行排序:
  • SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price, prod_name;.
• 利用DESC关键字指定排序方向为降序, SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price DESC;.
  • DESC 关键字只应用到直接位于其前面的列名.
• 关键字是 ASC ( ASCENDING ), 在升序排序时可以指定它; --- 默认是升序的.
• SELECT prod_price FROM products ORDER BY prod_price DESC LIMIT 1; --- prod_price DESC 保证行是按照由最昂贵到最便宜检索的, 而LIMIT 1 告诉MySQL仅返回一行.

过滤数据

• WHRER子句指定搜索条件. --- 根据特定操作或报告的需要提取表数据的子集.
  • 只检索所需数据需要指定搜索条件(search criteria), 搜索条件也称为过滤条件(filter condition).
• SELECT prod_name, prod_price FROM products WHERE prod_price = 2.5; --- 只返回 prod_price 值为 2.50 的行.
  
• 检查单个值:
  • SELECT prod_name, prod_price FROM products WHERE prod_name = 'fuses'; --- 它返回 prod_name 的值
    为 Fuses 的一行.
  • SELECT prod_name, prod_price FROM products WHERE prod_price < 10; --- 价格小于10的行.
• 不匹配检查:
  • SELECT vend_id, prod_name FROM products WHERE vend_id <> 1003; 等价与SELECT vend_id, prod_name FROM products WHERE vend_id != 1003;.
• 范围值检查:
  • 为了检查每个范围的值, 可使用BETWEEN操作符, 需要两个值, 即范围的开始值和结束值.
  • SELECT prod_name, prod_price FROM products WHERE prod_price BETWEEN 5 AND 10;.
• 空值检查:
  • NULL无值(no value), 它与字段包含0, 空字符串或仅仅包含空格不同.
  • SELECT prod_name FROM products WHERE prod_price IS NULL; --- 返回没有价格(空 prod_price 字段,不是价格为 0 )的所有产品,由于表中没有这样的行,所以没有返回数据.

数据过滤

• 组合WHERE子句:
  • AND子句的方式或OR子句的方式使用.
  • 操作符(operator) 用来联结或改变 WHERE 子句中的子句的关键字。也称为逻辑操作符(logical operator).
  • SELECT prod_id, prod_price, prod_name FROM products WHERE vend_id = 1003 AND prod_price <= 10;. --- AND用在WHERE子句中用来知识检索满足所有给定条件的行.
  • SELECT prod_id, prod_price, prod_name FROM products WHERE vend_id = 1002 OR prod_price vend_id = 1003; --- OR 操作符告诉DBMS匹配任一条件而不是同时匹配两个条件.
• SQL(像多数语言一样)在处理 OR 操作符前, 优先处理 AND 操作符。 --- 使用圆括号明确地分组相应的操作符. 不要过分依赖默认计算次序.
• IN 操作符:
  • IN 操作符用来指定条件范围, 范围中的每个条件都可以进行匹配.
  • IN 取合法值的由逗号分隔的清单, 全都括在圆括号中.
  • SELECT prod_name, prod_price FROM products WHERE vend_id IN(1002, 1003) ORDER BY prod_name;.
  • IN操作符的优点:
    • 子啊使用长的合法选项清单时, IN操作符的语法更清楚且更直观.
    • 在使用IN时, 计算的次序更容易管理(因为使用的操作符更少).
    • IN操作符一般比OR操作符清单执行更快.
    • IN操作符的最大优点是可以包含其他SELECT语句, 使得能够更动态地建立WHERE子句.
      • IN WHERE子句中用来指定要匹配值的清单的关键字, 功能与OR相当.
• NOT操作符:
  • NOT能否定它之后所跟的任何条件.
  • SELECT prod_name, prod_price FROM products WHERE vend_id NOT IN(1002, 1003) ORDER BY prod_name;.
  • MySQL支持使用NOT对IN、BETWEEN和EXISTS子句取反, 这与多数其他DBMS允许使用NOT对各种条件取反有很大的差别.

用通配符进行过滤

• LIKE操作符进行通配搜索, 以便对数据进行复杂过滤.
  • 通配符(wildcard) 用来匹配值的一部分的特殊字符.
  • 搜索模式(search pattern) 由字面值、通配符或两者组合构成的搜索条件.
• 通配符本身实际是SQL的 WHERE 子句中有特殊含义的字符.
  • LIKE 指示MySQL, 后跟的搜索模式利用通配符匹配而不是直接相等匹配进行比较.
  • %表示任何字符出现任意次数, SELECT prod_id, prod_name FROM products WHERE prod_name LIKE 'jet%';.
  • 通配符可在搜索模式中任意位置使用,并且可以使用多个通配符.
  • %代表搜索模式中给定位置的0个、1个或多个字符.
  • 下划线(_)通配符:
    • _只匹配单个字符而不是多个字符.
• 通配符搜索一般比其他搜索所花时间更长:
  • 不要过度使用通配符.
  • 在确定需要使用通配符时, 除非绝对有必要, 否则不要把他们用在搜索模式的开始处.
  • 仔细注意通配符的位置.

正则表达式进行搜索

• 正则表达式是用来匹配文本的特殊的串(字符集合).
• 正则表达式的作用是匹配文本,将一个模式(正则表达式)与一个文本串进行比较.
• SELECT prod_name FROM products WHERE prod_name REGEXP '1000' ORDER BY prod_name; --- 利用关键字REGEXP进行正则表达式.
• 为了区分关键字, 使用BINARY关键字.
• SELECT prod_name FROM products WHERE prod_name REGEXP '1000|2000' ORDER BY prod_name; --- 匹配两个条件.
• []匹配几个字符之一. [^123]却匹配除这些字符以外的任何东西.
• [0-9]匹配范围. 用双斜杠表示匹配特殊字符, 比如\\-表示查找-, \\.表示查找.;
  • \f --- 换页.
  • \n --- 换行.
  • \r --- 回车.
  • \v --- 纵向制表.
• 匹配字符类, 预定义的字符集, 称为字符类(character class);
  
  
  
• ^ 匹配串的开始. LIKE 匹配整个串而 REGEXP 匹配子串.

创建计算字段

• 存储在数据库中的数据一般不是应用程序所需要的格式.
• 需要直接从数据库中检索出转换、计算或格式化过的数据.
• 计算字段并不实际存在于数据库表中, 计算字段是运行时在SELECT语句内创建.
  • 字段(field) 基本上与列(column)的意思相同, 经常互换使用, 不过数据库列一般称为列,而术语字段通常用在计算字段的连接上.
• 拼接(concatenate) 将值联结到一起构成单个值. MySQL则使用 Concat() 函数来实现.
  • Concat() 拼接串,即把多个串连接起来形成一个较长的串.
• 使用MySQL的 RTrim() 函数来完成删除数据右侧多余的空格来整理数据.
  • LTrim() (去掉串左边的空格)以及Trim() (去掉串左右两边的空格).
• 别名(alias)是一个字段或值的替换名。别名用 AS 关键字赋予.
  • AS vend_title 。它指示SQL创建一个包含指定计算的名为 vend_title 的计算字段.
  • 别名有时也称为导出列(derived column).
• 执行算术运算:

  SELECT prod_id,
         quantity,
         item_price,
         quantity*item_price AS expended_price
  FROM orderitens
  WHERE order_num = 20005 
  

使用数据处理函数

• SQL支持利用函数来处理数据。函数一般是在数据上执行的,它给数据的转换和处理提供了方便.

• 使用函数:

  • 用于处理文本串(删除或填充值, 转换值为大写或小写)的文本函数.
  • 用于在数值数据上进行算术操作(绝对值, 进行代数运算)的数值函数.
  • 用于处理日期和时间值并从这些值中提取特定成分的日期和时间函数.
  • 返回DBMS正使用的特殊信息(用户登录信息, 检查版本)的系统函数.
    
    
    
    

• Soundex()函数进行搜索时, 可以匹配所有发音类似的字符串.

• Date(order_date)指示仅提取列的日期部分.

聚集函数

• 聚集函数(aggregate function) 运行在行组上, 计算和返回单个值的函数.
  

• AVG() 只能用来确定特定数值列的平均值, 而且列名必须作为函数参数给出.

• 使用 COUNT(*) 对表中行的数目进行计数, 不管表列中包含的是空值( NULL )还是非空值.

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

• MAX() 函数忽略列值为 NULL 的行.

• 对所有的行执行计算,指定 ALL 参数或不给参数(因为 ALL 是默认行为).

• 只包含不同的值, 指定 DISTINCT 参数. --- 排序了相同的值.

• 组合聚集函数:

  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子句.
• 分组是在 SELECT 语句的 GROUP BY 子句中建立的.

SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id;

• 通过ID号进行分组.

• HAVING非常类似于WHERE, WHERE过滤行, HAVING过滤组.

• WHERE 在数据分组前进行过滤, HAVING 在数据分组后进行过滤.

  SELECT vend_id, COUNT(*) AS num_prods
  FROM products
  WHERE prod_price >= 10
  GROUP BY vend_id
  HAVING COUNT(*) >= 2;
  

• 分组和排序(ORDER BY 和 GROUP BY)

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

  • 进行排序

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

• SELECT子句顺序:
  
  

使用子查询

• SELECT语句是SQL的查询, SQL还允许创建子查询(subquery), 即嵌套在其他查询中的查询.

  SELECT cust_id
  FROM orders
  WHERE order_num IN (SELECT order_num
                      FROM orderitems
                      WHERE prod_id = 'TNT2');
  

联结表

• SQL最强大的功能之一就是能在数据检索查询的执行中联结(join)表.

• 关系表的设计就是要保证把信息分解成多个表, 一类数据一个表.

• 外键为某个表中的一列, 它包含另一个表的主键值, 定义了两个表之间的关系.

• 分解数据为多个表能更有效地存储, 更方便地处理, 并且具有更大的可伸缩性.

• 使用联结从多个数据表中检索出数据.

• 联结是一种机制, 用来在一条 SELECT语句中关联表, 因此称之为联结.

  SELECT vend_name, prod_name, prod_price
  FROM vendors, products
  WHERE vendors.vend_id = products.vend_id
  ORDER BY vend_name, prod_name;
  

• 在一条 SELECT 语句中联结几个表时, 相应的关系是在运行中构造的.

创建高级联结

• 使用不同类型的联结
  • 内部联结或等值联结(equijoin)的简单联结.
  • 自联结:

    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';
    

    • 用自联结而不用子查询. --- 联结比处理子查询更快.
  • 自然联结:
    • 自然联结排除多次出现, 使每个列只返回一次.
  • 外部联结: 一个表中的行与另一个表中的行相关联, 联结包含了那些在相关表中没有关联行的行.
    • 在使用OUTER JOIN语法时, 必须使用RIGHT或LEFT关键字指定包括其所有行的表.

    SELECT customers.cust_id, orders,order_num
    FROM customers LEFT OUTER JOIN orders
      ON customers.cust_id = orders.cust_id;
    

• 带聚集函数的联结.
• 使用联结和联结条件:
  • 注意使用的联结类型.
  • 保证使用正确的联结条件, 否则将返回不正确的数据.
  • 应该总是提供联结条件, 否则会得出笛卡尔积.
  • 一个联结中可以包含多个表, 甚至可以采用不同的联结类型.

组合查询

• MySQL也允许执行多个查询(多条 SELECT 语句), 并将结果作为单个, 查询结果集返回.
  • 组合查询通常称为并( union ) 或复合查询(compound query).
• 需要用到组合查询的地方:
  • 在单个查询中从不同的表返回类似结构的数据.
  • 对单个表之赐你个多个查询, 按单个查询返回数据.
• 利用UNION操作符来组合数条SQL查询.

  SELECT vend_id, prod_id, prod_price
  FROM products
  WHERE prod_price <= 5
  UNION
  SELECT vend_id, prod_id, prod_price
  FROM products
  WHERE vend_id IN (1001, 1002);
  

• UNION 规则:
  • UNION 必须由两条或两条以上的 SELECT 语句组成,语句之间用关键字 UNION 分隔.
  • UNION 中的每个查询必须包含相同的列、表达式或聚集函数.
  • 列数据类型必须兼容:类型不必完全相同, 但必须是DBMS可以隐含地转换的类型.
• UNION 从查询结果集中自动去除了重复的行.
  • 如果想返回所有匹配行,可使用 UNION ALL 而不是 UNION.
• 在用 UNION 组合查询时, 只能使用一条 ORDER BY 子句,它必须出现在最后一条 SELECT 语句之后.

全文搜索

• 全文搜索功能进行高级的数据查询和选择.

  • MyISAM支持全文搜索, InnoDB不支持全文搜索.

• 性能: 通配符和正则表达式匹配通常要求尝试表中的所有行, 比较耗时.

  • 使用通配符和正则表达式匹配, 很难(而且并不总是能)明确地控制匹配什么和不匹配什么.
  • 智能化的选择结果的方法.

• 使用全文本搜索时, MySQL不需要分别查看每个行, 不需要分别分析和处理每个词.

• SELECT 可与 Match() 和 Against() 一起使用以实际执行搜索.

• CREATE TABLE 语句定义表 productnotes 并列出它所包含的列即可.

• 在定义之后,MySQL自动维护该索引。在增加、更新或删除行时, 索引随之自动更新.

  SELECT note_text
  FROM productnotes
  WHERE Match(note_text) Against('rabbit');
  

  • 传 递 给 Match() 的 值 必 须 与FULLTEXT() 定义中的相同.

• 行越多越好 表中的行越多(这些行中的文本就越多), 使用查询扩展返回的结果越好.

  • 查询扩展用来设法放宽所返回的全文本搜索结果的范围;
  • Against('anvils' WITH QUERY EXPANSION); --- 查询扩展.

• 布尔文本搜索:

  • 要匹配的词;
  • 要排斥的词;
  • 排列提示;
  • 表达式分组;
  • 另一些内容.
  • 即使没有 FULLTEXT 索引也可以使用.

  SELECT note_text
  FROM productnotes
  WHERE Match(note_text) Against('heavy -rope*' IN BOOLEAN MODES);
  

  .

• 全文本搜索的使用说明:

  • 在索引全文本数据时,短词被忽略且从索引中排除.
  • 带有一个内建的非用词(stopword)列表, 这些词在索引全文本数据时总是被忽略.
  • 许多次出现频率很高, 索索他们没有用处(返回太多的结果).
  • 表中的行数少于3行, 全文搜索不返回结果.
  • 忽略词中的单引用. --- don't被认为是dont.
  • 不具有词分割符的语言不能恰当地返回全文搜索结果.
  • 仅在MyISAM数据库引擎中支持全文本搜索.

插入数据

• INSERT是用来插入(或添加)行到数据库表的:
  • 插入完整的行;
  • 插入行的一部分;
  • 插入多行;
  • 插入某些查询的结果.
  • INSERT语句一般不会产生输出.

更新和删除数据

• 更新数据 --- UPDATE语句. 要更新的表; 列名和他们的新值; 确定要更新行的过滤条件.
  • 更新表中特定行;
  • 更新表中所有行.

  UPDATE customers
  SET cust_emial = 'elmer@fudd.com'
  WHERE cust_id = 10005;
  

• 删除数据: DELETE语句
  • DELETE FROM customers WHERE cust_id = 10006; DELETE FROM 要求指定从中删除数据的表名, WHERE 子句过滤要删除的行.
• 许多SQL程序员使用 UPDATE 或 DELETE 时所遵循的习惯:
  • 除非确实打算更新和删除每一行,否则绝对不要使用不带 WHERE子句的 UPDATE 或 DELETE 语句.
  • 保证每个表都有主键, 尽可能像 WHERE 子句那样使用它.
  • 在对 UPDATE 或 DELETE 语句使用 WHERE 子句前,应该先用 SELECT 进行测试,保证它过滤的是正确的记录,以防编写的 WHERE 子句不正确.
  • 使用强制实施引用完整性的数据库, 这样MySQL将不允许删除具有与其他表相关联的数据的行.

创建和操作表

• 使用具有交互式创建和管理表的工具 或 表也可以直接用MySQL语句操纵.
• 利用CREATE TABLE创建表, 必须给出下列信息:
  • 新表的名字, 在关键字CREATE TABLE 之后给出.
  • 表列的名字和定义, 用逗号分隔.

  CREATE TABLE custeomers
  (
    cust_id      int       NOT NULL AUTO_INCREMENT,
    cust_name    char(50)  NOT NULL,
    PRIMARY KEY (cust_id)   ## 主键值
  ) ENGING=InnoDB;
  

• 主键值必须唯一, 表中的每个行必须具有唯一的主键值.
• AUTO_INCREMENT 告诉MySQL,本列每当增加一行时自动增量.
• DEFAULT 关键字指定.
• MySQL有一个具体管理和处理数据的内部引擎.
• 如果省略 ENGINE= 语句, 则多数SQL语句都会默认使用它。
  • InnoDB 是一个可靠的事务处理引擎,它不支持全文本搜索;
  • MEMORY 在功能等同于 MyISAM ,但由于数据存储在内存(不是磁盘)中, 速度很快(特别适合于临时表).
  • MyISAM 是一个性能极高的引擎, 它支持全文本搜索(参见第18章), 但不支持事务处理.
• RENAME TABLE语句可以重命名一个表.
  • RENAME TABLE customers2 TO customers;.
• ALTER TABLE 用来更改表列(或其他诸如约束或索引等对象).
• DROP TABLE 用来完整地删除一个表. --- 永久删除该表.

使用视图

• 视图是虚拟的表, 视图只包含使用时动态检索数据的查询.
• 使用视图:
  • 视图用CREATE VIEW语句来创建.
  • 使用SHOW CREATE VIEW viewname; 来查看创建视图的语句.
  • 用DROP删除视图, 其语法为DROP VIEW viewname;
  • CREATE OR REPLACE VIEW来更新视图.

使用存储过程

• 执行一个处理需要针对许多表的多条MySQL语句.
• 存储过程简单来说,就是为以后的使用而保存的一条或多条MySQL语句的集合.

  CALL productpricing(@pricelow,
                      @pricehigh,
                      @priceaverage);
  # 执行名为productpricing的存储过程, 计算并返回产品的最低, 最高和平均价格.
  CREATE PROCEDURE productpriting()
  BEGIN
    SELECT Avg(prod_price) AS priceaverage
    FROM products;
  END;
  

使用游标

• 游标(cursor)是一个存储在MySQL服务器上的数据库查询, 它不是一条 SELECT 语句,而是被该语句检索出来的结果集.
• 游标主要用于交互式应用,其中用户需要滚动屏幕上的数据, 并对数据进行浏览或做出更改。
• 游标用 DECLARE 语句创建, DECLARE 命名游标, 并定义相应的 SELECT 语句,根据需要带 WHERE 和其他子句.

  CREATE PROCEDURE processoerders()
  BEGIN
    -- 声明一个游标
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;
  END;
  

• OPEN ordernumbers; --- 打开游标;
• CLOSE ordernumbers; --- 关闭游标.

使用触发器

• 触发器是MySQL响应以下任意语句而自动执行的一条MySQL语句(或位于 BEGIN 和 END 语句之间的一组语句).
  • DELETE;
  • INSERT;
  • UPDATE.
• 触发器用 CREATE TRIGGER 语句创建.

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

• 更新触发器:

  CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors
  FOR EACH ROW SET new.VEND_STATE = Upper(NEW.vend_state);
  

管理事务处理

• 利用COMMIT和ROLLBACK语句来管理事务处理.
• 事务处理(transaction processing)可以用来维护数据库的完整性, 它保证成批的MySQL操作要么完全执行,要么完全不执行.
• 关于事物处理的几个术语:
  • 事务(transaction): 指一组SQL语句;
  • 回退(rollback): 指撤销指定SQL语句的过程;
  • 提交(commit): 指将未存储的SQL语句结果写入数据库表;
  • 保留点(savepoint): 指事务处理中设置的临时占位符(place-holder), 你可以对它发布回退(与回退整个事务处理不同).
• START TRANSACTION 标识事务的开始.
• 一般的MySQL语句都是直接针对数据库表执行和编写的.

全球化和本地化

• 数据库表用来存储和检索数据.
• 重要术语:
  • 字符集: 为字母和符号的集合.
  • 编码: 为某个字符集成员的内部表示.
  • 校对: 为规定字符如何比较的指令.
• SHOW CHARACTER SET --- 查看所支持的字符集完整列表.
• 使用 Cast() 或 Convert ()函数进行字符集之间的转换.

安全管理

• CREATE USER 语句创建一个新用户账号.

数据库维护

• ANALYZE TABLE, 用来检查表键是否正确;
• CHECK TABLE 用来针对许多问题对表进行检查.

改善性能

• MySQL(与所有DBMS一样)具有特定的硬件建议.
• 关键的生产DBMS应该运行在自己的专用服务器上。
• MySQL是用一系列的默认设置预先配置的,从这些设置开始通常是很好的。但过一段时间后你可能需要调整内存分配、缓冲区大小等.
  • SHOW VARIABLES 查看当前设置;
  • SHOW STATUS 查看相应状态.
  • MySQL一个多用户多线程的DBMS,换言之,它经常同时执行多个任务;
  • 总是有不止一种方法编写同一条 SELECT 语句。应该试验联结、并、子查询等,找出最佳的方法.
  • 使用 EXPLAIN 语句让MySQL解释它将如何执行一条 SELECT 语句.
  • 存储过程执行得比一条一条地执行其中的各条MySQL语句快.
  • 必须索引数据库表以改善数据检索的性能.
  • 通过使用多条SELECT 语句和连接它们的 UNION 语句,你能看到极大的性能改进.
  • 索引改善数据检索的性能,但损害数据插入、删除和更新的性能.
  • LIKE 很慢。一般来说,最好是使用 FULLTEXT 而不是 LIKE.
  • 数据库是不断变化的实体。一组优化良好的表一会儿后可能就面目全非了。由于表的使用和内容的更改,理想的优化和配置也会改变.