李新乾的个人博客
0%

数据库（三）-SQL

发表于

三、SQL

全部是重点、十多分

=NULL错误

JDBC的操作步骤

触发器的

  1. 定义事件 before / after insert\update\delete
  2. 关键字old\new after update后两个都可以用
    after delete用old
    before insert用new
  3. authorization

3.1、SQL概述

1、历史

image-20220329133219172

image-20220329133328971

DBMS:MySQL,Oracle,DB2,SQL Sever

2、SQL的特点

image-20220329133912991

image-20220329134132726

image-20220329134904865

image-20220329145620253

3.2、学生-课程数据库

image-20220329145801272

image-20220329145832639

image-20220329145841031

image-20220329145848884

3.3、数据定义

1、创建表

image-20220329140206316

2、基本表的定义、删除与修改

image-20220329140315971

<>必须有 []可有可无

image-20220329140732529

image-20220329140900853

image-20220329150033271

image-20220329141101141

image-20220329145113627

4、修改基本表

image-20220405133459139

image-20220405133633735

image-20220405133745468

image-20220405135304468

3、索引的建立与删除

B+Tree索引、Hash索引、聚集索引

hash索引只能用在等于的情况下

如果是范围索引，用不到hash

聚集索引：每个表都只有一个聚集索引，创建表的时候自动创建。作用：决定数据在硬盘上的物理存储位置

一列可以添加多个索引

image-20220405135922612

image-20220405140529692

image-20220405140753608

image-20220405140847492

查询

image-20220405142121947

image-20220405142229647

image-20220405142353663

image-20220405142401855

更新

image-20220405143544414

image-20220405144327707

防止出错 set autocommit = 0;

image-20220407151637029

image-20220407151906643

查询

image-20220407151953162

image-20220407152001143

image-20220407152040779

image-20220407152227071

image-20220407152407915

image-20220407152514204

image-20220407152932148

image-20220407153006719

image-20220407153057255

image-20220407153339228

image-20220407153604629

image-20220407153655052

image-20220407153827523

查找包含%的字符

image-20220412135357794

image-20220412140026084

image-20220412140455405

image-20220412140615313

3.4、数据更新

image-20220412140719541

image-20220412141218564

limit 分页查询，指明开始和查几个

image-20220412142419378

image-20220412142544689

image-20220412142917596

image-20220412143816221

having 是分组后的查询

where是分组前

连接查询

image-20220414152201337

左连接

image-20220414152722945

image-20220414153214620

3.5、数据查询

嵌套查询，子查询

image-20220419134617718

嵌套查询可以放到select,from,where中

放到select中，只可以返回一列

image-20220419135355613

放到from中

image-20220419135727134

n+1查询，

image-20220419141114824

在where

image-20220419142631689

image-20220419143404437

image-20220419143833277

-------------本文结束感谢您的阅读-------------