【学习笔记】sql语法笔记

短命女 2022-03-25 18:08 342阅读 0赞

### 文章目录 ###

*  表操作
 *   *  建表
     *  删除表
     *  修改字段
 *  增删查找
 *   *  增加
     *   *  插入值
         *  添加查询结果
     *  删除
     *  更新
     *  查找
     *   *  选择
         *  更名
         *  匹配
         *  where
         *  集合函数
         *  聚集函数
         *  嵌套子句
 *  建立数据库
 *   *  完整性约束
     *  视图
     *  索引
     *  用户自定义类型
     *  权限

# 表操作 #

## 建表 ##

create table

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

//完整性约束
    foreign key(dept_name) references department

## 删除表 ##

删表

`drop table r;

## 修改字段 ##

增加字段

alter table r add A D;
    alter table r drop A;

# 增删查找 #

## 增加 ##

### 插入值 ###

insert into instructor
    values(10211,'smith','Biplogy','66000');
    order by: salary desc,name asc;
    //desc:降序，asc：升序（默认升序）

### 添加查询结果 ###

insert into course
    value ('CS-437','Database System','comp.sci',4)

insert into instructor
    select ID,name,dept_name,1800
    from student
    where dept_name='Music' and cot_cred>144;

## 删除 ##

删所有元组

//从student中删除所有元组
    delete from student;

删除条件元组

delete from r
       where P; //省略where将删除全部

## 更新 ##

update instructor
    set salary =salary* 1.05;
    set salary=case
                       when ... then ...
                       when ... then ...
                       ...
                       else ...  ( else()否则为空)
                       end

## 查找 ##

### 选择 ###

select

*  distinct
 *  all
 *  salary \*1.1  
    from
 *  r1 natural join r2;
 *  r2 join r2 using ( );//允许哪些列相等

### 更名 ###

select name as instructor_name

select T.name,S.course_id
    from instructor as T,teacher as S
    where T.ID=S.ID

### 匹配 ###

where building like

*  ‘Intro%’ 以Intro 打头
 *  ‘% comp% ’包含comp
 *  ‘\_ \_ \_’ 匹配只含三个字符
 *  ‘\_ \_ \_%’ 匹配至少三个字符
 *  like 'ab%cd%'escape ‘’ 以“ab%cd”开头
 *  not like

### where ###

*  between
 *  (a1,b1)=(c1,d1)

### 集合函数 ###

*  （ ）union ( );
 *  ( ) intersect ( );
 *  ( ) except ( );

### 聚集函数 ###

*  select avg(salary)  
    from  
    where
 *  select count(distinct ID)  
    select count(\*)//计算元组个数
 *  分组  
    group by  
    having avg(salary)>4200 //建完表后再一次筛选

### 嵌套子句 ###

*  in/not in (‘mozart’,‘Einstein’)
 *  集合比较

>some // 至少比某一个大
    <> some // not in
    =all    // ==in
    >all    // 比所有都大

*  exists / not exsists( ) // 在此集合中是否存在
 *  unique // 最多一次  
    not unique // 最少两次

select T.course_id
    from course as T // 从course 表选
    where unique (
    select R.course_id
    from section as R
    where T.course_id=R.course_id 
    and R.year =2009); //重复元组存在性测试显示为true的元组

*  from

select dept_name , avg_salary
    from (select dept_name,avg(salary)
             from instructor
             group by dept_name)
    as dept_avg(dept_name,avg_salary)//rename
    where avg_salary>42000;

*  lateral

select
    from instructo I1,lateral
    (select avg(salary)
      from instructor I2
      where I2.dept_name=I1.dept_name);

*  with: 临时关系

with max_budget(value) as
    (select max(budget)
        from department) //临时表

# 建立数据库 #

## 完整性约束 ##

单个关系

not null
    unique (Aj1,Aj2,Ajm)
    check

参照完整性

foreign key(dept_name)references department

dept_name varchar(20) references department

*  tot\_cred numeric (3,0) default 0 // 默认值
 *  cast e as t

date '2001-04-25'
    time '09:30:00'
    timestamp'2007-04-25 10:29:01.45'

## 视图 ##

creat view faculty as
    select ID,name,dept_name
    from instructor;

## 索引 ##

create index student ID_index on student (ID);

## 用户自定义类型 ##

create type Dollars as numeric (12,2) final;

## 权限 ##

*  grant(select,update(budget)在…属性上的更新,delete)  
    on  
    to
 *  revoke  
    on  
    from
 *  create role instructor;  
    grant select on department to Amit with grant option

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

后记

> 新人上路，旨在分享交流干货经验，若有错误或者不当之处，请大家批评指正！  
> 更多说明请查看 [Doraengineer’s blog说明][Doraengineer_s blog]

[Doraengineer_s blog]: https://blog.csdn.net/weixin_42641395/article/details/82558190