跳至主要內容

SQL语句

LincZero大约 9 分钟

SQL语句

目录

DDL数据定义语言

表的增删改查

  • create
  • drop
  • alter
  • show/desc

CREATE（创建）

create ...，创建表语句，比较长且繁琐

创建表

create table 表名(
	字段名1 数据类型 [补充],
    字段名2 数据类型 [补充],
    字段名3 数据类型 [补充],
    ...
);

补充：表名在数据库当中一般建议以：t_或tbl_开始

字段数据类型

数据类型

数据类型说明编程语言对应应用
int整数型int
bigint长整型long
float浮点型float / double
char定长字符串String
varchar可变长字符串Java中的 StringBuffer/StringBuilder
date日期类型Java中的 java.sql.Date
BLOB二进制大对象Java中的 Object存储图片、视频等流媒体信息（Binary Large Object）
CLOB字符大对象Java中的 Object存储较大文本，比如可以存储4G的字符串（Character Large Object）

选用

  • char还是varchar
    • 存生日定长/日期，存昵称 / 签名可以用动态长度（如1~20）
    • VARCHAR(50)，不会浪费空间
    • CHAR(50)，不满50字符串会填充空格至50字符串，浪费空间
  • BLOB还是CLOB
    • 以一个电影表为例
    • 注意：BLOB不能使用INSERT语句插入，需要使用IO流

字段其他属性（约束）

约束类型（Constraint）

约束类型缩写使用约束说明
非空约束NNnot null约束的字段不能为NULL
唯一约束unique约束的字段不能重复，但可多个NULL
主键PKprimary key约束的字段不能为NULL且不能重复
外键约束FKforeign key...
检查约束checkOracle数据库有Check约束，但MySQL不支持
自动增加AIAUTO_Increment可以自动维护主键值
Default/Expression 默认值默认值

非空约束

略

  • 列级约束 / 表级约束：只有列级约束、没有表级约束

唯一约束

  • 联合唯一性：单个可重复，配对结果不重复。主键同理
  • 列级约束 / 表级约束：有列级约束和表级约束，数量任意
drop table if exists t_user;
create table t_user(
	id int unique,				-- 【列级约束】单个唯一性
    usercode varchar(255),
    username varchar(255),
    unique(usercode,username)	-- 【表级约束】联合唯一性
)

主键

drop table if exists t_user;
create table t_user(
	id int primary key,			-- 【列级约束】单个唯一性（只能有一个）
    usercode varchar(255),
    username varchar(255)
    -- primary key(id,username)	-- 【表级约束】联合唯一性（不建议）
)
  • 范式规则：每个表中都应该有主键。在一些数据库图形化软件中，若没有主键甚至不允许编辑
  • 主键作用：主键是这行记录在这张表中的唯一标识
  • 注意要点：一张表的主键约束只能有一个
  • 主键分类
    • 根据字段数量
      • 单一主键（建议使用）
      • 复合主键 / 联合主键（不建议，复合主键违背三大范式）
    • 根据主键性质
      • 自然主键：一个与业务没有任何关系的自然数，例如自然增长的主键id（建议）
      • 业务主键：例如银行卡卡号、身份证号码（不推荐使用） （不建议，因为以后业务一旦发生改变，主键值可能也需要发生变化，但变化可能会导致主键重复） （视频里老师说不建议，但我挺有异议的）
  • 列级约束 / 表级约束：有列级约束和表级约束，但一张表只能有一个主键约束

主键值自增【MySQL特有】

create table t_user(
	id int primary key auto_increment
);

Oracle中也提供了一个自增机制，叫做：序列（Sequence）对象

外键约束

create table t_class(		-- 父表，班级表
	cno int,
    cname varchar(255),
    primary key(cno)
);
create table t_student(		-- 子表，学生表
    sno int,
    sname varchar(255),
    classno int,
    foreign key(classno) references t_calss(cno)	-- 外键约束、引用
);
  • 作用：表A的字段必须在表B的字段里
  • 应用举例：学生表的班级号必须在班级表里面有（学生表是子表，班级表是父表）
  • 父子表顺序：先创建父表再创建子表、添加数据先添加父表再添加子表、先删除子表再删除父表
  • 注意：外键字段可以为NULL，被引用的字段不一定是主键但一定是有唯一性的

存储引擎、与默认字符集

create table t_table (
	id int primary key auto_increment
) engine=innodb default charset=utf8;
  • 简概
    • show create table t_table;显示出来的create建表语句中：最后可能会带有ENGINE = InnoDB DEFAULT CHARSET=utf8
    • 即若不指定存储引擎，默认使用InnoDB存储引擎和utd8字符集
  • 数据库差异
    • 在MySQL中该机制叫存储引擎，但在Oracle中为表的存储方式
  • 用途
    • 每一个存储引擎都有优缺点，可选择合适的存储引擎
  • 种类
    • 查看当前mysql支持的存储引擎：show engines \G
    • mysql 5.5.36版本支持的有9个
  • 选用
    • 一般用innoDB就行了

MyISAM 存储引擎

  • 简概

    • 是MySQL最常用的引擎（不是默认引擎）

  • 表的特征

    • 使用三个文件表示每个表

      • 文件文件名文件作用
        格式文件mytable.frm存储表结构的定义
        数据文件mytable.MYD存储表行的内容
        索引文件mytable.MYI存储表上索引，索引像是目录，能通过缩小范围来加速查询速度

    • 灵活的AUTO_INCREMENT字段处理

  • 优点

    • 可被压缩，节省存储空间
    • 可转换为只读表，提高检索效率

  • 缺点

    • 不支持事务

InnoDB 存储引擎

  • 简概
    • 是MySQL的缺省（默认）引擎
  • 表的特征
    • 每个InnoDB表在数据库目录中以.frm格式文件表示
    • InnoDB表空间tablespace被用于存储表的内容（逻辑概念，意味着无法被压缩、无法转换成只读）
    • 提供一组用来记录事务性活动的日志文件
    • 用COMMIT（提交）、SAVEPOINT及ROLLBACK（回滚）支持事务处理
    • 提供全ACID兼容
    • 在MySQL服务器崩溃后提供自动恢复
    • 多版本（MVCC）和行级锁定
    • 支持外键及引用的完整性，包括级联删除和更新
  • 优点
    • 支持事务、行级锁、外键等。即数据安全性比较好

MEMORY 存储引擎

  • 表的特征
    • 数据库目录内，每个表均以.frm格式的文件表示
    • 表数据及所有被存储在内存中
    • 表级锁机制
    • 不能包含TEXT或BLOB字段
  • 优点
    • 查询速度最快
  • 缺点
    • 不支持事务、数据容易丢失。因为所有数据和索引都是存储在内存当中的
  • 补充
    • 以前叫做HEPA引擎

DROP（删除）

写法

  • drop table 表名，该写法通用
  • drop table if exists demotable;，当表存在则删除，Oracle不支持这种写法

怎么删除大表？（几亿）

  • 删除时间非常长，因为没有释放数据所占空间
  • truncate table demotable;，表的截断
    • 与delete from demotable比较：特点是速度快，但表被截断、不可回滚（即无法恢复，需慎重，要再三问清楚）
    • 与drop table demotable;比较，truncate直接截断表中的数据而非删除表

ALTER（修改）

略，使用工具完成即可

因为开发中表设计后很少会修改表结构，，除非设计得有问题

一般修改表结构语句不会出现在程序代码中，但一般可以使用可视化工具去修改。不是一定要掌握的

SHOW（查看 - 表数据）

  • show tables form demotable;，查看指定表
    • show tables;，查看当前表
  • show create table demotable，查看表创建语句

DESC（查看 - 表结构）

  • desc demotable;，查看表结构

其他表命令

INDEX（索引）

  • 作用
    • 相当于一本书的目录，可以快速找到对应的资源
    • 查询一张表有两种检索方式：全面扫描和根据索引检索（效率很高）
  • 原理
    • 作用原理：根本原理是缩小了扫描的范围
    • 实现原理：B+树
    • 深剖原理流程
      • 添加索引时
        • 给某个字段添加索引后，会先对该字段进行自动排序并保存（硬盘/内存）为一个对象（叫索引对象）
        • 然后对排序过后的字段进行层层分区
      • 检索时
        • 先看条件的字段有没有索引，若有则找到对应的索引对象
        • 层层定位，如 “SMITH”，先定位S区，继续定位M区......然后缩小扫描的范围，直接定位到物理地址
        • （每一行记录都有一个对应的物理地址，该物理地址在Oracle中叫 rowid）最后想当与where 物理地址 = 0x???;
  • 缺点
    • 不能随意添加索引，因为所以也是数据库当中的对象，也需要数据库不断的维护，有维护成本
    • 比如表中的数据经常被修改就不适合添加索引
    • 因为数据一旦修改，索引需要重新排序、进行维护
    • 注意：索引可能会失效：当使用模糊查询且第一个通配符为%时
  • 使用场景
    • 数据量庞大
    • 很少的DML操作
    • 该字段经常出现在WHERE子句中（当该字段没有索引是进行全表扫描）
  • 索引分类
    • 单一索引，给单个字段添加索引
    • 复合索引，给多个字段联合起来添加1个索引
    • 主键索引：主键上会自动添加索引（启示：根据主键查询效率较高，尽量根据主键检索）
    • 唯一索引，有unique约束的字段上会自动添加索引（主键和具有unique约束的字段会自动添加索引）
    • ......

添加索引

添加索引是给某一字段，或某些字段添加索引

  • 添加索引
    • 格式：create index 索引名称 on 表名(字段名);
    • 举例：create index emp_sal_index on emp(sal);
  • 删除索引
    • 格式：drop index 索引名称 on 表名;

其他

查看sql语句的执行计划，可以用来验证查询的范围

explain select

如explain select ename,sal from emp where sal = 5000;

VIEW（视图）

  • 简概
    • 站在不同的角度去看数据
  • 使用
    • 创建视图：create view myview as select empno,ename from emp;
    • 删除视图：drop view myview;
  • 面向视图操作
    • 对视图进行增删改查，会影响到原表数据
  • 应用
    • 可减少列数、使用别名
    • 可隐藏表的实现细节，保密级别较高的系统，数据库只对外提供相关的视图。程序员只对视图对象进行CRUD

示例

create table emp1 as select * from emp;				-- 创建新表（根据旧表）
select * from emp1;									-- 查看表

create view myview as select id,ename from emp1;	-- 创建视图（仅显示两个字段）
select * from myview;								-- 查看视图
update myview set ename='hehe' where id = 1;		-- 更新视图，会影响到原表数据