| SQL Type |
MySql Query |
MS Sql Query |
Oracle Query |
MongoBD Query |
| DDL(Data Definition Language) |
create table
create index
|
CREATE TABLE CUSTOMERS(
ID INT NOT NULL AUTO_INCREMENT,
NAME VARCHAR (20) NOT NULL UNIQUE,
AGE INT DEFAULT '0',
ADDRESS TEXT ,
SALARY DECIMAL (18, 2),
person_id int CHECK(person_id > 0),
is_active tinyint default 0 CHECK(is_active in(0,1)),
PRIMARY KEY (ID),
CONSTRAINT FK_customer_person FOREIGN KEY (person_id) REFERENCES Person(id)
);
CREATE INDEX/UNIQUE INDEX name_index ON person (name,mobile);
|
|
|
|
drop table/DATABASE
drop index
|
DROP TABLE CUSTOMERS;
DROP DATABASE SQL_TEST;
DROP INDEX person.name_index
DROP INDEX name_index ON person
DROP INDEX name_index
|
|
|
|
alter table
alter procedure
|
ALTER TABLE person ADD is_active tinyint default 0;
ALTER TABLE person MODIFY COLUMN/ALTER COLUMN/MODIFY name tinyint default 0;
ALTER TABLE person DROP COLUMN is_active;
|
|
|
|
TRUNCATE
(TRUNCATE command removes all the records from a table. But this command will not destroy the table's structure)
|
TRUNCATE TABLE table_name
|
|
|
|
COMMENT
|
|
|
|
|
RENAME
|
RENAME TABLE old_table_name to new_table_name
|
|
|
|
| DML(Data Manipulation Language) |
SELECT
|
|
|
|
|
INSERT
|
INSERT INTO student VALUES(101, 'Adam', 15);
or
INSERT INTO student(id, name) values(102, 'Alex');
|
|
|
|
UPDATE
|
UPDATE student SET name='Abhi', age=17 where s_id=103;
|
|
|
|
DELETE
|
DELETE FROM student;
or
DELETE FROM student WHERE s_id=103;
|
|
|
|
| DCL(Data Control Language) |
|
Data Control Language(DCL) is used to control privileges in Database. To perform any operation in the database, such as for creating tables, sequences or views, a user needs privileges. Privileges are of two types
In DCL we have two commands
- System: This includes permissions for creating session, table, etc and all types of other system privileges.
- Object: This includes permissions for any command or query to perform any operation on the database tables.
- GRANT: Used to provide any user access privileges or other priviliges for the database.
- REVOKE: Used to take back permissions from any user.
|
GRANT CREATE SESSION TO username;
GRANT CREATE TABLE TO username;
ALTER USER username QUOTA UNLIMITED ON SYSTEM;
GRANT sysdba TO username
GRANT CREATE ANY TABLE TO username
GRANT DROP ANY TABLE TO username
REVOKE CREATE TABLE FROM username
|
|
|
|
| TCL(transaction Control Language) |
COMMIT
COMMIT command is used to permanently save any transaction into the database.
ROLLBACK
This command restores the database to last commited state. It is also used with SAVEPOINT command to jump to a savepoint in an ongoing transaction.
SAVEPOINT
SAVEPOINT command is used to temporarily save a transaction so that you can rollback to that point whenever required.
|
INSERT INTO class VALUES(5, 'Rahul');
COMMIT;
UPDATE class SET name = 'Abhijit' WHERE id = '5';
SAVEPOINT A;
INSERT INTO class VALUES(6, 'Chris');
SAVEPOINT B;
INSERT INTO class VALUES(7, 'Bravo');
SAVEPOINT C;
SELECT * FROM class;
ROLLBACK TO B;
SELECT * FROM class;
ROLLBACK TO A;
SELECT * FROM class;
|
|
|
|
| analyze |
EXPLAIN/ANALYZE SELECT * FROM CUSTOMERS; |
