第2章 數(shù)據(jù)庫(kù)創(chuàng)建、修改、刪除

系統(tǒng)數(shù)據(jù)庫(kù)：mysql、test

SHOW databases;
# 創(chuàng)建數(shù)據(jù)庫(kù)，制定數(shù)據(jù)庫(kù)的字符集
create DATABASE database_name character SET character_name;
# 修改數(shù)據(jù)庫(kù)使用的字符集（只能對(duì)字符集進(jìn)行修改）
ALTER DATABASE database_name character SET character_name;
# 刪除數(shù)據(jù)庫(kù)
DROP DATABASE database_name;

第3章 數(shù)據(jù)表的創(chuàng)建、修改刪除

數(shù)據(jù)類型

數(shù)值類型

• 整數(shù)：int、tinyint、smallint、bigint
• 小數(shù)：decimal、float、double

字符串類型
char、varchar、binary、varbinary、blob(tinyblob、blob、mediumblob、longblob)、text(tinytext、text、mediumtext、longtext)

日期類型
datetime、date、timestamp、time

枚舉類型：enum
集合類型：set
位類型：bit、bool

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

# 創(chuàng)建數(shù)據(jù)表語(yǔ)法規(guī)則
create TABLE table_name(
    column_name datatype,
    column_name datatype,
);

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

# 修改表名稱
alter table table_name rename table_new_name;

# 修改數(shù)據(jù)表語(yǔ)法
ALTER TABLE table_name ADD column_name datatype；   --增加列
ALTER TABLE table_name MODIFY column_name datatype；--修改列
ALTER TABLE table_name DROP COLUMN column_name;     --刪除列
# 修改表中字段的名字
ALTER TABLE table_name CHANGE old_colname new_colname datatype;

ALTER TABLE product_info MODIFY proname varchar(30);
ALTER TABLE product_info CHANGE proname pronamenew varchar(30);

# 修改字段順序
ALTER TABLE 表名 MODIFY 字段名1 數(shù)據(jù)類型 FIRST ｜ AFTER 字段名2;

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

DROP TABLE table_name;

第4章 約束

主鍵約束：唯一標(biāo)識(shí)表中的一個(gè)列，只能有一個(gè)主鍵約束，但是可以包含多列。

create TABLE table_name{
  column_name1 datatype PRIMARY KEY,
  column_name2 datatype,
};

#聯(lián)合主鍵
create TABLE table_name{
  column_name1 datatype,
  column_name2 datatype,
  [CONSTRAINT constraint_name] PRIMARY KEY(column_name1, column_name2)
};

#添加主鍵約束
ALTER TABLE table_name ADD CONSTRAINT pk_name PRIMARY KEY(column_name);

#添加聯(lián)合主鍵約束
ALTER TABLE table_name ADD CONSTRAINT pk_name PRIMARY KEY(column_name1， column_name2);

#刪除主鍵約束，因?yàn)橹挥幸粋€(gè)，所以不需要指定
ALTER TABLE table_name DROP PRIMARY KEY;

外鍵約束
確保數(shù)據(jù)的正確性。兩個(gè)表之間的關(guān)系，如果表A中的某列數(shù)據(jù)出現(xiàn)在表B中，那么A表稱為父表，B表稱為子表，A中的列要設(shè)置成主鍵約束，B表中與之相同的列才能設(shè)置為外鍵約束。

create TABLE table_name{
  column_name1 datatype,
  column_name2 datatype,
  CONSTRAINT fk_name foreign key(column_name1) reference table_name1(column_name2)
};

#添加外鍵約束
ALTER TABLE table_name ADD CONSTRAINT fk_name foreign key(column_name1) reference table_name2(column_name2);
#刪除外鍵約束
ALTER TABLE table_name DROP foreign key fk_name;

默認(rèn)值約束

create TABLE table_name(
column_name1 datatype DEFAULT default_value
);

#修改默認(rèn)值約束
ALTER TABLE table_name ALTER column_name SET DEFAULT default_value;

#刪除默認(rèn)值約束
ALTER TABLE table_name ALTER col_name DROP default;

非空約束

create TABLE table_name{
    column_name datatype NOT NULL，
    column_name datatype 
};

#添加非空約束
ALTER TABLE table_name MODIFY column_name datatype NOT NULL;

檢查約束
檢查字段有效性,檢查約束在表中是不生效的，仍然可以插入不符合條件的數(shù)據(jù)。

create TABLE table_name{
    column_name datatype CHECK(expression),
    column_name datatype
};

#添加表級(jí)約束
ALTER TABLE table_name ADD CONSTRAINT ch_name CHECK(expression);

唯一約束
確保列的唯一性，可以有多個(gè)唯一約束，允許有空值，但是只能有一個(gè)空值。

create TABLE table_name{
    column_name datatype UNIQUE，
    column_name2 datatype
};

create TABLE table_name{
    column_name datatype,
    column_name2 datatype,
    [CONSTRAINT constraint_name] UNIQUE (column_name1, column_name2)
};

#修改時(shí)添加約束
ALTER TABLE table_name ADD CONSTRAINT uq_name UNIQUE (column_name1, column_name2);

#刪除唯一約束
DROP INDEX column_name  and table_name;

第5章 DML語(yǔ)言操作數(shù)據(jù)表

DML：數(shù)據(jù)操作語(yǔ)言，主要包括數(shù)據(jù)表數(shù)據(jù)的增刪改查。

添加數(shù)據(jù)：INSERT

# 語(yǔ)法
INSERT [INTO] table_name [(col_name, ...)]  VALUES (col_value,...);

#指定字段插值
INSERT [INTO] table_name SET col_name = col_value,...;

#插入查詢結(jié)果
INSERT [INTO] table_name [(column_name1, column_nam2,..)] select * from table_name2;

#為表添加多條數(shù)據(jù)
INSERT INTO table_name VALUES (value_list1),...,(value_listn);

修改數(shù)據(jù)：UPDATE

#指定修改符合修改條件的修改順序，限制修改行數(shù)
UPDATE table_name 
SET col_name1 = value1,col_name2 = value2,...
[where 條件] 
[order by col_name1 DESC/ASC, col_name2,DESC/ASC] 
[LIMIT row_count];

#修改表中的全部數(shù)據(jù)
UPDATE table_name SET col_name1 = value1,col_name2 = value2,...;

刪除數(shù)據(jù)：DELETE

DELETE from table_name{
    [where 條件]
    [order by ...]
    [LIMIT row_count]
}

#另一種刪除全部數(shù)據(jù)，不會(huì)返回刪除數(shù)據(jù)的行數(shù)
TRUNCATE TABLE tablename;

第6章 簡(jiǎn)單查詢和子查詢

運(yùn)算符

#查詢信息，修改查詢結(jié)果
selcect name, expensive+5, expensive-5,subject, tel from examinfo;
#修改信息
UPDATE examinfo SET expense = expense +5 where name = '張三';
# 乘除法
select name, expensive*id, expensive/2,subject, tel from examinfo;
select name, expensive%5,,subject, tel from examinfo;
# 比較運(yùn)算符
select 5>10, 5+6>=10;
select 5=10, 5<>10;

# IS NULL 運(yùn)算符
select 10 IS NULL, NULL IS NULL;

#BETWEEN 判斷是否在某一范圍內(nèi)
select 10 BETWEEN 1 and 10, 10 BETWEEN 11 and 100;

#LEAST 用來(lái)得到一組數(shù)中的最小值
select LEAST(5, 2,20), LEAST('a','b','c');

# 邏輯運(yùn)算符,邏輯非，NOT優(yōu)先級(jí)非常低
select NOT 10, NOT 0, NOT NULL;
select !10, !0 ,!NULL;

# 邏輯與
select 2 and 3, 1 and 0 , NULL and 1;
select 2 && 3, 1 && 0 , NULL && 1;

# 邏輯或
select 1 OR 2, 10 OR 0, NULL OR 2, 0 OR 0;
select 1 || 2, 10 || 0, NULL || 2, 0 || 0;

select 1 XOR 3, 2 XOR 2, NULL XOR 2, 0 XOR 1;

#位運(yùn)算符

簡(jiǎn)單查詢

select [* | DISTINCT | DISTINCTROW| col_name]       #去除查詢結(jié)果中相同的行
[from table_name]                                   
[where condition]
[group by col_name];
#分組
[having condition]          #分組條件語(yǔ)句,只能用在分組查詢中，在group by后面
[order by col_name [ASC|DESC]]
[LIMIT [offset,] rowcount]

select name subject from Examinfo;

#使用別名
select col_name1 AS 別名1, col_name2 AS 別名2...

#單一條件查詢數(shù)據(jù)
select subject from examinfo where expense >100;

#模糊查詢，查詢含有英語(yǔ)的信息
select * from examinfo where subject LIKE '%英語(yǔ)%';

# 多個(gè)條件查詢
select * from examinfo where name = '張三' OR subject ='英語(yǔ)口語(yǔ)';

#查詢結(jié)果排序
select * from examinfo order by expense DESC;

聚合函數(shù)

select MAX(expense) from examinfo;
select subject, MIN(expense) form examinfo;
select AVG(expense) form examinfo;
select SUM(expense) AS '總費(fèi)用' from examinfo;
select count(*) AS '報(bào)名總數(shù)' from examinfo;

子查詢
查詢語(yǔ)句中的查詢語(yǔ)句。

常用操作符

select col_name1, col_name2, ... from table_name
where col_name in (select ...);

select * from examinfo where subject in ('數(shù)學(xué)', '英語(yǔ)');
select * from examinfo where subjuect in (select name from subjectinfo);

select name, expense from examinfo where exists(select * from subjectinfo where name = '數(shù)學(xué)');#true，查詢examinfo全部數(shù)據(jù)??！

select * from examinfo where subject = any(select name form subjectinfo);

第7章 復(fù)雜查詢

分組查詢:group by

# 單列分組查詢
select subject, count(*) from studentinfo group by subject;  

# 使用having的分組查詢
# where查詢效率更高，因?yàn)閣here先過(guò)濾，再分組
select subject, AVG(score) from studentinfo group by subject having subject = '英語(yǔ)';
select subject, AVG(score) from studentinfo where subject = '英語(yǔ)';  

# 多列分組查詢
select subject,techer, AVG(score) from studentinfo group by subject, teacher;

# 分組查詢后排序， order by 在所有子句后面
select teacher,sum(score) from studentinfo group by teacher order by SUM(score) DESC;

多表查詢

# 等值連接：將多個(gè)表之間的相同字段作為條件查詢數(shù)據(jù)，一般是主鍵、外鍵
select newsstudentinfo.name,subjectinfo.subjectname from newstudentinfo, subjectinfo where newstudentinfo.subjectid = subjectinfo.id;


select newsstudentinfo.name,subjectinfo.subjectname teacherinfo.teachername from newstudentinfo, subjectinfo, teacherinfo where newstudentinfo.subjectid = subjectinfo.id and newstudentinfo.teacherid = teacherinfo.id;

# 笛卡爾積:所有列的和以及行的積，沒有上面的等值連接，一般沒有意義
select * from newstudentinfo, subjectinfo;

# 左外鏈接：返回表中符合條件的記錄和左表中剩下的全部記錄
select colname1, colname2
from table_name1 left/right outer join table_name2
on 條件

# 左外連接，subjectname列可能出現(xiàn)null
select newstudentinfo.name,subjectinfo.subjectname from newstudentinfo, subjectinfo left outer join subjectinfo on newstudentinfo.subjectid = subjectinfo.id;

# 右外連接， name列可能出現(xiàn)null
select newstudentinfo.name,subjectinfo.subjectname from newstudentinfo, subjectinfo right outer join subjectinfo on newstudentinfo.subjectid = subjectinfo.id;

# 內(nèi)連接：類似等值連接，都是符合條件的結(jié)果;好處是更好地明確數(shù)據(jù)表的連接方式
select colname1, colname2...
from table_name1 inner join table_name2 
on 條件

select newstudentinfo.name,subjectinfo.subjectname,teacherinfo.teachername
from newstudentinfo subjectinfo inner join subjectinfo inner join teacherinfo ON newstudentinfo.subjectid = subjectinfo.id and newstudentinfo.teacherid = teacherinfo.id;

合并查詢結(jié)果
UNION:連接兩個(gè)查詢結(jié)果，查詢結(jié)果中的列數(shù)和數(shù)據(jù)類型必須一致

select col_name from tabel_name1
UNION[ALL]
select col_name from table_name2;

select * from subjectinfo
UNION
select * from teacherinfo;

select id AS '編號(hào)', subjectname AS '名稱' from subjectinfo
UNION
select * from teacherinfo;

# 合并后的查詢結(jié)果排序
(select col_name from tabel_name1)
UNION[ALL]
(select col_name from table_name2)
order by colname;

(select id, subjectname from subjectinfo)
UNION
(select * from teacherinfo)
order by id;

# 限制組合查詢結(jié)果的行數(shù)
(select col_name from tabel_name1)
UNION[ALL]
(select col_name from table_name2)
LIMIT 行數(shù);

第8章 函數(shù)

數(shù)值類型函數(shù)

# 絕對(duì)值函數(shù)
select abs(-21), abs('-1'), abs(-91/4);
# 取余函數(shù)
select mod(10,3);
# 平方根函數(shù)
select sqrt(100);
# 隨機(jī)數(shù)函數(shù)
select rand(), rand(2);
# 四舍五入函數(shù)
select round(4.5123,2);
# 符號(hào)函數(shù)
select sign('21');
# 冪函數(shù)
select power(8, 2);
# 對(duì)數(shù)運(yùn)算函數(shù)
select log(5, 25);
# pi
select pi();
# 三角函數(shù)
select sin(pi()/2), sin(pi());
# 獲取最小正數(shù)
select ceil(4.5);

字符串函數(shù)

# 合并字符串
select concat('this','is','a','test');

#計(jì)算長(zhǎng)度，
select length('1234');          --以字符為單位
select char_length('1234');     --以字節(jié)為單位

# 大小寫轉(zhuǎn)換
select upper('abcd測(cè)試');
select lower('ABCD測(cè)試');

# 獲取指定長(zhǎng)度字符串
select left(str, len);
right(str, len);

# 填充字符串
lpad(str, len, padstr) -- 左邊填充，若str<len則str保留len長(zhǎng)度
rpad(str, len, padstr)

# 刪除指定字符,，默認(rèn)刪除兩端空格
trim(both | leading |trailing remstr from str)

# 刪除字符串前端或后端的空格
ltrim(str)
rtrim(str)

# 重復(fù)生成字符串
repeat (str, count)

# 空格函數(shù)
space(num)

# 替換函數(shù)
replace(str, from_str, to_str)

# 插入函數(shù),將從pos起的len長(zhǎng)度字符串替換
INSERT(str, pos, len, newstr)

# 比較字符串大小
strcmp(str1, str2)

# 獲得子串，len參數(shù)可以省略
substring(str, pos, len)

# 逆序
reverse(str)

# 返回指定字符串位置的函數(shù)，返回位置序號(hào)1-2..
field(str, str_1, str_2)

日期和時(shí)間

# 返回指定日期對(duì)應(yīng)的工作日索引
select dayofweek('2011-11-11')          --1表示周日，7表示周六
select weekday(date)                   --0表示周一，6表示周日

# 返回每月的日索引
select dayofmonth('2007-3-20');

# 年的日索引
select dayofyear('2011-1-1');

# 月份
select month('2011-1-1');

# 月名稱
select monthname('2011-1-1');

# 星期幾名稱
select dayname('2011-1-1');

# 季度
slelect quarter('2011-1-1');

# 年份
select year('2011-1-1');

# 小時(shí)
select hour('2011-1-1 12:22:00');

# 分鐘
select minute('2011-1-1 12:22:00');
select second(t'2011-1-1 12:22:00');

# 增加月 date格式Y(jié)YMM 或者 YYYYMM
select period_add(201201, 5)；

# 取月份差 date格式Y(jié)YMM 或者 YYYYMM
select period_diff(1201,1206)

# 返回當(dāng)前日期函數(shù)
select curdate();

# 當(dāng)前時(shí)間函數(shù)
select curtime()

# 當(dāng)前日期時(shí)間
select now()
select sysdate()

# 秒轉(zhuǎn)換成時(shí)間
select sec_to_time(seconds)

# 時(shí)間轉(zhuǎn)化成秒 格式：HH:MM:SS
select time_to_sec(time)

其他函數(shù)

# 返回當(dāng)前用戶名
select session_user();

# 返回當(dāng)前數(shù)據(jù)庫(kù)
database();

# 返回字符串的md5校驗(yàn)
select md5('md5test');

第9章 視圖

查詢結(jié)果集/邏輯表，里面的數(shù)據(jù)允許開發(fā)者進(jìn)行查詢，但是數(shù)據(jù)更新有限制。當(dāng)基本表中的數(shù)據(jù)發(fā)生改變時(shí)，視圖中的數(shù)據(jù)也會(huì)發(fā)生改變。
優(yōu)勢(shì)：可簡(jiǎn)化復(fù)雜數(shù)據(jù)，增加數(shù)據(jù)安全性，隔離數(shù)據(jù)。

創(chuàng)建視圖

create 
    view view_name [(column_list)]
    as select_statement
    [WITH CHECK OPTION]
    
# 單源表視圖創(chuàng)建:視圖只有一個(gè)基表
# test數(shù)據(jù)庫(kù)創(chuàng)建view_st_score視圖
create 
    VIEW 'test'.'view_st_score'
    as
    (
    select sc.id, sc.scores,sc.subject, st.name,st.age, st.sex
    from scoresinfo sc, studentinfo st
    where sc.student_id = st.id
    )
# 查看視圖中的數(shù)據(jù)
select * FORM view_st_score where sex = 1;

# 多源表視圖創(chuàng)建：數(shù)據(jù)來(lái)源于兩張或者多張表
create 
    VIEW 'test'.'view_st_score'
    as
    (
    select sc.id, sc.scores,sc.subject, st.name,st.age, sx.name
    from scoresinfo sc, studentinfo st, sexinfo sx
    where sc.student_id = st.id
    and sx.id = st.sex
    )
# 查看視圖中的數(shù)據(jù)
select * from view_st_score where age>17 order by age;

修改視圖

create [OR REPLACE]
    VIEW view_name [(column_list)]
    as select_statement
    [WITH CHECK OPTION]
# 修改為sex列數(shù)據(jù)為0的記錄    
create OR REPALCE
    VIEW 'test'.'view_st_score'
    as
    (
    select sc.id, sc.scores,sc.subject, st.name,st.age, st.sex
    FORM scoresinfo sc, studentinfo st
    where sc.student_id = st.id
    and st.sex = 0
    )

刪除視圖

DROP VIEW [IF EXISTS]
    view_name [, view_name]...
    
#防止刪除錯(cuò)誤
drop VIEW  EXISTS view_st_score_se;

查看視圖

DESCRIBE view_name;
DESC view_name;
# 查看創(chuàng)建視圖的語(yǔ)句，格式化顯示
show create VIEW view_name\G;

第10章 索引

索引是某個(gè)表中一列或者若干列值的集合和相應(yīng)的指向表中物理標(biāo)識(shí)的這些值的數(shù)據(jù)頁(yè)的邏輯指針清單。可以使用戶快速找到表中或索引視圖的特定信息。索引包含從表中或者視圖中一個(gè)或多個(gè)列生成的鍵，以及映射到指定數(shù)據(jù)存儲(chǔ)位置的指針?？梢燥@著提高數(shù)據(jù)庫(kù)的查詢性能，減少查詢讀取的數(shù)據(jù)量，強(qiáng)制表中的記錄具有唯一性，確保數(shù)據(jù)的完整性。

分類

• 普通索引：沒有唯一性限制，允許重復(fù)
• 唯一索引：索引列值需要唯一，需要使用UNIQUE
• 主鍵索引：專門為主鍵字段創(chuàng)建的索引
• 全文索引：類型為FULLTEXT，可以VARCHAR或TEXT類型上創(chuàng)建

作用

• 加快數(shù)據(jù)檢索速度
• 主鍵約束列使用唯一索引
• 查詢中使用了order by和group by子句時(shí)，索引使用可以明顯減少查詢時(shí)間
• 表表連接查詢時(shí)，創(chuàng)建索引列能提高表表連接速度

注意事項(xiàng)

• 索引占用數(shù)據(jù)庫(kù)空間，需要考慮索引所占用的空間
• 為提高查詢速度，建議把表和表的索引放在不同的磁盤上
• 過(guò)多的索引能提高查詢速度，但是會(huì)降低更新速度
• 索引列應(yīng)當(dāng)是where子句中使用相對(duì)頻繁的列
• 創(chuàng)建索引的表應(yīng)當(dāng)是：數(shù)據(jù)大、查詢頻繁，但更新較慢的表，小表不建議使用索引

管理索引

# 創(chuàng)建普通索引
create index index_name
    on table_name (column_list(length));
    
alter TABLE table_name
    ADD INDEX index_name(column_list);
    
create INDEX idx_studentinfoix_one
    ON studentinfoix(NAME);
    
ALTER TABLE studentinfoix
    ADD INDEX idx_studentinfoix_th(name(5));

# 創(chuàng)建唯一索引
create UNIQUE INDEX index_name
    ON table_name (column_list(length));
    
ALTER TABLE table_name
    ADD UNIQUE INDEX index_name(column_list);
    
create UNIQUE INDEX idx_studentinfoix_se
    ON studentinfoix (NAME, age(2), sex(1));
    
ALTER TABLE studentinfoix
    ADD UNIQUE INDEX idx_studentinfoix_th(name(5),age(2), sex(1));
    
# 創(chuàng)建主鍵索引
ALTER TABLE table_name
    ADD PRIMARY KEY(column_list);
ALTER TABLE studentinfoix
    ADD PRIMARY KEY(id(7));
    
# 查看索引
SHOW INDEX from tb_name;

#刪除索引
DROP INDEX index_name ON table_name;
ALTER TABLE table_name DROP INDEX index_name;

第11章 自定義函數(shù)

create FUNCTION fu_name([param_name datatype[, ...]])
    RETURNS type
    routine_body
    
# 創(chuàng)建函數(shù)，第一行指明結(jié)束標(biāo)記
delimiter //

create FUNCTION myfstfun()
RETURNS VARCHAR(5)

BEGIN 
    RETURN 2+3;
END//

select myfstfun();

# 創(chuàng)建帶參數(shù)的函數(shù)
DELIMITER //
create FUNCTION
    test.myfstfun_2(in_id VARCHAR(20), in_str1 VARCHAR(20), in_str2 VARCHAR(20))
    RETURNS VARCHAR(5)
    
    begin
        DECLARE int_1 INT;
        DECLARE int_2 INT;
        IF(in_id IS NOT NULL) THEN
            select scores into int_1
            from scoresinfo
            where student_id = in_id and SUBJECT = in_str1;
            select scores into int_2
            from scoresinfo
            where student_id = in_id and SUBJECT = in_str2;
        end if;
        return int_1+int_2;
    end //

 select myfstfun_2('000002', '英語(yǔ)','數(shù)學(xué)')；       

函數(shù)的管理

# 刪除函數(shù)
DROP {FUNCTION} [IF EXISTS]
fn_name

DELIMITER //
DROP FUNCTION [if exists]
myfstfun_4
//

第12章 存儲(chǔ)過(guò)程

存儲(chǔ)過(guò)程：SQL語(yǔ)句集，當(dāng)執(zhí)行成功后會(huì)被存儲(chǔ)在數(shù)據(jù)庫(kù)服務(wù)器中，并允許客戶端直接調(diào)用，而且存儲(chǔ)過(guò)程可以提高SQL語(yǔ)句的執(zhí)行效率。允許包含一條或者多條SQL語(yǔ)句，完成一個(gè)或多個(gè)邏輯功能。

優(yōu)點(diǎn)：

• 封裝復(fù)雜的操作：把SQL語(yǔ)句包含到一個(gè)獨(dú)立的單元中
• 使數(shù)據(jù)獨(dú)立：調(diào)存儲(chǔ)過(guò)程代替SQL語(yǔ)句，隔離用戶和數(shù)據(jù)
• 提高安全性：外部程序無(wú)法直接操作數(shù)據(jù)表
• 提高性能：SQL語(yǔ)句在服務(wù)器中直接執(zhí)行，客戶端調(diào)用，避免了多次連接和發(fā)送SQL語(yǔ)句的過(guò)程

# 創(chuàng)建存儲(chǔ)過(guò)程
create PROCEDURE sp_name ([IN | OUT | INOUT] param_name type[,...])
body

DELIMITER //
create PROCEDURE test.fst_prc(OUT param1 INT)
    begin
        select count(*) INTO param1 from scoresinfo;
    end//

# 創(chuàng)建無(wú)參存儲(chǔ)過(guò)程
DELIMITER //
create PROCEDURE test.fst_scoresinfo_proc()
    begin 
        UPDATE scoresinfo SET remark = '優(yōu)秀' where scores >= 90;
    end//
    
DELIMITER ;
CALL fst_scoresinfo_proc//

# 創(chuàng)建含參存儲(chǔ)過(guò)程
DELIMITER //
create 
    PROCEDURE test.se_scoresinfo_proc(IN param1 INT)
    BEGIN
        IF (param1 IS NOT NULL) THEN
            UPDATE scoresinfo SET remark = '一般' where scores <= 70;      
        END IF;
    END//
    
DELIMITER //
CALL SE_scoresinfo_proc(1)
//

# 創(chuàng)建OUT類型參數(shù)存儲(chǔ)過(guò)程
DELIMITER //
create 
    PROCEDURE test.th_proc(OUT param1 INT)
    BEGIN
        select count(*) INTO param1 from studentinfo;
    END//
DELIMITER //
CALL th_proc(@X)
//
select @x
//

# 創(chuàng)建INOUT類型參數(shù)存儲(chǔ)過(guò)程
DELIMITER //
create 
    PROCEDURE test.fo_scoresinfo_proc(INOUT param1 INT)
    BEGIN
        IF (param1 IS NOT NULL) THEN 
            select count(*) INTO param1 from studentinfo;
        END IF;
    END//
DELIMITER //
SET @a = 1//
CALL fo_scoresinfo_proc(@a)
//
select @a
//

修改存儲(chǔ)過(guò)程
利用SQLyog工具修改。

刪除存儲(chǔ)過(guò)程

DROP PROCEDURE
[IF EXISTS]
sp_name

存儲(chǔ)過(guò)程中的變量

# 聲明
DECLARE var_name[, ...] type [DAFAULT value]
# 賦值
SET var_name =expr [, var_name = expr]...

結(jié)構(gòu)控制語(yǔ)句

# IF
IF search_condition THEN statement_list
    [ELSEIF search_condition and THEN statement_list]
    [ELSE statement_list]
END IF

# CASE
# 存儲(chǔ)過(guò)程中的CASE語(yǔ)句與函數(shù)里的CASE語(yǔ)句有差別，存儲(chǔ)過(guò)程里的CASE語(yǔ)句不能有ELSE NULL子句，并且用END CASE終止。
CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list]
    [ELSE statement_list]
END CASE

CASE
    WHEN search_conditi and THEN statement_list
    [WHEN search_conditi and statement_list]
    [ELSE statement_list]
END CASE

# LOOP循環(huán)控制語(yǔ)句
[begin_label:]LOOP
    statement_list
END LOOP [end_label]

create PROCEDURE prcloop(x INT)
BEGIN 
    label1:LOOP
    SET x =x+1;
    IF x<8
    THEN ITERATE label1;
    END IF;
    # 退出LOOP語(yǔ)句
    LEAVE  label1; 
    END LOOP label1;
    SET @xx =x;
END

#另一種方法
create PROCEDURE prcloopse(x INT)
BEGIN 
    label1:LOOP
        SET x =x+1;
        IF x>=8
            THEN LEAVE  label1;
        END IF;
    END LOOP label1;
    SET @xx =x;
END

# while語(yǔ)句的使用
[begin_label:]while search_condit and DO
    statement_list
END while [end_label]

DELIMITER //
create 
    PROCEDURE test.prcwhile()
    BEGIN
    DECLARE vx INT DEFAULT 10;
     vx>0 DO
        SET vx = vx-1;
    END while 
    SET @xx = vx;
    END //
select @xx    

第13章 觸發(fā)器

觸發(fā)器只能由數(shù)據(jù)庫(kù)的特定事件來(lái)觸發(fā)，并且不能接受參數(shù)。特定事件包括：插入、更改、刪除。
用途

• 防止誤操作
• 評(píng)估修改前后狀態(tài)，采取對(duì)應(yīng)措施
• 實(shí)現(xiàn)數(shù)據(jù)的級(jí)聯(lián)修改，保證了數(shù)據(jù)的完整性
• 記錄操作事件，實(shí)現(xiàn)日志

創(chuàng)建觸發(fā)器

注意：在同一個(gè)表下，不可能有兩個(gè)相同時(shí)間或事件的觸發(fā)器。

# 語(yǔ)法
create TRIGGER tigger_name trigger_time trigger_event
ON tbl_name for EACH ROW                    #tbl_name 觸發(fā)器宿主
trigger_stmt

# BEFORE INSERT類型的觸發(fā)器
DELIMITER //
create TRIGGER test.fstINSERTting
    BEFORE INSERT ON newsstudentinfo
    FOR EACH ROW
    BEGIN 
        INSERT INTO Logtab(oname, otime) VALUES('test', SYSDATE());
    END
//

# AFTER INSERT 類型的觸發(fā)器
DELIMITER //
create trigger test.secINSERTtrg
    # 增加數(shù)據(jù)時(shí)觸發(fā)該觸發(fā)器
    AFTER INSERT ON newsstudentinfo 
    FOR EACH ROW
        BEGIN
            INSERT INTO Logtab(oname, otime) VALUES ('test_after', SYSDATE());
        END
//

INSERT INTO tst.studentinfo VALUES('111111', '測(cè)試', 12.0);//
INSERT * from test.logtab;//

管理觸發(fā)器

# 刪除觸發(fā)器
drop TRIGGER
[schema_name.] trigger_name 

DELIMITER //
DROP TRIGGER test.fivdeltrgger//
#修改觸發(fā)器：刪除，重新創(chuàng)建

第14章 數(shù)據(jù)庫(kù)的權(quán)限與備份

# 登錄
mysql -u root -p
# 創(chuàng)建用戶
create USER
'username'@'localhost' IDENTIFIED BY 'password'
# 用戶權(quán)限
GRANT privileges
ON databasename.tablename
TO 'username'@'localhost'

數(shù)據(jù)備份和恢復(fù)

# mysqldump備份
mysqldump [OPTIONS] database [tables]
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2, DB3]
mysqldump [OPTIONS] --all -databases [OPTIONS] 

#備份一個(gè)數(shù)據(jù)庫(kù)的多個(gè)表
mysqldump -u root -p abcd1234 test_db book users>c:\tabbak.sql
# 備份多個(gè)數(shù)據(jù)庫(kù)
mysqldump -u root -p abcd1234 --databases test_db test >c:\dbbak.sql
# 備份所有數(shù)據(jù)庫(kù)的結(jié)構(gòu)
mysqldump -u root -p abcd1234 -A-D>c:\allbak.sql

# 恢復(fù)操作
mysql -u root -p abcd1234 test < c:\tabbak.sql

JAVA連接MySQL

JDBC：JAVA DataBase Connectivity，Java應(yīng)用程序接口，用來(lái)執(zhí)行SQL語(yǔ)句