SQL数据库_创建表

4.1.3SQL语言功能概述SQL功能命令动词数据查询SELECT数据定义CREATE、DROP、ALTER数据操纵INSERT、UPDATE、DELETE数据控制GRANT、REVOKE四部分：数据定义功能、数据控制功能、数据查询功能和数据操纵功能。4.2SQL的数据类型•数值型•字符串型•日期时间型•货币型数值型•准确型•整数Bigint:8字节，Int：4字节Smallint：2字节，Tinyint：1字节Bit：1位，存储1或0•小数Numeric（p,q）或Decimal（p,q），其中：p为数字位长度，q：小数位长度。•近似型Float：8字节Real：4字节字符串型•普通编码字符串类型•统一字符编码字符串类型•二进制字符串类型普通编码字符串类型•Char（n）:定长存储，n=8000•Varchar（n）：不定长存储（按实际长度存储），长度最大不超过n,n=8000注：n为字符个数•Text：存储大于8000字节的文本统一字符编码字符串类型•nchar（n）:定长存储，n=4000•nvarchar（n）：不定长存储，长度最大不超过n,n=4000•ntext：存储大于8000字节的文本•特点：每个字符占两个字节二进制字符串类型•Binary(n)：固定长度，n=8000。•Varbinary(n)：可变长度，n＝8000。注：n为二进制数据的字节数•image：大容量、可变长二进制字符数据，可用于存储文件。日期时间型•Datetime：8字节，年月日时分秒毫秒（例：‘2001/08/0310:30:00.000’）•SmallDateTime：4字节，年月日时分（例：‘2001/08/0310:30:00’）•日期、时间的输入格式货币类型•Money：8个字节，精确到货币单位的千分之十。•Smallmoney：4个字节，精确到货币单位的千分之十。•限制到小数点后4位。•可以带有适当的货币符号。例如，100英镑可表示为£100。4.3基本表的定义、删除及修改•4.3.1基本表的定义与删除•4.3.2修改表结构4.3.1基本表的定义与删除•1．定义基本表使用SQL语言中的CREATETABLE语句实现，其一般格式为：CREATETABLE表名（列名数据类型[列级完整性约束定义]{,列名数据类型[列级完整性约束定义］…}[,表级完整性约束定义]）在列级完整性约束定义处可以定义的约束•NOTNULL：限制列取值非空。•DEFAULT：给定列的默认值。•UNIQUE：限制列取值不重。•CHECK：限制列的取值范围。•PRIMARYKEY：指定本列为主码。•FOREIGNKEY：定义本列为引用其他表的外码。使用形式为：［FOREIGNKEY（外码列名）］REFERENCES外表名（外表列名）几点说明•NOTNULL和DEFAULT只能是列级完整性约束；•其他约束均可在表级完整性约束处定义。•注意以下几点：•第一，如果CHECK约束是定义多列之间的取值约束，则只能在表级完整性约束处定义；•第二，如果表的主码由多个列组成，则也只能在表级完整性约束处定义，并将主码列用括号括起来，即：PRIMARYKEY（列1｛[，列2]…｝）；•第三，如果在表级完整性约束处定义外码，则“FOREIGNKEY(外码列名)”部分不能省。约束定义•①列取值非空约束列名类型NOTNULL例：snamechar(10)NOTNULL约束定义（续）•②表主码约束•在定义列时定义主码（仅用于单列主码）列定义PRIMARYKEY例：SNOchar(7)PRIMARYKEY•在定义完列时定义主码（用于单列或多列主码）PRIMARYKEY（列名序列）例：PRIMARYKEY(SNO)PRIMARYKEY(SNO，CNO)约束定义（续）③外码引用约束•指明本表外码列引用的表及表中的主码列。[FOREIGNKEY（本表列名）]REFERENCES外表名（外表主码列名）例：FOREIGNKEY（sno）REFERENCES学生表（sno）约束定义（续）•④默认值约束•格式：DEFAULT默认值•例：定义系的默认值为“计算机系”。DEFAULT‘计算机系’约束定义（续）•⑤CHECK约束•格式：CHECK(约束表达式)•例：定义成绩大于等于0。CHECK(grade=0)约束定义（续）•⑥UNIQUE约束•在列级约束定义（仅用于单列约束）列定义UNIQUE例：SNAMEchar(7)UNIQUE•在表级约束定义（用于单列或多列组合约束）UNIQUE（列名序列）例：UNIQUE(SNO，CNO)•用于限制在一个列中不能有重复的值。•用在事实上具有惟一性的属性列上，比如每个人的身份证号码、驾驶证号码等均不能有重复值。•注意：•允许有一个空值；•在一个表中可以定义多个UNIQUE约束；•可以在一个列或多个列上定义UNIQUE约束。创建学生表CREATETABLEStudent(Snochar(7)PRIMARYKEY,Snamechar(10)NOTNULL,Ssexchar(2)CHECK(Ssex='男'ORSsex='女'),SagetinyintCHECK(Sage=15ANDSage=45),Sdeptchar(20)DEFAULT'计算机系')创建课程表CREATETABLECourse(Cnochar(10)NOTNULL,Cnamechar(20)NOTNULL,CcredittinyintCHECK(Ccredit0),SemestertinyintCHECK(Semester0),PeriodintCHECK(Period0),PRIMARYKEY(Cno))创建SC表CREATETABLESC(Snochar(7)NOTNULL,Cnochar(10)NOTNULL,Gradetinyint,CHECK(Grade=0andGrade=100),PRIMARYKEY(Sno,Cno),FOREIGNKEY(Sno)REFERENCESStudent(Sno),FOREIGNKEY(Cno)REFERENCESCourse(Cno))删除表•当确信不再需要某个表时，可以将其删除•删除表时会将与表有关的所有对象一起删掉，包括表中的数据。•删除表的语句格式为：DROPTABLE表名{[,表名]…}•例：删除test表的语句为：DROPTABLEtest4.3.2修改表结构•在定义完表之后，如果需求有变化，比如添加列、删除列或修改列定义，可以使用ALTERTABLE语句实现。•ALTERTABLE语句可以对表添加列、删除列、修改列的定义、定义主码、外码，也可以添加和删除约束。修改表结构语法ALTERTABLE表名[ALTERCOLUMN列名新数据类型]|[ADD[COLUMN]列名数据类型|[DROPCOLUMN列名]|[ADDPRIMARYKEY（列名[，…n]）]|[ADDFOREIGNKEY（列名）REFERNECES表名（列名）]示例•例2．为SC表添加“修课类别”列，此列的定义为：XKLBchar(4)ALTERTABLESCADDXKLBchar(4)NULL示例•例3．将新添加的XKLB的类型改为char(6)。ALTERTABLESCALTERCOLUMNXKLBchar(6)示例•例4．删除Course表的Period列。ALTERTABLECourseDROPCOLUMNPeriod指数据的正确性、有效性和相容性然而大多数情况下，我们所提到的完整性，实际是指完整性规则为保证完整性，数据应该满足的约束条件，又称为完整性约束完整性的原义关系模型中的完整性•关系完整性•在关系模型中的地位•三要素之一（关系,关系完整性,关系操作）•三种类型•实体完整性；•参照完整性；•用户定义完整性；•三种关系完整性规则•解决现实世界的三个问题•如何保证一个实体是可识别（区分）的•方法：实体完整性•如何保证能够从一个实体找到另一个相关联的实体，而不会出现找不到的情况•方法：参照完整性•如何保证用于描述实体的属性，其取值是合理的•方法：用户定义完整性三种关系完整性规则•实体完整性•目的：保证每个元组（所代表的实体）是可标识（区分）的•规则：元组在主码的每个属性上取唯一值，且不能为空•意义：•关系往往对应一个实体集，这时关系中的每个元组对应一个实体•现实世界中的实体是可区分的，关系中的元组也必须是可区分的•主码起到唯一标识（区分）每个元组的作用，所以它必须是唯一的，同时也是非空的三种关系完整性规则•三种关系完整性规则要点：•如果一个关系的主码由多个属性构成，那么每个属性都不能取空值三种关系完整性规则•参照完整性复习：外部码•外部码•定义：关系R1中的单个属性或属性集合，对应另一个关系•R2的主码•R1称为参照关系，R2称为被参照关系•来源：由联系集转化而来，反映实体（元组）之间的联系复习：外部码三种关系完整性规则参照完整性•目的：保证外码引用的是一个有效的主码•规则：如果关系R的外部码对应关系S的主码，则R每个元组在•外部码上的取值必须满足：或者等于空值或者等于某个对应的主码值（S某个元组的主码值）•意义：•在被参照关系S中，一个主码值代表所在的元组（一个实体）•在参照关系R中，元组的外部码•要么取空值，表示不和任何S元组（实体）存在联系•要么取S的某个主码值，表示表示联系是存在的，且涉及的是•这个主码值所代表的S元组（实体）•如果违反以上两种情况，表示联系涉及了不存在的实体，这是不允许的思考•Thinking：•根据外部码取值的可能性，我们在定义外部码的属性类型时，应遵循什么原则?三种关系完整性规则•用户定义完整性•目的：保证实体属性的取值有效，等等•规则：用户根据具体的应用环境定义•例如•年龄的取值范围为0到200，性别只能是“男”或“女”•职工编号是4位整数•意义：•反映了现实世界的真实情况，即实体的属性必须满足要满足这些条件，反映了程序编制的要求思考•Thinking•什么操作会破坏关系完整性。查询操作，还是修改操作?课堂练习•依次执行如下操作，哪些能够成功？•1．零件关系：添加(3,绿,null)•2．供应商关系：添加(null,四化,广州)•3．供应商关系：添加(E,北电,广州)•4．零件关系：修改(2,白,A)为(2,黑,F)•5．供应商关系：删除(A,红星,北京)•6．零件关系：修改(3,蓝,B)为(3,蓝,E)完整性控制概述•完整性控制•目的：保证数据的正确性、有效性和相容性•由DBMS负责，包括两方面•完整性规则的定义：通过SQL•（运行时）完整性规则的检查：通过专门的“完整性子系统”主码约束•规则：主码值不允许空，也不允许出现重复•定义：在创建或修改关系的定义语句中，用PRIMARYKEY关键字声明主码的同时，即定义了一个主码约束createtable关系名(……[,primarykey(属性{,属性})]……)主码约束•例：createtable选修(学号VARCHAR(10),课程号VARCHAR(10),……,primarykey(学号,课程号),……)唯一约束•规则：对指定的一个属性或属性组合，不允许出现重复值•定义：在创建或修改关系的定义语句中，用Unique关键字声明createtable关系名(……[,unique(属性{,属性})]……)主码约束•例：createtable学生(……,身份证号VARCHAR(15),……,unique(身份证号),……)外部码约束•规则：外部码要么取空值，要么取对应的某个主码值•定义：在创建或修改关系的定义语句中，用FOREIGNKEY关键字声明一个外部码的同时，即定义了一个外部码约束createtable表名(……{,foreignkey(属性名{,属性名})references关系名(属性名{,属性名})ondelete参照动作onupdate参照动作