首页/资讯中心/详情

SQL知识点

SQL知识点
📅 发布时间：2026/7/18 22:28:59

数据库 (Database)

create database databasename;

功能：创建新数据库。
语法：

CREATEDATABASEdatabase_name;

示例：

CREATEDATABASEmy_shop;

use databasename

功能：选择要操作的数据库。
语法：

USEdatabase_name;

示例：

USEmy_shop;

drop database databasename

功能：删除数据库及其所有数据（谨慎使用！）。
语法：

DROPDATABASEdatabase_name;

示例：

DROPDATABASEold_backup;

表 (Table)

create

功能：创建新表。
语法：

CREATETABLEtable_name(column1 datatype[constraints],column2 datatype[constraints],...);

常用约束：PRIMARY KEY,NOT NULL,UNIQUE,DEFAULT value,FOREIGN KEY
示例：

CREATETABLEusers(idINTPRIMARYKEYAUTO_INCREMENT,usernameVARCHAR(50)NOTNULLUNIQUE,emailVARCHAR(100),created_atTIMESTAMPDEFAULTCURRENT_TIMESTAMP);

delete from

功能：从表中删除记录。
语法：

DELETEFROMtable_nameWHEREcondition;

注意：如果不加WHERE子句，将删除表中所有数据！
示例：

DELETEFROMusersWHEREid=5;

select

功能：从表中查询数据（基础形式）。
语法：

SELECTcolumn1,column2,...FROMtable_name;

示例：

SELECTusername,emailFROMusers;

insert into

功能：向表中插入新记录。
语法：

-- 指定列名插入INSERTINTOtable_name(column1,column2,...)VALUES(value1,value2,...);-- 为所有列插入（需按表结构顺序）INSERTINTOtable_nameVALUES(value1,value2,...);

示例：

INSERTINTOusers(username,email)VALUES('john_doe','john@example.com');

update tablename

功能：更新表中已有的记录。
语法：

UPDATEtable_nameSETcolumn1=value1,column2=value2,...WHEREcondition;

注意：务必使用WHERE子句限定要更新的行，否则会更新所有行！
示例：

UPDATEusersSETemail='new_email@example.com'WHEREusername='john_doe';

SELECT 查询详解

功能：从数据库表中检索数据，是 SQL 中最核心和复杂的命令。

完整语法结构：

SELECT[DISTINCT]column1,column2,...FROMtable1[JOINtable2ONjoin_condition][WHEREcondition][GROUPBYcolumn1,column2,...][HAVINGgroup_condition][ORDERBYcolumn1[ASC|DESC],...][LIMITnumber][OFFSETnumber];

子句说明与示例：

  1. DISTINCT
    去除结果中的重复行。

    SELECTDISTINCTdepartmentFROMemployees;

  2. JOIN
    连接多个表。

    -- 内连接 (INNER JOIN)SELECTorders.id,customers.nameFROMordersINNERJOINcustomersONorders.customer_id=customers.id;-- 左连接 (LEFT JOIN)SELECTemployees.name,departments.dept_nameFROMemployeesLEFTJOINdepartmentsONemployees.dept_id=departments.id;

  3. WHERE
    过滤行。

    SELECT*FROMproductsWHEREprice>100ANDstock>0;

  4. GROUP BY
    按列分组，常与聚合函数 (COUNT,SUM,AVG,MAX,MIN) 一起使用。

    SELECTdepartment,COUNT(*)asemployee_count,AVG(salary)asavg_salaryFROMemployeesGROUPBYdepartment;

  5. HAVING
    过滤分组后的结果（WHERE用于分组前过滤行）。

    SELECTdepartment,COUNT(*)asemp_countFROMemployeesGROUPBYdepartmentHAVINGCOUNT(*)>5;

  6. ORDER BY
    对结果排序。

    SELECTname,scoreFROMstudentsORDERBYscoreDESC;

  7. LIMIT & OFFSET
    限制返回行数和实现分页。

    -- 获取前10条SELECT*FROMpostsORDERBYcreated_atDESCLIMIT10;-- 获取第11-20条（每页10条的第2页）SELECT*FROMpostsORDERBYcreated_atDESCLIMIT10OFFSET10;

select

窗口函数

功能：窗口函数（Window Function）允许在不减少行数的情况下，对一组相关的行（称为“窗口”）进行计算，为每一行返回一个值。它与GROUP BY聚合不同，不会将多行合并为一行。

基本语法：

SELECTcolumn1,column2,window_function(column3)OVER([PARTITIONBYpartition_column1,partition_column2,...][ORDERBYorder_column1[ASC|DESC],...][frame_clause])ASalias_nameFROMtable_name;

核心子句说明：
4.frame_clause: 定义窗口框架，即函数计算时具体考虑分区内的哪些行。常用ROWS BETWEEN ... AND ...或RANGE BETWEEN ... AND ...。
-语法：{ROWS | RANGE} BETWEEN frame_start AND frame_end
-frame_start和frame_end可选值：
-UNBOUNDED PRECEDING: 分区第一行。
-n PRECEDING: 当前行之前的第 n 行（ROWS）或值小于当前值 n 个单位内的行（RANGE）。
-CURRENT ROW: 当前行。
-n FOLLOWING: 当前行之后的第 n 行（ROWS）或值大于当前值 n 个单位内的行（RANGE）。
-UNBOUNDED FOLLOWING: 分区最后一行。
-常用简写：
-ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW(默认框架，可省略)：从分区开始到当前行。
-ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING：前后各两行，共五行（当前行及前后各两行）。
-ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING：从当前行到分区结束。
-ROWSvsRANGE：
-ROWS: 按物理行偏移。
-RANGE: 按逻辑值范围偏移（需配合ORDER BY，且排序列通常为数值或日期类型）。常用窗口函数类别与示例：

1. 排名函数

  • ROW_NUMBER(): 为分区内的每一行分配一个唯一的连续序号。
  • RANK(): 排名，相同值排名相同，但会留下“空位”。
  • DENSE_RANK(): 密集排名，相同值排名相同，且不留“空位”。
-- 按部门分区，按薪水降序排名SELECTname,department,salary,ROW_NUMBER()OVER(PARTITIONBYdepartmentORDERBYsalaryDESC)ASrow_num,RANK()OVER(PARTITIONBYdepartmentORDERBYsalaryDESC)ASrank,DENSE_RANK()OVER(PARTITIONBYdepartmentORDERBYsalaryDESC)ASdense_rankFROMemployees;

2. 聚合函数作为窗口函数

  • SUM(),AVG(),COUNT(),MIN(),MAX()
-- 计算每个员工的薪水在其部门内的占比SELECTname,department,salary,SUM(salary)OVER(PARTITIONBYdepartment)ASdept_total_salary,salary*100.0/SUM(salary)OVER(PARTITIONBYdepartment)ASsalary_percentageFROMemployees;

3. 取值函数

  • LAG(column, offset): 获取分区内当前行之前第offset行的值。
  • LEAD(column, offset): 获取分区内当前行之后第offset行的值。
  • FIRST_VALUE(column): 获取分区内第一行的值。
  • LAST_VALUE(column): 获取分区内最后一行的值（通常需要指定正确的窗口框架）。
-- 查看每位员工相对于上一位员工的薪水变化SELECTname,department,salary,LAG(salary,1)OVER(PARTITIONBYdepartmentORDERBYhire_date)ASprev_salary,salary-LAG(salary,1)OVER(PARTITIONBYdepartmentORDERBYhire_date)ASsalary_changeFROMemployees;

4. 分布函数

  • PERCENT_RANK(): 计算行的相对排名（百分比）。
  • CUME_DIST(): 计算行的累积分布。

窗口框架示例：

-- 计算截至当前行的累计薪水（默认框架：RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW）SELECTname,hire_date,salary,SUM(salary)OVER(ORDERBYhire_date)ASrunning_totalFROMemployees;-- 计算移动平均（最近3行，包括当前行和前2行）SELECTdate,revenue,AVG(revenue)OVER(ORDERBYdateROWSBETWEEN2PRECEDINGANDCURRENTROW)ASmoving_avg_3dayFROMdaily_sales;

注意事项：

  • 窗口函数在SELECT子句和ORDER BY子句中可用，但不能在WHERE、GROUP BY或HAVING子句中使用。
  • 性能：在大数据集上，复杂的窗口函数可能影响查询性能，需合理使用索引和分区。

count

功能：统计行数或非空值的数量。
语法：

-- 统计所有行数（包括 NULL 值）SELECTCOUNT(*)FROMtable_name;-- 统计指定列中非 NULL 值的数量SELECTCOUNT(column_name)FROMtable_name;-- 结合 DISTINCT 统计唯一值的数量SELECTCOUNT(DISTINCTcolumn_name)FROMtable_name;

示例：

-- 统计员工总数SELECTCOUNT(*)AStotal_employeesFROMemployees;-- 统计有邮箱地址的员工数量SELECTCOUNT(email)ASemployees_with_emailFROMemployees;-- 统计不重复的部门数量SELECTCOUNT(DISTINCTdepartment)ASunique_departmentsFROMemployees;

case when then else end

功能：实现条件逻辑，根据条件返回不同的值。
语法：

SELECTcolumn1,CASEWHENcondition1THENresult1WHENcondition2THENresult2...ELSEdefault_resultENDASnew_column_nameFROMtable_name;

示例：

-- 根据分数划分等级SELECTstudent_name,score,CASEWHENscore>=90THEN'A'WHENscore>=80THEN'B'WHENscore>=70THEN'C'WHENscore>=60THEN'D'ELSE'F'ENDASgradeFROMexam_results;-- 简化版 CASE 表达式（等值判断）SELECTproduct_name,CASEcategory_idWHEN1THEN'Electronics'WHEN2THEN'Clothing'WHEN3THEN'Books'ELSE'Other'ENDAScategory_nameFROMproducts;

if NULLIF

功能：IF函数用于条件判断，NULLIF函数用于比较两个表达式，相等时返回 NULL。
语法：

-- IF 函数（MySQL）SELECTIF(condition,value_if_true,value_if_false)FROMtable_name;-- NULLIF 函数（标准 SQL）SELECTNULLIF(expression1,expression2)FROMtable_name;

示例：

-- IF 示例：判断员工是否高薪SELECTname,salary,IF(salary>100000,'High','Normal')ASsalary_levelFROMemployees;-- NULLIF 示例：避免除零错误SELECTtotal_sales,number_of_customers,total_sales/NULLIF(number_of_customers,0)ASavg_sales_per_customerFROMsales_summary;-- 组合使用：如果邮箱为空，显示 'No Email'SELECTname,IF(NULLIF(email,'')ISNULL,'No Email',email)AScontact_emailFROMusers;

子查询

功能：嵌套在主查询内部的查询，可用于SELECT、FROM、WHERE、HAVING等子句中。
语法：

-- 在 WHERE 子句中SELECT*FROMtable1WHEREcolumn1IN(SELECTcolumn2FROMtable2);-- 在 FROM 子句中（派生表）SELECT*FROM(SELECT*FROMtable1)ASsubquery_table;-- 在 SELECT 子句中（标量子查询）SELECTcolumn1,(SELECTMAX(column2)FROMtable2)ASmax_valueFROMtable1;

示例：

-- 找出薪水高于部门平均薪水的员工SELECTname,department,salaryFROMemployees e1WHEREsalary>(SELECTAVG(salary)FROMemployees e2WHEREe2.department=e1.department);-- 使用派生表计算每个部门的员工数SELECTdepartment,employee_countFROM(SELECTdepartment,COUNT(*)ASemployee_countFROMemployeesGROUPBYdepartment)ASdept_stats;-- 标量子查询：获取每个产品的类别名称SELECTp.product_name,(SELECTcategory_nameFROMcategories cWHEREc.id=p.category_id)AScategoryFROMproducts p;

like

功能：用于在WHERE子句中进行模式匹配（模糊查询）。
语法：

SELECT*FROMtable_nameWHEREcolumn_nameLIKE'pattern';

通配符说明：

  • %：匹配任意多个字符（包括零个字符）
  • _：匹配单个字符
  • [charlist]：匹配字符列表中的任意单个字符（部分数据库支持）
  • [^charlist]或[!charlist]：匹配不在字符列表中的任意单个字符
    示例：
-- 查找以 'A' 开头的名字SELECT*FROMemployeesWHEREnameLIKE'A%';-- 查找以 'son' 结尾的姓氏SELECT*FROMemployeesWHERElast_nameLIKE'%son';-- 查找包含 'admin' 的职位SELECT*FROMemployeesWHEREjob_titleLIKE'%admin%';-- 查找第二个字符是 'a' 的名字SELECT*FROMemployeesWHEREnameLIKE'_a%';-- 查找以 A、B 或 C 开头的名字（MySQL）SELECT*FROMemployeesWHEREnameREGEXP'^[A-C]';-- 查找不以数字开头的产品代码SELECT*FROMproductsWHEREproduct_codeNOTLIKE'[0-9]%';

join

功能：用于基于相关列合并两个或多个表中的行。
语法：

SELECTcolumnsFROMtable1[JOIN_TYPE]JOINtable2ONtable1.column=table2.column;

常用 JOIN 类型：

  • INNER JOIN：返回两个表中匹配的行
  • LEFT (OUTER) JOIN：返回左表所有行，右表匹配的行，无匹配则为 NULL
  • RIGHT (OUTER) JOIN：返回右表所有行，左表匹配的行，无匹配则为 NULL
  • FULL (OUTER) JOIN：返回两个表中所有行，匹配的合并，无匹配则为 NULL
  • CROSS JOIN：返回两个表的笛卡尔积（所有可能的组合）
    示例：
-- INNER JOIN：获取有订单的客户信息SELECTcustomers.name,orders.order_date,orders.amountFROMcustomersINNERJOINordersONcustomers.id=orders.customer_id;-- LEFT JOIN：获取所有客户及其订单（包括没有订单的客户）SELECTcustomers.name,orders.order_date,orders.amountFROMcustomersLEFTJOINordersONcustomers.id=orders.customer_id;-- RIGHT JOIN：获取所有订单及其客户信息（包括没有客户信息的订单）SELECTcustomers.name,orders.order_date,orders.amountFROMcustomersRIGHTJOINordersONcustomers.id=orders.customer_id;-- FULL OUTER JOIN：获取所有客户和所有订单（MySQL 不支持，可用 UNION 模拟）SELECTcustomers.name,orders.order_date,orders.amountFROMcustomersLEFTJOINordersONcustomers.id=orders.customer_idUNIONSELECTcustomers.name,orders.order_date,orders.amountFROMcustomersRIGHTJOINordersONcustomers.id=orders.customer_idWHEREcustomers.idISNULL;-- CROSS JOIN：生成所有可能的组合SELECTcolors.color_name,sizes.size_nameFROMcolorsCROSSJOINsizes;

with tablename AS() 临时表

功能：公共表表达式（CTE），创建临时命名结果集，可在同一查询中多次引用，提高复杂查询的可读性。
语法：

WITHcte_name(column1,column2,...)AS(-- 子查询定义SELECT...FROM...WHERE...)SELECT*FROMcte_name;

示例：

-- 简单 CTE：计算部门平均薪水WITHdept_avgAS(SELECTdepartment,AVG(salary)ASavg_salaryFROMemployeesGROUPBYdepartment)SELECTe.name,e.department,e.salary,d.avg_salaryFROMemployees eJOINdept_avg dONe.department=d.departmentWHEREe.salary>d.avg_salary;-- 递归 CTE：生成数字序列（1 到 10）WITHRECURSIVE numbersAS(SELECT1ASnUNIONALLSELECTn+1FROMnumbersWHEREn<10)SELECT*FROMnumbers;-- 多个 CTEWITHhigh_salary_employeesAS(SELECT*FROMemployeesWHEREsalary>100000),department_summaryAS(SELECTdepartment,COUNT(*)ASemp_countFROMhigh_salary_employeesGROUPBYdepartment)SELECT*FROMdepartment_summaryORDERBYemp_countDESC;

distinct

功能：去除查询结果中的重复行，返回唯一值。
语法：

-- 去除所有列的重复组合SELECTDISTINCTcolumn1,column2,...FROMtable_name;-- 结合聚合函数使用SELECTCOUNT(DISTINCTcolumn_name)FROMtable_name;

示例：

-- 获取所有不重复的部门名称SELECTDISTINCTdepartmentFROMemployees;-- 获取不重复的（城市，国家）组合SELECTDISTINCTcity,countryFROMlocations;-- 统计不重复的客户数量SELECTCOUNT(DISTINCTcustomer_id)ASunique_customersFROMorders;-- 注意：DISTINCT 作用于所有选择的列SELECTDISTINCTfirst_name,last_nameFROMemployees;-- 这可能会返回多行，因为不同的人可能有相同的名字但不同的姓氏