关于数据库的操作 (以下操作均在MySQL中进行)

show databases ;         查看现在MySQL包含的数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| servers            |
| sys                |
+--------------------+

mysql> select now();       查询时间
+---------------------+
| now()               |
+---------------------+
| 2019-08-10 14:58:53 |
+---------------------+
1 row in set (0.14 sec)

mysql> select now() as "时间" ;              输出时，将now换成时间
+---------------------+
| 时间                |
+---------------------+
| 2019-08-10 14:59:07 |
+---------------------+
1 row in set (0.39 sec)
同样的  user(), version() 等都可以这样输出

create database servers default charset utf8mb4 collate utf8mb4_general_ci;
创建servers数据库 ，并且指定了字符集
alter database servers 选项信息   修改数据库的选项信息
drop database servers  删库
status  查看当前库的信息

use servers  使用数据库
mysql> show create database servers\G     查看当前库的字符集
*************************** 1. row ***************************
       Database: servers
Create Database: CREATE DATABASE `servers` /*!40100 DEFAULT CHARACTER SET utf8mb4 */

表管理

create table phy_server(
  id int auto_increment primary key,
  host_name  varchar(36)  unique key not null, 
  server_model  varchar(96) not null,
  server_sn varchar(128) not null,
  server_vendor varchar(96) not null  comment "厂商",
  board_sn varchar(96),
  bios_version varchar(96) not null,
  os_version varchar(96) not null,
  kernel_version varchar(96) not null,
  cpu_phy_num int,
  cpu_core_of_phy int,
  cpu_model  varchar(32),
  add_date datetime,
  change_date  datetime default now()
);               创建表里面为各个字段属性

insert  into phy_server(
    host_name,server_model,server_sn,
    server_vendor,
    board_sn,
    bios_version,
    os_version,
    kernel_version,
    cpu_phy_num,
    cpu_core_of_phy,
    cpu_model,
    add_date
)
values(
'redis1',
'PowerEdge_R710',
'PowerEdge_R720',
"4c4c4544-0059",
"Dell_Inc",
"CN1374006T00B7",
"Centos7",
"3.4",
2,
4,
"Intel_E5506",
"20190819"
),(
'redis2',
'PowerEdge_R710',
'PowerEdge_R720',
"4c4c4544-0059",
"Dell_Inc",
"CN1374006T00B7",
"Centos7",
"3.4",
2,
4,
"Intel_E5506",
"20190819"
),
(
'redis3',
'PowerEdge_R710',
'PowerEdge_R720',
"4c4c4544-0059",
"Dell_Inc",
"CN1374006T00B7",
"Centos7",
"3.4",
2,
4,
"Intel_E5506",
"20190819"
)

单表查询

mysql> select id,host_name from phy_server ;
+----+------------+
| id | host_name  |
+----+------------+
|  1 | kvm-docker |
|  4 | redis1     |
|  5 | redis2     |
|  6 | redis3     |
|  3 | rouger     |
|  8 | rouoru2    |
|  9 | rourou1    |
|  7 | rourou3    |
+----+------------+

mysql> select id,host_name from phy_server where id > 5
    -> ;
+----+-----------+
| id | host_name |
+----+-----------+
|  6 | redis3    |
|  7 | rourou3   |
|  8 | rouoru2   |
|  9 | rourou1   |
+----+-----------+

有条件的查询：

mysql> select id ,host_name from phy_server where id > 5 and host_name != 'rourou3';
+----+-----------+
| id | host_name |
+----+-----------+
|  6 | redis3    |
|  8 | rouoru2   |
|  9 | rourou1   |
+----+-----------+

mysql> select id ,host_name from phy_server where id  in (2,5,8)
    -> ;
+----+-----------+
| id | host_name |
+----+-----------+
|  5 | redis2    |
|  8 | rouoru2   |
+----+-----------+
相同的也可以写 not in (2,5,8),那就是不查询id是2,5,8的行

mysql> select id ,host_name from phy_server where id  between 3 and 5;
+----+-----------+
| id | host_name |
+----+-----------+
|  3 | rouger    |
|  4 | redis1    |
|  5 | redis2    |
+----+-----------+

嵌套查询
mysql> select id ,host_name from phy_server where id  in (select * from t1);
+----+------------+
| id | host_name  |
+----+------------+
|  1 | kvm-docker |
|  6 | redis3     |
+----+------------+

mysql> select id ,host_name from phy_server where host_name like 'rourou%';
+----+-----------+
| id | host_name |
+----+-----------+
|  9 | rourou1   |
|  7 | rourou3   |
+----+-----------+
mysql> select id ,host_name from phy_server where host_name like 'rourou_';
+----+-----------+
| id | host_name |
+----+-----------+
|  9 | rourou1   |
|  7 | rourou3   |
+----+-----------+
看起来是一样的，但是其实是不同的。
加%的查询时，规定的字符后可以接0到无数个字符
加_的查询时，规定的字符后必须接1到无数个字符

mysql> select id ,host_name from phy_server limit 4;     取出前4行的内容
+----+------------+
| id | host_name  |
+----+------------+
|  1 | kvm-docker |
|  4 | redis1     |
|  5 | redis2     |
|  6 | redis3     |
+----+------------+

mysql> select id ,host_name from phy_server limit 2,2;   从第3行往后取两行
+----+-----------+
| id | host_name |
+----+-----------+
|  5 | redis2    |
|  6 | redis3    |
+----+-----------+
可以看出，在这里行数也是从零开始计算的，因此0为第一行

mysql> select id ,host_name from phy_server order by id asc ;   按照id的大小从小到大排序
+----+------------+
| id | host_name  |
+----+------------+
|  1 | kvm-docker |
|  3 | rouger     |
|  4 | redis1     |
|  5 | redis2     |
|  6 | redis3     |
|  7 | rourou3    |
|  8 | rouoru2    |
|  9 | rourou1    |
+----+------------+

mysql> select id ,host_name from phy_server order by id desc ;      按照id的大小从大到小排序
+----+------------+
| id | host_name  |
+----+------------+
|  9 | rourou1    |
|  8 | rouoru2    |
|  7 | rourou3    |
|  6 | redis3     |
|  5 | redis2     |
|  4 | redis1     |
|  3 | rouger     |
|  1 | kvm-docker |
+----+------------+

当然也可以两个组合，
select id ,host_name from phy_server order by id desc ,host_name asc;
类似于此，当id相同时，会按照host_name规定的排序