演练用幼功SQL语句

图片 1

演练用幼功SQL语句

com.mysql.jdbc.exception:jdbc4.MySQLSyntaxErrorException:Unknown column
‘man’ in ‘field list’

本文语句超过四分之二SQL语句出自《数据库系统概论》(第四版卡塔尔国王珊&萨师煊
,是大家上课用的讲义,以为特别不利,总计了内部第三章的SQL语句,比较实用,希望对我们有帮衬。总括如下,能够用来上学幼功的SQL语句。

数据库表中的字段:sno sname sage ssex

 

插入数据时: sno sname sage man

树立数据库

当然是想插队ssex这些字段的值为“man”,结果将man弄成了ssex字段名

CREATE DATABASE DB_Student

在插入时,给字符串类型的字段值加上双引号

 

建立表

CREATE TABLE Student

    (Sno CHAR(9) PRIMARY KEY,–主码

    Sname CHAR(20) UNIQUE,–唯一值

    Ssex CHAR(2),

    Sage SMALLINT,

    Sdept CHAR(20)

    );

CREATE TABLE Course

    (Cno CHAR(4) PRIMARY KEY,

    Cname char(40),

    Cpno CHAR(4),

    Ccredit SMALLINT,

    FOREIGN KEY (Cpno) REFERENCES Course(Cno)

    );

CREATE TABLE SC

    (Sno CHAR(9),

     Cno CHAR(4),

     Grade SMALLINT,

     PRIMARY KEY (Sno,Cno),

     FOREIGN KEY (Sno) REFERENCES Student(Sno),–外码

     FOREIGN KEY (Cno) REFERENCES Course(Cno)

    );

数据

 图片 1

表操作

alter table Student add S_entrance date–增加列

alter table student alter column Sage int–修改字段类型

alter table course add unique (Cname)–扩展唯意气风发性节制

drop table Student–删除基本表

drop table student cascade–删除基本表及相关依赖对象

 

创立索引

drop index stusname

 

查询数据

select sno,sname from student

select sname,sno,sdept from student

select sname,2004-sage from student

select sname,’Year of Birth:’,2000-sage, lower(sdept) from
student–查询结果第二列是几个算数表明式

select sname name,’Year of Birth:’ BIRTH,2002-sage birthday,LOWECRUISER(sdept)
department from student–LOWE瑞虎()小写字母

select sno from sc

select distinct sno from sc–消逝重复行

select sno from sc

select all sno from sc

select sname from student where sqept=’CS’

–=、>、<、>=、<=、!=、<>、!>、!<  比较的运算符

select sname,sage from student where sage<20

select distinct sno from sc where sage<20

 

select sname,sdept,sage from student where sage between 20 and 23

select sname,sdept,sage from student where sage not between 20 and 23

 

select sname,ssex from student where sdept in (‘CS’,’MA’,’IS’)

select sname,sage from student where sdept not in(‘CS’,’MA’,’IS’)

 

select * from student where sno like ‘200215121’

select * from student where sno=’200215121′

–字符相配

–% 放肆长度字符串,_ 跋扈单个字符,ESCAPE 转义字符

select sname,sno,ssex from student where sname like ‘刘%’

select sname from student where sname like ‘欧阳__’

select sname,sno from student where sname like ‘__阳%’

select sname,sno,ssex from student where sname not like ‘刘%’

select cno,ccredit from course where cname like ‘DB_design’ escape

select * from course where cname like ‘DB_%i__’ escape ”

select sno,cno from sc where grade is null –null 空值

select sno,cno from sc where grade is not null

select sname from student where sdept=’CS’ and sage<20

select sname,sage from studnet where sdept=’CS’ or sdept=’MA’ or
sdept=’IS’

select sno,grade from sc where cno=’3′ order by grade desc — order by
排序

select * from student order by sdept,sage desc –空值最大

–集中函数

select count(*) from student — count() 行数

select count(distinct sno) from sc

select avg(grade) from sc where cno=’1′ — avg() 平均数

select max(grade) from sc where cno=’1′ — max() 最大值

select sum(Ccredit) from sc,course where sno=’200215012′ and
sc.cno=course.cno — sum() 总数

–分组

select cno,count(sno) from sc group by cno

select sno from sc group by sno having count(*) >3 –having
给出选拔组的口径

–连接查询

select student. *,SC.* FROM STUDENT,SC where student.sno=sc.sno

select student.sno,sname,ssex,sage,sdept,cno,grade from student,sc where
student.sno =sc.sno

select first.cno,second.cpno from course first,course second fwhere
first.cpno=second.cno — 本身连接

select student.sno,sname,ssex,sage,sdept,cno,grade from student left out
join sc in (student.sno=sc.sno)–外连接

–from student left out join sc using (sno)

select student.sno,sname from student,sc where student.sno=sc.sno and
sc.cno=’2′ and sc.grade>90

select student.sno,sname,cname,grade from student,sc,course where
student.sno=sc.sno and sc.cno=course.cno

select sname from student where sno in (select sno from sc shere
con=’2′)

select sdept from student where sname=’刘晨’

select sno.sname,sdept from student where sdept=’CS’

–嵌套查询

select sno,sname,sdept from student where sdept in (select sdept from
studnet where sname=’刘晨’)

select sno,sname,sdept from student where sdept in (‘CS’)

select s1.sno,s1.sname,s1.sdept from student s1,student s2 where
s1.sdept =s2.sdept and s2.sname=’刘晨’

select sno,sname from student where sno in (select sno from sc where cno
in(select cno from course where cname=’新闻系统’))

select student.sno,sname from student ,sc,course where
student.sno=sc.sno and sc.cno =course.cno and course.cname=’新闻体系’

–内查询的结果是八个值,因而得以用=代替in

select sno,sname,sdept from student where sdpet=(se3lect sdept from
studnet where sname=’刘晨’)

select sno,sname,sdept from student
where(select sdept from student where sname=’刘晨’)=sdept

select sno,cno from sc x where grade >=(select avg(grade) from sc y
where y.sno=x.sno)

select avg(grade) from sc y where y.sno=’200215121′

select sno,cno from sc x where grade>=88

select sname,sage from student where sage <ANY (SELECT sage from
student where sdept=’CS’) and sdept <>’CS’

select sname,sage from student where sage<(select max(sage) from
student where sdept=’CS’) and sdept <> ‘CS’

select sname,sage from student where sage < all (select sage from
student where sdept =’CS’)

select sname,sage from student where sage<(select min(sage) from
student where sdept=’CS’) and sdept <>’CS’

 

select sname from student where exists(select * from sc where
sno=student.sno and cno=’1′)

select sname from student where not exists (select * from sc where
sno=student.sno and cno=’1′)

select sno.sname,sdept from student s1 where exists(select * from
studetn s2 where s2.sdept=s1.sdept and s2.sname=’刘晨’)

select sname from student where not exists (select * from course where
not exists(select * from sc where sno=student.sno and cno=course.cno))

select distinct sno frome sc scx where not exists (select * from sc scy
where scy.sno=’200215122′ and not exists(select * from sc scz where
scz.sno=scx.sno and scz.cno=scy.cno))

集结查询

select * from student where sdept =’CS’ union select * from student
where sage<=19 –union并操作

select sno from sc where cno=’1′ union select sno from sc where sc where
cno=’2′

select * from student where sdept=’cs’ intersect select * from student
where sage<=19 –intersect 交操作

select * from student where sdept=’cs’ and sage<=19

select sno from sc where cno=’1′ intersect select sno from sc where
cno=’2′

select sno from sc where cno=’1′ and sno in (select so from sc where
cno=’2′)

select * from student where sdept=’cs’ except select * from student
where sage<=’19’ –except 差操作

select * from student where sdept =’cs’ and sage>19

 

布置数据

insert into student(sno,sname,ssex,sdept,sage)
values(‘200215128′,’陈东’,’男’,’IS’,’18’)

insert into student values(‘200215126′,’张成敏’,’男’,’18’,’cs’)

insert into sc(sno,cno) values(‘200215128′,’1’)

insert into sc values(‘200215128′,’1’,null)

 

履新数据

create table dept_age(sdept char(15) avg_agea smallint)

insert into dept_age(sdept,avg_age) select sdept,avg(sage) from
student group by sdept

update student set sage=22 where sno=’200215121′

update student set sage=sage+1

update sc set grade=0 where ‘cs’=(select sdept from student where
student.sno=sc.sno)

update is_student set sname=’刘晨’ where sno=’200215122′

update student set sname=’刘晨’ where sno=’200212122′ and sdept=’IS’

delete from student where sno=’200215128′

delete from is_student where sno=’200215129′

delete from student where sno=’200215129′ and sdept=’IS’

delete from sc

delete fro sc where ‘cs’=(select sdept from student where
student.sno=sc.sno)

 

删去操作

delete from Student where Sno=’200215128’

delete from SC

delete from SC where ‘cs’ = (select Sdept from Student where
Student.Sno=SC.Sno)

 

开创视图

create view is_student

as

select sno,sname,sage from student where sdpet=’IS’

 

create view is_student

as

select sno,sname,sage from student where sdept=’IS’ with check option

 

create view is_s1(sno,sname,grade)

as

select student.sno,sname,grade from student,sc where student,sc where
sdept==’IS’ and student.sno=sc.sno and sc.cno=’1′

 

create view is_s2

as

select sno,sname,grade from is_s1 where grade>=90

 

create view bt_s(sno,sname,sbirth)

as

select sno,sname,2004-sage from student

 

create view s_g(sno,gavg)

as

select sno,avg(grade) from sc group by sno

 

create view f_student(f_sno,name,sex,age,dept)

as

select * from student where ssex=’女’

 

除去视图

drop view is_s1 cascade

 

select sno,sage from is_student where sage<20

select sno,sage from student where sdept=’IS’ and sage<20

select is_sutdent.sno,sname from is_student,sc where
is_student.sno=sc.sno and sc.cno=’1′

select * from s_g where gavg>=90

 

分组

select sno,avg(grade) from sc group by sno

select sno,avg(grade) from sc where avg(grade)>=90 group by sno

select sno,avg(grade) from sc group by sno having avg(grade) >=90

 

作者: 初行
Q Q: 121866673
图片 2
QQ群: 313686804(验证:博客园)
来源: http://zxlovenet.cnblogs.com
声称: 本文原创公布于博客园,作者为初行本文接待转发,但未经作者同意必得保留此段评释,且在篇章页面鲜明地点给出原版的书文连接,不然视为侵犯权益。

储存进度

CREATE PROCEDURE Insert_pass

@pass NVARCHAR(50)

AS

  BEGIN

      DECLARE @count INT

 

      SELECT @count = (SELECT Count(*)

                       FROM   list

                       WHERE  pass = @pass)

 

      IF @count = 0

        BEGIN

            INSERT INTO list

                        (pass)

            VALUES     (@pass)

        END

  END

 

实施存款和储蓄进度

call procedure Insert_pass(2011)

 

除去存款和储蓄进度

drop procedure Insert_pass()

 

触发器

CREATE TRIGGER insert_table1

ON table1

FOR INSERT, DELETE

AS

  BEGIN

      UPDATE table2

      SET    count = (SELECT Count(*)

                      FROM   table1)

      WHERE  id = 1;

  END;

 

游标:游标是系统为客商设置的多个多少缓冲区,贮存SQL语句的施行结果,各种游标区都有一个名字。用户能够通过游标逐生龙活虎获取记录,并赋值给主变量,交由主语言进一层管理。

 

PS:以上来自《数据库系统概论》(第四版卡塔 尔(英语:State of Qatar)王珊&萨师煊

 

admin

网站地图xml地图