SQL-Select单表查询总结

数据定义：
建立一张学生表

1. CREATE TABLE Student
2. (Sno CHAR(10) PRIMARY KEY,
3. Sname CHAR(20) UNIQUE,
4. Ssex CHAR(2),
5. Sage SMALLINT,
6. Sdept CHAR(20)
7. );

建立一张课程表

1. CREATE TABLE `sc` (
2. `Sno` char(7) NOT NULL,
3. `Cno` char(4) NOT NULL,
4. PRIMARY KEY (`Sno`,`Cno`),
5. KEY `cno_fk` (`Cno`),
6. CONSTRAINT `cno_fk` FOREIGN KEY (`Cno`) REFERENCES `course` (`Cno`),
7. CONSTRAINT `sno_fk` FOREIGN KEY (`Sno`) REFERENCES `student` (`Sno`)
8. )

建立一张课程和学生的关联表

1. CREATE TABLE `sc` (
2. `Sno` char(7) NOT NULL,
3. `Cno` char(4) NOT NULL,
4. PRIMARY KEY (`Sno`,`Cno`),
5. KEY `cno_fk` (`Cno`),
6. CONSTRAINT `cno_fk` FOREIGN KEY (`Cno`) REFERENCES `course` (`Cno`),
7. CONSTRAINT `sno_fk` FOREIGN KEY (`Sno`) REFERENCES `student` (`Sno`)
8. )

查询 单表查询
1.查出带入计算的。

1. SELECT sname ,2017-sage FROM student

2.运用一些函数进行查询

1. SELECT sname ,"year of birth" ,2017-sage ,LOWER(sdept)FROM student

3.取别名

1. SELECT sname NAME,"year of birth" birth,2017-sage age ,LOWER(sdept) dept FROM student

4.取消重复行使用关键字DISTINCT

2. SELECT DISTINCT sno FROM student

5.where条件

• 根据指定条件查询
  

2. SELECT * FROM student WHERE sdept="cs"

1. SELECT sname ,sage FROM student WHERE sage>10

• 确定范围between和not between
  

1. SELECT sname ,sage ,sdept FROM student WHERE sage BETWEEN 10 AND 20

• 确定集合IN和NOT IN
  

2. SELECT sname ,sage,sdept FROM student WHERE sdept IN ("cs","rj")

• 字符匹配，%表示任意字符，_表示单个字符，关键字LIKE
  

1. SELECT * FROM student WHERE sname LIKE "%s%"

• 空值查询 IS NULL
  

1. SELECT * FROM student WHERE sname IS NOT NULL

• 多重条件and 或者or

1. SELECT * FROM student WHERE sdept="cs" AND sname="lisi"

1. SELECT * FROM student WHERE sdept="cs" OR sname="lisi"

6.排序oder by,用于一个属性或者多个属性的排序，ASC升序， DESC降序

1. SELECT * FROM student ORDER BY sage,sno ASC

7.聚集函数

2. SELECT COUNT(sage) FROM student

8.group by 子句，将查询结果按某一列或者多列分组，值相等的为一组，并用having 子句进行筛选条件

1. SELECT Customer,SUM(OrderPrice) FROM Orders
2. GROUP BY Customer