Beherrschen Sie die grundlegenden Operationen von MySQL-Abfrageanweisungen
- coldplay.xixinach vorne
- 2020-12-01 17:17:313994Durchsuche
MySQL-Video-TutorialDie Spalte stellt die Funktionsweise von Abfrageanweisungen vor
Verwandte kostenlose Lernempfehlungen: MySQL-Video-Tutorial
1. Grundlegende Operationen von Abfrageanweisungen
1.查询语句的基本操作 - select - from - where:约束条件 - group by:分组 - having：过滤 - distinct：去重 - order by：排序 - limit：限制查询记录的数量 - 聚合函数: count（计数） max（最大值） min（最小值） avg（平均值） sum(求和)
2 1. Vorläufige Tabelle und Datenvorbereitung Auf die Aggregationsfunktion muss folgen gruppieren nach Behind (Ausführungsreihenfolge);
# 创建一张部门表
create table emp(
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
);
# 插入记录
# 三个部门：教学，销售，运营
insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
('tank','male',17,'20170301','张江第一帅形象代言部门',7300.33,401,1), # 以下是教学部
('egon','male',78,'20150302','teacher',1000000.31,401,1),
('kevin','male',81,'20130305','teacher',8300,401,1),
('jason','male',73,'20140701','teacher',3500,401,1),
('owen','male',28,'20121101','teacher',2100,401,1),
('jerry','female',18,'20110211','teacher',9000,401,1),
('大饼','male',18,'19000301','teacher',30000,401,1),
('sean','male',48,'20101111','teacher',10000,401,1),
('歪歪','female',48,'20150311','sale',3000.13,402,2),# 以下是销售部门
('丫丫','female',38,'20101101','sale',2000.35,402,2),
('丁丁','female',18,'20110312','sale',1000.37,402,2),
('星星','female',18,'20160513','sale',3000.29,402,2),
('格格','female',28,'20170127','sale',4000.33,402,2),
('张野','male',28,'20160311','operation',10000.13,403,3), # 以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3);
# PS：如果在windows系统中，插入中文字符，select的结果为空白，可以将所有字符编码统一设置成gbk
- select * from emp; # 若数据比较多，比较凌乱，可以在表后面+ \G
- select * from emp\G
- select * from emp; # 若数据比较多，比较凌乱，可以在表后面+ \G - select * from emp\GErgänzung: concat
写SQL语句必须遵循两点:
- 书写顺序:
# 获取id为 4、5的两条记录
select * from emp where id > 3 and id < 6;
- select
- from
- where
- 执行顺序:
比如: 图书管理员，得先找到是哪一个图书馆(哪张表),
再找这本书在图书馆的哪个位置(哪一条记录), 最后查找这个本书中某一页(哪些字段值);
select * from emp where id > 3 and id < 6;
- from ---> 找到图书馆
- where ---> 找到书的位置
- select ---> 找到书本中的某一页
注意: 必须记住SQL语句的 书写顺序 与 执行顺序(*******);3.having (Filter)# PS: 根据执行顺序来书写 SQL语句，一步一步来写;
# 1.查询id大于等于3小于等于6的数据(and:与)
select * from emp where id >= 3 and id <= 6;
mysql> SELECT * FROM EMP WHERE ID > 3 AND ID <= 6;
+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+
| 4 | jason | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 |
| 5 | owen | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 |
| 6 | jerry | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 |
+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+
3 rows in set (0.00 sec)
#可以使用between()and() :两者之间
mysql> select * from emp where id between 3 and 6;
+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+
| 3 | kevin | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 |
| 4 | jason | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 |
| 5 | owen | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 |
| 6 | jerry | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 |
+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+
4 rows in set (0.00 sec)
# 2.查询薪资是20000或者18000或者17000的数据
# or: 或者
select * from emp where salary=20000 or salary=18000 or salary=17000;
# in: 在什么里
select * from emp where salary in (20000, 18000, 17000);
mysql> select * from emp where salary = 20000 or salary = 18000 or salary = 17000;
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 |
| 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 |
| 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 |
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
3 rows in set (0.00 sec)
# 也可以使用in(3,4,5,6)
select * from emp where id in(3,4,5,6);
+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+
| 3 | kevin | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 |
| 4 | jason | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 |
| 5 | owen | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 |
| 6 | jerry | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 |
+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+
4 rows in set (0.00 sec)
# 3.查询员工姓名中包含o字母 的 员工姓名和薪资
# like: 模糊匹配
# %: 匹配0个或多个任意字符(若前后都有%(%o%),查所有带有索引字符的记录,若头有%而尾没有(%o),则查所有尾带有索引字符的记录,若尾有%而头没有(o%),则查所有头带有索引字符的记录,若前后都没有%(o),则只会查询只有索引字符的字符)
# _: 匹配一个任意字符
select name, salary from emp where name like "%o%";
+-------+------------+
| name | salary |
+-------+------------+
| egon | 1000000.31 |
| jason | 3500.00 |
| owen | 2100.00 |
+-------+------------+
3 rows in set (0.00 sec)
mysql> select name, salary from emp where name like "o";
+------+--------+
| name | salary |
+------+--------+
| o | NULL |
+------+--------+
1 row in set (0.00 sec)
# 4.查找名字个数为3个的员工 名字 与 薪资
select name, salary from emp where name like "___";
+-----------+----------+
| name | salary |
+-----------+----------+
| 程咬金 | 20000.00 |
| 程咬银 | 19000.00 |
| 程咬铜 | 18000.00 |
| 程咬铁 | 17000.00 |
+-----------+----------+
4 rows in set (0.00 sec)
# 或者 # char_length(name): 计算名字字符的长度
select name, salary from emp where char_length(name) = 4;
mysql> select name, salary from emp where char_length(name) = 3;
+-----------+----------+
| name | salary |
+-----------+----------+
| 程咬金 | 20000.00 |
| 程咬银 | 19000.00 |
| 程咬铜 | 18000.00 |
| 程咬铁 | 17000.00 |
+-----------+----------+
4 rows in set (0.00 sec)
# 5.查询id小于3或者大于6的数据
# not in: 不再什么什么中
select * from emp where id not in (3, 4, 5, 6);
select * from emp where id not between 3 and 6;
# 6.查询薪资不在20000，18000，17000范围的数据
select * from emp where salary not in (20000, 18000, 17000);
# 7.查询岗位描述为空的 员工名 与 岗位名 post_comment
# 用等于号无法判定空字段
select name, post from emp where post_comment = null;
select * from emp where post_comment = null;
Empty set (0.00 sec)
# 注意: 针对null的值 需要使用 is
select name, post from emp where post_comment is null;
select name, post_comment from emp where post_comment is null;
+-----------+--------------+
| name | post_comment |
+-----------+--------------+
| tank | NULL |
| egon | NULL |
| kevin | NULL |
| jason | NULL |
| owen | NULL |
| jerry | NULL |
| 大饼 | NULL |
| sean | NULL |
| 歪歪 | NULL |
| 丫丫 | NULL |
| 丁丁 | NULL |
| 星星 | NULL |
| 格格 | NULL |
| 张野 | NULL |
| 程咬金 | NULL |
| 程咬银 | NULL |
| 程咬铜 | NULL |
| 程咬铁 | NULL |
| o | NULL |
+-----------+--------------+
19 rows in set (0.00 sec)
4.distinct (Duplikat entfernen) - 书写顺序:
- select
- from
- where
- group by
- 执行顺序:
- from
- where
- group by
- select
# 什么叫分组
# 比如: 一张员工表中有性别字段，可以根据性别分组，一组是男性，一组是女性，或者是根据部门分组，有教学部、销售部等...
# 1.根据部门分组
# 非严格模式下可以获取 分组条件post 以外的字段数据
select post, salary from emp group by post;
# 设置严格模式:
show variables like "%mode%";
# 全局设置: 永久有效
set global sql_mode="strict_trans_tables,only_full_group_by";
"""
设置sql_mode为only_full_group_by，意味着以后但凡分组，只能取到分组的依据，
不应该在去取组里面的单个元素的值，那样的话分组就没有意义了，并且会报错,因为不分组就是对单个元素信息的随意获取
"""
mysql> select post, salary from emp group by post;
ERROR 1046 (3D000): No database selected
# 可以同聚合函数，间接获取其他字段数据
聚合函数:
count: 计数
max: 最大值
min: 最小值
avg: 平均值
sum: 求和
group_concat(name): 可以将分组后的 所有名字获取并进行拼接
# 指定以:拼接
select post, group_concat(name) from emp group by post;
select post, group_concat(name, ':') from emp group by post;
2.获取每个 部门 的最高工资
select post,max(salary) from emp group by post;
mysql> select post,max(salary) from emp group by post;
+-----------------------------------+-------------+
| post | max(salary) |
+-----------------------------------+-------------+
| NULL | NULL |
| operation | 20000.00 |
| sale | 4000.33 |
| teacher | 1000000.31 |
| 张江第一帅形象代言部门 | 7300.33 |
+-----------------------------------+-------------+
5 rows in set (0.00 sec)
补充: as 别名: 可以给字段 加一个 别名
select post as '部门', max(salary) as '薪资'
from emp group by post;
# 也可以简写,但是不推荐
select post '部门', max(salary) '薪资'
from emp group by post;
mysql> select post as '部门', max(salary) as'薪资'
-> from emp group by post;
+-----------------------------------+------------+
| 部门 | 薪资 |
+-----------------------------------+------------+
| NULL | NULL |
| operation | 20000.00 |
| sale | 4000.33 |
| teacher | 1000000.31 |
| 张江第一帅形象代言部门 | 7300.33 |
+-----------------------------------+------------+
5 rows in set (0.00 sec)
3.每个部门的最低工资
select post, min(salary) from emp group by post;
4.每个部门的平均工资
select post, avg(salary) from emp group by post;
5.每个部门的工资总和
select post, sum(salary) from emp group by post;
6.每个部门的员工个数
# count(): 括号中可以填任意非空值
select post, count(salary) from emp group by post;
select post, count(post_comment) from emp group by post;
查询岗位名以及各岗位内包含的员工个数
select post, count(id) from emp group by post;
查询公司内男员工和女员工的个数
select sex, count(*) from emp group by sex;
查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
select sex, avg(salary) from emp group by sex;
.统计各部门年龄在30岁以上的员工平均工资:
# 步骤: 先找到表，再找年龄30岁以上，再根据部门分组，最后求平均薪资;
select post, avg(salary) from emp where age > 30 group by post;
5.Ordnung nach (Sortierung)# 聚合函数: 若没有group by 分组，默认将查出来的数据当做一个分组, 也能使用;
select max(salary) from emp;
9. limit( Anzahl der Abfragedatensätze begrenzen)
# group_concat(name): 可以将分组后的 所有名字获取并进行拼接
# 默认以, 拼接
select post, group_concat(name) from emp group by post;
# 指定以:拼接
select post, group_concat(name, ':') from emp group by post;
#
select post, group_concat('Name: ', name) from emp group by post;
select post, group_coucat(name) from emp group by post;
mysql> select post, group_concat(name) from emp group by post;
+-----------------------------------+------------------------------------------------+
| post | group_concat(name) |
+-----------------------------------+------------------------------------------------+
| NULL | o |
| operation | 程咬铁,程咬铜,程咬银,程咬金,张野 |
| sale | 格格,星星,丁丁,丫丫,歪歪 |
| teacher | sean,大饼,jerry,owen,jason,kevin,egon |
| 张江第一帅形象代言部门 | tank |
+-----------------------------------+------------------------------------------------+
5 rows in set (0.00 sec)
# 给每个部门的员工名字前 + NB_
select post, group_concat('NB_', name) from emp group by post;
# 拼接部门员工名字+薪资
select post, group_concat(name, ":", salary) from emp group by post;
10. Regulär # 4.补充concat（不分组时用）拼接字符串达到更好的显示效果 as语法并用
select concat('Name: ', name) as '名字', concat('Sal: ', salary) as '薪资' from emp;
mysql> select concat('name: ', name) as '名字',concat('sal: ',salary) as '薪资' from emp;
+-----------------+-----------------+
| 名字 | 薪资 |
+-----------------+-----------------+
| name: tank | sal: 7300.33 |
| name: egon | sal: 1000000.31 |
| name: kevin | sal: 8300.00 |
| name: jason | sal: 3500.00 |
| name: owen | sal: 2100.00 |
| name: jerry | sal: 9000.00 |
| name: 大饼 | sal: 30000.00 |
| name: sean | sal: 10000.00 |
| name: 歪歪 | sal: 3000.13 |
| name: 丫丫 | sal: 2000.35 |
| name: 丁丁 | sal: 1000.37 |
| name: 星星 | sal: 3000.29 |
| name: 格格 | sal: 4000.33 |
| name: 张野 | sal: 10000.13 |
| name: 程咬金 | sal: 20000.00 |
| name: 程咬银 | sal: 19000.00 |
| name: 程咬铜 | sal: 18000.00 |
| name: 程咬铁 | sal: 17000.00 |
| name: o | NULL |
+-----------------+-----------------+
19 rows in set (0.00 sec) 3. Abfrage mehrerer Tabellen Abfrage mehrerer Tabellen
-Assoziative Abfrage -UnterabfrageTabelle erstellen und Datenvorbereitung einfügen
1.having与where语法一样，只不过having必需要在group by后使用;
2.where 不能使用聚合函数，但having可以;
- 书写顺序:
- select
- from
- where
- group by
- having
- 执行顺序:
- from
- where
- group by
- having
- select
1、统计各 部门 年龄在30岁以上的员工平均工资，并且保留平均工资大于10000的部门;
select post, avg(salary) from emp where age > 30 group by post having avg(salary) > 10000;
mysql> select post, avg(salary) from emp where age > 30 group by post having avg(salary) > 10000;
+---------+---------------+
| post | avg(salary) |
+---------+---------------+
| teacher | 255450.077500 |
+---------+---------------+
1 row in set (0.00 sec)
1. Verwandte Abfrage
# distinct: 去重
- 书写顺序:
- select
- distinct
- from
- where
- group by
- having
执行顺序:
- from
- where
- group by
- having
- select
- distinct
# 注意: 查询的字段值必须是重复的才有效，只要有一个字段值是不重复的就没有效果。
# 若所查字段内有不重复的字段记录就不会去重
select distinct id, post from emp;
mysql> select distinct id, post from emp;
+----+-----------------------------------+
| id | post |
+----+-----------------------------------+
| 1 | 张江第一帅形象代言部门 |
| 2 | teacher |
| 3 | teacher |
| 4 | teacher |
| 5 | teacher |
| 6 | teacher |
| 7 | teacher |
| 8 | teacher |
| 9 | sale |
| 10 | sale |
| 11 | sale |
| 12 | sale |
| 13 | sale |
| 14 | operation |
| 15 | operation |
| 16 | operation |
| 17 | operation |
| 18 | operation |
| 19 | NULL |
+----+-----------------------------------+
19 rows in set (0.00 sec)
select distinct post from emp;
mysql> select distinct post from emp;
+-----------------------------------+
| post |
+-----------------------------------+
| 张江第一帅形象代言部门 |
| teacher |
| sale |
| operation |
| NULL |
+-----------------------------------+
5 rows in set (0.00 sec)
- 书写顺序:
- select
- from
- where
- group by
- having
- order by
执行顺序:
- from
- where
- group by
- having
- select
- order by # 通过select 查出来的数据再进行排序
# order by 默认升序
# asc升序
# desc降序
# 1、根据薪资进行升序
select name, salary from emp order by salary; # 默认升序
mysql> select name, salary from emp order by salary;
+-----------+------------+
| name | salary |
+-----------+------------+
| o | NULL |
| 丁丁 | 1000.37 |
| 丫丫 | 2000.35 |
| owen | 2100.00 |
| 歪歪 | 3000.13 |
| 星星 | 3000.29 |
| jason | 3500.00 |
| 格格 | 4000.33 |
| tank | 7300.33 |
| kevin | 8300.00 |
| jerry | 9000.00 |
| sean | 10000.00 |
| 张野 | 10000.13 |
| 程咬铁 | 17000.00 |
| 程咬铜 | 18000.00 |
| 程咬银 | 19000.00 |
| 程咬金 | 20000.00 |
| 大饼 | 30000.00 |
| egon | 1000000.31 |
+-----------+------------+
19 rows in set (0.00 sec)
2.select * from emp order by salary asc; # 指定升序
3.select name, salary from emp order by salary desc; # 指定降序
mysql> select name, salary from emp order by salary desc;
+-----------+------------+
| name | salary |
+-----------+------------+
| egon | 1000000.31 |
| 大饼 | 30000.00 |
| 程咬金 | 20000.00 |
| 程咬银 | 19000.00 |
| 程咬铜 | 18000.00 |
| 程咬铁 | 17000.00 |
| 张野 | 10000.13 |
| sean | 10000.00 |
| jerry | 9000.00 |
| kevin | 8300.00 |
| tank | 7300.33 |
| 格格 | 4000.33 |
| jason | 3500.00 |
| 星星 | 3000.29 |
| 歪歪 | 3000.13 |
| owen | 2100.00 |
| 丫丫 | 2000.35 |
| 丁丁 | 1000.37 |
| o | NULL |
+-----------+------------+
19 rows in set (0.00 sec)
# 先按照age升序，再按照salary降序
select age, salary from emp order by age asc, salary desc;
# 统计 各部门(分组) 年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行升序序
select post, avg(salary) from emp where age > 10 group by post having avg(salary) > 1000 order by avg(salary);
rrree
2Das obige ist der detaillierte Inhalt vonBeherrschen Sie die grundlegenden Operationen von MySQL-Abfrageanweisungen. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!
Stellungnahme：
Dieser Artikel ist reproduziert unter:cnblogs.com. Bei Verstößen wenden Sie sich bitte an admin@php.cn löschen
Vorheriger Artikel：Was bedeutet eine Datenbanktransaktion?Nächster Artikel：Was bedeutet eine Datenbanktransaktion?