SQL基础

第一章

  1. 数据库
    数据库是长期存储在计算机内的,有组织的,可共享的大量数据的集合

  2. 数据库管理系统
    数据库管理系统位于用户和操作系统之间,是一种曹总和管理数据库的大型软件,用于建立,使用的维护数据库

  3. 数据库系统
    数据库系统通常由硬件,软件,数据库和用户组成,管理的对象是数据

  4. 数据库管理系统基本系统包含如下四大模块
    存储管理,数据库管理系统通常回字形配置磁盘空间,将数据存入存储装置的数据库
    查询处理,负责处理用户下达的查询命令语句
    事务管理,保障数据库商业事务的操作需要
    恢复管理,主要是日志管理子系统,负责记录数据库的所有操作

  5. 数据库系统主要包括如下组件
    用户,数据,软件,硬件

  6. 数据库系统的体系结构

    1. 集中式结构
    2. C/S结构
    3. B/S结构
    4. 分布式结构
  7. 常见的数据库管理系统软件
    Qracle, SQL, DB2等

  8. 三种关系运算与特点

    1. 选择:用于查找符合条件的行,从而形成新关系,记录行变少。

    2. 投影:从一个表中找出若干字段形成新表的操作,新旧表的行一样多,数据列变少

    3. 连接:从两个表中选择满足条件的记录

  9. 概念模型的表示方法
    常用的有『E-R模型』
    『E-R模型』由『实体型』,『实体属性』,『实体间的联系』三种概念单元表示

  10. 数据模型

    1. 概念:在概念模型基础上建立使用与数据库层的模型成为数据模型

    2. 三要素:数据结构,数据操作,完整性约束

    3. 常用的数据模型: 层析模型,网状模型,关系模型,面向对象模型

  11. 实体的概念

    1. 实体:客观存在并相互区分的事物叫实体
    2. 属性:属性是实体所具有的某些特性,通过属性对实体进行描述,实体是由属性组成的
    3. 主键:一个实体往往有多个属性,如果有一个属性或者多个属性构成的子集能够唯一标识整个属性集合,则称该属性子集为属性集合的主键
    4. 实体型:用实体名及其属性名集合来抽象刻画同类实体,成为实体型
    5. 实体集:同型实体的集合成为实体集
    6. 联系:『例如』教师实体和学生实体之间存在着教和学的联系
  12. 实体之间的联系
    1. 一对一
    2. 一对多
    3. 多对多
  13. 关系数据库的完整性
    1. 实体完整性:关系的主关键字不能取『空值』。关系对应现实世界中的一个实体集,实体应具有唯一标识。在关系模式中主关键字是唯一标识,而主关键字的属性不能为空,否则表明关系模式中存在着不可标识的实体,这样的实体就不是一个完整实体
    2. 域完整性:域完整性确保属性中只允许一个有效数据。域是属性可能值的范围,是否为空值也是域完整性的一部分
    3. 参照完整性:参照完整性是定义建立关系之间联系的主关键字与外部关键字引用的约束条件

第二章

  1. 什么是 SQL 2008 实例
    SQL 2008 实例 实际上就是虚拟的 SQL 服务器。每个实例包括一组私有的程序和数据文件,同时也可以和其他实例共用一组共享程序或文件
    功能:可以常见数据库及其对象,实现安全性,对服务器对象,复制和SQL代理等进行管理
    类型:默认实例,命名实例。默认实例只能有一个,命名实例可以有多个

  2. SQL 2008 的服务器与客户端的关系
    客户端:指的是一些适合家庭,实验室,办公环境下使用的安装了一些共享网络服务的PC
    服务器:是指具有适应大容量的数据存储和频繁的客户端的访问操作的计算机
    关系:客户端通过网络将要求传递给服务器,服务器按客户端要求返回结果

  3. SQL 2008 系统数据库的主要功能

    1. master 数据库:SQL系统最重要的数据库,记录SQL系统的所有系统信息
    2. model 数据库:在SQL实例上穿件所有数据库的模板
    3. msdb 数据库:代理服务数据库,为其报警,任务调度和记录操作员的操作提供存储空间
    4. tempdb 数据库:为临时表,临时存储过程及其他临时操作提供存储空间的临时数据库
    5. resource 系统数据库:隐形只读数据库

第三章

  1. SQL 2008 中文件组的作用和分类
    作用

    1. 对于大型数据库,若硬件设置上需要多个磁盘驱动空间,就可以把特定的对象或文件分配到不同的磁盘上,将数据库文件组织成用户文件组
    2. 帮助数据库管理人员执行相应的数据布局,以及某些管理任务。
    3. 可以再特定的文件中定位特定的对象,从而将频繁查询和频繁修改的文件分离出来,提高磁盘驱动器的效率

    分类

    1. 主文件组,包含主数据文件和任何没有明确分配给其他文件组的其他文件
    2. 用户定义文件组
  2. 事务日志文件与数据文件分别存放的优点
    当存储数据文件或日志文件的磁盘发生损坏,可以采取相应的措施保护数据

  3. 数据库的创建与修改

    创建数据库 create database name
    数据库名 name
    数据文件所在位置 filename
    初始容量 size
    最大容量 maxsize (无限大:unlimited)
    增长量 filegrowth
    添加文件 add file
    添加日志文件 add log file
    将文件添加到文件组 to filegroup
    修改数据库 alter database
    数据库更名 modify name = ’new’
    删除数据库 drop database name
  4. 添加多个数据文件或日志文件时,用括号来进行分割

  5. 数据库所有者
    有权限访问数据库的用户,数据库所有者是唯一的

  6. 架构
    架构是形成单个命名空间的数据库实体的集合

  7. 数据库文件

    1. 主数据文件:包含数据库的启动信息,并指向数据库中的其他文件。每个数据库只有一个主要数据文件,扩展名为『.mdf』
    2. 次要数据文件:次要数据文件是可选的,由用户定义并存储用户数据,次要文件可用于将数据分散到多个磁盘上,扩展名为『.ndf』
    3. 日志文件:保存用于恢复数据库的日志信息,每个数据库必须至少有一个日志文件。扩展名为『.ldf』
  8. 数据库的存储结构
    1. SQL用于存储数据的基本单位是页,八个连续的页组成一个区
    2. 数据页:SQL将8KB的数据划分为一个页,SQL数据库中的1MB数据中包含128页
  9. 文件组
    1. 概念:文件组是指将数据库相关的一组磁盘文件组成的集合
    2. 功能
      1. 帮助数据库管理人员执行相应的数据布局,及某些管理任务
      2. 可以在特定的文件中定位特定的对象,将频繁查询与修改的文件分离出来,提高磁盘驱动器的效率
      3. 通过创建用户文件组,可以将数据文件集合起来,便于管理,数据分配和放置

第四章

  1. 创建与修改表
    1. 创建表
      create table score(
      studetno nchar(11) not null,
      courseno nchar(6) not null)
    2. 表结构的修改
      alter table student   /*修改表*/
      add class varchar(20) null  /*添加『class』列*/
      drop column grade /*删除『grade』列*/
      alter column class char(20) not null /*修改列『class』的数据类型*/
      exec sp_rename 'student.class','st_class' /*修改列名,旧名+新名*/
    3. 表数据的修改
      insert into score(usaually,final)
      values(79,91)   /*向score表中添加usually final成绩*/
      update score
      set final =87
      where studentno = '15' and courseno ='2' /*把score表中学号为1,课程编号为2的期末成绩进行修改*/
      delete from student1
      where st_class ='jsj1812' /*删除st_class列中包含值js1812的所有行*/
  2. 完整性与约束
    1. 数据完整性类型
      1. 域完整性:域指表中的列,要求列的数值具有正确的类型,格式和有效值范围,并确定是否允许有空值。实现机制有『默认值』,『检查』,『外键』,『数据类型』
      2. 实体完整性:实体指表中的行,要求表中的每一行具有唯一标识。实现机制有『主键』,『唯一码』,『唯一索引』,『标识列』
      3. 引用完整性:指两个表的主键与外键之间定义的数据完整性,将确保主键和外键的关系。实现机制有『外键』,『检查』,『触发器』,『存储过程』
      4. 用户定义完整性:用户根据应用环境的不同,对数据库设置一些特殊的约束条件。实现机制有『规则』,『触发器』,『存储过程』『约束』
    2. 约束的实现
      1. check约束
        alter table score  /*创建check约束,限定其值在0~100之间*/
        add constraint ck_usually check(usually>=0 and usually<=100)
        
      2. unique约束
        alter table student  /*为mail列添加约束*/
        add constraint u_mail unique nonclustered(mail)  /*『nonclustered』为非聚集索引,可以不加*/
        
      3. foreign key约束
        add constraint fk_sc foreign key (studentno) /*为studentno列添加约束*/
        
      4. primary key约束
        同上

      5. not null 约束

第五章

  1. @定义一个局部变量,@@定义一个全局变量。
    声明一个『@myvar』变量: declare @myvar

  2. 常用的函数

    聚合函数 作用
    avg 返回组中数据的平均值
    count 返回组中项目的数量
    max 返回最大值
    min 返回最小值
    sum 返回组中数据总和
    数学函数 作用
    abs 返回表达式的绝对值
    ceiling 返回『大于或等于』指定数值的『最小整数』
    floor 返回『小于或等于』指定数值的『最大整数』
    pi 常量,圆周率
    power 返回给定表达式的『指定次方』的值
    rand 返回0~1之间的随机 float 数
    round 返回指定小数的位数的表达式的值
    sign
    sqrt 返回给定表达式的平方根
    square 返回给定表达式的平方
    字符串函数 作用
    ascll
    char
    charindex
    left
    len
    lower
    ltrim
    replace
    reverse
    right
    rtrim
    space
    str
    substring
    upper
    replacate

第六章

  1. select from 语句指定列,where 子句指定行,into子句生成新表

  2. 改变列名
    select 原名 as 别名

  3. order by 子句
    升序ASC是默认,降序DES必须表明

  4. 消除重复行

    select distinct studentno,sname
    from st_score
    
  5. 输出前N行
    select top 5 studentno,sname
    from student
    
  6. 『注意』:聚合函数不能够被用于select语句的where子句中

  7. group by 的使用
    group by 将查询结果按照属性列或属性列组合在行的方向上进行分组,每组在属性列或属性列组合上具有相同的聚合值
    『例』

    select sex as '性别',count (*) as '人数' /*count(*)是数一下行数的意思*/
    from student
    group by sex
  8. 利用现有表生成新表
    『例』

    select student.studentno,student.sname,score.courseno,final*0.8+usually*0.2 as '总评'
    into stu_score  /*新表的名称*/
    from student,score /*两个旧表*/
    where student.studentno = score.studentno /*设置的条件*/

第七章

  1. 使用游标步骤
    声明游标→打开游标→获取记录信息→关闭游标→释放游标
    使用fetch获取记录信息,fetch函数的参数表如下

    参数 含义
    next 移至下一行
    prior 移至上一行
    first 移至第一行
    last 移至末行
    absolute 位移到第n行
    pelative 从当前位置移n 行
    into 将当亲字段值付给变量
  2. SQL 子查询
    子查询(Sub Query)或者说内查询(Inner Query),也可以称作嵌套查询(Nested Query),是一种嵌套在其他 SQL 查询的 WHERE 子句中的查询。
    子查询用于为主查询返回其所需数据,或者对检索数据进行进一步的限制。
    子查询可以在 SELECT、INSERT、UPDATE 和 DELETE 语句中,同 =、<、>、>=、<=、IN、BETWEEN 等运算符一起使用。 使用子查询必须遵循以下几个规则:
    • 子查询必须括在圆括号中。
    • 子查询的 SELECT 子句中只能有一个列,除非主查询中有多个列,用于与子查询选中的列相比较。
    • 子查询不能使用 ORDER BY,不过主查询可以。在子查询中,GROUP BY 可以起到同 ORDER BY 相同的作用。
    • 返回多行数据的子查询只能同多值操作符一起使用,比如 IN 操作符。
    • SELECT 列表中不能包含任何对 BLOB、ARRAY、CLOB 或者 NCLOB 类型值的引用。
    • 子查询不能直接用在集合函数中。
    • BETWEEN 操作符不能同子查询一起使用,但是 BETWEEN 操作符可以用在子查询中。

    SELECT 语句中的子查询
    通常情况下子查询都与 SELECT 语句一起使用,其基本语法如下所示:

    SELECT column_name [, column_name ]
    FROM   table1 [, table2 ]
    WHERE column_name OPERATOR
    (SELECT column_name [, column_name ]
    FROM table1 [, table2 ]
    [WHERE])

    示例
    考虑 CUSTOMERS 表,表中记录如下所示:

    ID NAME AGE ADDRESS SALARY
    1 Ramesh 35 Ahmedabad 2000.00
    2 Khilan 25 Delhi 1500.00
    3 kaushik 23 Kota 2000.00
    4 Chaitali 25 Mumbai 6500.00
    5 Hardik 27 Bhopal 8500.00
    6 Komal 22 MP 4500.00
    7 Muffy 24 Indore 10000.00

    在 SELECT 语句中进行子查询:

    SELECT * 
    FROM CUSTOMERS 
    WHERE ID IN (SELECT ID FROM CUSTOMERS WHERE SALARY > 4500)

    上述语句的执行结果如下所示:

    ID NAME AGE ADDRESS SALARY
    4 Chaitali 25 Mumbai 6500.00
    5 Hardik 27 Bhopal 8500.00
    7 Muffy 24 Indore 10000.00

    INSERT 语句中的子查询
    子查询还可以用在 INSERT 语句中。INSERT 语句可以将子查询返回的数据插入到其他表中。子查询中选取的数据可以被任何字符、日期或者数值函数所修饰。
    其基本语法如下所示:

    INSERT INTO table_name [ (column1 [, column2 ]) ]
              SELECT [ *|column1 [, column2 ]
    FROM table1 [, table2 ]
    [ WHERE VALUE OPERATOR ]

    示例
    考虑与 CUSTOMERS 表拥有相似结构的 CUSTOMERS_BKP 表。现在要将 CUSTOMER 表中所有的数据复制到 CUSTOMERS_BKP 表中,代码如下:

    INSERT INTO CUSTOMERS_BKP
    SELECT * FROM CUSTOMERS 
    WHERE ID IN (SELECT ID FROM CUSTOMERS)

    UPDATE 语句中的子查询
    子查询可以用在 UPDATE 语句中。当子查询同 UPDATE 一起使用的时候,既可以更新单个列,也可更新多个列。
    其基本语法如下:

    UPDATE table
    SET column_name = new_value
    [ WHERE OPERATOR [ VALUE ]
    (SELECT COLUMN_NAME
    FROM TABLE_NAME)
    [ WHERE) ]

    示例
    假设有一份 CUSTOMERS_BKP 表作为 CUSTOMERS 表的备份。
    下面的示例将 CUSTOMERS 表中所有 AGE 大于或者等于 27 的客户的 SALARY 字 段都变为了原来的 0.25 倍:

    UPDATE CUSTOMERS
    SET SALARY = SALARY * 0.25
    WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP WHERE AGE >= 27 )

    这将影响两行数据,随后 CUSTOMERS 表中的记录将如下所示:

    ID NAME AGE ADDRESS SALARY
    1 Ramesh 35 Ahmedabad 125.00
    2 Khilan 25 Delhi 1500.00
    3 kaushik 23 Kota 2000.00
    4 Chaitali 25 Mumbai 6500.00
    5 Hardik 27 Bhopal 2125.00
    6 Komal 22 MP 4500.00
    7 Muffy 24 Indore 10000.00

    DELETE 语句中的子查询
    如同前面提到的其他语句一样,子查询还可以同 DELETE 语句一起使用。
    其基本语法如下所示:

    DELETE FROM TABLE_NAME
    [ WHERE OPERATOR [ VALUE ]
    (SELECT COLUMN_NAME
    FROM TABLE_NAME)
    [ WHERE) ]

    示例
    假设有一份 CUSTOMERS_BKP 表作为 CUSTOMERS 表的备份。
    下面的示例将从 CUSTOMERS 表中删除所有 AGE 大于或者等于 27 的记录:

    DELETE FROM CUSTOMERS
    WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP WHERE AGE > 27 )
    

    这将影响两行数据,随后 CUSTOMERS 表中的记录将如下所示:

    ID NAME AGE ADDRESS SALARY
    2 Khilan 25 Delhi 1500.00
    3 kaushik 23 Kota 2000.00
    4 Chaitali 25 Mumbai 6500.00
    6 Komal 22 MP 4500.00
    7 Muffy 24 Indore 10000.00