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

 

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