Search code snippets, questions, articles... Chrome Extension

mysql code snippets

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)
)
Find us at: