MySQL數(shù)據(jù)庫入門知識點

一、寫在前面黑窗口啟動mysql服務(wù)：netstartmysql

登錄：mysql-uroot-p

備份數(shù)據(jù)庫:mysqldump-uroot-p1234store28>g:/1.sql

建議使用Navicat工具操作MySQL二、SQL語句的分類DDL：數(shù)據(jù)定義語言

create、drop、alter

DML：數(shù)據(jù)操作語句

insert、delete、update、truncate

DQL：數(shù)據(jù)查詢語言：

select、show三、操作數(shù)據(jù)庫①查詢所有數(shù)據(jù)庫

showdatabases;

查看活躍連接

showprocesslist;

②創(chuàng)建數(shù)據(jù)庫

createdatabaseblogdefaultcharactersetutf8;

③查看數(shù)據(jù)庫的默認(rèn)字符集

showcreatedatabaseblog;

④刪除數(shù)據(jù)庫

dropdatabaseblog;

⑤修改數(shù)據(jù)庫

alterdatabaseblogdefaultcharactersetgbk;

⑥選擇數(shù)據(jù)庫

useblog;四、操作表①查看所有表

showtables;

②創(chuàng)建表

createtablestudent(sidint,snamevarchar(20));

③查看表結(jié)構(gòu)

descstudent;

④刪除表

droptablestudent;

⑤修改表

添加字段

altertablestudentaddsgendervarchar(2);

刪除字段

altertablestudentdropsgender;

修改字段類型

altertablestudentmodifysgendervarchar(100);

修改字段名稱

altertablestudentchangesgendergendervarchar(2);

修改表名稱

altertablestudentrenametoteacher;五、增刪改數(shù)據(jù)①增加數(shù)據(jù)

insertintostudentvalues(1,'張三','男',20);

insertintostudent(id,name)values(2,'李四');

②修改數(shù)據(jù)

updatestudentsetgender='女';

updatestudentsetgender='男'whereid=1;

updatestudentsetgender='男',age=30whereid=2;

③刪除數(shù)據(jù)

deletefromstudent;

deletefromstudentwhereid=2;

truncatetablestudent;

刪除對比：

deletefrom可以帶條件刪除；只能刪除表的數(shù)據(jù)，不能刪除表的約束；可以回滾（事務(wù)）

truncatetable不能帶條件刪除；既可以刪除表的數(shù)據(jù)，也可以刪除表的約束；不能回滾六、查詢數(shù)據(jù)（重點）①查詢時添加常量列

selectid,name,gender,age,'java學(xué)習(xí)'as'java'

fromstudent;

查詢時合并列（合并列只能合并數(shù)值類型的字段）

selectid,name,(servlet+jsp)as'總成績'fromstudent;

查詢時去除重復(fù)記錄

selectdistinctgenderfromstudent;

selectdistinct(gender)fromstudent;

②條件查詢<>(不等于)

select*fromstudentwherejspbetween75and90;

select*fromstudentwheregender<>'男';

判空條件(null空字符串)

select*fromstudentwhereaddressisnulloraddress='';

select*fromstudentwhereaddressisnotnullandaddress<>'';

模糊條件：like

%表示任意個字符

_表示一個字符

③聚合查詢

常用的聚合函數(shù)：

sum()

avg()

max()

min()

count()

注意：count（）函數(shù)統(tǒng)計的數(shù)量不包含null的數(shù)據(jù)

④分頁查詢（開始行,行數(shù)）0開始

select*fromstudentlimit(當(dāng)前頁-1)*每頁顯示條數(shù),每頁顯示條數(shù);

⑤查詢排序

select*fromstudentorderbyservletasc,jspdesc;

⑥分組查詢

selectgender,count(*)fromstudentgroupbygender;

⑦分組查詢后篩選（分組之前條件使用where關(guān)鍵字，分組之后條件使用having關(guān)鍵字）

selectgender,count(*)fromstudentgroupbygenderhavingcount(*)>2;七、六種約束類型①默認(rèn)值

default

②非空notnull

③唯一unique

④主鍵primarykey非空且唯一

⑤自增長

AUTO_INCREMENT

--自增長，從0開始

zerofill零填充

--刪除：delete不能影響自增長約束，truncate可以影響自增長約束

例：idint(4)zerofillprimarykeyauto_increment,

⑥外鍵

例：constraintemlyee_dept_fkforeignkey(deptid)referencesdept(id)

注意：

1）被約束的表稱為副表，約束別人的表稱為主表，外鍵設(shè)置在副表上。

2）主表的參考字段通用為主鍵。

3）添加數(shù)據(jù)：先添加主表，再添加副表。

級聯(lián)操作：

級聯(lián)修改：onupdatecascade

級聯(lián)刪除：ondeletecascade

constraintemlyee_dept_fkforeignkey(deptid)referencesdept(id)onupdatecascadeondeletecascade八、數(shù)據(jù)庫(表)設(shè)計①設(shè)計原則：設(shè)計的表盡量遵守三大范式。

②三大范式：

第一范式:確保每列的原子性.

第二范式:在第一范式的基礎(chǔ)上,確保表中的每列都和主鍵相關(guān).

第三范式:在第二范式的基礎(chǔ)上,確保每列都和主鍵列直接相關(guān),而不是間接相關(guān).（非主鍵字段不能相互依賴）九、關(guān)聯(lián)查詢(多表查詢)多表查詢規(guī)則：

a.確定查詢哪些表

b.確定哪些哪些字段

c.表與表之間連接條件(規(guī)律：連接條件數(shù)量是表數(shù)量-1)

①

內(nèi)連接查詢

selecte.empname,d.deptnamefromemployeeeinnerjoindeptdone.deptid=d.id;

②

左[外]連接查詢：使用左邊表的數(shù)據(jù)去匹配右邊表的數(shù)據(jù)，如果符合連接條件的結(jié)果則顯示，如果不符合連接條件則顯示null

selectd.deptname,e.empnamefromdeptdleftjoinemployeeeond.id=e.deptid;

③

右[外]連接查詢：使用右邊表的數(shù)據(jù)去匹配左邊表的數(shù)據(jù)，如果符合連接條件的結(jié)果則顯示，如果不符合連接條件則顯示null

selectd.deptname,e.empnamefromemployeeerightjoindeptdond.id=e.deptid;

④

自連接查詢:

selecte.empname,b.empnamefromemployeeeleftouterjoinemployeebone.bossid=b.id;十、UNION和UNIONALL的作用和語法UNION用于合并兩個或多個SELECT語句的結(jié)果集，并消去表中任何重復(fù)行。

UNION內(nèi)部的SELECT語句必須擁有相同數(shù)量的列，列也必須擁有相似的數(shù)據(jù)類型。

同時，每條SELECT語句中的列的順序必須相同.

示例：

SELECTcolumn_nameFROMtable1

UNION

SELECTcolumn_nameFROMtable2

注意：

默認(rèn)地，UNION操作符選取不同的值。如果允許重復(fù)的值，使用UNIONALL。UNIONALL不消除重復(fù)行

另外，UNION結(jié)果集中的列名總是等于UNION中第一個SELECT語句中的列名。

UNION結(jié)果集中的列名總是等于第一個SELECT語句中的列名十一、存儲過程①描述：帶有邏輯的sql語句。

②優(yōu)缺點：

執(zhí)行效率非常快，存儲過程是在數(shù)據(jù)庫的服務(wù)器端執(zhí)行的。

移植性很差，不同數(shù)據(jù)庫的存儲過程是不能移植。

③語法：

--全局變量（內(nèi)置變量）：mysql數(shù)據(jù)庫內(nèi)置的變量（所有連接都起作用）

--查看所有全局變量：showvariables

--查看某個全局變量：select@@變量名

--修改全局變量：set變量名=新值

--character_set_client:mysql服務(wù)器的接收數(shù)據(jù)的編碼

--character_set_results：mysql服務(wù)器輸出數(shù)據(jù)的編碼

--會話變量：只存在于當(dāng)前客戶端與數(shù)據(jù)庫服務(wù)器端的一次連接當(dāng)中。如果連接斷開，那么會話變量全部丟失

--定義會話變量:set@變量=值

--查看會話變量：select@變量

--局部變量：在存儲過程中使用的變量就叫局部變量。只要存儲過程執(zhí)行完畢，局部變量就丟失

④示例：

delimiter$

createprocedurepro_test(outresultVARCHAR(20))

begin

DECLAREscoreintdefault0;

SELECTavg(english)intoscorefromstudent;

ifscore>90then

setresult='優(yōu)秀';

elseifscore>70andscore<=90then

setresult='良好';

else

setresult='一般';

endif;end$CALLpro_test(@result);select@result;

dropprocedurepro_test;十二、觸發(fā)器①描述：當(dāng)操作了某張表時，希望同時觸發(fā)一些動作行為，使用觸發(fā)器完成。

②示例：

createtriggertri_empaddafterinsertonemployeeforeachrow

--當(dāng)往員工表插入一條記錄時

insertintotest_log(content)values('員工表插入了一條記錄');十三、MySQL權(quán)限問題--

mysql數(shù)據(jù)庫，用戶配置:user表

usemysql;

--查看密碼

selectpassword('root');

--修改密碼

updateusersetpassword=password('123456')whereuser='root';

--分配權(quán)限賬戶

grant權(quán)限on數(shù)據(jù)庫.表to'賬戶名'@'賬戶類型'identifiedby'密碼';

權(quán)限：selectinsertdeleteupdatedropcreate/

或,all

舉例：grantselectonday16.employeeto'eric'@'localhost'identifiedby'123456';

--備份（無需登陸）

mysqldump-uroot-pday17>c:/bak.sql

--恢復(fù)（無需登陸）

mysql-uroot-pday17<d:/back.sql十四、MySQL常用函數(shù)version()

返回mysql服務(wù)器的版本

from_unixtime(ts,fmt)

根據(jù)指定的fmt格式，格式化unix時間戳ts

time_format(time,fmt)

依照字符串fmt格式化時間time值

date_format(date,fmt)

依照字符串fmt格式化日期date值

format(x,y)

把x格式化為以逗號隔開的數(shù)字序列，y是結(jié)果的小數(shù)位數(shù)

substring（str,pos）字段,開始位置

substring（str,pos,length）

字段,開始位置,長度n

now()返回當(dāng)前的日期和時間

curdate()

返回當(dāng)前的日期

curtime()

返回當(dāng)前