MySQL常用SQL语句和使用场景代码示例

create database mydb character set utf8mb4 collate utf8mb4_unicode_ci;

show databases;

use mydb;

drop database mydb;

alter database mydb character set utf8mb4;

create table users (
    id int auto_increment primary key,
    username varchar(50) not null unique,
    email varchar(100) not null,
    password varchar(255) not null,
    age int default 18,
    status tinyint default 1 comment '1:active 0:inactive',
    created_at timestamp default current_timestamp,
    updated_at timestamp default current_timestamp on update current_timestamp
) engine=innodb default charset=utf8mb4;

desc users;
-- 或
describe users;
-- 或
show columns from users;

show tables;

-- 添加列
alter table users add column phone varchar(20);
​
-- 删除列
alter table users drop column phone;
​
-- 修改列类型
alter table users modify column username varchar(100);
​
-- 重命名列
alter table users change column username user_name varchar(50);
​
-- 添加索引
alter table users add index idx_email (email);
​
-- 添加外键
alter table orders add constraint fk_user_id
    foreign key (user_id) references users(id);

drop table users;
-- 或
drop table if exists users;

truncate table users;

rename table old_name to new_name;

-- 插入单条记录
insert into users (username, email, password) values ('zhangsan', 'zhangsan@example.com', '123456');
​
-- 插入多条记录
insert into users (username, email, password) values
    ('lisi', 'lisi@example.com', '123456'),
    ('wangwu', 'wangwu@example.com', '123456');
​
-- 从另一表插入数据
insert into users_backup select * from users;

-- 更新单个字段
update users set email = 'newemail@example.com' where id = 1;
​
-- 更新多个字段
update users set email = 'new@example.com', status = 0 where id = 1;
​
-- 批量更新
update users set status = 0 where age < 18;

-- 删除指定记录
delete from users where id = 1;
​
-- 批量删除
delete from users where status = 0;
​
-- 清空表（delete方式，会重置自增id）
delete from users;

-- 查询所有列
select * from users;
​
-- 查询指定列
select id, username, email from users;
​
-- 去重查询
select distinct status from users;

-- 等于
select * from users where id = 1;
​
-- 不等于
select * from users where status != 0;
​
-- 多条件 and
select * from users where age >= 18 and age <= 60;
​
-- 多条件 or
select * from users where age < 18 or age > 60;
​
-- in 查询
select * from users where id in (1, 2, 3);
​
-- not in 查询
select * from users where id not in (1, 2, 3);
​
-- between 范围查询
select * from users where age between 18 and 60;
​
-- like 模糊查询
select * from users where username like 'zhang%';  -- 以zhang开头
select * from users where username like '%san';     -- 以san结尾
select * from users where username like '%an%';     -- 包含an
select * from users where username like 'zh_ng';    -- _匹配单个字符
​
-- is null 查询
select * from users where phone is null;
​
-- is not null 查询
select * from users where phone is not null;

-- 升序
select * from users order by age asc;
​
-- 降序
select * from users order by created_at desc;
​
-- 多字段排序
select * from users order by status asc, age desc;

-- 查询前n条
select * from users limit 10;
​
-- 分页查询（跳过m条，取n条）
select * from users limit 0, 10;   -- 第1页，每页10条
select * from users limit 10, 10;  -- 第2页，每页10条

-- 按状态分组统计
select status, count(*) as count from users group by status;
​
-- 多字段分组
select status, age, count(*) as count from users group by status, age;
​
-- 分组后过滤（having）
select status, count(*) as count from users
group by status
having count > 5;

-- count 统计数量
select count(*) from users;
select count(distinct email) from users;

-- sum 求和
select sum(amount) from orders;

-- avg 平均值
select avg(age) from users;

-- max 最大值
select max(price) from products;

-- min 最小值
select min(price) from products;

-- 组合使用
select
    count(*) as total,
    avg(age) as avg_age,
    max(age) as max_age,
    min(age) as min_age
from users;

-- 只返回匹配的记录
select u.username, o.order_no, o.amount
from users u
inner join orders o on u.id = o.user_id;

-- 返回左表所有记录，右表没有匹配则为null
select u.username, o.order_no
from users u
left join orders o on u.id = o.user_id;

-- 返回右表所有记录，左表没有匹配则为null
select u.username, o.order_no
from users u
right join orders o on u.id = o.user_id;

select u.username, o.order_no, p.product_name
from users u
inner join orders o on u.id = o.user_id
inner join order_items oi on o.id = oi.order_id
inner join products p on oi.product_id = p.id;

-- 普通索引
create index idx_username on users(username);

-- 唯一索引
create unique index idx_email on users(email);

-- 组合索引
create index idx_status_age on users(status, age);

-- 全文索引
create fulltext index idx_content on articles(content);

show index from users;

drop index idx_username on users;

create view user_orders as
select u.id, u.username, count(o.id) as order_count
from users u
left join orders o on u.id = o.user_id
group by u.id, u.username;

select * from user_orders;

drop view user_orders;

-- 开启事务
start transaction;
-- 或
begin;

-- 执行sql
update users set balance = balance - 100 where id = 1;
update users set balance = balance + 100 where id = 2;

-- 提交事务
commit;

-- 回滚事务
rollback;

create user 'testuser'@'localhost' identified by 'password123';
create user 'testuser'@'%' identified by 'password123';  -- 允许远程连接

-- 授予所有权限
grant all privileges on mydb.* to 'testuser'@'localhost';

-- 授予特定权限
grant select, insert, update on mydb.* to 'testuser'@'localhost';

-- 刷新权限
flush privileges;

show grants for 'testuser'@'localhost';

revoke all privileges on mydb.* from 'testuser'@'localhost';

drop user 'testuser'@'localhost';

alter user 'testuser'@'localhost' identified by 'newpassword';

-- 注册（插入用户）
insert into users (username, email, password) values ('newuser', 'user@example.com', md5('password'));

-- 登录验证
select * from users where username = 'newuser' and password = md5('password');

-- 通用分页公式：limit (page-1)*pagesize, pagesize
select * from users order by created_at desc limit 0, 10;   -- 第1页
select * from users order by created_at desc limit 10, 10;  -- 第2页

-- 每日新增用户统计
select date(created_at) as date, count(*) as new_users
from users
group by date(created_at)
order by date desc;

-- 各状态用户数量
select
    case status
        when 1 then '活跃'
        when 0 then '禁用'
        else '未知'
    end as status_name,
    count(*) as count
from users
group by status;

-- 用户消费排行top10
select u.username, sum(o.amount) as total_amount
from users u
inner join orders o on u.id = o.user_id
group by u.id, u.username
order by total_amount desc
limit 10;

-- 查找重复的邮箱
select email, count(*) as count
from users
group by email
having count(*) > 1;

-- 删除重复数据（保留id最小的）
delete u1 from users u1
inner join users u2
where u1.id > u2.id and u1.email = u2.email;

-- 导出数据到csv文件（命令行）
mysql -u root -p -e "select * from users" mydb > users.csv;

-- 导入csv文件
load data infile '/path/to/users.csv'
into table users
fields terminated by ','
enclosed by '"'
lines terminated by '\n';

-- case when 批量更新
update users
set status = case
    when age < 18 then 0
    when age >= 18 and age < 60 then 1
    else 2
end;

-- 查询最近7天的数据
select * from orders
where created_at >= date_sub(now(), interval 7 day);

-- 查询本月数据
select * from orders
where year(created_at) = year(now())
and month(created_at) = month(now());

-- 查询指定时间段
select * from orders
where created_at between '2024-01-01' and '2024-12-31';

-- 假设categories表有id, name, parent_id字段
-- 查找某分类的所有子分类（递归，mysql 8.0+）
with recursive category_tree as (
    select * from categories where id = 1
    union all
    select c.* from categories c
    inner join category_tree ct on c.parent_id = ct.id
)
select * from category_tree;

-- 软删除（添加is_deleted字段）
update users set is_deleted = 1, deleted_at = now() where id = 1;
-- 查询未删除的数据
select * from users where is_deleted = 0;
-- 恢复数据
update users set is_deleted = 0, deleted_at = null where id = 1;

concat(str1, str2)      -- 连接字符串
length(str)             -- 字符串长度
upper(str)              -- 转大写
lower(str)              -- 转小写
trim(str)               -- 去除首尾空格
substring(str, pos, len) -- 截取子串
replace(str, old, new)  -- 替换

now()                   -- 当前日期时间
curdate()               -- 当前日期
curtime()               -- 当前时间
date(datetime)          -- 提取日期部分
year(date)              -- 提取年份
month(date)             -- 提取月份
day(date)               -- 提提取日
date_format(date, format) -- 日期格式化
date_add(date, interval expr unit) -- 日期增加
date_sub(date, interval expr unit) -- 日期减少

abs(num)                -- 绝对值
round(num, decimals)    -- 四舍五入
ceil(num)               -- 向上取整
floor(num)              -- 向下取整
rand()                  -- 随机数