add chapter to mysql setup

Sascha Schulz

2025-08-25 deb50954f0776638e269211a512d361951ad48d2

add chapter to mysql setup

 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;
                                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");
                                
                                create table stores (id int not null primary key, number varchar(5) not null);
var connection = mysql.createConnection({
        host: 'localhost', user: 'root', password: 'root', database: 'erp'
});
                                
                                insert into stores (id, number) values (1, "10001");
connection.connect();
                                
                                select * from stores;
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>