sql基础

最新推荐文章于 2023-11-02 11:22:04 发布

「已注销」

最新推荐文章于 2023-11-02 11:22:04 发布

阅读量110

点赞数

分类专栏： sql

本文链接：https://blog.csdn.net/qq_51962013/article/details/113642845

版权

sql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

主键

主键是关系表中记录的唯一标识。主键的选取非常重要：主键不要带有业务含义，而应该使用BIGINT自增或者GUID类型。主键也不应该允许NULL。

SELECT

基本查询

使用SELECT查询的基本语句SELECT * FROM <表名>可以查询一个表的所有行和所有列的数据。
SELECT查询的结果是一个二维表。

条件查询

SELECT语句可以通过WHERE条件来设定查询条件，查询结果是满足查询条件的记录。

例如，要指定条件“分数在80分或以上的学生”，SELECT * FROM students WHERE score >= 80。

条件表达式可以用<条件1> AND <条件2>表达满足条件1并且满足条件2。例如，符合条件“分数在80分或以上”，并且还符合条件“男生”，把这两个条件写出来：

条件1：根据score列的数据判断：score >= 80；
条件2：根据gender列的数据判断：gender = 'M'，注意gender列存储的是字符串，需要用单引号括起来。

就可以写出WHERE条件：score >= 80 AND gender = 'M'；

投影查询

如果我们只希望返回某些列的数据，而不是所有列的数据，我们可以用SELECT 列1, 列2, 列3 FROM ...，让结果集仅包含指定列。这种操作称为投影查询。

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

采用中括号[***]设置中文别名

排序

查询结果集通常是按照主键排序。根据其他条件排序可以加上ORDER BY子句。

SELECT id, name, gender, score FROM students ORDER BY score;

如果要反过来，按照成绩从高到底排序，我们可以加上DESC表示“倒序”。

SELECT id, name, gender, score FROM students ORDER BY score DESC;

默认的排序规则是ASC：“升序”，即从小到大。ASC可以省略。

如果有WHERE子句，那么ORDER BY子句要放到WHERE子句后面。

分页查询

使用LIMIT <M> OFFSET <N>可以对结果集进行分页，每次查询返回结果集的一部分；

每页3条记录。要获取第1页的记录，可以使用LIMIT 3 OFFSET 0；

分页查询需要先确定每页的数量和当前页数，然后确定LIMIT和OFFSET的值。

LIMIT总是设定为pageSize；
OFFSET计算公式为pageSize * (pageIndex - 1)。
OFFSET是可选的，如果只写LIMIT 15，那么相当于LIMIT 15 OFFSET 0。

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

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

MySQL分页排序，各页中记录存在重复数据现象

现象描述：

项目在做分页查询的时候，用到了排序 order by ctime，由于是测试环境，ctime时间值都是一样的。在通过mybatis分页查询的时候，发现最后一页数据和第一页数据重复。后来就拿出sql进行测试，发现偶尔是重复的。

解决方案

增加一个二级排序【比如：主键，时间戳】，总之实现绝对排序即可

order ctime,id 即可。

聚合查询

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

仍然以查询students表一共有多少条记录为例，我们可以使用SQL内置的COUNT()函数查询：

SELECT COUNT(*) FROM students;

通常，使用聚合查询时，我们应该给列名设置一个别名，便于处理结果。

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

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

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

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

分组查询

SELECT COUNT(*) num FROM students GROUP BY class_id;

SELECT class_id, COUNT(*) num FROM students GROUP BY class_id; 区分班级

聚合查询的列中，只能放入分组的列。

多表查询

同时从students表和classes表的“乘积”，即查询数据，可以这么写：

SELECT * FROM students, classes;

可以利用投影查询的“设置列的别名”来给两个表各自的id和name列起别名：

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

多表查询也是可以添加别名，WHERE条件的：

SELECT s.id sid, s.name, s.gender, s.score, c.id cid, c.name cname FROM students s, classes c WHERE s.gender = 'M' AND c.id = 1;

链接查询

我们先使用最常用的一种内连接——INNER JOIN来实现

结果集同时包含所在班级的名称，上面的结果集只有class_id列，缺少对应班级的name列。

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;

注意INNER JOIN查询的写法是：

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

使用别名不是必须的，但可以更好地简化查询语句。

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。
FULL OUTER JOIN会把两张表的所有记录全部选择出来，并且，自动把对方不存在的列填充为NULL

INSERT

INSERT语句的基本语法是：

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

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

INSERT INTO students (class_id, name, gender, score) VALUES
(1, '大宝', 'M', 87),
(2, '二宝', 'M', 81);

SELECT * FROM students;

UPDATE

UPDATE语句的基本语法是：

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

UPDATE语句的WHERE条件和SELECT语句的WHERE条件其实是一样的，因此完全可以一次更新多条记录：

UPDATE students SET name='小牛', score=77 WHERE id>=5 AND id<=7;

在UPDATE语句中，更新字段时可以使用表达式。

UPDATE students SET score=score+10 WHERE score<80;

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

UPDATE students SET score=60;

这时，整个表的所有记录都会被更新。所以，在执行UPDATE语句时要非常小心，最好先用SELECT语句来测试WHERE条件是否筛选出了期望的记录集，然后再用UPDATE更新。

DELETE

DELETE语句的基本语法是：

DELETE FROM <表名> WHERE ...;

例如，我们想删除students表中id=1的记录，就需要这么写：

DELETE FROM students WHERE id=1;

DELETE语句的WHERE条件也是用来筛选需要删除的行，因此和UPDATE类似，DELETE语句也可以一次删除多条记录：

DELETE FROM students WHERE id>=5 AND id<=7;

最后，要特别小心的是，和UPDATE类似，不带WHERE条件的DELETE语句会删除整个表的数据

MySQL

要列出所有数据库，使用命令：

mysql> SHOW DATABASES;

要创建一个新数据库，使用命令：

mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.01 sec)

要删除一个数据库，使用命令：

mysql> DROP DATABASE test;
Query OK, 0 rows affected (0.01 sec)

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

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

mysql> USE test;
Database changed

表

列出当前数据库的所有表，使用命令：

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服务器仍然继续运行。

实用SQL语句

插入或替换

如果我们希望插入一条新记录（INSERT），但如果记录已经存在，就先删除原记录，再插入新记录。此时，可以使用REPLACE语句，这样就不必先查询，再决定是否先删除再插入：

REPLACE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);

若id=1的记录不存在，REPLACE语句将插入新记录，否则，当前id=1的记录将被删除，然后再插入新记录。

插入或更新

如果我们希望插入一条新记录（INSERT），但如果记录已经存在，就更新该记录，此时，可以使用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;

若id=1的记录不存在，INSERT语句将插入新记录，否则，当前id=1的记录将被更新，更新的字段由UPDATE指定。

插入或忽略

如果我们希望插入一条新记录（INSERT），但如果记录已经存在，就啥事也不干直接忽略，此时，可以使用INSERT IGNORE INTO ...语句：

INSERT IGNORE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);

若id=1的记录不存在，INSERT语句将插入新记录，否则，不执行任何操作。

快照

如果想要对一个表进行快照，即复制一份当前表的数据到一个新表，可以结合CREATE TABLE和SELECT：

-- 对class_id=1的记录进行快照，并存储为新表students_of_class1:
CREATE TABLE students_of_class1 SELECT * FROM students WHERE class_id=1;

新创建的表结构和SELECT使用的表结构完全一致。

写入查询结果集

如果查询结果集需要写入到表中，可以结合INSERT和SELECT，将SELECT语句的结果集直接插入到指定表中。

例如，创建一个统计成绩的表statistics，记录各班的平均成绩：

CREATE TABLE statistics (
    id BIGINT NOT NULL AUTO_INCREMENT,
    class_id BIGINT NOT NULL,
    average DOUBLE NOT NULL,
    PRIMARY KEY (id)
);

然后，我们就可以用一条语句写入各班的平均成绩：

INSERT INTO statistics (class_id, average) SELECT class_id, AVG(score) FROM students GROUP BY class_id;

确保INSERT语句的列和SELECT语句的列能一一对应，就可以在statistics表中直接保存查询的结果：

> SELECT * FROM statistics;
+----+----------+--------------+
| id | class_id | average      |
+----+----------+--------------+
|  1 |        1 |         86.5 |
|  2 |        2 | 73.666666666 |
|  3 |        3 | 88.333333333 |
+----+----------+--------------+
3 rows in set (0.00 sec)

强制使用指定索引

在查询的时候，数据库系统会自动分析查询语句，并选择一个最合适的索引。但是很多时候，数据库系统的查询优化器并不一定总是能使用最优索引。如果我们知道如何选择索引，可以使用FORCE INDEX强制查询使用指定的索引。例如：

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

指定索引的前提是索引idx_class_id必须存在。

事务

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

要手动把多条SQL语句作为一个事务执行，使用BEGIN开启一个事务，使用COMMIT提交一个事务，这种事务被称为显式事务，例如，把上述的转账操作作为一个显式事务：

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

很显然多条SQL语句要想作为一个事务执行，就必须使用显式事务。

有些时候，我们希望主动让事务失败，这时，可以用ROLLBACK回滚事务，整个事务会失败：

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
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	-	-	-

Read Uncommitted

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

Read Committed

在Read Committed隔离级别下，一个事务可能会遇到不可重复读（Non Repeatable Read）的问题。

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

Repeatable Read

在Repeatable Read隔离级别下，一个事务可能会遇到幻读（Phantom Read）的问题。

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

Serializable

Serializable是最严格的隔离级别。在Serializable隔离级别下，所有事务按照次序依次执行，因此，脏读、不可重复读、幻读都不会出现。

虽然Serializable隔离级别下的事务具有最高的安全性，但是，由于事务是串行执行，所以效率会大大下降，应用程序的性能会急剧降低。如果没有特别重要的情景，一般都不会使用Serializable隔离级别。

Summarize

主键：唯一标识一行的列

外键：标示表与表相对关系的键（一对一，一对多，多对一，多对多）

索引：为了加快查询速度（插入等更改操作会变慢），为列预排序而建立的列

事务：多条语句变为原子操作

脏读：未提交的更改导致另外一个事务提交前后查询的数据不一致

不可重复读：事务提交后，另一个事务在这个事务开始前与事务提交完查询后的数据不一致

幻读：一个事务插入操作，另外一个事务可以更改，并且查询出该行

//基本查询
SELECT * | 列1 <别名>，列二 , ..... FROM 表名 <别名>，表二 别名>，....      //查询哪一个表内的什么列
WHERE 条件表达式                                   //条件  
ORDER BY 列名 <ARC | DESC>;                        //排序

//聚集函数
//根据某列分组计算聚集函数，然后显示
SELECT AVG(列名) <别名> | SUM(列名) | .... FROM 表名
GROUP BY  列名 

//Crud
//插入一个或者多个
INSERT INTO 表名(列 1， 列2，.....) VALUES(v1,v2,v3,.....) ,( v1 )   ;

//删除根据条件一个或者多个行
DELETE FROM <表名> WHERE ...;

//更改-哪个表，哪个字段，哪个值
UPDATE <表名> SET 字段1=值1, 字段2=值2, ... WHERE ...;

//事务
BEGIN
....   //语句一
....   //语句二
COMMIT;

//回滚 -事务失败回滚(不是逻辑错误，逻辑错误能够运行成功)
BEGIN
...
....
...
ROLLBACK;

「已注销」

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
sql基础

主键主键是关系表中记录的唯一标识。主键的选取非常重要：主键不要带有业务含义，而应该使用BIGINT自增或者GUID类型。主键也不应该允许NULL。SELECT基本查询使用SELECT查询的基本语句SELECT * FROM <表名>可以查询一个表的所有行和所有列的数据。SELECT查询的结果是一个二维表。条件查询SELECT语句可以通过WHERE条件来设定查询条件，查询结果是满足查询条件的记录。例如，要指定条件“分数在80分或以上的学生”，SELECT * FROM studen
复制链接

扫一扫