MySQL必知必会笔记（一）基础知识和基本操作

第一章  了解MySQL

    数据库       保存有组织的数据的容器。（通常是一个文件或一组文件）

    人们经常使用数据库这个术语代替他们使用的软件。这是不正确的，确切的说，数据库软件应称为DBMS(数据库管理系统)，数据库是通过DBMS创建和操纵的容器。漱口可以是保存在硬件设备上的文件，但也可以不是。你使用DBMS来代替自己访问数据库。

    表           表是一种结构化的文件可用来存储某种特定的类型的数据。

     某种特定类型数据的结构化清单。

    模式        关于数据库和标的布局及特性的信息

    列         表中的一个字段。表由列组成。列中存储着表里某部分的信息。

   数据类型    所容许的数据的类型。每个表列都有相应的数据类型，它限制（或容许）该列中存储的数据。

    行         表中的一个记录

    主键       一列（或一组列），其值能够唯一区分表中的每个行。

     虽然不是必须的，但是一般都建立主键。便于以后的数据管理

     表中任何列都可以作为主键，只要满足以下条件：

        1 任意两行都不具有相同的主键值。

        2 每个行都必须有一主键值（主键值不允许为NULL）

     主键的最好习惯：

         不更新主键列的值

        不重用主键列的值

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

    什么是sql

    Sql是结构化查询语言的缩写。Sql是一种专门用类与数据库通信的语言。

 

第二章  MySQL简介

    什么是Mysql    Mysql是一种DBMS，即它是一种数据库软件。

   Mysql版本主要更改

       4-——InnoDB引擎，增加了事务处理、并、改进全文搜索等支持

       4.1——对函数库、子查询、集成帮助等的重要增加、

       5——存储过程、触发器、游标、试图等。

 

第三章  使用mysql

    连接

         主机名端口   一个合法用户用户口令

        Mysql -uroot-p -hmyserver-P9999

    选择数据库

         可使用USE关键字，mysql语言组成部分的一个关键字，绝不要使用关键字命名一个表或列

        USE dataname;

    显示数据库列表

        SHOW  DATABASES;

    一个数据库内的表的列表（USE进入数据库的情况下）

        SHOW TABLES;

        SHOW也可以用来显示表列

           SHOW COLUMNS FROM column;

        DESCRIBE 是SHOW COLUMNS的一种快捷方式；DESCRIBE cust;

    其他的SHOW语句

        SHOW STATUS       显示广泛的服务器状态信息

        SHOW CREATE DATABASE  显示创建特定数据库的MYSQL语句

        SHOW CREATE TABLE  显示创建特定表的MYSQL语句

        SHOW GRANTS       显示授权用户的安全权限

        SHOW ERRORS       显示服务器的错误信息

        SHOW WARNINGS        显示服务器的警告信息

MySQL必知必会笔记（二）SELECT语句 检索 排序 过滤 通配符搜索 正则表达式搜索

第四章 检索数据

    检索单列

        SELECTcolumnOneFROM table;

    检索多列

        SELECTcolumnOne,columnTwo,columnThireFORM table;

    检索所有列

        SELECT*FROM products;           //一般，除非你确实需要表中的每个列，否则最好不要用*通配符

    检索不同的行

       检索出来的数据不重复DISTINCT关键字，顾名思义返回不同的值

          SELECTDISTINCTcolumnOneFROM table;  //检索出来的columnOne没有重复值

       DISTINCT关键字应用于所有列而不仅是前置它的列

        SELECTDISTINCT vend_id，prod_price......要求vend_id，prod_price这两列都不出现重复的值

  限制结果条数

      sql语句后面加入下面sql语句

        LIMIT 5         显示结果的前5条

       LIMIT3,4       从行3开始的后4行

       LIMIT 4OFFSET 3 从行3开始的后4行

第五章 排序检索数据

  子句       Sql语句是由子句构成，有些子句是必须的，有些事可选的。一个子句通常是由一个关键字和所提供的数据组成。

  按单列排序

 SELECT columnOneFROM table ORDER BY columnOne; 

 SELECT columnOneFROM table ORDER BY columnTwo; //用非检索列也是可以的，如根据columnTwo

  按多列排序

 SELECT columnOne,columnTwo,columnThireFROM productsORDER BYprod_price,prod_name;

 排序是先根据前面的columnOne排序，如果一样再根据后面的columnTwo排序

  指定排序方向

 ASC 升序默认    DESC降序   关键字仅作用到直接位于前面的列名

 SELECT columnOne,columnTwo,columnThireFROM tableORDER BY columnOneASC,columnTwo      DESC;   //先按columnOne正排序再按columnTwo倒序排序

第六章 过滤数据

    使用WHERE语句

       SELECT columnOne,columnTwoFROM table WHERE columnOne = 3;

WHERE子句操作符

       操作符        说明

       =               等于

       <>             不等于（数字比较）

       !=              不等于（数字和字符串比较）

       <               小于

       >               大于

       <=             小于等于

       >               大于

       >=             大于等于

BETWEEN在指定的两个指之间必须指定2个值。这两个值必须使用AND连接

       SELECTcolumnOne,columnTwo,FROM table WHERE columnOne BETWEEN 3 AND 8; // 检索columnOne值为3到8之间的行

空值检查

        SELECT语句中有一个子句检查具有NULL值的列,IS NULL子句。

       SELECT columnOneFROM table WHERE columnOne IS NULL;   //检索 columnOne值为NULL的行

       注意：在数据库中NULL是不能被匹配和不匹配的语句找出来，所以一定要注意表中是否存在NULL值。

 

第七章 数据过滤

   组合WHERE子句

   组合方式

AND组合和OR组合

       必须满足所有条件

           SELECTcolumnOne,columnTwo,columnThireFROM table WHERE columnOne = 2009AND columnTwo <=10;    //检索columnOne =2009且columnTwo <= 10的行

       满足任意一个条件

           SELECTcolumnOne,columnTwo,columnThireFROM table WHERE columnOne = 2009OR columnTwo <=10;//检索columnOne =2009或columnTwo <= 10的行

   计算次序

       先计算AND再计算OR,不要过分依赖默认计算次序，可以使用括号改变计算次序，它能消除歧义。

   IN操作符    IN操作符用来指定范围，范围中的每个条件进行匹配。IN取合法值的逗号分隔的清单。

       SELECTcolumnOne,columnTwoFROM table WHERE columnOne IN (1002,1005,1006)ORDERBY columnTwo;    //检索columnOne为1002或1005或1006的行并且按columnTwo分组

   IN操作符完成与OR相同的功能，优点如下：

        1使用长的合法选项清单时，IN操作符的预防更清楚且直观

        2使用IN时，计算的次序更容易管理（以为使用的操作符更少）

        3 IN操作符一般比OR操作符执行更快

        4 IN操作符最大的优点可以包含其他SELECT语句，使得能够更动态的创建WHERE子句。

   NOT操作符     WHERE子句中的NOT操作符有且只有一个功能，那就是否定它之后所跟的任何条件

       SELECTcolumnOne,columnTwoFROM table WHERE columnOne NOTIN (1002,1005,1006)ORDERBY columnTwo;    //检索columnOne不为1002或1005或1006的行并且按columnTwo分组

   MySQL支持使用NOT对IN、BETWEEN和EXISTS子句取反。

 

第八章 用通配符进行过滤

   LINK操作符

   通配符：用来匹配值的一部分的特殊字符。

   搜索模式：又字面值、通配符或两者组成构成的搜索条件

   %     任何字符出现任何位置区分大小写

       //检索以jet开头的词或句子

       SELECTcolumnOne,columnTwoFROM table WHERE columnOne LINK ' jet% ';

       //检索以jet结尾的词或句子

       SELECTcolumnOne,columnTwoFROM table WHERE columnOne LINK ' %jet ';

       //检索以jet包含的词或句子

       SELECTcolumnOne,columnTwoFROM table WHERE columnOne LINK ' %jet% ';

       //检索以e开头，以u结尾的词或句子

        //检索' e%u ';

   %还可以匹配0字符，注意尾空格会影响搜索模式的结果。

 

   _匹配单个字符

       SELECTcolumnOne,columnTwoFROM table WHERE columnOne LINK '_abc'

       //匹配aabc eabc  eabc 等前面一个字母的词

    让like区分大小写的方法

       在WHERE和列名之间加BINARY关键字，或者再建立表时就指定区分大小写name varhar(50)binary

   使用通配符是有代价的，提供以下的技巧：

       不要过度的使用通配符

       除非是必要的，否则通配符不要用在搜索模式的开始处

       仔细注意通配符的位置。不要放错位置

 

第九章 用正则表达式进行搜索

   仅支持正则表达式的一小部分

   基本字符匹配

   检索列prod_name包含1000的所有行

       SELECT columnOneFROM table WHERE columnOne REGEXP'1000'ORDERBY columnOne

   .  匹配任意一个字符1000 2000 3000  a000

       SELECT columnOneFROM table WHERE columnOne REGEXP'.000'ORDERBY columnOne

   正则匹配不区分大小写,如想区分匹配可在REGEXP后面加上BINARY关键字

   进行OR匹配

   为搜索两个或n个字符串之一

       SELECT columnOneFROM table WHERE columnOne REGEXP'1000|2000|3000' ;

   匹配单个字符

       SELECT columnOneFROM table WHERE columnOne REGEXP '[123] Ton'; //匹配1 Ton或2 Ton或3 Ton

当有非匹配的内容时使用[],它是|的另一种形式，如1|2|3 Ton这时匹配的只有3带有Ton

       如果想要得到非匹配的内容可以使用[^123]的形式

   匹配范围      [0-9] [a-z][A-Z]

       SELECT columnOneFROM table WHERE columnOne REGEXP '[1-5] Ton' ;

   匹配特殊字符

       想要匹配 . [ ] |这些字符串怎么办呢，可以在这些字符前加 \\进行转义,第一个\ mysql自己解释，第二个给正则解释的

       元字符        说明

       \\\         \

        \\f        换页

        \\n                换行

        \\r        回车

        \\t        制表

        \\v        纵向制表

 

   匹配字符串类

       自己经常使用的数字、所有字母或所有数字字母字符等的匹配。为了方便工作，可以使用预定义的字符集，称为字符集：

       类       说明

        [:alnum:]     任意字母和数字，同[0-9a-zA-Z]

        [:alpha:]任意字符，同[a-zA-Z]

        [:blank:]      空格和制表，同\\t

        [:cntrl:] ASCII控制字符，ASCII0到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个或多个匹配

       ?          0个或1个匹配

       {n}     指定数目匹配

       {n,}    不少于n个匹配

       {n,m}  匹配数目的范围m不超过255

       列：SELECT columnOneFROM table WHERE columnOne REGEXP '\\([0-9]sticks?)\\';

Sticks?匹配的是 stick或sticks（？号决定前面的s出现一次或0次）

       列：SELECT columnOneFROM table WHERE columnOne REGEXP'[[:digit:]]'ORDERBY columnOne;

[:digit:]匹配任意的数字，{4}要求前面匹配的数字出现4次

 

   定位符

       前面的所有例子都是匹配一个串中的任意位置的文本。为了匹配特定位置的文本

       元字符        说明

       ^         文本的开始

       $         文本的结束

       [[:<:]]  词的开始

       [[:>:]]  词的结束

       例如：你要找一个数（包括以小数点开始的数）开始的所有产品，怎么办，前面都是在行内任意位置匹配。所以不行

              SELECT columnOneFROM table WHERE columnOne REGEXP '^[0-9\\ . ]';

 

       简单的正则测试,可以不在数据库操作的情况下练习

       SELECT 'hello' REGEXP '[0-9]';

MySQL必知必会笔记（三）SELECT语句 计算字段 数据处理函数 汇总函数 分组数据 子查询

第十章  创建计算字段

    计算字段

       存储在表中的数据一般不是应用程序所需要的格式。我们需要直接从数据库中检索出转换、计算或格式化的数据。而不只是检索出数据，然后再到应用程序或报告程序中区格式化。

       这就发挥了计算字段的作用了。与前面的字段不同，计算字段并不实际存在于数据库中。计算字段是运行时在SELECT语句中创建的。

       需要注意的是，只有SELECT语句知道那些列是实际列，哪些列不是，客户机的角度来看，计算字段和其他字段是一样的。

   拼接字段

   拼接：将值联结到一起构成单个值。

       生成供应商  columnOne（columnTwo） 的格式

       SELECTConcat(columnOne, '(' ,columnTwo, ')')FROM table ORDERBY columnOne;

   使用别名

        别名使用AS关键字赋予

   执行算术运算

       另一常见的用途就是对检索出来的数据进行算术运算。

       例如：检索出column_id为2005的columnOne乘以columnTwo的值

       SELECT column_id,columnOne, columnTwo, columnOne*columnTwoAScolumn_priceFROM table WHERE column_id = 2005

   操作符     + - * /

        SELECT 3*2;将返回6

        SELECT Now();返回当前的日期和时间

 

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

    SQL实现了一下类型的函数

       1用于处理文本串，如删除、填充、装换大小写

       2用于数据上进行的算术操作，如返回绝对值，进行代数运算

       3用于处理日期和时间值并从这些值中提取特定的成分，如返回两个日期差，检查日期有效性

       4返回DBMS正使用的特殊信息，如用户登录信息，检查版本细节信息

   文本处理函数

       Upper() 将文本转换为大写

      SELECT vend_name,Upper(vend_name)AS vend_name_upcaseFROM vendors ORDERBY vend_name;

   常用的文本处理函数

       Left()         返回串左边的字符

       Length()            返回串的长度

       Locate()             找出串的一个子串

       Lower()             将串转换为小写

       Right()       返回右边的字符

       Soundex()   返回串的SOUNDEX值

       SubString() 返回串的字符

       Upper()      将串转换ewing大写

         Soundex()是一个将任何文字串转换为描述语音表示的字母数字模式的算法。他考虑了类似发信字符和音节，使得能对串进行发音的比较而不是字母比较，如：Y.lee搜索可以匹配Y.lie

      SELECT cust_name ,cust_contact FROM customersWHERESoundex(cust_contact) =Soundex('Y Lie')

   删除多余空格的函数

       RTrim()       LTrim() Trim() 依次是删除右边 左边  两边的空格

      SELECTConcat(RTrim(vend_name), '( ' ,RTrim(vend_country), ')FROM vendors ORDERBY vend_name;

 

   日期和时间处理函数

      日期和时间常用相应的数据类型和特色的格式存储，以便能快速和有效的排序或过滤，并节省物理存储空间。

       AddDate()   增加一个日期  天、周等 

       AddTime()  增加一个时间  时、分等

       CurDate()   返回当前日期

       CurTime()   返回当前时间

       Date()         返回日期时间的日期部分

       DateDiff()   计算两个日期之差    

       Date_Add() 高度灵活的日期或时间串 

       Date_Format()    返回一个格式的日期或时间串 

       Day()         返回一个日期的天数部分

       DayOfWeek()对于一个日期，返回对于星期几

       Hour()        返回一个时间的小时部分

       Minute()            返回一个时间的分钟部分

       Moth()        返回一个日期的月份部分

       Now()        返回当前的日期和时间

       Second()            返回一个时间的秒部分

       Time()        返回一个日期时间的时间部分

       Year()        返回一个日期的年份部分

       MySQl日期格式必须为yyyy-mm-dd,如2010-05-03。虽然其他日期格式也行，但这是首选格式，因为他排除了多义性。

      例如，存储的日期列中的日期是2010-11-05 15：23:05如果想找出2010-11-05这天的数据，使用前面的语句就不行了。这是要使用Date（）函数指示mysql提取列的日期的部分

      SELECT cust_id, order_numFROM orders WHEREDate(order_date) ='2010-11-05';

      检索出2005-5月份的订单怎么办呢？可以使用BETWEEN

      SELECT cust_id,order_numFROM orders WHEREDate(order_date)BETWEEN '2005-05-01' AND'2005-05-30';

      还有一种不需要记住天数的函数Month（），使年份相等，再让月份相等就行了

      SELECT cust_id,order_num FROM orders WHEREYear(order_date) = 2005ANDMonth(order_date) = 5;

 

   数值处理函数

       Abs()          返回一个数的绝对值

       Cos()          返回一个角度的余弦

       Exp()         返回一个数的指数值

       Mod()         返回除操作的余数

       Pi()                   返回圆周率

       Rand()        返回一个随机数

       Sin()          返回一个角度的正弦值

       Sqrt()         返回一个数的平方根

       Tan()          返回一角度的正切

 

第十二章 汇总函数

Msyql提供这些函数以便分析和报表生成，这种类型的检索例子有以下几种：

   汇聚函数 ：运行在行组上，计算和返回单个值的函数

      1确定表中的行数（或者满足某个条件或包括某个特定的值的行数）

      2获得表中行组的和

      3找出表列（或所有行或某特定行的）最大值、最小值和平均值

    AVG()            返回某列的平均值

    COUNT() 返回某列的行数

    MAX()            返回某列的最大值

    MIN()             返回某列的最小值

    SUM()            返回某列值之和

   求products表中产品的平均价格

      SELECTAVG(prod_price) AS avg_price FROM products;

   求products表中编号为1003产品的平均价格

      SELECTAVG(prod_price) AS avg_price FROM products WHERE vend_id = 1003;

   AVG()函数只能用来确定特定数值列的平均值，而且名必须作为函数参数给出。为了获得多个平均值，必须使用多个AVG()函数，AVG()函数忽略列值为NULL的行

   COUNT()函数的两种使用方式：

       1 COUNT(*)对表中的数目进行计数，不管表列中包含的是NULL值还是非空值

       2 COUNT(column)对特定的列中具有值的进行计数，忽略NULL值

      SELECT COUNT(*)AS num_custFROM customers;

      SELECT COUNT(cust_email)AS num_custFROM customers;

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

       SELECT MAX()  AS max_price FROM products;

   MIN() 与MAX()函数一样

   SUM()函数

      SELECTSUM(quantity)AS items_orderedFROM orderitemsWHERE order_num = 2005

   下面是mysql5以后的函数 在mysql4中不能正常运行

   DISTINCT 参数，作用是返回不相同的值

   下面是返回供应商提供的产品的平均值，它与上面的SELECT语句相同，但使用了DISTICT参数，因此平均值只考虑各个不同价格合起来的平均值

      SELECTAVG(DISTINCTprod_price) AS avg_price FROM products WHEREvend_id=10003；

   如果指定了列名，DISTINCT参数只能用于COUNT(),DISTINCT参数不能用于COUNT(*)应为DISTINCT必须使用列名。

第十三章 分组数据

   分组允许把数据分成多个逻辑组，比便能对每个组进行聚集计算。

   分组是在SELECT语句的 GROUP BY子句中建立的。

   返回每个厂商提供了几个产品

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

   GROUP BY一些重要规定

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

       2如果在GROUP BY        子句中嵌套了分组，数据将在最后规定的分组上进行汇总，换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别列取回数据）

       3 GROUP BY      子句列出的每个列都必须是检索列或有效的表达式，（但不能是聚集函数）如果。在SELECT中使用了表达式，则必须在GROUP BY子句中指定相同的表达式，不能使用别名。

       4除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子句中给出

       5如果分组列中具有NULL值，不管一个或多个都将作为一个分组返回

       6 GROUP BY子句必须出现在WHERE子句之后，GROUP BY子句之前

 

   过滤分组

      如想要得到大于3的不同产品订单

       WHERE是过滤指定的行而不是列。 HAVING支持所有 WHERE 操作符。

      SELECT cust_id,COUNT(*) AS orders FROM ordersGROUPBY cust_idHAVINGCOUNT(*) >３;

       WHERE是在分组前进行过滤， HAVING是在分组后进行过滤

   分组和排序区别

       ORDER BY                    GROUP BY

      排序产生的输出              分组行，单输出可能不是分组的顺序

      任意列都可以使用（甚至只可能使用选择列或表达式，而且必须使用每个选择列表达式

      是分选择列也可以使用）如果与聚集哈思楠一起使用列（或表达式）则必须使用

      不一定需要

   例子：检索总计订单价格大于50的订单号和总计订单

   SELECT order_num ,SUM(quantity*item_price) AS ordertotal     FROM orderitemsGROUP BY order_numHAVING SUM(quantity*item_price)>= 50;

   按订单价格进行排序,后面加 ORDER BYordertotal;

 

   SELECT子句顺序

      SELECT            要返回的列或表达式

      FROM        从中检索数据的表

      WHERE            行级过滤

      GROUNP BY     分组说明

      HAVING           组级过滤

      ORDER BY       输出排序顺序

      LIMIT        要检索的行数

第十四章 使用子查询

 

    Sql还允许使用子查询，即嵌套在其他查询中的查询。

   利用子查询进行过滤

      SELECT cust_idFROM orders WHERE order_num IN（

      SELECT order_numFROM orderitemsWHERE prod_id = 'TNT2'

      ）

      可以把一条SELECT语句返回的结果用于另一条SELECT语句的WHERE子句

      格式化sql   包含子查询的sql语句难以阅读，可以使用适当的缩进。

      得到了订购物品TNT2的所有客户ID，下一步是检索这些客户的信息,总语句是

      SELECT cust_name ,cust_contact FROM  customersWHERE cust_id IN(

   SELECT cust_idFROM orders WHERE order_num IN  (

   SELECT order_numFROM orderitemsWHERE prod_id = 'TNT2'

    )

       )

      列必须匹配在WHERE子句中使用子查询，应该保证SELECT语句具有WHERE子句中相同数目的列。通常子查询将返回的单个列于单个列匹配，但如果需要也可以多个列。

      虽然子查询一般与IN操作符结婚使用，但也可以用于测试等于、不等于等符号

 

   作为计算字段使用子查询

      使用子查询的另一个方法是创建计算字段。

      假设需要显示sustomers表中每个客户的订单总数。

      SELECT cust_name，cust_state,(

      SELECTCOUNT(*)FROM orders WHERE orders,coust_id =customers . Cust_id

       )AS orders FROM customers ORDERBY cust_name;

MySQL必知必会笔记（四）SELECT语句 联结表 高级联结 组合查询 全文本搜索

第十五章 联结表

    Sql最强大的功能之一就是能在数据检索查询的执行中联结（join）表。联结是利用sql的select能执行的最重要的操作，能很好的理解联结及其语法是学习sql的一个极为重要的组成部分。

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

       好处：供应商信息不重复，不浪费空间和时间，方便日后修改，一个表信息改动不影响另一个表的信息

       联结是一种机制，使用特殊的语法，可以联结多个表返回一组输出，联结在运行时关联表中正确的行。

   创建联结

           SELECTvend_name , prod_name , prod_price

           FROMvendors , products

           WHEREvendors.vend_id = products . Vend_id

           ORDER BYvend_name , prod_name; ;

       如果没有WHERE子句，表一中的每行将于表二中的每行配对，而不管他们逻辑是否可以配在一起。其配对的结果是笛卡尔积。就是表一行数*表二行数

 

   内部联结

       前面讲的联结称为等值联结，它基于两个表之间的相等测试。这种联结也称为内部联结。前其实这种联结可以使用稍微不同的语法来明确指定联结的类型。下面是语句与上面返回一样

           SELECTvend_name ,prod_name , prod_price

          FROMvendors

           INNER JOIN productsON vendors . vend_id = products . vend_id;

       这里的两个表之间的关系是由FROM子句组成的。以 INNER JION指定。使用这种语法时。联结子句的条件用特定的ON子句而不是WHERE子句给出。传递给ON的跟传递给WHERE的子句相同。

       首选使用INNER JOIN语法，这样不会忘记联结的条件，虽然WHERE子句定义联结比较简单。

   联结多个表

           SELECTprod_name ,vend_name ,prod_price , quantity

           FROMorderitems , products , vendors

           WHEREproducts . Vend_id = vendors.vend_id

           AND orderitems.prod_id =products.prod_id

           ANDorder_num = 20005;

   考虑到多个表联结时会耗费资源，所以尽量不要联结不必要的表。

第十六章 创建高级联结

   使用别名

           SELECT Concat(RTrim(vend_name) , '(' ,RTrim(vend_country) , ')')AS vend_title

           FROM vendorsORDER BY vend_name；

   别名除了用于列名和计算字段外，sql还允许给表名起别名。一可以缩短sql语句，二允许在单条SELECT语句中多次使用相同的表

           SELECTcust_name ,cust_contact

           FROMcustomersAS c , ordersAS o , orderitemsAS oi

           WHEREc.cust_id =o.cust_id ANDoi.order_num =o.order_num AND prod_id = 'TN2'

       表的别名和列的别名不一样，表的别名不返回给客户端

 

       使用不同类型的联结

       前面为止我们使用的只是称为内部联结或等值联结的简单联结。现在来看其他3中联结。分别是自联结、自然联结和外部联结

   自联结

       使用表别名的主要原因之一是能在单条SELECT语句中不止一次引用相同的表。举个例子：

       如果发现某物品（id为abc）存在问题，因此想知道生产该物品的生产商生产的其他物品是否也存在问题。此程序要求首先找到生产id为abc的物品的生产商，然后找出这个生产商生产的其他的物品。下面一种解决方法：

           SELECTprod_id , prod_nameFROM productsWHERE vend_id = (

           SELECTvend_idFROM products WHERE prod_id = 'abc'

       );

       上面使用的子查询，现在看使用联结的相同查询：

           SELECTp1.prod_id ,prod_name

           FROMproductsASp1 ,productsASp2

           WHERE p1.vend_id =p2.vend_idAND p2.prod_id ='abc';

       此联结查询需要的两个表实际上同一个表，

       用自联结不用子查询 子联结通常作为外部语句用来代替从相同表中检索数据时使用的子查询语句，虽然结果一样，但是处理联结远不处理子查询要快的多。

   自然联结

       SELECT *  改为 SELECT表名1.列名1 ,表名2.列名3 这样可以去除无用的列。执行效率更快

 

   外部联结

       许多联结将一个表中的行与另一个表中的行相关联。但有时候会需要包含没有关联行的那些行。

       例如完成需要使用联结完成以下任务：

           1对每个用户下了多少订单进行计数，包括那些至今尚未下订单的客户

           2  列出所有产品以及订购数量，包括那些没有人订购的产品

           3  计数平均销售规模，包括那些至今未下订单的客户

       上述例子中，联结包含了那些在相关表中没有关联的行，这种联结类型的联结称为外部联结。

       下面是SELECT语句给出一个简单的内部联结。它检索出了所有客户以及订单:

           SELECTcustomers.cust_id , orders.order_numFROM customers

           INNER JOIN ordersON customers.cust_id = orders.cust_id;

       外部联结语法类似。为了检索所有用户，包含那些没有订单的客户。可如下进行：

           SELECTcustomers.cust_id , orders.order_numFROM customers

           LEFT OUTER JOIN ordersON customers.cust_id = order_cust_id;

       这条sql语句使用了关键字OUTER JOIN来指定联结的类型(而不是WHERE指定)。但是与内部联结关联两个表中的行不同的是，外部联结还包括没有关联的行。

       使用OUTER JOIN必须使用LEFT或 RIGHT关键字指定包括其所有含的表。

 

   使用带聚集函数的联结

       聚集函数是用来汇总数据。它可以中单个表中汇总数据，也可以在联结中一起使用。

       例子：检索所有客户及每个客户所下的订单数。。下面使用了COUNT()函数的代码完成。

           SELECTcustomer.cust_name, customer . cust_id ,COUNT(order.order_num) AS num_ord

           FROMcustomers

           INNER JOIJNordersON customers.cust_id = orders.cust_id

           GROUP BY customers.cust;

       GROUP BY子句按客户分组数据。因此，函数调用COUNT(orders.order_num)对每个客户的订单计数，将他作为num_ord返回

       聚集函数也可以方便地与其他联结一起使用

           SELECTcustomers.cust_name, customers.cust_id ,COUNT(order.order_num) AS num_ord

           FROM customers

           LEFT OUTER JOIN ordersON customers.cust_id = order.cust_id

           GROUP BY customers.cust_id;

   使用联结和联结条件

       1注意所使用的联结类型，一般我们使用内部联结，但使用外部联结也是有效的。

       2保证使用正确的联结条件，否则将返回不正确的数据；

       3应该始终提供联结条件，否则会得出笛卡尔积

       4在一个联结中可以包含多个表，甚至对于每个联结可以采用不同的联结类型。虽然这样做是合法的，一般也很有用，但应该在测试它们之前，分别测试每个联结。这将使故障排除更为简单。

 

第十七章 组合查询

   前面的都是从一个或多个表中返回数据的单条SELECT语句。MYSQL也允许执行多个查询（多条SELECT语句），并将结果作为单条查询结果集返回。这些组合查询通常称为并union或复合查询

   有两种情况需要使用组合查询

       1在单个查询中从不同的表返回累世结构的数据

       2对单个表执行多个查询，按单个查询返回数据

   组合查询和多个WHERE条件多数情况下，组合相同表的两个查询完成的工作与具有多个WHERE子句完成的工作相同，换句话说，任何具有多个WHERE子句的SELECT语句都可以作为一个组合查询给出。

   创建组合查询

       可用UNION操作符来组合数条SQL查询。给出多条SELECT语句，将它们的结合组合成单个结果返回

   使用UNION

       使用很简单，在多条SELECT语句之间放上关键词UNION。

       例如：需要价格不大于5元，但是又要包含供应商1002和1001的所有产品，（不考虑价格）

           SELECTvend_id，prod_id , prod_priceFROMproducts WHERE prod_price <= 5

           UNION

           SELECTvend_id，prod_id , prod_priceFROMproducts WHERE vend_id IN(1001,1002)

       OR语句也能实现上面的查询

           SELECTvend_id，prod_id , prod_priceFROMproducts

           WHERE prod_price <= 5OR vend_id IN (1001,1002)

从上面这个简单的例子中，使用UNION可能比使用WHERE子句更为复杂。但对于更复杂的过滤条件，或者从多个表而不是单个表中检索数据的情形，使用UNION肯会使处理更简单

   UNION规则

       正如所见，并是非常容易使用的，但在进行并时有几条规则需要注意

       1 UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔

       2 UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过各列不需要以相同的次序列出）

       3列数据类型必须兼容：类型不完全相同，但不想是DBMS可以隐含滴转换的类型。

   包含或取消重复的行

       如果SELECT语句中返回的行相同，UNION会自动的去除重复的行。这是他的默认行为，但是如果需要，可以改变它，可以使用UNION ALL而不是UNION

           SELECTvend_id，prod_id , prod_priceFROMproducts WHERE prod_price <= 5

           UNION ALL

           SELECTvend_id，prod_id , prod_priceFROMproducts WHERE vend_id IN (1001,1002)

   对组合查询结果排序

       SELECT语句的输出用ORDER BY子句排序。在UNION组合查询时，只能使用一条ORDER BY子句，它必须出现在最后一条SELECT语句之后。

           SELECTvend_id，prod_id , prod_priceFROMproducts WHERE prod_price <= 5

           UNION ALL

           SELECTvend_id，prod_id , prod_priceFROMproducts WHERE vend_id IN (1001,1002)

           GROUP BYvend_id，prod_price;

第十八章 全文本搜索

   理解全文本搜索

       并非所有的引擎都支持全文本搜索。最常使用的是MyISAM和InnoDB，前者支持全文本搜索，而后者不支持。如果你需要全文本搜索功能，应该记住这一点

 

       关键字LIKE，它利用通配操作匹配的文本（和部分文本）。使用LIKE，能够查找包含特殊值或部分值的行（不管这些值位于那些位置）

       基于文本的搜索作为正则表达式匹配列值的更进一步的介绍。使用正则表达式，可以编写查找所有行的非常复杂的匹配模式

       虽然这些搜索机制非常有用。但存在几个重要的限制：

           1性能——通配符和正则表达式匹配通常要求MYSQL尝试匹配表中所有的行（而且这些搜索极少使用表索引），因此，由于被搜索行数不断增加，这些搜索非常耗时

           2明确控制——使用通配符和正则表达式匹配，很难（而且并不总能）明确的控制匹配什么和不匹配什么

           3智能化的结果——虽然前两者都能提供了非常灵活的搜索，但它们都不能提供非常智能化的匹配结果。

       前面的很多限制都可以使用全文本搜索来解决。使用全文本搜索。Mysql创建指定列中各词的一个索引， 搜索可以针对这些词进行。

 

   使用全文本搜索

       启用全文本搜索

       一般在创建表时启用全文本搜索。CREATE TABLE语句接受FULLTEXT子句。它给出被索引列的一个逗号分隔的列表。

       CREATE TABLEproductontes

       (

    note_idintNOT_NULLAUTO_INCREMENT,

    prod_id char(10) NOT_NULL,

    note_datedatetimeNOT_NULL,

    note_text text NOT_NULL,

    PRIMARY KEY(note_id),

    FULLTEXT(note_text)

        )ENGINE=MyISAM;

      为了进行全文本搜索，mysql根据子句FULLTEXT(note_text)的指示对它进行索引。这里FUNLL_TEXT索引了单个列，如果需要可以索引多个列，多列之间用逗号分隔

       在定义之后mysql对自动维护该索引，在增加、更新和删除行时，索引随之自动更新。

       不要在导入数据时使用FUNLLTEXT,否则会耗费很多时间，应该先导入数据，再定义FULLTEXT这样有助于更快的导入数据

 

   进行全文本搜索

       在索引之后，使用两个函数Match()和Against()执行全文本搜索，其中Match()指定被搜索的列Against()指定要使用的搜索表达式。

           SELECTnote_textFROM productnotesWHEREMatch(note_text) Against('rabbit');

       使用完整的Match()说明：传递给Match()的值必须和与FULLTEXT()定义中的相同。如果指定了多个列，则必须列出它们（而且次序正确）

       除非使用BINARY方式，否则全文本搜索不区分大小写

 

       使用SELECT语句同样可以检索出两行，但次序不同。

       全文本搜索返回一文本匹配的良好程度的数据。具有较高等级的行先返回。

 

   使用查询扩展

       查询扩展用来设法放宽所返回的全文本搜索结果的范围。想找出与搜索有关的其他行。不包含要搜索才字母。

           如SELECT note_textFROM productnotesWHERE Match(note_text)Against('anvils');

           返回一行数据

       使用查询扩展

           SELECTnote_textFROM productnotes

           WHERE Match(note_text)Against('anvils'WITH QUERY EXPANSION);

           返回7行

 

   布尔文本搜索

       Mysql支持全文本搜索的另一种形式，称为布尔方式。布尔方式可以提供以下细节：

           1要匹配的词

           2要排斥的词（如果该行包含这个词，则不返回该行，即使它已经包含指定的词也是如此）

           3排列提示（指定某些词比其他词重要，更重要的词的词等级更高）

           4表达式分组

           5另外一些内容

       即使没有FULLTEXT索引也可以使用 布尔方式不同迄今为止使用的全文本搜索语法的地方在于，即使没有定义FULLTEXT索引，也可以使用它，但这是一种非常缓慢的操作（其性能将随着数据量的增加而降低）

       例如：匹配包含heavy但不包含任意以rope开始的词的行，可以使用以下查询：

           SELECTnote_textFROM productnotes

           WHERE Match（note_text）Against（'heavy -rope*' INBOOLEAN MODE）

       全文本布尔操作符

           +包含，词必须存在

           -排除，词必须不出现

           >包含，而且增加等级值

           <包含，而且减少等级值

           ()吧词组成子表达式（允许这些子表达式作为一个组被包含）

           ~取消一个词的排序值

           *词尾的通配符

           ""定义一个短语，（与单词不一样，它匹配整个短语以便包含或排除这个短语）

           SELECTnote_textFROM productnotes

           WHERE Match（note_text）Against('+hea +rop' IN BOOLEAN MODE）

           //包含hea和rop的行

           SELECTnote_textFROM productnotes

           WHERE Match（note_text）Against('hea  rop' IN BOOLEAN MODE）

           //包含hea和rop至少一个就行的行

           SELECTnote_textFROM productnotes

           WHERE Match（note_text）Against('"hea rop"' IN BOOLEAN MODE）

           //包含hea rop这个短语而不是hea和rop的行

           SELECTnote_textFROM productnotes

           WHERE Match（note_text）Against('>hea <rop' IN BOOLEAN MODE）

           //包含hea和rop的行,增加前者的等级，降低后者的等级

           SELECTnote_textFROM productnotes

           WHERE Match（note_text）Against('+hea +(<rop)' IN BOOLEAN MODE）

           //包含hea和rop的行降低后者的等级

 

   全文本搜索使用说明：

       1在索引全文本数据时，短语被忽略且从索引中排除，短语定义为那些具有3个或3个以下字符的词，如果需要，这个数目可以更改

       2 mysql内建一个非用词（stopword）列表，这些词在索引全文本数据时总是被忽略，如果需要，可以覆盖这个列表

       3许多词出现的频率高，搜索它们没有用处（返回太多的结果），因此mysql规定了一条50%的规则，如果一个词出现在50%以上的行中，则它将作为一个非用词忽略，50%规则不适用与IN BOOLEAN MODE

       4如果表中的行数少于3行，则全文本搜索不返回结果（因为每个词或者不出现，或者少于出现在50%的行中）

       5忽略词中的单引号，例如 don't索引为 dont

       6不具有词分隔符(包含日语和汉语)的语言不能恰当地返回全文本搜索结果

       7如前所述，仅在MyISAM数据库引擎中是支持全文本搜索的

MySQL必知必会笔记（五）插入数据 创建和操纵表

第十九章 插入数据

 

INSERT是用来插入（或添加）行到数据库表的。插入数据的几种方式：

1  插入完整的行

2 插入行的一部分

3  插入多行

4 插入某些查询结果

INSERTSELECT语句

INSERT INTOcustomers(cust_id,cust_name,cust_email)

SELECTcust_id,cust_name,cust_emailFROM custnew;

列省略

    如果表中定义允许，则可以在INSERT操作中省略某些列，省略的列必须满足以下某个条件。

1 该列定义为允许为NULL

2 在表定义中给出默认值，

 

提高整体性能

    数据库经常多个客户访问，对处理什么请求以及用什么次序处理进行管理mysql的任务。INSERT操作可能很耗时（特别是由很多索引需要更新时），而且它可能降低等待处理的SELECT语句性能。

    如果数据检索是最重要的（通常是这样的），则你可以通过在INSERT和INTO之间添加关键字LOW_PRIORITY 指示mysql降低INSERT语句的优先集

 第二十章 更新和删除数据

 

更新和删除数据

 

    为了更新（修该）表中的数据，可使用UPDATE语句，可采用两种方式使用UPDATE

1 更新表中特定的行

2 更新表中所有的行

更新行和删除行的时候一定要加上WHERE子句，否则后果自负

UPDATE bname SET zda ='acontent' , zdb = 'bcontent' WHEREzdid='20005';

    IGNORE 关键字   如果使用UPDATE更新多行，并且在更新这些行中的一行或多行时出现一个错误，则整个UPDATE操作被取消，（错误发生前更新的所有行被恢复到他们原来的值）为即使发生错误也继续进行更新。可使用IGNORE关键字

UPDATE  IGNORE bname;

 

删除数据

为了从表中删除(去掉)数据，使用DELETE语句，可以使用两种方式：

        1 从表中删除特定的行

2 从表中删除所有的行

    不要省略WHERE子句

 

    更快的删除  如果想从表中删除所有的行，不要使用DELETE，可以使用TRUNCATE TABLE语句，他完成相同的工作，但速度更快（TRUNCATE实际上是删除原来的表并重新创建一个表，而不是逐行去删除表中的数据）

 

更新和删除的指导原则

下面是许多sql程序员使用的UPDATE或DELETE是所遵循的习惯

        1 除非确实打算更新和删除每一行，否则绝对不要使用不带WHERE子句的UPDATE或DELETE语句。

        2 保证每个表都有主键，尽可能的像WHERE子句那样使用它(可以指定各主键、多个值、和值的范围)

        3 在对UPDATE和DELETE语句操作使用WHERE前，应该先用SELECT进行测试，保证它过滤的数据是正确的记录，以防编写的WHERE子句不正确。

        4 使用强制实施引用完整的数据库，这样MYSQL将不允许删除具有与其他表相关联的数据的行

 

 

第二十一章  创建和操纵表

创建表

一般两种创建表的方法

1 使用具有交互式创建和管理表的工具

2 表也可以直接用MySQL语句操纵

在使用交互式工具时，工具也是生成MySQL语句操纵数据库

表创建基础

为了利用CREATE TABLE创建表，必须给出下列信息：

1 新表的名字，在关键字CREATE TABLE之后给出

2 表列的名字和定义，用逗号分隔

CREATE TABLE table

(

column_id  int      NOT NULL AUTO_INCRMENT,

columnOne  char(20) NOT NULL DEFAULT 1,

columnOne  char(20) NOT NULL,

column  char(20) NOT NULL ,

.....

PRIMARY  KEY ( column_id)

) ENGINE =  InnoDB

    如果表已经存在，则必须先删除后在创建他，不可以直接覆盖。如果仅想在表没有存在的情况下创建它，应该在表名后给出IFNOT EXISTS

使用NULL值 如果不指定列为NOT NULL则它默认是NULL

主键再介绍 主键的值必须是唯一的。创建主键时可以用单列做主键PRIMARY KEY(columnOne,columnTwo)，也可以使用多列做主键PRIMARY KEY(columnOne,columnTwo).主键值不允许为NULL

索引创建

   CREATE INDEX indexnameON tablename(column [ASC|DESC],....);

使用AUTO_INCREMENT

    每个表只允许使用一个AUTO_INCREMENT列，而且它必须被索引，（如通过使它为主键）

如果两个表关联，添加时一表时需要另一表的主键，该怎么获得呢：

    可以使用last_insert_id()函数获得这个值 此语句返回最后一个AUTO_CREMENT的值。

指定默认值

    如果在插入行时没有给出值，mysql允许指定此时使用默认值。默认值是在CREATETABLE语句的列定义中的DEFAULT关键字定义的。

        columnOne  char(20) NOT NULL  DEFAULT1,

MySQL不允许使用函数作为默认值，它只支持常量

引擎搜索

    你可能已经注意到，迄今为止使用的CREATE  TABLE语句全都以ENGINE = InnoDB语句结束

    与其他的DBMS一样，MySQL有一个具体管理和处理数据的内部引擎，在你使用CREATETABLE语句是，该引擎具体创建表，在其他应用中区修改读取删除表等处理你的请求，多数时候它隐藏在DBMS内，不需要过多的关注它。

    但MySQL与其他的DBMS不一样，它具有多种引擎，它打包多个引擎，这些引擎都隐藏在MySQL服务器内，全都能处理用户的请求。

    为什么要发行多种引擎呢，因为他们具有各自不同的功能和特性，为了不同的任务选择正确的引擎能获得良好的功能和灵活性

    当然你也可以省略这些数据库引擎，如果省略ENGINE=语句，则使用默认引擎(很可能为MyISAM),多数sql语句都会默认使用它，但并不是所有的语句都默认使用它，这就是为什么ENGINE=语句很重要的原因。

以下几个需要知道的引擎

1 InnoDB是一个可靠的事务处理引擎，它不支持全文本搜索。

    2 MEMORY 在功能上等同于MySAM，但由于数据存储在内存，所以速度更快（特别适用于临时表）

    3 MySAM是一个性能极高的引擎，它支持全文本搜索，但不支持事务处理

        引擎类型可以混用。混用引擎类型的一个大缺陷。外键不能跨引擎，即使用一个引擎的表不能引用具有使用不同引擎的表的外键

更新表

        为了更新表，可以使用ALTER TABLE语句，但是在理想状态下，当表中存储了数据后就不应该再去更新表了。所以在设计表时需要花费大量的时间去考虑，以便后期不做太大的改动。

    ALERT TABLE更改表结构，必须给出下面的信息：

    1 在ALERT  TABLE之后给出要修改的表名

    2 所做更改的列表

    添加一列ALERT TABLE columnADD columnFour CHAR(90);必须指明数据类型

    删除一列ALERT TABLE column DORPcolumnFour ;

        定义外键   

            ALERT TABLE tableOneADD CONSTRAINTcolumnOne

           FOREIGN KEY (column_id)PEFERENCES tableTwo(column_id)

        可以单条ALTER TABLE语句对单个表进行多个修改，每个修改用逗号分隔开。

    复杂的表结构一般需要手动删除过程

        1 用新的列布局创建一个新表

        2 使用INSERT SELECT 语句从旧表复制数据当道新表。如果有必要，可以使用转换函数和计算字段

        3 检验包含所需数据的新表

        4 重名名旧表

        5 用旧表原来的名字重命名新表

        6 根据需要，重新创建触发器、存储过程、索引和外键

        小心使用ALTER TABLE 因为它是不可逆的操作，最好操作前完成已备份。

    删除表

       DORP TABLE table；   不可撤销，永久删除表

    重命名表

       RENAME TABLE table TO table2；  多个表重命名 逗号分隔开

 

 

CREATE DATABSE [IF NOT EXISTS] dataname;

DORP DATABSES [IF EXISTS] databses;

 

在整型数据列后加上 UNSIGNED属性可禁止负数，取值从0开始。范围扩大一倍-125---125  0-250

ZEROFILL  数据字段属性，在数值之前自动用0补足不足的位数，声明一个int（3）ZEROFILL插入5返回005

CREATE TABLE tablename(

Id int(5) UNSIGNED.......

Num ind(3) ZEROFILL....

Sex  int(1) DEFAULT 0 ......

Namevarchar(10) NOT NULL......

)

Mysql默认字符集设置：win my.ini    linux   /etc/my.cnf

Character-set-server =gbk;

Collation-server=gbk_chinese_ci;

创建数据库时设置字符集语句：

CREATE DATABASE IF NOTmydb DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

数据库文件结构

一个MyISAM数据表会有三个文件，以.frm为后缀的结构定义文件，以.MYD为后缀名的数据文件，一个以.MYI为后缀名的索引文件

一个InnoDB数据表只有一个文件

CREATE TABLE t (i INT) ENGINE =INNODB;

CREATE TABLE t (i INT) TYPE = MYISAM;

 

Mysql_db_query(''sql........,$linke) 当文件连接两个数据库时指定在哪个数据库上执行

当query函数出错时可以用mysql_errno()【错误号】和mysql_error()函数来确定

当query函数执行后可以使用mysql_affected_rows()来查看他们到底修改了多少行

还可以mysql_insert_id()查看最后插入的自增字段（id）值

查询结果集处理

$result =mysql_query(select * from books);

$rows=mysql_num_rows($result);   显示结果的行数

$cols =mysql_num_fields($result);显示结果的列数

 

Mysql_fetch_row();将一条结果记录返回并以一个普通索引数组的形式保存

Mysql_fetch_assoc();将一条结果记录返回并以一个普通关联数组的形式保存

Mysql_fetch_array();将一条结果记录返回一个关联数组或索引数组，或同同时获得索引关联数组，通过传递MYSQL_ASSOC、MYSQL_NUMMYSQL_BOTH中的一个常量返回不同的数组形态。默认使用MYSQL_BOTH常量

Mysql_fetch_object();  以一个对象的形式返回一条结果记录，它的各个记录需要以对象的方式进行访问。

获取列数信息（数据类型，长度，索引） mysql_fetch_fields()

php默认把结果集一直保存到php脚本执行结束为止，如果想提前释放结果集，使用mysql_free_result()函数。

 

MySQL必知必会笔记（六）存储过程 游标  触发器

第二十三章 使用存储过程

   MySQL5 中添加了存储过程的支持。

   大多数SQL语句都是针对一个或多个表的单条语句。并非所有的操作都怎么简单。经常会有一个完整的操作需要多条才能完成

   存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批文件。虽然他们的作用不仅限于批处理。

   为什么要使用存储过程：优点

       1通过吧处理封装在容易使用的单元中，简化复杂的操作

       2由于不要求反复建立一系列处理步骤，这保证了数据的完整性。如果开发人员和应用程序都使用了同一存储过程，则所使用的代码是相同的。还有就是防止错误，需要执行的步骤越多，出错的可能性越大。防止错误保证了数据的一致性。

       3简化对变动的管理。如果表名、列名或业务逻辑有变化。只需要更改存储过程的代码，使用它的人员不会改自己的代码了都。

       4提高性能，因为使用存储过程比使用单条SQL语句要快

       5存在一些职能用在单个请求中的MySQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码

      换句话说3个主要好处简单、安全、高性能

   缺点

       1一般来说，存储过程的编写要比基本的SQL语句复杂，编写存储过程需要更高的技能，更丰富的经验。

       2你可能没有创建存储过程的安全访问权限。许多数据库管理员限制存储过程的创建，允许用户使用存储过程，但不允许创建存储过程

   存储过程是非常有用的，应该尽可能的使用它们

   执行存储过程

       MySQL称存储过程的执行为调用，因此MySQL执行存储过程的语句为CALL      .CALL接受存储过程的名字以及需要传递给它的任意参数

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

         //执行名为productpricing的存储过程，它计算并返回产品的最低、最高和平均价格

   创建存储过程

      CREATE  PROCEDURE存储过程名()

         一个例子说明：一个返回产品平均价格的存储过程如下代码：

        CREATE  PROCEDURE  productpricing()

          BEGIN

         SELECT Avg(prod_price) AS priceaverage

         FROM products;

         END;

      //创建存储过程名为productpricing，如果存储过程需要接受参数，可以在（）中列举出来。即使没有参数后面仍然要跟（）。BEGIN和END语句用来限定存储过程体，过程体本身是个简单的SELECT语句

      在MYSQL处理这段代码时会创建一个新的存储过程productpricing。没有返回数据。因为这段代码时创建而不是使用存储过程。

 

   Mysql命令行客户机的分隔符

      默认的MySQL语句分隔符为分号 ; 。Mysql命令行实用程序也是 ;作为语句分隔符。如果命令行实用程序要解释存储过程自身的 ;字符，则他们最终不会成为存储过程的成分，这会使存储过程中的SQL出现句法错误

      解决方法是临时更改命令实用程序的语句分隔符

         DELIMITER //   //定义新的语句分隔符为//

         CREATE PROCEDURE productpricing()

         BEGIN

         SELECT Avg(prod_price) AS priceaverage

         FROM products;

         END//

         DELIMITER ;   //改回原来的语句分隔符为;

         除\符号外，任何字符都可以作为语句分隔符

       CALLproductpricing();  //使用productpricing存储过程

      执行刚创建的存储过程并显示返回的结果。因为存储过程实际上是一种函数，所以存储过程名后面要有()符号

   删除存储过程

      DROP PROCEDURE productpricing ;    //删除存储过程后面不需要跟(),只给出存储过程名

      为了删除存储过程不存在时删除产生错误，可以判断仅存储过程存在时删除

       DROP PROCEDURE IFEXISTS

      使用参数

       Productpricing只是一个简单的存储过程，他简单地显示SELECT语句的结果。

      一般存储过程并不显示结果，而是把结果返回给你指定的变量

         CREATE PROCEDURE productpricing(

         OUT p1DECIMAL(8,2),

         OUT phDECIMAL(8,2),

         OUT paDECIMAL(8,2),

          )

         BEGIN

         SELECT Min(prod_price)

         INTO p1

         FROM products;

         SELECT Max(prod_price)

         INTO ph

         FROM products;

         SELECTAvg(prod_price)

         INTO pa

          FROM products;

         END;

         此存储过程接受3个参数，p1存储产品最低价格，ph存储产品最高价格，pa存储产品平均价格。每个参数必须指定类型，这里使用十进制值。关键字OUT指出相应的参数用来从存储过程传给一个值（返回给调用者）。MySQL支持IN（传递给存储过程）、OUT（从存储过程中传出、如这里所用）和INOUT（对存储过程传入和传出）类型的参数。存储过程的代码位于BEGIN和END语句内，如前所见，它们是一些列SELECT语句，用来检索值，然后保存到相应的变量（通过INTO关键字）

      调用修改过的存储过程必须指定3个变量名：

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

      这条CALL语句给出3个参数，它们是存储过程将保存结果的3个变量的名字

   变量名 所有的MySQL变量都必须以@开始

   使用变量

       SELECT@priceaverage ;

       SELECT @pricelow ,@pricehigh , @priceaverage ;  //获得3给变量的值

      下面是另一个例子，这次使用IN和OUT参数。ordertotal接受订单号，并返回该订单的合计

          CREATE PROCEDURE ordertotal(

         IN onumber INT,

         OUT ototalDECIMAL(8,2)

          )

         BEGIN

         SELECT Sum(item_price*quantity)

         FROM orderitems

         WHERE order_num =onumber

         INTO ototal;

         END;

         //onumber定义为IN，因为订单号时被传入存储过程，ototal定义为OUT，因为要从存储过程中返回合计，SELECT语句使用这两个参数，WHERE子句使用onumber选择正确的行，INTO使用ototal存储计算出来的合计

   为了调用这个新的过程，可以使用下列语句：

      CALL ordertotal(2005 ,@total);  //这样查询其他的订单总计可直接改变订单号即可

      SELECT @total;

   建立智能的存储过程

      上面的存储过程基本都是封装MySQL简单的SELECT语句，但存储过程的威力在它包含业务逻辑和智能处理时才显示出来

      例如：你需要和以前一样的订单合计，但需要对合计增加营业税，不活只针对某些顾客（或许是你所在区的顾客）。那么需要做下面的事情：

          1获得合计（与以前一样）

          2吧营业税有条件地添加到合计

          3返回合计（带或不带税）

      存储过程的完整工作如下：

         -- Name: ordertotal

         -- Parameters: onumber=订单号

         --           taxable = 1为有营业税0为没有

         --           ototal =合计

         CREATE  PROCEDURE ordertotal(

         IN onumber INT,

         IN taxable BOOLEAN,

         OUT ototalDECIMAL(8,2)

         -- COMMENT()中的内容将在SHOW PROCEDURE STATUSordertotal()中显示，其备注作用

          )COMMENT 'Obtain ordertotal , optionally adding tax'

         BEGIN

         --定义total局部变量

         DECLARE total DECIMAL(8,2)

         DECLARE taxrate INTDEFAULT 6;

 

         --获得订单的合计，并将结果存储到局部变量total中

         SELECT Sum(item_price*quantity)

         FROM orderitems

         WHERE order_num =onumber

         INTO total;

 

         --判断是否需要增加营业税，如为真，这增加6%的营业税

         IF taxableTHEN

         SELECTtotal+(total/100*taxrate)INTO total;

                 END IF;

         --把局部变量total中才合计传给ototal中

         SELECT totalINTO ototal;

         END;

         此存储过程有很大的变动，首先，增加了注释（前面放置--）。在存储过程复杂性增加时，这样很重要。在存储体中，用DECLARE语句定义了两个局部变量。DECLARE要求制定变量名和数据类型，它也支持可选的默认值（这个例子中taxrate的默认设置为6%），SELECT语句已经改变，因此其结果存储到total局部变量中而不是ototal。IF语句检查taxable是否为真，如果为真，则用另一SELECT语句增加营业税到局部变量total，最后用另一SELECT语句将total（增加了或没有增加的）保存到ototal中。

   COMMENT关键字 本列中的存储过程在CREATE PROCEDURE语句中包含了一个COMMENT值，他不是必需的，但如果给出，将在SHOW PROCEDURESTATUS的结果中显示

   IF语句  这个例子中给出了MySQL的IF语句的基本用法。IF语句还支持ELSEIF和ELSE子句（前者还使用THEN子句，后者不使用）

   检查存储过程

      为显示用来创建一个存储过程的CREATE语句，使用SHOW CREATEPROCEDURE语句

         SHOW CREATE PROCEDURE ordertotal;

      为了获得包括何时、有谁创建等详细信息的存储过程列表。使用SHOW PROCEDURE STATUS.限制过程状态结果，为了限制其输出，可以使用LIKE指定一个过滤模式，例如：SHOW PROCEDURESTATUS LIKE ''ordertotal;

第二十四章   使用游标

    MySQL5添加了对游标的支持

   只能用于存储过程

   由前几章可知，mysql检索操作返回一组称为结果集的行。都与mysql语句匹配的行（0行或多行），使用简单的SELECT语句，没有办法得到第一行、下一行或前10行，也不存在每次行地处理所有行的简单方法（相对于成批处理他们）

   有时，需要在检索出来的行中前进或后退一行或多行。这就是使用游标的原因。游标（cursor）是一个存储在MYSQL服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。

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

   使用游标

      使用游标涉及几个明确的步骤：

          1在能够使用游标前，必须声明（定义）它，这个过程实际上没有检索数据，它只是定义要使用的SELECT语句

          2一旦声明后，必须打开游标以供使用。这个过程用钱吗定义的SELECT语句吧数据实际检索出来

          3对于填有数据的游标，根据需要取出（检索）的各行

          4在接受游标使用时，必须关闭它如果不明确关闭游标，MySQL将会在到达END语句时自动关闭它

   创建游标

      游标可用DECLARE语句创建。 DECLARE命名游标，并定义相应的SELECT语句。根据需要选择带有WHERE和其他子句。如：下面第一名为ordernumbers的游标，使用了检索所有订单的SELECT语句

         CREATE PROCEDURE processorders()

         BEGIN

         DECLARE ordernumbersCURSOR

         FOR

         SELECT order_numFROM orders ;

         END;

         存储过程处理完成后，游标就消失，因为它局限于存储过程

    打开和关闭游标

         CREATE PROCEDURE processorders()

         BEGIN

         DECLAREordernumbersCURSOR

         FOR

         SELECT order_numFROM orders ;

         Open ordernumbers ;

         Close ordernumbers ; //CLOSE释放游标使用的所有内部内存和资源，因此，每个游标不需要时都应该关闭

         END;

    使用游标数据

       在一个游标被打开后，可以使用FETCH语句分别访问它的每一行。FETCH指定检索什么数据（所需的要列），检索出来的数据存储在什么地方。它还向前移动游标中的内部行指针，使下一条FETCH语句检索下一行，相当于PHP中的each()函数

循环检索数据，从第一行到最后一行

         CREATE PROCEDURE processorders()

         BEGIN

         --声明局部变量

         DECLARE doneBOOLEAN DEFAULT 0;

         DECLARE oINT;

 

         DECLAREordernumbersCURSOR

         FOR

         SELECT order_numFROM orders ;

         --当SQLSTATE为02000时设置done值为1

         DECLARE CONTINUEHANDLER FOR SQLSTATE '02000' SET done=1;

         --打开游标

         Open ordernumbers ;

         --开始循环

         REPEAT

         --把当前行的值赋给声明的局部变量o中

         FETCH ordernumbersINTO o;

         --当done为真时停止循环

         UNTIL doneEND REPEAT;

         --关闭游标

         Close ordernumbers ; //CLOSE释放游标使用的所有内部内存和资源，因此，每个游标不需要时都应该关闭

         END;

      语句中定义了CONTINUE HANDLER ,它是在条件出现时被执行的代码。这里，它指出当SQLSTATE '02000'出现时，SET done=1。SQLSTATE '02000'是一个未找到条件，当REPEAT没有更多的行供循环时，出现这个条件。

    DECLARE语句次序 用DECLARE语句定义局部变量必须在定义任意游标或句柄之前定义，而句柄必须在游标之后定义。不遵守此规则就会出错

重复和循环  除这里使用REPEAT语句外，MySQL还支持循环语句，它可用来重复执行代码，直到使用LEAVE语句手动退出为止。通常REPEAT语句的语法使它更适合于对游标进行的循环。

为了把这些内容组织起来，这次吧取出的数据进行某种实际的处理

      CREATE PROCEDURE processorders()

      BEGIN

      --声明局部变量

      DECLARE doneBOOLEAN DEFAULT 0;

      DECLARE oINT;

      DECLARE tDECIMAL(8,2)

 

      DECLAREordernumbersCURSOR

      FOR

      SELECT order_numFROM orders ;

      --当SQLSTATE为02000时设置done值为1

      DECLARE CONTINUEHANDLER FOR SQLSTATE '02000' SET done=1;

      --创建一个ordertotals的表

      CREATE TABLE IF NOTEXISTS ordertotals( order_num INT , total DECIMAL(8,2))

      --打开游标

      Open ordernumbers ;

      --开始循环

      REPEAT

      --把当前行的值赋给声明的局部变量o中

      FETCH ordernumbersINTO o;

      --用上文讲到的ordertotal存储过程并传入参数，返回营业税计算后的合计传给t变量

      CALL ordertotal(o , 1,t)

      --把订单号和合计插入到新建的ordertotals表中

       INSERT INTO ordertotals(order_num,total)VALUES(o , t);

      --当done为真时停止循环

      UNTIL doneEND REPEAT;

      --关闭游标

      Close ordernumbers ; //CLOSE释放游标使用的所有内部内存和资源，因此，每个游标不需要时都应该关闭

      END;

      最后SELECT * FROMordertotals就能查看结果了

第二十五章 使用触发器

   MySQL5版本后支持触发器

   只有表支持触发器，视图不支持触发器

    MySQL语句在需要的时被执行，存储过程也是如此，但是如果你想要某条语句（或某些语句）在事件发生时自动执行，那该怎么办呢：例如：

       1每增加一个顾客到某个数据库表时，都检查其电话号码格式是否正确，区的缩写是否为大写

       2每当订购一个产品时，都从库存数量中减少订购的数量

       3无论何时删除一行，都在某个存档中保留一个副本

   这写例子的共同之处是他们都需要在某个表发生更改时自动处理。这就是触发器。触发器是MySQL响应一下任意语句而自动执行的一条MySQL语句（或位于BEGIN和END语句之间的一组语句）

    1 DELETE

    2 INSERT

    3 UPDATE

   其他的MySQL语句不支持触发器

   创建触发器

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

       1唯一的触发器名； //保存每个数据库中的触发器名唯一

       2触发器关联的表；

       3触发器应该响应的活动（DELETE、INSERT或UPDATE）

       4触发器何时执行（处理前还是后,前是BEFORE后是AFTER）

      创建触发器用CREATE TRIGGER

      CREATE TRIGGER newproductAFTERINSERT ON products

      FOR EACH ROW SELECT'Product added'

      创建新触发器newproduct，它将在INSERT语句成功执行后执行。这个触发器还镇定FOR EACH ROW,因此代码对每个插入的行执行。这个例子作用是文本对每个插入的行显示一次product added

      FOR EACH ROW针对每个行都有作用，避免了INSERT一次插入多条语句

   触发器定义规则

      触发器按每个表每个事件每次地定义，每个表每个事件每次只允许定义一个触发器，因此，每个表最多定义6个触发器（每条INSERT UPDATE 和DELETE的之前和之后）。单个触发器不能与多个事件或多个表关联，所以，如果你需要一个对INSERT和UPDATE存储执行的触发器，则应该定义两个触发器

   触发器失败 如果BEFORE(之前)触发器失败，则MySQL将不执行SQL语句的请求操作，此外，如果BEFORE触发器或语句本身失败，MySQL将不执行AFTER(之后)触发器

   删除触发器

       DROP TRIGGERnewproduct；

      触发器不能更新或覆盖，所以修改触发器只能先删除再创建

   使用触发器

      我们来看看每种触发器以及它们的差别

   INSERT触发器

       INSERT触发器在INSERT语句执行之前或之后执行。需要知道以下几点：

    1在INSERT触发器代码内，可引用一个名为NEW的虚拟表，访问被插入的行

    2在BEFORE INSERT触发器中，NEW中的值也可以被更新（允许更改插入的值）

    3对于AUTO_INCREMENT列，NEW在INSERT执行之前包含0，在INSERT执行之后包含新的自动生成值

      提示：通常BEFORE用于数据验证和净化（目的是保证插入表中的数据确实是需要的数据）。本提示也适用于UPDATE触发器

   DELETE触发器

       DELETE触发器在语句执行之前还是之后执行，需要知道以下几点：

    1在DELETE触发器代码内，你可以引用一个名为OLD的虚拟表，访问被删除的行；

    2 OLD中的值全部是只读的，不能更新

      例子演示适用OLD保存将要除的行到一个存档表中

      CREATE TRIGGERdeleteorderBEFORE DELETE ON orders

      FOR EACH ROW

      BEGIN 

      INSERT INTOarchive_orders(order_num , order_date , cust_id)

      VALUES(OLD.order_num ,OLD.order_date , OLD.cust_id);

      END;

      //此处的BEGIN  END块是非必需的，可以没有

   在任何订单删除之前执行这个触发器，它适用一条INSERT语句将OLD中的值（将要删除的值）保存到一个名为archive_orders的存档表中

    BEFORE DELETE触发器的优点是（相对于AFTER DELETE触发器），如果由于某种原因，订单不能被存档，DELETE本身将被放弃执行。

   多语言触发器 正如上面所见，触发器deleteorder使用了BEGIN和END语句标记触发器体。这在此例中并不是必需的，不过也没有害处。使用BEGIN  END块的好处是触发器能容纳多条SQL语句。

   UPDATE触发器

       UPDATE触发器在语句执行之前还是之后执行，需要知道以下几点：

       1在UPDATE触发器代码中，你可以引用一个名为OLD的虚拟表访问（UPDATE语句前）的值，引用一名为NEW的虚拟表访问新更新的值

       2在BEFORE UPDATE触发器中，NEW中的值可能被更新，（允许更改将要用于UPDATE语句中的值）

       3 OLD中的值全都是只读的，不能更新

         例子：保证州名的缩写总是大写（不管UPDATE语句给出的是大写还是小写）

         CREATE TRIGGER updatevendorBEFORE UPDATE ON vendoresFOR EACH ROW SET NEW.vend_state =Upper(NEW.vend_state)

   触发器的进一步介绍

    1与其他DBMS相比，MySQL5中支持的触发器相当初级。以后可能会增强

    2创建触发器可能需要特殊的安全访问权限，但是触发器的执行时自动的.如果INSERT UPDATEDELETE能执行，触发器就能执行

    3应该用触发器来保证数据的一致性（大小写、格式等）。在触发器中执行这种类型的处理的优点是它总是进行这个处理，而且是透明地进行，与客户机应用无关

    4触发器的一种非常有意义的使用创建审计跟踪。使用触发器把更改（如果需要，甚至还有之前和之后的状态）记录到另一表非常容易

    5遗憾的是，MySQL触发器中不支持CALL语句，这表示不能从触发器中调用存储过程。所需要的存储过程代码需要复制到触发器内

MySQL必知必会笔记（七）事务处理 全球化和本地化（字符集和校对）

第二十六章  管理事务处理

   并非所有引擎都支持事务处理，MyISAM不支持.InnoDB支持

   事务处理可以用来维护数据库的完整性，它保证成批的MySQL语句操作要骂完全执行，要么完全不执行

   一些操作（如：添加订单，银行转账等）如果执行到一半的时候因某种数据库故障（如超出磁盘空间、安全限制、表锁等）阻止了这个过程的完成是非常危险的。这怎么样才能解决呢？

 

   这就要用到事务处理了。事务处理是一种机制，用来管理必须成批执行的MySQLCZ ,以保证不包含不完整的支持结果。利用事务处理，可以保证一组操作要么整体执行，要么完全不执行。发生错误后以前执行的SQL语句进行回退（撤销）已恢复数据到某个已知且安全的状态。

   下面是关于事务处理需要知道的几个术语：

   事务(transaction)指一组SQL语句

   回退(rollback)      指撤销指定SQL语句的过程

   提交(commit)    指将末存储的SQL语句结果写入数据库表；

   保留点(savepoint)指事务处理中设置的临时占位符（place-holder）你可以对它发布回退（与回退整个事务处理不同）

   控制事务处理

      既然知道了什么是事务处理，下面来讨论事务处理的管理中涉及的问题

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

   使用ROLLBACK

      MySQL的ROLLBACK命令用来回退（撤销）MySQL语句。

      SELECT * FROMordertotals;  //24章填充的表不为空

      START TRANSACTION; 

      DELETE FROMordertotals;  //删除所有行

      SELECT * FROMordertotals; //为空

      ROLLBACK;              //回退

      SELECT * FROMordertotals; //不为空

      显然ROLLBACK只能在一个事务处理内使用（在执行一条START TRANSACTION命令之后）

   哪些语句可以回退   INSERT、UPDATE、DELETE语句可以回退，SELECT不可以，也没意义。不能回退CREATE或DROP操作

   使用COMMIT

      一般的MySQL语句都是直接对数据库表执行和编写的。这就是所谓的隐含提交即提交或保存操作是自动执行的。

      但是在事务处理块中，提交不会隐含提交。必须进行明确提交。未了进行明确提交。使用COMMIT语句。

       START TRANSACTION;

       DELETE FROMorderitemsWHEREorder_num = 2000;

       DELETE FROMordersWHEREorder_num = 2000;

       COMMIT;

      这个例子中，完全删除一个订单需要更新两个表。所以使用事务处理块来保证订单不被部分删除。最后的COMMIT语句仅在不出错是写出更改。如果任何一条语句出错。这DELETE不会提交（实际上，他是被自动撤销的）

   使用保留点

      简单的ROLLBACK和COMMIT语句就可以写入或撤销整个事务处理。但是，简单的可以这么做，复杂的事务处理可能需要部分提交或回退

      为了支持回退部分事务处理，必须在事务处理块中合适的位置放置占位符。这样，如果需要回退，可以回退到某个占位符

      这些占位符称为保留点。为了创建占位符。可如下使用SAVEPOINT语句：

      SAVEPOINT delete1;

      每个保留点都取标识它的唯一名字，以便在回退时，MySQL知道要回退到何处。为了回退懂啊某个保留点，可如下进行：

       ROLLBACK TOdelete1;

       保留点越多越好  可以在MySQL中设置多个保留点，越多越好，越多控制回退就越灵活

      释放保留点     保留点在事务处理完成（执行一条ROLLBACK或COMMIT）后自动释放。      MySQL5以来，也可以用RELEASE SAVEPOINT明确地释放保留点

   更改默认提交行为

      默认MySQL行为是自动提交所有更改。换句话说，任何时候你执行一条MySQL语句，该语句实际上都是针对表执行的，而且所做的更改立即生效。为了指示MySQL不自动提交更改。需要使用以下语句：

       SET autocommit= 0;

 

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

      字符集和校对顺序

      数据库表被用来存储和检索数据。不同语言和字符集需要以不同的方式存储和检索。因此，MySQL需要使用不同的字符集，适用不同的排序和检索数据的方法

       字符集   为字母和符号的集合

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

       校对为规定字符如何比较的指令

   校对为什么重要  排序英文正文很容易吗？或许不，考虑词APE、apex和Apple。怎么排序。还有区分和不区分大小写怎么排序，其他字符集更为复杂情况怎么排序。

      在MySQL的正常数据库活动（SELECT  INSERT等）中，不需要操心太多的东西。使用何种字符集和校对的决定在服务器、数据库和表级进行。

   使用字符集和校对顺序

      MySQL正常众多字符集。为了查看所支持的字符集的描述和默认校对可以使用下来语句

      SHOW CHARACTER SET;

      为了查看所支持校对以及适用的字符集的完整列表，使用下列语句：

      SHOW COLLATION;

      通常系统管理在安装时定义一个默认的字符集和校对。此外也可以在创建数据库时，指定默认的字符集和校对。使用以下语句

      SHOW VARIABLES LIKE'character%'

      SHOW VARIABLES LIKE'collation%'

      实际上，字符集很少是服务器范围（甚至是数据库范围）的设置。不同的表，甚至不同的列都可能需要不同的字符集。而且两者都可以在创建时指定

      为了给表指定字符集和校对，可以使用带子句的CREATETABLE

      CREATE TABLEmytable

      (

      Columnn1 INT,

      Columnn2VARCHAR(10)

      )DEFAULT CHARACTER SET hebrew

       COLLATEhebrew_general_ci

      创建了一个包括两列的表，并且指定了一个政府结合校对顺序

      这个例子中指定了CHARACTERSET和COLLATE两者，一般MySQL如下确定使用什么样的字符集和校对

      1如果指定CHARACTER SET和COLLATE两者，这使用这些值

      2如果只指定CHARACTER SET则使用此字符集及其默认的校对顺序

      3如果都没有指定则使用数据库默认

      除了指定字符集和校对表外，MySQL还允许对每个列设置他们的字符集和校对，如下：

      CREATE TABLEmytable

      (

      Column1 INT,

      Column2VARCHAR(10),

      Column3VARCHAR(10)CHARACTER SET latin1COLLATElatin1_general_ci;

      ) DEFAULT CHARACTERSET hebrew

       COLLATEhebrew_general_ci;

         这里对整个表以及特定的列指定了CHARACTER SET和COLLATE

      校对在对用ORDER BY子句检索出来的数据排序时起重要的作用。如果你想需要用于创建表示不同的校对排序特定的SELECT语句，也可以在SELECT语句自身中进行：

      SELECT * FROMcustomers

      ORDER BYlastname , firstnameCOLLATE latin1_general_cs;

      此SELECT使用CALLATE指定了一个备用的校对顺序

临时区分大小写 上面的语句演示了再通常不区分大小写的表上进行区分大小写的搜索的一种技术

      SELECT的其他子句 除了这里的ORDER BY子句中使用COLLATE还可以在GROUP BY、HAVING、聚集函数、别名等。

MySQL必知必会笔记（八）安全管理 数据库维护 改善性能

第二十八章   安全管理

访问控制

    MySQL服务器的安全基础是：用户应该对他们需要的数据具有适当的访问权，不能多也不能少。换句话说，用户不能对过多的数据有更过的访问权。

    访问控制   你需要给用户提供他们所需的访问权，且仅提供他们所需的访问权。

    管理访问控制需要创建和管理用户账号

    使用MySQL Administrator提供的图形用户界面，可更方便的来管理用户及账号权限。

    在非现实的数据库中使用root登录进行调试是对的。不过在现实世界的日常工作中，决不能使用root。而应该创建一系列不同权限的账号，用这些账号来登录对数据库进行操作。

    防止无意的错误访问控制的目的不仅仅是防止用户的恶意企图。数据库更常见的是无意识错误的结果，如错打mysql语句，在不合适的数据库中存储或其他一些错误。通过保证用户不能他们不应该执行的语句，来避免这些情况的发生。

    不应在日常生活中使用root

管理用户

MySQL用户账号和信息存储在名为mysql的MySQL数据库中。

USE mysql;

SELECT userFROM user;

    实验对用户账号和权限进行更改的最好办法是打开多个数据库客户机（如：mysql命令行实用查询的多个副本），一个作为管理登录，其他作为被测试的用户登录

创建用户账号

为了创建一个新用户账号，使用CREATE USER语句：

CREATE USER lileiIDENTIFIED BY 'p@$$wOrd';

   CREATE USER创建了一个新用户账号。在创建用户时不一定需要口令，不过这个例子用IDENTIFIED BY'p@$$wOrd'给出了一个口令

    指定散列口令 IDENTIFIED BY指定的口令为纯文本，MySQL将在保存到user表之前对其进行加密。为了作为散列值指定口令，使用IDENTIFIED BYPASSWORD.

    使用GRANT或INSERT   GRANT语句，也可以创建用户账号，但一般来说CREATE USER是最清楚和最简单的句子。此外，也可以通过直接出入行到user用户表来增加用户，不过安全起见，一般不建议这样做，MySQL用来存储用户账号信息的表以及表模式极为重要，对它们的任何毁坏都可能严重的伤害到MySQL服务器。因此相对于直接处理来说，最好用标记和汗水来处理这些表。

重命名一个用户账号

使用RENAME USER语句

                RENAME USER lileiTO likai;

                MySQL5 之前不支持RENAME USER，以前重命名一个用户使用UPDATE直接更新user表

删除用户账号

               为了删除用户账号，使用DROP USER语句。

                    DROP USER likai;

   MySQL5之后，DROP USER删除用户账户和所有相关的账号权限。在MySQL5之前，DROP USER只能用来删除用户账号，不能删除权限。因此使用旧版本的MySQL，需要先用REVOKE删除与账号相关的权限，然后再用DROP USER删除账号。

设置访问权限

    在创建用户账号后，必须接着分配访问权限。新创建的用户没有访问权限，它们只能登陆服务器，但不能看到数据，也不能执行任何数据库操作。

查看用户账号的权限

SHOW GRANTS FOR lilei;

    用户定义为user@host  MySQL的权限用用户名和主机名结合定义，如果不指定主机名，则使用默认的主机名%（授予用户访问权限而不管主机名）

    设置权限 可使用GRANT语句，GRANT要求至少给出以下信息：

    1 要授予的权限

    2 被授予访问呢权限的数据库或表

    3 用户名

    GRANT SELECT ONcrashcourse . * TO lilei;

//GRANT允许用户在crashcourse.*(crashcourse数据库的所有表)上使用SELECT。通过只授予SELECT访问权限，用户lilei对crashcourse数据库中的所有数据具有只读访问权限。

撤销权限

    REVOKE SELECT ONcrashcourse .* FROM  lilei;

    //取消lilei对crashcourse表的SELECT访问权限，被撤销的权限必须存在，否则出错

    GRANT和REVOKE可在几个层次上控制访问权限

    1 整个服务器，使用GRANT ALL和REVOKE ALL

    2 整个数据库，  使用ON database . *

    3 特定表，使用ON database . Table

    4 特定列；

    5特定的存储过程

列出可以授予或撤销的每个权限

权限                             说明

ALL                除GRANT OPTION外的所有权限

ALTER            使用ALTER TABLE

ALTER ROUTINE                使用ALTER PROCEDURE和DROP PROCEDURE

CREATE                      使用CREATE TABLE

CREATE ROUTINE        使用CREATE PROCEDURE

CREATE TEMPORARY       使用CREATE TEMPORARYTABLE

TABLES

CREATE USER       使用CREATE USER、DROP USER、RENAME USER、                                       使用REVOKE ALLPRIVILEGES

CREATE VIEW             使用CREATE VIEW

DELETE                 使用DELETE

DROP             使用DROP TABLE

EXECUTE              使用CALL和存储过程

FILE               使用SELECT INTO OUTFILE和LOAD DATA INFILE

GRANT OPTION                  使用GRANThe和REVOKE

INDEX                  使用CREATE INDEX和DROP INDEX

INSERT                       使用INSERT

LOCK TABLES              使用LOCK TABLES

PROCESS              使用SHOW FULL PROCESSLIST

PELOAD                       使用FLUSH

REPLICATION CLIENT    服务器位置的访问

REPLICATION SLAVE           由复制从属使用

SELECT                       使用SELECT

SHOW DATABASES       使用SHOW DATABASES

SHOW VIEW          使用SHOW CREATE VIEW

SHUTDOWN                 使用mysqladmin shutdown(用来关闭MySQL)

SUPER            使用CHANGE MASTER、KILL、LOGS、PURGE、MASTER                                         和SET GLOBAL。还允许mysqladmin调试登录

UPDATE          使用UPDATE

USAGE                  无访问权限

    简化多次授权

    可通过列出各权限并用逗号分隔，将多条GRANT语句串在一起

    GRANTSELECT , INSERTON crahcourse .* TO bfroa;

    更改口令

    为了更改用户口令，可使用SET PASSWORD语句，新口令必须传递给Password（）函数加密：

    SET PASSWORD FOR bforta =Password('n3wp@$$wOrd');  //设置bforta的口令

    SET PASSWORD =Password('n3wp@$$wOrd');    //设置自己的口令

第二十九章  数据库维护

首先刷新未写数据 为了保证所有数据被写到磁盘（包括索引数据），可能需要在进行备份前使用FLUSH TABLES语句

备份数据库

    像所有的数据一样，MySQL的数据也必须经常备份。由于MySQL数据库是基于磁盘的文件，普通的备份系统和例程就能备份MySQL的数据库，但是，由于这些文件总是处于打开和使用状态，普通的文件副本备份不一定总是有效。

下面列出这个问题的可能解决方案：

   1 使用命令行实用程序mysqdump转储所有数据库内容到某个外部文件。在进行常规备份前这个实用程序应该正常运行，以便能正确地备份转储文件。

   2 可用命令行实用程序mysqlhotcopy从一个数据库复杂所有数据（并非使用数据库引擎都转储这个实用程序）

   3 可以使用MySQL的BACKUP TABLE或SELET INTO OUTFILE转储所有数据到某个外部文件。这条语句都能接受将要创建的系统文件名，此系统文件必须不存在，否则会出错。数据可以用RESTORE TABLE来复原

进行数据库维护

MySQL提供了一系列的语句，可以（应该）用来保证数据库正确和正常运行。以下是你应该指定的一些语句：

   1 ANALYZE TABLE  用来检查表键知否正确。

   2 CHECK TABLE用来针对许多问题对表进行检查。在MyISAM表上还对索引进行检查。CHECK TABLE支持一系列的用于MyISAM表的方式。

   CHANGED      检查最好一次检查yi8lai改动过的表。

   EXTENDED     执行最彻底的检查

   FAST       做检查为正常关闭的表

   MEDIUM  检查所有被删除的链接并进行键校验

   QUICK           只进行快速扫描

   3 如果MyISAM表访问产生不正确或不一致的结果，可能需要用REPAIR TABLE来修股相应的表。这条语句不应该经常使用，如果需要经常使用，可能会有更大的问题要解决

   4 如果一个表中删除大量数据，应该使用OPTIMIZE TABLE来收回所用的空间，从而优化表的性能

诊断启动问题

    服务器的启动问题通常在对MySQL配置或服务器本身进行更改时出现。MySQL在这个问题发生时报告错误，但由于多数MySQL服务器是作为系统进程或服务自动启动的，这些消息可能看不到。

    在排除系统启动问题时，首先应该尽量用手动启动服务器。MySQL服务器自身通过在命令行上这些mysqld启动。下面是几个重要的mysqld命令行选项：

   1  --help显示帮助

   2  --safe-mode装载减去某些最佳配置的服务器

   3  --verbose显示全文本消息

   4  --version显示版本信息然后退出

    查看日志文件

   MySQL维护管理员依赖的一系列日志文件。主要文件有以下几种：

       1 错误日志。它包含启动和关闭问题以及任意根据错误的细节。此日志通常名为hostname.err，位于data目录中。此日志可用--log-error命令行选项更改

       2 查询日志。它记录所有MySQL活动，在诊断问题是非常有用。词日志文件可能会很快的变得非常大，因此不应该长期使用它。此日志通常名为hostname.log,.位于data目录中。此名字可以用--log命令行选项更改

       3 二进制日志。它记录更新过数据（或者可能更新过数据）的所有语句。此日志通常名为hostname-bin，位于data目录内，此名字可以用--log-bin命令行选项更改，注意：这个日志文件是MySQL5中添加的一切的MySQL版本中使用的是更新日志

       4 缓慢查询日志，顾名思义，词日志记录执行缓慢的任何查询。这个日志在确定数据库何处需要优化很有用。此日志通常名为hostname-slow.log，位于data目录中。此名字可以用--log-slow-queries命令行选项更改

第三十章   改善性能

 改善性能

    下面内容并不是完全决定MySQL的性能。我们回顾一下前面各章的重点，提供进行性能优化探讨和分析的一个出发点：

     1 首先，MySQL（与所有的DBMS一样）具有特定的硬件建议。在学习和研究MySQL时，使用任何旧的计算机作为服务器都可以。但对于生产的服务器来时，应该坚持遵循这些硬件建议

   2 一般来说，根据的生产DBMS应该允许在自己的专业服务器上

   3 MySQL是用一系列的默认设置预先配置的，从这些设置开始通常是很好的。但过一段时间后你可能需要调整内存分配、缓冲区大小等（为了查看当前配置，可使用SHOW VARIABLES;和 SHOW STATUS;）

   4 一个MySQL一个多用户多线程的DBMS，换言之，它经常同时执行多个任务。如果这些任务中的某个执行缓慢，则所有的请求都会执行缓慢。如果你遇到显著的性能不良，可使用SHOW PROCESSLIST显示所有活动进程（以及它们的线程ID和执行时间），你还可以用KILL命令终结某个特定的进程（使用这个命令需要作为管理员登陆）

   5 总是不止一种方法编写一条SELECT语句，应该实验联结、并、子查询等，找出最佳的方法

   6使用EXPLAIN语句让MySQL解释它将如何执行一条SELECT语句

   7 一般来说，存储过程执行的比一条一条执行的其中的各条MySQL语句快

   8 应该总是使用正确的数据类型

   9 决不要检索比需求还要多的数据，换言之，不要用SELECT * FROM (除非真的需要)

   10 有的操作（包括INSERT）支持一个可选的DELAYED关键字，如果使用它，将把控制立即返回给条用程序，并且一旦有可能就现实执行该操作

   11 在导入数据时，应该关闭自动提交。你可以还想删除索引（包括FULLTEXT索引），然后再导入完成后再重建它们。

   12 必须索引数据库以改善数据检索的性能。确定索引什么不是一个微不足道的任务，需要分析使用的SAELECT语句以找出重复的WHERE和ORDER BY 子句。如果一个简单的WHERE子句返回结果所花的时间太长，则可以断定其中使用的列（或几个列）就需要索引的对象

   13 你的SELECT语句中有一系列复杂的OR条件吗？通过使用多条SELECT语句和联结它们的UNION语句，就需要索引的对象

   14 索引改善数据检索的性能，但损害数据插入、删除和更新的新能。如果你有一些表，它们世界数据库且不经常被搜索，则在有必要之前不要索引它们。

   15 LIKE很慢，一般来说，最好的使用FULLTEXT而不是LIKE

   16 数据库是不断变化的实体。一组优化良好的表一会儿后可能就面目全非了。由于表的使用和内容的更改，理想的优化和配置也会发生改变。

   17 最重要的规则是,每条规则在某些条件下都会被打破。