本文章提供了三款创建表的sql语句实例代码,一一举例测试并说明了如何利用sql创建数据表与数据库说明实例。
本文章提供了三款创建表的sql语句实例代码,一一举例测试并说明了如何利用sql创建数据表与说明实例。
use [master] go if exists(select 1 from sysdatabases where name=n’hktemp’) begin drop database hktemp –如果数据库存在先删掉数据库 end go create database hktemp on primary –创建主数据库文件 ( name=’hktemp’, filename=’e:databaseshktemp.dbf’, size=5mb, maxsize=20mb, filegrowth=1mb ) log on –创建日志文件 ( name=’hktemplog’, filename=’e:databaseshktemp.ldf’, size=2mb, maxsize=20mb, filegrowth=1mb ) go –添加表 if not exists (select * from sys.objects where object_id = object_id(n’hksj_user’) and type in (n’u’)) begin create table hksj_user ( id int identity(1,1) not null, sname nvarchar(20) not null, snickname nvarchar(20), spassword nvarchar(30) not null, dcreatedate datetime , screator nvarchar(20), semail nvarchar(50), sphone nvarchar(50), sidentifyid nvarchar(30), dlasttimelogon datetime primary key clustered ( id asc )with (ignore_dup_key = off) on [primary] ) on [primary] end go –添加注释 exec sys.sp_addextendedproperty @name=n’ms_description’, @value=n’登录名’ , @level0type=n’schema’,@level0name=n’dbo’, @level1type=n’table’,@level1name=n’hksj_user’, @level2type=n’column’,@level2name=n’sname’ go exec sys.sp_addextendedproperty @name=n’ms_description’, @value=n’邮箱’ , @level0type=n’schema’,@level0name=n’dbo’, @level1type=n’table’,@level1name=n’hksj_user’, @level2type=n’column’,@level2name=n’semail’
实例二
drop table cell_tch;
drop table ms;
drop table msc;
drop table bsc;
drop table bts;
drop table cell;
drop table antenna;
drop table data;
drop table test;
drop table neighbor;create table msc (
mscid integer not null,
mscname character (10),
msccompany character (10),
msclongitude decimal (9,6),
msclatitude decimal (8,6),
mscaltitude integer,
primary key ( mscid) ) ;create table bsc (
bscid integer not null,
bscname character (10),
bsccompany character (10),
longitude decimal (9,6),
latitude decimal (8,6),
mscid integer,
primary key (bscid),
foreign key (mscid) references msc (mscid)) ;create table bts (
btsname character (20) not null,
bscid integer not null,
longitude decimal (9,6),
latitude decimal (8,6),
altitude integer,
btscompany character (10),
btspower decimal (2,1),
primary key (btsname),
foreign key (bscid) references bsc (bscid)) ;create table cell (
cellid integer not null,
btsname character (20),
areaname character (10),
lac integer,
longitude decimal (9,6),
latitude decimal (8,6),
direction integer,
radious integer,
antnum integer,
bcch integer,
primary key ( cellid),
foreign key (btsname) references bts (btsname)) ;create table ms (
imei bigint not null,
msisdn bigint,
username varchar(10),
mscompany varchar (20),
gsmmsense integer,
gsmmsheight decimal (3,2),
gsmmspfout decimal (3,2),
mzone character (10),
cellid integer,
primary key ( imei),
foreign key (cellid) references cell (cellid)) ;create table antenna (
cellid integer not null,
antennahigh integer,
halfpangle integer,
maxattenuation integer,
gain integer,
anttilt integer,
pt integer,
mspwr integer,
primary key ( cellid));create table cell_tch (
cellid integer,
freq integer,
foreign key (cellid) references cell (cellid));create table data (
date integer not null,
time integer not null,
cellid integer not null,
ntch integer,
traff decimal (9,7),
rate decimal (10,9),
thtraff decimal (9,7),
callnum integer,
congsnum integer,
callcongs decimal (10,9),
primary key ( date,time,cellid)) ;create table test (
keynum integer not null,
cellid integer,
latitude decimal (9,6),
longitude decimal (8,6),
rxlev decimal (9,6),
primary key ( keynum),
foreign key (cellid) references cell (cellid));create table neighbor (
cellid integer,
adjcellid integer,
celllac integer,
adjcelllac integer,
foreign key (cellid) references cell (cellid));
import from “c:msc.csv” of del method p (1, 2, 3, 4, 5, 6) messages “1” insert into db2admin.msc (mscid, mscname, msccompany, msclongitude, msclatitude, mscaltitude);import from “c:bsc.csv” of del method p (1, 2, 3, 4, 5, 6) messages “1” insert into db2admin.bsc (bscid, bscname, bsccompany, longitude, latitude, mscid);
import from “c:bts.csv” of del method p (1, 2, 3, 4, 5, 6, 7) messages “1” insert into db2admin.bts (btsname, bscid, longitude, latitude, altitude, btscompany, btspower);
import from “c:cell.csv” of del method p (1, 2, 3, 4, 5, 6, 7, 8, 9, 10) messages “1” insert into db2admin.cell (cellid, btsname, areaname, lac, longitude, latitude, direction, radious, antnum, bcch);
import from “c:data.csv” of del method p (1, 2, 3, 4, 5, 6, 7, 8, 9, 10) messages “1” insert into “data” (date, time, cellid, ntch, traff, rate, thtraff, callnum, congsnum, callcongs);
import from “c:ms.csv” of del method p (1, 2, 3, 4, 5, 6, 7, 8, 9) messages “1” insert into db2admin.ms (imei, msisdn, username, mscompany, gsmmspsense, gsmmsheight, gsmmspfout, mzone, cellid);
import from “c:cell_tch.csv” of del method p (1, 2) messages “1” insert into db2admin.cell_tch (cellid, freq);
import from “c:test.csv” of del method p (1, 2, 3, 4, 5) messages “1” insert into db2admin.test (keynum, cellid, latitude, longitude, rxlev);
import from “c:antenna.csv” of del method p (1, 2, 3, 4, 5, 6, 7, 8) messages “1” insert into db2admin.antenna (cellid, antennahigh, halfpangle, maxattenuation, gain, anttilt, pt, mspwr);
import from “c:neighbor.csv” of del method p (1, 2, 3, 4) messages “1” insert into db2admin.neighbor (cellid, adjcellid, celllac, adjcelllac);
方法三
举例:
create table [学生]
(
学号 char(8) not null primarykey,
姓名 varchar(8) not null
)
上面写了两列,其余差不多,至于不允许有重复姓名,加上“约束”就行了。
版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至253000106@qq.com举报,一经查实,本站将立刻删除。
发布者:PHP中文网,转转请注明出处:https://www.chuangxiangniao.com/p/1836339.html