一、存儲(chǔ)過程
創(chuàng)建過程如下 先改變結(jié)束符號(hào)
delimiter && //表示以&&為結(jié)束符號(hào)
create procedure test(a int)
begin
select * from student where sno=a;
end &&
使用的時(shí)候用 call test(x);? x為int類型 表示帶參存儲(chǔ)過程
begin
declare a int;? //當(dāng)需要定義參數(shù)的時(shí)候在begin里面定義 并且所有語句必須有分號(hào);結(jié)尾 否則報(bào)錯(cuò)
set a=5;
select * from student where sno=a;
二、自定義函數(shù)
創(chuàng)建自定義函數(shù)過程如下 規(guī)則和存儲(chǔ)過程差不多
delimiter &&
create function test5(i int,o int,p int)
returns int
begin? // declare d int; 也可以在begin之后聲明一個(gè)變量
set p=i*o; //同理用set創(chuàng)建函數(shù)體
return p;
end &&
這個(gè)是帶參并且是begin里面有函數(shù)體的函數(shù)
使用的時(shí)候用? select test5(i,o,p) 賦值3個(gè)int即可 執(zhí)行結(jié)果只與函數(shù)體有關(guān)
三、觸發(fā)器
創(chuàng)建觸發(fā)器 首先也是delimiter一下 然后
delimiter &
create trigger test after insert on student for each row? //這里可以有after 和 before兩種觸發(fā)器
update student_h set row=row+1;? //當(dāng)有函數(shù)體的時(shí)候用begin包裹起來
end &
四、數(shù)據(jù)庫所有賬戶
三種類型：create login login_name?? //登錄賬戶 指登錄sql server2008的賬戶 可以擁有多個(gè)user 如下
create user user_name?????????? // 數(shù)據(jù)庫用戶 隸屬于賬戶里面的，可以簡(jiǎn)稱為映射 grant on 表名 to <用戶名,賬號(hào)名> with grant option表示該主體可以向其他主體授予所指定的權(quán)限
? deny? execute on test1 to studnet cascade 同理grant 屬于權(quán)限的一種? cascade=with grant option
? revoke 同理deny 表示收回權(quán)限
各類操作匯總：
數(shù)據(jù)庫操作：
新增：create database test;?
刪除：drop database if exists test1;?
修改：alter database test1 default character set gb2312;
表操作：
一、和表結(jié)構(gòu)之類有關(guān)：
查看：
desc table_name;? Or? show columns from table_name;
新增：
create table test(
a int not null default 0 primary key auto_increment CHECK (a>0),
b char(10) not null,
c datetime，
CONSTRAINT chk_Person CHECK (b什么什么的 AND c 什么什么的)//這種是為了命名約束
//? CHECK (a>0)可以寫在這里
);? //當(dāng)約束作用于兩列以上時(shí)候要寫在最后面
更改(列)：
alter table test? ? //新增行
add column d char(1) not null default 'a' after b;?
//after可以換成first表示設(shè)為第一行(只有這兩種)
alter table test? ?
alter column c char(10); //修改行
[alter column b set default 'hello';]? // 也可以修改默認(rèn)值
alter table test1
? ? drop column b;? //刪除行
alter table test?
? ? change column b e varchar(2) null default '啊';? //同時(shí)改列名和類型(將列名b改為e)
alter table test
modify column b char(5) first;? //只修改數(shù)據(jù)類型 可以使用first或者after修改它的順序
alter table test
rename to test_backup //修改table名字
重命名表：
rename table test_backup to test;
復(fù)制表：
create table test_copy like test; //結(jié)構(gòu)完全復(fù)制
create table test_copy select * from test;? //將test作為結(jié)果集復(fù)制過去 不會(huì)復(fù)制約束和索引等
create table test_copy as select * from test; //同上 可以用括號(hào)將as后面的東西包裹起來
刪除表:
drop table [if exists] test;? //表里的外鍵可以用cascade策略 例如： on delete cascade 表示
在父表上update/delete記錄時(shí)，同步update/delete掉子表的匹配記錄
二、和數(shù)據(jù)有關(guān)：
插入：
insert into test values(1,'潘冠宇','2017-03-18 16:52:00');
insert into test(a,b) values(1,'潘冠宇');? //如果數(shù)據(jù)只插入某些列 那么要在表名后用圓括號(hào)括起來
insert into test set 列名=值,列名=值; //只對(duì)想輸入的列輸入且主鍵非空 not null自動(dòng)賦值但會(huì)有warning
replace into test values(1,'張馨允','2017-03-18 16:52:00');?
//當(dāng)要替換信息的時(shí)候可以用replace更改除主鍵以外的信息
刪除：
delete from test where 列名=值;? //
delete [*] from test 或者 turncate test //均表示刪除所有行? 還有一種從多個(gè)表刪除看書
修改：
update test set j=1.22,g='女' where f=1;? //修改多列的時(shí)候用逗號(hào)
用了ignore關(guān)鍵詞在更新中有錯(cuò)誤也不會(huì)中斷
查詢：
select * from test;
select a,b from test;
{--簡(jiǎn)單Case函數(shù)
case:
CASE sex
WHEN '1' THEN '男'
WHEN '2' THEN '女'
ELSE '其他' END
--Case搜索函數(shù)
CASE WHEN sex = '1' THEN '男'
WHEN sex = '2' THEN '女'
ELSE '其他' END}
select a,case b
when ...? //可以使用上述的case函數(shù) 此為高級(jí)查詢
select a,b from test where a like '我_%';? //%表示匹配任意字符0到任意次數(shù) 下劃線_表示匹配一個(gè)任意字符 當(dāng)列值有_的時(shí)候 需要轉(zhuǎn)義字符#去除下劃線_的特殊作用
regexp:
select * from test where a regexp '我';? //等同于where a like '%我%' 表示含有我字的值
select distinct * from test where g regexp '東莞|北京'; 表查詢帶有東莞和北京的記錄
group by a? //表示通過a中的每個(gè)值來分組
having 字句會(huì)對(duì)分組的過濾 比如count(*)<=3 那么通過group by分組之后的就只取小于等于3的
select a,b from test order by c limit 4,3(limit 3 offset 4); //limit表示限制從第5行開始的3行數(shù)據(jù)
在MySQL中 沒有top n的用法 需要用limit對(duì)數(shù)據(jù)進(jìn)行選取 比如從0開始? limit 0,10
視圖操作：
創(chuàng)建：
create view view_name as select column_name(s) from table_name where condition [with check option] // with check option 用于指定在可更新視圖上的修改都需要滿足select中所指定的限制條件。
修改：? alter view...? //同創(chuàng)建一樣
? ? ? ? // 視圖的操作大多數(shù)和表一樣 在插入數(shù)據(jù)的時(shí)候 當(dāng)有check option的選項(xiàng)時(shí) 那么在插入的時(shí)候必須滿足condition的條件比如：
插入： create view v as select * from test where age > 20 with check option;
mysql> insert into v1(ssex) values(11); //如果在創(chuàng)建的時(shí)候有 with check option
ERROR 1369 (HY000): CHECK OPTION failed 'test1.v1' //那么在插入數(shù)據(jù)不滿足創(chuàng)建的condition的時(shí)候就會(huì)報(bào)錯(cuò)? 并且主表也不會(huì)創(chuàng)建
// 如果沒有加option? 那么數(shù)據(jù)會(huì)插入到主表上 但是視圖不會(huì)挑選這一條行記錄 所有數(shù)據(jù)隨時(shí)更新
刪除：
drop view v1;
更新：
update v1 set a='你好';
索引操作：
創(chuàng)建：
1、 create index index_name on test.test(a,b) [using btree];? //在test數(shù)據(jù)庫的test表對(duì)a和b列創(chuàng)建索引
2、 create table test(
a int not null primary key, //primary key也是一個(gè)索引 是對(duì)本列聲明非空的索引
b char(2),
name char(50),
index index_name(b),? //在創(chuàng)建表的時(shí)候可以同時(shí)創(chuàng)建索引 可以創(chuàng)建多個(gè)索引
constraint foreign key(name) references customers(name));
//外鍵也是一種索引 key通常是index的同義詞
3、 alter table test add [constraint fk_name] foreign key(sno) references student(sno);
//上面這一條是增加外鍵約束 被參照的列必須是主鍵或者unique索引
alter table test add index index_name(a,b); //
查看：
show index from 表名;
刪除：
alter table test drop index index_name,drop paimary key;
//無法直接用? drop index 索引名稱; 那是標(biāo)準(zhǔn)SQL MYSQL不支持
存儲(chǔ)過程操作：
一、存儲(chǔ)過程：
創(chuàng)建：
delimiter &&
create procedure sp_name(in/out/inout cid int,in csex char(1))
//? in/out/inout 表示三種參數(shù)，in表示輸入?yún)?shù)，out表示輸出參數(shù)，inout表示輸入輸出參數(shù)。
begin
update customers set cust_sex=csex where cust_id=cid;
end &&
delimiter ;
事件操作：
一、事件調(diào)度：
查看:
show variables like 'event_scheduler';
select @@event_scheduler;? //兩種方式都可以查看? 結(jié)果為1或者on表示開啟
開啟：
set global event_scheduler=1;
set global event_scheduler=ture;? //經(jīng)測(cè)試 這一條value不可以為true;
二、事件操作：
創(chuàng)建：
1、 delimiter &&
create event if not exists et
on schedule every 1 month //注意這里是 schedule 沒有 【r】??！
// year quarter month day hour minute week second 等等
starts curdate() +interval 1 month
ends '2018-03-30'
do
begin
if year(curdate())<2018 then
insert into t1()
values(1,'你好'); //這個(gè)事件的功能是 每個(gè)月向表t1插入一條數(shù)據(jù)
? end if; 該事件于下個(gè)月開始 并且結(jié)束于2018年3月30號(hào)。
end &&?
2、 delimiter &&
create event t3
on schedule every 10 second
starts curdate() +interval 1 minute
ends '2017-03-21 21:50:00'? //可以精確到秒數(shù)
do
begin
insert into test(number) values(number+1);
end
&&
修改：
alter event et....? //打法和創(chuàng)建一樣
alter event et disable; // 事件關(guān)閉
alter event et enable; //事件再次開啟
alter event et rename to et1；? //事件改名
刪除事件:
drop event if exists et;
觸發(fā)器操作：
一、創(chuàng)建觸發(fā)器(標(biāo)準(zhǔn)):
create triggerigger_name trigger_time trigger_event on tbl_name for each row trigger_stmt
//trigger_time 有兩個(gè)選項(xiàng)分別是 before 和 after 表示觸發(fā)器被觸發(fā)的時(shí)刻
// trigger_event指定激活觸發(fā)器的語句的種類：insert delete update
// trigger_body 可使用begin..end 復(fù)合語句結(jié)構(gòu)
create trigger t after insert on t1 for each row set @str='one customer added!';
刪除：
drop trigger [if exists] trugger_name;
二、insert觸發(fā)器(詳解)：
//也可以被load data觸發(fā) 均表示新增數(shù)據(jù)
create trigger insert_trigger after insert on customers for each row set @str=new.id;
//往t2插入數(shù)據(jù)的時(shí)候往對(duì)應(yīng)的t1表中增加人數(shù)
delimiter &&
create trigger tr1 after insert
on t2 for each row
begin
declare a int;
set a=(select count from t1 where sno=new.sno);
update t1 set count=a+1 where sno=new.sno;
end &&?
三、delete和update
//update和delete觸發(fā)器寫法于insert大同小異 在trigger_time和trigger_event上有所不同
并且在運(yùn)用old和new臨時(shí)表的不同
//old上保存已刪除的數(shù)據(jù)記錄? new保存即將更新的數(shù)據(jù)，包括insert和update
當(dāng)涉及自身表的更新的時(shí)候 只能使用before update觸發(fā)器 而 after觸發(fā)器不被允許
