Wednesday, 10 February 2016



(1)to create database use  command:

SQL> create database practice;

(2)to create table use command:

SQL>create table  myrecord
         (     id   int,
               name varchar(20)

(3)to insert record into table:
there is two type:

 type 1:

insert into  myrecord(id,name)values('1','om');

type 2:

insert into  myrecord values('2','som');

(4)Display record from table:

(a) how to display all record from table:

SQL>select  * from  myrecord;

(b)how to display particular column record:

SQL>select  name from myrecord;  //for single column

SQL>select id,name from myrecord; //for more than one column

(c)how to display  particular rows record:

SQL>select  *  from myrecord where id='1';      // for single row

SQL>select * from myrecord  where id in(1,2);    //for more than one row

(5)how to update record  of  a table:

SQL>update  myrecord   set id='3' , name='omanjali'   where  id='1';    //update by id

SQL>update  myrecord   set id='3' , name='omanjali'   where  name='som';   //update by name

(6)how to delete record from table:

SQL>delete  from myrecord ;   //delete all record

SQL>delete from  myrecord where  id='1';    //delete by id

SQL>delete from myrecord where name='om';    //delete by name

(7)how to add new column into a table :

SQL>alter table  myrecord
          add  city  varchar(200);

(8)how to modify size or type of  of a column:

SQL>alter table myrecord
   modify   city varchar(20);

(9)how to  rename a table :

SQL>rename table  myrecord  to  mynewrecord;

(10)how to rename a column:


alter table  table_name  change  old_column_name  new_column_name  data_type;


SQL>ALTER TABLE mynewrecord  CHANGE  name  myname  VARCHAR(20);

(11)how to change position  of  column into table:


ALTER TABLE tablename   MODIFY   COLUMN   columnname  datatype  AFTER  column;


SQL>ALTER TABLE  mynewrecord   MODIFY   COLUMN    name   VARCHAR(50)   AFTER    id;

(12)how to drop a particular column :

SQL>alter table mynewrecord  drop name;

Primary key &  Foreign key  concept :

(1)CREATE TABLE categories(
   cat_id int not null auto_increment primary key,        //creating a primary key
   cat_name varchar(255) not null,
   cat_description VARCHAR(200)
) ;

(2)CREATE TABLE products(
   prd_id int not null auto_increment primary key,
   prd_name    varchar(355) not null,
   prd_price    decimal,
   cat_id int    not null,
   FOREIGN KEY   fk_cat(cat_id)                              //creating a foreign key
   REFERENCES categories(cat_id)

how to add  foreign key  to a column:

(a)just  create another table name is  myorder:

create table myorder
(order_id   int  not null primary key auto_increment,
order_name   varchar(200)

(b)now  add   a column  order_id to products table:
alter table products  add  order_id   int not null;

(c)now  we add  foreign key to column order_id in products table:

ALTER TABLE products

ADD FOREIGN KEY   fk_order(order_id)

REFERENCES  myorder(order_id)



Conclusion :

a table contain only one  primary  and more than foreign key..

Step 1:

Create table lefttable:

create table lefttable
(id  int(10),
name  varchar(20),
city   varchar(20));

Create table righttable:

create table righttable
(id  int(10),
fname  varchar(20),
fcity   varchar(20));

Note: enter some values   &   some   same   id   value   into  both  tables ..

Step 2:

(1)simple join  example :

select,r.fname  from  lefttable l , righttable r;

(2)Left Join   example:

select,r.fname from lefttable l  LEFT JOIN  righttable r ON;

(3)Right Join   example:

select,r.fname from lefttable l  RIGHT JOIN  righttable r  ON;

(4)Inner Join   example:

select,r.fname from lefttable l  INNER JOIN  righttable r  ON;

VIEW example:

(1)Create a view myview:

CREATE VIEW   myview  AS

SELECT  NAME  FROM myrecord;

(2)Alter view  :

alter  VIEW   myview  AS

SELECT  NAME,contact  FROM myrecord;

(3)How to Drop a  view :

drop view  myview;

user  Grant   & Revoke privileges  on Database  ( for example database name is  practice and user name is omji1)
(1)how to create user on database:

CREATE USER 'omji1'@'localhost' IDENTIFIED BY 'om';

(2)how to Grant all privileges to user:

GRANT USAGE ON *.* TO 'omji1'@'localhost' IDENTIFIED BY 'om1';

(3)how to Grant only few privileges to user:

GRANT INSERT, UPDATE ON `practice`.* TO 'omji1'@'localhost';

(4)Revoke all privileges from user:

REVOKE ALL PRIVILEGES ON `practice`.* FROM 'omji'@'localhost';

(5)revoke all privileges and gran only insert privileges to user:

REVOKE ALL PRIVILEGES ON `practice`.* FROM 'omji1'@'localhost'; GRANT INSERT ON `practice`.* TO 'omji1'@'localhost';

Written by

We are Creative Blogger Theme Wavers which provides user friendly, effective and easy to use themes. Each support has free and providing HD support screen casting.


Post a Comment

PHP Training Mumbai, PHP MySQL Training Mumbai, PHP Course Mumbai, PHP Institute Mumbai


© 2013 PHP TRAINING IN MUMBAI BY OM SIR . All rights resevered. Designed by OM SIR

Back To Top