MySQL命令【查询】
MySQL查询
一、基本查询数据
1、全表查询
select * from 数据库名称; #全表查询
// #进入employees数据库
mysql> use employees;
Database changed
// #查看其中的所有表格
mysql> show tables;
+----------------------+
| Tables_in_employees |
+----------------------+
| current_dept_emp |
| departments |
| dept_emp |
| dept_emp_latest_date |
| dept_manager |
| employees |
| salaries |
| titles |
+----------------------+
8 rows in set (0.00 sec)
// #查询departments全表数据
mysql> select * from departments;
+---------+--------------------+
| dept_no | dept_name |
+---------+--------------------+
| d009 | Customer Service |
| d005 | Development |
| d002 | Finance |
| d003 | Human Resources |
| d001 | Marketing |
| d004 | Production |
| d006 | Quality Management |
| d008 | Research |
| d007 | Sales |
+---------+--------------------+
9 rows in set (0.00 sec)2、查询部分字段
desc 表格名称; #查询部分字段
// #查询dept_manager表格中的部分字段
mysql> desc dept_manager;
+-----------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| emp_no | int(11) | NO | PRI | NULL | |
| dept_no | char(4) | NO | PRI | NULL | |
| from_date | date | NO | | NULL | |
| to_date | date | NO | | NULL | |
+-----------+---------+------+-----+---------+-------+
4 rows in set (0.00 sec)3、查询数据信息的数量（计数）
1）查询一个表里面所有的数据信息
select count(*) from 表格名称; #查询⼀个表里面所有的数据信息
// #查询employees表格里的所有数据
mysql> select count(*) from employees;
+----------+
| count(*) |
+----------+
| 300024 |
+----------+
1 row in set (0.08 sec)2） 查询一个表里面某一条件的数据信息（见聚合函数）
二、条件过滤
1、多少行（limit）
select * from 表格名称 limit n; # 查询表格中前n行信息
select 条件字段,条件字段 from 表格名称 limit n; # 查询表格中条件字符的前n个信息
// #查看其中的所有表格
mysql> show tables;
+----------------------+
| Tables_in_employees |
+----------------------+
| current_dept_emp |
| departments |
| dept_emp |
| dept_emp_latest_date |
| dept_manager |
| employees |
| salaries |
| titles |
+----------------------+
8 rows in set (0.00 sec)
// #查询employees表格中前5行信息
mysql> select * from employees limit 5;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 |
| 10002 | 1964-06-02 | Bezalel | Simmel | F | 1985-11-21 |
| 10003 | 1959-12-03 | Parto | Bamford | M | 1986-08-28 |
| 10004 | 1954-05-01 | Chirstian | Koblick | M | 1986-12-01 |
| 10005 | 1955-01-21 | Kyoichi | Maliniak | M | 1989-09-12 |
+--------+------------+------------+-----------+--------+------------+
5 rows in set (0.00 sec)
// #查看employees表格
mysql> desc employees;
+------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no | int(11) | NO | PRI | NULL | |
| birth_date | date | NO | | NULL | |
| first_name | varchar(14) | NO | | NULL | |
| last_name | varchar(16) | NO | | NULL | |
| gender | enum('M','F') | NO | | NULL | |
| hire_date | date | NO | | NULL | |
+------------+---------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
//查看employees表格中的emp_no与birth_date前5条数据
mysql> select emp_no,birth_date from employees limit 5;
+--------+------------+
| emp_no | birth_date |
+--------+------------+
| 10001 | 1953-09-02 |
| 10002 | 1964-06-02 |
| 10003 | 1959-12-03 |
| 10004 | 1954-05-01 |
| 10005 | 1955-01-21 |
+--------+------------+
5 rows in set (0.00 sec)2、并且（and）
select * from 表格名称 where 字段='字段中的数据' and 字段='字段中的数据'; #查询同时满足条件的数据
// #查询employees表格中前5行信息
mysql> select * from employees limit 5;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 |
| 10002 | 1964-06-02 | Bezalel | Simmel | F | 1985-11-21 |
| 10003 | 1959-12-03 | Parto | Bamford | M | 1986-08-28 |
| 10004 | 1954-05-01 | Chirstian | Koblick | M | 1986-12-01 |
| 10005 | 1955-01-21 | Kyoichi | Maliniak | M | 1989-09-12 |
+--------+------------+------------+-----------+--------+------------+
5 rows in set (0.00 sec)
// #在employees表格中查找同时有着first_name字符中的georgi与last_name字符中的Facello的数据
mysql> select * from employees where first_name='georgi' and last_name='Facello';
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 |
| 55649 | 1956-01-23 | Georgi | Facello | M | 1988-05-04 |
+--------+------------+------------+-----------+--------+------------+
2 rows in set (0.13 sec)3、或者（or）
select * from 表格名称 where 字段='字段中的数据' or 字段='字段中的数据'; #查询满足其中一个条件的数据
mysql> select * from employees where first_name='Georgi' or last_name='Facello' limit 5;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 |
| 10327 | 1954-04-01 | Roded | Facello | M | 1987-09-18 |
| 10909 | 1954-11-11 | Georgi | Atchley | M | 1985-04-21 |
| 11029 | 1962-07-12 | Georgi | Itzfeldt | M | 1992-12-27 |
| 11430 | 1957-01-23 | Georgi | Klassen | M | 1996-02-27 |
+--------+------------+------------+-----------+--------+------------+
5 rows in set (0.00 sec)4、包含（in）
select * from 表格名称 where 字段 in ('条件','条件'); #查询表格中某个字段的指定数据
mysql> select * from employees where last_name in ('Christ','lamba','asdfg')limit 10;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 10128 | 1958-02-15 | Babette | Lamba | F | 1988-06-06 |
| 12499 | 1955-09-22 | Izaskun | Lamba | F | 1989-12-13 |
| 12909 | 1962-08-04 | Dipankar | Lamba | F | 1993-10-19 |
| 13648 | 1954-03-28 | Zejun | Lamba | F | 1985-11-16 |
| 13684 | 1955-05-03 | Elvia | Christ | M | 1985-02-06 |
| 14403 | 1963-01-09 | Stella | Christ | F | 1992-07-01 |
| 14564 | 1953-11-15 | Tadahiro | Christ | F | 1998-12-04 |
| 14634 | 1955-08-04 | Shirish | Lamba | F | 1989-05-08 |
| 14692 | 1952-08-16 | Annemarie | Lamba | M | 1990-08-01 |
| 14997 | 1956-07-22 | Kshitij | Lamba | M | 1985-05-22 |
+--------+------------+------------+-----------+--------+------------+
10 rows in set (0.02 sec)5、范围检查（between and）
select * from 表格名称 where 字段（日期为主） between '条件一' and '条件二'; #查询在表格中某一字符中从条件一到条件二的数据
mysql> select * from employees where hire_date between '1986-12-01' and '1986-12-31'
-> limit 5;
+--------+------------+------------+----------------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+----------------+--------+------------+
| 10004 | 1954-05-01 | Chirstian | Koblick | M | 1986-12-01 |
| 10103 | 1953-11-26 | Akemi | Birch | M | 1986-12-02 |
| 10168 | 1964-09-11 | Dharmaraja | Stassinopoulos | M | 1986-12-06 |
| 10204 | 1956-12-09 | Nevio | Ritcey | F | 1986-12-04 |
| 10268 | 1958-06-03 | Nishit | Siochi | M | 1986-12-17 |
+--------+------------+------------+----------------+--------+------------+
5 rows in set (0.02 sec)6、否定（not）
1）查看不符合条件的数据：not in
select * from 表格名称 where 字段 not in(条件);
2）查看不在范围内的数据：not between.....and.......
select * from 表格名称 where 字段 not between 条件一 and 条件二;
// #查询first_name中不包含Georgi的前5条数据；
mysql> select * from employees where first_name not in('Georgi') limit 5;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 10002 | 1964-06-02 | Bezalel | Simmel | F | 1985-11-21 |
| 10003 | 1959-12-03 | Parto | Bamford | M | 1986-08-28 |
| 10004 | 1954-05-01 | Chirstian | Koblick | M | 1986-12-01 |
| 10005 | 1955-01-21 | Kyoichi | Maliniak | M | 1989-09-12 |
| 10006 | 1953-04-20 | Anneke | Preusig | F | 1989-06-02 |
+--------+------------+------------+-----------+--------+------------+
5 rows in set (0.00 sec)
// #查询hire_date的范围不在1989-07-22（包括）到1999-10-10（包括）的前5条数据；
mysql> select * from employees where hire_date not between '1989-07-22' and '1999-10-10' limit 5;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 |
| 10002 | 1964-06-02 | Bezalel | Simmel | F | 1985-11-21 |
| 10003 | 1959-12-03 | Parto | Bamford | M | 1986-08-28 |
| 10004 | 1954-05-01 | Chirstian | Koblick | M | 1986-12-01 |
| 10005 | 1955-01-21 | Kyoichi | Maliniak | M | 1989-09-12 |
+--------+------------+------------+-----------+--------+------------+
5 rows in set (0.00 sec)7、模糊查询（%、^、$）
1）%匹配任意字符
select * from 表格名称 where 字段 like "字符%" limit n； #在字符后面匹配任意字符
select * from 表格名称 where 字段 like "%字符%" limit n; #在字符前后匹配任意字符
mysql> select * from employees where first_name like 'georgi%'limit 5;
+--------+------------+------------+-------------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-------------+--------+------------+
| 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 |
| 10909 | 1954-11-11 | Georgi | Atchley | M | 1985-04-21 |
| 11029 | 1962-07-12 | Georgi | Itzfeldt | M | 1992-12-27 |
| 11430 | 1957-01-23 | Georgi | Klassen | M | 1996-02-27 |
| 12157 | 1960-03-30 | Georgi | Barinka | M | 1985-06-04 |
+--------+------------+------------+-------------+--------+------------+
5 rows in set (0.01 sec)
mysql> select * from employees where first_name like '%er%'limit 5;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 10013 | 1963-06-07 | Eberhardt | Terkki | M | 1985-10-20 |
| 10014 | 1956-02-12 | Berni | Genin | M | 1987-03-11 |
| 10027 | 1962-07-10 | Divier | Reistad | F | 1989-07-07 |
| 10034 | 1962-12-29 | Bader | Swan | M | 1988-09-21 |
| 10058 | 1954-10-01 | Berhard | McFarlin | M | 1987-04-13 |
+--------+------------+------------+-----------+--------+------------+
5 rows in set (0.00 sec)2）_ _ _%匹配指定字符
select * from 表格名称 where 字段 like "_ _ _字符%" limit n; #在字符前面匹配三（任意数据）个字符
mysql> select * from employees where first_name like '___dr%'limit 5;
+--------+------------+------------+--------------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+--------------+--------+------------+
| 10090 | 1961-05-30 | Kendra | Hofting | M | 1986-03-14 |
| 11929 | 1959-05-18 | Kendra | Beeson | F | 1989-04-12 |
| 12296 | 1961-05-29 | Kendra | Garnier | M | 1993-01-08 |
| 12989 | 1961-03-02 | Kendra | Bugrara | F | 1985-10-15 |
| 16775 | 1962-11-12 | Kendra | Fortenbacher | F | 1990-01-10 |
+--------+------------+------------+--------------+--------+------------+
5 rows in set (0.02 sec)3）^匹配指定开头字符
select * from 表格名称 where 字段 rlike "字符^" limit n; #以字符开头匹配任意字符
mysql> select * from employees where last_name rlike '^MA' limit 5;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 10005 | 1955-01-21 | Kyoichi | Maliniak | M | 1989-09-12 |
| 10037 | 1963-07-22 | Pradeep | Makrucki | M | 1990-12-05 |
| 10085 | 1962-11-07 | Kenroku | Malabarba | M | 1994-04-09 |
| 10096 | 1954-09-16 | Jayson | Mandell | M | 1990-01-14 |
| 10124 | 1962-05-23 | Geraldo | Marwedel | M | 1991-09-05 |
+--------+------------+------------+-----------+--------+------------+
5 rows in set (0.00 sec)4）$匹配指定结尾字符
select * from 表格名称 where 字段 rlike "字符$" limit n; #以字符结尾匹配任意字符
mysql> select * from employees where last_name rlike 'er$' limit 5;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 10030 | 1958-07-14 | Elvis | Demeyer | M | 1994-02-17 |
| 10039 | 1959-10-01 | Alejandro | Brender | M | 1988-01-19 |
| 10049 | 1961-04-24 | Basil | Tramer | F | 1992-05-04 |
| 10054 | 1957-04-04 | Mayumi | Schueller | M | 1995-03-13 |
| 10061 | 1962-10-19 | Tse | Herber | M | 1985-09-17 |
+--------+------------+------------+-----------+--------+------------+
5 rows in set (0.00 sec)8、别名（as）
select 字段 as 别名 from 表格名称; #设置字段为某别名
mysql> select count(1) from employees;
+----------+
| count(1) |
+----------+
| 300024 |
+----------+
1 row in set (0.12 sec)
// #设定count（1）的别名为 总数
mysql> select count(1) as 总数 from employees;
+--------+
| 总数 |
+--------+
| 300024 |
+--------+
1 row in set (0.10 sec)9、排序（order by）
select * from 表格名称 order by 字段 desc limit n; #查询字段最高的信息
select * from 表格名称 order by 字段 asc limit n; #查询字段最低的信息
//#查询薪资最高的员工信息
mysql> select * from salaries order by salary desc limit 10;
+--------+--------+------------+------------+
| emp_no | salary | from_date | to_date |
+--------+--------+------------+------------+
| 43624 | 158220 | 2002-03-22 | 9999-01-01 |
| 43624 | 157821 | 2001-03-22 | 2002-03-22 |
| 254466 | 156286 | 2001-08-04 | 9999-01-01 |
| 47978 | 155709 | 2002-07-14 | 9999-01-01 |
| 253939 | 155513 | 2002-04-11 | 9999-01-01 |
| 109334 | 155377 | 2000-02-12 | 2001-02-11 |
| 109334 | 155190 | 2002-02-11 | 9999-01-01 |
| 109334 | 154888 | 2001-02-11 | 2002-02-11 |
| 109334 | 154885 | 1999-02-12 | 2000-02-12 |
| 80823 | 154459 | 2002-02-22 | 9999-01-01 |
+--------+--------+------------+------------+
10 rows in set (1.09 sec)
////#查询薪资最低的员工信息
mysql> select * from salaries order by salary asc limit 10;
+--------+--------+------------+------------+
| emp_no | salary | from_date | to_date |
+--------+--------+------------+------------+
| 253406 | 38623 | 2002-02-20 | 9999-01-01 |
| 49239 | 38735 | 1996-09-17 | 1997-09-17 |
| 281546 | 38786 | 1996-11-13 | 1997-06-26 |
| 15830 | 38812 | 2001-03-12 | 2002-03-12 |
| 64198 | 38836 | 1989-10-20 | 1990-10-20 |
| 475254 | 38849 | 1993-06-04 | 1994-06-04 |
| 50419 | 38850 | 1996-09-22 | 1997-09-22 |
| 34707 | 38851 | 1990-10-03 | 1991-10-03 |
| 49239 | 38859 | 1995-09-18 | 1996-09-17 |
| 274049 | 38864 | 1996-09-01 | 1997-09-01 |
+--------+--------+------------+------------+
10 rows in set (1.09 sec)10、聚合（group by、sum、min、max、agv、year、distinct、having）
1）相同结果聚合（group by）
select 字段1,count(1) from 表格目录 group by 字段2; #查询字段的数据条数；
// #把salary字段的所有数据中相同的进行数据聚合在一起
// #根据数据的多少按照从高到低的顺序进行排列
// #将count(1)的别名设置为count
mysql> select salary,count(1) as count from salaries group by salary order by count desc limit 10;
+--------+-------+
| salary | count |
+--------+-------+
| 40000 | 95373 |
| 59114 | 103 |
| 51084 | 101 |
| 49321 | 101 |
| 58195 | 101 |
| 53869 | 100 |
| 52254 | 99 |
| 44390 | 99 |
| 55492 | 99 |
| 60357 | 98 |
+--------+-------+
10 rows in set (2.38 sec)
//验证count(1)的数值
mysql> select count(1) from salaries where salary=40000;
+----------+
| count(1) |
+----------+
| 95373 |
+----------+
1 row in set (0.79 sec)2）最大（max）
select max(字段) from 表格名称; #查询某个表中某个字段的最大值
mysql> select max(salary) from salaries;
+-------------+
| max(salary) |
+-------------+
| 158220 |
+-------------+
1 row in set (0.84 sec)3）最小（min）
select min(字段) from 表格名称; #查询某个表中某个字段的最小值
mysql> select min(salary) from salaries;
+-------------+
| min(salary) |
+-------------+
| 38623 |
+-------------+
1 row in set (0.84 sec)4）平均（agv）
select agv(字段) from 表格名称; #查询某个表中某个字段的平均值
mysql> select avg(salary) from salaries;
+-------------+
| avg(salary) |
+-------------+
| 63810.7448 |
+-------------+
1 row in set (0.83 sec)5）和值（sum）
select sum(字段) from 表格名称; #查询某个表中某个字段的和值
mysql> select sum(salary) from salaries;
+--------------+
| sum(salary) |
+--------------+
| 181480757419 |
+--------------+
1 row in set (0.81 sec)6）显示年份（year）
select year(字段) from 表格名称; #查询某个表中某个字段的日期
mysql> select year(from_date) from salaries limit 5;
+-----------------+
| year(from_date) |
+-----------------+
| 1986 |
| 1987 |
| 1988 |
| 1989 |
| 1990 |
+-----------------+
5 rows in set (0.01 sec)举例
（1）按照年份，对员工的薪资进行从⾼到低的进行排序的命令
select year(from_date) as dateYear,sum(salary) as sumSalary from salaries group by dateYear order by sumSalary desc;
mysql> select year(from_date) as dateyear,sum(salary) as sumSalary from salaries group by dateyear order by sumSalary desc limit 10;
+----------+-------------+
| dateyear | sumSalary |
+----------+-------------+
| 2000 | 17535667603 |
| 2001 | 17507737308 |
| 1999 | 17360258862 |
| 1998 | 16220495471 |
| 1997 | 15056011781 |
| 1996 | 13888587737 |
| 1995 | 12638817464 |
| 1994 | 11429450113 |
| 2002 | 10243347616 |
| 1993 | 10215059054 |
+----------+-------------+
10 rows in set (1.90 sec)（2）按照年份，对员⼯的平均薪资进行从高到低的进行排序
select year(from_date) as dateyear,avg(salary) as avgSalary from salaries group by dateyear order by avgSalary desc limit 10;
mysql> select year(from_date) as dateyear,avg(salary) as avgSalary from salaries group by dateyear order by avgSalary desc limit 10;
+----------+------------+
| dateyear | avgSalary |
+----------+------------+
| 2002 | 72683.9397 |
| 2001 | 70694.9159 |
| 2000 | 68556.2781 |
| 1999 | 66525.3619 |
| 1998 | 65540.2683 |
| 1997 | 64565.4264 |
| 1996 | 63618.9426 |
| 1995 | 62681.0430 |
| 1994 | 61727.7589 |
| 1993 | 60753.6565 |
+----------+------------+
10 rows in set (1.88 sec)11、去重（distinct）
select distinct 字符 from 表格名称; #对表格名称里的前10条重复字符去重
mysql> select title from titles limit 10;
+-----------------+
| title |
+-----------------+
| Senior Engineer |
| Staff |
| Senior Engineer |
| Engineer |
| Senior Engineer |
| Senior Staff |
| Staff |
| Senior Engineer |
| Senior Staff |
| Staff |
+-----------------+
10 rows in set (0.01 sec)
mysql> select distinct title from titles limit 10;
+--------------------+
| title |
+--------------------+
| Senior Engineer |
| Staff |
| Engineer |
| Senior Staff |
| Assistant Engineer |
| Technique Leader |
| Manager |
+--------------------+
7 rows in set (0.25 sec)12、过滤（having）
select 字符 from 表格名称 having 字符>70000 limit 10; #从表格中获取字符的数据；取字符大于n的数据；
select 字符 from 表格名称 having 字符<70000 limit 10;#从表格中获取字符的数据；取字符小于n的数据；
select 字符 from 表格名称 having 字符=70000 limit 10;#从表格中获取字符的数据；取字符等于n的数据；
mysql> select salary from salaries having salary>70000 limit 10;
+--------+
| salary |
+--------+
| 71046 |
| 74333 |
| 75286 |
| 75994 |
| 76884 |
| 80013 |
| 81025 |
| 81097 |
| 84917 |
| 85112 |
+--------+
10 rows in set (0.00 sec)
mysql> select salary from salaries having salary<70000 limit 10;
+--------+
| salary |
+--------+
| 60117 |
| 62102 |
| 66074 |
| 66596 |
| 66961 |
| 65828 |
| 65909 |
| 67534 |
| 69366 |
| 40006 |
+--------+
10 rows in set (0.00 sec)
mysql> select salary from salaries having salary=70000 limit 10;
+--------+
| salary |
+--------+
| 70000 |
| 70000 |
| 70000 |
| 70000 |
| 70000 |
| 70000 |
| 70000 |
| 70000 |
| 70000 |
| 70000 |
+--------+
10 rows in set (0.16 sec)
