MYSQL Database

MySQL shell:-
cmd:-

Create User:-
mysql> CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

Provide Access to the user:-
mysql>GRANT ALL PRIVILEGES ON * . * To 'newuser'@'localhost';

1.The asterisks in this command refer to the database and table (respectively) that they can access.
2.This specific command allows to the user to: read, edit, execute and perform all tasks across all the databases and tables.

Reload all the privileges to the new user:-
mysql>FLUSH PRIVILEGES;

1.once you have finalized the permissions that you want to set up for your new user's always be sure to reload all the privileges.
2.Your changes now will be in effect.

MySQL Users:-
SELECT User FROM mysql.user;

Here is a short list of other commands continuee........
--------
--------
--------
continuee.....
.
Review user's current permission:-
>SHOW GRANTS username;

Drop User:-
>DROP USER 'username'@'localhost';

logout current user:-
>quit;

 and log-back in with this command in terminal:-
 $mysql -u [username] -p

MySQL View

MySQL, View is a virtual table created by a query by joining one or more tables.

MySQL Create VIEW
A VIEW is created by SELECT statements. SELECT statements are used to take data from the source table to make a VIEW.

Syntax:

  1. CREATE [OR REPLACE] VIEW view_name AS  
  2. SELECT columns  
  3. FROM tables  
  4. [WHERE conditions];

Parameters:
OR REPLACE: It is optional. It is used when a VIEW already exist. If you do not specify this clause and the VIEW already exists, the CREATE VIEW statement will return an error.

view_name: It specifies the name of the VIEW that you want to create in MySQL.

WHERE conditions: It is also optional. It specifies the conditions that must be met for the records to be included in the VIEW.
The following example will create a VIEW name "trainer". This is a virtual table made by taking data from the table "courses".


  1. CREATE VIEW trainer AS  
  2. SELECT course_name, course_trainer   
  3.  FROM courses;

Created

To see the created VIEW:

SELECT * FROM trainer;


MySQL Update VIEW

MYSQL, the ALTER VIEW statement is used to modify or update the already created VIEW without dropping it.

Example:
  1. ALTER VIEW trainer AS  
  2. SELECT course_name, course_trainer, course_id  
  3. FROM courses;

To see the altered VIEW:

SELECT*FROM trainer;


MySQL Drop VIEW


You can drop the VIEW by using the DROP VIEW statement.

 Syntax:

 DROP VIEW [IF EXISTS] view_name;

 Parameters:
view_name: 
It specifies the name of the VIEW that you want to drop.

IF EXISTS: 
It is optional. If you do not specify this clause and the VIEW doesn't exist, the DROP VIEW statement will return an error.

Example:

DROP VIEW trainer;


Single line comment:-
--SELECT * FROM Customers;
Multi Line Comments:-
/*Select all the columns
of all the records
in the Customers table:*/
comment to ignore part of a statement:
SELECT * FROM Customers WHERE (CustomerName LIKE 'L%'
OR CustomerName LIKE 'R%' /*OR CustomerName LIKE 'S%'
OR CustomerName LIKE 'T%'*/ OR CustomerName LIKE 'W%')
AND Country='USA'
ORDER BY CustomerName;
Create Database:-
Create database test;
Create Table:-
Create table Persons
(
PersonID int,
Last Name varchar(255),
First Name varchar(255),
Address varchar(255),
City varchar(255)
);
Insert Data only in specified columns:-
INSERT into Customers (Cusname, city, country)
VALUES (‘Cardinal’, Stavanger‘’, ‘Norway’);


DELETE RECORDS FROM TABLE:-
Delete From Customers
WHERE CustomerName = ‘Alfred Schmidt’, City=’Hamburg’ AND ContactName = ‘Maria Anders’;

UPDATE RECORDS FROM TABLE:-
Update Customers
To SET ContactName = ‘Alfred Schmidt’, City=’Hamburg’
WHERE CustomerName = ‘Alfreds Futteriske’;

Update Warning!
UPDATE Customers
SET ContactName='Alfred Schmidt', City='Hamburg';

NOTE: If u are not giving WHERE Clause by default
It will affect all the records same.

RENAME TABLE NAME:-
Alter Table Persons
Rename To listOfPersons;

ADD COLUMN:-
Alter Table Persons
Add DateOfBirth date;

RENAME COLUMN:-
Alter Table Persons
Change userid username text;

Change Data Type:-
Alter Table Persons
Alter Column DateOfBirth year;


DROP COLUMN:-
Alter Table Persons
Drop Column DateOfBirth;

Truncate Table:-
Truncate Table Persons;

DELETE TABLE:-

Drop Table Persons;


MySql Joins:-

It is used to retrieve data from multiple tables.

 

LEFT OUTER JOIN

SELECT  officers.officer_name, officers.address, students.course_name  
FROM officers  
LEFT JOIN students  
ON officers.officer_id = students.student_id;

RIGHT OUTER JOIN

SELECT officers.officer_name, officers.address, students.course_name, students.student_nam  
FROM officers  
 RIGHT JOIN students  
ON officers.officer_id = students.student_id;

RIGHT OUTER JOIN

SELECT officers.officer_name, officers.address, students.course_name, students.student_nam  
FROM officers  
RIGHT JOIN students  
ON officers.officer_id = students.student_id; 

SQL PRIMARY KEY

A column or columns is called primary key (PK) that uniquely identifies each row in the table.
If you want to create a primary key, you should define a PRIMARY KEY constraint when you create or modify a table.
When multiple columns are used as a primary key, it is known as composite primary key.
In designing the composite primary key, you should use as few columns as possible. It is good for storage and performance both, the more columns you use for primary key the more storage space you require.
In terms of performance, less data means the database can process faster.

Points to remember for primary key:

·                     Primary key enforces the entity integrity of the table.
·                     Primary key always has unique data.
·                     A primary key length cannot be exceeded than 900 bytes.
·                     A primary key cannot have null value.
·                     There can be no duplicate value for a primary key.
·                     A table can contain only one primary key constraint.
When we specify a primary key constraint for a table, database engine automatically creates a unique index for the primary key column.

Main advantage of primary key:

The main advantage of this uniqueness is that we get fast access.

In oracle, it is not allowed for a primary key to contain more than 32 columns.

SQL PRIMARY KEY FOR ONE COLUMN:
The following SQL command creates a PRIMARY KEY on the "S_Id" column when the "students" table is created.
MySQL:
1.    CREATE TABLE students  
2.    (  
3.    S_Id int NOT NULL,  
4.    LastName varchar (255) NOT NULL,  
5.    FirstName varchar (255),  
6.    Address varchar (255),  
7.    City varchar (255),  
8.    PRIMARY KEY (S_Id)  
9.    )  

Oracle
CREATE TABLE students  
2.    (  
3.    S_Id int NOT NULL PRIMARY KEY,  
4.    LastName varchar (255) NOT NULL,  
5.    FirstName varchar (255),  
6.    Address varchar (255),  
7.    City varchar (255),  
8.    )  

SQL PRIMARY KEY FOR MULTIPLE COLUMNS:

MySQL, Oracle
CREATE TABLE students  
2.    (  
3.    S_Id int NOT NULL,  
4.    LastName varchar (255) NOT NULL,  
5.    FirstName varchar (255),  
6.    Address varchar (255),  
7.    City varchar (255),  
8.    CONSTRAINT pk_StudentID PRIMARY KEY (S_Id, LastName)  
9.    )  
Note:you should note that in the above example there is only one PRIMARY KEY (pk_StudentID). However it is made up of two columns (S_Id and LastName).

SQL PRIMARY KEY ON ALTER TABLE
When table is already created and you want to create a PRIMARY KEY constraint on the ?S_Id? column you should use the following SQL:
Primary key on one column:
1.    ALTER TABLE students  
2.    ADD PRIMARY KEY (S_Id)  
Primary key on multiple column:
1.    ALTER TABLE students  
2.    ADD CONSTRAINT pk_StudentID PRIMARY KEY (S_Id,LastName)  

//When you use ALTER TABLE statement to add a primary key, the primary key columns must not contain NULL values (when the table was first created).

HOW TO DROP A PRIMARY KEY CONSTRAINT?
If you want to DROP (remove) a primary key constraint, you should use following syntax:
MySQL:
1.    ALTER TABLE students  
2.    DROP PRIMARY KEY  
SQL Server / Oracle / MS Access:
1.    ALTER TABLE students  
2.    DROP CONSTRAINT pk_StudentID

SQL FOREIGN KEY

In the relational databases, a foreign key is a field or a column that is used to establish a link between two tables.
In simple words you can say that, a foreign key in one table used to point primary key in another table.

SQL FOREIGN KEY

In the relational databases, a foreign key is a field or a column that is used to establish a link between two tables.
In simple words you can say that, a foreign key in one table used to point primary key in another table.
Let us take an example to explain it:
Here are two tables first one is students table and second is orders table.
Here orders are given by students.





Here you see that "S_Id" column in the "Orders" table points to the "S_Id" column in "Students" table.

  1. The "S_Id" column in the "Students" table is the PRIMARY KEY in the "Students" table.
  2. The "S_Id" column in the "Orders" table is a FOREIGN KEY in the "Orders" table.
3.    The foreign key constraint is generally prevents action that destroy links between tables.
4.    It also prevents invalid data to enter in foreign key column.



SQL FOREIGN KEY CONSTRAINT ON CREATE TABLE:
(Defining a foreign key constraint on single column)
To create a foreign key on the "S_Id" column when the "Orders" table is created:
MySQL:
1.    CREATE TABLE orders  
2.    (  
3.    O_Id int NOT NULL,  
4.    Order_No  int NOT NULL,  
5.    S_Id int,  
6.    PRIMAY KEY (O_Id),  
7.    FOREIGN KEY (S_Id) REFERENCES Persons (S_Id)  
8.    )  
SQL Server /Oracle / MS Access:
1.    CREATE TABLE Orders  
2.    (  
3.    O_Id int NOT NULL PRIMAY KEY,  
4.    Order_No int NOT NULL,  
5.    S_Id int FOREIGN KEY REFERENCES persons (S_Id)  
6.    )  
SQL FOREIGN KEY CONSTRAINT FOR ALTER TABLE:
If the Order table is already created and you want to create a FOREIGN KEY constraint on the ?S_Id? column, you should write the following syntax:
Defining a foreign key constraint on single column:
MySQL / SQL Server / Oracle / MS Access:
1.    ALTER TABLE Orders  
2.    ADD CONSTRAINT fk_PerOrders  
3.    FOREIGN KEY(S_Id)  
4.    REFERENCES Students (S_Id)  
DROP SYNTAX FOR FOREIGN KEY COSTRAINT:
If you want to drop a FOREIGN KEY constraint, use the following syntax:
MySQL:
1.    ALTER TABLE Orders  
2.    ROP FOREIGN KEY fk_PerOrders  
SQL Server / Oracle / MS Access:
1.    ALTER TABLE Orders  
2.    DROP CONSTRAINT fk_PerOrders  

DIFFERENCE BETWEEN PRIMARY KEY AND FOREIGN KEY IN SQL:
These are some important difference between primary key and foreign key in SQL:-

1.    Primary key cannot be null on the other hand foreign key can be null.
2.    Primary key is always unique while foreign key can be duplicated.
3.    Primary key uniquely identify a record in a table while foreign key is a field in a table that is primary key in another table.
4.    There is only one primary key in the table on the other hand we can have more than one foreign key in the table.
5.    By default primary key adds a clustered index on the other hand foreign key does not automatically create an index, clustered or non-clustered.
6.    You must manually create an index for foreign key.


SQL Sub Query

A Subquery is a query within another SQL query and embedded within the WHERE clause.
Important Rule:
·                     A subquery can be placed in a number of SQL clauses like WHERE clause, FROM clause, HAVING clause.
·                     You can use Subquery with SELECT, UPDATE, INSERT, DELETE statements along with the operators like =, <, >, >=, <=, IN, BETWEEN, etc.
·                     A subquery is a query within another query. The outer query is known as the main query, and the inner query is known as a subquery.
·                     Subqueries are on the right side of the comparison operator.
·                     A subquery is enclosed in parentheses.
In the Subquery, ORDER BY command cannot be used. But GROUP BY command can be used to perform the same function as ORDER BY command.



1. SUB QUERIES WITH THE SELECT STATEMENT

SQL subqueries are most frequently used with the Select statement.
Syntax
1.    SELECT column_name  
2.    FROM table_name  
3.    WHERE column_name expression operator   
4.    ( SELECT column_name  from table_name WHERE ... );  

Example

Consider the EMPLOYEE table have the following records:














The subquery with a SELECT statement will be:
1.    SELECT *   
2.        FROM EMPLOYEE  
3.        WHERE ID IN (SELECT ID   
4.        FROM EMPLOYEE   
5.        WHERE SALARY > 4500);  

This would produce the following result:
2. SUB QUERIES WITH THE INSERT STATEMENT

·                     SQL subquery can also be used with the Insert statement. In the insert statement, data returned from the subquery is used to insert into another table.
·                     In the subquery, the selected data can be modified with any of the character, date functions.
Syntax:
1.    INSERT INTO table_name (column1, column2, column3....)   
2.    SELECT *  
3.    FROM table_name  
4.    WHERE VALUE OPERATOR  
Example
Consider a table EMPLOYEE_BKP with similar as EMPLOYEE.
Now use the following syntax to copy the complete EMPLOYEE table into the EMPLOYEE_BKP table.
1.    INSERT INTO EMPLOYEE_BKP  
2.       SELECT * FROM EMPLOYEE   
3.       WHERE ID IN (SELECT ID   
4.       FROM EMPLOYEE);  
3. SUB QUERIES WITH THE UPDATE STATEMENT
The subquery of SQL can be used in conjunction with the Update statement. When a subquery is used with the Update statement, then either single or multiple columns in a table can be updated.
Syntax
1.    UPDATE table  
2.    SET column_name = new_value  
3.    WHERE VALUE OPERATOR  
4.       (SELECT COLUMN_NAME  
5.       FROM TABLE_NAME  
6.       WHERE condition);  
Example
Let's assume we have an EMPLOYEE_BKP table available which is backup of EMPLOYEE table. The given example updates the SALARY by .25 times in the EMPLOYEE table for all employee whose AGE is greater than or equal to 29.
1.    UPDATE EMPLOYEE  
2.       SET SALARY = SALARY * 0.25  
3.       WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP  
4.          WHERE AGE >= 29);  
This would impact three rows, and finally, the EMPLOYEE table would have the following records.














4. SUB QUERIES WITH THE DELETE STATEMENT

The subquery of SQL can be used in conjunction with the Delete statement just like any other statements mentioned above.


Syntax
1.    
DELETE FROM TABLE_NAME  
2.    WHERE VALUE OPERATOR  
3.       (SELECT COLUMN_NAME  
4.       FROM TABLE_NAME  
5.       WHERE condition);   
Example
Let's assume we have an EMPLOYEE_BKP table available which is backup of EMPLOYEE table. The given example deletes the records from the EMPLOYEE table for all EMPLOYEE whose AGE is greater than or equal to 29.
1.    DELETE FROM EMPLOYEE  
2.       WHERE AGE IN (SELECT AGE FROM EMPLOYEE_BKP  
3.          WHERE AGE >= 29 );  
This would impact three rows, and finally, the EMPLOYEE table would have the following records.










  

SQL Clauses



The following are the various SQL clauses:
















1. GROUP BY


·                     SQL GROUP BY statement is used to arrange identical data into groups. The GROUP BY statement is used with the SQL SELECT statement.
·                     The GROUP BY statement follows the WHERE clause in a SELECT statement and precedes the ORDER BY clause.
·                     The GROUP BY statement is used with aggregation function.
Syntax
1.    SELECT column  
2.    FROM table_name  
3.    WHERE conditions   
4.    GROUP BY column  
5.    ORDER BY column  
Sample table:
PRODUCT_MAST


















Example:

1.    SELECT COMPANY, COUNT(*)  
2.    FROM PRODUCT_MAST   
3.    GROUP BY COMPANY;  
Output:
Com1   5
Com2   3
Com3   2

 

2. HAVING

·                     HAVING clause is used to specify a search condition for a group or an aggregate.
·                     Having is used in a GROUP BY clause. If you are not using GROUP BY clause then you can use HAVING function like a WHERE clause.
Syntax:
1.    SELECT column1, column2   
2.    FROM table_name  
3.    WHERE conditions   
4.    GROUP BY column1, column2   
5.    HAVING conditions  
6.    ORDER BY column1, column2;  
Example:
1.    SELECT COMPANY, COUNT(*)  
2.    FROM PRODUCT_MAST   
3.    GROUP BY COMPANY  
4.    HAVING COUNT(*)>2;  
Output:
Com1   5
Com2   3

 

3. ORDER BY

·                     The ORDER BY clause sorts the result-set in ascending or descending order.
·                     It sorts the records in ascending order by default. DESC keyword is used to sort the records in descending order.
Syntax:
1.    SELECT column1, column2  
2.    FROM table_name  
3.    WHERE condition  
4.    ORDER BY column1, column2... ASC|DESC;  
Where
ASC: It is used to sort the result set in ascending order by expression.
DESC: It sorts the result set in descending order by expression.

Example: Sorting Results in Ascending Order


Table:
CUSTOMER











ter the following SQL statement:
1.    SELECT *  
2.    FROM CUSTOMER  
3.    ORDER BY NAME;  
Output:



Example: Sorting Results in Descending Order

Using the above CUSTOMER table
1.    SELECT *  
2.    FROM CUSTOMER  
3.    ORDER BY NAME DESC;  
Output:








The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed. ... DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command. Therefore DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back.

DELETE and TRUNCATE are two SQL commands used to remove records from a particular table. But they differ in how they execute and operate.
 
–> DELETE: (MSDN)
1. Removes Some or All rows from a table.
2. A WHERE clause can be used to remove some rows. If no WHERE condition is specified, all rows will be removed.
3. Causes all DELETE triggers on the table to fire.
4. It removes rows row-by-row one at a time and records an entry in the Transaction logs, thus is slower than TRUNCATE.
5. Every deleted row in locked, thus it requires more number of locks and database resources.
6. According to MS BOL, if a table is a Heap or no Clustered index is defined than the row-pages emptied are not de-allocated instantly and remain allocated in the heap. Thus, no other object can reuse this associated space. Thus to de-allocate the space a Clustered index is required or TABLOCK hint should be applied in the DELETE statement.
7. This is a DML command as it is just used to manipulate/modify the table data. It does not change any property of a table.


–> TRUNCATE: (MSDN)
1. Removes All rows from a table.
2. Does not require a WHERE clause, so you can not filter rows while Truncating.
3. With SQL Server 2016 you can Truncate a Table Partition, for more details check [here].
4. IDENTITY columns are re-seeded on this operation, if no seed was defined then the default value 1 is used.
5. No Triggers are fired on this operation because it does not operate on individual rows.
6. It de-allocates Data Pages instead of Rows and records Data Pages instead of Rows in Transaction logs, thus is faster than DELETE.
7. While de-allocating Pages it locks Pages and not Rows, thus it requires less number of locks and few resources.
8. TRUNCATE is not possible when a table:
a. is reference by a Foreign Key or tables used in replication or with Indexed views.
b. participates in an Indexed/Materialized View.
c. published by using Transactional/Merge replication.
9. This is a DDL command as it resets IDENTITY columns, de-allocates Data Pages and empty them for use of other objects in the database.
Note: It is a misconception among some people that TRUNCATE cannot be roll-backed. But in reality both DELETE and TRUNCATE operations can be COMMITTED AND ROLL-BACKEDif provided inside a Transaction. The only method to Rollback a committed transaction after DELETE/TRUNCATE is to restore the last backup and run transactions logs till the time when DELETE/TRUNCATE is about to happen.


–> DROP: (MSDN)
1. The DROP TABLE command removes one or more table(s) from the database.
2. All related Data, Indexes, Triggers, Constraints, and Permission specifications for the Table are dropped by this operation.
3. Some objects like Views, Stored Procedures that references the dropped table are not dropped and must be explicitly dropped.
4. Cannot drop a table that is referenced by any Foreign Key constraint.
5. According to MS BOL, Large tables and indexes that use more than 128 extents are dropped in two separate phases: Logical and Physical. In the Logical phase, the existing allocation units used by the table are marked for de-allocation and locked until the transaction commits. In the physical phase, the IAM pages marked for de-allocation are physically dropped in batches.

https://youtu.be/sVo4sHpegI0

No comments:

Post a Comment

Subscribe to: Posts (Atom)