mysql数据库(七)

来源：互联网发布：js中undefined null 编辑：程序博客网时间：2024/06/05 00:50

1:创建数据表

create table t_student(
-> id int(10) primary key,
-> name varchar(32) not null,
-> birth date
-> );
Query OK, 0 rows affected (0.65 sec)

2：查看表结构

3：插入记录

insert into t_student(id,name,birth) values(1,'jack','2017-05-14');
Query OK, 1 row affected (0.14 sec)

4：查看数据表中的记录

select * from t_student;
+----+------+------------+
| id | name | birth |
+----+------+------------+
| 1 | jack | 2017-05-14 |
+----+------+------------+
1 row in set (0.00 sec)

5：str_to_date向数据表中插入记录（字段是varchar类型，需要将varchar类型转化为date类型）

insert into t_student(id,name,birth) value(2,'zhangsan','05-14-2017');
Query OK, 1 row affected, 1 warning (0.08 sec)

mysql> insert into t_student(id,name,birth) value(3,'lisi',str_to_date('05-14-2017'));
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'str_to_date'
mysql> insert into t_student(id,name,birth) value(3,'lisi',str_to_date('05-14-2017','%m-%d-%Y'));
Query OK, 1 row affected (0.07 sec)

mysql> select * from t_student;
+----+----------+------------+
| id | name | birth |
+----+----------+------------+
| 1 | jack | 2017-05-14 |
| 2 | zhangsan | 0000-00-00 |
| 3 | lisi | 2017-05-14 |
+----+----------+------------+
3 rows in set (0.00 sec)

6：date_format函数:将date类型转化为具有特定格式的字符串

select ename,date_format(hiredate,'%m-%d-%Y') from emp;
+---------+----------------------------------+
| ename | date_format(hiredate,'%m-%d-%Y') |
+---------+----------------------------------+
| SMITH | 05-13-2017 |
| ALLEN | 05-13-2017 |
| WARD | 05-13-2017 |
| JONES | 05-13-2017 |
| MARTIM | 05-13-2017 |
| BLAKE | 05-13-2017 |
| CLARK | 05-13-2017 |
| SCOLL | 05-13-2017 |
| KING | 05-13-2017 |
| IUSRNER | 05-13-2017 |
| ADAMS | 05-13-2017 |
| JAMES | 05-13-2017 |
| FORD | 05-13-2017 |
| MILLER | 05-13-2017 |
+---------+----------------------------------+
14 rows in set (0.06 sec)

7：查询员工的入职日期，以‘10/12/1980’的格式显示到窗口中

select ename,date_format(hiredate,'%m-%d-%Y') as hiredate from emp;
+---------+------------+
| ename | hiredate |
+---------+------------+
| SMITH | 05-13-2017 |
| ALLEN | 05-13-2017 |
| WARD | 05-13-2017 |
| JONES | 05-13-2017 |
| MARTIM | 05-13-2017 |
| BLAKE | 05-13-2017 |
| CLARK | 05-13-2017 |
| SCOLL | 05-13-2017 |
| KING | 05-13-2017 |
| IUSRNER | 05-13-2017 |
| ADAMS | 05-13-2017 |
| JAMES | 05-13-2017 |
| FORD | 05-13-2017 |
| MILLER | 05-13-2017 |
+---------+------------+
14 rows in set (0.00 sec)

8：mysql默认date类型为'%Y-%m-%d',date_format主要使用在查询语句中，客户需要日期以某种格式显示

select ename,hiredate from emp;
+---------+------------+
| ename | hiredate |
+---------+------------+
| SMITH | 2017-05-13 |
| ALLEN | 2017-05-13 |
| WARD | 2017-05-13 |
| JONES | 2017-05-13 |
| MARTIM | 2017-05-13 |
| BLAKE | 2017-05-13 |
| CLARK | 2017-05-13 |
| SCOLL | 2017-05-13 |
| KING | 2017-05-13 |
| IUSRNER | 2017-05-13 |
| ADAMS | 2017-05-13 |
| JAMES | 2017-05-13 |
| FORD | 2017-05-13 |
| MILLER | 2017-05-13 |
+---------+------------+
14 rows in set (0.00 sec)

mysql> select ename,date_format(hiredate,'%Y-%m-%d') as hiredate from emp;
+---------+------------+
| ename | hiredate |
+---------+------------+
| SMITH | 2017-05-13 |
| ALLEN | 2017-05-13 |
| WARD | 2017-05-13 |
| JONES | 2017-05-13 |
| MARTIM | 2017-05-13 |
| BLAKE | 2017-05-13 |
| CLARK | 2017-05-13 |
| SCOLL | 2017-05-13 |
| KING | 2017-05-13 |
| IUSRNER | 2017-05-13 |
| ADAMS | 2017-05-13 |
| JAMES | 2017-05-13 |
| FORD | 2017-05-13 |
| MILLER | 2017-05-13 |
+---------+------------+
14 rows in set (0.00 sec)

0 0