数据库语句练习

最新推荐文章于 2022-09-02 17:32:04 发布

郝拾柒

最新推荐文章于 2022-09-02 17:32:04 发布

阅读量1.1w

点赞数

分类专栏： SQL

本文链接：https://blog.csdn.net/CrownNew/article/details/103266099

版权

SQL 专栏收录该内容

1 篇文章

订阅专栏

这篇博客涵盖了数据库操作的实践内容，包括查找技巧、视图的创建与使用，以及权限的授予和回收。通过29道练习题，读者可以深入理解和掌握这些核心概念。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

查找练习

SELECT student.*,SC.* FROM student, sc 
WHERE student.sno = sc.Sno

SELECT student.sno,Sname,Ssex,Sage,Sdept,Cno,Grade FROM student,sc 
WHERE student.sno = SC.sno

SELECT student.sno  FROM student,sc 
WHERE student.sno = SC.sno

SELECT FIRST.cno,SECOND.Cpno FROM course FIRST,course SECOND 
WHERE FIRST.cpno = SECOND.cno

SELECT student.sno,Sname,Ssex,Sage,Sdept,Cno,Grade FROM student 
LEFT OUTER JOIN SC ON (Student.Sno = SC.cno)

SELECT student.sno,Sname,Cname,Grade FROM student,sc,course 
WHERE student.sno = SC.sno AND SC.cno = course.Cno

SELECT Sname FROM student 
WHERE sno IN (SELECT sno FROM sc WHERE cno = '2')

SELECT Sno,Sname,Sdept FROM student 
WHERE sdept IN (SELECT sdept FROM student WHERE sname = '����')

SELECT S1.Sno ,S1.Sname,S1.sdept FROM student S1, student S2 
WHERE S1.sdept = S2.sdept AND S2.sname =  '����'

SELECT Sno,Sname FROM student 
WHERE sno IN
	(SELECT sno FROM sc
	WHERE cno IN (
		SELECT cno FROM course WHERE cname = '��Ϣϵͳ')) 

SELECT Student.Sno,Sname FROM student,sc,course 
WHERE student.sno = SC.sno AND SC.Cno = course.cno AND course.cname = '��Ϣϵͳ'

SELECT Sno,Sname,Sdept FROM student 
WHERE sdept = (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)

SELECT Sname,Sage FROM student 
WHERE sage <ANY(SELECT sage From student WHERE sdept = 'CS')

SELECT Sname,Sage FROM student 
WHERE sage < (SELECT MAX(sage) From student WHERE sdept = 'CS')

SELECT Sname,Sage FROM student 
WHERE sage <ALL(SELECT sage From student WHERE sdept = 'CS') AND sdept <> 'CS'

SELECT Sname,Sage FROM student 
WHERE sage < (SELECT MIN(sage) From student WHERE sdept = 'CS')

SELECT Sname FROM student 
WHERE EXISTS(SELECT * FROM sc WHERE sno = student.sno AND cno = '2')

SELECT Sname FROM student 
WHERE NOT EXISTS(SELECT * FROM sc WHERE sno = student.sno AND cno = '2')

SELECT Sno,Sname,Sdept FROM student S1 
WHERE EXISTS
 	(SELECT * FROM student S2 WHERE S2.sdept = S1.sdept AND S2.sdept = S1.sdept AND S2.Sname = '����')

SELECT Sname FROM student 
WHERE NOT EXISTS(
	SELECT * FROM course WHERE NOT EXISTS(
							SELECT * FROM sc WHERE sno = student.sno AND cno = course.cno)) 

SELECT DISTINCT Sno FROM sc SCX 
WHERE NOT EXISTS(
  SELECT * FROM SC SCY 
	 WHERE SCY.sno = '201215122' AND NOT EXISTS (
		SELECT * FROM SC SCZ WHERE SCZ.cno = SCX.cno AND SCZ.cno = SCY.cno))

SELECT * FROM student WHERE sdept = 'CS' 
UNION
SELECT * FROM student WHERE sage <= 19

SELECT Sno FROM SC WHERE Cno = '1' 
UNION
SELECT Sno FROM SC WHERE Cno = '2'

SELECT * FROM student WHERE sdept = 'CS'
INTERSECT
SELECT * FROM student WHERE sage <= 19

SELECT Sno FROM SC WHERE Cno = '1' 
INTERSECT
SELECT Sno FROM SC WHERE Cno = '2'

SELECT Sno FROM sc WHERE cno = '1' AND sno IN (SELECT sno FROM sc WHERE cno = '2')

SELECT Sno,Cno FROM sc,(SELECT Sno,AVG(Grade) FROM SC GROUP BY Sno)
  AS AVG_sc(avg_sno,avg_grade)
WHERE SC.Sno = AVG_SC.avg_sno AND SC.Grade >= AVG_sc.avg_grade

SELECT Sname FROM student,(SELECT Sno FROM sc WHERE cno = '1') AS SC1
	 WHERE student.sno = SC1.Sno

INSERT INTO student(sno,sname,ssex,sdept,sage)
VALUES('201515128','�Ž���','��','IS',18)

INSERT INTO student
VALUES('201515129','�Ž�','Ů',20,'CS')

INSERT INTO sc (sno,cno)
VALUES('20121519','1')

UPDATE student
SET sage = 22
WHERE sno = '201215121'

UPDATE student SET sage = sage + 1

UPDATE sc SET grade = 0 
WHERE sno IN (SELECT sno FROM student WHERE sdept = 'CS')

DELETE FROM Student 
WHERE Sno = '201215128'

DELETE FROM sc 
WHERE sno IN (SELECT sno FROM student WHERE student WHERE sdpet = 'CS')

视图

INSERT
INTO student(Sno,Sname,Ssex,Sdept,Sage)
VALUES('201215128','�¶�','��','IS',18)

INSERT
INTO sc(sno,cno)
VALUES('201215128','1')

INSERT
INTO sc
VALUES('201215129','2',NULL)

INSERT
INTO Dept_age(Sdept,Avg_age)
SELECT Sdept,AVG(Sage)
FROM Student
GROUP BY Sdept

UPDATE student
SET sage=22
WHERE sno='201111111'

UPDATE student
SET sage=sage+1

UPDATE sc
SET grade=0
WHERE sno IN
	(SELECT sno
	FROM student
	WHERE sdept='CS')

DELETE
FROM student
WHERE sno='210111111'

DELETE
FROM sc
WHERE sno IN(
	SELECT sno
	FROM student
	WHERE sdept='CS')

SELECT SNO
FROM sc
WHERE grade<60 AND cno='1'
UNION
SELECT SNO
FROM sc
WHERE grade IS NULL AND cno='1'

SELECT SNO
FROM sc
WHERE cno='1' AND (grade<60 OR grade IS NULL)

CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM student
WHERE sdept='IS'
WITH CHECK OPTION

CREATE VIEW IS_S1(Sno,Sname,Grade)
AS
SELECT student.sno,Sname,grade
FROM student,sc
WHERE sdept='IS' AND
		student.sno=SC.sno AND
		SC.cno='1'

CREATE VIEW IS_S2(Sno,Sname,Grade)
AS
SELECT sno,Sname,grade
FROM IS_S1
WHERE Grade>=90

CREATE VIEW BT_S(Sno,Sname,Sbirth)
AS
SELECT Sno,Sname,2014-Sage
FROM student

CREATE VIEW S_G(Sno,Gavg)
AS
SELECT Sno,AVG(Grade)
FROM sc
GROUP BY Sno

CREATE VIEW F_Student(F_sno,name,sex,age,dept)
AS
SELECT *
FROM student
WHERE ssex='Ů'

UPDATE IS_Student
SET Sname='����'
WHERE Sno='201215122'

INSERT INTO IS_Student
VALUES('201215129','����',20)

DELETE
FROM IS_Student
WHERE Sno='20151219'

CREATE VIEW S_G(Sno,Gavg)
AS
SELECT SNO,AVG(Grade)
FROM sc
GROUP BY Sno

CREATE VIEW VMGRADE
AS
SELECT Sno,MAX(Grade) M_Grade
FROM SC
GROUP BY Sno

授权回收

�����û�U1
CREATE LOGIN U1 WITH PASSWORD = '123123'
CREATE USER U1;

�Ѳ�ѯStudent����Ȩ����Ȩ���û�U1
GRANT SELECT
ON Student
TO U1;

�����û�U2
CREATE LOGIN U2 WITH PASSWORD = '124124'
CREATE USER U2;

�����û�U3
CREATE LOGIN U3 WITH PASSWORD = '123123'
CREATE USER U3;

�ѶԱ�student��course��ȫ������Ȩ�������û�U2��U3
GRANT ALL PRIVILEGES
ON student,course
TO U2,U3;

�ѶԱ�SC�Ĳ�ѯȨ�����������û�
GRANT SELECT
ON SC
TO PUBLIC;

�����û�U4
CREATE LOGIN U4 WITH PASSWORD = '123123'
CREATE USER U4;

�Ѳ�ѯStudent�����޸�ѧ��ѧ�ŵ�Ȩ���ڸ��û�U4
GRANT UPDATE(Sno),SELECT
ON Student
TO U4;

�����û�U5
CREATE LOGIN U5 WITH PASSWORD = '123123'
CREATE USER U5;

�ѶԱ�SC��INSERTȨ������U5�û�������������Ȩ�������������û�
GRANT INSERT 
ON SC
TO U5
WITH GRANT OPTION;

�����û�U6
CREATE LOGIN U6 WITH PASSWORD = '123123'
CREATE USER U6;

U5��Ȩ��U6���Լ�����Ȩ��
GRANT INSERT 
ON SC
TO U6
WITH GRANT OPTION;

�����û�U7
CREATE LOGIN U7 WITH PASSWORD = '123123'
CREATE USER U7;

U6��Ȩ��U7��û�д���Ȩ��
GRANT INSERT 
ON SC
TO U7;

�ջ�U4�޸�ѧ��ѧ�ŵ�Ȩ��
REVOKE UPDATE(Sno)
ON Student
FROM U4;

�ջ������û��Ա�SC�Ĳ�ѯȨ��
REVOKE SELECT
ON SC
FROM PUBLIC;

�ջ�U5��SC����INSERTȨ��
REVOKE SELECT
ON SC
FROM U5 CASCADE;

����ɫ��һ��Ȩ����Ȩ���û�

�����û�R1
CREATE ROLE R1;

ʹR1ӵ��Student����SELECT,UPDATE,INSERTȨ��
GRANT SELECT,UPDATE,INSERT 
ON Student
TO R1;

�����û���ƽ
CREATE LOGIN ��ƽ WITH PASSWORD = '123123'
CREATE USER ��ƽ;

�����û� ����
CREATE LOGIN ���� WITH PASSWORD = '123123'
CREATE USER ����;

�����û�����
CREATE LOGIN ���� WITH PASSWORD = '123123'
CREATE USER ����;

����ɫ�������ƽ,����,����
GRANT R1
TO ��ƽ,����,����;

�ջ���ƽ��Ȩ��
REVOKE R1
FROM ��ƽ;

Ȩ���޸�
GRANT DELETE
ON Student
TO R1;

����DELETEȨ��
REVOKE SELECT
ON Student
FROM R1;

CREATE VIEW CS_Student
AS
SELECT *
FROM student
WHERE sdept = 'CS';

GRANT SELECT
ON CS_Student
TO ��ƽ;

GRANT ALL PRIVILEGES
ON CS_Student
TO ����;

29道题练习

-- 1.
	 CREATE CLUSTERED INDEX xhindex ON XS(xh)
	 CREATE UNIQUE INDEX xmindex ON XS(xm)
	 CREATE INDEX zyindex ON XS(zy)

-- 2.	
	 DROP INDEX XS.zyindex

-- 3.
	SELECT SNO,SNAME,CITY
	FROM S
	WHERE city = '���'

-- 4.
	SELECT DISTINCT city
	from S

-- 5.
	SELECT sno '��Ӧ�̴���',pno '�������',jno '������Ŀ����',qty '��Ӧ����'
	FROM SPJ
	WHERE qty > 200
	
--  6.
	 SELECT *
	 FROM j
	 WHERE city in ('����','���','�Ͼ�')
	
-- 7.
	SELECT *
	FROM p
	WHERE pname LIKE '��%'
	
-- 8.
	SELECT *
	FROM p
	WHERE pname LIKE '_��%'
	
-- 9.
	SELECT *
	FROM p
	WHERE color LIKE '��' AND weight > 20
	
-- 10.
	SELECT *
	FROM p
	WHERE color LIKE '��' or weight > 20
	
-- 11.
	SELECT *
	FROM j
	WHERE city NOT IN ('����','���')
	
-- 17.
	SELECT *
	FROM SPJ
	WHERE jno = 'J1'
	ORDER BY qty ASC
	
-- 18.
	SELECT SUM(qty) '������',MIN(qty) '��С����',MAX(qty) '�������',AVG(qty) 'ƽ������'
	FROM SPJ
	GROUP BY pno HAVING pno = 'P3'
	
-- 17.
	SELECT  pno '�����',SUM(qty) '������',MIN(qty) '��С����',MAX(qty) '�������',AVG(qty) 'ƽ������'
	FROM SPJ
	GROUP BY pno 

-- 18.
	SELECT jno '������',COUNT(sno) '�������'
	FROM spj
	GROUP BY jno
	
-- 19.
	SELECT jno '������',SUM(qty) '���������'
	FROM spj
	GROUP BY jno
	
-- 20.
	SELECT jno '������',COUNT (pno) '�������'
	FROM spj
	GROUP BY jno HAVING COUNT(pno)>2
	
-- 21.
	SELECT sno '��Ӧ��',COUNT(jno) '��Ŀ����'
	FROM spj
	GROUP BY sno
	
-- 22.
	SELECT sno '��Ӧ��',pno '�����',COUNT(jno) '��Ŀ����',SUM(qty) '���������' 
	FROM spj 
	GROUP BY pno,sno

	
-- 23.
	SELECT spj.sno '��Ӧ�̴���',sname '��Ӧ������',pno '�����',jno '������Ŀ��',qty '��Ӧ����'
	from  spj,s
	where qty > 200
	
-- 24.
	SELECT j.jno '������Ŀ����',jname '������Ŀ��', city '���ڳ���',pno '�������',qty '�������'
	from spj,j
	
-- 25.
	SELECT jno '������Ŀ����',jname '������Ŀ��',city '���ڳ���'
	FROM j
	WHERE jno IN
				(SELECT jno
				FROM spj
				WHERE sno = 'S1')
			
			
-- 26.
	SELECT jno '������Ŀ����',jname '������Ŀ��',city '���ڳ���'
	FROM j
	WHERE jno IN
				(SELECT jno
				FROM spj
				WHERE pno IN 
							(SELECT	pno
							 FROM P
							 WHERE color LIKE '��'))		


-- 27.
	SELECT pno '�����',pname '�������',color '��ɫ' 
	FROM p 
	WHERE pno IN 
			(SELECT pno 
			 FROM spj 
			 WHERE qty>200)


-- 28.
	SELECT sx.sno '��Ӧ�̴���',sx.sname '��Ӧ������'
	FROM s sx
	WHERE sx.city IN
					  (SELECT sy.city
					   FROM s sy
					   WHERE sy.sno = 'S1') 

-- 29.
	SELECT jno '������Ŀ����',jname '������Ŀ��',city '���ڳ���' 
	FROM j 
	WHERE EXISTS
				(SELECT * FROM spj 
				 WHERE spj.jno=j.jno AND pno='p1')

-- 29.
	SELECT jno '������Ŀ����',jname '������Ŀ��',city '���ڳ���' 
	FROM j 
	WHERE NOT EXISTS
					(SELECT * FROM spj 
					 WHERE spj.jno=j.jno AND pno='p1')