讲师中心 微信公众号

工具推荐

视频效率加速
立刻MV
立刻MV

低成本歌曲 MV、视频生成

Seko
Seko

AI短剧一键生成成片

剧云
剧云

中文短剧剧本制作平台

SoundView
SoundView

字幕擦除翻译、配音口型同步

webday16数据库完整性约束,mySQL编码问题,备份与恢复,多表查询_MySQL

陌萱酱_1610

陌萱酱_1610

发布时间:2016-05-27 13:45:05

|

1273人浏览过

|

来源于php中文网

原创

约束

* 约束是添加在列上的,用来约束列的!

1. 主键约束(唯一标识)

****非空***

****唯一***

****被引用****

* 当表的某一列被指定为主键后,该列就不能为空,不能有重复值出现。

* 创建表时指定主键的两种方式:

>

CREATE TABLE stu(

sid CHAR(6) PRIMARY KEY,

sname VARCHAR(20),

age INT,

gender VARCHAR(10)

);

指定sid列为主键列,即为sid列添加主键约束

>

CREATE TABLE stu(

sid CHAR(6),

sname VARCHAR(20),

age INT,

gender VARCHAR(10),

PRIMARYKEY(sid)

);

指定sid列为主键列,即为sid列添加主键约束

* 修改表时指定主键:ALTER TABLE stu ADD PRIMARYKEY(sid);

* 删除主键:ALTER TABLE stu DROP PRIMARYKEY;

2. 主键自增长

* 因为主键列的特性是:必须唯一、不能为空,所以我们通常会指定主键类为整型,然后设置其自动增长,这样可以保证在插入数据时主键列的唯一和非空特性。

* 创建表时指定主键自增长

CREATE TABLE stu(

sidINT PRIMARY KEY AUTO_INCREMENT,

sname VARCHAR(20),

age INT,

gender VARCHAR(10)

);

* 修改表时设置主键自增长:ALTER TABLE stu CHANGEsid sid INT AUTO_INCREMENT;

* 修改表时删除主键自增长:ALTER TABLE stu CHANGEsid sid INT;

* 测试主键自增长:

> INSERT INTO stu VALUES(NULL, 'zhangSan',23,'male');

> INSERT INTO stu(sname,age,gender) VALUES('zhangSan',23,'male');

3. 非空约束

* 因为某些列不能设置为NULL值,所以可以对列添加非空约束。

* 例如:

CREATE TABLE stu(

sidINT PRIMARY KEY AUTO_INCREMENT,

sname VARCHAR(20) NOT NULL,

age INT,

gender VARCHAR(10)

);

* 对sname列设置了非空约束

4. 唯一约束

* 车库某些列不能设置重复的值,所以可以对列添加唯一约束。

* 例如:

CREATE TABLE stu(

sidINT PRIMARY KEY AUTO_INCREMENT,

sname VARCHAR(20) NOT NULL UNIQUE,

age INT,

gender VARCHAR(10)

);

* 对sname列设置了唯一约束

5. 概念模型

对象模型:可以双向关联,而且引用的是对象,而不是一个主键!

关系模型:只能多方引用一方,而且引用的只是主键,而不是一整行记录。

对象模型:在java中是domain!!!例如:User、Student

is a

has a(关联)

> 1对1

> 1对多

> 多对多

use a

关系模型:在数据库中表!!!

当我们要完成一个软件系统时,需要把系统中的实体抽取出来,形成概念模型。

例如部门、员工都是系统中的实体。概念模型中的实体最终会成为Java中的类、数据库中表。

实体之间还存在着关系,关系有三种:

* 1对多:例如每个员工都从属一个部门,而一个部门可以有多个员工,其中员工是多方,而部门是一方。

* 1对1:例如老公和老婆就是一对一的关系,一个老公只能有一个老婆,而一个老婆只能有一个老公。

* 多对多:老师与学生的关系就是多对多,一个老师可以有多个学生,一个学生可以有多个老师。

概念模型在Java中成为实体类(javaBean)

类就使用成员变量来完成关系,一般都是双向关联!

多对一双向中关联,即员工关联部门,部门也关联员工

 class Employee {//多方关联一方
    ...
    private Department department;
  }
  class Department {//一方关联多方
    ...
    private List<Employee> employees;
  }
 
 class Husband {
    ...
    private Wife wife;
  }
 class Wife {
    ...
    private Husband
  }
 
 class Student {
    ...
    private List<Teacher> teachers
  }
  classTeacher {
    ...
    private List<Student> students;
  }


6. 外键约束

* 外键必须是另一表的主键的值(外键要引用主键!)

* 外键可以重复

* 外键可以为空

* 一张表中可以有多个外键!

概念模型在数据库中成为表

数据库表中的多对一关系,只需要在多方使用一个独立的列来引用1方的主键即可

/*员工表*/

create talbe emp (

empno int primary key,/*员工编号*/

...

deptno int/*所属部门的编号*/

);

/*部门表*/

create table dept (

deptno int primary key,/*部门编号*/

...

);

emp表中的deptno列的值表示当前员工所从属的部门编号。也就是说emp.deptno必须在dept表中是真实存在!

但是我们必须要去对它进行约束,不然可能会出现员工所属的部门编号是不存在的。这种约束就是外键约束。

我们需要给emp.deptno添加外键约束,约束它的值必须在dept.deptno中存在。外键必须是另一个表的主键!

语法:CONSTRAINT 约束名称 FOREIGN KEY(外键列名) REFERENCES 关联表(关联表的主键)

创建表时指定外键约束

create talbe emp (

empno int primary key,

...

deptno int,

CONSTRAINT fk_emp FOREIGN KEY(mgr) REFERENCES emp(empno)

);

修改表时添加外键约束

ALERT TABLE emp

ADDCONSTRAINT fk_emp_deptno FOREIGN KEY(deptno) REFERENCES dept(deptno);

修改表时删除外键约束

ALTER TABLE emp

DROP FOREIGN KEY fk_emp_deptno;/*约束名称*/

--------------------------

7数据库表与表之间的关系

一对一:

例如t_person表和t_card表,即人和身份证。这种情况需要找出主从关系,即谁是主表,谁是从表。人可以没有身份证,但身份证必须要有人才行,所以人是主表,而身份证是从表。

设计从表可以有两种方案:

在t_card表中添加外键列(相对t_user表),并且给外键添加唯一约束;

给t_card表的主键添加外键约束(相对t_user表),即t_card表的主键也是外键。

一对多(多对一):

最为常见的就是一对多!一对多和多对一,这是从哪个角度去看得出来的。t_user和t_section的关系,从t_user来看就是一对多,而从t_section的角度来看就是多对一!这种情况都是在多方创建外键!

多对多:

例如t_stu和t_teacher表,即一个学生可以有多个老师,而一个老师也可以有多个学生。这种情况通常需要创建中间表来处理多对多关系。例如再创建一张表t_stu_tea表,给出两个外键,一个相对t_stu表的外键,另一个相对t_teacher表的外键。

数据库一对一关系

在表中建立一对一关系比较特殊,需要让其中一张表的主键,即是主键又是外键。

create table husband(

hid int PRIMARY KEY,

...

);

create table wife(

wid int PRIMARY KEY,

...

ADD CONSTRAINT fk_wife_wid FOREIGN KEY(wid) REFERENCES husband(hid)

);

其中wife表的wid即是主键,又是相对husband表的外键!

husband.hid是主键,不能重复!

wife.wid是主键,不能重复,又是外键,必须来自husband.hid。

所以如果在wife表中有一条记录的wid为1,那么wife表中的其他记录的wid就不能再是1了,因为它是主键。

同时在husband.hid中必须存在1这个值,因为wid是外键。这就完成了一对一关系。

*****从表的主键即是外键!


数据库多对多关系

在表中建立多对多关系需要使用中间表,即需要三张表,在中间表中使用两个外键,分别引用其他两个表的主键。

create table student(

sid int PRIMARY KEY,

...

);

create table teacher(

tid int PRIMARY KEY,

...

);

create table stu_tea(

sid int,

tid int,

ADD CONSTRAINT fk_stu_tea_sid FOREIGN KEY(sid) REFERENCES student(sid),

ADD CONSTRAINT fk_stu_tea_tid FOREIGN KEY(tid) REFERENCES teacher(tid)

);

这时在stu_tea这个中间表中的每条记录都是来说明student和teacher表的关系

例如在stu_tea表中的记录:sid为1001,tid为2001,这说明编号为1001的学生有一个编号为2001的老师

sid tid

101 201 /*编号为101的学生有一个编号为201的老师*/

101 202 /*编号为101的学生有一个编号为202的老师*/

101 203 /*编号为101的学生有一个编号为203的老师*/

102 201 /*编号为102的学生有一个编号为201的老师*/

102 204 /*编号为102的学生有一个编号为204的老师*/

-----------------------

编码

1. 查看MySQL数据库编码

*SHOW VARIABLES LIKE 'char%';

2. 编码解释

*character_set_client:MySQL使用该编码来解读客户端发送过来的数据,例如该编码为UTF8,那么如果客户端发送过来的数据不是UTF8,那么就会出现乱码

*character_set_results:MySQL会把数据转换成该编码后,再发送给客户端,例如该编码为UTF8,那么如果客户端不使用UTF8来解读,那么就会出现乱码

其它编码只要支持中文即可,也就是说不能使用latin1

3. 控制台乱码问题

* 插入或修改时出现乱码:

> 这时因为cmd下默认使用GBK,而character_set_client不是GBK的原因。我们只需让这两个编码相同即可。

> 因为修改cmd的编码不方便,所以我们去设置character_set_client为GBK即可。

* 查询出的数据为乱码:

> 这是因为character_set_results不是GBK,而cmd默认使用GBK的原因。我们只需让这两个编码相同即可。

> 因为修改cmd的编码不方便,所以我们去设置character_set_results为GBK即可。

* 设置变量的语句:

> set character_set_client=gbk;

> set character_set_results=gbk;

注意,设置变量只对当前连接有效,当退出窗口后,再次登录mysql,还需要再次设置变量。

为了一劳永逸,可以在my.ini中设置:

设置default-character-set=gbk即可。

 

4. 指定默认编码

我们在安装MySQL时已经指定了默认编码为UTF8,所以我们在创建数据库、创建表时,都无需再次指定编码。

为了一劳永逸,可以在my.ini中设置:

设置character-set-server=utf8即可。

character_set_client | utf8 --> mysql把我们客户端传递的数据都当成是utf8!一是给它传递utf8,二是如果我们传递的是gbk,那么需要修改这个变量为gbk

character_set_connection | utf8

character_set_database | utf8

character_set_results | utf8 --> mysql发送给客户端的数据都是utf8的。一是客户端用utf8编码,二是如果客户端使用gbk来编码,那么需要修改这个变量为gbk的。

character_set_server | utf8

character_set_system | utf8

----------------------------

character_set_client=utf8,无论客户端发送的是什么编码的数据,mysql都当成是utf8的数据!

> 若客户端发送的是GBK

> 服务器会当成utf8对待

> 总结:必然乱码!

处理问题的手段有两种;

> 让客户端发送utf8的数据(行不通)

> 把character_set_client修改为gbk

setcharacter_set_client=gbk; --> 只在当前窗口内有效,也就是说,关闭窗口后,再打开,又回到utf8了。

character_set_results=utf8,把数据用什么编码发送给客户端!

> 若服务器发送给客户端的是utf8的数据

> 客户端会把它当成gbk,因为我们的小黑屏,只能显示gbk

> 总结:必然乱码!

处理问题的手段有两种:

> 让服务器发送gbk的数据:set character_set_results=gbk

> 让小黑屏使用utf8来解读(行不通)

my.ini

在总配置文件中进行配置,可以一劳永逸

[client]

port=3306

[mysql]

default-character-set=gbk /*它可以一劳永逸!它可以修改三个变量:client、results、connection*/


-------------------------

备份与恢复

数据库 --> sql语句

sql语句 --> 数据库

1. 数据库导出SQL脚本(备份数据库内容,并不是备份数据库!)

> mysqldump –u用户名–p密码 数据库名>生成的脚本文件路径

> 例如:mysqldump -uroot-p123 mydb1>C:\mydb1.sql (与mysql.exe和mysqld.exe一样, 都在bin目录下)

> 注意,不要打分号,不要登录mysql,直接在cmd下运行

> 注意,生成的脚本文件中不包含createdatabase语句

2. 执行SQL脚本

第一种方式

> mysql -u用户名 -p密码数据库

> 例如:

*先删除mydb1库,再重新创建mydb1库

*mysql -uroot -p123 mydb1

> 注意,不要打分号,不要登录mysql,直接在cmd下运行

第二种方式

> 登录mysql

> source SQL脚本路径

> 例如:

*先删除mydb1库,再重新创建mydb1库

*切换到mydb1库

*source c:\mydb1.sql

---------------------------------

数据库 --> sql:备份

sql --> 数据库:恢复

------------------

mysqldump -uroot -p123 mydb3>c:/a.sql--> 备份

mysql -uroot -p123 mydb3恢复

source c:/a.sql --> 恢复

---------------------------------

多表查询

关键字执行顺序

select *

from emp, dept, (select * from emp)

where

group by

having

order by

limit

分类

*合并结果集(了解)

*连接查询

*子查询

合并结果集

* 要求被合并的表中,列的类型和列数相同

*UNION,去除重复行

*UNION ALL,不去除重复行

SELECT * FROM cd

UNION ALL

SELECT * FROM ab;

连接查询

1. 分类

*内连接

*外连接

> 左外连接

> 右外连接

> 全外连接(MySQL不支持)

*自然连接(属于一种简化方式)

2. 内连接

*方言:SELECT * FROM 表1 别名1, 表2 别名2 WHERE 别名1.xx=别名2.xx

*标准:SELECT * FROM 表1 别名1 INNER JOIN 表2 别名2 ON 别名1.xx=别名2.xx

*自然:SELECT * FROM 表1 别名1 NATURAL JOIN 表2 别名2

*内连接查询出的所有记录都满足条件。

3. 外连接

*左外:SELECT * FROM 表1 别名1 LEFT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx

> 左表记录无论是否满足条件都会查询出来,而右表只有满足条件才能出来。左表中不满足条件的记录,右表部分都为NULL

*左外自然:SELECT * FROM 表1 别名1 NATURAL LEFT OUTERJOIN 表2 别名2 ON 别名1.xx=别名2.xx

*右外:SELECT * FROM 表1 别名1 RIGHT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx

> 右表记录无论是否满足条件都会查询出来,而左表只有满足条件才能出来。右表不满足条件的记录,其左表部分都为NULL

*右外自然:SELECT * FROM 表1 别名1 NATURAL RIGHT OUTERJOIN 表2 别名2 ON 别名1.xx=别名2.xx

*全链接:可以使用UNION来完成全链接

子查询

  :查询中有查询(查看select关键字的个数!)

1. 出现的位置:

*where后作为条件存在

*from后作为表存在(多行多列)

2. 条件

*(***)单行单列:SELECT * FROM 表1 别名1 WHERE 列1 [=、>、=、

*(**)多行单列:SELECT * FROM 表1 别名1 WHERE 列1 [IN, ALL, ANY] (SELECT 列 FROM 表2 别名2 WHERE 条件)

*(*)单行多列:SELECT * FROM 表1 别名1 WHERE (列1,列2) IN (SELECT 列1, 列2 FROM 表2 别名2 WHERE 条件)

*(***)多行多列:SELECT * FROM 表1 别名1 , (SELECT ....) 别名2 WHERE 条件

====================================================

笛卡尔积

{a, b, c} {1,2}

{a1, a2, b1, b2, c1, c2}

====================================================




热门AI工具

更多
讯飞绘文

讯飞绘文是一款由科大讯飞推出的一站式 AIGC 内容运营平台。

创客贴AI

创客贴AI是一款AI图像与设计工具,AI辅助的智能在线设计工具。

PixTV
PixTV Hot

PixTV是一款面向AIGC内容创作的AI视频生成工具。

豆包大模型

豆包大模型是一款由字节跳动推出的企业级大语言模型服务平台。

WorkBuddy

一款AI办公效率工具,主要用于腾讯云推出的AI原生桌面智能体工作台,适合需要提升相关任务效率的用户。

DeepSeek

DeepSeek是一款面向对话、写作、编程和推理场景的AI大模型工具。

火山引擎

火山引擎是一款面向企业的云计算与AI服务平台。

SoundView
SoundView Hot

SoundView是一款面向跨境电商的视频翻译、配音和本地化 AI 创作平台。

UpDream
UpDream Hot

一款AI视频创作工具,主要用于哔哩哔哩推出的自研AI视频创作工具,适合需要提升相关任务效率的用户。

相关专题

更多
Go-Zero API开发使用教程
Go-Zero API开发使用教程

本专题聚焦Go-Zero的API开发,拆解.api描述文件的语法结构、路由与分组定义、参数绑定与校验规则,说明统一响应体、JWT鉴权、跨域与接口文档生成的落地方式,并给出网关聚合与接口测试的实践建议。

160

2026.11.12

Go-Zero安装与goctl环境配置教程
Go-Zero安装与goctl环境配置教程

本专题围绕Go-Zero的安装与工具链配置展开,讲清Go版本要求、goctl安装方式、代理镜像、protoc依赖与环境变量设置,说明多版本共存、升级回退与CI流水线安装的做法,并整理安装阶段的常见报错与验证步骤。

100

2026.11.12

Go-Zero零基础入门教程
Go-Zero零基础入门教程

本专题整理Go-Zero的入门知识,从框架定位、环境准备到跑通第一个API服务,讲清目录分层、配置加载、日志与中间件的基本用法,并对比Gin、Kratos的差异,帮助Go开发者建立整体认知,判断它是否适合当前项目。

100

2026.11.12

Postman身份认证怎么配置
Postman身份认证怎么配置

本专题从实际API接口调试场景出发,讲解Postman身份认证的完整配置流程,包括常见认证协议介绍、授权参数设置、访问令牌填写、OAuth 2.0授权及认证失败排查,帮助用户正确完成接口授权与访问验证。

180

2026.11.06

Postman请求头Headers怎么设置
Postman请求头Headers怎么设置

围绕Postman HTTP请求头配置展开,讲解Headers选项的具体使用方式,包括Key和Value参数填写、请求头信息添加、常见Header字段设置及请求头修改方法,帮助用户解决接口调试过程中遇到的请求头配置问题。

140

2026.11.06

Postman接口请求发送教程
Postman接口请求发送教程

详细介绍Postman接口请求的创建与发送方法,涵盖GET和POST请求、URL配置、请求参数、Headers请求头、Body请求体设置以及响应结果查看,帮助用户掌握Postman接口请求的完整操作流程。

120

2026.11.06

DBeaver怎么导入数据
DBeaver怎么导入数据

DBeaver数据导入专题,整理Excel、CSV、SQL、TXT等常见文件的数据导入方法,涵盖目标数据库选择、数据表创建、字段映射、数据类型设置及导入参数配置等操作,帮助用户将本地文件中的数据写入数据库,并了解批量导入、重复数据处理和导入失败等常见问题的解决方法。

120

2026.11.05

DBeaver安全防护教程大全
DBeaver安全防护教程大全

DBeaver安全防护专题,围绕数据库连接安全、账号密码保护和访问权限管理展开,整理SSL/TLS加密连接、SSH隧道、数据库凭据保存及证书验证等配置方法,同时介绍敏感信息保护、远程连接风险和高权限账号使用等注意事项,帮助用户提高数据库日常管理的安全性。

100

2026.11.05

DBeaver连接创建与配置教程
DBeaver连接创建与配置教程

本专题聚焦DBeaver数据库连接配置与管理,覆盖从新建连接、选择数据库类型到填写连接参数和验证连接的完整流程。针对MySQL、PostgreSQL、SQL Server等常见数据库,整理相应的连接设置方法,并提供驱动缺失、认证失败和连接异常等问题的排查方向。

120

2026.11.04

热门下载

更多
网站特效
/
网站源码
/
网站素材
/
前端模板

精品课程

更多
相关推荐
/
热门推荐
/
最新课程
DBeaver数据传输指南
DBeaver数据传输指南

共0课时 | 0人学习

Docker Desktop故障排查手册
Docker Desktop故障排查手册

共0课时 | 0人学习

关于我们 免责申明 举报中心 意见反馈 讲师合作 广告合作 最新更新
php中文网:公益在线php培训,帮助PHP学习者快速成长!
关注服务号
PHP中文网订阅号
每天精选资源文章推送

Copyright 2014-2026 https://www.php.cn/ All Rights Reserved | php.cn