MySQL 查询

条件查询

符号
< = >
!=
<= >=
模糊条件
LIKE
NOT LIKE
包含两端值
BETWEEN AND
NOT BETWEEN AND
为null
IS NULL
IS NOT NULL
例子
SELECT * FROM teacher WHERE age > 18
SELECT * FROM teacher WHERE id_number LIKE ‘%020X’
SELECT * FROM teacher WHERE age > 18 AND name LIKE ‘王%’
SELECT * FROM teacher WHERE age > 25 OR name LIKE ‘王%’
UPDATE teacher SET id_number = ‘无’ WHERE age BETWEEN 20 AND 30

联合查询

UNION ALL
SELECT * FROM teacher WHERE age > 20
UNION ALL
SELECT * FROM teacher WHERE age > 25
UNION
删除了重复数据
SELECT * FROM teacher WHERE age > 20
UNION
SELECT * FROM teacher WHERE age > 25

排序

关键词
ASC
DESC
单字段
SELECT * FROM teacher ORDER BY age ASC
多字段
SELECT * FROM teacher ORDER BY age DESC,id ASC
字符串
根据ASCII码

联表

语句
LEFT JOIN
INNER JOIN
RIGHT JOIN
例子
普通
SELECT * FROM course INNER JOIN teacher ON course.teacher_id=teacher.id
别名
SELECT c.id AS course_id,c.,t. FROM course c LEFT JOIN teacher t ON c.teacher_id=t.id
简单
SELECT teacher.name, teacher.age, course.course_name
FROM teacher, course
WHERE teacher.id = course.teacher_id

去重

DISTINCT
单字段
SELECT DISTINCT student_id
FROM
student_course a
INNER JOIN
student b
ON a.student_id=b.id
多字段
SELECT *
FROM
student_course a
INNER JOIN
course b
ON a.course_id=b.id
INNER JOIN teacher c
ON b.teacher_id=c.id
分组

聚合

语句
平均数
AVG()
总数
COUNT()
总和
SUM()
最小值
MIN()
最大值
MAX()
举例
SELECT name,MAX(age) as age FROM teacher