2022-09-06 1、為某個欄位設置別名(as關鍵字) 以“students”為例: students表的欄位有:id,name,age,gender,is_del select name as n,age as a from students; 說明:select 屬性名 as 新名稱,屬性名 ...
2022-09-06
1、為某個欄位設置別名(as關鍵字)
以“students”為例:
students表的欄位有:id,name,age,gender,is_del
select name as n,age as a from students;
說明:select 屬性名 as 新名稱,屬性名2 as 新名稱 from 表名;
2、給某個欄位去除重覆的值,使用(distinct關鍵字)
以“students”為例:
select distinct gender from students;
說明:格式:select distinct 欄位名 from 表名;
3、查詢某個欄位不等於某個值
有兩種方式,使用where關鍵字
以“students”為例,查詢姓名不為“Tom”的同學:
(1)方式一
select * from students where name != 'Tom';
(2)方式二
select * from students where name<>'Tom';
4、條件查詢1(邏輯運算符and )
以“students”為例:
查詢年齡大於等於18而且性別為girl的同學:
select * from students where age>=18 and gender='girl';
說明:select * from 表名 where 欄位約束條件1 and 欄位約束條件2;
5、條件查詢2(邏輯運算符or)
以“students”為例:
查詢id大於3或者性別為boy的同學:
select * from students where id > 3 or gender = 'boy';
6、查詢同學中以某個姓開頭的語句
select * from 表名 where name like '某姓%';
7、查詢某個欄位中某個連續範圍的值(between...and...)
以“students”為例:
select * from students where id between 2 and 4;
8、查詢某個欄位中多個屬性值(使用in關鍵字)
以“students”為例:
select * from students where name in ('Tom','Amy');
說明:格式 : select * from 表名 where 欄位名 in (屬性值1,屬性值2);
9、查詢某個欄位中為null的值(使用 is null)
註意,此處不能使用 “= null”,如果使用了結果會顯示查詢為空
以“students”為例:
select * from students where gender is null;
10、按某個欄位名排序查詢(order by)
desc: 降序排序,從大到小
asc: 升序排序,從小到大,預設情況下為升序。
以“students”為例:
select * from students where age > 18 and gender = 'boy' order by id desc;
說明:select * from 表名 where 約束條件 order by 欄位名 desc/asc;
(2)多次排序
以年齡降序排序,當年齡相同時,以編碼升序排序
select * from students order by age desc,id asc;
11、當數據量多,一頁顯示不了時,使用分頁查詢(limit)
以“students”為例:
select * from students where gender = 'boy' limit 0,3;
說明:格式:select * from 表名 where 約束條件 limit start,count;
其中start指的是索引行,預設以0開始;count指的是每一頁最多顯示的條數。