数据库（复习用）

1.初识数据库

数据库(Database)是按照数据结构来组织、 存储和管理数据的仓库。在1990年以后，数据管理不再是存储和管理数据，而是转变成用户所需要的各种数据管理的方法。数据库具有能存在一起、能与多个用户共享、具有尽可能小的冗余度、与应用程序彼此独立的作用。数据库系统在各个方面都得到了广泛的应用。

1.1数据库分类

①关系型数据库：关系型数据库模型是把复杂的数据结构归结为简单的二元关系（即二维表格形式）。在关系型数据库中，对数据的操作几乎全部建立在一个或多个关系表格上，通过对这些关联的表格分类、合并、连接或选取等运算来实现数据库的管理。其实，目前关系型数据库的具体产品有：Oracle和MySQL。其中Oracle在数据库领域是占领导地位的。

②键值数据库：键值数据库就类似传统语言中使用的哈希表。可以通过key来添加、查询或者删除数据库，因为使用key主键访问，所以会获得很高的性能及扩展性。键值数据库主要使用一个哈希表，这个表中有一个特定的键和一个指针指向特定的数据。Key/value模型对于IT系统来说的优势在于简单、易部署、高并发；典型产品：Memcached、Redis、MemcacheDB

③列存储数据库：列存储数据库将数据存储在列族中，一个列族存储经常被一起查询的相关数据，比如人类，我们经常会查询某个人的姓名和年龄，而不是薪资。这种情况下姓名和年龄会被放到一个列族中，薪资会被放到另一个列族中。这种数据库通常用来应对分布式存储海量数据；这种数据库的典型产品有：Cassandra、HBase

④面向文档数据库：面向文档数据库会将数据以文档形式存储。每个文档都是自包含的数据单元，是一系列数据项的集合。每个数据项都有一个名词与对应值，值既可以是简单的数据类型，如字符串、数字和日期等；也可以是复杂的类型，如有序列表和关联对象。数据存储的最小单位是文档，同一个表中存储的文档属性可以是不同的，数据可以使用XML、JSON或JSONB等多种形式存储。这种数据库的典型产品有：MongoDB、CouchDB；

⑤图形数据库：图形数据库允许我们将数据以图的方式存储。实体会被作为顶点，而实体之间的关系则会被作为边。比如我们有三个实体，Steve Jobs、Apple和Next，则会有两个“Founded by”的边将Apple和Next连接到Steve Jobs。这种数据库的典型产品有：Neo4J、InforGrid。

其中，关系型数据库是使用最广泛，也是使用人数最多的。

1.2数据库语言

①DDL（数据定义语言）

数据定义语言DDL用来创建数据库中的各种对象-----表、视图、索引、同义词、聚簇等

②DCL（数据控制语言）

数据控制语言DCL用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等。

③DML（数据操作语言）

数据操纵语言DML主要有三种形式：

1) 插入：INSERT

2) 更新：UPDATE

3) 删除：DELETE

④DQL（数据查询语言）

数据查询语言DQL基本结构是由SELECT子句，FROM子句，WHERE子句组成的查询块：

2.语言介绍

2.1DDL（数据定义语言）

①创建数据库

CREATE {DATABASE | SCHEMA} db_name

②创建数据表

CREATE [TEMPORARY] TABLE tablename（数据名 数据类型）；

③修改数据库表格

ALERT TABLE tablename CHANGE 原列名 修改列名 修改后的数据类型;

2.2DCL（数据控制语言）

①创建用户
#创建用户
#create user '用户名'@'主机名' identified by '密码';
#注意：主机名可以是localhost，任意主机可以为%，表示限制登陆ip
create user 'test'@'localhost' identified by '123456';
②修改用户密码
普通用户修改密码

#mysql 8.0之前版本
#普通用户：登陆自己的账号后 set password = password('密码');
set password = password('123321');
#root用户给普通用户修改密码 set password for '用户名'@'主机名' = password ('密码');
set password for 'test'@'localhost' = password ('123');
 #mysql8.0之后
#alter user "用户名"@"主机名" identified with mysql_native_password by '新密码'
alter user "test"@"localhost" identified with mysql_native_password by '123321';
root用户修改密码
mysql版本在8.0之前

#root用户修改自己的密码
#set password = password('新密码');
set password = password('123321');
mysql版本在8.0之后

#mysql8.0之后
#alter user "用户名"@"主机名" identified with mysql_native_password by '新密码'
alter user "test"@"localhost" identified with mysql_native_password by '123321';
③用户授权
#给用户授予权限，权限可以是select、update、delete、create、reload、drop等等
#如果要全部授予可以直接all
#授予的对象可以是库或者库下的某张表
grant select,update,delete on blog.test01 to ‘test’@’localhost’;
#授予所有库下所有表的所有权限
grant all on *.* to ‘test’@’localhost’;
④查看用户权限
#查看用户的权限
#show grants for ‘用户名’@’主机名’;
show grants for 'test'@'localhost';
⑤撤销用户权限
#撤销用户权限
#revoke 权限1,权限2,权限3... on 库名.表名 from '用户名'@'主机名';
revoke select on blog.test01 from 'test'@'localhost';
⑥删除用户
#删除用户
#drop user '用户名'@'主机名';
drop user 'test'@'localhost';

2.3DML（数据操作语言）

drop database dbname
drop table tablename / alter table tablename drop colum 字段
修改表：
alter table tablename MODIFY[COLUMN] 字段 约束
增加表字段：
alter table tablename ADD[COLUMN] 字段 约束
修改字段名字：
alter table tablename change 字段 字段1 约束
修改字段排列顺序：
放在字段2后：
alter table tablename add 字段1 date after 字段2
放在最前面：
alter table tablename modify 字段 约束
更该表名：
alter table tablename RENAME[to]new_tablename

更新表数据：
update tablename SET file1=value1,file2=value2,…filen=valuen
where condition

更新多张表：
update t1,t2,t3…tn SET t1.file1=expr1…tn.filen=exprn
where condition

删除记录：
delete from tablename where condition
删除多个表的数据：
delete t1,t2,t3…tn from t1,t2,…tn
where condition

排序限制查询：
多条件查询：
select * from tablename
where 字段=condition and 字段<condition
排序：
select * from tablename
where condition ORDER BY file1 [DESC\ASC],file2[DESC],…filen[ASC]
排序限制：
select * from tablename order by file1 limit 3;

聚合：
select [file1,file2,…,filen]fun_name
from tablename
[WHERE where_condition]
[GROUP BY file1,file2,…,filen[WITH ROLLUP]]
[HAVING where_condition]

fun_name:要做的聚合操作，聚合函数有(sum)求和，count(*)(记录数)，max，min
GROUP BY:关键字表示要进行分类聚合的字段
WITH ROLLUP:聚合后的结果进行再汇总
HAVING:对分类后的结果在进行条件过滤

having,where的区别在于：
having是对聚合后的结果进行调节的过滤
where是在聚合前对记录进行过滤。(先where后having效率最大)

表连接：
select file1,file2 from tablename1,tablename2
where tablename1.file=tablename2.file

左连接：

右连接：

子查询：
select * from tablename1
where file in(select file from tablename2);
子查询转化为表连接：
select tablename.* from tablename1,tablename2
where tablename1.file=tablename2.file;

2.4DQL（数据查询语言）

SELECT * FROM 表名;

查询指定列

 SELECT 列1 [, 列2, ... 列N] FROM 表名;

完全重复的记录只一次

当查询结果中的多行记录一模一样时，只显示一行。一般查询所有列时很少会有这种情况，但只查询一列（或几列）时，这总可能就大了！

 SELECT DISTINCT * | 列1 [, 列2, ... 列N] FROM 表名;
 -- 查询员工表的工资，如果存在相同的工资只显示一次
 SELECT DISTINCT sal FROM emp;

列运算：数量类型的列可以做加、减、乘、除运算

 SELECT sal*1.5 FROM emp;
 SELECT sal+comm FROM emp;

字符串类型可以做连续运算

 SELECT CONCAT('$', sal) FROM emp;

转换NULL值

有时需要把NULL转换成其它值，例如com+1000时，如果com列存在NULL值，那么NULL+1000还是NULL，而我们这时希望把NULL当前0来运算。

 /*IFNULL(comm, 0)：如果comm中存在NULL值，那么当成0来运算*/
 SELECT IFNULL(comm, 0)+1000 FROM emp;
 /*如果student_name列为null，则返回'没有名字'*/
 select ifnull(student_name,'没有名字') from student_table;
 /*如果student_name列等于'张三'，则返回null*/
 select nullif(student_name,'张三') from student_table;
 /*如果student_name列为null，则返回'没有名字'，否则返回'有名字'*/
 select if(isnull(student_name),'没有名字','有名字') from student_table;

给列起别名

你也许已经注意到了，当使用列运算后，查询出的结果集中的列名称很不好看，这时我们需要给列名起个别名，这样在结果集中列名就显示别名了

-- 其中AS可以省略
SELECT IFNULL(comm, 0)+1000 AS 奖金 FROM emp;

条件控制（条件查询）

与UPDATE和DELETE语句一样，SELECT语句也可以使用WHERE子句来控制记录

SELECT empno,ename,sal,comm FROM emp WHERE sal > 10000 AND comm IS NOT NULL;
SELECT empno,ename,sal FROM emp WHERE sal BETWEEN 20000 AND 30000;
SELECT empno,ename,job FROM emp WHERE job IN ('经理', '董事长');

模糊查询

-- 模糊查询需要使用运算符：LIKE，其中_匹配一个任意字符，注意，只匹配一个字符而不是多个。下面语句查询的是姓张，名字由两个字组成的员工
SELECT * FROM emp WHERE ename LIKE '张_';
-- 姓名由3个字组成的员工
SELECT * FROM emp WHERE ename LIKE '___'; 
-- 其中%匹配0~N个任意字符，所以上面语句查询的是姓张的所有员工
SELECT * FROM emp WHERE ename LIKE '张%';
-- 千万不要认为上面语句是在查询姓名中间带有阿字的员工，因为%匹配0~N个字符，所以姓名以阿开头和结尾的员工也都会查询到
SELECT * FROM emp WHERE ename LIKE '%阿%';
-- 这个条件等同与不存在，但如果姓名为NULL的查询不出来
SELECT * FROM emp WHERE ename LIKE '%';

排序

 -- 按sal排序，升序,其中ASC是可以省略的
 SELECT * FROM emp ORDER BY sal ASC;
 -- 按comm排序，降序,其中DESC不能省略
SELECT * FROM emp ORDER BY comm DESC;
-- 使用sal升序排，如果sal相同时，使用comm的降序排
SELECT * FROM emp ORDER BY sal ASC, comm DESC;

聚合函数：聚合函数用来做某列的纵向运算

• COUNT

-- 计算emp表中所有列都不为NULL的记录的行数
SELECT COUNT(*) FROM emp;
-- 计算emp表中comm列不为NULL的记录的行数
SELECT COUNT(comm) FROM emp;

• MAX

 -- 查询最高工资
 SELECT MAX(sal) FROM emp;

• MIN

-- 查询最低工资
SELECT MIN(sal) FROM emp;

• SUM

 -- 查询工资合
 SELECT SUM(sal) FROM emp;

• AVG

-- 查询平均工资
SELECT AVG(sal) FROM emp;

分组查询 分组查询是把记录使用某一列进行分组，然后查询组信息。

-- 使用deptno分组，查询部门编号和每个部门的记录数
SELECT deptno, COUNT(*) FROM emp GROUP BY deptno;
-- 使用job分组，查询每种工作的最高工资
SELECT job, MAX(SAL) FROM emp GROUP BY job;
-- 以部门分组，查询每组记录数。条件为记录数大于3,组条件只能是分组字段或分组函数
SELECT deptno, COUNT(*) FROM emp GROUP BY deptno HAVING COUNT(*) > 3;

limit子句(方言)： LIMIT用来限定查询结果的起始行，以及总行数。

-- 查询起始行为第5行，一共查询3行记录，其中4表示从第5行开始，其中3表示一共查询3    行。即第5、6、7行记录。
SELECT * FROM emp LIMIT 4, 3;
-- 一页的记录数：10行，查询第3页（公式：(当前页-1) * 每页记录数）
select * from emp limit 20, 10；

3.视图与存储功能

3.1视图

1、创建视图
可以先查询出想要的数据，再在前面加上 create view 视图名称 as 就行了。
例如：create view userviewo1 as select * from user where（ 如果不希望往视图里面插入数据的时候进行校验，则这样。如果希望进行where后面的校验， 则在 最 后加入 with check option）
2、往视图里面插入数据(将创建视图看做一张表)

3.2存储过程

1、create procedure
存储过程在navicat中显示在函数中，可以直观的看出这是一个函数，被调用

-- 存储过程
create procedure userpro(in name varchar(20), out num int(3))
begin
    if name is null or name = "" then
        select * from user;
    else 
        select * from user where name like concat('%', name , '%');
    end if;
    select foun_rows() into num;
end; 

call userpro(null, @num);
select @num;