mysql表数据的增删查改

mysql> create table staff
    -> (
    -> id int,
    -> name varchar(20),
    -> gender char(2),
    -> birthday date,
    -> entry_date date,
    -> job varchar(20),
    -> salary double,
    -> resume longtext
    -> )character set utf8
    -> collate utf8_general_ci;
Query OK, 0 rows affected (0.14 sec)

mysql> desc staff
    -> ;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | int(11)     | YES  |     | NULL    |       |
| name       | varchar(20) | YES  |     | NULL    |       |
| gender     | char(2)     | YES  |     | NULL    |       |
| birthday   | date        | YES  |     | NULL    |       |
| entry_date | date        | YES  |     | NULL    |       |
| job        | varchar(20) | YES  |     | NULL    |       |
| salary     | double      | YES  |     | NULL    |       |
| resume     | longtext    | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
8 rows in set (0.01 sec)

mysql> insert into staff
    -> values
    -> (1,'jack','m''1989-11-11','2013-11-11','java engineer',3000,'this is good man');
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> insert into staff
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the

right syntax to use near '' at line 1
mysql> insert into staff
    -> values
    -> (1,'jack','m''1989-11-11','2013-11-11','java',3000,'this');
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> insert into staff
    -> values
    -> (1,'jack','m','1989-11-1','2013-11-11','java engineer',3000,'this is good man');
Query OK, 1 row affected (0.07 sec)

mysql> select * from staff;
+------+------+--------+------------+------------+---------------+--------+------------------+
| id   | name | gender | birthday   | entry_date | job           | salary | resume           |
+------+------+--------+------------+------------+---------------+--------+------------------+
|    1 | jack | m      | 1989-11-01 | 2013-11-11 | java engineer |   3000 | this is good man |
+------+------+--------+------------+------------+---------------+--------+------------------+
1 row in set (0.00 sec)

mysql> insert into staff
    -> (id,name,gender)
    -> values
    -> (1,'kate','f');
Query OK, 1 row affected (0.06 sec)

mysql> select * from staff;
+------+------+--------+------------+------------+---------------+--------+------------------+
| id   | name | gender | birthday   | entry_date | job           | salary | resume           |
+------+------+--------+------------+------------+---------------+--------+------------------+
|    1 | jack | m      | 1989-11-01 | 2013-11-11 | java engineer |   3000 | this is good man |
|    1 | kate | f      | NULL       | NULL       | NULL          |   NULL | NULL             |
+------+------+--------+------------+------------+---------------+--------+------------------+
2 rows in set (0.00 sec)

mysql> insert into staff
    -> (id,name,gender)
    -> values
    -> (1,'冯立','f');
ERROR 1366 (HY000): Incorrect string value: '\xB7\xEB\xC1\xA2' for column 'name' at row 1
mysql> set character_set_client=gbk;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into staff
    -> (id,name,gender)
    -> values
    -> (1,'冯立','f');
Query OK, 1 row affected (0.07 sec)

mysql> select * from staff;
+------+--------+--------+------------+------------+---------------+--------+------------------+
| id   | name   | gender | birthday   | entry_date | job           | salary | resume           |
+------+--------+--------+------------+------------+---------------+--------+------------------+
|    1 | jack   | m      | 1989-11-01 | 2013-11-11 | java engineer |   3000 | this is good man |
|    1 | kate   | f      | NULL       | NULL       | NULL          |   NULL | NULL             |
|    1 | 鍐珛   | f      | NULL       | NULL       | NULL          |   NULL | NULL             |
+------+--------+--------+------------+------------+---------------+--------+------------------+
3 rows in set (0.00 sec)

mysql> set character_set_results=gbk;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from staff;
+------+------+--------+------------+------------+---------------+--------+------------------+
| id   | name | gender | birthday   | entry_date | job           | salary | resume           |
+------+------+--------+------------+------------+---------------+--------+------------------+
|    1 | jack | m      | 1989-11-01 | 2013-11-11 | java engineer |   3000 | this is good man |
|    1 | kate | f      | NULL       | NULL       | NULL          |   NULL | NULL             |
|    1 | 冯立     | f      | NULL       | NULL       | NULL          |   NULL | NULL             |
+------+------+--------+------------+------------+---------------+--------+------------------+
3 rows in set (0.00 sec)

mysql> update table staff
    -> set salary=3000;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the

right syntax to use near 'table staff
set salary=3000' at line 1
mysql> update staff set salary=3000;
Query OK, 2 rows affected (0.13 sec)
Rows matched: 3  Changed: 2  Warnings: 0

mysql> select * from staff;
+------+------+--------+------------+------------+---------------+--------+------------------+
| id   | name | gender | birthday   | entry_date | job           | salary | resume           |
+------+------+--------+------------+------------+---------------+--------+------------------+
|    1 | jack | m      | 1989-11-01 | 2013-11-11 | java engineer |   3000 | this is good man |
|    1 | kate | f      | NULL       | NULL       | NULL          |   3000 | NULL             |
|    1 | 冯立     | f      | NULL       | NULL       | NULL          |   3000 | NULL             |
+------+------+--------+------------+------------+---------------+--------+------------------+
3 rows in set (0.00 sec)

mysql> update staff set salary=10000 where name='kate'
    -> ;
Query OK, 1 row affected (0.08 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update staff set id=2 where name='kate';
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update staff set id=3 where name='冯立';
Query OK, 1 row affected (0.13 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from staff;
+------+------+--------+------------+------------+---------------+--------+------------------+
| id   | name | gender | birthday   | entry_date | job           | salary | resume           |
+------+------+--------+------------+------------+---------------+--------+------------------+
|    1 | jack | m      | 1989-11-01 | 2013-11-11 | java engineer |   3000 | this is good man |
|    2 | kate | f      | NULL       | NULL       | NULL          |  10000 | NULL             |
|    3 | 冯立     | f      | NULL       | NULL       | NULL          |   3000 | NULL             |
+------+------+--------+------------+------------+---------------+--------+------------------+
3 rows in set (0.00 sec)

mysql> update staff set birthday='1989-01-11' ,entry_date='1999-01-11' where birchday is null and entry_date is null;
ERROR 1054 (42S22): Unknown column 'birchday' in 'where clause'
mysql> update staff set birthday='1989-01-11' ,entry_date='1999-01-11' where birthday is null and entry_date is null;
Query OK, 2 rows affected (0.07 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from staff;
+------+------+--------+------------+------------+---------------+--------+------------------+
| id   | name | gender | birthday   | entry_date | job           | salary | resume           |
+------+------+--------+------------+------------+---------------+--------+------------------+
|    1 | jack | m      | 1989-11-01 | 2013-11-11 | java engineer |   3000 | this is good man |
|    2 | kate | f      | 1989-01-11 | 1999-01-11 | NULL          |  10000 | NULL             |
|    3 | 冯立     | f      | 1989-01-11 | 1999-01-11 | NULL          |   3000 | NULL             |
+------+------+--------+------------+------------+---------------+--------+------------------+
3 rows in set (0.00 sec)

mysql> select id,name from staff;
+------+------+
| id   | name |
+------+------+
|    1 | jack |
|    2 | kate |
|    3 | 冯立     |
+------+------+
3 rows in set (0.00 sec)

mysql> select id,name as 姓名 from staff;
+------+------+
| id   | 姓名     |
+------+------+
|    1 | jack |
|    2 | kate |
|    3 | 冯立     |
+------+------+
3 rows in set (0.00 sec)

mysql> select id as 编号,name as 姓名 from staff;
+------+------+
| 编号     | 姓名     |
+------+------+
|    1 | jack |
|    2 | kate |
|    3 | 冯立     |
+------+------+
3 rows in set (0.00 sec)

mysql> delete from staff where id=3;
Query OK, 1 row affected (0.07 sec)

mysql> select * from staff;
+------+------+--------+------------+------------+---------------+--------+------------------+
| id   | name | gender | birthday   | entry_date | job           | salary | resume           |
+------+------+--------+------------+------------+---------------+--------+------------------+
|    1 | jack | m      | 1989-11-01 | 2013-11-11 | java engineer |   3000 | this is good man |
|    2 | kate | f      | 1989-01-11 | 1999-01-11 | NULL          |  10000 | NULL             |
+------+------+--------+------------+------------+---------------+--------+------------------+
2 rows in set (0.00 sec)

mysql> truncate staff;
Query OK, 0 rows affected (0.05 sec)

mysql> select * from staff;
Empty set (0.00 sec)

mysql> insert into staff
    -> values
    -> truncate staff;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the

right syntax to use near 'truncate staff' at line 3
mysql> insert into staff
    -> values
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the

right syntax to use near '' at line 2
mysql> quit