半小時(shí)MySQL基礎(chǔ)入門值得收藏

半小時(shí)MySQL基礎(chǔ)入門，值得收藏

概述

這篇文章主要針對(duì)剛?cè)腴T的開(kāi)發(fā)，一般半個(gè)小時(shí)是可以學(xué)完的，當(dāng)然要理解的話估計(jì)不止30分鐘，對(duì)于初學(xué)者來(lái)說(shuō)只需滿足自己需求可以增刪改查等簡(jiǎn)易的維護(hù)即可。

下面介紹下MySQL的根底方面內(nèi)容。

一、開(kāi)始使用

MySQL為關(guān)系型數(shù)據(jù)庫(kù)(RelationalDatabaseManagementSystem)，一個(gè)關(guān)系型數(shù)據(jù)庫(kù)由一個(gè)或數(shù)個(gè)表格組成,表格一般包括下列：

表頭(header):每一列的名稱;

列(col):具有相同數(shù)據(jù)類型的數(shù)據(jù)的匯合;

行(row):每一行用來(lái)描述某個(gè)人/物的具體信息;

值(value):行的具體信息,每個(gè)值必須與該列的數(shù)據(jù)類型相同;

鍵(key):表中用來(lái)辨認(rèn)某個(gè)特定的人\物的辦法,鍵的值在當(dāng)前列中具有唯一性。

二、登錄MySQL

mysql-h-u用戶名-p

mysql-D所選擇的數(shù)據(jù)庫(kù)名-h主機(jī)名-u用戶名-p

三、創(chuàng)立數(shù)據(jù)庫(kù)

對(duì)于表的操作需要先進(jìn)入庫(kù)use庫(kù)名;

--創(chuàng)立一個(gè)名為samp_db的數(shù)據(jù)庫(kù)，數(shù)據(jù)庫(kù)字符編碼指定為utf8

createdatabasesamp_dbcharactersetutf8;

dropdatabasesamp_db;--刪除庫(kù)名為samp_db的庫(kù)

showdatabases;--顯示數(shù)據(jù)庫(kù)列表。

usesamp_db;--選擇創(chuàng)立的數(shù)據(jù)庫(kù)samp_db

showtables;--顯示samp_db下面所有的表名字

describe表名;--顯示數(shù)據(jù)表的結(jié)構(gòu)

deletefrom表名;--清空表中記錄

四、創(chuàng)立數(shù)據(jù)庫(kù)表

使用createtable語(yǔ)句可完成對(duì)表的創(chuàng)立,createtable的常見(jiàn)形式:語(yǔ)法：createtable表名稱(列聲明);

--如果數(shù)據(jù)庫(kù)中存在user_accounts表，就把它從數(shù)據(jù)庫(kù)中drop掉

DROPTABLEIFEXISTS`user_accounts`;

CREATETABLE`user_accounts`(

`id`int(100)unsignedNOTNULLAUTO_INCREMENTprimarykey,

`password`varchar(32)NOTNULLDEFAULT''COMMENT'用戶密碼',

`reset_password`tinyint(32)NOTNULLDEFAULT0COMMENT'用戶類型：0－不需要重置密碼；1-需要重置密碼',

`mobile`varchar(20)NOTNULLDEFAULT''COMMENT'手機(jī)',

`create_at`timestamp(6)NOTNULLDEFAULTCURRENT_TIMESTAMP(6),

`update_at`timestamp(6)NOTNULLDEFAULTCURRENT_TIMESTAMP(6)ONUPDATECURRENT_TIMESTAMP(6),

--創(chuàng)立唯一索引，不允許重復(fù)

UNIQUEINDEXidx_user_mobile(`mobile`)

)

ENGINE=InnoDBDEFAULTCHARSET=utf8

COMMENT='用戶表信息';

數(shù)據(jù)類型的屬性解釋

NULL：數(shù)據(jù)列可包含NULL值；

NOTNULL：數(shù)據(jù)列不允許包含NULL值；

DEFAULT：默認(rèn)值；

PRIMARYKEY：主鍵；

AUTO_INCREMENT：自動(dòng)遞增，適用于整數(shù)類型；

UNSIGNED：是指數(shù)值類型只能為正數(shù)；

CHARACTERSETname：指定一個(gè)字符集；

COMMENT：對(duì)表或者字段表明；

五、增刪改查

1、SELECT

SELECT語(yǔ)句用于從表當(dāng)選取數(shù)據(jù)。

語(yǔ)法：SELECT列名稱FROM表名稱

語(yǔ)法：SELECT*FROM表名稱

--表station取個(gè)別名叫s，表station中不包含字段id=13或者14的，并且id不等于4的查詢出來(lái)，只顯示id

SELECTs.idfromstationsWHEREidin(13,14)andidnotin(4);

--從表Persons選取LastName列的數(shù)據(jù)

SELECTLastNameFROMPersons

--從表users選取id=3的數(shù)據(jù)，并只拉一條數(shù)據(jù)(據(jù)說(shuō)能優(yōu)化性能)

SELECT*FROMuserswhereid=3limit1

--結(jié)果集中會(huì)自動(dòng)去重復(fù)數(shù)據(jù)

SELECTDISTINCTCompanyFROMOrders

--表Persons字段Id_P等于Orders字段Id_P的值，

--結(jié)果集顯示Persons表的LastName、FirstName字段，Orders表的OrderNo字段

SELECTp.LastName,p.FirstName,o.OrderNoFROMPersonsp,OrdersoWHEREp.Id_P=o.Id_P

--gbk和utf8中英文混合排序最簡(jiǎn)單的方法

--ci是caseinsensitive,即大小寫(xiě)不敏感

SELECTtag,COUNT(tag)fromnewsGROUPBYtagorderbyconvert(tagusinggbk)collategbk_chinese_ci;

SELECTtag,COUNT(tag)fromnewsGROUPBYtagorderbyconvert(tagusingutf8)collateutf8_unicode_ci;

2、UPDATE

Update語(yǔ)句用于修改表中的數(shù)據(jù)。

語(yǔ)法：UPDATE表名稱SET列名稱=新值WHERE列名稱=某值

--update語(yǔ)句設(shè)置字段值為另一個(gè)結(jié)果取出來(lái)的字段

updateusersetname=(selectnamefromuser1whereuser1.id=1)

whereid=(='小蘇');

--更新表orders中id=1的那一行數(shù)據(jù)更新它的title字段

UPDATE`orders`settitle='這里是標(biāo)題'WHEREid=1;

3、INSERT

INSERTINTO語(yǔ)句用于向表格中插入新的行。

語(yǔ)法：INSERTINTO表名稱VALUES(值1,值2,....)

語(yǔ)法：INSERTINTO表名稱(列1,列2,...)VALUES(值1,值2,....)

--向表Persons插入一條字段LastName=JSLite字段Address=shanghai

INSERTINTOPersons(LastName,Address)VALUES('JSLite','shanghai');

--向表meeting插入字段a=1和字段b=2

INSERTINTOmeetingSETa=1,b=2;

--SQL實(shí)現(xiàn)將一個(gè)表的數(shù)據(jù)插入到另外一個(gè)表的代碼

--如果只希望導(dǎo)入指定字段，可以用這種辦法：

--INSERTINTO目標(biāo)表(字段1,字段2,...)SELECT字段1,字段2,...FROM來(lái)源表;

INSERTINTOorders(user_account_id,title)SELECTm.user_id,m.titleFROMmeetingmwherem.id=1;

--向表charger插入一條數(shù)據(jù)，已存在就對(duì)表charger更新`type`,`update_at`字段；

INSERTINTO`charger`(`id`,`type`,`create_at`,`update_at`)VALUES(3,2,'2022-05-1811:06:17','2022-05-1811:06:17')ONDUPLICATEKEYUPDATE`id`=VALUES(`id`),`type`=VALUES(`type`),`update_at`=VALUES(`update_at`);

4、DELETE

DELETE語(yǔ)句用于刪除表中的行。

語(yǔ)法：DELETEFROM表名稱WHERE列名稱=值

--在不刪除table_name表的情況下刪除所有的行，清空表。

DELETEFROMtable_name

--或者

DELETE*FROMtable_name

--刪除Person表字段LastName='JSLite'

DELETEFROMPersonWHERELastName='JSLite'

--刪除表meetingid為2和3的兩條數(shù)據(jù)

DELETEfrommeetingwhereidin(2,3);

5、WHERE

WHERE子句用于規(guī)定選擇的規(guī)范。

語(yǔ)法：SELECT列名稱FROM表名稱WHERE列運(yùn)算符值

--從表Persons當(dāng)選出Year字段大于1965的數(shù)據(jù)

SELECT*FROMPersonsWHEREYear>1965

六、操作符

1、AND和OR

AND-如果第一個(gè)條件和第二個(gè)條件都成立；

OR-如果第一個(gè)條件和第二個(gè)條件中只要有一個(gè)成立；

1.1、AND

--刪除meeting表字段

--id=2并且user_id=5的數(shù)據(jù)和

--id=3并且user_id=6的數(shù)據(jù)

DELETEfrommeetingwhereidin(2,3)anduser_idin(5,6);

--使用AND來(lái)顯示所有姓為"Carter"并且名為"Thomas"的人：

SELECT*FROMPersonsWHEREFirstName='Thomas'ANDLastName='Carter';

1.2、OR

--使用OR來(lái)顯示所有姓為"Carter"或者名為"Thomas"的人：

SELECT*FROMPersonsWHEREfirstname='Thomas'ORlastname='Carter'

2、ORDERBY

語(yǔ)句默認(rèn)按照升序?qū)τ涗涍M(jìn)行排序。

ORDERBY-語(yǔ)句用于根據(jù)指定的列對(duì)結(jié)果集進(jìn)行排序。

DESC-按照降序?qū)τ涗涍M(jìn)行排序。

ASC-按照順序?qū)τ涗涍M(jìn)行排序。

--Company在表Orders中為字母，那么會(huì)以字母順序顯示公司名稱

SELECTCompany,OrderNumberFROMOrdersORDERBYCompany

--后面跟上DESC那么為降序顯示

SELECTCompany,OrderNumberFROMOrdersORDERBYCompanyDESC

--Company以降序顯示公司名稱，并OrderNumber以順序顯示

SELECTCompany,OrderNumberFROMOrdersORDERBYCompanyDESC,OrderNumberASC

3、IN

IN-操作符允許我們?cè)赪HERE子句中規(guī)定多個(gè)值。

IN-操作符用來(lái)指定范圍，范圍中的每一條，都進(jìn)行匹配。IN取值規(guī)律，由逗號(hào)分割，全部放置括號(hào)中。語(yǔ)法：SELECT"字段名"FROM"表格名"WHERE"字段名"IN('值一','值二',...);

--從表Persons選取字段LastName等于Adams、Carter

SELECT*FROMPersonsWHERELastNameIN('Adams','Carter')

4、NOT

NOT-操作符總是與其他操作符一起使用，用在要過(guò)濾的前面。

SELECTvend_id,prod_nameFROMProductsWHERENOTvend_id='DLL01'ORDERBYprod_name;

5、UNION

UNION-操作符用于合并兩個(gè)或多個(gè)SELECT語(yǔ)句的結(jié)果集。

--列出所有在中國(guó)表〔Employees_China〕和美國(guó)〔Employees_USA〕的不同的雇員名

SELECTE_NameFROMEmployees_ChinaUNIONSELECTE_NameFROMEmployees_USA

--列出meeting表中的pic_url，

--station表中的number_station別名設(shè)置成pic_url防止字段不一樣報(bào)錯(cuò)

--按更新時(shí)間排序

SELECTid,pic_urlFROMmeetingUNIONALLSELECTid,number_stationASpic_urlFROMstationORDERBYupdate_at;

--通過(guò)UNION語(yǔ)法同時(shí)查詢了products表和comments表的總記錄數(shù)，并且按照count排序

SELECT'product'AStype,count(*)ascountFROM`products`unionselect'comment'astype,count(*)ascountFROM`comments`orderbycount;

6、AS

as-可理解為：用作、當(dāng)成，作為；別名

一般是重命名列名或者表名。

語(yǔ)法：selectcolumn_1as列1,column_2as列2fromtableas表

SELECT*FROMEmployeeASemp

--這句意思是查找所有Employee表里面的數(shù)據(jù)，并把Employee表格命名為emp。

--當(dāng)你命名一個(gè)表之后，你可以在下面用emp代替Employee.

--示例SELECT*FROMemp.

SELECTMAX(OrderPrice)ASLargestOrderPriceFROMOrders

--列出表Orders字段OrderPrice列最大值，

--結(jié)果集列不顯示OrderPrice顯示LargestOrderPrice

--顯示表users_profile中的name列

SELECTfrom(SELECT*fromusers_profilea)ASt;

--表user_accounts命名別名ua，表users_profile命名別名up

--滿足條件表user_accounts字段id等于表users_profile字段user_id

--結(jié)果集只顯示mobile、name兩列

SELECTua.mobile,FROMuser_accountsasuaINNERJOINusers_profileasupONua.id=up.user_id;

7、JOIN

用于根據(jù)兩個(gè)或多個(gè)表中的列之間的關(guān)系，從這些表中查詢數(shù)據(jù)。

JOIN:如果表中有至少一個(gè)匹配，那么返回行

INNERJOIN:在表中存在至少一個(gè)匹配時(shí)，INNERJOIN關(guān)鍵字返回行。

LEFTJOIN:即使右表中沒(méi)有匹配，也從左表返回所有的行

RIGHTJOIN:即使左表中沒(méi)有匹配，也從右表返回所有的行

FULLJOIN:只要其中一個(gè)表中存在匹配，就返回行(MySQL是不支持的，通過(guò)LEFTJOIN+UNION+RIGHTJOIN的方式來(lái)實(shí)現(xiàn))

SELECTPersons.LastName,Persons.FirstName,Orders.OrderNo

FROMPersons

INNERJOINOrders

ONPersons.Id_P=Orders.Id_P

ORDERBYPersons.LastName;

七、SQL函數(shù)

1、COUNT

COUNT讓我們能夠數(shù)出在表格中有多少筆資料被選出來(lái)。

語(yǔ)法：SELECTCOUNT("字段名")FROM"表格名";

--表Store_Information有幾筆store_name欄不是空白的資料。

--"ISNOTNULL"是"這個(gè)欄位不是空白"的意思。

SELECTCOUNT(Store_Name)FROMStore_InformationWHEREStore_NameISNOTNULL;

--獲取Persons表的總數(shù)

SELECTCOUNT(1)AStotalsFROMPersons;

--獲取表station字段user_id相同的總數(shù)

selectuser_id,count(*)astotalsfromstationgroupbyuser_id;

2、MAX

MAX函數(shù)返回一列中的最大值。NULL值不包括在計(jì)算中。

語(yǔ)法：SELECTMAX("字段名")FROM"表格名"

--列出表Orders字段OrderPrice列最大值，

--結(jié)果集列不顯示OrderPrice顯示LargestOrderPrice

SELECTMAX(OrderPrice)ASLargestOrderPriceFROMOrders

3、round

ROUND函數(shù)用于把數(shù)值字段舍入為指定的小數(shù)位數(shù)。

語(yǔ)法：SELECTROUND(column_name,decimals)FROMtable_name

--列知名稱和價(jià)格舍入為最接近的整數(shù)

SELECTProductName,ROUND(UnitPrice,0)asUnitPriceFROMProducts

4、format

FORMAT函數(shù)用于對(duì)字段的顯示進(jìn)行格式化。

語(yǔ)法：SELECTFORMAT(column_name,format)FROMtable_name

--顯示每天日期所對(duì)應(yīng)的名稱和價(jià)格〔日期的顯示格式是"YYYY-MM-DD"〕

SELECTProductName,UnitPrice,FORMAT(Now(),'YYYY-MM-DD')asPerDateFROMProducts

5、ucase

UCASE函數(shù)把字段的值轉(zhuǎn)換為大寫(xiě)。

語(yǔ)法：SELECTUCASE(column_name)FROMtable_name

--選取"LastName"和"FirstName"列的內(nèi)容，然后把"LastName"列轉(zhuǎn)換為大寫(xiě)。

SELECTUCASE(LastName)asLastName,FirstNameFROMPersons

6、lcase

LCASE函數(shù)把字段的值轉(zhuǎn)換為小寫(xiě)。

語(yǔ)法：SELECTLCASE(column_name)FROMtable_name

--選取"LastName"和"FirstName"列的內(nèi)容，然后把"LastName"列轉(zhuǎn)換為小寫(xiě)

SELECTLCASE(LastName)asLastName,FirstNameFROMPersons

八、觸發(fā)器

語(yǔ)法：createtrigger{before|after}#之前或者之后出發(fā)insert|update|delete#指明了激活觸發(fā)程序的語(yǔ)句的類型on#操作哪張表foreachrow#觸發(fā)器的執(zhí)行間隔，foreachrow通知觸發(fā)器每隔一行執(zhí)行一次動(dòng)作，而不是對(duì)整個(gè)表執(zhí)行一次。

delimiter$

CREATETRIGGERset_userdateBEFOREINSERT

on`message`

forEACHROW

BEGIN

set@statu=new.status;--聲明復(fù)制變量statu

if@statu=0then--判斷statu是否等于0

UPDATE`user_accounts`SETstatus=1WHEREopenid=NEW.openid;

endif;

END

$

DELIMITER;--恢復(fù)結(jié)束符號(hào)

OLD和NEW不辨別大小寫(xiě)

NEW用NEW.col_name，沒(méi)有舊行。在DELETE觸發(fā)程序中，僅能使用OLD.col_name，沒(méi)有新行。

OLD用OLD.col_name來(lái)引用更新前的某一行的列

九、添加索引

1、普通索引(INDEX)

語(yǔ)法：ALTERTABLE表名字ADDINDEX索引名字(字段名字)

--–直接創(chuàng)立索引

CREATEINDEXindex_userONuser(title)

--–修改表結(jié)構(gòu)的方式添加索引

ALTERTABLEtable_nameADDINDEXindex_nameON(column(length))

--給user表中的name字段添加普通索引(INDEX)

ALTERTABLE`user`ADDINDEXindex_name(name)

--–創(chuàng)立表的時(shí)候同時(shí)創(chuàng)立索引

CREATETABLE`user`(

`id`int(11)NOTNULLAUTO_INCREMENT,

`title`char(255)CHARACTERSETutf8COLLATEutf8_general_ciNOTNULL,

`content`textCHARACTERSETutf8COLLATEutf8_general_ciNULL,

`time`int(10)NULLDEFAULTNULL,

PRIMARYKEY(`id`),

INDEXindex_name(title(length))

)

--–刪除索引

DROPINDEXindex_nameONtable

2、主鍵索引(PRIMARYkey)

語(yǔ)法：ALTERTABLE表名字ADDPRIMARYKEY(字段名字)

--給user表中的id字段添加主鍵索引(PRIMARYkey)

ALTERTABLE`user`ADDPRIMARYkey(id);

3、唯一索引(UNIQUE)

語(yǔ)法：ALTERTABLE表名字ADDUNIQUE(字段名字)

--給user表中的creattime字段添加唯一索引(UNIQUE)

ALTERTABLE`user`ADDUNIQUE(creattime);

4、全文索引(FULLTEXT)

語(yǔ)法：ALTERTABLE表名字ADDFULLTEXT(字段名字)

--給user表中的description字段添加全文索引(FULLTEXT)

ALTERTABLE`user`ADDFULLTEXT(description);

5、添加多列索引

語(yǔ)法：ALTERTABLEtable_nameADDINDEXindex_name(column1,column2,column3)

--給user表中的name、city、age字段添加名字為name_city_age的普通索引(INDEX)

ALTERTABLEuserADDINDEXname_city_age(name(10),city,age);

6、建立索引的機(jī)會(huì)

在WHERE和JOIN中出現(xiàn)的列需要建立索引，但也不完全如此：

MySQL只對(duì)，>=，BETWEEN，IN使用索引

某些時(shí)候的LIKE也會(huì)使用索引。

在LIKE以通配符%和_開(kāi)頭作查詢時(shí)，MySQL不會(huì)使用索引。

--此時(shí)就需要對(duì)city和age建立索引，

--由于mytable表的userame也出現(xiàn)在了JOIN子句中，也有對(duì)它建立索引的必要。

SELECTt.NameFROMmytabletLEFTJOINmytablemONt.Name=m.usernameWHEREm.age=20ANDm.city='上海';

SELECT*FROMmytableWHEREusernamelike'admin%';--而下句就不會(huì)使用：

SELECT*FROMmytableWHERENamelike'%admin';--因此，在使用LIKE時(shí)應(yīng)注意以上的區(qū)別。

十、創(chuàng)立后表的修改

1、添加列

語(yǔ)法：altertable表名add列名列數(shù)據(jù)類型[after插入位置];

例如:

--在表students的最后追加列address:

altertablestudentsaddaddresschar(60);

--在名為age的列后插入列birthday:

altertablestudentsaddbirthdaydateafterage;

--在名為number_people的列后插入列weeks:

altertablestudentsaddcolumn`weeks`varchar(5)notnulldefault""after`number_people`;

2、修改列

語(yǔ)法：altertable表名change列名稱列新名稱新數(shù)據(jù)類型;

--將表tel列改名為telphone:

altertablestudentschangeteltelphonechar(13)default"-";

--將name列的數(shù)據(jù)類型改為char(16):

altertablestudentschangenamenamechar(16)notnull;

--修改COMMENT前面必須得有類型屬性

altertablestudentschangenamenamechar(16)COMMENT'這里是名字';

--修改列屬性的時(shí)候倡議使用modify,不需要重建表

--change用于修改列名字，這個(gè)需要重建表

altertablemeetingmodify`weeks`varchar(20)NOTNULLDEFAULT''COMMENT'開(kāi)放日期周一到周日：0~6，間隔用英文逗號(hào)隔開(kāi)';

--`user`表的`id`列，修改成字符串類型長(zhǎng)度50，不能為空，`FIRST`放在第一列的位置

altertable`user`modifyCOLUMN`id`varchar(50)NOTNULLFIRST;

3、刪除列

語(yǔ)法：altertable表名drop列名稱;

--刪除表students中的birthday列:

altertablestudentsdropbirthday;

4、重命名表

語(yǔ)法：altertable表名rename新表名;

--重命名students表為workmates:

altertablestudentsrenameworkmates;

5、清空表數(shù)據(jù)

辦法一：deletefrom表名;辦法二：truncatetable"表名";

DELETE:1.DML語(yǔ)言;2.可以回退;3.可以有條件的刪除;

TRUNCATE:1.DDL語(yǔ)言;2.無(wú)法回退;3.默認(rèn)所有的表內(nèi)容都刪除;4.刪除速度比delete快。

--清空表為workmates里面的數(shù)據(jù)，不刪除表。

deletef