centos 安装mysql8 以及常用sql语句

安装环境：centos7
刚租了台服务器安装mysql的时候发现之前的笔记已经不合适了，更新一下。

mysql安装配置

1、检测是否安装过

1
rpm -qa | grep mysql

2、删除当前已安装版本

1
rpm -e --nodeps `rpm -qa | grep mysql`

3、在线安装

1
yum -y install mysql-server

这里可能会找不到包，如果没有可用的包，按照如下操作
去这里http://repo.mysql.com/
选择最新版本的mysql-community的rpm包复制链接地址

1
2
3
wget http://repo.mysql.com/mysql80-community-release-el7-1.noarch.rpm
rpm -ivh mysql80-community-release-el7-1.noarch.rpm
yum -y install mysql-server

4、开启mysql服务

1
service mysqld start

5、mysql添加开机启动

1
chkconfig mysqld on

6、初始化配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
grep 'temporary password' /var/log/mysqld.log    //查看初始密码
2018-12-04T14:08:38.524688Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: #Fwd;l5cl*r*   //初始密码  这很反人类
//下面初始化
whereis mysql_secure_installation    //找到mysql_secure_installation
mysql_secure_installation: /usr/bin/mysql_secure_installation /usr/share/man/man1/mysql_secure_installation.1.gz
/usr/bin/mysql_secure_installation    //直接运行mysql_secure_installation
Securing the MySQL server deployment.

Enter password for user root:            //输入刚才查看的密码

The existing password for the user account root has expired. Please set a new password.

New password:                              //新密码大小写数字加特殊符号

Re-enter new password:              //重复新密码
The 'validate_password' component is installed on the server.
The subsequent steps will run with the existing configuration
of the component.
Using existing password for root.

Estimated strength of the password: 100
Change the password for root ? ((Press y|Y for Yes, any other key for No) :        //直接跳过  选Y的话是重新设置密码
 ... skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL 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? (Press y|Y for Yes, any other key for No) : y  //禁止匿名访问
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? (Press y|Y for Yes, any other key for No) : y  //不允许root远程访问
Success.

By default, MySQL 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? (Press y|Y for Yes, any other key for No) : y  //删除测试数据库test
 - 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? (Press y|Y for Yes, any other key for No) : y  重新加载授权信息
Success.

All done!
```   

#### 常用命令
1、开启/关闭mysql服务

service mysqld stop/restart

1
2、访问mysql数据库

mysql -uroot -p[password]

1
3、显示数据库列表

show databases;

1
4、选择数据库

use databases; #数据库名

1
5、显示表

show tables;

1
6、显示表结构

describe table; #表名

1
7、新建/删除数据库

create database 库名;
drop database 库名;

1
8、建表

##demo##
CREATE TABLE user_info(
-> id varchar(30) NOT NULL,
-> user_name varchar(10),
-> password varchar(10),
-> PRIMARY KEY ( id )
-> );

1
9、删除表

drop table 表名;

1
10、清空表中数据

delete from 表名;

1
11、显示表中所有数据

select * from 表名;

1
2
12、表中添加一列  
如果想在一个已经建好的表中添加一列：

alter table TABLE_NAME add column NEW_COLUMN_NAME varchar(20) not null;

1
这条语句会向已有的表中加入新的一列，这一列在表的最后一列位置。如果我们希望添加在指定的一列：

alter table TABLE_NAME add column NEW_COLUMN_NAME varchar(20) not null after COLUMN_NAME;

1
注意，上面这个命令的意思是说添加新列到某一列后面。如果想添加到第一列的话：

alter table TABLE_NAME add column NEW_COLUMN_NAME varchar(20) not null first;

1
13、修改一列数据长度/类型

alter table user modify column id varchar(20);

1
14、删除列

alter table user drop column id;

1
15、中文显示？？？

service mysqld stop #关闭mysql
whereis my.cnf #确定配置文件位置
vim /etc/my.cnf #具体情况看自己的路径

#[mysqld]下加以下两行
character_set_server=utf8
init_connect=’SET NAMES utf8’

#保存退出
service mysqld start

1
2
`需要注意的是，之前在默认情况下创建的表的编码格式并不会改变！所以，如果想让在修改编码格式之前就创建好的表也修改，使用如下指令`
`1.修改数据库的编码格式`

alter database <数据库名> character set utf8;

1
`2.修改数据表格编码格式`

alter table <表名> character set utf8;

1
`3.修改字段编码格式`

alter table <表名> change <字段名> <字段名> <类型> character set utf8;
//demo
alter table user change username username varchar(20) character set utf8 not null;

`**修改完的数据库和库里的表 并不会使原来的数据生效，而是新加入的数据才会生效。`
  • © 2020-02 MonkeyInWind
  • GitHub

请我喝杯咖啡吧~