MYSQL資料庫知識點總結
一、SQL語句篇
*特別說明:FILED代表資料表字段,CONDITIONS代表where之後的條件,TABLENAME代表資料表名 []中括號內的內容代表 可有可無。
建立資料庫
create database DATABASE;
刪除資料庫
drop database DATABASE
建立資料表
create tableTABLENAME(
`id` int unsigned auto_increment primary key, //把id設定為主鍵,資料型別是無符號int型,自動遞增
//注:unsigned 只能緊跟在資料型別 後,順序不能亂 unsigned相當於去掉數字負數部分 例:tinyint 預設取值範圍 -128到127 unsigned 後 取值範圍變 0到 255
`userid` int unsigned references FOREIGNTABLE(FOREIGNFILED), //把userid設定為無符號整型,並設定成外來鍵。關聯主鍵表(FOREIGNTABLE)中的主鍵(FOREIGNFILED)
`imgid` int unsigned not null default 1 comment '圖片ID', //把imgid 設定成無符號整數 且不為空 預設值為1 註釋說明為 “圖片ID”
foreign key(imgid)references FOREIGNTABLE(FOREIGNFILED) //設定 imgid 為外來鍵。 關聯主鍵表(FOREIGN_TABLE)中的主鍵(FOREIGN_FILED) (下同)
)engine =MYISAM default charset =utf8 collate =utf8_general_ci //設定資料表引擎為MYISAM ,預設字符集為utf8 且字符集排序規則為 utf8_general_ci
刪除資料表
drop table if exists TABLENAME;
新增欄位
alter tableTABLENAME add colum FIELD DATATYPE ATTRIBUTES //DATATYPE :欄位資料型別 ATTRIBUTES欄位屬性
eg: alter table test ADD COLUMN ceshi varchar(50) not null default '' comment '測試欄位'
刪除欄位
alter tableTABLENAME drop colum FIELD ;
eg:alter table test DROP COLUMN ceshi;
修改欄位屬性
altertable TABENAME modify FIELD char(10) default 0 COMMENT "這是整數";
修改欄位名稱
alter tableTABENAME change FIELD mytest int unsigned not null default 1;
新增預設值
eg: alter tabletest alter column user_idset default 1;
刪除預設值
alter table TABENAME alter column FIELD drop default ;
設定主鍵
alter tableTABENAME add primary key (FIELD1 [ ,FIELD2,.. ] );
刪除主鍵
alter tableTABLENAMEdrop primary key; //注意:如果表裡的主鍵是具有自增長屬性的;那麼直接刪除是會報錯的
新增唯一索引
alter tableTABLENAME add constraint KEY_NAME unique (FIELD); //KEY_NAME :索引名稱 (下同)
新增外來鍵索引
alter table TABLENAME add constraint KEY_NAME foreign key (FIELD) references FOREIGN_TABLE(FOREIGN_FIELD) ;
刪除索引(外來鍵、唯一)
alter tableTABLENAME drop index KEY_NAME
或
alter tableTABLENAME drop key KEY_NAME
檢查約束 :注意,MYSQL 目前並不支援check檢查約束。可以右一下方法,設定欄位為列舉值
eg: alter tableTABENAMEmodify sexENUM ("男","女");
修改表的儲存引擎
alter tableTABENAMEengine =INNODB
修改資料表的自增長值
altertable TABENAME AUTO_INCREMENT=100;
新增資料
insert [ into ] TABLENAME (FIELD1,FELD2,...) values(值1,值2,值3,...) ,(值1,值2,值3,...),...
修改資料
update TABLENAME set FILED1=值1,FEILD2=值2,.... where CONDITISONS
刪除資料
delete fromTABLENAME [ where CONDITIONS ];
清空資料
truncate table TABLENAME; 自增ID會重置。
查詢資料表所有
select* from TABLENAME
統計查詢(查詢結果集中的資料條數)
selectcount (FIELD) from TABLENAME [ where CONDITIONS]
查詢某個欄位的最大、最小、平均、求和的值
selectmax (FIELD) from TABLENAME [where CONDITIONS];
selectmin (FIELD) from TABLENAME [where CONDITIONS];
selectavg (FIELD) from TABLENAME [where CONDITIONS];
selectsum (FIELD) from TABLENAME [where CONDITIONS];
排序查詢
select * from TABLENAME [ where CONDITIONS]order by FIELD ; 從小到大排
select * from TABLENAME [ where CONDITIONS] order by FIELDdesc ; 從大到小排
限制查詢
select * from TABLENAME [ where CONDITIONS] [ order by FIELD ]limit START,NUM //START代表資料集的開始位置,0為第一條資料的位置,依次往後為資料的位置。NUM代表限制資料集的資料條數。至少1條。
去重查詢
selectdistinct FILED1,FIELD2,... from TABLENAME [ where CONDITIONS] [ order by FIELD ] [ limit START,NUM ] //代表FIELD1,FIELD2,....所有欄位都重複時,才去除重複的資料條。
分組查詢 ( group by FIELD)
示例::select name, first_letter,sum(parent_id) ascid from mm_city where `parent_id` = 18 group by first_letter having cid >50 limit 0,1 //順序不能亂。當有統計欄位別名做條件時,不能用where,只能用 having。
模糊查詢
select * from TABLENAME where FIELDlike '%值%'; // % 代表任意多個任意字元
內連線查詢
selectTABLENAME1 .FIELD1,TABLENAME2.FIELD2,TABLENAME3.FIELD3,... from TABLENAME1
join TBALENAME2 on TABLENAME1.FIELD = TABLENAME2.FIELD
(join TBALENAME3 on TABLENAME1 .FIELD = TABLENAME3.FIELD )
或
(join TBALENAME3 on TABLENAME2.FIELD = TABLENAME3.FIELD )
[ where TABLENAME1.FIELD = 值 ... ] //查詢或作為條件的欄位中,若所有表中任意兩張表都有該欄位,則必須要指明資料表。即在欄位前用 表名連上點(.)
外連線
左聯接:left join ;查詢出來的資料,若有空值,則以在left join前面的表中的資料條數為準
select TABLENAME1 .FIELD1,TABLENAME2.FIELD2,TABLENAME3.FIELD3,... from TABLENAME1
leftjoin TBALENAME2 on TABLENAME1.FIELD =TABLENAME2.FIELD
(left join TBALENAME3 on TABLENAME1 .FIELD =TABLENAME3 .FIELD )
或
(left join TBALENAME3 on TABLENAME2.FIELD = TABLENAME3.FIELD )
[ where TABLENAME1.FIELD = 值 ... ] //查詢或作為條件的欄位中,若所有表中任意兩張表都有該欄位,則必須要指明資料表。即在欄位前用 表名連上點(.)
右聯接:rightjoin ;查詢出來的資料,若有空值,則以在left join後面的表中的資料條數為準
select TABLENAME1 .FIELD1,TABLENAME2.FIELD2,TABLENAME3.FIELD3,... from TABLENAME1
rightjoin TBALENAME2 on TABLENAME1.FIELD = TABLENAME2.FIELD
(right join TBALENAME3 on TABLENAME1 .FIELD = TABLENAME3 .FIELD )
或
(right join TBALENAME3 on TABLENAME2.FIELD = TABLENAME3.FIELD )
[ where TABLENAME1.FIELD = 值 ... ] //查詢或作為條件的欄位中,若所有表中任意兩張表都有該欄位,則必須要指明資料表。即在欄位前用 表名連上點(.)
常用資料庫函式
LENGTH:返回字串或列的資料的長度
e g: select length(city) as citylen from Demo
lower/upper: 返回字串的小寫/大寫
eg: select UPPER(account) from admininfo;
REPLACE :替換字串
eg:select REPLACE('SQL SERVER','SQL','sql')結果是'sql SERVER'
POWER()取數值的冪值
eg:select POWER(5,3)結果是125
ABS返回絕對值
eg:select ABS(-99)結果是99
ROUND根據指定精度返回數值的四捨五入
eg:select ROUND(3.1415926,3) 結果是3.142
資料庫物件
建立儲存過程
drop procedure if exists pr_multi; //如果存在名為pr_multi的儲存過程,則刪掉
create procedurepr_multi(ou tc int ,a int , b int ) //建立名為pr_multi的儲存過程(函式),第一個為資料型別int的輸出引數,第二、三個分別為資料型別int的輸入引數
begin//過程體開始標記
if a is null THEN //判斷 a 引數是否為空,若果為空,
set a=10; //給 a賦值10
end if;
if b is null THEN //判斷 b 引數是否為空,若果為空,
set b=20; //給 b 賦值20
end if;
set c=a*b; //將引數c設定為 a 和 b 的乘積
end //過程體結束標記
call pr_multi(@name,5,3); //呼叫儲存過程pr_multi 用變數 name 接收輸出引數 c ,給引數 a 傳值 5,b傳值 3
select @name //查詢變數name的值。。 上述結果為 15