数据库 所有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 内链接

复合条件连接

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. 若视图的字段来自字段表达式或常数,则不允许对此视图执行INSETUPDATE 操作, 但允许DELETE 操作
    3. 若视图的字段来自聚集函数,则此视图不允许更新
    4. 若视图定义中 含有 GROUP BY 子句, 则此视图不允许更新
    5. 若视图定义中含有 DISTINCT 短语, 则此视图不允许更新
    6. 若视图定义中有嵌套查询,并且内层查询的 FROM 子句中涉及的表也是导出该视图的基本表,则此视图不允许更新。
    7. 一个不允许更新的视图上定义的视图也不允许更新
  • 视图的作用
    • 视图能简化用户的操作
    • 视图使用户能以多种角度看待同一数据
    • 视图对重构数据库提供了一定程度的逻辑独立性
    • 视图能够对机密数据提供安全保护
    • 适当的利用视图可以更清晰的表达查询



数据库性能调优

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

最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 211,884评论 6 492
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 90,347评论 3 385
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 157,435评论 0 348
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 56,509评论 1 284
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 65,611评论 6 386
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 49,837评论 1 290
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 38,987评论 3 408
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 37,730评论 0 267
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 44,194评论 1 303
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 36,525评论 2 327
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,664评论 1 340
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 34,334评论 4 330
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 39,944评论 3 313
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,764评论 0 21
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,997评论 1 266
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 46,389评论 2 360
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 43,554评论 2 349

推荐阅读更多精彩内容