MySQL queries

Posted on 19-Jun-2018

Create table

CREATE TABLE table_name
(
    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    column_1 VARCHAR(255) NOT NULL DEFAULT '',
    column_2 CHAR(32) NOT NULL DEFAULT '',
    column_3 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

Create database

CREATE DATABASE my_database;

Create user

CREATE USER 'db_username'@'db_hostname' IDENTIFIED BY 'clear_password';

Grant user privileges

GRANT ALL PRIVILEGES ON `database_name`.`table_name` TO 'db_username'@'db_hostname' WITH GRANT OPTION;
# OR
GRANT ALL PRIVILEGES ON *.* TO 'db_username'@'db_hostname' WITH GRANT OPTION;

Make changes available

FLUSH PRIVILEGES;

Drop table

DROP TABLE table_name;

Truncate table

TRUNCATE TABLE table_name;

Insert into statement

INSERT INTO table_name
SET column_1 = 'value_1',
column_2 = 'value_2'

# or
INSERT INTO table_name (column_1, column_2)
VALUES ('value_1', 'value_2')

Insert into select statement

INSERT INTO table_name_1
(column_1, column_2)
SELECT column_3, column_4
FROM table_name_2

Update statement

UPDATE table_name
SET column_1 = 'value_1',
column_2 = 'value_2'
WHERE (condition)

Delete from statement

DELETE FROM table_name
WHERE (condition)

Select statement

SELECT column_name 
FROM table_name

Distinct

SELECT DISTINCT column_name 
FROM table_name

Where

SELECT DISTINCT column_name 
FROM table_name 
WHERE column = 'value'

In

SELECT column_name 
FROM table_name 
WHERE column IN ('value_1', 'value_2')

Between

SELECT column_name
FROM table_name
WHERE column_name BETWEEN 'value_1' AND 'value_2'

Like

SELECT column_name
FROM table_name
WHERE column_name LIKE 'word%'

Order By

SELECT column_name
FROM table_name
[WHERE condition]
ORDER BY column_name [ASC, DESC]

Count

SELECT COUNT(column_name)
FROM table_name

# or

SELET COUNT(*)
FROM table_name

Group By

SELECT column_name_1, SUM(column_name_2)
FROM table_name
GROUP BY column_name_1

Having

SELECT column_name_1, SUM(column_name_2)
FROM table_name
GROUP BY column_name_1
HAVING (aritmethic function condition)