MySQL Single table query

1. Querying All Records
SELECT * from department;
SELECT * from student;
SELECT * from Student_detail;

2. Querying the selected column records

3. Querying for records under specified conditions
Select S_name from student where s_id>2;

4. Alias the column after the query
Select S_name as name from student;

5. Fuzzy query

INSERT into student (s_name,dept_id) VALUES (' Zhang Sanfeng ', 2), (' Xiao Ming ', 3), (' Little Red ', 2);
SELECT * FROM student where s_name like ' small% ';

6. Sort order By:asc Ascending (default) desc Descending
Descending: SELECT * from ' SELECT ' ORDER by ' s_id ' desc;

7. Limit the number of displayed data
#按学生学号升序输出的前4条数据
Mysql> SELECT * from ' SELECT ' ORDER by s_id limit 2;

#指定的返回的数据的位置和数量
Mysql> SELECT * from ' SELECT ' ORDER by s_id limit 2, 2;

8. Common aggregation functions
#求最大年龄
Mysql> Select MAX (age) from Student_detail;

#求最小年龄
Mysql> Select MIN (age) from Student_detail;

#求和
Mysql> Select SUM (age) from Student_detail;

#求平均数
Mysql> Select AVG (age) from Student_detail;

#四舍五入
Mysql> Select ROUND (AVG (age)) from Student_details;

#统计
Mysql> Select COUNT (s_id) from student;

9. Packet Query GROUP BY

#对学生表中学院栏进行分组, and statistics on the number of students in the college:
Mysql> Select DEPT_ID as Academy ID, COUNT (dept_id) as number of students from student group by dept_id;

Having group conditions
After having the field must be a select after the occurrence of the

# See which colleges, only one student
Mysql> Select DEPT_ID as Academy ID, COUNT (dept_id) as student number from student group by dept_id number of students = 1;

MySQL Single table query