MYSQL-query-guidance

--------------------------------------Basic mysql command:------------------------------------------------------
(1)create databse tutorials:

create database tutorials;

(2)crete table employee:

create table employee
(id   int,
 name varchar(200),
 address varchar(200),
 city   varchar(200));


and rename table name use:

rename table  oldtablename  to  newtablename;

(3)how to add new column after creating a table employee:

alter table  employee
add  dob  varchar(200);


(4)how to modify  dob column type form varchar type  to date type:

alter table  employee
modify  dob  date;

(5)how to modify   id  int type to int primary key  & auto_increment:

alter table  employee

modify  id int primary key auto_increment;

-----------------------------------Advance Query in mysql:------------------------------------------------------

(1)Order by:

select *  from  lefttable order by id;

(2)Group by:

select  *  from  lefttable group by   id; 

(3)having clause:

select  * from lefttable  having id<3; 

(4)Like :

select * from lefttable  where name like 'o%';

(5)Avg:

select avg(id) from lefttable ;

(6)Sum:

select  sum(id) from lefttable;

(7)Count:

select count(id) from lefttable;

(8)Min:

select  min(id) from lefttable;

(9)Max:

select  max(id) from lefttable;

(10)Lcase:

SELECT LCASE('Tech on the Net');

(11)Ucase:

SELECT UCASE('myteststring');


-------------------Foreign key---------primary  key example---------------
MySQL create table foreign key example



CREATE DATABASE IF NOT EXISTS dbdemo;

USE dbdemo;

CREATE TABLE categories(
   cat_id int not null auto_increment primary key,
   cat_name varchar(255) not null,
   cat_description text
) ENGINE=InnoDB;

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)
   REFERENCES categories(cat_id)
   ON  UPDATE CASCADE
   ON  DELETE RESTRICT
)ENGINE=InnoDB;


--------------------------Adding-----foreign---key--using----alter-----command------------------------------------

ALTER TABLE test1

ADD FOREIGN KEY fk_vendor(testid)

REFERENCES test(id)

ON DELETE NO ACTION

ON UPDATE CASCADE;