数据库学习＞2.24

均来自B站黑马程序员JavaWeb课程，为整理供个人使用的笔记。大多为课程ppt截图和课例代码。​​​​​​​

 一、约束

CREATE TABLE emp (
id INT PRIMARY KEY , --员工id，主键且自增长
ename VARCHAR(50) NOT NULL UNIQUE，--员工姓名，非空并且唯一
joindate DATE NOT NULL，--入职日期，非空
salary DOUBLE(7,2) NOT NULL ，--工资，非空
bonus DOUBLE(7,2) DEFAULT 0 --奖金，如果没有奖金默认为0
);

-- 演示自动增长：auto_increment：当列是数字类型并且唯一约束

 

--员工表
CREATE TABLE emp (
    id int primary key auto_increment,
    name varchar(20),
    age int,
    dep_id int,

--添加外键dep_id,关联dept表的id主键
CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES dept(id)
);

--删除外键
alter table emp drop FOREIGN KEY fk_emp_dept;

--建完表后，添加外键
alter table emp add CONSTRAINT fk_emp_dept FOREIGN key(dep_id) REFERENCES dept(id);

二、数据库设计 

--订单表
CREATE TABLE tb_order (
    id int primary key auto_increment,
    payment double(10,2),
    payment_ type TINYINT,
    status TINYINT
);

--商品表
CREATE TABLE tb_goods (
    id int primary key auto_increment,
    title varchar(100),
    price doub1e(10,2)
);

--订单商品中间表
CREATE TABLE tb_order_goods(
    id int primary key auto incrementr，
    order_id int,
    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);

 

 三、多表查询

 

select * from emp;

--多表查询
select * from emp ， dept;

--笛卡尔积:有A,B两个集合,取A,B所有的组合情况
--消除无效数据
--查询 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 emp.dep_id = dept.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;

--查询工资高于猪八戒的员工信息

--1.查询猪八戒的工资
select salary from emp where name = '猪八戒';

--2.查询工资高于猪八戒的员工信息
select * from emp where salary > 3600;

--合二为一
select * from emp where salary > (select salary from emp where name = '猪八戒');

--查询财务部和市场部所有的员工信息
--1.查询财务部所有的员工信息
select did from dept where dname = '财务部';
select * from emp where dep_id =(select did from dept where dname = '财务部');
--总
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'; --只是emp表
select * from emp, dept where emp.dep_id = dept_did; --只是两表交集，还需要emp有限制条件
select * from （select * from emp where join_date > '2011-11-11'） t1, dept where t1.dep_id = dept_did;

多表查询案例

--1.查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述
/*分析:
    1.员工编号，员工姓名，工资 信息在emp员工表中
    2.职务名称，职务描述 信息在job职务表中
    3.job职务表 和emp员工表 是一对多的关系 emp.job_id=job.id
    4.部门名称，部门位置 来自于 部门表dept
    5.dept 和 emp 是一对多的关系，dept.id=emp.dept_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;


--2.查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
--隐式内连接
select emp.id, emp.ename, emp.salary, job.jname, job.description, dept.dname, dept.loc  
from emp, job, dept 
where emp.job_id = job.id and dept.id = emp.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 dept.id = emp.dept_id;


--3.查询员工姓名，工资，工资等级
/*分析:
    1.员工姓名，工资信息在emp员工表中
    2.工资等级信息在salarygrade 工资等级表中
    3. emp.salary >= salarygrlde.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;


--4.查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
/*分析:
    1.员工编号，员工姓名，工资信息在 emp员工表 中
    2.职务名称，职务描述，信息在 job职务表 中
    3.job 职务表和 emp员工表是一-对多的关系 emp.job_ id = job.id
    4.部门名称，部门位置来自于部门表dept
    5.dept.和 emp 一对多关系 dept.id = emp.dept_ id
    6.工资等级信息在salarygrade 工资等级表中
    7.emp.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 dept.id = emp.dept_id
inner join salarygrade t2 on emp.salary betweem t2.losalary and t2.hisalary;

--5.查询出部门编号、部门名称、部门位置、部门人数
/*分析:
    1.部门编号、部门名称、部门位置 来自于部门 dept 表
    2.部门人数:在emp表中 按照dept_id 进行分组，然后count(*)统计数量
    3.使用子查询，让部门表和分组后的表进行内连接
*/
select * from dept;
select dept_id, count(*) from emp group by dept_id;

select dept.id, dept.dname, dept.loc, t1.count 
from dept, (select dept_id, count(*) from emp group by dept_id) t1 
where dept.id = t1.dept_id;

四、事务