1. START THE SERVER
cd/mysql/bin //Go to directory
mysqld --console //run the sever
mysql -u root -p //open new terminal and enter this query for login
//enter the password
====================================================================================================================
2. DATABASE (Not Case Sensitive)
SHOW databases;
USE database_name;
SHOW tables;
====================================================================================================================
3. DDL (Data Defination Language)
CREATE, ALTER, TRUNCATE, DROP
3.1 CREATE
CREATE DATABASE database_name;
CREATE table table_name(
column datatype constraint,
column datatype
);
or
//with constraint name and type of constraint ,datatypes
CREATE table table_name(
column1 number(s),
column2 number(p,s),
column3 char(s),
column4 varchar(225),
column5 data,
constraint constraint_name primary key(column1),
constraint constraint_name foreign key(column2) references table_name(column),
constraint constraint_name unique key(column3),
constraint constraint_name NOT NULL(column4),
constraint constraint_name check (column5 >= 18)
);
3.2 ALTER (ADD, MODIFY, RENAME)
ALTER TABLE table_name
ADD column_NAME datatype(s); //you can use (AFTER column_NAME;) to place column into specific position
ALTER TABLE table_name
MODIFY column_NAME datatype(s);
----------------------------------------
ALTER TABLE table_name
RENAME To new_table_NAME;
ALTER TABLE table_name
RENAME COLUMN old_column_NAME To new_column_NAME;
---------------------------------------
3.3 TRUNCATE (Remove whole data in table)
TRUNCATE table table_name;
3.4 DROP (Remove everything)
DROP DATABASE database_name;
DROP TABLE table_name;
DROP COLUMN column_NAME;
DROP constraint constraint_NAME;
====================================================================================================================
4. DML (Data Manipulation Language)
(INSERT, UPDATE, DELETE, SELECT)
4.1 INSERT
INSERT INTO table_name
values('abc',123);
INSERT INTO table_name (column1,column2)
values('abc',123);
4.2 UPDATE
UPDATE table_name
SET column_NAME = value
WHERE condition;
4.3 DELETE
DELETE from table_name; //all row deleted
DELETE from table_name
where condition; //particular row deleted
4.4 SELECT
SELECT * from table_name;
SELECT DISTINCT column_NAME from table_name; //remove duplicate by using DISTINCT
-------------------------------------------------------------------------------------
//CLAUSES
WHERE FROM
IN
AND OR BETWEEN < > <= >= = !=
LIKE
SELECT column_NAME from table_name where column_NAME LIKE '%_abc_%';
SELECT column_NAME from table_name where column_NAME LIKE '%abc_%';
SELECT column_NAME from table_name where column_NAME LIKE '%_abc%';
SELECT column_NAME from table_name where column_NAME LIKE '_a_';
LIMIT
SELECT column_NAME from table_name LIMIT 0,5;
-------------------------------------------------------------------------------------
//ORDER BY
SELECT column_NAME from table_name
ORDER BY column_NAME ASC;
SELECT column_NAME from table_name
ORDER BY column_NAME DESC;
-------------------------------------------------------------------------------------
//Arithmetic(+,-,*./) and ALIAS
SELECT (((column_NAME+100)/100)*10) AS alias_name from table_name;
=====================================================================================================================
5. FUNCTION
count()
lower(c) | reverse(c)
upper(c) | mid(c,startIndex,endIndex) substr(c,startIndex,endIndex)
length(c) | concat(c1,c2)
initcap(c)
min(c) | round(c,2)
max(c) | ceil(c)
avg(c) | floor(c)
sum(c) | mod(m,n)
convert(c,data type)
to_char()
to_number()
to_date()
date_format(c,'%d %m %Y')
nvl(c1,value)
