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

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

概述

這篇文章主要針對剛?cè)腴T的開發(fā)，一般半個小時是可以學完的，當然要理解的話估計不止30分鐘，對于初學者來說只需滿足自己需求可以增刪改查等簡易的維護即可。

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

一、開始使用

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

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

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

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

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

鍵(key):表中用來辨認某個特定的人\物的辦法,鍵的值在當前列中具有唯一性。

二、登錄MySQL

mysql-h-u用戶名-p

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

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

對于表的操作需要先進入庫use庫名;

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

createdatabasesamp_dbcharactersetutf8;

dropdatabasesamp_db;--刪除庫名為samp_db的庫

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

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

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

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

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

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

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

--如果數(shù)據(jù)庫中存在user_accounts表，就把它從數(shù)據(jù)庫中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'手機',

`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：默認值；

PRIMARYKEY：主鍵；

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

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

CHARACTERSETname：指定一個字符集；

COMMENT：對表或者字段表明；

五、增刪改查

1、SELECT

SELECT語句用于從表當選取數(shù)據(jù)。

語法：SELECT列名稱FROM表名稱

語法：SELECT*FROM表名稱

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

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

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

SELECTLastNameFROMPersons

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

SELECT*FROMuserswhereid=3limit1

--結(jié)果集中會自動去重復(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中英文混合排序最簡單的方法

--ci是caseinsensitive,即大小寫不敏感

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

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

2、UPDATE

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

語法：UPDATE表名稱SET列名稱=新值WHERE列名稱=某值

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

updateusersetname=(selectnamefromuser1whereuser1.id=1)

whereid=(='小蘇');

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

UPDATE`orders`settitle='這里是標題'WHEREid=1;

3、INSERT

INSERTINTO語句用于向表格中插入新的行。

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

語法：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實現(xiàn)將一個表的數(shù)據(jù)插入到另外一個表的代碼

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

--INSERTINTO目標表(字段1,字段2,...)SELECT字段1,字段2,...FROM來源表;

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

--向表charger插入一條數(shù)據(jù)，已存在就對表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語句用于刪除表中的行。

語法：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ī)范。

語法：SELECT列名稱FROM表名稱WHERE列運算符值

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

SELECT*FROMPersonsWHEREYear>1965

六、操作符

1、AND和OR

AND-如果第一個條件和第二個條件都成立；

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

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來顯示所有姓為"Carter"并且名為"Thomas"的人：

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

1.2、OR

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

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

2、ORDERBY

語句默認按照升序?qū)τ涗涍M行排序。

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

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

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

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

SELECTCompany,OrderNumberFROMOrdersORDERBYCompany

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

SELECTCompany,OrderNumberFROMOrdersORDERBYCompanyDESC

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

SELECTCompany,OrderNumberFROMOrdersORDERBYCompanyDESC,OrderNumberASC

3、IN

IN-操作符允許我們在WHERE子句中規(guī)定多個值。

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

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

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

4、NOT

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

SELECTvend_id,prod_nameFROMProductsWHERENOTvend_id='DLL01'ORDERBYprod_name;

5、UNION

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

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

SELECTE_NameFROMEmployees_ChinaUNIONSELECTE_NameFROMEmployees_USA

--列出meeting表中的pic_url，

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

--按更新時間排序

SELECTid,pic_urlFROMmeetingUNIONALLSELECTid,number_stationASpic_urlFROMstationORDERBYupdate_at;

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

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

6、AS

as-可理解為：用作、當成，作為；別名

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

語法：selectcolumn_1as列1,column_2as列2fromtableas表

SELECT*FROMEmployeeASemp

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

--當你命名一個表之后，你可以在下面用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ù)兩個或多個表中的列之間的關(guān)系，從這些表中查詢數(shù)據(jù)。

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

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

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

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

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

SELECTPersons.LastName,Persons.FirstName,Orders.OrderNo

FROMPersons

INNERJOINOrders

ONPersons.Id_P=Orders.Id_P

ORDERBYPersons.LastName;

七、SQL函數(shù)

1、COUNT

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

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

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

--"ISNOTNULL"是"這個欄位不是空白"的意思。

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值不包括在計算中。

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

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

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

SELECTMAX(OrderPrice)ASLargestOrderPriceFROMOrders

3、round

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

語法：SELECTROUND(column_name,decimals)FROMtable_name

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

SELECTProductName,ROUND(UnitPrice,0)asUnitPriceFROMProducts

4、format

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

語法：SELECTFORMAT(column_name,format)FROMtable_name

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

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

5、ucase

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

語法：SELECTUCASE(column_name)FROMtable_name

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

SELECTUCASE(LastName)asLastName,FirstNameFROMPersons

6、lcase

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

語法：SELECTLCASE(column_name)FROMtable_name

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

SELECTLCASE(LastName)asLastName,FirstNameFROMPersons

八、觸發(fā)器

語法：createtrigger{before|after}#之前或者之后出發(fā)insert|update|delete#指明了激活觸發(fā)程序的語句的類型on#操作哪張表foreachrow#觸發(fā)器的執(zhí)行間隔，foreachrow通知觸發(fā)器每隔一行執(zhí)行一次動作，而不是對整個表執(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é)束符號

OLD和NEW不辨別大小寫

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

OLD用OLD.col_name來引用更新前的某一行的列

九、添加索引

1、普通索引(INDEX)

語法：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)立表的時候同時創(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)

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

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

ALTERTABLE`user`ADDPRIMARYkey(id);

3、唯一索引(UNIQUE)

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

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

ALTERTABLE`user`ADDUNIQUE(creattime);

4、全文索引(FULLTEXT)

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

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

ALTERTABLE`user`ADDFULLTEXT(description);

5、添加多列索引

語法：ALTERTABLEtable_nameADDINDEXindex_name(column1,column2,column3)

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

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

6、建立索引的機會

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

MySQL只對，>=，BETWEEN，IN使用索引

某些時候的LIKE也會使用索引。

在LIKE以通配符%和_開頭作查詢時，MySQL不會使用索引。

--此時就需要對city和age建立索引，

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

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

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

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

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

1、添加列

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

例如:

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

altertablestudentsaddaddresschar(60);

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

altertablestudentsaddbirthdaydateafterage;

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

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

2、修改列

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

--將表tel列改名為telphone:

altertablestudentschangeteltelphonechar(13)default"-";

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

altertablestudentschangenamenamechar(16)notnull;

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

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

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

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

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

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

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

3、刪除列

語法：altertable表名drop列名稱;

--刪除表students中的birthday列:

altertablestudentsdropbirthday;

4、重命名表

語法：altertable表名rename新表名;

--重命名students表為workmates:

altertablestudentsrenameworkmates;

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

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

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

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

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

deletef