mysql经常用的操作

mysql总结
更改字符集
set names utf8会修改
 character_set_client
 character_set_connection
 character_set_results
 
 其他集合分别用
 set character_set_database = 'utf8';
 进行设置
 character_set_database
 character_set_filesystem
 character_set_server
 character_set_system
 
查询状态show status
查询系统设置变量 show variable

差用语句
create database openfire default character set='utf8' collate utf8_general_ci;
desc [tablename]
show create table [tablename]
alter database openfire default character set 'gbk';

mysql> create table user (
    -> id mediumint(3) not null auto_increment,
    -> name varchar(20) not null default 'wxj',
    -> primary key(id));
 
mysql> alter table user change name name varchar(15) not null;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | mediumint(3) | NO   | PRI | NULL    | auto_increment |
| name  | varchar(15)  | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> insert into user values(0,'wxj');
Query OK, 1 row affected (0.06 sec)

mysql> update user set name='wangxijie' where id =1;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

mysql> show table status where name ='user';
+------+--------+---------+------------+------+----------------+-------------+--
---------------+--------------+-----------+----------------+--------------------
-+-------------+------------+-----------------+----------+----------------+-----
----+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | M
ax_data_length | Index_length | Data_free | Auto_increment | Create_time
 | Update_time | Check_time | Collation       | Checksum | Create_options | Comm
ent |
+------+--------+---------+------------+------+----------------+-------------+--
---------------+--------------+-----------+----------------+--------------------
-+-------------+------------+-----------------+----------+----------------+-----
----+
| user | InnoDB |      10 | Compact    |    1 |          16384 |       16384 |
             0 |            0 |   4194304 |              2 | 2009-05-19 21:42:33
 | NULL        | NULL       | utf8_general_ci |     NULL |                |
    |
+------+--------+---------+------------+------+----------------+-------------+--
---------------+--------------+-----------+----------------+--------------------
-+-------------+------------+-----------------+----------+----------------+-----
----+
1 row in set (0.00 sec)

mysql> select * from user where id =1;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | wangxijie |
+----+-----------+
1 row in set (0.00 sec)

mysql> delete from user where id=1;
Query OK, 1 row affected (0.09 sec)

mysql> drop table user;
Query OK, 0 rows affected (0.06 sec)