数据库SQL

标准SQL语法：

1、SELECT 语句 - 从表中检索数据

SELECT column1, column2, ...  
FROM table_name  
WHERE condition;  
  
-- 示例：从名为"students"的表中选择所有年龄大于20的学生  
SELECT name, age  
FROM students  
WHERE age > 20;

2、INSERT INTO 语句 - 向表中插入新数据

INSERT INTO table_name (column1, column2, ...)  
VALUES (value1, value2, ...);  
  
-- 示例：向"students"表中插入一条新记录  
INSERT INTO students (name, age, grade)  
VALUES ('张三', 22, '三年级');

3、UPDATE 语句 - 更新表中的数据

UPDATE table_name  
SET column1 = value1, column2 = value2, ...  
WHERE condition;  
  
-- 示例：将名为"张三"的学生的年龄更新为23  
UPDATE students  
SET age = 23  
WHERE name = '张三';

4、DELETE 语句 - 从表中删除数据

DELETE FROM table_name  
WHERE condition;  
  
-- 示例：删除所有年龄小于20的学生  
DELETE FROM students  
WHERE age < 20;

5、CREATE TABLE 语句 - 创建一个新表

CREATE TABLE table_name (  
    column1 datatype,  
    column2 datatype,  
    ...  
);  
  
-- 示例：创建一个名为"courses"的表  
CREATE TABLE courses (  
    id INT PRIMARY KEY,  
    course_name VARCHAR(255) NOT NULL,  
    teacher_name VARCHAR(255)  
);

6、ALTER TABLE 语句 - 修改表结构（例如，添加、删除或修改列）

-- 示例：向"courses"表添加一个名为"credits"的列  
ALTER TABLE courses  
ADD credits INT;  
  
-- 示例：从"courses"表中删除名为"teacher_name"的列  
ALTER TABLE courses  
DROP COLUMN teacher_name;

7、DROP TABLE 语句 - 删除表

DROP TABLE table_name;  

-- 示例：删除名为"courses"的表  
DROP TABLE courses;

8、SELECT ... ORDER BY 语句 - 对结果集进行排序

SELECT column1, column2, ...  
FROM table_name  
ORDER BY column1, column2, ... ASC|DESC;  

-- 示例：从"students"表中选择所有学生，并按年龄升序排序  
SELECT * FROM students  
ORDER BY age ASC;

9、SELECT ... GROUP BY 语句 - 结合聚合函数，按一个或多个列对结果集进行分组

SELECT column_name(s), aggregate_function(column_name)  
FROM table_name  
WHERE condition  
GROUP BY column_name(s);  
  
-- 示例：计算每个年级的学生数量  
SELECT grade, COUNT(*) as student_count  
FROM students  
GROUP BY grade;

10、SELECT ... JOIN 语句 - 从两个或多个表中检索数据

SELECT columns  
FROM table1  
JOIN table2  
ON table1.column_name = table2.column_name;  
  
-- 示例：从"students"和"courses"表中检索学生的姓名和他们所选的课程名称（假设有一个连接表）  
SELECT students.name, courses.course_name  
FROM students  
JOIN enrollment ON students.id = enrollment.student_id  
JOIN courses ON enrollment.course_id = courses.id;