（一）linux基于rhel7.0数据库的基础使用

一，数据库的安装

1，安装mariadb-server软件包，开启服务，关闭防火墙

[root@localhost ~]# yum install mariadb-server -y
[root@localhost ~]# systemctl start mariadb
[root@localhost ~]# systemctl stop firewalld

2，查看数据库端口，默认是打开状态

[root@localhost ~]# netstat -antlpe | grep mysql
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      27         57059      3356/mysqld         

3，修改主配置文件，不裸露网络接口，重启，再次查看

[root@localhost ~]# vim /etc/my.cnf

# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
skip-networking=1                                             <<<<设置跳过网络
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log

[root@localhost ~]# systemctl restart mariadb
[root@localhost ~]# netstat -antlpe | grep mysql      <<<<没有接口了

4，默认状态下直接输入mysql就可以登陆了，不安全，进行安全初始化

[root@dns-server ~]# mysql_secure_installation 
/usr/bin/mysql_secure_installation: line 379: find_mysql_client: command not found

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

Set root password? [Y/n]
New password:                         <<<<<<<<<输入两次密码之后就一直enter 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] 
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] 
 ... Success!

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] 
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] 
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

5，尝试登陆

[root@localhost ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
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)]> quit;
Bye

注意：mysql -uroot -predhat也可以登陆，但是这样就明文显示密码了

二，数据库的基本查看功能

1，显示所有库

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

2，使用库

MariaDB [(none)]> USE mysql;
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 [mysql]> 

4，查看数据表

MariaDB [mysql]> SHOW TABLES;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
24 rows in set (0.00 sec)

4，查看某一数据表的具体内容

MariaDB [mysql]> SELECT * FROM user;   (内容太多，不允显示)

5，查看数据表的所有字段及属性

+------------------------+-----------------------------------+------+-----+---------+-------+
| Field                  | Type                              | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host                   | char(60)                          | NO   | PRI |         |       |
| User                   | char(16)                          | NO   | PRI |         |       |
| Password               | char(41)                          | NO   |     |         |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N       |       |

6，查看某一数据表的某几个字段的内容

MariaDB [mysql]> SELECT User,Host,Password FROM user
    -> ;                                                 <<<<只要没有；就表示没有结束
+------+-----------+-------------------------------------------+
| User | Host      | Password                                  |
+------+-----------+-------------------------------------------+
| root | localhost | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| root | 127.0.0.1 | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| root | ::1       | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
+------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)

7，显示满足某条件的数据内容

MariaDB [mysql]> SELECT User,Host,Password FROM user Where User='root';     <<<<在user这个数据表里面查看User这个字段属性为root的User,Host,Password的字段的内容
+------+-----------+-------------------------------------------+
| User | Host      | Password                                  |
+------+-----------+-------------------------------------------+
| root | localhost | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| root | 127.0.0.1 | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| root | ::1       | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
+------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)

MariaDB [(none)]> SELECT User,Host,Password FROM mysql.user WHERE User='root' AND Host='localhost'; <<<<AND的用法
+------+-----------+-------------------------------------------+
| User | Host      | Password                                  |
+------+-----------+-------------------------------------------+
| root | localhost | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
+------+-----------+-------------------------------------------+
1 row in set (0.00 sec)

注意：数据内容即有效值一定要引号引起来！！！！！！！！！！！！

注意：这里面的语句不区分大小写，但是一般按规范最好区分，单双引号也不区分；

三，数据库的基本创建功能

1，创建一个库

MariaDB [mysql]> CREATE DATABASE westos;
Query OK, 1 row affected (0.00 sec)

MariaDB [mysql]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| westos             |
+--------------------+
4 rows in set (0.00 sec)

2，创建一个数据表，并加入字段

MariaDB [westos]> show tables;
Empty set (0.00 sec)

MariaDB [westos]> create table linux(                 <<<<<分行写的好处是如果报错便于插错
    -> username varchar(6) not null,                  <<<<<username这个字段数据类型是varchar,不超过6个字符，不允许为空
    -> password varchar(50) not null);                <<<<<password这个字段数据类型是varchar,不超过50个字符，不允许为空
Query OK, 0 rows affected (0.18 sec)

MariaDB [westos]> show tables;
+------------------+
| Tables_in_westos |
+------------------+
| linux            |
+------------------+
1 row in set (0.00 sec)

MariaDB [westos]> DESC linux;                           <<<<<<<显示数据表的字段及其属性要求
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(6)  | NO   |     | NULL    |       |
| password | varchar(50) | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

3，在数据表中写入内容

MariaDB [westos]> insert into linux values ( 'zm', 'redhat');      <<<<<<values的括号内就是两个字段的属性值，还要引起来
Query OK, 1 row affected (0.06 sec)

MariaDB [westos]> select * from linux;
+----------+----------+
| username | password |
+----------+----------+
| zm       | redhat   |
+----------+----------+
1 row in set (0.00 sec)

四，数据库的基本修改功能

注意：数据库名和数据表名一般不做修改，防止出现错乱。

1，修改数据库名

[root@localhost ~]# cd /var/lib/mysql/
[root@localhost mysql]# ls
aria_log.00000001  ibdata1      ib_logfile1  mysql.sock          westos<<<<<这个就是我们自己建立的
aria_log_control   ib_logfile0  mysql        performance_schema
[root@localhost mysql]# mv westos hello                                <<<<<改名字
[root@localhost mysql]# systemctl restart mariadb                      <<<<<重启
[root@localhost mysql]# mysql -uroot -predhat               
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
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;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hello              |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]> quit;
Bye

注意：也可以不重启，进入数据库之后，执行刷新命令flush privileges

2，修改数据表名

MariaDB [westos]> show tables;
+------------------+
| Tables_in_westos |
+------------------+
| linux            |
+------------------+
1 row in set (0.00 sec)

MariaDB [westos]> alter table linux rename message;         <<<<<<<<<注意格式
Query OK, 0 rows affected (0.02 sec)

MariaDB [westos]> show tables;
+------------------+
| Tables_in_westos |
+------------------+
| message          |
+------------------+
1 row in set (0.00 sec)

3，在数据表里面加入字段

MariaDB [westos]> alter table message add age varchar(4);      <<<<<向message数据表里面加入age类型为varchar，最长不超过4的字段
Query OK, 1 row affected (0.16 sec)                
Records: 1  Duplicates: 0  Warnings: 0

MariaDB [westos]> select * from message;
+----------+----------+------+
| username | password | age  |
+----------+----------+------+
| zm       | redhat   | NULL |
+----------+----------+------+
1 row in set (0.00 sec)

注意：这个默认加载最后面，指定位置的方法如下

MariaDB [westos]> alter table message add classroom varchar(5) after username;    <<<<在字段username后面按要求天加字段classroom
Query OK, 1 row affected (0.22 sec)                
Records: 1  Duplicates: 0  Warnings: 0

MariaDB [westos]> select * from message;
+----------+-----------+----------+------+
| username | classroom | password | age  |
+----------+-----------+----------+------+
| zm       | NULL      | redhat   | NULL |
+----------+-----------+----------+------+
1 row in set (0.00 sec)

4，删除某个字段

MariaDB [westos]> alter table message drop age;        <<<<<<<删除age这个字段
Query OK, 1 row affected (0.15 sec)                
Records: 1  Duplicates: 0  Warnings: 0

MariaDB [westos]> select * from message;
+----------+-----------+----------+
| username | classroom | password |
+----------+-----------+----------+
| zm       | NULL      | redhat   |
+----------+-----------+----------+
1 row in set (0.00 sec)

注意：对字段的操作都会用到alter

5，修改字段的数据内容 （注意WHERE的用法！！！）

MariaDB [westos]> select * from message;
+----------+-----------+----------+
| username | classroom | password |
+----------+-----------+----------+
| zm       | NULL      | redhat   |
+----------+-----------+----------+
1 row in set (0.00 sec)

MariaDB [westos]> update message set username='nihao' where username='zm';      <<<<<<把username数据由zm改为nihao
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [westos]> select * from message;
+----------+-----------+----------+
| username | classroom | password |
+----------+-----------+----------+
| nihao    | NULL      | redhat   |
+----------+-----------+----------+
1 row in set (0.00 sec)

4，删除某一行的内容

MariaDB [westos]> delete from message where username='nihao'      >>>>>数据表message中删除username='nihao'的这一行
    -> ;
Query OK, 1 row affected (0.02 sec)

MariaDB [westos]> select * from message;
Empty set (0.00 sec)

5，删除数据表

MariaDB [westos]> drop table message;             <<<<<<<<<drop
Query OK, 0 rows affected (0.02 sec)

MariaDB [westos]> show tables;
Empty set (0.00 sec)

6，删除数据库

MariaDB [westos]> drop database westos;            <<<<<<<<drop
Query OK, 0 rows affected (0.00 sec)

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