MySQL命令

1.mysql  -uroot -p  進(jìn)入到MySQL客戶端

2.show databases; 顯示所有的數(shù)據(jù)庫列表

3.use *;連接到某個(gè)數(shù)據(jù)庫

4.show create database  * ;顯示這個(gè)數(shù)據(jù)庫的建庫信息(字符編碼...)

5.show tables [from *]; 顯示某個(gè)數(shù)據(jù)庫下所有的數(shù)據(jù)表;

6.show create table  * ;顯示某個(gè)數(shù)據(jù)表的建表信息(引擎類型,字符編碼,字段及屬性等);

7.create table table_name[if not exists](

id tinyint unsigned PRimary key auto_increment,

name varchar(20) not null); 建表語句

8.insert [into] table_name[field_name1,...] values(value1,...),(value2,...);插入記錄,可同時(shí)插入多條記錄

9.insert table_name [field_name1,...] select expression; 將查找到的記錄直接插入.

10.delete from table_name where expression;刪除符合where條件的所有記錄;

11.update table_name set column_name1=new_value1,column_name2=new_value2,..

     where expression;將符合where條件的記錄進(jìn)行更改;

12.select column_name from table_name where expression group by column_name having 條件 order by 條件 limit 條件;

理解順序:從表中選擇出符合where條件的所有記錄,再按照group by 的條件進(jìn)行分組,

符合group by 條件的所有記錄分為一組,再由having 過濾條件進(jìn)行過濾,最終按照order by 的條件進(jìn)行排序并輸出.[having 過濾表達(dá)式是對group by 后的各個(gè)組進(jìn)行過濾后,再輸出]

13.子查詢subquery;

     由比較運(yùn)算符引發(fā)的子查詢: = ,<,>,<=,>=,!=,<>;

      由[not] in / exists 引發(fā)的子查詢;

14.多表連接進(jìn)行更新

建表的同時(shí),將查詢的結(jié)果進(jìn)行插入:

create table table_name(expr) select columns from table_name;

多表連接進(jìn)行更新,可以對數(shù)據(jù)表瘦身,減小存儲(chǔ)體積.

update table_name1 [as] alias1 inner join table_name2 [as] alias2 

ON 連接條件

SET更新內(nèi)容;

將兩個(gè)表進(jìn)行連接,當(dāng)某條記錄符合連接條件時(shí),則執(zhí)行SET 語句進(jìn)行記錄的更新;

15.連接包括Inner join , left join,right join 三種.內(nèi)連接用的多.

select [columns] from table_name1 as alias1 

inner join table_name2  as alias2 ON 連接條件

inner join table_name3  as alias3 ON 連接條件

...;

多表的連接查詢;

內(nèi)連接只顯示符合連接條件的所有記錄;

左外連接顯示左表中的所有記錄 , 以及   右表中符合連接條件的記錄;

右外連接顯示右表中的所有記錄,  以及   左表中符合連接條件的記錄;

16.delimiter //       delimiter ; 改變命令行的分界符;

17.運(yùn)算符及函數(shù)

1.字符函數(shù): concat('manny','rose')='mannyrose',

    concat_ws('--','manny','rose','L')='manny--rose--L',

    format(1111.111,1),

    lower('MANNY')='manny',

     upper('manny')='MANNY',

    left('manny',2)='ma',

    right('manny',2)='ny',length(' manny ')=7,

    ltrim(' manny')='manny',[刪除前導(dǎo)空格]

    rtrim('manny ')='manny',[刪除后繼空格]

    trim(' manny ')='manny',[刪除前后空格]

    trim(leading '?' from '??manny')='manny',[刪除前導(dǎo)的特定字符]

    trim(trailing '?' from 'manny??')='manny',[刪除后繼的特定字符]

    trim(both '?' from '??manny??')='manny',[刪除前導(dǎo)與后繼的特定字符]

    substring('manny',2,2)='an',[注意:mysql中字符是從下標(biāo)1開始排序]

    [not] like ,[模式匹配] %表示任意多個(gè)的任意字符; _表示一個(gè)任意字符; 

    select * from test where first_name LIKE '%1%%' escape '1';

    表示'1'后面的%不需要按任意字符去解析.

    replace('manny','n','m')='mammy'[將某些字符換成另外一些字符]

2.數(shù)值運(yùn)算符及函數(shù)

ceil(3.3)=4;[進(jìn)一取整]

3 div 4 = 0;[結(jié)果取整]

floor(3.3)=3,floor(3.8)=3;[舍一取整]

10 mod 4 = 2; 10.2 mod 4=2.2; -10 mod 4=-2;[取余,同運(yùn)算符號 %]

power(2,2)=4;[冪運(yùn)算]

round(3.3)=3;round(3.5)=4;[四舍五入]

truncate(1111.11,1)=1111.1;[數(shù)字截取]

truncate(1111.11,-1)=1110;truncate(1111.11,-2)=1100;

3.比較運(yùn)算符及函數(shù)

[NOT] between ... and ...[在某個(gè)區(qū)間內(nèi)]

select 5.5 between 4 and 6; return 1; 0表示false,1表示true;

[NOT] in()[在列出的值范圍內(nèi)]

select 5.5 in (4.5,5.5,6.5); return 1;

IS [NOT] NULL[為空]

select null is null; return 1;

select '' is null; return 0;

select * from test where first_name is not null;

    4.日期時(shí)間函數(shù)

NOW();當(dāng)前日期與時(shí)間

select now();

CURDATE(); 當(dāng)前日期

select curdate();

CURTIME(); 當(dāng)前時(shí)間

select curtime();

DATE_ADD();日期加或減

select date_add('2014-02-02',interval 2 week);

select date_add('2014-2-2',interval 2 year);

select date_add('2014-2-2',interval 2 day);

select date_add(curdate(),interval 2 month);

DATEDIFF();日期差值[以日為單位]

select datediff('2014-2-2','2015-2-2');return -365;

select datediff(curdate(),'2014-2-2');

DATE_FORMAT();時(shí)間/日期格式化

select date_format('2014-2-2','%m/%d/%Y');return 02/02/2014;[帶有前導(dǎo)0]

select date_format('2014-2-2','%M/%D/%y');return February/2nd/14;

select date_format(now(),'%Y年%m月%d日 %H點(diǎn):%i分:%s秒');

[只能用i,不能用m]

[h表示12小時(shí)制,H表示24小時(shí)制]

[return 2014年07月30日 22點(diǎn):48分:04秒]

[注意大小寫的差別]

     5.信息函數(shù)

CONNECTION_ID();[連接ID(一個(gè)連接是一個(gè)線程滴!!!)]

DATABASE(); [當(dāng)前數(shù)據(jù)庫]

LAST_INSERT_ID();[最后插入記錄的ID][注意:如果一次插入多條記錄時(shí),按一條計(jì)算這個(gè)ID]

USER(); [當(dāng)前的用戶名]

VERSION(); [當(dāng)前MySQL軟件的版本號]

     6.聚合函數(shù)

[特點(diǎn):只有一個(gè)返回值]

AVG();[平均值函數(shù)]

COUNT();[計(jì)數(shù)函數(shù)] select count(*) from test;[計(jì)算所有的記錄數(shù)]

MAX();[最大值函數(shù)]

MIN();[最小值函數(shù)]

SUM();[求和函數(shù)]

     7.加密函數(shù)

md5();[信息摘要函數(shù)]

select md5('admin');返回一個(gè)32位的字符串;

PASSWord();[密碼算法]

set password=password('manny');[更改客戶端登陸密碼]

[以上為均為內(nèi)置函數(shù)]

18.自定義函數(shù)[UDF:user-defined function]

CREATE  FUNCTION  function_name

RETURNS type{STRING,INTEGER,REAL,DECIMAL}

routine_body[函數(shù)體]

關(guān)于函數(shù)體:

1>函數(shù)體由合法的SQL語句構(gòu)成

2>函數(shù)體可以是簡單的SELECT或INSERT語句;

3>函數(shù)體如果為復(fù)合結(jié)構(gòu)則使用BEGIN

...

   END語句;

4>復(fù)合結(jié)構(gòu)可以包含聲明/循環(huán)/控制結(jié)構(gòu);

1.不帶參數(shù)的自定義函數(shù)

CREATE FUNCTION f1()

RETURNS  varchar(30)

RETURN    date_format(now(),'%Y年%m月%d日 %H點(diǎn):%i分:%s秒');

2.帶有參數(shù)的自定義函數(shù)

CREATE FUNCTION f2(num1 smallint unsigned,num2 smallint unsigned)

RETURNS float(10,2) unsigned

RETURN   (num1+num2)/2 ;

調(diào)用 SELECT f2(1,2); return 1.5;

刪除 DROP FUNCTION f2;

3.具有復(fù)合結(jié)構(gòu)函數(shù)體的自定義函數(shù)

此時(shí)分界符必須修改成其他 DELIMITER //,否則與復(fù)合體中的;混淆

CREATE FUNCTION addUser(username varchar(20))

RETURNS  int unsigned

BEGIN

INSERT test(username) values(username);

RETURN LAST_INSERT_ID();

END

//

19.編碼:  創(chuàng)建數(shù)據(jù)庫時(shí)可以指定編碼方式[create database charset utf8]

這樣在此數(shù)據(jù)庫中建表時(shí),這個(gè)表默認(rèn)使用的編碼方式和數(shù)據(jù)庫的編碼方式一樣.

當(dāng)然也可以單獨(dú)修改某個(gè)表的編碼方式[alter table test charset utf8]

而SET NAMES GBK 則只是改變了客戶端的編碼,寫入到數(shù)據(jù)表中的數(shù)據(jù)還是

按照服務(wù)器中數(shù)據(jù)表所指定的編碼方式進(jìn)行存儲(chǔ).

20.存儲(chǔ)過程 [PROCEDURE]

定義:存儲(chǔ)過程是SQL語句和控制語句的預(yù)編譯集合,以一個(gè)名字存儲(chǔ)并作為一個(gè)單元處理.[可以存在多個(gè)返回值]

優(yōu)點(diǎn):

增強(qiáng)SQL語句的功能與靈活性

實(shí)現(xiàn)較快的執(zhí)行速度.[在創(chuàng)建時(shí)已經(jīng)進(jìn)行語法分析和編譯,再次調(diào)用時(shí)便不再需要分析和編譯]

減少網(wǎng)絡(luò)流量.

CREATE 

[DEFINER={user|CURRENT_USER}][不指定時(shí),默認(rèn)當(dāng)前用戶]

PROCEDURE sp_name ([proc_parameter[,...]])[也可不帶參數(shù)]

[characteristic...] 

routine_body[稱為存儲(chǔ)過程的過程體]

proc_parameter:

[IN|OUT|INOUT] param_name  type[參數(shù)類型,名稱]

IN:表示該參數(shù)的值必須在調(diào)用存儲(chǔ)過程時(shí)指定.

OUT:表示該參數(shù)的值可以被存儲(chǔ)過程的過程體改變,并可以返回.

INOUT:表示該參數(shù)在調(diào)用存儲(chǔ)過程時(shí)必須指定,并且可以被改變和返回.

關(guān)于過程體:1>過程體由合法的SQL語句構(gòu)成

2>過程體可以是任意的SQL語句.[主要是CRUD語句]

3>過程體如果為復(fù)合結(jié)構(gòu)則使用BEGIN[超過了兩個(gè)及以上的語句]

...

   END語句;

4>復(fù)合結(jié)構(gòu)可以包含聲明/循環(huán)/控制結(jié)構(gòu);

[存儲(chǔ)過程的修改ALTER只能修改特性/注釋,不能修改過程執(zhí)行體,只能刪除后創(chuàng)建.]

1>創(chuàng)建不帶參數(shù)的存儲(chǔ)過程:

create procedure p1()

select version();

2>創(chuàng)建帶有IN類型參數(shù)的存儲(chǔ)過程:

create procedure p2(in p_id int unsigned)

begin

delete from users where id=p_id;

end

//[同樣要將定界符修改成其他]

3>創(chuàng)建帶有IN和OUT的存儲(chǔ)過程:

create procedure p3(in p_id int unsigned, out userNums int unsigned)

begin

delete from users where id=p_id;[注意:列名與參數(shù)名不能重合]

select count(*) from usersInto userNums;[將查詢結(jié)果放到輸出參數(shù)中]

end

//

call p3(3,@nums);[@聲明用戶變量][局部變量declare只在begin...end之間的首行]

select @nums;

4>創(chuàng)建帶有多個(gè)OUT參數(shù)的存儲(chǔ)過程:

row_count(): 得到插入/刪除/更新操作所影響的行數(shù).

create procedure p4( in p_id int unsigned,out deleteNums int unsigned,

   out remainNums int unsigned)

begin

delete from users where id=p_id;

select row_count() from users INTO deleteNums;

select count(*) from users INTO remainNums;

end

//

call p4(3,@delete,@remain);[調(diào)用存儲(chǔ)過程]

select @delete,@remain;[查詢結(jié)果]

21.存儲(chǔ)過程與自定義函數(shù)的區(qū)別

1.存儲(chǔ)過程實(shí)現(xiàn)的功能更復(fù)雜一些,主要針對表來操作,而函數(shù)則針對性更強(qiáng),功能更單一.

2.存儲(chǔ)過程可以返回多個(gè)值,而函數(shù)只能有一個(gè)返回值.

3.存儲(chǔ)過程一般獨(dú)立來執(zhí)行[call p_name([ ])],而函數(shù)可以作為其他SQL語句的組成部分出現(xiàn).[ 可以把某些功能封裝成存儲(chǔ)過程,而不是全都用程序來實(shí)現(xiàn),這樣更有效率. ]

22.存儲(chǔ)引擎

什么是存儲(chǔ)引擎: MySQL將數(shù)據(jù)以不同的技術(shù)存儲(chǔ)在文件(內(nèi)存)中,這種技術(shù)就稱為存儲(chǔ)引擎.

每一種存儲(chǔ)引擎使用不同的存儲(chǔ)機(jī)制,索引技巧,鎖定水平,最終提供廣泛且不同的功能.

了解每種引擎的特點(diǎn):

并發(fā)控制:當(dāng)多個(gè)連接對記錄進(jìn)行修改時(shí)保證數(shù)據(jù)的一致性和完整性.

鎖 共享鎖(讀鎖):同一時(shí)段,多個(gè)用戶可以讀取同一資源,讀取過程中數(shù)據(jù)不會(huì)發(fā)生任何變化.

鎖 排他鎖(寫鎖):在任何時(shí)候,只有一個(gè)用戶可以進(jìn)行寫入資源,當(dāng)進(jìn)行寫鎖時(shí)會(huì)阻塞其他用戶進(jìn)行操作(其他的讀鎖或?qū)戞i操作).

鎖的粒度: 行鎖:開銷最大的鎖策略

表鎖:開銷最小的鎖策略

事務(wù)處理:用于保證數(shù)據(jù)庫的完整性.

[多個(gè)過程當(dāng)作一個(gè)整體來對待,要么全部執(zhí)行完成,要么一點(diǎn)也不執(zhí)行.]

事務(wù)的特性:原子性/一致性/隔離性/持久性[ACID]

外鍵:用于保證數(shù)據(jù)一致性的策略.

索引:是對數(shù)據(jù)表中的一列或多列進(jìn)行排序的一種結(jié)構(gòu).可以快速定位查找.

修改數(shù)據(jù)表存儲(chǔ)引擎的方法:

1.default-storage-engine= myisam

2.通過創(chuàng)建數(shù)據(jù)表時(shí)設(shè)定:

create table t_name(

...

) engine=myisam;

3.alter table t_name engine [=] myisam;