数据库期末复习知识点

第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员

 有三种类型 => 基本关系, 查询结果, 视图

create table 表名
(
    列名1  数据类型  列属性,
    列名2  数据类型  列属性
);
 

drop table 表名;// 改表名
alter table 旧表名
rename to 新表名// 增加字段
alter table 表名
add 字段名 数据类型// 删除字段
alter table 表名
drop 字段名// 修改字段
alter table 表名
change 原字段名 新字段名 新数据类型// 修改数据类型
alter table 表名
modify 字段名 新数据类型// 查看当前数据库中的表
show tables;// 查看某个表对应的建表语句
show create table 表名;// 查看表结构
describe 表名;
desc 表名;// 只复制结构
create table 新表名
like 旧表名;// 同时复制结构和数据
create table 新表名
as (select * from 旧表名)

// 增
insert into 表名(列名1, 列名2)
values
(值1_1, 值1_2),
(值2_1, 值2_2);
// 增 - 更改主键
replace into// 删 - delete
delete from 表名
where 条件;
// 删 - 全部 delete
delete from 表明;
// 删 - 全部 truncate
truncate from 表名;// 改
update 表名
set 列名1 = 值1,列名2 = 值2;delete from s
where sname in ('小明', '小王');

select 列名
from 表名
where 条件
group by 列名
having 条件
order by 列名
limit n;// like 
where 列名 like '%str%'
where 列名 like '\%str%'
where 列名 like '_str_' 
where 列名 like '\_str_'// in,  not in
select name, price
from fruit
where season = '夏' and price in (select price from fruit where season = '秋'
);// all
select name, price
from fruit
where season = '夏' and price > all (select price from fruit where season = '秋'
);// any, some
select name, price
from fruit
where season = '夏' and price = any (select price from fruit where season = '秋'
);

select 列名
from 表A
inner join 表B
on 表A.列名 = 表B.列名

select from staff.id, staff.name, market.month, moarket.sales
from staff
innner join market
on staff.id = market.id;

// 并 - 去重
select 列名 from 表A
union
select 列名 from 表B;
// 并 - 不去重
select 列名 from 表A
union all
select 列名 from 表B;// 交
select distinct 列名
from 表A
where 列名 IN (select 列名 from 表B);// 差集
select 列名 from 表A where 列名 not in (select 列名 from 表B);

SELECT * FROM Student WHERE Sdept ='CS' UNION
SELECT * FROM Student WHERE Sage <=19SELECT Sno FROM SC  WHERE Cno = '1'
AND Sno IN (
SELECT Sno FROM SC WHERE Cno = '2')SELECT Sno FROM SC SCX WHERE Cno = '1'
AND EXISTS (SELECT Sno FROM SC SCY WHERE Cno = '2'AND SCX.Sno = SCY.Sno)

select 列名
from 表名1, 表名2;

select 列名
from 表名1 as 别名1, 表名1 as 别名2;select s1.name, s1.age, count(*) as 排名
from staff as s1, staff as s2
where s1.age < s2.age
group by s1.id
order by 排名;

// 增
create view 视图名
as 查询语句;// 删
drop view if exists 视图名1, 视图名2;// 改 -  alterview
alter view 视图名
as 查询语句;// 改 -  create or replace view -- 不管原先存不存在, 都能改掉
create or replace view 视图名
as 查询语句// 查
describe 视图名;
show table status like '视图名'; // like 接字符串, 所以需要单引号
show create view 视图名;// 增
insert into 视图名
values();// 删
delete from 表名
where 列名 = '';// 改
update 视图名
set 列名 = 新值;// 查
select * from 表名;

create view CS_s
as select * from s
where smajor = '计算机'
with cheak option;grant select ON CS_s to 王平;
grant all previleges on CS_s to 张明

两个男人A和B；一个女人L男人A和B一起能满足女人L的需求而男人A或B任意一个人都不能满足女人L的需求，要两个人一起才能满足叫做女人L完全依赖于两个男人A和B男人A和B共同一起成为主属性，女人L当成非主属性

两个男人A和B；一个女人L男人A和B一起能满足女人L的需求，但是男人A或B其中一个人就能满足女人L的需求，叫做女人L部分依赖于两个男人A和B

// 增 - 无参数
create procedure 存储过程名()
beginend;
// 增 - 带参数 默认都是in
create procedure 存储过程名(a float, b float)
beginselect name, pricefrom fruitwhere price between a and b;
end;
// 增 - out参数
create procedure test(out n int)
beginselect n;set n = 20;select n;
end;set @b = 10;
call test(@b);
// 以上输出结果   null  20// 增 - inout参数
create procedure test(inout n int)
beginselect n;set n = 20;select n;
end;set @b = 10;
call test(@b);
// 以上输出结果 10  20// 删
drop procedure 存储过程名;// 改 - 简单了解, 只能修改存储过程的特征
alter procedure 存储过程名()
beginend;// 查
show procedure status like '存储过程名';
show create procedure 存储过程名;

DROP PROCEDURE IF EXISTS ps;CREATE PROCEDURE ps(IN sid CHAR(12), OUT avgs DECIMAL)
BEGIN
SELECT AVG(score)
INTO avgs
FROM sc
WHERE sc.snum = sid
END;--
CALL ps('202205010229', @s);
SELECT @s;

// 增
CREATE trigger triggerName
after/before insert/update/delete on 表明
for each row #这句话在Mysql固定
beginsql语句;
end;// 删
drop trigger 触发器名;// 查
show triggers;

ssex CHAR(1) CHECK(ssex = '男' or ssex = '女')，
 

SELECT *
FROM s
LIMIT 2500, 10;
 

SELECT sc.snum, s.sname, cnum, score
FROM sc, s
WHERE sc.snum = s.snum;SELECT s2.sname, s2.ssex
FROM s s1, s s2 
WHERE s1.sname = '沈羿' AND s1.dnum = s2.dnum;SELECT dname, COUNT(*)
from d, s
WHERE d.dnum = s.dnum
GROUP BY s.dnum
ORDER BY COUNT(*) DESCSELECT MAX(tsalary), MIN(tsalary), AVG(tsalary)
FROM t, coll
WHERE tsex = '男' AND collname = '信息工程学院' AND coll.collnum = t.collnum
GROUP BY ttitle
HAVING AVG(tsalary) > 7000
ORDER BY AVG(tsalary) DESC;SELECT sname, snum
FROM s
WHERE snum IN(SELECT snum FROM sc WHERE cnum = 'B3503021');SELECT snum, sname
FROM s
WHERE EXISTS(SELECT *
from sc
WHERE sc.snum = s.snum AND cnum = 'B3503021');SELECT snum, sname
FROM s
WHERE NOT EXISTS(select *
FROM sc
WHERE sc.snum = s.snum AND cnum = 'B3503021');DECLARE num int default 0;
SET num = 10;SELECT AVG(score) INTO @ave
FROM sc;
SET @ave = 10;

DROP PROCEDURE IF EXISTS blesoo;
CREATE PROCEDURE blesoo()
BEGIN
DECLARE ccount INT DEFAULT(0);
DECLARE ave INT DEFAULT(0);
SELECT COUNT(*)
INTO ccount
from sc;
SELECT AVG(score)
INTO ave
FROM sc;
IF(ave > ccount) THEN
SELECT ave;
ELSE
SELECT ccount;
END IF;
END;CALL blesoo();