SQL 四大语言分类中DDL、DML、DCL、DQL的使用

-- 创建数据库
create database school;

-- 创建表
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)
);

-- 创建索引
create index idx_name on students(name);

-- 创建视图
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 = '毕业班');

-- 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';
-- 发现错误
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: 创建表结构
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: 创建用户并授权
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;