SQL Tutorial: Understanding Your Database Language

Most database use Structured Query Language (SQL). This language interfaces between you and your database tables. Even if you know another web-based scripting language (C#, PHP or VB.NET), you’ll need to know how to query data from your database. MySQL, Microsoft SQL Server and Oracle use similar language structures. While they are similar, they have some differences, but learning one language will help you understand the rest of them.

New to SQL? Learn how to work with the language at Udemy.com

A Basic Query to Retrieve Data

The “select” statement is the main phrase for retrieve data. No data is changed or deleted. It’s just retrieved for your use. You can display the data on a web page or use it to update data later on in your program. The following code is an example of a select statement:

select * from users

The above statement is probably the most basic SQL statement you can write. The statement basically says “select all records from the users table.” The asterisk tells the database to return all columns. For instance, the “users” table could have first and last name, address and signup date columns. With the above statement, you’d see them all.

Obviously, you want to filter your records. You usually want to retrieve only a certain record set. For instance, a customer logs in to your website. You then retrieve the customer’s information based on the customer’s ID (userid in this example). The following code retrieves a user with an ID of 3:

select * from users where userid=3

In addition to retrieving a filtered record set, you should also limit the number of columns returned. The asterisk returns all columns. This includes signup dates, modified dates, foreign keys and other useless information for a front-end web page. You can specify the columns returned. You should only return columns you need to work with on the front-end. The following statement selects only the first and last name columns:

select first_name, last_name from users where userid=3

There are several other types of select statement. To learn more about the SQL language, take a course at Udemy.com.

The Update Statement

The update statement is what you use to edit records. Most of your tables won’t stay static. You’ll need to change the data. The update statement is how it’s done. The following code is an example of a basic update statement:

update users set first_name=’Jane’ where userid=3

An important part of this statement is the where clause. If you forget it, you’ll update every record in the users table to “Jane.” You must ensure you include a where clause in your update statements unless you want to change every record (and you usually don’t). You can change multiple columns at a time as well. The following code changes the first and last names for the user with an ID of 3:

update users set first_name=’Jane’, last_name=’Smith’ where userid=3

The Delete Statement

The SQL delete statement removes records from a table. Again, the where clause is important in this statement as well. Without the where clause, you’ll delete every record in your table. As with the update statement, this type of mistake is disastrous and it means you’ll need to restore data from a backup.

The following code is an example of a delete statement:

delete from users where userid=3

The above code just deletes one record, but you can use the where clause to delete multiple records at a time.  You should probably limit the amount of delete statements you execute. It’s standard to set records as active or inactive instead of just deleting them. You add a “bit” column to the table and deactivate when necessary, but you still have a record of the account, order or other type of record.

Most SQL databases are relational. This means that tables are linked together using primary and foreign keys. For instance, a customer ID is stored to each order record to link customers with orders. This means that you need to delete the order before you delete the customer. If you don’t, it leaves what is called “orphaned records.” Aside from the problems with deleting records, orphaned records can cause bugs in your system.

The Insert Statement

The insert statement is how you add a record to the database table. The insert statement’s length is dependent on the data required to create a record and any default values set in the table’s column declarations. For instance, if you don’t allow nulls for a user’s first and last name, then you must include the first and last name statement in your insert statement. The alternative to this limitation is setting a default value in the table definition. You’ll set these values when you define your tables.

The following code is an example of an insert statement:

insert into users (first_name, last_name) values (‘Jane’, ‘Smith’)

The above statement adds one record. The value “Jane” and “Smith” are used for the first and last name.

This statement will throw an error if you don’t include all necessary data. For instance, most user tables have an ID column. You don’t calculate this unique ID, but the database can be set to auto-increment the column by 1 each time a new record is inserted. If the column does not increment, an error returns. These columns are also set as primary keys by some database administrators. Primary keys must be unique and cannot be null, so you must account for this issue when inserting new data.

You can also insert some default data. For instance, you should have a “Create Date” in your database. In MSSQL, you can use the following statement to insert a record with the default system date using the getDate() function:

insert into users (first_name, last_name, create_date) values (‘Jane’, ‘Smith’, getdate())

These are the four basic SQL statements you’ll need as you work with a SQL database. More complex statements exist, but you won’t need them for most operations.

Learn more complex SQL queries with a class at Udemy.com.