SQL语句

--数据操作
SELECT --从数据库表中检索数据行和列
INSERT --向数据库表添加新数据行
DELETE --从数据库表中删除数据行
UPDATE --更新数据库表中的数据
--数据定义
CREATE TABLE --创建一个数据库表
DROP TABLE --从数据库中删除表
ALTER TABLE --修改数据库表结构
CREATE VIEW --创建一个视图
DROP VIEW --从数据库中删除视图
CREATE INDEX --为数据库表创建一个索引
DROP INDEX --从数据库中删除索引
CREATE PROCEDURE --创建一个存储过程
DROP PROCEDURE --从数据库中删除存储过程
CREATE TRIGGER --创建一个触发器
DROP TRIGGER --从数据库中删除触发器
CREATE SCHEMA --向数据库添加一个新模式
DROP SCHEMA --从数据库中删除一个模式
CREATE DOMAIN --创建一个数据值域
ALTER DOMAIN --改变域定义
DROP DOMAIN --从数据库中删除一个域
--数据控制
GRANT --授予用户访问权限
DENY --拒绝用户访问
REVOKE --解除用户访问权限
--事务控制
COMMIT --结束当前事务
ROLLBACK --中止当前事务
SET TRANSACTION --定义当前事务数据访问特征

数据库中数据的正确性、有效性和相容性称为( )。

A．恢复
B．并发控制
C．完整性
D．安全性 

 

上图答案有错误 

1.
SELECT DISTINCT SNO FROM SPJ WHERE SPJ.JNO='J1';
2.
SELECT SNO FROM SPJ WHERE JNO='J1' AND PNO='P1';
3.
SELECT SNO FROM SPJ,P WHERE SPJ.PNO=P.PNO AND P.COLOR='红色' AND JNO='J1';
4.
SELECT JNO FROM J WHERE JNO NOT IN (SELECT JNO FROM SPJ,S,P WHERE SPJ.SNO=S.SNO
AND SPJ.PNO=P.PNO AND CITY='天津' AND COLOR='红色');
5.
SELECT DISTINCT JNO FROM SPJ X WHERE NOT EXISTS (SELECT * FROM SPJ Y WHERE Y.SNO='S1' AND 
NOT EXISTS(SELECT * FROM SPJ Z WHERE Z.JNO=X.JNO AND Z.PNO=Y.PNO));

上图答案不一定对

1.
SELECT SNAME,CITY FROM S;
2.
SELECT PNAME,COLOR,WEIGHT FROM P;
3.
SELECT DISTINCT JNO FROM SPJ WHERE SNO='S1';
4.
SELECT PNAME,QTY FROM SPJ,P WHERE  SPJ.PNO=P.PNO AND JNO='J2' 
5.
SELECT JNO FROM SPJ,S WHERE  SPJ.SNO=S.SNO AND CITY='上海' ;
6.
SELECT JNAME FROM SPJ, S,J WHERE SPJ.SNO=S.SNO  AND SPJ.JNO=J.JNO AND S.CITY='上海';
7.
SELECT JNO FROM J WHERE JNO NOT IN(SELECT JNO FROM SPJ,S WHERE SPJ.SNO=S.SNO AND 
S.CITY='天津');
8.
UPDATE P SET COLOR='蓝' WHERE COLOR='红';
9.
UPDATE SPJ SET SNO='S3' WHERE SNO='S5' AND JNO='J4' AND PNO='P6';
10.
DELETE FROM S WHERE SNO='S2';
DELETE FROM SPJ WHERE SNO='S2';
11.
INSERT INTO SPJ VALUES('S2','J6','P4',200);
12.
CREATE VIEW SSS(SNO,PNO,QTY) AS SELECT  SNO,PNO,QTY FROM SPJ,J WHERE SPJ.JNO=J.JNO AND
J.JNAME='三建';
13.
SELECT DISTINCT PNO,QTY FROM SSS WHERE SNO='S1';
14.
SELECT DISTINCT  * FROM SSS WHERE SNO='S1';

查询选修课程6成绩最高学生学号
1.建视图
create view sss(cno,max) as select cno,max(grade) from sc group by cno;
select sno from sc,sss  where sc.cno=6 and sss.cno=sc.cno and grade=max;
或者
2.all语句
select sno from sc where cno=3 and grade>=all(select grade from sc where cno=6);
查询语句中select from where group by having order by的执行顺序
查询中用到的关键词主要包含六个，并且他们的顺序依次为 
select--from--where--group by--having--order by 
sql语句的书写顺序并不是一样的，而是按照下面的顺序来执行 
from--where--group by--having--select--order by

1.
SELECT C#,CNAME FROM C WHERE TEACHER='程军';
2.
SELECT S#,SNAME FROM S WHERE AGE>21 AND SEX='男';
3.
SELECT SNAME FROM S WHERE NOT EXISTS(SELECT * FROM C WHERE C.TEACHER='程军' AND NOT EXISTS(
SELECT * FROM SC WHERE SC.S#=S.S# AND SC.C#=C.C#));
4.
SELECT C# FROM C WHERE C# NOT IN (SELECT SC.C# FROM SC,S WHERE SC.S#=S.S# AND SNAME='李强';
5.
SELECT S# FROM SC GROUP BY S# HAVING COUNT(CNO)>=2;
6.
SELECT C#,CNAME FROM C WHERE NOT EXISTS(SELECT * FROM S WHERE NOT EXISTS (SELECT * FROM 
SC WHERE SC.C#=C.C# AND SC.S#=S.S#));
7.
SELECT S# FROM SC WHERE C# IN (SELECT C# FROM C WHERE TEACHER='程军');
8.
SELECT S# FROM SC WHERE CNO='K5' AND S# IN (SELECT S# FROM SC WHERE C#='K1');
9.
SELECT SNAME FROM S WHERE NOT EXISTS (SELECT * FROM C WHERE NOT EXISTS (SELECT * FROM SC
WHERE SC.S#=S.S# AND SC.C#=C.C#));
10.
SELECT DISTINCT S# FROM FROM SC X WHERE NOT EXISTS(SELECT * FROM SC Y WHERE S#='2' AND NOT EXISTS(
SELECT * FROM SC Z WHERE Z.S#=X.S# AND Z.C#=Y.C#));