Mysql数据库练习

 

我自己建的库名company

一、 建立部门表 “dept”: 

CREATE TABLE `company`.`dept`(  
  `dno` INT NOT NULL,
  `dname` VARCHAR(10) NOT NULL,
  PRIMARY KEY (`dno`)
);

插入部门表数据

INSERT INTO dept VALUES(1,"财务处")
INSERT INTO dept VALUES(2,"人事处")
INSERT INTO dept VALUES(3,"市场部")

二、建立职工表
CREATE TABLE company.emp(  
  eno INT PRIMARY KEY,
  ename CHAR(10) NOT NULL,
  sex  ENUM("男","女"),
  birth DATE,
  dy  ENUM('是','否'),
  rztime DATE,    
  depart INT,
  address CHAR(10),
  FOREIGN KEY(depart) REFERENCES dept(dno)
);

插入职工表数据

INSERT INTO emp VALUES(1,"孙华","男","1952-1-3","是","1970-10-10",1,"天津市");
INSERT INTO emp VALUES(2,"陈明","男","1945-5-8","否","1965-01-01",2,"北京市");
INSERT INTO emp VALUES(3,"程西","女","1980-6-10","否","2002-07-10",1,"北京市");
INSERT INTO emp VALUES(4,"苏天气","女","1965-3-10","是","1987-07-10",2,"天津市");
INSERT INTO emp VALUES(5,"刘少文","女","1942-1-11","否","1960-08-10",2,"");
INSERT INTO emp VALUES(6,"刘新","男","1952-10-8","否","1970-07-01",1,"上海市");
INSERT INTO emp VALUES(7,"余辉","女","1980-12-4","否","2001-07-10",3,"武汉市");
INSERT INTO emp VALUES(8,"王晓艳","女","1980-11-10","是","2001-07-10",2,"河北省");
INSERT INTO emp VALUES(9,"李玉",“女","1970-10-10","是","1989-07-10",1,"天津市");
INSERT INTO emp VALUES(10,"周涛","男","1963-2-10","否","1983-07-20",3,"河北市");
INSERT INTO emp VALUES(11,"欧阳少兵","男","1965-4-19","否","1984-07-12",2,"北京市");
INSERT INTO emp VALUES(12,"张旗","男","1980-11-10","否","1999-09-18",3,"天津市");
INSERT INTO emp VALUES(13,"李涵","女","1982-5-10","否","2002-02-14",3,"天津市");
INSERT INTO emp VALUES(14,"魏君","女","1977-3-23","是","1993-07-10",3,"");
INSERT INTO emp VALUES(15,"李艺","男","1979-9-18","否","2001-07-20",3,"上海市");

三、建立工资表

CREATE TABLE `company`.`sal`(  
  `eno` INT,
  `month` INT CHECK(`month`>=1 AND `month`<=12),
  `wages` INT,
  `allowance` INT,
  `suply` INT,
  `award` INT,
  `deduct` INT,
  `tax` INT,
  FOREIGN KEY(eno) REFERENCES emp(eno)
);

插入工资表数据

INSERT INTO sal VALUES(1,8,2800,1200,180,200,0,220);
INSERT INTO sal VALUES(2,8,2600,1000,200,300,0,180);
INSERT INTO sal VALUES(3,8,1880,880,200,260,50,120);
INSERT INTO sal VALUES(4,8,2200,980,180,230,80,110);
INSERT INTO sal VALUES(5,8,2300,880,210,0,0,125);
INSERT INTO sal VALUES(6,8,2100,600,220,180,50,110);
INSERT INTO sal VALUES(7,8,2200,800,180,200,120,120);
INSERT INTO sal VALUES(8,8,2000,780,120,100,0,100);
INSERT INTO sal VALUES(9,8,2500,870,130,150,0,120);
INSERT INTO sal VALUES(10,8,2350,700,250,180,50,120);
INSERT INTO sal VALUES(11,8,1900,800,260,130,0,100);
INSERT INTO sal VALUES(12,8,1800,500,200,100,50,100);
INSERT INTO sal VALUES(13,8,1580,500,200,100,0,100);
INSERT INTO sal VALUES(14,8,1300,450,200,100,0,100);
INSERT INTO sal VALUES(15,8,1380,450,200,100,0,10);

 

四、建表完成后，可以做如下练习了

a) 查询所有职工的基本信息 
SELECT * FROM emp ;

b) 查询所有市场部职工的基本信息 
SELECT * FROM emp e  JOIN dept d ON e.depart = d.dno  WHERE d.dname = "市场部";
SELECT * FROM emp e ,dept d WHERE e.depart = d.dno AND d.dname = "市场部";

c) 查询出所有人事处职工的姓名和入职时间 
SELECT ename,rztime  FROM emp e JOIN dept d ON e.depart= d.dno WHERE d.dname ="人事处";

d) 查询出所有男职工的基本信息 
SELECT * FROM emp WHERE sex = "男";

e) 查询出所有女职工的姓名、出生年月和所在部门 
SELECT ename,birth,dname FROM emp e JOIN dept d ON e.depart = d.dno WHERE e.sex = "女";

f) 查询出所有入职时间在 2000 年以后的女职工的职工号、姓名和入职时间 
SELECT eno,ename,rztime FROM emp WHERE rztime >"2000"

h) 查询出所有姓名是两个字的员工基本信息
SELECT * FROM emp e  JOIN dept d ON e.depart = d.dno  WHERE e.ename LIKE "__";

i) 查询出所有姓李的职工的基本信息
SELECT * FROM emp e  JOIN dept d ON e.depart = d.dno  WHERE e.ename LIKE "李_";

j) 查询出财务处的所有的党员 
SELECT * FROM emp e JOIN dept d  ON e.depart = d.dno WHERE d.dname = "财务处" AND e.dy="是";

k) 查询所有的女党员
SELECT ename e FROM emp WHERE sex = "女" AND dy ="是";

l) 查询出所有出生年月在 1960 以前的员工 
SELECT ename FROM emp WHERE birth<"1960";

m) 查询出所有职工的姓名和年龄（注意：是年龄不是出生日期）
SELECT ename AS 姓名,TIMESTAMPDIFF(YEAR, birth, CURDATE()) AS 年龄 FROM emp

n) 查询出所有籍贯是北京市或天津市的职工信息（使用 OR 和 IN 分别查 询） 
SELECT * FROM emp WHERE address ="北京市" OR address = "天津市";
SELECT * FROM emp WHERE address IN("北京市","天津市");

o) 查询出所有籍贯不是天津市的职工信息（使用 NOT 和<>分别查询） 
SELECT * FROM emp WHERE address <>"天津市";
SELECT * FROM emp WHERE NOT address ="天津市";

p) 查询出所有籍贯不是天津市也不是北京市的员工信息（使用 AND 和 NOT IN 分别查询） 
SELECT * FROM emp WHERE address <>"天津市" AND address <>"北京市";
SELECT * FROM emp WHERE address NOT IN ("北京市","天津市");

q) 查询出职工号在 5-10 之间的员工信息（使用 AND 和 BETWEEN 分别查 询） 
SELECT * FROM emp WHERE eno >=5 AND eno <=10;
SELECT * FROM emp WHERE eno BETWEEN 5 AND 10;

r) 查询出所有职工的实际工资和职工号 
SELECT (wages+allowance+suply+award-deduct-tax) AS 实际工资,eno AS 职工号 FROM sal;

s) 查询出所有实际工资在 3000-3500 之间的职工号和税收 
SELECT eno AS 职工号,tax AS 税收,(wages+allowance+suply+award-deduct-tax) AS 实际工资 
FROM sal 
WHERE (wages+allowance+suply+award-deduct-tax) 
BETWEEN 3000 AND 3500

t) 查询出所有籍贯为空的职工的基本信息 
SELECT * FROM emp WHERE address ="";

u) 查询出姓李并且第二个字是玉或者艺字的职工 
SELECT * FROM emp WHERE ename LIKE '李玉%' OR ename LIKE "李艺%";

v) 查询出姓李并且第二个字不是玉或者艺的职工
SELECT * FROM emp WHERE ename LIKE "李%" AND ename NOT LIKE '李玉%' AND ename NOT LIKE "李艺%";

.针对上表，做如下操作 
a) 新添加 16 号员工的信息，其他字段值自定
INSERT INTO emp VALUES(16,"李峰晨","男","1982-02-10","否","2002-08-24",2,"陕西省")

b) 删除 1950 年以前的员工信息 
DELETE FROM emp WHERE birth<"1950";

c) 工资的调整：

i. 1-5 号职工，基本工资增加 200
UPDATE sal SET wages=wages+200 WHERE eno BETWEEN 1 AND 5;

ii. 6-10 号职工，基本工资增加百分之十，津贴增加 50 
UPDATE sal SET wages=wages*1.1,allowance = allowance+50 WHERE eno BETWEEN 6 AND 10;

iii. 11 号以上职工，基本工资增加 150，津贴增加 30，补助增加 20
UPDATE sal SET wages=wages+150,allowance = allowance+30,suply = suply+20 WHERE eno >=11;

iv. 所有职工奖金增加 80 
UPDATE sal SET award = award+80 

v. 有“扣除”项的职工，没有奖金
UPDATE sal SET award = 0 WHERE deduct <>0;

3. 高级查询 
a) 按基本工资由大到小显示所有职工的职工号和实际工资 
SELECT eno,(wages+allowance+suply+award-deduct-tax) AS 实际工资 FROM sal ORDER BY wages DESC;

b) 按实际工资由大到小显示所有职工的职工号和实际工资 
SELECT eno,(wages+allowance+suply+award-deduct-tax) AS 实际工资 
FROM sal 
ORDER BY (wages+allowance+suply+award-deduct-tax) DESC;

c) 按部门升序显示职工的职工号和实际工资 
SELECT emp.`eno`,emp.`depart`,(wages+allowance+suply+award-deduct-tax) AS 实际工资 
FROM sal JOIN emp ON sal.eno=emp.eno ORDER BY depart ASC;

d) 求出所有职工的基本工资的和 
SELECT SUM(wages) AS 基本工资总和 FROM sal;

e) 求出所有职工的实际工资的和 
SELECT SUM((wages+allowance+suply+award-deduct-tax) )AS 实际工资总和 FROM sal;

f) 求出 5-10 号没有被扣除工资的员工的基本工资和 
SELECT SUM(wages) AS 基本工资总和 FROM sal WHERE eno BETWEEN 5 AND 10;

g) 求出所有职工基本工资的平均工资 
SELECT AVG(wages) AS 平均工资 FROM sal

h) 求出所有职工实际工资的平均工资
SELECT AVG((wages+allowance+suply+award-deduct-tax)) AS 平均工资 FROM sal

i) 统计出女工的数目
SELECT COUNT(*) AS 女工数量 FROM emp WHERE sex ="女";

j) 统计出天津的女工个数 
SELECT COUNT(*) AS 女工数量 FROM emp WHERE sex ="女" AND address = "天津市";

k) 统计出本月被扣除工资的职工数和扣除的总金额
SELECT COUNT(*),SUM(deduct) FROM sal;

l) 统计出实际工资大于 3000 的职工数
SELECT COUNT(*) FROM sal WHERE (wages+allowance+suply+award-deduct-tax)>3000;

m) 统计出职工的最大工龄 
SELECT MAX(TIMESTAMPDIFF(YEAR,rztime,CURDATE())) AS 最大工龄 FROM emp;

n) 统计出女职工的最小年龄 
SELECT MIN(TIMESTAMPDIFF(YEAR,birth,CURDATE())) AS 最小女年龄 FROM emp WHERE sex = "女";

o) 统计出天津女党员的最大年龄 
SELECT MAX(TIMESTAMPDIFF(YEAR,birth,CURDATE())) AS 最大天津年龄 
FROM emp 
WHERE sex = "女" AND address = "天津市";

p) 统计出各部门最高的工龄 
SELECT MAX(TIMESTAMPDIFF(YEAR,rztime,CURDATE())) AS 最大工龄,dname AS 部门 
FROM emp JOIN  dept ON emp.`depart`=dept.`dno`
GROUP BY dname;
SELECT MAX(TIMESTAMPDIFF(YEAR,rztime,CURDATE())) AS 最大工龄,depart FROM emp GROUP BY depart;

q) 统计出最大的男工和女工的年龄 
SELECT MAX(TIMESTAMPDIFF(YEAR,rztime,CURDATE())) AS 最大工龄,sex FROM emp GROUP BY sex;

r) 统计出各部门的员工数
SELECT dname,COUNT(*) FROM emp JOIN dept ON emp.`depart`=dept.`dno` GROUP BY dname;

s) 列出各部门的女工数 
SELECT dname,COUNT(*) FROM emp JOIN dept ON emp.`depart`=dept.`dno` WHERE sex = "女" GROUP BY dname;

t) 列出各部门的男工数和女工数 
SELECT dname,COUNT(*) AS 总数,sex FROM emp JOIN dept ON emp.`depart`=dept.`dno` GROUP BY dname,sex;

8. 多表查询 
a) 显示出所有职工的姓名和部门名 
SELECT ename,dname FROM emp JOIN dept ON emp.`depart`=dept.`dno`

b) 按部门升序显示出所有职工的姓名和部门名 
SELECT ename,dname FROM emp JOIN dept ON emp.`depart`=dept.`dno` ORDER BY dno ASC;

c) 显示出所有职工的姓名、基本工资和入职时间
SELECT ename,wages,rztime FROM sal JOIN emp ON sal.`eno`=emp.eno;

d) 显示出所有职工的姓名、年龄、部门和实际工资 
SELECT ename,TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age,dname,(wages+allowance+suply+award-deduct-tax) 
AS 实际工资 FROM sal JOIN emp ON sal.eno = emp.`eno` JOIN dept ON emp.`depart`= dept.`dno`;

e) 显示出所有被扣工资的员工姓名和被扣金额 
SELECT ename,deduct AS 被扣金额 FROM sal JOIN emp ON sal.eno = emp.eno WHERE deduct <>0;

f) 按部门升序显示职工的姓名、所在部门和实际工资 
SELECT ename,dname,(wages+allowance+award+suply-deduct-tax) AS 实际工资 FROM dept JOIN emp ON dept.dno=emp.depart JOIN sal ON 
emp.eno = sal.eno ORDER BY depart ASC;

g) 按部门名升序、实际工资降序显示部门名称、职工号和实际工资
SELECT dname,eno,(wages+allowance+award+suply-deduct-tax) AS 实际工资 FROM dept JOIN emp ON dept.dno=emp.depart JOIN sal ON 
emp.eno = sal.eno ORDER BY emp.depart ASC  ORDER BY (wages+allowance+award+suply-deduct-tax) DESC;

9. 子查询 
a) 显示出工龄最大的职工姓名和工龄 
SELECT ename,TIMESTAMPDIFF(YEAR,rztime,CURDATE()) AS 工龄 FROM emp ORDER BY 工龄 DESC LIMIT 0,1;

b) 统计出所有北京籍员工的工资总和 
SELECT SUM(wages) FROM sal JOIN emp ON sal.eno = emp.eno WHERE address = "北京市"

c) 统计出所有女职工的平均工资 
SELECT AVG(wages) FROM sal JOIN emp ON sal.eno = emp.eno WHERE address = "天津市"  AND sex = "女";

d) 统计出所有天津女党员的最低工资
SELECT MIN(wages)

FROM sal JOIN emp ON sal.eno = emp.eno

WHERE address = "天津市"  AND sex = "女" AND dy="是";

e) 查询所有市场部职工的基本信息
SELECT * FROM emp JOIN dept ON emp.depart = dept.dno WHERE dname = "市场部"；

f) 显示出最小的女职工的姓名和年龄 
SELECT ename,TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS 年龄 FROM emp WHERE sex = "女" ORDER BY 年龄 ASC LIMIT 0,1;

g) 统计出实际工资最高的北京籍员工的姓名和实际工资 
SELECT ename,(wages+allowance+suply+award-deduct-tax) AS 实际工资 FROM emp JOIN sal ON emp.eno = sal.eno WHERE address = "北京市"
ORDER BY 实际工资 DESC LIMIT 0,1;

h) 显示出年龄最大的天津女党员 
SELECT ename,TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS 年龄 FROM emp WHERE  address = "天津市"  AND sex = "女" AND dy="是" 
ORDER BY 年龄 DESC LIMIT 0,1;

i) 显示出基本工资比天津市最高工资高的所有职工
SELECT ename 
FROM emp JOIN sal ON emp.eno= sal.eno  
WHERE wages > (SELECT wages FROM sal JOIN emp ON sal.eno = emp.eno WHERE address = "天津市" ORDER BY wages DESC LIMIT 0,1);

j) 显示出实际工资比总体平均工资低的女职工 
SELECT ename,(wages+allowance+suply+award-deduct-tax) AS 实际工资 FROM emp JOIN sal ON emp.eno= sal.eno  WHERE 
(wages+allowance+suply+award-deduct-tax)<(SELECT AVG(wages) FROM sal JOIN emp ON sal.eno = emp.eno );

k) 显示出人数最多的部门的所有职工信息 
SELECT * FROM emp WHERE depart = (SELECT depart FROM emp GROUP BY depart ORDER BY COUNT(*) DESC  LIMIT 0,1);

l) 显示出工资最高的职工的姓名，性别，籍贯和所在部门名称 
SELECT ename,sex,address,dname 
FROM dept JOIN emp ON dept.`dno`=emp.`depart` JOIN sal ON emp.`eno`=sal.`eno` 
ORDER BY wages DESC LIMIT 0,1;

m) 显示出比天津市入职最晚的职工入职还晚的职工 
SELECT ename FROM emp WHERE rztime > (SELECT rztime FROM emp WHERE address = "天津市" ORDER BY rztime DESC LIMIT 0,1);

n) 显示出比‘刘新’工资高的所有职工姓名和实际工资 
SELECT ename,(wages+allowance+suply+award-deduct-tax) AS 实际工资 FROM emp JOIN sal ON emp.eno = sal.`eno` WHERE wages>(
SELECT wages FROM emp JOIN sal ON emp.eno = sal.`eno` WHERE ename= "刘新");

o) 显示出奖金和‘李艺’一样多的职工姓名和所在部门名称
SELECT ename,dname FROM dept JOIN emp ON dept.dno = emp.`depart`JOIN sal ON emp.`eno`= sal.`eno` WHERE award=
(SELECT award FROM emp JOIN sal ON emp.eno = sal.`eno` WHERE ename= "李艺")

p) 给所有市场部的职工增加 200 元的基本工资 
UPDATE sal SET wages= wages+200 WHERE eno IN (SELECT eno FROM dept JOIN emp ON dept.dno = emp.depart WHERE dname ="市场部");

q) 所有非天津籍员工的补助增加 100 元作为思乡补助 
UPDATE sal SET suply= suply+100 WHERE eno NOT IN (SELECT eno FROM  emp  WHERE address ="天津市");

);