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 | 179 +++++++++++++++++++++++++++++++++++++++++++++++------------
1 files changed, 143 insertions(+), 36 deletions(-)
diff --git a/index.html b/index.html
index 7cf03bd..93dc23f 100644
--- a/index.html
+++ b/index.html
@@ -5072,7 +5072,7 @@
<p>Bearbeiten > Einstellungen > Protocols > TLS > (Pre)-Master-Secret log filename</p>
</section>
<section>
- Einrichtung eines Datenbank-Servers (Beispiel MongoDB)
+ Einrichtung eines Datenbank-Servers (Beispiel MySql)
</section>
<section>
<p>Datenbank installieren:</p>
@@ -5081,50 +5081,157 @@
# Paketquellen aktualisieren
sudo apt update
- # Tools installieren
- sudo apt install gnupg curl
+ # MySql installieren
+ sudo apt install mysql-server
- # GPG-Signatur herunterladen und importieren
- curl -fsSL https://www.mongodb.org/static/pgp/server-8.0.asc | \
- sudo gpg -o /usr/share/keyrings/mongodb-server-8.0.gpg \
- --dearmor
- </code>
- </pre>
- </section>
- <section>
- <pre>
- <code class="bash" data-trim data-line-numbers>
- # URL den Paketquellen hinzufügen
- echo "deb [ arch=amd64,arm64 signed-by=/usr/share/keyrings/mongodb-server-8.0.gpg ] https://repo.mongodb.org/apt/ubuntu noble/mongodb-org/8.0 multiverse" | \
- sudo tee /etc/apt/sources.list.d/mongodb-org-8.0.list
-
- # Paketquellen aktualisieren
- sudo apt update
-
- # MongoDB installieren
- sudo apt install mongodb-org
- </code>
- </pre>
- </section>
- <section>
- <pre>
- <code class="bash" data-trim data-line-numbers>
# systemd neuladen
sudo systemctl daemon-reload
-
- # Autostart aktivieren
- sudo systemctl enable mongod
-
- # Dienst starten
- sudo systemctl start mongod.service
</code>
</pre>
</section>
<section>
- <p>Der Server sollte nun erreichbar sein:</p>
+ <p>Der Server sollte nun laufen:</p>
<pre>
<code class="bash" data-trim data-line-numbers>
- mongosh
+ 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>
--
Gitblit v1.9.3