SQL-创建表

SQL-创建表

转载请注明出处：58电脑社区 http://www.expert58.com

创建表 (一)

一、创建表

1、   数据类型

整型：int

精确小数：decimal(m,n)或numeric(m,n)

          M表示数据长度，N小数位数

货币：money 默认保留小数点后四位（储蓄）

时间日期：datetime

字符串：char(n),varchar(n)

Char(n)：定量长度字符串，N表示字符长度

        输入的值小于N时，后面自动补空格

Varchar(n)：变长字段串，N表示字符长度

        输入的值小于N时，有多少算多少

注：字符串和时间日期的值要加单引号

 

例：book(isbn,title,page,price,press)

读者(借书证号,身份证,姓名,性别)

借阅(书号,借书证号,借阅时间,归还时间)

查询分析建立表：

Create table 表名  数据库名.dbo.表名

                 数据库名..表名

                 表名

(字段名１　数据类型，

 字段名２　数据类型，

 字段名3  数据类型，

…….)

 

2、   完善表格（约束）

A、 主键约束  primary key

不允许空不重复

当单字段充当键码时：

直接在字段的数据类型后加入primary key

当多字段充当键码时：

交通事故（肇事者，受害人，时间，地点）

Create table 表名(

字段名1 数据类型，

字段名2 数据类型，

字段名3 数据类型，

……            ，

Primary key(字段1，2，3，……)

)

注：每个表中只能出现一次primary key

B、 非空约束 not null

直接在字段的数据类型后加入not null

可以多次出现。

C、 唯一性约束 unique

直接在字段的数据类型后加入unique

可以多次出现。

D、外键约束  references ，至少要有两个表格，一个表是主键表，另一个表是外键表。外键表中的某个字段是从主键表中继承过来的键码。在定义外键表时设置外键约束。

注：必须先建立主键表，再创建外键表

Create table 外键表名(

某字段名 数据类型 references

主键表名（键码）

）

注：定义外键表时要求外键表中继承字段的数据类型必须与约束它的主键表中的键码数据类型完全一样。

 

E、  默认值约束 default

直接在字段数据类型后加入default ‘值’

注：字段定义为默认值，如果不填充该字段，则该字段会以默认值自动填充，如果手填充该字段，以手动填充为准。

 

特例：以系统时间为默认填充

Getdate() 时间日期函数

 

计算字段：

Create table 表名(

字段1 数据类型，

字段2 数据类型，

字段3 数据类型，

……           ，

字段n  as  其它字段的算术运算表达式）

注：计算字段无法填充，它的值来自于其它字段的运算结果。

 

-------------------------------------------------------------------------------------------------------------------------

创建表二

 

创建表格（二）

F、条件约束 check

基础知识：

1、算术运算：+，-，*，/，%（求余）

2、赋值运算：declare @变量名 数据类型(定义)

         Set @变量名=’值’        (赋值)

3、位运算：按位与运算，按位或，按位异或

按位与：&  将数据转为二进制，位数对齐，相同位的数据如果都是1其值为1，否则为0

按位或：| 将数据转为二进制，位数对齐，相同位的数据只要有一个是1其值为1，否则为0

按位异或：^ 相同位的数据如果相同其值为0，否则为1

4、比较运算：>，<，=，>=，<=，<>

             !=不等于,!>不大于,!<不小于

5、逻辑运算：AND，OR，NOT

格式：create table 表名(

某字段 数据类型 check(条件)

）

              字段名 比较运算符 值（每个条件）

例1：图书表中的价格为正值

Create table 图书(

书号 char(13) primary key,

书名 varchar(20) not null,

价格 decimal(10,2) check(价格>0)

)

例2：要求读者中的性别必须是男或女

Create table 读者(

借书证号 int primary key,

身份证 char(18) unique,

姓名 varchar(50) not null,

性别 char(2) check(性别=’男’ or 性别=’女’)

)

例3：要求员工的年龄必须是在18到55岁之间。

Create table 员工(

工号 int primary key,

姓名 varchar(50),

年龄 int check (年龄>=18 and年龄<=55)

)

特殊的check关系

Between   A   AND  B   在A和B之间

In （‘A’，‘B’，‘C’，……）在…范围内

Like ‘值+通配符’

 

1、   between

check(字段名 between  A   AND  B)相当于 字段名>=A  AND  字段名<=B

注：between包含边界，并且A小于B

Create table 员工(

工号 int primary key,

姓名 varchar(50),

年龄 int check (年龄 between 18 and 55)

)

例4：要求学生的年龄大于等于6岁并小于25岁。 

Check(年龄>=6 and 年龄<25) 不可以用between

2、   IN

Check(字段名  in (‘A’,’B’,’C’,……))相当于

Check(字段名=’A’ OR 字段名=’B’  or

字段名=’C’  or ……)

Create table 读者(

借书证号 int primary key,

身份证 char(18) unique,

姓名 varchar(50) not null,

性别 char(2) check(性别 in(’男’,’女’))

)

注：只有等于号的或连接才可以用IN

例5：要求学生籍贯只能是南京，徐州，淮安，杭州

Check(籍贯 in(‘南京’,’徐州’,’淮安’,’杭州’))

3、   like

check(字段名 like ‘值+通配符’)

                          % 表示0个，1个或多个字符

            _ 表示有且仅有一个字符

例6：图书名中必须含有‘计算机’三个字

Check(书名 like ‘%计算机%’)

 

规则的应用： rule

规则和约束之间最主要的区别是约束是依赖于表格才能存在，而规则是独立的，需要与表格进行绑定。

在企业管理器中建立规则：

确定规则名，文本内容：

@变量名 比较运算符  值

与check中的条件书写格式一样，只不过用变量来代替字段名。

在查询分析器中建立规则：

Create rule 规则名

as

@变量名 比较运算符  值

绑定规则：sp_bindrule

Sp_bindrule ‘规则名’,’表名.字段名’

注：每个字段上可以绑定多个规则，但只有最后绑定的规则起作用。其它规则自动解除绑定，但每个规则可以绑定到多个字段上。

解除绑定：sp_unbindrule  ‘表名.字段名’

删除规则：drop rule 规则名

注：规则只有解除绑定才能删

G、标识（自动编号） identity

Identity(seed,incroment)

 

          初始值   增量   (可省略，默认为1)

注：identity不能填充，只有整型才可以自动编号。

直接跟在字段数据类型后。 

 

-------------------------------------------------------------------------------------------------------------------------

SQL创建表的例子

-------------------------------------------------------------------------------------------------------------------------

 例一

 

1、          新建‘学生管理’数据库，要求在该数据库中有两个文件组，primary和 ‘教学管理’

Primary 文件组中有一个文件，文件名为‘班级管理’，保存在E盘‘班级管理’文件夹中

教学管理文件夹中有两个文件分别是‘学生’和‘课程’保存在E盘

 

答案:

create database 学生管理

on(name=班级管理,filename='e:\班级管理.mdf'),

filegroup 教学管理

(name=学生,filename='e:\学生.mdf'),

(name=课程,filename='e:\课程.mdf')

 

2、          新建表格 班级（班级编号，班级教室，班主任）要求班级编号为主健码，班级教室默认为‘CSTP投24’，班主任不允许为空。

 

答案:

use 学生管理

create table 班级

(班级编号 char(10) primary key,

 班级教室 varchar(20) default 'CSTP投24',

 班主任 varchar(10) not null)

 

3、          新建表格 学生（学号，姓名，性别，年龄，班级）要求学号为主键码，姓名和性别不允许为空，班级受到班级表中班级编号的约束。

 

答案:

use 学生管理

create table 学生

(学号 char(10) primary key,

 姓名 varchar(20) not null,

 性别 char(2) not null,

 年龄 int,

 班级 char(10) references 班级)

 

4、          新建表格 课程（课号，课名，课时）要求课号为主键，课名不允许为空。

 

答案:

use 学生管理

create table 课程

(课号 char(10) primary key,

 课名 varchar(20) not null,

 课时 char(50)) 

 

5、          新建表格 选课（课号，学号）要求课号+学号为键码，课号受到课程表中的课号的约束，学号受到学生表中学号的约束。

 

答案:

use 学生管理

create table 选课

(课号 char(10) references 课程,

 学号 char(10) references 学生,

 primary key(课号,学号)) 

 

6、          新建表格 成绩（学号，数据库，C语言，JAVA，总分，平均分）要求学号受到学生表中学号的约束，总分和平均分为计算字段。

 

答案:

use 学生管理

create table 成绩

(学号 char(10) references 学生,

 数据库 int,

 C语言 int,

 JAVA int,

 总分 as 数据库+C语言+JAVA,

 平均分 as (数据库+C语言+JAVA)/3)

 

完成以上题目，字段的数据类型自定。以本班学生为例填充数据。

 

-------------------------------------------------------------------------------------------------------------------------

例二

 

 

1、          新建数据库‘图书销售’

数据库中有两个文件组，primary和销售记录

在primary组中有一个文件，文件名叫‘图书种类’保存在E盘‘图书’文件夹中，初始大小为5MB

销售记录组中有两个文件，分别是‘已售图书’和‘退还图书’，保存在E盘‘销售记录’文件夹中，初始大小为10MB,上限为20MB

 

答案:

create database 图书销售

on (name=图书种类,filename='e:\图书.mdf',size=5),

filegroup 销售记录

(name=已售图书,filename='e:\已售图书.mdf',size=10,maxsize=20),

(name=退还图书,filename='e:\退还图书.mdf',size=10,maxsize=20)

 

2、          新建表格‘出版社’

出版社（出版社名，出版社地址，出版社邮编）

要求出版社名为主键，出版社地址和出版社邮编不允许空。同时出版社邮编唯一

 

答案:

create table 出版社(

 出版社名 varchar(50) primary key,

 出版社地址 varchar(100) not null,

 出版社邮编 varchar(50) not null unique

)

 

 

3、          新建表格‘图书’

图书（书号，书名，价格，页数，出版社）

要求书号为主键，书名不允许空，页数为正值，出版社默认为‘电子工业出版社’，图书中的出版社字段受到出版社表中出版社名字段的约束。

 

答案:

create table 图书(

 书号 char(20) primary key,

 书名 varchar(50) not null,

 价格 decimal(9,2),

 页数 int check(页数>0),

 出版社 varchar(50) default '电子工业出版社' references 出版社(出版社名)

)

 

 

4、          新建表格‘作者’

作者（作者笔名，作者姓名，作者国籍，性别）

要求作者笔名为主键，作者姓名不允许空，作者国籍默认为‘中国’

作者性别的值是‘男’或‘女’

 

答案:

create table 作者(

 作者笔名 varchar(50) primary key,

 作者姓名 varchar(50) not null,

 作者国籍 varchar(50) default '中国',

 性别 char(2) check(性别 in('男','女'))

)

 

5、          新建表格‘著作’

著作（序号，书号，作者）

要求书号+作者笔名为主键，书号从图书表中的书号中继承，作者从作者表中作者笔名中继承。序号为自动编号

 

答案:

create table 著作(

 序号 int identity(1,1),

 书号 char(20) references 图书(书号),

 作者 varchar(50) references 作者(作者笔名),

 primary key(书号,作者)

)

 

6、          新建规则，要求定义规则名为’A’，作用是限制图书的价格在20到100之间。

 

答案:

create rule A as @x>=20 and @x<=100

sp_bindrule 'A','图书.价格'

 

7、          新建规则，要求定义规则名为‘B’作用是限制图书的书名中必须要有‘语言’二个字

 

答案:

create rule B as @x like '%语言%'

sp_bindrule 'B','图书.书名'

转载请注明出处：58电脑社区 http://www.expert58.com