######数据库mariadb#########
*******基本操作************
mysql_secure_installation ##配置向导
mysql -uroot -p ##登陆数据库
Enter password: ##输入密码
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 5.5.35-MariaDB MariaDB Server
Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
SHOW DATABASES; ##显示数据库
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
USE mysql; ##进入数据库
SHOW tables; ##显示数据库中的表
DROP DATABASE westos; ##删除数据库
SELECT * FROM mysql.user ##查看数据库中user表中的所有内容
********创建、删除数据库*************
MariaDB [mysql]> CREATE DATABASE westos; ##创建数据库westos
Query OK, 1 row affected (0.00 sec)
MariaDB [mysql]> USE westos; ##进入数据库
Database changed
MariaDB [westos]> CREATE TABLE linux( ##创建表
-> username varchar(15) not null, ##用户名字符长度最多为15
-> password varchar(15) not null ##密码
-> );
Query OK, 0 rows affected (0.17 sec)
MariaDB [westos]> DESC linux ##查看表的数据结构
-> ;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(15) | NO | | NULL | |
| password | varchar(15) | NO | | NULL | |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
MariaDB [westos]> INSERT INTO linux VALUES ('USER1','123'); ##向linux表中添加数据user1,密码为123
Query OK, 1 row affected (0.01 sec)
MariaDB [westos]> SELECT * FROM linux;
+----------+----------+
| username | password |
+----------+----------+
| USER1 | 123 |
+----------+----------+
1 row in set (0.00 sec)
MariaDB [westos]> ALTER TABLE linux ADD age varchar(4); ##在表后面添加age字段
Query OK, 1 row affected (0.07 sec)
Records: 1 Duplicates: 0 Warnings: 0
MariaDB [westos]> SELECT * FROM linux;
+----------+----------+------+
| username | password | age |
+----------+----------+------+
| USER1 | 123 | NULL |
+----------+----------+------+
1 row in set (0.00 sec)
MariaDB [westos]> ALTER TABLE linux DROP age; ##删除age字段
Query OK, 1 row affected (0.09 sec)
Records: 1 Duplicates: 0 Warnings: 0
MariaDB [westos]> ALTER TABLE linux ADD age varchar(4) AFTER username; ##将age字段添加到username字段之后
Query OK, 1 row affected (0.06 sec)
Records: 1 Duplicates: 0 Warnings: 0
MariaDB [westos]> SELECT * FROM linux;
+----------+------+----------+
| username | age | password |
+----------+------+----------+
| USER1 | NULL | 123 |
+----------+------+----------+
1 row in set (0.00 sec)
MariaDB [westos]> update linux set password='456' where username='user1'; ##将user1对应的密码改为456
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [westos]> SELECT * FROM linux;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 456 |
+----------+----------+
1 row in set (0.00 sec)
MariaDB [westos]> INSERT INTO linux VALUES ('user2','123');
Query OK, 1 row affected (0.02 sec)
MariaDB [westos]> SELECT * FROM linux;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 456 |
| user2 | 123 |
+----------+----------+
2 rows in set (0.00 sec)
MariaDB [westos]> DELETE FROM linux WHERE username='user1'; ##删除用户为user1的一行
Query OK, 1 row affected (0.01 sec)
MariaDB [westos]> SELECT * FROM linux;
+----------+----------+
| username | password |
+----------+----------+
| user2 | 123 |
+----------+----------+
1 row in set (0.00 sec)
MariaDB [westos]> DROP TABLE linux; ##删除linux表格
Query OK, 0 rows affected (0.01 sec)
********备份mysqldump**************
数据文件默认保存目录 /var/lib/mysql
[root@localhost ~]# mysqldump -uroot -pwestos mysql > /mnt/mysql.sql ##将mysql备份到/mnt/mysql.sql中
[root@localhost ~]# mysql -uroot -pwestos -e "DROP DATABASE westos;" ##直接删除westos数据库
[root@localhost ~]# mysql -uroot -pwestos -e "SHOW DATABASES;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
[root@localhost ~]# mysql -uroot -pwestos mysql < /mnt/mysql.sql ##恢复备份
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 18
Server version: 5.5.35-MariaDB MariaDB Server
Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
