MySQL之select查詢、function函數

標籤：distinct   預設   多列   name   串連   class   where   blog   order   

一、select查詢

//查詢某張表所有資料select * from temp;//查詢指定列和條件的資料//查詢name和age這兩列，age等於22的資料select name,age from temp where age = 22;//as對列重新命名//as可以省略不寫，如果重新命名的列名出現特殊字元，如單引號，那就需要用雙引號引在外面select name as ‘名稱‘ from temp;//給table去別名select t.name Name from temp as t;//where條件查詢>、>=、<、<=、=、<>都可以出現在where語句中select from t where a > 2 or a>=3 or a<5 or a<=6 or a=7 or a<>0;//and 並且//查詢名稱等於Jack並且年齡大於20的select * from temp where age > 20 and name = ‘jack‘;//or或者--滿足一個條件即可select * from temp where name = ‘jack‘ or name = ‘jackson‘;//between v and v2--大於等於v且小於等於v2select * from temp where age between 20 and 25;//in 查詢--可以多個條件，類似於or--查詢id 在括弧中出現的資料select *from temp where id in (1, 2, 3);//like模糊查詢--查詢name以j開頭的select * from temp where name like ‘j%‘;--查詢name包含k的select * from temp where name like ‘%k%‘;--escape轉義,指定\為逸出字元，上面的就可以查詢name中包含“_”的資料select * from temp where name like ‘\_%‘ escape ‘\‘;//is null、is not null--查詢為null的資料select * from temp where name is null;--查詢不為null 的資料select * from temp where name is not null;//order by--排序，升序(desc)、降序(asc)--預設升序select * from temp order by id;select * from temp order by id asc;--多列組合select * from temp order by id, age;//notselect * from temp where not (age > 20);select * from temp where id not in(1, 2);//distinct去掉重複資料select distinct id from temp;//多列將是組合的重複資料select distinct id, age from temp;//查詢常量select 5+2;select concat(‘a‘, ‘bbb‘);//concat函數，字串串連//concat和null進行串連，會導致串連後的資料成為nullselect concat(name, ‘-eco‘) from temp;//對查詢的資料進行運算操作select age +2, age / 2, age - 2, age * 2 from temp where age - 2 > 22;

 

MySQL之select查詢、function函數