USE [master];
GO
DROP DATABASE [mydata];
GO
CREATE DATABASE [mydata];
GO
USE [mydata];
GO
CREATE TABLE [dbo].[t_user]
(
[id] [int] IDENTITY(1,1) NOT NULL,
[username] [nvarchar](max) NOT NULL DEFAULT (N'无'),
[gender] [varchar](max) NOT NULL DEFAULT ('m'),
[birthday] [date] NOT NULL DEFAULT ('1900-01-01'),
[homeaddress] [nvarchar](max) NOT NULL DEFAULT (N'无'),
[idnumber] [char](18) NOT NULL DEFAULT ('0'),
PRIMARY KEY ([id]),
CHECK ([gender]='m' OR [gender]='f')--批量插入数据时先注销这一句,因为每次检查是不是男,女和无速度会很慢.
);
insert into t_user ([username]) values (N'张三');
insert into t_user ([gender]) values ('m');
insert into t_user ([birthday]) values ('1980-1-1');
insert into t_user ([homeaddress]) values (N'北京市');
insert into t_user ([idnumber]) values ('0');
declare @i int;
set @i = 0;
while @i < 240
begin
insert into t_user ([username], [gender], [birthday], [homeaddress], [idnumber]) values (N'七七', 'f', '1980-1-1', N'北京市东城区2号', '110101198001010010');
set @i = @i + 1
end
select username as 姓名,gender as 性别,birthday as 出生年月, homeaddress as 地址,idnumber as 身份证 from t_user;
select * from t_user where idnumber = '0'