天天看點

【Linux雲計算架構:第二階段-Linux必會的20多種服務】第12章——mysql-sql語句進階

回顧前面的基礎指令語句

修改資料表

添加字段:

alter table 表名 add 字段名 列類型 [not null|null][primary key][unique][auto_increment][default value]
alter table 表名 add 字段定義 after ar_id;      

删除字段:

alter table 表名 drop 字段名      

修改字段:

alter table 表名 modify 字段名 字段新類型      

完整修改字段:

alter table 表名  change 舊字段名稱  新字段定義      

修改表名稱

alter table 表名 rename 新名字      

删除表

drop table [if (not) exists] 表名;      

表中行的操作

insert

insert [into] 資料表名稱 [(字段清單)] values|value (表達式|null|default,...),(表達式|null|default,...)
insert [into] 資料表名稱 set 字段名稱=值,...
insert與insert...set的差別是後者可以帶有子查詢。      

update – 單表

update 表名 set 字段名稱=值,... [where 條件]
如果省略WHERE條件将更新全部記錄。      

删除記錄 – 單表

delete from 資料表名稱 [where 條件]
如果省略where條件,将删除全部記錄      

select

select 字段清單 from 資料表 [[as] 别名] [where 條件]      

别名的用法:

Select * from 資料表 [[as] 别名]
字段名稱 [[as]别名]
Select product_offer_instance_object_id as ID, product_offer_instance_object_name name,coumn33 ‘金額’From table
select btypeid as '圖書類别ID',btypename as '圖書類型' from category;       
【Linux雲計算架構:第二階段-Linux必會的20多種服務】第12章——mysql-sql語句進階
select語句傳回零條或多條記錄;屬于記錄讀操作

insert、update、delete隻傳回此次操作影響的記錄數;屬于寫操作      

資料類型:

MySQL中定義資料字段的類型對你資料庫的優化是非常重要的。

MySQL支援多種類型,大緻可以分為三類:​

​數值、日期/時間和字元串(字元)類型。​

​ 數值類型

【Linux雲計算架構:第二階段-Linux必會的20多種服務】第12章——mysql-sql語句進階

日期和時間類型:

【Linux雲計算架構:第二階段-Linux必會的20多種服務】第12章——mysql-sql語句進階

字元串類型:

【Linux雲計算架構:第二階段-Linux必會的20多種服務】第12章——mysql-sql語句進階

整型:

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

浮點型:

float([m[,d]]) 占4位元組,1.17E-38~3.4E+38 
double([m[,d]]) 占8位元組
decimal([m[,d]]) 以字元串形式表示的浮點數      

字元型:

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

​​http://www.runoob.com/mysql/mysql-select-query.html​​

常用select指令:

使用select指令檢視mysql資料庫系統資訊:

– 列印目前的日期和時間

select now();      

– 列印目前的日期

select curdate();      

– 列印目前的時間

select curtime();      

– 列印目前資料庫

select database();      

– 列印MySQL版本

select version();      

– 列印目前使用者

select user();      

–檢視系統資訊

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;檢視支援哪些存儲引擎      

–檢視系統運作狀态資訊

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

多使用help:

導出,導入資料庫

導入資料庫

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       |
+----------------+      

導出資料庫

導出資料庫:mysqldump -u 使用者名 -p 資料庫名 > 導出的檔案名
mysqldump -u system -p123456 book>book2.sql      

擴充知識

Mysqldump –uroot –p123456 –B 庫名>檔案.sql
-B : 導出整個庫包含建庫語句
-A:導出全部資料庫      

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

select * into outfile '/tmp/123.txt' from books;  # 此處有個檔案通路權限問題,mysql使用者是可以通路/tmp路徑的,是以這裡放到tmp下
select * from books into outfile '/tmp/456.txt';      

其實就是備份資料庫

擴充: ​

​5.7版本導出報錯,可以設定my.cnf 加上secure-file-priv="/ "​

Sql查詢語句進階

在我們剛導入的book資料庫進行測試

檢視表的内容:

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

檢視字段類型:

desc 表名
mysql> desc books;      

邏輯運算符:

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 |
| 網站設計全程教程  | 科學出版社 |    50 |
| ASP資料庫系統開發執行個體導航  | 人民郵電出版社 |    60 |
| Delphi 5程式設計與控件參考 | 電子工業出版社 |    60 |
| ASP資料庫系統開發執行個體導航  | 人民郵電出版社 |    60 |
+--------------------------------------+--------------------------+-------+      

算術運算符:

= 等于
<>  不等于  !=
>  大于
<  小于
>= 大于等于
<= 小于等于
in  運算符
IN 運算符用于 WHERE 表達式中,以清單項的形式支援多個選擇,文法如下:
WHERE column IN (value1,value2,...)
WHERE column NOT IN (value1,value2,...)
Not in 與in相反
當 IN 前面加上 NOT 運算符時,表示與 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);      

排序:

升序:​​

​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 |
| 網站設計全程教程  |    50 |
| ASP資料庫系統開發執行個體導航  |    60 |
| Delphi 5程式設計與控件參考   |    60 |
| ASP資料庫系統開發執行個體導航  |    60 |

mysql> select bName,price from books where price  in (50,60,70) order by price desc;
+--------------------------------------+-----------------+
| bName                                | price |
+--------------------------------------+-----------------+
| ASP資料庫系統開發執行個體導航  |    60 |
| Delphi 5程式設計與控件參考   |    60 |
| ASP資料庫系統開發執行個體導航  |    60 |
| Illustrator 10完全手冊            |    50 |
| FreeHand 10基礎教程               |    50 |
| 網站設計全程教程              |    50 |      

多個字段排序

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

範圍運算:

[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      

模糊比對查詢:

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

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

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

MySQL子查詢:

概念:在select 的where條件中又出現了select

查詢中嵌套着查詢

選擇 類型名為“網絡技術”的圖書:

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

選擇類型名稱為“黑客”的圖書;

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

Limit限定顯示的條目:

SELECT * FROM table LIMIT [offset,] rows

偏移量 行數

  LIMIT 子句可以被用于強制 SELECT 語句傳回指定的記錄數。LIMIT 接受一個或兩個數字參數。參數必須是一個整數常量。如果給定兩個參數,​​

​第一個參數指定第一個傳回記錄行的偏移量,第二個參數指定傳回記錄行的最大數目。​

​初始記錄行的偏移量是 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個數字,從2開始,偏移量為1

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

檢視所有書籍中價格中最低的三條記錄

我們對所有記錄排序以升序排列,取出前面3個來

mysql> select bName,price from books order by price asc limit 0,3;
+-----------------------------+-------+
| bName                       | price |
+-----------------------------+-------+
| 網站制作直通車       |    34 |
| 黑客與網絡安全       |    41 |
| 網絡程式與設計-asp |    43 |      

我們将子查詢和限制條目,算術運算結合起來查詢

顯示字段bName ,price ;條件:找出價格比電子工業出版社出版的書中最便宜還要便宜的書。

針對這種查詢,我們一步步的來,先找出電子工業出版社出版中最便宜的書

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

或者

多行子查詢: all表示小于子查詢中傳回全部值中的最小值

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

連接配接查詢:

以一個共同的字段,求兩張表當中符合條件的并集。 通過共同字段把這兩張表連接配接起來。

常用的連接配接:

内連接配接:根據表中的共同字段進行比對
外連接配接分兩種:左外連接配接、右外連結。      

内連接配接

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

内連接配接:根據表中的共同字段進行比對

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表從表

主表内容全都有,從表内沒有的顯示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;   # 右連接配接,可以多表連接配接      

聚合函數:

函數:執行特定功能的代碼塊。

算數運算函數: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 desc price limit 0,3;      

可見最貴書是Javascript與Jscript從入門到精通,而不是網站制作直通車

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 |
+-----------------------+-------+
| 網站制作直通車 |    34 |
+-----------------------+-------+      

count()統計記錄數:

統計價格大于40的書籍數量

mysql> select count(*) from books where price>40;
+----------+
| count(*) |
+----------+
|       43 |
+----------+
Count()中還可以增加你需要的内容,比如增加distinct來配合使用
select count(distinct price) from books where price>40;      

算數運算: + - * /

給所有價格小于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;      

字元串函數:

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;      
upper()大寫 : 轉為大寫輸出
mysql> select upper(bname) from books where bId=9;
+---------------------------+
| upper(bname)              |
+---------------------------+
| DREAMWEAVER 4ǽɡµň¶Ľ |
+---------------------------+      
lower()小寫 :轉為小寫輸出
mysql> select lower(bName) from books where bId=10;
+-------------------------------+
| lower(bName)                  |
+-------------------------------+
| 3d max 3.0 創作效果百例 |
+-------------------------------+      

繼續閱讀