Frequently Used MySQL operations

MySQL Summary
Change Character Set
Set names utf8 will be modified
Character_set_client
Character_set_connection
Character_set_results
 
Use
Set character_set_database = 'utf8 ';
Set
Character_set_database
Character_set_filesystem
Character_set_server
Character_set_system
 
Query status show status
Query System Setting variable show variable

Differential statement
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 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 | 21:42:33
| 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)