Task 01: 初识数据库
1.1 初识数据库
- 什么是数据库?
数据库是一个以某种有组织的方式存储数据的集合。可以把它想像成一个文件柜,这个文件柜是一个存放数据的物理位置,不管数据是什么,也不管数据是如何组织的。
数据库(database,DB):保存有组织的数据的容器(通常是一个文件或一组文件)。
数据库的种类:
- 层次数据库 (Hierarchical Database, HDB)
- 关系数据库 (Relational Database, RDB)
1.1.1 DBMS 的种类
数据库管理系统(Database Management System,DBMS):用来管理(创建和操纵)数据库的计算机系统。人们通常使用数据库这个术语来代表他们使用的数据库软件,这是不正确的。确切地说,数据库软件应称为数据库管理系统。
比较具有代表性的商业级关系数据库管理系统(RDBMS)包括:
- 甲骨文公司的Oracle
- Database Microsoft公司的 SQL
- Server IBM公司的 DB2 Universal
Database - 开源的MySQL
1.1.2 RDBMS 的常见系统结构
使用RDBMS时,最常见的系统结构就是客户端/服务器类型(C/S类型)
1.2 初识 SQL
1970年,IBM研究院的E.F.Codd博士发表了一篇名为“大型共享数据银行的数据关系模型“的论文,提出使用表集合来表示数据,但相关条目之间并不使用指针来导航,而是借助冗余数据来连接不同表中的记录。这里的冗余数据指的就是一些表中包含了导航到其他表的信息。这些列被称为外键,用于作为账户信息网络结构中各实体之间的连线。
什么是 SQL ?
SQL是一种结构化查询语言,用于操控关系表中的数据。设计SQL的目的是很好的完成一样任务 –— 提供一种从数据库中读写数据的方法(简单有效)。
SQL语句分为三类:
- SQL模式语句:用于创建数据库对象(表、索引、约束等)的语句。
- SQL数据语句:用于创建、操纵和检索存在数据库中数据的语句。
- SQL 事务句:用于开始、结束或回滚事务。
SQL 的扩展
许多 DBMS 厂商通过增加语句或指令,对 SQL 进行了扩展。这种扩展的目的是提供执行特定操作的额外功能或简化方法。
- DDL
DDL (Data Definition Language, 数据定义语言) 用来创建或者删除存储数据用的数据库中的表等对象。
DDL包含以下几种指令: -
CREATE:创建**数据库和表**等对象 DROP:删除**数据库和表**等对象 ALTER:修改**数据库和表**等对象
- DML
DML (Data Manipulation Language, 数据操纵语言) 用来查询或者变更表中的记录。
DML包含以下几种指令: -
SELECT :查询表中的数据
-
INSERT :向表中插入新数据
-
UPDATE : 更新表中的数据
-
DELETE : 删除表中的数据
- DCL
DCL (Data Control Language, 数据控制语言) 用来确认或者取消对数据库中的数据进行的变更。除此之外,还可以对RDBMS 的用户是否有权限操作数据库中的对象(数据库表等)进行设定。 -
COMMIT: 确认对数据库中的数据进行变更 ROLLBACK: 取消对数据库中的数据进行变更 GRANT: 赋予用户操作权限 REVOKE: 取消用户操作权限
1.2.1 MySQL 命令行实用程序
命令用;或\g结束,换句话说,仅按Enter不执行命令
输入quit 或 exit 退出命令行实用程序
SQL 不区分关键字的大小写,但是插入到表中的数据是区分大小写的
win 系统默认不区分表名及字段名的大小写
常数的书写方式是固定的
单词需要用半角空格或者换行来分隔
1.2.2 MySQL 数据库的主要操作
MySQL 数据库的主要操作包括:
- 创建数据库
- 选择数据库
- 修改数据库
- 查看数据库
- 删除数据库
- 数据库的备份、还原等
1. 创建数据库 (CREATE DATABASE 语句)
语法: CREATE DATABASE < 数据库名 >;
查找数据库在哪里 SHOW VARIABLES LIKE 'datadir';
2. 选择数据库
语法: USE <数据库名>;
数据库命名规则:
- 不能与其他数据库重名;
- 由任意字母、阿拉伯数字、下划线 _ 、和”$”组成,但不能使用单独的数字。
- 不能使用关键字;
- Windows不区分大小写,Linux区分
3. 修改数据库
ALTER DATABASE 数据库名
DEFAULT CHARACTER SET 字符集
DEFAULT COLLATE 比较规则;`
4. 查看数据库
SHOW DATABASES;
5. 删除数据库
DROP DATABASE < 数据库名 >;
1.2.3 MySQL 数据类型
-
Eg:定义年龄字段age为无符号数,可以使用SQL片段: ```sql age TINYINT UNSIGNED ``` 其中用unsigned约束年龄age的取值不能为负数。
-
精确小数类型 decimal(length,precision) 用于表示精度确定(小数点后数字的位数确定)的小数类型, length决定该小数的最大位数,precision用于设置精度(小数点后数字的位数)。 eg: decimal(5,2)表示小数范围:-999.99~999.99 decimal(5,0)表示:-99999 ~ 99999的整数。
- 字符串类型在数据外观上使用单引号括起来,如 Teacher_name = ‘潘多拉’
- Gender enum(‘男’,‘女’) 选一个
Interest set(‘唱歌’,‘游泳’,‘网球’) 选多个
1.2.4 数据表结构
- Definition
数据表是由表名、表中的字段和表的记录三个部分组成的。设计数据表结构就是定义数据表文件名,确定数据表包含哪些字段,各字段的字段名、字段类型、及宽度等,并将这些数据输入到计算机当中。
在设计表结构时要考虑下面几个方面:
(1) 字段名要通俗易懂且具有代表性,字段名不允许重复,为了编程序方便,尽量使用英文名字。
(2) 字段类型选择的原则是:根据需求选择合适的字段类型,在满足需求的情况下字段类型尽可能小。不参加数学计算的数字定义为字符串型,如学号、电话号码等。
(3) 字段长度是指能容纳该字段的最大数值,如学号为12位数字,长度设置为12;课程名可能包含10个汉字,长度就要设置为10 .
(4) 字符集编码选择:含有中文字符时统一采用Utf8或gbk类型,以避免乱码情况的发生。
1.2.5 数据库表主要操作
1.2.5.1 创建数据库表
语法格式如下:
CREATE TABLE <表名>(
<字段名 1> <数据类型> [该字段约束条件],
<字段名 2> <数据类型> [该字段约束条件],
<字段名 3> <数据类型> [该字段约束条件],
... ...
[该表的约束条件 1], [该表的约束条件 2]) 其他选项;
1.2.5.2 查看数据表,显示表结构
SHOW TABLES; --查看数据列表命令
DESCRIBE/DESC <表名>; --查看表结构
SHOW CREATE TABLE <表名>; --查看表的详细信息
1.2.5.3 复制表结构
复制一个表结构有两种实现方法:
- 将源表的表结构复制到新表中,语法格式如下:
CREATE TABLE < 新表名 > LIKE < 源表 >;
- 复制表结构和表记录到新表中,
CREATE TABLE < 新表名 > SELECT * FROM < 源表 >;
1.2.5.4 修改表名
RENAME TABLE <旧表名> TO <新表名>;
或者
ALTER TABLE <旧表名> RENAME <新表名>;
1.2.5.5 删除表
DROP TABLE <表名>;
- 如果表之间存在外键约束,应先删除外键约束条件,再删除表。或者先删子表,再删父表。
1.2.6 表记录操作
- 插入记录
- 修改记录
- 删除记录
- 查询记录
1.2.6.1 插入记录
插入记录:
INSERT INTO < 表名 >(字段列表) VALUES(值列表)
- 插入一条记录,只对部分字段赋值
INSERT INTO < 表名 >(字段A, 字段B, 字段C) VALUES(值A, 值B, 值C)
- 插入一条记录,对所有字段赋值
INSERT INTO < 表名 > VALUES(值A, 值B, 值C, 值D, ...,值F);
- 一次插入多条纪录
INSERT INTO < 表名 >(字段列表) VALUES(值列表 1),(值列表 2),...,(值列表 n);
-- 向表中插入从另一个或多个表查询的结果集
INSERT INTO <目标表名>(字段列表 1) SELECT (字段列表 2) FROM 源表 WHERE 条件表达式;
1.2.6.2 修改记录 (update)
语法格式:UPDATE 表名 SET 字段名 1 = 值 1, 字段名 2 = 值 2, ...,字段名 n = 值 n WHERE 条件表达式;
其中,set 子句指定了要修改的字段以及该字段修改后的值。
where 子句指定了要修改的记录。若省略了where,则修改表中的所有记录。(指定哪几行的列值需要修改)
1.2.6.3 删除记录 (delete)
语法格式:DELETE FROM 表名 [WHERE 条件表达式];
说明:
- 如果没有指定 where 条件,将删除所有记录;如果制定了 where 条件,将按照指定的条件进行删除。
- 从多表中删除有外键约束的记录时,外键级联规则选项设置为 cascade.
- 清空某个表,使用 truncate 语句
语法格式:TRUNCATE[TABLE] 表名;
truncate table 用于完全清空一个表。清空记录的表如果是父表,truncate 命令将永远执行失败。
1.2.6.4 查询记录
SELECT * FROM 表名;
1.2.7 修改表结构 (ALTER TABLE 语句)
- 修改字段的相关信息
- 修改约束条件
- 修改表的其他选项
语法格式如下:
ALTER TABLE 表名 alter_spec [, alter_spec …];
alter_spec 定义要修改的内容,其常见语法片段如下:
ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name] --添加新字段
| ADD [CONSTRAINT [symbol]] PRIMARY KEY (index_col_name,…) --添加主键
| ADD [CONSTRAINT [symbol]] UNIQUE (index_col_name,…) --添加唯一索引
| ADD [CONSTRAINT [symbol]] FOREIGN KEY (index_col_name,…) REFERENCE (index_col_name,…) --添加外键
| ALTER [COLUMN] col_name { SET DEFAULT literal | DROP DEFAULT } --修改默认值
| CHANGE [COLUMN] old_col_name new_col_name column_definition --修改字段名及数据类型
| MODIFY [COLUMN] col_name column_definition -- 修改字段类型
| DROP [COLUMN] col_name -- 删除字段
| DROP PRIMARY KEY -- 删除主键
| DROP INDEX index_name -- 删除索引名称
| DROP FOREIGN KEY fk_symbol -- 删除外键
| table_options -- 更改表的其它选项
- ALTER TABLE 语句允许对表进行多个修改操作,其子句间用逗号分隔。
修改表的其他选项:
ALTER TABLE 表名 ENGINE = 新的存储引擎类型;
ALTER TABLE 表名 CHARSET = 新的字符集;
ALTER TABLE 表名 AUTO_INCREMENT = 新的初始值;
ALTER TABLE 表名 PACK_KEYS = 新的压缩类型;
1.2.8 索引
索引分类
- 普通索引:index 是 MySQL 的基本索引。普通索引允许字段重复,一个数据表中可以有多个索引。
- 唯一性索引:unique 索引对于普通索引来说有一点特别是唯一性索引所对应得值必须是唯一得,但允许为空值。
- 主键索引:primary key 索引,是一种特殊得唯一性索引,它不允许有空值,而一个数据表中只能创建一个主键索引。
- 全文索引:FULLTEXT类型索引,可以在CHAR\VARCHAR或者TEXT类型的字段上创建,一般用于拉丁语系,像英文。
- 单列索引:只包含一列得索引。
- 组合索引:是指在表的多个字段组合上创建得索引。
创建索引
创建索引的方法有两种:一是在创建表的同时创建索引,二是在已有表上创建索引。
- 在创建表时创建索引
语法格式:
CREATE TABLE 表名(
字段名 1 数据类型[约束条件],
字段名 2 数据类型[约束条件],
... ...
[其它约束条件],
[unique | fulltext] index [索引名](字段名[(长度)] [asc | desc])
)engine = 存储引擎类型 default charset = 字符集类型;
- 在已有表上创建索引
语法格式如下:
CREATE [unique | fulltext] index 索引名 ON 表名(字段名[(长度)][asc | desc])
- 创建普通索引示例
CREATE INDEX teacher_id_index ON teacher(teacher_id);
- 创建唯一性索引示例
CREATE UNIQUE INDEX teacher_id_name ON teacher(teacher_id);
删除索引
DROP INDEX 索引名 ON 表名;
- 索引的缺点
(1) 创建索引和维护索引需要耗费时间。
(2) 索引是需要占用物理内存的。
(3) 当对表中的数据进行增加、删除和修改的时候,索引也需要进行动态维护,这样就降低了数据库的维护速度。
下载安装指路
-
http://datawhale.club/t/topic/478/1