Nastavenie MySQL

MariaDB : Stiahnut

MySQL : Stiahnut

Docker + MySQL Github

XAMPP Stiahnut

WAMP Stiahnut

Pripojenie k MySQL

Konzola/terminal : dokumentacia

    mysql -u pouzivatel -p
 

MySQL workbench : Stiahnut

DataGrip : Stiahnut

In MySQL there are three main types : text, number, and Date/Time types.

Text types

Number types

*The integer types have an extra option called UNSIGNED. Normally, the integer goes from an negative to positive value. Adding the UNSIGNED attribute will move that range up so it starts at zero instead of a negative number.

Date types

Data type	Description
DATE()	A date. Format: YYYY-MM-DD Note: The supported range is from '1000-01-01' to '9999-12-31'
DATETIME()	*A date and time combination. Format: YYYY-MM-DD HH:MI:SS Note: The supported range is from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'
TIMESTAMP()	*A timestamp. TIMESTAMP values are stored as the number of seconds since the Unix epoch ('1970-01-01 00:00:00' UTC). Format: YYYY-MM-DD HH:MI:SS Note: The supported range is from '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC
TIME()	A time. Format: HH:MI:SS Note: The supported range is from '-838:59:59' to '838:59:59'
YEAR()	A year in two-digit or four-digit format. Note: Values allowed in four-digit format: 1901 to 2155. Values allowed in two-digit format: 70 to 69, representing years from 1970 to 2069

*Even if DATETIME and TIMESTAMP return the same format, they work very differently. In an INSERT or UPDATE query, the TIMESTAMP automatically set itself to the current date and time. TIMESTAMP also accepts various formats, like YYYYMMDDHHMISS, YYMMDDHHMISS, YYYYMMDD, or YYMMDD.

---

SQL príkazy sa delia na:

• DDS - Data Definition Statements ( ALTER, CREATE, DROP, TRUNCATE, RENAME, ... )
• DMS - Data Manipulation Statements ( DELETE, INSERT, LOAD, REPLACE, SELECT, UPDATE,... )
• Transactional and Locking Statements ( START TRANSACTION, COMMIT, ROLLBACK, ... )
• Replication Statements
• Prepared Statements
• Compound Statement Syntax ( BEGIN, END, DECLARE,...)
• DAS - Database Administration Statements ( SHOW, ... )
• Utility Statements ( USE, DESCRIBE, ...)

Viac informacii na : https://dev.mysql.com/doc/refman/8.0/en/sql-statements.html [zdroj]

---

Používateľ

Vytvorenie pouzivatela

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'user_password';
CREATE USER 'newuser'@'1.1.0.1' IDENTIFIED BY 'user_password';
CREATE USER 'newuser'@'%' IDENTIFIED BY 'user_password';
CREATE USER 'newuser'@'%' IDENTIFIED WITH mysql_native_password  BY 'user_password';
CREATE USER 'newuser'@'localhost'
  WITH MAX_QUERIES_PER_HOUR 500 MAX_UPDATES_PER_HOUR 100;
CREATE USER 'newuser'@'localhost' PASSWORD EXPIRE;
CREATE USER 'newuser'@'localhost' PASSWORD EXPIRE DEFAULT;
CREATE USER 'newuser'@'localhost' PASSWORD EXPIRE NEVER;
CREATE USER 'newuser'@'localhost' PASSWORD EXPIRE INTERVAL 180 DAY;  
     

Viac informacii: MySQL dokumentacia

Prava a povolenia pouzivatela

Najčastejšie používané povolenia :

• ALL PRIVILEGES – Všetky povolenia pre používateľa.
• CREATE – Vytváranie tabuliek a databáz.
• DROP - Odstraňovanie tabuliek a databáz.
• DELETE - Vymazanie riadkov z tabuliek.
• INSERT - Vkladanie riadkov do tabuliek.
• SELECT – Čítanie riadkov z tabuliek.
• UPDATE - Upravovanie riadkov v tabulkách.

Udelenie povolení používatelovi

GRANT permission1, permission2 ON database_name.table_name TO 'database_user'@'localhost';
     

GRANT ALL PRIVILEGES ON *.* TO 'database_user'@'localhost';
GRANT ALL PRIVILEGES ON database_name.* TO 'database_user'@'localhost';
GRANT ALL PRIVILEGES ON database_name.table_name TO 'database_user'@'localhost';
GRANT SELECT, INSERT, DELETE ON database_name.* TO database_user@'localhost';

     

Viac informacii: MySQL dokumentacia

Zistenie povolení používatela

SHOW GRANTS FOR 'database_user'@'localhost';
     

+---------------------------------------------------------------------------+
| Grants for database_user@localhost                                       |
+---------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'database_user'@'localhost'                        |
| GRANT ALL PRIVILEGES ON `database_name`.* TO 'database_user'@'localhost' |
+---------------------------------------------------------------------------+
2 rows in set (0.00 sec)
 

Odstraňovanie povolení používatela

REVOKE ALL PRIVILEGES ON database_name.* FROM 'database_user'@'localhost';
     

Odstraňovanie používatela

DROP USER 'user'@'localhost'
     

---

Databaza

Vytvorenie Databazy (DDS)

CREATE DATABASE moja_databaza;
     

Viac informacii: CREATE DATABASE

Zoznam databaz (DAS)

SHOW DATABASES;
     

Vymazanie databazy (DDS)

DROP DATABASE moja_databaza;
     

Predvolenie databazy (Utility Statements)

USE moja_databaza;
     

---

Tabulka

Vytvorenie tabulky (DDS)

CREATE TABLE users (
    user_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    registration TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
     

Viac informacii: CREATE TABLE

Zobrazenie tabuliek (DAS)

SHOW TABLES;
     

Zobrazenie struktury tabulky (Utility Statements)

DESCRIBE users;
     

Zmena tabulky (DDS)

ALTER TABLE users MODIFY name VARCHAR(100) NOT NULL;
ALTER TABLE users CHANGE name meno VARCHAR(100) NOT NULL;
ALTER TABLE users RENAME COLUMN meno TO name;
ALTER TABLE users AUTO_INCREMENT = 13;
ALTER TABLE users DROP COLUMN registration;
     

Vlozenie do tabulky (DMS)

INSERT INTO users (name) VALUES ('maros');
INSERT INTO users (user_id,name) VALUES (4,'martin');
     

Viac informacii: INSERT INTO

Zobrazenie udajov v tabulke (DMS)

SELECT * FROM users;
     

Uprava udajov v tabulke (DMS)

UPDATE users
SET name = 'martin'
WHERE user_id = 1;
     

Viac informacii: UPDATE

Vymazanie udajov v tabulke (DMS)

DELETE FROM users
WHERE user_id = 1;
     

Viac informacii: DELETE

Vymazanie tabulky (DDS)

DROP TABLE users;
     

Resetovanie tabulky (DDS)

TRUNCATE TABLE users;
     

Premenovanie tabulky (DDS)

RENAME TABLE users TO pouzivatelia;
     

Primarne a cudzie kluce

Viac informacii: PRIMARY KEY

Viac informacii: FOREIGN KEY

---

Zdroj a viac informacii: http://www.w3schools.com/sql/