数据库（MySQL）练习

一、练习

1.15练习

win11安装配置MySQL超详细教程: https://baijiahao.baidu.com/s?id=1786910666566008458&wfr=spider&for=pc

准备工作：

mysql -uroot -p #以管理员身份登录

mysql> select user(); #查看当前登录账户，登录方式
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)mysql> show databases; #查看当下账户的所有库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb1_test         |
| mydb2_stuinfo      |
| mydb3_employee     |
| mydb4_product      |
| mydb5_sales        |
| mydb_temp1         |
| mysql              |
| performance_schema |
| sys                |
| temp1              |
+--------------------+
11 rows in set (0.00 sec)mysql> create database mydb6_product; #创建新库 mydb6_product
Query OK, 1 row affected (0.02 sec)mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb1_test         |
| mydb2_stuinfo      |
| mydb3_employee     |
| mydb4_product      |
| mydb5_sales        |
| mydb6_product      |
| mydb_temp1         |
| mysql              |
| performance_schema |
| sys                |
| temp1              |
+--------------------+
12 rows in set (0.00 sec)
mysql> use mydb6_product; #转换当前登录库
Database changed
mysql> select database(); 
+---------------+
| database()    |
+---------------+
| mydb6_product |
+---------------+
1 row in set (0.00 sec)

employees表：

主键：primary key
不能为空：not null
设置默认值：default’***'

mysql> create table employees(id int primary key , name varchar(50) not null ,age int ,gender varchar(10) not null default'unknown' ,salary float);
Query OK, 0 rows affected (0.04 sec)mysql> desc employees;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int         | NO   | PRI | NULL    |       |
| name   | varchar(50) | NO   |     | NULL    |       |
| age    | int         | YES  |     | NULL    |       |
| gender | varchar(10) | NO   |     | unknown |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.02 sec)

orders表

mysql> create table orders(id int primary key , name varchar(100) not null ,price float,
quantity int ,category varchar(50));
Query OK, 0 rows affected (0.03 sec)mysql> desc orders;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int          | NO   | PRI | NULL    |       |
| name     | varchar(100) | NO   |     | NULL    |       |
| price    | float        | YES  |     | NULL    |       |
| quantity | int          | YES  |     | NULL    |       |
| category | varchar(50)  | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

invoices表：

主键自增长：primary key auto_increment
外键关联到orders表的id列：,foreign key(order_id) references orders(id) 注意用逗号分隔后再开始写外键关联
日期型：date
要求数据大于0：check(数据>0)

mysql> create table invoices(number int primary key auto_increment, order_id int ,foreign key(order_id) references orders(id) ,in_date date,total_amount float check(total_amount>0));
Query OK, 0 rows affected (0.03 sec)mysql> desc invoices;
+--------------+-------+------+-----+---------+----------------+
| Field        | Type  | Null | Key | Default | Extra          |
+--------------+-------+------+-----+---------+----------------+
| number       | int   | NO   | PRI | NULL    | auto_increment |
| order_id     | int   | YES  | MUL | NULL    |                |
| in_date      | date  | YES  |     | NULL    |                |
| total_amount | float | YES  |     | NULL    |                |
+--------------+-------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

3张表如下:

mysql> show tables;
+-------------------------+
| Tables_in_mydb6_product |
+-------------------------+
| employees               |
| invoices                |
| orders                  |
+-------------------------+
3 rows in set (0.01 sec)

1.16练习

准备工作：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb1_test         |
| mydb2_stuinfo      |
| mydb3_employee     |
| mydb4_product      |
| mydb5_sales        |
| mydb6_product      |
| mydb7_openlab      |
| mydb_temp1         |
| mydbx_temp3        |
| mysql              |
| performance_schema |
| sys                |
| temp1              |
+--------------------+
14 rows in set (0.00 sec)mysql> create database mydb8_work;
Query OK, 1 row affected (0.01 sec)mysql> use mydb8_work;
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| mydb8_work |
+------------+
1 row in set (0.00 sec)

插入数据：

mysql> insert into t_worker values(101,1001,'2015-5-4',7500.00,'群众','张春燕','1990-7
-1');
Query OK, 1 row affected (0.02 sec)mysql> insert into t_worker values(101,1002,'2019-2-6',5200.00,'团员','李名博','1997-2-8');
Query OK, 1 row affected (0.02 sec)mysql> insert into t_worker values(102,1003,'2008-1-4',10500.00,'党员','王博涵','1983-
6-8');
Query OK, 1 row affected (0.00 sec)mysql> insert into t_worker values(102,1004,'2016-10-10',5500.00,'群众','赵小军','1994-9-5');
Query OK, 1 row affected (0.02 sec)mysql> insert into t_worker values(102,1005,'2014-4-1',8800.00,'党员','钱有财','1992-12-30');
Query OK, 1 row affected (0.01 sec)mysql> insert into t_worker values(103,1006,'2019-5-5',5500.00,'党员','孙菲菲','1996-9
-2');
Query OK, 1 row affected (0.02 sec)

完成查询：

(1)、显示 所有 职工 的基本信息。

mysql> select * from t_worker;
+---------------+-----------+-------------+----------+----------+--------+------------+
| department_id | worker_id | worker_date | wages    | politics | name   | borth_date |
+---------------+-----------+-------------+----------+----------+--------+------------+
|           101 |      1001 | 2015-05-04  |  7500.00 | 群众     | 张春燕 | 1990-07-01 |
|           101 |      1002 | 2019-02-06  |  5200.00 | 团员     | 李名博 | 1997-02-08 |
|           102 |      1003 | 2008-01-04  | 10500.00 | 党员     | 王博涵 | 1983-06-08 |
|           102 |      1004 | 2016-10-10  |  5500.00 | 群众     | 赵小军 | 1994-09-05 |
|           102 |      1005 | 2014-04-01  |  8800.00 | 党员     | 钱有财 | 1992-12-30 |
|           103 |      1006 | 2019-05-05  |  5500.00 | 党员     | 孙菲菲 | 1996-09-02 |
+---------------+-----------+-------------+----------+----------+--------+------------+
6 rows in set (0.00 sec)

(2)、查询所有职工所属部门的部门号，不显示重复的部门号。
去重：distinct

mysql> select * from t_worker;
+---------------+-----------+-------------+----------+----------+--------+------------+
| department_id | worker_id | worker_date | wages    | politics | name   | borth_date |
+---------------+-----------+-------------+----------+----------+--------+------------+
|           101 |      1001 | 2015-05-04  |  7500.00 | 群众     | 张春燕 | 1990-07-01 |
|           101 |      1002 | 2019-02-06  |  5200.00 | 团员     | 李名博 | 1997-02-08 |
|           102 |      1003 | 2008-01-04  | 10500.00 | 党员     | 王博涵 | 1983-06-08 |
|           102 |      1004 | 2016-10-10  |  5500.00 | 群众     | 赵小军 | 1994-09-05 |
|           102 |      1005 | 2014-04-01  |  8800.00 | 党员     | 钱有财 | 1992-12-30 |
|           103 |      1006 | 2019-05-05  |  5500.00 | 党员     | 孙菲菲 | 1996-09-02 |
+---------------+-----------+-------------+----------+----------+--------+------------+
6 rows in set (0.00 sec)mysql> select distinct department_id from t_worker;
+---------------+
| department_id |
+---------------+
|           101 |
|           102 |
|           103 |
+---------------+
3 rows in set (0.00 sec)

(3)、求出所有职工的人数。
聚合函数 - count（统计数量）
select 聚合函数(字段列表) from 表名

有主键count(worker_id)，不然就用count(1)

mysql> select * from t_worker;
+---------------+-----------+-------------+----------+----------+--------+------------+
| department_id | worker_id | worker_date | wages    | politics | name   | borth_date |
+---------------+-----------+-------------+----------+----------+--------+------------+
|           101 |      1001 | 2015-05-04  |  7500.00 | 群众     | 张春燕 | 1990-07-01 |
|           101 |      1002 | 2019-02-06  |  5200.00 | 团员     | 李名博 | 1997-02-08 |
|           102 |      1003 | 2008-01-04  | 10500.00 | 党员     | 王博涵 | 1983-06-08 |
|           102 |      1004 | 2016-10-10  |  5500.00 | 群众     | 赵小军 | 1994-09-05 |
|           102 |      1005 | 2014-04-01  |  8800.00 | 党员     | 钱有财 | 1992-12-30 |
|           103 |      1006 | 2019-05-05  |  5500.00 | 党员     | 孙菲菲 | 1996-09-02 |
+---------------+-----------+-------------+----------+----------+--------+------------+
6 rows in set (0.00 sec)mysql> select count(worker_id) from t_worker;
+------------------+
| count(worker_id) |
+------------------+
|                6 |
+------------------+
1 row in set (0.03 sec)

(4)、列出最高工和最低工资。
聚合函数：

mysql> select max(wages) '最高工资' , min(wages)'最低工资' from t_worker;
+----------+----------+
| 最高工资 | 最低工资 |
+----------+----------+
| 10500.00 |  5200.00 |
+----------+----------+
1 row in set (0.00 sec)

(5)、列出职工的平均工资和总工资。

mysql> select round(avg(wages),2) '平均工资',sum(wages)'总工资' from t_worker;
+----------+----------+
| 平均工资 | 总工资   |
+----------+----------+
|  7166.67 | 43000.00 |
+----------+----------+
1 row in set (0.01 sec)

(6)、创建一个只有职工号、姓名和参加工作的新表，名为工作日期表。

mysql> create table t_workdate select worker_id,name,worker_date from t_worker;
Query OK, 6 rows affected (0.03 sec)
Records: 6  Duplicates: 0  Warnings: 0mysql> select * from t_workdate;
+-----------+--------+-------------+
| worker_id | name   | worker_date |
+-----------+--------+-------------+
|      1001 | 张春燕 | 2015-05-04  |
|      1002 | 李名博 | 2019-02-06  |
|      1003 | 王博涵 | 2008-01-04  |
|      1004 | 赵小军 | 2016-10-10  |
|      1005 | 钱有财 | 2014-04-01  |
|      1006 | 孙菲菲 | 2019-05-05  |
+-----------+--------+-------------+
6 rows in set (0.00 sec)

(7)、显示所有党员的年龄。

mysql> select year(now())-year(borth_date) '年龄' from t_worker where politics='党员';+------+
| 年龄 |
+------+
|   42 |
|   33 |
|   29 |
+------+
3 rows in set (0.01 sec)

使表格更加完整：

mysql> select name'姓名',politics'政治面貌',year(now())-year(borth_date) '年龄' from t
_worker where politics='党员';
+--------+----------+------+
| 姓名   | 政治面貌 | 年龄 |
+--------+----------+------+
| 王博涵 | 党员     |   42 |
| 钱有财 | 党员     |   33 |
| 孙菲菲 | 党员     |   29 |
+--------+----------+------+
3 rows in set (0.00 sec)

(8)、列出工资在4000-8000之间的所有职工姓名

mysql> select name from t_worker where wages>=4000 and wages<=8000;
+--------+
| name   |
+--------+
| 张春燕 |
| 李名博 |
| 赵小军 |
| 孙菲菲 |
+--------+
4 rows in set (0.00 sec)

(9)、列出所有孙姓和李姓的职工姓名。

mysql> select name '姓名' from t_worker where name like '孙%' or name like '李%' ;
+--------+
| 姓名   |
+--------+
| 李名博 |
| 孙菲菲 |
+--------+
2 rows in set (0.00 sec)

(10)、列出所有部门号为102和103日不是党员的职工号、姓名。

mysql> select worker_id,name from t_worker where (department_id=102 or department_id=103) and politics != '党员';
+-----------+--------+
| worker_id | name   |
+-----------+--------+
|      1004 | 赵小军 |
+-----------+--------+
1 row in set (0.00 sec)

(11)、将职工表t_worker中的职工按出生的先后顺序排序。

升序：asc(ascend)，默认 降序：desc (descend)

mysql> select * from t_worker order by borth_date;
+---------------+-----------+-------------+----------+----------+--------+------------+
| department_id | worker_id | worker_date | wages    | politics | name   | borth_date |
+---------------+-----------+-------------+----------+----------+--------+------------+
|           102 |      1003 | 2008-01-04  | 10500.00 | 党员     | 王博涵 | 1983-06-08 |
|           101 |      1001 | 2015-05-04  |  7500.00 | 群众     | 张春燕 | 1990-07-01 |
|           102 |      1005 | 2014-04-01  |  8800.00 | 党员     | 钱有财 | 1992-12-30 |
|           102 |      1004 | 2016-10-10  |  5500.00 | 群众     | 赵小军 | 1994-09-05 |
|           103 |      1006 | 2019-05-05  |  5500.00 | 党员     | 孙菲菲 | 1996-09-02 |
|           101 |      1002 | 2019-02-06  |  5200.00 | 团员     | 李名博 | 1997-02-08 |
+---------------+-----------+-------------+----------+----------+--------+------------+
6 rows in set (0.00 sec)

(12)、显示工资最高的前3名职工的职工号和姓名。
分页查询 - limit 记录数 # 从第一条记录开始显示几条记录

mysql> select worker_id,name from t_worker order by wages desc limit 3;
+-----------+--------+
| worker_id | name   |
+-----------+--------+
|      1003 | 王博涵 |
|      1005 | 钱有财 |
|      1001 | 张春燕 |
+-----------+--------+
3 rows in set (0.00 sec)

(13)、求出各部门党员的人数。

mysql> select department_id '部门号',count(worker_id) '党员人数' from t_worker where p
olitics = '党员' group by department_id;
+--------+----------+
| 部门号 | 党员人数 |
+--------+----------+
|    102 |        2 |
|    103 |        1 |
+--------+----------+
2 rows in set (0.00 sec)

(14)、统计各部门的工资和平均工资并保留2位小数

mysql> select department_id '部门号',sum(wages)'部门工资总和'from t_worker group by de
partment_id;
+--------+--------------+
| 部门号 | 部门工资总和 |
+--------+--------------+
|    101 |     12700.00 |
|    102 |     24800.00 |
|    103 |      5500.00 |
+--------+--------------+
3 rows in set (0.00 sec)

(15)、列出总人数大于等于3的部门号和总人数。

mysql> select department_id '部门号',count(worker_id)'人数'from t_worker group by depa
rtment_id having count(worker_id)>=3;
+--------+------+
| 部门号 | 人数 |
+--------+------+
|    102 |    3 |
+--------+------+
1 row in set (0.00 sec)

二、注意事项

2.1 第四天

日期需要以字符串形式添加 eg.‘2000-10-1’

mysql> insert into student2 value(1001 , '李成峰','男','2000-10-1');
Query OK, 1 row affected (0.01 sec)mysql> select * from student2;
+------+--------+------+------------+
| id   | name   | sex  | birthday   |
+------+--------+------+------------+
| 1001 | 李成峰 | 男   | 2000-10-01 |
+------+--------+------+------------+
1 row in set (0.00 sec)

delete一定要带where，

mysql> select * from student2;
+------+--------+------+------------+
| id   | name   | sex  | birthday   |
+------+--------+------+------------+
| 1001 | 李成峰 | 男   | 2000-10-01 |
| 1002 | 薛佳尹 | 女   | 2012-12-12 |
| 1003 | 孙德胜 | 男   | 1998-12-31 |
| 1004 | 凤飞飞 | 女   | 2009-03-15 |
| 1005 | 尹志平 | 男   | 1235-09-01 |
+------+--------+------+------------+
5 rows in set (0.00 sec)mysql> delete from student2 where id=1005; #单条删除
Query OK, 1 row affected (0.02 sec)mysql> select * from student2;
+------+--------+------+------------+
| id   | name   | sex  | birthday   |
+------+--------+------+------------+
| 1001 | 李成峰 | 男   | 2000-10-01 |
| 1002 | 薛佳尹 | 女   | 2012-12-12 |
| 1003 | 孙德胜 | 男   | 1998-12-31 |
| 1004 | 凤飞飞 | 女   | 2009-03-15 |
+------+--------+------+------------+
4 rows in set (0.00 sec)

delete from student2不加where，删除全表内容，表结构还存在
drop 删除包括表结构

删除之前可开启一个事务，方便回溯

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
开启一个事务，方便回溯mysql> delete from student2 ;
Query OK, 4 rows affected (0.00 sec)mysql> select * from student2;
Empty set (0.00 sec)mysql> desc student2;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | NO   | PRI | NULL    |       |
| name     | varchar(30) | NO   |     | NULL    |       |
| sex      | char(2)     | YES  |     | 女      |       |
| birthday | date        | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)mysql> rollback;
Query OK, 0 rows affected (0.00 sec)mysql> select * from student2;
+------+--------+------+------------+
| id   | name   | sex  | birthday   |
+------+--------+------+------------+
| 1001 | 李成峰 | 男   | 2000-10-01 |
| 1002 | 薛佳尹 | 女   | 2012-12-12 |
| 1003 | 孙德胜 | 男   | 1998-12-31 |
| 1004 | 凤飞飞 | 女   | 2009-03-15 |
+------+--------+------+------------+
4 rows in set (0.00 sec)