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-%ddatetime是长日期:包括年月日时分秒信息,默认格式:%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_time的datetime改为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: 0t_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: 0mysql> 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)
