sql练习2

delete 
from exam_record
where score is null
or timestampdiff(minute,start_time,submit_time)<5

order by start_time

limit 3

时间相减函数timestampdiff

Select a.device_id,a.university,a.gpa
from user_profile a
join (
    select university,min(gpa) as gpa 
    from user_profile
    group by university
) b
on a.university=b.university and a.gpa=b.gpa
order by a.university

 join的用法，自定义表，进行连接

select distinct user_info.uid,nick_name,achievement
from user_info,exam_record,practice_record
where nick_name like '牛客%号'
and achievement in (1200,2500)
and user_info.uid=exam_record.uid
and practice_record.uid=user_info.uid
and (month(start_time)='2021-09'
    or month(exam_record.submit_time)='2021-09'
    or month(practice_record.submit_time)='2021-09')

INSERT INTO exam_record_before_2021(uid, exam_id, start_time, submit_time, score)
SELECT uid, exam_id, start_time, submit_time, score
FROM exam_record
WHERE YEAR(submit_time) < '2021';

 插入表时可用select语句

insert into table （...）select ...from

select end.emp_no,(end.salary - start.salary) growth 
from (select emp_no,salary
      from salaries 
      where to_date = '9999-01-01') end 
      left join (select s.emp_no,salary 
                 from salaries s
                 , employees e 
                 where s.emp_no = e.emp_no
                 and s.from_date = e.hire_date) start
                 on start.emp_no = end.emp_no 
                 order by growth

 

select a.emp_no,b.emp_no manager_no,a.salary emp_salary, b.salary manager_salary
from (select dept_emp.emp_no,salaries.salary,dept_emp.dept_no
     from salaries,dept_emp
     where salaries.to_date='9999-01-01'
     and salaries.emp_no=dept_emp.emp_no) a,
     (select dept_manager.emp_no,salaries.salary,dept_manager.dept_no
     from salaries,dept_manager
     where 
     salaries.to_date='9999-01-01') b
     where b.dept_no=a.dept_no
     and b.salary<a.salary

select *
from student
where week(date_format('2020-04-21 00:00:00.000000','%Y%m%d'))=week(Sage)

date_format函数用来将字符串转化为date形式