Basic of Data Manipulation Lanaguage (DML).

 The Data manipulation Lanaguage (DML) basically is the manipulation of the data. In the DML there are the following operation we can perform.

• INSERT
• DELETE
• UPDATE
• SELECT

The INSERT statement basically used to insert the data row into data table. The syntax of the INSERT is:-

    INSERT into table_name(column_name) values('value which you want to store')

The DELETE statement is used to delete the data from data table. The syntax of the DELETE is:-

    DELETE from table_name

 

The above query delete all records from the table. if we want to delete the particular record from the table then we use the following query. 

     DELETE from table_name where <condtiion>

The UPDATE statement is used to update the record in the table. The syntax of the UPDATE is:- 

     UPDATE table_name set column_Name=value  

The above query update all the column records in the table. if want to update the particular row then we use the following query.

    UPDATE table_name set column_Name=value UPDATE table_name set column_Name=value

The SELECT statement is used to select and retrive the records from table. The syntax of the SELECT is:-

    SELECT * from table_name 

The above query retrive all the information from the table. if we want to retrive the particular column with some condition then we use the following query.

    SELECT column_name1,column_name2 from table_name where <condtiion>

Transaction in sql server

What is transaction?

A transaction is a group of commands that change the data store in a database. a transaction, is treated as a single unit. a transaction is ensures that, either all of the commands succeed,or none of them. If one of the commands in the transaction fails, all of the commands fails, and any data that was modified in database is rolled back. In this way, transaction maintain the integrity of data in a database.

Transaction processing follows these steps : 

1.Begin a transaction.

2.Process database commands.

3.Check for errors.

If errors occurred,

  rollback the transaction,

Else

  commit the transaction

Note : we are not able to see the un-committed changes by using transaction in the query.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

By setting isolation level read uncommitted in query the we can see the changes value in database.

We have apply the transaction on the following tables.

create table tblCustomerAddress(

id int identity primary key,

customer_code nvarchar(100),

city nvarchar(10),

pin_code nvarchar(6)

)

create table tblMailingAddress(

id int identity primary key,

customer_code nvarchar(100),

city nvarchar(10),

pin_code nvarchar(6)

)

create procedure SP_UpdateAddress

as

begin

begin try

begin transaction

update tblCustomerAddress set city='Delhi'

where id=1 and customer_code='101'

update tblMailingAddress set city='Delhi'

where id=1 and customer_code='101'

commit transaction

end try

begin catch

rollback transaction

end catch

End

The above procedure is working fine when execute the store procedure.

alter procedure SP_UpdateAddress

as

begin

begin try

begin transaction

update tblCustomerAddress set city='Delhi'

where id=1 and customer_code='101'

update tblMailingAddress set city='moradabad moradabad'

where id=1 and customer_code='101'

commit transaction

end try

begin catch

rollback transaction

end catch

End

The above procedure is not working fine because of that we are trying to update the longer value in city column which are set only 10 character limit in database,  so one query is fail then other query also fail here.

How to use not equal to condition in mysql query ?

Here  is the syntax of how to use not equal to condition in mysql

$sql="SELECT * FROM student where NOT city='delhi'";

here this query retrieve the result from student table which student not belong to Delhi.

Alter drop create collation show tables desc

1. Show full structure of column in mysql

 SHOW FULL COLUMNS FROM math;

 mysql> SHOW FULL COLUMNS FROM math;
+--------+---------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field  | Type    | Collation       | Null | Key | Default | Extra | Privileges                      | Comment |
+--------+---------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| id     | int(10) | NULL            | NO   | PRI | NULL    |       | select,insert,update,references |         |
| symbol | text    | utf8_general_ci | YES  |     | NULL    |       | select,insert,update,references |         |
+--------+---------+-----------------+------+-----+---------+-------+---------------------------------+---------+
2 rows in set (0.00 sec)

2. Change Collation of table in mysql

 ALTER TABLE math CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

3. Reset Auto Increment Id  by using following  mysql query

ALTER TABLE table_name AUTO_INCREMENT = 1

4. list of all databases .

SHOW databases;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| list_databases          |
+--------------------+
2 rows in set (0.00 sec)

 5. list of all tables .

  SHOW tables;

 6. Describe the tables column

    DESC table_name;

mysql> DESC math;
+--------+---------+------+-----+---------+-------+
| Field  | Type    | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| id     | int(10) | NO   | PRI | NULL    |       |
| symbol | text    | YES  |     | NULL    |       |
+--------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

7. Delete coulumn from table in mysql

ALTER TABLE table_name DROP COLUMN column_name;

Delete query using JOIN in MYSQL

    Delete query using JOIN in MYSQL
  
    we can delete the record using join
  
    DELETE table-name FROM table-name
    JOIN table-name1 ON table-name.id=table-name1.refID
    WHERE
     Condition