SQL学习记录

一、mysql学习踩坑记录

1.导入数据库报错ERROR 1366 (HY000) at line 29: Incorrect string value: '\x80\xE7\x8F\xAD' for column 'name' at row 1

解决办法：在数据库脚本中第一行加入set character set utf8;即可正常读取中文字符

参考：https://www.liaoxuefeng.com/discuss/1253602103632832/1269131233982560

2.查询数据库表数据报错ERROR 1046 (3D000): No database selected

解决办法：USE <database>切换数据库(这里的database是统称，需根据实际情况修改)

参考：MySql ERROR 1046(3D000): No Database Selected的解决办法

二、SQL相关知识点

来源：SQL教程

（一）查询数据

1.基本查询
  • 语法：SELECT * FROM <表名>
  • 使用SELECT * FROM students时，SELECT是关键字，表示将要执行一个查询，*表示“所有列”，FROM表示将要从哪个表查询，本例中是students表。该SQL将查询出students表的所有数据。注意：查询结果也是一个二维表，它包含列名和每一行的数据
  • SELECT可以用作计算(如SELECT 100+200;)，但它并不是``SQL的强项。但是，不带FROM子句的SELECT语句有一个有用的用途，就是用来判断当前到数据库的连接是否有效，可通过执行SELECT 1;`来测试数据库连接
2.条件查询
  • 语法：SELECT * FROM <表名> WHERE <条件表达式>，条件表达式可以用<条件1> AND <条件2>表达满足条件1并且满足条件2，也可以用<条件1> OR <条件2>，表示满足条件1或者满足条件2，还可以用NOT <条件>，表示“不符合该条件”，组合三个或者更多的条件，就需要用小括号()表示如何进行条件运算(如果不加括号，条件运算按照NOT、AND、OR的优先级进行，即NOT优先级最高，其次是AND，最后是OR。括号可以改变优先级)
  • SELECT语句通过WHERE条件来设定查询条件，查询结果是满足查询条件的记录，例如SELECT * FROM students WHERE score >= 80就是查询 指定条件“分数在80分或以上的学生”
常用的条件表达式
条件 表达式举例1 表达式举例2 说明
判断相等= score = 80 name = ‘abc’ 字符串需要用单引号括起来
判断大于> score > 80 name > ‘abc’ 英文字符比较根据ASCII码，中文字符比较根据数据库设置
判断大于或相等>= score >= 80 name >= ‘abc’ -
判断小于< score < 80 name <= ‘abc’ -
判断小于或相等<= score <= 80 name <= ‘abc’ -
判断不相等<> score <> 80 name <> ‘abc’ -
判断相似LIKE name LIKE ‘ab%’ name LIKE ‘%bc%’ %表示任意字符，例如’ab%’将匹配’ab’，’abc’，’abcd’
3.投影查询
  • 只返回某些列的数据，而不是所有列的数据，我们可以用SELECT 列1, 列2, 列3 FROM ...，让结果集仅包含指定列，这种操作称为投影查询；使用SELECT 列1 别名1, 列2 别名2, 列3 别名3 FROM ...给每一列起个别名，这样结果集的列名就可以与原表的列名不同
  • 除列的别名外还可以给表设置别名，设置方法同列别名的设置
4.排序

使用SELECT查询时，查询结果集通常是按照id(主键)排序的。这也是大部分数据库的做法

  • 根据其他条件排序则通过加上ORDER BY实现，例如SELECT id, name, gender, score FROM students ORDER BY score;按成绩从低到高排序，若要按照成绩从高到底排序，加上DESC实现”倒序”
  • 如果score列有相同的数据，要进一步排序，可以继续添加列名。例如，使用ORDER BY score DESC, gender表示先按score列倒序，如果有相同分数的，再按gender列排序
  • 默认的排序规则是ASC：“升序”，即从小到大。ASC可以省略，即ORDER BY score ASC和ORDER BY score效果一样。如果有WHERE子句，那么ORDER BY子句要放到WHERE子句后面
5.分页查询

  • 使用SELECT查询时，如果结果集数据量很大，比如几万行数据，放在一个页面显示的话数据量太大，不如分页显示，每次显示100条。要实现分页功能，实际上就是从结果集中显示第1100条记录作为第1页，显示第101200条记录作为第2页，以此类推。因此，分页实际上就是从结果集中“截取”出第M~N条记录。这个查询可以通过LIMIT <N-M> OFFSET <M>子句实现

  • 例如SELECT id, name, gender, score FROM students ORDER BY score LIMIT 3 OFFSET 0;表示将所有学生按照成绩从低到高排序再把结果集分页，每页3条记录；LIMIT 3 OFFSET 0表示，对结果集从0号记录开始，最多取3条。注意SQL记录集的索引从0开始

可见，分页查询的关键在于，首先要确定每页需要显示的结果数量pageSize（这里是3），然后根据当前页的索引pageIndex（从1开始），确定LIMIT和OFFSET应该设定的值：

  • LIMIT总是设定为pageSize；
  • OFFSET计算公式为pageSize * (pageIndex - 1)。

这样就能正确查询出第N页的记录集；OFFSET超过了查询的最大数量并不会报错，而是得到一个空的结果集

  • 注意

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

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

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

6.聚合查询

对于统计总数、平均数这类计算，SQL提供了专门的聚合函数，使用聚合函数进行查询，就是聚合查询，它可以快速获得结果。

  • 统计table中的记录条数：COUNT()函数，例如SELECT COUNT(*) FROM students; COUNT(*)表示查询所有列的行数，要注意聚合的计算结果虽然是一个数字，但查询的结果仍然是一个二维表，只是这个二维表只有一行一列，并且列名是COUNT(*)
  • 通常，使用聚合查询时，我们应该给列名设置一个别名，便于处理结果;COUNT(*)和COUNT(id)实际上是一样的效果。另外注意，聚合查询同样可以使用WHERE条件

除了COUNT()函数外，SQL还提供了如下聚合函数：

函数 说明
SUM 计算某一列的总和值，该列必须为数值类型
AVG 计算某一列的平均值，该列必须为数值类型
MAX 计算某一列的最大值
MIN 计算某一列的最小值

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

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

  • 对于聚合查询，SQL还提供了“分组聚合”的功能，例如SELECT class_id, COUNT(*) num FROM students GROUP BY class_id;，GROUP BY子句指定了按class_id分组，因此，执行该SELECT语句时，会把class_id相同的列先分组，再分别计算，因此，得到了3行结果

7.多表查询

查询多张表的语法是：SELECT * FROM <表1> <表2>，表与表使用逗号分隔

例如：SELECT * FROM students,classes;，查询的结果也是一个二维表，它是students表和classes表的“乘积”，即students表的每一行与classes表的每一行都两两拼在一起返回。结果集的列数是students表和classes表的列数之和，行数是students表和classes表的行数之积

注意：使用多表查询可以获取M x N行记录；多表查询的结果集可能非常巨大，要小心使用

8.连接查询

连接查询是另一种类型的多表查询。连接查询对多个表进行JOIN运算，简单地说，就是先确定一个主表作为结果集，然后，把其他表的行有选择性地“连接”在主表结果集上，常使用内连接INNER JOIN

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

注意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等子句

除内连接查询外还有外连接查询：RIGHT OUTER JOIN、LEFT OUTER JOIN，以及FULL OUTER JOIN。区别如下：

  • INNER JOIN只返回同时存在于两张表的行数据，由于students表的class_id包含1，2，3，classes表的id包含1，2，3，4，所以，INNER JOIN根据条件s.class_id = c.id返回的结果集仅包含1，2，3。

  • RIGHT OUTER JOIN返回右表都存在的行。如果某一行仅在右表存在，那么结果集就会以NULL填充剩下的字段。

  • LEFT OUTER JOIN则返回左表都存在的行。如果我们给students表增加一行，并添加class_id=5，由于classes表并不存在id=5的行，所以，LEFT OUTER JOIN的结果会增加一行，对应的class_name是NULL

注意：mysql 不支持FULL OUTER JOIN语法，只能左右连接两次结果UNION起来

（二）修改数据

关系数据库的基本操作就是增删改查，即CRUD：Create、Retrieve、Update、Delete。对于增、删、查、改，对应的SQL语句分别是：

  • SELECT：查询记录；
  • INSERT：插入新记录；
  • UPDATE：更新已有记录；
  • DELETE：删除已有记录。
1.INSERT
  • 基本语法：INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...);

注意，字段顺序不必和数据库表的字段顺序一致，但值的顺序必须和字段顺序一致；还可以一次性添加多条记录，只需要在VALUES子句中指定多个记录值，每个记录是由(...)包含的一组值，逗号分隔

2.UPDATE
  • 基本语法：UPDATE <表名> SET 字段1=值1, 字段2=值2, ... WHERE ...;

注意：如果WHERE条件没有匹配到任何记录，UPDATE语句不会报错，也不会有任何记录被更新；若没有WHERE条件则整个表的所有记录都会被更新

3.DELETE
  • 基本语法：DELETE FROM <表名> WHERE ...;

注意：和UPDATE类似，不带WHERE条件的DELETE语句会删除整个表的数据，这时，整个表的所有记录都会被删除。所以，在执行DELETE语句时也要非常小心，最好先用SELECT语句来测试WHERE条件是否筛选出了期望的记录集，然后再用DELETE删除。

（三）MySQL

  • 本地连接MySQL数据库服务器：mysql -u root -p；连接远程MySQL数据库服务器，使用-h指定IP或域名：mysql -h <ip/domain> -u root -p；若要指定连接后使用的数据库则在-p后添加数据库名称：mysql -u root -p 'test'；断开与MySQL数据库服务器连接：exit
1.数据库

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

    • 其中，information_schema、mysql、performance_schema和sys是系统库，不要去改动它们。其他的是用户创建的数据库

  • 创建/删除数据库：CREATE DATABASE <database>;/DROP DATABASE <database>;

    • 注意：删除一个数据库将导致该数据库的所有表全部被删除

  • 数据库切换：USE <database>;

2.表

  • 列出当前数据库所有表：SHOW TABLES;

  • 查看某个表的结构：DESC <table>;

  • 查看创建表的SQL语句：SHOW CREATE TABLE <table>;

  • 创建/删除表：CREATE TABLE <table>;/DROP TABLE <table>;

  • 修改/删除列：

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

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

    • 删除列，例如删除birthday列：

      1
      ALTER TABLE students DROP COLUMN birthday;
3.实用SQL语句

  • 插入或替换(若记录存在则先删除原记录再插入新记录，否则直接插入新记录)：REPLACE INTO <table> (字段1, 字段2, ...) VALUES (值1, 值2, ...);

  • 插入或更新(若记录存在则更新记录，否则直接插入新记录)：此时使用INSERT INTO ... ON DUPLICATE KEY UPDATE ...语句；例如：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 ...语句；例如：INSERT IGNORE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);

  • 快照：即复制一份当前表的数据到一个新表(新创建的表结构与SELECT实用的表结构完全相同)，可以结合CREATE TABLE和SELECT，例如：CREATE TABLE students_of_class1 SELECT * FROM students WHERE class_id=1;

  • 强制实用指定索引：使用FORCE INDEX强制查询使用指定的索引，但前提是该索引必须存在，例如：SELECT * FROM students FORCE INDEX (idx_class_id) WHERE class_id = 1 ORDER BY id DESC;

（四）事务

把多条语句作为一个整体进行操作的功能，被称为数据库_事务_。数据库事务可以确保该事务范围内的所有操作都可以全部成功或者全部失败。如果事务失败，那么效果就和没有执行这些SQL一样，不会对数据库数据有任何改动

可见，数据库事务具有ACID这4个特性：

  • A：Atomicity，原子性，将所有SQL作为原子工作单元执行，要么全部执行，要么全部不执行；
  • C：Consistency，一致性，事务完成后，所有数据的状态都是一致的，即A账户只要减去了100，B账户则必定加上了100；
  • I：Isolation，隔离性，如果有多个事务并发执行，每个事务作出的修改必须与其他事务隔离；
  • D：Durability，持久性，即事务完成后，对数据库数据的修改被持久化存储。

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

要手动把多条SQL语句作为一个事务执行，使用BEGIN开启一个事务，使用COMMIT提交一个事务，这种事务被称为_显式事务_；COMMIT是指提交事务，即试图把事务内的所有SQL所做的修改永久保存。如果COMMIT语句执行失败了，整个事务也会失败。若希望主动让事务失败，则使用ROLLBACK回滚事务，整个事务会失败

隔离级别

对于两个并发执行的事务，如果涉及到操作同一条记录的时候，可能会发生问题。因为并发操作会带来数据的不一致性，包括脏读、不可重复读、幻读等。数据库系统提供了隔离级别来让我们有针对性地选择事务的隔离级别，避免数据不一致的问题。

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

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