实例创建数据库及员工和部门表
--创建新数据库
create database spdbl
--创建部门表
create table dept
(
deptno int primary key,
dname nvarchar(30),
loc nvarchar(30)
)
select*from dept
--创建员工表
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需要设置为外键
)
select*from emp
--1外键只能指向主键
--2外键和主键的类型要一致
添加数据
-- 插入部门数据;
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')
--插入雇员数据
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);
简单查询
--查询SMITH的薪水,工作,所在部门
select sal,job,deptno from emp where ename='smith'--sqlserver表内不区分大小写
--消除重复记录(只能消除完全重复的行)
select distinct deptno from emp
select distinct deptno,ename from emp
--显示每个员工的年工资
--年工资为别名
--如何处理空值的问题isnull
select ename,sal*13+isnull(comm,0)*13 年工资 from emp
--显示工资高于2000的员工
select ename from emp where sal>2000
--如何查找1982.1.1后入职的员工
select*from emp where hiredate>'1982-1-1'
--显示工资在2000到2500的员工
select*from emp where sal>2000 and sal<2500
select*from emp where sal between 2000 and 2500--between包括2000和2500
--显示首字母为S的员工姓名和工资
select ename,sal from emp where ename like 'S%'--%表示多个字符
--显示第三个字母为O的员工姓名和工资
select ename,sal from emp where ename like '__O%'--_表示单个字符
--显示empno为123,345,7698的员工
select*from emp where empno in(123,345,7698)
--显示没有上级的员工
select*from emp where mgr is null
--查询工资高于500或岗位为MANAGER的员工,同时要满足他们的姓名首字母为J
select*from emp where (sal>500 or job='MANAGER ')and ename like 'J%'