表操作
[root@server ~ 09:57:55]# yum install -y mariadb-server [root@client ~ 09:59:22]# yum install -y mariadb [root@server ~ 09:59:00]# systemctl enable mariadb --now #注册设置。。。 [root@server ~ 10:00:40]# mysql_secure_installation [root@server ~ 10:02:15]# mysql -u root -p #授予所有权限给lyk，此用户可以从任意端登录 MariaDB [(none)]> grant all privileges on *.* to lyk@'%' identified by '123'; Query OK, 0 rows affected (0.00 sec) [root@client ~ 09:59:22]# mysql -u lyk -p123 -h server ERROR 2005 (HY000): Unknown MySQL server host 'server' (2) [root@client ~ 10:07:16]# mysql -u lyk -p123 -h 10.1.8.10 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 12 Server version: 5.5.68-MariaDB MariaDB Server 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)]> quit Bye #client端编辑添加 [root@client ~ 10:21:15]# vim /etc/my.cnf.d/client.cnf [client] user=lyk password=123 host=10.1.8.10 port=3306 [root@client ~ 10:23:52]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 16 Server version: 5.5.68-MariaDB MariaDB Server 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 中 SQL
[root@server ~ 10:34:19]# mysql -uroot -p123 MariaDB [(none)]> CREATE DATABASE inventory; Query OK, 1 row affected (0.00 sec) [root@server ~ 10:36:27]# rz -E rz waiting to receive. [root@server ~ 10:36:33]# ls inventory.dump inventory.dump [root@server ~ 10:36:41]# mysql -uroot -p123 inventory < inventory.dump [root@server ~ 10:38:16]# mysql -uroot -p123 MariaDB [(none)]> show databases; MariaDB [(none)]> USE inventory;
查询表
#查询表列表 MariaDB [inventory]> SHOW TABLES; +---------------------+ | Tables_in_inventory | +---------------------+ | category | | manufacturer | | product | +---------------------+ 3 rows in set (0.00 sec) #查询表结构 MariaDB [inventory]> DESCRIBE product; +-----------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(100) | NO | | NULL | | | price | double | NO | | NULL | | | stock | int(11) | NO | | NULL | | | id_category | int(11) | NO | | NULL | | | id_manufacturer | int(11) | NO | | NULL | | +-----------------+--------------+------+-----+---------+----------------+ 6 rows in set (0.00 sec) #**查询表中所有记录所有字段** MariaDB [inventory]> select * from product; +----+-------------------+---------+-------+-------------+-----------------+ | id | name | price | stock | id_category | id_manufacturer | +----+-------------------+---------+-------+-------------+-----------------+ | 1 | ThinkServer TS140 | 539.88 | 20 | 2 | 4 | | 2 | ThinkServer RD630 | 2379.14 | 20 | 2 | 4 | | 3 | RT-AC68U | 219.99 | 10 | 1 | 3 | | 4 | X110 64GB | 73.84 | 100 | 3 | 1 | +----+-------------------+---------+-------+-------------+-----------------+ 4 rows in set (0.00 sec) MariaDB [inventory]> SELECT * FROM category; +----+------------+ | id | name | +----+------------+ | 1 | Networking | | 2 | Servers | | 3 | Ssd | +----+------------+ 3 rows in set (0.00 sec) MariaDB [inventory]> SELECT * FROM manufacturer; +----+----------+----------------+-------------------+ | id | name | seller | phone_number | +----+----------+----------------+-------------------+ | 1 | SanDisk | John Miller | +1 (941) 329-8855 | | 2 | Kingston | Mike Taylor | +1 (341) 375-9999 | | 3 | Asus | Wilson Jackson | +1 (432) 367-8899 | | 4 | Lenovo | Allen Scott | +1 (876) 213-4439 | +----+----------+----------------+-------------------+ 4 rows in set (0.00 sec) #**查询表中所有记录特定字段** MariaDB [inventory]> SELECT name,price,stock FROM product; +-------------------+---------+-------+ | name | price | stock | +-------------------+---------+-------+ | ThinkServer TS140 | 539.88 | 20 | | ThinkServer RD630 | 2379.14 | 20 | | RT-AC68U | 219.99 | 10 | | X110 64GB | 73.84 | 100 | +-------------------+---------+-------+ 4 rows in set (0.00 sec)
WHERE子句
# 条件操作符包括：=、<>、>、<、>=、<=
MariaDB [inventory]> SELECT * FROM product WHERE price > 100;
# BETWEEN，匹配2个数字之间（包括数字本身）的记录。
MariaDB [inventory]> SELECT * FROM product WHERE id BETWEEN 1 AND 3;
# IN，匹配列表中记录
MariaDB [inventory]> SELECT * FROM product WHERE id IN (1,3);
MariaDB [inventory]> SELECT * FROM category WHERE name IN ('Servers','Ssd');
# LIKE，用于匹配字符串。%表示一个或多个字符，_表示一个字符，[charlist]表示字符列中的任何单一字符，[^charlist]或者[!charlist]不在字符列中的任何单一字符
MariaDB [inventory]> SELECT * FROM product WHERE name like '%Server%';
# 逻辑与AND
MariaDB [inventory]> SELECT * FROM product WHERE name like '%RD%' AND price>1000;
# 逻辑或or
MariaDB [inventory]> SELECT * FROM product WHERE name like '%RD%' OR price>500;
# ORDER BY 关键字用于对结果集进行排序
MariaDB [inventory]> SELECT * FROM product ORDER BY price;
MariaDB [inventory]> SELECT * FROM product ORDER BY price desc;
多表查询
#产品类型是Servers的产品名称和价格 MariaDB [inventory]> SELECT product.name,product.price -> FROM product,category -> WHERE product.id_category = category.id -> AND category.name='Servers'; #查询厂商是Lenovo的产品名称和价格 MariaDB [inventory]> SELECT product.name,product.price -> FROM product,manufacturer -> WHERE product.id_manufacturer = manufacturer.id -> AND manufacturer.name='Lenovo';
函数
#查询产品价格平均值 MariaDB [inventory]> SELECT avg(price) FROM product; #查询产品价格最大值 MariaDB [inventory]> SELECT max(price) FROM product; #查询产品价格最小值 MariaDB [inventory]> SELECT min(price) FROM product; #查询产品存量 MariaDB [inventory]> SELECT sum(stock) FROM product; #查询产品价格最小值的那个产品信息 MariaDB [inventory]> SELECT min(price) FROM product; MariaDB [inventory]> SELECT * FROM product WHERE price=73.84; MariaDB [inventory]> SELECT * FROM product WHERE price=(SELECT min(price) FROM product); #查询Lenovo厂商提供了几种产品 MariaDB [inventory]> SELECT count(product.name) -> FROM product,manufacturer -> WHERE product.id_manufacturer = manufacturer.id -> AND manufacturer.name='Lenovo'; #GROUP BY 语句用于结合聚合函数，根据一个或多个列对结果集进行分组 MariaDB [inventory]> SELECT id_category,sum(stock) FROM product GROUP BY id_category;
创建表
MariaDB [inventory]> CREATE TABLE staff( -> id INT(11) NOT NULL, -> name VARCHAR(100) NOT NULL, -> age INT(11) DEFAULT 10, -> id_department INT(11) -> ); Query OK, 0 rows affected (0.01 sec)
插入记录
MariaDB [inventory]> INSERT INTO staff (id,name,age,id_department) -> VALUES (1,'aaa',11,1111); Query OK, 1 row affected (0.00 sec) MariaDB [inventory]> INSERT INTO staff (id,name,age,id_department) VALUES (1,'bbb',22,2222); Query OK, 1 row affected (0.00 sec) MariaDB [inventory]> INSERT INTO staff (id,name,age,id_department) VALUES (1,'ccc',33,3333); Query OK, 1 row affected (0.00 sec)
更新记录
MariaDB [inventory]> UPDATE staff SET age=11 WHERE id=1; Query OK, 2 rows affected (0.00 sec) Rows matched: 3 Changed: 2 Warnings: 0
如果使用不带WHERE子句的UPDATE，则表中的所有记录都会更新
删除记录
MariaDB [inventory]> DELETE FROM staff WHERE id=1 ; Query OK, 3 rows affected (0.00 sec)
删除表
MariaDB [inventory]> DROP TABLE staff ;
管理 MariaDB 用户
创建用户账户
MariaDB [(none)]> CREATE USER lyk@'%' IDENTIFIED BY '123'; Query OK, 0 rows affected (0.00 sec)
控制用户权限
MariaDB [(none)]> SELECT USER(); +---------------+ | USER() | +---------------+ | lyk@localhost | +---------------+ 1 row in set (0.00 sec) MariaDB [(none)]> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | +--------------------+ 1 row in set (0.00 sec) MariaDB [(none)]> USE mysql; ERROR 1044 (42000): Access denied for user 'lyk'@'%' to database 'mysql' MariaDB [(none)]> CREATE DATABASE inventory; ERROR 1044 (42000): Access denied for user 'lyk'@'%' to database 'inventory'
查询用户权限(root权限)
[root@server ~ 13:54:14]# mysql -uroot -p MariaDB [(none)]> SHOW GRANTS FOR root@localhost; +----------------------------------------------------------------------------------------------------------------------------------------+ | Grants for root@localhost | +----------------------------------------------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' WITH GRANT OPTION | | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION | +----------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
授予用户权限(root下执行)
MariaDB [(none)]> GRANT SELECT, UPDATE, DELETE, INSERT -> ON inventory.category -> TO lyk@localhost; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> exit Bye
验证权限
[root@server ~ 14:01:33]# mysql -u lyk -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 30 Server version: 5.5.68-MariaDB MariaDB Server 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 inventory; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MariaDB [inventory]> SELECT * FROM category; +----+------------+ | id | name | +----+------------+ | 1 | Networking | | 2 | Servers | | 3 | Ssd | +----+------------+ 3 rows in set (0.01 sec)
回收用户权限
REVOKE SELECT, UPDATE, DELETE, INSERT ON inventory.category FROM 'lyk'@'%';
删除用户
MariaDB [(none)]> DROP USER lyk@localhost;
更改用户密码
# root用户修改普通用户账户密码
MariaDB [(none)]> USE mysql;
MariaDB [(mysql)]> UPDATE user SET password=PASSWORD('mypass') WHERE user='lyk' and host=’localhost’;
# 或者
MariaDB [(none)]> SET PASSWORD FOR 'lyk'@'localhost' = PASSWORD('mypass');
# 普通用户修改自己账户密码
MariaDB [(none)]> SET PASSWORD = PASSWORD('mypass');
MariaDB [(none)]> FLUSH PRIVILEGES;
忘记 root 用户密码
[root@server ~]# mysql -u root
MariaDB [(none)]> UPDATE mysql.user SET password=PASSWORD('新密码') where USER='root';
MariaDB [(none)]> exit
执行备份
执行物理备份
#备份
[root@server ~ 14:46:04]# systemctl stop mariadb
[root@server ~ 14:46:14]# ls /var/lib/mysql*
aria_log.00000001 ibdata1 ib_logfile1 mysql
aria_log_control ib_logfile0 inventory performance_schema
[root@server ~ 14:47:53]# cp -r /var/lib/mysql{,.back}
[root@server ~ 14:48:49]# ls /var/lib/mysql*
/var/lib/mysql:
aria_log.00000001 ibdata1 ib_logfile1 mysql
aria_log_control ib_logfile0 inventory performance_schema
/var/lib/mysql.back:
aria_log.00000001 ibdata1 ib_logfile1 mysql
aria_log_control ib_logfile0 inventory performance_schema
[root@server ~ 14:48:52]# systemctl start mariadb
[root@server ~ 14:49:48]# mysql -uroot -p123
MariaDB [(none)]> drop user root@localhost;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> drop user root@127.0.0.1;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> quit
恢复
[root@server ~ 14:51:30]# systemctl stop mariadb #发现不能进入 [root@server ~ 14:52:22]# mysql -uroot -p123 ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) #查看权限，权限不对执行以下命令 [root@server ~ 14:53:04]# ll /var/lib/mysql/mysql chmod 660 /var/lib/mysql/mysql/user.* chown mysql:mysql /var/lib/mysql/mysql/user.* 改为： -rw-rw---- 1 mysql mysql 10630 8月 8 14:52 user.frm -rw-rw---- 1 mysql mysql 532 8月 8 14:52 user.MYD -rw-rw---- 1 mysql mysql 2048 8月 8 14:52 user.MYI #重启查看服务 [root@server ~ 14:53:09]# systemctl start mariadb [root@server ~ 14:54:54]# mysql -uroot -p123 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 2 Server version: 5.5.68-MariaDB MariaDB Server 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)]>