数据库——MySQL综合练习

最新推荐文章于 2024-11-15 18:38:47 发布
最新推荐文章于 2024-11-15 18:38:47 发布
阅读量86 收藏 1
点赞数 1
分类专栏： MySQL 文章标签： 数据库 mysql sql 经验分享 笔记 学习 开发语言
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_55251408/article/details/132409556
版权 
use db03;

# 创建dept表
create table dept
(
    dept_id       int auto_increment comment '部门id'
        primary key,
    dept_name     varchar(64)  null comment '部门名称',
    dept_location varchar(255) null comment '部门位置'
)
    comment '部门表';

desc dept;

# 创建employee表
create table employee
(
    emp_id     int          not null comment '员工编号'
        primary key,
    emp_name   varchar(64)  null comment '员工姓名',
    emp_gender tinyint(1)   null comment '员工性别（0男 1女）',
    emp_job    varchar(128) null comment '员工工作',
    emp_salary smallint     null comment '员工薪水',
    hire_date  date         null comment '出生日期',
    dept_id    int          null comment '所属部门编号',
    constraint employee_dept_dept_id_fk
        foreign key (dept_id) references dept (dept_id)
)
    comment '员工表';

desc employee;

# 向dept表中插入数据
insert into dept (dept_id, dept_name, dept_location)
values (10, 'ACCOUNTING', 'ShangHai'),
       (20, 'RESEARCH', 'BeiJing'),
       (30, 'SALES', 'ShenZhen'),
       (40, 'OPERATIONS', 'FuJian');

select *
from dept;

# 向employee表中插入数据
insert into employee (emp_id, emp_name, emp_gender, emp_job, emp_salary, hire_date, dept_id)
values (1001, 'SMITH', '0', 'CLERK', 800, '2005-11-12', 20),
       (1002, 'ALLEN', '1', 'SALESMAN', 1600, '2003-05-12', 30),
       (1003, 'WARD', '1', 'SALESMAN', 1250, '2003-05-12', 30),
       (1004, 'JONES', '0', 'MANAGER', 2975, '1998-05-18', 20),
       (1005, 'MARTIN', '0', 'SALESMAN', 1250, '2001-06-12', 30),
       (1006, 'BLAKE', '1', 'MANAGER', 2850, '1997-02-15', 30),
       (1007, 'CLARK', '0', 'MANAGER', 2450, '2002-09-12', 10),
       (1008, 'SCOTT', '0', 'ANALYST', 3000, '2003-05-12', 20),
       (1009, 'KING', '1', 'PRESIDENT', 5000, '1995-01-01', 10),
       (1010, 'TURNER', '1', 'SALESMAN', 1500, '1997-10-12', 30),
       (1011, 'ADAMS', '0', 'CLERK', 1100, '1999-10-05', 20),
       (1012, 'JAMES', '0', 'CLERK', 950, '2008-06-15', 30);

select *
from employee;

# 在employee表中，查询所有记录的emp_id、emp_name和emp_salary字段值
select emp_id, emp_name, emp_salary
from employee;

# 在employee表中，查询dept_id等于10和20的所有记录
select *
from employee e
where e.dept_id in (10, 20);

# 在employee表中，查询工资范围在800~2500之间的员工信息
select *
from employee e
where e.emp_salary between 800 and 2500;

# 在employee表中，查询部门编号为20的部门中的员工信息
select *
from employee e
where e.dept_id = 20;

# 在employee表中，查询每个部门最高工资的员工信息
select max(emp_salary) as max_salary, dept_id
from employee e
group by dept_id;

# 查询员工BLAKE所在部门和部门所在地
select dept_id, dept_location
from dept d
where d.dept_id in (select dept_id
                    from employee e
                    where e.emp_name = 'BLAKE');

# 使用连接查询，查询所有员工的部门和部门信息
select e.emp_id, e.emp_name, d.*
from dept d,
     employee e
where d.dept_id = e.dept_id;

# 在employee表中，计算每个部门各有多少名员工
select dept_id, count(*) as emp_number
from employee e
group by e.dept_id;

# 在employee表中，计算不同类型职工的总工资数
select emp_job, sum(emp_salary) as sum_sal
from employee e
group by e.emp_job;

# 在employee表中，计算不同部门的平均工资
select dept_id, avg(emp_salary) as avg_sal
from employee e
group by e.dept_id;

# 在employee表中，查询工资低于1500的员工信息
select *
from employee e
where e.emp_salary < 1500;

# 在employee表中，将查询记录先按部门编号由高到低排列，再按员工工资由高到低排列
select *
from employee e
order by e.dept_id desc,
         e.emp_salary desc;

# 在employee表中，查询员工姓名以字母’A’或’S’开头的员工的信息
select *
from employee e
where e.emp_name regexp '^[AS]';

# 在employee表中，查询到目前为止，工龄大于等于10年的员工信息
select *, datediff(date(now()), e.hire_date) / 365 as year
from employee e
where datediff(date(now()), e.hire_date) / 365 >= 10;

# 创建索引
create index emp_id_index on employee (emp_id);

show index from employee;

# 修改索引
drop index emp_id_index on employee;

create index employee_id_index on employee (emp_id);

# 创建视图
create view emp_view
as
select emp_id, emp_name, dept_name
from employee e
         join dept d on d.dept_id = e.dept_id;

select *
from emp_view;

# 修改视图
create or replace view emp_view
as
select emp_id, emp_name, dept_name, dept_location
from employee e
         join dept d on d.dept_id = e.dept_id;

select *
from emp_view;

# 删除视图
drop view if exists emp_view;

show table status like 'emp_view';

# 创建存储函数 count_by_id(), 参数传入 dept_id,
# 该函数查询 dept_id 部门的员工人数，并返回，数据类型为整型
delimiter //
create function count_by_id(dept_id int)
    returns int
begin
    return (select count(*)
            from employee e
            where e.dept_id = dept_id);
end //
delimiter ;

# 调用存储函数，查询20号部门员工人数
select count_by_id(20);

# 修改存储函数
    alter function count_by_id
    sql security invoker comment '查询某部门人数';

show function status like 'count_by_id';

# 删除存储函数
drop function if exists count_by_id;

select *
from information_schema.ROUTINES R
where R.ROUTINE_NAME = 'count_by_id'
  and R.ROUTINE_TYPE = 'function';

# 创建存储过程 show_emp_sal(), 查看 employee 表的某个员工的薪资，
# 并用 IN 参数 emp_name 输入员工姓名，用 OUT 参数 emp_sal 输出员工薪资
delimiter //
create procedure show_emp_sal(in emp_name varchar(64), out emp_sal smallint)
begin
    select emp_salary
    into emp_sal
    from employee e
    where e.emp_name = emp_name;
end //
delimiter ;

# 调用存储过程，查询KING的工资
set @emp_name := 'KING';
call show_emp_sal(@emp_name, @emp_sal);
select concat('员工 ', @emp_name, ' 的薪水为：', @emp_sal) as 'emp_sal';

# 修改存储过程
alter procedure show_emp_sal
    sql security invoker comment '查询员工薪资';

show procedure status like 'show_emp_sal';

# 删除存储过程
drop procedure if exists show_emp_sal;

select *
from information_schema.ROUTINES R
where R.ROUTINE_NAME = 'show_emp_sal'
  and R.ROUTINE_TYPE = 'procedure';

# 创建触发器 sal_check_tri, 基于员工表 employee 的INSERT事件，
# 在INSERT之前检查将要添加的新员工薪资是否大于薪资上限5000，如果大于5000,
# 则报sqlstate_value为'HY000'的错误，从而使得添加失败。
delimiter //
create trigger sal_check_tri
    before insert
    on employee
    for each row
begin
    if new.emp_salary > 5000
    then
        signal sqlstate 'HY000' set message_text = '错误，薪资高于5000';
    end if;
end //
delimiter ;

# 测试触发器
insert into employee (emp_id, emp_name, emp_gender, emp_job, emp_salary, hire_date, dept_id)
values (2, '小象', 0, '架构师', 8848, '2990-03-12', 20);

# 查看触发器
select *
from information_schema.TRIGGERS
where TRIGGER_NAME = 'sal_check_tri';

# 删除触发器
drop trigger if exists sal_check_tri;

select *
from information_schema.TRIGGERS
where TRIGGER_NAME = 'sal_check_tri';

# 分支结构之if 声明存储过程 update_salary_by_if, 定义IN参数 emp_no, 输入员工编号。
# 判断该员工薪资如果低于9000元，就更新薪资为9000元；薪资如果大于等于9000元且
# 低于10000的，涨薪1000元；其他的扣除工资100元
delimiter //
create procedure update_salary_by_if(in emp_no int)
begin
    # 声明
    /* 记录员工的工资 */
    declare emp_sal smallint;
    # 赋值
    select emp_salary
    into emp_sal
    from employee e
    where e.emp_id = emp_no;
    # 判断
    if emp_sal < 9000
    then
        update employee
        set emp_salary = 9000
        where emp_id = emp_no;
    elseif emp_sal < 10000
    then
        update employee
        set emp_salary = emp_salary + 9000
        where emp_id = emp_no;
    else
        update employee
        set emp_salary = emp_salary - 100
        where emp_id = emp_no;
    end if;
end;
delimiter ;

call update_salary_by_if(2);

select *
from employee;

# 分支结构之case 声明存储过程 update_salary_by_if, 定义IN参数 emp_no, 输入员工编号。
# 判断该员工薪资如果低于9000元，就更新薪资为9000元；薪资如果大于等于9000元且
# 低于10000的，涨薪1000元；其他的扣除工资100元
delimiter //
create procedure update_salary_by_case(in emp_no int)
begin
    # 声明
    /* 记录员工的工资 */
    declare emp_sal smallint;
    # 赋值
    select emp_salary
    into emp_sal
    from employee e
    where e.emp_id = emp_no;
    # 判断
    case
        when emp_sal < 9000
            then update employee
                 set emp_salary = 9000
                 where emp_id = emp_no;
        when emp_sal < 10000
            then update employee
                 set emp_salary = emp_salary + 9000
                 where emp_id = emp_no;
        else update employee
             set emp_salary = emp_salary - 100
             where emp_id = emp_no;
        end case;
end //
delimiter ;

call update_salary_by_case(1001);

select *
from employee;

# 循环结构之while 市场环境不好时，公司为了渡过难关，决定暂时降低大家的薪资。
# 声明存储过程 “update_sal_while()”, 声明OUT参数num，输出循环次数。
# 存储过程中实现循环给大家降薪，薪资降为原来的90%。直到全公司的平均薪资
# 达到2000结束。并统计循环次数。
delimiter //
create procedure update_sal_while(out num int)
begin
    # 声明
    /* 记录员工的平均工资 */
    declare avg_sal decimal(7, 2);
    /* 记录循环的次数 */
    declare while_count int default 0;
    # 赋值
    /* ①初始化条件 */
    select avg(emp_salary)
    into avg_sal
    from employee;
    /* ② 循环条件 */
    while avg_sal > 2000
        do
            /* ③ 循环体 */
            update employee
            set emp_salary = emp_salary * 0.9;
            /* 记录循环次数 */
            set while_count = while_count + 1;
            /* ④ 迭代条件 */
            select avg(emp_salary)
            into avg_sal
            from employee;
        end while;
    # 使用
    set num = while_count;
end //
delimiter ;

call update_sal_while(@num);
select @num;

# 循环结构之loop 当市场环境变好时，公司为了奖励大家，决定给大家涨工资。
# 声明存储过程 “update_sal_loop()”, 声明OUT参数num, 输出循环次数。
# 存储过程中实现循环给大家涨薪，薪资涨为原来的1.1倍。直到全公司的平
# 均薪资达到10000结束。并统计循环次数。
delimiter //
create procedure update_sal_loop(out num int)
begin
    # 声明
    /* 记录员工的平均工资 */
    declare avg_sal smallint;
    /* 记录循环的次数 */
    declare loop_count int default 0;
    # 赋值
    /* ①初始化条件 */
    select avg(emp_salary)
    into avg_sal
    from employee;
    loop_label:
    loop
        /* ② 循环条件 */
        if avg_sal >= 10000
        then
            leave loop_label;
        end if;
        /* ③ 循环体 */
        update employee
        set emp_salary = emp_salary * 1.1;
        /* ④ 迭代条件 */
        select avg(emp_salary)
        into avg_sal
        from employee;
        /* 记录循环次数 */
        set loop_count = loop_count + 1;
    end loop loop_label;
    # 使用
    set num = loop_count;
end //
delimiter ;

call update_sal_loop(@num);
select @num;

# 循环结构之repeat 市场环境不好时，公司为了渡过难关，决定暂时降低大家的薪资。
# 声明存储过程 “update_sal_repeat()”, 声明OUT参数num，输出循环次数。
# 存储过程中实现循环给大家降薪，薪资降为原来的80%。直到全公司的平均薪资
# 达到5000结束。并统计循环次数。
delimiter //
create procedure update_sal_repeat(out num int)
begin
    # 声明
    /* 记录员工的平均工资 */
    declare avg_sal smallint;
    /* 记录循环的次数 */
    declare repeat_count int default 0;
    # 赋值
    /* ①初始化条件 */
    select avg(emp_salary)
    into avg_sal
    from employee;
    repeat
        /* ③ 循环体 */
        update employee
        set emp_salary = emp_salary * 0.8;
        /* 记录循环次数 */
        set repeat_count = repeat_count + 1;
        /* ④ 迭代条件 */
        select avg(emp_salary)
        into avg_sal
        from employee;
    until
        /* ② 循环条件 */
        avg_sal <= 5000
        end repeat;
# 使用
    set num = repeat_count;
end //
delimiter ;

call update_sal_repeat(@num);
select @num;

# 跳转语句之iterate 定义局部变量num，初始值为0。循环结构中执行num + 1操作
# 如果num < 10，则继续执行循环；
# 如果num > 15，则退出循环结构；
delimiter //
create procedure iterate_begin()
begin
    # 声明
    declare num int default 0;
    # 赋值
    loop_label:
    loop
        set num = num + 1;
        if num < 10
        then
            iterate loop_label;
        elseif num > 15
        then
            leave loop_label;
        end if;
        select '游戏结束';
    end loop;
    # 使用
    select num;
end //
delimiter ;

call iterate_begin();

# 新闻发布管理系统
MySQL数据库实操教程(01)——MySQL数据库安装
谷哥的小弟
07-21 5562
DataBase Management System是操作和管理数据库的大型软件，所以从本质上来讲它是一种管理软件；用户可通过数据库管理系统访问数据库中的数据。DataBase Management System的主要功能是建立数据库、使用数据库、维护数据库、并对数据库进行统一管理以保证数据库的安全性和完整性。
数据库开发——MySQL——基本操作
Alex
02-08 722
一、SQL语句 有了mysql这个数据库软件，就可以将程序员从对数据的管理中解脱出来，专注于对程序逻辑的编写。 mysql服务端软件帮我们管理好文件夹以及文件，前提是作为使用者需要下载mysql的客户端，或者其他模块来连接到mysql，然后使用mysql软件规定的语法格式去提交自己命令，实现对文件夹或文件的管理。 该语法即sql（Structured Query Language 即结构化查询语言...
数据库综合题
hello world
06-18 2224
如何创建索引？（必考） 例1：在s表的sname（姓名），sex（性别）列上建立一个聚集索引，S表中的记录将按照SNAME值的降序存放    create  clustered  index   stusname  on  s(sname,sex  desc);     例2：在emp表的last_name列上创建一个唯一性索引 create  unique  index  emp_l
MYSQL——综合练习
TYW0823的博客
01-26 900
创建数据库创建学生表创建课程表创建教师表创建成绩表。
数据库综合练习一
雪山飞狐
05-22 316
主要使用postgreSQL，数据库下载 Question 1: Write an SQL query that gives the number of films whose rental rate is greater than the average rental rate of all movies. Your query must generate a single column cou...
数据库系统原理与应用--综合练习（1）
bubbleTg的博客
06-22 2163
--1.在Oracle数据库中新建用户，用户名为你的姓名，密码为和用户名相同create user tiangui01 identified by tiangui01;--为新建用户授权，包括：建表、建视图、建触发器、建存储过程等权限grant resource,create any view,connect to tiangui01;3.           利用新建用户连接数据库，并在新用户（...
数据库练习题
09-09
数据库练习题。建表、查询、视图、触发器。
MySQL数据库——基础语句练习(一)
冷漠的小猿
09-02 2339
三道SQL基础练习题 一、设有成绩表如下所示 1.查询两门及两门以上不及格的学生姓名 2.查询学生平均分 3.查询姓名是张三的学生 成绩和 4.将学生信息按照 分数倒序 5.获取学生信息中 分数最低的学生姓名和分数最高的学生姓名 6.查询两门及两门以上不及格同学的平均分。 编号 姓名 科目 分数 1 张三 数学 90 2 张三 语文 50 3 张三 地理 4...
MySQL数据库——子查询
L_earning_的博客
03-18 1176
子查询以及相关练习
练习——MySQL数据库操作
Jchan_Wu的博客
08-13 248
基础题 关系型数据库核心元素有哪些？ 数据行 数据列 数据表（数据行的集合） 数据库（数据表的集合） 使用命令行连接数据库服务器的命令是什么？ mysql -u用户名 -p密码 或 mysql -u用户名 -p 回车后输入用户名 列出数据库的命令 查看所有数据库 show databases; 使用数据库 use 数据库名; 查看当前使用的数据库 select database(); 创建数据库 create database 数据库名; 删除数据库 dorp database 数据
MySql数据库——复杂查询
aqipa的博客
06-02 1405
MySql的复杂查询一、连接查询1.1、CROSS JOIN（交叉连接）1.2、等值连接1.2.1、NATURAL JOIN（自然连接）1.2.2、USING 连接1.2.3、ON 连接1.3、非等值连接1.4、外链接1.4.1、LEFT OUTER JOIN （左外连接）1.4.2、RIGHT OUTER JOIN（右外连接）1.4.2、FULL OUTER JOIN（全外连接）1.5、自连接二、联合查询三、子查询3.1、EXISTS 子查询3.2、集合比较子查询3.3、子查询作为计算字段 一、连接查询
数据库作业之十三综合练习（一）
我的博客
04-05 813
13.题目要求：1、创建数据库MyLib，保存于E盘根目录下以自己学号+姓第一个字母（阿拉伯数字+大写字母）方式创建的文件夹中，初始大小5MB，最大200MB，以20%方式增长，日志文件存于同一文件夹，初始大小2MB，最大50MB，以5MB方式增长；2、创建表Reader，借书证号为主键，11位数字，姓名，长度10个字符，专业名，长度12个字符，性别为BIT型，默认值为0，出生时间为日期型，借书数...
数据库经典练习题(带答案)
11-26
mysql经典例题,带答案,欢迎下载.分值不高....
MySql数据库——指令集
aqipa的博客
05-29 737
mysql指令集一、数据库的登录语句：二、数据库的基本操作1、查询数据库2、创建数据库 一、数据库的登录语句： mysql -h主机ip -u用户名 -p密码 ① 、本机登录不需要加-h ②、不建议把密码加到-p后面 你好！ 这是你第一次使用 Markdown编辑器 所展示的欢迎页。如果你想学习如何使用Markdown编辑器, 可以仔细阅读这篇文章，了解一下Markdown的基本语法知识。 二、数据库的基本操作 1、查询数据库 show databases; # 查看所
Mybatis
最新发布
庸不易的博客
11-15 816
1、定义不同：#{} 预处理：而 ${} 是直接替换2、使用不同：#{} 适用于所有类型的参数匹配；但 ${} 只适用于数值类型。3、安全性不同：#{} 性能高，并且没有安全问题；但 $ {} 存在 SQL 注入的安全问题。4、使用场景不同：当传递的是一个 SQL 关键字 的时候，只能 使用 ${}。当传递的是一个字段，总之，就是需要获取到参数类型 与 内容，只能使用 #{}。（PS：数字类型，#{} 和 ${} 都是可以使用的！5、 ${} 不能用于 模糊匹配查询；
【2024最新】springboot网上商城购物系统lw+ppt
man2017的博客
11-12 629
开发技术：SpringBoot、php、Python、小程序、SSM、Vue、MySQL、JSP、ElementUI等，文末源码。专栏推荐：SpringBoot项目源码、Vue项目源码、SSM项目源码、微信小程序源码精品专栏：Java精选实战项目源码、Python精选实战项目源码、大数据精选实战项目源码。
MySQL 8.0特性-自增变量的持久化
myneth的博客
11-13 567
在MySQL 8.0之前，自增主键AUTO_INCREMENT的值如果大于max(primary key)+1，在MySQL重启后，会重置AUTO_INCREMENT=max(primary key)+1，这种现象在某些情况下会导致业务主键冲突或者其他难以发现的问题。下面通过案例来对比不同的版本中自增变量是否持久化。
[Docker#9] 存储卷 | Volume、Bind、Tmpfs | -v/mount | MySQL 灾难恢复 | 问题
2301_80171004的博客
11-14 811
本文介绍了 Docker 存储卷的概念、分类及其应用。从什么是存储卷、生活案例到存储卷的必要性，再到管理卷、绑定卷和临时卷的具体创建与使用方法，最后通过 MySQL 灾难恢复的实战案例，帮助读者全面掌握存储卷的运用技巧。
约束（MYSQL）
2301_80363309的博客
11-13 331
not null（非空） unique（唯一） default（默认约束，规定值） 主键约束primary key（非空且唯一） auto_increment（自增类型） 复合主键 check（保证列中值符合指定条件） foreign key（外键约束）
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

小丶象

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值