sql语法相关学习笔记

前言

一、语法

1. 基础语法

-- 这是注释

2. select

2.1. order by 排序

-- 根据某一列排序（列名）
select * from table_test order by id
-- 根据某一列排序（序号）
select * from table_test order by 3

1) order by什么情况下会使用索引进行排序

• 正常直接order by是不会使用索引进行排序的，当order by的字段在where中则会使用索引进行排序
• 测试得到如下四个结果
  • order by的字段不在where条件不在select中，有排序操作
  • order by的字段不在where条件但在select中，有排序操作
  • order by的字段在where条件但不在select中，无排序操作，使用索引排序后查数据
  • order by的字段在where条件但不在select中(倒序) ，无排序操作，使用索引排序后查数据

原因

sql处理分为三个阶段：

1. 根据where条件和统计信息生成执行计划
2. 处理数据
3. 返回数据

当第二步要对数据进行排序时，发现第一步执行计划中使用到了索引，而索引正好是排序字段，则直接使用索引进行排序

2) where和order by字段不一样如何使用索引排序

• 需要添加联合索引，并且联合索引要求where中的字段在前面，order by中的字段在后面

-- 直接执行where配合查询，不会使用索引排序，直接内存排序
mysql> explain select name from resource where status=1 order by name;
+----+-------------+----------+------------+------+-----------------+-----------------+---------+-------+------+----------+----------------+
| id | select_type | table    | partitions | type | possible_keys   | key             | key_len | ref   | rows | filtered | Extra          |
+----+-------------+----------+------------+------+-----------------+-----------------+---------+-------+------+----------+----------------+
|  1 | SIMPLE      | resource | NULL       | ref  | resource_status | resource_status | 5       | const |   24 |   100.00 | Using filesort |
+----+-------------+----------+------------+------+-----------------+-----------------+---------+-------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

-- 添加order by的字段在前的索引，还是使用内存排序，不会使用索引
mysql> create index name_status on resource (name, status);
Query OK, 0 rows affected (1.50 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select name from resource where status=1 order by name;
+----+-------------+----------+------------+------+-----------------------------+-----------------+---------+-------+------+----------+----------------+
| id | select_type | table    | partitions | type | possible_keys               | key             | key_len | ref   | rows | filtered | Extra          |
+----+-------------+----------+------------+------+-----------------------------+-----------------+---------+-------+------+----------+----------------+
|  1 | SIMPLE      | resource | NULL       | ref  | resource_status,name_status | resource_status | 5       | const |   24 |   100.00 | Using filesort |
+----+-------------+----------+------------+------+-----------------------------+-----------------+---------+-------+------+----------+----------------+
1 row in set, 1 warning (0.01 sec)

mysql> alter table resource drop index name_status;
Query OK, 0 rows affected (0.27 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 添加where字段在前的索引，就会使用索引进行排序
mysql> create index status_name on resource (status, name);
Query OK, 0 rows affected (0.52 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select name from resource where status=1 order by name;
+----+-------------+----------+------------+------+-----------------------------+-------------+---------+-------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys               | key         | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------+------------+------+-----------------------------+-------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | resource | NULL       | ref  | resource_status,status_name | status_name | 5       | const |   24 |   100.00 | Using index |
+----+-------------+----------+------------+------+-----------------------------+-------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

2.2. union 联表

• union 将两个表的数据合并，需要保证列数一致
• 会使用第一个 select 对应的列，第二个 select 仅留下数据

-- 将两个表的数据合并，只列出不同的值
select column1 from table1
union
select column1 from table2
-- 允许有相同的值
select column1 from table1
union all
select column1 from table2

2.3. distinct 去重

select distinct name from table1;

2.4. group by 统计

select name, count(*) as count from table1 group by name order by count;

2.5. where

(1) null判断

select name from table1 where id is not null

select name from table1 where id is null

2.6. concat 字符串拼接

• 将查询结果拼接其他字符串展示

SELECT concat('DROP TABLE IF EXISTS ', table_name, ';')
FROM information_schema.tables
WHERE table_schema = 'mydb';

3. truncate

3.1. 用法

• 删除一个表里面所有数据
• sqlite不支持此命令

truncate table [table_name]
--也可以不带table
truncate [table_name]

3.2. truncate 和 delete 的区别

	truncate	delete
功能	删除表的所有数据	可以删除所有，也可以只删除部分
行为	删除内容，不删除表，释放空间	删除内容，不删除表，不释放空间
速度	快	慢
日志	一条页的释放日志	一条一条删，每行都有日志
能否恢复	由于空间被释放，无法恢复	可以被回滚

4. replace

4.1. 基础用法

replace into emm_data
    (`path`, `size`, `modify`) value('/a/b/c', '12345', '2022-02-22 22:22:22')

二、特定数据库软件

2. sqlite3

2.1. 系统表

(1) sqlite_master

sqlite数据库有一个系统表sqlite_master储存了数据库表的信息

sqlite> .mod line
sqlite> select * from sqlite_master;
    type = table
    name = task
tbl_name = task
rootpage = 2
     sql = CREATE TABLE `task` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT
)

    type = table
    name = sqlite_sequence
tbl_name = sqlite_sequence
rootpage = 3
     sql = CREATE TABLE sqlite_sequence(name,seq)

(2) sqlite_sequence

sqlite_sequence表也是SQLite的系统表。该表用来保存其他表的RowID的最大值。数据库被创建时，sqlite_sequence表会被自动创建。该表包括两列。第一列为name，用来存储表的名称。第二列为seq，用来保存表对应的RowID的最大值。该值最大值为9223372036854775807。当对应的表增加记录，该表会自动更新。当表删除，该表对应的记录也会自动删除。如果该值超过最大值，会引起SQL_FULL错误。所以，一旦发现该错误，用户不仅要检查SQLite文件所在的磁盘空间是否不足，还需要检查是否有表的ROWID达到最大值。

sqlite> .mod line
sqlite> select * from sqlite_sequence;
 name = task
  seq = 13

踩坑记