My sql tutorial free download pdf

Below is a step-by-step walkthrough of the code. In English, this line of code reads "Select every entry from the table example". In our MySQL table "example," there are only two fields that we care about: name and age.

These names are the keys to extracting the data from our associative array. In the next lesson we will see how to retrieve every entry of a table and put it into a nicely formatted table.

If you have been jumping around our MySQL Tutorial then you would have already seen this function popping up all over the place. It isn't something you can directly manipulate, that is for sure. Additional PHP functions are required to extract the data from this Resource. Our table example basically looks like the table below.

In our table example these are: name and age. You can keep doing this until the MySQL Resource has reached the end which would be three times in our example. Sounds like an awfully repetitive task. We will get a new row of MySQL information that we can print out each time the while loop checks its conditional statement. Now that we know what we need to do and how to go about doing it, the code pretty much writes itself, so let's move on to the next lesson.

Just kidding! You could apply this script to any MySQL table as long as you change both the table name in the query and the column names that we have in the associative array. In this example we will select everything in our table "example" and put it into a nicely formatted HTML table. If you added more entries to your database's table, then you would see each additional row appear in the above table.

Since we want to use this data in our table we need to store it in a variable. By putting it in a while loop it will continue to fetch the next array until there is no next array to fetch. This function can be called as many times as you want, but it will return FALSE when the last associative array has already been returned. By placing this function within the conditional statement of the while loop, we can kill two birds with one stones.

We can tell the while loop to stop printingn out information when the MySQL Resource has returned the last array, as False is returned when it reaches the end and this will cause the while loop to halt. In our MySQL table "example" there are only two fields that we care about: name and age. These fields are the keys to extracting the data from our associative array.

It might be useful to try out other methods of HTML formatting as well. See which one you like best! Those entries that do not pass the test will be left out. We will be assuming the data from a previous lesson for the following examples.

To select Sandy only we could either specify Sandy's age 21 or we could use her name Sandy Smith. In the future there may be other people who are 21, so we will use her name as our requirement. WHERE is used in conjuction with a mathematical statement. Here's how to do it. With the tools you have now, you could make 10 different queries, one for each age 20, 21, Experiment with it so you can see for yourself how powerful this little trick can be.

Note: The wildcard was used for example purposes only. If you really wanted to explicilty select people who are in their 20's you would use greater than 19 and less than 30 to define the 20's range. Using a wildcard in this example would select unwanted cases, like a 2 year old and your year old great-great-great-grandparents. A common way to do this in the real world is to order a big list of items by name or amount.

What ORDER BY does is take the a column name that you specify and sort it in alphabetical order or numeric order if you are using numbers. Ordering is also used quite frequently to add additional functionality to webpages that use any type of column layout. For example, some forums let you sort by date, thread title, post count, view count, and more. We have an ordered MySQL result! Notice that we didn't have to change any of our PHP code.

This is fine for simple takes, but in most real world MySQL usage you will often need to get data from multiple tables in a single query. The act of joining in MySQL refers to smashing two or more tables into a single table. This means everything you have learned so far can be applied after you've created this new, joined table.

The two tables we will be using relate to a families eating habits. In the "family" table, the Position column contains all the members of the family and their respective ages. In the "food" table the Position column contains the family member who enjoys that dish. It's only through a shared column relationship such as this that tables can be joined together, so remember this when creating tables you wish to have interact with each other.

We will be performing a generic join of these two tables using the Position column from each table as the connector. If you do not have either of them created, you can either create them using our MySQL Create Table lesson or do it manually yourself. Position, food. Meal ". Position" will restrict the results to the rows where the Position exists in both the "family" and "food" tables. Let's analyze the tables to make sure we agree with these results.

And by manually perusing the tables it looks like there were indeed three meals liked by family members. Note: This is a very simple example of a join.

If you do not understand it yet do not despair. Joins are a very hard concept to grasp for beginning MySQL developers. This lesson will teach you how to do a specialized join: left join. First of all, the syntax is quite different and somewhat more complex. Nothing tricky about that. This extra consideration to the left table can be thought of as special kind of preservation.

Each item in the left table will show up in a MySQL result, even if there isn't a match with the other table that it is being joined to. Meal, family. This is because a left join will preserve the records of the "left" table. Let's see if the results are what we expected. This stuff isn't easy!

One of the columns in this table is called "Seniority" and it holds an integer value of how many months an employee has worked at your company.

Unfortunately for you, your job is to update these numbers every month. You may be thinking that you'll have to open up your MySQL administration tool and edit each entry by hand. That would take hours. On the other hand, you could master MySQL and have an automated script that you run each month to get the job done for you. Sandy has just had a birthday and she now 22 years old. In a table where Sandy is not the onlyl entry, this may become a problem, and a more sophisticated solution would be necessary.

From time to time, you may even need to delete items from your database. Some potential reasons for deleting a record from MySQL include when: someone deletes a post from a forum, an employee leaves a company, or you're trying to destroy your records before the federalies come! We need to choose a table, tell MySQL to perform the deletion, and provide the requirements that a record must have for it to be deleted. Say we want to delete the youngest employee from our previously created table because he has to go back to school.

This is how we do it. Since Bobby was the only 15 year old this was not a problem. Test your delete queries before even thinking about using them on your table. As long as you take caution when using this powerful query you should not run into any problems. Depending on what you're using your databases for -- be it to store forum messages, employee information, or your spending information -- you are going to need to choose a backup schedule that meets your needs.

You may or may not know that MySQL databases are just files that are stored on your web server. This fact makes the whole backup and restore process extremely simple and painless once you have figured out how to do it. However, no matter which application, control panel tool, or SSH script you use, all of the backups will fit into two types of backups: a dump or raw backup. MySQL Dump A MySQL dump is a bit slower than a raw backup because it creates all the SQL queries required to create the tables of that database, as well as all the insert queries required to place the information back into the database's tables.

If you want to perform the mysql dump manually, without the assistance of your hosts control panel, then run SSH to your web server and do the following taken from MySql.

However, not many control panels support this type of backup, so do not worry if your hosting provider doesn't have this option set up for you. MySQL Backup in Control Panel cPanel cPanel is the most widely used web host control panel at this time, so we thought it would make sense to provide a walkthrough specifically for cPanel. From the application selection screen click "Backup". This will bring you to the backup application that allows you to generate and download complete backups for your site.

Below that title should be a listing of every database that you have created. Simply click on the name of the database you want to backup and save it to your computer. That's it! Before we talk about what they are, let's review the definition of aggregate, as it relates to MySQL:.

Aggregate - Constituting or amounting to a whole; total. The most common types of aggregate functions let you find out things like the minimum, maximum and even the average of a "grouped" set of data. The trick to understanding aggregate functions is often understanding what kind of data is being grouped and analyzed.

Below is the SQL for our "products" table. You can download the products. Below is the MySQL table products. In this case we would be "grouping" by the product type and finding the minimum price of each group. Additionally, we want to use the type column as our group. For example, if you have a number of products of the same type, and you want to find out some statistical information like the minimum, maximum, or other top-level info, you would use GROUP BY.

Remember to group by the column you want information about and not the one you are applying the aggregate function on. In our above example we wanted information on the type column and the aggregate function was applied to the price column. If you missed the Aggregate Introduction Lesson, please check it out now, as it explains many concepts used in this lesson!

Below is the MySQL table "products". The "products" table that is displayed above has several products of various types. For a slight change of pace, let's count the name column to find how many products there are per type. There are 1 Food items. There are 3 Music items. There are 2 Toy items. If you haven't already read through Tizag's Aggregate Introduction Lesson, please check it out now. It explains concepts used in this lesson. Here's a visual of the "products" table.

One use of SUM might be to find the total of all the items' price for each product type. If you missed the Aggregate Introduction Lesson, please check it out now. The table we will be using is "products" and you can download the products. Our imaginary customers have been complaining recently that our prices are too high, so we would like to find out the average price of each product type to see if this is in fact the truth. I think our imaginary customers should change their view and keep buying products from us.

You can download the table used in this example, products. The products table that is displayed above has several products of various types. One use of MIN might be to find out the cheapest item in each group. The column that will have the MIN function applied to it is, of course, price. If you missed theAggregate Introduction Lesson, please check it out now, as it explains many concepts used in this lesson!

You can download the example the products. We could use the MAX function to find the most expensive item for each type of product. We will also be applying the aggregate function to the price column. This lesson will teach you how to help prevent this from happening and help you secure your scripts and MySQL statements.

Injection usually occurs when you ask a user for input, like their name, and instead of a name they give you a MySQL statement that you will unknowingly run on your database. However, the injection attack has actually made our query behave differently than we intended. By using a single quote ' they have ended the string part of our MySQL query. More Serious SQL Injection Attacks Although the above example displayed a situation where an attacker could possibly get access to a lot of information they shouldn't have, the attacks can be a lot worse.

Now that you know this is a problem, how can you prevent it? Lets try out this function on our two previous injection attacks and see how it works. Now all these queries will do is try to find a username that is just completely ridiculous:.

You have no excuse not to use it after reading this lesson! These date types are chosen for a column when you create a new table in MySQL. Often the most difficult part of using dates in MySQL is to be sure the format of the date you are trying to store matches the format of your table's date column. If you haven't already, try to create a new MySQL table with the date types we mentioned above. We have assembled a "date playground" MySQL table that can be used to follow along with this lesson.

Also, the following acronyms are used in this lesson:. Y - year segment. M - month segment. D - day segment. H - hour segment. Below is the proper format of a DATE. Date Range: to If you try to enter a date in a format other than the Year-Month-Day format then it might work, but it won't be storing them as you expect.

Date Range: to It should be noted that the range of years that can be stored are from to Below we have created another manual and automatic example to show off YEAR's use. The most straightforward to select data with SQL is to select all the records in a table. This is accomplished using the structure below:. If we wanted to only return a number of columns from a table, we could specify the column names in our SELECT statement. This follows the structure below:. In true databases, tables will have many, many more rows than our sample tables.

This follows a similar structure to a regular select statement, except we add a LIMIT clause at the end with a number of rows we want to limit the query to. To be able to more accurately filter data, we can use different operators, which are listed out below.

Within this, we can use the different operators that we showed above. This follows the format below:. We could do this using the following code:. OR statements are used when only one condition needs to be true. The table above includes any record where there userid is equal to 1 or where the total is greater or equal to In the sample below, make note of how the brackets are used to contain the OR statement:. SQL is not only useful for selecting data or maintaining databases, but also for aggregating data.

We can do this easily in SQL using our sample database by writing the following code:. Say we wanted to know what the total value of orders and count of orders were, by client, we could write:.

If we wanted to apply this to our query from the Aggregating Data example from earlier in our SQL for beginners tutorial, we could write:. However, most databases have many more tables and queries often require joining data from multiple tables. This is done using the JOIN statement.

Practically, this means that a single client can have multiple orders. Because this relationship exists, we know that we can join these two tables. An Inner Join only the rows of tables that exist in both tables.

Take the two tables as an example. If we created a new client that did not yet have any orders, that new client would not show up as he or she would not be represented within the orders table. Say we wanted to join in the first and last names of clients onto the orders table. If this runs correctly, we should not see our new client returned in the table.

What we would expect to see is that any client that does not yet have any orders would still exist in the returned data, but not have any data in the columns relating to orders.

A full outer join will return records from both tables, regardless if they exist in one and not in the other. This brings us to the end of our SQL for beginners tutorial!

Thanks so much for reading and I hope you learned everything you need to get started! You can download our printable PDF of this guide along with a complete database file by signing up for our mailing list below! We provide tons of free Python resources — check them out here! Introduction: SQL is a basic query language which every programmer must know. Master Most in Demand Skills Now! What is SQL? How is SQL Used?

What is SQLite? In this database, we have two tables: clients and orders. Primary Keys of tables are in bold. Primary keys uniquely identify a record in a table. A line is drawn between columns that have a relationship. Each client can have multiple orders — this means that the client table has a one-to-many relationship.

In order to create the two tables for our sample database, we would write the following code: When we run this command, we create our two tables. We then follow with a list of values we want to include. We ask SQL to return all columns in a table. FROM is used to specify from which table we want to return data. This follows the format below: This returns only records where both condition1 and condition2 are met.

We could do this using the following code: This returns the following table: Combining Conditions with OR Statements OR statements are used when only one condition needs to be true. To change a column name in SQL, an alias is used.