This lesson will teach you how to use SQL to access and manipulate data in: MySQL, SQL Server, Access, Oracle, Sybase, DBZ, and other database systems.
SQL Syntax
SELECT Company, Country FROM Customers WHERE Country <> 'USA'
SQL AND & OR Operators
The AND & OR operators are used to filter records based on more than one condition.
In the previous section, we have seen that the WHERE keyword can be used to conditionally select data from a table. This condition can be a simple condition, or it can be a compound condition. Compound conditions are made up of multiple simple conditions connected by AND or 0R. There is no limit to the number of simple conditions that can be present in a single SQL statement.
The AND & OR Operators
The AND operator displays a record if both the first condition and the second condition is true.
OR operator displays a record if either the first condition or the second condition is true.
Now we want to select only the persons with the first name equal to “John” AND last name equal to “Jameson”:
We use the following SELECT statement:
The result-will look like this:
OR Operator example:
Now we want to select only the persons with the first name equal to “John” OR the first name equal to “Kate”:
We use the following SELECT statement:
Combining AND & OR
You can also combine AND and OR (use parenthesis to form complex expressions).
Now we want to select only the persons with the last name equal to “Jameson” AND the firstname equal to “John” OR to “Kate”:
We use the following SELECT statement:
SQL Delete Statement
The DELETE statement is used to delete records in a table
The DELETE statement is used to delete rows in a table.
Note: Notice the WHERE clause in the DELETE syntax. The WHERE clause specifies which record or records that should be deleted. If you omit the WHERE clause, all records will be deleted!
Now we want to delete the person “Mason, Adam” in the “Persons” table
We use the following SQL statement:
Delete all rows
It is possible to delete all rows in a table without deleting the table. This means that the table structure, attributes, and indexes will be intact:
Note: Be very careful when deleting records. You cannot undo this statement!
SQL SELECT DISTINCT Statement
The SELECT keyword allows us to grab all information from a column (or columns) on a table. This, of course, necessarily mean that there will be redundancies. What if we only want to select each
DISTINCT element? This is easy to accomplish in SQL. All we need to do is to add DISTINCT after SELECT.
The SQL SELECT DISTINCT Statement
In a table, some of the columns may contain duplicate values. This is not a problem, however, sometimes you will want to list only the different (distinct) values in a table.
The DISCTINCT keyword can be used to return only distinct (different) values.
Now we want to select only the distinct values from the column named “City” from the table above.
We use the following SELECT statement:
SQL INSERT INTO Statement
The INSERT INTO statement is used to insert new records in a table.
The INSERT INTO Statement
The INSERT INTO statement is used to insert a new row in a table.
SQL INSERT INTO Syntax
It is possible to write the INSERT INTO statement in two forms.
The first form doesn’t specify the column names where the data will be inserted, only their values:
SQL INSERT into Example
We have the following “Persons” Table
Now we want to insert a new row in the “Persons” table
We use the following SQL statement:
Insert Data Only in Specified Columns
It is also possible to only add data in specific columns.
The following SQL statement will add a new row, but only add data in the “P_Id”, “LastName” and the “FirstName” columns:
SQL ORDER BY Keyword
So far, we have seen how to get data out of a table using SELECT and WHERE commands. Often, however, we need to list the output in a particular order. This could be in ascending order, in descending order, or could be based on either numerical value or text value. In such cases, we can use the ORDER BY keyword to achieve our goal.
The ORDER BY Keyword
The ORDER BY keyword is used to sort the result-set by a specified column.
ORDER BY keyword sort the records in ascending order by default
If you want to sort the records in a descending order, you can use the DESC keyword.
ORDER BY Example
The “Persons” table:
The result-set look like this:
ORDER BY DESC Example
Now we want to select all the persons from the table above, however, we want to sort the persons descending by their last name.
We use the following SELECT statement:
SQL SELECT Statement
What do we use SQL commands for? A common use is to select data from the tables located in a database. Immediately, we see two keywords: we need to SELECT information FROM a table.
Note that a table is a container that resides in the database where the data is stored.
The SELECT statement is used to select data from a database.
The result is stored in a result table, called the result-set.
Note: SQL is not case sensitive. SELECT is the same as select.
SQL SELECT Example :
The “Persons” table:
We shall use this table as an example throughout the tutorial ( this table will appear in all sections).
Now we want to select the content of the columns named
“ LastName” and “FirstName” from the table above.
WE use the following SELECT statement:
Now we want to select all the columns form the “Persons” table.
We use the following SELECT statement:
SELECT * FROM Persons
Tip: the asterisk (*) is a quick way of selecting all columns!
The result-set will look like this:
Navigation in a Result-set
Most database software systems allow navigation in the result –set with programming functions like: Move-To-First-Record, Get-Record-Content, Move-to-Next-Record, etc.
Programming functions like these are not a part of this tutorial. To learn about accessing data with function calls, please read ADO manual or PHP manual.
Walang komento:
Mag-post ng isang Komento