# Practice in class 4-3

你可以比较两个查询,在MySQL的Sakila示例数据库中分析不同查询的差异

  1. 导入数据库
* 下载Sakila数据库:https://dev.mysql.com/doc/index-other.html
* 解压 
$ unzip sakila-db.zip 
* 导入数据库
$ mysql < sakila-schema.sql 
$ mysql < sakila-data.sql

如果数据库有密码,使用以下命令
$ sudo mysql -p < sakila-schema.sql
$ sudo mysql -p < sakila-data.sql
导入成功

去数据库查看,是否导入成功

使用EXPLAIN命令查询执行计划和执行时间,分析一下性能差异的原因

EXPLAIN SELECT DISTINCT film.film_id FROM sakila.film
INNER JOIN sakila.film_actor USING(film_id);
EXPLAIN SELECT film_id FROM sakila.film WHERE EXISTS(
SELECT * FROM sakila.film_actor
WHERE film.film_id = film_actor.film_id);

截屏2021-04-13 上午11.21.44

截屏2021-04-13 上午11.22.01

截屏2021-04-13 上午11.24.28

第二个用了高层DISTINCT,并且产生了临时表

# Practice in class 4-4

• Orders(custid,ordered,totalitems)

• 需要显示每一个客户购物件数最多的日期,如何用连接改写这个SQL的子查询

Select custid, ordered, totalitems From orders o1
 Where o1.ordered = (
    select max(ordered)
     from orders o2
     where o1.custid = o2.custid )
select o1.custid, o1.ordered, o1.totalitems
from orders o1 , ( select custid, max(ordered) from orders) as o2
where o1.custid = o2.custid 
	and o1.ordered = o2.ordered

# Practice in class 4-5

• 把行数据变成以某种符号分割符的列表,比如逗号

截屏2021-04-13 下午12.05.59

 SELECT deptno,group_concat(emps separator ',') FROM dept group by deptno;

# Practice in class 4-6

• 2.5是进行累加,你看看怎么做累计乘法,和累计减法。

累乘

select e.empno,e.ename,e.sal,
       ( select round(exp(sum(ln(d.sal)))) 
            from emp d
         where d.empno <= e.empno) as running_prod
  from emp e

累减

select ename,sal,
       sum(case when rn = 1 then sal else -sal end)
       over(order by sal,empno) as running_diff
from emp
order by 2

# Practice in class 4-7

• SQL题目

  • 1)找出当前季度的第一个星期天和最后一个星期三

  • 2)计算两个日期差几个月,几年,比如17-dec-2017,和12-JAN-2020,不能直接 2020-2017,因为实际他们只差了25个月,两年多一点点
select datediff(month,date1,date2),
       datediff(month,date1,date2)/12
  • 3)找到同月同日的人
select t1.name, t2.name
from t1, t2
where MONTH(t1.date) = MONTH(t2.date)
AND DAY(t1.date) = DAY(t2.date)
  • 2,3两题,日期都是从数据库不同记录中读出的,是一个通用的SQL,比如这组例子, 2是员工入职的最大值和最小值之间差多少,3比如找到入职的同月和同日的人

# Practice in class 4-8

• 接4.6,修改了一个条件,不是所有员工都有奖金

• 请计算出部门编号为10的员工的工资总额和奖金总额

截屏2021-04-13 下午12.23.28

select e.empno, e.ename,
	e.sal,e.deptno,
	e.sal* case when eb.type = 1 then .1
		when eb.type = 2 then .2
		else .3 end as bonus
from emp e, emp_bonus eb 
where e.empno = eb.empno
and e.deptno = 10
select deptno, sum(sal) as total_sal, sum(bonus) as total_bonus
from (
    select e.empno, e.ename, e.sal, e.deptno,
    	e.sal*case when eb.type is null then 0
    	when eb.type = 1 then .1
    	when eb.type = 2 then .2
    	else .3 
    	end as bonus 
    from emp e, (
        		select empno, type
        		from emp left join emp_bonus
        		on emp.empno = emp_bonus.empno
    				) as eb
    where e.empno = eb.empno and e.deptno = 10
    )
group by deptno

# Practice in class 5-1

• 程序开发中,没有银弹,任何技术都能解决一个问题,而同时带来新的问题, 你能不能在你现在正在学习和使用的一些工具和技术中,总结一些他们能解决的问题,以及带来的新的问题是什么?期待你的留言。

Last Updated: 4/13/2021, 6:40:20 AM