mysql使用技巧

1 在suse 10.0 以上的版本中集成了mysql，使用时要先初始化数据库才可以启动：mysql_install_db --user=mysql
2 修改mysql的字符集支持中文
shell> vi /etc/my.cnf
[client]中加入 default-character-set=utf8
[mysqld]中加入 default-character-set=utf8
3 启动mysql服务
 mysqld_safe --u mysql
4 验证mysql服务已经启动
A 查看mysql的版本
shell> mysqladmin version
B 查看变量信息
shell> mysqladmin variables
我们可以看到字符集都已变成utf8
-+-----------------------------------------+
| Variable_name                   | Value                       
+-----------------------------------------+
| auto_increment_increment        | 1                                       |
| auto_increment_offset           | 1                                       |
| automatic_sp_privileges         | ON                                      |
| back_log                        | 50                                      |
| basedir                         | /usr/                                   |
| binlog_cache_size               | 32768                                   |
| bulk_insert_buffer_size         | 8388608                                 |
| character_set_client            | utf8                                    |
| character_set_connection        | utf8                                    |
| character_set_database          | utf8                                    |
| character_set_results           | utf8                                    |
| character_set_server            | utf8                                    |
| character_set_system            | utf8                                    |
| character_sets_dir              | /usr/share/mysql/charsets/              |
| collation_connection            | utf8_general_ci                         |
| collation_database              | utf8_general_ci                         |
| collation_server                | utf8_general_ci                         |
| completion_type                 | 0                                       |
| concurrent_insert               | 1                                       |
| connect_timeout                 | 5                                       |
| datadir                         | /var/lib/mysql/                         |
| date_format                     | %Y-%m-%d                                |
| datetime_format                 | %Y-%m-%d %H:%i:%s                       |
| default_week_format             | 0                                       |
| delay_key_write                 | ON                                      |
| delayed_insert_limit            | 100                                     |
| delayed_insert_timeout          | 300                                     |
| delayed_queue_size              | 1000                                    |
| div_precision_increment         | 4                                       |
| engine_condition_pushdown       | OFF                                     |
| expire_logs_days                | 0        

5 默认情况下mysql的root帐号是没有密码的可以为其设置密码
shell> mysqladmin -u root password newpwd
6 登录mysql：
shell> mysql -u root -p
password
7 查看数据库
mysql> show databases;
8 建立数据库
create database yourdb;
9 删除数据库
mysql > drop database yourdb;
10 打开数据库
mysql> use test;
11 从文件执行sql语句
.\ filename(本句不要使用分号结束)
12 查看数据库中的表
mysql> show tables;
13 显示表结构
mysql>describe yourtable;
14 使用jdbc连接mysql
    public Connection getConn(){
        Connection conn = null;
        String url = "jdbc:mysql://localhost/accptrain?user=root&password=root&useUnicode=true&characterEncoding=utf-8";
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url);
            //System.out.println("^_^ connect to database ok!");
        } catch (ClassNotFoundException ex) {
            System.out.println(":( a exception occor!" + ex.getMessage());
            ex.printStackTrace();
        } catch (SQLException ex) {
            /** @todo Handle this exception */
            System.out.println(":( a exception occor!" + ex.getMessage());
            ex.printStackTrace();
        }

        return conn;

    }
 
15 关闭mysql服务
shell> mysqladmin shutdown
16 持续更新中...