【MySQL必知必会 常用的SQL语句（二）】

最新推荐文章于 2023-02-13 20:06:06 发布
置顶 最新推荐文章于 2023-02-13 20:06:06 发布
阅读量223 收藏 1
点赞数
分类专栏： 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_43372187/article/details/106721369
版权

文章目录

SQL开发

DML(Data Manipulation Language)

关键字作用
INSERT向表中插入数据
DELETE删除表中数据
SELECT查询表中数据
UPDATE更新表中数据

向表中写入数据

在这里插入图片描述
ON DUPLICATE KEY UPDATE ：在出现主键冲突或索引冲突的时候，加上这条语句，更新除了唯一索引主键列外的其他列的值.
编写INSERT语句的思路：

  • 确认要把数据插入到哪个表中
  • 确认表的数据库结构，哪些列不能为NULL,不能为null列是否有默认值
  • 确认插入列的插入值清单

举个例子，向imc_class分类表中的分类名称列插入数据：

INSERT INTO imc_class(class_name) VALUES ('MySQL'),('Redis'),('MongoDB');

接着将该列的值设置为唯一索引，让该列的值不重复，

CREATE UNIQUE INDEX uqx_classname ON imc_class(class_name);

若要再次插入相同的值，则需加上ON DUPLICATE KEY UPDATE

INSERT INTO imc_class(class_name) VALUES ('MySQL'),('Redis'),('MongoDB') ON DUPLICATE KEY UPDATE add_time=CURRENT TIME;

这样就可以插入成功，同时发现加入时间这一列的值更新为当前的时间。

查询表中的数据

在这里插入图片描述

ALL语句

从课程表中选出学习人数这一列所有的值

SELECT ALL study_cnt FROM imc_course;
DISTINCT语句

对于每一列中可能会有重复的值，DISTINCT可以滤除重复的值
有如下一张表CESHIDEMO
在这里插入图片描述
执行以下语句得到如下结果：

select distinct name from CESHIDEMO

在这里插入图片描述
但如果查询的是多个字段，则只会滤除所有字段值相同的记录，如

SELECT DISTINCT  name, age FROM CESHIDEMO；

得到的结果依然是：
在这里插入图片描述

WHERE子句

1.使用LIKE关键字过滤字符串
我们这有这有一个需求，查询所有课程名中包含MySQL的课程名称

SELECT title
FROM imc_course
WHERE title LIKE '%MySQL%';

这个查询语句中LIKE起到过滤作用，MySQL中前面%代表任意个字符，后面一个%也代表任何数量的字符，这句查询语句就是过滤出所有含有MySQL的课程名称；

编写一条简单的SELECT语句的思路
  • 确认要查询的数据在哪些表中
  • 确认要查询的字段
  • 确认是否需要对数据过滤
MySQL的比较运算符
比较运算符说明
= > < >= <= <> !=<>和!=都表示不等于
BETWEEN min AND max列的值大于等于最小值，小于等于最大值
IS NULL,IS NOT NULL判断列的值是否为空
LIKE, NOT LIKE%代表任何数量的字符，下划线代表任意一个字符
IN , NOT IN判断列的值是否在指定范围内

1.查找学习人数大于1000的课程，列出课程标题和学习人数

SELECT title,study_cnt
FROM imc_course
WHERE study_cnt>1000
;

2.查找学习人数大于等于1000小于等于2000的课程，列出课程标题和学习人数

SELECT title,study_cnt
FROM imc_course
WHERE study_cnt BETWEEN 1000 AND 2000
;

3.建一张表，插入数据，查找值为空的列

CREATE TABLE test_id(id INT,c VARCHAR(10),PRIMARY KEY(id));
INSERT INTO test_id VALUES(1,'aa'),(2,NULL),(3,'cc');
SELECT * FROM test_id WHERE c IS NULL;

在这里插入图片描述
4.查找一段字符串是否包含某一段字符：

SELECT 'this is mysql course' LIKE '%mysql%';

在这里插入图片描述

SELECT 'mysql' LIKE '_ysql';

在这里插入图片描述

SELECT 'xxxxxxxmysql' LIKE '_ysql';

在这里插入图片描述
5.查找课程ID为1,3，5,7，9的课程名称

SELECT title
FROM imc_course
WHERE course_id IN (1,3,5,7,9);
MySQL的逻辑运算符

在这里插入图片描述
任何值与NULL进行逻辑运算都为NULL。

1.查询出课程标题含有MySQL关键字且学习人数大于5000的课程中的课程标题和学习人数

SELECT title,study_cnt
FROM imc_course
WHERE title LIKE '%MySQL%' AND study_cnt>5000;

2.查询出课程标题含有MySQL关键字或者学习人数大于5000的课程中的课程标题和学习人数

SELECT title,study_cnt
FROM imc_course
WHERE title LIKE '%MySQL%' OR study_cnt>5000;

3.查询出课程标题不含有MySQL关键字但学习人数大于5000的课程中的课程标题和学习人数
和查询出课程标题含有MySQL关键字但学习人数不大于5000的课程中的课程标题和学习人数
法一：

SELECT title,study_cnt
FROM imc_course
WHERE title LIKE '%MySQL%' XOR study_cnt>5000;

法二：

SELECT title,study_cnt
FROM imc_course
WHERE title LIKE '%MySQL%' AND study_cnt<=5000
UNION ALL
SELECT title,study_cnt
FROM imc_course
WHERE title NOT LIKE '%MySQL%' AND study_cnt>5000;
多表查询
  • 表与表之间要存在一定的关联关系
    在这里插入图片描述
使用JOIN关联多个表

在这里插入图片描述

内关联

JOIN分为内关联和外关联，其中内关联就是找出两个表中关联之后都存在的数据，也就是找出两个表的交集。

我们来通过一个例子来理解一下，这里有个需求，就是查询出每一门课程的课程ID，课程名称和章节名称，这里就要关联课程表和章节表，通过课程ID将他们关联起来，

SELECT a.course_id,a.title,b.chapter_name
FROM imc_course a
JOIN imc_chapter b ON b.course_id=a.course_id;

在这里插入图片描述
假如我们在课程表中插入一条新数据，这时候再内关联查询，是否可以查询出这条新数据呢？
在这里插入图片描述
会发现无法查询出该数据，所以说，内关联只能查询出同时存在于两个表中的数据，那么怎样查询出这条数据呢，这就需要外关联

外关联

左外关联：
在这里插入图片描述
左外关联查询到的数据就是一定存在于左表中的，若右表的关联列为空，则只查询到左表且不存在于右表的数据（即用于查询不存在右表的数据的情况）；所以上文中提到的在左表新加一条记录如何查询到，就用左外关联LEFT JOIN；

SELECT a.course_id,a.title,b.chapter_name
FROM imc_course a
LEFT JOIN imc_chapter b ON b.course_id=a.course_id;
WHERE title='MySQL关联测试';

如我们要查询只存在于课程表，但是不存在于章节表中的课程ID和课程名称

SELECT a.course_id,a.title,b.chapter_name
FROM imc_course a
LEFT JOIN imc_chapter b ON b.course_id=a.course_id
WHERE b.course_id IS NULL;

右外关联：
在这里插入图片描述
与左外关联一样，可以查询存在于右表或只存在于右表的数据，其他用法参照左外关联

分组查询GROUP BY…HAVING子句
  • 把结果集按某些列分成不同的组，并对分组后的数据进行聚合操作

如下，我们想要把课程表中按难度计算每一种难度的课程数目为多少
在这里插入图片描述
在这里，level_name为分组键，所有非聚合函数列都应作为分组键

SELECT b.level_name,count(*)
FROM imc_course a
JOIN imc_level  b ON b.level_id=a.level_id
GROUP BY level_name

在这里插入图片描述

  • 可以通过选择合适的HAVING子句对聚合后的数据进行过滤

需求：统计每个分类下课程大于3门的难度有哪些

SELECT level_name,class_name,count(*)
FROM imc_course a
JOIN imc_level b ON b.level_id=a.level_id
JOIN imc_class c ON c.class_id=a.class_id
GROUP BY level_name,class_name HAVING count(*)>3
;
聚合函数

在这里插入图片描述
1.统计不同难度下的课程学习人数

SELECT level_name,SUM(study_cnt)
FROM imc_course a
JOIN imc_level b ON b.level_id=a.level_id
GROUP BY level_id
;

在这里插入图片描述
2.统计每门课程的平均学习人数

SELECT AVG(study_cnt)
FROM imc_course

3.查询出课程人数最多的课程
要先查询出最多的人数是多少，再将这作为条件再查找人数为这个的课程

SELECT title,study_cnt
FROM imc_course
WHERE study_cnt=(SELECT MAX(study_cnt) FROM imc_course)
;
使用ORDER BY 子句进行排序
  • 列名后加ASC是按该列的升序排序，加DESC指定按该列的降序排序
  • 也可以使用SELECT中未出现的列或函数

1.查询出所有课程的学习人数并按从高到低排列（降序）

SELECT title,study_cnt
FROM imc_course
ORDER BY study_cnt DESC
使用LIMIT分页查询
  • 常用于数据列表分页
  • 一定要配合ORDER BY子句使用
  • LIMIT 起始偏移量，结果集的行数

1.分页返回课程ID和课程名称，每页返回10行数据

SELECT course_id,title
FROM imc_course
ORDER BY study_cnt DESC
LIMIT 0,10
;
创建视图

在这里插入图片描述
1.定义一个包括课程ID，课程名称，分类名称，方向名称，难度名称的视图

CREATE VIEW vm_view
AS
SELECT a.course_id,a.title,b.class_name,c.type_name,d.level_name
FROM imc_course
JOIN imc_class b ON b.class_id=a.class_id
JOIN imc_type c ON c.type_id=a.type_id
JOIN imc_level d ON d.level_id=a.level_id
;

按条件删除表中的数据

在这里插入图片描述
1.删除课程方向表中重复的方向，并且保留ID最小的一条，并在方向名称上增加唯一索引

DELETE a
FROM imc_type a
JOIN
(SELECT type_name,MIN(type_id),COUNT(*)
FROM imc_type
GROUP BY type_name HAVING COUNT(*)>1) b
ON b.type_name=a.type_name AND a.type_id>b.type_id;

//建立唯一索引
CREATE UNIQUE INDEX uqx_typename ON imc_type(type_name);

数据更新语句UPDATE

在这里插入图片描述

  • 确定要更新的数据存在哪个表中
  • 确定要更新的列及列的值
  • 确定过滤条件

1.随机推荐10门课程
首先先给课程表中添加一列，是否推荐，

ALTER TABLE imc_course
ADD is_recommend TINYINT DEFAULT 0 COMMENT '是否推荐，0不推荐，1推荐';
UPDATE imc_course
SET is_recommend=1
ORDER BY RAND()
LIMIT 10
;
SELECT title,course_id
FROM imc_course
WHERE is_recommend=1
;

系统函数

时间函数

在这里插入图片描述
1.

SELECT CURDATE(),CURTIME(),NOW()；

在这里插入图片描述
2.
在这里插入图片描述

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s')；

在这里插入图片描述
3.

SELECT SEC_TO_TIME(60),TIME_TO_SEC('01:00:00')；

在这里插入图片描述
在这里插入图片描述
4.

SELECT NOW(),
       DATE_ADD(NOW(),INTERVAL 1 DAY),--当前日期加1天
       DATE_ADD(NOW(),INTERVAL 1 YEAR),--当前日期加1年
       DATE_ADD(NOW(),INTERVAL -1 DAY),--当前日期减1天
       DATE_ADD(NOW(),INTERVAL '-1:30' HOUR_MINUTE)；--当前日期减1个小时30分钟

在这里插入图片描述
5.

SELECT NOW(),
       EXTRACT(YEAR FROM NOW()),--提取年
       EXTRACT(MONTH FROM NOW()),--提取月
       EXTRACT(DAY FROM NOW()),--提取日期
       EXTRACT(HOUR FROM NOW()),--提取小时
       EXTRACT(MINUTE FROM NOW()),--提取分钟
       EXTRACT(SECOND FROM NOW());--提取秒

在这里插入图片描述

字符串函数

在这里插入图片描述
1.

SELECT CONCAT_WS('||',class_name,title)
FROM imc_course a
JOIN imc_class b ON b.class_id=a.class_id
;

在这里插入图片描述
2.

SELECT class_name,LENGTH(class_name),CHAR_LENGTH(class_name)
FROM imc_class；

在这里插入图片描述
3.

SELECT  FORMAT('123456.789',4)

在这里插入图片描述
在这里插入图片描述
1.LEFT / RIGHT函数

SELECT LEFT('www.baidu.com',6),
       RIGHT('www.baidu.com',6)

在这里插入图片描述
2.SUBSTRING函数

SELECT SUBSTRING('www.baidu.com',6);

在这里插入图片描述
3.SUBSTRING_INDEX函数

SELECT SUBSTRING_INDEX('www.baidu.com','.',-1),
       SUBSTRING_INDEX('www.baidu.com','.',1);

在这里插入图片描述
4.LOCATE函数

SELECT title
       ,LOCATE('-',title)
       ,SUBSTRING(title,1,LOCATE('-',title)-1)
FROM imc_course

在这里插入图片描述
5.TRIM函数

SELECT TRIM( '  imooc '),TRIM('x' FROM 'xxximoocxxx')

在这里插入图片描述

其他函数

在这里插入图片描述

MYSQL8.0新特性

公共表表达式

在这里插入图片描述

//公共表定义
WITH cte AS (
SELECT title,study_cnt,class_id
FROM imc_course
WHERE study_cnt>2000
)
//查询
SELECT *
FROM cte
UNION ALL--连接
SELECT *
FROM cte
ORDER BY title;

公共表表达式可以多次引用，与子查询不同，子查询只可引用一次
在这里插入图片描述
还可用公共表表达式生成递归序列：

WITH RECURSIVE test AS (
SELECT 1 AS n
UNION ALL
SELECT 1+n FROM test WHERE n<10
)
SELECT * FROM test;

在这里插入图片描述

窗口函数

在这里插入图片描述
1.
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
然后执行以下查询语句

SELECT study_name,class_name,score
       ,ROW_NUMBER() OVER (PARTITION BY class_name ORDER BY score DESC) AS rw
       ,RANK() OVER (PARTITION BY class_name ORDER BY score DESC) AS rk
       ,DENSE_RANK() OVER (PARTITION BY class_name ORDER BY score DESC) AS drk
FROM test
ORDER BY class_name,rw;

在这里插入图片描述
2.计算每门课程学习人数占总学习人数的百分比

WITH tmp AS(
SELECT class_name,title,study_cnt
       ,SUM(study_cnt) OVER(PARTITION BY class_name ) AS class_total
FROM imc_course a
JOIN imc_class b ON b.class_id=a.class_id)
SELECT class_name,title,CONCAT(study_cnt/class_total*100,'%')
FROM tmp
ORDER BY class_name

在这里插入图片描述

SQL开发规范

  • 使用SELECT…LIMIT 1来判断是否存在符合条件的数据，不要使用COUNT(*)
  • 在执行一条更新语句后，使用ROW_COUNT()判断是否更新成功
  • 使用JOIN关联代替子查询，由于子查询的性能差，子查询的结果集无法使用索引，通常子查询的结果集会被存储到临时表中，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响。特别是对于返回结果集比较大的子查询，其对查询性能的影响也就越大。
  • 在明显没有重复值的时候使用UNION ALL对结果进行连接
  • 尽量减少使用SELECT *进行查询，应使用SELECT 【字段名】
爱技术的小小林
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
MySQl数据库必知必会sql语句(加强版)
09-09
本文给大家分享了一篇关于mysql数据库必会sql语句加强版内容，非常不错，具有参考借鉴价值，需要的朋友参考下吧
sql必知必会
谁家的喵
07-24 1586
SQL(Structured Query Language：结构化查询语言)，是一种专门用来与数据库沟通的语言。SQL的优点：SQL不是摸个特定数据库厂商的专有语言。绝大多数重要的DBMS支持SQL，所以学习了SQL就能够与几乎所有数据库打交道............
mysql中使用like模糊查询时如何转义%
gm_163的专栏
03-25 2823
我们经常使用like语句配合%%通配符来进行模糊查询，那么如果字段内容本身含有%，而你要查询包含有%的记录怎么办呢怎么办呢。假设字段是title，那么下面的语句可以吗？ select * from table where title like “%%%”; 如果你试过就知道，肯定不行的，那么正确的写法是怎样的呢？ 我们应该对%符号进行转义： select * from table wher
SQL 必知必会（总结）
No Excuses! Confidence! Vigorous！
04-09 1498
一．SQL基础： SELECT  列名  FROM  表名 或者 SELECT * from  表名。SELECT LastName,FirstName FROM Persons *  所有的字段！ SQL对大小写不敏感！ 3．分号是在数据库系统中分隔每条 SQL 语句的标准方法，这样就可以在对服务器的相同请求中执行一条以上的语句！ 4. SQL distinct：在表中，可能会包含重复
SQL必知必会【笔记】
isymikasan的博客
02-13 1355
sql系统学习，sql必知必会学习笔记
Mysql必知必会
12-14
查看建表语句或者创建数据库的语句 show create table table_name show create database database_name; 用来显示授权用户的安全权限 show grants 用来显示服务器错误或者警告 show errors show warnings limit...
mysql必知必会_MYSQL_MySQL必知必会_
09-29
mysql入门教材，包括pdf和操作的sql语句，适合新手入门
SQL必知必会（第五版）
醉与浮的博客
12-08 8657
SQL必知必会(第五版)快速学习
SQL零基础入门必知必会！
华章IT官方博客
10-07 1万+
???? 前言SQL语言有40多年的历史，从它被应用至今几乎无处不在。我们消费的每一笔支付记录，收集的每一条用户信息，发出去的每一条消息，都会使用数据库或与其相关的产品来存储，而操纵数据库的语言...
史上最全SQL基础知识总结(理论+举例)
热门推荐
PILIpilipala的博客
02-13 14万+
SQL基础知识一、SQL 概述1、什么是 SQL2、语法要求二、SQL 分类三、DDL（Data Definition Language）：数据定义语言基本操作1.操作数据库2.数据类型3.操作表四、DML（Data Manipulation Language）：数据操作语言1.插入数据2.修改数据3.删除数据五、DCL（Data Control Language）：数据控制语言1.创建用户2.给用户授权3.撤销授权4.查看用户权限5.删除用户6.修改用户密码（以root身份）六、DQL（Data Quer
SQL必知必会知识点汇总
m0_62279905的博客
09-13 1900
又一个SQL学习阶段结束啦！
SQL必知必会 | 1
itsxwz
05-21 290
一、了解SQL：一门半衰期很长的语言 数据库管理系统，例如MySQL、Oracle等的语言，最具有中台能力的语言便是SQL。自从加入TIOBE编程语言排行榜，就一直保持在Top 10。 1.技术人员、产品经理、运营人员都需要掌握SQL。 2.从诞生到现在，很少变化。 3.SQL入门并不难。 1.DDL，英文叫做 Data Definition Language，也就是数据定义语言，它用来定义我们的数 据库对象，包括数据库、数据表和列。通过使用 DDL，我们可以创建，删除和修改数据库 和表结构。 2.DML，
MYSQL必知必会,详尽入门,一文帮你学会SQL必知必会
小杰312的博客
03-29 9799
MYSQL基操一网打尽,详尽入门,一文帮你学会SQL必知必会
深耕MySQL - SQL必知必会
你今天真好看呀
01-02 575
文章目录01. 数据准备02. 选择语句 select03. 排序检索数据04. 过滤数据05. 高级数据过滤06. 用通配符进行过滤07. 聚合函数08. 分组数据09. 内连接10. 外连接11. 三张表连接12. 子查询 01. 数据准备 drop table if exists dept; drop table if exists salgrade; drop table if exists emp; create table dept( deptno int(10) primary ke
SQL必知必会笔记
zhaohui blog
08-06 1215
极客时间-陈旸-SQL必知必会 笔记整理
【SQL必知必会】
专注Java（全栈）应用开发，求知若渴，虚心若愚，而知也无涯
08-01 165
组合查询： 本章讲述如何利用的union 的操作符合将多条的select 语句组合成一个结果集合。 17.1 组合查询 多数sql 查询的都只能从一个多个表中返回数据的单条select 语句。Mysql也允许执行多个查询，（或多条select 语句），并将结果查询结果返回。 这些组合的查询的称之为并或符合查询。 有2中情况需要是使用组合查询： 1. 在单个查询中从不同的表中返回类似结...
mysql必知必会 pdf完整版
最新发布
01-20
《Mysql必知必会》是一本关于MySQL数据库的入门级书籍，它涵盖了MySQL数据库的基本概念、SQL语句的基本使用和一些常见的数据库操作。这本书的完整版PDF可以在互联网上找到并下载，它对想要学习MySQL数据库的初学者来...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

爱技术的小小林

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值