with SQL four basic data manipulation statements: Insert, Select, Update, and Delete.
SQL is a database user training to master a valuable asset in this article, we will guide you to master the four basic -SQL data manipulation statement is the core function - to introduce more operators in order to select three-valued logic assertions, and when you complete the study, the obviously you have begun to be proficient in SQL has.
Before we begin, first use the CREATE TABLE statement to create a table (Figure 1). DDL statements for database objects such as tables, columns, and depending on the definition they are not on the table rows to process, because DDL statements are not dealing with database actual data in these work by another type of SQL statement - data manipulation language (DML) statements for processing.
SQL DML has four basic operations: INSERT, SELECT, UPDATE, and DELETE. Since this is the most frequently used SQL user, we need them in this setting them in Figure 1, we give a table called EMPLOYEES, where each row corresponds to a specific employee record, please know This table, in the latter case we will need it.
INSERT statement
user can use INSERT statement to insert a row into a table in the specified example, to employees John Smith’s record was inserted into the table in this example, you can use the following statement:
INSERT INTO EMPLOYEES VALUES
(’Smith’, ‘John’, ‘1980-06-10 ‘,
‘Los Angles’, 16,45000);
through this INSERT statement, the system will try to fill in these values to the corresponding column in these columns when creating a table in accordance with our define the order in this case, the first value br> We have said that the system will reject an operation and return an error message.
SQL rejected if you fill in a value statement, the value of the other columns will not be filled in. This is because SQL provides support for transactions . a transaction consistent database transfer from one to another consistency of a part if the transaction fails, the entire transaction will fail, the system will be restored (or call back) to this state of affairs before .
back to the original example of the INSERT, please note that all of the plastic does not need to use a single decimal quotation marks, type the string and the value of period must be enclosed in single quotes to distinguish order to increase the available Reading between the insertion of a comma in numbers will cause an error. Remember, in SQL, the element separator is a comma.
also important to note enter text values to use single quotes. double quotes for the package gauge identifier.
for period type, we must use the SQL standard date format (yyyy-mm-dd), but in the system can be defined to accept other formats, of course, 2000 is approaching, you most Use four to represent good or copies.
Since you already understand how the INSERT statement to work, let us go to other parts of the EMPLOYEES table:
INSERT INTO EMPLOYEES VALUES
(’Bunyan’, ‘Paul’, ‘1970-07-04 ‘,
‘ Boston ‘, 12,70000);
INSERT INTO EMPLOYEES VALUES
(’John’, ‘Adams’, ‘1992-01-21′,
‘Boston’, 20,100000);
INSERT INTO EMPLOYEES VALUES
(’Smith’, ‘Pocahontas’, ‘1976-04-06′,
‘Los Angles’, 12,100000);
INSERT INTO EMPLOYEES VALUES
(’Smith’, ‘Bessie’, ‘1940-05-02 ‘,
‘ Boston ‘, 5,200000);
INSERT INTO EMPLOYEES VALUES
(’ Jones ‘,’ Davy ‘, ‘1970-10-10′,
‘Boston’, 8,45000);
INSERT INTO EMPLOYEES VALUES
(’Jones’ , ‘Indiana’, ‘1992-02-01 ‘,
‘ Chicago ‘, NULL, NULL);
the last one, we do not know Mr. Jones’ salary and wage levels So we entered NULL (not cited). NULL is a special case of the SQL, we discuss in detail later and now we just think that NULL represents an unknown value.
sometimes, like We have just discussed, we may want some, but not all, of a column assignment in addition to the input of NULL to omit the column, you can also use another kind of INSERT statement, as follows:
INSERT INTO EMPLOYEES (
FIRST_NAME, LAST_NAME,
HIRE_DATE, BRANCH_OFFICE)
VALUE (
‘Indiana’, ‘Jones’,
‘1992-02-01 ‘,’ Indianapolis’);
this way, we first listed in the table after a series of column names. not listed in the column will automatically fill in default values buy office 2010 key, if not set the default value of fill NULL. Please note that we changed the order of the columns, while the corresponding value of the order to the order of the new column if the statement omitted FIRST_NAME and LAST_NAME items (these two provisions can not be empty), SQL operation will fail.
Let’s take a look at the INSERT statement syntax diagram:
INSERT INTO table
[(column , column)]
VALUES
(columnvalue [, columnvalue]);
and the previous article, we use square brackets to indicate optional braces that can be repeated any number of items (not in the actual SQL statement to use these special characters). VALUE clause and an optional list of column names must be used parentheses.
SELECT statement
SELECT statement can select one or more tables in a particular row and column, because the database query and retrieve data is the most important function of management, so the SQL SELECT statement is part of the largest workload in fact, just to access the database analyze data and generate reports on other SQL statements one can know nothing about.
SELECT statement the result is usually to generate another table in the course of a standard system based on user selected from the database matching the rows and columns, the results into a temporary table in the direct SQL (direct SQL), it will display the results displayed in the terminal, or the results to a printer or file can also be combined with other SQL statements to put the results to a known name in the table.
SELECT statement is a powerful, though seemingly only used to complete the first part of this article mentioned the relationship between algebra, , but in fact it can be done two other relational operators - br>
SELECT columns FROM tables;
when we execute a SELECT statement in this form, the system returns the selected column from the user to select the table and all rows of a specified result table This relationship is to achieve a form of projection operator.
Let’s look at using the EMPLOYEES table in Figure 1 some examples (this table is that we should all future instances of the use of the SELECT statement while in Figure 2. and Figure 3 shows the actual query results in the other examples we will use these results).
Suppose you want to see a list of department employees. That is what you need to write the following SQL query :
SELECT BRANCH_OFFICE FROM EMPLOYEES;
execution of the SELECT statement above in Figure 2 will produce the results shown in Table 2.
Since we only specified in the SELECT statement a row, so our results only in a table column. Note that the results table with duplicate rows, because there are multiple employees in the same sector (remember the SQL from the selected value will return all rows) To eliminate duplicate rows from the result, as long as the SELECT statement with DISTINCT clause:
SELECT DISTINCT BRANCH_OFFICE
FROM EMPLOYEES;
if the results of the query Table 3.
now eliminate duplicate rows, but the results are not out of order, and if you want the results listed in alphabetical order how should do? just the ORDER BY sub- sentence can be ascending or descending order according to the results:
SELECT DISTINCT BRANCH_OFFICE
FROM EMPLOYEES
ORDER BY BRANCH_OFFICE ASC;
this query The results shown in Table 4. Please note that the ORDER BY column name followed BRANCH _OFFICE how to place, and this is what we want to sort them out, why even the results of only one column in the table must also be pointed out that we name? This is because we have other columns in the table can be sorted, even if they are not displayed. BRANCH_ OFFICE column names after the keyword ASC said in ascending order, if you want to descending order, then you can use the key word DESC.
Similarly, we should point out the ORDER BY clause only in the temporary table to sort the results; does not affect the original table.
Suppose we want to sort and from wages by sector the highest minimum wages for employees to arrange a list of employees in addition to wages, including the content, we also want to see employment in accordance with the time beginning from the recently hired employees listed in the list that you will use the following statement:
SELECT BRANCH_OFFICE, FIRST_NAME,
LAST_NAME, SALARY, HIRE_DATE
FROM EMPLOYEES
ORDER BY SALARY DESC,
HIRE_DATE DESC;
Here we had a multi-column selection and sort order of priority by the statement in the order determined by the column name. SQL will be listed in the first column of the sort office 2007 ultimate key, if in the first column appeared in repeat rows, these rows are sorted according to the second column, if in the second column has appeared in duplicate rows, these lines turn out to be sorted according to the third … … and so on. if the results of the query Table 5.
a long table to write out all the column names is a very troublesome thing, SQL allows selecting all the columns to use *:
SELECT * FROM EMPLOYEES;
The query returns the EMPLOYEES table, as shown in Table 1.
Here we give the beginning of the SELECT statement syntax, click Update (vertical line represents an option which allows a choice.):
SELECT [DISTINCT]
(column [, columns]) | *
FROM table [, table]
[ORDER BY column [ASC] | DESC
[, column [ASC]]];
defined selection criteria
described in our current SELECT statement, we list the results table to make a choice, but returns all the rows in the table Let us look at how to make a SELECT statement to limit it only to return desired line:
SELECT columns FROM tables [WHERE predicates];
WHERE clause of the conditions were set, only to meet the conditions of the line was only included to the results list. These conditions by the assertion (predicate) to specify (something about the assertion that a possible fact) if the claim is for the establishment of a given line, the line will be included in the result table, or the line is ignored. assertions in the SQL statement is usually expressed by comparing, for example, if you need to check all the staff of the surname Jones, you can use the following SELECT statement:
SELECT * FROM EMPLOYEES
WHERE LAST_NAME = ‘Jones’;
LAST_NAME = ‘Jones’ part is the assertion in the statement is executed, SQL LAST_NAME column of each row with the the claim holds, the employee’s information will be included in the result table (see Table 6).
use up to six more
we include the example assertions based on equivalent >
less than <
than>
less than or equal to < =
greater than or equal to> =
below shows the equivalent comparison is not based on an example:
SELECT * FROM EMPLOYEES
WHERE SALARY> 50000;
the query will return higher than the $ 50,000.00 paid staff (see Table 7).
logical connectors
sometimes we need to define more than one assertion of a SELECT statement for example, if you just want to see Davy Jones’ message, then the results in Table 6 is incorrect. In order to further define a WHERE clause, you can use logical connectors AND, OR and NOT. staff of Davy Jones to only get the records, the user can type the following statement :
SELECT * FROM EMPLOYEES
WHERE LAST_NAME = ‘Jones’ AND FIRST_NAME = ‘Davy’;
In this case, we will be two logical connectors AND assertions connected and only two assertions are met will meet the entire expression if the user needs to define a SELECT statement to make when any of them set up to meet the conditions, you can use the OR connector:
SELECT * FROM EMPLOYEES
WHERE LAST_NAME = ‘Jones’ OR LAST_NAME = ‘Smith’;
sometimes the best way to define an assertion is described to illustrate the opposite, if you want to see In addition to the staff of the Boston office of all officers other than the information, you can make the following query:
SELECT * FROM EMPLOYEES
WHERE NOT (BRANCH_OFFICE = ‘Boston’);
NOT followed by the keyword enclosed in parentheses to use more expression. The result is a negative result to take, if a staff member’s department offices in Boston, including the expression returns true, but NOT operation negated the value of character cheap office 2007 key, so the line will not be selected.
assertions can be used with other assertions nested order to ensure the correct order they are evaluated, you can enclose them with brackets:
SELECT * FROM EMPLOYEES
WHERE (LAST_NAME = ‘Jones’
AND FIRST_NAME = ‘Indiana’)
OR (LAST_NAME = ‘Smith’
AND FIRST_NAME = ‘Bessie’);
SQL follows standard mathematical expression evaluation of the agreement - the expression within the parentheses will be evaluated first, and other expressions from evaluated left to right.
above the logical connectors are described in the description of the contents of the following, we once again update the syntax of the SELECT statement:
SELECT [ ,],[br>
[, column [ASC]]]
WHERE predicate [logical-connector predicate];
NULL and three-valued logic
In SQL, NULL is a complex topic, a detailed description of NULL in SQL is more suitable for advanced tutorials and introductory tutorial not now be introduced, but the need for special treatment as NULL, and you are likely to encounter to it, so we look briefly to explain.
First windows 7 ultimate key, the assertion in NULL to determine the need for special syntax, for example, if you need to display all paid staff of all the unknown information, the user can use the following SELECT statement:
SELECT * FROM EMPLOYEES
WHERE SALARY IS NULL;
Conversely, if the user needs all known information about the staff salary data you can use the following statement:
SELECT * FROM EMPLOYEES
WHERE SALARY IS NOT NULL;
Please note that we used in the column names after the keyword IS NULL or IS NOT NULL, rather than Comparison of the standard form: COLUMN = NULL, COLUMN <> NULL or a logical operator NOT (NULL).
this form is quite simple, but when you do not explicitly test for NULL (and they do exist ), things become very confusing.
example, we look back in Figure 1 EM-PLOYEES table, you can see the Indiana Jones of the wage or salary level values are unknown. two a column contains NULL. imagine running the following query:
SELECT * FROM EMPLOYEES
WHERE GRADE < = SALARY;
this time, Indiana Jones should appear in the result table, because NULL is equal, so imagine they are less than or equal GRADE SALARY through the check, which no doubt is a query, but it did not matter. SQL to allow such a comparison, as long as two types of columns are numbers, however, Indiana Jones does not appear in query results, and why?
As we previously mentioned, NULL indicates an unknown value (and not, as some people might expect that a value is NULL) for SQL mean this value is unknown, but as long as this value is unknown, can not be compared with other values (even if other value is NULL). Therefore, SQL allows In addition to true and false in addition there are types of true value, called assert a non-inverted or used to determine the AND or OR after the merger with the other assertions, the result is still non-identified because of the result table includes only assertion is need to use the special operators IS NULL and IS NOT NULL.
UPDATE statement
UPDATE statement allows the user to a table known to modify the existing line.
For example, we just found out that Indiana Jones is rated 16, the wage of $ 40,000.00, we can use the following SQL statement to update the database (and remove those pesky NULL).
UPDATE EMPLOYEES
SET GRADE = 16, SALARY = 40000
WHERE FIRST_NAME = ‘Indiana’
AND LAST_NAME = ‘Jones’;
above example illustrates a single update, but UPDATE statement can operate on multiple lines. satisfy the WHERE condition of all rows are updated, if you want all the staff in the Boston office moved to New York, you can use the following statement:
UPDATE EMPLOYEES
SET BRANCH_OFFICE = ‘New York’
WHERE BRANCH_OFFICE = ‘Boston’;
If you omit the WHERE clause, all rows in the table values will be the department update for the ‘New York’.
UPDATE statement syntax flow diagram as shown below:
UPDATE table
SET column = value [, column = value]
[WHERE predicate [logical-connector predicate]]; DELETE statement DELETE statement to delete rows in the table are known. the same as the UPDATE statement , all satisfy the WHERE clause in the conditions rows are deleted. because there is no UNDO SQL statements or offices and lay off all the office staff, this vile can be done by the following statement to achieve this: DELETE FROM EMPLOYEES WHERE BRANCH_OFFICE = ‘Los Angeles’; the same as the UPDATE statement, omit the WHERE clause will make the operation applied to the table all the rows. DELETE statement syntax flow diagram as shown below: DELETE FROM table [WHERE predicate [logical-connector predicate]]; now we have completed the data manipulation language (DML) statement describes the main we are not able to complete all of the SQL function description. SQL also provides a number of functions, such as averages, sums and other calculations on data in the table, SQL can also complete the query from multiple tables (multi-table query, or so-called connection ) work. This language also allows you to use GRANT and REVOKE commands control user access to data.
Read Category: Database View commentsRelated Articles:
