Learn MySQL basic commands for Beginners to Advance

Tables in a Database represents as a Metricxs which contains rows and columns that called as Reational Database. It can be assume very similar to a Spreadsheet that we would have worked on several time where vartical cells represent a Database columns and Horizotal Cell represents a Database Rows of the table.

- Columns represents headers of the table which can have types of data like strings, dates, numbers etc.
- Rows contains data of the table respected to the same columns data types and each cell of the row is called as the field.
- Syntax Validation. SQL query runs with the predefine patterns, if we forgetting to add any required symbols between the query such as comma or a brackets or a logical expressions can break things. Also MySql keywords are not case sensative, it can be written either samll or capitalize but the data of the table does matter.

Basics Database Commands:

1. Create Command

create command always gives you new table in the database, if the table alreay exists it will not create same table again in the database, same rule applies while creating new Database.

 

mysql> CREATE database test1;

mysql> CREATE TABLE pdfcup_student ( StudentID int
                     ,Name varchar(100)
                     ,Degree  varchar(100)
                     ,Experience varchar(100)
);

2. Show Command

show command is used to display the name of the tables or databases already exists in the Database server. Also, display the schema defination like all the columns names, data types, primary key and secondary key etc.


mysql> show databases;
--sys
--test1
--example1
mysql>
mysql>
mysql> show tables;
-- pdfcup_student
mysql>
mysql> show columns from pdfcup_student;

3. Primary Key

The primary key is the key which is unique in nature on entire database's table, it can be apply on any column of the table that will not allow to insert dublicate values on the seleced Primary Key Column. Primanry key cannot be decleared again if already define on another column of the same table as the Relational Databasse principles not supports it.


mysql> CREATE TABLE pdfcup_employee (EmpID INT NOT NULL
                      		  ,FIRST_NAME VARCHAR(100) NULL
                      		  ,LAST_NAME VARCHAR(100) NULL
                      		  ,PRIMARY KEY (EmpID));

4. Auto Increment

The AutoIncrement keyword used to make the rows identical for selecting the records, it initilize the unique number to the next row on last inserted data. Most of the cases, it is used with the primary key column.

   

mysql> CREATE TABLE pdfcup_autoINCREment (RollNo INT NOT NULL AUTO_INCREMENT
                      		  ,NAME VARCHAR(100) NULL
                      		  ,College_NAME VARCHAR(100) NULL
                      		  ,PRIMARY KEY (RollNo));

4. DROP Command

DROP command is used to completely remove a schema or table from the database. Data cannot be restored once the 'DROP' command is executed, hence, be very careful while using the DROP query.

   

mysql> DROP TABLE pdfcup_autoINCREment;
mysql>
mysql> DROP DATABASE testdb;
mysql>

5. ALTER Command

ALTER command is mainly used to change something on the schema or table definition in terms of deleting or adding an entire column or its data type.

 
mysql> ALTER TABLE pdfcup_autoINCREment drop column College_NAME;   
mysql>
mysql> ALTER TABLE pdfcup_employee add column DOB date NULL;

6. INSERT Command


mysql> INSERT INTO TableName( Column1 , Column2, ColumnN) VALUES ('Value1','Value2', 'ValueN');


mysql> INSERT INTO movies ( Title , ReleaseYear, Rating )
       VALUES('Spiderman Homecoming',2001,'PG-13')
            ,('The Amazing Spiderman 1',2016,'PG-13')
            ,('The Amazing Spiderman 2',2014,'PG')
            ,('The Amazing Spiderman 3',2009,'PG')
            ,('Superman vs Batman',2005,'PG')            
            ,('Tropic Thunder',2008,'R');

7. CONCAT Command


SELECT CONCAT(Column1, Separator, Column2) AS ColumnAliases FROM TableName;


SELECT CONCAT(LAST_NAME, ', ', FIRST_NAME) AS Name FROM pdfcup_employee;

8. SUBSTRING Command

To grab certain data from the rows under a column (instead of all the data) you can use substring:


SELECT Column1, SUBSTRING(Column1, StartRange, EndRange) AS ColumnAliases FROM TableName;


SELECT LAST_NAME, SUBSTRING(LAST_NAME,1,4) FROM pdfcup_employee;

9. SELECT Command


SELECT Column1, anyConditionColumn2 FROM TableName;


SELECT   FIRST_NAME,
		 CONCAT(SUBSTRING(FIRST_NAME,1,1),LAST_NAME) AS USER_NAME,
      	 1+2 AS Calculation
		 FROM pdfcup_employee;

10. LIMIT Command


SELECT * FROM TableName LIMIT AnyRange;


--Large datasets where you don’t want to tax the system returning millions of rows use limit:

SELECT * FROM pdfcup_employee LIMIT 10;

11. ORDERBY Command


SELECT * FROM TableName ORDER BY ColumnReference;


-- Order by can be used to sort the data that is returned in the query result. 
-- Bydefault the data is sorted with the Ascending order. 

mysql> SELECT * FROM pdfcup_employee ORDER BY 3 ;    -- (3 = Third column)

-- The ASC or DESC can be use to sort the data in Dscending or Ascending order.

mysql> SELECT * FROM pdfcup_employee ORDER BY LAST_NAME ASC;

mysql> SELECT * FROM pdfcup_employee ORDER BY 3 DESC;

12. DISTINCT Command


SELECT DISTINCT ColumnName FROM TableName;


-- Don’t like the duplicate rows and want to only display unique values, use DISTINCT to filter the records.
SELECT DISTINCT FIRST_NAME FROM pdfcup_employee;

13. COUNT Command


mysql> SELECT COUNT(*) AS AliasesName FROM TableName;

mysql> SELECT Column1 COUNT(*) AS AliasesName FROM TableName GROUP BY Column1;


-- Returns Total number of records exists in the table.
mysql> SELECT COUNT(*) AS TotalRecord FROM pdfcup_employee;

-- Returns Total number of unique records belongs to each name.
mysql> SELECT Last_Name, COUNT(*) AS Total FROM pdfcup_employee GROUP BY Last_Name;

14. LIKE Command


SELECT DISTINCT ColumnName FROM TableName;


--Like is used when it is necessary to match some values in the table, it works with the percentage '%' symbol.

-- returns all those records starts with character 'N'.
SELECT * FROM pdfcup_employee WHERE FIRST_NAME like 'n%';     

-- returns all those records having word 'at' in the value.
SELECT * FROM pdfcup_employee WHERE Department like '% at %'; 

-- Return total count having character 'at' in the value.
SELECT COUNT(*) AS "Toal_SH" FROM pdfcup_employee WHERE FIRST_NAME like '%at%';

SQL Pratical Handson Use Case

1. What are the top 5 selling products?

Solution: What are the top 5 selling products?


SELECT
  p.v2ProductName,
  p.v2ProductCategory,
  SUM(p.productQuantity) AS units_sold,
  ROUND(SUM(p.localProductRevenue/1000000),2) AS revenue
FROM `data-to-insights.ecommerce.web_analytics`,
UNNEST(hits) AS h,
UNNEST(h.product) AS p
GROUP BY 1, 2
ORDER BY revenue DESC
LIMIT 5;

2. Out of the total visitors who visited our website, what % made a purchase?

Solution: Out of the total visitors who visited our website, what % made a purchase?

 
#standardSQL
WITH visitors AS(
  SELECT 
    COUNT(DISTINCT fullVisitorId) AS total_visitors 
  FROM 
    'VideoAnalytics'
), 
purchasers AS(
  SELECT 
    COUNT(DISTINCT fullVisitorId) AS total_purchasers 
  FROM 
    'VideoAnalytics' 
  WHERE 
    totals.transactions IS NOT NULL
) 
SELECT 
  total_visitors, 
  total_purchasers, 
  total_purchasers / total_visitors AS conversion_rate 
FROM 
  visitors, 
  purchasers

3. How many visitors bought on subsequent visits to the website?

Solution: How many visitors bought on subsequent visits to the website?



# visitors who bought on a return visit (could have bought on first as well
WITH all_visitor_stats AS (
SELECT
  fullvisitorid, # 741,721 unique visitors
  IF(COUNTIF(totals.transactions > 0 AND totals.newVisits IS NULL) > 0, 1, 0) AS will_buy_on_return_visit
  FROM 'VideoAnalytics'
  GROUP BY fullvisitorid
)

SELECT
  COUNT(DISTINCT fullvisitorid) AS total_visitors,
  will_buy_on_return_visit
FROM all_visitor_stats
GROUP BY will_buy_on_return_visit

PDFcup.com