SQL基础关键字

持续更新中 :-)

在SQL server 中关键字不区分大小写

目录

一、基本模式定义 

二、SQL单关系查询

三、表操作与视图

四、内连接外连接

---

        基本类型

• char(n): 具有用户指定长度n的固定长度的字符串
• varchar(n): 具有用户指定的最大长度n的可变长度的字符串
• nvarchar(n): Unicode编码存储，一个汉字两个字节，可最大存储n个汉字
• int：整数
• smallint：小整数
• numeric（p,d）: 具有用户指定精度的定点数，这个数有p位数字，小数点右边有d位数字。例如numeric（3，1）可以存储44.5
• real，double precision：浮点数与双精度浮点数，精度依赖于机器
• float(n): 精度至少为n位数字的浮点数
• smalldatetime的有效时间范围1900.01.01~2079.06.06
• datetime的有效时间范围1753.01.01~9999.12.31
• SQL Server中，smalldatetime精确到分钟，而datatime可以精确到3%秒（3.33毫秒）

  ---

  聚集函数

• 平均值:avg

• 最小值:min

• 最大值:max

• 总和:sum

• 计数:count

  ---

一、基本模式定义 

1.create table 命令来定义SQL关系

例如：在大学中有department（系）这样的关系

create table department
(dept_name varchar(20),
 building  varchar(15),
 budget    numeric(12,2),
 primary key (dept_name));

注意create table语句最后出现了分号。

2.主码primary key

用 primary key 关键字来标识主码

主码有时不只有一个属性

create table teaches 
(ID varchar(5),
 course_id varchar(8),
 sec_id varchar(8),
 semester varchar(8),
 year numeric(4,0),
 primary key(ID,course_id,sec_id,semester,year));

上面 teaches 教师关系中主码包含多个属性分别用逗号隔开，这些属性共同来区分不同的元组。

主码中的属性必须是唯一并且是非空的。

在上面那个 department 的例子中主码为 dept_name 它唯一的标识了某一个元组。

2.外码 foreign key(A1,A2,A3....An)reference s

在course关系中有

create table course
(course_id varchar(7),
 title varchar(50),
 dept_name varchar(20),
 credits numeric(2,0),
 primary key(course_id),
 foreign key(dept_name)references department);

在course这个关系中dept_name受到了来自department关系中的dept_name外码的约束。

如果缺失了这个约束就有可能某一门课程指定了一个不存在的系名。

3.非空not null

not null 代表一个属性不允许取空置，直接将其加在属性定义后即可。

name varchar(20)not null,

4.删除drop、delete

drop table r,表示从数据库中删除关系r的所有信息包括 r 所有的元组与 r 的模式

delete table r,表示从数据库中删除所有的元组但保留其关系

二、SQL单关系查询

1.单关系查询select...from...

select building
from department;

从department关系中查询building属性，注意语句结尾的分号。

2.单关系查询中的关键字 all 与 distinct

select distinct building
from department;

 在select 后加上distinct关键字表示在查询的结果中重复的building只会出现一次，如果是下面的写法表示的是不去除重复。all关键字不是必须的，因为当你什么都写的时候默认为all。

select all building
from department;

 3.select子句中可以含有运算符

select ID,name,dept_name,salary*1.1
from instructor;

在输出的结果中salary的值为先前值的1.1倍。这样的运算仅仅只是在输出结果中改变了值实际值未改变。

4.查询中的where子句

where子句允许我们选出在from子句中的结果满足关系的特定元组，实际上就是为select子句添加了修饰限定条件。

select name
from instructor
where dept_name='Music'and salary>7000;

在之前的例子中只是查询了关系中某一属性或多个属性的全部（不考虑重复的问题），where子句将属性进一步筛选，上述查询意思是：找出在Music系中工资超过7000的所有教师的姓名。

三、表操作与视图

1.插入insert

按照建表时属性列出的顺序全部插入

--例如向课程关系中插入数据
insert into course
values('001','Database Systems','computer',4);

按照任意属性对应顺序插入

insert into course(属性名)
values (对应属性名顺序的值);

在查询结果的基础上插入 

insert into instructor--在教师表中做插入
       select  ID,name,dept_name,8000--此行对应的是教师表中要插入的属性
       from student--来自学生表
       where dept_name='Music'and core>4.5;

上面例子的意思是我希望把一个来自音乐系的学分绩点大于4.5的学生作为教师插入到教师列表中，再插入过程中查询的属性与对应教师列表的属性要对应。

2.更新数据update

无条件的更新

--给所有的老师涨工资（涨的有点少哈）
update instructor
set salary=salary*1.05;

 有where子句的简单更新

--给那些工资少于七千的教师涨工资（比上面更精准了）
update instructor
set salary=salary*1.05
where salary<7000;

嵌套子查询的更新

--给那些低于平均工资的教师涨工资
update instructor
set salary =salary*1.05
where salary<(select avg(salary)--avg是聚集函数求平均值
              from instructor);

3.删除delete

删除整个元组

--将满足谓词P的元组从关系表r中删除
delete from r
where P;
--例如删除那些属于Finance系的教师的所有元组
delete from instructor
where dept_name='Finance';

嵌套查询的删除

delete from instructor
where dept_name in (select dept_name
                    from department
                    where building='Watson');
          

这个删除是从instructor关系中删除所有这样的教师元组，他们在位于Watson大楼的系里工作。 

4. 视图

视图的几个特点

• 从一个或几个基本表导出的表
• 数据库中只存放视图的定义而不存放视图对应的数据
• 视图是一个虚表
• 用户可以在视图上在定义视图

基本的创建视图如下： 

--建立学生系的视图
create view is_student
as
select Sno,Sname,Sage--分别指的是学生学号，姓名，年龄
from student
where Sdept='IS'--条件是系别为‘IS’
with check option;

 with check option选项

• 通过视图进行的修改，必须也可以通过视图看到修改后的结果

因此有以下小结

1.对于update，若有with check option，要保证更新后，数据依旧可以被查出。即我无法更新出现在在这个视图无法满足条件的数据。

2.对于delete，因为是删除数据因此with check option对操作无限制。

3.对于insert，若有with check option，要保证插入后的数据能够满足视图条件，即可以再次被视图查询。否则不允许插入。

4.对于没有where子句的视图，因为没有条件限制因此with check option不构成影响。

对视图的操作 

基于视图的视图

• 这更像是一种递进的关系，新的视图在被引用的视图上再次进行操作。如果说第一次视图是筛选一次的话，基于视图的视图就是二次筛选。
• 这种视图的定义与前种视图相同。

create view is_student_2
as
select Sno,Sname
from is_student--is_student是一个视图名之前已经建立好
where Sage>=20;

删除视图

• 使用drop view <视图名> [cascade]
• cascade是可选择的，它的意思是级联。如果加此关键字那么所有基于这个视图创建的视图也将一并被删除。
• 在缺省状态下，即没有cascade关键字时默认仅删除当前视图，如果当前视图具有级联关系则不能完成删除，系统报错。

四、内连接外连接

1.内连接

自然连接natural join

​​​​​​​student natural join takes

• 自然连接只考虑在2个关系的模式中都出现的那些属性上取值相同的元组对。与简单的将两个关系作笛卡尔积不同，自然连接并没有重复列出在两个关系的模式中都出现的属性。
• 简单的说就是相同的属性只保留一个。前提是在笛卡尔积形成的关系对中进行筛选。

select name,course_id
from student natural join takes;

 上述代码段在查询结果上等价于

select name,course_id
from student,takes
where student.ID=takes.ID;

 多个关系连接以及join...using

• 当连接多个关系时，如果仅仅依靠自然连接那么有可能会损失查询结果
• 例如student natural join takes natural join course
• student 先与 takes 连接并处理掉重复的属性，形成的结果再与course进行连接再次去掉重复的属性。这样的做法将带来危险，因为在处理时可能无形中增加了一个约束条件。
• 因此我们引入join...using来指定一个属性列表
• 例如：

select name,title
from (student natural join takes)join course using(course_id);

2.外连接 

• 左外连接：只保留出现在左外连接运算之前（左边）的关系中的元组
• 右外连接：只保留出现在右外连接运算之后（右边）的关系中的元组
• 全外连接：保留出现在两个关系中的元组

持续更新中 :-)