Toggle navigation


MySQL/MariaDB使用参考

Version 1.0.0

MySQL是一个关系型数据库管理系统由瑞典MySQL AB 公司开发,目前属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一,在 WEB 应用方面,MySQL是最好的 RDBMS (Relational Database Management System,关系数据库管理系统) 应用软件。

MariaDB数据库管理系统是MySQL的一个分支,主要由开源社区在维护,采用GPL授权许可 MariaDB的目的是完全兼容MySQL,包括API和命令行,使之能轻松成为MySQL的代替品。在存储引擎方面,使用XtraDB(英语:XtraDB)来代替MySQL的InnoDB。 MariaDB由MySQL的创始人Michael Widenius(英语:Michael Widenius)主导开发,他早前曾以10亿美元的价格,将自己创建的公司MySQL AB卖给了SUN,此后,随着SUN被甲骨文收购,MySQL的所有权也落入Oracle的手中。MariaDB名称来自Michael Widenius的女儿Maria的名字。

本文前提: 用户的ECS已经安装Websoft9提供的相应镜像
镜像安装后,建议您每日使用快照功能进行备份,以便配置错误后,快速恢复系统。

常见新手问题进入,更多的技术支持请访问网址:http://www.websoft9.com/support。

软件版本和目录

版本: MySQL5.7/5.6/5.5或MariaDB10.1/11.1等      具体版本以镜像产品描述文件为准
目录: /usr/local/mysql或/usr/local/mariadb
配置文件: /etc/my.cnf
数据文件目录: /data/mysql或/data/mariadb
默认用户/密码:root/123456  

使用命令行登录数据,可以看到数据库具体版本号:

修改root密码

本文中主要是命令行方式描述。请注意理解命令后使用,不要因为使用错误造成不可挽回的损失,更多mysql命令请上网搜索。

登录命令:mysql -uroot -p密码 

方法1:用UPDATE直接编辑user表
首先登录MySQL。

mysql> use mysql; 
mysql> update user set password=password('1231qaz') where user='root'; 
mysql> flush privileges; 

方法2: 用SET PASSWORD命令
首先登录MySQL。
格式:mysql> set password for 用户名@localhost = password(‘新密码’); 
例子:mysql> set password for root@localhost = password(‘1231qaz’); 

方法3:在忘记root密码的时候,可以这样
以windows为例:
1. 关闭正在运行的MySQL服务。
2. 打开DOS窗口,转到mysqlbin目录。
3. 输入mysqld –skip-grant-tables 回车。–skip-grant-tables 的意思是启动MySQL服务的时候跳过权限表认证。
4. 再开一个DOS窗口(因为刚才那个DOS窗口已经不能动了),转到mysqlbin目录。
5. 输入mysql回车,如果成功,将出现MySQL提示符 >。
6. 连接权限数据库: use mysql;  
6. 改密码:update user set password=password(“1231qaz”) where user=”root”;
7. 刷新权限(必须步骤):flush privileges; 
8. 退出 quit。
9. 注销系统,再进入,使用用户名root和刚才设置的新密码1231qaz登录。

一般情况下,我们会为每个应用创建一个独立的数据库和用户,所以本节中将介绍如何操作。

1.登陆MySQL:
方法1: 使用putty工具远程登录到服务器后台,通过ssh命令登录数据库:
mysql -uroot –p123456                      #默认用户/密码root/123456

方法2: 使用浏览器查看网址 http://服务器公网IP/phpMyAdmin ,打开phpMyAdmin工具。(注意:如是纯数据库镜像,则无法使用该工具)

2.创建数据库,为刚创建的数据库创建一个数据库用户,并授权对数据库操作: 方法点此进入

创建/删除/查看数据库

创建一个数据库

mysql -uroot  –p密码                                        #进入数据库控制台

MySQL [(none)]> create database 数据库名称;     #特别注意有分号

MySQL [(none)]>  show  databases;                      #查看数据库

MySQL [(none)]>  exit;                                 #退出数据库控制台,特别注意有分号

删除一个数据库

MySQL [(none)]> drop database 数据库名称;     #删除数据库

MySQL [(none)]> exit;                                     #退出数据库控制台,特别注意有分号

查看数据库: show databases;           #如下图中3个数据库是默认数据库,不可删除

websoft9-mysql

选择数据库: use dbname;

表的操作

显示库下面的表
show tables;

查看表的结构:
desc tableName;

查看表的创建过程:
show create table tableName;

创建表:
create table tbName ( 列名称1 列类型 [列参数] [not null default ], ….列2… …. 列名称N 列类型 [列参数] [not null default ] )engine myisam/innodb charset utf8/gbk

例子:
create table user ( id int auto_increment, name varchar(20) not null default ”, age tinyint unsigned not null default 0, index id (id) )engine=innodb charset=utf8;
注:innodb是表引擎,也可以是myisam或其他,但最常用的是myisam和innodb,
charset 常用的有utf8,gbk;

修改表
1.修改表之增加列:
alter table tbName add 列名称1 列类型 [列参数] [not null default ] #(add之后的旧列名之后的语法和创建表时的列声明一样)

2.修改表之修改列
alter table tbName change 旧列名 新列名 列类型 [列参数] [not null default ]
(注:旧列名之后的语法和创建表时的列声明一样)

3.修改表之减少列:
alter table tbName drop 列名称;

4.修改表之增加主键
alter table tbName add primary key(主键所在列名);
例:alter table goods add primary key(id)
该例是把主键建立在id列上

5.修改表之删除主键
alter table tbName drop primary key;

6.修改表之增加索引
alter table tbName add [unique|fulltext] index 索引名(列名);

7.修改表之删除索引
alter table tbName drop index 索引名;

8.清空表的数据
truncate tableName;

表数据操作

1.插入数据 
	insert into 表名(col1,col2,……) values(val1,val2……); -- 插入指定列
	insert into 表名 values (,,,,); -- 插入所有列
	insert into 表名 values	-- 一次插入多行 
	(val1,val2……),
	(val1,val2……),
	(val1,val2……);


2.修改数据
	update tablename 
	set 
	col1=newval1,  
	col2=newval2,
	...
	...
	colN=newvalN
	where 条件;

3.删除数据    delete from tablenaeme where 条件;

4.select查询

  (1)  条件查询   where  a. 条件表达式的意义,表达式为真,则该行取出
			   b.  比较运算符  = ,!=,< > <=  >=
                           c.  like , not like ('%'匹配任意多个字符,'_'匹配任意单个字符) 
				in , not in , between and
                           d. is null , is not null			
  (2)  分组       group by 
			一般要配合5个聚合函数使用:max,min,sum,avg,count
  (3)  筛选       having
  (4)  排序       order by
  (5)  限制       limit

5.连接查询
5.1 左连接
	.. left join .. on
	table A left join table B on tableA.col1 = tableB.col2 ; 
  例句:
  select 列名 from table A left join table B on tableA.col1 = tableB.col2
5.2 右链接: right join
5.3 内连接: inner join 左右连接都是以在左边的表的数据为准,沿着左表查右表. 内连接是以两张表都有的共同部分数据为准,也就是左右连接的数据之交集. 7 子查询 where 型子查询:内层sql的返回值在where后作为条件表达式的一部分 例句: select * from tableA where colA = (select colB from tableB where ...); from 型子查询:内层sql查询结果,作为一张表,供外层的sql语句再次查询 例句:select * from (select * from ...) as tableName where ....

字符集、索引、存储引擎、触发器等

字符集
客服端sql编码 character_set_client
服务器转化后的sql编码 character_set_connection
服务器返回给客户端的结果集编码     character_set_results
快速把以上3个变量设为相同值: set names 字符集

索引
提高查询速度,但是降低了增删改的速度,所以使用索引时,要综合考虑. 索引不是越多越好,一般我们在常出现于条件表达式中的列加索引. 值越分散的列,索引的效果越好
索引类型 primary key主键索引 index 普通索引 unique index 唯一性索引 fulltext index 全文索引
存储引擎 engine=12
1 Myisam  速度快 不支持事务 回滚
2 Innodb  速度慢 支持事务,回滚

数据库事务
①开启事务          start transaction
②运行sql;
③提交,同时生效回滚 commitrollback

触发器 trigger
监视地点:表
监视行为:增 删 改
触发时间:afterbefore
触发事件:增 删 改
创建触发器语法
create trigger tgName
after/before insert/delete/update
on tableName
for each row
sql; — 触发语句
删除触发器:drop trigger tgName;

注意:为了安全考虑, 默认情况下数据库没有开启远程连接, 如果需要远程使用数据库客户端直接连接服务器, 请参考以下操作.
1、开启3306端口;

2、开启数据库远程连接,参考:http://bbs.websoft9.com/forum.php?mod=viewthread&tid=360

service mysqld {start|stop|restart|reload|status}

以上命令需要使用putty远程登录到服务器,然后执行命令。如:
service mysqld start                #启动mysql
service mysqld stop #停止mysql
service mysqld restart #重新启动mysql
service mysqld status #查看mysql服务状态

使用putty工具远程登录到服务器后台,通过ssh命令创建FTP账号:

cd /root/oneinstack
./pureftpd_vhost.sh

如下图:
Mirroring_help_ois_10

FTP 客户端参考:windows CMD客户端连接FTP方法参考链接。除此之外,用户可以选择winscp工具上传下载方法,无须创建FTP用户,直接使用root用户即可操作,参考链接

修改root密码

  1. 登录PhpMyAdmin后,默认页面-常规设置,点击“修改密码”
  2. 修改密码->保存->退出登录,刷新浏览器后便可以使用新密码登录了

 

增加一个数据库

  1. 登录PhpMyAdmin后,点击左侧菜单栏的“新建”,进入如下的数据库创建界面
  2. 填写数据库名->点击创建按钮,一个新的数据库变建立成功
  3. 默认情况下,root拥有新建的数据库的全部权限

增加一个数据库用户

数据库用户与数据库是分离的,是“多对多”的关系。可以通过关联使得某个用户具有某个数据库的权限

  1. 登录PhpMyAdmin后,点击左侧菜单栏中新打算对其新增用户的数据库(例如:mywebsoft9)
  2. 点击顶部菜单栏的“权限”,找到“新增用户账户”,如下新增用户界面
  3. 根据上图填写用户名、主机地址和密码,然后关联对应的数据库和勾选权限设置
  4. 点击“执行”,就完成新增用户和数据库关联了

说明:也可以登录PhpMyAdmin的默认页面后,点击顶部菜单上“账户”,对用户和权限进行管理

数据库导入和导出

导出即备份数据库,导入即恢复数据库。这个两个操作对PhpMyAdmin来说比较简单,具体如下:

  1. 登录PhpMyadmin后,选择您需要操作的数据库后,点击顶部菜单栏的“导出”
  2. 选择导出方式(默认为“快速”)和格式(默认为“SQL”),点击“执行”按钮
  3. 数据库备份文件(.sql后缀)生成后,保存到本地完成导出工具
  4. 恢复数据库,对应的是“导入”操作,具体参考下图
  5. 导入文件特别要注意字符集兼容性

如果忘记了root密码,需要通过如下的操作进行MySQL密码重置:
1. 首先停止MySQL服务,输入命令:systemctl stop mysqld.service
2. MySQL服务停止后,输入命令:mysqld_safe –skip-grant-tables
3. 再打开一个ssh终端:输入mysql命令,进入mysql。
4. 在mysql中输入SQL语句 修改密码:
update mysql.user set authentication_string=password(‘123456′) where user=’root’ and Host = ‘localhost’;
显示有OK的时候 输入exit退出
5. 输入命令killall -9 mysqld_safe
6. systemctl start mysqld.service 启动数据库
7. mysql -uroot -p 然后输入修改后的密码 检查是否进入MySQL