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

🕗 发布于 2024-10-12 04:39 mysql 数据库

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 status
        WHEN '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 by
    Students.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

SELECT
    product_id,
    IFNULL(Round(SUM(sales) / SUM(units), 2), 0) AS average_price
FROM (
    SELECT
        Prices.product_id AS product_id,
        Prices.price * UnitsSold.units AS sales,
        UnitsSold.units AS units
    FROM Prices 
    LEFT JOIN UnitsSold ON Prices.product_id = UnitsSold.product_id
    AND (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 delivery
    group 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 activity
    group by player_id
)

原文地址：https://blog.csdn.net/weixin_44248258/article/details/140390955

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

上一篇：Android JNI调用.c文件
下一篇：Python创建多个线程分别启动http、WebSocket服务

开发指南072-图片热点
/处理跳转： area.getAttribute('href');平台支持使用图像导航界面，例如展示如下一张图，用户点击对应位置触发对应动作。热点数据通过后台接口获取（注意处理权限，没有权限的热点不生
阅读更多2024-10-13
使用机器学习边缘设备的快速目标检测
这项机器学习研究探讨了一种低成本的边缘设备，该设备与具有计算机视觉功能的嵌入式系统集成，以提高目标检测和分类的推理时间和精度。研究的主要目标是减少推理时间并降低功耗，以支持一个竞技型类人机器人的嵌入式
阅读更多2024-10-13
【Windows】【DevOps】Windows Server 2022 安装ansible，基于powershell实现远程自动化运维部署入门到放弃！
文件URL：https://www.python.org/ftp/python/3.13.0/python-3.13.0-amd64.exe。直接拿linux主机测试ansible连接windows
阅读更多2024-10-13
C# 中循环的应用说明
一循环的概念说明二、循环类型三、循环控制语句四、无限循环
阅读更多2024-10-13
Linux `vmstat` 命令详解
vmstat（Virtual Memory Statistics）是 Linux 系统中的一个监控工具，用于报告系统的虚拟内存、进程、CPU 活动等信息。它能帮助用户了解系统的整体性能状况，尤其是内存
阅读更多2024-10-13
Linux下多任务编程（网络编程2）
本文介绍解决accpet和recv相互阻塞的问题，可以用多线程并发外也可以用epoll I/O多路复用的方式解决。
阅读更多2024-10-13
[单master节点k8s部署]37.微服务（一)springCloud 微服务
微服务架构的一个重要特点是，它与开发中使用的具体或无关。每个微服务都可以使用最适合其功能需求的语言或技术来实现。例如，一个微服务可以用Java编写，另一个微服务可以用Python、Go、Node.js
阅读更多2024-10-13
Zynq(3)使用外设MIO/EMIO
使用MIO/EMIO实现流水灯，着重介绍Zynq IP核的配置，解读vitis中的c语言程序，介绍MIO与EMIO的区别。
阅读更多2024-10-13
笔试算法总结
思路很简单，但是当时做题提交的时候，通过率总是18%。不知道为啥，后面我改成了Long类型，然后就通过了全部用例。（易错1：第一次提交没考虑0的情况）使用 StringBuilder 模拟栈的行为，通
阅读更多2024-10-13
快速学习一个算法，Transformer模型架构
它的主要思想是在同一时间通过多个独立的注意力头（Attention Head）来关注序列中不同部分的信息，然后将这些信息综合起来，生成更丰富的表示。自注意力机制的目的是对输入序列中的每个元素计算一个输
阅读更多2024-10-13

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

目录

1. 常用sql

2. 刷题知识点

相关文章