mysql foreignkey
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 values 21 (1,'IT','IT技术有限部门'), 22 (2,'销售部','销售部门'), 23 (3,'财务部','花钱太多部门'); 24 25 insert into emp values 26 (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 );
后面的同步删除,更新
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: 0 30 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 cascade 12 on update cascade 13 ); 14 15 # 先往被关联表中插入记录 16 insert into press(name) values 17 ('北京工业地雷出版社'), 18 ('人民音乐不好听出版社'), 19 ('知识产权没有用出版社') 20 ; 21 22 # 再往关联表中插入记录 23 insert into book(name,press_id) values 24 ('九阳神功',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 cascade 13 on update cascade, 14 constraint fk_book foreign key(book_id) references book(id) 15 on delete cascade 16 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) values 31 (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 cascade 16 on update cascade 17 ); 18 #插入用户表中的记录 19 insert into user(name) values 20 ('alex'), 21 ('wusir'), 22 ('egon'), 23 ('xiaoma') 24 ; 25 # 插入博客表的记录 26 insert into blog(url,user_id) values 27 ('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约束
（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)
