mysql foreignkey-白红宇

mysql foreignkey

阅读量：7296 次

发布时间：2019-06-30

本文共 13326 字，大约阅读时间需要 44 分钟。

1.foreign key

当数据足够大的时候,字段会出现大量重复,

解决:额外定义一个大量冗余的字段表,(有id) 一张是关联表(从表),一张是被关联表(主表)

进行关联的时候 ,先创建被关联表, 现在被关联表添加,再是关联表

constraint fk_dep(随便写) foreign key(dep_id) references dep(id)

在创建关联表 但是再修改出现问题是先删除这个部门的员工才能删除这个部门 问题代码:

1 #1.创建表时先创建被关联表，再创建关联表 2 # 先创建被关联表（dep表） 3 create table dep( 4     id int primary key, 5     name varchar(20) not null, 6     descripe varchar(20) not null 7 ); 8  9 #再创建关联表（emp表）10 create table emp(11     id int primary key,12     name varchar(20) not null,13     age int not null,14     dep_id int,15     constraint fk_dep foreign key(dep_id) references dep(id) 16 );17 18 #2.插入记录时，先往被关联表中插入记录，再往关联表中插入记录19 20 insert into dep values21 (1,'IT','IT技术有限部门'),22 (2,'销售部','销售部门'),23 (3,'财务部','花钱太多部门');24 25 insert into emp values26 (1,'zhangsan',18,1),27 (2,'lisi',19,1),28 (3,'egon',20,2),29 (4,'yuanhao',40,3),30 (5,'alex',18,2);31 32 3.删除表33 #按道理来说，删除了部门表中的某个部门，员工表的有关联的记录相继删除。34 mysql> delete from dep where id=3;35 ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`db5`.`emp`, CONSTRAINT `fk_name` FOREIGN KEY (`dep_id`) REFERENCES `dep` (`id`))36 37 #但是先删除员工表的记录之后，再删除当前部门就没有任何问题38 39 mysql> delete from emp where dep =3;40 Query OK, 1 row affected (0.00 sec)41 42 mysql> select * from emp;43 +----+----------+-----+--------+44 | id | name     | age | dep_id |45 +----+----------+-----+--------+46 |  1 | zhangsan |  18 |      1 |47 |  2 | lisi     |  18 |      1 |48 |  3 | egon     |  20 |      2 |49 |  5 | alex     |  18 |      2 |50 +----+----------+-----+--------+51 rows in set (0.00 sec)52 53 mysql> delete from dep where id=3;54 Query OK, 1 row affected (0.00 sec)55 56 mysql> select * from dep;57 +----+-----------+----------------------+58 | id | name      | descripe             |59 +----+-----------+----------------------+60 |  1 | IT        | IT技术有限部门       |61 |  2 | 销售部    | 销售部门             |62 +----+-----------+----------------------+63 rows in set (0.00 sec)

删除员工才能删除部门

解决办法: 关联表升级:

on delete cascade #同步删除    on update cascade #同步更新 就可以同步删除了

1 create table emp(2     id int primary key,3     name varchar(20) not null,4     age int not null,5     dep_id int,6     constraint fk_dep foreign key(dep_id) references dep(id) 7     on delete cascade #同步删除8     on update cascade #同步更新9 );

修改的emp

后面的同步删除,更新

1 #再去删被关联表(dep)的记录，关联表(emp)中的记录也跟着删除 2 mysql> delete from dep where id=3; 3 Query OK, 1 row affected (0.00 sec) 4  5 mysql> select * from dep; 6 +----+-----------+----------------------+ 7 | id | name      | descripe             | 8 +----+-----------+----------------------+ 9 |  1 | IT        | IT技术有限部门       |10 |  2 | 销售部    | 销售部门             |11 +----+-----------+----------------------+12 rows in set (0.00 sec)13 14 mysql> select * from emp;15 +----+----------+-----+--------+16 | id | name     | age | dep_id |17 +----+----------+-----+--------+18 |  1 | zhangsan |  18 |      1 |19 |  2 | lisi     |  19 |      1 |20 |  3 | egon     |  20 |      2 |21 |  5 | alex     |  18 |      2 |22 +----+----------+-----+--------+23 rows in set (0.00 sec)24 25 #再去更改被关联表(dep)的记录，关联表(emp)中的记录也跟着更改26 27 mysql> update dep set id=222 where id=2;28 Query OK, 1 row affected (0.02 sec)29 Rows matched: 1  Changed: 1  Warnings: 030 31 # 赶紧去查看一下两张表是否都被删除了，是否都被更改了32 mysql> select * from dep;33 +-----+-----------+----------------------+34 | id  | name      | descripe             |35 +-----+-----------+----------------------+36 |   1 | IT        | IT技术有限部门       |37 | 222 | 销售部    | 销售部门             |38 +-----+-----------+----------------------+39 rows in set (0.00 sec)40 41 mysql> select * from emp;42 +----+----------+-----+--------+43 | id | name     | age | dep_id |44 +----+----------+-----+--------+45 |  1 | zhangsan |  18 |      1 |46 |  2 | lisi     |  19 |      1 |47 |  3 | egon     |  20 |    222 |48 |  5 | alex     |  18 |    222 |49 +----+----------+-----+--------+50 rows in set (0.00 sec)

同步删除,更新

2.

因为有foreign key 的约束 ,使表有了三种关系

多对一书出版商 (一个出版商可以出版多种书而一本书只能由一个出版商出,因为版权)

先创建出版商,在创建书 (含有

constraint fk_book_press foreign key(press_id) references press(id)    on delete cascade    on update cascade);

)

1 create table press( 2     id int primary key auto_increment, 3     name varchar(20) 4 ); 5  6 create table book( 7     id int primary key auto_increment, 8     name varchar(20), 9     press_id int not null,10          constraint fk_book_press foreign key(press_id) references press(id)11     on delete cascade12     on update cascade13 );14 15 # 先往被关联表中插入记录16 insert into press(name) values17 ('北京工业地雷出版社'),18 ('人民音乐不好听出版社'),19 ('知识产权没有用出版社')20 ;21 22 # 再往关联表中插入记录23 insert into book(name,press_id) values24 ('九阳神功',1),25 ('九阴真经',2),26 ('九阴白骨爪',2),27 ('独孤九剑',3),28 ('降龙十巴掌',2),29 ('葵花宝典',3)30 ;31 32 查询结果：33 mysql> select * from book;34 +----+-----------------+----------+35 | id | name            | press_id |36 +----+-----------------+----------+37 |  1 | 九阳神功        |        1 |38 |  2 | 九阴真经        |        2 |39 |  3 | 九阴白骨爪      |        2 |40 |  4 | 独孤九剑        |        3 |41 |  5 | 降龙十巴掌      |        2 |42 |  6 | 葵花宝典        |        3 |43 +----+-----------------+----------+44 rows in set (0.00 sec)45 46 mysql> select * from press;47 +----+--------------------------------+48 | id | name                           |49 +----+--------------------------------+50 |  1 | 北京工业地雷出版社             |51 |  2 | 人民音乐不好听出版社           |52 |  3 | 知识产权没有用出版社           |53 +----+--------------------------------+54 rows in set (0.00 sec)55 56 书和出版社（多对一）

多对一

多对多书作者 (作者可以写多本书,书也可由多个作者)

这样是无法创建foreign ke关系的,可以创建第三张表存放

1.创建书和作者 2.创建出一个表明关系的表 3.放入作者和作者的作品.4.在公共表放上数据(

insert into author2book(author_id,book_id) values

)

1 # 创建被关联表author表，之前的book表在讲多对一的关系已创建 2 create table author( 3     id int primary key auto_increment, 4     name varchar(20) 5 ); 6 #这张表就存放了author表和book表的关系，即查询二者的关系查这表就可以了 7 create table author2book( 8     id int not null unique auto_increment, 9     author_id int not null,10     book_id int not null,11     constraint fk_author foreign key(author_id) references author(id)12     on delete cascade13     on update cascade,14     constraint fk_book foreign key(book_id) references book(id)15     on delete cascade16     on update cascade,17     primary key(author_id,book_id)18 );19 #插入四个作者，id依次排开20 insert into author(name) values('egon'),('alex'),('wusir'),('yuanhao');21 22 # 每个作者的代表作23 egon: 九阳神功、九阴真经、九阴白骨爪、独孤九剑、降龙十巴掌、葵花宝典24 alex: 九阳神功、葵花宝典25 wusir:独孤九剑、降龙十巴掌、葵花宝典26 yuanhao:九阳神功27 28 # 在author2book表中插入相应的数据29 30 insert into author2book(author_id,book_id) values31 (1,1),32 (1,2),33 (1,3),34 (1,4),35 (1,5),36 (1,6),37 (2,1),38 (2,6),39 (3,4),40 (3,5),41 (3,6),42 (4,1)43 ;44 # 现在就可以查author2book对应的作者和书的关系了45 mysql> select * from author2book;46 +----+-----------+---------+47 | id | author_id | book_id |48 +----+-----------+---------+49 |  1 |         1 |       1 |50 |  2 |         1 |       2 |51 |  3 |         1 |       3 |52 |  4 |         1 |       4 |53 |  5 |         1 |       5 |54 |  6 |         1 |       6 |55 |  7 |         2 |       1 |56 |  8 |         2 |       6 |57 |  9 |         3 |       4 |58 | 10 |         3 |       5 |59 | 11 |         3 |       6 |60 | 12 |         4 |       1 |61 +----+-----------+---------+62 rows in set (0.00 sec)63 64 作者与书籍关系（多对多）

多对多

一对一

在其中从表的仿造主表加上unique

1 #例如： 一个用户只能注册一个博客 2  3 #两张表： 用户表 （user）和 博客表(blog) 4 # 创建用户表 5 create table user( 6     id int primary key auto_increment, 7     name varchar(20) 8 ); 9 # 创建博客表10 create table blog(11     id int primary key auto_increment,12     url varchar(100),13     user_id int unique,14     constraint fk_user foreign key(user_id) references user(id)15     on delete cascade16     on update cascade17 );18 #插入用户表中的记录19 insert into user(name) values20 ('alex'),21 ('wusir'),22 ('egon'),23 ('xiaoma')24 ;25 # 插入博客表的记录26 insert into blog(url,user_id) values27 ('http://www.cnblog/alex',1),28 ('http://www.cnblog/wusir',2),29 ('http://www.cnblog/egon',3),30 ('http://www.cnblog/xiaoma',4)31 ;32 # 查询wusir的博客地址33 select url from blog where user_id=2;34 35 用户和博客（一对一）

一对一

二.

单表查询

语法 select 字段1,字段2 ...(*) from 表名

where 条件

group by

（1）where 约束　

where子句中可以使用

1.比较运算符>,<,>=,<=,<>,!=

2.between 80 and 100;(前面是)需要查找的条件

3. in (80,90,100);值再()中间

4.like"jin%" %表示查找多个含有jin的字符

like"ale_" _表示查找一个含有ale关键字的字符

5.逻辑运算符: 可以再多个条件下使用逻辑运算符 and or not

1 #1 :单条件查询  2 mysql> select id,emp_name from employee where id > 5;  3 +----+------------+  4 | id | emp_name   |  5 +----+------------+  6 |  6 | jingliyang |  7 |  7 | jinxin     |  8 |  8 | xiaomage   |  9 |  9 | 歪歪       | 10 | 10 | 丫丫       | 11 | 11 | 丁丁       | 12 | 12 | 星星       | 13 | 13 | 格格       | 14 | 14 | 张野       | 15 | 15 | 程咬金     | 16 | 16 | 程咬银     | 17 | 17 | 程咬铜     | 18 | 18 | 程咬铁     | 19  20 #2 多条件查询 21 mysql> select emp_name from employee where post='teacher' and salary>10000; 22 +----------+ 23 | emp_name | 24 +----------+ 25 | alex         | 26 | jinxin     | 27 +----------+ 28  29 #3.关键字BETWEEN AND 30  SELECT name,salary FROM employee  31         WHERE salary BETWEEN 10000 AND 20000; 32  33  SELECT name,salary FROM employee  34         WHERE salary NOT BETWEEN 10000 AND 20000; 35  36 #注意''是空字符串，不是null 37  SELECT name,post_comment FROM employee WHERE post_comment=''; 38  ps： 39         执行 40         update employee set post_comment='' where id=2; 41         再用上条查看，就会有结果了 42 #5:关键字IN集合查询 43 mysql>  SELECT name,salary FROM employee WHERE salary=3000 OR salary=3500 OR salary=4000 OR salary=9000 ; 44 +------------+---------+ 45 | name       | salary  | 46 +------------+---------+ 47 | yuanhao    | 3500.00 | 48 | jingliyang | 9000.00 | 49 +------------+---------+ 50 rows in set (0.00 sec) 51  52 mysql>  SELECT name,salary FROM employee  WHERE salary IN (3000,3500,4000,9000) ; 53 +------------+---------+ 54 | name       | salary  | 55 +------------+---------+ 56 | yuanhao    | 3500.00 | 57 | jingliyang | 9000.00 | 58 +------------+---------+ 59 mysql>  SELECT name,salary FROM employee  WHERE salary NOT IN (3000,3500,4000,9000) ; 60 +-----------+------------+ 61 | name      | salary     | 62 +-----------+------------+ 63 | egon      |    7300.33 | 64 | alex      | 1000000.31 | 65 | wupeiqi   |    8300.00 | 66 | liwenzhou |    2100.00 | 67 | jinxin    |   30000.00 | 68 | xiaomage  |   10000.00 | 69 | 歪歪      |    3000.13 | 70 | 丫丫      |    2000.35 | 71 | 丁丁      |    1000.37 | 72 | 星星      |    3000.29 | 73 | 格格      |    4000.33 | 74 | 张野      |   10000.13 | 75 | 程咬金    |   20000.00 | 76 | 程咬银    |   19000.00 | 77 | 程咬铜    |   18000.00 | 78 | 程咬铁    |   17000.00 | 79 +-----------+------------+ 80 rows in set (0.00 sec) 81  82 #6:关键字LIKE模糊查询 83 通配符’%’ 84 mysql> SELECT * FROM employee WHERE name LIKE 'jin%'; 85 +----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+ 86 | id | name       | sex    | age | hire_date  | post    | post_comment | salary   | office | depart_id | 87 +----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+ 88 |  6 | jingliyang | female |  18 | 2011-02-11 | teacher | NULL         |  9000.00 |    401 |         1 | 89 |  7 | jinxin     | male   |  18 | 1900-03-01 | teacher | NULL         | 30000.00 |    401 |         1 | 90 +----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+ 91 rows in set (0.00 sec) 92  93  94 通配符'_' 95  96 mysql> SELECT  age FROM employee WHERE name LIKE 'ale_'; 97 +-----+ 98 | age | 99 +-----+100 |  78 |101 +-----+102 row in set (0.00 sec)103 104 练习：105 1. 查看岗位是teacher的员工姓名、年龄106 2. 查看岗位是teacher且年龄大于30岁的员工姓名、年龄107 3. 查看岗位是teacher且薪资在9000-1000范围内的员工姓名、年龄、薪资108 4. 查看岗位描述不为NULL的员工信息109 5. 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资110 6. 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资111 7. 查看岗位是teacher且名字是jin开头的员工姓名、年薪112 113 #对应的sql语句114 select name,age from employee where post = 'teacher';115 select name,age from employee where post='teacher' and age > 30; 116 select name,age,salary from employee where post='teacher' and salary between 9000 and 10000;117 select * from employee where post_comment is not null;118 select name,age,salary from employee where post='teacher' and salary in (10000,9000,30000);119 select name,age,salary from employee where post='teacher' and salary not in (10000,9000,30000);120 select name,salary*12 from employee where post='teacher' and name like 'jin%';121 122 where约束

where 详解以及练习

（2）group by 分组查询

#1、首先明确一点：分组发生在where之后，即分组是基于where之后得到的记录而进行的#2、分组指的是：将所有记录按照某个相同字段进行归类，比如针对员工信息表的职位分组，或者按照性别进行分组等#3、为何要分组呢？    取每个部门的最高工资    取每个部门的员工数    取男人数和女人数小窍门：‘每’这个字后面的字段，就是我们分组的依据#4、大前提：    可以按照任意字段分组，但是分组完毕后，比如group by post，只能查看post字段，如果想查看组内信息，需要借助于聚合函数 在分组前需要进行

mysql> set global sql_mode='ONLY_FULL_GROUP_BY'; 也可以将

ONLY_FULL_GROUP_BY 放置配置文件上

聚合函数辅助分组

1 mysql> select * from emp group by post;# 报错 2 ERROR 1054 (42S22): Unknown column 'post' in 'group statement' 3  4  5  6 mysql>  select post from employee group by post; 7 +-----------------------------------------+ 8 | post                                    | 9 +-----------------------------------------+10 | operation                               |11 | sale                                    |12 | teacher                                 |13 | 老男孩驻沙河办事处外交大使              |14 +-----------------------------------------+15 rows in set (0.00 sec)

分组

转载于:https://www.cnblogs.com/zhangqing979797/p/9795211.html

你可能感兴趣的文章

关于安装sql2012出现的netfx3功能问题