Jump to content

Recommended Posts

Posted (edited)
Hi!

 

Since all the SQL tutorials here seem to be very much outdated (non existent). I wanted to write a tutorial on how to write basic SQL statements. SQL is a VERY complex and nasty language, there's so much more to know than I can cover in a simple tutorial, but I will try my best.

 

I will start with a tutorial about selecting data, but if you guys want me to make more for updating, deleting, creating, subqueries, counting, summing or whatever, tell me. 

 

Let's say we own a website which has users and these users can all update their profile with a certain country. Our tables look like this:

 

user

id country_id name

1 1 Example Name1

2 1 Example Name2

3 2 Example Name3

4 2 Example Name4

5 NULL Example Name5

 

country

id name

1 Australia

2 Germany

3 China

 

The most basic select statement looks like this:

 



SELECT columnname, columnname, columnname FROM tablename;


  

Let’s say we want to select the name and the id of all users, we would write it like this:

 



SELECT id, name FROM user;


  

This will return the name and id of ALL the records. It will look like this:

id name

1 Example Name1

2 Example Name2

3 Example Name3

4 Example Name4

5 Example Name5

 

But what if we only want to know the name and id from the users who live in Australia? We first need to look in our country table and we see that Australia has id 1. Now we have to add a ‘WHERE’ statement to our SELECT. The most basic SELECT WHERE statement looks like this:

 



SELECT columnname, columname, columnname FROM tablename WHERE columname = 'value' OR columname = 'value' AND columnname = 'value';


  

As you can see, you can add ORs and ANDs to your statement, this works just like you would expect it to work. If you are a programmer, you can see OR as || and AND as &&.

 

Now let’s select our Australian users:



SELECT id, name FROM user WHERE country_id = 1;


  

This will return only Australian users. It will look like this:

id name

1 Example Name1

2 Example Name2

 

But what if we want to select id, name and country name? How do we do that? We can do that with a JOIN. There are a lot of different joins; INNER JOIN, OUTER JOIN, RIGHT JOIN, LEFT JOIN, RIGHT OUTER JOIN, etc.

 

This is a lot of information to take in at once, so we’ll keep it simple and narrow this guide down to two JOINs: the LEFT JOIN and the INNER JOIN. 

So what is the difference? I’ll show you this in the following examples, since writing it down is difficult.

 

The most basic JOIN looks like this:



SELECT tablename1.columnname , tablename1.columname, tablename2.columname FROM tablename1
JOIN tablename2 ON tablename2.keycolumn1 = tablename1.keycolumn2;


  

The key columns represent a column belonging to table2 which is also present in table1.

Now to ALSO select the country name for all users, we write our SELECT statement like this:

 



SELECT user.id, user.name, country.name FROM user
LEFT JOIN country ON country.id = user.country_id;


 

We can add some aliases to make our query a bit more readable:

 



SELECT u.id, u.name, c.name FROM user u
LEFT JOIN country c ON c.id = u.country_id;


 

Using aliases is NOT a must, it just comes down to a matter of preference whether you want to use it or not. 

 

However if you are selecting two columns with the same column name, in our example this is u.name and c.name, you have to add an alias to one of them. I would do it like this:

 



SELECT u.id, u.name, c.name country_name FROM user u
LEFT JOIN country c ON c.id = u.country_id;


  

This query will return the following:

id name country_name

1 Example Name1 Australia

2 Example Name2 Australia

3 Example Name3 Germany

4 Example Name4 Germany

5 Example Name5 NULL

 

But wait.. Example Name5 hasn’t got a country_id filled in, what if we only want users who do have a country_id? This is where the INNER JOIN comes in handy, it will remove all those NULL records from the query result.

 

We use the exact same query and only change LEFT JOIN to INNER JOIN, which will look like:



SELECT u.id, u.name, c.name country_name FROM user u
INNER JOIN country c ON c.id = u.country_id;


  

Now our result is:

id name country_name

1 Example Name1 Australia

2 Example Name2 Australia

3 Example Name3 Germany

4 Example Name4 Germany

 

Thanks.

 

PS: I dont own this guide, i copied it from a pdf file to share it with people keep in mind and thanks again.

Edited by Sawadee
  • Downvote 1
Posted

Nice beginner guide! :)

Btw, you should use parentheses in WHERE columname = 'value' OR columname = 'value' AND columnname = 'value'...

Posted

Nice beginner guide! :)

Btw, you should use parentheses in WHERE columname = 'value' OR columname = 'value' AND columnname = 'value'...

 

 

just made my exams from it so at start i can offer for free and see more in future

 

I will fix it thanks for the tip brother :) 

 

It could be nice to see people helpin each other on Lineage2 Section actually thats what this community needs.

Posted

-1 reputation due to copy paste without proper credits. I was going to give you +1 but then elfo happened. 

Is fine if you copy paste, everyone does it. But at least give credits even if is 5 lines or 4 lines. 

 

If you sit down and make a better and bigger tutorial with more examples such as if i want to find ID of a monster that type is not equal to X 

i'll give you again +

Posted (edited)

 

 

-1 reputation due to copy paste without proper credits. I was going to give you +1 but then elfo happened. 

Is fine if you copy paste, everyone does it. But at least give credits even if is 5 lines or 4 lines. 

 

If you sit down and make a better and bigger tutorial with more examples such as if i want to find ID of a monster that type is not equal to X 

i'll give you again +

 

I didnt copied anything from RageZone, and i simply wanted to share this guide from a tutorial guide.pdf that i have on my pc, So do i have to give credits to my Pdf? i dont care about reputations but explain me why do you butthurt some much and instead of doin actually something you just trashtalkin other topics and posts? and please dont start tellin me that This my opinion and im free to say anythin, if you wanna say anythin u want just go on spam topics or make a guide share it with people and dont expect anythin.

Edited by Sawadee
Posted

I didnt copied anything from RageZone, and i simply wanted to share this guide from a tutorial guide.pdf that i have on my pc, So do i have to give credits to my Pdf? i dont care about reputations but explain me why do you butthurt some much and instead of doin actually something you just trashtalkin other topics and posts? and please dont start tellin me that This my opinion and im free to say anythin, if you wanna say anythin u want just go on spam topics or make a guide share it with people and dont expect anythin.

 

Lel, i made the biggest guide since 2012 about l2j gathered 100k views +

Also now you're being bitch so you deserve -1 and xdem's laugh. 

Posted (edited)

He even c/p "Hi" just promote this autist already

 

Lel, i made the biggest guide since 2012 about l2j gathered 100k views +

Also now you're being bitch so you deserve -1 and xdem's laugh. 

 

I wont argue with people like you, once i did while u wasnt even registered on mxc. U are free to post anythin you want im not gonna feed some random trolls :) NM2YaH5.png

Edited by Sawadee
Posted (edited)

i have on my pc, So do i have to give credits to my Pdf? 

Oh... and you 'PDF' is saying :

 

I will start with a tutorial about selecting data, but if you guys want me to make more for updating,

 

You want ur pdf make updates? :D

 Its cool bro :D

Edited by melron

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.



  • Posts

    • My official facebook profile!: https://www.facebook.com/spectrumL2 Specifications: Revamped L2JACIS revision FROM the core Private project!!! Revision that has been receiving corrections for over 3 years!!! Events already installed in the revision: TVT CTF KTB PARTY FARM SPOIL EVENT CRAZY RATES TOURNAMENT TIME ZONE (INSTANCE) All working correctly!!! SIEGE ESSENTIAL FEATURES: Walls fix Gates fix Flags fix 100% functional: OLYMPIADS: Implemented settings Hero receives enchanted Weapons with equal status PvP Weapons Optional /true/false Hero can acquire all Hero Weapons Optional true/false OTHER IMPLEMENTATIONS: Teleport fixed (directly to Giran) Teleport effect classic Vip skins vip collor name Pack NPCs with effect already configured BOSES already configured Mobs already configured CLASS BALANCE SPECIAL SYSTEM We have a SPECIAL system developed for Class Balance with only 1 digit in XML %tage of configurable debuffs Player limitation system in BOSES or PvP zones BS blocking system in FLEG zones or events Among others dozens of improvements made in the review... price: 390 USD !  OBS: WE CAN CHANGE THE BANNER AND NAME OF THE SERVICE TO THE ONE OF YOUR PREFERENCE BUT THE SETTINGS MUST BE KEPT ANY CHANGES REQUIRE ADDITION        
    • Server is Online – 1,000+ Active Players! We’re excited to announce the addition of a Europe Proxy to improve connectivity for our EU players! Clans can now benefit from VIP Access to help you catch up faster. 🎯 If you're a clan leader with at least 9 active members, join our Discord and open a ticket to claim your VIP rewards!  
    • The Telegram team is rolling out a new batch of Stars-only gifts you’ll be able to mint as NFTs. Don’t miss your chance to join the next Telegram trend and earn from it! Buy Telegram Stars cheap and KYC-free 1 Star from $0.0149 (min. 50 Stars, bulk discounts available) Promo code STARS5 — 5 % off Pay any way you like: bank cards · crypto · other popular methods How to purchase: ➡Online Store — Click ➡ Telegram bot — Click Other services: ➡ SMM panel — Click Regular buyers get extra discounts and promo codes. Support: ➡ Telegram: https://t.me/solomon_bog ➡ Telegram channel: https://t.me/accsforyou_shop ➡ Discord: https://discord.gg/y9AStFFsrh ➡ WhatsApp: https://wa.me/79051904467 ➡ Email: solomonbog@socnet.store Use these contacts to discuss wholesale orders, partnerships (current list: https://socnet.bgng.io/partners) or to become a supplier. SocNet — your shop for digital goods and premium subscriptions
    • The Telegram team is rolling out a new batch of Stars-only gifts you’ll be able to mint as NFTs. Don’t miss your chance to join the next Telegram trend and earn from it! Buy Telegram Stars cheap and KYC-free 1 Star from $0.0149 (min. 50 Stars, bulk discounts available) Promo code STARS5 — 5 % off Pay any way you like: bank cards · crypto · other popular methods How to purchase: ➡Online Store — Click ➡ Telegram bot — Click Other services: ➡ SMM panel — Click Regular buyers get extra discounts and promo codes. Support: ➡ Telegram: https://t.me/solomon_bog ➡ Telegram channel: https://t.me/accsforyou_shop ➡ Discord: https://discord.gg/y9AStFFsrh ➡ WhatsApp: https://wa.me/79051904467 ➡ Email: solomonbog@socnet.store Use these contacts to discuss wholesale orders, partnerships (current list: https://socnet.bgng.io/partners) or to become a supplier. SocNet — your shop for digital goods and premium subscriptions
    • 📜 • Mass PVP – Craft – Progressive Server (ITEMS, ARMOR, WEAPONS, ETC) 🕹️ • Chronicles: Lineage 2 - Interlude (C6) 🛠️ • Retail status 🕒 • Server Time: GMT -3 🏙️ • Main Town: Giran ✨ • Teleportation for all Towns, Gk Global 🛡️ • NPC BUFFER - GMSHOP B-GRADE - DONATION SHOP - AUCTION MANAGER 🐉 • Epic Bosses: Chaotic Zones 🔁 • Protection respawn: 15 seconds ⏰ • Restart Server: 05:00 AM Today 💸 • RTM allowed between players (ask Staff if in doubt) 📊 SERVER RATES: • EXP: x8 • SP: x10 • Adena: x3 • Seal Stone: x3 • Drop: x3 • Spoil: x5 • Raid EXP/SP/Drop: x3 • Premium Rates: x2 🌐 Website: https://www.l2roosters.com 💬 Discord: https://discord.gg/cUyYXrfy 🔥 Join us now and forge your legacy at Roosters Gaming!
  • Topics

×
×
  • Create New...

AdBlock Extension Detected!

Our website is made possible by displaying online advertisements to our members.

Please disable AdBlock browser extension first, to be able to use our community.

I've Disabled AdBlock