Podstawy MySQL

Opublikowano 5 kwietnia 2017, autor: micek

W tej części zajmiemy się podstawowymi poleceniami w bazie MySQL. Poniższe polecenia testowałem na bazie MariaDB więc może się czasami różnić trochę składnią ale większość jest identyczna jak w bazie MySQL.

Na początek podłączamy się do bazy:

[root@localhost bin]# mysql -u root -p

Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.1.22-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

Sprawdźmy jakie bazy znajdują się naszej instancji:

MariaDB [(none)]> show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.01 sec)

Stwórzmy swoją bazę na której będziemy mogli przeprowadzać testy

MariaDB [(none)]> create database testowadb1;

Query OK, 1 row affected (0.00 sec)

Sprawdzamy czy się udało

MariaDB [(none)]> show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| testowadb1         |
+--------------------+
5 rows in set (0.00 sec)

Aby się podłączyć (i wykonywać kolejne polecenia) wydajemy polecenie:

MariaDB [(none)]> use testowadb1;

Database changed

Stworzymy tabelę z czterema kolumnami (id, imię , nazwisko i email):

MariaDB [testowa]> create table testtable1 (id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, firstname VARCHAR(30) NOT NULL, lastname VARCHAR(30) NOT NULL, email VARCHAR(50));

Query OK, 0 rows affected (0.03 sec)

MariaDB [testowa]> desc testtable1;

+-----------+-----------------+------+-----+---------+----------------+
| Field     | Type            | Null | Key | Default | Extra          |
+-----------+-----------------+------+-----+---------+----------------+
| id        | int(6) unsigned | NO   | PRI | NULL    | auto_increment |
| firstname | varchar(30)     | NO   |     | NULL    |                |
| lastname  | varchar(30)     | NO   |     | NULL    |                |
| email     | varchar(50)     | YES  |     | NULL    |                |
+-----------+-----------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

Wprowadźmy do niej jeden wiersz

MariaDB [testowa]> INSERT INTO testtable1 (firstname, lastname, email) VALUES('Jan', 'Kowalski', 'jan.kowalski@mail.pl');

Query OK, 1 row affected (0.01 sec)

I kolejny

MariaDB [testowadb1]> INSERT INTO testtable1 (firstname, lastname, email) VALUES('Anna', 'Nowak', 'anna.nowak@mail.pl');

Query OK, 1 row affected (0.00 sec)

MariaDB [testowadb1]> select * from testtable1;

+----+-----------+----------+----------------------+
| id | firstname | lastname | email                |
+----+-----------+----------+----------------------+
|  1 | Jan       | Kowalski | jan.kowalski@mail.pl |
|  2 | Anna      | Nowak    | anna.nowak@mail.pl   |
+----+-----------+----------+----------------------+
2 rows in set (0.00 sec)

Wartości ID nie musimy wprowadzać bo podaliśmy że ma się tworzyć kolejno do wprowadzanych wierszy.
Jeśli teraz usuniemy jeden wiersz (np id=2) kolejny otrzyma kolejny numer

MariaDB [testowadb1]> delete from testtable1 where id=2;

Query OK, 1 row affected (0.00 sec)

MariaDB [testowadb1]> select * from testtable1;

+----+-----------+----------+----------------------+
| id | firstname | lastname | email                |
+----+-----------+----------+----------------------+
|  1 | Jan       | Kowalski | jan.kowalski@mail.pl |
+----+-----------+----------+----------------------+
1 row in set (0.00 sec)

Wprowadzimy kolejne kilka wierszy
INSERT INTO testtable1 (firstname, lastname, email) VALUES('Anna', 'Nowak', 'anna.nowak@mail.pl');
INSERT INTO testtable1 (firstname, lastname, email) VALUES('Katarzyna', 'Polska', 'katarzyna.polska@mail.pl');
INSERT INTO testtable1 (firstname, lastname, email) VALUES('Robert', 'Barski', 'robert.barski@mail.pl');
INSERT INTO testtable1 (firstname, lastname, email) VALUES('Andrzej', 'Dulski', 'andrzej.dulski@mail.pl');
INSERT INTO testtable1 (firstname, lastname, email) VALUES('Agnieszka', 'Frankowska', 'agnieszka.frankowska@mail.pl');
INSERT INTO testtable1 (firstname, lastname, email) VALUES('Hubert', 'Goglewski', 'hubert.goglewski@mail.pl');

Sprawdzamy czy dane zostały wprowadzone do naszej tabeli:
MariaDB [testowadb1]> select * from testtable1;

+----+-----------+------------+------------------------------+
| id | firstname | lastname   | email                        |
+----+-----------+------------+------------------------------+
|  1 | Jan       | Kowalski   | jan.kowalski@mail.pl         |
|  3 | Anna      | Nowak      | anna.nowak@mail.pl           |
|  4 | Katarzyna | Polska     | katarzyna.polska@mail.pl     |
|  5 | Robert    | Barski     | robert.barski@mail.pl        |
|  6 | Andrzej   | Dulski     | andrzej.dulski@mail.pl       |
|  7 | Agnieszka | Frankowska | agnieszka.frankowska@mail.pl |
|  8 | Hubert    | Goglewski  | hubert.goglewski@mail.pl     |
+----+-----------+------------+------------------------------+
7 rows in set (0.00 sec)

Jak widać naszego usuniętego wiersza 2 brakuje.

Aby usunąć naszą tabelę wykonujemy

MariaDB [testowa]> drop table testtable1;

natomiast aby usunąć całą bazę wykonujemy

MariaDB [testowa]> drop database testowadb1;

Aby odłączyć się od bazy wydajemy polecenie:

MariaDB [(none)]> exit

Bye

Na koniec zajmiemy się trochę plikami bazy MySQL. najprostszym sposobem aby sprawdzić gdzie znajdują się pliki bazodanowe bazy jest wykonanie polecenia:

MariaDB [information_schema]> select @@datadir;

+-----------------+
| @@datadir       |
+-----------------+
| /var/lib/mysql/ |
+-----------------+
1 row in set (0.00 sec)

W tym folderze znajdują się m.in foldery posiadająe nazwy tak jak nasze bazy (czyli np TESTOWADB1)

[root@localhost mysql]# ls

aria_log.00000001      ibdata1      ib_logfile1      multi-master.info      mysql.sock
tc.log      testowadb1      aria_log_control      ib_logfile0      localhost.pid  
mysql       performance_schema      test

W folderze tym znajdują się pliki OPT i FRM odpowiedzialne za bazy
[root@localhost testowadb1]# ls -la

total 108
drwx------. 2 mysql mysql    61 Apr  4 20:32 .
drwxr-xr-x. 6 mysql mysql  4096 Apr  6 09:06 ..
-rw-rw----. 1 mysql mysql    65 Apr  4 20:31 db.opt
-rw-rw----. 1 mysql mysql  1111 Apr  4 20:32 testtable1.frm
-rw-rw----. 1 mysql mysql 98304 Apr  4 20:35 testtable1.ibd

I na koniec mała podpowiedź jakbyśmy chcieli zrestartować bazę MySQL

[root@localhost testowadb1]# systemctl stop mariadb

[root@localhost testowadb1]# systemctl status mariadb

● mariadb.service - MariaDB database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
  Drop-In: /etc/systemd/system/mariadb.service.d
           └─migrated-from-my.cnf-settings.conf
   Active: inactive (dead) since Thu 2017-04-06 09:58:25 CEST; 8s ago
  Process: 1849 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
  Process: 900 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION (code=exited, status=0/SUCCESS)
  Process: 857 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ]   && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS)
  Process: 846 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
 Main PID: 900 (code=exited, status=0/SUCCESS)
   Status: "MariaDB server is down"

Apr 06 09:06:51 localhost.localdomain systemd[1]: Started MariaDB database server.
Apr 06 09:58:23 localhost.localdomain systemd[1]: Stopping MariaDB database server...
Apr 06 09:58:23 localhost.localdomain mysqld[900]: 2017-04-06  9:58:23 139838323800832 [Note] /usr/sbin/mysqld: Normal shutdown
Apr 06 09:58:23 localhost.localdomain mysqld[900]: 2017-04-06  9:58:23 139838323800832 [Note] Event Scheduler: Purging the queue. 0 events
Apr 06 09:58:23 localhost.localdomain mysqld[900]: 2017-04-06  9:58:23 139837583615744 [Note] InnoDB: FTS optimize thread exiting.
Apr 06 09:58:23 localhost.localdomain mysqld[900]: 2017-04-06  9:58:23 139838323800832 [Note] InnoDB: Starting shutdown...
Apr 06 09:58:24 localhost.localdomain mysqld[900]: 2017-04-06  9:58:24 139838323800832 [Note] InnoDB: Waiting for page_cleaner to finish flu...er pool
Apr 06 09:58:25 localhost.localdomain mysqld[900]: 2017-04-06  9:58:25 139838323800832 [Note] InnoDB: Shutdown completed; log sequence number 1640322
Apr 06 09:58:25 localhost.localdomain mysqld[900]: 2017-04-06  9:58:25 139838323800832 [Note] /usr/sbin/mysqld: Shutdown complete
Apr 06 09:58:25 localhost.localdomain systemd[1]: Stopped MariaDB database server.
Hint: Some lines were ellipsized, use -l to show in full.

[root@localhost testowadb1]# systemctl start mariadb
[root@localhost testowadb1]# systemctl status mariadb

● mariadb.service - MariaDB database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
  Drop-In: /etc/systemd/system/mariadb.service.d
           └─migrated-from-my.cnf-settings.conf
   Active: active (running) since Thu 2017-04-06 09:59:12 CEST; 2s ago
  Process: 2250 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
  Process: 2210 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ]   && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS)
  Process: 2208 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
 Main PID: 2222 (mysqld)
   Status: "Taking your SQL requests now..."
   CGroup: /system.slice/mariadb.service
           └─2222 /usr/sbin/mysqld

Apr 06 09:59:11 localhost.localdomain mysqld[2222]: 2017-04-06  9:59:11 140128322922752 [Note] InnoDB: Highest supported file format is Barracuda.
Apr 06 09:59:11 localhost.localdomain mysqld[2222]: 2017-04-06  9:59:11 140128322922752 [Note] InnoDB: 128 rollback segment(s) are active.
Apr 06 09:59:11 localhost.localdomain mysqld[2222]: 2017-04-06  9:59:11 140128322922752 [Note] InnoDB: Waiting for purge to start
Apr 06 09:59:11 localhost.localdomain mysqld[2222]: 2017-04-06  9:59:11 140128322922752 [Note] InnoDB:  Percona XtraDB (http://www.percona.c...1640322
Apr 06 09:59:11 localhost.localdomain mysqld[2222]: 2017-04-06  9:59:11 140128322922752 [Note] Plugin 'FEEDBACK' is disabled.
Apr 06 09:59:11 localhost.localdomain mysqld[2222]: 2017-04-06  9:59:11 140127598774016 [Note] InnoDB: Dumping buffer pool(s) not yet started
Apr 06 09:59:12 localhost.localdomain mysqld[2222]: 2017-04-06  9:59:12 140128322922752 [Note] Server socket created on IP: '::'.
Apr 06 09:59:12 localhost.localdomain mysqld[2222]: 2017-04-06  9:59:12 140128322922752 [Note] /usr/sbin/mysqld: ready for connections.
Apr 06 09:59:12 localhost.localdomain mysqld[2222]: Version: '10.1.22-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server
Apr 06 09:59:12 localhost.localdomain systemd[1]: Started MariaDB database server.
Hint: Some lines were ellipsized, use -l to show in full.

Ten wpis został opublikowany w kategorii MySQL (MariaDB) i oznaczony tagami . Dodaj zakładkę do bezpośredniego odnośnika.

Dodaj komentarz