数据库的基本操作

Mariadb简介
MariaDB数据库管理系统是MySQL的一个分支，主要由开源社区在维护，采用GPL授权许可MariaDB的目的是完全兼容MySQL，包括API和命令行，是MySQL的替代品。
1.安装数据库
[root@dns-server named]# yum search mariadb
[root@dns-server named]# yum install mariadb-server.x86_64 下载mariadb

[root@dns-server named]# mysql_secure_installation 做好安全设定


[root@dns-server named]# vim /etc/my.cnf
11 skip-networking=1

[root@dns-server named]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 5.5.35-MariaDB MariaDB Server
Copyright © 2000, 2013, Oracle, Monty Program Ab and others.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

2.数据库的管理
1）建立

SHOW DATABASES；
CREATE DATABASE kk；
USE kk；

CREATE TABLE linux（
-> username varchar(10) not null,
-> password varchar(50) not null
-> );

DESC linux;

INSERT INTO linuxVALUES (‘BABA’,‘123’);
Query OK, 1 row affected (0.00 sec)
MariaDB [kk]> SELECT * FROM linux;
±---------±---------+
| username | password |
±---------±---------+
| BABA | 123 |
±---------±---------+
1 row in set (0.00 sec)

MariaDB [kk]> INSERT INTO linux VALUES (‘yeye’,‘kk’);
Query OK, 1 row affected (0.01 sec)
MariaDB [kk]> SELECT username,password FROM linux;
±---------±---------+
| username | password |
±---------±---------+
| BABA | 123 |
| yeye | kk |
±---------±---------+
2 rows in set (0.00 sec)

2）更改文件

MariaDB [kk]> ALTER TABLE linux ADD class varchar(20);
Query OK, 2 rows affected (0.05 sec)
Records: 2 Duplicates: 0 Warnings: 0

MariaDB [kk]> SELECT * FROM linux
-> ;
±---------±---------±------+
| username | password | class |
±---------±---------±------+
| BABA | 123 | NULL |
| yeye | kk | NULL |
±---------±---------±------+
2 rows in set (0.00 sec)
##添加class字段到文件最后面

MariaDB [kk]> ALTER TABLE linux DROP class;
Query OK, 2 rows affected (0.07 sec)
Records: 2 Duplicates: 0 Warnings: 0

MariaDB [kk]> SELECT * FROM linux
-> ;
±---------±---------+
| username | password |
±---------±---------+
| BABA | 123 |
| yeye | kk |
±---------±---------+
2 rows in set (0.00 sec)

MariaDB [kk]> ALTER TABLE linux ADD age varchar(20) AFTER username;
Query OK, 2 rows affected (0.05 sec)
Records: 2 Duplicates: 0 Warnings: 0

MariaDB [kk]> SELECT * FROM linux
-> ;
±---------±-----±---------+
| username | age | password |
±---------±-----±---------+
| BABA | NULL | 123 |
| yeye | NULL | kk |
±---------±-----±---------+
2 rows in set (0.00 sec)
##添加age字段到username之后，不能添加到最前面，因为设置第一字段为基准点，如果添加到第一字段前文件内容会发生错乱

MariaDB [kk]> UPDATE linux SET age=(‘40’) where username=‘BABA’;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [kk]> SELECT * FROM linux
-> ;
±---------±-----±---------+
| username | age | password |
±---------±-----±---------+
| BABA | 40 | 123 |
| yeye | NULL | kk |
±---------±-----±---------+
2 rows in set (0.00 sec)

MariaDB [kk]> UPDATE linux SET age=(‘60’) where username=‘yeye’;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [kk]> SELECT * FROM linux
-> ;
±---------±-----±---------+
| username | age | password |
±---------±-----±---------+
| BABA | 40 | 123 |
| yeye | 60 | kk |
±---------±-----±---------+
2 rows in set (0.00 sec)
##单独更改文件内容 对应username对应age

3)备份文件

[root@dns-server named]# mysqldump -u root -p kk > /mnt/westos.sql
Enter password: ##将数据备份到/mnt目录下

然后执行删除kk
MariaDB [(none)]> DROP DATABASE kk; ##将kk数据库删除
Query OK, 1 row affected (0.01 sec)

MariaDB [(none)]> SHOW DATABASES;
±-------------------+
| Database |
±-------------------+
| information_schema |
| mysql |
| performance_schema |
±-------------------+
3 rows in set (0.00 sec)

方法1：vim /mnt/westos.sql
CREATE DATABASE kk;
USE kk;

[root@dns-server mnt]# mysql -u root -p < /mnt/westos.sql
Enter password:


方法2：
vim /mnt/westos.sql
注释掉刚才写好的两行
[root@dns-server mnt]# mysql -u root -p -e “CREATE DATABASE kk;” ##创建database
Enter password:
[root@dns-server mnt]# mysql -u root -p kk < /mnt/westos.sql
Enter password:
将westos.sql导入到kk中

4）删除文件

DELETE FROM redhat where username=‘BABA’;
DROP TABLE linux;
DROP DATABASE kk;

5)用户授权

MariaDB [(none)]> CREATE USER kk@‘localhost’ identified by ‘westos’;##建立kk用户
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> GRANT SELECT,INSERT on kk.* TO kk@localhost;
Query OK, 0 rows affected (0.00 sec) ##给用户添加授权

MariaDB [(none)]> SHOW GRANTS FOR kk@localhost; ##查看用户拥有的权力 ‘’
±----------------------------------------------------------------------------------------------------------+
| Grants for kk@localhost |
±----------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON . TO ‘kk’@‘localhost’ IDENTIFIED BY PASSWORD '86395FF05A68EFE06838C93D89A625211ED65961’ |
| GRANT SELECT, INSERT ON kk. TO ‘kk’@‘localhost’ |
±----------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;##刷新
Query OK, 0 rows affected (0.00 sec)

测试：登陆kk用户

[root@dns-server ~]# mysql -u kk -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 23
Server version: 5.5.35-MariaDB MariaDB Server

Copyright © 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;
±-------------------+
| Database |
±-------------------+
| information_schema |
| kk |##能够看到kk DATABASE
±-------------------+
2 rows in set (0.00 sec)

Database changed

MariaDB [kk]> SELECT * FROM linux;
±---------±-----±---------+
| username | age | password |
±---------±-----±---------+
| BABA | 40 | 123 |
| yeye | 60 | kk |
±---------±-----±---------+
2 rows in set (0.00 sec)

MariaDB [(none)]> USE kk; ##进入kk
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 [kk]> INSERT INTO linux VALUES (‘LEE’,‘java’,‘123’); ##添加一份数据进去
Query OK, 1 row affected (0.00 sec)

MariaDB [kk]> SELECT * FROM linux;
±---------±-----±---------+
| username | age | password |
±---------±-----±---------+
| BABA | 40 | 123 |
| yeye | 60 | kk |
| LEE | java | 123 |
±---------±-----±---------+##能够看到数据添加成功
3 rows in set (0.00 sec)

3.图形方式处理数据库
1.下载
在软件仓库中下载一个
phpMyAdmin-3.4.0-all-languages.tar.b软件包

2.[root@dns-server mnt]# yum install php php-mysql -y

3.system restart httpd

4.[root@dns-server mnt]# tar jxf phpMyAdmin-3.4.0-all-languages.tar.bz2 -C /var/www/htmlctl ##解压缩软件包

5.在/var/www/html中新建一个目录/mysqladmin
[root@dns-server html]# mv phpMyAdmin-3.4.0-all-languages/ mysqladmin

测试：
在firefox中输入：http：//172.25.254.115/mysqladmin


图形方式处理数据库相比较命令方式更为简单且方便。