MySQL学习笔记（2）

古城微笑少年丶 2022-09-16 13:24 138阅读 0赞

> 1.  简单的查询语句

select 字段1， 字段2
    from 表名

//查询员工年薪 字符串尽量用单引号
    mysql> select ename, sal * 12 as '年薪'
        -> from emp;
    +--------+----------+
    | ename  | 年薪     |
    +--------+----------+
    | SMITH  |  9600.00 |
    | ALLEN  | 19200.00 |
    | WARD   | 15000.00 |
    | JONES  | 35700.00 |
    | MARTIN | 15000.00 |
    | BLAKE  | 34200.00 |
    | CLARK  | 29400.00 |
    | SCOTT  | 36000.00 |
    | KING   | 60000.00 |
    | TURNER | 18000.00 |
    | ADAMS  | 13200.00 |
    | JAMES  | 11400.00 |
    | FORD   | 36000.00 |
    | MILLER | 15600.00 |
    +--------+----------+
    14 rows in set (0.00 sec)

> 1.  条件查询

select 字段1， 字段2
    from 表名
    where 条件

// 1. 查询工资为5000的员工
    mysql> select ename, sal
        -> from emp
        -> where sal > 3000;
    +-------+---------+
    | ename | sal     |
    +-------+---------+
    | KING  | 5000.00 |
    +-------+---------+
    1 row in set (0.00 sec)
    
    // 2. 找出工资大于等于1000并且小于等于3000的员工
    mysql> select ename, sal
        -> from emp
        -> where sal >= 1000 and sal <= 3000;
    +--------+---------+
    | ename  | sal     |
    +--------+---------+
    | ALLEN  | 1600.00 |
    | WARD   | 1250.00 |
    | JONES  | 2975.00 |
    | MARTIN | 1250.00 |
    | BLAKE  | 2850.00 |
    | CLARK  | 2450.00 |
    | SCOTT  | 3000.00 |
    | TURNER | 1500.00 |
    | ADAMS  | 1100.00 |
    | FORD   | 3000.00 |
    | MILLER | 1300.00 |
    +--------+---------+
    11 rows in set (0.00 sec)
    
    // 或者是下面这种写法
    mysql> select ename, sal
        -> from emp
        -> where sal between 1000 and 3000;
        
    // 3. 找出没有津贴的员工
    mysql> select ename, sal, comm
        -> from emp
        -> where comm is null or comm = 0;
    +--------+---------+------+
    | ename  | sal     | comm |
    +--------+---------+------+
    | SMITH  |  800.00 | NULL |
    | JONES  | 2975.00 | NULL |
    | BLAKE  | 2850.00 | NULL |
    | CLARK  | 2450.00 | NULL |
    | SCOTT  | 3000.00 | NULL |
    | KING   | 5000.00 | NULL |
    | TURNER | 1500.00 | 0.00 |
    | ADAMS  | 1100.00 | NULL |
    | JAMES  |  950.00 | NULL |
    | FORD   | 3000.00 | NULL |
    | MILLER | 1300.00 | NULL |
    +--------+---------+------+
    11 rows in set (0.00 sec)
    
    // 4. 找出名字里面含有o的员工
    //模糊查询，%匹配任意多个字符，_匹配一个字符
    mysql> select ename
        -> from emp
        -> where ename like '%o%';
    +-------+
    | ename |
    +-------+
    | JONES |
    | SCOTT |
    | FORD  |
    +-------+
    3 rows in set (0.00 sec)

> 1.  排序

select 字段1， 字段2
    from 表名
    where 条件
    order by 字段3

// 1. 按照工资升序排列，工资相同的按照名字升序排列
    // 默认的排序顺序是升序，也就是asc可以省略
    mysql> select ename, sal
        -> from emp
        -> order by sal desc, ename asc;
    +--------+---------+
    | ename  | sal     |
    +--------+---------+
    | KING   | 5000.00 |
    | FORD   | 3000.00 |
    | SCOTT  | 3000.00 |
    | JONES  | 2975.00 |
    | BLAKE  | 2850.00 |
    | CLARK  | 2450.00 |
    | ALLEN  | 1600.00 |
    | TURNER | 1500.00 |
    | MILLER | 1300.00 |
    | MARTIN | 1250.00 |
    | WARD   | 1250.00 |
    | ADAMS  | 1100.00 |
    | JAMES  |  950.00 |
    | SMITH  |  800.00 |
    +--------+---------+
    14 rows in set (0.00 sec)
    
    // 2. 找出工作岗位是salesman的员工，并按工资顺序排列
    mysql> select ename, sal
        -> from emp
        -> where job = 'salesman'
        -> order by sal;
    +--------+---------+
    | ename  | sal     |
    +--------+---------+
    | WARD   | 1250.00 |
    | MARTIN | 1250.00 |
    | TURNER | 1500.00 |
    | ALLEN  | 1600.00 |
    +--------+---------+
    4 rows in set (0.00 sec)

> 1.  分组函数

五个分组函数count, min, max, sum,avg，分组函数是对一组数据进行操作的  
**注意：分组函数不可以直接使用在where语句中（例4）**  
因为group by在where后执行，而分组函数在group by后执行，因此在where中不能直接使用分组函数

select 字段1， 字段2
    from 表名
    where 条件
    group by 字段3

// 1. 求出总人数
    mysql> select count(*)
        -> from emp;
    +----------+
    | count(*) |
    +----------+
    |       14 |
    +----------+
    1 row in set (0.00 sec)
    
    // 2. 求津贴总额
    // 分组函数自动忽略null值
    mysql> select sum(comm)
        -> from emp;
    +-----------+
    | sum(comm) |
    +-----------+
    |   2200.00 |
    +-----------+
    1 row in set (0.00 sec)
    
    // 3. count(*)和count(字段)的区别：
    // count(*)统计的是总记录的条数，雨字段无关
    // 而count(字段)统计的是某个字段中不为null的元素总数
    mysql> select count(*)
        -> from emp;
    +----------+
    | count(*) |
    +----------+
    |       14 |
    +----------+
    1 row in set (0.00 sec)
    
    // 4.找出工资高于平均工资的员工
    // 以下是错误语句。
    mysql> select ename, sal
        -> from emp
        -> where sal > avg(sal);
    ERROR 1111 (HY000): Invalid use of group function
    // 下面是正确语句
    mysql> select ename, sal
        -> from emp
        -> where sal > (select avg(sal) from emp);
    +-------+---------+
    | ename | sal     |
    +-------+---------+
    | JONES | 2975.00 |
    | BLAKE | 2850.00 |
    | CLARK | 2450.00 |
    | SCOTT | 3000.00 |
    | KING  | 5000.00 |
    | FORD  | 3000.00 |
    +-------+---------+
    6 rows in set (0.00 sec)

> 5.分组

select 字段，分组函数       5
    from 表名                  1
    where 条件                 2
    group by 字段              3   
    having 条件                4
    order by 字段              6

**注意：当语句中有group by时，select中只能跟分组函数和参与分组的字段（例1）**

// 1. 找出每个岗位的最高薪资
    //以下是错误语句（MySQL中可以执行，但是没有意义，Oracle中直接报错）
    mysql> select ename,job, max(sal)
        -> from emp
        -> group by job;
    +-------+-----------+----------+
    | ename | job       | max(sal) |
    +-------+-----------+----------+
    | SMITH | CLERK     |  1300.00 |
    | ALLEN | SALESMAN  |  1600.00 |
    | JONES | MANAGER   |  2975.00 |
    | SCOTT | ANALYST   |  3000.00 |
    | KING  | PRESIDENT |  5000.00 |
    +-------+-----------+----------+
    5 rows in set (0.00 sec)
    
    //2. 每个岗位的平均工资
    mysql> select job, avg(sal)
        -> from emp
        -> group by job;
    +-----------+-------------+
    | job       | avg(sal)    |
    +-----------+-------------+
    | CLERK     | 1037.500000 |
    | SALESMAN  | 1400.000000 |
    | MANAGER   | 2758.333333 |
    | ANALYST   | 3000.000000 |
    | PRESIDENT | 5000.000000 |
    +-----------+-------------+
    5 rows in set (0.00 sec)
    
    // 3. 找出每个部门不同岗位的最高薪资
    // 联合分组
    mysql> select deptno, job, max(sal)
        -> from emp
        -> group by deptno, job;
    +--------+-----------+----------+
    | deptno | job       | max(sal) |
    +--------+-----------+----------+
    |     20 | CLERK     |  1100.00 |
    |     30 | SALESMAN  |  1600.00 |
    |     20 | MANAGER   |  2975.00 |
    |     30 | MANAGER   |  2850.00 |
    |     10 | MANAGER   |  2450.00 |
    |     20 | ANALYST   |  3000.00 |
    |     10 | PRESIDENT |  5000.00 |
    |     30 | CLERK     |   950.00 |
    |     10 | CLERK     |  1300.00 |
    +--------+-----------+----------+
    9 rows in set (0.00 sec)
    
    // 4. 找出每个部门的最高薪资，显示薪资大于2900
    mysql> select max(sal), deptno 
        -> from emp 
        -> where sal > 2900 group by deptno;
    +----------+--------+
    | max(sal) | deptno |
    +----------+--------+
    |  3000.00 |     20 |
    |  5000.00 |     10 |
    +----------+--------+
    2 rows in set (0.00 sec)
    
    // 还有一种having写法，但是不建议这种写法，效率低。
    // 当where和having都能用的时候优先选择where
    mysql> select max(sal), deptno 
        -> from emp 
        -> group by deptno 
        -> having max(sal) > 2900;
    +----------+--------+
    | max(sal) | deptno |
    +----------+--------+
    |  3000.00 |     20 |
    |  5000.00 |     10 |
    +----------+--------+
    2 rows in set (0.00 sec)
    
    // 5. 找出每个部门的平均工资，显示平均薪资大于2000
    // 这里不能用where，因为where后面不能直接使用分组函数
    mysql> select deptno, avg(sal) 
        -> from emp 
        -> group by deptno 
        -> having avg(sal) > 2000;
    +--------+-------------+
    | deptno | avg(sal)    |
    +--------+-------------+
    |     20 | 2175.000000 |
    |     10 | 2916.666667 |
    +--------+-------------+
    2 rows in set (0.00 sec)

> 1.  去重

// 1. 查询有哪些岗位
    mysql> select distinct job
        -> from emp;
    +-----------+
    | job       |
    +-----------+
    | CLERK     |
    | SALESMAN  |
    | MANAGER   |
    | ANALYST   |
    | PRESIDENT |
    +-----------+
    5 rows in set (0.00 sec)
    
    //2. 查询有多少个岗位
    mysql> select count(distinct job)
        -> from emp;
    +---------------------+
    | count(distinct job) |
    +---------------------+
    |                   5 |
    +---------------------+
    1 row in set (0.00 sec)
    
    // 2. 每个部门的岗位
    // 联合去重
    mysql> select distinct deptno, job
        -> from emp;
    +--------+-----------+
    | deptno | job       |
    +--------+-----------+
    |     20 | CLERK     |
    |     30 | SALESMAN  |
    |     20 | MANAGER   |
    |     30 | MANAGER   |
    |     10 | MANAGER   |
    |     20 | ANALYST   |
    |     10 | PRESIDENT |
    |     30 | CLERK     |
    |     10 | CLERK     |
    +--------+-----------+
    9 rows in set (0.00 sec)