iBGAP

                      Must be something I forgot
                      Down on Bullshit Avenue{*}
                      ..

                      {*} Должно быть, я что-то забыл.
                          Спускаясь по Проспекту Несусветной Чуши..

                      George Harrison, Brainwashed, 2002

## ПРО SQL:
-----------

Матрешки

  1 MySQL
  2   База данных
  3     Таблица
  4       Столбец
  5         Тип
  6           VARCHAR(max_length)
  7           INTEGER
  8           DOUBLE (8 bytes)
  9        Строка

1. mysql -u root
   connect

## SQL -- BIG PICTURE{*}

  SQL: DDL (data definition language)
    CREATE
    ALTER
    DROP
  SQL: DML (data manipulation language)
    SELECT
    INSERT
    UPDATE
    DELETE
  SQL: DCL (data control language)
    GRANT
    REVOKE
  SQL: TCL (transaction control language)
    BEGIN
    COMMIT
    ROLLBACK

  {*}. https://www.geeksforgeeks.org/sql-ddl-dml-dcl-tcl-commands/

Пример.

  # cat /var/www/html/qtpt.php

  <?php
  $servername = "localhost";
  $username   = "php";
  $password   = "php";
  $dbname     = "students";

  header('Content-Type: text/html; charset=utf-8');

  // Create connection

  $conn = mysqli_connect($servername, $username, $password, $dbname);

  // Check connection

  if (!$conn) {
      die("Connection failed: " . mysqli_connect_error());
  }

  // encoding

  if (!mysqli_set_charset($conn, "utf8")) {
      printf("Error loading character set utf8: %s\n", mysqli_error($conn));
      exit();
  } else {
      printf("Current character set: %s\n", mysqli_character_set_name($conn));
  }

  // Query

  $sql = "SELECT id, name, surname FROM people";
  $result = mysqli_query($conn, $sql);

  if (mysqli_num_rows($result) > 0) {
      while($row = mysqli_fetch_assoc($result)) {
          echo "id: " . $row["id"] . " - Name: " . $row["name"] . " " . $row["surname"] . "<br>";
      }
  } else {
      echo "0 results";
  }

  // Close Connection

  mysqli_close($conn);
  ?>

1. mysql не всегда любит экспонировать рута в PHP скриптах:

  CREATE USER 'php'@'localhost' IDENTIFIED BY 'php';
  GRANT ALL PRIVILEGES ON students.* TO 'php'@'localhost' IDENTIFIED BY 'php';
  FLUSH PRIVILEGES;

2. Кодировка utf8:

  mysqli_set_charset($link, "utf8"))

students.sql

  CREATE DATABASE IF NOT EXISTS students;
  USE students;

  DROP TABLE IF EXISTS people;
  CREATE TABLE people (
    id INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,
    base varchar(1) NOT NULL,
    class varchar(20) NOT NULL,
    name varchar(50) NOT NULL,
    surname varchar(50) NOT NULL
  );

  INSERT INTO people ( base, class, name, surname) VALUES
  ("P", "AA-00", "FIRST", "ONE"),
  ("P", "AA-00", "SECOND", "TWO");

Другие операции:

  ALTER TABLE students ADD date timestamp;

  DROP TABLE students;

  UPDATE people SET name = "Йан" WHERE id=1;
  DELETE FROM people;

## Using DESCRIBE

The SQL query to get the table structure is:

  DESCRIBE students;

## Links:

  1. https://www.electrictoolbox.com/mysql-table-structure-describe/
