SQL学习笔记

SQL Server管理

(1) SQL Server的两种验证方式：用户名验证和Windows验证，开发时用Windows验证就行了。

(2) 常用字段类型：bit(可选值0,1)，datetime，int，varchar，nvarchar(可能含有中文用nvarchar)。

(3) varchar(),nvarchar().char(n)的区别：char(n)不足长度n的部分用空格填充。Var： variable:可变的。

 

 

SQL语句入门

(1) SQL语句是和DBMS”交谈”专用的语句，不同DBMS都认SQL语法。

(2) SQL语句中字符串用单引号。

(3) SQL语句是大小写不敏感的，不敏感指的是SQL关键字，字符串值还是大小写敏感。

(4) 创建表，删除表不仅可以手工完成，还可以执行SQL语句来完成，在自动化部署，数据导入中用的很多。

创建表： create table T_Person              

               (

              ID int not null,

              Name nvarchar(50),

              Age int null

              )

删除表：drop table T_Person 

(5) 执行简单的Insert语句： insert into T_Person(ID,Name,Age) values(1,’Jim’,39)

(6) *(熟悉)：SQL主要分DDL(数据定义语言)和DML(数据操作语言)两类，

create table，drop table，alter table等属于DDL，

select，insert，Update，delete等属于DML。

 

数据插入

(1) insert语句可以省略表名后的列名，但是不推荐。Insert into Person2 values(‘lucy’,’38’)

(2) 如果插入的行中有些字段的值不确定，那么insert的时候不指定那些列即可。

 

数据更新

(1) 更新一个列：update T_Person set Age=30

(2) 更新多个列：update T_Person set Age=30,Name=’Tom’

(3) 更新一部分数据：update T_Person set Age=30 where Name=’Tom’，用where语句表示只更新Name是’Tom’的行，注意SQL中等于判断用单个=，而不是==。

(4) where中还可以使用复杂的逻辑判断： update T_Person set Age=30 where Name=’Tom’ or Age<25. or相当于C#中的||(或者)。 

(5) where中可以使用的其他逻辑运算符是：or，and，not，<，>，>=，<=，!=(或< >)等。

 

数据检索

(1) 简单的数据检索：select * from T_Employee。

(2) 只检索需要的列：select FName from T_Employee。

(3) 列别名：select FNumber as 编号,FName as 姓名 from T_Employee。

(4) 还可以检索不与任何表关联的数据：select 1+1，select newId()，select getDate()。

 

数据汇总

(1) SQL聚合函数：max(最大值)，min(最小值)，avg(平均值)，sum(和)，count(数量)。

 

数据排序

(1) order by子句位于select语句的末尾，它允许指定按照一个列或者多个列进行排序，还可以指定排序方式是升序(从小到大排序，ASC)还是降序(从大到小排序，DESC)。

(2) 按照年龄从大到小排序，如果年龄相同则按照工资从大到小排序   select * from T_Employee order by FAge Desc,FSalary Desc 

(3) order by子句要放到where子句之后：   select * from T_Employee where FAge>23 order by FAge Desc,FSalary Desc

 

通配符过滤(模糊匹配)

(1) 通配符过滤使用Like。

(2) 单字符匹配的通配符为半角下划线”_”，它匹配单个出现的字符，以任意字符开头，剩余部分为”erry”：

  select * from T_Employee where FName like ‘_erry’

(3) 多字符匹配的通配符为半角百分号”%”，它匹配任意次数(零或者多个)出现的任意字符，”K%”匹配以K开头，任意长度的字符串。检索姓名中包含”n”的员工的信息：

  select * from T_Employee where FName like ‘%n%’

 

空值处理

(1) 数据库中，一个列如果没有指定值，那么值就为null，这个null和C#中的null不一样，数据库中的null表示”不知道”，而不是表示没有，因此select null+1结果是null，因为”不知道”加1的结果还是”不知道”。

(2) select * from T_Employee where FName=null    and     select * from T_Employee where FName!=null。都没有任何返回结果，因为数据库”不知道”。

(3) SQL中使用is null，is not null来进行空值判断。

 select * from T_Employee where FName is null

 select * from T_Employee where FName is not null

 

多值匹配 ：where

(1) select FAge,FNumber,FName from T_Employee where FAge in(34,23,35)

(2) 范围值：

1) select * from T_Emploee  where FAge>=23 and FAge<=27

2) select * from T_Employee where Fage Between 23 and 27

 

数组分组 ： Group by

(1) 按照年龄进行分组统计各个年龄段的人数：

   select FAge,Count(*) from T_Employee Group by Fage

(2) Group by子句必须放在where语句的后面

(3) 没有出现在Group by子句中的列是不能放到select语句后的列名列表中的（聚合函数除外）。

  1) 错误：select FAge,FSalary from T_Employee group by Fage。

  2) 正确：select Fage,Avg(FSalary) from T_Employee group by Fage。

 

Having子句 ：Having是对组进行过滤。

(1) 在where中不能使用聚合函数，必须使用Having，Having要位于Group by之后。

   select Fage,Count(*) as 人数 from T_Employee Group by FAge Having Count(*)>1

(2) 注意Having中不能不能使用为参数分组的列，Having不能代替where,作用不一样，Having是对组进行过滤。

 

限制结果集行数 ：top

(1) select top 5 * from T_Employee order by FSalary Desc

(2) (*)检索按照工资从高到低排序检索从第六名开始一共三个人的信息

  select top 3 * from T_Employee where FNumber not in (select top 5 FNumber from T_Employee order by Fsalary desc) order by FSalary Desc。

(3) SQL Server2005后增加了Row_Number函数简化实现

 

 

去掉数据重复 ：distinct

(1) select FDepartment from T_Employee——>select distinct FDepartment from T_Employee

(2) distinct是对整个结果集进行数据重复处理的，而不是针对某一个列，因此下面的语句并不会只保留Fdepartment进行重复值处理。

    select distinct FDepartment,FSubcompany from T_Employee

 

 

 

SQL语句执行顺序：

1. FROM子句组装来自不同数据源的数据

2. WHERE子句基于指定的条件对记录进行筛选

3. GROUP BY子句将数据划分为多个分组

4. 使用聚集函数进行计算

5. 使用HAVING子句筛选分组

6. 计算所有的表达式

7. 使用ORDER BY对结果集进行排序

 

 

关系代数：

五种基本运算：并、差、笛卡尔积、选择、投影

 

 

数据库范式：

1NF（First Normal Form）：当且仅当所有域只包含原子值，即每个分量都是不可再分的数据项，则称实体E满足第一范式

2NF（Second Normal Form）：当且仅当实体E满足第一范式，且每一个非键属性完全依赖主键时，满足第二范式

3NF（Third Normal Form）：当且仅当实体E是第二范式（2NF），且E中没有非主属性传递依赖时，满足第三范式

 

 

控制冗余（Controlled Redundancy）与非控制冗余（Uncontrolled Redundancy）的区别：

未受控制的数据存储冗余会导致如下的问题：

1. 更新数据时的重复工作

2. 浪费空间

3. 数据可能会不一致

 

因此，理想情况下，我们应该设计一个没有冗余的数据库，但是有时候我们需要提高查询的效率，因此我们引入了控制冗余（Controlled Redundancy）

例如： 我们将学生名和课程号冗余存储于GRADE_REPORT表中，因为查询成绩的时候我们需要同时查询学生姓名以及课程号。

 

 

数据的约束条件：完整性约束

1. 域约束：对属性取值范围的约束

2. 键约束：每个关系必须要有主键，且每个主键必须不相同

3. 非空约束：属性值不能为NULL

4. 实体完整性约束：主键值不能为空

5. 参照完整性约束：外键可以取NULL值，但若外键为另一关系主键，则不能为NULL。

6. 用户定义的完整性

各种数据操作可能违反的完整性约束

插入操作：域约束、键约束、非空约束、实体完整性约束、参照完整性约束

删除操作：参照完整性约束

更新操作：域约束、键约束、非空约束、实体完整性约束、参照完整性约束

 

 

附：数据库基本结构知识介绍：

名词积累：

数据库（Database）：存放和提供数据的“库房”

数据（Data）：数据库中存储的基本对象。

数据库管理系统（DBMS）：位于用户与操作系统之间的一层数据管理软件。

数据库系统（Database System）：包括数据库、DBMS、应用系统、数据库管理员（DBA）

主键（Primary Key）：用于唯一的标识表中的某一条记录的属性或属性的集合。

外键（Foreign Key）：用于与另一张表关联，外键为另一张表的主键

超键（Super Key）：超键是能唯一区分元组的属性或属性的集合

键（候选键）：去掉了超键中多余的属性，仍然能够保证区分不同的元组。

模式（Schema）：一个数据库的描述，包括对数据库结构，数据类型，还有约束。

实例（Instance / State）：数据库中在某一时刻存储的真实的数据。（Instance是Schema在某一时刻的具体化、实例化）

数据操纵语言（DML：Data Manipulation Language）：增删改查

数据定义语言（DDL：Data Definition Language）：定义、删除、修改数据库中的对象

数据控制语言（DCL：Data Control Language）：用于控制用户对数据库操纵的权限

数据模型（Data Model）：现实世界数据特征的抽象，用来定义数据如何组织，数据之间的关系怎样

并相容性（Union Compatibility）：两个关系具有并相容性需要保证具有相同的属性个数，以及对于每个属性都有相同的域

视图（VIEW）：视图是虚拟的表，并不是物理存储的数据。而是由基本表或其他视图派生的数据。对视图的更新，实际上转换为对实际基表的更新。

数据模型（Data Model）： 基本概念：现实世界数据特征的抽象，用来定义数据如何组织，数据之间的关系怎样。 

层次：

1.概念模型（Conceptual）：按用户的观点来对数据和信息建模

2.逻辑/实现模型（Logical / Implementation)：层次模型，网状模型，关系模型

3.物理模型（Physical）：数据在具体DBMS产品中的物理储存方式

数据库系统的三级模式结构：

1.内模式（Internal Schema）：         （也称存储模式。）数据物理结构和储存方式的描述，是数据在数据库内部的表示方式

2.概念模式（Conceptual Schema）：（也称全局模式。）有时简称“模式”。是对数据库中全体数据的逻辑结构和特征的描述

3.外模式（External Schemas）：       （也称子模式或者用户模式。）数据库用户能够看见和使用的局部数据的逻辑结构和特征的描述

 

 

.试述事务的概念及事务的四个特性。

答：

事务是用户定义的一个数据库操作序列，这些操作要么全做要么全不做,是一个不可分割的工作单位。

事务具有四个特性：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持续性（Durability）。这个四个特性也简称为ACID特性。

原子性：事务是数据库的逻辑工作单位，事务中包括的诸操作要么都做，要么都不做。

一致性：事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。

隔离性：一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对其他并发事务是隔离的，并发执行的各个事务之间不能互相干扰。

持续性：持续性也称永久性（Permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其执行结果有任何影响。