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
    
Image Description
Nov 17, 2020

Flex

Image Description
Nov 17, 2020

Linux

Subscribe

Stay in the know

Get special offers on the latest developments from Front.

Be the lucky user to earn 100$ bonus now!

Be the lucky user to earn 100$ bonus now!

Image Description
Image Description
Image Description