Master the four basic data operation statements of SQL: insert, select, update and delete.
Mastering SQL is a valuable asset for database users. In this paper, we will guide you to master four basic data operation statements-the core function of ——SQL-and introduce comparison operators, selective assertions and ternary logic in turn. When you have finished learning this, it is obvious that you have begun to master SQL.
Before we begin, use the CREATE TABLE statement to create a table (as shown in figure 1). DDL statements define database objects such as tables, columns, and views. They do not process rows in the table because DDL statements do not process actual data in the database. These tasks are handled by another SQL statement, namely a data manipulation language (DML) statement.
There are four basic DML operations in SQL: insert, select, update and delete. Since these are frequently used by most SQL users, it is necessary for us to explain them here. In figure 1, we give a table named EMPLOYEES. Each row corresponds to a specific employee record. Please be familiar with this table. We will use it in the following example.
Insert statement
Users can use the INSERT statement to insert a row of records into the specified table. For example, in this example, to insert the record of employee John Smith into the table, you can use the following statement:
Insert employee values
('Smith',' John',' 1980-06- 10',
Los Angeles',16,45000);
With such an INSERT statement, the system will try to fill these values into the corresponding columns. These columns are arranged in the order defined when we created the table. In this example, the first value "Smith" will be filled in the first column LAST _ NAME and the second value "John" will be filled in the second column FIRST_NAME … and so on.
We said that the system would "try" to fill in the values, and besides executing the rules, it would also perform type checking. If the types do not match (for example, a string is filled in a column of type number), the system will reject the operation and return an error message.
If SQL rejects the value of one column you fill in, the values of other columns in the statement will not be filled in. This is because SQL provides support for transactions. Transactions transfer a database from one consistency to another. If a part of a transaction fails, the whole transaction will fail and the system will revert (or roll back) to the state before the transaction.
Back to the original example of INSERT, please note that all integer decimals do not need to be enclosed in single quotes, but string and date type values should be distinguished by single quotes. Inserting commas between numbers to increase readability can lead to errors. Remember, commas are separators for elements in SQL.
When entering literal values, you should also pay attention to using single quotation marks. Double quotation marks are used to encapsulate the license identifier.
For the date type, we must use the SQL standard date format (yyyy-mm-dd), but it can be defined in the system to accept other formats. Of course, as the year 2000 approaches, you'd better use four digits to represent the year.
Now that you understand how the INSERT statement works, let's move on to the rest of the EMPLOYEES table:
Insert employee values
('Bunyan',' Paul',' 1970-07-04',
Boston',12,70000);
Insert employee values
('John',' Adams','1992-01-21',
Boston', 20,100000);
Insert employee values
('Smith',' Pocahontas in the Wind',' 1976-04-06',
Los Angeles', 12,100000);
Insert employee values
('Smith',' Bessie',' 1940-05-02',
Boston, 5.2 million pounds);
Insert employee values
('Jones',' David','1970-10-10',
Boston', 8,45000);
Insert employee values
('Jones',' Indiana',' 1992-02-0 1',
Chicago', NULL, null);
Finally, we don't know Mr. Jones's salary level and annual salary, so we enter NULL (without quotation marks). NULL is a special case in SQL, which we will discuss in detail later. Now we just need to think that NULL represents an unknown value.
Sometimes, as we just discussed, we may want to assign values to some columns, but not all columns. In addition to entering NULL for the column to be omitted, you can use another INSERT statement, as follows:
Insert into employee (
First name, surname,
Date of employment, branch)
Value (
Indiana, Jones,
1992-02-0 1',' Indianapolis');
In this way, we first list a series of column names after the table name. Unlisted columns will be automatically filled with default values, and NULL will be filled if no default values are set. Note that we changed the order of the columns, and the order of the values should correspond to the order of the new columns. If the FIRST_NAME and LAST_NAME items are omitted from this statement (these two items cannot be empty), the SQL operation will fail.
Let's take a look at the syntax diagram of the INSERT statement above:
Insert table
[(column {，column})]
values
(columnvalue [{，column value }])；
As in the last article, we use square brackets to indicate options and braces to indicate items that can be repeated any number of times (these special characters cannot be used in actual SQL statements). Parentheses must be used in the VALUE clause and the optional list of column names.
select order
SELECT statements can select specific rows and columns from one or more tables. Because querying and retrieving data are the most important functions in database management, SELECT statement is the most workload part in SQL. In fact, people who only access the database to analyze data and generate reports may know nothing about other SQL statements.
The result of the SELECT statement is usually to generate another table. During the execution, the system selects matching rows and columns from the database according to the user's criteria, and puts the results in a temporary table. In direct SQL, it displays the results on the display screen of the terminal, or sends the results to a printer or file. You can also combine other SQL statements to put the results into a table with a known name.
SELECT statement is powerful. Although on the surface, it is only used to complete the relational algebra operation "selection" (or "restriction") mentioned in the first part of this paper, in fact, it can also complete two other relational operations-"projection" and "connection", and the select statement can also complete aggregation calculation and data sorting.
The simplest syntax of the SELECT statement is as follows:
Select a column from the table;
When we execute the SELECT statement in this form, the system returns a result table, which consists of the selected column and all the specified rows in the table selected by the user. This is a form of realizing relational projection operation.
Let's look at some examples of using the EMPLOYEES table in figure 1 (this table will be used by all SELECT statement instances in the future. We give the actual results of the query in Figure 2 and Figure 3. We will use these results in other examples).
Suppose you want to see a list of departments where employees work. Here are the SQL queries you need to write:
Select BRANCH _ OFFICE from employees.
Executing the above SELECT statement will produce the result as shown in Table 2 in Figure 2.
Because we only specified one column in the SELECT statement, there is only one column in the result table. Note that there are duplicate rows in the result table because there are multiple employees working in the same department (remember, SQL returns values from all selected rows). To eliminate duplicate rows in the result, just add a DISTINCT clause in the SELECT statement:
Choose a different branch
From employees;
The results of this query are shown in Table 3.
Duplicate rows have now been eliminated, but the results are not in order. What if you want to list the results in alphabetical order? As long as you use the ORDER BY clause, you can sort the results in ascending or descending order:
Choose a different branch
From employees
Sort by branch ASC;
The results of this query are shown in Table 4. Notice how the column name BRANCH _OFFICE is placed after the ORDER BY, which is the column we want to sort. Why should we point out the column name even if there is only one column in the result table? This is because we can also sort by other columns in the table, even if they are not displayed. The keyword ASC after the column name BRANCH_ OFFICE indicates that it is arranged in ascending order. If you want to sort in descending order, you can use the keyword desc.
Similarly, we should point out that the ORDER BY clause only sorts the results in the temporary table; Does not affect the original table.
Suppose we want to get a list sorted by department, from the highest paid employee to the lowest paid employee. In addition to the content in the salary scale, we also want to see a list of recently hired employees arranged by employment time. Here are the sentences you will use:
SELECT branch, name,
Last name, salary, employment date
From employees
DESC by salary,
Date of employment of DESC;
Here we have selected and sorted multiple columns. The priority of sorting is determined by the order of column names in the statement. SQL will sort the first column listed first. If there are duplicate rows in the first column, they will be sorted by the second column, if there are duplicate rows in the second column, they will be sorted by the third column, and so on. The results of this query are shown in Table 5.
Writing all the column names in a long table is quite troublesome, so SQL allows you to use the * symbol when selecting all the columns in the table:
SELECT * FROM EMPLOYEES
The query returns the entire EMPLOYEES table, as shown in table 1.
Let's update the syntax of the SELECT statement given at the beginning (the vertical bar indicates an option and allows you to choose one. ):
Select [different]
(column [{,column}]) | *
From table [{,table}]
[sort by column [ASC] | DESC
[{,column [ASC] | desc}]];
Define selection criteria
In the SELECT statement we introduced so far, we selected the columns in the result table, but returned all the rows in the table. Let's see how to restrict the SELECT statement to return only the rows you want:
Select the column [WHERE predicate] from the table;
The WHERE clause sets the conditions, and the result table only contains the qualified rows. These conditions are specified by a predicate (an assertion represents a possible fact about something). If the assertion of a given row holds, it will be included in the result table, otherwise it will be ignored. Assertions in SQL statements are usually expressed by comparison. For example, if you need to query all employees whose last name is Jones, you can use the following SELECT statement:
SELECT * FROM employees
Where LAST _ NAME =' Jones
The LAST_NAME = 'Jones' part is an assertion. When this statement is executed, SQL compares the LAST_NAME column of each row with "Jones". If an employee's surname is "Jones", the assertion is valid, and the employee's information will be included in the result table (see Table 6).
Six most commonly used comparisons
The assertions in our last example include comparisons based on "equivalence" (LAST_NAME = 'Jones'), but SQL assertions can also include several other types of comparisons. The most commonly used are:
be qualified for sth
unequal to
absent
Greater than >
less than or equal to
Greater than or equal to > =
The following is an example that is not based on equivalence comparison:
SELECT * FROM employees
In which salary & gt50000;
The query will return employees whose annual salary is higher than $50,000.00 (see Table 7).
Logical connector
Sometimes we need to define a SELECT statement with multiple assertions. For example, if you only want to see information about Davey Jones, the results in Table 6 will be incorrect. To further define the WHERE clause, users can use logical connectors AND, or and NOT. In order to get only the record of employee Davey Jones, the user can enter the following statement:
SELECT * FROM employees
Where last name =' Jones' and first name =' David';
In this example, we connect two assertions through the logical connector AND. Only when both assertions are satisfied can the whole expression be satisfied. If the user needs to define a SELECT statement so as to meet the conditions when any one of the statements holds, 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 to explain it through the opposite description. If you want to check the information of all employees except the Boston office, you can perform the following query:
SELECT * FROM employees
WHERE NOT(BRANCH _ OFFICE = ' Boston ')；
The NOT keyword is followed by a comparison expression enclosed in parentheses. The result is a negative result. If the office of the employee's department is in Boston, the expression in parentheses returns true, but the NOT operator inverts the value, so the row will not be selected.
Assertions can be nested with other assertions. To ensure that they are calculated in the correct order, you can enclose them in parentheses:
SELECT * FROM employees
Where (last name =' Jones'
And FIRST_NAME = 'Indiana')
Or (last name =' Smith'
And first _ name =' Bessie');
SQL follows the mathematical standard expression evaluation convention-the expressions in brackets are evaluated first, and other expressions are evaluated from left to right.
The logical connector is as described above. Before that, we update the syntax of the SELECT statement again:
Select [different]
(column [{,column}]) | *
From table [{,table}]
[sort by column [ASC] | [DESC
[{,column [ASC] | [DESC}]]
WHERE predicate [{logical connection predicate}];
Zero-sum ternary logic
NULL is a complicated topic in SQL, and the detailed description of NULL is more suitable to be introduced in the advanced tutorial of SQL than in the current introductory tutorial. But because NULL needs special treatment, you are likely to encounter it, so let's explain it briefly.
First of all, special syntax is needed when making empty judgments in assertions. For example, if the user needs to display all the information of all employees whose annual salary is unknown, the user can use the following SELECT statement:
SELECT * FROM employees
Where the salary is empty;
On the contrary, if the user needs to know the information of all employees with annual salary data, the following statement can be used:
SELECT * FROM employees
Where salary is not empty;
Note that we use the keyword IS NULL or IS NOT NULL after the COLUMN name instead of the standard comparison form: column = NULL, column.
This form is very simple. But when you don't explicitly test NULL (they do exist), things get messy.
For example, if we look back at the employees table in figure 1, we can see that Indiana Jones' salary grade or annual salary is unknown. Both columns contain NULL. Imagine running a query like this:
SELECT * FROM employees
Where grade & lt= salary;
At this point, Indiana Jones should appear in the results table. Because all zeros are equal, it is conceivable that they can pass the check of rank less than or equal to salary. This is actually an unquestionable query, but it is not important. As long as both columns are numbers, SQL allows this comparison. However, Indiana Jones did not appear in the query results. Why?
As we mentioned earlier, NULL means an unknown value (not a null value as some people think). For SQL, it means that this value is unknown. As long as this value is unknown, it cannot be compared with other values (even if other values are empty). So SQL allows a third kind of truth besides true and false, which is called "unknown" value.
If both sides of the comparison are empty, the whole assertion is considered uncertain. After reversing an uncertain assertion OR merging it with other assertions using AND or OR, the result is still uncertain. Because the result table only includes rows with an asserted value of "true", NULL cannot satisfy this check. Therefore, it is necessary to use special operators IS NULL and IS NOT NULL.
Update statement
The UPDATE statement allows users to modify existing rows in a known table.
For example, we just found out that Indiana Jones has a grade of 16 and a salary of $40,000.00. We can update the database (and clear those annoying null values) through the following SQL statements.
Update employees
Setting grade = 16, salary = 40000.
Where name =' Indiana'
And LAST _ NAME =' Jones.
The above example illustrates a single-line UPDATE, but the update statement can operate on multiple lines. All rows that meet the WHERE condition will be updated. If you want all the employees in the Boston office to move to new york, you can use the following statement:
Update employees
SET BRANCH_OFFICE =' new york'
Where BRANCH _ OFFICE =' Boston
If the WHERE clause is omitted, the department values of all rows in the table will be updated to "New York".
The syntax flow chart of the UPDATE statement is as follows:
Update table
SET column = value [{，column = value}]
[WHERE predicate [{logical join predicate}]];
Delete statement
The DELETE statement is used to delete rows in a known table. As in the UPDATE statement, all rows that meet the conditions in the WHERE clause will be deleted. Because there is no UNDO statement in SQL or "Are you sure you want to delete it?" For a warning like this, be careful when executing this statement. If you decide to cancel the Los Angeles office and fire all the staff in the office, this despicable work can be achieved by the following sentence:
Delete from employees
Where BRANCH_OFFICE =' Los Angeles';
As in the UPDATE statement, omitting the WHERE clause will cause the operation to be applied to all rows in the table.
The syntax flow chart of DELETE statement is as follows:
Delete from the table
[WHERE predicate [{logical join predicate}]];
Now we have completed the introduction of the main statements of Data Manipulation Language (DML). We haven't explained all the functions that SQL can do. SQL also provides many functions, such as average and sum of data in tables. In addition, SQL can also complete multi-table queries (multi-table queries, or join). The language also allows you to use the GRANT and REVOKE commands to control users' data access rights.