SQL笔记
MARCUS_YANG
SQL基本概念
SQL(Structured Query Language)结构化查询语言,是关系数据库的标准语言特点:
SELECT Sno, Grade
FROM SC
WHERE Cno= '3'
ORDER BY Grade DESC;
//从表 SC 中选取学号 Sno 和成绩 Grade 满足条件课程号 Cno=“3”按照成绩 Gradel 降序排列
- 综合统一:
- 集数据定义语言(DDL),数据操纵语言(DML),数据控制语言(DCL)功能于一体。
- 定义和修改、删除关系模式,定义和删除视图,插入数据,建立数据库;
- 对数据进行增删改查
- 数据库的重构和维护
- 数据库安全性、完整性控制,以及事务控制
- 嵌入式和动态SQL定义
- 高度非过程化:SQL 只要提出“做什么”,无须了解存取路径。
- 面向集合的操作方式:
- 非关系数据模型采用面向记录的操作方式,操作对象是一条记录
- 采用集合操作方式
- 操作对象、查找结果可以是元组的集合
- 一次插入、删除、更新操作的对象可以是元组的集合
- 以同一种语法结构提供多种使用方式:独立使用和嵌入高级语言中
- 语言简洁,易学易用
![a7dc2a4fad95fe554ded5104537157aa.png](https://i-blog.csdnimg.cn/blog_migrate/235e70cbaac574fd6db1aa085dead777.jpeg)
- SQL支持关系数据库三级模式结构:
![8ddcfe6fcce102ab06be700f0c7f5ef3.png](https://i-blog.csdnimg.cn/blog_migrate/e1cfb471b1db994c5b3bacf79d16b039.jpeg)
-
- 基本表:
- 本身独立存在的表
- SQL 中一个关系就对应一个基本表
- 一个(或多个)基本表对应一个存储文件
- 一个表可以带若干索引
- 存储文件:
- 逻辑结构组成了关系数据库的内模式
- 物理结构对用户来说是隐蔽的
- 基本表:
-
- 视图:
- 用户可以在视图上再定义视图
- 视图是一个虚表
- 数据库中只存放视图的定义而不存放视图对应的数据
- 从一个或几个基本表导出的表
- 视图:
贯彻这门课的三个样例表:
student table:
SNO 学号Sname 姓名Ssex 性别Sage 年龄Sdept 系17150011001MarcusMA21CS
Course表
Cno课程号Cname 课程名Cpno 先修课Ccredit 学分1数据库542数学NULL23信息系统144操作系统635数据结构746数据处理NULL27PASCAL64
学生课程表SC
Sno 学号Cno 课程号Grade 成绩171500110011100
数据定义
SQL 的数据定义功能:定义各种数据库的“对象”
- 模式定义
- 表定义
- 视图定义
- 索引定义
- [ ]
![a6d72a33090535adaad996ce2b629d62.png](https://i-blog.csdnimg.cn/blog_migrate/0ceb5ffff67e0eba73038f22859bfff5.jpeg)
定义模式:
example: 定义模式实际上定义了一个命名空间(或者说目录)
[例3.1] 为用户WANG定义一个学生-课程模式S-T
CREATE SCHEMA “S-T” AUTHORIZATION WANG;
[例3.2] CREATE SCHEMA AUTHORIZATION WANG;
该语句没有指定<模式名,<模式名隐含为<用户名
- 在 CREATE SCHEMA 中可以接受 CREATETABLE CREATE VIEW 和 GRANT 子句。
- [例3.3]为用户ZHANG创建了一个模式TEST,并且在其中定义一个表 TAB1CREATE SCHEMA TEST AUTHORIZATION ZHANGCREATE TABLE TAB1(COL1 SMALLINT,
COL2 INT,
COL3 CHAR(20),
COL4 NUMERIC(10,3),
COL5 DECIMAL(5,2)
);
删除模式
DROP SCHEMA <模式名<CASCADE|RESTRICT
- CASCADE删除模式的同时把该模式中所有的数据库对象全部删除
- RESTRICT如果该模式中定义了下属的数据库对象(如表、视图等),则拒绝该删除语句的执行。仅当该模式中没有任何下属的对象时才能执行。
[例3.4] DROP SCHEMA ZHANG CASCADE;
删除模式ZHANG
同时该模式中定义的表TAB1也被删除
基本表的定义
![a01117488ee7abe3c63e03146a5f6027.png](https://i-blog.csdnimg.cn/blog_migrate/fba104d71b999d889ea340c73321d8f7.jpeg)
- 列级完整性约束条件:涉及相应属性列的完整性约束条件
- 表级完整性约束条件:涉及一个或多个属性列的完整性约束条件
- 如果完整性约束条件涉及到该表的多个属性列,则必须定义在表级上。
[例3.5] 建立“学生”表Student。学号是主码,姓名取值唯一。
CREATE TABLE Student
(Sno CHAR(9) PRIMARY KEY,
/* 列级完整性约束条件,Sno是主码*/
Sname CHAR(20) UNIQUE,
Ssex CHAR(2),
Sage SMALLINT,
Sdept CHAR(20)
);
[例3.6 ] 建立一个“课程”表Course
CREATE TABLE Course
(Cno CHAR(4) PRIMARY KEY,
Cname CHAR(40),
Cpno CHAR(4),/*先修课*/
Ccredit SMALLINT,
FOREIGN KEY (Cpno) REFERENCES Course(Cno)/*Cpno是外码,被参照表是Course,被参照列是Cno*/
);
[例3.7] 建立一个学生选课表SC
CREATE TABLE SC
(Sno CHAR(9),
Cno CHAR(4),
Grade SMALLINT,
PRIMARY KEY (Sno,Cno),/* 主码由两个属性构成,必须作为表级完整性进行定义*/
FOREIGN KEY (Sno) REFERENCES Student(Sno),/* 表级完整性约束条件,Sno是外码,被参照表是Student */
FOREIGN KEY (Cno)REFERENCES Course(Cno)/* 表级完整性约束条件, Cno是外码,被参照表是Course*/
);
数据类型
关系模型中“域”的概念:
MYSQL:https://www.runoob.com/mysql/mysql-data-types.html
作为SQL标准的扩展,MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。
类型大小范围(有符号)范围(无符号)用途TINYINT1 byte(-128,127)(0,255)小整数值SMALLINT2 bytes(-32 768,32 767)(0,65 535)大整数值MEDIUMINT3 bytes(-8 388 608,8 388 607)(0,16 777 215)大整数值INT或INTEGER4 bytes(-2 147 483 648,2 147 483 647)(0,4 294 967 295)大整数值BIGINT8 bytes(-9,223,372,036,854,775,808,9 223 372 036 854 775 807)(0,18 446 744 073 709 551 615)极大整数值FLOAT4 bytes(-3.402 823 466 E+38,-1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38)0,(1.175 494 351 E-38,3.402 823 466 E+38)单精度 浮点数值DOUBLE8 bytes(-1.797 693 134 862 315 7 E+308,-2.225 073 858 507 201 4 E-308),0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308)0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308)双精度 浮点数值DECIMAL对DECIMAL(M,D) ,如果MD,为M+2否则为D+2依赖于M和D的值依赖于M和D的值小数值
日期和时间类型
表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
每个时间类型有一个有效值范围和一个"零"值,当指定不合法的MySQL不能表示的值时使用"零"值。
TIMESTAMP类型有专有的自动更新特性,将在后面描述。
类型大小 ( bytes)范围格式用途DATE31000-01-01/9999-12-31YYYY-MM-DD日期值TIME3'-838:59:59'/'838:59:59'HH:MM:SS时间值或持续时间YEAR11901/2155YYYY年份值DATETIME81000-01-01 00:00:00/9999-12-31 23:59:59YYYY-MM-DD HH:MM:SS混合日期和时间值TIMESTAMP41970-01-01 00:00:00/2038结束时间是第 2147483647 秒,北京时间 2038-1-19 11:14:07,格林尼治时间 2038年1月19日 凌晨 03:14:07YYYYMMDD HHMMSS混合日期和时间值,时间戳
字符串类型
字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。
类型大小用途CHAR0-255 bytes定长字符串VARCHAR0-65535 bytes变长字符串TINYBLOB0-255 bytes不超过 255 个字符的二进制字符串TINYTEXT0-255 bytes短文本字符串BLOB0-65 535 bytes二进制形式的长文本数据TEXT0-65 535 bytes长文本数据MEDIUMBLOB0-16 777 215 bytes二进制形式的中等长度文本数据MEDIUMTEXT0-16 777 215 bytes中等长度文本数据LONGBLOB0-4 294 967 295 bytes二进制形式的极大文本数据LONGTEXT0-4 294 967 295 bytes极大文本数据
注意:char(n) 和 varchar(n) 中括号中 n 代表字符的个数,并不代表字节个数,比如 CHAR(30) 就可以存储 30 个字符。
修改基本表
- ALTER TABLE <表名
- [ADD[COLUMN] <新列名 <数据类型 [ 完整性约束 ] ]
- [ADD <表级完整性约束]
- [DROP [ COLUMN ] <列名 [CASCADE| RESTRICT] ]
- [DROP CONSTRAINT<完整性约束名[ RESTRICT | CASCADE ] ]
- [ALTER COLUMN <列名<数据类型 ] ;
- ADD子句用于增加新列、新的列级完整性约束条件和新的表级完整性约束条件
- DROP COLUMN子句用于删除表中的列
- CASCAD:则自动删除引用了该列的其他对象
- RESTRICT:如果该列引用了其他对象,则关系数据库解决删除
- DROP CONSTRAINT子句用于删除指定的完整性约束条件
- ALTER COLUMN子句用于修改原有的列定义,包括修改列名和数据类型
- [例3.8] 向Student表增加“入学时间”列,其数据类型为日期型
ALTER TABLE Student ADD S_entrance DATE; - [例3.9] 将年龄的数据类型由字符型(假设原来的数据类型是字符型)改为整数。
ALTER TABLE Student ALTER COLUMN Sage INT;[例3.10] 增加课程名称必须取唯一值的约束条件。
ALTER TABLE Course ADD UNIQUE(Cname);
删除基本表
- DROP TABLE <表名[RESTRICT| CASCADE&