mysql traffic_mysql命令

服务启动&关闭

net start mysql　// win下开启mysql的服务

net stop mysql　　// win下关闭mysql的服务

#linux下一般需要在root权限下，开启、重启、关闭mysql

mysqladmin start

/ect/init.d/mysql start // 前面为mysql的安装路径

mysqladmin restart

/ect/init.d/mysql restart

mysqladmin shutdown

/ect/init.d/mysql shutdown

登陆&退出

mysql -h 192.168.1.23 -u testuser -p12345abc trafficDB //p与密码之间不加空格

mysql -h game.yxs.db -u testuser -p12345abc -P20001 trafficDB //-h 也在可以为域名，-P指定端口号

exit // 退出mysql

quit // 退出mysql

状态

show character set;　　// 查看当前mysql支持的字符集

show variables like 'character_set%'; // 查看当前mysql使用的字符集设置

show variables like 'collation_%'; // 查看当前mysql使用的校对规则设置

set names utf8;   // 等价于以下3条命令

-----------------------------------

set character_set_client=utf8;

set character_set_results=utf8;

set character_set_connection=utf8;

-----------------------------------

#mysql中有六个关键位置使用了字符集的概念：client 、connection、database、results、server 、system

除了mysql的自身的这几个字符集外，还需要注意两个字符集：

(1) 源数据本身的字符编码

(2) 终端展示的字符编码

character_set_client    // 客户端请求上来的数据的使用的字符集

character_set_results  // 返回给客户端的数据使用的字符集

character_set_connection // 请求与返回都会通过它转码，可以当作一个透明的中间层

乱码包括4个方面：【字符集从大集合到小集合，由于大集合的数据超出小集合的范围才会导致乱码】

(1) 把编码A的数据当作编码B来解析(如果编码B不兼容编码A，则会出现不可逆的永久性数据损坏)

(2) 数据被转编码，但可逆(暂时乱码) 如：gbk->utf8(gbk的数据转编成utf8的数据)

(3) 数据被转编码，不可逆(永久性数据损坏) 如：utf8->gbk(utf8的数据转编成gbk的数据)

(4) 数据被截断(永久性数据损坏)(类似于第二点，但和第二点有本质的区别) 如：gbk->gb2312(因为：gb18030>gbk>gb2312)

用户

select user(); // 当前登陆用户 testuser@localhost

show grants; // 查看当前用户的权限

show grants for testuser@localhost; // 查看testuser的用户权限

flush privileges; // 修改完权限后，刷新使其生效

// 新建一个只能从localhost登陆的testuser用户，密码为123456的用户

create user 'testuser'@'localhost' identified by '123456';

// 新建一个只能从localhost登陆的testuser用户，无密码

create user 'testuser'@'localhost' identified by '';

// 删除只能从localhost登陆的testuser用户

drop user 'testuser'@'localhost';

// 用户testuser可以从任意一台主机登陆(%表示任意字符串，也就是在任何主机上都可以)

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

// 删除testuser的数据库操作权限

revoke all privileges on * . * from 'testuser'@'localhost';

// 删除testuser授权给其他用户的权限

revoke grant option on * . * from 'testuser'@'localhost';

// 分配指定权限给testuser用户，并对该用户进行mysql的资源限定

grant select, create,show databases on * . * to 'testuser'@'localhost'

with MAX_QUERIES_PER_HOUR 1000 MAX_CONNECTIONS_PER_HOUR 100 MAX_UPDATES_PER_HOUR 50 MAX_user_CONNECTIONS 8;

// 设置testuser用户有对trafficdb数据库的操作权限

grant all privileges on `trafficdb` . * to 'testuser'@'localhost';

// 将testuser的密码设置为123456

set password for 'testuser'@'localhost' = password( '123456' );

// 使用mysqladmin修改mysql中testuser用户的密码

mysqladmin -h 192.168.1.88 -u testuser -p password 123456

数据库

1. 创建

create dababase trafficDB;

// 创建名为trafficDB的数据库，字符集设置为utf8，校对规则设置为utf8_bin

create dababase `trafficDB` default character set utf8 collate utf8_bin;

注：ci是case insensitive, 即 "大小写不敏感", a 和 A 会在字符判断中会被当做一样的;

bin是二进制, a和A会别区别对待.

2. 删除

drop database trafficDB;

drop database if exists trafficDB;

3. 设置

use trafficDB; //设置当前数据库

4. 查看

show databases; // 查看当前用户下所有数据库

select database(); // 显示当前操作数据库

show create dababasetrafficDB; // 查看trafficDB的字符集与校对规则设置

5. 修改

alter database trafficdb default character set 'gbk' collate 'gbk_bin'; // 修改trafficDB的字符集为gbk、校对规则为gdb_bin(不会修改已存在数据的字符编码)

6. 导出

// 导出trafficDB数据库中，所有建表命令与表中的数据到trafficDB.sql中

mysqldump -h 192.168.1.88 -u testuser -p trafficDB > trafficDB.sql

// 以gbk编码方式，导出trafficDB数据库中，所有建表命令与表中的数据到trafficDB.sql中

mysqldump -h 192.168.1.88 -u testuser -p --default-character-set=gbk trafficDB > trafficDB.sql

// 导出trafficDB数据库中，所有建表命令到createTables.sql中

mysqldump -h 192.168.1.88 -u testuser -p -d trafficDB > createTables.sql

// 导出trafficDB数据库中，所有建表命令(增加若表存在则先删除命令)到createTables.sql中

mysqldump -h 192.168.1.88 -u testuser -p -d --add-drop-table trafficDB > createTables.sql

// 导出trafficDB数据库中，所有表中的数据到tablesData.sql中

mysqldump -h 192.168.1.88 -u testuser -p -t trafficDB > tablesData.sql

7. 脚本导入

#注意导入文件的字符编码格式；否则导入后，中文字串的字符会出现乱码现象

mysqldump -h 192.168.1.88 -u testuser -p create trafficDB // 创建trafficDB数据库

mysqldump -h 192.168.1.88 -u testuser -p trafficDB < trafficDB.sql // 将trafficDB.sql导入到trafficDB数据库中

mysql -h 192.168.1.88 -u testuser -p trafficDB < trafficDB.sql // 将trafficDB.sql导入到trafficDB数据库中

// shell中执行sql语句

mysql -h 192.168.1.88 -u testuser -p123456 -e"use Commodity;insert into CommodityInfo values(1,'衣服',1000);"

source /home/dbuser/trafficDB.sql // 在mysql中执行trafficDB.sql脚本

8. 文本导入

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

3　　rose　　深圳二中　　1976-10-10

4　　mike　　深圳一中　　1975-12-23

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

注：最好将文件复制到\mysql\bin目录下，并且要先用use命令打开表所在的库

表

1. 创建

create table `trafficdb`.`road` (

`id` int( 4 ) not null auto_increment primary key comment '主键id',

`name` varchar( 255 ) character set utf8 collate utf8_bin null ,

`post_no` int( 4 ) not null default '23410',

`length` float not null ,

`build_date` date not null ,

`build_time` time not null ,

`date_time` datetime not null ,

`data` blob not null ,

unique (`post_no`) ,

index ( `length` )

);

create table `student` (

`id` tinyint( 255 ) unsigned not null auto_increment primary key ,

`content` varchar( 255 ) not null

) type = myisam character set gbk collate gbk_chinese_ci;

2. 删除

drop table road ;

drop table if exists road ;

3. 清空

truncate table road ;

4. 查看

show tables;

show create table road;//查看road表的字符集与校对规则设置

5. 修改表名

rename table vehicle to driver;

alter table vehicle rename driver;

alter table vehicle default character set 'utf8' collate 'utf8_bin'; //不会修改已存在数据的字符编码

6. 表结构

desc road; 【describe road;】//查看表结构

show columns from road; //查看表结构

alter table `road` add `extra` bool null comment '额外说明'; //在末尾增加一个字段extra

alter table `road` add `extra` int( 4 ) not null first; //在表开头添加一个字段extra

// 在字段length后添加一个字段extra

alter table `road` add `extra` char( 1 ) character set ascii collate ascii_bin null after `length`;

alter table `road` drop `extra`; //删除字段extra

alter table `road` change `post_no` `post_no` text null; //修改字段post_no信息

7. 三种索引：主键索引(primary key)、唯一索引(unique)、普通索引(index)

# 若表中有自动增长的字段，若要设置primary key，只能为该字段

alter table `road` drop primary key;  //删除road表的主键

alter table `road` add primary key ( `id` ); //将id设为主键

alter table `road` drop primary key , add primary key ( `post_no` ); //删除当前主键，将post_no设为主键

alter table `road` add unique (`post_no`); // 将post_no设置为unique

alter table `road` add index ( `post_no` ); // 将post_no设置为index

show index from road; // 显示road表上所有索引

show keys from road; // 显示road表上所有索引

create index idx1 on road (length); //在road表的名为length字段上添加名为idx1的索引

create unique index uq1 on road (post_no); //在road表的名为post_no字段上添加名为uq1的unique索引

drop index idx1 on road;  // 删除名为idx1的索引

alter table road drop index uq1; //删除名为uq1的索引

8. 强制更新表

flush tableroad;

9. 优化表

optimize tableroad;

10. 导出

// 导出trafficDB数据库的road表中，所有建表命令与表中的数据到trafficDB.sql中

mysqldump -h 192.168.1.88 -u testuser -p trafficDB road > trafficDB_road.sql

记录

1. 查询

select * from road;

select * from road \G;  // 以列的方式显示查询到的内容

select * from road limit 1, 5 ; // 显示查询到的第1-5行(序号从0开始)

select * from road limit 10 ; // 等价于select * from road limit 0, 10 ;

select id, name, post_no from road order by id desc; // asc表示升序(不写默认为升序)，desc表示降序

select count(*) from road; // road表的记录数

select max(length) from road; // road表中所有记录的length字段的最大值

select min(length) from road; // road表中所有记录的length字段的最小值

select sum(length) from road; // road表中所有记录的length字段的之和

select avg(length) from road; // road表中所有记录的length字段的平均值

select distinct country, city FROM Customers; // 去重查询Customers表中country与city字段(同样的记录会被删除)

select * from `qpapers`

where `id` >=11100

and `title` like '%中国%'

and `type` is not null; // 条件查询

select * from `qpapers`

where `id` >=11100

and `type` in ( 0, 1, 2 ); // 条件查询

select count( * ) as `行数`, `type` from `qpapers`

group by `type` order by `type`; // 查询type字段的非重复值

2. 插入

insert into `qss`.`qpapers` (

`id` ,`title` ,`type` ,`status` ,`style_id` ,`owner` ,`url` ,`conn_qp_id` ,`start_time` ,`end_time`)

values ('120', '你好', '3', '2', '0', 'admin', null , null , '2013-05-05 15:46:05', null);

insert into `trafficdb`.`road` (

`id` ,`name` ,`post_no` ,`length` ,`build_date` ,`build_time` ,`date_time` ,`blob_data`)

values ('2', '珞瑜路', '450000', '50.8', '2013-05-05', now( ) , '2013-05-05 15:54:21', 0x3002a00c20010706);

insert into myclass values(1, 'Tom', 96.45),(2, 'Joan', 82.99),(3, 'Wang' 90);

3. 更新

update  `qss`.`qpapers` set  `end_time` =  '2013-05-05 15:41:40' where  `qpapers`.`id` =11138 ;

update  `qss`.`qpapers` set  `title` = `love`, `end_time` =  '2013-05-05 15:41:40' where  `qpapers`.`id` =11138 ;

4. 替换

replace into `road` (`id`, `name`, `post_no`, `length`, `build_date`, `build_time`, `date_time`, `blob_data`) values

(2, '珞瑜路', 450000, 50.8, '2013-05-05', '15:58:33', '2013-05-05 15:54:21', 0x3002a00c20010706);

replace into myclass values(1, 'Tom', 96.45),(2, 'Joan', 82.99),(3, 'Wang' 90);

5. 删除

delete from `qss`.`qpapers` where `qpapers`.`id` = 11138 ;

函数

select from_unixtime(1367997752);　　// 得到2013-05-08 15:22:32

select unix_timestamp("2013-05-08 15:22:32"); // 得到1367997752

select unix_timestamp(now()); // 得到1367662473

select current_date(); // 得到当前日期 2013-05-05

select ( 20 +5 ) *4 as Result, sin(pi( )/3), now(); // 100 0.86602540378444 2013-05-05 16:45:49

select hex( 'Aa我' ); // 查看字符串二进制，得到4161E68891(当前字符集为utf8)

select unhex('4161E68891');// 重新得到Aa我

select cast(-123 as char(4)); // 得到'-123'字符串  整型转字符串

select cast(-123 as char(3)); // 得到'-12'字符串整型转字符串

select cast(123 as char(3)); // 得到'123'字符串整型转字符串

select convert(-123,char(4)); // 得到'-123'字符串  整型转字符串

select convert(-123,char(3)); // 得到'-12'字符串  整型转字符串

select convert(123,char(3)); // 得到'123'字符串  整型转字符串

select concat(520193225,'');  // 得到'520193225'字符串  整型转字符串

select concat(520193225,'0');  // 得到'5201932250'字符串

select '520193118'+0; // 得到520193118整型  字符串转整型

参考