区块链技术博客
www.b2bchain.cn

数据库篇·第4章[1]·数据库基础求职学习资料

本文介绍了数据库篇·第4章[1]·数据库基础求职学习资料,有助于帮助完成毕业设计以及求职,是一篇很好的资料。

对技术面试,学习经验等有一些体会,在此分享。

[TOC]

1. 数据库概念

参考oracle官网定义
https://www.oracle.com/cn/database/what-is-database/
数据库是结构化信息或数据(一般以电子形式存储在计算机系统中)的有组织的集合,通常由数据库管理系统 (DBMS) 来控制。
在现实中,数据、DBMS 及关联应用一起被称为数据库系统,通常简称为数据库。

为了提高数据处理和查询效率,当今最常见的数据库通常以行和列的形式将数据存储在一系列的表中,支持用户便捷地访问、管理、修改、更新、控制和组织数据。另外,大多数数据库都使用结构化查询语言 (SQL) 来编写和查询数据。

1.1 数据库类型

参考oracle官网定义
https://www.oracle.com/cn/database/what-is-database/#link5
数据库有很多种,至于各种数据库孰优孰劣,主要取决于企业希望如何使用数据。

  • 关系数据库:关系数据库在 20 世纪 80 年代成为主流。关系数据库中的项被组织为一系列具有列和行的表。关系数据库技术为访问结构化信息提供了最有效和灵活的方法。
  • 面向对象数据库:面向对象数据库中的信息以对象的形式表示,这与面向对象的编程相类似。
  • 分布式数据库:分布式数据库由位于不同站点的两个或多个文件组成。数据库可以存储在多台计算机上,位于同一个物理位置,或分散在不同的网络上。
  • 数据仓库:数据仓库是数据的中央存储库,是专为快速查询和分析而设计的数据库。
  • NoSQL 数据库:NoSQL 或非关系数据库,支持存储和操作非结构化及半结构化数据(与关系数据库相反,关系数据库定义了应如何组合插入数据库的数据)。随着 Web 应用的日益普及和复杂化,NoSQL 数据库得到了越来越广泛的应用。
  • 图形数据库:图形数据库根据实体和实体之间的关系来存储数据。
  • OLTP 数据库:OLTP 数据库是一种高速分析数据库,专为多个用户执行大量事务而设计。 这些只是目前投入使用的几十种数据库中的一小部分。另外还有许多针对具体的科学、财务或其他功能而定制的不太常见的数据库。除了不同的数据库类型之外,技术开发方法的变化以及云和自动化等重大进步也在推动数据库朝新的方向发展。一些最新的数据库包括
  • 开源数据库:开源数据库系统是指源代码为开放源码的数据库系统,它可以是 SQL 或 NoSQL 数据库。
  • 云数据库:云数据库指位于私有云、公有云或混合云计算平台上的结构化或非结构化数据集合。云数据库模式分为两类:传统模式和数据库即服务 (DBaaS)。在 DBaaS 中,管理任务和维护由服务提供商执行。
  • 多模型数据库:多模型数据库将不同类型的数据库模型结合到一个集成的后端中。这意味着它们可以支持各种不同的数据类型。
  • 文档/JSON 数据库:文档数据库专为存储、检索和管理面向文档的信息而设计,是一种以 JSON 格式存储数据的现代方法,而不是采用行和列的形式。
  • 自治驾驶数据库:基于云的自治驾驶数据库(也称作自治数据库)是一种全新的极具革新性的数据库,它利用机器学习技术自动执行数据库调优、保护、备份、更新,以及传统上由数据库管理员 (DBA) 执行的其他常规管理任务。

1.2 数据库和电子表格的区别

数据库和电子表格(例如 Microsoft Excel)都可以便捷地存储信息,两者的主要区别包括:

  • 存储和操作数据的方式
  • 谁可以访问数据
  • 可以存储多少数据 从一开始,电子表格就是专门针对单一用户而设计的,其特性也反映了这一点。电子表格非常适合不需要执行太多高度复杂的数据操作的单一用户或少数用户。另一方面,数据库的设计是为了保存大量有组织的信息,有时是海量的。数据库允许多个用户同时使用高度复杂的逻辑和语言,快速、安全地访问和查询数据。

1.3 关系型数据库和非关系型数据库的区别

参考 https://cloud.tencent.com/developer/article/1784274

1.3.1 关系型

关系型数据库指的是使用关系模型(二维表格模型)来组织数据的数据库。
关系模型可以简单理解为二维表格模型,而一个关系型数据库就是由二维表及其之间的关系组成的一个数据组织。
常见关系型数据库管理系统(ORDBMS):
Oracle, MySql, Microsoft SQL Server, SQLite, PostgreSQL, IBM DB2等。

  • 关系型数据库的优势:
    • 采用二维表结构非常贴近正常开发逻辑(关系型数据模型相对层次型数据模型和网状型数据模型等其他模型来说更容易理解)
    • 支持通用的SQL(结构化查询语言)语句;
    • 丰富的完整性大大减少了数据冗余和数据不一致的问题。并且全部由表结构组成,文件格式一致;
    • 可以用SQL句子多个表之间做非常繁杂的查询;
    • 关系型数据库提供对事务的支持,能保证系统中事务的正确执行,同时提供事务的恢复、回滚、并发控制和死锁问题的解决。
    • 数据存储在磁盘中,安全可靠。
  • 关系型数据库存在的不足: 随着互联网企业的不断发展,数据日益增多,因此关系型数据库面对海量的数据会存在很多的不足。
    • 高并发读写能力差:网站类用户的并发性访问非常高,而一台数据库的最大连接数有限,且硬盘 I/O 有限,不能满足很多人同时连接。
    • 海量数据情况下读写效率低:对大数据量的表进行读写操作时,需要等待较长的时间等待响应。
    • 可扩展性不足:不像web server和app server那样简单的添加硬件和服务节点来拓展性能和负荷工作能力。
    • 数据模型灵活度低:关系型数据库的数据模型定义严格,无法快速容纳新的数据类型(需要提前知道需要存储什么样类型的数据)。

1.3.2 非关系型

非关系型数据库(NOSQL):
非关系型数据库又被称为 NoSQL(Not Only SQL ),意为不仅仅是 SQL。通常指数据以对象的形式存储在数据库中,而对象之间的关系通过每个对象自身的属性来决定,常用于存储非结构化的数据。
常见的NOSQL数据库:
键值数据库:Redis、Memcached、Riak
列族数据库:Bigtable、HBase、Cassandra
文档数据库:MongoDB、CouchDB、MarkLogic
图形数据库:Neo4j、InfoGrid

  • 非关系型数据库的优势:
    • 非关系型数据库存储数据的格式可以是 key-value 形式、文档形式、图片形式等。使用灵活,应用场景广泛,而关系型数据库则只支持基础类型。
    • 速度快,效率高。 NoSQL 可以使用硬盘或者随机存储器作为载体,而关系型数据库只能使用硬盘。
    • 海量数据的维护和处理非常轻松,成本低。
    • 非关系型数据库具有扩展简单、高并发、高稳定性、成本低廉的优势。
    • 可以实现数据的分布式处理。
  • 非关系型数据库存在的不足:
    • 非关系型数据库暂时不提供 SQL 支持,学习和使用成本较高。
    • 非关系数据库没有事务处理,无法保证数据的完整性和安全性。适合处理海量数据,但是不一定安全。
    • 功能没有关系型数据库完善。
    • 复杂表关联查询不容易实现。

2. SQL

参考oracle官网定义
https://www.oracle.com/cn/database/what-is-database/#link2
目前几乎所有的关系数据库都使用 SQL 编程语言来查询、操作和定义数据,进行数据访问控制。SQL 最初于 20 世纪 70 年代由 IBM 开发,当时 Oracle 是一个主要的贡献者,这推动了 SQL ANSI 标准的实施,而 SQL 的兴起也刺激了 IBM、Oracle 和 Microsoft 等公司开始全面扩张。时至今日,虽然 SQL 仍被广泛使用,但是新的编程语言也已经崭露头角。

参考菜鸟教程
https://www.runoob.com/sql/sql-intro.html

  • SQL 指结构化查询语言,全称是 Structured Query Language。
  • SQL 让您可以访问和处理数据库,包括数据插入、查询、更新和删除。
  • SQL 在1986年成为 ANSI(American National Standards Institute 美国国家标准化组织)的一项标准,在 1987 年成为国际标准化组织(ISO)标准。
  • 虽然 SQL 是一门 ANSI(American National Standards Institute 美国国家标准化组织)标准的计算机语言,但是仍然存在着多种不同版本的 SQL 语言。 然而,为了与 ANSI 标准相兼容,它们必须以相似的方式共同地来支持一些主要的命令(比如 SELECT、UPDATE、DELETE、INSERT、WHERE 等等)。 注释:除了 SQL 标准之外,大部分 SQL 数据库程序都拥有它们自己的专有扩展!

2.1 SQL分类

SQL语句的五种分类分别是DQL、DML、DDL、TCL和DCL,下面对SQL语句的五种分类进行列举:

  • 数据库查询语言(DQL)
    数据查询语言DQL基本结构是由SELECT子句,FROM子句,WHERE 子句组成的查询块,简称DQL,Data Query Language。代表关键字为select。

  • 数据库操作语言(DML)
    用户通过它可以实现对数据库的基本操作。简称DML,Data Manipulation Language。代表关键字为insert、delete 、update。

  • 数据库定义语言(DDL)
    数据定义语言DDL用来创建数据库中的各种对象,创建、删除、修改表的结构,比如表、视图、索引、同义词、聚簇等,简称DDL,Data Denifition Language。
    代表关键字为create、drop、alter。
    和DML相比,DML是修改数据库表中的数据,而 DDL 是修改数据中表的结构。

  • 事务控制语言(TCL)
    TCL经常被用于快速原型开发、脚本编程、GUI和测试等方面,它的语句能确保被DML语句影响的表的所有行及时得以更新,简称:TCL,Trasactional Control Languag。代表关键字为begin transaction 、commit、rollback。

  • 指针控制语言(CCL)
    它的语句,像DECLARE CURSOR,FETCH INTO和UPDATE WHERE CURRENT用于对一个或多个表单独行的操作。

  • 数据控制语言(DCL)
    数据控制语言DCL用来授予或回收访问数据库的某种特权,并控制数据库操纵事务发生的时间及效果,对数据库实行监视等,是一种可对数据访问权进行控制的指令,它可以控制特定用户账户对数据表、查看表、预存程序、用户自定义函数等数据库对象的控制权。简称:DCL,Data Control Language。代表关键字为grant、revoke。DCL以控制用户的访问权限为主,GRANT为授权语句,对应的REVOKE是撤销授权语句。

2.2 SQL基础语法

参考https://www.runoob.com/sql/sql-syntax.html
SQL 对大小写不敏感:SELECT 与 select 是相同的。
某些数据库系统要求在每条 SQL 语句的末端使用分号。
SQL的注释:
# 注释内容
-- 注释内容前有一个空格
/*注释内容*/

2.2.1 库相关操作

指定当前使用的字符集(兼容客户端工具字符集,避免数据显示乱码)

set names utf8;

列出数据库:

show databases; show databases [like '%模糊名称%'];

建库:

create database  test_db default character set utf8mb4 collate utf8mb4_0900_ai_ci;

查看建库DDL语句:

show create database test_db;

切换到当前库:

use test_db;

显示当前正在使用的是哪个库:

status;

修改数据库:

http://c.biancheng.net/view/2414.html 在 MySQL 数据库中只能对数据库使用的字符集和校对规则进行修改 ALTER DATABASE [数据库名] {  [ DEFAULT ] CHARACTER SET <字符集名> | [ DEFAULT ] COLLATE <校对规则名>}

删除数据库:

删除数据库后,数据库中存储的所有数据表和数据也将一同被删除,而且不能恢复。因此最好在删除数据库之前先将数据库进行备份。 DROP DATABASE [ IF EXISTS ] <数据库名>

2.2.2 表相关操作

列出当前库中所有表:

show tables; show tables [like %模糊表名%];

建表

CREATE TABLE <表名> ([表定义选项])[表选项][分区选项]; 其中,[表定义选项]的格式为: <列名1> <类型1> [,…] <列名n> <类型n> CREATE TABLE 命令语法比较多,其主要是由表创建定义(create-definition)、表选项(table-options)和分区选项(partition-options)所组成的。  CREATE TABLE `user_infos` (   `id` bigint unsigned NOT NULL AUTO_INCREMENT,   `name` longtext,   `gender` longtext,   `hobby` longtext,   `created_at` datetime(3) DEFAULT NULL,   `updated_at` datetime(3) DEFAULT NULL,   `deleted_at` datetime(3) DEFAULT NULL,   PRIMARY KEY (`id`),   KEY `idx_user_infos_deleted_at` (`deleted_at`) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;  CREATE TABLE statistics (     id BIGINT NOT NULL AUTO_INCREMENT,     class_id BIGINT NOT NULL,     average DOUBLE NOT NULL,     PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

查看表字段信息:

desc tb_name;

查看建表语句:

show create table tb_name;

插入记录

insert into tb_name (column1, column2, ...) values (val1, val2, ...); INSERT INTO ... SELECT 

查询记录

select */[distinct] columnN from tb_name1[,tb_name2...] [where ...[and...][or...][like...][in...][between...and] [group by...][having ...][order by] [limit ...]]

更新记录

update tb_name set column1=val1, column2=val2, ... where ...

删除记录

delete from tb_name; truncate table tb_name; 如果我们仅仅需要删除表内的数据,但并不删除表本身,那么我们该如何做呢? 请使用 TRUNCATE TABLE 语句: TRUNCATE TABLE table_name

改表结构

其语法格式如下: ALTER TABLE <表名> [修改选项]  修改选项的语法格式如下: { ADD COLUMN <列名> <类型> | CHANGE COLUMN <旧列名> <新列名> <新列类型> | ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT } | MODIFY COLUMN <列名> <类型> | DROP COLUMN <列名> | RENAME TO <新表名> | CHARACTER SET <字符集名> | COLLATE <校对规则名> }

改字段

例如: ALTER TABLE tb_emp1 CHANGE col1 col3 CHAR(30); 例如: ALTER TABLE tb_emp1 MODIFY col2 VARCHAR(30);

加字段

ALTER TABLE <表名> ADD <新字段名><数据类型>[约束条件]; 例如: ALTER TABLE student ADD age INT(4); ALTER TABLE student ADD stuId INT(4) FIRST;  ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] AFTER <已经存在的字段名>; ALTER TABLE student ADD stuno INT(11) AFTER name;

删字段

例如: ALTER TABLE tb_emp1 DROP col2;

改表名

ALTER TABLE <旧表名> RENAME [TO] <新表名>; 例如: alter table product rename products;

改表字符集

ALTER TABLE 表名 [DEFAULT] CHARACTER SET <字符集名> [DEFAULT] COLLATE <校对规则名>; 例如: ALTER TABLE tb_students_info CHARACTER SET gb2312  DEFAULT COLLATE gb2312_chinese_ci;

2.2.3 连接查询相关

A inner join B 取交集。
A left join B 取 A 全部,B 没有对应的值为 null。
A right join B 取 B 全部 A 没有对应的值为 null。
A full outer join B 取并集,彼此没有对应的值为 null。
对应条件在 on 后面填写。

  • inner join等同于平时常用的多表关联
SELECT column_name(s) FROM table1 [INNER] JOIN table2 ON table1.column_name=table2.column_name; 等同于 SELECT column_name(s) FROM table1, table2 where table1.column_name=table2.column_name;
  • left join
LEFT JOIN 关键字从左表(table1)返回所有的行,即使右表(table2)中没有匹配。如果右表中没有匹配,则结果为 NULL。 SELECT column_name(s) FROM table1 LEFT [OUTER] JOIN table2 ON table1.column_name=table2.column_name;
  • right join
RIGHT JOIN 关键字从右表(table2)返回所有的行,即使左表(table1)中没有匹配。如果左表中没有匹配,则结果为 NULL。 SELECT column_name(s) FROM table1 RIGHT [OUTER[ JOIN table2 ON table1.column_name=table2.column_name;
  • full join
FULL OUTER JOIN 关键字只要左表(table1)和右表(table2)其中一个表中存在匹配,则返回行. FULL OUTER JOIN 关键字结合了 LEFT JOIN 和 RIGHT JOIN 的结果。

2.2.4 union 和 union all

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
默认地,UNION 操作符选取不同的值。如果允许重复的值,请使用 UNION ALL。

2.2.5 like

LIKE 子句中使用百分号 %字符来表示任意字符,类似于UNIX或正则表达式中的星号 *。
如果没有使用百分号 %, LIKE 子句与等号 = 的效果是一样的。

SQL 提供了四种匹配方式。 %:表示任意 0 个或多个字符。可匹配任意类型和长度的字符,有些情况下若是中文,请使用两个百分号(%%)表示。 _:表示任意单个字符。匹配单个任意字符,它常用来限制表达式的字符长度语句。 []:表示括号内所列字符中的一个(类似正则表达式)。指定一个字符、字符串或范围,要求所匹配对象为它们中的任一个。 [^] :表示不在括号所列之内的单个字符。其取值和 [] 相同,但它要求所匹配对象为指定字符以外的任一个字符。 查询内容包含通配符时,由于通配符的缘故,导致我们查询特殊字符 “%”、“_”、“[” 的语句无法正常实现,而把特殊字符用 “[ ]” 括起便可正常查询。

2.2.5.1 like导致索引失效

使用like %通配符时常常会引起索引失效的问题(%放在开头会引起索引失效)。
其实使用覆盖索引(覆盖索引是select的数据列只用从索引中就能够取得,不必读取数据行,换句话说查询列要被所建的索引覆盖。索引的字段不只包含查询列,还包含查询条件、排序等),也没有解决索引失效的问题,使用覆盖索引后还是会在索引文件中进行全表扫描,也就是没有使用到索引的有序性进行二分查找,而是直接扫描索引文件,他的type只是index,而不是ref或者range。使用覆盖索引在这里只是避免了回表。

真要提高搜索效率还得用 全文索引 或 es
参考 https://blog.csdn.net/mrzhouxiaofei/article/details/79940958
MySQL 5.6 及以后的版本,MyISAM 和 InnoDB 存储引擎均支持全文索引;
只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。
测试或使用全文索引时,要先看一下自己的 MySQL 版本、存储引擎和数据类型是否支持全文索引。

create table fulltext_test (     id int(11) NOT NULL AUTO_INCREMENT,     content text NOT NULL,     tag varchar(255),     PRIMARY KEY (id),     FULLTEXT KEY content_tag_fulltext(content,tag)  // 创建联合全文索引列 ) ENGINE=MyISAM DEFAULT CHARSET=utf8;  create fulltext index content_tag_fulltext on fulltext_test(content,tag);  alter table fulltext_test add fulltext index content_tag_fulltext(content,tag);  drop index content_tag_fulltext on fulltext_test;  select * from fulltext_test  where match(content,tag) against('xxx xxx'); 注意: match() 函数中指定的列必须和全文索引中指定的列完全相同,否则就会报错,无法使用全文索引,这是因为全文索引不会记录关键字来自哪一列。如果想要对某一列使用全文索引,请单独为该列创建全文索引。

2.2.6 group by

GROUP BY 语句根据一个或多个列对结果集进行分组。
在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。
select子句中的列名必须为分组列或列函数,列函数对于group by子句定义的每个组返回一个结果。
group by 子句中列出的每个列都必须是检索列或有效的表达式,但不能是聚集函数,如果在select 中使用表达式,则必须在group by 子句中指定相同的表达式,不能使用别名。

SELECT column_name, function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name;

2.2.6.1 使用 WITH ROLLUP

WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计(SUM,AVG,COUNT…)。
数据库篇·第4章[1]·数据库基础
数据库篇·第4章[1]·数据库基础

2.2.7 having 过滤分组

可应用限定条件进行分组,以便系统仅对满足条件的组返回结果。因此,在GROUP BY子句后面包含了一个HAVING子句。HAVING类似于WHERE(唯一的差别是WHERE过滤行,HAVING过滤组)HAVING支持所有WHERE操作符。

例如,查找雇员数超过2个的部门的最高和最低薪水: SELECT dept ,MAX(salary) AS MAXIMUM ,MIN(salary) AS MINIMUM FROM STAFF GROUP BY dept HAVING COUNT(*) > 2 ORDER BY dept  例如,查找雇员平均工资大于3000的部门的最高薪水和最低薪水: SELECT dept,MAX(salary) AS MAXIMUM,MIN(salary) AS MINIMUM FROM STAFF GROUP BY dept HAVING AVG(salary) > 3000 ORDER BY dept

索引
create index
drop index

2.2.5 约束

SQL 约束用于规定表中的数据规则。
如果存在违反约束的数据行为,行为会被约束终止。
约束可以在创建表时规定(通过 CREATE TABLE 语句),或者在表创建之后规定(通过 ALTER TABLE 语句)。
在 SQL 中,我们有如下约束:

  • NOT NULL – 指示某列不能存储 NULL 值。
  • UNIQUE – 保证某列的每行必须有唯一的值。
  • PRIMARY KEY – NOT NULL 和 UNIQUE 的结合。 确保某列(或两个列多个列的结合)有唯一标识,有助于更容易更快速地找到表中的一个特定的记录。 拥有自动定义的 UNIQUE 约束。 每个表可以有多个 UNIQUE 约束,但是每个表只能有一个 PRIMARY KEY 约束。
  • FOREIGN KEY – 主键,保证一个表中的数据匹配另一个表中的值的参照完整性。 FOREIGN KEY 约束用于预防破坏表之间连接的行为。 FOREIGN KEY 约束也能防止非法数据插入外键列,因为它必须是它指向的那个表中的值之一。
  • CHECK – 保证列中的值符合指定的条件。限制列中的值的范围。 如果对单个列定义 CHECK 约束,那么该列只允许特定的值。
  • DEFAULT – 规定没有给列赋值时的默认值。
CREATE TABLE Persons (     ID int NOT NULL,     LastName varchar(255) NOT NULL,     FirstName varchar(255) NOT NULL,     Age int ); ALTER TABLE Persons MODIFY Age int NOT NULL; -- 添加not null ALTER TABLE Persons MODIFY Age int NULL; -- 去除not null
CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName) ); --命名约束 ALTER TABLE Persons ADD UNIQUE (P_Id); ALTER TABLE Persons ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName); ALTER TABLE Persons DROP INDEX uc_PersonID; -- 撤销unique ALTER TABLE Persons DROP CONSTRAINT uc_PersonID;
CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), PRIMARY KEY (P_Id) ); CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName) ); -- 命名主键且由多个字段组成主键 ALTER TABLE Persons ADD PRIMARY KEY (P_Id); ALTER TABLE Persons ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName); ALTER TABLE Persons DROP PRIMARY KEY; --撤销主键
CREATE TABLE Orders ( O_Id int NOT NULL, OrderNo int NOT NULL, P_Id int, PRIMARY KEY (O_Id), FOREIGN KEY (P_Id) REFERENCES Persons(P_Id) ); CREATE TABLE Orders ( O_Id int NOT NULL, OrderNo int NOT NULL, P_Id int, PRIMARY KEY (O_Id), CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id) REFERENCES Persons(P_Id) ); -- 命名外键  ALTER TABLE Orders ADD FOREIGN KEY (P_Id) REFERENCES Persons(P_Id); ALTER TABLE Orders ADD CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id) REFERENCES Persons(P_Id);  ALTER TABLE Orders DROP FOREIGN KEY fk_PerOrders;
CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), CHECK (P_Id>0) ); CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes') );  ALTER TABLE Persons ADD CHECK (P_Id>0); ALTER TABLE Persons ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes');  ALTER TABLE Persons DROP CHECK chk_Person;
CREATE TABLE Persons (     P_Id int NOT NULL,     LastName varchar(255) NOT NULL,     FirstName varchar(255),     Address varchar(255),     City varchar(255) DEFAULT 'Sandnes' ); CREATE TABLE Orders (     O_Id int NOT NULL,     OrderNo int NOT NULL,     P_Id int,     OrderDate date DEFAULT GETDATE() );  ALTER TABLE Persons ALTER City SET DEFAULT 'SANDNES'; ALTER TABLE Persons ALTER City DROP DEFAULT;

[TOC]

1. 数据库概念

参考oracle官网定义
https://www.oracle.com/cn/database/what-is-database/
数据库是结构化信息或数据(一般以电子形式存储在计算机系统中)的有组织的集合,通常由数据库管理系统 (DBMS) 来控制。
在现实中,数据、DBMS 及关联应用一起被称为数据库系统,通常简称为数据库。

为了提高数据处理和查询效率,当今最常见的数据库通常以行和列的形式将数据存储在一系列的表中,支持用户便捷地访问、管理、修改、更新、控制和组织数据。另外,大多数数据库都使用结构化查询语言 (SQL) 来编写和查询数据。

1.1 数据库类型

参考oracle官网定义
https://www.oracle.com/cn/database/what-is-database/#link5
数据库有很多种,至于各种数据库孰优孰劣,主要取决于企业希望如何使用数据。

  • 关系数据库:关系数据库在 20 世纪 80 年代成为主流。关系数据库中的项被组织为一系列具有列和行的表。关系数据库技术为访问结构化信息提供了最有效和灵活的方法。
  • 面向对象数据库:面向对象数据库中的信息以对象的形式表示,这与面向对象的编程相类似。
  • 分布式数据库:分布式数据库由位于不同站点的两个或多个文件组成。数据库可以存储在多台计算机上,位于同一个物理位置,或分散在不同的网络上。
  • 数据仓库:数据仓库是数据的中央存储库,是专为快速查询和分析而设计的数据库。
  • NoSQL 数据库:NoSQL 或非关系数据库,支持存储和操作非结构化及半结构化数据(与关系数据库相反,关系数据库定义了应如何组合插入数据库的数据)。随着 Web 应用的日益普及和复杂化,NoSQL 数据库得到了越来越广泛的应用。
  • 图形数据库:图形数据库根据实体和实体之间的关系来存储数据。
  • OLTP 数据库:OLTP 数据库是一种高速分析数据库,专为多个用户执行大量事务而设计。 这些只是目前投入使用的几十种数据库中的一小部分。另外还有许多针对具体的科学、财务或其他功能而定制的不太常见的数据库。除了不同的数据库类型之外,技术开发方法的变化以及云和自动化等重大进步也在推动数据库朝新的方向发展。一些最新的数据库包括
  • 开源数据库:开源数据库系统是指源代码为开放源码的数据库系统,它可以是 SQL 或 NoSQL 数据库。
  • 云数据库:云数据库指位于私有云、公有云或混合云计算平台上的结构化或非结构化数据集合。云数据库模式分为两类:传统模式和数据库即服务 (DBaaS)。在 DBaaS 中,管理任务和维护由服务提供商执行。
  • 多模型数据库:多模型数据库将不同类型的数据库模型结合到一个集成的后端中。这意味着它们可以支持各种不同的数据类型。
  • 文档/JSON 数据库:文档数据库专为存储、检索和管理面向文档的信息而设计,是一种以 JSON 格式存储数据的现代方法,而不是采用行和列的形式。
  • 自治驾驶数据库:基于云的自治驾驶数据库(也称作自治数据库)是一种全新的极具革新性的数据库,它利用机器学习技术自动执行数据库调优、保护、备份、更新,以及传统上由数据库管理员 (DBA) 执行的其他常规管理任务。

1.2 数据库和电子表格的区别

数据库和电子表格(例如 Microsoft Excel)都可以便捷地存储信息,两者的主要区别包括:

  • 存储和操作数据的方式
  • 谁可以访问数据
  • 可以存储多少数据 从一开始,电子表格就是专门针对单一用户而设计的,其特性也反映了这一点。电子表格非常适合不需要执行太多高度复杂的数据操作的单一用户或少数用户。另一方面,数据库的设计是为了保存大量有组织的信息,有时是海量的。数据库允许多个用户同时使用高度复杂的逻辑和语言,快速、安全地访问和查询数据。

1.3 关系型数据库和非关系型数据库的区别

参考 https://cloud.tencent.com/developer/article/1784274

1.3.1 关系型

关系型数据库指的是使用关系模型(二维表格模型)来组织数据的数据库。
关系模型可以简单理解为二维表格模型,而一个关系型数据库就是由二维表及其之间的关系组成的一个数据组织。
常见关系型数据库管理系统(ORDBMS):
Oracle, MySql, Microsoft SQL Server, SQLite, PostgreSQL, IBM DB2等。

  • 关系型数据库的优势:
    • 采用二维表结构非常贴近正常开发逻辑(关系型数据模型相对层次型数据模型和网状型数据模型等其他模型来说更容易理解)
    • 支持通用的SQL(结构化查询语言)语句;
    • 丰富的完整性大大减少了数据冗余和数据不一致的问题。并且全部由表结构组成,文件格式一致;
    • 可以用SQL句子多个表之间做非常繁杂的查询;
    • 关系型数据库提供对事务的支持,能保证系统中事务的正确执行,同时提供事务的恢复、回滚、并发控制和死锁问题的解决。
    • 数据存储在磁盘中,安全可靠。
  • 关系型数据库存在的不足: 随着互联网企业的不断发展,数据日益增多,因此关系型数据库面对海量的数据会存在很多的不足。
    • 高并发读写能力差:网站类用户的并发性访问非常高,而一台数据库的最大连接数有限,且硬盘 I/O 有限,不能满足很多人同时连接。
    • 海量数据情况下读写效率低:对大数据量的表进行读写操作时,需要等待较长的时间等待响应。
    • 可扩展性不足:不像web server和app server那样简单的添加硬件和服务节点来拓展性能和负荷工作能力。
    • 数据模型灵活度低:关系型数据库的数据模型定义严格,无法快速容纳新的数据类型(需要提前知道需要存储什么样类型的数据)。

1.3.2 非关系型

非关系型数据库(NOSQL):
非关系型数据库又被称为 NoSQL(Not Only SQL ),意为不仅仅是 SQL。通常指数据以对象的形式存储在数据库中,而对象之间的关系通过每个对象自身的属性来决定,常用于存储非结构化的数据。
常见的NOSQL数据库:
键值数据库:Redis、Memcached、Riak
列族数据库:Bigtable、HBase、Cassandra
文档数据库:MongoDB、CouchDB、MarkLogic
图形数据库:Neo4j、InfoGrid

  • 非关系型数据库的优势:
    • 非关系型数据库存储数据的格式可以是 key-value 形式、文档形式、图片形式等。使用灵活,应用场景广泛,而关系型数据库则只支持基础类型。
    • 速度快,效率高。 NoSQL 可以使用硬盘或者随机存储器作为载体,而关系型数据库只能使用硬盘。
    • 海量数据的维护和处理非常轻松,成本低。
    • 非关系型数据库具有扩展简单、高并发、高稳定性、成本低廉的优势。
    • 可以实现数据的分布式处理。
  • 非关系型数据库存在的不足:
    • 非关系型数据库暂时不提供 SQL 支持,学习和使用成本较高。
    • 非关系数据库没有事务处理,无法保证数据的完整性和安全性。适合处理海量数据,但是不一定安全。
    • 功能没有关系型数据库完善。
    • 复杂表关联查询不容易实现。

2. SQL

参考oracle官网定义
https://www.oracle.com/cn/database/what-is-database/#link2
目前几乎所有的关系数据库都使用 SQL 编程语言来查询、操作和定义数据,进行数据访问控制。SQL 最初于 20 世纪 70 年代由 IBM 开发,当时 Oracle 是一个主要的贡献者,这推动了 SQL ANSI 标准的实施,而 SQL 的兴起也刺激了 IBM、Oracle 和 Microsoft 等公司开始全面扩张。时至今日,虽然 SQL 仍被广泛使用,但是新的编程语言也已经崭露头角。

参考菜鸟教程
https://www.runoob.com/sql/sql-intro.html

  • SQL 指结构化查询语言,全称是 Structured Query Language。
  • SQL 让您可以访问和处理数据库,包括数据插入、查询、更新和删除。
  • SQL 在1986年成为 ANSI(American National Standards Institute 美国国家标准化组织)的一项标准,在 1987 年成为国际标准化组织(ISO)标准。
  • 虽然 SQL 是一门 ANSI(American National Standards Institute 美国国家标准化组织)标准的计算机语言,但是仍然存在着多种不同版本的 SQL 语言。 然而,为了与 ANSI 标准相兼容,它们必须以相似的方式共同地来支持一些主要的命令(比如 SELECT、UPDATE、DELETE、INSERT、WHERE 等等)。 注释:除了 SQL 标准之外,大部分 SQL 数据库程序都拥有它们自己的专有扩展!

2.1 SQL分类

SQL语句的五种分类分别是DQL、DML、DDL、TCL和DCL,下面对SQL语句的五种分类进行列举:

  • 数据库查询语言(DQL)
    数据查询语言DQL基本结构是由SELECT子句,FROM子句,WHERE 子句组成的查询块,简称DQL,Data Query Language。代表关键字为select。

  • 数据库操作语言(DML)
    用户通过它可以实现对数据库的基本操作。简称DML,Data Manipulation Language。代表关键字为insert、delete 、update。

  • 数据库定义语言(DDL)
    数据定义语言DDL用来创建数据库中的各种对象,创建、删除、修改表的结构,比如表、视图、索引、同义词、聚簇等,简称DDL,Data Denifition Language。
    代表关键字为create、drop、alter。
    和DML相比,DML是修改数据库表中的数据,而 DDL 是修改数据中表的结构。

  • 事务控制语言(TCL)
    TCL经常被用于快速原型开发、脚本编程、GUI和测试等方面,它的语句能确保被DML语句影响的表的所有行及时得以更新,简称:TCL,Trasactional Control Languag。代表关键字为begin transaction 、commit、rollback。

  • 指针控制语言(CCL)
    它的语句,像DECLARE CURSOR,FETCH INTO和UPDATE WHERE CURRENT用于对一个或多个表单独行的操作。

  • 数据控制语言(DCL)
    数据控制语言DCL用来授予或回收访问数据库的某种特权,并控制数据库操纵事务发生的时间及效果,对数据库实行监视等,是一种可对数据访问权进行控制的指令,它可以控制特定用户账户对数据表、查看表、预存程序、用户自定义函数等数据库对象的控制权。简称:DCL,Data Control Language。代表关键字为grant、revoke。DCL以控制用户的访问权限为主,GRANT为授权语句,对应的REVOKE是撤销授权语句。

2.2 SQL基础语法

参考https://www.runoob.com/sql/sql-syntax.html
SQL 对大小写不敏感:SELECT 与 select 是相同的。
某些数据库系统要求在每条 SQL 语句的末端使用分号。
SQL的注释:
# 注释内容
-- 注释内容前有一个空格
/*注释内容*/

2.2.1 库相关操作

指定当前使用的字符集(兼容客户端工具字符集,避免数据显示乱码)

set names utf8;

列出数据库:

show databases; show databases [like '%模糊名称%'];

建库:

create database  test_db default character set utf8mb4 collate utf8mb4_0900_ai_ci;

查看建库DDL语句:

show create database test_db;

切换到当前库:

use test_db;

显示当前正在使用的是哪个库:

status;

修改数据库:

http://c.biancheng.net/view/2414.html 在 MySQL 数据库中只能对数据库使用的字符集和校对规则进行修改 ALTER DATABASE [数据库名] {  [ DEFAULT ] CHARACTER SET <字符集名> | [ DEFAULT ] COLLATE <校对规则名>}

删除数据库:

删除数据库后,数据库中存储的所有数据表和数据也将一同被删除,而且不能恢复。因此最好在删除数据库之前先将数据库进行备份。 DROP DATABASE [ IF EXISTS ] <数据库名>

2.2.2 表相关操作

列出当前库中所有表:

show tables; show tables [like %模糊表名%];

建表

CREATE TABLE <表名> ([表定义选项])[表选项][分区选项]; 其中,[表定义选项]的格式为: <列名1> <类型1> [,…] <列名n> <类型n> CREATE TABLE 命令语法比较多,其主要是由表创建定义(create-definition)、表选项(table-options)和分区选项(partition-options)所组成的。  CREATE TABLE `user_infos` (   `id` bigint unsigned NOT NULL AUTO_INCREMENT,   `name` longtext,   `gender` longtext,   `hobby` longtext,   `created_at` datetime(3) DEFAULT NULL,   `updated_at` datetime(3) DEFAULT NULL,   `deleted_at` datetime(3) DEFAULT NULL,   PRIMARY KEY (`id`),   KEY `idx_user_infos_deleted_at` (`deleted_at`) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;  CREATE TABLE statistics (     id BIGINT NOT NULL AUTO_INCREMENT,     class_id BIGINT NOT NULL,     average DOUBLE NOT NULL,     PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

查看表字段信息:

desc tb_name;

查看建表语句:

show create table tb_name;

插入记录

insert into tb_name (column1, column2, ...) values (val1, val2, ...); INSERT INTO ... SELECT 

查询记录

select */[distinct] columnN from tb_name1[,tb_name2...] [where ...[and...][or...][like...][in...][between...and] [group by...][having ...][order by] [limit ...]]

更新记录

update tb_name set column1=val1, column2=val2, ... where ...

删除记录

delete from tb_name; truncate table tb_name; 如果我们仅仅需要删除表内的数据,但并不删除表本身,那么我们该如何做呢? 请使用 TRUNCATE TABLE 语句: TRUNCATE TABLE table_name

改表结构

其语法格式如下: ALTER TABLE <表名> [修改选项]  修改选项的语法格式如下: { ADD COLUMN <列名> <类型> | CHANGE COLUMN <旧列名> <新列名> <新列类型> | ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT } | MODIFY COLUMN <列名> <类型> | DROP COLUMN <列名> | RENAME TO <新表名> | CHARACTER SET <字符集名> | COLLATE <校对规则名> }

改字段

例如: ALTER TABLE tb_emp1 CHANGE col1 col3 CHAR(30); 例如: ALTER TABLE tb_emp1 MODIFY col2 VARCHAR(30);

加字段

ALTER TABLE <表名> ADD <新字段名><数据类型>[约束条件]; 例如: ALTER TABLE student ADD age INT(4); ALTER TABLE student ADD stuId INT(4) FIRST;  ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] AFTER <已经存在的字段名>; ALTER TABLE student ADD stuno INT(11) AFTER name;

删字段

例如: ALTER TABLE tb_emp1 DROP col2;

改表名

ALTER TABLE <旧表名> RENAME [TO] <新表名>; 例如: alter table product rename products;

改表字符集

ALTER TABLE 表名 [DEFAULT] CHARACTER SET <字符集名> [DEFAULT] COLLATE <校对规则名>; 例如: ALTER TABLE tb_students_info CHARACTER SET gb2312  DEFAULT COLLATE gb2312_chinese_ci;

2.2.3 连接查询相关

A inner join B 取交集。
A left join B 取 A 全部,B 没有对应的值为 null。
A right join B 取 B 全部 A 没有对应的值为 null。
A full outer join B 取并集,彼此没有对应的值为 null。
对应条件在 on 后面填写。

  • inner join等同于平时常用的多表关联
SELECT column_name(s) FROM table1 [INNER] JOIN table2 ON table1.column_name=table2.column_name; 等同于 SELECT column_name(s) FROM table1, table2 where table1.column_name=table2.column_name;
  • left join
LEFT JOIN 关键字从左表(table1)返回所有的行,即使右表(table2)中没有匹配。如果右表中没有匹配,则结果为 NULL。 SELECT column_name(s) FROM table1 LEFT [OUTER] JOIN table2 ON table1.column_name=table2.column_name;
  • right join
RIGHT JOIN 关键字从右表(table2)返回所有的行,即使左表(table1)中没有匹配。如果左表中没有匹配,则结果为 NULL。 SELECT column_name(s) FROM table1 RIGHT [OUTER[ JOIN table2 ON table1.column_name=table2.column_name;
  • full join
FULL OUTER JOIN 关键字只要左表(table1)和右表(table2)其中一个表中存在匹配,则返回行. FULL OUTER JOIN 关键字结合了 LEFT JOIN 和 RIGHT JOIN 的结果。

2.2.4 union 和 union all

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
默认地,UNION 操作符选取不同的值。如果允许重复的值,请使用 UNION ALL。

2.2.5 like

LIKE 子句中使用百分号 %字符来表示任意字符,类似于UNIX或正则表达式中的星号 *。
如果没有使用百分号 %, LIKE 子句与等号 = 的效果是一样的。

SQL 提供了四种匹配方式。 %:表示任意 0 个或多个字符。可匹配任意类型和长度的字符,有些情况下若是中文,请使用两个百分号(%%)表示。 _:表示任意单个字符。匹配单个任意字符,它常用来限制表达式的字符长度语句。 []:表示括号内所列字符中的一个(类似正则表达式)。指定一个字符、字符串或范围,要求所匹配对象为它们中的任一个。 [^] :表示不在括号所列之内的单个字符。其取值和 [] 相同,但它要求所匹配对象为指定字符以外的任一个字符。 查询内容包含通配符时,由于通配符的缘故,导致我们查询特殊字符 “%”、“_”、“[” 的语句无法正常实现,而把特殊字符用 “[ ]” 括起便可正常查询。

2.2.5.1 like导致索引失效

使用like %通配符时常常会引起索引失效的问题(%放在开头会引起索引失效)。
其实使用覆盖索引(覆盖索引是select的数据列只用从索引中就能够取得,不必读取数据行,换句话说查询列要被所建的索引覆盖。索引的字段不只包含查询列,还包含查询条件、排序等),也没有解决索引失效的问题,使用覆盖索引后还是会在索引文件中进行全表扫描,也就是没有使用到索引的有序性进行二分查找,而是直接扫描索引文件,他的type只是index,而不是ref或者range。使用覆盖索引在这里只是避免了回表。

真要提高搜索效率还得用 全文索引 或 es
参考 https://blog.csdn.net/mrzhouxiaofei/article/details/79940958
MySQL 5.6 及以后的版本,MyISAM 和 InnoDB 存储引擎均支持全文索引;
只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。
测试或使用全文索引时,要先看一下自己的 MySQL 版本、存储引擎和数据类型是否支持全文索引。

create table fulltext_test (     id int(11) NOT NULL AUTO_INCREMENT,     content text NOT NULL,     tag varchar(255),     PRIMARY KEY (id),     FULLTEXT KEY content_tag_fulltext(content,tag)  // 创建联合全文索引列 ) ENGINE=MyISAM DEFAULT CHARSET=utf8;  create fulltext index content_tag_fulltext on fulltext_test(content,tag);  alter table fulltext_test add fulltext index content_tag_fulltext(content,tag);  drop index content_tag_fulltext on fulltext_test;  select * from fulltext_test  where match(content,tag) against('xxx xxx'); 注意: match() 函数中指定的列必须和全文索引中指定的列完全相同,否则就会报错,无法使用全文索引,这是因为全文索引不会记录关键字来自哪一列。如果想要对某一列使用全文索引,请单独为该列创建全文索引。

2.2.6 group by

GROUP BY 语句根据一个或多个列对结果集进行分组。
在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。
select子句中的列名必须为分组列或列函数,列函数对于group by子句定义的每个组返回一个结果。
group by 子句中列出的每个列都必须是检索列或有效的表达式,但不能是聚集函数,如果在select 中使用表达式,则必须在group by 子句中指定相同的表达式,不能使用别名。

SELECT column_name, function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name;

2.2.6.1 使用 WITH ROLLUP

WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计(SUM,AVG,COUNT…)。
数据库篇·第4章[1]·数据库基础
数据库篇·第4章[1]·数据库基础

2.2.7 having 过滤分组

可应用限定条件进行分组,以便系统仅对满足条件的组返回结果。因此,在GROUP BY子句后面包含了一个HAVING子句。HAVING类似于WHERE(唯一的差别是WHERE过滤行,HAVING过滤组)HAVING支持所有WHERE操作符。

例如,查找雇员数超过2个的部门的最高和最低薪水: SELECT dept ,MAX(salary) AS MAXIMUM ,MIN(salary) AS MINIMUM FROM STAFF GROUP BY dept HAVING COUNT(*) > 2 ORDER BY dept  例如,查找雇员平均工资大于3000的部门的最高薪水和最低薪水: SELECT dept,MAX(salary) AS MAXIMUM,MIN(salary) AS MINIMUM FROM STAFF GROUP BY dept HAVING AVG(salary) > 3000 ORDER BY dept

索引
create index
drop index

2.2.5 约束

SQL 约束用于规定表中的数据规则。
如果存在违反约束的数据行为,行为会被约束终止。
约束可以在创建表时规定(通过 CREATE TABLE 语句),或者在表创建之后规定(通过 ALTER TABLE 语句)。
在 SQL 中,我们有如下约束:

  • NOT NULL – 指示某列不能存储 NULL 值。
  • UNIQUE – 保证某列的每行必须有唯一的值。
  • PRIMARY KEY – NOT NULL 和 UNIQUE 的结合。 确保某列(或两个列多个列的结合)有唯一标识,有助于更容易更快速地找到表中的一个特定的记录。 拥有自动定义的 UNIQUE 约束。 每个表可以有多个 UNIQUE 约束,但是每个表只能有一个 PRIMARY KEY 约束。
  • FOREIGN KEY – 主键,保证一个表中的数据匹配另一个表中的值的参照完整性。 FOREIGN KEY 约束用于预防破坏表之间连接的行为。 FOREIGN KEY 约束也能防止非法数据插入外键列,因为它必须是它指向的那个表中的值之一。
  • CHECK – 保证列中的值符合指定的条件。限制列中的值的范围。 如果对单个列定义 CHECK 约束,那么该列只允许特定的值。
  • DEFAULT – 规定没有给列赋值时的默认值。
CREATE TABLE Persons (     ID int NOT NULL,     LastName varchar(255) NOT NULL,     FirstName varchar(255) NOT NULL,     Age int ); ALTER TABLE Persons MODIFY Age int NOT NULL; -- 添加not null ALTER TABLE Persons MODIFY Age int NULL; -- 去除not null
CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName) ); --命名约束 ALTER TABLE Persons ADD UNIQUE (P_Id); ALTER TABLE Persons ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName); ALTER TABLE Persons DROP INDEX uc_PersonID; -- 撤销unique ALTER TABLE Persons DROP CONSTRAINT uc_PersonID;
CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), PRIMARY KEY (P_Id) ); CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName) ); -- 命名主键且由多个字段组成主键 ALTER TABLE Persons ADD PRIMARY KEY (P_Id); ALTER TABLE Persons ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName); ALTER TABLE Persons DROP PRIMARY KEY; --撤销主键
CREATE TABLE Orders ( O_Id int NOT NULL, OrderNo int NOT NULL, P_Id int, PRIMARY KEY (O_Id), FOREIGN KEY (P_Id) REFERENCES Persons(P_Id) ); CREATE TABLE Orders ( O_Id int NOT NULL, OrderNo int NOT NULL, P_Id int, PRIMARY KEY (O_Id), CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id) REFERENCES Persons(P_Id) ); -- 命名外键  ALTER TABLE Orders ADD FOREIGN KEY (P_Id) REFERENCES Persons(P_Id); ALTER TABLE Orders ADD CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id) REFERENCES Persons(P_Id);  ALTER TABLE Orders DROP FOREIGN KEY fk_PerOrders;
CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), CHECK (P_Id>0) ); CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes') );  ALTER TABLE Persons ADD CHECK (P_Id>0); ALTER TABLE Persons ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes');  ALTER TABLE Persons DROP CHECK chk_Person;
CREATE TABLE Persons (     P_Id int NOT NULL,     LastName varchar(255) NOT NULL,     FirstName varchar(255),     Address varchar(255),     City varchar(255) DEFAULT 'Sandnes' ); CREATE TABLE Orders (     O_Id int NOT NULL,     OrderNo int NOT NULL,     P_Id int,     OrderDate date DEFAULT GETDATE() );  ALTER TABLE Persons ALTER City SET DEFAULT 'SANDNES'; ALTER TABLE Persons ALTER City DROP DEFAULT;

[TOC]

1. 数据库概念

参考oracle官网定义
https://www.oracle.com/cn/database/what-is-database/
数据库是结构化信息或数据(一般以电子形式存储在计算机系统中)的有组织的集合,通常由数据库管理系统 (DBMS) 来控制。
在现实中,数据、DBMS 及关联应用一起被称为数据库系统,通常简称为数据库。

为了提高数据处理和查询效率,当今最常见的数据库通常以行和列的形式将数据存储在一系列的表中,支持用户便捷地访问、管理、修改、更新、控制和组织数据。另外,大多数数据库都使用结构化查询语言 (SQL) 来编写和查询数据。

1.1 数据库类型

参考oracle官网定义
https://www.oracle.com/cn/database/what-is-database/#link5
数据库有很多种,至于各种数据库孰优孰劣,主要取决于企业希望如何使用数据。

  • 关系数据库:关系数据库在 20 世纪 80 年代成为主流。关系数据库中的项被组织为一系列具有列和行的表。关系数据库技术为访问结构化信息提供了最有效和灵活的方法。
  • 面向对象数据库:面向对象数据库中的信息以对象的形式表示,这与面向对象的编程相类似。
  • 分布式数据库:分布式数据库由位于不同站点的两个或多个文件组成。数据库可以存储在多台计算机上,位于同一个物理位置,或分散在不同的网络上。
  • 数据仓库:数据仓库是数据的中央存储库,是专为快速查询和分析而设计的数据库。
  • NoSQL 数据库:NoSQL 或非关系数据库,支持存储和操作非结构化及半结构化数据(与关系数据库相反,关系数据库定义了应如何组合插入数据库的数据)。随着 Web 应用的日益普及和复杂化,NoSQL 数据库得到了越来越广泛的应用。
  • 图形数据库:图形数据库根据实体和实体之间的关系来存储数据。
  • OLTP 数据库:OLTP 数据库是一种高速分析数据库,专为多个用户执行大量事务而设计。 这些只是目前投入使用的几十种数据库中的一小部分。另外还有许多针对具体的科学、财务或其他功能而定制的不太常见的数据库。除了不同的数据库类型之外,技术开发方法的变化以及云和自动化等重大进步也在推动数据库朝新的方向发展。一些最新的数据库包括
  • 开源数据库:开源数据库系统是指源代码为开放源码的数据库系统,它可以是 SQL 或 NoSQL 数据库。
  • 云数据库:云数据库指位于私有云、公有云或混合云计算平台上的结构化或非结构化数据集合。云数据库模式分为两类:传统模式和数据库即服务 (DBaaS)。在 DBaaS 中,管理任务和维护由服务提供商执行。
  • 多模型数据库:多模型数据库将不同类型的数据库模型结合到一个集成的后端中。这意味着它们可以支持各种不同的数据类型。
  • 文档/JSON 数据库:文档数据库专为存储、检索和管理面向文档的信息而设计,是一种以 JSON 格式存储数据的现代方法,而不是采用行和列的形式。
  • 自治驾驶数据库:基于云的自治驾驶数据库(也称作自治数据库)是一种全新的极具革新性的数据库,它利用机器学习技术自动执行数据库调优、保护、备份、更新,以及传统上由数据库管理员 (DBA) 执行的其他常规管理任务。

1.2 数据库和电子表格的区别

数据库和电子表格(例如 Microsoft Excel)都可以便捷地存储信息,两者的主要区别包括:

  • 存储和操作数据的方式
  • 谁可以访问数据
  • 可以存储多少数据 从一开始,电子表格就是专门针对单一用户而设计的,其特性也反映了这一点。电子表格非常适合不需要执行太多高度复杂的数据操作的单一用户或少数用户。另一方面,数据库的设计是为了保存大量有组织的信息,有时是海量的。数据库允许多个用户同时使用高度复杂的逻辑和语言,快速、安全地访问和查询数据。

1.3 关系型数据库和非关系型数据库的区别

参考 https://cloud.tencent.com/developer/article/1784274

1.3.1 关系型

关系型数据库指的是使用关系模型(二维表格模型)来组织数据的数据库。
关系模型可以简单理解为二维表格模型,而一个关系型数据库就是由二维表及其之间的关系组成的一个数据组织。
常见关系型数据库管理系统(ORDBMS):
Oracle, MySql, Microsoft SQL Server, SQLite, PostgreSQL, IBM DB2等。

  • 关系型数据库的优势:
    • 采用二维表结构非常贴近正常开发逻辑(关系型数据模型相对层次型数据模型和网状型数据模型等其他模型来说更容易理解)
    • 支持通用的SQL(结构化查询语言)语句;
    • 丰富的完整性大大减少了数据冗余和数据不一致的问题。并且全部由表结构组成,文件格式一致;
    • 可以用SQL句子多个表之间做非常繁杂的查询;
    • 关系型数据库提供对事务的支持,能保证系统中事务的正确执行,同时提供事务的恢复、回滚、并发控制和死锁问题的解决。
    • 数据存储在磁盘中,安全可靠。
  • 关系型数据库存在的不足: 随着互联网企业的不断发展,数据日益增多,因此关系型数据库面对海量的数据会存在很多的不足。
    • 高并发读写能力差:网站类用户的并发性访问非常高,而一台数据库的最大连接数有限,且硬盘 I/O 有限,不能满足很多人同时连接。
    • 海量数据情况下读写效率低:对大数据量的表进行读写操作时,需要等待较长的时间等待响应。
    • 可扩展性不足:不像web server和app server那样简单的添加硬件和服务节点来拓展性能和负荷工作能力。
    • 数据模型灵活度低:关系型数据库的数据模型定义严格,无法快速容纳新的数据类型(需要提前知道需要存储什么样类型的数据)。

1.3.2 非关系型

非关系型数据库(NOSQL):
非关系型数据库又被称为 NoSQL(Not Only SQL ),意为不仅仅是 SQL。通常指数据以对象的形式存储在数据库中,而对象之间的关系通过每个对象自身的属性来决定,常用于存储非结构化的数据。
常见的NOSQL数据库:
键值数据库:Redis、Memcached、Riak
列族数据库:Bigtable、HBase、Cassandra
文档数据库:MongoDB、CouchDB、MarkLogic
图形数据库:Neo4j、InfoGrid

  • 非关系型数据库的优势:
    • 非关系型数据库存储数据的格式可以是 key-value 形式、文档形式、图片形式等。使用灵活,应用场景广泛,而关系型数据库则只支持基础类型。
    • 速度快,效率高。 NoSQL 可以使用硬盘或者随机存储器作为载体,而关系型数据库只能使用硬盘。
    • 海量数据的维护和处理非常轻松,成本低。
    • 非关系型数据库具有扩展简单、高并发、高稳定性、成本低廉的优势。
    • 可以实现数据的分布式处理。
  • 非关系型数据库存在的不足:
    • 非关系型数据库暂时不提供 SQL 支持,学习和使用成本较高。
    • 非关系数据库没有事务处理,无法保证数据的完整性和安全性。适合处理海量数据,但是不一定安全。
    • 功能没有关系型数据库完善。
    • 复杂表关联查询不容易实现。

2. SQL

参考oracle官网定义
https://www.oracle.com/cn/database/what-is-database/#link2
目前几乎所有的关系数据库都使用 SQL 编程语言来查询、操作和定义数据,进行数据访问控制。SQL 最初于 20 世纪 70 年代由 IBM 开发,当时 Oracle 是一个主要的贡献者,这推动了 SQL ANSI 标准的实施,而 SQL 的兴起也刺激了 IBM、Oracle 和 Microsoft 等公司开始全面扩张。时至今日,虽然 SQL 仍被广泛使用,但是新的编程语言也已经崭露头角。

参考菜鸟教程
https://www.runoob.com/sql/sql-intro.html

  • SQL 指结构化查询语言,全称是 Structured Query Language。
  • SQL 让您可以访问和处理数据库,包括数据插入、查询、更新和删除。
  • SQL 在1986年成为 ANSI(American National Standards Institute 美国国家标准化组织)的一项标准,在 1987 年成为国际标准化组织(ISO)标准。
  • 虽然 SQL 是一门 ANSI(American National Standards Institute 美国国家标准化组织)标准的计算机语言,但是仍然存在着多种不同版本的 SQL 语言。 然而,为了与 ANSI 标准相兼容,它们必须以相似的方式共同地来支持一些主要的命令(比如 SELECT、UPDATE、DELETE、INSERT、WHERE 等等)。 注释:除了 SQL 标准之外,大部分 SQL 数据库程序都拥有它们自己的专有扩展!

2.1 SQL分类

SQL语句的五种分类分别是DQL、DML、DDL、TCL和DCL,下面对SQL语句的五种分类进行列举:

  • 数据库查询语言(DQL)
    数据查询语言DQL基本结构是由SELECT子句,FROM子句,WHERE 子句组成的查询块,简称DQL,Data Query Language。代表关键字为select。

  • 数据库操作语言(DML)
    用户通过它可以实现对数据库的基本操作。简称DML,Data Manipulation Language。代表关键字为insert、delete 、update。

  • 数据库定义语言(DDL)
    数据定义语言DDL用来创建数据库中的各种对象,创建、删除、修改表的结构,比如表、视图、索引、同义词、聚簇等,简称DDL,Data Denifition Language。
    代表关键字为create、drop、alter。
    和DML相比,DML是修改数据库表中的数据,而 DDL 是修改数据中表的结构。

  • 事务控制语言(TCL)
    TCL经常被用于快速原型开发、脚本编程、GUI和测试等方面,它的语句能确保被DML语句影响的表的所有行及时得以更新,简称:TCL,Trasactional Control Languag。代表关键字为begin transaction 、commit、rollback。

  • 指针控制语言(CCL)
    它的语句,像DECLARE CURSOR,FETCH INTO和UPDATE WHERE CURRENT用于对一个或多个表单独行的操作。

  • 数据控制语言(DCL)
    数据控制语言DCL用来授予或回收访问数据库的某种特权,并控制数据库操纵事务发生的时间及效果,对数据库实行监视等,是一种可对数据访问权进行控制的指令,它可以控制特定用户账户对数据表、查看表、预存程序、用户自定义函数等数据库对象的控制权。简称:DCL,Data Control Language。代表关键字为grant、revoke。DCL以控制用户的访问权限为主,GRANT为授权语句,对应的REVOKE是撤销授权语句。

2.2 SQL基础语法

参考https://www.runoob.com/sql/sql-syntax.html
SQL 对大小写不敏感:SELECT 与 select 是相同的。
某些数据库系统要求在每条 SQL 语句的末端使用分号。
SQL的注释:
# 注释内容
-- 注释内容前有一个空格
/*注释内容*/

2.2.1 库相关操作

指定当前使用的字符集(兼容客户端工具字符集,避免数据显示乱码)

set names utf8;

列出数据库:

show databases; show databases [like '%模糊名称%'];

建库:

create database  test_db default character set utf8mb4 collate utf8mb4_0900_ai_ci;

查看建库DDL语句:

show create database test_db;

切换到当前库:

use test_db;

显示当前正在使用的是哪个库:

status;

修改数据库:

http://c.biancheng.net/view/2414.html 在 MySQL 数据库中只能对数据库使用的字符集和校对规则进行修改 ALTER DATABASE [数据库名] {  [ DEFAULT ] CHARACTER SET <字符集名> | [ DEFAULT ] COLLATE <校对规则名>}

删除数据库:

删除数据库后,数据库中存储的所有数据表和数据也将一同被删除,而且不能恢复。因此最好在删除数据库之前先将数据库进行备份。 DROP DATABASE [ IF EXISTS ] <数据库名>

2.2.2 表相关操作

列出当前库中所有表:

show tables; show tables [like %模糊表名%];

建表

CREATE TABLE <表名> ([表定义选项])[表选项][分区选项]; 其中,[表定义选项]的格式为: <列名1> <类型1> [,…] <列名n> <类型n> CREATE TABLE 命令语法比较多,其主要是由表创建定义(create-definition)、表选项(table-options)和分区选项(partition-options)所组成的。  CREATE TABLE `user_infos` (   `id` bigint unsigned NOT NULL AUTO_INCREMENT,   `name` longtext,   `gender` longtext,   `hobby` longtext,   `created_at` datetime(3) DEFAULT NULL,   `updated_at` datetime(3) DEFAULT NULL,   `deleted_at` datetime(3) DEFAULT NULL,   PRIMARY KEY (`id`),   KEY `idx_user_infos_deleted_at` (`deleted_at`) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;  CREATE TABLE statistics (     id BIGINT NOT NULL AUTO_INCREMENT,     class_id BIGINT NOT NULL,     average DOUBLE NOT NULL,     PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

查看表字段信息:

desc tb_name;

查看建表语句:

show create table tb_name;

插入记录

insert into tb_name (column1, column2, ...) values (val1, val2, ...); INSERT INTO ... SELECT 

查询记录

select */[distinct] columnN from tb_name1[,tb_name2...] [where ...[and...][or...][like...][in...][between...and] [group by...][having ...][order by] [limit ...]]

更新记录

update tb_name set column1=val1, column2=val2, ... where ...

删除记录

delete from tb_name; truncate table tb_name; 如果我们仅仅需要删除表内的数据,但并不删除表本身,那么我们该如何做呢? 请使用 TRUNCATE TABLE 语句: TRUNCATE TABLE table_name

改表结构

其语法格式如下: ALTER TABLE <表名> [修改选项]  修改选项的语法格式如下: { ADD COLUMN <列名> <类型> | CHANGE COLUMN <旧列名> <新列名> <新列类型> | ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT } | MODIFY COLUMN <列名> <类型> | DROP COLUMN <列名> | RENAME TO <新表名> | CHARACTER SET <字符集名> | COLLATE <校对规则名> }

改字段

例如: ALTER TABLE tb_emp1 CHANGE col1 col3 CHAR(30); 例如: ALTER TABLE tb_emp1 MODIFY col2 VARCHAR(30);

加字段

ALTER TABLE <表名> ADD <新字段名><数据类型>[约束条件]; 例如: ALTER TABLE student ADD age INT(4); ALTER TABLE student ADD stuId INT(4) FIRST;  ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] AFTER <已经存在的字段名>; ALTER TABLE student ADD stuno INT(11) AFTER name;

删字段

例如: ALTER TABLE tb_emp1 DROP col2;

改表名

ALTER TABLE <旧表名> RENAME [TO] <新表名>; 例如: alter table product rename products;

改表字符集

ALTER TABLE 表名 [DEFAULT] CHARACTER SET <字符集名> [DEFAULT] COLLATE <校对规则名>; 例如: ALTER TABLE tb_students_info CHARACTER SET gb2312  DEFAULT COLLATE gb2312_chinese_ci;

2.2.3 连接查询相关

A inner join B 取交集。
A left join B 取 A 全部,B 没有对应的值为 null。
A right join B 取 B 全部 A 没有对应的值为 null。
A full outer join B 取并集,彼此没有对应的值为 null。
对应条件在 on 后面填写。

  • inner join等同于平时常用的多表关联
SELECT column_name(s) FROM table1 [INNER] JOIN table2 ON table1.column_name=table2.column_name; 等同于 SELECT column_name(s) FROM table1, table2 where table1.column_name=table2.column_name;
  • left join
LEFT JOIN 关键字从左表(table1)返回所有的行,即使右表(table2)中没有匹配。如果右表中没有匹配,则结果为 NULL。 SELECT column_name(s) FROM table1 LEFT [OUTER] JOIN table2 ON table1.column_name=table2.column_name;
  • right join
RIGHT JOIN 关键字从右表(table2)返回所有的行,即使左表(table1)中没有匹配。如果左表中没有匹配,则结果为 NULL。 SELECT column_name(s) FROM table1 RIGHT [OUTER[ JOIN table2 ON table1.column_name=table2.column_name;
  • full join
FULL OUTER JOIN 关键字只要左表(table1)和右表(table2)其中一个表中存在匹配,则返回行. FULL OUTER JOIN 关键字结合了 LEFT JOIN 和 RIGHT JOIN 的结果。

2.2.4 union 和 union all

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
默认地,UNION 操作符选取不同的值。如果允许重复的值,请使用 UNION ALL。

2.2.5 like

LIKE 子句中使用百分号 %字符来表示任意字符,类似于UNIX或正则表达式中的星号 *。
如果没有使用百分号 %, LIKE 子句与等号 = 的效果是一样的。

SQL 提供了四种匹配方式。 %:表示任意 0 个或多个字符。可匹配任意类型和长度的字符,有些情况下若是中文,请使用两个百分号(%%)表示。 _:表示任意单个字符。匹配单个任意字符,它常用来限制表达式的字符长度语句。 []:表示括号内所列字符中的一个(类似正则表达式)。指定一个字符、字符串或范围,要求所匹配对象为它们中的任一个。 [^] :表示不在括号所列之内的单个字符。其取值和 [] 相同,但它要求所匹配对象为指定字符以外的任一个字符。 查询内容包含通配符时,由于通配符的缘故,导致我们查询特殊字符 “%”、“_”、“[” 的语句无法正常实现,而把特殊字符用 “[ ]” 括起便可正常查询。

2.2.5.1 like导致索引失效

使用like %通配符时常常会引起索引失效的问题(%放在开头会引起索引失效)。
其实使用覆盖索引(覆盖索引是select的数据列只用从索引中就能够取得,不必读取数据行,换句话说查询列要被所建的索引覆盖。索引的字段不只包含查询列,还包含查询条件、排序等),也没有解决索引失效的问题,使用覆盖索引后还是会在索引文件中进行全表扫描,也就是没有使用到索引的有序性进行二分查找,而是直接扫描索引文件,他的type只是index,而不是ref或者range。使用覆盖索引在这里只是避免了回表。

真要提高搜索效率还得用 全文索引 或 es
参考 https://blog.csdn.net/mrzhouxiaofei/article/details/79940958
MySQL 5.6 及以后的版本,MyISAM 和 InnoDB 存储引擎均支持全文索引;
只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。
测试或使用全文索引时,要先看一下自己的 MySQL 版本、存储引擎和数据类型是否支持全文索引。

create table fulltext_test (     id int(11) NOT NULL AUTO_INCREMENT,     content text NOT NULL,     tag varchar(255),     PRIMARY KEY (id),     FULLTEXT KEY content_tag_fulltext(content,tag)  // 创建联合全文索引列 ) ENGINE=MyISAM DEFAULT CHARSET=utf8;  create fulltext index content_tag_fulltext on fulltext_test(content,tag);  alter table fulltext_test add fulltext index content_tag_fulltext(content,tag);  drop index content_tag_fulltext on fulltext_test;  select * from fulltext_test  where match(content,tag) against('xxx xxx'); 注意: match() 函数中指定的列必须和全文索引中指定的列完全相同,否则就会报错,无法使用全文索引,这是因为全文索引不会记录关键字来自哪一列。如果想要对某一列使用全文索引,请单独为该列创建全文索引。

2.2.6 group by

GROUP BY 语句根据一个或多个列对结果集进行分组。
在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。
select子句中的列名必须为分组列或列函数,列函数对于group by子句定义的每个组返回一个结果。
group by 子句中列出的每个列都必须是检索列或有效的表达式,但不能是聚集函数,如果在select 中使用表达式,则必须在group by 子句中指定相同的表达式,不能使用别名。

SELECT column_name, function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name;

2.2.6.1 使用 WITH ROLLUP

WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计(SUM,AVG,COUNT…)。
数据库篇·第4章[1]·数据库基础
数据库篇·第4章[1]·数据库基础

2.2.7 having 过滤分组

可应用限定条件进行分组,以便系统仅对满足条件的组返回结果。因此,在GROUP BY子句后面包含了一个HAVING子句。HAVING类似于WHERE(唯一的差别是WHERE过滤行,HAVING过滤组)HAVING支持所有WHERE操作符。

例如,查找雇员数超过2个的部门的最高和最低薪水: SELECT dept ,MAX(salary) AS MAXIMUM ,MIN(salary) AS MINIMUM FROM STAFF GROUP BY dept HAVING COUNT(*) > 2 ORDER BY dept  例如,查找雇员平均工资大于3000的部门的最高薪水和最低薪水: SELECT dept,MAX(salary) AS MAXIMUM,MIN(salary) AS MINIMUM FROM STAFF GROUP BY dept HAVING AVG(salary) > 3000 ORDER BY dept

索引
create index
drop index

2.2.5 约束

SQL 约束用于规定表中的数据规则。
如果存在违反约束的数据行为,行为会被约束终止。
约束可以在创建表时规定(通过 CREATE TABLE 语句),或者在表创建之后规定(通过 ALTER TABLE 语句)。
在 SQL 中,我们有如下约束:

  • NOT NULL – 指示某列不能存储 NULL 值。
  • UNIQUE – 保证某列的每行必须有唯一的值。
  • PRIMARY KEY – NOT NULL 和 UNIQUE 的结合。 确保某列(或两个列多个列的结合)有唯一标识,有助于更容易更快速地找到表中的一个特定的记录。 拥有自动定义的 UNIQUE 约束。 每个表可以有多个 UNIQUE 约束,但是每个表只能有一个 PRIMARY KEY 约束。
  • FOREIGN KEY – 主键,保证一个表中的数据匹配另一个表中的值的参照完整性。 FOREIGN KEY 约束用于预防破坏表之间连接的行为。 FOREIGN KEY 约束也能防止非法数据插入外键列,因为它必须是它指向的那个表中的值之一。
  • CHECK – 保证列中的值符合指定的条件。限制列中的值的范围。 如果对单个列定义 CHECK 约束,那么该列只允许特定的值。
  • DEFAULT – 规定没有给列赋值时的默认值。
CREATE TABLE Persons (     ID int NOT NULL,     LastName varchar(255) NOT NULL,     FirstName varchar(255) NOT NULL,     Age int ); ALTER TABLE Persons MODIFY Age int NOT NULL; -- 添加not null ALTER TABLE Persons MODIFY Age int NULL; -- 去除not null
CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName) ); --命名约束 ALTER TABLE Persons ADD UNIQUE (P_Id); ALTER TABLE Persons ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName); ALTER TABLE Persons DROP INDEX uc_PersonID; -- 撤销unique ALTER TABLE Persons DROP CONSTRAINT uc_PersonID;
CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), PRIMARY KEY (P_Id) ); CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName) ); -- 命名主键且由多个字段组成主键 ALTER TABLE Persons ADD PRIMARY KEY (P_Id); ALTER TABLE Persons ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName); ALTER TABLE Persons DROP PRIMARY KEY; --撤销主键
CREATE TABLE Orders ( O_Id int NOT NULL, OrderNo int NOT NULL, P_Id int, PRIMARY KEY (O_Id), FOREIGN KEY (P_Id) REFERENCES Persons(P_Id) ); CREATE TABLE Orders ( O_Id int NOT NULL, OrderNo int NOT NULL, P_Id int, PRIMARY KEY (O_Id), CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id) REFERENCES Persons(P_Id) ); -- 命名外键  ALTER TABLE Orders ADD FOREIGN KEY (P_Id) REFERENCES Persons(P_Id); ALTER TABLE Orders ADD CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id) REFERENCES Persons(P_Id);  ALTER TABLE Orders DROP FOREIGN KEY fk_PerOrders;
CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), CHECK (P_Id>0) ); CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes') );  ALTER TABLE Persons ADD CHECK (P_Id>0); ALTER TABLE Persons ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes');  ALTER TABLE Persons DROP CHECK chk_Person;
CREATE TABLE Persons (     P_Id int NOT NULL,     LastName varchar(255) NOT NULL,     FirstName varchar(255),     Address varchar(255),     City varchar(255) DEFAULT 'Sandnes' ); CREATE TABLE Orders (     O_Id int NOT NULL,     OrderNo int NOT NULL,     P_Id int,     OrderDate date DEFAULT GETDATE() );  ALTER TABLE Persons ALTER City SET DEFAULT 'SANDNES'; ALTER TABLE Persons ALTER City DROP DEFAULT;

部分转自互联网,侵权删除联系

赞(0) 打赏
部分文章转自网络,侵权联系删除b2bchain区块链学习技术社区 » 数据库篇·第4章[1]·数据库基础求职学习资料
分享到: 更多 (0)

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

b2b链

联系我们联系我们