Linux下使用MySQL教程

是最流行的关系型数据库管理系统之一,属于 Oracle 旗下产品

安装启动操作

1.安装mysql命令 :$ sudo apt-get install -y mysql-server
2.查看mysql的版本命令(注意-V是大写,不然会出现如下错误):$ mysql -V
3.启动mysql命令(关闭,重启等只需将start换成stop,restart等即可):$sudo service mysql start
4.登录mysql命令为:$ mysql -u用户名 -p密码
5.连接远程数据库:$ mysql -h <host> -P <port> -u<username> -p<password>

数据库操作

1.查看数据库:> show databases; (注意分号“;”不要落下)
2.新建一个数据库命令:> create database 数据库名称;
删除一个数据库命令:> drop database 数据库名称;
3.使用某个数据库:> use 数据库名称;

表操作

1.查看表命令:> show tables;
2.建立一个新表:> create table 表名 (字段参数); 或 >create table if not exists 表名(字段参数);
删除一个旧表:> drop table 表名; 或 >drop table if exists 表名;
3.查看表结构:> desc 表名称; 或 >show columns from 表名称;
4.对表数据的操作:
增:>insert into 表名称 (字段名1,字段名2,字段名3......) values(字段名1的值,字段名2的值,字段名3的值......);
删:>delete from 表名称 where 表达式;

建立一个新表:> create table 表名 (字段参数); 或 >create table if not exists 表名(字段参数)

删除一个旧表:>drop table 表名; 或 >drop table if exists 表名

改:>update 表名称 set 字段名=“新值” where 表达式;
查:>select 字段名1,字段名2,字段名3..... from 表名称;
5.增加字段:>alter table 表名称 add 字段名 数据类型 其它; (其它包括默认初始值的设定等等)
6.删除字段:>alter table 表名称 drop 字段名;

用户相关操作

注:以下命令均需先以root身份登录mysql:mysql -uroot -p

show databases;  显示此用户下的数据库;

use 数据库名;       切换数据库

show tables;    显示该数据库下所有表

desc users;   显示users这个表的详细信息

1.添加新用户
(1)创建新用户:> insert into mysql.user(Host,User,Password) values("localhost","user1",password("password1"));


(2)为用户分配权限:
设置用户可以在本地访问mysql:grant all privileges on *.* to username@localhost identified by "password" ;
设置用户只能访问指定数据库:grant all privileges on 数据库名.* to username@localhost identified by "password" ;
(3)刷新系统权限表:>flush privileges;
2.查看MySql当前所有的用户:>SELECT DISTINCT User FROM mysql.user;
3.删除用户及其数据字典中包含的数据:>drop user 'xbb'@'localhost';

实例:

mysql使用手册

1、mysql登录

1.1 远程登陆mysql

mysql -h ip -u root -p 密码

1.2 本地登陆mysql

mysql -u root -p 密码

2、为数据库配置远程连接权限

采用授权法

例如:以用户名:root 密码:888888从任何主机连接到mysql服务器

先在服务器上用root用户登录:

mysql –uroot –p

出现命令行提示符:

mysql>

输入:

mysql>GRANT ALL PRIVILEGES ON *.* TO ‘root’@’%’IDENTIFIED BY ‘1qaz2wsx’ WITH GRANT OPTION;

然后使配置生效:

mysql>FLUSH PRIVILEGES;

mysql>exit;

如果仅仅允许用户root 从ip为192.168.1.6的主机连接到mysql服务器,并使用888888作为密码

mysql>GRANT ALL PRIVILEGES ON *.* TO ‘root’@’192.168.1.6’ IDENTIFIED BY ‘888888’ WITH GRANT OPTION;

mysql>FLUSH PRIVILEGES ;

mysql>exit;

3、用户使用权限

mysql> use mysql;

Database changed

mysql> select host, user, password from user;

+———–+———+——————————————-+

| host      | user    | password                                  |

+———–+———+——————————————-+

| localhost | root    | *535CC5FDC76618DED475584E5F5957B9F7E63D4B |

| 127.0.0.1 | root    | *7C057B0B526E12B8D3AFBD4B9C187CA3E62DA280 |

| ::1       | root    | *7C057B0B526E12B8D3AFBD4B9C187CA3E62DA280 |

| %         | phptest | *523DF7DC12DF59EC98AD12A7125000A5B4E63721 |

| %         | root    |                                           |

| %         | mysqld  | *83D34C89B8E0F100D54C6D9276D357DB43E8779F |

| %         | server  | *866D5A029D62EC05ACC4584CE50F1CD2F50E0E82 |

+———–+———+——————————————-+

7 rows in set (0.00 sec)

4、用户登陆及密码更改

#修改当前登录用户密码:

mysql> SET PASSWORD = PASSWORD(‘1qaz2wsx’);

mysql> exit;

 

#添加ydlm用户查询、插入、更新、删除的权限:

grant select,insert,update,delete on *.* to test@”%” identified by “123456“;

grant select,insert,delete,update,create,drop on *.* to test@”%” identified by “123456”;

flush privileges;

5、限制root用户远程登陆

mysql> delete from user where user=”root” and host=”%”;

mysql> flush privileges;

mysql> exit;

#启用root用户远程登陆

mysql>GRANT ALL PRIVILEGES ON *.* TO ‘root’@’%’IDENTIFIED BY ‘1qaz2wsx’ WITH GRANT OPTION;

然后使配置生效:

mysql>FLUSH PRIVILEGES;

mysql>exit;

6、删除某用户

drop user 用户名@’%’;

drop user ydlm@’%’;

DELETE FROM user WHERE user=’test’;

或者

drop user test@”%”;

delete from user Where User=’test’ and Host=’%’; #指定用户和主机名

7、数据库导入

source /opt/test.dump_20171124_112640.sql

8、数据库的备份与恢复

— 备份数据库

— /var/lib/mysql

— 备份

— mysqldump -u 用户名 -p [密码]  [选项]  [数据库名]   [表名]  > /备份路径/备份文件名 ,选项有 –all-databases 表示备份所有数据库

mysqldump -u root -p studb > /studb.sql

mysqldump -uroot -p –all-databases >/studb.sql   #不建议用,恢复麻烦

— 还原数据库

mysql -u root -p studb < /studb.sql

9、mysql数据库使用

mysql> show databases;   查看数据库列表信息

mysql> use mysql;        USE  数据库名

mysql> show tables;

— 创建新的数据库

— CREATE DATABASE 数据库名

— 创建新的数据表

— CREATE TABLE 表名 (字段定义……)

— 删除指定的数据表

— DROP TABLE [数据库名.]表名

— 删除指定的数据库

— DROP DATABASE 数据库名

— 建表:

— create  table  student (表名称是student

— studentName  varchar(30)  not  null,   字段studentName

— studentId   int  not  null,                 字段studentId

— studentAge  int  not  null,                字段studentAge

— loginPass  varchar(10) 字段loginPass

— );

10、重置mysql数据库root用户密码

1、修改MySQL的登录设置:

vi /etc/my.cnf

在[mysqld]的段中加上一句:skip-grant-tables

[mysqld]

datadir=/var/lib/mysql

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

skip-name-resolve

skip-grant-tables

2、重新启动mysqld:

/etc/init.d/mysqld restart

3、登录并修改MySQL的root密码:

/usr/bin/mysql

USE mysql ;

UPDATE user SET Password = password ( ‘new-password’ ) WHERE User = ‘root’

flush privileges ;

Quit;

4、再把配置文件修改回来,再重启服务

11、Mysql备份脚本

#!/bin/sh

# File: /root/mysql_dump/mysql_back.sh

# Database info

DB_NAME=”test”

DB_USER=”root”

DB_PASS=”123456″

# Others vars

# whereis mysqldump

# IS ` but not ‘

BIN_DIR=”/usr/bin”

BCK_DIR=”/root/mysql_dump/data”

DATE=`date +%Y%m%d_%H%M%S`

# TODO DATABASE BAK AND DELETE 15 DAYS AGO BAKFILES

mkdir -p $BCK_DIR

$BIN_DIR/mysqldump –opt -u$DB_USER -p$DB_PASS $DB_NAME \

 > $BCK_DIR/$DB_NAME.dump_$DATE.sql

find $BCK_DIR -mtime +15 -name “test.dump_*” -exec rm -rf {} \;

linu mysql数据导出来_linux下如何导入导出MySQL数据库

用mysqldump命令行

命令格式

mysqldump -u 用户名 -p 数据库名 > 数据库名.sql

范例:

mysqldump -u root -p abc > abc.sql

(导出数据库abc到abc.sql文件)

提示输入密码时,输入该数据库用户名的密码。

二、导入:

用mysql命令行

命令格式

mysql -u 用户名 -p 数据库名

三、直接COPY

如果数据库比较大,可以考虑用直接拷贝的方法,但不同版本和操作系统之间可能不兼容,要慎用。

3.1 将原始数据库用tar打包为一个文件

tar -zcvf mydb.tar.gz mydb

3.2 创建空数据库

3.3 解压

在临时目录中解压,如:

cd /tmp

tar -zxvf mydb.tar.gz

3.4 拷贝

将解压后的数据库文件拷贝到相关目录

cd mydb/

cp * /var/lib/mysql/mydb/

对于FreeBSD:

cp * /var/db/mysql/mydb/

3.5 权限设置

将拷贝过去的文件的属主改为mysql:mysql,权限改为660

chown mysql:mysql /var/lib/mysql/mydb/*

chmod 660 /var/lib/mysql/mydb/*

//——————————————————————————————————–mysqldump -u 用户名 -p 数据库名 > 导出的文件名  mysqldump -u wcnc -p smgp_apps_wcnc > wcnc.sql2.mysql导出数据库一个表mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名  mysqldump -u wcnc -p smgp_apps_wcnc users> wcnc_users.sql3.mysql导出一个数据库结构mysqldump -u wcnc -p -d –add-drop-table smgp_apps_wcnc >d:wcnc_db.sql  -d 没有数据 –add-drop-table 在每个create语句之前增加一个drop table4.mysql导入数据库常用source 命令  进入mysql数据库控制台,  如mysql -u root -p  mysql>use 数据库  然后使用source命令,后面参数为脚本文件(如这里用到的.sql)

mysql>source d:wcnc_db.sql

//————————————————————————————————————-

一、导出数据库用mysqldump命令(注意mysql的安装路径,即此命令的路径):

1、导出数据和表结构:

mysqldump -u用户名 -p密码 数据库名 > 数据库名.sql

#/usr/local/mysql/bin/mysqldump -uroot -p abc > abc.sql

敲回车后会提示输入密码

2、只导出表结构

mysqldump -u用户名 -p密码 -d 数据库名 > 数据库名.sql

#/usr/local/mysql/bin/mysqldump -uroot -p -d abc > abc.sql

二、导入数据库

1、首先建空数据库

mysql>create database abc;

2、导入数据库

方法一:

(1)选择数据库

mysql>use abc;

(2)设置数据库编码

mysql>set names utf8;

(3)导入数据(注意sql文件的路径)

mysql>source /home/abc/abc.sql;

方法二:

mysql -u用户名 -p密码 数据库名 < 数据库名.sql

#mysql -uabc_f -p abc < abc.sql

注意:有命令行模式,有sql命令

如何重设 MySQL 的 root 密码

幸运地是,重设密码很容易。

注意:MySQL的root用户和服务器操作系统的root用户是两个不同的用户,不要搞混了。

安全模式重置法

基本的思路是,以安全模式启动mysql,这样不需要密码可以直接以root身份登录,然后重设密码。

首先,我们停掉MySQL服务:

sudo service mysql stop  

以上命令适用于Ubuntu和Debian。CentOS、Fedora和RHEL下使用mysqld替换mysql。

以安全模式启动MySQL:

sudo mysqld_safe --skip-grant-tables --skip-networking &  

注意我们加了--skip-networking,避免远程无密码登录 MySQL。

这样我们就可以直接用root登录,无需密码:

mysql -u root  

接着重设密码:

mysql> use mysql;  
mysql> update user set password=PASSWORD("mynewpassword") where User='root';  
mysql> flush privileges;  

注意

我的mysql版本 MYSQL V5.7.9,旧版本请使用:

1
UPDATE mysql.user SET Password=PASSWORD('新密码') WHERE User='root';

,命令后需要加分号。

重设完毕后,我们退出,然后启动 MySQL 服务:

mysql > quit  

quit不需要分号。

重启服务:

sudo service mysql restart  

同样,以上命令适用于Ubuntu和Debian,Centos、Fedora和RHEL需要用mysqld替换mysql

现在可以尝试用新密码登录了:

mysql -u root -pmynewpassword  

注意,-p 和密码间不能有空格。

Mac OS X – 重置 MySQL Root密码

密码太多记不住??你是否忘记了Mac OS 的MySQL的root密码? 通过以下4步就可重新设置新密码:

1. 停止 mysql server.

通常是在 ‘系统偏好设置’ > MySQL > ‘Stop MySQL Server’

2. 打开终端,输入:

1
sudo /usr/local/mysql/bin/mysqld_safe --skip-grant-tables

3. 打开另一个新终端,输入:

1
2
3
4
5
6
7
sudo /usr/local/mysql/bin/mysql -u root
  UPDATE mysql.user SET authentication_string=PASSWORD('你的新密码') WHERE User='root';
  FLUSH PRIVILEGES;
  exit

4. 重启MySQL.

C:\Program Files\MySQL\MySQL Server 5.7\bin>mysql
ERROR 2003 (HY000): Can’t connect to MySQL server on ‘localhost’ (10061)

C:\Program Files\MySQL\MySQL Server 5.7\bin>mysqladmin -u root flush-privileges
password “newpassword”
mysqladmin: [Warning] Using a password on the command line interface can be inse
cure.
Warning: Since password will be sent to server in plain text, use ssl connection
to ensure password safety.
mysqladmin: unable to change password; error: ‘Operation ALTER USER failed for ‘
root’@’skip-grants host”

其他方案

以上是通用方案,在Ubuntu和Debian系统上,有一个debian-sys-maint用户,Debian类系统下一些系统脚本对mysql的操作是通过这个用户完成的。所以我们可以通过这个用户来修改 root 密码。该用户的密码可以在/etc/mysql/debian.cnf下找到:

# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host     = localhost  
user     = debian-sys-maint  
password = PASSWORD  
socket   = /var/run/mysqld/mysqld.sock  
[mysql_upgrade]
host     = localhost  
user     = debian-sys-maint  
password = PASSWORD  
socket   = /var/run/mysqld/mysqld.sock  
basedir  = /usr  

用该用户登录 MySQL 后,也可以修改密码(具体修改过程见上):

sudo mysql -u debian-sys-maint -p  

mysql:Windows修改MySQL数据库密码(修改或忘记密码)

以实际操作修改root密码为例,操作系统为windows
这里我们需要注意的是,修改MySQL是需要MySQL中的root权限,一般用户是无法更改的,除非请求管理员。

修改密码的三种简单方法

第一种​用SET PASSWORD命令

1.打开cmd进入MySQL的bin目录;(如我的路径是F:\MySQL\mysql-5.7.24-winx64\bin)

2.通过输入命令 mysql -u root -p 指定root用户登录MySQL,输入后回车会提示输入密码,输入我们原来的密码然后回车。

3.修改MySQL的root用户密码,格式:mysql> set password for 用户名@localhost = password(‘新密码’);
例如(上面例子将用户root的密码更改为root):mysql> set password for root@localhost = password(‘root’);

4.退出mysql重新登录,输入新密码root登录就可以了;
在这里插入图片描述

第二种 用mysqladmin修改密码

1.打开cmd进入MySQL的bin目录;(如我的路径是F:\MySQL\mysql-5.7.24-winx64\bin)

2.修改MySQL的root用户密码格式:mysqladmin -u用户名 -p旧密码; password 新密码
例如(第一种方法中我将密码改成了root,这里再改回123):
mysqladmin -uroot -proot password 123(这里一定要注意-uroot和 -proot是整体,不要写成-u root -p root,我亲自验证过他们直接加-u和root间可以加空格,但是会有警告出现,所以就不要加空格了)

重新登录,输入新密码123就ok了;
在这里插入图片描述

第三种用UPDATE直接编辑user表

首先声明,这种方法我测试了半个小时也没有成功,但是网上查询很多人都成功了,感兴趣的话可以尝试一下。下面是步骤
首先还是通过cmd 登录MySQL
连接权限数据库: use mysql;
改密码:update user set password=password(“123”) where user=“root”;(别忘了最后加分号) 。

以上不行可试:

UPDATE mysql.user SET authentication_string=PASSWORD('你的新密码') WHERE User='root';
刷新权限(必须步骤):flush privileges;

忘记root密码情况

1.关闭正在运行的MySQL服务。打开cmd进入MySQL的bin目录;

2.输入mysqld –skip-grant-tables 回车。(–skip-grant-tables 的意思是启动MySQL服务的时候跳过权限表认证。)

3.再开一个DOS窗口(因为刚才那个DOS窗口已经不能动了),输入mysql回车,如果成功,将出现MySQL提示符 >。
连接权限数据库: use mysql; 。
4.改密码:update user set password=password(“root”) where user=“root”;(别忘了最后加分号) 。
刷新权限(必须步骤):flush privileges; 。
退出 quit;
重启mysql服务,使用用户名root和刚才设置的新密码root登录就ok了;

在这里插入图片描述

以上2步,可以修改my.ini

C:\ProgramData\MySQL\MySQL Server 5.7\my.ini

”,在“查找内容”处输入[mysqld],并点击“查找下一个”,它会自动转到[mysqld]字段行。在下面增加一行skip-grant-tables并保存,如下图:

MySQL创建全文索引

使用索引是数据库性能优化的必备技能之一。在MySQL数据库中,有四种索引:聚集索引(主键索引)、普通索引、唯一索引以及我们这里将要介绍的全文索引(FULLTEXT INDEX)。

全文索引(也称全文检索)是目前搜索引擎使用的一种关键技术。它能够利用“分词技术“等多种算法智能分析出文本文字中关键字词的频率及重要性,然后按照一定的算法规则智能地筛选出我们想要的搜索结果。在这里,我们就不追根究底其底层实现原理了,现在我们来看看在MySQL中如何创建并使用全文索引。

在MySQL中,创建全文索引相对比较简单。例如,我们有一个文章表(article),其中有主键ID(id)、文章标题(title)、文章内容(content)三个字段。现在我们希望能够在titlecontent两个列上创建全文索引,article表及全文索引的创建SQL语句如下:

--创建article表
CREATE TABLE article (
	id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
	title VARCHAR(200),
	content TEXT,
	FULLTEXT (title, content) --在title和content列上创建全文索引
);

上面就是在创建表的同时建立全文索引的SQL示例。此外,如果我们想要给已经存在的表的指定字段创建全文索引,同样以article表为例,我们可以使用如下SQL语句进行创建:

--给现有的article表的title和content字段创建全文索引
--索引名称为fulltext_article
ALTER TABLE article
ADD FULLTEXT INDEX fulltext_article (title, content)

在MySQL中创建全文索引之后,现在就该了解如何使用了。众所周知,在数据库中进行模糊查询是使用LIKE关键字进行查询,例如:

SELECT * FROM article WHERE content LIKE '%查询字符串%'

那么,我们使用全文索引也是这样用的吗?当然不是,我们必须使用特有的语法才能使用全文索引进行查询。例如,我们想要在article表的titlecontent列中全文检索指定的查询字符串,可以如下编写SQL语句:

SELECT * FROM article WHERE MATCH(title, content) AGAINST('查询字符串')

强烈注意:MySQL自带的全文索引只能用于数据库引擎为MyISAM的数据表,如果是其他数据引擎,则全文索引不会生效。此外,MySQL自带的全文索引只能对英文进行全文检索,目前无法对中文进行全文检索。如果需要对包含中文在内的文本数据进行全文检索,我们需要采用Sphinx(斯芬克斯)/Coreseek技术来处理中文。本站将会在后续文章中对Sphinx以及Coreseek进行介绍。

备注1:目前,使用MySQL自带的全文索引时,如果查询字符串的长度过短将无法得到期望的搜索结果。MySQL全文索引所能找到的词的默认最小长度为4个字符。另外,如果查询的字符串包含停止词,那么该停止词将会被忽略。

备注2:如果可能,请尽量先创建表并插入所有数据后再创建全文索引,而不要在创建表时就直接创建全文索引,因为前者比后者的全文索引效率要高。

mysql之模糊查询的方法

1.主要有两种方式:

like与SQL通配符  和  正则表达式模糊查询。

2.like与SQL ,必须使用like关键字

  • SQL的通配符如下:

% 百分号: 替代一个或者多个字符

_  下划线:仅替代一个字符

[字符列]  :字符列中任何一个单一字符

[^字符列] 或者[!字符列]:  不在字符列中的任何一个单一字符

  • 用法如下

select 字段列表 from 表名 where 字段 like ‘Ne%’;     表示:查询以Ne开头的数据

select 字段列表 from 表名 where 字段 like ‘%Ne%’;  表示:查询含有Ne的数据

select 字段列表 from 表名 where 字段 like ‘L_ve’;     表示:查询含有 L+某一个字符+ve的数据

select 字段列表 from 表名 where 字段 like ’[abc]%’;  表示:查询a/b/c开头的数据

3.正则表达式模糊查询 必须使用regexp关键字

牢记如下规则:

 . , 尖角符  ^ , 美元符 $ ,  字符集 [ ] ,  逻辑或 |  , 星号 * , 加号 + , 问号 ,大括号 { }

  • 语法如下:

select 字段列表 from 表名 where 字段 [not] regexp [binary] ‘正则表达式’;

  • 用法如下:

select 字段列表 from 表名 where 字段 regexp ‘j.’ ;   表示:查询 j 开头且为两个字符的数据

select 字段列表 from 表名 where 字段 regexp ‘158[0-9]{9}’;  表示:查询 158开头,11位的电话号码

。。。。。。

三、总结

  • 正则表达式的模式匹配比like 运算符的模式匹配更加强大、灵活。

Mysql模糊查询正常情况下在数据量小的时候,速度还是可以的,但是不容易看出查询的效率,在数据量达到百万级,千万级的甚至亿级时 mysql查询的效率是很关键的,也是很重要的。

一、一般情况下 like 模糊查询的写法:前后模糊匹配

这个SQL语句,如果用explain解释的话,我们很容易就能发觉它是没有走索引搜索,而是对全表进行了扫描,这显然是很慢的,还有卡库的可能。

如果将上面的SQL语句改成下面的写法:

就是把‘keyword’前面的%去掉了,这样的写法用explain解释看到,SQL语句使用了索引,这样就可以大大的提高查询的效率。

有时候,我们在做模糊查询的时候,并非要想查询的关键词都在开头,所以如果不是特别的要求,”keywork%”并不合适所有的模糊查询。

二、模糊查询高效的方法:

1、LOCATE(’substr’,str,pos)方法

解释:返回 substr 在 str 中第一次出现的位置,如果 substr 在 str 中不存在,返回值为 0 。如果pos存在,返回 substr 在 str 第pos个位置后第一次出现的位置,如果 substr 在 str 中不存在,返回值为0。

实例:

备注:keyword是要搜索的内容,business为被匹配的字段,查询出所有存在keyword的数据

2、POSITION(‘substr’ IN `field`)方法

其实我们就可以把这个方法当做是locate()方法的别名,因为它和locate()方法的作用是一样的。

实例:

3、INSTR(`str`,’substr’)方法

格式:

实例:

除了上述的方法外,还有一个函数FIND_IN_SET,这个方法比较特殊,他所查询的必须要是以“,”分隔开。

4、FIND_IN_SET(str1,str2):

返回str2中str1所在的位置索引,其中str2必须以”,”分割开。

格式:

实例: