数据库系统 ##SQL语言简单查询

数据库系统

SQL语言简单查询

带分号
字符串用单引号
相等判断 =

创建数据库

//定义表ddl
create table Student(S# char(8) not null,Sname char(10),
    Ssex char(2),Sage integer,D# char(2),Sclass char(6));
//添加元组，行信息dml
insert into Student(S#,Sname,Ssex,Sage,D#,Sclass) 
    values('23123','张思','女',20,'03','4545');

简单查询

select * from Student;
select S#,Sname,Ssex,Sage,D# from Student;
select * from Student where Sage<20;
select distinct S# from SC where score>80;
select distinct S# from SC where score>80 
    order by S# desc;  
select Sname from Student where Sname not like '%(1,多个字符)
    _(单个字符,2个表示一个汉字)\%';

多表查询：多表做笛卡尔积后where

select Sname from Student,SC where Student.S#=SC.S# 
    and SC.C#='001' order by score desc;
//使用别名来与自己做笛卡尔积
select t1.Tname as Teacher1,t2.Tname as Teacher2 from Teacher t1,Teacher t2 
    where t1.salary>t2.salary;
//单表既学过001又学过002课,别名连接

子查询：利用select更新

//select结果当做多条value
insert into st(s#,sname) select s#,sname from student
    where sname like '%伟';
//删除所有元组
delete from student;
delete from student where Sage<20;
update student set sage =sage+10 where sage<20;

数据库定义修正和撤销

alter table Student add Saddr char[40],PID char[18];
alter table Student modify Saddr char[50];
alter table Student drop unique(S#);
drop table Student;
use databasename;
close databasename;