Mysql常用命令及sql代码整理 非常实用值得收藏

2018年8月13日09:50:38 发表评论 24 阅读

Part 1

1、MySQL常用命令

create database name; 创建数据库
use databasename; 选择数据库
drop database name 直接删除数据库,不提醒
show tables; 显示表
describe tablename; 表的详细描述
select 中加上distinct去除重复字段
mysqladmin drop databasename 删除数据库前,有提示。

Mysql常用命令及sql代码整理 非常实用值得收藏

Mysql常用命令及sql代码整理

显示当前mysql版本和当前日期

select version(),current_date; 

2、修改mysql中root的密码:

shell>mysql -u root -p 
mysql> update user set password=password("admin") where user='root'; 
mysql> flush privileges //刷新数据库 
mysql>use dbname; 打开数据库: 
mysql>show databases; 显示所有数据库 
mysql>show tables; 显示数据库mysql中所有的表:先use mysql;然后 
mysql>describe user; 显示表mysql数据库中user表的列信息); 

3、grant 创建一个可以从任何地方连接服务器的一个完全的超级用户,但是必须使用一个口令something做这个 mysql> grant all privileges on . to user@localhost identified by 'something' with 增加新用户 格式:grant select on 数据库.* to 用户名@登录主机 identified by “密码”

GRANT ALL PRIVILEGES ON *.* TO monty@localhost IDENTIFIED BY 'something' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO monty@”%” IDENTIFIED BY 'something' WITH GRANT OPTION;

删除授权:

mysql> revoke all privileges on *.* from root@”%”;
mysql> delete from user where user=”root” and host=”%”; 
mysql> flush privileges; 

创建一个用户custom在特定客户端it363.com登录,可访问特定数据库fangchandb

mysql >grant select, insert, update, delete, create,drop on fangchandb.* to custom@ it363.com identified by ‘ passwd'

重命名表:

mysql > alter table t1 rename t2; 

4、mysqldump

备份数据库

shell> mysqldump -h host -u root -p dbname >dbname_backup.sql 

恢复数据库

shell> mysqladmin -h myhost -u root -p create dbname 
shell> mysqldump -h host -u root -p dbname < dbname_backup.sql 

如果只想卸出建表指令,则命令如下:

shell> mysqladmin -u root -p -d databasename > a.sql 

如果只想卸出插入数据的sql命令,而不需要建表命令,则命令如下:

shell> mysqladmin -u root -p -t databasename > a.sql 

那么如果我只想要数据,而不想要什么sql命令时,应该如何操作呢? mysqldump -T./ phptest driver 其中,只有指定了-T参数才可以卸出纯文本文件,表示卸出数据的目录,./表示当前目录,即与mysqldump同一目录。如果不指定driver 表,则将卸出整个数据库的数据。每个表会生成两个文件,一个为.sql文件,包含建表执行。另一个为.txt文件,只包含数据,且没有sql指令。

5、可将查询存储在一个文件中并告诉mysql从文件中读取查询而不是等待键盘输入。可利用外壳程序键入重定向实用程序来完成这项工作。例如,如果在文件my_file.sql 中存放有查 询,可如下执行这些查询: 例如,如果您想将建表语句提前写在sql.txt中:

mysql > mysql -h myhost -u root -p database < sql.txt 

Part 2

一、启动与退出 1、进入MySQL:启动MySQL Command Line Client(MySQL的DOS界面),直接输入安装时的密码即可。此时的提示符是:mysql> 2、退出MySQL:quitexit

二、库操作 1、、创建数据库 命令:create database <数据库名> 例如:建立一个名为xhkdb的数据库

mysql> create database xhkdb; 

2、显示所有的数据库 命令:show databases (注意:最后有个s)

mysql> show databases; 

3、删除数据库 命令:drop database <数据库名> 例如:删除名为 xhkdb的数据库

mysql> drop database xhkdb; 

4、连接数据库 命令: use <数据库名> 例如:如果xhkdb数据库存在,尝试存取它:

mysql> use xhkdb; 

屏幕提示:Database changed

5、当前选择(连接)的数据库

mysql> select database(); 

6、当前数据库包含的表信息:

mysql> show tables; (注意:最后有个s) 

三、表操作,操作之前应连接某个数据库 1、建表 命令:create table <表名> ( <字段名1> <类型1> [,..<字段名n> <类型n>]);

mysql> create table MyClass( 
> id int(4) not null primary key auto_increment, 
> name char(20) not null, 
> sex int(4) not null default '0', 
> degree double(16,2)); 

2、获取表结构 命令: desc 表名,或者show columns from 表名

mysql> desc MyClass; 
mysql> show columns from MyClass; 

3、删除表 命令:drop table <表名> 例如:删除表名为 MyClass 的表

mysql> drop table MyClass; 

4、插入数据 命令:insert into <表名> [( <字段名1>[,..<字段名n > ])] values ( 值1 )[, ( 值n )] 例如,往表 MyClass中插入二条记录, 这二条记录表示:编号为1的名为Tom的成绩为96.45, 编号为2 的名为Joan 的成绩为82.99, 编号为3 的名为Wang 的成绩为96.5.

mysql> insert into MyClass values(1,'Tom',96.45),(2,'Joan',82.99), (2,'Wang', 96.59);

5、查询表中的数据 1)、查询所有行 命令: select <字段1,字段2,...> from < 表名 > where < 表达式 > 例如:查看表 MyClass 中所有数据

mysql> select * from MyClass; 

2)、查询前几行数据 例如:查看表 MyClass 中前2行数据

mysql> select * from MyClass order by id limit 0,2; 

6、删除表中数据 命令:delete from 表名 where 表达式 例如:删除表 MyClass中编号为1 的记录

mysql> delete from MyClass where id=1; 

7、修改表中数据:update 表名 set 字段=新值,… where 条件

mysql> update MyClass set name='Mary' where id=1; 

7、在表中增加字段: 命令:alter table 表名 add字段 类型 其他; 例如:在表MyClass中添加了一个字段passtest,类型为int(4),默认值为0

mysql> alter table MyClass add passtest int(4) default '0' 

8、更改表名: 命令:rename table 原表名 to 新表名; 例如:在表MyClass名字更改为YouClass

mysql> rename table MyClass to YouClass; 

更新字段内容 update 表名 set 字段名 = 新内容 update 表名 set 字段名 = replace(字段名,'旧内容','新内容');

文章前面加入4个空格

update article set content=concat('  ',content);

Part 3 字段类型

1.INT[(M)] 型: 正常大小整数类型

2.DOUBLE[(M,D)] [ZEROFILL] 型: 正常大小(双精密)浮点数字类型

3.DATE 日期类型:支持的范围是1000-01-01到9999-12-31。MySQL以YYYY-MM-DD格式来显示DATE值,但是允许你使用字符串或数字把值赋给DATE列

4.CHAR(M) 型:定长字符串类型,当存储时,总是是用空格填满右边到指定的长度

5.BLOB TEXT类型,最大长度为65535(2^16-1)个字符。

6.VARCHAR型:变长字符串类型

Part 4 mysqldump 命令的使用

备份和导出数据库

mysqldump -h database_ip -u Username -p --opt databasename > backup-file.sql 

只导出数据库表结构

mysqldump -h database_ip -d -u Username -p databasename >database_structure.sql 

只导出数据库中的某个表

mysqldump --opt --add-drop-table -u Username -p databasename tablename > dump.sql

如果不想手工输入密码 请使用--password 参数

mysqldump -h database_ip -u Username --password=123456 --opt databasename > backup-file.sql
mysqldump -h database_ip -d -u Username --password=123456 databasename >database_structure.sql

Part 5 mysql 命令使用

将查询结果保存到文件

select title from book into outfile '/tmp/outfile.txt'; 

查找表中多余的重复记录重复记录是根据某个字段(peopleId)来判断

select * from people where peopleId in (select peopleId from people group by 
peopleId having count(peopleId) > 1); 

查询表中不重复记录(排除重复记录)

select * from phome_ecms_wma where title in (select distinct title from phome_ecms_wma);

删除表中重复记录,重复记录是根据某个字段(title)来判断

select *,count(distinct title) INTO OUTFILE '/tmp/table.bak' from phome_ecms_wma group by title;
delete from phome_ecms_wma; 
LOAD DATA INFILE '/tmp/table.bak' REPLACE INTO TABLE phome_ecms_wma character set utf8;

查询数据库当前编码

mysql> show variables like "character_set%"; 

修改表字段类型

mysql> alter table table_name change last_action last_action datetime NOT NULL default '0000-00-00 00:00:00';

给表添加一个新字段 mysql> ALTER TABLE host ADD ks_mac VARCHAR(100);

从表中删除一个字段

mysql> ALTER TABLE table_name DROP field_name; 

重命名表

mysql>alter table t1 rename t2; 

给字段加索引

mysql> alter table tablename add index 索引名 (字段名1[,字段名2 …]); 
mysql> alter table tablename add index emp_name (name); 

加主关键字的索引

mysql> alter table tablename add primary key(id); 

加唯一限制条件的索引

mysql> alter table tablename add unique emp_name2(cardnumber); 

删除某个索引

mysql>alter table tablename drop index emp_name; 

远程访问mysql 设置

mysql> GRANT ALL PRIVILEGES ON database_test.* to root@192.168.1.9 IDENTIFIED BY '123456';
mysql> FLUSH PRIVILEGES; 

1、使用SHOW语句找出在服务器上当前存在什么数据库

mysql> show databases; 

2、创建一个数据库MYSQLDATA

mysql> create database mydata; 

3、选择你所创建的数据库

mysql> use mydata; 

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 ("test","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=test; 刚安装好的MySQL包含一个含空密码的root帐户和一个匿名帐户,这是很大的安全隐患,对于一些重要的应用我们应将安全性尽可能提高,在这里应把匿名帐户删除、root帐户设置密码,可用如下命令进行:

mysql> use mysql; 
mysql> delete from User where User=""; 
mysql> update User set Password=PASSWORD(newpassword) where User=root; 

如果要对用户所用的登录终端进行限制,可以更新User表中相应用户的Host字段,在进行了以上更改后应重新启动数据库服务,此时登录时可用如下类似命令:

shell> mysql -uroot -p; 
shell> mysql -uroot -pnewpassword; 
shell> mysql mydb -uroot -p; 
shell> mysql mydb -uroot -pnewpassword; 

上面命令参数是常用参数的一部分,详细情况可参考文档。此处的mydb是要登录的数据库的名称。 在进行开发和实际应用中,用户不应该只用root用户进行连接数据库,虽然使用root用户进行测试时很方便,但会给系统带来重大安全隐患,也不利于管理技术的提高。我们给一个应用中使用的用户赋予最恰当的数据库权限。如一个只进行数据插入的用户不应赋予其删除数据的权限。MySQL的用户管理是通过User表来实现的,添加新用户常用的方法有两个,一是在User表插入相应的数据行,同时设置相应的权限;二是通过grant命令创建具有某种权限的用户。其中grant的常用用法如下:

mysql> grant all on mydb.* to NewUserName@HostName identified by "password" ;
mysql> grant usage on *.* to NewUserName@HostName identified by "password";
mysql> grant select,insert,update on mydb.* to NewUserName@HostName identified by "password";
mysql> grant update,delete on mydb.TestTable to NewUserName@HostName identified by "password";

若要给此用户赋予他在相应对象上的权限的管理能力,可在grant后面添加with grant option选项。而对于用插入User表添加的用户,Password字段应用password函数进行更新加密,以防不轨之人窃看密码。对于那些已经不用的用户应给予清除,权限过界的用户应及时回收权限,回收权限可以通过更新User表相应字段,也可以使用revoke操作。以下是常用权限的解释:

全局管理权限

FILE:在MySQL服务器上读写文件。 
PROCESS:显示或杀死属于其它用户的服务线程。 
RELOAD:重载访问控制表,刷新日志等。 
SHUTDOWN:关闭MySQL服务。 
数据库/数据表/数据列权限 
ALTER:修改已存在的数据表(例如增加/删除列)和索引。 
CREATE:建立新的数据库或数据表。 
DELETE:删除表的记录。 
DROP:删除数据表或数据库。 
INDEX:建立或删除索引。 
INSERT:增加表的记录。 
SELECT:显示/搜索表的记录。 
UPDATE:修改表中已存在的记录。 
特别的权限 
ALL:允许做任何事(和root一样)。 
USAGE:只允许登录--其它什么也不允许做。 
=============================================

MySQL常用操作基本操作,以下都是MySQL5.0下测试通过首先说明下,记住在每个命令结束时加上;(分号) 1.导出整个数据库

mysqldump -u 用户名 -p --default-character-set=latin1 数据库名 > 导出的文件名(数据库默认编码是latin1) 
mysqldump -u wcnc -p smgp_apps_wcnc > wcnc.sql 

2.导出一个表

mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名 
mysqldump -u wcnc -p smgp_apps_wcnc users> wcnc_users.sql 

3.导出一个数据库结构

mysqldump -u wcnc -p -d –add-drop-table smgp_apps_wcnc >d:wcnc_db.sql 
-d 没有数据 –add-drop-table 在每个create语句之前增加一个drop table 

4.导入数据库

常用source 命令 进入mysql数据库控制台, 如mysql -u root -p

mysql>use 数据库 

然后使用source命令,后面参数为脚本文件(如这里用到的.sql)

mysql>source d:wcnc_db.sql 

MySQL 的Optimizer(优化元件)在优化SQL语句时,首先需要收集一些相关信息,其中就包括表的cardinality(可以翻译为“散列程度”),它表示某个索引对应的列包含多少个不同的值——如果cardinality大大少于数据的实际散列程度,那么索引就基本失效了。 我们可以使用SHOW INDEX语句来查看索引的散列程度:

SHOW INDEX FROM PLAYERS; 

TABLE KEY_NAME COLUMN_NAME CARDINALITY 
------- -------- ----------- ----------- 
PLAYERS PRIMARY PLAYERNO 14 

因为此时PLAYER表中不同的PLAYERNO数量远远多于14,索引基本失效。 下面我们通过Analyze Table语句来修复索引:

ANALYZE TABLE PLAYERS; 
SHOW INDEX FROM PLAYERS; 
结果是: 
TABLE KEY_NAME COLUMN_NAME CARDINALITY 
------- -------- ----------- ----------- 
PLAYERS PRIMARY PLAYERNO 1000 

此时索引已经修复,查询效率大大提高。

需要注意的是,如果开启了binlog,那么Analyze Table的结果也会写入binlog,我们可以在analyze和table之间添加关键字local取消写入。

Checksum Table

数据在传输时,可能会发生变化,也有可能因为其它原因损坏,为了保证数据的一致,我们可以计算checksum(校验值)。 使用MyISAM引擎的表会把checksum存储起来,称为live checksum,当数据发生变化时,checksum会相应变化。 在执行Checksum Table时,可以在最后指定选项qiuck或是extended;qiuck表示返回存储的checksum值,而extended会重新计算checksum,如果没有指定选项,则默认使用extended。

Optimize Table

经常更新数据的磁盘需要整理碎片,数据库也是这样,Optimize Table语句对MyISAM和InnoDB类型的表都有效。 如果表经常更新,就应当定期运行Optimize Table语句,保证效率。 与Analyze Table一样,Optimize Table也可以使用local来取消写入binlog。

Check Table

数据库经常可能遇到错误,譬如数据写入磁盘时发生错误,或是索引没有同步更新,或是数据库未关闭MySQL就停止了。 遇到这些情况,数据就可能发生错误: Incorrect key file for table: ' '. Try to repair it. 此时,我们可以使用Check Table语句来检查表及其对应的索引。 譬如我们运行

CHECK TABLE PLAYERS; 

结果是

TABLE OP MSG_TYPE MSG_TEXT 
-------------- ----- -------- -------- 
TENNIS.PLAYERS check status OK 

MySQL会保存表最近一次检查的时间,每次运行check table都会存储这些信息:

执行

SELECT TABLE_NAME, CHECK_TIME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME = 'PLAYERS' 
AND TABLE_SCHEMA = 'TENNIS'; 

结果是

TABLE_NAME CHECK_TIME 
---------- ------------------- 
PLAYERS 2006-08-21 16:44:25 

Check Table还可以指定其它选项:
UPGRADE:用来测试在更早版本的MySQL中建立的表是否与当前版本兼容。
QUICK:速度最快的选项,在检查各列的数据时,不会检查链接(link)的正确与否,如果没有遇到什么问题,可以使用这个选项。
FAST:只检查表是否正常关闭,如果在系统掉电之后没有遇到严重问题,可以使用这个选项。
CHANGED:只检查上次检查时间之后更新的数据。
MEDIUM:默认的选项,会检查索引文件和数据文件之间的链接正确性。
EXTENDED:最慢的选项,会进行全面的检查。

Repair Table

用于修复表,只对MyISAM和ARCHIVE类型的表有效。
这条语句同样可以指定选项:
QUICK:最快的选项,只修复索引树。
EXTENDED:最慢的选项,需要逐行重建索引。
USE_FRM:只有当MYI文件丢失时才使用这个选项,全面重建整个索引。

与Analyze Table一样,Repair Table也可以使用local来取消写入binlog。

一、连接MYSQL。

格式: mysql -h主机地址 -u用户名 -p用户密码

1、连接到本机上的MYSQL。

首先打开DOS窗口,然后进入目录mysql\bin,再键入命令mysql -u root -p,回车后提示你输密码.注意用户名前可以有空格也可以没有空格,但是密码前必须没有空格,否则让你重新输入密码.

如果刚安装好MYSQL,超级用户root是没有密码的,故直接回车即可进入到MYSQL中了,MYSQL的提示符是: mysql>

2、连接到远程主机上的MYSQL。假设远程主机的IP为:110.110.110.110,用户名为root,密码为abcd123。则键入以下命令:

mysql -h110.110.110.110 -u root -p 123;(注:u与root之间可以不用加空格,其它也一样)

3、退出MYSQL命令: exit (回车)

二、修改密码。

格式:mysqladmin -u用户名 -p旧密码 password 新密码

1、给root加个密码ab12。首先在DOS下进入目录mysql\bin,然后键入以下命令

mysqladmin -u root -password ab12

注:因为开始时root没有密码,所以-p旧密码一项就可以省略了。

2、再将root的密码改为djg345。

mysqladmin -u root -p ab12 password djg345

三、增加新用户。 (注意:和上面不同,下面的因为是MYSQL环境中的命令,所以后面都带一个分号作为命令结束符)

格式:grant select on 数据库.* to 用户名@登录主机 identified by “密码”

1、增加一个用户test1密码为abc,让他可以在任何主机上登录,并对所有数据库有查询、插入、修改、删除的权限。首先用root用户连入MYSQL,然后键入以下命令:

grant select,insert,update,delete on *.* to test1@”%” Identified by abc”;

但增加的用户是十分危险的,你想如某个人知道test1的密码,那么他就可以在internet上的任何一台电脑上登录你的mysql数据库并对你的数据可以为所欲为了,解决办法见2。

2、增加一个用户test2密码为abc,让他只可以在localhost上登录,并可以对数据库mydb进行查询、插入、修改、删除的操作(localhost指本地主机,即MYSQL数据库所在的那台主机),

这样用户即使用知道test2的密码,他也无法从internet上直接访问数据库,只能通过MYSQL主机上的web页来访问了。

grant select,insert,update,delete on mydb.* to test2@localhost identified by abc”;

如果你不想test2有密码,可以再打一个命令将密码消掉。

grant select,insert,update,delete on mydb.* to test2@localhost identified by “”;

下篇我是MYSQL中有关数据库方面的操作。注意:你必须首先登录到MYSQL中,以下操作都是在MYSQL的提示符下进行的,而且每个命令以分号结束。

一、操作技巧

1、如果你打命令时,回车后发现忘记加分号,你无须重打一遍命令,只要打个分号回车就可以了。

也就是说你可以把一个完整的命令分成几行来打,完后用分号作结束标志就OK。

2、你可以使用光标上下键调出以前的命令。

二、显示命令

1、显示当前数据库服务器中的数据库列表:

mysql> SHOW DATABASES;

注意:mysql库里面有MYSQL的系统信息,我们改密码和新增用户,实际上就是用这个库进行操作。

2、显示数据库中的数据表:

mysql> USE 库名;
mysql> SHOW TABLES;

3、显示数据表的结构:

mysql> DESCRIBE 表名;

4、建立数据库:

mysql> CREATE DATABASE 库名;

5、建立数据表:

mysql> USE 库名;
mysql> CREATE TABLE 表名 (字段名 VARCHAR(20), 字段名 CHAR(1));

6、删除数据库:

mysql> DROP DATABASE 库名;

7、删除数据表:

mysql> DROP TABLE 表名;

8、将表中记录清空:

mysql> DELETE FROM 表名;

9、显示表中的记录:

mysql> SELECT * FROM 表名;

10、往表中插入记录:

mysql> INSERT INTO 表名 VALUES (”hyq”,”M”);

11、更新表中数据:

mysql-> UPDATE 表名 SET 字段名1='a',字段名2='b' WHERE 字段名3='c';

12、用文本方式将数据装入数据表中:

mysql> LOAD DATA LOCAL INFILE “D:/mysql.txt” INTO TABLE 表名;

13、导入.sql文件命令:

mysql> USE 数据库名;
mysql> SOURCE d:/mysql.sql;

14、命令行修改root密码:

mysql> UPDATE mysql.user SET password=PASSWORD('新密码') WHERE User='root';
mysql> FLUSH PRIVILEGES;

15、显示use的数据库名:

mysql> SELECT DATABASE();

16、显示当前的user:

mysql> SELECT USER();

三、一个建库和建表以及插入数据的实例

drop database if exists school; //如果存在SCHOOL则删除

create database school; //建立库SCHOOL

use school; //打开库SCHOOL

create table teacher //建立表TEACHER
(
id int(3) auto_increment not null primary key,
name char(10) not null,
address varchar(50) default ‘深圳',
year date
); //建表结束

//以下为插入字段
insert into teacher values(”,'allen','大连一中','1976-10-10′);
insert into teacher values(”,'jack','大连二中','1975-12-23′);

如果你在mysql提示符键入上面的命令也可以,但不方便调试。

(1)你可以将以上命令原样写入一个文本文件中,假设为school.sql,然后复制到c:\下,并在DOS状态进入目录\mysql\bin,然后键入以下命令:

mysql -uroot -p密码 < c:\school.sql

如果成功,空出一行无任何显示;如有错误,会有提示。(以上命令已经调试,你只要将//的注释去掉即可使用)。

(2)或者进入命令行后使用 mysql> source c:\school.sql; 也可以将school.sql文件导入数据库中。

四、将文本数据转到数据库中

1、文本数据应符合的格式:字段数据之间用tab键隔开,null值用\n来代替.例:

3 rose 大连二中 1976-10-10

4 mike 大连一中 1975-12-23

假设你把这两组数据存为school.txt文件,放在c盘根目录下。

2、数据传入命令 load data local infile “c:\school.txt” into table 表名;

注意:你最好将文件复制到\mysql\bin目录下,并且要先用use命令打表所在的库。

五、备份数据库:(命令在DOS的\mysql\bin目录下执行)

1.导出整个数据库

导出文件默认是存在mysql\bin目录下

mysqldump -u 用户名 -p 数据库名 > 导出的文件名

mysqldump -u user_name -p123456 database_name > outfile_name.sql

2.导出一个表

mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名

mysqldump -u user_name -p database_name table_name > outfile_name.sql

3.导出一个数据库结构

mysqldump -u user_name -p -d add-drop-table database_name > outfile_name.sql

-d 没有数据 –add-drop-table 在每个create语句之前增加一个drop table

4.带语言参数导出

mysqldump -uroot -p default-character-set=latin1 set-charset=gbk skip-opt database_name > outfile_name.sql

一、连接MYSQL。

格式: mysql -h主机地址 -u用户名 -p用户密码

1、例1:连接到本机上的MYSQL。

首先在打开DOS窗口,然后进入目录 mysqlbin,再键入命令mysql -uroot -p,回车后提示你输密码,如果刚安装好MYSQL,超级用户root是没有密码的,故直接回车即可进入到MYSQL中了,MYSQL的提示符是:mysql>

2、例2:连接到远程主机上的MYSQL。假设远程主机的IP为:110.110.110.110,用户名为root,密码为abcd123。则键入以下命令:

mysql -h110.110.110.110 -uroot -pabcd123 

(注:u与root可以不用加空格,其它也一样)

3、退出MYSQL命令: exit (回车)

二、修改密码。

格式:mysqladmin -u用户名 -p旧密码 password 新密码

1、例1:给root加个密码ab12。首先在DOS下进入目录mysqlbin,然后键入以下命令

mysqladmin -uroot -password ab12 

注:因为开始时root没有密码,所以-p旧密码一项就可以省略了。

2、例2:再将root的密码改为djg345。

mysqladmin -uroot -pab12 password djg345 

三、增加新用户。(注意:和上面不同,下面的因为是MYSQL环境中的命令,所以后面都带一个分号作为命令结束符)

格式:grant select on 数据库.* to 用户名@登录主机 identified by \"密码\"

例1、增加一个用户test1密码为abc,让他可以在任何主机上登录,并对所有数据库有查询、插入、修改、删除的权限。首先用以root用户连入MYSQL,然后键入以下命令:

grant select,insert,update,delete on *.* to test1@\"%\" Identified by \"abc\";

但例1增加的用户是十分危险的,你想如某个人知道test1的密码,那么他就可以在internet上的任何一台电脑上登录你的mysql数据库并对你的数据可以为所欲为了,解决办法见例2。

例2、增加一个用户test2密码为abc,让他只可以在localhost上登录,并可以对数据库mydb进行查询、插入、修改、删除的操作(localhost指本地主机,即MYSQL数据库所在的那台主机),这样用户即使用知道test2的密码,他也无法从internet上直接访问数据库,只能通过MYSQL主机上的web页来访问了。

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

如果你不想test2有密码,可以再打一个命令将密码消掉。

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

在上篇我们讲了登录、增加用户、密码更改等问题。下篇我们来看看MYSQL中有关数据库方面的操作。注意:你必须首先登录到MYSQL中,以下操作都是在MYSQL的提示符下进行的,而且每个命令以分号结束。

一、操作技巧

1、如果你打命令时,回车后发现忘记加分号,你无须重打一遍命令,只要打个分号回车就可以了。也就是说你可以把一个完整的命令分成几行来打,完后用分号作结束标志就OK。

2、你可以使用光标上下键调出以前的命令。但以前我用过的一个MYSQL旧版本不支持。我现在用的是mysql-3.23.27-beta-win。

二、显示命令

1、显示数据库列表。

show databases; 

刚开始时才两个数据库:mysql和test。mysql库很重要它里面有MYSQL的系统信息,我们改密码和新增用户,实际上就是用这个库进行操作。

2、显示库中的数据表:

use mysql //打开库,学过FOXBASE的一定不会陌生吧 

show tables;

3、显示数据表的结构:

describe 表名;

4、建库:

create database 库名;

5、建表:

use 库名; 

create table 表名 (字段设定列表);

6、删库和删表:

drop database 库名; 

drop table 表名;

7、将表中记录清空:

delete from 表名;

8、显示表中的记录:

select * from 表名;

三、一个建库和建表以及插入数据的实例

drop database if exists school; //如果存在SCHOOL则删除 

create database school; //建立库SCHOOL 

use school; //打开库SCHOOL 

create table teacher //建立表TEACHER 
( 

id int(3) auto_increment not null primary key, 
name char(10) not null, 
address varchar(50) default '深圳', 
year date 
); //建表结束 

//以下为插入字段 

insert into teacher values('','glchengang','深圳一中','1976-10-10');

insert into teacher values('','jack','深圳一中','1975-12-23');

注:在建表中(1)将ID设为长度为3的数字字段:int(3)并让它每个记录自动加一:auto_increment并不能为空:not null而且让他成为主字段primary key(2)将NAME设为长度为10的字符字段(3)将ADDRESS设为长度50的字符字段,而且缺省值为深圳。varchar和char有什么区别呢,只有等以后的文章再说了。(4)将YEAR设为日期字段。

如果你在mysql提示符键入上面的命令也可以,但不方便调试。你可以将以上命令原样写入一个文本文件中假设为school.sql,然后复制到c:\下,并在DOS状态进入目录\mysql\bin,然后键入以下命令:

mysql -uroot -p密码 < c:\\school.sql 

如果成功,空出一行无任何显示;如有错误,会有提示。(以上命令已经调试,你只要将//的注释去掉即可使用)。

四、将文本数据转到数据库中

1、文本数据应符合的格式:字段数据之间用tab键隔开,null值用\n来代替.

例:

3 rose 深圳二中 1976-10-10

4 mike 深圳一中 1975-12-23

2、数据传入命令 load data local infile \"文件名\" into table 表名;

注意:你最好将文件复制到\mysql\bin目录下,并且要先用use命令打表所在的库。

五、备份数据库:(命令在DOS的\mysql\bin目录下执行)

mysqldump --opt school>school.bbb 

注释:将数据库school备份到school.bbb文件,school.bbb是一个文本文件,文件名任取,打开看看你会有新发现。

有很多朋友虽然安装好了 MySQL 但却不知如何使用它。在这篇文章中我们就从连接 MySQL、修改密码、增加用户等方面来学习一些 MySQL 的常用命令。

一、连接MySQL。

格式: mysql -h主机地址 -u用户名 -p用户密码

1、例1:连接到本机上的MYSQL。

首先在打开 DOS 窗口,然后进入目录 mysql\bin,再键入命令mysql -uroot -p,回车后提示你输密码,如果刚安装好 MySQL,超级用户 root 是没有密码的,故直接回车即可进入到 MySQL 中了,MySQL 的提示符是:mysql>

2、例2:连接到远程主机上的 MySQL。

假设远程主机的IP为:110.110.110.110,用户名为root,密码为abcd123。则键入以下命令:

mysql -h110.110.110.110 -uroot -pabcd123 

(注:u与root可以不用加空格,其它也一样)

3、退出 MySQL 命令: exit (回车)

二、修改密码。

格式:mysqladmin -u用户名 -p旧密码 password 新密码

1、例1:给root加个密码ab12。首先在DOS下进入目录mysqlbin,然后键入以下命令

mysqladmin -u root -password ab12 

注:因为开始时root没有密码,所以-p旧密码一项就可以省略了。

2、例2:再将root的密码改为djg345。

mysqladmin -u root -p ab12 password djg345 

三、增加新用户。

(注意:和上面不同,下面的因为是 MySQL 环境中的命令,所以后面都带一个分号作为命令结束符)

格式:grant select on 数据库.* to 用户名@登录主机 identified by "密码"

例1、增加一个用户 test1 密码为 abc,让他可以在任何主机上登录,并对所有数据库有查询、插入、修改、删除的权限。首先用以 root 用户连入 MySQL,然后键入以下命令:

grant select, insert, update, delete on *.* to test1@"%" Identified by "abc";

但例1增加的用户是十分危险的,你想如某个人知道test1的密码,那么他就可以在internet上的任何一台电脑上登录你的mysql数据库并对你的数据可以为所欲为了,解决办法见例2。

例2、增加一个用户test2密码为abc,让他只可以在localhost上登录,并可以对数据库 mydb进行查询、插入、修改、删除的操作(localhost指本地主机,即MYSQL数据库所在的那台主机),这样用户即使用知道test2的密码,他也无法从internet上直接访问数据库,只能通过MYSQL主机上的web页来访问了。

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

如果你不想test2有密码,可以再打一个命令将密码消掉。

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

在上篇我们讲了登录、增加用户、密码更改等问题。下篇我们来看看MYSQL中有关数据库方面的操作。注意:你必须首先登录到MYSQL中,以下操作都是在MYSQL的提示符下进行的,而且每个命令以分号结束。

一、操作技巧

1、如果你打命令时,回车后发现忘记加分号,你无须重打一遍命令,只要打个分号回车就可以了。也就是说你可以把一个完整的命令分成几行来打,完后用分号作结束标志就OK。

2、你可以使用光标上下键调出以前的命令。但以前我用过的一个MYSQL旧版本不支持。我现在用的是mysql-3.23.27-beta-win。

二、显示命令

1、显示数据库列表。

show databases; 

刚开始时才两个数据库:mysql 和 test。mysql 库很重要它里面有 MySQL 的系统信息,我们改密码和新增用户,实际上就是用这个库进行操作。

2、显示库中的数据表:

use mysql; //打开库,学过 FOXBASE 的一定不会陌生吧 

show tables;

3、显示数据表的结构:

describe 表名;

4、建库:

create database 库名;

5、建表:

use 库名; 

create table 表名 (字段设定列表);

6、删库和删表:

drop database 库名; 

drop table 表名;

7、将表中记录清空:

delete from 表名;

8、显示表中的记录:

select * from 表名;

三、一个建库和建表以及插入数据的实例

drop database if exists school; //如果存在SCHOOL则删除 
create database school; //建立库SCHOOL 
use school; //打开库SCHOOL 
create table teacher //建立表TEACHER 
( 
id int(3) auto_increment not null primary key, 
name char(10) not null, 
address varchar(50) default '深圳', 
year date 
); //建表结束 

//以下为插入字段 
insert into teacher values('','glchengang','深圳一中','1976-10-10'); 
insert into teacher values('','jack','深圳一中','1975-12-23');

注:在建表中(1)将 ID 设为长度为 3 的数字字段 int(3) 并让它每个记录自动加一,auto_increment 并不能为空 not null 而且让他成为主字段 primary key(2)将 NAME 设为长度为 10 的字符字段(3)将 ADDRESS 设为长度 50 的字符字段,而且缺省值为深圳。varchar 和char 有什么区别呢,只有等以后的文章再说了。(4)将 YEAR 设为日期字段。

如果你在 MySQL 提示符键入上面的命令也可以,但不方便调试。你可以将以上命令原样写入一个文本文件中假设为 school.sql,然后复制到 c:\ 下,并在 DOS 状态进入目录 \mysql\bin\ ,然后键入以下命令:

mysql -uroot -p密码 < c:\school.sql 

如果成功,空出一行无任何显示;如有错误,会有提示。(以上命令已经调试,你只要将//的注释去掉即可使用)。

四、将文本数据转到数据库中

1、文本数据应符合的格式:字段数据之间用 tab 键隔开,null 值用 \n 来代替.

例:

3 rose 深圳二中 1976-10-10 

4 mike 深圳一中 1975-12-23 

2、数据传入命令

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

注意:你最好将文件复制到 \mysql\bin 目录下,并且要先用 use 命令打表所在的库。

五、备份数据库: (命令在DOS的 \mysql\bin 目录下执行)

mysqldump --opt school>school.bbb 

注释:将数据库 school 备份到 school.bbb 文件,school.bbb 是一个文本文件,文件名任取,打开看看你会有新发现。

后记:其实 MySQL 的对数据库的操作与其它的 SQL 类数据库大同小异,您最好找本将 SQL 的书看看。我在这里只介绍一些基本的,其实我也就只懂这些了,呵呵。最好的MYSQL教程还是"晏子"译的"MYSQL中文参考手册"不仅免费每个相关网站都有下载,而且它是最权威的。可惜不是象"PHP4中文手册"那样是chm的格式,在查找函数命令的时候不太方便。

MySQL 日期类型:日期格式、所占存储空间、日期范围 比较。 日期类型 存储空间 日期格式 日期范围

------------ --------- --------------------- -----------------------------------------
datetime 8 bytes YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 
timestamp 4 bytes YYYY-MM-DD HH:MM:SS 1970-01-01 00:00:01 ~ 2038 
date 3 bytes YYYY-MM-DD 1000-01-01 ~ 9999-12-31 
year 1 bytes YYYY 1901 ~ 2155 

在 MySQL 中创建表时,对照上面的表格,很容易就能选择到合适自己的数据类型。不过到底是选择 datetime 还是 timestamp,可能会有点犯难。这两个日期时间类型各有优点:datetime 的日期范围比较大;timestamp 所占存储空间比较小,只是 datetime 的一半。

另外,timestamp 类型的列还有个特性:默认情况下,在 insert, update 数据时,timestamp 列会自动以当前时间(CURRENT_TIMESTAMP)填充/更新。“自动”的意思就是,你不去管它,MySQL 会替你去处理。

建表的代码为:

create table t8 (
`id1` timestamp NOT NULL default CURRENT_TIMESTAMP,
`id2` datetime default NULL
);

一般情况下,我倾向于使用 datetime 日期类型。

两者之间的比较:

  1. timestamp容易所支持的范围比timedate要小。 并且容易出现超出的情况

2.timestamp比较受时区timezone的影响以及MYSQL版本和服务器的SQL MODE的影响.

MySQL 时间类型:时间格式、所占存储空间、时间范围。 时间类型 存储空间 时间格式 时间范围

------------ --------- --------------------- -----------------------------------------
time 3 bytes HH:MM:SS -838:59:59 ~ 838:59:59 

time 时间范围居然有这么大的范围,特别是 time 可以取负值,有点奇怪。后来,看了 MySQL 手册才知道这是为了满足两个日期时间相减才这样设计的。

select timediff('2000:01:31 23:59:59', '2000:01:01 00:00:00'); -- 743:59:59
select timediff('2000:01:01 00:00:00', '2000:01:31 23:59:59'); -- -743:59:59 
select timediff('23:59:59', '12:00:00'); -- 11:59:59

注意,timediff 的两个参数只能是 datetime/timestamp, time 类型的,并且这两个参数类型要相同。即:datetime/timestamp 和 datetime/timestamp 比较;time 和 time 相比较。

虽然 MySQL 中的日期时间类型比较丰富,但遗憾的是,目前(2008-08-08)这些日期时间类型只能支持到秒级别,不支持毫秒、微秒。也没有产生毫秒的函数。

《MySQL:MySQL日期数据类型、MySQL时间类型使用总结》适用于 MySQL 5.X 及以上版本。

一、MySQL 获得当前日期时间 函数 1.1 获得当前日期+时间(date + time)函数:now()

mysql> select now(); 

+---------------------+ 
| now() | 
+---------------------+ 
| 2008-08-08 22:20:46 | 
+---------------------+ 

除了 now() 函数能获得当前的日期时间外,MySQL 中还有下面的函数:

current_timestamp() 
,current_timestamp 
,localtime() 
,localtime 
,localtimestamp -- (v4.0.6) 
,localtimestamp() -- (v4.0.6) 

这些日期时间函数,都等同于 now()。鉴于 now() 函数简短易记,建议总是使用 now() 来替代上面列出的函数。

1.2 获得当前日期+时间(date + time)函数:sysdate()

sysdate() 日期时间函数跟 now() 类似,不同之处在于:now() 在执行开始时值就得到了, sysdate() 在函数执行时动态得到值。看下面的例子就明白了:

mysql> select now(), sleep(3), now(); 

+---------------------+----------+---------------------+
| now() | sleep(3) | now() | 
+---------------------+----------+---------------------+ 
| 2008-08-08 22:28:21 | 0 | 2008-08-08 22:28:21 | 
+---------------------+----------+---------------------+ 

mysql> select sysdate(), sleep(3), sysdate(); 

+---------------------+----------+---------------------+
| sysdate() | sleep(3) | sysdate() | 
+---------------------+----------+---------------------+ 
| 2008-08-08 22:28:41 | 0 | 2008-08-08 22:28:44 | 
+---------------------+----------+---------------------+ 

可以看到,虽然中途 sleep 3 秒,但 now() 函数两次的时间值是相同的; sysdate() 函数两次得到的时间值相差 3 秒。MySQL Manual 中是这样描述 sysdate() 的:Return the time at which the function executes。

sysdate() 日期时间函数,一般情况下很少用到。

  1. 获得当前日期(date)函数:curdate()
mysql> select curdate(); 

+------------+ 
| curdate() | 
+------------+ 
| 2008-08-08 | 
+------------+ 

其中,下面的两个日期函数等同于 curdate():

current_date() 
,current_date 
  1. 获得当前时间(time)函数:curtime()
mysql> select curtime(); 

+-----------+ 
| curtime() | 
+-----------+ 
| 22:41:30 | 
+-----------+ 

其中,下面的两个时间函数等同于 curtime():

current_time() 
,current_time 
  1. 获得当前 UTC 日期时间函数:utc_date(), utc_time(), utc_timestamp()
mysql> select utc_timestamp(), utc_date(), utc_time(), now()

+---------------------+------------+------------+---------------------+
| utc_timestamp() | utc_date() | utc_time() | now() | 
+---------------------+------------+------------+---------------------+ 
| 2008-08-08 14:47:11 | 2008-08-08 | 14:47:11 | 2008-08-08 22:47:11 | 
+---------------------+------------+------------+---------------------+ 

因为我国位于东八时区,所以本地时间 = UTC 时间 + 8 小时。UTC 时间在业务涉及多个国家和地区的时候,非常有用。

五、MySQL 时间戳(Timestamp)函数 1. MySQL 获得当前时间戳函数:current_timestampcurrent_timestamp()

mysql> select current_timestamp, current_timestamp();

+---------------------+---------------------+ 
| current_timestamp | current_timestamp() | 
+---------------------+---------------------+ 
| 2008-08-09 23:22:24 | 2008-08-09 23:22:24 | 
+---------------------+---------------------+ 
  1. MySQL (Unix 时间戳、日期)转换函数:
unix_timestamp(), 
unix_timestamp(date), 
from_unixtime(unix_timestamp), 
from_unixtime(unix_timestamp,format) 

下面是示例:

select unix_timestamp(); -- 1218290027 ===得到当前时间的UNIX时间值

将具体时间来转换成timestamp

select unix_timestamp('2008-08-08'); -- 1218124800
select unix_timestamp('2008-08-08 12:30:00'); -- 1218169800

将timestamp来转换成具体时间

select from_unixtime(1218290027); -- '2008-08-09 21:53:47' 
select from_unixtime(1218124800); -- '2008-08-08 00:00:00' 
select from_unixtime(1218169800); -- '2008-08-08 12:30:00' 

select from_unixtime(1218169800, '%Y %D %M %h:%i:%s %x'); -- '2008 8th August 12:30:00 2008'
  1. MySQL 时间戳(timestamp)转换、增、减函数:
timestamp(date) -- date to timestamp 
timestamp(dt,time) -- dt + time 
timestampadd(unit,interval,datetime_expr) -- 
timestampdiff(unit,datetime_expr1,datetime_expr2) --

请看示例部分:

select timestamp('2008-08-08'); -- 2008-08-08 00:00:00
select timestamp('2008-08-08 08:00:00', '01:01:01'); -- 2008-08-08 09:01:01 
select timestamp('2008-08-08 08:00:00', '10 01:01:01'); -- 2008-08-18 09:01:01 

select timestampadd(day, 1, '2008-08-08 08:00:00'); -- 2008-08-09 08:00:00
select date_add('2008-08-08 08:00:00', interval 1 day); -- 2008-08-09 08:00:00

MySQL timestampadd() 函数类似于 date_add()

select timestampdiff(year,'2002-05-01','2001-01-01'); -- -1
select timestampdiff(day ,'2002-05-01','2001-01-01'); -- -485 
select timestampdiff(hour,'2008-08-08 12:00:00','2008-08-08 00:00:00'); -- -12 

select datediff('2008-08-08 12:00:00', '2008-08-01 00:00:00'); -- 7

MySQL timestampdiff() 函数就比 datediff() 功能强多了,datediff() 只能计算两个日期(date)之间相差的天数。


二、MySQL 日期时间 Extract(选取) 函数。 1. 选取日期时间的各个部分:日期、时间、年、季度、月、日、小时、分钟、秒、微秒

set @dt = '2008-09-10 07:15:30.123456'; 

select date(@dt); -- 2008-09-10 
select time(@dt); -- 07:15:30.123456 
select year(@dt); -- 2008 
select quarter(@dt); -- 3 
select month(@dt); -- 9 
select week(@dt); -- 36 
select day(@dt); -- 10 
select hour(@dt); -- 7 
select minute(@dt); -- 15 
select second(@dt); -- 30 
select microsecond(@dt); -- 123456
  1. MySQL Extract() 函数,可以上面实现类似的功能:
set @dt = '2008-09-10 07:15:30.123456'; 

select extract(year from @dt); -- 2008 
select extract(quarter from @dt); -- 3 
select extract(month from @dt); -- 9 
select extract(week from @dt); -- 36 
select extract(day from @dt); -- 10 
select extract(hour from @dt); -- 7 
select extract(minute from @dt); -- 15 
select extract(second from @dt); -- 30 
select extract(microsecond from @dt); -- 123456 

select extract(year_month from @dt); -- 200809 
select extract(day_hour from @dt); -- 1007 
select extract(day_minute from @dt); -- 100715 
select extract(day_second from @dt); -- 10071530 
select extract(day_microsecond from @dt); -- 10071530123456 
select extract(hour_minute from @dt); -- 715 
select extract(hour_second from @dt); -- 71530 
select extract(hour_microsecond from @dt); -- 71530123456 
select extract(minute_second from @dt); -- 1530 
select extract(minute_microsecond from @dt); -- 1530123456 
select extract(second_microsecond from @dt); -- 30123456

MySQL Extract() 函数除了没有date(),time() 的功能外,其他功能一应具全。并且还具有选取‘day_microsecond' 等功能。注意这里不是只选取 day 和 microsecond,而是从日期的 day 部分一直选取到 microsecond 部分。够强悍的吧!

MySQL Extract() 函数唯一不好的地方在于:你需要多敲几次键盘。

  1. MySQL dayof... 函数:dayofweek()dayofmonth()dayofyear()

分别返回日期参数,在一周、一月、一年中的位置。

set @dt = '2008-08-08'; 

select dayofweek(@dt); -- 6 
select dayofmonth(@dt); -- 8 
select dayofyear(@dt); -- 221 

日期 '2008-08-08' 是一周中的第 6 天(1 = Sunday, 2 = Monday, ..., 7 = Saturday);一月中的第 8 天;一年中的第 221 天。

  1. MySQL week... 函数:week(), weekofyear(), dayofweek(), weekday(), yearweek()
set @dt = '2008-08-08'; 

select week(@dt); -- 31 
select week(@dt,3); -- 32 
select weekofyear(@dt); -- 32 

select dayofweek(@dt); -- 6 
select weekday(@dt); -- 4 

select yearweek(@dt); -- 200831

MySQL week() 函数,可以有两个参数,具体可看手册。 weekofyear() 和 week() 一样,都是计算“某天”是位于一年中的第几周。 weekofyear(@dt) 等价于 week(@dt,3)。

MySQL weekday() 函数和 dayofweek() 类似,都是返回“某天”在一周中的位置。不同点在于参考的标准, weekday:(0 = Monday, 1 = Tuesday, ..., 6 = Sunday); dayofweek:(1 = Sunday, 2 = Monday, ..., 7 = Saturday)

MySQL yearweek() 函数,返回 year(2008) + week 位置(31)。

  1. MySQL 返回星期和月份名称函数:dayname()monthname()
set @dt = '2008-08-08'; 

select dayname(@dt); -- Friday 
select monthname(@dt); -- August

思考,如何返回中文的名称呢?

  1. MySQL last_day() 函数:返回月份中的最后一天。
select last_day('2008-02-01'); -- 2008-02-29 
select last_day('2008-08-08'); -- 2008-08-31

MySQL last_day() 函数非常有用,比如我想得到当前月份中有多少天,可以这样来计算:

mysql> select now(), day(last_day(now())) as days;

+---------------------+------+ 
| now() | days | 
+---------------------+------+ 
| 2008-08-09 11:45:45 | 31 | 
+---------------------+------+ 

三、MySQL 日期时间计算函数 1. MySQL 为日期增加一个时间间隔:date_add()

set @dt = now(); 

select date_add(@dt, interval 1 day); -- add 1 day
select date_add(@dt, interval 1 hour); -- add 1 hour 
select date_add(@dt, interval 1 minute); -- ... 
select date_add(@dt, interval 1 second); 
select date_add(@dt, interval 1 microsecond); 
select date_add(@dt, interval 1 week); 
select date_add(@dt, interval 1 month); 
select date_add(@dt, interval 1 quarter); 
select date_add(@dt, interval 1 year); 

select date_add(@dt, interval -1 day); -- sub 1 day

MySQL adddate(), addtime()函数,可以用 date_add() 来替代。下面是 date_add() 实现 addtime() 功能示例:

mysql> set @dt = '2008-08-09 12:12:33'; 

mysql> 
mysql> select date_add(@dt, interval '01:15:30' hour_second); 

+------------------------------------------------+
| date_add(@dt, interval '01:15:30' hour_second) | 
+------------------------------------------------+ 
| 2008-08-09 13:28:03 | 
+------------------------------------------------+ 

mysql> select date_add(@dt, interval '1 01:15:30' day_second);

+-------------------------------------------------+
| date_add(@dt, interval '1 01:15:30' day_second) | 
+-------------------------------------------------+ 
| 2008-08-10 13:28:03 | 
+-------------------------------------------------+ 

date_add() 函数,分别为 @dt 增加了“1小时 15分 30秒” 和 “1天 1小时 15分 30秒”。建议:总是使用 date_add()日期时间函数来替代 adddate(), addtime()。

  1. MySQL 为日期减去一个时间间隔:date_sub()
mysql> select date_sub('1998-01-01 00:00:00', interval '1 1:1:1' day_second);

+----------------------------------------------------------------+
| date_sub('1998-01-01 00:00:00', interval '1 1:1:1' day_second) | 
+----------------------------------------------------------------+ 
| 1997-12-30 22:58:59 | 
+----------------------------------------------------------------+ 

MySQL date_sub() 日期时间函数 和 date_add() 用法一致,不再赘述。另外,MySQL 中还有两个函数 subdate(), subtime(),建议,用 date_sub() 来替代。

  1. MySQL 另类日期函数:period_add(P,N)period_diff(P1,P2)

函数参数“P” 的格式为“YYYYMM” 或者 “YYMM”,第二个参数“N” 表示增加或减去 N month(月)。

MySQL period_add(P,N):日期加/减去N月。

mysql> select period_add(200808,2), period_add(20080808,-2)

+----------------------+-------------------------+
| period_add(200808,2) | period_add(20080808,-2) | 
+----------------------+-------------------------+ 
| 200810 | 20080806 | 
+----------------------+-------------------------+ 

MySQL period_diff(P1,P2):日期 P1-P2,返回 N 个月。 

mysql> select period_diff(200808, 200801); 

+-----------------------------+ 
| period_diff(200808, 200801) | 
+-----------------------------+ 
| 7 | 
+-----------------------------+ 

在 MySQL 中,这两个日期函数,一般情况下很少用到。

  1. MySQL 日期、时间相减函数:datediff(date1,date2)timediff(time1,time2)

MySQL datediff(date1,date2):两个日期相减 date1 - date2,返回天数。

select datediff('2008-08-08', '2008-08-01'); -- 7
select datediff('2008-08-01', '2008-08-08'); -- -7

MySQL timediff(time1,time2):两个日期相减 time1 - time2,返回 time 差值。

select timediff('2008-08-08 08:08:08', '2008-08-08 00:00:00'); -- 08:08:08
select timediff('08:08:08', '00:00:00'); -- 08:08:08

注意:timediff(time1,time2) 函数的两个参数类型必须相同。

四、MySQL 日期转换函数、时间转换函数 1. MySQL (时间、秒)转换函数:time_to_sec(time)sec_to_time(seconds)

select time_to_sec('01:00:05'); -- 3605 
select sec_to_time(3605); -- '01:00:05'
  1. MySQL (日期、天数)转换函数:to_days(date)from_days(days)
select to_days('0000-00-00'); -- 0 
select to_days('2008-08-08'); -- 733627 

select from_days(0); -- '0000-00-00' 
select from_days(733627); -- '2008-08-08'
  1. MySQL Str to Date (字符串转换为日期)函数:str_to_date(str, format)
select str_to_date('08/09/2008', '%m/%d/%Y'); -- 2008-08-09
select str_to_date('08/09/08' , '%m/%d/%y'); -- 2008-08-09 
select str_to_date('08.09.2008', '%m.%d.%Y'); -- 2008-08-09 
select str_to_date('08:09:30', '%h:%i:%s'); -- 08:09:30 
select str_to_date('08.09.2008 08:09:30', '%m.%d.%Y %h:%i:%s'); -- 2008-08-09 08:09:30

可以看到,str_to_date(str,format) 转换函数,可以把一些杂乱无章的字符串转换为日期格式。另外,它也可以转换为时间。“format” 可以参看 MySQL 手册。

  1. MySQL Date/Time to Str(日期/时间转换为字符串)函数:date_format(date,format)time_format(time,format)
mysql> select date_format('2008-08-08 22:23:00', '%W %M %Y');

+------------------------------------------------+
| date_format('2008-08-08 22:23:00', '%W %M %Y') | 
+------------------------------------------------+ 
| Friday August 2008 | 
+------------------------------------------------+ 

mysql> select date_format('2008-08-08 22:23:01', '%Y%m%d%H%i%s');

+----------------------------------------------------+
| date_format('2008-08-08 22:23:01', '%Y%m%d%H%i%s') | 
+----------------------------------------------------+ 
| 20080808222301 | 
+----------------------------------------------------+ 

mysql> select time_format('22:23:01', '%H.%i.%s');

+-------------------------------------+ 
| time_format('22:23:01', '%H.%i.%s') | 
+-------------------------------------+ 
| 22.23.01 | 
+-------------------------------------+ 

MySQL 日期、时间转换函数:date_format(date,format), time_format(time,format) 能够把一个日期/时间转换成各种各样的字符串格式。它是 str_to_date(str,format) 函数的 一个逆转换。

5. MySQL 获得国家地区时间格式函数:get_format()

MySQL get_format() 语法:

get_format(date|time|datetime, 'eur'|'usa'|'jis'|'iso'|'internal'

MySQL get_format() 用法的全部示例:

select get_format(date,'usa') ; -- '%m.%d.%Y' 
select get_format(date,'jis') ; -- '%Y-%m-%d' 
select get_format(date,'iso') ; -- '%Y-%m-%d' 
select get_format(date,'eur') ; -- '%d.%m.%Y' 
select get_format(date,'internal') ; -- '%Y%m%d' 
select get_format(datetime,'usa') ; -- '%Y-%m-%d %H.%i.%s' 
select get_format(datetime,'jis') ; -- '%Y-%m-%d %H:%i:%s' 
select get_format(datetime,'iso') ; -- '%Y-%m-%d %H:%i:%s' 
select get_format(datetime,'eur') ; -- '%Y-%m-%d %H.%i.%s' 
select get_format(datetime,'internal') ; -- '%Y%m%d%H%i%s' 
select get_format(time,'usa') ; -- '%h:%i:%s %p' 
select get_format(time,'jis') ; -- '%H:%i:%s' 
select get_format(time,'iso') ; -- '%H:%i:%s' 
select get_format(time,'eur') ; -- '%H.%i.%s' 
select get_format(time,'internal') ; -- '%H%i%s'

MySQL get_format() 函数在实际中用到机会的比较少。

6. MySQL 拼凑日期、时间函数:makdedate(year,dayofyear), maketime(hour,minute,second)

select makedate(2001,31); -- '2001-01-31' 
select makedate(2001,32); -- '2001-02-01' 

select maketime(12,15,30); -- '12:15:30'

六、MySQL 时区(timezone)转换函数

convert_tz(dt,from_tz,to_tz) 

select convert_tz('2008-08-08 12:00:00', '+08:00', '+00:00'); -- 2008-08-08 04:00:00

时区转换也可以通过 date_add, date_sub, timestampadd 来实现。

select date_add('2008-08-08 12:00:00', interval -8 hour); -- 2008-08-08 04:00:00
select date_sub('2008-08-08 12:00:00', interval 8 hour); -- 2008-08-08 04:00:00 
select timestampadd(hour, -8, '2008-08-08 12:00:00'); -- 2008-08-08 04:00:00

一.错误修正表

mysqladmin flush -tables
myisamchk -o phpbb2_users.MYI //表名

备份:

mysqldump -h localhost -u root -p admin nationz >D:\nationz.sql

同时备份多个MySQL数据库

mysqldump -hhostname -uusername -ppassword –databases databasename1 databasename2 databasename3 > multibackupfile.sql

备份服务器上所有数据库

mysqldump –all-databases > allbackupfile.sql

备份MySQL数据库的命令

mysqldump -hhostname -uusername -ppassword databasename > backupfile.sql

备份MySQL数据库为带删除表的格式,能够让该备份覆盖已有数据库而不需要手动删除原有数据库。

mysqldump -–add-drop-table -uusername -ppassword databasename > backupfile.sql

直接将MySQL数据库压缩备份

mysqldump -hhostname -uusername -ppassword databasename | gzip > backupfile.sql.gz

备份MySQL数据库某个(些)表

mysqldump -hhostname -uusername -ppassword databasename specific_table1 specific_table2 > backupfile.sql

同时备份多个MySQL数据库

mysqldump -hhostname -uusername -ppassword –databases databasename1 databasename2 databasename3 > multibackupfile.sql

仅仅备份数据库结构

mysqldump –no-data –databases databasename1 databasename2 databasename3 > structurebackupfile.sql

备份服务器上所有数据库

mysqldump –all-databases > allbackupfile.sql

还原MySQL数据库的命令

mysql -hhostname -uusername -ppassword databasename < backupfile.sql

还原压缩的MySQL数据库

gunzip < backupfile.sql.gz | mysql -uusername -ppassword databasename

将数据库转移到新服务器

mysqldump -uusername -ppassword databasename | mysql –host=*.*.*.* -C databasename

停止:net stop mysql 启动:net start mysql

在java代码中调用msyql命令行,用Process调用

例如:

Process process = null;
process = Runtime.getRuntime().exec("sql语句");

// 读取属性值
     public static Properties getPprVue(String properName) { 
      //properName 是什么   哪里来的 
      InputStream inputStream = mysql_util.class.getClassLoader().getResourceAsStream(properName); //mysql_util哪里来的
      Properties p = new Properties();
      try {
        p.load(inputStream);
        inputStream.close();
      } catch (IOException e) {
        e.printStackTrace();
      }
      return p;
     }

    public String backupMysql() {
          Properties pros = getPprVue("prop.properties");
          // 这里是读取的属性文件,也可以直接使用
          String username = pros.getProperty("username");
          String password = pros.getProperty("password");         
          // 得到MYSQL的用户名密码后调用 mysql 的 cmd:
          String mysqlpaths = pros.getProperty("mysqlpath");
          String databaseName = pros.getProperty("databaseName");
          String address = pros.getProperty("address");
          String sqlpath = pros.getProperty("sql");
          File backupath = new File(sqlpath);
          if (!backupath.exists()) {
           backupath.mkdir();
          }
          StringBuffer sb = new StringBuffer();
          sb.append(mysqlpaths);
          sb.append("mysqldump ");
          sb.append("--opt ");
          sb.append("-h ");
          sb.append(address);
          sb.append(" ");
          sb.append("--user=");
          sb.append(username);
          sb.append(" ");
          sb.append("--password=");
          sb.append(password);
          sb.append(" ");
          sb.append("--lock-all-tables=true ");
          sb.append("--result-file=");
          sb.append(sqlpath);
          sb.append(sql);
          sb.append(" ");
          sb.append("--default-character-set=utf8 ");
          sb.append(databaseName);
          Runtime cmd = Runtime.getRuntime();
          try {
           Process p = cmd.exec(sb.toString());
          } catch (IOException e) {
           e.printStackTrace();
          }
        return null;
    }

定时备份:

@echo off
D:\MySQL\bin\mysqladmin -u root --password=123456 shutdown
D:\MySQL\bin\mysqldump --opt -u root --password=123456 mydb > E:\mybackup\%date:~0,10%.sql
D:\MySQL\bin\mysqld-nt
hcyaobin

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: