常见Mysql数据库操作语句

-- DDL创建数据库结构
-- 查询所有数据库
show databases ;
-- 修改数据库字符集
alter database db02 charset utf8mb4;
-- 创建字符编码为utf——8的数据库
create database db05 DEFAULT CHARACTER SET utf8;


-- 创建表格
create table tb_user(
  id int auto_increment primary key comment 'ID,唯一标识',
  username varchar(20) not null unique comment '用户名',
  name varchar(20) not null  comment '姓名',
  age int comment '年龄',
  gender varchar(20)  default '男' comment '性别'
)comment '用户表';

-- 创建表格2
create table tb_emp
(
    id          int auto_increment comment 'ID主键'
        primary key,
    create_time datetime                     not null comment '创建时间',
    update_time datetime                     not null comment '更新时间',
    username    varchar(20)                  not null comment '用户名',
    password    varchar(32) default '123456' null comment '密码',
    name        varchar(10)                  not null comment '员工姓名',
    gender      tinyint unsigned             not null comment '1男 2女',
    image       varchar(300)                 null comment '图像',
    job         tinyint unsigned             null comment '1班主任 2讲师 3学工主管 4教研主管',
    entrydate   date                         null comment '入职日期',
    constraint tb_emp_username_uindex
        unique (username)
)
    comment '员工表';

-- 查询表结构
desc tb_emp;

-- 删除表
drop table tb_emp;

-- DML数据操作语言
insert into tb_emp(id, create_time, update_time, username, password, name, gender, image, job, entrydate) values
(null,now(),now(),'zhouzhiruo3','123','周芷若',2,'1.jpg',2,'2023-12-28');

insert into tb_emp values (null,now(),now(),'zhangwuji2','123','张无忌',1,'2.jpg',1,'2023-12-28');

insert into tb_emp (id, create_time, update_time, username, name, gender) values (null,now(),now(),'weiyixiao1','韦一笑',2);

insert into tb_emp (create_time, update_time,username, name, gender) values (now(),now(),'weiyixiao3','韦一笑',2);

update tb_emp set name='张三',update_time=now() where id=1;

update tb_emp set entrydate='2024-1-1',update_time=now() ;

delete from tb_emp where id=10;

-- DQL数据查询语言
select id, create_time, update_time, username, password, name, gender, image, job, entrydate from tb_emp ;
-- 别名
select username as '用户名' ,job  '职位'  ,update_time 更新时间 from tb_emp;
-- 去重
select distinct job  from tb_emp;
-- 条件查询
select * from tb_emp where entrydate between '2023-12-28' and '2023-12-29';

select * from tb_emp where name like '张_';

select * from tb_emp where job in (1,3);

select * from tb_emp where image is not null ;
-- 聚合函数
select count('7') from tb_emp;

-- 分组查询
select gender,count(*) from tb_emp group by gender having count(*)>3;

select job,count(*) from tb_emp where entrydate<'2024-01-01'group by job having job=2;

select * from tb_emp where username='zhouzhiruo3';

-- 排序查询
select * from tb_emp order by entrydate desc ,job;

-- 分页查询
select * from tb_emp limit 5;
select * from tb_emp limit 0,5;

select * from tb_emp limit 5,5;

-- MYSQL控制函数 if 和case

select if(gender=1,'男性','女性') 性别,count(*) 数量 from tb_emp group by gender;

select (case job when 1 then '班主任' when 2 then '学生' when 3 then '助教' else '其他' end) 职位 ,count(*)from tb_emp group by job;
select (case job when 1 then '班主任' when 2 then '学生' when 3 then '助教' else '其他' end) 职位 from tb_emp;


-- 案例创建category分类表
-- 多表查询

select * from tb_emp,tb_dept;
select * from tb_emp;

-- 隐式内连接
select * from tb_emp,tb_dept where tb_emp.dept_id=tb_dept.id;

select tb_emp.name,tb_dept.deptname from tb_emp,tb_dept where tb_emp.dept_id=tb_dept.id;

-- 显式内连接
select tb_emp.name,tb_dept.deptname from tb_emp inner join tb_dept on tb_emp.dept_id = tb_dept.id;
select e.name,d.deptname from tb_emp e join tb_dept d on e.dept_id = d.id;
-- 左外链接

select e.name,d.deptname from tb_emp e left join tb_dept d on e.dept_id = d.id

-- 右外链接
select e.name,d.deptname from tb_emp e right join tb_dept d on e.dept_id = d.id;

-- 标量子查询
-- 查询教研部门下的员工
select id from tb_dept where tb_dept.deptname='教研部门';

select * from tb_emp where tb_emp.dept_id = 2;

select * from tb_emp e where e.dept_id = (select id from tb_dept d where d.deptname='教研部门');

-- 查看房东白后入职的员工信息
select entrydate from tb_emp e where e.name='方东白';
select entrydate from tb_emp e where entrydate >'2021-01-04';

select entrydate from tb_emp e where entrydate >(select entrydate from tb_emp e where e.name='方东白');
-- 列子查询
-- 查询教研部门和后勤部门的员工信息

select id from tb_dept where deptname in('教研部门','后勤部门');

select * from tb_emp where tb_emp.dept_id in(2,3);

select * from tb_emp where tb_emp.dept_id in(select id from tb_dept where deptname in('教研部门','后勤部门'));

-- 行子查询
-- 查询和风不吹相同入职时间和职位的员工信息。
select entrydate,job from tb_emp where name ='风不吹';

select * from tb_emp where entrydate = '2025-01-04' and job  = 7 ;

select * from tb_emp where (entrydate,job) = (select entrydate,job from tb_emp where name ='风不吹');

-- 表子查询
-- 查询2024-01-04号后的员工信息，及部门名称
select * from tb_emp where entrydate >'2024-01-04' ;

select e.*,d.deptname from (select * from tb_emp where entrydate >'2024-01-04') e,tb_dept d where e.dept_id= d.id;

-- 分类表
create table category
(
    id          int unsigned auto_increment comment '主键ID'
        primary key,
    name        varchar(20)                not null comment '分类名称',
    type        tinyint unsigned           not null comment '分类类型：1菜品分类 2套餐分类',
    sort        tinyint unsigned           not null comment '排序字段',
    status      tinyint unsigned default 0 not null comment '状态：0停售 1启售',
    create_time datetime                   not null comment '创建时间',
    update_time datetime                   not null comment '更新时间',
    constraint category_name_uindex
        unique (name)
)
    comment '分类表';
-- 菜品表
create table dish
(
    id           int unsigned auto_increment comment '主键id'
        primary key,
    name         varchar(20)                not null comment '菜品名称',
    category_id  int unsigned               not null comment '菜品分类',
    price        decimal(8, 2)              not null comment '价格',
    image        varchar(300)               not null comment '图片',
    describetion varchar(200)               null comment '描述',
    status       tinyint unsigned default 0 not null comment '状态 0停售 1起售',
    create_time  datetime                   not null comment '创建时间',
    update_time  datetime                   not null comment '修改时间',
    constraint dish_name_uindex
        unique (name)
)
    comment '菜品表';

-- 套餐表
create table setmeal
(
    id          int unsigned auto_increment comment '主键id'
        primary key,
    name        varchar(20)                not null comment '套餐名称',
    category_id int unsigned               not null comment '套餐分类id',
    price       decimal(8, 2)              not null comment '价格',
    image       varchar(300)               not null comment '图片',
    description varchar(200)               null comment '描述信息',
    status      tinyint unsigned default 0 not null comment '状态：0停售 1启售',
    create_time datetime                   not null comment '创建时间',
    update_time datetime                   not null comment '修改时间',
    constraint setmeal_name_uindex
        unique (name)
)
    comment '套餐表';
-- 套餐菜品表
create table setmeal_dish
(
    id         int unsigned auto_increment comment '主键ID'
        primary key,
    setmeal_id int unsigned     not null comment '套餐ID',
    dish_id    int unsigned     not null comment '菜品ID',
    copies     tinyint unsigned not null comment '菜品的份数'
)
    comment '套餐菜品关系表';

-- 查询价格低于10元的，菜品名称价格和及菜品的分类名称

select d.name,d.price,c.name from category c,dish d where c.id=d.category_id and d.price<10  ;
-- 查询所有价格在10-50（包含）之间的菜品且状态为‘起售’的名称，价格，及分类名称（即使菜品没有分类，也要查询出来）

select d.name,d.price,c.name , d.status from dish d left join category c on d.category_id = c.id  where  d.status=1  and  d.price between 10 and 50  ;
select d.name,d.price,c.name from dish d left join category c on d.category_id = c.id  where  d.price between 10 and 50 and d.status=1 ;

-- 查询每个分类下，最贵的菜品，展示出分类的名称，最贵的菜品价格；
select c.name,d.name,max(d.price) from dish d ,category c where d.category_id=c.id group by c.id ;

-- 查询每个分类下，菜品状态为‘起售’，并且该分类下菜品的数量大于等于2的分类名称

select c.name,count(*) from category c,dish d where c.id = d.category_id and d.status=1 group by c.name having count(*)>=2;


-- 查询经典川菜中包含那些菜品，套餐的名称价格，菜品的名称价格和份数。

select d.name,s.name,s.price,d.name,sd.copies from dish d,setmeal s,setmeal_dish sd where s.id=sd.setmeal_id and d.id=sd.dish_id and s.name='经典川菜';

-- 查询低于菜品平均价格的菜品信息，菜品名称，菜品价格
select avg(price)
from dish;

select name,price from dish where price<(select avg(price) from dish);

-- 事务
-- 开启事务
start transaction ;
delete  from tb_dept where id=1;
delete  from tb_emp where dept_id=1;
-- 提交事务
commit;
-- 回滚事务
rollback;

select * from tb_emp;
select * from tb_dept;

-- 创建索引
create index idx_emp_name on tb_emp(name);

-- 查看索引
show index  from tb_emp;

-- 删除索引
drop index idx_emp_name on tb_emp;