What Are SQL Databases?

SQL (Structured Query Language) is a database language with multiple functions such as data manipulation and data definition. This language has interactive features and can provide users with great convenience. The database management system should make full use of the SQL language to improve computer application systems. Quality and efficiency. Not only can the SQL language be used independently in the terminal, but it can also be used as a sub-language to provide effective assistance to other programming. In this program application, SQL can optimize the program functions together with other programming languages to provide users with more comprehensive information. ^[1]

In the era of big data, the data types and scale of database systems are constantly expanding, which brings certain challenges to database management. In the social production and life, the application scope of the database is gradually increasing, and improving the efficiency of database development and application is the key to ensuring the efficient operation of the social production and life in China.

1) Non-procedural language

2) Unified language

3) Yes all

Before learning the SQL language, let us first have a basic understanding of the SQL language and introduce the components of the SQL language:

1. An SQL database is a collection of tables, which are defined by one or more SQL schemas.

2. An SQL table consists of a set of rows, a row is a sequence (set) of columns, and each column corresponds to a row

SQL system database products are widely used at home and abroad, and it can be said that as an integrated network database system, such products must be used. There are many types of SQL database products, commonly used are Oracle, Sybase, Informix and so on.

These database management systems occupy the vast majority of the database market in the world, of which Oracle has the largest market share. Sybase is a kind of multimedia database, because of its excellent performance, it is in a rising period. More and more users have begun to use Sybase in China, and the scope of use has also become wider. When selecting a database, you should pay attention to a problem that it must match the network operating system. According to the performance of these distributed databases, when choosing a Netware network operating system, you should generally use a Sybase or Oracle database. If you choose Windows NT Advanced Server network operation When the system is used, Microsoft's product MS SQL Server should be selected because the core of this database of Microsoft is Sybase, which is a distributed multimedia database. By selecting a database in this way, it is easy to set up a Client / Server structure, making the publication and utilization of the entire network data more reasonable.

SQL includes all operations on the database, and is mainly composed of 4 parts:

1. Data definition: also known as "DDL language", defines the logical structure of the database, including defining the database, basic tables, views and indexes.

2. Data manipulation: also known as "DML language", including three operations: insert, delete and update.

3. Data query: also known as "DQL language", including data query operations.

4. Data control: Also known as "DCL language", the control of user access to data has the authorization and recycling of basic tables and views.

5. Transaction control: also known as "TCL language", including transaction submission and rollback.

6. The use of the embedded SQL language: the rules that specify the use of SQL statements in the host language program.

Below we will introduce separately:

SQL database data definition

SQL data definition functions include defining databases, basic tables, indexes, and views.

SQL database

First, let us understand the basic data types provided by SQL: (such as ^ 00100009b ^)

1. Database creation and deletion

(1) Establish a database: A database is a data set that includes multiple basic tables, and its statement format is:

CREATE DATABASE <database name> [Other parameters]

Among them, <database name> must be unique in the system and cannot be repeated, otherwise it will cause data access errors. [Other parameters] vary depending on the specific database implementation system.

Example: To build a project management database (xmmanage), the statement should be:

CREATE DATABASE xmmanage

(2) Delete the database: Delete the database and all its contents from the system.

Its statement format is: DROP DATABASE <database name>

Example: delete the project management database (xmmanage), the statement should be: DROP DATABASE xmmanage

2. Definition and change of basic table

A table that exists independently is called a basic table, and a relationship in SQL language uniquely corresponds to a basic table. The definition of a basic table refers to the establishment of a basic relationship model, while a change refers to the deletion and modification of an existing basic table in the database.

SQL database data query

SQL is a language with a strong query function. As long as it is data that exists in a database, it can always be found out of the database by an appropriate method. There is only one query statement in SQL: SELECT, which can cooperate with other statements to complete all query functions. The full syntax of a SELECT statement can have 6 clauses. The complete syntax is as follows:
SELECT target table column name or column expression set FROM base table or (and) view set [WHERE condition expression]
[GROUP BY column name set [HAVING group conditional expression]]
[ORDER BY column name [collection] ...]

Simple query, using TOP clause

The query results are sorted by default ASC ascending order, using the keyword DESC descending order.

Conditional query where, using arithmetic expressions, using logical expressions, using between and in keywords.

Fuzzy query like

The semantics of the entire statement are as follows: from the tables listed in the FROM clause, select a tuple that meets the conditional expression given in the WHERE clause, and then group by the value of the specified column in the GROUPBY clause (grouping clause), Then extract those groups that satisfy the group conditional expression in the HAVING clause, and evaluate and output according to the column name or column expression given by the SELECT clause. The ORDER BY clause reorders the output target table, and can additionally specify ASC (ascending) or DESC (descending) order.
The following operators and operation functions can appear in the conditional expression F in the WHERE clause:
Arithmetic comparison operators: <, <=,>,> =, =, <>,! =.
Logical operators: AND, OR, NOT.
Set operators: UNION (deduplication and merging), UNION ALL (no deduplication and merging), INTERSECT (intersection), MINUS (bad).
Set membership operators: IN, NOT IN
Predicates: EXISTS (existing quantifier), ALL, ANY, SOME, UNIQUE.
Aggregation functions: AVG (average value), MIN (minimum value), MAX (maximum value), SUM (sum), COUNT (count).
The operation object can also be another SELECT statement, that is, the SELECT statement can be nested.
The above only lists several main operations that can occur in the WHERE clause. Since the conditional expression in the WHERE clause can be very complicated, the semantics that the SELECT sentence can express is much more complicated than its mathematical primitive.
Below, we take the three basic tables established above as examples to demonstrate the application of SELECT:
1. Unconditional query example: find out the enrollment of all students SELECT st_no, su_no
FROM score
Example: Find out the situation of all students SELECT *
FROM student
"*" Is a wildcard, which means to find the values of all the attributes of the relationship indicated in FROM.
2. Conditional query A conditional query is a query with a WHERE clause. The object to be queried must meet the conditions given by the WHERE clause.
Example: Find out the student status, course number and score of any subject with a score of 70 or above. SELECT UNIQUE student.st_class, student.st_no, student.st_name, student.st_sex, student.st_age, score.su_no, score.score
FROM student, score
WHERE score.score> = 70 AND score.stno = student, st_no
The use of UNIQUE here does not remove duplicate rows from the query result set. If DISTINCT is used, duplicate rows are removed. The precedence of logical operators is NOT AND OR.
Example: Find out the student whose course number is c02, who failed the test SELECT st_no
FROM score
WHERE su_no = 'c02' AND score <60
3. Sorting query Sorting query refers to sorting the query results according to the ascending order (ASC) or descending order (DESC) of the specified attribute, which is indicated by the ORDER BY clause.
Example: Find failing courses, and arrange the results in descending order by course number SELECT UNIQUE su_no
FROM score
WHERE score <60
ORDER BY su_no DESC
4. Nested query Nested query refers to the WHERE clause and the SELECT clause, which is used for more complex queries across multiple basic tables.
Example: Find the student number and name of the student whose course number is c03 and the course score is above 80. SELECT st_no, st_name
FROM student
WHERE stno IN (SELECT st_no
FROM score
WHERE su_no = 'c03' AND score> 80)
What needs to be clear here is that when the query involves multiple basic tables, nested queries are used to solve the hierarchy one by one, which has the characteristics of structural program design. In nested queries, IN is a commonly used predicate. If the user can know exactly that the inner query returns a single value, then the user's requirements can also be expressed using an arithmetic comparison operator.
5. Computational query Computational query refers to the use of specific functions (aggregation functions) provided in the system to directly obtain certain results that can only be obtained through calculation. Common functions are:
COUNT (*) counts the number of tuples.
COUNT (column name) Counts the values in a column and automatically ignores null values.
SUM (column name) Find the sum of the values in a column (the column values are numeric).
AVG (column name) Find the average value of a column value (this column value is numeric).
MAX (column name) Find the largest value in a column.
MIN (column name) Find the minimum value in a column.
Example: Find the total number and average age of male students SELECT COUNT (*), AVG (st_age)
FROM student
WHERE st_sex = 'Male'
Example: Count the number of students who have taken the course SELECT COUNT (DISTINCT st_no)
FROM score
Note: Be sure to join DISTINCT here, because some students may take multiple courses, but only one person can be counted in the statistics, so use DISTINCT for filtering.

SQL database data update

Data updates include data insertion, deletion, and modification operations. They are completed by INSERT statement, DELETE statement and UPDATE statement. These operations can be performed on any basic table, but there are restrictions on views. Among them, when a view is exported from a single basic table, insert and modify operations can be performed, but delete operations cannot be performed; when a view is exported from multiple basic tables, none of the above three operations can be performed.
1. Data insertion There are two ways to insert data into the basic table of SQL: one is the insertion of unit groups, and the other is the insertion of multivariate groups.
Cell group insertion: To insert a result tuple (100002, c02, 95) into the basic table score, you can use the following statement:
INSERT INTO score (st_no, su_no, score) VALUES ('100002', 'c02', 95)
From this, the insert statement format of the unit group can be given:
INSERT INTO table name (column name 1, column name 2 ...) VALUES (column value 1, column value 2 ...)
The column name sequence is the set of column names to be inserted, and the column value sequence is the corresponding value to be inserted. If all the column values of a table are inserted, the column names can be omitted without writing (st_no, su_no, score) as above; if the partial columns of the table are inserted, the corresponding column names must be listed At this point, column names not listed in the relationship take null values.
Multivariate insert: This is a method to insert the results of a SELECT statement into a known basic table.
For example: the average score of each student needs to be found in the table score and kept in a table. At this time, you can first create a new basic table stu_avggrade, and then use the INSERT statement to insert the average grade of each student obtained in the table score (obtained by SELECT) into stu_avggrade.
CREATE TABLE stu_avggrade
(st_no CHAR (10) NOT NULL, // define column st_no student number, type is a 10-bit fixed-length string, non-empty age_grade SMALLINT NOT NULL) // define column age_grade average score, type is short integer, non-null stu_avggrade (st_no, age_grade)
SELECT st_no, AVG (score)
FROM score
GROUP BY st_no // Because each student's average grade for all courses is required, it must be calculated by group.
2. Data delete SQL delete operation refers to delete the records that meet the WHERE <condition expression> from the basic table. If there is no WHERE clause, all records in the table are deleted, but the table structure still exists. Its statement format is:
DELETE FROM table name [WHERE condition expression]
Here are some examples:
Deletion of unit group: To delete the student whose student number is 100002 from the table student, the following statement can be used:
DELETE FROM student
WHERE st_no = '100002' // Because the student with the student ID of 100002 has only one student in the table, it is the deletion of the multi-element group: the student with the student ID of 100002 is deleted from the table score.
DELETE FROM score
WHERE st_no = '100002' // Since there may be multiple tuples with the student number of 100002 in the table score, delete the delete operation with a subquery for the multivariate group: delete all failed student records, you can use the following statement DELETE FROM student
WHERE st_no IN
(SELETE st_no
FROM score
WHERE score <60)
3. The data modification and modification statement is to modify the corresponding column value of the record that meets the conditional expression in the specified table according to the expression in the SET clause. Its statement format is as follows:
UPDATE table name SET column name = column change value [WHERE condition expression]
Example: To change the course name of c02 to English, you can use the following sentence:
UPDATE subject
SET su_subject = 'English'
WHERE su_no = 'c02'
Example: Increase the student score of 70 in the course and increase it by 10%
UPDATE score
SET score = 1.1 * score
WHERE score> = 70
The WHERE clause usage in SQL delete and modify statements is the same as the WHERE clause usage in SELECT. To delete and modify data, you must first perform a SELECT query operation, and then delete or modify the found tuples.

SQL database data control

Because the database management system is a multi-user system, in order to control the user's access to data and maintain data sharing and completeness, SQL language provides a series of data control functions. Among them, mainly include security control and integrity control.
1. Security Control The security of data refers to protecting the database from data leakage and destruction caused by illegal use. The main method to ensure data security is to prevent the illegal use of data in the database through the control of database access rights. That is to restrict the permissions of different users to operate different data objects.
Access control includes the granting, checking and revoking of powers. The power grant and revoke commands are used by the database administrator or specific application personnel. Before operating the database, the system verifies whether the corresponding user is authorized to perform the required operation on the corresponding data.
(1) Power grant: Power is granted in two forms: database administrator-specific authorization and user-available authorization. The format of the database administrator-specific authorization command is as follows:
| CONNECT |
GRANT | RESOURCE | TO username [IDENTIFED BY password]
| DBA |
Among them, CONNECT means that the database administrator allows specified users to have the right to connect to the database. This authorization is for new users; RESOURCE means that users are allowed to establish their own new relationship mode. After users get CONNECT rights, they must obtain RESOURCE rights to create themselves New table; DBA means that the database administrator grants his privileges to the specified user. To grant a user multiple rights in the above three types of authorizations at the same time, they must be specified by three corresponding GRANT commands.
In addition, users with CONNECT and RESOURCE authorization can create their own tables and have the rights to query, insert, modify, and delete on the tables and views that they have created. However, the relationship of other users cannot usually be used unless the corresponding power delegated to him by other users can be obtained.
Example: If the user SSE is allowed to connect to the database and can establish his own relationship, the authority can be granted by the following command:
GRANT CONNECT TO SSE IDENTIFIED BY BD1928
GRANT RESOURCE TO SSE
The authorization available to a user refers to the form of a command in which the user delegates some or all of its power to other users. The command format is as follows:
| SELECT |
| INSERT |
| DELETE |
GRANT | UPDATE (column name 1 [, column name 2] ...) | ON | table name | TO | user name | [WITH GRANT OPTION]
| ALTER | | View Name | | PUBLIC |
| NDEX |
| ALL |
If multiple operation rights are granted to a user at the same time, the operation command symbols can be separated by ",".
PUBLIC means that all users of the database are granted authority, pay attention when using:
The option WITH GRANT OPTION indicates that the authorized user has the right to delegate the obtained power to other users.
Example: If you grant the query right to the table student to all users, you can use the following command:
GRANT SELECT ON student TO PUBLIC
Example: If the insert and modify right of the subject is granted to the user SSE and he has the right to delegate this right to others, the following command can be used:
GRANT INSERT, UPDATE (su_subject) ON subject TO SSE WITH GRANT OPTION
Here, UPDATE followed by su_subject indicates the columns that it can modify.
(2) Recovery of power: Recovery of power refers to the recovery of certain powers originally granted by the designated user. In line with the power-granting command, power recovery also comes in two forms dedicated to the database administrator and available to the user.
The DBA-specific power recovery command format is:
| CONNECT |
REVOKE | RESOURCE | FROM username | DBA |
The format of the power recovery command available to users is:
| SELECT |
| INSERT |
| DELETE |
REVOKE | UPDATE (column name 1 [, column name 2] ...) | ON | table name | FROM | user name |
| ALTER | | View Name | | PUBLIC |
| INDEX |
| ALL |
Example: Recovery of DBA authority of user SSE:
REVOKE DBA FROM SSE
2. Integrity control Database integrity refers to the correctness and compatibility of data, which is an important concept in database theory. The main purpose of integrity control is to prevent semantically incorrect data from entering the database. Integrity constraints in relational systems include entity integrity, referential integrity, and user-defined integrity. The definition of integrity constraints is mainly accomplished through the [CHECK] clause in the CREATE TABLE statement. In addition, there are auxiliary commands for data integrity protection. Such as UNIQUE (unique constraint) and NOT NULL (non-null constraint), the former is used to prevent duplicate values from entering the database, the latter is used to prevent null values.

SQL database transaction control

A transaction is the basic unit of concurrency control and the basic unit of recovery. The concept of transactions is supported in SQL. The so-called transaction is a user-defined sequence of operations (sets). These operations are either all done or none of them. It is an indivisible whole. A transaction usually starts with BEGIN TRANSACTION and ends with a COMMIT or ROLLBACK.
SQL provides two commands for transaction commit and transaction undo:
(1) Transaction submission: The order for transaction submission is:
COMMIT WORK
Transaction commit indicates that a certain application operation on the database is successfully completed. All operations on the database must be submitted to the system as transactions to be effective. Once a transaction is committed, it cannot be undone.
(2) Transaction cancellation: The order of transaction cancellation is:
ROLLBACK WORK
Transaction undo indicates that the corresponding transaction fails on the database operation, so the changes to the database must be undone, that is, "rollback" to the state when the corresponding transaction started.
When the system ends abnormally (such as power failure or system crash), the ROLLBACK command will be executed automatically.