史上最全数据库笔记（上）数据库adsedfewf的博客-

一、  为什么使用SQL数据库？

数据库又称作数据集合，如果没有数据库管理人员需要一条一条的进行输入数据，于是有人发明了一条C语言语句，可以循环使用（增删改查）比如双十一的购物车就是数据库的体现，把想要的数据整理到一个文件中，在互联网中进行数据记录，查询，整理，统计等工作。

 

二、  什么是数据？

信息，文字，音乐，图片，视频，文件等，多媒体文件都是数据

 

三、  什么是大数据？

数据量多，数据流量大，并发量高，比如生活中的抖音，每秒都有很多人上传视频，下载视频，流量大，并发量高，数据量多是大数据的特点。

 

四、  什么是数据库？

数据库本身是一个文件，用来存储数据信息，对数据进行分类整理，一般数据库是以二进制进行存储的，因为CPU是直接处理二进制的，所以数据库直接以二进制进行存储，这样可以提高数据库的处理速度。

 

五、  数据库管理系统的作用？

用于创建数据库文件，管理数据库文件，提供数据库的操作界面（人机交互界面），对数据库内的数据进行增、删、改、查。

 

六、  数据库系统是什么？

是由开发、管理、应用一体的数据应用系统，一般是指开发人员在应用程序中对数据库的调用。

 

七、  主流数据库系统有哪些？

1.  SQL Server（微软）

简单，易学，有大量的模板，完整的错误提示代码信息。

2.  Oracle（甲骨文）

数据分析：比如百度的点击量排名，就是数据分析后的结果

面向应用对象的数据库

面向大型企业，大型数据管理

3.  DB2（IBM—国际商务机器公司）

大型数据库（金融系统使用较多）

因为DB2注重的是安全

4.  MySQL（现已经被甲骨文收购了）

开源，体积小，速度快，稳定

应用数量最多的数据库 MySQL

   

八、  数据库存储数据的种类

1.  关系性数据库à二维表格

特点：建立数据库时不用考虑库与库之间，或表与表之间的关系，在数据库应用时再建立关系

优点：

1） 易于维护：都是使用表结构，格式一致；

2） 使用方便：SQL语言通用，可用于复杂查询；

3） 复杂操作：支持SQL，可用于一个表以及多个表之间非常复杂的查询

缺点：

1)   读写能力比较差，尤其是海量数据的高效率读写；

2)   固定的表结构，灵活度稍欠；

3)   高并发读写需求，传统关系型数据库来说，硬盘I/O是一个很大的瓶颈；

2.  非关系性数据库à数据结构化存储方法的集合

非关系型数据库严格上不是一种数据库，应该是一种数据结构化存储方法的集合，可以是文档或者键值对等。

优点：

1）  格式灵活：存储数据的格式可以是key，value形式、文档形式、图片形式等等，文档形式、图片形式等等，而关系性数据库则只支持基础类型。

2）  速度快：nosql可以使用硬盘或者随机存储器作为载体，而关系型数据库只能使用硬盘；

3）  高扩展性；

4）  成本低：nosql数据库部署简单，基本都是开源软件

缺点：

1）  不提供sql支持，学习和使用成本较高；

2）  无事务处理；

3）  数据结构相对复杂，复杂查询方面稍欠

九、  数据库管理

1.     1. 关系性数据库的范式（可以作为典范的形式和样式）

       1） 第一范式（每个字段都分割成不可再分割的子项）

              例：将

地址

山东省泰安市

                  分割成

省	市
山东省	泰安市

       2） 第二范式

              在满足第一范式的前提下，可以让这一张表只做一件事情

              例：一行只介绍地址

3） 第三范式

    在满足第一、第二范式的前提下，尽可能减少重复字段，当两张表建立引用关系时

2.  非关系性数据库的范式

        在一些特殊邻域，需要数据库的其他应用规则，而关系性数据库的工作效率会很低。

           #关系性数据库搜索数据的时候是从头开始找的

       nosql——非关系型数据库，库与库之间，表之间不建立任何关系。

           #非关系性数据库是库与库之间查找

       Column-Orientied——面向检索式的列式存储，分类进行存储。

           #而检索式是在查找时有对应的类别然后进行查找

       Key-Value——面向高并发读写的缓冲存储

           #分布式数据库存储，面向高并发读写的缓冲存储

       Document-Orientied——面向海量文档存储

3.  数据库命令简介，数据文件简介

1）   命令体系

             i.     create 添加

           ii.     alter      修改

          iii.     drop   删除[迪1] 

           iv.     insert 添加数据

             v.     delete 删除数据，清除数据

           vi.     select 查询数据

          vii.     updata 更新数据，修改数据[迪2] 

2）   数据文件简介

名称	后缀名	个数	是否必须	作用
主数据文件	.mdf	1个	必须	包含该数据库的启动文件，用于存储数据
辅助数据文件	.ndf	多个	非必须	当有些文件不能置于主数据文件时，可以创建辅助数据文件
事务日志文件	.ldf	多个	必须	作用[迪3]

 

4.  创建数据库

1）   系统数据库的文件详解·

 

Master ：主数据库文件，存储数据管理系统的用户名，密码，权限，数据库信息存储文件等

Model ：模板库，存储命令语句的模板

Msdb ：代理信息库，报警，计划任务，第三方软件信息等

Tempdb ：临时数据，平时为空，存储添加，修改，删除的原始数据

2）   创建数据库命令

create database hehe[迪4] 

on primary[迪5] [迪6] 

(

    name = ‘hehe’,[迪7] 

    filename = ‘f:hehe.mdf’,[迪8] 

    size = 10MB,[迪9] [迪10] 

    maxsize = 30MB,[迪11] [迪12] 

    filegrowth = 10MB[迪13] 

)

5.  创建日志文件

1）   创建日志文件命令   //log on是创建数据库的子命令，必须与创建数据库命令一起执行，不可单独执行

log on                         //开启数据库的日志文件

(

    name = ‘hehe_log’,          //定义日志文件的逻辑名成为hehe_log

    filename = ‘f:hehe.ldf’        //定义日志文件的存储位置

)

6.  扩展数据库（辅助数据文件）

1）   作用：当有些数据不能创建到主数据文件中，要创建辅助数据文件，然后存储到辅助数据文件中。

2）   创建日志文件命令

alter database hehe                 //修改数据库名为hehe

add file                               //add创建   file文件

(

    name = ‘hehe_ndf’,              //创建逻辑名为hehe_ndf文件的辅助数据文件

    filename = ‘f:hehe_ndf.ndf’,       //物理路径为f:hehe_ndf.ndf

    size = 10MB,                       //起始大小为10MB

    maxsize = 30MB,                 //最大大小为30MB

    filegrowth = 5MB                   //数据库可用空间的增长率

)

7.  增加数据库的起始空间或最大空间

1）   作用：当主数据文件需要增加最大占用系统空间时，可以修改其最大的使用空间

2）   增加数据库的起始空间的命令

alter database hehe                 //alter修改 定义修改的数据库名为hehe

modify file                        //modify修改 file文件

(

    name = ‘hehe_ndf’,              //修改的文件名为hehe_ndf

    size = 20MB                    //修改后的大小为20MB[迪14] 

)

8.  收缩数据库文件空间容量

1）   作用：

2）   收缩数据库其实空间的命令

use hehe                               //连接数据库名为hehe

GO[迪15] 

dbcc shrinkfile (N’hehe_log’,5)     //使用dbcc管理工具，N是代表的name（名字），文件逻辑名为hehe_log，收缩为5mb

GO

9.  分离数据库、附加数据库（分立与附加要配合使用）

1）   作用：在数据库迁移时，数据库正在被sqlserver调用时，无法迁移，我们需要对数据库进行分立操作，让管理系统不再调用数据库，然后就可以进行附加操作

2）   分离数据库命令

use master                                 //链接数据库名master的主数据库

exec[迪16]  master[迪17] .dbo[迪18] [迪19] .sp_detach_db [迪20] @dbname[迪21] =N’hehe’    //将库名为hehe的数据库从master分离出去

3）   附加数据库命令

4）    create database hehe on                    //添加名为hehe的数据库，并on开启

(filename = ‘f:haha.mdf’),             //添加数据库的路径，继续添加用，隔开

(filename = ‘f:hehe.mdf’)                 //添加数据库的路径

for attach                                 //attach附加的意思

10. 删除数据库

1）   忘记就可以了没必要记住

2）   删除数据库命令：

Drop database hehe                     //删除库名为hehe的数据库

十、    表的管理

1.  数据类型

1） 精准数字（存储整数）

a)   Int    4字节存储  2,147,483,647

b)   Bigint 8字节存储  长字节的int类型

c)   Smallint   2字节存储  65535

d)   Tingint 1字节存储  0-255之间的整数

#电话号码不可以设置为int类型，因为位数不够，要设置为字符串类型

2） 位数型

a)   Bit    1-未知字节存储[迪22] [迪23] 

3） 小数型

a)   Decimal 8字节存储  格式为：decimal（8[迪24] ，2[迪25] ）

b)   Numeric 8字节存储  格式为：numeric（8，2）

4） 货币型

a)   Money  8字节存储

b)   Smallmoney 4字节存储

5） 近似数字

a)   Float  8字节存储

b)   Real       4字节存储

6） 字符串

不加n

存储数字、英文 一 个字节，存储汉字为 两 个字节

a)   char（）      最大8000个字符，4000个汉字，存储固定长度[迪26] 的字符串

b)   varchar（） 最大8000个字符，4000个汉字，存储可变长度[迪27] [迪28] 的字符串

c)   text（）      存储20亿个字符，10亿个汉字                                //存储文章，文本

注意事项：再添加记录时，不能将多种语言混合输入到列表中，会出现乱码

7） Unicode字符串

国际通用型字符串，将所有字符都改为双字节，大部分语言文字放入一个字库中，能够相互兼容

加上n以后

存储汉字和英文都为 两 个字节

a)   nchar（）  最大4000个字符，固定长度

b)   nvarchar（）   最大4000个字符，可变长度

c)   ntext      最大10亿个字符

字符串备注：

text

ntext

存储文章，下面的等于上面这两个

varchar(max)

nvarchar(max)

8） 二进制字符串

存储图片文件类数据，常用于存储.bmp[迪29] [迪30] 的图片

binary     8000字节存储  固定长度

varbinary      8000字节存储  可变长度

image      存储容量较大的图片文件，存储文件低（淘汰）

9） 日期与时间

a)   date    3字节存储     格式：年-月-日 例：2017-02-21

b)   time    3-5字节存储    格式：小时:分:秒   例：16:51:59

c)   datetime   8字节存储     格式：年-月-日 小时:分:秒  [迪31]  例：2017-02-21 6:51:59

2.  命令

1)   创建表

use haha                     //链接数据库haha

create table 成绩             //创建表名称为”成绩

(

  姓名 char(4),               //定义姓名列类型为char大小为4字节

  年龄 smallint,          //定义年龄这行类型为smallint

  成绩 smallint               //定义成绩这行类型为smallint

)

1)   添加数据

insert into 成绩               //定义需要添加数据的表格为“成绩

values(‘陈迪[迪32] ‘,19,99)        //添加内容第一列为“陈迪“，第二列为19，第三列为99

2)   查询数据

select * from 成绩         //select定义查询的列，from定义要查询的表

十一、   表中的字符约束

1.  完整性约束

1)   实体完整性 → 二维表，行，列

行记录必须要完整，标记为非空的，必须要填写数据，并且关键字不能空

2)   域完整性 → 列，规则来填，进行填充字段

域就是列，字段填写的数据满足字段的类型

3)   引用完整性

两张表建立关系时，建立引用的字段内容必须相同

4)   自定义完整性 → 对数据范围进行控制

对添加的范围进行控制

2.  主键约束

1） 定义：在一张表中能快速定义一行记录的字段可以设置为主键

2） 设置主键对字段的要求：

要有唯一性

一列字段不能为空

一列字段不能有重复值

3） 主键作为表中的关键键有什么作用：

主要用来快速查询数据

表与表之间建立关系使用的

4） 命令

—创建主键约束

create table biao2

(

   id int primary key,[迪33]   —定义主键

   name nvarchar(10)

)

 

insert into biao2(id,name)

values (1,‘陈迪‘)

—查看表格

select [迪34] * from[迪35]  biao3

 

—因为定义为主键的内容必须唯一，所以不能在主键一列内存入相同数值

insert into biao2(id.name)

values (1,‘谔谔‘)

违反了 PRIMARY KEY 约束“PK__biao2__3213E83F7DB1EEA5”。不能在对象“dbo.biao2”中插入重复键。重复键值为 (1)。

 

—并且定义为主键一列的内容不能为空

insert into biao2(id,name)

values (NULL,‘哈哈‘)

不能将值 NULL 插入列 ‘id’，表 ‘taian.dbo.biao2’；列不允许有 Null 值。INSERT 失败。

3.  唯一性约束

1)   定义：指定字段中的数据不能重复，但可以为空，在每张表中可以对每个字段建立唯一性约束

2)   命令：

—唯一性约束

create table biao3

(

  id int primary key, —定义主键约束

  name char(10) unique, —定义唯一性约束（可以为空但是不能相同）

  age int unique not null[迪36]  —定义唯一性约束（不能为空也不能相同）

)

 

—添加数值（正确添加）

insert into biao3(id,name,age)

values (1,‘陈迪‘,19)

—查看表格

select [迪37] * from[迪38]  biao3

 

 

—添加数值（测试name的唯一性约束和age的唯一约束性）

insert into biao3 (id,name,age)

values (2,‘陈迪‘,19)

违反了 UNIQUE KEY 约束“UQ__biao3__72E12F1B24F4527F”。不能在对象“dbo.biao3”中插入重复键。重复键值为 (陈迪      )。

再将陈迪改为丁丁

违反了 UNIQUE KEY 约束“UQ__biao3__DE509E6B191DF1F3”。不能在对象“dbo.biao3”中插入重复键。重复键值为 (19)。

 

—添加数值(测试name的可NULL和age的不可NULL)

insert into biao3 (id,name,age)

values (2,NULL,NULL)

不能将值 NULL 插入列 ‘age’，表 ‘taian.dbo.biao3’；列不允许有 Null 值。INSERT 失败。

 

4.  检查约束

1)   定义：设置字段中的数据范围或指定的字符串在添加数据时，会对数据进行条件比较，满足条件的可以输入，不满足条件的将会被丢弃，检查约束目前只有MySQL不支持使用，但是在MySQL中，语句不会出错

2)   命令：

—检查约束

create table biao4

(

  id int primary key, —定义主键

  name nvarchar(10) unique, —定义唯一性约束

  age int check ( age>16 and age<35 ), —检查约束将age的范围定义在大于16并小于35岁之间

  sex nchar(4) check ( sex=‘男‘ or sex=‘女‘ or sex=‘人妖‘) not NULL[迪39] —检查约束将sex的范围定义在‘男‘或‘女‘或‘人妖‘

)

 

—添加数值（正确添加）

insert into biao4 (id,name,age,sex)

values (1,‘陈迪‘,17,‘男‘)

—查询数值

select * from biao4

 

—添加数值(测试age和sex的范围是否生效)

insert into biao4 (id,name,age,sex)

values (2,‘丁丁‘,16,‘中‘)

INSERT 语句与 CHECK 约束“CK__biao4__age__2B3F6F97”冲突。该冲突发生于数据库“taian”，表“dbo.biao4”, column ‘age’。

再将16改为正确值

INSERT 语句与 CHECK 约束“CK__biao4__sex__2C3393D0”冲突。该冲突发生于数据库“taian”，表“dbo.biao4”, column ‘sex’。

           

—添加数值(测试age和sex中的数值可否为NULL)

insert into biao4 (id,name,age,sex)

values (2,‘丁丁答‘,NULL,NULL)

不能将值 NULL 插入列 ‘sex’，表 ‘taian.dbo.biao5’；列不允许有 Null 值。INSERT 失败。

将sex改为男

(1 行受影响)   //添加成功

 

5.  默认约束

1)   定义：用户没有对该字段添加数据时，默认添加一个指定的字符串或数据

2)   命令：

—默认约束

create table biao6

(

    id int primary key,—此列字段为主键

    name char (20),

    sex char (4) default ‘男‘ not null–default为默认约束的命令字，并且不能添加空值

)

 

—将sex字段添加空值

insert into biao6 (id,name,sex)

values (1,‘陈迪‘,NULL)

不能将值 NULL 插入列 ‘sex’，表 ‘taian.dbo.biao6’；列不允许有 Null 值。INSERT 失败。

 

—不添加sex字段数值，测试默认约束是否生效

insert into biao6 (id,name)

values (2,‘陈迪‘)

 

—查看添加

select * from biao6

 

6.  外键约束

1)   定义：建立给字段添加数据时的范围参照，当被参照字段中存在相应的数据时，建立外键的字段才能添加相应的数据

2)   建立规则：

a)   被参照字段必须是表中的主键

b)   两个相互引用的字段，数据类型要相同，

c)   两个相互引用的字段，数据长度也要相同

d)   建立外键的字段遵守其不可逾越范围即可，可以重复

3)   命令：

—外键约束

—创建biao_a

create table biao_a

(

  id int primary key, —被参照的字段必须是表中的主键

  name char(10)

)

 

—创建biao_b

create table biao_b

(

  id int,

  gongzi int,

  constraint fk_id foreign key (id) references biao_a(id)

—语法格式：constraint 约束名称 foreign key (建立外键名称) references 被引用的表名(被引用的字段名)

)

 

—向biao_a添加数据

insert into biao_a(id, name)

values (1,‘陈迪‘)

 

—向biao_添加数据，必须遵守外键规则

insert into biao_b(id,gongzi)

values (1,5000)

 

select * from biao_b

 

7.  非空约束

1)   概念：指定字段不能为空，在常用的约束中，主键和外键自带非空约束，默认约束也不需要添加非空组合

2)   使用方法：在需要标识为非空的字段后面添加not null

8.  自动增长

1） 概念：指定字段添加数据时，可以为空，但是id会自动增长

2） 使用环境：讲数据复制到另一个表中，进行操作时，对数据进行整理

3） 命令：

mysql> create table biao4

-> (id int primary key auto_increment, name char(20));

十二、 表内数据的管理

1.     添加字段

1） 定义：将字段添加到表中，添加到最后一行，新添加的字段不会对现有数据做出任何修改。

2） 命令：alter table 表名 add (新添字段 新添字段的数据类型)

a)   添加一个

mysql> alter table biao1 add (age int);

—在biao1中添加age字段并定义为int类型

    b） 添加多个·

mysql> alter table biao1 add (age int, csny date);

2.     修改字段名

1） 定义：将指定字段修改为自定义字段，当需要修改的表没有被引用时可以修改字段

2） 命令·：alter table 表名 change 修改字段 修改字段后 数据类型

mysql> alter table biao1 change name name1 char(20);

—将biao1中的字段name修改为name1，并定义为char(20)数据类型

3.     修改字段的数据类型

1） 定义：将指定字段的数据类型修改为自定义类型，修改时如果是没有数据的空表可以随意修改，如果有数据则需要考虑数据的兼容性。

2） 命令：alter table 表名 change 字段名 字段名 修改后的数据类型

alter table biao2 change id id int;

4.     删除字段

5.     添加删除约束

1.添加主键约束

方法1：

alter  table  表名  modify  字段名  数据类型  primary key;

方法2：

alter  table  表名  change 旧字段名 新字段名 数据类型  primary  key;

方法3：

alter  table  表名  add  primary  key(字段名);

方法4：

alter  table   表名 add   constraint   约束名  primary   key(字段名);

             

例：

alter table biao9 change id id int primary key;

 

2.删除主键约束

alter  table  表名  drop  primary key;

 

例：

alter  table  biao1  drop  primary key;

注意：删除主键后，会保留字段中的非空约束。

 

3.添加唯一性约束

语法：

alter  table  表名  change 旧字段名  新字段名  数据类型   unique;

 

例：

alter table biao9 change name name char(20) unique;

 

4.删除唯一性约束

语法：

alter table 表名  drop index 字段名(定义了唯一性约束的字段名)

 

例

alter table biao9 drop index name;

 

5.添加默认约束

语法：

alter  table  表名  change 旧字段名  新字段名  数据类型  default 默认数值

 

例：

alter table biao9 change sex sex char(4) default “男”;

 

6.删除默认约束

语法：

alter  table  表名  change 旧字段名  新字段名  数据类型;

 

例：

alter table biao9 change sex sex char(4)

             

7.添加非空约束

语法：

alter  table  表名  change 旧字段名  新字段名  数据类型 not  null;

 

例：

alter table biao9 change age age int not null;

 

8.删除非空约束

语法：

alter  table  表名  change 旧字段名  新字段名  数据类型;

 

例：

alter table biao9 change age age int;

             

9.添加外键约束

语法：

alter table 表名 add constraint 约束名 foreign key(外键字段) references 参照表名(参照字段名)

 

例：

alter table biao9 add constraint fk_jg foreign key(jg_id) references jiguan(jg_id);

 

 

10.删除外键约束

语法：

alter table 表名 drop foreign key 约束名;

 

例：

alter table biao9 drop foreign key fk_jg;

 

6.     添加数据

1） 定义：向字段中添加数据

2） 命令：insert  into  表名  （字段名1,字段名2..） values (值1，值2…),(值1，值2….)

insert into biao1 (id,name,sex,age) values  (1,’陈迪’,’男’,20) , (2,’丁月’,’女’,’18’);

 

1)  定义：向字段中添加数据

2)  命令：insert into 表名 (字段名1,字段名2…) select 值1,值2… union select 值1,值2…

7.     修改数据

1） 修改单个数据

2） 命令：update 表名 set 字段名=修改后的值 where 字段名=值[迪40] 

8.     删除数据

1） 删除所有数据

delete from 表名;

2） 删除指定的数据记录

delete from 表名 where字段名=需要删除的数据;