-- 创建表
create table users (
id int auto_increment primary key,
username varchar(50) not null,
email varchar(100) unique
);
-- 修改表结构
alter table users
add column last_login datetime;
-- 删除表
drop table if exists temp_data;
-- 清空表数据
truncate table audit_log;
DML(数据操作语句)
-- 插入数据
insert into users (username, email)
values ('john_doe', 'john@example.com');
-- 批量插入
insert into products (name, price)
values ('product1', 10.99), ('product2', 20.99);
-- 更新数据
update users set last_login = now()
where id = 1001;
-- 删除数据
delete from inactive_users
where last_login < date_sub(now(), interval 1 year);
DQL(数据查询语句)
-- 基础查询
select * from users where status = 'active';
-- 连接查询
select u.username, o.order_id
from users u
join orders o on u.id = o.user_id;
-- 聚合查询
select user_id, count(*) as order_count
from orders
group by user_id;
-- 子查询
select name from products
where id in (select product_id from order_items);
DCL(数据控制语句)
-- 授予权限
grant select, insert on db_name.users to 'user1'@'localhost';
-- 撤销权限
revoke delete on db_name.orders from 'user2'@'%';
-- 查看权限
show grants for 'user1'@'localhost';
TCL(事务控制语句)
-- 事务示例
start transaction;
update accounts set balance = balance - 100 where id = 1;
update accounts set balance = balance + 100 where id = 2;
commit;
-- 回滚事务
begin;
delete from important_data;
rollback;
-- 保存点
start transaction;
insert into log values (1, 'operation1');
savepoint sp1;
insert into log values (2, 'operation2');
rollback to sp1; -- 只回滚到保存点
commit;
