SQL语言基础教程-2024-07-11-08-53-05.lever.md

SQL语言基础教程SQL简介1.SQL的历史与发展SQL，全称为StructuredQueryLanguage，即结构化查询语言，是用于管理关系型数据库的标准语言。SQL的起源可以追溯到20世纪70年代，由IBM的两位研究人员DonaldD.Chamberlin和RaymondF.Boyce基于关系数据库理论开发。1974年，他们发表了“SEQUEL：AStructuredEnglishQueryLanguage”的论文，这是SQL的前身。1979年，SQL被标准化，随后在1986年被国际标准化组织（ISO）采纳，成为国际标准。自那时起，SQL经历了多次更新和扩展，以适应不断变化的数据库需求。例如，SQL-92（也称为SQL2）引入了更多功能，如视图、子查询和存储过程。SQL-99（也称为SQL3）进一步增强了语言，包括对可空性的改进、支持多语句触发器和更复杂的查询。SQL-2003（也称为SQL4）则引入了对XML数据的支持，以及更强大的窗口函数和递归查询。2.SQL在数据库管理中的作用SQL在数据库管理中扮演着核心角色，它提供了与数据库交互的统一方式，包括数据的检索、更新、插入和删除。以下是SQL在数据库管理中的主要功能：2.1数据查询SQL允许用户通过SELECT语句从数据库中检索数据。例如，从一个名为employees的表中检索所有员工的姓名和部门：--SQL查询示例

SELECTname,department

FROMemployees;2.2数据定义SQL提供了创建、修改和删除数据库结构的能力。例如，创建一个新的表employees：--创建表的SQL示例

CREATETABLEemployees(

idINTPRIMARYKEY,

nameVARCHAR(100),

departmentVARCHAR(100),

hire_dateDATE

);2.3数据操作SQL允许对数据库中的数据进行操作，包括插入、更新和删除。例如，向employees表中插入一条新记录：--插入数据的SQL示例

INSERTINTOemployees(id,name,department,hire_date)

VALUES(1,'张三','销售部','2023-01-01');2.4数据控制SQL还提供了数据访问控制功能，允许管理员设置用户权限，确保数据的安全性。例如，授予用户user1对employees表的读取权限：--授予权限的SQL示例

GRANTSELECTONemployeesTOuser1;2.5数据完整性SQL支持数据完整性约束，如主键、外键、唯一性和检查约束，确保数据的一致性和准确性。例如，设置employees表的id字段为主键：--设置主键的SQL示例

ALTERTABLEemployees

ADDCONSTRAINTpk_employeesPRIMARYKEY(id);2.6数据分析SQL还支持复杂的数据分析，通过聚合函数如SUM、AVG、COUNT等，用户可以对数据进行统计和分析。例如，计算employees表中每个部门的员工总数：--使用聚合函数的SQL示例

SELECTdepartment,COUNT(*)astotal_employees

FROMemployees

GROUPBYdepartment;2.7数据检索优化SQL支持索引的创建，以优化数据检索速度。例如，为employees表的department字段创建索引：--创建索引的SQL示例

CREATEINDEXidx_departmentONemployees(department);2.8数据备份与恢复虽然SQL本身不直接提供数据备份和恢复的功能，但它可以与数据库管理系统（DBMS）的其他工具结合使用，实现数据的备份和恢复。例如，使用mysqldump工具备份MySQL数据库：--使用mysqldump备份数据库的示例

mysqldump-uroot-pdatabase_name>backup.sql2.9数据同步SQL可以用于数据同步，通过比较两个数据库的差异并应用必要的更新，确保数据的一致性。例如，使用INSERTINTO...SELECT语句从一个表同步数据到另一个表：--数据同步的SQL示例

INSERTINTOemployees_backup(id,name,department,hire_date)

SELECTid,name,department,hire_date

FROMemployees;2.10数据安全SQL支持加密和安全策略，确保数据在传输和存储过程中的安全性。虽然具体的实现细节依赖于DBMS，但SQL提供了基础的框架，如使用SSL进行安全连接：--使用SSL连接数据库的示例

--这通常在数据库配置文件中设置，而不是直接在SQL语句中2.11数据审计SQL可以用于数据审计，通过记录数据更改的历史，帮助追踪数据的修改情况。例如，创建一个审计表来记录employees表的更改：--创建审计表的SQL示例

CREATETABLEemployees_audit(

idINT,

nameVARCHAR(100),

departmentVARCHAR(100),

hire_dateDATE,

actionVARCHAR(10),

timestampTIMESTAMP

);2.12数据迁移SQL可以用于数据迁移，将数据从一个数据库系统迁移到另一个系统。这通常涉及到数据导出和导入的过程，例如，使用SELECTINTOOUTFILE导出数据：--数据导出的SQL示例

SELECT*INTOOUTFILE'/tmp/employees.csv'

FIELDSTERMINATEDBY','OPTIONALLYENCLOSEDBY'"'

LINESTERMINATEDBY'\n'

FROMemployees;2.13数据可视化虽然数据可视化通常由专门的工具完成，但SQL可以提供数据预处理和清洗，为数据可视化准备数据。例如，使用CASE语句对数据进行分类：--使用CASE语句分类数据的SQL示例

SELECTdepartment,

COUNT(CASEWHENhire_date<'2023-01-01'THEN1END)asold_employees,

COUNT(CASEWHENhire_date>='2023-01-01'THEN1END)asnew_employees

FROMemployees

GROUPBYdepartment;2.14数据治理SQL在数据治理中也扮演着重要角色，通过定义数据标准和规则，确保数据的质量和合规性。例如，使用CHECK约束确保hire_date字段的值在合理范围内：--使用CHECK约束的SQL示例

ALTERTABLEemployees

ADDCONSTRAINTchk_hire_dateCHECK(hire_date>='1900-01-01'ANDhire_date<=CURRENT_DATE);2.15数据集成SQL可以用于数据集成，将来自不同源的数据合并到一个统一的视图中。例如，使用JOIN语句从两个表中检索相关数据：--数据集成的SQL示例

SELECT,d.department_name

FROMemployeese

JOINdepartmentsdONe.department=d.department_id;2.16数据清洗SQL可以用于数据清洗，通过删除重复记录、修正错误数据和填充缺失值，提高数据质量。例如，删除employees表中的重复记录：--数据清洗的SQL示例

DELETEFROMemployees

WHEREidNOTIN(

SELECTMIN(id)

FROMemployees

GROUPBYname,department,hire_date

);2.17数据建模虽然数据建模通常在设计数据库结构时完成，但SQL提供了实现这些模型的工具，如创建表、定义关系和设置约束。例如，定义employees表与departments表之间的关系：--定义表间关系的SQL示例

ALTERTABLEemployees

ADDCONSTRAINTfk_department

FOREIGNKEY(department)REFERENCESdepartments(department_id);2.18数据质量监控SQL可以用于数据质量监控，通过定期运行查询来检查数据的完整性和一致性。例如，检查employees表中是否有缺失的hire_date值：--数据质量监控的SQL示例

SELECTCOUNT(*)

FROMemployees

WHEREhire_dateISNULL;2.19数据生命周期管理SQL支持数据生命周期管理，包括数据的创建、使用、归档和销毁。例如，归档旧数据到另一个表：--数据归档的SQL示例

INSERTINTOemployees_archive(id,name,department,hire_date)

SELECTid,name,department,hire_date

FROMemployees

WHEREhire_date<'2023-01-01';2.20数据合规性SQL可以用于确保数据的合规性，通过设置访问控制和审计日志，满足法规要求。例如，记录对employees表的修改：--记录数据修改的SQL示例

CREATETRIGGERupdate_employees_audit

AFTERUPDATEONemployees

FOREACHROW

BEGIN

INSERTINTOemployees_audit(id,name,department,hire_date,action,timestamp)

VALUES(NEW.id,NEW.name,NEW.department,NEW.hire_date,'UPDATE',CURRENT_TIMESTAMP);

END;2.21数据性能优化SQL提供了性能优化的工具，如查询优化、索引管理和存储过程。例如，优化SELECT查询：--查询优化的SQL示例

EXPLAINSELECTname,department

FROMemployees

WHEREdepartment='销售部';2.22数据备份策略虽然具体的备份策略依赖于DBMS和组织的需要，但SQL提供了基础的备份和恢复命令。例如，使用pg_dump工具备份PostgreSQL数据库：--使用pg_dump备份数据库的示例

pg_dump-Uusername-hhostname-Fcdatabase_name>backup.dump2.23数据恢复策略SQL支持数据恢复，通过恢复备份或使用事务日志。例如，使用mysql工具恢复MySQL数据库：--使用mysql恢复数据库的示例

mysql-uroot-pdatabase_name<backup.sql2.24数据安全策略SQL支持数据安全策略，包括用户认证、权限管理和数据加密。例如，设置用户密码：--设置用户密码的SQL示例

SETPASSWORDFOR'user1'@'localhost'=PASSWORD('new_password');2.25数据审计策略SQL可以用于数据审计策略的实施，通过记录数据更改和访问日志，确保数据的透明度和可追溯性。例如，创建一个触发器来记录数据更改：--创建触发器记录数据更改的SQL示例

CREATETRIGGERinsert_employees_audit

AFTERINSERTONemployees

FOREACHROW

BEGIN

INSERTINTOemployees_audit(id,name,department,hire_date,action,timestamp)

VALUES(NEW.id,NEW.name,NEW.department,NEW.hire_date,'INSERT',CURRENT_TIMESTAMP);

END;2.26数据治理策略SQL支持数据治理策略，包括数据分类、数据生命周期管理和数据质量控制。例如，使用TRUNCATE语句清空表：--清空表的SQL示例

TRUNCATETABLEemployees;2.27数据集成策略SQL可以用于数据集成策略的实施，通过合并多个数据源，提供统一的数据视图。例如，使用UNION操作符合并两个查询的结果：--使用UNION操作符的SQL示例

SELECTname,'员工'astype

FROMemployees

UNION

SELECTname,'经理'astype

FROMmanagers;2.28数据清洗策略SQL支持数据清洗策略，包括数据验证、数据修正和数据标准化。例如，修正employees表中的错误数据：--修正数据的SQL示例

UPDATEemployees

SETdepartment='市场部'

WHEREdepartment='市场营销部';2.29数据建模策略SQL可以用于数据建模策略的实施，通过设计数据库结构和定义数据关系，确保数据的一致性和完整性。例如，使用ALTERTABLE语句添加新字段：--添加新字段的SQL示例

ALTERTABLEemployees

ADDCOLUMNsalaryDECIMAL(10,2);2.30数据质量监控策略SQL支持数据质量监控策略，包括数据完整性检查和数据一致性验证。例如，使用CHECK约束确保salary字段的值在合理范围内：--使用CHECK约束的SQL示例

ALTERTABLEemployees

ADDCONSTRAINTchk_salaryCHECK(salary>=0);2.31数据生命周期管理策略SQL可以用于数据生命周期管理策略的实施，通过数据归档和数据销毁，管理数据的生命周期。例如，销毁employees表中的旧数据：--销毁旧数据的SQL示例

DELETEFROMemployees

WHEREhire_date<'2023-01-01';2.32数据合规性策略SQL支持数据合规性策略，包括数据保护法规的遵守和数据隐私的保护。例如，使用REVOKE语句撤销用户权限：--撤销用户权限的SQL示例

REVOKESELECTONemployeesFROMuser1;通过上述示例和解释，我们可以看到SQL在数据库管理中的广泛作用，从数据的创建和操作到数据的分析和治理，SQL都是不可或缺的工具。SQL基础语法3.数据查询SELECT语句3.1原理SELECT语句是SQL中最常用的语句之一，用于从数据库中检索数据。它可以从一个或多个表中选择数据，并可以对数据进行过滤、排序、分组等操作。SELECT语句的基本语法如下：SELECTcolumn1,column2,...

FROMtable_name;这里，column1,column2,...是你想要从表table_name中检索的列名。你可以选择所有列，使用*作为列名。3.2内容选择所有列--选择所有列

SELECT*FROMEmployees;选择特定列--选择特定列

SELECTFirstName,LastNameFROMEmployees;过滤数据使用WHERE子句来过滤数据，只选择满足特定条件的行。--过滤数据

SELECT*FROMEmployeesWHERESalary>50000;排序数据使用ORDERBY子句来对结果进行排序。--排序数据

SELECT*FROMEmployeesORDERBYSalaryDESC;分组数据使用GROUPBY子句来对数据进行分组，通常与聚合函数如COUNT,SUM,AVG,MAX,MIN一起使用。--分组数据

SELECTDepartment,COUNT(*)FROMEmployeesGROUPBYDepartment;使用聚合函数--使用聚合函数

SELECTDepartment,AVG(Salary)FROMEmployeesGROUPBYDepartment;4.数据操作INSERT,UPDATE,DELETE语句4.1原理INSERT,UPDATE,DELETE语句用于在数据库中插入、更新和删除数据。这些操作是数据库管理的基本组成部分，允许你修改数据库中的数据。4.2内容插入数据使用INSERT语句向表中插入新行。--插入数据

INSERTINTOEmployees(FirstName,LastName,Salary)

VALUES('John','Doe',60000);更新数据使用UPDATE语句来修改表中已存在的行。--更新数据

UPDATEEmployees

SETSalary=65000

WHEREFirstName='John'ANDLastName='Doe';删除数据使用DELETE语句来删除表中的行。--删除数据

DELETEFROMEmployees

WHEREFirstName='John'ANDLastName='Doe';4.3示例假设我们有一个Employees表，包含以下数据：EmployeeIDFirstNameLastNameDepartmentSalary1JohnDoeHR600002JaneSmithIT550003MikeJohnsonHR58000插入新员工--插入新员工

INSERTINTOEmployees(EmployeeID,FirstName,LastName,Department,Salary)

VALUES(4,'Sara','Williams','IT',62000);更新员工工资--更新员工工资

UPDATEEmployees

SETSalary=65000

WHEREFirstName='Jane'ANDLastName='Smith';删除员工--删除员工

DELETEFROMEmployees

WHEREFirstName='Mike'ANDLastName='Johnson';通过这些操作，你可以有效地管理数据库中的数据，确保数据的准确性和完整性。数据表操作5.创建表CREATETABLE5.1原理在SQL中，CREATETABLE语句用于创建一个新的数据表。数据表是数据库中存储数据的基本单位，由行和列组成。每一列称为一个字段，每一行称为一条记录。创建表时，需要定义表的结构，包括字段名、字段类型、字段约束等。5.2内容语法CREATETABLE表名(

字段1数据类型[约束],

字段2数据类型[约束],

...

字段N数据类型[约束]

);示例假设我们需要创建一个名为students的表，用于存储学生信息，包括id、name、age和gender字段。--创建students表

CREATETABLEstudents(

idINTPRIMARYKEY,--定义id为整型，且为主键

nameVARCHAR(50)NOTNULL,--定义name为可变长字符串，最大长度50，且不能为空

ageINTCHECK(age>=0),--定义age为整型，且年龄必须大于等于0

genderCHAR(1)--定义gender为单字符，通常用于表示性别（M/F）

);5.3解释idINTPRIMARYKEY：定义id字段为整型，且为表的主键，意味着id字段的值必须是唯一的，且不能为NULL。nameVARCHAR(50)NOTNULL：定义name字段为可变长字符串类型，最大长度为50，且不能为空。ageINTCHECK(age>=0)：定义age字段为整型，且通过CHECK约束确保年龄值大于等于0。genderCHAR(1)：定义gender字段为单字符类型，通常用于存储性别信息。6.修改表ALTERTABLE6.1原理ALTERTABLE语句用于修改已存在的表的结构。这包括添加、修改或删除字段，以及添加或删除约束。6.2内容语法ALTERTABLE表名

ADD字段名数据类型[约束],

DROPCOLUMN字段名,

MODIFYCOLUMN字段名新数据类型[新约束],

ADDCONSTRAINT约束名约束类型(字段名),

DROPCONSTRAINT约束名;示例假设我们想要修改students表，添加一个email字段，并修改age字段的约束，使其可以存储负数。--添加email字段

ALTERTABLEstudents

ADDemailVARCHAR(100)UNIQUE;

--修改age字段的约束

ALTERTABLEstudents

MODIFYCOLUMNageINT;6.3解释ADDemailVARCHAR(100)UNIQUE：向students表中添加一个email字段，类型为可变长字符串，最大长度为100，并且通过UNIQUE约束确保每个email值都是唯一的。MODIFYCOLUMNageINT：修改students表中的age字段类型为整型，移除了之前的CHECK约束，允许存储负数。7.删除表DROPTABLE7.1原理DROPTABLE语句用于删除数据库中的表。执行此操作将永久删除表及其所有数据，除非在删除前进行了备份。7.2内容语法DROPTABLE表名;示例假设我们决定不再需要students表，可以使用以下语句删除它。--删除students表

DROPTABLEstudents;7.3解释执行DROPTABLEstudents;语句后，students表及其所有数据将从数据库中永久删除。在删除表之前，应确保不再需要该表中的数据，或者已经进行了适当的备份。以上内容详细介绍了SQL中创建、修改和删除数据表的基本操作，包括语法、示例和解释。通过这些操作，可以有效地管理数据库中的数据结构，满足不同场景下的需求。数据类型与约束8.SQL中的数据类型在SQL中，数据类型定义了存储在数据库中的数据的种类。选择正确的数据类型对于确保数据的准确性和优化存储空间至关重要。以下是一些常见的SQL数据类型：数值类型INT：用于存储整数。FLOAT：用于存储浮点数。DECIMAL(p,s)：用于存储定点数，其中p是总位数，s是小数点后的位数。字符串类型VARCHAR(n)：用于存储可变长度的字符串，n是最大长度。CHAR(n)：用于存储固定长度的字符串，n是长度。TEXT：用于存储长文本。日期和时间类型DATE：用于存储日期值。TIME：用于存储时间值。DATETIME：用于存储日期和时间值。布尔类型BOOLEAN：用于存储真或假的值。二进制类型BLOB：用于存储二进制大对象，如图像或文件。VARBINARY(n)：用于存储可变长度的二进制数据。8.1示例：创建一个包含不同数据类型的表--创建一个包含不同数据类型的表

CREATETABLEemployees(

idINTAUTO_INCREMENTPRIMARYKEY,

nameVARCHAR(50)NOTNULL,

hire_dateDATE,

salaryDECIMAL(10,2),

is_managerBOOLEANDEFAULTFALSE,

photoBLOB

);9.表约束与数据完整性表约束用于限制表中的数据，以确保数据的完整性和一致性。这些约束可以是列级的，也可以是表级的。以下是一些常见的表约束：主键约束：PRIMARYKEY，确保列的唯一性，且不允许为空。外键约束：FOREIGNKEY，用于维护两个表之间的关系，确保引用完整性。唯一约束：UNIQUE，确保列的值是唯一的，但允许有空值。非空约束：NOTNULL，确保列的值不能为空。默认值约束：DEFAULT，为列提供默认值，如果插入新行时未指定值，则使用默认值。检查约束：CHECK，用于限制列中的值，可以是复杂的条件。9.1示例：创建一个包含约束的表--创建一个包含主键、外键、唯一、非空和默认值约束的表

CREATETABLEorders(

order_idINTAUTO_INCREMENTPRIMARYKEY,

customer_idINTNOTNULL,

order_dateDATENOTNULL,

total_amountDECIMAL(10,2)CHECK(total_amount>=0),

FOREIGNKEY(customer_id)REFERENCEScustomers(id)

);在这个例子中，orders表的customer_id列是外键，引用customers表的id列。total_amount列有一个检查约束，确保金额非负。9.2示例：使用唯一约束--创建一个包含唯一约束的表

CREATETABLEemails(

emailVARCHAR(100)UNIQUENOTNULL

);在这个例子中，emails表的email列有唯一约束，这意味着每个电子邮件地址在表中只能出现一次。通过这些约束，SQL数据库能够自动执行数据验证，减少数据错误和不一致性，从而提高数据的质量和可靠性。SQL查询进阶10.连接查询JOIN10.1原理在SQL中，JOIN是一种用于从两个或多个表中检索数据的查询。当数据分布在多个表中，而这些表之间存在某种关联时，JOIN操作就变得非常有用。JOIN操作允许你根据一个或多个键的匹配条件，将来自不同表的行组合在一起。10.2类型内连接（INNERJOIN）：返回两个表中键相匹配的行。左连接（LEFTJOIN）：返回左表中的所有行，即使右表中没有匹配的行，也会在结果集中显示为NULL。右连接（RIGHTJOIN）：与左连接相反，返回右表中的所有行，即使左表中没有匹配的行，也会在结果集中显示为NULL。全连接（FULLJOIN）：返回左表和右表中所有匹配和不匹配的行。10.3示例假设我们有两个表，Employees和Departments，其中Employees表包含员工信息，Departments表包含部门信息。Employees表有一个DepartmentID字段，它与Departments表的ID字段相对应。--创建Employees表

CREATETABLEEmployees(

IDINTPRIMARYKEY,

NameVARCHAR(255),

DepartmentIDINT

);

--创建Departments表

CREATETABLEDepartments(

IDINTPRIMARYKEY,

NameVARCHAR(255)

);

--插入数据到Employees表

INSERTINTOEmployees(ID,Name,DepartmentID)

VALUES(1,'JohnDoe',1),

(2,'JaneSmith',2),

(3,'MikeJohnson',1);

--插入数据到Departments表

INSERTINTODepartments(ID,Name)

VALUES(1,'Sales'),

(2,'Marketing'),

(3,'IT');内连接示例SELECTEmployees.Name,Departments.NameASDepartmentName

FROMEmployees

INNERJOINDepartmentsONEmployees.DepartmentID=Departments.ID;解释：此查询将返回Employees表中员工的名字和他们所在部门的名字，只包括那些在Departments表中也有匹配的部门ID的员工。左连接示例SELECTEmployees.Name,Departments.NameASDepartmentName

FROMEmployees

LEFTJOINDepartmentsONEmployees.DepartmentID=Departments.ID;解释：此查询将返回Employees表中所有员工的名字，以及他们所在部门的名字。如果某个员工的DepartmentID在Departments表中没有匹配，那么部门名字将显示为NULL。10.4使用场景数据分析：当需要从多个表中提取数据进行分析时，JOIN操作是必不可少的。报表生成：在生成包含多个表数据的报表时，JOIN可以确保数据的完整性和准确性。11.子查询与嵌套查询11.1原理子查询（也称为嵌套查询）是在另一个查询语句中嵌入的查询。子查询可以出现在SELECT、FROM、WHERE或HAVING子句中，用于过滤或提供外部查询的数据源。11.2示例假设我们有一个Orders表，其中包含订单信息，以及一个Products表，其中包含产品信息。--创建Orders表

CREATETABLEOrders(

IDINTPRIMARYKEY,

ProductIDINT,

QuantityINT

);

--创建Products表

CREATETABLEProducts(

IDINTPRIMARYKEY,

NameVARCHAR(255),

PriceDECIMAL(10,2)

);

--插入数据到Orders表

INSERTINTOOrders(ID,ProductID,Quantity)

VALUES(1,1,5),

(2,2,3),

(3,1,2);

--插入数据到Products表

INSERTINTOProducts(ID,Name,Price)

VALUES(1,'Laptop',1200.00),

(2,'Mouse',20.00),

(3,'Keyboard',50.00);子查询示例SELECTOrders.ID,Orders.Quantity,Products.Name,Products.Price

FROMOrders

JOINProductsONOrders.ProductID=Products.ID

WHEREProducts.Price>(SELECTAVG(Price)FROMProducts);解释：此查询将返回所有订单，其中产品的价格高于Products表中所有产品的平均价格。子查询(SELECTAVG(Price)FROMProducts)用于计算平均价格，然后外部查询使用这个结果作为过滤条件。11.3使用场景数据过滤：子查询可以用于过滤数据，例如找出所有价格高于平均价格的产品。数据汇总：子查询可以用于汇总数据，然后在外部查询中使用这些汇总结果。11.4注意事项性能考虑：子查询可能会影响查询性能，特别是在大型数据集上。在可能的情况下，考虑使用JOIN或子查询的其他替代方案。可读性：复杂的子查询可能难以阅读和理解。确保使用注释和清晰的命名来提高代码的可读性。通过以上示例和解释，你可以更深入地理解JOIN和子查询在SQL中的应用，以及它们如何帮助你更有效地处理和分析数据。聚合函数与分组12.使用聚合函数SUM,AVG,COUNT等聚合函数在SQL中用于对一组值执行计算并返回单个值。以下是最常用的聚合函数：SUM:返回指定列的总和。AVG:返回指定列的平均值。COUNT:返回指定列或行的数目。MIN:返回指定列的最小值。MAX:返回指定列的最大值。12.1示例：使用SUM和AVG假设我们有一个sales表，包含product_id,quantity,和price列。我们想要计算所有销售的总数量和平均价格。--计算所有销售的总数量

SELECTSUM(quantity)ASTotalQuantity

FROMsales;

--计算所有销售的平均价格

SELECTAVG(price)ASAveragePrice

FROMsales;12.2示例：使用COUNT如果我们想要知道有多少不同的产品被销售过，我们可以使用COUNT函数与DISTINCT关键字。--计算销售过的不同产品的数量

SELECTCOUNT(DISTINCTproduct_id)ASNumberOfProducts

FROMsales;13.GROUPBY与HAVING子句GROUPBY子句用于将数据表中的数据，根据一列或多列的不同值，进行分组。HAVING子句则用于过滤这些分组，只显示满足特定条件的分组。13.1示例：GROUPBY假设我们想要知道每个产品的总销售数量。--按产品ID分组，计算每个产品的总销售数量

SELECTproduct_id,SUM(quantity)ASTotalQuantity

FROMsales

GROUPBYproduct_id;13.2示例：GROUPBY与HAVING如果我们只对销售数量超过100的产品感兴趣，我们可以添加HAVING子句。--按产品ID分组，计算每个产品的总销售数量，只显示销售数量超过100的产品

SELECTproduct_id,SUM(quantity)ASTotalQuantity

FROMsales

GROUPBYproduct_id

HAVINGSUM(quantity)>100;13.3示例：GROUPBY与多个列我们也可以根据多个列进行分组，例如，我们想要知道每个产品在每个城市的销售情况。--假设sales表中还有city列

--按产品ID和城市分组，计算每个产品在每个城市的总销售数量

SELECTproduct_id,city,SUM(quantity)ASTotalQuantity

FROMsales

GROUPBYproduct_id,city;13.4示例：GROUPBY与HAVING的复杂使用如果我们想要知道每个城市中销售数量超过100的产品，我们可以结合使用GROUPBY和HAVING。--按产品ID和城市分组，计算每个产品在每个城市的总销售数量，只显示销售数量超过100的产品

SELECTproduct_id,city,SUM(quantity)ASTotalQuantity

FROMsales

GROUPBYproduct_id,city

HAVINGSUM(quantity)>100;通过这些示例，我们可以看到聚合函数和GROUPBY、HAVING子句在SQL中的强大功能，它们可以帮助我们从大量数据中提取出有用的信息，进行数据分析和决策支持。排序与限制14.ORDERBY子句在SQL查询中，ORDERBY子句用于对结果集进行排序。这可以基于一个或多个列，按照升序（ASC，默认）或降序（DESC）排列数据。使用ORDERBY可以让你更有效地分析和展示数据，特别是在处理大量数据时。14.1原理ORDERBY子句在查询执行的最后阶段应用，它不改变表中的数据，只改变查询结果的显示顺序。当数据库接收到一个包含ORDERBY的查询时，它首先执行查询以获取所有匹配的行，然后根据ORDERBY子句中指定的列和排序顺序对这些行进行排序。14.2内容基本语法SELECTcolumn1,column2,...

FROMtable_name

ORDERBYcolumn1[ASC|DESC],column2[ASC|DESC],...;示例假设我们有一个名为employees的表，其中包含id、name、salary和department等列。下面的查询将返回所有员工的姓名和薪水，按薪水降序排列：--查询所有员工的姓名和薪水，按薪水降序排列

SELECTname,salary

FROMemployees

ORDERBYsalaryDESC;如果我们要按部门和薪水排序，可以使用多个列：--查询所有员工的姓名、薪水和部门，先按部门升序，再按薪水降序排列

SELECTname,salary,department

FROMemployees

ORDERBYdepartmentASC,salaryDESC;15.LIMIT子句与分页查询LIMIT子句用于限制查询结果的行数，这对于分页查询特别有用，可以有效地展示数据的一部分，而不是整个结果集。15.1原理LIMIT子句允许你指定返回的行数，以及从结果集的哪一行开始。这在处理大数据集时非常有用，可以避免一次性加载过多数据，提高查询性能和用户体验。15.2内容基本语法SELECTcolumn1,column2,...

FROMtable_name

LIMIT[start,]count;start是可选的，表示从结果集中的哪一行开始返回数据。count是必须的，表示要返回的行数。示例假设我们想要从employees表中获取前10名薪水最高的员工：--获取薪水最高的前10名员工

SELECTname,salary

FROMemployees

ORDERBYsalaryDESC

LIMIT10;如果我们想要实现分页查询，例如每页显示10条记录，要获取第2页的数据，可以使用以下查询：--获取薪水排序后的第2页数据，每页10条记录

SELECTname,salary

FROMemployees

ORDERBYsalaryDESC

LIMIT10OFFSET10;在这个例子中，OFFSET10表示跳过前10行，LIMIT10表示从跳过后的行开始返回10行数据。通过结合ORDERBY和LIMIT子句，你可以有效地对数据进行排序和限制，实现数据的高效展示和管理。视图与存储过程16.视图VIEW的创建与使用16.1视图的概念视图（View）是SQL中的一种虚拟表，其内容由存储在数据库中的查询结果组成。视图并不实际存储数据，而是存储查询语句，当访问视图时，数据库会执行视图中的查询语句，从而获取数据。视图可以简化复杂的查询，提供数据的安全访问，以及隐藏数据的复杂性。16.2创建视图创建视图使用CREATEVIEW语句。下面是一个创建视图的例子：--创建一个名为view_employees的视图，显示员工的姓名和部门

CREATEVIEWview_employeesAS

SELECTemployee_name,department

FROMemployees

WHEREdepartment='Sales';16.3使用视图使用视图就像使用普通表一样，可以进行查询、更新等操作。例如，查询view_employees视图：--查询view_employees视图

SELECT*FROMview_employees;16.4更新视图视图也可以被更新，但只有当视图基于单个表且查询中没有使用聚合函数、DISTINCT、GROUPBY、HAVING等时，才能更新。--更新view_employees视图中的数据

UPDATEview_employees

SETdepartment='Marketing'

WHEREdepartment='Sales';16.5删除视图使用DROPVIEW语句可以删除视图。--删除view_employees视图

DROPVIEWview_employees;17.存储过程的编写与调用17.1存储过程的概念存储过程（StoredProcedure）是SQL中的一组预编译的SQL语句和控制流语句的集合，存储在数据库中，通过一个名称来调用，可以接受输入参数，返回输出参数，甚至可以返回结果集。存储过程可以提高代码的重用性和执行效率，简化应用程序的开发。17.2创建存储过程创建存储过程使用CREATEPROCEDURE语句。下面是一个创建存储过程的例子：--创建一个名为proc_get_employee的存储过程，接受部门作为参数，返回该部门的员工信息

DELIMITER//

CREATEPROCEDUREproc_get_employee(INdeptVARCHAR(50))

BEGIN

SELECT*FROMemployeesWHEREdepartment=dept;

END//

DELIMITER;17.3调用存储过程调用存储过程使用CALL语句。例如，调用proc_get_employee存储过程：--调用proc_get_employee存储过程，传入'Sales'作为参数

CALLproc_get_employee('Sales');17.4存储过程的参数存储过程可以有输入参数、输出参数和输入输出参数。下面是一个包含输出参数的存储过程示例：--创建一个名为proc_get_department_count的存储过程，接受部门作为输入参数，返回该部门的员工数量

DELIMITER//

CREATEPROCEDUREproc_get_department_count(INdeptVARCHAR(50),OUTcountINT)

BEGIN

SELECTCOUNT(*)INTOcountFROMemployeesWHEREdepartment=dept;

END//

DELIMITER;调用时需要声明一个变量来接收输出参数：--声明一个变量接收输出参数

DECLAREemp_countINT;

--调用存储过程并接收输出参数

CALLproc_get_department_count('Sales',@emp_count);

--显示输出参数的值

SELECT@emp_count;17.5删除存储过程使用DROPPROCEDURE语句可以删除存储过程。--删除proc_get_employee存储过程

DROPPROCEDUREproc_get_employee;17.6存储过程的控制流语句存储过程可以包含控制流语句，如IF、CASE、LOOP等，以实现更复杂的逻辑。下面是一个包含IF语句的存储过程示例：--创建一个名为proc_check_salary的存储过程，接受员工ID作为参数，检查其工资是否超过10000

DELIMITER//

CREATEPROCEDUREproc_che