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