数据库概论期末复习(MySQL)

永远的萌新01

于 2024-06-02 21:50:51 发布

阅读量166

点赞数 4

文章标签：数据库服务器 mysql 期末数据库概论

本文链接：https://blog.csdn.net/weixin_71890898/article/details/139393409

版权

#SQL 建表

# 定义基本表
# 固定格式
CREATE TABLE SC
(
	# PRIMARY KEY 表示Sno 为主码
	Sno CHAR(9) PRIMARY KEY,# 属性名 类型 约束
	Sname CHAR(20) UNIQUE, # UNIQUE 表示取值唯一
	Ssex CHAR(3) Not Null, # Not Null 非空
	Sage SMALLINT,# SMALLINT 短整型
	Grade SMALLINT CHECK(Grade >=0 AND Grade<=100)
	# 检查Grade
	Sdept CHAR(20)
);

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
);

# 定义基本表
# 固定格式
CREATE TABLE SC
(
	# PRIMARY KEY 表示Sno 为主码
	Sno CHAR(9) PRIMARY KEY,# 属性名 类型 约束
	Sname CHAR(20) UNIQUE, # UNIQUE 表示取值唯一,既该名字只能有一条
	Ssex CHAR(3) Not Null, # Not Null 非空
	Sage SMALLINT,# SMALLINT 短整型
	Grade SMALLINT CHECK(Grade >=0 AND Grade<=100),
	Grade SMALLINT CHECK(grade BETWEEN 0 and 100) #闭区间
	# 检查Grade
	Sdept CHAR(20)
);

# 外码作为主属性
CREATE TABLE SC
(
	PRIMARY KEY (Sno,Cno),
	FOREIGN KEY (Sno) REFERENCES Student(Sno),
	FOREIGN KEY (Cno) REFERENCES Course(Cno)
	# 主码由两个属性组成，该属性为外码
);

# 删除基本表
DROP TABLE Student;

# 练习创建表
CREATE TABLE Student
(
	Sno PRIMARY KEY,# 主码
	Sname char(20) UNIQUE,
	#性别必须为男或女，默认为男
	Ssex char(2) CHECK(Ssex IN ("男","女")) DEFAULT ("男"),
	# Sdept 只能取值其中之一
	Sdept enum ("计科","软件","网络"),
);

#向表中增加新的列
ALTER TABLE Student ADD ruxuetime DATE; # DATE为日期型

#将表中的列的数据类型更改
ALTER TABLE Student ALTER COLUMN Sage INT; #假设原来的数据是字符型改为整数

#增加约束条件
ALTER TABLE Course ADD UNIQUE(Cname);

#表上建立视图
CREATE VIWE IS_Student 
AS #前面是建立视图语句，后面是查询语句
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept="IS";

#建立索引
# SC表按学号升序和课程号降序建立唯一索引
CREATE UNIQUE INDEX SCno ON SC(Sno ASC,Cno DESC);

#修改索引
#将SC表的SCno索引名改为SCSno
ALTER INDEX SCno RENAME TO SCSno;

#删除索引
#删除Student表的Stusname索引
DROP INDEX Stusname;

#############################################################################

#查询表练习数据库数据

#查询练习数据
#Mysql版本
drop table SC;
drop table Student;
drop table Course;

create table Student
( Sno varchar(10) primary key,
  Sname varchar (10),
  Ssex varchar (4),
  Sage int,
  Sdept varchar (10));

create table Course
( Cno varchar (10) primary key,
  Cname varchar (15),
  Cpno varchar (10),
  Ccredit int,
  Foreign key (Cpno) references Course(Cno));

create table SC
( Sno varchar (10),
  Cno varchar (10),
  Grade int,
  Primary key (Sno,Cno),
  Foreign key (Sno) references Student(Sno),
  Foreign key (Cno) references Course(Cno));

insert into Student values('200215121','李勇','男',20,'CS'),('200215122','刘晨','女',19,'CS'),('200215123','王敏','女',18,'MA'),('200215125','张立','男',19,'IS');

insert into Course values('2','数学',null,2);
insert into Course values('6','数据处理',null,2);
insert into Course values('4','操作系统','6',3);
insert into Course values('7','PASCAL语言','6',4);
insert into Course values('5','数据结构','7',4);
insert into Course values('1','数据库','5',4);
insert into Course values('3','信息系统','1',4);


insert into SC values('200215121','1',92);
insert into SC values('200215121','2',85);
insert into SC values('200215121','3',88);
insert into SC values('200215122','2',90);
insert into SC values('200215122','3',80);

#############################################################################

#单表数据查询

# 数据查询
SELECT - FROM - WHERE句型：

# WHERE  常用的查询条件：
确定范围 BETWEEN AND,NOT BETWEEN AND
确定集合 IN, NOT IN
字符匹配 LIKE,NOT LIKE
空值 IS NULL,IS NOT NULL
逻辑运算 AND,OR,NOT
比较符号

例:
#查询全体学生的学号与姓名
SELECT Sno,Sname
FROM student;

#查询全体学生的所有信息
SELECT *
FROM student;

#查询全体学生的姓名以及出生年份
SELECT Sname,2014-Sage #这里是用2014减去年龄得到 出生年份
FROM student;

#查询中单加一个无中生有的列，和全体学生所在的院系，要求用小写字母表示系名
SELECT Sname,'Year of Birth:',LOWER(Sdept) #不改变数据，大写用UPPER()
FROM student;

#给查询结果的列标题指定别名
SELECT Sname NAME #后面这个是别名
FROM student;

#消除取值重复的行
SELECT DISTINCT Sno #执行后Sno列相同的取值将被去掉  默认为ALL，就是保留重复
FROM SC;

#查询CS的同学姓名
SELECT Sname
FROM student
WHERE Sdept='CS';

#查询字符匹配 
SELECT Sname
FROM student
WHERE Sname LIKE '刘%'; # %代表任意长度 _代表任意单个字符

#如果查询的字符中含有% 或 _，需要用加上\来进行转义
#WHERE Sname NOT LIKE '王\%' ESCAPE '\';  #这里的ESCAPE是定义转义符

# 查询是否为空
SELECT Sname
FROM student
WHERE Sname IS NOT NULL; #空是 IS NULL

#ORDER BY语句
#ORDER BY语句写在查询最后，默认为升序ASC，降序为DESC
SELECT Sname,Grade
FROM student
WHERE Sdept='CS';
ORDER BY Grade DESC;

##################   聚集函数    ##################
#在WHERE子句中不能使用聚集函数
# ORDER BY语句 SELECT语句中可以加聚集函数
聚集函数
SELECT COUNT(*)/COUNT(DISTINCT Sno)/AVG(Grade)/MAX(Grade)/MIN(Grade)

#GROUP BY语句  按谁分组，谁写在GROUP BY中
SELECT Cno,COUNT(Sno) #对组计数得到一个值
FROM SC
GROUP BY Cno;

#HAVING 短语  如果分组之后还要对组筛选，输出符合条件的组使用
SELECT Cno,COUNT(Sno) #对组计数得到一个值
FROM SC
GROUP BY Cno;
HAVING COUNT(*)>3;

#连接查询

#连接查询

#等值连接
#查询每个学生及其选课情况
SELECT S.*,SC.*
FROM S,SC
WHERE S.Sno=SC.Sno;

#多表连接（3表）
#查询每个学生的学号姓名选修课程名及成绩
SELECT student.Sno,Sname,Cname,Grade
FROM student,SC,course
WHERE student.Sno=SC.Sno AND SC.Cno=course.Cno;

#自身连接
#查询每一门课的间接先修课号（先修课的先修课）
SELECT 本课.Cno,先修.Cpno
FROM course 本课,course 先修
WHERE 本课.Cpno =先修.Cno;

#外连接
#查询学生的选课情况(未选课学生的课号和成绩为空)
#将student选择的数据左插入SC表中，并且对没有的值设置为空
SELECT student.Sno,Sname,Cno,Grade
FROM Student left join SC
on student.Sno=SC.Sno;

#嵌入查询  不相关
#查询与'刘晨'在同一个系的学生
SELECT *
FROM student
WHERE Sdept IN(  #先找出刘晨所在的所有系，然后在找这个系的学生信息
	SELECT Sdept
	FROM student
	WHERE Sname='刘晨'
);

#嵌入查询（相关）

#找出每个学生超过他选修课平均成绩的课程号
SELECT Sno,Cno
FROM SC x
WHERE Grade>(
	SELECT AVG(Grade)   #先找成绩大于平均成绩的，再找出课程号
	FROM SC y
	WHERE y.Sno=x.Sno
);

#带有ANY（SOME）或ALL谓词的子查询
WHERE Sage<ANY(嵌套查询) AND Sdept != 'CS';

#EXISTS 谓词的子查询
#查询所有选修了1号课程的学生姓名
SELECT Sname FROM student WHERE EXISTS(嵌套查询);
#查询没有选修了1号课程的学生姓名
SELECT Sname FROM student WHERE NOT EXISTS(嵌套查询);

#集合查询 并
查询 UNION 查询

#集合查询 交
查询 INTERSECT 查询

#集合查询 差
查询 EXCEPT 查询

#数据修改和授权角色

#插入数据
INSERT INTO SC VALUES('201215128','1',NULL);
或 INSERT INTO SC(Sno,Cno) VALUES('201215128','1');

#修改数据
#所有学生的年龄增加1岁
UPDATE student SET Sage=Sage+1;

UPDATE student SET sage=22 WHERE Sno='201215128';


#删除数据
#删除所有学生的选课记录
DELETE FROM SC;

DELETE FROM student WHERE Sno='201215128';


#建立和删除视图
CREATE VIEW IS_student AS 查询语句;

DROP VIEW IS_student;


#授权和回收
#把SC表的修改成绩的权限授予用户U1
GRANT UPDATE(grade) ON SC TO U1;
#授权所有
GRANT ALL ON SC TO ;

#授权并允许传播权限
GRANT UPDATE(grade) ON SC TO U1 WITH GRANT OPTION;

#收回权限
REVOKE SELECT ON SC FROM U1;


#数据库角色
CREATE ROLE R; #创建角色R
#给角色加权限
#给角色减少权限  上述相同

#将角色授予其他用户
GRANT R TO U1 WITH ADMIN OPTION;

#收回角色从其他角色
REVOKE R FROM U1；

#触发器

#触发器
#格式
CREATE TRIGGER 名 事件 ON 表
类型
BEGIN
动作
END;

例如：
CREATE TRIGGER T BEFORE DELETE ON SC
FOR EACH ROW
BEGIN
	declare V float;#定义一个变量V并将平均成绩存入该变量
	SELECT AVG(grade) INTO V FROM SC;
	UPDATE TEST SET id=V;
END;

#删除触发器
DROP TRIGGER CAT;

#new值和old值  意思old表示原有的值，new表示即将输入改变的新值
CREATE TRIGGER T BEFORE DELETE ON SC  #AFTER UPDATE ON SC
FOR EACH ROW
BEGIN
	IF(new.grade>1.1*old.grade) THEN
	插入语句;
	END IF; #IF语句结束
END;

#数据库设计

以后部分非代码演示，故不作上传。。。