5.12数据库学习笔记

SQL简介

SQL (Structured Query Language:结构化查询语言) 是用于管理关系数据库管理系统（RDBMS）。 SQL 的范围包括数据插入、查询、更新和删除，数据库模式创建和修改，以及数据访问控制。

SQL语言分类

• 数据查询语言（DQL:Data Query Language）： select 

其语句，也称为“数据检索语句”，用以从表中获得数据，确定数据怎样在应用程序给出。保留字SELECT是DQL（也是所有SQL）用得最多的动词，其他DQL常用的保留字有WHERE，ORDER BY，GROUP BY和HAVING。这些DQL保留字常与其他类型的SQL语句一起使用。

• 数据操作语言（DML：Data Manipulation Language）：INSERT，UPDATE和DELETE

其语句包括动词INSERT，UPDATE和DELETE。它们分别用于添加，修改和删除表中的行。也称为动作查询语言。

• 事务处理语言（TPL）：

它的语句能确保被DML语句影响的表的所有行及时得以更新。TPL语句包括BEGIN TRANSACTION，COMMIT和ROLLBACK。

• 数据控制语言（DCL）：

它的语句通过GRANT或REVOKE获得许可，确定单个用户和用户组对数据库对象的访问。某些RDBMS可用GRANT或REVOKE控制对表单个列的访问。

• 数据定义语言（DDL）：跟表的结构相关。create  drop  alter(更改表结构)

其语句包括动词CREATE和DROP。在数据库中创建新表或删除表（CREAT TABLE 或 DROP TABLE）；为表加入索引等。DDL包括许多与人数据库目录中获得数据有关的保留字。它也是动作查询的一部分。

• 指针控制语言（CCL）：

它的语句，像DECLARE CURSOR，FETCH INTO和UPDATE WHERE CURRENT用于对一个或多个表单独行的操作。

SQL常用操作语句

查询语句

select 列1,列2,...,列n 
from 表名
where 条件
group by 列
having 条件
order by 列 asc/desc
limit 位置,记录数

插入语句

#按照字段插入
insert into 表名(列名1，列名2。。。) values(列值1，列值2。。。);
#插入一条
insert  into  表名  
values
(id,name,age,address);
#同时插入多条语句
insert  into  表名  
(列1,列2,列3,列4)
values
(值1,值2,值3,值4), 
(值1,值2,值3,值4),
(值1,值2,值3,值4);
#同时插入多条语句
insert into 表名
(列1,列2,列3,列4)
select 值1,值2,值3,值4 union
select 值1,值2,值3,值4 

修改语句

update  表名   
set   
    列名1 = '列值1',      
    列名2 = '列值2',      
    列名3 = '列值3'
where   条件 

删除语句

delete  from  表名  where  条件

数据完整性

数据完整性(Data Integrity)

数据完整性（Data Integrity）是指数据的精确性（Accuracy） 和可靠性（Reliability）。它是应防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。数据完整性分为四类：实体完整性（Entity Integrity）、域完整性（Domain Integrity）、参照完整性（Referential Integrity）、用户自定义完整性（User-defined Integrity）。

数据完整性分类

• 实体完整性(Entity Integrity)：保证数据记录的唯一性，主键,自增,唯一

• 域完整性(Domain Integrity) : 非空,默认值,外键约束

• 引用完整性(Referential Integrity) : 外键约束

• 用户自定义完整性(User-defined Intergrity) 

实体完整性(Entity Integrity)约束

实体完整性要确保实体的唯一性。

主键约束（primary key）

特点: 唯一且非空。并且主键列会自动生成索引。

行数据的唯一标识，不能为null，不能重复，一个表中应该有主键，只能有一个主键，有主键（索引）的表，查询效率高。

主键 数据库主键是指表中一个列，其值能唯一地标识表中的每一行。这样的一列称为表的主键，通过它可强制表的实体完整性。当创建或更改表时可通过定义 PRIMARY KEY约束来创建主键。一个表只能有一个 PRIMARY KEY 约束，而且 PRIMARY KEY 约束中的列不能接受空值。由于 PRIMARY KEY约束确保唯一数据，所以经常用来定义标识列。

主键的主要作用：

（1）保证实体的完整性；

（2）加快数据库的操作速度；

（3）在表中添加新记录时，数据库会自动检查新记录的主键值，不允许该值与其他记录的主键值重复；

（4)  数据库自动按主键值的顺序显示表中的记录。如果没有定义主键，则按输入记录的顺序显示表中的记录。

主键的特点:

（1）唯一性：一个表中只能有一个主键。如果在其他字段上建立主键，则原来的主键就会取消。联合主键！

（2）非空性：主键的值不可重复，也不可为空；

（3）一张表要有一个无意义的主键。 主键主要是用于其他表的外键关联，以及本记录的修改与删除；

自增约束（auto_increment）

特点: 系统自动生成，自动加1。要和主键约束一起使用。一个表只能由一个自增列

注意: 自增值在某些场景下会出现断层

1.在mysql存储引擎是innodb的情况下，如果使用select union语法做批量插入，则会导致两次插入的自增值断
层。而使用 insert values语法不会导致这个问题。将表的存储引擎改为MyISAM后这个问题可以解决

#查看表使用存储引擎
show create table emp
#创建表指定引擎
create table emp(	
    empId int primary key auto_increment,	
    empName varchar(20),	
    sex char(1),	
    phone varchar(11) unique
)ENGINE = 'myisam'

2. 在innodb存储引擎下，在插入唯一约束数据重复，外键无效数据时，此时报错，会导致自增断层。而数据长度
过长导致的插入不成功不会导致自增断层。3. 在有自增列的表中删除记录之后，再插入数据时，自增不会连续增
长。

唯一约束（unique）

特点: 确保数据的唯一性。唯一约束允许null值。

引用完整性

引用完整性用于限定多表之间的关联关系。

外键约束(foreign key)

特点: 确保表与表之间的数据的一致性。

外键约束(foreign key )：主要是为了维护两个表之间的数据的一致性关系，是建立在主键的基础之上的 。A表中的某列（外键）会引用B表中的主键；数据类型一致； 体现表与表之间的关联关系；必须先有主键数据，才可以录入外键数据；外键要主键数据一致。

如果一张表的某个列指向另一个表的主键列 ，这个列就称之为 外键列。这个表被称为从表，而其关联的目标表被称为主表。

在具有引用完整性的表之间，禁止用户进行下列操作

（1）当主表中没有关联的记录时，将记录添加到从表中。

（2）更改主表表中的被关联值并导致从表中的记录孤立。

（3）从主表中删除记录，但仍存在与该记录匹配的相关记录

(4) 主表被从表关联，此时删除主表结构，会报错，主表无法删除。

解决方法: 先删除从表，再删除主表

注意： 在实际项目中考虑到数据库的处理性能和表的维护，一般并不会实际加外键约束。而只是设计表时让从表和主表有对应数据列即可。这被称为逻辑约束。

域完整性(Domain Integrity)

域完整性，主要用于限定数据范围。

默认值约束：default，

当没有为列提供值得时候，用默认值填充列

非空约束：not null

确保某一列的值不能为空。

外键约束：foreign key

特点: 确保表与表之间的数据的一致性。

*检查约束：check约束，MySql中没有实现check约束,SQLServer中有这个约束

用户自定义完整性

用户自定义完整性指针对某一具体关系数据库的约束条件，它反映某一具体应用所涉及的数据必须满足的语义要求。例如某个属性必须取唯一值，某个非主属性也不能取空值，某个属性的取值范围在0-100之间等

用户定义完整性可以涵盖实体完整性、域完整性、参照完整性等完整性类型。

建表时加约束

实例:

#创建班级表
create table class (	
    id int(5) primary key auto_increment,	
    classname varchar(30) unique not null
);
#创建学生表：
create table student(	
    id int(5) primary key auto_increment,	
    name varchar(50) not null,	
    age it(5) not null,	
    classid int(5),
    #外键列    
    constraint 外键名 classid_fk_id foreign key(引用id) references 被引用表名 (被引用id)
);

注意：

在从表(学生表)中插入数据时，其中的班级列数据必须是班级表中主键列的数据。

在删除主表(班级表)中的数据时，要确保删除的数据没有被从表中的外键数据关联。

以后的项目中 大多都用逻辑外键 (列之间有对应的数据关系)，不用实际外键（表与表中有foreign key 关联的）。

逻辑外键：在逻辑关系上存在外键关联，但是在实际创建表结构时，并没有添加foreigh key 关联。

好处：如果有实际外键，当我们每一次去操作数据时都会进行外键的维护。效率低，占资源。

所以实际开发中经常使用逻辑外键关系。

小数据量基本体现不出快慢效果。快慢效果只有在庞大的数据基数下才可以体现。

数据定义语言(DDL)

主要用来修改数据表结构用的，增加/减少/修改 字段 （属性） 添加各种约束

//添加新的字段 
alter table 表名 add 列名 <类型> ; 
//添加新的字段 位置在第一列
alter table 表名 add 列名 <类型> first; 
//将列名1添加到列名2的后边
alter table 表名 add 列名1 <类型> after 列名2;  
//修改字段名和类型
alter table 表名 change COLUMN <旧列名> <新列名> <新列类型>;
//修改列名的类型
alter table 表名 modify <列名> <类型>;
//删除列
alter table 表名 drop <列名>;
//给表重命名
alter table 表名 rename <新表名>;
//设置字符集
alter table 表名 CHARACTER SET <字符集名>;
//移动列的位置  将列名a移动到列名b的后边
alter table 表名 modify <列名a>(列类型) after <列名b>;
//给id添加自增约束
alter table 表名 modify id int(5) auto_increment;
//修改表中的字段不为空/或者为空
alter table 表名 modify 字段名  类型 not null/null;
//添加表的字段自增主键    如果要修改只需将add 改成 modify
alter table 表名 add column 字段名 类型 auto_increment not null, add primary key(cid);
//添加唯一约束：
alter table 表名 add constraint uk_(字段名) unique(字段名);
//删除表的字段
alter table 表名 drop column 字段;
//删除自增主键约束
alter table 表名 modify id int;
//删除表的主键约束，要先删除自增约束才可以删除主键约束
alter table 表名 drop primary key;
//添加外键约束
//外键名  fk_classid_id   student classid    class id
alter table 表名 add constraint 外键名  foreign key(引用id) references 被引用表名 (被引用id)
//添加主键约束
alter table 表名 add constraint pk_id primary key(id(修改的主键列名));
//删除外键约束
-- 需要先获取外键约束名称,该名称系统会自动生成,可以通过查看表创建语句来获取名称
show create table 表名;
-- 获取名称之后就可以根据名称来删除外键约束
alter table 表名 drop foreign key 外键名;