SQL语言的功能概述
DDL语句引导词:Create(建立),Alter(修改),Drop(撤销)
DML语句引导词:Insert,Delete,Update,Select
DCL语句引导词:Grant,Revoke
SQL
创建Database:create database 数据库名;
示例:创建课程学习数据库SCT
create database SCT;
创建Table:Create table 表名(列名 数据类型 [Primary key | Unique][Not null])[, 列名 数据类型 [Not null], ...]
create table Student( Snumber char(8) not null, Sname char(10), Ssex char(2), Sage integer, Dnumber char(2), Sclass char(6));
向表中追加元组:insert into 表名[(列名[,列名]...] values (值 [,值],...);
insert into Student Values('98030101', '张三', '男', 20, '03', '980301'); insert into Student (Snumber, Sname, Ssex, Sage, Dnumber, Sclass) Values('98030102', '张四', '女', 20, '03', '980301');
SQL提供结构形式一致但功能多样化的检索语句Select:Select 列名 [[,列名] ...] From 表名 [Where 检索条件];
select Sage, Sname from Student Where Sage <= 19; select Tname from Teacher Where (Salary < 1500 or Salary >2000) and Dnumber = '03';
结果唯一性问题,在检索结果中要求无重复元组,通过DISTINCT保留字实现。
select distinct Snumber from SC Where Score > 80;
结果排序问题,通过增加order by子句实现,order by 列名 [asc | desc] desc为降序,asc或省略为升序。
select Snumber from SC where Cnumber = '002' and Score > 80 order by score desc;
模糊查询问题,引入运算符like来表示,列名 [ [t not ] like “字符串”
select Snumber, Sname from Student where Sname like '张%'; select Sname from Student where Sname like '张_ _'; select Sname from Student where Sname not like '张%';
多表联合查询
Select 列名 [ [, 列名] … ]
From 表名1, 表名2, …
Where 件 检索条件 ;
select Sname from Student, SC, Course where Student.Snumber = SC.Snumber and SC.Cnumber = Course.Cnumber and Cname = '数据库' order by Score desc;
重名处理
Select 名 列名 as 名 列别名 [ [, 名 列名 as 列别名] … ]
From 表名1 as 表别名1, 表名2 as 表别名2, …
Where 件 检索条件 ;
select S1.Sname as Stud1, S2.Sname as Stud2 from Student S1, Student S2 where S1.Sage > S2.Sage;
更新操作
元组新增Insert:新增一个或一些元组到数据库的Table中
元组更新Update:对某些元组中的某些属性值进行重新设定
元组删除Delete:删除某些元组
单一元组新增命令形式: 插入一条指定元组值的元组
insert into 表名 [( 列名[ ,列名]…)]
values (值 [ ,值]…);
insert into Teacher (Tnumber, Tname, Dnumber, Salary) values ("005", " 阮小七", "03", "1250"); insert into Teacher values ("006", " 李小虎", "03", "950");
批数据新增命令形式:插入子查询结果中的若干条元组。待插入的元组由子查询给出。
insert into 表名 [( 列名[ ,列名]…)]
子查询;
insert into St (Snumber, Sname) select Snumber, Sname from Student where Sname like ‘%伟 ’ ; insert into St (Snumber, Sname) select Snumber, Sname from Student order by Sname;
元组删除Delete 命令: 删除满足指定条件的元组
Delete From 名 表名 [ Where 条件表达式] ;
delete from SC where Snumber = '98030101' ; delete from Student where Snumber in ( Select Snumber from SC where Score < 60 group by Snumber having Count(*)>= 4 );
元组更新Update 命令: 用指定要求的值更新指定表中满足指定条件的元组的指定列的值
Update 表名
Set 列名 = 式 表达式 | ( 子查询)
[ [ , 列名 = 式 表达式 | ( 子查询) ] … ]
[ Where 式 条件表达式] ;
update Teacher set Salary = Salary * 1.1 where Dnumber in ( select Dnumber from Dept where Dname = '计算机');
//示例:将张三同学001号课的成绩置为其班级该门课的平均成绩 update SC set Score = ( Select AVG(SC2.Score) from SC SC1, Student S1, SC SC2, Student S2 where S1.Sclass = S2.Sclass and SC1.S# = S1.S# and SC2.S# = S2.S# and S1.Sname=‘ 张三’ and SC1.C# = ‘001’ and SC1.C# = SC2.C# ) where C# = ‘001’ and S# in ( select S# from Student where Sname = ‘ 张三’ ) ;
修正基本表的定义
alter table tablename
[add {colname datatype, …}] 增加新列
[drop { 完整性约束名}] 删除完整性约束
[modify {colname datatype, …}] 修改列定义
//示例: 在学生表Student(S#,Sname,Ssex,Sage,D#,Sclass)基础上增加二列Saddr, PID Alter Table Student Add Saddr char[40], PID char[18] ; //示例:将上例表中Sname列的数据类型增加两个字符 Alter Table Student Modify Sname char(10) ; //示例:删除学生姓名必须取唯一值的约束 Alter Table Student Drop Unique( Sname );
撤消基本表
drop table 表名
//示例:撤消学生表Student Drop Table Student;