8. SELECT

标签：join no -- emp test SELECT left

一. LIMIT和ORDER BY

[root@mysql.sock][employees]> select * from employees limit 1; -- 从employees中随机取出一条数据，结果是不确定的
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
+--------+------------+------------+-----------+--------+------------+
1 row in set (0.34 sec)

[root@mysql.sock][employees]> select * from employees order by emp_no asc limit 1; -- 使用order by排序，默认是升序
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
+--------+------------+------------+-----------+--------+------------+
1 row in set (0.00 sec)

[root@mysql.sock][employees]> select * from employees order by emp_no desc limit 1; -- 降序
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
| 499999 | 1958-05-01 | Sachin     | Tsukuda   | M      | 1997-11-30 |
+--------+------------+------------+-----------+--------+------------+
1 row in set (0.29 sec)

[root@mysql.sock][employees]> show create table employees\G
*************************** 1. row ***************************
       Table: employees
Create Table: CREATE TABLE `employees` (
  `emp_no` int(11) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `hire_date` date NOT NULL,
  PRIMARY KEY (`emp_no`)  -- emp_no是主键，order by主键不会创建临时表，主键索引本身有序
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

[root@mysql.sock][employees]> select * from employees order by emp_no asc limit 5,5;  -- limit start, limit 从第5条开始取，取5条出来
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10006 | 1953-04-20 | Anneke     | Preusig   | F      | 1989-06-02 |
|  10007 | 1957-05-23 | Tzvetan    | Zielinski | F      | 1989-02-10 |
|  10008 | 1958-02-19 | Saniya     | Kalloufi  | M      | 1994-09-15 |
|  10009 | 1952-04-19 | Sumant     | Peac      | F      | 1985-02-18 |
|  10010 | 1963-06-01 | Duangkaew  | Piveteau  | F      | 1989-08-24 |
+--------+------------+------------+-----------+--------+------------+
-- 以上这个分页效果会随着start的增加性能下降，因为会扫描表（从1到start）
-- 相对比较推荐的方法如下。但是这种方法无法做到连续分页。最好的办法是将数据存到cache里，如Redis
[root@mysql.sock][employees]> select * from employees where emp_no > 20000 order by emp_no limit 5;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  20001 | 1962-05-16 | Atreye     | Eppinger  | M      | 1990-04-18 |
|  20002 | 1955-12-25 | Jaber      | Brender   | M      | 1988-01-26 |
|  20003 | 1953-04-11 | Munehiko   | Coors     | F      | 1991-02-07 |
|  20004 | 1952-03-07 | Radoslaw   | Pfau      | M      | 1995-11-24 |
|  20005 | 1956-02-20 | Licheng    | Przulj    | M      | 1992-07-17 |
+--------+------------+------------+-----------+--------+------------+
5 rows in set (0.29 sec)

ORDER BY 是把已经查询好的结果集进行排序

二. WHERE

WHERE是将查询出来的结果，通过WHERE后面的条件（condition）对结果进行排序

[root@mysql.sock][employees]> select * from employees
    -> where (emp_no > 40000 and hire_date > '1991-01-01')-- 使用()明确逻辑条件
    -> or (emp_no > 40000 and birth_date > '1961-01-01')
    -> order by emp_no limit 5;
+--------+------------+------------+------------+--------+------------+
| emp_no | birth_date | first_name | last_name  | gender | hire_date  |
+--------+------------+------------+------------+--------+------------+
|  40003 | 1960-01-26 | Jacopo     | Marshall   | F      | 1991-09-30 |
|  40005 | 1961-02-27 | Zsolt      | Fairtlough | F      | 1991-07-08 |
|  40006 | 1962-11-07 | Basim      | Panienski  | F      | 1986-12-27 |
|  40012 | 1955-02-07 | Chinhyun   | Ozeri      | F      | 1995-08-12 |
|  40015 | 1964-10-08 | Ioana      | Lemarechal | M      | 1997-08-07 |
+--------+------------+------------+------------+--------+------------+
5 rows in set (0.00 sec)

三. JOIN

-- 查出普通员工的title，部门名称，薪资
SELECT 
    e.emp_no,
    CONCAT(e.first_name, ' ', e.last_name) AS emp_name,
    t.title,
    p.dept_name,
    s.salary
FROM
    employees AS e
        LEFT JOIN
    titles AS t ON e.emp_no = t.emp_no
        LEFT JOIN
    dept_emp AS d ON e.emp_no = d.emp_no
        LEFT JOIN
    departments p ON d.dept_no = p.dept_no
        LEFT JOIN
    salaries AS s ON e.emp_no = s.emp_no
WHERE
    t.title != 'Engineer';

3.1 INNER JOIN

select e.emp_no, concat(first_name, ' ', last_name) as emp_name, gender, title from employees as e, titles as t where e.emp_no = t.emp_no limit 5;
等价于:
select e.emp_no, concat(first_name, ' ', last_name) as emp_name, gender, title from employees as e join titles t on e.emp_no = t.emp_no limit 5;

3.2 OUTER JOIN

[root@mysql.sock][test]> create table test_left_join_1(a int);
Query OK, 0 rows affected (0.07 sec)

[root@mysql.sock][test]> create table test_left_join_2(a int);
Query OK, 0 rows affected (0.07 sec)

[root@mysql.sock][test]> insert into test_left_join_1 values(1);
Query OK, 1 row affected (0.29 sec)

[root@mysql.sock][test]> insert into test_left_join_1 values(2);
Query OK, 1 row affected (0.04 sec)

[root@mysql.sock][test]> insert into test_left_join_2 values(1);
Query OK, 1 row affected (0.00 sec)

[root@mysql.sock][test]> select * from test_left_join_1 left join test_left_join_2 on test_left_join_1.a = test_left_join_2.a;
+------+------+
| a    | a    |  
+------+------+
|    1 |    1 |  -- 满足条件的显示t2中该条记录的值
|    2 | NULL |  -- 不满足条件的，用NULL填充
+------+------+
2 rows in set (0.01 sec)

--  left join ： 左表left join 右表on 条件；
--  左表全部显示，右表是匹配表，
--  如果右表的某条记录满足[on 条件] 匹配，则该记录显示
--  如果右表的某条记录 不 满足 匹配，则该记录显示NULL

select * from test_left_join_1 right join test_left_join_2 on test_left_join_1.a = test_left_join_2.a;
+------+------+
| a    | a    |
+------+------+
|    1 |    1 |  -- 右表t2全部显示
+------+------+
1 row in set (0.00 sec)
--  right join ： 左表right join 右表on 条件
--  右表全部显示，左边是匹配表
--  同样和left join，满足则显示，不满足且右表中有值，则填充NULL

[root@mysql.sock][test]> select * from test_left_join_1 left join test_left_join_2 on test_left_join_1.a = test_left_join_2.a where test_left_join_2.a is NULL;
+------+------+
| a    | a    |
+------+------+
|    2 | NULL |  -- 数据1在左表和右表中都存在，所以不显示
+------+------+
1 row in set (0.00 sec)

-- left join ：left outer join , outer关键字可以省略
-- right join：right outer join , outer 关键字可以省略
-- join无论inner还是outer，列名不需要一样，甚至列的类型也可以不一样，会进行转换。
-- 一般情况下，表设计合理，需要关联的字段类型应该是一样的

--
--  查找哪些员工不是经理
--
[root@mysql.sock][employees]> SELECT                                                                                                         ->     employees.emp_no,
    ->     CONCAT(first_name, ' ', last_name) AS emp_name,
    ->     dept_no
    -> FROM
    ->     employees
    ->         LEFT JOIN
    ->     dept_manager ON employees.emp_no = dept_manager.emp_no
    -> WHERE
    ->     dept_manager.dept_no IS NULL limit 5;
+--------+-------------------+---------+
| emp_no | emp_name          | dept_no |
+--------+-------------------+---------+
|  10001 | Georgi Facello    | NULL    |
|  10002 | Bezalel Simmel    | NULL    |
|  10003 | Parto Bamford     | NULL    |
|  10004 | Chirstian Koblick | NULL    |
|  10005 | Kyoichi Maliniak  | NULL    |
+--------+-------------------+---------+
5 rows in set (0.00 sec)

-- 在inner join中，过滤条件放在where或者on中都是可以的
-- 在outer join中 条件放在where和on中是不一样的
[root@mysql.sock][test]> SELECT
    ->     *
    -> FROM
    ->     test_left_join_1 AS t1
    ->         LEFT JOIN
    ->     test_left_join_2 AS t2 ON t1.a = t2.a
    -> WHERE
    ->     t2.a IS NULL;
+------+------+
| a    | a    |
+------+------+
|    2 | NULL |
+------+------+
1 row in set (0.01 sec)

[root@mysql.sock][test]> SELECT
    ->     *
    -> FROM
    ->     test_left_join_1 AS t1
    ->         LEFT JOIN
    ->     test_left_join_2 AS t2 ON t1.a = t2.a AND t2.a IS NULL;  -- 除了a=b, 还要找到b=null的，但是b里面没有null，所以两张表关联之后，
+------+------+                                                     -- t2里面的值都不符合条件，则a全部显示，b全为null
| a    | a    |
+------+------+
|    1 | NULL |
|    2 | NULL |
+------+------+
2 rows in set (0.00 sec)

-- ON 参与outer join的结果的生成，而where只是对结果的一个过滤

3.3 GROUP BY

--
-- 员工数量大于5000的部门
--
[root@mysql.sock][employees]> SELECT
    ->     dept_no, COUNT(emp_no) AS count_
    -> FROM
    ->     dept_emp
    -> GROUP BY dept_no
    -> HAVING count_ > 5000;
+---------+--------+
| dept_no | count_ |
+---------+--------+
| d001    |  20211 |
| d002    |  17346 |
| d003    |  17786 |
| d004    |  73485 |
| d005    |  85707 |
| d006    |  20117 |
| d007    |  52245 |
| d008    |  21126 |
| d009    |  23580 |
+---------+--------+
9 rows in set (0.12 sec)

--
-- 查找客户每年每月产生的订单数
--
[root@mysql.sock][dbt3_s1]> SELECT
    ->     o_custkey, COUNT(o_orderkey), o_orderDATE
    -> FROM
    ->     orders
    -> GROUP BY o_custkey , MONTH(o_orderDATE) , YEAR(o_orderDATE)
    -> LIMIT 10;
+-----------+-------------------+-------------+
| o_custkey | COUNT(o_orderkey) | o_orderDATE |
+-----------+-------------------+-------------+
|         1 |                 1 | 1997-03-23  |
|         1 |                 1 | 1992-04-19  |
|         1 |                 1 | 1996-06-29  |
|         1 |                 1 | 1996-07-01  |
|         1 |                 1 | 1992-08-22  |
|         1 |                 1 | 1996-12-09  |
|         2 |                 1 | 1997-02-22  |
|         2 |                 1 | 1995-03-10  |
|         2 |                 1 | 1992-04-05  |
|         2 |                 1 | 1994-05-21  |
+-----------+-------------------+-------------+
10 rows in set (14.16 sec)

-- 使用date_format 函数
[root@mysql.sock][dbt3_s1]> SELECT
    ->     o_custkey,
    ->     COUNT(o_orderkey),
    ->     DATE_FORMAT(o_orderDATE, '%Y-%m')
    -> FROM
    ->     orders
    -> GROUP BY o_custkey , DATE_FORMAT(o_orderDATE, '%Y-%m')
    -> LIMIT 10;
+-----------+-------------------+-----------------------------------+
| o_custkey | COUNT(o_orderkey) | DATE_FORMAT(o_orderDATE, '%Y-%m') |
+-----------+-------------------+-----------------------------------+
|         1 |                 1 | 1992-04                           |
|         1 |                 1 | 1992-08                           |
|         1 |                 1 | 1996-06                           |
|         1 |                 1 | 1996-07                           |
|         1 |                 1 | 1996-12                           |
|         1 |                 1 | 1997-03                           |
|         2 |                 1 | 1992-04                           |
|         2 |                 1 | 1994-05                           |
|         2 |                 1 | 1994-08                           |
|         2 |                 1 | 1994-12                           |
+-----------+-------------------+-----------------------------------+
10 rows in set (15.70 sec)

查找客户每周（以年，月，周显示）产生的订单量

标签：join,no,--,emp,test,SELECT,left
From： https://www.cnblogs.com/gavin-zheng/p/17354281.html