1.数据库基本概念，MySQL 安装，约束（薪牛主讲）

教程 ⋅ 阿布都的都 ⋅ 于 2023-01-06 11:19:45 ⋅ 817 阅读

1 数据库基本概念

1.1 数据库

​ 数据库就是存储数据的仓库，其本质是一个文件系统，数据按照特定的格式将数据存储起来，用户可以对数据库中的数据进行增加，修改，删除及查询操作。

1.2 关系型数据库

一个关系型数据库就是由二维表及其之间的联系所组成的一个数据组织。

主流的关系型数据库有 MySQL、Oracle、DB2、SQL Server等。

MYSQL ：开源免费的数据库，小型的数据库.已经被Oracle收购了.MySQL6.x版本也开始收费。

Oracle ：收费的大型数据库，Oracle公司的产品。Oracle收购SUN公司，收购MYSQL。

DB2 ：IBM公司的数据库产品，收费的。常应用在银行系统中。

SQLServer：MicroSoft 公司收费的中型的数据库。C#、.net等语言常使用。

本课程使用MySQL

1.3 实体关系模型-ER图

​ 实体关系模型对现实世界进行抽象，得出实体类型和实体间的关系，用来描述现实世界中数据的组成结构。

​ 实体关系图（Entity Relationship Diagram）是指提供了表示实体、属性和关系的图形化表示方式，用来描述现实世界的概念模型，也简称为E-R图。

实体关系模型核心的元素

实体（Entity）：是具有相同特征和属性的现实世界事务的抽象，在E-R图中用矩形表示，矩形框内注明实体的名称

属性（Attribute）：是指实体具有的特性，一个实体可以包含若干个实体。在E-R图中属性用椭圆形表示，并使用线条将其与相应的实体连接起来。比如员工具有工号、入职日期等属性

关系（Relationship）：是指实体之间的相互联系的方式，一般具有一对一关系（1:1）、一对多关系（1:N）、多对多关系（M:N）

例子：

file

E-R图与数据表

E-R图 数据表
实体 表
属性 字段
实体的一个记录 表的一行记录

比如：

​ 学生实体 对应 学生表。

​ 一个实体有多个属性， 对应 学生表 有多个字段（二维表的多列）。

​ 一个实体的一条记录， 对应 学生表的一行记录（二维表的一行）。

1.4 数据库三范式

​ 为了规范化关系型数据模型，要求数据库系统在设计时必须遵循一定的规则，这种规则就称为关系型数据库系统范式。

​ 使用范式的主要目的是为了降低数据的冗余，设计结构合理的数据库。

三范式

​ 第一范式（1NF）：字段必须具有单一属性特性，不可再拆分，比如姓名字段，必须具有单一的属性，不可以在一个字段中包含员工中文名或英文名，必须考虑拆分为两个字段。

​ 第二范式（2NF）：表要具有唯一性的主键列。也就是说表中的每一行要具有一个唯一性的标识列，比如通常使用GUID或自动增长的数字编号来唯一地标识一行，或者是使用学号来唯一标识一个学生。

​ 第三范式（3NF）：表中的字段不能包含在其他表中已出现的非主键字段，比如学生表里面不能出现班级名字字段,可以使用班级ID。

2 MySQL安装

2.1 MySQL安装

1）点开mysql安装包，会提示安装mysql，点击下一步即可，我们这里应用的是5.0 windows32位的安装程序

file

2）点击 next 之后,会出现安装类型选项 一共有“Typical（默认）”、“Complete（完全）”、“Custom（用户自定义）”三个选项,我们选择Typical（默认）即可

file

3）是确定你的安装类型 和 安装位置 有的同学可能不想安装在C盘，其实大可以放心，重新安装系统之后数据库肯定也得重装，所以安装在C盘格式化的时候一波带走岂不美滋滋

file

4）正在安装中耐心等候

file

5）安装好了之后，会弹出一个mysql的广告，会告诉你mysql非常非常好用之类的，直接next即可

file

6）补充说明mysql在各个领域都好用，直接点next

file

7） 到此已经安装完成mysql的安装程序,然后出现这个页面，这个页面是告诉你马上要开始配置一个mysql数据库了，此处点击finsh完成mysql软件的安装

file

8）开始配置mysql的数据库

file

9）此处问你是要详细的配置一个mysql数据库(Detailed Configuration)还是标准化配置一个mysql数据库(StandardConfiguration)

作为一个mysql的专业从业人员来说，我们肯定是选择详细配置的

file

10）选择服务器类型，“Developer Machine（开发测试类，mysql占用很少资源）”、“Server Machine（服务器类型，mysql占用较多资源）”、

“Dedicated MySQL Server Machine（专门的数据库服务器，mysql占用所有可用资源）”，

大家根据自己的类型选择了，一般选“Server Machine”，不会太少，也不会占满。但是我们作为helloworld来说,开发环境就够用

file

11）选择mysql数据库的大致用途，

“Multifunctional Database（通用多功能型，好）”、

“Transactional Database Only（服务器类型，专注于事务处理，一般）”、

“Non-Transactional Database Only（非事务处理型，较简单，主要做一些监控、记数用），

随自己的用途而选择了，我这里选择“Multifunctional Database”，按“Next”继续。

file

关于Innodb数据库引擎,可参见https://baike.baidu.com/item/innodb/8970025?fr=aladdin

12）对InnoDB Tablespace进行配置，就是为InnoDB 数据库文件选择一个存储空间，此处不建议修改，否则可能会造成数据库损坏，

当然，对数据库做个备份就没问题了，这里不详述。我这里没有修改，使用用默认位置，直接按“Next”继续

还有要注意，此处真的不建议修改，修改了会导致你的数据库安装失败，其实装在C盘也没有什么，换句话说，你才能操作多少条数据呀，对不对，我们现在还是处于小数据的环境下，所以数据库的位置真的可以不用调整

file

13）选择您的网站的一般mysql访问量，同时连接的数目，

​ “Decision Support(DSS)/OLAP（20个左右）”、

​ “Online Transaction Processing(OLTP)（500个左右）”、

​ “Manual Setting（手动设置，自己输一个数）”，我这里选“Online Transaction Processing(OLTP)”，

​ 自己的服务器，应该够用了，按“Next”继续

file

14）是否启用TCP/IP连接，设定端口，如果不启用，就只能在自己的机器上访问mysql数据库了，

​ 我这里启用，把前面的勾打上，Port Number：3306，在这个页面上，您还可以选择“启用标准模式”（Enable Strict Mode），

​ 这样MySQL就不会允许细小的语法错误。如果您还是个新手，我建议您取消标准模式以减少麻烦。但熟悉MySQL以后，

​ 尽量使用标准模式，因为它可以降低有害数据进入数据库的可能性。还有就是后面的"Add firewall exception for this port",这句话是说将3306加入防火墙

​ 如果你是win10的系统或者你没有关闭防火墙,这步就一定要去掉那个勾,否则会导致你安装失败,一定要切记,按“Next”继续

file

15）这一步也很关键，设置字符集，

【Standard Character Set】默认字符集，支持的其它字符的很少；

【Best Support For Multilingualism】能支持大部分语系的字符，主要是以UTF-8的形式存储，所以一般选择这一项；

【Manual Selected Default Character Set/ Collation】，手动设置字符集

其实此处也可以手动设置为UTF-8 或者 GBK (当然需要看下你的eclipse的编码是GBK还是UTF-8,最后和eclipse保持一致)

file

16）设置Windows的操作，

​ 【Install AS Windows Service】，设置Service Name，一般默认的就可以，这个名字会出现在windows服务里。

​ 【Include Bin Directory in Windows PATH】，

​ 把Bin目录放在Windows Path路径中，建议选择上它，这样在Dos窗口里直接可以访问MySQL，不需要到MySQL的bin目录下进，方便，快速。按【Next】继续。

file

17）这一步询问是否要修改默认root用户（超级管理）的密码（默认为空），

​ “New root password”如果要修改，就在此填入新密码（如果是重装，并且之前已经设置了密码，

​ 在这里更改密码可能会出错，请留空，并将“Modify Security Settings”前面的勾去掉，安装配置完成后另行修改密码），

​ “Confirm（再输一遍）”内再填一次，防止输错。 “Enable root access from remote machines（是否允许root用户在其它的机器上登陆，如果要安全，就不要勾上，如果要方便，就勾上它）”。

​ 最后“Create An Anonymous Account（新建一个匿名用户，匿名用户可以连接数据不能操作数据，包括查询）”，一般就不用勾了，设置完毕，按“Next”继续。

file

18）确认设置无误，如果有误，按“Back”返回检查。按“Execute”使设置生效。

file

file

到此 mysql数据库软件和mysql数据库都已经安装到位了,也是美滋滋

安装后，MySQL会以windows服务的方式为我们提供数据存储功能。开启和关闭服务的操作：右键点击我的电脑→管理→服务→可以找到MySQL服务开启或停止。

file

file

file

如果出现这个问题，找到mysql的安装路径
修改my.ini的配置文件
修改INNODB的选项为MYISAM
然后重启服务

file

file

file

2.2 用控制台登录MySQL

1）配置MySQL安装目录配置到系统环境变量path中

file

2）登录MySQL数据库

MySQL是一个需要账户名密码登录的数据库，登陆后使用，它提供了一个默认的root账号，使用安装时设置的密码即可登录。

格式：mysql –u用户名 –p密码

2.3 用 Navicat 工具连接MySQL数据库

该工具是可视化操作MySQL数据库的第三方软件，使用很便捷。

2.3.1 安装 Navicat 工具

file

直接双击exe安装文件，傻瓜式安装即可。

破解参考 《安装步骤.txt》

安装的时候指定一个自己的目录，然后安装完毕以后找到这个目录
将payload.bin version.dll两个文件直接扔进去，替换掉原来的文件就可以了

2.3.2 用 Navicat 工具连接MySQL

打开工具进入主界面

点击：

file

弹出连接对话框

file

点击确定后进入数据库操作界面。

file

3 SQL语句

使用SQL语句，程序员和DBA（数据库管理员）可以完成如下任务：

  • 在数据库中检索信息。
  • 对数据库的信息进行更新。
  • 改变数据库的结构。
  • 更改系统的安全设置。
  • 增加用户对数据库或表的许可权限。

3.1 SQL语句分类

数据定义语言（DDL）：

​ 主要由create（创建库、表）、alter（修改结构）、drop（删除库、表） 和 truncate（摧毁重建） 四个关键字完成。

数据操作语言（DML）：

​ 分别用于添加、修改和删除表中的行。主要由insert（添加）、update（修改） 和 delete（删除） 三个关键字完成。

数据查询语言（DQL）：

​ 用来查询数据库中表的记录。

​ 主要由select关键字完成，查询语句是SQL语句中最复杂、功能最丰富的语句。

数据控制语言（DCL）：

​ 用来定义数据库的访问权限和安全级别，及创建用户。

​ 主要由 grant 和 revoke 两个关键字 完成。

事务控制语句（TCL）：

主要讲前三种。 主要由commit 、rollback 和 savepoint 三个关键字完成。

3.2 SQL通用语法

  • SQL语句可以单行或多行书写，以分号结尾
  • 可使用空格和缩进来增强语句的可读性
  • MySQL数据库的SQL语句不区分大小写(但是数据区分)
  • 同样可以使用//的方式完成注释，也可以用 -- 的形式完成注释

3.3 MySQL的数据类型

byte short int long float double boolean char

file
3 DDL操作

3.1 DDL操作之数据库

-- 创建数据库,数据库中数据的编码采用的是安装数据库时指定的默认编码 utf8
create database 数据库名;
-- 创建指定字符集编码的数据库
create database 数据库名 character set 字符集;

-- 查看数据库列表
show databases;

-- 使用数据库
use 数据库名;

-- 查看当前使用数据库
select database();

-- 删除数据库
drop database 数据库名称;

代码示例：

-- 创建hainiudb数据库
create database hainiudb;
-- 创建utf8字符集编码的数据库
create database testdb  character set utf8;

-- 查看数据库列表
show databases;

-- 使用hainiudb数据库
use hainiudb;

-- 查看当前使用数据库
select database();

-- 删除testdb数据库
drop database testdb;

3.2 DDL操作之数据表

3.2.1 创建表

建表格式：

create table 表名 (
    --可以有多个列 定义
    字段名1 数据类型 [约束][缺省值][描述],
    ...
    字段名N 数据类型 [约束][缺省值][描述],
    ...
);

举例 : 比如对京东商城进行数据库表分析

file

从上图我们可以知道京东的商品是先分成大类，然后大类中再分成小类小类中再保存具体商品

file

那么接下来我们也按照这种思路来进行表的学习

创建一张商品类别表

-- 使用hainiudb数据库
use hainiudb;

-- 创建商品类别表
-- 表内有 类别id， 类别名称字段
CREATE TABLE `category` (
  `cid` varchar(32) NOT NULL,
  `cname` varchar(50),
  PRIMARY KEY (`cid`)
);
-- 其中： cid 是主键， 通过 在后面标记字段是 PRIMARY KEY (`cid`)，主键可以唯一定义一行记录

3.2.2 查看表

-- 查看表列表
show tables;

-- 查看表结构
desc category;

file

3.2.3 修改表

1）**修改表添加列**

语法：alter table 表名 add 列名 类型(长度) [约束]; 

-- 为商品类别表添加一个新的字段为 分类描述 varchar(10)
-- 当添加列后，这个列成为表的最后的字段
alter table category add `desc` varchar(10);

2）**修改表中列的类型长度及约束**

语法：alter table 表名 modify 列名 类型(长度) 约束;

--为商品类别表的描述字段进行修改，类型varchar(30) 添加约束 not null(该字段的值不能为null)
 alter table category modify `desc` varchar(30) not null;

注意：在使用过程中要考虑表中已经存储的数据，所以只会修改字段类型长度扩容。

3）修改表中列名

语法：alter table 表名 change 旧列名 新列名 类型(长度) 约束;

-- 为商品类别表的分类名称字段进行更换 更换为 cdesc varchar(30)
ALTER TABLE `category` CHANGE `desc` `cdesc`  VARCHAR(30);

4）修改表删除列

语法：alter table 表名 drop 列名;

--  删除商品分类表中cdesc这列
alter table category drop cdesc;

5）修改表名

语法：rename table 表名 to 新表名;

-- 为分类表category 改名成 product_type
rename table category to product_type;

3.2.4 删除表

是把表结构和表数据都删除

格式：drop table 表名;

-- 删除表
drop table product_type;

4 DML操作

4.1 插入表记录——insert

语法：

-- 向表中插入某些字段
insert into 表 (字段1,字段2,字段3..) values  (值1,值2,值3..);

--向表中插入所有字段,字段的顺序为创建表时的顺序
insert into 表 values (值1,值2,值3..);

注意：

​ 值与字段必须对应，个数相同，类型相同

​ 值的数据大小必须在字段的长度范围内

​ 除了数值类型外，其它的字段类型的值必须使用引号引起。（建议单引号）

​ 如果要插入空值，可以不写字段，或者插入 null。

示例：

1）重新创建商品类别表

-- 创建商品类别表
-- 表内有 类别id， 类别名称、描述 三个字段
CREATE TABLE `category` (
  `cid` varchar(32) NOT NULL,
  `cname` varchar(50),
  PRIMARY KEY (`cid`)
);

2）给表添加数据

-- 添加数据
-- 最标准写法(列与值是一一对应的)
insert into category (cid,cname) values (1,'家用电器');

-- 简易写法(值得顺序必须和列循序一致)
insert into category values (2,'手机/运营商/数码');

-- 便捷写法(一次性插入多条语句可以共享前置语法)
insert into category values (3,'电脑/办公'),(4,'家居/家具/家装/厨具'),(5,'男装/女装/童装/内衣');

4.2 修改表记录——update

用来修改指定条件的数据，将满足条件的记录指定列修改为指定值

语法：

--更新所有记录的指定字段（一般不这么用）
update 表名 set 字段名=值,字段名=值,...;

--更新符合条件记录的指定字段（常用）
update 表名 set 字段名=值,字段名=值,... where 条件;

注意：

​ 列名的类型与修改的值要一致.

​ 修改值得时候不能超过最大长度.

​ 除了数值类型外，其它的字段类型的值必须使用倒引号引起

​ 更新时要加一定的筛选条件。

示例：

-- 将家用电器更新为 美妆/个护清洁/宠物
update category set cname = '美妆/个护清洁/宠物' where cname = '家用电器';
update tablename set column = int/varchar/decimal/double// where column = xxx
-- 将ID为5的数据更新为 男鞋/运动/户外
update category set cname = '男鞋/运动/户外' where cid = 5;

4.3 删除表记录——delete

1）**如果删除表中的部分数据**

此种方式只是删除表中的数据

语法：delete from 表名 where 条件;

2）如果删除表中所有数据

语法：delete from 表名;

或者

truncate table 表名;

3）delete vs truncate

delete：一条一条删除表中的数据，如果表中数据非常多，那这个执行很慢。

truncate：先把表删除，再创建一个空表，也相当于删除了表数据。

在数据非常多的情况下，要删除表所有数据，建议用truncate。

示例：

-- 删除ID为1的数据
delete from category where cid = 1;

-- 删除商品类别表的所有数据
delete from category;

-- 摧毁商品类别表的表结构然后重新创建
truncate table category;

5 SQL约束

常用约束如下：

NOT NULL：非空约束，指定某列不能为空。

UNIQUE：唯一约束，指定某列或某几列组合不能重复。

PRIMARY KEY：主键，指定该列的值可以唯一地标识该条记录。

FOREIGN KEY：外键，指定该行记录从属于主表中的一条记录，主要用于保证参照完整性。

5.1 主键约束

PRIMARY KEY 约束唯一标识数据库表中的每条记录。

主键必须 唯一、非空。

每个表都应该有一个主键，并且每个表只能有一个主键。

作用:

​ 1）保证实体的完整性;

​ 2）加快数据库的操作速度

​ 3） 在表中添加新记录时，DBMS会自动检查新记录的主键值，不允许该值与其他记录的主键值重复。

​ 4) DBMS自动按主键值的顺序显示表中的记录。如果没有定义主键，则按输入记录的顺序显示表中的记录。

示例：

-- 建表时指定主键
create table persons2(
pid int,
pname varchar(30),
sex VARCHAR(1),
age int,
pfrom varchar(50),
primary key (pid)z
);

5.2 自动增长列

我们通常希望在每次插入新记录时，数据库自动生成字段的值。

我们可以在表中使用 auto_increment（自动增长列）关键字，自动增长列类型必须是整形，自动增长列必须为键(一般是主键)。

示例：

-- 把 "persons" 表中的 "pid" 列定义为 auto_increment 主键
create table persons(
pid int AUTO_INCREMENT,
pname varchar(30),
sex VARCHAR(1),
age int,
pfrom varchar(50),
primary key (pid)
);

创建完后，每添加表记录，pid 就会自动增长

添加记录示例：

-- 向persons添加数据时，可以不为pId字段设置值，也可以设置成null，数据库将自动维护主键值：
-- 标准写法
insert into persons (pname,sex,age,pfrom) values ('苏磊','男',35,'北京市');

-- 简易写法
insert into persons values (null,'刘鑫','男',24,'河北省');

插入后查询结果：

file

扩展：默认AUTO_INCREMENT 的开始值是 1，如果希望修改起始值，请使用下列 SQL 语法： 

查询当前自增id
select auto_increment from information_schema.tables where table_schema='myDB' and table_name='Specs';
修改表的自增id
alter table demo.user auto_increment=10;

5.3 非空约束

字段不能为空。

示例：

-- 删除 之前的persons表
drop table persons;

-- 创建带有非空约束的表
create table persons(
pid int AUTO_INCREMENT,
pname varchar(30) not null,
sex VARCHAR(1) not null,
age int not null,
pfrom varchar(50) not null,
primary key (pid)
);

此时添加数据，标记 非空的字段，必须给值，否则约束不通过。

insert into persons (pname,sex,age) values ('苏磊','男',35);

file

5.4 默认值

default：当设置约束后，插入数据时如果不想给值，可以设置默认值。

当插入时，发现你没给值，可以使用默认值填充。

-- 删除 之前的persons表
drop table persons;

-- 创建约束的表
create table persons(
pid int AUTO_INCREMENT,
pname varchar(30) not null,
sex VARCHAR(1) not null,
age int not null,
pfrom varchar(50) not null default '',
primary key (pid)
);

插入数据，可以不给 pfrom 赋值

insert into persons (pname,sex,age) values ('苏磊','男',35);

file

5.5 唯一约束

unique：字段数据必须唯一不重复、字段可以为NULL。

示例：

-- 删除 之前的persons表
drop table persons;

-- 创建约束的表， pname: 唯一约束
create table persons(
pid int AUTO_INCREMENT,
pname varchar(30) unique,
sex VARCHAR(1) not null,
age int not null,
pfrom varchar(50) not null default '',
primary key (pid)
);

插入重复值违反唯一约束

-- 第一个正常执行
insert into persons (pname,sex,age) values ('苏磊','男',35);
-- 第二个报错
insert into persons (pname,sex,age) values ('苏磊','男',35);

file

版权声明:原创作品,允许转载，转载时务必以超链接的形式表明出处和作者信息。否则将追究法律责任。来自海汼部落－阿布都的都,http://hainiubl.com/topics/76005
点赞
成为第一个点赞的人吧 :bowtie:
回复数量: 0
    暂无评论~~
    • 请注意单词拼写，以及中英文排版，参考此页
    • 支持 Markdown 格式, **粗体**、~~删除线~~、`单行代码`, 更多语法请见这里 Markdown 语法
    • 支持表情，可用Emoji的自动补全, 在输入的时候只需要 ":" 就可以自动提示了 :metal: :point_right: 表情列表 :star: :sparkles:
    • 上传图片, 支持拖拽和剪切板黏贴上传, 格式限制 - jpg, png, gif，教程
    • 发布框支持本地存储功能，会在内容变更时保存，「提交」按钮点击时清空
    Ctrl+Enter
    新 建 话 题

    作者：阿布都的都

    阿布都的都 的其他话题

    分类下其他主题

    随机推荐话题

    QQ群1：551936262 海汼部落QQ群1

    关注海汼部落技术社区