1. SQL简介
SQL(Structured Query Language,结构化查询语言)是用于管理关系数据库的标准编程语言,包括数据查询、数据操纵、数据定义和数据控制等功能。
2. 数据查询(SELECT)
- 基础查询:从表中检索数据。
SELECT column1, column2 FROM table_name; SELECT Sname, Sno FROM Student;
- WHERE子句:过滤记录。
SELECT * FROM table_name WHERE condition; SELECT * FROM Student WHERE Sno = 'S001';
- ORDER BY子句:对结果集进行排序。
SELECT column1, column2 FROM table_name ORDER BY column1 ASC|DESC; SELECT Cname, Cno FROM Course ORDER BY Cname ASC;
- 聚合函数:计算汇总值。
SELECT COUNT(column_name) FROM table_name; SELECT COUNT(Sno) AS TotalStudents FROM Student;
- GROUP BY子句:按列对结果集进行分组。
SELECT column1, COUNT(column2) FROM table_name GROUP BY column1; SELECT Cno, COUNT(Sno) AS StudentCount FROM Student GROUP BY Cno;
3. 数据操纵(DML)
- INSERT INTO:向表中插入新记录。
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...); INSERT INTO Student (Sname, Sno, Cno) VALUES ('张三', 'S002', 'C001');
- UPDATE:修改表中的现有记录。
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition; UPDATE Student SET Sname = '李四' WHERE Sno = 'S001';
- DELETE:从表中删除记录。
4. 数据定义(DDL)
- CREATE TABLE:创建新表。
CREATE TABLE table_name ( column1 datatype, column2 datatype, ... ); CREATE TABLE Enrollment ( Eno INT PRIMARY KEY, Sno VARCHAR(10), Cno VARCHAR(10), Grade INT, FOREIGN KEY (Sno) REFERENCES Student(Sno), FOREIGN KEY (Cno) REFERENCES Course(Cno) );
- ALTER TABLE:修改现有表结构。
ALTER TABLE table_name ADD column_name datatype; ALTER TABLE Student ADD Email VARCHAR(50);
- DROP TABLE:删除表及其所有数据。
5. 其他常用功能
- 通配符:用于模糊匹配字符串。
SELECT * FROM table_name WHERE column_name LIKE 'pattern%'; SELECT * FROM Student WHERE Sname LIKE '张%';
- CASE语句:在查询中进行条件判断。
SELECT column1, CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ... ELSE result END AS alias_name FROM table_name; SELECT Sname, CASE WHEN Age < 20 THEN '青少年' WHEN Age BETWEEN 20 AND 30 THEN '青年' ELSE '成年' END AS AgeGroup FROM Student;