SQL语句之表的操作

妖狐艹你老母 2022-05-24 03:26 296阅读 0赞

> 本节主要说明基本表的定义，删除 与修改  
> 用到的命令主要是SQL语句中数据定义相关的指令  
> 即：create drop alter 只有他们才可以对表进行修改

### 1.0 定义基本表（creat） ###

基本格式为：  
create table 表名（  
列名 数据类型 列级完整性约束，  
列名 数据类型 列级完整性约束，  
。。。，  
表级完整性约束  
）

#### 创建一个student 表 ####

create table student (Sno varchar(10) primary key, Sname varchar(10), Sex varchar(5), Sage smallint, Sdept char(10) );

这就创建了有个学生表 表的内容有  
学号  
学生姓名  
性别  
年龄  
专业

如图：  
![这里写图片描述][70]

primary key 的意思是这个属性是主键

语句1 就是表的创建语句

语句2 是表的查询语句  
因为我们只创建表 没有添加数据 所以查询结果是空

语句3 是显示表结构

Sno varchar(10) primary key, 这句话中 Sno 是列名 然后是这个列对应的字段类型 然后是列级完整性约束条件  
其中 约束条件可有可无 但是要有主键

#### 创建一个带外键的表 Course ####

create table Course( Cno char(10) primary key, Cname char(40) not Null, /*课程名*/ Cpno char(10), /*选修课*/ foreign key(Cpno) references Course(Cno), Ccredit smallint );

上述定义说明 参照表和被参照表可以是同一个表

![这里写图片描述][70 1]

sql语言 是不区分大小写的

### 接下来我们建立一个学生选课表SC ###

create table SC(. Sno char(10), Cno char(10), Grage smallint, primary key(Sno,Cno),/*主键是两个属性构成 必须作为表级完整性约束定义*/ foreign key(Sno) references Student(Sno), foreign key(Cno) references Course(Cno) );

![这里写图片描述][70 2]

这个SC的表 叫做关系表 一般来说可以用来做表与表之间多对多关系的映射

<table> 
 <thead> 
  <tr> 
   <th>数据类型</th> 
   <th>含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>char(n) character(n)</td> 
   <td>长度为n的定长字符串</td> 
  </tr> 
  <tr> 
   <td>varchar(n) var character(n)</td> 
   <td>度为n的变长字符串</td> 
  </tr> 
  <tr> 
   <td>clob</td> 
   <td>字符串大对象</td> 
  </tr> 
  <tr> 
   <td>blob</td> 
   <td>二进制大对象</td> 
  </tr> 
  <tr> 
   <td>int integer</td> 
   <td>长整型 4字节</td> 
  </tr> 
  <tr> 
   <td>smallint</td> 
   <td>短整型 2字节</td> 
  </tr> 
  <tr> 
   <td>bigint</td> 
   <td>大整数 8字节</td> 
  </tr> 
  <tr> 
   <td>number(p,d)</td> 
   <td>定点数 长度为p 小数部分有d位数字</td> 
  </tr> 
  <tr> 
   <td>decimal(p,d) dec(p,d)</td> 
   <td>同上</td> 
  </tr> 
  <tr> 
   <td>real</td> 
   <td>取决于机器精度的单精度浮点数</td> 
  </tr> 
  <tr> 
   <td>double precision</td> 
   <td>取决于机器精度的双精度浮点数</td> 
  </tr> 
  <tr> 
   <td>float(n)</td> 
   <td>可选精度的浮点数 精度至少是n位数字</td> 
  </tr> 
  <tr> 
   <td>Boolean</td> 
   <td>逻辑布尔量</td> 
  </tr> 
  <tr> 
   <td>data</td> 
   <td>日期 包括年月日 格式 yyyy-mm–dd</td> 
  </tr> 
  <tr> 
   <td>time</td> 
   <td>时间 包含一日的时分秒 格式：HH:MM:SS</td> 
  </tr> 
  <tr> 
   <td>timestamp</td> 
   <td>时间戳</td> 
  </tr> 
  <tr> 
   <td>interval</td> 
   <td>时间间隔类型</td> 
  </tr> 
 </tbody> 
</table>

### 2.0 修改基本表（alter）程序员最烦的事之一 ###

表结构不稳定 需求变更 所以设计表的时候尽量长点脑子 不要经常修改  
基本格式为

alter table 表名  
\[  
add column 新列名 数据类型 完整性约束  
add 表级完整性约束  
drop column 列名 cascade|restrict  
drop constraint 完整性约束名 cascade|restrict  
alter column 列名 数据类型

\]/\* 实际使用没有中括号\*/

说明：

1.  add 用来增加新的列 新的列级完整性约束 新的表级完整性约束
2.  drop 用来删除表中的列 如果指明了cascade 则会删除了其他引用该列的队像，如果指明了restrict 则如果该列被其他队形引用，则会被拒绝删除
3.  drop constraint 用来删除指定的完整性约束条件的
4.  alter column 修改原有列的定义

**eg1：** 向学生表中添加入学时间 其数据类型是时间类型  
alter table Student add column Sentrance data;

![这里写图片描述][70 3]

新增加的列全部为空值

其中 column 可有可无的

**eg2:** 删除列  
![这里写图片描述][70 4]

![这里写图片描述][70 5]

**eg3：**修改列的数据类型 modify  
alter table student modify column Sentrance int; /*把原来的Sentrance 列的属性变为int*/

![这里写图片描述][70 6]

**eg4:** 同时改变列名和数据类型 change

alter table student change Sentrance sen varchar(10);

![这里写图片描述][70 7]

**eg5：** 增加完整性约束条件

alter table student add unique(sen);

![这里写图片描述][70 8]  
**eg6:** 增加非空约束

alter table student modify column sen int not null;

![这里写图片描述][70 9]

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

##### 删除唯一约束 #####

alter table student drop index sen;

因为上面的例子中我们给列 sen 添加了唯一约束 unique 这样我们在插入同样的数据的时候就会报错，

所以我们需要删除唯一约束 也就是上面的语句

接下来我们通过建立stu 表 不设主键

create table stu(  
Sno char(10),  
Sname char(10),  
Sex char(10),  
Sage smallint(6),  
Sdept char(10),  
sen char(10));  
表结构如图：  
![这里写图片描述][70 10]

##### 添加主键约束 添加非空约束 #####

alter table stu modify Sno char(10) not null;

alter table stu modify Sno char(10) primary key;

![这里写图片描述][70 11]

##### 把student 表的数据导入新建的表 因为表结构一样 #####

insert into stu select \* from student;

![这里写图片描述][70 12]

##### 删除student 表的数据 #####

delete from student；

##### 给student 添加外键 #####

alter table student add foreign key(sen) references Course(Cno);  
![这里写图片描述][70 13]

外键添加成功

##### 外键删除 #####

![这里写图片描述][70 14]

#### stu表的数据插入student 同上 ####

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

### 3.0 删除表 ###

基本语法

drop table 表名 \[restrict|cascade\]

restrict 如果有对象引用了这个列或者表 那么就不能删除这个表 cascade就是可以删除同时相关的依赖也都删除，例如SC表的主键和student关联了 那么就不能删除了 ，如果采用 drop table student cascade ；进行删除  
那么就会删除SC表 默认的删除是drop table student restrict ；restrict 可以不写

注意mysql 不支持删除有外键关联的表  
![这里写图片描述][70 15]

[70]: /images/20220524/e67bc76e9f504da78bf543ecb75970bb.png
[70 1]: /images/20220524/f55590e5aa3b417d91d173eb10998767.png
[70 2]: /images/20220524/328c91c3676046eab045ce284d5e2b57.png
[70 3]: /images/20220524/0e6034d1f82d41e5b4d145635598e35a.png
[70 4]: /images/20220524/caba34d38acb4a70b78de15d55f8be62.png
[70 5]: /images/20220524/6a3c3af3eff848dbb151d2c189633b53.png
[70 6]: /images/20220524/81ed74f0c58b4f9899db4f2152adb1a6.png
[70 7]: /images/20220524/6bf2cc587a584b3ca68f20b034a4e421.png
[70 8]: /images/20220524/139ab8d6af064326bb85f2fb27cedb84.png
[70 9]: /images/20220524/303d8f182e5f4ae6a5c5ae0795771ab0.png
[70 10]: /images/20220524/ac7a9ba9c35f48369c14e1dad540bee1.png
[70 11]: /images/20220524/8a1e131b5aee4a6f9aa687926b06c743.png
[70 12]: /images/20220524/49591295365f4c4a9fd5372adc43bc88.png
[70 13]: /images/20220524/29ec46b272cd4c009870aa9585b773d3.png
[70 14]: /images/20220524/a1d2abe03f7b4627adba647d80d5f000.png
[70 15]: /images/20220524/b12df30b353b42fe86a49d0e0d2ef072.png