mysql表关系

mysql数据库

知识要点：

单表查询

子查询

联表查询

事务

在进行查询之前，我们要先建好关系表，并往数据表中插入些数据。为查询操作做好准备。

五张关系表的创建：

#创建并进入数据库：

mysql>CREATEDATABASE`info`;

QueryOK,1rowaffected (0.00sec)

mysql>USE`info`;

Databasechanged

#创建学院表：

mysql>CREATETABLE`department`(

->`id`INTPRIMARYKEYAUTO_INCREMENT,

->`name`VARCHAR(20)NOTNULL

-> );

QueryOK,0rows affected (0.69sec)

#创建学生表：

mysql>CREATETABLE`students`(

->`s_id`INTPRIMARYKEYAUTO_INCREMENT,

->`name`VARCHAR(20)NOTNULL,

->`d_id`INT,

->FOREIGNKEY(`d_id`)REFERENCES`department`(`id`)

-> );

QueryOK,0rows affected (0.65sec)

#创建学生的详细信息表：

mysql>CREATETABLE`stu_details`(

->`s_id`INTPRIMARYKEY,

->`age`INT,

->`gender`CHAR(1)

-> ,

->FOREIGNKEY(`s_id`)REFERENCES`students`(`s_id`)

-> );

QueryOK,0rows affected (0.55sec)

#创建课程表：

mysql>CREATETABLE`course`(

->`id`INTPRIMARYKEYAUTO_INCREMENT,

->`name`VARCHAR(20)NOTNULL

-> );

QueryOK,0rows affected (0.50sec)

#创建中间表：

mysql>CREATETABLE`select`(

->`s_id`INT,

->`c_id`INT,

->PRIMARYKEY(`s_id`,`c_id`),

->FOREIGNKEY(`s_id`)REFERENCES`students`(`s_id`),

->FOREIGNKEY(`c_id`)REFERENCES`course`(`id`)

-> );

QueryOK,0rows affected (0.84sec)

#查看当前存在的表：

mysql>SHOWTABLES;

+----------------+

| Tables_in_info |

+----------------+

| course |

| department |

|select|

| stu_details |

| students |

+----------------+

5rowsinset(0.00sec)

往数据表中添加数据

#往学院表中添加数据：

mysql>INSERTINTO`department`(`name`)

->VALUES('外国语'),

-> ('艺术'),

-> ('计算机'),

-> ('化工')

-> ;

QueryOK,4rows affected (0.11sec)

Records:4Duplicates:0Warnings:0

#往学生表中添加数据：

mysql>INSERTINTO`students`(`name`,`d_id`)

->VALUES('小明',1),

-> ('小红',3),

-> ('小花',3),

-> ('小新',4)

-> ;

QueryOK,4rows affected (0.09sec)

Records:4Duplicates:0Warnings:0

#往学生详细信息表中添加数据：

mysql>INSERTINTOstu_details

->VALUES(1,18,'m'),

-> (4,20,'m'),

-> (3,16,'f'),

-> (2,19,'f')

-> ;

QueryOK,4rows affected (0.11sec)

Records:4Duplicates:0Warnings:0

#往课程表中添加数据：

mysql>INSERTINTO`course`

-> (`name`)VALUES

-> ('心理学'),

-> ('佛学'),

-> ('近代史'),

-> ('音乐鉴赏')

-> ;

QueryOK,4rows affected (0.08sec)

Records:4Duplicates:0Warnings:0

#查看中间表的结构：

mysql>DESC`select`;

+-------+---------+------+-----+---------+-------+

+-------+---------+------+-----+---------+-------+

| s_id |int(11) |NO| PRI |NULL| |

| c_id |int(11) |NO| PRI |NULL| |

+-------+---------+------+-----+---------+-------+

2rowsinset(0.03sec)

#往中间表中添加数据

mysql>INSERTINTO`select`

->VALUES(1,2),

-> (1,4),

-> (2,1),

-> (2,4),

-> (4,1),

-> (4,2),

-> (4,4)

-> ;

QueryOK,7rows affected (0.06sec)

Records:7Duplicates:0Warnings:0

查询

查询所有记录

SELECT * FROM tb_name;

查询选中列记录

SELECT col_name1,col_name2 FROM tb_name;

查询指定条件下的记录

SELECT col_name FROM tb_name WHERE 条件

查询后为列取别名

SELECT col_name AS new_name FROM tab_name

#查询所有记录：

mysql>SELECT*FROM`students`;

+------+--------+------+

| s_id | name | d_id |

+------+--------+------+

|1| 小明 |1|

|2| 小红 |3|

|3| 小花 |3|

|4| 小新 |4|

+------+--------+------+

4rowsinset(0.00sec)

#查询选中列记录

mysql>SELECTname,d_idFROMstudents;

+--------+------+

| name | d_id |

+--------+------+

| 小明 |1|

| 小红 |3|

| 小花 |3|

| 小新 |4|

+--------+------+

4rowsinset(0.00sec)

#查询指定条件下的记录

mysql>SELECT*FROMstudentsWHERE`name`='小红';

+------+--------+------+

| s_id | name | d_id |

+------+--------+------+

|2| 小红 |3|

+------+--------+------+

1rowinset(0.00sec)

#查询后为列取别名

mysql>SELECTnameAS`姓名`,d_idAS学院idFROMstudentsWHEREs_id>=2;

+--------+----------+

| 姓名 | 学院id |

+--------+----------+

| 小红 |3|

| 小花 |3|

| 小新 |4|

+--------+----------+

3rowsinset(0.00sec)

排序ORDER BY

ASC升序(默认) DESC降序

#查询学生的选修表（中间表）

mysql>SELECT*FROM`select`;

+------+------+

| s_id | c_id |

+------+------+

|2|1|

|4|1|

|1|2|

|4|2|

|1|4|

|2|4|

|4|4|

+------+------+

7rowsinset(0.00sec)

#按学生学号升序输出

mysql>SELECT*FROM`select`ORDERBY`s_id`;

+------+------+

| s_id | c_id |

+------+------+

|1|2|

|1|4|

|2|1|

|2|4|

|4|1|

|4|2|

|4|4|

+------+------+

7rowsinset(0.00sec)

#按课程id降序输出：

mysql>SELECT*FROM`select`ORDERBY`c_id`DESC;

+------+------+

| s_id | c_id |

+------+------+

|4|4|

|2|4|

|1|4|

|4|2|

|1|2|

|4|1|

|2|1|

+------+------+

7rowsinset(0.00sec)

限制显示数据的数量LIMIT

#按学生学号升序输出的前4条数据

mysql>SELECT*FROM`select`ORDERBY`s_id`LIMIT4;

+------+------+

| s_id | c_id |

+------+------+

|1|2|

|1|4|

|2|1|

|2|4|

+------+------+

4rowsinset(0.00sec)

#指定的返回的数据的位置和数量

mysql>SELECT*FROM`select`ORDERBY`s_id`LIMIT4,2;

+------+------+

| s_id | c_id |

+------+------+

|4|1|

|4|2|

+------+------+

2rowsinset(0.00sec)

分组查询GROUP BY

例：

对学生表中学院栏进行分组，并统计学院的学生人数：

mysql> SELECT d_id AS 学院id,count(d_id) AS 学生个数 FROM students GROUP BY `d_id`;

+----------+--------------+

| 学院id | 学生个数 |

+----------+--------------+

| 1 | 1 |

| 3 | 2 |

| 4 | 1 |

+----------+--------------+

3 rows in set (0.00 sec)

HAVING分组条件

HAVING 后的字段必须是SELECT后出现过的

mysql> SELECT d_id AS 学院id,count(d_id) AS 学生个数 FROM students GROUP BY `d_id`HAVING 学生个数=1;

+----------+--------------+

| 学院id | 学生个数 |

+----------+--------------+

| 1 | 1 |

| 4 | 1 |

+----------+--------------+

2 rows in set (0.01 sec)

查询中一些较为常见的函数

#求最大年龄

mysql>SELECTMAX(`age`)FROM`stu_details`;

+------------+

|MAX(`age`) |

+------------+

|20|

+------------+

1rowinset(0.03sec)

#求最小年龄

mysql>SELECTMIN(`age`)FROM`stu_details`;

+------------+

|MIN(`age`) |

+------------+

|16|

+------------+

1rowinset(0.00sec)

#求和

mysql>SELECTSUM(`age`)FROM`stu_details`;

+------------+

|SUM(`age`) |

+------------+

|73|

+------------+

1rowinset(0.05sec)

#求平均数

mysql>SELECTAVG(`age`)FROM`stu_details`;

+------------+

|AVG(`age`) |

+------------+

|18.2500|

+------------+

1rowinset(0.00sec)

子查询

出现在其他SQL语句内的SELECT字句。

1)嵌套在查询内部2)必须始终出现在圆括号内3)可以包含多个关键字或条件

#查找出大于平均年龄的数据

mysql>SELECT*FROM`stu_details`WHERE`age`>18.25;

+------+------+--------+

| s_id | age | gender |

+------+------+--------+

|2|19| f |

|4|20| m |

+------+------+--------+

2rowsinset(0.00sec)

#将平均数的SQL语句作为子查询放入上一条语句中

mysql>SELECT*FROM`stu_details`WHERE`age`>(SELECTAVG(`age`)FROM`stu_details`);

+------+------+--------+

| s_id | age | gender |

+------+------+--------+

|2|19| f |

|4|20| m |

+------+------+--------+

2rowsinset(0.10sec)

联表查询

内连接[INNER| CROSS] JOIN

无条件内连接：无条件内连接，又名交叉连接/笛卡尔连接第一张表种的每一向会和另一张表的每一项依次组合

有条件内连接在无条件的内连接基础上，加上一个ON子句当连接的时候，筛选出那些有实际意义的记录行来进行拼接

在写条件时注意两张表的列名是否一样，如果时一样的则要在前面加上表名，tb_name.colname这种形式存在

#无条件内连接：

mysql>SELECT*FROM`students`INNERJOIN`department`;

+------+--------+------+----+-----------+

| s_id | name | d_id | id | name |

+------+--------+------+----+-----------+

|1| 小明 |1|1| 外国语 |

|2| 小红 |3|1| 外国语 |

|3| 小花 |3|1| 外国语 |

|4| 小新 |4|1| 外国语 |

|1| 小明 |1|2| 艺术 |

|2| 小红 |3|2| 艺术 |

|3| 小花 |3|2| 艺术 |

|4| 小新 |4|2| 艺术 |

|1| 小明 |1|3| 计算机 |

|2| 小红 |3|3| 计算机 |

|3| 小花 |3|3| 计算机 |

|4| 小新 |4|3| 计算机 |

|1| 小明 |1|4| 化工 |

|2| 小红 |3|4| 化工 |

|3| 小花 |3|4| 化工 |

|4| 小新 |4|4| 化工 |

+------+--------+------+----+-----------+

16rowsinset(0.04sec)

#有条件内连接：

mysql>SELECT*FROM`students`INNERJOIN`department`

->ONd_id=id;

+------+--------+------+----+-----------+

| s_id | name | d_id | id | name |

+------+--------+------+----+-----------+

|1| 小明 |1|1| 外国语 |

|2| 小红 |3|3| 计算机 |

|3| 小花 |3|3| 计算机 |

|4| 小新 |4|4| 化工 |

+------+--------+------+----+-----------+

4rowsinset(0.03sec)

有条件的外连接：{ LEFT| RIGHT } [OUTER] JOIN

左外连接：两张表做连接的时候，在连接条件不匹配的时候留下左表中的数据，而右表中的数据以NULL填充

右外连接对两张表做连接的时候，在连接条件不匹配的时候留下右表中的数据，而左表中的数据以NULL填充

#往学生表中添加数据，只添加名字

mysql>INSERTINTOstudents(name)

->VALUES('xixi');

QueryOK,1rowaffected (0.11sec)

#查看所有学生表数据

mysql>SELECT*FROMstudentS;

+------+--------+------+

| s_id | name | d_id |

+------+--------+------+

|1| 小明 |1|

|2| 小红 |3|

|3| 小花 |3|

|4| 小新 |4|

|5| xixi |NULL|

+------+--------+------+

5rowsinset(0.00sec)

#使用内连接加条件只能看到有分配好学院的学生的信息；

mysql>SELECT*FROMstudentsINNERJOINdepartment

->ONd_id=id;

+------+--------+------+----+-----------+

| s_id | name | d_id | id | name |

+------+--------+------+----+-----------+

|1| 小明 |1|1| 外国语 |

|2| 小红 |3|3| 计算机 |

|3| 小花 |3|3| 计算机 |

|4| 小新 |4|4| 化工 |

+------+--------+------+----+-----------+

4rowsinset(0.02sec)

#使用左连接把学生的数据全取出来，该学生没有学院信息的用NULL填充

mysql>SELECT*FROMstudentsLEFTJOINdepartment

->ONd_id=id;

+------+--------+------+------+-----------+

| s_id | name | d_id | id | name |

+------+--------+------+------+-----------+

|1| 小明 |1|1| 外国语 |

|2| 小红 |3|3| 计算机 |

|3| 小花 |3|3| 计算机 |

|4| 小新 |4|4| 化工 |

+------+--------+------+------+-----------+

5rowsinset(0.00sec)

#使用右外连接把目前还没有学生的学院的数据也显示出来

mysql>SELECT*FROMstudentsRIGHTJOINdepartment

->ONd_id=id;

+------+--------+------+----+-----------+

| s_id | name | d_id | id | name |

+------+--------+------+----+-----------+

|1| 小明 |1|1| 外国语 |

|2| 小红 |3|3| 计算机 |

|3| 小花 |3|3| 计算机 |

|4| 小新 |4|4| 化工 |

+------+--------+------+----+-----------+

5rowsinset(0.00sec)

mysql>

查询SQL的优化

MySQL的执行顺序

1.FROM: 对FROM的左边的表和右边的表计算笛卡尔积。产生虚表VT1

2.ON: 对虚表VT1进行ON筛选，只有那些符合的行才会被记录在虚表VT2中。

3.JOIN：如果指定了OUTERJOIN（比如leftjoin、rightjoin），那么保留表中未匹配的行就会作为外部行添加到虚拟表VT2中，产生虚拟表VT3, rugfrom子句中包含两个以上的表的话，那么就会对上一个join连接产生的结果VT3和下一个表重复执行步骤1~3这三个步骤，一直到处理完所有的表为止。

4.WHERE：对虚拟表VT3进行WHERE条件过滤。只有符合的记录才会被插入到虚拟表VT4中。

5.GROUPBY: 根据groupby子句中的列，对VT4中的记录进行分组操作，产生VT5.

6.CUBE |ROLLUP: 对表VT5进行cube或者rollup操作，产生表VT6.

7.HAVING：对虚拟表VT6应用having过滤，只有符合的记录才会被插入到虚拟表VT7中。

8.SELECT：执行select操作，选择指定的列，插入到虚拟表VT8中。

9.DISTINCT：对VT8中的记录进行去重。产生虚拟表VT9.

10.ORDERBY: 将虚拟表VT9中的记录按照进行排序操作，产生虚拟表VT10.

11.LIMIT：取出指定行的记录，产生虚拟表VT11, 并将结果返回。

通过上面的执行顺序不难想到，要想SQL执行更快，就必须把筛选条件尽量的往前面放。如下：

SELECT

s.`name`,

e.`name`

FROM

`students`s

LEFTJOIN(

SELECT

se.`s_id`,

c.`name`

FROM

`select`se

JOIN`course`cONse.`c_id`= c.`id`

) eONs.`id`=e.`stu_id`

SELECT

FROM

`student`s

WHERE

s.`dep_id`= (

SELECT

`id`

FROM

`department`d

WHERE

d.`name`='外国语学院'

)

在这两个例子中，第一个SQL中的子表只会被查询一次，但是在第二个SQL中，子表会被执行n次，这个n取决student表中的数据条数，如果子表的数据量很大的话，那么SQL的执行速度会十分慢。

这是典型的通过执行顺序来优化SQL，除此之外，要想SQL执行快一点，应该尽量避免模糊匹配，如：like,in,not in 等这些匹配条件。

还有几点建议给大家：

1.尽量避免整表扫描，如SELECT *

2.建立合适的索引

3.使用合适的存储引擎

4.在JOIN中，尽量用小表LEFT JOIN 大表

5.除非十分必要，尽量不要使用ORDER BY,GROUP BY 和 DISTINCT(去重)，尽量用索引来代替

事务

为了保证数据库记录的更新从一个一致性状态变更为另一个一致性状态使用事务来处理是非常必要。

例：

创建一张银行账户的表

mysql> CREATE TABLE `account`(

-> `id` INT PRIMARY KEY AUTO_INCREMENT,

-> `name` VARCHAR(20) NOT NULL,

-> `balance` INT

-> );

Query OK, 0 rows affected (0.52 sec)

添加两个用户及用户的存款的信息

mysql> INSERT INTO `account`(`name`,`balance`)

-> VALUES('shangdian',10000),

-> ('xiaoming',2000)

-> ;

Query OK, 2 rows affected (0.09 sec)

Records: 2 Duplicates: 0 Warnings: 0

假设现在用户小明在商店买了500元东西，现在要转账给商店，那么就需要从小明的账户上减去500，然后在商店的用户上加上500，但是如果在减500的过程中出现了系统故障，再重新启动后发现小明的钱扣了，但商店却没有收到，这时候就会出现数据变动不一致。对于这种数据的修改我们需要的就是要么同时修改成功，要么同时修改失败，所以这就需要用事务来进行出来。

START TRANSACTION：开始一个新的事务

COMMIT：提交当前事务，做出永久改变

ROLLBACK：回滚当前事务，放弃修改

mysql> START TRANSACTION;

Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE `account`

-> SET `balance`= `balance`-50

-> WHERE `name` ='xiaoming'

-> ;

Query OK, 1 row affected (0.04 sec)

Rows matched: 1 Changed: 1 Warnings: 0

使用ROLLBACK;使数据的修改不生效，回到事务前的状态：

mysql> ROLLBACK;

Query OK, 0 rows affected (0.06 sec)

做一次正确的操作：

mysql> START TRANSACTION;

Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE `account`

-> SET `balance`=`balance`-50

-> WHERE `name`='xiaoming'

-> ;

Query OK, 1 row affected (0.03 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> UPDATE `account`

-> SET `balance`=`balance`+50

-> WHERE `name`='shangdian'

-> ;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT * FROM `account`;

mysql> COMMIT;

Query OK, 0 rows affected (0.07 sec)

当COMMIT后，数据修改成功，ROLLBACK也没法回到之前了。

mysql> ROLLBACK;

Query OK, 0 rows affected (0.00 sec)

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 206,839评论 6赞 482
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 88,543评论 2赞 382
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 153,116评论 0赞 344
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 55,371评论 1赞 279
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 64,384评论 5赞 374
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 49,111评论 1赞 285
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 38,416评论 3赞 400
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 37,053评论 0赞 259
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 43,558评论 1赞 300
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 36,007评论 2赞 325
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 38,117评论 1赞 334
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,756评论 4赞 324
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 39,324评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 30,315评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,539评论 1赞 262
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 45,578评论 2赞 355
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,877评论 2赞 345

mysql表关系

推荐阅读更多精彩内容