一. 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)查找客户每周（以年，月，周 显示）产生的订单量