发表更新学习7 分钟读完 (大约1030个字)

Mysql调优

分库分表

分库分表很明显，一个主表（也就是很重要的表，例如用户表）无限制的增长势必严重影响性能，分库与分表是一个很不错的解决途径，也就是性能优化途径，现在的案例是我们有一个1000多万条记录的用户表members,查询起来非常之慢，做法是将其散列到100个表中。

修改表结构

不停机修改mysql表结构，同样还是members表，前期设计的表结构不尽合理，随着数据库不断运行，其冗余数据也是增长巨大

索引的建立

索引应建立在那些将用于Join，Where判断和orderBy排序的字段上。尽量不要对数据库中某个含有大量重复的值的字段建立索引。

sql语句执行顺序

select -> wherer -> group by -> having -> order by

explain

参考链接：MySQL explain 应用详解(吐血整理🤩) - SegmentFault 思否

使用优化器可以模拟优化器执行SQL查询语句，从而知道MySQL怎么处理你的SQL语句的，分析你的查询语句和表结构的性能瓶颈。

explain能够干什么？
  • 读取表的顺序
  • 哪些索引能够被使用
  • 数据读取操作的操作类型
  • 哪些索引能够被实际使用
  • 表之间的引用
  • 每张表有多少行被物理查询

explain的使用，如下所示：

1
explain select * from course;

输出如下：

image-20210924144139921

explain各个字段代表的意思：
  • id ：select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
  • select_type ：查询类型 或者是 其他操作类型
  • table ：正在访问哪个表
  • partitions ：匹配的分区
  • type ：访问的类型
  • possible_keys ：显示可能应用在这张表中的索引，一个或多个，但不一定实际使用到
  • key ：实际使用到的索引，如果为NULL，则没有使用索引
  • key_len ：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度
  • ref ：显示索引的哪一列被使用了，如果可能的话，是一个常数，哪些列或常量被用于查找索引列上的值
  • rows ：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需读取的行数
  • filtered ：查询的表行占表的百分比
  • Extra ：包含不适合在其它列中显示但十分重要的额外信息
Id与table
  1. id相同时，执行顺序由上至下
  2. 如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
  3. id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行
select_type字段
  • SIMPLE 简单查询，不包括子查询和union查询
  • PRIMARY 当存在子查询时，最外面的查询被标记为主查询
  • SUBQUERY 子查询
  • UNION 当一个查询在UNION关键字之后就会出现UNION
  • UNION RESULT 连接几个表查询后的结果
partitions字段

该列显示的为分区表命中的分区情况。非分区表该字段为空（null）。

type字段

首先说一下这个字段，要记住以下10个状态，（从左往右，越靠左边的越优秀）

1
NULL > system > const > eq_ref > ref > ref_or_null > index_merge > range > index > ALL
  • NULL：MySQL能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引
  • system：表只有一行记录（等于系统表），这是const类型的特列，平时不大会出现，可以忽略。
发表更新学习4 分钟读完 (大约537个字)

Sql查询语句学习

已知有如下4张表：

学生表：student(学号,学生姓名,出生年月,性别)

成绩表：score(学号,课程号,成绩)

课程表：course(课程号,课程名称,教师号)

教师表：teacher(教师号,教师姓名)

根据以上信息按照下面要求写出对应的SQL语句。

参考文章：图解SQL面试题：经典50题 - 知乎 (zhihu.com)

阅读更多
发表更新学习9 分钟读完 (大约1421个字)

sql学习记录

实验一

参照实验指导书附录，建立数据库SCHOOL，分别建立students、teachers、courses、choices四张表。

  1. 用SQL创建数据库、建表。建表时为各表建立关键字、设置外码

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    create dabase db_school
    on primary
    (
    	name='schoolDB_data',
    	filename='E:\schoolDB_data.mdf',
    	size=5mb,
    	maxsize=100mb,
    	filegrowth=15%
    )

    log on(
    	name='schoolDB_log',
    	filename='E:\schollDB_log.ldf',
    	size=2mb,
    	filegrowth=1mb
    )

    create table students(
    	sid char(10) primary key,
    	sname char(30) not null,
    	email char(30),
    	gread int
    )

    create table teachers(
    	tid char(10) primary key,
    	tname char(30) not null,
    	email char(30),
    	salary int 
    )

    create table courses(
    	cid char(10) primary key,
    	cname char(30) not null,
    	hour int
    )

    create table choices(
    	no int primary key,
    	sid char(10),
    	tid char(10),
    	cid char(10),
    	score int,
    	foreign key(sid) references students(sid),
    	foreign key(tid) references teachers(tid),
    	foreign key(cid) references courses(cid)
    )

  2. 为students表、courses建立按主键增序排列的索引

    1
    2
    create index stuid on students(sid)
    create index couid on courses(cid)

  3. 删除course上的索引

    1
    drop index courses.couid

  4. 在students表中增加一个“出生日期”字段，类型为日期时间型

    1
    2
    alter table students
    add "出生日期" datetime

  5. 删除students表中的出生日期字段

    1
    2
    alter table students
    drop column 出生日期

  6. 删除students表

    1
    drop table students

实验二 sql语句

  1. 查询年级为2001的所有学生的名称,按编号顺序排列

    1
    2
    3
    select sid,sname from students
    where grade = '2001'
    order by sid ASC

  2. 查询所有课程中含有data的课程编号

    1
    2
    select cid from courses
    where cname like 'data%'

  3. 统计所有老师的工资

    1
    select avg(salary) from teachers

  4. 查询至少选了3门课的学生编号

    1
    2
    3
    select sid from choices
    group by sid
    having count(*)>3

  5. 查询学号为80009026的学生的姓名、所选课名及成绩

    1
    2
    3
    4
    5
    select students.name, courses.cname, score
    from students, courses, choices
    where
    students.sid = choices.sid and choices.cid = courses.cid
    and choices.sid = '80009026'

  6. 查询没有学生选的课程编号

    1
    2
    3
    4
    5
    6
    select cid from courses
    where not exists
    (
    select courses.cid from courses, choices
    where choices.cid = courses.cid
    )

  7. 查询既选了C++又选了Java课程的学生编号

    1
    2
    3
    4
    5
    6
    7
    8
    9
    select sid from choices,courses
    where choices.cid=courses.cid 
    and cname='C++'
    and sid in
    (
    select sid from choices,courses
    where choices.cid=courses.cid 
    and cname='Java'
    )

  8. 查询选了C++但没选Java课程的学生编号

    1
    2
    3
    4
    5
    6
    7
    8
    9
    select sid from choices
    where
    cid = (select cid from courses where cname='C++')
    and
    sid not in
    (
    select sid from choices where
    cid = (select cid from courses where cname='Java')
    )

  9. 向students表中插入”LiMing”的个人信息(编号：700045678，名字：LiMing，Email：
    LX@cdemg.com，年级：1992)

    1
    2
    insert into students(sid, sname, email, grade)
    values('700045678','LiMing','LX@cdemg.com','1992')

  10. 将”LiMing”的年级改为2002

    1
    2
    update students set grade='2002'
    where sname='LiMing'

  11. 删除所有选了Java课程的学生选课记录

    1
    2
    3
    4
    5
    6
    delete from choices
    where cid in
    (
    select cid from courses
    where cname='Java'
    )

  12. 求出每门课的课程号、选课人数，结果存入数据库表T1中

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    create table T1(
    cid char(10),
    m int
    )

    insert into T1(cid, m)
    select courses.cid, count(sid)
    from courses, choices
    where courses.cod = choices.cid
    group by courses.cid

  13. 查询所有选课记录的成绩并换算为五分制

    1
    2
    select sid, cid, score/20 as score
    from choices

  14. 查询成绩小于80的选课记录，统计总数、平均分、最大值和最小值

    1
    2
    3
    select count(*) m, avg(score) avg_score, max(score) max_score,
    min(score) min_score from choices
    where score<80

  15. 按成绩顺序排序显示choices表中所有记录

    1
    2
    select * from choices
    order by score

  16. 创建视图V1,显示学生姓名、所选课名称、任课教师名

    1
    2
    3
    4
    5
    6
    7
    create view V1
    as 
    select sname, cname, tname
    from students, courses, teachers, choices
    where students.sid = choices.sid
    and courses.cid = choices.cid
    and teachers.tid = choices.tid

  17. 取消V1视图

    1
    drop view V1

实验三 数据库完整性与安全性

  1. 创建一张新表class，包括Class_id(varchar(4)), Name(varchar(10)),Department(varchar(20))三个属性列， 其中Class_id为主码

    1
    2
    3
    create TABLE CLASS( Class_id varchar(4) primary key,
    		    Class_name varchar(10),
    		    Department varchar(20))

  2. 使用SQL命令创建一张学生互助表，要求：包括学生编号、学生姓名、学生帮助对象的编号，每个学生有且仅有一个帮助对象，帮助的对象必须是已存在的学生

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    create table help
    (
    	sid char(10) primary key,
    	sname char(10),
    	help_sid char(10) 
    );

    alter table help
    add constraint fk_help 
    foreign key (help_sid) 
    references help(sid);

  3. 使用STC数据库，修改设置，完成以下2种不同的要求，请写出对应的语句或简述操作过程：

    1. 当更新、删除STU表中记录时，若SC表中有此学生的记录，则拒绝
      1
      2
      3
      4
      5
      6
      alter table sc
      add constraint fuck 
      foreign key (sno) 
      references stu(sno)
      on update no action
      on delete no action
    2. 当更新、删除STU表中记录时，若SC表中有此学生的记录，则自动更新或删除
      1
      2
      3
      4
      5
      6
      alter table sc
      add constraint fuck2
      foreign key (sno) 
      references stu(sno)
      on update cascade
      on delete cascade

  4. 使用sql命令完成以下任务

    1. 创建Worker表,定义约束U1、U2，其中U1规定Name字段取值唯一，U2规定sage字段上限是28

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      create table worker(
      number char(5),
      name char(8),
      sex char(1),
      sage int,
      deapartment char(20)
      );

      alter table worker 
      add constraint U1 unique(name);
      alter table worker 
      add constraint U2 check(sage <= 28);

    2. 去除U1约束,修改约束U2，令sage的值大于等于0

      1
      2
      3
      4
      5
      6
      7
      alter table worker
      drop constraint U1;
      alter table worker
      drop constraint U2;

      alter table worker 
      add constraint U2 check(sage >= 0)

    3. 创建规则rule_sex,规定更新或插入的值只能是M或F，并绑定到Worker的sex字段

      1
      2
      3
      create rule rule_sex
      as @value in('M','F');
      sp_bindrule rule_sex, 'worker.[sex]';

  5. 创建触发器并测试

    1. 为worker表创建触发器T1，当插入或更新表中数据时，保证所操作记录的sage大于0

      1
      2
      3
      4
      5
      6
      create trigger T1
      on worker
      for update,insert
      as 
      if  (select sage from inserted) <= '0'
      rollback transaction

    2. 为worker表创建触发器T2，禁止删除编号为00001的记录

      1
      2
      3
      4
      5
      6
      create trigger T2
      on worker
      for delete
      as 
      if (select number from deleted) = '00001'
      rollback transaction

    3. 为worker表创建触发器T3，要求更新一个巨鹿时，表中记录的sage要比老记录的sage值大

      1
      2
      3
      4
      5
      6
      create trigger T3
      on worker
      for update
      as 
      if (select sage from inserted) < (select sage from deleted)
      rollback transaction
关注我

链接

分类

最新文章

标签

BFS3
DFS3
Java面经1
algorithm4
android1
bfs2
c++9
dfs7
dp1
dpLearning13
java1
jvm9
ordered-map1
python8
spring6
sql3
tools16
二分查找1
二叉树13
位运算10
傻屌题1
分治2
分治算法2
动态规划6
双指针7
哈希1
哈希表2
回溯5
堆2
堆排序1
多线程1
字符串5
总结1
找规律2
指针1
排序9
数字1
数据库1
数组9
最短路径1
查找2
栈9
树12
测试6
测试知识1
滑动窗口1
自动机1
计算机基础2
计算机网络1
计算机网络基础1
论文阅读10
设计1
贪心算法3
较难13
递归6
链表14
队列1
需要review8
面试记录1
×