表的简单查询
参考：https://www.cnblogs.com/clschao/articles/9995531.html
一：单表的查询：
查询数据的本质：mysql会去本地的硬盘上面找到对应的文件，然后打开文件，按照你的查询条件找到你需要的数据。
查询的方法
01：select * from , 这个select * 指的是要查询所有字段的数据
02：select distinct 字段1，字段2.... from 库名.表名 #from后面是说从库中的某个表中去查找，mysql会自动去这个库对应的文件夹
下去找和表对应的那个数据文件，找不到就报错，找点了就继续后续的操作。
03：where 条件 #从表中找符合条件的数据记录，where后面跟的是你的查询条件
04：group by field （字段） #分组
05：having 筛选(having 是对组进行删选) #过滤，过滤之后执行select后面的字段筛选，就是说我要先确定一下需要哪个字段的数据，
你查询的字段数据进行去重，然后再进行下面的操作。
06：order by field（字段） #将结果按照后面的字段进行排序
07：limit 限制条数 #将最后的结果加一个限制条数，就是说我要将锅炉或则说限制查询出来的数据记录的条数
例子：创建一个员工表：
01：创建表employee 表：
mysql> create table employee(
-> id int not null unique auto_increment,
-> name varchar(20) not null,
-> sex enum("male","female") not null default "male",
-> age int(3) unsigned not null default 28,
-> hire_date date not null,
-> post varchar(50),
-> post_comment varchar(100),
-> salary double(15,2),
-> office int,
-> depart_id int
-> );
Query OK, 0 rows affected (0.15 sec)
02：查看employee表的结构
mysql> desc employee;
+--------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| sex | enum('male','female') | NO | | male | |
| age | int(3) unsigned | NO | | 28 | |
| hire_date | date | NO | | NULL | |
| post | varchar(50) | YES | | NULL | |
| post_comment | varchar(100) | YES | | NULL | |
| salary | double(15,2) | YES | | NULL | |
| office | int(11) | YES | | NULL | |
| depart_id | int(11) | YES | | NULL | |
+--------------+-----------------------+------+-----+---------+----------------+
10 rows in set (0.00 sec)
View Code
03：往表里添加数据：
如果在windows系统中，插入中文字符，select的结果为空白，可以将所有字符编码统一设置成gbk
mysql> insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values
-> ("egon","male",18,"20170707","包子店",7300.33,401,1),
-> ("教师02","male",78,"20180808","teacher",10010.31,401,1),
-> ("销售01","female",46,20190909,"sale",3000.13,402,2),
-> ("销售02","female",23,201909010,"sale",3000.14,402,2),
-> ("运营01","male",26,20150505,"operation",1000.13,403,3),
-> ("运营02","male",26,20150506,"operation",1000.23,403,3);
Query OK, 6 rows affected, 1 warning (0.07 sec)
Records: 6 Duplicates: 0 Warnings: 1
04：查看数据：
ps:也可以用select * from employee 进行查询，但是这样查找的效率会比较低。
mysql> select id,name,sex,age,hire_date,post,post_comment,salary,office,depart_id from employee;
+----+----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| 1 | 教师01 | male | 18 | 2017-07-07 | 包子店 | NULL | 7300.33 | 401 | 1 |
| 2 | 教师02 | male | 78 | 2018-08-08 | teacher | NULL | 10010.31 | 401 | 1 |
| 3 | 销售01 | female | 46 | 2019-09-09 | sale | NULL | 3000.13 | 402 | 2 |
| 4 | 销售02 | female | 23 | 0000-00-00 | sale | NULL | 3000.14 | 402 | 2 |
| 5 | 运营01 | male | 26 | 2015-05-05 | operation | NULL | 1000.13 | 403 | 3 |
| 6 | 运营02 | male | 26 | 2015-05-06 | operation | NULL | 1000.23 | 403 | 3 |
+----+----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
6 rows in set (0.00 sec)
数据去重：
05:我们需要查询post，用select post from employee,但是我只想查询有几种职业。下面这种显示不是很方便
mysql> select post from employee;
+-----------+
| post |
+-----------+
| 包子店 |
| teacher |
| sale |
| sale |
| operation |
| operation |
+-----------+
6 rows in set (0.00 sec)
06：但是上面这种select post from employee 查询职业，会有重复的职业。所以我们需要去重。
用 distinct 即可。
mysql> select distinct post from employee;
+-----------+
| post |
+-----------+
| 包子店 |
| teacher |
| sale |
| operation |
+-----------+
4 rows in set (0.02 sec)
显示结果（结果已经去重了）：
mysql> select distinct post from employee;
+-----------+
| post |
+-----------+
| 包子店 |
| teacher |
| sale |
| operation |
+-----------+
4 rows in set (0.02 sec)
注意：注意一点，使用distinct对记录进行去重的时候，distinct必须写在所有查询字段的前面，不然会报错，当然有些特别的用法可以结合着写到字段的中间或者后面，
select post，sex from employee；
select distinct post，sex from employee；
数据进行四则运算查询：
07：将月薪进行四则运算：
先查月薪：
mysql> select salary from employee;
+----------+
| salary |
+----------+
| 7300.33 |
| 10010.31 |
| 3000.13 |
| 3000.14 |
| 1000.13 |
| 1000.23 |
+----------+
6 rows in set (0.00 sec)
将月薪进行四则运算，算成年薪：
mysql> select name,salary*12 from employee;
+----------+-----------+
| name | salary*12 |
+----------+-----------+
| egon | 87603.96 |
| 教师02 | 120123.72 |
| 销售01 | 36001.56 |
| 销售02 | 36001.68 |
| 运营01 | 12001.56 |
| 运营02 | 12002.76 |
+----------+-----------+
6 rows in set (0.04 sec)
注意，将月薪进行运算后得到后，原来的字段salary变成了 salary*12 ，salary*12其实也是一个别名，是mysql自动给加上的。
我们也可以给其用as命名，例如：salary*12 字段就变成 nianxin 了,但是这个新的字段只是在内存中，并不是我们存在硬盘中的。
mysql> select name,salary*12 as nianxinn from employee;
+----------+-----------+
| name | nianxinn |
+----------+-----------+
| egon | 87603.96 |
| 教师02 | 120123.72 |
| 销售01 | 36001.56 |
| 销售02 | 36001.68 |
| 运营01 | 12001.56 |
| 运营02 | 12002.76 |
+----------+-----------+
6 rows in set (0.03 sec)
自定义显示格式，自己规定查询结果的显示格式（ concat（））：
concat（） 函数用于连接字符串。
例子：
mysql> select concat("姓名:,name","年薪：salary") as Annual_salary from employee;
+-----------------------------+
| Annual_salary |
+-----------------------------+
| 姓名:,name年薪：salary |
| 姓名:,name年薪：salary |
| 姓名:,name年薪：salary |
| 姓名:,name年薪：salary |
| 姓名:,name年薪：salary |
| 姓名:,name年薪：salary |
+-----------------------------+
6 rows in set (0.04 sec)
分成两列查询：还可以分成两列来查询，例如：
mysql> select concat("姓名:,name","年薪:,salary*12") as annual_salary,concat("性别:sex") from employee;
+-------------------------------+----------------------+
| annual_salary | concat("性别:sex") |
+-------------------------------+----------------------+
| 姓名:,name年薪:,salary*12 | 性别:sex |
| 姓名:,name年薪:,salary*12 | 性别:sex |
| 姓名:,name年薪:,salary*12 | 性别:sex |
| 姓名:,name年薪:,salary*12 | 性别:sex |
| 姓名:,name年薪:,salary*12 | 性别:sex |
| 姓名:,name年薪:,salary*12 | 性别:sex |
+-------------------------------+----------------------+
6 rows in set (0.00 sec)
字符串拼接查询：cancat_ws() 第一个参数为分隔符来进行字符串拼接
mysql> select concat_ws("___",name,salary*12) as annual_salary from employee;
+----------------------+
| annual_salary |
+----------------------+
| egon___87603.96 |
| 教师02___120123.72 |
| 销售01___36001.56 |
| 销售02___36001.68 |
| 运营01___12001.56 |
| 运营02___12002.76 |
+----------------------+
6 rows in set (0.00 sec)
二：where约束
1：比较运算：〉，〈 ， 〉= ，〈=，〈〉，！=
2：between 80 and 100 :值在80到100之间
3：in(80,90,100) 值是80.或则90.或则100
4：like "tom%": pattern 可以是%或则_,%表示任意多字符，_表示一个字符
5：逻辑运算：在多个条件下可以直接使用逻辑运算符 and or not
条件查询：
01：单条件查询
还是以employee为例子：
查询月薪大约10000的teacher下的名字和月薪
mysql> select name,salary from employee where post="teacher" and salary>10000;
+----------+----------+
| name | salary |
+----------+----------+
| 教师02 | 10010.31 |
+----------+----------+
1 row in set (0.04 sec)
02：多条件查询：
mysql> select name,salary from employee where post="teacher" and salary>10000;
+----------+----------+
| name | salary |
+----------+----------+
| 教师02 | 10010.31 |
+----------+----------+
1 row in set (0.00 sec)
03:关键字 between 查询：（在--到--之间的）
# between
mysql> select name,salary from employee where salary between 10000 and 20000;
+----------+----------+
| name | salary |
+----------+----------+
| 教师02 | 10010.31 |
+----------+----------+
1 row in set (0.03 sec)
# not between
mysql> select name,salary from employee where salary not between 10000 and 20000;
+----------+---------+
| name | salary |
+----------+---------+
| egon | 7300.33 |
| 销售01 | 3000.13 |
| 销售02 | 3000.14 |
| 运营01 | 1000.13 |
| 运营02 | 1000.23 |
+----------+---------+
5 rows in set (0.00 sec)
04:关键字is null (关键字IS NULL(判断某个字段是否为NULL不能用等号，需要用IS) 判断null只能用is)
mysql> select name,post_comment from employee
-> where post_comment is null;
+----------+--------------+
| name | post_comment |
+----------+--------------+
| egon | NULL |
| 教师02 | NULL |
| 销售01 | NULL |
| 销售02 | NULL |
| 运营01 | NULL |
| 运营02 | NULL |
+----------+--------------+
6 rows in set (0.00 sec)
05:查询空字符串：
注意''是空字符串，不是null，两个是不同的东西，null是啥也没有，''是空的字符串的意思，是一种数据类型，null是另外一种数据类型
mysql> select name,post_comment from employee where post_comment="";
Empty set (0.00 sec)
06：关键字in集合查询(判断是我们要找数据的是否在这个集合中）
mysql> select name,salary from employee where salary in(3000.14,3500,4000,9000);
+----------+---------+
| name | salary |
+----------+---------+
| 销售02 | 3000.14 |
+----------+---------+
1 row in set (0.00 sec)
07：关键字like 模糊查询：
通配符 * ：#匹配任意所有字符
select * from employee
通配符 _ : #匹配任意一个字符
select * from employee where name like "ag_";
这样会找不到数据，因为我只写了一个 _ ,egon后面还有两个字符，后面的没有匹配到，所以需要些两个_
错误示范：
mysql> select * from employee where name like "eg_";
Empty set (0.00 sec)
正确示范：
mysql> select * from employee where name like "eg__";
+----+------+------+-----+------------+-----------+--------------+---------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+------+------+-----+------------+-----------+--------------+---------+--------+-----------+
| 1 | egon | male | 18 | 2017-07-07 | 包子店 | NULL | 7300.33 | 401 | 1 |
+----+------+------+-----+------------+-----------+--------------+---------+--------+-----------+
1 row in set (0.00 sec)
mysql> select * from employee where name like "eg%";
+----+------+------+-----+------------+-----------+--------------+---------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+------+------+-----+------------+-----------+--------------+---------+--------+-----------+
| 1 | egon | male | 18 | 2017-07-07 | 包子店 | NULL | 7300.33 | 401 | 1 |
+----+------+------+-----+------------+-----------+--------------+---------+--------+-----------+
1 row in set (0.00 sec)
总结：where条件运行的时候，我们以select id,name,age from employee where id>7;这个语句来说，首先先找到employee表，找到这个表
之后，mysql会拿着where后面的约束条件去表里面找符合条件的数据，然后遍历你表中所有的数据，查看一下id是否大于7，逐条的对比，然后只
要发现id比7大的，它就会把这一整条记录给select，但是select说我只拿id、name、age这个三个字段里面的数据，然后就打印了这三个字
段的数据，然后where继续往下过滤，看看id是不是还有大于7的，然后发现一个符合条件的就给select一个，然后重复这样的事情，直到把数据
全部过滤一遍才会结束。这就是where条件的一个工作方式。
三：分组查询：（group by)
什么是分组：分组时在where之后得到的记录生成的，指的是将所有记录按照某个相同的字段进行分类，比如针对员工信息表的职位分类。或则按照性别分类等等。
为啥要分组：因为我们需要对数据以组为单位来统计一些数据或则计算。比如我们取工资：where salary 〉1000 group by post
01:group by 例子：
mysql> select * from employee group by post;
+----+----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| 5 | 运营01 | male | 26 | 2015-05-05 | operation | NULL | 1000.13 | 403 | 3 |
| 3 | 销售01 | female | 46 | 2019-09-09 | sale | NULL | 3000.13 | 402 | 2 |
| 2 | 教师02 | male | 78 | 2018-08-08 | teacher | NULL | 10010.31 | 401 | 1 |
| 1 | egon | male | 18 | 2017-07-07 | 包子店 | NULL | 7300.33 | 401 | 1 |
+----+----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
4 rows in set (0.00 sec)
02:only_full_group_by
01:先查询mysql默认的sql_mode:
mysql> select @@global.sql_mode;
+------------------------+
| @@global.sql_mode |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+
1 row in set (0.02 sec)
mysql> select * from employee group by post;
+----+----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| 5 | 运营01 | male | 26 | 2015-05-05 | operation | NULL | 1000.13 | 403 | 3 |
| 3 | 销售01 | female | 46 | 2019-09-09 | sale | NULL | 3000.13 | 402 | 2 |
| 2 | 教师02 | male | 78 | 2018-08-08 | teacher | NULL | 10010.31 | 401 | 1 |
| 1 | egon | male | 18 | 2017-07-07 | 包子店 | NULL | 7300.33 | 401 | 1 |
+----+----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
4 rows in set (0.00 sec)
设置only_full_group_by
mysql> set global sql_mode="only_full_group_by";
Query OK, 0 rows affected (0.03 sec)
设置后一定要推出，重新进入数据库，找到那个表：
#退出：
mysql> quit
Bye
#重新进入：
mysql> use day;
Database changed
#报错：意思是告诉我，select后面取的字段必须在你的group by后面的字段里面才行
mysql> select * from employee group by post;
ERROR 1055 (42000): 'day.employee.id' isn't in GROUP BY
#正确的写法：（id 在group by 后面的额post 的字段里面。
mysql> select post,count(id) from employee group by post; #count 做统计用的
+-----------+-----------+
| post | count(id) |
+-----------+-----------+
| operation | 2 |
| sale | 2 |
| teacher | 1 |
| 包子店 | 1 |
+-----------+-----------+
4 rows in set (0.01 sec)
02:按每个部门进行分类，并显示每个部门的员工信息。
我们显示每个部门的员工名字和薪资(按岗位分类，并显示每个部门的员工名字和他的薪资，并用逗号 ， 分割。
mysql> select post,group_concat(name,salary) from employee group by post;
+-----------+---------------------------------+
| post | group_concat(name,salary) |
+-----------+---------------------------------+
| operation | 运营011000.13,运营021000.23 |
| sale | 销售013000.13,销售023000.14 |
| teacher | 教师0210010.31 |
| 包子店 | egon7300.33 |
+-----------+---------------------------------+
4 rows in set (0.00 sec)
03：聚合函数（一般group by 都会和聚合函数一起使用，聚合就是将分组的数据聚集在一起，合并起来拿到一个最后的结果。
例子：
mysql> select post,count(id) as count from employee group by post;
+-----------+-------+
| post | count |
+-----------+-------+
| operation | 2 | #这个部门的人数出现了2次
| sale | 2 |
| teacher | 1 |
| 包子店 | 1 |
+-----------+-------+
4 rows in set (0.00 sec)
查看以下employee的数据：
mysql> select * from employee;
+----+----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| 1 | egon | male | 18 | 2017-07-07 | 包子店 | NULL | 7300.33 | 401 | 1 |
| 2 | 教师02 | male | 78 | 2018-08-08 | teacher | NULL | 10010.31 | 401 | 1 |
| 3 | 销售01 | female | 46 | 2019-09-09 | sale | NULL | 3000.13 | 402 | 2 |
| 4 | 销售02 | female | 23 | 0000-00-00 | sale | NULL | 3000.14 | 402 | 2 |
| 5 | 运营01 | male | 26 | 2015-05-05 | operation | NULL | 1000.13 | 403 | 3 |
| 6 | 运营02 | male | 26 | 2015-05-06 | operation | NULL | 1000.23 | 403 | 3 |
+----+----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
6 rows in set (0.00 sec)
关于集合函数，mysql提供了以下几种聚合函数：count、max、min、avg、sum等，上面的group_concat也算是一个聚合函数了，做字符串拼接的操作
ps聚合函数补充点：
01:计数使用count(*)
mysql> select count(*) from employee;
+----------+
| count(*) |
+----------+
| 6 |
+----------+
1 row in set (0.03 sec)
02:#后面跟where条件的意思是统计一下满足depart_id=1这个的所有记录的个数
mysql> select count(*) from employee where depart_id=1;
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
03: #max（）统计分组后每组的最大值，这里没有写group by，那么就是统计整个表中所有记录中薪资最大的，薪资的值
mysql> select max(salary) from employee;
+-------------+
| max(salary) |
+-------------+
| 10010.31 |
+-------------+
1 row in set (0.03 sec)
04:统计分组后的最小值
mysql> select min(salary) from employee;
+-------------+
| min(salary) |
+-------------+
| 1000.13 |
+-------------+
1 row in set (0.02 sec)
05：计算统计后的薪资的平均值。
mysql> select avg(salary) from employee;
+-------------+
| avg(salary) |
+-------------+
| 4218.545000 |
+-------------+
1 row in set (0.00 sec)
06：计算统计后薪资总和
mysql> select sum(salary) from employee;
+-------------+
| sum(salary) |
+-------------+
| 25311.27 |
+-------------+
1 row in set (0.00 sec)
07：计算满足where后面要求的薪资的总和
mysql> select sum(salary) from employee where depart_id=3;
+-------------+
| sum(salary) |
+-------------+
| 2000.36 |
+-------------+
1 row in set (0.00 sec)
四：having 过滤
01：以下这种查找方法也是可以的（和select id,name from employee; )是一样的。
mysql> select employee.id,employee.name from employee;
+----+----------+
| id | name |
+----+----------+
| 1 | egon |
| 2 | 教师02 |
| 3 | 销售01 |
| 4 | 销售02 |
| 5 | 运营01 |
| 6 | 运营02 |
+----+----------+
6 rows in set (0.00 sec)
02：但是当我们给新查找的结果起一个新的表名时候，会发现报错
mysql> select employee.id,employee.name from employee as biaoming;
ERROR 1054 (42S22): Unknown column 'employee.id' in 'field list'
mysql>
原因：因为这个语句先执行的是from，那么后面的as也是比select要先执行的，所以你先将表employee起了个新名字叫做tb1，
然后在tb1里面取查询数据，那么tb1里面找不到employee.id这个字段，就会报错，如果我们查询的
时候不带表名，你as来起一个新的表名也是没问题的。
03：having和where的区别。
五：limit 限制查询（ 经常做分页处理）
01：select * from 表名 order by salary
limit 3； #从0开始取3个数。
02：select * from 表名 order by salary
limit 5，5 #从第5个开始，即先查询第6个数
扫一扫
2034
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
