目录
一、常见用法
1.1 创建类
1.创建数据库
create database database-namek
2.删除数据库
drop database dbname
3.修改数据库的名称
sp_renamedb 'old_name', 'new_name'
4.创建新表
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)
A：create table tab_new like tab_old (使用旧表创建新表)
B：create table tab_new as select col1,col2… from tab_old definition only
5.删除表
drop table tabname
6.增加列(列增加后不能删除)
Alter table tabname add column col type
7.添加主键：
Alter table tabname add primary key(col)
8.删除主键：
Alter table tabname drop primary key(col)
9.创建索引(索引创建不能修改，只能删除)
create [unique] index idxname on tabname(col….)
10.删除索引：
drop index idxname
11.创建视图
create view viewname as select statement
12.删除视图：
drop view viewname
1.2 查询处理类
1.2.1 常见汇总
1.选择：select * from table1 where 范围
2.插入：insert into table1(field1,field2) values(value1,value2)
3.删除：delete from table1 where 范围
4.更新：update table1 set field1=value1 where 范围
5.查找：select * from table1 where field1 like ’%value1%’ —like的语法很精妙，查资料!
6.排序：select * from table1 order by field1,field2 [desc] -先排序，后取
7.总数：select count as totalcount from table1
8.求和：select sum(field1) as sumvalue from table1
9.平均：select avg(field1) as avgvalue from table1
10.最大：select max(field1) as maxvalue from table1
11.最小：select min(field1) as minvalue from table1
Tips:
（1）查询时from后最多可以跟256张表或视图，
（2）where用来过滤指定条件，包括=，>，<，>=，<=，<>，between，like，in，可以包含一个或多个and or
（3）like用于在where条件中搜索指定列样式的记录，%：用于表示0个，1个或者多个字符。_：用于表示1个字符，where CustomerName like '_r%';
（4）in：WHERE column_name IN (value1, value2, ...)
（5）not:where City not in ('Paris', 'London');
（6）Between选择给定范围内的值:where Price between 10 and 20 and CategoryID in (1,2,3);1.2.2 连接
内连接(INNER) JOIN：返回两个表中具有匹配值的记录
左连接LEFT (OUTER) JOIN：返回左表中的所有记录以及右表中匹配的记录
右连接RIGHT (OUTER) JOIN：返回右表中的所有记录以及左表中匹配的记录
全连接FULL (OUTER) JOIN：当左表或右表中有匹配项时返回所有记录
inner join和join是相同的
select Orders.orderID, Customers.CustomerName, Shippers.ShipperName
from ((Orders
inner join Customers on Orders.CustomerID = Customers.CustomerID)
inner join Shippers on Orders.ShipperID = Shippers.ShipperID);1.2.3 最大值最小值
MIN() MAX()
Min()返回选择列的最小值，max()返回选择列的最大值
# 从Products表中选择最小的价格命名为SmallestPrice
select min(Price) as SmallestPrice from Products;1.2.4 计算行数，求和，平均
（1）COUNT()
count()用来返回匹配指定条件的行数，如果指定column_name而不是用*，NULL值不会被计数
（2）Sum()函数用来返回一个数值列的总和
（3）AVG()
Avg()函数用来返回一个数值列的平均值，NULL值会被忽略
# 从Products表选择Price大于20的价格种类数
select count(distinct Price) as [number of price] from Products where Price > 20;
# 从OrderDetails表中选择产品id为1的总金额数，单价为10
select sum(Quantity * 10) from OrderDetails where ProductID = 1;
# 从Products中选择价格大于平均值的记录
select * from Products where Price > (select avg(Price) from Products);1.2.5 排序
order by用于排序结果集，默认升序，desc降序
# 按country升序排列，CustomerName降序排列
select * from Customers order by Country ASC, CustomerName DESC;1.2.6 按行汇总
GROUP BY，group by语句将具有相同值的行分组到汇总行中，
select count(CustomerID), Country from Customers group by Country order by count(CustomerID) desc;题目：
答案：
# Write your MySQL query statement below select -- * p.product_id, p.product_name from Product p left join Sales s on p.product_id = s.product_id group by p.product_id having -- or null：表达的不在范围内就转换为null count(sale_date between '2019-01-01' and '2019-03-31' or null) = count(*)
mysql之group by和having用法详解_Mysql_脚本之家
1.2.7 插入
insert into用来在表格里插入新记录
# 指定列插入值
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
# 不指定列需要确保值和表中列完整对应
INSERT INTO table_name
VALUES (value1, value2, value3, ...);1.2.8 optional/NULL/not NULL
字段可以设置为optional，这样插入和更新记录时可以不添加该字段的值，会存储为NULL Value
# 选择空值的记录
SELECT column_names
FROM table_name
WHERE column_name IS NULL;
# 选择非空的记录
SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;1.2.9 更新
update用来更新表格中已经存在的记录，如果漏掉where会更新所有记录
# 更新客户1的ContactName和City
update Customers
set ContactName = 'Alfred Schmidt', City = 'Frankfurt'
where CustomerID = 1;1.2.10 删除
Delete用来删除表格中已经存在的记录，注意where条件，如果漏掉where会删除所有记录
# 删除表格中指定条件的记录（只删除记录不删除表格）
DELETE FROM table_name WHERE condition;
# 完全删除表格
DELETE FROM table_name;
# 删除Alfreds Futterkiste的记录
delete from Customers
where CustomerName='Alfreds Futterkiste';1.2.11 个数限制
LIMIT:LIMIT用来指定返回记录的个数，在SQL Server中语法是SELECT TOP，在MySQL是LIMIT，可以用offset
# 选择CustomerName倒序前三名
select * from Customers
order by CustomerName desc
limit 3;1.2.12 临时名称
As：As用于为表或表中的列提供临时名称
# 作用于列
SELECT column_name AS alias_name
FROM table_name;
# 作用于表
SELECT column_name(s)
FROM table_name AS alias_name;
# 选择ProductName列并且修改为my product
select ProductName as [my product]
from Products;1.2.13 case判断
CASE 表达式的作用相当于进行判断的函数，用来判断各个元素（= 行）是否属于满足了某种条件的集合。这样的函数我们称为特征函数（characteristic function），或者从定义了集合的角度来将它称为定义函数
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
ELSE resultN
END
-- 这里，conditionN 是需要被评估的条件，如果 conditionN 为真（TRUE），
-- 则 CASE 表达式返回 ==
-- resultN。如果没有任何条件为真，则返回 ELSE 子句中的 resultN
--（如果提供了 ELSE 子句）。
CASE expression
WHEN value1 THEN result1
WHEN value2 THEN result2
...
ELSE resultN
END
-- 这里，expression 是被比较的表达式，valueN 是与 expression 进行比较的值，
-- 如果匹配，则返回 resultN。如果没有任何 valueN 匹配 expression，则返回 ELSE
-- 子句中的 resultN（如果提供了 ELSE 子句）。题目：
答案：
select machine_id, round(sum(case when activity_type = 'end' then timestamp else -timestamp end) / count(distinct process_id),3) as processing_time from Activity group by machine_id
1.2.14 日期处理
基本语法
DATE_FORMAT(date, format) |
date：要格式化的日期或时间值。format：指定输出格式的字符串。
举例：
- SELECT DATE_FORMAT(trans_date, '%Y-%m-%d') AS formatted_date FROM Transactions; 返回
trans_date字段的日期值，格式为 YYYY-MM-DD。- SELECT DATE_FORMAT(trans_date, '%m/%d/%Y') AS formatted_date FROM Transactions; 这将返回 trans_date 字段的日期值，格式为 MM/DD/YYYY。
1.2.15 IF
基本用法：
IF(expression, value_if_true, value_if_false)
- expression：要评估的表达式。
- value_if_true：如果expression为真（非零或非NULL），则返回此值。
- value_if_false：如果expression为假（零或NULL），则返回此值。
举例：
- SELECT employee_id, employee_name, IF(salary > 50000, 'High', 'Low') AS salary_level FROM employees;根据他们的薪水水平（高于或低于50,000）分类的薪水级别。
1.2.16 四舍五入，保留小数点后几位
基本用法：
ROUND(column_name, decimals)
- column_name：要四舍五入的列名或数值表达式。
- decimals：可选参数，指定要保留的小数位数。如果省略此参数，则默认四舍五入到整数。如果 decimals 为负数，则表示在小数点左侧进行四舍五入（即四舍五入到十位、百位等）。
举例：
- SELECT ROUND(123.567); -- 结果：124
- SELECT ROUND(123.4567, 2); -- 结果：123.46
1.2.16 正则表达式
用法举例：
题目：
答案：
select user_id, name, mail from users where mail regexp '^[a-zA-Z][a-zA-Z0-9-._]*\\@leetcode\\.com$'
1.2.17 字符串处理
题目：
答案：
# Write your MySQL query statement below # mysql 字符串函数各种复习 -- CONCAT(str1, str2)：字符连接函数 -- UPPER(str)：将字符串改为大写字母 -- LOWER(str)：将字符串改为小写字母 -- LENGTH(str)：判定字符串长度 -- SUBSTRING(str, a, b):提取字段中的一段，从字符串str的第a位开始提取，提取b个字符 -- LEFT(str, n)：提取字符串最左边的n个字符 -- RIGHT(str, n)：提取字符串最右边的n个字符 SELECT a.user_id, CONCAT(UPPER(SUBSTRING(a.name, 1, 1)), LOWER(SUBSTRING(a.name, 2))) AS NAME FROM Activity a ORDER BY a.user_id;
1.2.18 窗口函数
LEAD函数用于获取当前行之后的第N行（默认N为1）的数据。
获取下一行的数据：LEAD函数可以让你获取当前行之后的下一行的数据，这在需要比较连续行数据时非常有用。
指定偏移量N：除了默认的N=1外，你可以指定N的值，例如LEAD(column, N)将获取当前行后第N行的column列数据。
处理最后几行的情况：LEAD函数对于最后几行的处理方式取决于如何定义边界条件。有些实现可以通过设置默认值或指定边界行为来处理末尾行的情况。
示例（SQL语法）：
SELECT id, value, LEAD(value) OVER (ORDER BY id) AS next_value FROM table_name;LEAD函数用于获取按照id排序的下一行的value值。
LAG函数与LEAD相反，它用于获取当前行之前的第N行（N默认为1）的数据。
获取前一行的数据：LAG函数允许你获取当前行之前的上一行数据，有时这对于计算差异或趋势分析很有帮助。
指定偏移量N：与LEAD类似，你可以指定N的值来获取当前行前第N行的数据。
处理第一行的情况：LAG函数同样需要考虑边界条件，通常可以通过设置默认值或者指定边界行为来处理第一行的情况。
示例（SQL语法）：
SELECT id, value, LAG(value) OVER (ORDER BY id) AS prev_value FROM table_name;用LAG函数来获取按照id排序的前一行的value值。
答案：
SELECT id,
case when id%2=1
then ifnull(lead(student,1)over(),student)
else lag(student,1)over() end
as student
FROM Seat二、场景题（来源牛客/力扣）
2.1 每个商品的销售总额
WITH
ProductSales AS (
SELECT
p.name AS product_name,
SUM(o.quantity) AS total_sales,
p.category
FROM
products p
JOIN orders o ON p.product_id = o.product_id
GROUP BY
p.name,
p.category
)
SELECT
product_name,
total_sales,
RANK() OVER (
PARTITION BY
category
ORDER BY
total_sales DESC
) AS category_rank
FROM
ProductSales
ORDER BY
category,
category_rank;2.2 每月交易 I
-- 使用DATE_FORMAT函数将trans_date字段格式化为年-月格式，并别名为month
-- 使用IF函数判断state字段是否为'approved'，是则返回1（用于计数），否则返回NULL（不计入总数）
-- SUM计算每个分组中所有交易的总金额
-- count计算每个分组中的交易总数
select date_format(trans_date, "%Y-%m") as month,country,
count(*) as trans_count,
count(if(state = 'approved', 1, NULL)) as approved_count,
sum(amount) as trans_total_amount,
sum(if(state = 'approved', amount, 0)) as approved_total_amount
from Transactions
group by month,country
扫一扫
1995
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
