本文共 1999 字,大约阅读时间需要 6 分钟。
续接前两篇博客
用到的数据库表如下:
employees_testCREATE TABLE employees_test(ID INT PRIMARY KEY NOT NULL,NAME TEXT NOT NULL,AGE INT NOT NULL,ADDRESS CHAR(50),SALARY REAL);
audit
CREATE TABLE audit(EMP_no INT NOT NULL,NAME TEXT NOT NULL);
titles_test
CREATE TABLE IF NOT EXISTS titles_test (id int(11) not null primary key,emp_no int(11) NOT NULL,title varchar(50) NOT NULL,from_date date NOT NULL,to_date date DEFAULT NULL);
员工奖金表emp_bonus
create table emp_bonus(emp_no int not null,recevied datetime not null,btype smallint not null);
薪水表salaries
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`));
create trigger audit_log after insert on employees_test begin insert into audit(EMP_no,NAME) values(new.ID,new.NAME); end;
insert into titles_test values ('1', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),('2', '10002', 'Staff', '1996-08-03', '9999-01-01'),('3', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01'),('4', '10004', 'Senior Engineer', '1995-12-03', '9999-01-01'),('5', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),('6', '10002', 'Staff', '1996-08-03', '9999-01-01'),('7', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01');
delete from titles_test where id not in (select min(id) from titles_test group by emp_no);
update titles_test set emp_no=replace(emp_no,10001,10005)where id=5;
-- 方法1-- select em.* from employees em,emp_v ev where em.emp_no=ev.emp_no;-- 方法2select * from employees intersect select * from emp_v;
update salariesset salary=salary*1.1where emp_no in (select emp_no from emp_bonus) and to_date='9999-01-01';
select (length('10,A,B')-length(replace('10,A,B',',','')))/length(',') as cnt;
转载地址:http://dslrb.baihongyu.com/