mysql常用操作命令， mysql脚本优化工具使用说明-CSDN博客

Mysql 忘记root密码

方法一：
在/etc/my.cfg [mysqld]下面添加
skip-grant-tables 或是skip-grant

重启MySQL
use mysql;
UPDATE user SET Password = password ( 'new-password' ) WHERE User = 'root' ;
mysql flush privileges;
将my.cfg文件修改回来，重启MySQL即可。

方法二：

在启动Mysql服务器时加上参数--skip-grant-tables来跳过授权表的验证
(./safe_mysqld --skip-grant-tables &)，这样我们就可以直接登陆Mysql服务器，
然后再修改root用户的口令，重启Mysql就可以用新口令登陆了。

不登陆数据库执行mysql命令小结

1.通过echo实现（这个比较常见）

echo "show databases;" | mysql -uroot -p'123456' -S /data/3308/mysql.sock

2.通过cat实现（此法用的不多,适合多行字符串比较多的时候）

cat |mysql -uroot -p'123456' -S /data/3308/mysql.sock << EOF

show databases;

EOF

3.通过mysql -e参数实现

mysql -u root -p'123456' -S /data1/3307/mysql.sock -e "show databases;"

特殊生产场景应用：

例一：mysql自动批量制作主从同步需要的语句。

cat |mysql -uroot -p'oldboy' -S /data/3308/mysql.sock<< EOF

CHANGE MASTER TO

MASTER_HOST='10.0.0.16',

MASTER_PORT=3306,

MASTER_USER='oldboyrep',

MASTER_PASSWORD='oldboyrep',

MASTER_LOG_FILE='mysql-bin.000025'

MASTER_LOG_POS=4269;

EOF

例二：mysql线程中，“大海捞针”

平时登陆数据库show processlist;,发现结果超长，找自己要看的的比较困难，而且，

SQL显示不全。如果直接执行show full processlist那更是瞬间滚了N屏。找到有问题的SQL语句非常困难，现在通过如下语句过滤当前执行的SQL语句完整内容，后面还可以加iconv等对中文转码。

根据需求过滤想要的内容，此命令屡试不爽啊。

mysql -u root -p'oldboy' -S /data1/3307/mysql.sock -e "show full processlist;"|grep -v Sleep

添加环境变量
export PATH=/usr/local/mysql/bin:$PATH 加入到/etc/profile.d/mysql.sh中

只需本机使用Mysql服务
在启动时还可以加上--skip-networking参数使Mysql不监听任何TCP/IP连接
(./safe_mysqld --skip-networking &)，增加安全性。

查看支持的存储引擎

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.01 sec)

查看当前MySQL的默认数据引擎

mysql> show variables like '%engine%';
+---------------------------+--------+
| Variable_name             | Value  |
+---------------------------+--------+
| default_storage_engine    | InnoDB |
| engine_condition_pushdown | ON     |
| storage_engine            | InnoDB |
+---------------------------+--------+
3 rows in set (0.00 sec)
mysql> show variables;   查看相关参数

查看和修改最大连接数

使用MySQL 数据库的站点，当访问连接数过多时，就会出现 "Too many connections" 的错误。
出现这种错误有两种情况:

一种是网站访问量实在太大，服务器已经负担不起，此时就应该考虑负载均衡或者其它减少服务器压力的办法。

另一种情况就是 MySQL最大连接数设置得太小，当访问量稍大就出现连接过多的错误。

show variables like '%max_connections%';
要对 mysql最大连接数进行修改，只需要在my.cnf 配置文件里面修改max_connections的值，然后重启mysql就行。

如果my.cnf 文件中没有找到 max_connections 条目，可自行添加以下条目。
max_connections = 220
或是
mysql>set global max_user_connections = 200; [仅本次修改有效]

实时查看mysql当前连接数
前提：对用户和远程主机有授权

mysql> grant all on *.* to root@192.168.9.9 identified by '123456';
mysql> flush privileges;

[root@test ~]# mysqladmin -uroot -p -h192.168.9.9 processlist;

只查看当前连接数(Threads就是连接数）
[root@OceanV ~]# mysqladmin -uroot -p123456 status;
Uptime: 4449 Threads: 2 Questions: 18697 Slow queries: 0 Opens: 77 Flush tables: 1 Open tables: 31 Queries per second avg: 4.202

mysql> show full processlist; 或者show status;

查看一下所有连接进程，注意查看进程等待时间以及所处状态是否locked
如果进程过多，就把进程打印下来，然后查看.
mysql -e 'show full processlist;' -p > 111.txt
查找非locked的进程，一般就是当前执行中卡死，导致后面的进程排队的原因。

查看MySQL连接数和当前用户Mysql连接数
先用管理员身份进入mysql提示符。
mysql -uroot -pxxxx
mysql >show processlist; #可以显示前100条连接信息
mysql >show full processlist; #可以显示全部。如果用普通账号登录，就只显示这用户的。

修改MySQL监听端口
1.如果安装的是系统自带的rpm包的情况：
修改 /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
port=3300 #修改成你自己想要的端口
socket=/var/lib/mysql/mysql.sock
[mysql.server]
user=mysql
basedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

2.如果安装的是源码包或者在同一服务器上安装多个mysqld服务情况：
找出my.cnf 文件。添加port= 端口号
需要注意的是
[client]
port = 3300
这里也要改一下。

在同一台机器上配置两个MySQL服务（跑两个端口）
cd /usr/local/

cp -r mysql mysql_2

cd mysql_2;
初始化mysql2

./scripts/mysql_install_db --user=mysql --datadir=/data/mysql2
拷贝配置文件：

cp /etc/my.cnf ./my.cnf
修改配置文件相关参数：vim my.cnf #更改port 以及socket
启动：/usr/local/mysql_2/bin/mysqld_safe --defaults-file=/usr/local/mysql_2/my.cnf --user=mysql &
若开机启动它，需加入到 /etc/rc.local中

查看mysql服务器当前版本, 当前日期, 当前用户, mysql状态, 当前数据库

mysql> select version();
mysql> select current_date();

mysql> select user();

mysql> show status;

mysql> select database();

查看服务器基本信息

mysql> \s
mysql Distrib 5.5.34, for Linux (x86_64) 
Connection id:          141700
Current database:       lab
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.5.34-log Source distribution
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:            /tmp/mysql.sock
Uptime:                 47 days 4 hours 54 min 1 sec
Threads: 4  Questions: 189434783  Slow queries: 1  Opens: 283512  Flush tables: 23  Open tables: 46  Queries per second avg: 46.447

常用字符集：atin英文，big5繁体中文，GB2312中文，UTF8通用语言

Mysql对于字符集的支持细化到四个层次:

服务器(server)；

数据库(database)；

数据表(table)(字段column)；

连接(connection)；

从上面可以看到四种默认字符集是latin1，修改四种默认字符集，client和connection两个要是一样的。

方法一：

set character_set_server=utf8; ##修改服务器默认字符集

set character_set_database=utf8; ##修改数据库字符集

set character_set_client=utf8; ##修改数据库客户端字符集

set character_set_connection=utf8; ##修改连接默认字符集

方法二：修改my.cnf默认字符集，重启mysql才会生效

[mysqld]

character_set_server=utf8

[mysql]

default-character-set=utf8

2.查看创建数据库时使用的字符集

show create database mydb;

3.修改已经存在库的字符集

3.1修改存在数据库的字符集

alter database mydb character set utf-8;

3.2修改存在表的字符集

alter table mytable default character set utf8

3.3修改存在表字段字符集

alter table mytable change old_column new_column varchar(255) character set utf8;

修改mysql参数
mysql> show variables like 'max_connect%';

mysql> set global max_connect_errors = 1000;

开启服务器　　
/etc/rc.d/init.d/mysqld start 或者 mysqld_safe --user=mysql &

停止服务器
/etc/rc.d/init.d/mysqld stop 或者 /usr/bin/mysqladmin -u root -p shutdown

更改MySQL目录
MySQL默认的数据文件存储目录为/var/lib/mysql。假如要把目录移到/home/data下需要进行下面几步：
1、home目录下建立data目录
mkdir /home/data
2、把MySQL服务进程停掉：
mysqladmin -u root -p shutdown
3、把/var/lib/mysql整个目录移到/home/data
mv /var/lib/mysql　/home/data/
这样就把MySQL的数据文件移动到了/home/data/mysql下
4、找到my.cnf配置文件
如果/etc/目录下没有my.cnf配置文件，请到/usr/share/mysql/下找到*.cnf文件，拷贝其中一个到/etc/并改名为my.cnf中。命令如下：
[root@test1 mysql]# cp /usr/share/mysql/my-medium.cnf　/etc/my.cnf
5、编辑MySQL的配置文件/etc/my.cnf
为保证MySQL能够正常工作，需要指明mysql.sock文件的产生位置。修改socket=/var/lib/mysql/mysql.sock一行中等号右边的值为：/home/mysql/mysql.sock 。
操作如下：
vim /etc/my.cnf
# The MySQL server
[mysqld]
port　　　= 3306
#socket　 = /var/lib/mysql/mysql.sock（原内容，为了更稳妥用#注释此行）
socket　 = /home/data/mysql/mysql.sock　　　（加上此行）
6、修改MySQL启动脚本/etc/rc.d/init.d/mysql
修改MySQL启动脚本/etc/rc.d/init.d/mysql，把其中datadir=/var/lib/mysql一行中，等号右边的路径改成你现在的实际存放路径：/home/data/mysql。
[root@test1 etc]# vi　/etc/rc.d/init.d/mysql
#datadir=/var/lib/mysql　　　　（注释此行）
datadir=/home/data/mysql　　（加上此行）
7、重新启动MySQL服务
/etc/rc.d/init.d/mysql restart
如果工作正常移动就成功了，否则对照前面的7步再检查一下。

赋权　　
myaql >grant all on dbname.* to username@% identified by 'password';
mysql >flush privileges;刷新权限表　

例如：增加用户user1密码为password1，让其可以在本机上登录，对所有数据库有查询、插入、修改、删除权限,首先使用root用户登录mysql

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

如果希望该用户能够在任何机器上登陆mysql，则将localhost改为"%"。
如果你不想user1有密码，可以再打一个命令将密码去掉。

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

mysql> grant all privileges on wpj1105.* to sunxiao@localhost identified by '123'; ###all privileges 所有权限

创建数据库

#判断是否存在数据库class,有的话先删除,注意数据库名不能以数字开头。

mysql> select database(); ##查看当前数据库

mysql> create database if not exists class;

mysql> show databases class\G; ##查看建库语句

*************************** 1. row ***************************

Database: mysql

Create Database: CREATE DATABASE `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */

mysql> create database if not exists class character set charset_name collate collation_name;

mysql> drop database if exists class;

创建表

mysql> use test;

mysql> drop table if exists student;

mysql> create table student(
id int auto_increment primary key,
name varchar(50),
sex varchar(20),
date varchar(50),
content varchar(100)
)default charset=utf8;

或者是

mysql> use test;

mysql> show tables;

mysql> desc student; 或者show create table person;

mysql> drop table student;

mysql> create table if not exits student (

id int auto_increment primary key,
name varchar(50),
sex varchar(20),
date varchar(50),
content varchar(100)
)default charset=utf8;

mysql> rename table user_node_log to user_node_log_bak; ###更改表名http://www.cnblogs.com/yunf/archive/2011/04/20/2022193.html

mysql支持全文索引

Linux服务器为my.cnf，在 [mysqld] 下面加入一行“ft_min_word_len=1”，然后重启Mysql

Windows 服务器为my.ini，修改方法相同，修改完成之后，大家在程序中就可以使用全文索引了。

查看表的状态信息

mysql> show table status like 'game_userlog'\G
*************************** 1. row ***************************
           Name: game_userlog_temp
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 9777636
 Avg_row_length: 259
    Data_length: 2535456768
Max_data_length: 0
   Index_length: 1654489088
      Data_free: 1970274304
 Auto_increment: 10342138
    Create_time: 2015-07-14 10:22:47
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: row_format=DYNAMIC
        Comment: ??????????
1 row in set (0.26 sec)

查看表所有的列：

mysql> SHOW FULL COLUMNS from person;

优化表　

mysql> use qqtexas;
mysql> optimize table users;

mysql> optimize table users,userid;

或者进行优化并检查修复任务

[root@test ~]# mysqlcheck -o dbname tblA tblB tblC -u root -p

对表进行分析　　
mysql> use qqtexas;

mysql> analyze table tblA;
或
[root@test ~]# mysqlcheck -a dbname [tblA tblB tblC ] -u root -p

对表进行检查　

mysql> use qqtexas;

mysql> check table users;

将一张表的数据插入到另一张表中（二张表结构相同）

insert into game_userlog_temp select * from game_userlog;

复制表的数据（包括表结构）

create table game_userlog_temp (select * from game_userlog);

查询一张表的最后一条数据记录

SELECT * from game_log_bak order by RoundID desc LIMIT 1；

复制表结构

create table a like users;

更改表存储引擎

alter table tt7 engine=innodb;

MySQL中将字符串aaa批量替换为bbb的SQL语句

UPDATE 表名 SET 字段名 = REPLACE (字段名, 'aaa', 'bbb');

#插入数据

insert into student values(null,'aa','男','1988-10-2','......');
insert into student values(null,'bb','女','1889-03-6','......');
insert into student values(null,'cc','男','1889-08-8','......');
insert into student values(null,'dd','女','1889-12-8','......');
insert into student values(null,'ee','女','1889-09-6','......');
insert into student values(null,'ff','null','1889-09-6','......');
#查询表中的数据
select * from student;
select id,name from student;

#修改某一条数据
update student set sex='男' where id=4;

#删除数据
delete from student where id=5;

# and 且
select * from student where date>'1988-1-2' and date<'1988-12-1';

# or 或
select * from student where date<'1988-11-2' or date>'1988-12-1';

#between
select * from student where date between '1988-1-2' and '1988-12-1';

#in 查询制定集合内的数据
select * from student where id in (1,3,5);

#排序 asc 升序 desc 降序
select * from student order by id asc;

#分组查询 #聚合函数
select max(id),name,sex from student group by sex;

select min(date) from student;

select avg(id) as '求平均' from student;

select count(*) from student; #统计表中总数

select count(sex) from student; #统计表中性别总数若有一条数据中sex为空的话,就不予以统计

#查询第i条以后到第j条的数据(不包括第i条)
select * from student limit 2,5; #显示3-5条数据

#巩固练习
create table c(
id int primary key auto_increment,
name varchar(10) not null,
sex varchar(50) , #DEFAULT '男' ,
age int unsigned, #不能为负值(如为负值则默认为0)
sno int unique #不可重复
);
desc c;

insert into c (id,name,sex,age,sno) values (null,'涛哥','男',68,1);
insert into c (id,name,sex,age,sno) values (null,'aa','男',68,2);
insert into c (id,name,sex,age,sno) values (null,'平平','男',35,3);

select * from c;

#修改数据
update c set age=66 where id=2;
update c set name='花花',age=21,sex='女' where id=2
delete from c where age=21;

#常用查询语句
select name,age ,id from c
select * from c where age>40 and age<60; #and
select * from c where age<40 or age<60;  #or
select * from c where age between 40 and 60 #between
select * from c where age in (30,48,68,99);     #in 查询指定集合内的数据
select * from c order by age desc;      #order by （asc升序 des降序）

#分组查询
select name,max(age) from c group by sex; #按性别分组查年龄最大值
#聚合函数
select min(age) from c;
select avg(age) as '平均年龄 ' from c;
select count(*) from c; #统计表中数据总数
select sum(age) from c;

#修改表的名字
#格式:alter table tbl_name rename to new_name
alter table c rename to a;

#表结构修改
create table test
(
id int not null auto_increment primary key, #设定主键
name varchar(20) not null default 'NoName', #设定默认值
department_id int not null,
position_id int not null,
unique (department_id,position_id) #设定唯一值
);

#修改表的名字
#格式:alter table tbl_name rename to new_name
alter table test rename to test_rename;

#向表中增加一个字段(列)
#格式:alter table tablename add columnname type;/alter table tablename add(columnname type);
alter table test add columnname varchar(20);

#修改表中某个字段的名字
alter table tablename change columnname newcolumnname type; #修改一个表的字段名
alter table test change name uname varchar(50);

select * from test;

#表position 增加列test
alter table position add(test char(10));
#表position 修改列test
alter table position modify test char(20) not null;
#表position 修改列test 默认值
alter table position alter test set default 'system';
#表position 去掉test 默认值
alter table position alter test drop default;
#表position 去掉列test
alter table position drop column test;
#表depart_pos 删除主键
alter table depart_pos drop primary key;
#表depart_pos 增加主键
alter table depart_pos add primary key PK_depart_pos
(department_id,position_id);

#用文本方式将数据装入数据库表中（例如D:/mysql.txt）
load data local infile "D:/mysql.txt" into table MYTABLE;

#导入.sql文件命令（例如D:/mysql.sql）
mysql> source d:/mysql.sql;

创建表字段索引

1.主键索引

创建一个主键索引，一般创建表时已经设置

alter table user add primary key(id);

删除一个主键索引

alter table user drop primary key;

2.普通索引

创建一个普通索引

alter table user add index index_name(name);
删除一个普通索引

alter table user drop index index_name;

查看主键索引和普通索引

show index from user;

show keys from user;

注：修改字段名不影响索引。

3.查看表中创建的索引

mysql> desc user2;
+-------+------------------+------+-----+---------+----------------+
| Field | Type               | Null    | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned   | NO      | PRI | NULL    | auto_increment |
| name  | varchar(30)        | YES     | MUL | NULL    |                |
| pass  | varchar(30)        | YES     |     | NULL    |                |

上面key中的PRI代表是主键索引，MUL代表是普通索引。

4.查看索引信息

mysql> show index from user2;
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+
| Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+
| user2 |          0 | PRIMARY    |            1 | id          | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
| user2 |          1 | index_name |            1 | name        | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+

5.测试是否使用索引

mysql> desc select id,name from user2 where name='zhangsan';
+----+-------------+-------+------+---------------+------------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key        | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+---------------+------------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | user2 | ref  | index_name    | index_name | 33      | const |    1 | Using where; Using index |
+----+-------------+-------+------+---------------+------------+---------+-------+------+--------------------------+

用desc检测语句，可以看到查询一条张三的记录，使用了普通索引index_name这个名字，检索了一行(rows)就找到了这条记录。

从mysqldump文件中恢复一张表

可以先到原数据库中使用"show tables;" 查看数据库表列表，注意，此列表已经按照字母排序比如，

table1

table2

table3

然后使用awk来过滤sql语句，假设你要恢复table2 表，可以使用下面的语句

# awk '/^-- Table structure for table .table2./,/^-- Table structure for table .table3./{print}' qqtexas.sql > /tmp/recovered_table.sql

从一个多数据库备份中提取一个数据库备份（假设数据库名字是 test）

# sed -n '/^-- Current Database: `test`/,/^-- Current Database: `/p' fulldump.sql > /tmp/test.sql

MySQL binlog 查看信息

1）按时间筛选

mysqlbinlog --start-datetime="2009-09-14 0:20:00" --stop-datetim="2009-09-15 01:25:00" xxx_db-bin.000001

2）按位置删选

mysqlbinlog --start-position=134 --stop-position=330 mysqlbin-log.000001

从mysqldump备份的文件中提取表结构或数据

#!/bin/bash
while getopts lf:dst:h ACT
 do
    case $ACT in
       l) LTBL="Y";;
       f) FLE=$OPTARG;;
       t) TBL=$OPTARG;;
       d) TBLD="Y";;
       s) TBLS="Y";;
    esac
done
######OPTIONS -h --- help
usage(){
  echo "USAGE:`basename $0` -f dumpfile [-lds][-t tablename]"
  echo " -f dumpfile     Dump file(with mysqldump)"
  echo " -l              List all tables in dumpfile"
  echo " -t tablename    Table Name(\" -t 1 \" list all table name,\" -t 2 \"list all table structure)"
  echo " -s              Export table structure"
  echo " -d              Export table data"
  echo " -h              Help,this massage"
  echo " MySQL(5.0|5.1)  mysqldump file splitor V1.0.11,BUG report to lybadmin@gmail.com"
}
 
if [ ${#} -lt 3 -o "$1" = "-h" ];then
  usage
  exit
fi
 
########OPTION -l ---List All Tables
if [ "$LTBL" = "Y" ];then
 awk '/^CREATE TABLE/{print $3}' $FLE|sed -e 's/`//gi'|nl
 exit
fi
 
########OPTION -t
if [ "X$TBL" != "X" ];then
   if [ "$TBL" = "1" ];then
       awk '/^CREATE TABLE.+/{print $3}' $FLE|nl
       exit
    elif [ "$TBL" = "2" ];then
      awk '/^CREATE TABLE.+/,/ENGINE=/{print $0}' $FLE
     exit
    fi
########OPTION -s
  if [ "$TBLS" = "Y" ];then
    CMDSTR="awk '/^CREATE TABLE \`$TBL\`[( )|\(]+.+/,/ENGINE=/{print \$0}' $FLE"
    eval $CMDSTR
    exit
  fi
########OPTION -d
  if [ "$TBLD" = "Y" -o "X${TBLS}${TBLD}" = "X" ];then 
    CMDSTR="awk '/^INSERT INTO \`$TBL\`.*\(.+\).+\);$/{print \$0}' $FLE"
    #echo $CMDSTR
    eval $CMDSTR
   exit
  fi
fi

从命令行登录MySQL数据库服务器

登录使用默认3306端口的MySQL
/usr/local/mysql/bin/mysql -u root -p
通过TCP连接管理不同端口的多个MySQL（注意：MySQL4.1以上版本才有此项功能）
/usr/local/mysql/bin/mysql -u root -p --protocol=tcp --host=localhost --port=3307
通过socket套接字管理不同端口的多个MySQL
/usr/local/mysql/bin/mysql -u root -p --socket=/tmp/mysql3307.sock
通过端口和IP管理不同端口的多个MySQL
/usr/local/mysql/bin/mysql -u root -p -P 3306 -h 127.0.0.1

修改登录密码
MySQL默认没有密码，安装完毕增加密码的重要性是不言而喻的。
usr/bin/mysqladmin -u root password 'new-password'
格式：mysqladmin -u用户名 -p旧密码 password 新密码

显示数据表的结构

mysql> use qqtexas;

mysql> desc tablesname;

显示表的创建信息
show create table tablesname;

更改表结构，将zhangyan表username字段的字段类型改为CHAR(25)
ALTER TABLE zhangyan CHANGE username username CHAR(25);

创建名称为ocean的数据库

mysql> create database ocean;

mysql> drop database ocean;

mysql> drop tables tablesname;

将表中记录清空

delete from命令格式：delete from 表名 where 表达式
例如，删除表 MyClass中编号为1 的记录： mysql> delete from MyClass where id=1;

mysql> delete from tablesname;

mysql> truncate table db1.t1;

将当前目录下的mysql.sql导入到数据库中
mysql> source ./mysql.sql;

添加主键： alter table 表名 add primary key(列名)
删除主键： alter table 表名 drop primary key;
给某列改名： alter table 表名 change 列名属性列表

比如alter table student change id no char(4)
添加某列：
1 在表的最前面添加列比如给student表添加id属性放在表的最前面
alter table student add id int(4) not null auto_increment primary key first;
2 在表的某个列后加列比如给student表添加id属性放在列no的最前面
alter table student add a int(11) afer no;
删除某列： alter table 表名 drop column 列名;
更改某列属性比如修改student no字段属性 alter table student modify no char(4);
添加索引：alter table 表名 add index 列名
删除索引：alter table 表名 drop index 列名
删除外键：alter table 表名 drop foreign key 约束名
limit的使用：
select * from 表名 limit 2;//如果只给定一个参数，它表示返回最大的记录行数目
select * from 表名 limit 1,3;// 第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目.

mysql命令行备份数据库

常见选项：

--all-databases, -A：备份所有数据库

--databases, -B：用于备份多个数据库，如果没有该选项，mysqldump 将把第一个名字参数作为数据库名，后面的作为表名。使用该选项，mysqldum把每个名字都当作为数据库名。

--force, -f：即使发现sql错误，仍然继续备份

--host=host_name, -h host_name：备份主机名，默认为localhost

--no-data, -d：只导出表结构

--password[=password], -p[password]：连接mysql数据库的密码

--port=port_num, -P port_num：连接mysql的端口号

--quick, -q：快速导出

--tables：覆盖 --databases or -B选项，后面所跟参数被视作表名

--user=user_name, -u user_name：连接mysql数据库的用户名

--xml, -X：导出为xml文件

例如：

数据库地址：127.0.0.1

数据库用户名：root

数据库密码：pass

数据库名称：myweb

备份服务器上所有数据库的数据和结构

mysqldump --all-databases -h127.0.0.1 -uroot -ppass > allbackupfile.sql

或者

mysqldump -h127.0.0.1 -uroot -ppass -A > all_data.sql

备份服务器上所有数据库的数据(加 -t 参数)

mysqldump -h127.0.0.1 -uroot -ppass -A -t > F:\all_data.sql

备份服务器上所有数据库的结构（加 -d 参数）

mysqldump -h127.0.0.1 -uroot -ppass -A -d > F:\all_struct.sql

同时一次备份多个MySQL数据库

mysqldump -h127.0.0.1 -uroot -ppass --databases myweb myweb2 > multibackupfile.sql

备份MySQL数据库某(些)表。此例备份table1表和table2表。备份到linux主机的/home下

mysqldump -h127.0.0.1 -uroot -ppass myweb table1 table2 > /home/backupfile.sql

备份单个数据库的数据和结构(数据库名myweb)

mysqldump -uroot -ppass myweb > F:\mydb.sql

备份数据库到D盘跟目录

mysqldump -h127.0.0.1 -uroot -ppass myweb > d:/backupfile.sql

导出单个数据库中的其中一张表的结构和数据

mysqldump -h127.0.0.1 -uroot -ppass database table1 > table1.sql

备份服务器上单个数据库的结构（不包含数据）

mysqldump -h127.0.0.1 -uroot -ppass myweb -d > F:\myweb.sql

仅仅备份数据库结构。同时备份名为myweb数据库和名为myweb2数据库

mysqldump --no-data -h127.0.0.1 -uroot -ppass --databases myweb myweb2 > structurebackupfile.sql

导出单个数据库中的其中一张表结构（不包含数据）

mysqldump -h127.0.0.1 -uroot -ppass -d myweb table1 > struty_table1.sql

备份服务器上单个数据库的数据

mysqldump -h127.0.0.1 -uroot -ppass myweb -t > F:\myweb.sql

直接将myweb数据库压缩备份备份到D盘跟目录

mysqldump -h127.0.0.1 -uroot -ppass myweb | gzip > d:/backupfile.sql.gz

备份到当前目录备份MySQL数据库为带删除表的格式，能够让该备份覆盖已有数据库而不需要手动删除原有数据库

mysqldump --add-drop-table -h127.0.0.1 -uroot -ppass myweb > backupfile.sql

mysql还原部分分（1）mysql命令行source方法和（2）系统命令行方法

1.还原服务器上所有数据库:

(1) mysql命令行：mysql>source f:\allbackupfile.sql

(2) 系统命令行： mysql -h127.0.0.1 -uroot -p123456 < allbackupfile.sql

2.还原服务器上单个数据库(需指定数据库)

(1) mysql>use mydb

mysql>source f:\mydb.sql

(2) mysql -h127.0.0.1 -uroot -p123456 mydb <f:\mydb.sql

3.还原服务器上单个数据库的多个表(需指定数据库)

(1) mysql>use mydb

mysql>source f:\multables.sql

(2) mysql -h127.0.0.1 -uroot -p123456 mydb <f:\multables.sql

4.还原服务器上多个数据库，（一个备份文件里有多个数据库的备份，此时不需要指定数据库）

(1) mysql命令行：mysql>source f:\muldbs.sql

(2) 系统命令行： mysql -h127.0.0.1 -uroot -p123456 <f:\muldbs.sql

5.还原压缩的MySQL数据库

gunzip < backupfile.sql.gz | mysql -h127.0.0.1 -uroot -ppass myweb

6.将数据库转移到新服务器。此例为将本地数据库myweb复制到远程数据库名为serweb中，其中远程数据库必须有名为serweb的数据库

mysqldump -h127.0.0.1 -uroot -ppass myweb | mysql --host=***.***.***.*** -u数据库用户名 -p数据库密码 -C serweb

windows下面备份和还原数据库相关操作

命令行导出数据库：

1，进入MySQL目录下的bin文件夹：cd MySQL中到bin文件夹的目录

如我输入的命令行：cd C:\Program Files\MySQL\MySQL Server 5.5\bin

(或者直接将windows的环境变量path中添加该目录)

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

如我输入的命令行:mysqldump -u root -p news > news.sql (输入后会让你输入进入MySQL的密码)

（如果导出单张表的话在数据库名后面输入表名即可）

3、会看到文件news.sql自动生成到bin文件下面。

命令行导入数据库：

1，将要导入的.sql文件移至bin文件下，这样的路径比较方便

2，同上面导出的第1步

3，进入MySQL：mysql -u 用户名 -p

例如我输入的命令行:mysql -u root -p (输入同样后会让你输入MySQL的密码)

4，在MySQL-Front中新建你要建的数据库，这时是空数据库，如新建一个名为news的目标数据库

5，输入：mysql>use 目标数据库名

例如我输入的命令行:mysql>use news;

6，导入文件：mysql>source 导入的文件名;

如我输入的命令行：mysql>source news.sql;

MySQL备份和还原,都是利用mysqldump、mysql和source命令来完成的。

查看和修改三种MySQL字符集的方法

一、查看MySQL数据库服务器和数据库MySQL字符集。

mysql> show variables like '%char%';

+--------------------------+-------------------------------------+------

| Variable_name | Value |......

+--------------------------+-------------------------------------+------

| character_set_client | utf8 |...... -- 客户端字符集

| character_set_connection | utf8 |......

| character_set_database | utf8 |...... -- 数据库字符集

| character_set_filesystem | binary |......

| character_set_results | utf8 |......

| character_set_server | utf8 |...... -- 服务器字符集

| character_set_system | utf8 |......

| character_sets_dir | D:\MySQL Server 5.0\share\charsets\ |......

+--------------------------+-------------------------------------+------

二、查看MySQL数据表（table）的MySQL字符集。

mysql> show table status from sqlstudy_db like '%countries%';

+-----------+--------+---------+------------+------+-----------------+------

+-----------+--------+---------+------------+------+-----------------+------

| countries | InnoDB | 10 | Compact | 11 | utf8_general_ci |......

+-----------+--------+---------+------------+------+-----------------+------

三、查看MySQL数据列（column）的MySQL字符集。

mysql> show full columns from countries;

+----------------------+-------------+-----------------+--------

| Field | Type | Collation | .......

+----------------------+-------------+-----------------+--------

| countries_id | int(11) | NULL | .......

| countries_name | varchar(64) | utf8_general_ci | .......

| countries_iso_code_2 | char(2) | utf8_general_ci | .......

| countries_iso_code_3 | char(3) | utf8_general_ci | .......

| address_format_id | int(11) | NULL | .......

+----------------------+-------------+-----------------+---

修改mysql默认字符集的方法

(1) 最简单的修改方法，就是修改mysql的my.ini文件中的字符集键值，

如 default-character-set = utf8

character_set_server = utf8

修改完后，重启mysql的服务，service mysql restart

使用 mysql> SHOW VARIABLES LIKE 'character%';查看，发现数据库编码均已改成utf8

+--------------------------+---------------------------------+

| Variable_name | Value |

+--------------------------+---------------------------------+

| character_set_client | utf8 |

| character_set_connection | utf8 |

| character_set_database | utf8 |

| character_set_filesystem | binary |

| character_set_results | utf8 |

| character_set_server | utf8 |

| character_set_system | utf8 |

| character_sets_dir | D:"mysql-5.0.37"share"charsets" |

+--------------------------+---------------------------------+

(2) 还有一种修改mysql默认字符集的方法，就是使用mysql的命令

mysql> SET character_set_client = utf8 ;

mysql> SET character_set_connection = utf8 ;

mysql> SET character_set_database = utf8 ;

mysql> SET character_set_results = utf8 ;

mysql> SET character_set_server = utf8 ;

mysql> SET collation_connection = utf8 ;

mysql> SET collation_database = utf8 ;

mysql> SET collation_server = utf8 ;

一般就算设置了表的mysql默认字符集为utf8并且通过UTF-8编码发送查询，你会发现存入数据库的仍然是乱码。问题就出在这个connection连接层上。解决方法是在发送查询前执行一下下面这句：

SET NAMES 'utf8';

它相当于下面的三句指令：

SET character_set_client = utf8;

SET character_set_results = utf8;

SET character_set_connection = utf8;

mysql脚本优化工具tuning-primer.sh使用

今天推荐给大家一个不错的脚本tuning-primer.sh，可以帮助我们去查看一下msyql运行情况，产生报告和给出一些建议，我们可以根据这些建议，结合mysql服务器的实际情况，对mysql进行优化。

一，下载以及使用

1，wget http://www.day32.com/MySQL/tuning-primer.sh

2，将tuning-primer.sh拷贝到my.cnf的同级目录

3，运行 sh tuning-primer.sh

[root@BlackGhost mysql]# sh tuning-primer.sh

Using login values from ~/.my.cnf
- INITIAL LOGIN ATTEMPT FAILED -
Testing for stored webmin passwords:
None Found
Could not auto detect login info!
Found Sockets: /tmp/mysql.sock                 //找到一个mysql.sock
Using: /tmp/mysql.sock                                 //使用这个.sock文件
Would you like to provide a different socket?: [y/N] n       //是否使用其他的socket
Do you have your login handy ? [y/N] : y        //是否手动输入用户名和密码，当然你可以在my.cnf里面设置
User: root
Password:

Would you like me to create a ~/.my.cnf file for you? [y/N] : n //是不是要帮你在创建一个my.cnf

你也可以把用户名和密码放到my.cnf里面，如下

[client]
user = 用户名
password = 密码
socket = /tmp/mysql.sock

二，运行结果，以及部分分析和建议

我是以本机的mysql为例来进行测试的。
-- MYSQL PERFORMANCE TUNING PRIMER --
- By: Matthew Montgomery -

MySQL Version 5.1.26-rc-log i686

Uptime = 0 days 0 hrs 0 min 28 sec
Avg. qps = 0
Total Questions = 15
Threads Connected = 1

Warning: Server has not been running for at least 48hrs.
It may not be safe to use these recommendations

告诉我服务运行还不超过48个小时，这样不安全

To find out more information on how each of these
runtime variables effects performance visit:

http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html

Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service

SLOW QUERIES （慢查询）
The slow query log is enabled.
Current long_query_time = 2.000000 sec.
You have 0 out of 36 that take longer than 2.000000 sec. to complete
Your long_query_time seems to be fine

BINARY UPDATE LOG （增加备份时的log文件）
The binary update log is enabled
The expire_logs_days is not set.
The mysqld will retain the entire binary log until RESET MASTER or PURGE MASTER LOGS commands are run manually
Setting expire_logs_days will allow you to remove old binary logs automatically
See http://dev.mysql.com/doc/refman/5.1/en/purge-master-logs.html
Binlog sync is not enabled, you could loose binlog records during a server crash

告诉我binary log没有设置生存时间，会一直保存下来，只到主服务器重起，并且binlog的同步并没有激活，如果服务器崩溃binlog数据将丢失

WORKER THREADS （工作线程）
Current thread_cache_size = 0
Current threads_cached = 0
Current threads_per_sec = 1
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS （最大连接数）
Current max_connections = 500
Current threads_connected = 1
Historic max_used_connections = 1
The number of used connections is 0% of the configured maximum.
You are using less than 10% of your configured max_connections.
Lowering max_connections could help to avoid an over-allocation of memory
See "MEMORY USAGE" section to make sure you are not over-allocating

告诉我，目前已连接的最大连接和设置的最大连接的比率差不多是0％，建议我把最大连接设置小一点，可以节省一点内存

No InnoDB Support Enabled!

MEMORY USAGE （内存使用）
Max Memory Ever Allocated : 17 M
Configured Max Per-thread Buffers : 796 M
Configured Max Global Buffers : 16 M
Configured Max Memory Limit : 812 M
Physical Memory : 1003 M
Max memory limit seem to be within acceptable norms

KEY BUFFER （KEY缓存）
No key reads?!
Seriously look into using some indexes
Current MyISAM index space = 16 M
Current key_buffer_size = 16 M
Key cache miss rate is 1 : 0
Key buffer free ratio = 88 %
Your key_buffer_size seems to be fine

QUERY CACHE   （查询缓存）
Query cache is supported but not enabled       查询缓存是支持的，但是没有激活
Perhaps you should set the query_cache_size   建议我设置一下query_cache_size的大小

SORT OPERATIONS （排序操作）
Current sort_buffer_size = 512 K
Current read_rnd_buffer_size = 512 K
No sort operations have been performed
Sort buffer seems to be fine

JOINS （连接）
Current join_buffer_size = 132.00 K
You have had 0 queries where a join could not use an index properly
Your joins seem to be using indexes properly

OPEN FILES LIMIT （最大打开文件数）
Current open_files_limit = 2500 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.

告诉我，如果大量使用myisam的话，把open_files_limit的大小设置成table_cache大小的2倍－3倍

Your open_files_limit value seems to be fine

TABLE CACHE （表缓存）
Current table_open_cache = 64 tables
Current table_definition_cache = 256 tables
You have a total of 506 tables
You have 64 open tables.
Current table_cache hit rate is 12%
, while 100% of your table cache is in use
You should probably increase your table_cache
You should probably increase your table_definition_cache value.

告诉我打开了多少文件，table_cache缓存的比率是多少，并建议我增加table_cache和table_definition_cache的值
TEMP TABLES （临时表）
Current max_heap_table_size = 16 M
Current tmp_table_size = 16 M
Of 141 temp tables, 10% were created on disk
Created disk tmp tables ratio seems fine

TABLE SCANS （表扫描）
Current read_buffer_size = 256 K
Current table scan ratio = 12 : 1
read_buffer_size seems to be fine

TABLE LOCKING （表锁定）
Current Lock Wait ratio = 0 : 225
Your table locking seems to be fine

从上面的分析报告，以及建议可以看出，本机的mysql真的要好好调优一下。

http://www.jincon.com/archives/227/

mysql表连接，子查询以及if判断

创建表：
CREATE TABLE emp (
ename varchar(10) DEFAULT NULL,
hiredate date DEFAULT NULL,
sal decimal(10,2) DEFAULT NULL,
deptno int(2) DEFAULT NULL,
age int(3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE dept (
deptno int(2) DEFAULT NULL,
deptname varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

表连接：当需要同时显示多个表中的字段时，使用表连接.

内连接：仅选出两张表中互相匹配的记录外连接：既选出匹配的记录，也选出不匹配的记录左连接：包含所有的左表中的记录，即使右表中没有和它相匹配的记录例如，用户表中有部门id，使用左连接查询右表，得到相应的部门名称，对于用户表中的id在右表中找不到对应的记录时结果为NULL select ename,hiredate,sal,emp.deptno,age,deptname from emp left join dept on emp.deptno=dept.deptno; +-------+------------+------+--------+------+----------+ | ename | hiredate | sal | deptno | age | deptname | +-------+------------+------+--------+------+----------+ | zzxl | 2000-01-01 | 2000 | 1 | NULL | dept1 | | lisa | 2003-01-01 | 3000 | 2 | 20 | dept2 | | dony | NULL | 2000 | 5 | NULL | dept5 | +-------+------------+------+--------+------+----------+

子查询：进行查询时，当需要的条件是另外一个查询的结果是，使用子查询
例如：查询所有部门中的用户

select * from emp where deptno in (select deptno from dept); +-------+------------+------+--------+------+| ename | hiredate | sal | deptno | age | +-------+------------+------+--------+------+| zzxl | 2000-01-01 | 2000 | 1 | NULL | | lisa | 2003-01-01 | 3000 | 2 | 20 || dony | NULL | 2000 | 5 | NULL | +-------+------------+------+--------+------+

如果子查询结果唯一，可以用=代替in

select * from emp where deptno = (select deptno from dept limit 1); +-------+----------+------+--------+------+| ename | hiredate | sal | deptno | age | +-------+----------+------+--------+------+| dony | NULL | 2000 | 5 | NULL | +-------+----------+------+--------+------+

注意：表连接用于优化子查询

记录联合：将多次查询结果合并显示

select deptno from emp union select deptno from dept;//去除重复数据

select deptno from emp union all select deptno from dept;

create table salary(userid int,salary decimal(10,2));
insert into salary values(1,1000),(2,2000),(3,3000),(4,4000),(1,null);

if判断

select *,if(salary>2000,'high','low') as statu from salary; +--------+--------+-------+| userid | salary | statu | +--------+--------+-------+| 1 | 1000 | low | | 2 | 2000 | low | | 3 | 3000 | high | | 4 | 4000 | high || 1 | NULL | low | +--------+--------+-------+

if null判断

select ifnull(salary,0) from salary; +------------------+| ifnull(salary,0) | +------------------+| 1000 | | 2000 | | 3000 | | 4000 || 0 | +------------------+

case when

select case when salary<=2000 then 'low' else 'high' end from salary;select case salary when 1000 then 'low' when 2000 then 'mid' else  'high' end from salary;

mysql 常用语句整理

++启动/关闭mysql
$ path/mysqld_safe -user=mysql &
$ /mysqladmin -p shutdown

++修改root口令
$ mysqladmin -u root -p password ‘新密码’

++查看服务器状态
$ path/mysqladmin version -p

++连接远端mysql服务器
$ path/mysql -u 用户名 -p #连接本机
$ path/mysql -h 远程主机IP -u 用户名 -p#连接远程MYSQL服务器

++创建/删除数据库或表
$ mysqladmin -u root -p create xxx
mysql> create database 数据库名;
mysql> create TABLE items (
id INT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
symbol CHAR(4) NOT NULL,
username CHAR(8),
INDEX sym (symbol),INDEX …..
UNIQUE(username)
) type=innodb;
mysql> drop database [if exists] 数据库名
mysql> create table 表名;
mysql> drop table 表名;

++查看数据库和查看数据库下的表
mysql> show databases;
mysql> show tables;
mysql> show table status;
mysql> desc 表名; #查看具体表结构信息
mysql> SHOW CREATE DATABASE db_name #显示创建db_name库的语句
mysql> SHOW CREATE TABLE tbl_name #显示创建tbl_name表的语句

++创建用户
mysql> grant select,insert,update,delete,alter on mydb.* to test2@localhost identified by “abc”;
mysql> grant all privileges on *.* to test1@”%” identified by “abc”;
mysql> flush privileges;

++用户管理
mysql> update user set password=password (’11111′) where user=’test1′; #修改test1密码为111111
mysql> DELETE FROM user WHERE User=”testuser” and Host=”localhost”; #删除用户帐号
mysql> SHOW GRANTS FOR user1; #显示创建user1用户的grant语句

++mysql数据库的备份和恢复
$ mysqldump -uuser -ppassword -B DB_name [--tables table1 --tables table2] > exportfile.sql
$ mysql -uroot -p xxx < aaa.sql #导入表
$ mysqldump -u 用户名 -p 数据库名表名> 导出的文件名 ##导出单独的表

++导出一个数据库结构
$ mysqldump -u wcnc -p -d ?add-drop-table smgp_apps_wcnc >wcnc_db.sql
-d 没有数据 ?add-drop-table 在每个create语句之前增加一个drop table

++忘记mysql密码
先停止所有mysql服务进程
$ mysqld_safe ?skip-grant-tables & mysql
mysql> use mysql;
mysql> update user set password=password(’111111′) where user=’root’;
mysql> flush privileges;
然后重启mysql并以新密码登入即可

++当前使用的数据库
mysql> select database();

===数据库日常操作维护====
++创建表
mysql> create table table_name
(column_name datatype {identity |null|not null},f_time TIMESTAMP(8),…)ENGINE=MyISAM AUTO_INCREMENT=3811 DEFAULT CHARSET=utf8;
例: CREATE TABLE guest (name varchar(10),*** varchar(2),age int(3),career varchar(10));
# desc guest可查看表结构信息
# TIMESTAMP(8) YYYYMMDD 其中(2/4/6/8/10/12/14)对应不同的时间格式
mysql> SHOW CREATE TABLE tbl_name #显示创建tbl_name表的语句

++创建索引
可以在建表的时候加入index indexname (列名)创建索引,
也可以手工用命令生成 create index index_name on table_name (col_name[(length)],… )
mysql> CREATE INDEX number ON guest (number(10));
mysql> SHOW INDEX FROM tbl_name [FROM db_name] #显示现有索引
mysql> repair TABLE date QUICK; #索引列相关变量变化后自动重建索引

++查询及常用函数
mysql> select t1.name, t2.salary from employee AS t1, info AS t2 where t1.name = t2.name;
mysql> select college, region, seed from tournament ORDER BY region, seed;
mysql> select col_name from tbl_name WHERE col_name > 0;
mysql> select DISTINCT …… [DISTINCT关键字可以除去重复的记录]
mysql> select DATE_FORMAT(NOW(),’%m/%d/%Y’) as DATE, DATE_FORMAT(NOW(),’%H:%m:%s’) AS TIME;
mysql> select CURDATE(),CURTIME(),YEAR(NOW()),MONTH(NOW()),DAYOFMONTH(NOW()),HOUR(NOW()),MINUTE(NOW());
mysql> select UNIX_TIMESTAMP(),UNIX_TIMESTAMP(20080808),FROM_UNIXTIME(UNIX_TIMESTAMP()); mysql> select PASSWORD(”secret”),MD5(”secret”); #加密密码用
mysql> select count(*) from tab_name order by id [DESC|ASC]; #DESC倒序/ASC正序

* 函数count,AVG,SUM,MIN,MAX,LENGTH字符长度,LTRIM去除开头的空头,RTRIM去尾部空格,TRIM(str)去除首部尾部空格,LETF/RIGHT(str,x)返回字符串str的左边/右边x个字符,SUBSTRING(str,x,y)返回str中的x位置起至位置y的字符mysql> select BINARY ‘ross’ IN (’Chandler’,’Joey’, ‘Ross’); #BINARY严格检查大小写

* 比较运算符IN,BETWEEN,IS NULL,IS NOT NULL,LIKE,REGEXP/RLIKE
mysql> select count(*),AVG(number_xx),Host,user from mysql.user GROUP by user [DESC|ASC] HAVING user=root; #分组并统计次数/平均值

++UNIX_TIMESTAMP(date)
返回一个Unix时间戳记(从’1970-01-01 00:00:00′GMT开始的秒数)
mysql> select UNIX_TIMESTAMP();
mysql> select UNIX_TIMESTAMP(’1997-10-04 22:23:00′);
mysql> select FROM_UNIXTIME(875996580); #根据时间戳记算出日期

++控制条件函数
mysql> select if(1<10,2,3), IF(55>100,’true’,’false’);
#IF()函数有三个参数,第一个是被判断的表达式,如果表达式为真,返回第二个参数,如果为假,返回第三个参数.
mysql> select CASE WHEN (2+2)=4 THEN “OK” WHEN (2+2)<>4 THEN ‘NOT OK’ END AS status;

++系统信息函数
mysql> select DATABASE(),VERSION(),USER();
mysql> select BENCHMARK(9999999,LOG(RAND()*PI())) AS PERFORMANACE; #一个测试mysql运算性能工具

++将wp_posts表中post_content字段中文字”old”替换为”new”
mysql> update wp_posts set post_content=replace(post_content,’old’,’new’)

++改变表结构
mysql> alter table table_name alter_spec [, alter_spec ...]
例：alter table dbname add column userid int(11) not null primary key auto_increment;
这样，就在表dbname中添加了一个字段userid，类型为int(11)。

++调整列顺序
mysql> alter table tablename CHANGE id id int(11) first;

++修改表中数据
insert [into] table_name [(column(s))] values (expression(s))
例：mysql>insert into mydatabase values(’php’,’mysql’,’asp’,’sqlserver’,’jsp’,’oracle’);
mysql> create table user select host,user from mysql.user where 1=0;
mysql> insert into user(host,user) select host,user from mysql.user;

++更改表名
命令：rename table 原表名 to 新表名;

++表的数据更新
mysql> update table01 set field04=19991022[, field05=062218] where field01=1;

++删除数据
mysql> delete from table01 where field01=3;
#如果想要清空表的所有纪录,建议用truncate table tablename而不是delete from tablename.

++SHELL提示符下运行SQL命令
$ mysql -e “show slave status\G ”

++坏库扫描修复
cd /var/lib/mysql/xxx && myisamchk playlist_block

++insert into a (x) values (’11a’)
出现: ata truncated for column ‘x’ at row 1
解决办法:
在my.ini里找到
sql-mode=”STRICT_TRANS_TABLES,NO_AUTO_Create_USER,NO_ENGINE_SUBSTITUTION”
把其中的STRICT_TRANS_TABLES,去掉,然后重启mysql就ok了

++复制表
mysql> create table target_table like source_table

++innodb支持事务
新表:create TABLE table-name (field-definitions) TYPE=INNODB;
旧表: alter TABLE table-name TYPE=INNODB;
mysql> start transaction #标记一个事务的开始
mysql> insert into….. #数据变更
mysql> ROLLBACK或commit #回滚或提交
mysql> SET AUTOCOMMIT=1; #设置自动提交
mysql> select @@autocommit; #查看当前是否自动提交

++表锁定相关
mysql> LOCK TABLE users READ; # 对user表进行只读锁定
mysql> LOCK TABLES user READ, pfolios WRITE #多表锁控制
mysql> UNLOCK TABLES; #不需要指定锁定表名字, MySQL会自动解除所有表锁定

=====一些mysql优化与管理======
++管理用命令
mysql> show variables #查看所有变量值
? max_connections 数据库允许的最大可连接数,
#需要加大max_connections可以在my.cnf中加入set-variable = max_connections=32000,可以对与下面的threads_connected值决定是否需要增大.

show status [like ....];
? threads_connected 数据库当前的连接线程数
#FLUSH STATUS 可以重置一些计数器

show processlist;
kill id;

++my.cnf配置
?Enable Slow Query Log
long_query_time=1
log-slow-queries=/var/log/mysql/log-slow-queries.log
log-queries-not-using-indexes

# mysqldumpslow -s c -t 20 host-slow.log #访问次数最多的20个sql语句
# mysqldumpslow -s r -t 20 host-slow.log #返回记录集最多的20个sql

?others
max_connections=500 #用过的最大连接数SHOW Status like ‘max_used_connection’;
wait_timeout=10 #终止所有空闲时间超过 10 秒的连接
table_cache=64 #任何时间打开表的总数
ax_binlog_size=512M #循环之前二进制日志的最大规模
max_connect_errors = 100

query_cache_size = 256M #查询缓存
#可用 SHOW STATUS LIKE ‘qcache%’;查看命中率
#FLUSH STATUS重置计数器, FLUSH QUERY CACHE清缓存

thread_cache = 40
#线程使用,SHOW STATUS LIKE ‘Threads_created %’; 值快速增加的话考虑加大

key_buffer = 16M
#show status like ‘%key_read%’; Key_reads 代表命中磁盘的关键字请求个数
#A: 到底 Key Buffer 要设定多少才够呢? Q: MySQL 只会 Cache 索引(*.MYI)，因此参考所有 MYI文件的总大小

sort_buffer_size = 4M #查询排序时所能使用的缓冲区大小,每连接独享4M
#show status like ‘%sort%’; 如sort_merge_passes很大，就表示加大

sort_buffer_sizesort_buffer_size = 6M #查询排序时所能使用的缓冲区大小,这是每连接独享值6M
read_buffer_size = 4M　#读查询操作所能使用的缓冲区大小
join_buffer_size = 8M #联合查询操作所能使用的缓冲区大小
skip-locking #取消文件系统的外部锁
skip-name-resolve
thread_concurrency = 8　　#最大并发线程数,cpu数量*2
long_query_time = 10 #Slow_queries记数器的查询时间阀值