mysql练习题

练习题1

学生信息管理

假设有一个名为students的表，结构如下：

mysql> create database school;
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> use school;
Database changed
mysql> create table students(id int auto_increment primary key,name varchar(50) not null,age
int,grade varchar(10),email varchar(100) unique);
Query OK, 0 rows affected (0.02 sec)

mysql> desc students;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int          | NO   | PRI | NULL    | auto_increment |
| name  | varchar(50)  | NO   |     | NULL    |                |
| age   | int          | YES  |     | NULL    |                |
| grade | varchar(10)  | YES  |     | NULL    |                |
| email | varchar(100) | YES  | UNI | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

mysql> 

插入5个学生的信息

mysql> insert into students(name,age,grade,email)values('Alice',20,'一年级','1234@qq.com'),('Bob',22,'二年级','123a@qq.com'),('charlie',19,'一年级','12abc@qq.com'),('david',23,'二年级','abcd@qq.com'),('eva',21,'二年级','111@qq.com');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> 

 查询所有年龄大于20岁的学生。

mysql> select * from students where age>20;
+----+-------+------+-----------+-------------+
| id | name  | age  | grade     | email       |
+----+-------+------+-----------+-------------+
|  2 | Bob   |   22 | 二年级    | 123a@qq.com |
|  4 | david |   23 | 二年级    | abcd@qq.com |
|  5 | eva   |   21 | 二年级    | 111@qq.com  |
+----+-------+------+-----------+-------------+
3 rows in set (0.00 sec)

mysql> 

将年龄为22的学生的年龄修改为23。

mysql> update students set age=23 where age=22;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from students;
+----+---------+------+-----------+--------------+
| id | name    | age  | grade     | email        |
+----+---------+------+-----------+--------------+
|  1 | Alice   |   20 | 一年级    | 1234@qq.com  |
|  2 | Bob     |   23 | 二年级    | 123a@qq.com  |
|  3 | charlie |   19 | 一年级    | 12abc@qq.com |
|  4 | david   |   23 | 二年级    | abcd@qq.com  |
|  5 | eva     |   21 | 二年级    | 111@qq.com   |
+----+---------+------+-----------+--------------+
5 rows in set (0.00 sec)

删除年龄最小的学生。

order by age desc //降序

order by age asc //升序

mysql> delete from students order by age asc limit 1; //按照age字段的值进行升序排序
Query OK, 1 row affected (0.00 sec)
mysql> select * from students;
+----+-------+------+-----------+-------------+
| id | name  | age  | grade     | email       |
+----+-------+------+-----------+-------------+
|  1 | Alice |   20 | 一年级    | 1234@qq.com |
|  2 | Bob   |   23 | 二年级    | 123a@qq.com |
|  4 | david |   23 | 二年级    | abcd@qq.com |
|  5 | eva   |   21 | 二年级    | 111@qq.com  |
+----+-------+------+-----------+-------------+
4 rows in set (0.00 sec)

 练习题2

假设有一个名为orders的表，存储了订单的信息，结构如下：

mysql> create database sale;
Query OK, 1 row affected (0.00 sec)

mysql> use sale;
Database changed
mysql> 
mysql> create table orders(order_id int auto_increment primary key,customer_id int,product_name varchar(100),quantity int,order_date date);
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> desc orders;
+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| order_id     | int          | NO   | PRI | NULL    | auto_increment |
| customer_id  | int          | YES  |     | NULL    |                |
| product_name | varchar(100) | YES  |     | NULL    |                |
| quantity     | int          | YES  |     | NULL    |                |
| order_date   | date         | YES  |     | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> 

插入3个订单的信息。

mysql> insert into orders(customer_id,product_name,quantity,order_date)values(1,'laptop',2,'2023-01-01'),(2,'phone',1,'2023-02-15'),(3,'monitor',3,'2023-03-08');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> 
mysql> select * from orders;
+----------+-------------+--------------+----------+------------+
| order_id | customer_id | product_name | quantity | order_date |
+----------+-------------+--------------+----------+------------+
|        1 |           1 | laptop       |        2 | 2023-01-01 |
|        2 |           2 | phone        |        1 | 2023-02-15 |
|        3 |           3 | monitor      |        3 | 2023-03-08 |
+----------+-------------+--------------+----------+------------+
3 rows in set (0.00 sec)

 查询在2023年购买的所有订单。

mysql> select * from orders where year(order_date)=2023
    -> ;
+----------+-------------+--------------+----------+------------+
| order_id | customer_id | product_name | quantity | order_date |
+----------+-------------+--------------+----------+------------+
|        1 |           1 | laptop       |        2 | 2023-01-01 |
|        2 |           2 | phone        |        1 | 2023-02-15 |
|        3 |           3 | monitor      |        3 | 2023-03-08 |
+----------+-------------+--------------+----------+------------+
3 rows in set (0.00 sec)
#查一月的
mysql> select * from orders where year(order_date)=2023 and month(order_date)=1;
+----------+-------------+--------------+----------+------------+
| order_id | customer_id | product_name | quantity | order_date |
+----------+-------------+--------------+----------+------------+
|        1 |           1 | laptop       |        2 | 2023-01-01 |
+----------+-------------+--------------+----------+------------+
1 row in set (0.00 sec)

mysql> 

 更新订单ID为2的订单数量，增加5个。

mysql> update orders set quantity=quantity+5 where order_id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from orders;
+----------+-------------+--------------+----------+------------+
| order_id | customer_id | product_name | quantity | order_date |
+----------+-------------+--------------+----------+------------+
|        1 |           1 | laptop       |        2 | 2023-01-01 |
|        2 |           2 | phone        |        6 | 2023-02-15 |
|        3 |           3 | monitor      |        3 | 2023-03-08 |
+----------+-------------+--------------+----------+------------+
3 rows in set (0.00 sec)

删除未指定customer_id的订单（即customer_id为NULL的订单）。

mysql> insert into orders(customer_id,product_name,quantity,order_date)values(null,'top',3,'2023-10-20');
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> select * from orders;
+----------+-------------+--------------+----------+------------+
| order_id | customer_id | product_name | quantity | order_date |
+----------+-------------+--------------+----------+------------+
|        1 |           1 | laptop       |        2 | 2023-01-01 |
|        2 |           2 | phone        |        6 | 2023-02-15 |
|        3 |           3 | monitor      |        3 | 2023-03-08 |
|        4 |        NULL | top          |        3 | 2023-10-20 |
+----------+-------------+--------------+----------+------------+
4 rows in set (0.00 sec)

mysql> delete from orders where customer_id is null;
Query OK, 1 row affected (0.00 sec)

mysql> select * from orders;
+----------+-------------+--------------+----------+------------+
| order_id | customer_id | product_name | quantity | order_date |
+----------+-------------+--------------+----------+------------+
|        1 |           1 | laptop       |        2 | 2023-01-01 |
|        2 |           2 | phone        |        6 | 2023-02-15 |
|        3 |           3 | monitor      |        3 | 2023-03-08 |
+----------+-------------+--------------+----------+------------+
3 rows in set (0.00 sec)