MySQL Simple query operation

Before saying so much, now finally to our vast number of programmers are most concerned about the "query" operation.

Query operations

-- 语法select [select选项] 字段列表 [as] 字段别名 /* from 数据源 [where条件语句] [group by 子句] [having 子句] [order by 子句] [limit 子句];

Select option:

Select how to handle the results of the query
All: Default, all results are preserved
DISTINCT: Will find out the results, remove duplicates (all fields are the same)

Field aliases

-- 语法字段 [as] 别名select studentid as "学生id",number as "学号", name "姓名" from my_student;

WHERE clause

WHERE clause: Used to determine filtering data.
The WHERE clause returns the result: 0 or 1, where 0 means false,1 represents true.

• Judging condition:
  Comparison operators: >,<,>=,<=,<>,=, like, between and, in, not in
  Logical operators: && (and), | | (OR),! (not)

Where principle: Remove a record from disk, start where to judge, determine if the result is saved to memory, if the failure is directly discarded.

-- 下面为了演示where语句，我先为my_student表格增加两个字段-- 增加age和height字段alter table my_student add age tinyint unsigned;alter table my_student add height tinyint unsigned;

-- 增加值：update my_student set age=floor(rand() * 20 + 20),height=floor(rand() * 20 + 170);

• Find students with a student ID of 1 or 3

select * from my_student where studentid in(1,3);-- 或者select * from my_student where studentid = 1 || studentid = 3;

• Find students who are 180 to 190 tall

select * from my_student where height > 180 and height < 190;-- 或者select * from my_student where height between 180 and 190;

GROUP BY clause

GROUP BY: Grouped according to a field (same to 1 groups, different to different groups)

-- 基本语法group by 字段名

Meaning of grouping: for statistical data (data statistics by Group field)
SQL provides a series of statistical functions:

count() :统计分组后的记录数，每一组有多少记录max() :统计每组中最大的值min() : 统计最小值Avg() : 统计平均值Sum() : 统计和

-- 为了演示groupbyaddenum(‘boy‘,‘girl‘);

-- 按照性别分组统计：身高高矮，平均年龄和年龄总和select gender,count(*),max(height),min(height),avg(age),sum(age) from my_student group by gender;

It is important to note that the count function used above: Two parameters can be used: "*" for Statistical records, "field name" to indicate the corresponding field (null not counted)

select gender,count(*),count(age),max(height),min(height),avg(age),sum(age) from my_student group by gender;

• Multi-field grouping: Group by one field first, then group the results again by the other results.
  

-- 多字段分组：先按照班级分组，在按照性别分组select c_id,gender,count(*) from my_student group by c_id,gender;   

• Group_concat (field)
  Group_concat (field): This function can string concatenation of a field in a grouped result

-- 使用group_contact() 函数select c_id,gender,count(*),group_concat(name) from my_student group by c_id,gender;    

HAVING clause

Having clauses: making conditional judgments.

The difference between having and where: where is the determination of the disk data, into the memory, will be grouped operations, the results of the group needs to be processed. In addition, the having can use aliases, and where is not, because where is the data from the disk, you can only use the field name, alias is generated after the field into memory.

-- 查询所有班级人数>2的学生人数select c_id,count(*) from my_student group by c_id having count(*) >=2;-- 上面的sql语句中多次使用了count(*)函数，我们可以对count(*)重新命名，做如下优化：select c_id,count(*) as total from my_student group by c_id having total >=2;

ORDER BY clause

ORDER By: Sort Ascending or descending according to a field

-- 语法：order by 字段名 [asc |descc]select * from my_student order by c_id;-- 先按照班级升序，在按照年龄降序  select * from my_student order by c_id,age desc;

Limit clause

Limit:limit can be used to limit the number of results of a query

There are two ways to use limit:

• Used to limit the amount of data: limit data volume
• Limit the location, limit the number: limit actually position, length

-- 查询my_student表中的前两条记录select * from my_student limit 2;

-- 从第2条记录开始，查询两条数据select *　from my_student limit 2, 2;

Connection Query

Connection query: Connect multiple tables and record them. The connection query is divided into the following four categories: Inner connection, outer connection, natural connection, cross connection

-- 使用语法左表 join 右表

-- 查询出所有的学生，并且显示学生所在的班级信息

Cross Connect

Cross connect: Loops out each record from a table, each record goes to another table to match, matches a certain reservation (unconditional match)

-- 交叉连接语法： 左表 cross join 右表 === from 左表,右表select * from my_student cross join my_class;select * from my_student,my_class;

Internal connection

INNER JOIN: Inner JOIN, take each record from the left table, go to the right table to match all records, match must be a condition in the left table is the same as in the right table, the result will not be retained in the end.

-- 内连接基本语法：左表 inner join 右表 on 左表.字段 = 右表.字段select * from my_student inner join my_class on my_student.c_id = my_class.c_id;

In addition, you can use the aliases of fields and tables to simplify SQL statements

-- 字段别名和表别名select s.* ,c.c_name,c.c_room from my_student as s inner join my_class as c on s.c_id = c.c_id;

External connection

Outer join: Take a table as the main, remove all the records inside, and then each with another table to connect, whether or not match, eventually will be preserved, can match, then the correct reservation, otherwise the field of the other table value is null null.

There are two types of outer joins: The main one is a table
Left join: outer JOIN, left table to main table
Right Join: Connected

-- 外连接基本语法：坐标 left/right join 右表 on 左表.字段 = 右表.字段-- 左连接select s.*,c.c_name,c.c_room from my_student as s left join my_class as c on s.c_id = c.c_id;

Natural connection

Natural connection: Automatically match the connection condition: The system automatically takes the field name as the matching pattern (the same name fields as the condition, multiple same name fields as conditions)

-- 自然内连接：左表 natural join 右表select *　from my_student natural join my_class;

MySQL Simple query operation