mysql数据库理论和实践教学

---

第一章 数据库概述

1.1 SQL概述

SQL是结构化查询语言，是一门标准通用的语言，标准的sql适合于所有的数据库产品
SQL是高级语言，只要能看懂英文，写出来的sql语句，能读懂什么意思就行。
sql语句在执行的时候，实际上内部也会先进行编译，然后再执行sql（SQL语句的编译有DBMS来完成）

SQL，一般发音为sequel，SQL的全称Structured Query Language)，SQL用来和数据库打交道，完成和数据库的通信，SQL是一套标准。但是每一个数据库都有自己的特性，别的数据库没有,当使用这个数据库特性相关的功能,这时SQL语句可能就不是标准了.(90%以上的SQL都是通用的)

1.2 什么是数据库

数据库，通常是一个或一组文件，保存了一些符合特定规格的数据,数据库对应的英语单词是DataBase,简称:DB,数据库软件称为数据库管理系统（DBMS），全称为DataBase Management System，如：Oracle、SQL Server、MySql、Sybase、informix、DB2、interbase、PostgreSql 。

1.3 MYSQL概述

MySQL最初是由“MySQL AB”公司开发的一套关系型数据库管理系统（RDBMS-Relational Database Mangerment System）。
MySQL不仅是最流行的开源数据库，而且是业界成长最快的数据库，每天有超过7万次的下载量，其应用范围从大型企业到专有的嵌入应用系统。
MySQL AB是由两个瑞典人和一个芬兰人：David Axmark、Allan Larsson和Michael “Monty” Widenius在瑞典创办的。
在2008年初，Sun Microsystems收购了MySQL AB公司。在2009年，Oracle收购了Sun公司，使MySQL并入Oracle的数据库产品线。

1.4 DB、SQL和DBMS三者之间的关系

DMBS负责执行SQL语句，通过执行sql语句来操作DB当中的数据。

1.5 MYSQL的安装

大家打开链接，直接下载就行了，下面是安装教程哦。
链接：https://pan.baidu.com/s/1bVriQ7zIxTGynTFXa9ssbw
提取码：1234

按“Next”继续

选择安装类型，有“Typical（默认）”、“Complete（完全）”、“Custom（用户自定义）”三个选项，我们选择“Custom”，有更多的选项，也方便熟悉安装过程

上一步选择了 Custom 安装，这里将设定 MySQL 的组件包和安装路径，设定好之后，单击 Next 继续安装。

现在软件安装完成了，出现上面的界面，将 “Configure the Mysql Server now”前面的勾打上，点“Finish”结束软件的安装并启动mysql配置向导。
mysql配置向导启动界面，按“Next”继续。

选择配置方式，“Detailed Configuration（手动精确配置）”、“Standard Configuration（标准配置）”，我们选择“Detailed Configuration”，方便熟悉配置过程。

选择服务器类型，“Developer Machine（开发测试类，mysql占用很少资源）”、“Server Machine（服务器类型，mysql占用较多资源）”、“Dedicated MySQL Server Machine（专门的数据库服务器，mysql占用所有可用资源）”，大家根据自己的类型选择了，一般选“Server Machine”，不会太少，也不会占满。

选择mysql数据库的大致用途，“Multifunctional Database（通用多功能型，能很好的支持InnoDB与MyISAM存储引擎）”、“Transactional Database Only（服务器类型，专注于事务处理，一般）”、“Non-Transactional Database Only（非事务处理型，较简单，主要做一些监控、记数用，对MyISAM数据类型的支持仅限于non-transactional），随自己的用途而选择了，我这里选择“Multifunctional Database”， 按“Next”继续。

对InnoDB Tablespace进行配置，就是为InnoDB 数据库文件选择一个存储空间，如果修改了，要记住位置，重装的时候要选择一样的地方，否则可能会造成数据库损坏，当然，对数据库做个备份就没问题了，这里不详述。我这里没有修改，使用用默认位置，直接按“Next”继续。

选择您的网站的一般mysql访问量，同时连接的数目，“Decision Support(DSS)/OLAP（20个左右）”、“Online Transaction Processing(OLTP)（500个左右）”、“Manual Setting（手动设置，自己输一个数）”，我这里选“Decision Support(DSS)/OLAP)”，按“Next”继续

是否启用TCP/IP连接，设定端口，如果不启用，就只能在自己的机器上访问mysql数据库了，我这里启用，把前面的勾打上，Port Number：3306，在这个页面上，您还可以选择“启用标准模式”（Enable Strict Mode），按“Next”继续。’

这个比较重要，就是对mysql默认数据库语言编码进行设置，第一个是西文编码，我们要设置的是utf8编码，按 “Next”继续。

选择是否将mysql安装为windows服务，还可以指定Service Name（服务标识名称），是否将mysql的bin目录加入到Windows PATH（加入后，就可以直接使用bin下的文件，而不用指出目录名，比如连接，“mysql.exe -uusername -ppassword;”就可以了，不用指出mysql.exe的完整地址，很方便），我这里全部打上了勾，Service Name不变。按“Next”继续。

设置完毕，按“Next”继续。

确认设置无误，如果有误，按“Back”返回检查。按“Execute”使设置生效。

设置完毕，按“Finish”结束mysql的安装与配置

可以通过服务管理器管理 MYSQL 的服务。
通过命令调用服务管理器:services.msc
停止 MYSQL 的服务。

启动 MYSQL 的服务。

也可以在 DOS 中直接通过命令行的形式进行控制。
停止 MYSQL 的服务。

启动 MYSQL 的服务。

1.6 什么是表

表(table)是一种结构化的文件，可以用来存储特定类型的数据，如：学生信息，课程信息，都可以放到表中。另外表都有特定的名称，而且不能重复。表中具有几个概念：列、行、主键。 列叫做字段(Column),行叫做表中的记录,每一个字段都有:字段名称/字段数据类型/字段约束/字段长度

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

大家可以打开EXCEL，就可以知道表长啥样了。

一列一列的叫字段，每个字段应该包含哪些属性？
字段名，数据类型，相关约束。

1.7 SQL的分类

数据查询语言(DQL-Data Query Language)
代表关键字:select ，凡是select语句都是DQL
数据操纵语言(DML-Data Manipulation Language)
代表关键字:insert,delete,update ，对表中的数据进行增删改
数据定义语言(DDL-Data Definition Language)
代表关键字:create ,drop,alter,对表结构的增删改
事务控制语言(TCL-Transactional Control Language)
代表关键字:commit提交事务 ,rollback回滚事务;
数据控制语言(DCL-Data Control Language)
代表关键字:grant授权,revoke撤销授权.

1.8 导入初始化数据

下面是准备好的数据，大家可以下载：
链接：https://pan.baidu.com/s/1-7bT3pHL2mhFba_AOWmKVA
提取码：df2p

第一步：登录MYSQL数据库管理系统
可以在开始菜单的搜索框中直接搜索MYSQL就行，然后输入密码，即可登录

第二步：查看有哪些数据库
show databases;(这个不是SQL语句，属于mysql命令，换句话说这个命令只在mysql中使用，在其他数据库中不适用。)
注：大家一定要区分哪些是SQL语句，哪些是MYSQL命令，因为SQL语句是通用，任何数据库都可以用，但MYSQL命令仅限于MYSQL中使用。

之后的教程中，我也会尽量给大家标出来，哪些不是SQL语句，大家注意一下。

MYSQL有一个可视化界面，叫SQLyog，也可以用来书写MYSQL语句，但是本教程不用可视化界面，因为在正式的工作中，一般都是不用可视化界面滴。

注：这是MYSQL自带的数据库。

第三步：创建我们自己的数据库
mysql> create database bjpowernode;(这个不是SQL语句，属于mysql命令）
注：这里的语法是 create database 库名，库名随便取，但尽量做到看到库名知道里面装的是啥数据。

第四步：使用bjpowernode数据库
mysql> use bjpowernode；(这个不是SQL语句，属于mysql命令）

第五步：查看当前使用的数据库有哪些表？
mysql> show tables;(这个不是SQL语句，属于mysql命令）

第六步：导入数据
source 路径;

现在我们创建的bjpowernode中就有表了。

1.9 对SQL脚本的理解

bjpowernode.sql 这个文件以sql结尾，这样的文件被称为‘sql脚本‘，什么是sql脚本？
当一个文件的扩展名是.sql，并且该文件中书写了大量的SQL语句，我们称这样的文件叫做sql脚本

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

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

第二章 常用命令

2.1 查看表结构

查看表结构：desc 表名；

2.2 查看表中元素

select *from 表名;

2.3 创建数据库

1. create database 数据库名称;
   create database bjpowernode;
2. use 数据库名称
   use bjpowernode;

注：在数据库中建立表，因此创建表的时候必须要先选择数据库

2.4 查询当前使用的数据库

select database();
查询数据库版本也可以使用
select version();

2.5 终止一条语句

如果想要终止一条正在编写的语句，可键入\c。

2.6 查看当前库中的表

2.7 查看其他库中的表

show tables from ;

2.8 查看表的创建语句

show create table 表名;

第三章 基础查询语句（DQL）

注：上面结束的常用命令非SQL语句，通用性并不强，其他数据库管理系统可以就不可用。下面才是要学习的重点哦。

大家学习的时候一定要分类学，比如这部分学的是DQL（数据查询语言），所以几乎这里所有的语法都跟select 有关，这样大家记忆起来会更加的简单。大家忘记的，可以看看上面，给大家都已经分类了。接下来的教程，我都会以一个个小例子展开。 大家都可以先自己练习一下，再跟我对答案。

3.1 简单查询

语法格式： select 字段名1，字段名2，字段名3，… from 表名;

提示：任何一条sql语句以分号 ; 结尾。

练习1：查询员工的名字。

练习2：查询员工编号和姓名

提示：SQL语句不区分大小写

练习3：查询emp表中全部的字段
(这里有两种写法)

练习4：查询员工的年薪

这里可以给sal*12取个别名，看起来更清楚。

这里as关键字可以省略，直接是一个空格效果是一样的。

注：这里起别名，对原表没有任何影响。还有就是中文必须要用单引号或者双引号引起来，要不然会报错哦。
但是标准的SQL语句中要求的是用单引号引起来，如果用双引号，就只能用于mysql其他数据库管理系统下是不能用的，所以我们之后统一都用单引号。

从这个练习中我们可以看出，我们的字段是可以参与数学运算的，也就是加减乘除等。

3.2 条件查询

条件查询需要用到where语句，where必须放到from语句表的后面
支持如下运算符：

语法格式：
select 字段名1，字段名2，… from 表名 where 条件语句；

练习1：查询工资为5000的员工姓名

练习2：查询SMITH的工资

注：字符串类型都需要加引号，怎么看是不是字符串类型。desc 表名。

练习3：找出工资大于等于3000的员工

练习4：找出工资不等于3000的

练习5：找出工资在1100和3000之间的员工，包括1100和3000


between and 是闭区间。
between and在使用的时候必须左小右大。

between and 除了能应用到数字中，还可以应用于字符中。

如果应用在字符中，我们可以理解为左闭右开。

练习6：找出哪些人津贴为null

注：在数据库中，NULL不是一个值，它代表的是什么也没有，为空，但不是为空值。空不是一个值，不能用等号来衡量，必须使用is null 和 is not null

练习7：找出哪些人的津贴不为null

练习8：找出哪些人没有津贴

练习9：找出工作岗位是manager和salesman的员工
（用in来写，in相当于or）

3.3 and和or优先级问题

练习1：找出工作岗位是manager和salesman的员工

练习2：找到薪资大于1500的并且部门编号为20或30的员工

这是一种保险的写法，因为有时候可能会忘记优先级的事情，所以就用括号括起来
那么如果不加括号，会怎么样呢？

3.4 模糊查询

Like可以实现模糊查询，like支持%和下划线匹配：
%匹配任意字符出现的个数
下划线只匹配一个字符

练习1：找出名字中含有O的：

练习2：查询姓名以M开头所有的员工

练习3：找出名字中第二个字母是A 的：

练习4：找出名字中有下划线的：
（这里我新建了一张表，之后会跟大家说怎么做，你们先跟着我建立就行了）

提示一下：这里会用到转义

练习5：找出最后一个字母是T的：

第四章 数组排序

（升序和降序）
排序采用order by子句，order by后面跟上排序字段，排序字段可以放多个，多个采用逗号间隔，order by默认采用升序，如果存在where子句那么order by必须放到where语句的后面
desc:降序
asc：升序

练习1：按照工资升序排列：


两者是一样的。

练习2：按照工资降序排，工资一样的按照名字的升序排：

注：多个字段排序，需要注意，越靠前的起的主导作用越大。

练习3：找出岗位为salesman的员工，这些员工按照工资的升序排：

第五章 分组函数/聚合函数/多行处理函数

注意：所有的分组函数都是对某一组数据进行操作的

所有的分组函数的使用语法都是 select 分组函数(字段名) from 表名;

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

分组函数都会自动忽略null

什么是单行处理函数？
输入一行，输出一行

列如，下面这个就是单行处理函数：

求出每个员工的年薪：

注意：不管是什么数据库，只要表达式中含有null那么结果一定为null
，除非进行ifnull处理。下面会给大家介绍ifnull，大家往后看。

5.1 sum函数

练习1： 取得薪水的合计

练习2：取得津贴的合计
注意：null会被忽略掉

练习3： 取得薪水的合计（sal+comm）

从以上结果来看，不正确，原因在于comm字段有null值，所以无法计算，sum会忽略掉，正确的做法是将comm字段转换成0

什么意思呢？
就是如果某一行为null，那么在用sum函数时，这一行就会被忽略掉，因此需要将comm字段转为0；

ifnull函数：
ifnull（参数1，参数2）：参数1，如果null的值，参数2如果为null让其等于多少。

5.2 avg函数

练习1：取得平均薪水

练习2：取得平均薪水（sal+comm）

5.3 max函数

练习1： 取得最高薪水：

练习2：取得最高薪水（sal+comm）

练习3：取得最晚入职得员工

5.4 min函数

练习1：取得最低薪水

练习2：取得最低薪资（sal+comm）：

5.5 count函数

练习1：取得所有的员工数

练习2： 取得津贴不为null员工数

练习3： 取得工作岗位的个数

重点：count(*)和count(某个字段)有什么区别
count（*）不是统计某个字段的个数，而是统计字段条数
count(某个字段)：统计该字段不为null的个数

5.6 组合聚合函数

练习1：找出工资高于平均工资的员工：

这里给大家提个醒，就是分组函数只能跟在select后面，不能跟在其他后面，是错误的。

可以将这些聚合函数都放到select中一起使用

第六章 分组查询

6.1 单个字段分组

分组查询主要涉及到两个子句，分别是：group by和having

group by :按照某个字段或者某些字段进行分组。
having :是对分组过后的数组进行二次过滤

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

它的顺序是，先进行group by 再进行max分组函数。

注意：分组函数，一般都会和group by 联合使用。并且任何一个分组函数都是在group by 函数执行过后再执行。
当一条sql 语句没有group by的时候，整个表会看成为一组。

这里可以解释一下，为什么分组函数不能用在where函数之后，是因为group by 是在where函数执行后执行，而分组函数又是在group by函数执行后执行。

整体的一个语法是这样的：
select
… 6
from
… 1.先从这个表开始查
where
… 2
group by
… 3根据2这个条件分完组
having
… 4分组过后的再次过滤
order by
… 5.最后排序输出

练习1：找出工资高于平均工资的员工
第一步：找出平均工资

第二步：找出高于平均工资的员工

那么是否可以拼接在一起？
答案是可以的

现在给大家看一个语句，大家想想这里面出现了什么问题。

发现没有ename与sal根本就不匹配，这是为什么？
因为ename这个语句根本没有参与分组，而job参与了，因此只加job不会有问题，但加了ename会导致每一个job对应的ename直接显示其中，根本不会和max（sal）进行一一对应。

练习2：取得每个工作岗位的工资合计，要求显示岗位名称和工资合计

6.2 多个字段分组查询

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

6.3 having和where的选择

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

注意：上面这种方式效率低。还可以使用where

先让where过滤掉，再进行分组。这样进行分组的数据变少，效率更高。

建议：能使用where过滤的就使用where

练习2：取得每个岗位的平均工资大于2500

像这种情况。无法使用where只能用having了。
大家记住了，where后面不能用分组函数！！！！！！！！！

6.4 select语句总结

一个完整的select语句格式如下

select 字段
from 表名
where …….
group by ………
having …….(就是为了过滤分组后的数据而存在的—不可以单独的出现)
order by ………

以上语句的执行顺序

1. 首先执行where语句过滤原始数据
2. 执行group by进行分组
3. 执行having对分组数据进行操作
4. 执行select选出数据
5. 执行order by排序

原则：能在where中过滤的数据，尽量在where中过滤，效率较高。having的过滤是专门对分组之后的数据进行过滤的。

6.5 补充知识：去除重复记录

用distinct，在字段前面加distinct


注意：这个语法是错误的，distinct只能出现在所有字段的最前方

这个是联合起来去除重复记录，并不是一个去一个不去。

第七章 连接查询

连接查询：在实际开发中，都不是从单表中查询数据，一般都是多张表联合查询，取得最终结果。
在实际开发中一个业务会对应多张表，比如：学生和班级，起码两张表。
连接查询：也可以叫跨表查询，需要关联多个表进行查询

连接查询的分类：
按照语法来分：SQL92和SQL99
按照表的连接方式来划分，包括：
内连接：
等值连接
非等值连接
自连接
外连接：
左外连接
右外连接

全连接（这个很少用）

7.1 笛卡尔积现象

在表的连接查询方面存在一种现象：笛卡尔积现象。
案例：找出每一个员工的部门名称，要求显示员工名和部门名。

笛卡尔积现象：笛卡尔乘积是指在数学中，两个集合X和Y的笛卡尔积（Cartesian product），又称直积，表示为X × Y，第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员

什么意思呢？在这个语句当中，每一个ename都要和dname进行匹配，其中ename有14条，dname4条，所以一共会出现56条记录

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

一般情况下都会加表的别名：
第一：执行效率高
第二：可读性好

如何避免笛卡尔积现象：
加条件进行过滤

思考：避免笛卡尔积现象，会减少匹配次数。
不会，次数还是56次，只不过显示的有效记录。

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

加上where，就避免了笛卡尔积现象。再提示一遍，匹配次数不变。不过这是92语法，之后会很少用。

7.2 内连接之等值连接

最大的特点：是条件是等量关系。

案例：找出每一个员工的部门名称，要求显示员工名和部门名。
SQL99语法：
select …
from …
join …
on …

就是在SQL92语法的基础上，将逗号改成join，将where改成on
on的后面还可以跟where

为什么SQL99更好，因为SQL92将之后 要用的数据过滤where融合在了一起，而SQL99将其分离开。

7.3 内连接之非等值连接

最大特点：是条件是非等量关系。
案例：找出每个员工的工资等级，要求显示员工名、工资、工资等级

7.4 自连接之自连接

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

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

7.5 外连接

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

• 内连接：假设A和B表进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录查询出来。AB两张表没有主副之分。
• 外连接：假设A和B表进行连接，使用外连接的话，AB两张表中有一张表为主表，一张为副表，主要查询主表中的数据，捎带着查询副表，当副表中的数据没有和主表中的数据匹配上，副表自动模拟出null与之匹配。

外连接的分类：

• 左外连接（左连接）：左边这个表是主表
• 右外连接（右连接）：右边这个表是主表
  左连接有右连接的写法
  右连接有左连接的写法

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

这个left指明是左外连接
写成right就是右外连接

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

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

7.6 3张以上表连接

案例：找出每个员工的部门名称和工资等级

• 分析：
• 第一步：找出每个员工的部门名称：
  
• 第二步：找出每个员工的工资等级
• 
• • 第三步：把查询的结果又看做是两张表
• 
  还有一种写法：
  …
  A
  join
  B
  on
  …
  join
  C
  on
  …
  表示：A表先与B表进行表连接，连接之后A表再与C表进行表连接

练习：找出每个员工的部门名称，上级领导和工资等级（这个必须使用外连接）

第八章 子查询

什么是子查询？子查询可以出现在哪里？
子查询就是嵌套的select语句，可以理解为子查询是一张表

8.1 where语句中使用子查询

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

• 第一步：找出平均薪资
  
• 第二步：找出高于平均薪资的员工
  
  练习：查询员工信息，查询哪些人是管理者，要求显示出其员工编号和员工姓名
• 第一步：首先取得管理者编号，去除重复的
  
• 第二步：查询员工编号包含管理者编号的
  

8.2 from语句中使用子查询

案例：查询员工信息，查询哪些人是管理者，要求显示出其员工编号和员工姓名

• 第一步：首先取得管理者编号去除重复的
  

• 第二步：将以上查询作为一张表，放到from语句的后面
  
  练习： 查询各个部门的平均薪水所属等级，需要显示部门编号，平均薪水，等级编号

• 第一步：查询各个部门的平均薪水
  

• 第二步：查询各个部门的平均薪水所属等级

8.3 select语句中使用子查询

案例：查询员工信息，并显示出员工所属的部门名称
第一种做法，将员工表和部门表连接

第二种做法，在select语句中再次嵌套select语句完成部分名称的查询

第九章 union

可以将查询结果集相加

案例：找出工作岗位是salesman和manager的员工？
第一种：

第二种：使用union

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

注意：在使用union时两张表显示的列数必须相同，要不然报错。

第十章 limit 的使用

重点中的重点，以后分页查询，全靠它

limit是MYSQL中特有的，其他数据库没有，不通用。（oracle中有一个相同的机制，叫做rownum）

limit取结果集中的部分数据，这是它的作用

语法机制：limit startIndext ,length
startIndext表示起始位置
length表示取几个

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


limit是SQL语句最后执行的一个环节

案例：找出工资排名在第四和第五的员工

5.1 通用的标准分页SQL

每页显示3条记录
第1页:0,3
第2页：3,3
第3页：6,3

总结：每页显示pageSize条记录：
pageNo页：（pageNo-1）*pageSize，pageSize

第十一章 表

11.1 创建表

• 语法格式
  create table 表名（
  字段名1 数据类型，
  字段名2 数据类型，
  字段名3 数据类型，
  …
  ）；
  MYSQL常用数据类型：
  

• char和varchar如何选择？
  在实际的开发中，当某个字段中的数据长度不发生改变的时候，是定长例如，性别，生日等都是才用char
  当一个字段的数据长度不确定，例如：简介，姓名等都用varchar

• BLOB和CLOB的使用
  电影表：t_movie
  id（int）-------name（varchar）-----playtime（date）--------haibao（BLOB）-----剧情（CLOB）

---

1------蜘蛛侠
2
3

案例：建立学生信息表，字段包括：学号、姓名、性别、出生日期、email、班级标识

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

11.2 插入数据

语法格式：
insert into 表名(字段名1，字段名2，字段名3,… ) values （值1，值2，值3,…）
注意：dos命令编码不是utf-8因此，不能使用中文

出现中文的地方就出现了乱码

删表：
drop table if exists 表名;

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

insert 另一种写法，
insert into 表名 values()，这就要求，values必须按原先列的顺序全部包含。

11.3 表的复制

create table 新表名 as select语句;
将查询结果当做表创建出来。
这样新表就完全复制了那张表。

将查询结果插入表中

11.4 修改表中的数据

update 表名 set 字段名1=值1，字段名2=值2,… where 条件
没有条件，整张表数据全部更新

案例：将部门编号为10的，地址改为上海

更新所有数据

11.5 删除数据

delete from 表名 where 条件；
注意：没有条件全部删除

怎么删除大表：（重点）
truncate table 表名 //表被截断，不可回滚

11.6 表结构的修改

在实际开发中，大家使用工具完成即可，对表结构的修改很少。

11.6.1 添加字段（alter）

alter table 表名 add 字段名 数据类型；
案例：需求发生改变，需要向t_student中加入联系电话字段，字段名称为：contatct_tel 类型为varchar(40)

11.6.2 修改字段

alter table 表名 modify 字段名 数据类型；
案例：name字段无法满足要求，长度需要改成100

案例：将sex字段名改成gender

11.6.3 删除字段

alter table 表名 drop 字段名；
案例：删除联系电话字段

11.7 约束作用及常用约束

• 常见的约束
  a) 非空约束，not null 约束的字段不能为空
  b) 唯一约束，unique 约束的字段不能重复
  c) 主键约束，primary key 约束的字段不能空也不能重复
  d) 外键约束，foreign key
  e) 自定义检查约束，check（不建议使用）(在mysql中现在还不支持)

11.7.1 非空约束

这里name写的就不是null
有的可能直接报错

11.7.2 唯一性约束

唯一性约束，它可以使某个字段的值不能重复，如：email不能重复，但可以为空

案例：让id变成唯一不可重复


由于唯一性约束，所以报错了。

案例：给两列或多列将唯一性约束

这两者的区别，前者表示这两个字段联合起来不能重复，后者表示两个字段分别都不能重复。前者叫“表级约束”，后者叫“列级约束”

注意：not null 只有列级约束

11.7.3 主键约束

怎么给一张表增加主键约束?


主键约束：要求非空且不能重复。
这叫列级约束。

主键相关的术语：
主键约束
主键字段
主键值

主键有什么作用？
表的设计有三范式要求，第一范式就是要求任何一张表都应该有主键。
主键的作用：
是这一行记录的唯一标识（就像身份证号一样）

主键的分类：

• 根据主键字段的字段数量来划分：
  单一主键和复合主键
  复合主键（多个字段联合起来添加一个主键约束）。复合主键不建议用，因为它违背三范式。
• 根据主键性质来划分
  自然主键和业务主键（主键值和系统的业务挂钩，列如拿着银行卡卡号做主键，拿着身份证号做主键）
  最好不要拿着和业务挂钩的字段作为主键，因为以后业务一旦发生的改变的时候，主键值可能也需要随着发生改变，但有的时候没有办法变化，因为变化可能导致主键值重复。

一张表的主键约束只能有一个。

使用表级约束定义主键：

主键值自增
在字段后面加auto_increment


提示：Oracle当中也提供了一个自增机制，叫做，序列(sequence)对象。

11.7.4外键约束

关于外键约束的相关术语：
外键约束：foreign key
外键字段：
外键值：

外键约束：规定某个字段所可取的值，主要用于两个表之间的链接。

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

外键值可以为null吗？
可以。

第十二章 存储引擎

12.1 存储引擎的使用

MYSQL中特有的。
表的存储方式。
• 数据库中的各表均被（在创建表时）指定的存储引擎来处理。
• 服务器可用的引擎依赖于以下因素：
• MySQL的版本
• 服务器在开发时如何被配置
• 启动选项

用 show create table 表名；
可以查看存储引擎

存储引擎不一样，存储方式就不一样。

因此完整的建表语句应该这样写：
create table 表名(字段1 数据类型,…)engine=InnoDB,default charset=utf-8’;

建表的时候可以指定存储引擎，也可以指定字符集
mysql默认使用的存储引擎是InnoDB，默认采用的字符集是utf-8

12.2 常用的存储引擎

目前支持的存储引擎：

12.2.1 MyISAM存储引擎

• MyISAM存储引擎是MySQL最常用的引擎，但这种引擎不是默认的。
• 它管理的表具有以下特征：
– 使用三个文件表示每个表：
• 格式文件 — 存储表结构的定义（mytable.frm）
• 数据文件 — 存储表行的内容（mytable.MYD）
• 索引文件 — 存储表上索引（mytable.MYI）
– 灵活的AUTO_INCREMENT字段处理
– 可被转换为压缩、只读表来节省空间

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

12.2.2 InnoDB存储引擎

• InnoDB存储引擎是MySQL的缺省引擎。
• 它管理的表具有下列主要特征：
– 每个InnoDB表在数据库目录中以.frm格式文件表示
– InnoDB表空间tablespace被用于存储表的内容
– 提供一组用来记录事务性活动的日志文件
– 用COMMIT(提交)、SAVEPOINT及ROLLBACK(回滚)支持事务处理
– 提供全ACID兼容
– 在MySQL服务器崩溃后提供自动恢复
– 多版本（MVCC）和行级锁定
– 支持外键及引用的完整性，包括级联删除和更新

优点：支持事务，行级锁，外键等，安全
缺点：数据存储在tablespace这样的表空间中（逻辑概念），无法被压缩，无法被转化为只读。

12.2.3 MEMORY存储引擎

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

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

优点：查询速度最快

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

练习题：取得每个部门最高薪水的人员名称
第一步：取得每个部门的最高薪水

第二步：取得每个部门最高薪水的人员名称

第十三章 事务

13.1 概述

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

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

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

事务可以保证多个操作原子性，要么全成功，要么全失败。对于数据库来说事务保证批量的DML要么全成功，要么全失败。事务具有四个特征ACID
a) 原子性（Atomicity）
 整个事务中的所有操作，必须作为一个单元全部完成（或全部取消）。
b) 一致性（Consistency）

• 在事务开始之前与结束之后，数据库都保持一致状态。
  c) 隔离性(Isolation)
• 一个事务不会影响其他事务的运行。
  d) 持久性(Durability)
• 在事务完成以后，该事务对数据库所作的更改将持久地保存在数据库之中，并不会被回滚。
  事务中存在一些概念：
  a) 事务（Transaction）：一批操作（一组DML）
  b) 开启事务（Start Transaction）
  c) 回滚事务（rollback）
  d) 提交事务（commit）
  e) SET AUTOCOMMIT：禁用或启用事务的自动提交模式

当执行DML语句是其实就是开启一个事务
关于事务的回滚需要注意：只能回滚insert、delete和update语句，不能回滚select（回滚select没有任何意义），对于create、drop、alter这些无法回滚.
事务只对DML有效果。

注意：rollback，或者commit后事务就结束了。

和事务相关的语句只有DML语句，（insert delete update）
为什么？因为它们这三条语句都是直接与数据库的“数据”相关的，事务的存在就是为了保证数据的完整性、安全性。

13.2 事务的原理

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

开启事务机制：

只要提交或回滚都会清空历史记录。只是提交事务会把硬盘上的数据改变。

关于事务之间的隔离性：
事务隔离性存在隔离级别，理论上隔离级别分为4个。
第一级别：读未提交read uncommitted（对方事务还没有提交，我们当前事务可以读取到对方为提交的数据）（读未提交存在脏读现象，表示读到了脏数据）
第二级别：读已提交read committed（对方事务提交之后的数据，我方可以提取到）（存在的现象是不可重复读）（解决了脏读现象）
第三级别：可重复读repeatable read（这种隔离级别，解决了不可重复读问题）（存在的问题，读取到的数据是幻想）
第四级别：序列化/串行化（解决了所有问题）（存在效率低问题，需要排队）
oracle默认的隔离级别是读已提交，mysql默认的隔离级别是可重复读。

13.3 演示事务

mysql事务默认情况是自动提交。
怎么关闭自动提交？
start transaction;

(1)创建表

（2）查询表中数据

（3）开启事务

（4）插入数据

（5）查看数据

（6）修改数据

（7）查看数据

（8）回滚事务

（9）查看数据

第十四章 索引

（1）什么是索引？有什么用
索引相当于一本书的目录，通过目录可以快速找到对应的资源。
在数据库方面，查询一张表的时候有两种检索方式。
第一种方式：全表扫描
第二种方式：根据索引检索（效率很高）

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

注意：主键，unique 都会默认的添加索引
根据主键查询效率较高，尽量根据主键检索。

给薪资sal创建索引：


删除索引
drop index 索引名称 on 表名；

14.1 索引的底层原理

索引底层采用的数据结构：b+tree

索引的分类：
单一索引：给单个字段添加索引
复合索引：给多个字段联合起来添加索引
主键索引：主键自动添加索引
唯一索引：给unique约束的字段自动添加索引。

第十五章 视图

15.1 什么是视图

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

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

注意：只有select语句可以创建视图。

第十六章 数据库导入和导出

16.1 导出整个数据库

在windows的dos命令窗口中执行：mysqldump bjpowernode>D:\bjpowernode.sql -uroot -p123

16.2 导出指定数据库的表

在windows的dos命令窗口中执行：mysqldump bjpowernode emp> D:\ bjpowernode.sql -uroot –p123

16.3 导入

登录MYSQL数据库管理系统之后执行：source D:\ bjpowernode.sql

第十七章 数据库设计的三范式

什么是设计范式？
设计表的依据，按照这三范式设计表不会出现数据冗余。

17.1 第一范式

任何一张表都应该有主键，每一个字段原子性不可再分。
学生编号 学生姓名 联系方式
1001 张三 zs@gmail.com,1359999999
1002 李四 ls@gmail.com,13699999999

1001 王五 ww@163.net,13488888888
存在问题：

• 最后一条记录和第一条重复（不唯一，没有主键）
• 联系方式字段可以再分，不是原子性的
  关于第一范式，每一行必须唯一，也就是每个表必须有主键，这是我们数据库设计的最基本要求，主要通常采用数值型或定长字符串表示，关于列不可再分，应该根据具体的情况来决定。如联系方式，为了开发上的便利行可能就采用一个字段了。

17.2 第二范式

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

学生编号(PK) 教师编号(PK) 学生姓名 教师姓名
1001 001 张三 王老师
1002 002 李四 赵老师
1003 001 王五 王老师
1001 002 张三 赵老师
以上虽然确定了主键，但此表会出现大量的冗余，主要涉及到的冗余字段为“学生姓名”和“教师姓名”，出现冗余的原因在于，学生姓名部分依赖了主键的一个字段学生编号，而没有依赖教师编号，而教师姓名部门依赖了主键的一个字段教师编号，这就是第二范式部分依赖。

那么如何解决？
三张表，多对多，两个外键

17.3 第三范式

建立在第二范式的基础上，所有非主键字段直接依赖主键，不能出现传递依赖。
建立在第二范式基础上的，非主键字段不能传递依赖于主键字段。（不要产生传递依赖）
学生编号（PK） 学生姓名 班级编号 班级名称
1001 张三 01 一年一班
1002 李四 02 一年二班
1003 王五 03 一年三班
1004 六 03 一年三班
从上表可以看出，班级名称字段存在冗余，因为班级名称字段没有直接依赖于主键，班级名称字段依赖于班级编号，班级编号依赖于学生编号，那么这就是传递依赖，解决的办法是将冗余字段单独拿出来建立表，如：
第一范式：有主键，具有原子性，字段不可分割
第二范式：完全依赖，没有部分依赖
第三范式：没有传递依赖
数据库设计尽量遵循三范式，但是还是根据实际情况进行取舍，有时可能会拿冗余换速度，最终用目的要满足客户需求。

一对一设计，有两种设计方案：
第一种设计方案：主键共享
第二种设计方案：外键唯一