mysql 學習總結

標籤：而不是   服務   where   max   決定   int   condition   時間間隔   timestamp   

MYSQL的增、刪、查、改   註冊、授權#建立一個對資料庫中的表有一些操作許可權的使用者，其中OPERATION可以用all privileges替換，DBNAME、TABLENAME可以用*替換，表示全部mysql> grant OPERATION on DBNAME.TABLENAME to ‘USERNAME‘@‘IP_ADDR‘ [identified by ‘PASSWD‘];mysql> create user ‘USERNAME‘@‘IP_ADDR‘ identified by ‘PASSWD‘;mysql> revoke OPERATION on DBNAME.TABLENAME from USERNAME;     #收回使用者的某些許可權mysql> show grants for ‘USERNAME‘[@‘IP_ADDR‘];     #查詢使用者權限$ mysqladmin -u USERNAME -p [OLD_PWD] password NEW_PWD     #更新密碼（註：如果原來沒有密碼OLD_PWD就不要寫）   mysql欄位的資料類型int[(M)]      #整型double[(M,D)]     #雙精確度浮點型date     #日期類型 格式YYYY-MM-DD 範圍1000-01-01——》9999-12-31char(M)     #字元類型blob text    #定長字串（會用空格填滿）varchar     #變長字串類型timestamp     #時間戳記  mysql資料類型相關函數INET_ATON(expr)     #將一個表示ip地址的字串轉換為整數INET_NTOA(expr)     #將一個整數轉換為表示ip地址的字串NOW()     #擷取目前時間戳TO_DAYS(timestamp)     #一年中的哪一天DAYOFWEEK(expr)     #一周中的哪一天，周日開始，從1計數WEEKDAY(expr)     #一周中的哪一天，周一開始，從0計數DAYOFMONTH(expr)     #一月中的哪一天，從1計數DAYOFYEAR(expr)     #一年中的哪一天，從1計數MONTH(expr)          #月份1-12DAYNAME(expr)          #星期的名字MONTHNAME(expr)       #月份的名字QUARTER(expr)     #一年中季度1-4WEEK(expr)          #一年中的周數0-52YEAR(expr)     #年份HOUR(expr)     #小時0-23MINUTE(expr)     #分鐘0-59SECOND(expr)          #秒0-59PERIOD_ADD(expr1, expr2)     #增加N個月，expr1為日期文字，expr2為增加的時間（與expr1最小單位一樣）PERIOD_DIFF(expr1, expr2)     #兩個最小單位相同的日期文字比較DATE_ADD(expr1, INTERVAL expr2 date_type)     #增加以date_type為單位的expr2個時間間隔DATE_SUB(expr1, INTERVAL expr2 date_type)     #減少以date_type為單位的expr2個時間間隔 DATA_FROMAT(expr1， format)     #將expr1表示的時間以format格式輸出，其中format的相關字元含義如下：%W 星期名字(Sunday……Saturday)%D 有英語首碼的月份的日期(1st, 2nd, 3rd, 等等。）%Y 年, 數字, 4 位%y 年, 數字, 2 位%a 縮寫的星期名字(Sun……Sat)%d 月份中的天數, 數字(00……31)%e 月份中的天數, 數字(0……31)%m 月, 數字(01……12)%c 月, 數字(1……12)%b 縮寫的月份名字(Jan……Dec)%j 一年中的天數(001……366)%H 小時(00……23)%k 小時(0……23)%h 小時(01……12)%I 小時(01……12)%l 小時(1……12)%i 分鐘, 數字(00……59)%r 時間,12 小時(hh:mm:ss [AP]M)%T 時間,24 小時(hh:mm:ss)%S 秒(00……59)%s 秒(00……59)%p AM或PM%w 一個星期中的天數(0=Sunday ……6=Saturday ）%U 星期(0……52), 這裡星期天是星期的第一天%u 星期(0……52), 這裡星期一是星期的第一天%% 一個文字“%”。   資料庫的增、刪、查mysql> create database DBNAME;     #建立資料庫mysql> use DBNAME;     #串連資料庫mysql> select database();     #查看當前串連的資料庫mysql> show databases;     #查看所有的資料庫mysql> drop database DBNAME;     #刪除資料庫   表的增、刪、查、改mysql> drop table if exists TABLENAME; create table if not exists TABLENAME (KEY1 varchar(128) TYPENAME1, KEY2 TYPENAME2, ....); #建表mysql> show tables;     #查看當前串連資料庫中所有的表名mysql> desc TABLENAME;     #查看錶結構mysql> rename table TABLENAME_OLD to TABLENAME_NEW;     #表的重新命名mysql> drop table TABLENAME;     #刪除表mysql> alter table TABLENAME add KEY TYPENAME;     #表中增加一列mysql> alter table TABLENAME modify column KEY1 TYPENAME1 [（befor | after） KEY2] ;     #修改欄位類型mysql> alter table TABLENAME drop KEY;     #刪除表的一個欄位mysql> insert into TABLENAME [(KEY1, KEY2, ....)] values(VALUE1, VALUE2, ....);     #表中插入資料mysql> select KEY1, KEY2, ... from TABLENAME where EXPRESSION;     #查詢表中資料mysql> delete from TABLENAME where EXPRESSION;     #刪除表中資料mysql> update TABLENAME set KEY1=VALUE1, KEY2=VALUE2, .... where EXPRESSION;     #更新表中資料註：由於mysql中不支援datetime欄位設定預設值，所以只能採用timestamp，但是timestamp只能到2038年  表查詢的進階用法 謂詞：ALL(合格全部)、DISTINCT(相同欄位資料只返回一條)、DISTINCTROW(相同記錄只返回一條)、TOP(頭尾的若干記錄，當使用百分比的時候，為TOP N PERCENT，N為數字)、AS(為結果欄位取別名，AS前面是原名、後面為別名)  比較符：= #等於      > #大於      < #小於      >= #大於等於<= #小於等於     <> #不等於      !> #不大於      !< #不小於     NOT #用於比較運算式前表示相反  模式比對（必須在LIKE之後）:%     #替代一個或者多個字元-     #僅替代一個字元[charlist]     #字元列中任何單一字元[^charlist]     #不在字元列中的任何單一字元 [NOT] BETWEEN ... AND .. #指定要搜尋的閉區間[之外]的範圍[NOT] IN     #用於[不]匹配列表中的任何一個值ORDER BY KEY1 (ASC|DESC), KEY2 (ASC|DESC) ...    #將結果以KEY的（升/降）序排列，預設是ASC... AND ...     #同時滿足兩個條件GROUP BY KEY1, KEY2... [HAVING CONDITION]     #以KEY1、KEY2等進行分組，HAVING 調用一些聚集合函式來過濾分組的查詢結果  聚集合函式：SUM(KEY)     #求和AVG(KEY)     #求均值COUNT(KEY)     #計數COUNT(*)     #所有記錄計數MAX(KEY)          #最大值MIN(KEY)          #最小值VAR(KEY)          #方差STDEV(KEY)     #標準差FIRST(KEY)     #第一個LAST(KEY)     #最後一個CONCAT(KEY1, KEY2, ...)     #將keys串連起來成為字串 #將查詢結果寫入到另一個表TABLENAME1中mysql> select KEY1, KEY2, ... into TABLENAME1 from TABLENAME2 where EXPRESSION; #將從TABLENAME1、TABLENAME2兩個表中的查詢資料合併展示mysql> select KEY1, KEY2, ... from TABLENAME1 where EXPRESSION1 union select KEY3, KEY4, .. from TABLENAME2 where EXPRESSION2; #TABLENAME1表中key3大於子查詢結果的記錄mysql> select KEY1, KEY2, ... from TABLENAME1 where KEY3 (>|=|<|<>) (ANY|ALL|SOME) (select KEY3 from TABLENAME2 where EXPRESSION); #TABLENAME1表中key3[不]屬於子查詢結果清單中的記錄mysql> select KEY1, KEY2, ... from TABLENAME1 where KEY3 [NOT] IN (select KEY3 from TABLENAME2 where EXPRESSION); #根據子查詢的結果來決定是否執行從TABLENAME1表中進行查詢mysql> select KEY1, KEY2, ... from TABLENAME1 where exists (select KEY3 from TABLENAME2 where EXPRESSION);   查詢（刪除）5min之前的內容select * from email_info where minute(now() - c_time) > 5;  該方法如果跨整點就會有問題select * from email_info where timestampdiff(minute, c_time, now()) > 5;delete from email_info where  timestampdiff(minute, c_time, now()) > 5;註：minute是將時間轉換成分鐘的函數，類似的還有year、dayofyear、month、monthname、dayofmonth、week、weekday、dayname、hour、minute、second等  索引、視圖、觸發器、預存程序、遊標、事務  視圖是虛擬表，相當於一個sql語句的別名，特定情況下可以對視圖進行增、刪、改操作，前提是：沒有group by分組、沒有union串連、沒有子查詢、沒有並、沒有聚集合函式、沒有DISTINCT、匯出（計算）列 索引、視圖的增、刪、改mysql> create index INDEX_NAME on TABLENAME(KEY);     #建立索引mysql> show index from TABLENAME;     #查詢索引mysql> drop index INDEX_NAME;     #刪除索引 mysql> create view VIEW_NAME(KEY1, KEY2, ...) as select KEY3, KEY4 from TABLENAME;     #建立視圖mysql> drop view VIEW_NAME;     #刪除視圖  預存程序簡單來說，就是為以後的使用而儲存的一條或多條MySQL語句的集合。可將其視為批檔案，雖然它們的作用不僅限於批處理。預存程序3個主要的好處：簡單、安全、高效能。 預存程序的建立、刪除、調用#建立一個預存程序PROC_NAME， 其中DELIMITER//告訴命令列公用程式使用//作為新的語句結束符，可以看到表示預存程序結束的END定義END//而不是END;作為語句結束。最後使用DELIMITER; 恢複原來的語句結束符，因為mysql中;預設為結束符，為了預存程序正常使用，所以需要替換procedure中的預存程序。#參數中的IN/OUT表示參數是傳入的還是輸出的，參數是無類型的，可以是一個簡單變數，也可以是一個表名（此時可以直接select @arg來查詢結果），通常預存程序使用select ... into ... 語句將結果儲存到輸出變數中 delimiter //create procedure PROC_NAME([IN/OUT] ARG1 TYPENAME, [IN/OUT]ARG2 TYPENAME, ...)BEGIN     SQL_SENTENCES;END//delimiter ; mysql> call PROC_NAME(@arg1, @arg2, ...);     #調用預存程序，mysql中變數前面需要加@mysql> drop procedure PROC_NAME if exists;     #刪除預存程序mysql> show create procedure PROC_NAME;          #查看建立預存程序的sql語句mysql> show procedure status like ‘EXP‘;     #查看預存程序的相關資訊  遊標是一個儲存在MySQL伺服器上的資料庫查詢，它不是一條SELECT語句，而是被該語句檢索出來的結構集。在儲存了遊標之後，應用程式可以根據需要滾動或瀏覽或更改其中的資料。遊標使用步驟：

1. 使用前需要先聲明
2. 使用時必須開啟遊標（執行相關的sql查詢語句），遊標開啟後使用FETCH語句分別訪問每一行、FETCH指定檢索什麼資料、儲存在何地，同時會將遊標指向下一行（即下一條FETCH就會檢索下一行）
3. 根據需要行資料，使用完畢後必須關閉

declare CURSOR_NAME cursor for select KEY1, KEY2, ... from TABLENAME;     #聲明一個遊標open CURSOR_NAME;     #開啟遊標FETCH CURSOR_NAME into TABLENAME2;     #將遊標擷取的一行記錄存到資料表中。close CURSOR_NAME;      #關閉遊標  觸發器是MySQL響應INSERT、UPDATE、DELETE三個中的任意一個語句而自動執行的一條sql語句。觸發器建立條件：名稱唯一、有具體表關聯（視圖、暫存資料表不行）、與相關的操作（INSERT/UPDATE/DELETE）關聯響應、在關聯語句執行前/後執行。 觸發器的建立、刪除mysql> create trigger TRI_NAME (before/after) (INSERT/UPDATE/DELETE) on TABLENAME for each row BEGIN SQL_SENTENSE END;mysql> drop trigger TRI_NAME;   交易處理可以用來維護資料庫的完整性，它保證成批的mysql操作要麼完全執行，要麼完全不執行。管理交易處理的關鍵在於將sql語句組分解成邏輯快，並明確規定資料何時應該回退、何時不回退。 start transaction 表示事務的開始。 事務的建立start transaction;     #事務開始SQL_SENTENCE1;SQL_SENTENCE2;...(commit/rollback);     #事務提交/復原#註：通常情況下是判斷上一條sql語句的執行結果，如果執行失敗，則執行rollback進行復原操作，若事務中所有的sql語句成功執行，則執行commit將更改實際寫到資料庫中。  資料匯入、匯出   匯入txt格式的資料mysql> load data local infile ‘FILENAME.txt‘ into table TABLENAME; #註：txt中各欄位用tab分隔   匯入sql格式的資料庫 （註：這裡的sql檔案是包含建表語句和表中資料） mysql> use DBNAME; source FILENAME.sql     #方法一 $ mysqldump -u USERNAME -p DBNAME <FILENAME.sql  #方法二 $ mysql -u USERNAME -p -D DBNAME <FILENAME.sql     #方法三  匯出整個資料庫中所有的表結構$ mysqldump -uUSERNAME -p [-hIP_ADDR] DBNAME>FILENAME.sql #包含建表語句以及插入資料的insert語句$ mysqldump -uUSERNAME -p -d [-hIP_ADDR] DBNAME >FILENAME.sql #僅包含建表語句$ mysqldump -uUSERNAME -p [-hIP_ADDR] --no-create-info DBNAME>FILENAME.sql #僅包含插入資料的insert$ mysqldump -uUSERNAME -p [-hIP_ADDR] DBNAME TABLENAME >FILENAME.sql #匯出一張表的全部內容

mysql 學習總結