SQL笔记

Posted on In

数据库(Database)是按照数据结构来组织、存储和管理数据的仓库。企业发展到一定程度，数据存储会面临一些问题：如何处理大量数据的检索与访问，如何保证数据信息的一致性和完整性，如何分享数据并保证安全性。

数据库概述

数据库经历了三个发展阶段

  1. 人工管理阶段

    时间：20世纪50年代以前 事件：计算机主要用于科学计算。从硬件层面看，当时没有直接用于存储的设备，仅能借助卡片、磁带等外存达到数据存储的目的；从软件层面看，当时也没有完整的操作系统以及专门管理数据的软件；从数据层面看，当时数据量非常小且有没固定的数据结构，所有数据都直接由用户管理。
    特点：数据不能长期保存，没有对数据进行管理的软件系统，数据不能共享，数据不具有独立性。

  2. 文件系统阶段

    时间：50年代后期到60年代中期 事件：计算机不仅用于科学计算，还运用到信息管理方面。从硬件层面看，这时出现了磁盘等数据存储设备；从软件层面看，系统可以按照文件的名称对其进行检索和访问，并可以实现文件内容的增加、修改与删除；从数据层面看，实现了文本内数据的结构化，即单个文本中各数据之间具有一定的关系，但从整体来看每个文件中的数据却是无结构的。
    特点：数据可以长期保存，由文件系统管理数据，数据具有一定的共享性和独立性。

  3. 数据库管理系统阶段

    时间：60年代后期 事件：数据库系统克服了文件系统的缺陷，提供了对数据更高级、更有效的管理，这个阶段程序与数据的联系通过数据库管理系统（DBMS）来实现，因此计算机可以用来存储和处理规模庞大的数据集。
    特点：数据实现结构化，共享性高、独立性强。

数据库基本概念

  1. 数据库文件

    按照一定规则，存放在硬盘上的数据文件集合。所以，MySQL 里的数据库都能在计算机硬盘内某个文件夹里找到对应的数据文件。

  2. 数据库实例

    指计算机内存中处于运行状态的数据库程序，以及为这些程序分配的一些内存空间。 实例是位于内存中，只在数据库处运行状态时才存在。简单点来讲，数据库实例就是把数据库加载到内存里面。

  3. 数据库应用

    建立在数据库上，对其功能进行扩充的程序。 qq 和手机银行app 都属于数据库应用程序，它们后台都需要调用数据库。

数据库分类

  1. 关系型数据库

    Oracle 运行稳定、可移植性高、功能齐全、性能超群，适用于大型企业。

    DB2 速度快、可靠性好、适用于海量数据、恢复性极强，适用于大中型企业。

    MySQL 开源、体积小、速度快，适用于中小型企业。

    SQL Server 全面高效、界面友好易操作，但是不跨平台，适用于中小型企业。

  2. 非关系型数据库

    NoSQL

    MongoDB

表结构

  1. 什么是表结构

    由固定列和任意行构成的二维表结构的数据集。 表中的列称为字段，表中的行称为记录。
    以字段为基本的存储单位和计算单位。
    每一个字段必须有字段名，且同一个表中的字段名不能重复。
    每个字段的数据类型必须一致。

  2. 为什么要使用表结构数据

    除Excel、WPS之外的其他分析工具，都是使用表结构的存储格式

存储引擎

  1. InnoDB：InnoDB：事务型数据库的首选引擎，也是MySQL的默认事务型引擎，支持事务安全表（ACID），支持行锁定和外键。

    使用场景：由于其支持事务处理、外键、支持崩溃修复能力和并发控制。 如果需要对事务的完整性要求比较高（比如银行），要求实现并发控制（比如售票），那选择InnoDB有很大的优势。
    如果需要频繁的更新、删除操作的数据库，也可以选择InnoDB，因为支持事务的提交（commit）和回滚（rollback）。

  2. MyISAM：在Web、数据仓储和其他应用环境下最常使用的存储引擎之一，拥有较高的插入、查询速度，但不支持事物和外键

    使用场景：如果表主要是用于插入新记录和读出记录，那么选择MyISAM能实现处理高效率。

  3. MEMORY：将表中的数据存储到内存中，未查询和引用其他表数据提供快速访问。

    使用场景：如果需要该数据库中一个用于查询的临时表。

  4. ARCHIVE：仅仅支持最基本的插入和查询，它使用zlib压缩库，在记录被请求时会实时压缩，所以它经常被用来当做仓库使用。

    使用场景：由于高压缩和快速插入的特点Archive非常适合作为日志表的存储引擎，但是前提是不经常对该表进行查询操作。

ER图

在创建数据库表之前，首先需要设计数据库。 设计数据库表的主要工具是ER图，它以图形化的形式来表现数据库中有哪些对象，每一个对象有哪些属性，对象和对象之间有什么关系。

  1. 实体：实体就是一个个对象
  2. 属性：属性就是实体所具有的某个特征属性
  3. 关系：关系就是实体和实体之间或者实体内部之间的联系

在ER图中，矩形代表实体，椭圆代表属性，菱形代表关系，各个形状之间用线段连接。

SQL语法

SQL对大小写不敏感。
用#或– 单行注释，用/* */多行注释，注释语句不可执行。

数据定义语言DDL

数据库增删改查

1
2
3
4
5
6
show databases;
create database 'name';
show create database 'name';
alter database 'name' charactor set 'format';
use 'name';
drop database 'name';

数据表增删改查

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
create table 'name' (
    'field1' 'type1' 'restrict1'
    'field2' 'type2' 'restrict2'
);

show create table 'name';
show tables;
desc 'name';

alter table 'oriname' rename 'newname';
alter table 'name' change 'orifield' 'newfield' 'type'['restrict'];
alter table 'name' modify 'field' 'newtype'['restrict'];
alter table 'name' add 'newfield' 'type'['restrict']['pos'];
alter table 'name' modify 'field' 'type'['restrict'] first;
alter table 'name' modify 'field1' 'typr'['restrict'] after 'field2';
alter table 'name' drop 'field';

drop table 'name';

数据类型

类型 说明 类型 说明
int 4字节整数值 text 长文本字符串型
float 单精度浮点型，float(5,2)表示最多5位数，其中有两位小数 date 日期型，‘yyyy-MM-dd’
char 固定长度字符串型，如char(10)，‘abc ’ time 时间型，‘hh:mm:ss’
varchar 可变长度字符串型，如varchar(10)，‘abc’ datetime 日期时间型，‘yyyy-MM-dd hh:mm:ss’

数据完整性

主体完整性：主键约束/唯一约束/唯一约束

1
2
3
4
5
6
7
8
9
10
11
create table 'name'(field1' 'type' primary key,'field2' 'type'[,…]);
create table 'name'('field1' 'type','field2' 'type'[,…], primary key('field'));
create table 'name'('field1' 'type','field2' 'type'[,…], primary key('field1', 'field2'[,…]));
alter table 'name' drop primary key;

create table 'name'('field1 'type,'field2 'type' unique[,…]);
alter table 'name' drop index 'restrict';

create table 'name'('field1' 'type' primary key auto_increment,'field2' 'type'[,…]); 
Alter table 'name' auto_increment=n;
alter table 'name' modify 'field' 'type'[ 'restrict'];

域完整性：非空约束/默认约束

1
2
3
4
5
create table 'name'('field1' 'type','field2' 'type' not null[,…]);
alter table 'name' modify 'field' 'type'[ 'restrict'];

create table 'name'('field1' 'type1','field2' 'type2' default 'value'[ ,…]);
alter table 'name' modify 'field' 'type'[ 'restrict'] ;

参照完整性：外键约束

1
2
creat table 'name'('field1' 'type1','field2' 'type2'[,……,][constraint 外键约束名] foreign key('field') references ''name('field'));
alter table 'name' drop foreign key 'restrict';

用户自定义完整性：检查约束

视图

视图是存储在数据库中的虚拟表，视图中不保存数据，内部封装了一条SELECT语句,数据来源于查询的一个或多个基本表。

1
2
3
4
5
create view 'viewname' as select 'query';
desc 'viewname';
select 'field1'[,'field2',...] from 'viewname'[ where 'criteria'];
create [or replace or alter] view 'viewname' as select 'query';
drop view 'viewname';

索引

索引的意义

用于快速找出在某个字段中有特定值的行。 如果不使用索引，MySQL必须从第一条记录开始检索表中的每一条记录，直到找出相关的行，那么表越大，查询数据所花费的时间就越多。
如果表中查询的字段有索引，MySQL能够快速到达一个位置去检索数据文件，而不必查看所有数据，那么将会节省很大一部分查询时间。

索引的优缺点

优点：

​ 通过索引对数据进行检索，大大提高了数据的查询效率

缺点：

​ 创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加

​ 索引也需要占空间的，创建的索引太多，索引文件也会占用数据库的存储空间

​ 当对表中的数据进行增加、删除、修改时，索引也需要动态的维护，降低了数据的维护速度

索引分类

类型 说明
单值索引 一个索引只包含一个字段
唯一索引 索引字段的取值不能重复，可以有空值，但空值也只能出现一次
主键索引 索引字段的取值不能为空，也不能重复
主键索引 一个索引包含多个字段
全文索引 仅限MyISAM引擎，只能在CHAR,VARCHAR,TEXT类型的字段上使用，通过关键字符，就能找到该字段所属的记录行
空间索引 仅限MyISAM引擎。对空间数据类型的字段建立的索引，且要求索引字段的取值不能为空

索引操作

1
2
3
4
5
6
create index 'index' on 'tablename'('field');
# 在表中定义了主键约束时，会自动创建一个对应的主键索引
# 在表中定义了外键约束时，会自动创建一个对应的普通索引
# 在表中定义了唯一约束时，会自动创建一个对应的唯一索引
show index from 'tablename';
drop index 'index' on 'tablename';

需要创建索引的情况

主键和外键字段自动创建索引

频繁作为查询条件的字段应该创建索引

查询中排序的字段应该创建索引

查询中分组或统计的字段应该创建索引

不需要创建索引的情况

表中记录太少不需要创建索引

需要频繁增删改的字段不适合创建索引

where子句中用不到的字段不需要创建索引

重复值较多的字段不需要创建索引

数据操作语言DML

添加数据

1
2
3
4
5
insert into 'tablename'('field1'[,'value1',…]) values('value1'[,'value1',…]), ('value1'[,'value2',…])[,…]);
insert into 'tablename' values('value1'[,'value1',…]), ('value1'[,'value1',…])[,…]); 

set sqlsafeupdates=0;
load data infile ‘$dir$.csv’into table 'tablename' fields terminated by ‘,' ignore 1 lines;

更新数据

1
update 'tablename' set 'field1'='value1'[, 'value1'='value1',…][ where 'crifield'='crivalue'];

删除数据

1
2
3
4
5
delete from 'tablename'[ where 'crifield'='crivalue']; 
truncate 'tablename'; 
# delete和truncate区别
# delete可以添加删除条件删除表中部分数据，truncate只能删除表中全部数据。
# delete删除表中数据保留表结构，truncate直接把表删除(drop)然后再创建一张新表，执行速度比delete快。

数据查询语言DQL

select语句执行后服务器会按照要求检索表中数据，并将检索结果发送到客户端，这个以表的形式展示出来的临时结果集就是虚拟表，它是存放在内存中，不是在磁盘中的，执行其他操作之后这个结果集就没有了，所以它是临时存在的虚拟表，而不是一个真实的表。

单表查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
select * from 'tablename'; 
select 'field1'[,'field2',…] from 'tablename'; 
select 'field2' as 'alias' from 'oritablename' [as] 'alias';
select distinct 'field2' from 'tablename'; 
select 'field1'[,'field2',…] from 'tablename' where 'criteria'; 
select 'field1'[,'field2',…] from 'tablename' where 'field2' is [not] null; 
select 'field1'[,'field2',…] from 'tablename' where 'field2' like 'wildcard'; 
# 百分号（%）通配符：匹配多个字符，可以匹配除了null以外的任何值
# 下划线（_）通配符：匹配一个字符

select 'field1'[,'field2',…] from 'tablename'[ where 'criteria'] group by 'groupfield1'[,'groupfield2',…]; 
select 'field1'[,'field2',…] from 'tablename'[ where 'criteria'][ group by 'groupfield1'[,'groupfield2',…]] having 'criteria'; 
select 'field1'[,'field2',…] from 'tablename' order by 'field1'[ 'direction','field2' 'direction',…];
select 'field1'[,'field2',…] from 'tablename' limit ['offsetvalue',] 'number' | 'number' offset 'offsetvalue';

多表查询

表之间关系：一对多，多对多，一对一。

消除笛卡尔积

1
select 'field1'[,…] from 'table1','table2'[,…] where 'table1'.key='table2'.key;

连接查询

1
2
3
select 'field1'[,…] from 'table1' [inner] join 'table2' on 'table1'.key='table2'.key;
select 'field1'[,…] from 'table1' left join 'table2' on 'table1'.key='table2'.key; 
select 'field1'[,…] from 'table1' right join 'table2' on 'table1'.key='table2'.key;

联合查询

1
2
3
4
# 去重
select 'field1'[,'field2',…] from 'tablename' union select 'field1'[,'field2',…] from 'tablename'; 
# 不去重
select 'field1'[,'field2',…] from 'tablename' union all select 'field1'[,'field2',…] from 'tablename';

子查询

标量子查询：返回的结果是一个数据(单行单列)

行子查询：返回的结果是一行(单行多列)

列子查询：返回的结果是一列(多行单列)

表子查询：返回的结果是一张临时表(多行多列)

常用操作符：IN, ANY, ALL, EXIST

函数

类型 函数 类型 函数 类型 函数 类型 函数
聚合 avg 数学 sign 日期 date_format 日期 time
聚合 count 数学 sqrt 日期 from_unixtime 开窗 cume_dist
聚合 max 字符串 ascii 日期 monthname 开窗 dense_rank
聚合 min 字符串 bit_length 日期 dayname 开窗 first_value
聚合 sum 字符串 concat 日期 now 开窗 lag
数学 abs 字符串 concat_ws 日期 curdate 开窗 last_value
数学 bin 字符串 insert 日期 curtime 开窗 lead
数学 exp 字符串 lcase 日期 quarter 开窗 nth_value
数学 greatest 字符串 ucase 日期 week 开窗 ntile
数学 least 字符串 left 日期 dayofyear 开窗 percent_rank
数学 ln 字符串 right 日期 dayofmonth 开窗 rank
数学 log 字符串 length 日期 dayofweek 开窗 row_number
数学 mod 字符串 position 日期 year
数学 pi 字符串 quote 日期 month
数学 rand 字符串 repeat 日期 day
数学 floor 字符串 reverse 日期 hour
数学 ceiling 字符串 ltrim 日期 minute
数学 round 字符串 rtrim 日期 second
数学 truncate 字符串 trim 日期 date

查询结果保存

1
2
create table 'newtablename' as select 'query';
select 'query' into outfile 'savedir';

如果报错提示：“The MySQL server is running with the –secure-file-priv option so it cannot execute this statement”，表示数据库不允许存到此路径。
运行：show variables like ‘securefilepriv’; 查看允许路径，再存到此路径下即可。

数据控制语言DCL

定义数据库访问权限和安全级别，常用指令：Grant，Revoke。