MySQL学习Day02

基本概念

• 数据库（database），保存有组织的数据的容器。

• 表（table），某种特定类型数据的结构化清单。

• 模式（schema），关于数据库和表的布局及特性的信息。（有时也用作数据库的同义词）

• 列（column），表中的一个字段。所有表都是由一个或多个列组成的。

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

• 行（row），表中的一个记录。（行和记录是可以相互替代的，但从技术上说，行才是正确的术语。）

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

  主键值规则：

  • 任意两行都不具有相同的主键值
  • 每个行都必须具有一个主键值（主键值不允许NULL值）
  • 不更新主键列中的值
  • 不重用主键列的值
  • 不在主键列中使用可能会更改的值。

• SQL，结构化查询语言（Structured Query Language），是一种专门用来与数据库通信的语言。

• DBMS，数据库管理系统（Database Management System）。

  DBMS可分为两类：

  • 一类为基于共享文件系统的DBMS，如Microsoft Access、FileMaker等，通常不用于高端或更关键的应用。
  • 另一类为基于客户机-服务器的DBMS，如MySQL、Oracle、Microsoft SQL Server等。应用范围更广、更高端。

• 子句（clause），SQL语句由子句构成，有些子句是必需的，而有些则是可选的。一个子句通常由一个关键字和所提供的数据组成。子句的例子有select语句中的from子句。

• 操作符（operator），用来联结或改变where子句中的子句的关键字。也称为逻辑操作符。

  • AND，用在where子句中的关键字，用来指示检索满足所有给定条件的行。
  • OR，用在where子句中的关键字，用来表示检索匹配任一给定条件的行。

• IN，where子句中用来指定要匹配值的清单的关键字，功能与OR相当。

• NOT，where子句中用来否定后跟条件的关键字。

• 通配符（wildcard），用来匹配值的一部分的特殊字符。

• 搜索模式（search pattern），由字面值、通配符或两者组合构成的搜索条件。

使用MySQL

1. 连接MySQL

   mysql -u root -p

   回车后，输入密码即可进入数据库。

   连接到MySQL，一般需要以下信息：

   • 主机名（计算机名），如果连接到本地MySQL服务器，则为localhost
   • 端口，默认为3306端口
   • 一个合法的用户名
   • 用户口令

2. 常用操作：

   • 查看数据库

     show databases;

   • 选择数据库

     use 数据库名;

   • 查看表

     show tables;

3. 在MySQL中，单引号用来限定字符串。

基本操作

select 语句

1. 检索单列

   select name from tb_course;

2. 检索多列

   select name,addr from tb_course;

   列名之间以逗号分隔。

   显示的数据也是按照检索时的列名顺序排列的。

3. 检索所有列

   select * from tb_course;

   提醒：尽量不要使用通配符，影响执行效率。

4. 检索不同的行，使用DISTINCT关键字，在列名的前面。

   select distinct pwd from tb_userlogin;

5. 限制结果。为了返回第一行或前几行，可使用LIMIT子句。

   • 返回前几行数据

   select pwd from tb_userlogin limit 5;

   运行结果：

   +--------+
   | pwd    |
   +--------+
   | mrsoft |
   | user   |
   | 111    |
   | 555    |
   | 111    |
   +--------+

   • 指定检索的开始行和行数

   select pwd from tb_userlogin limit 0,5;

   运行结果：

   +--------+
   | pwd    |
   +--------+
   | mrsoft |
   | user   |
   | 111    |
   | 555    |
   | 111    |
   +--------+

   注意：检索出来的第一行为行0而不是行1。limit 1,1，将检索出第二行。

6. 对于列的使用，既可以通过列名引用列，也可以使用完全限定的名字来使用列（表名和列名同时使用）。部分情形需要使用完全限定名。

order by 子句

1. 排序数据

   select name from tb_specialty order by name;

   order by 子句中使用的列通常为所选择的列，如上述语句。但是，并不一定要这样，也可以使用非选择列进行排序，如下面的语句。

   select name from tb_specialty order by lengthYear;

2. 按多个列排序

   select enterYear,name,lengthYear 
   from tb_specialty 
   order by enterYear,lengthYear;

   在按多个列排序时，排序完全按所规定的顺序进行。

3. 指定排序方向

   默认的排序顺序是升序排序（从A到Z），也可以指定以降序顺序排序。这时使用DESC关键字。

   select name from tb_specialty order by name desc;

   如果想在多个列上进行降序排序，必须对每个列指定DESC关键字。

   select enterYear,name,lengthYear 
   from tb_specialty 
   order by enterYear desc,lengthYear desc;

   关键字ASC用于升序排序，但无多大用处，因为系统默认为升序排序。

4. order by 和 limit 的组合可以找出一个列的最高或最低的值。

   select id from tb_specialty order by id desc limit 1;

   上述语句，找出了表tb_specialty中的最大的id值。

where 子句

1. 过滤数据

   select enterYear,name 
   from tb_specialty 
   where enterYear = 2005;

   在同时使用order by 和where 子句时，应让order by位于where之后，否则将会产生错误。

   select enterYear,name 
   from tb_specialty 
   where enterYear = 2005 
   order by name;

2. where子句操作符

   操作符	说明
   =	等于
   <>	不等于
   !=	不等于
   <	小于
   <=	小于等于
   >	大于
   >=	大于等于
   between	在指定的两个值之间（包括开始值和结束值）

   注意：MySQL在执行匹配时默认不区分大小写。

   • 检查单个值

     select enterYear,name,lengthYear 
     from tb_specialty 
     where lengthYear <= 3;

   • 不匹配检查

     select enterYear,name,lengthYear 
     from tb_specialty 
     where lengthYear <> 3;

     select enterYear,name,lengthYear 
     from tb_specialty 
     where lengthYear != 3;

     上述两个语句的结果是完全相同的。

   • 范围值检查

     select enterYear,name,lengthYear 
     from tb_specialty 
     where lengthYear 
     between 3 and 4;

     between匹配范围中所有的值，包括指定的开始值和结束值。

   • 空值检查

     在一个列不包含值时，称其为包含空值NULL。

     select enterYear,name,lengthYear 
     from tb_specialty 
     where lengthYear is null;

数据过滤

1. 为了进行更强的过滤控制，MySQL允许给出多个where子句。这些子句能够以and子句的方式或是or子句的方式使用。

   • and操作符

     select enterYear,name,lengthYear 
     from tb_specialty 
     where enterYear = 2005 and lengthYear <= 3;

     运行结果：

     +-----------+--------------------+------------+
     | enterYear | name               | lengthYear |
     +-----------+--------------------+------------+
     | 2005      | 美术装潢设计         | 2          |
     | 2005      | 遥感技术            | 2          |
     +-----------+--------------------+------------+

   • or操作符

     select enterYear,name,lengthYear 
     from tb_specialty 
     where enterYear = 2005 or lengthYear <= 3;

     运行结果：

     +-----------+--------------------+------------+
     | enterYear | name               | lengthYear |
     +-----------+--------------------+------------+
     | 2005      | 美术装潢设计         | 2          |
     | 2005      | 进出口贸易          | 4          |
     | 2010      | 计算机              | 3          |
     | 2010      | 数据结构            | 2          |
     | 2005      | 遥感技术            | 2          |
     +-----------+--------------------+------------+

   • and操作符在计算次序中优先级高于or操作符。然而，该知识点不需要强记，在实际操作中，使用括号即可，高效避免不必要的错误。

2. IN操作符

   IN操作符用来指定条件范围，范围中的每个条件都可以进行匹配。

   IN取合法值的由逗号分隔的清单，全部括在圆括号中。

   select enterYear,name,lengthYear 
   from tb_specialty 
   where enterYear in (2012,2005);

   运行结果：

   +-----------+--------------------+------------+
   | enterYear | name               | lengthYear |
   +-----------+--------------------+------------+
   | 2005      | 美术装潢设计       | 2          |
   | 2005      | 进出口贸易         | 4          |
   | 2005      | 遥感技术           | 2          |
   | 2012      | 编译原理           | NULL       |
   +-----------+--------------------+------------+

   IN操作符的优点：

   • 语法更清楚且更直观
   • 计算次序容易管理（使用的操作符少）
   • IN操作符一般比OR操作符清单执行更快
   • 最大的优点是，可以包含其它select语句，使得能够更动态地建立where子句。

3. NOT操作符

   NOT操作符有且只有一个功能——否定后跟的任何条件。

   select enterYear,name,lengthYear from tb_specialty where enterYear not in (2012,2005);

   运行结果：

   +-----------+--------------+------------+
   | enterYear | name         | lengthYear |
   +-----------+--------------+------------+
   | 2010      | 计算机       | 3          |
   | 2010      | 数据结构     | 2          |
   +-----------+--------------+------------+

使用通配符进行过滤

1. LIKE操作符，搭配通配符使用。

   通配符可以在搜索模式中任意位置使用，并且可以使用多个通配符。

   • 百分号（%）通配符，表示任何字符出现任意次数。

     select stuName,tel from tb_stuuser where stuName like '张%';

     运行结果：

     +-----------+---------------+
     | stuName   | tel           |
     +-----------+---------------+
     | 张三      | 0431-84972266 |
     | 张天      | 0551-88569856 |
     | 张大民    | 1212-45445484 |
     +-----------+---------------+

     注意：除了一个或多个字符外，%还可以匹配0个字符。

   • 下划线（_）通配符，只匹配单个字符。

     select stuName,tel from tb_stuuser where stuName like '张_';

     运行结果：

     +---------+---------------+
     | stuName | tel           |
     +---------+---------------+
     | 张三    | 0431-84972266 |
     | 张天    | 0551-88569856 |
     +---------+---------------+

     与%能匹配0个字符不同，_总是匹配一个字符，不能多也不能少。

2. 使用通配符的技巧

   • 不要过度使用通配符。尽量使用其它操作符。
   • 在确实需要使用通配符时，除非绝对有必要，否则不要把它们用在搜索模式的开始处，会很影响执行效率。
   • 仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据。

用正则表达式（regexp）进行搜索

正则表达式是用来匹配文本的特殊的串（字符集合）。

1. 基本字符匹配

   select stuName,tel 
   from tb_stuuser 
   where stuName regexp '张';

   运行结果：

   +-----------+---------------+
   | stuName   | tel           |
   +-----------+---------------+
   | 张三      | 0431-84972266 |
   | 张天      | 0551-88569856 |
   | 张大民    | 1212-45445484 |
   +-----------+---------------+

   .是正则表达式语言中一个特殊的字符，它表示匹配任意一个字符。

   select stuName,tel from tb_stuuser where stuName regexp '.天';

   运行结果：

   +---------+---------------+
   | stuName | tel           |
   +---------+---------------+
   | 张天    | 0551-88569856 |
   | 明天    | 1121-12349234 |
   +---------+---------------+

2. 进行OR匹配

   为搜索两个（或多个）串之一，使用|。

   select stuName,tel 
   from tb_stuuser 
   where stuName regexp '张|王';

   运行结果：

   +-----------+---------------+
   | stuName   | tel           |
   +-----------+---------------+
   | 张三      | 0431-84972266 |
   | 张天      | 0551-88569856 |
   | 张大民    | 1212-45445484 |
   | 王明      | 0551-15984951 |
   +-----------+---------------+

3. 匹配几个字符之一

   为匹配特定的字符，使用[]。

   select stuName,tel from tb_stuuser where stuName regexp '[12A]tom';

   这里的[12A]定义了一组字符，它的意思是匹配1或2或A。

   运行结果：

   +-----------+---------------+
   | stuName   | tel           |
   +-----------+---------------+
   | 1Tom      | 87793***      |
   | Atom      | 0431-84972266 |
   | 2Tom Chen | 0431-84972266 |
   +-----------+---------------+

   字符集合也可以被否定，即它们将匹配除指定字符外的任何东西。为否定一个字符集，在集合的开始处放置一个^即可。

   select stuName,tel from tb_stuuser where stuName regexp '[^12A]tom';

   运行结果：

   +---------+---------------+
   | stuName | tel           |
   +---------+---------------+
   | 5Tom    | 0431-84972266 |
   +---------+---------------+

4. 匹配范围

   集合可用来定义要匹配的一个或多个字符。

   常见集合[0-9]（等价于[0123456789]）、[a-z]，可拆分。

   select stuName,tel from tb_stuuser where stuName regexp '[1-9]tom';

   运行结果：

   +-----------+---------------+
   | stuName   | tel           |
   +-----------+---------------+
   | 1Tom      | 87793***      |
   | 2Tom Chen | 0431-84972266 |
   | 5Tom      | 0431-84972266 |
   | .7Tom     | 0431-84972266 |
   +-----------+---------------+

   [a-z]和[A-Z]是一样的，大小写不区分。

   select stuName,tel from tb_stuuser where stuName regexp '[a-z]om';

   select stuName,tel from tb_stuuser where stuName regexp '[A-Z]om';

   运行结果都是：

   +-----------+---------------+
   | stuName   | tel           |
   +-----------+---------------+
   | 1Tom      | 87793***      |
   | Atom      | 0431-84972266 |
   | 2Tom Chen | 0431-84972266 |
   | 5Tom      | 0431-84972266 |
   | .7Tom     | 0431-84972266 |
   +-----------+---------------+

5. 匹配特殊字符

   为了匹配特殊字符，必须用\\为前导。\\-表示查找-，\\.表示查找.。

   select stuName,tel from tb_stuuser where stuName regexp '\\.';

   运行结果：

   +---------+---------------+
   | stuName | tel           |
   +---------+---------------+
   | .7Tom   | 0431-84972266 |
   +---------+---------------+

6. 匹配字符类

   为方便工作，可以使用预定义的字符集，称为字符类（character class）。

   字符类	说明
   [:alnum:]	任意字母和数字（[a-zA-Z0-9]）
   [: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:]	任意大写字母
   [:xdigit:]	任意十六进制数字（[a-fA-f0-9]）

   select stuName,tel 
   from tb_stuuser 
   where stuName regexp '[[:alpha:]]om';

   运行结果：

   +-----------+---------------+
   | stuName   | tel           |
   +-----------+---------------+
   | 1Tom      | 87793***      |
   | Atom      | 0431-84972266 |
   | 2Tom Chen | 0431-84972266 |
   | 5Tom      | 0431-84972266 |
   | .7Tom     | 0431-84972266 |
   +-----------+---------------+

   实践证明，在查询过程中，不区分大小写。

7. 匹配多个实例

   重复元字符	说明
   *	0个或多个匹配
   +	1个或多个匹配（{1,}）
   ？	0个或1个匹配（等于{0,1}）
   {n}	指定数目的匹配
   {n,}	不少于指定数目的匹配
   {n,m}	匹配数目的范围（m不超过255）

   select stuName,tel 
   from tb_stuuser 
   where stuName regexp '[[:alpha:]]oms?q';

   s后的？使s可选，因为？匹配它前面的任何字符的0次或1次出现。

   运行结果：

   +----------+---------------+
   | stuName  | tel           |
   +----------+---------------+
   | 5Tomsqqq | 0431-84972266 |
   | .7Tomqqq | 0431-84972266 |
   +----------+---------------+

8. 定位符

   为了匹配任意位置的文本，需要用到下面的定位符。

   定位元字符	说明
   ^	文本的开始
   $	文本的结尾
   [[:<:]]	词的开始
   [[:>:]]	词的结尾

   select stuName,tel from tb_stuuser where stuName regexp '^[[:digit:]]';

   运行结果：

   +-----------+---------------+
   | stuName   | tel           |
   +-----------+---------------+
   | 1Tom4545  | 87793***      |
   | 2Tom Chen | 0431-84972266 |
   | 5Tomsqqq  | 0431-84972266 |
   | 89oms     | 33            |
   | 8仙       | 8             |
   | 2货       | 2             |
   | 996       | 555           |
   +-----------+---------------+

9. ^有两种用法，在集合中（[]）用于否定该集合，集合外用于指示串的开始处。

10. LIKE和REGEXP的不同在于，LIKE匹配整个串而REGEXP匹配子串。

创建计算字段

• 字段（field），基本上与列的意思相同，经常互换使用。不过数据库列一般成为列，而术语字段经常用在计算字段的连接上。

  计算字段是运行时在select语句内创建的。

• 拼接（concatenate），将值联结到一起构成单个值。

1. 字段拼接

   在MySQL的SELECT语句中，可使用Concat()函数来拼接两个列。不区分大小写。

   select concat(stuName,'(',tel,')') 
   from tb_stuuser 
   where stuName like '张%';

   运行结果：

   +-----------------------------+
   | concat(stuName,'(',tel,')') |
   +-----------------------------+
   | 张三(0431-84972266)         |
   | 张天(0551-88569856)         |
   | 张大民(1212-45445484)       |
   +-----------------------------+

   去空格函数：

   • RTrim()：除去字符串右边的空格
   • LTrim()：除去字符串左边的空格
   • Trim()：除去字符串左右两边的空格

2. 使用别名

   别名（alias）是一个字段或值的替换名。别名用AS关键字赋予。有时也称为导出列。

   别名由字母、数字、下划线（_）、符号$、￥构成，不可出现其它字符（如括号等）。

   select Concat(stuName,'(',tel,')') as name_tel 
   from tb_stuuser 
   where stuName like '张%';

   运行结果：

   +--------------------------+
   | name_tel                 |
   +--------------------------+
   | 张三(0431-84972266)      |
   | 张天(0551-88569856)      |
   | 张大民(1212-45445484)    |
   +--------------------------+

3. 执行算数计算

   MySQL支持下表中列出的基本算术运算符，此外圆括号可用来区分优先顺序。

   MySQL算数操作符	说明
   +	加
   -	减
   *	乘
   /	除

   select name,enterYear,enterYear+lengthYear as endYear 
   from tb_specialty 
   order by enterYear;

   运行结果：

   +--------------------+-----------+---------+
   | name               | enterYear | endYear |
   +--------------------+-----------+---------+
   | 美术装潢设计       | 2005      |    2007 |
   | 进出口贸易         | 2005      |    2009 |
   | 遥感技术           | 2005      |    2007 |
   | 计算机             | 2010      |    2013 |
   | 数据结构           | 2010      |    2012 |
   | 编译原理           | 2012      |    NULL |
   +--------------------+-----------+---------+

4. 测试计算

   有时可以省略from子句以便简单地访问和处理表达式。

   • select 6*9;
     运行结果：
     +-----+
     | 6*9 |
     +-----+
     |  54 |
     +-----+
     - ```mysql
       select Trim('abc');
     
     运行结果：
     
     +-------------+
     | Trim('abc') |
     +-------------+
     | abc         |
     +-------------+
     

   • select  now();
     
     +---------------------+
     | now()               |
     +---------------------+
     | 2019-08-14 10:05:47 |
     +---------------------+
     ### 使用数据处理函数
     
     1. 文本处理函数
     
        | 常用的文本处理函数 | 说明                              |
        | ------------------ | --------------------------------- |
        | Left()             | 返回串左边的字符                  |
        | Length()           | 返回串的长度                      |
        | Locate()           | 找出串的一个字串                  |
        | Lower()            | 将串转换为小写                    |
        | LTrim()            | 去掉串左边的空格                  |
        | Right()            | 返回串右边的字符                  |
        | RTrim()            | 去掉串右边的空格                  |
        | Soundex()          | 返回串的SOUNDEX值（模糊发音搜索） |
        | SubString()        | 返回子串的字符                    |
        | Upper()            | 将串转换为大写                    |
     
        ```mysql
        select stuName,Upper(stuName) as stuName_upcase 
        from tb_stuuser 
        where stuName = 'alexander';
     

   运行结果：

   +-----------+----------------+
   | stuName   | stuName_upcase |
   +-----------+----------------+
   | alexander | ALEXANDER      |
   +-----------+----------------+

5. 日期和时间处理函数

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

   select stuName,birthday,tel 
   from tb_stuuser 
   where Date(birthday) between '1981-08-01'and '1981-08-10';

   运行结果：

   +-------------+------------+---------------+
   | stuName     | birthday   | tel           |
   +-------------+------------+---------------+
   | 张三        | 1981-08-01 | 0431-84972266 |
   | Atom        | 1981-08-01 | 0431-84972266 |
   | 2Tom Chen   | 1981-08-01 | 0431-84972266 |
   | 5Toms   qqq | 1981-08-08 | 0431-84972266 |
   | alexander   | 1981-08-01 | 0431-84972266 |
   +-------------+------------+---------------+

6. 数值处理函数

   常用的数值处理函数	说明
   Abs()	返回一个数的绝对值
   Cos()	返回一个角度的余弦
   Exp()	返回一个数的指数值
   Mod()	返回除操作的余数，即取余
   Pi()	返回圆周率
   Rand()	返回一个随机数
   Sin()	返回一个角度的正弦
   Sqrt()	返回一个数的平方根
   Tan()	返回一个角度的正切

汇总数据

1. 聚集函数

   聚集函数（aggregate function），运行在行组上，计算和返回单个值的函数。

   SQL聚集函数	说明
   AVG()	返回某列的平均值（忽略列值为NULL的行）
   COUNT()	返回某列的行数
   MAX()	返回某列的最大值（忽略列值为NULL的行）
   MIN()	返回某列的最小值（忽略列值为NULL的行）
   SUM()	返回某列值之和（忽略列值为NULL的行）

2. AVG()函数

   注意：AVG()函数忽略列值为NULL的行。

   select AVG(lengthYear) as avg_lengthYear from tb_specialty;

   运行结果：

   +--------------------+
   | avg_lengthYear     |
   +--------------------+
   | 2.1666666666666665 |
   +--------------------+

3. COUNT()函数

   两种使用方式：

   • count(*)，对表中行的数目进行计数，不管表列中包含的是否为空值（NULL）。

     select count(*) as class_count from tb_specialty;

     运行结果：

     +-------------+
     | class_count |
     +-------------+
     |           6 |
     +-------------+

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

     select count(lengthYear) as class_count from tb_specialty;

     运行结果：

     +-------------+
     | class_count |
     +-------------+
     |           5 |
     +-------------+

4. MAX()函数

   select MAX(lengthYear) as max_lengthYear from tb_specialty;

   运行结果：

   +----------------+
   | max_lengthYear |
   +----------------+
   | 4              |
   +----------------+

5. MIN()函数

   select MIN(lengthYear) as min_lengthYear from tb_specialty;

   运行结果：

   +----------------+
   | min_lengthYear |
   +----------------+
   | 2              |
   +----------------+

6. SUM()函数

   select SUM(lengthYear) as sum_lengthYear from tb_specialty;

   运行结果：

   +----------------+
   | sum_lengthYear |
   +----------------+
   |             13 |
   +----------------+

7. 聚集不同值

   对以上5个聚集函数都可以如下使用：

   • 对所有的行执行计算，指定ALL参数或不给参数（ALL是默认行为）
   • 只包含不同的值，指定DISTINCT参数，distinct必须使用列名。

   select AVG(distinct lengthYear) as avg_lengthYear from tb_specialty;

   运行结果：

   +----------------+
   | avg_lengthYear |
   +----------------+
   |              3 |
   +----------------+

   可以看到，使用了distinct后，平均值明显上升。

8. 组合聚集函数

   select语句可根据需要包含多个聚集函数。

   mysql> select AVG(distinct lengthYear) as avg_lengthYear,
       -> COUNT(lengthYear) as class_count,
       -> MAX(lengthYear) as max_lengthYear,
       -> MIN(lengthYear) as min_lengthYear,
       -> SUM(lengthYear) as sum_lengthYear
       -> from tb_specialty;

   运行结果：

   

   注意：在指定别名以包含某个聚集函数的结果时，不应该使用表中实际列名。

分组数据

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

1. 创建分组

   select enterYear,COUNT(*) as nun_lengthYear 
   from tb_specialty 
   group by enterYear;

   运行结果：

   +-----------+----------------+
   | enterYear | nun_lengthYear |
   +-----------+----------------+
   | 2005      |              3 |
   | 2010      |              2 |
   | 2011      |              1 |
   +-----------+----------------+

   错误的示例1：

   select enterYear,COUNT(*) as nun_lengthYear from tb_specialty;

   运行结果：

   +-----------+----------------+
   | enterYear | nun_lengthYear |
   +-----------+----------------+
   | 2005      |              6 |
   +-----------+----------------+

   结果是明显有问题的！！！！

   错误的示例2：

   select enterYear,lengthYear from tb_specialty group by enterYear;

   运行结果：

   +-----------+------------+
   | enterYear | lengthYear |
   +-----------+------------+
   | 2005      | 2          |
   | 2010      | 3          |
   | 2011      | 3          |
   +-----------+------------+

   返回的是每个分组的第一个数据。

   使用WITH　ROLLUP关键字，可以得到每个分组以及每个分组汇总级别（针对每个分组）的值。

   select enterYear,COUNT(*) as nun_lengthYear 
   from tb_specialty 
   group by enterYear with rollup;

   运行结果：

   +-----------+----------------+
   | enterYear | nun_lengthYear |
   +-----------+----------------+
   | 2005      |              3 |
   | 2010      |              2 |
   | 2011      |              1 |
   | NULL      |              6 |
   +-----------+----------------+

   GROUP BY子句使用，注意事项：

   1. GROUP BY 子句可以包含任意数目的列，使得对数据分组有更细致的控制。
   2. 如果在GROUP BY子句中嵌套了分组，数据将在最后规定的分组进行汇总。（在建立分组时，指定的所有列都一起计算，不能从个别列取回数据）。
   3. GROUP BY子句中列出的每个列都必须是检索列或有效的表达式（不可为聚集函数），不能使用别名。
   4. 除聚集计算语句外，select中的每个列都必须在GROUP BY子句中给出。
   5. 如果分组列中有NULL值（无论一行或多行），都将NULL作为一个分组返回。
   6. GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。

2. 过滤分组

   where过滤行，having过滤分组。

   where在数据分组前进行过滤，having在数据分组后进行过滤。

   select enterYear,COUNT(*) as nun_lengthYear 
   from tb_specialty 
   group by enterYear 
   having count(*) > 2;

   运行结果：

   +-----------+----------------+
   | enterYear | nun_lengthYear |
   +-----------+----------------+
   | 2005      |              3 |
   +-----------+----------------+

3. 分组和排序

   ORDER BY	GROUP BY
   排序产生的输出	分组行。但输出可能不是分组的顺序。
   任意列都可以使用（非选择的列也行）	只可能使用选择列或表达式列，而且必须使用每个选择列表达式
   不一定需要	如果与聚集函数一起使用列（或表达式），则必须使用

   为保证数据正确排序，一般在使用GROUP BY子句时，也应给出ORDER BY子句，且ORDER BY子句使用的列应为GROUP BY子句中的第一个列（或顺序列），否则会乱序。

4. select子句顺序

   子句	说明	是否必须使用
   select	要返回的列或表达式	是
   from	从中检索数据的表	仅在从表选择数据时使用
   where	行级过滤	否
   group by	分组说明	仅在按组计算聚集时使用
   having	组级过滤	否
   order by	输出排序顺序	否
   limit	要检索的行数	否

使用子查询

查询（query），任何SQL语句都是查询。但此术语一般指select语句。

1. 嵌套查询

   子查询一般与IN操作符结合使用，也可使用等于（=）、不等于（<>）等。

   select name,enterYear 
   from tb_specialty 
   where id in (select id from tb_specialty where lengthYear = 2);

   运行结果：

   +--------------------+-----------+
   | name               | enterYear |
   +--------------------+-----------+
   | 美术装潢设计       | 2005      |
   | 数据结构           | 2010      |
   | 遥感技术           | 2005      |
   +--------------------+-----------+

   下面的语句和上述查询的结果是一样的：

   select id from tb_specialty where lengthYear = 2;
   select name,enterYear from tb_specialty where id in (1,4,5);

   注意：不建议多层嵌套，影响效率。

2. • 利用子查询进行过滤，就如上面的嵌套查询
   • 作为计算字段使用子查询（具体示例见《MySQL必知必会》p94）

• 相关子查询（correlated subquery），涉及外部查询的子查询。

联结表

• 主键（primary key），一列（或一组列），其值能够唯一区分表中每个行。
• 外键（foreign key），外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。
• 可伸缩性（scale），能够适应不断增加的工作量而不失败。设计良好的数据库或应用程序称之为可伸缩性好（scale well）。
• 联结，联结是一种机制，用来在一条select语句中关联表，因此称之为联结。使用特殊的语法，可以联结多个表返回一组输出，联结在运行时关联表中正确的行。联结不是物理实体，它在实际的数据库表中不存在。

1. 创建联结

   mysql> select students.name as name,students.age as age,teac_name as teacher,sex as teac_sex
       -> from students,teachers
       -> where main_teacher = teac_name;

   运行结果：

   +--------+-----+-----------+----------+
   | name   | age | teacher   | teac_sex |
   +--------+-----+-----------+----------+
   | 张宇   |   7 | 汪子颜    | 女       |
   | 小明   |   6 | 刘诗诗    | 女       |
   +--------+-----+-----------+----------+

   在联结两个表时，你实际上做的是将第一个表中的每一行与第二个表中的每一行配对。

   where子句作为过滤条件，它只包含那些匹配给定条件的行。

   没有where子句，第一个表中的每个行将与第二个表中的每个行配对，而不管它们逻辑上是否可以配在一起。

   mysql> select students.name as name,students.age as age,teac_name as teacher,sex as teac_sex
       -> from students,teachers;

   运行结果：

   +--------+-----+-----------+----------+
   | name   | age | teacher   | teac_sex |
   +--------+-----+-----------+----------+
   | 张宇   |   7 | 汪子颜    | 女       |
   | 小明   |   6 | 汪子颜    | 女       |
   | 张宇   |   7 | 张帅      | 男       |
   | 小明   |   6 | 张帅      | 男       |
   | 张宇   |   7 | 刘诗诗    | 女       |
   | 小明   |   6 | 刘诗诗    | 女       |
   +--------+-----+-----------+----------+

   PS：匹配的结果很好玩，似乎是以第二张表的记录为主去匹配第一张表的各条记录，具体过程我也不清楚，后面再说吧。

2. 笛卡儿积（cartesian product），由没有联结条件的表关系返回的结果为笛卡儿积。检索出的行数是两个表行数之积。例如上一个例子，就是笛卡儿积的应用。

   应该保证所有联结都有where子句，还应保证where子句的正确性，这样才能保证MySQL返回我们想要的数据。

   返回笛卡儿积的联结称为叉联结。

3. 内部联结，也称等值联结，它是基于两个表之间的相等测试。

   mysql> select students.name as name,students.age as age,teac_name as teacher,sex as teac_sex
       -> from students inner join teachers
       -> on main_teacher = teac_name;

   运行结果：

   +--------+-----+-----------+----------+
   | name   | age | teacher   | teac_sex |
   +--------+-----+-----------+----------+
   | 张宇   |   7 | 汪子颜    | 女       |
   | 小明   |   6 | 刘诗诗    | 女       |
   +--------+-----+-----------+----------+

   此语句的运行结果与前面创建联结中示例的结果是一样的，select语句相同、from子句不同。以inner join指定两个表之间的关系，特定的on子句进行条件限制，此处的on子句作用与where子句相同。

   注意：联结时，首选inner join语法，这样不仅确保不会忘记联结条件，有时还会更高效。

4. 多表联结

   视情况而定，联结的表越多，性能下降越厉害。

创建高级联结

1. 使用表别名

   mysql> select s.name as name,s.age as age,teac_name as teacher,sex as teac_sex
       -> from students as s,teachers as t
       -> where s.main_teacher = t.teac_name;

   运行结果：

   +--------+-----+-----------+----------+
   | name   | age | teacher   | teac_sex |
   +--------+-----+-----------+----------+
   | 张宇   |   7 | 汪子颜    | 女       |
   | 小明   |   6 | 刘诗诗    | 女       |
   +--------+-----+-----------+----------+

   注意：

   • 表使用了别名后，select语句要使用表的别名（或者不用），不能直接使用原来的名字，这是因为select语句在最后一步执行。
   • 表别名只在查询执行中使用。与列别名不同，表别名不返回客户机。
2. • 自联结：与表自身进行联结
   • 自然联结：所有的内部联结都是自然联结
   • 外部联结：分为左外部联结和右外部联结

   左外部联结：

   mysql> select *
       -> from students left outer join teachers
       -> on main_teacher = teac_name;

   运行结果：

   +----+--------+-----+--------------+---------+-----------+------+------+
   | id | name   | age | main_teacher | teac_id | teac_name | age  | sex  |
   +----+--------+-----+--------------+---------+-----------+------+------+
   |  1 | 张宇   |   7 | 汪子颜       |       1 | 汪子颜    |   22 | 女   |
   |  2 | 小明   |   6 | 刘诗诗       |       3 | 刘诗诗    |   20 | 女   |
   +----+--------+-----+--------------+---------+-----------+------+------+

   右外部联结：

   mysql> select *
       -> from students right outer join teachers
       -> on main_teacher = teac_name;

   运行结果：

   +------+--------+------+--------------+---------+-----------+-----+-----+
   | id   | name   | age  | main_teacher | teac_id | teac_name | age | sex |
   +------+--------+------+--------------+---------+-----------+-----+-----+
   |    1 | 张宇   |    7 | 汪子颜       |       1 | 汪子颜    |  22 | 女  |
   |    2 | 小明   |    6 | 刘诗诗       |       3 | 刘诗诗    |  20 | 女  |
   | NULL | NULL   | NULL | NULL         |       2 | 张帅      |  26 | 男  |
   +------+--------+------+--------------+---------+-----------+-----+-----+

   我们可以看到，左联结会保留左边表的所有数据，右联结会保留右边表的所有数据，没有匹配项则填NULL。

3. 带聚集函数的联结

   聚集函数是在select语句中做操作，而联结是在from子句中做操作。

组合查询

MySQL允许执行多个查询（多条select语句），并将结果作为单个查询结果集返回。这些组合通常称为并（union）或复合查询（compound query）。

mysql> select name,age
    -> from students
    -> union
    -> select teac_name,age
    -> from teachers;

运行结果：

+-----------+-----+
| name      | age |
+-----------+-----+
| 张宇      |   7 |
| 小明      |   6 |
| 汪子颜    |  22 |
| 张帅      |  26 |
| 刘诗诗    |  20 |
+-----------+-----+

我们可以看到，union将两条查询结果合并在了一起，并以第一条查询语句的select中的字段作为表头。

UNION规则：

• union必须由两条或两条以上的select语句组成，语句之间用关键字union分隔。
• union中的每个查询必须包含相同的列、表达式或聚集函数，次序也应一致
• 列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型。

注意：union从查询结果集中除去了重复的行，这是默认行为。若想返回所有匹配的行，可以使用UNION ALL。

全文本搜索

并非所有的引擎都支持全文本搜索，例如InnoDB就不支持，但MyISAM支持全文本搜索。

1. 启用全文本搜索支持。一般在建表时启用全文本搜索，在CREATE语句中接受fulltext子句，它给出被索引列的一个逗号分隔的列表。

   mysql> create table productnotes
       -> (
       -> note_id         int      not null auto_increment,
       -> prod_id         char(10) not null,
       -> note_date       datetime not null,
       -> note_text       text     null,
       -> primary key(note_id),
       -> fulltext(note_text)
       -> )engine=MyISAM;

   可以在建表时启用，也可在稍后指定（此时已有数据必须建立索引）。

   注意：不要在数据导入时使用fulltext，会花费大量时间，降低效率。

2. 进行全文本搜索。使用match()和against()函数，match()指定被搜索的列，against()指定要使用的搜索表达式。

   select note_text
   from productnotes
   where match(note_text) against('rabbit');

   全文本搜索的一个重要部分就是对结果排序，具有较高等级的行先返回。

3. 使用查询扩展

   • 简单全文搜索

     select note_text
     from productnotes
     where match(note_text) against('baby');

   • 带有查询扩展的全文搜索

     select note_text
     from productnotes
     where match(note_text) against('rabbit' with query expansion);

   • 简单查询返回了1条数据，而查询扩展返回了7条数据。关键字with query expansion，扩展查询的结果并不一定与查询的字符串完全相同，但一定会相关。

4. 即使没有fulltext索引，也可以使用布尔方式的全文本搜索。但该操作非常缓慢。

   select note_text
   from productnotes
   where match(note_text) against('rabbit' in boolean mode);

   全文本布尔操作符不在此做过多赘述。

5. 全文本搜索的使用说明

   • 在索引全文本数据时，短词（3个或3个以下字符的词）会被忽略且从索引中排除。字符串长度可更改。
   • MySQL带有一个内建的非用词（stopword）列表，这些词在索引全文本数据时总是被忽略。列表可被覆盖。
   • 如果一个词出现在50%以上的行中，则将它作为非用词忽略。（不适用与in boolean mode）。
   • 若表中的行数少于3行，则全文本搜索不返回结果。
   • 忽略词中的单引号。如don’t索引为dont。
   • 不具有词分隔符的语言（日语和汉语），不能恰当地返回全文本搜索的结果。
   • 仅在MyISAM数据库引擎中支持全文本搜索。

插入数据

插入数据，使用INSERT语句。

mysql> insert into customers
    -> (
    ->  cust_address,
    ->  cust_city,
    ->  cust_country,
    ->  cust_email
    -> )
    -> values
    -> ('紫云路',
    ->  '上海',
    ->  'CHINA',
    ->  'yys@qq.com'
    -> ),
    -> (
    ->  '西藏路',
    ->  '北京',
    ->  'CHINA',
    ->  '1212@163.com'
    -> );

插入数据后的表：

在插入数据时，

1. 如若不在表名后给出列名，则需按照数据库表中列的定义次序给出相应的数据；
2. 给出列名后，无需理会表中列的定义次序，按照自己给的列名插入相应数据即可，该方法虽繁琐，但却安全，可以很好适应表结构的变动。
3. 在插入多条记录时，用逗号分隔。（如示例）
4. 可以在insert和into之间添加关键字LOW_PRIORITY，以此来降低该insert语句的优先级。对于侧重于数据检索的数据库，整体性能会有较大提升。
5. 单条insert语句处理多个插入比使用多条insert语句快。

插入检索出的数据时，可以使用所谓的INSERT SELECT语句（注意用法）。

mysql> insert into newcustomers
    -> (cust_address,cust_city,cust_email)
    -> select cust_address,cust_city,cust_email from customers;

插入数据后的表：

+---------+--------------+--------------+--------------+-----------+
| cust_id | cust_address | cust_country | cust_email   | cust_city |
+---------+--------------+--------------+--------------+-----------+
|       1 | 紫云路       | NULL         | yys@qq.com   | 上海      |
|       2 | 西藏路       | NULL         | 1212@163.com | 北京      |
+---------+--------------+--------------+--------------+-----------+

更新和删除数据

• 更新数据，使用UPDATE语句。不要省略where子句！！！！

  两种更新方式：

  • 更新表中特定行
  • 更新表中所有行（不建议，一定要小心）

  更新时，若不进行限定，则会更新整个表。

  mysql> update newcustomers
      -> set cust_email = 'alex@163.com',
      -> 	   cust_country = 'CHINA'
      -> where cust_email = 'alex@qq.com';

  更新结果：

  +---------+--------------+--------------+--------------+-----------+
  | cust_id | cust_address | cust_country | cust_email   | cust_city |
  +---------+--------------+--------------+--------------+-----------+
  |       1 | 紫云路       | NULL         | yys@qq.com   | 上海      |
  |       2 | 西藏路       | CHINA        | alex@163.com | 北京      |
  +---------+--------------+--------------+--------------+-----------+

  IGNORE关键字，在UPDATE语句中使用了之后，即使在更新的过程中发生了错误也继续进行更新。

  UPDATE IGNORE customers……

• 删除数据，使用DELETE语句。不要省略where子句！！！！

  两种删除方式：

  • 删除表中特定行
  • 删除表中所有行（不建议，一定要小心）

  删除时，若不进行限定，则会删除整个表。

  mysql> delete from newcustomers
      -> where cust_id = 1;

  删除数据后的表：

  +---------+--------------+--------------+--------------+-----------+
  | cust_id | cust_address | cust_country | cust_email   | cust_city |
  +---------+--------------+--------------+--------------+-----------+
  |       2 | 西藏路       | CHINA        | alex@163.com | 北京      |
  +---------+--------------+--------------+--------------+-----------+

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

• 更新和删除的指导原则

  1. 除非确实打算更新或删除每一行，否则绝对不要使用不带where子句的update或delete语句。
  2. 保证每个表都有主键。
  3. 在对update或delete语句使用where子句前，应先用select进行测试，保证它过滤的是正确的记录，以防编写的子句不正确。
  4. 使用强制实施引用完整性的数据库。
  5. MySQL没有撤销（undo）按钮，一定要极其小心使用update、delete语句。

创建和操作表

1. 表的创建

   • 新表的名字，在关键字create table之后给出
   • 表列的名字和定义，用逗号分隔。

   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_country    char(50)        NULL,
       ->  cust_contact    char(50)        NULL,
       ->  cust_email      char(50)        NULL,
       ->  PRIMARY KEY (cust_id)
       -> )ENGINE=InnoDB;

2. NULL值、主键和AUTO_INCREMENT

   • NULL值就是没有值或缺值。不要把NULL值与空串相混淆，NULL值是没有值，而空串（’’）是个有效的值，它不是无值。

   • 主键值必须唯一。主键是一个或多个列构成，使用PRIMARY KEY ()指定。可以在创建表时定义，也可以在创建表之后定义。

     mysql> create table orderitems
         -> (
         ->  order_num       int     not null,
         ->  order_item      int     not null,
         ->  prod_id         char(10)not null,
         ->  quantity        int     not null,
         ->  item_price      decimal(8,2)    not null,
         ->  primary key (order_num,order_item)
         -> )engine=InnoDB;

   • AUTO_INCREMENT，本列每当增加一行时自动增量。

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

     可以通过last_insert_id()函数获得最后一个AUTO_INCREMENT值。

3. 指定默认值、引擎类型

   • 默认值用create table语句的列定义中的default关键字指定。MySQL只允许常量作为默认值。

     mysql> create table orderitems
         -> (
         ->  order_num       int     not null,
         ->  order_item      int     not null,
         ->  prod_id         char(10)not null,
         ->  quantity        int     not null	default 1,
         ->  item_price      decimal(8,2)    not null,
         ->  primary key (order_num,order_item)
         -> )engine=InnoDB;

   • 引擎用于管理和处理数据。

     常见的引擎：

     • InnoDB是一个可靠的事务处理引擎，它不支持全文本搜索。
     • MyISAM是一个性能极高的引擎，它支持全文本搜索，但不支持事务处理。
     • MEMORY在功能等同于MyISAM，但由于数据存储在内存（不是磁盘）中，速度很快（适用于临时表）。

     注意：虽然引擎类型可以混用，但外键不能跨引擎。

4. 更新表

   为更新表定义，可使用ALTER TABLE语句。除非必要，否则不建议更新表。

   • 在ALTER TABLE之后给出要更改的表名（该表必须存在，否则报错）
   • 所做更改的列表

   mysql> alter table customers
       -> add cust_state char(5) not null;

   mysql> alter table customers
       -> drop column cust_state;

   // 修改表列名
   alter table customers change column cust_state new_state char(5) null;

5. 删除表

   删除表，使用DROP TABLE语句即可。

   drop table stu;

   删除表没有确认，也不能撤销，执行这条语句将永久删除该表。

6. 重命名表

   使用REMANE TABLE语句可以重命名一个表。

   mysql> rename table stu to students,
       ->          teacher to teachers;

使用视图

视图，是虚拟的表，建立在实体表之上。视图不包含表中的任何列或数据，它包含的是一个SQL查询。

1. 视图的优点：

   • 重用SQL语句。
   • 简化复杂的SQL操作。
   • 使用表的组成部分而不是整个表。
   • 保护数据。
   • 更改数据格式和表示。

2. 视图的规则和限制：

   • 与表一样，视图必须唯一命名（表、视图的名字都是唯一的）。
   • 对于可以创建的视图数目没有限制。
   • 为了创建视图，必须具有足够的访问权限，通常由数据库管理员授权。
   • 视图可以嵌套。
   • order by可以用在视图中，但如果从该视图检索数据的select语句中也含有order by子句，那么视图中的order by将会被覆盖。
   • 视图不能索引，也不能有关联的触发器或默认值。
   • 视图可以与表一起使用。

3. 视图的创建

   • 视图用create view语句来创建。

   • 使用show create view viewname;来查看创建视图的语句。

   • 使用DROP删除视图。

   • 更新视图，既可以先DROP再CREATE，也可以直接用CREATE OR REPLACE VIEW。对于后者，若要更新的视图不存在，则会创建一个新视图；否则，替换原有视图

     mysql> CREATE OR REPLACE
         -> VIEW specialty
         -> as select name,lengthYear from tb_specialty;

4. mysql> create view specialty as 
       -> select name,lengthYear 
       -> from tb_specialty
       -> where lengthYear > 1;
   ```mysql
   select name,lengthYear from specialty where lengthYear = 2;
   
   运行结果：
   
   +--------------------+------------+
   | name               | lengthYear |
   +--------------------+------------+
   | 美术装潢设计       | 2          |
   | 数据结构           | 2          |
   | 遥感技术           | 2          |
   +--------------------+------------+
   
   在MySQL处理上述查询语句时，它将指定的where语句添加到视图查询中的已有的where子句中，以便正确过滤数据。也极大简化了复杂SQL语句的使用。
   
   对表的大多操作都可以实施到视图上来。
   

5. 如果视图定义中有以下操作，则不能进行视图的更新：

   • 分组（group by 和having）
   • 联结
   • 子查询
   • 并
   • 聚集函数（MIN()、COUNT()等）
   • DISTINCT
   • 导出（计算）列

   视图主要用于检索（select）而非更新（insert、update、delete）。

高级应用

使用存储过程

存储过程，简单地来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批文件，但它的作用远不止于此。

存储过程的三大好处：简单、安全、高性能。

1. 存储过程的创建

   create procedure ordertotal(
   	in onumber int,
   	out ototal decimal(8,2)
   )
   begin
   	select Sum(item_price*quantity)
   	from orderitems
   	where order_num = onumber
   	into ototal;
   end;

   存储过程的创建和Java中方法的创建很类似，方法名后跟参数，然后是方法体。

   但存储过程的参数分三类，输入参数（in）、输出参数（out）、无参数。

   begin和end语句用于限定存储过程体。

   注意：存储过程中的注释，在内容前面放置--即可。单行注释。

   mysql 命令行实用程序使用;作为分隔符，若存储过程自身内有;字符，则会在解释时报错。解决方法：

   delimiter //
   
   create procedure ordertotal(
   	in onumber int,
   	out ototal decimal(8,2)
   )
   begin
   	select Sum(item_price*quantity)
   	from orderitems
   	where order_num = onumber
   	into ototal;
   end; //
   
   delimiter;

   其中，delimiter //告诉命令行实用程序使用//作为新的语句结束分隔符，而标志存储过程结束的end也被定义为end //，最后的delimiter;的作用是回复原来的语句分隔符。

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

2. 执行存储过程

   MySQL称存储过程的执行为调用，使用call关键字。

   调用存储过程：

   call ordertotal(2005,@total);

   显示结果：

   select @total;

   调用存储过程时返回的参数名（即@后面的内容）可以自定义。

3. 删除存储过程

   drop procedure ordertotal;

   注意：删除时，只需给出存储过程名即可，无需添加()。

   如果要删除的存储过程不存在，则上述语句便会报错。

   最好使用下述语句：

   drop procedure if exist;

4. 检查存储过程

   其实就是显示存储过程的创建语句、时间、创建者等详细信息。

   show create procedure ordertotal;

使用游标

游标（cursor），是一个存储在MySQL服务器上的数据库查询，他不是一条select语句，而是被该语句检索出来的结果集。游标主要用于交互式应用，其中用户需滚动屏幕上的数据，并对数据进行浏览或做出更改。

MySQL游标只能用于存储过程和函数。

1. 创建游标，使用declare关键字。

   create procedure processorder()
   begin
   	declare ordernumbers cursor
   	for
   	select order_num from orders;
   end

2. 打开和关闭游标

   使用open打开游标

   open ordernumbers;

   使用close关闭游标

   close ordernumbers

3. 使用游标数据

   在一个游标被打开后，可以使用fetch语句访问它的每一行。fetch指定检索什么数据（哪一列），检索出来的数据存储在什么地方。它还向前移动游标中的内部行指针，使下一条fetch语句检索下一行。

   create procedure processorder()
   begin
   	-- 定义局部变量
   	declare done boolean default 0;
   	declare o int;
   	
   	-- 定义游标
   	declare ordernumbers cursor
   	for
   	select order_num from orders;
   	
   	-- 定义continue handler
   	declare continue handler for SQLSTATE '02000' set done=1;
   	
   	-- 打开游标
   	open ordernumbers;
   	
   	-- 在此处定义自己需要的操作
   	
   	-- 循环所有行
   	repeat
   		-- 获取数据，存入中间变量o
   		fetch ordernumbers into o;
   	-- 结束循环
   	until done end repeat;
   	
   	-- 关闭游标
   	close ordernumbers;
   	
   end

   • declare语句的发布存在特定的次序：局部变量 > 游标 > 句柄。
   • continue handler，在条件出现时执行的代码。在这里，当SQLSTATE ‘02000’出现时，set done=1。
   • repeat用于定义循环，until done end repeat;用于控制循环结束，当done的值为1时，循环结束。

使用触发器

1. 触发器用create trigger语句创建。

   create trigger newproduct after insert on products
   for each row select 'product add';

   创建了名为newproduct的触发器，在insert语句成功执行后执行。for each row，对每个插入行执行，每成功插入，都会显示’product add’。

   注意：

   • 每个数据库的触发器名应唯一。
   • 触发器按每个表每个事件每次地定义，每个表每个事件每次只允许一个触发器。故，每个表最多支持6个触发器（insert、update、delete的之前和之后）。单一触发器不能与多个事件或多个表关联。

2. 删除触发器用drop trigger语句。

   drop trigger newproduc;

   注意：触发器不能被更新或覆盖。所以想要修改触发器，只能先删除再重建。

3. 触发器

   • insert触发器
     • 在insert触发器代码内，可引用一个名为new的虚拟表，访问被插入的行
     • 在before insert触发器中，new中的值可以被更新
   • delete触发器
     • 在delete触发器代码内，可引用一个名为old的虚拟表，访问被删除的行
     • old中的值全部都是只读的，不能更新
   • update触发器
     • 在update触发器代码中，可引用一个名为old的虚拟表访问以前的值，引用一个名为new的虚拟表访问新更新的值
     • 在before update触发器中，new中的值可以被更新
     • old中的值全都是只读，不能更新
   • 通常，before用于数据验证或净化（目的是保证插入表中的数据确实是需要的数据），适用于insert、update触发器。

   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;

管理事务处理

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

术语：

• 事务（transaction），指一组SQL语句。
• 回退（rollback），指撤销指定SQL语句的过程。
• 提交（commit），指将未存储的SQL语句结果写入数据库表。
• 保留点（savepoint），指事务处理中设置的临时占位符（placeholder），你可以对他发布回退（与回退整个事务处理不同）。

MySQL使用如下的语句来标志事务的开始：

start transaction;

1. rollback

   -- 查看students表
   select * from students;
   -- 开始事务
   start transaction;
   -- 删除表中记录
   delete from students;
   -- 确认删除成功
   select * from students;
   -- 事务回退
   rollback;
   -- 查看表中记录，恢复如初
   select * from students;

   上述操作都是一条一条执行的。

   显然，rollback只能在一个事务处理内使用（即在start transaction;语句之后）。

   事务处理用来管理insert、update、delete语句，故可以回退。但select、create、drop都不能进行回退操作。

2. commit

   一般的SQL语句都是直接针对数据库表执行和编写的。这就是隐含提交（implicit commit），即提交操作是自动进行的。

   但是在事务处理块中，提交不会隐含地进行，为进行明确的提交，需要使用commit语句。

   start transaction;
   delete from students;
   select * from students;
   commit;

   注意：当commit或rollback语句执行后，事务会自动关闭（也称隐含事务关闭）。

3. 保留点

   应用于事务处理时的部分提交或回退。

   创建保留点

   savepoint delete1;

   回退到保留点

   rollback to delete1;

   注意：

   • 保留点越多越好
   • 保留点在事务处理完成后会自动释放，也可使用release savepoint明确释放保留点。

4. 更改默认的提交行为

   针对一般SQL语句，默认是自动提交的，但也可更改。

   修改MySQL不自动提交更改：

   set autocommit=0;

   注意：autocommit标志是针对每个连接而非服务器。

全球化和本地化

1. 字符集和校对顺序

   术语：

   • 字符集，字母和符号的集合。
   • 编码，某个字符集成员的内部表示。
   • 校对，规定字符如何比较的指令。

   查看所支持的字符集：

   show character set;

   查看所支持的校对：

   show collation;

安全管理

1. 管理用户

   mysql数据库有一个名为user的表，它包含所有的用户账号。

2. • 创建用户

     create user test identified by 'password';

     有时会报错“ERROR 1290 (HY000): The MySQL server is running with the –skip-grant-tables option so it cannot execute this statement”，使用以下语句即可：

     flush privileges;

     该语句的作用是刷新权限表。

   • 重命名用户账号

     rename user test to yang;

   • 删除用户账号

     drop user yang;

3. 设置访问权限

   • 查看权限

     show grants for test;

     运行结果：

     +----------------------------------+
     | Grants for test@%                |
     +----------------------------------+
     | GRANT USAGE ON *.* TO 'test'@'%' |
     +----------------------------------+

     usage表示根本没有权限。

   • 授权

     grant select on students.* to test;

     运行结果：

     +--------------------------------------------+
     | Grants for test@%                          |
     +--------------------------------------------+
     | GRANT USAGE ON *.* TO 'test'@'%'           |
     | GRANT SELECT ON `students`.* TO 'test'@'%' |
     +--------------------------------------------+

   • 撤销权限

     revoke select on students.* from test;

     运行结果：

     +----------------------------------+
     | Grants for test@%                |
     +----------------------------------+
     | GRANT USAGE ON *.* TO 'test'@'%' |
     +----------------------------------+

4. 更改口令（登录密码）

   set password for test = password('new_password');

   在不指定用户名时，set password更新当前登录用户的口令。

数据库维护

1. 备份数据

   • mysqldump，转储所有数据库内容到某个外部文件。
   • mysqlhotcopy，从一个数据库复制所有数据。
   • 可以使用MySQL的backup table或select into outfile转储所有数据到某个外部文件。数据复原可以用restore table。

2. 数据库维护

   • analyze table，用来检查表键是否正确。

     analyze table students;

     运行结果：

     +-----------------+---------+----------+----------+
     | Table           | Op      | Msg_type | Msg_text |
     +-----------------+---------+----------+----------+
     | testdb.students | analyze | status   | OK       |
     +-----------------+---------+----------+----------+

   • check table，用来针对许多问题对表进行检查。

     check table student;

     运行结果：

     +-----------------+-------+----------+----------+
     | Table           | Op    | Msg_type | Msg_text |
     +-----------------+-------+----------+----------+
     | testdb.students | check | status   | OK       |
     +-----------------+-------+----------+----------+

   • 如果MyISAM表访问产生不正确和不一致的结果，可能需要使用repair table来修复相应的表。不常用，若经常使用则表示存在更大的问题。

   • 删除大量表中数据，使用optimize table来回收所用的空间，从而优化表的性能。

3. 日志文件在MySQL安装目录的data文件夹下。

改善性能

• 使用explain语句让MySQL解释它将如何执行一条select语句。

  explain select * from students;

  运行结果：

  

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

• 坚决不用select *语句。

• 在导入数据时，应关闭自动提交。

• 必须索引数据库表以改善数据检索的性能。

• like很慢。

OVER