.ico)
SQL Commands
 |
| SQL Commands:DQL,DDL,DML,DCL,TCL |
SQL Commands are mainly divided into five types of categories there are DQL,DDL,DML,DCL,TCL lets we see in details.
Introduction
Welcome to all who want learn and to become professional in SQL.This tutorial is special made to our our students and who interested to learn SQL with us.
This SQL tutorial provide to all of you basic SQL commands.here we see all the SQL commands in details with there syntax.
SQL Stands for (Structured Query Language) and it is help to users and used to store the records,creating tables,update tables,delete records,drop the recordes,insert the records,by using SQL we change the table and column name.
Also SQL gives permission to Grant and Revoke permission to user so for that we have to understand the concept and syntax/commands of SQL SO let's see.
"SQL is structured query language and it is used to storing,manipulating and retrieving data which is stored in relational database"
NOTE:-SQL is not case sensitive language i.e select is same as SELECT you write select or SELECT both are valid.
Use semicolon after SQL Statements
It important to understand the semicolon important in SQL.Basically semicolon is used to separate each SQL statements and it is standard and good approach.semicolon is used to denote the end of a statements and helps to database engine to understand the each individual queries or commands will executing.
But some database not require the semicolon but by practise its being good approach to not raise any single issue.
Therefore best way to give semicolon at the end of statement and its also a good approach.
Following are the types SQL commands
SQL Commands are mainly divided into five types of categories there are DQL,DDL,DML,DCL,TCL lets we see in details.
SQL Commands1.DQL1) DQL is first type of SQL command where full form of DQL is (Data Query Language). 2) Select command is come under DQL. 3) DDL is subset of SQL data query language is used to retrieve or fetch the data from database. A.SELECT Command/Select QueryIn DQL command select command is come in SQL Select command is used to retrieve or fetch the data from database whatever data is fetch from database is return in the form of result set. Syntax SELECT *FROM table_name; Example SELECT Customer name,city FROM customer; note:-Here * means column names from the table where you wants fetch the records 2.DDL1) DDL stands for Data Definition Language. 2) DDL change the structure of table by using following commands like it can be create,alter,drop,truncate and rename the table. 3) In DDL what we change is permanently save in database that means it is auto-committed. 4) Following are five commands comes under DDL
A) CREATE Use 1.CREATE command is used to create a table 2.By using create command we can create database also For CREATE TABLE Syntax CREATE TABLE Table_Name (column 1 datatype, column 2 datatype, column 3 datatype........); Example CREATE TABLE employe (employe id int, firstname varchar(20), address varchar(20)); For CREATE DATABASE Syntax CREATE DATABASE database_name; Example CREATE DATABASE facebook; B) ALTER Use 1.ALTER Command comes under ddl 2.ALTER command is used to add new column in the table 3.By using alter command we also drop the existing column from table 4.By using alter command also use to modify the data from existing table. For Add new column in table Syntax ALTER TABLE table_name ADD column_name DATATYPE; Example ALTER TABLE customer ADD column_name varchar(20); For Drop the existing column from table Syntax ALTER TABLE table_name DROP column_name; Example ALTER TABLE customer DROP Email ; For modify the Data From Existing Table. Syntax ALTER TABLE table_name MODIFY column_name DATATYPE; Example ALTER TABLE customer MODIFY column_name ; c) DROP USE 1.DROP command is used to drop the database. 2.DROP the existing table. 3.DROP command Rollback is not possible means what we can delete we can't get it back. 4.DROP Command is used to delete all records with table(structure). For DROP database Syntax DROP database database_name; Example DROP database facebook; For DROP Table Syntax DROP TABLE table_name; Example DROP table customer; D) TRUNCATE USE 1.TRUNCATE command is used to delete the data from table structure of table remains same. Syntax TRUNCATE TABLE table_name; Example TRUNCATE table customer; E) RENAME USE 1.RENAME command is used to rename the existing column and existing table. RENAME the existing column Syntax exec sp_rename 'table_name.old column_name', 'new column_name', 'column' ; Example exec sp_rename customer.customer name', 'first name', 'column' ; RENAME the existing Table Syntax exec sp_rename 'old table_name', 'new table_name' ; Example exec sp_rename 'customer', 'employee' ; 3.DML1) DML stands for data manipulation language. 2) DML Commands are
3) DML is used to modify database/table 4) By using INSERT,UPDATE,DELETE Commands from dml we do all changes in database. A) INSERT USE 1. INSERT command used to insert the new records/new value/new data in table. 2.INSERT having two syntax 1.Syntax ( if we want to add column name with value) INSERT INTO table_name; (column 1,column 2,column 3,......) VALUES (value 1, value 2, value 3); Example INSERT INTO Customer; (first name,middle name, address) VALUES (trisha,sanjay, abc); 1.Syntax ( if we want to add only values) INSERT INTO table_name; VALUES (value 1, value 2, value 3........); Example INSERT INTO Customer; VALUES (trisha,sanjay, abc); B) UPDATE USE 1.UPDATE command is used to update the existing data from table. Syntax UPDATE table_name; SET (column=value 1,column 2=value 2....) WHERE condition; Example UPDATE customer; SET first name=sanket,address=abc WHERE customer id=1; A) DELETE USE 1.DELETE command is used to delete the existing data from table. 2.Rollback is possible in delete. Syntax DELETE FROM table_name; WHERE condition; Example DELETE FROM customer; WHERE customer name='manisha'; USE By using DELETE command it is possible to delete all records without deleting the table Syntax DELETE FROM table_name; Example DELETE FROM customer; 4.DCL1.DCL stand for Data Control Language 2.DCL have authority to gives permission to user and also take back given permission from user. 3.Following are the DCL commands
A) GRANT USE By using GRANT command it is possible to give access permission to user. Syntax GRANT select,update on my_table to some_user,another_user; B) REVOKE USE By using REVOKE command it is possible to give access permission back from user. Syntax REVOKE select,update on my_table to from user 1, user 2; 5.TCL1.TCL stands for Transaction Control Language. 2.TCL commands used with DML commands like insert,update,delete. 3.TCL is auto committed 4.TCL commands
For that we need Begin transaction is used to start the transaction Begin transaction <write statement> A) COMMIT USE By using COMMIT command transactions is permanently save. Syntax Begin transaction <write statement> Example Begin transaction <write statement> commit; B) ROLLBACK USE By using COMMIT command is used to cancel the transactions. Syntax Begin transaction rollback; People also ask this QuestionsViewers This software testing tutorial is specially for beginners and professionals and students and who want to learn testing from basic. Comments Please drop your comment and send me your valuable response. |
.ico){kind=small}