常用SQL

Posted on

基本原理

笛卡尔积

database 中的表要联合查询，就需要进行笛卡尔积，然后再做筛选处理。 笛卡尔积就是排列组合一下，新表是由两个原表的列相加、行相乘构造出来的。

显示方式

通常 sql 语句末尾;表示正常显示，末尾用\G表示每条纵向显示。

字符串表示

  • 一般用单引号'作为字符串包围字符

    • 如果内容包含单引号，则需要用单引号转义，如'''前面有一个单引号'
    • 如果内容包含\，则用\\表示，如'\\前面有一个反斜杠'

  • 用反引号`表示一个标识符，如 dbName、tableName、fieldName 等。例：DROP TABLE `db.tb`;

    • 在自定义标志符和保留符号重复时必须用这种写法区分，否则报语法错误

DML(Data Manipulation Language)

操纵表中的数据，不会影响表结构。

  • 语句以;结尾
  • 单词之间通常以空格间隔，有时用 Tab 会导致无法识别

注释

-- 单行注释, 注意"--"后边有一个空格

/*
  多行注释
*/

USE xxdb

选定要使用的 database

USE xxxdb 注意，这个语句后没有;

SELECT

SELECT ... FROM ... WHERE ... FOR UPDATE 在事务操作时，可以用这个语句锁住对应的行，直到事务提交/回滚/超时。

  • 一般要先执行set autocommit=0;避免自动提交，最后执行commit;或rollback;，以及时解锁
  • 对NULL值的条件过滤方法：column IS NULL

INNER JOIN

在右边表中存在至少一个匹配时，INNER JOIN 关键字返回行，INNER JOIN 等价于 JOIN

  • join 时可以有多个 on

WHERE

  • WHERE column1 IN/NOT IN (value1, value2) 某字段在多个值范围内查找

DISTINCT

SELECT DISTINCT name1,name2 FROM table_name 取出的数据去重

DESCRIBE

显示 table 列信息

  • DESCRIBE dbName.tableName 以Name Null Type的文本表格方式显示列信息

  • DESC xx.xx 也可以用缩写命令

HAVING

HAVING 可以放在 GROUP BY 后，对分组后的结果设置条件。与 WHERE 相比可以使用聚合函数作为条件。

SELECT ... GROUP BY ... HAVING SUM(xxx) > 100;

LIMIT

限制符合条件的数据返回的条数

OFFSET

设置符合条件的数据返回的偏移量(跳过的数量)，和 LIMIT 结合可实现分页。

INSERT INTO

插入数据

INSERT INTO T1 (col1, col2) values(1,2);

UPDATE

UPDATE table1 SET col1 = val1, col2 = val2 WHERE col = val;

UPDATE SET CASE

  • 可用于批量更新心跳类场景
  • 如果有 CURRENT_TIMESTAMP 类型字段可同时更新
UPDATE your_table
SET
  value1 = CASE
    WHEN id = 1 THEN 'new_value_1'
    WHEN id = 3 THEN 'new_value_3'
    -- 根据需要添加更多的WHEN条件
    ELSE value1 -- 如果id不匹配任何条件，则保持原值
  END,
  value2 = CASE
    WHEN id = 1 THEN 'new_value_2'
    ELSE value1
  END,
  timestamp1 = CURRENT_TIMESTAMP  -- 更新符合 WHERE 条件的时间戳
WHERE id IN (1, 2, 3); -- 根据实际情况指定要更新的id范围
  • 未被 case 匹配的行，由于未发生变更，ON UPDATE CURRENT_TIMESTAMP 类型的字段 不会自动更新。

UPSERT

如果存在则更新；如果不存在则插入。同时会返回 id

UPSERT INTO T1 (col1, col2) values(1,2);

防止数据重复(除了唯一索引之外，用语句实现)

insert ignore into

插入数据时，如果数据存在，则忽略此次插入

INSERT ignore INTO login(`name`,`password`) VALUES("ganhuojun","password")

replace into

插入数据时，如果数据存在，则删除再插入

REPLACE INTO login(`name`,`password`) VALUES("ganhuojun","password")

insert if not exists

先判断 mysql 数据库中是否存在这条数据，如果不存在，则正常插入，如果存在，则忽略

INSERT INTO login ( `name`, `password` ) SELECT
'ganhuojun','password' FROM login WHERE NOT EXISTS (
  SELECT `name` FROM login WHERE `name` = 'ganhuojun')

on duplicate key update

插入数据时，如果数据存在，则执行更新操作

INSERT INTO login ( `name`, `password` )
VALUES ( "ganhuojun", "password" )
ON DUPLICATE KEY UPDATE PASSWORD = 'passwd'

UNION

将两个查询的内容合并，要求字段类型兼容，顺序一致

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

UNION ALL

同 UNION，要求字段名完全一致

DELETE

DELETE FROM table_name WHERE column_name = value 删除符合条件的行

PREPARE

预处理 SQL 语句，提高执行效率。相当于提前在 MySQL Server 端部署一个模板，执行 SQL 语句时只需传参数即可。

函数

注意，SQL 中数组下标是从 1 开始的。

count

SELECT COUNT(column_name) FROM table_name 统计数据条数，colum_name 可以直接用数字代替，如count(1)表示第一个字段

  • 如果 column_name 为主键，则count(column_name)效率会最高

字符串拼接

  • CONCAT(col1, col2) 拼接各种类型为字符串
  • CONCAT_WS(',', col1, col2) 以特定字符串间隔连接为字符串，同时进行类型转换(如 int 转为字符串)

replace

替换字符串

  • 有时特殊字符无法直接加到字符串中，可以用下面方法：
-- 查找以换行符开头的字段
SELECT * FROM test_table where name like replace('\n%','\n',char(10));

IF(条件，值 1，值 2)

  • 条件为 True 返回值 1，否则返回值 2
  • 可以利用多层 IF 嵌套实现复杂逻辑
  • MySQL 中没有 SWITCH，用 IF 嵌套即可实现

IFNULL

为 NULL 值填写默认值

SELECT IFNULL(column_name, default_value) FROM table_name

char_length

  • 计算字符串长度，不论中英文都按字符计算。如果需要按字节计算，使用length

INTERVAL

时间段相关的操作

  • INTERVAL expr unit

  • SELECT NOW() + INTERVAL 1 HOUR 取当前时间点向后 1 小时的时间

位函数

  • BIT_COUNT(x)计算位中 1 的个数
  • BIT_AND(x, y)按位取与后的结果
  • BIT_OR(x,y)按位取或后的结果

时间函数

  • CURDATE() 不带时分秒

  • NOW() 带时分秒

  • SELECT DATE_SUB(CURDATE(), INTERVAL 1 WEEK); 一周前

  • DATE_FORMAT(date, format) 格式化时间

  • DATE_ADD(date, INTERVAL expr unit) 时间加减

  • DATE_SUB(date, INTERVAL expr unit) 时间加减

  • DATE_ADD(date, INTERVAL expr unit) 时间加减

聚合函数

group by 聚合时对字段进行处理的函数

  • GROUP_CONCAT(column_name SEPARATOR ',') 将某字段值聚合为字符串，以特定分割符分割

DDL(Data Definition Language)

操纵表结构。DDL 语句默认带 commit，不能 rollback。

创建数据库

CREATE DATABASE dbName

  • 创建数据库时要指定字符集和编码方式(MySQL 默认 UTF-8 Unicode)、排序规则(MySQL 默认 utf8_general_ci)。之后对文字类型的字段也可以设定字符集和排序规则

  • 显示建表命令

show create table <table_name> \G;

全局变量

  • 查询变量
show variables like '%slow_query_log%';
  • 设置变量
set global slow_query_log = 1;

自动 commit(默认开启)

  • set autocommit=0; 关闭自动提交，需要最后手动执行commit;提交。
  • 在 commit 结束之前，可以通过 rollback 回滚之前在缓冲区的操作。
  • 也可以通过start transaction;开启一个事务，暂时关闭自动 commit 功能。
  • begin; 也可以开启一个事务

表

  • 查询表

SHOW TABLES [FROM dbName]

  • 查询建表命令
show create table t1;
  • 创建表
create table t1 {
  id int(5) primary key default 0;
} engine=innodb default charset=utf8;

  • 增加 IF NOT EXISTS 可以增加兼容性： CREATE TABLE db.tb IF NOT EXISTS {...

  • 删除表

DROP TABLE IF EXISTS `db.tb`;

-- 重命名表
RENAME TABLE `db1.tb1` TO `db2.tb2`;
  • 创建临时表
create temporary table ...
CREATE TEMPORARY TABLE ...

临时表会和 session 生命周期保持一致，名字可以与已存在表重复，并且show tables无法显示。

变更表内字段

  • 多条变更合并执行

    ALTER TABLE `aaa.bbb`
    ADD `column1` xxx,
    ...,
    ...;

  • 新增字段

ALTER TABLE `aaa.bbb` ADD COLUMN `name` VARCHAR(64) NOT NULL DEFAULT 0 COMMENT '' AFTER `xxx`;
  • 修改字段
alter table student modify name varchar(100) not null;
  • 删除字段
ALTER TABLE tableName DROP COLUMN [IF EXISTS] columnName;
  • 删除索引 
    ALTER TABLE tableName DROP INDEX `idx_xxx`

索引

  • 查询索引
show index from 'db1.t1';
  • 创建索引
-- 单值索引
create index a_idx on t1(name1);
-- 唯一索引
create unique index b_idx on t1(name1);
-- 复合索引
create index a_idx on t1(name1, name2, name3);
  • 增加索引
-- 单值索引
alter table t1 add index a_idx(name1);
-- 唯一索引
alter table t1 unique index b_idx(name1);
-- 复合索引
alter table t1 a_idx(name1, name2, name3);
  • 删除索引
drop index a_idx on t1;

DCL(Data Control Language)

修改权限