本文详细介绍了SQL中的表结构创建、主键、外键约束，以及各种类型的连接（内连接、左外连接、右外连接），并展示了如何在员工表和部门表中实现一对一和多对多关系，以及执行SQL查询的操作实例。
-- 员工表
-- 如果存在这个表就删除
drop table if exists emp;
-- 创建表
create table emp(
id int primary key auto_increment,#主键且自增长
ename varchar(50) not null unique,#员工姓名，非空并且唯一
joindate date not NULL,#非空
salary double (7,2) not null ,#非空
bonus double(7,2) default 0 #设置默认值为0
);
insert into emp(id ,ename,joindate,salary,bonus) values (1,'张三','1999-11-11',8800,5000)
-- 演示主键约束：非空且唯一 下面的演示id不能等于null，会报错
insert into emp(id ,ename,joindate,salary,bonus) values (null,'张三','1999-11-11',8800,5000)
-- 在添加一个id为1的学员信息，仍会报错，因为不唯一了
insert into emp(id ,ename,joindate,salary,bonus) values (1,'张三','1999-11-11',8800,5000)
-- 在添加一位学员信息
insert into emp(id ,ename,joindate,salary,bonus) values (2,'李四','1999-11-11',8800,5000)
-- 演示非空约束，名字不能为空，这样会报错
insert into emp(id ,ename,joindate,salary,bonus) values (3,null,'1999-11-11',8800,5000)
-- 唯一约束，仍会报错（名字重复了）
insert into emp(id ,ename,joindate,salary,bonus) values (3,'李四','1999-11-11',8800,5000)
-- 演示默认约束，不添加默认值的时候才能生效
insert into emp(id ,ename,joindate,salary) values (3,'王五','1999-11-11',8800);
-- 如果改为null的话，他就不会走默认值0了
insert into emp(id ,ename,joindate,salary,bonus) values (4,'赵六','1999-11-11',8800,null)
-- 演示自动增长：当列是数字类型并且唯一约束
insert into emp(ename,joindate,salary,bonus) values ('李四','1999-11-11',8800,5000)
insert into emp(id,ename,joindate,salary,bonus) values (null,'李四2','1999-11-11',8800,5000)
insert into emp(id,ename,joindate,salary,bonus) values (null,'李四3','1999-11-11',8800,5000)
SELECT * from emp
-- 创建部门表
create table dept (
id int PRIMARY key auto_increment,
dep_name varchar(20),
addr varchar(20)
);
drop table emp
-- 员工表
create table emp (
id int primary key auto_increment,
name varchar(20),
age int,
dep_id int,
-- 添加外键dep_di关联，dept 表的id主键,当添加外键之后，就不可以删除部门的信息了，因为外键限制，必须要把该部门的学员信息给删除了，才能删除该表。
CONSTRAINT fk_emp_dept foreign key (dep_id) REFERENCES dept(id)
);
-- 添加2个部门
insert into dept (dep_name,addr) values ('研发部','广州'),('销售部','深圳')
-- 添加员工,dep_id 表示员工所在部门
insert into emp (name,age, dep_id) values ('张三',20,1),('李四',20,1),('王五',20,1),('赵六',20,2),('孙七',22,2),('周八',18,2)
select * from emp
select * from dept
-- 删除外键
alter table emp drop foreign key fk_emp_dept;
-- 建完表后，添加外键
alter table emp add constraint fk_emp_dept foreign key(dep_id) REFERENCES dept(id)
-- 多对多
-- 订单表
drop table tb_order
create table tb_order(
id int primary key auto_increment,
payment double (10,2),
payment_type tinyint,
status tinyint
);
-- 商品表
drop table tb_goods
create table tb_goods(
id int primary key auto_increment,
title varchar(100),
price double(10,2)
);
-- 订单商品中间表
create table tb_order_goods(
id int primary key auto_increment,
-- 订单表的id字段
order_id int,
-- 商品表的id字段
goods_id int,
count int
);
-- 添加外键
alter table tb_order_goods add constraint fk_order_id foreign key (order_id) references tb_order(id)
alter table tb_order_goods add constraint fk_goods_id foreign key (goods_id) references tb_goods(id)
-- 删除外键
alter table tb_order_goods drop foreign key fk_goods_id
-- 一对一
-- 创建"users"表，存储用户信息
CREATE TABLE users (
id INT PRIMARY KEY,
username VARCHAR(255),
email VARCHAR(255),
password VARCHAR(255)
);
-- 创建"profiles"表，存储用户的个人资料信息
CREATE TABLE profiles (
id INT PRIMARY KEY,
unique_user_id INT UNIQUE,
full_name VARCHAR(255),
address VARCHAR(255),
phone_number VARCHAR(255),
FOREIGN KEY (unique_user_id) REFERENCES users(id)
);
-- 多表查询
-- 删除表
drop table emp
drop table dept
-- 创建部门表
create table dept(
did int primary key auto_increment,
dname varchar(20)
);
-- 创建员工表
create table emp(
id int primary key auto_increment,
name varchar(10),
gender char(1),
salary double,
join_date date,
dep_id int,
foreign key(dep_id) references dept (did)#外键，关联部门表（部门表的主键）这个并没有定义外键名称
);
-- 添加部门数据
insert into dept(dname) values ('研发部'),('市场部'),('财务部'),('销售部');
-- 添加员工数据
insert into emp(name,gender,salary,join_date,dep_id) values
('孙悟空','男',7200,'2013-02-24',1),
('猪八戒','男',3600,'2010-12-02',2),
('唐僧','男',9000,'2008-08-08',2),
('白骨精','女',5000,'2015-10-07',3),
('蜘蛛精','女',4500,'2011-03-14',1),
('小白龙','男',2500,'2011-02-14',null);
select * from dept;
select * from emp;
-- 消除无效数据
select * from emp,dept where emp.dep_id=dept.did;
-- 查询emp和dept 的数据，要求emp.dep_id=dept.did 
-- 隐式内连接
#查询全部
select * from emp,dept where emp.dep_id =dept.did
-- 查询emp的name,gender, dept表的dname
select emp.name,emp.gender,dept.dname from emp,dept where emp.dep_id =dept.did
-- 字段太长，给表起名
select t1.name,t1.gender,t2.dname from emp t1,dept t2 where t1.dep_id =t2.did
-- 显示内连接、
select * from emp inner join dept on emp.dep_id=dept.did;
-- 左外连接
-- 查询emp表中所有的数据和对应的部门信息
select * from emp left join dept on emp.dep_id=dept.did;
-- 右外连接
-- 查询dept表中所有的数据和对应的部门信息
select * from emp right join dept on emp.dep_id=dept.did;
-- 查询八戒工资
select salary from emp where name ='猪八戒'
-- 查询工资高于八戒的员工信息
select * from emp where salary > 3600;
-- 综合
SELECT * from emp where salary > (select salary from emp where name ='猪八戒');
-- 单行单列
-- 查询财务部的员工信息
select * from dept where dname ='财务部'
select * from emp where dep_id=3
-- 综合
-- 这个可以，外部查询期望子查询只返回一个列的结果
select * from emp where dep_id=(select did from dept where dname ='财务部')
-- 这个不行
select * from emp where dep_id=(select * from dept where dname ='财务部')
-- 多行单列
-- 查询财务部和市场部的所有员工信息
select did from dept where dname='财务部' or dname='市场部'
select * from emp where dep_id in (2,3)
select * from emp where dep_id in (select did from dept where dname='财务部' or dname='市场部')
-- 查询入职日期是2011-11-11之后的员工信息
select * from emp where join_date>'2011-11-11'实操
-- 部门表
create table dept (
id int primary key ,#部门id
dname varchar(50), #部门名称
loc varchar(50)#部门所在地
);
-- 职务表，职务名称，职务描述
create table job(
id int primary key ,
jname varchar(20),
description varchar(50)
);
-- 员工表
drop table emp
create table emp(
id int primary key,#员工id
ename varchar(50),#员工姓名
job_id int ,# 职务id
mgr int ,#上级领导
joindate date, #入职日期
salary decimal(7,2),#工资
bonus decimal(7,2),#奖金
dept_id int,#所在的部门编号
constraint emp_jobid_ref_job_id_fk foreign key (job_id) references job(id),
constraint emp_deptid_ref_dept_id_fk foreign key (dept_id) references dept(id)
);
-- 工资等级表
create table salarygrade(
grade int primary key,#级别
losalary int, #最低工资
hisalary int #最高工资
);
-- 添加4个部门
insert into dept (id,dname,loc) values
(10,'教研部','北京'),
(20,'学工部','上海'),
(30,'销售部','广州'),
(40,'财务部','深圳');
-- 添加4个职务
insert into job(id,jname,description) values
(1,'董事长','管理整个公司,接单'),
(2,'经理','管理部门员工'),
(3,'销售员','向客人推销产品'),
(4,'文员','使用办公软件');
-- 添加成员
insert into emp (id,ename,job_id,mgr,joindate,salary,bonus,dept_id) values
(1001,'孙悟空',4,1004,'2000-12-17','8000.00',Null ,20),
(1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),
(1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30),
(1004,'唐僧',2,1009,'2001-04-02','29750.00',null,20),
(1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),
(1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30),
(1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10),
(1008,'猪八戒',4,1004,'2007-04-19','30000.00',NULL,20),
(1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10),
(1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30),
(1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20),
(1012,'李逵' ,4,1006,'2001-12-03 ','9500.00',NULL,30),
(1013,'小白龙',4,1004,'2001-12-03' ,'30000.00',NULL,20),
(1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10);
insert into salarygrade (grade,losalary,hisalary) values
(1,7000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);
-- 一、查询所有员工的信息、查询员工编号、员工姓名、工资、职务名称、职务描述
-- 1员工编号、员工姓名、工资在emp表中
-- 2职务名称、职务描述在job职务表中
-- 3job职务表和emp员工表中是一对多的关系 emp.job_id=job.id
-- 隐式内连接
select emp.id,emp.ename,emp.salary,job.jname,job.description from emp ,job where emp.job_id =job.id;
-- 显示内连接
select emp.id,emp.ename,emp.salary,job.jname,job.description from emp inner join job On emp.job_id =job.id;
-- 二、查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
-- 1员工编号、员工姓名、工资在emp表中
-- 2职务名称、职务描述在job职务表中
-- 3job职务表和emp员工表中是一对多的关系 emp.job_id=job.id
-- 4部门名称，部门位置，来自于部门表dept
-- 5dept和emp是一对多的关系，dept.id=emp.dept_id
-- 隐式内连接
select emp.id ,emp.ename,emp.salary,job.jname,job.description,dept.dname,dept.loc from emp,dept,job where emp.job_id =job.id and emp.dept_id=dept.id
-- 显示内连接
select emp.id,emp.ename,emp.salary,job.jname,job.description,dept.dname,dept.loc from emp inner join job On emp.job_id =job.id inner join dept On emp.dept_id=dept.id
-- 三、查询员工姓名，工资，工资等级
-- 1员工姓名、工资在emp表中
-- 2工资等级在salarygrade中
-- 3emp.salary >=salarygrade.losalary and emp.salary <=salarygrade.hisalary
-- 隐式内连接
select emp.ename,emp.salary,t2.grade from emp,salarygrade t2 where emp.salary >=t2.losalary and emp.salary <=t2.hisalary
-- 四、查询员工姓名、工资、职务名称、职务描述、部门名称、部门位置、工资等级
-- 1员工编号、员工姓名、工资在emp表中
-- 2职务名称、职务描述在job职务表中
-- 3job职务表和emp员工表中是一对多的关系 emp.job_id=job.id
-- 4部门名称，部门位置，来自于部门表dept
-- 5dept和emp是一对多的关系，dept.id=emp.dept_id
-- 6工资等级在salarygrade中
-- 7emp.salary >=salarygrade.losalary and emp.salary <=salarygrade.hisalary
SELECT
emp.id,
emp.ename,
emp.salary,
job.jname,
job.description,
dept.dname,
dept.loc,
t2.grade
FROM
emp
INNER JOIN job ON emp.job_id = job.id
INNER JOIN dept ON emp.dept_id = dept.id
inner join salarygrade t2 on emp.salary >=t2.losalary and emp.salary <=t2.hisalary
-- 五、查询出部门编号、部门名称、部门位置、部门人数
-- 1.部门编号、部门名称、部门位置来自与部门dept表
-- 2.部门人数：在emp表中，按照dept_id进行分组，然后用count(*)统计数量
-- 3使用子查询，让部门表和分组后的表进行内连接
select * from dept
select dept_id,count(*) from emp group by dept_id
select * from dept,(select dept_id,count(*) from emp group by dept_id) t1 where dept.id =t1.dept_id
-- 最终版
select dept.id,dept.dname,dept.loc,t1.count from dept,(select dept_id,count(*) count from emp group by dept_id) t1 where dept.id =t1.dept_id
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
