mysql查询测试题

创建employees表
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` ) 
);

插入的数据
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26'); INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21'); INSERT INTO employees VALUES(10003,'1959-12-03','Parto','Bamford','M','1986-08-28'); INSERT INTO employees VALUES(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01'); INSERT INTO employees VALUES(10005,'1955-01-21','Kyoichi','Maliniak','M','1989-09-12'); INSERT INTO employees VALUES(10006,'1953-04-20','Anneke','Preusig','F','1989-06-02'); INSERT INTO employees VALUES(10007,'1957-05-23','Tzvetan','Zielinski','F','1989-02-10');
INSERT INTO employees VALUES(10008,'1958-02-19','Saniya','Kalloufi','M','1994-09-15'); INSERT INTO employees VALUES(10009,'1952-04-19','Sumant','Peac','F','1985-02-18');

INSERT INTO employees VALUES(10010,'1963-06-01','Duangkaew','Piveteau','F','1989-08-24'); INSERT INTO employees VALUES(10011,'1953-11-07','Mary','Sluis','F','1990-01-22');

1、查询最晚入职员工的人

第一种方法
select * from employees 
order by cast(hire_date as datetime) desc
LIMIT 0, 1;

第二种方法
SELECT *FROM employees ORDER BY hire_date DESC LIMIT 1;

2、请参照上表，你查找employees里入职员工时间排名倒数第三的
员工所有信息 

第一种方法
select * from employees 
order by cast(hire_date as datetime) desc
LIMIT 2, 1;

第二种方法
SELECT *FROM employees ORDER BY hire_date DESC LIMIT 2,1;

3、找出所有员工当前薪水salary情况 
SELECT DISTINCT`salary` FROM `salaries`
GROUP BY salary DESC;
DISTINCT可以去重；有两个列相同的情况下

4、创建一个actor表，
show CREATE TABLE actor;
CREATE TABLE `actor` (
`cator_id` smallint(5) NOT NULL COMMENT '主键',
`first_name` varchar(45) NOT NULL COMMENT '名字',
`last_name` varchar(45) NOT NULL COMMENT '姓氏',
`last_update` date NOT NULL COMMENT '日期',
PRIMARY KEY (`cator_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

5、查找employees表所有emp_no为奇数，且last_name不为
Mary的员工信息，并按照hire_date逆序排列，
 

筛选奇数：
mod(id,2) 也可以直接用 id % 2来表示=1;
emp_no%2=1求奇数，emp_no%2=0求偶数，

第一种方法
SELECT *FROM employees 
WHERE mod(emp_no,2) AND first_name!='Mary'
ORDER BY cast(hire_date as datetime) DESC;

第二种方法
SELECT *FROM employees 
WHERE emp_no%2=1 and last_name !='mary'
ORDER BY hire_date DESC ;

6、在actor表中对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);

7、分页查询employees表，每5行一页，返回第2页的数据 (8分)

SELECT * FROM `employees`
LIMIT 5,5;
第一个5是偏移量，从哪里开始，
第二个5是指定显示的长度，

8、查找排除当前最大、最小salary之后的员工的平均工资
 avg_salary 

第一种方法
SELECT AVG(salary) AS 平均值 FROM salaries 
WHERE
salary!=(SELECT MAX(salary) FROM salaries) AND salary!= (SELECT MIN(salary) FROM salaries);
!= 可以换成 not IN

第二种方法
SELECT AVG(salary) AS avg_salary FROM salaries 
WHERE
salary NOT IN (SELECT MAX(salary) FROM salaries)
AND salary NOT IN (SELECT MIN(salary) FROM salaries);

9、统计salary的累计和running_total 
SELECT s1.emp_no, s1.salary, 
(SELECT SUM(s2.salary) FROM salaries AS s2 
WHERE s2.emp_no <= s1.emp_no AND s2.to_date = '9999-01-01') AS running_total 
FROM salaries AS s1 WHERE s1.to_date = '9999-01-01' ORDER BY s1.emp_no;

10、获得积分最多的人 
判断你的数据中有无 user 和grade_info表，有就删除
drop table if exists user;
drop table if exists grade_info;
创建user表
CREATE TABLE user ( 
id int(4) NOT NULL,
name varchar(32) NOT NULL );

创建grade_info表
CREATE TABLE grade_info (
user_id int(4) NOT NULL, 
grade_num int(4) NOT NULL, 
type varchar(32) NOT NULL );

在user和grade_info表插入数据
INSERT INTO user VALUES (1,'tm'), (2,'wwy'), (3,'zk'), (4,'qq'), (5,'lm');
INSERT INTO grade_info VALUES (1,3,'add'),
(2,3,'add'), (1,1,'reduce'), (3,3,'add'), (4,3,'add'), (5,3,'add'), (3,1,'reduce');

第一种方法
SELECT
tmp1.user_id,
tmp1.grade_num - ifnull(tmp2.grade_num,0) as total
FROM
( SELECT user_id, sum( grade_num ) AS grade_num FROM grade_info WHERE type = 'add' GROUP BY user_id ) AS tmp1
LEFT JOIN ( SELECT user_id, sum( grade_num ) AS grade_num FROM grade_info WHERE type = 'reduce' GROUP BY user_id ) tmp2
on tmp1.user_id = tmp2.user_id
order by total desc 

第二种方法
select tmp.user_id,sum(tmp.grade_num) grade_num from(
(select user_id,sum(grade_num) as  grade_num from grade_info
where type = 'add'
group by user_id)
union all
(select user_id,0-sum(grade_num) as grade_num from grade_info
where type = 'reduce'
group by user_id)) as tmp group by tmp.user_id
order by grade_num

第三种方法
select user_id ,sum(
   case 
        when type='add' then grade_num 
        when type='reduce' then -grade_num
   end
) as total from grade_info group by user_id

触发器
在BEGIN和END中间写入触发条件
选择之后
old原本的数据
CREATE TRIGGER AFTER DELETE ON `result` FOR EACH ROW 
BEGIN#开始位置
INSERT INTO history_result    #history_result表名
(studentNo,subjectNo,score,examDate)
VALUES(old.studentNo,old.subjectNo,old.studentResult,old.examDate);
END#结束位置

 选择之前
new新的数据
CREATE DEFINER=`root`@`localhost` TRIGGER `insert_to_history` AFTER INSERT ON `result` FOR EACH ROW BEGIN
 insert into history_result values(new.id,new.studentNo,new.subjectNo,new.score,new.exam_date,now(),'insert');
END