MySQL数据库

PHP 同时被 2 个专栏收录
1 篇文章 0 订阅
15 篇文章 0 订阅

数据库作为程序中数据的主要载体,在整个项目中扮演着重要的角色。PHP自身可以与大多数数据库进行连接,但MySQL数据库树开源界所公认的与PHP结合最好的数据库,它具有安全、跨平台、体积小和高效等特点,可谓PHP的“黄金搭档”。

MySQL简介

PHP在开发Web站点或一些管理系统时,需要对大量的数据进行保存。XML文件和文本文件虽然可以作为数据的载体,但不易进行管理和对大量数据的存储,所以在项目开发时,数据库就显得非常重要。PHP可以连接的数据库种类很多,其中MySQL数据库与其兼容较好,在PHP数据库开发中被广泛地应用。

什么是MySQL

MySQL是一款安全、跨平台、高效的,并与PHP、Java等主流编程语言紧密结合的数据库系统。该数据库系统是由瑞典的MySQLAB公司开发、发布并支持,由MyCQL的初始开发人员David Axmark和Michael Monty Widenius于1995年建立的。MySQL的象征符符号是一只名为Saklia的海豚,代表着MySQL数据库的速度、能力、精确和优秀本质。
目前,MySQL被广泛地应用在Internet上的中小型网站中。由于体积小、速度快、总体拥有成本低,尤其是开放源码的这一特点,很多公司都采用MySQL数据库以降低成本。
MySQL数据库可以称得上是目前运行速度最快的SQL语言数据库之一。除了具有许多其它数据库所不具备的功能外,MySQL数据库还是一种完全免费的产品。用户可以直接通过网络下载MySQL数据库,而不必支付任何费用。

MySQL特点

MySQL具有以下主要的特点:

  • 功能强大:MySQL中提供了多种数据库存储引擎,各引擎各有所长,适用于不同的应用场合。用户可以选择最合适的引擎以得到最高性能,以处理每天访问量超过数亿的高强度的搜索web站点。MySQL 5支持事务、视图、存储过程、触发器等。
  • 支持跨平台:MySQL支持20种以上的开发平台,包括Linux、Windows、FreeBSD、IBMAIX、AIX等。这使得在任何平台下编写的程序都可以进行移植,而不需要对程序做任何的修改。
  • 运行速度快:高速是MySQL的显著特性。MySQL中使用了极快的B树磁盘表(MyISAM)和索引压缩;通过使用优化的单扫描多连接,能够快速地实现连接;SQL函数使用高度优化的类库实现,运行速度极快。
  • 支持面向对象:PHP支持混合编程方式。编程方式可分为纯粹面向对象、纯粹面向过程、面向对象与面向过程混合3种方式。
  • 安全性高:灵活和安全的权限与密码系统,允许基本主机的验证。连接到服务器时,所有的密码传输均采用加密形式,从而保证了密码的安全。
  • 成本低:MySQL数据库是一种完全免费的产品,用户可以直接通过网络下载。
  • 支持各种开发语言:MySQL为各种流行的程序设计语言提供了支持,为它们提供了很多API函数,包括PHP、ASP.NET、Java、Eiffel、Python、Ruby、Tcl、C、C++、Perl语言等。
  • 数据库存储容量大:MySQL数据库的最大有效表尺寸通常是由操作系统对文件大小限制决定的,而不是由MySQL内部限制决定的。InnoDB存储引擎将InnoDB表保存在一个表空间内。该表空间可由数个文件创建。表空间的最大容量为64TB,可以轻松处理拥有上千万条记录的大型数据库。
  • 支持强大的内置函数:PHP中提供了大量的内置函数,几乎涵盖了Web应用开发的所有功能。它内置了数据库的连接、文件的上传等功能。MySQL支持大量的扩展库,如MySQLi等,可以快速开发Web应用提供了便利。

MySQL 5支持的特性

MySQL 5已经是一个非常成熟的企业级应用得数据库管理系统,在许多大型的开源项目中被广泛地应用。MySQL 5支持许多基本和高级的特性,例如:

  • 支持各种数据类型
  • 支持事务、主键外键、行级锁定等特性
  • select查询语句和where子句中,提供完整的操作符合函数支持
  • 支持子查询
  • 支持group by和order by子句
  • 支持left outer join和right outer join多表连接查询
  • 支持各种聚合函数
  • 支持表别名、字段别名
  • 支持跨库多表连接查询
  • 支持查询缓存,能够极大地提升查询性能
  • 支持存储过程、视图和触发器等特性
  • 支持多平台、多CPU等特性
  • 支持嵌入式,可以将MySQL集成到嵌入式程序中

启动和关闭MySQL服务器

启动和停止MySQL服务器非常简单。但通常情况下,不要暂停或停止MySQL服务器,否则数据库无法使用。

启动MySQL服务器

只有启动MySQL服务器,才可以操作MySQL数据库。

连接和断开MySQL服务器

  1. 连接MySQL服务器
    MySQL服务器启动后,就是连接服务器。MySQL提供了MySQL console命令窗口,客户端实现了与MySQL服务器之间的交互。单击任务栏系统托盘中的WampServer图标,选择"MySQL",单击"MySQL console",打开MySQL命令窗口。
    输入MySQL服务器root账户的密码,并且按<Enter>键(如果密码为空,直接按<Enter>键即可)。如果密码输入正确,表明通过MySQL命令窗口成功连接了MySQL服务器。
  2. 断开MySQL服务器
    连接到MySQL服务器后,可以通过在MySQL提示符下输入"exit"或者"quit"命令并按<Enter>键来断开MySQL服务器。

操作MySQL服务器

针对MySQL数据库的操作可以分为创建、选择和删除3种。

创建数据库

在MySQL中,应用create database语句创建数据库。其语法如下:

 create database 数据库名称;

在创建数据库时,数据库的命名要遵循如下规则:

  • 不能与其他数据库重名
  • 名称可以由任意字母、数字、下划线(_)或者"$"符号,可以使用上述任意字符开头,但不能使用单独的数字,否则会造成它与数值相混淆
  • 名称最长可以为64个字符(包括表、列和索引的命名),而别名最多可长达256个字符
  • 默认情况下,Windows下数据库名、表名的字母大小写是不敏感的,而Linux下数据库名、表名的字母大小写是敏感的。为了便于数据库在平台间进行移植,建议采用小写字母来定义数据库名和表名

说明:"E:\wamp64\bin\mysql\mysql5.7.26\data"目录是MySQL配置文件my.ini设置的数据库文件的存储目录。用户可以通过修改配置选项datadir的值,对数据库文件的存储目录进行重新设置。

选择数据库

use语句用于选择一个数据库,使其成为当前默认数据库。其语法如下:

use 数据库名称;

查看数据库

数据库创建完成以后,可以使用show databases命令查看mySQL数据库中所有已存在的数据库。语法如下:

show databases;

删除数据库

删除数据库使用的是drop database语句,语法如下:

  drop database 数据库名称;

MySQL数据类型

在MySQL数据库中,每一条数据都有其数据类型。MySQL支持的数据类型主要分为3类:数字类型、字符串(字符)类型、日期和时间类型。

数字类型

MySQL支持所有的ANSI/ISO SQL 92数字类型,包括准确数字的数据类型(NUMERIC、DECIMAL、INTEGER和SMALLINT)、近似数字的数据类型(FLOAT、REAL和DOUBLE PRECISION)。其中,关键字INT是INTEGER的简写,关键字DEC是DECIMAL的简写。
一般来说,数字类型可以分为整型和浮点类型。

整数数据类型

数据类型取值范围说明单位
TINYINT符号值:-127~127,无符号值:0~255最小的整数1字节
BIT符号值:-127~127,无符号值:0~255最小的整数1字节
BOOL符号值:-32768~32767,无符号值:0~65535小型整数2字节
MEDIUMINT符号值:-8388608~8388607,无符号值:0~16777215中型整数3字节
INT符号值:-2147683648~2147683647,无符号值:0~4294967295标准整数4字节
BIGINT符号值:-9223372036854775808~9223372036854775807,无符号值:0~18446744073709551615大整数8字节

浮点数据类型

数据类型取值范围说明单位
FLOAT+(-)3.402823466e+38单精度浮点数8字节或4字节
DOUBLE+(-)1.7976931348623157E+308 +(-)2.2250738585072014E-308 双精度浮点数8字节
DECIMAL可变一般整数自定义长度

说明:
确定在创建表时使用哪种数据类型,应遵循以下原则:
(1)选择最小的可用类型,如果值永远不超过127,则使用TINYINT要比使用INT好。
(2)对于都是数字的,可以选择整数类型。
(3)浮点类型用于可能具有小数部分的数,如货物单价、网上购物交付金额等。

字符串类型

字符串类型可以分为3类:普通的文本字符串类型(CHAR和VARCHAR)、可变类型(TEXT和BLOB)和特殊类型(SET和ENUM)。它们之间都有一定的区别,取值的范围不同,应用的地方也不同。

  1. 普通的文本字符串类型
    普通的文本字符串类型即CHAR和VARCHAR类型。CHAR类型列的长度在创建表时指定,取值在1~255之间;VARCHAR类型列的值是可变的字符串,取值和CHAR一样。
    普通的文本字符串类型
类型取值范围说明
[national] char(M) [binary|ASCII|unicode]0~255个字符固定长度为M的字符,其中M的取值范围为0~255。national关键字指定了应该使用的默认字符集。binary关键字指定了数据是否区分大小写(默认是区分大小写的)。ASCII关键字指定了在该列中使用了latinl字符集。unicode关键字指定了使用UCS字符集
char0~255个字符和char(M)类似
[national] varchar(M) [binary|ASCII|unicode]0~255个字符长度可变,其他和char(M)类似
  1. 可变类型TEXT和BLOB
    它们的大小可以改变。TEXT类型适合存储长文本;而BLOB类型适合存储二进制数据,支持任何数据,如文本、声音和图像等。
    TEXT和BLOB类型
类型最大长度(字节数)说明
TINYBLOB2^8~1(225)小BLOB字段
TINYTEXT2^8~1(225)小TEXT字段
BLOB2^16~1(65535)常规BLOB字段
TEXT2^16~1(65535)常规TEXT字段
MEDIUMBLOB2^24~1(16777215)中型BLOB字段
MEDIUMTEXT2^24~1(16777215)中型TEXT字段
LONGBLOB2^32~1(4294967295)长BLOB字段
MEDIUMTEXT2^32~1(4294967295)长TEXT字段
  1. 特殊类型SET和ENUM
    SET和ENUM类型
类型最大长度(字节数)说明
Enum("value1","value2",...)65535该类型的列只可以容纳所列值之一或为NULL
Set("value1","value2",...)64该类型的列可以容纳一组值或为NULL

说明: 在创建表时,使用字符串类型时应遵循以下原则:
(1)从速度方面考虑,要选择固定的列,可以使用CHAR类型。
(2)要节省空间,使用动态的列,可以使用VARCHAR类型。
(3)要将列中的内容限制在一种选择,可以使用ENUM类型。
(4)允许在一个列中有多于一个的条目,可以使用SET类型。
(5)如果要搜索的内容不区分大小写,可以使用TEXT类型。
(6)如果要搜索的内容区分大小写,可以使用BLOB类型。

日期和时间类型

日期和时间类型包括DATETIME、DATE、TIMESTAMP、TIME和YEAR。每种类型都有其取值的范围,如赋予它一个不合法的值,将会被"0"替代。
日期和时间数据类型

类型取值范围说明
DATE1000-01-01 9999-12-31日期,格式为YYYY-MM-DD
TIME-838:58:59 835:59:59时间,格式为HH:MM:SS
DATETIME1000-01-01 00:00:00 9999-12-31 23:59:59日期和时间,格式为YYYY-MM-DD HH:MM:SS
TIMESTAMP1979-01-01 00::00:00 2037年的某个时间时间标签,在处理报告时使用的显示格式取决于M的值
YEAR1901-2155年份可指定两位数字和四位数字的格式
在MySQL中,日期的顺序是按照标准的ANSISQL格式进行输入的。

操作数据表

数据库创建完成后,即可在命令提示符下对数据库进行操作,如创建数据表、更改数据表结构以及删除数据表等。

创建数据表

MySQL数据库中,可以使用create table 命令来创建数据表。语法如下:

create [TEMPORARY]table[IF NOT EXISTS]数据库名称
[(create_definition,...)][table_options][select_statement]

create table语句的参数说明

关键字说明
TEMPORARY如果使用该关键字,表示创建一个临时表
IF NOT EXISTS该关键字用于避免表存在时MySQL报告的错误
create_definition这是表的列属性部分。MySQL要求在创建表时,表要至少包含一列
table_options表的一些特性参数
select_statementSELECT语句描述部分,用它可以快速地创建表

列属性create_definition的使用方法,每一列具体的定义格式如下:

col_name type[NOT NULL|NULL][DEFAULT default_value][AUTO_INCREMENT][PRIMARY KEY][reference_definition]

列属性create_definition的参数说明

关键字说明
col_name字段名
type字段类型
NOT NULL|NULL指出该列是否允许是空值。但是数据"0"和空格都不是空值,系统一般默认允许为空值,所以当不允许为空值时,必须使用NOT NULL
DEFAULT default_value表示默认值
AUTO_INCREMENT表示是否自动编号,每个表只能有一个AUTO_INCREMENT列,并且必须被索引
PRIMARY KEY表示是否为主键。一个表只能有一个PRIMARY KEY。如果表中没有一个PRIMARY KEY,而某些应用程序要求PRIMARY KEY,MySQL将返回第一个没有任何NULL列的UNIQUE键作为PRIMARY KEY。
reference_definition为字符添加注释

在实际应用中,使用create table命令创建数据表的时候,只需指定最基本的属性即可。格式如下:

create table table_name(列名1 属性,列名2 属性,......)
mysql> use db_user;
Database changed
mysql> create table tb_user(
    -> id int primary key auto_increment,
    -> user varchar(30) not null,
    -> pwd varchar(30) not null,
    -> createtime datetime);
Query OK, 0 rows affected (0.38 sec)

查看表结构

成功创建数据表之后,可以使用show columns命令或describe命令查看指定数据表的表结构。

  1. show column命令
    show column命令的语法格式如下:
 show [full] columns 数据表名称 [from 数据库名称];
 或写成
 show [full] columns from 数据库名称.数据表名称;
mysql> show columns from tb_user;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(11)     | NO   | PRI | NULL    | auto_increment |
| user       | varchar(30) | NO   |     | NULL    |                |
| pwd        | varchar(30) | NO   |     | NULL    |                |
| createtime | datetime    | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> show columns from db_user.tb_user;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(11)     | NO   | PRI | NULL    | auto_increment |
| user       | varchar(30) | NO   |     | NULL    |                |
| pwd        | varchar(30) | NO   |     | NULL    |                |
| createtime | datetime    | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
  1. describe命令
    describe命令的语法如下:
 describe 数据表名称;

其中,describe可以简写为desc。在查看表结构时,也可以只列出某一列的信息,语法格式如下:

describe 数据表名称 列名;
mysql> describe tb_user;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(11)     | NO   | PRI | NULL    | auto_increment |
| user       | varchar(30) | NO   |     | NULL    |                |
| pwd        | varchar(30) | NO   |     | NULL    |                |
| createtime | datetime    | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> describe tb_user user;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| user  | varchar(30) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> desc tb_user;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(11)     | NO   | PRI | NULL    | auto_increment |
| user       | varchar(30) | NO   |     | NULL    |                |
| pwd        | varchar(30) | NO   |     | NULL    |                |
| createtime | datetime    | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

修改表结构

修改表结构采用alter table命令。修改表结构值增加或者删除字段、修改字段名称或者字段类型、设置取消主键外键、设置取消索引以及修改表的注释等。
语法如下:

alter [IGNORE] table 数据表名称 alter_spec[,alter_spec]....

注意:
当指定IGNORE时,如果出现重复关键的行,则只执行一行,其他重复的行被删除。其中alter_spec子句用于定义要修改的内容,语法如下:

alter_specification:
ADD[COLUMN]create_definition[FIRST|AFTER column_name]  --添加新字段
|ADD INDEX [index_name](index_col_name,...)   --添加索引名称
|ADD PRIMARY KEY(index_col_name,...)  --添加主键名称
|ADD UNIQUE [index_name](index_col_name,...) --r添加唯一索引
|ALTER [COLUMN] col_name {SET DEFAULT literal|DROP DEFAULT}--修改字段名称
|CHANGE[COLUMN] old_col_name create_definition --修改字段类型
|MODIFY [COLUMN] create_definition --修改子句定义字段
|DROP[COLUMN] col_name --删除字段名称
|DROP PRIMARY KEY --删除主键名称
|DROP INDEX index_name --删除索引名称
|RENAME [AS] new_tbl_name --修改表名
|table_options  

alter table 语句允许指定多个动作,动作间使用逗号分隔,每个动作表示对表的一个修改。

mysql> alter table tb_user add address varchar(60) not null,modify user varchar(50);
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show columns from tb_user;
+-----------+------------+------+------+--------+---------------+
| Field      | Type       | Null  | Key | Default | Extra         |
+-----------+------------+------+------+--------+---------------+
| id        | int(11)     | NO  | PRI  | NULL   | auto_increment |
| user      | varchar(50) | YES |      | NULL   |               |
| pwd      | varchar(30) | NO  |      | NULL   |               |
| createtime | datetime    | YES |      | NULL   |               |
| address   | varchar(60) | NO  |      | NULL   |               |
+-----------+------------+------+------+--------+---------------+
5 rows in set (0.04 sec)

重命名数据表

重命名数据表采用rename table命令。语法格式如下:
rename table 数据表名称1 to 数据表名称2;

mysql> rename table tb_user to tb_member;
Query OK, 0 rows affected (0.00 sec)
mysql> desc tb_member;
+-----------+------------+------+------+--------+---------------+
| Field      | Type       | Null  | Key | Default | Extra         |
+-----------+------------+------+------+--------+---------------+
| id        | int(11)     | NO  | PRI  | NULL   | auto_increment |
| user      | varchar(50) | YES |      | NULL   |               |
| pwd      | varchar(30) | NO  |      | NULL   |               |
| createtime | datetime    | YES |      | NULL   |               |
| address   | varchar(60) | NO  |      | NULL   |               |
+-----------+------------+------+------+--------+---------------+
5 rows in set (0.04 sec)

说明: 该语句可以同时对多个数据表进行重命名,多个表之间以逗号","分隔。

删除数据表

删除数据表的操作含简单,与删除数据库的操作类似,使用drop table命令即可实现。格式如下:

drop table 数据表名称;

在删除数据表的过程中,如果删除一个不存在的表,将会产生错误。这时在删除语句中加入if exists关键字,就可以避免出错。格式如下:

drop table id exists 数据表名称;

注意:
在对数据表进行操作之前,首先必须选择数据库,否则是无法对数据表进行操作的。

数据表记录的更新操作

数据库中包含数据表,而数据表中包含数据。在MySQL与PHP的结合应用中,真正被操作的是数据表中的数据,因此如何更好地操作和使用这些数据才是使用MySQL数据库的根本。向数据库表中的插入,修改和删除记录可以在MySQL命令行中使用SQL语句完成。

数据表记录的添加

建立一个空的数据库和数据表时,首先要想到的就是如何向数据表中添加数据。这个操作可以通过insert命令来实现。
语法如下:

 insert into 数据表名称(column_name,column_name2,...) values(values,values2,...);

在MySQL中,一次可以同时插入多行记录,各行记录的值清单在values关键字后以逗号","分隔,而标准的SQL语句一次只能插入一行。
说明: 值列表中的值应与表中字段的个数和顺序相对应,值列表中的值的数据类型必须要与相应字段的数据类型保持一致。

--添加一条记录
mysql> insert into tb_user(user,pwd,createtime,address) values
('mr','123456','2019-11-20 19:29:30',"Shanghai");
--添加多条记录
mysql> insert into tb_user(user,pwd,createtime,address) values
('simon','123456','2019-11-20 19:29:30',"Shanghai"),
('ben','123','2019-11-20 19:29:30',"Beijing");
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> insert into tb_user values
(null,'microsoft','micro','2019-11-20 19:44:50','China Shanghai');

数据表记录的修改

要执行修改的操作,可以使用update命令。该语句的格式如下:

update 数据表名称 set column_name=new _value1,column_name2=new_value2,... where condition;

其中set子句指出要修改的列及其给定的值;where子句是可选的,如果给出该子句,将指定记录中哪行应该被更新,否则,所有的记录行都将被更新。

mysql> update tb_user set pwd='222' where user='simon';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

数据表记录的删除

在数据表时中,有些数据已经失去意义或者是错误的,就需要将它们删除,此时可以使用delete命令。该命令的格式如下:

delete from 数据表名称 where condition;

注意:
该语句在执行过程中如果没有指定where条件,将删除所有的记录;如果指定了where条件,将按照指定的条件进行删除。
使用delete命令删除整个表的效率并不高,还可以使用truncate命令。利用它可以快速删除表中的所有的内容。

delete from tb_user where user='mr';

数据表记录的查询操作

要从数据库中把数据查询出来,就要用到数据查询命令select。select命令是最常用的查询命令。
语法如下:

--要查询的内容,选择哪些列
select selection_list
--指定数据表
from 数据表名称
--查询时需要满足的条件,行必须满足的条件
where primary_constraint
--如何对结果进行分组
group by grouping_columns
--如何对结果进行排序
order by sorting_columns
--查询时满足的第二条件
having secondary_constraint
--限定输出的查询结果
limit count
  1. selection_list
    设置查询内容。如果要查询表中所有列,可以将其设置为"*";如果要查询表中某一列或多列,则直接输入列名,并以","为分隔符。
--查询数据表中所有的数据
mysql> select * from tb_user;
+----+-----------+--------+---------------------+----------------+
| id | user      | pwd    | createtime          | address        |
+----+-----------+--------+---------------------+----------------+
|  1 | mr        | 123456 | 2019-11-20 19:29:30 | Shanghai       |
|  2 | simon     | 222    | 2019-11-20 19:29:30 | Shanghai       |
|  3 | ben       | 123    | 2019-11-20 19:29:30 | Beijing        |
|  4 | microsoft | micro  | 2019-11-20 19:44:50 | China Shanghai |
+----+-----------+--------+---------------------+----------------+
4 rows in set (0.00 sec)
--查询数据表中id和user列的数据
mysql> select id,user from tb_user;
+----+-----------+
| id | user      |
+----+-----------+
|  1 | mr        |
|  2 | simon     |
|  3 | ben       |
|  4 | microsoft |
+----+-----------+
4 rows in set (0.00 sec)
  1. table_list
    指定查询的数据表。既可以从一个数据表中查询,也可以从多个数据表中进行查询,多个数据表之间用","分隔,并且通过where子句使用连接运算来确定表之间的联系。
--使用tb_mrbook.bookname=tb_bookinfo.bookname等同连接(不使用则是笛卡尔积(全连接))
select tb_mrbook.id,tb_mrbook.bookname,author,price from tb_mrbook,tb_bookinfo where tb_mrbook.bookname=tb_bookinfo.bookname and tb_bookinfo.bookinfo='php自学视频教程';
  1. where条件语句
    在使用查询语句时,如要从很多的记录中查询出想要的记录,就需要一个查询的条件。只有设定查询的条件,查询才有实际的意义。设定查询条件应用的是where子句。
    where子句的功能非常强大,通过它可以实现很多复杂的条件查询。在使用where子句时,需要使用一些比较运算符。
    常用的where子句比较运算符
运算符名称示例
=等于id=10
>大于id>10
<小于id<10
>=大于等于id>=10
<=小于等于id<=10
!=或<>不等于id!=10/id<>10
is null为空id is null
is not null不为空id is not null
between在两个值之间id between 1 and 10
in在指定范围id in(4,5,6)
not in不在指定范围id not in(a,b)
like模式匹配name like ('abc%')
not like模式匹配name not like ('abc%')
regexp常规表达式name正则表达式
select * from tb_mrbook where type='PHP';
  1. DISTINCT在结果中去除重复行
    使用DISTINCT关键字,可以去除结果中重复行。
select distinct type from tb_mrbook;
  1. ORDER BY对结果排序
    使用ORDER BY可以对查询结果进行升序和降序排列。默认情况下,ORDER BY按升序输出结果。如果使用降序排列,可以使用DESC来实现。
    对含有NULL值的列进行排序时,如果按升序排列,NULL值将出现在最前面;如果按香蕉降序排列,NULL值将出现在最后。
select * from tb_mrbook order by id desc limit 5;
  1. LIKE模糊查询
    LIKE属性较常用的比较运算符,通过它可以实现模糊查询。它有2种通配符:"%“和”_"。"%“可以匹配一个或多个字符,而”_"只能匹配一个字符。
select * from tb_mrbook where bookname like ('%PHP%');

说明: 无论是一个英文字符还是中文字符,都算作一个字符。在这一点上,英文字母和中文没有区别。

  1. CONCAT联合多列
    使用CONCAT函数可以联合多个字段,构成一个总的字符串。
    例如,把tb_mrbook表中的书名(bookname)和价格(price)合并到一起,构成一个新的字符串。代码如下:
select id,concat(bookname,":",price) as info,type from tb_mrbook;

其中,合并后的字段名为CONCAT函数形成的表达式"bookname:price",看上去十分复杂,通过AS关键字给合并字段取一个别名,这样看上去就清晰了。

mysql> select id,concat(user,":",pwd) as info,address from tb_user;
+----+-----------------+----------------+
| id | info            | address        |
+----+-----------------+----------------+
|  1 | mr:123456       | Shanghai       |
|  2 | simon:222       | Shanghai       |
|  3 | ben:123         | Beijing        |
|  4 | microsoft:micro | China Shanghai |
+----+-----------------+----------------+
4 rows in set (0.00 sec)
  1. LIMIT限定结果行数
    LIMIT子句可以对查询结果的记录条数进行限制,控制它的行数。
    例如,查询tb_mrbook表,按照图书价格升序排列,展示10条记录,代码如下:
select * from tb_mrbook order by price asc limit 10;

使用LIMIT还可以从查询结果的中间部分取值。首先要定义两个参数,参数1是开始读取的第一条记录的编号(在查询结果中,第一个结果的记录编号是0,而不是1),参数2是要查询记录的个数。
例如,查询tb_mrbook表,从第三条记录开始,查询6条记录,代码如下:

select * from tb_mrbook order by price asc limit 2,6;
mysql> select * from tb_user;
+----+----------+--------+-------------------+---------------+
| id | user     | pwd   | createtime         | address       |
+----+----------+--------+-------------------+---------------+
|  1 | mr       | 123456 | 2019-11-20 19:29:30 | Shanghai      |
|  2 | simon    | 222    | 2019-11-20 19:29:30 | Shanghai      |
|  3 | ben      | 123    | 2019-11-20 19:29:30 | Beijing        |
|  4 | microsoft | micro  | 2019-11-20 19:44:50 | China Shanghai |
+----+----------+--------+-------------------+---------------+
4 rows in set (0.04 sec)
mysql> select * from tb_user order by user asc limit 3;
+----+----------+--------+-------------------+---------------+
| id | user     | pwd   | createtime         | address       |
+----+----------+--------+-------------------+---------------+
|  3 | ben      | 123    | 2019-11-20 19:29:30 | Beijing        |
|  4 | microsoft | micro  | 2019-11-20 19:44:50 | China Shanghai |
|  1 | mr       | 123456 | 2019-11-20 19:29:30 | Shanghai      |
+----+----------+--------+-------------------+---------------+
3 rows in set (0.03 sec)
mysql> select * from tb_user order by user desc limit 1,2;
+----+----------+--------+-------------------+---------------+
| id | user     | pwd   | createtime         | address       |
+----+----------+--------+-------------------+---------------+
|  1 | mr       | 123456 | 2019-11-20 19:29:30 | Shanghai      |
|  4 | microsoft | micro  | 2019-11-20 19:44:50 | China Shanghai |
+----+----------+--------+-------------------+---------------+
2 rows in set (0.05 sec)
  1. 使用函数和表达式
    在MySQL中,还可以使用表达式计算各列的值,作为输出结果。表达式还可以包含一些函数。
    例如,计算tb_mrboo表中各类图书的总价格,代码如下:
select sum(price) as totalprice,type from tb_mrbook group by type; 

在对MySQL数据库进行操作时,有时需要对数据库中的记录进行统计,如求平均值、最小值、最大值等。这时可以使用MySQL中的统计函数。
MySQL常用的统计函数

名称说明
avg(字段名)获取指定列的平均值
count(字段名)如指定一个字段,则会统计出该字段中的非空记录。如在前面增加DISTINCT,则会统计不同值的记录,相同的值当作一条记录。如使用COUNT(*),则会统计出包含空值的所有记录数
min(字段名)获取指定列的最小值
max(字段名)获取指定列的平均值
avg(字段名)获取指定列的最大值
avg(字段名)获取指定列的平均值
avg(字段名)获取指定列的平均值
std(字段名)指定字段的标准背离值
stdtev(字段名)与STD相同
sum(字段名)获取指定字段所有记录的总和

除了使用函数之外,还可以使用算术运算符、字符串运算符、逻辑运算符来构成表达式。
例如,可以计算图书打九折后的价格,代码如下:

select *,(price*0.9) as '90%' from tb_mrbook;
  1. GROUP BY对结果分组
    通过GROUP BY子句可以将数据划分到不同的组中,实现对数据进行分组查询。在查询时,所有查询列必须包含在分组的列中,目的是使查询的数据没有矛盾。在与AVG()函数或SUM()函数一起使用时,GROUP BY子句能发挥最大作用。
    例如,查询tb_mrbook表,按照type进行分组,求每类图书的平均价格,代码如下:
select avg(price),type from tb_mrbook group by type;
  1. 使用having子句设定第二个查询条件
    having子句通常与group byz子句一起使用。在对数据结果进行分组查询和统计之后,还可以使用having子句对查询结果进行进一步的筛选。having子句和where子句都用于指定查询条件,不同的是,where子句在分组查询之前应用,而having子句在分组查询之后应用,而且having子句还可以包含统计函数。
    例如,计算tb_mrbook表白中各类图书的平均价格,并筛选出图书的平均价格大于60的记录,代码如下:
select avg(price),type from tb_mrbook group by type having avg(price)>60;

MySQL中的特殊字符

当MySQL语句中存在特殊字符时,需要使用’'对特殊字符进行转义,否则将会出现错误。
MySQL中的特殊字符

特殊字符转义后的字符
\'单引号
\"双引号
\\反斜杆
\n换行符
\r回车符
\t制表符
\'单引号
\00字符
\%%字符
\__字符
\b退格符

例如,向用户信息表tb_user中添加一条用户名为O’Neal的记录,然后查询表中的所有记录,代码如下:

mysql> insert into tb_user values(null,'O\'Neal','1234','2019-11-22 16:20:24','大连市');
Query OK, 1 row affected (0.00 sec)
mysql> select * from tb_user;
+----+----------+--------+-------------------+---------------+
| id | user     | pwd   | createtime         | address       |
+----+----------+--------+-------------------+---------------+
|  1 | mr       | 123456 | 2019-11-20 19:29:30 | Shanghai      |
|  2 | simon    | 222    | 2019-11-20 19:29:30 | Shanghai      |
|  3 | ben      | 123    | 2019-11-20 19:29:30 | Beijing        |
|  4 | microsoft | micro  | 2019-11-20 19:44:50 | China Shanghai |
|  5 | O'Neal   | 1234   | 2019-11-22 16:20:24 | 大连市        |
+----+----------+--------+-------------------+---------------+
5 rows in set (0.05 sec)

MySQL数据库的备份与还原

备份数据是数据库管理最常用的操作。为了保证数据库中数据的安全,数据库管理员需要定期进行数据备份。一旦数据库遭到破坏,即可通过备份的文件还原数据库。因此,数据备份是很重要的工作。

使用mysqldump命令备份数据库

在命令提示符窗口中使用mysqldump命令,可以将数据库中的数据备份成一个文本文件。表的结构和表中的数据将存储在生成的文本中。mysqldump命令的工作原理很简单。它先查出需要备份的表的结构,再在文本文件中生成一个CREATE语句。然后,将表中的所有记录转换成一条INSERT语句。这些CREATE语句和INSERT语句都是还原时使用的。还原数据时就可以使用其中的CREATE语句创建表,使用其中的INSERT语句还原数据。
使用mysqldump语句备份一个数据库的基本语法如下:

mysqldump -u username -p dbname table 1,table2,....>BackupName.sql

参数说明如下:
username:表示连接数据库的用户名
dbname:表示要备份的数据库的名称
table1和table2:表示表的名称。没有该参数时,将备份整个数据库
BackName.sql:表示备份文件的名称,文件名前面必须要加上一个绝对路径。通常将数据库备份成一个后缀名为.sql的文件
说明:
(1)mysqldump命令备份的文件并非一定要求后缀名为.sql,备份成其他格式文件也可以,如后缀名为.txt文件。但是,通常情况下是备份成后缀名为.sql文件。
(2)由于mysqldump命令位于"E:\wamp64\bin\mysql\mysql5.7.26\bin"目录下,所以在’命令提示符’窗口中使用mysqldump命令时需要首先进入该目录中,然后才能使用mysqldump命令。
例如,使用root用户备份db_user数据库。首先需要打开"命令提示符"窗口,然后进入"E:\wamp64\bin\mysql\mysql5.7.26\bin"目录下

C:\Users\qiean>E:
E:\>cd wamp64\bin\mysql\mysql5.7.26\bin

然后输备份数据库db_user的命令并<Enter>键,此时提示用户输入root账户的密码,输入密码后按<Enter>键即可完成数据库的备份

E:\wamp64\bin\mysql\mysql5.7.26\bin>mysqldump -u root -p db_user>E:\db_user.sql
Enter password: ****
E:\wamp64\bin\mysql\mysql5.7.26\bin

命令执行完之后,可以在计算机中的E盘中找到db_user.sql文件。

使用mysql命令还原数据库

管理员的非法操作和计算机的故障都会破坏数据库文件。当数据库遇到这些意外时,可以通过备份文件将数据库还原到备份时的状态,这样可以将损失降到最小。
通常使用mysqldump命令将数据库中的数据备份成一个后缀名为.sql的文件,需要还原时,可以使用mysql命令还原备份的数据,mysql命令的基本语法如下:

mysql -u root -p dbname <backup.sql

其中,dbname参数表示还原的数据库名称,backup.sql表示备份文件的名称,文件名前面可以加上一个绝对路径
说明:
(1)由于mysqldump命令位于"E:\wamp64\bin\mysql\mysql5.7.26\bin"目录下,所以在’命令提示符’窗口中使用mysqldump命令时需要首先进入该目录中,然后才能使用mysql命令。
(2)在还原数据库之前,首先需要在数据库的存储目录中创建一个空的数据库文件夹,如果存在该文件夹,则无需创建。
例如,使用root用户还原db_user数据库。首先在数据库的存储目录中创建db_user文件夹,然后在"命令提示符"窗口中输入如下命令:

mysql -u root -p db_user<E:/db_user.sql
Enter password: ****
E:\wamp64\bin\mysql\mysql5.7.26\bin

注意:
在进行数据库还原时,MySQL数据库中必须存在一个空的、将要恢复的数据库,否则就会出现错误提示。

  • 0
    点赞
  • 0
    评论
  • 1
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 技术黑板 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值