网站服务器价格,中国公司排名500强名单,网页定制公司,熊掌号如何做网站数据库#xff08;MySQL#xff09;练习 一、练习1.15练习1.16练习 二、注意事项2.1 第四天 一、练习
1.15练习 win11安装配置MySQL超详细教程: https://baijiahao.baidu.com/s?id1786910666566008458wfrspiderforpc
准备工作#xff1a;
mysql -uroot -p #以… 数据库MySQL练习 一、练习1.15练习1.16练习 二、注意事项2.1 第四天 一、练习
1.15练习 win11安装配置MySQL超详细教程: https://baijiahao.baidu.com/s?id1786910666566008458wfrspiderforpc
准备工作
mysql -uroot -p #以管理员身份登录mysql select user(); #查看当前登录账户登录方式
----------------
| user() |
----------------
| rootlocalhost |
----------------
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 defaultunknown ,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 要求数据大于0check(数据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_amount0));
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 wages4000 and wages8000;
--------
| 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_id102 or department_id103) 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 id1005; #单条删除
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)
