Shulou(Shulou.com)05/31 Report--

This article mainly describes how mysql database query statements, the text is very detailed, has a certain reference value, interested friends must read!

Query statements: 1."select * from table name;" to query all the data in the table;2."select field name from table name;" to query the data of the specified field in the table;3."select distinct field name from table name" to query the data in the table.

Operating environment of this tutorial: Windows 7 system, MySQL8 version, Dell G3 computer.

Single table query

1. General inquiry

(1) Command: select * from ;//pass

(2) Order: select from ;

2. Duplicate query (distinct)

Command: select distinct from

3. Sort query (order by)

Ascending: asc

Descending order: desc

Select from order by desc

No desc generally defaults to ascending order

4. Group query (group by)

Command: select , Sum(score) from group by

Suppose we now have another student result. Ask for a student's total score. We divided them into different groups according to their student numbers.

Command:

mysql>select id, Sum(score) from result group by id; multitable query

1. Equivalence query

There are now two tables:

Now I want to inquire about the failing grades of students younger than 20.

sentence: select stu.id,score from stu,result where stu.id = result.id and age

< 20 and score < 60; 它的查询如下图所示： 可见等值查询效率太低 二、连接查询 1、外连接查询 （1）左外连接查询 假设我们依旧使用的是上面的两个表，任然查询年龄小于20岁学生的不及格成绩 我们利用左外连接查询，先将学生表中所有年龄小于20岁的学生取出来，再在成绩表中将所有成绩小于60的学生取出来，然后再进行配对，我们会发现效率大大得提高，只用匹配四次就可以找到。 如下图所示： 语句为： select a.id,scorefrom(select id,age from stu where age < 20) a (过滤左表信息)left join(select id, score from result where score < 60) b (过滤右表信息)on a.id = b.id; 左外连接就是左表过滤的结果必须全部存在。如果存在左表中过滤出来的数据，右表没有匹配上，这样的话右表就会出现NULL； （2）右外连接查询 select a.id,score from (select id,age from stu where age < 20) a (过滤左表信息) right join (select id, score from result where score < 60) b (过滤右表信息) on a.id = b.id; 左外连接就是左表过滤的结果必须全部存在 如图： 我们发现过滤出来的表进行的匹配只有两条满足条件（红色代表条件满足），但最后的结果却是： 左表不匹配的数据改为空，右表过滤出来的数据都要存在。 （3）全外连接查询 结合了左外连接和右外连接，使得左表和右表的数据都存在。 2、内连接查询 只筛选匹配结果 比如过滤的结果如下： 最后的结果为：

Match only the results we need.

The sentence is:

select a.id,score from (select id,age from stu where age < 20) a (filter left table information) inner join (select id, score from result where score < 60) b (filter right table information) on a.id = b.id; above is "mysql database how to implement query statements" All the contents of this article, thank you for reading! Hope to share the content to help everyone, more relevant knowledge, welcome to pay attention to the industry information channel!

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.