MySQL查询1

1、将下列语句复制到sqlyog的询问栏

/*!40101 SET NAMES utf8 */;

create table `t_student` (
`id` double ,
`stuName` varchar (60),
`age` double ,
`sex` varchar (30),
`gradeName` varchar (60)
);
insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('1','张三','23','男','一年级');
insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('2','张三丰','25','男','二年级');
insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('3','李四','23','男','一年级');
insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('4','王五','22','男','三年级');
insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('5','珍妮','21','女','一年级');
insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('6','李娜','26','女','二年级');
insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('7','王峰','20','男','三年级');
insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('8','梦娜','21','女','二年级');
insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('9','小黑','22','男','一年级');
insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('10','追风','25','男','二年级');
insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('11','小小张三','21',NULL,'二年级');
insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('12','小张三','23','男','二年级');
insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('13','张三锋小','24',NULL,'二年级');

这样就会在选定的数据库db_student里创建一张t_student表。

注：查询语句写在询问栏后，全选或者光标在语句中，再点击执行查询(F9)按钮，就可以在下方的结果栏看到查询结果。

 

2、查询所有字段

SELECT id,stuName,age,sex,gradeName FROM t_student;

SELECT * FROM t_student;

 

3、查询指定字段

 SELECT id,stuName FROM t_student;

 

4、Where条件查询

SELECT * FROM t_student WHERE id=1;

 SELECT * FROM t_student WHERE age>22;

 

5、带IN关键字查询

 SELECT * FROM t_student WHERE age IN (21,23);

SELECT * FROM t_student WHERE age NOT IN (21,23);

 

6、带BETWEEN AND的范围查询

SELECT * FROM t_student WHERE age BETWEEN 22 AND 24;

SELECT * FROM t_student WHERE age NOT BETWEEN 22 AND 24;

SELECT * FROM t_student WHERE age NOT BETWEEN 22 AND 24 ORDER BY id DESC;

 

7、带LIKE的模糊查询

 SELECT * FROM t_student WHERE stuName LIKE '珍妮';

SELECT * FROM t_student WHERE stuName LIKE '张三%';

SELECT * FROM t_student WHERE stuName LIKE '张三_';

SELECT * FROM t_student WHERE stuName LIKE '张三__';

SELECT * FROM t_student WHERE stuName LIKE '%张三%';

 

 

8、空值查询

SELECT * FROM t_student WHERE sex IS NULL;

SELECT * FROM t_student WHERE sex IS NOT NULL;

 

9、带AND的多条件查询

 SELECT * FROM t_student WHERE gradeName='一年级' AND age=23;

 

10、带OR的多条件查询

 

SELECT * FROM t_student WHERE gradeName='一年级' OR age=23;

 

11、DISTINCT去重复查询

SELECT DISTINCT gradeName FROM t_student;

 

12、对查询结果排序

 默认升序：

SELECT * FROM t_student ORDER BY age ASC;

SELECT * FROM t_student ORDER BY age DESC;

 

13、GROUP BY分组查询

 SELECT gradeName, GROUP_CONCAT(stuName) FROM t_student GROUP BY gradeName;

SELECT gradeName,COUNT(stuName) FROM t_student GROUP BY gradeName;

SELECT gradeName,COUNT(stuName) FROM t_student GROUP BY gradeName HAVING COUNT(stuName)>3;

SELECT gradeName,COUNT(stuName) FROM t_student GROUP BY gradeName WITH ROLLUP;

SELECT gradeName,GROUP_CONCAT(stuName) FROM t_student GROUP BY gradeName WITH ROLLUP;

注：group_concat()，手册上说明:该函数返回带有来自一个组的连接的非NULL值的字符串结果

 

14、LIMIT分页查询

SELECT * FROM t_student LIMIT 0,5;

SELECT * FROM t_student LIMIT 5,5;

SELECT * FROM t_student LIMIT 10,5;