开发必备----SQL备忘录

最新推荐文章于 2023-03-15 18:10:36 发布

爱听歌的Fengx

最新推荐文章于 2023-03-15 18:10:36 发布

阅读量202

点赞数 1

分类专栏： MySQL 文章标签： sql

本文链接：https://blog.csdn.net/qq_37651252/article/details/110392188

版权

MySQL 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

前言

唉，说是Java开发，其实写的最多的还是SQL，搞得我有点迷啊，我这是SQL工程师吧。。。。不说了，还是写一篇备忘录吧，一方面巩固，一方面方便自己查询，没有的就查漏补缺，后面可能会根据不同数据库，场景，性能来添油加醋。。。

说明一下

这个SQL备忘录主要使用于MySQL，其他数据库可能略有不同

SQL 支持以下三种注释

# 注释
SELECT *
FROM mytable; -- 注释
/* 注释1
   注释2 */

DDL（数据定义语言）

创建新数据库

CREATE DATABASE [数据库名]

使用数据库

USE [数据库名]

删除数据库

DROP DATABASE [数据库名]

创建表

CREATE TABLE mytable (
  id INT NOT NULL AUTO_INCREMENT,
  col1 INT NOT NULL DEFAULT 1,
  col2 VARCHAR(45) NULL,
  col3 DATE NULL,
  PRIMARY KEY (`id`));

修改表

添加列

ALTER TABLE mytable
ADD col CHAR(20);

删除列

ALTER TABLE mytable
DROP COLUMN col;

删除表

DROP TABLE mytable;

创建索引

CREATE INDEX [索引名] ON [表名]

删除索引

DROP INDEX [索引名]

DML（数据操作语言）

insert（插入）

单条插入

insert into students (class_id,name,gender,score) values (2,'大牛','M',80);

多条插入

insert into students (class_id,name,gender,score) values 
(2,'大牛','M',80),
(3,'小牛','S',30);

插入检索出来的数据

INSERT INTO mytable1(col1, col2) 
SELECT col1, col2
FROM mytable2;

将一个表的内容插入到一个新表

CREATE TABLE newtable AS
SELECT * FROM mytable;

delete（删除）

单条

delete from students where id = 1;

多条

delete from students where id >= 5 and id <= 7;

删除表的所有行

TRUNCATE TABLE mytable;

注意：使用更新和删除操作时一定要用 WHERE 子句，不然会把整张表的数据都破坏。可以先用 SELECT 语句进行测试，防止错误删除。

更新（update）

单条

update students set name = '大牛',score = 66 where id = 1;

多条

update students set name = '小牛',score = 77 where id >= 5 and id <= 10;

使用表达式

update students set score = score + 10 where id <= 20;

咳咳，很快啊，重点来了

查询（select）

select常见完整语句

select [目标列名] 
from [数据表名]
[where 行条件表达式]
[group by [列名]]
[having [组表达式]]
[order by [列名] [asc/desc]]

经典查询

查询所有

select * from students;

实际项目中尽量不要使用，影响性能

查询单行、多行

select name,class_id from students where id = 10;

select name,class_id from students where id > 10 and id < 20;

过滤

不进行过滤的数据非常大，导致通过网络传输了多余的数据，从而浪费了网络带宽。因此尽量使用 SQL 语句来过滤不必要的数据，而不是传输所有的数据到客户端中然后由客户端进行过滤。

WHERE

过滤条件关键字。

AND、OR

AND 和 OR 用于连接多个过滤条件。优先处理 AND，当一个过滤表达式涉及到多个 AND 和 OR 时，可以使用 () 来决定优先级，使得优先级关系更清晰。
IN
IN 操作符用于匹配一组值，其后也可以接一个 SELECT 子句，从而匹配子查询得到的一组值。

NOT

NOT 操作符用于否定一个条件。

通配符

通配符也是用在过滤语句中，但它只能用于文本字段。

% 匹配 >=0 个任意字符；
_ 匹配 ==1 个任意字符；
[] 可以匹配集合内的字符，例如 [ab] 将匹配字符 a 或者 b。用脱字符 ^ 可以对其进行否定，也就是不匹配集合内的字符。

使用 Like 来进行通配符匹配。

SELECT *
FROM mytable
WHERE col LIKE '[^AB]%'; -- 不以 A 和 B 开头的任意文本

不要滥用通配符，通配符位于开头处匹配会非常慢。

计算字段

在数据库服务器上完成数据的转换和格式化的工作往往比客户端上快得多，并且转换和格式化后的数据量更少的话可以减少网络通信量。

计算字段通常需要使用 AS 来取别名，否则输出的时候字段名为计算表达式。

SELECT col1 * col2 AS alias
FROM mytable;

CONCAT() 用于连接两个字段。许多数据库会使用空格把一个值填充为列宽，因此连接的结果会出现一些不必要的空格，使用 TRIM() 可以去除首尾空格。

SELECT CONCAT(TRIM(col1), '(', TRIM(col2), ')') AS concat_col
FROM mytable;

日期和时间处理

日期格式: YYYY-MM-DD
时间格式: HH:MM:SS

函数	说明
AddDate()	增加一个日期(天、周等)
AddTime()	增加一个时间(时、分等)
CurDate()	返回当前日期
CurTime()	返回当前时间
Date()	返回日期时间的日期部分
DateDiff()	计算两个日期之差
Date_Add()	高度灵活的日期运算函数
Date_Format()	返回一个格式化的日期或时间串
Day()	返回一个日期的天数部分
DayOfWeek()	对于一个日期，返回对应的星期几
Hour()	返回一个时间的小时部分
Minute()	返回一个时间的分钟部分
Month()	返回一个日期的月份部分
Now()	返回当前日期和时间
Second()	返回一个时间的秒部分
Time()	返回一个日期时间的时间部分
Year()	返回一个日期的年份部分

mysql> SELECT NOW();

2018-4-14 20:25:11

数值处理

函数	说明
SIN()	正弦
COS()	余弦
TAN()	正切
ABS()	绝对值
SQRT()	平方根
MOD()	余数
EXP()	指数
PI()	圆周率
RAND()	随机数

投影查询

让结果集仅包含指定列
SELECT 列1, 列2, 列3 FROM …
例子：

SELECT id, score, name FROM students;

起别名
SELECT 列1 别名1, 列2 别名2, 列3 别名3 FROM …;
可以给每一列起个别名，结果集的列名可以与原表的列名不同
例子：

SELECT id stu_id, score points, name stu_name FROM students;

WHERE

SELECT id, score points, name FROM students WHERE gender = 'M';

DISTINCT

相同值只会出现一次。它作用于所有列，也就是说所有列的值都相同才算相同。

SELECT DISTINCT col1, col2
FROM mytable;

排序

ORDER BY

ASC升序

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

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

DECS降序

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

多排序

当score列有相同的数据，要进一步排序，可以继续添加列名，例如使用ORDER BY score DESC, gender表示先按score列倒序，如果有相同分数的，再按gender列排序：

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

WHERE
如果有WHERE子句，ORDER BY子句要放到WHERE子句后面,先查出来在排序

SELECT id, name, gender, score
FROM students
WHERE class_id = 1
ORDER BY score DESC;

分页查询

LIMIT OFFSET

或 LIMIT N,M （例：limit 0,20：第0条记录开始，拿20条数据）

实现：
要实现分页功能，实际上就是从结果集中显示第1到100条记录作为第1页，显示第101到200条记录作为第2页，以此类推。
先进行排序：

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

现在，我们把结果集分页，每页3条记录。要获取第1页的记录，可以使用LIMIT 3 OFFSET 0：

SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 0;

上述查询LIMIT 3 OFFSET 0表示，对结果集从0号记录开始，最多取3条。注意SQL记录集的索引从0开始。
如果要查询第2页，那么我们只需要“跳过”头3条记录，也就是对结果集从3号记录开始查询，把OFFSET设定为3：

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

LIMIT 3表示的意思是“最多3条记录”。

OFFSET超过了查询的最大数量并不会报错，而是得到一个空的结果集。
OFFSET是可选的，如果只写LIMIT 15，那么相当于LIMIT 15 OFFSET 0。
在MySQL中，LIMIT 15 OFFSET 30还可以简写成LIMIT 30, 15。

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

聚合查询

SELECT COUNT(*) FROM students;

COUNT((星号))表示查询所有列的行数，要注意聚合的计算结果虽然是一个数字，但查询的结果仍然是一个二维表，只是这个二维表只有一行一列，并且列名是COUNT((星号))。

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

SELECT COUNT(*) num FROM students;

WHERE

SELECT COUNT(*) boys FROM students WHERE gender = 'M';

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

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

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

SELECT AVG(score) average FROM students WHERE gender = 'M';

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

分组聚合

分组就是把具有相同的数据值的行放在同一组中。

可以对同一分组数据使用汇总函数进行处理，例如求分组数据的平均值等。

指定的分组字段除了能按该字段进行分组，也会自动按该字段进行排序。

GROUP BY

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

GROUP BY 自动按分组字段进行排序，ORDER BY 也可以按汇总字段来进行排序。

SELECT col, COUNT(*) AS num
FROM mytable
GROUP BY col
ORDER BY num;

WHERE 过滤行，HAVING 过滤分组，行过滤应当先于分组过滤。

SELECT col, COUNT(*) AS num
FROM mytable
WHERE col > 2
GROUP BY col
HAVING num >= 2;

也可以使用多个列进行分组。例如，我们想统计各班的男生和女生人数：

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

上述查询结果集一共有6条记录，分别对应各班级的男生和女生人数。

多表查询

查询多张表

SELECT * FROM students, classes;

students表的每一行与classes表的每一行都两两拼在一起返回。
结果集的列数是students表和classes表的列数之和，行数是students表和classes表的行数之积。

这种多表查询又称笛卡尔查询，使用笛卡尔查询时要非常小心，由于结果集是目标表的行数乘积，对两个各自有100行记录的表进行笛卡尔查询将返回1万条记录，对两个各自有1万行记录的表进行笛卡尔查询将返回1亿条记录。

正确用法

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;

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;

子查询

子查询中只能返回一个字段的数据。

可以将子查询的结果作为 WHRER 语句的过滤条件:

SELECT *
FROM mytable1
WHERE col1 IN (SELECT col2
               FROM mytable2);

下面的语句可以检索出客户的订单数量，子查询语句会对第一个查询检索出的每个客户执行一次:

SELECT cust_name, (SELECT COUNT(*)
                   FROM Orders
                   WHERE Orders.cust_id = Customers.cust_id)
                   AS orders_num
FROM Customers
ORDER BY cust_name;

连接查询

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

连接用于连接多个表，使用 JOIN 关键字，并且条件语句使用 ON 而不是 WHERE。

连接可以替换子查询，并且比子查询的效率一般会更快。

可以用 AS 给列名、计算字段和表名取别名，给表名取别名是为了简化 SQL 语句以及连接相同表。

SELECT s.id, s.name, s.class_id, s.gender, s.score FROM students s;

上面的结果集只有class_id列，缺少对应班级的name列。
最常用的一种内连接——INNER JOIN来实现：（内连接）

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等子句。

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

自连接

自连接可以看成内连接的一种，只是连接的表是自身而已。

一张员工表，包含员工姓名和员工所属部门，要找出与 Jim 处在同一部门的所有员工姓名。

子查询版本

SELECT name
FROM employee
WHERE department = (
      SELECT department
      FROM employee
      WHERE name = "Jim");

自连接版本

SELECT e1.name
FROM employee AS e1 INNER JOIN employee AS e2
ON e1.department = e2.department
      AND e2.name = "Jim";

外连接

外连接保留了没有关联的那些行。分为左外连接，右外连接以及全外连接，左外连接就是保留左表没有关联的行。

检索所有顾客的订单信息，包括还没有订单信息的顾客。

SELECT Customers.cust_id, Orders.order_num
FROM Customers LEFT OUTER JOIN Orders
ON Customers.cust_id = Orders.cust_id;

customers 表:

cust_id	cust_name
1	a
2	b
3	c

orders 表:

order_id	cust_id
1	1
2	1
3	3
4	3

结果:

cust_id	cust_name	order_id
1	a	1
1	a	2
3	c	3
3	c	4
2	b	Null

left（outer）join

左外连接（左连接）：结果集几包括连接表的匹配行，也包括左连接表的所有行。

select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

right（outer）join

右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。

full/cross（outer）join

全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。

函数

总数

select count as totalcount from table1

求和

select sum(field1) as sumvalue from table1

平均

select avg(field1) as avgvalue from table1

AVG() 会忽略 NULL 行。

使用 DISTINCT 可以让汇总函数值汇总不同的值。

SELECT AVG(DISTINCT col1) AS avg_col
FROM mytable;

最大

select max(field1) as maxvalue from table1

最小

select min(field1) as minvalue from table1

文本处理

函数	说明
LEFT()	左边的字符
RIGHT()	右边的字符
LOWER()	转换为小写字符
UPPER()	转换为大写字符
LTRIM()	去除左边的空格
RTRIM()	去除右边的空格
LENGTH()	长度
SOUNDEX()	转换为语音值

其中， SOUNDEX() 可以将一个字符串转换为描述其语音表示的字母数字模式。

SELECT *
FROM mytable
WHERE SOUNDEX(col1) = SOUNDEX('apple')

高级查询运算词

UNION 运算符

UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。

当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。

两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。

只能包含一个 ORDER BY 子句，并且必须位于语句的最后。

SELECT col
FROM mytable
WHERE col = 1
UNION
SELECT col
FROM mytable
WHERE col =2;

EXCEPT 运算符

EXCEPT运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。

当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。

INTERSECT 运算符

INTERSECT运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。

当 ALL随 INTERSECT 一起使用时 (INTERSECT ALL)，不消除重复行。

注：使用运算词的几个查询结果行必须是一致的。

视图

视图是虚拟的表，本身不包含数据，也就不能对其进行索引操作。

对视图的操作和对普通表的操作一样。

视图具有如下好处:

简化复杂的 SQL 操作，比如复杂的连接；
只使用实际表的一部分数据；
通过只给用户访问视图的权限，保证数据的安全性；
更改数据格式和表示。

CREATE VIEW myview AS
SELECT Concat(col1, col2) AS concat_col, col3*col4 AS compute_col
FROM mytable
WHERE col5 = val;

存储过程

存储过程可以看成是对一系列 SQL 操作的批处理。

使用存储过程的好处：

代码封装，保证了一定的安全性；
代码复用；
由于是预先编译，因此具有很高的性能。

命令行中创建存储过程需要自定义分隔符，因为命令行是以 ; 为结束符，而存储过程中也包含了分号，因此会错误把这部分分号当成是结束符，造成语法错误。

包含 in、out 和 inout 三种参数。

给变量赋值都需要用 select into 语句。

每次只能给一个变量赋值，不支持集合的操作。

create procedure myprocedure( out ret int )
    begin
        declare y int;
        select sum(col1)
        from mytable
        into y;
        select y*y into ret;
    end

call myprocedure(@ret);
select @ret;

游标

在存储过程中使用游标可以对一个结果集进行移动遍历。

游标主要用于交互式应用，其中用户需要对数据集中的任意行进行浏览和修改。

使用游标的四个步骤:

声明游标，这个过程没有实际检索出数据；
打开游标；
取出数据；
关闭游标；

create procedure myprocedure(out ret int)
    begin
        declare done boolean default 0;
        
        declare mycursor cursor for
        select col1 from mytable;
        # 定义了一个 continue handler，当 sqlstate '02000' 这个条件出现时，会执行 set done = 1
        declare continue handler for sqlstate '02000' set done = 1;

        open mycursor;

        repeat
            fetch mycursor into ret;
            select ret;
        until done end repeat;

        close mycursor;
    end

触发器

触发器会在某个表执行以下语句时而自动执行: DELETE、INSERT、UPDATE。

触发器必须指定在语句执行之前还是之后自动执行，之前执行使用 BEFORE 关键字，之后执行使用 AFTER 关键字。BEFORE 用于数据验证和净化，AFTER 用于审计跟踪，将修改记录到另外一张表中。

INSERT 触发器包含一个名为 NEW 的虚拟表。

CREATE TRIGGER mytrigger AFTER INSERT ON mytable
FOR EACH ROW SELECT NEW.col into @result;

SELECT @result; -- 获取结果

DELETE 触发器包含一个名为 OLD 的虚拟表，并且是只读的。

UPDATE 触发器包含一个名为 NEW 和一个名为 OLD 的虚拟表，其中 NEW 是可以被修改的，而 OLD 是只读的。

MySQL 不允许在触发器中使用 CALL 语句，也就是不能调用存储过程。

事务管理

基本术语：

事务(transaction)指一组 SQL 语句；
回退(rollback)指撤销指定 SQL 语句的过程；
提交(commit)指将未存储的 SQL 语句结果写入数据库表；
保留点(savepoint)指事务处理中设置的临时占位符(placeholder)，你可以对它发布回退(与回退整个事务处理不同)。

不能回退 SELECT 语句，回退 SELECT 语句也没意义；也不能回退 CREATE 和 DROP 语句。

MySQL 的事务提交默认是隐式提交，每执行一条语句就把这条语句当成一个事务然后进行提交。当出现 START TRANSACTION 语句时，会关闭隐式提交；当 COMMIT 或 ROLLBACK 语句执行后，事务会自动关闭，重新恢复隐式提交。

通过设置 autocommit 为 0 可以取消自动提交；autocommit 标记是针对每个连接而不是针对服务器的。

如果没有设置保留点，ROLLBACK 会回退到 START TRANSACTION 语句处；如果设置了保留点，并且在 ROLLBACK 中指定该保留点，则会回退到该保留点。

START TRANSACTION
// ...
SAVEPOINT delete1
// ...
ROLLBACK TO delete1
// ...
COMMIT

字符集

基本术语:

字符集为字母和符号的集合；
编码为某个字符集成员的内部表示；
校对字符指定如何比较，主要用于排序和分组。

除了给表指定字符集和校对外，也可以给列指定:

CREATE TABLE mytable
(col VARCHAR(10) CHARACTER SET latin COLLATE latin1_general_ci )
DEFAULT CHARACTER SET hebrew COLLATE hebrew_general_ci;

可以在排序、分组时指定校对:

SELECT *
FROM mytable
ORDER BY col COLLATE latin1_general_ci;

权限管理

MySQL 的账户信息保存在 mysql 这个数据库中。

USE mysql;
SELECT user FROM user;

创建账户

新创建的账户没有任何权限。

CREATE USER myuser IDENTIFIED BY 'mypassword';

修改账户名

RENAME myuser TO newuser;

删除账户

DROP USER myuser;

查看权限

SHOW GRANTS FOR myuser;

授予权限

账户用 username@host 的形式定义，username@% 使用的是默认主机名。

GRANT SELECT, INSERT ON mydatabase.* TO myuser;

删除权限

GRANT 和 REVOKE 可在几个层次上控制访问权限:

整个服务器，使用 GRANT ALL 和 REVOKE ALL；
整个数据库，使用 ON database.*；
特定的表，使用 ON database.table；
特定的列；
特定的存储过程。

REVOKE SELECT, INSERT ON mydatabase.* FROM myuser;

更改密码

必须使用 Password() 函数

SET PASSWROD FOR myuser = Password('new_password');

爱听歌的Fengx

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
开发必备----SQL备忘录

前言唉，刚毕业入职差不多半年，说是Java开发，其实写的最多的还是SQL，搞得我有点迷啊，我这是SQL工程师吧。。。。不说了，还是写一篇备忘录吧，一方面巩固，一方面方便自己查询，没有的就查漏补缺，后面可能会根据不同数据库，场景，性能来添油加醋。。。说明一下这个SQL备忘录主要使用于MySQL，其他数据库可能略有不同，后面会总结一下。。。DDL（数据定义语言）创建新数据库CREATE DATABASE [数据库名]删除数据库DROP DATABASE [数据库名]同理，创建和删除表一样
复制链接

扫一扫