net stop mysql
#linux下一般需要在root權限下，開啟、重啟、關閉mysql
mysqladmin start
/ect/init.d/mysql start
mysqladmin restart
/ect/init.d/mysql restart
mysqladmin shutdown
/ect/init.d/mysql shutdown
登陸&退出
mysql -h 192.168.1.23 -u testuser -p12345abc trafficDB
exit
quit
show character set;
show variables like 'character_set%';
show variables like 'collation_%';
set names utf8;
-----------------------------------
set character_set_client=utf8;
set character_set_results=utf8;
set character_set_connection=utf8;
-----------------------------------
除了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();
show grants;
show grants for testuser@localhost;
flush privileges;
create user 'testuser'@'localhost' identified by '123456';
create user 'testuser'@'localhost' identified by '';
drop user 'testuser'@'localhost';
grant select,insert,update,delete on *.* to 'testuser'@"%" Identified by "abc";
revoke all privileges on * . * from 'testuser'@'localhost';
revoke grant option on * . * from 'testuser'@'localhost';
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;
grant all privileges on `trafficdb` . * to 'testuser'@'localhost';
set password for 'testuser'@'localhost' = password( '123456' );
mysqladmin -h 192.168.1.88 -u testuser -p password 123456
數據庫
1. 創建
create dababase trafficDB;
create dababase `trafficDB` default character set utf8 collate utf8_bin;
2. 刪除
drop database trafficDB;
drop database if exists trafficDB;
3. 設置
use trafficDB;
4. 查看
show databases;
select database();
show create dababase trafficDB;
5. 修改
alter database trafficdb default character set 'gbk' collate 'gbk_bin'; ）
6. 導出
mysqldump -h 192.168.1.88 -u testuser -p trafficDB > trafficDB.sql
mysqldump -h 192.168.1.88 -u testuser -p --default-character-set=gbk trafficDB > trafficDB.sql
mysqldump -h 192.168.1.88 -u testuser -p -d trafficDB > createTables.sql
mysqldump -h 192.168.1.88 -u testuser -p -d --add-drop-table trafficDB > createTables.sql
mysqldump -h 192.168.1.88 -u testuser -p -t trafficDB > tablesData.sql
#注意導入文件的字符編碼格式；否則導入後，中文字串的字符會出現亂碼現象
mysqldump -h 192.168.1.88 -u testuser -p create trafficDB
mysqldump -h 192.168.1.88 -u testuser -p trafficDB < trafficDB.sql
mysql -h 192.168.1.88 -u testuser -p trafficDB < trafficDB.sql
mysql -h 192.168.1.88 -u testuser -p123456 -e"use Commodity;insert into CommodityInfo values(1,'衣服',1000);"
source /home/dbuser/trafficDB.sql
8. 文本導入
(1) 文本數據應符合的格式：字段數據之間用tab鍵隔開，null值用\n來代替. 例：
3 rose 深圳二中 1976-10-10
4 mike 深圳一中 1975-12-23
(2) 數據傳入命令
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;
5. 修改表名
rename table vehicle to driver;
alter table vehicle rename driver;
alter table vehicle default character set 'utf8' collate 'utf8_bin';
6. 表結構
desc road; 【】
show columns from road;
alter table `road` add `extra` bool null comment '額外說明';
alter table `road` add `extra` int( 4 ) not null first;
alter table `road` add `extra` char( 1 ) character set ascii collate ascii_bin null after `length`;
alter table `road` drop `extra`;
alter table `road` change `post_no` `post_no` text null;
7. 三種索引：主鍵索引（primary key）、唯一索引（unique）、普通索引（index）
alter table `road` drop primary key;
alter table `road` add primary key ( `id` );
alter table `road` drop primary key , add primary key ( `post_no` );
alter table `road` add unique (`post_no`);
alter table `road` add index ( `post_no` );
show index from road;
show keys from road;
create index idx1 on road (length);
create unique index uq1 on road (post_no);
drop index idx1 on road;
alter table road drop index uq1;
flush table road;
9. 優化表
optimize table road;
10. 導出
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 ;
select * from road limit 10 ;
select id, name, post_no from road order by id desc;
select count(*) from road;
select max(length) from road;
select min(length) from road;
select sum(length) from road;
select avg(length) from road;
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`;
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 ;
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);
select unix_timestamp("2013-05-08 15:22:32");
select unix_timestamp(now());
select current_date();
select ( 20 +5 ) *4 as Result, sin(pi( )/3), now();
select hex( 'Aa我' );
// 重新得到Aa我
