Review of database -- 3. SQL language

One .SQL

1.sql The classification of languages ：

（1）DDL（ Data definition language ）eg：CREAT,ALERT,DROP

（2）DML（ Data manipulation language ）eg：INSERT,DELETE,UPDATE

（3）DQL（ Data query language ）eg：SELECT

（4）DCL（ Data control language ）eg： Rights management （GRANT.REVOKE）, Business management

2. Basic types

char（n）: Fixed length String , User specified length

varchar（n）: Variable length String , User specified maximum length

int： Integer types , Equivalent to Integer

smallint： Small integer type

numeric（p,d）： Fixed-point number , The accuracy is specified by the user , Express p Digit number , among d Place to the right of the decimal point

real,double precision: Floating point numbers and double precision floating point numbers , Accuracy is related to the machine

float（n）: Accuracy at least n Bit floating point

null: Each type can contain a special value , The value is null , You can declare that the attribute value is not empty , It is forbidden to add null values

date: The date type （ About the type of time in different sql May be different in ）

3. Modify the basic table  

use ALERT  TABLE Specific operation functions MODIFY（ Modify properties ）,ADD（ Attribute added ）,DROP（ Delete attribute ）

4. Delete base table ： use DROP

5.SQL Definition statement ：

（1） Building a database :CREATE DATABASE Database name

（2） Create a basic table :CREATE TABLE Table name   { Name , data type , Integrity constraints }

            Integrity constraints include ： Primary key clause , Check clause , Foreign key clause ,not null

6.DELETE Delete statements ：DELETE FROM Table name [WHERE expression ]

7.UPDATE Modify the statement UPDATE Table name SET Name = expression [WHERE Conditional expression ]

Two .  Basic knowledge of table operation

1.

SELECT Name 1, Name 2,...

FROM surface 1, surface 2...

WHERE Connection condition

（ The equivalent relational algebra of the above query statement is ： First pair FROM The latter relationship The cartesian product , And then according to WHERE Conduct choice , According to the SELECT Conduct Projection ）

Be careful ：

1.SQL It is not allowed to use "-", Instead, use "_", And it is not case sensitive , You can name the table in uppercase or lowercase , attribute

2.SQL Allow in relationships and SQL Duplicate tuples appear in the expression , If you want to forcibly remove , You can add distinct keyword

3.select You can also use arithmetic expressions with operators in Clauses , The operand can be a constant or an attribute of a tuple

4.SQL You can use as Clause , Mechanisms for renaming relationships and attributes ,as Can appear in select clause , It can also appear in from clause

      eg：select name as instructor_name,course_id from instructor,teachers where....（ Rename properties ）

      eg：select T.name,S.course_id from instructor as T,teachers as S where ....( Rename two tables , For the sake of simplicity ）

      eg：select distinct T.name from instructor as T , instructor as S where T.salary > S.salary and S.dept_name = 'a' ( Rename a table twice , In order to distinguish between )

5.like Fuzzy query （%,_）

6. Sort : Use order by Clause , The ascending order is asc( Default ), The descending order is desc

      eg：select * from instructor order by salart desc,number asc

7.union Either or ,intersect Both ,except Remove

8. Aggregation function ：avg,min,max,sum,count among sum and avg Must be a number set , But other operators can also act on sets of non numeric data types , Such as a string , Be careful ： except count(*) All aggregate functions other than will ignore null values in the input set , Empty set count The operation value is 0, All other aggregation operations return a null value when the input is an empty set

9. Group aggregation group by : Clause gives one or more attributes to construct the grouping , stay group by Tuples with the same value on all attributes in the clause will be grouped , in addition . have access to having（ Be similar to where, It's just for grouping ） Select groups

 2. nested subqueries

1.exist You can test whether there are tuples in a subquery , When the subquery is empty true value

2.unique You can test whether there are duplicate tuples in a subquery , If there are no duplicate tuples , Will return true

3. Database delete operation

delete from instructor  where  ... ... 

4. Database insertion

insert into (...) values (...)

insert into course(course_id,title,credit) values ('cs-232','base-system','5')

  summary ：SQL Query statement execution order ：from->where->group->having->select->order by

3、 ... and . Join query of table （ Internal and external connections ）

1. Internal connection

（1） Equivalent connection ： There is a “ be equal to ” Connections , Generate a join temporary table , Then process the table to produce the final result

（2） Non equivalent connection ： The relationship between tables is not equivalent, but other relationships , Through the specified non equivalent relationship , Join the two tables , Generate a temporary table , Then the temporary table is processed to generate the final result

（3） Self join : Sometimes you need to join the same table , This connection is called self connection , Self join is like separating two tables , You can connect a row of a table with another row in the same table .

2. External connection ： Returns the FROM All rows of at least one table or view mentioned in Clause , As long as these lines match any WHERE Or search terms , Index all rows of coordinates referenced through the left outer link , And all rows of the right table referenced through the right outer join （ The left outer join , Right connection , Full outer join ）

（1） The left outer join

Be careful ：null It will also be put in

（2） Right connection

（3） Full outer join ： Keep mismatch information

3、 ... and . Subquery

1. General subquery ： Commonly used in SELECT Of the statement WHERE clause

2. Correlation subquery

（3） with EXISTS Subquery of the test （exists Generally used in WHERE clause . Followed by SELECT Subquery , So as to form a condition , with EXIST The subquery of does not repudiate any data , Only return logical true and logical false ）

Four .sql Control statement （ Transaction management and data protection ）

1. Grant authority ：GRANT

Format ：GRANT jurisdiction ON object TO user (WITH GRANT OPTION)

2. Take back authority ：REVOKE

5、 ... and . View

1. Definition ： It's from Tables exported from one or more basic tables , Different from the basic table , It's a Virtual table , Only the definition of view is stored in the database , Instead of storing the data corresponding to the view , These data are still stored in the basic table , When querying the view, execute the corresponding SELECT sentence

If a view is derived from a single basic table , And just remove some rows and columns of the basic table , But the main code is retained , We call this kind of view row column subset view

2. The function of view ：

· Simplify the operation of users ;

· Enable users to view the same data from multiple perspectives ;

· It provides a certain degree of logical independence for the reconstruction of the database ;

· It can provide security protection for confidential data ;

3. View Update restrictions ： One view corresponds to one SELECT sentence

SQL Chinese vs SELECT Statements must meet the following rules ：

• FROM Clause can contain only one relationship R, No more relationships
• WHERE Clause and sub query cannot use relation R
• SELECT（ No, DISTINCT） The attribute list in must contain enough attributes , To ensure that the tuple of the view is inserted , Able to use null Or default values to fill in all other attributes that do not belong to the view
• The query does not contain group by perhaps having Clause

4. View creation and deletion

（1） establish :

CREATE VIEW View name （ List the sequence ）AS SELECT Query statement

（2） Delete view  

DROP VIEW View name

6、 ... and . Indexes （ In order to speed up the query , Can pass sql Statement type create delete index , Space for time ）

1. The function of index

• Improve query speed
• Improve connectivity ,ORDER BY and GROUP BY Speed of execution
• The query optimization process depends on indexes
• The uniqueness of enforcement

2. Index type

• Clustered index ： The physical order in the data table records is the same as the index order , That is, the order of miniature determines the storage order of record rows in the table , Records are sorted , So each table can only have one clustered index
• Nonclustered indexes ： Each column in the table can establish its own nonclustered index
• Uniqueness index ： The index values of any two records in the identification table are different , Similar to the primary key of a table , It can Make sure that the index column does not contain duplicate values

Be careful ： A unique index will be automatically created on the columns that create primary keys and uniqueness constraints , If no other clustered index is created in the table , The clustered index will be automatically created

3. Create index

create index Index name on Table name

4. Delete index

DROP INDEX Table name , Index name