创建员工信息表t_user
CREATE TABLE t_user(
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20) ,
password VARCHAR(20) ,
age int ,
phone VARCHAR(20) ,
email VARCHAR(20) ,
is_Delete int
)DEFAULT CHARSET=UTF8;
往员工表中插入数据:
INSERT
INTO t_user
VALUES(null,'张三','123456',23,'110','[email protected]',1),(null,'历史','123456',23,'110','[email protected]',1),(null,'孙悟空','123456',23,'110','[email protected]',2),(null,'李白','123456',23,'110','[email protected]',2),(null,'八戒','123456',23,'110','[email protected]',2);
问题:发现用delete from t_user where id=6;这个语句删除不了,其它字段名也试过了也不行,如下:
mysql> select * from t_user;
+----------+----------------+----------------+-----------+-------------+--------------+-----------------+
| id | username | password | age | phone | email | is_Delete |
+----------+----------------+----------------+-----------+-------------+--------------+-----------------+
| 1 | 唐僧 | 123456 | 23 | 110 | [email protected] | 2 |
| 2 | 张三 | 123456 | 23 | 110 | [email protected] | 1 |
| 3 | 历史 | 123456 | 23 | 110 | [email protected] | 1 |
| 4 | 孙悟空 | 123456 | 23 | 110 | [email protected] | 2 |
| 5 | 李白 | 123456 | 23 | 110 | [email protected] | 2 |
| 6 | 八戒 | 123456 | 23 | 110 | [email protected] | 2 |
+----------+----------------+----------------+-----------+-------------+--------------+-----------------+
6 rows in set (0.00 sec) mysql> mysql> delete from t_user where id=6;
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 'delete from t_user where id=6' at line 1
mysql> delete from t_user where id='';
ERROR 1054 (42S22): Unknown column 'id' in 'where clause'
mysql> delete form t_user where id=6;
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 't_user where id=6' at line 1
mysql> delete from t_user where id=6;
ERROR 1054 (42S22): Unknown column 'id' in 'where clause'
mysql> delete from t_user where username='八戒';
ERROR 1054 (42S22): Unknown column 'username' in 'where clause'
mysql> delete from t_user where t_user.id=6;
ERROR 1054 (42S22): Unknown column 't_user.id' in 'where clause'
mysql>
然后发现是表中的字段名有空格,正常的没空格的如下:
mysql> select * from dept;
+----+--------+
| id | name |
+----+--------+
| 1 | 神仙 |
| 2 | 妖怪 |
+----+--------+
字段名出现空格的话,通过字段名进行数据操作的时候要给字段名添加单引号,如下:
mysql> delete from t_user where 'id'=6;
Query OK, 0 rows affected, 1 warning (0.07 sec)