centos7 mysql命令_Centos7中mysql安装以及命令

Centos7将默认数据库mysql替换成了Mariadb，首先要卸载默认数据库的

[root@localhost ~]# rpm -qa|grep mariadb

mariadb-libs-5.5.52-1.el7.x86_64

[root@localhost ~]# rpm -e --nodeps mariadb-libs-5.5.52-1.el7.x86_64

开始安装mysql5.7

1. 下载安装包mysql-5.7.20-linux-glibc2.12-i686.tar.gz

官网下载地址 https://dev.mysql.com/downloads/mysql/ 选择 Linux-Generic

2. 进入/usr/local/目录，并解压

[root@localhost local]#tar -zxvf mysql-advanced-5.6.24-linux-glibc2.5-x86_64.tar.gz

将解压好的文件夹重命名为mysql5.7

3、 创建一个mysql用户组和mysql的用户名

[root@localhost local]# groupadd mysql

[root@localhost local]# useradd -g mysql mysql    //将mysql用户添加到mysql的用户组中

4. 进入mysql5.7目录，并创建两个目录

[root@localhost mysql5.7]# mkdir data

[root@localhost mysql5.7]# mdir logs

创建一个配置文件 my.cnf

[root@localhost mysql5.7]# vim my.cnf

配置文件参考： https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html

关于mysql的配置文件 my.cnf配置  , 以下先给一个例子：

[client]

port = 3306

socket = /var/lib/mysql/mysql.sock

[mysql]

#这个配置段设置启动MySQL服务的条件；在这种情况下，no-auto-rehash确保这个服务启动得比较快。

no-auto-rehash

[mysqld]

user = mysql

port = 3306

socket = /var/lib/mysql/mysql.sock

basedir = /usr/local/mysql

datadir = /data/mysql/data/

open_files_limit = 10240

back_log = 600

#在MYSQL暂时停止响应新请求之前，短时间内的多少个请求可以被存在堆栈中。如果系统在短时间内有很多连接，则需要增大该参数的值，该参数值指定到来的TCP/IP连接的监听队列的大小。默认值80。

max_connections = 3000

#MySQL允许最大的进程连接数，如果经常出现Too Many Connections的错误提示，则需要增大此值。默认151

配置文件中有很多分组，例如[client] 、[mysql]、 [mysqld]、[mysqldump]、[myisamchk]、[mysqlhotcopy]

每个分组对应一个可运行的程序；比如 mysql， 则对应的是  bin/mysql

以上这些工具都是客户端(client)工具的共同选项都可以放到 [client] 组中([client] 下的选项必须是所有程序都有的，否则可能出错)，比如说 --socket。

这样就不用在[mysql]或[mysqladmin]等组下分别指定 socket 了，不过也可以在每个分组中分别配置

可以通过命令来获取某个分类的参数详情,比如要获取mysql的配置参数，执行以下命令

[root@localhost mysql]# bin/mysql --help –verbose

再比如分组：mysqld 则可以通过执行来获取参数详情

[root@localhost mysql]# bin/mysqld --help –verbose

以下为部分配置文件

[mysql]

# 设置mysql客户端默认字符集

default-character-set=utf8

socket=/usr/local/mysql5.7/mysql.sock

[mysqld]

#ip地址名称解析

skip-name-resolve

pid-file=/usr/local/mysql5.7/data/mysql.pid

#设置3306端口，这是默认的端口

port = 3306

socket=/usr/local/mysql5.7/mysql.sock

basedir=/usr/local/mysql5.7

datadir=/usr/local/mysql5.7/data

# 允许最大连接数

max_connections=200

# 服务端使用的字符集，设置为 utf8格式

character-set-server=utf8

# 创建新表时将使用的默认存储引擎，mysql5.7默认的是INNODB

default-storage-engine=INNODB

#默认为0，表名区分大小写，

#lower_case_table_names=1

max_allowed_packet=16M

#Whether the slow query log is enabled ，0=OFF;1=ON

slow_query_log=1

#The name of the slow query log file

slow_query_log_file=/usr/local/mysql5.7/logs

#The name of the error log file

log-error=/usr/local/mysql5.7/logs

#mysql binlog日志文件保存的过期时间，过期后自动删除

expire_logs_days = 5

5. 初始化数据库

[root@localhost mysql5.7]# chown -R mysql:mysql ./　　修改当前目录拥有者为mysql用户

[root@localhost mysql5.7]# bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql5.7 --datadir=/usr/local/mysql5.7/data/

2017-10-21T07:51:33.816187Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2017-10-21T07:51:35.087814Z 0 [Warning] InnoDB: New log files created, LSN=45790

2017-10-21T07:51:35.294635Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.

2017-10-21T07:51:35.316519Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: a94ee7f1-b634-11e7-b38d-000c29c1b6db.

2017-10-21T07:51:35.319510Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.

2017-10-21T07:51:35.323145Z 1 [Note] A temporary password is generated for root@localhost: #8#9Jf%a+Tiq

最后一行，#8#9Jf%a+Tiq 表示root在登录mysql时的初始密码。

6. 将mysql配置为服务项目

修改my.cnf的权限。

[root@localhost mysql5.7]# chown 777 my.cnf

修改support-files/mysql.server

mysql.server的修改如下：

将

basedir=

datadir =

改为如下：

将  conf = etc/my.cnf 改为  conf=$basedir/my.cnf

将文件mysql.server 复制到/etc/rc.d/init.d(启动脚本目录)目录中,并重新命名为mysql

[root@localhost mysql5.7]# cp ./support-files/mysql.server /etc/rc.d/init.d/mysql

[root@localhost mysql5.7]# chkconfig --add mysql   //将mysql服务加入到系统服务中

[root@localhost mysql5.7]# chkconfig --list mysql   //是否已经生效

mysql 0:off 1:off 2:on 3:on 4:on 5:on 6:off

表明mysql服务已经生效，在2、3、4、5运行级别随系统启动而自动启动

[root@localhost mysql5.7]# systemctl start mysql.service

将mysql5.7的bin目录加入PATH环境变量，编辑/etc/profile文件

[root@localhost mysql5.7]# vi /etc/profile

在文件最后添加如下信息：

export PATH=$PATH:/usr/local/mysql5.7/bin

保存，退出

[root@localhost mysql5.7]# source /etc/profile

登录mysql,root的初始密码在刚才初始化数据库是已生成。

[root@localhost mysql5.7]# mysql -u root -p

SQL语言共分为四大类：数据查询语言DQL，数据操纵语言DML，数据定义语言DDL，数据控制语言DCL。

1. 数据查询语言DQL: select

2 .数据操纵语言DML：insert，update,delete

3. 数据定义语言DDL: CREATE TABLE(表)/VIEW(视图)/INDEX(索引)

4. 数据控制语言DCL: 数据控制语言DCL用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等。如：GRANT,ROLLBACK,COMMIT

在数据库的插入、删除和修改操作时，只有当事务在提交到数据库时才算完成。在事务提交前，只有操作数据库的这个人才能有权看到所做的事情，别人只有在最后提交完成后才可以看到。

提交数据有三种类型：显式提交、隐式提交及自动提交。下面分别说明这三种类型。

(1) 显式提交

用COMMIT命令直接完成的提交为显式提交。其格式为：

SQL>COMMIT；

(2) 隐式提交

用SQL命令间接完成的提交为隐式提交。这些命令是：ALTER，AUDIT，COMMENT，CONNECT，CREATE，DISCONNECT，DROP，EXIT，GRANT，NOAUDIT，QUIT，REVOKE，RENAME。

(3) 自动提交

若把AUTOCOMMIT设置为ON，则在插入、修改、删除语句执行后，系统将自动进行提交，这就是自动提交。其格式为：

SQL>SET AUTOCOMMIT ON；

一、用户管理：

新建用户 'test',并且设置密码为 'Test#123456'

create user test identified by 'Test#123456';

将用户 test的密码改为 Test%123456

alter user 'root'@'%' identified by 'test%123';

二、权限管理

mysql>show grants; --当前用户(自己)权限mysql>grant select, insert, update, delete on mytestdb.* to test; //给test用户mytestdb数据库的权限

mysql>show grants for test;

mysql>revoke delete on mytestdb.* from test; //去除删除权限；

mysql>grant all on mytestdb.* to test; //给test用户mytestdb数据库的所有权限

mysql>revoke all on mytestdb.* from test; //去除所有权限；

配置允许远程访问方式

首先配置允许访问的用户，采用授权的方式给用户权限

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'IDENTIFIED BY 'w123456' WITH GRANT OPTION;

说明：root是登陆数据库的用户，w123456 是登陆数据库的密码，% 表示任何来源任何主机都有权限。

flush privileges;

grant, revoke 用户权限后，该用户只有重新连接 MySQL 数据库，权限才能生效。

如果想让授权的用户，也可以将这些权限 grant 给其他用户，需要选项 “grant option“

grant select on mytestdb.* to test@localhost with grant option;

这个特性一般用不到。实际中，数据库权限最好由 DBA 来统一管理。

三、数据库操作：

show databases; //查看数据库

create database mytestdb;　　//创建数据库mytestdb

drop database mytestdb; //删除数据库mytestdb

四、数据表操作：

1、创建表：

>create table  table_name(

>id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, //id值，递增——唯一性，主键。

>name VARCHAR(60) NOT NULL

>score TINYINT UNSIGNED NOT NULL DEFAULT 0,　　　　//设置默认列值

>PRIMARY KEY(id)

>)ENGINE=InnoDB　　　　//设置表的存储引擎，一般常用InnoDB和MyISAM；InnoDB可靠，支持事务；MyISAM高效不支持全文检索

>DEFAULT charset=utf8;　　//设置默认的编码，防止数据库中文乱码

2、复制表：

mysql>create table table_name select * from tableName;

mysql>create table table_name select id,name from tableName; //复制部分字段：

3、创建临时表：

mysql>create temporary table tableName(这里和创建普通表一样);

4、查看数据库中可用的表：

mysql>show tables;

5、查看表的结构：

mysql>desc tableName;

也可以使用：

mysql>show columns in tableName;  //from也可以

6、删除表：

>DROP [ TEMPORARY ] TABLE [ IF EXISTS ] tableName[ ,tb_name2.......];

实例：

>DROP TABLE IF EXISTS tableName;

7、表重命名：

>RENAME TABLE name_old TO name_new;

还可以使用：

>ALTER TABLE name_old RENAME name_new;

五、修改表：

1、更改表结构：

>ALTER TABLE table_name ADD[CHANGE,RENAME,DROP] ...要更改的内容...

查看列：desc table_name;

修改表名：alter table table_name rename to new_tablename;

添加列：alter table table_name add column 列名 varchar(30) NOT NULL;

删除列：alter table table_name drop column 列名;

修改列名：

alter table lesson change OldColumnName NewColumnName varchar(100) not null charset utf8;

如： alter table lesson change title title varchar(100)  charset utf8;

在已经存在的表中创建索引，除了使用CREATE INDEX，还可以使用ALTER TABLE语句，语法格式

ALTER TABLE 表名 ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX索引名  (字段名 [(长度)] [ASC|DESC])

参数说明

UNIQUE、FULLTEXT和SPATIAL都是可选参数，分别用于表示唯一性索引、全文索引和空间索引

ADD，表示向表中添加字段

六、MySQL的正则表达式：

1、Mysql支持REGEXP的正则表达式：

>SELECT * FROM table_name WHERE name REGEXP '^[A-D]' //找出以A-D 为开头的name

2、特殊字符需要转义。

七、MySQL的一些函数：

1、字符串链接——concat()

>select concat(name,'=>',score) from table_name

2、数学函数：AVG、SUM、MAX、MIN、COUNT；

3、文本处理函数：TRIM、LOCATE、UPPER、LOWER、SUBSTRING

4、运算符： +、-、*、\

5、时间函数：DATE()、CURTIME()、DAY()、YEAR()、NOW().....

八、全文检索

1、自然语言模式(IN NATURAL LANGUAGE MODE)，即通过MATCH AGAINST 传递某个特定的字符串来进行检索。

2、布尔模式(IN BOOLEAN MODE)，可以为检索的字符串增加操作符，例如“+”表示必须包含，“-”表示不包含，“*”表示通配符(这种情况， 即使传递的字符串较小或出现在停词中，也不会被过滤掉)，其他还有很多特殊的布尔操作符，可以通过如下参数控制：

mysql> show variables like '%ft_boolean_syntax%';

+-------------------+----------------+

| Variable_name | Value |

+-------------------+----------------+

| ft_boolean_syntax | + ->

+-------------------+----------------+

1 row in set (0.00 sec)

3、查询扩展模式(WITH QUERY EXPANSION), 这种模式是自然语言模式下的一个变种，会执行两次检索，第一次使用给定的短语进行检索，第二次是结合第一次相关性比较高的行进行检索。

4、MySQL5.6版本开始支持InnoDB引擎的全文索引

5、目前MySQL支持在CHAR、VARCHAR、TEXT类型的列上定义全文索引。

6：创建索引:

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name  [USING index_type]  ON tbl_name (index_col_name,...)

index_col_name:  col_name [(length)] [ASC | DESC]

查看索引： SHOW INDEX FROM table_name;

删除索引:  drop index index_name on table_name ;

alter table table_name drop index index_name ;

九、视图

十、使用存储过程：

1、创建存储过程：

>CREATE PROCEDURE pro(IN num INT,OUT total INT)

>BEGIN

>SELECT SUM(score) into total FROM tb_name WHERE id=num;

>END;

注： IN (传入的参数)，OUT(从存储过程传出一个值)，INOUT(对存储过程传入、传出)

2、调用存储过程：

>CALL pro(13,@total);　//这里的存储过程两个变量，一个是IN一个是OUT，这里的OUT也是需要写上的，不写会出错

>SELECT @total; //这里就可以看到结果了；

3、存储过程的其他操作：

>SHOW PROCEDURE STATUS;//显示当期的存储过程

>DROP PROCEDURE pro;//删除指定存储过程

数据库字符集设置

1.列出MYSQL支持的所有字符集：

mysql> show character set;

+----------+---------------------------------+---------------------+--------+

| Charset | Description | Default collation | Maxlen |

+----------+---------------------------------+---------------------+--------+

| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |

| dec8 | DEC West European | dec8_swedish_ci | 1 |

| cp850 | DOS West European | cp850_general_ci | 1 |

| hp8 | HP West European | hp8_english_ci | 1 |

| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |

| latin1 | cp1252 West European | latin1_swedish_ci | 1 |

| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |

| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |

| ascii | US ASCII | ascii_general_ci | 1 |

| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |

| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |

| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |

| tis620 | TIS620 Thai | tis620_thai_ci | 1 |

| euckr | EUC-KR Korean | euckr_korean_ci | 2 |

| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |

| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |

| greek | ISO 8859-7 Greek | greek_general_ci | 1 |

| cp1250 | Windows Central European | cp1250_general_ci | 1 |

| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |

| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |

| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |

| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |

| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |

| cp866 | DOS Russian | cp866_general_ci | 1 |

| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |

| macce | Mac Central European | macce_general_ci | 1 |

| macroman | Mac West European | macroman_general_ci | 1 |

| cp852 | DOS Central European | cp852_general_ci | 1 |

| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |

| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 |

| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |

| utf16 | UTF-16 Unicode | utf16_general_ci | 4 |

| utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 |

| cp1256 | Windows Arabic | cp1256_general_ci | 1 |

| cp1257 | Windows Baltic | cp1257_general_ci | 1 |

| utf32 | UTF-32 Unicode | utf32_general_ci | 4 |

| binary | Binary pseudo charset | binary | 1 |

| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |

| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |

| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |

| gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 |

+----------+---------------------------------+---------------------+--------+

41 rows in set (0.00 sec)

注：collation, 即比对方法, 用于指定数据集如何排序，以及字符串的比对规则(比如：where语句查找)

mysql的字符序遵从命名惯例。以_ci(表示大小写不敏感)，以_bin(表示用编码值进行比较，即大小写敏感，比如：utf8_bin)

2.建库时指定字符集

mysql>create database testdb character set gbk collate gbk_chinese_ci;

Query OK, 1 row affected (0.00 sec)

或：

mysql>create database testdb default charset utf8 collate utf8_general_ci;

3.当前MYSQL服务器字符集设置

mysql> show variables like 'character_set_%';

+--------------------------+----------------------------+

| Variable_name | Value |

+--------------------------+----------------------------+

| character_set_client | utf8 |

| character_set_connection | utf8 |

| character_set_database | utf8 |

| character_set_filesystem | binary |

| character_set_results | utf8 |

| character_set_server | utf8 |

| character_set_system | utf8 |

| character_sets_dir | /usr/share/mysql/charsets/ |

+--------------------------+----------------------------+

8 rows in set (0.01 sec)

4.当前MYSQL服务器字符集校验设置

mysql> show variables like 'collation_%';

+----------------------+-----------------+

| Variable_name | Value |

+----------------------+-----------------+

| collation_connection | utf8_general_ci |

| collation_database | utf8_general_ci |

| collation_server | utf8_general_ci |

+----------------------+-----------------+

3 rows in set (0.00 sec)

5.显示某数据库字符集设置

mysql> show create database test;

+----------+----------------------------------------------------------------+

| Database | Create Database |

+----------+----------------------------------------------------------------+

| test | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf32 */ |

+----------+----------------------------------------------------------------+

1 row in set (0.00 sec)

注： 其中的 /*!40100 ...*/ 这部分注释会被MySQL执行，表示服务端版本号大于4.1.00时会被执行。

6.修改数据库字符集

mysql> alter database test default character set 'utf8';

Query OK, 1 row affected (0.00 sec)

mysql> show create database test;

+----------+---------------------------------------------------------------+

| Database | Create Database |

+----------+---------------------------------------------------------------+

| test | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */ |

+----------+---------------------------------------------------------------+

1 row in set (0.00 sec)

7.显示某数据表字符集设置

mysql> show create table wxuser;

+--------+------------------------------------------------+

| Table | Create Table |

+--------+------------------------------------------------+

| wxuser | CREATE TABLE `wxuser` (

`openId` varchar(32) NOT NULL,

`nickName` varchar(200) CHARACTER SET utf8 NOT NULL,

`gender` varchar(10) NOT NULL DEFAULT '',

`province` varchar(10) NOT NULL,

`city` varchar(10) NOT NULL DEFAULT '',

`avatarUrl` varchar(255) NOT NULL DEFAULT '',

`unionId` varchar(32) NOT NULL DEFAULT '',

`appid` varchar(32) NOT NULL DEFAULT '',

`isCust` tinyint(4) NOT NULL DEFAULT '0',

PRIMARY KEY (`openId`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

+--------+------------------------------------------------+

1 row in set (0.00 sec)

8.修改数据表字符集

mysql> alter table wxuser default character set 'utf8';

Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0