mysql

Mysql server

    
        --connect to MySQL
        mysql -u nameUser;

        --with password
        mysql -u nameUser -p;
    

Data base

    
        --list databases
        SHOW DATABASES;

        -- create database
        CREATE DATABASE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

        -- choose database
        USE db_name;
    

Table

    
        -- list table
        SHOW TABLES;

        -- show table
        DESCRIBE NAME_TABLE;

        -- create 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
        );

        -- delete table
        DROP TABLE table_name;

    

Users

    
        -- list users
        select user, host, password from mysql.user;

        --create new user
        CREATE USER 'newuser' @ 'localhost' IDENTIFIED BY 'password';

        --grant permission for user on all database
        GRANT ALL PRIVILEGES ON * . * TO 'newuser' @ 'localhost';

        --permission only for certain database
        GRANT ALL PRIVILEGES ON nameBase. * TO 'newuser' @ 'localhost';

        --update permission
        FLUSH PRIVILEGES;
    

Backup, restore

    
        --backup db
        mysqldump -u root -p --default-character-set=utf8 db_name > /path_/db_name.sql

        --backup db from remote server
        ssh root@domain.ru "mysqldump -u root dbname| gzip" | gunzip > /root/dbname.sql

        --Restore db
        mysql -u root -p db_name < db_name.sql

        --backup all db
        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

    
        --change title in row with id 1
        UPDATE table_name SET title = ‘Text for row' WHERE id = '1';
    

DELETE

    
        --check before delete
        SELECT * from `b_event` where `EVENT_NAME` = 'COUPON_AFTER_SUCCESSFUL_ORDER'
        --delete
        delete from `b_event` where `EVENT_NAME` = 'COUPON_AFTER_SUCCESSFUL_ORDER'
    

error Row size too large

    
        --In file /etc/my.cnf add in section [mysqld]
        default_storage_engine=MyISAM
        innodb_strict_mode = 0