目录
一、数据库简介
1.1 数据库
- 概念:在计算机中有组织的存储数据的仓库,简单来说就是存储数据的仓库
- 例如:现实中图书馆是存放书本的地方,图书馆就是书籍的管理仓库
1.2 数据库的分类
-
关系型数据库(RDBMS:relational database management system)
作用:以表格的形式存储大批量的数据方式
-
常见关系型数据库软件
- Oracle:大型项目中应用。比如:电信行业、电网行业等。
- MySQL:常见互联网类项目应用。比如:电商平台等。
- Microsoft SQL server:常见于微软服务器系统相关应用的项目。
- SQLite:轻量级应用系统。
-
关系型数据库的核心要素
由小到大范围的构成
- 数据行
- 数据列
- 数据表
- 数据库
-
-
非关系型数据库(No SQL)
作用:以Key/value(键值对)、文本、图片等形式存储数据方式【不是以表格形式存储】
- 常见非关系型数据库
- mongodb
- redis
- HBASE
- 常见非关系型数据库
-
关系型数据库和非关系型数据库在实际中的应用
- RDBMS:存储大批量不经常变化的数据
- NoSQL:经常用于缓存,读取速度快,存储一些频繁使用的数据
在实际工作中,常常是关系型数据库和非关系型数据库结合一起使用
1.3 SQL介绍
- 结构化查询语言SQL (Structured Query Language):针对于关系型数据库的操作语言
- 应用划分
- 数据查询语言: DQL (data query language), 对于数据库表中的数据查询操作,关键词:select
- 数据库操作语言: DML (data manipulation language), 对于数据库表中的数据进行操作(增删改),关键词: insert、delete、update
- 数据库定义语言: DDL (data defination language),对于数据库或者数据表本身的操作(创建、修改、删除) , 关键词: create、alter、drop
- 数据库控制语言: DCL(data control language),对于数据库操作进行授权或者回收权限操作,关键词: grant、revoke
二、MySQL的介绍
2.1 MySQL简介
- MySQL概念:是一种关系型数据库(应用软件)
- MySQL的特点:
- 支持多操作系统、多编程语言
- 可移植性好,开源社区版免费
- MySQL组成
- 服务端:已经在虚拟机上安装的MySQL应用程序
- 客户端:编写语句返回结果的交互窗口(比如DBeaver)
- DBeaver
- Navicat
- workbench
2.2MySQL 连接
-
命令行连接数据库
#通过终端命令行连接数据库 mysql -u root -p # 输入数据库账号的密码 # -u 表示后面需要数据库管理员账号root # -p 表示连接的时候需要输入数据库密码 # -h 表示后面需要连接数据库主机的IP # -P 表示后面需要连接MySQL数据库的端口号,默认3306
-
DBeaver连接数据库
步骤: 1.检查centos的IP地址 2.打开DBeaver新建连接选择MySQL 3.通过DBeaver连接输入上述查到IP地址进行连接 4.测试连接是否能够通,并确认连接
三、DBeaver 操作MySQL
3.1 鼠标操作数据库
-
创建数据库
-
使用数据库
- 双击打开数据库
-
修改数据库
-
双击打开直接修改数据库(不能修改数据库名)
-
-
删除数据库
- 右键删除数据库
3.2鼠标操作表
--eg:
--创建数据表student,要求需要有学号(ID),姓名(name)字段
--设计数据表student,要求增加年龄字段(age)
--删除数据库student
-
新建表
-
修改表(结构)
- 删除表
- 选中表右键删除
3.3鼠标操作数据
-
新增、修改、删除、清空数据
3.4命令操作数据库
-
数据库基本操作
字符:在计算机中通过文字、字母、符号等形式表示数据的形式
字符集:计算机中每个字符对应的二进制编号的集合。
编码方式:编码方式就是将字符转换为二进制的标准。
排序规则:描述字符集内字符的排列顺序。
-
数据库操作语法
-- 创建数据库 create database 数据库名 character set 字符集 collate 排序规则; create database 数据库名 charset 字符集 collate 排序规则; -- 修改数据库 alter database 数据库名 character set 字符集 collate 排序规则; -- 删除数据库 drop database 数据库名; -- 查看所有的数据库 show databases; -- 使用(打开)数据库 use 数据库名; -- 查看当前使用的数据库(指通过use打开的数据库) select database();
-
案例演示
#创建数据库 create database bbb; #修改数据库(只能修改字符集和排序规则) alter database bbb charset utf8 collate utf8_general_ci; #删除数据库 drop database bbb; #使用数据库 use bbb; #查看当前使用的数据库 select database(); #查看所有数据库 show databases;
四、数据类型及约束⭐️
作用:对于操作的数据进行 正确性 和 完整性 的验证
简单理解:告诉使用者我们输入的数据应该遵循的格式、规律,方便计算机(数据库系统)识别
4.1数据库常见数据类型
下表是MySQL数据库常见的数据类型:
例如:decimal(3,2) ----->总长度是3位,小鼠位是2位
4.2数据库常见约束
-
对于输入数据的要求
- 主键:在一张表中存储数据的物理顺序
一张表只能有一个主键,主键作用相当于(非空+唯一)
- 外键:做表关联使用,对于关系字段进行约束。主要限制主表和副表进行数据一致性和正确性
- 唯一:表示输入的数据不能重复
- 非空:该字段对应数据不能为空
- 自增长:对于整数类型进行数据的递增,一般和主键配合使用
- 默认:创建表时该字段可以有初始化的值,不输入时使用默认值
五、数据库其他操作⭐️
-
备份/恢复数据库
作用:在测试工作中对于原始数据的保存和恢复,防止数据丢失
-
备份
-
恢复数据
-
-
常用快捷键
SQL语句操作快捷键 注释快捷键:ctrl+/
六、数据表操作
6.1创建表⭐️
-
语法格式:
-- 创建表 create table 表名 ( 字段1 数据类型 [约束], 字段2 数据类型 [约束], ... );
-
注意事项:
- SQL中所有标点符号一律用英文格式
- 括号中字段和字段之间用英文逗号分割
- 字段名、数据类型、约束次序不能变
- 数据类型必须有,约束可选
-
案例演示
#1.创建学生表(student)字段要求如下:
#姓名(长度为10)、年龄、身高(保留2位小数)
create table student(
name varchar(10),
age int,
height decimal(3,2) --例如:1.72m
);
#2.例:创建一个学生表(stu),
# 要求:
#学号是整型无符号类型,学号有主键约束,并且是自增长的
#姓名长度为10
#年龄整型无符号
#身高保留两位有效数字,并且长度为3
6.2删除表
- 语法格式
#格式一
drop table 表名;
#格式二(推荐):带条件判断的删除
drop table if exists 表名;
#区别:带条件的删除如果表不存在不报错
- 案例演示
#例:删除学生表
drop table if exists students;
数据表已经不存在时,依然删除时,会报错
数据表已经不存在时,依然删除时,不会报错
6.3其他命令
#查看当前数据库所有表
show tables;
#查看表结构(表中字段名、数据类型、约束)
desc 表名;
#查看创建表语句
show create table 表名;
七、数据操作⭐️
7.1 增加数据
提示:增加(插入)数据前最好先看看表结构(指定字段的数据类型和约束)
- 语法格式
-- 插入一条数据
-- 全部字段
insert into 表名 values(值1,值2,...);
-- 指定字段(部分)
insert into 表名(字段1,字段2) values(值1,值2);
-- 插入多条数据
-- 全部字段
insert into 表名 values (值1,值2,...),(值11,值12,...);
-- 指定字段(部分)
insert into 表名(字段1,字段2) values(值1,值2),(值11,值12);
- 注意事项
- 自增长约束的主键字段,插入字段对应数据时可以用0或null或default任何一个代替主键字段值输入
- 如果主键字段不设置自增长,则插入数据需要正确输入对应主键字段值
- 插入指定字段对应的数据时,该字段和数据的顺序是必须一一对应
- 除了整数和小数之外,其他数据类型值需要在值上面加引号(英文格式)
- 案例演示
# 给students表插入数据
-- 给students表中插入数据
insert into students values(1,"张三",18,1.71);
-- 给students表中的指定字段插入数据
insert into students (id,name) values (2,"李四");
-- 给students表中插入多行数据
insert into students values
(3,"孙悟空",500,1.50),
(4,"唐僧",200,1.80);
-- 给students表中部分字段插入多行数据
insert into students (id,name) values
(5,"猪八戒"),
(6,"沙僧");
-- 给students表中插入多行数据(有主键自增长的列)
insert into students values
(0,"刘能",32,1.60),
(null,"广坤",44,1.65),
(default,"王老七",37,1.70);
7.2 修改数据
- 语法格式
#修改某列的所有值为xx
update 表名 set 列1=值1
#修改某列的某一个值(带条件)
update 表名 set 列1=值1,列2=值2,...where 条件
- 注意事项
- 修改数据建议带条件,否则会导致整列数据修改
- 案例演示
--修改学号为2号人的姓名为:尼古拉斯赵四
update students set name="尼古拉斯赵四" where id = 2;
--修改学号为2号的人的年龄和身高
update students set age=30,height=1.85 where id = 2;
7.3 删除数据
- 语法格式
#格式一(常用)
#带条件的删除
delete from 表名 where 条件
#格式一
#用delete清空表
delete from 表名;
#格式二
#用truncate清空表记录
truncate table 表名;
#格式三
#删除表的数据记录及结构(这个表就不存在)
drop table 表名;
-
注意事项:
- 通过delete清空数据之后,新插入的数据不影响主键
- 通过truncate清空数据之后,新插入的数据主键从头开始记录
-
案例演示
-- 删除姓名叫王老七的人的信息 delete from stu where name = "张三"; -- 清空数据(新增数据时主键列不受影响) delete from stu; -- 清空数据通过truncate(新增数据时,主键列默认从头开始) truncate table stu; -- 通过drop删除表时,表和数据都被删除 drop table stu;
通过delete清空数据之后,新插入的数据不影响主键
通过truncate清空数据之后,新插入的数据主键从头开始记录
7.4 查询数据
- 基本查询
作用:想知道列表有哪些字段及对应值
- 语法格式
-- 查询所有字段信息
select * from 表名;
-- 查询指定字段信息
select 字段1,字段2,...from 表名;
-
注意事项
- 所有字段通过
*
代替 - 查询指定字段时,多个字段直接用英文逗号隔开
- 所有字段通过
-
案例演示
-- 查询students中所有数据 select * from students; -- 查询students中的学号和姓名 select id,name from students;
- 起别名?
作用:名字太长不方便操作时可以使用其别名
应用场景:在多张表进行组合查询时可以用到
-
语法格式
-- 给表起别名 select 别名.字段1,别名.字段2,... from 表名 [as] 别名; -- 给字段起别名 select 字段1 [as] 别名1,字段2 [as] 别名2,... from 表名;
-
注意事项
- 别名的关键词as可以省略,如果省略时,前后需要有空格
- 已经起别名的应用字段,如果要做查询时,需要在字段名前面带别名:
别名.字段名
- 不建议用中文别名,如果要用,中文字符上需要加英文格式引号
-
案例演示
-- 起别名 -- 1.表起别名为stu select * from students as stu; -- 2.给字段起别名 select sid '学号', 'name' '姓名' from students; -- 3.先给表起别名,在给字段起别名 select stu.sid as '学号' from students as stu;
- 数据去重
作用:去除表某字段中重复的数据
应用场景:查询某字列表不同的数据
-
语法格式
``` select distinct 字段 from 表名; ```
-
案例演示
-- 去除重复的年龄(查询表中不同年龄信息) select distinct age from students;
八、条件查询⭐️
应用场景:在实际测试中,往往是通过查询多张表才能查询到所需要的数据
数据准备(创建以下三个表)
use day01;
DROP TABLE IF EXISTS courses;
CREATE TABLE `courses`(
courseNo int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
name varchar(10) CHARACTER SET Utf8 COLLATE utf8_general_ci
NULL DEFAULT NULL,
PRIMARY KEY(courseNo)USING BTREE)
ENGINE = InnODB AUTO_INCREMENT =7 CHARACTER SET = Utf8
COLLATE=utf8_general_ci ROW_FORMAT = Dynamic;
INSERT INTO courses VALUES (1,'数据库');
INSERT INTO courses VALUES (2,'qtp');
INSERT INTO courses VALUES (3,'linux');
INSERT INTO courses VALUES (4,'系统测试');
INSERT INTO courses VALUES (5,'单元测试');
INSERT INTO courses VALUES (6,'测试过程');
DROP TABLE IF EXISTS scores;
CREATE TABLE scores(
id int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
courseNo int(10) NULL DEFAULT NULL,
studentno varchar(10) CHARACTER SET Utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
score tinyint(4) NULL DEFAULT NULL,
PRIMARY KEY(id)USING BTREE
)ENGINE = InnODB AUTO_INCREMENT =9 CHARACTER SET = Utf8 COLLATE= utf8_general_ci ROW_FORMAT = Dynamic;
INSERT INTO scores VALUES (1,1,'001',98);
INSERT INTO scores VALUES (2,1,'002',75);
INSERT INTO scores VALUES (3,2,'002',98);
INSERT INTO scores VALUES (4,3,'001',86);
INSERT INTO scores VALUES (5,3,'003',80);
INSERT INTO scores VALUES (6,4,'004',79);
INSERT INTO scores VALUES (7,5,'005',96);
INSERT INTO scores VALUES (8,6,'006',80);
DROP TABLE IF EXISTS students;
CREATE TABLE students (
studentNo varchar(10) CHARACTER SET Utf8 COLLATE utf8_general_ci NOT NULL ,
name varchar(10) CHARACTER SET Utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
sex varchar(1) CHARACTER SET Utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
hometown varchar(20) CHARACTER SET Utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
age tinyint(4) NULL DEFAULT NULL,
class varchar(10) CHARACTER SET Utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
card varchar(20) CHARACTER SET Utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY(studentNo)USING BTREE
)ENGINE = InnODB CHARACTER SET = Utf8 COLLATE= utf8_general_ci ROW_FORMAT = Dynamic;
INSERT INTO students VALUES ('001', '王昭君', '女', '北京', 20, '1班', '340322199001247654');
INSERT INTO students VALUES ('002', '诸葛亮', '男', '上海', 18, '2班', '340322199002242354');
INSERT INTO students VALUES ('003', '张飞', '男', '南京', 24, '3班', '340322199003247654');
INSERT INTO students VALUES ('004', '白起', '男', '安徽', 22, '4班', '340322199005247654');
INSERT INTO students VALUES ('005', '大乔', '女', '天津', 19, '3班', '340322199004247654');
INSERT INTO students VALUES ('006', '孙尚香', '女', '河北', 18, '1班', '340322199006247654');
INSERT INTO students VALUES ('007', '百里玄策', '男', '山西', 20, '2班', '340322199007247654');
INSERT INTO students VALUES ('008', '小乔', '女', '河南', 15, '3班', '');
INSERT INTO students VALUES ('009', '百里守约', '男', '湖南', 21, '1班', ' ');
INSERT INTO students VALUES ('010', '妲己', '女', '广东', 26, '2班', '340322199607247654');
INSERT INTO students VALUES ('011', '李白', '男', '北京', 30, '4班', '340322199005267754');
INSERT INTO students VALUES ('012', '孙膑', '男', '新疆', 26, '3班', '340322199000297655');
INSERT INTO students VALUES ('013', '杜甫', '男', '河北', 35, '1班', NULL);
SET FOREIGN_KEY_CHECKS = 1;
8.1 条件查询
条件查询:根据特定条件查询所需要的数据。eg:查询名字叫张三的人的信息
- 语法格式
select 字段1,字段2,... from 表名 where 条件;
- 条件的构成
- 比较运算符
条件通过字段名和比较运算符将值连接起来表示
eg: age > 18—>年龄大于18
- 大于、大于等于: >, >=
- 小于、小于等于: <, <=
- 等于: =
- 不等于: != 或者 <>
- 案例演示
-- 例1:查询小乔的年龄
select age from students where name = "小乔";
-- 例2:查询20岁以下的学生(的信息)
select * from students where age < 20;
-- 例3:查询家乡不在北京的学生
select * from students where hometown != "北京";
-- 查询表结构
desc students;
-- 练习1:查询学号是'007'的学生的身份证号
select card from students where studentNo = "007";
-- 练习2:查询'1班'以外的学生信息
select * from students where class != "1班";
-- 练习3:查询年龄大于20的学生的姓名和性别
select name,sex from students where age > 20;
– 逻辑运算符
将多个条件通过逻辑运算符连接起来表示
- and(与):多个条件同时满足
- or(或):满足其中一个条件即可
- not(非):不满足条件(非类似于不等号)
- 案例演示
-- 例1:查询年龄小于20的女同学(信息)
-- 分析:
-- 关键词:查询同学(的信息) select * from students
-- 修饰词:年龄小于20岁 age < 20 女(性别为女) sex = "女"
-- 组合:
select * from students where age < 20 and sex = "女";
-- 例2:查询女学生或'1班'的学生(信息)
select * from students where sex = "女" or class = "1班";
-- 例3:查询非天津的学生
select * from students where not hometown = "天津";
select * from students where hometown != "天津";
- 模糊查询
查询某字段值,值不清楚时可以用模糊查询
like :关键词,把字段和值通过like连接起来
%:表示任意长度的值
_:表示单个长度的值
- 案例演示
-- 例1:查询姓孙的学生
select * from students where `name` like "孙%";
-- 例2:查询姓孙且名字是一个字的学生
select * from students where `name` like "孙_";
-- 例3:查询姓名以乔结尾的学生
select * from students where `name` like "%乔";
-- 例4:查询姓名含白的学生
select * from students where `name` like "%白%";
-- 例5:查询姓孙且名是两个字的学生信息
select * from students where `name` like "孙__";
– 范围查询
查询某字段值,值有一定的范围时可以用
-连续范围:
字段 between 值1 and 值2
-非连续范围:
字段 in(值1,值2,...)
- 案例演示
-- 例1:查询家乡是北京或上海或广东的学生
select * from students where hometown = "北京" or hometown = "上海" or hometown = "广东";
select * from students where hometown in ("北京","上海","广东");
-- 例2:查询年龄为18至20的学生
select * from students where age between 18 and 20;
-- 1、查询年龄在18或19或22的女生(的学生信息)
select * from students where age in (18,19,22) and sex = "女";
-- 2、查询年龄在20到25以外的学生
select * from students where not age between 20 and 25;
select * from students where age <20 or age >25;
– 空判断
查询某字段是否存在空值的情况
-空值:
字段 is null
-非空值:字段 is not null
– 案例演示
-- eg1:查询没有填写身份证的学生
select * from students where card is null;
-- eg2:查询填写了身份证的学生
select * from students where card is not null;
-- eg3:查询身份证号是空字符串的学生信息
select * from students where card = "";
- 注意事项
- 空值:用
字段 is null
表示 - 空字符串:用
字段 = ""
表示
- 空值:用
8.2 排序
排序:按照大小规则进行查询所需要的数据。eg:按照年龄从大小排序显示人员信息
- 语法格式
select * from 表名 order by 字段名1 asc|desc,字段名2 asc|desc,...
- 注意事项
- 默认排序规则的升序,asc可以不写
- 如果是降序必须要写排序规则desc
- 案例演示
-- eg1:查询所有学生信息,按年龄从小到大排序
select * from students order by age asc;
select * from students order by age;
-- eg2:查询所有学生信息,按年龄从大到小排序,年龄相同时,再按学号从小到大排序
select * from students order by age desc,studentNo asc;
九、复杂查询
9.1 聚合函数
聚合函数:对列表数据的统计计算。eg:统计这个班的平均年龄
-
常见分类
- count (): 统计表中所有的记录数,通过count(*/字段)表示
- max():计算某列的最大值,通过max(字段)表示
- min():计算某列的最小值,通过min(字段)表示
- sum():计算某列的和,通过sum(字段)表示
- avg():计算某列的平均值,通过avg(字段)表示
-
语法格式
select 聚合函数 from 表名;
-
注意事项
- count (*)统计所有记录数,包含某列为空值的记录数
- count(字段)统计某列非空的所有记录数
案例演示
#聚合函数案例
--eg:查询students表中学生总数
select count(*) from students;
-- eg:统计students表中学生身份证数量
select count(card) from students;
-- eg:查询女生最大年龄
select max(age) from students where sex="女";
--eg:查询1班的最小年龄
select min(age) from students where class="1班";
-- eg:查询北京学生的年龄总和
select sum(age) from students where hometown="北京";
-- eg:查询学生的平均年龄
select avg(age) from students where sex="女";
-- 练习1、查询所有学生的最大年龄、最小年龄、平均年龄
select avg(age),max(age),min(age) from students;
-- 练习2、一班一共有多少学生
select count(*) from students where class="1班";
-- 练习3、查询3班年龄小于18岁的同学有几个
select count(age) from students where class="3班" and age <18;
9.2 分组查询⭐️
分组作用:将字段对应相同的值分到一个组中方便统计。eg:将年龄相同的可以分到一个组
-
语法格式
-- 普通分组查询 select 字段1,字段2,聚合... from 表名 group by 字段1,字段2,...; --带条件分组查询 select 字段1,字段2,聚合... from 表名 group by 字段1,字段2,... having 条件;
-
where 和 having 对比
- 相同点:
- where后面可以带的条件,having后面都可以带;
- 不同点:
- where是对原始表数据的筛选,因为where后面不能带聚合函数的条件,having可以
- having是对分组之后的结果进行筛选,因而不能单独使用,必须和分组结合使用
- 相同点:
-
案例演示:
#分组查询 -- eg1:查询各种性别的人数 -- 分析: -- 关键词:查询人数--->select count(*)from students -- 修饰词:各种性别--->性别不一样 --->对性别分组(相同性别在一个组)--->group by sex -- 组合 select sex,count(*) from students group by sex; -- eg2:查询每个班级中各种性别的人数 -- 关键词:查询人数 -- 修饰词:各种性别--->对性别分组;、每个班级-->对班级分组 -- 组合 --->group by sex,class select sex,class,count(*) from students group by sex,class; -- eg3:查询每个班级中各种性别的人数,并按照班级升序排列 select sex,class,count(*) from students group by sex,class order by class; -- SQL语句中同时出现分组和排序时,分组在排序前面 -- eg4:查询男生总人数(两种方式实现) -- 可以先对性别分组,然后在查看人数 select sex, count(*) from students group by sex having sex="男"; select sex, count(*) from students where sex = "男"; -- 思考:如果在分组中用where能不能使用? select sex, count(*) from students where sex = "男" group by sex; -- eg5:查询班级平均年龄大于22岁的班级有哪些 -- 关键词:查询班级有哪些 --> select class from students -- 修饰词:班级平均年龄大于22岁-->班级平均年龄 avg(age)>22 select class from students group by class having avg(age) > 22 ; -- eg6:查询每个班级中女生的平均年龄大于22岁的班级有哪些 -- 关键词:查询班级有那些 -->select class from students -- 修饰词:每个班级,女生,平均年龄大于22 -- 每个班级--> group by class -- 女生 --> sex="女" -- 平均年龄大于22 -->avg(age) > 22 -- 组合:where > group by > having >order by select class from students group by class,sex having avg(age) > 22 and sex="女" ; -- 能否使用where select class from students where sex="女" group by class having avg(age)>22; -- 结论:having 后面如果要跟普通字段构成条件,必须先对该普通字段分组 -- 分组练习: -- 1.查询各个班级学生的平均年龄、最大年龄、最小年龄 -- 各个班级:对于班级进行分组:group by class select class, avg(age),max(age),min(age) from students group by class; -- 1.查询1班除外其他班级学生的平均年龄、最大年龄、最小年龄 -- 1班除外:不算1班 --> class !="1班" select class, avg(age),max(age),min(age) from students where class !="1班" group by class ; select class, avg(age),max(age),min(age) from students group by class having not class ="1班";
9.3 分页查询
分页:将批量数据按照指定的页数进行查询。eg:批量数据按照某页指定数据先
- 常见应用:分页常常和排序结合使用对应求最大最小值条件的应用
-
语法格式:
select * from 表名 limit start,count;
-
使用说明
- start:是下标/索引,表示从第几条开始,默认从0开始计数,这个0可以省略
- count:需要显示的记录
-
案例演示
#分页 -- eg1:查询前3行学生信息 -- start -- count select * from students limit 0,3; select * from students limit 3; -- eg2:查询第4到第6行学生信息 select * from students limit 3,3;
-
分页计算公式【扩展】
-- 基本格式 select * from 表名 limit (n-1)*m,count --解释说明 -- n:表示第几页数据 -- m:表示每页显示几条数据(默认每页条数是固定的) -- count 代表查询的数据总数
-- eg3:查询学生信息表中年龄最大的学生信息 -- 关键词:查询学生信息-->select*from students -- 修饰词:年龄最大-->max(age)--->该方式行不通 -- 转换思路:把学生信息的年龄从大到小排序,然后在获取第一个记录 -- 组合 select * from students order by age desc limit 0,1; -- 查询学生信息表中年龄最小的学生信息 select * from students order by age limit 0,1;
-
案例练习【扩展】
-- 查询第2页的记录,每页10条记录
-- select * from 表名 limit (n-1)*m,count
-- n=2 m=10 count=m
select * from students limit 10,10;
-- 查询第5页的前15条记录,每页20条
-- n=5 m=20 count=15
select * from students limit 80,15;
-- 查询第3页的记录,每页20条,从第三页的第5条记录开始向后显示10条记录
-- n=3 m=20 count=10
-- start = (n-1)*m + 4
select * from students limit 44 ,10;
-- 编写SQL的次序:select...from-->inner join ...on --> where -->
-- group by ...having-->order by ... --> limit
9.4 连接查询⭐️
连接查询:将相关联多张表进行连接起来查询所需要数据,即单张表无法满足要求。
eg:查询xx人某门课程的成绩(比如在三张表中查询)
- 常用连接分类
- 内连接:左右两边都匹配到的公共数据
- 左连接:左右两边的公共数据+左表特有的数据(即左表显示全部数据),右表不存在数据用null表示
- 右连接:左右两边的公共数据+右表特有的数据(即右表显示全部数据),左表不存在数据用null表示
-
语法格式
-- 内连接语法格式 select * from 表1 inner join 表2 on 表1.列=表2.列 --左连接语法格式 select * from 表1 left join 表2 on 表1.列=表2.列 --右连接语法格式 select * from 表1 right join 表2 on 表1.列=表2.列
-
注意事项
- 连接查询中可以对表和字段起别名,简化SQL语句
-
案例演示
#连接查询 -- 例1:查询学生信息及学生的成绩,通过内连接的形式查询 select * from students st inner join scores sc on st.studentNo =sc.studentno; -- 例2:查询课程信息及课程的成绩 select * from courses co inner join scores sc on co.courseNo=sc.courseNo ; -- 例3:查询学生信息及学生的课程对应的成绩 select * from students st inner join scores sc on st.studentNo = sc.studentno inner join courses co on sc.courseNo = co.courseNo; -- 例4:查询王昭君的成绩,要求显示姓名、课程号、成绩 -- 关键词:查询成绩 --->select score -- 修饰词:(姓名叫)王昭君 --> name = "王昭君",不在scores表中,次数需要做来连接 -- 组合 select st.name,sc.courseNo,sc.score from scores sc inner join students st on sc.studentno = st.studentNo where st.name = "王昭君"; -- 例5:查询王昭君的数据库成绩,要求显示姓名、课程名、成绩 -- 修饰词: 王昭君 --> name="王昭君","数据库" --> name="数据库" select st.name,co.name,sc.score from scores sc inner join students st on sc.studentno = st.studentNo inner join courses co on sc.courseNo = co.courseNo where st.name = "王昭君" and co.name="数据库"; -- 例6:查询所有学生的数据库成绩,要求显示姓名、课程名、成绩 select st.name,co.name,sc.score from scores sc inner join students st on sc.studentno = st.studentNo inner join courses co on sc.courseNo = co.courseNo where co.name="数据库"; -- 例7:查询男生中最高的成绩,要求显示姓名、课程名、成绩 -- 关键词:查成绩-->select*from scores -- 修饰词:男生、最高成绩 -- 男生-->sex ="男" 最高成绩-->max(score) -->无法构成条 -- 最高成绩-->把成绩从大到小排序,然后取第一个-->order by score desc limit 1 -- 组合 select st.name,co.name,sc.score from scores sc inner join students st on sc.studentno = st.studentNo inner join courses co on sc.courseNo = co.courseNo where sex="男" ORDER by sc.score DESC limit 0,1; -- 例8:查询所有学生的成绩,包括没有成绩的学生 -- 包含没有成绩的学生 -->需要有学生的信息,可以没有成绩 -- 右连接 select sc.score,st.name from scores sc right join students st on sc.studentNo = st.studentNo ; -- 左连接 select sc.score,st.name from students st left join scores sc on st.studentNo = sc.studentNo ; -- 例9:查询所有学生的成绩,包括没有成绩的学生,需要显示课程名 -- 左连接 select st.*,sc.score ,co.name from students st left join scores sc on st.studentNo = sc.studentNo left join courses co on sc.courseNo = co.courseNo ; -- 右连接 select *from courses co right join scores sc on co.courseNo = sc.studentNo right join students st on sc.studentNo = st.studentNo ;
9.5 自关联
自关联:同一张表中的某列关联该表中的另一列组合查询。eg:表示籍贯信息的表包含身份、市区、县城等
- 本质还是连接查询
-
准备数据
#创建省会城市表 drop table if exists areas; create table areas( aid int primary key, atitle varchar(20), pid int ); -- 插入多条数据 insert into areas values ('130000','河北省',NULL), ('130100','石家庄市','130000'), ('130400','邯郸市','130000'), ('130600','保定市','130000'), ('130700','张家口市','130000'), ('130800','承德市','130000'), ('410000','河南省',NULL), ('410100','郑州市','410000'), ('410300','洛阳市','410000'), ('410500','安阳市','410000'), ('410700','新乡市','410000'), ('410800','焦作市','410000'), ('410101','中原区','410100'), ('410102','二七区','410100'), ('410301','洛龙区','410300');
-
案例演示
-- 例1:查询河南省所有的市 select * from areas a1 -- 省份表 inner join areas a2 on a1.aid = a2.pid where a1.atitle ="河南省"; -- 例2:查询郑州市的所有的区 select * from areas a2 -- 城市表 inner join areas a3 on a2.aid = a3.pid --a3 县区表 where a2.atitle = "郑州市"; -- 例3:查询河南省的所有的市和区 select a1.atitle ,a2.atitle ,a3.atitle from areas a1 left join areas a2 on a1.aid = a2.pid left join areas a3 on a2.aid = a3.pid where a1.atitle = "河南省"; -- 问题:区和市之间是否一定有直接的关联关系? -- 部分市下面没有区
-
截图
9.6 子查询⭐️
子查询:在一个查询里面嵌套另一查询的方式。eg:查询班级中小于平均年龄并且1班的人的信息
应用场景:一般在条件查询或者连接查询中会应用到子查询,想办法在一张表中查到结果
- 主查询:括号外面的select查询
- 子查询:括号里面的select查询
-
子查询充当条件:子查询的结果如果是一行一列/一行多列/多行一列,可以直接作查询条件
-
一行一列
-- 例1:查询大于平均年龄的学生 -- 关键词: 查询学生(的信息) --> select* from students -- 修饰词:(学生年龄) 大于平均年龄 --> age >(平均年龄) -- 组合 -- select * from students where age > (平均年龄); select avg(age) from students; -- 22.614 select * from students where age >(select avg(age) from students); -- 例2:查询王昭君的成绩,要求显示成绩 -- 关键词: 查成绩 --> select score from scores -- 修饰词: 姓名叫 王昭君 --> name = "王昭君" -- 转换: 成绩表中找到王昭君的学号,就可以查到王昭君的成绩 -- 组合 -- 方式1:直接连接 select sc.score,st.name from scores sc inner join students st on sc.studentno = st.studentNo where st.name ="王昭君"; -- 方式2:子查询--思考在同一张表中查,不具备的条件通过另一个SQL查询出 select score from scores where studentno = (王昭君的学号) -- 查王昭君的学号: select studentNo from students where name = "王昭君"; select score from scores where studentno = (select studentNo from students where name = "王昭君");
-
一行多列
-- 例3:查询和王昭君同班、同龄的学生信息 select * from students -- 关键信息:查询学生信息 -- 修饰词: 王昭君同班(class)、王昭君同龄(age) --> name="王昭君" -- select * from students where age="王昭君的年龄" and class ="王昭君的班级"; -- select class, age from students where name = "王昭君"; select * from students where ( class,age)=(select class,age from students where name = "王昭君");
-
多行一列
-- 例4:查询18岁的学生的成绩,要求显示成绩 -- 关键词:查询成绩 --> select score from scores -- 修饰词:年龄18岁 --> age = 18 -- -->找出年龄18岁的人的学号 --> studentno=(18岁的人的学号) -- 18岁的人的学号:select studentNo from students where age = 18; select score from scores where studentno in (select studentNo from students where age = 18);
-
-
子查询充当数据源:子查询的结果如果是多行多列(单独的表),需要结合连接查询
-- 例5:查询数据库和系统测试的课程成绩 -- 关键词:查询成绩 --> select score from scores -- 修饰词:数据库和系统测试 --> where name in("数据库","系统测试") -- 转换:把数据库和系统测试对应的课程号及名称信息找到 -- select courseNo,name from courses where name in("数据库","系统测试"); -- select * from scores sc -- inner join (中间表) as tmp on sc.courseNo = tmp.courseNo; -- 第一种方式:子查询是一张表 select tmp.name ,sc.score from scores sc inner join ( select courseNo,name from courses where name in("数据库","系统测试") )as tmp on sc.courseNo = tmp.courseNo ; -- 第二种方式:子查询是一列数据 select score from scores where courseNo in (select courseNo,name from courses where name in("数据库","系统测试") );