MySQL基本操作命令汇总–笔记

单表查询

//建立表student
create table student(
  id int not null auto_increment,
  name varchar(20) not null,
  grade float,
  primary key(id)
);
//插入数据
insert into student (name,grade) values
("howie1",40),
("howie1",50),
("howie2",50),
("howie3",60),
("howie4",70),
("howie5",80),
("howie6",null);
//查询全部
select * from student;
//查询某个字段
select name from student;
//条件查询,查询id=2学生的信息
select * from student where id=2;
//in关键字查询,也可以使用not in
select * from student where id IN(1,2,3);
//between and关键字查询
select * from student where id between 2 and 5;
//空值(NULL)查询，使用IS NULL来判断
select * from student where grade is null;
//distinct关键字查询
select distinct name from student;
//like关键字查询,查询以h开头，e结尾的数据
select * from student where name like "h%e";
//and关键字多条件查询,or关键字的使用也是类似
select * from student where id>5 and grade>60;

高级查询

//聚合函数
//count()函数,sum()函数,avg()函数,max()函数,min()函数
select count(*) from student;
select sum(grade) from student;
select avg(grade) from student;
select max(grade) from student;
select min(grade) from student;
//对查询结果进行排序
select * from student order by grade;
//分组查询
//1.单独使用group by分组
select * from student group by grade;
//2.和聚合函数一起使用
select count(*),grade from student group by grade;
//3.和having关键字一起使用
select sum(grade),name from student group by grade having sum(grade) >100;
//使用limit限制查询结果的数量
select * from student limit 5;
select * from student limit 2,2;
select * from student order by grade desc limit 2,2;
//函数,mysql提供了许多函数
select concat(id,':',name,':',grade) from student;
//为表取别名
select * from student as stu where stu.name="howie";
//为字段取别名,as关键字也可以不写
select name as stu_name,grade stu_grade from student;

外键

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

//创建表class,student
create table class(
   id int not null primary key,
   classname varchar(20) not null
)ENGINE=InnoDB;
create table student(
   stu_id int not null primary key,
   stu_name varchar(20) not null,
   cid int not null      -- 表示班级id，它就是class表的外键
)ENGINE=InnoDB;
//添加外键约束
alter table student add constraint FK_ID foreign key(cid) references class(id);
//删除外键约束
alter table student drop foreign key FK_ID;

一对多

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