MySql-查询数据

1.单表查询
（1）查询所有字符段
SELECT 字段1，字段2…字段n FROM 表名；

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

SELECT * From 表名；

SELECT * FROM t_student;

（2）查询指定字段
SELECT 字段1，字段2… FROM 表名；

SELECT stuName,gradeName FROM t_student;

（3）条件查询
SELECT 字段1，字段2… FROM 表名 WHERE 条件表达式；

SELECT * FROM t_student WHERE id=1;
SELECT * FROM t_student WHERE age>22;

（4）带IN关键字查询
SELECT 字段1，字段2… FROM 表名 WHERE 字段 [NOT]IN(元素1，元素2…)；

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

（5）带BETWEEN ADN的范围查询
SELECT 字段1，字段2… FROM 表名 WHERE 字段 [NOT]BETWEEN 取值1 AND 取值2；

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

（6）带LIKE的模糊查询
SELECT 字段1，字段2… FROM 表名 WHERE 字段 [NOT]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 '%张三%';

（7）空值查询
SELECT 字段1，字段2… FROM 表名 WHERE 字段 IS [NOT] NULL；

SELECT * FROM t_student WHERE sex IS NULL;
SELECT * FROM t_student WHERE sex IS NOT NULL;

（8）带AND的多值查询
SELECT 字段1，字段2… FROM 表名 WHERE 表达式1 AND 表达式2[…表达式n]；

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

（9）带OR的多值查询
SELECT 字段1，字段2… FROM 表名 WHERE 表达式1 OR 表达式2[…表达式n]；

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

（10）DISTINCT去重复查询
SELECT DISTINCT 字段 FROM 表名；

SELECT DISTINCT gradeName FROM t_student;

（11）带查询结果排序
SELECT 字段 FROM 表名 ORDER BY 属性名 [ASC|DESC]；

SELECT * FROM t_student ORDER BY age ASC;
SELECT * FROM t_student ORDER BY age DESC;

（12）GROUP BY 分组查询

SELECT * FROM t_student GROUP BY gradeName;
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;

（14）LIMIT分页查询
SELECT 字段1，字段2… FROM 表名 LIMIT 初始位置,记录数；

SELECT * FROM t_student LIMIT 0,5;
SELECT * FROM t_student LIMIT 5,5;
SELECT * FROM t_student LIMIT 10,5;

注：上诉代码可以在CSDN中运行，也可以在图形工具sqlyog中运行（用这个）

在sqlyog中运行步骤
1.选中要运行的代码

2.点击按钮运行

2.使用聚合函数查询
（1）COUNT()函数
统计记录的条数，常与GROUP BY一起使用

SELECT COUNT(*) FROM t_grade;
SELECT COUNT(*) AS total FROM t_grade;
SELECT stuName,COUNT(*) FROM t_grade GROUP BY stuName;

（2）SUN()函数
求和函数，常与GROUP BY一起使用

SELECT stuName,SUM(score) FROM t_grade WHERE stuName="张三";
SELECT stuName,SUM(score) FROM t_grade GROUP BY stuName;

（3）AVG()函数
求平均数，常与GROUP BY一起使用

SELECT stuName,AVG(score) FROM t_grade WHERE stuName="张三";
SELECT stuName,AVG(score) FROM t_grade GROUP BY stuName;

（4）MAX()函数
求最大值，常与GROUP BY一起使用

SELECT stuName,course,MAX(score) FROM t_grade WHERE stuName="张三";
SELECT stuName,MAX(score) FROM t_grade GROUP BY stuName;

（5）MIN()函数
求最小值，常与GROUP BY一起使用

SELECT stuName,course,MIN(score) FROM t_grade WHERE stuName="张三";
SELECT stuName,MIN(score) FROM t_grade GROUP BY stuName;

3 连接查询

（1）内连接查询

SELECT * FROM t_book,t_bookType WHERE t_book.bookTypeId=t_bookType.id;
SELECT bookName,author,bookTypeName FROM t_book,t_bookType WHERE t_book.bookTypeId=t_bookType.id;
SELECT tb.bookName,tb.author,tby.bookTypeName FROM t_book tb,t_bookType tby WHERE tb.bookTypeId=tby.id;

（2）外连接查询

外连接分为左连接和右连接
左连接

SELECT * FROM t_book LEFT JOIN t_bookType ON t_book.bookTypeId=t_bookType.id;
SELECT tb.bookName,tb.author,tby.bookTypeName FROM t_book tb LEFT JOIN t_bookType tby ON tb.bookTypeId=tby.id;

右连接

SELECT * FROM t_book RIGHT JOIN t_bookType ON t_book.bookTypeId=t_bookType.id;
SELECT tb.bookName,tb.author,tby.bookTypeName FROM t_book tb RIGHT JOIN t_bookType tby ON tb.bookTypeId=tby.id;

4 子查询
（1）带IN关键字的子查询
一条查询语句的条件可能落在另一条查询语句的结果中

SELECT * FROM t_book WHERE booktypeId IN (SELECT id FROM t_booktype);
SELECT * FROM t_book WHERE booktypeId NOT IN (SELECT id FROM t_booktype);

（2）带比较运算符的子查询
子查询可以使用比较运算符

SELECT * FROM t_book WHERE price>=(SELECT price FROM t_pricelevel WHERE priceLevel=1);

（3）带Exists关键字的子查询
如果子查询查询到结果，则执行外层查询，否则不执行外层查询

SELECT * FROM t_book WHERE EXISTS (SELECT * FROM t_booktype);
SELECT * FROM t_book WHERE NOT EXISTS (SELECT * FROM t_booktype);

（4）带Any关键字的子查询
Any关键字表示满足其中任一条件

SELECT * FROM t_book WHERE price>= ANY (SELECT price FROM t_pricelevel);

（5）带All关键字的子查询
All关键字表示满足所有条件

SELECT * FROM t_book WHERE price>= ALL (SELECT price FROM t_pricelevel);

5 合并查询结果
（1）UNION
将所有查询结果合并到一起，然后去掉相同的记录

SELECT id FROM t_book UNION SELECT id FROM t_booktype;

（2）UNION ALL
将所有查询结果合并到一起，不会去掉记录

SELECT id FROM t_book UNION ALL SELECT id FROM t_booktype;

6 为表和字段取别名
（1）为表取别名
格式：表名 别名

SELECT * FROM t_book t WHERE t.id=1;//为表取别名t

（2）为字段取别名
格式：属性名 [AS] 别名

SELECT t.bookName bName FROM t_book t WHERE t.id=1;//为bookName取别名bName
SELECT t.bookName AS bName FROM t_book t WHERE t.id=1;//为bookName取别名bName