永洪社区

标题: 史上最全SQL基础知识总结（理论+举例+10条建议） [打印本页]

---

作者: 喝酸奶不舔盖    时间: 2024-5-30 17:11
标题: 史上最全SQL基础知识总结（理论+举例+10条建议）

一、SQL 概述

1、什么是 SQL

SQL，全称Structured Query Language，意为结构化查询语言，是用于操作关系数据库（MySQL、Oracle、SQL Server 等）的编程语言。

2、SQL语言的版本历史

• SQL-86：这是SQL最早的版本，于1986年发布。它定义了SQL的基本语法和数据操纵功能。
• SQL-89：在1989年发布，这个版本引入了更多的特性，包括排序、分组和连接操作的支持。
• SQL-92：于1992年发布，这个版本通过引入新的特性进一步扩展了SQL语言的能力，包括多表连接、子查询、视图和存储过程等，是目前获得最广泛支持的版本。
• SQL:1999：这个版本于1999年发布，也被称为SQL3。它引入了更多的面向对象和复杂数据类型的支持，并增加了XML处理能力。
• SQL:2003：在2003年发布，这个版本引入了更多的标准化特性，包括窗口函数、异常处理和递归查询等。
• SQL:2008：于2008年发布，这个版本增加了对逻辑文件名和批量操作的支持。
• SQL:2011：在2011年发布，这个版本增加了大数据集处理和空间数据操作的能力。
• SQL:2016：于2016年发布，这个版本增加了JSON处理和新的日期时间类型支持。
• SQL:2019：在2019年发布，这个版本增加了对机器学习模型和图形数据处理的支持。
• 
  
  

3、SQL语句基础语法规范

• SQL语句仅可识别半角字符；切记！切记！
• 一条完整的SQL语句以分号结尾，但可以分单行或多行书写；
• SQL关键字不区分大小写，但为了与非关键字作区分，建议使用大写；
  
• 
  

二、SQL 分类：DDL、DML、DCL、DQL

• DDL：数据定义语言，用于定义和管理SQL数据库中的表结构和索引。
• DML：数据操作语言, 用于对数据库进行增、删、改、查这些操作。
• DCL：数据控制语言，主要用于控制不同权限的数据库用户对数据库表、视图等的访问。
• DQL：数据查询语言，用于从数据库获取信息，它也是大多数终端用户及应用程序员最经常使用的SQL语言。
• 
  
  

三、DDL（Data Definition Language）：数据定义语言

1、基本操作

1.1操作数据库：创建库CREATE DATABASE，删除库DROP DATABASE，修改库ALTER DATABASE。

•                                         
  

CREATE DATABASE TestDB;

DROP DATABASE TestDB;

ALTER DATABASE TestDB COLLATE Chinese_PRC_CI_AS;

1.2 数据类型：数字型（int，tinyint，decimal，float等）、日期和时间类型（Date，TIME等）、字符串类型（char，varchar等）。

        

CREATE TABLE NumTable (

    id INT,

    smallNum TINYINT,

    money DECIMAL(10, 2),

    size FLOAT

);

CREATE TABLE DateTable (

    date_column DATE,

    time_column TIME

);

CREATE TABLE StringTable (

    fixed_length_name CHAR(50),

    variable_length_address VARCHAR(255)

);

1.3 操作表：创建表CREATE TABLE，删除表DROP TABLE，修改表ALTER TABLE，重命名表 RENAME TABLE。

•                
• CREATE TABLE EmployeeTable (
  

    ID int,

    Name varchar(255),

    Age int

);

DROP TABLE EmployeeTable;

ALTER TABLE EmployeeTable ADD Email varchar(255);

ALTER TABLE EmployeeTable MODIFY COLUMN Age smallint;

ALTER TABLE EmployeeTable DROP COLUMN Age;

RENAME TABLE EmployeeTable TO StaffTable;

四、DML（Data Manipulation Language）：数据操作语言

4.1 插入数据：利用INSERT INTO语句添加一条或多条记录。

•                         
  

INSERT INTO employees (id, name, department_id) VALUES (1, 'Li Ming', 101);   

(向employees表中插入一条新的员工记录，id为1, 名字是'Li Ming', 部门id是101)

INSERT INTO employees (name, department_id) VALUES ('Wang Gang', 102);      

(向employees表中插入一条新的员工记录, id将自动生成，名字是'Wang Gang', 部门id是102)

INSERT INTO employees VALUES (3, 'Zhang San', 103);            

(向employees表中插入一条新的员工记录，id为3, 名字是'Zhang San', 部门id是103)

4.2 修改数据：用UPDATE语句可以修改表中的数据。

•                         
  

UPDATE employees SET department_id = 201 WHERE name = 'Li Ming';            

(修改名为'Li Ming'的员工的部门id为201)

UPDATE employees SET name = 'Liu Yan' WHERE id = 2;               

(修改id为2的员工的名字为'Liu Yan')

UPDATE employees SET name = 'Zhu Rongji', department_id = 202 WHERE id = 3;                  

(修改id为3的员工的名字为'Zhu Rongji'和部门id为202)

4.3 删除数据：DELETE FROM语句用于在表中删除一条或者多条记录。

•                
  

DELETE FROM employees WHERE id = 1;               

(删除id为1的员工记录)

DELETE FROM employees WHERE name = 'Wang Gang';                  

(删除名字为'Wang Gang'的员工记录)

DELETE FROM employees WHERE department_id = 202;                 

(删除部门id为202的所有员工记录)

五、DCL（Data Control Language）：数据控制语言

5.1 创建用户：使用CREATE USER语句创建新的数据库用户。

•                         
  

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

（在"localhost"上创建一个名为"newuser"的新用户，密码是"password"。）

5.2 给用户授权：使用GRANT语句为用户分配访问权限。

•                         
  

GRANT SELECT, INSERT, DELETE ON database_name.table_name TO 'username'@'localhost';

（在localhost上给"username"用户赋予运database_name数据库中table_name表的SELECT，INSERT和DELETE权限。）

5.3 撤销授权：使用REVOKE语句撤销用户的访问权限。

•                         
  

REVOKE INSERT ON database_name.table_name FROM 'username'@'localhost';

（在localhost上撤销"username"用户对database_name数据库中table_name表的INSERT权限。）

5.4 查看用户权限：使用SHOW GRANTS语句查看用户当前的权限列表。

•                         
  

SHOW GRANTS FOR 'username'@'localhost';

（显示"localhost"上"username"用户的所有权限。）

5.5 删除用户：用DROP USER语句来删除一个用户。

•                         
  

DROP USER 'username'@'localhost';

（删除"localhost"上名为"username"的用户。）

5.6 修改用户密码（以root身份）：通过ALTER USER语句可以修改用户的密码。

•                         
  

ALTER USER 'username'@'localhost' IDENTIFIED BY 'newpassword';

（修改在"localhost"上username用户的密码为"newpassword"。）

六、DQL（Data Query Language）：数据查询语言

6.1 基础查询：利用SELECT 语句可以查询数据库中的数据。

•                 
  

SELECT *

FROM Employee;  

（查询Employees表中的所有记录）

SELECT FirstName, LastName

FROM Employee;  

（查询Employee表中的FirstName和LastName字段所有记录）

6.2 条件查询：通过使用WHERE子句来设定查询条件。

•                 
  

SELECT *

FROM Employee

WHERE Salary > 5000;

（查询Employee表中Salary大于5000的所有记录）

SELECT FirstName, LastName

FROM Employee

WHERE Age <= 30;

（查询Employee表中年龄小于等于30的员工的firstName和lastName字段记录）

SELECT *

FROM Employee

WHERE Salary > 5000

AND Age <= 30;

（查询Employee表中Salary大于5000且年龄小于等于30的所有记录）

SELECT *

FROM Employee

WHERE Salary > 5000

OR Age <= 30;

（查询Employee表中Salary大于5000或年龄小于等于30的所有记录）

6.3 模糊查询：使用LIKE子句，配合通配符%和_进行模糊查询。

•                         
  

SELECT *

FROM Employee

WHERE FirstName LIKE 'John%';

（查询Employee表中FirstName以John开头的所有记录）

SELECT *

FROM Employee

WHERE FirstName LIKE '%John%';

（查询Employee表中FirstName包含John的所有记录）

SELECT FirstName, LastName

FROM Employee

WHERE LastName LIKE '%son_';

（查询Employee表中LastName以son+一个任意字符结束的所有记录）

6.4 字段控制查询：运用DISTINCT关键字进行去重查询。

•                 
  

SELECT DISTINCT City

FROM Employee;

（查询Employee表中，City字段去重之后的所有城市记录）

SELECT DISTINCT Age, Salary

FROM Employee

WHERE Age<50;

（查询Employee表中，Age小于50岁的员工的Age和Salary字段组合记录，并进行去重处理）

6.5 排序：用ORDER BY子句按照一个或多个列进行排序。

单列排序：

•                         
  

SELECT *

FROM Employee

ORDER BY Salary;

（按'工资'列的升序返回Employee表中的所有行。）

多列排序：

•                 
  

SELECT *

FROM Employee

ORDER BY Salary, Age DESC;

（首先根据'工资'列的升序对Employee表中的行进行排序，然后在工资相同的情况下，根据'Age'列的降序进行排序。）

6.6 聚合函数：包括COUNT，SUM，MAX，MIN，AVG等函数。

COUNT：        

        

SELECT COUNT(*)

FROM Employee;

（返回Employee表的总行数。）

SUM：

•                 
  

SELECT SUM(Salary)

FROM Employee;

（返回Employee表中所有员工的薪水总和。）

MAX：

•                 
  

SELECT MAX(Age)

FROM Employee;

（返回Employee表中员工的最大年龄。）

MIN：

•                 
  

SELECT MIN(Age)

FROM Employee;

（返回Employee表中员工的最小年龄。）

AVG：

•                 
  

SELECT AVG(Salary)

FROM Employee;

（返回Employee表中员工的平均薪水。）

6.7 分组查询：GROUP BY子句是用于结合聚合函数，依据一个或多个列进行分组。

按单列分组：

•                 
  

SELECT Department, COUNT(*)

FROM Employee

GROUP BY Department;

（返回每个部门的员工数量。）

按多列分组：

•                 
  

SELECT Department, JobTitle, AVG(Salary)

FROM Employee

GROUP BY Department, JobTitle;

（返回每个部门及职位下员工的平均薪水。）

分组后过滤：

•                         
  

SELECT Department, JobTitle, AVG(Salary)

FROM Employee

GROUP BY Department, JobTitle

HAVING AVG(Salary) > 5000;

（返回每个部门及职位下员工的平均薪水大于5000的部分。）

6.8 LIMIT：用来限定查询结果的起始行，以及总行数。

简单LIMIT：

•                 
  

SELECT *

FROM Employee

ORDER BY Salary DESC

LIMIT 10;

（返回前10个工资最高的员工。）

带偏移量的LIMIT：

•                 
  

SELECT *

FROM Employee

ORDER BY Salary DESC

LIMIT 10 OFFSET 5;

（返回工资排名第6到第15的10个员工。）

6.9 多表连接查询：

内连接：INNER JOIN

•                
• SELECT Orders.OrderID, Customers.CustomerName
  

FROM Orders

INNER JOIN Customers

ON Orders.CustomerID = Customers.CustomerID;

左连接：LEFT JOIN

•                         
  

SELECT Customers.CustomerName, Orders.OrderID

FROM Customers

LEFT JOIN Orders

ON Customers.CustomerID = Orders.CustomerID;

右连接：RIGHT JOIN

•                         
  

SELECT Orders.OrderID, Customers.CustomerName

FROM Orders

RIGHT JOIN Customers

ON Orders.CustomerID = Customers.CustomerID;

全外连接：FULL JOIN

•                
•         
  

SELECT Customers.CustomerName, Orders.OrderID

FROM Customers

FULL JOIN Orders

ON Customers.CustomerID = Orders.CustomerID;

笛卡尔积：CROSS JOIN

•                 
  

SELECT Products.ProductName, Suppliers.SupplierName

FROM Products

CROSS JOIN Suppliers;

七、SQL编程的10条吐血建议

• 使用标准的SQL关键字：使用标准的SQL关键字，比如 SELECT, INSERT, UPDATE, DELETE, WHERE 等。
• 遵循语法规则：SQL语句需要遵循一定的语法规则，比如 SELECT 语句后面应该跟了表名和要查询的列名。
• 使用合适的数据类型：在创建表格或添加数据时，应选择正确和适合的数据类型。
• 注释的使用：注释可以帮助他人或者自己理解代码，包括在单行中使用 "--" 或者在多行中使用 "/* ... */" 。
• 使用适当的排序：使用 ORDER BY 语句以对结果进行适当的排序。
• 区分大小写：虽然大部分SQL平台都是不区分大小写的，但是要注意某些平台可能仍然做大小写区分。
• 合理使用索引：使用索引可以加快查询速度，但要注意不要过度使用，因为索引会占用磁盘空间，并且在插入、删除和更新操作时可能使性能下降。
• 防止SQL注入：在编写可执行参数的SQL语句时，确保参数已正确转义。
• 利用子查询：子查询可以在一个SQL语句中执行另一个SQL语句，帮助解决复杂的查询问题。
• 适当使用表别名：为表设置适当的表别名，不仅可以减少SQL语句的复杂度，也能尽量避免出错，让SQL语句更加的直观、易理解。
• 
  
• 文章源自：公众号 跟强哥学SQL
  
  

---

欢迎光临 永洪社区 (https://club.yonghongtech.com/)

Powered by Discuz! X3.4