sql语句之必备基础用法（mysql）_select * from a right join b right join c-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_50652063/article/details/144248515

主要介绍mysql常用的sql语句。

DQL

基础用法

select distinct name from table1; --去重查询

select min(sal) from table1; -- 支持max(),sum(),avg()

select count(*) from table1; -- 或使用count(1) 或 count(字段名) 统计行数

select * from table1 where name like '%xx%'; --like '_xx' 代表匹配一个字符，%匹配任意多个字符

select * from table1 order by column1 limit 3; --按column1列排序，排序后取前3行，默认升序，DESC代表降序

select * from table where num between 1 and 10;

select * from table where num in (10, 20, 30);

select * from table where num is NULL;

select * from table where num is not NULL;

select * from table where num=10 or num=1;

注意点：where后不允许使用列字段别名，这是因为SQL语句的执行顺序导致了 WHERE 子句在字段别名被定义之前执行。

分组函数

SELECT column1, COUNT(*)
FROM table_name
GROUP BY column1
HAVING COUNT(*) > 1;

CASE...WHEN..THEN..END语句

time	res
2024-09-08	胜利
2024-09-08	失败
2024-09-08	胜利
2024-09-18	胜利
2024-09-18	失败

写sql输出结果是：

time	victory	fail
2024-09-08	2	1
2024-09-18	1	1

select time, count(if(res=='胜利',1, NULL)) as victory, count(if(res=='胜利',NULL,1)) as fail
from table group by time;

select time, count(case res when '胜利' then 1 else 0 END) as victory, count(case res when '胜利' then 0 else 1 END) as fail
from table group by time;

sql中的子查询

列子查询

大多都是列子查询

SELECT *
FROM table1
WHERE column1 IN (SELECT column2 FROM table2 WHERE 条件);

对于oracle数据，支持嵌套聚合函数，如下是列子查询：

xxx in (select max(avg(sal)) from emp group by deptno, select min(avg(sal)) from emp group by deptno)

行子查询

SELECT *
FROM table1
WHERE (column1,column2) = (SELECT column1, column2 FROM table2 WHERE 条件);

对于oracle数据，支持嵌套聚合函数，如下是行子查询：

xxx in (select max(avg(sal)), min(avg(sal)) from emp group by deptno)

sql中的连接

等值连接：

NATURAL JOIN，自然连接，两张表需要有相同的列名且列字段类型相同。

SELECT columns
FROM table1
NATURAL JOIN table2;

USING...ON子句，两张表需要有相同的列名，但列字段类型可不相同。

SELECT columns
FROM table1
JOIN table2 USING(common_field);

(INNER) JOIN...ON 子句，使用ON关键字明确关联条件，推荐使用。

SELECT columns
FROM table1
JOIN table2 ON table1.common_field = table2.common_field;

非等值连接：

LEFT/RIGHT (OUTER) JOIN...ON子句，左外连接返回左表的所有行，即使右表中没有匹配的行。如果右表中没有匹配的行，结果集中的右表字段将为NULL。

SELECT columns
FROM table1
LEFT JOIN table2
ON table1.common_field = table2.common_field;

FULL OUTER JOIN...ON 子句，全外连接返回两个表中的所有行，无论它们是否匹配。

SELECT columns
FROM table1
FULL OUTER JOIN table2
ON table1.common_field = table2.common_field;

需要注意的是，mysql并不支持全连接，但它可以通过以下方式达到全连接的效果。

SELECT * FROM a LEFT JOIN b ON a.name = b.name
UNION
SELECT * FROM a RIGHT JOIN b ON a.name = b.name;

# UNION会自动去重，UNION ALL会展示所有结果

交叉连接：

生成笛卡尔积，它将两个表中的每一行与另一个表中的每一行配对，生成所有可能的行组合。

SELECT columns
FROM table1
CROSS JOIN table2;

特殊应用：自连接

自连接是一种特殊的连接操作，其中一个表与自身进行连接。这种查询通常用于查找表中与另一行相关联的行。

INSERT INTO employees (employee_id, manager_id, employee_name, position) VALUES
(1, NULL, 'Alice', 'CEO'),
(2, 1, 'Bob', 'CTO'),
(3, 1, 'Charlie', 'CFO'),
(4, 2, 'David', 'Senior Developer'),
(5, 2, 'Eva', 'Project Manager'),
(6, 5, 'Frank', 'Developer');


SELECT 
    e1.employee_name AS 'Employee',
    e1.position AS 'Position',
    e2.employee_name AS 'Manager',
    e2.position AS 'Manager Position'
FROM 
    employees e1
LEFT JOIN 
    employees e2
ON 
    e1.manager_id = e2.employee_id;

返回结果如下：

Employee	Position	Manager	Manager Position
Alice	CEO	NULL	NULL
Bob	CTO	Alice	CEO
Charlie	CFO	Alice	CEO
David	Senior Developer	Bob	CTO
Eva	Project Manager	Bob	CTO
Frank	Developer	Eva	Project Manager

再举个例子，我要求这张表中time字段相邻的差值

id	time
1	2024-12-24 09:00:04
2	2024-12-24 09:40:26

select timestampdiff(second, t1.time, t2.time) diff from table t1
left join table t2
on t1.id=(t2.id-1);

DML

insert语句

insert into 表名

values

(1, 'jj', 30, 'M'),

(2, 'gg', 23, 'F');

或者

insert into 表名(id, name, age, sex, remark)

values

(1, 'jj', 30, 'M', NULL),

(2, 'gg', 23, 'F', 'beizhu');

注意，以上语句只适用于mysql，而oracle是不能这样多行插入的。

还有，可以通过select语句插入，两边列字段个数要一致：

insert into 表名(column1, column2...)

select column1, column2... from table2 where [condition];

update语句

update 表名 set column1='xxx' where [condition]; --更新匹配的行
update students set name = CONCAT(name, '_test'); --字段后追加_test

update students set name = REPLACE(name, 'aa', 'bb'); --字段内容aa替换为bb

update students set name = IFNULL(name, ’default'); --字段内容若为空，填充default

delete语句

delete from table where [condition]; --删除匹配的行

delete from table; --删除表中所有数据
delete from table order by age limit 3; --排序后删除

DDL

数据定义语言，要了解这块，必须先了解mysql数据库的数据类型和约束。

数据类型

详细内容请参考：MySQL 数据类型 | 菜鸟教程

这里大概介绍下：

数值型：

整数类型：bool、tiny int、small int、medium int、int、big int
浮点数类型：float、double
定点数类型：decimal

字符串类型：

字符串：char、varchar
文本类型：tiny text、text、medium text、longtext
二进制文本类型：tiny blob、blob、medium blob、long blob

日期类型：date、datetime、timestamp、time、year

其他数据类型：set等

约束

NOT NULL 、UNIQUE、PRIMARY KEY、CHECK(>mysql 8.0）、FOREIGN KEY、DEFAULT

外键一定要指向有唯一约束的列，可以是主键，也可以非主键。

表级约束：主键、外键、唯一、check

列级约束：非空、默认、唯一、check

create语句

create table 表名(

id int primary key,

name varchar(20) NOT NULL UNIQUE,

age tinyint default 20，

birth_day datetime,

sex char(4) check(sex in ('F', 'M')),

class_id int,

FOREIGN KEY(class_id) references class(id)

);

或者

create table 表名(

id int,

name varchar(20),

age tinyint，

birth_day datetime,

sex char(4),

class_id int,

FOREIGN KEY(class_id) references class(id)，

PRIMARY KEY(id, name), # 联合主键

UNIQUE(age, birth_day), # 复合唯一

CHECK(sex in ('F', 'M')）

);

create table 表名 as select * from another_表名;

create table 表名(字段1，字段2...) as select 字段1，字段2... from another_表名;

alter语句

### 添加约束

alter table 表名 add constraint 约束名 unique(字段名);

alter table 表名 add constraint 约束名 check(字段名>1);

alter table 表名 add constraint 约束名 foreign key(字段名) references 表名(字段名);

alter table 表名 add primary key(字段名);

alter table 表名 modify 字段名 NOT NULL;

alter table 表名 modify 字段名 DEFAULT 'PPP';

### 删除约束

alter table 表名 drop constraint 约束名; --删除约束(约束名可以自己创建，若没创建，系统默认生成一个）

alter table 表名 drop primary key; # 删除主键

alter table 表名 drop index 索引名；# 删除唯一约束

alter table 表名 drop check 检查约束名; # 删除检查约束

alter table 从表名 drop foreign key 外键约束名; # 删除外键约束

alter table 从表名 drop index 索引名; # 删除外键的索引

alter table 表名 modify 字段名 NULL; # 删除非空约束

alter table 表名 modify 字段名数据类型; # 删除默认值约束、非空约束、自增

alter table 表名 drop column 列名1, 列名2 # 删除列

drop语句

drop table 表名；

drop table if exists 表名；

DCL

控制语句

用户管理

create user 'test'@'%' identified by '123456'; --创建用户

alter user 'test'@'%' identified by '654321'; --修改密码

drop user test; --删除用户

权限管理

grant all privileges on 数据库名.表名 to USER；--赋权限

revoke all privileges on 数据库名.表名 from USER； --收回权限

grant create any table to USER;

flush privileges; --刷新权限

开启远程访问的设置：

use mysql;

UPDATE user SET host='%' WHERE user='your_username';

flush privileges;

或者

grant all privileges on *.* to 'USER'@'%' with grant option; --权限级联

远程访问mysql数据库的正确打开方式_mysql 开启远程访问-优快云博客

事务

只有InnoDB引擎才支持事务。原子性、一致性、隔离性、持久性：

begin;
update account set money= money + 100 where name='A';
SAVEPOINT S1;
update account set money= money + 100 where name='B';
SAVEPOINT S2;
exception when others then
ROLLBACK TO S1;  -- 回滚到S1点
commit;  -- 提交事务

for update，用于事务中的select语句，给加行级锁或表级锁。

START TRANSACTION;

SELECT * FROM table_name WHERE id = 1 FOR UPDATE;

UPDATE table_name SET column_name = 'new_value' WHERE id = 1;

COMMIT;

日期函数

# 日期内置函数
select NOW();  # 2024-12-12 15:14:00
SELECT CURDATE();  # 2024-12-12
SELECT CURTIME();  # 15:14:26
SELECT CURRENT_TIMESTAMP();  # 2024-12-12 15:14:59
SELECT CURRENT_DATE();  # 2024-12-12

# DATE_SUB DATE_ADD  DATE_FORMAT DATEDIFF举例
# 据当前日期相差20天的行 DATE(datetime)-》2024-11-22
SELECT DATE(datetime) FROM ai_model am WHERE DATE_SUB(CURDATE(), INTERVAL 20 DAY) <= DATE(datetime); 
# 给datetime字段加上1个月展示
SELECT DATE_ADD(datetime, INTERVAL 1 MONTH), datetime FROM ai_model am WHERE datetime BETWEEN '2024-11-06 12:00' AND '2024-11-11 12:00';   
# CURDATE()-datetime的天数差
SELECT DATEDIFF(CURDATE(), datetime) from ai_model am WHERE datetime  like '2024-%'; 
# 06-14 11:17  格式化输出
SELECT DATE_FORMAT(datetime, '%m-%d %H:%i') from ai_model am;


# TIMESTAMPDIFF 计算小时分钟差异，第一个参数是返回值的单位
SELECT timestampdiff(minute, datetime1, datetime2)/60 from table;

mysql常提起的几种日志

slow_query_log：mysql记录所有执行超过long_query_time参数设定的时间阈值的sql语句，因为查询慢，所以称为slow_query_log。日志输出方式有FILE和TABLE两种，FILE文件记录在slow_query_log_file字段中；TABLE是记录在mysql.slow_log表中。默认关闭。

general_log：记录MySQL所有sql语句，无论是DML DCL DQL DDL，日志非常大，所以不建议开启；默认关闭。

# 打开慢查询日志，并查看日志存储路径
show variables like '%slow_query%';
set GLOBAL slow_query_log='ON'; # 打开日志开关
set GLOBAL long_query_time=3;  # 设置慢查询时间为3s, 不加global，重启客户端会恢复；
"""
slow_query_log	ON
slow_query_log_file	/var/lib/mysql/9064e6425e7a-slow.log
"""
# 查看日志输出方式
show variables like 'log_output';  # FILE

# 查看表的创建语句
show create table mysql.slow_log;
show create table mysql.general_log;

# 一般日志
show variables like '%general_log%';
"""
general_log	OFF
general_log_file	/var/lib/mysql/9064e6425e7a.log
"""

bin_log：记录所有对数据库表结构变更和表数据修改的操作，不包括查询日志select 、show此类语句，它是二进制的，要通过mysqlbinlog工具解析查看。我们常说的“删库跑路”，就需要用bin_log来恢复：(log_bin 默认开启)

Bin-log是追加写的模式，不去主动删除磁盘的日志文件，并且磁盘的空间还足够，一般Bin-log日志文件都会在本地，当你删库后，可以直接去本地找Bin-log的日志文件，然后拷贝出来一份，再打开最后一个文件，把里面删库的记录手动移除，再利用mysqlbinlog工具导出xx.SQL文件，最后执行该SQL文件即可恢复删库前的数据。
————————————————

版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
原文链接：https://blog.youkuaiyun.com/weixin_46984703/article/details/139971597

当慢查询日志发现有查询语句执行慢的时候怎么分析呢？一种方法就是加上explain关键字，以模拟优化器执行SQL查询语句，它会返回执行计划的信息，并不真正执行这条SQL。

Explain可以用来分析SQL语句和表结构的性能瓶颈。通过explain的结果，可以了解到如下内容：

1、表的读取顺序

2、数据读取操作的操作类型

3、哪些索引可以使用

4、哪些索引被实际使用

5、表之间的引用

6、每张表有多少行被查询