MySQL 查询语句篇

目录

• select查询全表数据
• where的简单应用
• in和not in的用法
• between...and的用法
• like和%的语法
• case when的用法

 

1.1、select查询全表数据

select * from student;

//这里我创建了一个表插入了一点数据，通过select语法可以查看所有的数据

 

2.1、where的简单应用

create table student
(
    sno int auto_increment primary key,
    sname char(10) not null,
    score varchar(10)
);

insert into student(sname,score)
value("张三","22"),
("李四","33"),
("王五","44"),
("老六","99.2"),
("小七","87.3"),
("帅十","77.3");

select * from student where id  < 5;

//这里查询了id小于5的学生信息

select * from student where id = 5;

//这里查询了id为5的学生信息

select * from student where sname = "帅十";

//这里查询了名字为帅十的学生信息

select sname , score from student;

//这里只显示名字和成绩的列

 

3.1、in和not in的简单用法

create table student
(
    sno int auto_increment primary key,
    sname char(10) not null,
    score varchar(10)
);

insert into student(sname,score)
value("张三","22"),
("李四","33"),
("王五","44"),
("老六","99.2"),
("小七","87.3"),
("帅十","77.3");


select * from student where sname in("李四","王五");

//这里我么筛选出名字为"李四","王五"的信息

select * from student where sname not in("李四","王五");

//not in则筛选出除了这两个人的其他人的个人信息

select * from student where sno in(select sno from student where id < 5);

//这里同样in里面可以在接select查询语句，但是必须查出来的数据必须是外面sno能够匹配的，如果子查询语句查询出来的是全部信息那么就无法匹配，必然错误。这里我们在in里面插入的子查询语句选出了id小于5的sno对外sno进行匹配，实际工作不可能这么写，这里我只是举例。

 

4.1、between...and的用法

create table student
(
    sno int auto_increment primary key,
    sname char(10) not null,
    score int
);

insert into student(sname,score)
value("张三","22"),
("李四","33"),
("王五","44"),
("老六","99"),
("小七","87"),
("帅十","77");

select * from student where score between 20 and 50;

//查询成绩在20到50之间的成绩的学生信息

 

5.1、like和%的用法

create table student
(
     id int primary key,
     name char(10) nut null,
     sex char(5) default "女",
    city char(10) not null,
    papers char(10)
);

insert  into student(id,name,city,papers)
value(1001,"王宁","广州","station"),
(1002,"李思","成都","admint"),
(1003,"宋璐","上海","buinne"),
(1004,"郭科","杭州","counnt");

select * from student where name like'王%'；

//查询名字开头带王的学生信息

select * from student where name like'%王%';

//查询名字中间带王的学生信息

select * from student where name like'%王';

//查询名字结尾带王的学生信息

select * from student where papers like binary'%in%';

//查询证件papers中带有in学生信息，这里加入binary说明区分大小写

select * from student where city not like'%海';

//这里查询城市中不以海结尾的学生的基本信息

 

6.1、case...when的用法

create table student
(
     id int primary key,
     name char(10) nut null,
     sex char(5) default "女",
    city char(10) not null,
    papers char(10)
);

insert  into student(id,name,city,papers)
value(1001,"王宁","广州","station"),
(1002,"李思","成都","admint"),
(1003,"宋璐","上海","buinne"),
(1004,"郭科","杭州","counnt");

select sno,case city 
when "广州" then "广东"
when "成都" then "四川"
when "上海" then "上海"
when "杭州" then "浙江"
else "其他" end as "city"
from student;

//这里的case when把所有城市重命名为省会，进行显示。一个一个进行匹配，匹配不上的自动被列到其他这个项里。