MYSQL使用基礎、進階分享

MySQL是一個關系型數(shù)據(jù)庫管理系統(tǒng)，由瑞典MySQL AB公司開發(fā)，屬于Oracle旗下產(chǎn)品，是最流行的關系型數(shù)據(jù)庫管理系統(tǒng)之一。

興縣網(wǎng)站建設公司成都創(chuàng)新互聯(lián)公司,興縣網(wǎng)站設計制作，有大型網(wǎng)站制作公司豐富經(jīng)驗。已為興縣千余家提供企業(yè)網(wǎng)站建設服務。企業(yè)網(wǎng)站搭建\成都外貿(mào)網(wǎng)站建設公司要多少錢，請找那個售后服務好的興縣做網(wǎng)站的公司定做！

端口是3306。

表很多時，使用linux腳本，需要根據(jù)需要修改一下：

和創(chuàng)建一樣，可以加上 if exists

可兩篇文章：

如：

用于在已有的表中添加、刪除或修改列。

添加 ADD

或

默認是添加到最后，但可以指定位置。 FIRST ：添加最前

AFTER 字段名 ：添加指定字段之后

例子：

刪除 DROP

修改 MODIFY 主要修改原列的類型或約束條件 同樣可以用 FIRST 和 AFTER 字段名 ，代表的是修改到哪里。

修改字段名 CHANGE

可以把表2的數(shù)據(jù)復制到表1中，但 不能復制約束性條件 。

單行

多行，注意 只有一個VALUES ：

不寫 (行1, 行2...) 這一部分的話，默認一一對應

除了以上方法外，還可以用SET為每一行附上相應的值。

假如沒有篩選的話，就給全部都修改了?？梢杂? WHERE 篩選。

假如 沒有篩選的話，就給全部刪除了 。相當于清空。

清空

先把表刪除，然后再建一個。與 DELETE FROM 相比， TRUNCATE 的效率更快，因為 DELETE FROM 是把記錄逐條刪除的。

查詢執(zhí)行的順序

FROM -- WHERE -- SELECT -- GROUP BY -- HAVING -- ORDER BY -- LIMIT

注意

當數(shù)據(jù)很大，上百萬的時候，使用LIMIT ... OFFSET ..的方式進行分頁十分浪費資源且耗時長。最好是結合WHERE使用，如：

REGEXP 使用正則表達進行匹配。 查詢時，需要搭配WHERE或HAVING使用 。

兩個表之間有交集且要用到兩個表的數(shù)據(jù)時，可以使用內(nèi)連接查詢。

LEFT JOIN 關鍵字從左表（table1）返回所有的行，即使右表（table2）中沒有匹配。如果右表中沒有匹配，則結果為 NULL。

用法：

RIGHT JOIN 關鍵字從右表（table2）返回所有的行，即使左表（table1）中沒有匹配。如果左表中沒有匹配，則結果為 NULL。 把LEFT JOIN的表1、表2調換順序，就是REGHT JOIN 。

FULL OUTER JOIN 關鍵字只要左表（table1）和右表（table2）其中一個表中存在匹配，則返回行. 相當于結合了 LEFT JOIN 和 RIGHT JOIN 的結果。

但 MySQL中不支持 FULL OUTER JOIN 。

即SELECT嵌套。

IN 一個查詢結果作為另一個查詢的條件。 如：

EXISTS 用于判斷查詢子句是否有記錄，如果有一條或多條記錄存在返回 True，否則返回 False。True時執(zhí)行。 如：

索引的本質是一種排好序的數(shù)據(jù)結構。利用索引可以提高查詢速度。

常見的索引有：

MySQL通過外鍵約束來保證表與表之間的數(shù)據(jù)的完整性和準確性。 外鍵的使用條件：

外鍵的好處：可以使得兩張表關聯(lián)，保證數(shù)據(jù)的一致性和實現(xiàn)一些級聯(lián)操作。

對已有的兩個表增加外鍵 比如：主表為A，子表為B，外鍵為aid，外鍵約束名字為a_fk_b

為子表添加一個字段，當做外鍵

為子表添加外鍵約束條件

假如刪除記錄報錯： [Err] 1451 -Cannot deleteorupdatea parent row: aforeignkeyconstraintfails (...)

這是因為MySQL中設置了foreign key關聯(lián)，造成無法更新或刪除數(shù)據(jù)?？梢酝ㄟ^設置 FOREIGN_KEY_CHECKS 變量來避免這種情況。 第一步：禁用外鍵約束，我們可以使用： SETFOREIGN_KEY_CHECKS=0; 第二步：刪除數(shù)據(jù) 第三步：啟動外鍵約束，我們可以使用： SETFOREIGN_KEY_CHECKS=1; 查看當前FOREIGN_KEY_CHECKS的值，可用如下命令： SELECT @@FOREIGN_KEY_CHECKS;

使用 UNION 來組合兩個查詢，如果第一個查詢返回 M 行，第二個查詢返回 N 行，那么組合查詢的結果一般為 M+N 行。

每個查詢必須包含相同的列、表達式和聚集函數(shù)。

默認會去除相同行，如果需要 保留 相同行，使用 UNION ALL 。

只能包含一個 ORDER BY 子句，并且必須位于語句的最后 。

內(nèi)置函數(shù)很多, 見: MySQL 函數(shù)

我們一般使用 START TRANSACTION 或 BEGIN 開啟事務, COMMIT 提交事務中的命令, SAVEPOINT : 相當于設置一個還原點, ROLLBACK TO : 回滾到某個還原點下

一般的使用格式如下:

開啟事務時, 默認加鎖

根據(jù)類型可分為共享鎖（SHARED LOCK）和排他鎖（EXCLUSIVE LOCK）或者叫讀鎖（READ LOCK）和寫鎖（WRITE LOCK）。

根據(jù)粒度劃分又分表鎖和行鎖。表鎖由數(shù)據(jù)庫服務器實現(xiàn)，行鎖由存儲引擎實現(xiàn)。

除此之外,我們可以顯示加鎖

加鎖時, 如果沒有索引，會鎖表，如果加了索引，就會鎖行

InnoDB默認支持行鎖，獲取鎖是分步的，并不是一次性獲取所有的鎖，因此在鎖競爭的時候就會出現(xiàn)死鎖的情況

解決方法:

即ACID特性:

由于并發(fā)事務會引發(fā)上面這些問題, 我們可以設置事務的隔離級別解決上面的問題.

MySQL的默認隔離級別（可重復讀）

查看當前會話隔離級別

方式1

方式2

設置隔離級別

主從集群的示意圖如下:

主要涉及三個線程： binlog 線程、 I/O 線程和 SQL 線程。

同步流程:

由于MySQL主從集群只會從主節(jié)點同步到從節(jié)點, 不會反過來同步, 所以需要讀寫分離

讀寫分離需要在業(yè)務層面實現(xiàn) , 寫數(shù)據(jù)只能在主節(jié)點上完成, 而讀數(shù)據(jù)可以在主節(jié)點或從節(jié)點上完成

索引是幫助MySQL高效獲取數(shù)據(jù)的排好序的數(shù)據(jù)結構

MySQL的索引有

推薦兩個在線工具:

簡單來說, B樹是在紅黑樹(一個平衡二叉樹)的基礎上將一個節(jié)點存放多個值, 實現(xiàn)的, 降低了樹的高度, 每個節(jié)點都存放索引及對應數(shù)據(jù)指針, 同一層的節(jié)點是遞增的

而B+樹在B樹的基礎上進行優(yōu)化, 非葉子節(jié)點存放 子節(jié)點的開始的索引, 葉子節(jié)點存放索引和數(shù)據(jù)的指針, 且葉子節(jié)點之間有雙向的指針

如下示意圖:

不同的引擎, 主鍵索引存放的數(shù)據(jù)也不一樣, 比如常見的 MyISAM 和 InnoDB

MyISAM 的B+樹葉子節(jié)點存放表數(shù)據(jù)的指針, InnoDB 的B+樹葉子節(jié)點存放處主鍵外的數(shù)據(jù)

其他的:

即多個列組成一個索引, 語法:

由于聯(lián)合索引的B+樹的結構, 根據(jù)列建立, 所以我們的查找條件也要根據(jù)索引列的順序( where column1=x, column2=y,columnN... ), 否則會全表掃描

如果你對列進行了 （+，-，*，/，!） , 那么都將不會走索引。

OR 引起的索引失效

OR 導致索引是在特定情況下的，并不是所有的 OR 都是使索引失效，如果OR連接的是 同 一個字段，那么索引 不會失效 ， 反之索引失效 。

這個我相信大家都明白，模糊搜索如果你前綴也進行模糊搜索，那么不會走索引。

這兩種用法，也將使索引失效。另 IN 會走索引，但是當IN的取值范圍較大時會導致索引失效，走全表掃描, 見: MySQL中使用IN會不會走索引

不走索引。

走索引。

所以設計表的時候, 建議不可為空, 而是將默認值設置為 "" ( NOT NULL DEFAULT "" )

mysql的count()

count()是Mysql的內(nèi)置函數(shù)，用于統(tǒng)計符合條件的行數(shù)。

一般用法:select count(*) from sql（sql語言）as name（別名）

select count(*) table_name

這里對count(1)和count(*)作對比

一般情況下，count(1)和count(*)返回的數(shù)據(jù)是相同的。

而效率方面也差不多。只是在有主鍵的情況下，count(*)是最快的。

兩者都是記錄null的行數(shù)

select count(a) from test?

-- 掃描a列的數(shù)據(jù)記錄數(shù)，如果a上沒有索引，則效率最差，并且如果a列包含null，則不會計數(shù)

mysql內(nèi)置函數(shù)使用有什么作用

mysql數(shù)據(jù)庫中提供了很豐富的函數(shù)。mysql函數(shù)包括數(shù)學函數(shù)、字符串函數(shù)、日期和時間函數(shù)、條件判斷函數(shù)、系統(tǒng)信息函數(shù)、加密函數(shù)、格式化函數(shù)等。通過這些函數(shù)，可以簡化用戶的操作。

簡單介紹幾類函數(shù)的使用范圍：

數(shù)學函數(shù)：這類函數(shù)只要用于處理數(shù)字。這類函數(shù)包括絕對值函數(shù)、正弦函數(shù)、余弦函數(shù)、獲取隨機數(shù)函數(shù)等。

字符串函數(shù)：這類函數(shù)主要用于處理字符串。其中包括字符串連接函數(shù)、字符串比較函數(shù)、將字符串的字母變成小寫或大寫字母的函數(shù)、獲取子串的函數(shù)等。

日期和時間函數(shù)：這類函數(shù)主要用于處理日期和時間。其中包括取當前時間的函數(shù)、獲取當前日期的函數(shù)、返回年份的函數(shù)、返回日期的函數(shù)等。

流程函數(shù)：這類函數(shù)主要用于在SQL語句中控制條件選擇。其中包括IF語句、CASE語句、WHEN語句等。

系統(tǒng)信息函數(shù)：這類函數(shù)主要用于獲取mysql數(shù)據(jù)庫的系統(tǒng)信息。其中包括獲取數(shù)據(jù)庫名的函數(shù)、獲取當前用戶的函數(shù)、獲取數(shù)據(jù)庫版本的函數(shù)等。

加密函數(shù)：這類函數(shù)主要用于對字符串進行加密解密。其中包括字符串加密函數(shù)、字符串解密函數(shù)等。

其他函數(shù)：包括格式化函數(shù)、鎖函數(shù)等。

mysql 的內(nèi)置函數(shù)uuid 怎么用

update

test

set

id

=

UUID();MYSQL無法在默認值中設置UUID函數(shù)，實際上其它函數(shù)除TIMESTAMP之外都不可以。

MYSQL的默認值目前只能是常數(shù)或者CURRENTTIMESTAMP。

如何用MySQL內(nèi)置函數(shù)來轉換ip地址與相關數(shù)字

　轉載 以下的文章主要介紹的是如何用MySQL內(nèi)置函數(shù)來轉換ip地址與相關數(shù)字，在實際操作中在處理字符格式的時候,例如將54789轉換為5，4789這樣的,我們只要用:format(12345,0)即可,以下就是具體內(nèi)容介紹。

用MySQL內(nèi)置函數(shù)轉換ip地址和數(shù)字

利用兩個內(nèi)置函數(shù)

inet_aton:將ip地址轉換成數(shù)字型

inet_ntoa:將數(shù)字型轉換成ip地址

充分利用mysql內(nèi)置的format函數(shù)

尤其是在處理字符格式的時候,例如將12345轉換成12,345這樣的,只要用:format(12345,0)即可,如果用format(12345,2)則顯示的是12,345.00了...

利用MySQL內(nèi)置函數(shù)處理時間戳問題

eg : select FROM_UnixTIME(UNIX_TIMESTAMP(),'%Y %D %M %h:%i:%s %x');

結果: 2004 3rd August 03:35:48 2004

利用mysql_convert_table_format轉換表類型

需要DBI和DBD的mysql相關模塊支持才能用,例子:

mysql_convert_table_format --user=root --password='xx' --type=myisam test yejr

修改mysql表中的字段名

alter table tb_name change old_col new_col definition...

利用臨時變量

select @var1:=a1+a2 as a_sum,@var2:=b1+b2 as b_sum,@var1+@var2 as total_sum from test_table xxx;

用int類型存儲ip地址

原先錯誤的認為必須用bigint才夠，后來發(fā)現(xiàn)使用int unsigned類型就足夠了。 :)

利用IF函數(shù)快速修改ENUM字段值

一個例子：

update rule set enable = if('0' = enable,'1','0') where xxx;

enable 類型：enum('0','1') not null default '0'

事務無法嵌套

避免長時間的sleep連接造成的連接數(shù)超出問題

設定全局變量 wait_timeout 和 interactive_timeout 為比較小的值，例如

10(s)，就能使每個sleep連接在10s之后如果還沒有查詢的話自動斷開。

設定mysql內(nèi)置函數(shù)客戶端的提示符(prompt)

export MYSQL_PS1="(\u:\h:)\d "

則用 mysql -hlocalhost -uroot -pxx db_name 登錄后，提示符變成：

(root:localhost:)db_name

好用吧 :), 時時刻刻提醒你在哪個服務器上，盡量避免誤操作的發(fā)生

更詳細的請查看MySQL手冊

整理MyISAM碎片

1.) 定期運行 OPTIMIZE TABLE 命令即可

2.) 用mysqldump出數(shù)據(jù)，然后重新import回去，這對大表來說顯然第一種方法比較方便

整理Innodb碎片

1.) 運行NULL命令， ALTER TABLE XXX ENGINE=Innodb;

2.) 同上的mysqldump方法

MySQL如果認為檢索的記錄數(shù)量超過總記錄數(shù)的30%，則選擇全表掃描，而非使用索引

MySQL 5.0.3之后,VARCHAR字段后面的空格就不再刪除

升級到4.1.1或更高后，就很難降級回到4.0或4.1了，因為 InnoDB 使用了多個表空間的緣故

MySQL 4.1之后，MySQL把字符串類型字段的長度定義理解為字符長度而不是字節(jié)長度

MySQL 4.1=5.0時，增加了一個新的啟動選項 innodb_table_locks，它導致 LOCK TABLE 時也可以請求 InnoDB

表鎖。這個選項默認打開，不過可能在 AUTOCOMMIT=1 和 LOCK TABLES 應用中會導致死鎖

5.0.3開始，在計算 DECIMAL 值和舍入精確值的時候采用精確數(shù)學，DECIMAL 用更有效的格式來存儲

從5.0.12開始，自然連接和使用 USING

的連接，包括外部連接的衍生形式，都按照SQL:2003標準來處理了;這個變化導致減少了自然連接和使用 USING

的連接產(chǎn)生的結果字段數(shù)，并且還將按照更合理的順序顯示這些字段，逗號比較符的優(yōu)先順序和 JOIN, LEFT JOIN 中的一樣了

在以前，等待超時的鎖會導致 InnoDB 回滾當前全部事務，從5.0.13開始，就只回滾最近的SQL語句了

InnoDB 和 MyISAM 表中空格結尾的 TEXT 字段索引順序改變了。因此需要運行 "CHECK TABLE" 語句修復數(shù)據(jù)表，如果出現(xiàn)錯誤，就運行

"OPTIMIZE TABLE" 或 "REPAIR TABLE" 語句修復，甚至重新轉儲(用mysqldump)

MySQL 5.0.3到5.0.5之間版本的 MyISAM 和 InnoDB 表中創(chuàng)建的 DECIMAL 字段升級到5.0.6之后會發(fā)生崩潰。

以上的相關內(nèi)容就是對用MySQL內(nèi)置函數(shù)轉換ip地址和數(shù)字的介紹，望你能有所收獲。

mysql中有哪些內(nèi)置函數(shù)，怎么使用？

1.字符串函數(shù)

char_lengt()

concat()

instr()

lcase()

left()

ltrim()

mid()

2.時間函數(shù)

now()

curdate()

curtime()

datediff()

date_add()

數(shù)學函數(shù)

abs()

ceiling()

floor()

pow()

rand()

round()

其他函數(shù)

md5()

version()

database()

UUID()

同一使用select 函數(shù)名(參數(shù)列表)