《MySQL必知必会》学习笔记

数据库：数据库是一种以某种有组织的方式存储的数据集合。其本质就是一个容器，通常是一个或者一组文件。

 

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

 

模式：描述数据库中特定的表以及整个数据库和其中表的关系。表具有一些特性，这些特性定义了数据在表中如何存储，可以存储什么样的数据，数据如何分解，各个部分信息如何命名等信息。

 

列：表中的一个字段，所有表都是有一个或多个列组成的。

 

行：行是表中的一个独立的记录，它包含了所有列的信息。

 

数据类型：数据类型限制可以存储在类型红的数据种类，还可以帮助正确的排序数据，并在优化磁盘使用方面起着重要作用。

 

数据库、表、列、用户、权限等信息被存储在数据可和表中，mysql数据库用来存储用户及权限信息，information_schema数据库存储数据库以及其中的表、列、存储过程等相关信息。

 

检索数据：检索数据使用SELECT语句，它可以从一个或者多个表中检索信息。使用SELECT必须至少给出两条信息：检索什么，从哪里检索。

 

检索数据如果没有明确排序查询结果，则返回的数据的顺序没有特殊意义。除非确实需要表中的每个列，否则最好不要使用*通配符，检索不需要的列通常会降低检索和应用程序的性能。

 

限制重复数据：使用DISTINCT关键字可以限制检索出具有不同值得列表，DISTINCT关键字应用于检索出的所有列中。

 

限制返回结果：使用LIMIT子句可以限制检索结果返回的数目，以及内容。

 

排序检索数据：使用ORDER BY子句可以对返回结果进行排序，当对多个列进行排序时，排序按照所规定的列的顺序进行排序，也就是先按照第一个列进行排序，第一个列排序结果相同的再按照第二个列进行排序，以此类推。每个列的排序方向默认为升序，如果指定降序需要特殊指定。DESC关键字只应用到直接位于其前面的列名。

搜索条件（search criteria）也成为过滤条件（filter condition）

检索数据可以通过SQL过滤和应用过滤两种方式。应用过滤数据库服务器会返回多余的数据，导致网络带宽的浪费，同时客户机处理数据库的工作也会极大的影响应用的性能，并且时所创建的应用完全不具备可伸缩性。所以应该尽量在数据库服务器上实现对数据的过滤。

Order by 子句位于where 子句之后

Where 字句可用的操作符有：=，<>,!=,<,>,<=,>=,between

Mysql在执行匹配时不区分大小写。

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

空值NULL 和字段包含0，空字符串（empty），或者仅仅包含空格不同，检查是否为空值用 IS NULL 子句，例如：SELECT * FROM Ttable_name WHERE column_name IS NULL;

查询条件使用!=不会返回NULL值行，如果想要返回NULL值行必须要用IS NULL 子句。

SQL中默认AND具有比OR高的操作优先级，在任何使用AND和OR的WHERE子句中，应该使用圆括号明确的分组操作符，不要依靠默认计算顺序消除歧义。

IN操作符完成与OR相同的功能，IN的最大优点在于可以包含其他的SELECT语句，似的能够更动态地建立WHERE子句。

NySQL允许使用NOT对IN,BETWEEN,EXISTS语句取反

为了在搜索子句中使用通配符，必须使用LIKE关键字。LIKE指示mysql，后跟的搜索模式利用通配符匹配，而不是直接相等匹配进行比较。

通配符：% 百分号表示任何字符出现任意次数。不能匹配NULL值。

       _ 下划线总是匹配任意单个字符。

使用通配符进行搜索的处理时间要比直接相等匹配所花时间更多。

直接从数据库中检索出转换、计算或者格式化过的数据要比在客户端中通过程序来完成要快得多，因为数据库管理系统是设计来快速有效的完成这种处理的。

拼接字段：Concat()函数  MySQL语句中可以使用Concat()函数把多个串拼接起来，各个串之间用逗号分隔。

去掉空格：RTrim()去掉右侧空格，LTrim()去掉左面空格，Trim()去掉两侧空格

MySQL首选的日期格式为yyyy-mm-dd 例如：2016-11-09。

SQL数据类型为datetime,显示时间和日期，如果只提取时间可以使用Date()函数，只把日期部分提取出来。

如果想要检索某一年一个月中的所有数据有两种实现方式1、BETWEEN关键字，限定日期区间2、使用如期处理函数 Year(date)=2016 AND Month(date)=12组合限定，这样可以不用担心每个月的实际天数或者闰月情况。

 

 

汇总数据

MySQL提供5个聚集函数：

    AVG()      返回某列的平均值

    COUNT()    返回某列的行数    count(*)对所有行计数包括NULL；

count(column)计数指定列,不包括NULL值。

    MAX()      返回某列的最大值

    MIN()      返回某列的最小值    

    SUM()      返回某列的和

MySQL中使用聚集函数来汇总数据他们的返回结果一版要比在客户端应用程序中计算快得多，而且更节省资源，不论是时间资源还是硬件资源。

 

 

分组数据

分组允许把数据分为多个逻辑组，以便能对每个组进行聚集计算。WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）。例如：SELECT ID,UPLOAD_TIME,COUNT(*) AS num_count FROM table_nbame WHERE MONTH(UPLOAD_TIME)=12  GROUP BY ID WITH ROLLUP 检索出的结果在num_count最后一行会返回所有分组统计数量的和

 

WHERE字句用来过滤掉不符合匹配条件的行，在分组中，可以使用HAVING子句进一步过滤掉不符合条件的分组。WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。

 

ORDER BY和GROUP BY，GROUP BY是对数据进行分组，ORDER BY 是对输出的数据进行排序，两个子句一起使用的时候ORDER BY 子句用在GROUP BY子句之后，并且数据在查询的时候先分组然后再排序输出。

 

SELECT语句中各个子句的书写顺序：SELECT->FROM->WHERE->GROUP BY->HAVING->ORDER BY->LIMIT

 

子查询

嵌套在其他查询中的查询语句为子查询。

1、将子查询的放置在WHERE子句中，可以利用子查询的结果充当查询条件过滤数据。

2、将子查询作为计算字段将查询结果充当返回的数据

子查询最常见的使用时再WHERE子句的IN操作符中，以及用来填充计算列

 

 

表的联结

关系型数据库设计的基础是要尽量减少相同数据的多次重复出现。关系表的设计就是要保证把信息分解成多个表，一类数据一个表，各表通过某些常用的值互相关联，这也正体现了关系的内涵。

 

按照关系数据库设计的基础，相关数据被存储在多个表中，用单条SQL语句检索出想要的数据的有效手段是使用联结。

 

创建联结的关键是要规定联结的所有表，以及他们如何实现关联。应该保证所有的连接都有WHERE子句。

 

笛卡尔积：没有联结条件的表检索出的行的数目是相联结的几个表的行数的积。

 

基于两个表之间的相等测试进行的联结成为等值联结，也叫做内部联结。等值联结形式： select table2.column1,table2.column1 from  table1,table2 where table1.column2=tabl2.column2

内部联结的规范形式为：select table1.column1,table2.column1 from table1 inner join table2 on table1.column2=table2.column2

联结条件使用特点的ON语句而不是WHERE子句。

 

使用子查询的场合很多也可以使用联结来实现数据的检索。

 

自联结：自联结就是同一张表的两次联结。在同一张表中有可能需要通过两次查询才能检索出想要的数据，只用一条SELECT语句通过子查询可以实现这种检索，同时使用自联结能更高效的实现从相同表中检索数据。

使用子查询的例子可能如下：SELECT id，name FROM products WHERE vend_id=(SELECT vend_id FROM product WHERE id=123)。这条语句检索出和id为123的产品具有相同生产商的产品信息。

使用自联结的形式如下：SELECT p1.id,p1.name FROM products AS p1,products AS p2 WHERE p1.vend_id=p2.vend_id AND p2.id=123

 

 

外部联结：内部联结是基于两个表中的相等测试建立起来的联结，外部联结比内部联结更强大，他可以包含那些在相关表中没有关联行的行。也就是说，使用外部关联，可以检索出在另外一张表里没有相关关联记录的字段信息。

 

组合查询:使用UNION关键字可以将两条或者以上的SELECT语句检索结果组合成单个查询结果集。使用UNION关键字要保证，每个SELECT语句必须包含相同的列、表达式或者聚集函数，也就是说每个SELECT查询出的内容必须是相同的，这样才能组合成单个结果集；每个查询的列数据类型必须兼容。UNION语句默认从结果集中自动去除了重复的行。如果需要包括重复的行可使用UNION ALL关键字。

 

数据插入及系统安全:利用MySQL的安全机制，可以针对每个表或者每个用户禁止使用INSERT语句。

插入数据使用INSERT语句，不要使用没有指定明确列的INSERT语句，这样可以不用考虑表结构中列的顺序的变化所带来的影响。

MySQL使用单条INSERT语句处理多个插入比使用多条INSERT语句要高效得多。

使用INSERT…SELECT语句可以实现把检索出来的数据插入到表中。

 

数据库引擎：数据库引擎用来具体管理和处理数据（有待更详细的介绍）

MySQL常见引擎：

    InnoDB:支持事务处理，不支持全文本搜索

    MyISAM:性能极高，支持全文本搜索，不支持事务处理

    MWMORY:功能上类似于MyISAM,但是数据存储在内存中而不是磁盘上，速度很快，适合用于临时表。

 

通过外键保持关系的表必须要具有相同的引擎类型。

 

视图:MySQL在MySQL5之后添加了对视图功能的支持。视图不是表，他也不包含任何数据，他包含的全部是根据需要检索数据的查询，也就是一条SELECT语句。在本质上，视图提供了一种MySQL的SELECT语句层次的封装，视图功能的全部意义在于简化复杂数据的处理。

使用视图的作用：

1. 重用SQL语句。这是封装的特性。
2. 简化复杂的SQL操作。这是视图功能最本质的作用。
3. 使用表的组成部分而不是整个表。视图已经做过一次查询，可以当做虚拟的表来使用。
4. 保护数据。通过给用户授予表的特定部分的访问权限而不是整个表，可以有效地保证数据安全。
5. 更改数据格式和表示。使用视图相当于增加了一个缓冲层，可以在此层次上做特殊的数据显示处理。

 

存储过程:MySQL5 添加了对存储过程的支持。存储过程实际上是一种保存在数据库服务器上的一种函数，所以存储过程名称后需要有圆括号（）。存储过程简单来说就是为了以后的使用而保存的一条或者多条MySQL语句的集合。当所需要使用的MySQL语句比较复杂，尤其是包含业务规则和智能处理时，使用存储过程能够很方便的得到结果。

 

使用存储过程的理由：

1. 把多条处理语句封装在一个单元中。和视图类似，通过封装简化复杂操作。不过视图只是封装SELECT语句，存储过程可以封装全部类型的SQL语句，并且还能进行智能处理。
2. 保证数据的完整性。
3. 简化对变动的处理。
4. 提高性能。存储过程比使用单独的SQL语句要快。
5. 用户使用和创建存储过程的权限是分开的，这保证了数据库的安全。

 

创建存储过程：

 

此例是一个阐明存储过程功能的很好的示例。CREATE PROCEDURE procedure_name()语句用来创建存储过程。如果此函数有接受或者返回的参数，那接收或者返回的参数要在（）中列举出来，并且使用IN或者OUT或者INOUT关键之指定该参数是传入参数还是传出参数，并且指定参数数据类型。BEGIN和END语句用来限定存储过程体。在存储过程体中，通过SELECT…INTO把要返回的数据存储在存储过程名的（）中指定的返回变量中。

 

 

触发器：触发器定义在数据库的表上，针对INSERT,DELETE,UPDATE三个事件发生时可以自动的执行某些操作。触发器明在每个表中必须唯一。只有真正的物理实体表才支持触发器，每个表每个事件只允许一个触发器。触发器可以定义在事件发生之前或者之后自动触发，因此每张表最多支持6个触发器。

 

 

事务处理：事务是一组SQL语句。事务处理是一种机制，用来管理必须成批执行的MySQL操作，以保证数据库不包含不完整的结果。通过事务处理，可以保证一组操作不会中途停止，他们作为整体执行，或者完全不执行。最著名的解释事务处理必要性的例子就是银行账户转账问题。

 

有关事务的几个名词：

1. 事务：一组SQL语句。
2. 回退：撤销指定SQL语句的过程。
3. 提交：将未存储的SQL语句结果写入数据库。
4. 保留点：事务处理中设置的临时占位符，可以控制事务回退的位置。

 

简单的回退使用ROLLBACK语句可以撤销整个事务，复杂的事务处理可以自己指定保留点，然后使用ROLLBACK TO回退到指定的保留点，这样可以是实现部分提交或回退。

 

安全管理：MySQL服务器的安全基础是：通过控制访问用户的权限，保证用户对他们需要的数据具有适当的访问权限，既不多也不少。管理访问控制需要创建和管理用户账号。

MySQL数据库的用户账号信息存储在mysql数据库的user表中。

 

创建用户账号：CREATE USER user_name IDENTIFIED BY ‘password’;创建用户账号及其对应的密码。新创建的用户不具有任何权限。

 

删除用户账号：DROP USER user_name;

设置访问权限：使用GRANT语句为用户设置权限，设置权限语句要给出的信息包含：

1. 要授予的权限
2. 被授予访问的数据库或者表
3. 被授予权限的用户

例如：GRANT SELECT ON database_name.* TO user_name;

这条语句授予user_name用户针对database_name数据库的所有表只读权限。

撤销访问权限：REVOKE语句是GRANT的反操作。

 

GRANT和REVOKE语句能够控制的访问权限层次：

1. 整个服务器 GRANT|REVOKE ALL;
2. 整个数据库 ON database.*;
3. 特定的表   ON database.table;
4. 特定的列
5. 特定的存储过程