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

程序员文章站 2022-05-27 17:02:01

...

约束

* 约束是添加在列上的，用来约束列的！

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 employees;
  }
 
 class Husband {
    ...
    private Wife wife;
  }
 class Wife {
    ...
    private Husband
  }
 
 class Student {
    ...
    private List teachers
  }
  classTeacher {
    ...
    private List 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

连接查询

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}

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

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

约束

1. 主键约束（唯一标识）

2. 主键自增长

3. 非空约束

4. 唯一约束

5. 概念模型

6. 外键约束

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

数据库一对一关系

数据库多对多关系

编码

1. 查看MySQL数据库编码

2. 编码解释

3. 控制台乱码问题

4. 指定默认编码

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

备份与恢复

多表查询

分类

合并结果集

连接查询

2. 内连接

3. 外连接

子查询

数据库技术：MySQL 多表，外键约束，数据库设计，索引，视图，存储过程，触发器，数据控制，数据备份与恢复

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

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