SQL第二讲 表的SQL操作

SQL是结构化的查询语言，同时又是高度非过程化的语言

SQL的功能

数据查询	数据的操作(针对记录)	数据的定义(针对结构)	数据控制
select	insert	create	revoke
/	update	alter	grante
/	delete	drop	/

        

注:SQL的核心是数据查询

一.库的操作

1.建立库:

create database [库名];

考题:建立数据库"学生档案"

create database 学生档案;

2.打开库:

open database [库名];

3.库中添加表:

add table [表名];

4.库中移除表:

remove table 表名 [delete];

注:没有delete只移除表不删除,有delete移除且删除;

5.删除库:

delete database 库名 [delete];

注:[delete]用来删除表

6.关闭库:

close database;

二.单表的SQL

1.表的建立

数据类型

字符型	数值型	逻辑型	日期型
C	N	L	D
"300"	300	.T./.F.	{^1985-12-23}
必须指定宽度	必须指定宽度	/	/

主键	唯一索引	非空	空
primary key	unique	not null	null

格式:

create table 表名(字段 类型,字段 类型......); 
--例
create table student (学号 n(6),姓名 c(6));

练习1

create table AB
(编号 c(5),名称 c(8),启用日期 d,价格 n(9,2),主要设备 l);

练习2 

create table dab 
(编号 n(7),姓名 c(6),性别 l,基本工资 n(7,2),实发工资 n(7,2),出生年月 d);

练习3

create database stu;
create table 学生表
(学号 c(6)primary key not null,姓名 c(8),性别 c(2),年龄 n(3),所在系 c(10));

2.表结构的修改

字段修改

增加字段	alter table 表名 add 字段 类型;
修改字段	alter table 表名 alter 字段 类型;
重命名字段	alter table 表名 rename 字段 to 新字段;
删除字段	alter table 表名 drop 字段;

 练习1

alter table student add 电话号码 c(11);

练习2

alter table student alter 学号 c(8);

记录的修改(插,更,删)

• 记录的插入

格式1:insert into 表名 values(值1,值2......);

若值的个数≠字段个数,必须指定字段名即格式二

格式2:insert into 表名 (字段1,字段2,......) values (值1,值2,......);

 练习1

insert into ab values ("6","格力空调",{^1999-01-01},5000,"学校办公室",.T.);

练习2.2 

insert into dab (编号,姓名,性别,基本工资,补贴,出生年月) 
values (5,"张涛",.t.,1000,500,{^1992-05-25});

2. order by 排序

top n	top n percent
表示查前n个	表示查n%个

★通常放在select之后

例：查成绩最高的前三名学生

select top 3 * form student order by 成绩 desc;

例：查进校最早的学生（表：student        进校日期）

select top 1 * from student order by 进校日期 asc;

练习：查身高由高到低前一半的学生（表：学生        身高）

select top 50 percent * from 学生 order by 身高 desc;

3.函数查询 

max

min

avg

sum

count

例：查外语系的最高分数

select max(成绩) as 最高分 from student where 所在系="外语系";

例：查少数民族平均分

select avg(成绩) as 平均分 from student where 民族!="汉";

例：统计男生人数

select cuont(*) as 人数 from student where 性别="男";

例：查每个职工的姓名及出生年（查询字段 姓名   出生年）

 

select 姓名,year(出生日期) as 出生年 from zg;

练习：查询每个商品的销售金额（表:sp）

select 商品名称,单价*销售数量 as 销售金额 from sp; 

练习3：统计IPAD的销量之和（表：商品表）

select 商品名,sum(数量) as 数量和 from 商品 where 商品名="IPAD";

left(字段,n)	right(字段，n)	substr（字段,从几,取几）	len(字段)
左取	右取	任意取	求字段长度

例:查所有姓张的学生

以下都是两种方法

select * from student where 姓名 like “张%”;

select * from student where left(姓名,2)="张"；

 练习1：查所有第二个字是小的学生

select * from student where 姓名 like "_小%";

select * from student where substr(姓名,3,2)="小";

练习2：查所有姓名是3个字的学生

select * from student where 姓名 like "___";

select * from student where len(姓名)=6;

实战1:

select * from 学生 where 学号 like "2002__04";

select * from 学生 where substr(学号,1,4)="2002" and right(学号,2)="04";

4.分组查询:        各        每        按        group by 字段 [having 条件]

格式：

 select 分组字段,统计函数 as 新名称 from 表 group by 分组字段

例：统计各系平均分（表：student        所在系        成绩）

sselect 所在系,avg(成绩) as 平均分 from student group by 所在系;

例：统计各部门职工人数（表：zg        部门）

select 部门,count(*) as 人数 from zg group by 部门;

例：统计男女生的最高分、最低分、平均分

select 性别,max(成绩) as 最高分,min(成绩) as 最低分,avg(成绩) as 平均分 from student group by 性别；

练习1：查每个学生的选课数        查询字段：学号 姓名 选课数

select 学号,姓名,count(*) as 选课数 from 选课 group by 学号;

练习2：查每门课程被选修的次数        查询字段：选修课程  被选次数

select 选修课程,count(*) as 被选次数 from 选课 group by 选修课程;

练习3：统计每类产品的销售总额    查询字段：商品名，销售总额

select 商品名,sum(单价*数量) as 销售总额 from 商品 group by 商品名;

注：having表示对分组后的条件限定（从分组中在淘汰一部分）

                特征：至少或至多

练习3：统计销售总至少10000的每类产品的销售总额    查询字段：商品名，销售总额

select 商品名,sum(单价*数量) as 销售总额 from 商品 
group by 商品名
aving sum(单价*数量)>=10000;

例:查至少选修3门课程的每个学生的最高分、最低分

        查询字段：学号、最高分、最低分

select 学号,max(成绩) as 最高分,min(成绩) as 最低分 from 选课 group by 学号
having count(*)>=3;

练习1：查至少被选修2次的每门课程的平均分

        查询字段：选修课程  平均分

        （表：选课        学号        姓名        选修课程        成绩）

select 选修课程,avg(成绩) as 平均分 from 选课 group by 选修课程
having count(*)>=2;

练习2：查平均工资至少4000的每个部门的职工人数

（表：zg        部门         工资）

查询字段：部门  人数

select 部门,count(*) as 人数 from zg
group by 部门 having avg(工资)>=4000;

四、查询去向

1.SQL+into table 新表名----------------->新表

2.SQL+into cursor 临时表名------------>临时表中

3.SQL+into arror 数组名----------------->数组中

4.SQL+to file 文本文件名---------------->文本文件中

5.SQL+to printer--------------------------->打印机中

例：将student表中姓张的学生存储至一个新表new01.dbf中

selece * from student where 姓名 like “张%”
into table new01;

例：将student表中姓张的学生存储至一个数组y中

select * from student where 姓名 like "张%"
into array y;

练习：将工艺科职工存储至一个文本文件yyy.txt中（表：职工        部门）

select * from zg where 部门="工艺科" to file yyy;

五、多表的查询

1.联接：

select......from 1,2 where 1. =2.  [and 其他条件];

2.嵌套：

select......from 1 where 字段 in (select 字段 from 2 [where 其他条件])

3.超联：

select......from 1 join 2 on 1.=2. [where 其他条件]

例：查外语系的选课情况（表：student    选课）

select 选课.* from 选课,student where 选课.姓名=student.姓名 and 所在系="外语系";

select 选课.* from 选课 where 姓名 in(select 姓名 from student where 所在系="外语系");

select 选课.* from 选课 join student on 选课.姓名=student.姓名 where 所在系="外语系";

练习：查北京的供应商所发出的订单信息（表：供应商    订购单）

--联接
select 订购单.* from 供应商,订购单 
where 订购单.供应商号=供应商.供应商号 
and 地址="北京";
--嵌入
select 订购单.* from 订购单 where 供应商号 in
(select 供应商.供应商号 from 供应商 where 地址="北京");
--超联
select 订购单.* from 订购单 join 供应商 on 订购单.供应商号=供应商.供应商号
where 地址="北京";

实战：

--联接
select 学生信息.* from 学生信息,院系信息
where 学生信息.院系ID=院系信息.院系ID
and 院系名称="计算机系";
--嵌套
select 学生信息.* from 学生信息 where 院系ID 
in(select 院系ID from 院系信息 
where 院系名称="计算机系");
--超联
select 学生信息.* from 学生信息 join 院系信息 
on 学生信息.院系ID=院系信息.院系ID
where 院系名称="计算机系";

注：1）若查询字段来自多表，必须指定表名

        2）三表：

select ... from 1,2,3 where 1. =2.  and 2. =3.  [and 其他条件]

                                where 1. =3.  and 2. =3.  [and 其他条件] 

                                where 1. =2.  and 1. =3.  [and 其他条件]

六、综合案例及典型例题

1.“和”的理解

例：查北京和西安的供应商信息（表：供应商）

注：同一字段="甲" and 同一字段="乙"        ×

select * from 供应商 where 地址="西安" and 地址="北京";    --×
select * from 供应商 where 地址="西安" or 地址="北京";     --√
select * from 供应商 where 地址 in("西安","北京");         --√

2.“至少”二字的理解

        做法：分组之后再having

例：查各系人数至少3人的各系最高分、最低分

表： student        所在系  成绩

查询字段：所在系  最高分  最低分

select 所在系 max(成绩) as 最高分,min(成绩) as 最低分 from student 
group by 所在系 having cuont(*)>=3;

例：查部门工资至少4000的各部门人数（表：zg  工资   部门）

select 部门,count(*) as 人数 from zg 
group by 部门 having min(工资>=4000);

3."都"字的理解

例：查所有部门工资都高于4000的部门名称

        表：zg    部门    工资

        查询字段：部门

a.把他理解为至少二字的题型（至少最低工资>=4000）

select 部门 from zg group by 部门 having min(工资)>=4000;

b.not in 反向写  （相当于负负得正）

select 部门 from zg where 部门 not in(select 部门 from zg where 工资<=4000);

c.not exist 反向写

select 部门 from zg a where not exist
(select * from zg b where a.部门=b.部门 and 工资<=4000);

练习:查所有选课成绩均高于80的学生姓名（表：选课  ）

--a.
select 学号 from 选课 group by 选课成绩 having min(成绩)>=80;
--b
select 学号 from 选课 where 成绩 not in
(select 学号 from 选课 where 成绩<=80);
--c
select 学号 from 选课 a where not exist
(select *from 选课 b where a.学号=b.学号 and 成绩<=80);

4.综合练习

案例:0

--2
select 院系信息.* from 院系信息 where 院系ID in
(select 院系ID from 学生信息 where 姓名="赵和堂");
--3
select 院系名称 from 院系信息 where 地址 like "行政楼%";
--4
select 性别,count(*) as 人数 from 学生信息 group by 性别;
--5
select 院系信息.* from 院系信息 where 院系ID in
(select 院系ID from 学生信息 where 籍贯="河北");

案例1:

--1
select 书名,出版社 from 图书 where 第一作者="张三";
--2
select 图书编号,借书日期 from 借阅 where 还书日期 is NULL;
--3
select * from 读者 into cursor one;
--4
select 借书证号,姓名 from 读者 where 单位 like "%北京%";
--5
select 图书编号,借书日期 from 借阅 where year(借书日期)=2009;
--6
select 图书编号 from 读者,借阅 
where 读者.借书证号=借阅.借书证号 and 读者.职称="工程师";

--1
select * from 选课 where 成绩>=60 and 成绩<90;
--2
select * from 选课 where 成绩 is NULL;
--3
select top 10 percent * from 选课 order by desc where 课程号="101";
--4注
select 学生.* from 学生,选课 where 学生.学号=选课.学号 and 选课.成绩>=90;
--5
alter table 选课 add 等级 c(2);

--1
creat table teacher (
教师编号 text(6)primary key,
姓名 text(3)not NULL,
性别 text(1),
出生日期 date);
--2
select 书名,出版社 from 图书 
where 价格>20 and 价格<50
order by 价格 desc;
--3
slect 书号,操作员账号 from 借出 where 操作员账号 in
(select 账号 from 操作员 where 姓名="李凡");

create table student 
(Sno CHAR(6),
Sname VARCHAR(12),
Sex CHAR(2),
Sage INT,
Sdept VARCHAR(16)
);