数据库 所有SQL语句收集

模式

定义模式实际上定义了一个命名空间，在这个空间中 可以进一步 定义该模式包含的数据对象，例如基本表，视图，索引等

• 创建模式
  CREATE SCHEMA <模式名> AUTHORIZATION 用户名

• 定义模式的同时，创建表，视图，定义授权
  CREATE SCHEMA <模式名> AUTHORIZATION <用户名> [<表定义子句> | <视图定义子句>|<授权定义子句>]

• 删除模式
  DROP SCHEMA <模式名> <CASCADE | RESTRICT>
  CASCADE 级联
  RESTRICT 限制

---

表创建修改删除

• 创建表
  CREATE TABLE <表名>(<列名> <数据类型> [列级完整性约束条件] [,<列名> <数据类型> [列级完整性约束]] [,<表级完整性约束条件>]);

• 修改表：
  ALTER TABLE <表名> [ADD <新列名> <数据类型> [完整性约束]] [DROP <完整性约束名>] [ALTER COLUMN <列名> <数据类型>]

  • ADD: 添加新列和新的完整性约束条件
    ALTER TABLE CategoriesNew ADD Commission money null
  • DROP: 用户删除指定的完整性约束条件， 也可以用来删除列
    ALTER TABLE CategoriesNew DROP COLUMN Sales_date
  • ALTER COLUMN 用户修改原有的列定义，包括修改列名和数据类型
    ALTER TABLE CategoriesNew ALTER COLUMN name varchar(20) NOT NULL

• 删除表：
  DROP TABLE <表名> [RESTRICT|CASCADE]

• 清空表数据：
  truncate table 表名称

其他操作

• 重命名表：
  exec sp_rename newPerson ,somePerson
• 修改列名：
  exec sp_rename 'somePerson.name',newName ,'column'

复制表：

• A. 复制整张表：(同时复制 表结构和 数据)
  select * into new_table from old_table

• B. 复制表结构：
  select * into new_table from old_table where 1=2

• C. 复制表内容：
  insert into new_tab select * from old_table

• . 修改identity列
  自增列不能直接修改，必须将原有ID列删除，然后重新添加一列具有identity属性的ID字段。比如你要修改的字段名为ID：

alter table 表名 drop column ID
alter table 表名 add ID int identity(1,1)

---

视图

• 建立视图：
  CREATE VIEW 视图名 AS SELECT 列名1，列名2.列名3 FROM Student WHERE 条件

---

索引

• 建立索引
  CREATE [UNIQUE][CLUSTER] INDEX <索引名> ON <表名> (<列名> [<次序>] [,<列名> [<次序>]]...);

例：
CREATE UNIQUE INDEX Stusno ON Student(Sno);
CREATE UNIQUE INDEX Coucno ON Course(Cno);
CREATE UNIQUE INDEX SCno ON SC(Sno ASC, Cno DESC)

• 删除索引：
  DROP INDEX <索引名>

索引实现的方式
B+ 树 动态平衡
HASH 索引 查找速度快

关于 GROUP BY 和聚合函数

• 数据库中最简单最原始的聚合函数

如果语句中有group by关键字，那么select后只能跟group by后出现的列，或者是聚合函数
max() min() count(),sum(),avg()

---

表操作

单表数据查询

• 一般格式：
  SELECT [ALL|DISTINCT] <目标列表达式> [,<目标列表达式>]... FROM <表名或视图名>[,<表名或视图名>]... [WHERE <条件表达式>] [GROUP BY <列名1> [HAVING <条件表达式>]] [ORDER BY <列名2> [ASC|DESC]];

• 单表查询

  • 查询指定列
    SELECT Sno,Sname FROM Student

  • 查询全部列
    SELECT * FROM Student

  • 查询经过计算的值
    SELECT Sname, 2004-Sage FROM Student

  • 指定别名
    SELECT Sname 姓名, 'Year of Birth:' 生日, 2004-Sage 年龄， LOWER(Sdept) 部门

  • 消除重复行
    SELECT DISTINCT Sno FROM SC

查询满足条件的元祖 where

• 比较大小

= 等于 , > 大于, <小于, >= 大于等于, <= 小于等于, != 或 <> 不等于, !> 不大于, !< 不小于

• 确定范围

  BETWEEN...(下限)AND...(上限) 在什么范围内
NOT BETWEEN...AND... 不在范围之内

• 确定集合 in 属性属于指定集合

• 字符匹配 [NOT] LIKE <匹配串> [ESCAPE <转义字符>]

  • % 任意长度的字符串(包括0)
  • _ 任意单个字符
  • ESCAPE 转义字符： 如 WHERE Cname LIKE 'DB\_%I__'ESCAPE'\'

• 涉及空值的查询 WHERE Grade IS NULL

• 多重条件查询 AND OR

• 排序 ORDER BY

默认是升序 ASC
DESC 降序

• 聚集函数 （主要用于服务 GROUP BY）
  COUNT([DISTINCT | ALL]) * 统计元祖个数
  COUNT([DISTINCT | ALL] <列名>) 统计列个数
  SUM([DISTINCT | ALL] <列名>) 列综合（数值型）
  AVG([DISTINCT | ALL]<列名>) 列平均值 （数值型）
  MAX([DISTINCT | ALL] <列名>) 列 最大值
  MIN([DISTINCT | ALL] <列名>) 列 最小值
• 分组 GROUP BY

---

多表数据查询

链接查询

• 等值查询 和 非等值查询

  • 等值查询: SELECT 表1的列，表2的列 FROME 表1, 表2 WHERE 表1.列=表2.列

  查询 表一 和 表二 某个列相等条件下 的表信息(从表1 和 表2 摘出一些列 组成新的表)

• 非等值查询: SELECT 表1的列，表2的列 FROME 表1, 表2 WHERE 表1.列>表2.列

  查询 表一 的某个列 大于表2的某个列条件下 的表信息(从表1 和 表2 摘出一些列 组成新的表)
  非等值查询的比较运算符有： >,<,!=,<>,!>,!<

• 自身链接 （自己链接自己）
  如：
  SELECT FIRST.Cno, SECOND.Cpno FROM Course FIRST, Course SECOND WHERE FIRST.Cpno=Second.Cno

---

外链接

• LEFT JOIN 左链接
• RIGHT JOIN 右链接
• INNER JOIN 内链接

• 详解SQL Server连接（内连接、外连接、交叉连接）
• SQL Server之连接

复合条件连接

AND OR

嵌套查询

• 带有IN 谓词的子查询
  如：SELECT Sname FROM Student WHERE Sno IN (SELECT Sno From SC WHERE Cno=2)

子查询条件 不依赖于父查询， 这类子查询称为不相关子查询
如果子查询的查询条件依赖于父查询，这种查询 称为 相关子查询

• 带有比较运算符的子查询
  如： SELECT Sno,Sname,Sdept FROM Student WHERE Sdept = (SELECT Sdept From Student WHERE Sname='刘晨')

子查询一定要放到 比较运算符后面

相关子查询的例子：
SELECT Sno,Cno FROM SC x WHERE Grade >= (SELECT AVG(Grade) FROM SC y WHERE y.Sno = x.Sno)

执行顺序

1. 外查询中取一个 Sno 传递给 内查询
2. 经过内查询 得到 平均值 88
3. 最后回到外查询， 得到最终结果

• 带有 ANY(SOME) 或 ALL 谓词的子查询
  > ANY 大于子查询结果中的某个值
  > ALL 大于子查询结果中的所有值
  < ANY 小于子查询结果中的某个值
  < ALL 小于子查询结果中的所有值
  >= ANY 大于等于子查询结果中的某个值
  >= ALL 大于等于子查询中的所有值
  <= ANY 小于等于子查询中的某个值
  <= ALL 小于等于子查询结果中的所有值
  = ANY 等于子查询结果中的某个值
  = ALL 等于子查询结果中的所有值（通常没有实际意义）
  != 或 <> ANY 不等于子查询结果中的某个值
  != 或 <> ALL 不等于子查询结果中的任何一个值

如：SELECT Sname,Sage FROM Student WHERE Sage<ANY (SELECT Sage FROM Student WHERE Sdept='CS')

	=	<>或者 !=	<	<=	>	>=
ANY	IN	--	<MAX	<=MAX	>MIN	>=MIN
ALL	--	NOT IN	<MIN	<=MIN	>MAX	>=MAX

表：ANY(或SOME)，ALL 谓词与聚集函数，IN 谓词的等价转换关系

• 带有 EXISTS （NOT EXISTS）谓词的子查询

EXISTS 代表存在量词 ，带有 EXISTS 谓词的子查询不返回任何数据， 只产生逻辑真值“ture” 或 逻辑假值"false"
如： SELECT Sname FROM Student WHERE EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno='1')

• 集合查询: 并集 UNION,交集INTERSECT,差EXCEPT

  SELECT 语句1 UNION SELECT 语句2
  如： SELECT * FROME Student Where Sdept='CS' UNION SELECT * FROM Student WHERE Sage<=19

---

---

表更新

• 插入数据

  • 插入元祖
    INSERT INTO <表名> (<属性列1>，[<属性列2 >...)] VALUES (<常量1>[，<常量2>]...)
    如： INSERT INTO Student(Sno,Sname,Ssex,Sdept,Sage) VALUES ('201710093','王斌斌','男','IS',18)

  • 插入子查询结果
    INSERT INTO <表名> (<属性列1>[,<属性列2>...])
    如： INSERT INTO Dept_age(Sdep,Avg_age) SELECT Sdept,AVG(Sage) FROM Student GROUP BY Sdept

• 修改表 UPDATE <表名> SET <列名>=<表达式>[,<列名=<表达式>]...[WHERE <条件>]

  • 修改某一个元祖的值
    如： UPDATE Student SET Sage=22 WHERE Sno='201710092'
  • 修改多个元祖的值
    如：UPDATE Student SET Sage=Sage+1
  • 带子查询的修改语句
    如：UPDATE SC SET Grade = 0 WHERE 'CS'=(SELECT Sdept FROM Student WHERE Student.Sno = SC.Sno)

• 删除数据
  DELETE FROM <表> [WHERE <条件>]

  • 删除某一个元祖的值
    DELECT FROM Student WHERE Sno='201710092'
  • 删除多个元祖的值
    DELETE FROM SC
  • 带子查询的删除语句
    DELETE FROM SC WHERE 'CS'=(SELECT Sdept FROM Student WHERE Student.Sno=SC.Sno)

---

---

视图

• 建立视图 CREATE VIEW <视图名> [(<列名>[,<列名>]...)] AS <子查询> [WITH CHECK OPTION]
  如： CREATE VIEW IS_Student AS SELECT Sno,Sname,Sage FROM Student WHERE Sdept='IS'
  • 不许有 ORDER BY
  • 不许有 DISTINCT

WITH CHECK OPTION 表示对视图进行UPDATE,INSERT 和 DELETE操作时，要保证更新，插入或者删除的行满足视图定义中的谓词条件（即子查询中的条件表达式）

组成视图的属性列名，要么全部省略 要么全部指定。

下面三种情况 必须指明所有列名

1. 某个目标列不是单纯的属性名，而是聚集函数或数列表达式
   如：CREATE VIEW BT_S(Sno,Sname,Sbirth) AS SELECT Sno,Sname,2017-Sage FROM Student
2. 多表链接时选出了几个同名列作为视图的字段
   如： CREATE VIEW IS_SI(Sno,Sname,Grade) AS SELECT Student, Sno,Sname,Grade FROM Student,SC WHERE Sdept='IS' AND Student.Sno=SC.Sno AND SC.Cno='1'
3. 需要在视图中卫某个列启用新的更合适的名字

• 删除视图 DROP VIEW <视图名> [CASCADE]

  如：DROP VIEW IS_SI CASCADE

• 查询视图 （同查表）

• 更新视图 （同更新表）

  1. 若视图是由两个以上基本表导出的，则此视图不允许更新
  2. 若视图的字段来自字段表达式或常数，则不允许对此视图执行INSET 和 UPDATE 操作， 但允许DELETE 操作
  3. 若视图的字段来自聚集函数，则此视图不允许更新
  4. 若视图定义中 含有 GROUP BY 子句， 则此视图不允许更新
  5. 若视图定义中含有 DISTINCT 短语， 则此视图不允许更新
  6. 若视图定义中有嵌套查询，并且内层查询的 FROM 子句中涉及的表也是导出该视图的基本表，则此视图不允许更新。
  7. 一个不允许更新的视图上定义的视图也不允许更新

• 视图的作用

  • 视图能简化用户的操作
  • 视图使用户能以多种角度看待同一数据
  • 视图对重构数据库提供了一定程度的逻辑独立性
  • 视图能够对机密数据提供安全保护
  • 适当的利用视图可以更清晰的表达查询

---

---

---

• 数据库工程师考试自学（7）---函数依赖与多值依赖

数据库性能调优

《数据库性能调优：原理与技术》
英文名
《Database Tuning:Principles,Experiments,and Troubleshooting Techniques》

• SQL中IN和EXISTS用法的区别，sql中exists,not exists的用法