后端MySQL常用命令-查询/select

时间:2024-01-23 22:55:22

基本查询

SELECT 字段名 from 表名

SELECT id,name,sex,age,weight,birth from ninth_student

条件查询

用where表示查询的条件

SELECT 字段名 from 表名 WHERE 条件

SELECT id,name,age,sex,weight,birth from ninth_student WHERE id BETWEEN 1 and 5

SELECT 字段名 from 表名 WHERE 条件 and/or 条件

SELECT id,name,age,sex,weight,birth from ninth_student WHERE id>5 and sex='男'
SELECT id,name,age,sex,weight,birth from ninth_student WHERE id>5 OR sex='男'

模糊查询/like

利用%

SELECT id,name,age,sex,weight,birth from ninth_student WHERE name LIKE '%亚'
SELECT id,name,age,sex,weight,birth from ninth_student WHERE name LIKE '亚%'
SELECT id,name,age,sex,weight,birth from ninth_student WHERE name LIKE '%亚%'

利用_,一个_代表一个字符,如果前面有两个字,就加两个

SELECT id,name,age,sex,weight,birth from ninth_student WHERE name LIKE '_马'

分页查询/limit

SELECT 字段名 表名 LIMIT (n-1)*每页展示个数,每页展示个数

SELECT id,name,age,sex,weight,birth from ninth_student LIMIT 0,4
SELECT id,name,age,sex,weight,birth from ninth_student LIMIT 4,4

排序

SELECT 字段名 from 表名 ORDER BY age ASC/DESC

SELECT id,name,age,sex,weight,birth from ninth_student ORDER BY age ASC
SELECT id,name,age,sex,weight,birth from ninth_student ORDER BY id DESC

聚合函数

SELECT COUNT(0) from 表名:表示总共有几条数据

SELECT SUM(字段名) from 表名:求和

SELECT AVG(字段名) from 表名:平均数

SELECT MIN(字段名) from 表名:最大

SELECT MAX(字段名) from 表名:最小

SELECT CAST(AVG(字段名) as DECIMAL(10,0)) as AVG from 表名:类型转换

SELECT COUNT(0) from ninth_student
SELECT SUM(age) from ninth_student
SELECT AVG(age) from ninth_student
SELECT MIN(age) from ninth_student
SELECT MAX(age) from ninth_student
SELECT CAST(AVG(age) as DECIMAL(10,0)) as AVG from ninth_student

分组查询

SELECT 字段名,聚合函数 from 表名 GROUP BY 字段名

GROUP BY 字段名:根据...分组

group by 分组字段,select后跟的也是相应的分组字段,他俩是一致的

where是分组前,having是分组后

having后面跟条件的话,也只能跟聚合函数

SELECT sex,COUNT(0),AVG(age) from ninth_student GROUP BY sex
SELECT sex,COUNT(0),AVG(age) from ninth_student WHERE age>22 GROUP BY sex HAVING AVG(age)>20

子查询

把查询到的结果当做另一个查询的条件

SELECT 字段名 from 表1 WHERE 字段名1 = (SELECT 表1_字段名1 from 表 WHERE 字段名='值')

SELECT name from dept WHERE id = (SELECT dept_id from ninth_student WHERE name='小马')

内连接

多表联查,一定要待条件!!!

inner join 表示内连接

on 表和表之间的连接,只能用on

inner join ... on ... 只要看见join就加on

看到,就写where

SELECT 表1.字段名1,表1.字段名1,字段名1,字段名1,字段名1,字段名1,表2.字段名2,表2.字段名2 from 表1 INNER JOIN 表2 ON 表1.字段名1 = 表2.字段名2

SELECT ninth_student.id,ninth_student.name,age,sex,weight,birth,dept.id,dept.name from ninth_student INNER JOIN dept ON ninth_student.id = dept.id
SELECT ninth_student.id,ninth_student.name,age,sex,weight,birth,dept.id,dept.name from ninth_student,dept WHERE ninth_student.dept_id = dept.id
SELECT ninth_student.id,ninth_student.name,age,sex,weight,birth,dept.id,dept.name from ninth_student,dept WHERE ninth_student.id = dept.id

外连接

在实际开发中,左外连接用的比较多

left join查询左边表的所有部分和右边表的交集,就是以左边的表为主

左外是以左边的表为主表,查询的结果只关注左表,不在乎右边

right join查询的是右边表的所有部分和左边表的交集,就是以右边的表为主

SELECT ninth_student.id,ninth_student.name,sex,age,weight,birth,dept.id,dept.name from dept LEFT JOIN ninth_student ON ninth_student.dept_id = dept.id
SELECT ninth_student.id,ninth_student.name,sex,age,weight,birth,dept.id,dept.name from dept RIGHT JOIN ninth_student ON ninth_student.dept_id = dept.id