mysql用法精華集錦，mysql精華集錦

mysql用法精華集錦，mysql精華集錦

1.      環境：windows，MySQL Server 5.5，Navicat forMySQL

2.      Mysql常用sql語句

SQL分類：

DDL—資料定義語言 (Data Definition Language)（CREATE，ALTER，DROP，DECLARE）

DML—資料操縱語言（SELECT，DELETE，UPDATE，INSERT）

DCL—資料控制語言（GRANT，REVOKE，COMMIT，ROLLBACK）

首先介紹基礎語句：

2.1建立資料庫

CREATE DATABASE database-name

2.2刪除資料庫

drop database dbname

2.3備份sql server

---建立備份資料的device

USE master EXEC sp_addumpdevice ‘disk’,’testBack’,’c:mssql7backupMyNwind_1.dat’

---開始備份

BACKUP DATABASE pubs TO testBack

2.4建立新表

create table tabname(col1 type1[notnull][primary key],col2 type2[not null])

根據已有的表建立新表

2.4A:create table tab_new liketab_old（使用舊錶建立新表）

2.4B:create table tab_new as selectcol1,col2…from tab_old definition only

2.5刪除表

Drop table tabname

2.6為表添加一列

Alter table tabname add column coltype  註：列增加後將不能刪除

2.7添加主鍵/刪除主鍵

Alter table tabname add primarykey(col)

Alter table tabname drop primarykey(col)

2.8添加索引/刪除索引

Create [unique] index idxname ontabname(col…)

drop index idxname

2.9  建立視圖/刪除視圖

create view viewname as select statement

drop view viewname

2.10常用的基本sql語句

尋找：select * fromtable1 where 範圍

插入：insert intotable1(field1,field2) values(value1,value2)

刪除：delete fromtable1 where 範圍

更新：update table1set field1=value1 where 範圍

模糊查詢：select *from table1 where field1 like ‘%value1%’

排序：select * fromtable1 order by field1,field2[desc]

總數：selectcount(*) as totalcount from table1

求和：selectsun(field1) as sunvalue from table1

平均：selectavg(field1) as avgvalue from table1

最大：selectmax(field1) as maxvalue from table1

最小：selectmin(field1) as minvalue from table1

2.11幾個常用進階查詢運算子

2.11A：UNION運算子

UNION運算子通過組合其他兩個結果表（例如table1和table2）並消去表中任何重複行而派生出一個結果表。當ALL與UNION一起使用時（即UNION ALL），不消除重複行。兩種情況下，派生表的每一行不是來自table1就是table2。

2.11B：EXCEPT運算子

EXCEPT運算子通過包括所有在table1中但不再table2中的行並消除所有重複行而派生出的一個結果表。當ALL隨EXCEPT一起使用時（EXCEPT ALL），不消除重複行。

2.11C：INTERSECT運算子

INTERSECT運算子通過至包括table1和table2中都有的行並消除所有重複行而派生出一個結果表。當ALL隨INTERSECT一起使用時（INTERSECT ALL），不消除重複行。

2.12使用外串連

2.12A：left outer join

左外串連：結果集包括串連表的匹配行，也包括做串連表的所有行

SQL：select a.a,a.b,a.c,b.c,b.d,d.f from a LEFT OUT JOIN b ON a.a=b.c

2.12B：right outer join

右外串連：結果集既包括串連表的匹配行，也包括右串連表的所有行

1.12C：full outer join

全外串連：不僅包括符號串連表的匹配行，還包括兩個串連表的所有行

3.      mysql子查詢

3.1mysql子查詢文法與用法執行個體

子查詢是將一個 SELECT 語句的查詢結果作為中間結果，供另一個SQL 陳述式調用。MySQL支援SQL標準要求的所有子查詢格式和操作，也擴充了特有的幾種特性。

樣本：select* from article where uid in(select uid from user where status=1)

3.2mysql標量子查詢

標量子查詢是指子查詢返回的是單一值的標量，如一個數字或一個字串，也是子查詢中最簡單的返回形式。

樣本：select* from article where uid=(select uid from user where status=1 order by uid desclimit 1)

3.3mysql列子查詢

列子查詢是指子查詢返回的結果集是 N 行一列，該結果通常來自對錶的某個欄位查詢返回。

            樣本：select *from article where uid in(select uid from user where status=1)

            3.4mysql行子查詢

         行子查詢是指子查詢返回的結果集是一行N列，該子查詢的結果通常是對錶的某行資料進行查詢而返回的結果集。

        樣本：select* from table1 where (1,2)=(select col1,col2 from table)

       3.5mysql表子查詢

         表子查詢是指子查詢返回的結果集是N行N 列的一個表資料。

        樣本：select *from article where (title,content,uid) in (select title, content, uid fromblog)

        3.6mysql from子查詢

          MySQL FROM 子查詢是指 FROM 的子句作為子查詢語句，主查詢再到子查詢結果中擷取需要的資料

        文法：select …from(subquery) as name…

        3.7mysql exists和notexists子查詢

        文法：select …from table where exists(subquery)

        該文法可以理解為：將主查詢的資料，放到子查詢中做條件驗證，根據驗證結果（TRUE或FALSE）來決定主查詢的資料結果是否得以保留。

        樣本：select *from article where exists(select * from user where article.uid=user.uid)

        3.8mysql關聯子查詢

        關聯子查詢是指一個包含對錶的引用的子查詢，該表也顯示在外部查詢中。通俗一點來講，就是子查詢引用到了主查詢的資料資料

        樣本：selectarticle.* from article inner join user on article.uid=user.uid

4.    mysql迴圈

4.1while…end while迴圈

Create procedure p1()

begin

declare v int;

set v=0;

while v<5 do

         insertinto t values(v)

         setv=v+1;

end while;

      end;

            4.2repeat…end repeat迴圈

      create procedure p2()

      begin

        declare v int;

        set v=0;

        repeat

          insert into t values(v);

          set v=v+1;

          until v>=5;

        end repeat;

      end;

註：until後面可以沒有分號

4.3loop…end loop迴圈

create procedure p3()

begin

  declare v int;

  set v=0;

   loop_label:loop

     insert into tvalues(v);

     set v=v+1;

     if v>=5 then

      leaveloop_label;

     end if;

   end loop;

end;

5.      mysql視圖查詢

5.1視圖定義

視圖是由查詢結果形成的一張虛擬表。

5.2視圖的使用

如果某個查詢結果出現的非常頻繁，也就是要經常拿這個查詢結果來做子查詢

5.3文法

create view 視圖名 as select 語句

5.4視圖優點

l  簡化查詢語句

樣本：有一張商品表，我們要經常查每個欄目下的商品的平均價格

Select cat_id, avg(shop_price) from goods group by cat_id;

建立一個視圖

create view avgPrice as select cat_id, avg(shop_price) fromgoods group by cat_id;

當我們重新查詢每個欄目平均價格時，只需要這麼寫

select * from avgPrice;

l  進行許可權控制

把表的許可權封閉，但是開放相應的視圖許可權，視圖裡只開放部分資料列，例如我們的goods商品表，我們不想讓別人看到我們的銷售價格，這個時候我們可以把商品表許可權封閉，建立一張視圖

create view showGoods as select goods_id,goods_name fromgoods;

       5.5視圖修改

          Alter view 視圖名 as select 語句;

       5.6視圖與表的關係

          視圖是表的查詢結果，自然表的資料變了，會影響視圖的結果

6.      mysql關聯查詢

6.1串連查詢簡介

串連查詢中用來串連兩個表的條件稱為串連條件或串連謂詞。其形式為：

[<表1>].<列名><串連運算子>[<表2>].<列2>

常見串連運算子包括

l  比較子：=、>、<、>=、<=、!=、between和and

l  邏輯運算子：not、and、or

      6.2串連按照結果集分類

l  內串連：表中的行互相串連，結果集的行數等於每個表滿足條件的行數乘積，參與串連的表示平等的。

l  外串連：參與串連的表有主次之分，主表的每一行資料去匹配從表的資料列，符合串連條件的資料直接返回到結果集中，不符合串連條件的的資料列將以null填充後返回到結果集中，其中外串連又分為左外串連，右外串連和全串連。

6.3內串連查詢

內串連文法結構:

Select <屬性或運算式列表> from <表名> [inner]join <表名> on <串連條件> [where <限定條件>]

  Inner可以省略，當只見到join時就是省略掉了inner。內串連就是傳統的串連操作，這裡用on子句指定串連條件，用where指定其他限定條件,

樣本：select p.name,c.countryname from country as c inner join person p onp.countryid=c.countryid

6.4左外串連查詢

  左外串連文法結構：

Select <屬性或運算式列表> from <表名> leftouter join <表名> on <串連條件> [where <限定條件>]

  左外串連在結果表中包含第一個表中滿足條件的所有記錄，如果串連條件匹配，第二張表返回相應的值，否則返回null。也就是說不管第二個表有沒有記錄，第一張表的欄位都會返回。

樣本：select p.name,c.countryname from country as c right join person p onp.countryid=c.countryid

6.5右外串連查詢

  右外串連查詢文法結構：

Select <屬性或運算式列表> from <表名> rightouter join <表名> on <串連條件> [where <限定條件>]

  右外串連的結果表中包含第二張表中滿足條件的所有記錄，如果串連條件匹配，第一張表返回相應的值，否則返回null。

樣本：select p.name, c.countryname from country as c right join person pon p.countryid=c.countryid

6.6全外串連查詢

  全外串連查詢文法結構：

Select <屬性或運算式列表> from <表名> fullouter join <表名> on <串連條件> [where <限定條件>]

  全外串連查詢的結果集表中包含兩個表滿足記錄所有記錄。如果在串連上條件上匹配的元組，則另一個表返回相應的值，否則返回null