给表起别名 从 from 后的表后面加上要起 给字段起别名 从 select Select Studentname fro m student 差单列 Select StudentName, phone from student; 差多列 Select student.StudentName,student.Sex from student; Select s.studentName,s.Phone from student AS s 的表名 Select s.StudentName as 姓名，s.Phone ‘电话’ from student AS s; 后的字段名后面加上要起的字段名称 查询条件 where Select s.StudentName,s.Phone from student s where s.StudentName 算符： 1 算术运算符：+-*/%div 2 比较运算符：>,<,=,=,<>/!=,in,not in,between,and,like,is null,is not null 点 is null 没有值 is not null 有值 任何数和 unll 的值是 unll div 代表不同的 =’郭靖’ like 代表符合...特 注意，null 与空字符''意思并不一样 就相当于 a between 20 and 30 和 a >= 20 and a <= 30 意思一样 3 赋值运算符：=，：= 查空 “:=” 是真正意义上的赋值操作，左边的变量设置为右边的值。 原来有的值，后来删除的空 Select* From student Where Address = “ 本来就没有值的空 Select* from student s where sex is null 4 逻辑运算符：&&/and,||/or,not/!, if(!a) { b }，||是或者的意思。 “; a && b === if(a) { b }，&&是同时必须满足的意思。a || b === 查连续区间 Select * from student s where s .studentNo >=1010 and s . studentNo <=1015; Select * from student s where s . studentNo between 1010 and 1015; Select * from student s where s . studentNo = 1000 or s . studentNo = 1010 or s . studentNo =1015; Select * from student s where s . studentNo in (1000,1010,1015); Select * from student s where s . studentNo not in (1000,1010,1015); 模糊查询 Select * from student where student . studentName like ‘郭_’; 查询姓郭学生的两个字的名 Select * from student where student . studentName like ‘张%’； 查询姓张学生的所有字的名 Select * from student where student . studentName like ‘张%/%‘ 查询张字在中间学生的名的学生 Order by 排序 正序 select * from student order by BornDate asc; #asc 可省略 asc 升序 倒序 select * from student order by desc; desc 降序 多字段排序 select * from student orber by sex , BornDate desc; Limit 控制返回行数，多由于分页 Select * from student order by student . BornDate desc limit 0 , 5; 0 是页数 5 是每页的个数 Select * from student order by student . BornDate desc limit 5 取生日最大的前 5 个 统计函数 求和 sum( ) 求平均数 avg ( ) 求行数 count（ ） # null 不计入统计 Select count( * ) from student select count( sex ) select count( 1 ) from student; 求最大值 max( ) 求最小值 min( ) select ceil(12.1) ceil 向上舍入最接近的整数，大于或者等于 

Select floor(12.1) floor 小于或者等于 X 的最大整数 select now( )； select year(‘ ‘ ); Where 添加条件 group by 分组 having 分组后加条件 order by 排序 limit 限制返回条数 连表查询 where 等值连接查询 Select * from result r ,student s where s . studentNo=r . studentNo; 两个表中有相同的数值 Join on 表连接 数据连接 左外连接 Select * from student s left join result r on s.StudentNo = r.StudentNo; 左补 Select s.studentName from student s left join result r on s.studentNO = r.studentNO where r.studentNo is null; 右外连接 Select * from student s right join grade g on s.GradeId = g.GradeID; 右补 Select * from student s right join grade g on s.GradeID = g.GradeID where s.Address is null; 差集 Select * from student s left join grade g on s.GradeID = g.GradeID where g.GradeID is null Union Select * from student s left join grade g on s.GradeID = g.GradeID where s.GradeID is null; union 操作符用于合并两个或多个 SELECT 语句的结果集，必须拥有相同数量的列，列也必须拥有相似的数据类型，每 条 SELECT 语句中的列的顺序必须相同。 全集 相当于左外连接合并右外连接 Select * from student s left join grade g on s.GradeId = g.GradeID Union Select * from student s right join grade g on s.GradeID = g.GradeID 笛卡尔积 Select * from student,grade; select * from student join grade; 如果 on 条件是非唯一字段，会出现笛卡尔积(局部笛卡尔积)；如果 on 条件是表的唯一字段，则不会出现笛卡尔积 自连接 Select * from result r1 ,result r2 where r1.subjectNo = 1 and r2.subjectNo = 2 And r1.studentResult > r2.StudentResult and r1.studentNo = r2.studentNo; 分组查询 Select sex, count(1) From student Where student.sex = 1 Union Select sex, count(1) from student where student.sex = 2; Select s.sex,count(1) From student s group by s.sex Having count(1)>10; Select 5 查什么 from 1 从哪查 where 2 行过滤 group by3 分组查询 having 4 分组后加条件 Order by 排序 limit 7 限制访问条数 

子查询 以一个查询的结果作为另一个查询的条件，这就是子查询。内部的查询叫做子查询，外部的查询叫做父查询。 子查询的分类： A 按照子查询与父查询的关系分类： 1，非相关子查询 2，相关子查询 B 按照子查询返回的结果分类： 1 标量子查询 2 行子查询 3 列子查询 4 表子查询 返回多行多列，多用于 FROM 后，作为表存在，必须其别名 Select s.BornDate from student s where s.StudentName =’郭靖’； 1986-12-11 00：00：00 Select * from student s where s.BornDate<(select s.BornDate from student s where s.StudentName = ‘郭靖 ‘）; 列子查询，运算符用 in Select * from student where student.GradeID in (select GradeID from grade where grade.GradeID>2); 表字查询 Select a.studentno,studentname from(select* from student where student.studentno between 1010 and 1015) a; 条件：叶平老师/成绩最高 所查：学生姓名/成绩 Select sc.sid,sc.cid,sc.score From sc,course c ,teacher t Where sc.cid = c.cid And c.tid= t.tid And t.tname = ‘叶平’ And sc.score = (select max(score) from sc,course c1 ,teacher t where sc.cid = ci.cid and cq.tid= t.tid and t.tname = ‘叶平’ and c.cid = c1.cid); 叶平老师所受课程的最高分 Select max(score) From sc,course c1 = c1.cid and c1.tid= t.tid and t.tname = ‘叶平’ and c1.cid = ?