【数据库（一）】SQL语言-表定义、查询

基本模式定义+

SQL支持许多不同的完整性约束。

　　not null， 在该属性上不允许空值

　　primary key 是否是是主码，主码必须非空且唯一

　　foreign key

　　check(P),P是谓词条件

Create Table instructor （ID char(5),

                                   name varchar(20) not null,

　　　　　　　　　　　　dept_name varchar(20),

　　　　　　　　　　　　salary numeric(8,2),

　　　　　　　　　　　　primary key(ID),

　　　　　　　　　　　　check(salary>=0));

Create Table instructor （ID char(5), primary key,

                                   name varchar(20),

　　　　　　　　　　　　dept_name varchar(20),

　　　　　　　　　　　　salary numeric(8,2),

　　　　　　　　　　　　check(salary>=0));

在SQL server2000中，与WHERE子句不同的是：它能为空

Drop table instructor

Alter Table instructor add birthday，增加新的属性

Alter Table instructor drop A，去掉某些属性，不用某个属性就好，误操作引起

Alter table instructor modify(ID char(10));

SQL是具有影响力的商用市场化的关系查询语言

　　数据定义语言（DDL）

　　数据操纵语言（DML）

　　数据控制语言（DCL）

SQL查询基本结构

　　select 

　　from

　　where

去除重复： select distinct dept_name from instructor

选择所有属性：select * from instructor;

where子句

　　select name frome instructor where dept_name ='Comp.Sci' and salary>70000;

逻辑连接符：and or not <= >= ><

from 子句 

找出关系instructor 和teachers 的笛卡尔积

select *

from instructor, teaches;

例子：找出Computer Science系的教师名称和课程名称

instructor(ID,name,dept_name,salary)  teaches(ID,course_id,sec_id,semester,year)

select name,course_id

from instructor, teaches

where instructor.ID = teachers.ID and instructor.dept_name = 'Comp. Sci';

【笛卡尔积】

在数学中，两个集合X和Y的笛卡儿积（Cartesian product），又称直积，表示为X × Y，第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员。

假设集合A={a, b}，集合B={0, 1, 2}，则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。

类似的例子有，如果A表示某学校学生的集合，B表示该学校所有课程的集合，则A与B的笛卡尔积表示所有可能的选课情况。A表示所有声母的集合，B表示所有韵母的集合，那么A和B的笛卡尔积就为所有可能的汉字全拼。

 

设A,B为集合，用A中元素为第一元素，B中元素为第二元素构成有序对，所有这样的有序对组成的集合叫做A与B的笛卡尔积，记作AxB.

笛卡尔积的符号化为：

A×B={(x,y)|x∈A∧y∈B}

例如，A={a,b}, B={0,1,2}，则

A×B={(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}

B×A={(0, a), (0, b), (1, a), (1, b), (2, a), (2, b)}

运算性质

1.对任意集合A，根据定义有

AxΦ =Φ , Φ xA=Φ

2.一般地说，笛卡尔积运算不满足交换律，即

AxB≠BxA（当A≠Φ ∧B≠Φ∧A≠B时）

3.笛卡尔积运算不满足结合律，即

(AxB)xC≠Ax(BxC)（当A≠Φ ∧B≠Φ∧C≠Φ时)

4.笛卡尔积运算对并和交运算满足分配律，即

Ax(B∪C)=(AxB)∪(AxC)

(B∪C)xA=(BxA)∪(CxA)

Ax(B∩C)=(AxB)∩(AxC)

(B∩C)xA=(BxA)∩(CxA)

SQL重命名机制

　　old name as new name

　　　　select name as instructor_name

【E】找出所有教师名，他们的工资至少比Biology系的某一个教师的工资要高：

select distinct T.name

from instructor as T,instructor as S

where T.salary >S.salary and S.dept_name = 'Biology';

字符串运算

　　like的模式匹配

　　百分号(%)匹配字符串通配符

　　下划线(_)匹配任意一个字符

转义字符

　　‘ab\%cd%’

排序

　　按照字母顺序列出在Physics习的所有老师

　　select name 

　　from instructor

　　where dept_name = 'Physics'

　　order by name;//按照name字段进行排序；

升序&降序（ordered by salary desc, name asc）//如果工资相同，那么按照name字母进行升序。

集合运算

except,剔除掉

聚集函数

　　平均值（avg）

　　最小值（min）

　　最大值（Max）

　　综合（sum）

分组聚集

　　group by 分组概念

　　having对分组再进行选择

select avg(salary) as avg_salary

from instructor 

where dept_name = 'Comp.Sci';

找出每个系的平均工资

select dept_name avg(salary) as avg_salary

from instructor

group by dept_name;

找出平均工资超过42000美元的系

select dept_name avg(salary) as avg_salary

from instructor

group by dept_name

having avg(salary) > 42000

空值

select name from instructor where salary is null;

空值的存在给聚集运算带来了麻烦，聚集函数根据以下原则处理空值

除了count *外，所有的函数都忽略空值