韩顺平循序渐进学java从入门到精通第59讲,第60讲sql.doc

-创建一个新的数据create database spdb1-创建dept表create table dept(deptno int primary key,dname nvarchar(30),loc nvarchar(30)-创建emp表create table emp(empno int primary key,ename nvarchar(30),job nvarchar(30),mgr int,hiredate datetime,sal numeric(10,2),comm numeric(10,2),deptno int foreign key references dept(deptno) -因为deptno根据需要做成外键-针对外键,请注意:-1）外键只能指向主键-2）外键和主键的数据类型要一致insert into emp (empno,ename,job,mgr,hiredate,sal,deptno) values (7369,SMITH,CLERK,7902,1980-12-17,800.00,20);insert into emp values (7499,ALLEN,SALESMAN,7698,1981-2-20,1600,300,30);insert into emp values (7521,WARD,SALESMAN,7698,1981-2-22,1250,500.00,30);insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7566,JONES,MANAGER,7839,1981-4-2,2975,null,20);insert into emp values (7654,MARTIN,SALESMAN,7698,1981-9-28,1250,1400,30);insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7698,BLAKE,MANAGER,7839,1981-5-1,2850,NULL,30);insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7782,CLARK,MANAGER,7839,1981-6-9,2450,NULL,10);insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values ( 7788, SCOTT, ANALYST, 7566, 1987-4-19, 3000, NULL, 20);insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values ( 7839, KING, PRESIDENT, NULL, 1981-11-17, 5000, NULL, 10);insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values ( 7844, TURNER, SALESMAN, 7698, 1981-9-8, 1500, 0, 30);insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7876, ADAMS, CLERK, 7788, 1987-5-23, 1100, NULL, 20);insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7900, JAMES, CLERK, 7698, 1981-12-3, 950, NULL, 30);insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values ( 7902, FORD, ANALYST, 7566, 1981-12-3, 3000, NULL, 20);insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7934, MILLER, CLERK, 7782, 1982-1-23, 1300, NULL, 10);insert into dept (deptno, dname, loc) values (10, ACCOUNTING, NEW YORK);insert into dept (deptno, dname, loc) values (20, RESEARCH, DALLAS);insert into dept (deptno, dname, loc) values (30, SALES, CHICAGO);insert into dept (deptno, dname, loc) values (40, OPERATIONS, BOSTON)select * from deptselect * from emp-查询SMITH的薪水，工作，所在部门select sal,job from emp where ename=SMITH-取消重复行(distinct只能消除完全一样的行)select distinct deptno from emp-显示每个雇员的年工资(工资+奖金)select ename,sal*13+isnull(comm,0)年工资 from emp; -工资高于的员工select * from emp where (sal+isnull(comm,0)3000-如何查找.1.1后入职的员工select *from emp where hiredate1982-1-1-工资在-3000的员工select * from emp where (sal+isnull(comm,0)=2500)and(sal+isnull(comm,0)500 or job=MANAGER) and ename likeJ%-按照工资从低到高显示雇员信息(order by 默认升序 asc升序 desc降序)select * from emp order by salselect * from emp order by sal desc-按照入职的先后顺序排序select * from emp order by hiredate-按照部门号