SQL-Select单表查询总结

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/u014206695/article/details/55224871
数据定义:
建立一张学生表
  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
  1. SELECT DISTINCT sno FROM student
5.where条件
  • 根据指定条件查询
  1. 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

  1. 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.聚集函数
  1. SELECT COUNT(sage) FROM student
8.group by 子句,将查询结果按某一列或者多列分组,值相等的为一组,并用having 子句进行筛选条件

  1. SELECT Customer,SUM(OrderPrice) FROM Orders
  2. GROUP BY Customer
阅读更多 登录后自动展开
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页