【MySQL】学习总结基础篇

weichen199

于 2022-04-03 12:28:35 发布

阅读量835

点赞数 1

CC 4.0 BY-SA版权

分类专栏： MySQL 文章标签： mysql 数据库 sql

本文链接：https://blog.youkuaiyun.com/weichen199/article/details/123935229

MySQL 专栏收录该内容

2 篇文章

订阅专栏

这篇博客详细介绍了MySQL的基础知识，包括关系型数据库和非关系型数据库的区别，以及SQL指令分类，如DQL、DML、DDL等。重点讲述了数据库指令、数据表操作，如创建、查看和删除，并探讨了查询语句、修改和删除语句。内容涵盖了表的设计、数据表的创建、查看、删除，以及DQL的聚合查询、分组操作和联合查询，为初学者提供了全面的学习指南。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、认识数据库

1.数据库大体分为两大类：关系型和非关系型数据库

关系型数据库（Relational Database Management System：RDBMS）
是指采用了关系模型来组织数据的数据库。关系模型简单来说就是二维表格模型，而一个关系型数据库就是有二维表机器之间的联系所组成的一个数据组织。关系型数据库都是基于标准的SQL，只是内部一些实现有区别。
常用的关系型数据库有以下几个：
1）Oracle：是甲骨文公司的一款关系数据库管理系统，具有系统可移植性好，功能强，适合各种大、中、小微机环静。适合大型项目，适用于做复杂的业务逻辑，如ERP 、OA等企业信息系统。收费。
2）MySQL：由瑞典MySQL AB公司开发，属于Oracle旗下产品，在WEB应用方面，MySQL是最好的RDBMS软件之一。优点：开放源码、体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都使用MySQL作为网站的数据库。
3）SQL Server：是微软的产品，具有使用方便、可伸缩性好、相关软件集成程度高、可跨越从笔记本电脑到大型多处理器的服务器等多平台使用。适用于中、大型项目。收费。
非关系型数据库（Not Only SQL：NoSQL）
数据之间没有关系，不规定基于SQL实现。现在更多是指NoSQL数据库，如：
1）基于键值对（Key-Value）：如 Memcached（分布式的高速缓存系统）、redis（Remote Dictionary Server 远程字典服务）
2）基于文档型：如 MongoDB（由C++编写）
3）基于列族：如 Hbase
4）基于图形：如 Neo4j

关系型和非关系型数据库的区别

	关系型数据库	非关系型数据库
使用SQL	是	不强制要求，一般补基于SQL实现
事务支持	支持	不支持
复杂操作	支持	不支持
海量读写操作	效率低	效率高
基本结构	基于表和列，结构固定	灵活性较较高
使用场景	业务方面的OLTP系统	用于数据的缓存、基于统计分析的OLAP系统

注：OLTP（On-Line Transaction Processing）是指联机事务处理，OLAP（On-Line Analytical Processing）是指联机分析处理。

二、使用指令

1.标题SQL中的指令分类

SQL中的指令分为以下5类：
1.1 DQL（数据库查询语言）
数据查询语句DQL基本结构是由 SELECT子句、FROM子句、WHERE子句组成的查询块，简称 DQL 即 Data Query Language。
代表关键字：select
1.2 DML（数据库操作语言）
用户通过它来实现数据库的基本操作。简称 DML 即 Date Manipulation Language。
代表关键字：insert，delete，update
1.3 DDL（数据库定义语言）
数据库定义语言DDL用来创建数据库中的各种对象，创建、删除、修改表的结构，比如表、试图、索引等。简称 DDL 即 Data Definition Language。与 DML 相比，DML 是修改数据库表中的数据，而 DDL 是修改表的结构。
代表关键字：create，drop，alter
1.4 TCL（事务控制语言）
TCL经常被用于快速原型开发、脚本编程、GUI和测试等方面。简称 TCL 即 Trasactional Control Language。
代表关键字：commint，rollback
1.5 DCL（数据控制语言）
用于授予或回收访问数据库的某种特权，并且控制数据库事务发生的时间和效果，对数据库进行实时监视等。简称 DCL 即 Data Control Language。
代表关键字：grant，revoke
以上主要学习DQL、DML、DDL的相关操作

2.数据库指令

展示数据库（以我数据库为例）：可以直观看到都有哪些数据库，数据库里面都是表

show databases；

在这里插入图片描述
提示：MySQL不区分大小写，并且语句结束后要加分号。

创建数据库：创建一个数据库后就可以在里面进行表的制作

create database 数据库名称；

在这里插入图片描述

选择数据库：只有选择数据库后才能进行表的创建和修改等操作

use 数据库名称；

在这里插入图片描述

删除数据库：删除数据库后，里面的表和数据就都没了，且无法撤销无法找回

drop database 数据库名称；

在这里插入图片描述

3.数据表指令

在对数据表进行操作之前，我们先认识数据表：
表的每一行是一个“记录”
每一列是一个“字段”
表头指的是表的第一行

3.1 创建表

创建表

create tabel 表名(列名a 类型 , 列名b 类型 , ..... ); 
-- 常用的类型有:int;doubel(M,D) M为总共的位数,D为小数的位数;varchar(N) N为最多N个字符;datetime...

在这里插入图片描述
补充: 类型后可以添加字段注释 : comment '字段注释 ’ ,
如何查看字段注释呢 ? 答案: show full columns from 表名;
表也可以加注释, 由于用得少, 这里不过多介绍

字段的约束

not null : 表示某字段(列)不能储存null值, 若尝试插入null, 系统报错
```
create table test(id int not null);
desc test;
```

在这里插入图片描述

unique : 表示数据唯一 , 如果尝试插入重复的值, 就会报错
```
create table test(id int unique);
```

在这里插入图片描述

default : 这这个字段约定一个默认值, 若在插入记录时在有默认值的字段中输入default, 则输出对应的默认值
```
create table test(id int , name varchar(20) default '匿名');
-- default'默认值',默认值可修改
```
primary key : 主键约束, 相当于数据的唯一身份标识, 类似于身份证号码. primary key 只在一整表中只给一个字段使用, 并且不能输入为空(null), 也不能重复输入.
```
create table score (id int primary key, math double(3,1));
-- 给id字段上了一个主键
```
补充: 自增主键 : primary key auto_increment , 设置好自增主键后, 插入记录时可以不用手动输入主键的内容, 主键会按顺序自动输入1,2,3,4,5…如果主动设置顺序, 则可在insert语句中输入你想开始的数字, 剩下的记录会顺着这个数字继续排下去…
```
create table score (id int primary key auto_increment, math double(3,1)) ;
-- 给id字段上了一个自增主键
```
效果如下:

foreign key : 外键约束, 外键用于关联其他表的主键或唯一键

案例:

1)创建一个班级表class, id为主键

2)创建学生表student,一个学生对应一个班级, 一个班级对应多个学生. 使用id为主键, class_id为外键, 关联班级表id.

create table class (classid int primary key , name varchar(20)) ;

create table student (id int primary key, 
                      name varchar(20),
                      class_id int ,
                     foreign key(class_id) references class(classid) ) ;
-- foreign key后面的class_id为子表(student表)里的字段(列),references后面的class表示父表,classid表示父表里的字段(列)

在这里插入图片描述

3.2 表的设计

三大范式 :

一对一
一对多
多对多

案例:

创建学生表

drop table if exists studnet;
create table student(id int primary key auto_increment, name varchar(20));

创建课程表

-- 创建课程表
drop table if exists coures;
create table course(id int primary key auto_increment, name varchar(20));

创建学生课程中间表, 考试成绩表

-- 创建学生课程中间表, 考试成绩表
drop table if exists score;
create table score(
		id int primary key auto_increment,
		score decimal(3,1),
		student_id int,
		course_id int ,
		foreign key (student_id) references student(id),
		foreign key (course_id) references course(id))

3.3 查看表

show tables;

可以看到你当前创建了哪些表, 注意, 进行表操作之前要选中数据库。

3.4 查看表的结构

可以看到表中各列的属性，包括名称，类型，是否为空，是否为主键等等…

desc 表名；

在这里插入图片描述

3.5 删除表

drop table 表名；

注意：删除之后的表及里面的数据不能恢复。

4. 查找语句

查找语句是SQL中最核心最复杂的操作

最基础的查找：全列查找

select * from 表名 ；

在这里插入图片描述
全列查找是把一个表中所有内容都查询（打印）出来。其中的 “ * ”叫做通配符，可以表示任何字符，在这里表示这张表的所有列。值得注意的是：select 查询操作不会影响到服务器上硬盘里报错的数据，查询结果的表只是一张临时表，随着打印进行完毕，占用的内存就释放了。

指定列查询

select 列名1， 列名2, .... from 表名；

顾名思义，指定列查询就是查询指定表里的列，只查询自己关注的列。

指定查询字段为表达式

select name ， score + 10 from student ；

意思就是查询 student 表中的 name 列和 score 列，并且 score 整个列里的数据要加上10，最后将 name，score+10 这两个列打印出来。

select name, chinese + math + english from score ；

意思就是查询 score 表中的 name, chinese，math，english这四列，再将chinese，math，english这三列里面的数据算数相加称为一列，名为 chinese + math + english ，最后打印name，chinese + math + english 这三列。
注意：这两次语句执行结果不会对数据作改变，打印出来的表也是临时表，但是要注意，在某些意外情况下，临时表中的数据类型不一定和原始表的数据类型完全一致，这时，类型会自适应，来保证计算结果是正确的。

查询字段指定别名: 相当于给临时表的列指定一个新的列名, 通过指定别名的方式, 避免临时表列名混乱.

select name, chinese + math + english as total_score from exam_result ;
-- 其中 as 可以省略
select name , (chinese + math + english) total from exam_result;
-- 这样写也可以

在这里插入图片描述

查询结果去重
```
select distinct math from exam_result  ;
```
从表中查询math列, 打印math列出来后没有重复的值.

如果去重是有多列, 就认为多个列的值都相同时才视为重复. 如下:
```
select distinct math, chinese from exam_result ;
```
结果如下:

排序: order by 需要排序的列名

select name, math from exam_result order by math desc ;
-- asc升序 desc降序

结果如下:
在这里插入图片描述
若记录中带有空值NULL, 则认为其是最小的数字.

也可以按照表达式结果来排, 如下:

select name, chinese+math+english from exam_result order by chinese+math+english desc ;

也可以通过别名来排序, 如下:

select name, chinese+math+english as total from exam_result order by total desc ;

也可以进行多列排序, 先排第一列, 若第一列与第二列相同, 再按二列排序, 如下:

select name ,chinese, math from exam_result order by chinese desc, math asc ;
-- chinese列降序排列,若两个记录值相等, 则按照math列升序排列.

结果如下:
$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RbE5yfmV-1648958317548)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20220319234222532.png)]$

条件查询:
```
select 列名 from 表名 where 条件 ;
```
其中, 条件查询中的条件有很多种, 有比较运算符, 逻辑运算符, 范围查询等

先介绍比较运算符: > < >= <= = ,其中 = 表示相等, 而不是赋值操作.

注意: 1. NULL = NULL结果还是NULL, 系统会视为假, 还有NULL和0没有关系, NULL与其他值计算结果还是NULL.
1. <=> 也表示用来比较是否相等, 用法与 = 基本一致, 只是用<=> 来比较两个NULL, 结果会输出为真.

其次介绍逻辑运算符: and / or
在这里插入图片描述
注意: and 的优先级比 or 高.

最后介绍范围查询: between and / in( , ) / like’ ’

select * from exam_result where math between 90 and 100 ;

上面意思是查询并打印数学分数在 90分到 100分之间( 包括90,100分 )的记录.

select * from exam_result where math in (99,98,97) ;

上面意思是查询并打印数学分数是 99, 98 ,97分的记录. 次查询也可以通过or来实现

select * from exam_result where math = 99 or math = 98 or math = 97 ;

select * from exam_result where name like ' j% ' ;

上面意思是查询name列首字母为 j 的记录, ’ % ’ 表示可以代表任意个字符, ’ _ ’ 表示只能代表一个字符 .

例如: 要查询 "rose ", 用 % 则可以输入 'r% ’ , 用 _ 则要输入 ‘r___’ .

aaa李aaa , aaaa李类似这种 ‘孙%’ 就匹配不到

aaa李aaa, aaaa李类似这种 ‘%孙%’ 就可以匹配到

最后补充一点: 查询中的NULL
在这里插入图片描述
目前表里有NULL空值, 若要查询空值, 可使用where name < = > null 来查询.

select * from exam_result where math <=> null ;

结果如下:
在这里插入图片描述
若直接使用 = , 则会显示空数据:

这是因为, math = null , 结果还是null, 会被系统视为false.

分页查询: 可以约定好一页中最多显示多少个结果, 假设一页显示20条, 第一页就显示1-20条, 第二页就显示21-40…

select * from exam_result limit 3 ; 
-- 一页显示3条记录

limit …offset… 用法

select * from exam_result limit 3 offset 0; 
-- 表示一页有3条记录, 并且记录从下标0开始, 默认下标从0开始.

在这里插入图片描述

select * from exam_result limit 3 offset 3 ;
-- 一页有3条记录, 从下标3开始

在这里插入图片描述
limit… , …用法

select * from exam_result limit 1,1 ;
-- 第一个1是指从下标1开始(默认0开始), 第二个1是指一页只显示1条记录

5.修改语句

update修改记录

update 表名 set 列名 = 修改后的值,列名 = 修改后的值...  where 条件 ..;

where 就是指对那些记录进行修改. 若将where去掉, 就是修改所有记录. 并且除了where 外, order by 和 limit 也可以使用其中, 值得注意的是: update语句会修改数据库上的原始数据.

update exam_result set math = 99 where name = 'jack' ;
-- 把jack 同学的数学成绩调整为99分

update exam_result set math = math + 10 where order by chinese desc limit 3;
-- 把班级语文成绩前三的同学的数学成绩都加上10分

update exam_result set english = english - 10 ;
-- 把全班所有同学的英语成绩减10分

alter修改表结构

修改表类型:

alter table 表名 modify 列名 修改后的列类型 ;
alter table exam_result modify [column] name varchar(10) ;
-- 将exam_result表中name列类型由varchar(20) 改为 varchar(10)
-- []表示可省略

增加表字段

alter table 表名 add column 列名 类型;
alter table exam_result add [column] class int ; 
-- 在exam_result表中,加一名为class,类型为int的列

删除表字段

alter table 表名 drop [column] 列名
alter table exam drop class ;
-- 在exam_result表中,删除class列

字段改名

alter table 表名 change [column] 旧列名 新列名 [新列的类型] ;
alter table exam_result change [column] name sname varchar(5) ;
-- 在exam_result表中, 将age改名为sname,同时秀在字段类型为varchar(5)
-- change 和 alter 都可以修改字段的类型, 不同的是change要输入两次字段名, 而alter只需要输入一次.
-- 但alter修改不了列名

修改字段排列顺序

alter table exam_result add class int first ;
-- 在第一列创建一个class字段
alter table exam_result add id int after name ;
-- 在字段name后创建一名为id的字段
-- add 可以替换为add/modify/change

注意: change/first/after column这些关键字都是MySQL自己的扩展, 在其他数据库上不一定适用

更改表名

alter table 表名 rename [to] 新表名 ;
alter table exam_result rename exam ;

6.删除语句

delete from 表名 where 条件 ;
-- 删除某一符合条件的记录

delete from 表名 ;
-- 删除表的全部记录, 但是表还在

drop table 表名 ;
-- 删除整个表, 包括表结构和记录

7.新增

新增是一个insert和select查询结合在一起的操作, 例如:

把从一个表中整个数据插入到另一张表中

insert into B select * from A ;
-- B是插入表, A是数据来源表

在这个语句中系统会先执行查找, 针对查找到的每个结果, 在执行插入操作, 插入到B中.

但我们需要保证: 从A中查询出来的结果类型要和B一样, 顺序可以不一样(可以在select 和from中间输入相对应顺序的类型名), 另外, 还可以给后面的select指定一些其他的条件: 排序/limit/去重…

ps: 插入的实际就是select执行结果的临时表

调换列顺序

若B表结构是 id int , name varchar(20), 但是C表是 name varchar(20) , id int 所以要在select和from中间换一下顺序, 如下:
```
insert into B select id , name from C; 
```

加条件

insert into B select id , name from C where id < 10 ;

8.查询进阶

8.1聚合查询

聚合查询 : 把多行(记录)的数据进行关联操作, 与前面学习的列与列之间的表达式查询不一样.

MySQL内置的一些聚合函数可以让我们直接使用

-- 1.计数
count([distinct] 列名 ) 
-- 2.求和
sum([distinct] 列名 )
-- 3.求平均值
avg([distinct] 列名 )
-- 4.求最大值
max([distinct] 列名 )
-- 5.求最小值
min([distinct] 列名 )

案例:

1) count计数多少行

select count(*) from test ;
select count(chinese) from test ;

在这里插入图片描述

注意: null不会记录在count中, avg也一样

2) sum求和: 把这一列的若干行进行相加

select sum(chinese) from test ;
select sum(a)+sum(b) from test ;

在这里插入图片描述

注意: sum只能对数字进行运算, 不能针对字符串, sum也可以加where条件语句, 所以系统会先执行筛选, 再求和, 其他函数用法一样…

8.2 分组操作

分组操作: group by , 根据行的值, 对数据进行分组, 把值都行通的行归为一组. 系统执行顺序为: 先分组, 再聚合

select role,max(salary),min(salary) from test group by role ;
-- 根据role这一列进行分组

在这里插入图片描述

having操作: 针对分组之后得到的结果, 可以通过 having 来指定筛选条件. 注意: having是从分组结果中筛选数据的(行), 而不是筛选列的. 其实 group by 也是可以使用 where 语句来添加条件的, 但是 where 是在分组之前执行的, 如果要对分组之后的结果进行筛选就要选用 having.

having操作如下:
在这里插入图片描述

where操作如下:
在这里插入图片描述

8.3 联合查询(多表查询的核心)

(1) 笛卡尔积是指针对任意两张表之间进行的计算, 把多个表的记录一齐合并, 一齐进行查询. 针对A,B两张表, 如何计算笛卡尔积呢? 可以使用 select * from 表名A , 表名B ;(其中一种做法). 来计算笛卡尔积, 此时笛卡尔积的列数, 就是表A的列数 + 表B的列数; 笛卡尔积的行数, 就是表A的行数 * 表B的行数.

操作介绍:
方法一 : 最简单的做法就是直接 select*from 表A, 表B… ; 笛卡尔积这个操作, 虽然执行效率不高, 但是是一个功能挺好的操作, 来完成一些更复杂的操作. 但是如果数据很大时, 建议不要轻易使用此方法, 因为会瞬间产生数量巨大的数据结果.

首先直接使用笛卡尔积查询演示结果如下:
在这里插入图片描述
接着在上一查询基础上加入条件语句:

补充: 当使用多表查询时, 为了区分表之间的列, 可以使用 “表名A.列名a” 来表示表A的列a.

接着在上一查询基础上过滤掉不需要展示的列:

在这里插入图片描述

方法二: join也可以实现多表查询: select 表名A.列名a, … from 表A join 表B on 条件1… ;
在这里插入图片描述
join的案例: 1. 查询所有同学的总成绩, 以及所有同学的个人信息:

select student.id,
		student.sn,
		student.name,
		student.qq_mail,
		student.classes_id, 
		sum(score) as totalscore 
from student join score on student.id = score.student_id group by student.id;

在这里插入图片描述
join的案例: 1. 查询所有同学的总成绩, 以及所有同学的个人信息:

select student.id, 
		student.name,
		course.name, 
		score.score 
from student,score,course 
where student.id = score.student_id and score.course_id = course.id;

在这里插入图片描述

那么 join 到底有什么与 where 不同呢??

答: join 有一个 where 实现不了的功能 ---- 外连接. 但是讲外连接之前我们先理解一下内连接. from 表1,表2… where 条件1,条件2… 的写法叫做内连接, 但是 join 既可以做到内连接, 也可以做到外连接:

内连接: select 表名.列名… from 主表名 inner join 表名 on 条件 ; inner 表示内连接, 可以省略.

外连接: select 表名.列名… from 主表名 left/right join 表名 on 条件 ; 表名的是左 / 右连接 .

如下所示:

首先创建两张表:
在这里插入图片描述
下来分别进行 join 的内连接 , 左连接, 右连接 :

可以看出: 内连接里的记录只是两个表里同时拥有的记录 ;

而左连接就是以左侧表为主, 左侧表每个记录都在左外连接中有体现; 右连接同理 .

他们类似于下图:
在这里插入图片描述
(2) 自连接 (不常见) : 把自己和自己进行笛卡尔积

自连接的目的是用来处理一些特殊的场景, 自连接的本质是把行和行之间的比较条件, 转换成列和列.

案例: 在一个班的成绩表中找出课程3比课程1分数高的同学

第一步: 自连接写法: (这样不加条件直接自连接会产生非常多的数据结果! ! 要注意)
在这里插入图片描述
提示: 可以给表名起别名, 方法是 表名 as 别名

第二步: 加入where筛选条件:

select *from score as s1, 
				score as s2 
where s1.student_id = s2.student_id 
		and s1.course_id = 3 and s2.course_id = 1 
		and s1.score > s2.score ;

得到结果:
在这里插入图片描述
第三步: 修改要显示的列:

select s1.student_id , 
		s1.score as course3, 
		s2.score as course2 
from score as s1 , 
		score as s2 
where s1.student_id = s2.student_id 
		and s1.course_id = 3 and s2.course_id = 1 
		and s1.score > s2.score ;

得到结果:
在这里插入图片描述
总结: 自连接的关键就是能把行变成列.

(3) 子链接 : 简单来说及时select 间的套娃, 把多个 select 合并成一个.

案例: 找出许仙同学的同班同学.

select name 
from student 
where classes_id = (select classes_id from student where name = '许仙');

(4) 合并查询: 把多个查询结果合并在一起. 关键字 union, 比如: 查询1 union 查询2 ;

注意: union 操作会自动去重 ; 但是 union all 则不会 .

	案例: 找出 课程名称为英文 和 课程id<3的 课程

select * from course where name = '英文'  union  select * from course where id<3 ;

结果如下:
在这里插入图片描述

以上就是MySQL的基本操作啦, 但是这并不是所有, MySQL还有很多深入知识, 比如索引, 锁机制, 事务, 优化等等等等, 需要我们不断学习, 所以保持一个好奇的心态, 一起加油吧 !

【MySQL】学习总结基础篇

文章目录

一、认识数据库

1.数据库大体分为两大类：关系型和非关系型数据库

二、使用指令

1.标题SQL中的指令分类

2.数据库指令

3.数据表指令

3.1 创建表

3.2 表的设计

3.3 查看表

3.4 查看表的结构

3.5 删除表

4. 查找语句

5.修改语句

6.删除语句

7.新增

8.查询进阶

8.1聚合查询

8.2 分组操作

8.3 联合查询(多表查询的核心)

未完待续…