【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;