数据库基础

数据库

概念

按照数据结构(逻辑结构,物理结构)来组织,存储,挂你数据的仓库

数据库的数据模型才是其核心和基础

分类

按照数据模型分类:网状数据库,层次数据库,关系型数据库

层次数据库

以树型结构表示实体以及之间的联系,关系支支持一对多.代表数据库IBM IMS

网状数据库

能够直接描述客观世界,可以表示实体间多种复杂关系,而这是层次数据模型无法做到的.一个结点可以有多个父节点,节点之间支持可以多对多关联

通用电气1964年开发的IDS,只能GE自己用

关系型数据库

对每一列的数据有数据类型的约束

关系型数据库,把数据存在硬盘中

非关系型数据库,把数据放在内存中

使用行列组成的二维表来组织数据和关系.

基于关系模型构建的数据库系统称为RDBMS(relational database system)

MySQL

MariaDB

mongoDB 文档型数据库

redis 微博 key-value形式

cassandra(国内不多)

elasticsearch

widecolumn store列存数据库,大数据领域有用

hbase

hive

sqlite

SQL

SQL是结构化查询语言Structured Query Language.

SQL语句分类

DDL数据定义语言,负责数据库定义,数据库对象定义,由CREATE,ALTER和DROP三个语法组成

DML数据操作语言,负责对数据库对象的操作,CRUD增删改查

DCL数据控制语言,负责数据库权限访问控制,由GRANT和REVOKE两个指令组成

TCL事务控制语言,负责处理ACID事务,支持commit,rollback指令

SQL语句大小写不敏感 规范,要么全小写,要么关键字全大写

SQL语句末尾应该使用分号结束.

DCL

GRANT授权,REVOKE撤销

*为通配符,指代任意库或者任意表.*.*表示所有库的所有表;employees.*表示employees库下所有表

%为通配符,他是SQL语句的通配符,匹配任意长度字符串.

DDL

删除用户(慎用)

DROP USER agsol;

创建数据库

库是数据的集合,所有数据按照数据模型组织在数据库中.

CHARACTER SET指定字符集,utf8md4是utf8的扩展,支持4字节的utf8mb4.需要MySQL5.5.3+

COLLATE指定字符集的校对规则,用来做字符串的比较的

删除数据库

DROP DATABASE IF EXISTS gogs;

创建表

表分行和列,MySQL是行存数据库,数据是一行行存的,列必须固定有多少列.

创建表如果不指定字符集,会默认使用数据库当前字符集

行Row,也称记录Record,元组.

列Column,也称为字段Field

反引号标注的名称,被认为是非关键字

DESC

查看列信息

`{DESCRIBE|DESC} tbl_name[col_name|wild]

PRIMARY KEY 主键

表中一列或者多列组成的唯一的key,也就是通过这一个或者多个列能唯一标识一条记录.

主键的列不能包含空值null,主键往往设置为整型,长整型,且自增AUTO_INCREAMENT(内容增加而增加,减少而不动)

表中可以没有主键,但是,一般表设计中,往往会有主键.

索引Index

是一个类似字典的方式

用空间换时间,显著提高查询效率.有可能会造成系统卡顿.

可以对一列或者多列字段设定索引

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

唯一键索引unique index:表中的索引列组成的索引必须唯一,但可以为空,非空值必须唯一.

普通索引:没有唯一性要求,就是建了一个字典目录而已.

普通索引重复不重复无所谓,A在第几行或第几行出现过

唯一键索引.不能重复,A只在第几行出现过

主键索引.不能重复,A只在第几行出现过

约束constrain

  • unique约束 唯一键约束

    定义了唯一键索引,就定义了唯一键约束.

  • primary key约束

    定义了主键,就定义了主键约束.

  • 外键约束 foreign key

    外键,在表B中的列,关联表A中的主键,表B中的列就是外键.

    不易过多使用

    如果B表中某一列关联表A中的主键,那么B键的这一列就是A的外键

    外键可设外键约束

    设置外键约束之后,若想删除表A主键中数据,需先删除表B中的引用,若表A想要更改主键中信息,需要先删除表B的相关记录后,才可以修改表A的主键.

    外键约束,为了保证数据完整性,一致性,杜绝数据冗余,数据讹误.

视图view

视图,也称虚表,看起来像表,实际由查询语句生成的,可以通过视图进行CRUD操作

作用:

常用于查询,不要用于增删改

简化操作,将复杂查询SQL语句定义为视图,可以简化查询.

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

数据类型

数值类型
类型 大小 范围(有符号) 范围(无符号) 用途
TINYINT 1字节 (-128，127) (0，255) 小整数值,bool或boolean,就是tinyint,0表示假,非0表示真
SMALLINT 2 字节 (-32 768，32 767) (0，65 535) 大整数值
MEDIUMINT 3 字节 (-8 388 608，8 388 607) (0，16 777 215) 大整数值
INT或INTERGER 4 字节 (-2 147 483 648，2 147 483 647) (0，4 294 967 295) 大整数值
BIGINT 8 字节 (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 615) 极大整数值
FLOAT 4 字节 (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) 0，(1.175 494 351 E-38，3.402 823 466 E+38) 单精度\n浮点数值
DOUBLE 8 字节 (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 双精度\n浮点数值
DECIMAL 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 依赖于M和D的值,等同于numeric 依赖于M和D的值,m为有效数字,d为小数部分数字 小数值

定义数据类型时候加unsigned 表示无符号.

日期和时间类型
类型 大小(字节) 范围 格式 用途
DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD 日期值
TIME 3 '-838:59:59'/'838:59:59' HH:MM:SS 时间值或持续时间
YEAR 1 1901/2155 YYYY 年份值
DATETIME 8 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值,年月日时分秒
TIMESTAMP 4 1970-01-01 00:00:00/2038,结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07 YYYYMMDD HHMMSS 混合日期和时间值，时间戳

TIMESTAMP类型有专有的自动更新特性

字符串类型
类型 大小 用途
CHAR(M) 0-255字节 定长字符串,右边会填充空格达到长度m.m表示字符长度.
VARCHAR(M) 0-65535 字节 变长字符串,m表示最大列字符长度.但是不能突破行最大字节数65535,内存中会加1位存储.多一个字节表示用来表示变长.
TINYBLOB 0-255字节 不超过 255 个字符的二进制字符串
TINYTEXT 0-255字节 短文本字符串
BLOB 0-65 535字节 大字节,二进制形式的长文本数据,最大长度为65535字节的blob列
TEXT 0-65 535字符 大文本,长文本数据,最大长度为65535个字符
MEDIUMBLOB 0-16 777 215字节 二进制形式的中等长度文本数据
MEDIUMTEXT 0-16 777 215字节 中等长度文本数据
LONGBLOB 0-4 294 967 295字节 二进制形式的极大文本数据
LONGTEXT 0-4 294 967 295字节 极大文本数据

LENGTH函数返回字节数,而char和varchar定义的M是字符数限制

char可以将字符串变成等长的,空间换时间,效率略高;varchar变长,节省了空间.

enum枚举类型

列出所有选项,加入内容只能使用枚举括号中的值

create table t2 (
id int auto_increment primary key,
    gender enum('male','female')
) charset utf8;

关系操作

关系:在关系数据库中,关系就是二维表

关系操作就是对表的操作

选择(selection):又称为限制,是从关系中选择出满足给定条件的元组.

投影(projection):在关系上投影就是选择出若干属性列组成新的关系.

连接(join):将不同的两个关系连接成一个关系.

DML(数据操作语言)--CRUD增删改查

INSERT 语句(可批量操作)

INSERT INTO table_name(表名)(col_name(列名),...) VALUES (value1,,,,):

向表中插入一行数据,自增字段,缺省值字段,可谓空字段不写

INSERT INTO table_name SELECT,,,:

将select查询的结果插入到表中,将select查询到的结果放入表中,要求前后数量一致

INSERT INTO table_name(col_name1,,,) VALUES (value1,...) ON DUPLICATE KEY UPDATE

col_name1 = value1,...:

这条语句的意思,就是主键不在新增记录,主键就在更新部分字段,不存在就更新,存在就更改

如果主键冲突,唯一键冲突就执行update后的设置.

INSERT IGNORE INTO table_name(col_name,...) VALUES (value1,...):

如果主键冲突,唯一键冲突就忽略错误,返回一个警告

alter table 表名 +方法语句

可加定义时的所有语句.

UPDATE语句

UPDATE[IGNORE] tbl_name

​ SET col_name1 = expr1[,col_name2 = expr2...]

​ [WHERE where_definition]

IGNORE意义同INSERT语句

UPDATE reg SET name= 'tom' WHERE id = 5:

DELETE语句

DELERE [IGNORE] FROM tbl_name

​ [WHERE where_definition]

删除符合条件的记录

SELECT语句

SELECT
	[DISTINCT]
	select_expr,...
	[FROM table_references]
	[WHERE where_definition]
	[GROUP BY {col_name|expr|position}
       [ASC|DESC],...[WITH ROLLUP]]
    [HAVING where_definition]
    [ORDER BY {col_name|expr|position}
       [ASC|DESC],...]
    [LIMIT {[office,]row_count|row_count OFFSET offset}]
    [FOR UPDATE|LOCK IN SHARE MODE]

FOR UPDATE 会把行进行写锁定,就是排它锁.

查询,查询的结果称为结果集recordset

--查询所有
SELECT * FROM employees;
--字符串合并加别名
SELECT emp_no ,CONCAT(first_name,' ',last_name) as name FROM employees;
--数字相加,字符串默认为0
SELECT emp_no ,first_no + last_no FROM employees;
--LIMIT 语句,
--limit 5表示只显示5行,
--offset表示偏移量,值向后偏移几位
-- LIMIT 5 OFFSET 2;也可以写成LIMIT 2,5
SELECT emp_no ,CONCAT(first_name,' ',last_name) as name FROM employees LIMIT 5 OFFSET 2;

WHERE 语句

运算符 描述
= 等于
<>,!= 不等于
<,>,<=,>= 本意
BETWEEN 在某个范围内,between a and b 等价于[a,b] 闭区间
LIKE 字符串模式匹配,%表示任意多个字符,_表示一个字符
IN 指定针对某个列的多个可能值
AND 与
OR 或

注意:如果很多表达式需要使用AND,OR计算逻辑表达式的值的时候,会产生结合律的问题,使用小括号来避免产生错误.

--条件模糊查询
SELECT * FROM employees WHRER emp_no < 10015 and last_name LIKE 'P%';
SELECT * FROM employees WHRER emp_no < 10015 and last_name LIKE '%P%';

ORDER BY 子句

对查询结果进行排序,可以升序ASC(默认升序),降序DESC.

BY后可写多个函数要求,从前到后依次满足执行

5.7之后要与聚合函数连用

--降序
SELECT * FROM employees WHERE emp_no in (10001,10002,10010) ORDER BY emp_no DESC;

DISTINCT 不返回重复记录(相当于去重)

--DISTINCT 使用
SELECT DISTINCT dept_no form dept_emp;

聚合函数

函数 描述
COUNT(expr) 返回记录中记录的数据,如果指定列,则返回非NULL值的行数.()中可加*表示所有
COUNT(DISTINCT expr[,expr]) 返回不重复非NULL值的行数
AVG([DISTINCT]expr) 返回平均值,[返回不重复值的平均值]
MIN(expr),MAX(expr) 最小值,最大值
SUM([DISTINCT]expr) 求和,[返回不重复值的和] 
--f1相当于创建了别名
SELECT sum(salary) f1 FROM salaries;

因为主键中不可以出现NULL,所以使用count函数建议使用主键

分组查询

使用GROUP BY 子句,如果有条件,使用HAVING 子句过滤分组,聚合过的结果(类似where).where先执行,group by如果与where同时出现,放在where后面

--聚合
SELECT emp_no ,SUM(salary),AVG(salary) AS sal_avg from salaries GROUP BY emp_no HAVING sal_avg > 60000;

子查询

查询语句可以嵌套,内部查询就是子查询.

子查询必须在一个小括号中.

子查询中不能使用ORDER BY

两种常用场景

  1. 在in语句中
  2. 作为一张临时表给其他人用

连接JOIN

交叉连接cross join

笛卡尔乘积,全部交叉,在MySQL中,cross join从语法上说于inner join等同,数据量大,慎用

SELECT * from employees CROSS JOIN salaries
内连接

inner join,省略为join

  • 等值连接,只选默写field相等的元组(行),使用ON限定关联的结果.
SELECT * from employees INNER JOIN ON employees.emp_no= salaries.emp_no;
  • 自然连接,是一种特殊的等值连接,会自动去掉重复的列.用的少
--自然连接,去掉了重复列,且自行使用employees.emp_no= salaries.emp_no的条件
SELECT * from employees NATRUAL JOIN salaries;
外连接

outer join,可以省略为join

分为左外连接,即左连接;右外连接,即右连接;全外连接

--左连接
SELECT * from employees LEFT JOIN salaries ON employees.emp_no= salaries.emo_no;
--右连接
SELECT * from employees RIGHT JOIN salaries ON employees.emp_no= salaries.emo_no;
--这个右连接等价于上面的左连接
SELECT * from salaries RIGHT JOIN employees ON employees.emp_no= salaries.emo_no;

左外连接,右外连接

看表的数据的方向,谁是主表,谁的所有数据都显示,匹配不上的显示null

自连接

表自己和自己连接

本质上是把一张表当成两张表来用

select manager.* from emp manager,emp worker where manaer.empno=worker.mgr and worker.empno=1;
select manager.* from emp manager inner join emp worker on manaer.empno=worker.mgr where
worker.empno=1;

事务Transaction

存储引擎

InnoDB引擎,支持事务

MyIsam不支持事务

事务

由若干条语句组成,指的是要做的一系列操作.

特性

关系型数据库中支持事务,必须支持其四个属性(ACID);

特性 描述
原子性(atomicity) 一个事务是一个不可分割的工作单位,事务中包括的所有操作要么全部做完,要么什么都不做
一致性(consistency) 事务必须是使数据库从一个一致性状态变到另一个一致性状态,一致性与原原子性是密切相关的
隔离性(isolation) 一个事务的执行不能被其他事务干扰.即一个事务内部的操作及使用的数据对并发的其他事务是隔离的,并发执行的各个事务之间不能互相干扰
持久性(durablilty) 持久性也称永久性(permanence),它对数据库中的数据的改变就是应该是永久性的.接下来的其他操作或故障不应该对其有任何影响.

原子性:要求事务中的所有操作,不可分割,不能做了一部分操作,还剩一部分操作;

一致性,多个事务并行执行的结果,应该和事务排队执行的结果一致.如果事务的并行执行和多项程读写共享资源一样不可预期,就不能保证一致性.

隔离性:就是指多个事务访问共同的数据,应该互不干扰.隔离性,指的是究竟再一个事务处理期间,其他事务能不能访问的问题.

持久性:就是事务提交后,数据不能丢失.

MySQL隔离级别

隔离性不好,事务的操作就会相互影响,带来不同严重程度的后果.

隔离性不好带来的问题:

  1. 更新丢失 lost update

    事务A和B,初始值10,A要加1,B要减1,结果是11,看上去就是A的更新消失了

  2. 脏读

    事务A和B,事务B读到了事务A未提交的数据(一个中间值)

  3. 不可重复读unrepeatable read

    事务A在事务执行中,相同的查信语句得到了不同的结果,不能保证同一条查询语句重复读相同的结果就是不可以重复读.

  4. 幻读phantom read

    事务A中同一个查询要进行多次,事务B插入数据,导致A返回不同的结果集.

    数据集有记录增加了,可以看作是增加了记录的不可重复读.

从而引出隔离级别:

隔离级别由低到高,

隔离级别 描述
READ UNCOMMINTTED 读取到未提交的数据
READ COMMITTED 读已经提交的数据,ORACLE默认隔离级别.
REPEATALBE READ 可以重复读,MySQL的默认隔离级别.
SERIALIZABLE 可串行化.事务间完全隔离,事务不能并发,只能串行执行

隔离级别越高,串行化越高,数据库执行效率低,当前事务处理的中间结果对其它事务不可见程度越高.

隔离级别越低,并行度越高,性能越高

事务语法

开始,START TRANSACTION或BENGIN开始一个事务,START TRANSACTION是标准的SQL语法.

使用COMMIT提交事务后,变成称为永久变更.

回滚,ROLLBACK可以在提交事务前,回滚变更,事务中的操作就如同没有发生过一样(原子性).

提交,SET AUTOCOMMIT语句可以禁用或启用默认的autocommit模式,用于当前连接.SET AUTOCOMMIT = 0 禁用自动提交事务,如果开启自动提交,每有一个修改表的语句执行后,会立即把更新存储到磁盘.

数据库和数据仓库的区别

数据仓库数据更海量

数据库存储在线交易数据OLTP;数据仓库存储历史数据用于分析OLAP

数据库支持在线业务,需要怕闹翻增删改查;数据仓库一把囤积历史数据支持用于分析的SQL,一般不建议删改.

其他概念

delimiter // 更换换界符

视图
增加视图：
create view 视图名 as SQL语句;
删除：	
drop view v1;

例子：
mysql> select * from user where name='zekai';
+----+-------+--------+
| id | name  | salary |
+----+-------+--------+
|  1 | zekai |    900 |
+----+-------+--------+
1 row in set (0.00 sec)

mysql> create view v1 as select * from user where name='zekai';
Query OK, 0 rows affected (0.07 sec)

mysql>
mysql> show tables;
+-----------------+
| Tables_in_test3 |
+-----------------+
| user            |
| v1              |
+-----------------+
2 rows in set (0.00 sec)

mysql> select * from v1;
+----+-------+--------+
| id | name  | salary |
+----+-------+--------+
|  1 | zekai |    900 |
+----+-------+--------+
1 row in set (0.00 sec)

就是将一条sql语句赋值给一个view对象,视图会占数据库资源,慎用

游标cursor

可以将游标当作一个指针,指向结果集中的某一行.

存储过程

存储过程(stored procedure),数据库系统中,一段完成特定功能的SQL语句.编写程类似函数的方式,可以传参并调用.支持流程控制语句.相当于创建一个函数.

触发器

触发器(trigger),由事件触发的特殊的存储过程,例如insert数据时触发.触发器功能强大,但是会有性能问题.

这两种,数据库高级内容,很少用.

数据库的备份

为了将重要的数据保存下来

用法：
#语法：
# mysqldump -h 服务器 -u用户名 -p密码 数据库名 表名,  表名,.... > aaa.sql

#示例：
#单库备份
mysqldump -uroot -p123 db1 > db1.sql
mysqldump -uroot -p123 db1 table1 table2 > db1-table1-table2.sql

#多库备份
mysqldump -uroot -p123 --databases db1 db2 mysql db3 > db1_db2_mysql_db3.sql

#备份所有库
mysqldump -uroot -p123 --all-databases > all.sql

重新导入：
mysql> source D:/test3.sql;
posted @ 2019-10-29 16:10  Agsol  阅读(228)  评论(0)  编辑  收藏  举报