SQL : Mosh
Mon Oct 18 2021 14:17:05 GMT+0000 (Coordinated Universal Time)
Saved by @jpannu #linux #server
//Install MySql on Windows system. > USE dataBaseName // Clauses - SELECT | FROM | WHERE | ORDER BY - SELECT : '*' Will return all the coloumns from the selected table. > SELECT fName,lName FROM customers //This will only look for the 2 specified columns. > SELECT lname, points, points + 10 FROM customers // 'points + 10' Will add the given points to existing points coloumn. > SELECT lname, points, points - 10 * 800 FROM customers // 'points - 10' Will Subtract the given points from existing points coloumn amnd then multiple by 800. We can use brackets if needed. > SELECT lname, points, points - 10 * 800 AS discount_factor FROM customers // 'points - 10 * 800' column heading will not make sense to read, So we use 'AS' ( Alliance ), It will rename the heading from 'points - 10 * 800' to 'discount_factor' > SELECT DISTINCT fname,lname from tableName; //It will return the unique values for fname and lname, Duplicates will be removed. - WHERE SELECT name FROM table1 WHERE name = 'tom'; SELECT name FROM table1 WHERE name != 'tom'; SELECT name FROM table1 WHERE name <> 'tom'; //"! and <>" are same. //WHERE NOT SELECT * FROM table1 WHERE NOT score = 120; SELECT * FROM table1 WHERE SCORE NOT IN ( 132, 20, 60); SELECT * FROM table1 WHERE score IN ( 132, 20, 60); SELECT score FROM table1 WHERE score = 100; SELECT score FROM table1 WHERE score > 10; SELECT score FROM table1 WHERE score < 200; SELECT * FROM table1 WHERE date > '1991-10-10'; //'ORDER BY' > SELECT * FROM customers ORDER BY first_name; //It will sort the list using the 'first Name' in ascending order. > SELECT * FROM customers ORDER BY first_name DESC; //It will sort the list using the 'first Name' in decending order. * WE can also add multiple //AND SELECT * FROM table1 WHERE date >= '1990-7-10' AND date <= '1990-10-10'; //OR SELECT * FROM table1 WHERE date >= '1990-7-10' OR date <= '1990-10-10'; //AND OR SELECT * FROM table1 WHERE date >= '1990-7-10' AND points > 100 AND score = 120; ** AND : 'AND' Operator has higer precedence then the 'OR' operator** : I.E 'AND' Operator will be executed first, Even if the 'OR' is mentioned first. We can change the order using parenthesis. 'IN' Operator: // 'NOT' // IN : We can use 'NOT' pretty much anywhere. SELECT * FROM table1 WHERE score = 20 OR score = 60 OR score = 132; OR SELECT * FROM table1 WHERE score IN ( 132, 20, 60); SELECT * FROM table1 WHERE score NOT IN ( 132, 20, 60); //Between SELECT * FROM table1 WHERE SCORE BETWEEN 100 AND 150; //Both the points are inlusive. SELECT * FROM table1 WHERE SCORE NOT BETWEEN 100 AND 150; //Like : It's used to find some pattern string/number/data. > SELECT * FROM table2 WHERE fname LIKE "kara%"; //look for string starting with 'kar' > LIKE "%ingh"; //look for string ending with 'ingh' > LIKE "%IN%"; //look for string 'IN' ( can be anywhere ) > NOT LIKE "%IN%"; // NOT look for string 'IN' ( can be anywhere ) > LIKE "___karan%"; //look for string 'karan' starting after 3 characters ( 3 uderscores ). > LIKE "b__y"; //look for string starting with 'b', ending with 'y' and have any two characters in between. //Regexp ( Regular expression ) > SELECT * FROM table2 WHERE fname REGEXP 'tring'; //It is same as '%tring%' > SELECT * FROM table2 WHERE fname REGEXP '^stri'; //It is same as '%tring' ( look for 'Starting' of starting ) > SELECT * FROM table2 WHERE fname REGEXP 'ing$'; //It is same as 'ing%' ( look for Ending Part) > SELECT * FROM nameinfo WHERE Name REGEXP 'Tom|Allen'; //We add pipe in between 2 strings, This way it will search for the either of the provided strings. > SELECT * FROM nameinfo WHERE Name REGEXP 'Tom|Allen|karan'; > SELECT * FROM nameinfo WHERE Name REGEXP 'Tom|Allen|rose$'; > SELECT * FROM nameinfo WHERE Name REGEX '[gim]e'; //It will look for 'ge' or 'ie' or 'me', 1 will be string from the brackets and which will be matched with 'e'. > SELECT * FROM nameinfo WHERE Name REGEX '[a-c]j'; //It will look for the pattern 'aj'or 'bj' or 'cj'. > SELECT * FROM nameinfo WHERE Name REGEX 'e[gim]'; //It will look for patter 'eg' or 'ei' or 'em' > SELECT * FROM nameinfo WHERE Name REGEX 'j[a-c]'; //It will look for the pattern 'ja'or 'jb' or 'jc'. > SELECT * FROM nameinfo WHERE Name REGEXP 'om$|en$'; //Will look for 'om' or 'en' at the end of the string. > SELECT * FROM nameinfo WHERE Name REGEXP 's|ll'; //Will look for 's' or 'll' anythere in the field ( can be starting,ending or middle.) -------------------------------------------- //Comments : We use double Hyphen ( -- ) in the front of the code to make it as a comment. > SELECT * > --FROM customers //This line will be commented. > WHERE customer_id = 100 --------------------------------------------- //Table > CREATE TABLE tableName ( columnName1 dataType(), columnName2 dataType(), columnName3 dataType(), ); > INSERT INTO tableName VALUES("abv","ds","sda"); > ALTER TABLE tableName ADD newColumn1 dataType(); //Add new coloumn to the existing table > DROP TABLE tableName1; //'tableName1' will be deleted from the selected DB. ---------------------------------------- //Null ( Empty Field ) > SELECT * FROM tableName WHERE phoneNumer IS NULL; //It will return all the phoneNumber's which has values NULL. -----------------------------------------------
Comments