sql语句单表查询以及多表查询

sql语句

• SELECT * FROM student;

• SELECT NAME FROM student;

• SELECT name,sex,age FROM student;

• #条件查询

• #WHERE后面跟的是条件查询的条件

• #and 和 多条条件语句的合并

• #or 或

  • SELECT * FROM student WHERE sex='男';

  • SELECT * FROM student WHERE sex='男' and age=20;

  • SELECT * FROM student WHERE sex='男' or age=20;

  • SELECT name,age FROM student WHERE not sex ='男';

  • SELECT * FROM student where class_num=20201001 and sex='男';

  • SELECT * FROM student where not class_num=20201001 and sex='女'; ​​​​​​​

     

• #去重查询DISTINCT

  SELECT DISTINCT age FROM student;

• #模糊查询LIKE

  #通配符 _ 只能匹配一个字符的通配符, % 可以匹配任意多个字符 

  #搜索功能实现用的是两个% SELECT * FROM student WHERE name like '%王%';

  #排序 ORDER BY后面跟的就是排序的依据

  #默认从小到大,倒叙排列的时候就用 DESC SELECT * FROM student ORDER BY age; SELECT * FROM student ORDER BY age DESC;

  #想通过年龄降序,学号升序 SELECT * FROM student ORDER BY age DESC,sno;

• #分页查询(限制查询) LIMIT

  #LIMIT后面跟的是限制查询的条数 

  #OFFSET是我们从哪里查询>>>>>默认从0开始查询 SELECT * FROM student LIMIT 3; SELECT * FROM student LIMIT 3 OFFSET 1;

  #每页展示的数量 pageSize;

  #当前页数 pageIndex; SELECT * FROM student LIMIT pageSize OFFSET pageSize *(pageIndex -1)

  #查询出年龄最高的学生信息 SELECT * FROM student ORDER BY age DESC LIMIT 1;

  #聚合查询

  #sum 求某一列的合并值

  #avg 求某一列的平均值

  #max 求某一列的最大值 #min 求某一列的最小值

  #count 求某一列的数据量

  #在sum(age)后面加xxx,代表算出来的值的名字. SELECT sum(age) num from student;

  #分组查询

  #查询出各个班男生的平均年龄 SELECT AVG(age) num FROM student GROUP BY class_num;

  #查询各个班的人数 SELECT count(*) 人数,class_num FROM student GROUP BY class_num;

• #多表查询

  • #多对一,或一对多的连接

  • inner join 查询出两个表交集的部分

  • left join 查询出左表单独的部分和左右表交集的部分(如下,左表有的右表没有,优先显示左表的)

  • 

  • right join 查询出右表单独的部分和左右表交集的部分(如下,右表有的左表没有,优先显示右表的)

  • 

    • #两种方式(第二个是内连接)

      SELECT t1.name,t2.deptname FROM yuangong t1,dept t2 where t1.deptid=t2.id and t2.deptname="人事"; SELECT t1.name,t2.deptname FROM yuangong t1 INNER JOIN dept t2 on t1.deptid=t2.id and t2.deptname="人事";

    • #多表查询第二种

      #左连接右链接,内连接,FROM后面yuangong t1是把员工表设置变量t1,用join就必须用on SELECT t1.name eName,t2.deptname dName FROM yuangong t1 LEFT JOIN dept t2 on t1.deptid=t2.id; SELECT t1.name eName,t2.deptname dName FROM yuangong t1 RIGHT JOIN dept t2 on t1.deptid=t2.id;

  • #多对多的连接

    • #两种方式

      第一种:SELECT * FROM content t3,course t1,student t2 WHERE t1.id=t3.cid and t2.id=t3.sid; 第二种:SELECT * FROM course t1 INNER JOIN student t2 INNER JOIN content t3 on t2.id=t3.sid and t1.id=t3.cid;

  例:多表链接,无限套娃,这里连了三个表,首先是两个表链接生成新表,然后再在新表的基础上查新数据

  • SELECT student.name,relationship.cno FROM student INNER JOIN relationship on student.sno=relationship.sno

    • 

  • SELECT t1.name,scoure.gradeName FROM (SELECT student.name,relationship.cno FROM student INNER JOIN relationship on student.sno=relationship.sno) t1 INNER JOIN scoure on t1.cno=scoure.cno;

    •