作者:牛客网 CZ???QM ID:476805 大约时间:2018 年

SQL 基本语句

SQL 分类:

DDL —数据定义语言 (CREATE , ALTER , DROP , DECLARE)

DML —数据操纵语言 (SELECT , DELETE , UPDATE , INSERT)

DCL —数据控制语言 (GRANT , REVOKE , COMMIT , ROLLBACK)

首先 , 简要介绍基础语句:

1 、说明:创建数据库

CREATE DATABASE DATABASE-NAME

2 、说明:删除数据库

DROP DATABASE DBNAME

3 、说明:备份 SQL SERVER

— 创建 备份数据的 DEVICE

USE MASTER

EXEC SP_ADDUMPDEVICE ‘DISK’, ‘TESTBACK’, ‘C:\MSSQL7BACKUP\MYNWIND_1.DAT’

— 开始 备份

BACKUP DATABASE PUBS TO TESTBACK

4 、说明:创建新表

CREATE TABLE TABNAME(COL1 TYPE1 [NOT NULL][primary key],COL2 TYPE2 [NOT NULL],..)

根据已有的表创建新表:

A : CREATE TABLE TAB_NEW LIKE TAB_OLD ( 使用旧表创建新表 )

B : CREATE TABLE TAB_NEW AS SELECT COL1,COL2 … FROM TAB_OLD DEFINITION ONLY

5 、说明:删除新表 DROP TABLE TABNAME

6 、说明:增加一个列 ALTER TABLE TABNAME ADD COLUMN COL TYPE

注:列增加后将不能删除。 DB2 中列加上后数据类型也不能改变,唯一能改变的是增加 VARCHAR 类型的长度。

7 、说明:添加主键: ALTER TABLE TABNAME ADD PRIMARY KEY(COL)

说明:删除主键: ALTER TABLE TABNAME DROP PRIMARY KEY(COL)

8 、说明:创建索引: CREATE [UNIQUE] INDEX IDXNAME ON TABNAME(COL … .)

删除索引: DROP INDEX IDXNAME

注:索引是不可更改的,想更改必须删除重新建。

9 、说明:创建视图: CREATE VIEW VIEWNAME AS SELECT STATEMENT

删除视图: DROP VIEW VIEWNAME

10 、说明:几个简单的基本的 SQL 语句

选择: SELECT * FROM TABLE1 WHERE 范围

插入: INSERT INTO TABLE1(FIELD1,FIELD2) VALUES(VALUE1,VALUE2)

删除: DELETE FROM TABLE1 WHERE 范围

更新: UPDATE TABLE1 SET FIELD1=VALUE1 WHERE 范围

查找: SELECT * FROM TABLE1 WHERE FIELD1 LIKE ’ %VALUE1% ’ —LIKE 的语法很精妙,查资料 !

排序: SELECT * FROM TABLE1 ORDER BY FIELD1,FIELD2 [DESC]

总数: SELECT COUNT * AS TOTALCOUNT FROM TABLE1

求和: SELECT SUM(FIELD1) AS SUMVALUE FROM TABLE1

平均: SELECT AVG(FIELD1) AS AVGVALUE FROM TABLE1

最大: SELECT MAX(FIELD1) AS MAXVALUE FROM TABLE1

最小: SELECT MIN(FIELD1) AS MINVALUE FROM TABLE1

11 、说明:几个高级查询运算词

A : UNION 运算符

UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2 )并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL ),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2 。

B : EXCEPT 运算符

EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL) ,不消除重复行。

C : INTERSECT 运算符

INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL) ,不消除重复行。

注:使用运算词的几个查询结果行必须是一致的。

12 、说明:使用外连接

A 、 LEFT OUTER JOIN :

左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。

SQL: SELECT A.A, A.B, A.C, B.C, B.D, B.F FROM A LEFT OUT JOIN B ON A.A = B.C

B : RIGHT OUTER JOIN:

右外连接 ( 右连接 ) :结果集既包括连接表的匹配连接行,也包括右连接表的所有行。

C : FULL OUTER JOIN :

全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。

SQL 子查询语句

1 、单行子查询

SELECT ENAME,DEPTNO,SAL

FROM EMP

WHERE DEPTNO=(SELECT DEPTNO FROM DEPT WHERE LOC='NEW YORK');

2 、多行子查询

SELECT ENAME,JOB,SAL

FROM EMP

WHERE DEPTNO IN ( SELECT DEPTNO FROM DEPT WHERE DNAME LIKE 'A%');

3 、多列子查询

SELECT DEPTNO,ENAME,JOB,SAL

FROM EMP

WHERE (DEPTNO,SAL) IN (SELECT DEPTNO,MAX(SAL) FROM EMP GROUP BY DEPTNO);

4 、内联视图子查询

(1)SELECT ENAME,JOB,SAL,ROWNUM

FROM (SELECT ENAME,JOB,SAL FROM EMP ORDER BY SAL);

(2)SELECT ENAME,JOB,SAL,ROWNUM

FROM ( SELECT ENAME,JOB,SAL FROM EMP ORDER BY SAL)

WHERE ROWNUM<=5;

5 、在 HAVING 子句中使用子查询

SELECT DEPTNO,JOB,AVG(SAL) FROM EMP GROUP BY DEPTNO,JOB HAVING AVG(SAL)>(SELECT SAL FROM EMP WHERE ENAME='MARTIN');

6 、内连接 左连接 右连接举例;

SELECT SYS_USER.USER_ID ,SYS_USER.USER_CODE FROM SYS_USER INNER JOIN XZFW_BANJIE ONSYS_USER.USER_ID=XZFW_BANJIE.USERID;

小例子:

SELECT TOP 10 * FROM SYS_USER WHERE USER_CODE NOT IN (SELECT USER_CODE FROM SYS_USER WHERE USER_CODE LIKE '%YZJ%');

SELECT TOP 2 _ FROM (SELECT TOP 2 _ FROM TD.USERS ORDER BY US_USERNAME DESC) USERS ORDER BY US_USERNAME DESC;

7 、删除约束语句:

ALTER TABLE DBO.XZFW_SYS_USER DROP CONSTRAINT FK1772E1891324F678;

8 、记录数查询

SELECT COUNT(USER_PASS) FROM SYS_USER;

SELECT COUNT(*) FROM SYS_USER WHERE USER_CODE!='ADMIN';

9 、在范围之间取值 ( BETWEEN … AND .. 用法 )

SELECT SYS_USER.USER_ID,SYS_USER.USER_NAME,XZFW_SHOUJIAN.CASEID FROM SYS_USER INNER JOIN 
XZFW_SHOUJIAN ON SYS_USER.USER_ID=XZFW_SHOUJIAN.USERID WHERE USER_ID BETWEEN 5 AND 100;

 SELECT * FROM SYS_USER WHERE USER_ID<10 AND USER_ID>1;

10 、 三表查询实例:(三张表为: USER_DETAILS , SUBJECT , SCORE )

SELECT USER_DETAILS.USER_NAME,SUBJECT.SUBJECTNAME,SCORE.SCORE FROM USER_DETAILS INNER JOIN SCOREON
USER_DETAILS.USER_ID=SCORE.USER_ID INNER JOIN SUBJECT ON SCORE.SUBJECTID=SUBJECT.SUBJECTIDWHERE USER_DETAILS.USER_ID=1;

常用查询举例

SELECT * FROM DBO.USER_DETAILS WHERE USER_NAME='CHEERS LI' AND USER_POSITION='SQE'

SELECT * FROM DBO.USER_DEPT

SELECT * FROM DBO.USER_DETAILS

SELECT TOP 3* FROM DBO.USER_DETAILS INNER JOIN DBO.USER_DEPT ONUSER_DETAILS.USER_DEPT_ID=DBO.USER_DEPT.USER_DEPT_ID

INSERT INTO DBO.USER_DEPT (USER_DEPT_ID,USER_DEPT_NAME)VALUES('QE_01','SOFTWARE QUALITY ENGINEER')

UPDATE USER_DEPT SET USER_DEPT_ID='QE_02' WHERE USER_DEPT_NAME='QUALITY CONTROL'

DELETE FROM DBO.USER_DEPT WHERE USER_DEPT_ID='QE_01'

SELECT DBO.USER_DETAILS.USER_NAME,DBO.USER_DETAILS.USER_AGE,DBO.USER_DEPT.USER_DEPT_NAME,USER_DEPT.USER_DEPT_ID
FROM DBO.USER_DETAILS RIGHT JOIN DBO.USER_DEPT ON USER_DETAILS.USER_DEPT_ID=DBO.USER_DEPT.USER_DEPT_ID

SELECT COUNT(USER_NAME)FROM DBO.USER_DETAILS WHERE USER_NAME='CHEERS LI'

ALTER TABLE USER_DEPT ADD TESTCOLUMN CHAR

ALTER TABLE USER_DEPT DROP COLUMN TESTCOLUMN

SELECT TOP 3* FROM(SELECT TOP 3* FROM DBO.USER_DETAILS WHERE USER_DEPT_ID='DEV_01' ORDER BYUSER_AGE DESC)AA ORDER BY USER_ID DESC

SELECT * FROM DBO.USER_DETAILS WHERE USER_NAME=(SELECT MAX(USER_NAME) FROMDBO.USER_DETAILS)

补充常用语句

SELECT EMPLOYEES.EMPLOYEE_ID,EMPLOYEES.FIRST_NAME,EMPLOYEES.LAST_NAME,SALARY*(1+0.1) NEW_SALARY FROM HR.EMPLOYEES;

SELECT EMPLOYEE_ID,FIRST_NAME FROM HR.EMPLOYEES WHERE FIRST_NAME LIKE 'B%';

SELECT COUNT(*) FROM HR.EMPLOYEES WHERE FIRST_NAME LIKE 'B%';

SELECT JOB_ID,AVG(SALARY),SUM(SALARY),MAX(SALARY),COUNT(*) FROM HR.EMPLOYEES GROUP BY JOB_ID;

其次,大家来看一些不错的 SQL 语句

1 、说明:复制表 ( 只复制结构 , 源表名: A 新表名: B) (ACCESS 可用 )

法一: SELECT * INTO B FROM A WHERE 1<>1

法二: SELECT TOP 0 * INTO B FROM A

2 、说明:拷贝表 ( 拷贝数据 , 源表名: A 目标表名: B) (ACCESS 可用 )

INSERT INTO B(A, B, C) SELECT D,E,F FROM B;

3 、说明:跨数据库之间表的拷贝 ( 具体数据使用绝对路径 ) (ACCESS 可用 )

INSERT INTO B(A, B, C) SELECT D,E,F FROM B IN ‘具体数据库’ WHERE 条件

例子: ..FROM B IN ‘“&SERVER.MAPPATH(“.”)&”\DATA.MDB” &”’ WHERE..

4 、说明:子查询 ( 表名 1 : A 表名 2 : B)

SELECT A,B,C FROM A WHERE A IN (SELECT D FROM B ) 或者 : SELECT A,B,C FROM A WHERE A IN (1,2,3)

5 、说明:显示文章、提交人和最后回复时间

SELECT A.TITLE,A.USERNAME,B.ADDDATE FROM TABLE A,(SELECT MAX(ADDDATE) ADDDATE FROM TABLE WHERE TABLE.TITLE=A.TITLE) B

6 、说明:外连接查询 ( 表名 1 : A 表名 2 : B)

SELECT A.A, A.B, A.C, B.C, B.D, B.F FROM A LEFT OUT JOIN B ON A.A = B.C

7 、说明:在线视图查询 ( 表名 1 : A )

SELECT * FROM (SELECT A,B,C FROM A) T WHERE T.A > 1;

8 、说明: BETWEEN 的用法 ,BETWEEN 限制查询数据范围时包括了边界值 ,NOT BETWEEN 不包括

SELECT * FROM TABLE1 WHERE TIME BETWEEN TIME1 AND TIME2

SELECT A,B,C, FROM TABLE1 WHERE A NOT BETWEEN 数值 1 AND 数值 2

9 、说明: IN 的使用方法

SELECT * FROM TABLE1 WHERE A [NOT] IN ( ‘值 1 ’ , ’值 2 ’ , ’值 4 ’ , ’值 6 ’ )

10 、说明:两张关联表,删除主表中已经在副表中没有的信息

DELETE FROM TABLE1 WHERE NOT EXISTS ( SELECT * FROM TABLE2 WHERE TABLE1.FIELD1=TABLE2.FIELD1 )

11 、说明:四表联查问题:

SELECT * FROM A LEFT INNER JOIN B ON A.A=B.B RIGHT INNER JOIN C ON A.A=C.C INNER JOIN D ON A.A=D.D WHERE …..

12 、说明:日程安排提前五分钟提醒

SQL: SELECT * FROM 日程安排 WHERE DATEDIFF(‘MINUTE’,F 开始时间 ,GETDATE())>5

13 、说明:一条 SQL 语句搞定数据库分页

SELECT TOP 10 B.* FROM (SELECT TOP 20 主键字段 , 排序字段 FROM 表名 ORDER BY 排序字段 DESC) A, 表名 B WHERE B. 主键字段 = A. 主键字段 ORDER BY A. 排序字段

14 、说明:前 10 条记录

SELECT TOP 10 * FORM TABLE1 WHERE 范围

15 、说明:选择在每一组 B 值相同的数据中对应的 A 最大的记录的所有信息 ( 类似这样的用法可以用于论坛每月排行榜 , 每月热销产品分析 , 按科目成绩排名 , 等等 .)

SELECT A,B,C FROM TABLENAME TA WHERE A=(SELECT MAX(A) FROM TABLENAME TB WHERE TB.B=TA.B)

16 、说明:包括所有在 TABLEA 中但不在 TABLEB 和 TABLEC 中的行并消除所有重复行而派生出一个结果表

(SELECT A FROM TABLEA ) EXCEPT (SELECT A FROM TABLEB) EXCEPT (SELECT A FROM TABLEC)

17 、说明:随机取出 10 条数据

SELECT TOP 10 * FROM TABLENAME ORDER BY NEWID()

18 、说明:随机选择记录

SELECT NEWID()

19 、说明:删除重复记录

DELETE FROM TABLENAME WHERE ID NOT IN (SELECT MAX(ID) FROM TABLENAME GROUP BY COL1,COL2,…)

20 、说明:列出数据库里所有的表名

SELECT NAME FROM SYSOBJECTS WHERE TYPE=’U’

21 、说明:列出表里的所有的

SELECT NAME FROM SYSCOLUMNS WHERE ID=OBJECT_ID(‘TABLENAME’)

22 、说明:列示 TYPE 、 VENDER 、 PCS 字段,以 TYPE 字段排列, CASE 可以方便地实现多重选择,类似 SELECT 中的 CASE 。

SELECT TYPE,SUM(CASE VENDER WHEN ‘A’ THEN PCS ELSE 0 END),SUM(CASE VENDER WHEN ‘C’ THEN PCS ELSE 0 END),SUM(CASE VENDER WHEN ‘B’ THEN PCS ELSE 0 END) FROM TABLENAME GROUP BY TYPE

显示结果:

TYPE VENDER PCS

电脑 A 1

电脑 A 1

光盘 B 2

光盘 A 2

手机 B 3

手机 C 3

23 、说明:初始化表 TABLE1

TRUNCATE TABLE TABLE1

24 、说明:选择从 10 到 15 的记录

SELECT TOP 5 _ FROM (SELECT TOP 15 _ FROM TABLE ORDER BY ID ASC) TABLE_ 别名 ORDER BY ID DESC

数据库基本理论整理

通俗地理解三个范式

通俗地理解三个范式,对于数据库设计大有好处。在数据库设计中,为了更好地应用三个范式,就必须通俗地理解三个范式 ( 通俗地理解是够用的理解,并不是最科学最准确的理解 ) :

第一范式: 1NF 是对属性的原子性约束,要求属性具有原子性,不可再分解;

第二范式: 2NF 是对记录的惟一性约束,要求记录有惟一标识,即实体的惟一性;

第三范式: 3NF 是对字段冗余性的约束,即任何字段不能由其他字段派生出来,它要求字段没有冗余。

没有冗余的数据库设计可以做到。但是,没有冗余的数据库未必是最好的数据库,有时为了提高运行效率,就必须降低范式标准,适当保留冗余数据。具体做法是:在概念数据模型设计时遵守第三范式,降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段,允许冗余。

基本表及其字段之间的关系 , 应尽量满足第三范式。但是,满足第三范式的数据库设计,往往不是最好的设计。为了提高数据库的运行效率,常常需要降低范式标准:适当增加冗余,达到以空间换时间的目的。