1. SQL 概述SQL 的五个特点⑴ 综合统一DDL, DML, DCL⑵ 高度非过程化⑶ 面向集合的操作方式⑷ 以同一种语法结构提供两种使用方式——既是自含式语言又是嵌入式语言⑸ 语言简捷易学易用。使用的动词SQL 功能动词数据定义 DDLCREATE、DROP、ALTER数据查询 DQLSELECT数据更新 DMLINSERT、UPDATE、DELETE数据控制 DCLGRANT、REVOKE动词的类别决定了它改的是结构还是数据——DDL 三个动词动的是对象结构库、表、视图、索引DML 三个动词动的是表里的行。第一部分全是 DDL。要点课件原文通常 SQL 语言中不区分大小写部分数据库提供参数可配置——指的是关键字库名/表名在 Linux 上的 MySQL 默认是区分大小写的。2. 数据定义总览与库对象层次操作对象创建删除修改库CREATE DATABASEDROP DATABASEALTER DATABASE表CREATE TABLEDROP TABLEALTER TABLE视图CREATE VIEWDROP VIEWALTER VIEW索引CREATE INDEXDROP INDEX—索引没有修改见 [[2 索引]]名称MySQL课件 16 页PostgreSQL课件 19 页数据库集群 cluster可包含 1 个或多个数据库可包含 1 个或多个数据库数据库 database可包含 1 个或多个基本表可包含 1 个或多个模式模式 schema等于 databaseMySQL 中两者等价包含表/视图/索引默认public另有系统模式pg_catalog系统表、内置数据类型与函数表 table存放数据的基本表存放数据的基本表表空间 tablespaceInnoDB共享表空间所有数据放一个表空间里可跨多个文件/ 独立表空间默认每个表独立索引与数据分离逻辑上分出的存储单元可按快慢盘分别放置频繁用的索引放快盘、归档表放慢盘MySQL 的库就是模式PG 的库里还能再分模式。所以USE 库名只是 MySQL 的写法PG 里没有。基本表base table本身独立存在的表CREATE TABLE建出来的就是它——一个关系对应一个基本表数据实际存在它里面与之相对的是视图虚表只存定义不存数据。模式schema本课这个词有两层意思——① 教材三级模式里的模式概念模式指数据库中全体数据的逻辑结构和特征是所有用户的公共数据视图② DBMS 实现里的schema指一组数据库对象表、视图、索引的命名容器上表说的MySQL 里 schema database是第二层意思。DATABASE 和 TABLE 的区别库是容器表是容器里一张二维表。一个库能装多张表一张表只能属于一个库所以要先USE 库名再建表CREATE DATABASE建容器CREATE TABLE建容器里的表。3. 建库CREATE / 查看 / ALTER / DROP DATABASE课件 22~23 页-- ① 建库课件 22 页CREATEDATABASEstudent;-- 课件原例最简形态CREATEDATABASEstudent-- 把字符集和排序规则一次交代清楚DEFAULTCHARACTERSETutf8mb4-- 字符集 utf8mb4DEFAULTCOLLATEutf8mb4_0900_ai_ci;-- 排序规则 utf8mb4_0900_ai_ci_ci 不区分大小写-- ② 查看课件 23 页查看数据库的信息SHOWDATABASES;-- 列出服务器上所有库SHOWCREATEDATABASEstudent;-- 看这个库的建库语句字符集、排序规则在这里USEstudent;-- 切到该库此后不带库名的表都建在它下面SELECTDATABASE();-- 确认当前在哪个库-- ③ 改库课件 23 页原例ALTERDATABASEmydbREADONLY0-- 0 可读写 / 1 只读DEFAULTCOLLATEutf8mb4_bin;-- _bin 按字节比较区分大小写-- ④ 删库课件 23 页原例DROPDATABASEIFEXISTSstudent;-- IF EXISTS库不存在时只警告不报错基础操作比较简单要点改库只能改库级选项只读开关、排序规则等改不了库名DROP DATABASE连库里的表一起删是不可回滚的 DDL。4. 建表CREATE TABLE4.1 语法与两类约束CREATETABLE表名(列名数据类型[列级完整性约束],[列名数据类型[列级完整性约束]]…,[表级完整性约束]);列级完整性约束条件只涉及一个属性列的约束。表级完整性约束条件涉及一个或多个属性列的约束组合主码、组合外码只能写这里。4.2 数据类型-- 整数类型 字节数 说明 范围smallint-- 2 小范围整数 -32768 ~ 32767int-- 4 常用的整数 -2^31 ~ 2^31-1bigint-- 8 大范围整数 -2^63 ~ 2^63-1-- 浮点与定点类型 字节数 说明decimal(m,n)-- 可变长 用户指定的精度精确总共 1~65 位小数点后 0~30 位numeric(m,n)-- 可变长 MySQL 中等于 decimalfloat-- 4 可变精度不精确约 ±10^387 位有效数字-- 例decimal(5,2) 的范围是 -999.99 ~ 999.99-- 字符类型 作用char(n)-- 定长字符数据不足补空白varchar(n)-- 变长字符数据有长度限制text-- 变长字符数据无长度限制-- 日期类型date-- 日期 2021-01-01范围 1000-01-01 ~ 9999-12-31datetime-- 日期时间 2021-01-01 10:10:29范围 1000-01-01 00:00:00.000000 ~ 9999-12-31 23:59:59.9999994.3 完整性约束-- 课件列出的约束Primary key, Foreign key, Unique, Null, default, CHECK, auto_incrementCREATETABLEperson(idINTNOTNULLAUTO_INCREMENTPRIMARYKEY,-- 主码 自动增长nameVARCHAR(8),-- [Null]不写 NOT NULL 就是允许空INDEXix_person_name(name)-- 建表时顺带建索引);这些完整性约束条件被存入系统的数据字典中。约束含义列级表级PRIMARY KEY主码非空且唯一每表一个✅✅组合主码只能写这里FOREIGN KEY … REFERENCES外码取值必须是被参照表里已有的值✅✅NOT NULL不允许为空✅—UNIQUE取值唯一主码之外还想唯一的列✅✅DEFAULT 值不给值时用什么✅—CHECK (条件)取值要满足条件✅✅AUTO_INCREMENT整数列自动加一MySQL✅—总体约束是写在建表语句里的规则由 DBMS 存进数据字典并在每次增删改时自动检查。大思路这些约束正对应教材里的三类完整性——主码实体完整性外码参照完整性其余非空/唯一/CHECK/默认用户定义完整性。要点PRIMARY KEY本身已经含NOT NULL列上再写一遍NOT NULL课件例一就是这么写的不报错但多余。4.4 建表例题-- [例1]课件 28 页建立学生表 StudentCREATETABLEStudent(SnoCHAR(8)NOTNULL,-- 学号不能为空SnameCHAR(20)UNIQUE,-- 姓名取值唯一MySQL 会自动建一个名为 Sname 的唯一索引SgenderCHAR(6),-- 性别SbirthdateDATE,-- 出生日期SmajorCHAR(40),-- 所在系PRIMARYKEY(Sno)-- 主码写成了表级约束);-- [例2]课件 29 页建立学生选课表 SCCREATETABLESC(SnoCHAR(8),-- 学号CnoCHAR(3),-- 课程号GradeINT,-- 成绩PRIMARYKEY(Sno,Cno),-- 组合主码一个学生一门课只有一条记录FOREIGNKEY(Sno)REFERENCESS(Sno),-- 外码①学号必须在 S 表中存在FOREIGNKEY(Cno)REFERENCESC(Cno)-- 外码②课程号必须在 C 表中存在);语法上要求什么能不能建成被参照的表S必须已经存在S里必须有Sno这一列而且这一列要是S的主码或候选码MySQL 里就是这一列上必须有主键或唯一索引随便一个普通列不能被引用两边类型也要相容不满足的话建表时直接报错。数据上要求什么插数据时的检查SC里每一行的Sno取值必须在S表里找得到对应的一行——插入或修改SC时 DBMS 会去S表核对找不到就报错。反方向不要求S里的学生可以一行选课记录都没有。所以不是一一对应是多对一SC.Sno可以重复一个学生选多门课S里同一个Sno也可以被引用多次、或者一次都不被引用。它保证的只有一件事——不会出现选了不存在的学生的课。外码列没写NOT NULL时允许为空为空的行不检查NULL不指向任何一行。5. 改表与删表5.1 改表ALTER TABLE-- 课件教材给的语法格式ALTERTABLE表名[ADD新列名数据类型[完整性约束]]-- 增加新列和新的完整性约束条件[ADD完整性约束名列名][DROP完整性约束名列名]-- 删除指定列或列的完整性约束条件[ALTERCOLUMN列名数据类型];-- 修改列名和数据类型-- MySQL 8.0 上实际能执行的动作一一对应ALTERTABLEStudentADDCOLUMNSemailVARCHAR(30);-- 加列ALTERTABLEStudentMODIFYCOLUMNSbirthdateVARCHAR(20);-- 改列的数据类型ALTERTABLEStudent CHANGECOLUMNSbirthdate SbirthVARCHAR(20);-- 改列名 改数据类型ALTERTABLEStudentDROPCOLUMNSemail;-- 删列总体四类动作——加列、改列、删列、改约束。注意课件的ALTER COLUMN是教材/标准写法MySQL 里要写成MODIFY COLUMN改类型或CHANGE COLUMN改列名类型理论课按课件写上机按 MySQL 写。整列重定义的意思MODIFY COLUMN 列 新定义是把这一列的定义整条换成你写的内容不是只替换你写了的那几项没写出来的属性一律回到默认值可空、无默认值而且不报错CREATETABLEt(aVARCHAR(5)NOTNULLDEFAULTx,bINT);ALTERTABLEtMODIFYCOLUMNaVARCHAR(10);-- 只想把长度改成 10DESCt;-- 结果 a 变成 NullYES、DefaultNULLNOT NULL 和 DEFAULT 都丢了-- 要改就把完整定义重写一遍ALTERTABLEtMODIFYCOLUMNaVARCHAR(10)NOTNULLDEFAULTx;类型转换不合法的情况确实有执行ALTER时 DBMS 要把每一行的旧值按新类型重转一遍转不过去的典型是——变长改短VARCHAR(20)→VARCHAR(5)严格模式下报ERROR 1406 Data too long、字符转数字abc转int报ERROR 1265或被截成 0、日期字符串格式不对以及改被外码引用的列外码两边类型必须一致直接报错。碰到了怎么办① 先SELECT找出会被破坏的行如WHERE CHAR_LENGTH(a) 5② 备份或先清理CREATE TABLE t_bak AS SELECT * FROM t;③ 再执行ALTER④ 改完用DESCSELECT复查。——课件例4 那句注修改原有的列定义有可能会破坏已有数据说的就是这件事。5.2 例三-- [例3] 向学生表增加邮箱地址列数据类型为字符型ALTERTABLEStudentADDSemailVARCHAR(30);-- 课件注不论基本表中原来是否已有数据新增加的列一律为空值-- [例4] 将 student 表中出生日期的数据类型改为字符型ALTERTABLEStudentALTERCOLUMNSbirthdateVARCHAR(20);-- 课件注修改原有的列定义有可能会破坏已有数据-- [例5] 增加学生名称必须取唯一值的约束条件ALTERTABLEStudentADDUNIQUE(Sname);-- [例6] 删除学生姓名必须取唯一值的约束ALTERTABLEStudentDROPCONSTRAINTIX_sname;5.3 删表DROP TABLE课件 32 页DROPTABLEStudent;-- [例7] 删除 Student 表要点表被别的表的外码引用时直接删会报错得先处理子表或外码上机脚本里为可重复执行通常写DROP TABLE IF EXISTS Student;。被外码引用就删不掉是什么意思如果别的表子表如SC上有外码指向StudentDROP TABLE Student;会被 DBMS 拒绝MySQL 报ERROR 3730 Cannot drop table student referenced by a foreign key constraint——删了之后子表的外码就指向不存在的数据参照完整性被破坏。想删就得先处理子表先删子表DROP TABLE SC;或先删子表上的外码ALTER TABLE SC DROP FOREIGN KEY 外码名;再删父表。注意建表时写的ON DELETE CASCADE只管删行、管不了删表标准 SQL 的DROP TABLE Student CASCADE;在 MySQL 里能写但不生效。除了被外码引用删不掉的常见原因就只剩权限不够当前用户没有这张表的DROP权限。IF EXISTS的作用表不存在时直接DROP会报错MySQL 报ERROR 1051 Unknown table加了IF EXISTS就只给一条警告、脚本继续往下跑——让建表脚本能反复执行。另外DROP TABLE属于 DDL删了不能回滚。