数据分析mysql4.21

昨日内容回顾

• 用户登录

  mysql -u用户名 -p密码
  
  # 修改密码
  mysqladmin -u用户名 -p原密码 password 新密码
  

• 忘记密码

  mysql --skip-grant-tables
  

• 配置文件

  统一编码
  
  自带的配置文件
      my-default.ini
  自定义配置文件
      my.ini
      
  # 修改完配置之后一定要重启服务端    
  

• 重要概念

  对应关系
  

• 基本SQL语句

  1.针对库
       查
            1.show databases;
            2.show create database 库名;
       增
            create database 库名;
       改
            alter database 库名 charset='gbk';
       删
            drop database 库名;
  2.针对表
      如何查看当前在哪个库下
           select database();
      如何切换数据库
           use 库名;
      查
            1.show tables;
            2.show create table 表名; # 针对具体表名 
            3.desc 表名;
       增
            create table 表名(字段名1 字段类型1,...);
       改
            alter table 表名 modify 字段名 字段类型(16);
       删
            drop table 表名;
  3.针对记录        
      查
      	1.select * from 表名;
          2.select * from 库名.表名;
          3.select 字段名1,字段名2... from 表名;
      增
      	insert into 表名 values(...);
          insert into 表名 values(...),(...),(...); # 插入多个
      改
      	update 表名 set 字段名=新的值 where 筛选条件;
      删
      	delete from 表名 where 筛选条件;   
  

• 存储引擎

  show engines;
  
  # 四种需要掌握的引擎
  MyISAM、InnoDB、BlackHole、Memory
  

• 数据类型之整型

  tinyint
  smallint
  int
  bigint
  能够存储的数字范围不同  并且默认情况下都是自带符号的
  

• 数据类型之浮点型

  float(255,30)
  double(255,30)
  decimal(65,30)
  精确度不一致
       float < double < decimal
  补充知识
     针对数字很多时候都会出现不精确的情况
     所以有时候看似需要用数字存储的数据内部可能存的都是字符串
  

• 数据类型之字符类型

  char(4)
  varchar(4)
  

• 严格模式

  show variables like '%mode%'
  
  set globai sql_mode = '...'
  

今日内容概要

• char与varchar对比

• 枚举与集合类型

• 日期类型

• 创建表的完整语法

• 字段的约束条件

  在字段类型的基础之上添加额外的限制
  

• 查询关键字

今日内容详细

char与varchar对比

char(4)
    不超过4个字符 还是存四个字符(空格填充)
varchar(4)
    不超过4个字符 有几个存几个
    
char
     优点：整存整取 不会出现数据错乱 速度快
     缺点：浪费存储空间
varchar
     优点：节省存储空间
     缺点：存取数据的速度较于char偏慢
"""
char(5)
    jasonegon kevintony tom jack
char(5)    
    11bytes+jason1bytes+egon1bytes+kevin1bytes+tony
"""
# 到底使用char还是varchar?
    其实二者都有应用场景
    一般情况下推荐使用varchar
    但是具体结合实际需求(产品经理 架构师...)

枚举与集合类型

枚举
    多选一
create table user1(
    id int,
    name varchar(16),
    gender enum('male','female','others')
);
# 列举可以写入的数据   不在范围之内直接报错(严格模式)


集合
    多选多(包括多选一)
create table user2(
    id int,
    name varchar(16),
    hobby set('read','study','run','ball')
);    

日期类型

datetime      年月日时分秒
date          年月日
time          时分秒
year           年


create table user3(
    id int,
    name varchar(16),
    reg_time datetime,
    birthday date,
    study_time time,
    stime year
);
# 我们在录入时间数据的时候 其实都是直接借助于获取时间的模块
# 只做模拟演示  自己写时间
insert into user3 values(1,'jason','2000-11-11 11:11:11',
'1995-01-21','12:30:00','2020');   

创建表的完整语法

create table 表名(
    字段名1 字段类型(宽度 约束条件),
    字段名2 字段类型(宽度 约束条件),
    字段名3 字段类型(宽度 约束条件)
);
1.字段名和字段类型是必须的 宽度和约束条件是可选的
2.约束条件可以有多个!!!
3.最后一个字段结尾不要加逗号

约束条件

1.unsigned           无符号
create table t1(id tinyint);
create table t2(id tinyint unsigned);
insert into t1 values(255);
insert into t2 values(255);

2.zerofill         0填充
create table t3(id tinyint zerofill);
insert into t3 values(1);

3.not null        非空
create table t4(name varchar(16) not null);
insert t4 values();    # 报错  必须给个值

4.default    默认值
create table t5(
    name varchar(16),
    gender varchar(16) default 'male'
);
"""
在插入数据的时候也可以指定字段  不指定默认就是按照顺序依次传值
insert into t5(name) values(值)
"""

###########################复杂################
5.unique          唯一
# 单列唯一
create table t6(
    id int,
    name varchar(16) unique
);
insert into t6(id,name) values(1,'jason'),(2,'jason');
# 多列唯一(联合唯一)
create table t7(
    id int,
    host varchar(16),
    port int,
    unique (host,port)
);
insert into t7 values(1,'127.0.0.1',8080),(2,'127.0.0.2',8080),
(3,'127.0.0.1',8080);

***********************
6.primary key      主键
***********************
    特点：1.主键在约束条件上相当于是not null + unique(非空且唯一)
    # 单列主键
    create table t8(
        id int primary key    # id int not null unique
    );
    # 多列主键(联合主键) 本质还是一个主键 并且这种情况使用较少
    create table t9(
        ip char(16),
        port int,
        primary key(ip,port)
    );
    2.由于5.6及之后的版本存储引擎都是InnoDB
    该引擎规定了所有的表都必须有且只有一个主键
    '''1.没有设置主键也没有其他约束条件'''
        InnoDB为了能够创建出表  会自动采取内部隐藏的字段作为主键
        隐藏意味我们无法使用到该主键  是没有意义的！！！
    '''2.没有设置主键但是有其他约束条件'''
        InnoDB会自上而下的将一个非空且唯一的字段升级为主键使用
        create table t12(
            id int,
            name char(16),
            age int not null unique,
            addr char(16) not null unique
        );
        字段将age升级为主键
        总结：以后我们在创建表的时候一定要自己指定主键
        并且主键一般都是序号 用来唯一标识数据
        所以主键一般情况下都是设置给表中的第一个id字段
            uid sid pid cid只要是用来标识数据唯一性即可
            
7.auto_increment      自增
    是专门用来给主键使用的 目的就是让主键能够自己增加不需要人为数数
    create table t13(
        id int primary key auto_increment,
        name char(16)
    );
    insert into t13(name) values('jason'),('kevin'),('tony');
    
"""
以后创建表的时候固定写序号字段
    create table t1(
    id int primary key auto_increment,
    ....
    );
在写入数据的时候也不需要考虑主键字段了
"""    

主键的特点

自增不会因为删除数据而重置
     delete from
如果真的想删除数据并重置主键
     truncate

外键（重点）

外键也属于约束条件 但是功能是用来建立表与表之间的关系
属于特殊的约束条件

# 前戏
创建一张公司里面的员工表
id   name    age     dep_name       dep_desc

缺陷：
    1.表含义不明确(可以忽略)
    2.数据存在重复浪费存储空间(可以忽略)
    3.数据修改极其繁琐 不便于扩展(不能忽略)
    
    措施：
        将员工表一分为二
            员工表
               id name age 
            部门表  
               id dep_name dep_desc
                
    外键字段(关系字段)的由来
    部门编号   对应的就是部门表里面的主键字段

表关系

1.一对多关系
2.多对多关系
3.一对一关系
4.没有关系
"""一定要学会表关系的判断！！！"""
# 表关系判断 遵循"换位思考"原则即可

员工表与部门表为例
    1.先站在员工表
        问：一个员工能否对应多个部门
           结论：不可以
    2.再站在部门表
        问：一个部门能否对应多个员工
           结论：可以
    总结：一个可以一个不可以 那么这种关系就是"一对多"
    
create table emp(
    id int primary key auto_increment,
    name char(16),
    gender enum('male','female') not null default 'male',
    dep_id int,
    foreign key(dep_id) references dep(id)
);
create table dep(
    id int primary key auto_increment,
    dep_name char(10),
    dep_comment char(60)
);
# 基于换位思考得出员工表与部门表是一对多的外键关系
"""
针对一对多的外键关系
    关系字段建在多的一方(员工表就是多)
"""

外键约束

1.在创建表的时候需要先创建被关联表
    简单理解：先创建没有外键字段的表再创建有外键字段的表
2.在第一次录入数据的时候需要先录入被关联表之后再录入关联表
    简单理解：先录入没有外键字段的表再录入有外键字段的表
3.涉及到编辑和删除操作
    修改数据
        # 修改部门编号   不行
        # 删除部门数据   不行
"""针对关系字段的修改与删除如何解决"""
级联更新  级联删除
create table dep1(
    id int primary key auto_increment,
    dep_name char(10),
    dep_comment char(60)
);
create table emp1(
    id int primary key auto_increment,
    name char(16),
    gender enum('male','female') not null default 'male',
    dep_id int,
    foreign key(dep_id) references dep1(id)
    on update cascade
    on delete cascade   # 这三句为一句 没有逗号
);   

多对多关系

以图书表与作者表为例
1.先站在图书表的角度
	问:一本书能否对应多个作者
        结论:可以
2.再站在作者表的角度
	问:一名作者能否对应多本书
        结论:可以
结论:两边都可以 那么表关系就是"多对多"

# SQL实现(先写基础的字段 之后再添加外键字段)
create table book(
    id int primary key auto_increment,
    title varchar(32),
    price float(8,2)
);
create table author(
    id int primary key auto_increment,
    name varchar(32),
    age int
);
"""针对多对多关系
    外键字段不能直接建在关系表中
        需要再额外创建一张专门用来存储关系的第三张表
"""
create table book2author(
    id int primary key auto_increment,
    book_id int,
    author_id int,
    foreign key(author_id) references author(id)
    on update cascade
    on delete cascade,
    foreign key(book_id) references book(id)
    on update cascade
    on delete cascade
);

一对一关系

以用户表与用户详情表为例
	1.先站在用户表角度
    	问:一个用户能否对应多个用户详情
            结论:不可以
    2.再站在用户详情表
    	问:一个用户详情能否对应多个用户
            结论:不可以
总结:两个都不可以 那么表关系就是"一对一关系"(没有关系的话直接就能看出来)

"""针对一对一表关系
   外键字段建在任何一方都可以，推荐建在查询频率较高的表中
"""
create table user(
    id int primary key auto_increment,
    addr varchar(32),
    age int,
    user_detail_id int unique,
    foreign key(user_detail_id) references user_detail(id)
    on update cascade
    on delete cascade
);
create table user_detail(
    id int primary key auto_increment,
    addr varchar(32),
    phone bigint
);

作业

1.整理今日内容
    约束条件
2.背诵表关系判断口诀并完成下列表关系判断
     学生表与课程表
     老师表与班级表
     成绩表与学生表
    # 要求 文字写出整个判断过程以及SQL语句 并在MySQL中录入截图为证