1.SELECT

SELECT 1+1;# SELECT可以没有FROM

SELECT 1+1 FROM DUAL;# 与上句等价，DUAL只是形式上的表

SELECT 1 AS a;# 起别名

SELECT 1 a;# 与上句等价，AS可以省略，但不建议省略

2.FROM

SELECT name # 输出名字列
FROM student;

SELECT name,score # 输出name,score列
FROM student;

SELECT * # 输出所有列
FROM student;

SELECT 
	CONCAT('姓名:',name) AS name # 输出计算列
FROM student;

SELECT 
	*,# 表示所有列的*必须在前，其他在后
	name # 输出所有列和name列，即name列输出了两次
FROM student;

SELECT MAX(score) # 输出聚合值
FROM student;

SELECT DISTINCT name # 输出去重后的学生姓名
FROM student;

SELECT COUNT(DISTINCT name) # 输出去重后的学生姓名总数
FROM student;

SELECT 
	E.*,D.dept_name AS dept_name # 输出其中一个表的所有列和另一个表的指定列
FROM 
	employee AS E # 起别名
	INNER JOIN department AS D # 内部联接
	USING(dept_id);

3.WHERE

SELECT name
FROM student
WHERE
	age=21;# 输出年龄为21岁的学生姓名

SELECT name
FROM student
WHERE
	id<=1005;# 输出学号小于1005的学生姓名

SELECT name
FROM student
WHERE
	score BETWEEN 90 AND 100;# 输出成绩在[90,100]区间的学生姓名

SELECT name
FROM student
WHERE
	name LIKE '张%';# 输出名字开头为张的学生姓名

SELECT name
FROM student
WHERE
	address IS NOT NULL;# 输出住址不为空的学生姓名

4.GROUP BY

SELECT 
	class,
	AVG(score)
FROM student
WHERE
	age=21
GROUP BY class;# 按班级分组

SELECT 
	class,
	AVG(score)
FROM student
WHERE
	age=21
GROUP BY class,gender;# 先按班级分组再按性别分组

5.HAVING

SELECT 
	class,
	AVG(score)
FROM student
WHERE
	age=21
GROUP BY class
HAVING AVG(score)>=80;# 班级平均分大于等于80的班级

SELECT 
	class,
	COUNT(name) # 班级学生姓名总数
FROM student
WHERE
	age=21
GROUP BY class
HAVING COUNT(DISTINCT name)>=30;# 班级不重复学生姓名总数大于等于30的班级

6.ORDER BY

SELECT 
	name
FROM student
WHERE
	age=21
ORDER BY class ASC;# 按班级名称升序

SELECT 
	name
FROM student
WHERE
	age=21
ORDER BY class;# 按班级名称升序，ASC可省略，但不建议省略

SELECT 
	name
FROM student
WHERE
	age=21
ORDER BY class ASC,score DESC;# 先按班级名称升序，再按学生成绩降序

7.LIMIT

SELECT 
	name
FROM student
LIMIT 5;# 输出前五行

SELECT 
	name
FROM student
LIMIT 5 OFFSET 3;# 输出从第四行起的五行，偏移值从0开始！！！

SELECT 
	name
FROM student
LIMIT 3,5;# 输出从第四行起的五行

8.总结

本篇只对一些常用的基础操作进行了介绍，进一步的用法请参考专栏相关内容或官方文档

更多知识见专栏