These components invovlved in SQL resultgeneration are Query Dispatcher, Optimization Engines, Classic Query Engine and SQL Query Engine, etc. Classic query engine handles all non-SQL queries but SQL query engine won't handle logical files
DDL Commands: CREATE, ALTER, DROP
DML Commands: SELECT, INSERT, UPDATE, DELETE
DCL Commands: GRANT , REVOKE
Commonly used constraints available in SQL:
NOT NULL Constraint: Ensures that a column cannot have NULL value.
DEFAULT Constraint: Provides a default value for a column when none is specified.
UNIQUE Constraint: Ensures that all values in a column are different.
PRIMARY Key: Uniquely identified each rows/records in a database table.
FOREIGN Key: Uniquely identified a rows/records in any another database table.
CHECK Constraint: The CHECK constraint ensures that all values in a column satisfy certain conditions.
INDEX: Use to create and retrieve data from the database very quickly.
Following categories of the data integrity exist with each RDBMS:
Entity Integrity: There are no duplicate rows in a table.
Domain Integrity: Enforces valid entries for a given column by restricting the type, the format, or the range of values.
Referential integrity: Rows cannot be deleted, which are used by other records.
User-Defined Integrity: Enforces some specific business rules that do not fall into entity, domain or referential integrity.
Syntax:
Important point to be noted is that SQL is case insensitive, which means SELECT and select have same meaning in SQL statements, but MySQL makes difference in table names.
SQL SELECT Statement:
SELECT column1, column2....columnN
FROM table_name;
SQL DISTINCT Clause:
SELECT DISTINCT column1, column2....columnN
FROM table_name;
SQL WHERE Clause:
SELECT column1, column2....columnN
FROM table_name
WHERE CONDITION;
SQL AND/OR Clause:
SELECT column1, column2....columnN
FROM table_name
WHERE CONDITION-1 {AND|OR} CONDITION-2;
SQL IN Clause:
SELECT column1, column2....columnN
FROM table_name
WHERE column_name IN (val-1, val-2,...val-N);
SQL BETWEEN Clause:
SELECT column1, column2....columnN
FROM table_name
WHERE column_name BETWEEN val-1 AND val-2;
SQL LIKE Clause:
SELECT column1, column2....columnN
FROM table_name
WHERE column_name LIKE { PATTERN };
SQL ORDER BY Clause:
SELECT column1, column2....columnN
FROM table_name
WHERE CONDITION
ORDER BY column_name {ASC|DESC};
SQL GROUP BY Clause:
SELECT SUM(column_name)
FROM table_name
WHERE CONDITION
GROUP BY column_name;
SQL COUNT Clause:
SELECT COUNT(column_name)
FROM table_name
WHERE CONDITION;
SQL HAVING Clause:
SELECT SUM(column_name)
FROM table_name
WHERE CONDITION
GROUP BY column_name
HAVING (arithematic function condition);
SQL CREATE TABLE Statement:
CREATE TABLE table_name(
column1 datatype,
column2 datatype,
column3 datatype,
.....
columnN datatype,
PRIMARY KEY( one or more columns )
);
SQL DROP TABLE Statement:
DROP TABLE table_name;
SQL CREATE INDEX Statement :
CREATE UNIQUE INDEX index_name
ON table_name ( column1, column2,...columnN);
SQL DROP INDEX Statement :
ALTER TABLE table_name
DROP INDEX index_name;
SQL DESC Statement :
DESC table_name;
SQL TRUNCATE TABLE Statement:
TRUNCATE TABLE table_name;
SQL ALTER TABLE Statement:
ALTER TABLE table_name {ADD|DROP|MODIFY} column_name {data_ype};
SQL ALTER TABLE Statement (Rename) :
ALTER TABLE table_name RENAME TO new_table_name;
SQL INSERT INTO Statement:
INSERT INTO table_name( column1, column2....columnN)
VALUES ( value1, value2....valueN);
SQL UPDATE Statement:
UPDATE table_name
SET column1 = value1, column2 = value2....columnN=valueN
[ WHERE CONDITION ];
SQL DELETE Statement:
DELETE FROM table_name
WHERE {CONDITION};
SQL CREATE DATABASE Statement:
CREATE DATABASE database_name;
SQL DROP DATABASE Statement:
DROP DATABASE database_name;
SQL USE Statement:
USE database_name;
SQL COMMIT Statement:
COMMIT;
SQL ROLLBACK Statement:
ROLLBACK;
SQL Data types and Operators //Refer to tutorials point
SQL - Expressions
SQL - Boolean Expressions:
SELECT column1, column2, columnN
FROM table_name
WHERE SINGLE VALUE MATCHTING EXPRESSION;
SQL - Numeric Expression:
SELECT numerical_expression as OPERATION_NAME
[FROM table_name
WHERE CONDITION] ;
SQL> SELECT COUNT(*) AS "RECORDS" FROM CUSTOMERS;
SQL - Date Expressions:
SQL> SELECT CURRENT_TIMESTAMP;
SQL> SELECT GETDATE();
More Syntax:
CREATE DATABASE DatabaseName;
DROP DATABASE DatabaseName;
USE DatabaseName;
SHOW DATABASES;
CREATE TABLE table_name(
column1 datatype,
column2 datatype,
column3 datatype,
.....
columnN datatype,
PRIMARY KEY( one or more columns )
);
DESC TABLE;
DROP TABLE table_name;
INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)]
VALUES (value1, value2, value3,...valueN);
INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);
INSERT INTO first_table_name [(column1, column2, ... columnN)]
SELECT column1, column2, ...columnN
FROM second_table_name
[WHERE condition];
SELECT column1, column2, columnN FROM table_name;
SELECT * FROM table_name;
SELECT column1, column2, columnN
FROM table_name
WHERE [condition]
//specify a condition using comparison or logical operators like >, <, =, LIKE, NOT, etc
SELECT column1, column2, columnN
FROM table_name
WHERE [condition1] AND [condition2]...AND [conditionN];
SELECT column1, column2, columnN
FROM table_name
WHERE [condition1] OR [condition2]...OR [conditionN]
UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];
DELETE FROM table_name
WHERE [condition];
DELETE FROM table_name; //ALL RECORDS
SELECT FROM table_name
WHERE column LIKE '%XXXX%'
SELECT FROM table_name
WHERE column LIKE '_XXXX_'
//The percent sign represents zero, one, or multiple characters. The underscore represents a single number or character.
SELECT TOP number|percent column_name(s)
FROM table_name
WHERE [condition]
//The SQL TOP clause is used to fetch a TOP N number or X percent records from a table.All the databases do not support TOP clause. For example MySQL supports LIMIT clause to fetch limited number of records and Oracle uses ROWNUM to fetch limited number of records used after WHERE
SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];
//The SQL ORDER BY clause is used to sort the data in ascending or descending order, based on one or more columns. Some database sorts query results in ascending order by default. To fetch the rows with own preferred order, the SELECT query would as follows(ordered according to delhi, bhopal, kota etc):
SQL> SELECT * FROM CUSTOMERS
ORDER BY (CASE ADDRESS
WHEN 'DELHI' THEN 1
WHEN 'BHOPAL' THEN 2
WHEN 'KOTA' THEN 3
WHEN 'AHMADABAD' THEN 4
WHEN 'MP' THEN 5
ELSE 100 END) ASC, ADDRESS DESC;
//The SQL GROUP BY clause is used in collaboration with the SELECT statement to arrange identical data into groups. The GROUP BY clause follows the WHERE clause in a SELECT statement and precedes the ORDER BY clause. This is useful as it allows you to summarize information. For instance you can use aggregate functions such as SUM and AVERAGE to calculate values.
SELECT column1, column2
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2
ORDER BY column1, column2
//The SQL DISTINCT keyword is used in conjunction with SELECT statement to eliminate all the duplicate records and fetching only unique records.
SELECT DISTINCT column1, column2,.....columnN
FROM table_name
WHERE [condition]
Advanced SQL Syntax:
//Constraints can be specified when a table is created with the CREATE TABLE statement or you can use ALTER TABLE statement to create constraints even after the table is created.
ALTER TABLE tablename DROP CONSTRAINT columnname;
ALTER TABLE tablename DROP PRIMARY KEY;
JOINS:
The SQL Joins clause is used to combine records from two or more tables in a database. A JOIN is a means for combining fields from two tables by using values common to each. Several operators can be used to join tables, such as =, <, >, <>, <=, >=, !=, BETWEEN, LIKE, and NOT; they can all be used to join tables.
SELECT ID, NAME, AGE, AMOUNT
FROM CUSTOMERS, ORDERS
WHERE CUSTOMERS.ID = ORDERS.CUSTOMER_ID;
There are different types of joins available in SQL:
INNER JOIN: returns rows when there is a match in both tables.
LEFT JOIN: returns all rows from the left table, even if there are no matches in the right table.
RIGHT JOIN: returns all rows from the right table, even if there are no matches in the left table.
FULL JOIN: returns rows when there is a match in one of the tables.
SELF JOIN: is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.
CARTESIAN JOIN: returns the Cartesian product of the sets of records from the two or more joined tables.
UNION:
The SQL UNION clause/operator is used to combine the results of two or more SELECT statements without returning any duplicate rows. To use UNION, each SELECT must have the same number of columns selected, the same number of column expressions, the same data type, and have them in the same ordER
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
UNION
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
The UNION ALL operator is used to combine the results of two SELECT statements including duplicate rows. use UNION ALL instead of UNION in above syntax
SQL INTERSECT Clause: is used to combine two SELECT statements, but returns rows only from the first SELECT statement that are identical to a row in the second SELECT statement.
SQL EXCEPT Clause : combines two SELECT statements and returns rows from the first SELECT statement that are not returned by the second SELECT statement.
Alias:
The use of table aliases means to rename a table in a particular SQL statement. The renaming is a temporary change and the actual table name does not change in the database.
SELECT column1, column2....
FROM table_name AS alias_name
WHERE [condition];
SELECT column_name AS alias_name
FROM table_name
WHERE [condition];
Indexes:
CREATE INDEX index_name ON table_name;
CREATE INDEX index_name
on table_name (column1, column2);
DROP INDEX index_name;
Implicit indexes are indexes that are automatically created by the database server when an object is created. Indexes are automatically created for primary key constraints and unique constraints.
ALTER TABLE:
ALTER TABLE table_name ADD column_name datatype;
ALTER TABLE table_name DROP COLUMN column_name;
ALTER TABLE table_name MODIFY COLUMN column_name datatype;
ALTER TABLE table_name MODIFY column_name datatype NOT NULL;
ALTER TABLE table_name
ADD CONSTRAINT MyUniqueConstraint UNIQUE(column1, column2...);
ALTER TABLE table_name
ADD CONSTRAINT MyUniqueConstraint CHECK (CONDITION);
ALTER TABLE table_name
ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...);
ALTER TABLE table_name
DROP CONSTRAINT MyUniqueConstraint;
TRUNCATE TABLE:
The SQL TRUNCATE TABLE command is used to delete complete data from an existing table.
TRUNCATE TABLE table_name;
Views:
A view is nothing more than a SQL statement that is stored in the database with an associated name.
CREATE VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition]
WITH CHECK OPTION;
DROP VIEW view_name;
The purpose of the WITH CHECK OPTION is to ensure that all UPDATE and INSERTs satisfy the condition(s) in the view definition. You can use view name instead of table name for select, update, insert, delete. However these operations require certain conditions be fulfilled
Having Clause:
The HAVING clause enables you to specify conditions that filter which group results appear in the final results.The WHERE clause places conditions on the selected columns, whereas the HAVING clause places conditions on groups created by the GROUP BY clause.
SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2
Transactions:
The COMMIT command is the transactional command used to save changes invoked by a transaction to the database.
COMMIT;
The ROLLBACK command is the transactional command used to undo transactions that have not already been saved to the database.
ROLLBACK;
A SAVEPOINT is a point in a transaction when you can roll the transaction back to a certain point without rolling back the entire transaction.
SAVEPOINT SAVEPOINT_NAME;
ROLLBACK TO SAVEPOINT_NAME;
The RELEASE SAVEPOINT command is used to remove a SAVEPOINT that you have created.
RELEASE SAVEPOINT SAVEPOINT_NAME;
The SET TRANSACTION command can be used to initiate a database transaction.
SET TRANSACTION [ READ WRITE | READ ONLY ];
Date Functions: Refer to tutorials point
Temporary Tables:
The most important thing that should be known for temporary tables is that they will be deleted when the current client session terminates.utomatically when the script finishes executing. If you are connected to the MySQL database server through the MySQL client program, then the temporary table will exist until you close the client or manually destroy the table.
CREATE TEMPORARY TABLE temporarytablename(
column1 datatype,
column2 datatype,
column3 datatype,
.....
columnN datatype,
PRIMARY KEY( one or more columns )
);
DROP TABLE temporarytable;
Sub Queries:
A Subquery or Inner query or Nested query is a query within another SQL query and embedded within the WHERE clause.A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators like =, <, >, >=, <=, IN, BETWEEN etc.
SELECT column_name [, column_name ]
FROM table1 [, table2 ]
WHERE column_name OPERATOR
(SELECT column_name [, column_name ]
FROM table1 [, table2 ]
[WHERE])
INSERT INTO table_name [ (column1 [, column2 ]) ]
SELECT [ *|column1 [, column2 ]
FROM table1 [, table2 ]
[ WHERE VALUE OPERATOR ]
UPDATE table
SET column_name = new_value
[ WHERE OPERATOR [ VALUE ]
(SELECT COLUMN_NAME
FROM TABLE_NAME)
[ WHERE) ]
DELETE FROM TABLE_NAME
[ WHERE OPERATOR [ VALUE ]
(SELECT COLUMN_NAME
FROM TABLE_NAME)
[ WHERE) ]
Sequences:
CREATE TABLE tablename(
column1 datatype AUTO_INCREMENT,
column2 datatype,
column3 datatype,
PRIMARY KEY (column1)
);
There may be a case when you have deleted many records from a table and you want to resequence all the records. If you determine that resequencing an AUTO_INCREMENT column is unavoidable, the way to do it is to drop the column from the table, then add it again.
ALTER TABLE tablename DROP column1;
mysql> ALTER TABLE tablename
-> ADD column1 INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
-> ADD PRIMARY KEY (column1);
By default, MySQL will start sequence from 1 but you can specify any other number as well at the time of table creation using AUTO_INCREMENT = number(10 or 100)
DDL Commands: CREATE, ALTER, DROP
DML Commands: SELECT, INSERT, UPDATE, DELETE
DCL Commands: GRANT , REVOKE
Commonly used constraints available in SQL:
NOT NULL Constraint: Ensures that a column cannot have NULL value.
DEFAULT Constraint: Provides a default value for a column when none is specified.
UNIQUE Constraint: Ensures that all values in a column are different.
PRIMARY Key: Uniquely identified each rows/records in a database table.
FOREIGN Key: Uniquely identified a rows/records in any another database table.
CHECK Constraint: The CHECK constraint ensures that all values in a column satisfy certain conditions.
INDEX: Use to create and retrieve data from the database very quickly.
Following categories of the data integrity exist with each RDBMS:
Entity Integrity: There are no duplicate rows in a table.
Domain Integrity: Enforces valid entries for a given column by restricting the type, the format, or the range of values.
Referential integrity: Rows cannot be deleted, which are used by other records.
User-Defined Integrity: Enforces some specific business rules that do not fall into entity, domain or referential integrity.
Syntax:
Important point to be noted is that SQL is case insensitive, which means SELECT and select have same meaning in SQL statements, but MySQL makes difference in table names.
SQL SELECT Statement:
SELECT column1, column2....columnN
FROM table_name;
SQL DISTINCT Clause:
SELECT DISTINCT column1, column2....columnN
FROM table_name;
SQL WHERE Clause:
SELECT column1, column2....columnN
FROM table_name
WHERE CONDITION;
SQL AND/OR Clause:
SELECT column1, column2....columnN
FROM table_name
WHERE CONDITION-1 {AND|OR} CONDITION-2;
SQL IN Clause:
SELECT column1, column2....columnN
FROM table_name
WHERE column_name IN (val-1, val-2,...val-N);
SQL BETWEEN Clause:
SELECT column1, column2....columnN
FROM table_name
WHERE column_name BETWEEN val-1 AND val-2;
SQL LIKE Clause:
SELECT column1, column2....columnN
FROM table_name
WHERE column_name LIKE { PATTERN };
SQL ORDER BY Clause:
SELECT column1, column2....columnN
FROM table_name
WHERE CONDITION
ORDER BY column_name {ASC|DESC};
SQL GROUP BY Clause:
SELECT SUM(column_name)
FROM table_name
WHERE CONDITION
GROUP BY column_name;
SQL COUNT Clause:
SELECT COUNT(column_name)
FROM table_name
WHERE CONDITION;
SQL HAVING Clause:
SELECT SUM(column_name)
FROM table_name
WHERE CONDITION
GROUP BY column_name
HAVING (arithematic function condition);
SQL CREATE TABLE Statement:
CREATE TABLE table_name(
column1 datatype,
column2 datatype,
column3 datatype,
.....
columnN datatype,
PRIMARY KEY( one or more columns )
);
SQL DROP TABLE Statement:
DROP TABLE table_name;
SQL CREATE INDEX Statement :
CREATE UNIQUE INDEX index_name
ON table_name ( column1, column2,...columnN);
SQL DROP INDEX Statement :
ALTER TABLE table_name
DROP INDEX index_name;
SQL DESC Statement :
DESC table_name;
SQL TRUNCATE TABLE Statement:
TRUNCATE TABLE table_name;
SQL ALTER TABLE Statement:
ALTER TABLE table_name {ADD|DROP|MODIFY} column_name {data_ype};
SQL ALTER TABLE Statement (Rename) :
ALTER TABLE table_name RENAME TO new_table_name;
SQL INSERT INTO Statement:
INSERT INTO table_name( column1, column2....columnN)
VALUES ( value1, value2....valueN);
SQL UPDATE Statement:
UPDATE table_name
SET column1 = value1, column2 = value2....columnN=valueN
[ WHERE CONDITION ];
SQL DELETE Statement:
DELETE FROM table_name
WHERE {CONDITION};
SQL CREATE DATABASE Statement:
CREATE DATABASE database_name;
SQL DROP DATABASE Statement:
DROP DATABASE database_name;
SQL USE Statement:
USE database_name;
SQL COMMIT Statement:
COMMIT;
SQL ROLLBACK Statement:
ROLLBACK;
SQL Data types and Operators //Refer to tutorials point
SQL - Expressions
SQL - Boolean Expressions:
SELECT column1, column2, columnN
FROM table_name
WHERE SINGLE VALUE MATCHTING EXPRESSION;
SQL - Numeric Expression:
SELECT numerical_expression as OPERATION_NAME
[FROM table_name
WHERE CONDITION] ;
SQL> SELECT COUNT(*) AS "RECORDS" FROM CUSTOMERS;
SQL - Date Expressions:
SQL> SELECT CURRENT_TIMESTAMP;
SQL> SELECT GETDATE();
More Syntax:
CREATE DATABASE DatabaseName;
DROP DATABASE DatabaseName;
USE DatabaseName;
SHOW DATABASES;
CREATE TABLE table_name(
column1 datatype,
column2 datatype,
column3 datatype,
.....
columnN datatype,
PRIMARY KEY( one or more columns )
);
DESC TABLE;
DROP TABLE table_name;
INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)]
VALUES (value1, value2, value3,...valueN);
INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);
INSERT INTO first_table_name [(column1, column2, ... columnN)]
SELECT column1, column2, ...columnN
FROM second_table_name
[WHERE condition];
SELECT column1, column2, columnN FROM table_name;
SELECT * FROM table_name;
SELECT column1, column2, columnN
FROM table_name
WHERE [condition]
//specify a condition using comparison or logical operators like >, <, =, LIKE, NOT, etc
SELECT column1, column2, columnN
FROM table_name
WHERE [condition1] AND [condition2]...AND [conditionN];
SELECT column1, column2, columnN
FROM table_name
WHERE [condition1] OR [condition2]...OR [conditionN]
UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];
DELETE FROM table_name
WHERE [condition];
DELETE FROM table_name; //ALL RECORDS
SELECT FROM table_name
WHERE column LIKE '%XXXX%'
SELECT FROM table_name
WHERE column LIKE '_XXXX_'
//The percent sign represents zero, one, or multiple characters. The underscore represents a single number or character.
SELECT TOP number|percent column_name(s)
FROM table_name
WHERE [condition]
//The SQL TOP clause is used to fetch a TOP N number or X percent records from a table.All the databases do not support TOP clause. For example MySQL supports LIMIT clause to fetch limited number of records and Oracle uses ROWNUM to fetch limited number of records used after WHERE
SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];
//The SQL ORDER BY clause is used to sort the data in ascending or descending order, based on one or more columns. Some database sorts query results in ascending order by default. To fetch the rows with own preferred order, the SELECT query would as follows(ordered according to delhi, bhopal, kota etc):
SQL> SELECT * FROM CUSTOMERS
ORDER BY (CASE ADDRESS
WHEN 'DELHI' THEN 1
WHEN 'BHOPAL' THEN 2
WHEN 'KOTA' THEN 3
WHEN 'AHMADABAD' THEN 4
WHEN 'MP' THEN 5
ELSE 100 END) ASC, ADDRESS DESC;
//The SQL GROUP BY clause is used in collaboration with the SELECT statement to arrange identical data into groups. The GROUP BY clause follows the WHERE clause in a SELECT statement and precedes the ORDER BY clause. This is useful as it allows you to summarize information. For instance you can use aggregate functions such as SUM and AVERAGE to calculate values.
SELECT column1, column2
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2
ORDER BY column1, column2
//The SQL DISTINCT keyword is used in conjunction with SELECT statement to eliminate all the duplicate records and fetching only unique records.
SELECT DISTINCT column1, column2,.....columnN
FROM table_name
WHERE [condition]
Advanced SQL Syntax:
//Constraints can be specified when a table is created with the CREATE TABLE statement or you can use ALTER TABLE statement to create constraints even after the table is created.
ALTER TABLE tablename DROP CONSTRAINT columnname;
ALTER TABLE tablename DROP PRIMARY KEY;
JOINS:
The SQL Joins clause is used to combine records from two or more tables in a database. A JOIN is a means for combining fields from two tables by using values common to each. Several operators can be used to join tables, such as =, <, >, <>, <=, >=, !=, BETWEEN, LIKE, and NOT; they can all be used to join tables.
SELECT ID, NAME, AGE, AMOUNT
FROM CUSTOMERS, ORDERS
WHERE CUSTOMERS.ID = ORDERS.CUSTOMER_ID;
There are different types of joins available in SQL:
INNER JOIN: returns rows when there is a match in both tables.
LEFT JOIN: returns all rows from the left table, even if there are no matches in the right table.
RIGHT JOIN: returns all rows from the right table, even if there are no matches in the left table.
FULL JOIN: returns rows when there is a match in one of the tables.
SELF JOIN: is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.
CARTESIAN JOIN: returns the Cartesian product of the sets of records from the two or more joined tables.
UNION:
The SQL UNION clause/operator is used to combine the results of two or more SELECT statements without returning any duplicate rows. To use UNION, each SELECT must have the same number of columns selected, the same number of column expressions, the same data type, and have them in the same ordER
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
UNION
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
The UNION ALL operator is used to combine the results of two SELECT statements including duplicate rows. use UNION ALL instead of UNION in above syntax
SQL INTERSECT Clause: is used to combine two SELECT statements, but returns rows only from the first SELECT statement that are identical to a row in the second SELECT statement.
SQL EXCEPT Clause : combines two SELECT statements and returns rows from the first SELECT statement that are not returned by the second SELECT statement.
Alias:
The use of table aliases means to rename a table in a particular SQL statement. The renaming is a temporary change and the actual table name does not change in the database.
SELECT column1, column2....
FROM table_name AS alias_name
WHERE [condition];
SELECT column_name AS alias_name
FROM table_name
WHERE [condition];
Indexes:
CREATE INDEX index_name ON table_name;
CREATE INDEX index_name
on table_name (column1, column2);
DROP INDEX index_name;
Implicit indexes are indexes that are automatically created by the database server when an object is created. Indexes are automatically created for primary key constraints and unique constraints.
ALTER TABLE:
ALTER TABLE table_name ADD column_name datatype;
ALTER TABLE table_name DROP COLUMN column_name;
ALTER TABLE table_name MODIFY COLUMN column_name datatype;
ALTER TABLE table_name MODIFY column_name datatype NOT NULL;
ALTER TABLE table_name
ADD CONSTRAINT MyUniqueConstraint UNIQUE(column1, column2...);
ALTER TABLE table_name
ADD CONSTRAINT MyUniqueConstraint CHECK (CONDITION);
ALTER TABLE table_name
ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...);
ALTER TABLE table_name
DROP CONSTRAINT MyUniqueConstraint;
TRUNCATE TABLE:
The SQL TRUNCATE TABLE command is used to delete complete data from an existing table.
TRUNCATE TABLE table_name;
Views:
A view is nothing more than a SQL statement that is stored in the database with an associated name.
CREATE VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition]
WITH CHECK OPTION;
DROP VIEW view_name;
The purpose of the WITH CHECK OPTION is to ensure that all UPDATE and INSERTs satisfy the condition(s) in the view definition. You can use view name instead of table name for select, update, insert, delete. However these operations require certain conditions be fulfilled
Having Clause:
The HAVING clause enables you to specify conditions that filter which group results appear in the final results.The WHERE clause places conditions on the selected columns, whereas the HAVING clause places conditions on groups created by the GROUP BY clause.
SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2
Transactions:
The COMMIT command is the transactional command used to save changes invoked by a transaction to the database.
COMMIT;
The ROLLBACK command is the transactional command used to undo transactions that have not already been saved to the database.
ROLLBACK;
A SAVEPOINT is a point in a transaction when you can roll the transaction back to a certain point without rolling back the entire transaction.
SAVEPOINT SAVEPOINT_NAME;
ROLLBACK TO SAVEPOINT_NAME;
The RELEASE SAVEPOINT command is used to remove a SAVEPOINT that you have created.
RELEASE SAVEPOINT SAVEPOINT_NAME;
The SET TRANSACTION command can be used to initiate a database transaction.
SET TRANSACTION [ READ WRITE | READ ONLY ];
Date Functions: Refer to tutorials point
Temporary Tables:
The most important thing that should be known for temporary tables is that they will be deleted when the current client session terminates.utomatically when the script finishes executing. If you are connected to the MySQL database server through the MySQL client program, then the temporary table will exist until you close the client or manually destroy the table.
CREATE TEMPORARY TABLE temporarytablename(
column1 datatype,
column2 datatype,
column3 datatype,
.....
columnN datatype,
PRIMARY KEY( one or more columns )
);
DROP TABLE temporarytable;
Sub Queries:
A Subquery or Inner query or Nested query is a query within another SQL query and embedded within the WHERE clause.A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators like =, <, >, >=, <=, IN, BETWEEN etc.
SELECT column_name [, column_name ]
FROM table1 [, table2 ]
WHERE column_name OPERATOR
(SELECT column_name [, column_name ]
FROM table1 [, table2 ]
[WHERE])
INSERT INTO table_name [ (column1 [, column2 ]) ]
SELECT [ *|column1 [, column2 ]
FROM table1 [, table2 ]
[ WHERE VALUE OPERATOR ]
UPDATE table
SET column_name = new_value
[ WHERE OPERATOR [ VALUE ]
(SELECT COLUMN_NAME
FROM TABLE_NAME)
[ WHERE) ]
DELETE FROM TABLE_NAME
[ WHERE OPERATOR [ VALUE ]
(SELECT COLUMN_NAME
FROM TABLE_NAME)
[ WHERE) ]
Sequences:
CREATE TABLE tablename(
column1 datatype AUTO_INCREMENT,
column2 datatype,
column3 datatype,
PRIMARY KEY (column1)
);
There may be a case when you have deleted many records from a table and you want to resequence all the records. If you determine that resequencing an AUTO_INCREMENT column is unavoidable, the way to do it is to drop the column from the table, then add it again.
ALTER TABLE tablename DROP column1;
mysql> ALTER TABLE tablename
-> ADD column1 INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
-> ADD PRIMARY KEY (column1);
By default, MySQL will start sequence from 1 but you can specify any other number as well at the time of table creation using AUTO_INCREMENT = number(10 or 100)
No comments:
Post a Comment