MySQL基础增删查改操作详解

create database db_name;
create database if not exists db_name;  -- 不存在时才创建
create database db_name character set utf8mb4 collate utf8mb4_unicode_ci;

drop database db_name;
drop database if exists db_name;  -- 存在时才删除

use db_name;

create table users (
    id int primary key auto_increment,        -- 主键，自增
    username varchar(50) not null unique,     -- 非空，唯一
    password varchar(255) not null,
    email varchar(100),
    age int default 18,                       -- 默认值
    created_at timestamp default current_timestamp,
    updated_at timestamp default current_timestamp on update current_timestamp
);

drop table table_name;
drop table if exists table_name;

-- 添加列
alter table users add column phone varchar(20);
-- 修改列
alter table users modify column email varchar(150);
-- 重命名列
alter table users change column phone mobile varchar(20);
-- 删除列
alter table users drop column mobile;
-- 添加索引
create index idx_username on users(username);
-- 删除索引
drop index idx_username on users;

-- 插入单条数据（指定列）
insert into users (username, password, email, age) 
values ('john_doe', '123456', 'john@example.com', 25);
-- 插入单条数据（所有列）
insert into users values (null, 'jane_doe', '654321', 'jane@example.com', 30, now(), now());
-- 插入多条数据
insert into users (username, password, email) 
values 
    ('alice', 'pass123', 'alice@example.com'),
    ('bob', 'pass456', 'bob@example.com'),
    ('charlie', 'pass789', 'charlie@example.com');

-- 查询所有列
select * from users;
-- 查询指定列
select id, username, email from users;
-- 条件查询
select * from users where age > 20;
select * from users where username = 'john_doe';
select * from users where age between 20 and 30;
select * from users where email like '%@example.com';
-- 逻辑运算符
select * from users where age > 20 and email like '%@example.com';
select * from users where age < 18 or age > 60;
-- 排序
select * from users order by age desc;        -- 降序
select * from users order by created_at asc;  -- 升序
-- 限制返回数量
select * from users limit 10;                 -- 前10条
select * from users limit 5, 10;              -- 跳过5条，取10条
-- 去重
select distinct age from users;
-- 分组统计
select age, count(*) as count from users group by age;
select age, count(*) from users group by age having count(*) > 1;
-- 聚合函数
select count(*) as total_users from users;
select avg(age) as avg_age from users;
select max(age) as max_age from users;
select min(age) as min_age from users;
select sum(age) as total_age from users;

-- 更新单个字段
update users set email = 'new_email@example.com' where id = 1;
-- 更新多个字段
update users set 
    email = 'updated@example.com',
    age = 26,
    updated_at = now()
where username = 'john_doe';
-- 基于现有值更新
update users set age = age + 1 where age < 30;
-- 批量更新
update users set status = 'inactive' where last_login < '2024-01-01';

-- 删除指定记录
delete from users where id = 1;
-- 删除多条记录
delete from users where age > 100;
-- 清空表（删除所有记录）
delete from users;
truncate table users;  -- 更快，不能回滚

-- 内连接
select u.username, o.order_id, o.amount
from users u
inner join orders o on u.id = o.user_id;
-- 左连接
select u.username, o.order_id
from users u
left join orders o on u.id = o.user_id;
-- 右连接
select u.username, o.order_id
from users u
right join orders o on u.id = o.user_id;

-- where子句中的子查询
select * from users 
where age > (select avg(age) from users);
-- from子句中的子查询
select avg_age_table.age_group, count(*)
from (select 
    case 
        when age < 20 then '少年'
        when age between 20 and 40 then '青年'
        else '中老年'
    end as age_group
    from users) as avg_age_table
group by age_group;

select username, email from active_users
union
select username, email from inactive_users;

-- 开始事务
start transaction;
-- 执行操作
update accounts set balance = balance - 100 where user_id = 1;
update accounts set balance = balance + 100 where user_id = 2;
-- 提交或回滚
commit;  -- 确认更改
-- 或
rollback;  -- 撤销更改

-- 创建表备份
create table users_backup as select * from users;
-- 插入备份数据
insert into users_backup select * from users where created_at > '2024-01-01';

-- 批量插入（提高性能）
insert into users (username, email) values 
('user1', 'user1@example.com'),
('user2', 'user2@example.com'),
('user3', 'user3@example.com');

select 
    username,
    age,
    case 
        when age < 18 then '未成年'
        when age between 18 and 60 then '成年'
        else '老年'
    end as age_group
from users;