精确查询和模糊查询

 # 创建数据库

create database day26db default charset utf8 collate utf8_general_ci;

# 创建表字段

create table depart(
　　id int not null auto_increment primary key,
    title varchar(16) not null
)default charset=utf8;
create table info(
　　id int not null auto_increment primary key,
    name varchar(16) not null,
    email varchar(32) not null,
    age int,
    depart_id int
)default charset=utf8;

# 添加数据

insert into depart(title) values("开发")，（"运营"）,("销售");
insert into info(name,email,age,depart_id) values("武沛齐","wupeiqi@live.com",19,1);
insert into info(name,email,age,depart_id) values("于超","pyyu@live.com",49,1);
insert into info(name,email,age,depart_id) values("alex","alex@live.com",9,2);
insert into info(name,email,age,depart_id) values("tony","tony@live.com",29,1);
insert into info(name,email,age,depart_id) values("kelly","kelly@live.com",99,3);
insert into info(name,email,age,depart_id) values("james","james@live.com",49,1);
insert into info(name,email,age,depart_id) values("李杰","lijie@live.com",49,1);

1.1 条件

根据条件搜索结果。

elect * from info where age > 30; 
select * from info where id > 1;
select * from info where id = 1;
select * from info where id >= 1;
select * from info where id != 1;
select * from info where id between 2 and 4; # id大于等于2且小于等于4
select * from info where name = '武沛齐' and age = 19; # where后面两个条件同时满足
select * from info where name = 'alex' or age = 49;# where后面两个条件满足一个
select * from info where (name='李杰' or email="pyyu@live.com") and age=49;#先行括号内的查询
select * from info where id in (1,4,6); # 查询id为1,4,6的数据
select * from info where id not in (1,4,6); # 查询id不为1,4,6的数据
select * from info where id in (select id from depart);#先查询括号内的数据，得到的结果在进行查询

• exists:查询数据是否存在

  # 先查询exists后的数据，如果存在就查询where前的数据，如果不存在就显示没有数据
  select * from info where exists (select * from depart where id=5);
  # 查询not exists后的数据，如果不存在就查询where前的数据，如果存在就显示没有数据
  select * from info where not exists (select * from depart where id=5);
  # 先查询括号内的数据，取个别名，然后根据结果查询后面的数据
  select * from (select * from info where id>2) as T where age > 10;

1.2 通配符

一般用于模糊搜索。

• like：模糊查询

  select * from info where name like "%沛%": # "%"表示任意字符
  elect * from info where email like "_upeiqi@live.com": # "_"表示任意一个字符

1.3 映射

想要获取的列。

select id,name from info;　　#  查询info表中的id和name字段

select id,name as na from info;　　# 查询info表中的id和name字段，name字段取别名

select id,name as na , 123 as uid from info;　　# 123 as uid：表示查询数据显示时新增一列，名称为uid，值全部都是123
# 查询需要显示的字段，在需要显示的字段中可以再进行查询得到的结果按照字段的形式显示出来
select 
　　id,
　　name,　　
　　666 as num,
　　( select max(id) from depart ) as mid, -- max/min/sum
　　( select min(id) from depart) as nid, -- max/min/sum
　　age
from info;

示例1：

# 查询depart表中的id等于info表中的id，然后在显示出来
select 
	id,
	name,
	( select title from depart where depart.id=info.depart_id) as x1
from info;

示例2：

select 
	id,
	name,
	( select title from depart where depart.id=info.depart_id) as x1,
	( select title from depart where depart.id=info.id) as x2
from info;

case 用法

语句：case 字段 when 值 then 显示值 end 字段别名

示例1：

select
    id,
    name,
    case depart_id when 1 then "第1部门" end v1　　# 如果字段depart_id 的值为1，显示为"第1部门" ，显示字段名字为v1
from info；

示例2：

select 
    id,
    name,
    case depart_id when 1 then "第1部门" else "其他" end v2    # 如果depart_id字段值为1，则显示为 "第1部门" ,否则显示为"其他",显示字段名称为v2
from info；

示例3：

select 
    id,
    name,
    case depart_id when 1 then "第1部门" end v1,
    case depart_id when 1 then "第1部门" else "其他" end v2,
    case depart_id when 1 then "第1部门" when 2 then "第2部门" else "其他" end v3,
    case when age<18 then "少年" end v4,
    case when age<18 then "少年" else "油腻男" end v5,
    case when age<18 then "少年" when age<30 then "青年" else "油腻男" end v6
from info;