文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

MySQL的知识点整理(学习笔记)

2023-09-09 13:20

关注

目录

一、认识数据库

1. 什么是数据库

2. 数据库类型

3. 常见的数据库管理系统

4. MySQL介绍

5. MySQL的安装&管理工具的安装

6. SQL概述

7. 在DOS系统中操作MySQL

8. MySql常用命令

二、操作数据库

1、使用navicat创建数据库

2、备份和还原数据库

3、MySQL数据类型

4、使用SQL语句创建表

5、 数据完整性

6、 表的管理

三、MySQL的数据操作

1.MySQL条件表达式和逻辑表达式

2.INSERT插入语句

3.DELETE/TRUNCATE删除语句

3.1 Delete语句

3.2 Truncate语句

3.3 三个删除的区别

4. UPDATE修改语句

5. 简单查询

6.CASCADE级联问题

6.1、问题阐述

6.2、解决办法

6.3、图形界面操作

6.4、SQL命令完成级联操作

四、 数据查询

一:查询的概述

1.1、Limit查询

1.2、Distinct去重

1.2、Where子句

1.3、查询结果排序

二:MySQL 内置函数

2.1、字符串函数

2.2、日期函数

2.3、数学函数

2.4、系统函数及其它

小结

五、 模糊查询与分组查询

案例用表

一:模糊查询

1.1、%通配符

1.2、_单个字符

1.3、转义字符 Escape

1.4、正则模式

1.5、Between运算符

1.6、IN运算符

二:聚合函数

三:分组查询

3.1、单列分组查询

3.2、多列分组查询

六、多表数据连接查询

一、必要性

二、多表连接查询

2.1、交叉连接

2.2、内连接

2.3、外连接

三、联合查询union

3.1、合并查询结果

3.2、Union 与 Join 的区别

四、子查询

4.1、基本子查询

4.2、高级子查询


1. 什么是数据库

从数据管理角度分析,数据库的发展可以分为三个阶段:

人工管理阶段 20世纪50年代中期之前

文件系统阶段 20世纪50年代中期到60年代中期

数据库系统阶段 20世纪60年代后期

先来认识几个概念:

数据和数据库:

数据(Data):是关于自然、社会现象和科学试验的定量或定性的记录,数据是所有计算机系统所要处理的对象。比如声音、图像,称为模拟数据。符号、文字,称为数字数据。

数据库(DataBase):在计算机体系内,简单来讲就是存储数据的“仓库”,是按照数据结构来组织、存储和管理数据的仓库。简称为DB。通常是一个或一组文件,保存了一些符合特定规格的数据,数据库软件称为数据库管理系统DataBase Management System(DBMS):数据库的运行管理系统。

数据库系统(DBS):数据库、数据库管理系统、应用系统、数据库管理员和用户构成。

实体和属性(数据表):

表(table)是一种结构化的文件,可用来存储特定类型的数据。

表都有特定的名称,同一个数据库中表名不能重复。

实体和属性是面向对象的概念,分别对应 “记录”和 “字段”

实体(行/记录):是客观存在并可以相互区别的事物,对应数据表中的一“记录”

属性(列/字段):是事物本身固有的性质,对应数据表中的“字段”或“列”

数据表与数据库

数据表就是记录的集合 。

数据库就是数据表、数据库关系和数据库关系对象的集合

2. 数据库类型

数据除了可以存放在数据库中,我们也可以将数据存储在文件中,但是在文件中读写数据速度相对较慢,所以现在我们使用关系型数据库管理系统(RDBMS)来存储和管理的大数据量。

数据库可以分为关系型数据库非关系型数据库

所谓的关系型数据库,是建立在关系模型基础上的数据库,借助于集合代数等数学概念和方法来处理数据库中的数据。

RDBMS即关系数据库管理系统(Relational Database Management System)的特点:

  1. · 数据以表格的形式出现

  2. · 每行为各种记录名称

  3. · 每列为记录名称所对应的数据域

  4. · 许多的行和列组成一张表

  5. · 若干的表组成database

关系型数据库:表与表之间存在对应关系。

非关系型数据库:表之间不存在关系,数据独立,随便存。

3. 常见的数据库管理系统

4. MySQL介绍

MySQL是最流行的关系型数据库管理系统,在WEB应用方面MySQL是最好的RDBMS(Relational Database Management System:关系数据库管理系统)应用软件之一。由瑞典MySQL AB公司开发,目前属于Oracle公司。MySQL是一种关联数据库管理系统,关联数据库将数据保存在不同的表中,而不是将所有数据放在一个大仓库内,这样就增加了速度并提高了灵活性。

5. MySQL的安装&管理工具的安装

MySQL数据库的安装,无脑下一步。参考安装步骤

管理工具选择使用:Navicat。

6. SQL概述

SQL(Structured Query Language),即结构化查询语言,是高级的非过程化编程语言。SQL是一种数据库查询和程序设计语言,用于存取数据以及查询、更新和管理关系型数据库系统。

SQL同时也是数据库文件格式的扩展名。

sql用来和数据库打交道,完成和数据的通信。SQL语言集数据查询(Data Query)、数据操纵(Data Manipulation)、数据定义(Data Definition)和数据控制(Data Control)功能于一体。

1986年10月,美国国家标准协会对SQL进行规范后,以此作为关系式数据库管理系统的标准语言。Sql成为一套国际标准,不过各种不同的数据库系统在其实践过程中都对SQL规范作了某些编改和扩充。所以,实际上不同数据库系统之间的SQL不能完全相互通用

7. 在DOS系统中操作MySQL

首先进入mysql安装目录下的bin目录下:

输入cmd,进入MySQL数据库: mysql -h localhost -uroot -p

-h 服务器(本地、指定服务器IP的)[本地localhost可以省略]

-u 用户名(我们用的是root用户:超级管理员)

-p 用户密码(不需要再它后面写密码,密码是回车之后写的)

 show databases;

显示当前所有数据库

 use mysql;(mysql指数据库名)

指定使用哪一个数据库

show tables;显示当前数据库内所有的表名

 desc users[表名];查看当前数据表的结构

 在数据库连接中,默认的数据库:

information_schema:提供了访问数据库元数据的方式。

mysql:是mysql的核心数据库。

performance_schema :主要用于收集数据库服务器性能参数。

test : mysql创建的测试库。

8. MySql常用命令

\1. 创建数据库

 create database [database_name];  

\2. 删除数据库

 drop database 数据库名;

\3. 查看已创建的数据库

 show databases;

\4. 使用一个数据库

 use 数据库名;

\5. 查看当前我们所使用的数据库

 select database();    -- 显示当前所使用的数据库

\6. 查看建库语句

 show create database 数据库名;   -- 查看这个数据库的建库语句;

\7. 对数据表的操作

 show create table 表名;   -- 查看这个表的建表语句 desc 表名;     -- 查看表结构

\8. 对表的操作

 # 创建表: create table 表名( •     字段名 类型(长度) [一堆约束……], •     …… );

l 启动服务

  1. 用命令行的方式启动 net start mysql57

  2. 打开windows+r,输入services.msc 找到MySQL的服务,右键启动。

1、使用navicat创建数据库

先建立连接:

创建数据库:

  

不需要的数据库,右键删除即可。

2、备份和还原数据库

选中要备份的数据库--->右键--->转储SQL文件--->结构和数据

 成功则可以生成一个sql文件。如果数据库中没有名字为ktv的数据库,就可以在数据库执行ktv.sql文件,可以直接生成该数据库,并带有数据。

3、MySQL数据类型

数值类型

字符类型

日期类型

 表示时间值的日期和时间类型为datetime、date、timestamp、time和year。

每个时间类型有一个有效值范围和一个"零"值,当指定不合法的MySQL不能表示的值时使用"零"值。

二进制类型

4、使用SQL语句创建表

-- 创建数据库CREATE database 2202_project;-- 改变当前使用的数据库  use 2202_project;-- 创建数据表   studentcreate table  student(sid int(10)  PRIMARY KEY auto_increment, -- 学生编号 类型 主键 自增sname varchar(20) unique , -- 学生姓名    唯一age int(10) not null,-- 年龄  非空classId int ,-- 所在班级号     需要加外键约束constraint fk_stu_class FOREIGN key(classId) REFERENCES class(classId));-- 此时因为班级表class不存在,所以外键约束添加错误。执行不成功。所以需要先创建班级表!# 班级表CREATE TABLE class(classId int PRIMARY key auto_increment,className varchar(10) not null,beginTime datetime not null,endTime datetime ,gradeId int not null);-- 删除数据表drop table student;

5、 数据完整性

数据完整性(Data Integrity)是指数据的精确性(Accuracy)和可靠性(Reliability)

数据完整性分为4类:

Ø 实体完整性(Entity Integrity)

实体完整性将行定义为特定表的唯一实体,它规定表的每一行记录在表中是唯一的。

实体完整性在表中的约束体现为:

约束含义描述
PRIMARY KEY主键约束主键是表中一列或多列数据,用来唯一标识一行数据。例如:学号主键必须不为空
AUTO_INCREMENT自增约束使列值自增,一般用来做主键使用。auto_increment表示第一行记录的列值为1,第二行的值为2。以后每次加1,不需要用户显式地添加数据
UNIQUE唯一约束可以用来确保在非主键列中不存在重复值,但列值可以是NULL(空)。

Ø 域完整性(Domain Integrity)

域完整性(Domain Integrity)是考虑如何限制向表中输入的值的范围

域完整性的约束:

Ø 引用完整性(Referential Integrity)

外键约束:是在插入或删除数据时,维护表间数据一致性的关系,是建立在主键的基础之上的

外键约束语法:

constraint 约束名称  foreign key(外键列--本表) references 关联表(关联表的主键) 

注意

  1. 本表外键列必须和关联表的关联列(主键)数据类型必须一致。

  2. 本表中外键列中录入的值必须在引用表中存在,否则不能录入

  3. 如果已经被引用过的数据,不能被删除

6、 表的管理

Ø 添加字段

-- 语法解析: -- alter  table  表名  add) column 列名 列类型  约束alter table employeeadd column mgr int  not null

Ø 删除字段

-- 删除字段alter table employeedrop column mgr;

Ø 删除外键约束

-- 删除外键约束alter table employeedrop foreign key fk_emp_dept;

Ø 添加外键约束

-- 添加外键约束alter table employeeadd CONSTRAINT fk_emp_dept foreign key(deptno) REFERENCES dept(deptno) 

Ø 修改字段

-- 修改字段alter table employeechange column job job2 varchar(10) default '职员'

1.MySQL条件表达式和逻辑表达式

表达式是指使用运算符将同类型的数据(如常量、变量、函数等)按一定的规则连接起来的,具有特定意义的语句。

MySQL中表达式包括:

这两种表达式结果只能为TRUE或FALSE。

SQL命令支持的逻辑运算符有 AndOrNot

And和Or运算符连接条件,Not否定条件。

当语句中使用了多个逻辑运算符时,运算时先求Not的值,然后求And的值,最后再求Or的值。

2.INSERT插入语句

INSERT 语句用来往数据库中添加数据,可以一次添加一行或者多行数据数据

语法:

INSERT  [INTO]  table_name [(COLUMN1 [,COLUMN2 [,......]])]VALUES (VALUE1 [,VALUE2 [,......]);
-- 插入单条语句insert into student(sname,age,sex,birthday) values('张三',18,'男','2000-03-05');-- 插入多条语句insert into student(sname,age,sex,birthday) values('刘备',18,'男','2000-03-05'),('关羽',18,'男','2000-03-05'),('张飞',18,'男','2000-03-05'),('赵云',18,'男','2000-03-05');

注意:

3.DELETE/TRUNCATE删除语句

3.1 Delete语句

delete 删除表中数据。

语法:delete from 表名 [where 字段名=?]

# 删除整张表delete from student;# 删除指定记录-- 删除编号为1的学生信息delete from student where sid=1; -- 删除姓名是jack并且是男性的学生信息delete from student where sname='jack' and sex='男';-- 删除所有班级是1和2的学生信息delete from student where classId in (1,2);

3.2 Truncate语句

truncate 清空整张表的数据,重置。

# 清空整张表truncate table student;

3.3 三个删除的区别

语句优点缺点
delete选择性地删除数据,可以返回行数。当删除整张表的数据时效率较低。删除自增列不会重新编码
truncate只能删除整张表的数据,但是效率高于使用DELETE语句。不能选择性删除。自增列会重新编码排序。
drop删除数据表、删除数据库不能操作数据。

4. UPDATE修改语句

UPDATE语句用于修改表数据。

语法:

UPDATE table_name SET COLUMN1={}, COLUMN2={}......WHERE<表达式> 

 案例:

-- 修改整张表中age的值为20 update student set age=20-- 修改编号为3的学生年龄为22update student set age=22 where sid=3-- 修改编号为5的学生 年龄为26,地址为北京update student set age=26 , addr='北京' where sid=5-- 修改 班级号是3 或者 名字是李四的人的性别为女。update student set sex='女' where sname='李四' or classid=3

5. 简单查询

-- 查询全表selct * from student-- 查询指定列select sname,age,birthday from student-- 查询 编号为6的学生的所有信息select * from student where sid =6-- 查询指定字段,并以  别名 为 字段名显示。select sname as 姓名,sex as 性别 , age 年龄 from student  -- as起别名,可以省略不写-- 查询年龄大于18的学生姓名和地址select sname,addr from studentwhere age>18  -- 查询年龄大于18的女孩子的姓名 和 班级编号selects sname,classid from studentwhere age>18 and sex='女'

6.CASCADE级联问题

6.1、问题阐述

当两个表存在主外键关联关系的时候,对主表中的主键信息进行修改、删除操作,外联表中存储与其相对应的外键信息也应该同步的修改。

比如:学生表和班级表有外键关联关系。

这时候删除某一个班级就会执行不成功,如下图所示:

 delete from classinfo where classid=1 

  

由于classinfo表被student表所引用,在删除或者修改时都会报错。因为违反了数据完整性中引用完整性和域完整性。

6.2、解决办法

第一种

可以在先删除或者更改外键信息的前提下,再删除或者修改该主键信息,就可以正常执行。

缺点

如果表的主键信息被多个表所引用,那么操作将是非常繁琐的,这个时候就不能通过这个方法来实现数据的完整性。

第二种:

MySQL提供了一种叫级联(CASCADE)的方式,当主键的信息删除或者修改时,引用的外键信息就会对应的删除或者修改。

6.3、图形界面操作

Navicat中选中表,右键----->设计表----->

删除时更新时的下拉框中选择CASCADE,然后保存即可。

6.4、SQL命令完成级联操作

如果要操作的表在之前创建的时候,已经创建好了外键约束,

则需要先删除之前的外键约束然后再添加带级联操作的外键约束

# 先删除旧有外键约束alter table studentdrop foreign key fk_约束名字;# 重新添加外键约束alter table studentadd constraint fk_约束名字 foreign key(classId) references Classinfo(classId)ON DELETE CASCADE#打开级联删除ON UPDATE CASCADE;  #打开级联更新

一:查询的概述

对数据表中现有数据的筛选,按照要求把符合查询的条件的记录的指定的字段值提取出来,形成一个新的表,把结果呈现给用户。

查询的语法结构:

SELECT    FROM 
[WHERE <表达式>] [ORDER BY < Column1, Column2……>[ASC或DESC]]

 示例:

-- 语法SELECT  姓名 , 年龄 , 工作地点  FROM  studentInfo WHERE  工作地点 = '上海';-- 案例:USE SchoolDB; -- 选择要使用的数据库SELECT * FROM StudentInfo; -- 查询表中全部数据

MySQL对大小写不敏感,包括关键字在内。

1.1、Limit查询

使用LIMIT限定查询返回行: LIMIT 子句用于限制SELECT 语句返回指定的记录数。

语法及案例:

# 语法  :  从a开始,查询b条select * from student where limit a,b
 select * from book limit 3
 select * from book limit 1,3

1.2、Distinct去重

去除重复行:对返回结果中重复的值进行去重。

 select DISTINCT author from book

1.2、Where子句

单条件查询

-- 要求查询年龄在20岁以上的学生信息 。SELECT  *  FROM  StudentInfo  WHERE  Age > 20; 

多条件复合查询

 -- 查询出年龄为20岁的女生信息 SELECT * FROM StudentInfo WHERE Age=20 AND Gender ='女'; ​ ​ -- 查询出家不在郑州的年龄或者年龄不到20岁的学生信息 SELECT * FROM StudentInfo WHERE Age<20 OR City <> '郑州'; ​ ​ -- 查询出不是1班的学生信息 SELECT * FROM StudentInfo WHERE NOT ClassID = 1;

1.3、查询结果排序

Ø 单列排序

单列排序是按照某一列升序或降序排列,即ORDER BY子句后面只有一个字段。

示例:

 -- 1.按照学生的年龄以升序进行排列查询。 SELECT  *  FROM  StudentInfo  ORDER  BY  Age  ASC; ​ -- 2.按照学生的年龄以降序进行排列查询。 SELECT * FROM StudentInfo ORDER BY Age DESC;

Ø 多列排序

示例:

 -- 查询所有学员的信息。按照 年龄降序进行排列和 出生日期的升序进行排列 。 SELECT * FROM StudentInfo ORDER BY Age DESC,Birthday ASC; 

二:MySQL 内置函数

2.1、字符串函数

函数名示例函数功能
Concatconcat(s1,s2....sn) concat(‘hello’,‘AAA’)返回 helloAAA把传入的参数连接成一个字符串。 如果有值为null,则最终返回null。 忽略null值则使用concat_ws函数。
Lengthlength('hello world')结果为11 length('加油中国')结果返回为12返回任何数据类型的字节数
Char_lengthChar_length('中国') 返回为2返回字符个数
Upperupper('abcd')返回为ABCD将字符串转为大写
LtrimLtrim(‘ abc’)返回为’abc’去除字符串左边的空格
RtrimRtrim(‘abc ’)返回为’abc’去除字符串右边的空格
ReplaceReplace('abcccd','c','x') 返回为’abxxxd’将abcccd中c替换为x
LocateLocate('a','helloaaa') 返回 6返回子串 a 在字符串 helloaaa 第一个出现的位置,不存在则返回 0
Substringsubstring('Johnson',5,3) 返回为‘son’从第5个位置开始截取长度为3的字符串

案例:

 --   CONCAT把传入的参数连接成一个字符串 select CONCAT('hello','world') as word select CONCAT(name,':',tel) 联系方式 from student -- length select name,LENGTH(name) from student -- replace update student set name = REPLACE(name,'林','Lin'); -- locate select LOCATE('a','peace and love') -- Substring select SUBSTR('hello world' ,2,3)

2.2、日期函数

函数名示例函数功能
Nownow()返回系统当前时间返回系统当前时间
TIMESTAMPDIFF语法: TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2) 例: SELECT TIMESTAMPDIFF (MONTH,'2009-09-01','2009-10-01'); 返回 1返回日期或日期时间表达式之间的整数差 unit可以是: MICROSECOND (microseconds), SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR.
DateAddDATE_ADD('1998-01-02', interval 1 YEAR) 返回 1999-01-02向日期指定部分添加数字,其中: YEAR表示是年, month表示月, day表是日 Interval--时间间隔
Dayofweekdayofweek('2008-08-08‘)返回 6 dayofweek:(1 = Sunday, 2 = Monday, ..., 7 = Saturday)返回一周中的位置
YEARWEEK(date[,mode])select yearweek('2008-8-8'); 返回200831 mode:0-7获取年份和周数
DateDiffselect datediff('2008-08-08', '2008-08-01'); 结果返回为 7两个日期相减 date1 - date2,返回天数
Datedate('2008-09-10 07:15:30'); 返回 2008-09-10以字符串形式返回某个日期部分。

案例:

 select date('2008-09-10 07:15:30');        -- 日期  2008-09-10* ​ select time('2008-09-10 07:15:30');        -- 07:15:30.123456* ​ select year('2008-09-10 07:15:30');        -- 年   2008* ​ select quarter('2008-09-10 07:15:30');     -- 季度 3* ​ select month('2008-09-10 07:15:30');       -- 月   9* ​ select week('2008-09-10 07:15:30');        -- 一年中的第几周 36* ​ select day('2008-09-10 07:15:30');         -- 日    10 ​ select hour('2008-09-10 07:15:30');        -- 小时  7 ​ select minute('2008-09-10 07:15:30');      -- 分钟  15 ​ select second('2008-09-10 07:15:30');      -- 秒    30 -- 当前时间 select now() --  返回两个日期之间的时间               生日  和当前时间 做对比,得出年龄 update student set age=TIMESTAMPDIFF(year,birthDay,NOW()) -- date_Add 向指定日期添加 一段时间     year是年   month 是月  day是日 INTERVAL  表示时间差 select DATE_ADD('1999-08-02',INTERVAL 10 MONTH) -- dayofweek     返回一周中的位置    select DAYOFWEEK(NOW()) -- dateDiff  两个日期相减,返回天数 select DATEDIFF(NOW(),birthday) from student

2.3、数学函数

函数名示例函数功能
AbsAbs(-1)返回为”1”求绝对值
CeilingCeiling(24.1)结果返回25大于24.1的最小整数
FloorFloor(24.1)结果返回为24小于24.1的最大整数
PowerPower(2,3)结果返回为“8”计算2的3次方
RoundRound(68.32,1)结果返回为68.30返回一个数字,舍入到指定的长度或精度
SignSign(123)结果返回为1返回数值的符号,正负零分别返回1, -1, 0
SqrtSqrt(16)结果返回为“4”开平方
PIPI() 返回3.141593圆周率
Rand([x])Rand()/Rand(x)返回一个随机浮点值
 -- 绝对值 select ABS(-4) --  返回符号   select SIGN(-6) -- 产生随机数 select RAND()

2.4、系统函数及其它

函数名示例函数功能
Convertconvert(1234,char(4)) 结果返回为”1234”数据类型转换函数,将1234数字类型转换为char类型
VERSIONVersion()返回数据库的版本号
LAST_INSERT_IDLAST_INSERT_ID()返回最后生成的AUTO_INCREMENT值
ifnull(expr,value)select ifnull(city,'未知') from studentInfo如果表达式的值非空,则返回表达式的值,否则返回value
PASSWORD/MD5PASSWORD(str)/MD5(str)对数据加密
select user,host from mysql.user 返回计算机的名字返回当前用户登录的计算机名字
SELECT Current_User 返回当前用户的登录名返回当前用户的名字
 --  查看版本号 select VERSION() ​ -- 查看当前用户登录的计算机名字 select user,host from mysql.user  

小结

学生信息表(StudentInfo表)

 成绩表(Exam表)

一:模糊查询

模糊查询提取的数据不一定是确切的。

它允许用户输入的查询条件也是模糊的、大概的、不特别明确的。

regexp 使用正则模式:

通配符说明
%代表任意长度的字符串
_下划线。代表一个字符
escape转义字符

1.1、%通配符

 -- 查询数据库表中姓孙的学生 SELECT * FROM studentinfo WHERE sname LIKE  '孙%'; ​ -- 查询包含字符"张"的学生信息,需要中间匹配 SELECT * FROM studentinfo WHERE sname LIKE '%张%';  ​ -- 查询以字符"乔"结尾的学生信息,需要结尾匹配 SELECT * FROM studentinfo WHERE sname LIKE '%乔';

1.2、_单个字符

 -- 查询"黄_"的学生 SELECT * FROM studentinfo WHERE sname LIKE '黄_'; ​ -- 查询所有Email的第二个字符为“u”的学生信息 SELECT * FROM StudentInfo WHERE Email LIKE '_U%';

1.3、转义字符 Escape

 -- 查询“李_"的学生  ( 比较特殊的名字叫李_ )  select * from student where sname like  '李/_' ESCAPE '/';

1.4、正则模式

正则模式REGEXP操作符

符号含义
.匹配任意单个的字符。
[ ]匹配在[]内的任意一个字符
[^]匹配不在[]内的任意一个字符
*匹配零个或多个在它前面的字符
^匹配文本的开始字符
$匹配文本的结尾字符
+匹配+前面的字符1次或多次
{n}匹配前面的字符至少n次

示例:

 -- 查询出所有的身份证号以14开头,第三位必须为0或6 的学生信息 select * from studentinfo where idcard REGEXP '^14[0|6]' ​ -- 查询学生电话号 是 13开头的人的信息 SELECT * FROM studentinfo WHERE  phone  regexp  '^[1][3]';

常见的匹配条件:

查询条件匹配结果描述
REGEXP '^4'4%以4开头的
REGEXP '4$'%4以4结尾的
REGEXP '[0-2]'0 1 2从0到2
REGEXP 'b{2}$'%bb以两个b结尾
REGEXP 'b{2}'bb,bbbb,bbbbbbb包含两个b

1.5、Between运算符

示例:

 -- 在student表中查询出年龄在18~20之间的学生信息  SELECT * FROM studentinfo  WHERE Age BETWEEN 18 AND 20;

1.6、IN运算符

示例:

 -- 查询出学生家在安徽或者浙江的学生信息 SELECT * FROM StudentInfo WHERE  Province  IN('安徽','浙江') ;

二:聚合函数

获取某些列的最大值、最小值、平均值等统计分析信息,有时候还需要计算出究竟查询到多少行数据项。

SUM函数用于对数据求和,返回选取结果集中某列所有值的总和,可以计算单列或多列的总和。

聚合函数,与普通列一起进行查询:

 -- 示例:查询出Exam表中学号(stuid)为2023002的所有科目的总成绩 select sum(exam) as 总成绩 ,stuid 学号 from exam where stuid=2023002
 # 分别查询Exam表中科目编号(SubjectId)为1的最高成绩和最低成绩 SELECT MAX(Exam) AS '最高成绩', MIN(Exam) AS  '最低成绩' FROM Exam WHERE SubjectId=1 ;
# 要求查询出所有及格学员的平均分SELECT AVG(Exam) FROM Exam WHERE Exam>=60 ;

COUNT函数返回符合条件的查询结果中非空值的计数

# 查询出在校学生的总人数SELECT COUNT(*) AS 学生人数 FROM StudentInfo;

三:分组查询

3.1、单列分组查询

使用Group By进行数据分组查询。

示例:

-- 查询每个科目的平均分SELECT  SubjectId,AVG(Exam)  as  平均分  FROM EXAM GROUP BY SubjectId;
SELECT ClassID  AS  '班级编号', COUNT(*)  AS  '班级人数' FROM StudentInfo GROUP BY ClassID;
SELECT province  AS  '所在省份',MIN(Age)  AS  '最小年龄' FROM StudentInfo GROUP BY  provinceORDER BY MIN(Age) ASC ;  # DESC 降序

3.2、多列分组查询

SELECT ClassId  as  '班级编号',Gender  as  '性别',COUNT(StuId) as '人数'FROM  StudentInfo  GROUP BY ClassID,Gender ;

Having子句-分组查询的条件筛选

SELECT  province  AS  '所在省份',MIN(Age)  AS '最小年龄' FROM StudentInfo GROUP BY provinceHAVING COUNT(StuID)>2 ORDER BY MIN(Age) ASC ; 

SELECT SubjectId,AVG(Exam)  as 平均分,COUNT(*)  AS  '及格人数' FROM EXAM WHERE EXAM>=60 GROUP BY SubjectIdHAVING AVG(Exam)>80;

一、必要性

在实际开发过程中很多时候需要同时使用多个表中的数据。

多表连接查询实际上是通过各个表之间共同列的关联性来查询数据的,它是关系数据库查询最主要的特征。

常用的连接有:内连接、外连接、交叉连接(笛卡尔乘积连接)。

内连接和外连接一样都是最常用的连接查询,它根据表中共同的的列进行匹配,特别是两个表存在主外键关系时,通常会使用到内连接查询。

外连接是至少返回一个表中的所有记录,根据匹配条件有选择性地返回另一张表的记录。外连接的体现形式为两种:左外链接、右外连接。

交叉联接返回左表中的所有行,左表中的每一行与右表中的所有行再一一组合,相当于两个表“相乘”。这种情况在数据库中,叫做笛卡尔乘积。

二、多表连接查询

2.1、交叉连接

 select A.*,B.* from student A,classinfo B

执行该语句发现并没有报错,但是查询所得到的结果并不是所需要的。

这是因为在查询过程中没有使用任何的匹配条件,就会将A表中的数据每取出一行就和B表中的数据进行组合。

这种情况在数据库中叫做:笛卡尔乘积,也就是交叉连接。

笛卡尔乘积返回的结果是存在大量冗余的,多数情况下,查出来的很多内容都是无用的,所以尽量不要使用!

2.2、内连接

1)Where子句多表查询

使用where子句实现多表连接查询(隐性连接)。

 select s.sname 姓名,s.age 年龄,s.sex 性别,s.addr 籍贯,c.className 班级 from student s,classinfo c where s.classid = c.classid

2)Join...On

使用join. ..on实现多表连接查询(显性连接) 1、内连接(等值连接)

inner. . . join:返回的结果集是两个表中所有想匹配的数据,抛弃掉不匹配的数据。

 # 语法 select ... from表1  [inner] join 表2  on条件表达式 ​ # 案例: select s.sname,s.age,c.classname from student s JOIN classinfo c on s.classid=c.classid

2、自连接

自连接是指表与其自身连接查询信息。在有些情况下,需要表自身连接来查询信息。

比如:在员工表emp中查询员工信息以及对应的领导名称。

 select a.ename 员工姓名,a.job 员工职位,b.ename 所属领导 from employee a JOIN employee b on a.mgr=b.empno

2.3、外连接

在外连接中两个表有主从之分。

外连接是以A表为主表,B表为副表, 查出A表中的所有信息,以及在B表中对应的相关信息,如果B表中没有相关信息,则返回null。

外连接可以分为2种:左外链接、右外连接

外连接的语法只需要把内连接的关键字INNER改为OUTER 即可,同时OUTER可以省略

1)左外链接

 -- 查询所有班级对应的学生 ​ select c.classname, s.sname from classinfo c left JOIN student s on c.classid=s.classid    -- > 显示结果中,有些班级没有学生。所以就是null表示 # 如果使用内连接: select c.classname,s.sname  from classinfo c,student s where c.classid=s.classid   -- >内连接只显示能够匹配到的值,没有学生的班级不会显示。

2)右外连接

右外联接是左外联接的反向联接,将返回右表的所有行,如果右表的某行在左表中没有匹配行,则将左表返回空值

返回包括右表中的所有记录和左表中关联字段相等的记录。

 select c.classname, s.sname from classinfo c right JOIN student s on c.classid=s.classid # 返回右表中在左表没有匹配到内容的,返回null,比如没有班级的学生

三、联合查询union

3.1、合并查询结果

UNION用于合并两个或多个 SELECT 语句的结果集,并消去表中任何重复行。

UNION内部的 SELECT 语句必须拥有相同数量的列,列也必须拥有相似的数据类型。 每条 SELECT 语句中的列的顺序必须相同.

允许重复的值,请使用 UNION ALL

示例:

 select sname from student union select classname from classinfo;

3.2、Union 与 Join 的区别

UnionJoin子句不同,因为union 总是垂直组合结果集,而join水平附加输出。

我们可以通过以下图例来理解它:

四、子查询

子查询是一个嵌套在SELECT、INSERT、UPDATE 或 DELETE语句中的查询。

嵌套在其它查询中的查询称之为子查询或内部查询。

包含子查询的查询称之为主查询或外部查询

4.1、基本子查询

分为三类:

通过IN、ANY、ALL修改的比较运算符引入的列表上操作的子查询

通过比较运算符引入且必须返回单个值的子查询

通过EXISTS引入的存在测试的子查询

案例:

 -- 查询和张三年龄相同的人的信息 select * from student  where age=(select age from student where sname='张三')

问题:查询张三的同班同学名单

用子查询实现 :

编写复杂的子查询(尤其是多层嵌套的子查询),最简单的方法是分解查询。

从最内层的子查询开始分解,将嵌套的SQL语句拆分为独立的SQL语句分别执行;

当确认结果无误后,再重新组合在一个语句中。

-- 同班同学select s.sname,c.className from student sjoin classinfo con s.classid=c.classIdwhere s.classId = (select classid from student where sname='张三')

4.2、高级子查询

1)使用IN、NOT IN的子查询。

IN后面的子查询允许返回多行记录,用于从一个范围来限制主查询的条件

-- 查询存在学生年龄大于20的班级select * from classinfo where classId in(select classid from student where age >20)-- 查询不存在学生年龄大于20的班级select * from classinfo where classId not in(select class_id from student where age >20)

2)使用ALL的子查询

通过比较运算符将一个表达式的值或列值与子查询返回的一列值中的每一行进行比较,只要有一次比较 的结果为FALSE,则ALL测试返回FALSE。

-- 查询 成绩比1002班所有成绩 都好的学生信息select * from student where score >ALL(select score from student where class_id =(select classId from classinfo where className='1002班'))

3)使用any/some

按照比较运算符、表达式或字段对子查询的结果每一行进行一次计算和比较。

只要有一次满足条件,那 么 ANY 的结果就是真;

当子查询每行结果与ANY前面的表达式或字段比较结果全为假时,则结果为假.

-- 查询 成绩只要比1002班中某位学生成绩好的 学生信息select * from student where score >ANY(select score from student where class_id =(select classId from classinfo where className='1002班'))ORDER BY score desc

4)使用EXISTS的子查询

使用EXISTS的子查询 :EXISTS代表存在量词“” 。

带有EXISTS谓词的子查询不返回任何实际数据 只产生逻 辑真值“true”或逻辑假值“false”。

-- 查询学生信息,条件是学生班级不是1002班中的select * from student swhere s.class_id not in (select classId from classinfo where className='1002班')# 或者select * from student swhere not EXISTS(select c.classId from classinfo c where s.class_id=c.classId andclassName='1002班')

👆最后一个属于相关子查询

不相关子查询: 内部查询的执行独立于外部查询,内部查询仅执行一次,执行完毕后将结果作为外部查询的条件使用

相关子查询: 内部查询的执行依赖于外部查询的数据,外部查询每执行一次,内部查询也会执行一次。每一次都是外部查询先执行,取出外部查询表中的一个元组,将当前元组中的数据传递给内部查询,然后执行内部查询。根据内部查询执行的结果,判断当前元组是否满足外部查询中的where条件,若满足则当前元组是符合要求的记录,否则不符合要求。然后,外部查询继续取出下一个元组数据,执行上述的操作,直到全部元组均被处理完毕。

来源地址:https://blog.csdn.net/woshishq1210/article/details/131061123

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯