U014-【Ubuntu】数据库管理的基本操作案例
学习目标
了解案例任务要求
掌握任务的配置步骤和方法
一、任务要求
为搭建企业内部数据库服务系统，公司购置了一台服务器，预装的是Linux系统，需要在服务器（10.0.X.15/24）上安装和配置数据库（MariaDB）服务，试运行阶段的测试环境配置要求为：
安装和配置MariaDB，支持中文字符集；
服务器本地连接测试，查看字符集、默认存储引擎、系统状态；
授权数据库管理员（root）远程登录连接；
客户端远程登陆数据库服务器，数据库测试操作；
数据库操作：查看已有数据库，创建数据库mycoolege，选择新建数据库，删除数据库；
数据表操作：再次创建并选择数据库mycoolege，创建数据表mystudent，表字段分别为：ID，int，primary key；Name，varchar，10；Birthday，datetime；Sex，char，8；Password，char，128。查看新建数据表，将数据表名称修改为myclass，查看数据表结构及建表语句，删除数据表。
数据记录操作：再次创建数据表mystudent，插入数据记录（1,'张三','1996-7-1','女','sz'；2,'李四','1997-8-2','男','ls'；3,'朱丽叶','1998-9-3','女','zly'；4,'罗米欧','1999-10-4','男','lmo'；5,'王五','2000-11-5','女','ww'；6,'赵六','2001-12-6','男','zl'），查看所有数据记录。修改张三的数据，将姓名改为章山，将第2条数据的姓名修改为李诗，密码改为li4。查询所有数据记录。仅列出所有记录的ID、Name、Sex字段的信息。按ID降序列出所有记录的ID、Name、Sex字段的信息。仅列出ID范围在3到5之间的记录，仅显示它们的ID、Name、Sex字段的信息。删除id为5的数据记录。删除表中所有的记录。
数据备份与还原操作：再次创建数据表mystudent，插入数据记录（1,'张三','1996-7-1','女','sz'；2,'李四','1997-8-2','男','ls'；3,'朱丽叶','1998-9-3','女','zly'；4,'罗米欧','1999-10-4','男','lmo'；5,'王五','2000-11-5','女','ww'；6,'赵六','2001-12-6','男','zl'），备份数据库mycoolege到当前目录下，备份文件名包括数据库的名称和备份日期（例如：mycoolege_2020_10_04.sql），测试数据库在被删除之后，在不登录数据库的情况下进行数据表和数据记录还原。再次删除数据库，使用SOURCE在登录数据库之后进行数据表和数据记录还原。
从外部文件向数据库导入数据操作：新建文件data.txt，内容为（7，晓均，1994-5-7，男，xj；8，文娟，1995-6-8，女，wj），登录数据库，使用LOAD DATA LOCAL INFILE从外部文件中导入数据，查看表中所有的数据记录。
用户管理和授权操作：使用CREATE USER创建用户zhang3并设置密码zhang3，账户可在任意客户端连接数据库；使用GRANT创建用户li4并设置密码li4，账户可在任意客户端连接数据库；使用DROP USER删除用户li4；使用DELETE删除用户zhang3；使用CREATE USER创建用户zhao6，并使用UPDATE设置其密码为123456，账户可在任意客户端连接数据库，授权对所有数据库中所有数据表均能增删改查操作，查看其授权结果；更改数据库管理员（root）的密码；回收用户zhao6的新增数据权限；回收用户zhao6所有操作权限。
二、确认实验环境
1、确认IP地址配置
（1）IP地址配置
root@dbserver:~# cat /etc/netplan/00-installer-config.yaml
# This is the network config written by 'subiquity'
network:
ethernets:
ens32:
addresses:
- 10.0.0.15/24
gateway4: 10.0.0.2
nameservers:
addresses:
- 114.114.114.114
search: []
version: 2
（2）确认地址生效
root@dbserver:~# ifconfig ens32
ens32: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 10.0.0.15 netmask 255.255.255.0 broadcast 10.0.0.255
inet6 fe80::20c:29ff:feb9:63e6 prefixlen 64 scopeid 0x20<link>
ether 00:0c:29:b9:63:e6 txqueuelen 1000 (Ethernet)
RX packets 37577 bytes 26871642 (26.8 MB)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 15690 bytes 1541948 (1.5 MB)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
2、确认服务器软件包已安装
root@dbserver:~# dpkg -l mariadb-* | grep ii
ii mariadb-client 1:10.1.48-0ubuntu0.18.04.1 all MariaDB database client (metapackage depending on the latest version)
ii mariadb-client-10.1 1:10.1.48-0ubuntu0.18.04.1 amd64 MariaDB database client binaries
ii mariadb-client-core-10.1 1:10.1.48-0ubuntu0.18.04.1 amd64 MariaDB database core client binaries
ii mariadb-common 1:10.1.48-0ubuntu0.18.04.1 all MariaDB common metapackage
ii mariadb-server 1:10.1.48-0ubuntu0.18.04.1 all MariaDB database server (metapackage depending on the latest version)
ii mariadb-server-10.1 1:10.1.48-0ubuntu0.18.04.1 amd64 MariaDB database server binaries
ii mariadb-server-core-10.1 1:10.1.48-0ubuntu0.18.04.1 amd64 MariaDB database core server files
三、服务配置
1、修改配置文件
root@dbserver:~# grep -v -E '^$|^#' /etc/mysql/mariadb.conf.d/50-server.cnf | grep -v grep
[server]
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
##更改服务监听端口
bind-address = 0.0.0.0
key_buffer_size = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
myisam_recover_options = BACKUP
query_cache_limit = 1M
query_cache_size = 16M
log_error = /var/log/mysql/error.log
expire_logs_days = 10
max_binlog_size = 100M
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
[embedded]
[mariadb]
[mariadb-10.1]
四、服务启动
1、服务启动
root@dbserver:~# systemctl start mariadb.service
2、服务状态
root@dbserver:~# systemctl status mariadb.service
● mariadb.service - MariaDB 10.1.48 database server
Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
Active: active (running) since Mon 2021-12-20 14:42:49 CST; 5h 47min ago
Docs: man:mysqld(8)
https://mariadb.com/kb/en/library/systemd/
Main PID: 3542 (mysqld)
Status: "Taking your SQL requests now..."
Tasks: 27 (limit: 4629)
CGroup: /system.slice/mariadb.service
└─3542 /usr/sbin/mysqld
3、服务监听端口
root@dbserver:~# netstat -tunlp | grep mysqld
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 5273/mysqld
4、服务进程
root@dbserver:~# ps -ef | grep mysqld | grep -v grep
mysql 3542 1 0 14:42 ? 00:00:21 /usr/sbin/mysqld
5、本地登录
root@dbserver:~# mysql -uroot -p123456
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 49
Server version: 10.1.48-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
6、查看数据库系统状态
（1）查看字符集
MariaDB [(none)]> SHOW VARIABLES LIKE 'character_set_%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
（2）查看默认存储引擎
MariaDB [(none)]> SHOW VARIABLES LIKE 'default_storage_engine';
+------------------------+--------+
| Variable_name | Value |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+
1 row in set (0.00 sec)
（3）查看系统状态
MariaDB [(none)]> \s
--------------
mysql Ver 15.1 Distrib 10.1.48-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
Connection id: 49
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server: MariaDB
Server version: 10.1.48-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
UNIX socket: /var/run/mysqld/mysqld.sock
Uptime: 5 hours 52 min 25 sec
Threads: 1 Questions: 449 Slow queries: 0 Opens: 167 Flush tables: 1 Open tables: 30 Queries per second avg: 0.021
--------------
7、授权root用户远程登录
### WITH GRANT OPTION 这个选项表示该用户可以将自己拥有的权限授权给别人。
MariaDB [(none)]> GRANT ALL ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> EXIT;
Bye
五、数据库操作
1、客户机远程登陆数据库
（1）确认网络连接
（2）确认地址配置
root@client:~# cat /etc/netplan/01-network-manager-all.yaml
# Let NetworkManager manage all devices on this system
network:
ethernets:
ens32:
addresses:
- 10.0.0.202/24
gateway4: 10.0.0.2
nameservers:
addresses:
- 114.114.114.114
version: 2
（3）确认软件包安装
root@client:~# apt install -y mariadb-client
root@client:~# dpkg -l mariadb-* | grep ii
ii mariadb-client 1:10.1.48-0ubuntu0.18.04.1 all MariaDB database client (metapackage depending on the latest version)
ii mariadb-client-10.1 1:10.1.48-0ubuntu0.18.04.1 amd64 MariaDB database client binaries
ii mariadb-client-core-10.1 1:10.1.48-0ubuntu0.18.04.1 amd64 MariaDB database core client binaries
ii mariadb-common 1:10.1.48-0ubuntu0.18.04.1 all MariaDB common metapackage
（4）远程登陆数据库
root@client:~# mysql -h 10.0.0.15 -uroot -p123456
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 30
Server version: 10.1.48-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
2、数据库的操作
（1）查看数据库
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
（2）创建数据库
MariaDB [(none)]> CREATE DATABASE mycoolege;
Query OK, 1 row affected (0.00 sec)
（3）查看建库语句
MariaDB [(none)]> SHOW CREATE DATABASE mycoolege;
+-----------+-----------------------------------------------------------------------+
| Database | Create Database |
+-----------+-----------------------------------------------------------------------+
| mycoolege | CREATE DATABASE `mycoolege` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+-----------+-----------------------------------------------------------------------+
1 row in set (0.00 sec)
（4）选择数据库
MariaDB [(none)]> USE mycoolege;
Database changed
（5）删除数据库
MariaDB [mycoolege]> DROP DATABASE mycoolege;
Query OK, 0 rows affected (0.00 sec)
3、数据表的操作
先决条件
CREATE DATABASE mycoolege;
USE mycoolege;
（1）创建数据表
MariaDB [mycoolege]> CREATE TABLE mystudent (
ID int primary key,
Name varchar(10),
Birthday datetime,
Sex char(8),
Password char(128)
);
Query OK, 0 rows affected (0.13 sec)
（2）查看数据表
MariaDB [mycoolege]> SHOW TABLES;
+---------------------+
| Tables_in_mycoolege |
+---------------------+
| mystudent |
+---------------------+
1 row in set (0.00 sec)
（3）修改数据表名
MariaDB [mycoolege]> RENAME TABLE mystudent TO myclass;
Query OK, 0 rows affected (0.01 sec)
MariaDB [mycoolege]> SHOW TABLES;
+---------------------+
| Tables_in_mycoolege |
+---------------------+
| myclass |
+---------------------+
1 row in set (0.00 sec)
（4）查看数据表结构
MariaDB [mycoolege]> DESC myclass;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| ID | int(11) | NO | PRI | NULL | |
| Name | varchar(10) | YES | | NULL | |
| Birthday | datetime | YES | | NULL | |
| Sex | char(8) | YES | | NULL | |
| Password | char(128) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
（5）查看建表语句
MariaDB [mycoolege]> SHOW CREATE TABLE myclass\G
*************************** 1. row ***************************
Table: myclass
Create Table: CREATE TABLE `myclass` (
`ID` int(11) NOT NULL,
`Name` varchar(10) DEFAULT NULL,
`Birthday` datetime DEFAULT NULL,
`Sex` char(8) DEFAULT NULL,
`Password` char(128) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
（6）删除数据表
MariaDB [mycoolege]> DROP TABLE myclass;
Query OK, 0 rows affected (0.01 sec)
4、数据记录的操作
先决条件
CREATE TABLE mystudent (
ID int primary key,
Name varchar(10),
Birthday datetime,
Sex char(8),
Password char(128)
);
（1）插入数据
## 单条数据添加
MariaDB [mycoolege]> INSERT INTO mystudent (ID,Name,Birthday,Sex,Password) VALUE (1,'张三','1996-7-1','女','sz');
Query OK, 1 row affected (0.00 sec)
MariaDB [mycoolege]> INSERT INTO mystudent (ID,Name,Birthday,Sex,Password) VALUE (2,'李四','1997-8-2','男','ls');
Query OK, 1 row affected (0.01 sec)
## 多条数据添加
MariaDB [mycoolege]> INSERT INTO mystudent (ID,Name,Birthday,Sex,Password) VALUES (3,'朱丽叶','1998-9-3','女','zly'),(4,'罗米欧','1999-10-4','男','lmo');
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [mycoolege]> INSERT INTO mystudent VALUES (5,'王五','2000-11-5','女','ww'),(6,'赵六','2001-12-6','男','zl');
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
## 结果查询
MariaDB [mycoolege]> SELECT * FROM mystudent;
+----+-----------+---------------------+------+----------+
| ID | Name | Birthday | Sex | Password |
+----+-----------+---------------------+------+----------+
| 1 | 张三 | 1996-07-01 00:00:00 | 女 | sz |
| 2 | 李四 | 1997-08-02 00:00:00 | 男 | ls |
| 3 | 朱丽叶 | 1998-09-03 00:00:00 | 女 | zly |
| 4 | 罗米欧 | 1999-10-04 00:00:00 | 男 | lmo |
| 5 | 王五 | 2000-11-05 00:00:00 | 女 | ww |
| 6 | 赵六 | 2001-12-06 00:00:00 | 男 | zl |
+----+-----------+---------------------+------+----------+
6 rows in set (0.00 sec)
（2）修改数据
MariaDB [mycoolege]> UPDATE mystudent SET Name='章山' WHERE name='张三';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [mycoolege]> UPDATE mystudent SET Name='李诗',Password='li4' WHERE id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [mycoolege]> SELECT * FROM mystudent;
+----+-----------+---------------------+------+----------+
| ID | Name | Birthday | Sex | Password |
+----+-----------+---------------------+------+----------+
| 1 | 章山 | 1996-07-01 00:00:00 | 女 | sz |
| 2 | 李诗 | 1997-08-02 00:00:00 | 男 | li4 |
| 3 | 朱丽叶 | 1998-09-03 00:00:00 | 女 | zly |
| 4 | 罗米欧 | 1999-10-04 00:00:00 | 男 | lmo |
| 5 | 王五 | 2000-11-05 00:00:00 | 女 | ww |
| 6 | 赵六 | 2001-12-06 00:00:00 | 男 | zl |
+----+-----------+---------------------+------+----------+
6 rows in set (0.01 sec)
（3）查询数据
##指定字段查询
MariaDB [mycoolege]> SELECT ID,Name,Sex FROM mystudent;
+----+-----------+------+
| ID | Name | Sex |
+----+-----------+------+
| 1 | 章山 | 女 |
| 2 | 李诗 | 男 |
| 3 | 朱丽叶 | 女 |
| 4 | 罗米欧 | 男 |
| 5 | 王五 | 女 |
| 6 | 赵六 | 男 |
+----+-----------+------+
6 rows in set (0.00 sec)
##排序查询
MariaDB [mycoolege]> SELECT ID,Name,Sex FROM mystudent ORDER BY ID DESC;
+----+-----------+------+
| ID | Name | Sex |
+----+-----------+------+
| 6 | 赵六 | 男 |
| 5 | 王五 | 女 |
| 4 | 罗米欧 | 男 |
| 3 | 朱丽叶 | 女 |
| 2 | 李诗 | 男 |
| 1 | 章山 | 女 |
+----+-----------+------+
6 rows in set (0.00 sec)
##区间查询
MariaDB [mycoolege]> SELECT ID,Name,Sex FROM mystudent WHERE ID>=3 AND ID<5;
+----+-----------+------+
| ID | Name | Sex |
+----+-----------+------+
| 3 | 朱丽叶 | 女 |
| 4 | 罗米欧 | 男 |
+----+-----------+------+
2 rows in set (0.00 sec)
（4）删除数据
MariaDB [mycoolege]> DELETE FROM mystudent WHERE id=5;
Query OK, 1 row affected (0.02 sec)
MariaDB [mycoolege]> DELETE FROM mystudent;
Query OK, 5 rows affected (0.01 sec)
MariaDB [mycoolege]> SELECT * FROM mystudent;
Empty set (0.00 sec)
5、数据备份与还原
先决条件
INSERT INTO mystudent VALUES (1,'张三','1996-7-1','女','sz'),(2,'李四','1997-8-2','男','ls'),(3,'朱丽叶','1998-9-3','女','zly'),(4,'罗米欧','1999-10-4','男','lmo'),(5,'王五','2000-11-5','女','ww'),(6,'赵六','2001-12-6','男','zl');
SELECT * FROM mystudent;
+----+-----------+---------------------+------+----------+
| ID | Name | Birthday | Sex | Password |
+----+-----------+---------------------+------+----------+
| 1 | 张三 | 1996-07-01 00:00:00 | 女 | sz |
| 2 | 李四 | 1997-08-02 00:00:00 | 男 | ls |
| 3 | 朱丽叶 | 1998-09-03 00:00:00 | 女 | zly |
| 4 | 罗米欧 | 1999-10-04 00:00:00 | 男 | lmo |
| 5 | 王五 | 2000-11-05 00:00:00 | 女 | ww |
| 6 | 赵六 | 2001-12-06 00:00:00 | 男 | zl |
+----+-----------+---------------------+------+----------+
EXIT;
（1）数据的备份
root@client:~# mysqldump -h 10.0.0.15 -P 3306 -uroot -p123456 mycoolege > ./mycoolege_`date +%F`.sql
root@client:~# ls -l mycoolege_2021-12-21.sql
-rw-r--r-- 1 root root 2306 12月 21 12:38 mycoolege_2021-12-21.sql
（2）数据的外部还原
先决条件
###删除数据库
root@client:~# mysql -h 10.0.0.15 -P 3306 -uroot -p123456 -e 'DROP DATABASE mycoolege;'
###确认已删除数据库
root@client:~# mysql -h 10.0.0.15 -P 3306 -uroot -p123456 -e 'SHOW DATABASES;'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
创库
###还原时不能还原数据库，所以需要手动创建数据库
root@client:~# mysql -h 10.0.0.15 -uroot -p123456 -e 'CREATE DATABASE mycoolege;'
root@client:~# mysql -h 10.0.0.15 -uroot -p123456 -e 'SHOW DATABASES;'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mycoolege |
| mysql |
| performance_schema |
+--------------------+
还原
###通过备份还原数据库中的数据表和数据记录
root@client:~# mysql -h 10.0.0.15 -uroot -p123456 mycoolege < ./mycoolege_2021-12-21.sql
验证
###查看还原后的数据表和数据记录
root@client:~# mysql -h 10.0.0.15 -uroot -p123456 -e 'SELECT * FROM mycoolege.mystudent;'
+----+-----------+---------------------+------+----------+
| ID | Name | Birthday | Sex | Password |
+----+-----------+---------------------+------+----------+
| 1 | 张三 | 1996-07-01 00:00:00 | 女 | sz |
| 2 | 李四 | 1997-08-02 00:00:00 | 男 | ls |
| 3 | 朱丽叶 | 1998-09-03 00:00:00 | 女 | zly |
| 4 | 罗米欧 | 1999-10-04 00:00:00 | 男 | lmo |
| 5 | 王五 | 2000-11-05 00:00:00 | 女 | ww |
| 6 | 赵六 | 2001-12-06 00:00:00 | 男 | zl |
+----+-----------+---------------------+------+----------+
（3）数据的内部还原
先决条件
###删除数据库
root@client:~# mysql -h 10.0.0.15 -uroot -p123456 -e 'DROP DATABASE mycoolege;'
###确认已删除数据库
root@client:~# mysql -h 10.0.0.15 -uroot -p123456 -e 'SHOW DATABASES;'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
登陆数据库
root@client:~# mysql -h 10.0.0.15 -uroot -p123456
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 40
Server version: 10.1.48-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
创库
MariaDB [(none)]> CREATE DATABASE mycoolege;
Query OK, 1 row affected (0.01 sec)
MariaDB [(none)]> USE mycoolege;
Database changed
还原
MariaDB [mycoolege]> SOURCE /root/mycoolege_2021-12-21.sql
验证
MariaDB [mycoolege]> SELECT * FROM mystudent;
+----+-----------+---------------------+------+----------+
| ID | Name | Birthday | Sex | Password |
+----+-----------+---------------------+------+----------+
| 1 | 张三 | 1996-07-01 00:00:00 | 女 | sz |
| 2 | 李四 | 1997-08-02 00:00:00 | 男 | ls |
| 3 | 朱丽叶 | 1998-09-03 00:00:00 | 女 | zly |
| 4 | 罗米欧 | 1999-10-04 00:00:00 | 男 | lmo |
| 5 | 王五 | 2000-11-05 00:00:00 | 女 | ww |
| 6 | 赵六 | 2001-12-06 00:00:00 | 男 | zl |
+----+-----------+---------------------+------+----------+
6 rows in set (0.00 sec)
（4）从外部导入数据
准备文件(字段间的间隔必须用tab键字符)
root@client:~# vim data.txt
7 晓均 1994-5-7 男 xj
8 文娟 1995-6-8 女 wj
登陆数据库
root@client:~# mysql -h 10.0.0.15 -uroot -p123456
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 41
Server version: 10.1.48-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> USE mycoolege;
Database changed
导入
MariaDB [mycoolege]> LOAD DATA LOCAL INFILE '/root/data.txt' INTO TABLE mystudent;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 0
验证
MariaDB [mycoolege]> SELECT * FROM mystudent;
+----+-----------+---------------------+------+----------+
| ID | Name | Birthday | Sex | Password |
+----+-----------+---------------------+------+----------+
| 1 | 张三 | 1996-07-01 00:00:00 | 女 | sz |
| 2 | 李四 | 1997-08-02 00:00:00 | 男 | ls |
| 3 | 朱丽叶 | 1998-09-03 00:00:00 | 女 | zly |
| 4 | 罗米欧 | 1999-10-04 00:00:00 | 男 | lmo |
| 5 | 王五 | 2000-11-05 00:00:00 | 女 | ww |
| 6 | 赵六 | 2001-12-06 00:00:00 | 男 | zl |
| 7 | 晓均 | 1994-05-07 00:00:00 | 男 | xj |
| 8 | 文娟 | 1995-06-08 00:00:00 | 女 | wj |
+----+-----------+---------------------+------+----------+
8 rows in set (0.00 sec)
6、账户管理和授权
（1）创建普通用户
使用CREATE USER创建用户
MariaDB [(none)]> CREATE USER 'zhang3'@'%' IDENTIFIED BY 'zhang3';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SELECT Host,User,Password FROM mysql.user WHERE User='zhang3';
+------+--------+-------------------------------------------+
| Host | User | Password |
+------+--------+-------------------------------------------+
| % | zhang3 | *6A72C8F2A38111C56BB2E412C96F1A62B4C6B122 |
+------+--------+-------------------------------------------+
1 row in set (0.00 sec)
使用GRANT创建用户
MariaDB [(none)]> GRANT SELECT ON mycoolege.* TO 'li4'@'%' IDENTIFIED BY 'li4';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SELECT Host,User,Password FROM mysql.user WHERE User='li4';
+------+------+-------------------------------------------+
| Host | User | Password |
+------+------+-------------------------------------------+
| % | li4 | *12634B947FABC16FA3B517C11786CCE477D5D5D1 |
+------+------+-------------------------------------------+
1 row in set (0.00 sec)
（2）删除普通用户
使用DROP USER删除用户
MariaDB [(none)]> DROP USER 'li4'@'%';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SELECT Host,User,Password FROM mysql.user WHERE User='li4';
Empty set (0.00 sec)
使用DELETE删除用户
MariaDB [(none)]> DELETE FROM mysql.user WHERE Host='%' AND User='zhang3';
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> SELECT Host,User,Password FROM mysql.user WHERE User='zhang3';
Empty set (0.01 sec)
（3）修改用户密码
MariaDB [(none)]> CREATE USER 'zhao6'@'%';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> UPDATE mysql.user SET Password=123456 WHERE User='zhao6' and Host='%';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [(none)]> SELECT Host,User,Password FROM mysql.user WHERE User='zhao6';
+------+-------+----------+
| Host | User | Password |
+------+-------+----------+
| % | zhao6 | 123456 |
+------+-------+----------+
1 row in set (0.00 sec)
（4）授予用户权限
MariaDB [(none)]> GRANT SELECT,INSERT,UPDATE,DELETE ON *.* TO 'zhao6'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SELECT *FROM mysql.user WHERE User='zhao6'\G
*************************** 1. row ***************************
Host: %
User: zhao6
Password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin:
authentication_string:
password_expired: N
is_role: N
default_role:
max_statement_time: 0.000000
1 row in set (0.00 sec)
（5）查看用户权限
MariaDB [(none)]> SHOW GRANTS FOR 'zhao6'@'%'\G
*************************** 1. row ***************************
Grants for zhao6@%: GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'zhao6'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'
1 row in set (0.00 sec)
（6）修改root密码
使用mysqladmin修改
root@client:~# mysqladmin -h 10.0.0.15 -uroot -p password
Enter password: 123456
New password: root
Confirm new password: root
使用GRANT修改
root@client:~# mysql -h 10.0.0.15 -u root -proot
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 49
Server version: 10.1.48-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> GRANT ALL ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> EXIT;
Bye
（7）收回用户权限
收回部分权限
MariaDB [(none)]> REVOKE INSERT ON *.* FROM 'zhao6'@'%';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SHOW GRANTS FOR 'zhao6'@'%'\G
*************************** 1. row ***************************
Grants for zhao6@%: GRANT SELECT, UPDATE, DELETE ON *.* TO 'zhao6'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'
1 row in set (0.00 sec)
收回全部权限
MariaDB [(none)]> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'zhao6'@'%';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SHOW GRANTS FOR 'zhao6'@'%'\G
*************************** 1. row ***************************
Grants for zhao6@%: GRANT USAGE ON *.* TO 'zhao6'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'
1 row in set (0.00 sec)