mysql进阶查询命令

表名：t_student

查询表数据内容（显示位置做了切换以及筛选）

SELECTid,stuName,age,sex,gradeName FROM t_student ;

SELECTstuName,id,age,sex,gradeName FROM t_student ;

SELECT * FROMt_student;

SELECTstuName,gradeName FROM t_student;

 

指定查询主键id=1的行

SELECT * FROMt_student WHERE id=1;

指定查询age数据大于22的行

SELECT * FROMt_student WHERE age>22;

指定查询age数据大于21和23的单行

SELECT * FROMt_student WHERE age IN (21,23);

指定查询age数据21到23之外的单行

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

指定查询21到24之内的全部行数

SELECT * FROMt_student WHERE age BETWEEN 21 AND 24;

指定查询21到24之外的全部行数

SELECT * FROMt_student WHERE age NOT BETWEEN 21 AND 24;

 

模糊查询：%表示多个任意字符 _表示单个任意字符

SELECT * FROMt_student WHERE stuName LIKE '张三';

SELECT * FROMt_student WHERE stuName LIKE '张三%';

SELECT * FROMt_student WHERE stuName LIKE '张三__';

SELECT * FROMt_student WHERE stuName LIKE '%张三%';

 

查询字符串为null的数据

SELECT * FROMt_student WHERE sex IS NULL;

查询字符串不是为null的数据

SELECT * FROMt_student WHERE sex IS NOT NULL;

 

 

查询表中一年级并且是23岁的

SELECT * FROMt_student WHERE gradeName='一年级' AND age=23

查询表中一年级和23岁的

SELECT * FROMt_student WHERE gradeName='一年级' OR age=23

 

列出所有grandeName列的数据

SELECT DISTINCTgradeName FROM t_student;

列出所有grandeName列的数据升序和降序

SELECT * FROMt_student ORDER BY age ASC;

SELECT * FROMt_student ORDER BY age DESC;

 

group用法重点：

将gradeName为主列GROUP_CONCAT(stuName)为从列，查询gradeName中有多少个stuName并且以逗号隔开显示

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

 

将gradeName为主列GROUP_CONCAT(stuName)为从列，查询gradeName数量是多少

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

 

将gradeName为主列GROUP_CONCAT(stuName)为从列，查询gradeName中有3次以上的stuName列的数据

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

将gradeName为主列GROUP_CONCAT(stuName)为从列，统计stuName总数

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

将gradeName为主列GROUP_CONCAT(stuName)为从列，统计stuName总数以逗号形式分开显示

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

 

分页查询

0代表第一行数据 0,5 代表显示第一行数据到第五行数据为一页

SELECT * FROMt_student LIMIT 0,5;

SELECT * FROMt_student LIMIT 5,5;

SELECT * FROMt_student LIMIT 10,5;