数据库演习-Class2 基础操作

2023-02-27 13:58:21 浏览数 (1)

写SQL习惯注意：

语法问题：

1.执行语句后面用分号;

2.设置主键外键的限制语句：用反引号！

3.表最后一个设定没有符号

原则问题：

#原则1：语句不能依赖上下文，这样有就删除再创建，没有就创建。

#原则2：删除时先删除外键所在的表中该元素，再删除主键所在表，

#原则3：创建时先创建主键所在表

2.1 创建数据库

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

使用数据库,后面的操作都在该数据库里面运行：

代码语言：javascript复制
use dlut002 ;

2.2 创建表：

代码语言：javascript复制
Create table 表名

#原则2：删除时先删除外键所在的表中该元素，再删除主键所在表，

下面例子中要删除学生，先删除映射表中的学号，再删除主键（学生）中的学号

首先认识数据之间的关系：

学号-学生–玉兰卡-卡号 玉兰卡的学号是外码，学生的学号是主码

多对多的对应关系，比如学生对老师，老师对学生，在这个关系中间弄个中间表-叫映射表 在这个例子中，映射表三个属性：编号，学号，教工号 映射表里面挑的属性，主键和外键，主键独一无二区分（编号），学号教工号这样的叫外键 上面的关系：ER图， 实体：矩形 虚体：椭圆形

代码语言：javascript复制
#注意写表的语法，这样写才满足原则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 '学生表';

sid是行名字，当行号每多一行，自动增长sid。

数据表字段的设计：

#1. 主键建议使用： 整数
#2. 字段的约束【unique唯一，not null，default（默认值）】
#3. ‘’ 单引号表示字符串 ！ ··是反向单引号，表示主键名字的
代码语言：javascript复制
#玉兰卡和学生一一对应，如何写约束关系？
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 '玉兰卡';

#删除记录：
代码语言：javascript复制
delete from card where cid =2;

delete from card;  #抹空数据

truncate card;     #截断，抹空数据把自动增长的计数器也抹空
代码语言：javascript复制
#构建教师表
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 '学生和教师映射表_多对多';

2.3增删改查

应用程序猿 CRUD程序员

插入

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

当要插入很大量数据，使用循环

数据库里面的函数叫 存储过程

代码语言：javascript复制
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);
#记不住存储过程去查一下

删除：

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

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

修改：

修改逻辑是最复杂的。

注意： 一般修改不修改主键

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

增加，修改和删除操作都是写入操作，意味着每条SQL语句都应该提交后生效，通常客户端中执行SQL默认每条语句后自动commit。

但是，自动提交不一定都是好的。

例子：

代码语言：javascript复制
delete from card where csid =4;  #将sid为4的用户的玉兰卡注销

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

为了删除这个学生，玉兰卡和学生信息必须都删除，不能只删除一个（执行第一句不执行第二句），这样导致信息不一致。

这个时候我们不让他自动提交，手工让它提交

代码语言：javascript复制
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

查询：只读操作，无需commit

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

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

数据查询过程中，先from，再投影【选择列】，再筛选where

代码语言：javascript复制
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'
编程算法 数据库 sql

0 人点赞