SQL知识点

数据库 (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子句将删除表中所有数据示例DELETEFROMusersWHEREid5;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,johnexample.com);update tablename功能更新表中已有的记录。语法UPDATEtable_nameSETcolumn1value1,column2value2,...WHEREcondition;注意务必使用WHERE子句限定要更新的行否则会更新所有行示例UPDATEusersSETemailnew_emailexample.comWHEREusernamejohn_doe;SELECT 查询详解功能从数据库表中检索数据是 SQL 中最核心和复杂的命令。完整语法结构SELECT[DISTINCT]column1,column2,...FROMtable1[JOINtable2ONjoin_condition][WHEREcondition][GROUPBYcolumn1,column2,...][HAVINGgroup_condition][ORDERBYcolumn1[ASC|DESC],...][LIMITnumber][OFFSETnumber];子句说明与示例DISTINCT去除结果中的重复行。SELECTDISTINCTdepartmentFROMemployees;JOIN连接多个表。-- 内连接 (INNER JOIN)SELECTorders.id,customers.nameFROMordersINNERJOINcustomersONorders.customer_idcustomers.id;-- 左连接 (LEFT JOIN)SELECTemployees.name,departments.dept_nameFROMemployeesLEFTJOINdepartmentsONemployees.dept_iddepartments.id;WHERE过滤行。SELECT*FROMproductsWHEREprice100ANDstock0;GROUP BY按列分组常与聚合函数 (COUNT,SUM,AVG,MAX,MIN) 一起使用。SELECTdepartment,COUNT(*)asemployee_count,AVG(salary)asavg_salaryFROMemployeesGROUPBYdepartment;HAVING过滤分组后的结果WHERE用于分组前过滤行。SELECTdepartment,COUNT(*)asemp_countFROMemployeesGROUPBYdepartmentHAVINGCOUNT(*)5;ORDER BY对结果排序。SELECTname,scoreFROMstudentsORDERBYscoreDESC;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 ROWSELECTname,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,CASEWHENscore90THENAWHENscore80THENBWHENscore70THENCWHENscore60THENDELSEFENDASgradeFROMexam_results;-- 简化版 CASE 表达式等值判断SELECTproduct_name,CASEcategory_idWHEN1THENElectronicsWHEN2THENClothingWHEN3THENBooksELSEOtherENDAScategory_nameFROMproducts;if NULLIF功能IF函数用于条件判断NULLIF函数用于比较两个表达式相等时返回 NULL。语法-- IF 函数MySQLSELECTIF(condition,value_if_true,value_if_false)FROMtable_name;-- NULLIF 函数标准 SQLSELECTNULLIF(expression1,expression2)FROMtable_name;示例-- IF 示例判断员工是否高薪SELECTname,salary,IF(salary100000,High,Normal)ASsalary_levelFROMemployees;-- NULLIF 示例避免除零错误SELECTtotal_sales,number_of_customers,total_sales/NULLIF(number_of_customers,0)ASavg_sales_per_customerFROMsales_summary;-- 组合使用如果邮箱为空显示 No EmailSELECTname,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.departmente1.department);-- 使用派生表计算每个部门的员工数SELECTdepartment,employee_countFROM(SELECTdepartment,COUNT(*)ASemployee_countFROMemployeesGROUPBYdepartment)ASdept_stats;-- 标量子查询获取每个产品的类别名称SELECTp.product_name,(SELECTcategory_nameFROMcategories cWHEREc.idp.category_id)AScategoryFROMproducts p;like功能用于在WHERE子句中进行模式匹配模糊查询。语法SELECT*FROMtable_nameWHEREcolumn_nameLIKEpattern;通配符说明%匹配任意多个字符包括零个字符_匹配单个字符[charlist]匹配字符列表中的任意单个字符部分数据库支持[^charlist]或[!charlist]匹配不在字符列表中的任意单个字符示例-- 查找以 A 开头的名字SELECT*FROMemployeesWHEREnameLIKEA%;-- 查找以 son 结尾的姓氏SELECT*FROMemployeesWHERElast_nameLIKE%son;-- 查找包含 admin 的职位SELECT*FROMemployeesWHEREjob_titleLIKE%admin%;-- 查找第二个字符是 a 的名字SELECT*FROMemployeesWHEREnameLIKE_a%;-- 查找以 A、B 或 C 开头的名字MySQLSELECT*FROMemployeesWHEREnameREGEXP^[A-C];-- 查找不以数字开头的产品代码SELECT*FROMproductsWHEREproduct_codeNOTLIKE[0-9]%;join功能用于基于相关列合并两个或多个表中的行。语法SELECTcolumnsFROMtable1[JOIN_TYPE]JOINtable2ONtable1.columntable2.column;常用 JOIN 类型INNER JOIN返回两个表中匹配的行LEFT (OUTER) JOIN返回左表所有行右表匹配的行无匹配则为 NULLRIGHT (OUTER) JOIN返回右表所有行左表匹配的行无匹配则为 NULLFULL (OUTER) JOIN返回两个表中所有行匹配的合并无匹配则为 NULLCROSS JOIN返回两个表的笛卡尔积所有可能的组合示例-- INNER JOIN获取有订单的客户信息SELECTcustomers.name,orders.order_date,orders.amountFROMcustomersINNERJOINordersONcustomers.idorders.customer_id;-- LEFT JOIN获取所有客户及其订单包括没有订单的客户SELECTcustomers.name,orders.order_date,orders.amountFROMcustomersLEFTJOINordersONcustomers.idorders.customer_id;-- RIGHT JOIN获取所有订单及其客户信息包括没有客户信息的订单SELECTcustomers.name,orders.order_date,orders.amountFROMcustomersRIGHTJOINordersONcustomers.idorders.customer_id;-- FULL OUTER JOIN获取所有客户和所有订单MySQL 不支持可用 UNION 模拟SELECTcustomers.name,orders.order_date,orders.amountFROMcustomersLEFTJOINordersONcustomers.idorders.customer_idUNIONSELECTcustomers.name,orders.order_date,orders.amountFROMcustomersRIGHTJOINordersONcustomers.idorders.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.departmentd.departmentWHEREe.salaryd.avg_salary;-- 递归 CTE生成数字序列1 到 10WITHRECURSIVE numbersAS(SELECT1ASnUNIONALLSELECTn1FROMnumbersWHEREn10)SELECT*FROMnumbers;-- 多个 CTEWITHhigh_salary_employeesAS(SELECT*FROMemployeesWHEREsalary100000),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;-- 这可能会返回多行因为不同的人可能有相同的名字但不同的姓氏