SQL语言备忘录

原创 2007年10月12日 13:02:00
 
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’);
2ANY (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’
 
UPPERgender = ‘F’ LOWER
 
多个表中有共同的字段 一定要指明是那个表的
 

C语言中文件操作备忘录(一)

 一:fopen()中的第二参数(模式参数)的格式详解可用来组合的字符有:r,w,a,t,+,b,它们分别代表的意义为:r: read;w: write;a: append;t: text;+: re...
  • anyue417
  • anyue417
  • 2006-08-13 14:41:00
  • 2060

备忘录(C语言实现)

  • 2008年05月28日 14:38
  • 162KB
  • 下载

用C语言编写的备忘录程序(事件记录程序)

  • 2014年01月12日 22:05
  • 5KB
  • 下载

实现备忘录功能代码

#import "ViewController.h"   #import "AppDelegate.h"   #import          @interface ViewControll...
  • qq_24554949
  • qq_24554949
  • 2015-11-10 22:04:00
  • 812

C语言程序备忘录

1、编写一个将输入复制到输出的程序,并将其中连续多个空格用一个空格代替。 解1: 注:从没想到,无中生有也可以用在这里。光靠C语言本身力量真的很有限。 解2: 解3: #in...
  • xiaotengyi2012
  • xiaotengyi2012
  • 2011-08-26 20:40:46
  • 805

c语言教材编写备忘录

  • 2011年07月13日 08:23
  • 3.21MB
  • 下载

C语言和设计模式(备忘录模式)

【 声明:版权所有,欢迎转载,请勿用于商业用途。  联系信箱:feixiaoxing @163.com】     备忘录模式的起源来自于撤销的基本操作。有过word软件操作经验的朋友,应该基本上...
  • feixiaoxing
  • feixiaoxing
  • 2012-01-05 21:08:29
  • 5817

vb写的备忘录,很漂亮

  • 2013年05月01日 08:11
  • 1.43MB
  • 下载

SQL Loader使用备忘录

 最近需要将大量的文件中的数据导入到数据库,sql loader当然是首选的工具,这两天研究了一下这个工具,从网络上得到了很多资料,自己做了个demo,留下来一些做个备忘。如果有不对的地方:yaoga...
  • fantian830211
  • fantian830211
  • 2007-07-23 13:26:00
  • 1629
收藏助手
不良信息举报
您举报文章:SQL语言备忘录
举报原因:
原因补充:

(最多只允许输入30个字)