【MySQL】（四）SQL 基础操作之 DQL 数据查询语言1

SELECT [ALL|DISTINCT]
  {* | 表名.* | 表名.字段名.... [AS 别名]}
  FROM 要查询的表名 [AS 别名]
  [LEFT | RIGHT | INNER JOIN 连接表名 [AS 别名]]
  WHERE 查询条件;

SELECT * FROM student;

SELECT stuName,gender FROM student;

SELECT stu.stuName AS '姓名',stu.gradeId + 1 年级 FROM student stu;

SELECT
  stuName AS '姓名',
  gradeId + 1 年级 
FROM
  student;

SELECT ALL stuName,gradeId FROM student;

SELECT DISTINCT stuName,gradeId FROM student;
SELECT DISTINCT stuName,gender,gradeId FROM student;

<关系/比较运算符和逻辑运算符>
SELECT * FROM student;

SELECT * FROM student WHERE gradeId > 1;

SELECT * FROM student WHERE stuName = '张三' AND gender = '女';

SELECT * FROM student WHERE gender = '女' OR gradeId = 3;

三种方法
SELECT * FROM student WHERE NOT gender = '女';
SELECT * FROM student WHERE gender <> '女';
SELECT * FROM student WHERE gender != '女';

IS NULL          IS NOT NULL

SELECT stuName FROM student WHERE address IS NULL;
SELECT stuName FROM student WHERE address IS NOT NULL;

SELECT stuName FROM student WHERE  gradeId >= 2 AND gradeId <= 3;
-- 更加简洁
SELECT stuName FROM student WHERE  gradeId BETWEEN 2 AND 3;

SELECT * FROM student WHERE gradeId = 1 OR gradeId = 3;
-- 更加简洁
SELECT * FROM student WHERE gradeId IN (1,3);
SELECT * FROM student WHERE gradeId NOT IN (1,3);

SELECT * FROM student WHERE stuName LIKE '张%';

SELECT * FROM student WHERE stuName LIKE '张_';

SELECT * FROM student WHERE stuName LIKE '%三%';

SELECT * FROM student WHERE stuName LIKE '_三%';

SELECT stuName,gradeId FROM student;

SELECT 
  stu.stuName,g.gradeName
FROM 
  student stu
INNER JOIN grade g 
ON stu.gradeId = g.gradeId; # 关联条件

SELECT
  stu.stuName,g.gradeName
FROM 
  student stu,grade g
SELECT
  stu.stuName,g.gradeName
FROM 
  student stu,grade g
WHERE
  stu.gradeId = g.gradeId;

SELECT
  stu.stuName,sub.subjectName,r.result
FROM
  student stu,`subject` sub,result r
WHERE 
  stu.stuId = r.stuId 
  AND 
  sub.subjectId = r.subjectId;

SELECT
  stu.stuName,g.gradeName
FROM
  student stu LEFT OUTER JOIN grade g
  ON stu.gradeId = g.gradeId;

SELECT
  stu.stuName,g.gradeName
FROM
  student stu RIGHT JOIN grade g
  ON stu.gradeId = g.gradeId;

SELECT
  c1.categoryName,c2.categoryName
FROM 
  category c1,category c2
WHERE
  c1.pid = c2.id

SELECT [ALL | DISTINCT] ALL表示查询出所有的内容 DISTINCT 去重
    {* | 表名.* | 表名.字段名[ AS 别名][,...]} 指定查询出的字段的
  FROM
    表名[AS 别名][,表1... AS 别名]
  [INNER | [LEFT | RIGHT] [OUTER] JOIN 另一张表名 [AS 别名] ON 关联条件]
  [WHERE 条件]
  [GROUP BY 分组字段[,...]] 
  [HAVING 给分组后的数据进行条件筛选]
  [ORDER BY 排序字段[,...]]
  [LIMIT [startIndex,]pageSize]

SELECT
  stu.stuName,sub.subjectName,r.result
FROM 
  result r,student stu,`subject` sub
WHERE
  r.stuId = stu.stuId AND r.subjectId = sub.subjectId
ORDER BY r.result DESC;

SELECT
  stu.stuName,sub.subjectName,r.result,r.examDate
FROM 
  result r,student stu,`subject` sub
WHERE
  r.stuId = stu.stuId AND r.subjectId = sub.subjectId
ORDER BY 
  r.result DESC , r.examDate DESC; # (X) ORDER BY r.result,r.examDate DESC;

LIMIT offset,row;

SELECT
  *
FROM
  student
LIMIT 0,2; -- 如果offset是从0开始 那么可以省略 LIMIT 2

-- 基本分页：
select * from result limit 10;
-- 取前10行
select * from result limit 5,10;
-- 从第六行开始，取前10行
select * from result limit 10 offset 5;
-- 排序分页
select * from result order by StudentNo limit 10;

SELECT * FROM student LIMIT 0,2;

SELECT * FROM student LIMIT 2,2;

SELECT * FROM student LIMIT 4,2;

startIndex = (currentPage - 1) * pageSize;