basic operations in sql

原创 2007年10月05日 12:14:00
CREATE TABLE TABNAME{
COL1 NUMBER(8) CONSTRAINT CON1 PRIMARY KEY [ON DELETE RESTRICT|SET NULL|SET DEFAULT|SET CASCADE],
COL2 VARCHAR(20) CONSTRAINT CON2 NOT NULL,
COL3 VARCHAR(20) CONSTRAINT CON3 UNIQUE,
COL4 NUMVER(2) CONSTARIN CON4 CHECK(COL>4),
COL5 NUMVER(3) DEFAULT 'SUN',
COL6 VARCHAR(5) CONSTRAINT COL6 REFERENCES MYTABLE(PK)
}

ALTER TABLE TANNAME
ADD CONSTRAINT CON1 FOREIGN KEY(KEYNAME) REFERENCES TABNAME(COLNAME);

ALTER TABLE TABNAME
ADD CONSTRAINT CON2 PRIMARY KEY(PK);

ALTER TABLE TANNAME
MODIFY COLNAME VARCHAR(2);

ALTER TABLE TABNAME
DROP(COLNAME);

ALTER TABLE TABNAME
DROP PRIMARY KEY;
DROP CONSTRAINT CONNAME;

DROP TABLE TABNAME;
TRUNCATE TABLE TABNMAE;

CREATE UNIQUE INDEX IDXNAME ON TABLE(COLNAME);
DROP INDEX IDXNAME;

INSERT INTO TABNAME(COLNAME...) VALUES(...);
INSERT INTO TABNAME VALUES(...);
INSERT INTO TABNAME(COLNAME...) VALUES(NULL,...);
INSERT INTO TABNAME(COLNAME...) WHERE SELECT COLNAME... FROM TABNAME WHERE...

DELETE FROM TABNAME WHERE...
DELETE FROM TABNAME;

UPDATE TABNAME SET COLNAME=XXX,COLNAME2=XXX WHERE...;

SELECT * FROM TABNAME;
SELECT (ALL) COLNAME FROM TABNAME;
SELECT DISTICT COLNAME FROM TABNAME;
SELECT COL1NAME AS "CUSTOMER1",COL2NAME AS "CUSTOMER2" FROM TABNAME;

SELECT COLNAME FROM TABLENAME WHERE COLNAME = XXX AND COLNAME2 = XXX;
SELECT COLNAME FROM TABLENAME WHERE NOT COLNAME = XXX AND NOT COLNAME = XXX;

SELECT COLNAME FROM TABLENAME WHERE COLNAME (NOT) BETWEEN X AND Y;(包含边界)

SELECT COLNAME FROM TABLENAME WHERE COLNAME (NOT) IN(VALUES);

SELECT COLNAME FROM TABLENAME WHERE COLNAME LIKE PARTTEN.(通配符:%任意多个字符,_一个字符)

SELECT COLNAME FROM TABLENAME WHERE COLNAME IS (NOT) NULL;

SELECT COLNAME FROM TABLENAME ORDER BY (COLNAME|COL_NUMVER) DESC/ASC;

SELECT MIN(COLNAME),MAX(COLNAME),AVG([DISTINCT]COLNAME),SUM([DISTINCT]COLNAME),COUNT(*|[DISTICT] COLNAME) FROM TABLENAME WHERE...
COUNT(*) =  NO.OF ROWS
COUNT(COLNAME) = NO. OF ROWS DO NOT HAVE NULL VALUE

GROUP BY必须在聚合函数使用的情况下使用,并且在select list中出现在的colname不在聚合函数中,就一定要出现在GROUP BY中。
HAVING COLNAME中的colname必须是出现在select list中的。也可以是selct list中的聚合函数
SELECT COLNAME,SUM(COLANME2) FROM TABNAME WHERE... GROUP BY COLNMAE HAVING SUM(COLNAME2) > XXX;

集合运算
并集UNION [ALL]:没有all时,结果消除重复项,有all则保留重复项。任何一个选择自己不能使用ORDER BY,但是UNION结果可以排序    
SELECT COLNAME...COLNAME FROM TAB1
  UNION [ALL]
SELECT COLNAME...COLNAME FROM TAB2

交集INTERSECT
SELECT COLNAME FROM TABNAME1
   INTERSECT
SELECT COLNAME FROM TABNAME2;

补集MINUS
SELECT COLNAME FROM TABNAME1
   MINUS
SELECT COLNAME FROM TABNAME2;

RESTRICTION就是水平投影,选出部分行,所有列,用WHERE实现
PROJECTION就是垂直投影,选出所有行,部分列。

连接运算JOIN
笛卡尔连接,内连接,等值连接,外连接其实质都是笛卡尔连接。
笛卡尔连接:SELECT a.colname1,b.colname2 from TAB1 a,TAB2 b;
            SELECT TAB1.COLNAME1,TAB2.COLNAME2 FROM TAB1,TAB2;

内连接(等值连接):SELECT a.colname1,b.colname2 from TAB1 a,TAB2 b WHERE a.colnamex = b.colnamey;
SELECT TAB1.COLNAME1,TAB2.COLNAME2 FROM TAB1,TAB2 WHERE TAB1.COLNAMEX = TAB2.COLNAMEY;

外连接
左外连接:SELECT TAB1.COLNAME1,TAB2.COLNAME2 FROM TAB1,TAB2 WHERE TAB1.COLNAMEX = TAB2.COLNAMEY(+);
右外连接:SELECT TAB1.COLNAME1,TAB2.COLNAME2 FROM TAB1,TAB2 WHERE TAB1.COLNAMEX(+) = TAB2.COLNAMEY;

自连接(一定要定义表的别名和列的别名):
SELECT COL1 AS "CL1",COL2 AS "CL2"... FROM TAB a,TAB b WHERE a.colname = b.colname;

独立子查询和相关查询
对于独立自查询而言,内查询先独立于外查询而执行,暂时存储查询结果,然后再独立进行外查询
相关查询:即内查询中要用到外查询的表。对外查询表的每一行,内查询都要全部执行一次。

EXIST
SELECT COLNAME...FROM TAB1 WHERE [NOT] EXIST (SELECT COLNAME... FROM TAB2 WHERE TAB1.COL = TAB2.COL) (WITH CHECK OPTION);

VIEW
CREATE VIEW VEIW_NAME(COL1,COL2...) AS SELECT ... FROM ...;
INSERT INTO VIEW_NAME(COL1...) VALUES(...);
DELETE VIEW_NAME WHERE...;
UPDATE VIEW_NAME SET...;
DROP VIEW VIEW_NAME;

GRANT
GRANT ALL [PRIVILEGES]|UPDATE(COLNAMES..)|SELECT|... ON TABNAME|VIWE_NAME TO USERNAME|PUBLIC [WITH GRANT OPTION];

REMOVE ALL [PRIVILEGES]|UPDATE(COLNAMES..)|SELECT|... ON TABNAME|VIWE_NAME FROM USERNAME|PUBLIC;
版权声明:本文为博主原创文章,未经博主允许不得转载。

相关文章推荐

Using INSTEAD OF triggers in SQL Server for DML operations

Problem I have created some views to provide limited data access for an application. Several of the...

Write operations are not allowed in read-only mode 只读模式下(FlushMode.NEVER/MANUAL)写操作不允

org.springframework.dao.InvalidDataAccessApiUsageException:Write operations are not allowed in read-...

Milling Operations In The Lathe_5

  • 2011-03-16 19:14
  • 29.22MB
  • 下载

Write operations are not allowed in read-only mode

使用Spring提供的Open Session In View而引起Write operations are not allowed in read-only mode (FlushMode.NEVE...

SSH Write operations are not allowed in read-only mode 错误解决

最近在配置 Structs, Spring 和Hibernate整合的问题: 开启OpenSessionInViewFilter来阻止延迟加载的错误的时候抛出了这个异常:     org.spri...

spring 声明式事务异常 :Write operations are not allowed in read-only mode

Write operations are not allowed in read-only mode 只读模式下(FlushMode.NEVER/MANUAL)写操作不允 Wri...

Write operations are not allowed in read-only mode 只读模式下(FlushMode.NEVER/MANUAL)写操作不允

org.springframework.dao.InvalidDataAccessApiUsageException: Write operations are not allowed in read...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

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