create table user_info
(
userid int identity(1,1) not null, --序号
userName varchar(50) not null, --姓名
sex varchar(4) , --性别
userNo varchar(50) null, --身份证号
userXieli varchar(20) null,--学历
style varchar(50) null,--类型
speciality varchar(50) null, --专业
tel varchar(50) null, --联系电话
city varchar(50) null, --意向城市
job varchar(50) null, --意向岗位
othreJob varchar(50) null,--其它岗位
salary varchar(50) null, --薪资要求
art varchar(50) null, ---专业特长
superemacy varchar(200) null, --优势
remark varchar(500) null, --备注
primary key (userid)
)
create proc sp_add_userInfo
as
declare @userName varchar(50) --姓名
declare @sex varchar(4) --性别
declare @userNo varchar(50) --身份证号
declare @userXieli varchar(20) --学历
declare @style varchar(50) --类型
declare @speciality varchar(50) --专业
declare @tel varchar(50) --联系电话
declare @city varchar(50) --意向城市
declare @job varchar(50) --意向岗位
declare @othreJob varchar(50) --其它岗位
declare @salary varchar(50) --薪资要求
declare @art varchar(50) ---专业特长
declare @superemacy varchar(200) --优势
declare @remark varchar(500)
declare @count integer
declare @index integer
declare @userID varchar(200)
set @index = 0
set @count = 20
while @index < @count
begin
insert into user_info(
userName ,
sex,
userNo,
userXieli,
style,
speciality,
tel,
city,
job,
othreJob,
salary,
art,
superemacy,
remark
)
values
(
@userName ,
@sex,
@userNo,
@userXieli,
@style,
@speciality,
@tel,
@city,
@job,
@othreJob,
@salary,
@art,
@superemacy,
@remark
)
set @index = @index+1
end
go
CREATE PROCEDURE add_UserInfo
AS
DECLARE @userCode VARCHAR(30)
DECLARE @userName VARCHAR(30)
DECLARE @userCode_base VARCHAR(30)
DECLARE @count INTEGER
DECLARE @index INTEGER
DECLARE @rand1 INTEGER
DECLARE @rand2 INTEGER
SET @userCode_base='qs_'
SET @userName='userName'
SET @count=100000
SET @index=10000
WHILE @index<@count
BEGIN
SET @userCode=@userCode_base+CONVERT(VARCHAR,@index)
SET @rand1=convert(int,rand()*5)
SET @rand2=convert(int,rand()*5)
INSERT INTO userInfo (userCode,roleType,groupID,userName,text1,text2,text3)
VALUES (@userCode,@rand1,@rand2,@userName,'aokei kaol jof','','aokei kaol jof')
SET @index=@index+1
END
GO
DECLARE @MyCounter INT
SET @MyCounter = 0 /*设置变量*/
WHILE (@MyCounter < 2) /*设置循环的次数*/
BEGIN
WAITFOR DELAY '000:00:10' /*延时10秒*/
INSERT INTO time_by_day
(time_id, the_date, the_year, month_of_year, quarter, day_of_month)
SELECT TOP 1 time_id + 1 AS time_id, the_date + 1 AS the_date, YEAR(the_date + 1)
AS the_year, MONTH(the_date + 1) AS month_of_year, { fn QUARTER(the_date + 1)
} AS quarter, DAY(the_date + 1) AS day_of_month
FROM time_by_day
ORDER BY time_id DESC
SET @MyCounter = @MyCounter + 1
END
select userid insert into
select * from person;
select * insert into person values('a','b',20,'110')
select * from emp where hiredate in
(
select hiredate from emp group by hiredate,deptno having count(*)>1
)
and deptno in
(select deptno from emp group by hiredate,deptno having count(*)>1)
select rownum,empno,ename from emp where rownum<11
minus
select rownum,empno,ename from emp where rownum<6
查询表中相同部门中年龄最大的职员信息
select * from person2 p,
(select max(age) age,dept from person2 group by dept)g
where p.age = g.age and p.dept = g.dept
0
157
3KB
2011-03-23
10
