-- 創(chuàng)建數據庫
CREATE DATABASE school;

-- 創(chuàng)建表
CREATE TABLE students (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  age INT CHECK (age > 0),
  class_id INT,
  FOREIGN KEY (class_id) REFERENCES classes(id)
);

-- 創(chuàng)建索引
CREATE INDEX idx_name ON students(name);

-- 創(chuàng)建視圖
CREATE VIEW student_view AS
SELECT id, name FROM students WHERE age > 10;

-- 添加列
ALTER TABLE students ADD COLUMN gender CHAR(1);

-- 修改列類型
ALTER TABLE students MODIFY COLUMN name VARCHAR(100);

-- 刪除列
ALTER TABLE students DROP COLUMN gender;

DROP TABLE IF EXISTS temp_students;
DROP VIEW student_view;

TRUNCATE TABLE log_data;

-- 插入單條記錄
INSERT INTO students (name, age, class_id)
VALUES ('張三', 15, 1);

-- 插入多條記錄
INSERT INTO students (name, age, class_id)
VALUES ('李四', 16, 1), 
       ('王五', 14, 2);

-- 從其他表插入數據
INSERT INTO graduate_students
SELECT * FROM students WHERE age > 18;

-- 更新單列
UPDATE students SET age = 16 WHERE name = '張三';

-- 更新多列
UPDATE students 
SET age = age + 1, class_id = 3
WHERE id = 5;

-- 使用子查詢更新
UPDATE students
SET class_id = (SELECT id FROM classes WHERE name = '高三')
WHERE age > 17;

-- 刪除特定記錄
DELETE FROM students WHERE id = 10;

-- 刪除所有記錄
DELETE FROM temp_students;

-- 使用子查詢刪除
DELETE FROM students
WHERE class_id IN (SELECT id FROM classes WHERE grade = '畢業(yè)班');

-- MySQL語法
INSERT INTO students (id, name, age)
VALUES (1, '張三', 15)
ON DUPLICATE KEY UPDATE age = 16;

-- PostgreSQL語法
INSERT INTO students (id, name, age)
VALUES (1, '張三', 15)
ON CONFLICT (id) DO UPDATE SET age = 16;

-- 基本查詢
SELECT * FROM students;

-- 條件查詢
SELECT name, age FROM students WHERE age > 15;

-- 排序
SELECT * FROM students ORDER BY age DESC, name ASC;

-- 分組聚合
SELECT class_id, COUNT(*) as student_count, AVG(age) as avg_age
FROM students
GROUP BY class_id
HAVING COUNT(*) > 5;

-- 連接查詢
SELECT s.name, c.class_name
FROM students s
JOIN classes c ON s.class_id = c.id;

-- 子查詢
SELECT name FROM students
WHERE class_id IN (SELECT id FROM classes WHERE grade = '高一');

-- 分頁查詢
SELECT * FROM students LIMIT 10 OFFSET 20;  -- MySQL
SELECT * FROM students OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;  -- SQL標準

WITH top_students AS (
  SELECT * FROM students ORDER BY score DESC LIMIT 10
)
SELECT * FROM top_students WHERE gender = 'F';

-- 授予SELECT權限
GRANT SELECT ON students TO user1;

-- 授予所有權限
GRANT ALL PRIVILEGES ON database.* TO 'admin'@'localhost';

-- 授予特定列權限
GRANT SELECT (name, age), UPDATE (age) ON students TO teacher_role;

-- 撤銷權限
REVOKE INSERT ON students FROM user2;

-- 撤銷所有權限
REVOKE ALL PRIVILEGES ON database.* FROM 'old_admin'@'localhost';

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

BEGIN TRANSACTION;
DELETE FROM orders WHERE status = 'pending';
-- 發(fā)現錯誤
ROLLBACK;

BEGIN TRANSACTION;
INSERT INTO log (message) VALUES ('Operation started');
SAVEPOINT sp1;
UPDATE data SET value = 10 WHERE id = 1;
-- 部分回滾
ROLLBACK TO SAVEPOINT sp1;
COMMIT;

-- DDL: 創(chuàng)建表結構
CREATE TABLE classes (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  grade VARCHAR(20)
) ENGINE=InnoDB;

-- DML: 插入班級數據
INSERT INTO classes (name, grade) VALUES 
('一班', '高一'), ('二班', '高一'), ('三班', '高二');

-- DQL: 查詢班級信息
SELECT * FROM classes WHERE grade = '高一';

-- DML: 更新班級信息
UPDATE classes SET grade = '高三' WHERE name = '三班';

-- DCL: 創(chuàng)建用戶并授權
CREATE USER 'teacher'@'%' IDENTIFIED BY 'password';
GRANT SELECT, UPDATE ON school.students TO 'teacher'@'%';
GRANT SELECT ON school.classes TO 'teacher'@'%';

-- DDL: 添加索引提高查詢性能
CREATE INDEX idx_class_grade ON classes(grade);

-- 事務處理示例 (DCL)
BEGIN TRANSACTION;
  -- DML: 轉班操作
  UPDATE students SET class_id = 2 WHERE id = 101;
  UPDATE class_stats SET student_count = student_count - 1 WHERE class_id = 1;
  UPDATE class_stats SET student_count = student_count + 1 WHERE class_id = 2;
COMMIT;