MySQL table query command and MySQL table Operation Command

MySQL table query command and MySQL table Operation Command
1. Basic query statement
Syntax:
Select attribute list
From table name (View) List
[Where condition expression 1]
[Group by attribute name 1 [having condition expression 2]
[Order by attribute name 2 [asc | desc]
Asc: ascending
Desc: Descending Order
2. Field Query
Example:
Select * from subject;/* query all data in the table */
3. query specified fields
Select SubjectName from subject;
4. query a specified record
Select SubjectName from subject where SubjectNo = 1;
5. query condition symbols or keywords
Comparison =, <, <=,>,> = ,! =, <>,!> ,! <
The specified range is between and, not between.
Specify the set in and not in
Matching characters like and not like
Is null or is not null?
Multiple query conditions and, or
6. deduplicate distinct in the query results
Select distinct SubjectNo from subject;
7. order by (in ascending order by default) [asc | desc]
Select SubjectName from subject order by SubjectNo;
-- Multi-field sorting:
First sort by the first field. If the same data is encountered, then sort by the second field.
8. group by grouping Query
8.1 use group by separately. One record for each group is displayed.
8.2 group_concat () in combination with ----------- group by sex to obtain the name of each group
Select sex, group_concat (name) from employee group by sex;
8.3 combined with Aggregate functions ------------ grouping with sex to obtain the number of members of each group
Select sex, count (sex) from employee group by sex;
8.4 use with where to restrict query Conditions
8.5 use with having to filter query results


More than 8.6 field groups
Select * from employee group by id, name;
8.7 combined with rollup ----- adds a data entry, which displays the total of all queried data.
Select * from employee group by id, name with rollup;
8.8 limit the number of query results
Select * from employee group by id limit 3;/* Display Three query results */
Select * from employee group by id limit;/* display the query results of subscript from 0 to 10 */
9. Aggregate Function Query
Count
Select count (id) from employee;/* query the number of all records in the field id */
Sum (), avg (), max (), min () is the same as count usage
10. link query
Join Query within 10.1 (where | inner join... on ...)
(The inner join is A perfect match, that is, table A and table B correspond one to one. If one party does not, the record will not be detected)
10.2 left outer link (left join... on)
(The left table is the primary table, showing all the information of the primary table, and NULL is displayed if there is no matching information with the right table)
10.3 right Outer join... on)
(The right table is the main table, showing all information in the right table)
10.4 compound query (inner | left | right join... on... where ...)
11. subquery
Subquery: nest one query statement in another query statement. The query results of the inner query statement provide conditions for the outer query.
11.1 keyword with in [not in]
Select * from employe where id in (select id from department );
11.2 comparison Operators
11.3 keyword with exists
11.4 With the any keyword (if one of the subqueries is satisfied, the parent query is executed)
Select SubjectName, GradeId from subject
Where GradeId> = any
(Select GradeId from grade );
11.5 With the all keyword (if all in the subquery is satisfied, the parent query is executed)
Select SubjectName, GradeId from subject
Where GradeId> = all
(Select GradeId from grade );
12. Merge query results
12.1 union)
Select * from employee
Union
Select * from department;
12.2 union all (merge query results do not remove duplicates)
13. Alias for table and field
13.1 alias for the table
Select * from employee as employee where id <11;
13.2 alias for Field
Select studentName as student name from student;
As clause
You can create a new alias for the data column.
You can get a new alias for the table.
The computed summary results can be replaced by another new name.
Note: as can also be omitted without writing
13.3 select concat (email, @ ibeifeng.com) as email from student; -- Query email
14. like keyword (fuzzy query)
Select name from class where name like 'Liu % '['Liu _'];
% Indicates matching all
_ Indicates matching a character
15. in keyword subquery (in a range)
Select name from class where age in (10, 11 );
16. Notes:
-- Arithmetic operations can be performed between records of the numerical data type;
-- Only data of the same data type can be compared;
17. limit Query
Select * from tableName limit 5; # Return the first five pieces of data in the query results
Select * from tableName limit 5, 10; # Return the 6-10 data records of the query results
18. union merge Query
Union merges identical data (one row of the same data is displayed together with the different data)
Union all: displays the data queried from the two tables.
Eg:
Select name from subject
Union [all]
Select name from myclass; View comments