DEV Community

Cover image for MySQL Cheat sheet
Shubham Athawane
Shubham Athawane

Posted on

MySQL Cheat sheet

  1. Creating tables πŸ“‘
CREATE TABLE BookHistory (
    Auther VARCHAR(129),
    title VARCHAR(129),
    btype VARCHAR(129),
    year CHAR(4)
);
Enter fullscreen mode Exit fullscreen mode
  1. To drop table column
ALTER TABLE tableName DROP column-Name;
ex.
ALTER TABLE BookHistory DROP Auther;
Enter fullscreen mode Exit fullscreen mode
  1. How to delete Data from a MySQL table ?
// Delete Statement is used to delete data, 

DELETE FROM table_name WHERE column_name = VALUE
EX. 
DELETE FROM BookHistory WHERE title = 'JungleBook'

Enter fullscreen mode Exit fullscreen mode
  1. Inserting Value into Table.
INSERT INTO table_name (Column1, Column2, Column3 ) VALUES (value1, value3, value3);
EX. 
INSERT INTO BookHistory (Author, title, btype, year) VALUES ("James Camron", "Avatar", "Adventure", 2006);
Enter fullscreen mode Exit fullscreen mode
  1. Update column name.
ALTER TABLE table_name RENAME COLUMN old_col_name TO new_col_name;
EX. 
ALTER TABLE BookHistory RENAME COLUMN btype TO Book_type;
Enter fullscreen mode Exit fullscreen mode
  1. Add new column in table
ALTER TABLE table_name ADD column_name DATATYPE;
EX. 
ALTER TABLE BookHistory ADD price INT(4);
Enter fullscreen mode Exit fullscreen mode
  1. The Select Command : DQL- DATA QUERY LANGUAGE Command.

    Used to visualize the table content.

SELECT * FROM table_name;
EX.
SELECT * FROM BookHistory;

// It will show all data present inside the BookHistory table
Enter fullscreen mode Exit fullscreen mode

We can use SELECT command to retrieve specific data from the table using WHERE clause . Like below

SELECT * FROM table_name WHERE condition;
EX.
SELECT * FROM BookHistory WHERE Book_type = "hacking";
Enter fullscreen mode Exit fullscreen mode
  1. MySQL numeric Data types

    MySQL has numeric data types for Integer, Fixed-point, Floating-Point and bit etc.

    *Numeric can be singed or unsinged

    1. TINYINT 6. FLOAT
    2. SMALLINT 7. DOUBLE
    3. MEDIUMINT 8. BIT
    4. INT
    5. BIGINT
  2. String Data type.

    1. CHAR 5. TINYBLOB
    2. VARCHAR 6. MEDIUMBLOB
    3. BINARY 7. LONGBLOB
    4. VARBINARY
  3. Temporal Data Types in MySQL

    1. DATE - A date value in 'CCYY-MM-DD'
    2. TIME- Time in 'HH:MM:SS'
    3. DATETIME - Date-Time - 'CCVV-MM-DD HH:MM:SS'
    4. TIMESTAMP - 'CCVV-MM-DD' HH:MM:SS
    5. YEAR - CCYY or YY
  4. Create user in MySQL

CREATE USE 'user-name' IDENTIFIED BY 'sample-password';
Enter fullscreen mode Exit fullscreen mode
  1. What are the "VIEWS" ?

β†’ In MySQL , A view consists of a set of rows that returned if particular query is executed.

β†’ It also known as "Virtual Table"

β†’ Advantages : Simplicity, security, not consume any memory, maintainability.

  1. How to you create & Execute VIEWS in MySQL ?

    β†’ We can create views using the CREATE VIEW Statement;

    -> A view is table in database that has no values, The views are created by joining one or more tables.
    -> Syntax for creating Views

    CREATE [or REPLACE] VIEW view_name AS SELECT columns FROM TABLES [ WHERE CONDITION ]
    
  2. SELECT AND command.

SELECT * FROM cust_tbl WHERE f_name = "shubham" AND cust_id > 3;
Enter fullscreen mode Exit fullscreen mode
  1. Truncate : It removes complete data without removing it’s structure. It is a DDL Command
TRUNCATE TABLE table_name;
EX.
TRUNCATE TABLE BookHistory;
Enter fullscreen mode Exit fullscreen mode
  1. Update Command in MySQL.
UPDATE 'table_name' SET 'column_name' = 'new_value' [WHERE CONDITION];
EX.
UPDATE BookHistory SET 'Auther' = 'James Bond' WHERE Auther = "JB"; 
Enter fullscreen mode Exit fullscreen mode
  1. BETWEEN : Get values between particular condition.
SELECT * FROM cus_tbl WHERE ID = 8 AND 11;
Enter fullscreen mode Exit fullscreen mode
  1. Find version of installed MySQL.

    Type Following Command.

SHOW VARIABLES LIKE "%version%";
Enter fullscreen mode Exit fullscreen mode
  1. ENUM and SET.

    ENUM data type is used in the MySQL datatypes to select any one value from the predefined list. Ex

CREATE DATABASE newEnum;
CREATE TABLE Clients (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50), 
    memberShip ENUM('silver', 'gold', 'Diamond'),
    interested SET('Movie', 'Music', 'concert');
)
Enter fullscreen mode Exit fullscreen mode

Cannot set multiple in ENUM but we can set multiple values in SET.

  1. What is different between Primary key and Foreign Key ?

-> The database table uses Primary key to identify each row uniquely, It is necessary to declare a primary key on those tables that require tp create a relationship among them. - One or More field of table can be declared as primary key

-> When primary key of any table is used in another table as the primary key or anther field for making a database relation then it is called Foreign Key.

-> Primary Key : Identified a record whereas foreign key refers the primary key of another tables. Primary key never accepts not null value. But Foreign key accepts null value.

  1. Filter duplicate values.

    -> A DISTINCT keyword is used to identify the duplicate data from table while retrieving the records. EX.

SELECT * FROM items
Enter fullscreen mode Exit fullscreen mode

output:

id name type brand man_id
1 samsung mobile samsung 1
2 iPhone mobile apple 2
3 Sony TV Sony 3
  • Using DISTINCT Keyword
SELECT DISTINCT type FROM items;
Enter fullscreen mode Exit fullscreen mode

output:

type
mobile
TV
  1. Which statement is used in a select query statement for partial matching ?

β†’ REGEXP and LIKE statement can be used in a select query for partial matching.

  • REGEXP : used to Search records bases on the pattern matching
  • LIKE : Is used to search any record by matching string at beginning or end or middle of particular filed value.

Ex. 1. REGEXP (Search records start with β€˜S’);

SELECT * FROM BookHistory WHERE name REGEXP "^s";
Enter fullscreen mode Exit fullscreen mode

Ex. 2. LIKE

SELECT * FROM BookHistory WHERE name LIKE "A%";
Enter fullscreen mode Exit fullscreen mode
  1. Rename Table
RENAME TABLE table_name TO new_table_name;
EX.
RENAME TABLE items TO Products;
Enter fullscreen mode Exit fullscreen mode
  1. Retrieve a portion of any Column value by using select Query ?

β†’ * SUBSTR() Function is used to retrieve the portion of any column. EX

OR we can we β€˜SUBSTRING’ keyword

SELECT SUBSTR(name, 1, 5) FROM Products;
Enter fullscreen mode Exit fullscreen mode

output:

Samsu
iPhon
Sony
  1. Calculate Sum of any Column of table ?

β†’ SUM() Function is used to calculate the sum of any column. EX.

SUM(DISTINCT expression)
EX.
SELECT SUM(Price) as total FROM Products;
Enter fullscreen mode Exit fullscreen mode

output:

total
2109.00

β€”β€”β€”β€” The Practical Approach β€”β€”β€”β€”

  1. Fetch FIRST_NAME from worker table.
SELECT First_name as worker_name from Woker;

//will return all first_name 's
Enter fullscreen mode Exit fullscreen mode
  1. Fetch FIRST_NAME as Upper Case
SELECT upper(First_name) FROM Worker

// Will return all name in upper case
Enter fullscreen mode Exit fullscreen mode
  1. Fetch unique Values from Department
SELECT DISTINCT department FROM Worker;
Enter fullscreen mode Exit fullscreen mode

output :

department
HR
ADMIN
ACCOUNT
  1. Find position of alphabets (”a”) in first_name column β€˜Amitabh’ from worker.
SELECT INSTR(first_name, BINARY'a') FROM worker WHERE first_name = "Amitabh";
Enter fullscreen mode Exit fullscreen mode

output :

INSTR( first_name, BINARY ’a’ )
5
  1. Remove white Spaces

    1. RTRIM : To remove white spaces from right side. EX
    SELECT RTRIM(first_name) FROM Worker;
    

    b. LTRIM : To remove white spaces from left side. EX

    SELECT LTRIM(Department) FROM Worker;
    
  2. Query to print first_name and salary from worker table into a single column NAME_SALARY

β†’ We use CONCAT() keyword to get combined result from two or more tables. EX

SELECT CONCAT(first_name, "=" , Salary) AS 'NAME_SALARY' FROM Worker;
Enter fullscreen mode Exit fullscreen mode
  1. Query to print all worker details from worker table order by First_Name Ascending.
SELECT * FROM Worker ORDER BY First_Name ASC;
Enter fullscreen mode Exit fullscreen mode
  1. Print details for worker with First_name as β€˜Shubham’ and β€˜NICK’ from worker table.
SELECT * FROM Worker WHERE First_Name In('Shubham', 'NICK');
Enter fullscreen mode Exit fullscreen mode
  1. Query to fetch the count of employee working in the department β€˜admin’.

β†’ The count function return cunt of given queries : EX.

SELECT COUNT(*) FROM Worker WHERE Department = 'Admin';
Enter fullscreen mode Exit fullscreen mode

output :

count(4)
4

AGGREGATE FUNCTIONS : -

SQL Aggregate functions is used to perform calculations on multiple row of a single column of a table it return single value.

  1. COUNT()
  2. SUM()
  3. AVG()
  4. MAX()
  5. MIN()

  1. COUNT()

β†’ Count the number of rows in database; It uses function COUNT(*) that return all rows

SELECT COUNT(*) FROM Worker WHERE Department = 'Admin';
Enter fullscreen mode Exit fullscreen mode
count(4)
4
  1. SUM()

β†’ SUM() Function is used to calculate the sum of all selected columns. it only work on numeric values. Syntax: sum();

SELECT SUM(salary) FROM Worker;
or 
SELECT 
Enter fullscreen mode Exit fullscreen mode

(will upload remaining one, sorry for inconvenience im running out of time nowadays)

Discussion (0)