在SQL(Structured Query Language)中,语句可以根据它们的功能被分类为几种主要类型。这些类型包括数据定义语言 (DDL),数据操纵语言 (DML),数据控制语言 (DCL),和事务控制语言 (TCL)
1. 数据定义语言 (DDL)
DDL涉及到数据库模式和结构的创建、修改和删除操作。
这些语句直接影响数据库的结构。
-
CREATE:创建新的数据库对象,如表、视图、索引等。
CREATE TABLE table_name (column1 INT, column2 VARCHAR(255));
-
ALTER:修改现有数据库对象的结构。
ALTER TABLE table_name ADD column3 DATE;
-
DROP:删除数据库对象。
DROP TABLE table_name;
-
TRUNCATE:删除表中的数据,但不删除表本身。
TRUNCATE TABLE table_name;
-
RENAME:重命名数据库对象。
RENAME TABLE old_table_name TO new_table_name;
2. 数据操纵语言 (DML)
DML主要用于插入、查询、更新和删除数据——这些是日常数据库操作的核心。
-
SELECT:从一个或多个表中检索数据。
SELECT column1, column2 FROM table_name WHERE condition;
-
INSERT:向表中插入新数据。
INSERT INTO table_name (column1, column2) VALUES (value1, value2);
-
UPDATE:更新表中的数据。
UPDATE table_name SET column1 = value1 WHERE condition;
-
DELETE:从表中删除数据。
DELETE FROM table_name WHERE condition;
3. 数据控制语言 (DCL)
DCL包括命令,主要用于定义数据库的安全策略,包括用户的权限和对数据的访问控制。
-
GRANT:授予用户或角色权限。
GRANT SELECT ON table_name TO user_name;
-
REVOKE:撤销用户或角色的权限。
REVOKE SELECT ON table_name FROM user_name;
4. 事务控制语言 (TCL)
TCL用于管理SQL事务,这对于保持数据库的完整性和一致性至关重要。
-
BEGIN TRANSACTION 或 START TRANSACTION:显式地开始一个新的事务。
-
COMMIT:提交当前事务,使所有更改成为永久性的。
-
ROLLBACK:回滚当前事务,撤销自事务开始以来所做的所有更改。
-
SAVEPOINT:在事务中创建一个保存点,可以回滚到这个点而不是整个事务。
-
ROLLBACK TO SAVEPOINT:回滚到指定的保存点。
-
RELEASE SAVEPOINT:删除一个保存点。
DELETE、TRUNCATE、DROP的区别
在MySQL中,DELETE、TRUNCATE 和 DROP 是三种不同的数据删除命令,它们在使用场景和影响上有显著的区别。
1. DELETE
DELETE
用于从表中删除一行或多行记录,其特点是可以带有条件。
- 使用方式:可以指定条件来删除特定的行。如果没有指定条件,它将删除表中的所有行。
- 事务:DELETE 操作是事务安全的,意味着可以撤销(rollback)操作。
- 性能:删除大量数据时可能较慢,因为它逐行删除数据,并记录日志以支持事务的撤销。
- 日志:生成大量日志,因为每个删除的行都会记录在事务日志中。
- 空间释放:不会立即释放表空间,空间仍然被表占用。
DELETE FROM table_name WHERE condition;
2. TRUNCATE
TRUNCATE
用于删除表中的所有行,其特点是速度快,但不可指定条件。
- 使用方式:快速删除表中的所有数据,但不能指定条件。
- 事务:虽然TRUNCATE可以在某些情况下回滚,但它主要是DDL操作,通常会自动提交事务。
- 性能:比DELETE快,因为它不逐行删除数据,而是通过重置表指针来快速清空数据。
- 日志:不记录每一行的删除,而是记录对整个表的操作,因此日志较少。
- 空间释放:释放表占用的空间回到数据库。
TRUNCATE TABLE table_name;
3. DROP
DROP
用于完全删除表或数据库,包括其结构和数据。
- 使用方式:删除整个表或数据库,不仅仅是数据。
- 事务:DROP 操作通常不能回滚,一旦执行,表或数据库就完全消失了。
- 性能:非常快,因为它直接删除对象。
- 日志:记录的是删除对象的操作,而不是单独的行操作。
- 空间释放:完全释放对象所占用的空间。
DROP TABLE table_name;
总结
- 使用
DELETE
时,如果需要删除部分数据并希望操作可回滚,或者需要依据特定条件删除行。 - 使用
TRUNCATE
时,如果需要快速清空一个表的数据并且不需要回滚。 - 使用
DROP
时,如果需要删除整个表或数据库,并且不再需要这些结构。
MySQL数据类型
MySQL支持多种数据类型,这些类型可以大致分为几个类别:数值类型、日期和时间类型、字符串(字符和字节)类型、空间数据类型以及JSON数据类型。
1. 数值类型
数值类型用于存储数值数据,可以进一步分为整数类型和浮点数/小数类型。
整数类型
- TINYINT:非常小的整数,占用1个字节。
- SMALLINT:小的整数,占用2个字节。
- MEDIUMINT:中等大小的整数,占用3个字节。
- INT 或 INTEGER:标准的整数,占用4个字节。
- BIGINT:大整数,占用8个字节。
浮点数和小数类型
- FLOAT:单精度浮点数。
- DOUBLE 或 DOUBLE PRECISION:双精度浮点数。
- DECIMAL 或 NUMERIC:固定精度和固定小数点数。适用于存储精确的数值,如货币。
2. 日期和时间类型
用于存储日期和时间。
- DATE:仅日期,格式为 YYYY-MM-DD。
- TIME:仅时间,格式为 HH:MM:SS。
- DATETIME:日期和时间的组合,格式为 YYYY-MM-DD HH:MM:SS。
- TIMESTAMP:日期和时间的组合,格式为 YYYY-MM-DD HH:MM:SS,通常用于记录何时发生了数据变更。
- YEAR:年份,可以是两位或四位格式。
3. 字符串类型
用于存储各种类型的文本和二进制数据。
文本类型
- CHAR:固定长度的字符串,最多255个字符。
- VARCHAR:可变长度的字符串,最多可存储65535个字符。
- TINYTEXT:非常小的文本字符串,最多255个字符。
- TEXT:文本字符串,最多65535个字符。
- MEDIUMTEXT:中等长度的文本字符串,最多16777215个字符。
- LONGTEXT:非常大的文本字符串,最多4294967295个字符。
二进制类型
- BINARY:固定长度的二进制字符串。
- VARBINARY:可变长度的二进制字符串。
- TINYBLOB:非常小的二进制数据。
- BLOB:二进制数据,最多65535字节。
- MEDIUMBLOB:中等长度的二进制数据,最多16777215字节。
- LONGBLOB:非常大的二进制数据,最多4294967295字节。
4. 空间数据类型
用于存储空间数据,如地理数据。
- GEOMETRY
- POINT
- LINESTRING
- POLYGON
- MULTIPOINT
- MULTILINESTRING
- MULTIPOLYGON
- GEOMETRYCOLLECTION
5. JSON 数据类型
用于存储 JSON(JavaScript Object Notation)格式的数据。
- JSON:存储 JSON 文档的数据类型。