SQL 使用总结一（ 规范、基础）

目录

写在之前

命名规范

规格化形式

1、第一规格形式（第一范式）

2、第二规格形式（第二范式）

3、第三规格化形式（第三范式）

4、规格化的优点和缺点

5、去规格化数据库

从已有表创建新表

完整性约束

1、主键约束

2、唯一性约束

3、外键约束

4、NOT NULL约束

5、检查约束

6、去除约束

GROUP BY分组

多个表结合查询

子查询

1、定义

2、调试

写在之前

数据库的使用有些年头，工作也换了几个，但是感觉数据库的使用情况都是大差不差的（工作过的几个公司）。这次刚刚换了一个新的工作，进来后发现数据库的使用情况也没比之前的有多大的进步。然而因为公司性质的不同，工作人员等的不同，相对来说，存储过程用的多了点；其他，总体上和之前上班公司的使用情况差不多。而找到这份工作前，也经历了一些面试，各种数据库的理论知识，高级应用等，很多公司都会问。

坐下来仔细想想，似乎只是在谈理论的时候这些知识才是有用的，但事实工作中又都没怎么去用，高级的不提，基础的如外键约束，唯一性约束等好多都是没有用过的。（当然这也和实际工作中的很多因素有关）但是最近看report service应用的时候，下载了微软官网给实例数据库，里面的表，从命名规范，到外键，约束，索引等等，使用的很好，至少在之前的工作中我不从遇到。没有在BAT等大公司蹲过，所以说我坐井观天也罢，没啥好说的；但是我想，对于大部分从事软件行业的人来说，又有多少是在BAT里面的呢？

所以总是感觉应该写点啥，但是这绝对不是教程，姑且算是我的一个总结吧~~（希望以后尽量能用到实际项目中，而不是只是在理论上遐想）

 

命名规范

命名规范是在数据库规格化过程中最重要的考虑因素之一。名称是我们应用数据库对象的方式。表的名称应该能够描述所保存信息的类型，以便我们找到需要的数据。对于没有参加数据库设计而需要查询数据库的用户来说，具有描述性的名称更为重要。所有在为对象选择名称时，特别是表和列的名称，应该让名称反应出所保存的数据。比如说，保存雇员信息的表可以命名为EMPLOYEE_TBL。列的名称也是如此，比如保存雇员电话号码的列，显然名称为PHONE_NUMBER是比较合适的。

应该在公司范围内统一命名规范，不仅是数据库里的表的命名，而是用户、文件和其他相关对象的命名都应该遵守。命名规范还让我们更容易判断表的用途和数据库系统里面文件的位置，从而有助于数据库的管理。设计和坚持命名规范是公司开发成功数据库实现的第一步。

规格化形式 1、第一规格形式（第一范式）

给数据库中的每一个表或者大多数表都设置主键。第一范式（1NF）是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。在第一范式（1NF）中表的每一行只包含一个实例的信息。

2、第二规格形式（第二范式）

提取对主键仅有部分依赖的数据，把它们保存到另一个表里。

从图中可以看出，第二范式（2NF）是在第一范式（1NF）的基础上，把两个表进一步划分为更明确的单元。

3、第三规格化形式（第三范式）

删除表里不依赖于主键的数据。满足第三范式（3NF）必须先满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。

4、规格化的优点和缺点

规格化为数据库带来了很多好处，主要包含了如下几点：

• 更好的数据库整体组织性
• 减少冗余数据
• 数据库内部的数据一致性
• 更灵活的数据库设计
• 更好的处理数据库安全
• 加强引用整体性的概念

规划虽然带来了好处，但是它有个不可回避的缺点：降低数据库性能。性能的降低程度取决于查询或事务被提交给数据库的时机，其中涉及多个因素，如CPU使用率，内存使用率和输入/输出。

5、去规格化数据库

去规格化是修改规格化数据库的表的结构，在可控制的数据冗余范围内提高数据库性能。尝试提高数据库性能是进行去规格化数据库的唯一原因。去规格化的数据库与没有进行规格化的数据库不一样，去规格化是在数据库规格化基础上进行一些调整，因为规格化的数据库需要频繁的进行表的结合而降低了性能。去规格化会把一些独立的表合成在一起，或是创建重复的数据，从而减少在数据检索时需要结合的表的数量，进而减少所需的I/O和CPU的时间。这在较大的数据仓库程序中会有明显好处，其中的计算可能会涉及表里数以百万行的数据。

去规格化也是有代价的。它增加了数据冗余，虽然提高了性能，但是需要付出更多的精力来处理相关的数据。程序代码会更复杂，因为数据被分散到了多个表，而且可能更难于定位。另外，引用完整性更加琐碎，因为相关数据存在于多个表里。规格化和去规格化都有好处，但是都需要我们对实际的数据和公司的详细业务需求有全面的了解。在确定要着手进行去规格化时，一定要仔细记录所采取的过程，以便于更好的处理像数据冗余这样的问题，维护系统内部的数据库完整性。

 

从已有表创建新表

语法格式如下

select [*|column1,column2...]
into new_table_name
from table_name
[where]

例如，下面是一个已经存在的表

执行如下语句

select * 
into table_new
from table_test
where Id = 1

可以看到如下内容

这表示已经从现有的表创建了一个新表，并且还把相应的数据添加到了新表中。

这里补充另一种情况，即对现有表插入数据，相同的是它们都有into关键字，但是这个是牵扯到是子查询的概念，子查询可以和select，insert ，update，delete等一起使用，甚至还有嵌套的子查询，但是这里不过多说了，有兴趣的自行度娘了解。这里要是的和insert一起使用而实现的对现有表进行数据插入。

代码为

insert into table_new 
select Id, TestNaem,ColName1,ColName3
from Table_Test
where Id in(2,3)

结果如下

 

完整性约束

完整性约束用于确定关系型数据库里数据的准确性和一致性。在关系型数据库里，数据完整性是通过引用完整性的概念实现的，而在引用完整性里包含了很多类型。

1、主键约束

主键是表里一个或多个用于实现记录唯一性的字段。虽然主键通常是有一个字段构成的，但是也可以有多个字段组成。

隐式实现

create table Employee_TBL
(
emp_id varchar(50) not null primary key,
....
)

显示实现

create table Employee_TBL
(
emp_id varchar(50) not null,
emp_name nvarchar(50) not null ,
primary key(emp_id)
)

包含了多个字段的主键定义有如下两种

create table Product_TST
(
prod_id varchar(20) not null,
vend_id varchar(20) not null,
product varchar(20) not null,
cost decimal(18,2) not null,
primary key (prod_id,vend_id)
)

alter  table Product_TST
add constraint products_pk primary key (prod_id,vend_id)

 

2、唯一性约束

唯一性约束要求表里某个字段的值在每条记录里都是唯一的，这一点和主键类似。即使我们对一个字段设置了主键约束，也可对另一个字段设置唯一性约束，尽管它不会当作主键使用。

create table Employee_TBL
(
emp_id varchar(50) not null primary key,
emp_name nvarchar(100) not null,
emp_phone varchar(11) unique,
....
)

 

3、外键约束

外键是子表里的一个字段，引用父表里的主键。外键约束是确保表与表之间引用完整性的主要机制。一个被定义为外键的字段用于引用另一个表里的主键。

create table Employee_Pay_tst
(
emp_id varchar(50) not null primary key,
position varchar(15) not null,
date_hire date null,
pay_rate decimal(12,2) not null,
date_last_raise date null,
constraint emp_id_fk foreign key (emp_id) references Employee_TBL (emp_id)
)

另外也可以使用alter table 添加外键

alter table Employee_Pay_tst add
constraint emp_id_fk foreign key (emp_id) references Employee_TBL (emp_id)

注：1、外键约束中两个关联的字段的数据类型和长度必须严格一致；2、两个表必须都有主键

 

4、NOT NULL约束

前面的范例中，每个字段的数据类型之后都使用了关键字NULL或NOT NULL。NOT NULL也是一个可以用于字段的约束，它不允许字段包含NULL值；换句话说，定义了NOT NULL的字段在每条记录中都必须是有值的。在没有指定NOT NULL时，字段默认是NULL，也就是可以是NULL值。

 

5、检查约束

检查约束用于检查输入到特定字段的数据的有效性，可以提供后端的数据库编辑，虽然编辑通常是在前端程序里完成的。一般情况下，编辑功能限制了能够输入到字段或对象的值，无论这个功能是在数据库还是前端程序里实现。检查约束为数据提供了另一层保护。

create table Employee_TBL
(
emp_id varchar(50) not null primary key,
emp_name nvarchar(100) not null,
emp_phone varchar(11) unique,
emp_age int,
constraint chk_emp_age  check(emp_age>20 and emp_age