mysql命令 积累笔记

这部分会不断总结，添加内容。

1. 修改表明：

ALTER  TABLE table_name RENAME TO new_table_name

例如 ALTER  TABLE admin_user RENAME TO a_user

1.1.显示表结构

describe table;

1.2显示参数

show variables;

1.3 查看当前用户的连接数（默认显示100条，如果现实所有，用show full processlist;）

show processlist;

1.4 .创建用户

grant all on *.* to 'root'@'%' identified by '123';

如果用户已经存在，可以起到对该用户访问授权的作用。


2. 修改数据库

  2.1修改字段类型和长度
alter table 表名 modify column 字段名 类型;
例如:数据库中user表 student字段是varchar(30)
alter table user modify column student varchar(50) ;


2.2 增加一个新列549830479
alter table adcontent add ppvod int(0) NOT NULL default 0;

2.3 删除列549830479
   alter table t2 drop column c;
例如 alter table adcontent drop column ppvod;

2.3 重命名列549830479
   alter table t1 change a b integer;
例如   alter table adcontent change  pps ppgo int;

2.4 重命名表549830479
   alter table gyes rename othertable;


2.5 改变列的类型549830479
   alter table t1 change b b bigint not null;
alter table infos change list list tinyint not null default '0';


2.6 删数据库
   DROP DATABASE databasename;

2.7修改密码
set password=password('new password');

2.7.1 修改指定用户的密码
update mysql.user set password=password('new password') where User='name' and Host='host';
2.7.2 删除用户
delete from user where User='name' and Host='%';

修改root密码
mysqladmin -u root -p password mypasswd




方法1
使用phpmyadmin，这是最简单的了，修改mysql库的user表，
不过别忘了使用PASSWORD函数。

方法2
使用mysqladmin，这是前面声明的一个特例。
mysqladmin -u root -p password mypasswd
输入这个命令后，需要输入root的原密码，然后root的密码将改为mypasswd。
把命令里的root改为你的用户名，你就可以改你自己的密码了。
当然如果你的mysqladmin连接不上mysql server，或者你没有办法执行mysqladmin，
那么这种方法就是无效的。 而且mysqladmin无法把密码清空。
下面的方法都在mysql提示符下使用，且必须有mysql的root权限：

方法3
mysql> INSERT INTO mysql.user (Host,User,Password)
VALUES(\%,jeffrey,PASSWORD( iscuit));
mysql> FLUSH PRIVILEGES

确切地说这是在增加一个用户，用户名为jeffrey，密码为biscuit。在《mysql中文参考手册》里有这个例子，所以我也就写出来了。 注意要使用PASSWORD函数，然后还要使用FLUSH PRIVILEGES。

方法4
和方法三一样，只是使用了REPLACE语句
mysql> REPLACE INTO mysql.user (Host,User,Password)
VALUES(\%,jeffrey,PASSWORD( iscuit));
mysql> FLUSH PRIVILEGES

方法5
使用SET PASSWORD语句，
mysql> SET PASSWORD FOR jeffrey@"%" = PASSWORD( iscuit);
你也必须使用PASSWORD()函数，
但是不需要使用FLUSH PRIVILEGES。

方法6
使用GRANT ... IDENTIFIED BY语句
mysql> GRANT USAGE ON *.* TO jeffrey@"%" IDENTIFIED BY  iscuit
这里PASSWORD()函数是不必要的，也不需要使用FLUSH PRIVILEGES。
注意： PASSWORD() [不是]以在Unix口令加密的同样方法施行口令加密。


2.8 修改存储引擎

    要把一个表从一个类型转到另一个类型，可使用ALTER TABLE语句：
    ALTER TABLE t ENGINE = MYISAM;
    ALTER TABLE t TYPE = BDB;


3. 数据导入导出
首先：若配置的环境变量可以直接执行下面命令。若没环境变量，就需要到bin目录下执行
    3.1 导出整个数据库
　 　mysqldump -u 用户名 -p 数据库名 > 导出的文件名
　 　mysqldump -u wcnc -p smgp_apps_wcnc > wcnc.sql
　　3.2 导出一个表
　　 mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名
　　 mysqldump -u wcnc -p smgp_apps_wcnc users> wcnc_users.sql
　　3.3导出一个数据库结构
　　 mysqldump -u wcnc -p -d --add-drop-table smgp_apps_wcnc >d:wcnc_db.sql
　　 -d 没有数据 --add-drop-table 在每个create语句之前增加一个drop table
　　
备忘: mysqldump在linux下可直接使用 在windows下有时需要进入mysql/bin中使用因为有时用的是类似appserv的套装软件 这样的话命令就不能直接使用 因为这些命令没在环境变量的目录里 而且用mysqldump导出的备份必须先建立数据库才能导入

    3.4 查看建表sql

    show create table tablename;
    show create table user;

    3.5 导入数据库
　　 常用source 命令
　　 进入mysql数据库控制台，
　　 如mysql -u root -p
　　 mysql>use 数据库
　　 然后使用source命令，后面参数为脚本文件(如这里用到的.sql)
　　 mysql>source d:wcnc_db.sql

存疑: phpmyadmin导入有大小限制 不知道source导入时有没限制 而且导出时是不可以限制文件大小 并且分数个文件导出



3.5.2 DROP TABLE 语句

    删除表比创建表要容易得多，因为不需要指定有关其内容的任何东西；只需指定其名称即可，如：

    DROP TABLE tb1_name

    MySQL对DROP TABLE 语句在某些有用的方面做了扩充。首先，可在同一语句中指定几个表对它们进行删除，如：

    DROP TABLE tb1_name1,tb1_name2,......

    其次，如果不能肯定一个表是否存在，但希望如果它存在就删除它。那么可在此语句中增加IF EXISTS。这样，如果DROP TABLE 语句中给出的表不存在，MySQL不会发出错误信息。如：

    DROP TABLE IF EXISTS tb1_name

    IF EXISTS 在mysql所用的脚本中很有用，因为缺省情况下， mysql将在出错时退出。例如，有一个安装脚本能够创建表，这些表将在其他脚本中继续使用。在此情形下，希望保证此创建表的脚本在开始运行时无后顾之忧。如果在该脚本开始处使用普通的DROP TABLE，那么它在第一次运行时将会失败，因为这些表从未创建过。如果使用IF EXISTS，就不会产生问题了。当表已经存在时，将它们删除；如果不存在，脚本继续运行。

    3.5.3 创建和删除索引

    索引是加速表内容访问的主要手段，特别对涉及多个表的连接的查询更是如此。这是第4章“查询优化”中的一个重要内容，第4章讨论了为什么需要索引，索引如何工作以及怎样利用它们来优化查询。本节中，我们将介绍索引的特点，以及创建和删除索引的语法。



//主键549830479

   alter table tabelname add new_field_id int(5) unsigned default 0 not null auto_increment ,add primary key (new_field_id);


加索引549830479

   mysql> alter table tablename change depno depno int(5) not null;
mysql> alter table tablename add index 索引名 (字段名1[，字段名2 …]);
mysql> alter table tablename add index emp_name (name);

加主关键字的索引549830479

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

加唯一限制条件的索引549830479

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

删除某个索引549830479

   mysql>alter table tablename drop index emp_name;

修改表：549830479

增加字段：549830479

   mysql> ALTER TABLE table_name ADD field_name field_type;

修改原字段名称及类型：549830479

   mysql> ALTER TABLE table_name CHANGE old_field_name new_field_name field_type;

删除字段：549830479

   mysql> ALTER TABLE table_name DROP field_name;

原有一unique索引AK_PAS_Name(PAC_Name)在表tb_webparamcounter中，
执行以下sql修改索引
alter table tb_webparamcounter drop index AK_PAS_Name;
alter table tb_webparamcounter add UNIQUE AK_PAS_Name(PC_ID,PAC_Name);

若发现索引的逻辑不对，还需要再加一个字段进去，执行
alter table tb_webparamcounter drop index AK_PAS_Name;
alter table tb_webparamcounter add UNIQUE AK_PAS_Name(PC_ID,PAC_Name,PAC_Value);
注意：这时的PC_ID,PAC_Name,PAC_Value三个字段不是FOREIGN KEY
否则必需先drop FOREIGN KEY，再重做上一步才行


5 修改Mysql的字符集
在linux下修改3个my.cnf的1个/etc/mysql/my.cnf文件

　　找到客户端配置[client] 在下面添加

　　default-character-set=utf8 默认字符集为utf8

　　在找到[mysqld] 添加

　　default-character-set=utf8 默认字符集为utf8

　　init_connect='SET NAMES utf8' (设定连接mysql数据库时使用utf8编码，以让mysql数据库为utf8运行)

　　修改好后，重新启动mysql 即可，查询一下show variables like 'character%';

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

　　| 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 | /usr/share/mysql/charsets/ |

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

　　此方法用于标准mysql版本同样有效，对于/etc/my.cnf文件，需要从mysql/support-files的文件夹cp my-large.cnf一份到/etc/my.cnf

6.5 远程访问数据库

第一步: 激活网络设置（这步没有经过验证。）
你需要编辑mysql配置文件my.cnf.

通常状况，my.cnf放置于在以下目录：
/etc/mysql/my.cnf (Debian linux/Ubuntu)
/etc/my.cnf （Red Hat Linux/Fedora Linux)
/var/db/mysql/my.cnf (FreeBSD)

# vi /etc/my.cnf(Ubuntu需要加sudo)
然后用vi编辑my.cnf，修改内容从以下行：

需要修改:
1. 确保skip-networking被删除或者屏蔽，否则不支持TCP/IP 访问
2. 屏蔽掉bind-address = 127.0.0.1
3. 保存并关闭配置文件
4. 重启mysql服务器:sudo /etc/init.d/mysql restart

第二步， 给远程ip授访问权限
1. 以root身份登陆mysql # mysql -u root -p
2. 授权给新数据库


mysql> grant all on simulatesystem.*（库名.表名  *标识全部） to root@192.168.0.70（授权的用户@授权人）  identified by "123456"（密码）;

>mysql -uroot -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.22-rc-community-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> grant all privileges on *.* to 'root'@'%' identified by 'youpasswd';
Query OK, 0 rows affected (0.05 sec)

mysql> flush privileges;   //在分配完权限后一定要flush下，不然不生效
Query OK, 0 rows affected (0.06 sec)

mysql> exit
Bye

第三步 测试
mysql -h192.168.*.* -uroot -p

注解；
-u root: root为mysql的用户名
-h IP or 域名: 192.168.*.* is MySQL server IP address or hostname (FQDN)
-p : 密码提示
你也可以使用telnet去连接远程的3306端口 $ telnet 65.55.55.2 3306

注：
这篇文章解决了我在suse环境中的mysql远程访问问题。但是实际操作中，my.cnf似乎并不是特别重要。在我的操作中，my.cnf创建和修改没有执行（忽略第一步）。最重要的步骤应该是第四步
GRANT ALL ON b2c.* TO root@'5.179.201.86' IDENTIFIED BY '';
其中b2c为所要访问的远程数据库名，5.179.201.86为mysql客户端主机的ip地址，也就是你的机器ip地址。
有个小细节需要注意。如果你的服务器装了防火墙的话，需要打开3306的访问，否则也会连接不上。



6.6.1 查看mysql系统配置（查看字符集部分）
show variables like 'character_set_%';