java 设计一个信息表,用java种来设计表

用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个条件去除废数据

//将所有的外键列和参照列去相等比较

//剩下的该写什么条件写什么条件

//多表查一定要给表起别名