sql数据库

1.外键：引用另一张表的主键值做当前这一列的值
INNER JOIN要求2表数据都存在，LEFT JOIN左表都会显示，左右是相对JOIN关键字而言
having 和 where ：where 直接用当前这一列的值作比较，从查出来的结果（平均值）再过滤用having ,=后面只能一个值，in多个,聚合的结果只能与聚合查询在一起（max，group一类不能和一般字段一起查,唯一时可以一起查）
错误示范：score 与 kid可以正常查，sid不可
select max(score),kid,sid
from sc
group by kid

–将employees表的所有员工的last_name和first_name拼接起来作为Name，中间以一个空格区分
SELECT last_name||" "||first_name AS Name FROM employees

–查询每门科目最高同学
SELECT SC.SCORE,ST.NAME
FROM SC
INNER JOIN ST ON ST.ID=SC.ID
INNER JOIN (
SELECT MAX(SCORE) SCORE,KID
FROM SC
GROUP BY KID) T ON SC.KID=T.KID AND SC.SCORE=T.SCORE

–查询选修叶平老师学生成绩最高的
select st.name,sc.score
form st
inner join …
where t.name = “叶平”
order by score desc
limit 1
–列出两门及以上不及格学生的姓名及平均成绩
select avg(score),st.name
from sc
inner join st on sc.sid=st.id
where sid in ( #只限制sid，不限制score，子查询
select sid
from sc
where score < 60
group by sid
having count(kid) >= 2
)
group by sid

–创建数据库及表
create database middle
use middle
create table m1(
m11 char(9),
m12 smallint,
m13 varchar(11),
m14 char(9),
m15 default (datetime(‘now’,‘localtime’)) not null,
constraint PK_m1 primary key(m11),
constraint CK_m12 check(m13 in(1,2))
)

–外键
create table n1(
n11 char(9) primary key,
n12 char(9),
constraint FK_n_m foreign key(n12) references m1(m11)
–m11必须为pk(不然无法唯一标识)，n12为外码 并且两者数据类型要一致
)

–修改表
alter table n1 add n13 char(2) null
alter table m1 add m20 smallint
alter table m1 add constraint CK_m20 check(m20 = 3)
alter table m1 with nocheck
alter table m1 drop column m12 --注意column

–插入
insert into m1 values (‘1’,‘2’,‘3’,3)，（…）
insert into n1 values(‘4’,‘1’,‘4’) --注意主键存在性

–多表查询
– 查询和汪远在同一个系学习的学生姓名，宿舍号和电话 (1)
select student.sname,student.dormno,tele
from(select * from student where student.sname = ‘汪远’) t
left join student on student.dno = t.dno left join dorm
on student.dormno = dorm.dormno
where student.sname <> ‘汪远’

–group and aver group by 是按组分类所以一个组只有一个值故需要用agv，max等
select cno,avg(score) 平均成绩
from grade
where cno in (‘01’,‘02’,‘03’)
group by cno
having avg(score) > 60

–any,all
–1 查询其他系比1系某一学生年龄小的学号
select sno
from student
where sage < any(select sage
from student
where dno = ‘01’)
and dno <> ‘01’

–exist
–查询至少选修了‘990104’课程的同学
–查询学生x，不存在：990104选了这门课，x没有选
select distinct sno
from grade g1
where not exists(
select*
from grade g2
where(g2.sno = ‘990104’)
and not exists(
select *
from grade g3
where(g3.cno = g2.cno and g3.sno = g1.sno)
)
)

–模糊匹配
– check(tele like ‘8830_ _ _ _’) 返回<= 4个的

–级联
– constraint **** on delete set null/ on update cascade

–view
–将学生的学号及他的平均成绩定义成一个视图
–视图是子表，来源于基本表（没有创建新表），更改视图会修改原表(不可视部分不可修改)
create view S_G(sno,gavg)
as
select sno,avg(score)
from grade
group by sno

–1查询‘1’系的全体学生的学号，姓名和宿舍情况，按宿舍号升序排列
select sno,sname,dormno
from student
where dno = ‘1’
order by dormno
–1.2
select sno,sname,dormno
from student
where exists(
select *
where dno in(1)
)
order by dormno
–1.3
select sno,sname,dormno
from student s1
where s1.sno in(
select sno
from student s2
where s2.dno = ‘1’
)
–1.4
select s1.sno,s1.sname,s1.dormno
from student s1
where exists(
select *
from student s2
where s2.dno = ‘1’ and s2.sno = s1.sno
)
order by dormno

–2查询1系详细记录，性别升序，年龄降序
select *
from student
where dno = ‘1’
order by sex,sage desc
–2.2
select *
from student s1
where s1.sno in(
select sno
from student s2
where s2.dno = ‘1’
)
order by sex,sage desc

–3查询2系姓名及出生年月（2003为标准），并用 ‘BIRTHDAY’ 命名
select sname,2013 - sage as BIRTHDAY
from student
where dno = ‘2’

–4查询grade表中所有被学生选修的课程号
select d.dno
from department d left join student s on
d.dno = s.dno right join grade g on
s.sno = g.sno
group by d.dno
–4.2
select distinct d.dno
from department d left join student s on
d.dno = s.dno right join grade g on
s.sno = g.sno

–5 查询年龄在 21-23之间的学生姓名宿舍号
select sname,dormno
from student
where sage >= 21 and sage <= 23
–5.2
select sname,dormno
from student
where sage in(21,22,23)

–6 查询2,3,4系的学生姓名
select sname
from student s
where s.dno in(2,3,4)
–6.2
select s1.sname
from student s1
where exists(
select *
from student s2
where s2.dno in(2,3,4) and s1.sno = s2.sno
)

–7 查询所有姓张学生
select *
from student
where sname like ‘张%’

–8查询姓名中第二个字为明的同学
select *
from student
where trim(sname) like ‘_明%’

–9查询course表没有先修课程课程名和教师
select cname, teacher
from course
where cpno is null

–10查询1系所有男生情况
select *
from student
where sex = ‘男’ and dno = ‘1’
–10.2
select *
from student s1
where exists(
select *
from student s2
where s2.sex = ‘男’ and s2.dno = ‘1’ and s2.sno = s1.sno
)
–10.3
select *
from student s1
where exists(
select *
where sex = ‘男’ and dno = ‘1’
)

–11 查询所有1系的学生人数
select dno,count(dno) 总人数
from student
where dno = ‘1’
group by dno

–12查询‘1’系选修‘1’号课程的最高分，最低分，平均分
select max(score),min(score),avg(score)
from grade
where score is not null and cno = ‘01’
group by cno

–13查询选修了4门以上课程学生的学号，姓名
select s.sno,s.sname
from student s
where s.sno
in ( select sno
from grade
group by sno
having count() > 4
)
–13.2
select s.sno,s.sname
from student s
where exists(
select *
from grade g2
group by g2.sno
having count() > 4 and g2.sno = s.sno
)

–14给出所有院系领导检查学生宿舍的可能
select distinct head,dormno
from department d,student s
where d.dno = s.dno and dormno is not null

–15查询所有学生及其宿舍情况
select ltrim(s.sname),s.dormno,d.tele
from student s,dorm d
where s.dormno = d.dormno

–16查询所有课程的间接先修课
select c1.cname,c2.cname 先修
from course c1 left join course c2
on c1.cpno = c2.cno

–17查询所有学生及其选修课情况
select s.sname,g.cno,g.score
from student s right join grade g
on s.sno = g.sno

–18 查询所有已选修学生学号，姓名，选修课程名，教师名
select s.sno,s.sname,c.cname,c.teacher
from student s right join grade g
on s.sno = g.sno left join course c
on g.cno = c.cno

–19查询‘秦峰’的学生的姓名和电话
select sname,tele
from student s,dorm d, department de
where de.head = ‘秦峰’ and s.dormno = d.dormno
and s.dno = de.dno

–20查询其他系比计算机所有学生年龄大的学生的姓名和年龄
select s1.sname, s1.sage
from student s1,department d2
where s1.dno = d2.dno
and s1.sage >= (
select max(sage)
from student s2,department d
where s2.dno = d.dno and
d.dname = ‘计算机系’
) and d2.dname <> ‘计算机系’

–21查询没有课程不及格的学生
–查询学生不存在：有一门课程不及格
select sname
from student s
where s.sno in (
select g2.sno
from grade g2
group by g2.sno
having min(score) >= 60
)

–22 查询至少选修了1号和2号课程的学生号码
select g1.sno
from grade g1
where g1.cno in(‘01’,‘02’)
group by sno
having count(*) = 2

–23查询至少选修了‘990102’学生全部课程的学生学号
select distinct sno
from grade g1
where not exists(
select *
from grade g2
where g2.sno = ‘990102’
and not exists(
select *
from grade g3
where g3.cno = g2.cno
and g3.sno = g1.sno)
)

–24查询‘1’系的学生年龄与年龄大于20岁学生的差集
select sname,sno
from student
where dno = ‘1’ and sage <= 20
–24.2
select sname,sno
from student
where dno = ‘1’ and
exists(
select *
where sage <= 20
)
–25查询选修数据库原理与高等数学学生的交集
select sno
from course c,grade g
where c.cno = g.cno and
c.cname in(‘数据库原理’,‘高等数学’)
group by g.sno
having count(*) = 2

–26创建计算机系男生视图
create view VIEW_1(sname,sage,sno,dormno,BIRTHDAY)
as
select sname,sage,sno,dormno,2013 - sage
from student s,department d
where s.dno = d.dno and sex = ‘男’ and d.dname = ‘计算机系’

–27 在VIEW_1中找出名字中有‘原’字学生的学号，姓名，宿舍号
select sno,sname,dormno
from VIEW_1
where sname like(’%原%’)

–28 修改视图VIEW_1中李军年龄为23，宿舍号2202
update VIEW_1 set sage = 23,dormno = ‘2202’
where sname = ‘李军’
/*update VIEW_1 set sage = 20，dormno = ‘2101’
where sname = ‘李军’ */

一、数据库中创建索引的优点

1、创建唯一性索引，保证数据库表中每一行数据的唯一性。

2、加快数据的检索速度，这也是创建索引的最主要的原因。

3、减少磁盘IO（向字典一样可以直接定位）。

4、通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

5、加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

二、数据库中创建索引的缺点

1、创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

2、索引需要占用物理空间，特别是聚集索引，需要较大的空间。

3、当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。
CREATE UNIQUE INDEX uniq_idx_firstname ON actor(first_name);
CREATE INDEX idx_lastname ON actor(last_name);

题目描述
针对actor表创建视图actor_name_view，只包含first_name以及last_name两列，并对这两列重新命名，first_name为first_name_v，last_name修改为last_name_v：
create view actor_name_view (first_name_v,last_name_v) as
select first_name,last_name from actor

LIMIT m,n : 表示从第m+1条开始，取n条数据；
LIMIT n ： 表示从第0条开始，取n条数据，是limit(0,n)的缩写。
#查找入职员工时间排名倒数第三的员工所有信息
select * from employees
order by hire_date desc
limit 2,1

1 第一范式（1NF）
在任何一个关系数据库中，第一范式（1NF） [2] 是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库。
所谓第一范式（1NF）是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。如果出现重复的属性，就可能需要定义一个新的实体，新的实体由重复的属性构成，新实体与原实体之间为一对多关系。在第一范式（1NF）中表的每一行只包含一个实例的信息。
简而言之，第一范式就是无重复的列。
2 第二范式（2NF）
第二范式（2NF） [2] 是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或行必须可以被唯一地区分。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。这个唯一属性列被称为主关键字或主键、主码。
第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性。如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。
简而言之，第二范式就是非主属性部分依赖于主关键字。
3 第三范式（3NF）
满足第三范式（3NF） [2] 必须先满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。
例如，存在一个部门信息表，其中每个部门有部门编号（dept_id）、部门名称、部门简介等信息。那么在图3-2的员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表，则根据第三范式（3NF）也应该构建它，否则就会有大量的数据冗余。
简而言之，第三范式就是属性不依赖于其它非主属性。