数据库简述及MySQL相关操作(DDL与DML)

1 数据库简述

数据库：数据的仓库（集散地），它解决了数据持久化和数据管理问题。

作用：持久化，将数据从内存转移到硬盘（可以长久保存数据的存储介质）。

数据库的分类：关系型数据库和非关系型数据库（NoSQL / NewSQL）。

关系型数据库特点：

    ~ 理论基础：关系代数、集合论

    ~具体表象：用二维表保存数据（行（记录））和列（字段）

    ~编程语言：SQL

关系型数据库产品：

     ~ Oracle 、DB2(IBM公司) 、MySQL 、SQLServer(微软) 、PostgreSQL

2 MySQL常用命令

查看所有数据库

    ~ show databases;

查看所有字符集（编码方式）

    ~ show character set;

查看所有的校对规则（排序）

    ~ show collation;

查看所有的引擎

    ~ show engines

查看所有日志文件

   ~ show binary logs;

查看数据库下所有表

    ~ show tables

获取帮助

    查看show命令的帮助

        ? show

    查看有哪些帮助内容

        ? contents

    获取函数的帮助

        ? functions

    获取数据类型的帮助

        ? data types

3 SQL - Structured Query Language -   结构化查询语言 

    - DDL - 数据定义语言 ---> create / drop（删除、丢弃） / alter（修改）

    - DML - 数据操作语言 ---> insert / delete / update（更新）
    - DQL - 数据查询语言 ---> select
    - DCL - 数据控制语言 ---> grant（授权） / revoke（召回）

注：SQL中不区分大小写

3.1 DDL

1.创建数据库                                                                                                                create

-- 创建名为school的数据库并设置默认的字符集和排序方式

    ~ create database school default charset utf8mb4;  （8以前的版本必须加default后的，8不用）
    ~ create database school default character set utf8mb4;

2.删除数据库                                                                                                                drop

    ~ drop database if exists school;

3.查看创建数据的过程

    ~ show create database school

4.切换到指定数据库

     ~ use school;

5.查看数据库下所有的表

    ~ show tables;

6.数据类型：
    ~ 整数：
        - int / integer ---> -2^31 ~ 2^31 - 1
        - tinyint / smallint / bigint  ---> -128 ~ 127 / - 2^15 ~ 2 ^15 -1 / - 2^63 ~ 2 ^63 -1
        - int unsigned ---> 0 ~ 2^32 - 1                                                                           unsigned
        - tinyint unsigned ---> 0 ~ 255
    ~ 小数：
        - float (单倍精度浮点数) / double(双倍精度浮点数) 
        - decimal(常用) ---> decimal(10,2)：总共有10位有效数字，小数点后有两位

    ~ 字符串：
        - char / varchar(常用) --->varchar上限65535个字符
        - longtext ---> 上限4G ---> 再大的话，放资源路径（URL）
        - longblob（二进制大对象）

    ~ 时间日期：
        - date / time / datetime(常用)
        - timestamp

    ~ 布尔型：
        - boolean ---> 底层是tinyint(1) ---> 0 (假)/ 1(真)
    
    ~ 枚举型：MySQL 特有的
        - enum ---> ' M ' / ' F '

7.创建二维表
        create table `tb_student` 
        (
            `stu_id` integer not null comment '学号',
            `stu_name` varchar(10) not null comment '姓名',
            `stu_sex` char(1) default 'M' not null comment '性别',
            `stu_birth` date comment '出生日期',
            primary key (`stu_id`)
            foreign key (`col_id`) references `tb_college` (`col_id`)

) engine=innodb comment '学生表';                             #  指定引擎，版本向下兼容  engine=innodb

 主键（primary key）：能够唯一确定一条记录的字段。

外键    foreign key：外来的主键(其他表的主键)

8. 添加、删除、修改

-- 修改表添加一个列                                                alter table......add column......
alter table `tb_student` add column `stu_addr` varchar(100) comment '地址';

-- 修改表删除一个列                                                    alter table......drop column......
alter table `tb_student` drop column `stu_sex`;

-- 修改表格改一个列(modify不能修改列的名字，change可以)         
alter table `tb_student` modify column `stu_sex` char(1) default 'M';         modify
alter table `tb_student` change column `stu_sex` 
`stu_gender`char(1) default 'F' comment '性别';                                            change

-- 修改表添加额外的约束条件
alter table `tb_student` add constraint `ck_student_gender`                      add constraint
check(`stu_gender`='M' or `stu_gender`='F') ;

-- 修改表删除指定的约束条件
-- alter table `tb_student` drop constraint `ck_student_gender`;                drop constraint

-- 修改表添加唯一性约束
alter table `tb_college` add constraint `uk_college_name` unique (col_name);        unique

9. 从属关系---> 关系的重数(1:1 / 1:N / M:N)

alter table `tb_student` add constraint `fk_student_col_id`
    foreign key(`col_id`) references `tb_college`(`col_id`);                        references

10.ER图  Entiyt Relationship Diagram  也称物理模型图：直接生成表或建表的SQL语句

实体：矩形框  ---->  表

属性：椭圆框  --->   列（字段、属性、特征）

关系：菱形框

重数：1:1 / 1:N / M:N

EER图  --->  Extended ER图

正向工程：先设计EER图，再将图生成数据库和表

反向工程：将设计好的数据库和表生成EER图

3.2 DML

1.插入insert、删除delete、更新update

-- 向学生表加入数据
insert into `tb_student` value                                                                         insert into
    (1001,'骆','1989-11-22','四川成都','M');

-- 插入多条数据
insert into `tb_student` 
    (`stu_id`, `stu_name`, `stu_sex`, `stu_birth`, `stu_addr`, `col_id`) 
values
    (1001, '杨过', 1, '1990-3-4', '湖南长沙', 1),
    (1002, '任我行', 1, '1992-2-2', '湖南长沙', 1),
    (1033, '王语嫣', 0, '1989-12-3', '四川成都', 1)；

-- 删除
delete from `tb_student` where `stu_id`=3923;                                            delete from...where...
delete from `tb_student` where `stu_id`=3923 or `stu_id`=3011;
delete from `tb_student` where `stu_id` in (3923,3011);
-- 删除全表(无日志下删除，不能被回复)
truncate table `tb_student`;

-- 更新(set后面的等号表示赋值,其他表示判断)
update `tb_student` set `stu_addr`='四川成都',`col_id`=2                         update...set..
where `stu_id`=3755;