SQL关键字分类及示例
【摘要】 1. 数据定义语言 (DDL)用于定义和管理数据库结构。CREATE:创建数据库对象(如表、视图、索引等)。CREATE TABLE students ( id INT PRIMARY KEY, name VARCHAR(50), age INT);ALTER:修改现有数据库对象。ALTER TABLE students ADD COLUMN email VARCHAR(...
1. 数据定义语言 (DDL)
用于定义和管理数据库结构。
CREATE
:创建数据库对象(如表、视图、索引等)。CREATE TABLE students ( id INT PRIMARY KEY, name VARCHAR(50), age INT );
ALTER
:修改现有数据库对象。ALTER TABLE students ADD COLUMN email VARCHAR(100);
DROP
:删除数据库对象。DROP TABLE students;
TRUNCATE
:快速删除表中的所有数据(保留表结构)。TRUNCATE TABLE students;
RENAME
(某些数据库支持):重命名表或列。ALTER TABLE students RENAME TO pupils;
2. 数据操作语言 (DML)
用于操作数据(增删改查)。
INSERT
:向表中插入数据。INSERT INTO students (id, name, age) VALUES (1, 'Alice', 20);
UPDATE
:更新表中的数据。UPDATE students SET age = 21 WHERE id = 1;
DELETE
:删除表中的数据。DELETE FROM students WHERE id = 1;
MERGE
(某些数据库支持):根据条件插入或更新数据(如SQL Server、Oracle)。MERGE INTO students AS target USING (VALUES (1, 'Bob', 22)) AS source (id, name, age) ON target.id = source.id WHEN MATCHED THEN UPDATE SET name = source.name, age = source.age WHEN NOT MATCHED THEN INSERT (id, name, age) VALUES (source.id, source.name, source.age);
3. 数据查询语言 (DQL)
用于查询数据。
SELECT
:从表中检索数据。SELECT name, age FROM students WHERE age > 18;
FROM
:指定查询的数据源。WHERE
:指定查询条件。GROUP BY
:按一个或多个列分组数据。SELECT age, COUNT(*) FROM students GROUP BY age;
HAVING
:对分组后的数据进行过滤。SELECT age, COUNT(*) FROM students GROUP BY age HAVING COUNT(*) > 1;
ORDER BY
:对结果集排序。SELECT name, age FROM students ORDER BY age DESC;
JOIN
:连接多个表。SELECT students.name, courses.course_name FROM students JOIN enrollments ON students.id = enrollments.student_id JOIN courses ON enrollments.course_id = courses.id;
UNION
:合并两个或多个SELECT
语句的结果集。SELECT name FROM students UNION SELECT teacher_name FROM teachers;
4. 数据控制语言 (DCL)
用于控制数据库访问权限。
GRANT
:授予用户权限。GRANT SELECT, INSERT ON students TO user1;
REVOKE
:撤销用户权限。REVOKE INSERT ON students FROM user1;
5. 事务控制语言 (TCL)
用于管理事务。
COMMIT
:提交事务,保存更改。COMMIT;
ROLLBACK
:回滚事务,撤销更改。ROLLBACK;
SAVEPOINT
:设置事务保存点。SAVEPOINT sp1;
SET TRANSACTION
:设置事务属性(如隔离级别)。SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
6. 其他常用关键字
INDEX
:创建或删除索引。CREATE INDEX idx_name ON students (name);
VIEW
:创建或删除视图。CREATE VIEW student_view AS SELECT name, age FROM students;
EXISTS
:检查子查询是否返回结果。SELECT name FROM students WHERE EXISTS (SELECT * FROM courses WHERE courses.teacher_id = 1);
CASE
:条件表达式。SELECT name, CASE WHEN age < 18 THEN 'Minor' ELSE 'Adult' END AS age_group FROM students;
NULL
:表示空值。DISTINCT
:去除重复值。SELECT DISTINCT age FROM students;
LIMIT
/OFFSET
(某些数据库支持):限制结果集大小。SELECT * FROM students LIMIT 10 OFFSET 5;
AS
:为列或表设置别名。SELECT name AS student_name FROM students;
7. 高级关键字
WITH
(公用表表达式,CTE):定义临时结果集。WITH student_counts AS ( SELECT teacher_id, COUNT(*) AS student_count FROM enrollments GROUP BY teacher_id ) SELECT teachers.name, student_counts.student_count FROM teachers JOIN student_counts ON teachers.id = student_counts.teacher_id;
PIVOT
/UNPIVOT
(某些数据库支持):旋转数据。OVER
:窗口函数,用于计算排名、累计和等。SELECT name, age, RANK() OVER (ORDER BY age DESC) AS rank FROM students;
总结
SQL关键字非常丰富,涵盖了数据库的各个方面。除了SELECT
、UPDATE
和DELETE
之外,还有许多其他关键字用于定义表结构、管理权限、控制事务、执行复杂查询等。掌握这些关键字可以帮助你更高效地操作数据库。
如果你对某个关键字或功能有更具体的需求,可以进一步深入学习!
【声明】本内容来自华为云开发者社区博主,不代表华为云及华为云开发者社区的观点和立场。转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息,否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱:
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论(0)