mysql笔记 - SELECT 语句
SELECT
[ALL | DISTINCT | DISTINCTROW ] *
[HIGH_PRIORITY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expr [, select_expr ...]
[FROM table_references
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[PROCEDURE procedure_name(argument_list)]
[INTO OUTFILE 'file_name'
[CHARACTER SET charset_name]
export_options
| INTO DUMPFILE 'file_name'
| INTO var_name [, var_name]]
[FOR UPDATE | LOCK IN SHARE MODE]]
select * from employees; -- 取出所有数据
desc employees; -- 查看表信息
通常会要求开发人员不要使用select * 即使是select所有字段推荐写上所有的列名，虽然很麻烦但是规范上推荐这样做
一个重要的原因是alter table会对表结构进行修改的很多操作，alter table后select ＊ from取出的字段可能比一开始涉及的要多，可能会遇到各种各样的问题。另外select alter table可以加列，可以加在某个字段中间的位置，那么程序那边取得的数据并不是一开始想要的数据，容易出错。所以建议select的时候建议把所有的列都带上
LIMIT 限制取出来的数据量
不加LIMIT限制会一次取出所有的数据，数据量大的时候可能是灾难性的事情，通常都会加一个LIMIT，图形化工具一般默认会加上一个LIMIT
select * from employees limit 0,3; -- 从第0条开始取出3条数据
select * from employees limit 1,3; -- 从第1条开始取出3条数据
LIMIT常用于分页，但是LIMIT offset数字越大性能越差，为什么呢？
select * from employees limit 30; -- 取30条，扫30条，查询快(0.00 sec)
select * from employees limit 1000000,30; -- 同样取30条，扫1000030行记录，查询变很慢(14.55 sec)
好的做法
select emp_no,birth_date,first_name,last_name,gender from employees limit 10;
+--------+------------+------------+-----------+--------+
| emp_no | birth_date | first_name | last_name | gender |
+--------+------------+------------+-----------+--------+
| 10001 | 1953-09-02 | Georgi | Facello | M |
| 10002 | 1964-06-02 | Bezalel | Simmel | F |
| 10003 | 1959-12-03 | Parto | Bamford | M |
| 10004 | 1954-05-01 | Chirstian | Koblick | M |
| 10005 | 1955-01-21 | Kyoichi | Maliniak | M |
| 10006 | 1953-04-20 | Anneke | Preusig | F |
| 10007 | 1957-05-23 | Tzvetan | Zielinski | F |
| 10008 | 1958-02-19 | Saniya | Kalloufi | M |
| 10009 | 1952-04-19 | Sumant | Peac | F |
| 10010 | 1963-06-01 | Duangkaew | Piveteau | F |
+--------+------------+------------+-----------+--------+
select emp_no,birth_date,first_name,last_name,gender from employees where emp_no > 10010 limit 10;
+--------+------------+------------+-------------+--------+
| emp_no | birth_date | first_name | last_name | gender |
+--------+------------+------------+-------------+--------+
| 10011 | 1953-11-07 | Mary | Sluis | F |
| 10012 | 1960-10-04 | Patricio | Bridgland | M |
| 10013 | 1963-06-07 | Eberhardt | Terkki | M |
| 10014 | 1956-02-12 | Berni | Genin | M |
| 10015 | 1959-08-19 | Guoxiang | Nooteboom | M |
| 10016 | 1961-05-02 | Kazuhito | Cappelletti | M |
| 10017 | 1958-07-06 | Cristinel | Bouloucos | F |
| 10018 | 1954-06-19 | Kazuhide | Peha | F |
| 10019 | 1953-01-23 | Lillian | Haddadi | M |
| 10020 | 1952-12-24 | Mayuko | Warwick | M |
+--------+------------+------------+-------------+--------+
从之前取得数据的最大值开始取数据，这样的一个好处是不管你的值多大，因为是通过大于等于来定位的，所以只是取了10条数据不会再去扫这么多的数据
下面的语句表示从mysql中随机取出3条数据，select语句不带order by表示随机取3条数据
select emp_no,first_name,last_name from employees limit 3;
+--------+------------+-----------+
| emp_no | first_name | last_name |
+--------+------------+-----------+
| 10001 | Georgi | Facello |
| 10002 | Bezalel | Simmel |
| 10003 | Parto | Bamford |
+--------+------------+-----------+
数据量小的时候不容易看出来，数据量大的时候容易发现这个问题，或者alter table一下后再试
alter table employees add index idx_name(first_name,last_name);
select emp_no,first_name,last_name from employees limit 3;
+--------+------------+-----------+
| emp_no | first_name | last_name |
+--------+------------+-----------+
| 69256 | Aamer | Anger |
| 486584 | Aamer | Armand |
| 237165 | Aamer | Azevdeo |
+--------+------------+-----------+
order by 表示根据哪个字段进行排序，可以按照物理数据顺序取出数据
select emp_no,first_name,last_name from employees order by emp_no limit 3;
+--------+------------+-----------+
| emp_no | first_name | last_name |
+--------+------------+-----------+
| 10001 | Georgi | Facello |
| 10002 | Bezalel | Simmel |
| 10003 | Parto | Bamford |
+--------+------------+-----------+
查询分区表
select * from titles partition (p19) limit 1;
+--------+--------------+------------+------------+
| emp_no | title | from_date | to_date |
+--------+--------------+------------+------------+
| 10052 | Senior Staff | 2002-01-31 | 9999-01-01 |
+--------+--------------+------------+------------+
ORDER BY 对取出来的数据做排序
order by根据某个字段进行分页
select emp_no,first_name,last_name from employees order by last_name limit 10;
+--------+------------+-----------+
| emp_no | first_name | last_name |
+--------+------------+-----------+
| 11761 | Bartek | Aamodt |
| 15427 | Aluzio | Aamodt |
| 18182 | Dekang | Aamodt |
| 16572 | Matt | Aamodt |
| 12791 | Mokhtar | Aamodt |
| 12516 | Sreenivas | Aamodt |
| 12982 | Sachem | Aamodt |
| 17400 | Basim | Aamodt |
| 19898 | Vidar | Aamodt |
| 17885 | Takanari | Aamodt |
+--------+------------+-----------+
WHERE 过滤数据
select emp_no,first_name,last_name from employees where emp_no = 10001;
+--------+------------+-----------+
| emp_no | first_name | last_name |
+--------+------------+-----------+
| 10001 | Georgi | Facello |
+--------+------------+-----------+
select emp_no,first_name,last_name from employees where emp_no >= 10001 limit 10;
+--------+------------+-----------+
| emp_no | first_name | last_name |
+--------+------------+-----------+
| 10001 | Georgi | Facello |
| 10002 | Bezalel | Simmel |
| 10003 | Parto | Bamford |
| 10004 | Chirstian | Koblick |
| 10005 | Kyoichi | Maliniak |
| 10006 | Anneke | Preusig |
| 10007 | Tzvetan | Zielinski |
| 10008 | Saniya | Kalloufi |
| 10009 | Sumant | Peac |
| 10010 | Duangkaew | Piveteau |
+--------+------------+-----------+
select emp_no,first_name,last_name from employees where emp_no >= 20001 and first_name like 'Am%' limit 10;
+--------+------------+---------------+
| emp_no | first_name | last_name |
+--------+------------+---------------+
| 100860 | Amabile | Aamodt |
| 285669 | Amabile | Akiyama |
| 276002 | Amabile | Albarhamtoshy |
| 454340 | Amabile | Alencar |
| 86625 | Amabile | Anger |
| 416143 | Amabile | Antonisse |
| 491486 | Amabile | Antonisse |
| 451988 | Amabile | Apsitis |
| 409363 | Amabile | Atchley |
| 208844 | Amabile | Baar |
+--------+------------+---------------+
select emp_no,first_name,last_name from employees where emp_no >= 20001 and first_name like '%Am%' limit 10;
+--------+------------+--------------+
| emp_no | first_name | last_name |
+--------+------------+--------------+
| 20044 | Kiam | Gist |
| 20062 | Uinam | Heuser |
| 20114 | Ramalingam | Zyda |
| 20118 | Mohammed | Schneeberger |
| 20142 | Arumugam | Emmart |
| 20159 | Isamu | Valiente |
| 20167 | Stamatina | Kobara |
| 20217 | Tamiya | Ruemmler |
| 20265 | Amalendu | Willoner |
| 20285 | Pramod | Escriba |
+--------+------------+--------------+
select emp_no,first_name,last_name from employees where emp_no >= 20001 or first_name like '%Am%' limit 10;
+--------+------------+-----------+
| emp_no | first_name | last_name |
+--------+------------+-----------+
| 69256 | Aamer | Anger |
| 486584 | Aamer | Armand |
| 237165 | Aamer | Azevdeo |
| 413688 | Aamer | Azuma |
| 281363 | Aamer | Baak |
| 242368 | Aamer | Baaleh |
| 206549 | Aamer | Baar |
| 259089 | Aamer | Baba |
| 60922 | Aamer | Bahl |
| 283280 | Aamer | Bahl |
+--------+------------+-----------+
where 1 = 1表示没有任何条件全部成立，因为不知道第一个where应该怎么写，如果提前把where 1 ＝ 1写好，这样程序那边拼接后面的条件就比较方便
select emp_no,first_name,last_name from employees where 1 = 1 limit 10;
+--------+------------+-----------+
| emp_no | first_name | last_name |
+--------+------------+-----------+
| 69256 | Aamer | Anger |
| 486584 | Aamer | Armand |
| 237165 | Aamer | Azevdeo |
| 413688 | Aamer | Azuma |
| 281363 | Aamer | Baak |
| 242368 | Aamer | Baaleh |
| 206549 | Aamer | Baar |
| 259089 | Aamer | Baba |
| 60922 | Aamer | Bahl |
| 283280 | Aamer | Bahl |
+--------+------------+-----------+
比如
select emp_no,first_name,last_name from employees where 1 = 1 and emp_no = 20000;
+--------+------------+-----------+
| emp_no | first_name | last_name |
+--------+------------+-----------+
| 20000 | Jenwei | Matzke |
+--------+------------+-----------+
GROUP BY
分组就是根据某个字段当中相同的值来进行分组，并且分组完之后是要加一个聚集(统计)函数
use test;
create table t (a int auto_increment primary key, b int, c int);
insert into t select NULL,1,1;
insert into t select NULL,1,7;
insert into t select NULL,1,8;
insert into t select NULL,2,8;
insert into t select NULL,2,10;
insert into t select NULL,3,8;
select * from t;
+---+------+------+
| a | b | c |
+---+------+------+
| 1 | 1 | 1 |
| 2 | 1 | 7 |
| 3 | 1 | 8 |
| 4 | 2 | 8 |
| 5 | 2 | 10 |
| 6 | 3 | 8 |
+---+------+------+
select b,sum(b),avg(b) from t group by b;
+------+--------+--------+
| b | sum(b) | avg(b) |
+------+--------+--------+
| 1 | 3 | 1.0000 |
| 2 | 4 | 2.0000 |
| 3 | 3 | 3.0000 |
+------+--------+--------+
HAVING
HAVING表示对group by中的聚合函数进行过滤
select b,sum(b),avg(b) from t where avb(b)>2 group by b; -- 这样是会报错的
ERROR 1305 (42000): FUNCTION test.avb does not exist
分组中需要对分组的条件进行过滤需要使用HAVING
select b,sum(b),avg(b) from t group by b having avg(b) > 2;
+------+--------+--------+
| b | sum(b) | avg(b) |
+------+--------+--------+
| 3 | 3 | 3.0000 |
+------+--------+--------+
不是说使用了group by就代表不能使用where了，这样也是可以使用where进行过滤的，只不过where过滤的是非聚合的结果(对查询的记录进行过滤)，而having是用来过滤聚合的结果
select b,sum(b),avg(b) from t where b < 3 group by b;
+------+--------+--------+
| b | sum(b) | avg(b) |
+------+--------+--------+
| 1 | 3 | 1.0000 |
| 2 | 4 | 2.0000 |
+------+--------+--------+
双重过滤
select b,sum(b),avg(b) from t where b < 3 group by b having avg(b) < 2;
+------+--------+--------+
| b | sum(b) | avg(b) |
+------+--------+--------+
| 1 | 3 | 1.0000 |
+------+--------+--------+
所以where是一开始选数据的时候就开始过滤了，having是在聚集结果出来之后才开始过滤
having也可以过滤指定的一列而不是聚集函数，但是这样写没有什么意义，使用where可以获得更好的性能，所以having一般都跟聚集函数搭配使用
select b,sum(b),avg(b) from t where b < 3 group by b having b < 2;
+------+--------+--------+
| b | sum(b) | avg(b) |
+------+--------+--------+
| 1 | 3 | 1.0000 |
+------+--------+--------+
select字段中没有选择的字段，having中使用会报错
select b,sum(b),avg(b) from t where b < 3 group by b having a < 2;
ERROR 1054 (42S22): Unknown column 'a' in 'having clause'
这样的语句没有意义，因为取出来的a字段数据不一定是146，mysql会随机取a字段的数据，而且这种写法在orcal中好像不支持
select a,b,sum(b) from t group by b;
+---+------+--------+
| a | b | sum(b) |
+---+------+--------+
| 1 | 1 | 3 |
| 4 | 2 | 4 |
| 6 | 3 | 3 |
+---+------+--------+
JOIN
基本的多表关联查询
SELECT
concat(first_name,' ',last_name), dept_name -- concat连接了2个字段的字符串
FROM
employees e, -- e是别名，下面3个雷同
dept_emp de,
departments d
WHERE
e.emp_no = de.emp_no -- 关联条件
AND de.dept_no = d.dept_no -- 再一次关联条件
LIMIT 3;
+----------------------------------+------------------+
| concat(first_name,' ',last_name) | dept_name |
+----------------------------------+------------------+
| Mary Sluis | Customer Service |
| Huan Lortz | Customer Service |
| Basil Tramer | Customer Service |
+----------------------------------+------------------+
新建一些例子
create table a (a int);
create table b (a int);
insert into a select 1;
insert into a select 2;
insert into a select 3;
insert into b select 1;
insert into b select 2;
select * from a;select * from b;
+------+
| a |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
+------+
| a |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.03 sec)
最简单的两表关联，并不是2张表关联就会做笛卡尔积，如果关联的时候没有写where关联条件就会产生笛卡尔积
select * from a,b where a.a = b.a;
+------+------+
| a | a |
+------+------+
| 1 | 1 |
| 2 | 2 |
+------+------+
select * from a,b; -- 这样会产生笛卡尔积
+------+------+
| a | a |
+------+------+
| 1 | 1 |
| 1 | 2 |
| 2 | 1 |
| 2 | 2 |
| 3 | 1 |
| 3 | 2 |
+------+------+
三张表只有1个过滤条件会出现图示效果，会产生笛卡尔积的效果，出现了4条记录
create table c (a int);
insert into c select 2;
insert into c select 3;
select * from a,b,c where a.a = b.a; -- 这样会产生笛卡尔积
+------+------+------+
| a | a | a |
+------+------+------+
| 1 | 1 | 2 |
| 2 | 2 | 2 |
| 1 | 1 | 3 |
| 2 | 2 | 3 |
+------+------+------+
还可以这样写，关联条件可以非等值
select * from a,b where a.a < b.a;
+------+------+
| a | a |
+------+------+
| 1 | 2 |
+------+------+
修改一下字段名先。。。
alter table a change a x int;
alter table b change a y int;
alter table c change a z int;
INNER JOIN
这3个写法没有区别
select * from a inner join b on a.x = b.y;
select * from a join b on a.x = b.y;
select * from a,b where a.x = b.y;
-- 都返回
+------+------+
| x | y |
+------+------+
| 1 | 1 |
| 2 | 2 |
+------+------+
以下语句的关联，通常来说会选择关联过滤度高的条件进行关联，这里b.y > 1的关联度会高一些。
select * from a,b where a.x = b.y and b.y > 1;
+------+------+
| x | y |
+------+------+
| 2 | 2 |
+------+------+
假设a表100w记录b表100w记录，其中的x，y一一对应，优化机通畅都会先从b>100 and b<200条件先进行过滤，这样就只需要100w和100条记录进行join就可以了，如果先从a.x = b.y进行过滤，那就是100w和100w的记录进行join，所以一般优化机是会把b>100 and b<200的过滤条件设置得更高
INNER JOIN的时候过滤条件即可写在on里面也可以写在where条件里面，对结果来说是没有区别的
select * from a inner join b on (a.x = b.y and b.y > 1);
select * from a inner join b on a.x = b.y where b.y > 1;
都返回
+------+------+
| x | y |
+------+------+
| 2 | 2 |
+------+------+
OUTER JOIN
outer join 分成left/right两种，outer可以关键词可以省略，left/right join代表左/右外连接
左连接中左边的表为保留表，保留表中所有的字段都是要出现的，如果关联条件存在的话就是一对一的情况，右表中关联条件不存在则关联出来的结果就是NULL值
select * from a left join b on a.x = b.y;
+------+------+
| x | y |
+------+------+
| 1 | 1 |
| 2 | 2 |
| 3 | NULL |
+------+------+
select * from a right join b on a.x = b.y;
+------+------+
| x | y |
+------+------+
| 1 | 1 |
| 2 | 2 |
+------+------+
a表中存在的但是b表中不存在的数据，左连接实现
select * from a left join b on a.x = b.y where b.y is NULL;
+------+------+
| x | y |
+------+------+
| 3 | NULL |
+------+------+
a表中但是不在b表中，不使用left join 使用not in ＋ 子查询，不过会存在一些性能上的问题
select * from a where a.x not in (select y from b);
+------+
| x |
+------+
| 3 |
+------+
这样的查询b.y会全部返回NULL，因为a.x = b.y and b.y is NULL不成立，所以b.y全部用NULL填充
select * from a left join b on a.x = b.y and b.y is NULL;
+------+------+
| x | y |
+------+------+
| 1 | NULL |
| 2 | NULL |
| 3 | NULL |
+------+------+
过滤条件写在on后还是where后是有讲究的，推荐使用on来进行两张表之间的关联，where用来进行数据的过滤。
外连接中where不能写在on前面，内连接则可以，left join是用到比较多的一个方式
多次left join
select * from a left join b on a.x = b.y left join c on a.x = c.z;
+------+------+------+
| x | y | z |
+------+------+------+
| 2 | 2 | 2 |
| 1 | 1 | NULL |
| 3 | NULL | 3 |
+------+------+------+
select * from a left join b on a.x = b.y left join c on b.y = c.z order by a.x;
+------+------+------+
| x | y | z |
+------+------+------+
| 1 | 1 | NULL |
| 2 | 2 | 2 |
| 3 | NULL | NULL |
+------+------+------+
第二次join使用等值连接尝试
select * from a left join b on a.x = b.y inner join c on b.y = c.z order by a.x;
+------+------+------+
| x | y | z |
+------+------+------+
| 2 | 2 | 2 |
+------+------+------+
一些例子
查询不是经理的员工
SELECT
e.emp_no
FROM
employees e
LEFT JOIN
dept_manager dm ON e.emp_no = dm.emp_no
WHERE
dm.emp_no IS NULL
LIMIT 3;
分页技巧
使用where而不是使用limit 10000,10 因为limit会扫10000前面的1w条数据
select * from employees where emp_no > 10000 limit 10;
根据生日分页
alter table employees add index idx_birth(birth_date,emp_no); -- 建立联合索引
select * from employees where birth_date > '1952-02-02' order by birth_date limit 10;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 16093 | 1952-02-03 | Luise | Tramer | M | 1992-02-28 |
| 16447 | 1952-02-03 | Zhiguo | Savasere | F | 1987-04-28 |
| 23857 | 1952-02-03 | Kristen | Frijda | M | 1986-05-31 |
| 27259 | 1952-02-03 | Michaela | Lipner | M | 1997-09-05 |
| 32094 | 1952-02-03 | Mohd | Buchter | F | 1986-03-16 |
| 32641 | 1952-02-03 | Tua | Tetzlaff | F | 1988-12-01 |
| 41374 | 1952-02-03 | JiYoung | Schurmann | M | 1988-01-25 |
| 43737 | 1952-02-03 | Debatosh | Beerel | F | 1987-12-07 |
| 56225 | 1952-02-03 | Miquel | Rusmann | F | 1990-06-17 |
| 59577 | 1952-02-03 | Adel | Swiler | F | 1986-11-27 |
+--------+------------+------------+-----------+--------+------------+
-- 错误的结果，日期直接跳到了02-03
select * from employees where (birth_date,emp_no) > ('1952-02-02', 10010) order by birth_date limit 10;
+--------+------------+------------+-------------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-------------+--------+------------+
| 12282 | 1952-02-02 | Tadahiro | Delgrange | M | 1997-01-09 |
| 13944 | 1952-02-02 | Takahito | Maierhofer | M | 1989-01-18 |
| 22614 | 1952-02-02 | Dung | Madeira | M | 1989-01-24 |
| 29456 | 1952-02-02 | Barun | Krohm | F | 1992-11-23 |
| 33131 | 1952-02-02 | Reinhold | Savasere | M | 1998-01-30 |
| 40660 | 1952-02-02 | Piyush | Erbe | F | 1988-04-04 |
| 48910 | 1952-02-02 | Zhongwei | DuBourdieux | M | 1999-12-19 |
| 51486 | 1952-02-02 | Jianwen | Sigstam | F | 1989-07-20 |
| 59884 | 1952-02-02 | Fan | Przulj | M | 1991-09-25 |
| 61382 | 1952-02-02 | Kristof | Ranft | M | 1989-04-21 |
+--------+------------+------------+-------------+--------+------------+
-- 正确的结果
select o_orderkey,o_orderstatus,o_totalprice from orders where(o_orderdate,o_orderkey) > ('1992-01-01',88199) order by o_orderdate limit 10;
select o_orderkey,o_orderstatus,o_totalprice from orders order by o_orderdate limit 10,10;
-- 结果一致
当然分页推荐使用redis来做，用数据库实现其实意义不大