【SQL】利用sql语句在mysql的表中插入date类型的数据,

╰半橙微兮° 2022-11-19 15:58 204阅读 0赞

一. 创建一个数据库

  1. create database test

二. 在数据库中创建表

  1. create TABLE employees (emp_no int(4) not null,birth_date date not null,first_name varchar(14) not null,last_name varchar(16) not null,gender char(1) not null,hire_dare date not null,primary key (emp_no));

三. 表结构

  1. mysql> desc employees;
  2. +------------+-------------+------+-----+---------+-------+
  3. | Field | Type | Null | Key | Default | Extra |
  4. +------------+-------------+------+-----+---------+-------+
  5. | emp_no | int(4) | NO | PRI | NULL | |
  6. | birth_date | date | NO | | NULL | |
  7. | first_name | varchar(14) | NO | | NULL | |
  8. | last_name | varchar(16) | NO | | NULL | |
  9. | gender | char(1) | NO | | NULL | |
  10. | hire_dare | date | NO | | NULL | |
  11. +------------+-------------+------+-----+---------+-------+
  12. 6 rows in set (0.73 sec)

四. 插入记录

  1. mysql> insert into employees values(2,1998-07-14,'li','bc','w',2019-02-21);
  2. Query OK, 1 row affected, 2 warnings (0.00 sec)

可以看到有告警

五. 查看表内容

  1. mysql> select * from employees;
  2. +--------+------------+------------+-----------+--------+------------+
  3. | emp_no | birth_date | first_name | last_name | gender | hire_dare |
  4. +--------+------------+------------+-----------+--------+------------+
  5. | 1 | 0000-00-00 | mai | yp | m | 0000-00-00 |
  6. | 2 | 0000-00-00 | li | bc | w | 0000-00-00 |

插入date类型的字段失效

六. 解决

  1. mysql> insert into employees values(3,1998-07-14,'li','bc','w',20190211);
  2. ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'
  3. mysql> insert into employees values(4,1998-07-14,'li','bc','w',20190211);
  4. Query OK, 1 row affected, 1 warning (0.05 sec)
  5. mysql> select * from employees;
  6. +--------+------------+------------+-----------+--------+------------+
  7. | emp_no | birth_date | first_name | last_name | gender | hire_dare |
  8. +--------+------------+------------+-----------+--------+------------+
  9. | 1 | 0000-00-00 | mai | yp | m | 0000-00-00 |
  10. | 2 | 0000-00-00 | li | bc | w | 0000-00-00 |
  11. | 3 | 0000-00-00 | li | bc | w | 0000-00-00 |
  12. | 4 | 0000-00-00 | li | bc | w | 2019-02-11 |
  13. +--------+------------+------------+-----------+--------+------------+
  14. 4 rows in set (0.00 sec)

参考:https://blog.csdn.net/lycyl/article/details/45689163

发表评论

表情:
评论列表 (有 0 条评论,204人围观)

还没有评论,来说两句吧...

相关阅读