1-1.数据库是什么
1-1-1 数据库
- 概念:数据库(DataBase,简称DB)是长期储存在计算机内、有组织的、可共享的大量数据的集合。
- 特性:数据库中的数据按一定的数据模型组织、描述和储存,具有较小的冗余度、较高的数据独立性和易扩展性,并可为各种用户共享。
1-1-2 DBMS
- 概念:数据库管理系统(DataBase Management System)是位于用户与操作系统之间的一层数据管理软件,和操作系统一样是计算机的基础软件,也是一个大型复杂的软件系统。
- 用途:科学地组织和存储数据,高效地获取和维护数据。
- 功能:
- 数据定义功能:提供定义语言(Data Definition Language,DDL),用户通过此可方便地对数据对象进行定义。
- 数据组织、存储和管理:确定以何种文件结构和存取方式在存储级是哪个组织数据,如何实现数据之间的练习。
- 数据操纵功能:提供数据操纵语言(Data Manipulation Language,DML),实现对数据库的基础操作,如增删改查等。
- 数据库的事务管理和运行管理:数据库在建立、运用和维护时由数据管理系统统一管理、统一控制,以保证数据的安全性、完整性、多用户对数据的并发使用及发生故障后的系统恢复。
- 数据库的建立和维护功能:数据库初始数据的输入、转换功能,数据库的转储、恢复功能,数据库的重组织功能和性能监视、分析功能等。
- 其他功能:DBMS与网络中其他软件系统的通信功能;DBMS之间或与文件系统的数据转换功能;异构数据库之间的互访和互操作功能等。
- 种类:通过数据的保存格式(数据库的种类)来进行分类
- 层次数据库(Hierarchical Database, HDB):把数据通过层次结构(树形结构)的方式表现出来,是最早的数据库系统,最基本的数据关系是层次关系,记录类型之间一对多的关系,也叫做双亲子女关系(PCR)。
- 关系型数据库(Relational Database, RDB):采用行和列组成的二维表来管理数据,使用专门的SQL(结构化查询语言)对数据进行操作,该类型的DBMS称为关系数据库管理系统(Relational Database Management System, RDBMS)。具有代表性的RDBMS有:
-
-
- Oracle Database:甲骨文公司的RDBMS
- SQL Server:公司的RDBMS
- DB2: IBM公司的RDBMS
- PostgreSQL:开源的RDBMS
- MySQL:开源的RDBMS
-
-
- 面向对象数据库(Object Oriented Database,OODB):把数据以及对数据的操作集合起来以对象为单位进行管理,编程语言当中有一种被称为面向对象开发,面向对象数据库就是用来保存这些对象的数据库。
- XML数据库(XML Database,XMLDB):一种支持对XML(标准通用标记语言下的一个应用)格式文档进行存储和查询等操作的数据管理系统;可以对XML形式的大量数据进行高速处理。
- 键值存储系统(Key-Value Store,KVS):键值数据库是一种用以键(Key)和值(Value)组合的方式存储数据的数据库。即非关系型数据库(No SQL)。代表性数据库有:
-
-
- Redis:结构化数据存储, 用于数据库、缓存、消息代理;
- Memcached: 基于易失性内存的键-值存储器。
-
-
- 重要性:
- 无法多人共享数据
- 无法提供操作大量数据所需的格式(文本文件和Excel工作表等无法提供相应的格式)
- 实现读写自动化需要编程能力
- 无法应对突发事故
1-2.数据库的结构
1-2-1 RDBMS常见系统结构
使用RDBMS最常见的系统结构是客户端/服务器类型(C/S)
通过网络可以实现多个客户端访问同一个数据库
1-2-2 表的结构
- 表的定义:用来管理数据的二维表在关系数据库中简称为表。表储存在由RDBMS管理的数据库中,一个数据库可以存储多个表。
数据库与表的关系:
根据SQL语句的内容返回的数据同样必须是二维表的形式。
- 结构:
- 表的列(垂直方向)称为字段,代表了保存在表中的数据项目。
- 表的行(水平方向)称为记录,相当于一条数据。关系数据库必须以行为单位进行读写。
- 行和列交汇的方格称为单元格,一个单元格中只能输入一个数据。
1-3 SQL概要
1-3-1 SQL语句及其种类
SQL用关键字、表名、列名等组合而成的一条语句(SQL语句)来描述操作的内容。
根据对RDBMS赋予的指令种类不同,SQL语句可以分为三类:
- DDL(Data Definition Language,数据定义语言)用来创建或者删除存储数据用的数据库以及数据库中的表等对象。 DDL 包含以下几种指令。
-
- CREATE: 创建数据库和表等对象
- DROP: 删除数据库和表等对象
- ALTER: 修改数据库和表等对象的结构
-
- DML(Data Manipulation Language,数据操纵语言)用来查询或者变更表中的记录,是使用最多的 。DML 包含以下几种指令。
-
- CREATE: 创建数据库和表等对象
- DROP: 删除数据库和表等对象
- ALTER: 修改数据库和表等对象的结构
-
- DCL(Data Control Language,数据控制语言)用来确认或者取消对数据库中的数据进行的变更。除此之外,还可以对 RDBMS 的用户是否有权限操作数据库中的对象(数据库表等)进行设定。 DCL 包含以下几种指令。
-
- COMMIT: 确认对数据库中的数据进行的变更
- ROLLBACK: 取消对数据库中的数据进行的变更
- GRANT: 赋予用户操作权限
- REVOKE: 取消用户的操作权限
-
1-3-2 SQL的基本书写规则
1.SQL语句要以分号(;)结尾;
3.SQL语句不区分关键词大小写,插入表中的数据区分大小写,为了理解使用以下规则书写:
-
- 关键词大写
- 表名的首字母大写
- 其余(列名等)小写
2.常数的书写方式是固定的
-
- 字符串和日期常数需要使用单引号(')将其括起来
- 数字常数无需加注单引号,直接书写即可
3.单词之间需要使用半角空格或者换行符进行分隔
1-4 表的创建
命名规则:数据库名称、表名以及列名都要使用半角字符英文字母、数字、下划线(_),必须以半角英文字母开头,名称不能重复
1-4-1 数据库的创建
创建数据库的CREATE DATABASE语句
CREATE DATABASE <数据库名称>;
1-4-2 表的创建
创建数据库的CREATE TABLE语句
CREATE TABLE <表名称>
(<列名1> <数据类型> <该列所需约束>,
<列名2> <数据类型> <该列所需约束>,
<列名3> <数据类型> <该列所需约束>,
<列名4> <数据类型> <该列所需约束>,
····
<该表的约束1>, <该表的约束2>, ······);
1-4-3 数据类型
1.字符型:由任何字母、符号和数字任意组合而成的数据。
- CHAR 和 VARCHAR 类型类似,但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
- BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR,不同的是它们包含二进制字符串而不要非二进制字符串。也就是说,它们包含字节字符串而不是字符字符串。这说明它们没有字符集,并且排序和比较基于列值字节的数值值。
- BLOB 是一个二进制大对象,可以容纳可变数量的数据。有 4 种 BLOB 类型:TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。
- 有 4 种 TEXT 类型:TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型,可存储的最大长度不同,可根据实际情况选择。
- 经常变化的字段用 varchar
- 知道固定长度的用 char
- 尽量用 varchar
- 超过 255 字符的只能用 varchar 或者 text
- 能用 varchar 的地方不用 text
2.数值型
3.日期时间型
4.货币类型
- money:货币数值介于-2^63与2^63-1之间,精确到货币单位的万分之一,存储大小为8个字节
- smallmoney:介于-2147483648与2147483647之间,精确到货币单位的万分之一,存储大小为4个字节
1-4-4 约束的设置
- 定义:约束(CONSTRAINT)是除了数据类型之外,对列中存储的数据进行限制或者追加条件的功能。
- 类型:
- NOT NULL :指示某列不能存储 NULL 值,约束强制字段始终包含值,如果不向字段添加值,就无法插入新记录或者更新记录。
例:建表时定义
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Age int
);
添加此约束
ALTER TABLE Persons
MODIFY Age int NOT NULL;
删除此约束
ALTER TABLE Persons
MODIFY Age int NULL;
- UNIQUE :保证某列的每行必须有唯一的值。每个表可以有多个 UNIQUE 约束。
例:建表时定义
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (P_Id)
)
添加或修改
ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
删除此约束
ALTER TABLE Persons
DROP INDEX uc_PersonID
- PRIMARY KEY:主键,NOT NULL 和 UNIQUE 的结合。确保某列(或两个列多个列的结合)有唯一标识,有助于更容易更快速地找到表中的一个特定的记录,不能包含 NULL 值。每个表只能有一个 PRIMARY KEY 约束。
例:建表时定义单个主键
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)
或:
CREATE TABLE Persons
(
P_Id int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
定义多个列的 PRIMARY KEY 约束
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
)
在上面的实例中,只有一个主键 PRIMARY KEY(pk_PersonID),pk_PersonID 的值是由两个列(P_Id 和 LastName)组成的。
表已创建并没有主键时,添加主键
ALTER TABLE Persons
ADD PRIMARY KEY (P_Id)
添加多个列为主键
ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
删除单列主键
ALTER TABLE Persons
DROP PRIMARY KEY
删除多列
ALTER TABLE Persons
DROP CONSTRAINT pk_PersonID
- FOREIGN KEY :外键, 保证一个表中的数据匹配另一个表中的值的参照完整性。用于预防破坏表之间连接的行为,也能防止非法数据插入外键列,因为它必须是它指向的那个表中的值之一。
- CHECK :保证列中的值符合指定的条件。对单个列定义 CHECK 约束,那么该列只允许特定的值;对一个表定义 CHECK 约束,那么此约束会基于行中其他列的值在特定的列中对值进行限制。
例:建表时定义
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
)
修改此约束
ALTER TABLE Persons
ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
删除此约束
ALTER TABLE Persons
DROP CHECK chk_Person
- DEFAULT: 规定没有给列赋值时的默认值。通过使用类似 GETDATE() 这样的函数,DEFAULT 约束也可以用于插入系统值。
例:建表时定义
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
)
使用 GETDATE() 函数
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
OrderDate date DEFAULT GETDATE()
)
修改此约束
ALTER TABLE Persons
ALTER City SET DEFAULT 'SANDNES'
撤销此约束
ALTER TABLE Persons
ALTER City DROP DEFAULT
1-5 表的删除和更新
1-5-1 表的删除
DROP TABLE <表名>;
删除了的表是无法恢复的。
1-5-2 表的更新
1.添加列
ALTER TABLE <表名> ADD COLUMN <列的定义> <数据类型>;
2.删除列
ALTER TABLE <表名> DROP COLUMN <列的定义> ;
注意:表定义变更后无法恢复
3.插入数据
BEGIN TRANSACTION;
INSERT INTO <表名> ( 列名1, 列名2,...列名N )
VALUES
( value1, value2,...valueN ),
( valueA1, valueA2,...valueAN ),
( valueB1, valueB2,...valueBN ),
……;
COMMIT;
如果数据是字符型,必须使用单引号或者双引号,如:"value"。
BEGIN TRANSACTION语句是开始插入行的指令语句,COMMIT语句是确定插入行的指定语句。