MySQL作业

目录

1.实验需求1：

（1）

（2）

（3）

（4）

（5）

2.实验步骤1：

（1）完成上述实验需求1，需要先创建一个db_sch数据库并且使用数据库。

?（1.1）然后创建一个表：

（1.2）?在表中插入数据：

?（1.3）最后完成上述图片问题

（2）创建的数据库和使用的数据库和上述（1）一样

（2.1）然后创建一个表：

（2.2）在表中插入数据：

（2.3）最后完成上述图片问题

（3）创建的数据库和使用的数据库和上述（1）一样

（3.1）然后创建一个表：

（3.2）在表中插入数据：

（3.3）最后完成上述图片问题

（4）创建的数据库和使用的数据库和上述（1）一样

（4.1）然后创建一个表：

（4.2）在表中插入数据：

（4.3）最后完成上述图片问题

（5）创建的数据库和使用的数据库和上述（1）一样

（5.1）然后创建一个表：

（5.2）在表中插入数据：

（5.3）最后完成上述图片问题

3.实验需求2：

4.实验步骤2：

1.完成上述实验需求2，需要先创建一个db_sch数据库并且使用数据库。

2.然后创建一个数据表：

3.在表中插入数据：

4.最后完成上述的问题

? ? ? ? （1）创建一个可以统计表格内记录条数的存储函数 ，函数名为count_sch()

????????（2）创建一个存储过程avg_sai，有3个参数，分别是deptno，job，接收平均工资，功能查询emp表dept为30，job为销售员的平均工资

---

1.实验需求1：

（1）

?

（2）

（3）

?

（4）

?

（5）

?

2.实验步骤1：

（1）完成上述实验需求1，需要先创建一个db_sch数据库并且使用数据库。

mysql> create database db_sch default ?charset=utf8mb4;
Query OK, 1 row affected (0.01 sec)
mysql> use db_sch;
Database changed

?（1.1）然后创建一个表：

mysql> CREATE TABLE tb_user_log ( ?
? ? -> ? ? id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID', ?
? ? -> ? ? uid INT NOT NULL COMMENT '用户ID', ?
? ? -> ? ? article_id INT NOT NULL COMMENT '视频ID', ?
? ? -> ? ? in_time datetime COMMENT '进入时间', ?
? ? -> ? ? out_time datetime COMMENT '离开时间', ?
? ? -> ? ? sign_in TINYINT DEFAULT 0 COMMENT '是否签到' ?
? ? -> ) CHARACTER SET utf8 COLLATE utf8_bin;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

（1.2）?在表中插入数据：

mysql> INSERT INTO tb_user_log(uid, article_id, in_time, out_time, sign_in)
? ? -> VALUES
? ? -> (101,0,'2021-11-01 10:00:00', '2021-11-01 10:00:42',1),
? ? -> (102,9001,'2021-11-01 10:00:00', '2021-11-01 10:00:09',0),
? ? -> (103,9001,'2021-11-01 10:00:01', '2021-11-01 10:01:50',0),
? ? -> (101,9002,'2021-11-02 10:00:09', '2021-11-02 10:00:28',0),
? ? -> (103,9002,'2021-11-02 10:00:51', '2021-11-02 10:00:59',0),
? ? -> (104,9001,'2021-11-02 11:00:28', '2021-11-02 10:00:50',0),
? ? -> (101,9003,'2021-11-03 11:00:55', '2021-11-03 11:01:24',0),
? ? -> (104,9003,'2021-11-03 11:00:45', '2021-11-03 11:00:55',0),
? ? -> (105,9003,'2021-11-03 11:00:53', '2021-11-03 11:00:59',0),
? ? -> (101,9002,'2021-11-04 11:00:55', '2021-11-04 11:00:59',0);
Query OK, 10 rows affected (0.00 sec)
Records: 10 ?Duplicates: 0 ?Warnings: 0

?（1.3）最后完成上述图片问题

mysql> SELECT ??
? ? -> ? ? first_days.first_day AS '日期', ?
? ? -> ? ? CONCAT(ROUND(COUNT(DISTINCT second_days.uid) / COUNT(DISTINCT first_days.uid) * 100, 2), '%') AS '次日留存率' ?
? ? -> FROM ( ?
? ? -> ? ? SELECT ??
? ? -> ? ? ? ? uid, ?
? ? -> ? ? ? ? MIN(DATE(in_time)) AS first_day ?
? ? -> ? ? FROM tb_user_log ?
? ? -> ? ? WHERE DATE(in_time) BETWEEN '2021-11-01' AND '2021-11-30' ?
? ? -> ? ? GROUP BY uid ?
? ? -> ) AS first_days ?
? ? -> LEFT JOIN tb_user_log AS second_days ON first_days.uid = second_days.uid ?
? ? -> ? ? AND DATE(second_days.in_time) = DATE_ADD(first_days.first_day, INTERVAL 1 DAY) ?
? ? -> GROUP BY first_days.first_day ?
? ? -> ORDER BY first_days.first_day;

?

（2）创建的数据库和使用的数据库和上述（1）一样

（2.1）然后创建一个表：

mysql> DROP TABLE IF EXISTS `Orderltems`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `Orderltems`(
? ? -> prod_id VARCHAR(255) NOT NULL COMMENT '商品号',
? ? -> order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
? ? -> quantity INT(255) NOT NULL COMMENT '商品数量'
? ? -> );
Query OK, 0 rows affected, 1 warning (0.00 sec)

（2.2）在表中插入数据：

mysql> INSERT `Orderltems` VALUES('BRO1','a1','105'),('BRO2','a2','1100'),
? ? -> ('BR02','a2','200'),('BR03','a4','1121'),('BR017','a5','10'),('BR02','a2','19'),
? ? -> ('BR017','a','75');
Query OK, 7 rows affected (0.00 sec)
Records: 7 ?Duplicates: 0 ?Warnings: 0

（2.3）最后完成上述图片问题

mysql> SELECT order_num, prod_id, quantity ?
? ? -> FROM Orderltems ?
? ? -> WHERE prod_id IN ('BRO1', 'BR02', 'BRO3') AND quantity >= 100;

（3）创建的数据库和使用的数据库和上述（1）一样

（3.1）然后创建一个表：

mysql> CREATE TABLE IF NOT EXISTS `Products`(
? ? -> `prod_id` VARCHAR(255) NOT NULL COMMENT '产品ID',
? ? -> `prod_name` VARCHAR(255) NOT NULL COMMENT '产品名称'
? ? -> );
Query OK, 0 rows affected (0.01 sec)

mysql> DROP TABLE IF EXISTS `Orderltems`;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE IF NOT EXISTS `Orderltems`(
? ? -> prod_id VARCHAR(255) NOT NULL COMMENT '产品id',
? ? -> quantity INT(16) NOT NULL COMMENT '商品数量'
? ? -> );
Query OK, 0 rows affected, 1 warning (0.01 sec)

?

（3.2）在表中插入数据：

mysql> INSERT INTO `Products` VALUES ('a0001','egg'),
? ? -> ('a0002','sockets'),
? ? -> ('a0013','coffee'),
? ? -> ('a0003','cola');
Query OK, 4 rows affected (0.00 sec)
Records: 4 ?Duplicates: 0 ?Warnings: 0

mysql> INSERT `Orderltems` VALUES ('a0001',105),('a0002',1100),('a0002',200),
? ? -> ('a0013',1121),('a0003',10),('a0003',19),('a0003',5);
Query OK, 7 rows affected (0.00 sec)
Records: 7 ?Duplicates: 0 ?Warnings: 0

（3.3）最后完成上述图片问题

mysql> SELECT ??
? ? -> ? ? p.prod_name, ?
? ? -> ? ? COALESCE(SUM(o.quantity), 0) AS quant_sold ?
? ? -> FROM ??
? ? -> ? ? Products p ?
? ? -> LEFT JOIN ??
? ? -> ? ? Orderltems o ON p.prod_id = o.prod_id ?
? ? -> GROUP BY ??
? ? -> ? ? p.prod_name, p.prod_id;

（4）创建的数据库和使用的数据库和上述（1）一样

（4.1）然后创建一个表：

mysql> DROP TABLE IF EXISTS `Customers`;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> CREATE TABLE IF NOT EXISTS `Customers`(
? ? -> cust_id VARCHAR(255) NOT NULL COMMENT '客户id',
? ? -> cust_name VARCHAR(255) NOT NULL COMMENT '客户姓名'
? ? -> );
Query OK, 0 rows affected (0.01 sec)

mysql> DROP TABLE IF EXISTS `Orders`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `Orders`(
? ? -> order_num VARCHAR(255) NOT NULL COMMENT '商品单号',
? ? -> cust_id VARCHAR(255) NOT NULL COMMENT '顾客id'
? ? -> );
Query OK, 0 rows affected (0.01 sec)

（4.2）在表中插入数据：

mysql> INSERT `Customers` VALUES ('cust10','andy'),('cust1','ben'),
? ? -> ('cust2' ,'tony'),('cust22','tom'),('cust221','an'),('cust2217','hex'),
? ? -> ('cust40','ace');
Query OK, 7 rows affected (0.00 sec)
Records: 7 ?Duplicates: 0 ?Warnings: 0

mysql> INSERT `Orders` VALUES ('a1','cust10'),('a2','cust1'),('a3','cust2'),
? ? -> ('a4','cust22'),('a5','cust221'),('a7','cust2217');
Query OK, 6 rows affected (0.01 sec)
Records: 6 ?Duplicates: 0 ?Warnings: 0

（4.3）最后完成上述图片问题

mysql> SELECT c.cust_name, o.order_num ?
? ? -> FROM Customers c ?
? ? -> LEFT JOIN Orders o ON c.cust_id = o.cust_id ?
? ? -> ORDER BY c.cust_name ASC;

（5）创建的数据库和使用的数据库和上述（1）一样

（5.1）然后创建一个表：

mysql> DROP TABLE IF EXISTS tb_user_event;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE tb_user_event(
? ? -> id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
? ? -> uid INT NOT NULL COMMENT '用户ID',
? ? -> product_id INT NOT NULL COMMENT '商品ID',
? ? -> event_time datetime COMMENT '行为时间',
? ? -> if_click TINYINT COMMENT '是否点击',
? ? -> if_cart TINYINT COMMENT '是否加购物车',
? ? -> if_payment TINYINT COMMENT '是否付款',
? ? -> if_refund TINYINT COMMENT '是否退货退款'
? ? -> ) CHARACTER SET utf8 COLLATE utf8_bin;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

（5.2）在表中插入数据：

mysql> INSERT INTO tb_user_event(uid, product_id, event_time, if_click,?
? ? -> ?if_cart,if_payment, if_refund) VALUES
? ? -> (101,8001,'2021-10-01 10:00:00',0,0,0,0),
? ? -> (102,8001,'2021-10-01 10:00:00',1,0,0,0),
? ? -> (103,8001,'2021-10-01 10:00:00',1,1,0,0),
? ? -> (104,8001,'2021-10-02 10:00:00',1,1,1,0),
? ? -> (105,8001,'2021-10-02 10:00:00',1,1,1,0),
? ? -> (101,8002,'2021-10-03 10:00:00',1,1,1,0),
? ? -> (109,8001,'2021-10-04 10:00:01',1,1,1,1);
Query OK, 7 rows affected (0.01 sec)
Records: 7 ?Duplicates: 0 ?Warnings: 0

（5.3）最后完成上述图片问题

mysql> SELECT ??
? ? -> ? ? product_id, ?
? ? -> ? ? COUNT(*) AS total_events, ?
? ? -> ? ? SUM(if_click) AS total_clicks, ?
? ? -> ? ? SUM(if_cart) AS total_adds_to_cart, ?
? ? -> ? ? SUM(if_payment) AS total_payments, ?
? ? -> ? ? SUM(if_refund) AS total_refunds ?
? ? -> FROM ??
? ? -> ? ? tb_user_event ?
? ? -> WHERE ??
? ? -> ? ? event_time BETWEEN '2021-10-01' AND '2021-10-31' ?
? ? -> GROUP BY ??
? ? -> ? ? product_id ?
? ? -> HAVING ??
? ? -> ? ? total_refunds <= total_events / 2;

3.实验需求2：

?创建表并插入数据：

字段名 数据类型 主键 外键 非空 唯一 自增
? ? ? ? id INT 是 否 是 ?是 否
? ? ? ? name VARCHAR(50) 否 否 是 ?否 否
? ? ? ? glass ?VARCHAR(50) 否 否 是 ?否 否

sch 表内容
id name glass
1 xiaommg glass 1
2 xiaojun glass 2

1、创建一个可以统计表格内记录条数的存储函数 ，函数名为count_sch()

2、创建一个存储过程avg_sai，有3个参数，分别是deptno，job，接收平均工资，功能查询emp表dept为30，job为销售员的平均工资

4.实验步骤2：

1.完成上述实验需求2，需要先创建一个db_sch数据库并且使用数据库。

mysql> create database db_sch default ?charset=utf8mb4;
Query OK, 1 row affected (0.01 sec)
mysql> use db_sch;
Database changed

2.然后创建一个数据表：

mysql> create table sch(
? ? -> id int primary key auto_increment,
? ? -> name varchar(255) not null,
? ? -> glass varchar(255) not null
? ? -> );
Query OK, 0 rows affected (0.02 sec)

3.在表中插入数据：

mysql> insert into sch (name, glass) values
? ? -> ('xiaommg', 'glass 1'),
? ? -> ('xiaojun', 'glass 2');
Query OK, 2 rows affected (0.01 sec)
Records: 2 ?Duplicates: 0 ?Warnings: 0

4.最后完成上述的问题

? ? ? ? （1）创建一个可以统计表格内记录条数的存储函数 ，函数名为count_sch()

mysql> create function count_sch() returns int begin declare i int default 0; select count(1) into i from sch; return i; end//
Query OK, 0 rows affected (0.01 sec)

?

注意：出现下面错误加入（mysql> SET GLOBAL log_bin_trust_function_creators = 1;）命令

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

????????（2）创建一个存储过程avg_sai，有3个参数，分别是deptno，job，接收平均工资，功能查询emp表dept为30，job为销售员的平均工资

mysql> delimiter !
mysql> create procedure avg_sal(in deptno int,in job varchar(50),out avg_salary decimal(10, 2))
? ? -> BEGIN
? ? -> select avg(salary) into avg_salary from employees where department_id = deptno and job_id = job;
? ? -> END !
Query OK, 0 rows affected (0.01 sec)

?