五十五、MySQL全套核心大总结

托马斯_酷涛
62 阅读25分钟

背景： 临近期末，复习MySQL，重新巩固一遍，顺便整理这篇文章，这篇文章从开学一直整理到现在，文章不够专业，基于自己的理解写的，还有改进完善，瑕疵，错误之处还请指出，希望对大家有所帮助，欢迎点赞 收藏 留言。

作者：托马斯-酷涛

座右铭： 种一棵树最好的时间是十年前，其次是现在，你的未来还有无限可能。

目录

数据库概念

安装MySQL数据库

卸载MySQL数据库

关于MySQL服务

MySQL基础操作

MySQL常识

数据库DQL操作

简单查询

条件查询

分组查询

连接查询

数据库DML操作

insert插入

update修改

delete删除

数据库TCL操作

事务

数据库DDL操作

数据库操作

创建数据库

删除数据库

数据表操作

数据库DCL操作

用户

权限

数据库三范式

数据库概念

什么是数据库？什么是数据库管理系统？什么是SQL？他们之间的关系是什么？

        数据库：英文单词DataBase，简称DB。按照一定格式存储数据的一些文件的组合。 顾名思义：存储数据的仓库，实际上就是一堆文件。这些文件中存储了具有特定格式的数据。

        数据库管理系统：DataBaseManagement，简称DBMS。数据库管理系统是专门用来管理数据库中数据的，数据库管理系统可以对数据库当中的数据进行增删改查。

        常见的数据库管理系统：MySQL、Oracle、MS SqlServer、DB2、sybase等....

        SQL：结构化查询语言，学习者需要学习SQL语句，通过编写SQL语句，然后DBMS负责执行SQL 语句，最终来完成数据库中数据的增删改查操作。

        三者之间的关系：DBMS--执行--> SQL --操作--> DB        先安装数据库管理系统MySQL，然后学习SQL语句怎么写，编写SQL语句之后，DBMS对SQL语句进行执行，最终来完成数据库的数据管理。

安装MySQL数据库

        安装一般选择经典版进行安装，需要进行一些MySQL数据库实例配置，一般采用默认配置就可以，默认下一步安装完成

      注意事项：

  1. 端口号port是任何一个软件/应用都会有的，端口号是应用的唯一代表。在同一台计算机上，端口号不能重复，具有唯一性。
  2. 设置mysql数据库的字符编码方式为 UTF8
  3. 服务名称默认为MySQL
  4. 环境变量path=其它路径;C:\Program Files (x86)\MySQL\MySQL Server 5.5\bin
  5. mysql超级管理员用户名不能改，一定是：root

卸载MySQL数据库

        1、双击安装包进行卸载，用程序自带的卸载包卸载，残留文件会少很多

        2、删除目录：
把C:\ProgramData下面的MySQL目录干掉。
把C:\Program Files (x86)下面的MySQL目录干掉。

        3、卸载结束

关于MySQL服务

计算机-->右键-->管理-->服务和应用程序-->服务-->找mysql服务

  • MySQL的服务，默认是“启动”的状态，只有启动了mysql才能用。默认情况下是“自动”启动，自动启动表示下一次重启操作系统的时候自动启动该服务。
  • 服务上点击右键：启动-->重启服务-->停止服务
  • 改变服务的默认配置：服务上点击右键，属性，然后可以选择启动方式

在windows操作系统当中，如何使用命令来启动和关闭mysql服务

        net start 服务名称;
net stop 服务名称;

其它服务的启停都可以采用以上的命令。

MySQL基础操作

使用客户端登录mysql数据库？

        使用bin目录下的mysql.exe命令来连接mysql数据库服务器

退出mysql客户端？

命令行输入exit退出客户端

查看mysql中有哪些数据库？

show databases;

注意：以分号结尾，分号是英文的分号。

如何使用某个数据库？

use test;

如何查看某个数据库下有哪些表？

show tables;

如何查看mysql数据库的版本号？

select version();

查看当前使用的是哪个数据库？

select database();

        注：在MySQL中，\c用来终止一条命令的输入，不见分号（英文）不执行，分号（英文）表示语句的结束！

MySQL常识

数据库当中最基本的单元是表：table

什么是表table？为什么用表来存储数据呢？

姓名    性别    年龄(列：字段) 
---------------------------------------------------------
张三    男        20                    ------->行（记录）
李四    女        21                    ------->行（记录）
王五    男        22                    ------->行（记录）

数据库当中是以表格的形式表示数据的，表具有简洁直观的效果。

任何一张表都有行和列：

  • 行（row）：被称为数据/记录。
  • 列（column）：被称为字段。

每一个字段都有：字段名、数据类型、约束等属性。

数据类型： 字符串，数字，日期等。

约束：

  • 非空约束：not null
  • 唯一性约束: unique
  • 主键约束: primary key （简称PK）
  • 外键约束：foreign key（简称FK）
  • 检查约束：check（mysql不支持，oracle支持）

主键：

        主键约束： 一种约束。
主键字段： 该字段上添加了主键约束，这样的字段叫做：主键字段。
主键值： 主键字段中的每一个值都叫做：主键值。

主键的作用： 主键值是每一行记录的唯一标识。任何一张表都应该有主键，没有主键，表可以称为无效！！

主键的特征： not null + unique（主键值不能是NULL，同时也不能重复！）

外键：

外键约束： 一种约束（foreign key）。
外键字段： 该字段上添加了外键约束。
外键值： 外键字段当中的每一个值。

        外键的作用： 防止数据冗余，空间浪费，使其字段更具科学性，唯一性

        外键的特征： 子表中的外键引用的父表中的某个字段，被引用的这个字段不一定是主键，但至少具有unique约束，外键值可以为NULL。

        注：非空约束not null约束的字段不能为NULL，not null只有列级约束，没有表级约束！

唯一性约束unique约束的字段不能重复，但是可以为NULL。

MySQL语句分类:

  • DQL： 数据查询语言（带有select关键字的都是查询语句） select...
  • DML: 数据操作语言（对表当中的数据进行增删改的都是DML）insert-->增   delete-->删  update-->改  主要进行的是对表中数据的操作
  • TCL:  事务控制语言 事务提交：commit ；  事务回滚：rollback；
  • DDL:  数据定义语言（带有create、drop、alter的都是DDL）create-->创建   drop-->删除   alter-->修改  主要进行的是对表结构的操作
  • DCL:  数据控制语言  授权grant、撤销权限revoke....

数据库DQL操作

          注：查询不会对数据库中的数据进行修改，只是一种显示数据的方式而已。

简单查询

查询数据表

SELECT 列名 FROM 表名 [WHERE 条件表达式]

#使用*表示所有列，字段
SELECT * FROM  表名;
#查询所有的学生信息：
select * from student;
#查询指定字段的数据,多个字段之间以逗号分隔
SELECT  字段名 1,  字段名 2,  字段名 3, ... FROM  表名;
#查询 student 表中的 name 和 age 字段
select name,age from student;

使用别名

SELECT  字段名1 AS  别名,  字段名2 AS  别名 FROM  表名;

对字段和表使用别名

SELECT  字段名1 AS  别名,  字段名2 AS  别名 FROM  表名 AS  表别名;

#使用别名
select name as 姓名,age as 年龄 from student;
#表使用别名
select st.name as 姓名,age as 年龄 from student as st

        注：表使用别名主要是用于多表联合查询，方便快捷，as关键字可以省略

数据去重

SELECT DISTINCT  字段名 FROM  表名;

#查询学生来自于哪些地方
select address from student;
#去掉重复的记录
select distinct address from student;

条件查询

条件查询：不需要将表中所有数据都查出来，只查询出来符合条件的，对记录进行过滤。

SELECT  字段名 FROM  表名 WHERE  条件;

数据表准备

CREATE DATABASE bjpowernode;

use bjpowernode;

CREATE TABLE DEPT(
    DEPTNO int(2) not null ,
	DNAME VARCHAR(14) ,
	LOC VARCHAR(13),
	primary key (DEPTNO));

CREATE TABLE EMP(
    EMPNO int(4)  not null ,
	ENAME VARCHAR(10),
	JOB VARCHAR(9),
	MGR INT(4),
	HIREDATE DATE  DEFAULT NULL,
	SAL DOUBLE(7,2),
	COMM DOUBLE(7,2),
	primary key (EMPNO),
	DEPTNO INT(2));

CREATE TABLE SALGRADE(
    GRADE INT,
	LOSAL INT,
	HISAL INT );

INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
10, 'ACCOUNTING', 'NEW YORK'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
20, 'RESEARCH', 'DALLAS'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
30, 'SALES', 'CHICAGO'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
40, 'OPERATIONS', 'BOSTON'); 
commit;
 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7369, 'SMITH', 'CLERK', 7902,  '1980-12-17'
, 800, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7499, 'ALLEN', 'SALESMAN', 7698,  '1981-02-20'
, 1600, 300, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7521, 'WARD', 'SALESMAN', 7698,  '1981-02-22'
, 1250, 500, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7566, 'JONES', 'MANAGER', 7839,  '1981-04-02'
, 2975, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7654, 'MARTIN', 'SALESMAN', 7698,  '1981-09-28'
, 1250, 1400, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7698, 'BLAKE', 'MANAGER', 7839,  '1981-05-01'
, 2850, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7782, 'CLARK', 'MANAGER', 7839,  '1981-06-09'
, 2450, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7788, 'SCOTT', 'ANALYST', 7566,  '1987-04-19'
, 3000, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7839, 'KING', 'PRESIDENT', NULL,  '1981-11-17'
, 5000, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7844, 'TURNER', 'SALESMAN', 7698,  '1981-09-08'
, 1500, 0, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7876, 'ADAMS', 'CLERK', 7788,  '1987-05-23'
, 1100, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7900, 'JAMES', 'CLERK', 7698,  '1981-12-03'
, 950, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7902, 'FORD', 'ANALYST', 7566,  '1981-12-03'
, 3000, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7934, 'MILLER', 'CLERK', 7782,  '1982-01-23'
, 1300, NULL, 10); 
commit;
 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
1, 700, 1200); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
2, 1201, 1400); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
3, 1401, 2000); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
4, 2001, 3000); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
5, 3001, 9999); 
commit;

         注：查看数据表内容

         注：查看数据表结构

1、计算员工年薪？

select ename,sal*12 from emp;

 2、查询薪资等于800的员工姓名和编号？

select empno,ename from emp where sal = 800;

 3、查询SMITH的编号和薪资？

select empno,sal from emp where ename = 'SMITH';

 4、查询薪资不等于800的员工姓名和编号？

select empno,ename from emp where sal <> 800;

 5、查询薪资小于2000的员工姓名和编号？

select empno,ename,sal from emp where sal < 2000;

6、 查询薪资在2450和3000之间的员工信息？

select empno,ename,sal from emp where sal between 2450 and 3000;

         注：between … and …. 两个值之间, 等同于 >= and <=   并且between and是闭区间，包括两端的值，必须遵循左小右大。

7、查询哪些员工的津贴/补助为null？

select empno,ename,sal,comm from emp where comm is null;

        注： is null 为 空（is not null 不为空），在数据库中null不能使用等号进行衡量，需要使用 is null 因为数据库中的null代表什么也没有，它不是一个值，所以不能使用等号衡量。

8、查询哪些员工的津贴/补助不为null？

select empno,ename,sal,comm from emp where comm is not null;

 9、查询工作岗位是MANAGER并且工资大于2500的员工信息？

select empno,ename,job,sal from emp where job = 'MANAGER' and sal > 2500;

 10、查询工作岗位是MANAGER和SALESMAN的员工？

select empno,ename,job from emp where job = 'MANAGER' or job = 'SALESMAN';

 11、查询工作岗位是MANAGER和SALESMAN的员工？

select empno,ename,job from emp where job in('MANAGER', 'SALESMAN');

         注：in不是一个区间, in后面跟的是具体的值,相当于选择。

like关键字

注： 称为模糊查询，支持%或下划线匹配    %匹配任意多个字符    下划线：任意一个字符。（%是一个特殊的符号，_ 也是一个特殊符号）

1、找出名字中含有O的？

select ename from emp where ename like '%O%';

 2、找出名字以T结尾的？

select ename from emp where ename like '%T';

 3、找出第二个字每是A的？

select ename from emp where ename like '_A%';

排序

1、查询所有员工薪资，排序？（默认为升序）

select ename,sal from emp order by sal;

2、 查询所有员工薪资，排序？（降序）

select  ename,sal from emp order by sal desc;

 3、查询员工名字和薪资，要求按照薪资升序，如果薪资一样，再按照名字升序排列？

select  ename,sal from emp order by  sal asc, ename asc;

4、 查询工资在1250到3000之间的员工信息，要求按照薪资降序排列？

select  ename,sal from  emp where sal between 1250 and 3000 order by  sal desc;

         注：执行顺序 from--> where--> select--> order by(排序最后执行)

分组函数

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

  • count    计数
  • sum    求和
  • avg    平均值
  • max    最大值
  • min    最小值

        注：分组函数在使用的时候必须先进行分组，然后才能使用，如果没有对数据进行分组，整张表默认为一组。

1、找出最高工资？

select max(sal) from emp;

 2、找出最低工资？

select min(sal) from emp;

 3、计算工资和？

select sum(sal) from emp;

 4、计算平均工资？

select avg(sal) from emp;

5、 计算员工数量？

select count(ename) from emp;

  • 分组函数自动忽略NULL，你不需要提前对NULL进行处理。
  • count(具体字段)：表示统计该字段下所有不为NULL的元素的总数。
  • count(*)：统计表当中的总行数。
  • 分组函数不能够直接使用在where子句中。
  • 所有的分组函数可以组合起来一起用。

分组查询

        注：先进行分组，然后对每一组的数据进行操作。

select	... from ... group by	...

1、查询每个工作岗位的工资和？

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

 2、查询每个部门的最高薪资？

实现思路：按照部门编号分组，求每一组的最大值。

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

3、查询“每个部门，不同工作岗位”的最高薪资？

实现思路：两个字段联合成一个字段来看（两个字段联合分组）

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

having关键字

        注：使用having可以对分完组之后的数据进一步过滤，having不能单独使用，having不能代替where，having必须和group by联合使用。

1、查询每个部门最高薪资，要求显示最高薪资大于3000的？

第一步：找出每个部门最高薪资按照部门编号分组，求每一组最大值。

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

第二步：显示最高薪资大于3000的部门编号。

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

2、查询每个部门平均薪资，要求显示平均薪资高于2500的部门？

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

注：此条案例单靠where是完成不了的，所以这时候引入了having关键字来进行进一步筛选。 

思路优化：where和having，优先选择where，where完成不了需求，再选择 having。 

连接查询

连接查询：从一张表中单独查询，称为单表查询。emp表和dept表联合起来查询数据，从emp表中取员工名字，从dept表中取部门名字， 这种跨表查询，多张表联合起来查询数据，被称为连接查询。

  • 内连接： 等值连接，非等值连接，自连接
  • 外连接： 左连接，右连接
  • 全连接: 了解

引入

当两张表进行连接查询时，没有任何条件的限制会发生什么现象？

1、查询每个员工所在部门名称？

select ename,deptno from emp;

 select * from dept;

两张表连接没有任何条件限制：

select ename,dname from emp, dept; 

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

如何避免笛卡尔积现象？

连接时添加条件，满足这个条件的记录被筛选出来！

select  emp.ename,dept.dname  from emp, dept where emp.deptno = dept.deptno;

         注：最终查询的结果条数是14条，但是匹配的过程中，匹配的次数并没有减少还是56次，只不过进行了四选一，次数没有减少。

        总结：通过笛卡尔积现象得出，表的连接次数越多效率越低，所以尽量避免表的连接次数过多。

内连接和外连接

1、查询每个员工所在部门名称，显示员工名和部门名？（等值连接）

select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno;

 2、查询每个员工的薪资等级，要求显示员工名、薪资、薪资等级？（非等值连接）

select  e.ename, e.sal, s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;

        注：on后面 e.deptno = d.deptno 条件是等量关系，所以被称为等值连接。on后面 e.sal between s.losal and s.hisal 条件不是一个等量关系，称为非等值连接。 

3、查询员工的上级领导，要求显示员工名和对应的领导名？（内连接之自连接）

select a.ename as '员工名', b.ename as '领导名' from emp a join  emp b on  a.mgr = b.empno;

4、 （内连接）

select   e.ename,d.dname from  emp e join  dept d on e.deptno = d.deptno;

5、（右外连接）

 select   e.ename,d.dname from  emp e right join  dept d on e.deptno = d.deptno;

6、（左外连接）

  select   e.ename,d.dname from  emp e left join  dept d on e.deptno = d.deptno;

         注：带有right的是右外连接，又叫做右连接。带有left的是左外连接，又叫做左连接。right代表什么：表示将join关键字右边的这张表看成主表，主要是为了将这张表的数据全部查询出来，捎带着关联查询左边的表，left同理。在外连接当中，两张表连接，产生了主次关系。所以：外连接的查询结果条数一定是 >= 内连接的查询结果条数！！！

子查询

子查询：select语句中嵌套select语句，被嵌套的select语句称为子查询。

子查询可以在哪里出现：

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

1、找出比最低工资高的员工姓名和工资？

第一步：查询最低工资是多少

select min(sal) from emp;

第二步：找出>800的

select ename,sal from emp where sal > 800;

第三步：合并

select ename,sal from emp where sal > (select min(sal) from emp);

from子句中的子查询： from后面的子查询，可以将子查询的查询结果当做一张临时表。

2、找出每个岗位的平均工资的薪资等级？（格式化后看的会明显一些，写一行是为了节约空间（哭哭哭......））

select 
			t.*, s.grade
		from
			(select job,avg(sal) as avgsal from emp group by job) t
		join
			salgrade s
		on
			t.avgsal between s.losal and s.hisal;

        注：这样去理解 ：t表和s表进行表连接，条件为 t表avg(sal) between s.losal and s.hisal; 

limit关键字

limit作用： 将查询结果集的一部分取出来。通常使用在分页查询当中。

eg:  百度一页的记录条数默认为10，分页的作用是为了提高用户的体验，因为一次全部都查出来，用户体验差，这样可以一页一页翻页看。

  • 完整用法： limit startIndex, length        startIndex是起始下标，length是长度。起始下标从0开始。
  • 缺省用法： limit 5; 这是取前5。

1、取出工资排名在[3-5]名的员工？

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

2、按照薪资降序，取出排名在前5名的员工？

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

数据库DML操作

DML操作语句主要进行的是对数据的增删改操作。

insert插入

插入记录

INSERT [INTO] 表名 [字段名] VALUES (字段值)

样例

#将表中的所有字段写出
INSERT INTO 表名 (字段名 1, 字段名 2, 字段名 3…) VALUES (值 1, 值 2, 值 3);
#只写插入数据
INSERT INTO 表名 VALUES (值 1, 值 2, 值 3…);
#插入部分数据
INSERT INTO 表名 (字段名 1, 字段名 2, ...) VALUES (值 1, 值 2, ...);

        注：字段名和值要一一对应，数量要对应。数据类型也要对应。insert语句一旦执行成功，那么必然会多一条记录。如果没有给其它字段指定值的话，默认值是NULL。

update修改

修改数据

update 表名 set 字段名1=值1,字段名2=值2,字段名3=值3... where 条件;

        注：如果没有条件限制会导致所有数据全部更新。

样例

#不带条件修改数据（修改所有）
UPDATE 表名 SET 字段名=值;
#带条件修改数据
UPDATE 表名 SET 字段名=值 WHERE 字段名=值;

delete删除

删除数据

delete from 表名 where 条件;

        注：没有条件，整张表的数据会全部删除！

样例

#不带条件删除数据
DELETE FROM 表名;
#带条件删除数据
DELETE FROM  表名 WHERE  字段名= 值;
#使用 truncate 删除表中所有记录
TRUNCATE TABLE  表名;

数据库TCL操作

事务

事务（transaction）：一个事务其实就是一个完整的业务逻辑，是一个最小的工作单元，不可再分。

eg: 假设转账，从A账户向B账户中转账10000.

第一步：将A账户的钱减去10000（update语句）

第二步：将B账户的钱加上10000（update语句）

这就是一个完整的业务逻辑，以上的操作是一个最小的工作单元，要么同时成功，要么同时失败，不可再分。这两个update语句要求必须同时成功或者同时失败，这样才能保证钱是正确的。

       注：事务是基于DML语句的，只有以上的DML语句是数据库表中数据进行增、删、改的。因为在实际生活中很多事是不能依靠一条DML语句就能完成的，需要多条DML语句共同联合起来才能完成，这便是事务存在的意义。

        简单理解：由多条DML语句组成，构成的一个完整事务逻辑。

  • 提交事务：清空事务性活动的日志文件，将数据全部彻底持久化到数据库表中，提交事务标志着，事务的结束，并且是一种全部成功的结束
  • 回滚事务：将之前所有的DML操作全部撤销，并且清空事务性活动的日志文件，回滚事务标志着，事务的结束。并且是一种全部失败的结束。

1、回滚事务

2、提交事务

3、事务的特征

  1. 原子性 ：说明事务是最小的工作单元，不可再分。
  2. 一致性：所有事务要求，在同一个事务当中，所有操作必须同时成功，或者同时失败，以保证数据的一致性。
  3. 隔离性：A事务和B事务之间具有一定的隔离，不会对同一张表同时操作。
  4. 持久性：事务最终结束的一个保障。事务提交，就相当于将没有保存到硬盘上的数据保存到硬盘上！

4、事务和事务之间的隔离级别

  1. 脏读：所谓的脏读就是指一个事务读取了另外一个事务未提交的数据。
  2. 不可重复读：所谓的不可重复读是指事务中两次查询的结果不一致，原因是在查询的过程中其他事务做了更新的操作。
  3. 幻读：又被称为虚读，是指在一个事务内两次查询中数据条数不一致，幻读和不可重复读有些类似，同样是在两次查询过程中，不同的是，幻读是由于其他事务做了插入记录的操作，导致记录数增加。
  4. 可串行化：事务的最高隔离级别，效率最低，解决了所有的问题，这种隔离级别表示事务排队，不能并发！ 每一次读取到的数据都是最真实的，并且效率是最低的。

数据库DDL操作

数据库操作

创建数据库

CREATE DATABASE 数据库名;

判断数据库是否已经存在，不存在则创建数据库

CREATE DATABASE IF NOT EXISTS 数据库名;

创建数据库并指定字符集

CREATE DATABASE 数据库名 CHARACTER SET 字符集;

样例

#创建数据库 test1
create database test1;
#判断是否存在，如果不存在则创建数据库 test2
create database if not exists test2;
#创建数据库并指定字符集为 gbk
create database db3 character set gbk;

删除数据库

DROP DATABASE 数据库名;

修改数据库

#修改数据库默认的字符集
ALTER DATABASE  数据库名 DEFAULT CHARACTER SET；
#将数据库test2的字符集改成 utf8
alter database test2 character set utf8;

数据表操作

创建数据表

CREATE TABLE 表名 (
字段名 1 字段类型 1,
字段名 2 字段类型 2
);

样例

#创建 student 表包含 id,name,birthday 字段
create table student (
id int,
name varchar(20),
birthday date
);

删除数据表

DROP TABLE 表名;

样例

#直接删除表 s1 表
drop table s1;
#判断表是否存在并删除 s1 表
drop table if exists `create`;

修改数据表

#添加数据表表列 ADD
ALTER TABLE  表名 ADD  列名  类型;
#修改列类型 MODIFY
ALTER TABLE 表名 MODIFY 列名 新的类型;
#修改列名 CHANGE
ALTER TABLE 表名 CHANGE 旧列名 新列名 类型;
#删除列 DROP
ALTER TABLE 表名 DROP 列名;
#修改表名
RENAME TABLE 表名 TO 新表名;
#修改字符集 character set
ALTER TABLE 表名 character set 字符集;

样例

#为学生表添加一个新的字段 remark,类型为 varchar(20)
alter table student add remark varchar(20);
#将 student 表中的 remark 字段的改成 varchar(100)
alter table student modify remark varchar(100);
#将 student 表中的 remark 字段名改成 intro，类型 varchar(30)
alter table student change remark intro varchar(30);
#删除 student 表中的字段 intro
alter table student change remark intro varchar(30);
#将学生表 student 改名成 student2
rename table student to student2;
#将 student2 表的编码修改成 gbk
alter table student2 character set gbk;

数据库DCL操作

用户

DBA：数据库管理员

DCL：管理用户，授权

管理用户

#添加用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
#删除用户
DROP USER '用户名'@'主机名';
#修改用户密码
UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名';
UPDATE USER SET PASSWORD = PASSWORD('abc') WHERE USER = 'lisi';
SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码');
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123');

权限

权限管理

#查询权限
SHOW GRANTS FOR '用户名'@'主机名';
SHOW GRANTS FOR 'lisi'@'%';
#授予权限
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
#授予tuomasi全部权限
GRANT ALL ON *.* TO 'tuomasi'@'localhost';
#撤销权限
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
REVOKE UPDATE ON test.`test1` FROM 'lisi'@'%';

数据库三范式

数据库设计三范式

  • 第一范式：要求任何一张表必须有主键，每一个字段原子性不可再分。
  • 第二范式：建立在第一范式的基础之上，要求所有非主键字段完全依赖主键，不要产生部分依赖。
  • 第三范式：建立在第二范式的基础之上，要求所有非主键字段直接依赖主键，不要产生传递依赖。

        注：设计数据库表的时候，按照以上的范式进行，可以避免表中数据的冗余，空间的浪费。

理解 + 思考 = 掌握

avatar
文章
阅读
粉丝