MySQL基础知识及思维导图（二）

DQL数据查询语言

通配符查询

  select * from 表名
  select * from student 

指定字段名查询

  select 字段名1，字段名2 from 表名
  select sid,sname from student;

distinct去重查询

  select distinct 字段名 from表名
  select distinct sid from student;

设置别名

  select 字段名 from 表名 as 表的别名
  select race from student as s;
  select 字段名 as 字段名别名 from 表名
  select height as h from student;

limit限制查询

  #指定初始位置：limit 初始位置，记录数  第一条记录的初始位置为0
  select * from studetn  limit 1,3 第一个数字表示开始的下标值，后面的数字表示返回的记录条数
  #不指定初始位置：limit 记录数 select * from student limit 10；
  select * from student limit 5; 只有一个数字时表示从0开始返回的记录数

order by排序

  #asc:升序  ，desc：降序 当排序的字段中存在null值，该null值作为最小值来对待
  #单字段排序： 
  select * from student order by sex desc;
  select * from student odery by height asc;
  #多字段排序： 
  select * from student order by firstday asc, heiht desc;
  select *  from student odery by race asc,sex desc;

where条件查询
比较运算符：= != > < >= <= in not in ,between, and ， or is is not, like % _ %可以代表任意长度，不能匹配null ，_ 代表单个字符

  select * from student where height=172;
  select * from student where remarks is null;
  select * from student where race='男', and height = 180;
  select * from student where sname like '张%'；
  select * from student where sname like '_平'

group by 分组
单独使用 GROUP BY 关键字时，查询结果会只显示每个分组的第一条记录

  select * from student group by sname;
  select sex from student group by sex;

GROUP BY 关键字结合聚合函数 COUNT(),SUM(),AVG()m,MAX() 和 MIN()一起使用，可以进行分组统计

  select cid,count(1) from student group by cid and cid=37;
  select sex ,max(height),min(height),avg(height) from student group by sex;
  select cid ,count(1) from student where height > 170 group by cid ; 
  #统计每个班级中，有多少人的身高超过了170
  select cid,sex , count(1) from student group by cid,sex;
   #统计不同班级，不同性别的学生各有多少人

使用having进行数据筛选

  select cid, count(1) from student having count(1)< 5; 
  #统计哪几个班级人数小于5

表交叉连接
SELECT <字段1>,…<字段n> FROM <表1>, <表2> [WHERE子句]

  select * from student,class where student.cid=class.cid;
  select s.sname,c.cname from student as s ,class as c where s.sid=c.cid;

表内连接
join on

  select t.tname,r.rname from teacher as t 
  join  role as r  on t.rid=r.rid where  t.tname = '丁华';
  
  select s,sname,cs.csname from score as sc 
  join student as s on sc.sid=s.sid join course as cs on sc.csid = cs.csid where s.sname='张三'；

表外连接
left join on left join 前面的表称为左表，后面的表称为右表 以左表为基准，显示左表所有记录， 右表的数据，能够关联的则显示，无关联的则显示null

  select c.cid,c.cname,s.sid from class as c left join student as s on c.cid=s.cid where s.sid is nul; 
  #显示哪些班级还没有录入学生记录

right join on right join 前面的表称为左表，后面的表称为右表 以右表为基准，显示右表所有记录， 左表的数据，有关联的则显示，无关联的则显示null

  select c.cid,c.cname,s.sid from student as s right join class as c on c.cid=s.cid where s.sid is null;

子查询
先执行括号中的子查询，再执行括号外的查询

where型子查询
使用= >< != 等符号的时候，子查询select的返回的列必须是一个，返回的行必须是一个；使用in not in 等符号时，子查询可以返回多行信息，但列仍然只能返回一个

  select * from studnt where cid=(select cid from class where cname='班级名称（不重复）')
  select * from student where cid in (select * from class where cname like '%测试%')；

from型子查询

 select * from (select * from student where race='男') as stu;

exists | not exists 是否存在
只要子查询返回有效记录就执行外面的查询 子查询不成立就不执行外面的查询

  select * from student  as s where exists(select distinct sid from score as c) 
  #子查询的语句与外面的查询语句没有关联
  select * from student as s where exists(select distinct sid from score as c where s.sid=c.sid); 
  #查询哪些学生有考试记录
  #将子查询的语句与外面的查询语句关联起来