【MySQL数据库开发之三】MySQL 获得数据库和表操作！

通过上一篇的介绍，大家可以创建自己的数据库和表以及插入表中数据等等，本章继续介绍更多的数据库的相关操作；

  1.  查看所有表单数据：（这里我直接使用上一篇创建的himiDB数据库与其中的people表进行讲解，还不太熟悉的请移步到上一篇的博文）   步骤：（获取）显示所有已存在的数据库->使用himidb数据库->（获取）显示所有表->（获取）显示所有表内的数据

1. mysql> show databases; 
2. +--------------------+ 
3. | Database           | 
4. +--------------------+ 
5. | information_schema | 
6. | himiDB             | 
7. | mysql              | 
8. | performance_schema | 
9. | test               | 
10. +--------------------+ 
11. 5 rows in set (0.00 sec) 
12.   
13. mysql> use himidb; 
14. Database changed 
15. mysql> show tables; 
16. +------------------+ 
17. | Tables_in_himidb | 
18. +------------------+ 
19. | people           | 
20. +------------------+ 
21. 1 row in set (0.00 sec) 
22.   
23. mysql> select *from people; 
24. +------------+------+----------+------+-----------+------------+ 
25. | name       | mz   | city     | sex  | birthday  | deathday   | 
26. +------------+------+----------+------+-----------+------------+ 
27. | himi       | h    | beijing  | m    | 1989-9-23 | NULL       | 
28. | himi       | h    | beijing  | m    | 1989-9-23 | NULL       | 
29. | himi3      | h    | beijing  | m    | 1989-9-23 | NULL       | 
30. | insertHimi | h    | Anhui    | m    | 1989-9-23 | NULL       | 
31. | tommy      | m    | chaoxian | w    | 19890823  | 2100-10-10 | 
32. +------------+------+----------+------+-----------+------------+ 
33. 5 rows in set (0.00 sec) 

2. 假设我们修改people中的tommy 的生日为  1990-1-1日：

2.1：第一种方式可以通过txt进行，假设我们已经有一个txt存储了所有表内数据，并且tommy的生日在txt中已经是最新的了，那么我们就可以直接如下来进行更改（注意这种方式是删除以前所有表元素直接进行重新添加的操作！如果你没有之前这些数据就不要使用此方式）

1. mysql> DELETE FROM people; 
2. mysql> LOAD DATA LOCAL INFILE '/xxx/xxx/people.txt' INTO TABLE people; 

2.2：使用UPDATE进行指向性修改；

语句形式： update xx(表名) set xx(item名)   =’xxx新value’ where xx(item索引) =”where中item索引的对应value”;

示例：

1. mysql> update people set birthday = '1990-1-1' where name = 'tommy'; 
2. Query OK, 1 row affected (0.11 sec) 
3. Rows matched: 1  Changed: 1  Warnings: 0 
4.   
5. mysql> select *from people; 
6. +------------+------+----------+------+-----------+------------+ 
7. | name       | mz   | city     | sex  | birthday  | deathday   | 
8. +------------+------+----------+------+-----------+------------+ 
9. | himi       | h    | beijing  | m    | 1989-9-23 | NULL       | 
10. | himi       | h    | beijing  | m    | 1989-9-23 | NULL       | 
11. | himi3      | h    | beijing  | m    | 1989-9-23 | NULL       | 
12. | insertHimi | h    | Anhui    | m    | 1989-9-23 | NULL       | 
13. | tommy      | m    | chaoxian | w    | 1990-1-1  | 2100-10-10 | 
14. +------------+------+----------+------+-----------+------------+ 
15. 5 rows in set (0.00 sec) 

1. mysql> select *from people where name='himi'; 
2. +------+------+---------+------+-----------+----------+ 
3. | name | mz   | city    | sex  | birthday  | deathday | 
4. +------+------+---------+------+-----------+----------+ 
5. | himi | h    | beijing | m    | 1989-9-23 | NULL     | 
6. | himi | h    | beijing | m    | 1989-9-23 | NULL     | 
7. +------+------+---------+------+-----------+----------+ 
8. 2 rows in set (0.08 sec) 

通过以上可以看出是在 select *from xx 后加入指向，比较容易理解；

 继续查看哪些是大于1990年之前的额people：

1. mysql> select *from people where birthday <'1990-1-1'; 
2. +------------+------+---------+------+-----------+----------+ 
3. | name       | mz   | city    | sex  | birthday  | deathday | 
4. +------------+------+---------+------+-----------+----------+ 
5. | himi       | h    | beijing | m    | 1989-9-23 | NULL     | 
6. | himi       | h    | beijing | m    | 1989-9-23 | NULL     | 
7. | himi3      | h    | beijing | m    | 1989-9-23 | NULL     | 
8. | insertHimi | h    | Anhui   | m    | 1989-9-23 | NULL     | 
9. +------------+------+---------+------+-----------+----------+ 
10. 4 rows in set (0.01 sec) 

1. mysql> select *from people where sex ="w"; 
2. +-------+------+----------+------+----------+------------+ 
3. | name  | mz   | city     | sex  | birthday | deathday   | 
4. +-------+------+----------+------+----------+------------+ 
5. | tommy | m    | chaoxian | w    | 1990-1-1 | 2100-10-10 | 
6. +-------+------+----------+------+----------+------------+ 
7. 1 row in set (0.00 sec) 

1. mysql> select *from people where sex ="m" and birthday < '1990-1-1'; 
2. +------------+------+---------+------+-----------+----------+ 
3. | name       | mz   | city    | sex  | birthday  | deathday | 
4. +------------+------+---------+------+-----------+----------+ 
5. | himi       | h    | beijing | m    | 1989-9-23 | NULL     | 
6. | himi       | h    | beijing | m    | 1989-9-23 | NULL     | 
7. | himi3      | h    | beijing | m    | 1989-9-23 | NULL     | 
8. | insertHimi | h    | Anhui   | m    | 1989-9-23 | NULL     | 
9. +------------+------+---------+------+-----------+----------+ 
10. 4 rows in set (0.00 sec) 

1. mysql> select *from people where city='beijing' or birthday <'1990-1-1'; 
2. +------------+------+---------+------+-----------+----------+ 
3. | name       | mz   | city    | sex  | birthday  | deathday | 
4. +------------+------+---------+------+-----------+----------+ 
5. | himi       | h    | beijing | m    | 1989-9-23 | NULL     | 
6. | himi       | h    | beijing | m    | 1989-9-23 | NULL     | 
7. | himi3      | h    | beijing | m    | 1989-9-23 | NULL     | 
8. | insertHimi | h    | Anhui   | m    | 1989-9-23 | NULL     | 
9. +------------+------+---------+------+-----------+----------+ 
10. 4 rows in set (0.00 sec) 

1. mysql> select *from people where (city='beijing' and sex='w') or (city ='chaoxian' and sex = 'w'); 
2. +-------+------+----------+------+----------+------------+ 
3. | name  | mz   | city     | sex  | birthday | deathday   | 
4. +-------+------+----------+------+----------+------------+ 
5. | tommy | m    | chaoxian | w    | 1990-1-1 | 2100-10-10 | 
6. +-------+------+----------+------+----------+------------+ 
7. 1 row in set (0.00 sec) 

4.查看所有数据的特定条件：

假如我们需要查看所有人的性别 和名字：

1. mysql> select name,birthday from people; 
2. +------------+-----------+ 
3. | name       | birthday  | 
4. +------------+-----------+ 
5. | himi       | 1989-9-23 | 
6. | himi       | 1989-9-23 | 
7. | himi3      | 1989-9-23 | 
8. | insertHimi | 1989-9-23 | 
9. | tommy      | 1990-1-1  | 
10. +------------+-----------+ 
11. 5 rows in set (0.00 sec) 

1. mysql> select distinct name from people; 
2. +------------+ 
3. | name       | 
4. +------------+ 
5. | himi       | 
6. | himi3      | 
7. | insertHimi | 
8. | tommy      | 
9. +------------+ 
10. 4 rows in set (0.00 sec) 

1. mysql> select name from people where city='chaoxian' and birthday = '1990-1-1';+-------+ 
2. | name  | 
3. +-------+ 
4. | tommy | 
5. +-------+ 
6. 1 row in set (0.00 sec) 

1. mysql> select birthday from people order by birthday; 
2. +-----------+ 
3. | birthday  | 
4. +-----------+ 
5. | 1989-9-23 | 
6. | 1989-9-23 | 
7. | 1989-9-23 | 
8. | 1989-9-23 | 
9. | 1990-1-1  | 
10. | 1991-2-3  | 
11. | 1992-5-3  | 
12. +-----------+ 
13. 7 rows in set (0.00 sec) 

1. mysql> select name from people order by name desc; 
2. +------------+ 
3. | name       | 
4. +------------+ 
5. | xiao       | 
6. | tommy      | 
7. | insertHimi | 
8. | himi3      | 
9. | himi       | 
10. | himi       | 
11. | chinle     | 
12. +------------+ 
13. 7 rows in set (0.00 sec) 

5. 日起计算；

MySQL提供了几个函数，可以用来计算日期，例如，计算年龄或提取日期部分:

YEAR()提取日期的年部分，

RIGHT(x,y)提取 x 日期的MM-DD (日历年)  部分的最右面y个字符。

1. mysql> select name ,birthday,curdate(),(year(curdate())-year(birthday))- (right(curdate(),5)<right(birthday,5)) from people; 
2. +------------+-----------+------------+--------------------------------------------------------------------------+ 
3. | name       | birthday  | curdate()  | (year(curdate())-year(birthday))- (right(curdate(),5)<right(birthday,5)) | 
4. +------------+-----------+------------+--------------------------------------------------------------------------+ 
5. | himi       | 1989-9-23 | 2012-04-17 |                                                                       23 | 
6. | himi       | 1989-9-23 | 2012-04-17 |                                                                       23 | 
7. | himi3      | 1989-9-23 | 2012-04-17 |                                                                       23 | 
8. | insertHimi | 1989-9-23 | 2012-04-17 |                                                                       23 | 
9. | tommy      | 1990-1-1  | 2012-04-17 |                                                                       22 | 
10. | xiao       | 1991-2-3  | 2012-04-17 |                                                                       20 | 
11. | chinle     | 1992-5-3  | 2012-04-17 |                                                                       19 | 
12. +------------+-----------+------------+--------------------------------------------------------------------------+ 
13. 7 rows in set (0.00 sec) 

1. mysql> select name ,deathday,curdate(),(year(curdate())-year(deathday))- (right(curdate(),5)<right(deathday,5)) from people where deathday is not null; 
2. +--------+------------+------------+--------------------------------------------------------------------------+ 
3. | name   | deathday   | curdate()  | (year(curdate())-year(deathday))- (right(curdate(),5)<right(deathday,5)) | 
4. +--------+------------+------------+--------------------------------------------------------------------------+ 
5. | tommy  | 2100-10-10 | 2012-04-17 |                                                                      -89 | 
6. | xiao   | 1994-1-1   | 2012-04-17 |                                                                       17 | 
7. | chinle | 1994-1-1   | 2012-04-17 |                                                                       17 | 
8. +--------+------------+------------+--------------------------------------------------------------------------+ 
9. 3 rows in set (0.00 sec) 

1. mysql> select name ,birthday, year(birthday),month(birthday) ,dayofmonth(birthday) from people; 
2. +------------+-----------+----------------+-----------------+----------------------+ 
3. | name       | birthday  | year(birthday) | month(birthday) | dayofmonth(birthday) | 
4. +------------+-----------+----------------+-----------------+----------------------+ 
5. | himi       | 1989-9-23 |           1989 |               9 |                   23 | 
6. | himi       | 1989-9-23 |           1989 |               9 |                   23 | 
7. | himi3      | 1989-9-23 |           1989 |               9 |                   23 | 
8. | insertHimi | 1989-9-23 |           1989 |               9 |                   23 | 
9. | tommy      | 1990-1-1  |           1990 |               1 |                    1 | 
10. | xiao       | 1991-2-3  |           1991 |               2 |                    3 | 
11. | chinle     | 1992-5-3  |           1992 |               5 |                    3 | 
12. +------------+-----------+----------------+-----------------+----------------------+ 
13. 7 rows in set (0.00 sec) 

1. mysql> select name,birthday,month(birthday)+1 from people; 
2. +------------+-----------+-------------------+ 
3. | name       | birthday  | month(birthday)+1 | 
4. +------------+-----------+-------------------+ 
5. | himi       | 1989-9-23 |                10 | 
6. | himi       | 1989-9-23 |                10 | 
7. | himi3      | 1989-9-23 |                10 | 
8. | insertHimi | 1989-9-23 |                10 | 
9. | tommy      | 1990-1-1  |                 2 | 
10. | xiao       | 1991-2-3  |                 3 | 
11. | chinle     | 1992-5-3  |                 6 | 
12. +------------+-----------+-------------------+ 
13. 7 rows in set (0.00 sec)