mysql8 查询关键字、多表关系

一、查询关键字

1、数据准备

> create table emp(
                         ->   id int primary key  auto_increment,
                         ->   name varchar(20)  not null,
                         ->   sex enum('male','female')  not null default  'male',
                         ->   age smallint(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
('tom','male',78,'20150302','teacher',1000000.31,401,1),#以下是教学部
('kevin','male',81,'20130305','teacher',8300,401,1),
('tony','male',73,'20140701','teacher',3500,401,1),
('owen','male',28,'20121101','teacher',2100,401,1),
('jack','female',18,'20110211','teacher',9000,401,1),
('jenny','male',18,'19000301','teacher',30000,401,1),
('sank','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);

2、查询关键字之where筛选

模糊查询:没有明确的筛选条件
关键字:like
关键符号:
　　%:匹配任意个数任意字符
　　_:匹配单个个数任意字符
show variables like '%mode%se';

1. 查询id大于等于3小于等于6的数据

> select * from emp where id >= 3 and id <=6;

> select * from emp where id between 3 and 6;

2. 查询薪资是20000或者18000或者17000的数据

> select * from emp where salary=20000 or salary = 18000 or salary  =17000;

> select * from emp where salary in (20000,18000,17000);

3. 查询员工姓名中包含o字母的员工姓名和薪资

> select name, salary from emp where name like '%o%';

> select name, salary from emp where name like 'o%';

> select name, salary from emp where name like '%o';

4. 查询员工姓名是由四个字符组成的员工姓名与其薪资

> select name, salary from emp where name like '____';

> select name, salary from emp where char_length(name)=4;

5. 查询id小于3或者大于6的数据

> select *  from emp where id < 3 or id > 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. 查询岗位描述为空的员工名与岗位名针对null不能用等号，只能用is

> select name,post from emp where post_comment = NULL;  # 查询为空

> select name,post from emp where post_comment is NULL;

> select name,post from emp where post_comment is not NULL;

'''在sql中，NULL和''不一样'''
# 假如字段想设置为空，该如何设置?
最好设置为'',不要使用NULL
尤其是两者混用

3、查询关键字之group by分组

GROUP BY 是一种用于对查询结果进行分组的语句，通常与聚合函数（如 sum max min avg count 等）一起使用，以便在每个组中计算聚合值。

当使用 GROUP BY 语句时，查询结果将根据指定的列或表达式进行分组。相同的值将被分配到同一组中。然后，可以对每个组应用聚合函数，以计算该组的聚合结果。

支持多个分组

select name,age, count(*) from emp group by name,age;

分组之后，得到的每一个分组中的第一条数据

1. 按部门分组

select post from emp group by post;

2. 每个部门的最低工资

> select post, min(salary) from emp group by post;

3. 每个部门的平均工资

select post, avg(salary) from emp group by post;

4. 每个部门的工资总和

select post, sum(salary) from emp group by post;

5. 每个部门的人数

> select post, count(*) from emp group by post;

> select post, count(id) from emp group by post;

> select post, count(1) from emp group by post;

注意：

* 表示取最长的一列 id 表示取id那一列 1 表示取第一列

6、配合分组使用的其他函数，分组之后只能获取到分组的依据，获取分组之外的字段

group_concat:

GROUP_CONCAT() 是 MySQL 8 中的一个聚合函数，用于将多行数据中的某一列的值拼接成一个字符串。

GROUP_CONCAT([DISTINCT] expr [, expr ...]
[ORDER BY {unsigned_integer | col_name | expr}
[ASC | DESC] [,col_name ...]]
[SEPARATOR str_val])

实例：查询每个岗位下的员工名

SELECT post AS '岗位', GROUP_CONCAT(name) as '员工' from emp GROUP BY post;

+-----------+-------------------------------------+
| 岗位      | 员工                                |
+-----------+-------------------------------------+
| operation | 僧龙,程咬金,程咬银,程咬铜,程咬铁    |
| sale      | 哈哈,呵呵,西西,乐乐,拉拉            |
| teacher   | tom,kevin,tony,owen,jack,jenny,sank |
+-----------+-------------------------------------+

concat：不分组使用

CONCAT() 是 MySQL 8 中的一个字符串函数，用于将多个字符串拼接在一起

> select concat(name, sex) from emp;

> select concat(name,'|', sex) from emp;
+-----------------------+
| concat(name,'|', sex) |
+-----------------------+
| tom|male              |
| kevin|male            |
| tony|male             |
| owen|male             |
| jack|female           |
| jenny|male            |
| sank|male             |
| 哈哈|female           |
| 呵呵|female           |
| 西西|female           |
| 乐乐|female           |
| 拉拉|female           |
| 僧龙|male             |
| 程咬金|male           |
| 程咬银|female         |
| 程咬铜|male           |
| 程咬铁|female         |
+-----------------------+

concat_ws()

CONCAT_WS() 是 MySQL 8 中的一个字符串函数，用于将多个字符串拼接在一起，并使用指定的分隔符分隔它们。语法： CONCAT_WS(separator, str1, str2, ..., strN)

CONCAT_WS() 还可以用于连接表中的列, SELECT CONCAT_WS('-', first_name, last_name) AS full_name FROM employees;

示例：

> SELECT CONCAT_WS('-', name, age) AS full_name FROM emp;

+-----------+
| full_name |
+-----------+
| tom-78    |
| kevin-81  |
| tony-73   |
| owen-28   |
| jack-18   |
| jenny-18  |
| sank-48   |
| 哈哈-48   |
| 呵呵-38   |
| 西西-18   |
| 乐乐-18   |
| 拉拉-28   |
| 僧龙-28   |
| 程咬金-18 |
| 程咬银-18 |
| 程咬铜-18 |
| 程咬铁-18 |
+-----------+

select post,concat_ws('|', name, age, gender) from emp group by post;

7、查询每个部门的最高工资

> select post as '部门',max(salary) as '最高工资' from emp group by post;

+-----------+------------+
| 部门      | 最高工资   |
+-----------+------------+
| teacher   | 1000000.31 |
| sale      |    4000.33 |
| operation |   20000.0  |
+-----------+------------+

4、关键字之having过滤，

having也是用来筛选数据的，功能上跟where是一样的，where 用在分组之前，having用在分组之后再筛选

统计各部门年龄在30岁以上的员工平均薪资,并且保留平均薪资大于10000的部门。

4.1 各部门年龄在30岁以上的员工平均薪资

> select avg(salary) from emp where age >30 group by post;
+-------------+
| avg(salary) |
+-------------+
| 255450.0775 |
|   2500.24   |
+-------------+

4.2 保留平均薪资大于10000的部门(having 用在分组之后再筛选)

> select avg(salary) from emp where age > 30 group by post having avg(salary) > 10000;

+-------------+
| avg(salary) |
+-------------+
| 255450.0775 |
+-------------+

5、关键字之distinct去重

对有重复的展示数据进行去重操作

> select distinct id,age from emp;

+----+-----+
| id | age |
+----+-----+
| 1  | 78  |
| 2  | 81  |
| 3  | 73  |
| 4  | 28  |
| 5  | 18  |
| 6  | 18  |
| 7  | 48  |
| 8  | 48  |
| 9  | 38  |
| 10 | 18  |
| 11 | 18  |
| 12 | 28  |
| 13 | 28  |
| 14 | 18  |
| 15 | 18  |
| 16 | 18  |
| 17 | 18  |
+----+-----+

> select distinct post from emp;
+-----------+
| post      |
+-----------+
| teacher   |
| sale      |
| operation |
+-----------+

6、关键字之order by排序

1、升序和降序

> select * from emp order by salary ;  # 默认就是升序asc
> select * from emp order by salary desc;  # 降序

2、先按照age降序排，在年轻相同的情况下再按照薪资升序排

order by 排序字段1 desc, 排序字段2;

> select * from emp order by age desc, salary;

3、统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序

> select  avg(salary) as avg_salary from emp where age > 10 group by post having avg(salary) > 1000  order by avg(salary) desc ;

+---------------+
| avg_salary    |
+---------------+
| 151842.901429 |
|  16800.026    |
|   2600.294    |
+---------------+

7、关键字之limit分页

查询工资最高的人的详细信息

> select * from emp order by  salary desc limit 1;

limit 的分页参数

select * from emp limit 0,5; # 第一个参数表示起始位置，第二个参数表示的是条数，不是索引位置
select * from emp limit 5,5; # 第一个参数代表的是起始位置，第二个参数是总的要查条数

8、关键字之regexp正则

> select * from emp where name regexp  '^j.*(n|y)$';

+----+-------+------+-----+------------+---------+--------------+---------+--------+-----------+
| id | name  | sex  | age | hire_date  | post    | post_comment | salary  | office | depart_id |
+----+-------+------+-----+------------+---------+--------------+---------+--------+-----------+
| 6  | jenny | male | 18  | 1900-03-01 | teacher | <null>       | 30000.0 | 401    | 1         |
+----+-------+------+-----+------------+---------+--------------+---------+--------+-----------+

⚠️：

. 匹配除换行符以外的任意字符

* 匹配一次或更多次

9、作业练习

1. 查询岗位名以及岗位包含的所有员工名字
SELECT CONCAT_WS('|', post, name) AS post_and_name FROM emp GROUP BY post;

2. 查询岗位名以及各岗位内包含的员工个数
select post, count(name) from emp group by post;

3. 查询公司内男员工和女员工的个数
select sex, count(sex) from emp group by sex;

4. 查询岗位名以及各岗位的平均薪资
select post, avg(salary) from emp group by post;

5. 查询岗位名以及各岗位的最高薪资
select post, max(salary) from emp group by post;

6. 查询岗位名以及各岗位的最低薪资
select post, min(salary) from emp group by post;

7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
select sex, avg(salary) from emp group by sex;

二、表关系

1、一对一：作者和作者详情

外键字段建在哪里?
一对一的外键字段可以建在任何一张表中，但是，推荐建在查询频率较高的一张表中

作者详情表

create table author_detail(
	id int primary key auto_increment,
    addr varchar(32),
    height decimal(5,2)
);

作者表

create table author(
	id int primary key auto_increment,
    name varchar(32),
    author_detail_id int unique,
    foreign key (author_detail_id) references author_detail(id)
);

2、一对多：出版社和图书的关系

外键盘约束

外键约束：foreign key(字段) references 表名(字段)

一对多的表关系外键字段建在多的一方

on update cascade # 级联更新，一个表更新，另一个表也会更新
on delete cascade # 级联删除，一个表删除，另一个表也会删除

1. 在创建表的时候，应该先创建被关联表(没有外键字段的表)
2. 在录入数据的时候，应该先录入被关联表(没有外键字段的表)
3. 在录入数据的时候，应该录入被关联表中已经存在的值.
4. 如果对被关联表中的数据进行修改和删除的时候，需要把关联表中的数据也跟着修改或者删除(不现实)

在创建出版表

创建出版表

create table publish(
id int primary key auto_increment,
title varchar(128)
);

创建图书表

create table book(
	id int primary key auto_increment,
    title varchar(128),
    price decimal(8, 2),
    publish_id  int,
    foreign key(publish_id) references publish(id) \
    on update cascade  \ # 级联更新
    on delete cascade   # 集联删除
);

往出版社表中录入

insert into publish (title) values ('北京出版社');
insert into publish (title) values ('东京出版社');

往book表中录入数据

insert into book (title, price, publish_id) values('金梅', 1000, 1);
insert into book (title, price, publish_id) values('西游记', 1000, 2);

3、多对多：图书和作者的关系

先创建图书表和作者表

create table book(
id int primary key auto_increment,
title varchar(128),
price decimal(8, 2)
);

create table author(
id int primary key auto_increment,
name varchar(32)
);

建立第三张表来保存两张表的关系

create table book2author(
id int primary key auto_increment,
book_id  int,
author_id  int,
foreign key(book_id) references book(id) on update cascade on delete cascade,
foreign key(author_id) references author(id) on update cascade on delete cascade
);

插入数据

> INSERT INTO book(title, price) VALUES ('金梅', 18), ('水浒传', 20);

>  insert into author(name) values('刘成安'),('刘凯')

>  insert into book2author(book_id, author_id) values(1, 1),(1, 2),(2, 1);

posted @ 2023-07-12 15:40 凡人半睁眼阅读(29) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

海棠未雨，梨花先雪，一半春休

想看山海，早也去，晚也去，一个人也去

mysql8 查询关键字、多表关系

一、查询关键字

二、表关系

公告

海棠未雨，梨花先雪，一半春休

想看山海，早也去，晚也去 ，一个人也去

mysql8 查询关键字、多表关系

一、查询关键字

二、表关系

公告

想看山海，早也去，晚也去，一个人也去