Hello There, Guest! Login Register
Logo
Dismiss this notice
MaDLeeTs is not responsible for any attacks that are carried out on networks, websites or servers.
MaDLeeTs staff members cannot be held responsible.
All information on this forum is for educational purposes only.


WORKING WITH SQL By madcode

  • 1 Vote(s) - 1 Average


06-04-2013, 02:11 AM #1
madcodE Offline Administrator **********
Junior Administrator
Posts:501 Threads:99 Joined:Oct 2012 Reputation: 8
Mood: Devilish
assalamualaikum madcode haxor here
in this article we will learn working with
databases :) SQL
this tutorial is written by madcode, for
madleets.com
itcommands.com
+ rep if you like it :D
SQL stands for structured query languages
we will be covering basics of sql here
SQL is langague of database , we will learn how to create database.
how to delete database, fetching rows , modifying rows etc.
SQL is ansi standarad language , it is use in
MYSQL, MS access, Oracle , postgress etc as standard language

So Lets start

Standard commands of SQL

Create : use to create databases, tables or other object
Alter : it is use to modify existing database ie tables
DROP : use to delete databases, tables etc
INSERT : create or store records etc
UPDATE : use to edit existing records
DELETE : name says it all, it delete records
SELECT : it is use to retrieve desired records
GRANT : it give privilages to users
REVOKE : it take back privilages that are granted to users

HOW we use SQL syntax?
we use sql statments under some rules in a proper way :) called syntax , All sql statements start with
KEYwords like select , insert, update etc and end with a
semicolon (;).

and one thing, SQL is case insensitive i.e SELECT and select are same

example SQL command

Code:
SELECT column1, column2 from table_name

this will be used to fetch records of column1 and column2 from specific table name



SQL AND/OR Clause:

Code:
SELECT column1, column2....columnN FROM table_name WHERE FIRST_CONDITION {AND|OR} 2ND CONDION;

SQL DISTINCT Clause:

Code:
SELECT DISTINCT column1, column2....columnN FROM table_name;

SQL WHERE Clause:

Code:
SELECT column1, column2....columnN FROM table_name WHERE CONDITION;


SQL BETWEEN Clause:

Code:
SELECT column1, column2....columnN FROM table_name WHERE column_name BETWEEN val-1 AND val-2;

SQL Like Clause:
Code:
SELECT column1, column2....columnN FROM table_name WHERE column_name LIKE { PATTERN };

SQL ORDER BY Clause:

Code:
SELECT column1, column2....columnN FROM table_name WHERE CONDITION ORDER BY column_name {ASC|DESC};

SQL GROUP BY Clause:

Code:
SELECT SUM(column_name) FROM table_name WHERE CONDITION GROUP BY column_name;

SQL COUNT Clause:

Code:
SELECT COUNT(column_name) FROM table_name WHERE CONDITION;
SQL HAVING Clause:
Code:
SELECT SUM(column_name) FROM table_name WHERE CONDITION GROUP BY
column_name HAVING (arithematic function condition);

SQL CREATE TABLE Statement:
Code:
CREATE TABLE table_name( column1 datatype, column2 datatype, column3
datatype, ..... columnN datatype, PRIMARY KEY( one or more columns )
);

SQL DROP TABLE Statement:

Code:
DROP TABLE table_name;


SQL DROP INDEX Statement :

Code:
ALTER TABLE table_name DROP INDEX index_name;

SQL DESC Statement :

Code:
DESC table_name;
SQL TRUNCATE TABLE Statement:

Code:
TRUNCATE TABLE table_name;

SQL ALTER TABLE Statement:

Code:
ALTER TABLE table_name {ADD|DROP|MODIFY} column_name {data_ype};

SQL ALTER TABLE Statement rename table :

Code:
ALTER TABLE table_name RENAME TO new_table_name;

SQL INSERT INTO Statement:

Code:
INSERT INTO table_name( column1, column2....columnN) VALUES ( value1, value2....valueN);

SQL UPDATE Statement:
Code:
UPDATE table_name SET column1 = value1, column2 = value2....columnN=valueN [ WHERE CONDITION ];

SQL DELETE Statement:
Code:
DELETE FROM table_name WHERE {CONDITION};

SQL CREATE DATABASE Statement:
Code:
CREATE DATABASE database_name;

SQL DROP DATABASE Statement:

Code:
DROP DATABASE database_name;

SQL USE database Statement :

Code:
use database database_name;

SQL commit Statement :

Code:
COMMIT;

SQL ROLLBACK Statement :
Code:
ROLLBACK;



SQL DATATYPES
character / string datatypes


char
varchar
varchar(max)
text

unicode character string datatypes :

nchar
nvchar
nvchar(max)
ntext

BINARY

binary
varbinary
varbinary(max)
image

misc

xml
timestamp
table
cursor
sql_variant

numeric

int
bigint
smallint
tinyint
bit
decimal
numeric
money
smallmoney
float
real

(there may be more but at this time these are in my mind)

SQL Operators
+
-
*
/
%

COMPARISON OPERATORS

=
!=
< >
<
>
>=
<=
!<
!>

lOGICAL OPERATORS

ALL
AND
ANY
BETWEEN
IN
EXISTS
IS NULL
UNIQUE
OR
NOT
LIKE

SQL EXPRESSIONS

SQL EXPRESSION IS A COMPBINATION OF ONE OR MORE VALUES, OPERATORS AND
SQL FUNCTIONS TO GET A VALUE

EXAMPLE
Code:
SELECT column1, column2, columnN FROM table_name WHERE [CONTION|EXPRESSION];

OKAY madleets..
here fun begins
now question is how we gonna create database ?
well okay lets start :)

suppose we are create database with name madleets db

we will be using command

Code:
CREATE database madleets;

[Image: 6eY5UoY.png]

now to make sure that database is created, we will use command

Code:
show databases;

and here is the result
[Image: lqqGF4s.png]

now how to delete or drop database :)
in the above picture u saw there was a db named madcode_1
so now im gonna delete this database we will use

Code:
DROP database madcode_1;

now you can see in below snap that database has been removed
[Image: Y0nRlSC.png]

now how we can select database to work with?
we will use USE command
example: we gonna work in madleets so we will use

Code:
use madleets;
it will show that database has been changed .. see
[Image: Xve2ry6.png]

now lets move to next step :)

creating tables and its columns in database..
for exmaple we are creating a table with name
mad_users with 3 columns we will be using
we will use followning syntax

Code:
CREATE mad_users(
column1 DATATYPE,
column2 DATATYPE,
column3 DATATYPE,
PRIMARY KEY(COLUMN1)
);

okay lets create a table in madleets db and with 4 columns
suppose columns are id,username,password,email

for this we will use

Code:
CREATE table mad_users(
id int(1),
username varchar(30),
password varchar(40),
email varchar(50),
PRIMARY KEY(id)
);
[Image: JRR2syE.png]

now table mad_users has been created :)

we can view table content by using desc commands

we can use command

Code:
desc mad_users;

[Image: lgt71a6.png]

now we know how to create tables :) we can also drop/delete tables

just like we delete databases, we can delete table by using

Code:
DROP TABLE table_name;


now intersting part begins .. we created database,tables, its columns now how can we
add records in columns of tables

for that we use insert into statment

Code:
INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)] VALUES (value1, value2, value3,...valueN);

or just

Code:
INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

will also work

lets add some users in mad_users table

we will be adding a record of my name i.e madcode, id, with password haxor123, and email [email protected]

Code:
insert into mad_users (id, username, password, email)
values (1, 'madcode','haxor123','[email protected]');

and use select command to view table that record is added or not?

[Image: 64Ckahq.png]

Code:
select * from mad_users;

[Image: lgtc997.png]

we use staric * to get all columns, we can also select from specific columns

we can also add records from other tables

syntax:
Code:
INSERT
INTO first_table_name [(column1, column2, ... columnN)] SELECT column1,
column2, ...columnN FROM second_table_name;

we can use select statment with where, and, or clause etc to get specific data etc


update command : we can update records in columns , like
my first email in record was [email protected]
lets change it to [email protected]

Code:
UPDATE mad_users SET email = '[email protected]' where id=1;

[Image: lgtd877.png]

and like update, we can delete record

just use command

Code:
delete from table_name;

well that was just basic :)
stay tuned to madleets to get more updates
thanks
regard's

madcode

Name: madcodE haXor
md5: b2eafeb799f022ab22e1af81c0a2bcfa
Email: [email protected]
Facebook: http://facebook.com/haxor.py

I was raised not to be rude, but I also try to get the best work out of people...


[Image: semqRxA.gif]

06-04-2013, 02:22 AM #2
Darksnipper Offline Junior Administrator **********
Junior Administrator
Posts:639 Threads:77 Joined:Jun 2012 Reputation: 4
Mood: None
Nice share,goNA READ IT WELl ,itz in next sem

06-04-2013, 02:23 AM #3
H4x0rl1f3 Offline Commander In Cheif *******
Administrators
Posts:1,129 Threads:25 Joined:Jun 2012 Reputation: 15
Mood: Stoned
Marbellous explanation BOSS !! loved it.

Always Aspect Reward from the Creator and not anyone or anything Created.
For Complaints & Help Contact:- [email protected]
Zone-h
http://zone-h.org/archive/notifier=H4x0rL1f3
I am the orphan of Gaza & Kashmir. Ruins of Iraq,Syria & Palestine Bombarded of Pakistan,Afghanistan, Mali & Yemen Change of Libya and Egypt Discriminated of Bahrain & Turkey Freedom of Earth's paradise and Al-AQSA. I am the hunger stricken child of Somalia, Ethopia, & rest of Africa I am the suffering of each and every single corner of the world. But Listen!!! Hope is what I am!!


06-04-2013, 02:29 AM #4
phpBuGz Offline Administrator *******
Administrators
Posts:167 Threads:60 Joined:Jun 2012 Reputation: 4
Mood: Busy
Select('mad')->where('nice')->Fetchall('work') ;

VIRUS!!!!!
The Only program that works perfectly every time.....

06-04-2013, 02:30 AM #5
Ch3rn0by1 Offline Respected *****
Respected
Posts:175 Threads:13 Joined:Aug 2012 Reputation: 0
Mood: None
Really i was just like :o while reading this really marvellous +rep added and love yea maddy Biggrinsmiley

06-04-2013, 02:35 AM #6
madcodE Offline Administrator **********
Junior Administrator
Posts:501 Threads:99 Joined:Oct 2012 Reputation: 8
Mood: Devilish
thank you guyz... :x just l0v3 madleets :x


Name: madcodE haXor
md5: b2eafeb799f022ab22e1af81c0a2bcfa
Email: [email protected]
Facebook: http://facebook.com/haxor.py

I was raised not to be rude, but I also try to get the best work out of people...


[Image: semqRxA.gif]

06-04-2013, 02:38 AM #7
TR4CK3R Offline MadLeets Vip ******
V.I.P
Posts:451 Threads:76 Joined:Mar 2013 Reputation: 0
Mood: In Love
I Loved IT Biggrinsmiley .. Well Explained wink

[Image: 213qbgp.jpg]


06-04-2013, 07:17 PM #8
madcodE Offline Administrator **********
Junior Administrator
Posts:501 Threads:99 Joined:Oct 2012 Reputation: 8
Mood: Devilish
soon will update the post with other examples

Name: madcodE haXor
md5: b2eafeb799f022ab22e1af81c0a2bcfa
Email: [email protected]
Facebook: http://facebook.com/haxor.py

I was raised not to be rude, but I also try to get the best work out of people...


[Image: semqRxA.gif]

06-04-2013, 08:56 PM #9
CrazyFrog Offline Junior Member **
Registered
Posts:15 Threads:3 Joined:May 2013 Reputation: 0
Mood: None
Love you [b]??dcodE i really learn so many things with this tutorial. No homo[/b] :bplz:

06-22-2013, 02:44 AM #10
Sn!p3r_GS Offline Super Moderator ******
Super Moderators
Posts:354 Threads:19 Joined:May 2013 Reputation: 1
Mood: Relaxed
a3some share bro

Love & Respect to : Zombie_Ksa-H4x0rL1f3-Shadow008-b0x-1337-Invectus-??dcodE-DeXter -MakMan-MindCracker-Sniffer






Forum Jump:


Users browsing this thread:1 Guest(s)