MySQL使用小结(Linux & Win)

zestsh

这是从开始使用MySQL，到现在所积累的一些杂乱无章的笔记，因为不是一口气写下来，而是以追加的形式构成，大多地方还是从各种地方粘过来(很抱歉不能列出出处，地方太多，当时没记)，难免有重复和累赘，所以结构逻辑不太分明，但又不想花时间改了，直接从电脑搬了出来，大家可以拿起砖头。。。不过有的地方还是挺关键的，所以分享出来供大家学习参考，更详细的资料可去：(MySQL doc:http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html)
史前准备：
Linux(Debian & Ubuntu):
Install:
    sudo apt-get install mysql-server
    [port#3306]
    sudo apt-get install mysql-client
As default, mysql will automatically run, check:
    sudo netstat -tap | grep mysql
   
    sudo mysqladmin -u root password newpassword
    mysql -uroot -p
Install gui tools:
    sudo apt-get install mysql-admin
    sudo apt-get install mysql-query-browser
Windows(曾经学Java连DB时使用作的总结):
虽然官方提供了非常好的安装文件，但是有的时候不想每次再重装系统之后都要安装一遍MySQL，需要使用zip包版本的MySQL。在配置的时候有以下几个问题需要解决：
1、下载mysql
       http://dev.mysql.com/downloads/
2、将mysql解压到待安装目录，使用%MYSQL_HOME%表示。
3、编辑my.ini文件(该文件放到C:\windows目录下)，内容如下：
        [mysqld]
        #default-character-set=gbk
        basedir=%MYSQL_HOME%
        datadir=%MYSQL_HOME%/data

        [WinMySQLadmin]
        Server=%MYSQL_HOME%/bin/mysqld-nt.exe
        user=root
        password=
4、打开windows的cmd命令窗口，进入到%MYSQL_HOME%\bin。
5、执行命令： mysqld-nt.exe --install  （安装到windows的服务）
6、执行命令： net start mysql
7、初始帐号:用户名root，密码为空，所以说默认情况下的MySQL5是不安全的。进入之后立即修改root帐号密码，命令如下：
        set password for root@localhost =password('yourPassword');  
        flush privileges;  
一般情况下，这样操作后，mysql安装完成。

    注：
    1、如果以前系统存在mysql。则进入以前系统存在的mysql目录,执行 mysqld-nt.exe --remove(先从系统中移
          除mysql服务)在开始执行上面第4步。
    2、如果出现1067错误，一般是my.ini文件配置问题。更改my.ini文件使之符合本地情况.
     
     
    尽管可以用现在的安装文件，但有的时候不想每次再重装系统之后都要安装一遍MySQL，需要使用zip包版本的MySQL。在配置的时候有以下几个问题需要了解的，总结一下：
     
    1. 设置MySQL为Windows服务
     
    在Windows下用mysqld-nt.exe可以注册为服务，即用mysqld-nt –install mysql
    这会设置为mysqld-nt.exe为mysql服务，相关信息在命令行模式下查询mysqld-nt –verbose –help
     
    2. 启动服务时出现“错误1067”的解决方法
     
    参考手册中明确说明了这个问题：
     
    These messages often occur when the MySQL base or data directories are installed in different locations than the default locations (C:\Program Files\MySQL\MySQL Server 5.0 and C:\Program Files\MySQL\MySQL Server 5.0\data, respectively).
     
    上面的意思是在没有将MySQL安装到默认的目录下面的情况下启动Windows服务的时候就会出现上述提示，解决的办法就是指定MySQL跟目录以及数据库目录。在my.ini文件最后增加以下内容，然后将my.ini拷贝到windows目录下。
     
    [mysqld]  
    # set basedir to your installation path  
    basedir=E:/mysql  
    # set datadir to the location of your data directory  
    datadir=D:/MySQLdata

一) 连接MYSQL：  
   格式： mysql -h主机地址 -u用户名 －p用户密码  
   查看状态信息：status;

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

2、例2：连接到远程主机上的MYSQL  
   假设远程主机的IP为：10.0.0.1，用户名为root,密码为123。则键入以下命令：
   mysql -h10.0.0.1 -uroot -p123  
  （注：u与root可以不用加空格，其它也一样）  

3、退出MYSQL命令  
   exit/quit （回车）  



(二) 用户管理：  
   格式：mysqladmin -u用户名 -p旧密码 password 新密码  

1、例1：给root加个密码123。首先在DOS下进入目录C:\mysql\bin，然后键入以下命令：  
   mysqladmin -uroot -password 123  
   注：因为开始时root没有密码，所以-p旧密码一项就可以省略了。  

2、例2：再将root的密码改为456  
   mysqladmin -uroot -pab12 password 456
   
3、权限管理
   格式：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 "";  
2.1 //创建用户
mysql> insert into mysql.user(Host,User,Password) values('localhost','phplamp',password('1234'));
//刷新系统权限表
mysql>flush privileges;
这样就创建了一个名为：phplamp  密码为：1234  的用户。
2.2 //为用户授权
//首先为用户创建一个数据库(phplampDB)
mysql>create database phplampDB;
//授权phplamp用户拥有phplamp数据库的所有权限
@>grant all privileges on phplampDB.* to phplamp@localhost identified by '1234';
//刷新系统权限表
mysql>flush privileges;
//如果想指定部分权限给一用户，可以这样来写:
mysql>grant select,update on phplampDB.* to phplamp@localhost identified by '1234';
//刷新系统权限表。
mysql>flush privileges;
mysql> grant 权限1,权限2,…权限n on 数据库名称.表名称 to 用户名@用户地址 identified by ‘连接口令’;
权限1,权限2,…权限n代表select,insert,update,delete,create,drop,index,alter,grant,references,reload,shutdown,process,file等14个权限。
2.3 //删除用户
mysql>DELETE FROM user WHERE User="phplamp" and Host="localhost";
mysql>flush privileges;
//删除用户的数据库
mysql>drop database phplampDB;
2.4 //修改指定用户密码：
mysql>update mysql.user set password=password('新密码') where User="phplamp" and Host="localhost";
mysql>flush privileges;
2.5 //查看所有用户：
mysql> select user,host,password from mysql.user;

(三) 如何使用：
3.1、关于远程访问：
在安装MySql后只有一个超级管理权限的用户ROOT，而且ROOT限制只能在数据库本机上使用，如果我们要远程管理MySql咋办呢？那么事实上我们需要添加一个具有超级管理权限并且可能远程访问的超级用户，而在MySql中有两种方法可以实现这个目的:
    1.修改MySQL配置文件（/etc/mysql/my.cnf），注释以下行：
    #bind-address = 127.0.0.1
    2.重启mysql数据库，对于Ubuntu系统，执行如下命令
    sudo /etc/init.d/mysql restart
    3.以root用户登录mysql后，执行如下命令：
    grant all privileges on *.* to root@’允许登录的远程机器ip’
    identified by ‘密码’
    其中，*.*表示该主机中所有数据库的所有表。（数据库.表）
    “给某IP地址上登录的root用户授予访问所有数据库中的所有表的所有权限”　

     你也可以通过发出GRANT语句增加新用户：首先在数据库本机上用ROOT用户登录上MySql（不用我告诉你如何登录吧？），然后：

        mysql>GRANT ALL PRIVILEGES ON *.* TO admin@localhost IDENTIFIED BY 'hsm' WITH GRANT OPTION;
        mysql>GRANT ALL PRIVILEGES ON *.* TO admin@"%" IDENTIFIED BY 'hsm' WITH GRANT OPTION;


第一句增加了一个admin用户授权通过本地机（localhost)访问，密码“hsm”。第二句则是授与admin用户从任何其它主机发起的访问（通配符％）,或者特定IP地址，注意这是欲连接到此Mysql数据库的客户端的IP地址，而不是Mysql数据库所在数据库服务器的IP地址，切记。

你也可以直接通过发出INSERT语句增加同样的用户存取信息：

mysql>INSERT INTO user VALUES('localhost','admin',PASSWORD('hsm'), 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y')
mysql>INSERT INTO user VALUES('%','admin',PASSWORD('hsm'), 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y')

该命令取决于你的MySQL版本，对上述，你可能必须使用一个不同数目'Y'值(在3.22.11以前的版本有更少的权限列)。

验证方法，在命令行中输入：mysql -h主机名或IP地址 -u用户名 -p密码
3.2、关于安全：
create user dba identified by 'sqlstudy
此时，创建的 MySQL 用户的全名是：dba@'%'
见到这样的MySQL 用户名是不是觉得有点困惑？原来 MySQL 的用户名包含两个部分： 1. 帐户名（account name）；2. 主机名（hostname）。 hostname 用来限制该MySQL 帐户（account）从何处来访问 MySQL 服务器。
dba@'%'             ：可以在网络中的任意地方，使用帐户名 dba 来访问 MySQL 服务。
dba@'localhost'     ：只能在本机（MySQL 服务所在的机器上），使用帐户名 dba 来连接 MySQL。
dba@'192.168.0.200' ：只能从网络中的 192.168.0.200 机器上，使用帐户名 dba 来连接 MySQL。
dba@'192.168.0.%'   ：可以从网络中的 192.168.0. 任意一台机器上，使用帐户名 dba 来连接 MySQL。
为了使 MySQL 更安全，我们在创建用户的时候，应该根据实际访问限制，来选择合适的用户名。 同时要谨慎使用 dba@'%'，另外在创建用户的时候，最好显式指定 hostname。
create user dba@'localhost' identified by 'sqlstudylocal'
create user dba@'%'         identified by 'sqlstudyany'
drop user dba
等价于：
drop user dba@'%'
3.3、数据操纵：
旧数据升级办法
    (1) 导出数据库：
    mysqldump -uroot -p123456 --default-character-set=latin1 --set-charset=utf8 --opt olddatabase > newdatabase.sql
    (2) 修改newdatabase.sql，在文件开头增加一条sql语句： “SET NAMES utf8;“，保存。
    (3) 导入数据库：
        mysql -hlocalhost -uroot my_db  music.sql
        mysql -uroot -p
            >create database music;
            >use music;
            >create user z;
            >source ./music.sql;
            >show tables;
            >charset utf8;// == set names 'utf8'
            >select * from composer;
   

(四) 显示命令  
（注意：和上面不同，下面的因为是MYSQL环境中的命令，所以后面都带一个分号作为命令结束符）  
1、显示数据库列表：  
   SHOW DATABASES;  
   刚开始时才两个数据库：mysql和test。mysql库很重要它里面有MYSQL的系统信息，我们改密码和新增用户，实际上就是用这个库进行操作。  

2、显示库中的数据表：  
   USE mysql； //打开库
   SHOW TABLES;  

3、显示数据表的结构：  
   DESCRIBE 表名;  

4、建库：  
   CREATE DATABASE 库名;  

5、建表：  
   USE 库名；  
   CREATE TABLE 表名 (字段设定列表)；  

6、删库和删表:  
   DROP DATABASE 库名;  
   DROP TABLE 表名；  

7、将表中记录清空：  
   DELETE FROM 表名;  

8、显示表中的记录：  
   SELECT * FROM 表名;
9、向表中添加数据：
   INSERT INTO table_name VALUES('','');
10、添加表：
   CREATE TABLE  `music`.`user` (`user` varchar(255) default NULL,`pwd` varchar(255) default NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8
11、更新表：
   UPDATE table_name SET name='HSM' WHERE id=1;
12、更改表：
   ALTER TABLE t2 DROP COLUMN c, DROP COLUMN d;
13、把表t1重新命名为t2：
   ALTER TABLE t1 RENAME t2;
14、删除列c：
   ALTER TABLE t2 DROP COLUMN c;
15、添加一个新的AUTO_INCREMENT整数列，名称为c：
   ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (c);
16、创建索引：
   CREATE INDEX part_of_name ON customer (name(10));
    示例：
   CREATE TABLE lookup (id INT) ENGINE = MEMORY;
   CREATE INDEX id_index USING BTREE ON lookup (id);
17、赋予权限：
   GRANT ALL ON amarok.* TO amarok IDENTIFIED BY 'amarok';
   
   SHOW GRANTS FOR amarok;
18、撤销权限：
   REVOKE ALL ON amarok.* FROM amarok;
               
               
               
               

本文来自ChinaUnix博客，如果查看原文请点：http://blog.chinaunix.net/u3/107272/showart_2110533.html