MySQL 数据库设计 （公司内部结构）

# 创建部门表
CREATE TABLE dept (
	id INT UNSIGNED auto_increment KEY,
	deptno VARCHAR ( 20 ),
	deptname VARCHAR ( 20 ),
	sta bit DEFAULT 1,
	isdel bit DEFAULT 0,
	parentid INT UNSIGNED,
	create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 
);
# 创建员工表
CREATE TABLE empoly (
	id INT UNSIGNED auto_increment KEY,
	NAME VARCHAR ( 10 ),
	mobile VARCHAR ( 11 ),
	empno VARCHAR ( 20 ),
	deptid INT UNSIGNED,
	create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 
);
#创建客户表
CREATE TABLE customer (
	id INT UNSIGNED auto_increment KEY,
	NAME VARCHAR ( 20 ),
	mobile VARCHAR ( 11 ),
	createuserid INT UNSIGNED,
	empno VARCHAR ( 20 ),
	create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 
);



#插入一级部门 

insert into dept(deptno,deptname,parentid) values('1','北游',0);

#插入二级部门 
insert into dept(deptno,deptname,parentid) values('101','市场部',1),('102','教学部',1),('103','学管部',1);

#插入三级部门
insert into dept(deptno,deptname,parentid) values('10101','市场一部',2),('10102','市场一部',2),('10103','市场三部',2);


#插入员工 
insert into empoly(name,mobile,empno,deptid) values('王老师','138823823','10201',3),('赵老师','15823823','10202',3);

#插入市场部 
insert into empoly(name,mobile,empno,deptid) values('杨老师','186823823','10101',2);

#插入市场一部
insert into empoly(name,mobile,empno,deptid) values('宋老师','157823823','1010101',5),('张老师','157823823','1010102',5);

#插入市场二部
insert into empoly(name,mobile,empno,deptid) values('唐老师','13823823','1010201',6),('李老师','157823823','1010202',6);


# 市场一部 宋老师 客户 
insert into customer(name,mobile,createuserid,empno) values('张三','1593943934',4,'1010101'),('李四','13843934',4,'1010101');

#市场一部 张老师 客户 
insert into customer(name,mobile,createuserid,empno) values('小明','156943934',5,'1010102'),('小红','13843934',5,'1010102');


#市场二部  唐老师 客户
insert into customer(name,mobile,createuserid,empno) values('lily','18743934',6,'1010201');

其中 部门表与员工表有关联，员工表和客户表有关联

select * from dept;
select * from customer;
select * from empoly;
#查询指定部门员工数据
select * from empoly where deptid=3
#查询市场部所有员工
# 1 查询市场部下所有部门 GROUP_CONCAT(id) 查询的所有的结果的id
select GROUP_CONCAT(id) from dept where deptname like '市场%'

select * from dept where deptname like '市场%'

#查找员工

select * from empoly where deptid in (2 ,5, 6, 3)

#子查询

select * from empoly where deptid in (select id from dept where deptname like '市场%')
# 修改员工表，增加薪水  DECIMAL 十进制

ALTER table empoly add money DECIMAL(10) default 1000

select * from empoly

#查询工资最高的员工
#不使用多表查询时，先查找工资最高的金额,然后根据金额查询员工信息

select MAX(money) money from empoly

select * from empoly where money = 100000
# 子查询
select * from empoly where money =(select max(money) from empoly)

# 查询是教学部的 子查询

select * from empoly where deptid = (select id from dept where deptname ='教学部')

# 查询教学部的老师

select `NAME` from empoly where deptid = (select id from dept where deptname like '教学部')

#查询员工名称及部门名称 子查询  查询两个表需要建立连接
select e.`NAME`, (select d.deptname from dept d where d.id=e.deptid) deptname from empoly e

# 内连接查询员工名称，及所在部门

select e.`NAME`,d.deptname from empoly e INNER JOIN dept d ON e.deptid=d.id