创建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