本站文章均为 李华明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)
|