大家好，我是讯享网，很高兴认识大家。



讯享网

1、查询语句

1.1 基本语句

注意：sql语句对大小写不敏感，所以创建表时id int，Id varchar这样的不能同时出现。

查询可以查：常量、表达式、函数；

讯享网

如果想查的字段包含重复的，自己想去重，那么可以使用。

讯享网

的作用：java中有拼接的功能，而mysql中不是，只有运算的功能；比如两个运算，如果直接能算，那么就计算，如果不能那么会试图把他们转换成数值型再计算，如果转换失败就会把它们转换为0，如果其中有一方为null，那么结果是null；

如果某个字段中有null值，拼接会为null，那么就需要使用函数IFNULL()了。

讯享网

1.2 where——筛选

筛选条件分为几类

• 按条件表达式筛选： =、<、>、<=、>=、<>
• 按逻辑表达式筛选：&&（与）、||（或）、！（非） ===>and、or、not
• 模糊查询：like、between…and、in、is null；like模糊查询其中表示任意多个字符，表示任意一个字符；between…and前后数不能调换，in中的值类型必须统一
• is null和<=>：is null只能判断null值，<=>除了判断null值，还可以判断别的值，推荐使用is null判断null值，因为可读性高

讯享网

1.3 order by——排序

order by后面支持别名。下面这个查询完年薪起别名，可以按别名排序。

order by子句后面可以跟单个字段、多个字段、表达式、函数、别名。

order by子句一般放在查询语句最后面，limit子句除外。

1.4 常见函数——单行函数

程序帮我们封装了，这样我们就可以直接使用，提高了代码的可复用性。

分类：

• 单行函数：concat、length、ifnull等
• 分组函数：又称为统计函数、聚合函数、分组函数、组函数

（1）字符函数

• length：表示获取参数值的字节数；

默认utf-8字符集：一个字母占1个字节、一个汉字占3个字节，不同的字符集占字节数不相同。

讯享网

• concat：拼接字符串

• upper和lower：转换为大写和小写

讯享网

• substr、substring：从索引开始截取字符串，索引是从1开始的，和java不一样

综合练习：姓名首字母大写，其他字母小写

讯享网

• instr：查询某个字符串A在字符串B的索引位置，找不到返回0

• trim：去掉前后的字符，下面是去掉前后的‘ ’空格

讯享网

• lpad：左填充指定长度的指定字符；rpad同理：右填充

• replace：替换

讯享网

（2）数学函数

• round：四舍五入

• ceil：向上取整；floor：向下取整

讯享网

• truncate：截断

• mod取余

讯享网

（3）日期函数

• now：返回当前系统日期，含时间

• curdate：返回系统当前日期，不含时间

讯享网

• curtime：返回系统当前时间，不含日期

• 获取指定的部分，年、月、日、小时、分钟、秒等

讯享网

• str_to_date:将日期格式的字符转换成指定格式的，格式不只有这一种

• date_format：将日期转换成字符

讯享网

• date_diff：计算日期相差多少天

（4）其他函数

• version：版本号
• database：select database();
• user：select user();

（5）流程控制函数

• if：类似if else

讯享网

• case：第一种方式：switch case的效果

1.5 常见函数——分组函数

传一组值，变成一个值。

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

分组函数特点：

1. sum和avg只支持数值型；
2. count、max、min可用于处理任何类型；
3. 所有分组函数均忽略null值，直接这一列就不计算，比如计算平均值，100个人有20个人有null值，那么计算avg除的是80；
4. 所有分组函数都可以和distinct搭配实现去重运算；
5. count()和count(列名)，但是count(列名)如果为null不计算，等于添加了一列1值，然后计算1的数量，等于计算了总个数，但是几乎不用；

在myisam存储引擎下，count()的效率最高，因为它里面包含了计数器，可以直接返回个数；

在innodb存储引擎下，count(*)和count(1)的效率差不多，但是比count(列名)高一些，因为count(列名)要进行一次null值判断

注意：和分组函数一起查询的字段是有限制的

讯享网

上面一个查的是一行，一个查的是多行，所以查出来的是有问题的，肯定不对。

1.6 group by——分组

1.7 having——查询结果作为查询的条件

讯享网

自己理解：这个查询如果使用where 数量 > 2肯定是不行的，因为where子句只能查询当前表，但是当前表中查不到数量字段，那么想使用查询结果作为一张表查询的可以使用having字句。

having的分组条件

还可以按表达式或函数分组或别名（废话）：

group by子句支持单个字段分组，也支持多个字段分组。

2、多表连接查询

2.1 连接条件及分类

从beauty的某女连接boys的某男，使用如下sql语句：

讯享网

这样查询语法上是没毛病的，但是会产生笛卡尔积现象；A表的每条数据都要和B表达的每条数据进行连接，从而产生不是我们想要的数据结果。需要加上相应的查询条件才能避免笛卡尔积。

所以条件如下：

添加有效的连接条件就可以避免，那么添加条件可以分为：

1. sql92标准：仅支持内连接和外连接。
2. sql99标准：内连接，外连接（左外 + 右外），交叉连接。

分类：

• 内连接
  • 等值连接
  • 非等值连接
  • 自连接
• 外连接
  • 左外连接
  • 右外连接
  • 全外连接
• 交叉连接

2.2 sql92语法

（1）等值连接

多表等值连接为多表的交集部分，n表连接至少要n-1个连接条件。

讯享网

如果表连接字段总是重复，两表容易有歧义，所以可以使用别名。

（2）非等值连接

也就是上面的条件变为条件。

讯享网

（3）自连接

连接的就是自己。

2.3 sql99语法

讯享网

上面可读性变高了，因为以前连接条件和筛选条件都是在一起的，而现在则分离了，比如以前的e. = f.;是连接条件却放在了where中。

以前学过了sql99包含很多种连接，每种的语法都不太一样，在join加上不同的说明即可：

内连接（inner）、左外连接（left 【outer】outer可省略）、右外连接（right【outer】outer可省略），全外连接（full【outer】outer可省略），交叉连接（cross）。

（1）内连接：等值、非等、自连接

（2）左外连接和右外连接

讯享网

拿A匹配B，也就是B join A，那么A表为主表，B表为从表，A中所有的数据都会显示，从表中匹配不到的用null去匹配。

left join：左边的是主表，右边的是从表；

right join：右边的是主表，左边的是从表；

（3）全外连接

mysql这里不支持。只是演示一下语法。

就是把两个表全部数据都查出来，查不到的用null填充。

（4）交叉连接

讯享网

有点类似不加条件的查询，包含笛卡尔乘积。

3、子查询

3.1 子查询的分类

嵌套查询，外部查询语句叫主查询或外查询，内部查询语句叫子查询或内查询。

分类

按照子查询出现的位置可以分为：

• 在select后面：仅仅支持标量子查询
• from后面：支持表子查询
• where或having后面：支持标量子查询、列子查询、也支持行子查询（出现的较少）
• exists后面（相关子查询）：支持的是表子查询

按照结果集的行列数不同：

• 标量子查询（结果集只有一行一列）
• 列子查询（结果集有一列多行）
• 行子查询（结果集有一行多列）
• 表子查询（结果集有多行多列）

3.2 where或having后面

特点：都放在小括号内，都放在条件的右侧。

• 标量子查询：一般搭配着单行操作符：>、<、=、>=、<=、!=

select子查询的结果如上，确实是一行一列。

• 列子查询：一般搭配着多行操作符：IN、ANY、SOME、ALL

  a > any(10,20,30) ==> a > min(10,20,30)

  a > all(10,20,30) ==> a > max(10,20,30)

讯享网

• 行子查询：一行多列或多行多列

3.3 select后面

讯享网

3.4 from后面

3.5 exists后面（相关子查询）

讯享网

4、分页查询

讯享网

公式：要显示的页数为:每页条目数为。

那么起始索引为：。

也就是：。

5、联合查询

将多个查询语句的结果合并为一个结果。

讯享网

什么时候用呢？

比如查询的结果来自不同的表，而且这几个表没有联系，但是所查询的信息一致的时候，可以用连接查询；而且这两个查询的结果列数需要相同。不然会报错，为了满足查询结果符合自己需求，查询的两个结果集的列顺序应一致。

union关键字默认是去重的，如果使用的是union all可以包含重复项。

1、插入语句

基本插入语句：

对于像二进制类型等在sql中没法直接插入，需要使用jdbc等java技术转换类型才能插入的，可以设置字段类型为nullable，这样，就可以不插入了。

可为空的字段，不想插入值的话，要么值写null，要么字段都不加上。

插入值不太多的时候可以用下面这个语句：

讯享网

两种方式对比

• 第一种方式支持多行插入，第二种不支持；
• 第一种方式支持子查询，第二种不支持；

2、修改语句

修改单表中记录的语句：

讯享网

修改多表中记录的语句（sql92语法）：

修改多表中记录的语句（sql99语法）：

讯享网

3、删除语句

• delete删除（单表删除）：

• delete删除（多表删除sql92语法）：

讯享网

• delete删除（多表删除sql99语法）：

这里需要知道多表删除就是想删哪张表就delete哪个表，另一张表join进来。

不加筛选条件整张表都删掉了，需要慎重。

• truncate清空数据：

讯享网

这样就是整张表的数据记录都删了，不能恢复，需要非常慎重。

delete和truncate

• delete可以加条件，truncate是直接删除不保留
• truncate效率高一点点
• 假如说要删除的表中有自增长列，用delete删除后，再插入值，自增长列的值从断点开始；truncate从1开始（）
• truncate删除没有返回值，delete删除有返回值（几行受影响）
• truncate删除不能回滚，delete删除可以回滚

1、简单介绍

• 创建：create
• 修改：alter
• 删除：drop

2、库的管理

2.1 库的创建

2.2 库的修改

讯享网

2.3 库的删除

3、表的管理

3.1 表的创建

讯享网

3.2 表的删除

3.3 表的修改

讯享网

3.4 表的复制

3.5 数据类型

怎么设置数据类型有无符号：默认是有符号的。设置成无符号，创建表时在表字段后面加上。

（1）整型

（2）浮点型

默认数值精度会按照首次插入精度来确定。

（3）定点型

decimal(m,d) 参数m<65 是总个数，d<30且 d<m 是小数位。默认。

（4）字符串

char比varchar耗费空间，但是效率更高一点。

（5）二进制数据

1._BLOB和_text存储方式不同，_TEXT以文本方式存储，英文存储区分大小写，而_Blob是以二进制方式存储，不分大小写。

2._BLOB存储的数据只能整体读出。

3._TEXT可以指定字符集，_BLO不用指定字符集。

（6）日期时间类型

timestamp支持的范围比较小，也就到1970-2038年的某个时间，而datetime的取值可以从1000-9999年。

timestamp和实际的时区有关，更能反应出实际的时间，而datetime只能反映出当地时区。（建议这里可以自己测试一下）

（7）枚举类型

又称为枚举类型哦，要求插入的值必须属于列表中指定的值之一。

如果列表成员为1~255，则需要1个字节存储；如果列表成员为255~65535，则需要2个字节存储，最多需要65535个成员！

（8）Set类型

和Enum类型类似，里面可以保存0~64个成员。和Enum类型最大的区别是：Set类型一次可以选取多个成员，而Enum只能选一个根据成员个数不同，存储所占的字节也不同。

讯享网

3.6 显示表结构

<img src=”https://gitee.com/yzdswzt/cloudimages/raw/master/img/image-20210902102020957.png” alt=“image-020957” style=“zoom:80%;” />

4、约束

4.1 简单介绍

一种限制，用于限制数据，保证其准确可靠。

一共有6大约束：

• NOT NULL：非空约束，保证字段值不为空，比如姓名、学号等；
• DEFAULT：默认约束，保证该字段有默认值，比如性别；
• PRIMARY KEY：主键约束，保证该字段值的唯一性，并且非空，比如学号、员工编号等；
• UNIQUE：唯一约束，保证该字段的值具有唯一性，但可以为空，比如座位号等；
• CHECK：检查约束，【mysql中不支持，语法没错，但是mysql中不起作用】，比如年龄等，可以用检查约束限制范围；
• FOREIGN KEY：外键约束，用于限制两个表的关系的，保证该字段值必须来自于主表的关联列的值，在从表中添加外键约束，用于引入主表的值，比如专业编号、员工部门编号等等；

约束添加的分类：

• 列级约束：上面几个都可以写，语法都支持，但没有效果；
• 表级约束：除了非空和默认，其他的都支持；

讯享网

4.2 添加列级约束

4.3 添加表级约束

讯享网

主键和唯一的对比：

外键的特点

1. 要求在从表设置外键关系
2. 从表的外键列的类型和主表的关联列的类型要求一致或兼容
3. 主表中的关联列必须是一个key（一般是主键或唯一键）

4.4 修改表时添加约束

4.5 修改表时删除约束

讯享网

5、标识列

又称为自增长列，，可以不用手动插入值，系统提供默认的序列值。

一个表最多只能有一个标识列。

标识列必须和主键搭配吗，不一定，但是要求是一个key。

标识列的类型只能是数值型。

5.1 创建表时创建标识列

5.2 设置步长

讯享网

5.3 修改表时创建标识列

1、简单介绍

TCL：Transaction Control Language事务控制语言。

事务：一组sql语句要么都执行，要么都不执行（回滚）。

Innodb支持事务，Myisam、memory不支持事务。

讯享网

1.1 事务的ACID原则

• 原子性（Atomicity）：原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
• 一致性（Consistency）：事务必须使数据库从一个一致性状态变换到另外一个一致性状态。
• 隔离性（Isolation）：事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
• 持久性（Durability）：持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。

2、事务的创建

• 隐式事务： 没有明显的开启和结束的标记，比如delete、insert、update语句；每一条语句相当于一条事务；
• 显式事务：有明显的开启和结束的标记，必须先设置上面这种自动提交功能为禁用，这样才能保证多条sql语句同时执行时，中途出现问题，不会一条一条提交；

2.1 使用事务的步骤

讯享网

delete和truncate事务的区别

delete删除可以回滚，truncate不可以，删了就没了。

3、并发情况下的事务问题

同时运行多个事务访问同一个数据资源时，可能会导致并发问题：

• 脏读：两个事务T1、T2，T1修改了数据但未提交就被T2读取到了，如果T1回滚，T2读到的数据就是无效的了；（字段）
• 不可重复读：两个事务T1、T2，T1读取了一个字段T2更新了，T1就读不到以前的那个值了；
• 幻读：对两个事务T1、T2，T1从一个表中读取了一个字段，然后T2在该表中插入了新行，T1再读这个表会多出几行；（插入行）

3.1 数据库的事务隔离级别

• Read uncommitted

读未提交，顾名思义，就是一个事务可以读取另一个未提交事务的数据。

事例：老板要给程序员发工资，程序员的工资是3.6万/月。但是发工资时老板不小心按错了数字，按成3.9万/月，该钱已经打到程序员的户口，但是事务还没有提交，就在这时，程序员去查看自己这个月的工资，发现比往常多了3千元，以为涨工资了非常高兴。但是老板及时发现了不对，马上回滚差点就提交了的事务，将数字改成3.6万再提交。

分析：实际程序员这个月的工资还是3.6万，但是程序员看到的是3.9万。他看到的是老板还没提交事务时的数据。这就是脏读。

• Read committed

读提交，顾名思义，就是一个事务要等另一个事务提交后才能读取数据。

事例：程序员拿着信用卡去享受生活（卡里当然是只有3.6万），当他埋单时（程序员事务开启），收费系统事先检测到他的卡里有3.6万，就在这个时候！！程序员的妻子要把钱全部转出充当家用，并提交。当收费系统准备扣款时，再检测卡里的金额，发现已经没钱了（第二次检测金额当然要等待妻子转出金额事务提交完）。程序员就会很郁闷，明明卡里是有钱的…

分析：这就是读提交，若有事务对数据进行更新（UPDATE）操作时，读操作事务要等待这个更新操作事务提交后才能读取数据，可以解决脏读问题。但在这个事例中，出现了一个事务范围内两个相同的查询却返回了不同数据，这就是不可重复读。

• Repeatable read

重复读，就是在开始读取数据（事务开启）时，不再允许修改操作

事例：程序员拿着信用卡去享受生活（卡里当然是只有3.6万），当他埋单时（事务开启，不允许其他事务的UPDATE修改操作），收费系统事先检测到他的卡里有3.6万。这个时候他的妻子不能转出金额了。接下来收费系统就可以扣款了。

分析：重复读可以解决不可重复读问题。写到这里，应该明白的一点就是，不可重复读对应的是修改，即UPDATE操作。但是可能还会有幻读问题。因为幻读问题对应的是插入INSERT操作，而不是UPDATE操作。

• Serializable 序列化

Serializable 是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。

3.2 默认事务隔离级别

• Oracle 支持的 2 种事务隔离级别：Read committed, Serializable。 Oracle 默认的事务隔离级别为: Read committed
• Mysql 支持 4 种事务隔离级别. Mysql 默认的事务隔离级别为:Repeatable read

3.3 实际操作

3.4 保存点

讯享网

演示如何使用savepoint;

1、简单介绍

视图其实就是虚拟表，和普通表一样使用，MySQL5.1版本出现的新特性，是通过表动态生成的数据。

舞蹈班和普通班：其实没有舞蹈班，只有领导来了以后，才临时搭建的舞蹈班，领导走了以后自动解散，普通班不是这样的。

示例（没用视图之前）：

讯享网

示例（有了视图）：

2、视图的创建

讯享网

视图的好处：

• 重用sql语句
• 简化复杂的sql操作，不必知道它的查询细节
• 保护数据，提高安全性

3、视图的修改

讯享网

4、视图的删除

5、视图的查看

讯享网

6、视图的更新

用于更改视图中的数据。其实和表的更新一样。

不过有特殊情况：

• 包含以下关键字的sql语句：分组函数、distinct、group by、having、union或者union all;
• 常量视图；
• Select中包含子查询；
• join；
• from一个不能更新的视图；
• where子句的子查询引用了from子句中的表；

视图和表的区别

• 一个是create view，一个是create table
• 是否实际占用物理空间？视图只占用了一小部分，表占用了物理空间
• 视图一般不能增删改（有限制），表可以增删改查

1、变量的分类

• 系统变量：
  • 全局变量
  • 会话变量
• 自定义变量：
  • 用户变量
  • 局部变量

2、系统变量

由系统提供，不是用户定义，属于服务器层面的。

讯享网

服务器每次启动都会为全局变量赋初始值，针对于所有的会话都有效，但不能跨重启，如果想真正重启也修改，就需要修改配置文件了。

会话变量的作用域针对于当前会话（连接），换一个连接就不一样了。

3、自定义变量

变量是自己定义的。

3.1 用户变量

作用域：针对于当前会话有效的，换一个连接就无效了，和Session的作用域一样。

使用在任何地方，begin end里面或外面都行。

3.2 局部变量

作用域：仅仅在局部有效，也就是begin end里有效。

讯享网

1、简单介绍

存储过程和函数类似Java的方法。

存储过程和函数定义：事先经过编译并存储在数据库中的一段sql语句的集合。

好处：

• 简化应用开发人员的很多工作
• 减少数据在数据库和应用服务器之间的传输
• 提高了数据处理的效率

2、存储过程

2.1 创建存储过程

注意：

• 参数列表包含三部分：参数模式、参数名、参数类型

讯享网

• 参数模式：
  • IN：该参数可以作为输入，也就是需要调用方传入值
  • OUT：该参数可以作为输出，也就是该参数可以作为返回值
  • INOUT：该参数既可以作为输入，又可以作为输出，既需要调用方传入值，又可以作为返回值
• 如果存储过程体仅仅只有一句话，BEGIN END可以省略，存储过程体种的每条sql语句都需要加分号结尾，存储过程的结尾可以使用重新设置

2.2 调用存储过程

讯享网

2.3 案例

有点难，案例记录一下！！

• 普通类型参数的存储过程

• 带in模式参数的存储过程

讯享网

• 带out模式参数的存储过程

讯享网

• 带inout模式参数的存储过程

2.4 删除存储过程

不能进行修改存储过程里面的内容，如果想修改，那就删掉原来的，再创建新的。

讯享网

2.5 查看存储过程

3、函数

存储过程和函数的区别：

• 函数只能有一个返回，存储过程可以又任意个，0个也行，多个也行
• 增删改比较适合存储过程，查询一个值比较适合函数

3.1 创建函数

讯享网

参数列表包含两部分：参数名、参数类型；函数更接近于Java中的方法。

函数体：肯定有return语句，如果没有会报错，如果return语句没放在函数体最后也不报错，但是没任何意义。

如果存储过程体仅仅只有一句话，BEGIN END可以省略，存储过程的结尾可以使用。

3.2 调用语法

3.3 案例

• 无参有返回的

讯享网

• 有参有返回的

3.3 查看函数

讯享网

3.4 删除函数

1、结构分类

顺序结构：程序从上向下依次执行；

分支结构：程序可以从多条路径中选择一条去执行；

循环结构：程序在满足一定条件的基础上，重复执行一段代码；

2、分支结构

2.1 if函数

可以实现简单的双分支。可以应用在任何地方。

讯享网

2.2 case结构

情况1：类似于switch语句，一般用于等值判断

情况2：类似于多重if，一般用于实现区间判断，看到底在哪个区间

讯享网

可作为表达式，嵌套在其他语句中使用，可以放在任何地方，begin end中或begin end的外面；也可以作为独立的语句中使用，只能放在begin end中。

2.3 if结构

实现多重分支。只能应用在begin end中。

3、循环结构

3.1 简单介绍

分类：while、loop、repeat。

循环控制：iterate类似于Java中的continue，结束当前循环，进行下一次循环；leave相当于Java中的break

3.2 while循环

讯享网

案例

含leave的语句

讯享网

3.3 loop循环

可以用来模拟简单的死循环。

3.4 repeat循环

讯享网