mysql使用03

mysql> #介绍datetime 和 timestamp的比较
mysql>
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| a              |
| employees      |
| t              |
| test01         |
| timetest       |
| tt             |
| updatetime     |
| z              |
+----------------+
8 rows in set (0.00 sec)

mysql> desc z;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sname | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> desc timetest;
+-------+-----------+------+-----+-------------------+-------+
| Field | Type      | Null | Key | Default           | Extra |
+-------+-----------+------+-----+-------------------+-------+
| a     | int(11)   | YES  |     | NULL              |       |
| b     | timestamp | NO   |     | CURRENT_TIMESTAMP |       |
+-------+-----------+------+-----+-------------------+-------+
2 rows in set (0.01 sec)

mysql> #我们使用timetest表
mysql> select * from timetest;
+------+---------------------+
| a    | b                   |
+------+---------------------+
|    1 | 2014-08-09 12:32:42 |
|    2 | 2014-08-09 12:32:47 |
|    3 | 2014-08-09 12:32:50 |
+------+---------------------+
3 rows in set (0.00 sec)

mysql> insert into(a) values(4);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(a) values(4)' at line 1
mysql> insert into timetest(a) values(4);
Query OK, 1 row affected (0.04 sec)

mysql> select * from timetest;
+------+---------------------+
| a    | b                   |
+------+---------------------+
|    1 | 2014-08-09 12:32:42 |
|    2 | 2014-08-09 12:32:47 |
|    3 | 2014-08-09 12:32:50 |
|    4 | 2014-08-10 12:19:02 |
+------+---------------------+
4 rows in set (0.00 sec)

mysql> update timetest set a = 3;
Query OK, 3 rows affected (0.05 sec)
Rows matched: 4  Changed: 3  Warnings: 0

mysql> select * from timetest;
+------+---------------------+
| a    | b                   |
+------+---------------------+
|    3 | 2014-08-09 12:32:42 |
|    3 | 2014-08-09 12:32:47 |
|    3 | 2014-08-09 12:32:50 |
|    3 | 2014-08-10 12:19:02 |
+------+---------------------+
4 rows in set (0.00 sec)

mysql> #这说明更新的时候 时间是不会进行更新的
mysql>
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| a              |
| employees      |
| t              |
| test01         |
| timetest       |
| tt             |
| updatetime     |
| z              |
+----------------+
8 rows in set (0.01 sec)

mysql> #现在我们来 看一下upfatetime表
mysql> desc updatetime;
+-------+-----------+------+-----+---------------------+-----------------------------+
| Field | Type      | Null | Key | Default             | Extra                       |
+-------+-----------+------+-----+---------------------+-----------------------------+
| a     | int(11)   | YES  |     | NULL                |                             |
| b     | timestamp | NO   |     | 0000-00-00 00:00:00 | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+---------------------+-----------------------------+
2 rows in set (0.00 sec)

mysql> #添加量 on update标志
mysql> select * from updatetime;
+------+---------------------+
| a    | b                   |
+------+---------------------+
|    4 | 2014-08-09 12:35:21 |
+------+---------------------+
1 row in set (0.00 sec)

mysql> update updatetime set a = 2;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> #change标志为1 说明已经更改
mysql> select * from updatetime;
+------+---------------------+
| a    | b                   |
+------+---------------------+
|    2 | 2014-08-10 12:21:50 |
+------+---------------------+
1 row in set (0.00 sec)

mysql> #现在来看一下yer\ar
mysql> create table yeartest (
    -> a year(2),
    -> b year(4))engine=innodb;
Query OK, 0 rows affected, 1 warning (0.08 sec)

mysql> insert into yeartest values ('1992','1993');
Query OK, 1 row affected (0.03 sec)

mysql> select * from yeartest;
+------+------+
| a    | b    |
+------+------+
|   92 | 1993 |
+------+------+
1 row in set (0.01 sec)

mysql> #说明此时进行了截取字符串的操作
mysql> #time类型 就不介绍了  可以查看相关的mysql资料
mysql>
mysql> #与日期和时间相关的函数
mysql>
mysql> select now(), current_timestamp(), sysdate();
+---------------------+---------------------+---------------------+
| now()               | current_timestamp() | sysdate()           |
+---------------------+---------------------+---------------------+
| 2014-08-10 12:26:39 | 2014-08-10 12:26:39 | 2014-08-10 12:26:39 |
+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> #可以 看出都是一样的 有什么区别呢？
mysql> #now() 和 current_timestamp()的语义是相同的 表示执行当前指令(sql语句)的时间，而sysdate()表示执行到当前函数时的时间
mysql>
mysql> select now(), now()+0;
+---------------------+-----------------------+
| now()               | now()+0               |
+---------------------+-----------------------+
| 2014-08-10 12:29:57 | 20140810122957.000000 |
+---------------------+-----------------------+
1 row in set (0.00 sec)

mysql> select now() as today,
    -> date_add(now(), interval 1 day) as tomorrow,
    -> date_add(now(), interval -1 day) as yesterday;
+---------------------+---------------------+---------------------+
| today               | tomorrow            | yesterday           |
+---------------------+---------------------+---------------------+
| 2014-08-10 12:31:29 | 2014-08-11 12:31:29 | 2014-08-09 12:31:29 |
+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> select now() as now,
    -> date_add(now(), interval 1 hour) as ont_hour_later;
+---------------------+---------------------+
| now                 | ont_hour_later      |
+---------------------+---------------------+
| 2014-08-10 12:34:16 | 2014-08-10 13:34:16 |
+---------------------+---------------------+
1 row in set (0.00 sec)

mysql>
mysql> #时间格式化函数 date_format
mysql> select date_format(now(), '%y-%m-%d') as datetime;
+----------+
| datetime |
+----------+
| 14-08-10 |
+----------+
1 row in set (0.01 sec)

mysql> select date_format(now(), '%y---%m---%d') as datetime;
+--------------+
| datetime     |
+--------------+
| 14---08---10 |
+--------------+
1 row in set (0.00 sec)

mysql>
mysql> #现在介绍下生日问题   使用mysql自带的员工数据表
mysql> #表的导入方法  source 源文件的路径
mysql>
mysql> select * from employees limit 20;
+-------+------------+------------+-------------+------+------------+
| id    | birth_date | first_name | last_name   | sex  | worktime   |
+-------+------------+------------+-------------+------+------------+
| 10001 | 1953-09-02 | Georgi     | Facello     | M    | 1986-06-26 |
| 10002 | 1964-06-02 | Bezalel    | Simmel      | F    | 1985-11-21 |
| 10003 | 1959-12-03 | Parto      | Bamford     | M    | 1986-08-28 |
| 10004 | 1954-05-01 | Chirstian  | Koblick     | M    | 1986-12-01 |
| 10005 | 1955-01-21 | Kyoichi    | Maliniak    | M    | 1989-09-12 |
| 10006 | 1953-04-20 | Anneke     | Preusig     | F    | 1989-06-02 |
| 10007 | 1957-05-23 | Tzvetan    | Zielinski   | F    | 1989-02-10 |
| 10008 | 1958-02-19 | Saniya     | Kalloufi    | M    | 1994-09-15 |
| 10009 | 1952-04-19 | Sumant     | Peac        | F    | 1985-02-18 |
| 10010 | 1963-06-01 | Duangkaew  | Piveteau    | F    | 1989-08-24 |
| 10011 | 1953-11-07 | Mary       | Sluis       | F    | 1990-01-22 |
| 10012 | 1960-10-04 | Patricio   | Bridgland   | M    | 1992-12-18 |
| 10013 | 1963-06-07 | Eberhardt  | Terkki      | M    | 1985-10-20 |
| 10014 | 1956-02-12 | Berni      | Genin       | M    | 1987-03-11 |
| 10015 | 1959-08-19 | Guoxiang   | Nooteboom   | M    | 1987-07-02 |
| 10016 | 1961-05-02 | Kazuhito   | Cappelletti | M    | 1995-01-27 |
| 10017 | 1958-07-06 | Cristinel  | Bouloucos   | F    | 1993-08-03 |
| 10018 | 1954-06-19 | Kazuhide   | Peha        | F    | 1987-04-03 |
| 10019 | 1953-01-23 | Lillian    | Haddadi     | M    | 1999-04-30 |
| 10020 | 1952-12-24 | Mayuko     | Warwick     | M    | 1991-01-26 |
+-------+------------+------------+-------------+------+------------+
20 rows in set (0.00 sec)

mysql> insert into employees select 10021,'1952-12-21','Jack','Lee','M','1999-01-01';
ERROR 1062 (23000): Duplicate entry '10021' for key 'PRIMARY'
mysql> insert into employees select 100021,'1952-12-21','Jack','Lee','M','1999-01-01';
ERROR 1062 (23000): Duplicate entry '100021' for key 'PRIMARY'
mysql> insert into employees select 1000021,'1952-12-21','Jack','Lee','M','1999-01-01';
Query OK, 1 row affected (0.07 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> #好了  不用原来的表量  从新建立一个
mysql>
mysql> create table new_emp like employees;
Query OK, 0 rows affected (0.08 sec)

mysql> insert into new_emp select * from employees limit 10;
Query OK, 10 rows affected (0.04 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> select * from new_emp;
+-------+------------+------------+-----------+------+------------+
| id    | birth_date | first_name | last_name | sex  | worktime   |
+-------+------------+------------+-----------+------+------------+
| 10001 | 1953-09-02 | Georgi     | Facello   | M    | 1986-06-26 |
| 10002 | 1964-06-02 | Bezalel    | Simmel    | F    | 1985-11-21 |
| 10003 | 1959-12-03 | Parto      | Bamford   | M    | 1986-08-28 |
| 10004 | 1954-05-01 | Chirstian  | Koblick   | M    | 1986-12-01 |
| 10005 | 1955-01-21 | Kyoichi    | Maliniak  | M    | 1989-09-12 |
| 10006 | 1953-04-20 | Anneke     | Preusig   | F    | 1989-06-02 |
| 10007 | 1957-05-23 | Tzvetan    | Zielinski | F    | 1989-02-10 |
| 10008 | 1958-02-19 | Saniya     | Kalloufi  | M    | 1994-09-15 |
| 10009 | 1952-04-19 | Sumant     | Peac      | F    | 1985-02-18 |
| 10010 | 1963-06-01 | Duangkaew  | Piveteau  | F    | 1989-08-24 |
+-------+------------+------------+-----------+------+------------+
10 rows in set (0.00 sec)

mysql> #现在插入一条记录
mysql> insert into new_emp select 10011,'1952-09-09','Jack','Lee','M','1999-01-01';
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from new_emp where id = 10011;
+-------+------------+------------+-----------+------+------------+
| id    | birth_date | first_name | last_name | sex  | worktime   |
+-------+------------+------------+-----------+------+------------+
| 10011 | 1952-09-09 | Jack       | Lee       | M    | 1999-01-01 |
+-------+------------+------------+-----------+------+------------+
1 row in set (0.00 sec)

mysql> select concat(first_name, ' ', last_name) as name birth_date as
    -> Birthday from new_emp;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'birth_date as
Birthday from new_emp' at line 1
mysql> select concat(first_name, ' ', last_name) as name, birth_date as Birthday from new_emp;
+--------------------+------------+
| name               | Birthday   |
+--------------------+------------+
| Georgi Facello     | 1953-09-02 |
| Bezalel Simmel     | 1964-06-02 |
| Parto Bamford      | 1959-12-03 |
| Chirstian Koblick  | 1954-05-01 |
| Kyoichi Maliniak   | 1955-01-21 |
| Anneke Preusig     | 1953-04-20 |
| Tzvetan Zielinski  | 1957-05-23 |
| Saniya Kalloufi    | 1958-02-19 |
| Sumant Peac        | 1952-04-19 |
| Duangkaew Piveteau | 1963-06-01 |
| Jack Lee           | 1952-09-09 |
+--------------------+------------+
11 rows in set (0.00 sec)

mysql> insert into new_emp select 10011,'1952-09-09','Smith','Lee','M','1992-02-29';
ERROR 1062 (23000): Duplicate entry '10011' for key 'PRIMARY'
mysql> insert into new_emp select 10012,'1952-09-09','Smith','Lee','M','1992-02-29';
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> #现在插入了一个smith的家伙  但是现在公司年会 要讨论下一年头三月的生日的人的日期
mysql> #现在发现问题来了
mysql> #smith的下一年的生日(oh  插错了)
mysql> insert into new_emp select 10013,'1992-02-29','Smith','Lee','M','1989-02-29';
Query OK, 1 row affected, 1 warning (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 1

mysql> #现在 他的生日写成1993-02-29?????
mysql> select name, birthday,
    -> if (cur>today, cur, next) as birth_day
    -> from
    -> (
    -> select name, birthday, today,
    -> date_add(cur, interval if(day(birthday)=29
    -> && day(cur)=28,1,0) day) as cur,
    -> date_add(next, interval if(day(birthday)=29
    -> && day(next)=28,1,0) day) as next
    -> from
    -> (
    -> select name, birthday, today,
    -> date_add(birthday, interval diff year) as cur,
    -> date_add(birthday, interval diff+1 year) as next
    -> from (select concat(first_name,' ', last_name) as name,
    -> birth_date as birthday,
    -> (
    -> year(now())-year(birth_date)) as diff,
    -> now() as today
    -> from new_emp
    -> ) as a
    -> ) as b
    -> )as c;
+--------------------+------------+------------+
| name               | birthday   | birth_day  |
+--------------------+------------+------------+
| Georgi Facello     | 1953-09-02 | 2014-09-02 |
| Bezalel Simmel     | 1964-06-02 | 2015-06-02 |
| Parto Bamford      | 1959-12-03 | 2014-12-03 |
| Chirstian Koblick  | 1954-05-01 | 2015-05-01 |
| Kyoichi Maliniak   | 1955-01-21 | 2015-01-21 |
| Anneke Preusig     | 1953-04-20 | 2015-04-20 |
| Tzvetan Zielinski  | 1957-05-23 | 2015-05-23 |
| Saniya Kalloufi    | 1958-02-19 | 2015-02-19 |
| Sumant Peac        | 1952-04-19 | 2015-04-19 |
| Duangkaew Piveteau | 1963-06-01 | 2015-06-01 |
| Jack Lee           | 1952-09-09 | 2014-09-09 |
| Smith Lee          | 1952-09-09 | 2014-09-09 |
| Smith Lee          | 1992-02-29 | 2015-03-01 |
+--------------------+------------+------------+
13 rows in set (0.00 sec)

mysql> #依据上面的写法即可  最后一句改变了数据的值 没有变为2-29
mysql> #那么 现在公司年会要找出下一年头三月的，看以下语句
mysql>
mysql> select name, birthday,
    -> if (cur>today, cur, next) as birth_day
    -> from
    -> (
    -> select name, birthday, today,
    -> date_add(cur, interval if(day(birthday)=29 && day(cur)=28,1,0) day) as cur,
    -> date_add(next, interval if(day(birthday)=29 && day(next)=28,1,0) day) as next
    -> from
    -> (
    -> select name, birthday, today,
    -> date_add(birthday, interval 0 year) as cur,
    -> date_add(birthday, interval 1 year) as next
    -> from
    -> (
    -> select concat(first_name,' ', last_name) as name,
    -> birth_date as birthday,
    -> now() as today
    -> from new_emp
    -> ) as a
    -> ) as b
    -> )as c;
+--------------------+------------+------------+
| name               | birthday   | birth_day  |
+--------------------+------------+------------+
| Georgi Facello     | 1953-09-02 | 1954-09-02 |
| Bezalel Simmel     | 1964-06-02 | 1965-06-02 |
| Parto Bamford      | 1959-12-03 | 1960-12-03 |
| Chirstian Koblick  | 1954-05-01 | 1955-05-01 |
| Kyoichi Maliniak   | 1955-01-21 | 1956-01-21 |
| Anneke Preusig     | 1953-04-20 | 1954-04-20 |
| Tzvetan Zielinski  | 1957-05-23 | 1958-05-23 |
| Saniya Kalloufi    | 1958-02-19 | 1959-02-19 |
| Sumant Peac        | 1952-04-19 | 1953-04-19 |
| Duangkaew Piveteau | 1963-06-01 | 1964-06-01 |
| Jack Lee           | 1952-09-09 | 1953-09-09 |
| Smith Lee          | 1952-09-09 | 1953-09-09 |
| Smith Lee          | 1992-02-29 | 1993-03-01 |
+--------------------+------------+------------+
13 rows in set (0.00 sec)

mysql> #现在来分析下这个查找过程
mysql> #从临时表 a 开始
mysql> #主要是进行姓名的链接 birthday的 today的别名建立
mysql> #第二个临时表b 将当前的生日别名为cur,下一年为next,这时候表中有五个字段，name， birthday, today,cur,next