数据库练习

# 牛客

like 表示模糊查找

浮点数的平均值可能小数点位数很多，按照示例保存一位小数，用 round 函数

多个条件进行分组： group by university,gender;

where 在 group by 前执行，所以需要用 having，而 order by 在 group by 后，所以可以不用 having。

每张表都需要有别名

多个条件判断： case when then

日期函数 day () 取日，month () 取月份，datediff 求两个日期之间的差

# 常见例题

年龄在 20 到 23 之间： SELECT device_id,gender,age from user_profile where age between 20 and 23

用 where 过滤空值练习： select device_id,gender,age,university from user_profile where age is not null;

查看学校名称中含北京的用户： select device_id,age,university from user_profile where university like '%北京%';

统计每个学校的答过题的用户的平均答题数：首先构造出一张以 device_id 为分组的表，然后以该表为主表，进行链接，然后根据学校进行分组，使用 sum 和 count（1）即可，或者 (count(question_id)/count(distinct(qpd.device_id))) 直接使用 count
SELECT DISTINCT Company FROM Orders： distinct 关键字表示只取一次，去重
结果不去重只需 union all 将两张表连接在一起。

选取文本中的最右的元素： substring_index(profile,',',-1)

查找倒数第二个元素需要两层： SUBSTRING_INDEX(SUBSTRING_INDEX(profile,',',-2),',',1)AS age;

# day1

# 字符串

verchar 和 char 的区别：verchar 存储时变长，最多容纳 65535 个字节；char 固定长度，最多容纳 255 个字符。

在配置文件（my.cnf）中加入如下配置： sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" 保存并重启即可进入非严格模式

长的文本：text

创建表： create table L3(id int not null primary key auto_increment,name varchar(5),depart char(3))default charset=utf8;

插入元素： insert into L3(name,depart) values("alex","sb");

# 增删改查

新增数据： insert into L2(salary) values(5.289);

删除数据： delete from L3 where name="alex" and id=1; 。删除 name=alex 的数据，and 链接多个条件

更新数据： update L2 set salary=1.99 where id=1; 。 update L3 set name=concat(name,"3") where id=2； 。PS：concat 函数用于拼接字符串

查找数据： select id,name from L3 where id=2;

显示一列标题为 age 的 111： select id,name,111 as age from L3;

# 小数

decimal (8,2) 表示数字位有 8 位（不包含负号），小数位保留 2 位。

小数位数过多会自动四舍五入，整数部分超过会报错

插入元素： insert into L2(salary) values(5.289);

新建表： create table L2(id int not null primary key auto_increment,salary decimal(8,2))default charset=utf8;

# 时间

timestamp 和 datetime 相比：除了表示的范围更小外，所存储的方式也不同，是根据当前时区再进行转换，而 datetime 是直接存储。

此外还有 date 和 time

创建表： create table L5(id int not null primary key auto_increment,dt datetime,tt timestamp)default charset=utf8;

插入表： insert into L5(dt,tt) values("2025-11-11 11:11:14","2025-11-11 11:11:14");

# 今日总结

修改表： alter table L6 add amount decimal(10,2) default 0 not null;

删除表： alter table L6 drop aaa;

插入记录： insert into L6(name,password,gender,email,crime,amount) values("xiqin4","dsasa",0,"7444545@qq.com","2020-7-1 11:11:14",100);

更新记录： update L6 set gender=1 where id>3;

查看： select * from L6 where amount>1000;

更新： update L6 set amount=amount+1000;

删除： delete from L6 where gender=1;

创建表：

create table L6(
    id int not null primary key auto_increment,
    name varchar(32) not null,
    password varchar(64) not null,
    gender char(2) check(gender in (0,1)),
    email varchar(64),
    crime datetime
)default charset=utf8;

# day2

# 左右连表

以 info 作为主表，将 depart 进行左链接，根据 depart.id 进行： select * from info left outer join depart on info.depart_id = depart.id;

指定选中的数据进行展示： select info.id,info.name,info.email,depart.title from info left outer join depart on info.depart_id = depart.id;

将 left 改成 right 就是右链接： select info.id,info.name,info.email,depart.title from info right outer join depart on info.depart_id = depart.id;

从表 right outer join 主表 on 主表.x = 从表.id

当主表中存在从表中没有的信息，则会产生区别；

简写 outer 可以省略

内链接：两张表进行链接，没有分主从，根据条件进行匹配：select * from info inner join depart on info.depart_id = depart.id;

# 指定列（映射）

帮 name 写别名 NM，并新增一列 123： select id,name as NM,123 from info ;

条件语句： select id,name,case when age<18 then "少年" when age<30 then "青年" else "老年" end v6 from info;

# 用户授权管理

用户

• 查看用户信息： select user,authentication_string,host from mysql.user;
• 查看用户和权限信息： desc mysql.user;
• 新建用户： create user '用户名'@'连接者的IP地址' identified by '密码' 。PS：'' 号可加可不加
• create user xiqin@127.0.0.1 identified by '159123zxc' 。解析：127.0.0.%：% 意味可以匹配任何值；地址为 % 意味所有地址都可以
• 删除用户： drop user xiqin@127.0.0.1;
• 修改用户： rename user 'xiqin'@'127.0.0.1' to 'huangyuqin'@'localhost';
• 修改密码： set password for 'huangyuqin'@'localhost'=Password('123123');

授权

• grant 权限 on 数据库。表 to ‘用户’@‘IP 地址’;
• 给用户 huangyuqin 用有数据库 day26db 中 info 表的查找和插入权限： grant select,insert on day26db.info to 'huangyuqin'@'localhost';
• 给用户 huangyuqin 拥有数据库的所有权限： grant all privileges on *.* to 'huangyuqin'@'localhost';
• 此时还没有真正生效，输入： flush privileges; 。将数据读入内存，从而立即生效。
• 选择不同的用户进行登录： "C:\Program Files\mysql-5.7.31-winx64\bin\mysql.exe" -h 127.0.0.1 -P 3306 -u huangyuqin -p
• 查看授权： show grants for 'huangyuqin'@'localhost';
• 取消授权： revoke INSERT on day26db.info from 'huangyuqin'@'localhost';
• 需要在管理员权限下使用

# 通配符

通配符主要用于模糊查找：

% 号表示 n 个字符，可以为 0 个： select * from info where name like "%明%"; _号表示一个字符： select * from info where name like "_明%";

注意：数据少时可以使用，数据量大时不要使用，因为效率很低。

# 上下连表和取部分

select id,title from depart union select id,name from info;

列数需相同，会自动去重。union all 则不去重

获取前五条数据：select * from info limit 5;

从位置 2 开始，向后获取前 5 条数据： select * from info limit 3 offset 2;

可以用于分页显示

# 排序和分组

desc 倒序排列，顺序排列，通常是得到结果再进行排序

优先按照 age 从小到大，age 相等则 id 从大到小： select * from info order by age asc,id desc;

将 age 相同的进行分组，遇到需要取舍的时候，选择 ID 更大的，同理还可以替换成 max (id),min (id),count (id),sum (id),avg (id)：select age,count (1) from info group by age;

对已经分组的数组，取其中 2 个以上的分组： select depart_id,count(id) from info group by depart_id having count(id)>2;

# 表关系

约束 depart_id 和 depart 中的 id，用于初始化的时候： constraint fk_info_depart foreign key (depart_id) reference depart(id);

表结构已创建，额外增加外键： alter table info add constraint fk_info_depart foreign key info(depart_id) references depart(id);

删除外键： alter table info drop foreign key fk_info_depart;

# SQL 语句优先级

优先级从高到低：join->on->where->group by->having->order by->limit

例子： select age,count(id) from info where id>2 group by age having count(id)>1 order by age desc limit 1;

# day3

# 表的数据的导入和导出

先判断是否存在，再进行删除： drop database if exist day27db;

导入文件：注意斜杠： source /Users/86136/Desktop/数据库/第三天/day27db.sql;

只能导入数据表，不能插入数据，不知道是什么问题，可能是主键导致的问题，暂时先不处理

查询姓李的老师个数： select * from teacher where tname like '李%';

查询男生和女生的人数： select gender,count(1) from student group by gender;

查询 “三年二班” 的所有学生： select * from student left join class on student.class_id = class.cid where class.caption='三年二班';

查询每个班级的班级名称、班级人数： select class.caption,count(1) from student left join class on student.class_id=class.cid group by class.caption;

查询平均成绩大于 60 的所有学生的学号、平均成绩、姓名： select student_id,avg(num) from score group by student_id having avg(num)>60;

查询成绩小于 60 分的同学学号、姓名、成绩、课程名称：

select student.sid,student.sname,score.num,course.cname from score 
left join student on score.student_id=student.sid 
left join course on score.course_id=course.cid 
where num<60;

查询各科成绩的总分、最高分、最低分、平均分，显示课程 ID、课程名称、总分、最高分、最低分：

select course_id,course.cname,sum(num),max(num),min(num),avg(num) from score 
left join course on score.course_id=course.cid 
group by course_id 
order by avg(num) desc;

查询各科成绩的平均分和及格率，显示：课程 ID、课程名称、平均分、及格率：

select course_id,course.cname,avg(num),count(1) as total,
sum(case when score.num > 60 then 1 else 0 end) as jige from score 
left join course on score.course_id =course.cid 
group by course_id;

select course_id,course.cname,avg(num),
sum(case when score.num > 60 then 1 else 0 end)/count(1)*100 as prescent from score 
left join course on score.course_id =course.cid 
group by course_id;

三个表依次连在一起：查询 “三年二班” 每个学生的学号、姓名、总成绩、平均成绩：

select student_id,sname,sum(num),avg(num) from score 
left join student on score.student_id=student.sid 
left join class on class.cid=student.class_id 
where class.caption='三年二班' 
group by student_id;

查询学过 “波多” 老师课的同学的学号，姓名：

select student.sid,student.sname from score 
left join student on score.student_id=student.sid 
left join course on score.course_id=course.cid 
left join teacher on course.teacher_id=teacher.tid 
where teacher.tname='波多';

查询没学过’波多’老师课的同学的学号，姓名（应该是选择先查询选过的同学的 ID，再进行排除，而不是直接使用！=）：

select * from student 
where sid not in(select student.sid from score 
left join student on score.student_id=student.sid 
left join course on score.course_id=course.cid 
left join teacher on course.teacher_id=teacher.tid 
where teacher.tname='波多');

查询选修‘苍空’老师所授课程的学生中，成绩最高的学生姓名及其成绩（考虑并列）不考虑并列只需使用到 order by 和 limit：

select student.sid,student.sname from score 
left join student on score.student_id =student.sid 
left join course on score.course_id=course.cid 
left join teacher on course.teacher_id=teacher.tid 
where teacher.tname='苍空' 
and score.num=(select max(num) from score 
left join course on score.course_id=course.cid 
left join teacher on course.teacher_id=teacher.tid 
where teacher.tname='苍空');

查询选修了所有课程的学生的学号，姓名：

select student.sid,student.sname from score 
left join student on score.student_id=student.sid 
group by student_id having count(1)=(select count(1) from course);

查询选修了 “生物” 和 “物理” 课程的所有学生学号，姓名：

select student.sid,student.sname from score 
left join course on score.course_id=course.cid 
left join student on score.student_id=student.sid 
where course.cname in ('生物','物理') 
group by student_id having count(1)=2;

查询至少有一门课与学号为 1 的学生所选的课程相同的其他学生学号和姓名：

select student.sid,student.sname from score 
left join course on score.course_id=course.cid 
left join student on score.student_id=student.sid 
where score.course_id in (select course_id from score where student_id=1) and score.student_id!=1 
group by student_id having count(1)>1;

查询 “生物” 比 “物理” 成绩高的所有学生的学号：PS：这里好像有点问题，存在没有该课成绩的情况，应该先选出同时有生物和物理成绩的同学

select student_id,max(case cname when "生物" then num else -1 end) as sw,
max(case cname when "物理" then num else -1 end) as wl from score 
left join course on score.course_id=course.cid 
where cname in ("生物","物理") 
group by student_id having sw>wl;

将一张表中的数据插入到另一张表中：

insert into sc select * from score;
insert into sc (student_id,course_id,num) 
select sid,2,80 from student 
where sid not in (select student_id from score where course_id=2);

# day4

# 执行计划

MySQL 中提供了执行计划，让你能够预判 SQL 的执行（只能给到一定的参考，不一定完全能预判准确）。

explain 关键字

主要看输出结果中的 type,all 表示进行了全表扫描，而 ref 则是通过索引进行查找

性能从低到高排序：all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const

# 无法命中索引的情况

类型不一致：例如数据库中是字符串类型，却传入数字进行查找；主键比较特殊，即使类型不一致也不影响

使用了不等于，特殊的主键 or，当 or 条件要未建立的列时失效，但当后面有 and 有建立索引的列时比较生效

排序，当根据索引排序时候，选择的映射如果不是索引，则不走索引。特殊的主键 like 进行模糊匹配，只有在通配符在最后面才有效

联合索引要满足最左前缀

使用函数，例如 reverse（）, 放在后面才可以，例如

• 未命中： select * from big where reverse(name) = "wupeiqi";
• 命中： select * from big where name = reverse("wupeiqi");

# 索引

使用索引是为了加速查找，为什么可以加速查找是因为索引是按照 B + 树的方式进行存储的

引擎的不同会对索引的产生区别：myisam 是数据和索引结构分开存储；innodb 是数据和主键索引结构存储在一起

建表时可以进行指定 engine=myisam

查看数据表引擎类型： show create table class;

innodb 是默认索引，使用更为方便

常见索引

1. 主键索引：加速查找、不能为空不能重复。
2. 唯一索引：加速查找，不能重复，可以为空，但只能一个为空，不然会视为重复。
3. 普通索引：加速查找。都可以进行联合索引
4. 联合索引的意思是多列连接起来作为一个键

主键索引

primary key关键字
create table 表名(
    id int not null auto_increment,
    name varchar(32) not null,
    primary key(列1,列2)          -- 如果有多列，称为联合主键（不常用且myisam引擎支持）
);
新增主键索引：alter table 表名 add primary key(列名);
删除主键索引：alter table 表名 drop primary key;
自增列一定要是键，即索引，索引删除时可能会报错，此时改为使用alter table 表 change id id int not null;

唯一索引：

unique关键字
create table 表名(
    id int not null auto_increment,
    name varchar(32) not null,
    unique (列1,列2)               -- 如果有多列，称为联合唯一索引。
);
新建create unique index 索引名 on 表名(列名);
删除drop unique index 索引名 on 表名;

普通索引：

index
create table 表名(
    id int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    index ix_email (name,email)     -- 如果有多列，称为联合索引。
);
新增create index 索引名 on 表名(列名);
删除drop index 索引名 on 表名;

# 视图

视图其实是一个虚拟表（非真实存在），其本质是【根据 SQL 语句获取动态的数据集，并为其命名】，用户使用时只需使用【名称】即可获取结果集，并可以将其当作表来使用，类似使用一个 temp 变量方便使用。

创建视图： create view v1 as select id,name from d1 where id>1; 。然后可以当作一张表进行使用

修改视图： alter view v1 as SQL语句

基于视图只能查询，针对视图不能执行 增加、修改、删除。 如果源表发生变化，视图表也会发生变化。

# 函数

字符串拼接： select concat('alex','sb');

休眠 1 秒： select sleep(1);

自定义函数：先将‘;’转换为美元符号，放在遇到；就自动结束了

执行函数： select f1(11,22); 。 select f1(11,id),name from d1;

删除函数： drop function f1;

delimiter $$
create function f1(
    i1 int,
    i2 int)
returns int
BEGIN
    declare num int;
    declare maxId int;
    select max(id) from big into maxId;
    set num = i1 + i2 + maxId;
    return(num);
END $$
delimiter ;

# 存储过程

事务，成功都成功，失败都失败。

存储过程，是一个存储在 MySQL 中的 SQL 语句集合，当主动去调用存储过程时，其中内部的 SQL 语句会按照逻辑执行。

rollback 进行回滚

创建存储过程

delimiter $$
create procedure p1()
BEGIN
    select * from d1;
END $$
delimiter ;
执行存储过程：call p1();

# 触发器

对某个表进行【增 / 删 / 改】操作的前后如果希望触发某个特定的行为时，可以使用触发器。

NEW 表示新数据，可以在里面直接进行使用。同理，old 表示原来的数据。

创建触发器

create trigger
例句：
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
    ...
END

# day5

# 锁

MySQL 中自带了锁的功能，可以帮助我们实现开发过程中遇到的同时处理数据的情况。

MYISAM 支持表锁，不支持行锁；InnoDB 引擎支持行锁和表锁。

在 innodb 引擎中，update、insert、delete 的行为内部都会先申请锁（排它锁），申请到之后才执行相关操作，最后再释放锁。

select 默认不加锁，需要配合事务 + 特殊语法进行加锁

对于数据库中的锁，从锁的范围来讲有：

• 表级锁，即 A 操作表时，其他人对整个表都不能操作，等待 A 操作完之后，才能继续。
• 行级锁，即 A 操作表时，其他人对指定的行数据不能操作，其他行可以操作，等待 A 操作完之后，才能继续。
• for update 排他锁：

  begin; 
      select * from L1 where name="武沛齐" for update;    -- name列不是索引（表锁）
  commit;
  select * from L1 where id=1 for update;              -- id列是索引（行锁）

• lock in share mode 共享锁，其他可读但不可写

  begin; 
      select * from L1 where name="武沛齐" lock in share mode;    -- 假设name列不是索引（表锁）
  commit;
  使数据按字典格式cursor = conn.cursor(pymysql.cursors.DictCursor)

# 事务

四大特性：原子性、一致性、隔离性、持久性。

开启事务：begin; 或者 start transaction;

提交事务：commit;

回滚：rollback;