数据库操作练习2

1.查找员工编号emp_now为10001其自入职以来的薪水salary涨幅值growth

题目描述

查找员工编号emp_now为10001其自入职以来的薪水salary涨幅值growth
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

输入描述:

无

输出描述:

growth28841

sql1:

     select max(salary)-min(salary) as growth from salaries where emp_no=10001;

sql2:

    select ((select salary from salaries where emp_no=10001 order by to_date desc limit 1)-
        (select salary from salaries where emp_no=10001 order by to_date asc limit 1)) as growth

2.查找employees表所有emp_no为奇数

题目描述

查找employees表所有emp_no为奇数，且last_name不为Mary的员工信息，并按照hire_date逆序排列
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));

输入描述:

无

输出描述:

emp_nobirth_datefirst_namelast_namegenderhire_date100111953-11-07MarySluisF1990-01-22100051955-01-21KyoichiMaliniakM1989-09-12100071957-05-23TzvetanZielinskiF1989-02-10100031959-12-03PartoBamfordM1986-08-28100011953-09-02GeorgiFacelloM1986-06-26100091952-04-19SumantPeacF1985-02-18

sql1:

    select * from employees where (emp_no%2==1) and last_name<> 'Mary' order by hire_date desc;

3. 找出所有员工当前具体的薪水salary情况

题目描述

找出所有员工当前(to_date='9999-01-01')具体的薪水salary情况，对于相同的薪水只显示一次,并按照逆序显示
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

输入描述:

无

输出描述:

salary946929440988958880707405772527597554331125828

sql1:

    select distinct salary from salaries where to_date='9999-01-01' order by salary desc

sql2:

    1. select salary from salaries  where to_date='9999-01-01' group by salary order by salary desc;
    2. SELECT DISTINCT salary FROM salaries WHERE to_date = '9999-01-01' ORDER BY salary DESC
对于distinct,groupby的性能。
数据量非常巨大时候，比如1000万中有300W重复数据，这时候的distinct的效率略好于group by；
对于相对重复量较小的数据量比如1000万中1万的重复量，用groupby的性能会远优于distnct。
简书上的一篇博客说的不错，大家可以穿送过去看一看传送门，mysql获取当前时间 : now()
sqlServer获取当前时间: getDate()(sql2参考牛客网友回答)