mysql_05_单表查询

 1 #创建数据库
 2 create database db_student;
 3 
 4 #使用数据库
 5 use db_student;
 6 
 7 #创建表
 8 create table `t_student` (
 9 `id` int primary key not null auto_increment,
10 `stuName` varchar (60),
11 `age` int ,
12 `sex` varchar (30),
13 `gradeName` varchar (60)
14 );
15 
16 #插入数据
17 insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('1','张三','23','男','一年级');
18 insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('2','张三丰','25','男','二年级');
19 insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('3','李四','23','男','一年级');
20 insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('4','王五','22','男','三年级');
21 insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('5','珍妮','21','女','一年级');
22 insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('6','李娜','26','女','二年级');
23 insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('7','王峰','20','男','三年级');
24 insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('8','梦娜','21','女','二年级');
25 insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('9','小黑','22','男','一年级');
26 insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('10','追风','25','男','二年级');
27 insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('11','小小张三','21',NULL,'二年级');
28 insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('12','小张三','23','男','二年级');
29 insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('13','张三锋小','24',NULL,'二年级');
30 
31  
32 
33 #第五章:查询数据
34 #第一节:单表查询
35 #5.1:查询所有字段
36 select id,stuName,age,sex,gradeName from t_student;
37 select stuName,gradeName,age,sex,id from t_student;
38 select * from t_student;
39 
40 #5.2:查询指定字段
41 select stuName,gradeName from t_student;
42 
43 #5.3:Where条件查询
44 select stuName,id from t_student where id=3;
45 select * from t_student where id=3;
46 select * from t_student where age>22;
47 
48 #5.4:带in关键字查询
49 select * from t_student where age in (21,23);#查询age=21，和age=23的学生的信息
50 select * from t_student where age not in (21,23);#查询age!=21，或者age!=23的学生的信息
51 
52 #5.5:带between and 的范围查询
53 select * from t_student where age between 22 and 24;#查询age范围在(22,24)的学生的信息
54 select * from t_student where age not between 22 and 24;#查询age范围不在(22,24)的学生的信息
55 
56 #5.6:带like的模糊查询,%:任意字符，_:单个字符
57 select * from t_student where stuName like "张%";#查询姓名为'张'字开头的信息
58 select * from t_student where stuName like "张_";#查询姓名为'张'字开头,并姓名只有两个字的信息
59 select * from t_student where stuName like "张__";#查询姓名为'张'字开头,并姓名只有三个字的信息
60 select * from t_student where stuName like "%张三%";#查询姓名包含张三的数据的信息
61 
62 #5.7:空值查询
63 select * from t_student where sex is null;
64 select * from t_student where sex is not null;
65 
66 #5.8:带and的多条件查询
67 select * from t_student where gradeName="一年级" and age=23;
68 
69 #5.9:带or的多条件查询
70 select * from t_student where gradeName="一年级" or age=23;
71 
72 #5.10:distinct去重复查询
73 select distinct gradeName from t_student;
74 
75 #5.11:order by对查询结果排序
76 select * from t_student order by age;#默认升序排列
77 select * from t_student order by age asc;#年龄升序排列
78 select * from t_student order by age desc;#年龄降序排列
79 
80 #5.12:group by分组查询
81 select gradeName,group_concat(stuName) from t_student group by gradeName;#按照年级进行分组，查询姓名
82 select gradeName,count(stuName) from t_student group by gradeName;#按照年级进行分组，查询各个年纪的人数
83 select gradeName,count(stuName) from t_student group by gradeName having count(stuName)>3;#对查询到的分组数据进行限制输出，只显示年级人数大于3的年级
84 select gradeName,count(stuName) from t_student group by gradeName with rollup;#多加一行显式总和
85 select gradeName,group_concat(stuName) from t_student group by gradeName with rollup;
86 
87 #5.13:limit分页查询;limit 初始位置,记录数;
88 select * from t_student limit 0,5;
89 select * from t_student limit 6,5;
90 select * from t_student limit 10,5;

 

转载于:https://www.cnblogs.com/xinwenpiaoxue/p/7242502.html