MySQL数据库基本操作常用命令详解

MySQL 是一个关系型数据库，使用 SQL 语言进行增删改查操作，目前属于 Oracle 旗下的产品。

MySQL 数据库开源免费，能够跨平台，支持分布式，性能也不错，可以和 PHP、Java 等 Web 开发语言完美配合，非常适合中小型企业作为 Web 数据库（网站数据库）。

数据库管理工具

• Navicat for MySQL

Navicat是一款桌面版MySQL管理工具，它和微软的SQLServer的管理器很像，简单易用。Navicat的优势在于使用图形化的用户界面，可以让用户管理更加轻松。

Navicat for MySQL：http://www.navicat.com.cn/products/navicat-for-mysql

• phpMyAdmin

通过WEB方式控制和操作MYSQL数据库

phpmyadmin官网：https://www.phpmyadmin.net/

phpmyadmin中文网：http://www.phpmyadmin.org.cn/

• Adminer

Adminer是一个类似于phpMyAdmin的MySQL管理客户端，支持MySQL、MS SQL、PostgreSQL、SQLite等主流，支持多语言（已自带11种翻译语言文件，可以按自己的需求翻译相应的语言），整个程序只有一个PHP文件，使用起来非常方便！

Adminer官网：https://www.adminer.org/

• SQLyog

SQLyog是一个易于使用的、快速而简洁的图形化管理MYSQL数据库的工具，它能够在任何地点有效地管理你的数据库。

SQLyog官网：https://sqlyog.en.softonic.com/

连接Mysql数据库

格式： mysql -h主机地址 -u用户名 －p用户密码

1、连接到本机上的MYSQL。
首先win+R输入CMD打开DOS窗口，然后进入目录mysql\bin，再键入命令mysql -u root -p，回车后提示你输密码.注意用户名前可以有空格也可以没有空格，但是密码前必须没有空格，否则让你重新输入密码。

如果刚安装好MYSQL，超级用户root是没有密码的，故直接回车即可进入到MYSQL中了，MYSQL的提示符是： mysql>

注：如果不想每次输入mysql命令都进入到mysql/bin目录下，可以设置环境变量，不然系统会提示找不到mysql命令。

2、系统环境变量的配置

找到MySQL安装的路径下的bin文件夹，我这里使用的是集成环境UPUPW ，MySQL的安装目录在D:\UPUPW_ANK_W64\Modules\MySQL\bin

环境变量的设置：我的电脑右键=>选择属性=>选择高级系统设置=>环境变量=>系统变量中的Path=>点击编辑=>点击新建=>将路径复制进去=>保存即可。设置好环境变量，在任意文件夹下都可以执行MySQL

3、连接到远程主机上的MYSQL。

假设远程主机的IP为：127.0.0.1，用户名为root,密码为abcd123。则键入以下命令：
 mysql -h 127.0.0.1-u root -p;（注:u与root之间可以不用加空格，其它也一样）

3、退出MYSQL命令： exit （回车）

修改MySQL连接密码

格式：mysqladmin -u用户名 -p旧密码 password 新密码

1、给root加个密码1234

mysqladmin -u root -password 1234
注：因为开始时root没有密码，所以-p旧密码一项就可以省略了。

2、再将root的密码改为admin
 mysqladmin -u root -p 1234 password admin

SQL 语句分类

• DDL: 数据定义, create, alter,drop

• DML: 数据操作, insert, update,delete,select,(CURD)

• DCL: 数据控制, grant, revoke

• TCL: 事件控制, commit, rollback

数据库定义语言DDL

　数据库模式定义语言DDL(Data Definition Language)，是用于描述数据库中要存储的现实世界实体的语言。

• CREATE DATABASE 创建数据库

• CREATE TABLE 创建表

• ALTER TABLE 修改表

• DROP TABLE 删除表

• CREATE VIEW 创建视图

• ALTER VIEW 修改视图

• DROP VIEW 删除视图

• TRUNCATE TABLE 清空表

• CREATE INDEX 创建索引

• DROP INDEX 删除索引

数据库相关操作

MySQL查看数据库（SHOW DATABASES语句）
MySQL创建数据库（CREATE DATABASE语句）
MySQL修改数据库：ALTER DATABASE用法简介
MySQL删除数据库（DROP DATABASE语句）
MySQL选择数据库（MySQL USE语句）

• 增

• create database 库名称;

• create database 数据库名称 charset 编码方式;

• 删

• drop database 库名称;

• 改

• 修改编码方式:alter database 库名称 charset 编码方式;

• 查

• 查看所有库:show databases;

• 查看指定库:show database 库名称;

• 选

• use 数据库名称;

-- 创建数据库# create database 数据库名称 charset 编码方式 collate 排序规则;create database phpedu collate utf8mb4_unicode_ci;-- 修改数据库  alter database 库名称 charset 编码方式;-- 删除数据库drop database phpedu;-- 选择默认数据库use phpedu;-- 查看所以数据库show databases;-- 查看建库语句show create database phpedu;-- 查看状态status-- 查看当前数据库select database();-- 查看登录用户select user();-- 连接数据库时就选中当前数据库myblogmysql -uroot -proot myblog;

对于数据库来说，字符集的选择更加重要。因为数据库中存储的大部分内容都是各种文字，字符集对数据库的存储、处理性能，以及日后系统的移植、推广都会受到影响。

utf8mb4 表示这种字符集由 1~ 4个字节组成，如果需要支持 emoji 表情，通常需要选择 utf8mb4 的字符集来支持。随着互联网的发展，有越来越多的应用需要 utf8mb4 的字符集来支持。在最新的 MySQL8.0 中，默认字符集已经由 latin1 变为了 utf8mb4。

排序规则：一般选择utf8mb4_unicode_ci是基于标准的Unicode来排序和比较，能够在各种语言之间精确排序

数据库表相关操作

• CREATE TABLE 创建表

• ALTER TABLE 修改表

• DROP TABLE 删除表

• 增

• 增加表单create table 表名称(字段1 数据类型,字段2 数据类型……..限制条件);

• 至少有一个字段和数据类型,最后一个字段已经最后一个数据类型结束后不加,逗号

• 删

• 删除表:drop table 表名称;

• 清空表里面数据: truncate table 表名称;

• 改

  字段

  表

• 改表名称:rename table 表名称 to 新名称

• 改表的编码:alter table 表名 charset 新编码;

• 添加字段:alter table 表名称 add 字段 字段数据类型

• 删除字段:alter table 表名称 drop 字段

• 修改字段数据类型:alter table 表名称 modify 字段 新的数据类型

• 替换字段:alter table 表名称 change 旧字段名称 新字段 新字段数据类型

• 查

• 查看所有表:show tables

• 查看指定表结构:desc  表名称

• 查看指定表创建信息:show create table 表名称

表的字段类型

MySQL提供了一组可以赋给表中各个列的数据类型，每个类型都强制数据满足为该数据类型预先确定的一组规则，例如大小、类型及格式。支持多种类型，大致可以分为三类：数值、日期/时间和字符串（字符类型）

日期和时间数据类型

MySQL数据类型	含义
date	3字节，日期，格式：2014-09-18
time	3字节，时间，格式：08:42:30
datetime	8字节，日期时间，格式：2014-09-18 08:42:30
timestamp	4字节，自动存储记录修改的时间
year	1字节，年份

timestamp与datetime：

• datetime 的日期范围比较大,1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 , timestamp 所占存储空间比较小，只是 datetime 的一半。

• TIMESTAMP 用于表示 年月日 时分秒，但是记录的年份 比较短暂，1970-01-01 00:00:01 ~ 2038, TIMESTAMP列必须有默认值，默认值可以为“0000-00-00 00:00:00”，但不能为null。

• TIMESTAMP 和时区相关，更能反映当前时间。当插入日期时，会先转换为本地时区后再存放；当查询日期时，会将日期转换为本地时区后再显示。所以不同时区的人看到的同一时间是 不一样的。

• TIMESTAMP 的属性受 Mysql 版本和服务器 SQLMode 的影响较大。

• 如果记录的日期需要让不同时区的人使用，最好使用 TIMESTAMP。

• 一般建表时候，创建时间用datetime int ，更新时间用timestamp。

数值数据类型

整型

MySQL数据类型	含义（有符号）
tinyint	1字节，范围（-128~127）
smallint	2字节，范围（-32768~32767）
mediumint	3字节，范围（-8388608~8388607）
int	4字节，范围（-2147483648~2147483647）
bigint	8字节，范围（+-9.22*10的18次方）

浮点型

MySQL数据类型	含义
float(m, d)	4字节，单精度浮点型，m总个数，d小数位
double(m, d)	8字节，双精度浮点型，m总个数，d小数位
decimal(m, d)	decimal是存储为字符串的浮点数

字符串数据类型

MySQL数据类型	含义
char(n)	固定长度，最多255个字符
varchar(n)	可变长度，最多65535个字符
tinytext	可变长度，最多255个字符
text	可变长度，最多65535个字符
mediumtext	可变长度，最多2的24次方-1个字符
longtext	可变长度，最多2的32次方-1个字符

1.char（n）和varchar（n）中括号中n代表字符的个数，并不代表字节个数，所以当使用了中文的时候(UTF8)意味着可以插入m个中文，但是实际会占用m*3个字节。
2.同时char和varchar最大的区别就在于char不管实际value都会占用n个字符的空间，而varchar只会占用实际字符应该占用的空间+1，并且实际空间+1<=n。
3.超过char和varchar的n设置后，字符串会被截断。
4.char的上限为255字节，varchar的上限65535字节，text的上限为65535。
5.char在存储的时候会截断尾部的空格，varchar和text不会。
6.varchar会使用1-3个字节来存储长度，text不会。

创建数据库表

CREATE TABLE 语句用于创建数据库中的表

命令格式如下：

CREATE TABLE table_name(column_name1 data_type(size) constraint_name,column_name2 data_type(size) constraint_name,column_name3 data_type(size) constraint_name,....);

• column_name 参数规定表中列的名称。

• data_type 参数规定列的数据类型（例如 varchar、integer、decimal、date 等等）。

• size 参数规定表中列的最大长度。

在 SQL 中，我们有如下约束：

• NOT NULL - 指示某列不能存储 NULL 值。

NOT NULL 约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录。

• UNIQUE - 保证某列的每行必须有唯一的值。

• PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。

PRIMARY KEY 约束唯一标识数据库表中的每条记录。主键必须包含唯一的值。主键列不能包含 NULL 值。每个表都应该有一个主键，并且每个表只能有一个主键。

• FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。

• CHECK - 保证列中的值符合指定的条件。

• DEFAULT - 规定没有给列赋值时的默认值。

名称	类型	约束条件	说明
user_id	int(10)	不允许为空,无重复	用户标识，主键，自动递增
user_name	varchar(20)	不允许为空,无重复	用户名，唯一索引
user_pwd	varchar(60)	不允许为空	用户密码
user_sex	tinyint(1)	默认值为0	用户性别  1男0女
create_time	int(10)	不允许为空	注册时间，时间戳
update_time	timestamp	不允许为空	修改时间

根据如上结构创建表

# 创建数据库create database mydb;# 选择当前数据库use mydb;## 创建用户表结构create table `users`(    `user_id` int(10) not null auto_increment comment '用户ID' primary key,    `user_name` varchar(20) not null  comment '用户名' unique key,    `user_pwd` varchar(60) not null comment '用户密码',    `user_sex` tinyint(1)  comment '0男1女'  default '0',    `create_time` int(10) not null,    `update_time` timestamp not null default current_timestamp() on update current_timestamp())ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

修改数据库表

修改表指的是修改数据库中已经存在的数据表的结构。MySQL 使用 ALTER TABLE 语句修改表。常用的修改表的操作有修改表名、修改字段数据类型或字段名、增加和删除字段、修改字段的排列位置、更改表的存储引擎、删除表的外键约束等。

常用的语法格式如下：
ALTER TABLE <表名> [修改选项]

修改选项的语法格式如下：

{   ADD COLUMN <列名> <类型>   # 添加字段| CHANGE COLUMN <旧列名> <新列名> <新列类型>  # 修改字段名称| ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT }| MODIFY COLUMN <列名> <类型> # 修改字段数据类型| DROP COLUMN <列名> # 删除字段| RENAME TO <新表名> #修改表名 }

-- 增加邮箱字段alter table users add email int(20) unsigned not null;alter table users add phone int(13) unsigned not null;-- 修改字段定义alter table users change email user_email varchar(20) not null default 'admin@qq.com';-- 删除字段alter table users drop phone;

删除数据库表

删除表中的索引

alter table table_name drop index index_name

删除表
drop table table_name

删除数据库
drop database database_name

删除表内的数据
truncate table table_name

查询数据库表

查看数据表结构

desc 表名称;

查看建表语句
show create table 表名称;

查看库中有哪些表
show tables;

　数据操纵语言DML

数据操纵语言（Data Manipulation Language, DML）是SQL语言中，负责对数据库对象运行数据访问工作的指令集，以INSERT、UPDATE、DELETE三种指令为核心，分别代表插入、更新与删除，是开发以数据为中心的应用程序必定会使用到的指令，因此有很多开发人员都把加上SQL的SELECT语句的四大指令以“CRUD”来称呼。

• select 查询数据

• insert 插入数据

• update 更新数据

• delete 删除数据

• 增

  插入一行值

  插入多行值

• insert into 表名 values(v1),(v2)……

• insert into 表名(字段名称1,字段名称2) values(v1,v1),(v2,v2)……

• insert into 表名 values(v1,v2,….) # 该方式必须保证 插入的数据个数与 表格字段一 一对应

• insert into 表名(字段名称1,字段名称2) values(v1,v2) # 该方式必须保证 插入的数据个数与 必须与指

• 删

• 删除所有:delete from 表名;

• 删除指定条件下:delete from 表名 where 条件;

• 改

• 修改所有数据:update 表名 set 字段名称=新的值,字段名称2=值2;

• 修改满足条件的数据:update 表名 set 字段名称=新的值,字段名称2=值2 where 条件

• 查

• 查看所有字段:select * from 表名;

• 查看指定字段:select 字段1,字段2 from 表名 ;

• 查看某个条件所有的字段:select * from 表名 where 条件;

• 查看某个条件下的某个字段select 字段 from 表名 where 条件;

CRUD

# insert 插入数据insert into users (user_name,user_pwd,user_sex,create_time,email) values ('admin','admin',0,1620793062699,'admin@qq.com');# 如果插入全部字段数据可以省略字段名，但插入的数据个数与表格字段要一一对应#insert into users  values ();# update 更新数据# delete 删除数据

SELECT

SELECT [DISTINCT] 表达式1|字段,....(*表示所有列)FROM 表名 别名 [, 数据源2 别名2]WHERE 查询条件GROUP BY 分组字段1,...HAVING 分组筛选条件1,...ORDER BY 排序字段1 ASC|DESC, 排序字段2 ASC | DESC ...LIMIT 偏移量,数量

时间戳介绍

在 UNIX 系统中，日期与时间表示为自 1970 年 01 月 01 日 00 时 00 分 00 秒（北京时间 1970 年 01 月 01 日 08 时 00 分 00 秒）起到当前时刻的总秒数，这种时间称为 UNIX 时间戳

UNIX 时间截提供了一种统一、简洁的时间表示方式，在不同的操作系统中均支持这种时间表示方式，同一时间在 UNIX 和 Windows 系统中均以相同的 UNIX 时间戳表示，所以不需要在不同的系统中进行转换。同时，UNIX 时间戳是一个时间差，与时区没有关系，无论当前 PHP 中使用的是何种时区，其 UNIX 时间戳是唯一的。

PHP 为 UNIX 时间戳的处理提供了多种函数。到目前的 PHP 版本为止，由于任何已知的 Windows 版本以及其他一些系统均不支持负的时间戳，因此在 Windows 中无法表示 1970 年 1 月 1 日之前的时间。

使用 PHP 中的 time() 函数来获取当前的 UNIX 时间戳长度为10字符，函数的语法格式如下：time();

<?php    $time = time();    echo '当前的时间戳为：'.$time.'<br>';    date_default_timezone_set('GMT');    $time = time();    echo '将时区设置为零时区，获取的时间戳为：'.$time;?>

可以使用 date() 函数来格式化一个本地的时间或日期，该函数的语法格式如下：

date($format [,$timestamp])

参数说明如下：

• $format：表示格式化后的时间格式，可以包含一些具有特殊含义的字符。

• $timestamp：表示待格式化的时间戳，是一个可选参数，默认为当前时间。也可以理解为 $timestamp 的默认值为 time()。

format 字符	说明	返回值例子
d	月份中的第几天，使用两位的数字表示，不足两位时在前面补 0	01 到 31
D	星期几的英文缩写（使用 3 个字母表示）	Mon 到 Sun
l	星期几的英文单词	Sunday 到 Saturday
S	每月天数后面的英文后缀（使用 2 个字符表示）	st、nd、rd 或者 th。可以和 j 一起使用
w	使用数字表示星期中的第几天	0（表示星期天）到 6（表示星期六）
z	使用数字表示一年中的第几天	0 到 365
F	月份的英文单词，例如 January 或者 June	January 到 December
m	使用两位的数字表示当前月份	01 到 12
M	月份的英文缩写	Jan 到 Dec
n	使用数字表示当前月份	1 到 12
t	指定月份的天数	28 到 31
L	指定的年份是否为闰年	如果是闰年值为 1，否则为 0
Y	使用 4 位数字表示完整的年份	例如：1999 或 2019

格式化时间的格式很多，我们常用的就是获取年月日时分秒

<?php    echo '当前的时间戳是：'.time().'<br>';    echo '当前时间：'.date('Y-m-d H:i:s').'<br>';     echo '使用“now”获取当前的时间戳：'.strtotime('now').'<br>';    echo '时间戳长度为：'.strlen(time());/*当前的时间戳是：1620795120当前时间：2021-05-12 12:52:00使用“now”获取当前的时间戳：1620795120时间戳长度为：10*/?>

用户注册案例

regist.html  前台注册页面

<!DOCTYPE html><html lang="en"><head>    <meta charset="UTF-8">    <title>用户注册页面</title>    <link rel="stylesheet" type="text/css" href="css/regist.css"></head><body>    <div class="wrapper">        <article>            <h1><span>个人博客注册</span></h1>            <div class="main">                <form action="" method="post"onsubmit="return false">                    <div class="tel">                        <input type="email" name="email" placeholder="请输入邮箱" id="telephone" required >                    </div>                    <div class="userName">                        <input type="text" name="userName" placeholder="请输入用户名" id="username" required>                    </div>                    <div class="password">                        <input type="password" name="pwd" placeholder="请输入密码" id="pwd" required>                    </div>                    <div class="againpwd">                        <input type="password" name="cpwd" placeholder="请再次输入密码" id="cmpwd" required>                    </div>                    <div class="captcha">                         <input type="text" name="captcha" id="captcha" placeholder="请输入验证码" required>                        <img width="60" height="37" onclick="this.src='gd_captcha.php?'+new Date().getTime();" style="margin: 10px auto;vertical-align: bottom;" src="gd_captcha.php">                        <em id="captchainfo">提示信息……</em>                    </div>                    <button>注册</button>                </form>            </div>        </article>           </div>    <script type="text/javascript" src="js/reg.js"></script></body></html>

reg.js  ajax异步请求

document.querySelector('button').addEventListener("click",function(e){    const formdata = new FormData(document.querySelector('form'));    const xhr = new XMLHttpRequest();    xhr.open("post", "regist_check.php");    xhr.onload = () => {        let json = JSON.parse(xhr.response);        if(json.status==0){            document.querySelector("#captchainfo").textContent = json.msg;            document.querySelector("#captchainfo").style.color = "red";        }else{            document.querySelector("#captchainfo").textContent = json.msg;            document.querySelector("#captchainfo").style.color = "green";            setTimeout(()=>{                window.location.href = "http://baidu.com";            },2000)        }    };    xhr.send(formdata);});document.querySelector('#captcha').addEventListener('blur',function(e){    // console.log(e.target.value);    const xhr = new XMLHttpRequest();    xhr.open("post", "captcha_check.php");    xhr.onload = () => {        let json = JSON.parse(xhr.response);        if(json.status==0){            document.querySelector("#captchainfo").textContent = json.msg;            document.querySelector("#captchainfo").style.color = "red";            e.target.focus();        }else{            document.querySelector("#captchainfo").textContent = json.msg;            document.querySelector("#captchainfo").style.color = "green"        }    };    xhr.setRequestHeader("Content-Type","application/x-www-form-urlencoded");    xhr.send("captcha="+e.target.value);})document.querySelector('#username').addEventListener('blur',function(e){    const xhr = new XMLHttpRequest();    xhr.open("post", "uname_check.php");    xhr.onload = () => {        let json = JSON.parse(xhr.response);        if(json.status==0){            document.querySelector("#captchainfo").textContent = json.msg;            document.querySelector("#captchainfo").style.color = "red";            e.target.focus();        }else{            document.querySelector("#captchainfo").textContent = json.msg;            document.querySelector("#captchainfo").style.color = "green"        }    };    xhr.setRequestHeader("Content-Type","application/x-www-form-urlencoded");    xhr.send("uname="+e.target.value);})

captcha_check.php 检测验证码是否正确

<?php  /** * 接受用户登陆时提交的验证码 */session_start();//1. 获取到用户提交的验证码$captcha = $_POST["captcha"];//2. 将session中的验证码和用户提交的验证码进行核对,当成功时提示验证码正确，并销毁之前的session值,不成功则重新提交if(!empty($captcha)){    if(strtolower($_SESSION["captcha"]) === strtolower($captcha)){        echo json_encode(['status'=>1,'msg'=>'验证码正确']);    }else{        echo json_encode(['status'=>0,'msg'=>'验证码不正确']);    }}

uname_check.php 检测用户名是否已存在

<?php  // 连接数据库服务require "./connect.php";// 获取用户名$uname = $_POST["uname"];if(!empty($uname)){    $sql = "select user_name from users where user_name='{$uname}'";    $res =  $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);    if($res){        echo json_encode(['status'=>0,'msg'=>'用户名已存在']);    }else{        echo json_encode(['status'=>1,'msg'=>'用户名合法']);    }}?>

regist_check.php 发送数据注册用户

<?php  require "./connect.php";session_start();$user = $_POST;if(strlen($user['userName'])<4 || !preg_match("/^[A-Za-z]/i",$user['userName']) ){    echo json_encode(['status'=>0,'msg'=>'用户名长度需不小于四位且以字母开头']);}else if(strcmp($user['pwd'],$user['cpwd'])!== 0){    echo json_encode(['status'=>0,'msg'=>'两次密码输入不一致']);}else if(strcasecmp($_SESSION["captcha"],$user["captcha"])!== 0){    echo json_encode(['status'=>0,'msg'=>'验证码不正确']);}else{    $create_time = time();    $sql  = "insert into users (user_name,user_pwd,create_time,user_email) values ('{$user['userName']}','{$user['pwd']}',{$create_time},'{$user['email']}')";    if($pdo->exec($sql)):        echo json_encode(['status'=>1,'msg'=>'注册成功，请稍后……']);    else:        echo json_encode(['status'=>0,'msg'=>'注册失败~~~']);    endif;}?>

connect.php  数据库连接

<?php  namespace pdo_connect;// 数据库配置信息$config = require __DIR__."./config/database.php";extract($config);// $uname = $_POST["uname"];$dsn = sprintf("%s:host=%s;dbname=%s",$type,$host,$dbname);$pdo = new \PDO($dsn,$username,$password);?>

效果展示：

1.01的365次方=37.78343433289 >>>1

0.99的365次方= 0.02551796445229 <<<1

每天进步一点点的目标，贵在坚持…