56数据库1_DCL_DDL_constrait_view

目录

SQL：... 1

DCL：... 2

DDL：... 2

PRIMARY KEY：... 6

INDEX：... 6

constraint：... 6

view：... 8

数据类型：... 10

按照数据结构来组织、存储、管理数据的仓库；

分类：

按数据模型分：

网状数据库；

层次数据库；

关系型数据库；

oracle；

sqlserver；

DB2；

sqlite，c++，手机中的存储；

cassandra、hbase，column store；

mongodb，document store；

redis，key-value；

elasticsearch（大型分布式）、solr（单机玩）、splunk，search engine；

hive，relational DBMS，数据仓库用于数据分析；

MySQL：

是一种关系型数据库管理软件，支持网络访问，默认3306port；

通信使用mysql协议；基于TCP；

MySQL5.0，里程碑;

5.5起默认的SE为InnoDB，行级锁；

连接字符串：

"server=127.0.0.1;uid=root;pwd=123456;database=test"

row，行，record，元组；

column，列，field；

mysql> show processlist;   #观察连接，与权限有关

SQL：

structured guery language，结构化查询语言；

1987年被ISO组织标准化；

所有主流的关系型数据库都支持SQL，nosql也有很大一部分支持SQL；

SQL语言分为：

DDL，定义，负责数据库定义、数据库对象定义，create、alter、drop；

DML，操作，负责对数据库对象的操作，CRUD；

DCL，控制，负责数据库权限访问控制，grant、revoke；

TCL，事务控制语言，负责处理ACID事务，commit、rollback；

SQL语句大小写不敏感，末尾应用分号结束；

]# mysql -uroot -p < test.sql

DCL：

mysql> grant all on test.* to 'jowin'@'%' identified by 'jowin';   #mysql.user中无此用户，赋权并创建用户，jowin/jowin

mysql> revoke all on test.* from 'jowin';

DDL：

mysql> drop user 'jowin';   #删除用户，慎用；开发设计中，一般是在逻辑上删除（假删）

CREATE DATABASE IF NOT EXISTS gogs CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;   #CHARACTER SET指定字符集，表中字符集可与库中不一样，表中不写字符集则默认用库中定义的；utf8mb4，5.5.3以上版本支持，utf8的扩展，支持4byte utf8；COLLATE，指定字符集排序规则，用来作字符串比较的

drop database IF EXISTS gogs;

CREATE TABLE `employees` (

  `emp_no` int(11) NOT NULL,

  `birth_date` date NOT NULL,   #date，特殊的数值

  `first_name` varchar(14) NOT NULL,

  `last_name` varchar(16) NOT NULL,

  `gender` enum('M','F') NOT NULL,

  `hire_date` date NOT NULL,

  PRIMARY KEY (`emp_no`)   #约束，另UNIQUE KEY ' 'ln' ('emp_no')

) ENGINE=InnoDB DEFAULT CHARSET=utf8;   #表引擎可单独设置

创建表：

定义field、定义约束、定义SE，storage engine存储引擎；

NOT NULL，插入记录后，不允许字符为空；

反引号为标准的名称，不是关键字；

建库、建表时，用工具建模，可转为建表语句；

如何数据库建模，见CMDB项目中；

mysql> desc employees;   #查看列信息

例：

设计一张表，记录登录账户；

应存储用户的姓名、登录名、密码；

使用navicat mysql工具：

表-->新建表

注：

password，单身加密，不可逆，双向加密很少用，建议128，散列越长越好，单身散列值；

id，点“主键”按钮，可上移到第1行，少用联合主键；

关注的重点，是字段设计符合业务需求；

至少2张表关联，很少有独立的表；

id，下方勾选，自动递增和无符号；

id，选项，自动递增：1；

登录名不允许重复：

点“索引”，名：ln，栏位：loginname，索引类型：Unique，索引方法：BTREE；

唯一键约束；

左边，右键"reg"表-->对象信息-->DDL，可查看建表语句：

mysql> desc reg;

+-----------+------------------+------+-----+---------+----------------+

| Field     | Type             | Null | Key | Default | Extra          |

+-----------+------------------+------+-----+---------+----------------+

| id        | int(11) unsigned | NO   | PRI | NULL    | auto_increment |

| loginname | varchar(48)      | NO   | UNI | NULL    |                |

| name      | varchar(64)      | YES  |     | NULL    |                |

| password  | varchar(128)     | NO   |     | NULL    |                |

+-----------+------------------+------+-----+---------+----------------+

4 rows in set (0.00 sec)

如果表里已有数据，再添字段，事就大了；若几十条记录还很快，若几百万行记录，一晚上估计都好不了，解决：

设计时提前预留2个字段，空间换时间，如reversed1 varchar,reversed2 varchar；

新增新表，迁移数据过去；

PRIMARY KEY：

表中一列或多列（多列少做），组成唯一的key，即通过这一个或者多个列能唯一的标识一条记录；

主键的列，不能包含空值NULL；

主键往往设计为整型、长整型、且自增AUTO_INCREMENT；

表中可以没有主键，但一般设计表中都会有主键；

字符串作为主键没整型效率高；

INDEX：

可看作是大字典的目录，为了快速检索用；

用空间换时间，显著提高查询效率；

可对一列或多列设索引；

主键索引，主键会自动建立主键索引，主键本身就是为了快速定位唯一记录的；

唯一索引，表中的索引列组成的索引必须唯一，但可为NULL空，非空值必须唯一；

普通索引，没有唯一性要求，就是建了一个字典的目录而已；

索引有副作用，增、删、改都会效率低下，可能系统慢的原因就是索引；

适用于r多w少，不适用于w多r少，OS大量IO在等待写；

constraint：

UNIQUE唯一键约束，定义了唯一键索引就定义了唯一键约束；

PRIMARY KEY主键约束，定义了主键，就定义了主键约束；

FOREIGN KEY外键约束，为保证数据完整性、一致性、杜绝数据冗余、数据讹误，能少用就少用，不宜过多滥用；

注：

FOREIGN KEY：

在B中的列关联A中的主键，表B中的列就是外键；

如果在表B插入一条数据，B的外键列插入了一个值，这个值必须是表A中存在的主键值；

修改表B的外键值也是这样，外键值同样要在表A中存在；

如果表A要删除一条记录，那么就等于删除这个主键，如果表B中引用到了这个主键，就必须先删除表B中引用的这个主键的记录，然后才能删除表A的记录，否则删除失效；

修改表A的主键，由于主键的唯一性，修改的主键相当于插入新主键，那么表B引用过这个主键，就阻止表A的主键修改，必须删除表B的相关记录后，才可修改表A的主键；

set foreign_key_checks=0;   #禁用外键约束

例：

建login表

在login表上建外键，fk_login_reg，udi，test，reg，id，删除时CASCADE级联；

CASCADE级联，危险，若在主表中删除了主键的记录，其它与之关联的表内容也将删除，能不删就不删；

NOACTION，无操作；

login表的uid必须要和reg表的id对应，如类型、长度、无符号等，否则创建不成功；

当删除reg表中的主键（第2行记录）时，会级联删除login表中相关的第2条记录；

view：

视图，也称虚表，看起来像表，它是由查询语句生成的，可通过视图进行CRUD操作；

最好用视图作查询，select语句过滤形成视图，select * from VIEW_NAME；

CRUD用视图要慎用；

作用：

简化操作，将复杂查询语句定义为视图，可简化查询；

数据安全，视图可只显示真实表的部分列，或计算的结果，隐藏真实表的数据；

例：

工资表，只查当月工资，其它列不允许看；

登录，只看登录的用户名，其它列不允许看；

例：

employees表，emp_no为PK；

salaries表，emp_no和from_date联合PK；

视图，新建视图，视图创建工具，将指定表托入右侧空白处，勾选要显示的字段，下方可修改语句，键入视图名v_salary；

左侧，test1库，视图，双击v_salary即可看到查询结果；

新建查询，select * from v_salary;

数据类型：

tinyint，1字节，bool或boolean就是tinyint，0假，非0真（包括负数），带符号范围-128-127，无符号范围0-255；

snallint，2字节，带符号-32768-32767，无符号0-65535；

int，整型，4字节，带符号-2147483648-2147483647，无符号0-4294967295-42亿；

bigint，长整型8字节，用作id，int足够；

float，单精度，精确到大约7位小数位；

double，双精度，精确到大约15位小数位

DATE、DATETIME、TIMESTAMP；

char(M)，固定长度，M为长度即字符个数，范围0-255；

varchar(M)，变长字符串，M为最大列长度，不能突破65535最大字节数；

text，大文本，最大长度65535个字符；

BLOB，大字节，65535字节的BLOG;

注：

char(M)、varchar(M)、text、BLOB，设计表中讲；

LENGTH函数返回字节数；

而char()、varchar()中的M是字符数限制；

char()可将字符串变成等长的，空间换时间，效率略高；

varchar()，变长，省了空间，存后参差不齐，当表中已有几百万行记录，这时要对前面某长记录作修改，恰好引起了长度的变化，会产生大量IO，修改的行之后的记录都要挪动，将导致表不对外工作；

例：

在连接上，右键“运行SQL文件”，选择文件，开始

模型：

UML，统一建模语言；

将模型转化为DB的table，转化为py的class；

建模工具：powerdesigner、rose（IBM）；