SQL-复习题(SELECT 语句,建表,修改表)

1.建立名为Test的数据库；

create database Test on

(

name=Test_data,

filename='e:090610210Test_',

size =10mb,

maxsize=unlimited,

filegrowth=10%

)

log on

(

name=Test_log,

filename='e:090610210Test_',

size=1mb,

maxsize=5mb,

filegrowth=1mb

)

2.用CREATE语句按要求建立如下四个表，并写出相应的语句。

（1）供应商表S：

列名 说明 数据类型 约束

SNO 供应商号 CHAR(6) PRIMARY KEY

SNAME 供应商名 VARCHAR(20) NOT NULL

STATUS 供应商状态 VARCHAR(50)

CITY 所在城市 VARCHAR(50)

Create table S

(

Sno char(6) primary key(Sno),

Sname varchar(20) not null,

Status varchar(50),

City varchar(50)

)

（2）零件表P：

列名 说明 数据类型 约束

PNO 零件号 CHAR(6) PRIMARY KEY

PNAME 零件名 VARCHAR(20) NOT NULL

COLOR 颜色 CHAR(2)

WEIGHT 重量 NUMERIC(9,2) CHECK(WEIGHT>0

WEIGHT<=100)

Create table P

(

Pno char(6) primary key,

Pname varchar(20) not null,

Color char(2),

Weight numeric(9,2) check(weight>0 and weight <=100)

AND

)

（3）工程项目表J：

列名 说明 数据类型 约束

JNO 项目号 CHAR(6) PRIMARY KEY

JNAME 项目名 VARCHAR(20) NOT NULL

CITY 城市 VARCHAR(50)

Create table J

(

Jno char(6) primary key,

Jname varchar(20) not null,

City varchar(50)

)

（4）供应情况表SPJ：

列名 说明 数据类型 约束

SNO 供应商号 CHAR(6) NOT NULL

PNO 零件号 CHAR(6) NOT NULL

JNO 项目号 CHAR(6) NOT NULL

QTY 供应数量 SMALLINT DEFAULT 100

Create table SPJ

(

Sno char(6) not null,

Pno char(6) not null,

Jno char(6) not null,

Qty smallint default 100

)

3.利用SQL语句对表结构进行修改: 为零件表P增加一个规格(GUIGE)列，数据类型为字符，长度50。

Alter table p add guige char(50)

使用insert 语句像表中输入数据：

SNO SNAME STATUS CITY

S1 精益 20 天津

S2 盛锡 10 北京

Insert S values(‘S1’,’精益’,’20’,’天津’),(‘S2’,’盛锡’,’10’,’北京’)

PNO PNAME COLOR WEIGHT

P1 螺母 红 12

P2 螺栓 绿 17

Insert P (PNO,PNAME,COLOR,WEIGHT) values('P1','螺母','红',12),('P2','螺栓','绿',17)

JNO JNAME CITY

J1 一汽 北京

J2 半导体厂 南京

Insert J values(‘J1’,’一汽’,’北京’),(‘J2’,’半导体厂’,’南京’)

SN0 PNO JNO QTY

S1 P1 J1 200

S1 P2 J1 100

Insert SPJ values('S1','P1','J1',200),('S1','P2','J1',100)

4.按要求修改表中数据：

（1）将所有spj表中数量QTY每行加1。

update SPJ set qty=Qty+1

（2）修改“S1”、“P1”的QTY为98。

update SPJ set qty=98 where sno='s1'and pno='p1'

（3）删除工程项目表J中城市为“南京”的项目信息。

Delete J where city='南京'

5.按要求写出对应查询数据的SQL语句

（1）查询全体零件的信息。

Select * from P

（2）查询颜色为“红”的零件的零件号，零件名和四舍五入到整数后的零件重量。

Select Pno,Pname,Weight=ROUND(Weight,0) from P

（3）找出使用供应商S1锁供应零件的工程号码。

Select Jno from SPJ where Sno='S1'

（4）找出所有零件的名称、颜色、重量。

Select Pname,Color,Weight from P

(5) 找出工程项目J2使用的各种零件的名称及其数量。

Select ,Pname, Qty from SPJ s,P where Jno='J1' and =

(6)找出上海厂商供应的所有零件号码。

Select Pno from spj where in (select Sno from s where City='上海')

(7)列出使用上海产的零件的工程名称。

Select Pname from P where Pno in (Select Pno from spj where Sno in (select

Sno from s where City='上海'))

（8）找出没有使用天津产的零件的工程号码。

Select Jno from SPJ where Sno not in (select Sno from s where City='天津')

附加信息：

排序：

Order by [desc/asc](前一个是降序，后一个是降序)

Select top N [查询内容] from table_name order by desc[asc] (查询排名前N的数据)

带有Distinct关键字的SELECT语句用来消除重复行。

排名函数：

row_number

rank

dense_rank

内连接：

外连接：

Select eeID,JobCandidateID from ee as E

[LEFT | RIGHT | FULL] outer join didate as JC

on eeID=eeID

交叉连接：

Select 查询内容列表 from 表A cross join 表B

自然连接：

Select 查询内容列表 from table_name_A JOIN table_name_B ON

rID=eeID

约束有以下类型：

主键约束(primary key)

唯一约束(unique)

外键约束(foreign key reference)

检查约束(check)

默认约束(default)

添加约束条件：

alter table Student add constraint ChkIDCardNo check (约束条件列表)

使用规则：

create rule RuleScore as @Score>=0 and @Score<=100 //创建规则

sp_bindrule ‘RuleScore‘,‘‘ //绑定规则

查看数据库关于数据库的信息象拥有者，大小，创建日期，和状态可由下例来查看：

Sp_helpdb company

重命名数据库

sp_renamedb 'old_name', 'new_name‘

删除数据库

Drop database database_name

插入数据

insert employee[(参数列表)] values('xiaoxiao','济源')

删除表：

Drop table_name 或者 Truncate table table_name(后一个不激活触发器)

添加列：

Alter table table_name add 列名 数据类型 [not null,约束条件]

从其他表中的到数据，添加到另一个表中（表必须存在）

insert studentbak select * from student where StudentNo<='180107105'

从其他表中得到数据，添加到新表中（新表不能存在）

select * into New_tableName from old_tableName where Score>=80

修改表中数据：

UPDATE table_name SET column_name = value[,column_name = value] [FROM

table_name] [WHERE condition]

例如：

update score set Score=80 from student where tNo=tNo

and StudentName='孙小刚' and CourseNo=2

/****************分组汇总********************/

显示列表：聚合函数 或者 分组依据

针对原始数据进行的筛选：where

针对组进行的筛选：having

select 聚合函数 或者 分组依据

from 表

where 针对原始数据的条件

group by 分组依据

having 针对组的条件

order by 排序依据

/******************带明细的汇总***************/

显示列表：聚合的列表必须在前面出现

select 聚合函数 或者 分组依据

from 表

where 针对原始数据的条件

compute 分组（avg，sum）

/****************带明细的分组汇总 compute by********/

与order by 一起使用

select *

from 表

where 针对原始数据的条件

order by 分组依据

compute avg，sum by 分组依据

查询显示10月份出生的员工的员工号，出生日期，上司的员工号，出生日期

select eeID,ate,rID,ate

from ee AS EA

JOIN ee AS EB

ON rID=eeID

where month(ate)=10

统计出每年应聘入公司的男性员工人数，其中要求结果集中每组人数大于10人，且按人数升序排序

select count(*),year(hiredate)

from ee

where gender='m'

group by year(hiredate)

having count(employeeid)>10

order by 1 asc

根据产品的子目录(ProductSubcategoryID)分组，既能查看每个产品的明细标准成本(StandardCost)，又能查看每个子目录产品的标准成本平均值和该目录产品种数。

select productid,ProductSubcategoryID,standardcost

from t

order by ProductSubcategoryID

compute avg(standardcost),count(standardcost)

by ProductSubcategoryID

字符串处理函数：

Select [ALL|DISTINCT] [查询内容] from table_name where [条件列表]

1、 大小写转换

将字符转换为大写 select upper('abs')

将字符转换为小写 select lower('ABs')

2、 ASCII码与字符转换

求首字符的ASCII select ascii('Abs')

返回整数对应的ASCII字符 select char(66)

3、 返回由数字数据转换的字符

str( float_expression [ , length [ , decimal ] ] )

4、 字符串检索

CHARINDEX ( expression1 ,expression2 [ , start_location ] )

求子串在主串中的位置（子串的第一个字符在主串中的位置）

5、 字符串比较：SOUNDEX 和 DIFFERENCE

SOUNDEX 函数将字符串转换为四位数字代码，以在比较中使用。比较中忽略元音。

DIFFERENCE 函数比较两个字符串的 SOUNDEX 值，并评估它们之间的相似性，最后返回 0 到 4 之间的一个值，其中 4 表示匹配性最高。

6、 求子串

返回字符串中从左边开始指定个数的字符

LEFT ( character_expression , integer_expression )

返回字符串中从右边开始指定个数的字符

RIGHT ( character_expression , integer_expression )

求子串

SUBSTRING ( expression ,start , length )

7、 空格处理

返回删除了前导空格之后的字符表达式

LTRIM ( character_expression )

截断所有尾随空格后返回一个字符串

RTRIM ( character_expression )

返回由重复的空格组成的字符串

SPACE ( integer_expression )

8、 字符串替换

删除指定长度的字符，并在指定的起点处插入另一组字符

STUFF ( character_expression , start ,

length ,character_expression )

替换指定字串

REPLACE ( 'string_expression1' , 'string_expression2' ,

'string_expression3' )

9、 返回字符表达式的逆向表达式

reverse ( character_expression )

10、 求字符串长度

len(

string_expression

)

11、 字符串连接+