*数据库及指令

• 数据库基本知识

• 常见的数据库

• 关系型数据库：数据库以表的形式组成，表以行和列的形式组成。

• 常见的数据库软件

• 关系型数据库

• 存储到硬盘，稳定，速度较慢

• Oracle--->Oracle公司：收费，功能强大--->提供给大型公司、国企、银行

• *MySQL--->Oracle公司：有免费版，互联网公司、大型公司；用户逐渐增加，用的最多

• Oracle和MySQL语句基本一致

• SQL sever--->微软

• Db2

• 缓存级别数据库

• 速度快

• redis：放置一些高频访问的数据；存储用户的登录信息（单点登录）；统计一些网站的访问量；应用软件的排行

• 文档存储数据库

• 适用新闻、小说类型网站

• mongodb 格式：json

• 嵌入式数据库

• sqllite：轻量级，嵌入到Android、iOS系统中，存储少量数据

• 比如：电话簿、短信

• MySQL数据库服务器、数据库和表的关系

• 数据在数据库中的存储方式

• MySQ数据库

• MySQL程序安装

• 安装和卸载都必须通过安装包完成。

• 安装完到后--->任务管理器--->服务--->查找MySQL--->查看是否为正在运行

• MySQL连接指令

• mysql -u用户名 -p密码

• 配置环境变量

• 意义

• 在电脑的任意位置都可以启动某些程序，不配置就只能到路径下启动

• 配置步骤

• 右击我的电脑--->属性--->高级系统设置--->环境变量--->新建MYSQL_HOME--->内容为MySQL.exe的路径（不带bin目录）--->在环境变量中找到path--->编辑文本--->在最前面添加：%MYSQL_HOME%\bin; --->保存--->配置完成

• SQL语言

• Structured Query Language, 结构化查询语言

• SQL是用来存取关系数据库的语言，具有查询、操纵、定义和控制关系型数据库的四方面功能

• SQL的分类

• DDL语言

• 数据库定义语言

• 数据库、表、列的创建、修改、删除等

• 结束符号：;

• 数据库操作

show databases;    --展示当前所有的数据库
create database 库名;    --创建数据库，名字叫你输入的用户名
drop database 库名;    --删除指定的数据库
show create database 库名;    --展示创建数据库的过程
use 库名;    --使用指定的数据库

• 数据库表操作

show tables;    --展示当前库中所有的表
create table student(id int,name varchar(20),age int);    --创建student表，字段id为数字类型，name类型为可变字符串类，age为数字类型
desc 表名;    --查看表结构
show create table 表名;    --查看表的创建过程
drop table 表名;
alter table 表名 modify 指定列 修改后的数据类型 数据约束;
alter table 表名 add 要增加的列 数据类型 数据约束;
    --NULL：是否允许为空；Default：默认值
    --modify：修改

• 数据类型

int    -- 整形
    INTEGER    -- int大写形式（包装类形式）
float/double    -- 浮点型，例如double(5,2)表示最多5位且其中必须有2为小数：即999.99为最大值，0.01为最小值
    float->FLOAT    -- 包装类形式
    double->Double    -- 包装类形式
char    -- 固定长度字符串。最大长度为char(255)。输入数据小于定义长度时会用空格补足
varchar    -- 可变长度字符串。最大长度为varchar(65535)。输入数据小于定义长度时按照输入数据保存，不需要补足
    varchar    -- 包装类形式
date    -- 日期类型，格式为：yyyy-MM-dd
time    -- 事件类型，格式为：hh:mm:ss
timestamp    -- 时间戳，默认为最后一次修改的时间

• 单表字段的约束

--约束写在数据类型之后；一个字段可以添加多个约束，用空格隔开

--主键约束：主键只能有一个，默认不允许为空
primary key    --关键词；通常用来约束id类似的核心字段
    alter table tablename drop primary key;    --删除主键

--非空约束：
not null    --关键词；不允许为空

--唯一约束
unique    --关键词；唯一

--属性
auto_increment    --自增,往后进行+1运算；如果手动输入数据以实际输入为准；写在约束关系后。

--eg：
create table xx(id int primary key auto_increment,name varchar(20) unique not null,sex varchar(20) not null,age int not null);    --创建一个名为xx的表，其中有id、name、sex、age四个字段，数据类型分别是int、varchar、varchar、int类型；约束分别为主键且自增运算、唯一且不为空、不为空、不为空。

• *DML语言

• 数据操纵语言

• 数据库、表的增、删、改操作

• 增加数据

create table xx(a int,b varchar(20),c int);    --创建一个名为xx的表，里面含有a，b，c三个列，数据类型分别为int,varchar,int
--下面开始增加数据

--增加数据的第一种方式
--指定字段，指定数据
insert into xx(a,b,c) values(1,"二",3);    --在表中插入数据：插入数据后a=1，b=二，c=3.
    --values：在表中要插入的值

--增加数据的第二种方式
--不指定字段和数据
insert into xx values(3,"一",2);    --不指定字段，直接对应填好要写入的数据

--增加数据的第三种方式
--连续插入多条数据；可以指定也可以不指定字段
insert into xx values(7,"八",9),(4,"五",6);    --数据之间用,隔开

    --在添加字符格式和日期格式的数据时要用""标注出来。
    --不指字段插入数据时，时间戳数据用：NULL表示。

• 修改数据

mysql> select * from xx;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 | 二   |    3 |
|    1 | 二   |    3 |
|    3 | 一   |    2 |
|    7 | 八   |    9 |
|    4 | 五   |    6 |
+------+------+------+
5 rows in set (0.00 sec)

--update：修改更新
update xx set c=10;    --修改c列中全部数据为10
update xx set c=11 where b="一";    --修改b=一的行对应的c列的数据为11；也可以用a=3来指定到对应的c列数据
update xx set b="九",c=6 where a=7;    --修改a=7的行对应的b，c的数据分别为九，6
    --where：哪里

-- 修改结束符号
-- 关键词delimiter 要修改成的结束符号
delimiter ￥￥    -- 将结束符号修改为￥￥

--and 与
update xx set c=7 where c<6 and c>2;    --将c列中小于6且大于2的数据改为7
--or 或
update xx set c=c+1 where c=2 or c=3;    --将c列中等于2或者等于3的数据在原有基础上加1

• 删除数据

delete from 表名;    --删除表中的所有数据
delete from 表名 where 条件;    --删除指定条件下的数据

--当表中数据太多的时候我们不采用delete from 表名;的删除方法，我们通常会用以下方法删除表
truncate 表名;    --摧毁表，并且创建一个新的表

• *DQL语言

• 数据查询语言

select 要查的字段 from 表名;    --在指定的表中查询指定的字段，多个字段之间用,隔开
--*：所有字段。
select * from 表名;    --查询指定的表中的所有字段
select 要查的字段 as/*可以不写*/ 字段展示名称 from 表名;    --字段 as 展示名称为一组，每组之间用,隔开；as可以不写，系统会默认有as

--去重复查询
distinct    --去除重复的值
select distinct 列名 from 库名;    --只支持单列查询

--查询列中所有为空或非空的数据
NULL判断
is NULL    --是空    
is not NULL    --非空
select * from 表名 where 列名 is NULL;    --查询指定列中所有为空的数据
select * from 表名 where 列名 is not NULL;    --查询指定列中所有不为空的数据

• 列直接运算

select 列名+N/*各种运算符号、N为要改变的范围*/ from 表名;    --列数据在输出的时候可以直接运算，不同列之间用,隔开

--在运算中如果有NULL且不给NULL赋值的话，结果为0；所以就需要给NULL赋值
ifnull(列名,0)    --如果有NULL，则给NULL赋值为0
select 列名+N+ifnull(含NULL列名,0) from 表名;    --输出结果为列名+N+ifnull(含NULL列名,0)算式，一般会在后面加上别名：select 列名+N+ifnull(含NULL列名,0) 别名 from 表名;

• 模糊查询

--占位符号
_    --一个_代表一个字符
%    --一个%可以代表多个字符

--eg:
select * from student/*表名*/ where name/*要查询的字段*/ like "%花%";    --查询所有名字中带“花”的同学

• 关系运算符

=
>
<
<=
>=
!=/*或者*/<>    --不等于

• 逻辑运算符

and    --与。前后条件都满足
or    --或。前后条件满足其中一个就可以

• limit分页展示

--使用方式
limit 0,3/*开始位置，长度*/    --从0开始展示3个，表中第一行数据为第0行

select * from 表名 limit 0,3;

--从第一行开始，每次展示3条，第N页展示数据
limit 0+（N-1）*3，3    --N为页码

• between...and...

a between 8000 and 10000    --8000-10000之间的数据；左右闭区间，包括8000、10000
    --等同于：a>=8000 and a<=10000

select * from X/*表名*/ where a/*列名*/ between 8000 and 10000;    --查询X表中a列的数据，范围是【8000，10000】

• in 在某些范围内

select * from student/*表名*/ where name/*列名*/ in ("小王","小张","小李"/*括号内为要查询的数据*/);    --在student表中查找name为小王、小张、小李的信息
    --等同于：name="小王" or name="小张" or name="小李"

• 排序

order by 列名;    --排序

--从小到大，正序，默认加asc
order by 列名 asc;
    --等同于：
    order by 列名;/*asc为默认指令，无需填写*/

--从大到小，倒叙，需手动加desc
order by 列名 desc;/*desc不是默认指令，需要手动添加*/

    --eg：
    select * from employee/*表名*/ order by salary/*列名*/ desc;    --employee表中工资列salary按照从高到低排序
    select * from employee order by salary;    --employee表中数据按照从低到高排序

--order by后可以加多个排序条件，不同排序条件之间用,隔开
select * from employee order by salary desc/*条件一*/,birthday desc/*条件二*/;    --employee表中先按工资salsary倒序排列，然后按照生日birthday倒序排列

• *聚合函数

• count统计用户个数

select count(*) from student/*表名*/;    --统计student表中，一共有多少行数据

    --也可以统计指定的列有多少行数据
    select count（id/*列名*/） from student/*表名*/;    --统计student表中，id列中有多少行数据

• sum统计总数量

select sum(列名) from student;    --指定列中数据之和
    select sum(列名1),sum(列名2) from student;    --可以同时对多个列中的数据求和，不同列的数据之间用,隔开
    select sum(列名) 别名 from student;    --给求和数据所在列添加一个别名
select 列名1,(列名2+列名3+列名4) 别名/*可以不设置别名*/ from student;    --也可以直接对每一行不同列的数据进行求和

• avg统计平均数据

select avg(chinese) 别名/*可以添加别名*/,avg(math),avg(english) from student;    --统计student表中语文、数学、英语各科的平均成绩

• max最大数据；min最小数据

--可以设置别名；可以同时统计多个列，不同列之间用,隔开
select max(chinese) from student;    --统计student表中Chinese列中的最大数据
select min(chinese) from student;    --统计student表中Chinese列中的最小数据

--子查询
select * from student where math=(select min(math) from student);    --先查询student表中math最小的数据，再根据最小数据查找对应信息

• 分组查询

• 原理：按照分组的列名，将该列中数据相同的部分分到一组，然后对每一组中的数据进行计算。

group by    --分组关键词

select 列名 from 表名 group by 分组的列;
select 列名 from 表名 where 限定条件 group by 分组的列;    --加上限制条件以后只显示符合限制条件的分组

select count(*) from 表名 group by 分组的列;    --显示每组中数据的个数
select sum(列名) from 表名 group by 分组的列;    --将每组中对应的列的数据进行求和

having    --用在group by之后的限制条件指令，等同于where
select 列名 from 表名 group by 分组的列 having 限制条件;    --having用在group by之后，各种限制条件都可以添加

• Navicat工具

• *多表设计

属于内连接查询

• 一对多

• 概念

• 一个对象A会对应多个对象B，从B的角度看一个对象B只会对应一个对象A。比如班级和学生的关系，一个班级有多个学生，而一个学生只会有一个班级。

• 场景

• 部门对员工、国家对省份、省份对城市、用户对订单、商品分类对商品

• 部门表和员工表:

部门表（dept）
did	dname
1	人事部
2	财务部

员工表(emp)
eid	name	address	esalary	did
1	张三	北京	3000	1
2	李四	上海	3000	1

• *约束

• 外键约束foreign key

--键入外键后，外键所在的表就无法随意删除
--再创建表时加在字段后面
--创建一个含有a,b,c三个字段的xx表，并键入外键c
create table xx(a,b,c,constraint fk_c/*外键别名:一般采用fk_外键名的格式*/ foregin key (c/*外键名*/) references 含有外键的表名(c/*外键名*/));

• 连接查询

• 练习

部门表dept
did	dname
1	人事部
2	财务部

员工表emp
eid	ename	esalary	did
1	张三	3000	1
2	李四	3500	1
3	王五	2000	1
4	赵六	5000	1
5	钱七	7000	2

• 练习内容

--查询出人事部所有员工的信息

--子查询
select * from emp where did=(select did from dept where dname=("人事部"));    --先查询出人事部在dept表中所对应的did，再根据对应的did查询出emp表中所有对应员工的信息

--两张表同时查
    --查询指令可以同时查找两张表，用,隔开；
    --在查询指令的where后的条件部分指明哪张表中的哪个字段=哪张表中的哪个字段。指明方式为：表名.字段名
select * from emp,dept where emp.did=dept.did;    --将两张表内容全部显示出来
    --可以给表名设置别名
select * from emp e,dept d where e.did=d.did;    --同上
    --添加人事部的条件，用and连接
select * from emp e,dept d where e.did=d.did and d.dname="人事部";    --先将两张表关联起来，再输出约束条件e.did=d.did and d.dname="人事部"同时满足的数据；其中e.did=d.did是将两张表连接起来，d.dname="人事部"就是输出数据的约束条件
    --筛选输出数据
select d.dname,e.ename,esalary from emp e,dept d where e.did=d.did and d.dname="人事部";

--查询出每个部门的总人数
select d.dname,count(*) 每个部门总人数 from dept d,emp e where e.did=d.did group by d.dname;

--查询出人事部的总人数
select d.dname,count(*) 人事部总人数 from dept d,emp e where e.did=d.did and d.dname="人事部";
    --等同于
    --简单写法
    select d.dname,count(*) 人数部总人数 from dept d,emp e where e.did=d.did group by d.dname having d.dname="人事部";
 
--查询出每个部门的总工资
select d.dname,sum(e.esalary) 部门总工资 from dept d,emp e where e.did=d.did group by b.bname;

--查询每个部门的平均工资
select d.dname,avg(e.esalary) 部门平均工资 from dept d,emp e where d.did=e.did group by d.dname;

• 多对多

• 场景

• 学生对老师，员工对项目

• 练习

学生表（student）
sid	sname
1	张三
2	李四
3	王五
4	赵六

中间表（st）
sid	tid
1	1
1	2
2	3
3	1
3	2
3	4

教师表（teacher）
tid	tname	tsubject
1	王老师	数学
2	李老师	语文
3	赵老师	英语
4	刘老师	物理

--查询出王老师教过的所有学生

--子查询
select tid from teacher where tname="王老师";    -- 根据老师名字在teacher表中查找tid
select sid from st where tid=(select tid from teacher where tname="王老师");    -- 根据查询出的tid在st表中查询对应的sid
select * from student where sid in (select sid from st where tid=(select tid from teacher where tname="王老师"));    -- 通过查找出的sid到student表中查看对应的学生信息。因为一个老师对应多个学生，且一个sid不能被同时赋予多个值所以不能采用=来表示，此处应该选择“或”关系也就是in。

-- 三表连接
select t.tname,sname from teacher t,st,student s where t.tid=st.tid and s.sid=st.sid and t.tname="王老师";    --通过中间表t.tid=st.tid and s.sid=st.sid将两张表连接起来，并声明约束t.name="王老师"后设置好要输出的字段，就可以得到王老师的所有学生。where后各条件之间应该用and连接

--查询出教过张三的所有老师
select t.tname,s.sname from teacher t,st,student s where t.tid=st.tid and s.sid=st.sid and s.sname="张三";    --同上

--查询出每个老师教过的学生数量
select t.tname,count(*) from teacher t,st,student s where t.tid=st.tid and s.sid=st.sid group by t.tname;    --将三表连接起来后，根据t.tname分组后使用count(*)查看每组数据量就得出每个老师教的学生数量

-- 统计王老师所教的学生数量
select t.tname,count(*) from teacher t,st,student s where t.tid=st.tid and s.sid=st.sid group by t.tname having t.tname="王老师";    --同上。
    --或者
    select t.tname,count(*) from teacher t,st,student s where t.tid=st.tid and s.sid=st.sid and t.tname="王老师" group by t.tname;

• 一对一

• 场景

• 一个学生对应一个学生档案材料

• 练习

--设置好想要输出的字段即可
-- 查询每个用户的所有信息
select u.*,ud.address,ud.age,ud.salary,ud.gender from user u,user_detail ud where u.idcard=ud.idcard;    --u.*代表u表的所有信息全部字段

-- 查询张三的所有信息
select * from user u,user_detail ud where u.idcard=ud.idcard and u.uname="张三";

-- 查询北京的用户
select * from user u,user_detail ud where u.idcard=ud.idcard and ud.address="北京";

• 连接查询

• 笛卡尔积查询

• 将两张表中的各项数据分别组合在一起：A表中x（2）条数据，B表中y（5）条数据，则输出10条数据（即x*y），不考虑其他条件

--笛卡尔积
select * from A,B;     --不添加任何约束
    --或者
    --数据库通用性写法
    --关键词cross join
    select * from A cross join B;

• 内连接

select * from A,B where A.a=B.a;    --通过两张表关联的数据将两张表连接起来
    --或者
    --数据库通用性写法
    --关键词inner join ... on ...
    select * from A inner join B on A.a=B.b;

• 左外连接

• 概念

将左表和右表进行关联，展示所有关联数据和左表中所有数据

--关键词left join ... on ...
select * from A left join B on A.a=B.b;

• 右外连接

• 概念

将左表和右表进行关联，展示所有关联数据和右表中的所有数据

--关键词right join ... on ...
select * from A right join B on A.a=B.b;

• 内外连接的区别

• 内连接返回两张表满足条件的所有数据

• 外连接除了返回两张表满足条件的所有数据，还会返回左表或者右表不满足条件的数据（左外连接返回左表，右外连接返回右表）

• 左外连接和右外连接的区别

• 左外连接除了返回两张表满足条件的所有数据，还会返回左表不满足条件的数据

• 右外连接除了返回两张表满足条件的所有数据，还会返回右表不满足条件的数据

• 备份和恢复数据库

• 通过指令备份

--在cmd中输入，不需进入mysql
mysqldump /*--no-defaults报错的话需要添加这个*/ -u用户名 -p 数据库名 > 要备份到的根路径\备份文件名称.sql

--在navicat中备份
右击数据库>转储SQL文件>结构和数据>选择路径，设置名称>开始

• 恢复数据库

--通过cmd指令恢复，不需进入mysql
mysql -u用户名 -p 数据库名 < 数据库文件路径\文件名.sql    --在cmd中使用

--进入mysql里面选择数据库使用
source 数据库文件路径\文件名.sql

--使用navicat
创建数据库>右击数据库>运行SQL文件>选择文件路径>执行

• 存储过程

• 概述

• 带有业务逻辑的SQL语句，里面有流程控制语句，if条件判断，while循环等

• 特点

• 执行效率非常快，存储过程是在数据库的服务器端执行的

• 移植性很差，不同数据库的存储过程是不能移植

• 创建存储过程

-- 创建存储过程
create procedure 存储过程的名字（IN/OUT 参数名字 参数类型）    -- IN输入参数，OUT输出参数；  
begin    -- 开始
编写SQL语句和业务逻辑;
end    -- 结束

-- 调用规则
call 存储过程的名字;

-- eg：
-- 查询person表
delimiter $$    -- 防止因为;将SQL语句提前结束导致最后的编写不出来，将结束符号修改为$$
create procedure selectperson()
begin
select * from person;    
end$$
delimiter ;    -- 编写完后将结束符号修改回;

• 什么是事务

• 概念

• 作为单个逻辑工作单元执行的一系列操作，由多条语句组成的集合。要么全部成功，要么全部失败，数据库通过事务来保证数据的一致性。

• 特点

• 原子性：事务是不可分割的最小单元

• 一致性：要么完全成功，要么完全失败

• 隔离性：各个事务之间，不互相影响

• 持久性：数据发生变化时，就是持久性变化

• 场景

• 事务广泛的运用于订单系统、银行系统等多种场景

• 执行演示

begin    -- 开启事务
sql语句
commit;    -- 都成功，手动提交
rollback;    -- 任何一部分失败了，回滚，返回最初状态

-- eg：
if 正确:
    begin:    -- 开始事务
        sql逻辑...
    commit:    -- 都成功，手动提交
else 错误:
    begin:    -- 开始事务
        sql逻辑...
    rollback:    -- 都成功，手动提交

• DCL语言

• 数据控制语言