数据库mysql

【数据库操作】

-- 创建数据库
create database my_database;

-- 删除数据库
drop database my_database;

-- 选择数据库
use my_database;

-- 显示所有数据库
show databases;

【表操作】

-- 创建表
create table users (
    id int primary key auto_increment,
    username varchar(50) not null,
    email varchar(100) unique,
    created_at timestamp default current_timestamp
);

-- 删除表
drop table users;

-- 显示表结构
describe users;
show columns from users;

-- 修改表结构
alter table users add age int;
alter table users drop column age;
alter table users modify column username varchar(100);
alter table users rename to customers;

【插入行】

-- 插入单行
insert into users (username, email) 
values ('john_doe', 'john@example.com');

-- 插入多行
insert into users (username, email) 
values 
    ('jane_doe', 'jane@example.com'),
    ('bob_smith', 'bob@example.com');

【去重，分组查询】

1.查询结果排序，升序，order by xx asc,降序，order by xx desc
2.查询多少条limit xx
3.去重查询，distinct，select distinct * from table
4.分组查询，group by xx,select * from table group by xx

-- 基本查询
select id, username, email from users where id > 10 order by username asc limit 5;

-- 去重查询
select distinct username from users;

-- 分组查询
select department, count(*) as employee_count 
from employees 
group by department 
having count(*) > 5;

【更新表】

update users 
set email = 'new_email@example.com' 
where username = 'john_doe';

【删除表字段】

delete from users where id = 5;

【聚合】

-- 聚合函数
select count(*) from users;
select sum(salary) from employees;
select avg(age) from customers;

COUNT()  -- 计数
SUM()    -- 求和
AVG()    -- 平均值
MAX()    -- 最大值
MIN()    -- 最小值
GROUP_CONCAT() -- 连接字符串

连表查询
【内连接】
select * from A inner join B on 2表关联

-- 查询订单及对应的客户信息
select o.order_id, o.order_date, c.customer_name
from orders o
inner join customers c on o.customer_id = c.customer_id;

-- 多表内连接
select p.product_name, c.category_name, s.supplier_name
from products p
inner join categories c on p.category_id = c.category_id
inner join suppliers s on p.supplier_id = s.supplier_id;

【左链接】
select * from A left join B on A.x=B.x where 条件

-- 查询所有客户及其订单（包括没有订单的客户）
select c.customer_name, o.order_id
from customers c
left join orders o on c.customer_id = o.customer_id;

-- 左连接配合where筛选
select c.customer_name, o.order_id
from customers c
left join orders o on c.customer_id = o.customer_id
where o.order_id is null;  -- 找出没有订单的客户

【右链接】

-- 查询所有订单及客户信息（包括没有客户信息的订单）
select o.order_id, c.customer_name
from orders o
right join customers c on o.customer_id = c.customer_id;