SQL经典18题(不知道经不经典) - 数据库编程

atetime_expr2the 之间的整数差。其结果的单位由interval 参数给出。interval 的法定值同TIMESTAMPADD()函数说明中所列出的相同。

SELECT TIMESTAMPDIFF(MONTH,'2009-10-01','2009-09-01');
interval可是：
秒 SECONDS
分钟 MINUTES

小时 HOURS
天 DAYS
月 MONTHS
年 YEARS

2.MySql下的建表语句和初始化数据

CREATE TABLE EMP 
   (EMPNO int auto_increment, 
	ENAME VARCHAR(10), 
	JOB VARCHAR(9), 
	MGR int, 
	HIREDATE DATETIME, 
	SAL int, 
	COMM int, 
	DEPTNO int, 
     CONSTRAINT PK_EMP PRIMARY KEY (EMPNO)
   );   

  CREATE TABLE DEPT 
   (DEPTNO int auto_increment, 
	DNAME VARCHAR(14), 
	LOC VARCHAR(13), 
	CONSTRAINT PK_DEPT PRIMARY KEY (DEPTNO)
   );

alter table EMP add constraint FK_DEPTNO foreign key (DEPTNO) references DEPT(DEPTNO);

Insert into DEPT (DEPTNO,DNAME,LOC) values (1,'ACCOUNTING','NEW YORK');
Insert into DEPT (DEPTNO,DNAME,LOC) values (2,'RESEARCH','DALLAS');
Insert into DEPT (DEPTNO,DNAME,LOC) values (3,'SALES','CHICAGO');
Insert into DEPT (DEPTNO,DNAME,LOC) values (4,'OPERATIONS','BOSTON');

Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7369,'SMITH','CLERK',7902,'1980-12-17',800,null,2);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7499,'ALLEN','SALESMAN',7698,'1981-2-20',1600,300,3);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7521,'WARD','SALESMAN',7698,'1981-2-22',1250,500,3);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7566,'JONES','MANAGER',7839,'1981-4-2',2975,null,2);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7654,'MARTIN','SALESMAN',7698,'1981-9-28',1250,1400,3);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7698,'BLAKE','MANAGER',7839,'1981-5-1',2850,null,3);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7782,'CLARK','MANAGER',7839,'1981-6-9',2450,null,1);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7788,'SCOTT','ANALYST',7566,'1981-4-19',3000,null,2);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7839,'KING','PRESIDENT',null,'1981-11-17',5000,null,1);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7844,'TURNER','SALESMAN',7698,'1981-9-8',1500,0,3);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7876,'ADAMS','CLERK',7788,'1987-5-23',1100,null,2);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7900,'JAMES','CLERK',7698,'1981-12-3',950,null,3);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7902,'FORD','ANALYST',7566,'1981-12-3',3000,null,2);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7934,'MILLER','CLERK',7782,'1982-1-23',1300,null,1);

SQL经典18题(不知道经不经典)(二)

2.MySql下的建表语句和初始化数据