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’));
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
多个表中有共同的字段
一定要指明是那个表的