本站文章均为 李华明Himi 原创,转载务必在明显处注明：
转载自【黑米GameDev街区】 原文链接: http://www.himigame.com/mysql/778.html
通过上一篇的介绍，大家可以创建自己的数据库和表以及插入表中数据等等，本章继续介绍更多的数据库的相关操作；
1. 查看所有表单数据：（这里我直接使用上一篇创建的himiDB数据库与其中的people表进行讲解，还不太熟悉的请移步到上一篇的博文） 步骤：（获取）显示所有已存在的数据库->使用himidb数据库->（获取）显示所有表->（获取）显示所有表内的数据
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | himiDB | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec)
mysql> use himidb; Database changed mysql> show tables; +------------------+ | Tables_in_himidb | +------------------+ | people | +------------------+ 1 row in set (0.00 sec)
mysql> select *from people; +------------+------+----------+------+-----------+------------+ | name | mz | city | sex | birthday | deathday | +------------+------+----------+------+-----------+------------+ | himi | h | beijing | m | 1989-9-23 | NULL | | himi | h | beijing | m | 1989-9-23 | NULL | | himi3 | h | beijing | m | 1989-9-23 | NULL | | insertHimi | h | Anhui | m | 1989-9-23 | NULL | | tommy | m | chaoxian | w | 19890823 | 2100-10-10 | +------------+------+----------+------+-----------+------------+ 5 rows in set (0.00 sec) |
2. 假设我们修改people中的tommy 的生日为 1990-1-1日：
2.1：第一种方式可以通过txt进行，假设我们已经有一个txt存储了所有表内数据，并且tommy的生日在txt中已经是最新的了，那么我们就可以直接如下来进行更改（注意这种方式是删除以前所有表元素直接进行重新添加的操作！如果你没有之前这些数据就不要使用此方式）
|
1 2 |
mysql> DELETE FROM people; 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 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql> update people set birthday = '1990-1-1' where name = 'tommy'; Query OK, 1 row affected (0.11 sec) Rows matched: 1 Changed: 1 Warnings: 0
mysql> select *from people; +------------+------+----------+------+-----------+------------+ | name | mz | city | sex | birthday | deathday | +------------+------+----------+------+-----------+------------+ | himi | h | beijing | m | 1989-9-23 | NULL | | himi | h | beijing | m | 1989-9-23 | NULL | | himi3 | h | beijing | m | 1989-9-23 | NULL | | insertHimi | h | Anhui | m | 1989-9-23 | NULL | | tommy | m | chaoxian | w | 1990-1-1 | 2100-10-10 | +------------+------+----------+------+-----------+------------+ 5 rows in set (0.00 sec) |
3.查看特定条件所有数据：
|
1 2 3 4 5 6 7 8 |
mysql> select *from people where name='himi'; +------+------+---------+------+-----------+----------+ | name | mz | city | sex | birthday | deathday | +------+------+---------+------+-----------+----------+ | himi | h | beijing | m | 1989-9-23 | NULL | | himi | h | beijing | m | 1989-9-23 | NULL | +------+------+---------+------+-----------+----------+ 2 rows in set (0.08 sec) |
通过以上可以看出是在 select *from xx 后加入指向，比较容易理解；
继续查看哪些是大于1990年之前的额people：
|
1 2 3 4 5 6 7 8 9 10 |
mysql> select *from people where birthday <'1990-1-1'; +------------+------+---------+------+-----------+----------+ | name | mz | city | sex | birthday | deathday | +------------+------+---------+------+-----------+----------+ | himi | h | beijing | m | 1989-9-23 | NULL | | himi | h | beijing | m | 1989-9-23 | NULL | | himi3 | h | beijing | m | 1989-9-23 | NULL | | insertHimi | h | Anhui | m | 1989-9-23 | NULL | +------------+------+---------+------+-----------+----------+ 4 rows in set (0.01 sec) |
继续查看表中有几个woman：
|
1 2 3 4 5 6 7 |
mysql> select *from people where sex ="w"; +-------+------+----------+------+----------+------------+ | name | mz | city | sex | birthday | deathday | +-------+------+----------+------+----------+------------+ | tommy | m | chaoxian | w | 1990-1-1 | 2100-10-10 | +-------+------+----------+------+----------+------------+ 1 row in set (0.00 sec) |
继续查看表中既是woman又是在1990年以前的：（AND）
|
1 2 3 4 5 6 7 8 9 10 |
mysql> select *from people where sex ="m" and birthday < '1990-1-1'; +------------+------+---------+------+-----------+----------+ | name | mz | city | sex | birthday | deathday | +------------+------+---------+------+-----------+----------+ | himi | h | beijing | m | 1989-9-23 | NULL | | himi | h | beijing | m | 1989-9-23 | NULL | | himi3 | h | beijing | m | 1989-9-23 | NULL | | insertHimi | h | Anhui | m | 1989-9-23 | NULL | +------------+------+---------+------+-----------+----------+ 4 rows in set (0.00 sec) |
继续查看表中叫tommy 或者 是beijing地区的：(OR)
|
1 2 3 4 5 6 7 8 9 10 |
mysql> select *from people where city='beijing' or birthday <'1990-1-1'; +------------+------+---------+------+-----------+----------+ | name | mz | city | sex | birthday | deathday | +------------+------+---------+------+-----------+----------+ | himi | h | beijing | m | 1989-9-23 | NULL | | himi | h | beijing | m | 1989-9-23 | NULL | | himi3 | h | beijing | m | 1989-9-23 | NULL | | insertHimi | h | Anhui | m | 1989-9-23 | NULL | +------------+------+---------+------+-----------+----------+ 4 rows in set (0.00 sec) |
AND和OR可以混用，但AND比OR具有更高的优先级。如果使用两个操作符，使用圆括号进行分组~如下：
|
1 2 3 4 5 6 7 |
mysql> select *from people where (city='beijing' and sex='w') or (city ='chaoxian' and sex = 'w'); +-------+------+----------+------+----------+------------+ | name | mz | city | sex | birthday | deathday | +-------+------+----------+------+----------+------------+ | tommy | m | chaoxian | w | 1990-1-1 | 2100-10-10 | +-------+------+----------+------+----------+------------+ 1 row in set (0.00 sec) |
4.查看所有数据的特定条件：
假如我们需要查看所有人的性别 和名字：
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> select name,birthday from people; +------------+-----------+ | name | birthday | +------------+-----------+ | himi | 1989-9-23 | | himi | 1989-9-23 | | himi3 | 1989-9-23 | | insertHimi | 1989-9-23 | | tommy | 1990-1-1 | +------------+-----------+ 5 rows in set (0.00 sec) |
从上面的名字来看，发现了重复的名字，那么如果只想让同一名字只显示一个，可以使用 distinct 关键字；如下：
|
1 2 3 4 5 6 7 8 9 10 |
mysql> select distinct name from people; +------------+ | name | +------------+ | himi | | himi3 | | insertHimi | | tommy | +------------+ 4 rows in set (0.00 sec) |
来个复杂点的：使用一个WHERE子句结合行选择与列选择
|
1 2 3 4 5 6 |
mysql> select name from people where city='chaoxian' and birthday = '1990-1-1';+-------+ | name | +-------+ | tommy | +-------+ 1 row in set (0.00 sec) |
选择出的列根据生日进行排序显示(提前Himi这里又往表中添加了很多人)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> select birthday from people order by birthday; +-----------+ | birthday | +-----------+ | 1989-9-23 | | 1989-9-23 | | 1989-9-23 | | 1989-9-23 | | 1990-1-1 | | 1991-2-3 | | 1992-5-3 | +-----------+ 7 rows in set (0.00 sec) |
选择出的列根据名字降序进行排序显示：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> select name from people order by name desc; +------------+ | name | +------------+ | xiao | | tommy | | insertHimi | | himi3 | | himi | | himi | | chinle | +------------+ 7 rows in set (0.00 sec) |
5. 日起计算；
MySQL提供了几个函数，可以用来计算日期，例如，计算年龄或提取日期部分:
YEAR()提取日期的年部分，
RIGHT(x,y)提取 x 日期的MM-DD (日历年) 部分的最右面y个字符。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> select name ,birthday,curdate(),(year(curdate())-year(birthday))- (right(curdate(),5)<right(birthday,5)) from people; +------------+-----------+------------+--------------------------------------------------------------------------+ | name | birthday | curdate() | (year(curdate())-year(birthday))- (right(curdate(),5)<right(birthday,5)) | +------------+-----------+------------+--------------------------------------------------------------------------+ | himi | 1989-9-23 | 2012-04-17 | 23 | | himi | 1989-9-23 | 2012-04-17 | 23 | | himi3 | 1989-9-23 | 2012-04-17 | 23 | | insertHimi | 1989-9-23 | 2012-04-17 | 23 | | tommy | 1990-1-1 | 2012-04-17 | 22 | | xiao | 1991-2-3 | 2012-04-17 | 20 | | chinle | 1992-5-3 | 2012-04-17 | 19 | +------------+-----------+------------+--------------------------------------------------------------------------+ 7 rows in set (0.00 sec) |
下面我们算下死亡时间和当前时间的差值，其中我们排除deathday为NULL的人。
|
1 2 3 4 5 6 7 8 9 |
mysql> select name ,deathday,curdate(),(year(curdate())-year(deathday))- (right(curdate(),5)<right(deathday,5)) from people where deathday is not null; +--------+------------+------------+--------------------------------------------------------------------------+ | name | deathday | curdate() | (year(curdate())-year(deathday))- (right(curdate(),5)<right(deathday,5)) | +--------+------------+------------+--------------------------------------------------------------------------+ | tommy | 2100-10-10 | 2012-04-17 | -89 | | xiao | 1994-1-1 | 2012-04-17 | 17 | | chinle | 1994-1-1 | 2012-04-17 | 17 | +--------+------------+------------+--------------------------------------------------------------------------+ 3 rows in set (0.00 sec) |
MySQL提供几个日期部分的提取函数，例如YEAR( )、MONTH( )和DAYOFMONTH( )。在这里MONTH()是适合的函数。为了看它怎样工作，运行一个简单的查询:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> select name ,birthday, year(birthday),month(birthday) ,dayofmonth(birthday) from people; +------------+-----------+----------------+-----------------+----------------------+ | name | birthday | year(birthday) | month(birthday) | dayofmonth(birthday) | +------------+-----------+----------------+-----------------+----------------------+ | himi | 1989-9-23 | 1989 | 9 | 23 | | himi | 1989-9-23 | 1989 | 9 | 23 | | himi3 | 1989-9-23 | 1989 | 9 | 23 | | insertHimi | 1989-9-23 | 1989 | 9 | 23 | | tommy | 1990-1-1 | 1990 | 1 | 1 | | xiao | 1991-2-3 | 1991 | 2 | 3 | | chinle | 1992-5-3 | 1992 | 5 | 3 | +------------+-----------+----------------+-----------------+----------------------+ 7 rows in set (0.00 sec) |
练习：找到一个下个月是几月份：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> select name,birthday,month(birthday)+1 from people; +------------+-----------+-------------------+ | name | birthday | month(birthday)+1 | +------------+-----------+-------------------+ | himi | 1989-9-23 | 10 | | himi | 1989-9-23 | 10 | | himi3 | 1989-9-23 | 10 | | insertHimi | 1989-9-23 | 10 | | tommy | 1990-1-1 | 2 | | xiao | 1991-2-3 | 3 | | chinle | 1992-5-3 | 6 | +------------+-----------+-------------------+ 7 rows in set (0.00 sec) |