SQL is an abbreviation for Structured Quevy Language (Structured Query Language). SQL is an operation command set that is designed for the database, which is a fully fully equipped database language. When using it, you only need to issue a "do" command, "how to do" is not considering the user. SQL is powerful, easy to learn, easy to use, has become the basis for database operation, and now almost all databases support SQL. ## 1 Second, SQL Database Data Architecture SQL Database The data architecture is basically a three-level structure, but the terminology is different from traditional relationship model. In SQL, relational mode (mode) is called "Basic Table"; storage mode (internal mode) is called "Stored File); Sub-mode (outside mode) is called" view "(View" ); The tuning is called "row"; the attribute is called "column". Name symmetrical, such as ^ 00100009A ^: ## 1 Third, the composition of the SQL language first allows us to make us a basic understanding of the SQL language, introduce the composition of the SQL language: 1. A SQL database is a table (Table The collection, which is defined by one or more SQL mode. 2. A SQL table consists of a line set, a line is a sequence (collection), each column corresponding to a data item. 3. A table or a basic table or a view. The basic table is a table that is actually stored in the database, and the view is the definition of a table consisting of several basic tables or other views. 4. A basic table can span one or more store files, one or more basic tables can also be stored. Each storage file corresponds to the external storage of the previous physical file. 5. Users can use the SQL statement to query the views and basic tables. In the user's point of view, the views and basic tables are the same, and there is no difference, all relationships (tables). 6. SQL users can be an application or a terminal user. The SQL statement can be embedded in the host language, and the host language has Fortran, COBOL, PASCAL, PL / I, C, and ADA languages. SQL users can also be used as an independent user interface for terminal users under interice. ## 1V, operate SQL for the database includes all the operations of the database, mainly consisting of 4 parts: 1. Data definition: This part is also called "SQL DDL", defines the logical structure of the database, including defining databases , Basic table, view, and index 4. 2. Data manipulation: This part is also called "SQL DML", including two major categories of data queries and data updates, where data updates include insert, delete, and update three operations. 3. Data Control: The control of the user access data has the authorization, integrity rules of the view, the transactional statement, and the like. 4. Use of embedded SQL languages: Specify the rules used in the host language program. Below we will introduce: ## 2 (1) Data Definition SQL Data Definition Function Includes Defining Database, Basic Table, Index, and View. First, let's take a look at the basic data type provided by SQL: (, if ^ 00100009B ^) 1. Create a database for the establishment of the database: Database is a data set including multiple basic tables, its statement format : CREATE DATABASE [Other Parameters] where must be unique in the system, can not be repeated, otherwise it will result in data access error. [Other parameters] varies depending on the specific database implementation system.
Example: To create a project management database (XMManage), its statement should be: CREATE DATABASE XMMANAGE (2) Database Delete: Remove the database and its entire content from the system. Its statement format is: DROP DATABASE Example: Delete Project Management Database (XMManage), its statement should be: DROP DATABASE XMMANAGE 2. The definition of the basic table and the change in the independent table is called a basic table, in SQL language One of the unique correspondence corresponds to a basic table. The definition of the basic table refers to the establishment of a basic relational mode, and the change is to delete and modify the basic tables already existing in the database.
(1) Definition of the basic table: Basic Table is a non-exported relationship, and its definition involves the table name, column name, and data type, etc., its statement format is: Create Table [.]
( Data Type [Default] [NOT NULL / NULL] [, Data Type [Default] [NOT NULL / NULL]] ... [, Unique (column name [, column name] ......)] [, Primary Key] [, Foreign Key] ...) Reference
(Column Name [, Column Name]. .....) [, Check (Condition)] [Other Parameters]) Where, .] Indicates that the newly established table is stored in the database; the new table consists of two parts: one is Tables and a set of column names, which are actually stored data (ie, simultaneously store data to tables directly); column names are user-defined, and spaces cannot be used in column names; The data type is the type of standard data type described above; [not null / null] indicates whether the column is allowed to store null values, the SQL language supports the concept of null value, the so-called null value is "not know" or "meaningless" Value, it is worth noting that data "0" and space are not null value, and the system generally allows for null values by default. The order is arranged. Non-empty; [, Foreign Key] ...) Reference
(Column [, Column Name] ...)] is used to specify foreign keys Referring to the integrity constraint, the Foreign Key specifies the relevant list as the foreign key, which refers to the specified column of another table, which is the column in the outer table introduced by Reference, and the system will default a column name when not specified That is the same as the column name of the reference key, it is to note that the reference must be used when using the foreign key, and the foreign key reference integrity constraint condition specifies: the value of the foreign key is the same as the corresponding primary key, or null value (empty value) Specifically vary from the implementation system) [, CHECK] for checking the data in the deposited table using the specified condition to determine its legitimacy and improve data security.
Example: To create a student situation (student) create table student file: // Create Basic Table Student (st_class char (8), // Definition column ST_CLASS class, data type is 8-bit set string ST_NO CHAR (10) NOT NULL, / / Definition column ST_NO students, type 10-bit set string, non-empty ST_NAME CHAR (8) not null, // Defines the column ST_NAME name, type 8-bit set string, non-empty ST_SEX Char (2), // Define the column ST_SEX gender, type 2-bit set string ST_AGE smallint, // definition column ST_AGE age, type is short-intensive primary key (ST_NO)) // Defines the ST_NO Learning number as the primary key. Example: To create a course setting table (SUBJECT) CREATE TABLE SUBJECT / / Create Basic Table Subject (SU_NO CHAR (4) Not null, // Defines the column SU_NO Class, type 4-bit set string, non-empty su_subject char ( 20) NOT NULL, / / Definition column Su_SUBJECT course name, type 20-bit set string, non-empty su_credit integer, // definition column Su_credit credits, type is long integer su_period integer, // When defining column SU_PERIOD, the type is Long integer Su_PRENO CHAR (4), // Defines the column SU_PRENO Prescription, type 4-bit set string primary key (su_no)) // Defines the SU_NO class as the primary key. Example: To create a student selection table (score) Create Table Score: // Create Basic Table Score (ST_NO CHAR (10), // Definition Column ST_NO Learning No. 10 String SU_NO CHAR (4), / / Define the column SU_NO class, type 4-bit set string sc_score integer null, // definition column SC_SCORE, type long shape, can be an empty value forign key (ST_NO) REFERENCETENT, // Introduced from Table Student Refer to foreign key ST_NO to ensure that the association of this table and the table student and the synchronous forward key (Suno) REFERENCE SUBJECT) // From the table Subject, the reference foreign key SU_NO is introduced from the table Subject to ensure the association and synchronization of the table Subject (2) Deletion of the basic table: to remove a basic table from the database and its full content, its statement format is: DROP TABLE [.] Table name, for example: the table created above Drop Table Student, SUBJECT, Score (3) Basic Table Modifications: After the Basic Table is established and used for a while, it may be necessary to modify the structure of the basic table according to the actual requirements, that is, add new attributes or delete properties.
Increase the statement format of the attribute: ALTER TABLE [.] Table Name Add ( Data Type [Default] [NOT NULL / NULL] [, Data Type [Default] [NOT NULL / NULL] ... [, unique (column name [, column name] ...... [, Primary Key] [, Foreign Key (column name [, Column name] ...) Reference
(Column [, Column Name] ...)] [, Check (Condition)] [Other Parameters]) For example: in the Basic Table Student Join the list of STBORNs, the data type is DATE, and the statement format that cannot be deleted with an empty value ALTER TABLE Student Add (STBORN DATE NOT NULL) is: ALTER TABLE [.] Name Drop ( Data Type [Default] [NOT NULL / NULL] [, Data Type [Default] [NOT NULL / NULL]] ...) For example: Column ST_AGE in Basic Table Student Delete ALTER TABLE Student Drop (ST_AGE) 3. View definitions and delete in SQL, the view is the basic unit of the external mode data structure. It is a table exported from one or several basic tables that draws a "special table" that makes a number of subsets from existing basic tables. This constructor must be implemented using the SELECT statement in SQL. When a view is defined, it is only defined in the data stored in the system, and the data corresponding to the view is not directly stored until the user uses the view to see the corresponding data.
(1) Definition of the view: Defining a view can be implemented using the CREATE VIEW statement, its statement format is: CREATE VIEW view name AS SELECT statement exported from a basic table: Example: Example: Example from the basic table Student export only female student situation view CREATE vIEW WOMANVIEW AS file: // Create a view WOMANVIEW sELECT st_class, st_no, st_name, st_age file: // selection column st_class, st_no, st_name, st_age display fROM student file: // is introduced from the base table WHERE st_sex student = ' Female '// Introduction Condition is gender to "female", pay attention to character variables Use single quotes to export views from multiple basic tables: for example: export only female students and score from the basic tables Student and Score, more than 60 points View CreateView Woman_score as file: // Defining view Womanscore Select Student.st_class, student.st_no, student.st_name, student.st_age, score.sc_score file: // selective display Related column from student.score file: // Introducing WHERE Student.st_SEX = 'and SCORE.SC_SCORE> = 60 and student.st_no = score.st_no file: // Select Condition: Select: "Female" and score is more than 60. And use ST_NO to link two tables. If this view is performed later, simply use statements Select * from Woman_Score File: // where "*" is a wildcard, deletion of all elements (2) view: Used to delete no longer used views, The statement format is as follows: Drop View View Certain: Delete the Woman_score view established above Drop View Woman_Score 4. The index definition and delete index belong to physical storage concepts, not logical concepts. Abandon the index concept in SQL, use the primary key concept directly. It is worth mentioning that some relationships of DBMS include indexing mechanisms and primary key mechanisms, here we recommend using primary key mechanisms because it takes a lower in system resource and high efficiency. (1) The definition of the index: index is based on the basic table, its statement format is: create [unique] index index name on [.] Table name (column name [ASC / DESC] [, column Name [ASC / DESC]] ...) Here, the reserved word unique indicates that the index value in the base table does not allow duplicate, and if the default indicates that the index value is allowed to repeat in the table; DESC represents descending order according to the index key If the default or ASC represents an ascending arrangement.
Example: Establish indexes in the basic table student, respectively, as ascending and descending, and index values do not allow the Create Unique Index Stindex ON // Creating Index Stindex Student (ST_NO ASC, ST_AGE DESC) / / to Student ST_NO and ST_AGE Establish Index (2) Deletion: Drop Index Index Certain: Delete Strip DROP INDEX Stindex ## 2 (2) Data Query SQL is a query function, as long as it is data stock Data, can always find it from the database through the appropriate method. The query statement in SQL has only one: SELECT, which can work with other statements to complete all query functions. The full syntax of the SELECT statement can have 6 clauses. The complete syntax is as follows: SELECT target table list or column express collection from Basic table or (and) view collection [WHERE condition expression] [Group By column name collection] [Order By Column Name] [Collection] ...] The semantics of the entire statement are as follows: From the table listed in the FROM clause, select the tuple that meets the conditional expression given in the WHERE clause, and then prescribed in the GroupBy clause (packet subsis) The value packet of the column, then extract those groups that meet the Conditional expressions in the Having clause, press the column name or column expression value to the select clause. The ORDER clause (sort clause) is reordering the target table of the output and can be attached to the ASC (ascending) or DESC (descending) arrangement. The following operators and computational functions can occur in the conditional expression f in the WHERE clause: arithmetic comparison operators: <, <=,>,> =, =, <>. Logical operators: and, or, not. Collection operator: Union (and), INTERSECT, ExcePt. Collection member qualification operators: in, NOT IN predicates: exists (existing quantifiers), all, some, unique. Polymer function: AVG (average), min (minimum), max (max), sum (and), count. F, the operation object can also be another SELECT statement, that is, the SELECT statement can be nested. The above is just a few main operations that can occur in the WHERE clause. Since the conditional expression in the WHERE clause can be complicated, SELECT sentence types can be complicated more complicated than its math. Below, we have used three basic tables as described as examples, demonstrate the application of SELECT: 1. Unconditional quotings: Find out the courses of all students Select St_no, Su_no from Score Example: Find all students SELECT SELECT * From student "*" is a wildcard, indicating the value of all attributes indicated by the relationship indicating from the FROM. 2. Conditional query condition query is a query with a WHERE clause, and the object to be queried must meet the conditions given by WHERE clause.
Example: Find a student of any less less than 70, class, class, 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 This uses unique is not from the query result set to repeat line, if you use Distin, you will remove your row. The priority order of the other logical operator is NOT → AND → OR. Example: Find the course number C02, the test score does not match the students Select St_no from score where su_no = 'c02'and score <60 3. Sort query sort query refers to the ascending order (ASC) of the query results in specified properties or Descending (DESC) is arranged by the Order By clause. Example: Find a child, and put the results from the big to small alignment Select Unique Su_no from score where score <60 ORDER BY SU_NO DESC 4. Nested Query Nested query refers to the WHERE clause contains Select Subs This sentence is used in more complicated situations that span multiple basic tables. Example: Finding the course number C03 and the student number of the course is 80 points, name SELECT ST_NO, ST_NAME FROM Student Where STNO INO INO = 'c03' and score> 80) Here need YES: When the query involves multiple basic tables, the nesting query is subjected to the layout, which has the structural programming characteristics. In nested queries, IN is commonly used. If the user can exactly know that the inner query returns a single value, it can also be used to represent the user's requirements. 5. Calculation Query Calculation The query refers to the results that can only be calculated only by the direct use of the specific function (aggregate function) provided by the system. Commonly used functions include count count (column name) calculating a number of sum (column name) in a column (column name) to sum (this column value is numerical) AVG (this column value is numerical) AVG Column name) Ask average value (this column value is a numeric type) max (column name) Summary MIN (column name) in a column value is the minimum value of a column value: ask male students Total number and average age Select count (*), avg (ST_AGE) from home: Samples of students in the course: Number of students in the course Select Count (DistINCT ST_NO) from score: This must be added to Distinct, because Some students may elect a multi-do-course, but only one person is counted, so it is necessary to filter with Distinct. ## 2 (3) Data Update Data Updates include data insertion, deletion, and modify operations. They are completed by the INSERT statement, the delete statement, and the UPDATE statement. These operations can be performed on any of the basic tables, but there is a limit on the view. Where the view is exported by a single basic table, the insertion and modification operation can be performed, but the delete operation cannot be performed; the above three operations cannot be performed when the view is exported from a plurality of basic tables. 1. There are two ways to insert the data insertion of the data into SQL: one is the insertion of the cell group, the other is insertion of the multi-group.
Insertion of unit group: Insert a grade tuple (100002, C02, 95) to the Basic Table Score, using the following statement: Insert Into Score (ST_NO, SU_NO, SCORE) VALUES ('100002', 'C02', 95) Thereby, the insert statement format of the cell group can be given: INSERT INTO table name (column name 1 [, column 2] ...) VALUES (column value 1 [, column value 2] ...) where the column name is to insert The value of the column name collection, the column value sequence is the corresponding value to be inserted. If inserted a full column value of a table, the column name can omit the unwritten, such as the above (ST_NO, SU_NO, SCORE) can be omitted; if inserted into a table value, the corresponding column name must be listed. At this time, the column names not listed in this relationship take a null value. Insertion of multiple groups: This is a method of inserting the SELECT statement query results into a known basic table. For example: The average score of each student is required in Table Score and remains in a table. At this point, you can create a new basic table Stu_avgGrade, and then use the INSERT statement to put the flat grade of each student in Table Score (available to SELECT) to STU_AVGRADE. CREATE TABLE STU_AVGRADE (ST_NO CHAR (10) NOT NULL, / / Defining column ST_NO students, type 10-bit set string, non-empty AGE_GRADE SMALLINT NOT NULL) // Defining column AGE_GRADE average, type is short, non- Empty INSERT INTO STU_AVGRADE (ST_NO, AGE_GRADE) SELECT ST_NO, AVG (SCORE) from score group by st_no file: // Because the average score of each student is required, you must calculate according to the school number group. 2. Data Delete SQL Delete Action refers to the record that meets WHERE from the basic table. If there is no WHERE clause, the full record in the table is deleted, but the table structure still exists. The format of its statement is: DELETE FROM Name [WHERE Condition Expression] Examples below: Delete of cell groups: Remove students from the list of 100002 from the table student, available: delete from student where st_no = '100002' // Because students who have been 100002 are only one in Table Student, so delete the unit group: The score of the secondary number 100002 is removed from the table score, and the following statement: delete from score where st_no = ' 100002 '// Due to multiple groups of 100002 may have multiple in Table Score, delete the deletion with subqueries for multi-group queries: Delete all non-complimentary student records, available below delete from student where ST_NO In (Selete St_no from Score WHERE SCORE <60) 3. Data Modification Modify Statement is the expression of the record that meets the records of the conditional expression in the specified table in the specified table.
The format of its statement is as follows: UPDATE Table Name SET Column Name = Column Change Value [WHERE Condition Expression] Example: Change the C02's course name to English, you can use the following statement: Update Subject set su_no = 'c02 'Example: Receive lessons 70 points, and then improve 10% update score set = 1.1 * score where score> = 70 SQL deletion statement and modification statement WHERE clause usage with Select WHERE clause the same. Data deletion and modification operations, actually do the SELECT query operation, then remove or modify the found tuple. ## 2 (4) Data Control Since the database management system is a multi-user system, in order to control the user's access rights to the data, the Sharing and completeness of the data is maintained, the SQL language provides a series of data control functions. Among them, there is mainly security control, integrity control, transaction control, and concurrent control. 1. Security control data is to protect the database to prevent illegal use to cause data leakage and destruction. The main method of ensuring data security is to prevent the data in the database by controlling the control of data inventory. That is, the permissions of different data objects are limited to different users. Access control includes a grant, inspection, and undoing of power. Authority and undo commands are used by database administrators or specific applicants. The system verifies whether the corresponding user has the right to perform the required operation before the corresponding data is performed before the database is operated. (1) Power Profight: Power teaches two forms of authorization and users available for database administrators. The database administrator dedicated authorization command format is as follows: | Connect | GRANT | Resource | To Username [Identifed By Password] | DBA | Where, Connect indicates that the database administrator allows the specified user to have power to connect to the database, this authorization is targeted New users; Resource means that users are allowed to build their own new relationship model, and after users get Connect power, they must obtain Resource power to create their own new table; DBA represents database administrators to grant their privileges with the specified user. To be awarded a variety of powers in the above three authorities at the same time, you must specify three corresponding GRANT commands. In addition, users with CONNECT and RESOURCE can establish their own tables and have the power of query, insert, modify, and delete on their own table and view. But usually cannot use other users, unless otherwise delayed to his corresponding power. Example: If the user SSE is allowed to connect to the database and can establish his own relationship, it can be granted with power by the following command: Grant Connect to Sse Inentified by bd1928 grant resource to SSE user-available authorization refers to the part or all of the user The authority to delegate the command form of other users, and its command format is as follows: | SELECT | | Insert | | Delete | GRANT | Update (column name 1 [, column name 2] ...) | ON | Table name | To | Username | [WITH GRANT OPTION] | ALTER | | View Name | | Public | | NDEX | | All | If a user is simultaneously granted with a variety of operational power, the operation command symbol can be separated by ",". Public represents all users of the database, when using: WITH GRANT OPTION indicates that the authorized user has the same power to other user powers.
Example: If you will use the Query of Table Student with all users, you can use the following command: GRANT SELECT ON Student to Public Example: If you put the Subject's insertion and modification of power with the user SSE and make him have this power to transfer others Power, you can use the following command: Grant INSERT, UPDATE (SU_SUBJECT) ON SUBJECT To SSE with Grant Option, update is followed by su_subject refers to the columns that it can modify. (2) Power recycling: Power recovery refers to some power to recover some of the authorities that have been certified by the user. Matches with the authority and the command, and the power recovery also has two forms of database administrator dedicated and user available. DBA-specific power recovery command format is: | Connect | Revoke | Resource | From Username | DBA | User Available Power Recycling Command Format: | SELECT | | Insert | | Delete | Revoke | Update (Column Name 1 [, Column Name 2] ...) | ON | Table name | From | Username | | ALTER | | View Name | | Public | | INDEX | | All | Case: Recycle User SSE DBA Power: Revoke DBA from SSE 2. Integrity Control The integrity of the database refers to the correctness and compatibility of the data, which is an important concept in the database theory. The main purpose of integrity control is to prevent semantically incorrect data from entering the database. Integrity constraints in relational systems include physical integrity, reference integrity, and user-defined integrity. The definition of integrity constraints is mainly completed by the [CHECK] clause in the CREATE TABLE statement. In addition, some auxiliary commands can be protected by data integrity. Such as unique and not null, the former is used to prevent repetition values from entering the database, which is used to prevent null values. 3. Transaction control transaction is the basic unit of concurrent control, and is also the basic unit of recovery. Support the concept of transaction in SQL. The so-called business is a user-defined an operational sequence (set), which is either do, or one is not done, it is an indivisible whole. One business is usually started with a commit or rollback end in BeGin Transaction. SQL provides both transaction submission and transaction undo commands: (1) Transaction Submit: Transaction Submitted commands to: commit [work] transaction Submit flags, all of the application operations for the database, all the operations of the database must It is only valid when submitted to the system as a transaction. If you submit it, you cannot undo. (2) Transaction revoke: The command of the transaction is: rollback [work] transaction undo marks the corresponding transaction failed to the database operation, so that the change to the database, that is, "roll back" to the status of the corresponding transaction. When the system is abnormal end (such as power down, system crash), the automatic execution rollback command SQL provides a mechanism for automatic submission of transactions, and its command is: set auto commit ON, the corresponding manual work mode command is: SET AUTO COMMIT Once the automatic submission of the transaction is specified, the system is considering each SQL command as a transaction and automatically completing the transaction automatically when the command is successfully completed. 4. Concurrently control the database as a shared resource, allow multiple user programs to access data in parallel. When multiple users operate in parallel, they need to be coordinated and controlled by concurrent control to ensure the correct execution of concurrent operations, and ensure consistency of the database.
