数据库演习-Class2 基础操作

drop database if exists dlut002;
create database if not exists dlut002;
#原则1：语句不能依赖上下文，这样有就删除再创建，没有就创建。

use dlut002 ;

Create table 表名

#注意写表的语法，这样写才满足原则1。
drop table if exists mapper;
drop table if exists card;
drop table if exists teacher;
drop table if exists student;
#满足原则1，2
#先创建student是为了满足原则3
create table if not exists student(
    sid integer auto_increment comment '学号',
    sname varchar(30) not null comment '姓名',
    phone varchar(11) unique comment '电话',
    uptime timestamp default current_timestamp on update current_timestamp comment '更新时间',
    #current——timestamp，这个是当前时间戳，生成时插入系统时间,修改时再插入修改时的当时时间
    primary key `s_pk` (`sid`) #主键设置放最后比较好，··是反向单引号，表示主键名字的！
    #设定主键后，编译器默认主键不能为空，可以为null。注意：主键还不能重复。
)comment '学生表';

#玉兰卡和学生一一对应，如何写约束关系？
create table if not exists card(
    cid integer auto_increment comment '卡号',
    cmoney double default 0.0 comment '余额',
    csid integer not null comment '学号',
    #如果csid写成sid，会导致查询sid时查询到两个表中的sid，产生歧义。这个是区分。
    uptime timestamp default current_timestamp on update current_timestamp,
    primary key `c_pk`(`cid`),
    #约束条件在这里：
    constraint foreign key `c_to_s_fk` (`csid`) references `student`(`sid`)
)comment '玉兰卡';

#删除记录：

delete from card where cid =2;

delete from card;  #抹空数据

truncate card;     #截断，抹空数据把自动增长的计数器也抹空

#构建教师表
create table if not exists  teacher(
    tid integer auto_increment comment '教工号',
    tname varchar(30) not null comment '姓名',
    salary double default 0.0 comment '薪水',
    uptime timestamp default current_timestamp on update current_timestamp comment '更新时间',
    primary key `t_pk` (`tid`)
)comment '教师表';

#构建映射表
create table if not exists mapper(
    mid integer auto_increment comment'映射号',
    msid integer not null comment'学号',
    mtid integer not null comment'教工号',
    uptime timestamp default current_timestamp on update current_timestamp comment'更新时间',
    primary key `m_pk`(`mid`),
    constraint foreign key `m_to_s_fk` (`msid`) references `student`(`sid`),
    constraint foreign key `m_to_t_fk` (`mtid`) references `teacher`(`tid`)
)comment '学生和教师映射表_多对多';

show tables;  #列出当前数据库中所有的表
show create table mapper;  #查创建组间时的SQL语句
#插入多行数据：
insert into student(sname, phone) value ('zs','110'),('ls','120'),('ww','130');

delimiter $$
create procedure sp_bs(IN nums int)#声明储存过程这样写，注意形参的写法
BEGIN
    declare count int default 1;
    while count <= nums do
        insert into student(sname,phone)
        values (concat('stu',count),concat('1',FLOOR(rand()*8999999999+1000000000)));
        #concat类似python的字符串f
        set count = count +1;
    end while;
end$$
delimiter ;
#调用存储过程
call sp_bs(9);
#记不住存储过程去查一下

delete from student  #这样是把整个表里面数据都删除了
delete #只删除数据，不清空表结构。
删除某个特定行加 where
truncate student
truncate #截断表，清空数据，清空组件的属性信息。

set foreign_key_checks = false #暂停外键约束
truncate student #这样再清除就不会因为可能的外键约束清除失败。
set foreign_key_checks = true #清除完了再启动，要不然之前写的外键都没用了。
#这种编程叫做临界区编程，核心就是进来的时候设置条件，等操作完了再回复。

update student set sname = '张三',phone = '110119120';
   这样改不可以，相当于把表里面所有的 sname 和 phone 值全改了。
   注意，phone之前设置为unique，所以这样修改会报错，因为会导致phone重复。
   update 要跟上 where 条件：
   update student set name='zhanngsan',phone = '123456' where sid =1;

delete from card where csid =4;  #将sid为4的用户的玉兰卡注销

delete from student where sid =4; #先删除玉兰卡再删除学生。

begin
delete from card where csid =4;  #将sid为4的用户的玉兰卡注销
rollback;
delete from student where sid =4; #先删除玉兰卡再删除学生。
commit
##这样写，两句里面哪一句出错,执行commit才能提交。

#还可以写成:
set autocommit = false;
delete from card where csid =4;  #将sid为4的用户的玉兰卡注销
delete from student where sid =4;#先删除玉兰卡后再删除学生。
commit;
set autocommit = true;

关于 begin 和 end
    procedure的那个begin，end相当于函数的花括号
    数据库的begin（刚刚上面用的那一个），相当于启动事物，
    比如说删除玉兰卡，再删除学生，最上面写一个begin，相当于认为它这个是一整个事物在进行
课后了解：ACID

select 1+1;   #生成一个Resultset 结果集【封装在内存】，其中 '1+1'作为列的别名，结果2成为结果集。
              #Resultset是数据集，自迭代（像是指针原地址+位移）

select @set;  #查询变量
select * from student; #查询所有属性

select sid,sname,phone,uptime from student as stu; 使用as起别名stu
select sid,stu.sname,stu.phone,stu.uptime from student as stu where stu.sid>1; 此时筛选是基于stu的sid，写成stu.sid比较好，但是因为sid只有一个，没写stu.也可以
    如果是多表联查，比如说好几个表里面都有uptime，怎么样去区分呢。使用别名

    多表连接查询：
    连接类型：外连接（包含左右连接，写left和right都一样），内连接，自然连接
    连接表数据量基数越少，查询效率越高。
    select * from student,card; --形成了笛卡尔积，没有意义。

    select * from student,card;
    left join card
    on student.sid = card.csid;  关键数据能对得上

    select * from card as c
    left join student as s
    on c.csid = s.sid
    where s.uptime > '2022-11-28' 联合查询uptime出现歧义，要指定，起别名更方便写，
    而写的程序读的是结果集，你起的别名替代了原来的属性名字出现在结果集，后面的程序都是基于这个结果集查询的。

    select * from card
    left join student
    on card.csid = student.sid
    where student.uptime > '2022-11-28'