PostgreSQL Serial类型和序列及子查询（五）

骑猪看日落 2023-10-17 17:08 19阅读 0赞

**一、Serial类型：自动增长**

PostgreSQL具有数据类型`smallserial`，`serial`和`bigserial`; 这些不是真正的类型，而只是在创建唯一标识符列的标志以方便使用。 这些类似于一些其他数据库支持的`AUTO_INCREMENT`属性。

如果您希望某列具有唯一的约束或是主键，则必须使用其他数据类型进行指定。

类型名称`serial`用于创建整数列。 类型名称`bigserial`创建一个`bigint`类型的列。 如果您期望在表的使用期限内使用超过`2^31`个标识符，则应使用`bigserial`。 类型名称`smallserial`创建一个`smallint`列。

**`SERIAL`数据类型的基本用法如下：**

CREATE TABLE tablename (
        colname SERIAL
    );

操作如下：

CREATE TABLE COMPANY(
       ID  SERIAL PRIMARY KEY,
       NAME           TEXT      NOT NULL,
       AGE            INT       NOT NULL,
       ADDRESS        CHAR(50),
       SALARY         REAL
    );
    
    --添加自动递增
    INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
    VALUES ( 'Paul', 32, 'California', 20000.00 );
    
    INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
    VALUES ('Allen', 25, 'Texas', 15000.00 );
    
    INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
    VALUES ('Teddy', 23, 'Norway', 20000.00 );
    
    INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
    VALUES ( 'Mark', 25, 'Rich-Mond ', 65000.00 );
    
    INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
    VALUES ( 'David', 27, 'Texas', 85000.00 );
    
    
    INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
    VALUES ( 'Kim', 22, 'South-Hall', 45000.00 );
    
    INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
    VALUES ( 'James', 24, 'Houston', 10000.00 );

**效果图：**

![Center][]

**二、序列语法：**

CREATE [ TEMPORARY | TEMP ] SEQUENCE [ IF NOT EXISTS ] name [ INCREMENT [ BY ] increment ]
        [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
        [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
        [ OWNED BY { table_name.column_name | NONE } ]

具体说明：

INCREMENT BY ： 每次序列增加（或减少）的步长

MINVALUE ： 序列最小值，NO MINVALUE表示没有最小值

MAXVALUE ： 序列最大值，NO MAXVALUE表示没有最大值

START WITH ：以什么序列值开始

CYCLE ： 序列是否循环使用

OWNED BY ： 可以直接指定一个表的字段，也可以不指定。

![Center 1][]

操作如下：

--创建序列
    create sequence seq_no increment by 1 minvalue 1 no maxvalue start with 1000;
    
    --删除表
    drop table student;
    --创建表
    create table student
    (
     sno integer default nextval('seq_no')  primary key,
     sname varchar(20) 
    );
    
    
    --添加数据
    insert into student(sname) values('zhang');
    insert into student(sname) values('fei');
    insert into student(sname) values('liu');
    
    
    --查询数据
    select *from student;

![Center 2][]

**三、子查询**

子查询或内部查询或嵌套查询是一个PostgreSQL查询中的查询，它可以嵌入到`WHERE`子句中。子查询用于返回将在主查询中使用的数据作为进一步限制要检索的数据的条件。  
子查询可以与`SELECT`，`INSERT`，`UPDATE`和`DELETE`语句以及运算符(如`=`，`<`，`>`，`>=`，`<=`，`IN`等)一起使用。

**子查询必须遵循以下规则：**

*  子查询必须括在括号中。
 *  子查询在SELECT子句中只能有一列，除非主查询中有多个列用于比较其所选列的子查询。
 *  `ORDER BY`不能用于子查询，尽管主查询可以使用`ORDER BY`。 `GROUP BY`可用于执行与子查询中的`ORDER BY`相同的功能。
 *  返回多行的子查询只能与多个值运算符一起使用，例如：`IN`，`EXISTS`，`NOT IN`，`ANY / SOME`，`ALL`运算符。
 *  `BETWEEN`运算符不能与子查询一起使用; 但是，`BETWEEN`可以在子查询中使用。

操作如下：

--(1) 子查询语法：
    SELECT column_name [, column_name ]
    FROM   table1 [, table2 ]
    WHERE  column_name OPERATOR
          (SELECT column_name [, column_name ]
          FROM table1 [, table2 ]
          [WHERE])
    
    
    select * from company;
    
    --查询工资>35000的信息 (子查询in)
    select * from  company where id in (select id from company where salary>35000);
    --查询工资>45000的信息(子查询exists)
    select * from company c where exists (select * from company cc where c.id=cc.id and cc.salary>45000);
    
    
    --（2）带INSERT语句的子查询语法：
    INSERT INTO table_name [ (column1 [, column2 ]) ]
               SELECT [ *|column1 [, column2 ]
               FROM table1 [, table2 ]
               [ WHERE VALUE OPERATOR ]
    
    
    --备份一个表COMPANY_Back
     --a)复制一个company的结构
    create table company_back as select * from company where 1!=1;
    
     --b)查询
     select *from company_back;
    
     --(c)批量添加数据
     insert into company_back  select * from company where id>3;
    
    
    
     --(3) 带UPDATE语句的子查询：
    UPDATE table
    SET column_name = new_value
    [ WHERE OPERATOR [ VALUE ](SELECT COLUMN_NAME FROM TABLE_NAME)[ WHERE) ]
    
    --在company_back表中AGE大于或等于27的将COMPANY表中的SALARY更新为0.50倍：
    update company set salary=salary*0.5 where id in (select id from company_back where age>=27);
    
     --b)查询
     select *from company;
    
    --(4) 带有DELETE语句的子查询：
    DELETE FROM TABLE_NAME
    [ WHERE OPERATOR [ VALUE ] (SELECT COLUMN_NAME FROM TABLE_NAME) [ WHERE) ]
    
    --在company_back表中AGE大于或等于27的将COMPANY表中删除：
    delete from company where  age in (select age from company_back where age>=27);

[Center]: https://img-blog.csdn.net/20171201142758223?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvaGx4MjAwODA4MDg=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
[Center 1]: https://img-blog.csdn.net/20171201143152888?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvaGx4MjAwODA4MDg=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
[Center 2]: https://img-blog.csdn.net/20171201143238194?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvaGx4MjAwODA4MDg=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center