认识 sql 并学习数据库的基础操作

mysql的学习和数据操作

0x00 引言

当前数据库分为关系型数据库和非关系型数据库

关系型数据库

关系型数据库：指采用了关系模型来组织数据的数据库。 关系模型指的就是二维表格模型，而一个关系型数据库就是由二维表及其之间的联系所组成的一个数据组织。

关系模型中常用的概念：
关系：一张二维表，每个关系都具有一个关系名，也就是表名
元组：二维表中的一行，在数据库中被称为记录
属性：二维表中的一列，在数据库中被称为字段
域：属性的取值范围，也就是数据库中某一列的取值限制
关键字：一组可以唯一标识元组的属性，数据库中常称为主键，由一个或多个列组成
关系模式：指对关系的描述。其格式为：关系名(属性1，属性2， … … ，属性N)，在数据库中成为表结构

关系型数据库的优点：

• 容易理解：二维表结构是非常贴近逻辑世界的一个概念，关系模型相对网状、层次等其他模型来说更容易理解

• 使用方便：通用的SQL语言使得操作关系型数据库非常方便

• 易于维护：丰富的完整性(实体完整性、参照完整性和用户定义的完整性)大大减低了数据冗余和数据不一致的概率

  • 实体完整性这项规则要求每个数据表都必须有主键，而作为主键的所有字段，其属性必须是独一及非空值。

  • 参照的完整性要求关系中不允许引用不存在的实体。

    例如，如果在学生表和选修课之间用学号建立关联，学生表是主表，选修课是从表，那么，在向从表中输入一条新记录时，系统要检查新记录的学号是否在主表中已存在，如果存在，则允许执行输入操作，否则拒绝输入，这就是参照完整性。

  • 用户自定义完整性指针对某一具体关系数据库的约束条件，它反映某一具体应用所涉及的数据必须满足的语义要求。

    例如某个属性必须取唯一值，某个非主属性也不能取空值，某个属性的取值范围在0-100之间等

关系型数据库存在的问题

• 网站的用户并发性非常高，往往达到每秒上万次读写请求，对于传统关系型数据库来说，硬盘I/O是一个很大的瓶颈
• 网站每天产生的数据量是巨大的，对于关系型数据库来说，在一张包含海量数据的表中查询，效率是非常低的
• 在基于web的结构当中，数据库是最难进行横向扩展的，当一个应用系统的用户量和访问量与日俱增的时候，数据库却没有办法像web server和app server那样简单的通过添加更多的硬件和服务节点来扩展性能和负载能力。当需要对数据库系统进行升级和扩展时，往往需要停机维护和数据迁移。
• 性能欠佳：在关系型数据库中，导致性能欠佳的最主要原因是多表的关联查询，以及复杂的数据分析类型的复杂SQL报表查询。为了保证数据库的ACID特性，必须尽量按照其要求的范式进行设计，关系型数据库中的表都是存储一个格式化的数据结构。

数据库事务必须具备ACID特性，ACID分别是Atomic原子性，Consistency一致性， Isolation隔离性，Durability持久性。

当今十大主流的关系型数据库

Oracle，Microsoft SQL Server，MySQL，PostgreSQL，DB2， Microsoft Access， SQLite，Teradata，MariaDB(MySQL的一个分支)，SAP

非关系型数据库

非关系型数据库：指非关系型的，分布式的，且一般不保证遵循ACID原则的数据存储系统。

非关系型数据库结构

非关系型数据库以键值对存储，且结构不固定，每一个元组可以有不一样的字段，每个元组可以根据需要增加一些自己的键值对，不局限于固定的结构，可以减少一些时间和空间的开销。

非关系型数据库的优点

• 用户可以根据需要去添加自己需要的字段，为了获取用户的不同信息，不像关系型数据库中，要对多表进行关联查询。仅需要根据id取出相应的value就可以完成查询。
• 适用于SNS(Social Networking Services)中，例如facebook，微博。系统的升级，功能的增加，往往意味着数据结构巨大变动，这一点关系型数据库难以应付，需要新的结构化数据存储。由于不可能用一种数据结构化存储应付所有的新的需求，因此，非关系型数据库严格上不是一种数据库，应该是一种数据结构化存储方法的集合。

非关系型数据库的不足：

只适合存储一些较为简单的数据，对于需要进行较复杂查询的数据，关系型数据库显的更为合适。不适合持久存储海量数据

非关系型数据库的分类

非关系型数据库都是针对某些特定的应用需求出现的，因此，对于该类应用，具有极高的性能。依据结构化方法以及应用场合的不同，主要分为以下几类：

面向高性能并发读写的key-value数据库：

key-value数据库的主要特点是具有极高的并发读写性能
Key-value数据库是一种以键值对存储数据的一种数据库，类似Java中的map。可以将整个数据库理解为一个大的map，每个键都会对应一个唯一的值。
主流代表为Redis， Amazon DynamoDB， Memcached， Microsoft Azure Cosmos DB和Hazelcast

面向海量数据访问的面向文档数据库：

这类数据库的主要特点是在海量的数据中可以快速的查询数据
文档存储通常使用内部表示法，可以直接在应用程序中处理，主要是JSON。JSON文档也可以作为纯文本存储在键值存储或关系数据库系统中。
主流代表为MongoDB，Amazon DynamoDB，Couchbase， Microsoft Azure Cosmos DB和CouchDB

面向搜索数据内容的搜索引擎：

搜索引擎是专门用于搜索数据内容的NoSQL数据库管理系统。
主要是用于对海量数据进行近实时的处理和分析处理，可用于机器学习和数据挖掘
主流代表为Elasticsearch，Splunk，Solr，MarkLogic和Sphinx

面向可扩展性的分布式数据库：

这类数据库的主要特点是具有很强的可拓展性
普通的关系型数据库都是以行为单位来存储数据的，擅长以行为单位的读入处理，比如特定条件数据的获取。因此，关系型数据库也被成为面向行的数据库。相反，面向列的数据库是以列为单位来存储数据的，擅长以列为单位读入数据。
这类数据库想解决的问题就是传统数据库存在可扩展性上的缺陷，这类数据库可以适应数据量的增加以及数据结构的变化，将数据存储在记录中，能够容纳大量动态列。由于列名和记录键不是固定的，并且由于记录可能有数十亿列，因此可扩展性存储可以看作是二维键值存储。
主流代表为Cassandra，HBase，Microsoft Azure Cosmos DB， Datastax Enterprise和Accumulo

CAP理论

NoSQL的基本需求就是支持分布式存储，严格一致性与可用性需要互相取舍
CAP理论：一个分布式系统不可能同时满足C(一致性)、A(可用性)、P(分区容错性)三个基本需求，并且最多只能满足其中的两项。对于一个分布式系统来说，分区容错是基本需求，否则不能称之为分布式系统，因此需要在C和A之间寻求平衡
*C(Consistency)一致性 *

一致性是指更新操作成功并返回客户端完成后，所有节点在同一时间的数据完全一致。与ACID的C完全不同

*A(Availability)可用性 *

可用性是指服务一直可用，而且是正常响应时间。

*P(Partition tolerance)分区容错性 *

分区容错性是指分布式系统在遇到某节点或网络分区故障的时候，仍然能够对外提供满足一致性和可用性的服务。

关系型与非关系型数据库的比较

1. 成本：Nosql数据库简单易部署，基本都是开源软件，不需要像使用Oracle那样花费大量成本购买使用，相比关系型数据库价格便宜。

2. 查询速度：Nosql数据库将数据存储于缓存之中，而且不需要经过SQL层的解析，关系型数据库将数据存储在硬盘中，自然查询速度远不及Nosql数据库。

3. 存储数据的格式：Nosql的存储格式是key,value形式、文档形式、图片形式等等，所以可以存储基础类型以及对象或者是集合等各种格式，而数据库则只支持基础类型。

4. 扩展性：关系型数据库有类似join这样的多表查询机制的限制导致扩展很艰难。Nosql基于键值对，数据之间没有耦合性，所以非常容易水平扩展。

5. 持久存储：Nosql不使用于持久存储，海量数据的持久存储，还是需要关系型数据库

6. 数据一致性：非关系型数据库一般强调的是数据最终一致性，不像关系型数据库一样强调数据的强一致性，从非关系型数据库中读到的有可能还是处于一个中间态的数据，Nosql不提供对事务的处理。

最近的数据库排名

0x01 MySQL连接和基本查询

关于数据库的混淆

数据库软件应该称为DBMS（数据库管理系统），数据库是通过DBMS创建和操纵的容器。

1. 连接数据库

mysql -uroot -p -hlocalhost -p3389

2. 查询数据库

SHOW DATABASES;

3. 查询数据库中存在的表

USE mysql;         //选择想要查询的数据库 use database（数据库名）
SHOW TABLES;

4. 查询表列

SHOW COLUMNS FROM user;  //选择想要查询的表 show columns from user（表名）

5. 查询显示效果

\G:将查询到的横向表格纵向输出，方便阅读 \g:等价于“；”

6. SQL语句不区分大小写， 对所有SQL关键字使用大写，而对所有列和表名使用小写，这样做使代码更易于阅读和调试。

7. 注释

   #注释语句
   -- 注释语句
   /*注释语句*/

0x02 MYSQL创建数据库、数据库表和表的操作

1. 创建testdb数据库

CREATE DATABASE testdb;

2. 删除刚刚创建的testdb数据库

DROP DATABASE testdb;

3. 向创建的testdb数据库中添加testtb表，

use testdb;

CREATE TABLE testtb(
    id INT NOT NULL AUTO_INCREMENT,
    user VARCHAR(50) NOT NULL,
    title VARCHAR(100) NOT NULL,
    cerateTime DATE,
    descDesc TEXT,
    PRIMARY KEY('id')
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

4. 删除表

DROP table testdb;

5. 向testdb表中插入数据

INSERT INTO testtb (user,title,createTime,createDesc) VALUES ("wayxz","safe","2019-8-11","学习安全的必要性");

6. MySQL UPDATE 更新数据库表中的某个数据；

UPDATE testtb SET title ="php" WHERE id =12

7. MySQL DELETE 语句 删除表中数据

DELETE FROM testdb where id = 13;

0x03 MYSQL检索

1. 检索单个列

SELECT user FROM user \G

2. 检索多个列

SELECT host,user，password FROM user \g

3. 检索所有列

SELECT * FROM user \G

4. 让检索返回不同的值

SELECT DISTINCT hostname FROM user \g

去重前	去重后

5. 限制结果

SELECT user FROM user limit 3 \g          /只显示前三行
SELECT user FROM product limit 3,3        /第一个数为检索开始的位置（真正的位置为检索开始的行数+1，及从第4行开始检索），第二个数为要检索的行数
SELECT user FROM product limit 3 OFFSET 3  /等同于上一句SELECT user FROM product limit 3,3

6. 检索排序

SELECT user FROM user ORDER BY user \g

7. 降序检索

SELECT user,address FROM product ORDER BY user SEDC \g

0x04 过滤数据

1. 过滤条件

SELECT  user FROM product WHERE user =1 \g

2. 筛选某个条件

SELECT user FROM product WHERE  user < 10 \g

3. 不匹配检查

SELECT user FROM product WHERE  user <> 3 \g  
SELECT user FROM product WHERE  user != 3 \g   //等同于上一句

4. 范围值筛选

SELECT user FROM product WHERE user BETWEEN 1 AND 6 \g   //筛选值为1到6的user

5. 空值筛选

SELECT user FROM product WHERE user IS NULL \g

6. AND 操作符

SELECT user,product from product WHERE user = 1 AND product <=500 \g

7. OR操作符

SELECT user FROM product WHERE user =1 or user =3 \g

8. IN操作符

SELECT usre FROM product WHERE user IN (2,6) \g   //IN指定条件范围2和6，和OR的功能类似SELECT usre FROM product WHERE user =2 OR user =6

9. NOT 操作符

SELECT user FROM product WHERE user NOT IN (2,6) \g  //筛选条件不为2和6和其他条件

0x05 通配符

1. LIKE操作符

SELECT user FROM product WHERE user LIKE "1" \g

2. % 通配符 %能匹配多个字符 %不能匹配到null值

SELECT user,product FROM product WHERE product LIKE "2%" \g   //筛选以2开头的product  
SELECT user,product FROM product WHERE product LIKE "%3%" \g   //筛选含有3的product

3. _ 通配符 _只能匹配单个字符

SELECT user,product FROM product WHERE product LIKE "_3" \g
SELECT user,product FROM product WHERE product LIKE "__3" \g

0x06 MySQL 导出数据

mysqldump -uroot -p testdb > C:\Users\local\Desktop\dump.txt   \\将创建的testdb数据库导出到桌面的dump.txt文件