1-3 SQL与建立关系型数据表

本文涉及的产品
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云原生数据库 PolarDB PostgreSQL 版,企业版 4核16GB
推荐场景:
HTAP混合负载
云原生数据库 PolarDB MySQL 版,通用型 2核4GB 50GB
简介:

1-3  SQL与建立关系型数据表

u  了解关系型数据库的完整性基本理论
u  掌握通过 SQL 语句创建基本表
u  掌握通过 SQL 语句修改基本表的模式结构
u  掌握通过 SQL 删除基本表

1-3-1 关系型数据库的完整性理论

SQL Structured Query Language 结构化查询化语言)不仅仅具有查询功能。它是一个通用性的数据库操纵、定义语言,被普遍使用在信息系统的应用中。 SQL 的数据定义语句首先是针对基本表、视图和索引的创建、删除和修改,具体的定义包括:
1-4   主文件组和用户定义文件组的差异

 

SQL 在建立基本表信息的时候,必须按照关系型数据库完整性理论建立基本表,该理论包括:实体完整性,参照完整性和用户定义完整性。各个完整性内容包括:

1. 实体完整性

即主码的非空性原则,如果主码为空则意味着这条元组不知道或者无意义。

2. 参照完整性

要求外码的填写或者为空,表示尚无规定的信息与此条主码信息对应;如果非空,则填充的外码信息必须出自父表的主码信息集合。

3. 用户定义完整性

由用户根据具体的信息逻辑所定义的信息填充方案,如性别只能够是男或者女
 

1-3-2 由一个案例所见到的关系数据理论

1 基本表情况说明

这里给出一个学校数据库的三个基本表,用在以后的实例中说明 SQL 语句的各种用法。
1 )学生表: Student(Sno,Sname,Ssex,birthday,class)
Student 由学号 (Sno) 、姓名 (Sname) 、性别 (Ssex) 、生日 (birthday) 、班级 (class) 五个属性组成,其中 Sno 为主码。
2 )课程表: Course(Cno,Cname,Cpno,Ccredit,tno)
Course 由课程号 (Cno) 、课程名 (Cname) 、先修课号 (Cpno) 、学分 (ccredit) 、任课教师号 (tno) 五个属性组成,其中 Cno 为主码。
3 )学生选课表: SCore(Sno,Cno,Grade
SC 由学号 (Sno) 、课程号 (Cno) 、成绩 (Grade) 三个属性组成,主码为 (Sno,Cno)
4 )教师表: Teacher(tno,name,sex,prof,depart,salary
Teacher 由教师号 ( tno) 、教师名 (name) 、性别 (sex) 、职称 (prof) 、部门 (depart) 、薪酬 (salary) 六个属性组成,主码为 (tno)
 表1-5  student基本表表                        
______________________________________________
Sno Sname Ssex Birthday Class
103 李勇 男 1982-3-4 95031
105 刘晨 女 1983-5-8 95031
107 王明 女 1983-12-21 95033
109 赵明棋 男 1982-11-4 95033
110 张惠 女 1982-9-26 95033
______________________________________________
1-6  sc基本表
______________________________________________
Sno Cno Grade
103 322 92
103 343 85
105 105 93
105 245 63
107 166 78
______________________________________________
表1-7  course基本表
______________________________________________
Cno Cname Cpno Ccredit Tno
322   数据库 166 4 804
343   数学  2 
105   信息系统  322 4 825
245   操作系统  166 3 835
166   数据结构  888 4 804
324   数据处理  166 2 856
888   C语言 343 4 856
______________________________________________
表1-8  Teacher基本表
______________________________________________
Tno Name Sex Birthday   Prof    Depart Salary
804 李诚    男      1958-12-2  副教授  计算机系     3400
825 王萍   女      1972-5-5   助教    计算机系     1230
835 刘冰    女      1977-8-14  助教    电子工程系   1142
856 张旭    男      1969-3-12  讲师    电子工程系   2450
______________________________________________
实验:可视化状态下建立基本表实验
第一步:启动 SQL Server2005 Management Studio ,进入管理平台界面后,鼠标右击对象资源管理器中的数据库,选择“新建数据库”后,新建数据库 school
第二步:在对象资源管理器中用鼠标右键单击新建数据库 school ,在弹出的快捷菜单中选择   “新建表”,如表 1-13
第三步:按照基本表说明内容依次建立四张基本表,需要说明的是针对于每一张基本表需要认真规定每个属性的名称和数据类型,见图 1-14 所示。另外对于主键以及外键的设计必须按照数据库概念设计模型进行规划设计后才可以实施,具体设计的方法和步骤见数据库模式设计篇章。
    
 
               1-13   新建基本表             1-14   定义基本表的属性名称和数据类型
第四步:在对象资源管理器 school 数据库的树形结构中找到数据库关系图一项,用鼠标右键单击后,于弹出的快捷菜单中选择“新建关系图”,见图 1-15 所示。
第五步:在弹出的“添加表”中将全部的表都选中,单击添加按钮后,将全部表添加到新建关系图之中,见图 1-16 所示。
 
1-16   添加基本表和基本关系图
第六步:在基本关系图之中,用鼠标左键点击某张表的外键,将之拖至父表的主键上,从而建立起主、外键的关联。需要特别说明的是对于基本表 course ,由于 cpno 是先修课程号码,必须来自 course 表中的 cno 主码集合,因此 cpno 属于单表自映射的主外键关系。见图 1-17 所示。在建立完该关系图后,既可以将具体数据录入数据表之中。
   
           1-15   新建数据库关系图图                      1-17   建立主外键之间的关系

1-3-3 通过SQL创建基本表

通过 SQL 可以更便捷的创建基本表,同时也可以更好的反映数据库完整性的思维方式。建立基本表语句格式见下:
create  table    表名(
列名    数据类型   [default   缺省值 ]  [not null][ UIQUE]
[ ,列名   数据类型   [default   缺省值 ]  [not null]]
……
[ primary  key (列名  [ ,列名 ] … ]
[ foreign  key  (列名  [ ,列名 ] …  references   表名   (列名  [ ,列名 ] … ]
[ check (条件) ]

1 SQL建立基本表注意事项:

(1)     表名是所要定义的基本表名称,它可以由一个或多个属性列组成。
(2)     建立表的时候,还可以定义与该表有关的关系性约束性条件,这些关系性约束性条件被存入系统的数据字典中。当用户操作数据库中表内的数据的时候,由 DBMS 自动检查该项操作是否违背这些关系性约束条件。
(3)     语句定义说明:
q Default :设置该列的缺省值,当插入数据,没有指定该列的值的时默认取该值。
q UNIQUE :唯一性约束,该列不允许取重复的值。
q NOT NULL :该列不允许取空值。
q Primary Key :主键约束。
q foreign  key  本表中的外码  .references   对应主表中的主键:外键约束。
q CHECK :用户自定义的约束条件,根据实际需要而定。

2 SQL Server 2005的主要数据类型

    SQL Server 2005 的基本数据类型包括有:
q char n ):固定长度的字符串,此数据类型可存储 1~8000 个定长字符串,字符串长度在创建时指定;如未指定,默认为 char(1) 。每个字符占用 1byte 存储空间。
q varchar n ):可变长字符串。(由于该类型可根据实际录入的数据字节量存储,因此在一定程度上较 char 要节省磁盘空间,故对于字符串类型数据建议使用该种类型。)此数据类型可存储最大值为 8000 个字符的可变长字符串。可变长字符串的最大长度在创建时指定,如 varchar(50) ,每个字符占用 1byte 存储空间。
q int :整数,此数据类型存储 -2147483648~2147483647 的整数,占用 4bytes 存储空间。
q smallint :小整数类型,此数据类型存储 -32768~32767 的整数,占用 2bytes 存储空间。
q numeric p d ):定点数,小数点左边 p 位,右边 q 位。
q float :此数据类型存储 1~53 的可变精度的浮点值, n 1~24 ,占用 4bytes 存储空间; n 25~53 ,占用 8bytes 存储空间。
q real :此数据类型存储 -3.40E+38~-1.18E-38 的负数和 1.18E~3.40E+38 的正数。占用 4bytes 存储空间。
q datetime :此数据类型存储从 1753 1 1 9999 12 31 的日期。占用 8bytes 存储空间。
q smalldatetime :此数据类型存储从 1900 1 1 2079 6 6 的日期。占用 4btyes 存储空间。
q time :时间(小时、分、秒)。
q money :此数据类型存储 -922337203685477.5808~922337203685477.5807 的货币值,精确到小数后 4 位。占用 8bytes 存储空间。
q binary :此数据类型存储 1~8000 个字符的二进制数据,其指定长度即为占用的存储空间。
实验:建立基本表的SQL实验
--1:建立基本表student
        CREATE TABLE Student
        (Sno varchar (5) NOT NULL UNIQUE,  
         /*Sno取值唯一,不许取空值*/
         Sname varchar (20) UNIQUE,                     
         Ssex char(1),
         Sage INT,
         Sdept varchar (15));
--2:建立基本表sc
     CREATE TABLE SC
        (Sno varchar (5) NOT NULL UNIQUE,  
         Cno varchar (4) default '1', /*cno默认值为1*/
         GRADE Smallint,                     
         Primary Key(Sno,Cno),
         Foreign Key (sno) References  student(sno),
         /*Sno为当前表的外键,对应于student表的主键*/
         Foreign Key (Cno) References  Course(Cno),
         Check(Grade between 0 AND 100));
         /*grade成绩属性在0100之间,用户定义完整性*/
--3:建立基本表student
        CREATE   TABLE   STUDENT
         (SNO varchar(4),
          SNAME  varchar (8)  NOT NULL,
          SAGE   Smallint,
          SSEX   varchar (1),
          sdept varchar (8),
          PRIMARY KEY (SNO), /*Sno为当前表的主键*/
          CHECK (SEX=0 OR  SEX=1));  /*Sex只能够取0或者1,用户定义完整性*/
--4:建立基本表course
         CREATE   TABLE  COURSE
         (CNO  varchar (4),
          CNAME varchar (10)  NOT NULL,
          CPNO  varchar (4),
          CCREDIT  SMALLINT, PRIMARY KEY(cno));
1-3-4 SQL修改基本表关系结构
建立完基本表后,由于关系数据库的模式设计需要,或者项目逻辑关系的变化,经常需要进行基本表逻辑关系的修改。修改基本表的语法见下:
alter table  表名
          [add < 新列名 > < 数据类型 > [ 完整性约束 ]]   -- 这里可以增加新的属性
          [drop < 完整性约束名 >]  -- 删除列
          [alter column < 列名 > < 数据类型 >] -- 修改列属性
  注意:
    增加完整性约束可以是Add Constraint 数据库中约束名称 [完整性约束条件],如果是用户定义完整性约束可以是check()语句。
实验:修改基本表的 SQL 实验
--1:向student表中增加入学时间列,其数据类型为日期型
 ALTER TABLE Student ADD Scome datetime;
--2:将年龄的数据类型改成为短整型,需注意的是修改原有的列可能会破坏已有的数据
alter table student alter column sage smallint;
--3:重新设定student的主键是sno
 ALTER TABLE Student ADD PRIMARY KEY(Sno);
--4:去除掉sage 
ALTER TABLE Student drop column sage;
--5:给教师表增加一项教研室号码int类型,并且不可以大于
Alter table teacher
Add 教研室号码int Constraint fk1 check(教研室号码<20)
--6:将刚才的教研室号码约束删掉后再将教研室号删掉
Alter table teacher drop fk1
Alter table teacher drop column 教研室号码
--7:更改一条外键的约束
alter table sc add constraint fk3 foreign key(sno) references student(sno)
--8:删除一个属性列
CREATE TABLE doc_exb ( column_a INT, column_b VARCHAR(20) NULL)
     ALTER TABLE doc_exb DROP COLUMN column_b /*删除列*/

1-3-5 SQL删除基本表

删除基本表的 SQL 语法比较简单,基本格式为: DROP TABLE < 表名 > 。例如删除学生表 student 就是 DROP TABLE Student 即可。
  注意:
q         撤消基本表后,基本表的定义、表中数据、索引都将被删除。
q         Drop Table不能够除去由foreign key约束引用的表,因此必须先除去引用的 foreign key约束条件。
小问题:
对关系型数据库系统而言,删除基本表的顺序是先删除父表还是先删除子表呢?
    

 





本文转自 qianshao 51CTO博客,原文链接:http://blog.51cto.com/qianshao/347764,如需转载请自行联系原作者

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
2月前
|
SQL 存储 弹性计算
GaussDB SQL调优:建立合适的索引
GaussDB SQL调优:建立合适的索引
26 0
|
SQL 关系型数据库 MySQL
省市区管理sql数据表设计、以及全国省市区全部最新数据
省市区管理sql数据表设计、以及全国省市区全部最新数据
1467 0
省市区管理sql数据表设计、以及全国省市区全部最新数据
|
2月前
|
SQL
现有用户成就统计需求,每个用户有多个成就,某一个成就会被多人拥有,写出数据表设计方案,用一条sql查出每个成就(B.ach_name)下的男生(sex=0)和女生(sex=1)分别有多少?
现有用户成就统计需求,每个用户有多个成就,某一个成就会被多人拥有,写出数据表设计方案,用一条sql查出每个成就(B.ach_name)下的男生(sex=0)和女生(sex=1)分别有多少?
53 0
|
1月前
|
SQL 关系型数据库 MySQL
sql如何建立数据库
在SQL中,建立数据库通常涉及使用“CREATE DATABASE”语句。以下是一个基本的步骤指南,以及一个示例SQL命令,用于创建一个新的数据库。 ### 步骤指南 1. **确定数据库
|
2月前
|
SQL 存储 弹性计算
GaussDB SQL调优:建立合适的索引
GaussDB SQL调优:建立合适的索引
|
7月前
|
SQL 数据库
SQL Server 创建和管理数据表
SQL Server 创建和管理数据表
89 1
|
2月前
|
SQL 安全 网络安全
IDEA DataGrip连接sqlserver 提示驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接的解决方法
IDEA DataGrip连接sqlserver 提示驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接的解决方法
254 0
|
10月前
|
SQL 数据库 C语言
使用SQL语句实现数据表管理
本次实验主要介绍如何在RDS-SQLServer数据库中使用SQL语句实现数据表管理。
193 0
|
SQL 存储 安全
详解:驱动程序无法通过使用安全套接字层(SSL)加密与SQL Server 建立安全连接。
详解:驱动程序无法通过使用安全套接字层(SSL)加密与SQL Server 建立安全连接。
3392 0