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语言