[大数据]MySQL

MySQL

一、MySQL

1.sql 、DB、 DBMS分别是什么，他们之间的关系？

DB: DataBase（数据库，数据库实际上在硬盘上以文件的形式存在)
DBMS : DataBase Management system（数据库管理系统，常见的有:MyS oracle DB2 sybase sq1server…)
SQL: 结构化查询语言，是一门标准通用的语言。标准的sq1适合于所有的数据库产品。sQz属于高级语言。只要能看懂英语单词的，写出来的sql语句，可以读懂什么意思。

? SQL语句在执行的时候，实际上内部也会先进行编译，然后再执行sql。(sql语句的编译由DBMs完成。)
? DBMs负责执行sql语句，通过执行sql语句来操作DB当中的数据。
? DBMS-(执行)-> sQL-(操作)->DB

2.表

表: table是数据库的基本组成单元，所有的数据都以表格的形式组织，目的是可读性强。

1.一个表包括行和列:
行:被称为数据/记录(data)列:被称为字段(column)

2.每一个字段应该包括哪些属性?
字段名、数据类型、相关的约束。

3.SQL语句的分类

DQL（数据查询语言):查询语句，凡是select语句都是DQL.
DML（数据操作语言): insert delete update，对表当中的数据进行增删改。
DDL（数据定义语言) : create drop alter，对表结构的增删改。
TCL(事务控制语言): commit提交事务，rollback回滚事务。(TCL中的T是Transaction)
DCL（数据控制语言) : grant授权、revoke撤销权限等

4.导入数据

第一步:登录mysql数据厍管理系统
dos命令窗口:mysql -uroot -p333
第二步:查看有哪些数据库
show databases;(这个不是sQL语句，属于MysQL的命令。)
第三步:创建属于我们自己的数据库
create database bjpowernode;(这个不是sQl语句，属于MysQl的命令。)
第四步:使用bjpowernode数据
use bjpowernode;(这个不是sQl语句，属于MysQL的命令。)
第五步:查看当前使用的数据库中有哪些表?
show tables;(这个不是sQL语句，属于MysQL的命令。)
第六步:初始化数据
mysql> source E:\白嫖日记\MySQL\bjpowernode.sql

注意:数据初始化完成之后,有三张表:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-N1gQ9PLS-1636900483228)(C:\Users\86173\AppData\Roaming\Typora\typora-user-images\image-20211110174140757.png)]

**补充：**bjpowernode.sql，这个文件以sql结尾，这样的文件被称为"sql脚本"。

5.什么是sql脚本呢?

当一个文件的扩展名是.sql，并且该文件中编写了大量的sql语句，我们称这样的文件为sql脚本。
注意:直接使用source命令可以执行sql脚本。
sql脚本中的数据量太大的时候，无法打开，请使用source命令完成初始化。

6.删除数据库

drop database 表名;

7.查看表结构 (DESC XXX)

以下是查看三个表的表结构命令和结果

DESC dept;  //部门表
DESC emp; //员工表
DESC salgrade; //薪资等级表

8.表中的数据？(select *from XXX)

SELECT *FROM emp; //查看员工表信息
SELECT *FROM dept;//查看部门表信息
SELECT *FROM salgrade;//查看薪资表信息

二、MySQL常用命令

1.查看mysql 版本

select version();

2.创建数据库

create database XXX;

3.查询当前使用的数据库

select database();

4.终止一条语句

\c

5.退出mysql

\q   或者  EXIT  或者  QUIT

6.查看创建表的语句

show create table XXX；

三、简单的查询(DQL)

任何一条sql语句以","结尾，且不区分大小写。

一条sql语句以单引号’ '使用字符串

语法格式： select 字段1，字段2.........from 表名

1.查询一个字段

 select 字段 from 表名

2.查询多个字段

 select 字段1，字段2.........from 表名

3.查询全部字段

 select  *from 表名

4.计算员工的年薪

SELECT ename,sal*12 FROM emp;

5.将查询出来的结果的列重命名？as

SELECT ename,sal*12 AS yearsal FROM emp;

SELECT ename,sal*12 AS '年薪' FROM emp;

四、条件查询

***1.按照条件查询(大于、小于、等于)

语法格式：
select 
   字段，字段....          5
  from
      表名                 1
    where
      条件；               2
      group by              
      ...                  3
      having      
      ...                  4
      order by
      ...                  6

例：找出工资大于4000的员工

SELECT  ename,sal FROM emp WHERE sal>4000;

2.<>操作符 和！=操作符

本例中相当于 “不等于”

SELECT  ename,sal FROM emp WHERE sal<>4000;

3.between …and…操作符 和 >=and<=

注意：数字是闭区间，字符是左开右闭

SELECT  ename,sal FROM emp WHERE sal BETWEEN 2600 AND 4800;

4.is null判断为空

不为空为 is not null

问题：选出工资中津贴为空的人 (这里不能使用=null,语法不支持)

SELECT ename,sal,comm FROM emp WHERE comm IS NULL;

5.or和and

找出薪资大于1000的并且部门编号是20或30部门的员工

SELECT ename,sal,deptno FROM emp WHERE sal>1000 AND(deptno=20 OR deptno=30);

6.in等同于or

找出工作岗位是MANAGER和SALESMAN的员工?

SELECT ename,job FROM emp WHERE job='SALESMAN' OR job='MANAGER';

SELECT ename,job FROM emp WHERE job IN ('SALESMAN'  ,'MANAGER');

7.模糊查询like

<%代表任意多个字符，_表示任意一个字符>

//找到名字第二个字母是'A的'

SELECT ename FROM emp WHERE ename LIKE  **'_A%';**

//找到名字中有O的

SELECT ename FROM emp WHERE ename LIKE **'%O%';

8.表达式的优先级

9.排序(升序、降序)——通过order by来实现

<默认是升序，asc表示升序，desc表示降序>

SELECT ename ,sal FROM emp **ORDER  BY** sal asc;//升序命令：

SELECT ename ,sal FROM emp **ORDER  BY** sal desc;//降序命令：

*按照工资的降序排列，当工资相同的时候再按照名字的升序排列。

SELECT ename ,sal FROM emp ORDER  BY sal ASC,ename ASC;

五、分组/(多行)处理函数

记住:所有的分组函数都是对 “某一组” 数据进行操作的

分组函数一共5个。
分组函数还有另一个名字:多行处理函数。
多行处理函数的特点:输入多行，最终输出的结果是1行。

分组函数自动忽略null

分组函数不可以直接使用在where句子中

1.count计数

求员工总数？
SELECT COUNT(ename)FROM emp;

count(*)和count(具体的某个字段)，他们有什么区别?

count(*): 不是统计某个字段中数据的个数，而是统计总记录条数。(和某个字段无关)

count(comm): 表示统计comm字段中不为NULL的数据总数量。

2.sum求和

求工资和
SELECT SUM(sal) FROM emp;

3.avg平均

求平均工资
SELECT AVG(sal)FROM emp;

4.max最大值

求工资最大值
SELECT MAX(sal)FROM emp;

5.min最小值

求工资最小值
SELECT MIN(sal)FROM emp;

6.组合聚合函数

*7.单行处理函数

单行处理函数：输入一行，输出一行。

***注意：所有数据库中，有null参与的运算结果一定是null

ifnull( )空处理函数——ifnull(可能为NULL的数据,被当做什么处理)

例：计算每个员工的年薪

SELECT ename,(sal+comm)*12 AS yearsal FROM emp;   //错误的命令为：

——此结果输出的有空null。因为所有数据库中，有null参与的运算结果一定是null。

SELECT ename,(sal +IFNULL(comm,0))*12 AS yearsal FROM emp;//正确的命令为：

8. group by

group by :按照某个字段或者某些字段进行分组。

having : having是对分组之后的数据进行再次过滤。

注意:分组函数一般都会和group by联合使用，这也是为什么它被称为分组函数的原因。
任何一个分组函数(count sum avg max min）都是在group by语句执行结束之后才会执行的。
当一条sql语句没有group by的话，整张表的数据会自成一组。

例：找到每个工作岗位的最高薪资

SELECT job,MAX(sal) FROM emp GROUP BY job;//命令：

注意：当一条语句中有group by的话，select后面只能跟分组函数和参与分组的字段。例如：
SELECT A,MAX(B) FROM emp GROUP BY A; //不要加多余的，否则系统会出错。

SELECT ename, job,MAX(sal) FROM emp GROUP BY job; //此时输出的就会出错

特例：找到工资中，大于平均工资的员工

SELECT ename,sal FROM emp WHERE sal>AVG(sal);//错误命令：

错误原因：因为AVG(sal)属于分组函数，本例中，省略了group by ，所以默认为一组，然而group by是在where之后才执行的，所以顺序不对，无法执行。

SELECT ename,sal FROM emp WHERE sal>(SELECT AVG(sal)FROM emp);//正确命令：

多字段分组：找到不同部门不同岗位的最高工资

select deptno,job,max(sal)from emp group by deptno,job;

9.having

例1：求每个部门中的最高工资，要求显示工资大于2600的

SELECT deptno,MAX(sal) FROM emp GROUP BY deptno HAVING MAX(sal)>2600;    //命令1效率低

SELECT deptno,max(sal) FROM emp where sal>2600 GROUP BY deptno ; //命令2：提前筛选掉工资小于2600的，效率高

例2：求每个部门中的平均工资，要求显示工资大于2000的

SELECT deptno,AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal)>2000;//命令：

注意：这里的平均工资>2000的不能用where，因为分组函数在where之后执行。

10.去除重复记录 (distinct)

注意：distinct只能出现在 所有字段的最前面

例：求每个部门中的各个职位(重复的不输出)

SELECT DISTINCT deptno,job  FROM emp;

例：统计岗位的数量

SELECT COUNT(DISTINCT job)FROM emp;

总结：DQL语句

语法格式：
select 
   字段，字段....       5
  from
      表名                 1
    where
      条件；              2
      group by              
      ...                     3
      having      
      ...                     4
      order by
      ...                     6
      limit
      ...                     7

六、连接查询

1.什么是 连接查询

在实际开发中，大部分的情况下都不是从单表中查询数据，一般都是多张表联合查询取出最终的结果.

2.连接查询的分类

根据 表的连接方式来划分，包括：

内连接：

? 等值连接、非等值连接、自然连接

外连接：

? 左外连接、右外连接、全连接(了解)

3.笛卡尔成积现象

当两张表进行连接查询的时候,没有任何条件进行限制
最终的查询结果条数是两张表记录条数的乘积。

①.关于表的别名：

SELECT e.ename,d.dname FROM emp e,dept d;

优点：执行效率高、可读性好

②.如何避免笛卡尔积现象

可以通过加条件来 避免

注意：不会减少匹配次数，但是会只显示有效记录。

③.例：找到每一个员工的部门名称，要求显示员工名和部门名

SELECT e.ename,d.dname FROM emp e,dept d; //错误命令：出现笛卡尔积现象

SELECT e.ename,d.dname FROM emp e,dept d WHERE e.deptno=d.deptno;//正确命令：

4.内连接中的等值连接

特点：条件是等量关系

SQL92版本语法格式(太老)：
SELECT e.ename,d.dname FROM emp e,dept d WHERE e.deptno=d.deptno;
SQL99版本语法格式：
...

A
(inner) join     //inner可以省略，但是带上可读性会更高
B
on
连接条件
where
...

例：找到每一个员工的部门名称，要求显示员工名和部门名

SELECT e.ename,d.dname FROM emp e JOIN dept d ON e.deptno=d.deptno;

5.内连接的非等值连接

特点：条件是非等量关系

例：拨出每个员工的工资等级，要求显示员工名、工资、工资等级。

SELECT e.ename,e.sal,s.grade FROM emp e JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal;

6.自连接

特点：一张表看做两张表，自己连自己

例：找出每个员工的上级领导,要求显示员工名和对应的领导名。

SELECT a.ename AS'员工名',b.ename AS'领导名' FROM emp a JOIN emp b ON a.mgr=b.empno;

***7.外连接

①.什么是外连接，和内连接有什么区别？

内连接:
假设A和B表进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录查询出来，这就是内连接。AB两张表没有主副之分，两张表是平等的。
外连窗:
假设a和B表进行连接，使用外连接的话，A两张表中有一张表是主表，一张表是副表，主要查询主表中的数据，捎带着查询副表，当副表中的数据没有和主表中的数据匹配上，副表自动模拟出NULL与之匹配。（主表的数据无条件全部显示）

②.外连接的分类

左外连接（左连接）：表示左边的表是主表
右外连接（右连接）：表示右边的表是主表

左连接有右连接的写法，右连接也会有对应的左连接的写法。

例：找出每个员工的上级领导,要求显示员工名和对应的领导名。

（左外连接）——与内连接的区别就是，当副表中的数据没有和主表中的数据匹配上，副表自动模拟出NULL与之匹配。

SELECT a.ename AS'员工名',b.ename AS'领导名' FROM emp a **LEFT** OUTER  JOIN emp b ON a.mgr=b.empno;

例：找出哪个部门没有员工

SELECT d.* FROM emp e  RIGHT JOIN dept d ON e.deptno=d.deptno WHERE e.empno IS NULL ;

7.三张以上表查询连接

例：找出每一个员工的部门名称以及工资等级。

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;

例：找出每一个员工的部门名称、工资等级、以及上级领导

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 LEFT JOIN emp el ON e.mgr=el.empno;

七、子查询的嵌套

1.子查询

①什么是子查询

select语句当中嵌套select语句，被嵌套的select语句是子查询。

②子查询都可以用在什么地方

select
..(select)
from
..(select)
where
..(select)

2.where子句中使用子查询

例：找出高于平均薪资的员工信息

***3.from后面嵌套子查询

例：找出每个部门的平均薪水的薪资等级

找出每个部门的平均薪水的薪资等级
第一步：找出每个部门的凭据薪资
     SELECT deptno,AVG(sal) AS avgsal FROM emp GROUP BY deptno;
第二部：求薪资的等级
     (把步骤一的表视为新表)      
     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;

例：找出每个部门平均值的薪资等级

第一步：
找到每个员工的薪资等级
SELECT e.ename,e.sal,e.deptno,s.grade FROM emp e JOIN salgrade s
ON e.sal BETWEEN s.losal AND s.hisal;
第二步：
通过deptno进行分组，然后求等级的平均值
select e.deptno,avg(s.grade) from emp e join salgrade s
on e.sal between s.losal and s.hisal group by e.deptno;

4.select后面嵌套子查询

例：找到每个员工所在的部门编号

SELECT e.ename ,d.dname FROM emp e JOIN dept d ON e.deptno=d.deptno;// 命令1：
SELECT e.ename,(SELECT d.dname FROM dept d WHERE e.deptno=d.deptno)AS dname FROM emp e; //命令2：

八、limit(分页)和unoin

1. unoin(可以将查询结果集相加)

注意：union的拼接的列数必须是相同的

例：找出工作岗位是salesman和manger的员工

SELECT ename ,job FROM emp WHERE job='MANGER' OR job='SALESMAN';//命令1：
SELECT ename ,job FROM emp WHERE job IN ('MANGER','SALESMAN');命令2：
SELECT ename,job FROM emp WHERE job='MANGER' UNION SELECT ename,job FROM emp WHERE job='SALESMAN';命令3：

***2.limit的使用(分页查询)

limit 的 Mysql 特有的

limit取结果集中的部分数据
语法机制：
limit startIndex，length
startIndex 表示起始位置
length表示取几个

例：取工资前五名的员工

SELECT ename ,sal FROM emp ORDER BY sal DESC LIMIT 5;

例：取工资第4——9名的员工

select ename,sal from emp order by sal desc limit 3,6;

***3.通常用的标准分页sql

每页显示pagesize条记录：

第pageNo页：(pageNo-1)*pagesize, pagesize
//pagesize是 每页显示多少条记录
//pageNo是显示第几页

九、表

1.创建表

创建表语句的语法格式：
create table 表名(
字段名 1    数据类型；
字段名 2    数据类型；
字段名 3    数据类型；
字段名 4    数据类型；
...
)CHARACTER SET utf8;

MySQL中常见的数据类型 ：

int、bigint(长整型)、float、char、varchar(可变长字符串)、date(日期类型)、
BLOB(二进制大对象)、CLOB(字符大对象)

char和varchar怎么选？

在实际的开发中，当某个字段中的数据长度不发生改变的时候,是定长的,例如:性别、生日等都是采用char.
当一个字段的数据长度不确定，例如:简介、姓名等都是采用varchar

BLOB和CLOB怎么选？

BLOB：二进制大对象（存储图片、视频等流媒体信息)
CLOB：字符大对象（存储较大文本，比如，可以存储4c的字符串。)

例：

创建学生表信息：
学生信息包括：
学号、姓名、性别、班级编号、生日
学号：bigint
姓名：varchar
性别：char
班级编号：int
生日：char

CREATE TABLE s_student(
NO BIGINT,
NAME VARCHAR(255),
sex CHAR(1),
classno VARCHAR(255),
birth CHAR(10)
)CHARACTER SET utf8; 

2.insert语句插入数据

语法格式：
insert into 表名(字段名1，字段名2....)values (值1，值2...)
要求：字段的数量和值的数量相同，并且数据类型要对应相同

INSERT into s_student(NO,NAME,sex,classno,birth)VALUES(1,'霸王花','0',19060207,'2000-11-16');
//查看表
SELECT*FROM s_student;

注意：
当一条insert语句执行成功之后,表格当中必然会多一行记录。即使多的这一行记录当中某些字段是NULL，后期也没有办法在执行insert语句插入数据了，只能使用update进行更新。

INSERT INTO s_student (NAME)VALUES('李承鄞');

当插入这一行时，除了姓名 外，其他均为NULL

3.表的复制及批量插入

表的复制语法：
create table 表名 as select 语句；
将查询 结果当做表创建出来从而达到复制的效果。

例：把员工表的姓名和工资选出来 建一张新的表赋给copy
CREATE TABLE copy AS SELECT e.ename ,e.sal FROM emp e;。

表的批量插入：
例：将部门表的部门号插入到copy中——有问题！！！插入在其下面
INSERT INTO copy SELECT d.deptno FROM dept d JOIN emp e ON d.deptno=e.deptno ;
SELECT *FROM copy;

3.修改数据

语法格式：
update 表名 set 字段名1=值1，字段名2=值2...where 条件；
注意:没有条件则整张表数据全部更新。

例：将copy表中的ename='ALLEN'的人的工资改为6000；
UPDATE copy SET sal=6000 WHERE ename='ALLEN';

4.删除数据

语法格式：
delete from 表名 where 条件；
注意：没有条件全部删除
***删除大表：
truncate table 表名;

例：删除copy中ename='KING'的信息
DELETE FROM copy WHERE ename='KING';

5.关于表结构的修改——工具修改

十、约束

1.什么是约束？常见的约束有哪些？

约束：在创建表的时候，可以给表的字段添加相应的约束，添加约束的目的是为了保证表中数据的合法性、有效性、完整性。

常见的约束：

非空约束(not null)：约束的字段不能为NULL
唯一约束(unique)：约束的字段不能重复
主键约束(primary key)：约束的字段既不能重复，也不能为NULL
外键约束(foreign key)
检查约束(check) (Mysql目前暂无,oracle有)

2.非空约束 not null

create table t_user(
id int ,
username varchar(255) not null,  //列级约束
password varchar(255)
)CHARACTER SET utf8;
select*from t_user;
insert into t_user (id,password)values(11,'fan1116');//错误的，因为username必须不为空
insert into t_user (id,username,password)values(11,'霸王花','fan1116');//正确的

3.唯一性约束——unique

唯一性约束的字段具有唯一性，不能重复，但可以为NULL

例1 ：（单个添加unique）
CREATE TABLE t_user(
id INT ,
username VARCHAR(255) UNIQUE,  //列级约束
PASSWORD VARCHAR(255)
)CHARACTER SET utf8;
SELECT *FROM t_user;
INSERT INTO t_user(id) VALUES(1);  //这里的unique 具有唯一性，但是可以为空，所以是可以的。
INSERT INTO t_user(id,username)VALUES(2,'霸王花');


例2：(多个添加唯一性)

CREATE TABLE t_user(
id INT ,
username VARCHAR(255) ,
PASSWORD VARCHAR(255),
UNIQUE(id,username)  //id 和username“两个”具有唯一性 //表级约束
)CHARACTER SET utf8;
INSERT INTO t_user VALUES(1,'霸王花',111);
INSERT INTO t_user VALUES(2,'霸王花',222);
INSERT INTO t_user VALUES(2,'李承鄞',333);
SELECT *FROM t_user;

4.主键约束——primary key

①注意：主键值不能为空、不能重复、(类似于一个人的身份证号)

? 一张表的主键约束只有一个！

例1：(列级约束)
CREATE TABLE t_user(
id INT  PRIMARY KEY,    //列级约束
username VARCHAR(255) ,
PASSWORD VARCHAR(255)

)CHARACTER SET utf8;

INSERT INTO t_user VALUES(1,'霸王花',111);
INSERT INTO t_user VALUES(2,'霸王花',222);
INSERT INTO t_user VALUES(2,'李承鄞',333);  //错误：主键值不能重复
INSERT INTO t_user (username ,PASSWORD) VALUES('易烊千玺',444); //错误：主键值不能为空

SELECT *FROM t_user;

②主键的分类：

根据主键字段的字段数量来划分： 单一主键(推荐使用)、复合主键(多个字段联合起来添加一个主键约束——不推荐)
根据主键性质来划分：自然主键(推荐)、业务主键(不推荐)

例2：(表级约束)
CREATE TABLE t_user(
id INT  ,    
username VARCHAR(255) ,
PASSWORD VARCHAR(255),
PRIMARY KEY(id)        //表级约束
)CHARACTER SET utf8;

INSERT INTO t_user VALUES(1,'霸王花',111);
INSERT INTO t_user VALUES(2,'霸王花',222);
INSERT INTO t_user VALUES(2,'李承鄞',333);  //错误：主键值不能重复

SELECT *FROM t_user;

例3：(复合主键)——不推荐使用
CREATE TABLE t_user(
id INT  ,    
username VARCHAR(255) ,
PASSWORD VARCHAR(255),
PRIMARY KEY(id,username )
)CHARACTER SET utf8;

INSERT INTO t_user VALUES(1,'霸王花',111);
INSERT INTO t_user VALUES(2,'霸王花',222);
INSERT INTO t_user VALUES(2,'李承鄞',333);  //以上都是正确的

SELECT *FROM t_user;

***③mysql提供主键自增——primary key auto_increment

补充：Oracle 中的自增叫做：序列对象

CREATE TABLE t_user(
id INT PRIMARY KEY AUTO_INCREMENT ,    //主键自增，这里是id数字自动增加
username VARCHAR(255) 

)CHARACTER SET utf8;

INSERT INTO t_user (username) VALUES('霸王花');
INSERT INTO t_user(username) VALUES('霸王花');
INSERT INTO t_user (username)VALUES('李承鄞');  

SELECT *FROM t_user;

5.外键约束——foreign key 子XX references 父XX

注意：外键可以为空、且被引用的字段不一定是主键，但是必须具有唯一性

例题：

上述表格过于冗余，所以可以拆分为两个表：

c_class (父表)

s_stud(子表)

代码如下：
DROP TABLE s_stud;  //先删除子表，后删除父表
DROP TABLE c_class; 
CREATE TABLE c_class(   //先创建父表，在创建子表
 c_id INT,
 c_name VARCHAR (255),
 PRIMARY KEY(c_id)       //设置主键约束
)CHARACTER SET utf8;


CREATE TABLE s_stud(
 s_id INT,
 s_name VARCHAR(255),
 class_id INT,
 FOREIGN KEY(class_id) REFERENCES c_class (c_id)    //进行外键约束
)CHARACTER SET utf8;

INSERT INTO c_class VALUES(1001,'XXXX');
INSERT INTO c_class VALUES(1002,'YYYY');
SELECT *FROM c_class;

INSERT INTO s_stud VALUES(1,'张三',1001);
INSERT INTO s_stud VALUES(2,'李四',1001);
INSERT INTO s_stud VALUES(3,'王麻子',1002);
INSERT INTO s_stud VALUES(4,'霸王花',1002);
INSERT INTO s_stud VALUES(5,'易烊千玺',1003);  //错误，超出了外键约束的条件，班级1003不正确
SELECT *FROM s_stud;

***十一、事务

1.事务的概述

1.一个事务是一个完整的业务逻辑单元,不可再分。
例：比如:银行账户转账，从A账户向z账户转账10000.需要执行两条update语句:

update t_act set balance = balance - 10000 where actno = 'act-001';
update t_act set balance = balance + 10000 where actno = 'act-002';

以上两条DM语句必须同时成功，或者同时失败，不允许出现一条成功，一条失败。
要想保证以上的两条DM语句同时成功或者同时失败,
那么就需要使用数据库的"事务机制".

2.和事务相关的语句只有：DML语句(insert 、delete、update)

事务的存在是为了保证数据的完整性、安全性

3.通常一个“事务”需要多条DML语句来执行

2.事务的四大特性ACID

A——原子性：事务的最小工作单位，不可再分
C——一致性：事务必须保证多条DML语句同时成功或失败
I——隔离性：事务A与事务B之间具有隔离
D——持久性：最终数据必须持久化到硬盘文件中，事务才算最终的结束

3.事务之间的隔离性

事务隔离性存在隔离级别,理论上隔离级别包括4个:

第一级别:读未提交(read uncommitted)
对方事务还没有提交，我们当前事务可以读取到对方未提交的数据。
读未提交存在脏读(Dirty Read）现象:表示读到了脏的数据。
第二级别:读已提交(read committed)
对方事务提交之后的数据我方可以读取到。
这种隔离级别解决了:脏读现象没有了。
读已提交存在的问题是:不可重复读。
第三级别:可重复读(repeatable read)
这种隔离级别解决了:不可重复读问题。
这种隔离级别存在的问题是:读取到的数据是幻象
第四级别:序列化读/串行化读
解决了所有问题。
效率低。需要事务排队。

oracle数据库默认的隔离级别是:读已提交。
mysql数据库默认的隔离级别是:可重复读。

4.演示事务

mysql 事务默认情况下是自动提交的 (一旦执行任意一条DML语句，就会自动提交)
使用 start transaction;关闭自动提交机制，则可以回滚rollback成功。

A.演示读未提交

例：
use bipowernode;

设置事务的全局隔离级别：
SET gloable TRANSACTION ISOLATION  LEVEL READ UNCOMMITTED;
查看事务的全局隔离级别：
SELECT @@gloable.tx ISOLATION;
 exit; //退出重登
use bipowernode;
start transaction;     //不能忘

建表+数据操作....（开两个查看）
commit ;//提交

B.演示读已提交

例：
use bipowernode;

设置事务的全局隔离级别：
SET gloable TRANSACTION ISOLATION  LEVEL READ read COMMITTED;
查看事务的全局隔离级别：
SELECT @@gloable.tx ISOLATION;
 exit; //退出重登
use bipowernode;
start transaction;     //不能忘

建表+数据操作....（开两个查看）
commit ;//提交

C.演示可重复读

例：
use bipowernode;

设置事务的全局隔离级别：
SET gloable TRANSACTION ISOLATION  LEVEL READ repeatable read ;
查看事务的全局隔离级别：
SELECT @@gloable.tx ISOLATION;
 exit; //退出重登
use bipowernode;
start transaction;     //不能忘

建表+数据操作....（开两个查看）
commit ;//提交

***D.演示串行化读

例：
use bipowernode;

设置事务的全局隔离级别：
SET gloable TRANSACTION ISOLATION  LEVEL serializable ;
查看事务的全局隔离级别：
SELECT @@gloable.tx ISOLATION;
 exit; //退出重登
use bipowernode;
start transaction;     //不能忘

建表+数据操作....（开两个查看）
commit ;//提交

十二、索引

注意：主键和具有unique 约束的字段会自动增加索引

1.什么是索引，有什么用？

索引相当于一本书的目录，通过目录，可以快速的找到对应的资源。

在数据库方面，查询一张表的时候有“两种检索方式”:
第一种方式:全表扫描
第二种方式:根据索引检索（效率很高)

索引为什么可以提高检索效率呢?
——其实最根本的原理是缩小了扫描的范围。
注意：索引虽然可以提高检索效率，但是不能随意的添加索引，因为索引也是数据库当中的对象，也需要数据库不断的维护。是有维护成本的。比如，表中的数据经常被修改这样就不适合添加索引，因为数据一旦修改，索引需要重新排序，进行维护。

添加索引是给某一个字段,或者说某些字段添加索引。快速定位

2.什么时候添加索引？

数据量庞大(根据现实)、该字段很少DML操作、该字段经常出现在where子句中

3.查看sql语句的执行计划

EXPLAIN SELECT ename, sal FROM emp WHERE sal=5000;//执行14行

create index emp_sal_index on emp (sal);//给薪资sal添加索引
EXPLAIN SELECT ename, sal FROM emp WHERE sal=5000;//之后执行1行

4.怎么创建索引对象，怎么删除索引对象？

create index 索引名称 on 表名(字段名)；   //创建索引对象
drop index 索引名称 on 表名 ；   //删除索引对象

5.索引的实现原理

索引底层采用的数据结构是:B +Tree

通过 Tree缩小扫描范围，底层索引进行了排序,分区 ，索引会携带数据在表中的"物理地址",最终通过索引检索到数据之后，获取到关联的物理地址，通过物理地址定位表中的数据，效率是最高的。
select ename from emp where ename = 'SMITH ';
通过索引转换为:
select ename from emp where物理地址= ox3;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-uNd6sGsI-1636900483232)(C:\Users\86173\AppData\Roaming\Typora\typora-user-images\image-20211113182507950.png)]

6.索引的分类

单一索引：给单个字段添加索引
复合索引：给多个字段联合起来添加1个索引
主键索引：主键上会自动添加索引
唯一索引：有unique 约束的字段会自动添加索引
…

7.索引什么时候失效？

select ename from emp where ename like 'A%';
模糊查询的时候，第一个通配符使用的是8，这个时候索引是失效的。

十三 、视图

1.什么是视图

站在不同的角度去看到数据。(同一张表的数据，通过不同的角度去看待）。

2.如何创建和删除视图

create view 视图名 as select 字段1,字段2 from 表名; //创建视图

drop view 视图名; //删除视图

***3.对视图进行增删改查，会影响到原表数据。

注意：视图只能通过DQL语句去创建。
可以通过CRUD对视图进行操作。

4.视图的作用

视图可以隐藏表的实现细节。保密级别较高的系统，数据库只对外提供相关的视图，java程序员只对视图对象进行CRUD。

***十四、数据库设计三范式

1.什么是设计范式

设计表 的一句，按照这个三范式设计的表不会出现数据冗余。

2.三范式都是 哪些？

第一范式:
任何一张表都应该有主键，并且每一个字段原子性不可再分。
第二范式:
建立在第一范式的基础之上，所有非主键字段完全依赖主键，不能产生部分依赖。(多对多？三张表，关系表两个外键)
第三范式:
建立在第二范式的基础之上，所有非主键字段直接依赖主键,不能产生传递依赖。(一对多？两张表，多的加外键)

3.一对一怎么设计

两种方案：

主键共享
外键唯一

十五、引擎(了解)

1.什么是存储引擎

存储引擎即“表的连接方式”

2.MyISAM引擎——节约空间

mysql最常用的引擎

它管理的表具有以下特征:
1.使用三个文件表示每个表:
·格式文件―存储表结构的定义( mytable.frm)
·数据文件一存储表行的内容〈 mytable.MYD)
·索引文件―存储表上索引( mytable.MYI)J
2.灵活的AUTO_INCREMENT字段处理
3.可被转换为压缩、只读表来节省空间

3.InnoDB引擎——数据安全

InnoDB存储引擎是 MySQL的缺省引擎。

它管理的表具有下列主要特征:
1.每个InnoDB表在数据库目录中以.frm格式文件表示

2. InnoDB表空间tablespace被用于存储表的内容
3. 提供一组用来记录事务性活动的日志文件
4. 用COMMIT(提交)、SAVEPOINT及ROLLBACK(回滚)支持事务处理
5. 提供全ACID兼容
6. 在MysQL服务器崩溃后提供自动恢复
7. 多版本（MVCC）和行级锁定
8. 支持外键及引用的完整性，包括级联删除和更新

4.MEMORY引擎

它管理的表具有下列主要特征:

-在数据库目录内，每个表均以.frm格式的文件表示。
-表数据及索引被存储在内存中。
-表级锁机制。
-不能包含TEXT或BLOB字段。
-MEMORY存储引擎以前被称为HEAP引擎。

5.选择合适的引擎

1.MyISAM表最适合于大量的数据读而少量数据更新的混合操作。MylSAM表的另一种适用情形是使用压缩的只读表。
2.如果查询中包含较多的数据更新操作，应使用1nnoDB。其行级锁机制和多版本的支持为数据读取和更新的混合操作提供了良好的并发机制。
3.可使用MEMORY存储引擎来存储非永久需要的数据，或者是能够从基于磁盘的表中重新生成的数据。

十六、DBA命令(了解)

1.数据库数据的导出

a.导出整个数据库

在windows的dos命令窗口中执行:

mysqldump bjpowernode>D: \bjpowernode.sql -uroot -p123.

b.导出指定库下的指定表

在windows的dos命令窗口中执行:

mysqldump bjpowernode emp> D:\ bjpowernode.sql -uroot-p123

2.数据库数据的导入

登录MYSQL数据库管理系统之后执行:

create database bjpowernode ;
use bjpowernode ;
source D:\ bjpowernode.sql

2.MyISAM引擎——节约空间

mysql最常用的引擎

它管理的表具有以下特征:
1.使用三个文件表示每个表:
·格式文件―存储表结构的定义( mytable.frm)
·数据文件一存储表行的内容〈 mytable.MYD)
·索引文件―存储表上索引( mytable.MYI)J
2.灵活的AUTO_INCREMENT字段处理
3.可被转换为压缩、只读表来节省空间

3.InnoDB引擎——数据安全

InnoDB存储引擎是 MySQL的缺省引擎。

它管理的表具有下列主要特征:
1.每个InnoDB表在数据库目录中以.frm格式文件表示

2. InnoDB表空间tablespace被用于存储表的内容
3. 提供一组用来记录事务性活动的日志文件
4. 用COMMIT(提交)、SAVEPOINT及ROLLBACK(回滚)支持事务处理
5. 提供全ACID兼容
6. 在MysQL服务器崩溃后提供自动恢复
7. 多版本（MVCC）和行级锁定
8. 支持外键及引用的完整性，包括级联删除和更新

4.MEMORY引擎

它管理的表具有下列主要特征:

-在数据库目录内，每个表均以.frm格式的文件表示。
-表数据及索引被存储在内存中。
-表级锁机制。
-不能包含TEXT或BLOB字段。
-MEMORY存储引擎以前被称为HEAP引擎。

5.选择合适的引擎

1.MyISAM表最适合于大量的数据读而少量数据更新的混合操作。MylSAM表的另一种适用情形是使用压缩的只读表。
2.如果查询中包含较多的数据更新操作，应使用1nnoDB。其行级锁机制和多版本的支持为数据读取和更新的混合操作提供了良好的并发机制。
3.可使用MEMORY存储引擎来存储非永久需要的数据，或者是能够从基于磁盘的表中重新生成的数据。

十六、DBA命令(了解)

1.数据库数据的导出

a.导出整个数据库

在windows的dos命令窗口中执行:

mysqldump bjpowernode>D: \bjpowernode.sql -uroot -p123.

b.导出指定库下的指定表

在windows的dos命令窗口中执行:

mysqldump bjpowernode emp> D:\ bjpowernode.sql -uroot-p123

2.数据库数据的导入

登录MYSQL数据库管理系统之后执行:

create database bjpowernode ;
use bjpowernode ;
source D:\ bjpowernode.sql