常见MySQL语句学习+练习

说明：本文章个人总结，用来练习学习和练习mysql语句

MySQL语句练习一

--   ========
--     查询语句的书写顺序和执行顺序
-- select ===> from ===> where ===> group by ===> having ===> order by ===> limit
--    查询语句的执行顺序
-- from ===> where ===> group by ===> having ===> select ===> order by ===> limit
create database db;

show databases db;


use test_db;  -- 选择数据库

show tables;  -- 查看当前数据库下的表

desc students;  -- 查看表结构

show create table students;  -- 查看数据库的建表结构

-- 对表的操作
create table students
(
    id      int auto_increment
        primary key,
    name    varchar(50) null,
    age     int         null,
    major   varchar(50) null,
    project varchar(10) null
);      -- 创建表

drop table if exists students; -- 删除表


-- （列）对表内容的列操作
alter table students add project varchar(5);   -- 添加

alter table students change project project varchar(10);   -- 修改

alter table students drop column project;   -- 删除字段

rename table students to  students_new;  -- 重命名表

-- （行）对表内容的行操作
    -- 添加数据insert
insert into tb_emp1 (id,username,gender,job,create_time) values(1,'小明',1,2,now());

insert into tb_emp1 values(2,'小明',1,2,now());

insert into tb_emp1 values(3,'小明',1,2,now()),(4,'小明',1,2,now());  -- 插入多行

     -- 更新数据
update tb_emp set name='张三' where id=1;
update tb_emp set entrydate='2010-01-01';

     -- 删除数据
delete from tb_emp where id=1;
delete from tb_emp ;



-- ============== 查询 ================

select * from tb_emp where name like '%张%' and gender = 1 order by update_time desc limit 10,10;

-- 基本查询
select name,entrydate from tb_emp;  -- 查询指定字段

select id,username,password,name,gender,image,job,entrydate,create_time,update_time from tb_emp;  -- (企业推荐)查询所有字段

select * from tb_emp;

select name as '姓  名' ,entrydate as '入职日期' from tb_emp; -- 查询时候设置别名

select name  '姓  名' ,entrydate  '入职日期' from tb_emp; -- 查询时候设置别名(as省略)

select distinct job from tb_emp; -- 去除重复记录的查询

-- 条件查询
            -- 1. 查询 姓名 为 杨逍 的员工
select * from tb_emp where name='杨逍';

            -- 2. 查询 id小于等于5 的员工信息
select *from tb_emp where id<=5;

            -- 3. 查询 没有分配职位 的员工信息
select * from tb_emp where job is null;

            -- 4. 查询 有职位 的员工信息
select * from tb_emp where job is not null;

            -- 5. 查询 密码不等于 '123456' 的员工信息
select * from tb_emp where password !='123456';
select * from tb_emp where password <>'123456';

            -- 6. 查询 入职日期 在 '2000-01-01' (包含) 到 '2010-01-01'(包含) 之间的员工信息
select * from tb_emp where entrydate >='2000-01-01' and entrydate<='2010-01-01';
select * from tb_emp where entrydate between '2000-01-01' and '2010-01-01';

            -- 7. 查询 入职时间 在 '2000-01-01' (包含) 到 '2010-01-01'(包含) 之间 且 性别为女 的员工信息
select * from tb_emp where entrydate between '2000-01-01' and '2010-01-01' and gender=2;

            -- 8. 查询 职位是 2 (讲师), 3 (学工主管), 4 (教研主管) 的员工信息
select * from tb_emp where job=2 or job=3 or job=4;
select * from tb_emp where job in(2,3,4);

            -- 9. 查询 姓名 为两个字的员工信息
select * from tb_emp where name like '__';

            -- 10. 查询 姓 '张' 的员工信息
select * from tb_emp where name like '张%';

-- 聚合(函数)查询
            -- 1. 统计该企业员工数量
select count(id) from tb_emp;   -- 统计的是非 NULL 的值
select count(*) from tb_emp;    -- 推荐 统计的是全部值(包括NULL)
select count('A') from tb_emp;

            -- 2. 统计该企业员工 ID 的平均值
select avg(id) from tb_emp;

            -- 3. 统计该企业最早入职的员工
select min(entrydate) from tb_emp;

            -- 4. 统计该企业最迟入职的员工
select max(entrydate) from tb_emp;

            -- 5. 统计该企业员工的 ID 之和
select sum(id) from tb_emp;


-- 分组查询
            -- 1. 根据性别分组 , 统计男性和女性员工的数量
select gender,count(*) from tb_emp group by gender;
select * from tb_emp group by gender;
            -- 3. 先查询入职时间在 '2015-01-01' (包含) 以前的员工 , 并对结果根据职位分组 , 获取员工数量大于等于2的职位
select job,count(*) from tb_emp where entrydate<='2015-01-01' group by job having count(*) >=2;




-- 排序查询
            -- 1. 根据入职时间, 对员工进行升序排序
select * from tb_emp order by entrydate asc;

select * from tb_emp order by entrydate ;
            -- 2. 根据入职时间, 对员工进行降序排序
select * from tb_emp order by entrydate desc;

            -- 3. 根据 入职时间 对公司的员工进行 升序排序 ， 入职时间相同 , 再按照 更新时间 进行降序排序
select * from tb_emp order by entrydate,update_time desc;

-- 分页查询
    -- 起始索引=(页码-1)*每页展示
            -- 1. 从起始索引0开始查询员工数据, 每页展示5条记录
select * from tb_emp limit 0,5;
select * from tb_emp limit 5;

            -- 2. 查询 第1页 员工数据, 每页展示5条记录
select * from tb_emp limit 0,5;
            -- 3. 查询 第2页 员工数据, 每页展示5条记录
select * from tb_emp limit 5,5;

            -- 4. 查询 第3页 员工数据, 每页展示5条记录
select * from tb_emp limit 10,5;

-- if(条件表达式,true取值，false取值)
select if(gender=1,'男性员工','女性员工')性别,count(*) from tb_emp group by gender;

-- case 表达式 when 值1 then 结果1 when值2 结果2 else 否则结果 end
select (case job when 1 then '班主任' when 2 then '讲师' when 3 then '学生主管' when 4 then '教研主管' else '未分配' end) 职位,
count(*)
from tb_emp
group by job;

-- year():返回年份  curdate()：返回当前时间  concat():连接字符

-- 查全体学生的姓名及其年龄
--       select sname,(year(curdate())-year(sbirthdate))from student;
-- 查询全体学生的姓名、出生日期和主修专业，输出包含 'Date of Birth:' 列
--       select sname,concat('Date of Birth:',sbirthdate),smajor from student;



-- 多变设计： 多的一方添加外键

MySQL语句练习二

use db_04;

-- A. 查询员工的姓名 , 及所属的部门名称 (隐式内连接实现)
select * from tb_dept d,tb_emp e where d.id=e.dept_id;

-- B. 查询员工的姓名 , 及所属的部门名称 (显式内连接实现)
--  select * from tb_dept d inner join tb_emp e on d.id=e.dept_id;

-- A. 查询员工表 所有 员工的姓名, 和对应的部门名称 (左外连接)
select e.name,d.name from tb_emp e left join tb_dept d on e.dept_id=d.id;

-- B. 查询部门表 所有 部门的名称, 和对应的员工名称 (右外连接)
select d.name,e.name from tb_emp e right join tb_dept d on e.dept_id=d.id;


-- ========================= 子查询 ================================
-- 标量子查询
-- A. 查询 "教研部" 的所有员工信息
select * from tb_emp where dept_id=(select id from tb_dept where name='教研部');

-- B. 查询在 "方东白" 入职之后的员工信息
select * from tb_emp where entrydate>(select entrydate from tb_emp where name='方东白');

-- 列子查询
-- A. 查询 "教研部" 和 "咨询部" 的所有员工信息
select id from tb_dept where name='教研部' or name='咨询部';

select * from tb_emp where dept_id in(select id from tb_dept where name='教研部' or name='咨询部');


-- 行子查询
-- A. 查询与 "韦一笑" 的入职日期 及 职位都相同的员工信息 ;
select entrydate,job from tb_emp where name='韦一笑';
select * from tb_emp where entrydate=(select entrydate from tb_emp where name='韦一笑') and job=(select job from tb_emp where name='韦一笑');
    -- 利用类似python元组来实现行
select * from tb_emp where (entrydate,job)=(select entrydate,job from tb_emp where name='韦一笑');


-- 表子查询
-- A. 查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息
select  * from tb_emp where entrydate>'2006-01-01';
select  dept_id from tb_emp where entrydate>'2006-01-01';


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


-- =====案例====
-- 1. 查询价格低于 10元 的菜品的名称 、价格 及其 菜品的分类名称 .
select d.name ,d.price,c.name from dish d,category c where d.category_id=c.id and d.price<10;

-- 2. 查询所有价格在 10元(含)到50元(含)之间 且 状态为'起售'的菜品名称、价格 及其 菜品的分类名称 (即使菜品没有分类 , 也需要将菜品查询出来).
--   错的： select * from dish d,category c where d.category_id=c.id and  d.price between 10 and 50 and d.status=1;

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;
-- 3. 查询每个分类下最贵的菜品, 展示出分类的名称、最贵的菜品的价格 .
select  c.name,max(d.price) from dish d,category c where d.category_id=c.id group by c.name;

-- 4. 查询各个分类下 状态为 '起售' , 并且 该分类下菜品总数量大于等于3 的 分类名称 .
select c.name,count(*) from category c,dish d where d.category_id=c.id and d.status=1 group by c.name having count(*)>=3;

-- 5. 查询出 "商务套餐A" 中包含了哪些菜品 （展示出套餐名称、价格, 包含的菜品名称、价格、份数）.
select * from setmeal s
                  left join setmeal_dish sd on s.id = sd.setmeal_id
                  left join dish d on sd.dish_id = d.id
where s.name = '商务套餐A';


-- 6. 查询出低于菜品平均价格的菜品信息 (展示出菜品名称、菜品价格).
select avg(price) from dish;
select * from dish where price<(select avg(price) from dish);



-- ======= 索引 ==========
        -- 提高查询效率
-- 创建 : 为tb_emp表的name字段建立一个索引 .
create index idx_tmp_name on tb_emp(name);

-- 查询 : 查询 tb_emp 表的索引信息 .
show index from tb_emp;

-- 删除: 删除 tb_emp 表中name字段的索引 .
drop  index idx_tmp_name on tb_emp;  

难点语句解答