add chapter to mysql setup

Sascha Schulz

2025-08-25 deb50954f0776638e269211a512d361951ad48d2

 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>