SQL入门大全

SQL（Structured Query Language，结构化查询语言）是一种用于管理关系型数据库的标准编程语言。它具有数据操纵和数据定义等多种功能，为数据库管理系统提供了强大的交互性特点，能够极大地提高计算机应用系统的工作质量与效率。SQL语言不仅能独立应用于终端，还可以作为子语言为其他程序设计提供有效助力，与其他程序语言一起优化程序功能，为用户提供更多更全面的信息。

SQL介绍：

SQL数据库的结构一般是通过表来存储和管理数据，表的结构类似于Excel，但具有一些特殊的特点和要求，如列名称为字段，每个字段都有自己的数据类型和名称；行称为记录，数据库以记录为单位进行读写操作；单元格只能输入一个数据，且不可再拆分。SQL数据库一般采用CS（客户端/服务器）结构，客户端程序（如Web浏览器、SQL客户端工具等）通过数据库管理系统（DBMS）与服务器端的数据库进行交互。

SQL语句是SQL语言的核心，通过关键字、库名、表名、列名等组合而成，用于操作数据库。

SQL语句主要分类：

数据定义语言（DDL） 用于定义和管理数据库中的对象（视图、索引等），包括CREATE、DROP、ALTER等关键字。
数据操纵语言（DML）： 用于查询和修改数据库中的数据，包括SELECT、INSERT、UPDATE、DELETE等关键字。
数据控制语言（DCL）： 用于控制数据库的访问权限和事务处理，包括COMMIT、ROLLBACK、GRANT、REMOVE等关键字。

常见的数据库

Oracle数据库： 甲骨文公司的一款关系数据库管理系统，系统可移植性好、使用方便、功能强，适用于各类大、中、小、微机环境。
DB2数据库： IBM开发的数据库，采取了对外开放的政策，有多个版本，如DB2工作组版、DB2企业版和DB2个人版等。
SQL Server数据库： 美国微软公司开发的一种关系型数据库系统，对所有的主流服务器骨架都有很好的支持，扩展性、高性能、系统管理先进性突出。
PostgreSQL数据库： 以加州大学伯克利分校计算机系开发的POSTGRES为基础，现已更名为PostgreSQL，特性齐全，对象软件自由，支持世界上最丰富的数据类型。
MySQL数据库： 一种开放源代码的关系型数据库管理系统，对外开放且免费使用，功能一般，性能中等，在小型办公方面占有一定优势。
达梦数据库： 一款性能较好、支持大容量数据的关系型数据库管理系统，适用于企业级的数据管理需求国产。

数据库基本操作及示例

数据定义语言（DDL）

数据定义语言（DDL）是用于定义或修改数据库结构的SQL语句。在MySQL中，DDL语句主要用于创建、删除、修改数据库、表、索引等对象。以下是一些常见的DDL关键字以及它们在MySQL中的使用示例：

1. 创建数据库

CREATE DATABASE

CREATE DATABASE database_name;  
-- 例如：  
CREATE DATABASE my_database;

1
2
3
4
5
6

2. 删除数据库

DROP DATABASE
DROP DATABASE database_name;  
-- 例如：  
DROP DATABASE my_database;
1
2
3
4

3. 创建表

CREATE TABLE

CREATE TABLE table_name (  
    column1 datatype,  
    column2 datatype,  
    column3 datatype,  
    ...  
);  


-- 例如：  
CREATE TABLE employees (  
    id INT AUTO_INCREMENT PRIMARY KEY,  
    name VARCHAR(50),  
    age INT,  
    email VARCHAR(100)  
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

4. 删除表

DROP TABLE

DROP TABLE table_name;  
-- 例如：  
DROP TABLE employees;
1
2
3
4
5

5. 修改表

ALTER TABLE
添加列

ALTER TABLE table_name ADD column_name datatype;  
-- 例如：  
ALTER TABLE employees ADD address VARCHAR(100);
删除列
ALTER TABLE table_name DROP COLUMN column_name;  
-- 例如：  
ALTER TABLE employees DROP COLUMN address;
修改列的数据类型
ALTER TABLE table_name MODIFY COLUMN column_name new_datatype;  
-- 例如：  
ALTER TABLE employees MODIFY COLUMN age TINYINT;
修改列的名称
MySQL没有直接的MODIFY COLUMN来重命名列，但你可以使用CHANGE关键字：

ALTER TABLE table_name CHANGE old_column_name new_column_name datatype;  
-- 例如：  
ALTER TABLE employees CHANGE age employee_age INT;
添加主键

ALTER TABLE table_name ADD PRIMARY KEY (column_name);  
-- 例如：  
ALTER TABLE employees ADD PRIMARY KEY (id);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

删除主键
注意：MySQL没有直接的DDL语句来删除主键，但你可以通过修改表结构来删除它（通常是通过删除主键约束并重新添加它，但不指定为主键）。

添加索引
CREATE INDEX index_name ON table_name (column_name);  
-- 或者使用 ALTER TABLE  
ALTER TABLE table_name ADD INDEX index_name (column_name);  
-- 例如：  
ALTER TABLE employees ADD INDEX idx_email (email);
删除索引

DROP INDEX index_name ON table_name;  
-- 或者使用 ALTER TABLE  
ALTER TABLE table_name DROP INDEX index_name;  
-- 例如：  
ALTER TABLE employees DROP INDEX idx_email;
1
2
3
4
5
6
7
8
9
10
11
12
13

数据操纵语言（DML）

数据操纵语言（DML）是用于查询和修改数据的SQL语句。在MySQL中，DML语句主要包括SELECT、INSERT、UPDATE和DELETE等关键字。以下是这些关键字的使用示例，按照分点表示和归纳的方式呈现：

1. SELECT
   功能：从数据库中检索数据。

示例：

SELECT column1, column2, ...  
FROM table_name  
WHERE condition;  
  
-- 例如，查询名为"employees"的表中所有年龄大于30的员工姓名和年龄：  
SELECT name, age  
FROM employees  
WHERE age > 30;
1
2
3
4
5
6
7
8

2. INSERT
   功能：向数据库表中插入新的行或数据。

示例：

方式一：插入单行数据

INSERT INTO table_name (column1, column2, ...)  
VALUES (value1, value2, ...);  
  
-- 例如，向"employees"表中插入一条新数据：  
INSERT INTO employees (name, age, email)  
VALUES ('John Doe', 35, 'john@example.com');
1
2
3
4
5
6

方式二：插入多行数据

INSERT INTO table_name (column1, column2, ...)  
VALUES   
(value1a, value2a, ...),  
(value1b, value2b, ...),  
...;  
  
-- 例如，向"employees"表中插入多条新数据：  
INSERT INTO employees (name, age, email)  
VALUES   
('Jane Smith', 30, 'jane@example.com'),  
('Bob Johnson', 40, 'bob@example.com');
1
2
3
4
5
6
7
8
9
10
11

3. UPDATE
   功能：更新数据库表中的现有行或数据。

示例：

UPDATE table_name  
SET column1 = value1, column2 = value2, ...  
WHERE condition;  
  
-- 例如，将"employees"表中所有年龄为35的员工的姓名改为"John Updated"：  
UPDATE employees  
SET name = 'John Updated'  
WHERE age = 35;
1
2
3
4
5
6
7
8

4. DELETE
   功能：从数据库表中删除行或数据。

示例：

DELETE FROM table_name  
WHERE condition;  
  
-- 例如，删除"employees"表中所有年龄大于40的员工：  
DELETE FROM employees  
WHERE age > 40;  
  
-- 注意：如果省略WHERE子句，将会删除表中的所有数据。这通常被称为TRUNCATE TABLE操作，但DELETE语句没有直接提供TRUNCATE的功能。
1
2
3
4
5
6
7
8

数据控制语言（DCL, Data Control Language）

数据控制语言（DCL, Data Control Language）主要用于管理数据库用户的权限和访问控制。虽然DCL不像DML（数据操纵语言）或DDL（数据定义语言）那样经常使用，但它在确保数据库的安全性和访问控制方面起着至关重要的作用。
最常用的关键字是GRANT和REVOKE，它们分别用于授予和撤销用户的权限。

1. GRANT
   GRANT语句用于授予用户或角色对数据库、表或列的特定权限。

示例：
授予用户user1从任何主机连接到MySQL服务器的权限，并允许其使用密码password1进行身份验证：

GRANT ALL PRIVILEGES ON *.* TO 'user1'@'%' IDENTIFIED BY 'password1';
1

注意：. 表示所有数据库的所有表。在生产环境中，为了安全起见，应尽量避免使用这种通配符组合，而应明确指定所需的数据库和表。

授予用户user2对mydb数据库中所有表的SELECT和INSERT权限：

GRANT SELECT, INSERT ON mydb.* TO 'user2'@'localhost';
1

授予用户user3对mydb数据库中mytable表的UPDATE权限：

GRANT UPDATE ON mydb.mytable TO 'user3'@'localhost';
1

2. REVOKE
   REVOKE语句用于撤销先前授予的权限。

示例：
撤销用户user1从mydb数据库中所有表的SELECT权限：

REVOKE SELECT ON mydb.* FROM 'user1'@'%';
1

撤销用户user2的所有权限：

REVOKE ALL PRIVILEGES ON *.* FROM 'user2'@'localhost';
1

注意事项：
在使用GRANT和REVOKE语句时，请始终确保您明确知道要授予或撤销的权限，以及要影响的用户、主机和数据库/表。
在生产环境中，应尽量避免使用通配符（如*.*），而应明确指定所需的数据库、表和权限。
权限的更改通常需要FLUSH PRIVILEGES;语句来使更改生效，但MySQL通常会在会话结束时自动执行此操作。然而，在某些情况下，您可能需要手动执行此语句来确保权限更改立即生效。
在授予权限时，请始终考虑安全性。不要授予不必要的权限，并确保只向可信的用户和主机授予权限。

SELECT拓展（重要）

SELECT语句是SQL中最基础和最常用的语句之一，它提供了丰富的功能和灵活性，可以根据需要进行数据的查询、筛选、排序、聚合等操作。通过以上各种用法的介绍，可以更加清晰地理解和使用SELECT语句。

1. 选择所有列
   用法：使用通配符*来选择所有列。
   示例：SELECT * FROM table_name;
2. 选择特定列
   用法：指定要查询的列名，用逗号分隔多个列名。
   示例：SELECT column1, column2 FROM table_name;
3. 使用别名
   用法：使用AS关键字给列名或表名指定别名。
   示例：SELECT column1 AS alias1, column2 AS alias2 FROM table_name;
4. 按条件选择数据
   用法：使用WHERE子句来筛选满足指定条件的行。
   示例：SELECT * FROM table_name WHERE condition;
5. 对查询结果排序
   用法：使用ORDER BY子句来按照指定列的升序或降序对结果进行排序。
   示例：
   升序（默认）：SELECT * FROM table_name ORDER BY column1 ASC;
   降序：SELECT * FROM table_name ORDER BY column1 DESC;
6. 限制结果集大小
   用法：使用LIMIT关键字来限制查询结果的数量。
   示例：SELECT * FROM table_name LIMIT 10;（选择前10行）
7. 消除重复行
   用法：使用DISTINCT关键字来消除查询结果中的重复行。
   示例：SELECT DISTINCT column1, column2 FROM table_name;
8. 连接多个表
   用法：使用JOIN语句来连接多个表，根据指定的条件关联它们的行。
   示例：SELECT * FROM table1 JOIN table2 ON table1.column1 = table2.column1;
9. 聚合函数
   用法：使用聚合函数对数据进行操作，如COUNT(), SUM(), AVG(), MAX(), MIN()等。
   示例：SELECT COUNT(column1) FROM table_name;（计算column1的非空值数量）
10. 分组和过滤结果
    用法：使用GROUP BY和HAVING子句来对结果进行分组和过滤。
    示例：SELECT column1, COUNT(column2) FROM table_name GROUP BY column1 HAVING COUNT(column2) > 10;（按column1分组，并筛选column2数量大于10的组）

事务拓展（重要）

在关系型数据库管理系统（RDBMS）中，事务（Transaction）是一个作为单个逻辑单元执行的工作单位。事务可以确保数据库从一个一致性状态转变到另一个一致性状态。如果事务中的某个操作失败，那么整个事务将被回滚（Rollback），数据库将返回到事务开始之前的状态。

在SQL中，事务的关键字主要包括：

BEGIN TRANSACTION 或 START TRANSACTION：开始一个新的事务。
示例（MySQL）：
START TRANSACTION或BEGIN;

COMMIT：提交事务，使事务中的更改永久化。
示例（MySQL）：
COMMIT;
ROLLBACK：撤销事务，回滚到事务开始之前的状态。
示例（MySQL）：
ROLLBACK;
SAVEPOINT：在事务中设置一个保存点，允许你在后续的操作中回滚到该保存点，而不是事务的开始。
示例（MySQL）：

SAVEPOINT savepoint_name;
ROLLBACK TO SAVEPOINT：回滚到指定保存点的状态。

示例（MySQL）：

ROLLBACK TO SAVEPOINT savepoint_name;
RELEASE SAVEPOINT：删除一个保存点。
示例（MySQL）：
RELEASE SAVEPOINT savepoint_name;
SET TRANSACTION：设置事务的属性，如隔离级别。
示例（MySQL）：

SET TRANSACTION ISOLATION LEVEL READ-COMMITTED;

MySQL中使用事务时，通常需要设置事务的隔离级别。MySQL支持以下隔离级别：

1. READ UNCOMMITTED（未提交读）：允许读取并发事务尚未提交的数据。
2. READ COMMITTED（提交读）：对同一字段的多次读取结果都是一致的。当某个事务读取该字段时，其他事务对该字段的修改对其不可见。
3. REPEATABLE READ（可重复读）：对同一字段的多次读取结果都是一致的。对同一字段的多次读取结果都是事务开始时的值。
4. SERIALIZABLE（可串行化）：最高的隔离级别，所有的事务依次逐个执行，这样事务之间就不可能产生干扰。
   使用事务时，通常的步骤是：

使用 START TRANSACTION 或 BEGIN 开始一个新的事务。
执行一系列的SQL语句，这些语句要么全部成功，要么全部失败。
如果所有语句都成功执行，使用 COMMIT 提交事务，使更改永久化。
如果在事务执行过程中发生错误，使用 ROLLBACK 撤销事务，回到事务开始之前的状态。
使用事务可以确保数据的完整性和一致性，特别是在涉及多个表或复杂操作的情况下。