mysql-database-tutorials - Hallo sahabat Tutorials, Pada Artikel yang anda baca kali ini dengan judul mysql-database-tutorials, kami telah mempersiapkan artikel ini dengan baik untuk anda baca dan ambil informasi didalamnya. mudah-mudahan isi postingan yang kami tulis ini dapat anda pahami. baiklah, selamat membaca.

Judul : mysql-database-tutorials
link : mysql-database-tutorials

mysql-database-tutorials

(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:

syntax:

alter table  table_name  change  old_column_name  new_column_name  data_type;

example:

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

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

SYNTAX:

ALTER TABLE tablename   MODIFY   COLUMN   columnname  datatype  AFTER  column;

example:

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)
   ON  UPDATE CASCADE
   ON  DELETE RESTRICT
);

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)

ON DELETE NO ACTION

ON UPDATE CASCADE;

Conclusion :

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

========================================================================
JOIN EXAMPLE
========================================================================
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  l.name,r.fname  from  lefttable l , righttable r;

(2)Left Join   example:

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


(3)Right Join   example:

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


(4)Inner Join   example:

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

========================================================================
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';

Demikianlah Artikel mysql-database-tutorials

Sekianlah artikel mysql-database-tutorials kali ini, mudah-mudahan bisa memberi manfaat untuk anda semua. baiklah, sampai jumpa di postingan artikel lainnya.

Anda sekarang membaca artikel mysql-database-tutorials dengan alamat link https://othereffect.blogspot.com/2016/02/mysql-database-tutorials.html

Tweet