用java类来设计表
?根据java类来设计表
做一个项目的时候首先需要对数据进行建模
?
// 多对一?? 员工--部门
drop table if exists employee;
// 建表
create table department
(
?????? id int primary key auto_increment,
?????? name varchar(20)
);
?
?
create table employee
(
?????? id int primary key auto_increment,
?????? name varchar(20),
?????? departmentid int,
?????? constraint departmentid_FK foreign key(departmentid) references department(id)
);
?
// 插入数据
insert into department (name) values('开发部');
insert into department (name) values('销售部');
insert into department (name) values('人事部');
?
insert into employee(name,departmentid) values('张三', 1);
insert into employee(name,departmentid) values('李四', 1);
insert into employee(name,departmentid) values('王五', 2);
insert into employee(name,departmentid) values('赵六', 2);
insert into employee(name,departmentid) values('田七', 3);
?
?
insert into employee(name,departmentid) values('田七', 4); 错
?
insert into employee(name) values('小红');
?
// 多对多
create table teacher
(
?????? id int primary key auto_increment,
?????? name varchar(20)
);
?
create table student
(
?????? id int primary key auto_increment,
?????? name varchar(20)??
);
?
中间关系表
create table tea_stu
(
?????? stuid int,
?????? teaid int,
?????? primary key(stuid,teaid),
?????? constraint stuid_FK foreign key(stuid) references student(id),
?????? constraint teaid_FK foreign key(teaid) references teacher(id)
);
?
// 插入数据
insert into student(name) values('张三');
insert into student(name) values('李四');
insert into student(name) values('王五');
insert into student(name) values('赵六');
insert into student(name) values('田七');
insert into student(name) values('周八');
?
insert into teacher(name) values('老张');
insert into teacher(name) values('老方');
?
// 插入中间表
insert into tea_stu (stuid,teaid) values(1,1);
insert into tea_stu (stuid,teaid) values(1,2);
insert into tea_stu (stuid,teaid) values(2,1);
insert into tea_stu (stuid,teaid) values(5,1);
insert into tea_stu (stuid,teaid) values(6,1);
insert into tea_stu (stuid,teaid) values(4,2);
insert into tea_stu (stuid,teaid) values(3,2);
?
// 一对一
create table people
(
?????? id int primary key auto_increment,
?????? name varchar(20)
);
?
create table idcard
(
?????? id int primary key,
?????? location varchar(20),
?????? constraint peopleid_FK foreign key(id) references people(id)????
);
?
// 插入数据
insert into people(name) values('张三');
insert into people(name) values('李四');
insert into people(name) values('王五');
?
insert into idcard(id,location) values(1,'天津');
insert into idcard(id,location) values(2,'北京');
insert into idcard(id,location) values(3,'上海');
?
九 、多表查询
// 查出1号部门所有的员工
select * from employee where departmentid=1;
?
?
?
题目：? 查出开发部所有的员工
select id from department where name='开发部';
// 子查询
select * from employee where departmentid=(select id from department where name='开发部');
?
// 一条语句查出来 就需要查两张表
select * from employee,department;
?
| id | name | departmentid | id | name?? |
+----+------+--------------+----+--------+
|? 1 | 张三???? |??????????? 1 |? 1 | 开发部?????? |
|? 1 | 张三???? |??????????? 1 |? 2 | 销售部????? |
|? 1 | 张三???? |??????????? 1 |? 3 | 人事部? ????|
|? 2 | 李四???? |??????????? 1 |? 1 | 开发部?????? |
|? 2 | 李四???? |??????????? 1 |? 2 | 销售部????? |
|? 2 | 李四???? |??????????? 1 |? 3 | 人事部????? |
|? 3 | 王五???? |??????????? 2 |? 1 | 开发部?????? |
|? 3 | 王五???? |??????????? 2 |? 2 | 销售部????? |
|? 3 | 王五??? ?|??????????? 2 |? 3 | 人事部????? |
|? 4 | 赵六???? |??????????? 2 |? 1 | 开发部?????? |
|? 4 | 赵六???? |??????????? 2 |? 2 | 销售部????? |
|? 4 | 赵六???? |??????????? 2 |? 3 | 人事部????? |
|? 5 | 田七???? |??????????? 3 |? 1 | 开发部?????? |
|? 5 | 田七???? |??????????? 3 |? 2 | 销售部????? |
|? 5 | 田七???? |??????????? 3 |? 3 | 人事部????? |
|? 6 | 小红??? |???????? NULL |? 1 | 开发部?????? |
|? 6 | 小红??? |???????? NULL |? 2 | 销售部????? |
|? 6 | 小红??? |???????? NULL |? 3 | 人事部????? |
?
笛卡尔积： 多张表所有的记录排列组合的结果
假如 a表(3条记录)和b表(4条记录)? 查出来有 3*4=12 条记录
?
在笛卡尔积中有很多无用(错误)的数据(废数据)， 需要想办法剔除
只有外键列的值和被参照列的值相等的记录才是有效的
剔除废数据的条件就是 外键列=被参照列
?
select * from employee,department where employee.departmentid=department.id;
?
+----+------+--------------+----+--------+
| id | name | departmentid | id | name?? |
+----+------+--------------+----+--------+
|? 1 | 张三???? |??????????? 1 |? 1 | 开发部?????? |
|? 2 | 李四???? |??????????? 1 |? 1 | 开发部?????? |
|? 3 | 王五???? |??????????? 2 |? 2 | 销售部????? |
|? 4 | 赵六???? |??????????? 2 |? 2 | 销售部????? |
|? 5 | 田七???? |??????????? 3 |? 3 | 人事部????? |
+----+------+--------------+----+--------+
剔除了废数据的结果，也就是数据都是有效的
在这个前提下再加条件，该查什么就查什么
?
select employee.* from employee,department where employee.departmentid=department.id and department.name='开发部';
?
// 多表查询 习惯性地会为表起别名
select e.* from employee e,department d where e.departmentid=d.id and d.name='开发部';
?
思考： 查张三是哪个部门的
?
题目： 查三张表
1) 查1号老师的学生的id
select * from tea_stu where teaid=1;
?
2) 查1号老师的学生的信息
select s.* from tea_stu ts,student s where ts.stuid=s.id and ts.teaid=1;
?
3) 查出老方的学生的信息
select s.* from tea_stu ts, student s, teacher t where ts.teaid=t.id and ts.stuid=s.id and t.name='老方';
?
// n张表联合查询，需要写n-1个条件去除废数据??
// 将所有的外键列和参照列去相等比较
// 剩下的该写什么条件写什么条件
// 多表查一定要给表起别名