数据库简介

• 数据库系统
  • 数据库
    • DataBase【DB】，指的是长期保存到计算机上的数据，按照一定顺序组织，可以被各种用户或者应用共享的数据集合
  • 数据库管理系统
    • DataBase Management System【DBMS】,能够管理和操作数据库的大型的软件
    • 数据库是CS模式:Client/Server
    • 用于建立、使用和维护数据库，对数据库进行统一的管理和控制，为了保证数据库的安全性和完整性，用户可以通过数据库管理系统访问数据库中的数据
  • 数据库的应用
    • 涉及到大量的数据需要长期存储，就可以使用数据库 ，持久化MySQL，缓存Redis
• 常见的数据库管理系统
  • Oracle（甲骨文）
  • DB2: IBM
  • SQL Server: Microsoft
  • MySQL: 免费的数据库系统，>sun>Oracle

安装MySQL

• 验证是否安装MySQL
  • mysql
• 安装 wget 软件
  • yum install wget
• 下载仓库文件
  • wget https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm
• 安装仓库文件
  • yum localinstall mysql80-community-release-el7-1.noarch.rpm
• 安装 YUM 管理工具包
  • yum install yum-utils
• 禁用 8.0 仓库
  • yum-config-manager --disable mysql80-community
• 启用 5.7 仓库
  • yum-config-manager --enable mysql57-community
• 安装 MySQL
  • yum install -y mysql-community-server
• 启动
  • systemctl status mysqld
• 设置开机自动启动此服务
  • systemctl enable mysqld
• 查看监听端口，默认 3306
  • ss -ntal
• 找到管理员账号的初始密码
  • grep password /var/log/mysqld.log
• 使用客户端程序 mysql 链接到 MySQL Server
  • mysql -u root -p‘YrgT95odi:vJ‘
• 修改初始密码
  • alter user root@localhost identified by ‘QFedu123!‘;
• 退出数据库交互程序
  • exit;
• 取消密码复杂度
  • 编辑 my.cnf配置文件, 在 [mysqld]配置块儿中添加如下内容
    • plugin-load=validate_password.so
    • validate-password=OFF
  • 保存退出后，重启服务, 修改密码。

SQL概述

• SQL简介
  • Structure Query Language，结构化查询语言
• 数据库服务器、数据库和表之间的关
  • 数据库服务器就像是一个仓储公司，这个公司可以有多个仓库(数据库)，每个仓库中有多个表。
• 数据在数据库中的存储形式
  • 数据库>表>字段>行（代表一条数据， 一条记录 ， 一个实体）
• SQL的分类
  • DDL
    • data definition language
  • DML
    • data manipulation language
  • DQL
    • data query language
  • DCL
    • data control language
  • 数据管理命令
  • 事务控制命令

数据库操作

• DDL
  • 使用关键字：CREATE ALTER DROP
  • 一般情况下，mysql关键字是大写的，但是为了方便，一般小写
  • create创建
    • 创建数据库
      • CREATE DATABASE database_name charset=utf8;
    • 创建表
      • create table user(
        id int primary key auto_increment,
        name varchar(20),
        age int
        );
  • show databases;
    • 查询当前数据库服务器中的所有数据库
  • select database();
    • 查看当前正在使用的数据库
  • use mydb1;
    • 切换数据库
  • alter操作
    • ALTER TABLE old_table_name RENAME [TO] new_table_name
      • 修改表名
      • alter table worker rename to user;
    • ALTER TABLE table_name MODIFY 字段名 数据类型
      • 修改字段的数据类型
      • DESC table_name
        • 检验结果
    • ALTER TABLE table_name CHANGE 旧字段名 新字段名 数据类型
      • 修改字段名
    • ALTER TABLE table_name ADD 新字段名 数据类型 [约束条件] [FIRST|AFTER 已经存在的字段名]
      • 添加字段
    • ALTER TABLE table_name DROP 字段名
      • 删除字段
    • ALTER TABLE table_name MODIFY 字段1 数据类型 FIRST|AFTER 字段2
      • 修改字段的排列位置
    • ALTER TABLE table_name DROP FOREIGN KEY 外键约束名
      • 删除表的外键约束
    • DROP TABLE [IF EXISTS] 表1，表2...
      • 删除没有被关联的表
        • 如果是关联的表，先解除关联 再进行删除
  • 常用数据类型
    • 数字数据类型
      • INT - 正常大小的整数，可以带符号
      • TINYINT - 一个非常小的整数，可以带符号
      • SMALLINT - 一个小的整数，可以带符号
      • MEDIUMINT - 一个中等大小的整数，可以带符号
      • BIGINT - 一个大的整数，可以带符号
      • FLOAT(M,D) -可以定义显示长度(M)和小数位数(D)，默认10，2
      • DOUBLE(M,D) - 不能使用无符号的双精度浮点数，默认16，4
        • real与double同义
      • DECIMAL(M,D) - 非压缩浮点数不能是无符号的，定义显示长度(M)和小数(D)的数量是必需的
        • numeric与decimal同义
    • 日期和时间类型
      • DATE - 以YYYY-MM-DD格式的日期，在1000-01-01和9999-12-31之间
      • DATETIME - 日期和时间组合以YYYY-MM-DD HH:MM:SS格式，在1000-01-01 00:00:00 到9999-12-31 23:59:59之间
      • TIMESTAMP - 1970年1月1日午夜之间的时间戳
      • TIME - 存储时间在HH:MM:SS格式
      • YEAR(M) - 以2位或4位数字格式来存储年份
    • 字符串类型
      • CHAR(M) - 固定长度的字符串是以长度为1到255之间个字符长度
      • VARCHAR(M) - 可变长度的字符串是以长度为1到255之间字符数
      • BLOB or TEXT - 字段的最大长度是65535个字符
      • TINYBLOB 或 TINYTEXT
      • MEDIUMBLOB or MEDIUMTEXT
      • LONGBLOB 或 LONGTEXT
      • ENUM - 枚举
        • 当定义一个ENUM，要创建它的值的列表，这些是必须用于选择的项(也可以是NULL)。例如，如果想要字段包含“A”或“B”或“C”，那么可以定义为ENUM为 ENUM(“A”，“B”，“C”)也只有这些值(或NULL)才能用来填充这个字段。
    • show tables;
      • 显示当前数据库中的所有表
    • desc worker;
      • 显示指定表中的所有字段
    • show create table user;
      • 查看创建表的详细信息
      • show create table user\G
    • alter table user character set gbk;
      • 修改表的字符集为gbk
    • alter table user change name username varchar(100);
      • 将列名name修改为username
    • DROP DATABASE database_name；
      • 删除数据库
    • drop table user;
      • 删除表
• DML
  • insert插入
    • INSERT INTO table_name (field1, field2,...fieldN) VALUES(value1, value2,...valueN);
      • 单行插入
    • INSERT INTO table_name (field1, field2,...fieldN)
      VALUES
      (value1, value2,...valueN),
      (value12, value22,...valueNN)...;
      • 多行插入[批量插入]
    • 如果插入的是空值，写Null/null
    • insert into worker values(3,‘rose‘,‘g‘,6000);
      • 如果给每个字段都赋值，就可以省略掉字段的书写
    • select * from worker;
      • 查询表的所有数据
    • 从另一个表插入数据
      • insert into t2
        select * from t1
        • 可以插入指定字段，可以使用where子句
  • update更新
    • UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]
    • update worker set salary=5000;
      • 将所有员工的薪水修改为5000
      • update worker set salary=3000 where name=‘tom‘;
    • update worker set salary=salary+1000 where name=‘rose‘;
  • where子句
    • 类似于Python中的if语句，可以做数据的筛选
    • = != < > <= >= in(A,B) between A and B AND
  • delete删除
    • DELETE FROM table_name [WHERE Clause]
      • 如果where子句没有指定，则默认将表中的数据全部删除【慎用！】
    • delete from worker where name=‘tom‘;
      • 删除表中tom的全部数据
    • truncate table worker;
      • Truncate是一个能够快速清空资料表内所有资料的SQL语法。并且能针对具有自动递增值的字段，做计数重置归零重新计算的作用。
    • delete:删除表中的指定数据，表结构还在，删除之后的数据可以找回，对自动增加的字段无影响
      truncate：清空表中的数据，删除的数据是不能找回的，执行速度比delete快，自动增加的字段会重新计数
      drop: 删除表，数据和表结构都删除
• DQL
  • 数据库执行DQL语言不会对数据库中的数据发生任何改变
  • SELECT 列名 FROM 表名【WHERE --> GROUP BY -->HAVING--> ORDER BY】
  • 基础查询
    • select * from student;
      • 查询所有列
    • select id,name,gender from student;
      • 查询指定列
  • 条件查询
    • 主要结合where的使用
      • in / not in：类似于Python中的成员运算符
      • is / is not: 类似于Python中的身份运算符 , 常用语判断null值， 如：name is null
    • select * from student where gender=‘female‘ and age=20;
      • 查询性别为女，并且年龄为20的记录
    • select * from student where age is null;
      • 查询年龄为null的记录
  • 模糊查询
    • where 子句中=表示精准查询
      • like/not like：一般情况下结合where子句使用
      • 通配符
        • _: 匹配一个任意字符
        • %：匹配0~n个任意字符【n大于等于1】
    • select * from student where name like ‘____‘;
      • 查询姓名由4个字符组成的记录
    • select * from student where name like ‘a%‘;
      • 查询以a开头的记录
  • 字段控制查询
    • as: 起别名，用法 ：select 字段 as 别名
      distinct: 去除重复记录
    • select distinct id from student;
      • 去除重复记录
    • select name as 姓名,gender as 性别 from student;
      • 给列名起别名
      • select name 姓名1,gender 性别1 from student;
  • 排序
    • order by:指定数据返回的顺序
      • asc
        • 升序
      • desc
        • 降序
    • select * from student order by age asc;
      • 查询所有的记录，按照年龄升序排序
    • select * from student order by age desc,id asc;
      • 查询所有学生记录，按照年龄降序排序，如果年龄相等，则按照编号进行升序排序
    • 聚合函数
      • 聚合函数主要用来做纵向运算
      • 查询关键字的书写顺序：select 聚合函数 from where order by
    • count():统计指定列不为null的记录行数
      • select count(*) from student where age>20;
        • 查询年龄大于20的人数
    • sum():计算指定列的数值和
      • select sum(age) from student;
        • 查询所有学生的年龄和
    • select max(age),min(age) from student;
      • 求最大年龄和最小年龄
    • average:平均数
      • select avg(age) from student;
    • 分组查询
      • group by：分组查询
        having：有...,表示条件，类似于where的用法
      • having和where的区别：having和where的区别：
        a.二者都表示对数据执行条件
        b.having是在分组之后对数据进行过滤
        where是在分组之前对数据进行过滤
        c.having后面可以使用聚合函数
        where后面不可以使用聚合函数
      • select count(*) from emp group by deptno;
        • 查询各个部门的人数
      • select deptno,sum(sal) from emp group by deptno;
        • 查询每个部门的部门编号和每个部门的工资和
      • select deptno,sum(sal) from emp group by deptno having sum(sal)>7000;
        • 查询工资总和大于7000的部门编号以及工资和
      • select deptno,sum(sal) from emp where sal>1500 group by deptno having sum(sal)>6000;
        • 查询工资大于1500，工资总和大于6000的部门编号和工资和
    • 分页查询
      • limit：用来限定查询的起始行，以及总行数
      • select * from emp limit 0,4;
        • 查询4行记录，起始行从0开始
    • 查询语句书写顺序：select----》from---》where---》group by-----》having-----》order by----->limit
      • select deptno,sum(sal) from emp where sal>1500 group by deptno having sum(sal)>5000 order by sum(sal) asc limit 4;

数据的完整性

• 作用与实质
  • 作用
    • 保证用户输入的数据保存到数据库中是正确的
  • 实质
    • 创建表的时候给表中的字段添加约束
• 实体完整性
  • 实体完整性的作用：标识每一行数据不重复
  • 主键约束/唯一约束/自动增长列
    • 主键约束
      • 数据唯一，且不能为null
      • 主键可以是表中的一个字段或者多个字段，它的值用来唯一标识表中的某一条记录
      • create table stu1(id int primary key);
      • create table stu1(id int,primary key (id));
      • create table stu1(id int);
        alter table stu1 add constraint stu1_id primary key (id);
    • 唯一约束
      • 在非主键列中不能输入重复的值
      • create table stu2(id int primary key,name varchar(50) unique);
    • 自动增长列
      • 给主键（一般添加给主键）添加自动增长性，列只能是整数类型
      • create table stu3(id int primary key auto_increment);
      • create table stu4(id serial);
        • 相当于create table stu4(id int primary key auto_increment);
• 域完整性
  • 作用
    • 限制单元格数据的正确性，域代表当前单元格
  • 约束类型
    • 非空约束
      • not Null
        • create table stu6( id int primary key auto_increment, name varchar(50) unique not null);
    • 默认值约束
      • default
        • create table stu7(id int primary key auto_increment,name varchar(50) unique not null,address varchar(50) default "beijing");
          insert into stu7 (id,name,address) values(2,‘bbb‘,default);
    • 外键约束
      • foreign key
        • 添加外键必须先有主键，主键和外键的类型必须保持一致
      • 作用
        • 将两个甚至多个表产生联系
          • 生产上不使用外键约束
      • create table score1( score int, courseid int,stuid varchar(10), constraint stu_sco_id foreign key(stuid) references student(stuid) );
        • 为自己的stuid字段加上外键
      • alter table score2 add constraint stu_sco_id2 foreign key(stuid) references student(stuid);

多表查询

• 表与表之间的关系
  • 一对一
    • 通过嵌套的方式
  • 一对多/多对一
    • 添加外键
  • 多对多
    • 单独创建一张新的表
      • 例：学生表与老师表，一个学生可以选多个老师的课，一个老师可以教多个学生
• 合并结果集
  • 作用
    • 将两个select语句的查询结果合并到一起
  • union
    • 去除重复记录（并集）
      • select * from A union select * from B;
  • union all
    • 获取所有结果
      • select * from A union all select * from B;
  • 如果列数不相同
    • select * from A union select name,score from C;
• 连接查询
  • 作用
    • 求出多个表的乘积，例如t1和t2，如果采用了连接查询，得到的结果是t1*t2
      • 笛卡尔积
      • 在实际应用中，需要去除重复记录，则需要通过条件进行过滤
      • 多表联查用where过滤（通过字段相等连接），或者使用子查询的办法代替
  • 内连接
    • 查询结果必须满足条件
    • select s.stuid,s.stuname,c.score,c.courseid from student s join score c on s.stuid=c.stuid;
  • 外连接
    • 左连接
      • select s.stuid,s.stuname,c.score,c.courseid from student s left join score c on s.stuid=c.stuid;
    • 右连接
      • select s.stuid,s.stuname,c.score,c.courseid from student s right join score c on s.stuid=c.stuid;
• 子查询
  • 在一个select语句中包含另外一个完整的select语句【select语句的嵌套】
  • 子查询出现的位置
    • from后，作为外层语句的源数据
    • where子句的后面，作为条件的一部分被查询
  • 当子查询出现在where后面作为条件时，可以使用关键字：any、all、some
    • select * from emp where sal>any(select sal from emp where name=‘smith‘ or name=‘allen‘ or name=‘ward‘);
      • some与any的作用一致
    • select * from emp where sal>all(select sal from emp where enname=‘allen‘ or enname=‘scott‘);
  • select * from emp where deptno=(select deptno from emp where enname=‘scott‘);
• 自关联
  • 正向查询
    • select * from department where id=(select id department where name=‘行政部‘);
  • 逆向查询
    • select * from department where up_dept_no=(select up_dept_no from department where name=‘行政部‘);

快速添加数据

• 在shell脚本中写好添加语句
• 在mysql中source shell脚本

数据库的备份与恢复

• 备份
  • mysqldump -u root -p 数据库名>生成sql脚本的路径
    • 生成SQL脚本，导出数据
    • 逻辑备份
  • 恢复
    • 执行sql脚本，恢复数据
      • 前提：必须先创建数据库【空的】
    • 登陆数据库
      • create database test;
        • use test;
          • source /home/rock/Desktop/mydb1.sql;

索引和事务

• 索引
  • 简介
    • 索引用于快速找出在某个列中有一特定值的行。
  • 分类
    • 普通索引
      • 最基本的索引，它没有任何限制
        • alter table 表 add index(字段)
        • alter table money add index(username);
    • 唯一索引
      • unique
        • alter table 表 add unique(字段)
        • alter table money add unique(email);
    • 主键索引
      • primary key
        • alter table 表 add primary key(字段)
        • alter table money add primary key(id);
        • 添加主键索引与添加主键是一样的
          • alter table money add constraint id_pk primary key (id)
    • 全文索引
      • 对于需要全局搜索的数据，进行全文索引
  • 查看索引
    • show index from tablename\G
      • show index from user\G
  • 创建表时添加索引
    • create table user (id int auto_increment,name varchar(20), primary key(id),unique (name));
  • 删除索引
    • ALTER TABLE table_name DROP INDEX index_name
    • alter table money drop index age;
    • alter table money drop primary key;
      • 删除主键索引
• 事务
  • 事务控制语言TCL
    • 我们每执行一条SQL语句,每执行一组SQL语句,我们都可以称为事务
    • 事务可以看作是一个“容器”，将多条语句，放入该容器，最后，只要一个命令行，来决定其中的所有语句是否“执行”
    • 一般在脚本中使用
  • 事务的四大特征
    • 原子性/不可分割性
      • 一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节
    • 一致性
      • 在事务开始之前和事务结束以后，数据库的完整性没有被破坏
    • 隔离性/独立性
      • 隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。
    • 持久性
      • 事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失
  • 事务模式
    • start transaction开头,开启事务模式=>
      必须以此开启事务模式
      • commit 语句执行后,才能真正生效
      • rollback 语句进行回滚
      • savepoint savepoint_name
        • 添加回滚点
        • rollback to sp1;

Python操作MySQL

• MySQL授权
  • 关闭防火墙
    • systemctl stop firewalld
      systemctl disable firewalld
  • use mysql
    • select * from user\G
  • grant all on . to root@‘%‘ identified by ‘passwd‘;
    • # 删除权限
      drop user ‘用户名‘@‘客户端来源IP地址‘;
  • Linux内远程连接
    • mysql -u root -p -h SQL_Sever的ip -P 3306
• 连接MySQL
  • import pymysql
  • conn = pymysql.connect(‘localhost‘, ‘root‘, ‘root‘, ‘mydb2‘)
    • 主机或ip地址/用户名/密码/数据库
    • 区别本地连接和远程连接
• 创建游标对象
  • cursor = conn.cursor()
  • 游标用来执行sql语句
• sql语句
  • sql = ‘select version()‘
    • 查看MySQL版本
• 执行sql语句
  • cursor.execute(sql)
• 关闭游标对象及连接
  • cursor.close()
  • conn.close()

# Python操作MySQL
# 关闭防火墙（在MySQL Sever运行的机器上）
# systemctl stop firewalld
# systemctl disable firewalld
# setenforce 0
# sed -ri ‘/SELINUX=/ c SELINUX=disabled‘ /etc/selinux/config

import pymysql

# 1-连接mysql
# 参数1：表示主机或ip地址
# 参数2：表示mysql的用户名
# 参数3：表示mysql的密码
# 参数4：表示mysql的数据库名
conn = pymysql.connect(‘192.168.88.130‘, ‘root‘, ‘nzw19940611‘, ‘mydb1‘)

# 创建游标对象: 可以执行sql语句
cursor = conn.cursor()

# sql语句
sql = ‘select version()‘

# 执行sql语句
cursor.execute(sql)

res = cursor.fetchone()
print(res)

# 关闭游标对象
cursor.close()

# 关闭连接
conn.close()



# 2-插入数据
# 建立连接与游标
conn = pymysql.connect(‘192.168.88.130‘, ‘root‘, ‘nzw19940611‘, ‘mydb1‘)
cursor = conn.cursor()

# sql语句及插入数据
sql = ‘insert into department(id, name, up_dept_no) values (7, "工程服务经营部", 1);‘

try:
    cursor.execute(sql)
    # 提交事务
    conn.commit()
except:
    # 回滚
    conn.rollback()

# 关闭游标及连接
cursor.close()
conn.close()



# 3-删除数据
# 建立连接与游标
conn = pymysql.connect(‘192.168.88.130‘, ‘root‘, ‘nzw19940611‘, ‘mydb1‘)
cursor = conn.cursor()

# sql语句及删除数据
sql = ‘delete from department where id=7;‘

try:
    cursor.execute(sql)
    # 提交事务
    conn.commit()
except:
    # 回滚
    conn.rollback()

# 关闭游标及连接
cursor.close()
conn.close()



# 4-更新数据
# 建立连接及游标
conn = pymysql.connect(‘192.168.88.130‘, ‘root‘, ‘nzw19940611‘, ‘mydb1‘)
cursor = conn.cursor()

# sql语句及更新数据
sql = ‘update department set name="第三营销事业部" where id=7;‘

try:
    cursor.execute(sql)
    # 提交事务
    conn.commit()
except:
    # 回滚
    conn.rollback()

# 关闭游标及连接
cursor.close()
conn.close()



# 5-查询数据
# 建立连接及游标
conn = pymysql.connect(‘192.168.88.130‘, ‘root‘, ‘nzw19940611‘, ‘mydb1‘)
cursor = conn.cursor()

# sql语句及插叙数据
sql = ‘select * from department;‘

# 查询数据
cursor.execute(sql)
print(cursor.fetchone())
print(cursor.fetchone())
print(cursor.fetchmany(2))
print(cursor.fetchall())
print(cursor.rowcount)

# 关闭游标与连接
cursor.close()
conn.close()

1-MySQL