数据库-mysql

最新推荐文章于 2024-02-26 11:51:43 发布

月球转转转

最新推荐文章于 2024-02-26 11:51:43 发布

阅读量868

点赞数

分类专栏：笔记 mysql 文章标签： mysql

本文链接：https://blog.csdn.net/weixin_45926582/article/details/106448438

版权

笔记同时被 2 个专栏收录

5 篇文章 0 订阅

订阅专栏

mysql

2 篇文章 0 订阅

订阅专栏

本文详细介绍了MySQL数据库，从数据模型的概念如E-R图和关系模型，到数据库设计的各个阶段，包括需求分析、概念结构设计、逻辑结构设计等。接着深入探讨了MySQL的安装、配置、存储引擎的选择，以及字符集的设定与管理。还讲解了MySQL客户端的使用，如命令行操作和PHPMyAdmin管理工具。此外，文章还涵盖了MySQL数据类型的选用、数据库操作，如创建、修改、查询和删除，以及PHP与MySQL的Web应用基础。内容详实，适合MySQL初学者和开发者参考学习。

摘要由CSDN通过智能技术生成

数据库-mysql

一、简介

一、数据模型

（一）E-R图：一种概念模型

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tFnc2zZo-1590849282556)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20200409204224623.png)]

（二）关系模型

关系模式 —— 对关系的描述一般形式为：关系名(属性1，属性2，……，属性n)

1.完整性约束：

实体完整性：主属性不能为空
参照完整性：外键的属性值必须在与其关联的另一张的主键的属性值范围之内，即外键是另一张表的主键
用户自定义完整性

2.关系运算

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QQ37oA3p-1590849282561)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20200409205200069.png)]

（1）集合操作

（2）纯关系操作：投影()、连接（等值连接、自然连接）、选择、除

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xm51qzLw-1590849282567)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20200409205329861.png)]

二、数据库设计

（一）需求分析

（二）概念结构设计：E-R图设计

实体与属性的划分原则：第一范式

现实世界的事物能作为属性对待的，尽量作为属性对待。 Ø 作为属性，不能再具有需要描述的性质。 Ø 属性必须是不可分的数据项，不能包含其他属性。 Ø 属性不能与其他实体具有联系。

（三）逻辑结构设计

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ZoFrka1u-1590849282572)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20200409210106335.png)]

1.数据模型的优化

——关系数据模型的优化通常以规范化理论为指导。

关系范式理论 : 范式是符合某一种级别的关系模式的集合。关系数据库中的关系必须满足一定的要求, 满足不同程度要求的为不同范式。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-61cSnHhc-1590849282576)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20200409210648173.png)]

（四）设计外模式

数据库模式

全局模式: 考虑系统全局应用需求，时间效率、空间效率、易维护等。
外模式——视图机制: 考虑局部应用的特殊需求和用户体验。

（1）使用更符合用户习惯的别名（2）针对不同级别的用户定义不同的视图，提高系统的安全性（3）简化用户对系统的使用

（五）物理结构设计——物理存储结构

为一个给定的逻辑数据模型选取一个最适合应用要求的物理结构的过程，就是数据库的物理设计。

数据库三范式：

三范式

1NF:字段不可分;
2NF:有主键，非主键字段依赖主键;
3NF:非主键字段不能相互依赖;

解释:
1NF:原子性字段不可再分,否则就不是关系数据库;
2NF:唯一性一个表只说明一个事物;
3NF:每列都与主键有直接关系，不存在传递依赖;

三、MySQL入门

安装MySQL的系统环境需求

了解操作系统的版本、以选取合适的MySQL安装包
数据库的安装位置
MySQL数据库文件的存储位置：data
MySQL默认端口：3306

如果在安装MySQL前，3306号端口已经被占用了，我们该如何处理?

（1）用命令查看端口占用情况： netstat -aon|findstr 3306

（2）在执行taskist命令根据进程id查找进程名称，在任务管理器中可以右击选择查看文件位置，也可以结束进程，这样再安装mysql就没问题了。

MySQL的获取路径 www.mysql.com

MySQL的图形用户管理工具

WAMP与LAMP

WAMP/LAMP是MySQL在Internet上构建网站与信息系统的一种应用环境，使用Windows/Linux作为操作系统，Apache作为Web服务器，数据管理系统为MySQL，服务器端所用的脚本语言是PHP。
WAMPServer安装
WAMPServer的关键配置

通过IDE实现MySQL的操作与通过Command命令操作各有什么特点？

一、MySQL的配置与管理

（一）MySQL字符集的设定

字符 character：是人类语言中最小的表义符号，用来表示字符的数值就是这个字符的编码。

字符集character set：字符和编码之间的映射集合，称为字符集。

字符序 collation：同一字符集内又存在字符间不同的比较规则，因而产生了字符序。

每个字符集都有多种字符序（其中一种为默认字符序default collation）

(1)MySQL字符序的命名规则：

以字符集的名称开头，以国家名或者general居中，以 ci（大小写不敏感）、cs（大小写敏感）或bin（二进

制编码）结尾。
例如：latin1_swedish_ci
建议使用UTF8字符集，它几乎包含了世界上所有的语言的编码，其默认字符序为 utf8_general_ci

命令： show character set; 用于查看当前MySQL实例支持的字符集、默认的字符序及字符集占用的最大字节长度等信息。
命令：**show variables like ‘character%’;**用于查看当前MySQL会话使用的字符集

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-1HG4ZsxB-1590849282581)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20200410152200343.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gBY6LGbE-1590849282584)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20200410152335649.png)]

(2)解决字符乱码：

可以通过程序代码、临时会话等多种手段为MySQL设定各个环节的字符集，但是设置的不一致会导致最终的查询结果出现乱码，下面介绍一种最简单最彻底的方法：

1、在Windows系统下中止MySQL服务

2、在MySQL的安装目录下找到my.ini

3、打开my.ini以后，在[client]和[mysqld]下面均加上default- character-set=utf8，保存并关闭

4、启动MySQL服务

（二）MySQL存储引擎

存储引擎，在关系数据库领域也叫做表类型，就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。MyISAM和InnoDB是最常用的两种存储引擎

MyISAM存储引擎：

MySQL默认的存储引擎，相对简单，效率上要优于InnoDB。如果系统读多，写少，那么MyISAM是最好的选择。且MyISAM恢复速度快，可直接用备份覆盖恢复。

InnoDB存储引擎：

支持事务处理，不加锁读取，支持外键，支持行锁。如果系统读少、写多，尤其是并发写入高，则首选InnoDB

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mYPxHXUk-1590849282587)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20200410153725266.png)]

查看存储引擎：

show variables like ‘%storage engine%’;

可以用来查看数据库当前使用的存储引擎

修改存储引擎：

最彻底的方法：在启动配置文件中，设置服务器存储引擎

[mysqld] default-storage-engine=

安全性较高：用 create table 语句，指定存储引擎

CREATE TABLE t (i INT) ENGINE = ;

使用set命令为当前客户机会话设置

SET @@storage_engine=;

修改时影响读取性能：使用 ALTER TABLE 命令，更改特定数据表的存储引擎

ALTER TABLE t ENGINE = InnoDB;

MySQL密码设定：

新版本的mysql用这个SQL语句：update user set authentication_string = password(‘root’), password_expired = ‘N’, password_last_changed = now() where user = ‘root’;

最后用flush privileges;刷新MySQL状态，即可使新的密码生效。

二、MySQL客户端使用

1、命令行界面执行命令，通常分为如下几种形式：

1.直接输入命令，并执行。此种方式适合于简单的命令。

执行编写好的SQL脚本，例如：source C:/test.sql

2、MySQL的PHPMyadmin管理工具的使用

三、MySQL数据库的Web应用入门

1.PHP连接并操纵MySQL数据库梗概

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-sxuJsswn-1590849282589)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20200410162636481.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-b4bW13N2-1590849282591)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20200410162740163.png)]

极简主义知识储备php

PHP语言以<?php ?>作为代码块标记；
PHP语言以$开头定义变量；
PHP5以上版本推荐用mysqli内置函数连接MySQL;
必要的SQL语句：insert \select…

四、MySQL数据库操作

（一）数据库的基本操作

创建数据库使用的SQL语句是：

CREATE DATABASE 数据库名;
找找数据库在哪里？

SHOW VARIABLES LIKE 'datadir‘;
选择数据库使用的SQL语句是：

USE 数据库名;
修改数据库的MySQL语句：

ALTER DATABASE [数据库名] [DEFAULT] CHARACTER SET <字符集> | [DEFAULT] COLLATE <比较规则>;

注意：[],<>,|在具体命令中不能出现
查看数据库的MySQL命令：

SHOW DATABASES;
删除数据库的SQL语句：

DROP DATABASE 数据库名;

（二）数据库命名的规则：

不能与其它数据库重名。
名称由任意字母、阿拉伯数字、下划线（_）和“$”组成，但不能使用单独的数字。
不能使用MySQL关键字作为数据库名、表名。
在默认情况下，Windows下数据库名、表名的大小写是一样的，而在Linux下数据库名、表名的大小写是有区别的

（三）MySQL数据类型

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9bc2Dv0M-1590849282593)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200411144527292.png)]

（1）数值类型之整数类型

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-uwRYXGo4-1590849282595)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200411144649677.png)]

（2）数值类型之小数类型

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-1IXIJU10-1590849282597)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200411144800757.png)]

精确小数类型decimal

decimal(length, precision)用于表示精度确定（小数点后数字的位数确定）的小数类型，length决定该小数的最大

位数，precision用于设置精度（小数点后数字的位数）。

例如：

decimal (5,2)表示小数取值范围：-999.99～999.99

decimal (5,0)表示： -99999～99999的整数。

(3)日期/时间类型

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Ro5wbbMh-1590849282599)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200411151055356.png)]

(4)字符串类型

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SIJcDqsj-1590849282601)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200411151208508.png)]

(5)复合类型

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-MMJQZE8g-1590849282603)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200411151254634.png)]

(6)二进制类型

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LVPTHVNi-1590849282605)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200411151406980.png)]

选择合适的数据类型

选择合适的数据类型，能节省储存空间，提升计算性能。

在符合应用要求（取值范围、精度）的前提下，尽量使用“短”数据类型
数据类型越简单越好。
在MySQ中，应该用内置的日期和时间数据类型，而不是用字符串来存储日期和时间。
尽量采用精确小数类型（例如decimal），而不采用浮点数类型。
尽量避免NULL字段，建议将字段指定为NOT NULL约束。

（四）数据表操作之创建、查看、复制、改名及删除操作

1.在设计表结构时要考虑下面几个方面：

l 字段名要通俗易懂且具有代表性，字段名不允许重复，为了编程序方便，尽量使用英文名字。
l 字段类型选择的原则是：根据需求选择合适的字段类型，在满足需求的情况下字段类型尽可能小。不参加数学计算的数字定义为字符串型，如学号、电话号码等。
l 字段长度是指能容纳该字段值的最大数量，如学号为12位数字，长度设置为12；课程名可能包含10个汉字，长度就要设置为10。
l 字符集编码选择：含有中文字符时统一采用utf8或gbk类型，以避免乱码情况的发生。

2.创建数据表:创建数据库表使用create table 语句。

语法格式如下：

create table 表名(

字段名1 数据类型 [约束条件],

字段名2 数据类型 [约束条件],

…

[其他约束条件],

[其他约束条件]

)其他选项；

3.SQL语句中关键字做一些说明：

\1. PRIMARY KEY用于定义主键。也可以使用多字段来定义主键。
\2. COMMENT 注释该字段的含义。
\3. NOT NULL是非空约束。
\4. DEFAULT为该字段加默认值，可以减少输入次数。
\5. AUTO_INCREMENT为自增型属性，一般用作主键，数值会自动加1。
\6. 当表中有外键字段时用CONSTRAINT设置外键。
\7. ENGINE=InnoDB是设置该表的存储引擎，DEFAULT CHARSET=utf8是设置该表的默认字符集。

4.显示表结构，查看数据表**

显示表结构的命令：

方式一：DESCRIBE/DESC 表名

– 使用DESCRIBE table_name可显示指定表的结构
– DESCRIBE也可用DESC替代

方式二：SHOW CREATE TABLE 表名查看表的详细信息

查看数据列表命令：show tables;

5.复制表结构

复制一个表结构有两种实现方法:

方法一：在create table语句的末尾添加like子句，可以将源表的表结构复制到新表中，语法格式如下：

create table 新表名 like 源表

方法二、在create table语句的末尾添加一个select语句，可以实现表结构和表记录的复制。语法格式如下：

create table 新表名 select * from 源表

6.修改表名

修改表名的语法格式较为简单，语法格式如下：

rename table 旧表名 to 新表名

或者：

alter table 旧表名 rename 新表名

7.删除表

删除表的SQL语法格式为:

drop table 表名;

如果表之间存在外键约束关系，应先删除外键约束条件，再删除表，或者先删子表，再删父表。

注意：删除数据表的操作应该谨慎使用。一旦删除了数据表，表中的数据将会全部清除，没有备份则无法恢复。

(五)数据表操作扎实基本功之修改表

修改表主要包括以下几个方面：

修改字段相关信息

修改约束条件

修改表的其他选项

1.修改表结构之Alter TABL语句

ALTER TABLE 表名 alter_spec [, alter_spec …];

alter_spec定义要修改的内容，其常见语法片段如下：

ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name] **–**添加新字段
**| ADD [CONSTRAINT [symbol]] PRIMARY KEY (index_col_name,…) --**添加主键
| ADD [CONSTRAINT [symbol]]UNIQUE (index_col_name,…) **–**添加唯一索引
| ADD [CONSTRAINT [symbol]] FOREIGN KEY (index_col_name,…) **reference (index_col_name,…) --**添加外键
**| ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT} --**修改默认值
**| CHANGE [COLUMN] old_col_name new_col_name column_definition --**修改字段名及数据类型
| MODIFY [COLUMN] col_name column_definition **–**修改字段类型
| DROP [COLUMN] col_name **–**删除字段
| DROP PRIMARY KEY --**删除主键
| DROP INDEX index_name --**删除索引名称
| DROP FOREIGN KEY fk_symbol **–**删除外键
**| table_options --****更改表的其它选项

> ALTER TABLE语句允许对表进行多个修改操作，其子句间用逗号分隔。

2.修改字段相关信息–添加/删除字段

向表添加新字段的语法格式如下：

Alter table 表名 add 新字段名数据类型 [first | after 旧字段名]

删除字段的语法格式如下：

alter table 表名 drop 字段名

3.修改字段相关信息–修改字段名/数据类型

修改表的字段名及数据类型的语法格式如下：

alter table 表名 change 旧字段名新字段名数据类型

只修改表字段数据类型的语法格式如下：

alter table 表名 modify 旧字段名新数据类型

4.设置自增型字段

默认情况下，MySQL自增型字段的值从1开始递增，且步长为1。

设置自增型字段的语法片段如下：

字段名数据类型 auto_increment

自增型字段的数据类型必须为整形，建议将自增型字段设置为主键。

5.数据完整性包括

实体完整性：通过主键约束和唯一性约束实现
参照完整性：通过外键约束实现
用户自定义的完整性：通过非空约束、默认值约束和检查约束实现

(1)添加主键、外键、唯一性约束条件的语法格式：

ALTER TABLE 表名 ADD [CONSTRAINT [约束名]] 约束类型(字段名)

(2)修改约束条件–添加或删除主键约束

主键约束（primary key）：确保表中每一行记录是唯一的，一个表只能有一个主键，主键的值不能重复而且不能为空（ null）。

向表添加主键的语法格式：

alter table 表名 add primary key (字段名);

删除主键约束的语法格式：

alter table 表名 drop PRIMARY KEY

(3)修改约束条件–添加或删除外键约束

外键约束（foreign KEY）：用于保证外键字段值与父表中主键字段值的一致性，外键字段值要么是NULL，要么是父表中主键字段值的复制。

添加外键的语法格式：

ALTER TABLE 表名 ADD CONSTRAINT 外键名

foreign KEY(外键字段) references 关联表名(关联字段) [on delete 级联选项] [on update 级联选项];

删除外键的语法格式：

alter table 表名 drop foreign Key 约束名

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VQe5QKNk-1590849282608)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200411162648737.png)]

(4)修改约束条件——添加或删除唯一性约束

唯一性约束（unique）：用于保证表中某个字段的值不重复且值能为空（ null），一个表可以定义多个唯一性约束。

添加唯一性约束的语法格式：

alter table 表名 add [constraint [约束名]] unique (字段名);

删除唯一性约束的语法格式：

alter table 表名 drop index 唯一索引名;

(5)修改约束条件–添加或删除非空约束

非空约束（not null）：用于保证表中某个字段的值不能取 null值。

添加非空约束的语法格式：

alter table 表名 modify 字段名数据类型 not null;

取消非空约束：

alter table 表名 modify 字段名数据类型 null;

(6)修改约束条件–添加或删除默认值约束

默认值约束（default）：用于指定一个字段的默认值。插入记录时，如果没有给该字段赋值，数据库系统会自动为这个字段插入默认值。

添加默认值约束：

alter table 表名 alter 字段名 set default 默认值;

删除默认值约束：

alter table 表名 alter 字段名 drop default;

6.修改表的其它选项

alter table 表名 engine=新的存储引擎类型

alter table 表名 default charset=新的字符集

alter table 表名 auto_increment=新的初始值

alter table 表名 pack_keys=新的压缩类型

数据完整性通过主键约束、外键约束和用户自定义约束来实现，保证了数据库中的数据不受非法数据的影响，保证了数据的一致性

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-w7RDKDt4-1590849282611)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200411163056845.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hHrwVY7P-1590849282614)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200411163037942.png)]

(六)真武·数据表记录操作

表记录操作包括：

插入记录

修改记录

删除记录

查询记录

1.-插入记录（INSERT）

语法格式一：

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

• 插入一条记录，只对部分字段赋值

insert into 表名(字段A, 字段B, 字段C) values(值A, 值B, 值C);

• 插入一条记录，对所有字段都赋值

insert into 表名 values(值A, 值B, 值C…, 值F);

• 一次插入多条记录

insert into 表名[(字段列表)] values

(值列表1),(值列表2),…(值列表n);

语法格式二:

insert into 目标表名[(字段列表1)] select (字段列表2) from 源表 where 条件表达式

该语句向表中插入从另一个或多个表查询的结果集。

replace命令：

replace与insert的区别：使用replace向表插入新记录时，如果新记

录的主键值或者唯一性约束的字段值与已有记录相同，则先删除已有

记录，然后再插入新记录（注意：已有记录删除时也不能违背外键约

束条件）。

特殊字符转义：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xCAqbjeK-1590849282617)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200412162101470.png)]

插入有约束的数据

• 向默认值约束字段插入数据时，字段值可以使用default关键字，表示插入的是该字段的默认值。

• 表之间存在外键约束时，外键的值要么是null，要么是父表中主键字段的值。

向auto_increment字段插入数据时，建议插入NULL值，此时将向自增型字段插入下一个编号。

2.修改记录（UPDATE）

语法格式:

update 表名

set 字段名1=值1,字段名2=值2,…… ,字段名n=值n

[where 条件表达式];

其中，set子句指定了要修改的字段以及该字段修改后的值。 where子句指定了表中需要修改的记录。若省略了where，则修改表中的所有记录。

3.删除记录（DELETE）

语法格式:

delete from 表名 [where 条件表达式];

说明：如果没有指定where条件，将删除所有的记录；如果指定了 where条件，将按照指定的条件进行删除。
从多表中删除有外键约束的记录时，外键级联规则选项设置为cascade 。

清空某个表，使用truncate语句，语法格式如下：

truncate [table] 表名；

说明：truncate table 用于完全清空一个表。清空记录的表如果是父表， truncate命令将永远执行失败

(七)索引分类

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-FYinNqfD-1590849282619)(…/…/…/…/Documents/Tencent%20Files/861545497/FileRecv/MobileFile/Screenshot_20200412_153259_com.netease.edu.ucmooc.jpg)][外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-auk7dMq3-1590849282621)(…/…/…/…/Documents/Tencent%20Files/861545497/FileRecv/MobileFile/Screenshot_20200412_153350_com.netease.edu.ucmooc.jpg)]

索引分类

Ø 普通索引（index）
Ø 唯一性索引（unique）
Ø 主键索引（primary key）
Ø 全文索引（fulltext）
Ø 空间索引（spatial）
Ø 单列索引和组合索引

索引设计原则

Ø 索引并非越多越好

Ø 字段的离散度越高，该字段越适合选作索引的关键字

Ø 经常排序和分组的数据列、两个表的连接字段应该建立索引

Ø 更新频繁的字段不适合创建索引，不会出现在where子句中的字段不应该创建索引

创建索引

在创建表的同时创建索引
在已有表上创建索引

（1）在创建表的同时创建索引

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-sjnn1GwB-1590849282623)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200412163630628.png)]

（2）在已有表上创建索引

方法一：

create [ unique | fulltext ] index 索引名 on 表名 ( 字段名 [(长度)] [ asc | desc ] )

创建普通索引示例

Create INDEX teacher_id_index ON teacher( teacher_id );
创建唯一性索引示例

Create UNIQUE INDEX teacher_id_index ON teacher( teacher_id);

方法二：

alter table 表名 add [ unique | fulltext ] index 索引名 ( 字段名 [(长度)] [ asc | desc ] )

（6）删除索引

drop index 索引名 on 表名

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RzFhFHvn-1590849282625)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200412164106544.png)]

五、数据查询

1.单表查询

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-J7TqDgvH-1590849282627)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200413101438890.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-1PvtOkhM-1590849282629)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200413101547604.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Wx66AN0o-1590849282631)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200413102254319.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-IcbOqqtM-1590849282633)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200413102316604.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-L5UymS3C-1590849282636)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200413102338008.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RKctLfP4-1590849282638)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200413102440160.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-H5Dp4yw7-1590849282640)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200413102544589.png)]

2.条件查询

条件查询及WHERE条件判断符

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ujlsdNXA-1590849282642)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200413102829906.png)]

带IN关键字的条件查询

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-pnKifcOi-1590849282644)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200413102940791.png)]
带BETWEEN AND的范围查询

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-FFmau5c6-1590849282646)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200413103019942.png)]

带LIKE关键字的字符匹配查询

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4QWcryPN-1590849282648)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200413103146539.png)]

空值查询(IS NULL)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kJ77WlXq-1590849282650)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200413103232135.png)]

3.连接查询

理解连接查询

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DLU0FQHz-1590849282652)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200413103511925.png)]

1.内连接查询(INNER JOIN)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7QP7Hqd3-1590849282656)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200413103620311.png)][外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HQsLNlYz-1590849282658)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200413103757019.png)]在设计内连接查询时，强调几点:

 表之间一定要有连接字段。

 查询结果的多个列来源于不同的表，需要在列名称前加表名称作为前缀。

 为了简化书写，可以给表指定别名，格式为：

SELECT … FROM table A
别名 A 和表名table 之间至少加一个空格

2.外连接查询 (OUTER JOIN)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jV33AhmO-1590849282660)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200413104101083.png)][外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zJFGYi0E-1590849282662)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200413104449978.png)][外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8C5d1d8k-1590849282666)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200413104515922.png)][外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QFyOzA57-1590849282667)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200413104547666.png)]

3.复合连接条件查询 (连接查询+WHERE)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Mq15uaAo-1590849282670)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200413104620561.png)]

六、排序及分组数据

1.查询结果排序 — ORDER BY

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-91Y5MSiF-1590849282672)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200413104927126.png)]

2.分组查询— GROUP BY

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lat7CWev-1590849282674)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200413105021029.png)]

3.查询结果不重复— DISTINCT

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-isAv5TUT-1590849282676)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200413105107023.png)]

4.限制查询结果的数量— LIMIT

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-X2HAf38U-1590849282678)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200413105155839.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5kx2KhgN-1590849282682)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200413105223681.png)]

5.Limit和Union的用法

一、limit的用法

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kbdMeCPr-1590849282684)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200413105344686.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fXQRpW0W-1590849282685)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200413105415797.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ldNtzLwe-1590849282687)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200413105444848.png)]

Limit的对效率的影响分析

当数据量很大并且起始行号较小时，使用limit能够避免全表扫描而提高查询效率；当数据量很大量并且起始行号较大时，仅用limit来限制提取行数会导致效率低下。

二、Union的用法

Union的应用场景 : 合并两个班级的学生信息查询结果

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xxCkNbIH-1590849282691)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200413105721177.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BlPZCJPb-1590849282693)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200413105802539.png)]

七、轻松搞定MySQL函数

7.1聚合函数

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-pvWNSgQP-1590849282694)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200415094451023.png)]

7.2字符串函数

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8SvlJxEM-1590849282696)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200415094609367.png)]

7.3数学函数

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-30jbDbUM-1590849282698)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200415094659525.png)]

7.4日期和时间函数

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-1VOpo8ZG-1590849282701)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200415094825763.png)]

7.5其他常用函数

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dGl6KXGz-1590849282704)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200415094954562.png)]

八、特殊字符序的实战经验

8.1特殊字符：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-a41An0iN-1590849282707)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200415095223912.png)]

查询实例

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0gRrFaxL-1590849282709)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200415095318015.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-PJKDJ8ZN-1590849282711)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200415095414715.png)]

将查询结果生成新表：

create table 表名查询语句；

可使用union将连接两个表的查询结果,若前面表查询时使用了别名，之后联合生成的新表会使用此别名。

8.2分页显示：

select * from person limit 5 offset 10;

limit限制显示条数，offset设置从第几条开始显示，显示第10-14条记录

8.2.1定义分页存储过程：

drop procedure if exists getpage;

delimiter //

create procedure getPage(recCnt int,pgNo int)

Begin

declare pgIdx int;

set pgIdx=recCnt*(pgNo-1);

select * from person limit recCnt offset pgIdx;

end //

recCnt 每页记录条数，pgIdx 开始记录条数【从0开始】/页码，pgNo表示条码【从1开始】

delimiter // 设置为以 / 为结束标记

delimiter ; 改回分隔符为 ;

8.2.2调用分页存储过程getPage(每页条数，页码)

call getPage(10,1);

call getPage(10,2);

九、嵌套查询/子查询

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-FMvesV2j-1590849282713)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200416082301308.png)]

9.1使用关系运算符与IN的子查询

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RSZVEuTy-1590849282716)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200416082447825.png)]

要点：

内查询和外查询必须使用意义相同的列来构造条件。

如果子查询只有一个返回值，外查询可以使用关系运算符或IN来构造条件；若返回值有多个，则只能使用IN来构造条件。

9.2 带有any、some、all的子查询

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7mDaMaNr-1590849282719)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200416084225801.png)]

9.3 带有exists关键字的子查询

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-V1Sp1RDc-1590849282721)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200416084317012.png)]

9.4 在数据操作中使用子查询

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-C9XyeIOp-1590849282722)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200416095305911.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-OVLZvzSu-1590849282724)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200416095424247.png)]

十、视图

10.1创建视图

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lJrNOxNZ-1590849282726)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200416095602212.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-EEZEW887-1590849282728)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200416095626211.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-oRVHxQJj-1590849282730)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200416095751665.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mri8jx92-1590849282733)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200416095839646.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ZeSFXvII-1590849282736)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200416095934907.png)]

创建视图

l 视图不仅可以从一个基表导出，还可以从多张基表导出来；

l 并且还可以在已有的视图基础上导出新的视图；

l 因此可以将视图的创建分为三种情况：在单表中创建视图，在多表中创建视图，在已有视图中创建新的视图。

单表：
create or replace view Stu_viewA(学号,姓名,电话) asselect Student_id, Student_name, Phone from student;
多表：
create view tech_view1 asselect Teacher_id, Teacher_name, Gender, Department_name, Major, Professional from teacher join department on teacher.Department_id= department. Department_id;
已有视图：
create view tech_view2(教工号,姓名,专业) asselect Teacher_id, Teacher_name,Major from tech_view1 where Professional like ‘%教授’ and Department_name=‘艺术传媒学院’;

总结：

l 视图是从一个或者多个表、或其他视图中通过select语句导出的虚拟表；

l 数据库中只存放了视图的定义，而并没有存放视图中的数据；

l 浏览视图时产生的数据均来自定义视图查询所引用的基表，并且在引用视图时动态生成；

l 通过视图可以实现对基表数据的查询或修改。

视图作用：

l **简化数据查询和处理：**视图可以为用户集中多个表中的数据，使用户可以将注意力集中在他们关心的数据上，简化用户对数据的查询和处理；

l **屏蔽数据库的复杂性：**数据库表的更改不影响用户对数据库的使用，用户也不必了解复杂的数据库中的表结构。例如，那些定义了若干张表连接的视图，就将表与表之间的连接操作对用户隐蔽起来了；

l **安全性：**如果想要使用户只能查询或修改用户有权限访问的数据，也可以只授予用户访问视图的权限，而不授予访问表的权限，这样就提高了数据库的安全性。

10.2 查看、修改、更新、删除视图

查看视图

查看视图：查看数据库中已存在的视图的定义。可以查看视图的基本信息、详细信息；

1、查看视图基本信息的方法：describe语句、show table status语句；

2、查看视图详细信息的方法：show create view语句、查询information_schema 数据库下的views表等。

1、查看视图基本信息的方法

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xHzWaMZN-1590849282738)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200416100552680.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5Vpmkr98-1590849282740)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200416100624893.png)]

2.查看视图详细信息

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-XGEJMncH-1590849282742)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200416100655178.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6lBMhyhM-1590849282744)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200416100736332.png)]

修改视图

修改视图：指修改数据库中已存在的表的定义。当基本表的某些字段发生改变时，可以通过修改视图来保持视图和基本表之间一致。

MySQL中通过create or replace view语句和alter语句来修改视图

1.用create or replace view语句来修改视图

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dH8BfP9X-1590849282748)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200416100857990.png)]

2.使用alter语句来修改视图

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bN7BcVFJ-1590849282751)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200416101008113.png)]

alter view tech_view2(教工号,姓名,职称) asselect Teacher_id,Teacher_name,Major from tech_view1 where Professional like '%教授' and (Department_name='艺术传媒学院' or Department_name='信息学院' );

视图数据更新、删除

视图数据更新

更新视图：通过视图来插入、更新、删除表中的数据，因为视图是一个虚拟表，其中没有数据。视图的更新实际上转到基本表上进行更新，即对视图增加或者删除记录，实际上是对其基本表增加或者删除记录。

本节将介绍3种视图更新的方法：insert、update、delete；

1.使用insert语句在视图中插入一条数据

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-V2vV5gPA-1590849282753)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200416101346260.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fXBucIPf-1590849282760)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200416101417741.png)]

2.使用update语句更新视图数据

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-AAdsQkXF-1590849282763)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200416101503039.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fmRNA7xR-1590849282770)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200416101607139.png)]

结论：

1、视图若只依赖于一个基表，则可以直接通过视图来更新基本表数据；

2、视图若依赖于多张基表，则一次只能修改一个基表的数据，不能同时修改多个基表的数据。

3.使用delete语句删除视图一条数据

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ToepHb2p-1590849282772)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200416101814366.png)]

注意：当视图中包含如下内容时，视图更新操作将不会被执行：

1、视图中不包含基本表中被定义为非空的列；

2、在定义视图的select语句后的字段列表中使用了数学表达式；

3、在定义视图的select语句后的字段列表中使用了聚合函数；

4、在定义视图的select语句引用不可更新视图；

5、在定义视图的select语句中使用了distinct、union、top、group by、order by、having 字句

4、删除视图

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ibwhzZKY-1590849282775)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200416101944118.png)]

drop view stu_viewA;

十一、数据库“双雄”之常量与变量

常量：固定数据值、字面量、字面值、标量值

作用：参与运算或给变量赋值

• 常量类型：5种

– 字符串型

– 数值型

– 日期时间型

– 布尔值

– NULL

• 常量表示方法：因常量类型而不同

1.字符串常量：

用成对单引号“‘”或双引号“””括起来的字符序列。

MySQL推荐用单引号表示（区别于其它语言的字符串）。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-al14u9qf-1590849282778)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200418102642757.png)]

2.数值型常量

• 二进制常量如：b’1011’ 、B’1011’。

• 十进制常量，最常用，直接书写，不需要定界符，如：

3，-5，+3.14，123.7E-2

（123.7E-2表示123.7×10-2，其中E前须有数字，E后须为整数）

• 十六进制常量，需要使用前缀和单引号表示：

只加前缀：0x41、0x4d7953514c

结合前缀和单引号：x’41’ 、X’41’、x’4d7953514c’

3.日期时间常量

用单引号括起来，是有特殊格式的字符串。

格式：‘年-月-日[ 时:分:秒[.微秒] ] ‘

如：‘2019-1-24 10:20:30.3000’

• 年份取值为1000-9999，月份取值1-12，日期取值1-31

• 方括号“[ ]” 表示可选项，具体值中不能出现方括号字符

• 省略“时:分:秒[.微秒]”只表示日期，可以精确到微秒级别

• 给TimeStamp列字段或变量赋值时会根据所处时区自动转换，而且会忽略微秒部分，并要求年份只能取值1970-2037。

• “年-月-日”之间的分隔符“-”可以换为“\”、 “@”、 “%”等

4.布尔值和NULL常量

• 布尔值只能表示True或False。其中False对应数值0，True对应数值1。

NULL • 用于表示“未知”、“待定” 、“没有值”、“无数据”等意义，是一种无类型的值，但不同于0或空字符串的含义。

常量小结

• 常量是一个固定值，用于运算或为变量赋值。

• 分字符串常量、数值常量、日期时间常量、布尔值以及NULL。

• 不同类型常量的表示方法不同。

• 十进制常量、布尔值以及NULL常量，无需前缀也不需定界符，

其它常量需要前缀、定界符或2者结合才能表示：

– 十六进制：“0x”前缀

– 字符串和日期时间常量：单引号

– 字符串常量：双引号定界符（不推荐）

– 二进制和十六进制：前缀+单引号 b’Value’、B’Value’、x’Value’、X’Value’

数据库“双雄”之变量

• 变量用于记录或暂时存放某一时段的状态值

如同一场NBA球赛的记分牌，随着比赛的继续，记分牌上记录的得分值随之变化。

MySQL中有2类变量。

一类是像记分牌一样的用户变量。用得最多。要先定义才能使用。定义变量就是确定变量的名称、类型和初始值。
另一类是如同场地广告牌一样的系统变量，MySQL已经定义好了，用户直接使用即可。

1.用户变量

根据使用范围分 会话用户变量和局部变量。

• 客户端与MySQL服务建立连接后，可以定义会话用户变量。
• 在用户断开连接之前，会话用户变量一直起作用，但也只有该用户的本次连接可用；连接断开时会自动释放会话用户变量。
• 局部变量只在一段封装的存储程序内部起作用。在存储程序调用结束释放局部变量

2.会话用户变量定义

形式1：

SET @var1 = 值1 [, @var2 = 值2, … ]

形式2：

SELECT 值1 INTO @var1 [ ,值2 INTO @var2, … ]

形式3：

SELECT @var1 :=值1 [ , @var2 :=值2, … ]

需要注意的是以下几点：

• 变量名@var1、@var2等必须以1个“@”开头，可以由当前字符集的**文字、数字、"."、“_”和“$”**等字符组成。

当变量名中需要包含了一些特殊符号（如空格、#等）时，可以用双引号、单引号或反引号（也叫勾号）“`”将整个变量名括起来。

• “:=”是赋值运算符，会产生结果集，在存储函数中不能用来定义会话用户变量。

• 值1、值2、… 可以是表达式或常量值。

• 会话用户变量的赋值或设置，其形式与定义时完全相同。

• 调用存储过程或存储函数时的实际参数如果是第一次写成“@var”形式，其实质也是定义会话用户变量。

3.局部变量定义

DECLARE var1 [, var2] … 数据类型 [DEFAULT 默认值]

作用：定义1 ~ n个指定类型的局部变量，并设置初始值。

值得注意的是以下几点：

• 局部变量的名称不可以用“@”开头。

• 缺省“[DEFAULT 默认值]” ，则初始值为NULL。

• “默认值”可以是表达式或常量。

• DECLARE只能用于BEGIN…END的开头部分定义局部变量，作用范围：只能在该BEGIN…END中使用。

• 存储函数和存储过程的形式参数也属于局部变量。

4.全局系统变量

生存期和作用范围不同

• 启动时MySQL根据系统配置定义全局系统变量并初始化为默认值（约393个全局系统变量）。

• 生存期从MySQL服务启动时起，直到MySQL服务停止或重启为止。

• 作用范围是整个系统，其值改变会影响整个系统。

5.会话系统变量

• 生存期从客户端连接MySQL服务开始，到连接断开为止。

连接开始时根据该瞬间的全局系统变量名称、类型和值复制创建专属于本连接的系统会话变量（约393个）。
此外，还会创建专属于本连接但不同于全局系统变量的其它变量（约12个系统会话变量不同于全局系统变量）。
会话系统变量的作用范围仅仅局限于本连接。用户更改专属于本连接的会话系统变量的值，只会影响到本连接的运行状态。

6.查看系统变量

SHOW [ GLOBAL | SESSION | LOCAL] VARIABLES [LIKE 模式字符串]

其中：

• GLOBAL限定查看全局系统变量，否则默认查看会话系统变量。

• “[LIKE 模式字符串]”查看特定系统变量，否则查看所有系统变量。

• 模式字符串中用“%”通配符表示0~多个字符。

7.关于变量名称

需要特别注意的是：

• 系统变量名多数都以2个“@”开头

• 用户会话变量必须以1个“@”开头

• 局部变量则不能以“@”开头

8.SET设置变量值

1.设置所有类型的变量值：

SET [ GLOBAL | [ SESSION | LOACAL| @@ | @ ]变量名1 = 值1

2.设置用户变量值：

SELECT 值1 INTO [@]变量名1 [ , 值2 INTO [@]变量名2, … ]
SELECT [@ ] 变量名1 :=值1 [, @ ] 变量名2 :=值2, … ]

需要特别指出的是以下几点：

• 全局系统变量需要用**GLOBAL或@@global.**特别指出。

• LOCAL和SESSION是同义词。

• 会话系统变量需要用SESSION、LOCAL、@@session.、@@local.或@@前缀。

• 会话用户变量需要用“@”字符。

• 整个可选项[@@ global.|@@session. | @@local. |@@|@] 都缺省则改变局部变量的值。

• 可以用DEFAULT代替相应的“值”来恢复系统变量的默认值

十二、运算符、表达式

表达式组成：常量、变量、运算符、函数等。

灵活构建和求解含多个运算符的表达式： ↑查询、计算和赋值， ↑判断选择、循环。

MySQL运算符种类

按功能分4类：

• 算术运算符

• 比较运算符

• 逻辑运算符

• 位运算符。

1、算术运算符

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7zHu8xwK-1590849282781)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200418105438656.png)]

• 表达式中不能省略任何一个运算符

• +和-还可以表示取原数和取相反数运算

• 注意乘法运算符是*不是数学里面的×，除法运算符是/不是数学里面的÷

• 模运算符%和MOD，表示求取2个整数整除后的余数，如： 13 % 5 和 13 MOD 5 结果为 3

• 整除运算符 DIV，表示求取2个整数相除后商的整数部分，如： 13 DIV 3 结果为 4

• +和-还可以用来计算日期，如： ‘2019-06-19 22:55:02’ + interval 22 day

#结果为：'2019-07-11 22:55:02’

• 算术表达式中的字符串会自动转换为数字进行运算。

2、比较运算符

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-V9BIpq6f-1590849282786)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200418105632302.png)]

• 大于或等于运算符是>=不是数学里面的≥，小于或等于运算符是<=不是数学里面的≤

• 不等于运算符是!=或者<>，不是数学里面的≠

• 相等或都为NULL运算符是 <=> ，当2个值相等或都是NULL时成立。

• NULL参加的比较运算，除了“<=>”和 IS [NOT] NULL外，结果都是NULL。

• 字符串比较时（除非用BINARY关键字），默认不区分大小写，还要先截掉字符串尾部所有的空格字符，再进行比较。

• [NOT] BETWEEN、 [NOT] IN、[NOT] LIKE、[NOT] REGEXP、IS [NOT] NULL 也被称为比较运算符。

3、逻辑运算符

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GikeYFQK-1590849282788)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200418105811163.png)]

• 逻辑非 !、NOT运算是对指定条件的否定，结果与指定条件相反。

• 除逻辑或 || 、OR外，NULL参与的逻辑运算结果一般都是为NULL。

• 逻辑异或XOR也叫排斥或，要求2个指定条件结果一样才成立。

• 判断选值运算符case也对多个条件进行运算，并根据条件的测试结果获取不同的结果，如下：

case

when 条件1 then 表达式1

when 条件2 then 表达式2

…

else 表达式n

end

4、位运算符

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RvH54ud7-1590849282790)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200418110004291.png)][外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jCDnSyDi-1590849282792)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200418110030567.png)]

运算符优先级

• 只需要1个数据就可以运算的是一元运算符

• 表达式中可能会出现一些强调或补充说明的关键字

• 关键字和一元运算符！、+、-、~ 优先级最高 （如表中的1、2、3、4优先级）

• 赋值运算符=、:=优先级最低

• 一般来说，各类运算符的优先次序如下： 算术运算 > 位运算 > 比较运算 > 逻辑运算

• 构建表达式时，为了保证准确性，尽量使用小括号“( )”保证自己的设计意图与系统的实际求解顺序相一致。

十三、条件结构

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ubHFcMQs-1590849282794)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200418110330124.png)]

1、IF 条件语句

IF语句说明

• 去除了全部任选项就实现单项选择；

• 仅仅保留最后一个可选项“ELSE Sn+1;”则可以实现二选一。

• 多项选择时需要加上相应的：

“ELSEIF 对应条件 THEN 对应选项; ” 并决定是否选用任选项：“ELSE Sn+1;”。

2、case语句

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-MgRvQWNl-1590849282796)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200418113300023.png)][外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9y0xktPf-1590849282798)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200418113300023.png)]

1、常用Case语句说明

• 去除了全部任选项就实现单项选择；

• 仅仅保留最后一个可选项“ELSE Sn+1;”则可以实现二选一。

• 多项选择时需要加上相应的：

“WHEN 对应条件 THEN 对应选项; ” 并决定是否选用任选项：“ELSE Sn+1;”。

2、简化CASE 语句说明

• 必选项、任选项等的使用与IF 条件语句相同。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TMHbSQvc-1590849282800)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200418113535399.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-MyVN2PVY-1590849282803)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200418113555609.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TFUlfODD-1590849282805)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200418113456399.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BFk9yIAZ-1590849282807)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200418113630624.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-1nCeC5ez-1590849282809)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200418113649533.png)]

• 无论是if语句还是case语句，都可以实现各种选择。

• 相比之下，case比if语句更加灵活，在设计多项选择时尤其简单明了。

十四、循环结构

3种循环语句：WHILE、REPEAT和LOOP

1.WHILE循环语句

语法格式：

[循环语句标签:] while 循环条件 do 循环语句 ;

# 可包含 leave[循环语句标签] 和 iterate语句

end while [循环语句标签] ;

功能：当循环条件成立时，一遍又一遍反复执行循环体，直到条件不成立为止。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tZItpq7q-1590849282811)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200419102046529.png)]

需要注意的是以下几点：

• while之前的 “[循环语句标签:]”和end while后的“[循环语句标签]”一致。

• end while后以“;”结束。

• “循环语句 ;”可以是1条或多条MySQL语句，可包含leave和iterate语句。

• iterate语句作用：

提前结束当前这一次循环体的执行。

此时还在循环中，若循环条件还成立，会再次从头执行循环体，直到条件不成立时循环语句才正常结束。

• leave语句用于在循环条件仍然成立时就结束循环语句，此时已经跳出循环外，提前终结本循环。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ks8fv14Q-1590849282813)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200419102230748.png)]

2.LOOP循环结构

语法格式：

[循环语句标签:] LOOP 循环语句 ;

#可包含iterate语句

IF 结束条件 THEN

LEAVE [循环语句标签] ;

END IF

END LOOP [循环语句标签] ;

功能：当结束条件不成立时，一遍又一遍反复执行循环体，直到结束条件成立为止。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-amHbFUJM-1590849282817)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200419103210938.png)]

注意事项

• LOOP之前的 “[循环语句标签:]”和END LOOP 之后的“[循环语句标签]”必须一致。

• END LOOP后必须以“;”结束。

• “循环语句 ;”可以是1条或多条MySQL语句，可包含iterate语句。

• LOOP循环本身没有结束机制，必须使用LEAVE方可终止循环，而LEAVE一般配合IF语句使用

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rkLy5yVg-1590849282819)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200419103347223.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CidavzKw-1590849282821)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200419103428945.png)]

3.REPEAT循环结构

语法格式：

[循环语句标签:] REPEAT 循环语句;

可包含ITERATE和LEAVE 语句

UNTIL 结束条件

END REPEAT [循环语句标签] ;

功能：当结束条件不成立时，一遍又一遍反复执行循环体，直到结束条件成立为止。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QYMs58BB-1590849282823)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200419103628207.png)]

REPEAT循环注意事项

• REPEAT之前的可选项“[循环语句标签:]”和 END REPEAT之后的“[循环语句标签]”必须一致。

• END REPEAT后必须以“;”结束。

• “循环语句 ;”可以是1条或多条MySQL语句，可包含 ITERATE和LEAVE语句。

• REPEAT循环本身就带有结束循环的机制，LEAVE语句不是必须的

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rkGBmI4c-1590849282824)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200419103728495.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ms8PFKme-1590849282826)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200419103745961.png)]

4.循环+游标

(1)游标使用步骤

游标（Cursor）使用4步骤：定义、打开、取数据和关闭。

①游标的定义，也叫游标声明：确定需查询的数据、指明数据的“查询要求” ：

declare 游标名 cursor for select查询语句;

②游标的打开：

**open **游标名;

执行游标的“select查询语句”，将结果存放到服务器内存中

③从游标中取数据

fetch 游标名 into 变量1 [, 变量2, … ];

取结果集中一条记录，存入into子句的1至多个变量中。

④关闭游标：

close 游标名;

释放查询结果占用的内存空间。

注：若游标未关闭，在其所属begin-end末尾也会自动关闭。

(2)用游标注意事项

• 执行1次fetch命令只能取到1条记录

• 1次select查询往往都有多条记录，要执行多次fetch。

如何用循环来控制fetch的执行次数呢？

方法1：根据结果集的条数确定。
方法2：用fetch取数据，

取完了、无法再取时就会出错并停止fetch。

用游标逐条处理数据

1.方法1的实现思路：

①先统计出符合查询条件的记录总数n，

②用计数器m记下当前fetch命令执行的次数。

当前fetch执行次数m超过记录总数时表示查询结果已经取完，停止执行循环体中的fetch。所以“m <= n” 就是循环执行的先决条件

(3)游标+循环举例

【例1】结合游标和循环，请查询给定学号的学生已经取得的总学分。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-pQODq217-1590849282828)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200419104318168.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2D4HCcid-1590849282830)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200419104353375.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-IXwcIt5m-1590849282832)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200419104423735.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QdJI4eGg-1590849282834)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200419104500358.png)]

十五、基于PHP的MYSQL web应用

PHP是Web编程语言

一、PHP的简明工作流程

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tkZwYxYe-1590849282836)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200501225000256.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UUEACE8i-1590849282838)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200501225101098.png)]

初识PHP程序

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-k0n4N8Bd-1590849282839)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200501225152356.png)]

总结与提高

PHP作为Web设计专用的语言，承担着在逻辑层和数据层通信的信使作用。PHP可以通过相关函数操纵SQL，从而实现在MySQL的世界中攫取数据，以HTML页面的形式将其呈现给网络用户。

二、PHP的MySQL数据库连接

PHP的内置MySQL函数连接数据库
PHP的内置MySQLi函数连接数据库
PHP的PDO方式连接数据库

PHP的内置MySQL函数连接数据库

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RgwPx1hN-1590849282841)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200502085417564.png)]

PHP的内置MySQLi函数连接数据库

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-khHPuxr7-1590849282843)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200502085521333.png)]

mysqli对象名= mysqli_connect(数据库服务器, 用户名, 密码, 数据库名)

MySQLi类中的成员方法

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Vm5HYexk-1590849282845)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200502085701895.png)]

PHP的PDO方式连接数据库

PDO和MySQLi都是通过面向对象的形式提供API，但是MySQLi同时也提供了面向过程的API。
用PDO的好处是，PDO支持多种数据库，而MySQLi只支持MySQL，一但掌握了PDO就可以随心所欲地使用它连接多种数据库。

三、使用PHP实现MySQL数据库级操作

利用PHP查询数据库
利用PHP创建数据库
利用PHP删除数据库

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0GyfOeeX-1590849282846)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200502090218997.png)]

利用PHP查询数据库

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TyPHxVNO-1590849282848)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200502090343878.png)]

PDO和MySQLi都是通过面向对象的形式提供API，但是MySQLi同时也提供了面向过程的API。

PHP对MySQL数据表级别的操作

解决利用PHP操作MySQL数据表的前提，是沿着如下思路进行的：

连接服务器—>创建数据库—>连接数据库—>创建数据表

解决问题的具体技术方案可以通过内置函数、MySQLi扩展、PDO等方式完成。

本节案例均采用MySQLi面向对象的方式实现：

模板化操作流程：建立数据库连接->执行SQL命令形成结果集->取回结果

将取回的结果显示页面取出数据有四种方式（assoc、row、object、array）

我们一般使用assoc这种方式
查询操作，返回的是一个对象。而插入，删除，修改操作，返回的是一个布尔值，但是我们并不知道表里面有没有变化。可以用$mysqli -> affected_rows，mysqli里面的属性进行判断，返回的结果是sql语句对数据表的影响行数。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-AT0DAjtp-1590849282850)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200502090717332.png)]

插入、修改、删除，同属于DML语句，其操作方法相同，可以采用字串连接复合赋值运算.=的方式组成SQL命令，利用mysqli的multi_query()方法批量执行。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yg36uYqs-1590849282852)(…/…/…/…/AppData/Roaming/Typora/typora-user-images/image-20200502090818150.png)]

十六、存储程序（一）

安装phpMyAdmin

Thank you for downloading phpMyAdmin

Your download should start soon, if not please click here.

Please verify the downloaded file

Please take additional steps to verify that the file you have downloaded is not corrupted, you can verify it using the following methods:

Verify its PGP signature, see the Verifying phpMyAdmin releases chapter for more information.
Check that the file’s SHA256 hash matches a6ea4f16ced9f2ce3a9b23d47c1cd0fee5a46ead9bd5f9ec76f8edfb7a153edc

phpMyAdmin needs your continued support to grow and thrive

phpMyAdmin would not exist without the work of many volunteers and contractors. You can support us to make phpMyAdmin even better by donating to our project. Every donation counts!

We have also a sponsorship program for corporates who are willing to spent more money and get some benefits such as a logo placement in return.

安装教程

https://blog.csdn.net/ggrjake25/article/details/86512588

位置访问网址 $cfg[‘PmaAbsoluteUri’] = ‘http://localhost/phpMyAdmin/’;

$cfg[‘Servers’][$i][‘host’] = ‘localhost’;

$cfg[‘Servers’][$i][‘port’] = ‘’;

$cfg[‘Servers’][$i][‘user’] = ‘root’;

$cfg[‘Servers’][$i][‘password’] = ‘用户密码’;

认证方法 $cfg[‘Servers’][$i][‘auth_type’] = ‘cookie’;

短语密码（blowfish_secret） $cfg[‘blowfish_secret’] = ‘php’;

认证方法

$cfg[‘Servers’][$i][‘auth_type’] = ‘cookie’;考虑到安全的因素，建议这里填写 cookie。在此有四种模式可供选择，cookie，http，HTTP，config

config 方式即输入 phpMyAdmin 的访问网址即可直接进入，无需输入用户名和密码，是不安全的，不推荐使用。

当该项设置为 cookie，http 或 HTTP 时，登录 phpMyAdmin 需要数据用户名和密码进行验证，具体如下：

PHP 安装模式为 Apache，可以使用 http 和 cookie；

PHP 安装模式为 CGI，可以使用 cookie。

短语密码（blowfish_secret）的设置

dmin.net/phpMyAdmin/5.0.2/phpMyAdmin-5.0.2-all-languages.zip).

Please verify the downloaded file

Please take additional steps to verify that the file you have downloaded is not corrupted, you can verify it using the following methods:

Verify its PGP signature, see the Verifying phpMyAdmin releases chapter for more information.
Check that the file’s SHA256 hash matches a6ea4f16ced9f2ce3a9b23d47c1cd0fee5a46ead9bd5f9ec76f8edfb7a153edc

phpMyAdmin needs your continued support to grow and thrive

phpMyAdmin would not exist without the work of many volunteers and contractors. You can support us to make phpMyAdmin even better by donating to our project. Every donation counts!

We have also a sponsorship program for corporates who are willing to spent more money and get some benefits such as a logo placement in return.