从零开始学习SQL操作:修改数据库和表

让我们一起来学习 SQL 基础知识,例如创建表、架构和视图。

SQL:我们都是这方面的专家吗?其实都是假装的,而且多亏了 百度,CSDN等技术分享平台,我们很多问题都是在这上面找到答案的。结合我们在 90 年代学习如何编码的丰富经验,我们对 PHPMyAdmin 和 LAMP 堆栈,集成环境中的现场工作基本上使我们成为专业程序员。如何继续在你的程序简历人生写下更多简历信息?而不是单单的我会某某集成环境下的开发?

SQL 的存在时间比我们的职业生涯还要长,那么为什么现在要开始一个关于它的系列呢?当然有足够多的文档供我们在需要编写查询时通过 百度、360、Bing、Google 搜索具体信息吗?我的朋友们,这正是问题所在。无论我们拥有什么可用的工具,有些技能最好还是熟记于心地学习和练习。SQL 就是其中一项技能。

当然,SQLAlchemy 或类似的 ORM 可能会保护我们不时地编写原始查询。考虑到 SQL 只是我们经常使用的众多查询语言之一(除了 NoSQL、GraphQL、JQL 等),成为 SQL 专家真的那么重要吗?简而言之,是的:关系数据库不仅会继续存在,而且将查询作为第二语言进行思考可以巩固人们对数据细节的理解。Marc Laforet最近发表了一篇 Medium 文章,其中阐述了依赖 SQL 的重要性:

更有趣的是,当这些转换脚本应用于 6.5 GB 数据集时,python 完全失败了。在 3 次尝试中,Python 崩溃了 2 次,而我的计算机在第 3 次完全死机……而 SQL 花了 226 秒。

将逻辑排除在我们的应用程序、管道以及 SQL 之外可以使执行速度呈指数级增长,同时比我们用我们选择的语言编写的任何内容都更具可读性和普遍理解性。我们可以将应用程序逻辑推送到堆栈中的位置越低越好。这就是为什么我更愿意看到数据领域充斥着 SQL 教程,而不是 Pandas 教程。

关系数据库术语

我讨厌信息材料一开始就涵盖明显的术语定义。一般情况下,我觉得这是陈词滥调,毫无帮助,而且有损作者的可信度;但这些都不是正常情况。在 SQL 中,词汇通常具有多种含义,具体取决于上下文,甚至取决于您使用的数据库类型。鉴于这一事实,个人完全有可能(并且很常见)积累关系数据库的经验,同时完全误解基本概念。让我们确保不会发生这种情况:

  • 数据库:每个数据库实例都在最高级别上分为数据库。是的,数据库是数据库的集合 - 我们已经有了一个良好的开端。

  • 模式:在 PostgreSQL(和其他数据库)中,模式是表和其他对象的分组,包括视图、关系等。模式是组织数据的一种方式。模式意味着属于它的所有数据都以某种形式相关,即使只是概念上相关。请注意,术语模式有时根据上下文用于描述其他概念。

  • 表:关系数据库的主要部分。表格由行和列组成,其中保存着我们的甜蜜数据。列最好被视为“属性”,而行是由所述属性的值组成的条目。列中的所有值必须共享相同的数据类型。

    • 主键:每行数据的标识标签。关系数据库中每条记录的主键都是不同的;必须提供值,并且它们在行之间必须是唯一的。

    • 外键:启用主数据库表和其他相关数据库之间的数据搜索和操作。

    • 键:键用于帮助我们组织和优化数据,并对传入的数据施加某些限制(例如,用户帐户的电子邮件地址必须是唯一的)。键还可以帮助我们记录条目的数量,确保自动唯一的值,并提供链接多个数据表的桥梁。

  • 对象:模式(某种程度上特定于 PostgreSQL)中存在的任何事物(包括关系)的总称。

    • 视图(PostgreSQL):视图以类似于表的方式显示数据,不同之处在于视图不存储数据。视图是以查询的形式从其他表中提取数据的快照;考虑视图的一个好方法是将它们视为“虚拟表”。

    • 函数 (PostgreSQL):与为了重用而保存的数据进行交互的逻辑。

在 MySQL 中,模式与数据库同义。这些关键字甚至可以在 MySQL 中互换使用 SCHEMA 和 DATABASE。因此,使用CREATE SCHEMA可以达到与 代替 相同的效果CREATE DATABASE。

导航和创建数据库

我们必须从某个地方开始,所以最好从数据库管理开始。诚然,这将是我们将要介绍的内容中最无用的。浏览数据库的行为最适合 GUI。

显示数据库

如果您通过命令行 shell 访问数据库(出于某种原因),第一个合乎逻辑的事情就是列出可用的数据库:

SHOW DATABASES;

+--------------------+
| Database     |
+--------------------+
| classicmodels   |
| information_schema|
| mysql       |
| performance_schema|
| sys        |
+--------------------+
5 rows in set (0.00 sec)

使用数据库

现在我们已经列出了可以连接的可能数据库,我们可以探索每个数据库包含的内容。为此,我们必须指定要连接到哪个数据库,也称为“使用”。

db> USE database_name;
Database changed

创建数据库

创建数据库很简单。创建数据库时一定要注意字符集:这将决定您的数据库能够接受哪些类型的字符。例如,如果我们尝试将特殊编码字符插入到简单的 UTF-8 数据库中,这些字符将不会按照我们的预期显示。

CREATE DATABASE IF NOT EXISTS database_name
CHARACTER SET utf-8
[COLLATE collation_name]

奖励:这是创建数据库然后显示结果的简写:

SHOW CREATE DATABASE database_name;

创建和修改表

在自动化数据导入时,通过 SQL 语法创建表非常重要。创建表时,我们还设置列名、类型和键:

CREATE TABLE [IF NOT EXISTS] table_name (
   column_name_1 [COLUMN_DATA_TYPE] [KEY_TYPE] [KEY_ATTRIBUTES] DEFAULT [DEFAULT_VALUE],
   column_name_2 [COLUMN_DATA_TYPE] [KEY_TYPE] [KEY_ATTRIBUTES] DEFAULT [DEFAULT_VALUE],
   PRIMARY KEY (column_name_1)
) ENGINE=[ENGINE_TYPE];

我们可以在创建表时指定IF NOT EXISTS是否要在查询中包含验证。如果存在,则仅当指定名称的表不存在时才会创建该表。

创建每个列时,我们可以为每个列指定许多内容:

  • 数据类型(必填):该列单元格可以保存的数据(如INTEGER、TEXT等)。

  • 键类型:为列创建键。

  • 键属性:任何与键相关的属性,例如自动递增。

  • 默认值:如果在表中创建行时没有将值传递到当前列,则该值指定为DEFAULT

  • 主键:允许将之前指定的任何列设置为表的主键。

MySQL表可以有一个通过指定的“存储引擎” ENGINE=[engine_type],它决定了表如何解释数据的核心逻辑。将此字段留空默认为 InnoDB,并且几乎可以肯定不用管它。如果您有兴趣,可以在这里(https://dev.mysql.com/doc/refman/8.0/en/storage-engines.html?ref=hackersandslackers.com)找到有关 MySQL 引擎的更多信息。

下面是实际查询的示例CREATE TABLE:

CREATE TABLE IF NOT EXISTS awards (
   id INTEGER PRIMARY KEY AUTO_INCREMENT,
   recipient TEXT NOT NULL,
   award_name TEXT DEFAULT 'Grammy',
   PRIMARY KEY (id)
) ENGINE=INNODB;

管理现有表的键

如果我们在创建表时没有指定键,我们总是可以在事后指定键。SQL 表可以接受以下键类型:

  • 主键:唯一标识表中一条记录的一个或多个字段/列。它不能接受空值、重复值。

  • 候选键:候选键类似于一组未提交的主键;这些键只接受唯一值,并且如果需要的话可以用来代替主键,但不是实际的主键。与主键不同,每个表可能存在多个候选键。

  • 备用键:指单个候选键(可以满足主键 id 需要的替代值)。

  • Composite/Compound Key:通过组合多个列的值来定义;它们的总和总会产生一个独特的价值。一张表中可以有多个候选键。每个候选键都可以作为主键。

  • 唯一键:表的一组一个或多个字段/列,唯一标识数据库表中的记录。与主键类似,但只能接受一个空值,并且不能有重复值。

  • 外键:外键表示充当另一个表的主键的字段。外键对于建立表之间的关系很有用。虽然作为主表的父表中需要外键,但在与其他表相关的表中,外键可以为 null 或为空。

让我们看一个示例查询,其中我们向表添加一个键并剖析各个部分:

ALTER TABLE table_name
ADD FOREIGN KEY foreign_key_name (column_name)
REFERENCES parent_table(columns)
ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }
ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }

ALTER TABLE用于对表结构进行任何更改,无论是修改列还是键。

在此示例中,我们的ADD键恰好是FOREIGN KEY. 虽然键始终引用列,但键本身必须有自己的名称,以区分列的数据和键的概念逻辑。我们命名我们的键foreign_key_name并指定该键将作用于哪一列(column_name)。因为这是一个外键,所以我们需要指定我们希望它与哪个表的主键关联。REFERENCES parent_table(primary_key_column)表示该表中的外键对应于名为 的表中名为primary_key_column的列中保存的值parent_table。

语句ON DELETE和ON UPDATE是分别在父表的主键被删除或更新时发生的操作。ON DELETE CASCADE如果相应的主键消失,将导致我们的表外键被删除。

添加列

添加列遵循我们创建表时使用的相同语法。一个有趣的附加功能是能够将新列放置在现有列之前或之后:

ALTER TABLE table_name
ADD COLUMN column_name [DATA_TYPE] [FIRST|AFTER existing_column];

当引用PostgreSQL数据库中的表时,我们必须指定所属的模式。因此,ALTER TABLE table_name变为ALTER TABLE schema_name.table_name. 这适用于我们引用表的任何时候,包括创建和删除表时。

突击测验

下面的语句使用了我们迄今为止所学到的有关修改和创建表结构的所有元素。你能看出这里发生了什么吗?

CREATE TABLE vendors(
    vdr_id int not null auto_increment primary key,
    vdr_name varchar(255)
)ENGINE=InnoDB;

ALTER TABLE products
ADD COLUMN vdr_id int not null AFTER cat_id;

ALTER TABLE products
ADD FOREIGN KEY fk_vendor(vdr_id)
REFERENCES vendors(vdr_id)
ON DELETE NO ACTION
ON UPDATE CASCADE;

丢弃数据

危险区域:这是我们可能开始把事情搞砸的地方。删除列或表会导致数据完全丢失:每当您看到“删除”一词时,都会感到害怕。

如果您确定知道自己在做什么并且想要删除表列,可以按如下方式完成:

ALTER TABLE tableDROP column;

删除表会破坏表结构及其中的所有数据:

DROP TABLE table_name;

另一方面,截断表将清除表中的数据,但保留表本身:

TRUNCATE TABLE table_name;

删除外键

像表和列一样,我们也可以删除键:

ALTER TABLE table_name
DROP FOREIGN KEY constraint_name;

这也可以通过删除 CONSTRAINT 来处理:

ALTER TABLE public.jira_epiccolors
DROP CONSTRAINT jira_epiccolors_pkey;

使用视图(特定于 PostgreSQL)

最后,让我们探讨一下创建视图的行为。PostgreSQL 可以处理三种类型的视图:

  • 简单视图:代表基础表数据的虚拟表。简单视图是自动可更新的:系统将允许在视图上使用 INSERT、UPDATE 和 DELETE 语句,就像在常规表上一样。

  • 物化视图:PostgreSQL 将视图概念扩展到了一个新的水平,允许视图“物理”存储数据,我们将这些视图称为物化视图。物化视图缓存复杂查询的结果,然后允许您定期刷新结果。

  • 递归视图:如果不深入研究递归报告的复杂(但很酷!)功能,递归视图有点难以解释。我不会详细介绍,但这些视图能够表示多层深入的关系。如果您好奇的话,可以快速体验一下:

示例RECURSIVE 查询:

WITH RECURSIVE reporting_line AS (
 SELECT
 employee_id,
 full_name AS subordinates
 FROM
 employees
 WHERE
 manager_id IS NULL
 UNION ALL
 SELECT
 e.employee_id,
 (
 rl.subordinates || ' > ' || e.full_name
 ) AS subordinates
 FROM
 employees e
 INNER JOIN reporting_line rl ON e.manager_id = rl.employee_id
) SELECT
 employee_id,
 subordinates
FROM
 reporting_line
ORDER BY
 employee_id;

输出:

employee_id |                         subordinates
-------------+--------------------------------------------------------------
           1 | Michael North
           2 | Michael North > Megan Berry
           3 | Michael North > Sarah Berry
           4 | Michael North > Zoe Black
           5 | Michael North > Tim James
           6 | Michael North > Megan Berry > Bella Tucker
           7 | Michael North > Megan Berry > Ryan Metcalfe
           8 | Michael North > Megan Berry > Max Mills
           9 | Michael North > Megan Berry > Benjamin Glover
          10 | Michael North > Sarah Berry > Carolyn Henderson
          11 | Michael North > Sarah Berry > Nicola Kelly
          12 | Michael North > Sarah Berry > Alexandra Climo
          13 | Michael North > Sarah Berry > Dominic King
          14 | Michael North > Zoe Black > Leonard Gray
          15 | Michael North > Zoe Black > Eric Rampling
          16 | Michael North > Megan Berry > Ryan Metcalfe > Piers Paige
          17 | Michael North > Megan Berry > Ryan Metcalfe > Ryan Henderson
          18 | Michael North > Megan Berry > Max Mills > Frank Tucker
          19 | Michael North > Megan Berry > Max Mills > Nathan Ferguson
          20 | Michael North > Megan Berry > Max Mills > Kevin Rampling
(20 rows)

创建视图

创建简单视图就像编写标准查询一样简单!所需要做的就是在查询之前添加CREATE VIEW view_name AS,这将为我们创建一个保存的位置,以便我们始终返回并引用此查询的结果:

CREATE VIEW comedies AS
    SELECT *
    FROM films    WHERE kind = 'Comedy';

走出去并开始 SQLing

我强烈鼓励任何人养成手动编写 SQL 查询的习惯。有了正确的 GUI,自动完成功能就可以成为您最好的朋友。

明确地强迫自己编写查询而不是复制和粘贴任何内容迫使我们认识到,例如 SQL 的操作顺序。事实上,这个查询拥有正确的语法......

SELECT *FROM table_nameWHERE column_name = 'Value';

...而这个则没有:

SELECT *WHERE column_name = 'Value'FROM table_name;

掌握 SQL 的微妙之处是速度极快和几乎一无所知之间的区别。好消息是,您会开始发现这些概念并不像它们曾经看起来那样令人畏惧,因此从“糟糕的数据工程师”到“专家”的道路是一个轻松的胜利,如果不这样做那就太愚蠢了拿。文章来源地址https://www.toymoban.com/diary/sql/574.html

到此这篇关于从零开始学习SQL操作:修改数据库和表的文章就介绍到这了,更多相关内容可以在右上角搜索或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

原文地址:https://www.toymoban.com/diary/sql/574.html

如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请联系站长进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用
数据库操作技巧:SELECT INTO、INSERT INTO SELECT 和 CASE 语句详解
上一篇 2023年12月09日 10:15
从零开始学习SQL数据库操作:选择、更新和删除数据
下一篇 2023年12月09日 10:43

相关文章

  • 【手写数据库】从零开始手写数据库内核,行列混合存储模型,学习大纲成型了

    ​ 专栏内容 : 参天引擎内核架构 本专栏一起来聊聊参天引擎内核架构,以及如何实现多机的数据库节点的多读多写,与传统主备,MPP的区别,技术难点的分析,数据元数据同步,多主节点的情况下对故障容灾的支持。 手写数据库toadb 本专栏主要介绍如何从零开发,开发的

    2024年02月04日
    浏览(60)
  • MySql学习2:SQL分类、数据库操作、表操作、数据的增删改查

    SQL分类: DDL:数据定义语言,用来定义数据库对象(数据库、表、字段) DML:数据操作语言,用来对数据库表中的数据进行增删改 DQL:数据库查询语言,用来查询数据库表中的记录 DCL:数据控制语言,用来创建数据库用户、控制数据库的访问权限 查询所有数据库 查询当前

    2024年02月11日
    浏览(53)
  • 从零开始:安装H2数据库的步骤解析

    在开发或编写示例时,有时需要用到数据库,如果本机上刚好没有安装,类似有些同学是临时借用的电脑或学校的电脑刚好没有安装时,我们可以使用H2数据库来快速代替,即方便又灵活。 步骤 1:下载H2数据库 访问H2数据库的官方网站(https://www.h2database.com/),并下载最新的

    2024年02月08日
    浏览(47)
  • 完全从零Java自学系列【入门篇】(第四课:Mysql服务端安装&使用客户端操作数据库&初识SQL基础操作&Java中使用第三方包&Java数据库操作&初步理解面相对象真正的意义之桥接设计模式)

      数据库是专门用来存储一系列集合数据的地方。所有的文件都可以被称之为库,当应用场景没那么复杂的时候,简单的应用程序用文本就可以了。数据库的意义是为了设计更好的保障数据安全(如多线程操作)、数据一致、索引(如何在庞大的数据中快速查找)等等一系

    2024年02月21日
    浏览(62)
  • 从零开始构建基于milvus向量数据库的文本搜索引擎

    在这篇文章中,我们将手动构建一个语义相似性搜索引擎,该引擎将单个论文作为“查询”输入,并查找Top-K的最类似论文。主要包括以下内容: 1.搭建milvus矢量数据库 2.使用MILVUS矢量数据库搭建语义相似性搜索引擎 3.从Kaggle下载ARXIV数据,使用dask将数据加载到Python中,并构

    2024年02月09日
    浏览(66)
  • openGauss学习笔记-201 openGauss 数据库运维-常见故障定位案例-执行修改表分区操作时报错

    201.1 执行修改表分区操作时报错 201.1.1 问题现象 执行ALTER TABLE PARTITION时,报错如下。 201.1.2 原因分析 在同一条ALTER TABLE PARTITION语句中,既存在DROP PARTITION又存在ADD PARTITION时,无论它们在语句中的顺序是什么,openGauss总会先执行DROP PARTITION再执行ADD PARTITION。执行完DROP PARTITIO

    2024年01月19日
    浏览(50)
  • wordpress如何修改数据库里用户ID下一个自增值的开始数字

    有时候我们为了让别人认为网站有很多注册用户,会想把网站用户ID的起始数改大一点,因为WP默认的用户ID是从1开始,注册一个就加1,这样别人就很容易知道网站的用户量。 那么如何改呢?首先进phpmyadmin,找到wp_users表,然后上面的菜单项里有个SQL,点击这个,输入sql语句

    2024年02月06日
    浏览(46)
  • SQL Server2008 修改数据库密码方法

    1、 使用Window身份验证方式登录,选择数据库实例,右键选择属性—安全性;将服务器身份验证选项从“Window身份验证模式”改为“SQLServer和Window身份验证模式”。点击确认,关闭当前对资源管理器。 2、 重新使用Windows验证方式登录,在左边的树结构中选择“数据库”下面的

    2024年02月13日
    浏览(65)
  • 利用SQL语句创建、修改、删除、查看与使用数据库

    【技术路线图】 1、连接Mysql服务:mysql -u root -p 2、创建数据库:create database teacherdb; 注意:数据库的名字一旦创建后不可更改。 3、查看刚刚创建好的数据库:show databses; 4、删除数据库并查看是否删除掉:drop database teacherdb; 5、输出创建数据库时的SQL语句信息:show create dat

    2023年04月21日
    浏览(88)
  • 利用yolov8零售商品识别实现的智能结算系统 yolo+后端flask+数据库sqlite+前端html(从零开始,全流程教学)

    全流程 教程,从数据采集到模型使用到最终展示。 支持用户点击添加至购物车、图片识别添加至购物车、摄像头识别添加至购物车,还包括用户信息,商品展示等功能。若有任何疑问和建议欢迎评论区讨论。 摄像头识别添加至购物车 图片识别添加至购物车 用户点击添加至

    2024年02月10日
    浏览(69)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包