【Mysql】 数据库高级查询

# 查询

# 查询所有字段
# select * from 表名
select * from students;
select * from class;


# 查询指定字段
# select 列1,列2,..... from 表名
select name,age from students;

# 使用as给字段起别名
# select 字段 as 名字 ...... from 表名
select name as 姓名,age as 年龄 from students;

# select 表名.字段, ...... from 表名
select students.name,students.age from students;

# 可以通过as给表起别名
# select 别名.字段 ...... from 表名 as 别名;
select s.name,s.age from students as s;

# 查询数据去重
select distinct gender from students;


# 条件查询
  # 比较运算符
  # >
  # 查询大于18岁的信息
  select * from students where age>18;
  # <
  # 查询小于18岁的信息
  select * from students where age<18;
  # >=
  # 查询大于18岁的信息
  select * from students where age>=18;
  # <=
  # 查询大于18岁的信息
  select * from students where age<=18;
  # =
  # 查询大于18岁的信息
  select * from students where age=18;
  # !=
  # 查询不等于18岁的信息
  select * from students where age!=18;

  # 逻辑运算符
  # and
  # 18到28之间的所有学生信息
  select * from students where age>18 and age<28; 
  # 查询18岁以上的女性
  select * from students where age>18 and gender="女";
  select * from students where age>18 and gender=2;
  # or
  # 18岁以上或者身高高过180以上
  select * from students where age>18 or height>180;
  # not
  # 不在18岁以上的女性这个范围内的信息
  select * from students where not (age>18 and age<28); 
  # 年龄不是小于或者等于18 ，并且是女性
  select * from students where not age<=18 and gender=2;

  # 模糊查询
  # 查询姓名为小的数据信息
  select name from students where name="小";
  # like 效率较低
  # % 替换0个或者多个
  # 查询姓名中以"小"开始所有的名字
  select name from students where name like "小%"; 
  # _ 替换一个
  # 查询姓名中以"小"开始的两个字的名字
  select name from students where name like "小_";
  # 查询姓名中包含有小的所有名字
  select name from students where name like "%小%";
  # 查询有两个字的名字
  select name from students where name like "__";
  # 查询有三个字的名字
  select name from students where name like "___";
  # 查询至少有两个字的名字
  select name from students where name like "__%";
  # rlike 正则
  # 查询以周开始的姓名
  select name from students where name rlike "^周.*";
  # 查询以周开始以伦结束的姓名
  select name from students where name rlike "^周.*伦$";

 
  # 范围查询
  # in {1,2,8} 表示在一个非连续的范围内
  # 查询18,34的姓名
  select name from students where age=18 or age=34;
  select name from students where age in {18,34,45};
  # not in 不在连续的范围之内
  # 年龄不在18,34岁之间的信息
  select name from students where age not in {18,34};
   
  # between...and... 表示在一个连续的范围之内
  select name,age from students where age between 18 and 34;
  # 查询 年龄不在18到34之间的信息
  select name from students where age not between 18 and 34;

 # 空判断
 # 判空 is null
 # 查询身高为空的信息
 select * from students where heights is null;
 select * from students where heights is Null;
 select * from students where heights is NULL;
 # 判非空is not null
 select * from students where heights is not null;