八、数据库

最新推荐文章于 2022-05-28 22:20:14 发布

粉皮皮

最新推荐文章于 2022-05-28 22:20:14 发布

阅读量184

点赞数

分类专栏：软件测试笔记

本文链接：https://blog.csdn.net/wangxf666/article/details/115490181

版权

软件测试笔记专栏收录该内容

17 篇文章 0 订阅

订阅专栏

8.1数据库概念

在这里插入图片描述

8.2SQL

在这里插入图片描述

8.3MySQL

在这里插入图片描述

8.4MySQL客户端与服务端

在这里插入图片描述

8.6Navicat使用

在这里插入图片描述

8.7SQL语言（增、删、改）

8.7.1创建表（重要）

在这里插入图片描述

create table students3(
id int unsigned primary key auto_increment,
name varchar(10),
age  int unsigned,
heigth decimal(5,2)

)

8.7.2删除表

-- 如果表存在则删除，不存在则报错
drop table stu


-- 如果表存在则删除，不存在，不删除 不报错
drop table stu if exists

8.7.3数据操作（增删改查）

（1）添加数据
在这里插入图片描述

-- -- 给所有的字段设置数据
-- insert into students3 values(0,'亚瑟1',20,162.3)

-- -- 给指定的字段设置数据
-- insert into students3(name) values('亚瑟2');
-- insert into students3(name,age) values('亚瑟3',20)

-- 插入多跳数据两种方法

-- 写多条sql语句
-- insert into students3 values(0,'鲁班1',20,162.3);
-- insert into students3 values(0,'鲁班2',20,162.3);
-- insert into students3 values(0,'鲁班3',20,162.3)

-- 一条语句插入多个数据
insert into students3(name) values('亚瑟4'),('亚瑟5'),('亚瑟6')

（2）修改数据
在这里插入图片描述

-- 修改
-- update students3 set age=19 where id=5
update students3 set name='狄仁杰', age=20 where id=5

（3）删除数据
在这里插入图片描述

-- 删除
-- 删除表内所有数据
-- delete from students3_copy
-- 删除id=6的学生
delete from students3 where id=6

逻辑删除

-- 逻辑删除
-- 1.设计表，给表添加一个字段isdelete,1代表删除，0代表没有删除
-- 2.所有的数据isdelete都改为0
-- 3.要删除某条数据，更新isdelete为1
-- 4.要查询数据时，只查询isdelete为0的数据
-- update students3 set isdelete=0
-- update students3 set isdelete=1 where id=1

select * from students3 where isdelete=0

8.8SQL语言（查询）（重要）

8.8.1简单语法

-- 创建表
-- drop table if exists students;
-- create table students (
-- studentNo varchar(10) primary key,
-- name varchar(10),
-- sex varchar(1),
-- hometown varchar(20),
-- age tinyint(4),
-- class varchar(10),
-- card varchar(20)
-- )




-- -- 插入数据
-- insert into students values
-- ('001', '王昭君', '女', '北京', '20', '1班', '25413342233333'),
-- ('002', '诸葛亮', '男', '上海', '18', '1班', '25413342233334'),
-- ('003', '张飞', '男', '北京', '22', '3班', '25413342233335'),
-- ('004', '大乔', '女', '河南', '19', '2班', ''),
-- ('005', '小乔', '女', '江苏', '22', '4班', '25413342233337'),
-- ('006', '李白', '男', '陕西', '21', '2班', '25413342233338'),
-- ('007', '妲己', '女', '河北', '19', '4班', null)
-- 




-- 查询所有数据
-- select * from students 
-- 查询指定数据
-- select name,sex,hometown from students 
-- 查询更改表头
-- select name as 姓名,sex as 性别,hometown as 家乡 from students 
-- 查询更改表名
-- select name,sex,hometown from students as s
-- 查询学生的性别有哪几种
-- select distinct sex as 性别 from students
select  distinct sex, class  from students

8.8.2 条件语法（比较运算、逻辑运算、模糊查询、空判断）

在这里插入图片描述
（1）比较运算

-- 条件查询
-- select age from students where name='小乔'

-- 比较运算
-- 查询20岁以下的
--  select * from students where age<20
-- 查询家乡不在北京的
--  select * from students where hometown!='北京'
-- 查询007号学生的身份证号
-- select card from students where studentNo='007'
-- 查询一班以外的学生信息
-- select * from students where class!='1班'
-- 查询年龄大于20岁以上的学生的姓名和性别
-- select name,sex from students where age>20

（2）逻辑运算
在这里插入图片描述

-- 逻辑运算
-- 查询年龄小于20的女同学
-- select * from students where age<20 and sex='女'
-- 查询女学生或1班的学生
--  select * from students where class='1班' or sex='女'
-- 查询非河南的学生
--  select * from students where  not hometown='河北'
-- 查询河南或河北的学生 
-- select * from students where hometown='河北'or hometown='河南'
-- 查询1班的上海的学生
--  select * from students where hometown='上海'and class='1班'
-- 查询非20岁的学生
 select * from students where not age=20

（3）模糊查询
在这里插入图片描述

-- 模糊查询
-- 查询姓李的学生
-- select * from students where name like '李%'
-- 查询名字为两个字的学生
-- select * from students where name like '__'
-- 查询姓张且年龄大于20的学生
-- select * from students where name like '张%' and age>20
-- 查询学号以3结尾的学生
-- select * from students where studentno like '%3'

（4）范围查询
在这里插入图片描述

-- 范围查询
-- 查询家乡是北京或上海或河南的学生
--  select * from students where hometown='北京'or hometown='上海'or hometown='河南'
-- select * from students where hometown in ('北京','上海','河南')
-- 查询年龄18至20的学生
-- select * from students where age between 18 and 20
-- select * from students where age>=18 and age<=20
-- 查询年龄在18或19或22的女生
-- select * from students where age in (18,19,22) and sex='女'
-- 查询年龄在18到21以外的学生
-- select * from students where not age between 18 and 21

（5）空判断
在这里插入图片描述

-- 空判断
-- 查询没有填写身份证的学生
-- select * from students where card is null
-- select * from students where card=''
-- 查询填写身份证的学生
-- select * from students where card is  not null

8.8.3 排序语法

在这里插入图片描述

-- 排序
-- 查询所有学生信息，按照年龄从小到大排
-- select * from students order by age asc
-- 降序
-- select * from students order by age desc
-- 查询所有学生信息，按照年龄从大到小排，年龄相同时按学号从小到大排
-- select * from students order by age desc, studentno 
-- 查询所有学生信息，按照班级从小到大排，班级相同时按学号从小到大排
-- select * from students order by class, studentno 
-- 查询所有学生信息，按照name(对中文)
-- select * from students order by convert(name using gbk)

8.8.4聚合函数

在这里插入图片描述

-- 聚合函数
-- 查询学生总数
-- select count(*) from students 
-- 查询女生中的最大年龄
-- select max(age) from students where sex='女'
-- 查询1班的最小年龄
-- select min(age) from students where class='1班'
-- 查询北京学生的年龄总和
-- select sum(age) from students where hometown='北京'
-- 查询女生的平均年龄
-- select avg(age) from students where sex='女'
-- 查询学生的最大年龄、最小年龄、平均年龄
--  select max(age)as 最大年龄,  min(age) as 最小年龄, avg(age) as 平均年龄 from students 
-- 1班共有多少学生
--  select count(*) from students where class='1班'
-- 查询1班年龄小于19岁的人
-- select count(*) from students where class='1班' and age<19

8.8.5分组

在这里插入图片描述

-- 分组
-- 查询各种性别的人数
-- select sex, count(*) from students group by sex
-- 查询各种年龄的人数
-- select age, count(*) from students group by age
-- 查询各个班级学生的最大年龄、最小年龄、平均年龄
--  select class, max(age)as 最大年龄,  min(age) as 最小年龄, avg(age) as 平均年龄 from students group by class
-- 查询各个班级学生男女生人数
-- select class, sex, count(*) from students group by class,sex 
-- 查询男生总人数
-- 方案1
-- select count(*) from students where sex='男' 
-- 方案2(分组后过滤)
-- select sex,count(*) from students group by sex having sex='男'
-- 查询1班除外其他班级学生的平均年龄、最大年龄、最小年龄
-- 方案1
-- select  class, avg(age) as 平均年龄, max(age)as 最大年龄,  min(age) as 最小年龄 from students where class!='1班' group by class 
-- 方案2
-- select  class, avg(age) as 平均年龄, max(age)as 最大年龄,  min(age) as 最小年龄 from students group by class having class !='1班

8.8.6分页

在这里插入图片描述

-- 获取部分行
-- 查询前三行学生信息
-- select * from students limit 0,3
--  select * from students limit 4,2
-- select * from students order by age limit 0,3
-- 检查第四到第六行学生信息
-- select * from students limit 3,3

在这里插入图片描述

分页
-- 要求每页显示三条
-- select count(*) from students
-- 7/3获取总页数
-- 第一页
-- select * from students limit 0,3  3*（1-1）
-- 第二页
-- select * from students limit 3,3  3*（2-1）
-- 第三页
-- select * from students limit 6,3  3*（3-1）

-- 要求每页显示五条
-- 第一页
-- select * from students limit 0,5  5*（1-1）
-- 第二页
-- select * from students limit 5,5  5*（2-1）

-- select * from students limit 0,7
-- select * from students limit 7

8.8.7连接查询(等值连接、内连接、左连接、右连接)

在这里插入图片描述

等值连接

此方式会产生笛卡尔积，生成表记录总数=表1总数*表2总数
会产生临时表占内存

-- 查询学生信息以及学生成绩
select * from students as stu,scores as sc where stu.studentNo=sc.studentno

内连接
不会产生笛卡尔积，也不会产生临时表，性能高

select * from students 
inner join scores on students.studentno=scores.studentno

8.8.8查询练习题

在这里插入图片描述

-- 查询课程信息及课程成绩
--方法1 等值连接
-- select * from courses,scores where courses.courseNo=scores.courseno
-- 方法2内连接
-- select * from courses
-- inner join scores on courses.courseno=scores.courseno

-- 查询学生信息及学生的课程对应的成绩
-- 方法1等值连接
-- SELECT
-- 	*
-- FROM
-- 	students,
-- 	courses,
-- 	scores
-- WHERE
-- 	STUDENTS.studentNo = scores.studentno
-- AND courses.courseNo = scores.courseNo
-- 方法2内连接
-- select * from students
-- inner join scores on students.studentno=scores.studentno
-- inner join courses on scores.courseno=courses.courseno

-- 查询王昭君的成绩，要求显示姓名、课程号、成绩
-- select students.name,courses.name as 课程名,courses.courseno,score  from students
-- inner join scores on students.studentno=scores.studentno
-- inner join courses on scores.courseno=courses.courseno



 -- 查询王昭君的数据库成绩，要求显示姓名、课程号、成绩
-- 方法1内连接
-- select students.name,courses.name as 课程名,courses.courseno,score  
-- from students
-- inner join scores on students.studentno=scores.studentno
-- inner join courses on scores.courseno=courses.courseno
-- where students.name='王昭君' 
-- and courses.name='数据库'
-- 方法2等值连接
-- select students.name,courses.name as 课程名,courses.courseno,score 
-- from students,courses,scores 
-- where students.studentno=scores.studentno and scores.courseno=courses.courseno
-- and students.name='王昭君' 
-- and courses.name='数据库'


-- 查询所有学生数据库成绩，要求显示姓名、课程号、成绩
-- select students.name,courses.name as 课程名,courses.courseno,score 
-- from students,courses,scores 
-- where students.studentno=scores.studentno and scores.courseno=courses.courseno
-- and courses.name='数据库'


-- 查询所有男生中最高成绩，要求显示姓名、课程号、成绩
-- select students.name,courses.name as 课程名,courses.courseno,score  
-- from students
-- inner join scores on students.studentno=scores.studentno
-- inner join courses on scores.courseno=courses.courseno
-- where sex='男' 
-- order by 
-- score desc 
-- limit 1

左连接

-- 左连接 join前边的为左表,把左表全部显示出来

-- 查询所有学生的成绩，包括没有成绩的同学
-- select * from students
-- inner join scores on students.studentno=scores.studentno
-- -- inner join 做不到
-- select * from students
-- left join scores on students.studentno=scores.studentno

-- 查询所有学生的成绩，包括没有成绩的同学，需要查询课程名
-- select * from students
-- left join scores on students.studentno=scores.studentno
-- -- join 前面生成的称为左表
-- left join courses on scores.courseno=courses.courseno

右连接

-- 右连接，把左表全部显示出来
-- 查询所有课程的成绩，包括没有成绩的课程
-- 新插入数据
-- insert into courses VALUES
-- (0,'语文'),
-- (0,'数学')

-- select * from scores
-- right join courses on scores.courseno=courses.courseno



-- 查询所有课程的成绩，包括没有成绩的课程，包括学生信息
-- select * from scores
-- right join courses on scores.courseno=courses.courseno
-- left join students on students.studentno=scores.studentno

-- 查询所有学生的成绩，包括没有成绩的同学，需要查询课程名
-- 方法1
-- select * from scores
-- inner join courses on scores.courseno=courses.courseno
-- right join students on students.studentno=scores.studentno
-- 方法2
select * from scores
right join courses on scores.courseno=courses.courseno
left join students on students.studentno=scores.studentno

8.8.9自关联（没那么重要）

在这里插入图片描述
准备数据

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');

-- 自关联
-- 查询河南省所有的城市
-- 数据源，从一个表中查询多次，必须起别名
-- select * from areas as sheng,areas as shi
-- where sheng.aid=shi.pid and sheng.atitle='河南省'

-- 添加区县数据
-- insert into areas VALUES
-- ('410101','中原区','410100'),
-- ('410102','二七区','410100'),
-- ('410103','金水区','410100');

-- 查询郑州市所有的区
-- select * from areas as sheng,areas as shi
-- where sheng.aid=shi.pid and sheng.atitle='郑州市'

-- 查询河南省所有的区县
-- select * from areas as sheng,areas as shi,areas as qu
-- where sheng.aid=shi.pid and sheng.atitle='河南省'
-- and shi.aid=qu.pid

8.8.10子查询

在这里插入图片描述

-- 子查询(标量子查询)
-- 查询大于平均年龄的学生
-- 方法1
-- select avg(age) from students
-- 20.1429
-- select * from students where age>20.1429
-- 方法2
--  select * from students where age>(select avg(age) from students)

-- 查询最小年龄的人
--  select * from students where age=(select min(age) from students)

-- 查询王昭君的成绩，要求显示成绩
-- select studentno from students where name='王昭君'
-- select score from scores  where studentno=(select studentno from students where name='王昭君')

-- 查询王昭君的数据库成绩
-- select score from scores  where studentno=(select studentno from students where name='王昭君')
-- and courseno=(select courseno from courses where name='数据库')


-- 列子查询（返回的是多行一列）
-- 查询18岁学生的成绩，要求显示成绩
-- 1
-- select * from students where age=22
-- select* from scores  where studentno in ('003','005')
-- 2
-- select* from scores  where studentno in (select studentno from students where age=22)


-- 行子查询（返回的是多行一列）
-- 查询男生中年龄最大的学生
-- 1
-- select * from students where sex='男' and age='22'
-- 2
-- select * from students where (sex, age)=('男' ,22)
-- 3
-- select * from students where (sex, age)=(select sex,age from students where sex='男' order by age desc limit 1)

-- 表子查询(子查询返回的是一个表多行多列)
-- 查询数据库和系统测试的课程成绩
-- 1
-- select * from scores
-- inner join courses on scores.courseNo=courses.courseNo
-- where courses.name in ('数据库','系统测试')
-- 
-- 2
-- select * from scores
-- inner join 
-- (select * from courses where name in ('数据库','系统测试')) as c  on scores.courseNo=courses.courseNo

子查询中特定关键字的使用

-- 子查询中关键字的使用
-- =any 等于 in 等于 some
-- select age from students where age in (select age from students where age between 18 and 20)
 
-- select age from students where age = any (select age from students where age between 18 and 20)

-- select age from students where age = some (select age from students where age between 18 and 20)

-- select age from students where age < any (select age from students where age between 18 and 20)

-- select age from students where age > any (select age from students where age between 18 and 20)

-- select age from students where age > all (select age from students where age between 18 and 20)

-- select age from students where age < all (select age from students where age between 19 and 20)

-- select age from students where age  !=all (select age from students where age between 18 and 20)