-- Add in existing table
ALTER TABLE table_name
ADD COLUMN is_active BOOLEAN DEFAULT 1
-- Add while creating the table
CREATE TABLE table_name (
is_active BOOLEAN NOT NULL default 1
);
ALTER TABLE users
MODIFY COLUMN username varchar(255);
-- 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';
--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
(SELECT DISTINCT
column_name
FROM
table_name
ORDER BY column_name DESC
LIMIT 1 OFFSET 1
) AS second_highest_value
;
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
ALTER TABLE Students
DROP COLUMN Email;
-- USE -- FOR SINGLE LINE COMMENT
-- SELECT * FROM STUDENTS
/* BLOCK LEVEL COMMENTS - COMMENT OUT A FULL BLOCK */
/*
SELECT NAME
FROM USERS
*/
mysql -u root -p test_db
# test_dbã¯ãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹å
-- Remove single table
DROP TABLE table_name;
-- Remove multiple tables
DROP TABLE table_name_1, table_name_2;
-- SINGLE TABLE
RENAME TABLE courses TO subjects;
-- MULTIPLE TABLES
RENAME TABLE
courses TO subjects,
topics TO subject_topics;
-- 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);
-- To create database
CREATE DATABASE db_name;
--To select database
USE db_name;
-- 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
);
SELECT DISTINCT
column_name1, column_name2
FROM
table_name;
SELECT * FROM table_name FROM ORDER BY column_name ASC
SELECT * FROM table_name FROM ORDER BY column_name DESC
ALTER TABLE
ContactDetails
ADD
EmployeeId INT UNSIGNED NOT NULL;
ALTER TABLE
ContactDetails
ADD
CONSTRAINT fk_EMP_CONTACT FOREIGN KEY (EmployeeId) REFERENCES Employee(Id);
SELECT
column_name,
COUNT(column_name)
FROM
table_name
GROUP BY column_name
HAVING COUNT(column_name) > 1;
//select all rows from table
SELECT * FROM table_name
//select rows based on a condition
SELECT * FROM table_name WHERE column_name='value'
ALTER TABLE
table_name
ADD (
Column_name1 data_type,
Column_name2 data_type (size)
);
INSERT INTO table_name (Column1, Column2, Column3)
VALUES(1, 'Value2', 'Value3')
or
INSERT INTO table_name
SET
Column1 = 'Value1',
Column2 = 'Value2',
Column3 = 'Value3'
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)
)
Search code snippets, questions, articles...