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);
当前名称:56数据库1_DCL_DDL_constrait_view
标题网址:http://myzitong.com/article/pcpdpg.html