数据库语言SQL

比较字符串大小
 'Student' < 'Stupid'
 
SELECT * FROM 关系名 WHERE 选择条件
 SELECT * FROM Student WHERE Age > 19 AND （Dept = '计算机系' OR Dept = '自动化系'）;
 SELECT * FROM Student WHERE Age IS NULL;
 SELECT * FROM Student WHERE Age IS NOT NULL;
SELECT 属性原名 AS 属性别名
 SELECT StudentNo AS No，StudentName AS Name FROM Student；
 显示结果中用No和Name替代了StudentNo和StudentName
SELECT * FROM Student WHERE StudentName LIKE '李%';
SELECT * FROM Student WHERE StudentName NOT LIKE '李%';
 %：能和由任意字符组成的任意长度的字符序列匹配
SELECT * FROM Student WHERE StudentName LIKE '李_';
SELECT * FROM Student WHERE StudentName NOT LIKE '李_';
 _：能和任意一个字符匹配
SELECT * FROM Student WHERE StudentName LIKE '李*';
SELECT * FROM Student WHERE StudentName NOT LIKE '李*';
 *：任意字符
SELECT * FROM Student WHERE Title = 'Feel100\%';
 \：转义字符，\%是将%当成普通字符，\\将\当成普通字符；这里查找Title是Feel100%
ORDER BY <属性表> DESC
 DESC降序输出， ASC升序输出（默认）;
 SELECT * FROM Student WHERE Dept = '计算机' ORDER BY Age DESC, StudentNo;
聚合运算符 ：SUM, AVG, MIN, MAX, COUNT
 SELECT AVG(Age) FROM Student;
 SELECT MAX(Age) FROM Student;
 SELECT COUNT(*) FROM Student WHERE Dept = '计算机';
分组：GROUP BY
 SELECT Dept, COUNT(*) FROM Student GROUP BY Dept;
 SELECT Dept, COUNT(*) FROM Student GROUP BY Dept HAVING COUNT(*) > 50;
查询并，交，差：
     查询某选修课1234的学生学号与计算机系年龄大于18的学生学号之间的交集：
  (SELECT StudentNo FROM StudentCourse WHERE CourseNo = 1234;)
  INTERSECT
  (SELECT StudentNo FROM Student WHERE Age > 18 AND Dept = '计算机';)
 查询计算机系年龄大于18的学生学号除去某选修课1234的学生学号：
  (SELECT StudentNo FROM Student WHERE Age > 18 AND Dept = '计算机';)
  EXCEPT
  (SELECT StudentNo FROM StudentCourse WHERE CourseNo = 1234;)
 查询某选修课1234的学生学号与计算机系年龄大于18的学生学号之间的并集：
  (SELECT StudentNo FROM StudentCourse WHERE CourseNo = 1234;)
  UNION
  (SELECT StudentNo FROM Student WHERE Age > 18 AND Dept = '计算机';)
自然连接与笛卡尔积：将两个关系Student和StudentCourse进行自然连接
 SELECT StudentNo FROM Student, StudentCourse WHERE Student.StudentNo = StudentCourse.StudentNo AND Dept = '计算机系' AND Age > 18 AND CourseNo = 1234;
 笛卡尔积：
  FROM 关系名1 CROSS JOIN 关系名2;
  SELECT A, R, S, C, D FROM R, S;
 SELECT * FROM Actor NATURAL JOIN Proceducer;
 等值连接：
  SELECT * FROM Movie JOIN StarsIN ON Title = MovieTitle AND Year = MovieYear;
 外部连接：
 FROM NATURAL OUTER JOIN [ON<连接条件表达式>]
  SELECT * FROM A NATURAL LEFT OUTRT JOIN P; 
关系的选择条件：EXISTS, IN, ALL, ANY
 找出平均成绩最好的学生学号：
  SELECT StudentNo FROM StudentCourse GROUP BY StudentNo
   HAVING AVG(Score)>=ALL
   (SELECT AVG(Score) FROM StudentCourse GROUP BY StudentNo);
  子查询按学号分组，算出每个学生的平均成绩
插入：
INSERT INTO R(A1，A2，A3，A4，...AN) VALUES(V1，V2，V3，V4, ...VN);
 INSERT INTO Student VALUES(92012,'维纳斯',20,'计算机');
 INSERT INTO Student(StudentNo, StudentName, Age, Dept) VALUES(92012,'维纳斯',20,'计算机');
 INSERT INTO Student
  (TABLE (02149,'ASD',24,'计算机'),
   (02149,'撒旦',24,'计算机'),
   (02149,'大树王',24,'电子系')
  );
删除：
DELETE FROM R WHERE <选择条件>;
 DELETE FROM Student WHERE StudentNo = 99232;
修改：
UPDATE R SET A1=E1，A2=E2... WHERE <选择条件>;
 UPDATE Student SET Age = Age + 1, Dept = '计算机科学系' WHERE Dept = '计算机系';
创建表：
CREATE TABLE R (
  A1<数据类型>,
  A2<数据类型>,
  ...
  An<数据类型>
  );
 CREATE TABLE Student (
   StudentNo INT NOT NULL UNIQUE,
   StudentName CHAR(8),
   Age INT DEFAULT 18,
   Dept VARCHAR(20)
   );
撤销表：
 删除一个表是删除一个表中所有元组，其关系模式还在，而表是空表;
 撤销一个表，就是彻底将它从数据库模式中清除，不仅表中所有元组都没了，关系模式也不再保留。
DROP TABLE R;
 DROP TABLE Student;
更改关系模式：
ALTER TABLE R 具体的更改操作;
 ALTER TABLE R ADD A <对A的描述，如数据类型及各种约束>；
  ALTER TABLE Student
  ADD *** CHAR(1) DEFAULT 'M';
  表Student中添加一列为***默认值为'M';
 ALTER TABLE R DROP A;
  ALTER TABLE Student
  DROP Age;
 ALTER TABLE R MODIFY A <对A的重新描述，如数据类型及各种约束>；
  ALTER TABLE Student
  MODIFY Dept CHAR(10);
索引：
CREATE INDEX <索引名> ON <关系名(属性名)>;
  CREATE INDEX YearIndex ON Movie(year);
 DROP INDEX <索引名>;
  DROP INDEX YearIndex;
视图：
创建视图：
CREATE VIEW <视图名> AS <子查询>;
 CREATE VIEW CSStudent AS
  SELECT StudentNo, StudentName
  FROM Student
  WHERE Dept = '计算机系';
查询视图：
SELECT StudentNo
 FROM CSStudent
 WHERE StudentName = '李明';
更新视图：
DELETE FROM CSStudent WHERE Age > 20;
 其等价基本表删除：DELETE FROM Student WHERE Age > 20 AND Dept = '计算机系';
UPDATE CSStudent SET StudentName = '李小明' WHERE StudentNo = 992019；
 其等价基本表修改：UPDATE CSStudent SET StudentName = '李小明' WHERE StudentNo = 992019 AND Dept = '计算机系'；
撤销视图：
DROP VIEW <视图名>；
 DROP VIEW CSStudent; 

 

 

                                               查找于《数据库系统概论》 清华大学出版社