SQL基础

本文是在学习廖雪峰老师的SQL教程后，梳理总结的，仅供自己学习使用。
原文地址在：https://www.liaoxuefeng.com/wiki/1177760294764384

1. 数据模型

   1. 层次模型：以上下级的层次关系来组织数据，结果看起来向一棵树。
   2. 网状模型：每个数据节点和其他很多节点连接起来，结构看起来像城市之间的路网。
   3. 关系模型：可以看作一个二维表格，数据提通过行号和列号来唯一确定。

2. 数据类型

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

   通常来说，BIGINT能满足整数存储的求，VARCHAR(N)能满足字符串存储的需求.

3. 主要的关系数据库

   • 商用数据库，例如：Oracle，SQL Server，DB2等；
   • 开源数据库，例如：MySQL，PostgreSQL等；
   • 桌面数据库，以微软Access为代表，适合桌面应用程序使用；
   • 嵌入式数据库，以Sqlite为代表，适合手机应用和桌面程序。

4. SQL：结构化查询语言
   方言： 各个数据库支持的各自扩展的功能，Oracle扩展的SQL称为PL/SQL，MicroSoft的称为T-SQL。
   SQL语言操作数据库的能力：

   名称	说明
   DDL: Date Definition Language	定义数据，即创建表，删除表，修改表结构。DDL通常由数据库管理员操作
   DML: Data Manipululation Language	删除，添加，更新数据。应用程序对数据库操作
   DQL: Data Query Language	查询数据

   SQL语言关键字不区分大小写

5. MySQL
   MySQL本身是一个SQL接口，内部有许多数据引擎，常用的有：

   • InnoDB: 由Innobase Oy公司开发的一款支持事务的数据库引擎，2006年被Oracle收购；
   • MyISAM：MySQL早期集成的默认数据库引擎，不支持事务。

6. 关系模型
   Record:记录， 表的每一行，逻辑意义上的一个数据。
   Column:字段，同一个表的每一行记录都拥有相同的字段。字段为null只表示字段数据不存在。
   关系数据库表和表之间：一对多，多对多，一对一。（表中一行数据对应另一个表中多行数据）

7. 主键
   用于唯一区分不同记录的字段，原则：不使用任何业务相关的字段作为主键；记录一旦插入到表中，主键最好不要修改。
   一般将这个字段命名为id:

   • 自增整数类型：数据库在插入数据时自动为每一条记录分配一个自增整数。
   • 全局唯一DUID类型：全局唯一字符串作为主键，类似于8f55d96b-8acc-4636-8cb8-76bf8abc2f57。GUID算法通过网卡MAC地址、时间戳和随机数保证任意计算机在任意时间生成的字符串都是不同的。

   联合主键：允许通过多个字段唯一表示记录，只要不是所有的主键都重复即可，一般不使用。

8. 外键
   将数据与另一张表关联起来，这种列称为外键。通过定义外键约束实现：

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

   定义外键约束，关系数据库可以保证无法插入无效的数据。外键约束会降低数据库的性能，大部分互联网应用程序为了追求速度，并不设置外键约束，而是仅靠应用程序自身来保证逻辑的正确性。这种情况下，仅仅是一个普通的列，只是起到了外键的作用而已。
   删除外键约束：

   ALTER TABLE students
   DROP FOREIGN KEY fk_class_id

9. 索引
   对关系数据库中的某一列或多个列的值进行与排序的数据结构。使数据库不要扫描整个表，而是直接定位到复合条件的巨鹿，这样能加快查询速度。
   创建索引：

   ALTER TABLE studnets
   ADD INDEX idx_score(score)
   //idx_score是名称,使用score索引
   ADD INDEX idx_score_name(score,name)

   索引的效率取决于索引值是否散列，该列值越不相同，效率越高。可以对一个表建立多个索引，有点在于提高查询效率，缺点在插入，更新和删除记录时需要更改索引，会降低速度。

   对于主键来说，也是索引，且效率高，因为主键绝对唯一。

   唯一索引：具有唯一性约束，具有业务含义。

   ALTER TABLE students
   ADD UNIQUE INDEX uni_name(name)
   //只创建唯一约束
   ADD CONSTRAINT uni_name UNIQUE(name)

10. 创建表

    // 创建test数据库
    create database if not exists test;
    // 使用test数据库
    use test;
    // 删除classes和students表
    drop table if exists classes;
    drop table if exists students;
    //创建classes表
    create table classes (
    id bigint not null auto_increment,
    name varchar(100) not null,
    primary key (id)
    )  engine=innodb default charset=utf8; 
    -- 创建students表
    create table students (
    id bigint not null auto_increment,
    class_id varchar(100) not null,
    name varchar(100) not null,
    gender varchar(1) not null,
    score int not null,
    primary key (id)
    )  engine=innodb default charset=utf8;        
    //插入class记录
    insert into classes(id,name) values(1,'一班');
    insert into classes(id,name) values(2,'二班');
    //插入students记录
    insert into studnets(id,class_id,name,gender,score) values(1,1,'小明','M',90);
    insert into students (id, class_id, name, gender, score) values (2, 1, '小红', 'F', 95);
    insert into students (id, class_id, name, gender, score) values (3, 1, '小军', 'M', 88);
    // ok
    select 'ok' as 'result';

11. 查询

    select * from students;        
    //查询结果是一个二维表，包含列名和每一行的数据。
    select 100+200;   //用于计算
    select 1;  //测试是否连接

    条件查询
    SELECT * FROM <表名> WHERE <条件表达式>

    select * from students where score >= 80 and gender = 'M';
    select * from students where score >= 80 or gender = 'M';
    select * from students where not class_id = 2;
    select * from students where (score < 80 or score > 90) and gender = 'M';

    <>判断不相等
LIKE判断相似
name LIKE '%bc%' %表示任意字符

    投影查询：返回某些列的数据
    select id,name,score from students where gender='M';
    对结果排序：(先按score倒叙)
    select id,name,gender,score from students where class_id=1 order by score desc,name;
    分页显示结果
    LIMIT总是设定为pageSize；
    OFFSET计算公式为pageSize * (pageIndex - 1)。
    select id,name,gender,score from students order by score desc limit 3 offest 3;
    聚合查询
    select count(*) from students;
count(*)查询结果是一个二维表，这个二维表只有一行一列，列名字为count(*)。给列明设置一个别名select count(*) num from students;
    还有以下聚合函数：
    sum,avg,max,min;max()和min()会返回排序最后和最前的字符(非数值类型时)。

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

    分组：
    select class_id,count(*) num from students group by class_id;

    多表查询：
    “笛卡尔查询”，结果集市目标表的行数乘积，列数是列数之和。

    select 
    students.id sid,
    students.name,
    students.gender,
    students.score,
    classes.id cid,
    classes.name cname
    from
    students,
    classes;

    多表查询时，要使用表名.列名这样的方式来引用列和设置别名。允许给表设置一个别名：

    SELECT 
    s.id sid, s.name, s.gender, s.score, c.id cid, c.name cname
    FROM
    students s,
    classes c;

    连接：

    • 内连接

      SELECT 
      s.id,
      s.name,
      s.class_id,
      c.name class_name,
      s.gender,
      s.score
      FROM
      students s
        INNER JOIN
      classes c ON s.class_id = c.id;

    • 外连接RIGHT OUTER JOIN，LEFT OUTER JOIN，以及FULL OUTER JOIN。结果集中会以null填充其中一表不存在的字段。

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

    //一次插入多个值
    INSERT INTO students (class_id, name, gender, score) VALUES
    (1, '大宝', 'M', 87),
    (2, '二宝', 'M', 81);

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

14. 删除
    DELETE FROM <表名> WHERE ...;
MySQL Client的可执行程序是mysql，MySQL Server的可执行程序是mysqld。

15. 管理mysql

    show databases;   
    //information_schema、mysql、performance_schema和sys是系统库
    drop database test;
    use test;
    show tables;
    desc students;
    show create table students;    //查看创建命令
    drop table students;
    alter table students add column birth varchar(10) not null;
    alter table students change column birth birthday varchar(20) not null;
    alter table students drop column birthday;

16. 一些常用SQL语句
    插入或替换
    REPLACE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);
    插入或更新
    INSERT INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99) ON DUPLICATE KEY UPDATE name='小明', gender='F', score=99;
    插入或忽略
    INSERT IGNORE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);
    快照
    CREATE TABLE students_of_class1 SELECT * FROM students WHERE class_id=1;
    写入查询结果集
    INSERT INTO statistics (class_id, average) SELECT class_id, AVG(score) FROM students GROUP BY class_id;

17. 事务
    多条语句作为一个整体进行操作的功能，被称为数据库事务。数据库事务可以确保该事务范围内的所有操作全部成功或者失败。如果失败，不会对数据库有任何改动。
    数据库事务具有ACID四个特性：

    • A: Atomic，原子性，将所有SQL作为原子工作单元执行，要么全部执行，要么全部不执行。
    • C:Consistent，一致性，事务完成后所有数据状态都是一致的。
    • I: Isolation，隔离性，多个事务并发执行，每个事务做出的修改必须与其他事务隔离。
    • D: Duration，持久性，事务完成后，对数据的修改将被持久化存储。

    隐式事务：对于单挑语句，自动作为一个事务执行。
    显式事务：使用BEGIN开启一个事务，COMMIT提交一个事务。

    BEGIN;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
    COMMIT;  //提交事务
    ROLLBACK;  //事务失败，回滚事务

    隔离级别

    Isolation Level	脏读（Dirty Read）	不可重复读（Non Repetable Read）	幻读（Phantom Read）
    Read Uncommitted	是	是	是
    Read Committed	-	是	是
    Repeatable Read	-	-	是
    Serializable	-	-	-

    Read Uncommitted隔离级别最低。一个事务会读到另一个事务更新后但未提交的数据，如果另一个事务回滚，那么当前事务读到的数据就是脏数据，这就是脏读（Dirty Read）。
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

    在Read Committed隔离级别下，一个事务可能会遇到不可重复读（Non Repeatable Read）的问题。不可重复读是指，在一个事务内，多次读同一数据，在这个事务还没有结束时，如果另一个事务恰好修改了这个数据，那么，在第一个事务中，两次读取的数据就可能不一致。
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

    在Repeatable Read隔离级别下，一个事务可能会遇到幻读（Phantom Read）的问题。幻读是指，在一个事务中，第一次查询某条记录，发现没有，但是，当试图更新这条不存在的记录时，可以成功，更新成功后，在次读取，就出现了。

    Serializable是最严格的隔离级别。在Serializable隔离级别下，所有事务按照次序依次执行，因此，脏读、不可重复读、幻读都不会出现。虽然Serializable隔离级别下的事务具有最高的安全性，但是，由于事务是串行执行，所以效率会大大下降，应用程序的性能会急剧降低。在MySQL中，如果使用InnoDB，默认的隔离级别是Repeatable Read。