两年

V1

2022/09/05阅读:23主题:橙心

mysql基础知识-需求案例2

”种一棵树最好是十年前,其次是现在“,结合个人十多年IT基础架构领域摸爬滚打的经验来看,数据库领域潜力无限,大有可为。运维领域知识面需要广,更需要专,数据库是我选择做专做深的方向。

今天继续来分享一下涉及到基础知识的基础案例,通过案例,如果能够独立的完成这些案例,就说明自己已经掌握了这方面的知识。这是一种快速高效的学习方法!

-- 约束


-- 建表时对字段进行约束
create table user(
    id int primary key auto_increment comment '主键',
    name varchar(10not null unique comment '姓名',
    age int comment '年龄',
    status char(1default '1' comment '状态',
    gender char(1comment '性别'
comment '用户表';

-- 保证能够支持中文数据插入
alter table user convert to character set utf8mb4;
-- 插入数据
insert into user(name, age, status, gender) values ('Tom','20','1','男');
insert into user(name, age, status, gender) values ('jack','22','1','女');
insert into user(name, age, status, gender) values ('Tom','22','1','女');

alter table empploy add dept_id int comment '部门编号';

create table dept(
    id int primary key auto_increment comment '部门编号',
    dept_name varchar(10comment '部门名称'
)comment '部门表';

alter table dept convert to character set utf8mb4;
insert into dept(dept_name) values ('市场部');
insert into dept(dept_name) values ('财务部');
insert into dept(dept_name) values ('技术部');
insert into dept(dept_name) values ('销售部');
insert into dept(dept_name) values ('行政部');
insert into dept(dept_name) values ('总裁办');

-- 添加外键

-- 默认是NO-ACTION和RESTRICT,修改为CASCADE
alter table empploy add constraint fk_empploy_dept_id foreign key(dept_id) references dept(id)
    on update cascade on delete cascade ;

alter table empploy drop foreign key fk_empploy_dept_id;

--多表查询

create table student(
    id int primary key auto_increment comment '主键id',
    name varchar(20not null unique comment '姓名',
    number int comment '学生编号'
)comment '学生表';
alter table student convert to character set utf8mb4;
insert into student(namenumber)  values('张三','0011002'),('李四','0011003'),('王五','0011004'),('赵六','0011005');

create table course(
    id int primary key auto_increment comment '主键id',
    name varchar(10comment '课程名'
)comment '课程表';
alter table course convert to character set utf8mb4;
insert into course values (null,'英语'),(null,'数学'),(null,'语文'),(null,'物理'),(null,'体育'),(null,'自然科学');

create table student_course(
    id int primary key auto_increment comment '主键id',
    student_id int comment '学生表主键',
    course_id int comment '课程表主键',
    constraint fk_student_id foreign key (student_id) references student(id),
    constraint fk_course_id foreign key (course_id) references course(id)
)comment '学生课程关系表';
insert into student_course values (null,1,1),(null,1,3),(null,2,1),(null,3,1),(null,4,2),(null,3,4),(null,3,6);

-- 多表查询,消除无效的笛卡尔积
select * from empploy,dept where empploy.id=dept.id;

-- 内连接,查询两个表的交集部分:隐式和显示两种方式
-- 查询每一个员工的姓名,及关联的部门名称(隐式内连接实现)
select username,dept_name from empploy e,dept d where e.id=d.id;
-- 查询每一个员工的姓名,及关联的部门名称(显式内连接实现)
select username,dept_name from empploy e inner join dept d on e.dept_id = d.id;

-- 外连接

-- 查询empploy表的用户和dept对应的部门信息(左外连接)
select e.username,d.dept_name from empploy e left join dept d on d.id = e.dept_id;
-- 查询dept表的信息和empploy对应的员工信息(右外连接)
select d.*,e.username from dept d right join empploy e on d.id = e.dept_id;

-- 联合查询

-- 查询所有男员工,和年龄小于28的员工全部查询出来
select * from empploy where sex='男'
union
select * from empploy where age<28;

-- 子查询

-- 列子查询
-- 查询”销售部“和”市场部“的所有员工信息
select * from empploy where dept_id in (select id from dept where (dept_name='销售部' or dept_name='市场部'));
-- 查询与张无忌薪资和直属领导相同的员工信息
select * from empploy where (selery,managerid) = (select selery,managerid from empploy where username='张无忌');
-- 表子查询
-- 查询与张三丰和张无忌职位和薪资相同的员工信息
select * from empploy where (selery,job) in (select selery,job from empploy where username = '张三丰' or username = '张无忌');
-- 查询入职日期”2016-01-03“之前的的员工信息及部门信息
select e.*,d.* from (select * from empploy where entrydate > '2016-01-03') e left join dept d on e.dept_id=d.id;

-- 事务

-- 创建事务演示表
create table account(
    id int auto_increment primary key comment '编号',
    username varchar(10comment '姓名',
    money int comment '金钱余额'
)comment '账户表';

alter table account change name username varchar(10);
alter table account convert to character set utf8mb4;
insert into account(id, username, money) values (null,'张三',2000),(null,'李四',2000);

-- 事务操作:

-- 控制事务方式一
select @@autocommit;
set @@autocommit = 0;
-- 手动提交事务
commit;
-- 回滚事务
rollback;

show databases ;
use my_test;
-- 具体事务示例
-- 0.查询张三账户余额
select * from account where username = '张三';
-- 1.给张三账户转账1000元
update account set money = money + 1000 where username = '张三';
-- 2.李四账户减掉1000元
update account set money = money - 1000 where username = '李四';

-- 控制事务方式二,不改变事务默认控制方式
select @@autocommit;
set @@autocommit = 1;
-- 开启事务
start transaction ;
-- 0.查询张三账户余额
select * from account where username = '张三';
-- 1.给张三账户转账1000元
update account set money = money + 1000 where username = '张三';
-- 2.李四账户减掉1000元
update account set money = money - 1000 where username = '李四';
-- 手动提交事务
commit ;
-- 回滚事务
rollback ;

分类:

后端

标签:

数据库

作者介绍

两年
V1