MySQL复习

SQL语法复习

grant select(title,Author),update(AvlNum) on book to username;

-- 除法运算！！！
-- R÷S ，S3行，则需要R内a1,a2,a3至少有3行记录

-- 行转列，列转行
-- 虚拟表
select a.name,a.fenshu,b.fenshu from
(select name,fenshu from students where kecheng='语文') a left join
(select name,fenshu from students where kecheng='数学') b on a.name=b.name;

SELECT userid,
SUM(CASE `subject` WHEN '语文' THEN score ELSE 0 END) as '语文',
SUM(CASE `subject` WHEN '数学' THEN score ELSE 0 END) as '数学',
SUM(CASE `subject` WHEN '英语' THEN score ELSE 0 END) as '英语',
SUM(CASE `subject` WHEN '政治' THEN score ELSE 0 END) as '政治' 
FROM tb_score 
GROUP BY userid

select * from t_a where exists
alter table borrow add foreign key(ReaderID) references reader(ReaderID);

-- 各种左右内外连接：zhuanlan.zhihu.com/p/59656673
create index idx_score on sc(score desc);
-- 多join
select s.sno,s.snm,floor(datediff(curdate(),s.birthday)/365),s.natnm,c.cnm,sc.score from s join sc on sc.sno=s.sno join c on sc.cno=c.cno;
select sum(score) from sc where _sno=sno group by sno
update s set tscore=(select sum(score) from sc where _sno=sno group by sno) where sno=_sno;

select right(natnm,locate('-',natnm)-1),round(avg(score),2) av from s,sc where s.sno=sc.sno group by s.natnm order by av desc;
-- group by 太多会重复出现一些数值


use t_a;
drop table if exists t_a;

truncate t_a; --重建
INSERT INTO t_a (SNO,SNM,SEX) VALUES
(),
(),
();
update t_a set c='char',i=1 where ii=2;
-- 查询所有学生的籍贯所在的省份，显示学号、姓名、省份（用LOCATE函数找到'-'分隔符的位置，再用left函数截取左边一部分）
select a.sno,a.snm,REPLACE(left(natnm,locate('-',natnm)-1),'省','') as sf from stu as a where locate('-',natnm)!=0 union
-- 双重否定变肯定：不存在没选修部分课程的人
-- 查询没选'网络空间安全导论'课程的学生的姓名和所在班级名
select snm,clsno from stu where sno not in (select sno from study where cno=(select cno from c where cnm='网安导论'));
delete from t_a where i=1;

select distinct i from t_a where not exists t_b; --distinct去重；此处t_a和t_b表结构需一致
select a,b,c,sum(i) from t_a where i not in (select i from t_b) group by a,b,c having sum(i)>0 order by a asc; --非聚集函数，全加在group by后面
select t_a.c from t_a left outer join t_b on t_a.i=t_b.i where 

cross join t_b on ...
inner join t_b on t_a.i=t_b.i
full (outer) join t_b on ...

create table t_a(
    iiii int, ii smallint, i tinyint, iiiiiiii bigint auto_increment,
    bb decimal(3,1), b boolean not null default true primary key,
    c varchar(256), ccc char(3) unique,
    d date, t time, dt datetime unique,
    foreign key dt references t_b(`columnname`)
);
create table copytableNoForeignKey like t_a;
create table subQueryNoP_Fkey_unique select * from t_a;

alter table t_a add test int not null;
alter table t_a modify iiii tinyint unique;
alter table t_a drop iiii;

create (unique) index idxname on t_a(bb,c);
show index from t_a;

create view viewName(col1,col2,col3) as
select * from t_a limit 3;
replace view viewName as select * from t_a;

delimiter $$
create function funcName(
    _input int
    _err varchar(256)
)returns int
begin
    select
        while (_err!=null) do
            case _err
                when 'a' then ...;
                when 'b' then ...;
                else return -1;
            end;
        end while;
        repeat
            case
                when (@var<0) then ...;
                else exit;
            end;
        until (@var<0)
        end repeat;
    from t_a;
    return (select i from t_a);
end$$

create procedure procName(
    in _input int,
    out _output int,
    inout _err varchar(256),
)
begin
    lable:loop
        if () then
            signal sqlstate '45000' set message_text='err!';
        elseif () then
            declare @var int default 0,
            set @var=(select * from t_a);
            select @var=0;
            select * into newTable from t_a;
            iterate lable; --continue
        else
            leave lable;
        end if;
    end loop;
end$$

--要修改NEW值用before，其余场景一律用after
create trigger trigName before insert 
on t_a
for each row --不加则只在创表后只执行一次，直至销毁表
begin
    -- i=null写法错误，不能用等于号
    if (new.i is null and old.i is not null) then
        new.i=old.i;
    elseif not exists (select * from t_a) then
        ...
    end if;
end$$

create trigger trigName2 after update
on t_a
for each row
begin
    --若令new.i=某个值，会报错，因为after不能修改new表中的值
end$$
delimiter ;

call funcName();

if (i is null,i=1,i=2);
ifnull (@var,null); --if @var not null then @var; else null;
convert(i as char(3));

abs(-1);
rand(); --0~1
round(5.4); --=5
pi();
sqrt(4); --=2
mod(5,2); --=2

curdate();
curtime();
now(); --date+time
year('2023-04-01');
adddate('2023-04-01',29);
str_to_date('04-01-2023','%m-%d-%y');
datediff('04-01','04-03'); --=2

start transaction;
...
savepoint lable;
...
rollback to lable;
...
commit;

between 0 and 100; -- 闭区间

第1章

DataBase Management Sys
DataBase Admin
Data Definition language
Data Manipulation language
Data Control Language

主动安全：抗性
被动安全：恢复

数据独立性：数据的组织和存储方法与应用程序互不依赖、彼此独立的特性。
数据完整性：保证数据正确性
数据一致性：多用户同时使用，解决矛盾&冲突

外部视图，内部硬盘，逻辑模型(层次/网状/关系)，概念蓝图
数据模型三要素：

数据结构

关系模型三大类

数据库系统的三级结构

数据库系统的三级模式

数据库系统的二级映射