javaWeb-day02-MySQL(二)

模糊查询

LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式
可以和通配符 (%、)配合使用，其中"%“表示0或多个任意的字符。”"表示一个任意的字符
语法：SELECT 列 | * FROM 表名 WHERE 列名 LIKE 值
25 查询 emp表中姓名中包含"涛"字的员工，显示员工姓名

select name from emp where name like '%涛%';

26 查询 emp表中姓名中以"刘"字开头的员工，显示员工姓名

select name from emp where name like '刘%';

27 查询 emp表中姓名以"刘"开头，并且姓名为两个字的员工，显示员工姓名

select name from emp where name like '刘_';

多行函数查询

多行函数也叫做聚合(聚集)函数，根据某一列或所有列进行统计
常见的多行函数有：

多行函数	作用
COUNT( 列名 | * )	统计结果集中某一列或记录行的行数。
MAX( 列名 )	统计结果集中某一列值中的最大值
MIN( 列名 )	统计结果集中某一列值中的最小值
SUM( 列名 )	统计结果集中某一列所有值的和
AVG( 列名 )	统计结果集中某一列值的平均值

提示：(1)多行函数不能用在where子句中
(2)多行函数和是否分组有关，分组与否会直接影响多行函数执行的结果。

28 统计emp表中薪资大于3000的员工个数

select count(sal) from emp where sal >3000;

29 求emp表中的最高薪资

select max(sal) from emp;

30 统计emp表中所有员工的薪资总和(不包含奖金)

select sum(sal) from emp;

31.统计emp表员工的平均薪资(不包含奖金)

select avg(sal) from emp;

多行函数需要注意的问题:
多行函数和是否分组有关，如果查询结果中的数据没有经过分组，默认整个查询结果是一个组，多行函数就会默认统计当前这一个组的数据。产生的结果只有一个
如果查询结果中的数据经过分组（分的组不止一个），多行函数会根据分的组进行统计，有多少个组，就会统计出多少个结果

分组查询

GROUP BY 语句根据一个或多个列对结果集进行分组
在分组的列上我们可以使用 COUNT，SUM，AVG，MAX，MIN等函数
语法：SELECT 列 | * FROM 表名 [WHERE子句] GROUP BY 列;
32.对emp表按照部门对员工进行分组，查看分组后效果

select * from emp group by dept;

33.对emp表按照职位进行分组，并统计每个职位的人数，显示职位和对应人数

select job,count(job) from emp group by job;

34.对emp表按照部门进行分组，求每个部门的最高薪资(不包含奖金)，显示部门名称和最高薪资

select dept,max(sal) from emp group by dept;

排序查询

使用 ORDER BY 子句将结果集中记录根据指定的列排序后再返回
语法：SELECT 列名 FROM 表名 ORDER BY 列名 [ASC|DESC];
ASC(默认)升序，即从低到高；DESC 降序，即从高到低
35.对emp表中所有员工的薪资进行升序(从低到高)排序，显示员工姓名、薪资

select name,sal from emp order by sal asc;
select name,sal from emp order by sal;

36.对emp表中所有员工的奖金进行降序(从高到低)排序，显示员工姓名、奖金

select name,bonus from emp order by bonus desc;
--按照奖金降序排序，当奖金相等时按照薪资降序
select name,bonus,sal from emp order by bonus desc,sal desc;

分页查询

在 MySQL 中，通过 limit 进行分页查询
*limit (页码-1)每页显示记录数, 每页显示记录数
37.查询emp表中的所有记录，分页显示：每页显示3条记录，返回第 1 页

select * from emp limit 0,3;

38.查询emp表中的所有记录，分页显示：每页显示3条记录，返回第 2 页

select * from emp limit 3,3;

求emp表中薪资最高的前3名员工的信息，显示姓名和薪资

select name,sal from emp order by sal desc limit 0,3;

其他函数

函数名	解释说明
curdate()	获取当前日期，格式是：年月日
curtime()	获取当前时间 ，格式是：时分秒
sysdate()/now()	获取当前日期+时间，格式是：年月日 时分秒
year(date)	返回date中的年份
month(date)	返回date中的月份
day(date)	返回date中的天数
hour(date)	返回date中的小时
minute(date)	返回date中的分钟
second(date)	返回date中的秒
CONCAT(s1,s2…)	将s1,s2 等多个字符串合并为一个字符串
CONCAT_WS(x,s1,s2…)	同CONCAT(s1,s2,…)函数，但是每个字符串之间要加上x，x是分隔符

39.查询emp表中所有在1993和1995年之间出生的员工，显示姓名、出生日期

select name,birthday from emp 
where birthday>='1993-1-1' and birthday <='1995-12-31';
--或者
select name,birthday from emp where year(birthday)>=1993 and year(birthday)<=1995;

40.查询emp表中本月过生日的所有员工

select * from emp where month(birthday)=month(now());

41.查询emp表中员工的姓名和薪资（薪资格式为: xxx(元) ）

select name,concat(sal,'元') from emp;

查询emp表中员工的姓名和薪资（薪资格式为: xxx/元 ）

select name,concat_ws('/',sal,'元') from emp;

外键约束

外键其实就是用于通知数据库两张表数据之间对应关系的这样一个列
这样数据库就会帮我们维护两张表中数据之间的关系
创建表的同时添加外键

create table emp(
  id int,
  name varchar(50),
  dept_id int,
  foreign key(dept_id) references dept(id)
);

1）如果是要表示两张表的数据之间存在对应关系，只需要在其中的一张表中添加一个列，保存另外一张表的主键，就可以保存两张表数据之间的关系
但是添加的这个列（dept_id）对于数据库来说就是一个普通列，数据不会知道两张表存在任何关系，因此数据库也不会帮我们维护这层关系

（2）如果将dept_id列设置为外键，等同于通知数据库，部门表和员工表之间存在对应关系，dept_id列中的数据要参考部门的主键，数据库一旦知道部门和员工表之间存在关系，就会帮我们维护这层关系

表关系

常见的表关系分为以下三种
一对多(多对一)、一对一、多对多

多表查询

准备数据：

-- 删除db30库(如果存在)
drop database if exists db30;
-- 重新创建db30库
create database db30 charset utf8;
-- 选择db30库
use db30;
-- 删除部门表, 如果存在
drop table if exists dept;
-- 重新创建部门表, 要求id, name字段
create table dept(
 id int primary key auto_increment, -- 部门编号
 name varchar(20)     -- 部门名称
);
-- 往部门表中插入记录
insert into dept values(null, '财务部');
insert into dept values(null, '人事部');
insert into dept values(null, '科技部');
insert into dept values(null, '销售部');

连接查询
42.查询部门和部门对应的员工信息

select * from emp,dept;

上面的查询中存在大量错误的数据，一般我们不会直接使用这种查询
笛卡尔积查询：所谓笛卡尔积查询就是指，查询两张表，其中一张表有m条记录，另一张表有n条记录，查询的结果是m*n条。
虽然笛卡尔积查询中包含大量错误数据，但我们可以通过where子句将错误数据剔除，保留下来的就是正确数据

--条件：员工所属的部门编号等于部门的编号
select * from dept,emp 
where dept_id=dept.id;

通过where子句将笛卡尔积查询中的错误数据剔除,保留正确的数据,这就是连接查询!
上面的查询可以换成下面的查询：

select * from dept inner join emp
on dept_id=dept.id;

左外连接查询
43.查询所有部门和部门下的员工，如果部门下没有员工，员工显示为null

select * from dept left join emp
on dept_id=dept.id;--左外连接查询

左外连接查询：可以将左边表中的所有记录都查询出来，右边表只显示和左边相对应的数据，如果左边表中某些记录在右边没有对应的数据，右边显示为null即可
右外连接查询
44.查询部门和所有员工，如果员工没有所属部门，部门显示为null

select * from dept right join emp 
on dept_id=dept.id;

右外连接查询：可以将右边表中的所有记录都查询出来，左边表只显示和右边相对应的数据，如果右边表中某些记录在左边没有对应的数据，可以显示为null
如果想将两张表中的所有数据都查询出来(左外+右外并去除重复记录)，可以使用全外连接查询，但是mysql又不支持全外连接查询。

select * from dept left join emp on dept_id=dept.id
union 
select * from dept right join emp on dept_id=dept.id;

可以使用union将左外连接查询的结果和右外连接查询的结果合并在一起，并去除重复的记录

需要注意的是：union可以将两条SQL语句执行的结果合并有前提:
(1)两条SQL语句查询的结果列数必须一致
(2)两条SQL语句查询的结果列名、顺序也必须一致
并且union默认就会将两个查询中重复的记录去除(如果不希望去除重复记录，可以使用union all)