MySQL总论

1. MySQL基本概念

1.1.  数据库的概念

数据库，简而言之就是存储数据的仓库，可以按照一定的数据结构存储管理大量的数据及数据与数据之间的关系，它本质上是一种信息管理系统。数据库根据存储采用的数据结构的不同可以分为许多种，其中常见的有层次式数据库、网络式数据库、关系型数据库。其中关系型数据库占据着市场的主流。

1.2.  关系型数据库

关系型数据库是建立在关系模型基础上的数据库。这种定义听起来十分抽象，这里我们不深入讨论什么叫做“关系模型”--大学计算机专业专门有一门课叫“离散数学”专门讨论过关系模型 --，只是简单的表述为 利用表来存储数据，用表和表之间的关系保存数据之间的关系的数据库称为关系型数据库，这个定义不太严谨，但是更好理解。

1.3.  常见的关系型数据库

商业数据库：

Oracle -- 甲骨文公司开发，市场占有率第一

SQLServer -- 微软公司开发，.Net技术中常用

DB2 -- IBM公司开发

Sybase -- Sybase公司开发

开源数据库：

MySql -- 瑞典MySql AB研发，非常受欢迎，已经被Oracle收购

SQLite -- 小型轻量级开源关系型数据库，常用作嵌入式设备数据库

1.4.  MySql数据库的安装配置

1.4.1.MySQL安装配置

安装配置步骤见附加文档

1.4.2.MySQL安装目录

MySQL的数据存储目录为data，data目录通常在\MySQLServer5.1\data位置。在data下的每个目录都代表一个数据库。

MySQL的安装目录下：

    bin目录中都是可执行文件；

    my.ini文件是MySQL的配置文件；

1.5.  基本命令

1.5.1.启动和关闭mysql服务器

启动：net start mysql；

关闭：net stop mysql；

在启动mysql服务后，打开windows任务管理器，会有一个名为mysqld.exe的进程运行，所以mysqld.exe才是MySQL服务器程序。

1.5.2.客户端登录退出mysql

在启动MySQL服务器后，我们需要使用管理员用户登录MySQL服务器，然后来对服务器进行操作。登录MySQL需要使用MySQL的客户端程序：mysql.exe

登录：mysql -u root -p 123 -h localhost；

-u：后面的root是用户名，这里使用的是超级管理员root；

-p：后面的123是密码，这是在安装MySQL时就已经指定的密码；

-h：后面给出的localhost是服务器主机名，它是可以省略的，例如：mysql -u root -p 123；

退出：quit或exit；

在登录成功后，打开windows任务管理器，会有一个名为mysql.exe的进程运行，所以mysql.exe是客户端程序。

2. MySql常见概念

2.1.  数据库服务器

指的就是一个数据库管理程序，这个程序可以管理多个数据库。

2.2.  数据库

一个数据库服务器可以管理多个数据库，可以认为是一个数据的“仓库”，一般一个web应用对应一个数据库。

2.3.  表

在保存应用实体数据时，通常会在数据库中创建多个表，以保存应用程序实体数据。一般来说应用程序中的一个javabean类可以对应一张数据库表。

2.4.  表记录

表中以一行记录保存一条信息，这样的一条记录称为表记录。可以认为一个javabean对象对应一条表记录。

3. SQL语言

3.1.  基本概念

3.1.1.SQL

SQL--Structured Query Language, 结构化查询语言,是关系型数据库通用的操作语言。

是一种非过程性语言。

由美国国家标准局(ANSI）与国际标准化组织（ISO）制定SQL标准。各大数据库厂商都对其做了实现。所以我们只要学会了SQL语言，就可以操作各大关系型数据库了。

为加强SQL的语言能力，各厂商增强了过程性语言的特征,增加了一些非标准的SQL，这样的SQL称为该数据库的“方言”。

SQL是用来存取关系数据库的语言，具有查询、操纵、定义和控制关系型数据库的四方面功能

3.2.  操作数据库

3.2.1.创建数据库

语法：

CREATE  DATABASE  [IF NOT EXISTS]  db_name  [create_specification [, create_specification] ...]

create_specification:

[DEFAULT] CHARACTER SET charset_name  |  [DEFAULT] COLLATE collation_name

其中charset_name是为数据库指定的默认字符集

Collate是为数据库指定的默认校对规则

校对规则是在字符集内用于比较字符的一套规则,可以控制select查询时where条件大小写是否敏感的规则。

练习：

~创建一个名称为mydb1的数据库。

    CREATE DATABASE mydb1;

~创建一个使用gbk字符集的mydb2数据库。

    CREATE DATABASE mydb2 CHARACTER SET gbk;

~创建一个使用utf8字符集，并带校对规则的mydb3数据库。

    CREATE DATABASE mydb3 CHARACTER SET utf8 COLLATE utf8_bin;

3.2.2.查看数据库

语法：

显示所有数据库：

SHOW DATABASES

显示数据库创建语句：

 SHOW CREATE DATABASE db_name

练习：

~查看当前数据库服务器中的所有数据库

   SHOW DATABASES;

~查看前面创建的mydb2数据库的定义信息

   SHOW CREATE DATABASE mydb2;

3.2.3.修改数据库

语法：

ALTER DATABASE db_name  [alter_specification [, alter_specification] ...]

alter_specification:   

[DEFAULT] CHARACTER SET charset_name  | [DEFAULT] COLLATE collation_name

练习：

~查看服务器中的数据库，并把mydb2库的字符集修改为utf8

   ALTER DATABASE mydb2 CHARACTER SET utf8;

3.2.4.删除数据库

语法：

DROP DATABASE  [IF EXISTS]  db_name

练习：

~删除前面创建的mydb1数据库

   DROP DATABASE mydb1;

3.2.5.选择数据库

语法：

选择数据库:use db_name;

查询当前选择的数据: select database();

没有退出数据库的命令,如果想退出当前数据库进入另一个数据,直接use 另一个数据库 就可以了

3.3.  操作表

3.3.1.SQL中常见数据类型

1)字符串型

VARCHAR：

变长的字符串，需要在声明字段时指定能存储的最大字符数，真实占用的空间取决于存入的字符数，存入的越多占用空间越多。适合保存内容长度不定的字符类型数据。能包含数据的大小，mysql5.0以前0~255字节，mysql5.0以后0~65535字节

CHAR:

定长字符串，需要在声明字段时指定固定字符数。即使存入的字符数少于该长度，该字段也会占用该固定长度。适合存储长度不变的字符类型数据。能包含数据的大小，0~255字节

2)大数据类型

BLOB：

大二进制类型，可以存入二进制类型的数据，通过这个字段，可以将图片、音频、视频等数据以二进制的形式存入数据库。最大为4GB。

TEXT：

大文本，被声明为这种类型的字段，可以保存大量的字符数据，最大为4GB。

注意：text属于mysql的方言，在其他数据库中为clob类型

3)数值型

TINYINT:占用1个字节，相对于java中的byte

SMALLINT:占用2个字节，相对于java中的short

INT：占用4个字节，相对于java中的int

BIGINT：占用8个字节，相对于java中的long

FLOAT：4字节单精度浮点类型，相对于java中的float

DOUBLE：8字节双精度浮点类型，相对于java中的double

4)逻辑型

BIT：

位类型，可以存储指定位的值，可以指定位的个数，如果不指定则默认值为1位，即只能保存0或1，对应到java中可以是boolean型。

5)日期型

DATE：日期

TIME：时间

DATETIME：日期时间

TIMESTAMP：时间戳

**DATETIME和TIMESTAMP的区别？

* DATETIME和TIMESTAMP显示的结果是一样的，都是固定的"YYYY-MM-DD HH:MM:SS"的格式

* DATETIME支持的范围是'1000-01-01 00:00:00'到'9999-12-31 23:59:59'。TIMESTAMP的显示范围是'1970-01-01 00:00:00'到2037年，且其实际的存储值为1970年到当前时间的毫秒数。

* 在建表时，列为TIMESTAMP的日期类型可以设置一个默认值，而DATETIME不行。

* 在更新表时，可以设置TIMESTAMP类型的列自动更新时间为当前时间。

3.3.2.字段约束

字段的约束：约束了表中一个字段的值需要符合的条件

1)定义主键约束

通常每张表都会有一个字段或多个字段联合起来唯一标识表记录，这样的字段称为这张表的主键。我们可以为这样的字段增加主键约束。主键约束要求字段必须不能为空且值必须唯一。

增加主键约束：PRIMARY KEY

可以在定义主键时指定主键的自动增长auto_increment，这样主键就不需要人为的维护，在新增记录时不需要指定主键的值，数据库会自动维护一个计数器，每次计数器的值+1后作为主键，保证主键的唯一性。

2)定义唯一约束

如果需要指定某个字段的值不能重复，可以为该字段指定唯一约束

增加唯一约束：unique

3)定义非空约束

如果需要指定某个字段的值不能为空，可以为该字段指定非空约束

增加非空约束：not null

4)外键约束

    见3.6

3.3.3.新增表

语法：

CREATE TABLE table_name

(

field1  datatype,

field2  datatype,

field3  datatype

)[character set 字符集] [collate 校对规则]

field：指定列名　datatype：指定列类型

 

注意：创建表时，要根据需保存的数据创建相应的列，并根据数据的类型定义相应的列类型。

创建表时，一般不需要指定字符集编码和校对规则，和数据库保持一致即可。

 

练习

~创建employee表

字段	属性
id	整型
name	字符型
gender	字符型
birthday	日期型
entry_date	日期型
job	字符型
salary	小数型
resume	大文本型

 

 

 

 

 

 

 

 

 

 

 

 

create table employee(

id int primary key auto_increment,

name varchar(20) unique,

gender bit not null,

birthday date,

entry_date date,

job varchar(100),

salary double,

resume text

);

3.3.4.查看表

语法：

查看表结构：

desc tabName

查看当前所有表：

show tables

查看当前数据库表建表语句

show create table tabName;

3.3.5.修改表

语法：

增加列：

ALTER TABLE tabname ADD (column datatype [DEFAULT expr][, column datatype]...);

 

修改列：

ALTER TABLE tabname MODIFY (column datatype [DEFAULT expr][, column datatype]...);

 

删除列：

ALTER TABLE tabname DROP (column);

 

修改表名：

ALTER TABLE old_tabname RENAME TO new_tabname;

或

RENAME TABLE old_tabname TO new_tabname;

 

修改列名称：

ALTER TABLE tabname CHANGE [column] old_col_name new_col_name datatype

 

修改列的顺序：

ALTER TABLE tabname MODIFY col_name1 datatype AFTER col_name2;

 

修改表的字符集：

ALTER TABLE tabname CHARACTER SET character_name;

练习：

~在上面员工表的基础上增加一个image列。

alter table employee add image blob;

~修改job列，使其长度为60。

alter table employee modify job varchar(60);

~删除gender列。

alter table employee drop gender;

~表名改为user。

alter table employee rename to user;

    或

rename table employee to user;

~列名name修改为username

alter table user change name username varchar(20);

~将image插入到gender列的后面

alter table user modify image blob after gender;

~修改表的字符集为utf8

alter table user character set utf8;

3.3.6.删除表

语法：

DROP TABLE tabname;

练习：

~删除user表

drop table user;

3.4.  操作表记录

3.4.1.INSERT

语法：

INSERT INTO tabname [(column [, column...])] VALUES (value [, value...]);

注意：

插入的数据应与字段的数据类型相同

数据的大小应在列的规定范围内

在values中列出的数据位置必须与被加入的列的排列位置相对应

字符串和日期格式的数据要用单引号引起来

如果要插入所有字段可以省写列列表，直接按表中字段顺序写值列表

 

练习：

~向员工表中插入三条数据

insert into employee (id,name,gender,birthday,entry_date,job,salary,resume)

values

(null,'张飞',1,'1990-09-09','2000-01-01','打手',998.0,'真的很能打...');

 

insert into employee

values

(null,'关羽',0,'1990-08-08','1990-08-09','财神',9999999.0,'公司挣钱就指着他了...');

        

insert into employee

values

(null,'刘备',1,'1990-06-06','1990-09-09','ceo',10.0,'公司的 ceo,基本没啥事干..'),

(null,'赵云',1,'2010-06-01','2010-06-01','保镖',400.0,'老板的      贴身护卫...');

3.4.2.mysql乱码解决：

查看当前数据库中字符集配置：

show variables like'character%';

其中：

client是客户端使用的字符集。

connection是连接数据库的字符集设置类型，如果程序没有指明连接数据库使用的字符集类型就按照服务器端默认的字符集设置。

database是数据库服务器中某个库使用的字符集设定，如果建库时没有指明，将使用服务器安装时指定的字符集设置。   

results是数据库给客户端返回时使用的字符集设定，如果没有指明，使用服务器默认的字符集。      

server是服务器安装时指定的默认字符集设定。      

system是数据库系统使用的字符集设定。（utf-8不可修改）

 

set names xxx; 命令是客户端通知服务器和当前客户端交互时使用什么编码，但是这种方式每次新开客户端都需要通知服务器 很麻烦   

我们可以修改mysql/my.ini,将default-character-set=gbk,这是修改服务器默认认为的客户端的字符集编码

这样一来,大部分人都是gbk的客户端,不需要设置任何东西,连进来就没乱码.如果有个别的人客户端不是gbk,也可以通过set names xxx;的方式声明自己的编码集,也没有乱码.

 

3.4.3.UPDATE

语法：

UPDATE tab_name SET col_name1=expr1 [, col_name2=expr2 ...]  [WHERE where_definition]   

UPDATE语法可以用新值更新原有表行中的各列。

SET子句指示要修改哪些列和要给予哪些值。

WHERE子句指定应更新哪些行。如没有WHERE子句，则更新所有的行。

练习：

~将所有员工薪水修改为5000元。

update employee set salary=5000;

~将姓名为’张飞’的员工薪水修改为3000元。

update employee set salary=3000 where name='张飞';

~将姓名为’关羽’的员工薪水修改为4000元,job改为”耍大刀”。

update employee set salary=4000,job='耍大刀' where name='关羽';   

~将刘备的薪水在原有基础上增加1000元。

update employee set salary=salary+1000 where name='刘备';

 

3.4.4.DELETE

语法：

delete from tab_name [WHERE where_definition]

where用来筛选要删除的记录，如果不使用where子句，将删除表中所有数据。

delete语句不能删除某一列的值（可使用update）

delete语句仅删除记录，不删除表本身。如要删除表，使用drop table语句。

TRUNCATE TABLE tab_name语句也可以删除表中数据，它和delete有所不同。delete是一条条删除记录，truncate是摧毁整表再重建相同结构的表，truncate效率更高。

 

练习：

~删除表中名称为’张飞’的记录。

delete from employee where name='张飞';

~删除表中所有记录。

delete from employee;

~使用truncate删除表中记录。

truncate table employee;

 

3.4.5.SELECT

(1)基本的查询

语法：

SELECT [DISTINCT] *|{column1, column2. column3..} FROM table;

select 指定查询哪些列的数据。

column指定列名。

*号代表查询所有列。

from指定查询哪张表。

DISTINCT可选，指显示结果时，是否剔除重复数据

练习：

create table exam(

id int primary key auto_increment,

name varchar(20) not null,

chinese double,

math double,

english double

);

 

insert into exam values(null,'关羽',85,76,70);

insert into exam values(null,'张飞',70,75,70);

insert into exam values(null,'赵云',90,65,95);

insert into exam values(null,'张三丰',82,79,null);

~查询表中所有学生的信息。

select * from exam;

~查询表中所有学生的姓名和对应的英语成绩。

select name,english from exam;

~过滤表中重复数据。

select distinct english from exam;

~在所有学生分数上加10分特长分显示。

select name,english+10 ,chinese+10 ,math+10 from exam;

~统计每个学生的总分。

select name,english+math+chinese from exam;

~使用别名表示学生总分。

select name as 姓名,english+math+chinese as 总分 from exam;

select name 姓名,english+math+chinese 总分 from exam;

select name english from exam;//~!这是错的

 

(2)使用where子句的查询

语法：

Select *|列名 from tablename [WHERE where_definition]

其中Where子句中支持： 

Like语句中，% 代表零个或多个任意字符，_ 代表一个字符，例first_name like ‘_a%’;

 

练习：

~查询姓名为关羽的学生成绩

select * from exam where name='关羽';

~查询英语成绩大于90分的同学

select * from exam where english > 90;

~查询总分大于230分的所有同学

select name 姓名,english+math+chinese 总分 from exam where english+math+chinese>230;

~查询语文分数在 80－100之间的同学。

                  select name,chinese from exam where chinese between 80 and 100;

~查询数学分数为75,76,77的同学。再查询分数不在这个范围内的同学

    select name,math from exam where math in (75,76,77);

    select name,math from exam where math not in (75,76,77);

~查询所有姓张的学生成绩。

select * from exam where name like '张%';

select * from exam where name like '张_';

select * from exam where name like '张__';

~查询数学分>70，语文分>80的同学。

select * from exam where math>70 and chinese>80;

 

(3)排序查询

语法：

SELECT column1, column2. column3.. FROM table order by column asc|desc;

Order by 指定排序的列，排序的列即可是表中的列名，也可以是select 语句后指定的列名。

Asc 升序、Desc 降序

ORDER BY 子句应位于SELECT语句的结尾。

 

练习：

~对英语成绩排序后输出。

select name ,english from exam order by english desc;

~对总分排序按从高到低的顺序输出

    select name 姓名, ifnull(english,0)+ifnull(math,0)+ifnull(chinese,0) 总分 from exam order by 总分 desc;

~对姓张的学生成绩排序输出

select name 姓名, ifnull(english,0)+ifnull(math,0)+ifnull(chinese,0) 总分 from exam where name like '张%' order by 总分 desc;

 

(4)聚合函数

SQL提供的聚合函数有计数函数COUNT()、求和函数SUM()、平均值函数AVG()、最大值函数MAX()、最小值函数MIN()等。如下表：

函数名称	函数功能
COUNT()	返回选取结果集中行的数目
SUM()	返回选取结果集中所有值的总和
AVG()	返回选取结果集中所有值的平均值
MAX()	返回选取结果集中所有值的最大值
MIN()	返回选取结果集中所有值的最小值

 

 

 

 

 

 

 

 

 

语法：

求符合条件的记录中指定列的记录数

    select count(列名)… from tablename [WHERE where_definition]

求符合条件的记录中指定列的和值

    Select sum(列名)… from tablename [WHERE where_definition]

求符合条件的记录中指定列的平均值

    Select avg (列名)… from tablename [WHERE where_definition]

求符合条件的记录中指定列的最大值

    Select max(列名)… from tablename [WHERE where_definition]

求符合条件的记录中指定列的最小值

    Select min(列名)… from tablename [WHERE where_definition]

练习：

~统计一个班级共有多少学生？

select count(*) from exam;

~统计数学成绩大于75的学生有多少个？

select count(*) from exam where math>75;

~统计总分大于230的人数有多少？

select count(name) from exam

where ifnull(english,0)+ifnull(math,0)+ifnull(chinese,0) >230;

~统计一个班级数学总成绩？

select sum(math) from exam;

~统计一个班级语文、英语、数学各科的总成绩

select sum(math) ,sum(english),sum(chinese) from exam;

~统计一个班级语文、英语、数学的成绩总和

select sum(ifnull(math,0)+ifnull(english,0)+ifnull(chinese,0)) from exam;

~统计一个班级语文成绩平均分

select sum(chinese)/count(*) from exam;

~求一个班级数学平均分？

select avg(math) from exam;

~求一个班级总分平均分？

select avg(ifnull(math,0)+ifnull(english,0)+ifnull(chinese,0))             from exam;

~求班级最高分和最低分

select max(ifnull(math,0)+ifnull(english,0)+ifnull(chinese,0))          from exam;

select min(ifnull(math,0)+ifnull(english,0)+ifnull(chinese,0))            from exam;

(5)分组查询

语法：

SELECT column1, column2. column3.. FROM     table group by column having ...

 

练习：

create table orders(

id int,

product varchar(20),

price float

);

 

insert into orders(id,product,price) values(1,'电视',900);

insert into orders(id,product,price) values(2,'洗衣机',100);

insert into orders(id,product,price) values(3,'洗衣粉',90);

insert into orders(id,product,price) values(4,'桔子',9);

insert into orders(id,product,price) values(5,'洗衣粉',90);

 

 

~对订单表中商品归类后，显示每一类商品的总价

select product ,sum(price) from orders group by product;

~查询购买了几类商品，并且每类总价大于100的商品

select product ,sum(price)

    from orders group by product having sum(price) >100;

~查询单价小于100而总价大于100的商品的名称.

    select product

    from orders

    where price < 100 group by product having sum(price)>100;

 

**where子句和having子句都可以进行过滤器,但是使用场景有所不同:

* where子句对分组之前的数据进行过滤,不能使用聚合函数

* having子句对分组之后的数据进行过滤,可以使用聚合函数

* 使用where子句的地方都可以用having替代，但是使用having的地方一般不能用 where替代

3.5.  mysql数据库的备份与恢复

3.5.1.备份数据库:

cmd窗口中使用mysqldump 备份数据库

mysqldump -u root -p db_name > c:/xxx.sql

例如：mysqldump -u root -p mydb2 > c:/mydb2.sql

3.5.2.恢复数据库:

方式1:cmd窗口中使用mysql命令 恢复数据库,注意,只能恢复数据库中的数据,不能恢复数据库本身!所以要提前建好数据库.

mysql -u root -p db_name < c:/xxx.sql

例如：mysql -u root -p mydb2 < c:/mydb2.sql

 

方式2:在mysql命令下,source xxx.sql,这个命令的作用就是,在当前位置执行sql文件中的所有的sql.首先新建出数据库,进入数据库,在source执行备份的sql文件即可。

例如：source c:/xxx.sql

注意：如果在cmd窗口下，执行出现“拒绝访问”提示，用管理员打开cmd窗口即可。

3.6.  外键约束

3.6.1.外键概念

数据库用表来保存数据，但是现实中，数据和数据之间往往具有一定的关系，在设计表时应该如何来保存这种关系呢？我们来看如下图。

 

一个部门可以具有多个员工，而一个员工只能属于一个部门。对于这种类型的关系我们称之为1对多的关系。

那么我们该如何保存他们之间的关系呢？

可不可以在dept表中设计一张表记录部门中员工的信息呢？

 

我们发现这是不可行的，因为一个部门中可能有多个员工，这就导致emp_id中一个字段可能需要保存多个值，这是不可行的。

那么反过来，可否在emp表保存员工所属部门id呢？

这种方式是可行的，因为一个员工只能属于一个部门，dept_id中只需存储一个值即可。

虽然我们设计了这样的字段保存两表之间的关系，但是数据库并不知道这样的关系。我们可以设想，如下的情况。

“公司老总发现科技部不挣钱，决定解散科技部”，对应到数据库时，就是如下的sql

delete from dept where id = 004；

正确的执行后，数据库中的数据变为如下

 

我们发现“555 郭德纲”人还在，所属部门却没有了，数据之间的对应关系出现了问题，我们称为违反了数据库的“完整性”。

之所以产生这样的问题，是因为虽然我们知道dept_id要参考dept表的id列，但是数据库并不知道，所以在删除数据造成违反“不完整”性的情况下数据库没有任何提示。

为了防止这样的问题产生，可以通过外键明确的通知数据库维系这种关系。

一旦任何操作违反了这种关系，数据库会阻止并报错。

 

3.6.2.建表时增加外键：

foreign key(ordersid) references orders(id)

3.6.3.修改方式增加外键：

alter table book add [constraint FK_BOOK] foreign key(pubid) references pub_com(id) [on delete restrict] [on update restrict];

 

可以明确指定外键的名称，如果不指定外键的名称,mysql会自动为你创建一个外键名称。

RESTRICT : 只要本表格里面有指向主表的数据， 在主表里面就无法删除相关记录。

CASCADE : 如果在foreign key 所指向的那个表里面删除一条记录，那么在此表里面的跟那个key一样的所有记录都会一同删掉。

               

3.6.4.删除外键

alter table 表名 drop foreign key 外键名

3.6.5.实验外键

略

4. 多表设计

4.1.  多表设计概念

在讲解外键时，我们提到了表和表之间往往存在着一定的关系，那么除了上述的1对多关系外还有哪些种关系呢？又如何设计外键来保存这些不同的关系呢？下面，我们一起来讨论表和表中的三种关系。

4.2.  三种关系及外键设计

一对一表和表之间的关系分为三种，分别是1对1、1对多、多对多，下面就用具体的例子讲解这三种关系

4.2.1. 

班级表和教室表之间的关系，一个班级只能在一个教室上课，一个教室也只能放下一个班级，这种关系就称为一对一的关系，对于一对一的关系，可以在任意一方保存另一方的主键作为外键保存两表之间的关系。

 

 

4.2.2.一对多

学生表和班级表的关系，一个班级可以有多个学生而一个学生只能属于一个班级，这种关系称为一对多的关系，对于一对多的关系，可以在多的一方设计外键保存一的一方的主键作为外键保存两表之间的关系。

 

4.2.3.多对多:

学生表和教师表的关系，一个学生可以由多个老师教授，一个老师也可以教授多个学生，这种关系称为多对多的关系，对于多对多的关系，需要设计一张第三方关系表保存两张表主键之间的对应关系。

 

5.      多表查询

5.1.  多表查询概念

表和表之间往往是存在关系的，而有时我们需要的数据在多张表中，如何跨着多张表查询数据呢?这就涉及到多表查询了

准备数据：

create table dept(

id int primary key auto_increment,

name varchar(20)

);

insert into dept values

(null,'财务部'),(null,'人事部'),(null,'科技部'),(null,'销售部');

create table emp(

id int primary key auto_increment,

name varchar(20),

dept_id int

);

insert into emp values

(null,'张飞',1),(null,'关羽',2),(null,'刘备',3),(null,'赵云',5);

 
需求：查询所有部门名称和对应的员工名称。

5.2.  多表查询设计

5.2.1.笛卡尔积查询:

这是一种最原始的查询方式，其实就是两张表相乘的结果,如果左表有m条记录,右表有n条记录,则查询出来m*n条记录.虽然查出数据了,但是其中包含了大量错误的数据,所以这种查询我们通常不使用。

select * from dept,emp;

 

 

5.2.2.内连接查询

内连接查询:只查询左边表有且右边表也有的数据,本质上是依据外键关系，在笛卡尔积查询的基础上过滤出正确的数据。通过观察结果，我们发现结果中只有两边都有对应数据的数据才能被查出来。

select * from dept ,emp where dept.id = emp.dept_id;

select * from dept inner join emp on dept.id = emp.dept_id;

 

5.2.3.外连接查询:

内连接查询只能查找出两边表都有对应数据的数据，如果两边冗余的数据也需要查询时该怎么办呢？

(1)左外连接查询:在内连接的基础上增加上左边表有而有边表没有的记录

select * from dept left join emp on dept.id = emp.dept_id;

 

#其中，left join 也可以写成 left outer join;

(2)右外连接查询:在内连接的基础上增加上右边表有而左边表没有的记录

select * from dept right join emp on dept.id = emp.dept_id;

 

#其中，right join 也可以写成 right outer join;

(3)全外连接查询:在内连接的基础上增加左边表有而右边表没有的记录和右边表有而左边表没有的记录

#mysql 不支持全外连接，但是其他的数据库支持，比如SQL Server

select * from dept full join emp on dept.id = emp.dept_id;

 

#我们可以在mysql中使用union关键字模拟全外连接

select * from dept left join emp on dept.id = emp.dept_id

union

select * from dept right join emp on dept.id = emp.dept_id;

 

#其中，full join 也可以写成 full outer join;

 

 

 

转载于:https://www.cnblogs.com/chuijingjing/p/10307320.html