五、初始数据库
五、初始数据库
1 初始数据库
1.1什么是数据库技术?
数据库所研究的问题就是如何科学的组织和存储数据,如何高效地获取和处理数据。
1.2 SQL语言(结构化查询语言)
SQL是专为数据库而建立的操作命令集,是一种功能齐全地数据库语言。
使用SQL语句的时候,只需要发出“做什么”地命令,具体“怎么做”是不需要使用者来参与,直接看SQL语句反馈的结果就行。
1.3数据库的基本架构
①数据(Data):对客观事物地符号表示,如图形符号、数字、字母等,数据是数据库中存储地基本对象。
数据的形式不能完全表达其内容
②数据库(Database):长期存在计算机内的、有组织的、可共享的数据集合。
数据库中的数据按一定的数据模型组织、描述和存储。
数据库中数据的特征:具有较小的冗余度(重复度)、较高的数据独立性和易扩展性(可修改),并可为各个用户共享。
③数据库管理系统(DBMS):位于应用程序与存储数据的的之间的一层数据管理软件。
用途是科学地组织和存储数据,高效地获取和处理数据。
DBMS的主要功能:
1、数据库的定义功能 :在SQL语言中——DDL语言(数据库定义语言),提供了三个语句:ceate(创建)、alter(修改)、drop(删除),操作对象是数据库中的基本架构——表。
备注:虽然数据库中存储的是各种各样的数据,但是数据是以表的形式来组织和存放的。
2、数据库的操作功能:
借助于SQL语言中——DML语言(数据库操作语言),实现对数据库中存储的数据进行增删改查的操作(insert、delete、update、select)
3、数据库的保护功能:保护数据的完整性和安全性
4、数据库的维护功能:数据库中表结构和数据的维护功能
DBMS的优点:
④数据库系统(DBS)
数据库系统=数据库(DB)+数据库管理系统(DBMS)+应用系统+数据库管理员+用户
数据库系统的发展经历3个阶段:
网状数据库、层次数据库、关系数据库,
数据组织方式:网状模型、 层次模型、 关系模型
架构: 图形结构 树形结构 表
1.4关系型数据库
关系型数据库的重要知识点:
1、信息:指的是数据经过加工处理后所获取的有用知识,信息是以某种数据形式来表示的
2、信息的分类——信息的3种世界:
现实世界:存在于人脑之外的客观世界
信息世界(观念世界):指的是现实世界在人脑中的反应
数据世界:指的是信息世界的产物在计算机中的表示。
1.4.1实体-联系模型-----E-R图
实体:客观事物在信息世界中被称为实体
属性:描述实体的数据项
学生是一个实体,属性:学号、姓名、性别体重....
联系:反映实体与实体之间的关联集合。常见实体联系:一对一、一对多、多对多
一对一:家教和孩子
一对多:老师和学生
多对多:学生和课程(一门课程可以被多个同学选修,一个同学可以选修多门课程)
1.4.2关系模型(关系型数据库中数据的组织方式)
关系:指的是一张表,没有重复行和重复列
关系名:表名
元组:指的是表中的行,也叫做记录。因为
属性:指的是表中的列,也叫做字段
属性名:指的是列名,也叫做字段名
属性值:指的是每个列的取值
域:属性的取值范围。
关键字:关系中唯一能区分、确定不同元组的属性或属性组合,称为该关系的一个关键字。
指的是列(可以把一个列或者多个列看成是关键字),做到唯一区分的作用。
一旦把某个列看成是关键字,其需要满足:唯一、不重复、不能为空
可以互推的过程:关键字<===>唯一、不重复、不能为空
关键字分为:
①主键(主关键字):针对一个表中的某个列来说的,假如把一个表中的某个列设置成主键,那么这个列就一定要求:唯一、不重复、不能为空
②外键:针对两个表来说的,加强表与表之间的联系。外键的设置也是在于列
1.4.3关系模式(对关系的描述)
格式:关系名(属性名1,属性名2,属性名3,.......)
在关系模式中,关系的特征:
1. 关系必须规范化,属性不可再分
2. 在同一个关系中,属性名不能重复
3. 在同一个关系中,元组和属性的顺序可以是任意的
1.4.4关系运算
1. 选择运算:从行的角度来出发的,在一个表中找出满足一定条件的行。
例如:查询计算机2001班来自陕西的学生有哪些?
2. 投影运算:从列的角度来出发的,在一个表中找出满足需求的列。
例如:员工信息表(员工编号,姓名,年龄,部门,工资),查询表中员工编号,姓名,部门
3. 连接运算:笛卡尔积,在两个关系中,把两个表中能够匹配到结果全部罗列出来(冗余)
4. 例如:集合A{a,b},集合B{0,1,2},A和B的笛卡尔积为{(a,0)(a,1)(a,2)(b,0)(b,1)(b,2)}
1.5关系的完整性约束
1. 实体完整性:主属性值不能为空。(是限定表中数据的约束)
主键:唯一,不重复,不为空
2.参照完整性:针对两个表来说的
会把两个表分别拆分成一个主表,一个从表;
参照关系:从表参照主表---->从表所作的操作,必须询问主表的意见,主表有的,从表才能做对应的操作,主表没有,就不能进行操作。
从表中列不是随便参照主表中的某个列,必须要求参照主表的这个列必须是主键约束。
2. 域完整性:限制某个列的取值要求,把取值固定在一个有效的集合范围之内。
1.6关系模型的规范化(范式)
定义:关系模型要满足的条件被称为规范化形式,简称NF
目的:减少数据冗余,消除存储异常,保证数据的完整性和存储效率,一般是3NF。
第一范式(1NF):如果关系R的所有属性都是简单属性,及每个属性都是不可再分的,则称R满足第一范式,其实就是没有重复的列。
第二范式(2NF):如果关系R先满足第一范式,且非主键字段完全依赖于主键,则称满足第二范式。
第三范式(3NF):如果关系R满足第二范式,且非主键字段之间不存在依赖关系,则满足第三范式。
一个基本的关系型数据库要满足第一范式,一个完整的关系型数据库要满足第三范式
1.7 E-R图(实体-联系图)
实体: 矩形内部写的是实体的名字
属性: 椭圆内写的是属性的名字
联系: 菱形内部写的是联系名
用线段连接起来
常见的联系:一对一(1:1)、一对多(1:n)、多对多(m:n)
1.8 MYSQL数据库
优点:体积小、速度快、成本低、开源、可移植性(跨平台)、可以和开发语言结合。
启动数据库:net start mysql80 停止数据库服务:net stop mysql80
登录数据库:mysql -u root -p 密码:root
显示现有数据库:show databases;
创建数据库:create database库名;
删除数据库:drop database库名;
2 数据库建表原则和方式
2.1 mysql数据类型
(1)数值型
整数类型:int
小数类型:decimal、float、double
小数格式:decimal(总长度,小数)
例如:decimal(5,2) 要求传递过来的数据总长度是5为,小数位是2位 123.45
(2)日期和时间类型
Datetime YYYY-MM-DD HH:MM:SS
Date YYYY-MM-DD
(3)字符串类型
Char(字符串长度) 定长
Varchar(字符串长度) 变长
姓名char(10) tom 3个字符,剩余七个字符的空间不会释放,系统会用空格填满
Varchar(10) tom 3个字符,剩余七个字符的空间给释放出来
2.2表
(1)创建表
创建表的语法格式:
Create table表名(
列名1数据类型,
列名2数据类型,
列名3数据类型);
切换数据库:use库名;
数据库的注释:
单行注释:--内容
多行注释:/*内容*/
(2)删除表
删除表:drop table表名;
删除多张表:drop table表名1,表名2,......;
(3) 修改表
添加列:alter table表名add列名 数据类型;
删除列:alter table表名drop列名;
修改属性的数据类型:alter table表名modify列名 新的数据类型;
修改字段名:alter table表名change旧字段名 新字段名 数据类型;
(4) 显示表结构
desc表名;
2.3约束
数据的完整性:指的是数据库中数据的准确性和可靠性
实体完整性约束:
目的:在表中至少要有一个唯一的标识,主属性字段中,不能为空,不能重复
2.3.1主键约束
主键约束:唯一、不重复、不能为空(Primary key)一个表中有且只有一个主键约束
1、创建表时创建主键约束
格式1:
create table表名(
列名1数据类型primary key,
列名2数据类型
);
格式2:主键约束的名字的写法:PK_列名
create table表名(
列名1数据类型,
列名2数据类型,
constraint主键约束的名字primary key(列名)
);
格式3:
create table表名(
列名1数据类型,
列名2数据类型,
primary key(列名1)
);
2、针对已经存在的表,添加主键约束
格式1:
alter table表名modify列名 数据类型primary key;
格式2:
Alter table表名add primary key(列名);
格式3:
Alter table表名add constraint主键约束的名字primary key(列名);
3、删除主键约束
Alter table表名drop primary key ;
2.3.2联合主键
联合主键:指的是把两个列看成一个整体,这个整体不为空,唯一,不重复
1、创建表的同时创建联合主键
格式2:主键约束的名字的写法:PK_列名
create table表名(
列名1数据类型,
列名2数据类型,
constraint主键约束的名字primary key(列名1,列名2)
);
格式3:
create table表名(
列名1数据类型,
列名2数据类型,
primary key(列名1,列名2)
);
2、针对已经存在的表,添加联合主键
格式2:
Alter table表名add primary key(列名1,列名2);
格式3:
Alter table表名add constraint主键约束的名字primary key(列名1,列名2);
2.3.3唯一约束
唯一约束特征: nuique
1. 不允许有重复的值,保证数据的唯一性
2. 可以有空值
3. 在一个表中,可以有多个唯一约束
4. 默认情况下,唯一约束的名字和列名保持一致
5. 添加唯一约束的列,系统也会默认给这个列添加一个唯一索引
1、创建表的同时创建唯一约束 UN_列名
格式1:
Create table表名(
列名1数据类型,
列名2数据类型,
constraint唯一约束的名字unique(列名1),
constraint唯一约束的名字unique(列名2)
);
格式2:
Create table表名(
列名1数据类型unique,
列名2数据类型unique,
列名3数据类型
);
2、针对已经存在的表,添加唯一约束
格式:alter table表名add unique(列名[,列名2]);
3、删除唯一约束
格式:alter table表名drop index唯一约束的名字;
域完整性约束:保证在表中不会输入无效的数据
2.3.4 默认约束
默认约束: default当默认约束来修饰某个列的时候,修饰的列即使不写数据也会默认一个值的。
1、创建表的同时创建默认约束
格式:
create table表名(
列名1数据类型default‘字符串类型或者日期类型的默认值’,
列名2数据类型default数值,
列名3数据类型
);
2、针对已经存在的表,添加默认约束
格式:alter table表名modify列名 数据类型default‘值’;
3、删除默认约束
格式:alter table表名modify列名 数据类型;
非空约束not null当前列必须有值
1、创建表时创建非空约束
格式:
Create table表名(
列名1数据类型not null,
列名2数据类型not null,
列名3数据类型
);
2、针对已有的表,添加非空约束
格式:
Alter table表名modify列名 数据类型not null;
3、删除非空约束
格式:alter table表名modify列名 数据类型;
参照完整性约束
2.3.5 外键约束
外键约束foreign key
外键约束的注意事项:外键约束是建立在从表上;当从表的列参照主表的列,列名可以不一样,但是里面的数据类型和内容要保持一致;从表引用主表的列,要求主表的列必须有主键约束或唯一约束;当主表的数据,被从表引用,主表的数据是没办法进行删除;当从表中的数据想做操作时,先询问主表的意见,主表有的,允许从表操作,主表没有,拒绝从表操作。
1、创建表的同时创建外键约束
主表
Create table表1(
列名1数据类型primary key,
列名2数据类型
);
从表(外键约束所在的表)FK_列名
Create table表2(
列名1数据类型,
列名2数据类型,
Constraint外键约束的名字foreign key(从表的列名1)references主表表名(列名1)
);
注意:关于引用的列要求是,从表的列和主表的列,列名可以不一样,但是这两个列的数据类型和内容必须保持一致
2、针对已经存在的表,添加外键约束
格式:
Alter table从表表名add constraint外键约束的名字foreign key(从表列名1)reference主表表名(主表列名);
3、删除外键约束
格式:alter table表名drop foreign key外键约束的名字;
2.3.6练习题
--写出创建教师授课管理数据库的SQL语句
create database教师授课管理系统;
--切换数据库
use数据库管理系统;
--创建教师表
create table teacher(
Tno varchar(7) primary key,
Tname varchar(10) not null,
Tsex char(2) default '男',
Birthday datetime,
Dept varchar(20),
Sid varchar(18)
);
--创建课程表Course
create table Course(
Cno varchar(10) primary key,
Cname varchar(20) not null,
Credit tinyint not null,
Category varchar(10) default '必修' not null
);
--创建授课表TC
create table TC(
Tno varchar(7) ,
Cno varchar(10),
Hours tinyint default 4,
constraint FK_Tno foreign key(Tno) references teacher(Tno)
);
--设置教师表Sid列不允许重复(唯一约束)
alter table teacher add unique(Sid);
--设置授课表的主码为Tno和Cno的组合(联合主键)
alter table tc add constraint PK_TC primary key(Tno,Cno);
--删除课程表Category列的default约束
alter table course modify category varchar(10);
--设置授课表的列Cno是引用课程表的Cno的外码
alter table TC add constraint FK_Cno foreign key(Cno) references course(Cno);
2.4 SQL语言(增、删、改)
2.4.1 SQL语言分类
1.DDL语句 数据库定义语言
Create:创建数据库alter:修改表 drop:删除库表
2.DML语言 数据库操作语言 对表中数据的操作
Insert插入(增)delete删除(删)update更新(改)
2.4.2 insert 插入语句
格式1:(插入的值要和列保持一一对应的关系)
Insert into表名(列名1,列名2,列名3... ) values(值1,值2,值3..);
格式2:(插入的值要和列保持一一对应的关系)
Insert into表名values(值1,值2,值3..);
格式3:
Insert into表名values(值1,值2,值3...),(值1,值2,值3..),(...);
2.4.3 delete 删除
delete和drop的区别:delete仅限于把表中的数据给删除,表结构还留着;drop是把表和数据一起删除了
格式1:
Delete from表名; 把整个表中的数据全部删除
格式2:
Delete from表名where条件; 根据条件来删除数据
And条件同时满足
Delete from表名where条件1 and条件2 and条件3....;
Or多个条件只需要满足其中一个
Delete from表名where条件1 or条件2 or条件3....;
2.4.4 update 更新
格式1:(整列的值都被修改)
Update表名set列名=值;
格式2:
Update表名set列名1=值1,列名2=值2;
格式3:有条件的更新
Update表名set列名=值where条件;
例子:(在更新过程中结合运算操作)
--把每个学生的年龄在原有的基础上增加3岁
UPDATE student SET年龄=年龄+3;
2.5 SQL语言(查)
2.5.1基本语句
基本语法结构(只要说到查询,一定离不开的两个关键词)
格式:
Select 列名from 表名;
Select子句---->select列名 要查询的是什么?
From子句---- >from表名 从哪里来查?
Select * from表名; -查询表中所有信息
查询多个列格式:Select列名1,列名2,列名3 from表名;
2.5.2 distinct
格式:(对查询结果进行去重复)
Select distinct列名from表名;
2.5.3 别名查询
针对查询出来的结果给它命名一个新的标题。
格式1:Select列名1 ‘别名1’ ,列名2‘别名2’ from表名;
格式2:Select列名as ‘别名’,列名2 as‘别名2’ from 表名;
计算列:指的是针对已经查询出来的结果,再进行计算。
例子:
--查询学生的姓名、年龄、在原有年龄上+10岁之后的结果
SELECT姓名,年龄,年龄+10 '新年龄' from student;
2.5.4 条件查询
2.5.4.1选择查询
格式:
Select列名from表名where条件;
Where子句就是用来限定表中数据的查询;
2.5.4.2比较运算符
= > < >= <= != <>
2.5.4.3逻辑运算符
And连接多个条件,这些条件必须同时满足
格式:select列名from where条件1 and条件2;
Or连接多个条件,这些条件满足其中一个即可
格式:select列名from where条件1 or条件2;
2.5.4.4范围搜索条件
针对查询结果满足某个范围内的数据。[开始值,结束值]
在某个范围内:between ... and
select列名from表名where列名between开始值and结束值;
不在某个范围值内:not between ... and
select列名from表名where列名not between开始值and结束值;
2.5.4.5列表搜索条件
通过IN关键字,使用户拿上条件可以和列表中任意值来做匹配,只要满足其中一个值即可
在这个列表范围值内 in[or]
格式:select列名from表名where列名in(值1,值2);
不在这个列表范围之内的 not in
格式:select列名from表名where列名not in(值1,值2);
2.5.4.6字符匹配符
like-模糊查询
格式:select列名from表名where列名like‘字符模式’;
排除某个字符模式:not like
格式:select列名from表名where列名not like‘字符模式’;
通配符:
%代表的是零个或多个字符
_代表的是一个字符
2.5.4.7空值的查询
空值(null)在数据库中表示不确定的值。
判断取值为空的格式:
Select列名from表名where列名is null;
判断取值不为空的格式:
Select列名from表名where列名is not null;
2.5.5 聚合函数
Sum(列名)对某个列进行求和
Avg(列名) 对某个列进行求平均值
Min(列名) 对某个列求最小值
Max(列名) 对某个列求最大值
Count(*) 统计元组的格式(看一个表有多少条记录)//count(*)计算空值,其余均不计算空值
Count(列名) 统计某个列有多少个值
格式:
Select聚合函数from表名;
Select聚合函数1,聚合函数2 from表名;
2.5.6 行数限定
格式:select列名from表名limit [指定开始查询的行, ]查几行;
开始行不写的话,默认从第0行开始。
2.5.7 分组
2.5.7.1 分组Group by
针对查询出来的结果进行分组
格式:
select列名 聚合函数from表名group by列名;
分组:
1. 先查询结果(先统计出相关的总数或总和)
2. 统计出来之后或查询出来之后,再来进行分组(依据某个列进行的分组)
例:统计每个年级的总人数
1. 先求出整个学校的总人数
2. 再根据年级进行分组
2.5.7.2 分组条件 having
在分组中进行条件限制(对分组结果添加条件)
格式:
Select列名from表名group by列名having条件(一般情况下都是聚合函数当作条件);
注意:
1. having必须和group by搭配使用
2. Having后面可以跟聚合函数,但where不可以
2.5.8排序
(升序)格式:select列名from表名order by列名[asc];
(降序)格式:select列名from表名order by列名desc;
扩充:排序根据两个列来排序
格式:select列名from表名order by列名1 asc,列名2 desc;
例子:查询成绩表中所有学生的记录,要求按照成绩升序排列,成绩相同时,按照学号进行降序排列
Select * from成绩表order by成绩asc,学号desc;
2.5.9 查询练习
做题思想:
1. 先确定表
2. 确定要查询的列
3. 查询条件
4. 分组
5. 排序
题目:
1、查询全体学生的记录
Select * from学生表;
2、查询学生选课表中的全部数据。
Select * from课程表;
3、查询全体学生的学号与姓名。
Select学号,姓名from学生表;
4、查询全体学生的姓名、学号、和所在系。
Select学号,姓名,系别from学生表;
5、查询全体学生的姓名及其出生年份。
Select姓名,year(出生日期) from学生表;
6、查询计算机系全体学生的姓名;
Select姓名from学生表 where系别=’计算机系’;
7、查询年龄在20岁以下的学生的姓名及年龄。
Select姓名,年龄from学生表where年龄<20;
8、查询考试成绩有不及格的学生的学号。
Select学号from成绩表where成绩<60;
9、查询成绩在70-80分之间的学生,包括学号,课程号和成绩。
Select学号,课程号,成绩from成绩表where成绩between 70 and 80;
10、查询年龄在20-23岁之间的学生的姓名,所在系和年龄。
Select姓名,系别,年龄from学生表where年龄between 20 and 23;
11、查询年龄不在20-23岁之间的学生姓名,所在系和年龄。
Select姓名,系别,年龄from学生表where年龄not between 20 and 23;
12、查询信息系和计算机系学生的姓名和性别。
Select姓名,性别from学生表where系别in (‘信息系’,’计算机系’)’;
13、查询既不是信息系,也不是计算机系学生的姓名和性别
Select姓名,性别from学生表where系别not in (‘信息系’,’计算机系’)’;
14、查询姓张的学生的详细信息。
Select * from学生表where姓名like‘张%’;
15、查询无考试成绩的学生的学号和相应的课程号
Select学号,课程号from成绩表where成绩is null;
16、查询所有考试成绩的学生的学号和课程号
Select学号,课程号from成绩表where成绩is not null;
17、将学生按年龄的升序排序
Select * from学生表where order by年龄;
18、查询选修了’c02’号课程的学生的学号及成绩,查询结果按成绩降序排列
Select学号,成绩from成绩表where课程号=’c02’ order by成绩desc;
19、查询全体学生的信息,查询结果按所在系的系名升序排列,同一系的学生按年龄降序排列
Select * from where order by系名asc,年龄desc;
20、查询计算机系年龄在18-20之间,且性别为男的学生,包括姓名和年龄
Select姓名,年龄from学生表where系别=’计算机系’ and年龄between 18 and 20 and性别=’男’;
聚合函数
1、统计学生总人数
Select count(*) ‘总人数’ from学生表;
2、统计选修了课程的学生的人数
Select count(distinct学号) from课程表;
3、计算机9512101号学生的考试总成绩之和
Select学号,sum(成绩) from成绩表where学号=’9512101’;
4、计算’C01’号课程学生的考试平均成绩
Select avg(成绩) from成绩表where课程号=’C01’;
5、查询选修了’C01’号课程的学生的最高分和最低分
Select max(成绩),min(成绩) from成绩表where课程号=’C01’;
6、查询计算机系学生的最大年龄和最小年龄;
Select max(年龄),min(年龄) from学生表;
7、统计每个系的学生人数
Select系别,count(*)‘学生人数’ from学生表group by系别;
8、统计每门课程的选课人数和考试最高分
Select课程号,count(*)‘选课人数’,max(成绩)‘最高分’ from成绩表group by课程号;
9、统计每名学生的选课门数和平均成绩
Select学号,count(*)‘选课门数’,avg(成绩)‘平均成绩’ from成绩表group by学号;
10、查询修课门数等于或大于4门的学生的平均成绩和选课门数
Select学号,avg(成绩)‘平均成绩’,count(*) ‘选课门数’ from成绩表group by学号having count(*)>=4;
11、统计每个学生的选课门数和考试总成绩,并按选课门数升序显示结果
Select学号,count(*)‘选课门数’,sum(成绩)‘总成绩’ from成绩表group by学号order by count(*) asc;
12、查询总成绩超过200分的学生,要求列出学号,总成绩
Select学号,sum(成绩)‘总成绩’ from成绩表group by学号having sum(成绩)>200;
2.6 MYSQL函数(只有在mysql中可以用)
2.6.1 字符串函数
①Length(字符串)求的是字符串的字节长度
MySQL数据库中,一个汉字占3个字节,一个字母占1个字节
②Char_length(字符串)字符串的字符长度
③Mid(原始字符串,截取的开始位置,截取的长度)从某个位置获取某个长度的字符(不是字节)
2.6.2 数学函数
MySQL***默认保留4位小数。
①Round(原始数值,保留小位数)--实现四舍五入
Round(原始数值)--------只保留整数部分
例子:求学生的平均成绩,结构保留两位小数
Select round(avg(grade),2) ‘平均成绩’ from sc;
②Least(值1,值2,值3...)求最小的数字--括号内必须传具体的值
③Geratest(值1,值2,值3...)求最大的数字
练习:
对45.8793保留三位数
Select round(45.8793,3);
求45,34,45,43,322中的最大值,最小值
Select least(45,34,45,43,322);
Select geratest(45,34,45,43,322);
2.6.3 时间日期函数
①now()获取当前日期和时间
②current_date()获取当前的日期
③current_time()获取当前的时间
④to_days(日期值)将日期转换成总天数
Select to_days(now());
⑤dayofyear(日期值) 求该年已过的天数
Select dayodyear(now());
⑥week(日期值)求该年已过的周数
Select week(now());
2.6.4 控制函数
①if(布尔表达式,参数1,参数2)--布尔表达式为真,输出参数1,否则输出参数2
Select if(5<6,’test’,’testing’);--输出test
Select if(5>6,’teat’,’testing’);--输出testing
if(null,参数1,参数2)--第一个参数为null,直接输出参数2,否则输出参数1
Select if(null,’teat’,’testing’);--输出testing
②ifnull(参数1,参数2)--输出参数1
Select ifnull(’test’,’testing’);----输出test
Ifnull(null,参数1)--输出参数1
Select ifnull(null,’testing’);--输出testing
2.7表连接
前提:有时候我们所需要的数据不止在一张表中,需要多个表做结合的查询,就可以用表连接实现。
(提供了两种表与表之间的连接方式)
2.7.1内部连接
表与表之间建立起关联的列:表名可以不一致,但是这两个列数据类型和内容保持一致
内连接查询:只关联表与表中能够匹配到的数据信息,才能有对应的查询结果
①表连接的实现是通过where关键字来进行的关联
格式1:
Select 表名1.列名1,表名1.列名2,表名2.列名1,表名1.列名2
From 表名1,表名2
Where 表名1.列名1=表名2.列名1;
②通过inner join on实现表与表之间的连接
格式2:
Select 表名1.列名1,表名1.列名2,表名2.列名1,表名1.列名2
From表名1 [inner] join表名2
On 表名1.列名1=表名2.列名1;
⭐Inner join on又划分为两个分类:
1. 等值连接 指的是条件中只包含等号“=”,没有其他符号在里面
2. 非等值连接 指的是条件中除了等号之外,还有其他的符号> >= < <= <>
多个表查询(表数量>2):
格式1:
Select 表名.列名
From 表名1,表名2,表名3
Where 表名1.列名=表名2.列名 and 表名2.列名=表名3.列名;
格式2:
Select 表名.列名
From 表名1 inner join 表名2
On 表名1.列=表名2.列 inner join 表名3
On 表名2.列=表名3.列
做题思路:
1. 确定表明确用到的表的数量
2. 确定列用到了哪张表中的哪个列表名.列名
3. 确定表与表之间能够关联的列表名1.列=表名2.列
4. 确定题上是否还有额外的要求
2.7.2 内连接案例
案例一:查询学生的学号,姓名,性别,以及所在的班级名称和年级。
学生信息表:
班级信息表
第一种:
Select学生信息.学号,学生信息.姓名,学生信息.性别,班级信息.班级名称,班级信息.年级
From学生信息,班级信息
Where学生信息.班级编号=班级信息.班级编号;
第二种:
Select S.学号,S.姓名,S.性别,C.班级名称,C.年级
From学生信息S inner join班级信息C
Where S.班级编号=C.班级编号;
运行结果:
案例二:查询学生的学号,姓名,系别,成绩
Student表:
Sc表:
格式1:
Select student.sno,sname,sdept,grade
From student,sc
Where student.sno=sc.sno
格式2:
Select s.sno,sname,sdept,c.grade
From student s inner join sc c
on s.sno=c.sno
运行结果:
案例三:查询籍贯是beijing,班级是以”pu”开头的学生的学号,姓名,性别,以及所在的班级名称和年级
学生信息表:
班级信息表:
格式1:
Select s.学号,s.姓名,s.性别,b.班级名称,b.年级
From学生信息s ,班级信息b
Where s.班级编号= b.班级编号and s.籍贯=’beijing’ and b.班级名称like‘pu%’;
格式2:
Select s.学号,s.姓名,s.性别,b.班级名称,b.年级
From学生信息s inner join班级信息b
On s.班级编号= b.班级编号
Where s.籍贯=’beijing’ and b.班级名称like‘pu%’;
案例四:查询成绩大于40的学生个人情况(学号,姓名,性别,班级编号,成绩),并按照成绩降序排列。
学生信息表:
成绩表
格式1:
Select s.学号,姓名,性别,s.班级编号,c.成绩
From学生信息表s,成绩表c
Where s.学号=c.学号and c.成绩>40
order by c.成绩desc;
格式2:
Select s.学号,姓名,性别,s.班级编号,c.成绩
From学生信息表s inner join成绩表c
On s.学号=c.学号
Where c.成绩>40
order by c.成绩desc;
注意事项:如果查询的这个列,两个表中都有,那么在写的过程中,列的前面一定要加表名;如果查询的这个列,只属于一个表中的,那就可以直接写列名。
进行表连接的列:列名可以不一致,但是数据类型和值要保持一致
2.7.3 内连接练习
1、查询选修”c02”课程的学生的姓名和所在院系
Select s.sname,s.dept
From student s,sc c
Where s.sno=c.sno and c.cno=’c02’;
2、查询成绩80分以上学生的姓名、课程号和成绩,并按照成绩降序排列结果.
Select s.sname,c.cno,c.grade
From student s,sc c
Where s.sno=c.sno and c.grade>80
Order by c.grade desc;
3、查询所有修了VB课程的学生的修课情况,要求列出学生姓名和所在的系。
Select sname,sdept
From student inner join course
On student.sno=sc.sno inner join course
On sc.cno=course.cno
Where cname=’VB’;
4、查询信息系修了VB课程的学生的修课成绩,要求列出学生姓名、课程名和成绩。
Select sname,cname,grade
From student inner join sc
On student.sno=sc.sno inner join course
On sc.cno=course.cno
Where sdept=’信息系’ and cname=’VB’;
查询结果:
student表:
Sc表:
2.7.4外部连接
特征:至少会返回出一个表的所有内容
分类:左外部连接、右外部连接
左外部连接:对连接条件中的左边的表不加限制,返回左表的所有行
右外部连接:对连接条件中的右边的表不加限制,返回右表的所有行
在from之后,先写的表是左表,后写的表是右表
左外部连接的格式:
Select表名.列名
From表名1 left outer join表名2
On表名1.列名=表名2.列名
会返回左表的所有内容,如果在右表中没有匹配到,在对应位置上显示为null。
案例:查询出所有学生的学号,姓名,课程编号,成绩
学生信息表:
成绩表:
Select s.学号,姓名,c.课程编号,c.成绩
From学生信息表s left outer join成绩表c
On s.学号=c.学号;
查询结果:
右外部连接的格式:
Select表名.列名
From表名1 right outer join表名2
On表名1.列名=表名2.列名
会返回右表的所有内容,如果在左表中没有匹配到,在对应位置上显示为null。
案例:根据成绩表查询出学生所对应的个人信息
Select c.学号,课程编号,成绩,姓名
From学生信息表s right outer join成绩表c
On s.学号=c.学号
查询结果:
2.8子查询
子查询指的就是查询语句里面嵌套查询语句
格式:select列名from表名where列名in(select查询语句);
子查询的注意事项:
1、where后面的条件要什么,我的子查询就查什么
例:条件要学号,子查询就查学号
2、什么情况优先考虑子查询?
①题上给出的条件没有明确的提示结果
例:查询计算机系学生选修了哪些课程?问:计算机系学生有哪些?
②where后面要用到聚合函数当条件
聚合函数不能直接写,就可以借助查询语句把聚合函数的结果给查出来。
成绩大于平均值
Where grade>(select avg(grade) from sc)==>where grade>78
例子:
1.select列名from表名where列名in(select列名from表名where列名in(select列名from表名where条件));
2.select列名from表名where列名in(select列名from表名where条件));
2.8.1 嵌套子查询
嵌套子查询指的是子查询中还包含其他的子查询
案例:查询计算机系学生选修了哪些课程?
Select cno
From sc
Where sno in(
Select sno
From student
Where sdept=’计算机系’
);
查询结果:
案例:查询修了’c02’课程且成绩高于此课程的平均成绩的学生的学号和成绩。
Sc表:
Select sno,grade
From sc
Where cno=’c02’ and grade>(select avg(grade) from sc where cno=’c02’ );
查询结果:
2.8.2相关子查询(单值子查询)
相关子查询指的是子查询的结果有且只有一个
案例:查询和’ff’在同一个班的学生的信息(学号,姓名,籍贯,班级名称,年级)
学生信息表:
班级信息表:
Select学号,姓名,籍贯,班级名称,年级
From学生信息表s inner join班级信息b
On s.班级编号=b.班级编号
Where s.班级编号=(select班级编号from student where姓名=’ff’);
2.8.3 insert和select结合
这两者结合就是把查询的结果插入到某一张表中。
格式:
Insert into 表名 select查询语句
例子:向学生信息表中插入数据,籍贯是北京的
Insert into学生信息表select * from学生信息where籍贯=’北京’;
例子:根据student表查询出学员对应的信息,插入到stu表
Insert into stu select sno,sname,sdept from student
2.8.4 update和select结合
格式:
Update 表名
Set 列名
Where 列名 in(select语句);
例子:将计算机系全体学生的成绩加5分
Update sc
Set grade=grade+5
Where sno in(select sno from student where sdept=’计算机系’);
2.8.5 delete和select
Delete from 表名 where 列名 in(select查询语句);
例子:删除计算机系学生的信息
Delete from sc where sno in(select sno from student where sdept=’计算机系’);
2.8.6 嵌套子查询练习
student表:
Sc表:
1. 查询成绩为大于90分的学生的学号、姓名。
Select sno,sname
From student
Where sno in(select sno from sc where grade>90);
2. 查询数学系成绩80分以上的学生的学号、姓名。
Select sno,sname
From student
Where sdept=’数学系’ and sno in(select sno from sc where grade>80);
3. 查询选修了”数据库基础”课程的学生的学号、姓名。
Select sno,sname
From student
Where sno in(select sno from course where cname=’数据库基础’);
4. 查询修了”c02”课程且成绩高于此课程的平均成绩的学生的学号和成绩。
Select sno,grade
From sc
Where cno=’c02’ and grade>(select avg(grade) from sc where cno=’c02’);
5. 查询计算机系考试成绩最高的学生的姓名。
Select sname
From student
Where sdept=’计算机系’ and sno in(
select sno from sc where grade=(
select max(grade) from sc where sno in(
select sno from student where sdept=’计算机系’));
2.9视图
2.9.1 视图的定义
视图:指的是根据某个实际的表(实表)查询出来,生成的一个虚表。
2.9.2 创建视图
Create view视图的名字as select查询语句
案例:创建数据库中的表’班级信息’中所有记录的视图view_班级信息
Create view view_班级信息as select * from班级信息;
注意:
1.视图既然作为一张虚表存在,那么对实表的增删改查操作,视图同样成立。
2.视图既然是根据实表得到的,那么对视图的增删改操作,也会影响实表
2.9.3 修改视图
Alter view视图名字as select查询语句;
2.9.4 删除视图
Drop view 视图名;
Drop view 视图名1,视图名2;
2.9.5 视图插入数据
Insert into 视图的名字 values(值1,值2...);
2.9.6 修改视图数据
Update视图名字set列名=值 where条件;
案例:更改视图,把er年级的学生人数改为100
Update view_班级信息
Set人数=100
Where年级=’er’;
视图结果:
班级信息表结果:
2.9.7 删除视图数据
Delete from 视图名 where 条件;
2.9.8 查询视图数据
Select 列名 from 视图名;
查询视图中所有内容
Select * from view_班级信息;
2.10索引
2.10.1索引定义
索引:类似于书本中的目录。指的是在数据库表中的一个列或多个列的设置,帮助快速定位所查询的数据。
索引作用(优点):
①加快数据检索(定位)
②保证数据的唯一性(唯一约束)
③实现表与表之间的参照性完整(进行外键约束参照的列)
④在使用group by、order by子句进行查询时,利用索引可以减少排序和分组的时间。(根据某个列进行分组或者排序)
索引缺点:
①创建和维护索引需要耗费时间;
②耗费时间的数据随数据量的增加而增加;
③索引需要物理空间,每个索引要做占一定的物理空间;
④增加、删除、和修改数据时,要动态维护索引,造成数据的索引速度下降。
⑤索引可以提高查询速度,但会降低插入记录的速度。
2.10.2索引的分类
索引分为普通索引、唯一性索引、全文索引、单列索引、空间索引
普通索引:在创建普通索引时不附加任何条件。
唯一性索引:使用unique可以设置唯一性索引。
全文索引:使用fulltext可以设置全文索引。全文索引只能创建在char/varchar或text类型的字段上。只有MyISAM存储引擎支持。
单列索引:在表的单个字段上创建索引,只根据该字段索引。
多列索引:在表的多个字段上创建索引,该索引指向创建时的多个字段,可以通过几个字段查询。但是只有当查询条件使用了这些字段的第一个字段时,索引才会使用。
空间索引:使用SPATIAL参数可以设置空间索引,必须是MyISAM存储引擎,而且索引字段必须有非空约束。
2.10.3创建索引
①在创建表的时候指定索引字段
Create table name(id int,name varchar(12), index(id));
②在已经创建表上添加索引
Create [unique|fulltext|spatial] index idx_id(索引名) on 表名(id(属性名));
2.10.4删除索引
Drop index index_name on 表名;