网站建设与设计 毕业设计,自己做的网站怎么上排行榜,怎样将视频放在网站里做,国外网站如何做seo今日内容 零、 复习昨日 一、单表查询 二、多表联查 零、 复习昨日 1 DDL,DML,DQL是啥 DDL 数据定义语言#xff08;库#xff0c;表#xff0c;列#xff09;DML 数据操作语言#xff08;表内数据的操作增删改#xff09;DQL 数据查询语言#xff08;表内数据的查询库表列DML 数据操作语言表内数据的操作增删改DQL 数据查询语言表内数据的查询 2 写出数据库相关单词 数据库 database 表 table 行 row 列 column 字段 field 创建 create 插入 insert 删除 drop delete 修改 alter update 展现 show 查询 select 已经 already 存在 exist 重复 duplicate 附近 near 值 value 数量count 匹配 match 日期 date 数据 data 3 写出建表语句 员工(emp)表,有员工编号empno 主键自增,员工姓名ename 不能为空, 员工工资salary 默认值为3000,员工入职日期 hiredate create table emp(empno int primary key auto_increment,ename varchar(10) not null,salary double(10,2) default 3000,hiredate date
);4 什么叫受影响行数: 执行增删改影响的数据行数,即结果 5 写出插入语句,向emp表插入1条数据 insert into emp values (1,zs,10000.0,1980-01-01);6 查出1980年以前入职的员工姓名和工资 select ename,salary from emp where hiredate 1980-01-01select * from pet where birthday 2022-01-01-- value和values
-- 插入一条数据
insert into pet values (3,小花,10.2,母,2020-01-01);-- 可以同时插入多条数据
insert into pet values
(41,小花41,10.2,母,2020-01-01),
(51,小花51,10.2,母,2020-01-01),
(61,小花61,10.2,母,2020-01-01)/*
date 日期,年月日
time 时间,时分秒
datetime 日期时间,年月日时分秒
timestamp 时间戳,年月日时分秒,当前行数据有变化,时间戳字段会自动改变成当前时间
*/
update pet set sjc 2022-01-01 10:01:59
update pet set nick 小红红 where id 61;一、DQL DQL 主要指查询语句,有查询单表数据,也有查多表数据表,今天主要学习单表查询 基本查询条件查询模糊查询排序查询聚合查询去重查询分组查询限制查询 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 age1 from stu;算数运算符描述两列做加法运算-两列做减法运算*两列做乘法运算/两列做除法运算
注意%是占位符而非模运算符。
1.3 条件查询 条件查询就是在基础查询基础上,再给sql设置条件,只查询部分符合条件的数据 条件语句 : select 字段1,字段2,… from 表名 where 字段 条件 值; 条件运算符 !andorinnot inbetween…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 李四;
-- 查询学号为100110021003的记录
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 女;
-- 查询性别为null的学生记录
update stu set sex null where sid 1009;
-- 查询是不能null运算
-- select * from stu where sex null;
select * from stu where sex is null;
-- 查询性别不为null的学生记录
select * from stu where sex is not null;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 表名; -- 聚合函数
/*
聚合函数: 把多行数据聚合在一起运算得到一个结果
注意: 聚合函数执行完,返回的结果只有一行记录与聚合函数一同出现的列必须出现在group by后
函数:count(字段) : 对该列不为null的行计数sum(字段): 对该列不为null的数据求和avg(字段): 对该列不为null的数据求和再求平均值min(字段): 对该列不为null的数据求最小值max(字段): 对该列不为null的数据求最大值
语法:放在select后from前,对查询的结果列进行运算放在having后使用
*/
-- 查询stu表中记录数
select count(sid) from stu;
select count(sid),sname from stu; -- 报错,语法不对
-- 查询stu表中有成绩的人数
select count(score) from stu;
select count(sex) 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;
select sname,age from stu;
-- 扩展: 使用as取别名,as可以省略
select sum(score) / count(sid) as 平均数 from stu;-- 可以
select sum(score) / count(sid) as 平均数 from stu;-- 可以
select sum(score) / count(sid) 平均数 from stu;-- 可以
-- 查询最高成绩和最低成绩
select max(score),min(score) from stu;
select max(score) max,min(score) min 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可以聚合函数 -- 分组查询
/*
分组查询:按照一定条件将数据分成若干组,然后对每个组内单独操作
主要事项:分组查询的结果,有几组结果就几行与聚合函数一同出现的列必须出现在group by后或者说,group by后出现的列可以与聚合函数同时出现
语法:select * from tb_name where ... group by 字段 having 条件
having 和where区别:
- having是分组后过滤数据,where是分组前过滤数据
- having后可以跟聚合函数的,where后不行
*/
-- 查询男生多少人,女生多少人
select count(sid) from stu group by sex;
-- 查询男生多少人,女生多少人
select sex,count(sid) from stu group by sex;-- 可以,3行(男,女,空)
-- 查询男生多少人,女生多少人,以及名字
select sex,sname,count(sid) from stu group by sex; -- 报错!!!
-- 在性别分组的基础上,再按照姓名分组
select sex,sname,count(sid) from stu group by sex,sname; -- 可以,(10行)-- 查询每个班级的班级编号和每个班级的成绩和
select cid,sum(score) from stu group by cid;
-- 查询每个班级的班级编号以及每个班级的人数
select cid,count(sid) from stu group by cid;
-- 查询每个班级的班级编号以及每班内男生女生各多少人
select cid,sex,count(sid) from stu group by cid,sex;
-- 查询成绩总和大于200的班级编号以及成绩和
select cid,sum(score) from stu group by cid having sum(score) 200
-- 查询所有人成绩都大于50的班级的班级编号和人数
select cid,count(sid) from stu group by cid having min(score) 50
-- 查询每个班成绩大于60的人数
select cid,count(sid) from stu where score 60 group by cid; -- 这个有逻辑问题,有的班会直接被过滤掉
select cid,count(if(score 60,null,score)) from stu group by cid;-- 这个可行-- 基本查询,条件查询,分组,排序
-- 查询成绩总和大于200的班级编号以及成绩和并根据成绩总和升序
select cid,sum(score) from stu
group by cid having sum(score) 200
order by sum(score) asc
--
select cid,sum(score) s from stu
group by cid having s 200
order by s asc1.9 限制查询 就是将查询完的数据,可以限制展现条数 语法: limit n – 限制输出指定n条,从第一条开始 limit x,y – 限制输出,从x下标处输出y条,第一条的下标是0 常用于分页操作 -- 分页(limit)查询
-- 在最后写limit x[,y]
-- limit x,限制输出结果的条数为x
select * from stu limit 5;
select * from stu where score 40 limit 5;
-- limit x,y 限制从x行处输出y条,x从0开始
select * from stu limit 0,4;
-- 分页中有些已知条件
-- 当前面 pageNo 1
-- 每页数据量pageSize 3
-- 数据总量 total count(sid)
-- 页数 pageCount total%pageSize0?total/pageSize:(total/pageSize)1
-- 第一页
select * from stu limit 0,3
-- 第二页
select * from stu limit 3,3
-- 第三页
select * from stu limit 6,3
-- 第pageNo页
select * from stu limit (pageNo-1)*pageSize,pageSize二、顺序
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 12.2 执行顺序
from 获得全部数据
where 过滤一部分数据
group by 分组
having 过滤分组后的数据
select 查询展现数据,这里有聚合的话在此时聚合
order 排序
limit 限制[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qPtu51ff-1683365026167)(https://qiushiju-tuchuang.oss-cn-hangzhou.aliyuncs.com/image/%E9%A1%BA%E5%BA%8F.png)]
三、多表联查
3.1 表之间的关系 表和表的关系有: 一对一 老公 -- 老婆 , 人 — 身份证/户口本一对多 皇帝 -- 妻妾 , 人 — 房/车多对多 订单 -- 商品 3.2 合并结果集 合并结果集,是将多表查询的结果纵向合并 语法: select field1,field2 from t1
union -- 合并结果集
select field1,field2 from t2create 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_b3.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.cname3.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.cid3.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学生编号、姓名、组长编号和组长姓名
SELECTt1.sid,t1.sname,t1.groupLeaderId,t2.sname
FROMstu t1,(SELECT* FROMstu WHEREsid ( SELECT groupLeaderId FROM stu WHERE sid 1008 ) ) t2
WHEREt1.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的班级编号以及成绩和并根据成绩和升序
