create database day16;
use day16;
//部门表
create table dept(
did int primary key auto_increment,
dname varchar(30)
);
//职员表
create table emp(
eid int primary key auto_increment,
ename varchar(20),
salaly double,
dno int
);
//查看所有编码
show variables like 'char%';
insert into dept values(null,'研发部');
insert into dept values(null,'销售部');
insert into dept values(null,'人事部');
insert into emp(ename,salaly,dno) values('班长',10000,1);
insert into emp(ename,salaly,dno) values('美美',40000,1);
insert into emp(ename,salaly,dno) values('小凤',50000,1);
update emp set dno = 2 where ename='美美';
update emp set dno = 3 where ename='小凤';
mysql> select * from dept;
+-----+--------+
| did | dname |
+-----+--------+
| 1 | 研发部 |
| 2 | 销售部 |
+-----+--------+
2 rows in set (0.00 sec)
mysql> select * from emp;
+-----+-------+--------+------+
| eid | ename | salaly | dno |
+-----+-------+--------+------+
| 1 | 班长 | 10000 | 1 |
| 2 | 美美 | 40000 | 2 |
| 3 | 小凤 | 50000 | 3 |
+-----+-------+--------+------+
3 rows in set (0.00 sec)
像这种情况明显不合理 第一张表是部门表 而第二张员工表 员工表中小凤的这条数据dno对应的部门被
删掉明显不合理，随便删掉明显不合理
这中时候就需要多表设置外键来约束不能随便的删除一表的数据
多表设计——外键约束
作用：保证数据的完整性。
定义外键约束
可以直接在create语句中定义外键
foreign key 当前表名 (字段名) references 目标表名 (目标表的主
键)
create table emp(
eid int primary key auto_increment,
ename varchar(20),
salaly double,
dno int,
foreign key emp('dno') references dept('did');
);
create table renyuan
(
id int primary key ,
name varchar(20) ,
age int NOT NULL,
sex varchar(2) ,
address varchar(20) default '重庆'
) ;
insert into renyuan (id,name,age,sex) values(1,'小李',12,'男');
insert into renyuan (id,name,age,sex) values(2,'老王',32,'男');
修改人员表的默认 address默认约束字段为null；
ALTER TABLE renyuan CHANGE ename ename VARCHAR(20) NULL;
alter table renyuan change address address varchar(20) null;
create table score(
id int primary key,
sid int,
zh int,
en int,
math int,
constraint fk_sid foreign key(sid) references user(id)
);
insert into score values(1,2,45,56,34);
insert into score values(2,1,88,90,120);
alter table score drop foreign key fk_sid;
mysql> update emp set dno =3 where eid=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from emp;
+-----+-------+--------+-----+
| eid | ename | salaly | dno |
+-----+-------+--------+-----+
| 1 | 班长 | 10000 | 2 |
| 2 | 美美 | 40000 | 1 |
| 3 | 小凤 | 50000 | 3 |
+-----+-------+--------+-----+
3 rows in set (0.00 sec)
mysql> desc emp;
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| eid | int(11) | NO | PRI | NULL | auto_increment |
| ename | varchar(20) | YES | | NULL | |
| salaly | double | YES | | NULL | |
| dno | int(11) | NO | MUL | NULL | |
+--------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
mysql> delete from dept where did = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constrai
nt fails (day16.emp, CONSTRAINT emp_ibfk_1 FOREIGN KEY (dno) REFERENCES
dept (did))
mysql>
多表的查询
笛卡尔积的概念：（了解）
举个栗子
表a 表b
aid aname bid bname
a1 aa1 b1 bb1
a2 aa2 b2 bb2
b3 bb3
查询的语法
select * from a,b;
这么写返回的结果就是笛卡尔积
a1 aa1 b1 bb1
a1 aa1 b2 bb2
a1 aa1 b3 bb3
a2 aa2 b1 bb1
a2 aa2 b2 bb2
a2 aa2 b3 bb3
这记过就是笛卡尔积 2*3 = 6条结果
这就是多表查询啊哈哈哈哈
select * from dept ,emp where dept.did=emp.eid;
select * from dept inner join emp on dept.did=emp.eid && salaly = 10000;
*多表查询
* 内连接（用的比较多）
* 普通内连接
前提条件:需要有外键的
关键字 inner join...on
select * from dept inner join emp on dept.did=emp.eid;
* 隐式内连接（用的是最多的）
select * from dept,emp where dept.did=emp.eid;
*外连接
左外连接
select * from dept left outer join emp on dept.did=emp.eid;
select * from dept left join emp on dept.did=emp.eid;
*右外连接
insert into dept values(null,'呵呵部');
insert into dept values(null,'卫生部');
select * from dept;
select * from dept left join emp on dept.did=emp.eid;
select * from dept right join emp on dept.did=emp.eid;
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| eid | int(11) | NO | PRI | NULL | auto_increment |
| ename | varchar(20) | YES | | NULL | |
| salaly | double | YES | | NULL | |
| dno | int(11) | NO | MUL | NULL | |
+--------+-------------+------+-----+---------+----------------+
insert into emp values(null,'呵呵部');
insert into emp values(null,'马云',2565000000,null);
insert into emp values(null,'马化腾',20000000000,null);
insert into emp values(null,'马克龙',1000000000,null);
insert into emp values(null,'马蓉',40000000,null);
5 rows in set (0.00 sec)
mysql> select * from dept left join emp on dept.did=emp.dno;
+-----+--------+------+-------+------------+------+
| did | dname | eid | ename | salaly | dno |
+-----+--------+------+-------+------------+------+
| 1 | 研发部 | 6 | 阿冷 | 40000 | 1 |
| 2 | 销售部 | 1 | 马云 | 2565000000 | 2 |
| 3 | 人事部 | 5 | 小凤 | 50000 | 3 |
| 3 | 人事部 | 7 | 提莫 | 100000 | 3 |
| 6 | 呵呵部 | NULL | NULL | NULL | NULL |
| 7 | 卫生部 | NULL | NULL | NULL | NULL |
+-----+--------+------+-------+------------+------+
6 rows in set (0.00 sec)
mysql> select * from dept right join emp on dept.did=emp.dno;
+------+--------+-----+--------+-------------+------+
| did | dname | eid | ename | salaly | dno |
+------+--------+-----+--------+-------------+------+
| 1 | 研发部 | 6 | 阿冷 | 40000 | 1 |
| 2 | 销售部 | 1 | 马云 | 2565000000 | 2 |
| 3 | 人事部 | 5 | 小凤 | 50000 | 3 |
| 3 | 人事部 | 7 | 提莫 | 100000 | 3 |
| NULL | NULL | 2 | 马化腾 | 20000000000 | NULL |
| NULL | NULL | 3 | 马克龙 | 1000000000 | NULL |
| NULL | NULL | 4 | 马蓉 | 40000000 | NULL |
+------+--------+-----+--------+-------------+------+
7 rows in set (0.00 sec)
mysql>
select * from dept,emp where dept.did = emp.dno;
select d.dname,count(*) from dept d ,emp e where d.did = e.dno group by d.dname;
网友评论