1 create database gongsi 2 go 3 use gongsi 4 go 5 create table bumen 6 ( 7 bcode int primary key not null, 8 bname varchar(20), 9 bceo varchar(20), 10 btel varchar(20), 11 ) 12 go 13 create table renyuan 14 ( 15 code int primary key identity(10001,1) not null, 16 name varchar(20), 17 sex char(10), 18 age int, 19 cid varchar(20), 20 tel varchar(20), 21 bumen int 22 ) 23 go 24 25 --插入数据 向部门表 26 insert into bumen values(1001,'财务部','张三','1234567') 27 insert into bumen values(1002,'企划部','李四','2345678') 28 insert into bumen values(1003,'市场部','王五','3456789') 29 insert into bumen values(1004,'客服部','赵六','4567890') 30 go 31 --插入人员表的信息 32 insert into renyuan values('张三','男',33,'123456789012345678','1234567',1001) 33 insert into renyuan values('张全蛋','男',29,'234567890123456789','7654321',1001) 34 insert into renyuan values('张','男',33,'3456789012345677777','8765432',1001) 35 36 insert into renyuan values('李四','女',45,'789012345678903456','2345678',1002) 37 insert into renyuan values('李莲英','男',55,'890789078907895622','6789056',1002) 38 insert into renyuan values('李洪','女',45,'456789076543265443','8765434',1002) 39 40 insert into renyuan values('王五','男',37,'876542345798765434','6543234',1003) 41 insert into renyuan values('王二麻','女',32,'23456876542345873','2376532',1003) 42 insert into renyuan values('王二丫','女',23,'12345654322345654','6542346',1003) 43 insert into renyuan values('王查查','女',23,'12345654322345654','6542346',1003) 44 insert into renyuan values('王甜','女',23,'12345654322345654','6542346',1003) 45 46 47 insert into renyuan values('赵六','女',26,'234765423456222','9874533',1004) 48 insert into renyuan values('赵敏','女',25,'5434567765433456','7623456',1004) 49 insert into renyuan values('赵英俊','男',32,'125432886543225','8565424',1004) 50 go 51 52 select * from bumen 53 select * from renyuan 54 --插入一条错误数据 显示一下外键是什么作用 55 insert into renyuan values('甲鱼','女',26,'234765423456222','9874533',1008) 56 57 --显示一下主键的作用 58 insert into bumen values(1001,'销售部','钱八','2345652') 59 60 61 ---查询人数最多的部门里年龄最大的人的信息 62 select bumen from renyuan group by bumen having COUNT(*)>4 63 select MAX(age) from renyuan where bumen=1003 64 select code from renyuan where bumen=1003 and age =37 65 select * from renyuan where code= 66 (select code from renyuan where bumen=1003 and age =37 67 ) 68 69 70 --按照年龄从小到大排序,取789号人员的所有信息 71 select top 3 * from renyuan where code not in 72 (select top 6 code from renyuan order by age) order by age 73 74 --查找所有男同志里面年龄最大的人的信息 75 select*from renyuan where code= 76 (select top 1 code from renyuan where age= 77 (select MAX(age) from renyuan where sex='男')) 78 79 --分页查询 80 select top 5 * from renyuan 81 --6~10 82 select top 5 * from renyuan where code not in 83 (select top 5 code from renyuan) 84 --11-14 85 select top 5 * from renyuan where code not in 86 (select top 10 code from renyuan) 87 88 --能分多少页 89 select ceiling(( select COUNT(*)from renyuan)/5.0) 90 91 92 --将人员表显示出来 并且将部门编号变为部门名称显示 93 select name , sex ,(select bname from bumen where bumen.bcode = renyuan.bumen) from renyuan