Shulou(Shulou.com)06/02 Report--

This article mainly explains "what are the query methods of SQL language". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what are the query methods of SQL language".

Foreign key constraint

MySQL is a relational database, the relationship between tables can be established, such as: student table and score table, add the student number in the score table to reference the student number in the student table, so that you do not have to add duplicate student information in the grade table, this relationship is also called primary and foreign key relationship, which can be achieved by setting foreign key constraints.

When you create a table, you can add foreign key constraints to ensure referential integrity between the table and the table. After adding a foreign key:

Before inserting foreign key table data, you must insert master table data

You must delete the foreign key table data before deleting the master table data

Syntax:

Create table table name

(

Field name type constraint

...

Constraint foreign key name foreign key (foreign key column) references primary table (primary key)

);

Code example:

Use mysql_db

-- create a score sheet

Drop table if exists tb_score

Create table tb_score

(

Score_id int primary key auto_increment

Score_stu_id int

Score int

Score_course varchar (20)

Constraint fk_score_stu_id foreign key (score_stu_id) references tb_student (stu_id)

);

Internal join query

In the query, we often have to query the relevant fields of multiple tables together, such as when querying students' scores, we should display the scores and students' names. At this time, we need to join query, join query is divided into internal join and external join, we first learn the inner join query.

The characteristic of the inner join query is that it will query the data that exists in the relevant tables.

There are two ways to implement syntax:

1) select field. From Table 1 inner join Table 2

On Table 1. Primary key = Table 2. Foreign key

Note: it is assumed that Table 1 is the main table, and the order of the inner join tables is independent.

2) select field. From Table 1, Table 2

Where Table 1. Primary key = Table 2. Foreign key

Code example:

-- query students' names and achievement methods 1

Select s.stu_id, s. Stuffed name on s.stu_id c. Scorekeeper mom c. Score from tb_score c inner join tb_student s on s.stu_id = c.score_stu_id

-- Mode 2

Select s.stu_id, s. Stuffed name where s.stu_id c. Scorekeeper head c. Score from tb_score c, tb_student s where s.stu_id = c.score_stu_id

The effect is the same:

Cdn.xitu.io/2019/6/5/16b265444d53bca3?w=300&h=196&f=png&s=35605 ">

External join query

The outer connection is divided into left outer connection and right outer connection:

1) left outer connection

Join queries the data of multiple tables, showing all the data of the left table, and there is a data complement null that does not match in the right table.

Syntax:

Select field... From left table left join right table

On master table. Primary key = child table. Foreign key

Code example:

-- left outer link to query students' names and grades

Select s. Stub _ from tb_student s left join tb_score _ on s.stu_id s. Stub _ name _ journal c. Scorekeeper _ camera _ journal c _ score _ on s.stu_id = c.score_stu_id

-- inquire all the students who have taken the exam

Select s. Stub _ from tb_student s left join tb_score _ on s.stu_id s. Stub _ name _ journal c. Scorekeeper _ camera _ journal c _ score _ on s.stu_id = c.score_stu_id where c.score is not null

-- inquire all the students who have not taken the exam

Select s. Stub _ from tb_student s left join tb_score _ on s.stu_id s. Stub _ name _ journal c. Scorekeeper _ camera _ journal c _ score _ on s.stu_id = c.score_stu_id where c.score is null

2) right external connection

Contrary to the left join, all the data in the right table is displayed, and the data that does not match in the left table is supplemented by null.

Syntax:

Select field... From left table right join right table

On master table. Primary key = child table. Foreign key

Code example:

Select s.stu_id,s.stu_name,c.score_course,c.score from tb_score c right join tb_student s

On s.stu_id = c.score_stu_id

Subquery

The query statement can also be embedded in the query statement, and the embedded query is also called sub-query, and the sub-query will be executed first. After the query reaches the result, the parent query can use the result as a query condition to make another query, which can solve the more complex query problems.

Syntax:

Select... From table where field comparison operator (select... From where condition)

Code example:

Inquire about the students who are older than Li

Select * from tb_student where stu_age > (select stu_age from tb_student where stu_name ='Li Si')

Inquire about Li Si's fellow-townsman

Select * from tb_student where stu_address = (select stu_address from tb_student where stu_name ='Li Si')

The IN of the subquery

Sometimes when there is more than one result in a subquery, there will be an error using the comparison operator, so we need to use some keywords to help filter the results.

The in keyword is used to equate any one of the fields and data lists, and the condition holds.

Code example:

-- to inquire about students with the same language scores, first use sub-query to check the language scores, then use the links to check the names and scores of the students who have taken the language examination, and compare the scores.

Select stu_name,score_course,score from tb_student inner join tb_score on tb_student.stu_id = tb_score.score_stu_id where score_course=' language 'and score in (select score from tb_score where score_course=' language')

The ALL of the subquery

All is used in conjunction with the comparison operator, and the result is only valid if the field and all query results are valid.

Syntax:

Field comparison operation all (query results)

Code example:

-- inquire about the female students who are younger than all the male students, and first check the ages of all the male students. If the female students are older than all of them, find out.

Select stu_name,stu_age,stu_gender from tb_student where stu_gender = 'female' and stu_age

< all(select stu_age from tb_student where stu_gender = '男'); 子查询之ANY any和比较运算符配合使用，如果字段和任意一个查询结果比较成立，则结果成立。 语法： 字段 比较运算 any(查询结果) 代码示例： -- 查询只要比一个南京学生大的武汉学生 select stu_name,stu_address from tb_student where stu_address = '武汉' and stu_age >

Any (select stu_age from tb_student where stu_address=' Nanjing')

The Exists of the subquery

Exists indicates whether there is a query result. If there is no result, false is returned. If there is a result, true is returned.

Syntax:

Exists (query results)

For students who have passed the English test, you need to determine whether the student id in the parent query exists in the child query.

Select * from tb_student where

Exists (select score_stu_id from tb_score where tb_student.stu_id = tb_score.score_stu_id and score_course = 'English')

Thank you for your reading, the above is the content of "what are the query methods of SQL language". After the study of this article, I believe you have a deeper understanding of what the query methods of SQL language have, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.