Mysql常用sql语句与刷题知识点

目录

  • 1. 常用sql
  • 2. 刷题知识点

1. 常用sql

#查询MySQL中所有的数据库
SHOW DATABASES;
#查询当前正在使用的数据库
SELECT DATABASE();#普通创建（创建已经存在的数据库会报错）
CREATE DATABASE 数据库名称;
#创建并判断（该数据库不存在才创建）
CREATE DATABASE IF NOT EXISTS 数据库名称;
#创建一个数据库，并指定字符集
create database itheima default charset utf8mb4;#普通删除（删除不存在的数据库会报错）
DROP DATABASE 数据库名称;
#删除并判断（该数据库存在才删除）
DROP DATABASE IF EXISTS 数据库名称;USE 数据库名称;CREATE TABLE 表名(字段名1 数据类型,字段名2 数据类型,...字段名n 数据类型  -- 最后一行不能加逗号！
);
CREATE TABLE IF NOT EXISTS `example_table` (`id` INT NOT NULL AUTO_INCREMENT,`name` VARCHAR(100) NOT NULL,`email` VARCHAR(100) NOT NULL,`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,PRIMARY KEY (`id`)，#主键UNIQUE KEY `email` (`email`),#唯一键INDEX idx_column2 (`name`)#索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;普通索引：
CREATE INDEX index_name ON table_name(column_name);
唯一索引：
CREATE UNIQUE INDEX index_name ON table_name(column_name);
全文索引（仅适用于MyISAM引擎）：
CREATE FULLTEXT INDEX index_name ON table_name(column_name);
组合索引：
CREATE INDEX index_name ON table_name(column1_name, column2_name);
添加索引：ALTER TABLE users ADD INDEX idx_email (email);
删除索引：ALTER TABLE users DROP INDEX idx_email;#查询当前数据库中所有表的名称
SHOW TABLES;
#查询表的结构
DESC 表名;
#查看建表语句（还能查看到建表时没写的默认参数）
show create table 表名;#修改表名
ALTER TABLE 表名 RENAME TO 新的表名;
#添加一列
ALTER TABLE 表名 ADD 列名 数据类型 [ COMMENT 注释 ];
#修改某列（字段）数据类型
ALTER TABLE 表名 MODIFY 列名 新的数据类型;
#修改列名和数据数据类型
ALTER TABLE 表名 CHANGE 旧列名 新列名 新数据类型 [ COMMENT 注释 ];
#删除列（字段）
ALTER TABLE 表名 DROP 列名;#普通删除（删除不存在的表会报错）
DROP TABLE 表名;
#删除并判断（该表存在才删除）
DROP TABLE IF EXISTS 表名;
#删除指定表并重新创建（相当于清空表中的数据）
TRUNCATE TABLE 表名;#给指定列添加数据
INSERT INTO 表名(列名1,[列名2],[...)VALUES(值1,[值2],[...]); -- 值1对应列名1,...
#给全部列添加数据（相当于添加新的一行）
INSERT INTO 表名[所有列名] VALUES(值1,值2,...);
-- []中表示可以省略，省略时默认按顺序填写字段，不建议省略
#批量添加数据
INSERT INTO 表名(列名1,[列名2],[...])VALUES(值1,值2,...),[(值1,值2,...)],[...];
#批量给全部列添加数据（相当于添加新的多行）
INSERT INTO 表名[所有列名] VALUES(值1,值2,...),(值1,值2,...),...;#修改数据
UPDATE 表名 SET 列名1=值1,[列名2=值2],[...][WHERE 条件];
-- 注意：如果不使用WHERE条件，会将表中所有数据进行修改！DELETE FROM 表名 [WHERE 条件];
-- 注意：如果不使用WHERE条件，会将表中所有的数据删除！#开启事务
BEGIN;  -- 或者使用START TRANSACTION开启事务
#提交事务
COMMIT;
#回滚事务
ROLLBACK; -- 恢复到事物开始前

2. 刷题知识点

（1）null要用is、<=>来判断

select name from Customer where not referee_id <=> 2;
select name from Customer where referee_id != 2 or referee_id is null;

（2）用DISTINCT关键字对返回指定结果字段去重
（3）order by 字段a 默认是升序排列字段a的结果。在字段后面加上关键字DESC（相反的为ASC）即可降序盘列。
（4）order by后面可加多个字段，用逗号隔开，越靠前的字段越优先排序。

select distinct viewer_id as id from views where viewer_id = author_id order by id;
select distinct viewer_id as id from views where viewer_id = author_id order by id DESC;

（5）普通函数可以用在where后面和select后面。聚集函数只能用在select后面（行组）

select count(tweet_id) from tweets where Length(content) > 15;

（6）利用子查询进行过滤，其在where后面的位置，与in连用，且要保证子查询的结果列与外层where的条件列相匹配，相当于子查询查出了一个集合（1，2，3，4）。计算顺序为从内到外。

select * from p1 where id in(select id from p2)

（7）如果表达式的值至少与子查询结果的某一个值相比较满足θ 关系，则“表达式 θ some (子查询)”的结果便为真；

找出001号课成绩不是最高的所有学生的学号
Select S# From SC
Where C# = “001” and
Score < some ( Select Score From SC Where C# = “001” );

（8）如果表达式的值与子查询结果的所有值相比较都满足θ 关系，则“表达式 θ all (子查询)”的结果便为真；

找出工资最低的教师姓名
Select Tname From Teacher
Where Salary <= all ( Select Salary From Teacher );

（9）内联结：通过匹配两个表中两个列中每行的值相等，输出仅包含匹配的所有列与行（包括匹配的两个列重复出现），对于没有匹配到的行将不会出现。

//用INNER JOIN 表2 ON 匹配条件
//用JOIN 表2 ON 匹配条件
SELECT employees.employee_name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;

//用from 表1,表2 where 匹配条件
SELECT employees.employee_name, departments.department_name
FROM employees,departments where employees.department_id = departments.department_id;

（10）外联结：通过匹配两个表中两个列中每行的值相等，指定输出某一个表的所有行（即在匹配到的所有行的基础上，加上某一个表没匹配到的剩余行），匹配上的行正常输出，没有匹配上的行值为null。外联结包括左向外联结（‌LEFT JOIN或LEFT OUTER JOIN）‌、‌右向外联结（‌RIGHT JOIN或RIGHT OUTER JOIN）‌

SELECT employees.employee_name, departments.department_name
FROM employees
OUTER JOIN departments ON employees.department_id = departments.department_id;

(11) union：用于按行拼接多个select语句的查询结果，每个select查询结果必须包含相同的列、表达式或聚集函数（不需要在每个select中以相同的次序列出输出的每个列）。union默认是去除了重复行，如果需要包含重复行，则用union all
（12） 可以先用联结（（left）join on）获取多个表的联结结果，之后再用where对联结后的所有结果进行筛选。
（13）可以用“group by 列名”对select结果按照某一列的值进行分组，select后面必须有分组“列名”的列，也可以使用聚集函数进行统计

select customer_id,count(visits.customer_id) as count_no_trans
-- select  visits.customer_id
from visits 
left join transactions 
on visits.visit_id = transactions.visit_id
where transactions.transaction_id is null
group by visits.customer_id;

（14）日期处理函数：DateDiff计算两个日期的天数之差。AddDate在日期上加时间
ADDDATE(date,INTERVAL expr unit)
--------参数说明--------
date:待操作的日期
expr:数字
unit:指定添加时间的单位

unit可能存在的参数值
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR

DATE_ADD(date,INTERVAL expr type)
SELECT DATE_add(NOW(),INTERVAL -7 DAY);//获取7天前的日期
SELECT DATE_add(NOW(),INTERVAL  7 DAY);//获取7天后的日期DATE_SUB(date, INTERVAL expr type)
SELECT DATE_SUB(NOW(),INTERVAL -7 DAY);//获取7天后的日期
SELECT DATE_SUB(NOW(),INTERVAL  7 DAY);//获取7天前的日期
select activity_date as day, count(distinct Activity.user_id) as active_users 
from Activity 
where Activity.activity_date between date_sub( '2019-07-27',INTERVAL 29 DAY) and '2019-07-27' 
group by activity_date
select a.id from weather a join weather b on (a.recorddate = adddate(b.recorddate,INTERVAL 1 day))
where a.temperature > b.temperature

select w1.id
from weather as w1 join weather as w2
on w1.temperature > w2.temperature 
where DateDiff(w1.recordDate,w2.recordDate) =1;

（15）计算字段部分可以用if函数 if(判断，判断为true的赋值，判断为false的赋值)。

SELECT machine_id, ROUND(avg( if(activity_type = 'end' ,timestamp, -timestamp)*2), 3) AS processing_time
FROM activity
GROUP BY machine_id

也可以用CASE WHEN关键字表示条件分支判断

SELECT id, CASE statusWHEN 'active' THEN '已激活'WHEN 'inactive' THEN '未激活'ELSE '未知状态'END AS 'status_label'
FROM users;

SELECT machine_id, ROUND(avg((CASE WHEN activity_type = 'end' THEN timestamp ELSE -timestamp END))*2, 3) AS processing_time
FROM activity
GROUP BY machine_id

（16）Cross join：就是将两个表进行笛卡尔积运算，没有联结条件。ifnull（判断字段，为true的值）

Select Students.student_id,Students.student_name,Subjects.subject_name,ifnull(attended_exams,0) as attended_exams
from Students
Cross join Subjects
left join (select student_id,subject_name,count(*) as attended_exams from  Examinations group by student_id,subject_name
) as grouped
on Students.student_id = grouped.student_id and Subjects.subject_name = grouped.subject_name
order byStudents.student_id,Subjects.subject_name

（17） 注意外联结可能表面上失效：使用left join或者right join后，会显示出侧重一边的表的所有行，on条件筛选中没有匹配上的行也会被显示出（其缺失值为null）。但是如果left join on后面还跟了where语句，可能就会不小心把之前on不匹配补上的行筛除掉，这样就会导致希望显示某一个表的所有行失败。

select p.product_id,IFNUll(round(sum(p.price * u.units)/sum(u.units),2) , 0) average_price
from Prices as p 
left  join UnitsSold as u 
on u.product_id = p.product_id and Date(u.purchase_date) between p.start_date and p.end_date
group by p.product_id

select p.product_id,round(sum(p.price * u.units)/sum(u.units),2)average_price
from UnitsSold as u 
right join Prices as p on u.product_id = p.product_id
where Date(u.purchase_date) between p.start_date and p.end_date
group by p.product_id

（18）select子查询可以放在select后面的计算字段中，也可以放在from后面将查询结果变成一个新表，还可以放在where的条件中

select r.contest_id, round(count(*)*100.0/(select count(*) from Users),2) percentage
from Register r
left join Users u
on r.user_id = u.user_id
group by r.contest_id
order by percentage desc, r.contest_id 

SELECTproduct_id,IFNULL(Round(SUM(sales) / SUM(units), 2), 0) AS average_price
FROM (SELECTPrices.product_id AS product_id,Prices.price * UnitsSold.units AS sales,UnitsSold.units AS unitsFROM Prices LEFT JOIN UnitsSold ON Prices.product_id = UnitsSold.product_idAND (UnitsSold.purchase_date BETWEEN Prices.start_date AND Prices.end_date)
) T
GROUP BY product_id

select customer_id, count(customer_id) as count_no_trans
from visits
where visit_id not in  
(select distinct visit_id from transactions )
group by customer_id;

（19）select后面的计算字段可以实现复杂的计算功能，结合使用常见的聚合函数和计算函数

select query_name, round(sum(rating/position)/count(*),2) quality, 
round(sum(if(rating<3,1,0))*100.0/count(*),2) poor_query_percentage 
from Queries 
where query_name is not null
group by query_name

（20）想分组后再进行复杂的过滤逻辑，可以放在having中

select customer_id 
from Customer
group by customer_id
having count(distinct product_key) = (select count(*) from product)

（21）巧用自联结表：当求出的条件与单个表自身有关，但是where又无法解决的时候，可以考虑自联结。

求出连续出现的数字
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| num         | varchar |
+-------------+---------+
在 SQL 中，id 是该表的主键。
id 是一个自增列。
找出所有至少连续出现三次的数字。select distinct l1.num as ConsecutiveNums
from logs l1,logs l2,logs l3
where l1.num = l2.num and l1.id = l2.id - 1 
and l1.num = l3.num and l1.id = l3.id -2

（22）group by分组后的注意事项：group by 后的字段可以出现在select后面，也可以不出现。当使用group by后，如果没有对每个分组进行特殊处理，那么对应显示的字段就会全部显示。如果在显示字段中有用到聚集函数，那么每个分组将只有一条记录，注意！！！！同时，对于每个分组中没有使用聚集函数的字段，则会自动显示单个分组中第一条记录的该字段值，与聚集函数获取的其他字段记录无关！！！！！如下：
在这里插入图片描述

 select customer_id,delivery_id, min(order_date)from deliverygroup by customer_id

下面结果中，用户id为3、日期最小的结果对应的delivery_id应该为5，但却打印出为4。
在这里插入图片描述
（23）where条件匹配中可以进行多个字段联合匹配，只有同时满足联合的多个字段条件（ where (customer_id,order_date) ），对应的记录才匹配成功

select round(sum(if(order_date = customer_pref_delivery_date,1,0))*100/count(*),2) as immediate_percentage
from delivery 
where (customer_id,order_date) in (select customer_id , min(order_date) from delivery group by customer_id)

select round(count(*) / (select count(distinct player_id) from activity), 2) as fraction
from activity
where (player_id, date_sub(event_date, interval 1 day)) in (select player_id, min(event_date)from activitygroup by player_id
)

（24）count（）聚集函数只会记录数据条数，只有数据为null才不会计数，如果是0或1，则依然会计数。如下having分组筛选时，sale_date between ‘2019-01-01’ and '2019-03-31’最终得到的是true（1） or false（0），不是null，因此无论是满足该条件还是不满足该条件，都会被计数，因此需要加上or null.

select p.product_id,p.product_name
from Product p
where product_id in (select product_id from sales group by product_id having count(sale_date between '2019-01-01' and '2019-03-31' or null) = count(product_id))

也可以用sum代替

select p.product_id,p.product_name
from Product p
where product_id in (select product_id from sales group by product_id having sum(sale_date between '2019-01-01' and '2019-03-31' ) = count(product_id))

（25）.group by

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.rhkb.cn/news/450185.html

如若内容造成侵权/违法违规/事实不符，请联系长河编程网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

终端威胁检测与响应 (EDR) 技术研究

终端威胁检测与响应 (EDR) 技术研究

终端安全面临的挑战 从安全日常管理实践出发&#xff0c;终端安全的常见风险点是钓鱼攻击。因终端业务场景复杂&#xff0c;涉及即时通信软件、邮件等方式&#xff0c;如设置较严苛的拦截规则&#xff0c;则会造成较大的业务影响&#xff0c;且部分钓鱼通道为加密通道&#xf…
阅读更多...
C_数据结构（栈） —— 栈的初始化、栈的销毁、入栈、出栈、bool类型判断栈是否为空、取栈顶元素、获取栈中有效元素个数

C_数据结构（栈） —— 栈的初始化、栈的销毁、入栈、出栈、bool类型判断栈是否为空、取栈顶元素、获取栈中有效元素个数

目录 一、栈 1、概念与结构 二、栈的实现 1、定义栈的结构 2、栈的初始化 3、栈的销毁 4、入栈 5、出栈 6、bool类型判断栈是否为空 7、取栈顶元素 8、获取栈中有效元素个数 三、完整实现栈的三个文件 Stack.h Stack.c test.c 一、栈 1、概念与结构 栈&#x…
阅读更多...
K8s环境下使用sidecar模式对EMQX的exhook.proto 进行流量代理

K8s环境下使用sidecar模式对EMQX的exhook.proto 进行流量代理

背景 在使用emqx作为mqtt时需要我们需要拦截client的各种行为&#xff0c;如连接&#xff0c;发送消息&#xff0c;认证等。除了使用emqx自带的插件机制。我们也可以用多语言-钩子扩展来实现这个功能&#xff0c;但是目前emqx仅仅支持单个grpc服务端的设置&#xff0c;所以会有…
阅读更多...
论文阅读-U3M(2)

论文阅读-U3M(2)

HOW MUCH POSITION INFORMATION DO CONVOLUTIONAL NEURAL NETWORKS ENCODE? 文章目录 HOW MUCH POSITION INFORMATION DO CONVOLUTIONAL NEURAL NETWORKS ENCODE?前言一、位置编码网络&#xff08;PosENet&#xff09;二、训练数据三、实验3.1 位置信息的存在性3.2 分析PosEN…
阅读更多...
多机编队—(3)Fast_planner无人机模型替换为Turtlebot3模型实现无地图的轨迹规划

多机编队—(3)Fast_planner无人机模型替换为Turtlebot3模型实现无地图的轨迹规划

文章目录 前言一、模型替换二、Riz可视化三、坐标变换四、轨迹规划最后 前言 前段时间已经成功将Fast_planner配置到ubuntu机器人中&#xff0c;这段时间将Fast_planner中的无人机模型替换为了Turtlebot3_waffle模型&#xff0c;机器人识别到环境中的三维障碍物信息&#xff0…
阅读更多...
X（twitter）推特的广告类型有哪些？怎么选择？

X（twitter）推特的广告类型有哪些？怎么选择？

X&#xff08;twitter&#xff09;推特是全球最热门的几大社交媒体平台之一&#xff0c;也是很多电商卖家进行宣传推广工作的阵地之一。在营销过程中不可避免地需要借助平台广告&#xff0c;因此了解其广告类型和适配场景也十分重要。 一、广告类型及选择 1.轮播广告 可滑动的…
阅读更多...
谷歌浏览器办公必备扩展推荐有哪些

谷歌浏览器办公必备扩展推荐有哪些

在现代办公环境中&#xff0c;谷歌浏览器凭借其强大的功能和丰富的扩展生态&#xff0c;成为了许多人日常工作中不可或缺的工具。为了进一步提升办公效率&#xff0c;本文将为您推荐几款实用的谷歌浏览器扩展&#xff0c;并解答在使用过程中可能遇到的一些常见问题。&#xff0…
阅读更多...
基于SpringBoot+Vue+Uniapp家具购物小程序的设计与实现

基于SpringBoot+Vue+Uniapp家具购物小程序的设计与实现

详细视频演示 请联系我获取更详细的演示视频 项目运行截图 技术框架 后端采用SpringBoot框架 Spring Boot 是一个用于快速开发基于 Spring 框架的应用程序的开源框架。它采用约定大于配置的理念&#xff0c;提供了一套默认的配置&#xff0c;让开发者可以更专注于业务逻辑而…
阅读更多...
【原创】java+springboot+mysql在线课程学习网设计与实现

【原创】java+springboot+mysql在线课程学习网设计与实现

个人主页&#xff1a;程序猿小小杨 个人简介&#xff1a;从事开发多年&#xff0c;Java、Php、Python、前端开发均有涉猎 博客内容&#xff1a;Java项目实战、项目演示、技术分享 文末有作者名片&#xff0c;希望和大家一起共同进步&#xff0c;你只管努力&#xff0c;剩下的交…
阅读更多...
【xilinx-versal】【Petalinux】添加TMP75温度传感器Linux驱动

【xilinx-versal】【Petalinux】添加TMP75温度传感器Linux驱动

Xilinx versal添加TMP75温度传感器Linux驱动 I2C总线的内核配置打开Cadence I2C 控制器配置xilinx I2C配置（不使用）添加设备树总结I2C总线的内核配置 TMP75挂载第一个i2c总线上，地址是0x48。 petalinux-config -c kernel打开内核配置界面。 打开Cadence I2C 控制器配置 │…
阅读更多...
MySQL中常见函数

MySQL中常见函数

1&#xff0c;日期类函数 1&#xff0c;获取年月日 关键字&#xff1a;current_date(); 2,获取时间 关键字&#xff1a;current_time(); 3,获取时间戳 关键字&#xff1a;current_timestamp(); 注意&#xff0c;MySQL的时间戳显示是以时间的方式显示&#xff0c;所以可以看…
阅读更多...
调查显示软件供应链攻击增加

调查显示软件供应链攻击增加

OpenText 发布了《2024 年全球勒索软件调查》&#xff0c;强调了网络攻击的重要趋势&#xff0c;特别是在软件供应链中&#xff0c;以及生成式人工智能在网络钓鱼诈骗中的使用日益增多。 尽管各国政府努力加强网络安全措施&#xff0c;但调查显示&#xff0c;仍有相当一部分企…
阅读更多...
Servlet[springmvc]的Servlet.init()引发异常

Servlet[springmvc]的Servlet.init()引发异常

报错&#xff1a; 原因之一&#xff1a; web.xml配置文件中监听器导入依赖项错误
阅读更多...
Node.js 中的 WebSocket 底层实现

Node.js 中的 WebSocket 底层实现

WebSockets 是一种网络通信协议&#xff0c;可实现双向客户端-服务器通信。 WebSockets 通常用于需要即时更新的应用程序&#xff0c;使用 HTTP 之上的持久双工通道来支持实时交互&#xff0c;而无需持续进行连接协商。服务器推送是 WebSockets 的众多常见用例之一。 本文首先…
阅读更多...
接口测试 —— 如何测试加密接口？

接口测试 —— 如何测试加密接口？

接口加密是指在网络传输过程中&#xff0c;将数据进行加密&#xff0c;以保护数据的安全性。接口加密可以采用多种加密算法&#xff0c;如AES、DES、RSA等。测试接口加密的目的是验证接口加密算法的正确性和安全性。以下是一些详细的测试方法和注意事项&#xff1a; 接口加密字…
阅读更多...
centos7.9调整磁盘分区大小

centos7.9调整磁盘分区大小

在安装centos7.9时我们一般采用默认分区设置&#xff0c;使用LVM来管理磁盘空间&#xff0c;根分区只有50GB&#xff0c;其余的所有可用空间都分配在/home分区下。可是centos7中大多数的应用软件都是安装在根分区的&#xff0c;在使用过程中经常会出现明明系统还有很大的磁盘空…
阅读更多...
Leetcode—1114. 按序打印【简单】（多线程）

Leetcode—1114. 按序打印【简单】（多线程）

2024每日刷题&#xff08;179&#xff09; Leetcode—1114. 按序打印 C实现代码 class Foo { public:Foo() {firstMutex.lock();secondMutex.lock();}void first(function<void()> printFirst) {// printFirst() outputs "first". Do not change or remove t…
阅读更多...
【后端开发】自动化部署、服务管理、问题排查工具（cicd流水线，k8s集群，ELK日志）

【后端开发】自动化部署、服务管理、问题排查工具（cicd流水线，k8s集群，ELK日志）

【后端开发】自动化部署、服务管理、问题排查工具&#xff08;cicd流水线&#xff0c;k8s集群&#xff0c;ELK日志&#xff09; 文章目录 1、Devops与CICD流水线(TeamCity, Jenkins&#xff0c;GitHub Actions)2、Kubernetes 集群的管理和操作&#xff08;对比Portainer&#x…
阅读更多...
排序算法上——插入，希尔，选择，堆排序

排序算法上——插入，希尔，选择，堆排序

前言&#xff1a; 常见排序方法如下&#xff1a; 本篇将介绍4种排序方法&#xff0c;分别为插入排序&#xff0c;希尔排序&#xff0c;选择排序&#xff0c;堆排序&#xff0c;并分别举例与讲解。 一. 插入排序 1.1 含义与动图分析 插入排序的思想是在有序区间的下一个位置…
阅读更多...
设计模式---责任链模式快速demo

设计模式---责任链模式快速demo

Handler&#xff08;处理者&#xff09;&#xff1a; 定义一个处理请求的接口。通常包括一个处理请求的方法。它可以是抽象类或接口&#xff0c;也可以是具体类&#xff0c;具体类中包含了对请求的处理逻辑。处理者通常包含一个指向下一个处理者的引用。ConcreteHandler&#x…
阅读更多...
最新文章
推荐文章

Copyright @ 2022~2023