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、
模糊查询:没有明确的筛选条件
关键字: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、
对有重复的展示数据进行去重操作
> 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、
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、
查询工资最高的人的详细信息
> select * from emp order by salary desc limit 1;
limit 的分页参数
select * from emp limit 0,5; # 第一个参数表示起始位置，第二个参数表示的是条数，不是索引位置 select * from emp limit 5,5; # 第一个参数代表的是起始位置，第二个参数是总的要查条数
8、
> 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);
