练习题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)