24_数据库服务概述 构建MySQL服务器 、 数据库基本管理 MySQL数据类型
版本：5.7.28
获取： 软件包
mysql-community-libs-5.7.28-1.el7.x86_64.rpm
mysql-community-common-5.7.28-1.el7.x86_64.rpm
mysql-community-client-5.7.28-1.el7.x86_64.rpm
mysql-community-server-5.7.28-1.el7.x86_64.rpm
mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpm
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-community-libs-5.7.28-1.el7.x86_64.rpm
https://downloads.mysql.com/archives/get/p/23/file/mysql-community-common-5.7.28-1.el7.x86_64.rpm
https://downloads.mysql.com/archives/get/p/23/file/mysql-community-client-5.7.28-1.el7.x86_64.rpm
https://downloads.mysql.com/archives/get/p/23/file/mysql-community-server-5.7.28-1.el7.x86_64.rpm
https://downloads.mysql.com/archives/get/p/23/file/mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpm
服务器：mysql 192.168.4.20
1.构建MySQL服务器
安装MySQL-server、MySQl-client软件包
修改数据库用户root的密码
确认MySQL服务程序运行、root可控
卸载残留
]# systemctl stop mariadb
]# rm -rf /etc/my.cnf
]# rm -rf /var/lib/mysql/*
]# rpm -qa | grep mariadb
]# rpm -e --nodeps mariadb-server mariadb
]# find / -name mysql # 找到之后删除
安装依赖包
]# yum -y install perl-Data-Dumper perl-JSON perl-Time-HiRes
安装mysql
]# mkdir mysql
]# cd mysql
mysql]# ls
mysql-community-client-5.7.28-1.el7.x86_64.rpm
mysql-community-common-5.7.28-1.el7.x86_64.rpm
mysql-community-libs-5.7.28-1.el7.x86_64.rpm
mysql-community-server-5.7.28-1.el7.x86_64.rpm
mysql]# yum -y install *.rpm
启动
]# systemctl start mysqld
]# systemctl enable mysqld
]# systemctl status mysqld
连接MySQL服务器，修改密码
]#grep 'temporary password' /var/log/mysqld.log
2020-02-03T01:55:43.917960Z 1 [Note] A temporary password is generated for root@localhost: t#6.Z!J5B!Yr
]# mysql -u root -p't#6.Z!J5B!Yr'
只验证长度
mysql>set global validate_password_policy=0;
修改密码长度,默认值是8个字符
mysql>set global validate_password_length=6;
修改登陆密码
mysql> alter user user() identified by "123456";
mysql> exit
]# mysql -u root -p123456
]# mysql -h 127.0.0.1 –uroot –p123456 远程登陆
2.数据库基本管理
2.1 查看/删除/创建库的相关操作
查看现有的库
mysql> show databases;
切换/使用指定的库
mysql> use sys;
Database changed
确认当前所在的库
mysql> select database();
新建名为newdb的库
mysql> create database newdb;
删除指定的库
mysql> drop database newdb;
3.查看/删除/创建表的相关操作
查看mysql库里有哪些表
mysql> use mysql;
mysql> show tables;
查看指定表的字段结构
mysql> desc columns_priv\G //以列表形式展现，末尾不用分号
查看columns_priv表的结构，以表格形式展现：
mysql> desc columns_priv; //查看表结构，以表格形式展现末尾需要有分号
mysql> desc mysql.columns_priv;
切换到mydb库
mysql> use mydb;
新建pwlist表
mysql> create table pwlist(
-> name char(16) not null,
-> password char(48)default '',
-> primary key(name)
-> );
Query OK, 0 rows affected (0.38 sec)
确认新创建的表：
mysql> show tables;
查看pwlist表的字段结构：
mysql> desc pwlist;
+----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| name | char(16) | NO | PRI | NULL | |
| password | char(48) | YES | | | |
+----------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
删除当前库中的pwlist表
mysql> drop table pwlist;
确认删除结果：
mysql> show tables;
在mydb库中创建一个学员表
mysql> CREATE TABLE mydb.student(
-> 学号 char(9) NOT NULL,
-> 姓名 varchar(4) NOT NULL,
-> 性别 enum('男','女') NOT NULL,
-> 手机号 char(11) DEFAULT '',
-> 通信地址 varchar(64),
-> PRIMARY KEY(学号)
-> ) DEFAULT CHARSET=utf8; //手工指定字符集，采用utf8
Query OK, 0 rows affected (0.31sec)
查看student表的字段结构：
mysql> DESC mydb.student;
+--------------+-------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------------+------+-----+---------+-------+
| 学号 | char(9) | NO | PRI | NULL | |
| 姓名 | varchar(4) | NO | | NULL | |
| 性别 | enum('男','女') | NO | | NULL | |
| 手机号 | char(11) | YES | | | |
| 通信地址 | varchar(64) | YES | | NULL | |
+--------------+-------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
查看student表的实际创建指令：
mysql> SHOW CREATE TABLE mydb.student;
修改MySQL服务的默认字符集
]# vim /etc/my.cnf
[mysqld]
.. ..
character_set_server=utf8
]# systemctl restart mysqld
3.各种时间函数的使用
使用now()查看当前的日期和时间
mysql> SELECT now();
+---------------------+
| now() |
+---------------------+
| 2020-02-03 10:08:34 |
+---------------------+
使用sysdate()查看系统日期和时间
mysql> SELECT sysdate();
+---------------------+
| sysdate() |
+---------------------+
| 2020-02-03 10:09:11 |
+---------------------+
使用curdate()获得当前的日期，不含时间
mysql> SELECT curdate();
2017-04-02
使用curtime()获得当前的时间，不含日期
mysql> SELECT curdate();
+------------+
| curdate() |
+------------+
| 2020-02-03 |
+------------+
分别获取当前日期时间中的年份、月份、日
mysql> SELECT year(now()),month(now()),day(now());
2017 | 4 | 2 |
获取系统日期时间中的月份、日
mysql> SELECT year(now()),month(now()),day(now());
+-------------+--------------+------------+
| year(now()) | month(now()) | day(now()) |
+-------------+--------------+------------+
| 2020 | 2 | 3 |
+-------------+--------------+------------+
获取系统日期时间中的时刻
mysql> SELECT time(sysdate());
+-----------------+
| time(sysdate()) |
+-----------------+
| 10:10:22 |
+-----------------+
