SQL 数据库简单使用

SQL 数据库

说明

看了这篇教程 SQL教程 – 廖雪峰 对 SQL 语言有了个初步的了解，以下是学习记录，推荐直接看这个教程，简单易懂，适合初学者

1. SQL(结构化查询语言): 用来访问操作数据库系统，学习SQL 一种语言就可以操作各种不同的数据库
2. SQL 语言定义的几种操作数据库能力：
   1. DDL(Data Definition Language)：允许用户定义数据，也就是创建表、删除表、修改表结构，一般由数据库管理员执行
   2. DML(Data Manipulation Language): 为用户提供添加、删除、变更数据的能力
   3. DQL(Data Query Language): 允许用户数据查询
3. SQL 语言关键字不区分大小写，但是为了通用性，最好总是大写，表名和列名小写
4. 关系模型
   1. 数据库是建立在关系模型上的，本质上是若干个存储数据的二维表
   2. 表的每一行称为记录 Record，每一列称为字段 Column , 字段定义了数据类型，以及是否允许 NULL, NULL 表示数据 不存在
   3. 通常情况下字段应该避免 允许 NULL，这样可以简化查询条件，加快查询速度
   4. 关系型数据库中，关系是通过主键和外键来维护的
   5. 主键：对于关系表，任意两条记录不能重复，重复的依据就是可以根据某个唯一字段区分，这个唯一字段就是主键
   6. 主键最好是完全业务无关的字段，一般把这个字段命名为id，一般 id 的类型有自增整数和GUID
   7. 联合主键，允许通过多个字段来唯一标识记录，这几个字段就是联合主键
   8. 通过某个字段，把一张表中的数据与另外一张表关联起来，这个字段称为外键
   9. 外键不是通过列名实现，而是通过 外键约束实现，外键约束，可以约束插入的外键的值是否存在，会消耗性能，因此很多企业只是通过程序逻辑自身来保证正确性，而不设置外键约束
   10. 外键约束：ALTER TABLE stu ADD CONSTRAINT fk_class_id FOREIGN KEY (class_id) REFERENCES classes (id); 给 stu 这个表创建一个名为 fk_class_id 的约束，这个约束关联了当前表（stu）的 class_id 字段到 classes 这个表的 id 字段
   11. 删除外键约束：ALTER TABLE stu DROP FOREIGN KEY fk_class_id;
   12. 表与表之间可以有三种关系
       1. 一对一：为提高查询速度，将一个大表拆分为经常查询的基本信息和不怎么查询的详细信息
       2. 一对多：大部分
       3. 多对多：通常用来关联两个一对多关系
   13. 索引: 用来在查找记录的时候，获得更快的速度，但是插入、更新、删除记录的操作会减慢，没有索引，一样可以正常执行查询
   14. 添加索引：ALTER TABLE students ADD INDEX idx_name (name); 给 students 表的 name 字段增加索引
   15. 删除索引：ALTER TABLE students DROP INDEX idx_name;
   16. 索引是针对某一列，或多个列的值进行预排序的数据结构，通过使用索引，使得数据库系统不用扫描整个表，而是直接定位到符合条件的记录
   17. 索引列的值越散列（差异性大），索引效率越高
   18. 唯一索引：不能出现两条以上同样的记录 ALTER TABLE students ADD UNIQUE INDEX idx_name (name);
   19. 只添加唯一约束而不添加索引：ALTER TABLE students CONSTRAINT uni_name UNIQUE (name);
5. 安装后登录 MySQL: 命令行执行 mysql -u root -p 输入密码后即可连接到 MySQL Server
   1. 执行 EXIT 即可断开与 MySQL Server 的链接
   2. MySQL Client 上编写的 SQL 通过 TCP 传递给 MySQL Server 执行， 默认端口号是 3306, 默认链接到本机的 127.0.0.1:3306
   3. 如果想要链接到远程的 MySQL Server, 可在执行登录命令前加上 ip mysql 10.0.1.99 -u root -p
6. MySQL 日常管理命令
   1. 创建数据库 CREATE DATABASE test1;
   2. 删除数据库 DROP DATABASE test1;
   3. 将要操作数据库是，需将数据库切换为当前数据库 USE test1;
   4. 列出当前数据库所有的表 SHOW TABLES; // classes students
   5. 查看表结构 DESC classes;
   6. 查看创建表的 SQL 脚本 SHOW CREATE TABLE students;
   7. 创建表 CREATE TABLE sts (id bigint NOT NULL, name VARCHAR(100));
   8. 删除表 DROP TABLE sts;
   9. 添加列 ALTER TABLE sts ADD COLUMN birth VARCHAR(10) NOT NULL;
   10. 更改列 ALTER TABLE sts CHANGE COLUMN birth birth VARCHAR(20);
   11. 删除列 ALTER TABLE sts DROP COLUMN birth;
7. 修改数据
   1. 插入一条记录：INSERT INTO students (id, class_id, name, gender, score) VALUES (13, 1, '小黑', 'B', 66); 如果一个字段有默认值，或者是自增主键，则可以不再插入语句中体现，例如上句中的 id 字段可以不写，数据库可以自己推断出来；另外可以在 VAlUES 子句中添加多个记录，例如：INSERT INTO students (class_id, name, gender, score) VALUES (3, '小A', 'C', 80), (2, '大A', 'B', 90);
   2. 更新一条记录: UPDATE students SET name="小AA", gender="C" WHERE id=15; 后边的 WHERE 是筛选条件，符合条件的都会被更新
      1. WHERE 部分可以是一个范围 UPDATE students SET name="小飞" WHERE id>=1 AND id<=3;
      2. 更新字段时可以使用表达式 UPDATE students SET score=score+5 WHERE gender="B";
      3. 如果不写 WHERE 条件则所有的记录都被更新
   3. 删除一条记录：DELETE FROM students WHERE gender="B";
      1. 如果不写 WHERE 条件则所有的记录都被删除
      2. 与 UPDATE 一样 更新字段时 WHERE 后面可以是一个范围
8. 查询数据
   1. 查询数据表的数据：SELECT * FROM students; 其中 * 表示所有列
   2. SELECT 语句除了查询，还可以计算，这个计算功能通常被用作测试数据库链接 SELECT 1;
   3. 条件查询：SELECT * FROM students WHERE score>=90; 查询 students 表中所有 score >= 90 的记录;
      1. 并列条件 AND(同时满足)：SELECT * FROM students WHERE score >= 90 AND gender="F";
      2. 或者条件 OR(满足其一)：SELECT * FROM students WHERE score >= 90 OR gender="F";
      3. “非”条件 NOT: SELECT * FROM students WHERE NOT class_id=2; 等价于 WHERE class_id <>2; 所以 NOT 不常用
      4. 相似判断 LIKE: SELECT * FROM students WHERE name LIKE '小%'; 查询 students 中 name 字段值类似 “小X” 的记录，% 表示任意字符
      5. 组合条件 通过 () 实现：SELECT * FROME students WHERE (name LIKE '小%' AND score < 90) OR gender='F';
   4. 投影查询：只返回查询值的某些列 SELECT id, name, class_id FROM students;
      1. 投影查询还可以改变输出的字段顺序以及字段名称 SELECT name, id s_id, class_id FROM students;
      2. 投影查询可以会条件查询结合使用
   5. 查询排序：默认按照主键 ASC 正序排列
      1. 按照其他条件排序：SELECT * FROM students ORDER BY score;
      2. 倒序排列：SELECT * FROM students ORDER BY score DESC; DESC 即为倒序，正序是 ASC
      3. 多条件排序：SELECT * FROM students ORDER BY score, class_id; 通过 , 隔开， 先按照 score 排序，有 score 相同的再按照 class_id 排序
   6. 分页查询：SELECT * FROM students LIMIT 3 OFFSET 0; 从 0 开始，返回查询出的 3 条数据
      1. 理论上分页查询：LIMIT pageSize OFFSET pageSize * (pageIndex - 1) (伪代码)
      2. OFFSET 是可以省略的 例如 SELECT * FROM students LIMIT 3, 0; 用 , 隔开即可
      3. 随着 OFFSET 的值越大，查询效率越低
   7. 聚合查询：就是使用 SQL 的聚合函数查询，可以快速的获得结果
      1. 查询数据量：SELECT COUNT(*) num FROM students WHERE gender='F'; 查询 gender=F 的数据个数，并重命名为 num
      2. 其他聚合方法 SELECT AVG(score) avg FROM students WHERE gender='F'; 取 score 字段的平均值，类似的聚合方法有很多 MAX（取最大）MIN（取最小）SUM（求和）等
      3. 聚合查询结果分组：SELECT class_id, gender, COUNTS(*) FROM students GROUP BY class_id, gender; 按照 class_id 以及 gender 分组，只有这两个字段可以作为输出表的字段，其他字段因不具备分组而不能展示
   8. 多表查询：
      1. 多表查询可获得表的乘积 SELECT * FROM students, classes; 返回的是两个表的每一行两两拼接的结果
      2. 多表查询返回结果的字段可能会有两个表重名的情况，可以使用重命名来区分 SELECT students.id s_id, students.name, classes.id c_id, classes.name c_name FROM students, classes;
   9. 链接查询：是另一种类型的多表查询，先确定一个主表结构，然后通过 JOIN 运算，将其他表中的数据有选择的链接到主表的结果集上
      1. 内连接 INNER JOIN： SELECT s.id, s.name, s.gender, s.score, s.class_id, c.name class_name FROM students s INNER JOIN classes ON s.class_id = c.id; 以 students 为主表 取别名 s，根据 students 的 class_id 字段，找到 classes 表中的对应记录，将其 name 字段的值，作为结果集的 class_name 字段展示
      2. 除了 INNER JOIN, 还有 RIGHT OUTER JOIN 等链接方式，具体差别如下：
         1. INNER JOIN：只返回同时存在两张表的行数据
         2. RIGHT OUTER JOIN：返回右表都存在的行，如果左侧表没有对应数据则会用 NULL 填充剩下字段
         3. LEFT OUTER JOIN： 返回左表都存在的行，如果右侧表没有对应数据则会用 NULL 填充剩下字段
         4. FULL OUTER JOIN，它会把两张表的所有记录全部选择出来，并且，自动把对方不存在的列填充为NULL
9. 实用语句：
   1. 插入或者替换：REPLACE INTO students (id, class_id, name, gender, score) VALUES (7, 3, '小林', 'F', 99); 如上语句，如id=7 的记录不存在则创建一条记录，如果存在则删除后创建一条记录
   2. 插入或者更新：INSERT INTO students (id, class_id, name, gender, score) VALUES (7, 3, '小林', 'F', 99) ON DUPLICATE KEY UPDATE name='小非林'; 如上语句，如果没有 id=7 的记录则创建 (7, 3, ‘小林’, ‘F’, 99)，有的话则更新 name=‘小非林’ 字段
   3. 插入或者忽略：INSERT IGNORE INTO students (id, class_id, name, gender, score) VALUES (7, 3, '小林', 'F', 99); 如果有 id=7 的记录则忽略这一次插入，如果没有则创建一个记录
   4. 结合查询结果创建一个新表：CREATE TABLE stu_copy SELECT id, name, gender FROM students WHERE score >=85; 从 students 中摘取 score >= 85 的记录，以 id, name, gender 作为字段创建 stu_copy 表
   5. 将查询结果写入已存在的表中：INSERT INTO avg_list (class_id, avg) SELECT class_id, AVG(score) FROM students GROUP BY class_id;
10. 事务
11. 事务就是把多条语句当做一个整体进行操作的功能
12. 数据库事务的 ACID 特性
    1. A(Atomic): 原子性，将所有 SQL 当做原子单元执行，要么全部执行，要么全部不执行
    2. C(Consistent): 一致性，事务完成后所有数据状态都是一致的
    3. I(Isolation)：隔离性，如果有多个事务并发执行，每个事务做出的修改必须与其他事务隔离
    4. D(Duration)：持久性，事务完成后，对数据库的修改被持久化存储
13. 隐式事务：即为单条 SQL 语句，数据库会将其当做一个事务执行
14. 显式事务：由 BEGIN 开始，由 COMMIT 提交结束, 可以用 ROLLBACK 回滚事务到 BEGIN 之前
15. 隔离级别：两个并发的事务，如果涉及到操作同一条记录时，可能会发生数据不一致的问题，例如脏读、不可重复读、幻读等。数据库提供了隔离级别，来有针对性的处理数据不一致的问题
    1. 脏读：一个事务会读到另一个事务更新但是未提交的数据，如果另一个事务回滚，那么当前事务读取的就是脏数据
    2. 不可重复读：一个事务中多次读取同一数据，在这个事务还没有结束时，另一个事务改了相应的数据，这样第一个事务两次读取的数据可能不一致
    3. 幻读：一个事务中查询某个记录，发现没有，另外一个事务中操作了这条记录，这样在第一个事务后续操作中竟然可以操作这个之前不存在的记录
    4. 隔离级别 - Read Uncommitted：会有脏读、不可重复读、幻读的情况
    5. 隔离级别 - Read Committed：会有不可重复读、幻读的情况, (脏读的情景下，后一个事务读到的是前一个事务更新前的记录)
    6. 隔离级别 - Repeatable Read：会有幻读的情况 (不可重复读的情景下，前一个事务提交后，后一个事务读到的还是前一个事务提交前的记录)
    7. 隔离级别 - Serializable：事务依次执行，不会出现上述状况，但是效率较低