1,select查询的基本结构
1select 字段
2 from 表
3 where 过滤条件
4 group by 分组条件
5 order by 排序条件
6 having 过滤的第二条件
7 limit 限定结果条件;
2,最简单的查询语句
8 mysql> select * from user;
9 +----+----------+----------+---------------------+
10 id username password createtime
11 +----+----------+----------+---------------------+
12 1 kenan kenan 2012-10-31 15:32:26
13 3 kenan lele 2012-10-31 15:32:26
14 +----+----------+----------+---------------------+
3，查询一列或多列
15 mysql> select username,createtime from user;
16 +----------+---------------------+
17 username createtime
18 +----------+---------------------+
19 kenan 2012-10-31 15:32:26
20 kenan 2012-10-31 15:32:26
21 +----------+---------------------+
22 2 rows in set (0.00 sec)
23 4,多表查询
mysql> select * from user;
24 +----+----------+----------+---------------------+
25 id username password createtime
26 +----+----------+----------+---------------------+
27 1 kenan kenan 2012-10-31 15:32:26
28 3 kenan lele 2012-10-31 15:32:26
29 +----+----------+----------+---------------------+
30 2 rows in set (0.00 sec)
31
32 mysql> select * from teacher;
33 +----+------------+---------------------+
34 id username createtime
35 +----+------------+---------------------+
36 1 Teacher Li 2012-11-01 21:00:58
37 2 Teacher Le 2012-11-01 21:02:07
38 +----+------------+---------------------+
39 2 rows in set (0.00 sec)
40
41 mysql> select * from user,teacher;
42 +----+----------+----------+---------------------+----+------------+---------------------+
43 id username password createtime id username createtime
44 +----+----------+----------+---------------------+----+------------+---------------------+
45 1 kenan kenan 2012-10-31 15:32:26 1 Teacher Li 2012-11-01 21:00:58
46 3 kenan lele 2012-10-31 15:32:26 1 Teacher Li 2012-11-01 21:00:58
47 1 kenan kenan 2012-10-31 15:32:26 2 Teacher Le 2012-11-01 21:02:07
48 3 kenan lele 2012-10-31 15:32:26 2 Teacher Le 2012-11-01 21:02:07
49 +----+----------+----------+---------------------+----+------------+---------------------+
50 4 rows in set (0.00 sec)
51
52 多表查询中，如果没有限制条件的话，两个表的记录会分别进行匹配,产生的结果就是两个表的笛卡尔积，叫做全连接
53 下面的这个带有条件的叫做等同连接，相当于在上面的查询结果中挑选出来满足where后面条件的记录，因为没有所有是空的结果。
54 mysql> select * from user,teacher where user.username = teacher.username;
55 Empty set (0.00 sec)
5,带有简单的where字句条件查询
56 mysql> select * from user where id>2;
57 +----+----------+----------+---------------------+
58 id username password createtime
59 +----+----------+----------+---------------------+
60 3 kenan lele 2012-10-31 15:32:26
61 +----+----------+----------+---------------------+
62 1 row in set (0.00 sec)
63 6,带有in的查询，in用来表示范围
mysql> select * from user where username in ('kenan','Micheal');
64 +----+----------+----------+---------------------+
65 id username password createtime
66 +----+----------+----------+---------------------+
67 1 kenan kenan 2012-10-31 15:32:26
68 3 kenan lele 2012-10-31 15:32:26
69 +----+----------+----------+---------------------+
70 2 rows in set (0.00 sec)
这里查询出来username是kenan或者是Micheal的记录
71
7,between and 用来表示一个字段的范围的查询
mysql> select * from user where id between 2 and 3;
72 +----+----------+----------+---------------------+
73 id username password createtime
74 +----+----------+----------+---------------------+
75 3 kenan lele 2012-10-31 15:32:26
76 +----+----------+----------+---------------------+
77 1 row in set (0.00 sec)
78
79 mysql> select * from user where id between 3 and 4;
80 +----+----------+----------+---------------------+
81 id username password createtime
82 +----+----------+----------+---------------------+
83 3 kenan lele 2012-10-31 15:32:26
84 +----+----------+----------+---------------------+
85 1 row in set (0.00 sec)
这里分别查询出来id在2和3之间的记录，3到4之间的记录，从结果可以看出 between and是包含边界的，就是>=2而且<=3
86
8, is null 查询空值
mysql> select * from user ;
87 +----+----------+----------+---------------------+
88 id username password createtime
89 +----+----------+----------+---------------------+
90 1 kenan kenan 2012-10-31 15:32:26
91 3 kenan lele 2012-10-31 15:32:26
92 4 lele lele NULL
93 +----+----------+----------+---------------------+
94 3 rows in set (0.00 sec)
95
96 mysql> select * from user where createtime is null;
97 +----+----------+----------+------------+
98 id username password createtime
99 +----+----------+----------+------------+
100 4 lele lele NULL
101 +----+----------+----------+------------+
102 1 row in set (0.00 sec)
9,用and连接多条件查询 并关系
103 mysql> select * from user where username = 'kenan' and password = 'kenan';
104 +----+----------+----------+---------------------+
105 id username password createtime
106 +----+----------+----------+---------------------+
107 1 kenan kenan 2012-10-31 15:32:26
108 +----+----------+----------+---------------------+
109 1 row in set (0.00 sec)
10，用or 的多条件查询 这个是或关系，满足一个条件即可
110 mysql> select * from user where username = 'kenan' or password = 'kenan';
111 +----+----------+----------+---------------------+
112 id username password createtime
113 +----+----------+----------+---------------------+
114 1 kenan kenan 2012-10-31 15:32:26
115 3 kenan lele 2012-10-31 15:32:26
116 +----+----------+----------+---------------------+
117 2 rows in set (0.00 sec)
11,聚合函数 count（）用来统计总数
118 mysql> select count(*) from user;
119 +----------+
120 count(*)
121 +----------+
122 3
123 +----------+
124 1 row in set (0.00 sec)
12，使用sum（）求和，使用avg函数求平均值
125 mysql> select id from user;
126 +----+
127 id
128 +----+
129 1
130 3
131 4
132 +----+
133 3 rows in set (0.00 sec)
134
135 mysql> select sum(id) from user;
136 +---------+
137 sum(id)
138 +---------+
139 8
140 +---------+
141 1 row in set (0.00 sec)
142
143 mysql> select avg(id) from user;
144 +---------+
145 avg(id)
146 +---------+
147 2.6667
148 +---------+
149 1 row in set (0.04 sec)
13，使用max（）求最大值，使用min求最小值
150 mysql> select id from user;
151 +----+
152 id
153 +----+
154 1
155 3
156 4
157 +----+
158 3 rows in set (0.00 sec)
159
160 mysql> select max(id) from user;
161 +---------+
162 max(id)
163 +---------+
164 4
165 +---------+
166 1 row in set (0.00 sec)
167
168 mysql> select min(id) from user;
169 +---------+
170 min(id)
171 +---------+
172 1
173 +---------+
174 1 row in set (0.00 sec)
本文出自 “Kenan_ITBlog” 博客，请务必保留此出处http://soukenan.blog.51cto.com/5130995/1047201
该贴被eink编辑于2012-11-16 10:01:08
当前位置：
论坛首页 »
自由讨论区 »
Android, Python及开发编程讨论区
»
发起投票
技术讨论
加好友
发消息
2012-11-16 9:45:08 |
赞(
操作
