MySQL基礎(chǔ)篇

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

MySQL中定義數(shù)據(jù)字段的類型對你數(shù)據(jù)庫的優(yōu)化是非常重要的。

2.1.1 數(shù)值類型

2.1.2 日期和時間類型

2.1.3 字符串類型

整型

?tinyint,占1字節(jié),有符號：-128~127,無符號位:0~255

浮點(diǎn)型

?float([m[,d]]) 占4字節(jié),1.17E-38~3.4E+38

字符型

?char([m]):固定長度的字符,占用m字節(jié)

2.2 常用select命令

使用select命令查看mysql數(shù)據(jù)庫系統(tǒng)信息：

select now();  

— 打印當(dāng)前的日期

select curdate();  

— 打印當(dāng)前的時間

select curtime();  

— 打印當(dāng)前數(shù)據(jù)庫

select database();  

— 打印MySQL版本

select version();  

— 打印當(dāng)前用戶

select user();  

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

show variables;   show global variables;   show global variables like '%version%';   show variables like '%storage_engine%'; 默認(rèn)的存儲引擎  

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

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

除了like 還有not like

–查看系統(tǒng)運(yùn)行狀態(tài)信息

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

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

2.3.1 導(dǎo)入數(shù)據(jù)庫

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

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

導(dǎo)入（方法一）

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

導(dǎo)入（方法二）

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

2.3.2 導(dǎo)出數(shù)據(jù)庫

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

mysqldump -uroot -p123456 book>book2.sql  

擴(kuò)展知識

如何把一個select的結(jié)果導(dǎo)出到文本

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

2.4 sql查詢語句進(jìn)階

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

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

mysql> select * from category;   mysql> select * from books;   mysql> select * from booksG  

2.4.2 查看字段類型：

desc 表名

mysql> desc books;  

2.4.3 邏輯運(yùn)算符:

and or not

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

2.4.4 算術(shù)運(yùn)算符：

= 等于

大于

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

Not in 與in相反

找出價格大于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 默認(rèn)

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

多個字段排序

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

2.4.6 范圍運(yùn)算：

[not]between ….and….

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

注：

(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)絡(luò)技術(shù)”的圖書：

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

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

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

比如select * from table limit m,n語句

查出category表中第2條到第6行的記錄。

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

查看所有書籍中價格中最低的三條記錄

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

我們將子查詢和限制條目，算術(shù)運(yùn)算結(jié)合起來查詢

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);  

或者

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

2.4.10 連接查詢：

以一個共同的字段，求兩張表當(dāng)中符合條件的并集。 通過共同字段把這兩張表連接起來。

內(nèi)連接

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

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

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

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

左連接： select 字段 from a表 left join b表 on 連接條件

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

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

2.4.11 聚合函數(shù)

函數(shù)：執(zhí)行特定功能的代碼塊。

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

avg()平均值：

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)計(jì)記錄數(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ù)運(yùn)算：

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

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

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

字符串函數(shù)：

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

select substr(bTypeName,8,2)from category where bTypeId=10;   +-----------------------+   | substr(bTypeName,8,2) |   +-----------------------+   | 技術(shù)                  |          只截取漢字   +-----------------------+   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)換

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

這樣轉(zhuǎn)換中文會出現(xiàn)亂碼

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