Sql Databases Mini Lecture by d4JinX / AZTEK / Spiderman / Mikkkeee http://blacksun.box.sk
*Note: This lecture was held impromptu so we didn't have a chance to cover many more important areas of sql databases.
*** Mikkkeee sets mode: +m
okay we got something set
its going to be an sql lecture
-xenitanus- thanks :)
pl/sql then php/sql then cracking sql
then we can kill reptile after we are done
*** Mikkkeee sets mode: -m
<_cir_> lol
sounds ok
hm
okay
lol nick, now its 4.30? and before 10.00?=)))))))))))) you are are from california, aren t you?
we will survive
=)
ok mikkee
espescially the end
<_cir_> im 4 killing reptile
n0 man
d4JinX: is starting
*** AZTEK sets mode: +m
lol
take it away d4JinX
Am I?
*** Mikkkeee sets mode: +o d4JinX
*** ChanServ sets mode: -o d4JinX
well you want to start with history
I think sql should run first..
*** SySt3mShk has joined #bsrf
ok
----------------sql lecture------------------------
Okay, SQL stands for Structured query language
SQL originated from IBM's research labs in the early 1070's
k..
SQL is the post cursor to SEQUEL
Structured English QUEry Language
*** d4JinX is now known as d4J1nX
*** Lone[Star] has quit IRC (Quit: see ya pplz l8r)
sql is strictly a language to access relational databases
*** _ciR_ has quit IRC (Quit: )
such as oracle or paradox
the latest ansi is recomendation sql-92
and thats my history now for d4J1nX
brb
As Aztek said SQL is a language used to access databases
It has very simple syntax
And is very easy to pick up
*** SySt3mShk has quit IRC (Ping timeout: 181 seconds)
Languages such as ASP and PHP use SQL statements
Say we had a database called people
I hope everyone knows what a database is ;-)
Within that database would be a number of tables
These tables hold the data in the form of fields with their corresponding types
ok back
eg people table
This could be made up of
wb Az
thx
SURNAME VARCHAR2(50)
FORENAME VARCHAR2(50)
TELEPHONE NUMBER
VARCHAR and number are the types
PHONENUM INTEGER(7)
*** StartX has joined #bsrf
There would also be an ID field
Used for indexing and joining tables
It has to be unique
And it cant be null when a new record is added to the table
*** SySt3mShk has joined #bsrf
PEOPLE_ID NUMBER
So we have a table called people with the fields already stated
Say we have a web front end
And we want to display all the people within the table
We would use a statement like
SELECT * FROM People
SELECT * from people
* being all the fields
We could pull back one field or two
SELECT surname,forename FROM table
If its a big table
SELECT *
Can take a long time
*** WishGenie has joined #bsrf
So sometimes its better to pull back only the fields required
If your lazy as well, SELECT * lol
*** Drager has quit IRC (Ping timeout: 180 seconds)
Once we issue this statement a temporary table is setup
a view
Which has the relevant fields and the relevant values
Yes, you can have views too
*** CodE4 has joined #bsrf
i was just wondering when you were goign to start them
*** idiocy|idle has quit IRC (Read error: 104 (Connection reset by peer))
If we want to add to the people tablAka's: Brian-17-, Mr_Innocent
Name: Brian
Age: 17
Department: Warez
Rank: Head
Skillz: nuking, kloning, email bombing, spoofing, flooding, viral attacks, networking, troubleshooting, finding illegal software
WeFuck
My buffer
its what i would have done first
kay
You go for it
lol from that site form the message board
phone
Yep
no no your doing fine
2 secs phone
*** [PhaLanX] has quit IRC (Ping timeout: 180 seconds)
*** ken has joined #bsrf
*** CodE4 has left #bsrf
*** d4J1nX has quit IRC (Ping timeout: 180 seconds)
okay the lecture will presume in a few min
i am sorta not here and here
so i will be in and out
same here iam going to wrap some stuff for the cracking part
*** freespeachlamer has joined #bsrf
*** AirriK has joined #bsrf
*** d4J1nX has joined #bsrf
*** [PhaLanX] has joined #bsrf
*** Mikkkeee sets mode: +v d4J1nX
wb
Thx
Side lecture - See when ya have a cable modem, dont let your cellphone anywhere near it, especially when it rings
*** AirriK is now known as _AirriK-
lol
Keep getting caught with that
Had to reboot
So, where were we?
was there any lecture?
*** _AirriK- has quit IRC (Quit: Leaving)
yah were doing an sql lecture instead
oh
If we want to add to the people tablAka's: Brian-17-, Mr_Innocent
Name: Brian
Age: 17
Department: Warez
Rank: Head
Skillz: nuking, kloning, email bombing, spoofing, flooding, viral attacks, networking, troubleshooting, finding illegal software
we were up to there
who's giving it?
oh ok
aztek/d4j1nx/and I
*** lostmode has joined #bsrf
Kay
*** xenitanus has quit IRC (Quit:)
So basically we pull back a temporary table from that statement
We can also have conditional syntax
Such as
*** freespeachlamer has left #bsrf
SELECT surname FROM people WHERE surname LIKE '%on'
Will pull back all the records where the surname has on at the end
Or
SELECT phone_number FROM people WHERE phone_number>809090
Self explanatry I hope
If we want to add to a table
Then we use an INSERT statement
*** Litte has joined #bsrf
*** cupelmen has joined #bsrf
INSERT into people(surname,forname,phone_number) VALUES ('woods','jim',0202020)
The ID will be created automatically by means of a trigger - will get to it later
If we wanted to update the table then we would use the UPDATE statement..
UPDATE people SET surname='newsurname';
deleting would require knowing the id, more practical, although not necessary
DELETE from people where people_id=349
*** StartX has quit IRC (Quit: )
The three basic db maintenance statements
All pretty simple syntax, as I mentioned before
*** Devil_Panther has joined #BSRF
*** zwanderer has joined #bsrf
*** Shadow_Stalker has joined #bsrf
So we know how to add to a table in a database, delete from a table and update a record in a table
*** reptile has joined #bsrf
*** Litte has quit IRC (Quit: Clone for reptile w00t w00t)
*** _ciR_ has joined #bsrf
yep it is simple
As I mentioned earlier..
A trigger is used to created the id automatically
*** _ciR_ is now known as __ciR_-
A trigger is a small script, if you like that will add the id to the id field on an insert into the table
*** D12_BoyZ has joined #bsrf
This ID number is taken from a number sequence in the database
The sequences are created manually
*** ewren has joined #bsrf
*** D12_BoyZ has left #bsrf
Each table that is normalized and has an ID field will have a different sequence
Now..
Say we have another field in our people table
*** paranoid has joined #bsrf
Called contact_name
And contact_address
*** MrBS480 has joined #bsrf
*** cupelmen has joined #bsrF
Now in order to normalise a db we would remove these two "repeating" fields and replace them with one
Contact_ID
And a separate table would be created called contacts
This table would have the fields.
Contact_id
Contact_name
*** Mikkkeee sets mode: +v Devil_Panther
Contact_address
brb door
*** paranoid has left #bsrf
okay seems like reptile showed up, so were going to finish this lecture and then do the vb coding
*** MrBS480 has quit IRC (Quit: )
*** ewren has quit IRC (Quit: )
*** flipu has joined #bsrf
back
Kay, I hope this is making some sense here, didnt really have time to prepare
Right so we now have two tables
*** lostmode has quit IRC (Quit: )
Contact table
And people table
Say we wanted all contact names for all the people whose first name was john
We would have to join our tables in the query statement
*** flipu has quit IRC (Not enough time connected)
*** torpor has joined #bsrf
*** __ciR_- has left #bsrf
SELECT contact_name FROM people p,contact c WHERE p.contact_id=c.contact_id AND forename = 'John'
Notice the c and p used for an alias and to remove ambiguity
*** WishGenie has quit IRC (Quit: i gotta reboot)
*** cupelmen has quit IRC (Quit: time to sleep...)
That statement will pull back the contact names for all the people called John
On an off note..
Say we had a drop down/combo box on our webpage
And we wanted the full details of a particular person
*** makzee has quit IRC (Ping timeout: 180 seconds)
*** SpiderMan has joined #bsrf
*** ChanServ sets mode: +o SpiderMan
In the drop box we would have the details "John Woods" or "Chris Simms"
*** bluehaze has quit IRC (Ping timeout: 180 seconds)
But the actual value would be the corresponding id in the table
hail spider
hey
Spidey
*** ken has quit IRC (Quit: )
all hail SpiderMan
hey AZTEK
*** Capt_InsaneO has joined #bsrf
Depending on the langauge being used we loop through each record brought back from the query and have a formselectOption for each
*** Mezzano has quit IRC (Read error: 113 (No route to host))
SELECT people_id, forename FROM people;
The forename would be in the dropdown
*** torpor has left #bsrf
But behind the scenes the actual value for that selection is the id
*** Capt_InsaneO has left #bsrf
*** mezzano has joined #bsrf
*** acid519 has joined #bsrf
oh... hi spidy
This is moving on to front end languages though and since php seems to be very popular at the moment, Aztek will take it form here
Hey Spider :-)
hi
sorry to disturb
np
ok i guess its php time
and we are going to php and not perl
so soon?
lets hit it spidy\
yea
ok
nothing more of SQL?
WHERE clause
php now
db normilization
ok
AZTEK,,, i guess you preffer php on perl... huh?!
we did the WHERE
yea
Thats already been done Spider
ok
Plus normalisation - although not alot
ok then I guess it is php time :)
mysql is the prefered datbase engine of choice by us phpers
because it's small, fast, and cheap
what make us choose mysql over any other
You want me to go into normalisation more first Aztek?
exactly
cant beat free
also it's avaliable on many platforms
ok ok...
LECTURE PLS
yea any platform apache and php run mysql does
lol
*** BaGeL has joined #bsrf
ok
mysql_connect();
you have to connect to the mysql host
Can php use access databases Az?
usually localhost
d4J1nX: yes
actually i am waiting for spidy to give me the syntax since i don't have my manual open
for mysql_connect()?
it's localhost, username, password
yea
in strings
yea but port
and stuff
if no port is given it uses the default
int mysql_connect ([string hostname [:port] [:/path/to/socket] [, string username [, string password]]])
you don't have to provide the port unless it's different then the standard
yea
i was wanting the full atribs :)
ah, I'll open my docs
ok mysql_connect() returns a database handle
that we will use later for other things
you should always check for a valid handle
we use mysql_query();