Details of SQL

>>SQL 是一门 ANSI 的标准计算机语言，用来访问和操作数据库系统。

To public data from website database,the following factors must be used.

>>>RDBMS (Relational Database Management System) 将数据组织为相关的行和列,RDBMS is the base of SQL ,also for modern DBMS.Such as MS SQL Server,IBM DB2,Oracle,MySQL,Microsoft Access. which save data in table called databse object.it makeups with colume and row.

>>>Service Script Language (PHP,ASP)

>>>SQL

>>>HTML/CSS

WE CAN SPLIT SQL TO DML (Data MANIPULATION LANGUAGE) AND DDL(DATA DEFINATION LANGUAGE) 

>>MS Access 和 SQL Server 2000不必在每条 SQL 语句之后使用分号，某些数据库软件要求必须使用分号。

DML:

>> SELECT

>>UPDATE

>>DELETE

>>INSERT INTO

DDL:

>>CREATE DATABASE

>>ALTER DATABASE

>>CREATE TABLE

>>ALTER TABLE

>>DROP TABLE

>>CREATE INDEX

>>DROP INDEX

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

>>SQL SELECT:

SELECT COL_NAME FROM TABLE_NAME

SELECT * FROM TABLE_NAME

SELECT LASTNAME,FIRSTNAME FROM PERSON

* IS A QUICK WAY TO SELECT ALL COLUME IN A TABLE.

>>SQL SELECT DISTINCT

SELECT DISTINCT COL_NAME FROM TABLE_NAME

 DISTINCT SHOW THE ONLY ONE AT A TABLE

>>SQL WHERE

SELECT COL_NAME FROM TABLE_NAME WHERE COL OPERATOR VALUE

OPERATOR INCLUDES--->>>   =, <>,>,<,>=,<=,BETWEEN,LIKE

>>EG:SELECT * FROM PERSON WHERE CITY='NEWYORK'

NOTE:VALUE WAS INCLUDE BY ',WE BELIEVE BOTH ' AND " CAN BE USED WELL HERE.

NOTE:WHEN VALUE IS A TEXTVALUE,USR QUOTE,WHEN NUMBER,NOT USE.

EG:

TEXT RIGHT: SELECT * FROM PERSONS WHERE FIRSTNAME='JOIN'

NUMBER RIGHT:SELECT * FROM PERSONS WHERE YEAR>NUMBER

>>SQL AND&OR

EG AND:  SELECT * FROM PERSON WHERE FIRSTNAME='A' AND LASTNAME='B'

EG OR:    SELECT * FROM PERSON WHERE FIRSTNAME='A' OR LASTNAME='B'

EG AND&OR:SELECT * FROM PERSONS WHERE (FN='A' OR FN='W')AND LN='C'

>>SQL ORDER BY

SORT THE RESULT ,DEFAULT SORT SMALL TO BIG

IF YOU WANT TO SORT FROM BIG TO SMALL ,USE DESC

SELECT A,B FROM D ORDER BY A

SELECT  COMPANY,ORDERNUMBER FROM ORDERS ORDER BY COMPANY,ORDERNUMBER

SELECT COMPANY,ORDERNUMBER FROM  ORDERS ORDER BYCOMPANY DESC

SELECT COMPANY,ORDERNUMBER FROM ORDERS ORDER BY COMPANY DESC ,ORDERNUMBER ASC

>>SQL INSERT INTO

INSERT INTO TABLE WHERE VALUES

WE CAN ALSO POINT WHICH COLUME WE WANT INSERT INTO

INSERT INTO TABLE_NAME(COL1,OL2...COL...) VALUES(V1,V2...)

EG:INSERT INTO PERSON VALUES('GATES','BILL','XUANWUMEN 10','BEIJING')

SELECT INTOPERSON(LASTNAME,ADDRESS)VALUES('WILSON',CHAMPS-ELYSEES')

>>SQL UPDATE

UPDATE TABLE_NAME SET COL_NAME=NEWVALUE WHERE COLNAME=VALUE

UPDATE PERSON SET ADDRESS='ZHONGGUO' CITY='NANJING' WHERE LASTNAME='WILLSON'

>.SQL DELETE(USED TO DELETE A ROW)

DELETE FROM TABLENAME WHERE COLNAME=VALUE

DELETE FROM PERSON WHERE LASTNAME='A'

DELETE FROM TABLENAME===> DELETE ALL ROW FROM TABLE AND KEEP STRUCTURE INDEX AND PROPERTY/ARRTIBUTE.

DELETE * FROM TABLENAME

>.SQL TOP

SELECT TOP NUMBER|PERCENT COLUMES NAME(S) FROM TABLENAME

SELECT TOP 2 * FROM  PERSONS

SELECT 50 PERCENT * FROM PERSONS

>>SQL LIKE

SELECT * FROM PERSONS WHERE CITY LIKE 'N%'

SELECT * FROM PERSONS WHERE CITY LIKE '%G'

SELECT * FROM PERONS WHERE CITYT LIKE '%LON%'

>.SQL CHARACTER

 %---REPLACE ONE OR MORE CHARACTERS

_ -----REPLACE ONLY ONE CHARACTER

[CHARLIST]-----ANY SINGLE CHARACTER IN LIST

[^CHARLIST]OR[!CHARLIST]--- NOT MATCH ANY CHARACTER IN CHARLIST

>.SQL IN

SELECT * FROM PERSONS WHERE LASTNAME IN ('A','B')

>>SQL BETWEEN

SELECT * FROM PERSONS WHERE LASTNAME BETWEEN A AND B(NOT INCLUDE)

SELECT * FROM PERSONS WHERE LASTNAME NOT BETWEEN A AND B

>.SQL ALIAS

SELECT LASTNAME AS FAMILY ,FIRSTNAME AS NAME

>>SQL JOIN SEARCH DATA FROM TWO OR MORE TABLES

SELECT PERSONS.LASTNAME,PERSONS.FIRSTNAME,ORDER.ORDERNO FROM PERSONS,ORDERS WHERE  PERSONS.ID_P=ORDERS.ID_P

SELECT PERSONS.LASTNAME,PERSONS.FIRSTNAME,ORDER.ORDERNO FROM PERSONS INNER JOIN ORDERS ON PERSONS.ID_P=ORDERS.ID_P ORDER BY PERSONS.LASTNAME

JOIN/INNER JOIN:  AL LEAST ONE MATCH

LEFT JOIN:IGNORE WHETHER RIGHT TABLE MATCHED OR NOT

RIGHT JOIN:IGNORE WHETHER LEFT TABLE MATCHHED OR NOT

FULL JOIN

>>SQL UNION

UNION SELECT MUST HAVE SAME NUMBER OF COLUME ,SAME DATETYPE ,SAME COLUME ORDER

BY DETAUTLT ,UNSION SELECT DISTINCT VALUE ,IF YOU WANT TO KEEP ALL USE UNION ALL

>>SQL SELECT INTO

SELECT DATA FROM ONE AND INSERT INTO ANOTHER ONE TABLES

SELECT * INTO NEWTABLE FROM OLDTABLE

SELECT COLUME INTO NEW-TABLE_NAME  FROM OLDTABLENAME

SELECT * INTO ABACKUP FROM A

SELECT LASTNAME,FIRSTNAME INTO PERSON_BACKUP FROM PERSON WHERE CITY='BEIJING'

SELECT PERSON.LASTNAME,ORDER.ORDERNO INTO PERSON)ORDER BACKUP FROM PERSONS INNER JOIN ORDERS  ON PERSN.IDp-=ORSER=IDP

>>SQL CREATE DATABASE

CREATE DATABASE NAME

>>SQL CREATE TABLE

INTRGER INT SMALLINT TINYINT (MAX SITES)

DECIMAL(SIZE,D),NUMERIC(SIZE,D)

CHAR(SIZE) VARCHAR(SIZE)

DATA(YYYYMMDD)

EG:

CREATE TABLE PERSONS{ID_P INT,LASTNAME VARCHAR (255),FIRSTNAME VARCHAR(255), ADDRESS VARCHAR(255),CITY VARCHAR(255))

>>SQL NOT NULL

NOT RECEIVE NULL VALUE

>>SQL UNIQUE

UNIQUE 约束唯一标识数据库表中的每条记录。

UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。

PRIMARY KEY 拥有自动定义的 UNIQUE 约束。

请注意，每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。

MYSQL:

CREATE TABLE PEROSON(ID_P INT NOT NULL,LASTNAME VARCHAR(255) NOT NULL,FIRST NAMEVARCHAR(355),UNIQUE(ID_P)

SQL SERVER/ORACLE/MS ACCESS:

CREATE TABLE PERSONS(ID_P INT NOT NULL UNIQUE...)

MYSQL/ORACLE/SQL SERVER/ACCESS

CREATE TABLE PERSONS (..... CONSTRAINT UC_PERSONID UNIQIE(ID_P,LASTNAME)

ALTER TABLE PERSONS(ADD UNIQUE UD_P)

MYSQL:ALTER TABLE PERSONS DROP INDEX UC_PERSONID

SQL SERVER/ORACLE/MS ACCESS: ALTER TABLE PERSONS DROP CONSTRAINT UC_PERSONID

>>SQL PRIMARY KEY

MYSQL:

CREATE TABLE PERSONS (.....PRIMARY KEY (ID_P)

SQL SERVER/ORACLE/ACCESS(ID_P INT NOT NULL  PRIMARY KEY...)

CONSTRAINT PK_PERSONID PRIMARY KEY (ID_P,LASTNAME)

>.SQL FOREIGN KEY

MYSQL: FOREIGN NAME(VALUE) REFERENCE NAME2

 CREATE TABLE ORDERS(.... PRIMARY KEY(ID_O), FOREIGN KEY (ID_P) REFERENCE PERSONS(ID)

SQL SERVER/ORACLE/MS ACCESS

CREATE TABLE ORSERS(...ID_P INT FOREIGN KEY REFERENCE PERSON(ID_P)

>.SQL CHECK

 ....CHECK(ID_P>0)

CONSTRAINT CHK_NAME CHECK (ID 0>.....)

ALTER TABLE NAME ADD CHECK (ID_P>0)

>.SQL DEFAULT

....DEFAULT ..

>>SQL CREATE INDEX

CREATE  INDEX  INDEX_NAME ON TABLENAME(COLUME NAME)

CREATE UNIQUE INDEX INDEXNAME  ON TABLENAME (COLUME NAME)

CREATE INDEX INDEXNAME ON TABLENAME(COLUME NAME DESC)

>>SQL DROP INDEX

 是一门 ANSI 的标准计算机语言，用来访问和操作数据库系统。