SQL常见用法总结(侧重查询)

目录

一、常见用法

1.1 创建类

1.2 查询

1.2.1 常见汇总

1.2.2 连接

1.2.3 最大值最小值

1.2.4 计算行数,求和,平均

1.2.5 排序

1.2.6 按行汇总

1.2.7 插入

1.2.8 optional/NULL/not NULL

1.2.9 更新

1.2.10 删除 

1.2.11 个数限制

1.2.12 临时名称

1.2.13 case判断

二、场景题(来源牛客/力扣)


一、常见用法

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;

题目:

. - 力扣(LeetCode)

答案:

# 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 子句)。

题目:

. - 力扣(LeetCode)

答案:

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:指定输出格式的字符串。

举例:

  1. SELECT DATE_FORMAT(trans_date, '%Y-%m-%d') AS formatted_date   FROM Transactions; 返回 trans_date 字段的日期值,格式为 YYYY-MM-DD。
  2. 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$'

. - 力扣(LeetCode)

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;

. - 力扣(LeetCode)

1.2.18 窗口函数

LEAD函数用于获取当前行之后的第N行(默认N为1)的数据。

  1. 获取下一行的数据:LEAD函数可以让你获取当前行之后的下一行的数据,这在需要比较连续行数据时非常有用。

  2. 指定偏移量N:除了默认的N=1外,你可以指定N的值,例如LEAD(column, N)将获取当前行后第N行的column列数据。

  3. 处理最后几行的情况: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)的数据。

  1. 获取前一行的数据:LAG函数允许你获取当前行之前的上一行数据,有时这对于计算差异或趋势分析很有帮助。

  2. 指定偏移量N:与LEAD类似,你可以指定N的值来获取当前行前第N行的数据。

  3. 处理第一行的情况:LAG函数同样需要考虑边界条件,通常可以通过设置默认值或者指定边界行为来处理第一行的情况。

示例(SQL语法):

SELECT id, value, LAG(value) OVER (ORDER BY id) AS prev_value FROM table_name;

用LAG函数来获取按照id排序的前一行的value值。

题目:. - 力扣(LeetCode)

答案:

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

. - 力扣(LeetCode)

 参考:常用的SQL语句大全总结-CSDN博客

SQL基础语法总结(查询)_sql 查询语法总结-CSDN博客

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

打赏作者

Rebecca.Yan

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

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

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

打赏作者

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

抵扣说明:

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

余额充值