了解RDBMS和SQL的基础和高级概念用于数据科学和分析
#python #sql #sqlite #100daysofdatascience

100天的第7天数据科学训练营从NOOB到专家。

github链接：Complete-Data-Science-Bootcamp

主要帖子：Complete-Data-Science-Bootcamp

回顾第6天

昨天，我们详细研究了使用Matplotlib和Seaborn在Python中的数据可视化。

开始吧

DBMS（数据库管理系统）是一个软件应用程序，可与最终用户，其他应用程序和数据库本身进行交互，以捕获和分析数据。它是一种允许数据科学家存储，管理和检索大量结构化和非结构化数据的工具。

有不同类型的DBMS，例如关系数据库，NOSQL数据库和图形数据库。

  • 关系数据库，例如MySQL和PostgreSQL，使用结构化查询语言（SQL）来管理存储在具有行和列的表中的数据。这些数据库非常适合可以轻松组织为表格格式的数据，并且对于数据仓库，商业智能和其他报告应用程序很有用。

  • NOSQL数据库，例如MongoDB和Cassandra，旨在处理大量的非结构化数据，并支持不同的数据模型，例如文档，键值和图形。这些数据库对大数据和实时应用程序有用，例如实时分析，社交媒体和游戏。

  • 图形数据库（例如Neo4J和Amazon Neptune）用于以图形格式存储和查询数据，其中节点和边缘代表实体和关系。这些数据库对于涉及复杂关系的应用程序很有用，例如欺诈检测，推荐系统和社交网络分析。

在数据科学中，DBM的选择取决于数据的类型和数量，数据之间的关系的复杂性以及应用程序的性能和可伸缩性要求。

在本文中，我们将详细研究关系数据库

基本理解

关系数据库是将数据组织到表中的一种数据库，每个表由行和列组成。

  • Tables：表是组织成行和列的相关数据的集合。每个表都有一个唯一的名称，可以在数据库中标识它。例如，名为“客户”的表格可能包含有关公司所有客户的信息，每行代表不同的客户，每列代表有关该客户的不同信息，例如名称，地址和电话号码。

  • Columns：列是表中的垂直值集，它具有名称和数据类型。每列代表表中数据的特定属性，例如“ customer_name”或“ customer_address”。例如，在名为“客户”的表中，这些列可以为“ customer_id”，“ customer_name”，“ customer_address”，“ customer_phone_number”，“ customer_email”等。

  • Rows：一行是表中的水平值集，它代表表中的单个记录或元组。每行代表表中数据的唯一实例，例如特定客户。例如，在名为“客户”的表中，一行可以代表一个具有诸如“ customer_id”，“ customer_name”，“ customer_name”，“ 123 main st”之类的值的客户“，” 555-555-5555“用于“ customer_phone_number”和“ customer_email”的“ customer_phone_number”和“ johndoe@gmail.com”

  • Primary keys：主键是表中唯一标识表中每一行的列或列集。每个表只能具有一个主键，它用于执行数据的完整性并与其他表建立关系。例如，在名为“客户”的表中，“ customer_id”列可能是主要键，因为它是每个客户唯一的，并且可以用于标识表中的特定客户。

  • Foreign keys：外键是表中的一列或一组列，它是指另一个表的主键。它用于在表和执行参考完整性之间建立关系。例如，在包含有关客户订单的信息的名为“订单”的表中，“ customer_id”列可能是一个外键，它涉及“客户”表中的主要密钥“ customer_id”。

  • Indexing：索引是创建单独的数据结构的过程，该过程允许在表中更快地搜索和分类数据。可以在表中的一个或多个列上创建索引，以提高搜索特定数据的查询性能。例如，如果您经常通过其姓氏来搜索客户，则可以在“客户”表中的“ Customer_name”列上创建索引，以加快这些搜索。

这是“客户”表的示例：

customer_id customer_name customer_address customer_phone_number customer_email
1 John Doe 123 Main St 555-555-5555 johndoe@gmail.com
2 简·史密斯 456 Park Ave 555-555-5556 janesmith@gmail.com
3 鲍勃·约翰逊 789 ELM ST 555-555-5557 bobjohnson@gmail.com

在此示例表中，“ Customer_ID”列是主要键，用于唯一标识每个客户。其他列，例如“ customer_name”，“ customer_address”，“ customer_phone_number”和“ customer_email”是非钥匙属性，它们提供了有关每个客户的其他信息。

可以在任何列上创建索引，以加快查询性能。在此示例中，如果我们经常需要以其名称搜索客户，则可以在“ Customer_name”列上创建索引。

重要的是要注意，该示例是表的非常简单的表示，在现实世界中，表可以将多个列作为主键，多个外键和带有多个表的复杂数据模型，但是基本概念保持不变。

SQL Quries

我们将使用python和sqlite

执行所有查询

连接到数据库

要开始与数据库进行交互，我们首先需要建立一个连接。

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect('100daysofdatascience.db')

print("Opened database successfully");

成功打开数据库

样本数据 

# Customers table:
conn.execute('''CREATE TABLE customers (
                        id INT PRIMARY KEY,
                        first_name VARCHAR(255),
                        last_name VARCHAR(255),
                        city VARCHAR(255)
                    );''')

conn.execute('''INSERT INTO customers (id, first_name, last_name, city) 
                            VALUES (1, 'John', 'Smith', 'Paris'),
                                   (2, 'Mary', 'Johnson', 'London'),
                                   (3, 'Michael', 'Williams', 'Berlin'),
                                   (4, 'Brad', 'Brown', 'Rome');''')

print("Customer table created successfully!")

客户表成功创建了！

# Products table:
conn.execute('''CREATE TABLE products (
                        id INT PRIMARY KEY,
                        product_name VARCHAR(255),
                        category VARCHAR(255),
                        price DECIMAL(10,2),
                        in_stock BOOLEAN
                    );''')

conn.execute('''INSERT INTO products (id, product_name, category, price, in_stock)
                        VALUES (1, 'MacBook Pro', 'electronics', 1500, true),
                               (2, 'iPhone', 'electronics', 1000, true),
                               (3, 'T-Shirt', 'clothing', 20, true),
                               (4, 'Jeans', 'clothing', 50, false);''')

print("Products table created successfully!")

产品表成功创建了！

# Orders table:
conn.execute('''CREATE TABLE orders (
                        id INT PRIMARY KEY,
                        customer_id INT,
                        order_date DATE,
                        total DECIMAL(10,2)
                    );''')

conn.execute('''INSERT INTO orders (id, customer_id, order_date, total)
                        VALUES (1, 1, '2021-01-01', 100),
                               (2, 2, '2021-01-02', 200),
                               (3, 3, '2021-01-03', 150),
                               (4, 2, '2021-01-04', 75);''')

print("Orders table created successfully!")

订单表成功创建了！

# Create the employee table
conn.execute('''CREATE TABLE employees
                (id INT PRIMARY KEY NOT NULL,
                name TEXT NOT NULL,
                salary REAL);''')

# Insert data into the employee table
conn.execute("INSERT INTO employees (id, name, salary) VALUES (1, 'John Smith', 50000)")
conn.execute("INSERT INTO employees (id, name, salary) VALUES (2, 'Mary Johnson', 55000)")
conn.execute("INSERT INTO employees (id, name, salary) VALUES (3, 'Michael Williams', 60000)")
conn.execute("INSERT INTO employees (id, name, salary) VALUES (4, 'Brad Brown', 65000)")

print("Employees table created successfully!")

员工表成功创建了！

conn.commit()

以下是数据科学家可能用来检索和操纵数据的基本的SQL查询的一些示例：

选择：

选择语句用于从一个或多个表中检索数据。例如，以下查询从称为“客户”的表中检索所有列：

import pandas as pd
data = pd.read_sql_query("SELECT * FROM customers", conn)
data
id first_name last_name 城市
0 1 约翰 史密斯 巴黎
1 2 玛丽 约翰逊 伦敦
2 3 迈克尔 威廉姆斯 柏林
3 4 brad 棕色 罗马

在哪里：

where子句用于过滤Select语句返回的数据。例如，以下查询从“客户”表中为“纽约”的客户检索所有列：

data = pd.read_sql_query("SELECT * FROM customers WHERE city = 'London'", conn)
data
id first_name last_name 城市
0 2 玛丽 约翰逊 伦敦

加入：

基于它们之间的相关列，使用JOIN子句将来自两个或多个表的行组合。例如，以下查询从“订单”表和“客户”表中检索所有列，其中“订单”表中的custeruty_id表与“客户”表中的ID匹配：

data = pd.read_sql_query("SELECT * FROM orders JOIN customers ON orders.customer_id = customers.id", conn)
data
id customer_id order_date 总计 id first_name last_name 城市
0 1 1 2021-01-01 100 1 约翰 史密斯 巴黎
1 2 2 2021-01-02 200 2 玛丽 约翰逊 伦敦
2 3 3 2021-01-03 150 3 迈克尔 威廉姆斯 柏林
3 4 2 2021-01-04 75 2 玛丽 约翰逊 伦敦

更新：

更新语句用于在表中修改数据。例如，以下查询将“员工”表中所有员工的薪水更新10％：

conn.execute('''UPDATE employees SET salary = salary * 10;''')
conn.commit()

data = pd.read_sql_query("SELECT * FROM employees", conn)
data
id 名称 薪水
0 1 约翰·史密斯 500000.0
1 2 玛丽·约翰逊 550000.0
2 3 迈克尔·威廉姆斯 600000.0
3 4 布拉德·布朗 650000.0

删除：

删除语句用于从表中删除数据。例如，以下查询从“员工”表中删除所有行，其中薪水小于$ 50,000：

conn.execute('''DELETE FROM employees WHERE salary < 550000;''')
conn.commit()
data = pd.read_sql_query("SELECT * FROM employees", conn)
data
id 名称 薪水
0 2 玛丽·约翰逊 550000.0
1 3 迈克尔·威廉姆斯 600000.0
2 4 布拉德·布朗 650000.0

通过...分组：

基于一个或多个列的选择语句中，该子句的组用于将行分组。例如，以下查询检索每个产品类别的总IN_STOCK：

data = pd.read_sql_query("SELECT category, SUM(in_stock) FROM products GROUP BY category;", conn)
data
类别 sum（in_stock）
0 服装 1
1 电子 2

订单作者：

按子句的顺序用于对Select语句返回的数据进行分类。例如，以下查询从“客户”表中检索所有列，并按姓氏排序排序：

data = pd.read_sql_query("SELECT * FROM customers ORDER BY last_name;", conn)
data
id first_name last_name 城市
0 4 brad 棕色 罗马
1 2 玛丽 约翰逊 伦敦
2 1 约翰 史密斯 巴黎
3 3 迈克尔 威廉姆斯 柏林

限制：

限制子句用于限制Select语句返回的行数。例如，以下查询通过in_stocks检索前2个产品：

data = pd.read_sql_query("SELECT * FROM products ORDER BY in_stock DESC LIMIT 2;", conn)
data
id product_name 类别 价格 in_stock
0 1 MacBook Pro 电子 1500 1
1 2 iPhone 电子 1000 1

喜欢：

类似运算符用于搜索列中的特定模式。例如，以下查询检索所有姓氏以“ S”开头的客户：

data = pd.read_sql_query("SELECT * FROM customers WHERE last_name LIKE 'S%';", conn)
data
id first_name last_name 城市
0 1 约翰 史密斯 巴黎

内部联接：

内部联接关键字用于根据它们之间的相关列组合两个或多个表的行。这只有在两张表中至少有一个匹配时才会返回行。例如：

data = pd.read_sql_query('''SELECT orders.id, customers.first_name
                            FROM orders
                            INNER JOIN customers ON orders.customer_id = customers.id;''', conn)
data
id first_name
0 1 约翰
1 2 玛丽
2 3 迈克尔
3 4 玛丽

外连接：

外部联接关键字用于根据它们之间的相关列组合两个或多个表的行。这将从一个表和另一个表的匹配行中返回所有行。如果没有匹配，则将返回零值。例如：

data = pd.read_sql_query('''SELECT orders.id, customers.first_name
                            FROM orders
                            LEFT OUTER JOIN customers ON orders.customer_id = customers.id;
                            ''', conn)
data
id first_name
0 1 约翰
1 2 玛丽
2 3 迈克尔
3 4 玛丽

联盟：

联合操作员用于组合两个或多个选择语句的结果集。默认情况下，联合操作员仅选择不同的值。例如：

data = pd.read_sql_query('''SELECT * FROM orders UNION SELECT * FROM customers;''', conn)
data
id customer_id order_date 总计
0 1 1 2021-01-01 100
1 1 约翰 史密斯 巴黎
2 2 2 2021-01-02 200
3 2 玛丽 约翰逊 伦敦
4 3 3 2021-01-03 150
5 3 迈克尔 威廉姆斯 柏林
6 4 2 2021-01-04 75
7 4 brad 棕色 罗马

指数：

索引用于通过允许数据库查找和检索特定行的速度来提高数据库的性能。例如，以下命令在“客户”表中的“ last_name”列上创建索引：

conn.execute('''CREATE INDEX last_name_indexes ON customers (last_name);''')
conn.commit()

日期和时间功能：

SQL提供了许多功能，可与日期和时间数据类型一起使用。一些示例包括：

  • curdate（） - 返回当前日期

  • now（） - 返回当前日期和时间

  • 年（） - 返回给定日期的年

  • 月（） - 返回给定日期的月份

  • day（） - 返回给定日期的当天

我们正在使用sqlite。因此，SQLITE提供了几个内置功能，用于使用日期和时间。这是sqlite中一些最常用的日期和时间函数的列表：

  • date(timestring, modifier, modifier, ...)：此功能返回日期时间字符串的日期部分。

  • time(timestring, modifier, modifier, ...)：此功能返回日期时间字符串的时间部分。

  • datetime(timestring, modifier, modifier, ...)：此功能返回日期时间字符串的日期和时间部分。

  • julianday(timestring, modifier, modifier, ...)：此功能返回朱利安日 - 公元前4714年11月24日中午在格林威治以来的天数。

  • strftime(format, timestring, modifier, ...)：此功能根据指定格式返回日期和时间的字符串表示。格式字符串可以在日期和时间的不同部分中包含各种占位符，例如年度％y，每月％m，每天％d，小时％h，分钟为％m，％m和％第二个。

  • date(timestring, '+' or '-', number, 'days' or 'months' or 'years')：此功能允许您从日期添加或减去几天，几个月或几年。

  • current_date，current_time，current_timestamp：这些功能分别返回当前日期，时间和时间戳。

  • year(timestring)，month(timestring)，day(timestring)，hour(timestring)，minute(timestring)，second(timestring)：这些功能分别返回给定时间的年度，月，每日，每日，小时，分钟和第二个。

例如，以下查询检索了当年每个月的总销售额：

data = pd.read_sql_query('''SELECT strftime('%m', order_date) as month, SUM(total) as totals
                                FROM orders
                                WHERE strftime('%Y', order_date) = strftime('%Y', 'now')
                                GROUP BY month;''', conn)

处理零值：

SQL提供了几个运算符和功能来处理空值。一些示例包括：

  • 是空的 - 用于检查零值

  • 不是null-用于检查非空值

  • cocece（） - 返回表达式列表中的第一个非零值

  • nullif（） - 如果两个表达式相等，则返回null，否则返回第一个表达式

例如，以下查询检索所有尚未下订单的客户：

data = pd.read_sql_query('''SELECT * FROM customers
                                WHERE id NOT IN (SELECT customer_id FROM orders)
                                ''', conn)
data
id first_name last_name 城市
0 4 brad 棕色 罗马

其他数据类型：

SQL还支持其他数据类型，例如blob（二进制大对象）来存储二进制数据，布尔值存储true/false值以及用于存储预定义的字符串的枚举。

例如，以下查询会检索所有带有图像的产品，并且只有当产品有库存时：

data = pd.read_sql_query('''SELECT product_name, in_stock FROM products
                                    WHERE product_name IS NOT NULL AND in_stock = TRUE;
                                    ''', conn)
data
product_name in_stock
0 MacBook Pro 1
1 iPhone 1
2 T恤 1

概括

本文提供了有关数据科学和分析的关系数据库管理系统（RDBMS）和SQL（结构性查询语言）的综合指南。它涵盖了RDBM和SQL的基础知识和高级概念，包括数据类型，查询以及高级概念，例如Join和Subquies。本文还提供了示例和代码片段，以帮助说明这些概念并提供动手学习经验。本文的主要目标是帮助数据科学家，分析师和开发人员了解RDBMS和SQL在管理和分析大型数据集中的重要性。

练习问题，您将在Github的第6天的练习笔记本中找到。

如果您喜欢它，然后...

Buy Me A Coffee