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