基础知识
- 每次对数据库里的记录产生了操作都需要(insert update delete) 都需要提交事务的
- 同时运行很多带 ;的select语句,就会同时出现很多查询窗口;
- 学习使用declare语句设置变量,参考韩啸-补上一个时间段所有的上下班卡.sql;
- 连表查询用JOIN
- 不等号<> 、!=都是可以的
基础操作
TO_DATE(TO_CHAR(sysdate,'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS');
SELECT xxmc,sname as xsxm,sex,phone,address jzdz FROM student
- 将两个表的联查结果作为临时表,与其他表进行连接查询,实行三表联查(四表,5表查询也可以这样实现)
with ab as (select name,dept,job,posi from a,b) ;
select * from ab;
with tmp as(select t1.police_num as tmpnum,count(*) as tmpcount from t_jxkh_attendance t1
where t1.day>'2020-06-31'
and t1.work_type_id not in (7)
and t1.on_duty_time is null and t1.off_duty_time is null
group by t1.police_num
having count(*)>=5);
select u.name,u.policenum,u.DUTYNAME,u.dutylevel,t2.name,tmp.tmpcount from t_sys_user u,t_sys_dept t2,tmp
where substr(u.deptid,0,8)=t2.deptid
and u.policenum=tmp.tmpnum
order by u.deptid,u.dutylevel,tmp.tmpcount