1.针对如下表actor结构创建索引:
CREATE TABLE IF NOT EXISTS actor (
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update timestamp NOT NULL DEFAULT (datetime(‘now’,’localtime’)))
对first_name创建唯一索引uniq_idx_firstname,对last_name创建普通索引idx_lastname
答案:
CREATE UNIQUE INDEX uniq_idx_firstname ON actor(first_name);
CREATE INDEX idx_lastname ON actor(last_name);
2.按照dept_no进行汇总,属于同一个部门的emp_no按照逗号进行连接,结果给出dept_no以及连接出的结果employees
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
));
输出格式:
dept_no employees
d001 10001,10002
d002 10006
d003 10005
答案:
select dept_no,group_concat
(emp_no) as employees from dept_emp group by dept_no;
group_concat函数,结合group by一起使用
3.分页查询employees表,每5行一页,返回第2页的数据
答案:
select * from employees limit 5,5;
注意:第一条开始 的索引为 0;
4.查找排除当前最大、最小salary之后的员工的平均工资avg_salary
默认当前时间是:’9999-01-01’
SELECT AVG(salary) AS avg_salary FROM salaries
WHERE to_date = '9999-01-01'
AND salary NOT IN (SELECT MAX(salary) FROM salaries WHERE to_date = '9999-01-01')
AND salary NOT IN (SELECT MIN(salary) FROM salaries WHERE to_date = '9999-01-01')
知识:not in
5.将所有获取奖金的员工当前的薪水增加10%。
create table emp_bonus(
emp_no int not null,
recevied datetime not null,
btype smallint not null);
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
));
update salaries set salary=salary*1.1 where emp_no in(select emp_no from emp_bonus);
知识:in