Mysql详细介绍

李白走高跷

于 2024-04-01 16:43:04 发布

阅读量591

点赞数 22

文章标签： mysql oracle 数据库

本文链接：https://blog.csdn.net/2301_80984114/article/details/137238692

版权

MySQL数据库

原理定义概念

数据库(Database)是按照数据结构来组织、存储和管理数据的建立在计算机存储设备上的仓库。数据库是长期储存在计算机内、有组织的、可共享的数据集合。

分类

非结构化数据：
- 数据相对来讲没有固定的特点，这种数据非常不便于检索
- 但是生活中很多都是非结构化数据
半结构化数据
- 数据之间有着相同的存储结构属性：值
- 每一条数据的属性可以不同
结构化数据
- 创建在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。
- 数据之间有着相同的存储结构属性值
- 同一个结构模型中每一条数据的属性都是相同的，但是值有可能不同，这些数据非常便于存储和管理。
- 于是针对这些数据，专门设计一套数据存储的软件，现在我们说的数据库主要用来存储这些数据

问题

我们所有的数据库都放在软件中
我们要获取到数据，就需要使用软件给我们提供的访问数据的接口
最痛苦的就是程序员，我们就要为存储数据学习不同的数据库
于是有人开始倡导：使用统一的操作数据库的方式

SQL

(Structured Query Language)结构化查询语言
全世界所有的结构化数据库都适用
分类
- DDL 数据库定义语言（定义数据库的一些组件表索引视图自增序列...）
- DML 数据库操作语言（添加删除修改） CRUD
- DQL 数据库查询语言查询
- DCL数据库控制语言（权限用户管理...）

SQL语言属于第四代语言，而java c++ 才属于第三代。

安装

官网：MySQL

下载地址：MySQL :: Download MySQL Community Server

安装过程详见课堂操作

安装路径 C:\Program Files\MySQL\MySQL Server 8.0

root密码 123456
端口 3306
数据库已经安装完成并可以使用了，但是现在就是操作不方便
进入mysql数据库的命令
cmd 进入到doc窗口
- 输入 cd C:\Program Files\MySQL\MySQL Server 8.0\bin
- mysql -u root -p
- 输入密码 123456
show databases;

安装mysql的访问工具，navicat 收费的，我们需要进行简单的破解。

数据库连接错误

1045 用户名和密码不匹配用户名root 密码 123456
2003 连接不到mysql服务器，很常见的是因为启动失败

概念

关系模型中常用的概念：

关系：可以理解为一张二维表，每个关系都具有一个关系名，就是通常说的表名
元组：可以理解为二维表中的一行，在数据库中经常被称为记录
属性：可以理解为二维表中的一列，在数据库中经常被称为字段
域：属性的取值范围，也就是数据库中某一列的取值限制
关键字：一组可以唯一标识元组的属性，数据库中常称为主键，由一个或多个列组成
关系模式：指对关系的描述。其格式为：关系名(属性1，属性2， ... ... ，属性N)，在数据库中成为表结构

scott用例表

emp 员工信息表

字段	描述	类型
empno	员工编号	int
ename	姓名	varchar
job	职位	varchar
mgr	经理	int
hiredate	入职时间	date
sal	薪资	int
comm	奖金	int
deptno	部门编号	int

dept 部门信息表

字段	描述	类型
deptno	部门编号	int
dname	部门名称	varchar
loc	地址	varchar

salgrade 薪资区间表

字段	描述	类型
grade	等级	int
losal	最低薪资	int
hisal	最高薪资	int

SQL的分类

DDL（Data Definition Languages)：数据定义语言，这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象。

主要的语句关键字包括create、drop、alter等。

DML（Data Manipulation Language）：数据操作语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性。

主要的语句关键字包括insert、delete、update、select等。

DCL（Data Control Language）：数据控制语句，用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。

主要的语句关键字包括grant、revoke等。

DQL语句（query）

格式

select 列名*N from 表名 where 查询条件1 and/or 查询条件2 group by 列 Having 分组条件 Order by 排序

规则

sql在书写的时候除了查询条件之外，大小写都可以。

select * from user where uname ='zs';
SELECT * FROM USER WHERE UNAME = 'zs';
保持大小写风格
去公司之后需要看其他员工SQL大小写
--属于sql语句的注释
所有的查询条件为字符串时，需要用''单引号进行修饰，否则就会当做列名去处理

注意1：

MySQL中字符串用单引号和双引号都可以，但是mysql 里面有个 sql mode 叫做：ANSI_QUOTES 。这个 ANSI_QUOTES 开启后会把双引号当作 ``。所以我们还是建议使用单引号来引用字符串。

注意2：

` 是 MySQL 的转义符，避免和 mysql 的本身的关键字冲突，只要你不在列名、表名中使用 mysql 的保留字或中文，就不需要转义。

举例：select `from` from `table`;

select查询列和别名

-- 查询所有员工信息(*通配符，默认查询所有的列)
select * from emp;
-- 查询员工的姓名
select ename from emp;
-- 查询员工的薪资
select sal from emp;
-- 查询员工的姓名和薪资
select ename , sal from emp;
select ename sal from emp;
select ename sal comm from emp;
-- 查询员工的姓名和薪资，推荐使用单引号
select ename '员工姓名', sal "薪资" from emp;
-- 查询到的数据可以直接进行运算
select ename ,sal ,sal * 12 from emp;
select ename ,sal ,comm ,(sal+comm) * 12 from emp;

select的条件查询

普通条件查询 = > < != <> >= <=

-- 查询员工编号为7369的员工
select ename,sal from emp where empno = 7369;
-- 查询员工姓名叫做SMITH
select ename,deptno,job from emp where ename = "SMITH";
select ename,deptno,job from emp where ename = "smith";
-- 查询薪资大于2000的员工姓名
select ename from emp where sal > 2000;
-- 查询工作为SALESMAN
select * from emp where job = "SALESMAN";
-- 查询部门在20的员工
select * from emp where deptno = 20;
-- 查询薪资不等于2000的员工
select * from emp where sal != 2000;

in 在某个范围中查找

-- 查询 员工编号为 7369 7788 7881的员工信息
select * from emp where empno in (7369,7788,7881);
-- 查询 员工编号除了 7369 7788 7881之外的所有员工信息
select * from emp where empno not in(7369,7788,7881);
-- 查询除了10,20部门之外的所有员工
select * from emp where deptno not in (10,20);

null值查询

-- 查询不发放津贴的员工信息
select * from emp where comm is null;
-- 查询发放津贴的员工信息
select * from emp where comm is not null;

范围比较

-- 查询薪资范围在1000-4000之间的员工信息 [1000.4000]
select * from emp where sal between 1000 and 4000;

模糊查询 % _

-- 查询名字中有S的员工
select * from emp where ename like '%S%';
-- 查询名字最后一个字符是S
select * from emp where ename like '%S';
-- 查询名字第一个字符是S
select * from emp where ename like 'S%';
-- 查询名字第二个字符是A
select * from emp where ename like '_A%';
-- 查询名字中有%的员工
select * from emp where ename like '%\%%';

多条件联合查询 and or

--查询在20部门并且薪资大于2000的员工
select * from emp where deptno =20 and sal >2000;
--查询在20部门或者薪资大于2000的员工
select * from emp where deptno = 20 or sal >2000;
--查询不在20部门并且薪资小于2000的员工
select * from emp where deptno <> 20 and sal <2000;

select结果排序 order by

-- 排序参照只有一列
-- 按照薪资进行排序（默认升序）
select * from emp order by sal;
-- 按照薪资进行排序（降序）
select * from emp order by sal desc;
-- 按照薪资进行排序（升序）
select * from emp order by sal asc;
-- 按照津贴进行排序(null排在最前面)
select * from emp order by comm;

-- 排序参照多个列
select * from emp order by deptno,sal;
-- 多个排序的列（部门升序 薪资降序）
select * from emp order by deptno,sal desc;
-- 多个排序的列（工作，薪资）
select * from emp order by job,sal;

select结果分页

-- 每次查询前N行
SELECT
*
FROM
    emp
    LIMIT 4;
    
-- 查询第N页,每页显示M个
select * from emp limit 0,3;
select * from emp limit 3,3;
select * from emp limit 6,3;
select * from emp limit (n-1)*M,M;
-- 查询薪资大于1000的逆序排列，然后显示前5条记录
select * from emp where sal >1000 order by sal desc limit 0,5 ;

SQL语句错误

select enane from emp

1054 - Unknown column 'enane' in 'field list' 将来出现错误的时候，直接将错误编号输入到百度查询即可。

单行函数

函数都是数据库提前给我们准备好的，所以我们可以直接调用，使用函数可以让指定的列计算出我们需要的数据。

单行函数，指的是操作一行数据返回一行数据，操作10行数据返回10行数据。

字符串函数

函数名称	作用
LENGTH	计算字符串长度函数，返回字符串的字节长度
CONCAT	合并字符串函数，返回结果为连接参数产生的字符串，参数可以是一个或多个
REPLACE	替换字符串函数
LOWER	将字符串中的字母转换为小写
UPPER	将字符串中的字母转换为大写
LEFT	从左侧截取字符串，返回字符串左边的若干个字符
RIGHT	从右侧截取字符串，返回字符串右边的若干个字符
TRIM	删除字符串左右两侧的空格
REPLACE	字符串替换函数，返回替换后的新字符串
SUBSTRING	截取字符串，返回从指定位置开始的指定长度的字符串
REVERSE	字符串反转函数

-- 长度
- select ename,length(ename) from emp;
-- 截取
- select ename,SUBSTR(ename,1,3) from emp;
- select * from emp where substr(ename,5,1)='S';
-- 大小写
- select ename, upper(ename),lower(ename) from emp;
-- 拼接
- select CONCAT(empno,'=',ename) from emp;
-- 替换
- select ename,REPLACE(ename,'T','—') from emp;

日期函数

-- 获取当前系统时间
- select hiredate,sysdate() from emp;
- select
hiredate,CURRENT_DATE(),CURRENT_TIME(),CURRENT_TIMESTAMP() from
emp;
-- 日期转换
- select DATE_FORMAT(sysdate(),'%Y-%m-%d %H:%i:%s')
- select hiredate, date_format(now(),'%Y年%m月%d日 %H时%i分%s秒')
from emp;
-- 分别获取 年月日 时分秒 星期
- select 
 - SECOND MINUTE HOUR DAY WEEK MONTH YEAR
-- 日期的加减操作
- select hiredate,ADDDATE(hiredate,9),ADDDATE(hiredate,-9) from
emp;
- select DATE('2022-11-11') ;

数字函数

函数名称	作用
ABS	求绝对值
SQRT	求二次方根
MOD	求余数
FLOOR	向下取整，返回值转为为一个GIBINT

-- 向上取整 向下取整
- select ceil(12,1),floor(12.9)
-- mod abs pow PI rand round TRUNCATE(直接进行截取，不进行四舍五入)
-- 保留多少位有效数字
- select round(1.4999999,2),round(1.4999999),round(1.4999999,-1)
- select TRUNCATE(1.4999999,2)

转换函数

-- 日期--》字符串
- date_format(date,expr)
- select DATE_FORMAT(sysdate(),'%Y-%m-%d %H:%i:%s');
-- 字符串--》日期
- 要注意字符串和格式的匹配
- select STR_TO_DATE('2020-4-16 17:15:24','%Y-%c-%d %H:%i:%s');
-- 数字--》字符串
- 直接拼接一个字符串即可，可以自动转换
-- 字符串--》数字
- 依靠函数提供的参数

其他函数

空值的处理 ifnull(exp1,exp2) exp1!=null?exp1:exp2 select IFNULL(comm,888) from emp;
加密算法 select MD5('123456'); select AES_ENCRYPT('123456','abcd'),AES_DECRYPT(AES_ENCRYPT('123456','abcd'),'a bcd');

多行函数（聚合函数）

不管函数处理多少条，只返回一条记录
如果你的数据可以分为多个组，那么返回的数据条数和组数相同
- 每个部门的平均薪资
- 10 20 30 --》3
常用的多行函数有5个
- max 最大值如果处理的值是字符串，将会把值按照字典序排序
- min 最小值如果处理的值是字符串，将会把值按照字典序排序
- avg 平均值只能用于数值型数据，求平均值
- sum 求和如果求和过程中有null，那么不会计算在内
- count 求总数如果统计的数据中有null,不会把null统计在内
经典的错误
- --查询公司最低薪资的员工是谁？ select min(sal) ,ename from emp;

mysql语法可行,oracle不可行。

将来工作的时候不能把普通列和组函数写在一起，虽然mysql语法不会报错，但是给的结果是错误的。

数据分组

按照某一个条件进行分组，每一组返回对应的结果
group by 可以对指定的列进行分组，列尽量有相同的
having可以对分组之后的数据进行过滤，所以能出现在having中的比较项一定是被分组的列或者是组函数
底层
- where称之为行级过滤，处理的是表中每一行数据的过滤
- having称之为组级过滤，处理的是分组之后的每一组数据
- 能使用where的，尽量不要使用having

-查询每种工作的平均薪资
select job,avg(sal) from emp group by job;
--查询每个部门的最高薪资和最低薪资
select max(sal),min(sal) from emp;
select deptno,max(sal),min(sal) from emp group by deptno;
--查询每个部门的人数和每月工资总数
select deptno,count(empno),sum(sal) from emp group by deptno;
--查询每个部门，每种工作的平均薪资
select deptno,job , avg(sal) from emp group by deptno,job;
select deptno,job , avg(sal) from emp group by deptno,job order by depto
,job;

--查询平均薪资高于2500的部门
select deptno,avg(sal) from emp group by deptno having avg(sal)>=2500;
select deptno,avg(sal) from emp group by deptno having ename like '%A%';
--查询20部门的平均薪资
select deptno,avg(sal) from emp group by deptno having deptno = 20;
select deptno,avg(sal) from emp where deptno = 20 group by deptno;

--查询10 20部门中，每个部门中平均薪资高于1500的工作是什么，并按照
部门，工作平均薪资进行排序
select * from emp where deptno in (10,20) ;
select deptno ,job ,avg(sal) from emp where deptno in (10,20) group by
deptno ,job having avg(sal)>1500 ;
select deptno ,job ,avg(sal) from emp where deptno in (10,20) group by
deptno ,job having avg(sal)>1500 order by deptno ,avg(sal) ;

-- 查询在二月份入职员工是谁
SELECT * FROM emp WHERE DATE_FORMAT(HIREDATE,'%m') = 2;
--美观写法
SELECT
    deptno,
    job,
    avg( sal ) '平均薪资'
FROM
	emp
WHERE
    deptno IN ( 10, 20 )
GROUP BY
    deptno,
    job
HAVING
	avg( sal )> 1500
ORDER BY
    deptno,
    avg( sal );

执行顺序

select 我们要显示那些列的数据
from 从那张表中获取数据
where 从表中获取数据的时候进行行级的数据过滤
group by 对数据进行分组处理，一组获取对应的结果
having 组级过滤，组级过滤的数据必须是分组条件或者是组函数
order by 排序 asc desc

执行顺序：from --> where -->group by -->having-->select -->order by

多表查询

基本规则

查询的两张表如果出现同名的列，我们需要将表名标注到列名前面
如果是非同名的列，表名可加可不加，推荐加上
- 为了书写方便，可以给表添加别名
- 一般情况下取首字母，特殊情况下取它所代表的含义
- 表的别名只在本次查询中生效
如果表与表进行关联查询的时候，如果不添加关联条件，查询的总记录数就是a*b = 笛卡尔积
- a 15 b 10 c 10 -->1500条
多表查询的时候必须要加条件
- 等值
- 非等值

--查询每个员工所在的部门名称
select ename,deptno from emp;
select deptno,dname from dept;
select emp.ename,emp.deptno,dept.deptno,dept.dname from emp , dept;
--等值关联查询
select emp.ename,emp.deptno,dept.deptno,dept.dname from emp , dept where
emp.deptno = dept.deptno;
select emp.ename,dept.dname from emp , dept where emp.deptno = dept.deptno;
--添加别名
select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno;

表与表关联的方式

因为表的关联条件和业务查询条件放在了一起，为了防止混淆于是提供了下面三种方式

自然连接，会自动选择列名相同并且类型相同的列

--查询薪资大于2000的员工姓名和部门名称
select e.ename,d.dname from emp e ,dept d where e.deptno = d.deptno and
e.sal >2000;
--自然连接
select e.ename,d.dname from emp e natural join dept d ;
select e.ename,d.dname from emp e natural join dept d where e.sal >
2000 ;

using，不需要mysql帮我们选择等值连接的列，现在我们指定等值连接的列

----查询薪资大于2000的员工姓名和部门名称 using
select e.ename,d.dname from emp e join dept d using(deptno);
select e.ename,d.dname from emp e join dept d using(deptno) where e.sal
> 2000;

on，我们可以指定两张表关联的条件，可以是非等值的操作

----查询薪资大于2000的员工姓名和部门名称 using
select e.ename,d.dname from emp e join dept d on(e.deptno = d.deptno);
select e.ename,d.dname from emp e join dept d on(e.deptno = d.deptno)
where e.sal > 2000;
--查询每个员工所对应的薪资等级
select e.ename,s.grade from emp e,salgrade s where e.sal between
s.losal and s.hisal;
select e.ename,s.grade from emp e join salgrade s on(e.sal between
s.losal and s.hisal);

查询名字中带有A字母的员工姓名，部门名称和薪资等级

SELECT
    e.ename,
    d.dname,
    s.grade
FROM
    emp e,
    dept d,
    salgrade s
WHERE
	e.deptno = d.deptno
	AND e.sal BETWEEN s.losal AND s.hisal
	AND e.ename LIKE '%A%';
-----------------------------------------
SELECT
    e.ename,
    d.dname,
    s.grade
FROM
	emp e
	JOIN dept d USING ( deptno )
	JOIN salgrade s ON (e.sal BETWEEN s.losal AND s.hisal)
WHERE
	e.ename LIKE '%A%';

表与表的外连接

当我们对两张表进行关联查询的时候，基于数据的原因导致其中一张表中的数据没办法被完全查询出来。

外连接可以让没查询出来的数据也显示出来。

因为我们写SQL的时候表总有左右之分 ,外连接也分为

左外连接：显示左面表所有的数据
右外连接：显示右面表所有的数据

--统计每个部门的人数
select deptno,count(empno) from emp group by deptno ;
select * from emp e join dept d using(deptno);
select * from emp e left join dept d using(deptno);
select * from emp e right join dept d using(deptno);
select deptno,count(e.empno) from emp e right join dept d using(deptno)
group by deptno;
-------------------------全外连接
SELECT
    deptno,
    e.ename,
    d.dname
FROM
	emp e RIGHT JOIN dept d USING ( deptno )
UNION
SELECT
    deptno,
    e.ename,
    d.dname
FROM
	emp e LEFT JOIN dept d USING ( deptno );
-------------------------Oracle的全外连接使用 Full Join

表与表的自连接

我们要查询的两个字段同时处于一张表上，我们只能将一张表当做含有不同意义的两张表去处理。

给相同的表取不同的简称（按照所代表的含义去取）

-- 查询每个员工与其直属领导的名字
select e.ename,m.ename from emp e,emp m where e.mgr = m.empno;
select e.ename,m.ename from emp e join emp m on(e.mgr = m.empno);

表与表的子连接

把一个SQL语句的查询结果当成另外一个SQL语句的查询条件

--查询公司中薪资最低的员工姓名
select ename,sal from emp where sal = (select min(sal) from emp);
--查询公司中谁的薪资高于平均薪资
select ename,sal from emp where sal > (select avg(sal) from emp);
--谁的薪资高于20部门员工的薪资
select ename,sal from emp where sal > all(select sal from emp where deptno =
20 );
select ename,sal from emp where sal > some(select sal from emp where deptno
= 20 );
select ename,sal from emp where sal in (select sal from emp where deptno =
20 );

表与表的伪表查询

如果我们所需要的查询条件需要别的SQL语句提供

如果只需要一个条件那么可以使用子查询来完成

如果需要多个查询条件，这是就要将所有的查询结果当做伪表进行管理

我们需要把一些含有特殊符号的列名设置别名，然后给伪表设置一个别名（见名知意）

--查询高于自己部门平均薪资的员工信息
select deptno,avg(sal) avgsal from emp group by deptno ;

SELECT
    e.ename,
    e.sal,
    e.deptno
FROM
	emp e,
	( SELECT deptno, avg( sal ) avgsal FROM emp GROUP BY deptno ) d
WHERE
    e.deptno = d.deptno
    AND e.sal > d.avgsal;

DML语句

Data Manipulation Language，数据操作语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性。

DML插入

insert into 表名 values();

insert into dept values(50,'uek','ty');

要求插入数据的数量，类型要和定义的表结构一致。

insert into 表名(列名) values(值...);

insert into emp(empno,ename,deptno) values(666,'ty',50);

要求插入数据的数量顺序和表名后的列要一致。

insert into 表名(列名) select ....

insert into dept(deptno,dname) select empno ,ename from emp;
create table dp as select * from dept where 1<>1;

DML删除

delete from 表名

delete from dept;

delete from 表名 where 条件

delete from emp where comm is null;

这属于一种物理删除，删完之后理论上不能再找回

truncate table emp;

截断表--不要使用*3

truncate和delete的区别:

1、条件删除。因为delete是可以带WHERE的，所以支持条件删除;而truncate只能删除整个表。

2、事务回滚。由于delete是数据操作语言(DML - Data Manipulation Language)，操作时原数据会被放到 rollback segment中，可以被回滚;而truncate是数据定义语言(DDL - Data Definition Language)，操作时不会进行存储，不能进行回滚。

3、清理速度。在数据量比较小的情况下，delete和truncate的清理速度差别不是很大。但是数据量很大的时候就能看出区别。由于第二项中说的，truncate不需要支持回滚，所以使用的系统和事务日志资源少。delete 语句每次删除一行，并在事务日志中为所删除的每行记录一项，固然会慢，但是相对来说也较安全。

4、高水位重置。随着不断地进行表记录的DML操作，会不断提高表的高水位线(HWM)，delete操作之后虽然表的数据删除了，但是并没有降低表的高水位，随着DML操作数据库容量也只会上升，不会下降。所以如果使用delete，就算将表中的数据减少了很多，在查询时还是很和delete操作前速度一样。

而truncate操作会重置高水位线，数据库容量也会被重置，之后再进行DML操作速度也会有提升。

DML修改

update 表名 set 列名=value ,列名=value

update salgrade set losal = 888 ,hisal = 999;

update 表名 set 列名=value ,列名=value where 条件

update salgrade set losal = 666 ,hisal = 1888 where grade = 3;

数据库事务

概述

数据库管理系统执行过程中的一个逻辑单位，由一个有限的数据库操作序列构成

事务指的是数据库一种保护数据的方式

事务一般由增删改操作自动调用，事务根据数据库不同提交的时机也是不同的

mysql数据库默认执行增删改就会提交事务我们可以设置为手动提交 begin 或者 start transaction;

事务的特征

ACID原则

原子性（atomicity）或不可分割性
- 事务是操作数据的最小单元，不可以再分
一致性（consistency）
- 事务提交之后，整个数据库所看到数据都是最新的数据
- 所有人看到的数据都是一致的
隔离性（isolation）或独立性
- 别人无法访问到我们未提交的数据，而且一旦这个数据被我修改，别人也无法进行操作
持久性（durability）
- 事务一旦被提交，数据库就进入到一个全新的状态
- 数据在也不能返回到上一个状态

事务如何开启和提交

事务的开启：当我们执行增删改操作的时候就会默认开启一个事务，这个事务和当前操作的窗口有关，别人是无法共享这个事务的。

事务的提交：

手动
- 显示，commit;提交；rollback;回滚
- 隐式，执行DDL操作，会默认提交当前的事务；用户退出，事务统一进行回滚（Mysql）
自动
- mysql数据库执行DML操作之后会自动的提交事务。这样的好处是方便，然而坏处是不能将多个SQL纳入到一个事务，不便于管理，另外还有当我们大批量插入数据的时候，数据库会频繁的开启关闭事务影响插入效率

事务的隔离级别

根据数据库的不同用途，我们可以对数据库的事务进行级别的设置级别设置的越高，数据越安全，但是效率越低

读未提交（READ-UNCOMMITTED）
- 我们可以读取到别人未提交的数据
- 有可能产生脏读的问题
读已提交（READ-COMMITTED）
- 只能读取别人提交后的数据
- 不能达到可重复读，但是可以避免脏读
- 有可能产生虚读或者幻读的情况
可重复读（REPEATABLE-READ） mysql默认事务隔离级别
- 当数据被我查询之后，别人就不能修改这个数据了
- 说明在我查询的时候已经有事务操作到这些数据，查询都会开启事务
- 但是不能防止别人查询别的数据
序列化（SERIALIZABLE）
- 当前数据库只能存在一个事务，但我操作数据库的时候，别人是不能访问数据库的
- 这时对于用户来讲数据相当安全，一般在倒库的时候才会开启这种级别

可以通过SHOW VARIABLES LIKE "transaction_isolation";命令查看mysql的事务隔离级别

读取数据情况

脏读读取别人未提交的数据，这个数据是不安全的
虚读第一次读取的数据，第二次在读取的时候有可能被被人修改了
幻读第一次读取的数据，第二次多了一条或者少了一条

DDL语句

Data Definition Languages，数据定义语言，这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象。

常见组成

库的操作，表，视图，存储过程，事件，索引，触发器

数据库的操作

# 数据库创建
create database 数据库名 charset utf8;
# 查看数据库
show databases;
show create database db;
select database();
# 选择数据库
use 数据库名
# 删除数据库
drop database 数据库名;
# 修改数据库
alter database db1 charset utf8;

数据类型

类型	取值范围	占用字节	补充
int/integer	正负21亿	4	推荐使用
varchar(n)	存储n个字符	跟字符集相关，统一utf-8	推荐使用
date	到9999-12-31	3个字节	推荐存生日，订单日期
timestamp	1970-01-01 00：00:01到2038-01-19 03：14:07	8个字节	推荐用来存储时间

表 table的创建

-- 我们首先要对你操作的数据有一个基础型的了解
-- 学号 姓名 性别 出生日期 入学时间 专业 院系 创建时间
-- 学号 int 姓名 varchar 性别 char 出生日期 date 入学时间 date 专业 varchar 院系
varchar 创建时间 timestamp
create table t_student(
     sno int,
     sname varchar(40),
     gender char(1),
     birthday date,
     schooltime date,
     major varchar(255),
     department varchar(255),
     createtime timestamp
);

表 table的修改

--根据查询语句创建表
CREATE TABLE STU01 AS SELECT * FROM t_student;
--添加一列
alter table t_student add updatetime timestamp default now();
--删除一列
alter table t_student drop column email;
--修改一列
alter table t_student modify major varchar(20);
--修改列名
alter table t_student rename COLUMN birthday to birth;
--修改表名
--修改表名
rename table t_student to t_s;
--删除一张表
drop table t_s;

表 table的约束

约束指的是我们创建的表，别人在插入数据的时候，对数据的约束,而不是对创建人的约束。

主键约束 `PRIMARY KEY`

主键值必须唯一标识表中的每一行，且不能为 NULL，即表中不可能存在有相同主键值的两行数据。
主键分为单字段主键和多字段联合主键
联合主键不能包含不必要的多余字段。当把联合主键的某一字段删除后，如果剩下的字段构成的主键仍然满足唯一性原则，那么这个联合主键是不正确的。这是最小化原则。

CREATE TABLE t_pk01(
id INT(11) PRIMARY KEY,
name VARCHAR(25),
deptId INT(11),
salary FLOAT
);
CREATE TABLE t_pk03(
tid INT(11),
cid INT(11),
salary FLOAT,
PRIMARY KEY(tid,cid)
);
CREATE TABLE t_pk02(
id VARCHAR(40),
name VARCHAR(25),
salary FLOAT
);
-- ALTER TABLE <数据表名> ADD PRIMARY KEY(<字段名>);
ALTER TABLE t_pk02 ADD PRIMARY KEY(id);
-- ALTER TABLE <数据表名> DROP PRIMARY KEY;
ALTER TABLE t_pk04 DROP PRIMARY KEY;

唯一性约束 unique

列中的值可以为空但是不能相同

CREATE TABLE tb_unique01(
id INT(11) PRIMARY KEY,
name VARCHAR(22),
phonenum VARCHAR(11) UNIQUE,
location VARCHAR(50)
);
-- ALTER TABLE <数据表名> ADD CONSTRAINT <唯一约束名> UNIQUE(<列名>);
ALTER TABLE tb_unique01 ADD CONSTRAINT t_unique01_unique_location
UNIQUE(location);
-- ALTER TABLE <表名> DROP INDEX <唯一约束名>;
ALTER TABLE tb_unique01 DROP INDEX unique_t_unique01_location;

非空约束 not null

列中的值不能为null

CREATE TABLE tb_null01(
id INT(11) PRIMARY KEY,
name VARCHAR(22) NOT NULL,
location VARCHAR(50)
);
-- ALTER TABLE <数据表名> CHANGE COLUMN <字段名> <字段名> <数据类型> NOT
NULL;
ALTER TABLE tb_null01 CHANGE COLUMN location location VARCHAR(50) NOT
NULL;
-- ALTER TABLE <数据表名> CHANGE COLUMN <字段名> <字段名> <数据类型> NULL;
ALTER TABLE tb_null01 CHANGE COLUMN location location VARCHAR(50) NULL;

外键约束

定义外键时，需要遵守下列规则：

主表必须已经存在于数据库中，或者是当前正在创建的表。如果是后一种情况，则主表与从表是同一个表，这样的表称为自参照表，这种结构称为自参照完整性。
必须为主表定义主键。
主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。
在主表的表名后面指定列名或列名的组合。这个列或列的组合必须是主表的主键或唯一性键。
外键中列的数目必须和主表的主键中列的数目相同。
外键中列的数据类型必须和主表主键中对应列的数据类型相同。

外键的删除

级联删除
- 设置为NULL
- 阻止删除
- 删除主表数据的时候，要保证这个ID没有被字表所使用

-- 1位老师对应N个学生 teacher表为主表，student表为外键表 tid为外键
create table teacher(
 tid varchar(40) primary key,
name varchar(40)
);
create table student(
 sid varchar(40) primary key,
name varchar(40),
 tid varchar(40),
 CONSTRAINT fk_teacher_student_tid FOREIGN KEY(tid) REFERENCES
teacher(tid) on delete cascade
);
insert into teacher values('a','t1');
insert into teacher values('b','t2');
insert into teacher values('c','t3');
insert into student values('1','s1','a');
insert into student values('2','s2','a');
insert into student values('3','s3','b');
insert into student values('4','s4','c');
--错误数据，因为主表主键没有d
insert into student values('5','任我行','d');
-- 修改表
ALTER TABLE <数据表名> ADD CONSTRAINT <外键名> FOREIGN KEY(<列名>)
REFERENCES <主表名> (<列名>);

约束回顾

-- 查看表中的约束
SHOW CREATE TABLE <数据表名>;
create table table_name(
列名1 数据类型 (int) primary key auto_increment,
列名2 数据类型  not null,
列名3 数据类型  unique,
列名4 数据类型 default '值',
constraint 索引名 foreign key(外键列) references 主键表(主键列) on delete
cascade | on delete set null
)
1.主键约束
添加:alter table table_name add primary key (字段)
删除:alter table table_name drop primary key
2.非空约束
添加:alter  table table_name modify 列名 数据类型  not null
删除:alter table table_name modify 列名 数据类型 null
3.唯一约束
添加:alter table table_name add unique 约束名（字段）
删除:alter table table_name drop key 约束名
4.自动增长
添加:alter table table_name modify 列名 int auto_increment
删除:alter table table_name modify 列名 int 
5.外键约束
添加:alter table table_name add constraint 约束名 foreign key(外键列)
references 主键表（主键列）

删除:
第一步:删除外键
alter table table_name drop foreign key 约束名
6.默认值
添加:alter table table_name alter 列名  set default '值'
删除:alter table table_name alter 列名  drop default

索引

问题：假如我们有一张表有1000万条记录，现在查询ename = ‘zs’的员工信息

原始的数据遍历

让我们查询的数据有序：可以使用折半查找法

概述

在MySQL中，索引（index）也叫做“键（key）”，它是存储引擎用于快速找到记录的一种数据结构。

索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对性能的影响就愈发重要。

索引优化应该是对查询性能优化最有效的手段。

-- CREATE INDEX indexName ON mytable(username);
Create Index index_dept_dname on dept(dname);
Drop Index index_dept_dname on dept;

索引的分类

常规索引常规索引，也叫普通索引（index或key），它可以常规地提高查询效率。一张数据表中可以有多个常规索引。常规索引是使用最普遍的索引类型，如果没有明确指明索引的类型，我们所说的索引都是指常规索引。
主键索引主键索引（Primary Key），也简称主键。它可以提高查询效率，并提供唯一性约束。一张表中只能有一个主键。被标志为自动增长的字段一定是主键，但主键不一定是自动增长。一般把主键定义在无意义的字段上（如：编号），主键的数据类型最好是数值。
唯一索引唯一索引（Unique Key），可以提高查询效率，并提供唯一性约束。一张表中可以有多个唯一索引。
外键索引外键索引（Foreign Key），简称外键，它可以提高查询效率，外键会自动和对应的其他表的主键关联。外键的主要作用是保证记录的一致性和完整性。

索引是数据库中一块独立的空间，专门存储索引值的一棵B-树

我们可以通过B树快速的定位到要查找的数据
尽量不要对重复列添加索引
优点
- 索引可以让MySQL快速地查找到我们所需要的数据，但这并不是索引的唯一作用。
- 索引大大减少了MySQL服务器需要扫描的数据量。
- 索引可以帮助服务器避免排序和临时表。
- 索引可以将随机I/O变为顺序I/O。
缺点
- 影响数据库的增删改速度

视图

MySQL 视图（View）是一种虚拟存在的表，同真实表一样，视图也由列和行构成，但视图并不实际存在于数据库中。

行和列的数据来自于定义视图的查询中所使用的表，并且还是在使用视图时动态生成的。

优点

定制用户数据，聚焦特定的数据
简化数据操作
提高数据的安全性
共享所需数据
更改数据格式
重用 SQL 语句

-- CREATE VIEW <视图名> AS <SELECT语句>
-- <视图名>：指定视图的名称。该名称在数据库中必须是唯一的，不能与其他表或视图同名。
-- <SELECT语句>：指定创建视图的 SELECT 语句，可用于查询多个基础表或源视图。
CREATE VIEW v_swordsman AS
( SELECT tid, NAME FROM teacher ) UNION ALL
( SELECT sid, NAME FROM student );
SELECT
*
FROM
v_swordsman
-- DESCRIBE 视图名；
DESCRIBE v_swordsman
-- SHOW CREATE VIEW 视图名;
SHOW CREATE VIEW v_swordsman
-- ALTER VIEW <视图名> AS <SELECT语句>
-- <视图名>：指定视图的名称。该名称在数据库中必须是唯一的，不能与其他表或视图同名。
-- <SELECT 语句>：指定创建视图的 SELECT 语句，可用于查询多个基础表或源视图。
--DROP VIEW IF EXISTS <视图名1> [ , <视图名2> …]

三范式

我们创建表需要遵循的规范。

第一范式

数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组

第二范式

数据库中每一行数据必须依赖于主键，每一个行数据都要有主键

主键是一行数据的唯一性标识

第三范式

表中非主键的列要完全依赖于主键，不能出现部分属性依赖于其他属性

当出现传递依赖的时候要将非依赖于主键的列专门创建一张表进行管理

表与表的关系

维护两张表的关系，根据两张表的对应关系可以分为：

1:1
- 两张表中的数据 1条只和1条对应
- 关联方案，让关联数据主键值相同，在一张表中存放另外一张表的主键

1:N
- A表中的一条数据有可能对应B表中的多条记录
- 在N方的表中设计一个1方的主键列，也称之为外键关联
N:N
- A表中的一条数据有可能对应B表中的多条记录
- 同时B表中的一条数据有可能对应A表中的多条记录
- 关联方案一般都会创建第三章表专门管理学生与课程的关系

李白走高跷

关注

22
点赞
踩
18

收藏

觉得还不错? 一键收藏
0
评论
Mysql详细介绍

数据库管理系统执行过程中的一个逻辑单位，由一个有限的数据库操作序列构成事务指的是数据库一种保护数据的方式事务一般由增删改操作自动调用，事务根据数据库不同提交的时机也是不同的mysql数据库默认执行增删改就会提交事务我们可以设置为手动提交 begin 或者 start transaction;在MySQL中，索引（index）也叫做“键（key）”，它是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对性能的影响就愈发重要。
复制链接

扫一扫