phpMyAdmin经常出问题，所以不得不学习一些命令行的操作
注
- 数据库版本不同，命令有差异，以下环境(Server version: 5.7.19-0ubuntu0.16.04.1 (Ubuntu))
- 很多时候是命令输错了
- 命令不区分大小
mysql>内命令以;结尾，不要漏了,命令写一半，可以换行书写- 建议配合phpMyAdmin一块学习
- sql语句中 如果字段/数据库名/数据表名含有空格，mysql中使用重音符括起来，一般推荐不含空格也用重音符括起来
先复习下数据库的结构
备份与导入数据库
参考
MySQL备份所有数据库脚本
MySQL备份之mysqldump工具–lock-all-tables、–single-transaction以及–lock-tables参数
备份MySQL出现Can’t open file when using LOCK TABLES错误
直接导出所有数据库
#备份数据库
$ mysqldump -u用户名 -p密码 --lock-all-tables --all-databases > all.sql
#导入数据库
$ mysql -u用户名 -p密码 < all.sql
注意-p后面直接跟密码，不能有空格或者不写密码，回车后输入(推荐)
-u可直接跟用户名，也可空格后跟用户名
这个是直接使用mysqldump命令导出MySQL所有数据库的命令，包括MySQL本身的一些库，例如
数据库mysql(里面包含,mysql的每个用户的密码账户权限等信息,参见后面的用户管理会更清晰的理解这里)
优点：备份完整，用户名和密码也一并备份
缺点：导入之后可能会改动原有的数据库用户信息(覆盖/重复/修改密码)
--lock-all-tables参数
指定--lock-all-tables参数，那么从一开始就对整个mysql实例加global read lock锁。
这整个全局读锁会一直持续到导出结束。
所以在这个过程中，数据库实际严格处于read only状态。
所以导出的数据库在数据一致性上是被严格保证的，也就是数据是一致性的。
导出某个数据库
$ mysqldump -u用户名 -p --databases 数据库名 > name.sql
bash脚本
涉及bash的知识可以参考20170805bash学习
该脚本来自MySQL Dump All Databases and Create (or Recreate) them on Import?
可以依次导出所有数据库
#!/bin/bash
USER="用户名"
PASSWORD="密码"
databases=`mysql -u$USER -p$PASSWORD -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`
for db in $databases; do
if [[ "$db" != "information_schema" ]] && [[ "$db" != "performance_schema" ]] && [[ "$db" != "mysql" ]] && [[ "$db" != _* ]] ; then
echo "Dumping database: $db"
mysqldump -u$USER -p$PASSWORD --databases $db > `date +%Y%m%d`.$db.sql
# gzip $OUTPUT/`date +%Y%m%d`.$db.sql
fi
done
优点：因为未导出
数据库mysql不影响MySQL本身的信息，不会改动原有数据库的用户名和密码
缺点：导入之后需要手动建立用户名并赋予权限，需重新修改网站配置文件
遇到的问题
1.使用mysqldump备份时提示(errno: 24) when using LOCK TABLES
添加--lock-tables=false解决，如
mysqldump -u root -p --lock-tables=false --databases baotuquan > baotuquan.sql
Mysql常用命令
登陆
mysql -u 用户名 -p
之后就进入了mysql命令界面
mysql>
查看
查看数据库
mysql> show databases;
查看数据表
mysql> use 数据库名 ;
mysql> show tables;
查看字段
show columns from `数据库名`.`数据表名`;
查看记录，更多关于记录的操作，后面会介绍
mysql> select `字段1`,`字段2`,`更多字段` from `数据库名`.`数据表名` [ where 条件] [LIMIT n] [OFFSET M] ;
Mysql用户管理
参考
Ubuntu 重置MySQL Root密码
在Linux环境下mysql的root密码忘记解决方法
mysql命令flush privileges
MySQL命令行创建用户与授权
MySQL用户管理：添加用户、授权、删除用户
Oracle中5个核心Sql语句的基本构造：Select、Insert、Update、Delete和Merge
MySQL用户管理
mysql数据表说明
Mysql的每个用户存储在mysql数据库中的user数据表中，也就是我们可以从这里快速更改用户名和权限，host，用户密码是以passwd()加密之后的所以不能直接更改
因此对用户的管理，基本上就是对数据库mysql的修改
以下内容引用自MySQL用户管理
MySQL授权系统主要通过五个表（user、db、host、tables_priv和columns_priv）来实现，其中用于访问数据库的各种用户信息都保存在mysql库的user表中。账户权限信息被存储在mysql数据库的user、db、host、tables_priv、columns_priv和procs_priv表中。
- user表存储用户的全局权限，如
登陆地址,用户名,全局不同权限，密码等数据分别对应Hosts,User,权限.priv,authentication_string等字段- db表存储用户对某数据库的权限，如
登陆地址,数据库名,用户名,各个权限等数据分别对应Hosts,Db,User,权限.priv。包含在db表中的权限适用于这个表标识的数据库。- host表——当您想在db表的范围之内扩展一个条目时，就会用到这个表。举例来说，如果某个db允许通过多个主机访问的话，那么超级用户就可以让db表内将host列为空，然后用必要的主机名填充host表。
- tables_priv表——该表与db表相似，不同之处是它存储用户对某表的权限。
- columns_priv——该表作用几乎与db和tables_priv表一样，存储的是针对某些表特定列的权限。
权限表的存取过程是：
1) 先从user表中的host、 user、 password这3个字段中判断连接的IP、用户名、密码是否存在表中，存在则通过身份验证；
2) 通过权限验证，进行权限分配时，按照user、db、tables_priv、columns_priv的顺序进行分配。即先检查全局权限表user，如果user中对应的权限为Y，则此用户对所有数据库的权限都为Y，将不再检查db,tables_priv,columns_priv；如果为N，则到db表中检查此用户对应的具体数据库，并得到db中为Y的权限；如果db中为N，则检 查tables_priv中此数据库对应的具体表，取得表中的权限Y，以此类推。
添加用户
mysql> create user 用户名@'host地址' identified by '密码';
此时可以通过phpMyAdmin或者mysql> select Host,User from mysql.user;可查看到以添加的用户
@'host地址'缺省为@'%'所有地址，localhost代表本地地址即127.0.0.1,具体每个数据库.数据表的权限由以下决定
给用户权限
mysql> grant 权限 on 数据库.数据表 to 用户名@'host地址' identified by '密码';
mysql> flush privileges
flush privileges刷新MySQL的系统权限相关表
权限(不分大小写)
- SELECT 提取/查看
- INSERT 插入
- UPDATE 更新/改变
- 等
- ALL 表示所有权限
数据库/数据表不存在会报错，可以数据库.*表示整个数据库
用户地址密码
- 如果不存在该用户，则自动创建用户
@'host地址'缺省为@'%'所有地址- 一个用户支持不同的访问地址(一个host地址只有一个密码，如果已存在密码不用输入，输入则覆盖)，每执行一次命令就在
mysql.user内添加相应Hsot,User,各种权限priv,密码`等字段
例：添加库.表的权限
添加netstu1.jia_netq表select权限为已有用户chen，地址为%,密码为chen默认密码
mysql> grant select on netstu1.jia_netq to chen;
可用phpMyAdmin在mysql.tables_priv里查看，或命令查看，(以后统一命令查看，不再提及phpMyAdmin，两种方法通用)
mysql> select Host,Db,User,Table_name from mysql.tables_priv where User = 'chen';
例：添加库的权限
添加netstu1.jia_netq库权限为已有用户chen，地址为%,密码为chen默认密码
mysql> grant select on netstu1.* to chen;
查看mysql.db表中
mysql> select Host,Db,User from mysql.db where User = 'chen';
例：添加全局权限
添加所有数据库的select权限为已有用户chen，地址为%,密码为chen默认密码
mysql> grant select on *.* to chen;
查看mysql.user表中
mysql> select Host,User,Select_priv from mysql.user where User = 'chen';
创建root级别账户(全局权限+授权权限)
mysql> grant all on *.* to 用户名@'localhost' identified by '密码';
之后发现，创建的用户除了给其他用户授权外有所有的权限
又
mysql> update `mysql`.`user` set `Grant_priv`='Y' where `mysql`.`user`.`Host`='localhost' AND `mysql`.`user`.`User`='用户名';
更新权限
flush privileges;
取消/修改权限
可参考修改字段,修改/删除mysql.相关数据表中的值
修改用户密码
就是更新mysql.user,修改命令参考下面的修改字段,具体命令
#使用PASSWD()函数生成密码的密文并更新到mysql数据库的密码字段
#MySQL 5.7 以前版本密码字段为Password
mysql> update mysql.user set password = password('密码') where user = '用户名' and host = 'host地址';
#MySQL 5.7 以后版本（Password字段改为了authentication_string）
mysql> update mysql.user set authentication_string=PASSWORD('密码') where user='用户名' and host = 'host地址';
更新权限
mysql> flush privileges;
删除用户
drop user zhangsan@'host地址';
drop user命令会删除用户以及对应的权限，执行命令后你会发现
mysql.user表和mysql.db等表的相应记录都消失了。
mysql 找回root密码
环境
ubuntu 16.04 Mysql 5.7.19
操作
1.首先确认服务器出于安全的状态
也就是没有人能够任意地连接MySQL数据库。 因为在重新设置MySQL的root密码的期间，MySQL数据库完全出于没有密码保护的 状态下，其他的用户也可以任意地登录和修改MySQL的信息。
2.修改mysql登陆设置
修改配置文件，我服务器(ubuntu16.04 Mysql 5.7.19)文件在/etc/mysql/mysql.conf.d/mysqld.cnf，其他教程都说在/etc/mysql/my.cnf
sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
在[mysqld]下面添加skip-grant-tables
使得：任何的帐号用任何的密码(当然也包括空)都可以登录到mysql数据库
3．重新启动mysqld
sudo /etc/init.d/mysql restart
4. 登陆并修改Mysql root 密码
就是替换mysql.user中root用户对应的密码字段
按照登陆，修改用户密码修改root密码
如
#登陆
mysql -u root
#使用mysql数据表
mysql> use mysql;
#使用PASSWD()函数生成密码的密文并更新到mysql数据库的密码字段
#MySQL 5.7 以前版本密码字段为Password
mysql> update user set Password=PASSWORD('new_password') where user='root';
#MySQL 5.7 以后版本（Password字段改为了authentication_string）
mysql> update user set authentication_string=PASSWORD('new_password') where user='root';
刷新MySQL的系统权限相关表
mysql> flush privileges;
退出
mysql> exit;
5. 修改/etc/mysql/mysql.conf.d/mysqld.cnf文件为默认内容，重启Mysql
sudo /etc/init.d/mysql restart
问题
1.新建用户时遇到密码不合适
参考解决 MySQL 5.7 中 Your password does not satisfy the current policy requirements. 问题
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
将安全级别降低
set global validate_password_policy=0;
还可以设置长度
set global validate_password_length=4;
数据库管理
MySQL 教程
MySQL 创建数据库
重命名mysql数据库的五个方法
创建数据库
mysql视图中创建
mysql> create database 数据库名;
mysqladmin创建
$mysqladmin -u root -p create 数据库名
Enter password:
php脚本创建
语法
mysqli_query(connection,query,resultmode);
其中
- connectio 为建立的mysql连接
- query 查询字符串(即mysql命令，创建为
create database 数据库名) - resultmode可选，默认为MYSQLI_STORE_RESULT，如果需要检索大量数据，使用MYSQLI_USE_RESULT
php脚本
<?php
$dbhost = 'localhost:3306'; // mysql服务器主机地址
$dbuser = 'root'; // mysql用户名
$dbpass = '123456'; // mysql用户名密码
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('连接错误: ' . mysqli_error($conn));
}
echo '连接成功<br />';
$sql = 'CREATE DATABASE 数据库名';
$retval = mysqli_query($conn,$sql );
if(! $retval )
{
die('创建数据库失败: ' . mysqli_error($conn));
}
echo "数据库 数据库名 创建成功\n";
mysqli_close($conn);
?>
删除数据库
mysql命令行中删除
mysql> drop database 数据库名;
mysqladmin删除
$mysqladmin -u root -p drop 数据库名
Enter password:
php脚本删除
语法
mysqli_query(connection,query,resultmode);
其中
- connectio 为建立的mysql连接
- query 查询字符串(即mysql命令，删除为
drop database 数据库名) - resultmode可选，默认为MYSQLI_STORE_RESULT，如果需要检索大量数据，使用MYSQLI_USE_RESULT
php脚本
<?php
$dbhost = 'localhost:3306'; // mysql服务器主机地址
$dbuser = 'root'; // mysql用户名
$dbpass = '123456'; // mysql用户名密码
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('连接错误: ' . mysqli_error($conn));
}
echo '连接成功<br />';
$sql = 'DROP DATABASE 数据库名';
$retval = mysqli_query($conn,$sql );
if(! $retval )
{
die('删除数据库失败: ' . mysqli_error($conn));
}
echo "数据库 数据库名 删除成功\n";
mysqli_close($conn);
?>
选择数据库
mysql命令
mysql> use 数据库名;
php脚本
mysqli_select_db(connection,dbname);
- connectio 为建立的mysql连接
- dbname 为数据库名
脚本
<?php
$dbhost = 'localhost:3306'; // mysql服务器主机地址
$dbuser = 'root'; // mysql用户名
$dbpass = '123456'; // mysql用户名密码
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('连接失败: ' . mysqli_error($conn));
}
echo '连接成功';
mysqli_select_db($conn, '数据库名' );
mysqli_close($conn);
?>
数据表管理
复习数据库的结构
创建数据表时，要指明数据表中的各个字段，和不同字段的相关属性
创建数据表
mysql视图
mysql> use 数据库名 ;
Database changed
mysql> create table 数据表名(字段1 字段1的相关属性,字段2 字段2的相关属性,字段3 字段3的相关属性,更多字段和属性);
或者
mysql> create table 数据库名.数据表名(字段1 字段1的相关属性,字段2 字段2的相关属性,字段3 字段3的相关属性,更多字段和属性);
如果字段/数据库名/数据表名含有空格，使用重音符括起来即
create table `数据库名`.`数据表名`(`字段1` 字段1的相关属性,`字段2` 字段2的相关属性,`字段3` 字段3的相关属性,更多字段和属性);
推荐以重音符括起来数据库名和数据表名方式创建
以下所有涉及数据库名和数据表名的命令，都可以加重音符
例1
mysql> use cndaqiangdb ;
Database changed
mysql> create table cn_table(字段1 INT);
例2
| 字段名 | 数字类型 | 数据宽度 | 是否为空 | 是否主键 | 自动增加 | 默认值 |
|---|---|---|---|---|---|---|
| id | int | 4 | 否 | primary key | auto_increment | |
| name | char | 20 | 否 | |||
| sex | int | 4 | 否 | 0 | ||
| degree | double | 16 | 是 |
mysql> create table MyClass(
> id int(4) not null primary key auto_increment,
> name char(20) not null,
> sex int(4) not null default '0',
> degree double(16,2));
php脚本，及下面内容的php脚本，先略
删除数据表
mysql> drop table 数据库名.数据表名;
或
mysql> use 数据库名;
mysql> drop table 数据表名;
修改数据表名
mysql> alter table `数据库名`.`数据表名` rename to `数据库名`.`数据表名`
字段管理
添加字段
alter table `数据库名`.`数据表名` add (`字段3` 属性,`字段4` 属性);
删除字段
alter table `数据库名`.`数据表名` drop `字段名`;
修改字段
alter table `数据库名`.`数据表名` change `旧段名` `新字段名` 修改属性;
记录/数据管理
添加记录
mysql> insert into `数据库名`.`数据表名` (`字段1`,`字段2`,`字段3`,`更多字段`) value (值1,值2,值3,更多值);
如果数据是字符型，必须使用单引号或者双引号，如：”value”。
查询记录/数据
mysql> select `字段1`,`字段2`,`更多字段` from `数据库名`.`数据表名` [ where 条件] [LIMIT n] [OFFSET M] ;
字段1,字段2为显示的字段[ where 条件]可选，展示符合条件的字段，如where `字段1`=`值`[LIMIT n]和[OFFSET M]配合使用，LIMIT确定最多显示的记录数，OFFSET决定从查询到的记录数偏移几个开始显示，默认0，不偏移，从第一个显示
示例
mysql> select `字段1`,`字段2` from `cndaqiangdb`.`table1` where `字段1`=1 limit 2 offset 1;
+-------+-------+
| 字段1 | 字段2 |
+-------+-------+
| 1 | 2 |
| 1 | 2 |
+-------+-------+
2 rows in set (0.00 sec)
删除记录/数据
mysql> delete from `数据库名`.`数据表名` [where 条件];
[where 条件]如果不写，则删除数据表内的所有记录/数据
更新/修改记录
mysql> update `数据库名`.`数据表名` set `字段1`=值1,`字段2`=值2 [where 条件];
[where 条件]如果不写，则替换所有记录的相应字段
批量替换
mysql> update `数据库名`.`数据表名` set `字段`=replace(`字段`,旧值,新值);
可以替换，数字，字符串等，替换字符串时，注意加引号
示例
mysql> select `1`,`2`,`3`,`4` from `cndaqiangdb`.`table1`;
+-----+-----+-----+-----+
| 1 | 2 | 3 | 4 |
+-----+-----+-----+-----+
| 222 | 222 | 333 | 444 |
| 222 | 222 | 3 | 444 |
+-----+-----+-----+-----+
2 rows in set (0.00 sec)
mysql> update `cndaqiangdb`.`table1` set `1`=replace(`1`,2,222);
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select `1`,`2`,`3`,`4` from `cndaqiangdb`.`table1`;
+-----------+-----+-----+-----+
| 1 | 2 | 3 | 4 |
+-----------+-----+-----+-----+
| 222222222 | 222 | 333 | 444 |
| 222222222 | 222 | 3 | 444 |
+-----------+-----+-----+-----+
2 rows in set (0.00 sec)
命令总结
备注
所有在mysql> 后面输入的命令，都可以在phpMyAdmin的SQL选项卡中输入
create
创建用户
mysql> create user 用户名@'host地址' identifide by '密码';
创建数据库
mysql> create database `数据库名`;
创建数据表
mysql> create table `数据库名`.`数据表名` ( `字段1` 属性,`字段2` 属性);
drop
删除数据库，数据表
mysql> drop database `数据库名`;
mysql> drop table `数据库名`.`数据表名`;
use
进入数据库视图，之后本数据库名可以不输入
use `数据库名` ;
alter
数据表重命名
mysql> alter table `数据库名`.`数据表名` rename to `数据库名`.`数据表名`;
添加字段
alter table `数据库名`.`数据表名` add (`字段3` 属性,`字段4` 属性);
删除字段
alter table `数据库名`.`数据表名` drop `字段名`;
修改字段
alter table `数据库名`.`数据表名` change `旧段名` `新字段名` 修改属性;
where
之前很多操作都有where 条件
语法
where 条件1 and/or 条件2
条件子句语法
字段 操作符 数据
操作符有:
=等于<>,!=不等于><>=<=大小关系
update
修改数据
mysql> update `数据库名`.`数据表名` set `字段1`=值1,`字段2`=值2 [where 条件];
delete
删除数据
mysql> delete from `数据库名`.`数据表名` [where 条件];
insert
添加数据
mysql> insert into `数据库名`.`数据表名` (`字段1`,`字段2`,`字段3`,`更多字段`) value (值1,值2,值3,更多值);
select
查询/展示数据
mysql> select `字段1`,`字段2`,`更多字段` from `数据库名`.`数据表名` [ where 条件] [LIMIT n] [OFFSET M] ;
本文首发于我的博客@cndaqiang.
本博客所有文章除特别声明外，均采用 CC BY-SA 4.0 协议 ，转载请注明出处！