在MySQL查询山东省男生信息_MySQL查询

1 --数据的准备

2 --创建一个数据库

3 create database python_test charset=utf8;4

5 --使用一个数据库

6 usepython_test;7

8 --显示使用的当前数据是哪个?

9 select database();10

11 --创建一个数据表

12 --students表

13 create tablestudents(14 id int unsigned primary key auto_increment not null,15 name varchar(20) default '',16 age tinyint unsigned default 0,17 height decimal(5,2),18 gender enum('男','女','中性','保密') default '保密',19 cls_id int unsigned default 0,20 is_delete bit default 0

21 );22

23 --classes表

24 create tableclasses (25 id int unsigned auto_increment primary key not null,26 name varchar(30) not null

27 );28

29

30

31 --查询

32 --查询所有字段

33 --select * from 表名;

34 select * fromstudents;35 select * fromclasses;36 select id, name fromclasses;37

38 --查询指定字段

39 --select 列1,列2,... from 表名;

40 select name, age fromstudents;41

42 --使用 as 给字段起别名

43 --select 字段 as 名字.... from 表名;

44 select name as 姓名, age as 年龄 fromstudents;45

46 --select 表名.字段 .... from 表名;

47 select students.name, students.age fromstudents;48

49

50 --可以通过 as 给表起别名

51 --select 别名.字段 .... from 表名 as 别名;

52 select students.name, students.age fromstudents;53 select s.name, s.age from students ass;54 --失败的select students.name, students.age from students as s;

55

56

57 --消除重复行

58 --distinct 字段

59 select distinct gender fromstudents;60

61

62 --条件查询

63 --比较运算符

64 --select .... from 表名 where .....

65 -->

66 --查询大于18岁的信息

67 select * from students where age>18;68 select id,name,gender from students where age>18;69

70 --<

71 --查询小于18岁的信息

72 select * from students where age<18;73

74 -->=

75 --<=

76 --查询小于或者等于18岁的信息

77

78 --=

79 --查询年龄为18岁的所有学生的名字

80 select * from students where age=18;81

82

83 --!= 或者 <>

84

85

86 --逻辑运算符

87 --and

88 --18到28之间的所以学生信息

89 select * from students where age>18 and age<28;90 --失败select * from students where age>18 and <28;

91

92

93 --18岁以上的女性

94 select * from students where age>18 and gender="女";95 select * from students where age>18 and gender=2;96

97

98 --or

99 --18以上或者身高查过180(包含)以上

100 select * from students where age>18 or height>=180;101

102

103 --not

104 --不在 18岁以上的女性 这个范围内的信息

105 --select * from students where not age>18 and gender=2;

106 select * from students where not (age>18 and gender=2);107

108 --年龄不是小于或者等于18 并且是女性

109 select * from students where (not age<=18) and gender=2;110

111

112 --模糊查询

113 --like

114 --% 替换1个或者多个

115 --_ 替换1个

116 --查询姓名中 以 "小" 开始的名字

117 select name from students where name="小";118 select name from students where name like "小%";119

120 --查询姓名中 有 "小" 所有的名字

121 select name from students where name like "%小%";122

123 --查询有2个字的名字

124 select name from students where name like"__";125

126 --查询有3个字的名字

127 select name from students where name like"__";128

129 --查询至少有2个字的名字

130 select name from students where name like "__%";131

132

133 --rlike 正则

134 --查询以 周开始的姓名

135 select name from students where name rlike "^周.*";136

137 --查询以 周开始、伦结尾的姓名

138 select name from students where name rlike "^周.*伦$";139

140

141 --范围查询

142 --in (1, 3, 8)表示在一个非连续的范围内

143 --查询 年龄为18、34的姓名

144 select name,age from students where age=18 or age=34;145 select name,age from students where age=18 or age=34 or age=12;146 select name,age from students where age in (12, 18, 34);147

148

149

150 --not in 不非连续的范围之内

151 --年龄不是 18、34岁之间的信息

152 select name,age from students where age not in (12, 18, 34);153

154

155 --between ... and ...表示在一个连续的范围内

156 --查询 年龄在18到34之间的的信息

157 select name, age from students where age between 18 and 34;158

159

160 --not between ... and ...表示不在一个连续的范围内

161 --查询 年龄不在在18到34之间的的信息

162 select * from students where age not between 18 and 34;163 select * from students where not age between 18 and 34;164 --失败的select * from students where age not (between 18 and 34);

165

166

167 --空判断

168 --判空is null

169 --查询身高为空的信息

170 select * from students where height is null;171 select * from students where height is NULL;172 select * from students where height is Null;173

174 --判非空is not null

175 select * from students where height is not null;176

177

178

179 --排序

180 --order by 字段

181 --asc从小到大排列，即升序

182 --desc从大到小排序，即降序

183

184 --查询年龄在18到34岁之间的男性，按照年龄从小到到排序

185 select * from students where (age between 18 and 34) and gender=1;186 select * from students where (age between 18 and 34) and gender=1 order byage;187 select * from students where (age between 18 and 34) and gender=1 order by age asc;188

189

190 --查询年龄在18到34岁之间的女性，身高从高到矮排序

191 select * from students where (age between 18 and 34) and gender=2 order by height desc;192

193

194 --order by 多个字段

195 --查询年龄在18到34岁之间的女性，身高从高到矮排序, 如果身高相同的情况下按照年龄从小到大排序

196 select * from students where (age between 18 and 34) and gender=2 order by height desc,id desc;197

198

199 --查询年龄在18到34岁之间的女性，身高从高到矮排序, 如果身高相同的情况下按照年龄从小到大排序,

200 --如果年龄也相同那么按照id从大到小排序

201 select * from students where (age between 18 and 34) and gender=2 order by height desc,age asc,id desc;202

203

204 --按照年龄从小到大、身高从高到矮的排序

205 select * from students order by age asc, height desc;206

207

208 --聚合函数

209 --总数

210 --count

211 --查询男性有多少人，女性有多少人

212 select * from students where gender=1;213 select count(*) from students where gender=1;214 select count(*) as 男性人数 from students where gender=1;215 select count(*) as 女性人数 from students where gender=2;216

217

218 --最大值

219 --max

220 --查询最大的年龄

221 select age fromstudents;222 select max(age) fromstudents;223

224 --查询女性的最高 身高

225 select max(height) from students where gender=2;226

227 --最小值

228 --min

229

230

231 --求和

232 --sum

233 --计算所有人的年龄总和

234 select sum(age) fromstudents;235

236

237 --平均值

238 --avg

239 --计算平均年龄

240 select avg(age) fromstudents;241

242

243 --计算平均年龄 sum(age)/count(*)

244 select sum(age)/count(*) fromstudents;245

246

247 --四舍五入 round(123.23 , 1) 保留1位小数

248 --计算所有人的平均年龄，保留2位小数

249 select round(sum(age)/count(*), 2) fromstudents;250 select round(sum(age)/count(*), 3) fromstudents;251

252 --计算男性的平均身高 保留2位小数

253 select round(avg(height), 2) from students where gender=1;254 --select name, round(avg(height), 2) from students where gender=1;

255

256 --分组

257

258 --group by

259 --按照性别分组,查询所有的性别

260 select name from students group bygender;261 select * from students group bygender;262 select gender from students group bygender;263 --失败select * from students group by gender;

264

265 --计算每种性别中的人数

266 select gender,count(*) from students group bygender;267

268

269 --计算男性的人数

270 select gender,count(*) from students where gender=1 group bygender;271

272

273 --group_concat(...)

274 --查询同种性别中的姓名

275 select gender,group_concat(name) from students where gender=1 group bygender;276 select gender,group_concat(name, age, id) from students where gender=1 group bygender;277 select gender,group_concat(name, "_", age, " ", id) from students where gender=1 group bygender;278

279 --having

280 --查询平均年龄超过30岁的性别，以及姓名 having avg(age) > 30

281 select gender, group_concat(name),avg(age) from students group by gender having avg(age)>30;282

283 --查询每种性别中的人数多于2个的信息

284 select gender, group_concat(name) from students group by gender having count(*)>2;285

286

287

288 --分页

289 --limit start, count

290

291 --限制查询出来的数据个数

292 select * from students where gender=1 limit 2;293

294 --查询前5个数据

295 select * from students limit 0, 5;296

297 --查询id6-10(包含)的书序

298 select * from students limit 5, 5;299

300

301 --每页显示2个，第1个页面

302 select * from students limit 0,2;303

304 --每页显示2个，第2个页面

305 select * from students limit 2,2;306

307 --每页显示2个，第3个页面

308 select * from students limit 4,2;309

310 --每页显示2个，第4个页面

311 select * from students limit 6,2; -------> limit (第N页-1)*每个的个数, 每页的个数;

312

313 --每页显示2个，显示第6页的信息, 按照年龄从小到大排序

314 --失败select * from students limit 2*(6-1),2;

315 --失败select * from students limit 10,2 order by age asc;

316 select * from students order by age asc limit 10,2;317

318 select * from students where gender=2 order by height desc limit 0,2;319

320

321

322 --连接查询

323 --inner join ... on

324

325 --select ... from 表A inner join 表B;

326 select * from students inner joinclasses;327

328 --查询 有能够对应班级的学生以及班级信息

329 select * from students inner join classes on students.cls_id=classes.id;330

331 --按照要求显示姓名、班级

332 select students.*, classes.name from students inner join classes on students.cls_id=classes.id;333 select students.name, classes.name from students inner join classes on students.cls_id=classes.id;334

335 --给数据表起名字

336 select s.name, c.name from students as s inner join classes as c on s.cls_id=c.id;337

338 --查询 有能够对应班级的学生以及班级信息，显示学生的所有信息，只显示班级名称

339 select s.*, c.name from students as s inner join classes as c on s.cls_id=c.id;340

341 --在以上的查询中，将班级姓名显示在第1列

342 select c.name, s.* from students as s inner join classes as c on s.cls_id=c.id;343

344 --查询 有能够对应班级的学生以及班级信息, 按照班级进行排序

345 --select c.xxx s.xxx from student as s inner join clssses as c on .... order by ....;

346 select c.name, s.* from students as s inner join classes as c on s.cls_id=c.id order byc.name;347

348 --当时同一个班级的时候，按照学生的id进行从小到大排序

349 select c.name, s.* from students as s inner join classes as c on s.cls_id=c.id order byc.name,s.id;350

351 --left join

352 --查询每位学生对应的班级信息

353 select * from students as s left join classes as c on s.cls_id=c.id;354

355 --查询没有对应班级信息的学生

356 --select ... from xxx as s left join xxx as c on..... where .....

357 --select ... from xxx as s left join xxx as c on..... having .....

358 select * from students as s left join classes as c on s.cls_id=c.id having c.id is null;359 select * from students as s left join classes as c on s.cls_id=c.id where c.id is null;360

361 --right join on

362 --将数据表名字互换位置，用left join完成

363

364 --自关联

365 --省级联动 url:http://demo.lanrenzhijia.com/2014/city0605/

366

367 --查询所有省份

368 select * from areas where pid is null;369

370 --查询出山东省有哪些市

371 select * from areas as province inner join areas as city on city.pid=province.aid having province.atitle="山东省";372 select province.atitle, city.atitle from areas as province inner join areas as city on city.pid=province.aid having province.atitle="山东省";373

374 --查询出青岛市有哪些县城

375 select province.atitle, city.atitle from areas as province inner join areas as city on city.pid=province.aid having province.atitle="青岛市";376 select * from areas where pid=(select aid from areas where atitle="青岛市")377

378

379 --子查询

380 --标量子查询

381 --查询出高于平均身高的信息

382 select * from students where height> (select avg(height) fromstudents);383

384 --查询最高的男生信息

385 select * from students where height = 188;386 select * from students where height = (select max(height) fromstudents);387

388 --列级子查询

389 --查询学生的班级号能够对应的学生信息

390 select * from students where cls_id in (select id from classes);