2018-11-25_mysql_popular_commands

--MySql Commands

--MySql Login
-h hostname -u username -p password

mysql-ctl cli
mysql-ctl START
mysql-ctl stop

SHOW DATABASES;
-- This list all the databases.

CREATE DATABASE [db_name_to_be_created];
CREATE DATABASE IF NOT EXISTS `wrong_database_name` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

DROP DATABASE [database_name_to_be deleted];
DROP DATABASE IF EXISTS `wrong_database_name`;

USE [database_name]


SHOW TABLES;
--List all the tables in selected database.

DESCRIBE [TABLE_NAME];
DROP TABLE [tablename];

CREATE TABLE people
(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255),
age INT
);

INSERT INTO
`people` (`name`, `age`)
VALUES
('John', 24),
('Jane', 22);

SELECT `name`, `age` FROM `people`;

SELECT
`name`,
`age`
FROM
`people`
WHERE
`age` > 22
LIMIT
1

UPDATE
`people`
SET
`age` = 23
WHERE
`name` = 'Jane';

DELETE FROM `people` WHERE `name` = 'Jane';

CREATE TABLE TABLE_NAME (
  column_1 datatype,
  column_2 datatype,
  column_3 datatype
);


ALTER TABLE TABLE_NAME ADD column_name datatype;


SELECT column_name(s) FROM TABLE_NAME WHERE column_1 = value_1 AND column_2 = value_2;
-- Combining two combinations

SELECT column_name AS 'Alias' FROM TABLE_NAME;
SELECT AVG(column_name) FROM TABLE_NAME;
SELECT column_name(s) FROM TABLE_NAME WHERE column_name BETWEEN value_1 AND value_2;

SELECT column_name,
  CASE
    WHEN condition THEN 'Result_1'
    WHEN condition THEN 'Result_2'
    ELSE 'Result_3'
  END
FROM TABLE_NAME;

SELECT COUNT(column_name) FROM TABLE_NAME;

SELECT column_name, COUNT(*)
FROM TABLE_NAME
GROUP BY column_name;

SELECT column_name, COUNT(*)
FROM TABLE_NAME
GROUP BY column_name
HAVING COUNT(*) > VALUE;

SELECT column_name(s)
FROM table_1
JOIN table_2
  ON table_1.column_name = table_2.column_name;

INSERT INTO TABLE_NAME (column_1, column_2, column_3)
VALUES (value_1, 'value_2', value_3);

SELECT column_name(s)
FROM TABLE_NAME
WHERE column_name LIKE pattern;

SELECT column_name(s)
FROM TABLE_NAME
LIMIT NUMBER;

SELECT MAX(column_name)
FROM TABLE_NAME;

SELECT MIN(column_name)
FROM TABLE_NAME;

SELECT column_name
FROM TABLE_NAME
ORDER BY column_name ASC | DESC;

SELECT column_name(s)
FROM table_1
LEFT JOIN table_2
  ON table_1.column_name = table_2.column_name;

SELECT ROUND(column_name, INTEGER)
FROM TABLE_NAME;

SELECT column_name
FROM TABLE_NAME;

SELECT DISTINCT column_name
FROM TABLE_NAME;

SELECT SUM(column_name)
FROM TABLE_NAME;

UPDATE TABLE_NAME
SET some_column = some_value
WHERE some_column = some_value;


SELECT column_name(s)
FROM TABLE_NAME
WHERE column_name operator VALUE;

WITH temporary_name AS (
   SELECT *
   FROM TABLE_NAME)
SELECT *
FROM temporary_name
WHERE column_name operator VALUE;