ChⅢ-数据库操作SQL语言

ChⅢ-数据库操作SQL语言

SQL 语句

  • 数据定义语言(DDL):创建修改删除数据库对象;create/drop/alter database/table/index
  • 数据操纵语言(DML):增删改数据;insert/update/delete
  • 数据查询语言(DQL):数据查询;
  • 数据控制语言(DCL):数据库对象访问控制;grant/deny/revoke
  • 事物处理语言(TPL):事物处理;begin transaction/commit/rollback
  • 游标控制语言(CCL):游标操作;declare cursor/fetch into/close curso

DDL

数据定义语言(Data Definition Language,DDL)是 SQL 语言中用于创建、修改或删除数据库对象的语句。

Database

SQL
-- databaseCREATE DATABASE db_name;ALTER DATABASE db_name RENAME TO new_db_name;DROP DATABASE db_name;

Table

SQL
-- tableCREATE TABLE table_name (    Sname varchar(10) NOT NULL,    Age int NOT NULL,    Sid char(10) PRIMARY KEY,);'''完整性约束包括:PRIMARY KEY 主键NOT NULL 非空NULL 空值UNIQUE 值唯一CHECK 有效性检查DEFAULT 缺省''''''假定创建一个表,City字段只能取值上海、北京,age 默认为 10'''CREATE TABLE TEST(    City varchar(10) CHECK(City IN('上海','北京')),    Age int NOT NULL DEFAULT 10);ALTER TABLE <表名> ADD <新列名称><数据类型>|[完整性约束] ALTER TABLE<表名> DROP  COLUMN <列名>ALTER TABLE<表名> DROP  CONSTRAINT<完整性约束名>ALTER TABLE <表名> RENAME TO <新表名>ALTER TABLE <表名> RENAME <原列名> TO <新列名>ALTER TABLE <表名> ALTER  COLUMN <列名> TYPE<新的数据类型>DROP TABLE <表名>;

Primary/Foreign Key Constraint

Primary Key Constraint syntax

  • 定义单列主键
    Age int PRIMARY KEY
  • 定义多列主键
    CONSTRAINT <constraint_name> PRIMARY KEY <col1,col2...>
  • 定义代理键时,注意主键应为 serial 类型
SQL
CREATE TABLE TEST(    City varchar(10) PRIMARY KEY,    Age int PRIMARY KEY);CREATE TABLE TEST(    Name varchar(10),    Age int,    CONSTRAINT PK_TEST PRIMARY KEY(Name,Age));CREATE TABLE TEST(    Idx serial PRIMARY KEY,    Name varchar(10),    Age int);

Foreign Key Constraint
为了成功创建外键,以下条件必须满足:

  • 引用的表(在 REFERENCES 后面指定的表)必须已经存在。
  • 被引用的字段必须是父表的主键或具有唯一性约束。
  • 数据类型必须兼容,即外键字段和被引用字段的类型必须相同或可转换。
SQL
CREATE TABLE parent_table (    id SERIAL PRIMARY KEY,    name VARCHAR(10))CREATE TABLE child_table (    id SERIAL PRIMARY KEY,    name VARCHAR(10),    parent_id INTEGER,    FOREIGN KEY (parent_id) REFERENCES parent_table(id));-- ON DELETE CASCADEALTER TABLE    child_tableADD CONSTRAINT    fk_parent_id FOREIGN KEY (parent_id)REFERENCES parent_table(id)ON DELETE CASCADE;

在 SQL 中,特别是像 MySQL 和 PostgreSQL 这样的关系型数据库管理系统中,外键不仅提供了数据完整性,还可以配置不同的行为,例如 ON DELETE 和 ON UPDATE 规则,来决定当父表中的记录被删除或更新时,子表中的关联记录应该如何处理,常见的 ON DELETE 和 ON UPDATE 规则包括:

  • RESTRICT 或 NO ACTION
    这是默认行为。如果尝试删除或更新主表中的记录,而子表中存在依赖这个记录的外键,那么操作会被阻止。这确保了参照完整性,防止了意外的数据丢失。
  • CASCADE:
    当主表中的记录被删除或更新时,所有关联的子表记录也将被相应地删除或更新。这样,两个表之间的数据保持一致。
  • SET NULL:
    如果主表中的记录被删除或更新,子表中对应的外键字段将被设置为 NULL。这要求外键字段在定义时允许为 NULL。
  • SET DEFAULT:
    设置外键在主键被删除或更新时,将子表中的外键字段设为其默认值。
    请注意,SET DEFAULT 在 MySQL 中不支持,
SQL
'''管理约束constraint_type指明了约束的类型,比如FOREIGN KEY、UNIQUE、PRIMARY KEY等。'''ALTER TABLE    table_nameADD CONSTRAINT    constraint_name constraint_type(column_name, ...);ALTER TABLE    child_tableADD CONSTRAINT IF NOT EXISTS    fk_parent_id FOREIGN KEY (parent_id)REFERENCES    parent_table(id)    ON DELETE CASCADE;ALTER TABLE child_table DROP CONSTRAINT fk_parent_id;

Index

索引(index):将关系表按照指定列的取值顺序组织元组数据的数据结构,加快查询,占用额外存储空间、开销较大

SQL
CREATE INDEX Birthday_Idx ON STUDENT(Birthday);ALTER INDEX Birthday_Idx RENAME TO Bday_Idx;DROP INDEX bday_idx;

DML

DML:数据操作语言,用于对数据进行 CRUD(Create, Retrieve, Update, Delete)

Ref: DataBase01

INSERT, UPDATE, DELETE

SQL
-- insertINSERT INTO  <表名|视图名>[<列名表>]  VALUES (列值表);INSERT INTO Student VALUES('2017220101105','柳因','女','1999-04-23','软件工程','[email protected]')-- updateUPDATE    <表名|视图名>SET    <列名1>=<表达式1> [,<列名2>=<表达式2>...][WHERE <条件表达式>];UPDATE    StudentSET    Email='[email protected]'WHERE    StudentName='赵东';-- deleteDELETE FROM    <表名|视图名>[WHERE <条件表达式>]DELETE FROM    StudentWHERE    StudentName='赵东';

DQL

SQL
SELECT  [ALL|DISTINCT]  <目标列>[,<目标列>…][ INTO <新表> ]FROM  <表名|视图名>[,<表名|视图名>…][ WHERE  <条件表达式> ][ GROUP BY  <列名> [HAVING <条件表达式> ]][ ORDER BY  <列名> [ ASC | DESC ] ];-- BETWEEN AND 限制列值范围SELECT  *FROM  STUDENTWHERE BirthDay BETWEEN 2000-01-01 AND 2000-12-30’;-- LIKE 通配符'_'代表一个字符'%'代表一个或多个字符SELECT  *FROM  STUDENTWHERE  Email  LIKE  '%@163.com';-- AND\OR\NOT 逻辑运算符SELECT  StudentID, StudentName, StudentGender, MajorFROM  STUDENTWHERE  Major='软件工程'  AND  StudentGender='男';-- IN 限定范围SELECT  StudentID, StudentName, StudentGender, MajorFROM  STUDENTWHERE  Major IN  ('CS','SE');-- ORDER BY <> ASC/DESC 默认升序ASC-- 多个列排序时,只有当前列相同时才会比较下一列SELECT  *FROM  STUDENTORDER  BY  Birthday DESC ,  StudentName  ASC;-- 内置函数SELECT  COUNT(* AS  学生人数FROM  Student;SELECT  Min(Birthday) AS 最大年龄,Max(Birthday) AS 最小年龄FROM  Student;-- GROUP BY <> HAVING-- 专业统计STUDENT表中男生人数,但限定只显示人数大于2的人数SELECT  Major  AS 专业,  COUNT(StudentID) AS 学生人数FROM  StudentWHERE  StudentGender=’男’GROUP  BY  MajorHAVING  COUNT(*)>2;

多表关联查询

子查询
SQL
SELECT  TeacherID, TeacherName, TeacherTitleFROM  TeacherWHERE  CollegeID  IN        (SELECT  CollegeID         FROM  College         WHERE  CollegeName='计算机学院');
连接查询
SQL
SELECT    B.CollegeName AS 学院名称,  A.TeacherID  AS 编号, A.TeacherName  AS 姓名,  A.TeacherGender  AS 性别,  A. TeacherTitle  AS 职称FROM    Teacher  AS  A,College  AS  BWHERE    A.CollegeID=B.CollegeIDORDER BY    B.CollegeName, A.TeacherID;-- JOIN ON 内连接SELECT    B.CollegeName AS 学院名称,  A.TeacherID  AS 编号, A.TeacherName  AS 姓名,  A.TeacherGender  AS 性别,  A. TeacherTitle  AS 职称FROM    TEACHER  AS  A  JOIN  COLLEGE  AS  BON    A.CollegeID=B.CollegeIDORDER BY    B.CollegeName, A.TeacherID;-- LEFT JOIN/RIGHT JOIN/FULL JOIN 外连接SELECT    C.CourseName AS 课程名称, T.TeacherName AS 教师,COUNT (R.CoursePlanID)  AS 选课人数FROM    COURSE  AS  CJOIN    PLAN  AS  P    ON C.CourseID=P.CourseIDJOIN    TEACHER  AS  T    ON P.TeacherID=T.TeacherIDLEFT  JOIN    REGISTER  AS  R    ON  P.CoursePlanID=R.CoursePlanIDGROUP BY    C.CourseName, T.TeacherName;

ChⅢ-数据库操作SQL语言

https://vluv.space/Ch3-DbSQL/

作者

GnixAij

发布于

2024-03-27

更新于

2025-08-12

许可协议

评论