Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Introduction to Sql & Mysql
#1
introduction to SQL & MySQL

- what are databases
- what is data
- what is sql
- how does a statement looks
- types of DBMS
- CREATE users
- CREATE databases
- setting USER perms
- INSERT DATA in tables
- SELECT tables
- SELECT columns
- UPDATE DATA in tables
- ALTER  tables

To keep things simple i have used two charcters JOHN AND JAMES. JAMES is an IT expert where as JOHN is learner.HE ie john  punched data ,he was curious to learn how database works and How extactly database is getting stored,why is SQl being used. this a converstation between JOHN & JAMES

[Image: H1W9ket.png]

[JOHN] How are databases used in our daily lives  ?

Virtually every application is connected to use a database at some point or another. Actually we live in world where Almost everything is data driven. Assume John is sent to buy groceries.John makes  a payment that transaction is stored in a database example CUSTOMER_TRANS.For instances you sign up on website your EMAIL & password are stored in a database.

[JOHN] what hell on Earth is data ?

Data is units of information exmaple it  can be anything like your name,phone,telephone address your age,height etc.. these are some exmaples you can related to.
collection of items form a data.

[JOHN] what is Database ?

Database is systematic collection of data. Databases allow you store and manipulated information of data. database makes data easy to manage you can pull data  form database at any point . example James pulls  out a data form database called myshop_db  SELECT * form emails; this query here selects EMAILS form table of database.

[JOHN] what is SQL ?

Structured Query language is also called as SQL is standard language when you deal with DBMS. SQL can do alot it can update,repair,create users,create  databases instering queries, use databases. etc..

[JOHN] how does a SQL statement looks like ?

Code:
SELECT * FORM USERS where age > 50;

here the query selects USERS where parameter is set to 50

[JAMES] types of DBMS you need to know !!

Hierarchical, Network, Relational & OO(Object-Oriented)DBMS

Hierarchical model :- in Hierarchical DBMS data is stored in structural order  top to bottom .Data is represented using partner&child.

[Image: 8jNLDQp.png]

Network model:- in Network model it is easy to represent objects to thier partner or relation-ships. data is represented using graphs.Here data can have many parent nodes.

[Image: UOonnLf.png]

Relational model :- it the easiest to store data. data is represented in rows & columns of table.is stored in structure and uses SQL to insert data

examples of RDBMS

- MySql
- Postgresql
- Ms Access
- Oracle database

[Image: ZnFpb6h.png]

Object-oriented model :- data stored in the form of objects. The structure which is called classes which display's data within it.database is defined as collection of objects or numbers. it records both data and member values

[Image: zRxN75C.png]

Mysql Installation

Windows

The best option for Windows is downloading is GUI installer and follow prompts https://dev.mysql.com/downloads/mysql/

Linux

There many options out there to install mysql on Linux one of the methods are using (terminal)

Code:
apt-get install mysql-server

After installation is completed you need to start the service

Code:
systemctl start mysql

Adding Mysql to lauch on boot (optional)

Code:
systemctl enable mysql

Mac os  

I assume you have homebrew installed on your Mac osx

Code:
brew install mysql

wait for installation to finish and start the mysql service

Code:
brew services  start  mysql

Initial Setup after installation

Need a Database first

Code:
CREATE DATABASE my_shop;

Secure is better

Adding a user who can ALTER,DROP,MODIFY,INSERT,CREATE the database.r00t has access gobally incase r00t account is breached you still have your DB secured to some extent

Adding a USER to DATABASE

Code:
CREATE USER 'my_shop'@'localhost' IDENTIFIED BY '123'

setting permissions to USER

Wondering what is GRANT cmd

GRANT is command which provides privileges on DATABASE.It can used to set permission on the user for example

Code:
GRANT CREATE, SELECT, UPDATE, DELETE
ON exmaple_wp.*
TO 'example_wp'@'localhost';

this command GRANTS permission to [email protected] to CREATE,SELECT,DELETE dbs

Code:
GRANT ALL PRIVILEGES ON my_shop.* TO 'my_shop'@'localhost' IDENTIFIED BY '123'

now the actually fun thing creating Tables

Code:
CREATE TABLE customers
(
cust_no INT,
cust_name VARCHAR (50),
cust_email (50)
);

INT stands for integers VARCHAR can be letters,numbers,chars etc

verifying if columns are created in our database table

Code:
show columns from customers ;

[Image: dFQqa1c.png]

Inserting data in tables

Code:
INSERT INTO customers (cust_no, cust_name , city , cust_email) VALUES ("1" ,"JohnRip3r", "newyork" ,[email protected]")

INSERT INTO customers (cust_no, cust_name, city ,cust_email) VALUES ("2" ,"why_fear" ,"LA" ,"[email protected]")

[Image: XLzdlr1.png]

this code inserts data in tablename(column1,column2,column3) VALUES follows

suppose JOHN runs a website and a user requests him to change his email address because customers lost acesss to his previously owned email

here JOHN needs to update data in column

it follows

Code:
UPDATE customers set cust_emails = "[email protected]" WHERE cust_no = 2 ;

old email address

[Image: 9uxkPYo.png]

updated email address

[Image: iFHlAYu.png]

JOHN has updated his website registartion page  and added DOB. He needs to ALTER customers table and add DOB

Code:
ALTER TABLE customers add (DOB INT ) ;

[Image: 6RHPted.png]

closing up hope like this sorry for my bad english :p
[Image: BezlSXT.gif]
[-] The following 1 user Likes admin's post:
  • Xyt0
Reply
#2
next tutorial ill make simple PHP website to demonstrate sql injections
[Image: BezlSXT.gif]
Reply
#3
Nice tutorial jinder......
[-] The following 1 user Likes Xyt0's post:
  • admin
Reply
#4
Awesome work.. helpful for beginners..
[-] The following 1 user Likes mrDon's post:
  • Xyt0
Reply


Forum Jump:


Users browsing this thread: 1 Guest(s)