1 distinct
把查询结果去除重复记录
注意:
- 原表数据不会被修改,只是查询结果去重。
- distinct只能出现在所有字段的最前方。
- distinct出现在job,deptno两个字段之前,表示两个字段联合起来去重。
mysql> select job from emp;
+-----------+
| job |
+-----------+
| CLERK |
| SALESMAN |
| SALESMAN |
| MANAGER |
| SALESMAN |
| MANAGER |
| MANAGER |
| ANALYST |
| PRESIDENT |
| SALESMAN |
| CLERK |
| CLERK |
| ANALYST |
| CLERK |
+-----------+
14 rows in set (0.01 sec)
mysql> select distinct job from emp;
+-----------+
| job |
+-----------+
| CLERK |
| SALESMAN |
| MANAGER |
| ANALYST |
| PRESIDENT |
+-----------+
5 rows in set (0.01 sec)
mysql> select distinct deptno,job from emp;
+--------+-----------+
| deptno | job |
+--------+-----------+
| 20 | CLERK |
| 30 | SALESMAN |
| 20 | MANAGER |
| 30 | MANAGER |
| 10 | MANAGER |
| 20 | ANALYST |
| 10 | PRESIDENT |
| 30 | CLERK |
| 10 | CLERK |
+--------+-----------+
9 rows in set (0.00 sec)
#统计一下工作岗位的数量
mysql> select count(distinct job) from emp;
+---------------------+
| count(distinct job) |
+---------------------+
| 5 |
+---------------------+
1 row in set (0.01 sec)
2 连接查询
2.1 什么是连接查询?
从一张表中单独查询,称为单表查询。
emp表和dept表联合起来查询数据,从emp表中取员工名字,从dept表中取部门名字。这种跨表查询,多张表联合起来查询数据,被称为连接查询。
笛卡尔积现象:当两张表进行连接查询,没有任何条件限制的时候,最终查询结果条数,是两张表条数的乘积。
select ename,dname from emp, dept;
怎么避免笛卡尔积现象?连接时加条件,满足这个条件的记录被筛选出来!
select
...
from
a
join
b
on
连接条件
where
...
最终查询的结果条数是14条,但是匹配的过程中,匹配的次数减少了吗?还是56次,只不过进行了四选一。次数没有减少。
注意:通过笛卡尔积现象得出,表的连接次数越多效率越低,尽量避免表的连接次数。
2.2 连接查询的分类
1.根据语法的年代分类:
- SQL92:1992年的时候出现的语法
- SQL99:1999年的时候出现的语法
我们这里重点学习SQL99.(这个过程中简单演示一个SQL92的例子)
2.根据表连接的方式分类:
- 内连接:
- 等值连接
- 非等值连接
- 自连接
- 外连接:
- 左外连接(左连接)
- 右外连接(右连接)
- 全连接(不讲)
2.3 内连接
2.3.1 等值连接
连接条件是等量关系,所以被称为等值连接
查询每个员工所在部门名称,显示员工名和部门名?
mysql> select e.ename,d.dname
-> from emp e
-> inner join dept d
-> on e.deptno=d.deptno;
+--------+------------+
| ename | dname |
+--------+------------+
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCH |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | ACCOUNTING |
| SCOTT | RESEARCH |
| KING | ACCOUNTING |
| TURNER | SALES |
| ADAMS | RESEARCH |
| JAMES | SALES |
| FORD | RESEARCH |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.01 sec)
注意:
- inner可以省略,不省略更直观,一看就知道是内连接
emp e
是给表起别名,可以更直观易懂
2.3.2 非等值连接
条件不是一个等量关系,称为非等值连接。
找出每个员工的薪资等级,要求显示员工名、薪资、薪资等级?
mysql> select e.ename,e.sal,s.grade
-> from emp e
-> inner join salgrade s
-> on e.sal between s.losal and s.hisal;
+--------+---------+-------+
| ename | sal | grade |
+--------+---------+-------+
| SMITH | 800.00 | 1 |
| ALLEN | 1600.00 | 3 |
| WARD | 1250.00 | 2 |
| JONES | 2975.00 | 4 |
| MARTIN | 1250.00 | 2 |
| BLAKE | 2850.00 | 4 |
| CLARK | 2450.00 | 4 |
| SCOTT | 3000.00 | 4 |
| KING | 5000.00 | 5 |
| TURNER | 1500.00 | 3 |
| ADAMS | 1100.00 | 1 |
| JAMES | 950.00 | 1 |
| FORD | 3000.00 | 4 |
| MILLER | 1300.00 | 2 |
+--------+---------+-------+
14 rows in set (0.00 sec)
2.3.3 自连接
技巧:一张表看做两张表。
查询员工的上级领导,要求显示员工名和对应的领导名?
mysql> select e1.ename as empname,e2.ename as mgrname
-> from emp e1
-> inner join emp e2
-> on e1.mgr=e2.empno;
+---------+---------+
| empname | mgrname |
+---------+---------+
| SMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
+---------+---------+
13 rows in set (0.00 sec)
2.4 外连接
内连接:A和B连接,AB两张表没有主次关系。平等的。
内连接的特点:完成能够匹配上这个条件的数据查询出来。
外连接:两张表连接,有主次关系。
右外连接:表示将join关键字右边的这张表看成主表,主要是为了将这张表的数据全部查询出来,捎带着关联查询左边的表。
左外连接:正好相反
外连接的查询结果条数一定是 >= 内连接的查询结果条数
mysql> select e1.ename as empname,e2.ename as mgrname
-> from emp e1
-> join emp e2
-> on e1.mgr=e2.empno;
+---------+---------+
| empname | mgrname |
+---------+---------+
| SMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
+---------+---------+
13 rows in set (0.00 sec)
mysql> select e1.ename as empname,e2.ename as mgrname
-> from emp e1
-> left join emp e2
-> on e1.mgr=e2.empno;
+---------+---------+
| empname | mgrname |
+---------+---------+
| SMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| KING | NULL |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
+---------+---------+
14 rows in set (0.00 sec)
mysql> select e1.ename as empname,e2.ename as mgrname
-> from emp e2
-> right join emp e1
-> on e1.mgr=e2.empno;
+---------+---------+
| empname | mgrname |
+---------+---------+
| SMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| KING | NULL |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
+---------+---------+
14 rows in set (0.00 sec)
2.5 多张表连接
select
...
from
a
join
b
on
a和b的连接条件
join
c
on
a和c的连接条件
right join
d
on
a和d的连接条件
一条SQL中内连接和外连接可以混合。都可以出现!
案例:找出每个员工的部门名称以及工资等级,还有上级领导,要求显示员工名、领导名、部门名、薪资、薪资等级?
mysql> select e1.ename as empname,e2.ename as mgrname, d.dname,e1.sal,s.grade
-> from emp e1
-> join dept d
-> on e1.deptno=d.deptno
-> join salgrade s
-> on e1.sal between s.losal and s.hisal
-> left join emp e2
-> on e1.mgr=e2.empno;
+---------+---------+------------+---------+-------+
| empname | mgrname | dname | sal | grade |
+---------+---------+------------+---------+-------+
| SMITH | FORD | RESEARCH | 800.00 | 1 |
| ALLEN | BLAKE | SALES | 1600.00 | 3 |
| WARD | BLAKE | SALES | 1250.00 | 2 |
| JONES | KING | RESEARCH | 2975.00 | 4 |
| MARTIN | BLAKE | SALES | 1250.00 | 2 |
| BLAKE | KING | SALES | 2850.00 | 4 |
| CLARK | KING | ACCOUNTING | 2450.00 | 4 |
| SCOTT | JONES | RESEARCH | 3000.00 | 4 |
| KING | NULL | ACCOUNTING | 5000.00 | 5 |
| TURNER | BLAKE | SALES | 1500.00 | 3 |
| ADAMS | SCOTT | RESEARCH | 1100.00 | 1 |
| JAMES | BLAKE | SALES | 950.00 | 1 |
| FORD | JONES | RESEARCH | 3000.00 | 4 |
| MILLER | CLARK | ACCOUNTING | 1300.00 | 2 |
+---------+---------+------------+---------+-------+
14 rows in set (0.00 sec)
3 子查询
select语句中嵌套select语句,被嵌套的select语句称为子查询。
子查询出现的位置:
select
..(select).
from
..(select).
where
..(select).
3.1 where子句中的子查询
案例:找出比最低工资高的员工姓名和工资?
思路:先找出最低工资,再把该查询结果作为条件放入where中
mysql> select min(sal) from emp;
+----------+
| min(sal) |
+----------+
| 800.00 |
+----------+
1 row in set (0.00 sec)
mysql> select ename,sal
-> from emp
-> where sal>(select min(sal) from emp);
+--------+---------+
| ename | sal |
+--------+---------+
| 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 |
+--------+---------+
13 rows in set (0.00 sec)
3.2 from子句中的子查询
技巧:from后面的子查询,可以将子查询的查询结果当做一张临时表。
案例:找出每个岗位的平均工资的薪资等级。
思路:先找出每个岗位的平均工资,再把结果当作一张临时表,从里面查询
mysql> select avg(sal) as avgsal ,job from emp group by job;
+-------------+-----------+
| avgsal | job |
+-------------+-----------+
| 1037.500000 | CLERK |
| 1400.000000 | SALESMAN |
| 2758.333333 | MANAGER |
| 3000.000000 | ANALYST |
| 5000.000000 | PRESIDENT |
+-------------+-----------+
5 rows in set (0.00 sec)
mysql> select a.job,a.avgsal,s.grade
-> from (select avg(sal) as avgsal ,job from emp group by job) a
-> join salgrade s
-> on a.avgsal between s.losal and s.hisal;
+-----------+-------------+-------+
| job | avgsal | grade |
+-----------+-------------+-------+
| CLERK | 1037.500000 | 1 |
| SALESMAN | 1400.000000 | 2 |
| MANAGER | 2758.333333 | 4 |
| ANALYST | 3000.000000 | 4 |
| PRESIDENT | 5000.000000 | 5 |
+-----------+-------------+-------+
5 rows in set (0.00 sec)
3.3 select后面出现的子查询
案例:找出每个员工的部门名称,要求显示员工名,部门名?
select
e.ename,e.deptno,(select d.dname from dept d where e.deptno = d.deptno) as dname
from
emp e;
4 union合并查询结果集
案例:查询工作岗位是MANAGER和SALESMAN的员工?
mysql> select ename,job from emp where job in ('MANAGER','SALESMAN');
+--------+----------+
| ename | job |
+--------+----------+
| ALLEN | SALESMAN |
| WARD | SALESMAN |
| JONES | MANAGER |
| MARTIN | SALESMAN |
| BLAKE | MANAGER |
| CLARK | MANAGER |
| TURNER | SALESMAN |
+--------+----------+
7 rows in set (0.00 sec)
mysql> select ename,job from emp where job = 'MANAGER'
-> union
-> select ename,job from emp where job = 'SALESMAN';
+--------+----------+
| ename | job |
+--------+----------+
| JONES | MANAGER |
| BLAKE | MANAGER |
| CLARK | MANAGER |
| ALLEN | SALESMAN |
| WARD | SALESMAN |
| MARTIN | SALESMAN |
| TURNER | SALESMAN |
+--------+----------+
7 rows in set (0.01 sec)
union的效率要高一些。对于表连接来说,每连接一次新表,则匹配的次数满足笛卡尔积。但是union可以减少匹配的次数。在减少匹配次数的情况下,还可以完成两个结果集的拼接。
如:
a 连接 b 连接 c
a 10条记录
b 10条记录
c 10条记录
匹配次数是:1000
a 连接 b一个结果:10 *10 --> 100次
a 连接 c一个结果:10 *10 --> 100次
使用union的话是:100次 + 100次 = 200次。
union把乘法变成了加法运算
注意事项:
- union在进行结果集合并的时候,要求两个结果集的列数相同。
- 结果集合并时列和列的数据类型要一致。(oracle有这个要求,My SQL不要求)
5 limit
limit作用:将查询结果集的一部分取出来。通常使用在分页查询当中。
例:百度默认一页显示10条记录。
分页的作用是为了提高用户的体验,因为一次全部都查出来,用户体验差。可以一页一页翻页看。
MySql 提供了 limit ,主要用于提取前几条或者中间某几行数据
select * from table limit m,n;
其中 m 是指记录开始的 index,从 0 开始,表示第一条记录
n 是指从第 m+1 条开始,取 n 条。
select * from tablename limit 2,4;
即取出第 3 条至第 6 条,共4 条记录
缺省用法:limit n;
取前n
案例1:按照薪资降序,取出排名在前5名的员工?
mysql> select ename,sal
-> from emp
-> order by sal desc
-> limit 5;
+-------+---------+
| ename | sal |
+-------+---------+
| KING | 5000.00 |
| SCOTT | 3000.00 |
| FORD | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+
5 rows in set (0.00 sec)
注意:mysql当中limit在order by之后执行
select
...
from
...
where
...
group by
...
having
...
order by
...
limit
...
执行顺序:
- from
- where
- group by
- having
- select
- order by
- limit…
案例2:取出工资排名在[3-5]名的员工?
mysql> select ename,sal from emp order by sal desc limit 2,3;
+-------+---------+
| ename | sal |
+-------+---------+
| FORD | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+
3 rows in set (0.00 sec)
6 表
6.1 创建表
create table 表名(字段名1 数据类型, 字段名2 数据类型, 字段名3 数据类型);
create table 表名(
字段名1 数据类型,
字段名2 数据类型,
字段名3 数据类型
);
表名:建议以t_ 或者 tbl_开始,可读性强。见名知意。
字段名:见名知意。
表名和字段名都属于标识符。
注意:数据库中的有一条命名规范:所有的标识符都是全部小写,单词和单词之间使用下划线进行衔接。
快速复制表:create table 表2 as select * from 表1;
6.1.1 MySQL常用数据类型
-
varchar(最长255)
可变长度的字符串
比较智能,节省空间。会根据实际的数据长度动态分配空间。
优点:节省空间
缺点:需要动态分配空间,速度慢。 -
char(最长255)
定长字符串
不管实际的数据长度是多少。
分配固定长度的空间去存储数据。使用不恰当的时候,可能会导致空间的浪费。
优点:不需要动态分配空间,速度快。
缺点:使用不当可能会导致空间的浪费。 -
int(最长11)
数字中的整数型。等同于java的int。 -
bigint
数字中的长整型。等同于java中的long。 -
float
单精度浮点型数据 -
double
双精度浮点型数据 -
date
短日期类型 -
datetime
长日期类型 -
clob
字符大对象,最多可以存储4G的字符串。比如:存储一篇文章,存储一个说明。
超过255个字符的都要采用CLOB字符大对象来存储。
Character Large OBject:CLOB -
blob
二进制大对象
Binary Large OBject
专门用来存储图片、声音、视频等流媒体数据。往BLOB类型的字段上插入数据的时候,例如插入一个图片、视频等,需要使用IO流才行。
例如:t_movie 电影表(存储电影信息)
编号 名字 故事情节 上映日期 时长 海报 类型
no(bigint) name(varchar) history(clob) playtime(date) time(double) image(blob) type(char)
------------------------------------------------------------------------------------------------------------------
10000 哪吒 ........... 2019-10-11 2.5 .... '1'
10001 林正英之娘娘 ........... 2019-11-11 1.5 .... '2'
....
6.1.2 创建学生表
#创建表
mysql> create table t_student(
-> no int,
-> name varchar(32),
-> sex char(1),
-> age int(3),
-> email varchar(255)
-> );
Query OK, 0 rows affected, 1 warning (0.16 sec)
#删除表,若表不存在则会报错
mysql> drop table t_student;
Query OK, 0 rows affected (0.05 sec)
#若表存在,删除表
mysql> drop table if exists t_student;
Query OK, 0 rows affected, 1 warning (0.01 sec)
6.2 增加/修改/删除表结构
采用 alter table
来增加/删除/修改表结构,不影响表中的数据
6.2.1 添加字段
mysql> alter table t_student add contact_tel varchar(40);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t_student;
+-------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| no | int | YES | | NULL | |
| name | varchar(32) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| age | int | YES | | NULL | |
| email | varchar(255) | YES | | NULL | |
| contact_tel | varchar(40) | YES | | NULL | |
+-------------+--------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
6.2.2 修改字段
1.修改数据类型modify
mysql> alter table t_student modify contact_tel varchar(41);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t_student;
+-------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| no | int | YES | | NULL | |
| name | varchar(32) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| age | int | YES | | NULL | |
| email | varchar(255) | YES | | NULL | |
| contact_tel | varchar(41) | YES | | NULL | |
+-------------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
2.修改字段名change
mysql> alter table t_student change contact_tel tel varchar(41);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t_student;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| no | int | YES | | NULL | |
| name | varchar(32) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| age | int | YES | | NULL | |
| email | varchar(255) | YES | | NULL | |
| tel | varchar(41) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
6.2.3 删除字段
mysql> alter table t_student drop tel;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t_student;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| no | int | YES | | NULL | |
| name | varchar(32) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| age | int | YES | | NULL | |
| email | varchar(255) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
6.3 添加/修改/删除数据
6.3.1 insert
语法格式:insert into 表名(字段名1,字段名2,字段名3...) values(值1,值2,值3..);
注意:
- 字段名和值要一一对应:数量要对应。数据类型要对应。
- 前面的字段名省略的话,等于都写上了!所以值也要都写上!如:
insert into t_student values(2, 'lisi', 'f', 20, 'lisi@123.com');
- insert可以一次插入多条数据,语法:
insert into t_user(字段名1,字段名2) values(),(),(),();
如:
insert into t_user(id,name,birth,create_time) values
(1,'zs','1980-10-11',now()),
(2,'lisi','1981-10-11',now()),
(3,'wangwu','1982-10-11',now());
例:
mysql> insert into t_student(no,name,sex,age,email) value(1,'zhangsan','m',20,'zhangsan@123.com');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t_student(email,name,sex,age,no) values('lisi@123.com','lisi','f',20,2);
Query OK, 1 row affected (0.01 sec)
mysql> insert into t_student(no) values(3);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t_student;
+------+----------+------+------+------------------+
| no | name | sex | age | email |
+------+----------+------+------+------------------+
| 1 | zhangsan | m | 20 | zhangsan@123.com |
| 2 | lisi | f | 20 | lisi@123.com |
| 3 | NULL | NULL | NULL | NULL |
+------+----------+------+------+------------------+
3 rows in set (0.00 sec)
6.3.2 update
update 表名 set 字段名1=值1,字段名2=值2,字段名3=值3... where 条件;
注意:没有条件限制会导致所有数据全部更新。
mysql> update t_student set name='jack' where no=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t_student;
+------+----------+------+------+------------------+
| no | name | sex | age | email |
+------+----------+------+------+------------------+
| 1 | zhangsan | m | 20 | zhangsan@123.com |
| 2 | jack | f | 20 | lisi@123.com |
| 3 | NULL | NULL | NULL | NULL |
+------+----------+------+------+------------------+
3 rows in set (0.00 sec)
6.3.3 delete
delete from 表名 where 条件;
注意:没有条件,整张表的数据会全部删除!
如:delete from t_student;
mysql> delete from t_student where no=2;
Query OK, 1 row affected (0.01 sec)
mysql> select * from t_student;
+------+----------+------+------+------------------+
| no | name | sex | age | email |
+------+----------+------+------+------------------+
| 1 | zhangsan | m | 20 | zhangsan@123.com |
| 3 | NULL | NULL | NULL | NULL |
+------+----------+------+------+------------------+
2 rows in set (0.00 sec)
6.3.4 日期
- str_to_date:将字符串varchar类型转换成date类型,格式为
str_to_date('字符串日期', '日期格式')
如:'01-10-1990’是varchar类型 - date_format:将date类型转换成具有一定格式的varchar字符串类型。格式为
date_format(日期类型数据, '日期格式')
这个函数通常使用在查询日期方面。设置展示的日期格式。
1.str_to_date
mysql的日期格式:
- %Y 年
- %m 月
- %d 日
- %h 时
- %i 分
- %s 秒
mysql> drop table if exists t_user;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> create table t_user(
-> id int,
-> name varchar(32),
-> birth date );
Query OK, 0 rows affected (0.03 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, 'zhangsan', str_to_date('01-10-1990','%d-%m-%Y'));
Query OK, 1 row affected (0.01 sec)
mysql> select * from t_user;
+------+----------+------------+
| id | name | birth |
+------+----------+------------+
| 1 | zhangsan | 1990-10-01 |
+------+----------+------------+
1 row in set (0.00 sec)
#如果提供的日期字符串是%Y-%m-%d格式,str_to_date函数就不需要了
mysql> insert into t_user(id,name,birth) values(2, 'lisi', '1990-10-01');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t_user;
+------+----------+------------+
| id | name | birth |
+------+----------+------------+
| 1 | zhangsan | 1990-10-01 |
| 2 | lisi | 1990-10-01 |
+------+----------+------------+
2 rows in set (0.00 sec)
2.date_format
mysql> select id,name,date_format(birth, '%m/%d/%Y') as birth from t_user;
+------+----------+------------+
| id | name | birth |
+------+----------+------------+
| 1 | zhangsan | 10/01/1990 |
| 2 | lisi | 10/01/1990 |
+------+----------+------------+
2 rows in set (0.00 sec)
6.3.5 date和datetime
-
date是短日期:只包括年月日信息。
-
datetime是长日期:包括年月日时分秒信息。
-
mysql短日期默认格式:%Y-%m-%d
-
mysql长日期默认格式:%Y-%m-%d %h:%i:%s
mysql> drop table if exists t_user;
Query OK, 0 rows affected (0.06 sec)
mysql> create table t_user(id int,name varchar(32),birth date,create_time datetime);
Query OK, 0 rows affected (0.03 sec)
mysql> desc t_user;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(32) | YES | | NULL | |
| birth | date | YES | | NULL | |
| create_time | datetime | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> insert into t_user(id,name,birth,create_time) values(1,'zhangsan','1990-10-01','2020-03-18 15:49:50');
Query OK, 1 row affected (0.01 sec)
mysql> select * from t_user;
+------+----------+------------+---------------------+
| id | name | birth | create_time |
+------+----------+------------+---------------------+
| 1 | zhangsan | 1990-10-01 | 2020-03-18 15:49:50 |
+------+----------+------------+---------------------+
1 row in set (0.00 sec)
mysql> insert into t_user(id,name,birth,create_time) values(2,'lisi','1991-10-01',now());
Query OK, 1 row affected (0.01 sec)
mysql> select * from t_user;
+------+----------+------------+---------------------+
| id | name | birth | create_time |
+------+----------+------------+---------------------+
| 1 | zhangsan | 1990-10-01 | 2020-03-18 15:49:50 |
| 2 | lisi | 1991-10-01 | 2021-10-27 17:14:16 |
+------+----------+------------+---------------------+
2 rows in set (0.00 sec)
mysql当中怎么获取系统当前时间:now()
,并且获取的时间带有时分秒信息,是datetime类型的。
6.4 删除表
drop table 表名;