Mysql Advance functoin system step by step list

SQL DML and DDL

SQL can be divided into two parts: The Data Manipulation Language (DML) and the Data Definition Language (DDL).
The query and update commands form the DML part of SQL:
  • SELECT - extracts data from a database
  • UPDATE - updates data in a database
  • DELETE - deletes data from a database
  • INSERT INTO - inserts new data into a database
The DDL part of SQL permits database tables to be created or deleted. It also define indexes (keys), specify links between tables, and impose constraints between tables. The most important DDL statements in SQL are:
  • CREATE DATABASE - creates a new database
  • ALTER DATABASE - modifies a database
  • CREATE TABLE - creates a new table
  • ALTER TABLE - modifies a table
  • DROP TABLE - deletes a table
  • CREATE INDEX - creates an index (search key)
  • DROP INDEX - deletes an index 



SQL Essential and most popular topic and funstion list:
………SELECT……
1.SELECT * FROM `user` ;

2.SELECT  user_name FROM user;
3.SELECT user_name,user_roll FROM user;

…………DISTINCT……
  1. SELECT DISTINCT user_name FROM user;
……………..Combind Distinct…….
1. SELECT DISTINCT user_name,user_roll FROM user


…….WHERE …..
  1. SELECT * FROM user WHERE user_name='saiful';

…… AND……
  1. SELECT * FROM user WHERE user_name='saiful' AND user_roll='30091';


…… OR….
  1. SELECT * FROM user WHERE user_name='saiful' or user_roll='300091';

……..COMBIN AN D & OR…..
  1. SELECT * FROM user WHERE user_name='saiful' AND (user_roll='300091' OR user_password='454358');

…….. ASC……..
  1. SELECT * FROM user ORDER BY id ASC;
…… DESC……..
1.SELECT * FROM user ORDER BY id  DESC;


…..ORDER BY….
1. SELECT * FROM user ORDER BY user_name;

………ORDER BY DESC…..
  1. SELECT * FROM user ORDER BY user_name DESC;

……. LIMIT…….
  1. SELECT user_name FROM user LIMIT 3;
  2. SELECT * FROM user LIMIT 3;
  3. SELECT * FROM accounce ORDER BY O_Id DESC LIMIT 2,4

……. LIKE …..
  1. SELECT * FROM user WHERE user_name LIKE 's%'
  2. SELECT * FROM user WHERE user_name LIKE '%sai%';
  3. SELECT * FROM user WHERE user_name NOT LIKE '%naz%';
  4. SELECT * FROM user WHERE user_name LIKE '%ful%';
…… s …..
  1. SELECT * FROM user WHERE user_name IN ('rima','nazmul');

……BETWEEN ….
1. SELECT * FROM user WHERE id BETWEEN '1' AND '2';


……..DELETE…..
  1. DELETE  FROM user WHERE user_name='saiful' AND user_roll='300091';


…….. LCASE ……
  1. SELECT LCASE (user_name) FROM user;


…….UCASE…….
  1. SELECT UCASE (user_name)FROM user;

                   ……..MID……

  1. SELECT MID(USER_name,1,4) as SmallCity FROM user
                            …… AVG……
  1. SELECT AVG (orderprice) AS TOTAL FROM accounce;
  2.  SELECT (orderprice) AS total FROM accounce;
…….SUM……..
  1. SELECT SUM(orderprice) AS total FROM accounce;
  2. SELECT customar FROM accounce
  3. WHERE orderprice>(SELECT AVG(orderprice) FROM accounce);
………………COUNT………………………
  1. SELECT COUNT(orderprice)FROM accounce;
  2. SELECT COUNT(DISTINCT orderprice) FROM accounce;
  3. SELECT COUNT(customar) AS customarsaiful FROM accounce WHERE customar='saiful';
  4. SELECT COUNT(*) AS NumberOfOrders FROM accounce;
  5. SELECT COUNT(*) FROM accounce USE INDEX(PRIMARY);
                          
                          …… MAX……
  1. SELECT MAX(orderprice) FROM accounce;
  2. SELECT MAX(customar) FROM accounce;
  3. SELECT MAX(customar) AS total FROM accounce;
  4. SELECT MAX(orderprice) AS total FROM accounce;
……………….MIN………………….
  1. SELECT MIN(orderprice) AS total FROM accounce;
  2. SELECT MIN(orderprice) AS total FROM accounce;
…………….ALIAS…………………………….
1. SELECT user_name as alias FROM user;
 
………………FIRST………….

……………………..LAST………….

…………GROUP BY………….
  1. SELECT Customar,SUM(orderprice) FROM accounce GROUP BY Customar;

………………………ROUND……………..
  1. SELECT customar, ROUND(orderprice,0) as orderprice FROM accounce;
……………….NOW………………………..
  1. SELECT customar, orderprice, Now() as orderDate FROM accounce;

…………………FORMATE…………………………
1. SELECT customar, orderprice, FORMAT(Now(),'YYYY-MM-DD') as orderDate
FROM accounce

……………RAND……………
  1. SELECT * FROM accounce ORDER BY RAND();
  2. SELECT * FROM accounce ORDER BY RAND()LIMIT 2,4
  3. SELECT FLOOR(RAND()*10) FROM accounce;

…….. DATABASE INDEX FOR ALL SHOW…..
  1. SHOW INDEX FROM accounce;

…………….NOW…………..
  1. SELECT NOW() FROM table_name
  2. SELECT NOW()FROM accounce
  3. SELECT NOW()customar FROM accounce WHERE customar = 'saiful'
  4. SELECT NOW()customar FROM accounce WHERE customar = 'saiful' AND O_Id = '6'
====     FORMAT==== 
1. SELECT user_roll, FORMAT(Now(),'YYYY-MM-DD') as PerDate
FROM user;
============= ROUND=============
  1. SELECT ROUND(user_roll) FROM user;

==================== Update function ================
1.

No comments:

Post a Comment