DQL语句

---

查询语句的执行流程

1. 首先执行为的为是from后的数据源（也就是各类表）
2. 然后是where语句后的指定条件对记录进行筛选
3. 然后是group by 将数据进行分组
4. 在进行聚集函数的计算
5. 然后是having对分组数据进行筛选
6. 计算表达式
7. select
8. distinct
9. order by排序
   每个步骤都会产生一个虚表成为下一流程的输入

查询注入问题

当我们使用表单提交用户名和密码时，用户使用 “aa” or ‘a’=‘a’
和我们后端的查询语句拼接时
select * from user where username= “aa” or ‘a’=‘a’ 是一定会为true
所以要使用预编译来防止注入：
select * from user where username=? 提前使用占位符进行站位 ，当 “aa” or ‘a’=‘a’ 放过来时会当成一个整体进行执行，表示这一串为一个字符串 ，就变成了
select * from user where username=’ “aa” or ‘a’=‘a’ ',就不会成功。
也可以使用mybatis中的#来进行防止。

建表语句

-- auto-generated definition
create table dept
(
    id   int         not null comment 'id'
        primary key,
    name varchar(50) not null comment '部门名称'
)
    comment '部门表';
-- auto-generated definition
create table emp
(
    id        int auto_increment comment 'ID'
        primary key,
    name      varchar(50) not null comment '姓名',
    age       tinyint     null comment '年龄',
    job       varchar(20) null comment '职位',
    salary    int         null comment '薪资',
    entrydate date        null comment '入职时间',
    managerid int         null comment '直属领导ID',
    dept_id   int         null comment '部门ID'
)
    comment '员工表';

-- auto-generated definition
create table salgrade
(
    id    int auto_increment
        primary key,
    grade int null,
    losal int null,
    hisal int null
)
    comment '薪资等级表';

-- auto-generated definition
create table score
(
    id      int         null comment 'ID',
    name    varchar(20) null comment '姓名',
    math    int         null comment '数学',
    english int         null comment '英语',
    chinese int         null comment '语文'
)
    comment '学员成绩表';

---

一、基本查询语句

/*
    基本查询语句
 */
select name, age, job
from emp;
select *
from emp;
#as 别名 可省略
desc emp;
select name as '姓名', job as '工作'
from emp;
select name '姓名', job '工作'
from emp;
#distinct 去重
select distinct job
from emp;

二、条件语句

/*
    条件查询语句
 */
select *
from emp
where name = '杨逍';
select *
from emp
where age < 20;
select *
from emp
where age <= 20;
# 查询null 不能使用= 用 is null
select *
from emp
where job is null;

select *
from emp
where job is not null;
select *
from emp
where age != 60;
select *
from emp
where age <> 60;
#也可以使用 between 15 and 25(两边都包含) 或者 &&
select *
from emp
where age >= 15
  and age < 25;
select *
from emp
where salary >= 6000
  and age <= 25;
#也可使用 age in(19,20,60)
select *
from emp
where age = 19
   or age = 20
   or age = 60;
#like模糊匹配 _代表单个字符 %代表任意字符
select *
from emp
where name like '__';
select *
from emp
where name like '张%';
select *
from emp
where name like '%东%';

三.分组语句

/*
    分组查询
 */
#查询返回的字段一般为聚和函数和分组字段
select COUNT(*) as '总人数', job
from emp
group by job;
select avg(age), job
from emp
group by job;
/*
    where 和 having的区别
        where是分组之前进行过滤，having 是对分组之后进行过滤
        where不能对聚合函数进行判断，having可以
    执行顺序：where>聚合>having
 */
select count(*), job
from emp
where age < 45
group by job
having count(*) >= 2;

四.排序查询

/*
    排序查询
 */
#默认升序排序
select *
from emp
order by age asc;
#降序排序
select *
from emp
order by entrydate desc;
#多种排序规则用,号分开 当第一种排序条件相同时按照第二排序条件排序
select *
from emp
order by age asc, entrydate desc;

五.分页查询

/*
    分页查询
    limit 起始索引 ，展示条数
    limit是mysql的方言
 */
select *
from emp
limit 0,10;
# 默认从第0行开始
select *
from emp
limit 10;

六.多表查询

/*
    多表查询：

            内连接 ：查询公共部分
            左连接 ：查询左表和公共部分
            右连接 ：查询右表和公共部分
            自连接 ：与自生进行连接查询
 */
#会出现两表相乘的结果数 （称为笛卡尔积）
select * from score,info;
#解决(消除无效的笛卡尔积）
select * from emp,dept where emp.dept_id=dept.id;
/*
    内连接：
        隐私内连接：
            通过where语句中进行关联
        显示内连接：
          inner(inner可省略 ) join  on
    外连接；
        左外连接：
           outer(outer可省略) left join
        右外连接：
            outer（outer可省略）right join
    自连接：
        自己连接自己

 */
#内连接 隐私连接
select emp.name,dept.name  from emp,dept where dept_id=dept.id;
#显示内连接
select emp.name,dept.name from emp inner join dept on emp.dept_id = dept.id;
#左外连接
select * from emp left join dept d on d.id = emp.dept_id;
#右外连接
select * from emp right join dept d on d.id = emp.dept_id;
#自连接
select e.name ,e1.name from emp e left join emp e1 on e.managerid=e1.id;

七.子查询

/*
    子查询：
      标量子查询：子查询的结果为一行一列
                常用判断> = <  <>
        列子查询：
                常用判断 in not in
        行子查询：
                常用判断
                上面的全部
        表子查询：
                吧其中一个查询结果当做一个临时表，在进行内连接


 */
#标量子查询
select * from emp where dept_id =(select id from dept where dept.name='销售部');
select * from  emp where entrydate>(select entrydate from emp where name='方东白');
#列子查询
select * from emp where dept_id in (select id from dept where dept.name in ('销售部','市场部'));
#行子查询 有多个结果的时候，可以通过括号相等进行比对
select * from emp where (salary,job) =(select salary,job from emp where name='鹿杖客');
#表子查询
select * from dept , (select * from emp where entrydate>'2006-01-01') s where dept_id=dept.id;

函数

/*
    函数:
        字符串函数
        数值函数
        日期函数
        流程控制函数

 */
/*
       字符串函数
 */
#长度
select char_length(name) as len, name
from emp
having len = 3;
#拼接
select concat('1', '2', '3');
#字符串拼接带分隔符
select concat_ws('-', '1', '2', '3');
#left
select left('hello_world', 2);
#right
select right('hello_world', 2);
#substring
select substring('hello_world', 1, 2);
/*
        数值函数
 */
#向上取整
select ceil(1.1);
#向下取整
select floor(2.1);
#返回0-1的随机数
select rand();
#四舍五入 保留两位有效小数
select round(1.954, 2);
/*
    日期函数
 */
#当前日期和时间
select now();
#当前日期
select curdate();
#当前时间
select curtime();
#年月日
select year(now()), month(now()), day(now());
#两个日期相差的天数
select datediff(curdate(), '2000-11-1');
/*
    流程控制函数
 */
# if(value,1,2 )  value为true 1 false 2
select name, if(age > 60, '老年人', '年轻人')
from emp;

#ifnull(1,2) 1是null 为2
select name, ifnull(job, '未分配')
from emp;

# case when 条件一 then  when 条件二 then else  end
select name,
       case
           when age > 60 then '老年人'
           when age > 40 then '中年人
'
           else '年轻人' end as '年龄段'
from emp;
# case key when value then 结果一 when value2 then 结果二 else 结果三 end
create table score
(
    id      int comment 'ID',
    name    varchar(20) comment '姓名',
    math    int comment '数学',
    english int comment '英语',
    chinese int comment '语文'
) comment '学员成绩表';
insert into score(id, name, math, english, chinese)
VALUES (1, 'Tom', 67, 88, 95),
       (2, 'Rose', 23, 66, 90),
       (3, 'Jack', 56, 98, 76);

select id,
       name,
       (case
            when math > 90 then '优秀'
            when math > 60 then '一般'
            else '不及格' end)  as '数学',
       (case
            when english > 90 then '优秀'
            when english > 60 then '一般'
            else '不及格' end) as'英语',
       (case
            when score.chinese > 90 then '优秀'
            when score.chinese > 60 then '一般'
            else '不及格' end) as '语文'


from score;
select name,id ,case chinese when 98 then 'good'
                     when 95  then  'nice'
                     else 'just so so' end as '评价'
from score;

/*

/*
    聚合函数3.

        以一列数据作为一个整体进行纵向计算
 */
#计数器 null 不参与计算
# count(1) 也是可以的 1代表的是这行有数据
select count(*)
from emp;
select avg(salary) as '平均薪资'
from emp;
select max(salary)
from emp;
select min(salary)
from emp;
select sum(salary)
from emp;

表间关系

/*
    多表关系：
        一对多
            多的一方设置外键对应一的一方主键
        一对一
            用于单表拆分，将基础字段放到一张表，其他详情表放在另外一张表中
            在任意一方设立外键，并设置外键为唯一的
        多对多
            建立第三张中间表，中间表中至少包含两个外键，分别关联两方主键

 */

多表练习

-- 1. 查询员工的姓名、年龄、职位、部门名称信息。 （隐式内连接）
select  emp.name,age,job,dept.name  from emp,dept where emp.dept_id=dept.id;

-- 2. 查询年龄小于30岁的员工姓名、年龄、职位、部门信息。 （显式内连接）
select  emp.name,age,job,d.name  from emp join dept d on emp.dept_id = d.id where age<30;

-- 3. 查询所有年龄大于40岁的员工, 及其归属的部门名称; 如果员工没有分配部门, 也需要展示出来。
select emp.*,d2.name from emp left join dept d2 on d2.id = emp.dept_id where age>40;

-- 4. 查询所有员工的 姓名、薪资、薪资等级（重要）
select  name,salary,grade from emp left join salgrade on salary<=salgrade.hisal and salary>=salgrade.losal;

-- 5. 查询 "研发部" 员工的平均工资。
select avg(salary) as '平均工资' from emp where dept_id in(select id from dept where dept.name='研发部');

-- 6. 查询工资比 "灭绝" 高的员工信息。
select * from emp where salary >(select salary from emp where name='灭绝');

-- 7. 查询比平均薪资高的员工信息。
select * from emp where salary>(select avg(salary) from emp );

-- 8. 查询低于本部门平均工资的员工信息。(重要)

select * from emp s where salary<(select avg(salary) from emp where dept_id=s.dept_id)