【C# & postgreSQL】SQL基础

结 构 化 查 询 语 言SQL

目录

• 结 构 化 查 询 语 言SQL
  • SQL概述
  • SQL分类与特点
    • 常见操作用法
      • 定义CREATE
      • 插入INSERT INTO
      • 更新UPDATE
      • 删除DELETE
      • 查询SELECT
        • 单表查询
          • 选择表中若干列
          • ORDER BY
          • 聚集函数
          • GROUP BY
        • 连接查询
          • 等值与非等值连接
          • 自身连接
          • 外连接
          • 复合条件连接
        • 嵌套查询
          • 带IN谓词的子查询
          • 带比较运算符的子查询
          • 带ANY（SOME）或ALL的谓词的子查询
          • 带EXISTS谓词的子查询
        • 集合查询
          • UNION
          • INTERSECT
          • EXCEPT

SQL概述

特点和发展要点：

• SQL是各种数据库通用语言
• 1999扩展对象关系型的概念——SQL99（SQL33）
• 多媒体数据库标准——SQL/MM标准

SQL3 级模式结构：

概念理解：

基表

视图

储存文件

SQL分类与特点

1） 数据定义——定义模式、表（TABLE) 视图(VIEW) 索引(INDEX)

2）数据操纵语言——最常用的四个

3）数据控制语言——数据库用户授权，读写等、操作范围

特点

• 综合统一

  • 集数据定义语言DDL、数据操纵语言DML、数据控制语言DCL功能为一体
  • 可以完成数据库生命周期全部获得
    • 定义关系模式、插入数据、建立数据库
    • 数据查询、更新
    • 重构和维护
    • 控制数据库安全性、完整性等
  • 数据库建成后可以根据需求调整模式而不影响数据运行
  • 操作符统一

• 高度非过程化

  • 只需要提出要求、不需要了解存取路径——数据存取、SQL命令系统自动完成

• 面向集合和表的操作

  • 非关系型数据模型采用面向记录的操作方式，操作对象是一条记录
  • SQL采用集合操作方式
    • 操作对象、查找结果可以是元组的集合
    • 一次插入、删除、更新操作的对象可以树元组的集合

• 以一种语法结构提高多种使用方式

  • 独立语言
  • 又是嵌入式语言，可以嵌入高级语言中

• 语言简洁，简单易学

  

基本规则和数据类型

• 命名

  长度限制

  • MySQL：64字符
  • Oracle: 30字符

  字母开头，其余可以为字母、数字、下划线组成

  • MySQL：任意数字、字符、符合，不能全部由数字组成
  • Oracle：任何字符

• 大小写不敏感

  • 关键字大写
  • 变量大写第一个

• 可写成多行，习惯上一个子句占一行

• 关键字不能在行与行之间分开（保证单词完整）

• ;分号为结束符，才能执行

• 注释/**/

  

• 不同数据类型，字符串、日期——半角英文单引号

  

常见操作用法

定义CREATE

模式（schema）：命名的对象集合，包括表、视图、索引、约束等，为了区别不同的表集合

/*1.建立数据库/模式*/
CREATE DATABASE SC  
#或 CREATE SCHEMA SC

/*2.打开数据库*/
USE SC

/*3.删除*/
DROP DATABASE SC
# 以上一般通过操作窗口直接实现

基表TABLE

/*1.定义基表*/
CREATE TABLE Student
(Sno CHAR(5) NOT NULL UNIQUE PRIMARY KEY, # 定义主键就意味着唯一性，所以不用重复unique了
 Sname CHAR(20) UNIQUE,
 Sage SMALLINT,
 
 PRIMARY KEY(Sno,cno),/*定义多个主键*/
 FOREIGN KEY(Cno) REFERENCES Course(Cno) /*表级完整性约束条件，Cno为外码，参照表是Course表中的Cno */
 
);

/*2.删除表 —— 一般删除数据就完全没了，所以要慎重，或规定删除的部分*/
DROP TABLE Student [RESTRICT|CASCADE]; # 后者删除所有，前者不级联删除

/*3.修改基本表——添加删除列、约束等*/
ALTER TABLE Student
ADD Stime DATE， # 添加列
ALTER COLUMN Sno CHAR(8), #修改列长度，“此方法”只能加长不能变短，不能修改列名,不能修改数据类型
DROP UNIQUE(Sname)，  # 删除约束条件
DROP COLUMN Sage;  # 删除某列

索引

/*1. 建立索引*/
CREATE CLUSTERED INDEX CCno ON Course(Cno); # 聚簇索引（字典），无索引文件，有索引文件：B-Tree& Hash
CREATE UNIQUE INDEX CCno ON Course(Cno ASC,Sno DESC);#直接建立索引，ASC、DESC为非聚簇

/*2. 删除索引*/
DROP INDEX CCno;  # 索引名

插入INSERT INTO

插入元组

INSERT INTO Student
(Sno,Sname,Sage)
VALUES
	('2020','ly',21),
	('2021','la',21),
	('2022','ld',23);   # 可插入多个，以分号结尾

插入子查询结果

# 建表
CREATE TABLE Sudent
(Sdept CHAR(15), /*系名*/
	Avg_age SMALLINT); /*学生平均年龄*/
/*1. 插入数据*/
INSERT INTO Student
(Sdept,Avg_age)  # 要插入的列
SELECT Sdept,AVG(Sage)  # 选择查询的数据结果
FROM SG
GROUP BY Sdept;  # 归组，并得到平均值

更新UPDATE

UPDATE "S-T".sc
SET grade=90; /*没有WHERE进行限制，修改整列的值*/
/*SET sage = sage+1;*/
UPDATE "S-T".sc
SET grade=93 WHERE cno='1' AND sno='01';

删除DELETE

查询SELECT

单表查询

选择表中若干列

（若干行、经过计算的值

SELECT Sno,Sname FROM Student;  /*若干列*/

SELECT * FROM Student;  /*全部列*/

SELECT DISTINCT Sno FROM SC; /*选择不重复的行*/

SELECT Sname,Sage FROM Student
WHERE Sage BETWEEN 20 AND 24;  /*在某范围中，也可使用> < = 等比较运算*/

SELECT Sname,Sage FROM Student
WHERE Sdept IN ('CS','GIS');  /*判断在不在集合里，可以用 IN or NOT IN*/

SELECT Sname,Sage FROM Student
WHERE Sname LIKE "张%";  /*字符匹配，除了%，还可以用下划线表示一个字符*/
WHERE Sgrade IS NULL; /*选择空值，IS NOT NULL*/


/*经过计算的值*/
SELECT Sname,2016-Sage BrithYear,LOWER(Sdept) DEPT
FROM Student;

ORDER BY

SELECT * FROM Student
ORDER BY Sdept,Sage;

聚集函数

COUNT(*); #统计元组个数
COUNT(sc.Sname)
SUM(sc.Score)
AVG()
MAX()
MIN()

SELECT AVG(SC.Grade) FROM SC
WHERE Sno = '20201'  /*查询某个学生的所有成绩*/

GROUP BY

SELECT Sno FROM SC
GROUP BY Sno
HAVING COUNT(*)>3  /*查询选修3门课以上的学生学号*/

连接查询

等值与非等值连接

SELECT Student.*,SC.* FROM Student,SC 
WHERE Student.Sno =SC.Sno;
/*查询每个学生及其选修课情况，相当于连接*/

自身连接

/*自身分为多个表进行连接*/
SELECT FIRST.Cno,SECOND.Cpno FROM Course FIRST,Course SECOND
WHERE FIRST.Cpno = SECOND.Cno

外连接

左连接：保证左表的信息都有，右表中有对应则写，没有对应的就空着

SELECT Student.Sno,Sname,Sgen,Sage,Sdep,Cno,Grade
FROM Student LEFT OUT JOIN SC ON (Student.Sno=SC.Sno);

右连接：列出右边的所有数据，左边有就连接，没有就空着

复合条件连接

/*选择课程2中成绩高于90的同学*/
/*1. 目标是选择学生姓名和学号；2.表连接（sno=sno），课程=2，score>90三个条件取交集，使用AND连接*/
SELECT Student.Sname,Sno FROM Student,SC
WHERE Student.Sno=SC=Sno AND SC.Cno='2' AND SC.Grade>90;
/*表示类别看似是数字但不是数字的字符串用单引号*/

/*查询所有学生、姓名、课程名、成绩*/
/*多个表之间的连接*/
SELECT Student.Sno,Sname,Cname,Grade FROM Student,SC,Course
WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno;

嵌套查询

带IN谓词的子查询

/*查询和刘晨同一院系的学生*/
/*way1*/
SELECT Sno,Sname,Sdept FROM Student
WHERE Sdept in (SELECT Sdept FROM Student WHERE Sname ='刘晨')

/*way2*/
SELECT S1.Sno,S1.Sname,S1.Sdept FROM Student S1,Student S2
WHERE S1.Sdept = S2.Sdept
				AND S1.Sname='刘晨' 
/*先进行笛卡尔积，再选择出与刘晨连接的人，即为同系*/

带比较运算符的子查询

相当于两层for循环

更优的方法是先生成平均值

带ANY（SOME）或ALL的谓词的子查询

eg1：先选出计算机系的所有年龄集合，比所有年龄还小的年龄

eg2：先直接给出最大年龄，不大于最大年龄的年龄

带EXISTS谓词的子查询

所有带IN、比较、ANY、ALL的子查询都能用EXISTS语句进行等价替换

如果找到存在，则NOT EXISTS 返回false，名字不会被选出

如果不存在，返回true，名字会被选出来，则得到结果

集合查询

UNION

/*查询选修了课程1或2的学生*/
SELECT Sno FROM SC WHERE Cno='1'
UNION
SELECT Sno FROM SC WHERE Cno='2';

SELECT Sno FROM SC
WHERE Cno='1' OR Cno='2';

INTERSECT

/*同时选择课程1和2的学生*/
SELECT Sno FROM SC WHERE Cno='1'
INTERSECT
SELECT Sno FROM SC WHERE Cno='2';

SELECT Sno FROM SC
WHERE Cno='1' AND Cno='2';

EXCEPT

/*查询计算机系学生和年龄不大于19岁学生的差集*/
SELECT * FROM Student WHERE Sdept='CS'
EXCEPT
SELECT * FROM Student WHERE Sage<=19;