day05

查询所有的行和列
-- *代表所有列,一般在项目中不允许使用，按需搜索
SELECT*FROM student;
# 查询部分列
SELECT student_no,student_name,sex FROM student;
# WHERE 查询条件
SELECT student_no,student_name,sex FROM student
WHERE sex='男';
-- 别名  外号
SELECT student_no as 学号,student_name as 姓名,sex as 性别 FROM student
WHERE sex='男';

SELECT student_no 学号,student_name 姓名,sex 性别 FROM student
WHERE sex='男';
-- 查询所有成绩小于80分的学生
-- 查询2024-09-09之后的所有考试
-- 查询所有科目信息
-- 查询所有年级信息
SELECT * FROM result WHERE student_result<80
SELECT * FROM result WHERE examDate>'2024-09-09'
SELECT subject_name as 科目 FROM subject;
SELECT grade_name AS 年级 FROM grade;
-- 查询null  NULL是从来没有添加过数组，或者添加null
SELECT * FROM student WHERE sex is NULL;
--  被清空的列 使用''
SELECT * FROM student WHERE sex ='' OR sex IS NULL;

-- 分页 （开始，数量）
SELECT * FROM student LIMIT 5; 

SELECT * FROM student LIMIT 0,5; 
# LIMIT 要放在语句的最后
SELECT * FROM student WHERE sex='男' LIMIT 5,5;

SELECT * FROM student WHERE grade_id=1;
SELECT student_no FROM student WHERE grade_id=1;  
SELECT * FROM student WHERE sex='女';
SELECT * FROM subjects WHERE class_hour>60;
SELECT phone FROM student WHERE student_name='咳咳咳';
SELECT student_name,address FROM student WHERE sex='男'
SELECT * FROM student LIMIT 5,13;

-- 模糊查询 项目中尽量不要使用
-- 张*
SELECT * FROM student WHERE  student_name LIKE '张__'
-- % 不限长度的字符
-- 手机号中有23
SELECT * FROM student WHERE phone LIKE '%23%'
-- 以名字包含铮的学生
SELECT * FROM student WHERE student_name LIKE '%铮%'

SELECT * FROM result WHERE student_result BETWEEN 60 AND 80;

-- '孟二金','瑶'
SELECT * FROM student WHERE student_name in ('孟二金','瑶')

SELECT * FROM result WHERE subject_id in (1,2)

SELECT * FROM student WHERE student_name NOT LIKE '%张%'

SELECT * FROM student ORDER BY birthday ASC;
-- DESC 降序
SELECT * FROM student ORDER BY birthday DESC;

-- 根据多个字段进行排序
SELECT * FROM student ORDER BY birthday DESC ,sex;
-- 获取成绩的前两名的信息
SELECT * FROM result ORDER BY student_result DESC LIMIT 2;