Database (connection query, variable)

One, connection query: Through the connection operator can implement more than one table query. Connection is the main feature of relational database model, and it is also a sign that distinguishes it from other types of database management system.

Common two link operators:

1.join on

2.union

in the relational database management system, the relationship between the data is not determined, and all the information of an entity is often stored in a single table. When retrieving data, the connection operation queries the information for different entities that reside in multiple tables. The connection operation gives the user a lot of flexibility and they can add new data types at any time. Create a new table for different entities, and then query through the connection.

Example 1

--all information and teacher in charge of the top five Chinese grades   Select Top 5Code,name,sex,yuwen,shuxue,yingyu, (SelectBoss fromBanjiwhereBanji.bcode=GRADE.BC) as'Head teacher',(SelectBanji fromBanjiwhereBanji.bcode=GRADE.BC) as 'class' fromGradeOrder  byYuwendesc   Select Top 5Grade.code,name,sex,yuwen,shuxue,yingyu,banji. Boss,banji fromGrade,banjiwhereBanji.bcode=Grade.bcOrder  byYuwendesc  --Link Query     Select Top 5Grade.code,name,sex,yuwen,shuxue,yingyu,banji. Boss,banji fromGradeJoinBanji onBanji.bcode=Grade.bcOrder  byYuwendesc     --link query with join on

Example 2

-- Joins   the grade table to the Banji table with the join on select   from Join  on Banji.bcode=GRADE.BC

Example 3

--Insert two incomplete dataInsert  intoGradeValues('Derong Wang','male', the,NULL,NULL,NULL)Insert  intoBanjiValues(1004,'Class Four',NULL)--Show AllSelectGrade.code,name,sex,yuwen,shuxue,yingyu,banji.banji,boss fromGrade Full JoinBanji onBanji.bcode=GRADE.BC--Show only leftSelectGrade.code,name,sex,yuwen,shuxue,yingyu,banji.banji,boss fromGrade Left JoinBanji onBanji.bcode=GRADE.BC--Show only RightSelectGrade.code,name,sex,yuwen,shuxue,yingyu,banji.banji,boss fromGrade Right JoinBanji onBanji.bcode=Grade.bc

Example 4

--View all information on the second and third classes with a language score greater than 85SelectCode,name,sex,yuwen,shuxue,yingyu, (SelectBanji fromBanjiwhereBanji.bcode=GRADE.BC) as 'class' fromGradewhereCodeinch(SelectCode fromGradewhereYuwen> -  andBc=(SelectBcode fromBanjiwhereBanji='Class Two' ))UnionSelectCode,name,sex,yuwen,shuxue,yingyu, (SelectBanji fromBanjiwhereBanji.bcode=GRADE.BC) as 'class' fromGradewhereCodeinch(SelectCode fromGradewhereYuwen> -  andBc=(SelectBcode fromBanjiwhereBanji='class Three'))

Query between three tables

Cases

--View the number, name, price, supplier, total quantity of the goods sold, select Code as ' number ', mname as ' name ', mprice as ' price ', gname as ' supplier ', (Mshu+cshu) as ' total merchandise ' from the men join Cang on Men.mcode=cang.code join gong on Cang.cg=gong.gcode

Two

Variable:

The SQL language, like other programming languages, has control statements such as variables, branches, loops, and so on.

In SQL language, variables are divided into local variables and global variables, and global variables are called system variables.

Local variables:

Using the declare keyword to declare a variable, the syntax is simple:declare @< variable name > < variable type >

You can assign values to variables using the set keyword, and you can assign only one value to a variable at a time using the Set keyword.

We can also assign a value to this variable in a query statement.

　　

Global variables: Also called system variables.

Examples of several system variables that are commonly used:

Variable Small exercise:

You need to print a single quotation mark in a printed string: Double-two single quotes.

Operator:

Practice:

Operator Precedence:

if...else ... statement:

Example:

while statement:

Example:

While if Nested examples: while in Break example of a jump show:

Database (connection query, variable)