07. Structured Query Language (SQL)

Structured Query Language (SQL)

MySQL cheatsheet

Types of SQL Commands
DDL Data Definition Language	DML Data Manipulation Language	DCL Data Control Language	TCL Transaction Control Language	DQL Data Query Language
CREATE ALTER DROP RENAME TRUNCATE	INSERT  UPDATE DELET	CRANT REVOK	COMMIT ROLLBACK SAVE POINT	SELECT

SQL COMMAND 

COMMAND	SQL SYNTAX	Example (Using testDB as databaseName)
DATABASE
Create Database	CREATE DATABASE DatabaseName	CREATE DATABASE testDB;
Alter Database	ALTER DATABASE DatabaseName	ALTER DATABASE  testDB
Show list of database	SHOW DATABASES;	SHOW DATABASESES;
Use database	User DatabaseName;	Use testDB;
Drop database	DROP DABASE DatabaseName;	DROP DATABASE testDB;
BackUp Database	BACKUP DATABASE databaseName TO DISK = ‘filepath’;	BACKUP DATABASE testDB TO DISK= ‘D:\backup\testDB.bak’;
(A differential back up only backs up the parth of the database that have changed since the last full database backup)	BACKUP DATABASE databaseName TO DISK = ‘filepath’ WITH DIFFERENTIAL;	BACKUP DATABASE testDB TO DISK= ‘D:\backup\testDB.bak’ WITH DIFFERENTIAL;

TABLE
List of table	SHOW TABLES;	SHOW TABLES;
Structure of table	SHOW FIELDS FROM table_name; Or DESCRIBE table_name	SHOW FIELDS FROM tableName ; DESCRIBE tableName;
Create Table	SHOW CREATE TABLE table_name Constraints: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT, CREATE INDEX Data type :  char(n), varchar(n), text Bit, tinyint, smallint, int, bigint, decimal Datetime, smalldatetime, date, time, timestamp	CREATE TABLE user(  id bigint not null,  name varchar(255),  Last_name varchar(255),  age  varchar(255),  email varchar(255),  primary key (id)  );
Drop table	DROP TABLE table_name TRUNCATE TABLE table_name;	DROP TABLE user;  → deletes a table in the database TRUNCATE TABLE user; →deletes the data inside a table
ALTER TABLE	ADD Column ALTER TABLE table_name ADD column_name datatype; DROP Column ALTER TABLE table_name  DROP COLUMN column_name  RENAME COLUMN ALTER TABLE table_name  RENAME COLUMN  old_name TO new_name ; ALTER TABLE table_name ALTER COLUMN column_name datatype; ALTER TABLE table_name  MODIFY column_name datatype; ALTER TABLE table MODIFY field1 type1 FIRST	ALTER TABLE user ADD phone_no int; ALTER TABLE user DROP COLUMN phone_no int; ALTER TABLE user  RENAME COLUMN  phone_no TO contact ; ALTER TABLE user ALTER COLUMN phone_no BigInt;

Data Query Language

Syntex  

SELECT column_name(s), aggretate_function(column_name)  FROM table_name 

WHERE condition  GROUP BY  column_name

 HAVING condition

 ORDER BY column_name(s);

SELECT 

SELECT * FROM table;

SELECT * FROM table1, table2;

SELECT field1, field2 FROM table1, table2;

SELECT * FROM table_name WHERE condition

Aggregate Functions

Total /Sum → select sum(salary) from tbl_accounts; 

Total /Sum → select avg(salary) from tbl_accounts; 

Total /Sum → select min(salary) from tbl_accounts; 

Total /Sum → select max(salary) from tbl_accounts; 

To Count Total → select count (account_name) from tbl_accounts;

To Count Total of Distinct/unique  → select count (distinct account_name) from tbl_accounts;

Distinct 

→ select distinct account_name from tbl_accounts;

GROUP BY Statement

→ The GROUP BY statement groups rows that have the same values into summary rows. → The GROUP BY statement is often used with aggregate functions 

Syntex  

SELECT column1, aggretate_function(column2)  FROM table_name WHERE condition  GROUP BY  column1 HAVING condition  

;GroupBy → select *  from tbl_accounts group by account_name ;

→ select account_name , count(account_name )  from tbl_accounts group by account_name ; →no of repeat name 

→ select *, count(account_name )  from tbl_accounts group by account_name ;

Note: WHERE clause, which filters individual rows before any grouping is done, the HAVING clause filters groups of rows after the grouping is performed.

HAVING→ The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions. 

SELECT column_name(s), aggretate_function(column2)  FROM table_name WHERE condition  GROUP BY  column_name HAVING condition  

Example : 

→ SELECT act_name, COUNT(ac_name)  FROM tbl_accounts  WHERE condition  GROUP BY  account_name HAVING count(ac_name)>20 

LIMIT 

MySQL → SELECT column_name(s)FROM table_name WHERE condition LIMIT number;

SQL Server / MS Access Syntax → SELECT TOP number|percent column_name(s)FROM table_name WHERE condition;

Oracle 12 : → SELECT column_name(s)FROM table_name ORDER BY column_name(s) FETCH FIRST number ROWS ONLY;

Joins

JOIN / INNER JOIN → Only matching values in both tables

→ SELECT column_name(s) FROM table1 JOIN table2   ON table1 .id1 = table2.id2 WHERE condition;

LEFT JOIN / LEFT OUTER JOIN→ all records from the left table (table1), and the matching records from the right table (table2).

→ SELECT column_name(s) FROM table1 LEFT JOIN table2   ON table1 .id1 = table2.id2 WHERE condition;

RIGHT JOIN /  RIGHT OUTER JOIN→ all records from the right table (table2), and the matching records from the left table (table1).

→ SELECT column_name(s) FROM table1 RIGHT JOIN table2   ON table1 .id1 = table2.id2 WHERE condition;

FULL OUTER JOIN / FULL JOIN → all records when there is a match in left (table1) or right (table2) table records.

→ SELECT column_name(s) FROM table1 FULL OUTER JOIN  table2   ON table1 .id1 = table2.id2 WHERE condition;

SELF JOIN →A self join is a regular join, but the table is joined with itself.

→SELECT column_name(s) FROM table1 T1, table1 T2 WHERE condition;

Conditions

1. field1 = value1→  equal 

2. field1 <> value1 →  not equal to → SELECT * FROM employees WHERE name <> 'ram';

3. field1 LIKE 'value _ %' →  wildcard character in LIKE expressions to → SELECT * FROM employees WHERE name LIKE 'J%';

4. field1 IS NULL → to check if the value of field1 is NULL. → SELECT *FROM your_table WHERE field1 IS NULL;

5. field1 IS NOT NULL→ to check if the value of field1 is not NULL. → SELECT *FROM your_tableWHERE field1 IS NOT NULL;

6. field1 IS IN (value1, value2) → to check if the value of field1 matches any of the values specified within the parentheses.

1. → SELECT *FROM your_tableWHERE field1 IN (value1, value2);

7. field1 IS NOT IN (value1, value2) → checks if a value does not match any value in a list.

1.  → SELECT *FROM your_table WHERE field1 NOT IN (value1, value2);

8. condition1 AND condition2 → to combine multiple conditions in a WHERE clause to form a more complex condition. 

1. → SELECT * FROM your_table WHERE condition1 AND condition2;

9. condition1 OR condition2→  to combine multiple conditions in a WHERE clause to form a more complex condition. 

1. → SELECT * FROM your_table WHERE condition1 OR condition2;

Insert

INSERT INTO table1 (column1, column1 ) VALUES (value, value);

Delete

DELETE FROM table1 WHERE  ID =2 → condition with the criteria that specify which rows you want to delete.

 TRUNCATE TABLE table 1→ RUNCATE TABLE statement. However, it removes all rows from the table without considering any conditions:

DELETE FROM table1 WHERE condition

DELETE table1 , table2 FROM table1 JOIN table2 ON table1.id1 = table2.id2  WHERE condition;→ DELETE with JOIN to delete records from multiple tables based on a condition.

Update

UPDATE table1 SET field1=new_value1 WHERE condition;

UPDATE table1, table2 SET field1=new_value1, field2=new_value2, ... WHERE  table1.id1 = table2.id2 AND condition;