MySQL学习之MySQL单表查询

学习峰哥java学习课程学自学记录：

http://www.java1234.com/javaxuexiluxiantu.html

1、自己学习的练习SQL语句

表结构如下：

1）从student表查询id，iname，age，sex

SELECT id,iname,age,sex FROM student;

 2）从student表查询所有字段

SELECT * FROM student;

 3）查询所有字段从student，条件分别是id=3和age>20.

SELECT * FROM student WHERE id=3;

SELECT * FROM student WHERE age > 20;

 4）查找年龄是23，18和15的人，查年龄不是23，18和15的人。

SELECT * FROM student WHERE age IN (23,18,15);

SELECT * FROM student WHERE age NOT IN (23,18,15);

 5）查找年龄在21到46之间的人，查询年龄不再21到46之间的人

SELECT * FROM student WHERE age BETWEEN 21 AND 46;

SELECT * FROM student WHERE age NOT BETWEEN 21 AND 46;

 6）查询字段类似指定字段的值（%表示任意字段，_表示任意的一个字段）

SELECT * FROM student WHERE iname LIKE '张三';

SELECT * FROM student WHERE iname LIKE '张三%';

SELECT * FROM student WHERE iname LIKE '%张三%';

SELECT * FROM student WHERE iname LIKE '张三_';

SELECT * FROM student WHERE iname LIKE '张三__';

 7）查询名字为null的人，查询名字不为null的人

SELECT * FROM student WHERE iname IS NULL;

SELECT * FROM student WHERE iname IS NOT NULL;

 8）and链接多条件查询

SELECT * FROM student WHERE sex=0 AND age=23;

SELECT * FROM student WHERE sex=0 OR age=23;

 9）去重复查询

SELECT DISTINCT sex FROM student;

 10）查询根据年龄升序、降序排序

SELECT * FROM student ORDER BY age ASC;

SELECT * FROM student ORDER BY age DESC;

 11）group by

分类

SELECT sex,GROUP_CONCAT(iname) FROM student GROUP BY sex;

统计

SELECT sex,c(iname) FROM student GROUP BY sex;

统计删选
SELECT sex,COUNT(iname) FROM student GROUP BY sex HAVING COUNT(iname) >3; 

最后一行加统计结果
SELECT sex,COUNT(iname) FROM student GROUP BY sex WITH ROLLUP;
12）分组查询
SELECT * FROM student LIMIT 0,5;
SELECT * FROM student LIMIT 5,5;