常用SQL语句查询分享
发布网友
发布时间：2022-04-24 05:32
共1个回答
热心网友
时间：2022-04-08 06:19
--创建数据库(文件：主要数据文件mdf==1，次要数据文件ndf>=0，日志文件ldf>=1)
--文件组：当1mdf,5个ndf(1,2,2),10个ldf(3,3,4),将它们分成多个组存放
CREATE
database
studb;
--创建表teacher,student
create
table
teacher
(
tid
int(10)
primary
key
auto_increment,
tname
varchar(20),
tage
int(10)
);
use
studb;
create
table
student
(
sid
int(10)
primary
key
auto_increment,
sname
varchar(20),
sage
int(10),
tid
int(10)
REFERENCES
teacher(tid)
);
--外键约束：你问张三的老师是谁？？
--select
teacher.tname
from
teacher,student
where
student.sname
=
'张三'
select
t.tname
from
teacher
t,student
s
where
s.sname
=
'张三'
and
t.tid
=
s.tid
--创建课程表
create
table
course
(
cid
int(10)
primary
key,
cname
varchar(20),
tid
int(10)
REFERENCES
teacher(tid)
);
--创建分数表
create
table
sc
(
scid
int(10)
primary
key,
sid
int(10)
REFERENCES
student(sid),
cid
int(10)
REFERENCES
course(cid),
score
int(10)
);
--联合查询：等值查询
--1..
select
c.cname
from
course
c,student
s,sc
where
s.sname
=
'小张'
and
s.sid
=
sc.sid
and
c.cid
=
sc.cid;
--2..
select
sname
from
student
s,course
c,sc
where
c.cname='android'
and
sc.score>=60
and
s.sid
=
sc.sid
and
c.cid
=
sc.cid;
--3..
--子查询：当条件也要查询的时候,我只知道学号，我不知道"小张"这个字段,那你知道小张的学号 吗
delete
from
sc
where
sid
=
(select
sid
from
student
where
sname
=
'小张');
--子查询中间的符号一定是父查询与子查询两张表关联的字段(一般是主外键)
--4..
update
sc
set
score=score+5
where
cid=????;
select
tid
from
teacher
where
tname='*'
==1
select
cname
from
course
where
tid
=
1
==课程名字，*教的
select
cid
from
course
where
cname='android'
==课程ID
update
sc
set
score=score+5
where
cid=
(
select
cid
from
course
where
cname=
(
select
cname
from
course
where
tid
=
(
select
tid
from
teacher
where
tname='*'
)
)
);
