MySQL
Tutorial
SQL CREATE DATABASE Example
The following SQL statement creates a database called
"my_db":
CREATE DATABASE my_db;
NOTE: Database
tables can be added with the CREATE TABLE statement.
SQL CREATE TABLE Example
CREATE TABLE Persons
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
Insert Data Only in Specified Columns
INSERT INTO Customers
(CustomerName, City, Country)
VALUES ('Cardinal', 'Stavanger',
'Norway');
Assume we wish to insert
a new row in the "Customers" table.
INSERT INTO Customers (CustomerName, ContactName,
Address, City, PostalCode, Country)
VALUES ('Cardinal','Tom B. Erichsen','Skagen
21','Stavanger','4006','Norway');
DELETE FROM Customers
WHERE CustomerName='Alfreds Futterkiste'
AND ContactName='Maria Anders';
UPDATE Customers
tgSET ContactName='Alfred Schmidt',
City='Hamburg'
WHERE CustomerName='Alfreds Futterkiste';
UPDATE Customers
SET ContactName='Alfred Schmidt',
City='Hamburg';
NOTE: If u are not giving WHERE Clause by
default
It will effect all the records same.
1.Check before every time.
2.When u update records.
ALTER TABLE NAME
RENAME TO new_name;
ALTER TABLE Persons
ADD DateOfBirth date
---This is working---
ALTER TABLE data CHANGE
oldname newname text;
ALTER TABLE Persons
ALTER COLUMN DateOfBirth
year
ALTER TABLE Persons
DROP COLUMN DateOfBirth
DELETE
TABLE:
DROP TABLE table_name
Distinct keyword is used for eliminating in the column
duplicate values.
SELECT DISTINCT City FROM
Customers
MySQL
JOINS
MySQL JOINS are used with SELECT statement. It is used to retrieve
data from multiple tables. It is performed whenever you need to fetch records
from two or more tables.
There are three types of MySQL joins:
- MySQL
INNER JOIN (or sometimes called simple join)
- MySQL
LEFT OUTER JOIN (or sometimes called LEFT JOIN)
- MySQL
RIGHT OUTER JOIN (or sometimes called RIGHT JOIN)
MySQL
Inner JOIN (Simple Join)
The MySQL INNER JOIN is used to return all rows from multiple
tables where the join condition is satisfied. It is the most common type of
join.
Syntax:
1. SELECT columns
2. FROM table1
3. INNER JOIN table2
4. ON table1.column = table2.column;
Image representation:
Let's take an example:
Consider two tables "officers" and "students",
having the following data.
Execute the following query:
1. SELECT officers.officer_name, officers.address, students.course_name
2. FROM officers
3. INNER JOIN students
4. ON officers.officer_id = students.student_id;
Output:
MySQL
Left Outer Join
The LEFT OUTER JOIN returns all rows from the left hand table
specified in the ON condition and only those rows from the other table where
the join condition is fulfilled.
Syntax:
1. SELECT columns
2. FROM table1
3. LEFT [OUTER] JOIN table2
4. ON table1.column = table2.column;
Image representation:
Let's take an example:
Consider two tables "officers" and "students",
having the following data.
Execute the following query:
1. SELECT officers.officer_name, officers.address, students.course_name
2. FROM officers
3. LEFT JOIN students
4. ON officers.officer_id = students.student_id;
Output:
MySQL
Right Outer Join
The MySQL Right Outer Join returns all rows from the RIGHT-hand
table specified in the ON condition and only those rows from the other table
where he join condition is fulfilled.
Syntax:
1. SELECT columns
2. FROM table1
3. RIGHT [OUTER] JOIN table2
4. ON table1.column = table2.column;
Image representation:
Let's take an example:
Consider two tables "officers" and "students",
having the following data.
Execute the following query:
1. SELECT officers.officer_name, officers.address, students.course_name, students.student_name
2. FROM officers
3. RIGHT JOIN students
4. 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. )
SQL Server, Oracle, MS Access:
1. 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, SQL Server, Oracle, MS Access:
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. 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.
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.
First table:
S_Id
|
LastName
|
FirstName
|
CITY
|
1
|
MAURYA
|
AJEET
|
ALLAHABAD
|
2
|
JAISWAL
|
RATAN
|
GHAZIABAD
|
3
|
ARORA
|
SAUMYA
|
MODINAGAR
|
Second table:
O_Id
|
OrderNo
|
S_Id
|
1
|
99586465
|
2
|
2
|
78466588
|
2
|
3
|
22354846
|
3
|
4
|
57698656
|
1
|
Here you
see that "S_Id" column in the "Orders" table points to the
"S_Id" column in "Students" table.
- The
"S_Id" column in the "Students" table is the PRIMARY
KEY in the "Students" table.
- The
"S_Id" column in the "Orders" table is a FOREIGN KEY
in the "Orders" table.
The foreign key constraint is generally prevents action that
destroy links between tables.
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-
Primary key cannot be null on the other hand foreign key can be
null.
Primary key is always unique while foreign key can be duplicated.
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.
There is only one primary key in the table on the other hand we
can have more than one foreign key in the table.
By default primary key adds a clustered index on the other hand
foreign key does not automatically create an index, clustered or non-clustered.
You must manually create an index for foreign key.
SQL
Composite Key
A composite key is a combination of two or more columns in a table
that can be used to uniquely identify each row in the table when the columns
are combined uniqueness is guaranteed, but when it taken individually it does
not guarantee uniqueness.
Sometimes more than one attributes are needed to uniquely identify
an entity. A primary key that is made by the combination of more than one
attribute is known as a composite key.
In other words we can say that:
Composite key is a key which is the combination of more than one
field or column of a given table. It may be a candidate key or primary key.
Columns that make up the composite key can be of different data
types.
SQL Syntax to specify composite key:
1. CREATE TABLE TABLE_NAME
2. (COLUMN_1, DATA_TYPE_1,
3. COLUMN_2, DATA_TYPE_2,
4. ???
5. PRIMARY KEY (COLUMN_1, COLUMN_2, ...));
In all cases composite key created consist of COLUMN1 and COLUMN2.
MySQL:
1. CREATE TABLE SAMPLE_TABLE
2. (COL1 integer,
3. COL2 varchar(30),
4. COL3 varchar(50),
5. PRIMARY KEY (COL1, COL2));
MySQL:
1. CREATE TABLE SAMPLE_TABLE
2. (COL1 integer,
3. COL2 varchar(30),
4. COL3 varchar(50),
5. PRIMARY KEY (COL1, COL2));
Oracle:
1. CREATE TABLE SAMPLE_TABLE
2. CREATE TABLE SAMPLE_TABLE
3. (COL1 integer,
4. COL2 varchar(30),
5. COL3 varchar(50),
6. PRIMARY KEY (COL1, COL2));
SQL Server:
Let's see the Syntax for the select top statement:
1. CREATE TABLE SAMPLE_TABLE
2. (COL1 integer,
3. COL2 nvarchar(30),
4. COL3 nvarchar(50),
5. PRIMARY KEY (COL1, COL2));
Unique
Key in SQL
A unique key is a set of one or more than one fields/columns of a
table that uniquely identify a record in a database table.
You can say that it is little like primary key but it can accept
only one null value and it cannot have duplicate values.
The unique key and primary key both provide a guarantee for
uniqueness for a column or a set of columns.
There is an automatically defined unique key constraint within a
primary key constraint.
There may be many unique key constraints for one table, but only
one PRIMARY KEY constraint for one table.
SQL UNIQUE KEY constraint on CREATE TABLE:
If you want to create a UNIQUE constraint on the ?S_Id? column
when the ?students? table is created, use the following SQL syntax:
SQL Server / Oracle / MS Access:
(Defining a unique key constraint on single column):
1. CREATE TABLE students
2. (
3. S_Id int NOT NULL UNIQUE,
4. LastName varchar (255) NOT NULL,
5. FirstName varchar (255),
6. City varchar (255)
7. )
MySQL:
1. CREATE TABLE students
2. CREATE TABLE students
3. (
4. S_Id int NOT NULL,
5. LastName varchar (255) NOT NULL,
6. FirstName varchar (255),
7. City varchar (255),
8. UNIQUE (S_Id)
9. )
(Defining a unique key constraint on multiple columns):
MySQL / SQL Server / Oracle / MS Access:
1. CREATE TABLE students
2. (
3. S_Id int NOT NULL,
4. LastName varchar (255) NOT NULL,
5. FirstName varchar (255),
6. City varchar (255),
7. CONSTRAINT uc_studentId UNIQUE (S_Id, LastName)
8. )
SQL UNIQUE KEY constraint on ALTER TABLE:
If you want to create a unique constraint on ?S_Id? column when
the table is already created, you should use the following SQL syntax:
(Defining a unique key constraint on single column):
MySQL / SQL Server / Oracle / MS Access:
1. ALTER TABLE students
2. ADD UNIQUE (S_Id)
(Defining a unique key constraint on multiple columns):
MySQL / SQL Server / Oracle / MS Access:
1. ALTER TABLE students
2. ADD CONSTRAINT uc_StudentId UNIQUE (S_Id, LastName)
DROP SYNTAX FOR A FOREIGN KEY constraint:
If you want to drop a UNIQUE constraint, use the following SQL
syntax:
MySQL:
1. ALTER TABLE students
2. DROP INDEX uc_studentID
SQL Server / Oracle / MS Access:
1. ALTER TABLE students
2. DROP CONSTRAINT uc_studentID
Alternate
Key in SQL
Alternate key is a secondary key it can be simple to understand by
an example:
Let's take an example of student it can contain NAME, ROLL NO., ID
and CLASS.
Here ROLL NO. is primary key and rest of all columns like NAME, ID
and CLASS are alternate keys.
If a table has more than one candidate key, one of them will
become the primary key and rest of all are called alternate keys.
In simple words, you can say that any of the candidate key which
is not part of primary key is called an alternate key. So when we talk about
alternate key, the column may not be primary key but still it is a unique key
in the column.
An alternate key is just
a candidate key that has not been selected as the primary 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.
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
Enter 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.
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.
1.
Removes All rows
from a table.
2. Does
not require a WHERE clause,
so you can not filter rows while Truncating.
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.
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.
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.
No comments:
Post a Comment