准备数据,倒入sql文件
运行sql文件 得到四张表
select * from 表名 * 代表全部
1、AS子句作为别名
select studentname as "姓名" , sex as "性别",gradeid as "班级ID" from student;
多表查询给表起别名 简化代码
2、distinct 去重
DISTINCT关键字
作用:
去掉SELECT查询返回的记录结果中重复的记录(所有返回列的值都相同),只返回一条
语法:
多条语句去重
select distinct ID,NAME fromstudent;
以 ID和NAME 为单位
例如:如下不算重复1aa1 bb
3、where条件语句
4、LIKE模糊查询
查询姓李的同学记录%所有字符
select* from student where StudentName like "李%";"_"代表一个字符
查询姓李X(名只有1个)的同学记录
select* from student where StudentName like "李_";
查询名字中包含文的记录
select* from student where StudentName like "%文%";
查询以 姓名 明 结尾的纪录
select* from student where StudentName like "%明"
查询所有姓 李 的学生所有成绩
select s.StudentNo as "'李'同学学号",r.stuResult as "'李'同学成绩" from student as s ,result as r
where s.StudentNo=r.StudentNo AND s.StudentName like "李%";
查询 Java的考试成绩
select s.subjectName as java,r.stuResult as "java的成绩" from subject as s ,result as r
where s.SubjectName="java" and s.SubjectNo=r.SubjectNo;
查询考试JAVA的学生的学号和考试成绩
select r.StudentNo as "学号",r.stuResult AS "考试成绩" from result as r ,subject as s
where r.subjectNo=s.subjectNo and s.subjectName="java";
查询考试Java的学生学号,姓名,考试成绩
select r.StudentNo as "学号",st.StudentName as "姓名",r.stuResult AS "考试成绩"
from result as r ,subject as s,student as st where
r.subjectNo=s.subjectNo AND
r.StudentNo=st.StudentNo and
s.subjectName="java";
5 查询Student表中年龄为12或性别为“女”的同学记录。
CURDATE() 函数返回当前的日期。
select * from student where TIMESTAMPDIFF(YEAR,BornDate,CURDATE())=12 or Sex=0;
year(now())-year(BronDate)
TIMESTAMPDIFF函数,有参数设置,
可以精确到天(DAY)、小时(HOUR),分钟(MINUTE)和秒(SECOND),
。对于比较的两个时间,时间小的放在前面,时间大的放在后面。
5、联合查询
SELECT 字段列表
FROM 表1 INNER|LEFT|RIGHT JOIN 表2
ON 表1.字段= 表2.字段;
要求:从subject和grade数据表查询课程名称和所属年级名称
select s.subjectName as "课程名称" ,g.gradeName as "年级名称" from subject as s INNER JOIN grade as g
on s.gradeid=g.gradeID;
JION左边的是左表 JOIN JOIN右边的是右表
6、order by排序
查询所有考试结果,并按成绩由高到低排列