数据库查询操作作业

作业1

需求1：创建管家婆数据库

CREATE DATABASE gjp;

需求2：在数据库中创建两张表

表1：分类数据表，表名 gjp_sort。
字段包含：
	 sid INT PRIMARY KEY AUTO_INCREMENT, -- id列，主键约束(数据唯一性) 自动增长
	 sname VARCHAR(100) , -- 分类名称
	 parent VARCHAR(100), -- 父分类，支出，收入
     sdesc VARCHAR(10000) -- 分类描述

CREATE TABLE gjp_sort(
	sid INT PRIMARY KEY AUTO_INCREMENT,
	sname VARCHAR(100),
	parent VARCHAR(100),
	sdesc VARCHAR(10000));

表2：创建账务的数据表,表名：gjp_ledger
字段包含：
	  lid INT PRIMARY KEY AUTO_INCREMENT , -- 主键，自动增长
	  parent VARCHAR(100), -- 所属的大分类
	  money DOUBLE , -- 金额
	  sid INT , -- 分类ID
	  account VARCHAR(100), -- 账户
	  createtime DATE , -- 创建日期和时间
	  ldesc VARCHAR(1000) -- 描述

CREATE TABLE gjp_ledger(
	lid INT PRIMARY KEY AUTO_INCREMENT,
	parent VARCHAR(100),
	money DOUBLE,
	sid INT,
	account VARCHAR(100),
	createtime DATE,
	ldesc VARCHAR(1000));

需求3：向两张表中添加数据(注意 ：账务表的外键ID(从表)，对应了分类表的主键ID(主表)

写入数据的方式，推荐使用：

• 向gjp_sort表插入数据(根据示例插入10条数据)：

示例：INSERT INTO gjp_sort
(sname,parent,sdesc) VALUES (‘基金收入’,‘收入’,‘我买了点基金’)

INSERT INTO gjp_sort(sid,sname,parent,sdesc) VALUES (1,'服装支出','支出','人靠衣装'),(2,'吃饭支出','支出','天天下馆子'),(3,'交通支出','支出','每天挤地铁'),(4,'住房支出','支出','五环外公寓'),(5,'工资收入','收入','工资不够花'),(6,'股票收入','收入','股票不赚钱'),(7,'礼金支出','支出','礼金拿不起'),(8,'其它支出','支出','啥也不敢买');

• 向gjp_ledger表插入数据(根据示例插入20条数据)：
  数据可以直接复制素材中 gjp的数据

示例：INSERT INTO gjp_ledger(lid,parent,money,sid,account,createtime,ldesc)
VALUES (1,‘支出’,247,2,‘交通银行’,‘2015-03-02’,‘家庭聚餐’);

INSERT INTO 
 gjp_ledger(lid,parent,money,sid,account,createtime,ldesc) 
 VALUES (1,'支出',247,2,'交通银行','2015-03-02','家庭聚餐'),
 (2,'收入',12345,5,'现金','2015-03-15','开工资了'),
 (3,'支出',1998,1,'现金','2015-04-02','买衣服'),
 (4,'支出',325,2,'现金','2015-06-18','朋友聚餐'),
 (10,'收入',8000,6,'工商银行','2015-10-28','股票大涨'),
 (11,'收入',5000,6,'工商银行','2015-10-28','股票又大涨'),
 (12,'收入',5000,5,'交通银行','2015-10-28','又开工资了'),
 (13,'支出',5000,7,'现金','2015-10-28','朋友结婚'),
 (14,'支出',1560,8,'现金','2015-10-29','丢钱了'),
 (15,'支出',2300,3,'交通银行','2015-10-29','油价还在涨啊'),
 (16,'支出',1000,2,'工商银行','2015-10-29','又吃饭'),
 (17,'收入',1000,5,'现金','2015-10-30','开资'),
 (18,'支出',2000,3,'现金','2015-10-30','机票好贵'),
 (19,'收入',5000,5,'现金','2015-10-30','又开资');

需求4：聚合函数的使用：

聚合函数 count() 的使用

• 4.1:统计 gjp_ledger表中共有多少条数据。
  

  提示：使用count(*)

SELECT COUNT(*) FROM gjp_ledger;

• 4.2：count 统计所有工商银行的内容，共有多少条
  

  提示：使用 LIKE + ‘%内容%’

SELECT COUNT(*) FROM gjp_ledger WHERE account LIKE '%工商银行%';

• 4.3：统计账务表中金额大于3000的有多少条记录
  

  提示：使用比较运算符 >

SELECT COUNT(*) FROM gjp_ledger WHERE money > 3000;

聚合函数 sum() 的使用

• 4.4: 查询账务表，求出money字段的和
  

  提示：使用sum(字段名)

SELECT SUM(money) FROM gjp_ledger;

• 4.5：查询账务表，统计所有支出的金额总和
  

  提示：查询条件为 ‘支出’

SELECT SUM(money) FROM gjp_ledger WHERE parent='支出';

聚合函数：max,min 求出最大和最小值

• 4.6：查询账务表，统计出 支出中的最大值 和 收入中的最小值

SELECT MAX(money) FROM gjp_ledger WHERE parent='支出';SELECT MIN(money) FROM gjp_ledger WHERE parent='收入';

聚合函数：avg():

• 4.7:查询账务表，计算出，所有支出的平均数
  

  提示：avg(字段) 计算这个字段的平均数

SELECT AVG(money) FROM gjp_ledger WHERE parent='支出';

排序order by:

• 4.8 :查询账务表，使用money字段，进行升序排序

SELECT * FROM gjp_ledger ORDER BY money;

• 4.9 ：查询账务表，使用createtime字段，进行降序排序

SELECT * FROM gjp_ledger ORDER BY createtime DESC;

需求5：分组 group by的使用

• 查询出，支出的共计多少钱，收入的共计多少钱
  

  提示：需要对支出和收入进行分组

SELECT SUM(money) AS 共计, parent AS 收支类型 FROM gjp_ledger GROUP BY parent;

• 查询出，所有的金额总和，先按照支出和收入分类，再按照银行的类型分。
  

  提示：需要对 支出分组，然后对银行类型分

SELECT SUM(money) AS 共计,parent AS 收支类型, account AS 银行类型 FROM gjp_ledger GROUP BY parent,account;

• 查询出，支出的共计多少钱，收入的共计多少钱， 只显示大于20000元的
  

  提示：使用 having 子句，跟随分组，分组后，对结果再次过滤

SELECT SUM(money) AS 共计,parent AS 收支类型 FROM gjp_ledger GROUP BY parent HAVING 共计>=20000;

作业2

1.返回员工信息以及员工所在的部门名称和部门地址。

SELECT e.*,d.dname,d.loc FROM emp e,dept d WHERE e.deptno = d.deptno;

2.工资水平多于smith的员工信息。

SELECT * FROM emp WHERE sal >(SELECT e.sal FROM emp e WHERE e.ename='smith');

3.返回员工和所属经理的姓名。

SELECT e.ename AS 员工,m.ename AS 经理 FROM emp e,emp m WHERE e.mgr=m.empno;

or （没有经理是否显示）

SELECT e.ename AS 员工,m.ename AS 经理 FROM emp e LEFT JOIN emp m ON e.mgr = m.empno;

4.返回雇员的雇佣日期早于其经理雇佣日期的员工及其经理姓名

SELECT e.ename AS 员工,m.ename AS 经理 FROM emp e,emp m WHERE e.mgr=m.empno AND e.hiredate<m.hiredate;

5.返回员工姓名及其所在的部门名称。

SELECT e.ename AS 员工姓名,d.dname AS 部门名称 FROM emp e,dept d WHERE e.deptno=d.deptno;

6.返回从事clerk工作的员工姓名和所在部门名称。

SELECT e.ename AS 员工姓名,d.dname AS 部门名称 FROM emp e,dept d WHERE e.deptno=d.deptno AND e.job='clerk';

7.返回部门号及其本部门的最低工资。

SELECT MIN(e.sal),e.deptno FROM emp e GROUP BY e.deptno;

8.返回销售部(sales)所有员工的姓名。

SELECT e.ename FROM emp e WHERE e.deptno=(SELECT d.deptno FROM dept d WHERE d.dname='sales');

9.返回工资水平多于平均工资的员工。

SELECT * FROM emp e WHERE e.sal>(SELECT AVG(sal) FROM emp);

10.返回与30部门员工工资水平相同的员工姓名与工资。

SELECT ee.ename,ee.sal FROM emp ee WHERE sal IN(SELECT e.sal FROM emp e WHERE e.deptno=30);

数据库学习第三天