Your Web News in One Place

Help Webnuz

Referal links:

Sign up for GreenGeeks web hosting
November 24, 2022 02:03 am

SQL for Beginners


Most modern web applications today interact with databases, usually with a language called SQL. Lucky for us, this language is quite easy to learn. In this article, we are going to start with some basic SQL queries and use them to interact with a MySQL database.


What You Need


SQL (Structured Query Language) is a language designed for interacting with relational database management systems (RDBMS), like MySQL, Oracle, or SQLite. To perform the SQL queries in this article, I suggest that you have MySQL installed. I also recommend phpMyAdmin as a visual interface to MySQL.


The following applications make it very easy to install both MySQL and phpMyAdmin on your local machine:



We are going to use the command line for queries. WAMP already comes with a MySQL console.


Creating a Database


Here comes our very first query. We are going to create a database to work with.


Firstly, open up your MySQL console and login. For WAMP, the default password is blank. For MAMP, the password should be root by default.


After logging in, type this query and hit enter:



Create a DatabaseCreate a DatabaseCreate a Database

Note that semicolon (;) is added at the end of the query, just like at the end of lines of code in JavaScript.


Also, the special words CREATE DATABASE are case insensitive, along with all special words in SQL. But for the sake of readability and convention, we will be writing them in uppercase.


List All Databases


The following query is used to get a list of all databases you have.



Show DatabasesShow DatabasesShow Databases

Delete a Database


You can delete an existing database with the following query.



Drop a DatabaseDrop a DatabaseDrop a Database


Be careful with this query, because it gives you no warnings. If you have tables and data under the database, they will all be deleted instantly.


It's always a good practice to take a backup of your database before you delete it. Later on, we'll see how you can take a complete backup of your database.


Selecting a Database


You can use the USE keyword to select a database you want to operate with. It is a statement and does not require a semicolon at the end.



Select a DatabaseSelect a DatabaseSelect a Database

It tells MySQL to select a default database to work with, for the current session. Now we are ready to create tables and do other things under this database.


What is a Database Table?


You can think of a database table like a spreadsheet or CSV file which holds structured data as shown in the following screenshot.


DB Table StructureDB Table StructureDB Table Structure

Just like in this example, tables have column names, and rows of data. With SQL queries we can create these tables. We can also add, read, update and delete the data.


Creating a Table


With this query we can create tables in the database. Unfortunately the MySQL documentation is not very friendly for new learners. The structure of this type of query can get very complex, but we will start with an easy one.


The following query will create a table with two columns.



Note that we are able to write a query in multiple lines, and even use tabs for indentation.


The first line is easy. We just create a table named users. Following that, in parentheses, we have a list of table columns separated by commas. After each column name, we have a data type, such as VARCHAR or DATE.


The VARCHAR(20) data type means that the column is a string type, and can be a maximum of twenty characters long. The DATE is also a data type which is specifically used for storing dates, in this format: YYYY-MM-DD.


Primary Keys


Ideally, we should also include a column of user_id, which will be a primary key. Without getting too much into the details, you can think of a primary key as a way to identify each row in a table. It's a unique number to identify any row and can't be duplicated for multiple rows.


Now the query should look like:



The INT data type makes this a 32 bit integer type (ie. numeric). The AUTO_INCREMENT keyword automatically generates a new id number every time we add new rows of data. It is not required, but makes it much more convenient.


This column does not have to be an integer, but it is the most commonly used type. Having a PRIMARY KEY column also is not required, but it is strongly recommended for good database design and performance.


Let's run the query:


How to Run a QueryHow to Run a QueryHow to Run a Query

List All Tables


The following query allows you to get a list of tables that are currently in the database.



List All TablesList All TablesList All Tables

Show Table Structure


To see the structure of an existing table, you can use the following query.



Show Table StructureShow Table StructureShow Table Structure

Fields (aka. columns) are listed in the results, with their properties.


Delete a Table


Just like the DROP DATABASES command, the following query deletes a table and its contents, without a warning.



Drop a TableDrop a TableDrop a Table

Again, be very carful with any DROP operation!


Modify a Table


You can modify a table with the ALTER query. This query also can have quite a complex structure because of the multitude of changes it can perform on a table. Let's look at some simple examples.


Make sure to re-create the table we just dropped or the following queries obviously won't work!


Add a Column



Thanks to the readability of SQL, I don't think this even needs an explanation. You can just the ADD keyword to add a new column in the ALTER query.


Add a ColumnAdd a ColumnAdd a Column

Remove a Column


In the same way, You can just the DROP keyword to remove an existing column in the ALTER query.



Remove a ColumnRemove a ColumnRemove a Column

It is also very simple. But use it with caution as it permanently removes data without a warning.


Re-add the email column because we are going to use it later:



Modify a Column


Sometimes you may want to change the properties of a column, so you don't have to delete and recreate it.


Let's have a look at the following query.



Modify a ColumnModify a ColumnModify a Column

It would rename the username column to user_name and change the type from VARCHAR(20) to VARCHAR(30). A change like this should not disturb any of the existing data in the table.


Add Data to a Table


Let's see how to add some data into the table using the following query.



Add Data to TableAdd Data to TableAdd Data to Table

As you can see, VALUES() contains the list of field values, separated by commas. The string values are enclosed in single quotes. And the values need to be in the order of the columns that were defined when we created the table.


Note that the first value is NULL for the primary key field we created, which is user_id. We do this so that an id is automatically generated, because the column is set to AUTO_INCREMENT. When entering a row of data for the first time, the id will be 1. Next inserted row will be 2 and so on and so forth.


In this way, you can add more data in the users table.


Alternate Syntax—SET


Here is another syntax for inserting rows.



Add Data to TableAdd Data to TableAdd Data to Table

In the above query, we are using the keyword SET instead of VALUES, and it is not followed by parentheses. This is safer than VALUES, because there is less danger of forgetting or mixing up the order of columns. There are a few things to note here:



  • A column can be omitted. For example we did not assign a value to user_id, which will default to the AUTO_INCREMENT functionality. If you omit a VARCHAR column, it would default to an empty string (unless a different default value was specified during table creation).

  • Each column has to be referenced by its name. Because of this, they can be in any order, unlike the previous syntax.


Alternate Syntax—Named Values


Here is yet another syntax. In this case we tell the INSERT statement what columns we are supplying values for, and then use the VALUES keyword to pass those values.



Add Data to TableAdd Data to TableAdd Data to Table

Again, since each column is referenced by name, they can be in any order.


The LAST_INSERT_ID() Function


You can use the following query to get the AUTO_INCREMENT id for the last inserted row, in the current session.



Get Last Insert IDGet Last Insert IDGet Last Insert ID

The NOW() Function


I think it is a good time to demonstrate how you can use a MySQL function inside your queries.


The NOW() function returns the current date. So you can use it to automatically set a DATE column to the current day while inserting a new row.



Get Current TimeGet Current TimeGet Current Time

Note that we received a warning from MySQL, but it is not a big deal. The reason is that NOW() actually returns time information as well.


Get Current TimeGet Current TimeGet Current Time

But the create_date column we created only contains the DATE, and not the time, therefore the returned data was truncated. We could use the CURDATE() function instead, which returns just the date, but the data stored at the end would be the same either way.


Read Data From a Table


Obviously the data we added would be useless unless we can read it. This is where the SELECT query comes in.


Here is the simplest possible SELECT query for reading from a table:



Read Data From TableRead Data From TableRead Data From Table

In this case, the asterisk (*) means that we asked to fetch all the columns from the table. If you want only specific columns, the query would look like this:



Read Data From TableRead Data From TableRead Data From Table

The WHERE Clause


More often than not, we are only interested in some of the rows, and not all. For example, let's say we want the email address for the tutsplus user.


To retrieve it, you can use the following query.



Where ClauseWhere ClauseWhere Clause

Think of it like an IF statement. The WHERE clause allows you to put conditions in the query for the results you are looking for.


Note that for the equality condition, only a single equal sign is used (=), instead of double equals (==) which you might be used to from programming.


You can use other comparison conditions too:



Where ClauseWhere ClauseWhere Clause

The AND and OR operators can be used to combine conditions:



And and Or ConditionsAnd and Or ConditionsAnd and Or Conditions

Note that numeric values do not have to be inside quotes.


The IN Operator


The IN operator is useful for matching multiple values.



IN ClauseIN ClauseIN Clause

The LIKE Operator


The LIKE operator allows you to do wildcard searches.



LIKE OperatorLIKE OperatorLIKE Operator

The percentage sign (%) is used as the wildcard.


The ORDER BY Clause


If you want the results to be returned in a specific order, you can use the ORDER BY clause.



ORDER BY ClauseORDER BY ClauseORDER BY Clause

The default order is ASC (ie. ascending). You can add DESC to order it by descending order.


The LIMIT ... OFFSET Clause


With the help of the LIMIT and OFFSET clause, you can limit the number of returned results.



LIMIT and OFFSET UseLIMIT and OFFSET UseLIMIT and OFFSET Use

The LIMIT 2 clause just gets the first 2 results. The LIMIT 1 OFFSET 2 gets 1 result, after the first 2 results. The LIMIT 2, 1 means the same thing, but note that the first number is the offset and the second number is the limit.


The GROUP BY Clause


The GROUP BY clause is really useful when you want to aggregate the data based on any particular column. Let's assume that in our users table, it has country_id column, which stores the country of the user. Now, if you want to get the count of all users based on country, you can use the GROUP BY clause to achieve it.


The query would look something like this.



Group By ClauseGroup By ClauseGroup By Clause

The JOIN Clause


The JOIN clause helps you to select common data from two different tables. Ideally, both tables should contain a column which is common among those two tables.


In our example, we've users table which contains the user_id column. Now, let's assume that we've another table called user_details, which contains user_id, user_company_name and user_ssn_no columns. The user_details table is used to store extra information about users. In this case, we've the user_id column in both tables and it's common, so you can use the JOIN clause to retrieve all the user information by using a single query.


Let's have a look the following query.



It would give you all the details of all users in a single query. There are different types of joins like LEFT JOIN, RIGHT JOIN and INNER JOIN. The INNER JOIN is one of the most commonly used join amongst all.


The HAVING Clause


The HAVING clause is generally used along with the the GROUP BY clause. You can think of it as a WHERE clause for the aggregated columns like COUNT, SUM etc. So basically, when you want to filter results based on the aggregate columns, you need to use the HAVING clause.


In the earlier section, we discussed how you can find total users group by the country_id column. We can extend the same example and filter the countries that have more than two users.


Let's have a look at the following example.



Having ClauseHaving ClauseHaving Clause

Update Data in a Table


The following query is used to update the data in a table.



Update Data in a TableUpdate Data in a TableUpdate Data in a Table

Most of the time, it is used with a WHERE clause, because you would want only specific rows to be updated. If a WHERE clause is not provided, all rows would be updated with the same changes! So, you have to be careful when you use UPDATE query.



An UPDATE query with no WHERE clause will write the same data to every record in the table—permanently erasing the previous data. Be very careful with this query!


You can also use a LIMIT clause to limit the number of rows to be updated.



Update with LIMIT ClauseUpdate with LIMIT ClauseUpdate with LIMIT Clause

Delete Data From a Table


Just like UPDATE, the following query is also usually used with a WHERE clause.



Delete Data From TableDelete Data From TableDelete Data From Table


An DELETE query with no WHERE clause will erase every record in the table—permanently. Be very careful with this query!


Truncate a Table


To delete the entire contents of a table, you can just do this:



But it is usually more performance efficient to use TRUNCATE instead.


TRUNCATE TableTRUNCATE TableTRUNCATE Table

The TRUNCATE clause also resets the AUTO_INCREMENT numbers so a new row will again start from the beginning. But this does not happen with a DELETE query, and the counter keeps going up.


Escaping String Values and Special Words


String Values


Certain characters need to be escaped, otherwise you can have problems.


Let's have a look at the following query.



Escape ValuesEscape ValuesEscape Values

The backslash (\) is used for escaping. That ensures that the character (for example the ' character in this example) is treated as part of the string, ignoring it's special meaning in SQL. 


This is also very important for security reasons. Any user input going into the database needs to be properly escaped. In PHP, you use the mysql_real_escape_string() function or use prepared statements since they do escaping automatically.


Special Words


Since MySQL has many special words like SELECT or UPDATE, you can prevent collision by putting quotes around your table and column names. But these are not the regular quotes; you need to use the back-tick (`) character.


Let's say you want to add a column named delete for some reason:


Special WordsSpecial WordsSpecial Words

As you can see, when we tried to run the query without back-tick character, MySQL gave us the warning. And when we used the back-tick character, it allowed us to run the query successfully.


How to Backup a Database


In this section, we'll quickly see how can take a backup of your MySQL database with the help of the command line console.


Let's have a look at the following command.



You need to replace the placeholders as required. The {HOSTNAME} should be replaced with your server name, it would be localhost if you are working locally. The {USERNAME} should be replaced with your MySQL username. The {DATABASENAME} should be replaced with your MySQL database name you want to take backup of. When you press enter to run the above command, it would ask for the password, since we've supplied the -p parameter. You just need to enter your MySQL password and it would make a backup of whole database into the backup.sql file.


How to Restore Backup


A backup isn't much use without a way to restore it!


Have a look at the following command.



This will restore the database.


Conclusion


Hopefully in this article I was able to convince you that SQL is very powerful, yet is easy to learn.



Original Link: https://code.tutsplus.com/articles/sql-for-beginners--net-8200

Share this article:    Share on Facebook
View Full Article

TutsPlus - Code

Tuts+ is a site aimed at web developers and designers offering tutorials and articles on technologies, skills and techniques to improve how you design and build websites.

More About this Source Visit TutsPlus - Code