SQL基本使用

关系型数据库

  • 数据库：保存用户数据

    • 随着应用程序的功能越复杂，数据量越大，管理这些数据就成了大问题
      • 读写文件并解析出数据需要大量重复代码；
      • 从成千上万的数据中快速查询出指定数据需要复杂的逻辑

  • 数据模型

    • 层次模型：以“上下级”的层次关系来组织数据的一种方式，看起来就像一颗树
    • 网状模型：每个数据节点和其他很多节点都连接起来，看起来就像很多城市之间的路网
    • 关系模型：数据看作是一个二维表格，任何数据都可以通过行号+列号来唯一确定，看起来就是一个Excel表

  • 常见数据类型

    • 名称类型说明
      INT整型4字节整数类型，范围约+/-21亿
      BIGINT长整型8字节整数类型，范围约+/-922亿亿
      REAL浮点型4字节浮点数，范围约+/-1038
      DOUBLE浮点型8字节浮点数，范围约+/-10308
      DECIMAL(M,N)高精度小数由用户指定精度的小数，例如，DECIMAL(20,10)表示一共20位，其中小数10位，通常用于财务计算
      CHAR(N)定长字符串存储指定长度的字符串，例如，CHAR(100)总是存储100个字符的字符串
      VARCHAR(N)变长字符串存储可变长度的字符串，例如，VARCHAR(100)可以存储0~100个字符的字符串
      BOOLEAN布尔类型存储True或者False
      DATE日期类型存储日期，例如，2018-06-22
      TIME时间类型存储时间，例如，12:20:59
      DATETIME日期和时间类型存储日期+时间，例如，2018-06-22 12:20:59

  • SQL

    • 结构化查询语言的缩写，用来访问和操作数据库系统。SQL语句既可以查询数据库中的数据，也可以添加、更新和删除数据库中的数据，还对数据库进行管理和维护操做
    • SQL语言关键字不区分大小写！！！但表名和列名不一定

  • MySQL本身只是一个SQL接口，内部包含多种数据引擎

    • InnoDB：一款支持事务的数据库引擎
    • MyISAM：MySQL早期默认数据库引擎，不支持事务

关系模型

  • 表的每一行称为记录（Record），记录是一个逻辑意义上的数据。

    表的每一列称为字段（Column），同一个表的每一行记录都拥有相同的若干字段。

  • 主键

    • 记录一旦插入到表中，主键最好不要再修改，因为主键是用来唯一定位记录的，修改了主键，会造成一系列的影响
    • 主键的一个基本原则：不使用任何业务相关的字段作为主键，也不应该允许NULL
    • 一般字段命名为id ，常见类型有
      • 自增整数类型：数据库会在插入数据时自动为每一条记录分配一个自增整数，这样我们就完全不用担心主键重复，也不用自己预先生成主键
      • 全局唯一GUID类型：也称UUID，使用一种全局唯一的字符串作为主键，类似8f55d96b-8acc-4636-8cb8-76bf8abc2f57。GUID算法通过网卡MAC地址、时间戳和随机数保证任意计算机在任意时间生成的字符串都是不同的
    • 联合主键
      • 通过多个字段唯一标识记录，即两个或更多的字段都设置为主键

  • 外键

    • 在students表中，通过class_id的字段，可以把数据与另一张表关联起来，这种列称为外键

    • 定义外键约束，关系数据库可以保证无法插入无效的数据。即如果classes表不存在id=99的记录，students表就无法插入class_id=99的记录

    • 外键并不是通过列名实现的，而是通过定义外键约束实现的：

      ALTER TABLE students
ADD CONSTRAINT fk_class_id
FOREIGN KEY (class_id)
REFERENCES classes (id);

      外键约束的名称fk_class_id可以任意，FOREIGN KEY (class_id)指定了class_id作为外键，REFERENCES classes (id)指定了这个外键将关联到classes表的id列（即classes表的主键）

    • 删除一个外键约束，也是通过ALTER TABLE实现的：

      ALTER TABLE students
DROP FOREIGN KEY fk_class_id;

    • 删除外键约束并没有删除外键这一列。删除列是通过DROP COLUMN ...实现

    • 多对多：两个一对多关系实现的，通过一个中间表，关联两个一对多关系

    • 一对一：一个表的记录对应到另一个表的唯一一个记录

  • 索引

    • 关系数据库中对某一列或多个列的值进行预排序的数据结构。通过使用索引，可以让数据库系统不必扫描整个表，而是直接定位到符合条件的记录，这样就大大加快了查询速度

      使用ADD INDEX idx_score (score)就创建了一个名称为idx_score，使用列score的索引。索引名称是任意的，索引如果有多列，可以在括号里依次写上

    • 索引的效率取决于索引列的值是否散列，即该列的值如果越互不相同，那么索引效率越高

    • 优点提高查询效率，缺点是在插入、更新和删除记录时，需要同时修改索引

    • 关系数据库会自动对主键创建主键索引。使用主键索引的效率是最高的，因为主键会保证绝对唯一

    • 唯一索引：

      • 通过UNIQUE关键字我们就添加了一个唯一索引

        ALTER TABLE students
ADD UNIQUE INDEX uni_name (name);

      • 只对某一列添加一个唯一约束而不创建唯一索引

        ALTER TABLE students
ADD CONSTRAINT uni_name UNIQUE (name);

查询数据

  • 在MySQL命令行客户端输入SQL后，记得加一个;

  • 基本查询

    • SELECT查询的基本语句SELECT * FROM <表名>可以查询一个表的所有行和所有列的数据
    • 不带FROM子句的SELECT语句有一个有用的用途，就是用来判断当前到数据库的连接是否有效。许多检测工具会执行一条SELECT 1;

  • 条件查询

    • SELECT * FROM <表名> WHERE <条件表达式>
      • 用<条件1> AND <条件2>表达满足条件1并且满足条件2
      • 用<条件1> OR <条件2>，表示满足条件1或者满足条件2
      • 用NOT <条件>，表示“不符合该条件”的记录
      • 组合三个或者更多的条件，就需要用小括号()表示如何进行条件运算

  • 投影查询

    • what : 使用SELECT *表示查询表的所有列，使用SELECT 列1, 列2, 列3则可以仅返回指定列，这种操作称为投影

    • 可以给每一列起个别名，结果集的列名就可以与原表的列名不同。语法是SELECT 列1 别名1, 列2 别名2, 列3 别名3 FROM ...

  • 排序

    • 根据其他条件排序，可以加上ORDER BY子句

      • ASC：升序，即从小到大。ASC可以省略
      • DESC：倒序

    • 有相同的数据，要进一步排序，可以继续添加列名

    • 有WHERE子句，那么ORDER BY子句要放到WHERE子句后面

  • 分页查询

    • why : 结果集数据量很大，比如几万行数据，放在一个页面显示的话数据量太大，不如分页显示

    • 查询可以通过LIMIT <N> OFFSET <M>子句实现 M从0开始

      • LIMIT N表示是最多N条记录。OFFSET <M>表示是从M号记录开始
      • 结果集分页，每页3条记录。获取第1页的记录，可以使用LIMIT 3 OFFSET 0
      • 要查询第2页，把OFFSET设定为3，LIMIT 3 OFFSET 3
      • 查询第2~4行，命令为 LIMIT 3 OFFSET 1简写为LIMIT 1,3

    • OFFSET超过了查询的最大数量并不会报错，而是得到一个空的结果集

    • 重点：

      • OFFSET是可选的，如果只写LIMIT 15，那么相当于LIMIT 15 OFFSET 0。

      • 在MySQL中，LIMIT 15 OFFSET 30还可以简写成LIMIT 30, 15。

      • 使用LIMIT <M> OFFSET <N>分页时，随着N越来越大，查询效率也会越来越低

  • 聚合查询

    • 对于统计总数、平均数这类计算，SQL提供了专门的聚合函数，使用聚合函数进行查询

      • 使用聚合查询时，我们应该给列名设置一个别名，便于处理结果
      • COUNT(*)和COUNT(id)实际上是一样的效果,查询所有列的行数
    • 函数说明
      SUM计算某一列的合计值，该列必须为数值类型
      AVG计算某一列的平均值，该列必须为数值类型
      MAX计算某一列的最大值
      MIN计算某一列的最小值

      MAX()和MIN()函数并不限于数值类型。如果是字符类型，MAX()和MIN()会返回排序最后和排序最前的字符

    • 如果聚合查询的WHERE条件没有匹配到任何行，COUNT()会返回0，而SUM()、AVG()、MAX()和MIN()会返回NULL

    • 分组

      • GROUP BY子句指定了按 该字段 分组
      • 执行该SELECT语句时，会把该字段相同的列先分组，再分别计算
      • 也可以使用多个列逐个进行分组

  • 多表查询

    • 获取M x N行记录，结果集可能非常巨大，要小心使用

  • 连接查询

    • 对多个表进行JOIN运算，先确定一个主表作为结果集，再把其他表的行有选择性地“连接”在主表结果集上

      • 注意INNER JOIN查询的写法是：
        1. 先确定主表，仍然使用FROM <表1>的语法；
        2. 再确定需要连接的表，使用INNER JOIN <表2>的语法；
        3. 然后确定连接条件，使用ON <条件...>，这里的条件是s.class_id = c.id，表示students表的class_id列与classes表的id列相同的行需要连接；
        4. 可选：加上WHERE子句、ORDER BY等子句。

    • 区分

      • INNER JOIN只返回同时存在于两张表的行数据
      • RIGHT OUTER JOIN返回右表都存在的行
      • LEFT OUTER JOIN则返回左表都存在的行
      • FULL OUTER JOIN，把两张表的所有记录全部选择出来，并且，自动把对方不存在的列填充为NULL

    • 假设查询语句是：

      SELECT ... FROM tableA ??? JOIN tableB ON tableA.column1 = tableB.column2;

      我们把tableA看作左表，把tableB看成右表，那么INNER JOIN是选出两张表都存在的记录：

      inner-join

      LEFT OUTER JOIN是选出左表存在的记录：

      left-outer-join

      RIGHT OUTER JOIN是选出右表存在的记录：

      right-outer-join

      FULL OUTER JOIN则是选出左右表都存在的记录：

      full-outer-join

    • 总结

      • JOIN查询需要先确定主表，然后把另一个表的数据“附加”到结果集上；
      • INNER JOIN是最常用的一种JOIN查询，它的语法是SELECT ... FROM <表1> INNER JOIN <表2> ON <条件...>；

修改数据

  • 语句使用

    • 增：INSERT：插入新记录；

    • 改：UPDATE：更新已有记录；

    • 删：DELETE：删除已有记录；

  • 插入数据

    • INSERT语句的基本语法是：

      INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...);

    • 还可以一次性添加多条记录，只需要在VALUES子句中指定多个记录值，每个记录是由(...)包含的一组值，每组值用逗号,分隔

  • 更新数据

    • UPDATE语句的基本语法是：

      UPDATE <表名> SET 字段1=值1, 字段2=值2, ... WHERE ...;

    • 要特别小心的是，UPDATE语句可以没有WHERE条件，例如：

      UPDATE students SET score=60;

      这时，整个表的所有记录都会被更新

  • 删除数据

    • DELETE语句的基本语法是：

      DELETE FROM <表名> WHERE ...;

    • DELETE FROM students WHERE id=1; 删除id=1的记录

MySQL

  • MYSQL

    • MySQL Server：真正的MySQL服务器

    • MySQL Client：命令行客户端，可以通过MySQL Client登录MySQL，然后，输入SQL语句并执行

    • 输入命令mysql -u root -p，就连上了MySQL Server

    • 输入exit断开与MySQL Server的连接并返回到命令提示符

    • 也可安装MySQL Client，连接远程MySQL Server。假设远程MySQL Server的IP地址是10.0.1.99，那么就使用-h指定IP或域名：

      mysql -h 10.0.1.99 -u root -p

  • 管理

    • 列出所有数据库：mysql> SHOW DATABASES;

      • information_schema、mysql、performance_schema和sys是系统库，不要去改动它们

    • 创建一个新数据库：

      mysql> CREATE DATABASE test;

    • 删除一个数据库：

      mysql> DROP DATABASE test;

    • 对一个数据库进行操作时，要首先将其切换为当前数据库：

      mysql> USE test;

    • 列出当前数据库的所有表：

      mysql> SHOW TABLES;

    • 要查看一个表的结构：可以知道表的字段结构

      mysql> DESC students;

    • 使用以下命令查看创建表的SQL语句：可以知道表的字段结构

      mysql> SHOW CREATE TABLE students;

    • 创建表使用CREATE TABLE语句，而删除表使用DROP TABLE语句：

      mysql> DROP TABLE students;

    • 要给students表新增一列birth，使用：

      ALTER TABLE students ADD COLUMN birth VARCHAR(10) NOT NULL;

    • 修改birth列，例如把列名改为birthday，类型改为VARCHAR(20)：

      ALTER TABLE students CHANGE COLUMN birth birthday VARCHAR(20) NOT NULL;

    • 删除列：

      ALTER TABLE students DROP COLUMN birthday;

    • EXIT命令退出MySQL：

      mysql> EXIT

      注意EXIT仅仅断开了客户端和服务器的连接，MySQL服务器仍然继续运行。

  • 实用快捷实用

    • 我们知道如何选择索引，可以使用FORCE INDEX强制查询使用指定的索引

      SELECT * FROM students FORCE INDEX (idx_class_id) WHERE class_id = 1 ORDER BY id DESC;

实用技巧以后再更，不会影响正常实用

事务

  • 某些业务要求，一系列操作必须全部执行，而不能仅执行一部分

    • 特性·

      • A：Atomicity，原子性，将所有SQL作为原子工作单元执行
      • C：Consistency，一致性，事务完成后，所有数据的状态都是一致的
      • I：Isolation，隔离性，如果有多个事务并发执行，每个事务作出的修改必须与其他事务隔离
      • D：Durability，持久性，即事务完成后，对数据库数据的修改被持久化存储

    • 单条SQL语句，数据库系统自动将其作为一个事务执行，这种事务被称为隐式事务

    • 手动把多条SQL语句作为一个事务执行，使用BEGIN开启一个事务，使用COMMIT提交一个事务，这种事务被称为显式事务

    • 希望主动让事务失败，这时，可以用ROLLBACK回滚事务，整个事务会失败

      BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
ROLLBACK;

  • 隔离级别

    • 两个并发执行的事务，如果涉及到操作同一条记录的时候，可能会发生问题。因为并发操作会带来数据的不一致性，包括脏读、不可重复读、幻读

    • 在MySQL中，如果使用InnoDB，默认的隔离级别是Repeatable Read

    • SQL标准定义了4种隔离级别，分别对应可能出现的数据不一致的情况：

      Isolation Level脏读（Dirty Read）不可重复读（Non Repeatable Read）幻读（Phantom Read）
      Read UncommittedYesYesYes
      Read Committed-YesYes
      Repeatable Read--Yes
      Serializable---

  • Read Uncommitted

    • 一个事务会读到另一个事务更新后但未提交的数据

  • Read Committed

    • 一个事务内，多次读同一数据，在这个事务还没有结束时，如果另一个事务恰好修改了这个数据，在第一个事务中，两次读取的数据就可能不一致

  • Repeatable Read

    • 在一个事务中，第一次查询某条记录，发现没有，但是，当试图更新这条不存在的记录时，竟然能成功，并且，再次读取同一条记录，它就神奇地出现

  • Serializable

    • 所有事务按照次序依次执行，因此，脏读、不可重复读、幻读都不会出现