Работа с базой данных MySQL

Mysql server

    
        --Соединение с MySQL
        mysql -u nameUser;

        --с паролем
        mysql -u nameUser -p;
    

База данных

    
        --список баз данных
        SHOW DATABASES;

        -- создание базы данных
        CREATE DATABASE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

        -- выбор базы данных
        USE db_name;
    

Таблицы

    
        -- cписок таблиц
        SHOW TABLES;

        -- просмотр таблицы
        DESCRIBE NAME_TABLE;

        -- создание таблицы
        CREATE TABLE table_name2 (
        id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        title VARCHAR(255) NOT NULL,
        slug VARCHAR(255) NOT NULL UNIQUE,
        publish BOOLEAN NOT NULL DEFAULT TRUE,
        created TIMESTAMP
        );

        -- удаление таблицы
        DROP TABLE table_name;

    

Типы данных

Character Data

Type Description Max bytes
char(20) fixed-length 255
varchar(20) variable-length 65,535

Text data

Type Max bytes
tinytext 255
text 65,535
mediumtext 16,777,215
longtext 4,294,967,295

Numeric Data

Type Signed range Unsigned range
tinyint −128 to 127 0 to 255
smallint −32,768 to 32,767 0 to 65,535
mediumint −8,388,608 to 8,388,607 0 to 16,777,215
int −2,147,483,648 to 2,147,483,647 0 to 4,294,967,295
int −2^63 to 2^63 - 1 0 to 2^64 - 1
float( p , s ) −3.402823466E+38 to −1.175494351E-38 1.175494351E-38 to 3.402823466E+38
double( p , s ) −1.7976931348623157E+308 to −2.2250738585072014E-308 2.2250738585072014E-308 to 1.7976931348623157E+308

Temporal Data

Type Default format Allowable values
date YYYY-MM-DD 1000-01-01 to 9999-12-31
datetime YYYY-MM-DD HH:MI:SS 1000-01-01 00:00:00.000000 to 9999-12-31 23:59:59.999999
timestamp YYYY-MM-DD HH:MI:SS 1970-01-01 00:00:00.000000 to 2038-01-18 22:14:07.999999
year YYYY 1901 to 2155
time HHH:MI:SS −838:59:59.000000 to 838:59:59.000000

Пользователи

    
        -- список пользователей
        select user, host, password from mysql.user;

        --создать нового пользователя
        CREATE USER 'newuser' @ 'localhost' IDENTIFIED BY 'password';

        --выдать пользователю права на все базы
        GRANT ALL PRIVILEGES ON * . * TO 'newuser' @ 'localhost';

        --выдать пользователю права на базу
        GRANT ALL PRIVILEGES ON nameBase. * TO 'newuser' @ 'localhost';

        --Обновить права доступа
        FLUSH PRIVILEGES;
    

Резервное копирование, восстановление

    
        --бэкап бд
        mysqldump -u root -p --default-character-set=utf8 db_name > /path_/db_name.sql

        --бэкап бд с удаленного сервера
        ssh root@domain.ru "mysqldump -u root dbname| gzip" | gunzip > /root/dbname.sql

        --Востановление бд
        mysql -u root -p db_name < db_name.sql

        --бэкап всех бд
        mysqldump -u root -p --default-character-set=utf8 --all-databases > alldb.sql
    

SELECT

    
        --Basics
        USE sql_store;
        SELECT *
        FROM customers
        WHERE state = ‘CA’
        ORDER BY first_name
        LIMIT 3; 

        -- Using expressions
        SELECT (points * 10 + 20) AS discount_factor
        FROM customers

        -- Removing duplicates
        SELECT DISTINCT state
        FROM customers

        -- AND (both conditions must be True)
        SELECT *
        FROM customers
        WHERE birthdate > ‘1990-01-01’ AND points > 1000

        -- OR (at least one condition must be True)
        SELECT *
        FROM customers
        WHERE birthdate > ‘1990-01-01’ OR points > 1000

        -- NOT (to negate a condition)
        SELECT *
        FROM customers
        WHERE NOT (birthdate > ‘1990-01-01’)

        --IN Operator
        -- Returns customers in any of these states: VA, NY, CA
        SELECT *
        FROM customers
        WHERE state IN (‘VA’, ‘NY’, ‘CA’)

        --BETWEEN Operator
        SELECT *
        FROM customers
        WHERE points BETWEEN 100 AND 200

        --LIKE Operator
        -- Returns customers whose first name starts with b
        SELECT *
        FROM customers
        WHERE first_name LIKE ‘b%’
        --• %: any number of characters
        --• _: exactly one character

        --REGEXP Operator
        -- Returns customers whose first name starts with a
        SELECT *
        FROM customers
        WHERE first_name REGEXP ‘^a’
        --• ^: beginning of a string
        --• $: end of a string
        --• |: logical OR
        --• [abc]: match any single characters
        --• [a-d]: any characters from a to d 

        -- Returns customers whose first name ends with EY or ON
        WHERE first_name REGEXP ‘ey$|on$’
        -- Returns customers whose first name starts with MY
        -- or contains SE
        WHERE first_name REGEXP ‘^my|se’
        -- Returns customers whose first name contains B followed by
        -- R or U
        WHERE first_name REGEXP ‘b[ru]’

        --IS NULL Operator
        -- Returns customers who don’t have a phone number
        SELECT *
        FROM customers
        WHERE phone IS NULL

        --ORDER BY Clause
        -- Sort customers by state (in ascending order), and then
        -- by their first name (in descending order)
        SELECT *
        FROM customers
        ORDER BY state, first_name DESC

        --LIMIT Clause
        -- Return only 3 customers
        SELECT *
        FROM customers
        LIMIT 3

        -- Skip 6 customers and return 3
        SELECT *
        FROM customers
        LIMIT 6, 3

        --Inner Joins
        SELECT *
        FROM customers c
        JOIN orders o
        ON c.customer_id = o.customer_id

        --Outer Joins
        -- Return all customers whether they have any orders or not
        SELECT *
        FROM customers c
        LEFT JOIN orders o
        ON c.customer_id = o.customer_id

        --USING Clause
        --If column names are exactly the same, you can simplify the join with the USING clause.
        SELECT *
        FROM customers c
        JOIN orders o
        USING (customer_id)

        --Cross Joins
        -- Combine every color with every size
        SELECT *
        FROM colors
        CROSS JOIN sizes

        --Unions
        -- Combine records from multiple result sets
        SELECT name, address
        FROM customers
        UNION
        SELECT name, address
        FROM clients 
    

INNER

    
        -- Insert a single record
        INSERT INTO customers(first_name, phone, points)
        VALUES (‘Mosh’, NULL, DEFAULT)

        -- Insert multiple single records
        INSERT INTO customers(first_name, phone, points)
        VALUES
        (‘Mosh’, NULL, DEFAULT),
        (‘Bob’, ‘1234’, 10)

    

UPDATE

    
        --Изменить значение title в записи с id 1
        UPDATE table_name SET title = ‘Вставка изменена id 1' WHERE id = '1';
    

DELETE

    
        --проверка перед удалением
        SELECT * from `b_event` where `EVENT_NAME` = 'COUPON_AFTER_SUCCESSFUL_ORDER'
        --удаление
        delete from `b_event` where `EVENT_NAME` = 'COUPON_AFTER_SUCCESSFUL_ORDER'
    

Внешний ключ

    
        --возможные варианты RESTRICT, CASCADE, SET NULL, NO ACTION
        ALTER TABLE tablename ADD FOREIGN KEY (post_id) REFERENCES r_tablename(id) ON UPDATE CASCADE ON DELETE CASCADE 
    

ошибка Row size too large

    
        --В файле /etc/my.cnf добавить в секцию [mysqld]
        default_storage_engine=MyISAM
        innodb_strict_mode = 0
    
Подписаться

Будьте в курсе

Получите специальное предложение от нас.

Подписывайтесь и получите скидку 100$ на анализ сайта!

Подписывайтесь и получите скидку 100$ на анализ сайта!

Image Description
Image Description
Image Description