单表查询：如何从数据库中获取你需要的数据
多表查询：实际开发中，需要进行2张表以上进行操作

2-1-1.单表查询

  1. //建立表student
  2. create table student(
  3.   id int not null auto_increment,
  4.   name varchar(20) not null,
  5.   grade float,
  6.   primary key(id)
  7. );
  8. //插入数据
  9. insert into student (name,grade) values
  10. ("howie1",40),
  11. ("howie1",50),
  12. ("howie2",50),
  13. ("howie3",60),
  14. ("howie4",70),
  15. ("howie5",80),
  16. ("howie6",null);
  17. //查询全部
  18. select * from student;
  19. //查询某个字段
  20. select name from student;
  21. //条件查询,查询id=2学生的信息
  22. select * from student where id=2;
  23. //in关键字查询,也可以使用not in
  24. select * from student where id IN(1,2,3);
  25. //between and关键字查询
  26. select * from student where id between 2 and 5;
  27. //空值(NULL)查询，使用IS NULL来判断
  28. select * from student where grade is null;
  29. //distinct关键字查询
  30. select distinct name from student;
  31. //like关键字查询,查询以h开头，e结尾的数据
  32. select * from student where name like "h%e";
  33. //and关键字多条件查询,or关键字的使用也是类似
  34. select * from student where id>5 and grade>60;

2-1-2.高级查询

  1. //聚合函数
  2. //count()函数,sum()函数,avg()函数,max()函数,min()函数
  3. select count(*) from student;
  4. select sum(grade) from student;
  5. select avg(grade) from student;
  6. select max(grade) from student;
  7. select min(grade) from student;
  8. //对查询结果进行排序
  9. select * from student order by grade;
  10. //分组查询
  11. //1.单独使用group by分组
  12. select * from student group by grade;
  13. //2.和聚合函数一起使用
  14. select count(*),grade from student group by grade;
  15. //3.和having关键字一起使用
  16. select sum(grade),name from student group by grade having sum(grade) >100;
  17. //使用limit限制查询结果的数量
  18. select * from student limit 5;
  19. select * from student limit 2,2;
  20. select * from student order by grade desc limit 2,2;
  21. //函数,mysql提供了许多函数
  22. select concat(id,':',name,':',grade) from student;
  23. //为表取别名
  24. select * from student as stu where stu.name="howie";
  25. //为字段取别名,as关键字也可以不写
  26. select name as stu_name,grade stu_grade from student;

2-2.多表操作

1.了解外键
2.了解关联关系
3.了解各种连接查询多表的数据
4.了解子查询，会使用各种关键字以及比较运算符查询多表中的数据

2-2-1.外键

外键是指引用另一个表中的一列或者多列，被引用的列应该具有主键约束或者唯一性约束，用于建立和加强两个数据表之间的连接。

  1. //创建表class,student
  2. create table class(
  3.    id int not null primary key,
  4.    classname varchar(20) not null
  5. )ENGINE=InnoDB;
  6. create table student(
  7.    stu_id int not null primary key,
  8.    stu_name varchar(20) not null,
  9.    cid int not null      -- 表示班级id，它就是class表的外键
  10. )ENGINE=InnoDB;
  11. //添加外键约束
  12. alter table student add constraint FK_ID foreign key(cid) references class(id);
  13. //删除外键约束
  14. alter table student drop foreign key FK_ID;

2-2-2.操作关联表

  1. //数据表有三种关联关系，多对一、多对多、一对一
  2. //学生(student)和班级(class)是多对一关系，添加数据
  3. //首选添加外键约束
  4. alter table student add constraint FK_ID foreign key(cid) references class(id);
  5. //添加数据,这两个表便有了关联若插入中文在终端显示空白，可设置set names 'gbk';
  6. insert into class values(1,"软件一班"),(2,"软件二班");
  7. insert into student values(1,"howie",1),(2,"howie1",2),(3,"howie2",1),(4,"howie3",2);
  8. //交叉连接
  9. select * from student cross join class;
  10. //内连接，该功能也可以使用where语句实现
  11. select student.stu_name,class.classname from student join class on class.id=student.cid;
  12. //外连接
  13. //首先在student,class表中插入数据
  14. insert into class values(3,"软件三班");
  15. //左连接，右连接
  16. select s.stu_id,s.stu_name,c.classname from student s left join class c on c.id=s.cid;
  17. select s.stu_id,s.stu_name,c.classname from student s right join class c on c.id=s.cid;
  18. //复合条件连接查询就是添加过滤条件
  19. //子查询
  20. //in关键字子查询跟上面的in关键字查询类似
  21. select * from student where cid in(select id from class where id=2);
  22. //exists关键字查询,相当于测试，不产生数据，只返回true或者false，只有返回true，外层才会执行，具体看下图
  23. select * from student where exists(select id from class where id=12);   -- 外层不会执行
  24. select * from student where exists(select id from class where id=1);    -- 外层会执行
  25. //any关键字查询
  26.  select * from student where cid>any(select id from class);
  27. //all关键字查询
  28.  select * from student where cid=any(select id from class);