关系型数据库和SQL

---

数据库(Database)

---

概念

存放数据的仓库，用于管理大量甚至海量数据

• DBMS: DataBase Management System，数据库管理系统，独立程序

• DBA: DataBase Administrator，数据库管理员

数据库两种类型

• 关系型数据库(RDMS，Relationship DBMS)
• 非关系型数据库(NO-SQL)

主流关系型数据库产品

• 大型数据库：Oracle、SQL Server、DB2等

• 中型数据库：MySQL等

• 小型数据库：Access等

• 微型数据库：SQLite（开源免费，C语言编写）

所有数据库产品都遵守SQL标准，即都可以通过SQL访问它们，所以随便选择一个学习就行

主流非关系型数据库产品： MongoDB，Redis等

---

SQL(Struct Query Language)

---

概念

结构化查询语言，用于访问DBMS交流沟通

Mysql常用命令

连接数据库：mysql -h(ip 地址，连接本机可省略) -u数据库名 -p密码

退出数据库：exit；quit

切换数据库：use 数据库名

用户改密码：update user set password = password('新密码') where user='用户名';

数据定义语言(DDL: Data Definition Language)：create，alter，drop

数据库及用户操作

show databases; 查看数据库

create database 数据库名 default character set utf8; 创建数据库 

drop database 数据库名; 删除

alter database 数据库名 character set utf8; 修改数据库编码

create user 'wh'@'%' identified by 'wh123'; 创建用户 drop user 用户名; 删除用户

rename user wh to wh1; 给用户改名

set password for 'wh'@'%' = password('123456'); 给用户改密码

表定义操作

show tables; 查看表    

desc table student; 查看表属性

use mydb; 使用数据库

create table student values(sno int(6) unsigned primary key, name varchar(20) nt null, sex varchar(5), birth date , sight float(2,1), phone char(11) unique); 创建一个含多样字段的表

drop table student; 删除表，若设置外键属性 foreign key 字段 references 表名(字段) 则不能删除 ，在外键属性后加上 on delete cascade 就可以删除(级联删除：递归删除关联表中的相关数据)

alter table student add column qq varchar(12) unique; 给表添加字段qq

alter table 表名 drop 列名; 删除表字段

alter table student modify sno int(6) unsigned; 更新表字段属性

alter table rename 原表名 新表名; 修改表名

数据控制语言(DCL: Data Control Language)：grant，revoke

grant all on 数据库名.* to 'wh'@'%'; 给用户授权数据库

revoke all on 数据库名.* from 'wh'@'%'; 撤回授权

flush privileges; 刷新(授权操作后一般需要刷新)

数据操作语言(DML: Data Manipulation Language)：insert，update，delete

插入数据：insert into 表名 [(字段列表)] values (值列表1)[, (值列表2), ..(值列表n)];

修改数据：update 表名 set 字段名1=字段值1, 字段名2=字段名2,..where[条件表达式];

删除数据：delete from 表名[where 条件表达式]; truncate table 表名

数据查询语言(DQL: Data Query Language)：select

select 字段列表|* 

from 表 

[where 条件表达式 

group by 分组表达式 --使用having关键字对分组之后的结果进行筛选

having 筛选表达式 --

order by 分组表达式 --desc表示逆序

limit 分页表达式]；

• 查询时去除重复记录：distinct
• 查询时添加常量列：as 例如：select * , ‘中国’ as country from student; //在后面增加一栏 country 内用全部是中国。
• 逻辑条件：and, or
• 比较条件：< , > , = , 在a和b之间：between a and b 等价于大于等于a，小于等于b
• in 字段值：in (值1, 值2, …)
• 判断空：is (not) null 只是判断是否为空，跟空字符串无关
• 模糊查询 like：%匹配任意多个字符， _匹配任意一个字符

约束(Constraint)

对插入数据库的数据进行合法性校验，保证数据的完整性

五种类型的约束

1. 非空约束(not null)：字段值不能为NULL
2. 唯一约束(unique)：字段值不能重复（可以插入多个NULL）
3. 主键约束(primary key)：非空并且唯一
4. 外键约束(foreign key)：字段值只能为另外一张表的某个字段值
   • 级联修改：on update cascade
   • 级联删除：on delete cascade
5. 检查约束(check)：限制字段的取值范围

查询(Query)

多表联合查询

• 笛卡儿集
• 查询结果集
• 表名太长可以使用别名简化写法
• where子句用于设置查询条件
• order by子句用于排序（desc逆序）
• limit 多级排序（limit 3 得到前三个，limit自居后不能直接跟and语句）

子查询

及嵌套查询，将一个select查询结果当作一个单值，一个集合或一个临时表对待，参与另一个select查询

分页查询

• 起始索引=(当前页号-1)*每页显示记录数；
• 结束索引=当前页号*每页显示记录数；

连接(Join)

分为三类：

• 交叉连接：无条件连接，即笛卡尔集

• 内连接：等值连接，不等值链接，自然连接(特殊的等值连接，在它的基础上去除重复字段)

  表与表、条件之间 用“表名1 inner join 表名2 on 关联条件
  select e.ename, m.ename 
  from emp e 
  inner joinemp m on e.mgr = m.empno;
  

• 外连接：左(外)连接(left (outer) join)，又(外)连接(right (outer) join)，全(外)连接full (outer) join)

• 左(外)连接：Left [ outer ] join 表 on关联条件

  使用左边表的数据去匹配右边表的数据，如果符合连接条件的结果则显示，如果不符合连接条件则显示null
  

• 右(外)连接：right [outer] join 表 on 关联条件

内置函数

分为两种：单行函数和聚合函数

单行函数是以一条记录为单位进行处理的，而聚合函数是用来存储多条记录的(即分组，通常和 group by子句使用)

视图(View)

• 相当于一张虚表，针对不同用户的需求呈现不同的数据，其本质就是将一个select查询封装为一张虚表

• 视图和表一样，都可以进行CRUD操作，但不建议对视图进行DML操作，因为可能会破坏数据的完整性

• 创建视图： create view 视图名 as 查询命令；

索引(Index)

提升查询效率的数据库对象，建议基于主键或唯一键创建索引

事务(Transaction)

事务就是一系列连续的操作，当这所有的操作都完成就可以提交事务（即持久化事务），如果其中任何一个提交失败就可以立即滚回到事务的开始状态，就像啥事都没有发生一样

事务ACID四大特性

• 原子性(Atomicity)：原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生

• 一致性(Consistency)：事务前后数据的完整性必须保持一致

• 隔离性(Isolation)：事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要互相隔离

• 持久性(Durability)：持久性是指一个事务一旦被提交，他对数据库的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响

TCL

begin/start transaction
commit
rollback
savepoint
rollback to savepoint
delimiter 

注意：像DDL等语句会导致事务隐式提交

存储过程(Storage Procedure)

实现某个功能的代码块，没有返回值的函数

函数(Function)

实现某个功能的代码块，有返回值的存储过程

游标(Cursor)

一种处理数据的方法，用于查看或处理查询结果集中的数据，可以让我们在查询结果集中以一行或多行为单位向前或向后浏览或处理数据

使用步骤：

1. 定义游标
2. 打开游标
3. 提取游标
4. 关闭游标

触发器(Trigger)

在发生某个事件时（比如对表中数据进行增删改查）会被自动执行的存储过程