📄 sqllecture.html
字号:
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META NAME="GENERATOR" CONTENT="Mozilla/4.04 [en] (Win95; I) [Netscape]">
<TITLE>Sql Database Lecture</TITLE>
</HEAD>
<BODY>
<HR SIZE=1 NOSHADE WIDTH="60%">
<CENTER>Sql Databases Mini Lecture</CENTER>
<CENTER>by</CENTER>
<CENTER>d4JinX / AZTEK / Spiderman / Mikkkeee</CENTER>
<CENTER><A HREF="http://blacksun.box.sk">http://blacksun.box.sk</A></CENTER>
<CENTER>
<HR SIZE=1 NOSHADE WIDTH="80%"></CENTER>
<B></B>
<P><B>*Note: This lecture was held impromptu so we didn't have a
chance to cover many more important areas of sql databases.</B><B></B>
<P>*** Mikkkeee sets mode: +m
<BR><Mikkkeee> okay we got something set
<BR><Mikkkeee> its going to be an sql lecture
<BR>-xenitanus- thanks :)
<BR><Mikkkeee> pl/sql then php/sql then cracking sql
<BR><Mikkkeee> then we can kill reptile after we are done
<BR>*** Mikkkeee sets mode: -m
<BR><_ciR_> lol
<BR><MASTURB4TION> sounds ok
<BR><N0_Nick> hm
<BR><N0_Nick> okay
<BR><UnHaFox> lol nick, now its 4.30? and before 10.00?=))))))))))))
you are are from california, aren t you?
<BR><N0_Nick> we will survive
<BR><UnHaFox> =)
<BR><UnHaFox> ok mikkee
<BR><MASTURB4TION> espescially the end
<BR><_ciR_> im 4 killing reptile
<BR><N0_Nick> n0 man
<BR><AZTEK> d4JinX: is starting
<BR>*** AZTEK sets mode: +m
<BR><d4JinX> lol
<BR><AZTEK> take it away d4JinX
<BR><d4JinX> Am I?
<BR>*** Mikkkeee sets mode: +o d4JinX
<BR>*** ChanServ sets mode: -o d4JinX
<BR><AZTEK> well you want to start with history
<BR><d4JinX> I think sql should run first..
<BR>*** SySt3mShk has joined #bsrf
<BR><AZTEK> ok
<BR><Mikkkeee> ----------------sql lecture------------------------
<BR><d4JinX> Okay, SQL stands for Structured query language
<BR><AZTEK> SQL originated from IBM's research labs in the early 1070's
<BR><d4JinX> k..
<BR><AZTEK> SQL is the post cursor to SEQUEL
<BR><AZTEK> Structured English QUEry Language
<BR>*** d4JinX is now known as d4J1nX
<BR>*** Lone[Star] has quit IRC (Quit: see ya pplz l8r)
<BR><AZTEK> sql is strictly a language to access relational databases
<BR>*** _ciR_ has quit IRC (Quit: )
<BR><AZTEK> such as oracle or paradox
<BR><AZTEK> the latest ansi is recomendation sql-92
<BR><AZTEK> and thats my history now for d4J1nX
<BR><AZTEK> brb
<BR><d4J1nX> As Aztek said SQL is a language used to access databases
<BR><d4J1nX> It has very simple syntax
<BR><d4J1nX> And is very easy to pick up
<BR>*** SySt3mShk has quit IRC (Ping timeout: 181 seconds)
<BR><d4J1nX> Languages such as ASP and PHP use SQL statements
<BR><d4J1nX> Say we had a database called people
<BR><d4J1nX> I hope everyone knows what a database is ;-)
<BR><d4J1nX> Within that database would be a number of tables
<BR><d4J1nX> These tables hold the data in the form of fields with their
corresponding types
<BR><AZTEK> ok back
<BR><d4J1nX> eg people table
<BR><d4J1nX> This could be made up of
<BR><d4J1nX> wb Az
<BR><AZTEK> thx
<BR><d4J1nX> SURNAME VARCHAR2(50)
<BR><d4J1nX> FORENAME VARCHAR2(50)
<BR><d4J1nX> TELEPHONE NUMBER
<BR><d4J1nX> VARCHAR and number are the types
<BR><AZTEK> PHONENUM INTEGER(7)
<BR>*** StartX has joined #bsrf
<BR><d4J1nX> There would also be an ID field
<BR><d4J1nX> Used for indexing and joining tables
<BR><d4J1nX> It has to be unique
<BR><d4J1nX> And it cant be null when a new record is added to the table
<BR>*** SySt3mShk has joined #bsrf
<BR><d4J1nX> PEOPLE_ID NUMBER
<BR><d4J1nX> So we have a table called people with the fields already
stated
<BR><d4J1nX> Say we have a web front end
<BR><d4J1nX> And we want to display all the people within the table
<BR><d4J1nX> We would use a statement like
<BR><AZTEK> SELECT * FROM People
<BR><d4J1nX> SELECT * from people
<BR><d4J1nX> * being all the fields
<BR><d4J1nX> We could pull back one field or two
<BR><d4J1nX> SELECT surname,forename FROM table
<BR><d4J1nX> If its a big table
<BR><d4J1nX> SELECT *
<BR><d4J1nX> Can take a long time
<BR>*** WishGenie has joined #bsrf
<BR><d4J1nX> So sometimes its better to pull back only the fields required
<BR><d4J1nX> If your lazy as well, SELECT * lol
<BR>*** Drager has quit IRC (Ping timeout: 180 seconds)
<BR><d4J1nX> Once we issue this statement a temporary table is setup
<BR><AZTEK> a view
<BR><d4J1nX> Which has the relevant fields and the relevant values
<BR><d4J1nX> Yes, you can have views too
<BR>*** CodE4 has joined #bsrf
<BR><AZTEK> i was just wondering when you were goign to start them
<BR>*** idiocy|idle has quit IRC (Read error: 104 (Connection reset by
peer))
<BR><d4J1nX> If we want to add to the people tablAka's: Brian-17-, Mr_Innocent
<BR><d4J1nX> Name: Brian
<BR><d4J1nX> Age: 17
<BR><d4J1nX> Department: Warez
<BR><d4J1nX> Rank: Head
<BR><d4J1nX> Skillz: nuking, kloning, email bombing, spoofing, flooding,
viral attacks, networking, troubleshooting, finding illegal software
<BR><d4J1nX> WeFuck
<BR><d4J1nX> My buffer
<BR><AZTEK> its what i would have done first
<BR><d4J1nX> kay
<BR><d4J1nX> You go for it
<BR><AZTEK> lol from that site form the message board
<BR><d4J1nX> phone
<BR><d4J1nX> Yep
<BR><AZTEK> no no your doing fine
<BR><d4J1nX> 2 secs phone
<BR>*** [PhaLanX] has quit IRC (Ping timeout: 180 seconds)
<BR>*** ken has joined #bsrf
<BR>*** CodE4 has left #bsrf
<BR>*** d4J1nX has quit IRC (Ping timeout: 180 seconds)
<BR><Mikkkeee> okay the lecture will presume in a few min
<BR><AZTEK> i am sorta not here and here
<BR><AZTEK> so i will be in and out
<BR><Mikkkeee> same here iam going to wrap some stuff for the cracking
part
<BR>*** freespeachlamer has joined #bsrf
<BR>*** AirriK has joined #bsrf
<BR>*** d4J1nX has joined #bsrf
<BR>*** [PhaLanX] has joined #bsrf
<BR>*** Mikkkeee sets mode: +v d4J1nX
<BR><Mikkkeee> wb
<BR><d4J1nX> Thx
<BR><d4J1nX> Side lecture - See when ya have a cable modem, dont let
your cellphone anywhere near it, especially when it rings
<BR>*** AirriK is now known as _AirriK-
<BR><AZTEK> lol
<BR><d4J1nX> Keep getting caught with that
<BR><d4J1nX> Had to reboot
<BR><d4J1nX> So, where were we?
<BR><bluehaze> was there any lecture?
<BR>*** _AirriK- has quit IRC (Quit: Leaving)
<BR><Mikkkeee> yah were doing an sql lecture instead
<BR><bluehaze> oh
<BR><Mikkkeee> <d4J1nX> If we want to add to the people tablAka's:
Brian-17-, Mr_Innocent
<BR><Mikkkeee> <d4J1nX> Name: Brian
<BR><Mikkkeee> <d4J1nX> Age: 17
<BR><Mikkkeee> <d4J1nX> Department: Warez
<BR><Mikkkeee> <d4J1nX> Rank: Head
<BR><Mikkkeee> <d4J1nX> Skillz: nuking, kloning, email bombing, spoofing,
flooding, viral attacks, networking, troubleshooting, finding illegal software
<BR><Mikkkeee> we were up to there
<BR><bluehaze> who's giving it?
<BR><bluehaze> oh ok
<BR><Mikkkeee> aztek/d4j1nx/and I
<BR>*** lostmode has joined #bsrf
<BR><d4J1nX> Kay
<BR>*** xenitanus has quit IRC (Quit:)
<BR><d4J1nX> So basically we pull back a temporary table from that statement
<BR><d4J1nX> We can also have conditional syntax
<BR><d4J1nX> Such as
<BR>*** freespeachlamer has left #bsrf
<BR><d4J1nX> SELECT surname FROM people WHERE surname LIKE '%on'
<BR><d4J1nX> Will pull back all the records where the surname has on
at the end
<BR><d4J1nX> Or
<BR><d4J1nX> SELECT phone_number FROM people WHERE phone_number>809090
<BR><d4J1nX> Self explanatry I hope
<BR><d4J1nX> If we want to add to a table
<BR><d4J1nX> Then we use an INSERT statement
<BR>*** Litte has joined #bsrf
<BR>*** cupelmen has joined #bsrf
<BR><d4J1nX> INSERT into people(surname,forname,phone_number) VALUES
('woods','jim',0202020)
<BR><d4J1nX> The ID will be created automatically by means of a trigger
- will get to it later
<BR><d4J1nX> If we wanted to update the table then we would use the
UPDATE statement..
<BR><d4J1nX> UPDATE people SET surname='newsurname';
<BR><d4J1nX> deleting would require knowing the id, more practical,
although not necessary
<BR><d4J1nX> DELETE from people where people_id=349
<BR>*** StartX has quit IRC (Quit: )
<BR><d4J1nX> The three basic db maintenance statements
<BR><d4J1nX> All pretty simple syntax, as I mentioned before
<BR>*** Devil_Panther has joined #BSRF
<BR>*** zwanderer has joined #bsrf
<BR>*** Shadow_Stalker has joined #bsrf
<BR><d4J1nX> So we know how to add to a table in a database, delete
from a table and update a record in a table
<BR>*** reptile has joined #bsrf
<BR>*** Litte has quit IRC (Quit: Clone for reptile w00t w00t)
<BR>*** _ciR_ has joined #bsrf
<BR><AZTEK> yep it is simple
<BR><d4J1nX> As I mentioned earlier..
<BR><d4J1nX> A trigger is used to created the id automatically
<BR>*** _ciR_ is now known as __ciR_-
<BR><d4J1nX> A trigger is a small script, if you like that will add
the id to the id field on an insert into the table
<BR>*** D12_BoyZ has joined #bsrf
<BR><d4J1nX> This ID number is taken from a number sequence in the database
<BR><d4J1nX> The sequences are created manually
<BR>*** ewren has joined #bsrf
<BR>*** D12_BoyZ has left #bsrf
<BR><d4J1nX> Each table that is normalized and has an ID field will
have a different sequence
<BR><d4J1nX> Now..
<BR><d4J1nX> Say we have another field in our people table
<BR>*** paranoid has joined #bsrf
<BR><d4J1nX> Called contact_name
<BR><d4J1nX> And contact_address
<BR>*** MrBS480 has joined #bsrf
<BR>*** cupelmen has joined #bsrF
<BR><d4J1nX> Now in order to normalise a db we would remove these two
"repeating" fields and replace them with one
<BR><d4J1nX> Contact_ID
<BR><d4J1nX> And a separate table would be created called contacts
<BR><d4J1nX> This table would have the fields.
<BR><d4J1nX> Contact_id
<BR><d4J1nX> Contact_name
<BR>*** Mikkkeee sets mode: +v Devil_Panther
<BR><d4J1nX> Contact_address
<BR><d4J1nX> brb door
<BR>*** paranoid has left #bsrf
<BR><Mikkkeee> okay seems like reptile showed up, so were going to finish
this lecture and then do the vb coding
<BR>*** MrBS480 has quit IRC (Quit: )
<BR>*** ewren has quit IRC (Quit: )
<BR>*** flipu has joined #bsrf
<BR><d4J1nX> back
<BR><d4J1nX> Kay, I hope this is making some sense here, didnt really
have time to prepare
<BR><d4J1nX> Right so we now have two tables
<BR>*** lostmode has quit IRC (Quit: )
<BR><d4J1nX> Contact table
<BR><d4J1nX> And people table
<BR><d4J1nX> Say we wanted all contact names for all the people whose
first name was john
<BR><d4J1nX> We would have to join our tables in the query statement
<BR>*** flipu has quit IRC (Not enough time connected)
<BR>*** torpor has joined #bsrf
<BR>*** __ciR_- has left #bsrf
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -