关系型数据库基础

第一章      关系型数据库管理系统简介

1.1为什么使用数据库

所谓数据库，就是在计算机上集中存放数据的地方。通过将我们所需的数据和信息都保存在计算机数据库中，可以提供对数据的集中控制。数据库的集中控制有如下优点：

@降低存储数据的冗余度

@更高的数据一致性

@存储的数据可以共享

@可以建立数据库所遵循的标准

@便于维护数据完整性

@能够实现数据的安全性

1.2数据在数据库中的此次形式

1：层次模型：单个记录以父子关系组织在一起，形成一个倒挂的树。

2：网状模型：多个父子关系，以增强层次模型

3：关系模型：访问层次状数据库的应用程序基于该数据库已定义好的实现，数据库结构必须硬编码到应用程序的编程语言中。如果数据库发生变化，应用程序就必须变化。但是关系型数据库是独立于应用程序的。更改数据库设计，而不影响应用程序成为可能，关系模型用组成相关数据的表的行和列这种结构，来替换父子框架。

4：对象模型：在关系模型和对象模型之间存在一种阻抗不匹配的关系。在对象模型中，我们将整个物体（包括其特征、操作）整体放到数据库。

1.3关系型数据库的基本概念

*关系模型的理论是1970年由IBM的codd博士提出的。关系模型把世界看作是由实体和联系组成的。所谓实体就是指在现实世界中客观存在并可相互区别的事物。实体可能是有形的或者无形的、具体的或抽象的、有生命的或无生命的。

*实体所具有的某一特性称为属性。实体可以通过若干属性来描述。

*以关系模型来创建的数据库称为关系型数据库。表是关系型数据库的核心单元，它是存储数据的地方。

*在表的内部，数据被分为行和列。填入表中的每一行，代表一个实体。而行的每一列，代表实体的一个属性

*在关系型数据库的表中，用一个唯一的标识符来标识每一行，这个标识符就是主键

*外键就是用来表达表和表之间的关联关系。

*表之间的关联关系存在三种基本的关系类型：一对一关系、一对多关系、多对多关系

1.4关系型数据库管理系统

RDBMS分为以下两种类型：

1.本地数据库管理系统

本地数据库管理系统又称为桌面型数据库管理系统。在这种模式下，RDBMS与数据库应用程序运行在统一客户端的进程中。

2.数据库服务器管理系统

   数据库运行在客户端，而RDBMS运行在专门的数据库服务器上

1.5结构化查询语言SQL

1.SQL是每个RDBMS都能理解的语言。SQL的优点在于，不管我们创建前台客户端用的是ASP.NET、Java、VisualBasic、C#，还是别的语言，SQL是所有这些前台关系型数据库通讯的语言。

2.SQL的非过程性质

  传统语言一般被认为是一种过程式语言，即，它们定义如何执行一个应用程序的操作，以及执行的次序。相反，SQL在本质上就是非过程式的，它主要关心的是操作结果，而由宿主语言决定如何操作。当然，这并不意味着SQL不包括过程元素。即使有了这些过程式元素，也没有使SQL变为一个过程式语言。我们必须使用过程式语言与SQL协同工作，来操作存储在RDBMS中的数据。

3.SQL语句

*数据定义语言（DDL）：用于创建、修改和删除数据库中的数据结构

创建和删除数据库的createdatabase和drop database

创建、修改、重命名、删除表的createtable、alter table、rename table、drop table

创建和删除索引的create index和drop index

*数据查询语言（DQL）：select语句为查询语句

*数据操作语句（DML）

将数据插入到一个表中的insert语句；

更新表中已有数据的update语句；

删除表中数据的delete语句。

*数据控制语句（DCL）

给用户授予访问权限的grant语句；

取消用户访问权限的revoke语句。

 

第二章       管理数据库和表

2.1管理数据库

１.创建和使用数据库：create database 数据库名；

２.连接到数据库：use 数据库名;

３.删除数据库：drop database 数据库名；

2.2数据类型

整数数据类型

类型大小范围（有符号）范围（无符号）用途 
TINYINT 1 字节 (-128，127) (0，255)小整数值 
SMALLINT 2 字节 (-32768，32767) (0，65535)大整数值 
MEDIUMINT 3 字节 (-8388 608，8 388607) (0，16 777215)大整数值 
INT 4 字节 (-2 147 483 648，2 147 483 647)大整数值 
BIGINT 8 字节 (-9233 372 036 854 775 808，9 223372 036 854 775 807) (0，18 446744 073 709 551 615) 极大整数值 

浮点数据类型
FLOAT 4 字节(-3.402 823 466 E+38，1.175494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) 0，(1.175 494 351 E-38，3.402 823 466 E+38) 单精度浮点数值 
DOUBLE 8 字节(1.797 693 134 862 315 7 E+308，2.225073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4E-308，1.797693 134 862 315 7 E+308) 0，(2.225073 858 507 201 4 E-308，1.797693 134 862 315 7 E+308) 双精度浮点数值 
DECIMAL 对DECIMAL(M,D)，ｍ为总数值，ｄ为小树值，ｍ必须大于ｄ

字符串类型

类型　大小　　用途 
CHAR 0-255字节定长字符串 
VARCHAR 0-255字节变长字符串 

二进制数据类型
TINYBLOB 0-255字节不超过 255个字符的二进制字符串 
BLOB 0-65 535字节二进制形式的长文本数据 
MEDIUMBLOB 0-16 777 215字节二进制形式的中等长度文本数据 
LOGNGBLOB 0-4 294 967 295字节二进制形式的极大文本数据 

日期和时间类型
类型大小　(字节)范围格式用途 
DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD 日期值 
TIME 3 '-838:59:59'/'838:59:59' HH:MM:SS 时间值或持续时间 
YEAR 1 1901/2155 YYYY 年份值 
DATETIME 8 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值 
TIMESTAMP 8 1970-01-01 00:00:00/2037 年某时 YYYYMMDD HHMMSS混合日期和时间值，时间戳

2.3管理表

1.创建表

Create table 表名(

列名1   数据类型  列的约束，

列名2   数据类型  列的约束，

列名3   数据类型  列的约束

);

表名在一个数据库中必须是唯一的，并且符合标识符规则。

2.删除表

Drop  table 表名；

3.复制表

（1）复制表的结构和数据

   Create table 表名select*from被复制的表名;

（2）只复制表的结构，不复制数据。只需在where子句后添加一个永远不等于ture的条件即可。

   Create table 表名select*from被复制的表名  where1=0;

4.修改表

  （1）①添加列：alter table 已存在表名 add新加的列名数据类型[约束];

       ②添加多列：alter table 已存在表名 add新加的列名1数据类型[约束],

                                       add 新加的列名2 数据类型[约束],

add 新加的列名2数据类型[约束];

   (2)更改列定义：alter table 表名

                 Change 旧列名新列名数据类型[约束条件]

                 Default‘默认值’;

  （3）删除列：alter table 表名 drop  column想要删除的列名;

2.4管理索引

1.创建索引：create index 索引名  on表名 (列名1),

                       索引名  on表名 (列名1);

2.删除索引：alter table 表名 drop index索引名;

 

第三章  保证数据完整性

3.1数据完整性概述

  保证数据完整性的目的是为了防止存储垃圾数据，影响数据库的执行效率。

1.       实体完整性：保证一行数据是有效的，保证每行所代表的实体能够相互区分（通过在表中设置主键约束、唯一约束或标识列属性来实现）

2.       域完整性：保证一列数据是有效的（通过外键约束、检查约束、默认值定义、非空约束实现）

3.       引用完整性：保证引用的编号是有效的

4.       用户自定义完整性：可以通过触发器、存储过程等实现

3.2数据完整性的实现

1.创建非空约束：在定义列时加上一个not null关键字，来定义一个非空约束

For explem:altertable friend add sarly int not null;

2.主键约束：主键是表中的一到多个列，主键列不能为空，也不能重复。一张表中只能有一个主键，但是主键可以有一列或多列组成。

只有一个主键列：

 Create  table friend(

  Friendid  int primary key,

  Name varchar(50)

);

主键约束有多列共同组成：

Create  table friend(

Friendid int ,

Name varchar(20),

Age int ,

Primarykey(friendid,age)

);

3.设置唯一约束：唯一约束是指给指定列所有的值必须是唯一的，该列在每一行的值必须唯一，确保输入到指定列的值不重复，但允许为null值。

Create table friend(

Friendid int,

Name varchar(20) unique

);

4.指定默认值：

Createtable friend(

Namevarchar(50) not null,

Phoneonvarchar(20) default‘不知道电话号码’

);

5．外键约束：

Createtable friend(

Friendidint,

Namevarchar(50) not null,

Phoneonvarchar(20)

);

Createtable friend1(

Friendidint,

Namevarchar(50) not null,

Phoneonvarchar(20),

Foreign key (friendid) references friend(friendid)

);

5.       使用自动编号列：这种自动产生值的字段通常与primary key约束一起配合使用．mysql使用auto_increment来实现字段的自增长。

默认情况下，种子值和增量值都是１

Createtabletest(

Friendidintprimary keyauto_increment,

Namevarchar(50) not null,

);

也可以通过下面的代码设置其实序号：

alter　table　表名　auto_increment＝100；

６．对已存在的表实时数据完整性　

MySQL查看约束，添加约束，删除约束添加列，修改列，删除列

·        添加主键约束：alter table 表名 add constraint主键（形如：PK_表名） primary key表名(主键字段);

·        添加外键约束：alter table 从表 add constraint外键（形如：FK_从表_主表） foreign key从表(外键字段) references主表(主键字段);

·        删除主键约束：alter table 表名 drop primary key;

·        删除外键约束：alter table 表名 drop foreign key外键（区分大小写）;

·        删除唯一约束：alter table表名 drop index unique;

·        修改表名：alter table t_book rename to bbb;

·        添加列：alter table 表名 add column列名 varchar(30);

·        删除列：alter table 表名 drop column列名;

·        修改列名MySQL： alter table bbb change nnnnn hh int;

·        修改列名SQLServer：exec sp_rename't_student.name','nn','column';

·        修改列名Oracle：alter table bbb rename column nnnnn to hh int;

·        修改列属性：alter table t_book modify name varchar(22);

 

第四章使用DML语句更改数据

4.1 添加新数据

4.1.1 插入单行数据

  Insertinto 表名（列名列表）values (值列表);

into 是insert和目标表之间可以使用的关键字

表名是插入记录的目标表的名称

列名列表是插入记录的目标表的列名，是一个以逗号分隔开的列名列表

4.1.2插入多行记录

方法1：Insert into 表名 values ( ),(  );

方法2：insert into 表名 (列名列表)select select语句；

4.1.3表数据的复制

方法1：insert  into 表名 select*from被复制的表名;

使用方法1的前提条件是，有一张表和被复制表的结构一致

方法2(建表的方式复制)：create table 表名 select*from被复制的表名;

4.2更改已有数据

Update 表名 set列名1=新值1，列名2=新值2  where 过滤条件;

4.2.1更新单列数据

Update friend set name=’李四’ where id=1;

4.2.2更新多列数据

Update friend set name=’李四’ , age=18 where id=2;

4.2.3 通过更新删除列中的数据

有时候需要通过数据的更新操作，实现删除某列的目的。通常的做法是把该列的值设置为null。当然这样做的前提是该列允许为null，即没有非空约束。

Update 表名 set列名=null  where过滤条件;

 4.3删除数据

4.3.1使用delete删除

Delete from 表名 where过滤条件；

使用行过滤条件可以从目标中删除一到多行数据。

Delete from student where id=1 orid=3;

删除区间行

Delete from student where id  between 10  and  19;

4.3.2 使用truncate语句

 Truncate table 表名；

Truncate table 不能用于有外键约束引用的表，这种情况下，需要使用不带where子句的delete语句。

truncate和delete、drop之间有什么区别

相同点：

1.truncate和不带where子句的delete、以及drop都会删除表内的数据。 

2.drop、truncate都是DDL语句(数据定义语言),执行后会自动提交。

不同点：

1. truncate 和 delete只删除数据不删除表的结构(定义)
drop 语句将删除表的结构被依赖的约束(constrain)、触发器(trigger)、索引(index)；依赖于该表的存储过程/函数将保留,但是变为invalid状态。

2. delete 语句是数据库操作语言(dml)，这个操作会放到 rollback segement中，事务提交之后才生效；如果有相应的 trigger，执行的时候将被触发。
truncate、drop是数据库定义语言(ddl)，操作立即生效，原数据不放到 rollback segment中，不能回滚，操作不触发 trigger。 

4.速度，一般来说: drop> truncate >delete 

5.安全性：小心使用 drop和 truncate，尤其没有备份的时候.否则哭都来不及
使用上,想删除部分数据行用delete，注意带上where子句.回滚段要足够大.
想删除表,当然用drop
想保留表而将所有数据删除，如果和事务无关，用truncate即可。如果和事务有关,或者想触发trigger,还是用delete。
如果是整理表内部的碎片，可以用truncate跟上reuse stroage，再重新导入/插入数据。

6.delete是DML语句,不会自动提交。drop/truncate都是DDL语句,执行后会自动提交。 

7、TRUNCATE  TABLE  在功能上与不带  WHERE  子句的  DELETE  语句相同：二者均删除表中的全部行。但  TRUNCATE   TABLE  比  DELETE  速度快，且使用的系统和事务日志资源少。DELETE  语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE  TABLE  通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。 

8、truncate   table  删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用  DELETE。如果要删除表定义及其数据，请使用  DROP   TABLE   语句。  
    
9、对于由   FOREIGN  KEY  约束引用的表，不能使用  TRUNCATE   TABLE，而应使用不带  WHERE  子句的  DELETE  语句。由于  TRUNCATE   TABLE  不记录在日志中，所以它不能激活触发器。    

10、TRUNCATE  TABLE  不能用于参与了索引视图的表。  

第五章 简单的数据查询

5.1 查询的基本结构

select  [distinct] {*|column [alias] ,……}

from  table

[where condition]

[order by column];

5.2投影操作

投影操作是指在SQL查询中，使用select 语句，选择对表中的哪些列进行操作

Select  列名列表 from 表名；

列名列表几种书写格式：

如果我们选择某个列中的多个列，那么列名之间用逗号分隔开；

如果是单个列，只需要列出该列的列名即可；

如果选择所有的列，可以简单地用*代替列名列表。

5.2.1  表名前缀(在更复杂的多表情况下)

Select 表名.列名from表名;

5.2.2列别名

使用别名可以在多个表有同样的列名的时候，使用别名更容易区分

Select 列名 as别名，列名 as别名from表 as别名；

5.2.3计算列

Select 列名+50  from表名；

用于拼接不同列的函数：selectconcat(列名1，列名2) from表名；

5.2.4排除重复数据

Select distinct 列名 from表名；

5.2.5返回限定行数的查询

Select 列名1，列名2from表名 limit开始序号，返回的行数；

5.3选择操作

5.3.1单选择操作

Select 列名列表 from表名where条件；

eg：select  name from  student  where id=3；

5.3.2多选择操作

Select 列名列表 from表  where条件1 and/or条件2；

5.3.3执行范围操作

Select 列名列表 from表

Where 列名 between下限 and上限;

5.3.4定义集合关系（在指定的几个值中进行搜索）

Select 列名列表 from表名  where列名 (not)in（值集合）;

例如：我们在instant数据库中的studentexam中查询studentid为2、7、和3的信息

Select studentid ,examid, mark  from studentexam where  studentidin(2,7,3);

5.3.5模糊查询

Select 列名列表 from表名  where列名 like模式;

eg: select *from  student where  money  like ‘%0’;