Querying data


SELECT CONCAT(“HI",”MySql")

Evaluation Order



Sorting data


SELECT contactLastname, contactFirstname
FROM customers
ORDER BY contactLastname DESC , contactFirstname ASC;

#The FIELD() function returns the position of the value in the list of values value1, value2, and so on
#FIELD(value, value1, value2, ...)

SELECT orderNumber,status FROM orders 
ORDER BY  FIELD(status, 'In Process', 'On Hold', 'Cancelled', 'Resolved', 'Disputed', 'Shipped');

Filtering data


SELECT firstName, lastName FROM employees WHERE lastName LIKE '%son' ORDER BY firstName;

SELECT 1 OR 0 AND 0;
SELECT (1 OR 0) AND 0;

#The value can be a column or an expression.
...value IN (value1, value2, value3,...)

SELECT 1 IN (1,2,3); #returns 1 because 1 is in the list

wildcard characters (%, _)


SELECT select_list
FROM table_name
ORDER BY  sort_expression LIMIT offset, row_count;
#from row *offset *row_count num of rows will be returned, 1st row is 0
#alternative way -> LIMIT row_count OFFSET offset

...LIMIT row_count; === LIMIT 0 , row_count;

SELECT customerName, creditLimit FROM customers ORDER BY creditLimit DESC LIMIT 1,1;

Joining tables


SELECT m.member_id,m.name AS member,c.committee_id, c.name AS committee
FROM members m
RIGHT JOIN committees c USING(name)
WHERE m.member_id IS NULL;

SELECT o.orderNumber, customerNumber, productCode FROM orders o LEFT JOIN orderDetails USING(orderNumber) WHERE orderNumber = 10123;

SELECT o.orderNumber, customerNumber, productCode FROM orders o LEFT JOIN orderDetails d ON o.orderNumber = d.orderNumber AND o.orderNumber = 10123;

Grouping data



Group By


FROM->WHERE->GROUP_BY->HAVING->SELECT->DISTINCT->ORDER_BY->LIMIT 

#To obtain the number of orders in each status
SELECT status, COUNT(*) FROM orders GROUP BY status;

Having


...GROUP BY year HAVING year > 2003;

SELECT ordernumber, SUM(quantityOrdered) AS itemsCount, SUM(quantityOrdered*priceeach) AS total 
FROM orderdetails 
GROUP BY ordernumber HAVING total > 1000;

Subqueries


SELECT lastName,
         officeCode
FROM employees
WHERE officeCode IN 
    (SELECT officeCode
    FROM offices
    WHERE country = 'USA');

#this is similar to WHERE officeCode IN (1,2,3)
#Operand should contain 1 column(s)
#IN,NOT IN, comparison OPERATORS,EXISTS and NOT EXISTS

correlated subquery


#1
SELECT productname,
         buyprice
FROM products AS p1
WHERE buyprice > 
    (SELECT AVG(buyprice)
    FROM products
    WHERE productline = p1.productline)
#2
SELECT customerNumber,
         customerName
FROM customers
WHERE EXISTS
    (SELECT orderNumber,
         SUM(priceEach * quantityOrdered)
    FROM orderdetails
    INNER JOIN orders
    USING (orderNumber)
    WHERE customerNumber = customers.customerNumber
    GROUP BY  orderNumber
    HAVING SUM(priceEach * quantityOrdered) > 60000);

derived tables


DerivedTables.png

SELECT 
    customerGroup, 
    COUNT(cg.customerGroup) AS groupCount
FROM
    (SELECT 
        customerNumber,
            ROUND(SUM(quantityOrdered * priceEach)) sales,
            (CASE
                WHEN SUM(quantityOrdered * priceEach) < 10000 THEN 'Silver'
                WHEN SUM(quantityOrdered * priceEach) BETWEEN 10000 AND 100000 THEN 'Gold'
                WHEN SUM(quantityOrdered * priceEach) > 100000 THEN 'Platinum'
            END) customerGroup
    FROM
        orderdetails
    INNER JOIN orders USING (orderNumber)
    WHERE
        YEAR(shippedDate) = 2003
    GROUP BY customerNumber) cg
GROUP BY cg.customerGroup;    

#1
SELECT * FROM customers c1
WHERE customerNumber IN 
    (SELECT customerNumber FROM orders );
#2    
SELECT * FROM customers c1
WHERE EXISTS 
    (SELECT *  FROM orders o  WHERE c1.customerNumber = o.customerNumber );

Set operators



Managing databases



Working with tables


#Insert data into a tbale from other tables
INSERT INTO credits(customerNumber, creditLimit)
SELECT customerNumber, creditLimit FROM customers WHERE creditLimit > 0;

ALTER Table/s


ALTER TABLE tbl ADD col1 column_definition  [FIRST | AFTER column_name], col2 column_definition  [FIRST | AFTER column_name]...
#Adding a new NOT NULL columns to an existing table will populated with default values

ALTER TABLE tbl MODIFY col1 column_definition [ FIRST | AFTER column_name], col1 column_definition [ FIRST | AFTER column_name]...;

ALTER TABLE tbl CHANGE COLUMN original_name new_name column_definition [FIRST | AFTER column_name];

ALTER TABLE table_name DROP COLUMN column_name;
#The COLUMN keyword in the DROP COLUMN clause is optional
#Before droping, need to check use cases of the column like FKs,SPs,Views,Triggers...

ALTER TABLE table_name RENAME TO new_table_name;

#Note:
RENAME TABLE table_name TO new_table_name;

DROP [TEMPORARY] TABLE [IF EXISTS] tbl1,tbl2...;

SELECT IF(count(*) = 1, 'Exist','Not Exist') AS result
FROM information_schema.columns
WHERE table_schema = 'classicmodels' AND table_name = 'vendors' AND column_name = 'phone';

Temporary tables


CREATE TEMPORARY TABLE temp_table_name
SELECT * FROM original_table LIMIT 0;

Truncateing a Table


TRUNCATE [TABLE] table_name; #The TABLE keyword is optional, but to distinguish TRUNCATE TABLE and TRUNCATE() use it.

Generated Column


CREATE TABLE contacts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    fullname varchar(101) GENERATED ALWAYS AS (CONCAT(first_name,' ',last_name))
);

column_name data_type [GENERATED ALWAYS] AS (expression) [VIRTUAL | STORED] [UNIQUE [KEY]]

MySQL constraints



Primary Key



Foreign Key


[CONSTRAINT constraint_name] #constraint_name is optional
FOREIGN KEY [foreign_key_name] (column_name, ...) # foreign_key_name is optional
REFERENCES parent_table(colunm_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]

CASCADE: if a row from the parent table is deleted or updated, the values of the matching rows in the child table are automatically deleted or updated. SET NULL: if a row from the parent table is deleted or updated, the values of the foreign key column (or columns) in the child table are set to NULL. RESTRICT: if a row from the parent table has a matching row in the child table, MySQL rejects deleting or updating rows in the parent table. NO ACTION: is the same as RESTRICT. SET DEFAULT: is recognized by the MySQL parser. However, this action is rejected by both InnoDB and NDB tables. 

#Drop FK
ALTER TABLE table_name DROP FOREIGN KEY constraint_name;

#Disabling and Enabling FK. Important for csv imports kind of things and droping tables
#Or else you wll have to import parent table data first and then import child table data, this check each row in parent table and time consuming
SET foreign_key_checks = 0;
SET foreign_key_checks = 1;

UNIQUE Key


CREATE TABLE suppliers (
    supplier_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    phone VARCHAR(15) NOT NULL UNIQUE, #column constraint
    address VARCHAR(255) NOT NULL,
    CONSTRAINT uc_name_address UNIQUE (name,address) #table constraint, Here name is optional
);

#Drop UK
DROP INDEX index_name ON table_name;
ALTER TABLE table_name DROP INDEX index_name;

#Add UK
ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (column_list);

NOT NULL


#Add not null
ALTER TABLE tasks 
CHANGE end_date end_date DATE NOT NULL;

#Drop not null
ALTER TABLE tasks 
MODIFY end_date DATE;

DEFAULT


#Adddefault
ALTER TABLE table_name
ALTER COLUMN column_name SET DEFAULT default_value;

#When inserting, you can pass DEFAULT or ignore passing value
INSERT INTO cart_items(name, quantity, price, sales_tax)
VALUES('Battery',4, 0.25 , DEFAULT);

#Drop default
ALTER TABLE table_name
ALTER column_name DROP DEFAULT;

CHECK


CREATE TABLE parts (
    part_no VARCHAR(18) PRIMARY KEY,
    description VARCHAR(40),
    cost DECIMAL(10,2 ) NOT NULL CONSTRAINT constraint_name CHECK (cost >= 0), #name is optional,this is a column constraint
    price DECIMAL(10,2) NOT NULL CHECK (price >= 0), #column constraint
    CONSTRAINT parts_chk_price_gt_cost  CHECK(price >= cost) #table constraint
);

INSERT INTO parts(part_no, description,cost,price) 
VALUES('A-001','Cooler',0,-100); //Fail

INSERT INTO parts(part_no, description,cost,price) 
VALUES('A-001','Cooler',200,100); //Fail

#Add CHECK
ALTER TABLE table_name ADD CHECK (expression);
ALTER TABLE table_name ADD CONSTRAINT contraint_name CHECK (expression);

#Drop Check
ALTER TABLE table_name DROP CHECK constraint_name;

MySQL data types



Modifying data in MySQL


INSERT INTO table_name(c1,c2,...)
VALUES(v1,v2,..),(v1,v2,..)...;

#Insert using a result of a query
INSERT INTO table_name(column_list)
SELECT select_list FROM another_table WHERE condition;

#This is also possible
INSERT INTO stats(totalProduct, totalCustomer, totalOrder)
VALUES(
	(SELECT COUNT(*) FROM products),
	(SELECT COUNT(*) FROM customers),
	(SELECT COUNT(*) FROM orders)
);

INSERT IGNORE INTO subscribers(email)
VALUES('john.doe@gmail.com'), 
      ('jane.smith@ibm.com');

#Converting a datetime string into a DATETIME (YYYY-MM-DD HH:MM:SS) using STR_TO_DATE()
INSERT INTO events (event_name, event_time) VALUES ('MySQL Party', STR_TO_DATE('10/28/2023 20:00:00', '%m/%d/%Y %H:%i:%s'));

#Converting a date string into the DATE type (YYYY-MM-DD) format using STR_TO_DATE()
INSERT INTO events (event_name, event_date) VALUES ('MySQL Innovate', STR_TO_DATE('10/29/2023', '%m/%d/%Y'));

#UPDATE to replace string 
UPDATE employees
SET email = REPLACE(email,'@classicmodelcars.com','@mysqltutorial.org')
WHERE
   jobTitle = 'Sales Rep' AND
   officeCode = 6;

#Update by a result of a select
UPDATE customers 
SET  salesRepEmployeeNumber = (SELECT...FROM...WHERE...ORDER BY...LIMIT 1)
WHERE salesRepEmployeeNumber IS NULL;

UPDATE T1
[INNER JOIN | LEFT JOIN] T2 ON T1.C1 = T2.C1
SET T1.C2 = T2.C2, 	T2.C3 = expr
WHERE condition;

#This
UPDATE T1
SET T1.c2 = T2.c2,T2.c3 = expr
WHERE T1.c1 = T2.c1 AND condition;

#Is equel to
UPDATE T1
INNER JOIN T2 ON T1.C1 = T2.C1
SET T1.C2 = T2.C2, T2.C3 = expr
WHERE condition;

UPDATE employees 
  INNER JOIN merits ON employees.performance = merits.performance 
SET salary = salary + salary * percentage;

UPDATE employees 
  LEFT JOIN merits ON employees.performance = merits.performance 
SET salary = salary + salary * COALESCE(percentage, 0.015);

#if T! or T@ omit, this query delete records only in other table
DELETE T1, T2 FROM T1
INNER JOIN T2 ON T1.key = T2.key
WHERE condition;

#This clause delete rows from T1 table that does not have corresponding rows in the T2 table:
DELETE T1 FROM T1 
LEFT JOIN T2 ON T1.key = T2.key 
WHERE T2.key IS NULL;

Replace


#Insert
REPLACE INTO cities(id,population) VALUES(2,3696820);

#Update
REPLACE INTO cities SET id = 4,name = 'Phoenix',population = 1768980;

#Select and update
REPLACE INTO cities(name,population)
SELECT name,population FROM cities WHERE id = 1;

Common Table Expressions


#syntax
WITH cte_name (column_list) AS ( query ) 
SELECT * FROM cte_name;

#1
WITH customers_in_usa AS (
    SELECT customerName, state FROM customers WHERE  country = 'USA'
) 
SELECT 
	customerName
FROM customers_in_usa WHERE  state = 'CA' ORDER BY customerName;

#2
WITH topsales2003 AS (...query with complex joins)
SELECT column_list
FROM another_table JOIN topsales2003

#3 Using multiple CTEs
WITH salesrep AS (...query),
customer_salesrep AS (...query)
SELECT * FROM customer_salesrep ORDER BY customerName;

#4 Joining two CTEs example
WITH e AS (...query),
o AS (...query)
SELECT * FROM e INNER JOIN o USING (colum)

Table Locking


LOCK TABLES table_name1 [READ | WRITE], 
            table_name2 [READ | WRITE],
             ... ;
#Unlock
UNLOCK TABLES;
#If the session is terminated, MySQL will release all the locks implicitly.

#Get connection ID of the current session
SELECT CONNECTION_ID();

#Show the processlist that are waiting for execute once a particular lock is released
SHOW PROCESSLIST;

MySQL globalization


SHOW CHARACTER SET; #To list all character sets in the current MySQL server
#Ex: utf8, utf8mb4, latin1, utf16

#Setting character sets for client connections
#1
SET NAMES 'utf8mb4';

#2 in configuration file (If the application supports the --default-character-set  option)
[mysql]
default-character-set=utf8mb4

#3 Using the charset in connection strings ( connectors allow you to specify )
$dsn ="mysql:host=$host;dbname=$db;charset=utf8mb4";

Server | Database | Table | Column


User-defined variables


#variable assignment
SET @variable_name = value; OR
SET @variable_name := value; OR
SELECT value INTO @variable_name;

SELECT c1, c2, c3, ...
INTO @v1, @v2, @v3,...
FROM table_name WHERE condition;

MySQL import & export CSV



Advanced techniques



Natural Sorting



Comparing two tables


SELECT pk, c1
FROM
 (
   SELECT t1.pk, t1.c1
   FROM t1
   UNION ALL
   SELECT t2.pk, t2.c1
   FROM t2
)  t
GROUP BY pk, c1
HAVING COUNT(*) = 1
ORDER BY pk

Find Duplicate Rows/Values


SELECT 
    first_name, COUNT(first_name),last_name,  COUNT(last_name),email,COUNT(email)
FROM contacts
GROUP BY first_name , last_name , email
HAVING  COUNT(first_name) > 1 AND COUNT(last_name) > 1 AND COUNT(email) > 1;

Delete Duplicate Rows


  1. using the DELETE JOIN statement - refer above
  2. using an intermediate table (create an intermediate table with same structure->insert distinct records->delete original table->rename intermediate table to the origianl tabel )
  3. using the ROW_NUMBER() function (from version 8.02)

Copy Table


#Copy only data
CREATE TABLE IF NOT EXISTS offices_bk 
SELECT * FROM offices;

SELECT * FROM offices_bk;

#copy data and all database objects associated with the offices table
# indexes, primary key constraints, foreign key constraints, triggers, and so on
CREATE TABLE offices2 LIKE offices;

INSERT offices2
SELECT * FROM offices;

#Copying tables across databases
CREATE TABLE destination_db.new_table 
LIKE source_db.existing_table;

INSERT destination_db.new_table 
SELECT * FROM source_db.existing_table;

Interval


SELECT '2020-01-01' + INTERVAL -1 DAY; # 2019-12-31

SELECT DATE_ADD('2020-01-01', INTERVAL 1 MONTH) 1_MONTH_LATER, 
       DATE_SUB('2020-01-01',INTERVAL 1 MONTH) 1_MONTH_BEFORE; # 2020-02-01 | 2019-12-01

SELECT TIMESTAMPADD(MINUTE,30,'2020-01-01') 30_MINUTES_LATER; # | 2020-01-01 00:30:00

Commands


#Connect
mysql -u username -p # Connect to a Local MySQL server
mysql -u username -p db_name # Connect to Local MySQL server with database
mysql -h remote_host -u username -p # Connect to Remote MySQL Server
mysql -h remote_host -u username -p db_name # Connect to Remote MySQL Server with Database
mysql -h remote_host -P port -u username -p # Specify MySQL server port
mysql --login-path=mypath # Connect to a MySQL server using a login path
mysql -h remote_host -u username -p --ssl-ca=ca.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem # Connect to MySQL Server with SSL

#Exit
\q OR quit OR exit Alternatively Ctrl+D in Unix and Ctrl+Z in windows  #Exit MySQL client

#Read
#If the number of columns is high, the output will not be readable. To fix it, you can 
#display the query results in a vertical format using the \G instead of the semicolon (;):
SELECT firstName, lastName FROM employees ORDER BY firstName LIMIT 2\G

#Execute
mysql -u username -p -e "SELECT * FROM table_name;" db_name # Execute a command and exit

mysql -u username -p db_name < script.sql # Execute queries from a file, This can be used to import a dump db as well

#use the result of another command as an input for mysql using the | operator:
#works on Unix-like systems such as macOS and Ubuntu
cat query.sql | mysql -u root -p classicmodels

mysql -u username -p db_name -e "select * from tblName" > path/to/file # Write a query result to a file,
# This can be used for export a db as a dump file as well if the file is .sql

Ex) writes the data to the employee.txt file
mysql -u root -p classicmodels -e "select * from employees" > employees.txt