两年前的学科笔记,直接转载本人印象笔记上的内容,如若侵权请联系我
SQL语言基本数据类型
-
字符:CHAR、VARCHAR、TEXT
-
char类型为固定长度的字符串,储存字母+数字+符号常用,最大长度为255
-
varchar类型是长度可变的字符串,储存汉字常用,最大长度255
-
text, 存放最大长度为 65,535 个字符的字符串。
-
整数:SMALLINT、INTEGER(INT)、BIGINT
-
int, 长整型,带符号范围-2147483648到2147483647
-
smallint,短整形, 带符号范围-32768到32767,无符号0到65535
-
bigint,大整形, 带符号的范围是-9223372036854775808到9223372036854775807
-
浮点数:NUMBER(n,d)、FLOAT(n,d)
-
FLOAT(size,d), 带有浮动小数点的小数字。在 size 参数中规定显示最大位数。在 d 参数中规定小数点右侧的最大位数。也可直接FLOAT(d)
-
NUMBER(size,d),带有浮动小数点的小数字, 123.89- NUMBER ( 3 )-124, 123.89- NUMBER ( 6 , 2 )- 123.89, 123.89 - NUMBER ( 3 )-123.9, 123.89 - NUMBER (4,2 )-123.9- exceeds precision ( 有效位为 5 , 5 > 4 ), 123.89- NUMBER ( 6 , - 2 )-100
-
日期:DATE、DATETIME、TIME
-
DATE(), 日期。格式:YYYY-MM-DD
-
DATETIME(), 日期和时间的组合。格式:YYYY-MM-DD HH:MM:SS
-
TIME(), 时间。格式:HH:MM:SS
-
货币:MONEY
-
money型数据可以存储从-922,337,203,685,477.5808到922, 337,203,685,477.5807的钱数。
数据定义SQL语句
数据库相关
-
CREATE DATABASE <数据库名>;
-
例 CREATE DATABASE CourseDB;
-
ALTER DATABASE <数据库名> <修改内容>;
-
例 将选课管理数据库CourseDB名称修改为 CourseManageDB: ALTER DATABASE CourseDB RENAME TO CourseManageDB;
-
DROP DATABASE <数据库名>;
-
例 DROP DATABASE CourseManageDB;
数据库表相关
-
数据库表创建
CREATE TABLE <表名>( <列名1> <数据类型> [列完整性约束],<列名2> <数据类型> [列完整性约束],<列名3> <数据类型> [列完整性约束],… );
列完整性约束关键词
-
PRIMARY KEY——主键
-
NOT NULL——非空值(必填)
-
NULL——空值(非必填)
-
UNIQUE——值唯一
-
CourseName varchar(20) NOT NULL UNIQUE,
-
CHECK——有效性检查(值在范围中)
-
CourseType varchar(10) NULL CHECK(CourseType IN('基础课','专业课','选修课')
-
DEFAULT——缺省值(默认值)
-
TestMethod char(4) NOT NULL DEFAULT '闭卷考试'
表约束定义主键(多个主键处理)
CREATE TABLE <表名>( <列名1> <数据类型> [列完整性约束],<列名2> <数据类型> [列完整性约束],<列名3> <数据类型> [列完整性约束],…CONSTRAINT <约束名> PRIMARY Key(主键列) );
例CREATE TABLE Plan( CourseID char(4) NOT NULL,TeacherID char(4) NOT NULL,CourseRoom varchar(30),CourseTime varchar(30),Note varchar(50),CONSTRAINT CoursePlan_PK(数据库名) PRIMARY Key(CourseID,TeacherID) );
使用表约束定义主键的优点:
-
便于定义复合主键
-
可命名主键约束
-
便于定义代理键
表约束定义代理键
CREATE TABLE <表名>( <代理键列名> <Serial数据类型> NOT NULL,<列名2> <数据类型> [列完整性约束],<列名3> <数据类型> [列完整性约束],…CONSTRAINT <约束名> PRIMARY Key(代理键列名) );
例CREATE TABLE Plan(CoursePlanID serial NOT NULL,CourseID char(4) NOT NULL,TeacherID char(4) NOT NULL,CourseRoom varchar(30),CourseTime varchar(30),Note varchar(50),CONSTRAINT CoursePlan_PK PRIMARY Key(CoursePlanID) );
serial数据类型:自动编号
表约束定义外键
CREATE TABLE <表名> (<列名1> <数据类型> [列完整性约束],<列名2> <数据类型> [列完整性约束],<列名3> <数据类型> [列完整性约束],…CONSTRAINT <约束名> FOREIGN Key(外键列) );
例CREATE TABLE Register(CourseRegID serial NOT NULL,CoursePlanID Int NOT NULL,StudentID char(13),Note varchar(30),CONSTRAINT CourseRegID_PK PRIMARY Key(CourseRegID),CONSTRAINT CoursePlanID_FK FOREIGN Key(CoursePlanID)REFERENCES Plan(CoursePlanID) //键所在的表ON DELETE CASCADE, //联级删除,即原表中删除了此处也会删除CONSTRAINT StudentID_FK FOREIGN KEY(StudentID)REFERENCES Student(StudentID) //键所在的表ON DELETE CASCADE );//联机删除
-
数据库表修改
1.语句基本格式
ALTER TABLE <表名> <修改方式>;
2.主要语句类型
ADD修改方式,用于增加新列或列完整性约束、ALTER TABLE <表名> ADD <新列名称><数据类型>|[完整性约束];ALTER TABLE <表名> ADD <完整性约束名>(列名);ALTER TABLE <表名> ADD 表级完整性约束条件;
DROP修改方式,用于删除指定列或列的完整性约束条件
ALTER TABLE<表名> DROP COLUMN <列名>;ALTER TABLE<表名> DROP CONSTRAINT<完整性约束名>;ALTER TABLE<表名> DROP COLUMN <列名> CASCADE;引用了该列的其他对象一起删除ALTER TABLE<表名> DROP COLUMN <列名> RESTRICT;若该列被引用则不删除ALTER TABLE<表名> DROP CONSTRAINT<完整性约束名> CASCADE;联级删除ALTER TABLE<表名> DROP CONSTRAINT<完整性约束名> CASCADE;限制删除
RENAME修改方式,用于修改表名称、列名称
ALTER TABLE <表名> RENAME TO <新表名>;ALTER TABLE <表名> RENAME <原列名> TO <新列名>;
ALTER修改方式,用于修改列的数据类型
ALTER TABLE <表名> ALTER COLUMN <列名> TYPE<新的数据类型>;
-
数据库表删除
1.语句基本格式
DROP TABLE <表名>;
例DROP TABLE Register;
数据库索引相关
-
索引创建SQL语句 : CREATE INDEX <索引名> ON <表名><(列名)>;
-
例 在学生信息表Student中,为出生日期Birthday列创建索引,以便支持按出生日期快速查询学生信息。
-
CREATE INDEX Birthday_Idx ON STUDENT (Birthday);
-
索引修改SQL语句 :ALTER INDEX <索引名> <修改项>;
-
索引名称修改语句格式如下:ALTER INDEX <索引名> RENAME TO <新索引名>;
-
例 在学生信息表Student中,将原索引Birthday_Idx更名为Bday_Idx,其索引修改SQL语句如下:
-
ALTER INDEX Birthday_Idx RENAME TO Bday_Idx;
-
索引删除SQL语句 : DROP INDEX <索引名> ;
-
例 在学生信息表Student中,删除bday_idx索引,其索引删除SQL语句如下:
-
DROP INDEX bday_idx;
数据操纵相关
-
数据插入SQL语句
1.语句基本格式
INSERT INTO <表名|视图名>[<列名表>] VALUES (列值表);
例INSERT INTO Student VALUES('2017220101105','柳因','女','1999-04-23','软件工程', 'liuyin@163.com');
-
数据更新SQL语句
1.语句基本格式
UPDATE <表名|视图名>SET <列名1>=<表达式1> [,<列名2>=<表达式2>...][WHERE <条件表达式>];
例 在学生信息表Student中,学生“赵东”的原有Email数据为空,现需要修改为“zhaodong@163.com”。UPDATE StudentSET Email=‘zhaodong@163.com'WHERE StudentName=‘赵东';
-
数据删除SQL语句
1.语句基本格式
DELETEFROM <表名|视图名>[WHERE <条件表达式>];
例 在学生信息表STUDENT中,删除姓名为 “张亮”的学生数据,其数据删除的SQL语句如下:DELETEFROM STUDENTWHERE StudentName='张亮';
单表数据查询
-
从单个表读取指定列
1.语句基本格式
SELECT <目标列>[,<目标列>…]FROM <关系表>;
例SELECT studentID,studentNAMEFROM student;
查询所有列:
SELECT *FROM student;
过滤重复:
SELECT DISTINCT majorFROM student;
得到结果是不重复的几种专业
-
从单个表读取指定行
1.语句基本格式
SELECT *FROM <关系表>WHERE <条件表达式>;
例SELECT *FROM studentWHERE gender='男';
-
从单个表读取指定行和列
1.语句基本格式
SELECT <目标列>[,<目标列>…]FROM <关系表>WHERE <条件表达式>;
例SELECT studentID,studentNAMEFROM studentWHERE gender='男';
-
Where条件子句
-
使用BETWEEN..AND关键词来限定列值范围,还可以使用关键词LIKE与通配符来限定查询条件。
-
使用通配符来限定字符串数据范围。下划线(_)通配符用于代表一个未指定的字符。百分号(%)通配符用于代表一个或多个未指定的字符。
例 若要从STUDENT表中查询出生日期在“2000-01-01”到“2000-12-30”的学生数据。其数据查询SQL语句如下:SELECT * FROM STUDENTWHERE BirthDay BETWEEN '2000-01-01' AND '2000-12-30';
例 若要从STUDENT表中查询邮箱域名为“@163.com”的学生数据。其数据查询SQL语句如下:SELECT *FROM STUDENTWHERE Email LIKE '%@163.com’;通配符: %匹配任意长度字符,_匹配单个字符SELECT * FROM STUDENTWHERE Email LIKE '____@163.com';
-
逻辑运算符(AND、OR、NOT)连接操作与IN或NOT IN关键词
例 从STUDENT表中查询性别为“男”,并且专业为“软件工程”的学生数据,其数据查询SQL语句如下。SELECT StudentID, StudentName, StudentGender, MajorFROM STUDENTWHERE Major='软件工程' AND StudentGender='男';
例 在STUDENT表查询时,使用IN关键字限定范围”计算机应用”专业的学生。其SQL语句如下所示。SELECT StudentID, StudentName, StudentGender, MajorFROM STUDENTWHERE Major IN ('计算机应用');
-
对结果集进行排序
在SELECT查询语句返回的结果集中,行的顺序是任意的。如果需要结果集排序,可以在SELECT语句中加入ORDER BY关键字。
例 若要从STUDENT表中按学生出生日期降序输出学生数据,其数据查询SQL语句如下SELECT *FROM STUDENTORDER BY Birthday DESC;默认升序,ASC升序DESC降序
例 若要将STUDENT表查询数据,首先按出生日期降序排列,然后按姓名升序排列,其数据查询SQL语句如下:SELECT *FROM STUDENTORDER BY Birthday DESC , StudentName ASC;
内置函数与分组统计
-
SQL聚合函数:一些对关系表中数值属性列进行计算并返回一个结果数值的函数。
聚合函数
|
功能
|
AVG
()
|
计算
结果集指定列
数据的
平均值
|
COUNT
()
|
计算结果集行数
|
MIN
()
|
找出
结果集指定列
数据的
最小值
|
MAX
()
|
找出
结果集指定列
数据的
最大值
|
SUM
()
|
计算
结果集指定列
数据的
总和
|
例 若要统计Student表中的学生人数,在SELECT语句中可以使用COUNT()函数来计算,其查询SQL语句如下:SELECT COUNT(*) AS 学生人数FROM Student;
例 找出STUDENT表中年龄最大和年龄最小的学生出生日期,其查询SQL语句如下:SELECT Min(Birthday) AS 最大年龄,Max(Birthday) AS 最小年龄FROM Student;
-
分组数据统计,Group By子语句
分组统计SQL语句基本格式:
SELECT 统计函数(目标列)FROM <表名>[WHERE 条件]GROUP BY <目标列>[Having 条件];
例:若要分专业统计STUDENT表中男生人数,但限定只显示人数大于2的人数,其查询SQL语句如下:SELECT Major AS 专业, COUNT(StudentID) AS 学生人数FROM StudentWHERE StudentGender=’男’GROUP BY MajorHAVING COUNT(*)>2;
group by 有一个原则,就是 select 后面的所有列中,没有使用聚合函数的列,必须出现在 group by 后面或者包含在having 后的聚合函数里。
where 子句的作用是在对查询结果进行分组前将不符合where条件的行去掉,即在分组之前过滤数据,条件中不能包含聚合函数。
having 子句的作用是筛选满足条件的组,即在分组之后过滤数据,条件中经常包含聚合函数。
having 子句中的每一个元素也必须出现在select列表中。
除了使用GROUP BY语句外,列的名称是不允许和内置函数一起混合使用。以下语句不规范。
SELECT MaxHours, SUM(MaxHours)FROM PROJECTWHERE ProjectID <= 1200;
DBMS产品在使用内置函数的方式也不一样。一般来说,内置函数是不能用于WHERE子句中的。以下语句不规范:
SELECT ProjectID, MaxHoursFROM PROJECTWHERE MaxHours < AVG(MaxHours);
当同时含有where子句、group by 子句 、having子句及聚集函数时,执行顺序如下:
-
执行where子句查找符合条件的数据;
-
使用group by 子句对数据进行分组;
-
对group by 子句形成的组运行聚集函数计算每一组的值;
-
最后用having 子句去掉不符合条件的组。
多表关联查询
-
子查询与多表关联
子查询SQL语句基本格式:
SELECT <目标列>[,<目标列>…]FROM <表名>WHERE <条件中嵌套另一关系表的SELECT 查询结果集>
例 在选课管理系统数据库中,希望能检索出“计算机学院”的教师名单。该操作需要关联教师信息表Teacher和学院信息表College,才能获得这些数据。这里可采用子查询方法实现两表关联查询,其查询SQL语句如下:SELECT TeacherID, TeacherName, TeacherTitleFROM TeacherWHERE CollegeID IN(SELECT CollegeIDFROM CollegeWHERE CollegeName=’计算机学院’);
如果where子句用聚合运算,则必须使用子查询
-
使用连接关联多表查询
连接关联多表查询SQL语句基本格式:
SELECT <目标列>[,<目标列>…]FROM <表名1>,<表名2>,…, <表名n>,WHERE <关系表之间的连接关联条件>
例 在选课管理系统数据库中,希望获得各个学院的教师信息列表,包括学院名称、教师编号、教师姓名、教师性别、职称等信息。要求按学院名称、教师编号分别排序输出,其查询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 <目标列>[,<目标列>…]FROM <表名1> JOIN <表名2> ON <连接条件>;
例 在选课管理系统数据库中,希望获得各个学院的教师信息,包括学院名称、教师编号、教师姓名、教师性别、职称等信息。要求按学院名称、教师编号分别排序输出,其查询SQL语句如下: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;
-
外部连接
例 在选课管理数据库中,希望查询所有开设课程的学生选课情况,包括课程名称、任课教师、选课学生人数。这需要关联课程信息表COURSE、教师信息表TEACHER、开课计划表PLAN、选课注册信息表REGISTER。其连接查询的SQL语句如下:SELECT C.CourseName AS 课程名称, T.TeacherName AS 教师,COUNT (R.CoursePlanID) AS 选课人数 FROM COURSE AS C JOIN PLAN AS P ON C.CourseID=P.CourseIDJOIN TEACHER AS T ON P.TeacherID=T.TeacherIDJOIN REGISTER AS R ON P.CoursePlanID=R.CoursePlanIDGROUP BY C.CourseName, T.TeacherName;
问题:在上面的内连接查询中,只能找出有学生注册的课程名称和选课人数,但不能找出没有学生注册的课程名称。
LEFT JOIN: 左外连接,即使没有与右表关联列值匹配,也从左表返回所有的行。
RIGHT JOIN: 右外连接,即使没有与左表关联列值匹配,也从右表返回所有的行。
FULL JOIN: 全外连接,同时进行左连接和右连接,就返回所有行。
例 在选课管理系统数据库中,希望能查询所有开设课程的学生选课情况,包括课程名称、任课教师、选课学生人数。这需要关联课程信息表COURSE、开课计划表CPLAN、教师信息表TEACHER、选课注册信息表REGISTER。若使用左外连接查询,该JOIN…ON连接查询的SQL语句如下:SELECT C.CourseName AS 课程名称, T.TeacherName AS 教师, COUNT (R.CoursePlanID) AS 选课人数FROM COURSE AS C JOIN PLAN AS PON C.CourseID=P.CourseIDJOIN TEACHER AS T ON P.TeacherID=T.TeacherID LEFT JOIN REGISTER AS R ON P.CoursePlanID=R.CoursePlanIDGROUP BY C.CourseName, T.TeacherName;
数据控制
-
GRANT权限授予语句
GRANT <权限列表> ON <数据库对象> TO <用户或角色> [ WITH GRANT OPTION ];
例 在选课管理系统数据库中,将课程注册表REGISTER的数据插入、数据修改、数据删除、数据查询访问权限赋予学生角色RoleS。GRANT SELECT, INSERT, UPDATE, DELETE ON REGISTER TO RoleS;
-
REVOKE权限收回语句
REVOKE <权限列表> ON <数据库对象> FROM <用户或角色> ;
例 在选课管理系统数据库中,收回学生角色RoleS在课程注册表REGISTER的数据删除访问权限。REVOKE DELETE ON REGISTER FROM RoleS;
-
DENY权限拒绝语句
DENY <权限列表> ON <数据库对象> TO <用户或角色> ;
例 在选课管理系统数据库中,若拒绝教师角色RoleT对教师表TEACHER的数据删除访问权限。DENY DELETE ON TEACHER TO RoleT;
视图
-
视图创建SQL语句
1.语句基本格式
CREATE VIEW <视图名>[(列名1),(列名2),…] AS <SELECT查询>;
例 在选课管理系统数据库中,若需要建立一个查看基础课数据的视图BasicCourseView,其创建SQL语句如下。CREATE VIEW BasicCourseView ASSELECT CourseName, CourseCredit, CoursePeriod, TestMethodFROM COURSEWHERE CourseType=‘基础课’;
-
视图查询SQL语句
例 使用SELECT语句查询该视图数据,并按课程名称排序输出,其SQL语句如下:SELECT *FROM BasicCourseViewORDER BY CourseName;
-
视图删除SQL语句
1.语句基本格式
DROP VIEW <视图名>;
例DROP VIEW BasicCourseView;
-
使用视图简化复杂SQL查询操作
例 在选课管理系统数据库中,查询选修“数据库系统原理与开发”课程的学生名单。这需要关联课程信息表COURSE、开课计划表PLAN、选课注册信息表REGISTER、学生信息表STUDENT,其查询SQL语句如下:
SELECT C.CourseName AS 课程名称, S.StudentID AS 学号, S.StudentName AS 姓名FROM COURSE AS C, PLAN AS P, REGISTER AS R, STUDENT AS SWHERE C.CourseID=P.CourseID AND C.CourseName='数据库原理及应用' AND P.CoursePlanID=R.CoursePlanID AND R.StudentID=S.StudentID;
较冗杂
先定义一个名称为DatabaseCourseView视图,其创建SQL语句如下:
CREATE VIEW DatabaseCourseView ASSELECT C.CourseName AS 课程名称, S.StudentID AS 学号, S.StudentName AS 姓名 FROM COURSE AS C,PLAN AS P, REGISTER AS R, STUDENT AS SWHERE C.CourseID=P.CourseID AND C. CourseName='数据库原理及应用' AND P.CoursePlanID=R.CoursePlanID AND R.StudentID=S. StudentID;
创建完成后外部就可以直接查询
SELECT * FROM DatabaseCourseView;
-
使用视图提高数据访问安全性
例 在选课管理系统数据库中,除管理部门用户外,其他用户只能浏览教师基本信息,如教师编号、教师姓名、性别、职称、所属学院。教师其他信息需要被隐藏,可通过视图来处理,其视图创建SQL语句如下:
CREATE VIEW BasicTeacherInfoView ASSELECT T.TeacherID AS 编号, T.TeacherName AS 教师姓名, T.TeacherGender AS 性别, T. TeacherTitle AS 职称, C.CollegeName AS 所属学院 FROM TEACHER AS T, COLLEGE AS CWHERE T.CollegeID=C. CollegeID;创建完成后外部就可以直接查询SELECT * FROM BasicTeacherInfoViewORDER BY 所属学院 , 教师姓名;