这是本人在大二上学期在校学习MySQL时整理的笔记。
如有不足，望指正！欢迎点赞收藏！
一、知识点
SQL语言
概念
结构化查询语言(Stuctured Query Language)简称SQL,是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。
特点
主要有7个动词：create、alter、drop、insert、update、delete、select。
SQL语言是一种面向集合的语言，每个命令的操作对象是一个或多个关系，结果也是一个关系。
SQL语言既是自含式语言，又是嵌入式语言。可独立使用，也可嵌入到宿主语言中。
功能
-
查询（DQL）
select where、order by、group by、having -
操纵（DML）
insert、update、delete -
定义（DDL）
create、drop、alter -
控制（DCL）
grant、revoke、commit、rollback、savepoint、declare、cursor、fetch into
数据库对象
- 表
- 视图
- 索引
- 存储过程
- 函数
- 触发器
MySQL数据类型
- 数值类型（整数类型和小数类型）
- 字符串类型
- 日期类型
- 复合类型（enum和set）
- 二进制类型
primary和unique的区别
- 在一个基本表中只能定义一个primary key约束，但可以定义多个unique约束；
- 对于指定的primary key的一个列或多个列的组合，其中任何一个列都不能出现空值，而对于unique所约束的唯一键，则允许为空，但最多只有一个出现null值。
- 注意：不能把同一列或一组列即定义unique又定义primary。
索引与约束
约束主要用于保证业务逻辑操作数据库时数据的完整性。约束是逻辑层面的概念。
索引是将关键字数据以某种数据结构的方式存储到外存，用于提升数据的检索性能；索引既有逻辑上的概念，更是一种物理存储方式，且事实存在、需要耗费一定的存储空间。
replace和insert的区别
使用replace语句向表中插入新纪录时，如果新记录的主键值或者唯一性约束的字段值与已有记录相同，则已有记录先被删除（注意：已有记录删除时也不能违背外键约束操作），然后再插入新记录
使用replace的最大好处就是可以将delete和insert合二为一，形成一个原子操作，这样就无需将delete操作与insert操作置于事务中了。
drop和delete的区别
delete可以带条件删除，drop不需要
delete和truncate的区别
delete可以带条件删除，truncate不需要；
delete支持事务回滚，truncate不支持。
内连接和外连接的区别
内连接要求满足连接条件的记录才会显示。
外连接中的两张表的关系是不平等的。连接条件只过滤一个表对另一个表不进行过滤（该表中的所有记录出现在结果集中。而在其他属性上填空值NULL）
where和having的区别
where在分组之前进行限定，如果不满足条件则不参与分组。having在分组之后进行限定，如果不满足结果则不会被查询出来。
where后不可以跟聚合函数，having可以进行聚合函数的判断。
union与union all的区别
使用union时，MySQL会筛选掉select结果集中重复的记录（在结果集合并后会对新产生的结果集进行排序运算，效率稍低）
使用union all时，MySQL会直接合并两个结果集，效率高于union。如果可以确定合并前的两个结果集中不包含重复的记录，建议使用union all
系统会话变量与用户会话变量的区别
共同之处：变量名大小写不敏感
区别：
- 系统会话变量以@@开头
- 用户会话变量一般以@开头
- 系统会话变量无需定义可以直接使用
- 用户会话变量无需定义，需要先赋值才可以使用
存储过程和函数的相同点
- 在调用时，只需要提供名字以及参数信息
- 可以重复使用
- 可以增强数据的安全访问控制
存储过程和函数的不同点
-
函数仅有一个返回值且必须指定其数据类型；存储过程可以无或一个或多个返回值，返回值需要用out或者inout参数定义
-
函数内用select—into为某变量赋值，但不能使用select语句返回结果；存储过程无此限制，甚至可以返回多个结果集
-
函数可以嵌入到SQL语句；存储过程一般需要call单独调用
-
函数中函数体限制较多；存储过程限制较少
二、数据库
登录
命令行
mysql -u root -p
创建
语法：
create database 数据库名[[default] character set 字符集名][[defalut] collate 字符序名]
例：创建名字为choose的数据库
create database choose;
例：创建名字为hello的数据库，并指定简体中文字符集
create database hello character set gbk;
修改
语法：
alter database 数据库名 character set 字符集
例：
alter database choose character set gbk;
显示结构
show create database 数据库名
例：
show create database choose;
查看
语法：
show databases;
选择当前操作的数据库
语法：
use 数据库名
删除
语法：
drop database[if exists] 数据名
三、表
创建
语法：
create table 表名 (
字段名1 数据类型[约束条件]，
字段名2 数据类型[约束条件]，
…
[其他约束条件]
)[engine=存储引擎][default charset=字符集];
例：
create table Department(
Departmentidchar(3) primary key,
DepartmentNamevarchar(20) NOT NULL unique,
DepartmentHeadervarchar(8) NOT NULL,
TeacherNum int
);
例：创建学生表(学号，姓名，性别，专业号，出生日期，班长)
use choose;
create table student(
sno int unsigned,
name varchar(8),
sex char(2),
spec char(20),
birth date,
level int unsigned
);
例：创建个人信息表(姓名、性别、爱好，使用集合和枚举类型)
use choose;
create table person(
xmvarchar(8),
xbenum('男','女'),
xqset ('电影','旅游','游戏','音乐','运动','购物')
);
查看
查看当前数据库中所有的表
show tables
查看指定表结构
desc 表名
查看指定表的详细信息
show create table 表名
复制
方法一：复制表结构
create table 新表名 like 原表
方法二：复制表结构及原数据
create table 新表名 select * from 原表
修改
添加新字段
alter table 表名 add 新字段名 新数据类型[新约束条件][first|after 旧字段名]
修改字段名
-
修改表的字段名（及数据类型）
alter table 表名 change 旧字段名 新字段名 新数据类型
-
如果仅对字段的数据类型进行修改
alter table 表名 modify 字段名 新数据类型
添加约束条件
alter table 表名 add constraint 约束名 约束类型（字段名）
删除约束条件
-
删除主键
alter table 表名 drop primary key
-
删除外键，需指定外键约束名称
alter table 表名 drop foreign key 约束名
-
若要删除表字段的唯一性约束，实际只需删除该字段的唯一性索引即可
alter table 表名 drop index 唯一索引名
-
其他
alter table 表名 engine=新的存储引擎类型
alter table 表名 default charset=新的字符集
alter table 表名 auto_increment=新的初始值
alter table 表名 pack_keys=新的压缩类型
修改表名
rename table 旧表名 to 新表名;
alter table 旧表名 rename 新表名;
删除
删除指定表
drop table 表名
删除字段
alter table 表名 drop 字段名
索引
种类：
* 主索引、聚簇索引* 唯一性索引* 普通索引* 符合索引* 全文索引
创建索引
方法一
create table 表名(
字段名1 数据类型[约束条件],
...[其他约束条件],
...[ unique | fulltext] index[索引名] ( 字段名[(长度)] [ asc| desc] )
)engine=存储引擎类型default charset=字符集类型
例：
create table book(
isbnchar(20) primary key,
name char(100) not null,
brief_introductiontext not null,
price decimal(6,2),
publish_timedate not null,
unique index isbn_unique(isbn),
index name_index(name (20)),
fulltextindex brief_fulltext(name,brief_introduction),
index complex_index(price,publish_time)
) engine=MyISAMdefault charset=gbk
方法二：在已有表上创建索引
create [ unique | fulltext] index 索引名 on 表名( 字段名[(长度)] [ asc| desc] )
alter table 表名 add [ unique | fulltext] index索引名( 字段名[(长度)] [ asc| desc] )
删除索引
drop index 索引名 on 表名
四、记录
插入
insert into 表名[(列名，列名)] values(值列表)
- 注意：
- 向自增型auto_increment字段插入数据时，建议插入null值
- 外键字段不能输入空值、默认值、父表没有的数据
在插入数据前设置字符集
set chatacter_set_client = 字符集名字
use choose;
set character_set_client=gbk;
insert into classes values(null,'2012自动化4班', '机电工程');
select * from classes;
批量插入多条记录
insert into 表名[(字段列表)] values
(值列表1),
(值列表2),
…
(值列表n);
例：向student表中插入多条数据
INSERT INTO student VALUES
('1414855308','马琦','13980569900',1),
('1414855328','刘梅红','13688500900',2),
('1414855406','王松','13680500900',3),
('1416855305','聂鹏飞','13680500900',1),
('1418855212','李冬旭','15888050090',4),
('1418855232','王琴雪','15580500900',4);
使用insert… select插入结果
insert into 目标表名[(字段列表1)]
select (字段列表2) from 原表 where 条件表达式
例：创建一个学生表，它与student表的结构相同，然后将student表中的所有记录插入新建的学生表中
use choose;
create table new_student like student;
insert into new_student select * from student;
select * from new_student;
使用replace插入新纪录
语法格式1:
replace into 表名[(字段列表)] values (值列表)
语法格式2：
replace [into] 目标表名[(字段列表1)]
select (字段列表2) from 原表 where 条件表达式
语法格式3：
replace [into] 表名 set 字段1=值1，字段2=值2
例：replace用向学生表插入数据
replace into student values ('2012001', '张三丰','15044500900',1);
查询
单表查询
select [distinct] 列名列表
字段列表
from 数据源
[where 条件]
[group by 分组字段[having条件表达式]]
[order by 排序字段[asc|desc]]
[limit [m,]n];
- group by：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用集函数
- having：筛选出只有满足指定条件的组
- order by：对查询结果表按执行列值的升序或降序排序DISTINCT则可以清除重复的行
- limit [start,] length：限制输出结果的行数。start表示从第几行记录开始检索，length表示检索多少行记录。表中第一行记录的start值为0
例：检索choose表从第2条开始的3条记录
select * from choose limit 1, 3;
where
使用逻辑运算符
-
逻辑非（!）的使用
例：检索课程上线不是60人的所有课程信息
select * from course where !(up_limit=60); select * from course where up_limit != 60; select * from course where up_limit <> 60; -
逻辑与and的使用
例：检索数据库课程不及格的学生
select studentid,courseid,gradefrom gradewhere courseid='Dp010003' and grade<60; -
取值范围（下届-上界）的使用
-
between…and…
select student_no, score from choose where score between 85 and 100; -
>= <=
select student_no, score from choose where score>=85 and score<=100;
-
-
逻辑或or的使用
例：检索所有姓张和姓田的学生
select * from student where student_name like '张%' or student_name like '田%'; -
in运算符的使用
例：检索所有姓张和姓田的学生
select * from student where substring(student_name,1,1) in('张','田');
使用like进行模糊查询
| 通配符 | 含义 | 举例说明 |
|---|---|---|
| % | 零个或多个任意字符串 | like '好%'表示以"好"开头的文字 |
| _ | 任意单个字符 | like ‘_rt’ 表示所有以 “rt”结尾的三个字母 |
where子句设置结果集的过滤条件
| 查询条件 | 运算符 |
|---|---|
| 比较 | =, <=, >=, <>, >, <, !=, !>, !< |
| 范围 | between…and…, not between…and… |
| 集合 | in，not in |
| 空值 | is NULL，isnot NULL |
| 字符匹配 | like, not like |
| 多重条件 | and, or, ! |
order by
select…
order by 字段名1 [asc|desc] […,字段名n [asc|desc]]
多列排序规则：当order by子句指定了多个排序列时，系统先按照子句中第一列的顺序排列，当该列出现相同值时，再按照第二列的顺序排列，依次类推。
例：按降序查询学生分数
select * from choose order by score desc;
聚合函数
常用的有5个：
-
count()
-
注意：排除了null值
-
解决方案：
-
选择不包含空的列进行计算
-
可用ifnull(列名, 0)，把null当做是0来操作
-
-
-
avg()
-
sum()
-
max()
-
min()
group by
group by 字段列表 [having 条件表达式] [with rollup]
- group by：将结果集分为若干个组
- 聚合函数：对每个组内的数据进行信息统计
- with rollup：对每个组进行汇总运算
group by子句将查询结果按照某个字段（或多个字段）进行分组（字段值相同的记录作为一个分组）
例：统计每一个班的学生人数
select count(*), class_no from student group by class_no;
例：按照性别分组，分别查询男、女同学的人数和平均分
select sex, count(id), avg(math) from student group by sex;
例：按照性别分组，分别查询男、女同学的人数和平均分。要求：分数低于70分的人不参与分组，分组后人数要大于2个人
select
sex, avg(math) count(id)
from
student
where
math > 70
group by
sex
having
count(id) > 2;
having
用于设置分组或聚合函数的过滤筛选条件
例：检索平均成绩高于70分的学生信息及平均成绩
select
ch.student_no, student_name, avg(score)
from
choose ch
join
student st
on
ch.student_no = st.student_no
group by
ch.student_no
having
avg(score) > 70;
小结：
查询语句执行顺序：先用where子句过滤不符合条件的数据记录，接着用group by子句对余下的数据记录进行分类汇总，最后再用having子句排除不符合条件的组。
多表查询
多张数据库表”拼接”成一个结果集时，需要制定“拼接”条件，该“缝补”条件称为连接条件
方法1：from 表1[连接类型] join 表2 on 条件
方法2：from 表1,表2 where 条件
- 方法2不是规范方法
连接类型：
- inner（内连接）
- left（左外连接，简称左连接）
- right（右外连接，简称右链接）
student表
| student_no | student_name | student_contact | class_no |
|---|---|---|---|
| 2012001 | 张三 | 15000000000 | 1 |
| 2012002 | 李四 | 16000000000 | 1 |
| 2012003 | 王五 | 17000000000 | 3 |
| 2012004 | 马六 | 18000000000 | 2 |
| 2012005 | 田七 | 19000000000 | 2 |
class表
| class_no | class_name | department_name |
|---|---|---|
| 1 | 2012自动化1班 | 机电工程 |
| 2 | 2012自动化2班 | 机电工程 |
| 3 | 2012自动化3班 | 机电工程 |
内连接
参与连接的表时平等关系，会从两个表中提取满足条件的记录并组合成新的记录。
select 列表列名 from 表1 [inner] join 表2 on 连接条件
例：
select
student.*, class_name, department_name
from
student
join
classes
on
student.class_no = classes.class_no;
外连接
与内连接不同，外连接只过滤一个表，对另一个表不进行过滤（该表的所有记录出现在结果集中）
from 表1 left join 表2 on 表1和表2之间的连接条件
from 表1 right join 表2 on 表1和表2之间的连接条件
-
表1左连接表2，意味着查询结果集中需包含表1的全部记录，然后表1按指定的连接条件与表2进行连接，若表2中没有满足连接条件的记录，则结果集中表2相应的字段填入NULL。
-
右连接同理
例：
select
student.*, class_name, department_name
from
student
left join
classes
on
student.class_no = classes.class_no;
多表连接
方法1：
from 表1,表2,表3 where 表1和表2之间的连接条件 and 表2和表3之间的连接条件
方法2：
from 表1
[连接类型] join 表2 on 表1和表2之间的连接条件
[连接类型] join 表3 on 表2和表3之间的连接条件
例：检索数据库课程不及格的学生
select
student.student_no, student_name, course_name, score
from
course
join
choose
on
course.course_no = choose.course_no
join
student
on
student.student_no = choose.student_no
where
course_name like '%数据库%' and score<60;
联合及嵌套查询
使用union可以将多个select语句的查询结果集组合成一个结果集。
select 字段列表1 from table1
union [all]
select 字段列表2 from table2
- 字段列表1与字段列表2的字段个数必须相同，且具有相同的数据类型。合并产生的新结果集的字段名与字段列表1中的字段名对应
例：检索所有的学生及教师的信息
select
student_no 编号, student_name 姓名, student_contact 电话
from
student
union select
teacher_no, teacher_name, teacher_contact
from
teacher;
修改
update 表名
set 字段名1=值1,字段名2=值2,…,字段名n=值n
[where 条件表达式]
例：将classes表中班级号小于等于3的院系名改为“机电工程学院”
update classes set depetment_name='机电工程学院' where class_no<=3;
例：将课程表中每门课程的课程上限人数增加10人
update course set up_limit=up_limit+10;
删除
delete from 表名 [where 条件表达式]
例：删除“2012计算机应用1班”班的学生信息
use choose;
delete from student where class_name='2012计算机应用1班';
例：删除所有的班级表的信息。
delete from classes;
truncate [table] 表名
例：清空学生表
use choose;
truncate student;
五、约束
- 主键约束（primary key）
- 非空约束（not NULL）
- 默认值约束（default）
- 唯一性约束（unique）
- 外键约束（foreign key）
- 检查约束（check）（借助触发器或符合类型实现）
主键约束primary key
primary key约束用于定义基本表的主键，起唯一标识作用，并且其值不能为NULL，以此来保证实体的完整性。
例：
use choose;
create table student(
student_no char(11) primary key,
student_name char(10) not null,
student_sex char(2),
student_contact char(20) not null,
student_birth date,class_noint
);
例：在choose数据库中创建班级表(班号，班名，系号），并将系号、班号组合设置为主键。
use choose;
create table classes(
class_noint,
class_name char(10) unique,
dept int,
primary key (dept, class_no)
);
非空约束not NULL
要求某个字段取值不能为空。
默认值约束default
向表中插入记录时，如果没有指定某个字段的取值，该字段则采用默认值插入。
status char(6) default "未审核"
唯一性约束unique
用于表名基本表在某一列或多个列的组合上的取值唯一。
class_name char(20) not null unique
注意：主键自带为空和唯一特性，不需要单独声明，非主键但是具有唯一值的用unique。
例：在choose数据库中创建班级表(班号，班名，系号），并将班号设置为主键。
use choose;create table classes( class_no int primary key , class_name char(10) unique, dept int);
外键约束foreign key
外键约束用于有关联的两个或多个表。通过使用主键和外键（或唯一值）之间的关系，使表中的键值在相关表中保持一致。
语法：
constraint 约束名 foreign key（子表字段名） references 外表名（主表字段名） [on delete 级联选项] [on update 级联选项]
例：
constraint st_cl_fk foreign key (class_no) references classes (class_no)
例：将学生表的班号设为外键，参照班级表的班号
use choose;
create table student(
student_no char(11) primary key,
student_name char(10) not null,
student_sex char(2),
student_contact char(20) not null,
student_birth date,
class_no int,
constraint st_cl_fk foreign key (class_no) references classes(class_no)
);
自增型字段auto_increment
优点：
- 自动编号，速度快，按顺序存放，有利于检索
- 数字型，占用空间小，易排序，在程序中传递也方便
- 如果通过非系统增加记录时，可以不用指定该字段，不用担心主键重复问题。
例：建班级表(班号，班名，系号），班号为主键且自增型
use choose;
create table classes(
class_no int auto_increment primary key,
class_name char(10) unique,
dept int
);
六、视图
视图是数据库对象之一，视图中保存的仅是一条select语句（虚表）。即将多个基本表（或视图）中的数据通过视图动态的组织在一起，像普通表一样使用
创建
create view 视图名(视图字段列表)
as select 语句
with [local|cascaded] check option
查看
show tables like ‘view%’;
show create view 视图名;
desc 视图名;
删除
drop view 视图名;
七、数据库编程
1.变量
-
系统变量（必须在前加@@）
-
全局变量
在启动时服务自动初始化
-
会话变量
建立新连接时。会其修改只会影响到当前的会话也就是当前的数据库连接
例如：
-- 获得现在使用的MySQL版本 select @@version; -- 获得系统当前时间，这个可以不用@@ select current_time; -- 设置会话变量sql_select_limit的值设置为50 set @@session.sql_select_limit = 50; -
-
用户变量（用前必须定义和初始化）
-
会话变量
其名称前加@，定义和初始化可直接用set或select
-
局部变量
用户函数或存储过程等程序中，定义和初始化可直接用declare和set或select赋值
-- 创建用户变量name1并赋值为“王林” set @name = '王林'; -- 创建用户变量user1并赋值为1，user2赋值为2，user3赋值为3 set @user1=1, @user2=2, @user3=3; -- 查询用户变量 select @name, @user1, @user2, @user3 -- 使用查询结果给变量赋值 set @studentid=(select studentid from student where studentname='张宏'); select studentname into @studentname from student where studentid='st0109010002'; select studentname,studentid into @sname1,@sid1 from student where studentid=@studentid; -- 将用户变量用于查询 select * from grade where StudentID = @studentid; -- 用select语句将表中数据赋值给变量 select @mg = max(grade) from grade; -- 查询用户变量 select @studentid, @mg;
-
运算符
| 运算符 | 含义 |
|---|---|
| = | 等于 |
| > | 大于 |
| < | 小于 |
| >= | 大于等于 |
| <= | 小于等于 |
| <>、!= | 不等于 |
| <==> | 相等或都等于空 |
| is null | 是否为NULL |
| between…and… | 是否在区间内 |
| in | 是否在集合内 |
| like | 模式匹配 |
| regexp | 正则表达式模式匹配 |
| not或! | 逻辑非 |
| and或&& | 逻辑与 |
| or或|| | 逻辑或 |
| xor | 逻辑异或 |
| & | 按位与 |
| | | 按位或 |
| ^ | 按位异或 |
| ~ | 按位取反 |
| >> | 位右移 |
| << | 位左移 |
2.系统函数
数学函数
字符串函数
| 函数名 | 作用 | 举例 |
|---|---|---|
| LEFT | 获取左边字符串 | select left(‘赵钱孙李’,3) |
| RIGHT | 截取右边字符串 | select right(‘赵钱孙李’,3) |
| LOWER | 转换为小写字符串 | select lower(‘ABCDEFG’) |
| UPPER | 转换为大写字符串 | select upper(‘abcdefg’) |
| REVERSE | 反序字符串 | select reverse(‘赵钱孙李’) |
| LENGTH | 获取字符串长度 | select length(‘天下之大，无奇不有’) |
| REPEAT | 重复生成字符串 | select repeat (‘hello’,5) |
| RTRIM | 清除右边空格 | select rtrim('123456789 ') |
| POSITOIN | 字符所在位置 | select position(‘l’ in ‘hello’); |
| CONCAT | 连接字符串 | select concat(‘My’,’_’,‘SQL’); |
例如：
-- 查找学生表家庭住址的前三个字符
select left(HomeAddr, 3) from student;
-- 将教师的姓名，性别和职称连接
select concat(Teachername, Sex, Profession) from teacher;
-- 查询学生姓名的长度
select length(StudentName) from student;
条件控制函数
| 函数名 | 函数作用 |
|---|---|
| if(条件，v1，v2) | 条件为真返回v1,否则v2 |
| ifnull(v1，v2) | 如果v1为null,返回v2 |
| nullif(v1，v2) | 如果v1等于v2,返回null，否则返回v1 |
| case …when… then …else default | 条件与when中值相等，则返回then对应的结果，否则返回default |
例如：
-- 根据分数显示成绩等级
set @score1=40, @score2=77;
select if(@score1 >= 60, '及格', '不及格') 数学成绩, if(@socre2>=60, '及格', '不及格') 语文成绩;
-- 根据分数显示成绩等级
select ifnull(@score1, '没有成绩') 成绩;
-- 转换今天的星期值
set @t=now(), @week_no=weekday(@t);
set @week=case @week_no
when 0 then '星期一'
when 1 then '星期二'
when 2 then '星期三'
when 3 then '星期四'
when 4 then '星期五'
else '周末'
end;
select @t,@week_no,@week
系统信息函数
日期时间函数
其他函数
-- found_rows()：返回最后一个select查询的进行检索的总行数
select found_rows();
-- cast()：将一个值转换为指定的数据类型
select cast(now() as char);
3.自定义函数创建
create function 函数名(参数1,参数2,…)
returns 数据类型
[函数选项]
begin
函数体;
return 语句;
end;
注意：
-
参数和java语法不同，例如是(x smallint)，而非(smallint x)
-
函数选项：
- no sql
- reads sql data
-
函数里变量的定义：declare 变量名 类型;
-
建议函数名前加fn_前缀，以示区分
例如
-- 创建一个名为fn_addTwoNumber()的函数
use xkgl;
delimiter $$
create function fn_addTwoNumber(x smallint, y smallint)
returns smallint
no sql
begin
return x+y;
end$$
-- 创建函数fn_get_name()，根据学生学号查询姓名
delimiter $$
create function fn_get_name(no char(12))
returns char(20)
reads sql data
begin
declare name char(20);
select studentname into name from student where studentid=no;
if isnull(name) then set name="无此人";
end if
return name;
end$$
4.自定义函数管理
查看
查看当前数据库中所有的自定义函数信息
show function status;
模糊查询自定义函数
show function status like 模式
show function status like 'fn_get%';
查看指定函数详细信息
show create function fn_get_name;
删除
drop function 函数名
drop function get_name_in;
5.条件语句
if…else
if 条件表达式1 then 语句块1;
[elseif 条件表达式2 then 语句块2]…
[else 语句块n]
end if;
- 注意：end if后必须以;结束
例如：
-- 创建函数get_name1，使该函数根据学生学号或者教师工号返回他们的姓名信息
delimiter $$
create function fn_get_name1(no char(20), role char(20))
returns char(20)
begin
declare name char(20);
if (role='student') then
select studentname into name from student where studentid=no;
elseif(role='teacher') then
select teachername into name from teacher where teacherid=no;
else set name='输入有误！';
end if;
if isnull(name) then set name='无此人';
end if;
return name;
end$$
case
分为简单case和搜索case
case 条件
when 条件值 then 操作;
[when 条件值 then 操作;]
…
[else 操作;]
end case;
case
when 条件 then 操作;
[when 条件 then 操作;]
…
[else 操作;]
end case;
- 注意：end case后必须以;结束
例如：
delimiter $$
create function fn_get_week(week_noint) returns char(20)
no sql
begin
declare week char(20);
case week_no
when 0 then set week='星期一';
when 1 then set week='星期二';
when 2 then set week='星期三';
when 3 then set week='星期四';
when 4 then set week='星期五';
else set week='周末';
end case;
return week;
end$$
6.循环语句
有三种循环语句,while、repeat、loop
while
[循环标签:]while 条件表达式 do
循环体;
end while[循环标签];
-- 创建函数get_sum实现1到n的累加运算
DELIMITER $$
CREATE FUNCTION fn_get_sum(n INT)
RETURNS INT
NO SQL
BEGIN
DECLARE start_, sum_ INT;
SET start_=1, sum_=0;
WHILE start_ <= n DO
SET sum_ = start_ + sum_;
SET start_ = start_ + 1;
END WHILE;
RETURN sum_;
END$$
-- 创建函数get_sum1实现1到n的累加运算,n大于100也只算到100
delimiter $$
create function fn_get_sum1(n int) returns int
no sql
begin
declare start, sum int default 0;
addnum: while start<n do
set start = start + 1;
set sum = sum + start;
if (start =100) then leave addnum;
end if;
end while addnum;
return sum;
end$$
-- 创建函数get_sum2实现1到n的奇数累加运算
delimiter $$
create function fn_get_sum2(n int) returns int
no sql
begin
declare start, sum intdefault 0;
addnum: while start<n DO
set start = start + 1;
if (start % 2)=0 then iterate addnum;
end if;
set sum=sum+start;
end while addnum;
returnsum;
end$$
repeat
[循环标签:]repeat
循环体;
until 条件表达式
end repeat [循环标签]；
其实就是do while
-- 创建函数get_sum3实现1到n的累加运算
delimiter $$
create function fn_get_sum3(n int) returns int
no sql
begin
declare start, sum intdefault 0;
repeat
set start = start + 1;
set sum=sum+start;
until start=n
end repeat;
return sum;
end$$
loop
[循环标签:] loop
循环体;
if 条件表达式 then
leave [循环标签];
end if;
end loop;
-- 创建函数get_sum4实现1到n的累加运算。
delimiter $$
create function fn_get_sum4(n int) returns int
no sql
begin
declare start,sum int default 0;
addnum: loop
set start = start + 1;
set sum=sum+start;
if start=n then leave addnum;
end if;
end loop;
return sum;
end
$$
7.存储过程创建
存储过程（Stored procedure）是在数据库中定义的一些完成特定功能的SQL语句集合，经编译后存储在数据库中。
优点：
- 存储过程能实现较快的执行速度
- 存储过程能够减少网络流量
- 存储过程可被作为一种安全机制来充分利用
create procedure 存储过程名(参数1,参数2,…)
[存储过程选项]
begin
存储过程语句块;
end;
-
必须说明参数类型（[in|out|inout]）和参数的数据类型（char, int）
- 例如 in a1 int, out a2 char(6)
-
建议过程名前加pr_前缀
-
存储过程选项：no sql, reads sql data, modifies sql data
-
变量的定义：declare 变量
例：创建名为get_num的存储过程，根据学号统计选修课程数输出保存到变量中
delimiter $$
create procedure pr_get_num(in st_no1 char(12),out ch_num1 int)
reads sql data
begin
select count(*) into ch_num1 from grade where studentid=st_no1;
end$$
例：创建名为count_dsn的存储过程，根据系号，统计不同系的学生人数。
delimiter $$
create procedure pr_count_dsn1(in did char(10))
reads sql data
begin
select departmentid系名,count(*) 人数
from class join student on student.classid=class.classid
where departmentid=did;
end$$
调用：
CALL 存储过程名([参数[,…]])
例：调用存储过程pr_get_num(in st_no1 char(12),out ch_num1 int)
set @st_no1 = 'St0109010002';
set @ch_num1 = 0;
call pr_get_num(@st_no1, @ch_num1);
select @ch_num1;
call pr_get_num('St0109010002', @count);
select @count;
例：调用存储过程pr_count_dsn(in did char(10))
call pr_count_dsn('dp01');
-- 完整版
delimiter $$
create procedure pr_get_num(in st_no1 char(12),out ch_num1 int)
reads sql data
begin
select count(*) into ch_num1 from grade where studentid=st_no1;
end$$
set @st_no1='st0109010002';
set @st_num=0;
call pr_get_num(@st_no1,@st_num);
select @st_num;
8.存储过程管理
查看
查看存储过程的状态
show procedure status like ‘pr%’;
查看存储过程的定义
show create procedure pr_get_num;
show create procedure pr_get_num\G
删除
drop procedure [endexits] 名字
9.触发器
触发器是数据库对象之一，实在对表进行操作时自动执行的一段SQL程序（类似存储过程或函数）。
特点：
- 针对特定的表
- 不能接受和传递参数
- 不能手工调用执行，而是由插入、更新和删除时间触发执行
八、数据库设计
数据库设计阶段
- 需求分析
- 概念设计
- 逻辑结构设计
- 物理设计
- 实施
- 运行和维护
ER模型设计
概念设计（E-R图的设计）：
将收集到的数据进行分类和全局性描述，建立实体、实体属性和实体间的联系
- E-R图由实体、属性、联系三个要素构成
实体
实体（Entity）是具有公共性质的可相互区别的现实世界对象的集合。用**矩形框**表示。实体中每个具体的记录值被称为实例。
属性
属性（Attribute）是实体所具有的某一特征或性质。一个实体的属性多少由用户的信息需求决定，属性在E-R图中用椭圆表示。能唯一标识实体的属性被称为码（Key）。
联系
关系（Relation）表示实体间存在的联系或对应关系，用菱形表示，菱形框内写明联系名，同时在无向边旁标上联系的类型（1:1, 1:n或m:n），联系也可以有属性。
三个实体型之间联系示例
九、事务
概念：如果一个包含多个步骤的业务操作被事务管理，那么这些操作要么同时成功，要么同时失败。
四大特征：原子性、持久性、隔离性、一致性。
开启、回滚、提交
开启事务
start transaction
回滚
rollback
提交
commit
CREATE TABLE ACCOUNT(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
balance DOUBLE
);
INSERT INTO ACCOUNT (NAME, balance) VALUES ("zhangsan",1000), ("lisi",1000);
SELECT * FROM ACCOUNT;
UPDATE ACCOUNT SET balance = 1000;
-- 张三给李四转账500元
-- 0.开启事务
START TRANSACTION;
-- 1.张三账户-500
UPDATE ACCOUNT SET balance = balance -500 WHERE NAME = 'zhangsan';
-- 2.李四账户+500
UPDATE ACCOUNT SET balance = balance +500 WHERE NAME = 'lisi';
-- 发现执行没有问题，提交事务
COMMIT;
-- 发现出问题了，回滚事务
ROLLBACK;
事务提交的两种方式：
- 自动提交
- MySQL自动提交
- 一条DML（增删改）语句会自动提交一次事务
- 手动提交
- Oracle默认手动提交事务
- 需要开启事务，再提交
修改事务的默认提交方式：
-
查看事务的默认提交方式
select @@autocommit; -- 1 代表自动提交 0 代表手动提交 -
修改默认提交方式
set @@autocommit = 0;
事务的隔离级别
概念：多个事务之间是隔离的，相互独立的。但是如果多个事务操作同一批数据则会引发一些问题，设置不同的隔离级别就可以解决这些问题。
存在问题：
- 脏读：一个事物读取到另一个事务中没有提交的数据。
- 不可重复读（虚读）：在同一个事务中，两次读取到的数据不一样。
- 幻读：一个事物操作（DML）数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改。
-
隔离级别：
-
read uncommitted：读未提交
- 产生的问题：脏读、不可重复读、幻读
-
read committed：读已提交（Oracle）
- 产生的问题：不可重复读、幻读
-
repeatable read：课重复读（MySQL默认）
- 产生的问题：幻读
-
serializable：串行化
- 可以解决所有的问题
-
-
注意：隔离级别从小到大安全性越来越高，但是效率越来越低
-
数据库查询隔离级别：
select @@tx_isolation; -
数据库设置隔离级别：
set global transaction isolation level 级别字符串
1348
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
