面试笔试--SQL语句

SQL语言由命令、子句、运算和集合函数等构成。在SQL中，数据定义语言DDL（用来建立及定义数据表、字段以及索引等数据库结构）包含的命令有CREATE、DROP、ALTER；数据操纵语言DML（用来提供数据的查询、排序以及筛选数据等功能）包含的命令有SELECT、INSERT、UPDATE、DELETE。

一、SQL语句

（1）Select 查询语句
语法：SELECT [ALL|DISTINCT] <目标列表达式> [AS 列名]
[,<目标列表达式> [AS 列名] ...] FROM <表名> [，<表名>…]
[WHERE <条件表达式> [AND|OR <条件表达式>...]
[GROUP BY 列名 [HAVING <条件表达式>>
[ORDER BY 列名 [ASC | DESC> 
解释：[ALL|DISTINCT]   ALL：全部； DISTINCT：不包括重复行
<目标列表达式> 对字段可使用AVG、COUNT、SUM、MIN、MAX、运算符等
<条件表达式> 
查询条件 谓词
比较 =、>,<,>=,<=,!=,<>,
确定范围 BETWEEN AND、NOT BETWEEN AND
确定集合 IN、NOT IN
字符匹配 LIKE（“%”匹配任何长度，“_”匹配一个字符）、NOT LIKE
空值 IS NULL、IS NOT NULL
子查询 ANY、ALL、EXISTS
集合查询 UNION（并）、INTERSECT（交）、MINUS（差）
多重条件 AND、OR、NOT
<GROUP BY 列名> 对查询结果分组
[HAVING <条件表达式>] 分组筛选条件
[ORDER BY 列名 [ASC | DESC> 对查询结果排序；ASC：升序 DESC：降序
例1： select student.sno as 学号, student.name as 姓名, course as 课程名, score as 成绩 from score,student where student.sid=score.sid and score.sid=:sid
例2：select student.sno as 学号, student.name as 姓名,AVG(score) as 平均分 from score,student where student.sid=score.sid and student.class=:class and (term=5 or term=6) group by student.sno, student.name having count(*)>0 order by 平均分 DESC
例3：select * from score where sid like '9634'
例4：select * from student where class in (select class from student where name='陈小小')

在对表进行连接时，最常用的连接条件是等值连接，也就是使两个表中对应列相等所进行的连接，通常一个列是所在表的主键，另一个列是所在表的主键或外键，只有这样的等值连接才有意义。

比如说有两张表分别为courses表（cno，cname，credit）和enrolls表（sno，cno，grade）。

查询所有学生所选的课程名称：

Select  sno,  enrolls.cno, cname, grade  from  enrolls, courses WHERE enrolls.cno = courses.cno

单表查询时，去掉重复行

比如查询Student表中所有系的名称，去掉重复行

Select  distinct  department  From  student

常用条件表达式运算符IN,NOT IN；between,and,not like.

在上面的Student表和enrolls表中，查询成绩在80分以上的的学号和姓名。

Select  sno,  sname  From  Student WHERE  sno  IN (select sno FROM enrolls Where grade > 80)

上面的SQL语句也是嵌套查询。

Having子句，筛选出只满足指定条件的组。注意的是，该子句只能同GROUP BY子句配合使用，筛选出符合条件的分组信息。

类似题目如下：查询Student表中每个系有三个以上的学生的所在系。

Select  department  From  Student  Group  BY department  Having  COUNT(*) >= 3。

（2）INSERT插入语句
语法：INSERT INTO <表名> [（<字段名1> [,<字段名2>, ...])] VALUES (<常量1> [,<常量2>, ...]) 
语法：INSERT INTO <表名> [（<字段名1> [,<字段名2>, ...])] 子查询 
例子：INSERT INTO 借书表（rid,bookidx,bdate）VALUES (edit1.text,edit2.text,date)
例子：INSERT INTO score1(sno,name) SELECT sno,name FROM student WHERE class=’9634’

1、单行插入，比如在上面的Student表中插入学生王强的信息。

Insert  into  Student(Sno,Sname,Ssex,Sage,Sdept)

Values(‘2005012’,’王强’,’男’,18,’计算机’)

2、多行插入，比如每个学生都要修操作系统c2这门课，将选课信息加入表enrolls中。

INSERT  INTTO  enrolls(sno,cno)

SELECT  Sno, ‘c2’  FROM  Student

 

（3）UPDATE-SQL
语法：UPDATE 〈表名〉
SET 列名1 = 常量表达式1[,列名2 = 常量表达式2 ...]
WHERE <条件表达式> [AND|OR <条件表达式>...]
例子：update score set credithour=4 where course='数据库'

比如给enrolls这个表中选修了操作系统这门课的学生的成绩修改为60分。

UPDATE enrolls

SET grade = 60

WHER cno IN

(SELECT  cno  FROM  courses  WHERE  cname  = ‘操作系统’)

（4）DELETE-SQL
语法：DELETE FROM〈表名〉[WHERE <条件表达式> [AND|OR <条件表达式>...>
例子：Delete from student where sid='003101'

比如删除Student表中年龄在20岁以上的学生

Delete   from  Student   where  Sage > 20

删除整张表的数据 delete from Student

（5）CREATE TABLE
CREATE TABLE | DBF TableName1 [NAME LongTableName] [FREE]
(FieldName1 FieldType [(nFieldWidth [, nPrecision])]
  [NULL | NOT NULL] 
  [CHECK lExpression1 [ERROR cMessageText1>
  [DEFAULT eExpression1]
  [PRIMARY KEY | UNIQUE]
  [REFERENCES TableName2 [TAG TagName1>
  [NOCPTRANS]
[, FieldName2 ...]
  [, PRIMARY KEY eExpression2 TAG TagName2
|, UNIQUE eExpression3 TAG TagName3]
  [, FOREIGN KEY eExpression4 TAG TagName4 [NODUP]
  REFERENCES TableName3 [TAG TagName5>
  [, CHECK lExpression2 [ERROR cMessageText2>)
| FROM ARRAY ArrayName

比如创建一个学生表student，它由学号Sno，姓名Sname，性别Ssex，年龄Sage，所在系Sdept五个属性组成。其中学号不能为空，值是唯一的，并且姓名取值也唯一。

CREATE TABLE Student

(Sno    CHAR(10) NOT NULL UNIQUE,

 Sname  CHAR(20) UNIQUE,

 Ssex    char(2),

Sage    INT,

Sdept  char(15)

)

（6）ALTER TABLE
ALTER TABLE TableName1
ADD | ALTER [COLUMN] FieldName1
  FieldType [(nFieldWidth [, nPrecision])]
  [NULL | NOT NULL]
  [CHECK lExpression1 [ERROR cMessageText1>
  [DEFAULT eExpression1]
  [PRIMARY KEY | UNIQUE]
  [REFERENCES TableName2 [TAG TagName1>
  [NOCPTRANS]

1、增加列Stel

Alter table Student ADD Stel Char(12)

2、删除列Stel

Alter Table Student DROP COLUMN Stel

3、修改列Sdept

ALTER  Table  Student  ALTER  COLUMN  Sdept  CHAR(8)  Sno CHAR(8)

（7）DROP TABLE
DROP TABLE [路径名.]表名

DROP  INDEX  Student  S_INDEX

（8）CREATE INDEX
CREATE INDEX index-name ON table-name（column[，column…]）

例：CREATE INDEX uspa ON 口令表(user,password)

在表Student中建立按年龄Sage升序建立索引

建立索引：Create  INDEX  S_INDEX  ON  Student(Sage)

（9）DROP INDEX
DROP INDEX table-name.index-name|PRIMARY
例：DROP INDEX 口令表.uspa

（10）存储过程（两个参数，根据输入的参数查询好数据后返回给输出的参数）

比如创建一个存储过程procGetDepName，它带有1个输入参数@sno，还带有1个输出参数@DepartmentName，功能：根据输入的学号，找到该生所在的院系，输出院系名称。

create procedure procGetDepName

@sno nvarchar(10),

@DepartmentName nvarchar(20) output

as

begin

select @DepartmentName = DepartmentName

from Department d, Student s

where d.DepartmentID = s.DepartmentID and

s.sno = @sno

end

二、在程序中使用静态SQL语句
  在程序设计阶段，将SQL命令文本作为TQuery组件的SQL属性值设置。

三、在程序中使用动态SQL语句
  动态SQL语句是指在SQL语句中包含有参数变量的SQL语句（如：select * from student where class=:class），在程序中可以为参数赋值。给参数赋值的方法有：  在程序设计阶段，将SQL命令文本作为TQuery组件的SQL属性值设置。

1、利用参数编辑器为参数赋值
  选中TQuery组件，在对象监视器OI中点取Params项，在弹出的参数编辑窗口中设置参数的值。
例：SELECT bookidx AS 书号,藏书表.bookname AS 书名, bdate AS 借书日期 FROM 借书表,藏书表 where 借书表.bookidx=藏书表.bookidx and rid=:rid

  2、在程序运行中通过程序为参数赋值
（1）根据参数在SQL语句中出现的顺序，使用TQuery的Params属性为参数赋值；

例：在借书表中插入一条记录
with Query1 do
begin
  SQL.clear;
  SQL.add('Insert Into 借书表（bookidx,rid,rdate）');
  SQl.add('Values(:bookidx,:rid,:rdate)');
  Params[0].AsString := bookidxEdit.Text;
  Params[1].AsString := ridEdit.Text;
  Params[2] .AsDate:=date;
  ExecSQL;
End;

（2）根据SQL语句中的参数名字，调用ParamByName方法为参数赋值；
ParamByName('bookidx').AsString := bookidxEdit.Text;
ParamByName('rid').AsString := ridEdit.Text;
ParamByName('rdate') .AsDate:=date;
ExecSQL;
有：AsString 、AsSmallInt 、AsInteger 、AsWord 、AsBoolean 、AsFloat 、AsCurrency 、AsBCD 、AsDate 、AsTime 、AsDateTime转换函数

3、使用数据源为参数赋值
  把TQuery的DataSource属性设置为另一个数据源（T DataSource名字），Delphi会把未赋值的参数与指定的数据源中的各字段相比较，并将匹配的字段的值赋给未赋值的参数，可实现主表—明细表应用。

四、对TQuery返回的数据集进行修改
  一般情况下，TQuery返回的数据集是只读的，不能修改；
  对不包含集操作（如：SUM、COUNT）的单表SELECT查询，设置TQuery的RequsetLive属性为True，则可修改TQuery返回的数据集。

var
I: Integer;
ListItem: string;
begin
for I := 0 to Query1.ParamCount - 1 do
begin
  ListItem := ListBox1.Items[I];
  case Query1.Params[I].DataType of
    ftString:
    Query1.Params[I].AsString := ListItem;
    ftSmallInt:
    Query1.Params[I].AsSmallInt := StrToIntDef(ListItem, 0);
    ftInteger:
    Query1.Params[I].AsInteger := StrToIntDef(ListItem, 0);
    ftWord:
    Query1.Params[I].AsWord := StrToIntDef(ListItem, 0);
    ftBoolean:
    begin
      if ListItem = 'True' then
        Query1.Params[I].AsBoolean := True
      else
        Query1.Params[I].AsBoolean := False;
    end;
    ftFloat:
    Query1.Params[I].AsFloat := StrToFloat(ListItem);
    ftCurrency:
    Query1.Params[I].AsCurrency := StrToFloat(ListItem);
    ftBCD:
    Query1.Params[I].AsBCD := StrToCurr(ListItem);
    ftDate:
    Query1.Params[I].AsDate := StrToDate(ListItem);
    ftTime:
    Query1.Params[I].AsTime := StrToTime(ListItem);
    ftDateTime:
    Query1.Params[I].AsDateTime := StrToDateTime(ListItem);
  end;
end;
end; 


五、数据结构常用数据类型和作用

第一大类:整数数据

bit:bit数据类型代表0，1或NULL,就是表示true,false.占用1byte.

int:以4个字节来存储正负数。可存储范围为:-2^31至2^31-1。

smallint:以2个字节来存储正负数。存储范围为:-2^15至2^15-1。

tinyint: 是最小的整数类型，仅用1字节,范围:0至此^8-1。

 

第二大类:精确数值数据

numeric:表示的数字可以达到38位,存储数据时所用的字节数目会随着使用权用位数的多少变化。

decimal:和numeric差不多。

 

第三大类:近似浮点数值数据

float:用8个字节来存储数据.最多可为53位。范围为:-1.79E+308至1.79E+308。

real:位数为24，用4个字节，数字范围:-3.04E+38至3.04E+38。

 

第四大类:日期时间数据

datatime:表示时间范围可以表示从1753/1/1至9999/12/31，时间可以表示到3.33/1000秒.使用8个字节。

smalldatetime:表示时间范围可以表示从1900/1/1至2079/12/31，使用4个字节。

 

第五大类:字符串数据

char:长度是设定的，不可变的。最短为1字节,最长为8000个字节.不足的长度会用空白补上。

varchar:长度是可变的。最短为1字节,最长为8000个字节，尾部的空白会去掉。

text:长宽也是设定的，最长可以存放2G的数据。

 

第六大类:Unincode字符串数据

nchar:长度是设定的，最短为1字节,最长为4000个字节。不足的长度会用空白补上，储存一个字符需要2个字节。

nvarchar:长度是设定的，可变的。最短为1字节，最长为4000个字节.尾部的空白会去掉。储存一个字符需要2个字节。

ntext:长度是设定的,最短为1字节,最长为2G.尾部的空白会去掉,储存一个字符需要2个字节。

 

第七大类:货币数据类型

money:记录金额范围为:-92233720368577.5808至92233720368577.5807.需要8 个字节。

smallmoney:记录金额范围为:-214748.3648至214748.36487.需要4个字节。

 

第八大类:标记数据

timestamp:该数据类型在每一个表中是唯一的!当表中的一个记录更改时,该记录的timestamp字段会自动更新.

uniqueidentifier:用于识别数据库里面许多个表的唯一一个记录.

 

第九大类:二进制码字符串数据

binary:固定长度的二进制码字符串字段,最短为1,最长为8000。

varbinary:与binary差异为数据尾部是00时,varbinary会将其去掉。

image:为可变长度的二进制码字符串,最长2G。

SQL学习地址