数据库与SQL基础

来自小能手俱乐部
跳到导航 跳到搜索

什么是sql？

SQL 是用于访问和处理数据库的标准的计算机语言。

我们日常工作当中，使用的是类似mySQL、oracle这类的数据库管理系统，这些数据库管理系统都遵循SQL语言。我们以来SQL语言来与数据库进行交流与沟通，对于从事变成与互联网行业的人来说，SQL语言是一个非常重要的语言。自从SQL加入了TIOBE编程语言排行榜，就一直保持在TOP10。

虽然 SQL 是一门 ANSI（American National Standards Institute 美国国家标准化组织）标准的计算机语言，但是仍然存在着多种不同版本的 SQL 语言。然而，为了与 ANSI 标准相兼容，它们必须以相似的方式共同地来支持一些主要的命令（比如 SELECT、UPDATE、DELETE、INSERT、WHERE 等等）。完全基于标准 SQL 的 RDBMS 很少，通常需要根据不同的 RDBMS 来编写特定的 SQL 语句，原则上，本课程介绍的是标准 SQL 的书写方式。


sql语言按照功能划分主要分为以下四个部分

DDL(data definition language)：数据定义语言，我们用来定义我们的数据库对象，包括数据库、数据表、列等，通过ddl我们可以创建、删除修改数据库和表结构。

DML(data manipulation language)：数据库操作语言，我们用它来操作和数据库相关的记录，比如增加、删除、修改数据表中的记录。

DCL(data control language)：数据控制语言，我们用它来定义访问权限和安全级别

DQL(data query language )：数据查询语言，我们用它来查询想要的记录，他是SQL语言中的重中之重，在实际使用当中也是我们用到的最多的。

DDL（Data Definition Language，数据定义语言）

DDL（Data Definition Language，数据定义语言） 用来创建或者删除存储数据用的数据库以及数据库中的表等对象。DDL 包含以下几种指令。

  • CREATE ： 创建数据库和表等对象
  • DROP ： 删除数据库和表等对象
  • ALTER ： 修改数据库和表等对象的结构

DML（Data Manipulation Language，数据操纵语言）

DML（Data Manipulation Language，数据操纵语言） 用来查询或者变更表中的记录。DML 包含以下几种指令。

  • SELECT ：查询表中的数据
  • INSERT ：向表中插入新数据
  • UPDATE ：更新表中的数据
  • DELETE ：删除表中的数据

DCL（Data Control Language，数据控制语言）

DCL（Data Control Language，数据控制语言） 用来确认或者取消对数据库中的数据进行的变更。除此之外，还可以对 RDBMS 的用户是否有权限操作数据库中的对象（数据库表等）进行设定。DCL 包含以下几种指令。

  • COMMIT ： 确认对数据库中的数据进行的变更
  • ROLLBACK ： 取消对数据库中的数据进行的变更
  • GRANT ： 赋予用户操作权限
  • REVOKE ： 取消用户的操作权限

什么是dbms?

db dbs dbms：

db：database也就是数据库，数据库是存储数据得集合。

dbms：DataBase Management System 数据库管理系统，实际上他可以对多个数据库进行管理，相当于DBMS =多个DB+管理程序。

dbs database system 数据库系统，他是比数据库更大的概念，包括了数据库、数据库管理系统、以及数据库管理员（dba）

常见dbms介绍

数据库排名：

https://db-engines.com/en/ranking

数据库排名图片

生成缩略图出错：无法找到文件



从上图可以看除 使用最多的是oracle、mysql、SQL server

关系型数据库（RDBMS）是建立在关系模型基础上的数据库，sql就是关系型数据库的查询语言。

sql与nosql

相比sql，nosql泛指非关系型数据库，如上图中的key-value、Document等

NoSql分类很多，刚才提到的key-value、document、wide column、Search engine

sql如何执行?

oracle中如何执行

oracle sql执行图

生成缩略图出错：无法找到文件


mysql中如何执行

mysql sql执行图

Mysql sql flow.png


什么是数据库表?

数据库表用来存储具体的数据内容，包含字段名称、字段类型、字段长度，以及主键、索引、外键等。

需要创建首先需要在数据库中创建一个数据库的示例，下面的语句是在mysql中创建数据库的DDL语句，在oracle中被称作新建一个实例，也可以理解成是创建了一个schema，不同数据库有不同的叫法。一个数据库管理系统上可以为不同的应用创建多个数据库的实例（或者schema），在数据库实例中再去创建相应的表。

CREATE DATABASE xns; // 创建一个名为xns的数据库
DROP DATABASE xns; // 删除一个名为xns的数据库

创建数据库表的DDL语句，不同的数据库数据字段类型可能叫法不同，也有一些字段属性不同，比如mysql中建表可以直接建立自增序列，而oracle中则需要创建序列实现相应的效果。创建数据库表的DDL语句如下：

CREATE TABLE [table_name](字段名 数据类型，......)

什么是分区?

数据库中的数据是以文件形式存储在磁盘上的，表分区是指根据一定规则，将数据库中的一张表分解多个部分，但是在使用上还是被看作一张表，但是在底层是实现上则被视为多个物理分区。一个表的不同分区可以存储在不同的磁盘上面，可以帮助分摊磁盘的读写。

一般分区方式根据某字段进行hash分区、range（区间值）、list(列表)几种方式进行分区。

hash：根据分区字段，以及要分多少个分区，对该字段的数值进行进行hash计算，将改行记录分配到hash值相同的分区内。

range：根据某字段cola的数值范围进行分区，比如a分区0~10 ，b分区11~50， c分区51~100，d分区100以上，比如字段cola的数值为25，它会被分配到b分区存储，数值如果是78，则会被分配到c分区存储。

list：根据某字段的数值内容进行分区，例如，一个表中根据城市代码进行分区，a城市分到01分区，b城市分到02分区...

oracle:

创建分区的ddl语句

CREATE TABLE 表名 (字段1,字段2....)
PARTITION BY RANGE (字段1) (
     PARTITION p0 VALUES LESS THAN (6),
     PARTITION p1 VALUES LESS THAN (11),
     PARTITION p2 VALUES LESS THAN (16),
     PARTITION p3 VALUES LESS THAN MAXVALUE
);

子分区，子分区是在分区的内再进行分区，比如，按月进行分区，在月份区内还可以按日建立子分区。

创建子分区的ddl语句

CREATE TABLE 表名 (字段1,字段2....)
    PARTITION BY RANGE( 字段1 )
    SUBPARTITION BY HASH( 字段2 ) (
        PARTITION p0 VALUES LESS THAN (1990) (
            SUBPARTITION s0,
            SUBPARTITION s1
        ),
        PARTITION p1 VALUES LESS THAN (2000) (
            SUBPARTITION s2,
            SUBPARTITION s3
        ),
        PARTITION p2 VALUES LESS THAN MAXVALUE (
            SUBPARTITION s4,
            SUBPARTITION s5
        )
    );

合理的分区有利于数据得管理，比如直接根据分区名就可以提取该分区的数据，清除该分区的数据等操作。

mysql:

mysql的表分区与oracle大体内容一样，特别有区别的一点是list分区，oracle的list分区可以根据字符串内容设置分区，而mysql的分区只能支持整型数字的数值分区。

1、RANGE表分区：范围表分区，按照一定的范围值来确定每个分区包含的数据，如上使用的就是range表分区；

partition by range(id)          partition p0 values less than()

分区函数使用的字段必须是整数类型（bit, int ,tinyint,bigint等），分区的定义范围必须是连续的，且不能重叠，使用values less than()来定义分区范围，从小到大定义范围。

给分区字段赋值的时候分区字段取值范围不能超过values less than()的取值范围。使用values less than maxvalue来将未来不确定的值放到这个表分区中。

按时间类型(datetime)来做表分区可以在RANGE()中使用函数来做转换，例如：partition by range(year(create_time)),timestamp可以使用unix_timestamp('2019-11-20 00:00:00')转化。

2、LIST表分区：列表表分区，按照一个一个确定的值来确定每个分区包含的数据

partition by list(id)         partition p0 values in(1,2,3)

分区字段必须是整数类型或者分区函数返回整数，取值范围通过values in()来定义。不能使用maxvalue。

create table user4(sex int(1)) partition by list(sex) (partition p0 values in(1),partition p1 values in(2));

3、HASH表分区：哈希表分区，按照一个自定义的函数返回值来确定每个分区包含的数据

partition by hash(id)        partitions 4

根据hash算法来分配到分区中，以上设置四个分区，并根据id%4进行取模运算，根据余数插入到指定的分区中。

create table user7(id int)  partition by hash(id) partitions 3;

4、KEY表分区：key表分区，与哈希表分区类似，只是用MySql自己的HASH函数来确定每个分区包含的数据；

partition by key()        partitions 4

key()括号里面可以包含0个或多个字段（不必是整数类型，可以是普通字段），如果表中有主键或者唯一键，所引用的字段必须是主键或者主键的一部分。如果没有写字段，默认使用主键，如果表中没有主键，则使用唯一键，但唯一键必须设置为not null。

5、多字段分区(range、list)：可以指定多个字段作为分区字段；以下以多字段range作为讲解：

例如：partition by range columns(id,name)

多字段分区可以使用非整数类型来作为分区字段。使用这个特性可以用来创建单字段的非整数类型的表分区。

对比方式从左到右，第一个字段值小于第一个字段分区值则放在第一个分区，等于或大于第一个字段分区值则对比第二个字段值与第一个字段分区值的大小，以此类推。

create table user6(sex varchar(10),name varchar(10),age varchar(10)) partition by range columns(sex,name,age) (partition p0 values less than('d','f','h'),partition p1 values less than('l','n','x'));

先比较sex字段，再比较name字段，最后比较age字段。使用select ('a','b')<('b','c')来验证


更多内容，mysql 与 oracle 的分区不同点

什么是索引？

索引是为了提高数据库的查询效率，就像书的目录一样。索引的出现是为了提高查询效率。

创建索引的字段可以是单个字段，也可以是几个字段联合起来创建索引。

常见的索引实现数据结构是，哈希表、有序数组和搜索树。例如mysql的InnoDB采用了B+书索引模型。

-- ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。
ALTER TABLE table_name ADD INDEX index_name (column_list)
ALTER TABLE table_name ADD UNIQUE (column_list)
ALTER TABLE table_name ADD PRIMARY KEY (column_list)
-- CREATE INDEX可对表增加普通索引或UNIQUE索引。
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)


什么是视图？

视图是从一个或几个基本表（或视图）中导出的虚拟的表。在视图的边界界面中仅存放了视图的定义，一般为一段sql，不存放视图对应的数据。

视图是原始数据库数据的一种变换，是查看表中数据的另外一种方式。可以将视图看成是一个移动的窗口，通过它可以看到感兴趣的数据。 视图是从一个或多个实际表中获得的，这些表的数据存放在数据库中。那些用于产生视图的表叫做该视图的基表。一个视图也可以从另一个视图中产生。

视图的定义存在数据库中，与此定义相关的数据并没有再存一份于数据库中。通过视图看到的数据存放在基表中。

视图看上去非常像数据库的物理表，对它的操作同任何其它的表一样。当通过视图修改数据时，实际上是在改变基表中的数据；相反地，基表数据的改变也会自动反映在由基表产生的视图中。由于逻辑上的原因，有些视图可以修改对应的基表，而有些则不能（仅仅能查询）。

什么是函数？

函数是数据库管理系统提供的一些算数、字符串处理、日期、转换等功能的程序单元，函数可以使用在sql语句中的select或者where语句中。

也可以根据自己的需要自定义一些函数使用。

常用函数

  • COUNT() - 返回行数
  • MAX() - 返回最大值
  • MIN() - 返回最小值
  • SUM() - 返回总和
  • AVG() - 返回平均值
  • FIRST() - 返回第一个记录的值
  • LAST() - 返回最后一个记录的值
  • UCASE() - 将某个字段转换为大写
  • LCASE() - 将某个字段转换为小写
  • MID() - 从某个文本字段提取字符，MySql 中使用
  • SubString(字段，1，end) - 从某个文本字段提取字符
  • LEN() - 返回某个文本字段的长度
  • ROUND() - 对某个数值字段进行指定小数位数的四舍五入
  • NOW() - 返回当前的系统日期和时间
  • FORMAT() - 格式化某个字段的显示方式

更多数据库函数参考数据库SQL函数。

什么是存储过程？

存储过程是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。

比如定期根据某些表生成一些数据，清理数据等操作。

存储过程的创建DDL

CREATE PROCEDURE  过程名([[IN|OUT|INOUT] 参数名 数据类型[,[IN|OUT|INOUT] 参数名 数据类型…]]) [特性 ...] 过程体

DELIMITER //
  CREATE PROCEDURE myproc(OUT s int)
    BEGIN
      SELECT COUNT(*) INTO s FROM students;
    END
    //
DELIMITER ;

存储过程使用

更多数据库函数参考数据库存储过程

其他学习资源

阿里的sql训练营：https://tianchi.aliyun.com/course/309/3584

菜鸟教程的sql：https://www.runoob.com/sql/sql-tutorial.html

w3c school 的sql教程：https://www.w3school.com.cn/sql/index.asp

取自“http://123.56.229.83:8088/mediawiki/index.php?title=数据库与SQL基础&oldid=556”