mysql学习笔记 查找技术 1207 0311

# 查看学生表
SELECT * FROM student;

# 查分数等于200分的学生
SELECT * FROM student WHERE score = 200;

# 查询分数不等于200的所有数据
SELECT * FROM student WHERE score != 200;
SELECT * FROM student WHERE score <> 200;

# 查城市为天门并且分数为100分的
SELECT * FROM student WHERE place="天门" AND score=100;
SELECT * FROM student WHERE place="天门" && score=100;


# 查询城点为襄阳或者分数为200的数据
SELECT * FROM student WHERE place="襄阳" OR score=200;
SELECT * FROM student WHERE place="襄阳" || score=200;

# 查找id在2-4之间的所有的数据，包含2与4
SELECT * FROM student WHERE id>=2 AND id<=4;
SELECT * FROM student WHERE id BETWEEN 2 AND 4;

# 查找地点要么是武汉的，要么是天门的
SELECT * FROM student WHERE place = "武汉" OR place = "天门";
SELECT * FROM student WHERE place IN ("武汉","天门");

# 空查找 查找为空的数据
SELECT * FROM student WHERE score IS NULL;

# 空查找 查找不为空的数据
SELECT * FROM student WHERE score IS NOT NULL;


# 模糊查询 查询姓诸葛的
SELECT * FROM student WHERE sname LIKE "诸葛%";

# 查询名字中是两个字的用户
SELECT * FROM student WHERE sname LIKE "__";

# 查询姓名中带有大的或者带有小的所有数据
SELECT * FROM student WHERE sname LIKE "%大%" OR sname LIKE "%小%";


# 排序查找 按分数升序排序
SELECT * FROM student ORDER BY score;
SELECT * FROM student ORDER BY score ASC;

# 按分数降序显示
SELECT * FROM student ORDER BY score DESC;

# 多个排序条件 按分数降序排 如果分数一样就按id降序排
SELECT * FROM student ORDER BY score DESC,id DESC;

# 插入一个数据
INSERT INTO student VALUES(NULL,"魔铃","圣域",0,138,"2021-1-1",NULL,3);