关系模型和SQL

刺骨的言语ヽ痛彻心扉 2022-01-07 02:15 266阅读 0赞

# 关系模型和SQL #

为了介绍关系模型，以MySQL数据为例。

*  安装MariaDB

#安装mariadb
    > yum install mariadb-server
    #启动服务
    > systemctl start mariadb.service
    #开机启动
    > systemctl enble mariadb.service
    # 为了安全设置Mysql服务
    > mysql_secure_installation
    # 数据库密码登录
    > mysql -u root -p
    # 显示所有数据库
    mysql> show databases;
    # 创建用户并授权
    mysql> grant all on *.* to 'xdd'@'%' identified by 'xdd';
    mysql> flush privileges;
    # 导入测试脚本
    > mysql -u root -p < test.sql

## SQL语句 ##

*  SQL是结构化查询语言Structured Query Language。1987年被ISO组织标准化。
 *  所有主流的关系型数据库都支持SQL，NoSQL也有很大一部分支持SQL。
 *  SQL语句分为：
    
    1.  **DCL数据库控制语言**
        
         *  负责数据库权限访问控制，由GRANT和REVOKE两个指令组成
    2.  **DDL数据定义语言**
        
         *  负责数据库定义，数据库对象定义，由CREATE,ALTER与DROP三种语句组成
    3.  **DML数据库操作语言**
        
         *  负责对数据库对象的操作，CRUD增删改查
    4.  **TCL事务控制语言**
        
         *  负责处理ACID事务，支持commit、rollback指令
 *  语言规范
    
    1.  SQL语句大小写不敏感
        
         *  一般建议，SQL的关键字、函数等大写
    2.  SQL语句末尾应该使用分号结束
    3.  注释
        
         *  多行注释 `/*注释内容*/`
         *  单行注释 `-- 注释内容`
         *  MySQL注释可以使用\#
    4.  使用空格或缩进来提高可读性
    5.  命名规范
        
         *  必须以字母开头
         *  可以使用数字、\#、$和\_
         *  不可使用关键字

## DDL命令 ##

## DCL数据库控制语言 ##

GRANT授权，REVOKE撤销

GRANT ALL ON employees.* TO 'xdd'@'%' IDENTIFIED by 'xdd';
    REVOKE ALL ON *.* FROM xdd;

1.  常用特殊符号
    
     *  `*`为通配符，代指任意库或者任意表。
     *  `*.*`所有库的所有表。
     *  `employess.*`表示employees库下所有的表
     *  `%`为通配符，它是SQL语句的通配符，匹配任意长度字符串

## DDL数据库定义语言 ##

1.  删除用户(慎用)
    
        DROP USER xdd;
2.  创建数据库
    
     *  库是数据的集合，所有数据按照数据模型组织在数据库中。
    
        CREATE DATABASE IF NOT EXISTS test CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
        CREATE DATABASE IF NOT test CHARACTER SET utf8;
    
     *  CHARACTER SET指定数据库字符集。
        
        1.  utf8mb4是utf8的扩展。支持4字节utf8mb4,需要MySQL5.5.3+。
     *  COLLATE指定字符集的校对规则，用来做字符串的比较。
3.  删除数据库
    
        DROP DATABASE IF EXISTS gogs;
4.  创建表
    
     *  表分为行和列，MySQL是行存数据库。数据是一行行存的，列必须固定多少列。
        
         *  行Row,也称为记录Record，元组。
         *  列Column,也称为字段Field、属性。
     *  字段的取值范围叫做域Domain。例如gender字段的取值就是M或者F两个值。
     *  反引号标注的名称，会被认为是非关键字，使用反引号避免冲突。
    
        CREATE TABLE `xddtable` (
            `emp_no` int(11) NOT NULL,
            `birth_date` date NOT NULL,
            `first_name` varchar(14) NOT NULL,
            `last_name` varchar(16) NOT NULL,
            `gender` enum('M','F') NOT NULL,
            `hire_date` date NOT NULL,
            PRIMARY KEY (`emp_no`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
5.  DESC查看列信息
    
     *  语法`{DESCRIBE | DESC} tb|_name [col_name | wild]`
    
        DESC xddtable;
        DESC xddtable '%name'

*  练习 设计一张表，记录登录账户的注册信息，应该存储用户的姓名、登录名、密码

DROP DATABASE IF EXISTS test;
    CREATE DATABASE IF NOT EXISTS test CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
    use test;
    CREATE TABLE `reg` (
        `id` int(11) NOT NULL,
        `loginname` varchar(50) NOT NULL,
        `name` varchar(64) DEFAULT NULL,
        `password` varchar(128) NOT NULL,
        PRIMARY KEY (`id`)
    ) Engine = InnoDB

### 数据类型 ###

*  MySQL中的数据类型

<table> 
 <thead> 
  <tr> 
   <th align="left">类型</th> 
   <th align="left">含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">tinyint</td> 
   <td align="left"><strong>1字节</strong>，带符号的范围是[-128,127]。无符号的范围是[0,255]。<br>bool或boolean，就是tinyint，0表示假,非0表示真</td> 
  </tr> 
  <tr> 
   <td align="left">smallint</td> 
   <td align="left"><strong>2字节</strong>，带符号的分为是[-32768,32767]。无符号的范围是[0,65535]</td> 
  </tr> 
  <tr> 
   <td align="left">int</td> 
   <td align="left"><strong>整形，4字节</strong>，同Integer,带符号的范围是[-21474483648,2147483647]。无符号的范围是[0,4294967295]</td> 
  </tr> 
  <tr> 
   <td align="left">bigint</td> 
   <td align="left"><strong>长整形，8字节</strong>，同Integer,带符号的范围是[-9223372036854775808,9223372036854775807]。无符号的范围是[0,18446744073709551615]</td> 
  </tr> 
  <tr> 
   <td align="left">float</td> 
   <td align="left"><strong>单精度浮点数</strong>精确到大约7位小数位</td> 
  </tr> 
  <tr> 
   <td align="left">double</td> 
   <td align="left"><strong>双精度浮点数</strong>精度确到大约15位小数位</td> 
  </tr> 
  <tr> 
   <td align="left">DATE</td> 
   <td align="left">日期。支持的范围为’1000-01-01’到’9999-12-31’</td> 
  </tr> 
  <tr> 
   <td align="left">DATETIME</td> 
   <td align="left">支持的范围是’1000-01-01 00:00:00’到’9999-12-31 23:59:59’</td> 
  </tr> 
  <tr> 
   <td align="left">TIMESTAMP</td> 
   <td align="left">时间戳。范围是’1970-01-01 00:00:00’到2037年</td> 
  </tr> 
  <tr> 
   <td align="left">char(M)</td> 
   <td align="left">固定长度，右边填充空格以达到长度要求。M为长度，范围为[0-255]。<br>M指代字符个数</td> 
  </tr> 
  <tr> 
   <td align="left">varchar(M)</td> 
   <td align="left">变长字符串。M表示最大列长度。M的范围是[0-65535]。但不能突破行最大字节数65535</td> 
  </tr> 
  <tr> 
   <td align="left">text</td> 
   <td align="left">大文本。最大长度为65535(<span><span><span> 2 16 − 1 2^{16}-1 </span><span><span><span style="height: 0.897438em; vertical-align: -0.08333em;"></span><span><span>2</span><span><span><span><span style="height: 0.814108em;"><span style="top: -3.063em; margin-right: 0.05em;"><span style="height: 2.7em;"></span><span><span><span>1</span><span>6</span></span></span></span></span></span></span></span></span><span style="margin-right: 0.222222em;"></span><span>−</span><span style="margin-right: 0.222222em;"></span></span><span><span style="height: 0.64444em; vertical-align: 0em;"></span><span>1</span></span></span></span></span>)个字符</td> 
  </tr> 
  <tr> 
   <td align="left">BLOB</td> 
   <td align="left">大字节。最大长度为65535(<span><span><span> 2 16 − 1 2^{16}-1 </span><span><span><span style="height: 0.897438em; vertical-align: -0.08333em;"></span><span><span>2</span><span><span><span><span style="height: 0.814108em;"><span style="top: -3.063em; margin-right: 0.05em;"><span style="height: 2.7em;"></span><span><span><span>1</span><span>6</span></span></span></span></span></span></span></span></span><span style="margin-right: 0.222222em;"></span><span>−</span><span style="margin-right: 0.222222em;"></span></span><span><span style="height: 0.64444em; vertical-align: 0em;"></span><span>1</span></span></span></span></span>)字节的BLOB列</td> 
  </tr> 
 </tbody> 
</table>

*  LENGTH()函数返回字节数。而char和varchar定义的M是字符数限制。
 *  char可以将字符串定义为固定长度，空间换时间，效率略高；varchar为变长，省了空间。

### 关系 ###

*  在关系数据库中，关系就是二维表，由行和列组成。
    
    1.  行Row,也称为记录Record,元组。
    2.  列Column,也称为字段Field、属性。
 *  字段的取值范围叫做**域**Domain。例如gender字段的取值就是M或者F两个值。
 *  **维数**：关系的维数指关系中属性的个数
 *  **基数**：元组的个数

**注意**：在关系中，属性的顺序并不重要。理论上，元组顺序也不重要，但是由于元组顺序与存储相关，会影响查询效率。

### 候选键 ###

*  关系中，能位移标识一条元组的属性或属性集合，称为候选键。

### PRIMARY KEY 主键 ###

*  表中一列或多个列组成唯一的key,也就是通过这一个或者多个列能唯一的标识一条记录。即被选择的候选键。
 *  主键的列不能包含空值null。主键往往设置为整型、长整型，可以为自增AUTO\_INCREMENT字段。
 *  表中可以没有组件，但是，一般表设计中，往往都会有主键，以避免记录重复。

### Foreign KEY外键 ###

*  严格来说，当一个关系中的某个属性或属性集合与另一个关系(也可以是自身)的候选键匹配时，就称作这个属性或属性结合是外键。

### 索引Index ###

*  可以看做是一本字典的目录，为了快速检索用的。空间换时间，显著提高查询效率。
 *  可以对一列或者多列字段设定索引。
    
    1.  **主键索引**：主键会自动建立主键索引，主键本身就是为了快速定位唯一记录的。
    2.  **唯一索引**：表中的索引列组成的索引必须唯一，但可以为空，非空必须唯一。
    3.  **普通索引**：没有唯一性的要求，就是建了一个字典的目录而已。
 *  在MySQL中，InnoDB和MyLSAM的索引数据结构可以使用Hash或BTree，默认是BTree。
    
    1.  Hash时间复杂度是O(1),但是只能进行精确度匹配，也就是Hash值的匹配，比如范围匹配就没办法了，hash值无序所以无法知道原有记录的顺序。Hash问题较多。
    2.  BTree索引，以B+树为存储结构。虽然索引可以提高查询所读，但是却影响增删改的效率，因为需要索引更新或重构。频繁出现在where子句中的列可以考虑使用索引。要避免把性别这种字段设索引。

### 约束Constraint ###

*  为了保证数据的完整正确，数据模型还必须支持完整性约束。
 *  "必须有值"约束：某些列的值必须有值，不许为空NULL。

### 域约束Domain Constraint ###

限定了表中字段的取值范围

### 实体完整性Entity Integrity ###

PRIMARY KEY约束定义了主键，就定义了**主键约束**。主键不重复且唯一，不能为空。

### 引用完整性Referential Integrity \*\*\* ###

*  外键定义中，可以不是引用另一张表的主键，但是，往往实际只会关注引用主键。
 *  外键：在表B中的列，引用了表A中的主键，表B中的列就是外键。A表称为主表，B表称为从表

1.  **插入规则**
    
     *  不需要指定。
     *  如果在表B插入一条记录，B的外键列插入了一个值，这个值必须是表A中存在的主键值。
2.  **更新规则**
    
     *  定义外键约束时指定该规则。
3.  **删除规则**
    
     *  定义外键约束时指定该规则。

*  **外键约束的操作**
    
    1.  外键约束，是为了保证数据完整性、一致性，杜绝数冗余、数据错误。

<table> 
 <thead> 
  <tr> 
   <th align="left"><strong>设定值</strong></th> 
   <th align="left"><strong>说明</strong></th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">CASCADE</td> 
   <td align="left">级联，从父表删除或更新会自动删除或更新子表中匹配的行</td> 
  </tr> 
  <tr> 
   <td align="left">SET NULL</td> 
   <td align="left">从父表删除或更新行，会设置字表中的外键为NULL，但必须保证字表列没有指定NOT NULL，也就是说子表的字段可以为NULL才行。</td> 
  </tr> 
  <tr> 
   <td align="left">RESTRICT</td> 
   <td align="left">如果从附表删除主键，如果子表引用了，则拒绝对父表的删除或更新操作</td> 
  </tr> 
  <tr> 
   <td align="left">NO ACTION</td> 
   <td align="left">标准SQL的关键字，在MySQL中与RESTRICT相同。拒绝对父表的删除或更新操作</td> 
  </tr> 
 </tbody> 
</table>

### 实体-联系E-R ###

数据库建立，需要搜集用户需求，设计符合企业要求的数据模型。而构建这种模型需要方法，这种方法需要称为E-R实体-联系建模。也出现了一种建模语言–UML(Unified Modeling Language)统一建模语言。

*  实体Entity:现实世界中具有相同属性的一组对象，可以是物理存在的事物或抽象的事物。
 *  联系Relationship:实体之间的关联集合。
 *  **实体间联系类型**  
    假设有实体部门，实体员工

<table> 
 <thead> 
  <tr> 
   <th align="left">类型</th> 
   <th align="left">描述</th> 
   <th align="left">解决方案</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">一对多联系1:n</td> 
   <td align="left">一个员工属于一个部门，一个部门有多个员工</td> 
   <td align="left">员工外键：部门主键</td> 
  </tr> 
  <tr> 
   <td align="left">多对多联系m:n</td> 
   <td align="left">一个员工属于多个部门，一个部门有多个员工</td> 
   <td align="left">建立第三表</td> 
  </tr> 
  <tr> 
   <td align="left">一对一联系1:1</td> 
   <td align="left">假设有实体管理者，一个管理者管理一个部门，一个部门只有一个管理者</td> 
   <td align="left">字段健在哪张表都行</td> 
  </tr> 
 </tbody> 
</table>

*  一对一关系用的比较少，往往表示表A的一条记录唯一关联表B的一条记录，反之亦然。
 *  它往往是为了将一张表多列分割并产生了多张表，合起来是完整的信息，或为了方便查询，或为了数据安全隔离一部分字段数据等等。

## 视图 ##

*  视图，也称为虚表，看起来像表。它是由查询语句生成的。可以通过视图进行CRUD操作。
 *  视图的作用
    
    1.  简化操作，将复杂查询SQL语句定义为视图，可以简化查询。
    2.  数据安全，视图可以只显示真实表的部分列，或计算后的结果，从而隐藏真实表的数据

## 关系操作 ##

*  **关系**：在关系数据库中，关系就是二维表。关系操作就是表的操作。
 *  **选择(selection)**: 又称为限制，是从关系中选择出满足给定条件的元组。
 *  **投影(projection)**: 在关系上投影就是从选择出诺干属性列组成新的关系。
 *  **链接(join)**:将不同的两个关系链接成一个关系。

## DML–CRUD增删改查 ##

1.  **Insert语句 插入语句**
    
     *  语法：`INSERT INTO 表名[(插入的字段名，插入的字段名，...)] VALUES (对应的字典内容，对应的字段内容，...)`
    
        INSERT INTO table_name (col_name,...) VALUES (value1,...);
        -- 向表中插入一行数据，自增字段、缺省值字段、可为空字段可以不写
        
        INSERT INTO table_name SELECT ... ;
        -- 将select查询的结果插入到表中
        
        INSERT INTO table_name (col_name1,...) VALUES (value1,...) ON DUPLICATE KEY UPDATE col_name1=value1,...;
        -- 如果主键冲突、唯一键冲突就执行update后的设置。这条语句的意思，就是主键不在新增记录，主键在就更新部分字 段。
        
        INSERT IGNORE INTO table_name (col_name,...) VALUES (value1,...);
        -- 如果主键冲突、唯一键冲突就忽略错误，返回一个警告。
    
        INSERT INTO reg (loginname,`name`,`password`) VALUES ('tom','tom','tom');
        INSERT INTO reg (id,loginname,`name`,`password`) VALUES (5,'tom','tom','tom');
        INSERT INTO reg (id,loginname,`name`,`password`) VALUES (5,'tom','tom','tom') ON DUPLICATE KEY UPDATE name = 'jerry'
2.  **Update语句,修改语句**
    
     *  语法 `UPDATE 需要更新的表名 SET 更新的指段名=更新的值 ，... WHERE 条件`
    
        UPDATE [IGNORE] tbl_name SET col_name1=expr1 [,col_name2=expr2,...][WHERE where_defintion]
        -- IGNORE 意义同Insert语句
        
        UPDATE reg SET name='张三' WHERE id = 5;
    
        -- 注意这一句非常危险，会更新所有数据
        UPDATE reg SET name = 'ben';
        
        -- 更新一定要加条件
        UPDATE reg SET name = 'ben',password = 'benpwd' WHERE id = 1;
3.  **Delete语句，删除语句**
    
     *  语法 `DELETE FROM 表名 [WHERE 条件]`
    
        DELETE FROM tbl_name [WHERE where_definition]
        -- 删除复合条件的记录
    
        --删除一定要有条件，否则非常危险
        DELETE FROM reg WHERE id = 1;
4.  **Select语句，查询语句**
    
     *  语法 `SELECT [DISTINCT] 字段名 FROM 表名 [WHERE 条件] [GROUP BY 需要分组的字段名 [HAVING 条件]] [ORDER BY 需要排序的字段名 [ASC | DESC]] [LIMIT {[offset,] row_count | row_count OFFSET offset}] [FOR UPDATE | LOCK IN SHARE MODE]`
    
        SELECT
            [DISTINCT]
            select_expr,...
            FROM table_references
            [WHERE where_definition]
            [GROUP BY {col_name | expr | position}
                [ASC | DESC],...[WITH ROLLUP]]
            [HAVING where_definition]
            [ORDER BY {col_name | expr | position}
                [ASC | DESC],...]
            [LIMIT {
             [offset,] row_count | row_count OFFSET offset}]
            [FOR UPDATE | LOCK IN SHARE MODE]
    
     *  FOR UPDATE会把行进行写锁，这是排它锁。
     *  查询，查询的结果为结果集recordset。
    
        select 1;
        
        -- 最简单的查询
        SELECT * FROM employees;
        
        -- 字符串合并
        SELECT emp_no,first_name + last_name FROM employees;
        SELECT emp_no,CONCAT(first_name,' ',last_name) FROM employees;
        
        -- AS 定义别名，可选。写AS是一个好习惯
        SELECT emp_no as `no`,CONCAT(first_name,' ',last_name) name FROM employees emp;
5.  **Where 子句 条件**
    
    <table> 
     <thead> 
      <tr> 
       <th align="left">运算符</th> 
       <th align="left">描述</th> 
      </tr> 
     </thead> 
     <tbody> 
      <tr> 
       <td align="left"><code>=</code></td> 
       <td align="left">等于</td> 
      </tr> 
      <tr> 
       <td align="left"><code>&lt;&gt;</code></td> 
       <td align="left">不等于</td> 
      </tr> 
      <tr> 
       <td align="left"><code>&gt;、&lt;、&gt;=、&lt;=</code></td> 
       <td align="left">大于、小于、大于等于、小于等于</td> 
      </tr> 
      <tr> 
       <td align="left">BETWEEN</td> 
       <td align="left">在某个范围之内，between A and B 等价于[A,B]</td> 
      </tr> 
      <tr> 
       <td align="left">LIKE</td> 
       <td align="left">字符串模式匹配，<code>%</code>表示任意多个字符，<code>_</code>表示一个字符</td> 
      </tr> 
      <tr> 
       <td align="left">IN</td> 
       <td align="left">指定针对某个列的多个可能值</td> 
      </tr> 
      <tr> 
       <td align="left">AND</td> 
       <td align="left">与</td> 
      </tr> 
      <tr> 
       <td align="left">OR</td> 
       <td align="left">或</td> 
      </tr> 
     </tbody> 
    </table>
    
     *  注意：如果很多表达式需要使用AND、OR计算逻辑表达式的值的时候，由于结合律的问题，建议使用小括号来避免产生错误
    
        -- 条件查询
        SELECT * FROM employees WHERE emp_no<10015 and last_name LIKE 'P%';
        SELECT * FROM employees WHERE emp_no BETWEEN 10010 AND 10015 AND last_name LIKE 'P%';
        SELECT * FROM employees WHERE emp_no in (10001,10002,10010);
6.  **Order by子句 排序**
    
     *  对查询结果进行排序。
        
         *  升序ASC，默认是升序，可以不用写
         *  降序DESC
    
        -- 降序
        SELECT * FROM employees WHERE emp_no in (10001,10002,10010) ORDER BY emp_no DESC;
7.  **DISTINCT 去重**
    
     *  不返回重复记录
    
        -- DISTINCT 使用
        SELECT DISTINCT dept_no from dept_emp;
        SELECT DISTINCT emp_no from dept_emp;
        SELECT DISTINCT dept_no,emp_no from dept_emp;
8.  **Limit子句，分页**
    
        -- 返回5条记录
        SELECT * FROM employees emp LIMIT 5;
        
        -- 返回5条记录，偏移18条
        SELECT * FROM employees emp LIMIT 5 OFFSET 18;
        SELECT * FROM employees emp LIMIT 18,5;
9.  **聚合函数**
    
    <table> 
     <thead> 
      <tr> 
       <th align="left">函数</th> 
       <th align="left">描述</th> 
      </tr> 
     </thead> 
     <tbody> 
      <tr> 
       <td align="left">COUNT(expr)</td> 
       <td align="left">返回记录中记录的数目，如果指定列，则返回非NULL值的行数</td> 
      </tr> 
      <tr> 
       <td align="left">COUNT(DISTINCT expr,[expr…])</td> 
       <td align="left">返回不重复的非NULL值的行数</td> 
      </tr> 
      <tr> 
       <td align="left">AVG([DISTINCT] expr)</td> 
       <td align="left">返回平均值，返回不同值的平均值</td> 
      </tr> 
      <tr> 
       <td align="left">MIN(expr),MAX(expr)</td> 
       <td align="left">最小值，最大值</td> 
      </tr> 
      <tr> 
       <td align="left">SUM([DISTINCT] expr)</td> 
       <td align="left">求和，DISTINCT返回不同值求和</td> 
      </tr> 
     </tbody> 
    </table>
    
        -- 聚合函数
        SELECT COUNT(*),AVG(emp_no),SUM(emp_no),MIN(emp_no),MAX(emp_no) FROM employees;
10. **分组查询**
    
     *  使用Group by子句，如果有条件，使用Having子句过滤分组、聚合过滤的结果。
    
        -- 聚合所有
        SELECT emp_no,SUM(salary),AVG(salary),COUNT(emp_no) from salaries;
        -- 聚合被选择的记录
        SELECT emp_no,SUM(salary),AVG(salary),COUNT(emp_no) from salaries WHERE emp_no <10003;
        
        -- 分组
        SELECT emp_no FROM salaries GROUP BY emp_no;
        SELECT emp_no FROM salaries WHERE emp_no < 10003 GROUP BY emp_no;
        
        -- 按照不同emp_no分组，每组分别聚合
        SELECT emp_no,SUM(salary),AVG(salary),COUNT(emp_no) from salaries WHERE emp_no < 10003 GROUP BY emp_no;
        
        -- HAVING子句对分组结果过滤
        SELECT  emp_no,SUM(salary),AVG(salary),COUNT(emp_no) from salaries GROUP BY emp_no HAVING AVG(salary) > 45000;
        
        --使用别名
        SELECT emp_no,SUM(salary),AVG(salary) AS sal_avg,COUNT(emp_no) FROM salaries GROUP BY emp_no HAVING sal_avg > 60000;
        
        -- 最后对分组过滤后的结果排序
        SELECT emp_no,SUM(salary),AVG(salary) AS sal_avg,COUNT(emp_no) FROM salaries GROUP BY emp_no HAVING sal_avg > 60000 ORDER BY sal_avg;
    
     *  分组是将数据按照指定字段分组，最终每组只能出来一条记录，这就带来了问题，每一组谁做代表，其实谁做代表都不合适。如果只投影分组字段、聚合数据，不会有问题，如果投影非分组字段，显示的时候不能确定是组内谁的数据。
    
        -- 分组
        SELECT emp_no, MAX(salary) FROM salaries; -- 10001 88958
        SELECT emp_no, MIN(salary) FROM salaries; -- 10001 40006
    
     *  上例很好的说明了使用聚合函数，虽然没有显式使用Group By 语句，但是其实就是把所有记录当做一个组，每组只能出一条，那么一组也只能出一条，所以结果就一条。
     *  但是emp\_no就是非分组字段，那么它就要开始覆盖，所以，显示为10001。当求最大值的时候，正好工资表中10001的工资最高，感觉是对的。但是，求最小工资的时候，明明最小工资是10003的40006，由于emp\_no不是分组字段，导致最后覆盖为10001。
    
        SELECT emp_no MIN(salary) FROM salaries GROUP BY emp_no;
    
     *  上句才是正确的语义，按照不同员工emp\_no工号分组，每一个人一组，每一个人有多少个工资记录，按时每组只能按照人头出一条记录。
    
        -- 单表较为复杂的语句
        SELECT
            emp_no,
            avg(salary) AS avg_salary
        FROM
            salaries
        WHERE
            salary > 70000
        GROUP BY
            emp_no
        HAVING
            avg_salary > 50000
        ORDER BY
            avg_salary DESC
        LIMIT 1;
11. **子查询**
    
     *  子查询语句可以嵌套，内部查询就是子查询。
     *  子查询必须在一组小括号中。
     *  子查询中不能使用Order by。
    
        -- 子查询
        SELECT * FROM employees WHERE emp_no in (SELECT emp_no from employees WHERE emp_no > 10015) ORDER BY emp_no DESC;
        
        SELECT emp.emp_no,emp.first_name,gender FROM (SELECT * FROM employees WHERE emp_no > 10015) AS emp WHERE emp.emp_no < 10019 ORDER BY emp_no DESC;
12. **链接查询join**
    
     *  **交叉链接cross join**也称为：笛卡尔乘积，全部交叉
        
         *  在Mysql中，CROSS JOIN从语法上说与INNER JOIN等同
         *  join会构建一张临时表
    
        -- 工资40行
        SELECT * FROM salaries;
        -- 20行
        SELECT * FROM employees;
        
        -- 800行
        SELECT * FROM employees CROSS JOIN salaries;
        
        --隐试链接，800行
        SELECT * FROM employees, salaries;
    
     *  注意： salaries和employees并没有直接的关系，做笛卡尔乘积只是为了看的清楚
     *  **内连接 inner join 可以省略为join**
     *  **等值链接(join … on)**，只选择某些field相等的元组(行)，使用On限定关联的结果
     *  **自然链接(NATURAL JOIN)**，特殊的等值链接，会去掉重复的列。用的少
    
        -- 内连接，笛卡尔乘积 800行
        SELECT * FROM employees JOIN salaries;
        SELECT * FROM employees INNER JOIN salaries;
        
        -- ON等值链接 40行
        SELECT * FROM employees JOIN salaries ON employees.emp_no = salaries.emp_no;
        
        -- 自然链接，去掉了重复列，且自行使用employees.emp_no = salaries.emp_no的条件
        SELECT * from employees NATURAL JOIN salaries;
    
     *  **外链接** outer join，可以省略为join
        
         *  分为左外链接，即**左链接**；右外链接，即**右链接**；**全外链接**
    
        -- 左链接
        SELECT * FROM employees LEFT JOIN salaries ON employees.emp_no = salaries.emp_no;
        
        -- 有链接
        SELECT * FROM employees RIGHT JOIN salaries ON employees.emp_no = salaries.emp_no;
        
        -- 这个右链接等价于上面的左链接
        SELECT * FROM salaries RIGHT JOIN employees on employees.emp_no = salaries.emp_no;
    
    `SELECT * FROM employees RIGHT JOIN salaries ON employees.emp_no = salaries.emp_no;`
    
     *  结果是先employees后salaries的字段显示，Right是看表的数据的方向，从salaries往employees看，以salaries为准，它的所有数据都显示
     *  **自链接**：表，自己和自己链接
    
        select manager.* from emp manager,emp worker where manager.empno = worker.mgr and worker.empno = 1;
        select manager.* from emp manager inner join emp worker on manager.empno = worker.mgr where worker.empno = 1;

## 存储过程、触发器 ##

*  存储过程（Stored Procedure），数据库系统中，一段完成特定功能的SQL语句。编写成类似函数的方式，可以传 参并调用。支持流程控制语句。
 *  触发器（Trigger），由事件触发的特殊的存储过程，例如insert数据时触发。

这两种技术，虽然是数据库高级内容，性能不错，但基本很少用了。它们移植性差，使用时占用的服务器资源，排错、维护不方便。最大的原因，不太建议把逻辑放在数据库中。

## 事务Transaction ##

*  InnoDB引擎，支持事务。
 *  事务，由诺干条语句组成的，指的是要做的一系列操作。
 *  关系型数据库中支持事务，必须支持其四个属性(ACID)：

<table> 
 <thead> 
  <tr> 
   <th align="left">特性</th> 
   <th align="left">描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">原子性(atomicity)</td> 
   <td align="left">一个事务是一个不可分割的工作单位，事务中包括的所有操作要么全部做完，要么什么都不做</td> 
  </tr> 
  <tr> 
   <td align="left">一致性(consistency)</td> 
   <td align="left">事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的</td> 
  </tr> 
  <tr> 
   <td align="left">隔离性(isolation)</td> 
   <td align="left">一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰</td> 
  </tr> 
  <tr> 
   <td align="left">持久性(durability)</td> 
   <td align="left">持久性也称为永久性(permanence),指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响</td> 
  </tr> 
 </tbody> 
</table>

*  **原子性**：要求事务中的所有操作，不可分割，不能做了一部分操作，还剩一部分操作；
 *  **一致性**：多个事务并行执行的结果，应该和事务排队执行的结果一致。如果事务的并行执行和多线程读写共享资源一样不可预期，就不能保证一致性。
 *  **隔离性**：就是指多个事务访问共同的数据了，应该互不干扰。隔离性，指的是究竟在一个书屋处理期间，其他事务能不能访问的问题。
 *  **持久性**：比较好理解，就是事务提交后，数据不能丢失。
 *  MySQL的隔离级别  
    隔离性不好，事务的操作就会互相影响，带来不同严重程度的后果。隔离性好不好会带来如下问题：
    
    1.  **更新丢失Lost Update**
        
         *  事务A和B，更新同一个数据，它们都读取了初始值100，A要减10，B要加100，A减去10后更新为90，B加100更新为200，A的更新丢失了，就像从来没有减过10一样。
    2.  **脏读**
        
         *  事务A和B，事务B读取到了事务A未提交的数据（这个数据可能是一个中间值，也可能事务A后来回滚事 务）。事务A是否最后提交并不关心。只要读取到了这个被修改的数据就是脏读。
    3.  **不可重复读Unrepeatable read**
        
         *  事务A在事务执行中相同查询语句，得到了不同的结果，不能保证同一条查询语句重复读相同的结果就是不可 以重复读。
         *  例如，事务A查询了一次后，事务B修改了数据，事务A又查询了一次，发现数据不一致了。
         *  注意，脏读讲的是可以读到相同的数据的，但是读取的是一个未提交的数据，而不是提交的最终结果。
    4.  **幻读Phantom read**
        
         *  事务A中同一个查询要进行多次，事务B插入数据，导致A返回不同的结果集，如同幻觉，就是幻读。
         *  数据集有记录增加了，可以看做是**增加了记录的不可重复读**。
 *  有了上述问题，数据库就必须要解决，提出了隔离级别。隔离级别由低到高，如下表

<table> 
 <thead> 
  <tr> 
   <th align="left">隔离级别</th> 
   <th align="left">描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">READ UNCOMMITTED</td> 
   <td align="left">读取到未提交的数据</td> 
  </tr> 
  <tr> 
   <td align="left">READ COMMITTED</td> 
   <td align="left">读取已经提交的数据，<strong>Oracle默认级别</strong></td> 
  </tr> 
  <tr> 
   <td align="left">REPEATABLEREAD</td> 
   <td align="left">可重复读，<strong>MySQL的默认隔离级别</strong></td> 
  </tr> 
  <tr> 
   <td align="left">SERIALIZABLE</td> 
   <td align="left">可串行化，事务间完全隔离，事务不能并发，只能串行执行</td> 
  </tr> 
 </tbody> 
</table>

*  隔离级别越高，串行 化越高，数据执行效率低；隔离级别越低，并行度越高，性能越高。
 *  隔离级别越高，当前事务处理的中间结果对其它事务 不可见程度越高。

-- 设置会话级或者全局隔离级别
    SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL
    {
       READ UNCOMMITTED | READ COMMITIED | REPEATABLE READ | SERIALIZABLE}
    
    -- 查询隔离级别
    SELECT @@global.tx_isolation;
    SELECT @@tx_isolation;
    
    SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
    SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    
    -- 禁止自动提交
    SET AUTOCOMMIT = 0

*  **SERIALIZABLE级别**，串行了，解决所有问题，但是大大降低了效率
 *  **REPEATABLE READ级别**,事务A中同一条查询语句返回同样的结果，就是可以重复读数据了。例如语句为(select \* from user)。解决的办法有：  
    1、 对select的数据加锁，不允许其他事务删除、修改操作  
    2、 第一次select的时候。对最后一次确切提交的事务的结果做快照，解决了不可重复读，但是有可能出现幻读。因为另一个事务可以增删数据。
 *  **READ COMMITTED级别**，在事务中，每次select可以读取到别的事务刚提交成功的新的数据。因为读到的是提交后的数据，解决了脏读，但是不能解决不可重复读和幻读的问题。因为其他事务前后修改了数据或增删了数据。
 *  **READ UNCOMMITTED级别**：能读取到别的事务还没有提交的数据，完全没有隔离性可言，出现了脏读，当前其它问题都可能出现。

### 事务语法 ###

*  START TRANSACTION或BEGIN开始一个事务，START TRANSACTION是标准SQL的语法。
 *  使用**COMMIT**提交事务后，变更成为永久变更。
 *  **ROLLBACK**可以在提交事务之前，回滚变更，事务中的操作就如同没有发生过一样(原子性)。
 *  SET AUTOCOMMIT语句可以禁用或启用默认的autocommit模式，用于当前连接。SET AUTOCOMMIT = 0禁用自动提交事务。如果开启自动提交，如果有一个修改表的语句执行后，会立即把更新存储到磁盘。

## 数据仓库和数据库的区别 ##

本质上来说没有区别，都是存放数据的地方。  
但是数据库关注数据的持久化、数据的关系，为业务系统提供支持，事务支持；  
数据仓库存储的数据是为了分析或者发掘而设计的表结构，可以存储海量数据。

*  数据库存储在线交易数据OLTP(联机事务处理OLTP,On-line Transaction Processing);数据仓库存储历史数据用于分析OLAP(联机分析处理OLAP,On-Line Analytical Processing)。
 *  数据库支持在线业务，需要频繁增删改查；数据仓库一般囤积历史数据支持用于分析的SQL,一般不建议删改。

## 其他概念 ##

1.  游标Cursor
    
     *  操作查询的结果集的一种方法。
     *  可以将游标当做一个指针，指向结果集中的某一行。