互联网数据库常考设计题SQL语句考点

《互联网数据库常考设计题SQL语句考点》由会员分享，可在线阅读，更多相关《互联网数据库常考设计题SQL语句考点（2页珍藏版）》请在装配图网上搜索。

1、1. DISTINCT 的语法如下 :SELECT DISTINCT 栏位名 FROM 表格 名(查找的结果不重复)2. AND OR 的语法：SELECT 栏 位名 FROM 表格名 WHERE 简单条件 AND|OR 简单条 件+，其中AND |OR可重复使 用 。例 子 ， 我 们 若要 在 Store_Information 表格中选出 所有 Sales 高于 $1,000 或是 Sales 在 $500 及 $275 之间 的 资 料 的 话， 我 们 就 打 入 SELECT store_name FROM Store_Information WHERE Sales 1000 OR

2、 (Sales 275)3. IN 的用法： SELECT 栏位名FROM 表格名 WHERE 栏位名 IN (值一, 值二, .)例子：我 们 要 在 Store_Information 表 格中找出所有含盖 Los Angeles 或 San Diego 的资料 SELECT * FROMStore_InformationWHERE store_name IN (Los Angeles, San Diego)我们就得 至V含盖 Los Angeles 和 san diego 资料的表格。4. BETWEEN 则是让我们可以运 用一个范围 (range) 内抓出数 据库中的值。BETWEEN

3、这个子 句的语法如下： SELECT 栏位 名 FROM 表格名 WHERE 栏位名 BETWEEN 值一 AND 值二5. LIKE 的语法如下： SELECT 栏位名 FROM 表格名 WHERE 栏位名 LIKE 套式。套式 经 常包括野卡 (wildcard). 以下是 几个例子： A_Z: 所有以 A 起头，另一 个 任 何值 的 字原 ，且 以 Z 为结尾的字 串。 ABZ 和 A2Z 都符合 这一个模式， 而 AKKZ 并不符合 (因为 在A和Z之间有两个字原， 而不是一个字原)。 ABC%: 所有以 ABC 起头的 字 串 。 举 例 来 说 ， ABCD 和 ABCABC 都

4、符合这个套式。 %XYZ: 所有以 XYZ 结尾的字 串。举例来说， WXYZ 和 ZZXYZ 都符合这个套式。 %AN%: 所有含有 AN 这个 套式的字串。举例来说， LOS ANGELES 和 SAN FRANCISCO 都 符合这个套式。6. ORDER BY 的语法如下(用来排序): SELECT 栏位名 FROM 表格名 WHERE 条件 ORDER BY 栏位名 ASC, DESC 代表 WHERE 是不一定需要 的。不过，如果 WHERE 子句 存在的话，它是在 ORDER BY 子句之前。 ASC 代表结果会以 由小往大的顺序列出，而 DESC 代表结果会以由大往小的顺序 列

5、出。例子： SELECT store_name, Sales,DateFROMStore_Information ORDER BY Sales DESC7. SQL 有提供一些这一类的函 数。它们是： AVG (平均 ) COUNT (计数) MAX (最大 值 ) MIN ( 最 小 值 ) SUM (总合) 运用函数的语 法是： SELECT 函数名(栏位 名) FROM 表格名 。举例来 说，若我们要由我们的示范表 格中求出 Sales 栏位的总合， 我们就输入 SELECT SUM(Sales) FROM Storenformation，这样 就找出 sales 的总和了。8. COU

6、NT 让我们能够数出在表格中 有多 少笔资 料被 选出 来。它的语法是： SELECT COUNT(栏位名)FROM 表格 名 举例来说，若我们要找出 我们的示范表格中有几笔 store_name 栏不是空白的资料 时 ， 我 们 输 入 SELECT COUNT(store_name)FROMStore_InformationWHEREstore_name is not NULL 结果: Count(store_name) 4 ， is not NULL 是 这个栏位不是空白 的 意 思 。 SELECT COUNT(DISTINCT store_name) FROM Store_Infor

7、mation,COUNT 和DISTINCT经常一块使用，表 示找出表格中有多少不同的资 料。9. GROUP BY：我们可以用sum这个指令来算出所有的 Sales (营业额)！如果我们的需求变成 是要算出每一间店 (store_name) 的 营 业 额 (sales)，那怎么办呢？在这个情 况下，我们要做至两件事：第 一，我们对于 store_name 及 Sales 这两个栏位都要选出。第 二，我们需要确认所有的 sales 都要依照 各个 store_name 来 分开算。这个语法为： SELECT 栏位 1, SUM(栏位 2) FROM 表格名 GROUP BY 栏位 1， 例子

8、语句： SELECT store_name, SUM(Sales)FROMStore_Information GROUP BY store_name。当我们选不只一个 栏位，且其中 至少一个栏位有 包含函数的运用时，我们就需 要用至 GROUP BY 这个指 令。在这个情况下，我们需要 确定我们有 GROUP BY 所有 其他的栏位。换句话说， 除了 有包括函数的栏位外，我们都 需要将其它所有栏目名放在 GROUP BY 的子句中。10. HAVING 我们可能只需要知道哪些店的营业额有超过 $1,500。在这个情况下，我们不 能使用 WHERE 的指令。 SQL 有提供一个 HAVING 的

9、指令， 而我们就可以用这个指令来达 至这个目标。 HAVING 子句通 常是在一个 SQL 句子的最后。 一个含有 HAVING 子句的 SQL 并不一定要包含 GROUP BY 子 句。 HAVING 的语法如下： SELECT 栏位 1, SUM(栏位 2) FROM 表格名 GROUP BY 栏位 1 HAVING (函数条件) 请 读者注意： GROUP BY 子句并 不是一定需要的。 SELECT store_name, SUM(sales) FROM Store_Information GROUP BY store_nameHAVINGSUM(sales) 1500。11. 另U名

10、(ALIAS)我们先来看一下栏位别名和表格别名的语法： SELECT 表格另名.栏位 1 栏位另名 FROM 表格名 表 格另 名， 例子： SELECT A1.store_nameStore,SUM(A1.Sales) Total Sales FROM Store_Information A1 GROUP BY A1.store_name12. 连接：将多个表格里的数据通 过某个栏目连接起来。例子我 们要知道每一区 (region_name) 的营业额 (sales)。 Geography 这个表格告诉我们每一区有哪 些 店 ， 而 Store_Information 告诉我们每一个店的营业

11、额。若我们要知道每一区的营业 额，我们需要将这两个不 同表 格中的资料串联起来。当我们 仔细了解这两个表格后，我们 会发现它们可经由一个相同的 栏位，store_name,连接起来。 我们先将 SQL 句列出， 之后 再讨论每一 个子句 的意 义：SELECT A1.region_name REGION, SUM(A2.Sales) SALES FROM GeographyA1,Store_Information A2 WHERE A1.store_name = A2.store_name GROUP BY A1.region_name，结 果 ： REGION SALES East $700

12、 West $205013. 外部连接：我们会在 WHERE子句中要选出所有资料的那个 表格之后加上一个 (+) 来代 表说这个表格中的所有资料我 们都要。 SELECT A1.store_name, SUM(A2.Sales) SALES FROM GeorgraphyA1,Store_Information A2 WHERE A1.store_name = A2.store_name (+) GROUP BY A1.store_name 我们在这里是使用了 Oracle 的 外部 连 接 语 法 。 结 果 ：store_name SALES Boston $700 New York Lo

13、s Angeles $1800 San Diego $250请注意： 当第二个表格没有相对的资料时， SQL 会 传回 NULL 值。在这一个例子 中， New York 并不存在于 Store_Information 表格，所以 它的 SALES 栏位是 NULL。14. Subquery ， 当我们在 WHERE 子 句 或 WHERE 子 句 或 HAVING 子句中插入另一个 SQL 语句时， 我们就有一个 subquery 的架构。它有连接表 格的作用。 Subquery 的语法如 下： SELECT 栏位 1 FROM 表格 WHERE 栏位 2 比较运 算素 (SELECT 栏位

14、 1 FROM 表格 WHERE 条件)。我们要运 用 subquery 来找出所有在西 部的店的营业额。我们可以用 下面的 SQL 来达至我们的目 的： SELECT SUM(Sales) FROM Store_Information WHERE Store_name IN (SELECT store_name FROM Geography WHERE region_name = West) 结果： SUM(Sales) 205015. UNION 指令的目的是将两个SQL 语句的结果合并起来，与 select distinct 类似，读出的是 不重复的结果。 UNION 的语法 如下： S

15、QL 语句 1 UNION SQL 语 句 2 例 子 ： SELECT Date FROMStore_InformationUNION SELECT Date FROM Internet_Sales16. UNION ALL ：与 union 不同之 处在于读出所有的资料而不管 其重复不重复。17. INTERSECT 则比较像 AND ( 这个值要存在于第一句和第 二句才会被选出)。 UNION 是 联集，而 INTERSECT 是交集。INTERSECT 的语法如下： SQL 语句 1 INTERSECT SQL 语句 218. MINUS 指令是运用 在两个 SQL 语句上。它先找出第

16、一个 SQL 语句所产生的结果，然后 看这些结果 有没有 在第 二个 SQL 语句的结果中。如果有的 话，那这一笔资料就被去除， 而不会在最后的结果中出现。语 法 SQL 语句 1 MINUS SQL 语句 219. CONCATO只允许两个参数，在Oracle 中，我们可以用|串连 多个字串，在 SQL Server 中可以 用+串连多个字串。例子 1， MY SQL ： SELECT CONCAT(region_name,store_na me) FROM Geography WHERE store_name = Boston; 结果： EastBoston 。例子 2， Oracle S

17、ELECT region_name | | store_name FROM Geography WHERE store_name = Boston; 结果： East Boston 例子 3： SQL Server：SELECTregion_name + + store_name FROM Geography WHERE store_name = Boston; 结果： East Boston20. SQL 中的 substring 函数是 用来抓出一个栏位资料中的其 中一部分。这个函数的名称在 不同的资料库中不 完全一样： MySQL： SUBSTR(), SUBSTRING() Oracl

18、e： SUBSTR() SQL Server： SUBSTRING() 。最常用至的方式 如下 (在这里我们用 SUBSTR() 为例)： SUBSTR(str,pos): 由 中，选出所有从第位置开始的 字元。请注意，这个语法不适 用 于 SQL Server 上 。 SUBSTR(str,pos,len): 由中的第 位置开始，选出接下去的个字 元。例子：Region-nameStore-nameeastbostonEastNewyorkwestLosangeleswestSandiego例 1:SELECTSUBSTR(store_name, 3) FROMGeography WHERE

19、store_name= Los Angeles; 结 果 ： s Angeles例2：SELECTSUBSTR(store_name,2,4) FROM Geography WHERE store_name = San Diego; 结果： an D21. SQL 中的 TRIM 函数是用来移 除掉一个字串中的字头或字尾。 最常见的用途是移除字首或字尾 的空白。这 个函数在不同的资料 库中有不同的名称： MySQL： TRIM(), RTRIM(), LTRIM() Oracle： RTRIM(), LTRIM() SQL Server： RTRIM(), LTRIM() 各种 trim 函数

20、 的语法如下：TRIM(位置要 移除的字串 FROM 字串)： 位置 的可能值为 LEADING (起头), TRAILING (结尾), or BOTH (起头 及结尾)。 这个函数将把 要移 除的字串从字串的起头、结尾， 或是起头及结尾移除。如果 我们 没有列出 要移除的字串 是什 么的话，那空白就会被移除。 LTRIM(字串):将所有字串起头的 空白移除。RTRIM(字串)：将所有 字串结尾的空白移除。 例 1: SELECT TRIM( Sample );结果: Sample例 2 ： SELECT LTRIM( Sample ); 结果: Sample 例 3: SELECT RTR

21、IM( Sample ); 结果: Sample22. CREATE TABLE 的语法是： CREATE TABLE 表格名 (栏位 1 栏位 1 资料种类, 栏位 2 栏位 2 资料种类, . ) 若我们要建立我们 上面提过的顾客表格，我们就打入以 下的 SQL： CREATE TABLE customer (First_Name char(50), Last_Name char(50), Address char(50), City char(50), Country char(25), Birth_Date date)23. CREATE VIEW 建立一个视观表 的语法如下：CREA

22、TE VIEW VIEW_NAME AS SQl 语句。CREATE VIEW V_Customer AS SELECT First_Name, Last_Name, CountryFROM Customer 现在，我们就有一个叫做 V_Customer 的视观表24. 建立索引的语法如下： CREATE INDEX INDEX_NAME ON TABLE_NAME列名)，例子：CREATE INDEX IDX_CUSTOMER_LAST_NAME on CUSTOMER (Last_Name)25. 修改表格 中的 数 据用 ALTER TABLEALTER TABLE 语句如下：ALTER

23、 TABLE table_name 改变方式 改变方式 的详细写法会依我们想 要达到的目标而有所不同。再以上列 出的改变中， 改变方式 加一个栏位: ADD 栏位1 栏位 1 资料种类删去一个栏位: DROP 栏位 1 改变栏位名称: CHANGE 原本栏位 名 新栏位名 新栏位名资料种类 改变栏位的资料种类: MODIFY 栏 位 1 新资料种类以下我们用在 CREATE TABLE 一 页建出的customer表格来当作例 子：ALTER table customer add Gender char(1),添加 gender char这一栏 接下来，我们要把 Address 栏 目改名为A

24、ddr。这可以用以下的指 令达成：ALTER table customer change Address Addr char(50)26.建立主键(primark key) 以下举几个在建置新表格时设定主 键的方式：MySQL:CREATE TABLE Customer(SID integer,Last_Name varchar(30), First_Name varchar(30),PRIMARY KEY (SID);Oracle:CREATE TABLE Customer (SID integer PRIMARY KEY, Last_Name varchar(30), First_Name

25、 varchar(30);SQL Server:CREATE TABLE Customer (SID integer PRIMARY KEY, Last_Name varchar(30), First_Name varchar(30);以下则是以改变现有表格架构来设 定主键的方式：MySQL:ALTER TABLE Customer ADD PRIMARYKEY (SID);Oracle:ALTER TABLE Customer ADD PRIMARYKEY (SID);SQL Server:ALTER TABLE Customer ADD PRIMARYKEY (SID);请注意，在用 AL

26、TER TABLE语 句来添加主键之前，我们需要确认被 用来当做主键的栏位是设定为NOT NULL；也就是说，那个栏位一定不 能没有资料。27. 以下列出几个在建置 ORDERS表格时指定外来键的方式：MySQL:CREATE TABLE ORDERS (Order_ID integer, Order_Date date, Customer_SID integer, Amount double, Primary Key (Order_ID),Foreign Key (Customer_SID) references CUSTOMER(SID);Oracle:CREATE TABLE ORDER

27、S (Order_ID integer primary key, Order_Date date,Customer_SID integer references CUSTOMER(SID),Amount double);SQL Server:CREATE TABLE ORDERS (Order_ID integer primary key, Order_Date datetime,Customer_SID integer references CUSTOMER(SID),Amount double);以下的例子则是藉着改变表格架构 来指定外来键。这里假设 ORDERS 表格已经被建置，而外来键

28、尚 未被指定：MySQL:ALTER TABLE ORDERSADD FOREIGN KEY (customer_sid) REFERENCES CUSTOMER(sid);Oracle:ALTER TABLE ORDERSADD (CONSTRAINT fk_orders1) FOREIGN KEY (customer_sid) REFERENCES CUSTOMER(sid);SQL Server:ALTER TABLE ORDERS ADD FOREIGN KEY (customer_sid) REFERENCES CUSTOMER(sid);28. 清除表格 SQL 有提供一个 DRO

29、P TABLE 的语法来让我 们清除表格 (DROP VIEW 删除视图)。 DROP TABLE 的语法是：DROP TABLE 表格名 我们如果要清除在 SQL CREATE 中 建立的顾客表格，我们就打入：DROP TABLE customer.29. TRUNCATE TABLE 在这个指令 之下，表格中的资料会完全消失，可 是表格本身会继续存在。 TRUNCATE TABLE的语法为下：TRUNCATE TABLE 表格名 所以，我们如果要清除在 SQL Create 那一页建立的顾客表格之内的 资料，我们就打入：TRUNCATE TABLE customer.30. 一次输入一笔资

30、料的语法如下： INSERT INTO 表格名 (栏位 1, 栏 位 2, .) VALUES (值1, 值 2, .) 例子我们要加以下的这一笔资料进 去这个表格：在 January 10, 1999， Los Angeles 店有 $900 的营业额。 我们就打入以下的 SQL 语句： INSERT INTO Store_Information (store_name, Sales, Date) VALUES (Los Angeles, 900, Jan-10-1999)第二种 INSERT INTO 能够让我 们一次输入多笔的资料。一次输入多 笔的资料的语法是：INSERT INTO 表

31、格 1 (栏位 1, 栏 位 2, .)SELECT 栏位 3, 栏位 4, . FROM 表格 2举例来说，若我们想要将1998 年 的 营 业 额 资 料 放 入Store_Information 表格，而我们知 道 资 料 的 来 源 是 可 以 由Sales_Information 表格取得的话， 那我们就可以打入以下的 SQL：INSERT INTO Store_Information (store_name, Sales, Date)SELECT store_name, Sales, Date FROM Sales_InformationWHERE Year(Date) = 199

32、831. 我们有时候可能会需要修改表格 中的资料。在这个时候，我们就需要 用到 UPDATE 指令。这个指令的语 法是：UPDATE 表格名SET 栏位 1 = 新值WHERE 条件我们发现说 Los Angeles 在 ja/38/1999的营业额实际上是$500， 而不是表格中所储存的 $300，因此 我们用以下的SQL来修改那一笔资 料：UPDATE Store_InformationSET Sales = 500WHERE store_name = Los Angeles AND Date = Jan-08-1999 也可以同时修改几个栏位值UPDATE 表格SET 栏位 1 = 值1

33、, 栏位 2 = 值 2WHERE 条件把所有学生的年龄加2。Update student set age=age+232. DELETE 在某些情况下，我们会需 要直接由数据库中去除一些资料。这 可以藉由 DELETE FROM 指令来达 成。它的语法是：DELETE FROM 表格名 WHERE 条 件例子：将有关 Los Angeles 的资料全 部去除DELETE FROM Store_Information WHERE store_name = Los Angeles33. 当要查询的是指定表格的所有属 性的时候，可以用*表示，例如：查 询数据库中全体学生的详细信息，这 些信息包括学

34、生的学号、姓名、年龄、 性别等等，这时就可以用： SELECT * FROM STUDENT 。 等同 于 SELECT no,name,age,sex FORM STUDENT34杳询条件谓词比较 、一!= ! 确定范围J / 7 丿丿丿Betweedand,notbetweed and确定隼合In not in字符匹配like not like空值Is null is not null1 IJzzL多重条件And or例如：查询选修了3 门以上课程学生 的 学 号 SELECT NO FROM CHOOSE-COURSE GROUP BY NO HAVING COUNT(*)335.EXI

35、STS谓词的子查询，例子：使 用 NOT EXISTS 的查询，求不选修 2 号课程的学生姓名。SELECT name FROM student WHRER NOT EXISTS (SELECT *FROM choose-course WHERE no=student.no AND seq=2)36.INSERT 插入语法为：INSERTINTO 表名(字段名 ,字 段名VALUES(常 量，常量.) 例句：把一个新学生： 99006，姓名 “张明“，信息系，20岁，插入表中。INSERT INTO student VALUES (99006,张明,信息系,20) 例句：多记录插入，对每一个系

36、求学 生的平均年龄，并把结果存入到数据库中。CREATE TABLE deptage(dept CHAR(15)Avgage SMALLINT)INSERTINTO deptage (dept,avgage)SELECT dept,AVG(age)FORM studentGROUP BY dept37. 授权，语法：GRANT 权 限,权限.ON 对 象 类型对象名TO 用户,用户.例句：把查询 course 表的权限授给用户 user1GRANT SELECTNO TABLE courseTO user138. 收回权限 REVOKE 例句：把用户 user4 修改学生学号的权限收回REVO

37、KE UPDATE (grade) ONTABLE choose-course FROM user4 39JDBC 类类名说 明Java.sql.date/drivermanager/driv erproperty info/time/timestamp/typesJav aJDBC类Java.sql.callablestatement/conne ction/databasemetadata/driver/p reparedstatement/resultset/resul tsetmetadata/statement接 口 类Java.sql.datatrunvation/sqlexcepti on/sql异学