From deb50954f0776638e269211a512d361951ad48d2 Mon Sep 17 00:00:00 2001
From: Sascha Schulz <sschulz@dh-software.de>
Date: Mo, 25 Aug 2025 15:39:10 +0200
Subject: [PATCH] add chapter to mysql setup
---
index.html | 164 ++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 164 insertions(+), 0 deletions(-)
diff --git a/index.html b/index.html
index 74b9993..93dc23f 100644
--- a/index.html
+++ b/index.html
@@ -5071,6 +5071,170 @@
<p>Eben angegebene Datei in Wireshark einstellen:</p>
<p>Bearbeiten > Einstellungen > Protocols > TLS > (Pre)-Master-Secret log filename</p>
</section>
+ <section>
+ Einrichtung eines Datenbank-Servers (Beispiel MySql)
+ </section>
+ <section>
+ <p>Datenbank installieren:</p>
+ <pre>
+ <code class="bash" data-trim data-line-numbers>
+ # Paketquellen aktualisieren
+ sudo apt update
+
+ # MySql installieren
+ sudo apt install mysql-server
+
+ # systemd neuladen
+ sudo systemctl daemon-reload
+ </code>
+ </pre>
+ </section>
+ <section>
+ <p>Der Server sollte nun laufen:</p>
+ <pre>
+ <code class="bash" data-trim data-line-numbers>
+ sudo systemctl status mysql.service
+ </code>
+ </pre>
+ </section>
+ <section>
+ <p>Root-Passwort setzen/ändern:</p>
+ <pre>
+ <code class="bash" data-trim data-line-numbers>
+ # Login mit generischen Credentials
+ sudo mysql --defaults-file=/etc/mysql/debian.cnf
+ </code>
+ </pre>
+ <pre>
+ <code class="sql" data-trim data-line-numbers>
+ ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';
+ exit;
+ </code>
+ </pre>
+ <pre>
+ <code class="bash" data-trim data-line-numbers>
+ # Server neustarten
+ sudo systemctl restart mysql.service
+ </code>
+ </pre>
+ <pre>
+ <code class="bash" data-trim data-line-numbers>
+ # Login mit neuen Credentials
+ mysql -uroot -p
+ </code>
+ </pre>
+ </section>
+ <section>
+ <pre>
+ <code class="sql" data-trim data-line-numbers>
+ CREATE DATABASE erp;
+
+ use erp;
+ </code>
+ </pre>
+ </section>
+ <section>
+ <pre>
+ <code class="sql" data-trim data-line-numbers>
+ create table stores
+ (
+ id int not null auto_increment primary key,
+ number varchar(5) not null
+ );
+ </code>
+ </pre>
+ </section>
+ <section>
+ <pre>
+ <code class="sql" data-trim data-line-numbers>
+ create table revenues
+ (
+ id int not null auto_increment primary key,
+ store int not null,
+ revenue int not null,
+ foreign key (store) references stores (id)
+ );
+ </code>
+ </pre>
+ </section>
+ <section>
+ <pre>
+ <code class="sql" data-trim data-line-numbers>
+insert into revenues (store, revenue)
+values (1, 10000),
+ (1, 20000),
+ (1, 15000),
+ (2, 13000);
+ </code>
+ </pre>
+ </section>
+ <section>
+ <pre>
+ <code class="sql" data-trim data-line-numbers>
+select stores.number, sum(revenues.revenue) as revenue
+from stores
+ inner join revenues on revenues.store = stores.id
+group by stores.id;
+ </code>
+ </pre>
+ </section>
+ <section>
+ <p>Zugriff in NodeJS</p>
+ <pre>
+ <code class="js" data-trim data-line-numbers>
+const mysql = require("mysql");
+
+var connection = mysql.createConnection({
+ host: 'localhost', user: 'root', password: 'root', database: 'erp'
+});
+
+connection.connect();
+
+connection.query('select * from stores;', function (error, results, fields) {
+ if (error) throw error;
+
+ console.log(results);
+});
+
+connection.end();
+ </code>
+ </pre>
+ </section>
+ <section>
+ <p>Aufgabe</p>
+ <p>Implementiere das <code>GROUP BY</code>-Statement aus dem SQL-Beispiel im NodeJS-Beispiel und gebe pro Zeile die Filiale mit dem Umsatz aus</p>
+ </section>
+ <section>
+ <p>Lösung</p>
+ <pre>
+ <code class="js" data-trim data-line-numbers>
+const mysql = require("mysql");
+
+var connection = mysql.createConnection({
+ host: 'localhost', user: 'root', password : 'root', database : 'erp'
+});
+
+connection.connect();
+
+const query = `
+ select stores.number, sum(revenues.revenue) as revenue
+ from stores
+ inner join revenues on revenues.store = stores.id
+ group by stores.id;
+`;
+
+connection.query(query, function (error, results, fields) {
+ if (error) throw error;
+
+ for (const result of results) {
+ console.log(result.number + ": " + result.revenue);
+ }
+});
+
+connection.end();
+ </code>
+ </pre>
+ </section>
</section>
</div>
</div>
--
Gitblit v1.9.3