mysql数据库项目式教程期末_MySQL完整教程（共8章）

正文

【第一章】

1.1 MySQL学习路线

基础阶段：MySQL数据库的基本操作(增删改查)，以及一些高级操作(视图、触发器、函数、存储过程等)。

优化阶段：如何提高数据库的效率，如索引，分表等。

部署阶段：如何搭建真实的环境系统，如服务器集群，负载均衡等。

1.2 数据库的基础

1.2.1 什么是数据库？

数据库：database，存储数据的仓库。

数据库(专业定义)：高效的存储和处理数据的介质(介质主要是两种：磁盘和内存)。

1.2.2 数据库的分类？

基于存储介质的不同

关系型数据库(SQL)

非关系型数据库(NoSQL)

1.2.3 不同的数据库阵营中的产品有哪些？

关系型数据库:

大型数据库：Oracle、DB2。

中型数据库：SQL Server、MySQL等。

小型数据库：access等。

非关系型数据库：

memached

mogodb

redis

1.2.4 两种数据库阵营的区别？

关系型数据库：安全(保存磁盘基本不可能丢失)，容易理解，比较浪费空间。

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

1.3 关系型数据库

1.3.1 什么是关系型数据库？

关系型数据库：是一种建立在关系模型(数学模型)上的数据库。

关系模型：一种所谓建立在关系的模型，关系模型包括以下三个方面：

数据结构：数据存储的问题，二维表(有行和列)。

操作指令集合：所有SQL语句。

完整性约束：表内数据约束，表与表之间约束(外键)。

1.3.2 关系型数据的设计？

从需要存储的数据需求中分析，如果是一类数据(实体，比如人、书本等)应该设计成一张二维表，表是由表头(字段名)和数据部分(实际存储的数据单元)组成，如下图所示。

表头

字段1

字段2

数据单元

数据1

数据2

以实际案例来进行处理：分析一个教学系统，讲师负责教学，教学生，在教室教学。

①找出系统中的实体：讲师表，学生表，班级表。

②找出实体中应该存在的数据信息：

讲师：姓名，性别，年龄，身高，工资等。

学生：姓名，性别，学好，学科等。

班级：班级名字，教室编号等。

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

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

姓名

性别

学号

学科

年龄

张三

男

001

java

23

李四

男

002

MySQL

王二

003

Linux

17

第二行的所有字段，都是描述张三这个学生(内部联系)；第二列只能存放性别(内部约束)。

关系型数据库的特点之一：如果表中对应的某个字段没有值(数据)，但是系统依然需要分配空间，所以关系型数据库比较浪费空间。

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

学生表：

姓名

性别

学号

学科

年龄

张三

男

001

java

23

李四

男

002

MySQL

王二

003

Linux

17

班级表：

班级名称

教室编号

java001

A001

Linux002

B005

解决方案：在学生表中增加一个班级字段来指向班级(必须能够唯一的找到一个班级信息)

姓名

性别

学号

学科

年龄

所属班级

张三

男

001

java

23

java001

李四

男

002

MySQL

MySQL005

王二

003

Linux

17

Linux002

学生实体与班级实体之间的关系：实体与实体之间的关系。

1.4 关键字说明

数据库：database。

数据库系统：database system(DBS):是一种虚拟系统，将多种内容关联起来的称呼。DBS=DBMS+DB。

DBMS：数据库管理系统，database management system，专门管理数据库。

DBA：数据库管理员，database administrator。

行/记录：row/record,本质是一个东西，都是指表中的一行(一条记录)。行是从结构角度，记录是从数据角度出发。

列/字段：column/Field，本质是一个东西。

1.5 SQL

SQL：Structured Query Language，结构化查询语言(数据主要以查询为主)。

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等)内部可能有一些细微的差别。

1.6 MySQL数据库

MySQL数据库是一种C/S结构的软件：客户端/服务端，如果想访问服务器必须通过客户端(服务器一直运行，客户端在需要使用的时候运行)。

交互方式：

①客户端连接认证：连接服务器，认证身份。

②发送SQL指令。

③服务器接收SQL指令，处理SQL指令，返回操作结果。

④客户端接收结果，显示结果。

⑤断开连接(释放资源：服务器并发限制)。

1.7 MySQL服务器对象

没有办法完全了解服务器内部的内容：只能粗略的去分析数据库服务器的内部结构。

将MySQL服务器内部对象分成了四层：

系统(DBMS)

数据库(DB)

数据表(Table)

字段(Field)

1.8 SQL的基本操作

基本操作：CRUD。

将SQL的基本操作根据操作对象进行分类，分为三类，如下所示：

库操作。

表操作(字段)。

数据操作。

1.9 库操作

对数据库的增删改查。

1.9.1 新增数据库

基本语法

create database 数据库名字 [库选项];

库选项：用来约束数据库，分为两个选项。

字符集设定：charset/character set 具体字符集(数据存储的编码格式)。

校对集设定：collate 具体校对集(数据比较的规则)。

示例：创建一个数据库。

-- 单行注释，也可以用#

# 创建数据库

CREATE DATABASE mydatabase CHARACTER SET utf8;

其实，数据库名字不能使用关键字(已经被使用的字符)或保留字(将来可能会使用的字符)。

当创建数据库的SQL语句执行之后，发生了什么？

①在数据库系统中，增加了对应的数据库信息。

②会在保存数据的文件夹下：Data目录，创建一个对应数据库名字的文件夹。

③每个数据库下都有一个opt文件:保存了库选项。

1.9.2 查看数据库

基本语法

①查看所有数据库。

-- 查看所有数据库

show databases;

②查看指定部门的数据库：模糊查询。

-- 查看指定部分的数据库

show databases like 'pattern'; -- pattern是匹配模式

%:表示匹配多个字符

_:表示匹配单个字符

③查看数据库的创建语句。

-- 查看数据库的创建语句

show create database 数据库名字;

示例：查看所有数据库

示例：查看指定名称为test的数据库。

示例：查看含有t名称的数据库。

示例：显示mydatabase数据库的创建语句。

1.9.3 更新数据库

数据库名字是不可以改变的，不然如何定位哪一个数据库，换句话说，原来有一个数据库名为test，你现在将其它数据库改为test，这样好吗？

数据库的修改仅限库选项：字符集和校对集(校对集依赖于字符集)。

基本语法

alter database 数据库名字 [库选项]。

character set [字符集]

collate 绞对集

示例：修改mydatabase数据库的默认编码。

1.9.4 删除数据库

所有的操作中：删除是最简单的。

基本语法

-- 删除数据库

drop database 数据库名字;

示例：删除mydatabase数据库。

当删除数据库语句执行之后，发生了什么？

在数据库内部看不到对应的数据库。

在对应的数据库存储的文件夹内，数据库名字对应的文件夹也被删除(级联删除：里面的数据库表一起被删除)。

注意：数据库的删除不是闹着玩的，不要随意的删除，应该先进行备份操作，然后才考虑是否删除(删除不可逆)。

1.10 表操作

表和字段是密不可分的。

1.10.1 新增数据表

基本语法

-- 新增数据表

create table [if not exists] 数据表名字(

字段名字 数据类型,

字段名字,数据类型 -- 最后一行不不要逗号

)[表选项];

if not exists：如果表名不存在，那么就创建，否则不执行创建。

表选项：控制表的表现。

字符集：charset/character set 具体字符集;--保证表中数据存在的字符集。

校对集：collate 具体校对集。

存储引擎：engine 具体的存储引擎(innodb和myisam)

示例：创建一个名为student的表。

任何一个表的设计都必须指定数据库。

解决方案：

①显示指定表所在的数据库。

-- 显示的指定表所在的数据库

create table 数据库名.表名(

字段1 数据类型,

字段2 数据类型

);

②隐式的指定表所属数据库：先进入到某个数据库环境，然后这样创建的数据库表自动归属到某个数据库。

-- 进入到数据库环境

use 数据库名字;

-- 创建数据表

create table 数据表名(

字段1 数据类型,

字段2 数据类型

)；

当创建数据库的SQL指令执行之后，到底发生了什么？

指定数据库下已经存在对应的表。

在数据库对应的文件夹下，会产生对应表的结构文件(和存储引擎有关)。

1.10.2 查看数据表

数据库能查看的方式，表都可以查看。

基本语法

查看所有表

-- 查看所有表

show tables

查看部分表

-- 查看部分表

show tables like 'pattern';

查看表的创建语句

-- 查看表的创建语句

show create table 表名;

查看表结构：查看表中的字段信息

-- 查看表结构

①desc 表名;

②describe 表名;

③show columns from 表名;

示例：查看所有表

示例：查看创建student表的语句。

示例：查看表结构。

1.10.3 修改数据表

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

修改表本身：

表名

基本语法：

-- 修改表名

rename table 老表名 to 新表名;

示例：将student表的名字改为stu.

表选项:字符集，校对集和存储引擎。

alter table 表名 [表选项];

示例：修改stu表的编码为gbk

修改字段

新增字段

alter table 表名 add [column] 字段名 数据类型[列属性][位置];

位置：字段名可以存在表中的任意位置

first：第一个位置

after:在哪个字段之后，after 字段名，默认在最后

修改字段

alter table 表名 modify 字段名 数据类型[列属性] [位置]；

重命名字段

alter table 表名 change 旧字段名 新字段名 数据类型[列属性][位置];

删除字段

alter table 表名 drop 字段名;

1.10.4 删除数据表

基本语法

-- 删除表

drop table 表名1,表名2,……;

当删除数据表的指令执行之后发生了什么？

①在表空间中，没有了指定的表(数据也没有了)。

②在数据库对应的文件夹下，表对应的文件(与存储引擎有关)也会被删除。

1.11 数据操作

1.11.1 新增数据

有两种方案

①给全表字段插入数据，不需要指定字段列表：要求数据的值出现的位置必须和表中设计的字段出现的顺序一致，凡是非数值的数据，都需要使用引号包裹。

insert into 表名 values (值列表)[,(值列表)]; -- 可以一次性插入多条记录

②给部分字段插入数据：需要选定字段列表，字段列表出现的顺序和字段的顺序无关，但是值列表的顺序必须和选定的字段的顺序一致。

insert into 表名 (字段列表) values (值列表);

1.11.2 查看数据表

基本语法

select */字段列表 from 表名 [where 条件] ;

示例：查看学生的所有信息

示例：查看学生的姓名和性别

1.11.3 更新数据

基本语法

update 表名 set 字段1=值1,字段2=值2,……[where 条件];

示例：更新id=1的年龄为32

1.11.4 删除数据

基本语法

delete from 表名 [where 条件];

示例：删除name为zhangsan的学生信息

1.12 中文数据问题

中文数据问题本质是字符集问题。

计算机只识别二进制，人类更多的是识别符号，所以需要有个二进制和字符的对应关系(字符集)。

客户端向服务器插入中文数据：没有成功

原因：\xD5\xC5\xC8\FD 代表的是“”张三”在当前编码(字符集)下的二进制转换为十六进制，两个汉字-->四个字节(GBK)。

报错：服务器没有识别对应的四个字节，服务器认为数据是utf8的，一个汉字对应三个字节；所以服务器读取三个字节转换成汉字，失败了;剩余的再读三个字节，最终失败。

所有的数据库服务器认为(表现)的一些特性是通过服务器端的变量来保存的，系统先读取自己的变量，看看应该怎么表现。

查看服务器识别的所有字符集

show character set;

服务器默认的和客户端打交道的字符集。

show variables like 'character_set%';

问题根源：客户端数据只能是GDK，而服务器认为是utf8。

解决方案：改变服务器。默认的接收字符集为GBK。

-- 修改服务器认为的客户端数据的字符集为GBK

set character_set_client=gbk;

插入中文的效果

查看数据效果：依然是乱码

原因：数据来源是服务器，解析数据是客户端(客户端只识别GBK：只会两个字节一个汉字)，但是服务器给的数据是UTF8，所以导致乱码。

解决方案：修改服务器给客户端的数据字符集为GBK。

set character_set_results=gbk;

查看数据效果

set 变量=值；这样修改只是会话级别(当前客户端当次连接有效，关闭失效)

设置服务器对客户端的字符集的认知，如果按照上面的方式，太麻烦了。可以使用快捷方式。

set names 字符集;

1.13 校对集问题

校对集：数据比较的方式。

校对集有三种方式：

_bin：binary，二进制比较，取出二进制位，一位一位的比较。区分大小写。

_cs：case sensitive，大小写敏感，区分大小写。

_ci