MySQL-----＞中篇

最新推荐文章于 2024-06-16 05:34:45 发布

二手Java程序员

最新推荐文章于 2024-06-16 05:34:45 发布

阅读量378

点赞数 1

分类专栏： Mysql 文章标签： mysql java 数据库

本文链接：https://blog.csdn.net/a778203081/article/details/129594560

版权

Mysql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

今日内容

零、复习昨日

1 DDL,DML,DQL是啥

DDL 数据定义语言: 针对数据库和表的操作
DML 数据操作语言: 针对表中的数据

2 写出数据库相关单词
数据库
表
行
列
字段
创建
删除
修改
展现

3 写出建表语句
员工(emp)表,有员工编号empno,员工姓名ename,
员工工资salary,员工入职日期 hiredate
4 什么叫受影响数
5 写出插入语句,向emp表插入数据

一、DQL

sql文件

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for class
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
  `cid` int(11) DEFAULT NULL,
  `cname` varchar(255) COLLATE utf8_bin NOT NULL,
  `caddress` varchar(255) COLLATE utf8_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- ----------------------------
-- Records of class
-- ----------------------------
INSERT INTO `class` VALUES ('1', 'BigData', '102');
INSERT INTO `class` VALUES ('2', 'HTML', '103');
INSERT INTO `class` VALUES ('3', 'VR', '104');
INSERT INTO `class` VALUES ('4', 'Java', '105');

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for stu
-- ----------------------------
DROP TABLE IF EXISTS `stu`;
CREATE TABLE `stu` (
  `sid` int(11) DEFAULT NULL,
  `sname` varchar(25) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `sex` char(6) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  `cid` int(11) DEFAULT NULL,
  `groupLeaderId` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of stu
-- ----------------------------
INSERT INTO `stu` VALUES (1001, '张三', '20', '男', '72', '1', '1003');
INSERT INTO `stu` VALUES (1002, '李四', '15', '女', '78', '1', '1003');
INSERT INTO `stu` VALUES (1003, '王五', '95', '男', '99', '1', '1010');
INSERT INTO `stu` VALUES (1004, '赵六张', '65', '女', '60', '1', '1007');
INSERT INTO `stu` VALUES (1005, '周七', '55', '男', '78', '3', '1007');
INSERT INTO `stu` VALUES (1006, '茅十八', '75', '女', '96', '3', '1007');
INSERT INTO `stu` VALUES (1007, '张三丰', '40', '男', '85', '3', '1010');
INSERT INTO `stu` VALUES (1008, '李四方', '45', '女', '90', '2', '1010');
INSERT INTO `stu` VALUES (1009, '艾三弗森', '45', '', '35', '4', '1008');
INSERT INTO `stu` VALUES (1010, '三欧文', '35', '女', '49', '2', '1008');

查询需求

基本查询
查询所有列
查询指定列
条件查询
查询学号为1001的学生信息
查询学生成绩大于60的学生id 姓名,成绩
查询学生性别为女，并且年龄小于50的记录
查询学生学号为1001，或者姓名为李四的记录
查询学号为1001，1002，1003的记录
查询学号不是1001,1002,1003的记录
查询学生年龄在20到40之间的学生记录
查询性别非男的学生记录
模糊查询
查询姓名以“张”开头的学生记录
查询姓名中包含“三”的学生记录
排序查询
查询所有学生记录，按年龄升序排序
查询所有学生记录，按年龄降序排序
查询所有学生记录，按年龄升序排序,如果年龄相同时，按编号降序排序
查询成绩大于60的学生id,姓名,成绩,并根据成绩降序
聚合函数
查询stu表中记录数：
查询stu表中有成绩的人数：
查询stu表中成绩大于60的人数：
查询所有学生成绩和：
统计所有学生平均成绩
查询最高成绩和最低成绩：
去重查询
查询年龄不重复的共有多少人
分组查询
查询男生多少人,女生多少人
查询每个班级的班级编号和每个班级的成绩和：
查询每个班级的班级编号以及每个班级的人数：
查询成绩总和大于200的班级编号以及成绩和：
查询成绩总和大于200的班级编号以及成绩和并根据成绩总和降序
分页(limit)查询
流程控制函数
IF(expr1,expr2,expr3) 如果expr1为真，则返回expr2，否则返回expr3
– isnull()函数,判断是否为空
– isnull(字段) 如果是null,返回1 不是null返回0

– 查询学生id,姓名,成绩,如果成绩为null,显示缺考
IFNULL(expr1,expr2) 如果 expr1不是NULL,则返回expr1，否则返回expr2；

CASE WHEN [expr1] THEN [result1]… ELSE [default] END 如果expr是真, 返回result1,否则返回default
– 查询学生id,姓名,成绩,如果成绩为null,显示缺考
CASE [value] WHEN [value1] THEN[result1]… ELSE[default] END 如果value等于value1, 返回result1,否则返回default

多表联查
合并结果集
连接查询
查询每个学生的信息,包括班级的全部信息
内连接
– 查询1班信息,以及对应学生信息
– 查询成绩大于60的学生信息,以及对应的专业
– 查询班级编号,班级名称,和每班人数
左外连接
右外连接
查询学生编号为1007的学生名称、学生成绩、班级名称、班级地址
子查询
查询与张三同一个班级的学生。
成绩高于3号班级所有人的学生信息
有2个以上直接组员的学生信息
求1008学生编号、姓名、组长编号和组长姓名
查询每个学生成绩大于等于60且成绩总和大于200的班级编号以及成绩和并根据成绩和升序

DQL 主要指查询语句,有查询单表数据,也有查多表数据表,今天主要学习单表查询

基本查询

select * from stu;
select sid,sname,age,age+1 from stu;

条件查询

# 条件查询
select * from stu where sid = 1001;
select sid,sname,score from stu where score > 60;
select * from stu where sex = '女' and age < 50;
select * from stu where sid = 1001 or sname = '李四';
select * from stu where sid in (1001,1002,1003);
select * from stu where sid not in (1001,1002,1003);
select * from stu where sid != 1001 and sid != 1002 and sid != 1003;
select * from stu where age between 20 and 40;
select * from stu where sex != '男';
select * from stu where sex = '女';
update stu set sex = null where sid = 1009;
select  * from stu where sex is null;
select  * from stu where  sex is not null;

模糊查询

# % 和 _ 的区别
select * from stu where sname like '张_';
select * from stu where sname like '张%';
select * from stu where sname like '%三%';

排序查询

# 排序查询
select * from stu order by score asc; # 降序
select * from stu order by score desc; # 序
select * from stu order by score; # 默认升序
select * from stu order by age asc , sid desc;
select sid,sname,score from stu where score > 60 order by score desc ;

聚合查询

select count(sid) from stu;
select count(sid) from stu where score > 60;
# ps 先过滤再去计算select后的函数。
select sum(score) from  stu;
# 统计有成绩的人数
select sum(score)/count(sid) from stu;
select avg(score) from  stu;
select max(score) from stu;
select min(score) from stu;

去重查询

select distinct (age) from stu;

分组查询

select distinct (age) from stu;
select count( distinct (age)) from stu;

限制查询

1.1 数据准备

将发的stu.sql导入到MySql中

1.2 基本查询

select 字段1,字段2,… from 表名;

查询返回的是一张虚拟表,查询对原表数据没有任何影响,默认查询的全表数据

-- 基本查询
-- 查询所有列
select sid,sname,age,sex,score,cid groupLeaderId from stu;
-- 查询所有列,在测试,练习时可以使用*代替
select * from stu;
-- 查询指定 列
select sid,sname,sex from stu;

-- 年龄+1
select age+1 from stu;

算数运算符	描述
+	两列做加法运算
-	两列做减法运算
*	两列做乘法运算
/	两列做除法运算

注意：%是占位符，而非模运算符。

1.3 条件查询

条件查询就是在基础查询基础上,再给sql设置条件,只查询部分符合条件的数据

条件语句 : select 字段1,字段2,… from 表名 where 字段条件值;

条件运算符

=
>
<
>=
<=
!=
and
or
in
not in
between…and

-- ============== 条件查询 ==============
-- 查询学号为1001的学生信息
select * from stu where sid = 1001;
-- 查询学生成绩大于60的学生id 姓名,成绩
select sid,sname,score from stu where score > 60;
-- 查询学生性别为女，并且年龄小于50的记录
select * from stu where sex = '女' and  age < 50;
-- 查询学生学号为1001，或者姓名为李四的记录
select * from stu where sid = 1001 or sname = '李四';
-- 查询学号为1001，1002，1003的记录
select * from stu where sid = 1001 or sid = 1002 or sid = 1003;
select * from stu where sid in (1001,1002,1003);
select * from stu where sid >= 1001 and sid <= 1003;
-- 查询学号不是1001,1002,1003的记录
select * from stu where sid not in (1001,1002,1003);
select * from stu where sid != 1001 and sid != 1002 and sid != 1003;
-- 查询学生年龄在20到40之间的学生记录
select * from stu where age >= 20 and age <= 40;
select * from stu where age between 20 and 40;
-- 查询性别非男的学生记录
select * from stu where sex != '男';
select * from stu where sex = '女';

1.4 模糊查询

模糊查询其实也是条件查询

语法: select 字段1,字段2,… from 表名 where 字段 like '_值%';

_ 匹配任意一个字符
% 匹配任意多个字符

-- ============== 模糊查询 ============== 
-- 查询姓名以“张”开头的学生记录
select * from stu where sname like '张_';
select * from stu where sname like '张__';
select * from stu where sname like '张%';
-- 查询姓名中包含“三”的学生记录
select * from stu where sname like '%三%';

1.5 排序查询

对查询后的数据按照指定字段以及指定规则排序

语法: select 字段1,字段2,… from 表名 order by 字段 [desc|asc];

desc 降序
asc 升序,默认是升序

排序查询写在最后

-- ============== 排序查询 ============== 
-- 查询所有学生记录，按年龄升序排序
select * from stu order by age asc;
select * from stu order by age;
-- 查询所有学生记录，按年龄降序排序
select * from stu order by age desc;
-- 查询所有学生记录，按年龄升序排序,如果年龄相同时，按编号降序排序
select * from stu order by age asc , sid desc;
-- 查询成绩大于60的学生id,姓名,成绩,并根据成绩降序
select sid,sname,score from stu where score > 60 order by score desc;

1.6 聚合函数

将查询的结果,聚合运算得到一个结果值,语法特点

聚合运算完,结果只有一行数据
其他字段不能和聚合函数同时查询,除非有分组查询

聚合函数分类

count(expr) 计算指定列的不为null的行数
max(expr) 计算指定列的最大值
min(expr) 计算指定列的最小值
avg(expr) 计算指定列的平均数,除以不为null的条数
sum(expr) 计算指定列的和 ,计算不为null的数据
函数中的expr,可以写列名,也可以写函数表达式

语法: select 聚合函数(字段) from 表名;

-- ============== 聚合函数  ============== 
-- 查询stu表中记录数：
select count(sid),sname from stu;
-- 查询stu表中有成绩的人数：
select count(score) from stu;
-- 查询stu表中成绩大于60的人数：
-- 先执行from获得全部数据,再通过where过滤数据,再计算select后
select count(sid) from stu where score > 60;
-- 查询所有学生成绩和
select sum(score) from stu;
-- 统计所有学生平均成绩
select avg(score) from stu;
select sum(score)/count(sid) from stu;
-- 查询最高成绩和最低成绩
-- 查询可以给列取别名,用as,as还可以省略
select max(score),min(score) from stu;
select max(score) as 最大值,min(score) as '最小值' from stu;
select max(score) 最大值,min(score) '最小值' from stu;

1.7 去重函数

可以将某列数据去重查询,distinct,一般不单独使用,配合聚合函数使用

-- ============== 去重查询 ============== 
-- 查询年龄不重复的共有多少人		
select count(distinct age) from stu;

1.8 分组查询

分组查询,就是将查询的数据分为几组.

语法: select 字段1,字段2,… from 表名 [where 字段条件值] group by 字段 having 字段条件值;

group by 字段,根据指定字段分组
having 字段值, 分组后再过滤

有个非常重要特点: SQL只要有分组,分成几组,查询结果就只有几行,所以一般配合聚合函数来使用，组内的聚合函数的聚合。

与聚合函数同时出现的列,必须出现在group by语句中

或者说group by后面的字段可以出现在select后

再或者说,sql语句中有group by,那么前面select后的字段,要么是group by字段,要么就是聚合函数

having和where都是过滤

where是分组前过滤,having是分组后过滤
where后不能使用聚合函数,having可以聚合函数

# 分组查询
# 语法 select 聚合函数，分组字段 from 表名 【where】 group by 字段
# select * from stu group by sex;
select COUNT(sid),sex from stu group by sex;
select cid 班级号,count(cid) 人数 from stu group by cid;
select cid,sum(score) from stu group by cid;
select cid,sex,count(sex) from stu group by cid,sex;
select cid,sex,count(sex) from stu group by cid,sex;
select cid,sum(score) from stu group by cid having sum(score) >200;
select cid, count(sid) from stu where score > 60 group by cid;
# select cid,count(sid) from stu group by cid
select cid,count(sid) from stu  group by cid having min(score) > 50;

1.9 限制查询

就是将查询完的数据,可以限制展现条数

语法: limit n – 限制输出指定n条,从第一条开始

limit x,y – 限制输出,从x下标处输出y条,第一条的下标是0

常用于分页操作

-- 限制(limit)查询
select * from stu limit 3;
select * from stu limit 3,2;
# 分页查询
select * from stu limit 5; # limit 限制输出结果的条数为x  限制,
select * from stu stu limit 2,3; # limit x,y 限制从x处输出y条，x从0开始

二、顺序

2.1 书写顺序

select * from 表名 
where 条件 
group by 字段 
having 条件 
order by 
limit x,y
------
select cid,sum(score) from stu
where sid >= 1002
group by cid
having sum(score) > 100
order by sum(score) desc
limit 1

2.2 执行顺序

from   获得全部数据
where  过滤一部分数据
group by 分组
having   过滤分组后的数据
select  查询展现数据,这里 有聚合的话在此时聚合
order   排序
limit   限制

三、多表联查

3.1 表之间的关系

表和表的关系有:

一对一老公 --> 老婆 , 人 —> 身份证/户口本
一对多皇帝 --> 妻妾 , 人 —> 房/车
多对多订单 --> 商品

3.2 合并结果集

合并结果集,是将多表查询的结果纵向合并

# 合并结果集要列数相同
# union 会去除重复数据
# union all 会保留全部数据

语法:

select field1,field2 from t1
union   -- 合并结果集
select field1,field2 from t2

create table tb_a(
 id int,
 name char(10),
 age int
);

create table tb_b(
 id int,
 name char(10)
);
/*
 合并结果集的两个表的字段
 数量,类型要一致
-----------
union 联合数据,将数据纵向拼接,如果有重复数据会去重
union all 如果有重复数据会全部保留
--------------
场景:
当表很大时,可以拆分成多个表
就可以使用联合查询
*/
select id,name from tb_a
union all
select id,name from tb_b

3.3 连接查询【重要】

连接查询是将多张表数据连接在一起(横向)查询返回,

这个连接是多表的乘积,t1 * t2 , 这就是笛卡尔积

连接查询需要使用表之间的关联关系来过滤数据

连接查询分为以下几种

内连接
外连接

3.3.1 内连接

数据准备, class表是班级表,stu是学生表, 一个班级对应多个学生

两表的关联列是学生表(stu)中的cid,引用了班级表(class)中的主键cid

语法:

select 字段列表 from 表1 inner join 表2 on 表1.字段 = 表2.字段

/*
 内连接
 select 字段列表 from 表1 inner join 表2 on 表1.字段 = 表2.字段
*/
-- 查询学生信息以及学生关联的班级信息
select * from stu inner join class on stu.cid = class.cid;
-- 查询学生的学号,姓名,分数,班号,班名
select stu.sid,stu.sname,stu.score,stu.cid,class.cname 
from stu 
inner join class 
on stu.cid = class.cid;
-- 也可以给表设置别名
select s.sid,s.sname,s.score,s.cid,c.cname 
from stu s
inner join class c
on s.cid = c.cid;
-- 内连接特点:只会查询满足关联条件的数据

-- 内连接标准写法
select * from stu inner join class on stu.cid = class.cid;
-- 内连接可以简写成(推荐)
select * from stu s,class c where s.cid = c.cid;

-- 标准语法
-- 简写
-- 特点
-- 什么叫笛卡尔积
-- 去除笛卡尔积

练习

-- 查询1班信息,以及对应学生信息
select * from class c,stu s where c.cid = s.cid and c.cid = 1;
-- 查询成绩大于60的学生信息,以及对应的专业
select * from stu s,class c where s.cid = c.cid and score > 60;
-- 查询班级编号,班级名称,和每班人数
select c.cid,c.cname,count(sid) from class c,stu s 
where c.cid = s.cid
group by c.cid,c.cname

3.3.2 外连接

外连接又分为左外连接,右外连接

法:
select 字段列表 from 表1 left|right outer join 表2 on 表1.字段 = 表2.字段
内外连接有什么区别?

内连接只查询符合关联添加的数据
外连接会保留不符合条件的数据

-- 1) 外连接会保留不符合条件的数据
-- 2) 左外是以左表为主,左表中有不符合条件的数据也会保留
--    右外相反...

-- 查询学生信息以及对应的班级信息
-- 左外
select * from stu s left outer join class c on s.cid = c.cid

-- 右外
select * from stu s right outer join class c on s.cid = c.cid

-- outer可以省略
select * from stu s left join class c on s.cid = c.cid

3.4 子查询【重要】

子查询(subquery)也叫嵌套查询

将sql语句当表,写在from后面
将sql语句当条件,写在where后面

-- 子查询就是嵌套查询
-- 查询的结果是一张虚拟表
select sid,sname,age from stu where sex = '男'

-- 子查询当表
select * from 
(select sid,sname,age from stu where sex = '男') t
where t.age > 50

-- 子查询当条件,但是要注意条件的值的个数(列数和行数)
select age from stu where sid = 1001
-- 年龄大于学号为1001这个人的年龄
select * from stu 
where age > (select age from stu where sid = 1001)

-- 查询与张三同一个班级的学生。
select * from stu 
where cid = (select cid from stu where sname = '张三');
-- 成绩高于3号班级所有人的学生信息
select * from stu
where score > (select max(score) from stu where cid  = 3)
-- 有2个以上直接组员的学生信息
select * from stu where sid in(
select groupLeaderId from stu 
group by groupLeaderId 
having count(sid) > 2)

-- 求1008学生编号、姓名、组长编号和组长姓名
SELECT
	t1.sid,
	t1.sname,
	t1.groupLeaderId,
	t2.sname 
FROM
	stu t1,(
	SELECT
		* 
	FROM
		stu 
	WHERE
		sid = ( SELECT groupLeaderId FROM stu WHERE sid = 1008 ) 
	) t2 
WHERE
	t1.sid = 1008
-- 上面这题可以改造成自连接
select s.sid,s.sname,s.groupLeaderId,z.sname from stu s,stu z where s.groupLeaderId = z.sid and s.sid = 1008

查询每个学生成绩大于等于60且成绩总和大于200的班级编号以及成绩和并根据成绩和升序

二手Java程序员

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
MySQL-----＞中篇

1 DDL,DML,DQL是啥2 写出数据库相关单词数据库表行列字段创建删除修改展现3 写出建表语句员工(emp)表,有员工编号empno,员工姓名ename,员工工资salary,员工入职日期 hiredate4 什么叫受影响数5 写出插入语句,向emp表插入数据。
复制链接

扫一扫