Verwandte kostenlose Lernempfehlungen: MySQL-Video-Tutorial
1.查询语句的基本操作 - select - from - where:约束条件 - group by:分组 - having：过滤 - distinct：去重 - order by：排序 - limit：限制查询记录的数量 - 聚合函数: count（计数） max（最大值） min（最小值） avg（平均值） sum(求和)
# 创建一张部门表
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\G
写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语句的 书写顺序 与 执行顺序(*******);# 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)
- 书写顺序: - 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;
# 聚合函数: 若没有group by 分组，默认将查出来的数据当做一个分组, 也能使用; select max(salary) from emp;
# 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;# 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)Abfrage mehrerer Tabellen
-Assoziative Abfrage -Unterabfrage1.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)
# 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);
Das 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!
MySQL ändert den Namen der Datentabelle
MySQL erstellt eine gespeicherte Prozedur
Der Unterschied zwischen Mongodb und MySQL
So überprüfen Sie, ob das MySQL-Passwort vergessen wurde
MySQL-Datenbank erstellen
MySQL-Standard-Transaktionsisolationsstufe
Der Unterschied zwischen SQL Server und MySQL
mysqlPasswort vergessen
![[Web-Frontend] Node.js-Schnellstart](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
