Quantcast

Aug 18, 2009

MSSQL BASICS

create database my_db -- to create new database
use my_db -- to use any created database (default database when we open editor will be master)

create table tablea
(
my_id int primary key,
my_name varchar(50) -- variable character length 50
)
select * from tablea --retrives the stored table values
insert into tablea values ('1','kathirvel')
insert into tablea values ('1','kathirvel') -- cant make it second time primary key constraint will stop from inserting
insert into tablea(my_name) values('kathirvel') -- will not work primary key cant be empty (null)

create table tableb
(
my_id int identity(1,1),
my_name varchar(50)
)
insert into tableb values ('1','kathirvel') -- won't work because identity column is a default increment value
insert into tableb(my_name) values('kathirvel') -- will work

/* now to update tablea change name for id 1 to mkjt*/

update tablea set my_name='mkjt' where my_id='1'

/* delete a particular row from table */
delete from tablea where my_id='1'


/********************************************************************************************************

1) DDL

Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:

* CREATE - to create objects in the database
* ALTER - alters the structure of the database
* DROP - delete objects from the database
* TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
* COMMENT - add comments to the data dictionary
* RENAME - rename an object

2) DML

Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:

* SELECT - retrieve data from the a database
* INSERT - insert data into a table
* UPDATE - updates existing data within a table
* DELETE - deletes all records from a table, the space for the records remain
* MERGE - UPSERT operation (insert or update)
* CALL - call a PL/SQL or Java subprogram
* EXPLAIN PLAN - explain access path to data
* LOCK TABLE - control concurrency

3) DCL

Data Control Language (DCL) statements. Some examples:

* GRANT - gives users access privileges to database
* REVOKE - withdraw access privileges given with the GRANT command

4) TCL

Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.

* COMMIT - save work done
* SAVEPOINT - identify a point in a transaction to which you can later roll back
* ROLLBACK - restore database to original since the last COMMIT
* SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use




********************************************************************************************************/

No comments:

Post a Comment