关于mysql:mysql常用函数

mysql数据库的启停

ebaserdb用户执行
xpecdmoni 开启
xsuperstop 敞开
mysql.server status 状态

登陆
mysql -uroot -pAAA -h10.47.235.192

查看版本：
mysql --help | grep Distrib
mysql -V
mysql> select version();
mysql> status;

第一招、mysql服务的启动和进行

net stop mysql

net start mysql

第二招、登陆mysql

语法如下： mysql -u用户名 -p用户明码

键入命令mysql -uroot -p，回车后提醒你输出明码，输出12345，而后回车即可进入到mysql中了，mysql的提示符是：

mysql>

留神，如果是连贯到另外的机器上，则须要退出一个参数-h机器IP

第三招、减少新用户

格局：grant 权限 on 数据库.* to 用户名@登录主机 identified by "明码"

如，减少一个用户user1明码为password1，让其能够在本机上登录，并对所有数据库有查问、插入、批改、删除的权限。首先用以root用户连入mysql，而后键入以下命令：

grant select,insert,update,delete on . to user1@localhost Identified by "password1";

如果心愿该用户可能在任何机器上登陆mysql，则将localhost改为"%"。

如果你不想user1有明码，能够再打一个命令将明码去掉。

grant select,insert,update,delete on mydb.* to user1@localhost identified by "";

第四招：操作数据库

登录到mysql中，而后在mysql的提示符下运行下列命令，每个命令以分号完结。

1、显示数据库列表。

show databases;

缺省有两个数据库：mysql和test。 mysql库存放着mysql的零碎和用户权限信息，咱们改明码和新增用户，实际上就是对这个库进行操作。

2、显示库中的数据表：

use mysql;

show tables;

3、显示数据表的构造：

describe 表名;

4、建库与删库：

create database 库名;

drop database 库名;

5、建表：

use 库名;

create table 表名(字段列表);

drop table 表名;

6、清空表中记录：

delete from 表名;

7、显示表中的记录：

select * from 表名;

第五招、导出和导入数据

导出数据：
mysqldump --opt test > mysql.test

行将数据库test数据库导出到mysql.test文件，后者是一个文本文件

如：mysqldump -u root -p123456 --databases dbname > mysql.dbname

就是把数据库dbname导出到文件mysql.dbname中。

导入数据:
mysqlimport -u root -p123456 < mysql.dbname。

不必解释了吧。

将文本数据导入数据库:

文本数据的字段数据之间用tab键隔开。

use test;

load data local infile "文件名" into table 表名;

1:应用SHOW语句找出在服务器上以后存在什么数据库：

mysql> SHOW DATABASES;

2:2、创立一个数据库MYSQLDATA

mysql> CREATE DATABASE MYSQLDATA;

3:抉择你所创立的数据库

mysql> USE MYSQLDATA; (按回车键呈现Database changed 时阐明操作胜利!)

4:查看当初的数据库中存在什么表

mysql> SHOW TABLES;

5:创立一个数据库表

mysql> CREATE TABLE MYTABLE (name VARCHAR(20), sex CHAR(1));

6:显示表的构造：

mysql> DESCRIBE MYTABLE;

7:往表中退出记录

mysql> insert into MYTABLE values ("hyq","M");

8:用文本形式将数据装入数据库表中(例如D:/mysql.txt)

mysql> LOAD DATA LOCAL INFILE "D:/mysql.txt" INTO TABLE MYTABLE;

9:导入.sql文件命令(例如D:/mysql.sql)

mysql>use database;

mysql>source d:/mysql.sql;

10:删除表

mysql>drop TABLE MYTABLE;

11:清空表

mysql>delete from MYTABLE;

12:更新表中数据

mysql>update MYTABLE set sex="f" where name='hyq';

posted on 2006-01-10 16:21 happytian 浏览(6) 评论(0) 编辑珍藏珍藏至365Key

13：备份数据库

mysqldump -u root 库名>xxx.data

14：例2：连贯到近程主机上的MYSQL

假如近程主机的IP为：110.110.110.110，用户名为root,明码为abcd123。则键入以下命令：

mysql -h110.110.110.110 -uroot -pabcd123 // 近程登录

(注:u与root能够不必加空格，其它也一样)

15、退出MYSQL命令： exit (回车)

--part-- funcation

1、substring_index(str,delim,count)

  str:要解决的字符串  delim:分隔符  count:计数

2、concat是连贯几个字符串，能够多个哦。
concat('wo','lin','xue','bin')
后果就是wolinxuebin。

CONCAT_WS('|','w','a','b')
后果： w|a|b

3、字符串长度
CHAR_LENGTH(str)

4、字符串替换
REPLACE(str,from_str,to_str)

5、
SELECT GREATEST(1,2,3,4) ;-- 后果为4
SELECT LEAST(1,2,3,4) ;-- 后果为1

6、
工夫函数
DATE_ADD(NOW(), INTERVAL -12 HOUR); -- 以后工夫12小时前返回类型为 TIMESTAMP

7、定时事件
1) 长期敞开事件
　　
　　ALTER EVENT e_test DISABLE;
　　
2) 开启事件
　　
　　ALTER EVENT e_test ENABLE;

3) 创立事件实例
SET GLOBAL event_scheduler = ON; -- 须要配置事件开关关上
DROP EVENT IF EXISTS P_SUBSCRIBE_CLEAN_JOB; -- 删除事件

CREATE EVENT P_SUBSCRIBE_CLEAN_JOB ON SCHEDULE -- 创立事件语句执行某个存储过程
EVERY 15 MINUTE
STARTS CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
-- ENDS CURRENT_TIMESTAMP + INTERVAL 1 MONTH
ENABLE
DO call zxinsag.p_subscribe_clean();

8、数据类型转换
SELECT CAST('12345.5' AS DECIMAL(9,2));
SELECT CONVERT ('12345.5',DECIMAL(9,2));

DECIMAL(9,2) 总共9位，小数点后2位

后果为： 12345.50

这个类型能够是以下值其中的一个：

BINARY[(N)]
CHAR[(N)]
DATE
DATETIME
DECIMAL
SIGNED [INTEGER]
TIME
UNSIGNED [INTEGER]

--part-- mysql 备份语句

mysqldump -uroot -pdb10&dollar;ZTE --add-drop-database --master-data1 --single-transaction -Y -R -E -B zxinsag zxsdp_op > mysqlbak.sql

-u 用户
-p 明码
-Y 导出表空间
-R 导出存储过程和函数
-E 导出事件
-B 导出数据库列表
-A 导出所有数据库
--single-transaction 在 InnoDB 表在备份时，通常启用选项 --single-transaction 来保障备份的一致性，实际上它的工作原理是设定本次会话的隔离级别为：REPEATABLE READ，以确保本次会话(dump)时，不会看到其余会话曾经提交了的数据。
--master-data1 1示意导出二进制日志的地位和文件名输入到导出文件中，并且在导入的时候执行；2示意导出时作为正文，在导入时不执行。这个参数会进行全库表锁，但如果和 --single-transaction 一起应用，则全局的读锁只在导出开始时呈现很短的工夫。

帮忙：
mysqldump -?

执行脚本：
mysql -uroot -pdb10&dollar;ZTE < mysqlbak.sql

--part-- mysql 表空间概念
一、概念
共享表空间： Innodb的所有数据保留在一个独自的表空间外面，而这个表空间能够由很多个文件组成，一个表能够跨多个文件存在，所以其大小限度不再是文件大小的限度，而是其本身的限度。从Innodb的官网文档中能够看到，其表空间的最大限度为64TB，也就是说，Innodb的单表限度基本上也在64TB左右了，当然这个大小是包含这个表的所有索引等其余相干数据。
独立表空间：

二、查看数据库的表空间
mysql> show variables like 'innodb_data%'
表空间有四个文件组成：ibdata1、ibdata2、ibdata3、ibdata4，每个文件的大小为10M，当每个文件都满了的时候，ibdata4会主动扩大；
以后的存储空间满的时候，能够在其余的磁盘增加数据文件，语法如下：语法如下所示：
pathtodatafile:sizespecification;pathtodatafile:sizespec;.;pathtodatafile:sizespec[:autoextend[:max:sizespecification]]
如果用 autoextend 选项形容最初一个数据文件，当 InnoDB 用尽所有表自由空间后将会主动裁减最初一个数据文件，每次增量为 8 MB。示例：
不论是共享表空间和独立表空间，都会存在innodb_data_file文件，因为这些文件不仅仅要存放数据，而且还要充当着相似于ORACLE的UNDO表空间等一些角色。

三、共享表空间优缺点
既然Innodb有共享表空间和独立表空间两种类型，那么这两种表空间存在必定都有时候本人的利用的场景，存在即正当。以下是摘自mysql官网的一些介绍：
3.1 共享表空间的长处
表空间能够分成多个文件寄存到各个磁盘，所以表也就能够分成多个文件寄存在磁盘上，表的大小不受磁盘大小的限度（很多文档形容有点问题）。
数据和文件放在一起方便管理。
3.2 共享表空间的毛病
所有的数据和索引寄存到一个文件，尽管能够把一个大文件分成多个小文件，然而多个表及索引在表空间中混合存储，当数据量十分大的时候，表做了大量删除操作后表空间中将会有大量的空隙，特地是对于统计分析，对于常常删除操作的这类利用最不适宜用共享表空间。
共享表空间调配后不能回缩：当呈现长期建索引或是创立一个长期表的操作表空间扩充后，就是删除相干的表也没方法回缩那局部空间了（能够理为oracle的表空间10G，然而才应用10M，然而操作系统显示mysql的表空间为10G），进行数据库的冷备很慢；

四、独立表空间的优缺点
4.1 独立表空间的长处
每个表都有自已独立的表空间，每个表的数据和索引都会存在自已的表空间中，能够实现单表在不同的数据库中挪动。
空间能够回收（除drop table操作处，表空不能自已回收）
Drop table操作主动回收表空间，如果对于统计分析或是日值表，删除大量数据后能够通过:alter table TableName engine=innodb;回缩不必的空间。
对于使innodb-plugin的Innodb应用turncate table也会使空间膨胀。
对于应用独立表空间的表，不管怎么删除，表空间的碎片不会太重大的影响性能，而且还有机会解决。
4.2 独立表空间的毛病
单表减少过大，当单表占用空间过大时，存储空间有余，只能从操作系统层面思考解决办法；

五、共享表空间和独立表空间之间的转换
5.1 查看以后数据库的表空间治理类型
mysql> show variables like "innodb_file_per_table";

ON代表独立表空间治理，OFF代表共享表空间治理；（查看单表的表空间治理形式，须要查看每个表是否有独自的数据文件）
5.2 批改数据库的表空间治理形式
批改innodb_file_per_table的参数值即可，然而批改不能影响之前曾经应用过的共享表空间和独立表空间；
innodb_file_per_table=1 为应用独占表空间
innodb_file_per_table=0 为应用共享表空间
5.3共享表空间转化为独立表空间的办法（参数innodb_file_per_table=1须要设置）
单个表的转换操作，脚本：alter table table_name engine=innodb;
当有大量的表须要操作的时候，先把数据库导出，而后删除数据再进行导入操作，该操作能够用mysqldump进行操作。

--part-- MySql 存储引擎
什么是MySql数据库

通常意义上，数据库也就是数据的汇合，具体到计算机上数据库能够是存储器上一些文件的汇合或者一些内存数据的汇合。咱们通常说的MySql数据库，sql server数据库等等其实是数据库管理系统，它们能够存储数据，并提供查问和更新数据库中的数据的性能等等。依据数据库如何存储数据和如何操作数据的实现机制不同，这些数据库之间即有区别又有共同点。MySql数据库是凋谢源代码的关系型数据库。目前，它能够提供的性能有：反对sql语言、子查问、存储过程、触发器、视图、索引、事务、锁、外键束缚和影像复制等。在前期，咱们会具体解说这些性能。同Oracle 和SQL Server等大型数据库系统一样，MySql也是客户/服务器零碎并且是单过程多线程架构的数据库。MySql区别于其它数据库系统的一个重要特点是反对插入式存储引擎。

那么什么是存储引擎呢？

存储引擎说白了就是如何存储数据、如何为存储的数据建设索引和如何更新、查问数据等技术的实现办法。因为在关系数据库中数据的存储是以表的模式存储的，所以存储引擎也能够称为表类型（即存储和操作此表的类型）。在Oracle 和SQL Server等数据库中只有一种存储引擎，所有数据存储管理机制都是一样的。而MySql数据库提供了多种存储引擎。用户能够依据不同的需要为数据表抉择不同的存储引擎，用户也能够依据本人的须要编写本人的存储引擎。

MySql中有哪些存储引擎？

 1 MyISAM：这种引擎是mysql最早提供的。这种引擎又能够分为动态MyISAM、动静MyISAM 和压缩MyISAM三种：动态MyISAM：如果数据表中的各数据列的长度都是事后固定好的，服务器将主动抉择这种表类型。因为数据表中每一条记录所占用的空间都是一样的，所以这种表存取和更新的效率十分高。当数据受损时，复原工作也比拟容易做。动静MyISAM：如果数据表中呈现varchar、xxxtext或xxxBLOB字段时，服务器将主动抉择这种表类型。绝对于动态MyISAM，这种表存储空间比拟小，但因为每条记录的长度不一，所以屡次批改数据后，数据表中的数据就可能离散的存储在内存中，进而导致执行效率降落。同时，内存中也可能会呈现很多碎片。因而，这种类型的表要常常用optimize table 命令或优化工具来进行碎片整顿。压缩MyISAM：以上说到的两种类型的表都能够用myisamchk工具压缩。这种类型的表进一步减小了占用的存储，然而这种表压缩之后不能再被批改。另外，因为是压缩数据，所以这种表在读取的时候要先时行解压缩。然而，不论是何种MyISAM表，目前它都不反对事务，行级锁和外键束缚的性能。2 MyISAM Merge引擎：这种类型是MyISAM类型的一种变种。合并表是将几个雷同的MyISAM表合并为一个虚表。常利用于日志和数据仓库。3 InnoDB：InnoDB表类型能够看作是对MyISAM的进一步更新产品，它提供了事务、行级锁机制和外键束缚的性能。4 memory(heap)：这种类型的数据表只存在于内存中。它应用散列索引，所以数据的存取速度十分快。因为是存在于内存中，所以这种类型常利用于长期表中。5 archive：这种类型只反对select 和 insert语句，而且不反对索引。常利用于日志记录和聚合剖析方面。当然MySql反对的表类型不止下面几种。上面咱们介绍一下如何查看和设置数据表类型。

MySql中对于存储引擎的操作

1 查看数据库能够反对的存储引擎用show engines; 命令能够显示以后数据库反对的存储引擎状况，如图1所示：                                         图1 数据库的存储引擎由上图可见以后零碎的默认数据表类型是MyISAM。当然，咱们能够通过批改数据库配置文件中的选项，设定默认表类型。2 查看表的构造等信息的若干命令要查看表的定义构造等信息能够应用以下几种命令：2.1Desc[ribe] tablename; //查看数据表的构造例如，查看表t1的构造，可得下图。                                     图2：查看表t1的构造2.2 Show create table tablename; //显示表的创立语句同上查问表t1,得下图：                                      图3 显示创立表t1的语句2.3 show table status like ‘tablename’\G显示表的以后状态值 同上查问表t1,得下图：                图4 显示表t1 的以后状态值综上可见，后两种形式都能够帮忙咱们查看某一表的存储引擎类型（图中已用红色方框标出）。3  设置或批改表的存储引擎3.1创立数据库表时设置存储存储引擎的根本语法是：

Create table tableName(
columnName(列名1) type(数据类型) attri(属性设置),
columnName(列名2) type(数据类型) attri(属性设置),
……..) engine = engineName

例如,假如要创立一个名为user的表,此表包含id,用户名username和性别sex三个字段，并且要设置表类型为merge。则可用如下的形式创立此数据表，

create table user(

id int not null auto_increment,

username char(20) not null,

sex char(2),

primary key(id)

) engine=merge

具体执行后果见下图：

        图5 创立表user

查看创立后表user的信息，可见表的以后存储引擎是merge，如图所示：

         图6 显示表t1 的以后状态值3.2批改存储引擎，能够用命令Alter table tableName engine =engineName

如果，若须要将表user的存储引擎批改为archive类型，则可应用命令alter table user engine=archive。如下图所示：

         图7 批改表user的存储引擎

查看批改后的表类型，可见表类型曾经变为archive类型。

         图8 显示表user批改后的状态值

小结

 在本文中次要介绍了什么是MySql数据库，并进一步引出了它的一个重要个性， 即插入式的多存储引擎机制。而后，简略介绍了什么是存储引擎和MySql中几种次要的存储引擎。最初，介绍了如何查看数据库反对的所有存储引擎，如何查看数据库表的存储引擎类型及如何设置或批改表的存储引擎类型。

--part-- mysql 二进制日志

binlog根本定义：二进制日志，也成为二进制日志，记录对数据产生或潜在产生更改的SQL语句，并以二进制的模式保留在磁盘中；

作用：MySQL的作用相似于Oracle的归档日志，能够用来查看数据库的变更历史（具体的工夫点所有的SQL操作）、数据库增量备份和复原（增量备份和基于工夫点的复原）、Mysql的复制（主主数据库的复制、主从数据库的复制）

二进制日志的信息：

文件地位：默认寄存地位为数据库文件所在目录下

文件的命名形式：名称为hostname-bin.xxxxx （重启mysql一次将会主动生成一个新的binlog）

状态的查看：mysql> show variables like '%log_bin%';

mysql> show variables like '%log_bin%';

| Variable_name | Value |

| log_bin | ON | //示意以后已开启二进制日志//

| log_bin_trust_function_creators | OFF |

| sql_log_bin | ON |

3 rows in set (0.00 sec)

二进制日志的治理：

1、开启二进制日志配置

办法一、批改my.cnf参数文件，该办法须要重启

log-bin = mysql-bin #关上日志(主机须要关上)，这个mysql-bin也能够自定义，这里也能够加上门路，如：/home/www/mysql_bin_log/mysql-bin

敞开二进制日志的办法：log-bin = mysql-bin正文掉即可

办法二、不重启批改二进制日志配置，该办法mysql的版本须要5.6以上

SET @@global.log_bin=1|0 （1为开启，0为敞开）

SET @@global.binlog_size=37268(单位bytes)

3、暂停二进制日志

SET sql_log_bin={0|1}

4、批改二进制日志的大小

批改my.cnf参数文件中的max_binlog_size的值；

阐明：如果你的二进制文件的大小超过了max_binlog_size，它就是主动创立新的二进制文件。当然如果恰好在日志文件达到它的最大尺寸时写入了大的事务，那么日志文件还是会超过max_binlog_size的大小

5、进行二进制日志的切换，默认状况下当二进制日志写满了或者数据库重启了才会进行切换，然而也能够手工的进行切换的动作

mysql> flush logs;

6、其余参数：

binlog-cache-size=100m 设置二进制日志缓存大小

sync-binlog=N（每个N秒将缓存中的二进制日志记录写回硬盘，默认值为0。不过，你常常会陷入group commit函数与I/O之间二选一的矛盾。如果在replication环境中，因为思考到耐久性与一致性，则须要设置1。同时，还须要设置innodb_flush_log_at_trx_commit=1以及innodb-support-xa=1默认开启；）

查看二进制日志的内容：

binlog不能间接用文本的形式关上，mysql提供了相应的查看工具：mysqlbinlog，间接查看单个二进制日志文件：mysqlbinlog filename

例如： mysqlbinlog /data/mysql/mysql-bin.000001

当然也能够通过二进制日志实现数据库的复原，具体的应用将在数据库的备份还原中介绍。

删除二进制日志

二进制日志会一直的增长,并产生多个文件。因而,须要制订备份打算和管理策略.无用的二进制日志要记得及时删除。

删除慢查问日志有三种办法:

1、操作系统命令间接删除

2、reset master

3、PURGE BINARY LOGS BEFORE '2014-07-09 12:40:26′;