数据库知识整理（一）

一、相关概念

1. Database：以某种有组织的方式存储的数据集合
2. DBMS：数据库管理系统，管理数据库的软件（以为数据库是直接保存在磁盘等位置的，无法直接访问所以需要DBMS）
3. DBS：database system=DS+DBMS
4. DBA：Database Administrater

• Server Information：显示客户机和被链接的服务器的状态
• Service Control：允许停止和启动MySQL以及指定服务器特性
• User Adimistration：用来定义MySQL用户、登录和权限
• catalogs：列出可用数据库并允许创建数据库和表
  MySQL Query Browser：图形交互客户机，编写和执行MySQL命令

5. 数据库分类
   
6. 模式（schema）：关于数据库和表的布局以及特性的信息（可以用来被描述数据库中特定表和整个数据库）
   表中有一些特性关于表中数据如何存储，如：可存储什么样的数据、数据如何分解、各部分信息如何命名等
7. column：在编写时要注意数据分解问题，降低关联度，才可能利用特定的列对数据进行过滤和排序
8. row：一个record
9. datatype：数据类型，每列一种，限制该列中存储的数据（限制类型，便于排序，优化磁盘）
10. primary key：能够唯一区分表中的每行，大多数保证每个表有一个key

• 构成key条件：任意两行不具有相同key值；每行都必须具有key，不允许其中有的行是空值
• 也可用多列：上述条件应用到每列；列值组合也唯一
• key使用的习惯：不更新key值；不重用key的值；不在key列中使用可能会更改的值

9. MySQL工具
   
10. SQL通用但并非完全可移植，是一种约束但不强制
    

二、连接数据库

交互方式：

1. 客户端认证，连接服务器认证身份

mysql -hPwp

mysql -h127.0.01 -P1120 -uzora -pchair0666
//h:数据库IP
//P：端口号
//u：用户名
//p：密码

2. 发送SQL命令
3. 服务器接受SQL指令，然后处理SQL指令并返回操作结果
4. 客户端接受并显示结果
5. 由服务器并发控制、需断开链接（三种指令：exit、quit和\q)，释放资源

三、使用数据库

选择数据库

在最初链接上数据库时，没有任何数据库打开供你使用，在你能执行任意数据库操作之前，需选择一个数据库：

USE DATABASENAME;
//该语句不产生任何结果，依赖于使用的客户机显示某种形式的通知

必须先打开数据库，才可以读取其中的数据

了解数据库和表

数据库、表、列、用户、权限等信息被存储在数据库和表中（MySQL使用MySQL来存储这些信息，不过，内部的一些表一般不直接访问，可用SHOW命令来显示这些信息，MySQL从内部表中提取这些信息）

show databases;返回可用数据库的一个列表
show tables;返回“当前选择的数据库”的“可用”列表
show columns from customers（表名）;对每个字段返回1行，行中包括字段名，数据类型，是否允许null键信息和其他信息
show status;用于显示广泛的服务器状态信息
show create database ;
show create table;
show grants;用于显示授予用户（所有用户或特定用户）的安全权限
show errors;用来显示服务器错误信息（没有为空）
show warnings;用来显示服务器警告信息(没有为空）

自动增量：每列有一个唯一值，若无手动分配，MySQL自动分配，但这样必须要记住最后一次的值，如果需要必须子啊用create语句创建表时，将它作为表定义的组成部分。

库操作

1.新增数据库

基本语法：create database +数据库名称+【库选项】;
其中，库选项是用来约束数据库的，为可选项（有默认值），共有两种，分别为：

• 字符集设定：charset/character set+具体字符集，用来表示数据存储的编码格式，常用字符集包括GBK和UTF8等
• 校对集设定：collate+具体校对集，表示数据比较的规则，其依赖字符集

create database TBL_ERROR_CODE charset utf8;

其中，数据库的名字不能用关键字（已经被占用的字符，例如 update 和 insert 等）或者保留字（将来可能会用的，例如 access 和 cast 等）。
如果非要使用数据库的关键字或者保留字作为数据库名称，那么必须用反引号将其括起来，例如：

create database `access` charset utf8;

如果还想使用中文作为数据库的名称，那就得保证数据库能够识别中文（强烈建议不要用中文命名数据库的名称），例如：

-- 设置中文名称的方法，其中 gbk 为当前数据库的默认字符集
set names gbk;
create database 北京 charset gbk;

2. 查询数据库

查看全部：show databases;
查看部分（模糊查询）：show databases like 'pattern';
其中pattern是匹配模式，有两种，分别为：

• %：表示匹配多个字符；
• _：表示匹配单个字符。
  此外，在匹配含有下划线_的数据库名称的时候，需要在下划线前面加上反斜线\_进行转义操作。

show databases like 'TBL%';匹配所有以TBL开头的数据库

查看数据库的创建：show create database+数据库名称;
在这里，查看的结果有可能与咱们书写的 SQL 语句不同，这是因为数据库在执行 SQL 语句之前会优化 SQL，系统保存的是优化后的结果。

3. 更新数据库

在这里，需要注意：数据库的名字不可以修改。（一旦创建不能修改？？）
数据库的修改仅限库选项，即字符集和校对集（校对集依赖字符集）。
基本语法：

alter database + 数据库名称 + 【库选项】;

charset/character set[=] 字符集;
collate [=] 校对集;

示例：alter database TBL_ERROR_CODE charset gbk;表示修改此数据库的字符集为gbk.

4.删除数据库

drop database + 数据库名称;

这里，需要注意：在删除数据库之前，应该先进行备份操作，因为删除为不可逆操作，所以不要随意删除数据库。

表操作

1.新增表

• 使用具有交互式创建和管理表的工具（实际上使用的也是MySQL语句，但不是用户编写的界面工具，会自动生成并执行相应的MySQL语句）
• 表也可以直接使用MySQL语句操纵
  基本语法：

建表基础：1.新表名字 2.表列的名字和定义，逗号分隔
create table [if not exisis]+ 表名(
  fieldname + datatype,
  ...
  fieldname + datatype
  )[表选项];

其中，if not exists表示
如果表名不存在，就执行创建代码；如果表名存在，则不执行创建代码。
表选项则是用来控制表的表现形式的，共有三种，分别为：
字符集设定：charset/ character set+ 具体字符集，用来表示数据存储的编码格式，常用的字符集包括GBK和UTF8等。
校对集设定：collate+ 具体校对集，表示数据比较的规则，其依赖字符集。
存储引擎：engine+具体存储引擎，默认为InnoDB，常用的还有MyISAM.
如何处理现有的表？

1. 在创建新表时，指定的表名必须不存在，否则将出错，如果要防止意外覆盖已有的表，SQL要求首先手工删除该表，然后重建。
2. 如果仅想在表不存在时创建，在表名后给出if not exists这样不仅检查已有表的模式是否与你打算创建的表模式相匹配，他只是查看表名是否存在，并在表名不存在的时候创建该表。

由于任何表都归属于某个数据库，因此在创建表的时候，都必须先指定具体的数据库。在这里，指定数据库的方式有两种，分别为：

• 显式的指定表所属的数据库，示例:

create table if not exists test.student(
	name varchar(10),
	age int,            /* 整型不需要指定具体的长度 */
	grade varchar(10)	/* 最后后一行，不需要加逗号 */
)charset utf8;

• 隐式的指定表所属的数据库，示例:

use test;				/* use + 数据库名称，表示切换到指定的数据库，这句命令其实不加分号也可以，但不建议这么做 */
create table if not exists student(
	name varchar(10),
	age int,            /* 整型不需要指定具体的长度 */
	grade varchar(10)	/* 最后后一行，不需要加逗号 */
)charset utf8;

使用NULL值

每个表列或者是NULL列或者是NOT NULL列，这种状态在创建表的时候由表的定义决定。声明NOT NULL会阻止插入列为空的值，如果试图插入没有值的列会返回错误且插入失败，不指定NOT NULL默认为NULL。

create table customers(
 cust_id int NOT NULL AUTO_INCREMENT,//指定非空且自动增量
 cust_name char(50) NOT NULL,
 primary key(cust_id)//指定cust_id是主键
 )enging = InnoDB;

主键
若使用单个列，值必须唯一，如果使用多个列，多个列的组合值必须不唯一

primary key (列名，列名)创建主键

主键必须非空，可以在创建表的时候定义，也可以在创建之后定义。
auto_increment
每列增加一行自增，每个表只允许一个列这样写，而且它必须被索引（通常使它成为主键）
覆盖auto_increment可以使用自己的值，只要它是唯一的。

select last_insert_id()//获得最后一个auto_increment的值

quantity int NOT NULL default 1//未给出数量的情况下默认为1

⚠️ mysql不允许使用函数作为默认值，只支持常量，但是其他DBMS大多支持
⚠️数据库开发人员使用默认值而不是NULL列，特别是对计算或者数据分组的列
引擎类型
DBMS都有管理和处理数据的内部引擎，多数时候该引擎都隐藏在DBMS内部不需要关注，但是MySQL与其他DBMS不同，他有多个内部引擎，因为不同的引擎各有不同的功能和特性。为不同的任务选择正确的引擎能获得很好的功能和灵活性，省略ENGING语句，使用默认引擎。

⚠️引擎类型可以混用
⚠️外键不能跨引擎

• 即使用一个引擎的表不能引用具有使用不同引擎的表的外键

2. 查询表

查看全部：show tables;
查看部分（模糊查询）：show tables like 'pattern';
其中pattern是匹配模式，有两种，分别为：

• %：表示匹配多个字符；
• _：表示匹配单个字符。
  此外，在匹配含有下划线_的表名称的时候，需要在下划线前面加上反斜线\_进行转义操作。
  查看表的创建：show create table + 表名;
  在这里，咱们也可以用\g和\G代替上述语句中的;分号，其中\g等价于分号，\G则在等价于分号的同时，将查的表结构旋转90度，变成纵向结构。
  查看表中的字段：desc/describe/show columns from + 表名;

3.更新表

理想状态，表中存储数据后，不应对其更新，所以表在设计时应花大量时间考虑以便后期不对表进行大的改动
在这里，需要注意：表的修改，分为修改表本身和修改表中的字段。

• 修改table本身：
  • 修改表名：rename table 旧表名 to 新表名;(可以有多个）
  • 修改表选项：alter table + 表名 + 表选项[=] + 值;
• 修改表中的字段，新增、修改、重命名和删除
  • 新增字段：alter table + 表名 + add + [column] + 字段名 + 数据类型 + [列属性][位置];
    - 其中，位置表示此字段存储的位置，分为first（第一个位置）和after + 字段名（指定的字段后，默认为最后一个位置）.
    - 示例：alter table student add column id int first;
  • 修改字段：alter table + 表名 + modify + 字段名 + 数据类型 + [列属性][位置];
    - 其中，位置表示此字段存储的位置，分为first（第一个位置）和after + 字段名（指定的字段后，默认为最后一个位置）.
    - 示例：alter table student modify name char(10) after id;
  • 重命名字段：alter table + 表名 + change + 旧字段名 + 新字段名 + 数据类型 + [列属性][位置];
    - 其中，位置表示此字段存储的位置，分为first（第一个位置）和after + 字段名（指定的字段后，默认为最后一个位置）.
    - 示例：alter table student change grade class varchar(10);
  • 删除字段，基本语法：alter table + 表名 + drop+ 字段名;
    - 示例：alter table student drop age;

注意：如果表中已经存在数据，那么删除该字段会清空该字段的所有数据，而且不可逆，慎用。
⚠️使用alter应该在改动之前做个完整的备份（模式和数据的备份）

定义外键

alter table orderitems 
add constant fk__orderitems_orders
foreign key (order_num) 
references orders (order_num);

复杂的表结构需要以下步骤：

1. 用新列布局创建一个新的表
2. insert select语句从旧表复制数据到新表，如有必要需要转换函数和计算字段
3. 检查包含所需数据的新表
4. 重命名旧表（if确定，可删除）
5. 根据所需重新创建触发器，存储过程，索引和外键

4. 删除表

在这里，需要注意：此删除为不可逆操作，希望大家谨慎使用。

/** 可以一次删除多张表 */
drop table + 表1, 表2 ... ;