MySQL基礎與進階講義

本文主要給大家介紹MySQL基礎與進階講義，希望可以給大家補充和更新些知識，如有其它問題需要了解的可以持續(xù)在億速云行業(yè)資訊里面關注我的更新文章的。

MySQL基礎篇

2.1  數(shù)據(jù)類型

MySQL中定義數(shù)據(jù)字段的類型對你數(shù)據(jù)庫的優(yōu)化是非常重要的。  
MySQL支持多種類型，大致可以分為三類：數(shù)值、日期/時間和字符串(字符)類型。

2.1.1  數(shù)值類型

2.1.2  日期和時間類型

2.1.3  字符串類型

整型

?tinyint,占1字節(jié),有符號：-128~127,無符號位:0~255  
?smallint,占2字節(jié),有符號：-32768~32767,無符號位:0~65535  
?mediumint,占3字節(jié),有符號：-8388608~8388607,無符號位:0~16777215  
?int,占4字節(jié),有符號：-2147483648~2147483647,無符號位:0~4284967295  
?bigint,占8字節(jié)  
?bool 等價于tinyint(1)  布爾型

浮點型

?float([m[,d]]) 占4字節(jié),1.17E-38~3.4E+38  
?double([m[,d]]) 占8字節(jié)  
?decimal([m[,d]]) 以字符串形式表示的浮點數(shù)

字符型

?char([m]):固定長度的字符,占用m字節(jié)  
?varchar[(m)]:可變長度的字符,占用m+1字節(jié)，大于255個字符：占用m+2  
?tinytext,255個字符(2的8次方)  
?text,65535個字符(2的16次方)  
?mediumtext,16777215字符(2的24次方)  
?longtext,(2的32次方)  
?enum(value,value,...)占1/2個字節(jié) 最多可以有65535個成員  
?set(value,value,...)占1/2/3/4/8個字節(jié)，最多可以有64個成員

2.2  常用select命令

使用select命令查看mysql數(shù)據(jù)庫系統(tǒng)信息：  
-- 打印當前的日期和時間

select now();  

-- 打印當前的日期

select curdate();  

-- 打印當前的時間

select curtime();  

-- 打印當前數(shù)據(jù)庫

select database();  

-- 打印MySQL版本

select version();  

-- 打印當前用戶

select user();  

--查看系統(tǒng)信息

show variables;  
show global variables;  
show global variables like '%version%';  
show variables like '%storage_engine%'; 默認的存儲引擎  

like模糊搜索還可用戶where字句，例如

select * from students where stname like '%l%1%2%3%';  

除了like 還有not like  
show engines;查看支持哪些存儲引擎

--查看系統(tǒng)運行狀態(tài)信息

show status;  
show global status like 'Thread%';  

2.3  導出，導入數(shù)據(jù)庫

2.3.1  導入數(shù)據(jù)庫

導入數(shù)據(jù)庫前必須創(chuàng)建一個空數(shù)據(jù)庫

mysql -e 'create database book' -uroot -p123456  
或者登陸 mysql   
create database book;  

導入（方法一）

mysql -uroot -p123456 book < book.sql  
mysql> use book;  
mysql> show tables;  
+----------------+  
| Tables_in_book |  
+----------------+  
| books          |  
| catego  
+----------------+  

導入（方法二）

create database book;  
mysql> use book;      
mysql> source /root/book.sql  #sql腳本的路徑  
mysql> show tables;  
+----------------+  
| Tables_in_book |  
+----------------+  
| books          |  
| category       |  
+----------------+  

2.3.2  導出數(shù)據(jù)庫

導出數(shù)據(jù)庫：mysqldump -u 用戶名 -p 數(shù)據(jù)庫名 > 導出的文件名

mysqldump -uroot -p123456 book>book2.sql  

擴展知識  
Mysqldump –uroot –p123456 –B 庫名>文件.sql  
-B : 導出整個庫包含建庫語句  
-A：導出全部數(shù)據(jù)庫

如何把一個select的結果導出到文本

select * into outfile '/tmp/123.txt' from books; 此處有個文件訪問權限問題，mysql用戶是可以訪問/tmp路徑的,所以這里放到tmp下  
select * from books into outfile '/tmp/456.txt';  
其實就是備份數(shù)據(jù)庫  

2.4  sql查詢語句進階

在我們剛導入的book數(shù)據(jù)庫進行測試

2.4.1  查看表的內(nèi)容：

mysql> select * from category;  
mysql> select * from books;  
mysql> select * from books\G  

2.4.2  查看字段類型：

desc 表名

mysql> desc books;  

2.4.3  邏輯運算符:

and or not  
and 且  
or  或  
not 非  
選擇出書籍價格為（30,40,50,60）的記錄,只顯示書籍名稱，出版社，價格

mysql> select bName,publishing,price from books where price=30 or price=40 or price=50 or price=60;  
+--------------------------------------+--------------------------+-------+  
| bName                                | publishing               | price |  
+--------------------------------------+--------------------------+-------+  
| Illustrator 10完全手冊    | 科學出版社 |    50 |  
| FreeHand 10基礎教程   | 北京希望電子出版  |    50 |  
| 網(wǎng)站設計全程教程  | 科學出版社 |    50 |  
| ASP數(shù)據(jù)庫系統(tǒng)開發(fā)實例導航    | 人民郵電出版社   |    60 |  
| Delphi 5程序設計與控件參考 | 電子工業(yè)出版社   |    60 |  
| ASP數(shù)據(jù)庫系統(tǒng)開發(fā)實例導航    | 人民郵電出版社   |    60 |  
+--------------------------------------+--------------------------+-------+  

2.4.4  算術運算符：

=   等于  
<>  不等于  !=

大于  
<   小于  
=  大于等于  
<=  小于等于  
in  運算符  
IN 運算符用于 WHERE 表達式中，以列表項的形式支持多個選擇，語法如下：

WHERE column IN (value1,value2,...)  
WHERE column NOT IN (value1,value2,...)  

Not in 與in相反  
當 IN 前面加上 NOT 運算符時，表示與 IN 相反的意思，即不在這些列表項內(nèi)選擇。

找出價格大于60的記錄

mysql> select bName,price from books where price>60;  

找出價格為60的

mysql> select bName,price from books where price=60;  

找出價格不等于60的

mysql> select bName,price from books where price<>60;  

找出價格是60，50，70的記錄

mysql> select bName,price from books where price in (50,60,70);  

找出價格不是60，50，70的記錄

mysql> select bName,price from books where price not in (50,60,70);  

2.4.5  排序：

升序：order by “排序的字段” asc  默認  
降序：oredr by “排序的字段” desc

mysql> select bName,price from books where price  in (50,60,70) order by price asc;  
+------------------------------------------------+-------+  
| bName                                | price |  
+------------------------------------------------+-------+  
| Illustrator 10完全手冊    |    50 |  
| FreeHand 10基礎教程   |    50 |  
| 網(wǎng)站設計全程教程  |    50 |  
| ASP數(shù)據(jù)庫系統(tǒng)開發(fā)實例導航    |    60 |  
| Delphi 5程序設計與控件參考     |    60 |  
| ASP數(shù)據(jù)庫系統(tǒng)開發(fā)實例導航    |    60 |  

mysql> select bName,price from books where price  in (50,60,70) order by price desc;  
+--------------------------------------+-----------------+  
| bName                                | price |  
+--------------------------------------+-----------------+  
| ASP數(shù)據(jù)庫系統(tǒng)開發(fā)實例導航    |    60 |  
| Delphi 5程序設計與控件參考     |    60 |  
| ASP數(shù)據(jù)庫系統(tǒng)開發(fā)實例導航    |    60 |  
| Illustrator 10完全手冊            |    50 |  
| FreeHand 10基礎教程               |    50 |  
| 網(wǎng)站設計全程教程              |    50 |  

多個字段排序

select bName,price from books where price  in (50,60,70) order by price desc,bName desc;  

2.4.6  范圍運算：

[not]between ....and....  
Between and 可以使用大于小于的方式來代替,并且使用大于小于意義表述更明確  
查找價格不在30到60之間的書名和價格

mysql> select bName,price from books where price not between 30 and 60 order by price desc; 

注：  
這里的查詢條件有三種：between。。。and，or 和 in

(30,60) >30 and <60  
[30,60] >=30 and <=60  

2.4.7  模糊匹配查詢：

字段名 [not]like '通配符'  ----》% 任意多個字符

查找書名中包括"程序"字樣記錄

mysql> select bName from books where bName like '%程序%';  
不含有  
mysql> select bName from books where bName not like '%程序%';  

2.4.8  MySQL子查詢：

概念：在select 的where條件中又出現(xiàn)了select  
查詢中嵌套著查詢

選擇 類型名為“網(wǎng)絡技術”的圖書：

mysql> select bName,bTypeId from books where bTypeId=(select bTypeId from category where bTypeName='網(wǎng)絡技術');  

選擇類型名稱為“***”的圖書；

mysql> select bName,bTypeId from books where bTypeId=(select bTypeId from category where bTypeName='***');  

2.4.9  Limit限定顯示的條目：

SELECT * FROM table LIMIT [offset,] rows   
                            偏移量  行數(shù)  

　　LIMIT 子句可以被用于強制 SELECT 語句返回指定的記錄數(shù)。LIMIT 接受一個或兩個數(shù)字參數(shù)。參數(shù)必須是一個整數(shù)常量。如果給定兩個參數(shù)，第一個參數(shù)指定第一個返回記錄行的偏移量，第二個參數(shù)指定返回記錄行的最大數(shù)目。初始記錄行的偏移量是 0(而不是 1)：

比如select * from table limit m,n語句  
表示其中m是指記錄開始的index，從0開始，表示第一條記錄  
n是指從第m+1條開始，取n條。

查出category表中第2條到第6行的記錄。  
首先2到6行有2，3，4，5，6總共有5個數(shù)字，從2開始，偏移量為1

mysql> select * from category limit 1,5;  
+---------+--------------+  
| bTypeId | bTypeName    |  
+---------+--------------+  
|       2 | 網(wǎng)站       |  
|       3 | 3D動畫     |  
|       4 | linux學習  |  
|       5 | Delphi學習 |  
|       6 | ***       |  
+---------+--------------+  

查看所有書籍中價格中最低的三條記錄  
我們對所有記錄排序以升序排列，取出前面3個來

mysql> select bName,price from books order by price asc limit 0,3;  
+-----------------------------+-------+  
| bName                       | price |  
+-----------------------------+-------+  
| 網(wǎng)站制作直通車       |    34 |  
| ***與網(wǎng)絡安全       |    41 |  
| 網(wǎng)絡程序與設計－asp |    43 |  

我們將子查詢和限制條目，算術運算結合起來查詢  
顯示字段bName ,price ；條件：找出價格比電子工業(yè)出版社出版的書中最便宜還要便宜的書。  
針對這種查詢，我們一步步的來，先找出電子工業(yè)出版社出版中最便宜的書

mysql> select bName,price from books where publishing="電子工業(yè)出版社" order by price asc limit 0,1;  
mysql> select bName,price from books where price<(select price from books where publishing="電子工業(yè)出版社" order by price asc limit 0,1);  

或者  
多行子查詢： all表示小于子查詢中返回全部值中的最小值

mysql> select bName,price from books where price<all(select price from books where publishing="電子工業(yè)出版社");  

2.4.10  連接查詢：

以一個共同的字段，求兩張表當中符合條件的并集。 通過共同字段把這兩張表連接起來。  
常用的連接：  
內(nèi)連接：根據(jù)表中的共同字段進行匹配  
外連接分兩種：左外連接、右外鏈接。

內(nèi)連接  
語法：

select 字段  from 表1 inner join 表2  on 表1.字段=表2.字段  

內(nèi)連接：根據(jù)表中的共同字段進行匹配  
測試

select a.bname,a.price,b.btypename from books a inner join category b on a.btypeid=b.btypeid;  
實際使用中inner可省略掉  
跟WHERE 子句結果一樣  
select a.bname,a.price,b.btypename from books a, category b where a.btypeid=b.btypeid;  

外連接 (分為左外連接；右外連接)

1. 左連接： select  字段 from a表 left join b表  on 連接條件  
   a表是主表，都顯示。  
   b表從表  
   主表內(nèi)容全都有，從表內(nèi)沒有的顯示null。

   Select a.bname,a.price,b.btypename from books a left join category b on a.btypeid=b.btypeid;  

2. 右連接：select 字段 from a表 right join  b表 on 條件  
   a表是從表，  
   b表主表，都顯示。  

   Select a.bname,b.* from books a right join category b on a.btypeid=b.btypeid;  
   右連接，可以多表連接  

2.4.11  聚合函數(shù)

函數(shù)：執(zhí)行特定功能的代碼塊。  
算數(shù)運算函數(shù)：  
Sum()求和  
顯示所有圖書單價的總合

    mysql> select sum(price) from books; 或select sum(price) as 圖書總價 from books;  
+------------+  
| sum(price) |  
+------------+  
|      10048 |  
+------------+  

avg()平均值：  
求書籍Id小于3的所有書籍的平均價格

mysql> select avg(price) from books where bId<=3;  
+------------+  
| avg(price) |  
+------------+  
|    39.3333 |  
+------------+  

max() 最大值：  
求所有圖書中價格最貴的書籍

mysql> select bName,max(price) from books; 這種方法是錯誤的  

我們來查一下最貴的圖書是哪本？

select bname,price from books order by price asc limit 0,3;  

可見最貴書是Javascript與Jscript從入門到精通，而不是網(wǎng)站制作直通車

select bName,price from books where price=(select max(price) from books);  
+----------------------------------------+-------+  
| bName                          | price |  
+----------------------------------------+-------+  
| Javascript與Jscript從入門到精通 |  7500 |  
+----------------------------------------+-------+  

min()最小值：  
求所有圖書中價格便宜的書籍？

mysql> select bName,price from books where price=(select min(price) from books);  
+-----------------------+-------+  
| bName                 | price |  
+-----------------------+-------+  
| 網(wǎng)站制作直通車 |    34 |  
+-----------------------+-------+  

count()統(tǒng)計記錄數(shù):  
統(tǒng)計價格大于40的書籍數(shù)量

mysql> select count(*) from books where price>40;  
+----------+  
| count(*) |  
+----------+  
|       43 |  
+----------+  

Count（）中還可以增加你需要的內(nèi)容，比如增加distinct來配合使用

select count(distinct price) from books where price>40;  

算數(shù)運算：

• • • /  
      給所有價格小于40元的書籍，漲價5元  

      mysql> update books set price=price+5 where price<40;  

      給所有價格高于70元的書籍打8折

      mysql> update books set price=price*0.8 where price>70;  

      字符串函數(shù)：  
      substr(string ,start,len) 截取：從start開始，截取len長.start 從1開始算起。

      mysql> select substr(bTypeName,1,7) from category where bTypeId=10;  
      +-----------------------+  
      | substr(bTypeName,1,7) |  
      +-----------------------+  
      | AutoCAD               |      本來是AutoCAD技術  
      +-----------------------+  

      select substr(bTypeName,8,2)from category where bTypeId=10;  
      +-----------------------+  
      | substr(bTypeName,8,2) |  
      +-----------------------+  
      | 技術                  |          只截取漢字  
      +-----------------------+  
      1 row in set (0.00 sec)  

concat(str1,str2,str3.....) 拼接。 把多個字段拼成一個字段輸出

mysql> select concat(bName,publishing) from books;  
mysql> select concat(bName,"-----",publishing) from books;  

大小寫轉(zhuǎn)換  
upper()大寫 : 轉(zhuǎn)為大寫輸出

    mysql> select upper(bname) from books where bId=9;  
+---------------------------+  
| upper(bname)              |  
+---------------------------+  
| DREAMWEAVER 4?ɡμň?? |  
+---------------------------+  

這樣轉(zhuǎn)換中文會出現(xiàn)亂碼  
lower()小寫 ：轉(zhuǎn)為小寫輸出

    mysql> select lower(bName) from books where bId=10;  
+-------------------------------+  
| lower(bName)                  |  
+-------------------------------+  
| 3d max 3.0 創(chuàng)作效果百例 |  

看了以上關于MySQL基礎與進階講義，希望能給大家在實際運用中帶來一定的幫助。本文由于篇幅有限，難免會有不足和需要補充的地方，如有需要更加專業(yè)的解答，可在官網(wǎng)聯(lián)系我們的24小時售前售后，隨時幫您解答問題的。