一、基本概念
1SQL语句分类
1、学习MySQL主要还是学习通用的SQL语句，那么SQL语句包括增删改查，SQL语句怎么分类呢？
DQL（数据查询语言）: 查询语句，凡是select语句都是DQL。
DML（数据操作语言）：insert delete update，对表当中的数据进行增删改。
DDL（数据定义语言）：create drop alter，对表结构的增删改。
TCL（事务控制语言）：commit提交事务，rollback回滚事务。(TCL中的T是Transaction)
DCL（数据控制语言）: grant授权、revoke撤销权限等。
2.约束
非空约束(not null)：约束的字段不能为NULL
唯一约束(unique)：约束的字段不能重复
主键约束(primary key)：约束的字段既不能为NULL，也不能重复（简称PK）
外键约束(foreign key)：…（简称FK）外键用来在两个表的数据之间建立链接，它可以是一列或者多列。一个表可以有一个或多个外键。外键对应的是参照完整性，一个表的外键可以为空值，若不为空值，则每一个外键值必须等于另一个表中主键的某个值。
[CONSTRAINT 外键名] FOREIGN KEY(字段名1 [ ,字段名2, … ])REFERENCES 主表名(主键列1 [ ,主键列2, … ])
CREATE TABLE borrow(borrowid char(6) PRIMARY KEY,borrowbookid char(6),borrowreaderid char(6),borrowdate datetime,num int(2),CONSTRAINT fk_bks_brw1 FOREIGN KEY(borrowbookid) REFERENCES books2(bookid)) ENGINE=InnoDB;
检查约束(check)：注意Oracle数据库有check约束，但是mysql没有，目前mysql不支持该约
1.查看约束
show keys from tableName
2.添加、删除主键
修改数据类型: ALTER TABLE 表名 MODIFY 字段名 数据类型;
为books表的bookid列添加主键约束，并设置自动递增ALTER TABLE books MODIFY COLUMN bookid INT PRIMARY KEY AUTO_INCREMENT;
在MySQL中删除主键需要两步：
ALTER TABLE 表名 CHANGE 原字段名 新字段名 新数据类型;
(1)如果有auto_increment，先删除之;
(2)删除主键约束 primary key
ALTER TABLE books CHANGE bookid boookid INT;ALTER TABLE books DROP PRIMARY KEY;
3、添加、删除非空约束
格式: ALTER TABLE 表名 MODIFY 列名 VARCHAR(225) NOT NULL
【例】为books表的bookname列添加非空约束。ALTER TABLE books MODIFY bookname varchar(100) not null;删除非空约束：ALTER TABLE books MODIFY bookname varchar(100);
4、添加、删除唯一约束
ALTER TABLE 表名 ADD UNIQUE(列名);ALTER TABLE tbl_name DROP INDEX index_name
二、使用步骤
第一步：登录mysql数据库管理系统
mysql -uroot -p123456
第二步：查看有哪些数据库
show databases;
第三步：创建属于我们自己的数据库
create database bjpowernode;
第四步：使用bjpowernode数据库
use bjpowernode;
第五步：查看当前使用的数据库中有哪些表？
show tables;
第六步：初始化数据
mysql> source D:\course\05-MySQL\resources\bjpowernode.sql
三、表的使用
一、创建表
create table student(#int类型, 不为空, 自增,默认为1id int not null auto_increment,name varchar(20),#可以为空,默认为18age int null default 18,sex char(2) null,primary key(id));
二、修改表结构
1.修改字段数据类型
ALTER TABLE 表名 MODIFY 字段名 数据类型;ALTER TABLE books MODIFY bookname VARCHAR(100);
2.修改字段名
ALTER TABLE 表名 CHANGE 原字段名 新字段名 新数据类型;ALTER TABLE books CHANGE author bookauthor VARCHAR(50);
3.添加字段
ALTER TABLE 表名 ADD 新字段名 数据类型alter table student add age char(20);
3.删除字段
alter table student drop column age;
4.修改字段的排列顺序
“FIRST “指 将字段1改为数据表的第一个字段； “AFTER 字段2”是指将字段1插入到字段2的后面。
ALTER TABLE 表名 MODIFY 字段1 数据类型 FIRST | AFTER 字段2;ALTER TABLE books MODIFY press VARCHAR(40) FIRST;ALTER TABLE books MODIFY press VARCHAR(40) AFTER bookauthor;
3、删除表
drop table student;
三、表数据的增删改
1.添加
insert into student(name,sex) values("xxx","女"); #id自增，age默认18，所以没添加
注意:一次性插入多个数据
insert into student(id,name,age,sex) values(2,"csy",17,"男"),(3,"xxx",18,"女"),(4,"yyy",19,"女");
2.修改
update student set name="xxy",age=7 where id=1;
3.删除
delete from student where id =1;
truncate table 可以清空表，也就是删除所有行。
truncate table student;
四、查询
一、单表查询
1.格式
语法格式：
select
字段,字段…
from
表名
where
条件;
执行顺序:先from,然后where,最后select
注意:A:给查询结果的列命名
select 字段名1,字段名2 yearsal,…… from 表名;
select 字段名1,字段名2 “yearsal”,…… from 表名;
select 字段名1,字段名2 as yearsal,…… from 表名;
以上三种方式都可以给列命名。
2.distinct
含义:不同的。相同的值只会出现一次。
select distinct age from student;
select ename,distinct job from emp;以上的sql语句是错误的。记住：distinct只能出现在所有字段的最前面。
3.limit
返回前 5 行：
select * from student limit 5;
select * from student limit 0,5;#limit 起始下标,长度
返回第 3 ~ 5 行：
select * from student limit 2,3; #开始从0开始
通用的标准分页sql？
每页显示3条记录：
第1页：0, 3 起始长度，长度
第2页：3, 3
第3页：6, 3
第4页：9, 3
每页显示pageSize条记录
第pageNo页:limit(当前页码-1)*每页条数，每页条数
二、连接查询
1.交叉连接
- 将第一个表的所有行分别与第二个表的每个行连接形成一个新的数据表。
- 行数等于两个表的行数的乘积，列数等于两个表的列数的和。如表A有3行，表B有2行，表A和B全连接后得到6行（3x2=6)的表。
2、内连接
等值连接：在连接条件中使用等号来比较连接列的列值，其查询结果中 包括重复列。 在等值连接中，两个表的连接条件通过表1 .主键=表2.外键”的形式。
自然连接：与等值连接相同，都是在连接条件中使用比较运算符，但结果集中不包括重复列。
1、内连接之等值连接
1：内连接之等值连接：最大特点是：条件是等量关系。
select
e.ename,d.dname
from
emp e
join
dept d
on
e.deptno = d.deptno;
2、内连接之非等值连接
2：内连接之非等值连接：最大的特点是：连接条件中的关系是非等量关系。
select
e.ename,e.sal,s.grade
from
emp e
join
salgrade s
on
e.sal between s.losal and s.hisal;
3、自连接
2：自连接：最大的特点是：一张表看做两张表。自己连接自己。
员工的领导编号 = 领导的员工编号
select
a.ename as ‘员工名’,b.ename as ‘领导名’
from
emp a
join
emp b
on
a.mgr = b.empno;
4、外连接
3：外连接：（左外连接/左连接）
select
a.ename ‘员工’, b.ename ‘领导’
from
emp a
left join
emp b
on
a.mgr = b.empno;
外连接：
假设A和B表进行连接，使用外连接的话，AB两张表中有一张表是主表，一张表是副表，主要查询主表中
的数据，捎带着查询副表，当副表中的数据没有和主表中的数据匹配上，副表自动模拟出NULL与之匹配。
4、三张表怎么查询？
4、三张表怎么连接查询？
案例：找出每一个员工的部门名称以及工资等级。
表示：A表和B表先进行表连接，连接之后A表继续和C表进行连接。
select
e.ename,d.dname,s.grade
from
emp e
join
dept d
on
e.deptno = d.deptno
join
salgrade s
on
e.sal between s.losal and s.hisal;
解析:便与理解 前两张图的连接
+————+——————+
| ename | dname |
+————+——————+
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
………….
+————+——————+
三、子查询
- select语句中嵌套select语句，被嵌套的select语句称为子查询。
- 子查询可以出现在哪里呢？
select
..(select).
from
..(select).
where
..(select).
3.可以将子查询的结果作为 WHRER 语句的过滤条件：
select
ename,sal
from
emp
where
sal >min(sal) 这种是错误的，where后面不能直接使用分组函数
正确的做法：
先查询最低工资是多少<br />select ename,sal from emp where sal>(select min(sal) from emp);<br />from后面的子查询：<br />注意：from后面的子查询，可以将子查询的查询结果当做一张临时表。（技巧）
案例：找出每个部门平均薪水的等级。
第一步：找出每个部门平均薪水（按照部门编号分组，求sal的平均值）
select deptno,avg(sal) as avgsal from emp group by deptno;
+————+——————-+
| deptno | avgsal |
+————+——————-+
| 10 | 2916.666667 | 的等级
| 20 | 2175.000000 | 的等级
| 30 | 1566.666667 | 的等级 临时表t
+————+——————-+
第二步：将以上的查询结果当做临时表t，让t表和salgrade s表连接，条件是：t.avgsal between s.losal and s.hisal
select
t.*,s.grade
from
(select deptno,avg(sal) as avgsal from emp group by deptno) t
join
salgrade s
on
t.avgsal between s.losal and s.hisal;
+————+——————-+———-+
| deptno | avgsal | grade |
+————+——————-+———-+
| 30 | 1566.666667 | 3 |
| 10 | 2916.666667 | 4 |
| 20 | 2175.000000 | 4 |
+————+——————-+———-+
四、组合查询
union
查询工作岗位是MANAGER和SALESMAN的员工?
select ename,job from emp where job in(‘MANAGER’,’SALESMAN’);
union （可以将查询结果集相加）要求：列数相同，数据类型也得一样。
select ename,job from emp where job = ‘MANAGER’
union
select ename,job from emp where job = ‘SALESMAN’;
五、排序、过滤、通配符、计算字段
排序
- ASC ：升序（默认）
- DESC ：降序
可以按多个列进行排序，并且为每个列指定不同的排序方式：
selectename,salfromemporder bysal asc , ename asc;
注意://sal在前起主导作用，只有sal相等的时候，才会考虑启用ename排序, 如果第
一列数据中所有值都是唯一的，将不再对第二列进行排序。
过滤
不进行过滤的数据非常大，导致通过网络传输了多余的数据，从而浪费了网络带宽。因此尽量使用 SQL 语句来过滤不必要的数据，而不是传输所有的数据到客户端中然后由客户端进行过滤。
SELECT *FROM mytableWHERE col IS NULL;
下表显示了 WHERE 子句可用的操作符
| 操作符 | 说明 |
|---|---|
| = | 等于 |
| < | 小于 |
| > | 大于 |
| <> != | 不等于 |
| <= !> | 小于等于 |
| >= !< | 大于等于 |
| BETWEEN… AND…(数字是闭区间) | 在两个值之间 |
| IS NULL | 为 NULL 值 |
应该注意到，NULL 与 0、空字符串都不同。
AND 和 OR 用于连接多个过滤条件。优先处理 AND，当一个过滤表达式涉及到多个 AND 和 OR 时，可以使用 () 来决定优先级，使得优先级关系更清晰。
IN 操作符用于匹配一组值，其后也可以接一个 SELECT 子句，从而匹配子查询得到的一组值。
理解:in后面的值不是区间，是具体的值。
NOT 操作符用于否定一个条件。
EXISTS 返回一个布尔类型的结果。如果子查询结果能够返回至少一行记录，则EXISTS的结果为TRUE，此时主查询语句将被执行；反之，如果子查询结果没有返回任何一行记录，则EXISTS结果为FALSE，此时主查询语句将不被执行。
ANY使用ANY关键字时，只要满足子查询结果中的任意一条，就可以通过该条件来执行外层查询语句。使用ALL关键字时，只有满足子查询语句返回的所有结果，才能执行外层查询语句。
通配符
通配符也是用在过滤语句中，但它只能用于文本字段。
- % 匹配 >=0 个任意字符；
- _ 匹配 ==1 个任意字符；
- [ ] 可以匹配集合内的字符，例如 [ab] 将匹配字符 a 或者 b。用脱字符 ^ 可以对其进行否定，也就是不匹配集合内的字符。
使用 Like 来进行通配符匹配。
SELECT *FROM mytableWHERE col LIKE '[^AB]%'; -- 不以 A 和 B 开头的任意文本
计算字段
在数据库服务器上完成数据的转换和格式化的工作往往比客户端上快得多，并且转换和格式化后的数据量更少的话可以减少网络通信量。
计算字段通常需要使用 AS 来取别名，否则输出的时候字段名为计算表达式。
SELECT col1 * col2 AS aliasFROM mytable;
CONCAT() 用于连接两个字段。许多数据库会使用空格把一个值填充为列宽，因此连接的结果会出现一些不必要的空格，使用 TRIM() 可以去除首尾空格。
SELECT CONCAT(TRIM(col1), '(', TRIM(col2), ')') AS concat_colFROM mytable;
六、函数
各个 DBMS 的函数都是不相同的，因此不可移植，以下主要是 MySQL 的函数。
汇总
| 函 数 | 说 明 |
|---|---|
| AVG() | 返回某列的平均值 |
| COUNT() | 返回某列的行数 |
| MAX() | 返回某列的最大值 |
| MIN() | 返回某列的最小值 |
| SUM() | 返回某列值之和 |
AVG() 会忽略 NULL 行。
使用 DISTINCT 可以汇总不同的值。
SELECT AVG(DISTINCT col1) AS avg_colFROM mytable;
文本处理
| 函数 | 说明 |
|---|---|
| LEFT() | 左边的字符 |
| RIGHT() | 右边的字符 |
| LOWER() | 转换为小写字符 |
| UPPER() | 转换为大写字符 |
| LTRIM() | 去除左边的空格 |
| RTRIM() | 去除右边的空格 |
| LENGTH() | 长度 |
| SOUNDEX() | 转换为语音值 |
其中， SOUNDEX() 可以将一个字符串转换为描述其语音表示的字母数字模式。
SELECT *FROM mytableWHERE SOUNDEX(col1) = SOUNDEX('apple')
日期和时间处理
- 日期格式：YYYY-MM-DD
- 时间格式：HH:
MM:SS | 函 数 | 说 明 | | —- | —- | | ADDDATE() | 增加一个日期（天、周等） | | ADDTIME() | 增加一个时间（时、分等） | | CURDATE() | 返回当前日期 | | CURTIME() | 返回当前时间 | | DATE() | 返回日期时间的日期部分 | | DATEDIFF() | 计算两个日期之差 | | DATE_ADD() | 高度灵活的日期运算函数 | | DATE_FORMAT() | 返回一个格式化的日期或时间串 | | DAY() | 返回一个日期的天数部分 | | DAYOFWEEK() | 对于一个日期，返回对应的星期几 | | HOUR() | 返回一个时间的小时部分 | | MINUTE() | 返回一个时间的分钟部分 | | MONTH() | 返回一个日期的月份部分 | | NOW() | 返回当前日期和时间 | | SECOND() | 返回一个时间的秒部分 | | TIME() | 返回一个日期时间的时间部分 | | YEAR() | 返回一个日期的年份部分 |
mysql> SELECT NOW();
2018-4-14 20:25:11
数值处理
| 函数 | 说明 |
|---|---|
| SIN() | 正弦 |
| COS() | 余弦 |
| TAN() | 正切 |
| ABS() | 绝对值 |
| SQRT() | 平方根 |
| MOD() | 余数 |
| EXP() | 指数 |
| PI() | 圆周率 |
| RAND() | 随机数 |
七、分组
把具有相同的数据值的行放在同一组中。
可以对同一分组数据使用汇总函数进行处理，例如求分组数据的平均值等。
指定的分组字段除了能按该字段进行分组，也会自动按该字段进行排序。
SELECT col, COUNT(*) AS numFROM mytableGROUP BY col;
GROUP BY 自动按分组字段进行排序，ORDER BY 也可以按汇总字段来进行排序。
SELECT col, COUNT(*) AS numFROM mytableGROUP BY colORDER BY num;
WHERE 过滤行，HAVING 过滤分组，行过滤应当先于分组过滤。
SELECT col, COUNT(*) AS numFROM mytableWHERE col > 2GROUP BY colHAVING num >= 2;
分组规定：
- GROUP BY 子句出现在 WHERE 子句之后，ORDER BY 子句之前；
- 重点结论：在一条select语句当中，如果有 group by语句的话，select 后面只能跟：参加分组的字段，以及分组函数，其他的一律不跟。
- NULL 的行会单独分为一组；
- 大多数 SQL 实现不支持 GROUP BY 列具有可变长度的数据类型。
分组函数不能直接在where后面。（因为分组函数在使用的时候必须先分组之后才能使用，
where在执行的时候还没有分组所以where后面不能出现分组函数。）
八、视图
什么是视图？
view:站在不同的角度去看待同一份数据。
视图是虚拟的表，本身不包含数据，也就不能对其进行索引操作。
用视图做什么?
对视图的操作和对普通表的操作一样。通过对视图的操作，会影响到原表数据。
视图具有如下好处：
- 简化复杂的 SQL 操作，比如复杂的连接；
- 只使用实际表的一部分数据；
- 通过只给用户访问视图的权限，保证数据的安全性；
- 更改数据格式和表示。
创建视图
```sql CREATE [OR REPLACE] VIEW 视图名 [(字段名,…)] AS SELECT语句 [WITH [CASCADED|LOCAL] CHECK OPTION]
```sqlCREATE VIEW myview ASSELECT Concat(col1, col2) AS concat_col, col3*col4 AS compute_colFROM mytableWHERE col5 = val;
参数说明如下：
- [OR REPLACE]：可选项，表示可以替换已有的同名视图。（如果数据库中已经有了这个视图，不写这个关键字则会导致创建失败。）
- [(字段名,…)]：可选项，声明视图中使用的字段名。各字段名由逗号分隔开，字段名的数目必须等于SELECT语句检索的列数。该选项省略时视图的字段名与源表或视图的字段名相同。
- SELECT语句：用来创建视图的SELECT语句，可在SELECT语句中查询多个表或视图。
- WITH CHECK OPTION：可选项，强制所有通过视图修改的数据必须满足SELECT语句中指定的选择条件。这样可以确保数据修改后，仍可通过视图看到修改的数据。当视图是根据另一个视图定义时，WITH CHECK OPTION给出LOCAL和CASCADE两个可选参数，它们决定了检查测试的范围。LOCAL表示只对定义的视图进行检查，CASCADED表示对所有视图进行检查，该选项省略时默认值为CASCADED。
WITH CHECK OPTION解释说明
我们来看下面的例子:
更新例子
create or replace view testviewasselect empno,ename from emp where ename like "M%'with check option;
这里我们创建了一个视图，并使用了with check option来限制了视图。然后我们来看一下视图包含的结果;
select * from testview得到:EMPNO ENAME7654 MARTIN7934 MILLER
这两条记录
然后我们在试图将其中一条更新:
update testviewset ename ="Mike'where empno = 7654;
OK,这条更新语句可以执行，并没有什么问题，但是当我们执行另一条更新时:
update testviewset ename = "Robinwhere empno = "7654';
就会出现ORA-01402:视图WITH CHECK OPTIDN违反where子句的错误，这是因为什么呢?
这是因为前面我们在创建视图时指定了witch check option关键字，这也就是说，更新后的每一条数据仍然要满足创建视图时指定的where条件,所以我们这里发生了错误ORA-01402。
但是需要说明的时，虽然指定了with check option，我们还是可以删除视图中的数据。例如上例中，我们可以使用delete from test where where empno = 7654
——————————————————————————————————————
插入例子
我创建一个视图:
create view ls_studentasselect sno,sname,sagefrom studentwhere sdept="IS"with check option;
加上了with check option;后，不能执行插入操作:
insert into is_student
values(‘95100”,”李娜”,12)
什么原因?不加上with check option则可以!
with check option可以这么解释:通过视图进行的修改，必须也能通过该视图看到修改后的结果。比如你insert，那么加的这条记录在刷新视图后必须可以看到;如果修改，修改完的结果也必须能通过该视图看到;如果删除，当然只能删除视图里有显示的记录。
—->而你只是查询出sdept=’is’的纪录，你插入的根本不符合sdept=’is’呀，所以就不行
默认情况下，由于行通过视图进行添加或更新，当其不再符合定义视图的查询的条件时，它们即从视图范围中消失。例如，可创建一个查询，从而定义一个视图以在表中检索所有员工薪水低于$30,000 的行。如果该员工的薪水涨到了$32,000，则查询视图时该特定员工将不再出现，因其薪水不符合视图所设的标准。但是，WITH CHECK OPTION子句强制所有数据修改语句均根据视图执行，以符合定义视图的 SELECT语句中所设的条件。如果使用该子句，修改行时需考虑到不让它在修改完后从视图中消失。任何可能导致行消失的修改都会被取消,并显示错误信息。
视图操作
查看视图信息
1.查看视图基本信息
DESC 视图名;
2.查看视图的定义
SHOW TABLE STATUS LIKE ‘视图名’
3.查看视图的详细定义
SHOW CREATE VIEW 视图名
4.查看视图详细信息
SELECT * FROM 视图名;
修改视图
视图被创建之后，若其关联的基本表的某些字段发生变化，则需要对视图进行修改，从而保持视图与基本表的一致性。
/*1.创建reader_view 其字段为readers表中 readerid，readername,age2.将readers表中readerid改为int3.将reader_view中readerid也修改为int4.向reader_view中插入一条记录*/
(1)CREATE OR REPLACE VIEW语句
CREATE OR REPLACE VIEW 视图名[(字段名,…)]AS SECLECT语句[WITH [CASCADED|LOCAL] CHECK OPTION]
【例1】使用CREATE OR REPLACE VIEW语句修改视图borrow_view，增加num一列。
CREATE OR REPLACE VIEW borrow_viewASSELECT borrowid,bookid,readerid,num FROM borrow;SELECT * FROM borrow_view;
(2)ALTER VIEW语句
ALTER VIEW 视图名[(列名,…)]AS SECLECT语句[WITH [CASCADED|LOCAL] CHECK OPTION]
【2】使用ALTER VIEW语句修改视图borrow_view，筛选出借阅数量大于3的记录。
ALTER VIEW borrow_viewAS SELECT borrowid,bookid,readerid,numFROM borrowWHERE num >= 3 WITH CHECK OPTION;
更新视图
- 更新视图是指通过视图来插入、修改和删除视图中的数据。因为视图是一个虚拟表，所以更新视图就是更新其关联的基本表中的数据。
要通过视图更新基本表数据，必须保证视图是可更新视图，即可以在INSERT、UPDATE或DELETE等语句当中使用它们。对于可更新视图，视图中的行和基本表中的行必须具有一对一的关系。
1.插入数据
插入数据的操作是针对视图中的字段的插入操作，而不是针对基表中的所有的字段的插入操作。由于进行插入操作的视图不同于基表，所以使用视图插入数据要满足一定的限制条件。
使用INSERT语句进行插入操作的视图必须能够在基表中插入数据，否则插入操作会失败。
- 如果视图上没有包括基表中所有属性为NOT NULL 的字段，那么插入操作会由于那些字段的NULL值而失败。
- 如果在视图中使用聚合函数的结果，或者是包含表达式计算的结果，则插入操作不成功。（使用聚合函数视图为不可更新视图）
- 不能在使用了DISTINCT,UNION，TOP，GROUP BY 或HAVING语句的视图中插入数据。(使用以上关键字视图为不可更新视图）
- 如果在创建视图的CREATE VIEW语句中使用了WITH CHECK OPTION ，那么所有对视图进行修改的语句必须符合WITH CHECK OPTION中限定条件。
- 对于由多个基表联接查询而生成的视图来说，一次插入操作只能作用于一个基表上。
【例1】向视图borrow_view中添加一条新的记录
INSERT INTO borrow_viewVALUES('B0066','C0001','S1001',4);SELECT * FROM borrow_view;SELECT * FROM borrow;
2.修改数据
- 当视图是来自多个基表中的数据时，与插入操作一样，每次更新操作只能更新一个基表中的数据，如果通过视图修改存在于多个基表中的数据时，则对不同的基表要分别使用UPDATE语句来实现。
- 在视图中使用UPDATE语句进行更新操作也受到与插入操作一样的限制。
【例1】将视图borrow_view中的所有借阅数量加1。
UPDATE borrow_view SET num = num + 1;
3.删除数据
- 当一个视图联接了两个以上的基表时，对数据的删除操作则不允许的。
【例1】删除视图borrow_view中借阅号为“B0066”的记录。
DELETE FROM borrow_view WHERE borrowid = 'B0066';
九、存储过程
存储过程可以看成是对一系列 SQL 操作的批处理。
使用存储过程的好处：
- 代码封装，保证了一定的安全性；
- 代码复用；
- 由于是预先编译，因此具有很高的性能。
命令行中创建存储过程需要自定义分隔符，因为命令行是以 ; 为结束符，而存储过程中也包含了分号，因此会错误把这部分分号当成是结束符，造成语法错误。
包含 in、out 和 inout 三种参数。
给变量赋值都需要用 select into 语句。
每次只能给一个变量赋值，不支持集合的操作。
delimiter //## 存储过程名 参数名 参数类型create procedure myprocedure( out ret int )begindeclare y int;select sum(col1)from mytableinto y;select y*y into ret;end //delimiter ;
说明：
1、参数前面的符号的意思
- IN：当前参数为输入参数，也就是表示入参；存储过程只是读取这个参数的值。如果没有定义参数种类，默认就是 IN，表示输入参数。
- OUT：当前参数为输出参数，也就是表示出参；执行完成之后，调用这个存储过程的客户端或者应用程序就可以读取这个参数返回的值了。
- INOUT：当前参数既可以为输入参数，也可以为输出参数。
2、形参类型可以是 MySQL数据库中的任意类型。
call myprocedure(@ret);select @ret;
十、游标
在存储过程中使用游标可以对一个结果集进行移动遍历。
游标主要用于交互式应用，其中用户需要对数据集中的任意行进行浏览和修改。
使用游标的四个步骤：
- 声明游标，这个过程没有实际检索出数据；
- 打开游标；
- 取出数据；
关闭游标；
delimiter //create procedure myprocedure(out ret int)begindeclare done boolean default 0;declare mycursor cursor forselect col1 from mytable;# 定义了一个 continue handler，当 sqlstate '02000' 这个条件出现时，会执行 set done = 1declare continue handler for sqlstate '02000' set done = 1;open mycursor;repeatfetch mycursor into ret;select ret;until done end repeat;close mycursor;end //delimiter ;
十一、触发器
触发器会在某个表执行以下语句时而自动执行：DELETE、INSERT、UPDATE。
触发器必须指定在语句执行之前还是之后自动执行，之前执行使用 BEFORE 关键字，之后执行使用 AFTER 关键字。BEFORE 用于数据验证和净化，AFTER 用于审计跟踪，将修改记录到另外一张表中。
INSERT 触发器包含一个名为 NEW 的虚拟表。
CREATE TRIGGER mytrigger AFTER INSERT ON mytableFOR EACH ROW SELECT NEW.col into @result;SELECT @result; -- 获取结果
DELETE 触发器包含一个名为 OLD 的虚拟表，并且是只读的。
UPDATE 触发器包含一个名为 NEW 和一个名为 OLD 的虚拟表，其中 NEW 是可以被修改的，而 OLD 是只读的。
MySQL 不允许在触发器中使用 CALL 语句，也就是不能调用存储过程。
十二、事务管理
基本术语：
- 事务（transaction）指一组 SQL 语句；
- 回退（rollback）指撤销指定 SQL 语句的过程；
- 提交（commit）指将未存储的 SQL 语句结果写入数据库表；
- 保留点（savepoint）指事务处理中设置的临时占位符（placeholder），你可以对它发布回退（与回退整个事务处理不同）。
不能回退 SELECT 语句，回退 SELECT 语句也没意义；也不能回退 CREATE 和 DROP 语句。
MySQL 的事务提交默认是隐式提交，每执行一条语句就把这条语句当成一个事务然后进行提交。当出现 START TRANSACTION 语句时，会关闭隐式提交；当 COMMIT 或 ROLLBACK 语句执行后，事务会自动关闭，重新恢复隐式提交。
设置 autocommit 为 0 可以取消自动提交；autocommit 标记是针对每个连接而不是针对服务器的。
如果没有设置保留点，ROLLBACK 会回退到 START TRANSACTION 语句处；如果设置了保留点，并且在 ROLLBACK 中指定该保留点，则会回退到该保留点。
START TRANSACTION// ...SAVEPOINT delete1// ...ROLLBACK TO delete1// ...COMMIT
十三、字符集
基本术语：
- 字符集为字母和符号的集合；
- 编码为某个字符集成员的内部表示；
- 校对字符指定如何比较，主要用于排序和分组。
除了给表指定字符集和校对外，也可以给列指定：
CREATE TABLE mytable(col VARCHAR(10) CHARACTER SET latin COLLATE latin1_general_ci )DEFAULT CHARACTER SET hebrew COLLATE hebrew_general_ci;
可以在排序、分组时指定校对：
SELECT *FROM mytableORDER BY col COLLATE latin1_general_ci;
十四、权限管理
MySQL 的账户信息保存在 mysql 这个数据库中。
USE mysql;SELECT user FROM user;
创建账户
新创建的账户没有任何权限。
CREATE USER myuser IDENTIFIED BY 'mypassword';
修改账户名
RENAME USER myuser TO newuser;
删除账户
DROP USER myuser;
查看权限
SHOW GRANTS FOR myuser;
授予权限
账户用 username@host 的形式定义，username@% 使用的是默认主机名。
GRANT SELECT, INSERT ON mydatabase.* TO myuser;
删除权限
GRANT 和 REVOKE 可在几个层次上控制访问权限：
- 整个服务器，使用 GRANT ALL 和 REVOKE ALL；
- 整个数据库，使用 ON database.*；
- 特定的表，使用 ON database.table；
- 特定的列；
- 特定的存储过程。
更改密码REVOKE SELECT, INSERT ON mydatabase.* FROM myuser;
必须使用 Password() 函数进行加密。SET PASSWROD FOR myuser = Password('new_password');
十五、设计三范式
数据库设计范式共有？
3个。
第一范式：要求任何一张表必须有主键，每一个字段原子性不可再分。
第二范式：建立在第一范式的基础之上，要求所有非主键字段完全依赖主键，
不要产生部分依赖。
第三范式：建立在第二范式的基础之上，要求所有非主键字段直接依赖主键，
不要产生传递依赖。
第一范式
最核心，最重要的范式，所有表的设计都需要满足。
必须有主键，并且每一个字段都是原子性不可再分。
学生编号 学生姓名 联系方式
—————————————————————
1001 张三 zs@gmail.com,1359999999
1002 李四 ls@gmail.com,13699999999
1003 王五 ww@163.net,13488888888
以上是学生表，满足第一范式吗？
不满足，第一：没有主键。第二：联系方式可以分为邮箱地址和电话
学生编号(pk) 学生姓名 邮箱地址 联系电话
——————————————————————————
1001 张三 zs@gmail.com 1359999999
1002 李四 ls@gmail.com 13699999999
1003 王五 ww@163.net 13488888888
第二范式
第二范式：
建立在第一范式的基础之上，
要求所有非主键字段必须完全依赖主键，不要产生部分依赖。
学生编号 学生姓名 教师编号 教师姓名
——————————————————————————
1001 张三 001 王老师
1002 李四 002 赵老师
1003 王五 001 王老师
1001 张三 002 赵老师
这张表描述了学生和老师的关系：（1个学生可能有多个老师，1个老师有多个学生）
这是非常典型的：多对多关系！
分析以上的表是否满足第一范式？
不满足第一范式。
怎么满足第一范式呢？修改
学生编号+教师编号(pk) 学生姓名 教师姓名
——————————————————————————
1001 001 张三 王老师
1002 002 李四 赵老师
1003 001 王五 王老师
1001 002 张三 赵老师
学生编号 教师编号，两个字段联合做主键，复合主键（PK: 学生编号+教师编号）
经过修改之后，以上的表满足了第一范式。但是满足第二范式吗？
不满足，“张三”依赖1001，“王老师”依赖001，显然产生了部分依赖。
产生部分依赖有什么缺点？
数据冗余了。空间浪费了。“张三”重复了，“王老师”重复了。
为了让以上的表满足第二范式，你需要这样设计：
使用三张表来表示多对多的关系！！！！
学生表
学生编号(pk) 学生名字
——————————————————
1001 张三
1002 李四
1003 王五
教师表
教师编号(pk) 教师姓名
———————————————————
001 王老师
002 赵老师
学生教师关系表
id(pk) 学生编号(fk) 教师编号(fk)
———————————————————————————
1 1001 001
2 1002 002
3 1003 001
4 1001 002
背口诀：
多对多怎么设计？
多对多，三张表，关系表两个外键！！！！！！！！！！！！！！！
第三范式
第三范式
第三范式建立在第二范式的基础之上
要求所有非主键字典必须直接依赖主键，不要产生传递依赖。
学生编号（PK） 学生姓名 班级编号 班级名称
————————————————————————————-
1001 张三 01 一年一班
1002 李四 02 一年二班
1003 王五 03 一年三班
1004 赵六 03 一年三班
以上表的设计是描述：班级和学生的关系。很显然是1对多关系！
一个教室中有多个学生。
分析以上表是否满足第一范式？
满足第一范式，有主键。
分析以上表是否满足第二范式？
满足第二范式，因为主键不是复合主键，没有产生部分依赖。主键是单一主键。
分析以上表是否满足第三范式？
第三范式要求：不要产生传递依赖！
一年一班依赖01，01依赖1001，产生了传递依赖。
不符合第三范式的要求。产生了数据的冗余。
那么应该怎么设计一对多呢？
班级表：一
班级编号(pk) 班级名称
————————————————————
01 一年一班
02 一年二班
03 一年三班
学生表：多
学生编号（PK） 学生姓名 班级编号(fk)
—————————————————————-
1001 张三 01
1002 李四 02
1003 王五 03
1004 赵六 03
背口诀：
一对多，两张表，多的表加外键！！！！！！！！！！！！
总结表的设计？
一对多：
一对多，两张表，多的表加外键！！！！！！！！！！！！
多对多：
多对多，三张表，关系表两个外键！！！！！！！！！！！！！！！
一对一：
一对一放到一张表中不就行了吗？为啥还要拆分表？
在实际的开发中，可能存在一张表字段太多，太庞大。这个时
若有收获，就点个赞吧
0 人点赞
