Mysql

2022-01-08 23:59:07

数据库学习笔记

1.SQL，db，dbms他们之间的关系？

sql：

结构化查询语言，是一门标准通用的语言。标准的sql适合于所有的数据库产品

DB：数据库

DBMS：数据库管理系统

DBMS负责执行sql语句，通过执行sql语句操作DB中的数据

2.什么是表？

表：table

表：table是数据库的基板组成单元，所有数据都已表哥的形式组织，目的是可读性强

一个表包括行和列：

行：被称为数据/记录（data）

列：被称为字段（column）

每一个字段应该包括那些属性？

字段名、数据类型、相关的约束

3.学习mysql主要还是学习通用的SQL 语句，那么SQL语句包括怎删改查，SQL语句怎么分类呢？

DQL（数据查询语言）：查询语句，凡是select语句都是DQL

DML（数据操作语言）：insert delete update，对表中的数据进行增删改

DDL（数据定义语言）：create drop alter，对表结构的增删改

TCL（事务控制语言）：commit提交事务，rollback回滚事务。（T：transaction）

DCL（数据控制语言）：grant授权、revoke撤销权限等。

4、导入数据（后期练习使用演示的数据）

第一步：登陆mysql数据库管理系统

dos命令窗口：

mysql -uroot -p123456

第二步：查看有哪些数据库

show databases；（这个不是sql语句，属于 mysql的命令）

第三步：创建属于我们自己的数据库

create database bjpowernode数据

第四步：使用bipowernode数据

use bjpowernode;（这个不是sql语句，属于mysql的命令。）

第五步：

查看当前使用的数据库中有那些表？

show tables;

第六步：初始化数据

mysql ->source sql文件绝对路径

5、bipowernode.sql,这个文件以sql结尾，这样的文件被称为“sql脚本”。什么是sql脚本呢？

当一个文件的扩展名是.sql，并且该文件中编写了大量的sql语句，我们称这样的文件为sql脚本。

注意：直接使用source命令可以执行sql脚本。

sql脚本中的数据量太大时，无法打开，请使用source命令完成初始化。

6、删除数据库：drop database bjpowernode；

7、查看表结构：desc dept;

8、表中的数据：select * from emp;(查看表中的数据)

9、常用命令？

mysql> select databases; 查看当前使用的是哪个数据库

mysql> select version(); 查看mysql的版本号

\C 命令结束一条语句

exit 退出mysql

10、查看创建表的语句：

show create table emp;

11、简单的查询语句（DQL）

语法格式：

select 字段名1，字段名2，。。。 from 表名;

提示：

1、任何一条sql语句以“;” 结尾；

2、sql语句不区分大小写。

查询员工的年薪？（字段可以参与数学运算。）

select ename,sal*12 from emp;

给查看结果的列重命名？

select ename,sal*12 as yearsal from emp;

别名中有中文？

select enamel，sal*12 as '年薪' from emp;

select enamel，sal*12 '年薪' from emp;

注意：1、标准sql语句中要求字符串使用单引号括起来。虽然mysql支持双引号，尽量别用。

2、as 可以省略

查询所有字段？

select *from emp; // 实际开发中不建议使用

12、条件查询？

语法格式：

select

字段，字段。。。

from

表名

where

条件；

执行顺序：先from，然后where，最后select

查询员工工资等于5000的姓名？

select ename from emp where sal=5000;

查询SMITH的工资？

select sal from emp where ename='SMITH'；

找出工资高于3000的员工？

select ename,sal from emp where sal>3000;

找出工资不等于3000的？

select ename,sal from emp where<> 3000;

找出工资在1100和3000的员工，包括110和3000？

select ename,sal from emp where sal >=1100 and sal<=3000; //between...and...是闭区间【1100,3000】

select ename,sal from emp where sal between 1100 and 3000

注意：between ...and...左小右大

between and 除了可以使用在数字方面之外，还可以使用在字符串方面。

select ename from emp where ename between 'A' and 'C'(左闭右开)

13、排序（升序、降序）

按照工资升序，找出员工名和薪资？

select ename,sal

from emp

order by

sal;

注意：默认是升序，怎么制定升序或者降序？asc表示指定升序，desc 表示降序。

select ename,sal from emp order by sal;//升序

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 desc,ename asc;

注意：越靠前的字段月能起到主导作用。只有当前面的字段无法完成排序的时候，才会启用后面的字段。

找出工作岗位是SALESMAN的员工，并且要求按照薪资的降序排列。

select

ename,job,sal

from

emp

where

job='SALEMAN'

order by

sal desc;

执行顺序：

select

from

emp 1

where

条件 2

order by

。。。; 4

14、分组函数？

count 计数

sum 求和

avg 平均值

max 最大值

min 最小值

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

找出工资总和？

select sum(sal) from emp;

找出最高工资？

select man(sal) from emp;

找出最低工资？

select min(sal) from emp;

找出平均工资？

select avg（sal） from emp；

找出总人数？

select count（*）from emp；

select count(ename) from emp;

分组函数一共有5个。

分组函数还有另一个名字：多行处理函数

多行处理函数的特点：输入多行，最终输出的结果是一行

分组函数自动忽略NULL。

select count(comm) from emp;

找出工资高于平均工资的员工？

第一步：

select avg（sal）from emp；

第二步：

select ename ，sal，from emp

where sal>平均工资、

结合：

select ename ，sal，from emp

where sal>（select avg（sal）from emp）；

原因：SQL语句当中有一个语法规则，分组函数不可以直接使用在where子句当中。？

因为group by是在where之后使用

select 5

...

from 1

...

where 2

...

group by 3

...

having 4

...

order by 6

执行顺序 where>group>分组函数。

count（*）和count（某个具体的字段）,他们之间的区别。

count（*）：不是统计某个字段中数据的个数，而是统计总记录条数（和某个字段无关）

count（comm）：表示统计comm字段中不为NULL的数据总数量。

分组函数也能组合起来使用：

select count(*),sum(sal),avg(sal),max(sal) from emp;

15、单行处理函数

什么是单行处理函数？

输入一行，输出一行。

计算每个员工的年薪？（只要数学表达式含有null 最后结果一定为null）

select ename,(sal+ifnull(comm,0))*12 as yearsal from emp;

ifnull() 空处理函数？

ifnull（可能为NULL的数据，被当做什么处理）：属于单行处理函数

select ename,ifnull(comm,0) as comm from emp;

16、group by 和having

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

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

案例：找出每个工作岗位的最高薪资。

select max(sal) from emp gruop by job;

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

select ename ，max(sal) ，job from emp gruop by job;

以上在mysql中，查询结果是有的，但是结果没有意义，在oracle数据库当中会报错。语法错误。oracle的语法规则比mysql语法规则严谨。

记住一个规则：当一条语句中有group by 的话，select 后面之后只能跟分组函数和参与分组的字段。

每个工作岗位的平均薪资？

select job,avg(sal) from emp group by job;

多个字段能不能联合起来一块分组？

案例：找出每个部门不同工作岗位的最高薪资。

select

deptno,job,max(sal)

from

emp

group by

deptno,job;

找出每个部门的最高薪资，要求显示薪资大于2500的数据。

第一步：找出每个部门的最高薪资

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

第二步：找出薪资大于2900

select max(sql) ,deptno from emp group by deptno having max(sql)>2900; //这种方式效率低。

select max(sql) ,deptno from emp where sal >2900 group by deptno;//效率较高

找出每个部门的平均薪资，要求显示薪资大于2000的数据

第一步：找出每个部门的平均薪资

select deptno,avg(sal) from emp group by deptno;

第二步：要求显示薪资大于2000的数据

select deptno ,avg(sal) from emp group by deptno having avg(sal)>2000;

select deptno ,avg(sal)

17、总结一个完整的DQL语句怎么写？

Select 5

...

From 1

...

where 2

...

group by 3

...

having 4

...

order by 6

...

day 02

1、关于查询结果集的去重？

select distinct job from emp;

select ename ,distinct job from emp;

以上的sql语句是错误的。

记住：distinct 智能出现在所有字段的最前面

select distinct deptno ,job from emp;//distinct 对后面的两个字段联合去重

案例：统计岗位的数量？

select count(distinct job) from emp;

2、连接查询

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

在实际开发中，一般一个业务都会对应多张表，比如：学生和班级，起码两张表。

学生和班级信息存储到一张表中，结果就像上面一样，数据会存在大量的重复，导致数据的冗余。

2.2、连接查询的分类？

根据语法出现的年代来划分的话，包括：

SQL92（一些老的DBA可能还在使用这种语法。DBA: DataBase Administrator，数据库管理员）

SQL99(比较新的语法)

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

内连接：

等值连接

非等值连接

自连接

外连接：

左外链接（左链接）

右外连接（右链接）

全连接（这个不讲，很少用）

2.3、在表的连接查询方面有一种现象被称为：笛卡尔积现象。（笛卡尔乘积现象）

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

select ename,dname from emp,dept;//(笛卡尔积现象)

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

关于表的别名：

select e.ename ,d.dname from emp e,dept d;

表的别名有什么好处？

第一：执行效率高

第二：可读性好

2.4、怎么避免笛卡尔积现象？当然是加条件进行过滤。

思考：避免了笛卡尔积现象，会减少记录的匹配次数吗？

不会，次数还是56次。只不过显示的是有效记录。

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

select

e.ename,d.dname

from

emp e,dept d

where

e.deptno=d.deptno; //SQL92,以后不用。

2.5、内连接之等值连接：最大特点是：条件是等量关系。

案例：查询每个员工的部门名称，要求显示员工名和部门名。

SQL92：

select

e.ename,d.dname

from

emp e,dept d

where

e.deptno=d.deptno and xxx and xxx;

//结构不清晰

SQL99:(常用的)

select

e.ename,d.dname

from

emp e

inner join

//inner可以省略，带着inner目的是可读性好一些。

dept d

连接条件

where

...

SQL99语法机构更清晰一些：表的连接条件和后来的where条件分离了。

2.6、内连接之非等值连接：最大的特点是：连接条件中的关系是非等量关系。

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

select

e.ename,e.sal,s.grade

from

emp e

inner join

salgrade s

e.sal between s.losal and s.hisal;

2.7、自连接：最大的特点是：一张表看做两张表。自己连接自己。

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

员工的领导编号=领导的员工编号

select

a.ename,b.ename

from

emp a

inner join

emp b

a.mgr=b.empno;

2.8、外连接？

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

内连接：

假设A和B表进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录查询出来，这就是内连接。

外连接：

假设A和B表进行连接，使用外连接的话，AB两张表中有一张表是主表，一张表是副表，主要查询主表中的数据，捎带着查询副表，当附表中的数据没有和主表中的数据匹配上，副表自动模拟出NULL与之匹配。

外连接的分类？

左外链接（左连接）：表示左边的这张表是主表

右外链接（右链接）：表示右边的这张表是主表

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

案例：找出每个员工的上级领导、（所有员工都必须查出来）

（错误--内连接）

select

a.ename '员工' ,b.bname '领导'

from

emp a

（inner）join //inner 可以省略

emp b

a.mgr=b.empno;

（正确--外连接）

select

a.ename '员工'，b.ename '领导'

from

emp a

left (outer) join / /out可以省略

emp b

a.mgr=b.empno;

外连接最重要的特点是：主表的数据无条件的全部查询出来

案例：找出那个部门没有员工？

内连接：

select

e.*,d.*

from

emp e

right join

dept d

e.deptno=d.deptno

where

e.empno is null;

2.9、三张表怎么连接查询？

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

注意，解释一下：

。。。

join

。。。

表示：A表和B表先进行表连接，连接之后A表继续和C表进行连接。

select

e.ename,d.dname,s.grade

from

emp e

join

dept d

e.deptno=d.deptno

join

salgrade s

e.sal between s.losal and s.hisal;

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

方法一：

select

e.ename,d.dname,s.grade

from

emp e

join

dept d

e.deptno=d.deptno

join

salgrade s

e.sal between s.losal and s.hisal

left join

emp e1

e.mgr=e1.empno;

方法二：

select e.ename,d.dname,s.grade,q.ename

from emp e

join dept d

on e.deptno=d.deptno

join salgrade s

on e.sal between s.losal and s.hisal

join emp q

e.mgr=q.empno

Order by s.grade;

insert

delete

update

select

3、子查询

3.1、什么是子查询？子查询都可以出现在哪里？

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

子查询可以出现在哪里？

select

..(select)

From

..(select)

where

..(select)

3.2、where子句当中使用子查询

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

select *from emp where sal>avg(sal) //错误

第一步：找出平均薪资

select avg(sal) from emp;

第二步：where 过滤

select * from emp where sal>2073;

第一步和第二步合并：

select *from emp where sal>(select avg(sal) from emp);

3.3、from后面嵌套子查询

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

第一步：找出每个部门的平均薪水（按照部门编号分组，求sal的平均值）

select deptno ,avg(sal) as avgsal from emp group by deptno;

第二步：将以上的查询结果当做临时表t，让t表和salgrade s 表连接诶，条件是：t.avgral between s.losal and s.histol;

select

t.*,s.grade

from

(select deptno ,avg(sal) as avgral from emp group by deptno )t

join

salgrade s

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.hissal;

第二步：基于以上的结果，继续按照deptno分组，求grade平均值。

select

e.ename,e.sal,e.deptno,s.grade

from

emp e join salgrade s

e.sal

between s.losal and s.hisal;

group by

e.deptno;

3.4、在select后面嵌套子查询。

案例：找出每个员工所在的部门名称，要求显示员工名和部门名。

select

e.ename,d.dname

from

emp e

join

dept d

e.deptno=d.deptno;

select

e.ename,e.deptno,

(select

d.dname

from

dept d

where

e.deptno=d.deptno;

) as dname

from

emp e;

4.union(可以将查询结果集相加)

案例：找出工作岗位是SALSMAN和MANAGER的员工？

第一种：select ename，job from emp where job='MANAGER' or job='SALSMAN'；

第二种：select ename,job from emp where job in ('MANAGER','SALSMAN')；

第三种：UNION

select ename,job from emp where job ='MANAGER'

UNION

select ename,job from emp where job ='SALSMAN';

两张不相干的表中的数据拼接在一起显示。

select ename from emp

union

select dname from dept;

注意：查询的列数要相等。

5、limit(重点中的重点，以后分页查询全靠它了)

5.1、limit是mysql特有的，其他数据库中没有，不通用。（Oracle中有一个相同的机制rownum）

5.2、limit取结果集中的部分数据，这时它的作用。

5.3、语法机制

limit startIndex，length

startIndex表示起始位置，从0开始，0表示第一条数据

length表示取几个

案例：取出工资前5名的员工（思路：降序取前5个）

select ename,sal from emp order by sal desc;

取前五个：

select ename ,sal from emp order by sal desc limit 0,5

select ename,sal from emp order by sal desc limit 5;

5.4、limit是sql语句的最后一个执行环节：

select 5

...

from 1

...

where 2

...

group by 3

...

having 4

...

order by 6

...

limit 7

...

5.5、案例：找出工资排名在第四到第九名的员工？

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

5.6、通用的标准分页sql？

每页显示3条记录：

第一页：0, 3

第二页：3, 3

第三页：6, 3

第四页：9, 3

第五页：12,3

每页显示pageSize条记录：

第pageNo页（pageNo-1）*pageSize，pageSize

pageSize是什么？是每页显示多少条记录

pageNo是什么？显示第几页

Java代码{

int pageNo=2; //页码是2

int pageSize=10;//每页显示10条

limit (pageNo-1)*pageSize,pageSize

}

6、创建表：

建表语句的语法格式：

create table 表名（

字段名1 数据类型

字段名2 数据类型

字段名3 数据类型

....

）；

关于MYSQL当中字段的数据类型？以下只说常见的

int 整数型（java中的int）

bigint 长整型（Java中的long）

float 浮点型（Java中的float double）

char 定长浮点型（string）

varchar 可变长字符串（stringbuffer/stringBuilder）

data 日期类型（对应java.sql. data类型）

BLOB 二进制大对象（存储图片、视频等流媒体信息）Binary large OBject （对应Java中的object）

CLOB 字符大对象（存储较大文本，比如，可以存储4G的字符串） Character Large OBject（对应java中的Object）

........

char和varchar怎么选择？

在实际的开发中，当某个字段中的数据长度不发生改变的时候，是定长的，例如：性别、生日等都是采用char。

当一个字段的数据长度不确定，例如：简介、姓名等都是采用varchar。

BLOB和CLOB类型的使用？

eg：

电影表：t_movie

haibao（BLOB）

history（CLOB）

表名在数据库当中一般建议以：t_或者tb1_开始。

创建学生表：

学生信息包括：

学号、姓名、性别、班级编号、生日

学号：bigint

姓名：varchar

性别：char

班级编号：int

生日：char

create table t_student(

no bigint,

name varchar(255),

sex char(1)，

classno varchar(255)，

birth char(10)

);

7、insert语句插入数据

语法格式：

insert into 表名（字段名1，字段名2，字段名3....）values(值1，值2，值3)

要求：字段的数量和值的数量相同，并且数据类型要对应相同 ,只要能对应上，顺序无所谓。

insert into t_student (no,name,sex,classno,birth) values(1,'zahngsan','1','gaosan1ban','1950-10-12');

insert into t_student （name）values（‘wangwu’）// 除name字段外，剩下的所有字段自动插入NULL。

drop table if exists t_student；//当这个表存在的话删除。

create table t_student (

no bigint,

name varcahr(255),

sex char(1) default 1,

classno varchar (255),

birth char(10)

);

insert into t_student (name) values('zhagnsan');

需要注意的地方：

当一条insert语句执行成功之后，表格当中必然会多一行记录。及时多的这一行记录当中某些字段是NULL ，后期也无法在执行insert 语句插入数据了，智能使用update进行更新。

insert into t_student values(1,'jack','0','gaosan2ban','1999-10-06');

//注意：字段可以省略不写，但是后面的value对数量和顺序都有要求。

//一行插入多行数据

insert into t_student (no,name,sex,classno,birth) values(3,'rose','1','gaosna2ban','1952-12-14'),(4,'laotie','1','gaosi2ban','1999-10-06');

8、表的复制

语法：

create table 表名 as select 语句；

将查询结果当做表创建出来。

9、将查询结果插入到一张表中？(表中的数据要对应)

insert into dept select* from dept;

select * from dept1;

10、修改数据：update

语法格式：

update 表名 set 字段名1=值1，字段名2=值2.。。where 条件；

注意：没有条件整张表数据全部更新。

案例：将部门10的loc修改为SHANGHAI,将部门名称修改为RENSHIBU

update dept1 set loc='SHANGHAI',dname='RENSHIBU' where deptno=10;

select * from dept1;

更新所有记录

update dept1 set loc='x',dname='y';

select * from dept1;

11、删除数据？

语法格式：

delete from 表名 where 条件；

注意：没有条件全部删除。

删除10部门数据？

delete from dept1 where deptno =10;

删除所有记录？

delete from dept1;

怎么删除大表中的数据？（重点）

truncate table emp1; //表被截断，不可回滚。永久丢失。

删除表？

drop table 表名；//这个通用

drop table if exists 表名；//oracle 不支持这种写法

12、DQL（select）DML(insert delete update)

DDL(create drop alter)

对于表结构的修改，这里不讲了，大家使用工具完成即可，因为在实际开发中表一旦设计好之后，对表结构的修改是很少的，修改表结构就是对之前的设计进行了否定，即使需要修改表结构，我们也可以直接使用工具操作。修改表结构的语句不会出现在Java代码当中。出现在java代码当中的sql包括：insert delete update select (这些都是表中的数据操作。)

增删改查有一个术语：CRUD操作

Create（增） Retrieve（检索） Update（修改）

Delete（删除）

13、约束（Constraint）

13.1、什么是约束？常见的约束有哪些呢？

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

常见的约束有哪些呢？

非空约束（not null）：约束的字段不能为 NULL。

主键约束（primary key）：约束的字段不能重复

外键约束（foreign key）：简称FK

检查约束（check）：注意oracle数据库有check约束，但是mysql没有，目前mysql不支持该约束

13.2、非空约束 not null

drop table if exists t_user;

create table t_user(

id int ,

username varchar(255) not null,

password varchar(255)

)；

insert into t_user(id ,password) values(1,'123');

insert into t_user(id,username,password) values(1,'lisi','123');

MYSQL DAY03

1、约束

1.1、唯一性约束（unique）

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

*案例：

drop table if exists t_user;

create table t_user(

id int ,

username varchar(255) unique

);

insert into t_user values(1,'zhangsan');

insert into t_user values(2,'zhagnsan')

*案例：给两个列或者多个列添加unique

drop table if exists t_user;

create table t_user(

id int ,

username varchar(255),

email varchar(255),

unique(usercode,username)//多个字段联合起来添加一个约束 [表级约束]

);

drop table if exists t_user;

create table t_user(

id int ,

username varchar(255) unique,//列级约束

email varchar(255) unique

);

*注意：not null 约束只有列级约束，没有表级约束

1.2、主键约束

*怎么给一张表添加主键约束呢？

drop table if exists t_user；

create table t_user(

id int primary key,

username varchar(255),

email varchar(255)

) ;

根据以上的测试得出：id是主键，因为添加了主键约束，主键约束中的数据不能为NULL，也不能重复。

主键的特点：不能为NULL，也不能重复。

主键相关的术语？

主键约束：primary key

主键字段：id字段添加primary key之后，id就叫做主键字段

主键值：id字段中的每一个值都是主键值。

主键有什么作用？

-表的设计三范式中有要求，第一范式就要求任何一张表都应该有主键。

-主键的作用：主键值是记录在这张表当中的唯一标识。（就像一个人的身份证号码一样）

主键的分类？

根据主键字段的字段数量来划分：

单一主键（推荐的，常用的）

复合主键（多个字段联合起来添加一个主键约束）（复合主键不建议使用，因为复合主键违背三范式）

根据主键性质来划分：

自然主键：主键值最好就是一个和业务没有任何关系的自然数。（这种方式是推荐的）

业务主键：主键值和系统的业务挂钩，例如：拿着银行卡的卡号做主键，拿着身份证号码作为主键（不推荐用）最好不要拿着和业务挂钩的字段作为主键。因为以后的业务一但发生改变的时候，主键值可能也需要随着发生变化，但有的时候没有办法变化，因为变化可能会导致主键值重复

一张表的主键约束只能有一个。（必须记住）

使用表级约束方式定义主键：

drop table if exists t_user;

create table t_user(

id int,

username varchar(255),

primary key(id)

);

以下内容是演示以下复合主键，不需要掌握：

drop table if exists t_user;

create table t_user(

id int,

username varchar(255),

primary key(id，username)

);

mysql 提供主键值自增：

drop table if exists t_user;

create table t_user (

id int primary key auto-increment, //id字段自动维护一个自增的数字，从1开始，以1递增。

username varchar(255)

);

提示：oracle当中也提供了一个自增机制，叫做：序列（sequence）对象。

1.3、外键约束

*关于外键约束的相关术语：

外键约束：foreign key

外键字段：添加有外键约束的字段

外键值：外键字段中的每一个值。

*业务背景：

请设计数据库表，用来维护学生和班级的信息？

第一种方案：一张表存储所有数据

no（pk） name classno classname

缺点：冗余。【不推荐】

第二种方案：两张表（班级表和学生表）

t_class 班级表

Cno（pk） cname

-------------------------------------------

t_student 学生表

Sno（pk） sname classno（该字段添加外键约束fk）

-----------------------------------------

*将以上表的建表语句写出来：

t_student中的classno字段引用t_class表中的cno字段，此时t_student表叫做字表。t_class表叫做父表。

顺序要求：

删除数据的时候，先删除子表，再删除父表。

添加数据的时候，先添加父表，再添加子表。

创建表的时候，先创建父表，再查创建子表。

删除表的时候，先删除子表，再删除父表。

drop table if exists t_student;

drop table if exists t_class;

Create table t_class(

Cno int,

Cname varcahr(255),

Primary key (cno)

);

Create table t_student(

Sno int,

Sname varchar(255),

Classno int,

Primary key(sno),

Foreign key(classno) references t_class(cno)

);

Insert into t_class values(101,’xxxxxxxxxxxxxxxxxxxxxxx’);

Insert into t_class values(101,’yyyyyyyyyyyyyyyyyyyyy’);

Insert into t_student values(1,’zs1’,102);

*外键值可以为NULL

外键值可以为NULL。

*外键字段引用其他表的某个字段的时候，被引用的字段必须是外键吗？

注意：被引用的字段不一定是主键，但至少具有unique约束。

存储引擎？

2.1、完整的建表语句

CREATE TABLE `dept` (

`DEPTNO` int(2) NOT NULL,

`DNAME` varchar(14) DEFAULT NULL,

`LOC` varchar(13) DEFAULT NULL,

PRIMARY KEY (`DEPTNO`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

注意：在mysql当中，凡是标识符是可以使用飘号括起来的。最好别用，不通用。

建表的时候可以指定存储引擎，也可以指定字符集。

Mysql默认使用的存储引擎是InnoDB方式。

默认采用的字符集是UTF8

2.2、什么是存储引擎呢？

存储引擎这名字只有在mysql中存在。（oracle中有相应的机制，但是不叫做存储引擎。Oracle中没有特殊的名字，就是”表的存储方式”）

Mysql支持很多存储引擎，每一个存储引擎都对应了一种不同的存储方式。

每一个存储引擎都有自己的优缺点，需要在合适的时机选择合适的存储引擎。

2.3、查看当前mysql支持的存储引擎？

Show engines \G

Show engines

Mysql 5.5.36版本支持的存储引擎有9个：

mysql> show engines;

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |

| MyISAM | YES | MyISAM storage engine | NO | NO | NO |

| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |

| CSV | YES | CSV storage engine | NO | NO | NO |

| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |

| ARCHIVE | YES | Archive storage engine | NO | NO | NO |

| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |

| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

9 rows in set (0.00 sec)

mysql> show engines \G

*************************** 1. row ***************************

Engine: FEDERATED

Support: NO

Comment: Federated MySQL storage engine

Transactions: NULL

XA: NULL

Savepoints: NULL

*************************** 2. row ***************************

Engine: MRG_MYISAM

Support: YES

Comment: Collection of identical MyISAM tables

Transactions: NO

XA: NO

Savepoints: NO

*************************** 3. row ***************************

Engine: MyISAM

Support: YES

Comment: MyISAM storage engine

Transactions: NO

XA: NO

Savepoints: NO

*************************** 4. row ***************************

Engine: BLACKHOLE

Support: YES

Comment: /dev/null storage engine (anything you write to it disappears)

Transactions: NO

XA: NO

Savepoints: NO

*************************** 5. row ***************************

Engine: CSV

Support: YES

Comment: CSV storage engine

Transactions: NO

XA: NO

Savepoints: NO

*************************** 6. row ***************************

Engine: MEMORY

Support: YES

Comment: Hash based, stored in memory, useful for temporary tables

Transactions: NO

XA: NO

Savepoints: NO

*************************** 7. row ***************************

Engine: ARCHIVE

Support: YES

Comment: Archive storage engine

Transactions: NO

XA: NO

Savepoints: NO

*************************** 8. row ***************************

Engine: InnoDB

Support: DEFAULT

Comment: Supports transactions, row-level locking, and foreign keys

Transactions: YES

XA: YES

Savepoints: YES

*************************** 9. row ***************************

Engine: PERFORMANCE_SCHEMA

Support: YES

Comment: Performance Schema

Transactions: NO

XA: NO

Savepoints: NO

9 rows in set (0.00 sec)

2.4、常见的存储引擎？

Engine: MyISAM

Support: YES

Comment: MyISAM storage engine

Transactions: NO

XA: NO

Savepoints: NO

MyISAM 这种存储引擎不支持事务。

MyISAM是mysql最常用的存储引擎，但是这种存储引擎不是默认的。

MyISAM采用三个文件组织一张表：

xxx.frm(存储格式的文件)

xxx.MYD(存储表中数据的文件)

xxx.MYI(存储表中索引的文件)

优点：可被压缩，节省存储空间。并且可以转换为只读表，提高检索效率。

缺点：不支持事务。

MyISAM存储引擎

MyISAM存储引擎是mysql最常用的引擎。

它管理的表具有以下特征：

-使用三个文件表示每个表：

*格式文件-存储表结构的定义（mytable.frm）

*数据文件-存储表行的内容（mytable.MYD）

*索引文件-存储表上索引（mytable.MYI）

-灵活的AUTO_INCREMENT字段处理

-可被转换为压缩、只读表来节省空间

InnoDB存储引擎

*InnoDB存储引擎是mysql的缺省引擎。

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

-每个InnoDB表在数据库目录中以.frm格式文件表示

-InnoDB表空间tablespace被用于存储表的内容

-提供一组用来记录事务性活动的日志文件

-用commit（提交）、SQVAPOINT及ROLLBACK（回滚）支持事务处理

-提供全ACID兼容

-在mysql服务器崩溃后提供自动恢复

-多版本（MVCC）和行级锁定

-支持外键及引用的完整性，包括级联删除和更新

Engine: InnoDB

Support: DEFAULT

Comment: Supports transactions, row-level locking, and foreign keys

Transactions: YES

XA: YES

Savepoints: YES

优点：支持事务、行级锁、外键等。这种存储引擎数据的安全得到保障。

表的结构存储在xxx.frm文件中

数据存储在tablespace这样的表空间中（逻辑概念），无法被压缩，无法转换成只读。

这种InnoDB存储引擎在MYSQL数据库崩溃之后提供自动恢复机制。

InnoDB支持级联删除和级联更新。

Engine: MEMORY

Support: YES

Comment: Hash based, stored in memory, useful for temporary tables

Transactions: NO

XA: NO

Savepoints: NO

缺点：不支持事务。数据容易丢失。因为所有数据和索引都是存储在内存当中的。

优点：查询速度最快。

以前叫做HEPA引擎。

MEMORY存储引擎

*使用MEMORY存储引擎的表，其数据存储在内容中，且行的长度固定，这两个特点使得MEMORY

存储引擎非常快。

*MEMORY存储引擎管理的表具有下列特征：

-在数据库目录内，每个表均以.frm格式的文件表示。

-表数据及索引被存储在内存中。

-表级锁机制。

-不能包含TEXT或BLOB字段。

*MEMORY存储引擎以前被称为HEAP引擎

总结：

*MyISAM表最适合于大量的数据读而少量数据更新的混合操作。MyISAM表的另一种适合情形是使用压缩的只读表。

*如果查询中包含较多的数据更新操作，应使用InnoDB。其行级锁机制和多版本的支持为数据读取和更新的混合操作提供了良好的并发机制。

*可使用MEMORY存储引擎来存储非永久需要的数据，或者是能够从基于键盘的表中重新生成的数据。

Engine: MEMORY

Support: YES

Comment: Hash based, stored in memory, useful for temporary tables

Transactions: NO

XA: NO

Savepoints: NO

缺点：不支持事务。数据容易丢失。因为所有数据和索引都是存储在内存当中的。

优点：查询速度最快。

以前叫做HEPA引擎。

事务（Transaction）

3.1、什么是事务？

一个事务是一个完整的业务逻辑单元，不可再分。

比如：银行账户转账，从A账户向B账户转账10000.需要执行两条update语句。

Update t_act set balance=balance-10000 where actno=’act-001’;

Update t_act set balance=balance+10000 where actno=’act-002’;

以上两条DML语句必须同时成功，或者同时失败，不允许出现一条成功，一条失败。

要想保证以上的两条DML语句同时成功或者同时失败，那么就需要使用数据库的“事务机制”。

事务可以保证多个操作原子性，要么全成功，要么全失败。对于数据库来说事务保证批量的DML要么全成功，要么全失败。事务具有四个特征ACID

原子性（Atomicity）

*整个事务中的所有操作，必须作为一个单元全部完成（或全部取消）。

C）一致性（Consistency）

*在事务开始之前与结束之后，数据库都保持一致状态。

隔离性（isolation）

*一个事务不会影响其他事务的运行。

D）持久性（durability）

*持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。

3.2、和事务相关的语句只有：DML语句。（insert delete update）

为什么？因为它们这三个语句都适合数据库表当中的“数据”相关的。

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

3.3、假设所有的业务的都能使用一条DML语句搞定，还需要事务机制吗？

不需要事务。

但实际情况不是这样的，通常一个“事儿（事务【业务】）”需要多条DML语句共同联合完成。

3.4、事务的原理

假设一个事儿，需要先执行一条insert，再执行一条update，最后执行一条delete。这个事儿才算完成。

开启事务机制：

执行insert语句-->insert...（这个执行成功之后，把这个执行记录到数据库的操作历史当中，并不会向文件中保存一条数据，不会真正的修改硬盘上的数据。）

执行update语句--->update...（这个执行也是记录一下历史操作，不会真正的修改硬盘上的数据）

执行delete语句--->delete...（这个执行也是记录一下历史操作【记录到缓存】，不会真正的修改硬盘上的数据）

提交事务或者回滚事务（结束）

3.4、事务的特性？

事务包括四大特性：ACID

A：原子性：事务是最小的工作单元，不可再分。

C：一致性：事务必须保证多条DML语句同时成功或者同时失败。

I: 隔离性：事务A与事务B之间具有隔离。

D：持久性：持久性说的是最终数据必须持久化到硬盘文件中，事务才算成功的结束。

3.5、关于事务之间的隔离性

事务隔离性存在隔离级别，理论上隔离级别包括四个：

第一级别：读未提交（read uncommitted）

对方事务还没有提交，我们当前事务可以读取到对方未提交的数据。

读未提交存在脏读（dirty read现象）：表示读到了脏的数据。

第二级别：读已提交（read committed）

对方事务提交之后的数据我方可以读取到。

这种隔离级别解决了：脏读现象没有了。

读已提交存在的问题是：不可重复读。

第三级别：可重复读（repeatable read）

这种隔离级别解决了：不可重复读问题。

第四级别：序列化读/串行化读（serializable

）

解决了所有问题。

效率低。需要事务排队。

Oracle数据库默认的隔离级别是二挡起步：读已提交。（read committed）

Mysql 数据库默认的隔离级别是三档起步：可重复读（repeatable read）。

3.6、演示事务

*mysql事务默认情况下是自动提交的。（什么是自动提交？只要执行任意一条DML语句则提交一次。）怎么关闭自动提交？ start transaction；

*准备表：

Drop table if exists t_user;

Create table t_user(

Id int primary key auto_increment,

Username varchar(255)

);

*演示：mysql中的事务是支持自动提交的，只要执行一条DML，则提交一次。

Insert into t_user(username) values(‘zs’);

select * from t_user;
Rollback;
Select *from t_user;

*演示：使用start transaction；关闭自动提交机制。

Start transaction；
Insert into t_user(username ) values(‘lisi’);
Insert into t_user(username ) values(‘wangwu’);
select * from t_user;
Rollback;
select * from t_user;

*演示提交

insert into t_user(username) values(‘rose’);
Insert into t_user(username) values(‘jack’);
Select * from t_user;
Commit; //已经将数据持久到硬盘中了。
Select * from t_user;
Rollback;
Select * from t_user;

4.7、使用两个事务演示以上的隔离级别

第一：演示read uncommitted(使用两个窗口)

use bjpowernode;

Set global transaction isolation level read uncommitted;//设置事务的全局隔离级别

Select @@global.tx isolation;//查看事务的全局隔离级别

退出窗口，重新开启

Use bjpowernode;

Start transaction;

Select * from t_user;(table1)

Insert into t_user(username) values(‘smith’);(table2)

Select * from t_user;(可查询到smith)

第二：演示read committed（读已提交）

Use bjpowernode;

Set global transaction isolation level read committed;

Select @@global.tx_isolation;

Exit;

use bjpowernode;(两个窗口同时使用)

Insert into t_user(name) values(‘test’);(窗口2)

Select * from t_user;(窗口1 未查询到记录)

Commit（窗口2）；

Select * from t_user;(窗口1，可查询到结果)

第三：演示repeatable read

set global transaction isolation level repeatable;

Select @@global.tx_isolation;

Exit;

Use bjpowernode;(两个窗口)

Start transaction；

Delete from t_user；（窗口二）

Commit;（窗口二）

Select *from t_user;(窗口二)

Insert into t_user(username) values(‘test’);(窗口二)

Commit;(窗口二)

Select * from t_user;（窗口二）

Select * from t_user；(窗口一)

第四：演示serializable

set transaction isolation level serializable;

Select @@global.tx_isolation;

Use bjpowernode;(both two)

Start transaction;(both two)

Select * from t_user;（窗口1）

Insert into t_user(username) values(‘hehe’);(窗口1)

Select * from t_user;(窗口二)

Commit；（窗口一）

Select * from t_user;(窗口二)

索引

4.1、什么是索引？有什么用？

索引就相当于一本书的目录，通过目录可以快速地找到对应的资源。在数据库方面，查询一张表的时候有两种检索方式：

第一种方式：全表扫描

第二种方式：根据索引检索（效率很高）

索引为什么可以提高检索效率呢？

其实最根本的原理是缩小了扫描的范围。

索引虽然可以提高检索效率，但是不能随意的添加索引，因为索引也是数据库当中的对象，也需要数据库不断地维护。是有维护成本的。比如，表中的数据也经常被修改这样就不适合添加索引，因为数据一旦修改，索引需要重新排序，进行维护。

添加索引是给某一个字段，或者说某些字段添加索引。

Select ename ,sal from emp where ename=’smith’;

当ename字段上没有添加索引的时候，以上sql语句会进行全表扫描，扫描enamel字段中的所有的值。

当enamel字段上添加索引的时候，以上sql语句辉根据索引扫描，快速定位。

4.2、怎么创建索引对象？怎么删除索引对象？

创建索引对象：

Create index 索引名称 on 表名（字段名）；

删除索引对象：

Drop index 索引名称（on 表名）；

4.3、什么时候考虑给字段添加索引？（满足什么条件）

*数据量庞大。（根据客户的需求，根据线上的环境）

*该字段很少的DML操作。（因为字段进行修改操作，索引也需要维护）

*该字段经常出现在where字句中。（经常根据哪个字段查询）

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

4.5、查看sql语句的执行计划：

Explain select ename,sal from emp where sal=5000;

给薪资sal字段添加索引：

Create index emp_sal_index on emp(sal);

Explain select ename,sal from emp where sal=5000;

4.6、索引底层采用的数据结构是：B+Tree

4.7、索引的实现原理？

通过B tree 缩小扫描范围，底层索引进行了排序，分析，索引会携带数据在表中的“物理地址”，最终通过索引检索到数据之后，获取到关联的物理地址，通过物理地址定位表中的数据，效率是最高的。

Select ename from emp where ename=’smith’;

通过索引转换为：

Select ename from emp where 物理地址=0x33；

4.8、索引的分类？

单一索引：给单个字段添加索引

复合索引：给多个字段联合起来添加一个索引

主键索引：主键上会自动添加索引

唯一索引：有unique约束的字段上会自动添加索引

。。。

4.9、索引什么时候失效？

Select ename from emp where ename like ‘%A%’;

模糊查询的时候，第一个通配符使用的是%，这个时候索引是失效的。

视图

5.1、什么是视图

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

*视图是一种根据查询（也就是SELECT表达式）定义的数据库对象，用于获取想要看到和使用的局部数据。

*视图有时也被称为“虚拟表”。

*视图可以被用来从常规表（称为“基表”）或其他视图中查询数据。

*相对于从基表中直接获取数据，视图有以下好处：

-访问数据变得简单

-可被用来对不同用户显示不同的表的内容

用来协助适配表的结构以适应前端现有的应用程序

5.2、视图作用

1、视图隐藏了底层的表结构，简化了数据访问操作，客户端不再需要知道底层表的结构及其之间的关系。

2、视图提供了一个统一访问数据的接口。（即可以允许用户通过视图访问数据的安全机制，而不授予用户直接访问底层表的权限）。

3、从而加强了安全性，使用户只能看到视图所显示的数据。

4、视图还可以被嵌套，一个视图中可以嵌套另一个视图。

5.3、怎么创建视图？怎么删除视图？

Create view myview as select empno,ename from emp;

Drop view myview;

注意：只有DQL语句才能以视图对象的方式创建出来。

5.4、对视图进行怎删改查，会影响原表数据。（通过视图影响原表数据的，不是直接操作的原表）

5.5、面向视图操作？

Select * from myview;

Create table emp_bak as select * from emp;

Create view myview1 as select empno,ename,sal from emp_bak;

Update myview1 set ename=’hehe’,sal=1 where empno=7369;//通过视图修改原表数据。

Delete from myview1 where empno=7369;//通过视图删除原表数据。

视图示例：

Create view myview2 as select empno a,ename b,sal c from emp_bak;

Select * from myview2;

Insert into myview2(a,b,c) values(...);

5.6、视图的作用？

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

DBA命令

6.1、将数据库当中的数据导出

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

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

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

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

6.2、导入数据

Create database bjpowernode;

Use bjpowernode;

Source D:\bjpowernode.sql

数据库设计三范式（重点内容，面试经常问）

7.1、什么是设计范式？

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

7.2、三范式都是哪些？

第一范式：任何一张表都应该有主键，并且每一个字段原子性不可再分。

第二范式：建立在第一范式的基础之上，所有非主键字段完全依赖主键，不能产生部分依赖。

多对多？三张表，关系表两个外键。

t_student学生表

sno（pk） sname

t_teacher讲师表

Tno（pk） tname

t_student_teacher_relation 学生讲师关系表

Id（pk） sno（fk） tno（fk）

第三范式：建立在第二范式的基础之上，所有非主键字段直接依赖主键，不能产生传递依赖。

一对多？两张表，多的表加外键。

班级t_class

cno（pk） cname

学生 t_student

Sno（pk） sname classno（fk）

提醒：在实际的开发中，以满足客户的需求为主，有的时候会拿冗余换执行速度。

7.3、一对一怎么设计？

一对一设计有两种方案：主键共享

t_user_login 用户登录表

Id（pk） username password

t_user_detail 用户详细信息表

Id（pk+fk） realname tel userid（fk+unique）

一对一设计有两种方案：外键唯一。

t_user_login 用户登录表

Id（pk） username password

t_user_detail 用户详细信息表

Id（pk） realname tel userid（fk+unique）

码农公寓

数据库学习笔记

相关文章