MySQL basic operation (ii)

1. Insert the data into the table MSG, create the table first, and then insert the data

Mysql> CREATE TABLE MSG (
ID int,
Title varchar (60),
Name varchar (10),
Content varchar (1000)
);
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT into MSG (id,title,name,content) VALUES (1, "newcomer", "Zhang San", "I am the eldest");
ERROR 1366 (HY000): Incorrect string value: ' \xe5\x88\x9d\xe6\x9d\xa5 ... ' for column ' title ' at row 1

The error is inserted because the database does not have a character set reason, set as follows:

Modifying the character set of a database

mysql> ALTER DATABASE test character set UTF8;
Query OK, 1 row affected (0.10 sec)

Modifying the database table character set

mysql> ALTER TABLE MSG character set UTF8;
Query OK, 0 rows affected (0.10 sec)
records:0 duplicates:0 warnings:0

Modify the character set for each table field

Mysql> ALTER TABLE MSG change title title varchar (character) set UTF8;
Query OK, 0 rows affected (0.33 sec)
records:0 duplicates:0 warnings:0

Mysql> ALTER TABLE MSG change name name varchar (character) set UTF8;

Query OK, 0 rows affected (0.14 sec)

Mysql> ALTER TABLE MSG change content content varchar (character) set UTF8;
Query OK, 0 rows affected (0.16 sec)
records:0 duplicates:0 warnings:0

or perform

Set names UTF8;

2. Update data

mysql> Update msg set name= "Harry", content= "I just want to be a dick" where msg.id=2;
Query OK, 1 row affected (0.02 sec)
Rows matched:1 changed:1 warnings:0

Mysql> select * from MSG;
+------+--------------+--------+--------------------+
| ID | Title | name | Content |
+------+--------------+--------+--------------------+
| 1 | Newcomers | Zhang San | I'm the Boss |
| 2 | Just arrived soon | Harry | I just want to be a dick |
+------+--------------+--------+--------------------+
2 rows in Set (0.00 sec)

Mysql>

3. Delete table data

Mysql> Delete from msg where id=3;
Query OK, 1 row affected (0.02 sec)

5. Query table Data

Mysql> select Id,name from msg;
+------+--------+
| ID | name |
+------+--------+
| 1 | Zhang San |
| 1 | Liu Bei |
| 2 | About |
+------+--------+
3 Rows in Set (0.00 sec)

MySQL basic operation (ii)