MYSQL常用函數以及如何操作資料

標籤：截取字串   運算式   預設   count   ant   substring   系統   insert   sql   

-- 【SQL語句的組成】

-- DML資料操作語言 （插入、修改和刪除資料庫中的資料） INSERT UPDATE DELETE

-- DQL資料查詢語言 （用來查詢資料庫中的資料） SELECT

-- DCL資料控制語言 （用來控制存取許可、存取許可權等） GRANT  REVOKE

-- DDL資料定義語言 (Data Definition Language) （用來建立資料庫、資料庫物件和定義表的列） CREATE DROP

USE mydb;

CREATE TABLE IF NOT EXISTS tb2( id INT UNSIGNED PRIMARY KEY auto_increment,

username VARCHAR(255) NOT NULL,

age INT DEFAULT 0,

sex enum("男","女") DEFAULT "男"

);

SHOW COLUMNS FROM tb2;

-- 資料插入語句  INSERT -- INSERT[INTO] 表名 （列名） VALUES （列值）[,（列值），……];

-- ①如果省略列名，則後面的列值必須為所有的列賦值，包括自增列和預設列。

-- ②如果不省略列名，則後面的列值需要與前面的列名一一對應；並且需要給所有非null列賦值 INSERT INTO tb2(username,age,sex) VALUES ("張三",12,"女"); INSERT tb2 VALUES (7,"張三",12,"女"); INSERT INTO tb2(username,age,sex) VALUES ("張三",12,"女"),("張三",12,"女"),("張三",12,"女"),("張三",12,"女");

-- 資料更新語句

-- UPDATE 表名 SET 列名=列值[,列名=列值,……] [WHERE 條件]

-- ① WHERE條件可以省略，但是表示修改表中所有的行 UPDATE tb2 SET sex="男"; UPDATE tb2 SET username="李小二",age=18,sex="女" WHERE id=8

/*[常用where條件判斷]

1、關係運算 >  <  =  !=  <>  >=  <=

2、邏輯運算  NOT AND OR

3、 ISNULL(欄位)檢測是否為空白  SELECT * FROM tb2 WHERE NOT ISNULL(age);

-- 所有年齡為空白的使用者

4、BETWEEN……AND……介於兩個值之間 SELECT * FROM tb2 WHERE age BETWEEN 10 AND 14; 

-- 相當於 age>=10 and  age<=14

5、in 在某些值之間的資料 SELECT * FROM tb2 WHERE id in(1,3,5,7,9);

6、LIKE 相似匹配

①相似匹配時，使用%表示任一字元個數（0到多個）；

 SELECT * FROM tb2 WHERE username LIKE "%張%";  -- "張%"->用"張"開頭  （"%張"->用"張"結尾   "%張%"->任意位置包含"張"）

②相似匹配時，使用_表示一個字元的個數；

SELECT * FROM tb2 WHERE username LIKE "_二%";  --  第二個子是"二"的

7、EXISTS（子查詢語句）：

如果子查詢與返回資料>=1行，則EXISTS返回true，否則返回false，

SELECT * FROM tb2 WHERE EXISTS(SELECT * FROM tb3);

-- 如果tb3返回至少一行資料，則條件成立

8、ALL（子查詢）：子查詢返回的資料，需要全部滿足，才能成立：

SELECT * FROM tb1 WHERE username !=ALL (SELECT username FROM tb1 WHERE id>7);

-- 使用者名稱 不能等於 子查詢返回使用者名稱列表中的任意一個；

9、ANY（子查詢）：子查詢返回的數組，滿足其中一個，即可成立：

SELECT * FROM tb1 WHERE username =ANY (SELECT username FROM tb1 WHERE id>7);

-- 使用者名稱 等於 子查詢返回使用者名稱列表中的任意一個； */

-- 刪除表中資料

-- DELETE [FROM] 表名 [WHERE 條件] DELETE FROM tb1 WHERE id>11;

SELECT * FROM tb2 WHERE EXISTS(SELECT * FROM tb2 WHERE username="李小二");

SELECT * FROM tb2 WHERE username LIKE "__二";

SELECT * FROM tb2 WHERE username LIKE "%張%";

SELECT * FROM tb2 WHERE id in(1,3,5,7,9);

SELECT * FROM tb2 WHERE age BETWEEN 10 AND 14;

SELECT * FROM tb2 WHERE NOT ISNULL(age);

SELECT * FROM tb2;

-- 資料查詢語句

-- SELECT 列名，[列名，……] FROM 表名 WHERE 條件 ORDER BY 排序的列名 ASC/DESC

-- ①SELECT * 表示查詢所有欄位；

-- ② ORDER BY 表示對查出的資料進行排序，必須在where後面；

--  ASC 表示升序排列  DESC 表示降序排列 SELECT * FROM tb2 ORDER BY id DESC

-- 列運算式

-- 根據已有的列，查詢出來的結果，我們使用case結構虛擬出來的列，稱為列運算式

-- AS 關鍵字

-- 用於給列名起別名，AS關鍵字還可以省略。 SELECT  username as ‘名字‘,sex ‘性別‘,age,

  -- 給username列起別名"名字"，sex起別名"性別"

( CASE -- 表示判斷的開始 WHEN sex="男"

THEN 1 -- 如果查詢出sex是男，則虛擬列運算式的值就是1

WHEN sex="女" THEN 2 ELSE 3 -- 上面所有的when都不成立時，列運算式的值為3

END -- 表示判斷的結束 )

as sexno  -- 給虛擬出的這一列，起別名叫sexno

FROM tb2;

-- DISTINCT 對查詢之後的結果去重。

* ABS(X)  返回X的絕對值

CEIL(X) 向上取整   1.5->2

FLOOR(X) 向下取整   -1.5->-2   1.5->1

ROUND(X) 四捨五入取整 ROUND(X,D)  將X保留D位小數，進行四捨五入

TRUNCATE`(X,D)  將X保留D位小數，不進行四捨五入

SIGN(X) x是負數返回-1   正數返回1，0返回0

POW(X,Y)  返回X的Y次冪 PI() 返回圓周率 */

SELECT CEIL(1.5) SELECT ABS(-1)

SELECT * FROM tb1

/* LENGTH(str):返回字串的長度。其中一個中文佔3個長度；

CHAR_LENGTH(str): 返回字串的字元數。其中一個中文只佔一個字元；

CONCAT(str1,str2,...) 拼接多個字串

UPPER(str):將字串全部轉為大寫；

LOWER(str):將字串全部轉為小寫；

LEFT(str,n): 返回字串的前n個字元；

RIGHT(str,n):返回字串的後n個字元；

TRIM(str)：去掉str左右兩邊的空格

REPLACE(str,s1,s2):  將字串str中所有的s1換成s2

SUBSTRING(str,pos,len):截取字串str，從pos個字元開始，截取len個。如果省略len，則從position開始截取到最後

INSTR(str,s1)：返回s1在str的下標

INSTR("12-23","-")-->3  下標從1開始

【字串轉數字格式】

SELECT CAST("-123" AS SIGNED);

SELECT CONVERT("123",SIGNED);

SELECT "123"+0; */

SELECT CONCAT("123","456","789");

/*[常用日期函數]

CURDATE():返回當前日期。2016-06-20

CURTIME():返回目前時間。09:52:52

NOW():返回當前的日期和時間

DATEDIFF(date1,date2):返回date1與date2之間的間隔天數

ADDDATE(dateTime1,days):返回dateTime1加上days天后的日期

YEAR(date):返回日期中的年份

MONTH(date):返回日期中的月份

DAY(date):返回日期中一個月中的第幾天

WEEK(date[,mode]):返回一個周中的第幾天  0->周一

HOUR(time):返回小時數

MINUTE(time): 返回分鐘數

SECOND(time):返回秒數

DATE_FORMAT(date1,format): 將date1使用format要求的格式顯示。

format中可用的預留位置格式：

%Y年份, 數字形式,4位元

%y年份, 數字形式 (2位元)

%M月份名稱 (January..December)

%m月份, 數字形式 (00..12)

%b月份的縮寫名稱  (Jan..Dec)

%c月份，數字形式(0..12)

%D帶有英語尾碼的該月日期  (0th, 1st, 2nd, 3rd, ...)

%d該月日期, 數字形式 (00..31)

%e該月日期, 數字形式(0..31)  

%H小時(00..23)

%h小時(01..12)

%k小時(0..23)

%l小時(1..12)  

%i分鐘,數字形式 (00..59)

%S秒 (00..59)

%s秒 (00..59)  

%W工作日名稱 (周日..周六)

%w一周中的每日 (0=周日..6=周六)

*/ SELECT CURDATE();

SELECT

/*[系統資訊函數]

VERSION(): 取到當前資料庫的版本號碼

DATABASE(): 返回當前資料庫的名字

USER():返回當前資料庫的使用者

CHARSET(str):返回字串的字元集

LAST_INSERT_ID():返回最後一個auto_increment的值

*/

/*[加密函數]

1、PASSWORD(str):對字串進行加密，常用於加密密碼；

2、MD5(str)：對字串進行MD5散列加密，常用於不需要解密的資料；

3、ENCODE(str,pwd):使用加密密碼pwd對字串str進行加密；          加密之後的結果是二進位，該欄位需要使用BLOB類型儲存。   

DECODE(mistr,pwd):將ENCODE加密的二進位進行解密，解密密碼需與ENCODE的加密密碼一致。 */

SELECT PASSWORD("lalalal");

/*[多行函數]

1、SUM():求和  SELECT SUM(age) FROM tb2;

2、AVG(): 求平均值  SELECT AVG(age) FROM tb2;

3、MIN():求最小值

4、MAX():求最大值

5、COUNT():求總個數

SELECT COUNT(username) FROM tb2;

MYSQL常用函數以及如何操作資料