insert插入日期
format

首先讲一下数字格式化:format

最先我们查找工资与名字,工资是以这样的格式显示的

mysql> select ename,sal from emp;
+--------+---------+
| ename  | sal     |
+--------+---------+
| SMITH  |  800.00 |
| ALLEN  | 1600.00 |
| WARD   | 1250.00 |
| JONES  | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| SCOTT  | 3000.00 |
| KING   | 5000.00 |
| TURNER | 1500.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| FORD   | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+
14 rows in set (0.00 sec)

现在我想以千分位显示工资,就需要用到format

format语法格式:

format(数字,'格式');

例:

mysql> select ename,format(sal,'$999,999') as sal from emp;
+--------+-------+
| ename  | sal   |
+--------+-------+
| SMITH  | 800   |
| ALLEN  | 1,600 |
| WARD   | 1,250 |
| JONES  | 2,975 |
| MARTIN | 1,250 |
| BLAKE  | 2,850 |
| CLARK  | 2,450 |
| SCOTT  | 3,000 |
| KING   | 5,000 |
| TURNER | 1,500 |
| ADAMS  | 1,100 |
| JAMES  | 950   |
| FORD   | 3,000 |
| MILLER | 1,300 |
+--------+-------+
14 rows in set, 14 warnings (0.00 sec)
str_to_date

str_to_date函数:将字符串varchar类型转换成date类型

例:

我先创建一个user表:

mysql> create table t_user(id int,name varchar(32),birth date);
Query OK, 0 rows affected (0.06 sec)

mysql> desc t_user;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(32) | YES  |     | NULL    |       |
| birth | date        | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

我插入数据:

mysql> insert into t_user(id,name,birth) values(1,'YuKi','21-02-2021');
ERROR 1054 (42S22): Unknown column '21-02-2021' in 'field list'

因为我原本是date,但这里插入的却是字符串类型,所以报错,这时候就可以使用str_to_date函数进行类型转换

语法格式:

str_to_date('字符串日期','日期格式')

mysql中,日期格式是这样的:

%Y年    %m月    %d日    %h时    %i分    %s秒

我们使用str_to_date试试

mysql> insert into t_user(id,name,birth) values(1,'YuKi',str_to_date('21-02-2021','%d-%m-%Y'));
Query OK, 1 row affected (0.01 sec)

mysql> select * from t_user;
+------+------+------------+
| id   | name | birth      |
+------+------+------------+
|    1 | YuKi | 2021-02-21 |
+------+------+------------+
1 row in set (0.00 sec)

注:如果你写的日期字符串是这个格式,mysql会自动转换,格式为

%Y-%m-%d

例:

mysql> insert into t_user(id,name,birth) values(2,'Facebook','2021-02-22');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t_user;
+------+----------+------------+
| id   | name     | birth      |
+------+----------+------------+
|    1 | YuKi     | 2021-02-21 |
|    2 | Facebook | 2021-02-22 |
+------+----------+------------+
2 rows in set (0.00 sec)
date和datetime区别
  • date是短日期:只包括年月日信息,默认格式:%Y-%m-%d
  • datetime是长日期:包括年月日时分秒信息,默认格式:%Y-%m-%d %h:%i:%s

例:我们创建一个新的生日表

mysql> create table t_user(id int,name varchar(32), birth date,create_time datetime);
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+---------------------+
| Tables_in_testmysql |
+---------------------+
| dept                |
| emp                 |
| salgrade            |
| t_user              |
+---------------------+
4 rows in set (0.00 sec)

插入数据,并且查看

mysql> insert into t_user(id,name,birth,create_time) values(1,'YQHP-YuKi','2021-03-02','2020-03-02 15:09:24');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_user;
+------+-----------+------------+---------------------+
| id   | name      | birth      | create_time          |
+------+-----------+------------+---------------------+
|    1 | YQHP-YuKi | 2021-03-02 | 2020-03-02 15:09:24 |
+------+-----------+------------+---------------------+
1 row in set (0.00 sec)

当我们试图把create_timedatetime改为date时,再次插入准确时间试试

mysql> drop table t_user;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t_user(id int,name varchar(32), birth date,create_time date);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t_user(id,name,birth,create_time) values(1,'YQHP-YuKi','2021-03-02','2020-03-02 15:09:24');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from t_user;
+------+-----------+------------+-------------+
| id   | name      | birth      | create_time |
+------+-----------+------------+-------------+
|    1 | YQHP-YuKi | 2021-03-02 | 2020-03-02  |
+------+-----------+------------+-------------+
1 row in set (0.00 sec)

就出现了虽然我输入了准确时间的datetime格式,但现实出来的任然是date的年月日格式

now()函数

mysql当中,now()函数可以获取系统的当前时间,并且获取的时间格式是datetime类型

例:

mysql> create table t_user(id int,name varchar(32), birth date,create_time datetime);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t_user(id,name,birth,create_time) values(1,'Google','2021-03-02',now());
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_user;
+------+--------+------------+---------------------+
| id   | name   | birth      | create_time         |
+------+--------+------------+---------------------+
|    1 | Google | 2021-03-02 | 2021-03-02 15:19:24 |
+------+--------+------------+---------------------+
1 row in set (0.00 sec)
修改update(DML)

语法格式:

update 表明 set 字段名1=值1,字段名2=值2,字段名3=值3... where 条件

注:没有条件限制会导致所有的数据全部更新

例:

修改前的信息:

mysql> select * from t_user;
+------+--------+------------+---------------------+
| id   | name   | birth      | create_time         |
+------+--------+------------+---------------------+
|    1 | Google | 2021-03-02 | 2021-03-02 15:19:24 |
+------+--------+------------+---------------------+
1 row in set (0.00 sec)

修改:

mysql> update t_user set name='YQHP-YuKi',birth='2000-00-00' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

修改后的信息:

mysql> select * from t_user;
+------+-----------+------------+---------------------+
| id   | name      | birth      | create_time         |
+------+-----------+------------+---------------------+
|    1 | YQHP-YuKi | 2000-00-00 | 2021-03-02 15:19:24 |
+------+-----------+------------+---------------------+
1 row in set (0.00 sec)

更新所有:

mysql> update t_user set name='Java';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t_user;
+------+------+------------+---------------------+
| id   | name | birth      | create_time         |
+------+------+------------+---------------------+
|    1 | Java | 2000-00-00 | 2021-03-02 15:19:24 |
+------+------+------------+---------------------+
1 row in set (0.00 sec)

所以记得要加条件,不然就会修改全部

删除delete(DML)

语法格式:

delete from 表名 where 条件;

注:没有条件,整张表的数据会全部删除

例:删除id=1的用户

删除前

mysql> select * from t_user;
+------+------+------------+---------------------+
| id   | name | birth      | create_time         |
+------+------+------------+---------------------+
|    1 | Java | 2000-00-00 | 2021-03-02 15:19:24 |
+------+------+------------+---------------------+
1 row in set (0.00 sec)

删除

mysql> delete from t_user where id = 1;
Query OK, 1 row affected (0.00 sec)

删除后

mysql> select * from t_user;
Empty set (0.00 sec)
insert插入多条记录

语法格式:

insert into 表名(字段1,字段2,字段3...) values(),(),()...;

例:插入

mysql> insert into t_user(id,name,birth,create_time) values(1,'YQHP-YuKi','2000-00-00',now()),(2,'Fackbook','2001-01-01',now()),(3,'Google','2002-02-02',now());
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

插入后

mysql> select * from t_user;
+------+-----------+------------+---------------------+
| id   | name      | birth      | create_time         |
+------+-----------+------------+---------------------+
|    1 | YQHP-YuKi | 2000-00-00 | 2021-03-04 17:04:41 |
|    2 | Fackbook  | 2001-01-01 | 2021-03-04 17:04:41 |
|    3 | Google    | 2002-02-02 | 2021-03-04 17:04:41 |
+------+-----------+------------+---------------------+
3 rows in set (0.00 sec)
表的快速复制

如果想对一张表进行快速复制,则可以使用将这张表查询出来并进行创建一张新表

例:

mysql> create table t_user2 as select * from t_user;
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

t_user

mysql> select * from t_user;
+------+-----------+------------+---------------------+
| id   | name      | birth      | create_time         |
+------+-----------+------------+---------------------+
|    1 | YQHP-YuKi | 2000-00-00 | 2021-03-04 17:04:41 |
|    2 | Fackbook  | 2001-01-01 | 2021-03-04 17:04:41 |
|    3 | Google    | 2002-02-02 | 2021-03-04 17:04:41 |
+------+-----------+------------+---------------------+
3 rows in set (0.00 sec)

t_user2

mysql> select * from t_user2;
+------+-----------+------------+---------------------+
| id   | name      | birth      | create_time         |
+------+-----------+------------+---------------------+
|    1 | YQHP-YuKi | 2000-00-00 | 2021-03-04 17:04:41 |
|    2 | Fackbook  | 2001-01-01 | 2021-03-04 17:04:41 |
|    3 | Google    | 2002-02-02 | 2021-03-04 17:04:41 |
+------+-----------+------------+---------------------+
3 rows in set (0.00 sec)
将查询结果新建为一张表

我们也可以将一张表的查询结果新建成为一张新的表

例:

mysql> create table result_table as select empno,ename from emp where job = 'MANAGER';
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> select * from result_table;
+-------+-------+
| empno | ename |
+-------+-------+
|  7566 | JONES |
|  7698 | BLAKE |
|  7782 | CLARK |
+-------+-------+
3 rows in set (0.00 sec)
将查询结果插入一张表

既然可以将查询结果创建为一张新的表,那么就也可以将查询记录插入到一张表

例:

先创建一个表

mysql> create table test_table as select * from dept;
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from test_table;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)

查询并插入

mysql> insert into test_table select * from dept;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from test_table;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
8 rows in set (0.00 sec)
Last modification:March 5th, 2021 at 09:59 am