MySQL SQL 编程练习

妍妍的宝贝

于 2024-07-25 18:13:56 发布

阅读量168

点赞数 3

文章标签： mysql sql linux

本文链接：https://blog.csdn.net/weixin_68398469/article/details/140691231

版权

创建提取emp_new表所有员工姓名和工资的存储过程s1

创建存储过程s2，实现输入员工姓名后返回员工的年龄

创建一个存储过程s3，有2个参数，传入部门号，返回该部门的平均工资

创建表并插入数据

# 创建goods商品表
mysql> CREATE TABLE goods
    -> (
    ->    gid CHAR(8) PRIMARY KEY, #商品编号
    ->    name VARCHAR(10),        #商品名
    ->    price DECIMAL(8,2),      #价格
    ->    num  INT                 #数量
    -> );
Query OK, 0 rows affected (0.01 sec)

# 创建orders订单表
mysql> CREATE TABLE orders
    -> (
    ->   oid  INT PRIMARY KEY AUTO_INCREMENT,  #订单号
    ->   gid  CHAR(10) NOT NULL,               #商品号
    ->   name VARCHAR(10),                     #商品名
    ->   price DECIMAL(8,2),                   #价格
    ->   onum  INT ,                           #订单数量
    ->   otime DATE                            #订单时间
    -> );
Query OK, 0 rows affected (0.05 sec)

# 给goods表插入数据 
mysql> insert into goods values
    -> ('A0001','橡皮',2.5,100),
    -> ('B0001','小楷本',2.8,210),
    -> ('C0001','铅笔',1.2,120),
    -> ('D0001','计算器',28,20);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

查看表结构

mysql> desc goods;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| gid   | char(8)      | NO   | PRI | NULL    |       |
| name  | varchar(10)  | YES  |     | NULL    |       |
| price | decimal(8,2) | YES  |     | NULL    |       |
| num   | int          | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> desc orders;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| oid   | int          | NO   | PRI | NULL    | auto_increment |
| gid   | char(10)     | NO   |     | NULL    |                |
| name  | varchar(10)  | YES  |     | NULL    |                |
| price | decimal(8,2) | YES  |     | NULL    |                |
| onum  | int          | YES  |     | NULL    |                |
| otime | date         | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)

创建触发器

创建INSERT 触发器

create trigger insert_after_orders_trigger 
    after insert on orders 
    for each row update goods
    begin
        set num=num-new.onum where gid=new.gid;
    end //


mysql> insert into orders(gid,name,price,onum,otime) value('A0001','橡皮','2.5',20,now());
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from goods;
+-------+-----------+-------+------+
| gid   | name      | price | num  |
+-------+-----------+-------+------+
| A0001 | 橡皮      |  2.50 |   80 |
| B0001 | 小楷本    |  2.80 |  210 |
| C0001 | 铅笔      |  1.20 |  120 |
| D0001 | 计算器    | 28.00 |   20 |
+-------+-----------+-------+------+
4 rows in set (0.00 sec)

mysql> select * from orders;
+-----+-------+--------+-------+------+------------+
| oid | gid   | name   | price | onum | otime      |
+-----+-------+--------+-------+------+------------+
|   1 | A0001 | 橡皮   |  2.50 |   20 | 2024-07-25 |
+-----+-------+--------+-------+------+------------+
1 row in set (0.00 sec)

创建DELETE 触发器

mysql> DELIMITER //
mysql> CREATE TRIGGER TRIGGER_DELETE_AFTER_ORDERS
    ->     AFTER DELETE ON orders
    ->     FOR EACH ROW
    ->     BEGIN       
    ->          UPDATE goods SET num = num + old.onum WHERE gid = old.gid;
    ->     END //
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;
mysql> SELECT * FROM goods;
+-------+-----------+-------+------+
| gid   | name      | price | num  |
+-------+-----------+-------+------+
| A0001 | 橡皮      |  2.50 |   80 |
| B0001 | 小楷本    |  2.80 |  210 |
| C0001 | 铅笔      |  1.20 |  120 |
| D0001 | 计算器    | 28.00 |   20 |
+-------+-----------+-------+------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM orders;
+-----+-------+--------+-------+------+------------+
| oid | gid   | name   | price | onum | otime      |
+-----+-------+--------+-------+------+------------+
|   1 | A0001 | 橡皮   |  2.50 |   20 | 2024-07-25 |
+-----+-------+--------+-------+------+------------+
1 row in set (0.00 sec)

mysql> DELETE FROM orders WHERE gid = 'A0001';
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM goods;
+-------+-----------+-------+------+
| gid   | name      | price | num  |
+-------+-----------+-------+------+
| A0001 | 橡皮      |  2.50 |  100 |
| B0001 | 小楷本    |  2.80 |  210 |
| C0001 | 铅笔      |  1.20 |  120 |
| D0001 | 计算器    | 28.00 |   20 |
+-------+-----------+-------+------+
4 rows in set (0.00 sec)

创建更新触发器

mysql> delimiter //
mysql>  CREATE TRIGGER TRIGGER_UPDATE_AFTER_ORDERS
    ->      AFTER UPDATE ON orders
    ->      FOR EACH ROW
    ->      BEGIN
    ->           UPDATE goods SET num = num + (old.onum-new.onum);
    ->      END //
Query OK, 0 rows affected (0.00 sec)


mysql> DELIMITER ;

mysql> SELECT * FROM goods;
+-------+-----------+-------+------+
| gid   | name      | price | num  |
+-------+-----------+-------+------+
| A0001 | 橡皮      |  2.50 |  100 |
| B0001 | 小楷本    |  2.80 |  210 |
| C0001 | 铅笔      |  1.20 |  100 |
| D0001 | 计算器    | 28.00 |   20 |
+-------+-----------+-------+------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM orders;
+-----+-------+--------+-------+------+------------+
| oid | gid   | name   | price | onum | otime      |
+-----+-------+--------+-------+------+------------+
|   2 | C0001 | 铅笔   |  1.20 |   20 | 2024-07-25 |
+-----+-------+--------+-------+------+------------+
1 row in set (0.00 sec)

mysql> UPDATE orders SET onum = 40 WHERE name = '铅笔';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM goods;
+-------+-----------+-------+------+
| gid   | name      | price | num  |
+-------+-----------+-------+------+
| A0001 | 橡皮      |  2.50 |   80 |
| B0001 | 小楷本    |  2.80 |  190 |
| C0001 | 铅笔      |  1.20 |   80 |
| D0001 | 计算器    | 28.00 |    0 |
+-------+-----------+-------+------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM orders;
+-----+-------+--------+-------+------+------------+
| oid | gid   | name   | price | onum | otime      |
+-----+-------+--------+-------+------+------------+
|   2 | C0001 | 铅笔   |  1.20 |   40 | 2024-07-25 |
+-----+-------+--------+-------+------+------------+
1 row in set (0.00 sec)

创建存储过程

创建提取emp_new表所有员工姓名和工资的存储过程s1

mysql> USE mydb7_openlab
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-------------------------+
| Tables_in_mydb7_openlab |
+-------------------------+
| dept                    |
| emp                     |
| emp_new                 |
| user                    |
+-------------------------+
4 rows in set (0.00 sec)


mysql> desc emp_new;
+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| sid            | int         | YES  |     | NULL    |       |
| name           | varchar(11) | YES  |     | NULL    |       |
| age            | int         | YES  |     | NULL    |       |
| worktime_start | date        | YES  |     | NULL    |       |
| incoming       | int         | YES  |     | NULL    |       |
| dept2          | int         | YES  |     | NULL    |       |
+----------------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

mysql> SELECT name,age FROM emp_new;
+---------+------+
| name    | age  |
+---------+------+
| 张三    |   35 |
| 李四    |   32 |
| 王五    |   24 |
| 赵六    |   57 |
| 荣七    |   64 |
| 牛八    |   55 |
+---------+------+
6 rows in set (0.00 sec)


mysql> DELIMITER //
mysql> CREATE PROCEDURE s1()
    -> BEGIN 
    ->     SELECT name,age FROM emp_new;
    -> END //
Query OK, 0 rows affected (0.00 sec)


mysql> DELIMITER ;
mysql> CALL s1();
+---------+------+
| name    | age  |
+---------+------+
| 张三    |   35 |
| 李四    |   32 |
| 王五    |   24 |
| 赵六    |   57 |
| 荣七    |   64 |
| 牛八    |   55 |
+---------+------+
6 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

创建存储过程s2，实现输入员工姓名后返回员工的年龄

mysql> desc emp_new;
+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| sid            | int         | YES  |     | NULL    |       |
| name           | varchar(11) | YES  |     | NULL    |       |
| age            | int         | YES  |     | NULL    |       |
| worktime_start | date        | YES  |     | NULL    |       |
| incoming       | int         | YES  |     | NULL    |       |
| dept2          | int         | YES  |     | NULL    |       |
+----------------+-------------+------+-----+---------+-------+

mysql> SELECT * FROM emp_new;
+------+---------+------+----------------+----------+-------+
| sid  | name    | age  | worktime_start | incoming | dept2 |
+------+---------+------+----------------+----------+-------+
| 1789 | 张三    |   35 | 1980-01-01     |     4000 |   101 |
| 1674 | 李四    |   32 | 1983-04-01     |     3500 |   101 |
| 1776 | 王五    |   24 | 1990-07-01     |     2000 |   101 |
| 1568 | 赵六    |   57 | 1970-10-11     |     7500 |   102 |
| 1564 | 荣七    |   64 | 1963-10-11     |     8500 |   102 |
| 1879 | 牛八    |   55 | 1971-10-20     |     7300 |   103 |
+------+---------+------+----------------+----------+-------+
6 rows in set (0.00 sec)


mysql> DELIMITER //
mysql> CREATE PROCEDURE s2(IN in_name VARCHAR(11),OUT out_age INT)
    -> BEGIN
    ->     SELECT age INTO out_age FROM emp_new WHERE name = in_name;
    -> END //
Query OK, 0 rows affected (0.01 sec)


mysql> DELIMITER ;
mysql> CALL s2('张三',@out_age);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @out_age;
+----------+
| @out_age |
+----------+
|       35 |
+----------+
1 row in set (0.00 sec)

创建一个存储过程s3，有2个参数，传入部门号，返回该部门的平均工资

mysql> desc emp_new;
+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| sid            | int         | YES  |     | NULL    |       |
| name           | varchar(11) | YES  |     | NULL    |       |
| age            | int         | YES  |     | NULL    |       |
| worktime_start | date        | YES  |     | NULL    |       |
| incoming       | int         | YES  |     | NULL    |       |
| dept2          | int         | YES  |     | NULL    |       |
+----------------+-------------+------+-----+---------+-------+

mysql> SELECT * FROM emp_new;
+------+---------+------+----------------+----------+-------+
| sid  | name    | age  | worktime_start | incoming | dept2 |
+------+---------+------+----------------+----------+-------+
| 1789 | 张三    |   35 | 1980-01-01     |     4000 |   101 |
| 1674 | 李四    |   32 | 1983-04-01     |     3500 |   101 |
| 1776 | 王五    |   24 | 1990-07-01     |     2000 |   101 |
| 1568 | 赵六    |   57 | 1970-10-11     |     7500 |   102 |
| 1564 | 荣七    |   64 | 1963-10-11     |     8500 |   102 |
| 1879 | 牛八    |   55 | 1971-10-20     |     7300 |   103 |
+------+---------+------+----------------+----------+-------+
6 rows in set (0.00 sec)

mysql> DELIMITER //
mysql> CREATE PROCEDURE s3(IN in_dept2 INT,OUT avg_incoming DOUBLE)
    -> BEGIN
    ->      SELECT ROUND(AVG(incoming),2) INTO avg_incoming  FROM emp_new WHERE dept2 = in_dept2;
    -> END //
Query OK, 0 rows affected (0.00 sec)

mysql> CALL s3(101,@AVG_incoming);
    -> //
Query OK, 1 row affected (0.00 sec)

mysql> DELIMITER ;
mysql> SELECT @AVG_incoming;
+---------------+
| @AVG_incoming |
+---------------+
|       3166.67 |
+---------------+
1 row in set (0.00 sec)