mysql code snippets

MySQL Query to change column type

ALTER TABLE users
MODIFY COLUMN username varchar(255);

Get table information in MySQL

desc table_name;

Show all column names of a table MySql query

-- First method
DESCRIBE table_name;

-- Second method
SHOW COLUMNS FROM table_name;

-- Third method
SELECT column_name
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = 'database_name' AND TABLE_NAME = 'table_name';

-- Get column names in a single line (comma seperated)
SELECT group_concat(column_name)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = 'database_name' AND TABLE_NAME = 'table_name';

Apply JOIN on multiple columns of the tables MySql

--using OR operator
Select 
    employee.name, employee.email, employee_address.street
FROM Employee
INNER JOIN employee_address
    ON employee.Id = employee_address.employee_id 
    OR employee.pincode = employee_address.street_code 
    OR employee.parmanent_code = employee_address.location_code 

--using AND operator
Select 
    employee.name, employee.email, employee_address.street
FROM Employee
INNER JOIN employee_address
    ON employee.Id = employee_address.employee_id 
    AND employee.pincode = employee_address.street_code 
    AND employee.parmanent_code = employee_address.location_code

Select second highest column value MySQL

SELECT
    (SELECT DISTINCT
            column_name
        FROM
            table_name
        ORDER BY column_name DESC
        LIMIT 1 OFFSET 1
    ) AS second_highest_value
;

Order by Date where date in string format MySQL

SELECT Id, FirstName, Lastname
FROM Students 
ORDER BY STR_TO_DATE(CreatedDate, '%d-%m-%Y') ASC;

-- USE 'DESC' in place of 'ASC' if you want result in decending format

Delete a column from Table MySQL

ALTER TABLE Students
DROP COLUMN Email;

Add comment in Sql or Mysql

-- USE -- FOR SINGLE LINE COMMENT
-- SELECT * FROM STUDENTS

/* BLOCK LEVEL COMMENTS - COMMENT OUT A FULL BLOCK */
/*
SELECT NAME
FROM USERS
*/

murata mysqlã«comman lineã‹ã‚‰ã‚¢ã‚¯ã‚»ã‚¹ã™ã‚‹æ–¹æ³•

mysql -u root -p test_db
# test_dbã¯ãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹å

Remove or drop table MySQL

-- Remove single table
DROP TABLE table_name;

-- Remove multiple tables
DROP TABLE table_name_1, table_name_2;

Rename table MySQL

-- SINGLE TABLE
RENAME TABLE courses TO subjects;

-- MULTIPLE TABLES
RENAME TABLE 
    courses TO subjects,
    topics TO subject_topics;

Add unique constraint MySQL

-- While creating table (Single)
CREATE TABLE table_name( colum_1 data_type,  column_2 data_type UNIQUE );

-- While creating table (Combine multiple column)
CREATE TABLE table_name(
   column_1 data_type,
   column_2 data_type,
   UNIQUE(column_1, column_2)
);

-- Add unique constraints on existing table
ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
UNIQUE (column_name);

Show all tables in a database MySQL

SHOW TABLES:

Show all Databases MySQL

SHOW DATABASES;

Create and select database MySQL

-- To create database
CREATE DATABASE db_name;

--To select database
USE db_name;

In Operator MySQL

-- Match with list values
SELECT 
    first_name, 
    last_name,
    city
FROM
    Students
WHERE
    city IN ('Delhi' , 'Newyork');


--Match with subquery records
SELECT    
	first_name, 
    last_name,
    city
FROM    
	Students
WHERE city IN
(
    SELECT 
        city
    FROM 
        city_list
);

Get distinct records MySQL

SELECT DISTINCT
    column_name1, column_name2
FROM
    table_name;

Order by ascending and descending MySQL

SELECT * FROM table_name FROM ORDER BY column_name ASC
SELECT * FROM table_name FROM ORDER BY column_name DESC

Add foreign key in existing table Mysql

ALTER TABLE 
ContactDetails 
ADD 
EmployeeId INT UNSIGNED NOT NULL;

ALTER TABLE 
ContactDetails 
ADD 
CONSTRAINT fk_EMP_CONTACT FOREIGN KEY (EmployeeId) REFERENCES Employee(Id);

Get duplicate rows and its counts MySQL

SELECT 
    column_name, 
    COUNT(column_name)
FROM
    table_name
GROUP BY column_name
HAVING COUNT(column_name) > 1;

Select rows from table SQL query

//select all rows from table
SELECT * FROM table_name

//select rows based on a condition
SELECT * FROM table_name WHERE column_name='value'

Add columns, alter table SQL query

ALTER TABLE 
    table_name 
ADD (
    Column_name1 data_type, 
    Column_name2 data_type (size)
);

Create table with constraints MySQL query

CREATE TABLE table_name(
    Id int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    Column1 char(40) DEFAULT NULL,
    Column2 varchar(100) DEFAULT '',
    Date timestamp DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(Id),
    UNIQUE (Column1)
)