目录
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});
记录的更新
update 表名 set 字段=内容 [where 条件]
如:update zg 工资=工资+1000 where 部门="设计课";
注:1.若省略条件则更新所有记录
2.不支持百分数,一律化成小数
练习1.2
update 奖牌榜 set 奖牌总数=金牌数+银牌数+铜牌数;
练习2
1.将女职工补贴下调5% (表:gz 补贴 性别)
update gz set 补贴=补贴*0.95 where 性别="女";
2.计算所有同学的总评成绩,要求机试占30%,笔试占70%
(表:考试 机试 笔试 总评分)
update 考试 set 总评分=机试*0.3+笔试*0.7;
3.计算每个产品的销售额(表:商品表)
update 商品表 set 销售额=单价*数量;
记录的删除
delete from 表名 [where 条件];
①若省略条件,则删除所有
如:delete from zg where 部门="车间";
练习1
delete from score where 成绩=0;
练习2
删除非计算机系的学生(表:student 所在系)
delete from student where 所在系!="计算机系"
⭐️总结
删库 | delete database 库名 [delete] |
删表 | drop table 表名 |
删字段 | alter table 表名 drop 字段名 |
删记录 | delete from 表名 [where条件] |
删视图 | drop view 视图名 |
三.单表的查询
格式:select 字段/函数/表达式 from 表+辅助格式
如:select 姓名,性别,民族 from student
1.where 条件
不等于 | != / <> / # |
且/或 | and/or |
介于 | where 字段 between 小 and 大(包含小和大) |
year(D) month(D) day(D) | 求年 求月 求日 |
in 也可以表示或 | in(值1,值2,值3) |
通配查询 | where 字段 like "%/-" |
空值查询 | where 字段 is null where 字段 is not null |
去重复值 | distinct |
特别强调 not in/not between...and.../not like
例:查工资在3000----5000的职工姓名及所在部门(表:zg 部门 姓名)
selete 姓名,部门 from zg where 工资 between 3000 and 5000;
例:查3月或8月进校的学生
select *from student where month(进校日期)in(3,8);
练习:查北京或西安的供应商信息(表:供应商)
select * from 供应商 where 地址 in("北京","西安");
练习:
查不是3月和8月进校的学生(student)
查工资不在3000-4500的职工信息(表:zg)
查既不姓张也不姓孙的学生信息
select * from student where month(进校日期) not in (3,8);
select * from zg where 工资 not between 3000 and 4500;
select * from student where 姓名 not like "张%" and 姓名 not like "孙%"
2. order by 排序
升序 | asc |
降序 | desc |
select * from 学生 order by 性别 asc,成绩 desc;
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)
);