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 | 117 ++++++++++++++++++++++++++++++++++++++++++++++++++++------
1 files changed, 105 insertions(+), 12 deletions(-)
diff --git a/index.html b/index.html
index 0ef65ae..93dc23f 100644
--- a/index.html
+++ b/index.html
@@ -5086,9 +5086,6 @@
# systemd neuladen
sudo systemctl daemon-reload
-
- # Status prüfen
- sudo systemctl status mysql.service
</code>
</pre>
</section>
@@ -5110,7 +5107,7 @@
</pre>
<pre>
<code class="sql" data-trim data-line-numbers>
- ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'mypass';
+ ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';
exit;
</code>
</pre>
@@ -5130,15 +5127,111 @@
<section>
<pre>
<code class="sql" data-trim data-line-numbers>
- CREATE DATABASE schema_name;
+ CREATE DATABASE erp;
- use schema_name;
-
- create table stores (id int not null primary key, number varchar(5) not null);
-
- insert into stores (id, number) values (1, "10001");
-
- select * from stores;
+ 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