MySQL数据库从小白到入门(二)

这篇具有很好参考价值的文章主要介绍了MySQL数据库从小白到入门(二)。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

多表关系:


    项目开发中,在进行数据库表结构设计时,会根据业务需求及业务模块之间的关系,分析并设计表结构。由于业务之间相互关联,所以各个表结构之间也存在着各种联系,基本上分为三种。

            外键:

                创建表时添加外键:


   -- 创建表时指定
            create table 表名(
                    字段名 数据类型,
                    ...
                    [constraint] [外键名称] foreign key (外键字段名) references 主表 (字段名)    
                );

 示例SQL:

#创建一个用户表
create table tb_user(
    id int unsigned  primary key auto_increment comment 'ID',
    name varchar(10) not null comment '姓名',
    gender tinyint unsigned not null comment '性别, 1 男  2 女'
) comment '用户信息表';


# 创建一个 用户详细信息表
create table tb_user_card(
    id int unsigned  primary key auto_increment comment 'ID',
    nationality varchar(10) not null comment '民族',
    birthday date not null comment '生日',
    idcard char(18) not null comment '身份证号',
    issued varchar(20) not null comment '签发机关',
    expire_begin date not null comment '有效期限-开始',
    expire_end date comment '有效期限-结束',
    user_id int unsigned not null unique comment '用户ID',
    constraint fk_user_id foreign key (user_id) references tb_user(id)  //创建外键
) comment '用户信息表';


                创建完表之后添加外键:


                    -- 建完表后,添加外键


alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(字段名);


# 给emp表添加一个外键  一个部门对应多个员工,一个员工所属一个部门,dept表是父表,emp表是子表
alter table emp add  constraint fk_emp_dept_id foreign key(dept_id) references dept(id);

                多表操作中保证数据的一致性,完整性和正确性。

               物理外键:


    概念:使用foreign key定义外键关联另外一张表。
     缺点:
               影响增删改的效率(需要检查外键关系)
               仅用于单节点数据库,不适用于分布式,集群场景
               容易引发数据库死锁问题,消耗性能


                        逻辑外键:


                            概念:在业务逻辑层中,解决外键关联。
                            通过逻辑外键,就可以很方便的解决上述问题。


        一对多(多对一)


            部门和员工的关系(一个部门有多个员工)
                在数据库表中多的一方,添加字段,来关联一的一方的主键。

                                        emp表                                dept表

MySQL数据库从小白到入门(二),数据库,mysql,oracle

SQL代码示例:

CREATE TABLE dept (
  id int unsigned PRIMARY KEY AUTO_INCREMENT COMMENT 'ID, 主键',
  name varchar(10) NOT NULL UNIQUE COMMENT '部门名称',
  create_time datetime DEFAULT NULL COMMENT '创建时间',
  update_time datetime DEFAULT NULL COMMENT '修改时间'
) COMMENT '部门表';

INSERT INTO dept VALUES (1,'学工部','2023-09-25 09:47:40','2023-09-25 09:47:40'),
						  (2,'教研部','2023-09-25 09:47:40','2023-10-09 15:17:04'),
						  (3,'咨询部2','2023-09-25 09:47:40','2023-11-30 21:26:24'),
						  (4,'就业部','2023-09-25 09:47:40','2023-09-25 09:47:40'),
						  (5,'人事部','2023-09-25 09:47:40','2023-09-25 09:47:40'),
						  (15,'行政部','2023-11-30 20:56:37','2023-11-30 20:56:37');




create table emp(
    id int unsigned primary key auto_increment comment 'ID,主键',
    username varchar(20) not null unique comment '用户名',
    password varchar(50) default '123456' comment '密码',
    name varchar(10) not null comment '姓名',
    gender tinyint unsigned not null comment '性别, 1:男, 2:女',
    phone char(11) not null unique comment '手机号',
    job tinyint unsigned comment '职位, 1 班主任, 2 讲师 , 3 学工主管, 4 教研主管, 5 咨询师',
    salary int unsigned comment '薪资',
    image varchar(300) comment '头像',
    entry_date date comment '入职日期',
    dept_id int unsigned comment '部门ID',
    create_time datetime comment '创建时间',
    update_time datetime comment '修改时间'
) comment '员工表';


INSERT INTO emp VALUES 
(1,'shinaian','123456','施耐庵',1,'13309090001',4,15000,'5.png','2000-01-01',2,'2023-10-20 16:35:33','2023-11-16 16:11:26'),
(2,'songjiang','123456','宋江',1,'13309090002',2,8600,'01.png','2015-01-01',2,'2023-10-20 16:35:33','2023-10-20 16:35:37'),
(3,'lujunyi','123456','卢俊义',1,'13309090003',2,8900,'01.png','2008-05-01',2,'2023-10-20 16:35:33','2023-10-20 16:35:39'),
(4,'wuyong','123456','吴用',1,'13309090004',2,9200,'01.png','2007-01-01',2,'2023-10-20 16:35:33','2023-10-20 16:35:41'),
(5,'gongsunsheng','123456','公孙胜',1,'13309090005',2,9500,'01.png','2012-12-05',2,'2023-10-20 16:35:33','2023-10-20 16:35:43'),
(6,'huosanniang','123456','扈三娘',2,'13309090006',3,6500,'01.png','2013-09-05',1,'2023-10-20 16:35:33','2023-10-20 16:35:45'),
(7,'chaijin','123456','柴进',1,'13309090007',1,4700,'01.png','2005-08-01',1,'2023-10-20 16:35:33','2023-10-20 16:35:47'),
(8,'likui','123456','李逵',1,'13309090008',1,4800,'01.png','2014-11-09',1,'2023-10-20 16:35:33','2023-10-20 16:35:49'),
(9,'wusong','123456','武松',1,'13309090009',1,4900,'01.png','2011-03-11',1,'2023-10-20 16:35:33','2023-10-20 16:35:51'),
(10,'linchong','123456','林冲',1,'13309090010',1,5000,'01.png','2013-09-05',1,'2023-10-20 16:35:33','2023-10-20 16:35:53'),
(11,'huyanzhuo','123456','呼延灼',1,'13309090011',2,9700,'01.png','2007-02-01',2,'2023-10-20 16:35:33','2023-10-20 16:35:55'),
(12,'xiaoliguang','123456','小李广',1,'13309090012',2,10000,'01.png','2008-08-18',2,'2023-10-20 16:35:33','2023-10-20 16:35:57'),
(13,'yangzhi','123456','杨志',1,'13309090013',1,5300,'01.png','2012-11-01',1,'2023-10-20 16:35:33','2023-10-20 16:35:59'),
(14,'shijin','123456','史进',1,'13309090014',2,10600,'01.png','2002-08-01',2,'2023-10-20 16:35:33','2023-10-20 16:36:01'),
(15,'sunerniang','123456','孙二娘',2,'13309090015',2,10900,'01.png','2011-05-01',2,'2023-10-20 16:35:33','2023-10-20 16:36:03'),
(16,'luzhishen','123456','鲁智深',1,'13309090016',2,9600,'01.png','2010-01-01',2,'2023-10-20 16:35:33','2023-10-20 16:36:05'),
(17,'liying','12345678','李应',1,'13309090017',1,5800,'01.png','2015-03-21',1,'2023-10-20 16:35:33','2023-10-20 16:36:07'),
(18,'shiqian','123456','时迁',1,'13309090018',2,10200,'01.png','2015-01-01',2,'2023-10-20 16:35:33','2023-10-20 16:36:09'),
(19,'gudasao','123456','顾大嫂',2,'13309090019',2,10500,'01.png','2008-01-01',2,'2023-10-20 16:35:33','2023-10-20 16:36:11'),
(20,'ruanxiaoer','123456','阮小二',1,'13309090020',2,10800,'01.png','2018-01-01',2,'2023-10-20 16:35:33','2023-10-20 16:36:13'),
(21,'ruanxiaowu','123456','阮小五',1,'13309090021',5,5200,'01.png','2015-01-01',3,'2023-10-20 16:35:33','2023-10-20 16:36:15'),
(22,'ruanxiaoqi','123456','阮小七',1,'13309090022',5,5500,'01.png','2016-01-01',3,'2023-10-20 16:35:33','2023-10-20 16:36:17'),
(23,'ruanji','123456','阮籍',1,'13309090023',5,5800,'01.png','2012-01-01',3,'2023-10-20 16:35:33','2023-10-20 16:36:19'),
(24,'tongwei','123456','童威',1,'13309090024',5,5000,'01.png','2006-01-01',3,'2023-10-20 16:35:33','2023-10-20 16:36:21'),
(25,'tongmeng','123456','童猛',1,'13309090025',5,4800,'01.png','2002-01-01',3,'2023-10-20 16:35:33','2023-10-20 16:36:23'),
(26,'yanshun','123456','燕顺',1,'13309090026',5,5400,'01.png','2011-01-01',3,'2023-10-20 16:35:33','2023-11-08 22:12:46'),
(27,'lijun','123456','李俊',1,'13309090027',2,6600,'8.png','2004-01-01',2,'2023-10-20 16:35:33','2023-11-16 17:56:59'),
(28,'lizhong','123456','李忠',1,'13309090028',5,5000,'6.png','2007-01-01',3,'2023-10-20 16:35:33','2023-11-17 16:34:22'),
(30,'liyun','123456','李云',1,'13309090030',NULL,NULL,'01.png','2020-03-01',NULL,'2023-10-20 16:35:33','2023-10-20 16:36:31'),
(36,'linghuchong','123456','令狐冲',1,'18809091212',2,6800,'1.png','2023-10-19',2,'2023-10-20 20:44:54','2023-11-09 09:41:04');







给emp表添加外键约束


# 添加外键约束 
    alter table emp add constraint fk_emp_dept_id foreign key(dept_id) references dept(id);

        一对一


            案例:用户和身份证的信息


  关系:一对一关系,多用于单表拆分,将一张表的基础字段放在一张表中,其他字段放在另一张表中,以提升操作效率。
  实现:在任意一方加入外键,关联另一方的主键,并且设置外键为唯一的(unique)

 MySQL数据库从小白到入门(二),数据库,mysql,oracle


-- ===========================================一对一=====================================
create table tb_user(
    id int unsigned  primary key auto_increment comment 'ID',
    name varchar(10) not null comment '姓名',
    gender tinyint unsigned not null comment '性别, 1 男  2 女',
    phone char(11) comment '手机号',
    degree varchar(10) comment '学历'
) comment '用户信息表';

insert into tb_user values (1,'白眉鹰王',1,'18812340001','初中'),
                        (2,'青翼蝠王',1,'18812340002','大专'),
                        (3,'金毛狮王',1,'18812340003','初中'),
                        (4,'紫衫龙王',2,'18812340004','硕士');


create table tb_user_card(
    id int unsigned  primary key auto_increment comment 'ID',
    nationality varchar(10) not null comment '民族',
    birthday date not null comment '生日',
    idcard char(18) not null comment '身份证号',
    issued varchar(20) not null comment '签发机关',
    expire_begin date not null comment '有效期限-开始',
    expire_end date comment '有效期限-结束',
    user_id int unsigned not null unique comment '用户ID',
    constraint fk_user_id foreign key (user_id) references tb_user(id)
) comment '用户信息表';

insert into tb_user_card values (1,'汉','1960-11-06','100000100000100001','朝阳区公安局','2000-06-10',null,1),
        (2,'汉','1971-11-06','100000100000100002','静安区公安局','2005-06-10','2025-06-10',2),
        (3,'汉','1963-11-06','100000100000100003','昌平区公安局','2006-06-10',null,3),
        (4,'回','1980-11-06','100000100000100004','海淀区公安局','2008-06-10','2028-06-10',4);

        多对多


            案例:学生与课程的关系:

MySQL数据库从小白到入门(二),数据库,mysql,oracle


            关系:一个学生可以选修多门课程,一门课程可以供多个学生选择。
            实现:建立第三张表,中间表至少包含两个外键,分别关联两方的主键。

--  ======================================多对多=============================
create table tb_student(
    id int auto_increment primary key comment '主键ID',
    name varchar(10) comment '姓名',
    no varchar(10) comment '学号'
) comment '学生表';
insert into tb_student(name, no) values ('黛绮丝', '2000100101'),('谢逊', '2000100102'),('殷天正', '2000100103'),('韦一笑', '2000100104');


create table tb_course(
   id int auto_increment primary key comment '主键ID',
   name varchar(10) comment '课程名称'
) comment '课程表';
insert into tb_course (name) values ('Java'), ('PHP'), ('MySQL') , ('Hadoop');


create table tb_student_course(
   id int auto_increment comment '主键' primary key,
   student_id int not null comment '学生ID',
   course_id  int not null comment '课程ID',
   constraint fk_courseid foreign key (course_id) references tb_course (id),
   constraint fk_studentid foreign key (student_id) references tb_student (id)
)comment '学生课程中间表';

insert into tb_student_course(student_id, course_id) values (1,1),(1,2),(1,3),(2,2),(2,3),(3,4);

        表关系设计案例:


            根据下图需求:设计一个员工的工作经历表:

MySQL数据库从小白到入门(二),数据库,mysql,oracle
                

分析的知,一个员工可以有多段的工作经历,所以员工和工作经历对应的表关系是:一对多。

 create table emp_expr(
    id int unique primary key auto_increment comment 'ID',
    begin_date date comment '开始时间',
    end_date date comment '结束时间',
    name varchar(50) comment '公司名称',
    job varchar(50) comment '职位',
    emp_id int unique comment '员工ID'   #这个字段绑定的是员工表的主键ID
)comment '工作经历表';

添加外键SQL语句:


#给员工的工作经历表添加一个外键,绑定的是 员工表的主键id
alter table emp_expr add constraint fk_emp_expr_emp_id foreign key(emp_id) references dept(id);

多表查询:

准备多表查询的数据

-- 部门管理
create table dept(
    id int unsigned primary key auto_increment comment 'ID, 主键',
    name varchar(10) not null unique comment '部门名称',
    create_time datetime comment '创建时间',
    update_time datetime comment '修改时间'
) comment '部门表' ;

insert into dept (id, name, create_time, update_time) values
        (1,'学工部',now(),now()),
        (2,'教研部',now(),now()),
        (3,'咨询部',now(),now()),
        (4,'就业部',now(),now()),
        (5,'人事部',now(),now());


-- 员工管理
create table emp(
    id int unsigned primary key auto_increment comment 'ID,主键',
    username varchar(20) not null unique comment '用户名',
    password varchar(32) not null comment '密码',
    name varchar(10) not null comment '姓名',
    gender tinyint unsigned not null comment '性别, 1:男, 2:女',
    phone char(11) not null unique comment '手机号',
    job tinyint unsigned comment '职位, 1:班主任,2:讲师,3:学工主管,4:教研主管,5:咨询师',
    salary int unsigned comment '薪资',
    image varchar(300) comment '头像',
    entry_date date comment '入职日期',
    dept_id int unsigned COMMENT '关联的部门ID',
    create_time datetime comment '创建时间',
    update_time datetime comment '修改时间'
) comment '员工表';


-- 准备测试数据
INSERT INTO `emp` VALUES (1,'shinaian','123456','施耐庵',1,'13309090001',4,15000,'01.png','2000-01-01',2,'2023-10-27 16:35:33','2023-10-27 16:35:35'),
			(2,'songjiang','123456','宋江',1,'13309090002',2,8600,'01.png','2015-01-01',2,'2023-10-27 16:35:33','2023-10-27 16:35:37'),
			(3,'lujunyi','123456','卢俊义',1,'13309090003',2,8900,'01.png','2008-05-01',2,'2023-10-27 16:35:33','2023-10-27 16:35:39'),
			(4,'wuyong','123456','吴用',1,'13309090004',2,9200,'01.png','2007-01-01',2,'2023-10-27 16:35:33','2023-10-27 16:35:41'),
			(5,'gongsunsheng','123456','公孙胜',1,'13309090005',2,9500,'01.png','2012-12-05',2,'2023-10-27 16:35:33','2023-10-27 16:35:43'),
			(6,'huosanniang','123456','扈三娘',2,'13309090006',3,6500,'01.png','2013-09-05',1,'2023-10-27 16:35:33','2023-10-27 16:35:45'),
			(7,'chaijin','123456','柴进',1,'13309090007',1,4700,'01.png','2005-08-01',1,'2023-10-27 16:35:33','2023-10-27 16:35:47'),
			(8,'likui','123456','李逵',1,'13309090008',1,4800,'01.png','2014-11-09',1,'2023-10-27 16:35:33','2023-10-27 16:35:49'),
			(9,'wusong','123456','武松',1,'13309090009',1,4900,'01.png','2011-03-11',1,'2023-10-27 16:35:33','2023-10-27 16:35:51'),
			(10,'lichong','123456','林冲',1,'13309090010',1,5000,'01.png','2013-09-05',1,'2023-10-27 16:35:33','2023-10-27 16:35:53'),
			(11,'huyanzhuo','123456','呼延灼',1,'13309090011',2,9700,'01.png','2007-02-01',2,'2023-10-27 16:35:33','2023-10-27 16:35:55'),
			(12,'xiaoliguang','123456','小李广',1,'13309090012',2,10000,'01.png','2008-08-18',2,'2023-10-27 16:35:33','2023-10-27 16:35:57'),
			(13,'yangzhi','123456','杨志',1,'13309090013',1,5300,'01.png','2012-11-01',1,'2023-10-27 16:35:33','2023-10-27 16:35:59'),
			(14,'shijin','123456','史进',1,'13309090014',2,10600,'01.png','2002-08-01',2,'2023-10-27 16:35:33','2023-10-27 16:36:01'),
			(15,'sunerniang','123456','孙二娘',2,'13309090015',2,10900,'01.png','2011-05-01',2,'2023-10-27 16:35:33','2023-10-27 16:36:03'),
			(16,'luzhishen','123456','鲁智深',1,'13309090016',2,9600,'01.png','2010-01-01',2,'2023-10-27 16:35:33','2023-10-27 16:36:05'),
			(17,'liying','12345678','李应',1,'13309090017',1,5800,'01.png','2015-03-21',1,'2023-10-27 16:35:33','2023-10-27 16:36:07'),
			(18,'shiqian','123456','时迁',1,'13309090018',2,10200,'01.png','2015-01-01',2,'2023-10-27 16:35:33','2023-10-27 16:36:09'),
			(19,'gudasao','123456','顾大嫂',2,'13309090019',2,10500,'01.png','2008-01-01',2,'2023-10-27 16:35:33','2023-10-27 16:36:11'),
			(20,'ruanxiaoer','123456','阮小二',1,'13309090020',2,10800,'01.png','2018-01-01',2,'2023-10-27 16:35:33','2023-10-27 16:36:13'),
			(21,'ruanxiaowu','123456','阮小五',1,'13309090021',5,5200,'01.png','2015-01-01',3,'2023-10-27 16:35:33','2023-10-27 16:36:15'),
			(22,'ruanxiaoqi','123456','阮小七',1,'13309090022',5,5500,'01.png','2016-01-01',3,'2023-10-27 16:35:33','2023-10-27 16:36:17'),
			(23,'ruanji','123456','阮籍',1,'13309090023',5,5800,'01.png','2012-01-01',3,'2023-10-27 16:35:33','2023-10-27 16:36:19'),
			(24,'tongwei','123456','童威',1,'13309090024',5,5000,'01.png','2006-01-01',3,'2023-10-27 16:35:33','2023-10-27 16:36:21'),
			(25,'tongmeng','123456','童猛',1,'13309090025',5,4800,'01.png','2002-01-01',3,'2023-10-27 16:35:33','2023-10-27 16:36:23'),
			(26,'yanshun','123456','燕顺',1,'13309090026',5,5400,'01.png','2011-01-01',3,'2023-10-27 16:35:33','2023-10-27 16:36:25'),
			(27,'lijun','123456','李俊',1,'13309090027',5,6600,'01.png','2004-01-01',3,'2023-10-27 16:35:33','2023-10-27 16:36:27'),
			(28,'lizhong','123456','李忠',1,'13309090028',5,5000,'01.png','2007-01-01',3,'2023-10-27 16:35:33','2023-10-27 16:36:29'),
			(29,'songqing','123456','宋清',1,'13309090029',NULL,5100,'01.png','2020-01-01',NULL,'2023-10-27 16:35:33','2023-10-27 16:36:31'),
			(30,'liyun','123456','李云',1,'13309090030',NULL,NULL,'01.png','2020-03-01',NULL,'2023-10-27 16:35:33','2023-10-27 16:36:31');




    多表查询:就是从多张表中查询数据


    笛卡尔积:笛卡尔乘积:笛卡尔乘积是指在数学中,两个集合(A集合与B集合)的所有组合情况。

 MySQL数据库从小白到入门(二),数据库,mysql,oracle

内连接:

        相当于查询 A,B交集部分的数据

        上面的案例中,给员工表添加了一个外键,要是有的员工表中的dept_id 为null,那么就不会显示这些员工的dept_id 为null 的数据。

语法:

内连接查询的是两张表交集部分的数据。

1 隐式内连接

        select 字段列表

        MySQL数据库从小白到入门(二),数据库,mysql,oracle


-- ============================= 内连接 ==========================
-- A. 查询所有员工的ID, 姓名 , 及所属的部门名称 (隐式、显式内连接实现)

# 隐式
select emp.id,emp.name,dept.name from emp,dept where emp.dept_id = dept.id;

#显式
select emp.id,emp.name,dept.name from emp join dept on emp.dept_id = dept.id;

-- B. 查询 性别为男, 且工资 高于8000 的员工的ID, 姓名, 及所属的部门名称 (隐式、显式内连接实现)

#隐式
select emp.id,emp.name,dept.name from emp,dept where emp.dept_id = dept.id and emp.gender = 1 and emp.salary > 8000;

#显式
select emp.id,emp.name,dept.name from emp join dept on emp.dept_id = dept.id where emp.gender = 1 and emp.salary > 8000;

# 给表起别名
    # 使用as关键字
select e.id,e.name,d.name from emp as e join dept as d on e.dept_id = d.id;

# as关键字可以省略
select e.id,e.name,d.name from emp e join dept d on e.dept_id = d.id;
外连接:

左外连接:查询左表所有数据(包括两张表交集部分数据)

如下图,红圈选中的数据。

语法:

-- 左外连接(常用)

select 字段列表 from 表1 left [outer] join 表2 on 连接条件.... 

MySQL数据库从小白到入门(二),数据库,mysql,oracle

右外连接:查询右表所有数据(包括两张表交集部分数据)

如下图红圈选中的数据。

语法:

-- 右外连接:

        select 字段列表 from 表1 right [outer] join 表2 on 连接条件 

 MySQL数据库从小白到入门(二),数据库,mysql,oracle

示例:


-- =============================== 外连接 ============================
-- A. 查询员工表 所有 员工的姓名, 和对应的部门名称 (左外连接)

select e.name,d.name from emp e left join dept d on e.dept_id = d.id;

-- B. 查询部门表 所有 部门的名称, 和对应的员工名称 (右外连接)

select e.name,d.name from emp e right join dept d on e.dept_id = d.id;

# 可以改成左外连接:
    select e.name,d.name from dept d left join emp e on e.dept_id = d.id;
-- C. 查询工资 高于8000 的 所有员工的姓名, 和对应的部门名称 (左外连接)

select e.name,d.name from emp e left join dept d on e.dept_id = d.id where e.salary > 8000;
温馨提示:

对于外连接,常用的是左外连接,因为右外连接的SQL也可以改造成为左外连接(两张表调换个顺序)。

子查询:
介绍:

SQL语句中嵌套select语句,称为嵌套查询,又称子查询。

形式:

select * from t1 where column1 = (select column1 from t2 …);

说明:

子查询外部的语句可以是insert / update / delete / select 的任何一个,最常见的是 select。

分类:

标量子查询:

子查询返回的结果是单个值

MySQL数据库从小白到入门(二),数据库,mysql,oracle
列子查询:

子查询返回的结果是一列,可以有多行。

MySQL数据库从小白到入门(二),数据库,mysql,oracle
 行子查询:

子查询返回的结果是一行,可以是多列。

MySQL数据库从小白到入门(二),数据库,mysql,oracle
 表子查询:

子查询返回的结果是多行多列。

 MySQL数据库从小白到入门(二),数据库,mysql,oracle

 示例:


-- ========================= 子查询 ================================
# 在写子查询SQL的时候,我们一般先拆分需求,最后合并成一条SQL语句即可
-- 标量子查询
-- A. 查询 最早入职 的员工信息

    #先查询 最早入职的时间
    select min(entry_date) from emp;
    # 根据上面的到的最早入职时间查询员工的信息
    select * from emp where entry_date = '2000-01-01';
    # 合并SQL语句
    select * from emp where entry_date = ( select min(entry_date) from emp);

-- B. 查询在 "阮小五" 入职之后入职的员工信息

    # 先查询 ‘阮小五的入职时间’
    select entry_date from emp where name = '阮小五';   -- 2015-01-01
    # 根据阮小五的入职时间,查询之后入职的员工
    select * from emp where entry_date > '2015-01-01';
    # 拼接SQL
    select * from emp where entry_date > (select entry_date from emp where name = '阮小五');

-- 列子查询
-- A. 查询 "教研部" 和 "咨询部" 的所有员工信息

    # 先查询到教研部和咨询部的部门id
    select id from dept where name in ('教研部','咨询部'); -- 3,2
    #根据上面查到的部门id 查询里面包含的员工信息
    select * from emp where dept_id in (3,2);
    #合并
    select * from emp where dept_id in (select id from dept where name in ('教研部','咨询部'));




-- 行子查询
-- A. 查询与 "李忠" 的薪资 及 职位都相同的员工信息 ;

    # 先查询 李忠的薪资和职位
    select salary,job from emp;
    # 在根据李忠的薪资和职位 查询与此相同的员工信息
    select * from emp where (salary,job) in (5000,5);
    # 合并
    select * from emp where (salary,job) in (select salary,job from emp);


-- 表子查询
-- A. 查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息

    # 先查询入职日期是‘2006-01-01’之后的员工信息
    select * from emp where entry_date > '2006-01-01';
    #根据上面的入职信息 查询部门信息
    select e.*,d.name from ( select * from emp where entry_date > '2006-01-01') e,dept d where e.dept_id = d.id;
连接查询和子查询的案例:
-- 1. 查询 "教研部" 的 "男性" 员工,且在 "2011-05-01" 之后入职的员工信息 。

select * from emp e,dept d where e.dept_id = d.id and d.name = '教研部' and e.gender = 1  and e.entry_date > '2011-05--1';

-- 2. 查询工资 低于公司平均工资的 且 性别为男 的员工信息 。

# 查询公司的平均工资
select avg(salary) from emp;

select * from emp p where p.salary < (select avg(salary) from emp);

-- 3. 查询工资 低于本部门平均工资的员工信息 。

select * from emp e where e.salary < (select avg(salary) from emp where dept_id = e.dept_id);

-- 4. 查询部门人数超过 10 人的部门名称 。

select d.name,count(*) from dept d,emp e where d.id = e.dept_id group by d.name having count(*) > 10;
再来一波猛的练习:

提供查询的数据表:文章来源地址https://www.toymoban.com/news/detail-759747.html

-- 部门管理
create table tb_dept(
    id int unsigned primary key auto_increment comment '主键ID',
    name varchar(10) not null unique comment '部门名称',
    create_time datetime not null comment '创建时间',
    update_time datetime not null comment '修改时间'
) comment '部门表';

insert into tb_dept (id, name, create_time, update_time) 
        values(1,'学工部',now(),now()),
        (2,'教研部',now(),now()),
        (3,'咨询部',now(),now()), 
        (4,'就业部',now(),now()),
        (5,'人事部',now(),now());



-- 员工管理
create table tb_emp (
    id int unsigned primary key auto_increment comment 'ID',
    username varchar(20) not null unique comment '用户名',
    password varchar(32) default '123456' comment '密码',
    name varchar(10) not null comment '姓名',
    gender tinyint unsigned not null comment '性别, 说明: 1 男, 2 女',
    image varchar(300) comment '图像',
    job tinyint unsigned comment '职位, 说明: 1 班主任,2 讲师, 3 学工主管, 4 教研主管, 5 咨询师',
    entrydate date comment '入职时间',
    dept_id int unsigned comment '部门ID',
    salary int unsigned comment '工资',
    create_time datetime not null comment '创建时间',
    update_time datetime not null comment '修改时间'
) comment '员工表';

INSERT INTO tb_emp
    (id, username, password, name, gender, image, job, entrydate,dept_id,salary, create_time, update_time) VALUES
    (1,'jinyong','123456','金庸',1,'1.jpg',4,'2000-01-01',2,20000,now(),now()),
    (2,'zhangwuji','123456','张无忌',1,'2.jpg',2,'2015-01-01',2,18000,now(),now()),
    (3,'yangxiao','123456','杨逍',1,'3.jpg',2,'2008-05-01',2,16800,now(),now()),
    (4,'weiyixiao','123456','韦一笑',1,'4.jpg',2,'2007-01-01',2,12000,now(),now()),
    (5,'changyuchun','123456','常遇春',1,'5.jpg',2,'2012-12-05',2,9000,now(),now()),
    (6,'xiaozhao','123456','小昭',2,'6.jpg',3,'2013-09-05',1,6000,now(),now()),
    (7,'jixiaofu','123456','纪晓芙',2,'7.jpg',1,'2005-08-01',1,6500,now(),now()),
    (8,'zhouzhiruo','123456','周芷若',2,'8.jpg',1,'2014-11-09',1,7200,now(),now()),
    (9,'dingminjun','123456','丁敏君',2,'9.jpg',1,'2011-03-11',1,5300,now(),now()),
    (10,'zhaomin','123456','赵敏',2,'10.jpg',1,'2013-09-05',1,12000,now(),now()),
    (11,'luzhangke','123456','鹿杖客',1,'11.jpg',5,'2007-02-01',3,8900,now(),now()),
    (12,'hebiweng','123456','鹤笔翁',1,'12.jpg',5,'2008-08-18',3,7800,now(),now()),
    (13,'fangdongbai','123456','方东白',1,'13.jpg',5,'2012-11-01',3,6800,now(),now()),
    (14,'zhangsanfeng','123456','张三丰',1,'14.jpg',2,'2002-08-01',2,15800,now(),now()),
    (15,'yulianzhou','123456','俞莲舟',1,'15.jpg',2,'2011-05-01',2,11500,now(),now()),
    (16,'songyuanqiao','123456','宋远桥',1,'16.jpg',2,'2007-01-01',2,8300,now(),now()),
    (17,'chenyouliang','123456','陈友谅',1,'17.jpg',NULL,'2015-03-21',NULL,4500,now(),now());



-- 薪资等级表
create table tb_salgrade(
    grade int comment '等级',
    losal int comment '该等级最低薪资',
    hisal int comment '该等级最高薪资'
) comment '薪资等级表';

insert into tb_salgrade values (1,0,3000);
insert into tb_salgrade values (2,3001,5000);
insert into tb_salgrade values (3,5001,8000);
insert into tb_salgrade values (4,8001,10000);
insert into tb_salgrade values (5,10001,15000);
insert into tb_salgrade values (6,15001,20000);
insert into tb_salgrade values (7,20001,25000);
insert into tb_salgrade values (8,25001,30000);
全部习题代码:


-- 1. 查询员工的姓名 , 及所属的部门名称 (隐式内连接实现)

select te.name 员工姓名,td.name 部门名称 from tb_emp te,tb_dept td where te.dept_id = td.id;

-- 2. 查询员工的姓名 , 及所属的部门名称 (显式内连接实现)

select te.name 员工姓名,td.name 部门名称 from tb_emp te join tb_dept td on te.dept_id = td.id;

-- 3. 查询员工的 姓名、性别、职位、部门名称 (隐式内连接)

select te.name 姓名,te.gender 性别,te.job 职位,td.name 部门名称 from tb_emp te,tb_dept td where te.dept_id = td.id;

-- 4. 查询 薪资 高于 10000 的员工的姓名、性别、职位、部门名称(显式内连接)

select te.name 姓名,te.gender 性别,te.job 职位,td.name 部门名称 from tb_emp te join tb_dept td on te.dept_id = td.id where te.salary > 10000;

-- 5. 查询员工表 所有 员工的姓名, 和对应的部门名称 (左外连接)

select te.name 员工姓名,td.name 部门名称 from tb_emp te left join tb_dept td on te.dept_id = td.id;

-- 6. 查询员工表 所有 员工的姓名, 和对应的部门名称 (右外连接)

select te.name 员工姓名,td.name 部门名称 from tb_dept td right join tb_emp te  on te.dept_id = td.id;

-- 7. 查询部门表 所有 部门的名称, 和对应的员工名称 (右外连接)

select td.name 部门名称,te.name 员工姓名 from tb_emp te right join tb_dept td on te.dept_id = td.id;

-- 8. 查询 "教研部" 的所有员工信息 (标量子查询)

select * from tb_emp where dept_id = (select id from tb_dept where tb_dept.name = '教研部');

-- 9. 查询在 "方东白" 入职之后的员工信息 (标量子查询)

select * from tb_emp where entrydate > (select entrydate from tb_emp where name = '方东白');

-- 10. 查询 "教研部" 和 "咨询部" 的所有员工信息 (列子查询)

select * from tb_emp where dept_id in (select id from tb_dept where tb_dept.name in ('教研部','咨询部'));

-- 11. 查询与 "韦一笑" 的入职日期 及 职位都相同的员工信息 (行子查询)

select * from tb_emp where (entrydate,job) in (select entrydate,job from tb_emp where name = '韦一笑');

-- 12. 查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息 (表子查询)

select * from (select * from tb_emp where entrydate > '2006-01-01') e,tb_dept td where e.dept_id = td.id;

-- 13. 查询 拥有员工的 部门ID、部门名称 (没有员工的部门无需展示)

select te.dept_id 部门id,td.name 部门名称 from tb_emp te,tb_dept td where te.dept_id = td.id;

-- 14. 查询所有 在 2010-01-01 之后入职的员工, 及其归属的部门名称; 如果员工没有分配部门, 也需要展示出来

select te.*,td.name 部门名称 from tb_emp te left join tb_dept td on te.dept_id = td.id where te.entrydate > '2010-01-01';

-- 15. 查询 "教研部" 员工的平均工资

select avg(salary) from tb_emp where dept_id = (select id from tb_dept where tb_dept.name = '教研部');

-- 16. 查询工资比 "俞莲舟" 高的员工信息。

select * from tb_emp te where te.salary > (select salary from tb_emp where tb_emp.name = '俞莲舟');

-- 17. 查询 工资 比该企业员工的平均薪资 还要高的员工信息

select * from tb_emp te where te.salary > (select avg(salary) from tb_emp);

-- 18. 查询所有的部门信息, 并统计部门的员工人数

select count(*) 员工数量,td.name 部门名称 from tb_dept td,tb_emp te where te.dept_id = td.id group by td.name;
-- 19. 查询所有员工的 姓名, 工资 , 及 工资等级 (有难度的哦)
select te.name 姓名,te.salary 工资,ts.grade 工资等级 from tb_emp te left join tb_salgrade ts on te.salary between ts.losal and ts.hisal;

select te.name 姓名,te.salary 工资,
       (case
           when te.salary <= 3000 then '1'
           when te.salary <= 5000 then '2'
           when te.salary <= 8000 then '3'
           when te.salary <= 10000 then '4'
           when te.salary <= 15000 then '5'
           when te.salary <= 20000 then '6'
           when te.salary <= 25000 then '7'
           when te.salary <= 30000 then '8'
           else '未知等级'
           end) 薪资等级
from tb_emp te;

-- 20. 查询 "教研部" 所有员工的信息 及 工资等级 (有难度的哦)
select te.*,ts.grade from tb_emp te
            left join tb_dept td on te.dept_id = td.id
            left join tb_salgrade ts on te.salary between ts.losal and ts.hisal
            where td.name = '教研部';

select e.*,
     (case
           when e.salary <= 3000 then '1'
           when e.salary <= 5000 then '2'
           when e.salary <= 8000 then '3'
           when e.salary <= 10000 then '4'
           when e.salary <= 15000 then '5'
           when e.salary <= 20000 then '6'
           when e.salary <= 25000 then '7'
           when e.salary <= 30000 then '8'
           else '未知等级'
      end) 薪资等级
from (select * from tb_emp te where te.dept_id = (select id from tb_dept td where td.name = '教研部')) e ;

-- 21. 查询 工资 低于 本部门平均工资的员工信息 (有难度的哦)

select * from tb_emp te where te.salary < (select avg(salary) from tb_emp where tb_emp.dept_id = te.dept_id);

-- 22. 列出所有部门的详细信息(包括部门ID, 部门名称)和人数

select td.id 部门id,td.name 部门名称,count(*) 部门人数 from tb_dept td left join tb_emp te on td.id = te.dept_id group by td.name;

-- 23、取得每个薪资等级有多少员工  (有难度的哦)
select te.name 姓名,te.salary 工资,ts.grade 工资等级 ,count(ts.grade) 等级数量 from tb_emp te
    left join tb_salgrade ts on te.salary between ts.losal and ts.hisal group by ts.grade;

select
    count(if(salary > 0 and salary < 3000,1,null)) as '1等级薪资人数',
    count(if(salary > 3001 and salary < 5000,1,null)) as '2等级薪资人数',
    count(if(salary > 5001 and salary < 8000,1,null)) as '3等级薪资人数',
    count(if(salary > 8001 and salary < 10000,1,null)) as '4等级薪资人数',
    count(if(salary > 10001 and salary < 15000,1,null)) as '5等级薪资人数',
    count(if(salary > 15001 and salary < 20000,1,null)) as '6等级薪资人数',
    count(if(salary > 20001 and salary < 25000,1,null)) as '7等级薪资人数',
    count(if(salary > 25001 and salary < 30000,1,null)) as '8等级薪资人数'
from tb_emp te;


-- 24. 取得每个部门最高薪水的人员名称


select * from tb_emp where (dept_id,salary) in (select dept_id,max(salary) from tb_emp group by dept_id);

到了这里,关于MySQL数据库从小白到入门(二)的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • 数据库问题记录(粗略版)oracle、mysql等主流数据库通用

    1. ORA-00918:未明确定义列 该问题情况大致为:select 所取列名错误、重复等问题。 2. “select * from temp where 1=0; ”的含义 布尔值为FALSE,只返回表结构,不返回数据。 举一反三: select * from temp where 10 , 布尔值为TRUE,返回所有数据记录; select * from temp where 1=0, 暂不清楚是何

    2024年02月07日
    浏览(48)
  • 从 Oracle 到 MySQL 数据库的迁移之旅

    目录 引言 一、前期准备工作 1.搭建新的MySQL数据库 2 .建立相应的数据表 2.1 数据库兼容性分析 2.1.1 字段类型兼容性分析 2.1.2 函数兼容性分析 2.1.3 是否使用存储过程?存储过程的个数?复杂度? 2.1.4 是否使用触发器?个数?使用的场景? 2.2 建表过程中其他需要注意的事项

    2024年04月11日
    浏览(49)
  • Mysql和Oracle数据库死锁查看以及解决

    一、Mysql数据库死锁排查 SQL : SQL: SQL: 示例: 备注:通过查看事务的trx_started(开始时间)来判断该事务的阻塞时间。 SQL  : 备注:线程ID指的是 1.1.3步骤中查询出来的 trx_mysql_thread_id。 SQL : 示例 : 字段解析 : SQL : 示例: SQL : 备注:多个session用逗号隔开。 View Code   造

    2024年01月20日
    浏览(44)
  • 小白带你学习linux的mysql数据库备份(三十)

    目录 一、概述 二、数据备份的重要性 三、造成数据丢失的原因 四、备份类型 1、物理与逻辑角度 1.1物理备份 1.2冷备份 1.3热备份 1.4逻辑备份 2、数据库备份策略角度 1.1完整备份 1.2增量备份 五、常见的备份方法 1、物理备份 2、使用专用备份工具 3、通过启用二进制日志增量

    2024年02月03日
    浏览(51)
  • 微信小程序使用PHP调用后台mysql数据库-小白版

    1.域名备案 首先,需要有一个域名,且这个域名是已经备过案的。(如果小程序不发布正式版只用于线上测试则不影响) 2.后台服务器 其次,需要一个服务器,我这里使用的是宝塔面板,对小白很友好,很方便操作。 也可以买个虚拟主机,一个月几块钱左右,很适合小白弄

    2024年04月14日
    浏览(49)
  • 数据库加密AES 适用 Mysql Oracle 以及java

    提示:文章写完后,目录可以自动生成,如何生成可参考右边的帮助文档 备忘录:加密key为:vU4C!~0_HQtlU_p@ 可采用任意长度为16位的字符串 数据库数据需要对敏感数据进行加密,比如手机号,身份证号以及银行卡号等进行加密。需要一套通用的加密方法可以在java项目以及or

    2024年02月15日
    浏览(48)
  • 查询Oracle和MySQL数据库中当前所有连接信息

    查询Oracle当前所有连接信息: 查询MySQL当前所有连接信息: 在这两个查询中,我为每个字段添加了中文别名,以提高查询结果的可读性

    2024年02月12日
    浏览(69)
  • 从MySQL迁移到Oracle数据库的详细步骤和方法

    在一些情况下,可能需要将现有的MySQL数据库迁移到Oracle数据库。本文将详细介绍如何进行这一迁移过程,涵盖了备份、转换和导入等关键步骤,以帮助你顺利完成数据库迁移。 在开始迁移之前,务必备份你的MySQL数据库,以便在出现问题时可以还原数据。 确保你已经安装了

    2024年02月08日
    浏览(51)
  • docker安装mysql、clickhouse、oracle等各种数据库汇总

    1:docker 安装mongo数据库并使用 官网:https://www.mongodb.com/docs/manual/ mongo shell教程1:http://c.biancheng.net/mongodb2/connection.html 安装1 :https://www.zhihu.com/question/54602953/answer/3047452434?utm_id=0 安装2:https://www.duidaima.com/Group/Topic/ArchitecturedDesign/9182 使用驱动进行java开发:https://mongodb.github.

    2024年02月10日
    浏览(51)
  • DataGrip连接数据库设置(MySQL、Oracle、SQL Server)

    1.1 配置信息 1.2 测试查询employees库中departments表信息 employees为测试库,具体来源,参考这篇文章 下载并导入MySQL示例数据库employees 。 1.3 测试查询employees库中employees表信息 将SID改为orcl: 3.1 打开DG后新建Data Source-Microsoft SQL Server 下载驱动文件后按图中配置: 3.2 开启Named Pipes和

    2024年02月12日
    浏览(45)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包