-
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)
)
-
Add columns, alter table SQL query
ALTER TABLE
table_name
ADD (
Column_name1 data_type,
Column_name2 data_type (size)
);
-
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'
-
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;
-
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);
-
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
-
Get distinct records MySQL
SELECT DISTINCT
column_name1, column_name2
FROM
table_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
);
-
Create and select database MySQL
-- To create database
CREATE DATABASE db_name;
--To select database
USE db_name;
-
Show all Databases MySQL
SHOW DATABASES;
-
Show all tables in a database MySQL
SHOW TABLES:
-
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);
-
Rename table MySQL
-- SINGLE TABLE
RENAME TABLE courses TO subjects;
-- MULTIPLE TABLES
RENAME TABLE
courses TO subjects,
topics TO subject_topics;
-
Remove or drop table MySQL
-- Remove single table
DROP TABLE table_name;
-- Remove multiple tables
DROP TABLE table_name_1, table_name_2;
-
murata mysqlにcomman lineからアクセスする方法
mysql -u root -p test_db
# test_dbはデータベース名
-
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
*/
-
Delete a column from Table MySQL
ALTER TABLE Students
DROP COLUMN Email;
-
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