MySql学习笔记（基础）

wFitting

于 2019-06-10 22:39:46 发布

阅读量501

点赞数

分类专栏： mysql 文章标签： MySQL

本文链接：https://blog.csdn.net/wfitting/article/details/91406976

版权

mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

MySql学习笔记（基础）

学习笔记

学习笔记

数据库基础

1.什么是数据库？
数据库：database，存储数据的仓库
数据库：高效的存储和处理数据的介质（介质主要是两种：磁盘和内存）

2.数据库的分类？
数据库基于存储介质的不同：进行了分类，分为两大类：关系型数据库（SQL）和非关系型数据库（NoSQL:Not Noly SQL,不是关系型的数据库都叫做非关系型数据库）

3.不同的数据库阵营中的产品有哪些？
关系型数据库：
大型：Oracle，DB2
中型：SQL-SERVER，MySql等
小型：access等

非关系型数据库：
memcached，mongodb，redis（同步到磁盘）

4.两种数据库阵营的区别？
关系型数据库：安全（保存磁盘基本不可能丢失）容易理解，比较浪费空间（二维表）

非关系型数据库：效率高，不安全（断电会丢失）

关系型数据库
1.什么是关系型数据库？
关系型数据库：是一种建立在关系模型（数学模型）上的数据库。
关系模型：一种所谓建立在关系上的模型，关系模型包含三个方面。
数据结构：数据存储的问题，二维表（有行和列）
操作指令集合：所有SQL语句
完整性约束：表内数据约束（字段与字段），表与表之间约束（外键）

2.关系型数据库的设计？
关系型数据库：从需要存储的数据需求中分析2，如果是一类数据（实体）应该设计成一张二位表：表示由表头（字段名：用来规定数据的名字）和数据部分组成（实际存储的数据单元）

以实际案例来进行处理：分析一个教学系统，讲师负责教学，教学生，在教室教学生
a.找出系统中所存在的实体：讲师表，学生表，班级表
b.找出实体中应该存在的数据信息
讲师：姓名，性别，年龄，工资
学生：姓名，性别，学号，学科
班级：班级名称，教室编号

关系型数据库：维护实体内部，实体与实体之间的关系

实体内部联系：每个学生都有姓名，性别，学号，学科信息

关系型数据库的特点之一：如果表中对应的摸个字段没有值
（数据），但是系统化依然要分配空间：关系型数据库比较浪费空间

实体与实体之间的联系：每个学生肯定属于某个班级，每个班级一定有多个学生（一对多）

关键字说明

数据库：database
数据库系统：DBS（Database System）：是一种虚拟系统，将多种内容关联起来的称呼

DBS = DBMS + DB
DBMS：Database Management System，数据库管理系统，专门管理数据库
DBA：Databse Administrator，数据库管理员

SQL
SQL：Structured Query Language，结构化查询语言（数据以查询为主：99%是在进行查询操作）

SQL分为三个部分：
DDL：Data Definition Language，数据定义语言，用来维护存储数据的结构（数据库，表）；
代表指令：create，drop，alter等。

DML：Data Manipulation Language，数据操作语言，用来对数据进行操作（数据表中的内容），代表指令：insert,delete,update等。其中DML内部又单独进行了一个分类：DQL（Data Query Language：数据查询语言，如select）

DCL：Data Control Language，数据控制语言，主要是负责全县管理（用户），代表指令：grant，revoke等

SQL是关系型数据库的操作指令，SQL是一种约束，但不强制（类似W3C）：不同的数据库产品（如Oracle，mysql）可能内部会有一些细微的区别。

Mysql数据库
Mysql数据库是一种C/S结构的软件：客户端/服务端。若想访问服务器必须通过客户端（服务器一直运行，客户端在需要使用的时候运行）。
在这里插入图片描述

SQL的基本操作

基本操作：CRUD（增删改查）

将SQL的基本操作根据操作对象进行分类：分为三类：库操作，表操作（字段），数据操作。

库操作
对数据库的增删改查

新增数据库
基本语法
create database 数据库名 [库选项];
库选项：用来约束数据库，分为两个选项
字符集设定：charset/character set具体字符集（数据存储的编码格式）：常用字符集：GBK 和 UTF8
校对集设定：collate具体校对集（数据比较的规则）

–双中划线+空格：注释（单行注释），也可以使用#号
create database mydatabase charset utf8;
在这里插入图片描述
其中：数据库名字不能用关键字（已经被使用的字符）或者保留字（将来可能会用到）

如果非要使用关键字或者保留字，需要加`反引号

中文数据库是可以的，但是有前提条件：保证服务器能够识别。（建议不用）

1.查看所有数据库
show databases;
2.查看指定部分的数据库：模糊查询
Show databases like ‘pattern’;-- pattern是匹配模式
%:表示匹配多个字符
_:表示匹配单个字符
在这里插入图片描述
3.查看数据库的创建语句：
show creat database 数据库名;

更新数据库

数据库名字不可以修改
数据库修改仅限库选项：字符集和校对集（校对集依赖字符集）
Alter database 数据库名字 [库选项];
Charset/characterset [=] 字符集
Collate 校对集

删除数据库

所有的操作中：删除是最简单的
Drop database 数据库名子;

当删除数据库语句执行之后，发生了什么？
1.在数据库内部看不到对应的数据库
2.在对应的数据库存储的文件夹内：数据库名子对应的文件夹也被删除（级联删除：里面的表的数据也全部删除）

注意：数据库的删除不是闹着玩儿的，不要随意删除，应该先进行备份后操作（删除不可逆）

表操作

表与字段是密不可分的。

新增数据表
Create table [if not exists] 表名(
字段名子数据类型,
字段名字数据类型
– 最后一行不需要逗号
)[表选项];

if not exists：如果表名不存在，那么就创建，否则不执行创建代码：检查功能

表选项：控制表的表现
字符集：charset/character set 集体字符集;-- 保证表中数据存储的字符集

校对集：collate 具体校对集;
存储引擎：engine 具体的存储引擎(innodb和myisam)

在这里插入图片描述
任何一个表的设计都必须指定数据库。
方案1：显示的指定表所属的数据库
Create table 数据库名.表名();-- 将当前数据表创建到指定的数据库下

方案2：隐式的指定表所属数据库：先进入到摸个数据库环境，然后这样创建的表自动归属到摸个指定的数据库。
进入数据库环境：use 数据库名子;

查看数据表
1.数据库能查看的方式，表都可以查看。
查看所有表
show tables;
2.查看部分表：
模糊匹配 show tables like ‘_%’;
3.查看表的创建语句
show create table student\g – 等价于分号
\G – 将查到的结构旋转90度变成纵向
4.查看表结构：查看表中的字段信息
Desc/describe/show columns form 表名;

在这里插入图片描述

修改数据表

表本身存在，还包含字段：表的修改分为两个部分：修改表本身和修改字段

修改表本身：
表本身可以修改：表名和表选项

修改表名(重命名表名)：
rename table 老表名 to 新表名;
strudent表 -> my_student（去数据库名子前两个字母）

修改表选项：
字符集校对集存储引擎
Alter table 表名表选项 [=] 值

修改字段
字段操作很多：新增，修改，重命名，删除

新增字段
Alter table 表名 add [column] 字段名数据类型 [列属性] [位置];
位置：字段名可以存放在表中的任意位置
First：第一个位置
After：在哪个字段之后：after 字段名;默认的是在最后一个字段之后

给学生表增加id放到第一个位置
在这里插入图片描述
修改字段名:修改通常是修改属性或者数据类型
Alter table 表名 modify 字段名数据类型 [属性] [位置];
将学生表中的number学号字段变成固定长度，切放到第二位（id之后）

重命名字段
Alter table 表名 change 旧字段新字段名数据类型 [属性] [位置];

修改学生表中的gender字段为sex
在这里插入图片描述
删除字段
alter table 表名 drop 字段名;

删除数据表
Drop table 表名1,… 可以一次性删除多张表

数据操作

新增数据
有两种方案：
方案1：给全表字段插入数据；不需要指定字段列表：要求数据的值出现的顺序必须与表中设计的字段出现的顺序一致；凡是非数值数据，都需要使用引号（建议使用丹壹号）包裹

Insert into 表名 values(值列表),[(值列表)];-- 可以一次性插入多条记录
在这里插入图片描述
方案2：给部分字段插入数据，需要选定字段列表：字段列表出现的顺序与字段的顺序无关；但是值列表的顺序必须与选定的字段的顺序一致。
Insert into 表名 (字段列表) values(值列表),[(值列表)];

查看数据

Select */字段列表 form 表名 [where 条件]
在这里插入图片描述
查看指定字段指定条件的字段

更新数据

Update 表名 set 字段 = 值 [where条件] --建议都有where；要不是更新全部。

删除数据

Delete from 表名 [where条件];

中文数据问题

中文数据问题本质是字符集问题。
计算机只识别二进制：人类更多是识别符号：需要有个二进制与字符的对应关系（字符集）

查看字符集
在这里插入图片描述
修改客户端字符集

数据乱码原因：
数据来源是服务器，解析数据是客户端（客户端只识别GBK;只会两个字节一个汉字，但事实服务器给的数据缺失UTF8，三个字节一个汉字；乱码）
解决方案：
修改服务器给客户端的数据字符集为GBK
Set character_set_results = gbk;
在这里插入图片描述
Set 变量 = 值;修改只是会话级别（当前客户端，当次连接有效，关闭失败）

校对集问题

校对集：数据比较的方式

校对集有三种格式
_bin;binary,二进制比较，取出二进制位，一位一位的比较；区分大小写
_cs;case sensitive,大小写敏感，区分大小写
_ci case insensitice,不区分大小写

查看数据库所支持的校对集：
show collation;

校对集应用：只有当数据产生比较的时候，校对集才会生效。

Web乱码问题
动态网站由三部分构成：浏览器，apache服务器(JAVA)，数据库服务器，三个部分都有自己的字符集（中文），数据需要在三个部分之间来回传递；很容易产生乱码

如何解决乱码问题：同一编码（三码合一）

但事实上不可能：浏览器是用户管理（根本不可能控制）；
但是必须要解决这些问题：主要靠java来解决这些问题

数据类型（列类型）

所谓的数据类型：对数据进行统一的分类，从系统的角度出发为了能够使用统一的方式进行管理；更好的利用有限的空间

SQL中将数据类型分成了三大类：数值类型，字符串类型和时间日期类型
在这里插入图片描述
数值型
数值型数据：都是数值
系统将数值型分为整数型和小数型

整数型
存放整型数据：在SQL中因为更多要考虑如何节省磁盘空间，所以系统将整型又细分成了5类：
Tinyint：迷你整型，使用一个字节存储，表示的状态最多为256种
Smallint：小整型，使用2个字节存储，表示的状态最多为65536种
Mediumint：中整型，使用3个字节存储
Int：标准整型，使用4个字节（常用）
Bigint：大整形，使用8个字节存储

字段属性

主键，唯一键和自增长

主键：
primary key，主要的键，一张表只能有一个字段可以使用对应的键，用来唯一的约束该字段里面的数据，不能重复：这种称之为主键。

一张表只能有最多一个主键。

增加主键：
SQL操作中有很多方式可以给表增加主键：大体分为三种。

方案1：在创建表的时候，直接在字段之后，跟primary key关键字（主键本身不允许为空） not null 不能为空 comment ‘备注’
在这里插入图片描述
优点：非常直接；
缺点：只能使用一个字段作为主键

方案2：在创建表的时候，在所有的字段之后，使用primary key(主键字段列表)来创建主键（如果有多个字段作为主键，可以是复合主键）
在这里插入图片描述
方案3：当表已经创建好之后，再次额外追加主键，可以修改表字段属性，也可以直接追加。
Alter table 表名 add primary key(字段列表);

前提：表中字段对应的数据本身是独立的（不重复）

主键约束：
主键对应的字段中的数据不允许重复：一旦重复，数据操作失败（增和改）

删除主键：
Alter table 表名 drop primary key;
在这里插入图片描述
表关系将实体与实体的关系，反应到最终数据库表的设计上来：将关系分成三种：一对一，一对多（多对一），多对多。所有的关系都是指的表与表之间的关系一对一关系：一张表的一条记录一定只能与另外一张表的一条记录进行对应，反之亦然。学生表：姓名，性别，身高，体重，籍贯，家庭住址，紧急联系人这些数据属于常用数据。如果每次都是查询所有数据，不常用的数据就会影响效率，实际不用。解决方案：将常用和不常用的信息分离存储，分成两张表保证不常用信息和常用信息一定要对应上：找一个具有唯一性（确定记录）的字段来共同连接两张表一个常用表中的一条记录：永远只能在一张不常用表中匹配一条记录，反过来，一张不常用的表中的一条记录在常用表中也只能匹配一条记录：一对一关系。一对多关系：一对多：一张表中有一条记录可以对应另外一张表中的多条记录；但是反过来，另外一张表的一条记录只能对应第一张表的一条记录，这种关系就是一对多或者多对一。母亲与孩子的关系应该在孩子表中添加一个字段指向母亲表，因为孩子表的记录只能匹配到一条母亲的记录多对多关系一张表中的一条记录能够对应另外一张表中的多条记录，同时另一个表中的一条记录也能对应该表中的多条记录：多对多的关系老师和学生表以上设计方案：实现了实体的设计，但是没有维护实体的关系。一个老师教过多个学生；一个学生也被多个老师教过解决方案：增加中间表：专门维护两张表之间的关系

范式：

No马来Format，是一种离散数学中的知识，是为了解决一种数据存储于优化的的问题，保存数据存储之后；范式能通过关系寻找出来的数据，坚决不再重复存储；中级目标是为了减少数据的冗余。
是一种分层结构的规范，分为六层：每一层都比上一层更加严格，若要满足下一层范式，前提是满足上一层范式。

六层范式：最高层最严格

Mysql属于关系型数据库，有空间浪费，也是致力于节省存储空间：与范式所有的解决的问题不谋而合：再设计数据库的时候，会利用范式来指导设计。

但是数据库不但是解决空间问题，要保证效率问题：范式知识为解决空间问题，所以数据库的设计又不可能完全按照范式的要求实现：一般情况下，只有钱三种范式需要满足。

范式在数据库的涉及当中是有指导意义：但是不是强制规范

1NF（第一范式）
在设计表存储数据的时候，如果表中设计的字段存储的数据，在取出来使用之前还需要额外的处理（拆分），那么说表的设计不满足第一范式：第一范式要求字段的数据具有原子性：不可拆分

2NF（第二范式）
在数据表设计的过程中，如果有复合主键（多字段主键），且表中有字段并不是由整个主键来确定，而是依赖主键中的某个字段（主键的部分）：存在字段依赖主键的部分问题，称之为部分依赖：第二范式就是要解决表设计不允许出现部分依赖
（复合主键：一个老师在一个班永远只带一个阶段的课）

3NF（第三范式）
要满足第三范式，必须满足第二范式
第三范式：一张表中的所有字段都应该直接依赖主键（逻辑主键：代表的是业务主键），如果表设计中存在一个字段，并不是直接依赖主键，而是通过某个非主键字段依赖，最终实现依赖逐渐：把这种不是直接依赖主键，而是依赖非主键字段的依赖关系称之为传递依赖。

蠕虫复制

从已有的数据中去获取数据，然后将数据进行新增操作：数据成倍的增加

表创建的高级操作：从已有表创建表（复制表结构）
Create table 表名 like 数据库.表名;

蠕虫复制：先查出数据，然后将查出的数据新增一遍
Insert into 表名[(字段列表)] select 字段列表/* from 数据表名;

查询数据

基本语法：
select 字段列表/* from 表名 [where 条件];

完整语法
select [select 选项] 字段列表[字段别名]/* from 数据源 [where 条件子句] [group by 子句] [having 子句] [order by 子句] [limit 子句];

select 选项：select对查出来的结果的处理方式
ALL：默认的，保留所有的结果
Distinct：去重，查出来的结果，将重复给去除（所有字段都相同）

数据源：
分为多种：单表数据源，多表数据源，查询语句

多表数据源
SELECT * FROM student,grade

查询语句
SELECT StuName,(SELECT g.GradeName FROM grade g WHERE g.GradeNo=s.GradeId),s.GradeId FROM student s

Where子句返回结果：0或1，
like模糊 between and in/not in or

找学生id为1,6,9
SELECT * from student WHERE StuNo in(1,6,9) --落在集合中
SELECT * from student where StuNo = 1 OR StuNo = 6 OR StuNo = 9 --逻辑判断或运算

Group By子句

Group By 分组的意思是，根据某个字段进行分组（相同的放一组，不相同的放一组）

根据班级分组
SELECT * from student GROUP BY GradeId

分组的意义：是为了统计数据（按组统计：按分组字段进行数据统计）
SQL提供了一系列的统计函数
count()：统计分组后的记录数：每一组有多少记录
Max()：统计每组中最大值
Min()：统计每组中最小值
Avg():统计平均值
Sum():统计和

SELECT StuSex,COUNT(*),MAX(StuAge),MIN(StuAge),AVG(StuAge) from student group BY StuSex

Count函数：里面可以使用两种参数：*代表统计记录，字段名代表统计对应的字段（Null 不统计）

分组会自动排序：根据分组字段：默认排序
Group by 字段 [asc|desc] --对分组的结果然后合并之后的整个结果进行排序

多字段分组：先根据一个字段进行分组，然后对分组后的结果再次按照其他全字段进行分组
SELECT GradeId,StuSex,COUNT() FROM student GROUP BY GradeId,StuSex

有一个函数：可以对分组的结果中的某个字段进行字符串连接（保留该组所有的某个字段）group_concat(字段)
SELECT GradeId,StuSex,COUNT(),GROUP_CONCAT(StuName) FROM student GROUP BY GradeId,StuSex
在这里插入图片描述
回溯统计：with rollup
任何一个分组后都会有一个小组，最后都需要向上级分组进行汇报统计：根据当前分组的字段，这就是回溯统计：回溯统计的时候会将分组字段置空
SELECT GradeId,COUNT(*) FROM student GROUP BY GradeId WITH ROLLUP

在这里插入图片描述
多字段分组回溯统计
SELECT GradeId,StuSex,COUNT(*),GROUP_CONCAT(StuName) FROM student GROUP BY GradeId,StuSex WITH ROLLUP

Having子句
与where子句一样，进行条件判断的。

where是针对磁盘数据进行判断：进入到内存之后，会进行分组操作：分组结果就需要having来处理

having能做where能做的几乎所有事情，但是where却不能做having能做的很多事情
分组统计的结果或者说统计函数只有having能做

求出所有班级人数大于等于3的学生人数
SELECT GradeId,COUNT() FROM student GROUP BY GradeId HAVING COUNT()>=3
在这里插入图片描述
Order by子句
排序，根据某个字段进行升序或者降序排列，依赖校对集

使用基本语法：
order by 字段名 [asc|desc]

Limit子句
是一种限制结果的语句：限制数量

Limit有两种使用方式
方案一：只用来限制长度（数据量） limit 数据量
方案二：限制起始位置，限制数量 limit起始位置,长度

SELECT (SELECT g.GradeName FROM grade g WHERE g.GradeNo=s.GradeId) ‘班级’,COUNT() ‘总人数’,GROUP_CONCAT(StuName) ‘学生姓名’,StuSex ‘性别’ FROM student s GROUP BY GradeId,StuSex ORDER BY COUNT() DESC LIMIT 0,4
在这里插入图片描述

连接查询

将多张表（可以大于2张）进行记录的连接（按照某个指定的条件进行数据拼接）
最终结果是：记录数有可能变化，字段数一定会增加（至少两张表的合并）

连接查询的意义：在用户查看数据的时候，需要显示的数据来自多张表

连接查询分类
Sql中将连接查询分成四类：内连接，外链接，自然连接和交叉连接
连接查询：join，使用方式：左表 join 右表
左表：在join关键字左边的表
右表：在join关键字右边的表

交叉连接：
cross join，从一张表中循环取出每一条记录，每条记录都去另外一张表进行匹配：匹配一定保留（没有条件匹配），儿连接本身字段就会增加（保留），最终形成的结果叫做：笛卡尔积

基本语法：左表 cross join 右表
SELECT * FROM student s CROSS JOIN grade g WHERE s.GradeId = g.GradeNo
在这里插入图片描述
交叉连接存在的价值：保证连接这种结构的完整性，没有实际意义

内连接
[inner] join，从左表中取出每一条记录，去右表中与所有的记录进行匹配：匹配必须是某个条件在左表中与右表中相同最终才会保留结果，否则不保留

基本语法：
左表 [inner] join 右表 on 左表.字段 = 右表.字段
on 表示连接条件，条件字段就是代表相同的业务含义
SELECT * FROM student s INNER JOIN grade g ON s.GradeId=g.GradeNo
在这里插入图片描述
外连接
以某张表为主，取出里面的所有记录，然后每条与另外一张表进行连接，不管能不能匹配上条件，最终都会保留，能匹配，正确保留，不能匹配，其他表的字段都置空