[SQL] Making basic SQL statements

Results 1 to 10 of 10
  1. #1
    Gaby is offline
      V.I.P  Rank
    Apr 2013 Join Date
    The NetherlandsLocation
    1,485Posts

    [SQL] Making basic SQL statements


    RaGEZONE Recommends

    RaGEZONE Recommends

    Hi!

    Since all the SQL tutorials here seem to be very much outdated. 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:

    PHP Code:
    SELECT columnnamecolumnnamecolumnname FROM tablename
    Let’s say we want to select the name and the id of all users, we would write it like this:

    PHP Code:
    SELECT idname 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:

    PHP Code:
    SELECT columnnamecolumnamecolumnname 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:
    PHP Code:
    SELECT idname 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:
    PHP Code:
    SELECT tablename1.columnname tablename1.columnametablename2.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:

    PHP Code:
    SELECT user.iduser.namecountry.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:

    PHP Code:
    SELECT u.idu.namec.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:

    PHP Code:
    SELECT u.idu.namec.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:
    PHP Code:
    SELECT u.idu.namec.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



  2. #2
    C# - Java - Notepad Ben is offline
    ModeratorRank
    Jul 2013 Join Date
    BelguimLocation
    1,229Posts

    Re: [SQL] Making basic SQL statements

    Oh ty ty ty Gaby :/ i got C# exam tomorrow and needed some revision on SQL because I need to database connections and my sql knowledge sucks balls...

    This comes at a great moment.

  3. #3
    Gaby is offline
      V.I.P  Rank
    Apr 2013 Join Date
    The NetherlandsLocation
    1,485Posts

    Re: [SQL] Making basic SQL statements

    Quote Originally Posted by Ben View Post
    Oh ty ty ty Gaby :/ i got C# exam tomorrow and needed some revision on SQL because I need to database connections and my sql knowledge sucks balls...

    This comes at a great moment.
    You are very welcome. ^^ Good luck with your exam.


  4. #4
    Account Upgraded | Title Enabled! Vimsoration is offline
    True MemberRank
    Apr 2015 Join Date
    USALocation
    241Posts

    Re: [SQL] Making basic SQL statements

    Thanks, already know this, but this will help alot of people! Good explained, and it is easy to understand. @MentaL this need a sticky.

  5. #5
    Babylon the Great Droppy is offline
    DeveloperRank
    Feb 2012 Join Date
    /home/droppyLocation
    1,947Posts

    Re: [SQL] Making basic SQL statements

    Great job writing this Gaby! I've been soooo long with basic MySQL usage/understading, that I basically have never tried to improve it. Perhaps by lazyness... or lack of capacity.

    In all cases, this is a great explaination of how JOINs work

    Subscribe | Rules
    PM Me | Add Me

    Developer / Ex-Moderator



  6. #6
    ◝(⁰▿⁰)◜Smile◝ (⁰▿⁰)◜ Taiga is offline
    ModeratorRank
    May 2007 Join Date
    InternetLocation
    2,479Posts

    Re: [SQL] Making basic SQL statements

    How can SQL be out of date, if has practically been the same for years? I haven't seen a lot of change the past years tough.


    Moderator & Developer
    I CAN NOT HELP YOU WITH YOUR SERVER, USE THE CORRECT HELP SECTION INSTEAD.
    I AM ONLY TAIGA ON RAGEZONE!
    BE CAREFUL WITH IMPOSTORS!


    DO NOT PM ME FOR GAME RELATED QUESTIONS, THESE MESSAGES WILL BE IGNORED!

    The Rules | Discord | MMORPG Extra | Contact Me


  7. #7
    Gaby is offline
      V.I.P  Rank
    Apr 2013 Join Date
    The NetherlandsLocation
    1,485Posts

    Re: [SQL] Making basic SQL statements

    Quote Originally Posted by CodeDragon View Post
    How can SQL be out of date, if has practically been the same for years? I haven't seen a lot of change the past years tough.
    It's amazing how people will always try to find something in a thread to bash on. I said the tutorials were outdated, not the language. If the last tutorial on SQL is from 2012, that's outdated in my opinion.


  8. #8
    ◝(⁰▿⁰)◜Smile◝ (⁰▿⁰)◜ Taiga is offline
    ModeratorRank
    May 2007 Join Date
    InternetLocation
    2,479Posts

    Re: [SQL] Making basic SQL statements

    Quote Originally Posted by Gaby View Post
    It's amazing how people will always try to find something in a thread to bash on. I said the tutorials were outdated, not the language. If the last tutorial on SQL is from 2012, that's outdated in my opinion.
    Asking a question is not the same as bashing. Ah, I see thanks for posting it! :D


    Moderator & Developer
    I CAN NOT HELP YOU WITH YOUR SERVER, USE THE CORRECT HELP SECTION INSTEAD.
    I AM ONLY TAIGA ON RAGEZONE!
    BE CAREFUL WITH IMPOSTORS!


    DO NOT PM ME FOR GAME RELATED QUESTIONS, THESE MESSAGES WILL BE IGNORED!

    The Rules | Discord | MMORPG Extra | Contact Me


  9. #9
    Gaby is offline
      V.I.P  Rank
    Apr 2013 Join Date
    The NetherlandsLocation
    1,485Posts

    Re: [SQL] Making basic SQL statements

    Quote Originally Posted by CodeDragon View Post
    Asking a question is not the same as bashing. Ah, I see thanks for posting it! :D
    The way it was typed made me think it was. I understood it wrong then. ;p


  10. #10
    :-) s-p-n is offline
    DeveloperRank
    Jun 2007 Join Date
    Next DoorLocation
    2,148Posts

    Re: [SQL] Making basic SQL statements

    Select and join aren't knew features in SQL, but that doesn't mean this tutorial is less helpful than existing ones. The replies are evidence that show there is an audience for this tutorial and it has helped people, so good job Gaby. The rules encourage improving existing tutorials over creating new ones, and grave-digging isn't a bad thing in the tutorial's section. But again, this tutorial clearly helped people, and the content is good, so it must be good that you posted it.




Advertisement