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;
注:上诉代码可以在优快云中运行,也可以在图形工具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