[第二周] 认识sql并学习数据库的基础操作

0x00 本周目标

  • 任务目标:认识 sql 并学习数据库的基本操作。
  • 任务介绍
    • 什么是关系型和非关系型数据库,两者都包含哪些种类的数据库(理解两者的区别);
    • 选择一种关系型数据库进行学习;
    • 学习数据库中的字段类型并创建库和用户表,需要包含所有字段类型(主要熟悉数据库的基本使用,可以自由创建、删除、修改数据库和表);
    • 学习数据库的增删改查,记录学习过程(重点是 sql 语句的理解)并形成报告(最终成果)。

0x01 关系型与非关系型数据库

1.1 关系型数据库

关系型数据库是以关系模型来创建的数据库。

关系模型就是指二维表格模型。

一个关系型数据库就是由二维表及其之间的联系组成的一个数据组织。

优点:

  1. 易于维护:丰富的完整性,都是使用表结构,格式一致;
  2. 使用方便:SQL语言通用,可用于复杂查询;
  3. 复杂操作:支持SQL,可用于一个表以及多个表之间非常复杂的查询;
  4. 容易理解:二维表结构是非常贴近逻辑世界一个概念,关系型 相对网状、层次等其他模型来说更容易理解。

缺点:

  1. 读写性能比较差,尤其是海量数据的高效率读写;
  2. 固定的表结构,灵活度稍欠;
  3. 高并发读写需求,传统关系型数据库来说,硬盘I/O是一个很大的瓶颈。

1.2 非关系型数据库

非关系型数据库严格上不是一种数据库,应该是一种数据结构化存储方法的集合。

非关系型数据库是以 非关系模型 来创建的数据库。

非关系模型有 列模型、键值对模型、文档类模型。

优点:

  1. 格式灵活:存储数据的格式可以是 key、value形式、文档形式、图片形式等等,使用灵活,应用场景广泛,而关系型数据库 只支持 基础类型;
  2. 速度快:nosql 可以使用硬盘或者随机存储器作为载体,而 关系型数据库只能使用硬盘
  3. 高扩展性:基于键值对,数据没有耦合性,容易扩展;
  4. 读写性能强大:无需经过 sql 层的解析,读写性能很高;
  5. 成本低:nosql 数据库部署简单,基本都是开源软件。

缺点:

  1. 不提供 sql 支持,学习和使用成本较高;
  2. 无事务处理,附加功能 bi 和 报表等支持也不好;
  3. 数据结构相对复杂,复杂查询方面稍欠。

非关系型数据库的分类和比较:

  1. 文档型:
  2. key-value型
  3. 列式数据库
  4. 图形数据库

1.3 关系型与非关系型 区别

1. 数据存储方式不同。

  • 关系型和非关系型数据库的主要差异是数据存储的方式。关系型数据天然就是表格式的,因此存储
    在数据表的行和列中。数据表可以彼此关联协作存储,也很容易提取数据。

  • 与其相反,非关系型数据不适合存储在数据表的行和列中,而是大块组合在一起。非关系型数据通
    常存储在数据集中,就像文档、键值对或者图结构。你的数据及其特性是选择数据存储和提取方式
    的首要影响因素。

2. 扩展方式不同。

  • SQL和NoSQL数据库最大的差别可能是在扩展方式上,要支持日益增长的需求当然要扩展。

  • 要支持更多并发量,SQL数据库是纵向扩展,也就是说提高处理能力,使用速度更快速的计算机,
    这样处理相同的数据集就更快了。
    因为数据存储在关系表中,操作的性能瓶颈可能涉及很多个表,这都需要通过提高计算机性能来客
    服。虽然SQL数据库有很大扩展空间,但最终肯定会达到纵向扩展的上限。而NoSQL数据库是横
    向扩展的。

  • 而非关系型数据存储天然就是分布式的,NoSQL数据库的扩展可以通过给资源池添加更多普通的
    数据库服务器(节点)来分担负载。

3. 对事务性的支持不同。

  • 如果数据操作需要高事务性或者复杂数据查询需要控制执行计划,那么传统的SQL数据库从性能和
    稳定性方面考虑是你的最佳选择。SQL数据库支持对事务原子性细粒度控制,并且易于回滚事务。

  • 虽然NoSQL数据库也可以使用事务操作,但稳定性方面没法和关系型数据库比较,所以它们真正
    闪亮的价值是在操作的扩展性和大数据量处理方面。

0x02 Mysql学习

2.1 MySQL基础

MySQL 是最流行的关系型数据库管理系统

在 WEB 应用方面 MySQL 是最好的 RDBMS(Relational Database Management System:关系数据库管理系统)应用软件之一。

2.1.1 什么是数据库?

数据库(Database)是按照数据结构来组织、存储和管理数据的仓库。每个数据库都有一个或多个不
同的 API 用于创建,访问,管理,搜索和复制所保存的数据。

我们也可以将数据存储在文件中,但是在文件中读写数据速度相对较慢。

所以,现在我们使用关系型数据库管理系统(RDBMS)来存储和管理的大数据量。所谓的关系型数据
库,是建立在关系模型基础上的数据库,借助于集合代数等数学概念和方法来处理数据库中的数据。

RDBMS 即关系数据库管理系统(Relational Database Management System)的特点:

1.数据以表格的形式出现;
2.每行为各种记录名称;
3.每列为记录名称所对应的数据域;
4.许多的行和列组成一张表单;
5.若干的表单组成database。

2.1.2 RDBMS 术语

数据库: 数据库是一些关联表的集合。
数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
列: 一列(数据元素) 包含了相同类型的数据, 例如邮政编码的数据。
行:一行(=元组,或记录)是一组相关的数据,例如一条用户订阅的数据。
冗余:存储两倍数据,冗余降低了性能,但提高了数据的安全性。
主键:主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
外键:外键用于关联两个表。
复合键:复合键(组合键)将多个列作为一个索引键,一般用于复合索引。
索引:使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性
约束条件,目的是保证数据的一致性。

MySQL 为关系型数据库(Relational Database Management System), 这种所谓的"关系型"可以理解
为"表格"的概念, 一个关系型数据库由一个或数个表格组成。

表头(header): 每一列的名称;
列(col): 具有相同数据类型的数据的集合;
行(row): 每一行用来描述某条记录的具体信息;
值(value): 行的具体信息, 每个值必须与该列的数据类型相同;
键(key): 键的值在当前列中具有唯一性。

2.1.3 MySQL数据类型

MySQL中定义数据字段的类型对于数据库的优化是非常重要的。
MySQL支持多种类型,大致可以分为三类:数值、日期/时间和字符串(字符)类型。

1、数值 数据类型

MySQL支持所有标准SQL数值数据类型。

这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC),以及近似数值数据类
型(FLOAT、REAL和DOUBLE PRECISION)。

关键字INT是INTEGER的同义词,关键字DEC是DECIMAL的同义词。

BIT数据类型保存位字段值,并且支持MyISAM、MEMORY、InnoDB和BDB表。

作为SQL标准的扩展,MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要
的每个整数类型的存储和范围。

2、日期/时间 数据类型

​ 表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
​ 每个时间类型有一个有效值范围和一个"零"值,当指定不合法的MySQL不能表示的值时使用"零"值。
​ TIMESTAMP类型有专有的自动更新特性,将在后面描述。

3、浮点型

4、字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。

该节描述了这些类型如何工作以及如何在查询中使用这些类型。

常见字段类型事项:

  1. char(n)和varchar(n)中括号中n代表字符的个数,并不代表字节个数,所以当使用了中文的
    时候(UTF8)意味着可以插入m个中文,但是实际会占用m*3个字节;
  2. 同时char和varchar最大的区别就在于char不管实际value长度多少,都会占用n个字符的空间,
    而varchar只会占用实际字符应该占用的空间+1{varchar在结尾会有结束符}),并且实际空间+1<=n;
  3. 超过char和varchar的n设置后,字符串会被截断;
  4. char的上限为255字节,varchar的上限65535字节,text的上限为65535;
  5. char在存储的时候会截断尾部的空格,varchar和text不会;
  6. varchar会使用1-3个字节来存储长度,text不会。

0x03 Mysql 数据库操作

3.1 MySQL连接

3.1.1 使用MySQL 二进制方式连接

可以使用 MySQL 二进制方式进入到 MySQL 命令提示符下来进行连接 MySQL数据库。

实例:

所用命令:mysql -uroot -p

在登录成功后会出现 mysql> 命令提示窗口,你可以在上面执行任何 SQL 语句。

以上命令执行后,登录成功输出结果如下:

退出 mysql> 命令:

mysql> exit
Bye

3.1.2 使用PHP 脚本连接 MySQL

PHP 提供了 mysqli_connect() 函数来连接数据库。

该函数有 6 个参数,在成功链接到 MySQL 后返回连接标识,失败返回 FALSE 。

语法:

mysqli_connect(host,username,password,dbname,port,socket);

参数说明:

你可以使用 PHP 的 mysqli_close() 函数来断开与 MySQL 数据库的链接。

该函数只有一个参数为 mysqli_connect() 函数创建连接成功后返回的 MySQL 连接标识符。

语法:

bool mysqli_close ( mysqli $link )

本函数关闭指定的连接标识所关联的到 MySQL 服务器的非持久连接。如果没有指定 link_identifier,则关闭上一个打开的连接。

**提示:**通常不需要使用 mysqli_close(),因为已打开的非持久连接会在脚本执行完毕后自动关闭。

实例:

连接 MySQL 服务器:

在网站根目录下创建 mysqli_connect.php 实例文件

mysqli_connect.php 实例文件内容:

<?php
$dbhost = 'localhost';  // mysql服务器主机地址
$dbuser = 'root';            // mysql用户名
$dbpass = '123456';          // mysql用户名密码
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
    die('Could not connect: ' . mysqli_error());
}
echo '数据库连接成功!';
mysqli_close($conn);
?>

3.2 数据库的基本操作

3.2.1 创建数据库

show databases;		查看数据库
use wpeter;			使用 wpeter 数据库
create database 数据库名;	创建数据库

3.2.2 删除数据库

drop 命令删除数据库:

drop database 数据库名;		drop 命令删除数据库
例如:drop database wpeter;

使用 mysqladmin 删除数据库:

使用 mysql mysqladmin 命令在 终端 来执行删除命令

[root@bogon wpeter]# mysqladmin -u root -p drop wpeter
Enter password: 

执行以上删除数据库命令后,会出现一个提示框,来确认是否真的删除数据库:

3.3 数据表的基本操作

创建表:
create table 表名 (
列名 数据类型,
......
)engine myisam charset utf8;

desc 表名; 	#显示表的属性、属性类型、主键信息,是否为NULL等信息
show columns from 表名; #同上
drop table 表名;	 #删除表
rename table oldname to newname; #更改表名
select * from 表名;	 #查看表的数据内容
truncate 表名; #删除表,再重新建表;与delete的区别是delete是相对行而言,是把表内的
数据擦除。

3.3.1 MySQL 创建数据表

​ 创建 MySQL 数据表 需要一下信息:

  • 表名

  • 表字段名

  • 定义每个表字段

    语法:

    以下为创建MySQL数据表的SQL通用语法:

create table table_name (column_name columntype);

​ 以下例子中我们将在 wpeter 数据库中创建 员工数据表 worker:

create table workers(
id INT UNSIGNED AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
gender VARCHAR(2) NOT NULL,
birthday DATE,
email VARCHAR(10),
remark VARCHAR(50),
PRIMARY KEY (id)
)ENGINE=InnoDB default CHARSET=utf8;

读取数据表:

desc 表名
show column from 表名

实例解析:

  • 如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL, 在操作数据库时如果输入该字段的数据为NULL ,就会报错。

  • AUTO_INCREMENT定义列为自增的属性,一般用于主键,数值会自动加1。

  • PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键,列间以逗号分隔。

  • ENGINE 设置存储引擎,CHARSET 设置编码。

3.3.2 进行 表 的修改

增加年龄列,将 email列 长度变为 30,删除 remark列,将name修改为 username;

ALTER TABLE workers ADD COLUMN age INT;		#增加年龄列
ALTER TABLE workers MODIFY COLUMN email VARCHAR(30);	#修改email长度为30
ALTER TABLE workers DROP remark;		#删除remark列
ALTER TABLE workers CHANGE COLUMN name username VARCHAR(20);	#修改name为username

操作成功:

3.3.3 数据表的增删改查

插入数据:

INSERT INTO workers VALUES(1,'张三','男','1930-11-20','123@qq.com',22);
INSERT INTO workers VALUES(2,'李四','男','1950-11-20','234@126.com',30);
INSERT INTO workers VALUES(3,'王二','男','1960-10-20','hello@qq.com',33);
INSERT INTO workers VALUES(4,'张花','女','1970-11-21','223@qq.com',23);
INSERT INTO workers VALUES(5,'如花','女','1980-9-21','223223@qq.com',33);

查看表单所有数据:

SELECT * FROM workers;

查看表单中的id、username、email、age数据:

SELECT id,username,email,age FROM workers;

将 如花 的性别改为男:

UPDATE workers SET gender = '男' WHERE id = 5;

删除 张花 的年龄为23 那一行:

DELNET FROM workers WHERE age = 23;

将张三的年龄也设置为33:

UPDATE workers SET age = 33 WHERE id = 1;

将李四改为李四(如花的亲戚),性别女,email为:8888@139.com :

UPDATE workers SET username = '李四(如花的亲戚)',gender = '女',email = '8888@139.com' WHERE id = 3;