MYSQL

作者: dptms | 来源:发表于2022-11-07 16:50 被阅读0次

概念

数据库是计算机应用系统中的一种专门管理数据资源的系统。数据库就是一组经过计算机整理后的数据，存储在一个或者多个文件中，而管理这个数据库的软件就称为数据库管理系统。

主流的软件开发中应用数据库有Oracle，SQL Server，PostgreSQL，MySQL等。

数据库在Web开发中的重要地位

动态网站都是对数据进行操作，我们平时浏览网页时，会发现网页的内容会经常变化，而页面的主体结构框架没变，Web系统的开发基本上都离不开数据库，因为任何东西都要存放在数据库中。

所谓的动态网站就是基于数据库开发的系统，最重要的就是数据管理，或者说我们在开发时都是在围绕数据库在写程序。我们再来回顾下Web工作原理：

Web工作原理

环境准备

PHPstudy

image.png

环境变量

win 键搜索编辑系统环境变量点击进入

image.png
依次如图，将 MYSQL 加入环境变量中去

image.png

命令行连接

win + R 键输入cmd 呼出命令行窗口
image.png
测试 MYSQL 是否能正确运行输入 mysql -V
image.png
连接数据库输入 mysql -uroot -p 回车并输入密码

mysql –h 服务器主机地址 –u 用户名 –p 用户密码

image.png

认识 SQL

对数据库服务器中数据的管理，必须使用客户机程序成功连接以后，再通过必要的操作指令对其进行操作，这种数据库操作指令被称为SQL（Structured Query Language）语言，即结构化查询语言。

MySQL支持SQL作为自己的数据库语言，SQL是一种专门用于查询和修改数据库里的数据，以及对数据库进行管理和维护的标准化语言。

SQL语言结构简洁，功能强大，简单易学，所以自从IBM公司1981年退出以来，SQL语言得到了广泛的应用。

无论是Oracle，Sybase，Informix，SQL Server这些大型的数据库管理系统，还是像Visual Foxpro，PowerBuilder这些PC上常用的数据库开发系统，都支持SQL语言作为查询语言。

创建数据库

建立数据库：

   CREATE DATABASE [IF NOT EXISTS] 数据库名称；

删除数据库：

   DROP DATABASE [IF EXISTS] 数据库名称；

显示当前数据库服务器下的所有数据库列表：

   SHOW DATABASES;

选择数据库：

   USE 数据库名称；

注意：
1.MySQL数据库中命令不区分大小写。
2.每创建一个数据库，就会在data目录下创建一个以此数据库名称命名的文件夹。
3.在Windows下，数据库名称也是不区分大小写的，但在Linux下，数据库名称严格区分大小写。

数据表概念

数据表是数据库中的基本对象元素，以记录（行）和字段（列）组成的二维结构用于存储数据。数据表由表结构和表内容两部分组成，先建立表结构，然后才能输入数据。数据表结构设计主要包括字段名称、字段类型和字段属性的设置。

通常情况下，同一个数据库中可以有多个数据表，但表名必须是唯一的，表中每一条记录描述了一个相关信息的集合，每一个字段必须为唯一的，每个字段都需要指定数据类型。

数据列四大数据类型

数值类数据列类型

数据类型	存储空间	说明	取值范围
TINYINT	1字节	非常小的整数	带符号值：-128_{127、无符号值：0}255
SMALLINT	2字节	较小的整数	带符号值：-32768_{32767、无符号值：0}65535
MEDIUMINT	3字节	中等大小的整数	带符号值：-8388608_{8388607、无符号值：0}16777215
INT	4字节	标准整数	带符号值：-2147483648_{2147483647、无符号值：0}4294967295
BIGINT	8字节	大整数	带符号值：-263_{263-1、无符号值：0}264-1
FLOAT	4或8字节	单精度浮点数	最小非零值：+- 1.175494351E-38、最大非零值：+- 3.402823466E+38
DOUBLE	8字节	双精度浮点数	最小非零值：+- 2.225073E-308、最大非零值：+- 1.797693E+308
DECIMAL	自定义	以字符串形式表示的浮点数	取决于存储单元字节数

整型注意事项：

INT(3)、SMALLINT（3）等整型后面的数字不会影响数值的存储范围，只会影响显示
整型后面的数字只有配合零填充的时候才有实际意义。
整型后面的数字可以省略

浮点型注意事项：

浮点型后面的数字会将存入的数字四舍五入，例如：把一个1.234存入FLOAT(6,1)数据列中，结果是1.2,6代表显示长度，1代表小数位长度，会四舍五入。

字符串类数据列类型

数据类型	存储空间	说明	取值范围
CHAR[(M)]	M字节	定长字符串	M字节
VARCHAR[(M)]	L+1字节	可变字符串	M字节
TINYBLOB，TINYTEXT	L+1字节	非常小的BLOB(二进制大对象)和文本串	28-1字节
BLOB,TEXT	L+2字节	小BLOB和文本串	216-1字节
MEDIUMBLOB,MEDIUMTEXT	L+3字节	中等的BLOB和文本串	224-1字节
LONGBLOB,LONGTEXT	L+4字节	大BLOB和文本串	232-1字节
ENUM(‘value1’,’value2’…)	1或2字节	枚举：可赋予某个枚举成员	65535个成员
SET(‘value1’，‘value2’…)	1,2,3,4或8字节	集合：可赋予多个集合成员	64个成员

字符串类型注意事项：

CHAR和VARCHAR类型的长度范围都在0~255之间
在使用CHAR和VARCHAR类型时，当我们传入的实际的值的长度大于指定的长度，字符串会被截取至指定长度
在使用CHAR类型时，如果我们传入的值的长度小于指定长度，实际长度会使用空格补至指定长度
在使用VARCHAR类型时，如果我们传入的值的长度小于指定长度，实际长度即为传入字符串的长度，不会使用空格填补
CHAR要比VARCHAR效率更高，当占用空间较大
BLOB和TEXT类型是可以存放任意大数据的数据类型
BLOB区分大小写，TEXT不区分大小写
ENUM和SET类型是特殊的的串类型，其列值必须从固定的串集中选择

ENUM只能选择其中一个值，SET可以选择多个值

日期和时间类数据列类型

数据类型	存储空间	说明	取值范围
DATE	3 字节	“YYYY-MM-DD”格式表示的日期值	1000-01-01~9999-12-31
TIME	3 字节	“hh:mm:ss”格式表示的时间值	-838:59:59-838:59:59
DATETIME	8 字节	“YYYY-MM-DD hh:mm:ss”格式	1000-01-01 00：00：00~9999-12-31
TIMESTAMP	4 字节	“YYYYMMDDhhmmss”格式表示的时间戳	19700101000000-2037年的某个时刻
YEAR	1 字节	“YYYY”格式的年份值	1901~2155

日期类型注意事项：

存储日期时，我们可以使用整型来进行存储时间戳，这样做便于我们进行日期的计算

NULL值

NULL值注意事项：

NULL意味着“没有值”或“未知值”
可以测试某个值是否为NULL
不能对NULL值进行算术计算
对NULL值进行算术运算，其结果还是NULL
0或NULL都意味着假，其余值都意味着真

数据字段属性

UNSIGNED

只能用于设置数值类型，不允许出现负数
最大存储长度会增加一倍

ZEROFILL

只能用于设置数值类型，在数值之前会自动用0补齐不足的位数

AUTO_INCREMENT

用于设置字段的自动增长属性，每增加一条记录，该字段的值会自动加1

NULL和NOT NULL

默认为NULL，即插入值时没有在此字段插入值，默认为NULL值，如果指定了NOT NULL，则必须在插入值时在此字段填入值

DEFAULT

可以通过此属性来指定一个默认值，如果没有在此列添加值，那么默认添加此值

创建索引

在MySQL中，主要有四类索引：

主键索引（PRIMARY KEY）

主键索引是关系数据库中最常见的索引类型，主要作用是确定数据表里一条特定的数据记录的位置。我们可以在字段后添加PRIMARY KEY来对字段设置为主键索引。
注意：
1.最好为每张表指定一个主键，但不是必须指定。
2.一个表只能指定一个主键，而且主键的值不能为空
3.主键可以有多个候选索引（例如NOT NULL，AUTO_INCREMENT）

唯一索引（UNIQUE）

唯一索引与主键索引一样，都可以防止创建重复的值。但是，不同之处在于，每个数据表中只能有一个主键索引，但可以有多个唯一索引。我们使用关键字UNIQUE对字段定义为唯一索引。

常规索引（INDEX）

常规索引技术是关系数据查询中最重要的技术，如果要提升数据库的性能，索引优化是首先应该考虑的，因为它能使我们的数据库得到最大性能方面的提升。常规索引也存在缺点：
1.多占用磁盘空间
2.会减慢插入，删除和修改操作
3.需要按照索引列上排序格式执行
创建索引我们可以使用INDEX和KEY关键字随表一同创建。

全文索引（FULLTEXT）

全文索引在MySQL中是一个FULLTEXT类型索引，但FULLTEXT索引只能用于MyISAM表，并且只可以在CHAR、VARCHAR或TEXT类型的列上创建，也允许创建在一个或多个数据列上。
但是FULLTEXT是不支持中文全文索引的，所以我们将来会使用效率更高的全文索引引擎Sphinx。

数据表的存储类型及存储位置

MySQL支持MyISAM、InnoDB、HEAP、BOB、ARCHIVE、CSV等多种数据表类型，在创建一个新MySQL数据表时，可以为它设置一个类型。

MyISAM和InnoDB两种表类型最为重要：

1.MyISAM数据表类型的特点是成熟、稳定和易于管理。
2.MyISAM表类型会产生碎片空间，要经常使用OPTIMIZE TABLE命令去清理表空间
3.MyISAM不支持事务处理，InnoDB支持
4.MyISAM不支持外键，InnoDB支持
5.MyISAM表类型的数据表效率更高
6.MyISAM表类型的数据表会产生三个文件，InnoDB表类型表默认只会产生一个文件。

字符集

创建表的时候，如果没有明确地指定任何字符集，则新创建数据表的字符集将由MySQL配置文件里charcter-set-server选项的设置决定。

在创建数据表时如果需要指定默认的字符集与之相同，但MySQL客户程序在与服务器通信时使用的字符集，我们需要使用default-character-set选项或通过SQL命令SET NAMES utf8来指定一个字符集为utf8.

创建表

创建数据表之前，我们应该注意：

1.创建数据库（如已存在则不需要创建）
2.选择数据库
3.在该数据库当中创建数据表

创建数据表需要注意：

1.指定数据表的名称（数据表不能重名）
2.指定该表的字段名称、字段数据类型、字段索引
3.指定表类型和表默认字符集（可省略）

建表实例

中文名	字段名	数据类型	属性	索引
用户编号	id	INT	UNSIGNED NOT NULL AUTO_INCREMENT	主键
用户名称	username	VARCHAR(50)	NOT NULL	普通
口令	userpass	VARCHAR(50)	NOT NULL	普通
联系电话	telno	VARCHAR(20)	NOT NULL	唯一
性别	sex	ENUM(‘男’,’女’)	NOT NULL DEFAULT ‘男’
出生日期	birthday	DATE	NOT NULL DEFAULT ‘0000-00-00’

CREATE TABLE IF NOT EXISTS `users`(
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `username` VARCHAR(50) NOT NULL,
    `userpass` VARCHAR(50) NOT NULL,
    `telno` VARCHAR(20) NOT NULL UNIQUE,
    `sex` ENUM('nan','nv') NOT NULL DEFAULT 'nan',
    `birthday` DATE NOT NULL DEFAULT '0000-00-00',
    PRIMARY KEY(`id`),
    INDEX username_index(`username`),
    INDEX userpass_index(`userpass`)
)ENGINE=MyISAM DEFAULT CHARSET=utf8 collate utf8_general_ci ;

注意事项：

1.表的字段之间要使用逗号隔开。
2.建表的最后一句一定不能有逗号。
3.表名称和字段名称尽量不要使用MySQL系统的关键字
4.如果一定要使用关键字，我们可以使用反引号将表名称和字段名称包含起来来进行过滤屏蔽。
5.使用反引号会使建表效率增高。
6.数据表名称和字段名称不能重名
7.AUTO_INCREMENT属性必须依附于主键索引或唯一索引

修改数据表

修改表的语法：

ALTER TABLE 表名 ACTION;

我们可以对表进行修改字段，添加字段，删除字段，添加索引，删除索引，更改表名称，更改字段名称，更改auto_increment属性的初始值等。

修改字段：

我们使用change或者是modify关键字

ALTER TABLE `uses` CHANGE `username` `uname` VARCHAR(32) NOT NULL;

ALTER TABLE `users` MODIFY `username` VARCHAR(32) NOT NULL;

由上例可以发现：
change可以改变字段名称，而modify不可以

添加字段：

我们使用add关键字

ALTER TABLE `uses` ADD `tname` VARCHAR(32) NOT NULL;

这样我们就会新增一个tname字段。

删除字段：

我们使用drop关键字

ALTER TABLE `users` DROP `tname`;

这样我们会删除tname字段

添加索引：

我们使用add关键字

ALTER TABLE `uses` ADD INDEX/UNIQUE/PRIMARY KEY 索引名称(字段名称)

这样会在该字段上建立索引（普通索引，唯一索引，主键索引）。

删除索引：

ALTER TABLE `users` DROP INDEX/UNIQUE/PRIMARY KEY  索引名称;

这样我们会删除这个索引，我们可以使用show indexes from 表名查看当前表索引。

更改表名称：

我们使用rename关键字

ALTER TABLE 旧表名 RENAME AS 新表名

将旧表名更改为新表名

更改AUTO_INCREMENT初始值:

ALTER TABLE 表名称 AUTO_INCREMENT=1

将AUTO_INCREMENT的初始值设置为1

删除表：

DROP TABLE [IF EXISTS] 表名称；

操作数据表中的数据记录（DML语句）

使用INSERT语句向数据表中添加数据

插入数据是向已经存在的数据表中添加一条新的记录，应该使用INSERT INTO语句。
格式：

INSERT INTO 表名 [(字段名1,字段名2,…字段名n)] VALUES(‘值1’,‘值2’,…,’值n’);

或者

INSERT INTO 表名 [(字段名1,字段名2,…字段名n)] VALUES(‘值1’,‘值2’，…，‘值n’),(‘值1’,‘值2’,…,’值n’);

使用UPDATE语句更新数据表中已存在的数据

SQL语句可以使用UPDATE语句对表中的一列或多列数据进行修改，必须指定需要修改的字段，并且需要赋予的新值。还要给出必要的WHERE子句指定要更新的数据行。
格式：

UPDATE 表名 SET 字段名=表达式 [,…][WHERE 条件]
[ORDER BY 字段][LIMIT 行数]

使用DELETE语句删除数据表中不需要的数据记录

DELETE语句用来删除数据表中的一条或多条数据记录。
格式：

DELETE FROM 表名 [WHERE 条件][ORDER BY 字段][LIMIT 行数]

通过DQL命令查询数据表中的数据

查询语句可以完成简单的单表查询，也可以完成复杂的多表查询和嵌套查询。SELECT语句主要用于数据的查询检索，是SQL语言的核心，在SQL语言中SELECT语句的使用频率是最高的。
格式：

SELECT [ALL|DISTINCT]{*|table.*|[table.]field1[AS alias1][,[table.]field2[AS alias2][,…]]}FROM 表名
 [WHERE子句]
[GROUP BY..][HAVING..][ORDER BY..][LIMIT count];

选择特定的字段

最简单的查询语句是使用SELECT语句检索记录的特定字段，多个字段可以用逗号分隔，例如：

SELECT username,password FROM user;

也可以使用*从表中检索出所有字段，使用SELECT *主要是针对用户的书写方便而言的。如果一张表当中的数据多大几百万，就意味着资源的浪费和漫长的查询等待，所以实际应用时要尽量避免使用它，而把查询的列名准确地列出来，也可以按自己指定的列顺序输出。

使用AS子句为字段取别名

如果想为返回的列取一个新的标题，以及经过对字段的计算或总结之后，产生了一个新的值，希望把它放到一个新的列里显示，则用AS保留。例如：在上例的输出结果中使用中文字段名，可以在MySQL控制台中输入的命令如下所示：

SELECT username as '用户名' FROM user;

定义别名时一定要使用单引号引起来，其中AS关键字是可选的，在原字段名和别名之间使用一个空格即可。

SELECT username ‘用户名’ FROM user；

DISTINCT关键字的使用

如果在使用SELECT语句返回的记录结果中包含重复的记录，可以使用DISTINCT关键字取消重复的数据，只返回一个。另外，要注意DISTINCT关键字的作用是整个查询的列表，而不是单独的一列。DISTINCT会消耗一定的服务器资源，如果不指定，会默认使用ALL关键字作为检索模式

SELECT DISTINCT gid FROM user_group；

在SELECT语句中使用表达式的列

在SQL中的表达式用法和PHP程序相似，主要包括算术表达式、逻辑表达式，以及使用SQL函数表达式等。
例如：

SELECT version(),1.23*10;

使用WHERE子句按条件检索

在SELECT语句中，可以使用WHERE子句指定搜索条件，实现从数据表中检索出符合条件的记录。其中，搜索条件可以由一个或多个逻辑表达式组成，这些表达式指定关于某一记录是真或假的条件。在WHERE子句中，可以通过逻辑操作符和比较操作符指定基本的表达式条件。

逻辑操作符

操作符	语法	描述
AND 或 &&	a AND b 或 a && b	逻辑与,若两个操作数同时为真，则为真
OR 或丨丨	a OR b 或 a丨丨b	逻辑或,只要有一个操作数为真，则为真
XOR	a XOR b	逻辑异或,若有且仅有一个操作数为真，则为真
NOT 或！	NOT a 或！a	逻辑非,若操作数为假，则为真

比较操作符

数据列类型	存储空间说明	取值范围
=	a=b	若操作数a与操作数b相等，则为真
<=>	a<=>b	若a与b相等，则为真，可以用于NULL值比较
!=或<>	a!=b或a<>b	若操作数a与b不相等，为真
<	a<b	若操作数a小于b，为真
<=	a<=b	若操作数a大于b，为真
>	a>b	若操作数a大于b，为真
>=	a>=b	若操作数a大于等于b，为真
IS NULL	a IS NULL	若操作数a为NULL，为真
IS NOT NULL	a IS NOT NULL	若操作数a不为NULL，为真
BETWEEN	a BETWEEN b AND c	若a在b和c之间，为真
NOT BETWEEN	a NOT BETWEEN b AND c	若操作数a不在b和c之间，为真
LIKE	a LIKE b	SQL模式匹配，若a匹配b，为真
NOT LIKE	a NOT LIKE b	SQL模式匹配，若a不匹配b，则为真
IN	a IN (b1,b2….)	若a等于b1,b2,b3,…中的某一个，则为真

在构造搜索条件时，要注意只能对数值数据类型的记录进行算术运算，并且只能在相同的数据类型之间进行记录的比较。

根据空值（NULL）确定检索条件

空值只能定义在允许NULL字段中出现，NULL值是特殊的值，代表“无值”，与零值（0）和空字符串（''）都不相同。当在不支持默认值的字段中未输入值，或在字段中显式的设置为空，就会出现空值，但不能用处理已知值的方式来处理NULL。为了进行NULL值的搜索，必须采用特殊的语法。如果要检索NULL值，必须使用IS NULL和IS NOT NULL关键字。

使用BEETWEEN AND进行范围比较查询

如果需要对某个字段通过范围的值进行比较查询，可以使用BETWEEN AND关键字实现，其中AND是多重条件符号，比较时也包括边界条件。也可以使用>=和<=完成同样的功能。

使用IN进行范围比对查询

在WHERE子句中，使用IN关键字并在后面的括号()中提供一个值的列表，以供与相应的字段进行比较。该列表中至少应该存在一个值，如果有多个值可以使用逗号,分隔。

使用LIKE进行模糊查询

在SELECT语句的WHERE子句中，可以使用LIKE关键字对数据表中的记录进行模糊查询，将查询结果锁定在一个范围内。在查询条件中通常会与_和%两个通配符一起使用，可以实现复杂的检索查询。这两个通配符的含义分别如下：

百分号“%”：表示0个或任意多个字符。
下画线“_”：表示单个的任意一个字符。

相反的，不匹配我们使用NOT LIKE

多表查询（连接查询）

多表查询给用户带来很大的灵活性，可以在任何时候增加新的数据类型，为不同实体创建新的表，然后通过连接进行查询。包括两种形式：

非等值和等值的多表查询

多表查询和普通的单表查询相似，都是使用SELECT语句。只不过在多表查询时需要把多张表的名字，全部填写在FROM子句中，并用逗号,将表名分开。同时，也可以对数据表使用别名进行引用。另外，为了在查询时区分多个表中出现的重复字段名，可以在字段列表中使用表名.列名的形式，如果不存在重名的列，可以省略表名。

自身连接查询

连接查询操作不仅可以用于多个表之间，也可以是一个表与其自己进行连接，称为自身连接查询。当一个表所代表的实体之间有关系时，就可以使用自身连接查询。

复合连接查询

前面介绍的多表查询是两个表之间，只有一个WHERE子句查询条件。如果在FROM子句后面有n个表需要查询，则在WHERE子句中就需要有多个连接条件。至少要比出现的表格数量少1个，也就是不能少于n−1个查询条件，多个条件使用AND关键词连接即可。

嵌套查询（子查询）

这种查询是在一个SELECT语句的WHERE子句中，包含另一个SELECT语句，也可以称为子查询。在子查询中只能返回一列，并将形成的结果又作为父查询的条件，在主句中进行进一步查询。 SQL语言允许多层嵌套查询，即一个子查中还可以有其他子查询。嵌套查询的求解方法是由里向外处理，即每个子查询都是在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。

使用ORDER BY对查询结果排序

使用SELECT语句获取数据表中的数据时，返回的记录一般是无规则排列的，有可能每次获取的查询记录截然不同。为了使用检索的结果方便阅读，可以在SELECT语句中使用ORDER BY子句，对检索的结果进行排序。

ORDER BY后面可以接一列或多列用于排序的字段，并且使用DESC或ASC关键字设计字段排序的方式。默认情况下按照升序排列，即使用ASC关键字。否则要按照降序排列，必须使用DESC关键字。ORDER BY子句可以和SELECT语句中的其他子句一起使用，但在子查询中不能有ORDER BY子句，因为ORDER BY子句只能对最终查询结果排序。

使用LIMIT

如果在数据表中的记录数非常多，一次从表中返回大量的记录不仅检索的速度慢，用户阅读也很不方便。所以在通过SELECT语句检索时，使用LIMIT子句一次取少量的记录，而用分页的方式继续阅读后面的数据。

LIMIT子句也可以和其他的SELECT子句一起使用，它可以指定两个参数，分别用以设置返回记录的起始位置，和返回记录的数量。

LIMIT子句也可以只使用一个参数，表示从开头位置，即偏移量为0的位置返回指定数量的记录，在上例中使用的“LIMIT 0, 5”等价于“LIMIT 5”。

使用统计函数

在数据库系统中提供了一系列的内置统计函数，在SQL查询中使用这些统计函数可以更有效地处理数据。这些统计函数把存储在数据库中的数据，描述为一个整体而不是一行行孤立的记录。

统计函数	描述
COUNT()	返回满足SELECT语句中指定条件的记录数，例如，COUNT(*)返回找到的记录行数
SUM()	通常为数值字段或表达列作统计，返回一列的总和
AVG()	通常为数值字段或表达列作统计，返回一列的平均值
MAX()	可以为数值字段、字符字段或表达列作统计，返回一列中最大的值
MIN()	可以为数值字段、字符字段或表达列统计，返回一列中最小的值

这些函数通常用在SELECT子句中，作为结果数据集的字段返回的结果。在SELECT语句的SELECT子句中使用函数的语法如下：

SELECT 函数名（列名1 或*），...函数名（列名n） FROM 表名;

使用GROUP BY对查询结果分组

前面使用统计函数返回的是所有记录的统计结果，如果要对数据进行分组统计，就需要使用GROUP BY子句。这将可以允许用户在对数据进行分类的基础上，进行再查询。GROUP BY子句将表按列值分组，列的值相同的分为一组。如果GROUP BY后面有多个列名，则先按第一个列名分组，再在每组中按第二个列名分组。

需要注意的是，在GROUP BY子句中不支持对字段分配别名，也不支持任何使用了统计函数的集合列。

在完成数据结果的分组查询和统计后，还可以使用HAVING子句来对查询的结果，进行进一步的筛选。

在SELECT语句的子句中：WHERE子句选择所需要的行；GROUP BY子句进行了必要的分组整理；而HAVING子句对最后的分组结果进行了重新筛选。