SQL (Structured Query Language) is a database language with multiple functions such as data manipulation and data definition. This language is interactive and can provide users with great convenience. The database management system should make full use of SQL language to improve the quality and efficiency of computer application systems.SQL language can not only be applied to the terminal independently, but also can be used as a sub language to provide effective assistance for other program design. In this program application, SQL can optimize program functions together with other program languages, thus providing more and more comprehensive information for users.[1]
The SQL Server database includes Microsoft SQL Server and Sybase SQL Server sub databases. Whether the database can run normally or not is directly related to the operation safety of the entire computer system.
In the era of big data, the data type and scale of database system are constantly expanding, which brings some challenges to database management.In social production and life, the application scope of database is gradually expanded, and improving the efficiency of database development and application is the key to ensure the efficient operation of social production and life in China.
As an operation command set, SQL is widely welcomed by the industry with its rich functions, and becomes the guarantee to improve the efficiency of database operation.The application of SQL Server database can effectively improve the speed of data request and return, and effectively deal with complex tasks, which is the key to improve work efficiency.
Because SQL Servers database management system has high data management performance, because of its superior performance, it has a very wide range of applications, and is widely used in server and client architectures.The nature of SQL Servers database is mainly reflected in the following aspects: the system's throughput, response time and parallel processing capability, the speed of sending a request server response, and the speed of switching between different screens.
SQL DatabaseData forArchitectureIt is basically a three-level structure, but the terms used are different from those of traditional relational models.In SQL, the relational schema (schema) is called“Basic table”(base table)；Storage mode（Internal mode）It is called "stored file";The sub mode (outer mode) is called "view";tupleCalled "row";Attributes are called columns.
form
Announce
edit
Before formally learning the SQL language, let's first have a basic understanding of the SQL language and introduce the composition of the SQL language:
1. A SQL database is a collection of tables defined by one or more SQL schemas.
2. An SQL table consists of a row set. A row is a sequence (set) of columns. Each column corresponds to a rowdata item。
SQL Database
3. A table is either a basic table or a view.A basic table is a table actually stored in a database, while a view is a definition of a table composed of several basic tables or other views.
4. A basic table can span one or more storage files, and a storage file can also store one or more basic tables.Each storage file corresponds to the last physical file in the external storage.
5. Users can query views and basic tables with SQL statements.From the user's point of view, the view and the basic table are the same. There is no difference. They are both relations (tables).
6. SQL users can be applications or end users.SQL statements can be embedded inHost LanguageThe host languages are FORTRAN, COBOL, PASCAL, PL/I, C and Ada.SQL users can also act as independentUser interfaceFor end users in an interactive environment.
classification
Announce
edit
SQL system database products are widely used at home and abroad. It can be said that database systems as integrated networks must use these products.There are many kinds of SQL database products, such as Oracle, Sybase, Informix, etc.
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 amultimedia database Because of its excellent performance, it is in the rising period.More and more domestic users begin to use Sybase, and the scope of use is also growing.When selecting a database, you should pay attention to one problem, that is, it must match with the network operating system. According to the performance of these distributed databases, when selecting the Netware network operating system, you should generally use Sybase or Oracle database. If you select the Windows NT Advanced Server network operating system, you should select the Microsoft product MS SQL Server,Because the core of Microsoft's database is Sybase, which is a distributed multimedia database.By selecting the database in this way, it is easy to establish a client/server structure, which makes the publication and utilization of the entire network data more reasonable.
operation
Announce
edit
SQL includes all operations on the database, which is mainly composed of data definition, data manipulation, data query, data control, transaction control and embedded SQL language usage rules.
1. Data definition: also known as "DDL language", it defines the logical structure of the database, including the definition of database, basic table, view andIndexesPart 4.
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 data includes authorization and recycling of basic tables and views.
5. Transaction control: also known as "TCL language", including transaction submission and rollback.
6. Rules for the use of embedded SQL language: specify that SQL statements can be used inHost LanguageThe rules used in the program.
Data definition
SQL Database
The SQL data definition function includes defining database, basic tableIndexesAnd views.
First, let's understand the basic data types provided by SQL: (e.g. ^ 00100009b ^)
1. Establishment and deletion of database
(1) Establish database: the database is a dataset including 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, or it will lead to data access errors. [Other parameters vary according to the specific database implementation system.
Example: To establish the project management database (xmmanage), the statement should be:
CREATE DATABASE xmmanage
(2) Database deletion: delete the database and all its contents from the system.
The statement format is DROP DATABASE<database name>
For example, to 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 of itself is called a basic table. In SQL, a relationship uniquely corresponds to a basic table.The definition of a basic table refers to the establishment of a basic relational schema, while change refers to the deletion and modification of existing basic tables in the database.
Data query
SQL is a language with strong query function. As long as there is data in the database, it can always be found from the database by appropriate methods.There is only one query statement in SQL: SELECT, which can cooperate with other statements to complete all query functions.SELECT statementThe complete grammar of can have 6 clauses.The complete syntax is as follows:
Column name or column expression set of SELECT target table
FROM base table or (and) view set
[WHERE conditional expression]
[GROUP BY column name set
[HAVING group conditional expression]
[ORDER BY column name [set]...]
Simple query, using TOP clause
The query results are sorted in ascending order by default ASC, and in descending order by the keyword DESC.
The query where with conditions uses arithmetic expressions, logical expressions, and between and in keywords.
Fuzzy query like
The semantics of the whole statement are as follows: from the tables listed in the FROM clause, select tuples that meet the conditional expression given in the WHERE clause, then group them by the value of the specified column in the GROUP BY clause (grouping clause), extract those groups that meet the group conditional expression in the HAVING clause, and evaluate and output them by the column name or column expression given in the SELECT clause.The ORDER BY clause is used to reorder the output target table, and can add an ASC (ascending order) or DESC (descending order) arrangement.
The following operators and operands can appear in the conditional expression F in the WHERE clause:
Aggregate functions: AVG (average), MIN (minimum), MAX (maximum), SUM (and), COUNT (count).
The operand can also be anotherSELECT statementThat is, SELECT statements can be nested.
The above list only lists several main operations that can appear in the WHERE clause. Because the conditional expression in the WHERE clause can be very complex, the semantics of the SELECT sentence pattern can be far more complex than its mathematical prototype.
Let's take the three basic tables created above as examples to demonstrate the application of SELECT:
1. Unconditional query
Example: find out the course selection of all students
SELECT st_no，su_no
FROM score
Example: find out the situation of all students
SELECT*
FROM student
"*" is a wildcard character, which means to find the values of all attributes of the relationship indicated in FROM.
2. Condition 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.
For example, find out the student's situation, class number and score of any course with a score of more than 70
WHERE score.score>=70 AND score.stno=student,st_no
Here UNIQUE does not remove duplicate rows from the query result set. If DISTINCT is used, duplicate rows will be removed.In addition, the priority of logical operators is NOT → AND → OR.
For example: find out the students whose course number is c02 and who fail the exam
SELECT st_no
FROM score
WHERE su_no=‘c02’ AND score<60
3. Sort Query
Sorting query refers to arranging the query results in ascending order (ASC) or descending order (DESC) of the specified attribute, as indicated by the ORDER BY clause.
Example: Find the failed courses and arrange the results by course number
SELECT UNIQUE su_no
FROM score
WHERE score<60
ORDER BY su_no DESC
4. Nested query
Nested query means that the WHERE clause also contains a SELECT clause, which is used for complex queries across multiple basic tables.
Example: Find the student number and name of the student whose course number is c03 and whose 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 made clear here is that when the query involves multiple basic tables, the nested query is used to solve the problem step by step with clear hierarchy, which has the characteristics of structural programming.In nested queries, IN is a commonly used predicate.If the user can know exactly that the returned value of the inner layer query is a single value, arithmetic can also be usedComparison operatorIndicates the user's requirements.
5. Calculation Query
Computed query refers to the direct use of a specific function (aggregate function) provided by the system in a statement to obtain some results that can only be obtained through calculation.Common functions are:
COUNT (*) Counts the number of tuples.
COUNT (column name) calculates the number of values in a column and automatically ignores null values.
SUM (column name) is used to calculate the sum of values in a column (this column value is numerical).
AVG (column name) Average the value of a column (the column value is numeric).
MAX (column name) calculates the maximum value in a column.
MIN (column name) calculates the minimum value in a column.
Example: 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 courses
SELECT COUNT(DISTINCT st_no)
FROM score
Note: DISTINCT must be added here, because some students may have taken multiple courses, but only one person can be counted in statistics, so DISTINCT should be used for filtering.
Data update
Data update includes data insertion, deletion and modification.They are composed of INSERT statements,DELETE statementAnd UPDATE statements.These operations can be performed on any basic table, but there are restrictions on views.When the view is exported from a single basic table, you can insert and modify it, but you cannot delete it;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 cell group insertion, and the other is multi group insertion.
Insertion of unit group: insert a score tuple (100002, c02, 95) into the basic table score. The following statements can be used:
INSERT INTO score (st_no，su_no，score) VALUES (‘100002’，‘c02’，95)
Thus, the insertion 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...)
Where, the column name sequence is the collection of column names to insert values, and the column value sequence is the corresponding value to insert.If all the column values of a table are inserted, the column names can be omitted, as shown above (st_no, su_no, score) can be omitted;If some column values of the table are inserted, the corresponding column names must be listed. At this time, the column names not listed in the relationship take null values.
Multi group insertion: This is a method to insert the query result of SELECT statement into a known basic table.
For example, you need to calculate the average score of each student in the table score and keep it in a table.At this point, you can first create a new basic table stu_avggrade, and then insert the average score of each student (calculated with SELECT) from the table score into stu_avggrade.
CREATE TABLE stu_avggrade
(st_no CHAR (10) NOT NULL,//Define the st_no student number of the column, the type is a 10 bit fixed length string, not empty
Age_grade SMALLINT NOT NULL)//Define the average score of the column age_grade. The type is short integer, not empty
INSERT INTO stu_avggrade(st_no，age_grade)
SELECT st_no，AVG(score)
FROM score
GROUP BY st_no//Because the average score of all courses for each student is required, it must be calculated by student number group.
2. Data deletion
The deletion operation of SQL refers to deleting records that meet WHERE<conditional expression>from the basic table.If there is no WHERE clause, all records in the table will be deleted, but the table structure still exists.The statement format is:
DELETE FROM table name [WHERE conditional expression]
The following is an example:
Delete unit group: 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 there is only one student with student number 100002 in the table student, it is the deletion of unit group
Deletion of multiple groups: The score with student number 100002 is deleted from the score table. The following sentence can be used:
DELETE FROM score
WHERE st_no='100002'//The tuple with student number 100002 may have more than one score in the table, so it is deleted for the multi group
Delete operation with sub query: delete all failed student records. The following statement can be used
DELETE FROM student
WHERE st_no IN
(SELETE st_no
FROM score
WHERE score<60)
3. Data modification
The modification statement is to modify the corresponding column value of the record that meets the condition expression in the specified table according to the expression in the SET clause.The statement format is as follows:
UPDATE table name SET column name=column change value [WHERE conditional expression]
For example, if you change the name of c02 course to English, you can use the following sentence:
UPDATE subject
SET su_subject='English'
WHERE su_no=‘c02’
For example: increase the score of students whose course score reaches 70 points by another 10%
UPDATE score
SET score=1.1*score
WHERE score>=70
The WHERE clause in the delete statement and modify statement of SQL is the same as that in SELECT.To delete and modify data, you need to perform SELECT query first, and then delete or modify the tuples found.
Data control
As the database management system is a multi-user system, SQL language provides a series of data control functions in order to control the access rights of users to data and maintain the sharing and completeness of data.Among them, it mainly includes security control and integrity control.
1. Security control
Data security refers to protecting the database from data leakage and destruction caused by illegal use.The main way to ensure data security is to prevent the illegal use of data in the database by controlling the access rights of the database.That is to limit the permission of different users to operate different data objects.
Access control includes authorization, inspection and revocation of authority.The delegation of authority and revocation of orders byDatabase administratorOr specific application personnel.Before operating the database, the system shall first verify whether the corresponding user has the right to perform the required operation on the corresponding data.
(1) Authorization: Authorization can be in two forms: authorization dedicated to database administrators and authorization available to users.The format of the database administrator specific authorization command is as follows:
|CONNECT |
GRANT | RESOURCE | TO user name (IDENTIFIED BY password)
|DBA |
Where, CONNECT means that the database administrator allows the specified user to have the right to connect to the database, and this authorization is for new users;RESOURCE means that users are allowed to establish their own new relationship mode. After obtaining CONNECT authority, users must obtain RESOURCE authority to create their own new tables;DBA means that the database administrator grants his/her own privileges to the specified user.To grant a user more than one of the above three authorizations at the same time, it must be specified by three corresponding GRANT commands.
In addition, users with CONNECT and RESOURCE authorization can create their own tables and have the right to query, insert, modify, and delete their own tables and views.However, the relationship with other users cannot be used unless the corresponding authority delegated to him by other users can be obtained.
For example, if you allow user SSE to connect to the database and establish his own relationship, you can grant authority through the following command:
GRANT CONNECT TO SSE IDENTIFIED BY BD1928
GRANT RESOURCE TO SSE
The authorization available to users refers to the command form that users delegate some or all of their own rights 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 the authority is granted to all users of the database. Please pay attention to:
The option WITH GRANT OPTION indicates that the authorized user has the right to delegate the concurrent power he/she has to other users.
For example, if you want to grant all users the query right of the table student, you can use the following command:
GRANT SELECT ON student TO PUBLIC
For example, if the user SSE is granted the right to insert and modify the table subject and has the right to delegate this power 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 can be modified.
(2) Power recovery: power recovery refers to the recovery of certain rights previously granted by the designated user.In line with the power granting command, power recovery also has two forms: database administrator dedicated and user available.
The format of the DBA specific power recovery command is:
|CONNECT |
REVOKE | RESOURCE | FROM user name
|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 |
For example: reclaiming the DBA power 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 includeEntity integrity, referential integrity, and user-defined integrity.The definition of integrity constraints is mainly completed through the [CHECK] clause in the CREATE TABLE statement.In addition, there are some auxiliary commandsData integrityProtection.For example, UNIQUE (uniqueness constraint) and NOT NULL (non null constraint), the former is used to prevent duplicate values from entering the database, and the latter is used to prevent null values.
Transaction control
Transaction is the basic unit of concurrency control and recovery.The concept of transaction is supported in SQL.A transaction is a user-defined sequence of operations (collection). These operations are either all or none, and are an indivisible whole.A transaction usually starts with BEGIN TRANSACTION and ends with COMMIT or ROLLBACK.
SQL provides transaction commit and transaction undo commands:
(1) Transaction commit: The transaction commit command is:
COMMIT 〔WORK〕
Transaction commit indicates that some application operation on the database has been 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 Undo: The transaction undo command is:
ROLLBACK 〔WORK〕
Transaction undo indicates that the corresponding transaction failed to operate on the database. Therefore, it is necessary to undo the changes to the database, that is, "rollback" to the state when the corresponding transaction started.
When the system ends abnormally (such as power failure and system crash), the ROLLBACK command will be automatically executed.
