关系型数据库--Mysql数据类型、数据增删改查的使用

1.初识数据库

# 初识数据库
    # 什么是数据库
    # 什么mysql
# 安装数据库
# 操作数据
    # 和用户 权限相关的 了解***
    # 基本操作   *****

# 了解数据在程序中的作用


# 服务器
    # 提供服务的机器
# 百度公司提供百度业务服务的机器 :百度服务器
# 输入法 服务器
# 浏览器 服务器

# 数据库服务器
    # 提供数据库服务器

# 关系型数据库   相对慢
# 非关系型数据库 相对快
    # 快递 快递单号
    # 视频 电影的id : 电影的内容

# mysql 5.6 经典版本安装
"""
1.安装后要修改配置文件，需要复制到pycharm修改**
2.设置mysql的安装目录
basedir=D:\mysql-5.6.44-winx64
设置mysql数据库的数据的存放目录
datadir=D:\mysql-5.6.44-winx64\data
3.设置环境变量
D:\mysql-5.6.44-winx64\bin
4.执行mysqld install和net start mysql
"""

2.Mysql的命令

# 环境变量
# python -->python.exe
# 在任何目录下都能够找到python.exe文件
# 才能在任意位置输入python命令启动python解释器

# mysqld install  安装mysql服务  mysql服务就被注册到操作系统中   点击服务查看程序运行
# net start mysql 启动mysql服务
# net stop mysql

# 启动客户端连接server
# mysql -uroot -p123 -h192.168.14.12

# mysql>select user();   查看当前登录的用户
# mysql>select database(); 查看当前数据库
# mysql>show create table engine1; 查看表结构
# mysql>set password = password('123'); 给当前用户设置密码
#
# 创建一个其他用户
# create user 'guest'@'192.168.14.%' identified by '123';  指示%网段，密码
# 给一个用户授权
# grant 权限类型 on ftp.* to 'guest'@'192.168.14.%';
# grant all
# grant select on day37.* to 'guest'@'192.168.14.%';
# grant select,insert
# mysql> flush privileges;  # 刷新使授权立即生效


# 操作数据库
# 查看所有数据库  show databases;
# 创建一个数据库  create database 数据库名;
# 切换到这个库下  use 数据库的名字
# 查看这个库下有多少表 show tables;

# 操作表
# 创建一张表
# create table student(name char(12),age int);
# 查看表结构
# desc student;

# 操作数据
# 插入数据 : insert into student values ('wusir',73);
# 查询数据 : select * from student;
# 修改数据 : update student set age=85 where name='alex';
# 删除数据 : delete from student where name = 'alex';

3.内容回顾

# 数据库 : DB
    # 所有的数据存放的仓库
    # 每一个文件夹也是一个数据库
# 数据库管理系统  -- 软件 DBMS
    # 关系型数据库 : mysql oracle sqllite sql server db2 access
    # 非关系型数据库 : redis mongodb memcache
# 数据库管理员  DBA
    # 管理数据库软件
# 数据库服务器 一台跑着一个数据库管理软件的机器
# 表 : 文件,一张存储了数据的表
# 数据/记录 : 表中的信息,一行就是一条记录

# 用户相关操作
# 查看当前用户是谁? select user();
# 给当前用户设置密码 set password = password('123');
# 创建用户 create user '用户名'@'主机的ip/主机域名' identified by '密码'
# 授权 grant select on 数据库名.* to '用户名'@'主机的ip/主机域名' identified by '密码'
# 授权并创建用户 grant select on 数据库名.* to '用户名'@'主机的ip/主机域名'

# 基础的库\表\数据操作
# 库 - 文件夹
    # 创建库             create database 数据库名;
    # 切换到这个库下     use 库名
    # 查看所有库         show databases;
# 表 - 文件
    # 查看这个库下的所有表  show tables;
    # 创建表                create table 表名(字段名 数据类型(长度),字段名 数据类型(长度),..);
    # 删除表                drop table 表名;
    # 查看表结构            desc 表名;
        # describe 表名;

# 数据(记录) - 文件中的内容
    # 增 : insert into 表 values (一行数据),(一行数据),(一行数据);
    # 删 : delete from 表 where 条件;
    # 改 : update 表 set 字段名=值,字段2=值2 where 条件;
    # 查 : select 字段 from 表;

# ip和域名
# 搜索的机器
# 10.125.23.1  sogou.search01.org
# 10.125.23.2  sogou.search02.org
# 10.125.23.3  sogou.search03.org
# 10.125.23.4  sogou.search04.org
# 浏览器
# 10.135.24.7
# 10.135.24.8
# 10.135.24.9
# 10.135.24.10
#
# www.baidu.com 136.17.2.2

# 今日内容
# 引擎介绍
    # innodb
    # myisam
    # memory
# 表介绍
# 创建表
# 查看表结构

# mysql中的数据类型
    # 数字
    # 时间
    # 字符串
    # enum/set
# 表的完整性约束
# 修改表结构
# 删除表
# 多表结构的创建与分析

4.存储引擎

# 存储引擎 -- 存储数据的方式
# 一张表
    # 数据
    # 表的结构
    # 索引(查询的时候使用的一个目录结构)

# Innodb存储引擎    mysql5.6之后的默认的存储引擎
# 数据和索引存储在一起 2个文件
    # 数据索引\表结构
# 数据持久化
# 支持事务   : 为了保证数据的完整性,将多个操作变成原子性操作   : 保持数据安全
# 支持行级锁 : 修改的行少的时候使用                          : 修改数据频繁的操作
# 支持表级锁 : 批量修改多行的时候使用                        : 对于大量数据的同时修改
# 支持外键   : 约束两张表中的关联字段不能随意的添加\删除      : 能够降低数据增删改的出错率


# Myisam存储引擎    mysql5.5之前的默认的存储引擎
# 数据和索引不存储在一起  3个文件
    # 数据\索引\表结构
# 数据持久化
# 只支持表锁

# Memory存储引擎
# 数据存储在内存中, 1个文件
    # 表结构
# 数据断电消失

# 面试题
# 你了解mysql的存储引擎么?
# 你的项目用了什么存储引擎,为什么?
    # innodb
    # 多个用户操作的过程中对同一张表的数据同时做修改
    # innodb支持行级锁,所以我们使用了这个存储引擎
    # 为了适应程序未来的扩展性,扩展新功能的时候可能会用到...,涉及到要维护数据的完整性
    # 项目中有一两张xx xx表,之间的外键关系是什么,一张表的修改或者删除比较频繁,怕出错所以做了外键约束

5.表和数据的基础操作

# create table 表名(
# id int,
# name char(18),
# 字段名3 类型[(宽度) 约束条件]
# );
# 放在中括号里的内容可以不写


# 写入数据的方式
# insert into 表 values (值1,值2,值3);
    # 这张表有多少的字段,就需要按照字段的顺序写入多少个值
# insert into 表 values (值1,值2,值3),(值1,值2,值3),(值1,值2,值3);
    # 一次性写入多条数据
# insert into 表 (字段1,字段3 ) values (值1,值3);
    # 指定字段名写入,可以任意的选择表中你需要写入的字段进行

# 查表中的数据
    # select * from 表

# 查看表结构
    # desc 表名;
        # 能够查看到有多少个字段\类型\长度,看不到表编码,引擎,具体的约束信息只能看到一部分
    # show create table 表名;
        # 能查看字段\类型\长度\编码\引擎\约束

6.数据类型-数字

# int 不约束长度,最多表示10位数
# float(m,n)
    # m 一共多少位,
    # n 小数部分多少位

# create table t1(
#     id int,               # 默认是有符号的
#     age tinyint unsigned  # 如果需要定义无符号的使用unsigned
# );

# create table t2(
#   f1 float(5,2),   # 保留2位小数 并四舍五入
#   f2 float,
#   f3 double(5,2),
#   f4 double
# )

# insert into t2(f2,f4) values(5.1783682169875975,5.1783682169875975179);

# create table t3(
#   f1 float,   # 保留2位小数 并四舍五入
#   d1 double,
#   d2 decimal(30,20),
#   d3 decimal
# );
# insert into t3 values(5.1783682169875975179,5.1783682169875975179,
#                       5.1783682169875975179,5.1783682169875975179);

7.数据类型-时间

# date  20190620
# time  121953
# datetime 20190620121900

# datetime
# year
# date
# time
# timestamp

# create table t4(
#     dt datetime,
#     y year,
#     d date,
#     t time,
#     ts timestamp
# );

# now()表示当前时间  datetime具有timestamp功能
# mysql> create table t5(
#     -> id int,
#     -> dt datetime NOT NULL                        # 不能为空
#                   DEFAULT CURRENT_TIMESTAMP        # 默认是当前时间
#                   ON UPDATE CURRENT_TIMESTAMP);    # 在更新的时候使用当前时间更新字段

8.数据类型-字符串

# char
# varchar

# char(18)    最多只能表示255个字符
    # 定长存储,浪费空间,节省时间
    # 'alex'   'alex                 '
# varchar(18) 最多能表示65535个字符
    # 变长存储,节省空间,存取速度慢
    # 'alex'   'alex4'

# 适合使用char
    # 身份证号
    # 手机号码
    # qq号
    # username 12-18
    # password 32
    # 银行卡号
# 适合使用varchar
    # 评论
    # 朋友圈
    # 微博

# create table t6(c1 char(1),v1 varchar(1),c2 char(8),v2 varchar(8));
# create table t6(c1 char,v1 varchar(1),c2 char(8),v2 varchar(8));

9.内容回顾

"""
mysql中的数据类型
  数字 int unsigned, float
  时间 date,time,datetime
  字符串 char,varchar
  emum/set
    emum 单选行为
    set 对选行为

表的完整性约束
修改表结构
删除表  drop table 表名;
多表结构的创建与分析
"""

10.数据类型-enum和set

# enum 单选
# set 多选

"""
create table t8(id int, name char(18), gender enum('male', 'female'));
"""

"""
create table t9(id int, name char(18), hobby set('抽烟', '喝酒', '烫头')); 可以去重和筛选范围内的
"""

11.完整性约束

"""
约束某一个字段
无符号的 int unsigned
不能为空 not null
默认值是什么 default
唯一约束 unique
  联合唯一 unique(字段1,字段2)
自增 auto_increment
  只能对数字有效，自带非空约束
  至少是unique的约束之后才能使用
主键 primary key
  一张表只能有一个
  如果不指定主键,默认为第一个非空+唯一
  联合主键 primary key(字段1,字段2)
外键 foreign key(字段名) references 表名(字段名)
"""

"""
create table t10(id int unsigned not null, name char(18) not null);
严格模式下，不支持设置不为空的字段插入null数据
在配置文件下加入后保存，永久生效
"""

"""
create table t10(id int unsigned not null, name char(18) not null,
male enum('male', 'female') not null default 'male');
"""

"""
不能重复 unique 值不能重复
create table t11(id1 int unique, id2 int);
id1 可以重复写入null值

联合唯一
create table t12(id int,
server_name char(12),
ip char(15),
port char(5),
unique(ip, port)
);
"""

"""
非空 + 唯一约束 = 主键
第一个被定义为非空+唯一的那一列会成为这张表的primary key
一张表只能定义一个主键
create table t15(id int not null unique,
username char(18) not null unique);
create table t15(id int not null unique,
username char(18) primary key);

联合主键
create table t12(id int,
server_name char(12),
ip char(15) not null,
port char(5) not null,
unique(ip, port)
);

create table t12(id int,
server_name char(12),
ip char(15) default '',
port char(5) default '',
primary key(ip, port)
);
"""

"""
自增 auto_increment
create table t20(id int unique auto_increment,
name char(12));
"""

"""
外键
学生表
create table stu(
id int primary key auto_increment,
name char(12) not null,
gender enum('male', 'female') default 'male',
class_id int,
foreign key(class_id) references class(cid)) on update cascade on delete cascade;  级联更新 级联删除(尽量不用)
\c指不执行
班级表
create table class(
cid int primary key auto_increment,
cname char(12) not null,
start_d date);
"""

12.修改表结构

"""
alter table 表名 rename 新表名;  修改表名
alter table 表名 add name char(18) after id;  增加字段
alter table 表名 add name char(18) first;
alter table 表名 drop 字段名;  删除字段
alter table 表名 modify name char(15) not null;  修改字段长度、约束  √√ ***
alter table 表名 change name cname char(10) not null;  修改字段名等
"""

13.多表结构的创建与分析

"""
表与表之间的关系
1.多对一
学生  班级
多个学生是一个班级的
学生表有一个外键 关联班级表

书籍  作者
多本书可以都是一个作者写的
书籍表有一个外键 关联作者表

书籍 出版社
多本书可以同是一个出版社出版的
书籍表有一个外键 关联出版社表

商品  订单
多个商品可以在一个订单中
商品表有一个外键 关联订单表

2.多对多  出现第三张表（两个外键）
学生  班级  多对一
  多个学生是一个班级的
班级  学生  多对一
  多个班级对应一个学生
一本书可以有多个作者
一个作者可以写多本书

一个订单可以有多个商品
一个商品可以属于多个订单

3.一对一
客户  学生
unique  foreign key unique
"""

14.内容回顾

# 存储引擎
# Innodb mysql5.6之后的默认存储引擎
# 2个文件,4个支持(支持事务,行级锁,表级锁,外键)
# Myisam mysql5.5之前的默认存储引擎
# 3个文件 支持表级锁
# Memory
# 1个文件 数据断电消失
# 数据类型
# 数字 : bool int float(7,2)
# 日期 : date time datetime year
# 字符串 :
# char    定长 效率高浪费空间 255
# varchar 变长 效率低节省空间 65535
# enum 和 set :
# 单选和多选
# 约束
# unsigned 无符号的
# not null 非空
# default  设置默认值
# unique   唯一,不能重复
# unique(字段1,字段2,字段3) 联合唯一
# auto_increment 自增
# int 必须至少unique字段,自带not null
# primary key 主键
# not null + unique
# 一张表只能有一个主键
# foreign key 外键
# a表中有一个字段关联b表中的一个unique
# a表中的是外键
# 建表
# create table 表名(
#   字段名1 类型(长度) 约束,
#   字段名1 类型(选项) 约束,
# );
# 修改表结构
# alter table 表名 rename 新名字;
# alter table 表名 add 字段名 类型(长度) 约束 after 某字段;
# alter table 表名 drop 字段名;
# alter table 表名 modify 字段名 类型(长度) 约束 first;
# alter table 表名 change 旧字名 新名字 类型(长度) 约束;
# 表之间的关系
# 一对一
# 一对多
# 多对多

# 删除表
# drop table 表名;

# 今日内容
# 数据的操作
    # 增
    # 删
    # 改
    # 查
        # 单表查询 (今天内容)
            # 分组聚合都不会用

15.数据的增删改

# create table t1(
#   id int primary key auto_increment,
#   username char(12) not null,
#   sex enum('male','female') default 'male',
#   hobby set('上课','写作业','考试') not null
# );

# 增 insert into 表(字段,...) values (值,...);
# insert into t1 value (1,'大壮','male','上课,写作业');
# insert into t1 values(2,'杜相玺','male','写作业,考试');
# insert into t1 values(3,'b哥','male','写作业'),(4,'庄博','male','考试');
# insert into t1(username,hobby) values ('杨得港','上课,写作业,考试'),('李帅','考试')
# insert into t2(id,name) select id,username from t1;

# 删
# 清空表
    # delete from 表;
        # 会清空表,但不会清空自增字段的offset(偏移量)值
    # truncate table 表;
        # 会清空表和自增字段的偏移量
# 删除某一条数据
    # delete from 表 where 条件;

# 改
    # update 表 set 字段=值 where 条件;
    # update 表 set 字段=值,字段=值 where 条件;


# 10个查询 1一个增删改

16.单表数据查询

# 1.select语句
# 最简单的select
    # select * from 表;
    # select 字段,... from 表;
# 重命名字段
    # select 字段 as 新名字,... from 表;
    # select 字段 新名字,... from 表;
# 去重
    # select distinct 字段 from 表;
    # select distinct age,sex from employee;
# 使用函数
    # concat  用于连接字符串
    # concat_ws  第一个参数为拼接符，如‘|’
# 四则运算的
    #  select emp_name,salary*12 from employee; 乘法
    #  select emp_name,salary*12 as annual_salary from employee;
# 使用判断逻辑
    # case when语句 相当于 if条件判断句

# where 筛选所有符合条件的行
    # 比较运算符
        # > < >= <= <> !=
    # 范围
        # between 10000 and 20000 要1w-2w之间的
        # in (10000,20000)   只要10000或者20000的
    # 模糊匹配
        # like
            # % 通配符 表示任意长度的任意内容  j% %j% j%
            # _ 通配符 一个字符长度的任意内容  cheng__
        # regexp
            # '^a'
            # 'g$'
    # 逻辑运算
        # not\and\or

# 查看岗位描述不为NULL的员工信息
    # is
    # select * from employee where post_comment is not null;
# 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资
    # select emp_name, age, salary
    # from employee wherepost = 'teacher' and salary not in(10000,9000,30000)
# 查看岗位是teacher且名字是jin开头的员工姓名、年薪
    #  select emp_name,salary*12 from employee where post = 'teacher' and emp_name like 'jin%';

# 分组 group by 根据谁分组,可以求这个组的总人数,最大值,最小值,平均值,求和 但是这个求出来的值只是和分组字段对应
    # 并不和其他任何字段对应,这个时候查出来的所有其他字段都不生效.
# 聚合函数
    # count 求个数
    # max  求最大值
    # min  求最小值
    # sum  求和
    # avg  求平均

    # SELECT post,emp_name FROM employee GROUP BY post;
    # SELECT post,GROUP_CONCAT(emp_name) FROM employee GROUP BY post;  看的情况下拼接出来

# having 过滤语句
    # 在having条件中可以使用聚合函数,在where中不行
    # 适合去筛选符合条件的某一组数据,而不是某一行数据
    # 先分组再过滤 : 求平均薪资大于xx的部门,求人数大于xx的性别,求大于xx人的年龄段
# 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数
# group by post having count(id) < 2;

# 排序 order by
    # 默认是升序  asc
    # 降序  desc
    # order by age ,salary desc  两个值排序
        # 优先根据age从小到大排,在age相同的情况下,再根据薪资从大到小排

# limit m,n
    # 从m+1项开始,取n项
    # 如果不写m,m默认为0

    # limit n offset m

17.内容回顾

# 数据的
# 增
    # insert into 表 values (值)
    # insert into 表(字段,字段2) values (值,值2)
    # insert into 表(字段,字段2) select 字段1,字段2  from 表2
# 删
    # delete from 表 where 条件;
    # truncate table 表名;
# 改
    # update 表 set 字段=值 where 条件;
# 查
    # select 字段 from 表
        # where 条件  根据条件筛选符合条件的行
        # group by 分组
        # having 过滤条件 根据分组之后的内容进行组的过滤
        # order by 排序
        # limit m,n 取从m+1开始的前n条

    # 1.where条件中不能用select字段的重命名  因为加载顺序，where在select之前
    # 2.order by 或者having可以使用select字段的重命名
        # 主要是因为order by 在select语句之后才执行
        # having经过了mysql的特殊处理,使得它能够感知到select语句中的重命名

    # 拓展
        # 在执行select语句的时候,实际上是通过where,group by,having这几个语句锁定对应的行
        # 然后循环每一行执行select语句

# 今日内容
# 多表查询
    # 连表查询
    # 子查询

# 如果一个问题既可以使用连表查询解决
# 也可以使用子查询解决
# 推荐使用连表查询 , 因为效率高

# pymysql的学习

18.连表查询

# 所谓连表
    # 总是在连接的时候创建一张大表,里面存放的是两张表的笛卡尔积
    # 再根据条件进行筛选就可以了

# 表与表之间的连接方式
    # 内连接 inner join ... on ...
        # select * from 表1,表2 where 条件;(了解)
        # select * from 表1 inner join 表2  on 条件
        # select * from department inner join employee on department.id = employee.dep_id;
        # select * from department as t1 inner join employee as t2 on t1.id = t2.dep_id;
    # 外连接
        # 左外连接 left join ... on ...
            # select * from 表1 left join 表2 on 条件
            # select * from department as t1 left join employee as t2 on t1.id = t2.dep_id;
        # 右外连接 right join ... on ...
            # select * from 表1 right join 表2 on 条件
            # select * from department as t1 right join employee as t2 on t1.id = t2.dep_id
        # 全外连接 full join
            # select * from department as t1 left join employee as t2 on t1.id = t2.dep_id
            # union
            # select * from department as t1 right join employee as t2 on t1.id = t2.dep_id;

# 1.找到技术部的所有人的姓名
# select * from department d inner join employee e on e.dep_id = d.id;
# select e.name from department d inner join employee e on e.dep_id = d.id where d.name='技术';

# 2.找到人力资源部的年龄大于40岁的人的姓名
# select * from department d inner join employee e on e.dep_id = d.id
# select * from department d inner join employee e on e.dep_id = d.id where d.name='人力资源' and age>40;

# 3.找出年龄大于25岁的员工以及员工所在的部门
# select * from department d inner join employee e on e.dep_id = d.id;
# select e.name,d.name from department d inner join employee e on e.dep_id = d.id where age>25;

# 4.以内连接的方式查询employee和department表，并且以age字段的升序方式显示
# select * from department d inner join employee e on e.dep_id = d.id order by age;

# 5.求每一个部门有多少人
# select d.name,count(e.id) from department d left join employee e on e.dep_id = d.id group by d.name;
# 且按照人数从高到低排序
# select d.name,count(e.id) c from department d left join employee e on e.dep_id = d.id group by d.name order by c desc;

# 所谓连表就是把两张表连接在一起之后 就变成一张大表  从from开始一直到on条件结束就看做一张表
# 之后 where 条件 group by 分组 order by limit 都正常的使用就可以了

19.子查询

# 查询平均年龄在25岁以上的部门名
# select name from department where id in (
#     select dep_id from employee group by dep_id having avg(age)>25);

# 查看技术部员工姓名
    # 先查询技术部的部门id
    # select id from department where name = '技术';
    # 再根据这个部门id找到对应的员工名
    # select name from employee where dep_id =(select id from department where name = '技术');
    # select name from employee where dep_id in (select id from department where name = '技术');

# 查看不足1人的部门名
    # 先把所有人的部门id查出来
    # select distinct dep_id from employee;
    # 然后查询部门表,把不在所有人部门id这个范围的dep_id找出来
    # select name from department where id not in (select distinct dep_id from employee);

# 查询大于所有人平均年龄的员工名与年龄
    # 求平均年龄
    # select avg(age) from employee;
    # select * from employee where age >28;
    # select name,age from employee where age >(select avg(age) from employee);

# 查询大于部门内平均年龄的员工名、年龄
    # select dep_id,avg(age) from employee group by dep_id;
    # select name,age from employee as t1 inner join (select dep_id,avg(age) avg_age from employee group by dep_id) as t2
    # on t1.dep_id = t2.dep_id where age>avg_age;

# source + 路径 导入数据
# 路径不要出现转义和中文名
"""
1.select gender, count(sid) from student group by gender
2.select * from student where sname like '张%'
3.select course_id, avg(num) from score group by course_id order by avg(num) desc
4.select t1.sid, t1.sname from student t1 inner join score t2 on
t1.sid = t2.student_id where num < 60
5.select sid, sname from student where class_id =
(select class_id from student where sid = 1)

第五题：
1、select sid,sname from student right join (select distinct student student_id from score where course_id in (select
course_id from score where student_id = 1) and student_id !=1) as t on student.sid = t.student_id;

2、select distinct student_id,sname from student as t1 inner join score as t2 on t1.sid = t2.student_id where course_id in
(select course_id from score where student_id = 1);
"""

20.内容回顾

# 多表查询
    # 连表查
        # 内连接 必须左表和右表中条件互相匹配的项才会被显示出来
            # 表1 inner join 表2 on 条件
        # 外连接 会显示条件不匹配的项
            # left join 左表显示全部，右表中的数据必须和左表条件互相匹配的项才会被显示出来
            # right join 右表显示全部，右表中的数据必须和右表条件互相匹配的项才会被显示出来
            # 全外连接
                # left join
                # union
                # right join
    # 子查询
        # select * from 表 where 字段 = (select 字段 from 表 where 条件)
        # select * from 表 where 字段 > (select 字段 from 表 where 条件)
        # select * from 表 where 字段 in (select 字段 from 表 where 条件)