文章目录
- 描述
- 结果
- ∗∗truncate语句,重置primary−key∗∗
- 二、单表查询
- sql脚本:
- 07、列出emp表中的所有员工,显示所有列
- 08、列出emp表中的所有的男员工,显示姓名、性别
- 09、列出emp表中的'培优部'的所有员工,显示部门名称, 员工姓名
- 10、列出emp表中员工的奖金(bonus),仅显示奖金,并剔除重复的值
- 11、列出emp表中所有奖金高于500的员工,显示姓名、奖金、职位
- 12、列出emp表中薪资(sal)在1000~2000之间的所有员工,显示姓名,薪资
- 13、列出emp表中奖金为300、500、700的所有员工,显示姓名、奖金
- 14、问答题:什么是 数据库服务器、数据库、表、表记录?
- 15、问答题:char和varchar的区别?
- 16、问答题:什么是主键约束(特点)、什么是唯一约束、什么是非空约束?
- 17、列出emp表中姓名以'王'开头的员工,显示员工姓名
- 18、列出emp表中姓名以'涛'结尾的员工,显示员工姓名
- 19、统计emp表中的所有的男员工的人数。
- 20、统计每个职位的人数, 显示职位和对应人数
- 21、统计emp表中所有员工的总薪资(包含奖金)
- 22、统计emp表中所有员工奖金的平均值
- 23、列出所有员工的入职(出生?)日期,由新到旧排列员工信息,显示姓名、总薪资
- 24、若把hdate看作员工的出生日期,查询下个月过生日的所有员工,显示员工姓名和出生日期
- 25、求1987年入职的员工信息。
- 26、求emp表中薪资最高的前3名员工的信息,显示姓名和薪资 - ∗∗看下∗∗
- 三、子查询、多表查询
- 27、(子查询)列出emp表中高于平均工资的所有员工,显示姓名、薪资
- 28、(子查询)查询emp表中比'齐雷'薪资高的所有员工,显示姓名、薪资
- 29、(子查询)查询emp表中和'齐雷'从事相同职位的所有员工,显示姓名、职位
- 30、(子查询)查询emp表中'陈子枢'所有下属员工,假设不知道陈子枢的编号(1011)
- 31、(左外连接)列出所有员工和员工对应的部门,如果员工没有对应的部门, 显示为null
- 32、(关联查询)列出在'就业部'任职的员工,假定不知道'就业部'的部门编号,显示部门名称和员工姓名
- 33、(自连接查询)列出上级及上级对应的下属员工,显示上级编号(id),上级姓名、员工姓名、上级编号(topid)
- 34、(分组、聚合函数)列出最低薪资大于1500的各种职位及从事此职位的员工人数。
- 二、单表查询
描述
----------------------------------
一、建库、建表,插入记录、修改记录、删除记录
----------------------------------
01、删除mydb2库(如果存在)
02、再次创建mydb2库,指定编码为utf8,并选择mydb2库
03、在mydb2库中,创建员工(emp)表,并添加如下列:
编号(整型、主键、自增)
姓名、性别(字符串类型)
出生年月(日期类型)
职位(字符串类型)
薪资、奖金(小数类型)
04、往员工(emp)表中插入3条记录
05、将员工的薪资在原有基础上增加1500(mysql不支持+=)
06、删除emp表中所有员工的记录
----------------------------------
二、单表查询
----------------------------------
!!下列练习使用db10库中数据,选择db10库,如果没有先创建(参考sql脚本)
07、列出emp表中的所有员工,显示所有列
08、列出emp表中的所有的男员工,显示姓名、性别
09、列出emp表中的'培优部'的所有员工,显示部门名称, 员工姓名
10、列出emp表中员工的奖金(bonus),仅显示奖金,并剔除重复的值
11、列出emp表中所有奖金高于500的员工,显示姓名、奖金、职位
12、列出emp表中薪资在1000~2000之间的所有员工,显示姓名,薪资
/* between...and... 在...之间*/
13、列出emp表中奖金为300、500、700的所有员工,显示姓名、奖金
14、问答题:什么是 数据库服务器、数据库、表、表记录?
15、问答题:char和varchar的区别?
16、问答题:什么是主键约束(特点)、什么是唯一约束、什么是非空约束?
17、列出emp表中姓名以'王'开头的员工,显示员工姓名
18、列出emp表中姓名以'涛'结尾的员工,显示员工姓名
19、统计emp表中的所有的男员工的人数。
20、统计每个职位的人数, 显示职位和对应人数
21、统计emp表中所有员工的总薪资(包含奖金)
22、统计emp表中所有员工奖金的平均值
!!下列练习使用db10库中数据,选择db10库,如果没有先创建(参考sql脚本)
23、列出所有员工的入职日期,由新到旧排列员工信息,显示姓名、总薪资
24、若把hdate看作员工的出生日期,查询下个月过生日的所有员工,显示员工姓名和出生日期
25、求1987年入职的员工信息。
26、求emp表中薪资最高的前3名员工的信息,显示姓名和薪资
----------------------------------
三、子查询、多表查询
----------------------------------
27、(子查询)列出emp表中高于平均工资的所有员工,显示姓名、薪资
28、(子查询)查询emp表中比'齐雷'薪资高的所有员工,显示姓名、薪资
29、(子查询)查询emp表中和'齐雷'从事相同职位的所有员工,显示姓名、职位
30、(子查询)查询emp表中'陈子枢'所有下属员工,假设不知道陈子枢的编号(1011)
31、(左外连接)列出所有员工和员工对应的部门,如果员工没有对应的部门, 显示为null
32、(关联查询)列出在'就业部'任职的员工,假定不知道'就业部'的部门编号,显示部门名称和员工姓名
----------------------------------------------
33、(自连接查询)列出上级及上级对应的下属员工,显示上级编号(id),上级姓名、员工姓名、上级编号(topid)
34、(分组、聚合函数)列出最低薪资大于1500的各种职位及从事此职位的员工人数。
-- 先查询出各种职位的最低薪资
-- 提示:对分组后的记录筛选过滤请使用having替换where,并且having书写在最后
-- 再查询出最低薪资>1500的职位
-- 最后查询出每个职位对应的员工人数
结果
零、登录mysql服务器,定义字符类型
mysql -uroot -proot --default-character-set=gbk
一、建库、建表,插入记录、修改记录、删除记录
01、删除mydb2库 (如果存在)
CREATE DATABASE IF NOT EXISTS mydb2 CHARSET utf8 ;
SHOW DATABASES ;
DROP DATABASE IF EXISTS mydb2 ; /* 练习 */
SHOW DATABASES ;
02、再次创建mydb2库,指定编码为utf8,并选择mydb2库
CREATE DATABASE IF NOT EXISTS mydb2 CHARSET utf8; /* 答案 */
USE mydb2 ; /* 答案 */
SELECT database();
03、在mydb2库中,创建员工(emp)表,并添加如下列:
编号(整型、主键、自增)
姓名、性别(字符串类型)
出生年月(日期类型)
职位(字符串类型)
薪资、奖金(小数类型)
CREATE DATABASE IF NOT EXISTS mydb2 CHARSET utf8;
USE mydb2 ;
SELECT database();
SHOW TABLES;
CREATE TABLE emp ( /* 答案 */
id INT PRIMARY KEY AUTO_INCREMENT,
name varchar(50),
gender char(1),
birthday DATE,
job varchar(50),
salary DOUBLE,
bonus DOUBLE
);
SHOW CREATE TABLE emp;
04、往员工(emp)表中插入3条记录
drop table if exists emp;
CREATE TABLE emp (
id INT PRIMARY KEY AUTO_INCREMENT,
name varchar(50),
gender char(1),
birthday DATE,
job varchar(50),
salary DOUBLE,
bonus DOUBLE
);
SELECT * FROM emp;
INSERT INTO emp VALUES( /* 答案 */
10, '员工 1', '男' , '1996-10-22' , '技术捡漏员', 9.9 , null
),(
null, '员工 2', '女' , '2009-12-31' , '吃瓜群众', null ,null
),(
22, '员工 3', '男' , '1111-11-11' , '单身狗', 1.1 , 0.011
) ;
SELECT * FROM emp;
05、将员工的薪资在原有基础上增加1500(mysql不支持+=)
SELECT * FROM emp;
UPDATE emp SET salary = ifnull(salary,0)+1500 ; /* 答案 */
SELECT * FROM emp;
06、删除emp表中所有员工的记录
/* 第一种删除:不重置id */
SELECT * FROM emp;
DELETE FROM emp ;
SELECT * FROM emp;
/* 第二种删除:重置id */
SELECT * FROM emp;
truncate table emp ; /* 答案 */
INSERT INTO emp VALUES(
null, '员工 1', '男' , '1996-10-22' , '技术捡漏员', 9.9 , null
),(
null, '员工 2', '女' , '2009-12-31' , '吃瓜群众', null ,null
),(
null, '员工 3', '男' , '1111-11-11' , '单身狗', 1.1 , 0.011
) ;
SELECT * FROM emp;
∗∗truncate语句,重置primary−key∗∗
二、单表查询
<mark>!!下列练习使用db10库中数据,选择db10库,如果没有先创建(参考sql脚本)</mark>
sql脚本:
(复制,在mysql界面粘贴)
-- -----------------------------------
-- 创建db10库、emp表并插入记录
-- -----------------------------------
-- 删除db10库(如果存在)
drop database if exists db10;
-- 重新创建db10库
create database db10 charset utf8;
-- 选择db10库
use db10;
-- 删除员工表(如果存在)
drop table if exists emp;
-- 创建员工表
create table emp(
id int primary key auto_increment, -- 员工编号
name varchar(50), -- 员工姓名
gender char(1), -- 员工性别
birthday date, -- 员工生日
dept varchar(50), -- 所属部门
job varchar(50), -- 所任职位
sal double, -- 薪资
bonus double -- 奖金
);
-- 往员工表中插入记录
insert into emp values(null,'王海涛','男','1995-10-25','培优部','金牌讲师','1800','300');
insert into emp values(null,'齐雷','男','1994-11-6','培优部','金牌讲师','2500','600');
insert into emp values(null,'刘沛霞','女','1996-09-14','培优部','金牌讲师','1400','300');
insert into emp values(null,'陈子枢','男','1991-05-18','培优部','部门总监','4200','500');
insert into emp values(null,'刘昱江','男','1993-11-18','培优部','金牌讲师','1600','500');
insert into emp values(null,'王克晶','女','1998-07-18','就业部','金牌讲师','3700','600');
insert into emp values(null,'苍老师','男','1995-08-18','就业部','部门总监','4850','400');
insert into emp values(null,'范传奇','男','1999-09-18','就业部','金牌讲师','3200','600');
insert into emp values(null,'刘涛','男','1990-10-18','就业部','金牌讲师','2700','400');
insert into emp values(null,'韩少云','男','1980-12-18',null,'CEO','5000',null);
-- -----------------------------------
-- 创建db20库、dept表、emp表并插入记录
-- -----------------------------------
-- 删除db20库(如果存在)
drop database if exists db20;
-- 重新创建db20库
create database db20 charset utf8;
-- 选择db20库
use db20;
-- 删除部门表, 如果存在
drop table if exists dept;
-- 重新创建部门表, 要求id, name字段
create table dept(
id int primary key auto_increment, -- 部门编号
name varchar(20) -- 部门名称
);
-- 往部门表中插入记录
insert into dept values(null, '财务部');
insert into dept values(null, '人事部');
insert into dept values(null, '科技部');
insert into dept values(null, '销售部');
-- 删除员工表, 如果存在
drop table if exists emp;
-- 创建员工表, 要求id, name, dept_id
create table emp(
id int primary key auto_increment, -- 员工编号
name varchar(20), -- 员工姓名
dept_id int -- 部门编号
-- ,foreign key(dept_id) references dept(id)
);
insert into emp values(null, '张三', 1);
insert into emp values(null, '李四', 2);
insert into emp values(null, '老王', 3);
insert into emp values(null, '赵六', 4);
insert into emp values(null, '刘能', 4);
-- -----------------------------------
-- 创建db30库、dept表、emp表并插入记录
-- -----------------------------------
-- 删除db30库(如果存在)
drop database if exists db30;
-- 重新创建db30库
create database db30 charset utf8;
-- 选择db30库
use db30;
-- 删除部门表, 如果存在
drop table if exists dept;
-- 重新创建部门表, 要求id, name字段
create table dept(
id int primary key auto_increment, -- 部门编号
name varchar(20) -- 部门名称
);
-- 往部门表中插入记录
insert into dept values(null, '财务部');
insert into dept values(null, '人事部');
insert into dept values(null, '科技部');
insert into dept values(null, '销售部');
-- 删除员工表, 如果存在
drop table if exists emp;
-- 创建员工表(员工编号、员工姓名、所在部门编号)
create table emp(
id int primary key auto_increment, -- 员工编号
name varchar(20), -- 员工姓名
dept_id int -- 部门编号
);
-- 往员工表中插入记录
insert into emp values(null, '张三', 1);
insert into emp values(null, '李四', 2);
insert into emp values(null, '老王', 3);
insert into emp values(null, '赵六', 5);
-- -----------------------------------
-- 创建db40库、dept表、emp表并插入记录
-- -----------------------------------
-- 删除db40库(如果存在)
drop database if exists db40;
-- 重新创建db40库
create database db40 charset utf8;
-- 选择db40库
use db40;
-- 创建部门表
create table dept( -- 创建部门表
id int primary key, -- 部门编号
name varchar(50), -- 部门名称
loc varchar(50) -- 部门位置
);
-- 创建员工表
create table emp( -- 创建员工表
id int primary key, -- 员工编号
name varchar(50), -- 员工姓名
job varchar(50), -- 职位
topid int, -- 直属上级
hdate date, -- 受雇日期
sal int, -- 薪资
bonus int, -- 奖金
dept_id int, -- 所在部门编号
foreign key(dept_id) references dept(id)
);
-- 往部门表中插入记录
insert into dept values ('10', '培优部', '北京');
insert into dept values ('20', '就业部', '上海');
insert into dept values ('30', '大数据部', '广州');
insert into dept values ('40', '销售部', '深圳');
-- 往员工表中插入记录
insert into emp values ('1001', '王克晶', '办事员', '1007', '1980-12-17', '800', 500, '20');
insert into emp values ('1003', '齐雷', '分析员', '1011', '1981-02-20', '1900', '300', '10');
insert into emp values ('1005', '王海涛', '推销员', '1011', '1981-02-22', '2450', '600', '10');
insert into emp values ('1007', '刘苍松', '经理', '1017', '1981-04-02', '3675', 700, '20');
insert into emp values ('1009', '张慎政', '推销员', '1011', '1981-09-28', '1250', '1400', '10');
insert into emp values ('1011', '陈子枢', '经理', '1017', '1981-05-01', '3450', 400, '10');
insert into emp values ('1013', '张久军', '办事员', '1011', '1981-06-09', '1250', 800, '10');
insert into emp values ('1015', '程祖红', '分析员', '1007', '1987-04-19', '3000', 1000, '20');
insert into emp values ('1017', '韩少云', '董事长', null, '1981-11-17', '5000', null, null);
insert into emp values ('1019', '刘沛霞', '推销员', '1011', '1981-09-08', '1500', 500, '10');
insert into emp values ('1021', '范传奇', '办事员', '1007', '1987-05-23', '1100', 1000, '20');
insert into emp values ('1023', '赵栋', '经理', '1017', '1981-12-03', '950', null, '30');
insert into emp values ('1025', '朴乾', '分析员', '1023', '1981-12-03', '3000', 600, '30');
insert into emp values ('1027', '叶尚青', '办事员', '1023', '1982-01-23', '1300', 400, '30');
-- ------------------- 执行完毕 -----------------------
07、列出emp表中的所有员工,显示所有列
USE db10 ;
SHOW TABLES ;
SELECT name AS '员工' FROM emp ; /* 答案 */
08、列出emp表中的所有的男员工,显示姓名、性别
USE db10 ;
SELECT name AS 姓名 , gender AS 性别 from emp
where gender='男' ;
09、列出emp表中的’培优部’的所有员工,显示部门名称, 员工姓名
USE db10 ;
SELECT dept as '部门名称' , name as '员工姓名' from emp
where dept='培优部';
10、列出emp表中员工的奖金(bonus),仅显示奖金,并剔除重复的值
USE db10 ;
SELECT DISTINCT bonus as '奖金' from emp ;
11、列出emp表中所有奖金高于500的员工,显示姓名、奖金、职位
use db10 ;
select name as '姓名' , bonus as '奖金' from emp ;
select name as '姓名' , bonus as '奖金' , job as '职位' from emp /* 答案 */
where bonus>500 ;
12、列出emp表中薪资(sal)在1000~2000之间的所有员工,显示姓名,薪资
/* between...and... 在...之间*/
use db10 ;
select name as '姓名' , sal as '薪资' from emp ;
select name as '姓名' , sal as '薪资' from emp /* 答案1 */
where ifnull(sal,0) between 1000 and 2000 ;
select name as '姓名' , sal as '薪资' from emp /* 答案2 */
where ifnull(sal,0) >1000 and ifnull(sal,0)< 2000 ;
13、列出emp表中奖金为300、500、700的所有员工,显示姓名、奖金
use db10 ;
select name as '姓名' , bonus as '奖金' from emp ;
select name as '姓名' , bonus as '奖金' from emp /* 答案1 */
where bonus in ( 300 , 500 , 700 );
select name as '姓名' , bonus as '奖金' from emp /* 答案2 */
where bonus=300 or bonus=500 or bonus=700;
select name as '姓名' , bonus as '奖金' from emp /* 错误答案 - */
where bonus = (300 or 500 or 700); -- 这样是查不到的
14、问答题:什么是 数据库服务器、数据库、表、表记录?
-
<mark>数据库服务器:</mark>
我的答案:管理数据库的东西。 - - 对外提供数据库管理的接口 -
数据库
我的答案:管理表的东西。 -
表
我的答案:管理数据的东西 -
表记录
我的答案:数据
书上的答案:
1、什么是数据库服务器
服务器软件,mysql 软件,将服务器软件装在电脑上,就可以作为一台服务器对外提供服务器。(存取数据)
2、什么是数据库
在每一个数据库服务器中,可以有很多个仓库(数据库),通常情况下,一个网站中的所有数据会存放在一个数据库中。
3、什么是表
一个数据库中可以创建多张表,而一张表用于存放一类信息。
(Java中的类对应数据库中的表)
4、什么表记录
一张表中可以包含多条表记录,一个表记录用于存放某一条具体的信息
15、问答题:char和varchar的区别?
- char :
长度(空间)固定,把多余的空间用“空格”填满,
效率高,占空间“大” - varchar :
长度不固定,根据传入数据调整空间。
效率低,占空间小
16、问答题:什么是主键约束(特点)、什么是唯一约束、什么是非空约束?
-
主键约束:
唯一、非空
通常:id
关键字:primary key -
唯一约束:
唯一、非非空
通常:username
关键字:unique -
非空约束:
非唯一、非空
通常: username、password
关键字:not null
17、列出emp表中姓名以’王’开头的员工,显示员工姓名
use db10 ;
select name as "员工姓名" from emp ;
select name as "员工姓名" from emp
where name like '王%';
18、列出emp表中姓名以’涛’结尾的员工,显示员工姓名
use db10 ;
select name as '员工姓名' from emp ;
select name as '员工姓名' from emp
where name like "%涛" ;
19、统计emp表中的所有的男员工的人数。
use db10 ;
select * from emp ;
select count(*) as '男员工的人数' from emp
where gender='男';
20、统计每个职位的人数, 显示职位和对应人数
select job, count(*) as 人数 from emp
group by job;
21、统计emp表中所有员工的总薪资(包含奖金)
select * from emp ;
select sum(ifnull(sal,0)+ifnull(bonus,0)) as 全部员工总薪资 from emp ;
22、统计emp表中所有员工奖金的平均值
<mark>!!下列练习使用db10库中数据,选择db10库,如果没有先创建(参考sql脚本)</mark>
use db10 ;
select database();
show tables ;
select * from emp ;
select avg(ifnull(bonus,0)) as 奖金平均值 from emp ;
23、列出所有员工的入职(出生?)日期,由新到旧排列员工信息,显示姓名、总薪资
select name , ifnull(sal,0)+ifnull(bonus,0) as '总薪资' , birthday from emp
order by birthday desc ;
24、若把hdate看作员工的出生日期,查询下个月过生日的所有员工,显示员工姓名和出生日期
(到这里发现,用的db40,就下面开始用 db40)
use db40 ;
show tables ;
select * from emp ;
select emp.name , emp.hdate
from emp
where month(hdate) = mod(month(now()),12)+1 ;
)
25、求1987年入职的员工信息。
select * from emp
where year(hdate)=1987;
26、求emp表中薪资最高的前3名员工的信息,显示姓名和薪资 - ∗∗看下∗∗
select * from emp ;
select name , sal from emp
order by sal desc
limit 0 , 3 ;
三、子查询、多表查询
27、(子查询)列出emp表中高于平均工资的所有员工,显示姓名、薪资
select * from emp ;
select avg(ifnull(sal,0)) from emp ;
select name , sal from emp , (select avg(ifnull(sal,0)) avg_sal from emp ) e
where sal >e.avg_sal;
28、(子查询)查询emp表中比’齐雷’薪资高的所有员工,显示姓名、薪资
select * from emp ;
select sal from emp where name='齐雷';
select emp.name , emp.sal from emp , (select sal from emp where name='齐雷') e
where emp.sal>e.sal ;
29、(子查询)查询emp表中和’齐雷’从事相同职位的所有员工,显示姓名、职位
select name , emp.job from emp , (select job from emp where name='齐雷') e
where emp.job=e.job;
30、(子查询)查询emp表中’陈子枢’所有下属员工,假设不知道陈子枢的编号(1011)
select * from emp ;
select emp.name,emp.id, emp.topid , e.name from emp , (select id,name from emp where name='陈子枢') e
where emp.topid= e.id;
31、(左外连接)列出所有员工和员工对应的部门,如果员工没有对应的部门, 显示为null
select emp.name , dept.name from emp left join dept
on emp.dept_id = dept.id ;
32、(关联查询)列出在’就业部’任职的员工,假定不知道’就业部’的部门编号,显示部门名称和员工姓名
select emp.name , dept.id, dept.name from emp , dept
where emp.dept_id = dept.id and dept.name='就业部';
33、(自连接查询)列出上级及上级对应的下属员工,显示上级编号(id),上级姓名、员工姓名、上级编号(topid)
select e2.name , e2.id , e1.name , e1.topid
from emp e1 , emp e2
where e1.topid=e2.id
order by e2.name ;
34、(分组、聚合函数)列出最低薪资大于1500的各种职位及从事此职位的员工人数。
-- 先查询出各种职位的最低薪资
-- 提示:对分组后的记录筛选过滤请使用having替换where,并且having书写在最后
-- 再查询出最低薪资>1500的职位
-- 最后查询出每个职位对应的员工人数
select * from emp order by job ;
select job , count(*) from emp
group by job
having min(sal)>1500;