SQL语言备忘录

 

Boolean Data

BOOLEAN    TURE/FALSE

Numeric Data

Exact Numbers

INTEGER (INT，INT4) 2,147,483,648 to 2,147,483,647

SMALLINT -32768 to 32767

BIGINT ( INT8) 8位整数

Approximate Numbers

FLOAT[precision]

REAL

DOUBLE PRECISION

Formatted Numbers

DECIMAL(i,j), DEC(i,j) or NUMERIC(i,j)

 

CREATE TABLE TT (A DATE, B DATE, C INTERVAL); (creat  错 )

INSERT INTO TT(A, C) VALUES(NOW(),'1 DAY');

UPDATE TT SET B = A+ '2 MONTH'::INTERVAL;

 

‘2000-02-29’ 推荐    to_date('2000/02/29' , ‘yyyy/mm/dd' )

 

CREATE DOMAIN hour_dom AS INTEGER DEFAULT 0 CHECK (VALUE >= 0);

CREATE DOMAIN genderDom AS CHAR    DEFAULT ‘M’
       CHECK (VALUE IN (‘F’, ‘f’,‘M’ ,‘m’));   

 

CREATE TABLE BOOK

 (  Book_Id NUMERIC(6) NOT NULL,

       Edition NUMERIC(3) NOT NULL,

       CallNumber VARCHAR(8) NOT NULL UNQUE,( UNQUE 备选码     唯一码)

       Primary Key (Book_Id),

       FOREIGN KEY (LibCheck) REFERENCES LIBRARIAN(SSN)

 );

 

DROP TABLE MEMBER CASCADE;

DROP TABLE MEMBER RESTRICT;

 

ALTER TABLE tableName

[ADD [COLUMN] columnName dataType [NOT NULL] [UNIQUE]

[DEFAULT defaultOption] [CHECK (searchCondition)]]

[DROP [COLUMN] columnName [ RESTRICT | CASCADE ]]

[ADD [CONSTRAINT [constraintName]]tableConstraintDefinition]

[DROP CONSTRAINT constraintName [ RESTRICT | CASCADE ]]

[ALTER [COLUMN] SET DEFAULT defaultOption]

[ALTER [COLUMN] DROP DEFAULT]

 

第一：

ALTER TABLE products ADD COLUMN description text ;

ALTER TABLE products DROP COLUMN description RESTRICT;

ALTER TABLE products DROP COLUMN description CASCADE;   

 

第二：

ALTER TABLE products

ADD CHECK (name <> '');

ALTER TABLE products

ADD CONSTRAINT some_name UNIQUE (product_no);

ALTER TABLE products

ADD FOREIGN KEY    (product_group_id) REFERENCES product_groups;

ALTER TABLE products DROP CONSTRAINT constraint_name;

 

第三：

ALTER TABLE LIBRARIAN

ALTER COLUMN Salary TYPE NUMBER(6,2);  

ALTER TABLE DEPENDENT

ALTER COLUMN kinship DROP DEFAULT（NOT NULL）;

ALTER TABLE BOOK

ALTER COLUMN BorrowerMemNo SET DEFAULT NULL(SET NOT NULL);

 

SELECT DISTINCT CallNumber （消重）

       FROM BOOK;

 

SELECT staffNo,fName,lName, salary/12 AS monthlySalary FROM Staff;

 

SELECT Book_id FROM BOOK WHERE BorrowerMemNo IS NULL;

 

SELECT * FROM LIBRARIAN  WHERE Salary BETWEEN 25000 AND 35000;

 

SQL 语句， WHERE 子句为真的才被选出

1.SELECT staffNo,fName,lName,position

              FROM Staff

              WHERE position NOT IN (‘Manager’, ‘Supervisor’) is not false ；（包括 NULL ）

( 题目句子中有 not in   就用此结构 )

2.Select SSN,name,salary

              From Employee

              Where SSN in

              (select MgrSSN

From DEP) is not null;   ( 包括 NULL)

 

_ 一个字符

%任意个字符

SELECT SSN, Name    FROM LIBRARIAN      WHERE Name LIKE 'Nick%';

表示本身 转义字

SELECT Name, CallNumber

       FROM TITLES

       WHERE Name LIKE '%10&%%' ESCAPE '&';

 

SELECT Fname || MI || Lname AS Name, PhoneNumber FROM MEMBER

       WHERE PhoneNumber LIKE '(412) 6_3%';

 

 

SELECT SUM (Salary) AS TotalSalaries,     --求和

           MAX (Salary) AS MaxSalary,              --最大值

           MIN (Salary) AS MinSalary,         --最小值

           AVG (Salary) AS AvgSalary,         --平均值

           COUNT (*) AS Cardinality,          --元组计数

           COUNT (DISTINCT Salary) AS Salarylevels –不重复非空值计数

FROM LIBRARIAN;

 

count(*)对所有选出元组计数， count(column)只对column非NULL的原组计数。

ELECT 后一般只能出现集函数和 GROUP BY 后的分组列 ( 补：将一些看是无用的放在 GROUP BY 后 )

SELECT CallNumber, COUNT(*) AS NoPopularBooks

       FROM BOOK

       WHERE Edition >= 3           --元组限制

       GROUP BY CallNumber        --分组

       HAVING COUNT (*) >= 5; --分组限制     

 

嵌套子查询

1 ． ( =, <>, >, >=, <, <= ) 要求子查询单值，类型可比，子查询只能在比较运算符右边。

SELECT SSN

              FROM Librarian

              WHERE salary <

                     (SELECT salary

                            FROM Librarian

                            WHERE SSN = ‘S3-07-021’);

2 ． ANY (that is, SOME) or ALL.

Comparisons ( =, <>, >, >=, <, <= ) + (ANY or ALL) 实现各种复杂语义

> ANY  不是最小， >= ALL 最大， <= ALL 最小

SELECT SSN

FROM Librarian

WHERE salary <= ALL

(SELECT salary

FROM Librarian);

 

Example 2： List the name and SSN of all head librarians whose salary is lower than that of any librarian who is not a head librarian.

SELECT Name, SSN

FROM LIBRARIAN, SECTION

WHERE SSN = HeadSSN AND salary < ANY            (SELECT Salary

FROM LIBRARIAN L

WHERE L.SSN NOT IN

( SELECT HeadSSN FROM SECTION ) IS NOT FALSE);

相关子查询：

计算时，类似两重循环。外层带值入内层，测试是否有值。

Example 1: List the names and SSNs of all librarians who are not head librarians.

SELECT L.SSN, L.Name

FROM LIBRARIAN L

WHERE NOT EXISTS

(SELECT *

FROM SECTION

WHERE L.SSN = HeadSSN); - - 内外层相关

 

The UNIQUE and NOT UNIQUE operators test for duplicates in a result. 唯一测试

Example: List the names and SSNs of all librarians with more than one dependent.

SELECT Name,SSN

FROM Library L

WHERE NOT UNIQUE

(SELECT * FROM DEPENDENT

WHERE LIBSSN = L.SSN);

 

表连接

1．SELECT Lname, Book_Id

       FROM Member,Book

       WHER MemNo=BorrowerMemNo; 

2．SELECT DISTINCT Lname, Address

FROM (MEMBER JOIN BOOK ON MemNo = BorrowerMemNo)

WHERE Fname = 'Andreas' or Fname = 'Rebecca';

 

UPDATE TableName

SET columnName1=value [, columnName1=value…]

[WHERE searchCondition]

UPDATE librarian

SET salary = salary*1.05;

       WHERE section =

                 (SELECT sectno

                   FROM sction

                   WHERE name = ‘Art’);

 

DELETE FROM TableName

[WHERE searchCondition]

DELETE FROM librarian

WHERE section =

(SELECT sectno FROM section

WHERE name = ‘checkout’);

DELETE FROM book;( 全部删除 )

 

 

集合关系

Intersect expect union

 

 

不用 <> 而用‘ not in ’ +’=’

 

Like (‘A’ or ‘B’) 不好  Like ‘A’ or Like ‘B’

 

UPPER （ gender ） = ‘F’  LOWER

 

多个表中有共同的字段   一定要指明是那个表的