数据库

数据库（database）

数据的仓库，它是保存和管理数据的仓库

如果希望在程序中实现数据持久化操作，数据库就是一种非常好的解决方案

BATCMD - 百度、阿里、腾讯、携程、美团、滴滴、…

IOE —> IBM小型机 / Oracle数据库 / EMC存储设备 —> 贵

X86服务器 / MySQL / fastDFS —> 去IOE运动 —> 性价比非常高

数据的分类：

1. 关系型数据库（SQL）
   1. 理论基础：关系代数 + 集合论
   2. 具体表象：用二维表保存数据
      1. 行：记录
      2. 列：字段（属性）
   3. 编程语言：结构化查询语言（Structured Query Language）
      1. DDL（数据定义语言,用来定义数据库对象：库、表、列等）：create、drop、alter
      2. DML（数据操作语言,用来定义数据库记录（数据））：insert、delete、update
      3. DQL（数据查询语言,用来查询记录（数据）：select
      4. DCL（数据控制语言，用来定义访问权限和安全级别）
2. 非关系型数据库（NoSQL）
3. NewSQL数据库

Hadoop —> Hive / PIG

关系型数据库产品：

1. Oracle
2. MySQL
3. PostgreSQL
4. SQL Server
5. DB2
6. SQLite

MySQL

1. 安装MySQL

   1. RPM - Redhat Package Manager

2. 启动MySQL

   1. systemctl start mysqld / systemctl stop mysqld / systemctl restart mysqld
   2. systemctl status mysqld / netstat -ntlp ? grep mysql

3. 使用客户端工具连接MySQL服务器

   1. mysql -u root -p

      —> mysql> alter user ‘toot’@‘localhost’ identified by ‘your passward’;

      mysql> show databases; —> 显示所有数据库

      注意,写完后要加分号（;）表示一句结束

MySQL命令：

1. show databases; —> 查看所有数据库
2. show tables; —> 查看当前数据库下所有的表

SQL

1. 创建数据库：create database school default charset utf8mb4;

2. 删除数据库：drop database if exists school；

3. 切换数据库：use school;

4. 创建二维表：

   1. create table tb_student (

      stuid integer not null,

      stuname varchar(20) not null,

      stusex char(1) / stusex boolean default 1,

      stubirth date,

      stuaddr varchar(100)，

      primary key (stuid)

      );

   2. 主键（primary key）列：能够唯一确定一条记录的列。

5. 查看表结构：

   1. desc tb_student;

6. 删除二维表：

   1. drop table if exists tb_student;

7. 修改二维表：

   1. 添加列：alter table tb_student add column stutel char(11) not null;

   2. 删除列：alter table tb_student drop column stutel;

   3. 修改列：

      alter table tb_student change column stusex stugender boolean default 1;

      alter table tb_student modify column stuaddr stuaddr varchar(250);

8. 图形化客户端工具：

   1. MySQL Workbench
   2. Navicat for MySQL —> 简单、优雅、付费
   3. SQLyog —> EE / CE
   4. Toad for MySQL

9. 修改root账号，允许远程连接。

   1. use mysql;
   2. update user set host=’%’ where user=‘root’;
   3. flush privileges;

Navicat

创建表以及录入信息

create table tb_college(
	collid int not null auto_increment comment '学院编号',
	collname varchar(50) not null comment '学院名称',
	intro varchar(500) default '' comment '介绍',
	teachers_count int unsigned not null comment '员工数量',
	fdate date comment '成立时间',
	primary key (collid)
);

-- 修改学院表给学院名称添加唯一性约束
alter table tb_college add constraint un_college_name unique(collname);

-- 修改学生表添加学员编号列
alter table tb_student add column cid int not null;

-- 修改学生表添加一个外键约束
-- 外键(foreign key)：外来的主键
alter table tb_student add constraint fk_student_collid foreign key (cid)
references tb_college (collid);

-- alter table tb_student add column collid int not null foreign key (collid) references tb_college (collid) 

-- 插入数据
insert into tb_college values (default, '计算机学院', '', 128, '1985-12-5');
insert into tb_college (collname, teachers_count, fdate) values ('外国语学院', 100, '1970-12-5');
insert into tb_college (collname, teachers_count, fdate) values 
('机电学院', 100, '1970-12-5'),
('理学院', 115, '1991-2-7'),
('经济管理学院', 120, '1998-3-1');

-- 删除
delete from tb_college where collid=3;

-- 更新修改数据
update tb_college set teachers_count=80, fdate='1966-1-1', intro='在党和国家的支持下创建的学院' where collid=2;

alter table tb_teacher add column cid int not null;

alter table tb_teacher add constraint fk_teacher_collid foreign key (cid)
references tb_college (collid);

MySQL查询表

1. select * from 表;—> 实际中不建议写 * 号

   select * from tb_student;
   

2. select column from 表 —> 建议使用这个，查询所有列

   select stuid, stuname, stusex, stubirth, stuaddr, collid from tb_student;
   

3. case when else 分支

   select stuname as 姓名,  case stusex when 1 then '男' when 2 then '未知' else '女' end as 性别 from tb_student;
   

4. 筛选操作：where

   1. 一个条件的

   select stuname, stubirth from tb_student where stusex=0;
   

   2. 筛选两个条件同时成立的

   -- 在执行筛选操作时，如果有多个条件，可以通过and和or关键字进行组合
   -- 也可以使用not讲条件变成其对立面（逻辑变反）
   -- 两种写法: 
   select stuname, stusex, stubirth from tb_student where 
   stubirth >= '1980-1-1' and stubirth <= '1989-12-31';
   
   select stuname, stusex, stubirth from tb_student where 
   stubirth between '1980-1-1' and '1989-12-31';
   

   3. 模糊查询
      • % —> 表示零个或多个字符
      • _ —> 表示一个字符

   select stuname, stusex from tb_student where stuname like '杨%';
   select stuname, stusex from tb_student where stuname like '杨_';
   

   4. union —> 并集操作（去重）
   5. union all —> 把重复的也选出来

   select stuname from tb_student where stuname like '%不%'
   union -- union all
   select stuname from tb_student where stuname like '%嫣%';
   

   6. 判断空值和非空值
      • is null
      • is not null

   select stuname from tb_student where  stuaddr is null;
   select stuname from tb_student where  stuaddr is not null;
   

5. 筛选：having

   1. 分组之前的筛选使用where子句，分组之后的用having子句

   select sid, round(avg(score), 1) as avgscore from tb_record group by sid having avgscore>=90;
   

   • round() —> 指定保留多少位小数
   • group by —> 分组

6. 去重：distinct

   select distinct seldate from tb_record;
   

7. 排序：order by

   • asc —> 升序(默认)
   • desc —> 降序

   select stuname, stubirth from tb_student where stusex=1 order by stubirth;
   

8. 将年龄计算出来：

   • floor —> 向小取整（取不超过指定数值的最大的整数）
   • datediff —> 计算两个日期相差多少天
   • curdate() —> 获取当前日期

   select stuname ,floor(datediff(curdate(), stubirth)/365) as age from tb_student where stusex=1 order by age desc;
   

   聚合函数（在使用聚合函数的时候，null值自动被忽略）

   1. max() / min() —> 求最大，最小

   select min(stubirth) from tb_student;
   

   2. avg() —> 平均数

   select avg(score) from tb_record where cid=1111;
   

   3. count() —> 计数

   select count(sid) from tb_record where cid=1111;
   

   4. sum() —> 求和

   select sum(score) from tb_record where cid=1111;
   

   嵌套查询：将一个查询的结果作为另外一个查询的一部分来使用，也叫子查询

   select stuname from tb_student where stubirth=(select min(stubirth) from tb_student);
   

   1. 判断元素在不在集合中的成员运算：in / not in

   select stuname from tb_student where stuid in (select sid from tb_record group by sid having count(sid)>2);
   

   连接查询

   查询每门课程的名称、学分和授课老师的姓名(连接查询/连表查询)
   连接两张表的时候，如果没有连接条件就会形成笛卡尔乘积（9*5=45）

   select couname, coucredit, teaname from tb_course, tb_teacher where tb_course.teaid=tb_teacher.teaid
   

   1. 将嵌套查询得到的子查询表来进行连接查询

   -- 查询每个学生的姓名和平均成绩(嵌套查询+连接查询)
   select stuname, avgscore from tb_student t1, (select sid, round(avg(score), 1) as avgscore from tb_record group by sid) t2 where t1.stuid=t2.sid;
   

   2. 按数量取记录n
      • limit n：只取前面的n条
      • limit n offset m：跳过前面m条，取n条 —> 简写：limit n, m

   -- 按成绩排降序，取前面5条记录
   select stuname, couname, score from tb_student t1, tb_course t2, tb_record t3 where stuid=sid and couid=cid and score is not null order by score desc limit 5;
   
   -- 按成绩排降序，跳过前面5条，取6-10条记录
   select stuname, couname, score from tb_student t1, tb_course t2, tb_record t3 where stuid=sid and couid=cid and score is not null order by score desc limit 5 offset 5;
   

   3. 外连接：左外、右外、全外（MySQL不支持）
      内连接：只能查出满足连表条件的记录
      左外连接：保证左表的记录要完整的查出来，即便它并不满足连表条件,不满足连表条件的地方填充null
      查询语句出现在前面的表叫左表，出现在后面的表叫右表

   -- 查询每个学生的姓名和选课数量(左外连接和子查询)
   select stuname, ifnull(total, 0) from tb_student t1 left outer join (select sid, count(sid) as total from tb_record group by sid) t2 on t1.stuid=t2.sid;
   

   • ifnull(n, 0) —> 如果n是null，那么就把它修改为0

   4. 一般情况下写SQL的时候，尽量避免使用distinct、in、not in运算，因为它们的性能都很差

      可以考虑使用存在性判断（exists、not exists）来替代去重集合成员运算

   select ename, job from tb_emp t1 where exists (select 'x' from tb_emp t2 where t1.eno=t2.mgr);
   

   总结

   一般查询语句的书写顺序：

   select + from + where + group by + having + order by + limit

使用python连接数据库

1. 点击python的 get from version control
2. 将你的仓库地址粘贴进去
3. 然后对这个项目创建虚拟环境
4. 最后在虚拟环境的终端里面安装三方库：pip install mysqlclient

插入数据

import MySQLdb as mysql

no = int(input('编号: '))
name = input('部门: ')
location = input('地址: ')
# 第1步: 创建数据库连接，获取连接对象
conn = mysql.connect(host='公网IP', port=3306,
                     user='root', password='你的数据库密码',
                     database='hrs', charset='utf8mb4',
                     connect_timeout=3)
try:
    # 第2步: 基于连接对象创建游标对象
    with conn.cursor() as cursor:
        # 第3步: 通过游标对象向数据库发出SQL语句并获取执行结果
        # affected_rows = cursor.execute('insert into tb_dept values (90, \'研发2部\', \'武汉\')')
        # SQL语句一定要避免格式字符化
        affected_rows = cursor.execute(
            'insert into tb_dept values (%s, %s, %s)',
            (no, name, location)
        )
        if affected_rows == 1:
            print('新增部门成功！！！')
    # 第4步: 操作成功: 通过连接对象执行提交操作（让之前的操作生效）
    conn.commit()
except mysql.MySQLError as err:
    print(err)
# 第4步: 操作失败: 通过连接对象执行回滚操作（撤销之前的操作）
    conn.rollback()
finally:
    # 第5步: 关闭连接，释放资源
    conn.close()

查询数据

第一种：得到元组
with conn.cursor() as cursor:
    affected_rows = cursor.execute(
        'select dno, dname, dloc from tb_dept'
    )
    # 第4步: 通过游标抓取数据
    for row in cursor.fetchall():
        print(row)

第二种：得到字典
with conn.cursor(mysql.cursors.DictCursor) as cursor:
    affected_rows = cursor.execute(
        'select dno as no, dname as name , dloc as location from tb_dept'
    )
    # 第4步: 通过游标抓取数据
    for row in cursor.fetchall():
        print(row)