数据库的建立、增、删、改、查

前言：一名计算机专业的在读大学生，博客主要对自己所学习的计算机专业基础进行总结，希望在加强自身基础的同时可以帮到你们！
  
目录
一、模式定义与删除
1、定义模式
2、向数据库中添加文件或删除文件
3、删除模式
二、基本表的定义、删除与修改
1、定义基本表结构
六大约束
2、数据类型
常用字符型
常用整型
浮点数
日期、时间类
3、模式与表
4、修改基本表
（1）向表中添加元组
（2）删除表中的元组
（3）添加约束
（4）修改约束
（5）删除约束
（6）修改数据类型
5、删除基本表
三、数据查询 select
1、单表查询
（1）、指定列 select sno,sname
（2）、全部列 
（3）、计算列
（4）、换名列
（5）、指定显示多少元组
（6）、带where 限制条件的查询
 聚合函数的使用：
（1）count（*）查询表中元组个数
（2）统计一列中的元素个数count（[dietinct] 列名）
（3）统计一列的总和 sum（[dietinct] 列名）
（4）计算一列的平均值avg（[dietinct] 列名）
（5）求一列的最大值max（[dietinct] 列名）
（6）求一列的最小值min（[dietinct] 列名）
 order by、group by的使用：
 order by、group by使用时机即注意事项总结：
 where子句和having短语
2、连接查询
（1）谓词连接查询
（2）内连接
（3）自身连接
 （4）外连接
3、嵌套查询
（1）带in谓词的子查询
 （2）带有运算符的子查询
（3）带有ANY或ALL谓词的比较类子查询
 （4）带有exists 谓词的子查询

---

一、模式定义与删除

1、定义模式

数据库的组成：
1)数据类文件：管理数据
   1、 主数据文件 有且唯一  .mdf 
   2、 辅助数据文件 可以有，可以没有，可以有多个  .ndf
2)日志类文件：备份数据  至少一个 .ldf
每一个文件有五个属性：
1 name:逻辑名称,字符型数据 name=’aaaa111’
标准化命名：数据库名字+_+data(log)+序号（类型）
2 filename:路径
标准化命名：盘符（例如d:\zs\）+逻辑名称+文件类型名
3 size:文件初始大小 size=5mb,
4 maxsize:最大大小 maxsize=20mb或maxsize=unlimited
5 filegrowth：文件增长方式filegrowth=5%或filegrowth=2mb
示例：建立名叫study的数据库如下

1. create database study
2. on
3. (name='study_data01',
4. filename='D:\大\数据库\study_data01.mdf',
5. size=5MB,
6. maxsize=20Mb,
7. filegrowth=20%),
8. (name='study_data02',
9. filename='D:\大\数据库\study_data02.ndf',
10. size=5MB,
11. maxsize=20Mb,
12. filegrowth=20%),
13. (name='study_data03',
14. filename='D:\大\数据库\study_data01.ndf',
15. size=5MB,
16. maxsize=20Mb,
17. filegrowth=20%)
18. log on
19. (name='study_log01',
20. filename='D:\大\数据库\study_log01.ldf',
21. size=5MB,
22. maxsize=20Mb,
23. filegrowth=20%),
24. (name='study_log02',
25. filename='D:\大\数据库\study_log02.ldf',
26. size=5MB,
27. maxsize=20Mb,
28. filegrowth=20%)

复制代码

                                                    

2、向数据库中添加文件或删除文件

主数据文件有且唯一，固不可添加，不可删除

• 添加数据类文件：管理数据
  

1. alter database study
2. add file
3. (name='study_data04',
4. filename='D:\大\数据库\study_data04.ndf',
5. size=5MB,
6. maxsize=20Mb,
7. filegrowth=20%),
8. (name='study_data05',
9. filename='D:\大\数据库\study_data05.ndf',
10. size=5MB,
11. maxsize=20Mb,
12. filegrowth=20%)

复制代码

• 添加日志类文件：备份数据
  

1. alter database study
2. add log file
3. (name='study_log03',
4. filename='D:\大\数据库\study_log03.ldf',
5. size=5MB,
6. maxsize=20Mb,
7. filegrowth=20%)

复制代码

• 删除辅数据文件：
  

1. alter database study
2. remove file study_data05

复制代码

• 删除日志文件：
  

1. alter database study
2. remove file study_log03

复制代码

3、删除模式

1. drop schema<模式名> <cascade|restrict>

复制代码

   其中cascade（级联）和restrict（限制）两者必选其一。
  cascade 表示删除模式的同时把该模式中所有的数据库对象全部删除；选择了restrict表示如果该模式中已经定义了下属数据库对象（如表、视图），则拒绝该删除语句的执行
  二、基本表的定义、删除与修改

1、定义基本表结构

1. CREATE TABLE <表名>
3.       （<列名> <数据类型>[ <列级完整性约束条件> ]
5.       [，<列名> <数据类型>[ <列级完整性约束条件>] ] …
7.       [，<表级完整性约束条件> ] ）；

复制代码

六大约束

• 主码约束 primary key     唯一非空
  
• 唯一约束 unique            可以没有，也可以有多个
  
• 非空约束 not null           可以省略
  
• 默认约束 default            加了该约束则代表非空
  
• 外码约束 foreign key
  
  
  • 表内外码
    
    
    
    • 同表不同属性
      
    • 其中一个属性取值来源另一个属性
      
    • foreign key (Cpno) reference  Course(Cno)
      
    
    
  • 表间外码
    
    
    
    • 不同表之间的相同属性
      
    • 一个来源另一个
      
    • foreign key (Sno) references Student(Sno)
      
    
    
  
  
• 检查约束 check （域，数据取值范围）
  

2、数据类型

• 常用字符型
  
  
  • 定长字符串 char（n）  character（n）
    
  • 变长字符串 varchar（n） charactervarying（n）
    
  
  
• 常用整型
  
  
  • 长整数 int integer
    
  • 短整型 smallint
    
  • 大整型 bigint
    
  
  
• 浮点数
  
  
  • flot（n）
    
  
  
• 日期、时间类
  
  
  • date   YYYY-MM-DD   加  '  '
    
  • time   HH：MM：SS
    
  
  

示例：在数据库study下建立 Student表、Course表、SC表

1. create table Student
2. (Sno char(9) primary key,
3. Sname char(12) unique,
4. Ssex  char(2) default('男'),
5. Sage  smallint not null,
6. Sdept char(14)        );
8. create table Course
9. (Cno char(6) primary key,
10.   Cname char(12)unique,
11.   Cpno char(6),
12.   Ccredit smallint not null,
13.   foreign key(Cpno) references Course(Cno));
14.   
15.   create table SC
16.   (Sno char(9),
17.    Cno char(6),
18.    Grade smallint,
19.    primary key(Sno,Cno),
20.    foreign key (Sno) references Student(Sno),
21.    foreign key (Cno) references Course(Cno));

复制代码

3、模式与表

 概念：每一个基本表都属于某一个模式，一个模式包含多个基本表;
当用户创建基本表时若没有指定模式，系统根据搜索路径（search path）来确定该对象所属的模式。 搜索路径包含一组模式列表，关系数据库管理系统会使用模式列表中第一个存在的模式作为数据库对象的模式名。若搜索路径中的模式名不存在，系统将会给出错误。
4、修改基本表

以修改数据库study下表Student为例：
（1）向表中添加元组

   alter table 表名
  add 列名  数据类型

1. alter table Student
2. add birthday date

复制代码

修改前：                                                                        修改后：
 
 注意：此时不能加非空约束 
（2）删除表中的元组

   alter table 表名
  drop column 列名
  删除Sdept

1. alter table Student
2. drop column Sdept

复制代码

删除前：                                                                删除后：

（3）添加约束

   alter table Student
add constraint 约束名  for 元组
  给你年龄添加默认18岁的默认约束

1. alter table Student
2. add constraint Sage_1 default(18) for Sage

复制代码

给年龄添加域，即检查属性

1. alter table Student
2. add constraint Sage_2 check(Sage>=15)

复制代码

（4）修改约束

修改约束先删除约束，再重新添加约束即可
（5）删除约束

   alter table Student
drop constraint

1. alter table Student
2. drop constraint Sage_2

复制代码

（6）修改数据类型

   alter table 表名
  alter column 列名 修改后的数据类型

1. alter table Student
2. alter column Sage smallint

复制代码

修改前：                                                       修改后：

 注意：在修改类型长度时，只能改大不能改小（数据丢失）

5、删除基本表

1. drop table<表名>

复制代码

三、数据查询 select

1、单表查询

（1）、指定列 select sno,sname

1. select Sno,Sname,Sage
2. from Students

复制代码

 查询结果：


（2）、全部列 

1. select *
2. from Students

复制代码

 查询结果：
（3）、计算列

1. select Sname as '姓名','出生年份'=2022-Sage
2. from Students

复制代码

查询结果：

（4）、换名列

1. select Sname as '姓名',Sage as '年龄',Ssex as '性别'
2. from Students

复制代码

查询结果：

（5）、指定显示多少元组

1. select top 3 *
2. from Students

复制代码

（6）、带where 限制条件的查询

一、准确查找
eg：年龄小与21的

1. select Sname as '姓名',sage as '年龄',Ssex as '性别'
2. FROM Students
3. WHERE Sage<21

复制代码

2、连接查询

    连接查询：查询的结果或条件同时涉及多个表的查询
（1）谓词连接查询

   from 后的表名通过  ","  连接，  表和表连接要保证两表有公共列
  where 后跟连接条件
   eg：查找学生的个人信息和选课信息

1. select Sno,Sage
2. from Students
3. where  Sage between 15 and 18

复制代码

（2）内连接

   from后表名通过 join 连接，中间表要放中间
  接连接条件写在 on后面 一个on只可接一个条件 
  顺序要和from后中表名对应（先内后外）
  格式：
  select 属性
  from 表名1 join 表名2 join 表名3
  on   表三和表二的连接条件（公共列）
  on  表一和表二的连接条件（公共列）
   eg：查找选修课程总学分超过5的，学生学号，姓名

1. select Sno,Sname,Faddress
2. from Students
3. where  Faddress in ('金州','芝加哥','克利夫兰','工地')

复制代码

（3）自身连接

   同一个表中查两次，
  from 后接同一表的不同别名
   eg：查找和学生表中李勇一个系的学生学号、姓名

1. select Sname as '姓名',sage as '年龄',Ssex as '性别'
2. FROM Students
3. where Sname like '%萌%'

复制代码

 （4）外连接

   左外连接 （左表为主表，右表为辅表，列出左表中所有元组，右表中没有出现的左边元组会将属性值设为空出现在查询结果中如下面例子的6、7）
  select  属性
  from 表名1 left outer jion  表名2
  on 表1和表2的连接条件
  
  右外连接 （列出右边关系表中所有元组）
  select  属性
  from 表名1 left outer jion  表名2
  on 表1和表2的连接条件
  
  完全外连接 （列出两个表中所有元组，没有的属性设置为空）
  select  属性
  from 表名1 left outer jion  表名2
  on 表1和表2的连接条件
  eg：下面依次完全外连接查询、左外连接查询、右外连接查询

1. select Sname as '姓名',sage as '年龄',Ssex as '性别'
2. FROM Students
3. where Sname like '杜%'

复制代码

3、嵌套查询

概念：
   一个SELECT-FROM-WHERE语句称为一个查询块
  将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询
  （1）带in谓词的子查询

   用in 或=做查询的连接
   Where 公共列 in (select 公共列
  In 可以无限替代=，反之则不成立
  子查询的结果有且唯一， in可以换成=，反之则不成立 
   eg：查询选修了数据库这门课程的的学生姓名

1. select Sname as '姓名',sage as '年龄',Ssex as '性别'
2. FROM Students
3. where Sname like '_小_'

复制代码

 （2）带有运算符的子查询

eg：查询成绩大于所选课程平均成绩的学生学号和课程号

1. select *
2. from Student
3. where Sname like '凡\_%'escape'\'

复制代码

（3）带有ANY或ALL谓词的比较类子查询

   any，all的选用
  满足子查询块的结果中任意一个条件用any
  满足所有条件则用all
  （条件容易满足用any，不容易满足用all）
   eg：1、查询年龄比cs系学生任意年龄小的非cs系得学生姓名、年纪
        2、查询选修课程1中成绩比选修课程2的所有成绩都高的学生学号、课程号、成绩

1. select *
2. from Student
3. where Sname like '%\_'escape'\'

复制代码

 （4）带有exists 谓词的子查询

    1、EXISTS谓词：行（元组）的连接存在量词
  带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。
  若内层查询结果非空，则外层的WHERE子句返回真值
  若内层查询结果为空，则外层的WHERE子句返回假值
  由EXISTS引出的子查询，其目标列表达式通常都用*，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义
  
  2. NOT EXISTS谓词
  若内层查询结果非空，则外层的WHERE子句返回假值
  若内层查询结果为空，则外层的WHERE子句返回真值
  Exists 连接：1不找公共列,2在最下层子查询块中写表之间的连接条件;3中间表要放最下层子查询块中
  eg：1、选修了1号课程的同学学号和姓名
        2、没有选修1号课程的学生学号和姓名

1. select COUNT(*) as '学生人数'
2. from Students

复制代码

连接查询总结：几种查询方式可以相互转换，
例如：带any的子查询可以转换为最小，最大来解决
eg：小于cs系中任意年龄的学生姓名、年龄
[code]//带any谓词的查询select Sname,Sagefrom Studentwhere Sage