职员
1、查找最晚入职员工的所有信息
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
));
select * from employees Order by hire_date desc limit 0,1
2、查找入职员工时间排名倒数第三的员工所有信息
select * from employees order by hire_date desc limit 2,1
3、查找各个部门当前(to_date=’9999-01-01’)领导当前薪水详情以及其对应部门编号dept _no
CREATE TABLE dept_manager
(
dept_no
char(4) NOT NULL,
emp_no
int(11) NOT NULL,
from_date
date NOT NULL,
to_date
date NOT NULL,
PRIMARY KEY (emp_no
,dept_no
));
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
));
select s.* , d.dept_no from salaries s , dept_manager d
where s.to_date='9999-01-01'
and d.to_date='9999-01-01'
and s.emp_no=d.emp_no
4、查找所有已经分配部门的员工的last _name和first _name
CREATE TABLE dept_emp
(
emp_no
int(11) NOT NULL,
dept_no
char(4) NOT NULL,
from_date
date NOT NULL,
to_date
date NOT NULL,
PRIMARY KEY (emp_no
,dept_no
));
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
));
select employees.last_name, employees.first_name,dept_emp.dept_no from employees inner join dept_emp on
employees.emp_no=dept_emp.emp_no;
5、查找所有员工的last _name和first _name以及对应部门编号dept _no,也包括展示没有分配具体部门的员工
select employees.last_name, employees.first_name, dept_emp.dept_no from employees left join dept_emp
on employees.emp_no=dept_emp.emp_no;
6、查找所有员工入职时候的薪水情况,给出emp_no以及salary, 并按照emp_no进行逆序
select employees.emp_no,salaries.salary from employees left join salaries
on employees.emp_no=salaries.emp_no and employees.hire_date=salaries.from_date
order by employees.emp_no desc;
7、查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t
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
));
SELECT emp_no,count(emp_no) as t from salaries
GROUP BY emp_no HAVING t >15;
8、找出所有员工当前(to_date=’9999-01-01’)具体的薪水salary情况,对于相同的薪水只显示一次,并按照逆序显示
注意:
1、显示一次:DISTINCT
2、当前员工:to_date=’9999-01-01’
SELECT DISTINCT salary from salaries where to_date='9999-01-01' order by salary desc ;
9、获取所有部门当前manager的当前薪水情况,给出dept_no, emp_no以及salary,当前表示to_date=’9999-01-01’
注意:
1、on:连接条件 、where:过滤
2、salaries和dept_manager顺序
SELECT d.dept_no,d.emp_no,s.salary
from salaries s JOIN dept_manager d
on d.emp_no=s.emp_no
where d.to_date='9999-01-01'
and s.to_date='9999-01-01';
10、获取所有非manager的员工emp_no
CREATE TABLE dept_manager
(
dept_no
char(4) NOT NULL,
emp_no
int(11) NOT NULL,
from_date
date NOT NULL,
to_date
date NOT NULL,
PRIMARY KEY (emp_no
,dept_no
));
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
));
SELECT emp_no
FROM employees
where emp_no NOT IN
(select emp_no from dept_manager);
SQL重复记录查询的几种方法
1、查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断
select * from people where peopleId in (select peopleId from people group by peopleId having count(peopleId)>1)
2、删除表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断,只留有rowid最小的记录
DELETE FROM people
where peopleId in (SELECT peopleId from people GROUP BY peopleId HAVING COUNT(peopleId)>1
and rowid not in (SELECT min(rowId) from people GROUP by peopleId HAVING count(peopleId)>1);
3、查找表中多余的重复记录(多个字段)
这里写代码片
count(1)与count(*)比较
1、如果你的数据表没有主键,那么count(1)比count(*)快
2、如果有主键的话,那主键(联合主键)作为count的条件也比count(*)要快
3、如果你的表只有一个字段的话那count(*)就是最快的啦
count(*) count(1) 两者比较。主要还是要count(1)所相对应的数据字段。
如果count(1)是聚索引,id,那肯定是count(1)快。但是差的很小的。
因为count(),自动会优化指定到那一个字段。所以没必要去count(?),用count(),sql会帮你完成优化的
count(*)和count(id)
count(*)会快点,它是取所有的行数,count(ID)是取除ID为NULL的所有的行数,多了个判断
COUNT(*)通常是对主键进行索引扫描,而COUNT(COL)就不一定了,另外前者是统计表中的所有符合的纪录总数,而后者是计算表中所有符合的COL的纪录数。还有有区别的