当前位置：首页 > news >正文

MySQL SQL 编程练习

news 来源：原创 2024/9/20 1:06:14

创建表并插入数据

查看表结构

创建触发器

创建INSERT 触发器

创建DELETE 触发器

创建更新触发器

创建存储过程

创建提取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 goodsbeginset 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: 0mysql> 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 -ADatabase 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)