数据库SQL语言基础知识

表的创建、更新、删除

创建表

模版

create table 表名
(属性名1 数据类型 约束,
 属性名2 数据类型 约束,
 …
 属性名n 数据类型 约束,
 primary key(主键名),
 foreign key(外键)references 表名);

数据类型:定长字符串char()  变长字符串verchar() 数值numeric ()

约束

完整性约束

复杂条件check

例子

 

修改表

//方法一
alter table <表名> add <列定义> | <完整性约束定义>
alter table S add email varchar(20) constraint S_Chk check(email like '%@%')
//方法二
alter table <表名> alter column <列名> <数据列名> [null |not null]
alter table S alter column SN char(12) not null

删除表

drop table 表名;

查询

关系代数与sql

投影

select 属性

选择

where 条件

等值连接

from 表1,表2
where 条件（共同属性相等）;

并差交

()union();
()except();
()intersect();

更名

select 属性名 as 新属性名
from 表 表新名

笛卡尔积

from 表1,表2;

除

给定关系R(X,Y)和S(Y,Z),其中X,Y,Z为属性组。R中Y与S中的Y可以有不同的属性名,但必须出自相同的域集。R与S的除运算可以得到一个新的关系P(X),P是R中满足下列条件的元组在X 属性列上的投影: 元组在X上的分量值x的像集Y(x)包含S在Y上的投影的集合。🌟理解关系除：A➗B，A不在B中的列包含AB所有共有列，除的结果是A满足条件元组且删去与B相同列

通用方法 R中非与S共有属性不存在其对应共有属性不在S中

select distinct R.X 
from R R1
where not exists
( 
    select S.Y from S
    where not exists
    ( 
        select * from R R2
        where R2.X=R1.X and R2.Y=S.Y 
    ) 
)

特例可以用其他方法实现

例：检索至少学过课程C135和课程C219课程的学生信息，包括学号、姓名、专业

select 学号,姓名,专业
from 学生
where 学号 in(select X1.学号
             from 学习 X1,学习X2
             where X1.学号=X2.学号 and X1.课程号=‘C135’ and X2.课程号=‘c219’);

聚集函数

平均值avg 最小值min 最大值max 总和sum 计数count

注意⚠️：sum和count的区别

group by 属性
having 限定输出条件;

聚集函数计算的值为分组后组内的函数值

嵌套子查询

嵌套在where中

   in  some   any   all

//找出在2009年秋季和2010年春季同时开课的所有课程
select distinct course_id
from section
where semester = ‘Fall’ and year= 2009 and 
course_id not in (select course_id
                  from section
                  where semester = ‘Spring’ and year= 2010);

//查出这些老师的姓名，他的工资要比Biology系某教师工资高
select name
from instructor
where salary > some (select salary
                     from instructor
                     where dept_name ='Biology');

//找出平均工资最高的系
select name
from instructor
group by dept_name
having avg(salary) >= all(select avg(salary) //集合的比较
                          from instructor
                          group by dept_name);

EXIST

EXISTS用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False。

EXISTS可以和NOT一起使用：NOT EXIST

强调的是是否返回结果集，不要求知道返回什么

//找出在2009年秋季和2010年春季同时开课的所有课程
select course_id
from section as S
where semester=‘Fall’
      and year=‘2009’
      and exists (select *
                  from section as T
                  where semester=‘Spring’ 
                        and year=‘2010’
                        and S.course_id=T.course_id);

嵌套在from中

//找出系平均工资超过42000美元的那些系的教师平均工资
select dept_name, avg_salary
from (select dept_name, avg(salary) as avg_slary (属性的别名)
      from instructor
      group by dept_name)
where avg_salary >42000;

 嵌套子句可用在select子句中生成标量值

//列出所有系以及它们拥有的教师数
select dept_name,(select count(*)
                  from instructor
                  where department.dept_name=instructor.dept_name)
as num_instructors (表的别名)
from department;

增删改

插入数据

字符串用'' 数值无需外加符号

//自定义属性列顺序
insert into course (course_id, title, dept_name, credits)
values (’CS-437’, ’Database Systems’, ’Comp. Sci.’, 4);
//按原有属性排序
insert into student
values (’3003’, ’Green’, ’Finance’, null);
//从其他表中选取数据插入
insert into instructor
select ID, name, dept_name, 18000
from student
where dept_name = ‘Music’ and tot_cred > 144;

删除数据

delete from 表名
where 条件;

更新数据

update 表名
set 属性=值
where 条件;

注：更新语句顺序会影响最终结果