Everything About MySQL

Running mysql from cmd

For ex:

Creating the database

CREATE DATABASE hospital ;

Let us connect with the database

CONNECT/USE hospital ;

Creating a table

CREATE TABLE name_of_table(col1 datatype constraints , ....);

CREATE TABLE patient
(sno INT PRIMARY KEY
,name CHAR(20)
,age INT
,dept ENUM ('surgery','ent','medicine','orthopaedic')
,dateOfAdm DATE
,charges INT NOT NULL
,gender ENUM('M','F') DEFAULT 'M'	
);

UNIQUE KEY and PRIMARY KEY constraint can be applied at row level also.

To get info about a table:
DESC/DESCRIBE/EXPLAIN nameOfTable;

Datatypes in SQL

--Format of writing date,time and datetime
DATE : yyyy/mm/dd  (or)   yyyy-mm-dd
TIME : hh:mm:ss
DATETIME : yyyy/mm/dd hh:mm:ss

YEAR(2) -- last 2 digits of year
YEAR(4) -- all 4 digits of year

CHAR -- default size is 1 ( if not specified )
ENUM -- storage of specified  values

Insert command in SQL

Insertion of one value

INSERT INTO nameOfTable VALUES (val1,val2,...valn);

Insertion of one mutliple values

Inserting Few Columns

INSERT INTO nameOfTable 
(col1,col2,...coln) VALUES (val1,val2,...valn);

Querying in SQL

SELECT columns FROM nameOfTable
WHERE conditions
ORDER BY ASC/DESC;

Selection of all records

Selection of specific column

Selection of specific records

Selection of distinct/different records

Ordering/Arranging the records

ORDER clause: ORDER BY desc/asc
default:  asc

Displaying different name of column + expressions in SQL

Note : AS can be used with simple columns as well

Displaying different values in records if the value is NULL

Operators in sql

Comparison operators

< , <= , > , >= , = , != or <> 
Note that NULL value can not be checked by "=" operator.
It requires IS operator.

"BETWEEN" , "IN" and "IS" operator

colname BETWEEN value1 AND value2 
--checks if value corresponding to the colname 
--is in between value1 and value2  
--or  
--not

colname IN(val1,val2,...) 
--checks if value corresponding to the colname 
--is in the val1,val2,...
--or  
--not

colname IS value
--checks if value corresponding to the colname is equal to value
To Check For null value : colname IS NULL

Note: NOT BETWEEN , NOT IN AND IS NOT ALSO EXISTS

"AND" and "OR" operator

AND can also be written as &&
condition1 AND condition2 
--returns true if both conditions are true

OR can also be written as ||
condition1 OR condition2
--returns true if any condition is true

"NOT" operator

NOT condition

Converts a condition which is true to false and vice-versa

"LIKE" operator

Wild Card Characters

_ REPRESENTS 1 character BEFORE "a" 
% REPRESENTS any no of character(includes 0) AFTER "a"

Similarly: NOT LIKE also exists

LIKE with DATE datatype

--Another way of doing the same thing

SELECT * FROM patient 
WHERE MONTH(dateOfAdm) = 11 ;

Similarly "DAY" and "YEAR" function also exists

Update command in SQL

UPDATE nameOfTable
SET colname=value
WHERE condition;

--To change multiple columns
UPDATE nameOfTable
SET colName1 = value1 , colName2 = value2
WHERE condition;

Aggregate Functions in SQL

Max/Min Function

--syntax
MAX(columname) -- for getting the max value in a column
MIN(columname) -- for getting the min value in a column

Avg function

--syntax
AVG(columname)

Sum Function

--syntax
SUM(columname)

Count function

--syntax
COUNT(columname) --returns count of all values inside a column
COUNT(DISTINCT columname) --returns count of distinct values inside a column
COUNT(*) -- returns count of all records in a table

Date Based Functions in SQL

Now Function

--Example
SELECT NOW();

Date Function

--Example
SELECT DATE(NOW());
SELECT DATE('2004/07/13'); -- my birth date
SELECT DATE('2004/07/13 13:11:09');

Month and MonthName Function

--Example:1
SELECT MONTH(NOW());
SELECT MONTHNAME(NOW());

--Example:2
SELECT MONTH('2004/07/13') AS 'Birth Month No.' , MONTHNAME('2004/07/13') AS 'Birth Month Name';

Day and Dayname function

--Example:1
SELECT DAY(NOW()) , DAYNAME(NOW());

--Example:2
SELECT DAY('2004/07/13') AS 'Birth Day No.' , 
DAYNAME('2004/07/13') AS 'Birth Day Name';

Mathematical functions in SQL

Power/Pow Function

--syntax:
POW(m,n) 
--or:
POWER(m,n)

--Example
SELECT POW(2,3),POWER(3,2);

Mod Function (Remainder)

--syntax
m MOD n  --or
MOD(m,n) --or
m % n

--Example
SELECT 2%3 , 2 MOD 3 , MOD(3,2);

Round Function

--syntax
ROUND(m,n)

--Example:1
SELECT ROUND(1.073,2) -- 1.07
,ROUND(1.073,1)    	  -- 1.1
,ROUND(1.073,-1)      -- 0
,ROUND(1.073,-3);     -- 0

--Example:2
SELECT ROUND(149,2) -- 149
,ROUND(149,-1)      -- 150
,ROUND(149,-3);     -- 0

Text Based Functions in SQL

UCASE/UPPER Function

--syntax
UCASE(string) --or
UPPER(string)

--Example
SELECT UCASE('hello')          -- HELLO
,UPPER('My name is vikas!!!'); -- MY NAME IS VIKAS!!!

LCASE/LOWER Function

--syntax
LCASE(string) --or
LOWER(string)

--Example
SELECT LCASE('hello')          -- hello     
,LOWER('My name is vikas!!!'); -- my name is vikas!!!

CONCAT function

CONCAT(string1 , string2 , string3 ,...)

'Used to add two strings.'

UPDATE patient 
SET name = CONCAT("Mr." , name) 
WHERE gender = "M" ;

LENGTH function

LENGTH(string)

'returns length of a string'

LENGTH('hello')
returns: 5

LEFT function

--Used to get starting n characters from a string
syntax : LEFT(string , n_Characters)

UPDATE patient
SET name=LEFT(name,3);

'All the name will be having first 3 characters stored only .'

RIGHT function

--Used to get ending n characters from a string
syntax : LEFT(string , n_Characters)

Much more advanced example of functions

'Suppose we had stored below paths in a column named file
of a table named path.

C://testpath//pygame.html
C://testpath//tkinter.html
C://testpath//tkinter-widegts.html

If we want to store only name 
(pygame.html,tkinter.html,tkinter-widgets.html)
and remove the starting "C://testpath//"
then we can do that by the given code'

UPDATE path
SET file = RIGHT(file, LENGTH(file)-14)

'Here 14 is the length of C://testpath//
LENGTH(file) = LENGTH of the path

Hence , we are saying that calculate the total length of path
and then subtract 14 from it.
This will give us the no of characters
after C://testpath//
and then we can use RIGHT function
to get those file names and save them'

MID/SUBSTRING/SUBSTR function

--syntax
MID(string,start_position,length)
SUBSTR(string,start_position,length)
SUBSTRING(string,start_position,length)

--Examples
SELECT SUBSTRING('I love my India.',2,4);  -- lov
SELECT SUBSTRING('I love my India.',-2,4); -- a.

INSTR Function

--syntax
INSTR(string,substring)

--Example
SELECT INSTR('I Love My India','India') -- 11
,INSTR('I Love My India','Hate');       -- 0

LTRIM and RTRIM Function

--syntax
LTRIM(string)
RTRIM(string)

--Example
SELECT LTRIM('   I am vikas') -- (I am vikas):result
,RTRIM('I love India   ')					-- (I love India):result

TRIM Function in SQL

--syntax
TRIM([{LEADING/TRAILING/BOTH} {char/substring} FROM] string)

--Example:1 
SELECT TRIM('   I love India    '); -- 'I love India'

--Example:2
SELECT TRIM(BOTH 'v' FROM 'vvvI Love India vvvv')
,TRIM(LEADING 'v' FROM 'vvvI Love India vvvv')
,TRIM(TRAILING 'v' FROM 'vvvI Love India vvvv');

--Example:3
SELECT TRIM(LEADING 'v,' FROM 'v,v,v,vikas is my namev,v,v,');

Delete command in SQL

DELETE FROM nameOfTable 
WHERE condition;

Drop command in SQL

DROP DATABASE/TABLE nameOf_Datbase/Table;

Alter command in sql

ALTER TABLE nameOfTable CLAUSE changeInStructure ;

--5 clauses used : 
ADD,RENAME,CHANGE,DROP,MODIFY

rename clause

--Used to rename a column
syntax: RENAME newName

Drop clause

--1. TO REMOVE A COLUMN:- 
DROP nameOfColumn

--2. TO REMOVE THE PRIMARY KEY:- 
DROP PRIMARY KEY

--3. TO REMOVE THE UNIQUE KEY:- 
DROP INDEX indexOfUniqueKey 

--Note: Usually indexOfUniqueKey is the name of column 
--To see the index : SHOW CREATE TABLE nameOfTable;

SHOW CREATE DATABASE nameOfDatabase ; 
--This is also a valid command.

Add Clause

--1. ADDING COLUMN:- 
ADD colname DATATYPE CONSTRAINTS

--2. ADDING PRIMARY KEY/UNIQUE KEY :-
ADD PRIMARY KEY(nameOfCol1,nameOfCol2,..) 
ADD UNIQUE KEY(nameOfCol1,nameOfCol2,..)

Change Clause

--To change/rename the column names
CHANGE oldName newName DATATYPE

Modify clause

--To apply constraints or change datatype or reorder:
MODIFY colName NEW/OLD_DATATYPE 
NULL/NOT NULL/DEFAULT value FIRST/AFTER colname