浅学一下Mysql

超、凢脫俗 2024-03-25 20:09 64阅读 0赞

## 一、数据存储 ##

--------------------

### **1.1 现有的数据存储方式有哪些？** ###

Java程序存储数据（变量、对象、数组、集合），数据保存在**内存**中，属于瞬时状态存储。

文件（File）存储数据，保存在**硬盘**上，属于持久状态存储。

### **1.2 以上存储方式存在哪些缺点？** ###

*  没有数据类型的区分。

*  存储数据量级较小。

*  没有访问安全限制。

*  没有备份、恢复机制。

## 二、数据库 ##

### **2.1 概念** ###

数据库是“按照数据结构来组织、存储和管理数据的仓库。是一个长期存储在计算机内的、有组织的、有共享的、统一管理的数据集合。

### **2.2 数据库的分类** ###

*  网状结构数据库：美国通用电气公司IDS（Integrated Data Store），以节点形式存储和访问。

*  层次结构数据库：IBM公司IMS（Information Management System）定向有序的树状结构实现存储和访问。

*  **关系结构数据库**：Oracle、DB2、MySQL、SQL Server，以**表格（Table**）存储，多表间建立关联关系，通过分类、合并、连接、选取等运算实现访问。

*  **非关系型数据库**：ElastecSearch、MongoDB、Redis，多数使用**哈希表**，表中**以键值（key-value）的方式**实现特定的键和一个指针指向的特定数据。

## 三、数据库管理系统（DBMS） ##

**数据库管理系统**（DataBase Management System，**DBMS**）：指一种操作和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制，以保证数据库的安全性和完整性。用户通过数据库管理系统访问数据库中的数据。

**关系数据库管理系统**（Rational DataBase Management System，**DBMS**）

## 四、MySQL ##

MySQL是一个**关系型数据库管理系统**，由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS(Relational Database Management System，关系数据库管理系统) 应用软件之一

<table style=""> 
 <tbody> 
 <tr> 
 <td style="width:451px;vertical-align:top;height:51px;"> 
 <ul> 
 <li style="margin-left:1.4em;list-style-type:disc;text-indent:0;text-align:left;">启动mysql</li> 
 </ul> 
 <ul> 
 <li style="margin-left:2.8em;list-style-type:circle;text-indent:0;text-align:left;">命令net start mysql</li> 
 </ul> 
 <ul> 
 <li style="margin-left:2.8em;list-style-type:circle;text-indent:0;text-align:left;">任务管理器启动</li> 
 </ul> 
 <ul> 
 <li style="margin-left:1.4em;list-style-type:disc;text-indent:0;text-align:left;">登录mysql</li> 
 </ul> 
 <ul> 
 <li style="margin-left:2.8em;list-style-type:circle;text-indent:0;text-align:left;">mysql -uroot -pxxxxx</li> 
 </ul> 
 <ul> 
 <li style="margin-left:1.4em;list-style-type:disc;text-indent:0;text-align:left;">退出mysql</li> 
 </ul> 
 <ul> 
 <li style="margin-left:2.8em;list-style-type:circle;text-indent:0;text-align:left;">exit</li> 
 </ul></td> 
 </tr> 
 </tbody> 
</table>

## 五、SQL语言 ##

SQL（Structured Query Language）结构化查询语言，用于存取数据、更新、查询和管理关系数据库系统的程序设计语言。

*  通常执行对数据库的“增删改查”，简称C（Create）R（Read）U（Update）D（Delete）。

*  **DDL**(Data Definition Language) 数据**定义**语言,定义**数据库,表,列**等

*  **DML**(Data Manipulation Language) 数据**操作**语言,操作**内部数据(增,删除,修改)**

*  **DQL**(Data Query Language) 数据**查询**语言,对**数据的查询**

*  **DCL**(Data Control Language) 数据**控制**语言,定义数据的**访问控制和安全**

## 六、DDL ##

### **6.1 数据库操作** ###

#### 查看MySQL中所有数据库 ####

mysql> SHOW DATABASES; #显示当前MySQL中包含的所有数据库

#### 创建自定义数据库 ####

mysql> CREATE DATABASE mydb1; #创建mydb数据库
    mysql> CREATE DATABASE mydb2 CHARACTER SET gbk; #创建数据库并设置编码格式为gbk
    mysql> CREATE DATABASE IF NOT EXISTS mydb4; #如果mydb4数据库不存在，则创建；如果存在，则不创建。

#### 查看数据库创建信息 ####

mysql> SHOW CREATE DATABASE mydb2; #查看创建数据库时的基本信息

#### 修改数据库 ####

mysql> ALTER DATABASE mydb2 CHARACTER SET gbk; #修改数据库的字符集

#### 删除数据库 ####

mysql> DROP DATABASE mydb1; #删除数据库mydb1

#### 查看当前所使用的数据库 ####

mysql> select database(); #查看当前使用的数据库

#### 使用数据库 ####

mysql> use mydb1; #使用mydb1数据库

### **6.2 数据表操作** ###

#### 查询当前库下的所有表 ####

mysql> show tables;

#### 创建数据表 ####

CREATE TABLE 表名（

列名 数据类型 \[约束\],

....

列名 数据类型 \[约束\] //最后一列的末尾不加逗号

）\[charset=utf8\] //可根据需要指定表的字符编码集

create table stu(
        -> id int,
        -> name varchar(20),
        -> score double(10,2),
        -> birthday date
        -> );
        
    mysql> -- 创建表
    mysql> -- 创建学生表(stu),列: id 数字类型,name 字符串类型,score 浮点型 ,birthday 日期类型
    mysql> create table stu(  -- 创建表
        -> id int,  -- id是列名,int是数据类型,长度是11,默认可以不写
        -> name varchar(20), -- name是列名,varchar是可变字符类型
        -> score double(10,2),-- score 是列名,double浮点型
        -> birthday date -- birthday是列名,date是日期类型,不需要长度
        -> );

<table style=""> 
 <tbody> 
 <tr> 
 <td style="width:167px;vertical-align:top;height:51px;">数据类型</td> 
 <td style="width:184px;vertical-align:top;height:51px;">解释</td> 
 <td style="width:304px;vertical-align:top;height:51px;">长度</td> 
 </tr> 
 <tr> 
 <td style="width:167px;vertical-align:top;height:51px;">int</td> 
 <td style="width:184px;vertical-align:top;height:51px;">整形</td> 
 <td style="width:304px;vertical-align:top;height:51px;">11,固定,可以省略</td> 
 </tr> 
 <tr> 
 <td style="width:167px;vertical-align:top;height:51px;">double</td> 
 <td style="width:184px;vertical-align:top;height:51px;">浮点型</td> 
 <td style="width:304px;vertical-align:top;height:51px;">(x,y) 总长x位,其中包含y位小数</td> 
 </tr> 
 <tr> 
 <td style="width:167px;vertical-align:top;height:51px;">char</td> 
 <td style="width:184px;vertical-align:top;height:51px;">定长字符</td> 
 <td style="width:304px;vertical-align:top;height:51px;">0-255,声明(10)的字符,即使字符不够,也会占用10位</td> 
 </tr> 
 <tr> 
 <td style="width:167px;vertical-align:top;height:51px;">varchar</td> 
 <td style="width:184px;vertical-align:top;height:51px;">可变长字符</td> 
 <td style="width:304px;vertical-align:top;height:51px;">0-255,声明(10)的字符,会根据实际存储缩放</td> 
 </tr> 
 <tr> 
 <td style="width:167px;vertical-align:top;height:51px;">date</td> 
 <td style="width:184px;vertical-align:top;height:51px;">日期</td> 
 <td style="width:304px;vertical-align:top;height:51px;">没有长度</td> 
 </tr> 
 <tr> 
 <td style="width:167px;vertical-align:top;height:51px;">datatime</td> 
 <td style="width:184px;vertical-align:top;height:51px;">日期时间</td> 
 <td style="width:304px;vertical-align:top;height:51px;">没有长度</td> 
 </tr> 
 <tr> 
 <td style="width:167px;vertical-align:top;height:51px;">timestamp</td> 
 <td style="width:184px;vertical-align:top;height:51px;">时间戳</td> 
 <td style="width:304px;vertical-align:top;height:51px;">没有长度</td> 
 </tr> 
 </tbody> 
</table>

#### 查看表结构 ####

desc stu;

#### 向现有表中添加列 ####

#在课程表基础上添加gradeId 列
    alter table stu add gradeId int;

#### 删除表中的列 ####

#删除课程表中 gradeId 列
    alter table stu drop gradeId;

**注意：删除列时，****每次只能删除一列**

#### 修改列名 ####

#修改课程表中 sex 列为 gender
    
    // 修改列名和类型
    alter table stu change sex gender int;
    // 修改列的存储类型
    alter table stu change gender gender char(1);

**注意：修改列名时，在给定列新名称时，****要指定列的类型和约束**

#### 修改表名 ####

#修改课程表的stu 为 student
    alter table stu rename student;

#### 删除学生表 ####

#删除学生表
    drop table student;

### 6.3总结 ###

-- 库操作
    create database 名字;
    show databases;
    drop database 名字;
    alter database 名字 xxxx
    -- 表操作
    show tables;
    create table 名字 (列名 数据类型,列名 数据类型,...)
    alter table 名字 xxx
    drop table 名字

## 七、DML ##

DML 数据操作语言,其实对表中的数据进行 增、删、改

### **7.1 插入** ###

**insert into** 表名 (字段1,字段2,...) **values** (值1,值2,...);

insert into 表名 values (值1,值2,...);

CREATE TABLE `stu` (
      `sid` int(11) DEFAULT NULL,
      `sname` varchar(10) DEFAULT NULL,
      `age` int(11) DEFAULT NULL,
      `score` double(5,2) DEFAULT NULL,
      `birthday` date DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    -- ================= 插入 =================
    /*
     insert into 表名 (字段1,字段2,...) values (值1,值2,...);
    注意事项:
    1) 数字直接写
    2) 字符串用 单引号
    3) 日期用单引号,格式yyyy-MM-dd
    4) 可以单独向部分字段插入数据
    5) 插入的字段顺序和原表没有关系,
       但是值顺序要和插入字段顺序一致
    6) 当 插入表中全部字段 时,可以省略字段,直接写值
       此时 字段顺序和原表一模一样
    7) 可以同时插入多行数据
    */
    -- 向表的全部字段插入数据
    insert into stu (sid,sname,age,score,birthday)
    values (1,'彩云',20,98.2,'2020-01-01');
    -- 插入部分字段
    insert into stu (sid,sname,age)
    values (2,'贝贝',20);
    -- 字段顺序和原表没关系
    insert into stu (age,sname,sid)
    values (18,'老邢',3);
    -- 给全部列插入值,省略字段
    insert into stu values (4,"骚磊",20,99.9,'2020-01-01');
    -- 同时插入多行
    insert into stu (age,sname,sid) values 
    (18,'阿七',5),
    (19,'阿西吧',6),
    (20,'阿力鸡',7),
    (21,'小飞鸡',8)

#### 注意符号！！！ ####

insert into stu (id,`name`,score,birthday)   // 反引号
       values (1,'阿西吧',60.2,'2000-12-14');  // 单引号

### **7.2 更新** ###

**update** 表名 **set** 字段 = 值 \[, 字段2 = 值2, ... \] \[where 字段 = 值\];

\-- \[, 字段2 = 值2, ... \] 是指,可选的,可以同时修改多个列的值

\-- \[where 字段 = 值\] 是指,可选的,加上是指过滤,只更新符合条件的数据

\-- 不加,就是更新全表数据, 不推荐!!

/*
 update 表名 set 字段 = 值;
 update 表名 set 字段 = 值 , 字段2 = 值2 ;
 update 表名 set 字段 = 值 , 字段2 = 值2 where 字段 = 值;
 推荐使用带条件的.条件有很多 = > < >= 等等
 */
 -- 更新学生的id为2的成绩为100
 update stu set score = 100 where sid = 2;
 -- 同时更新多个字段
 update stu set score = 100, birthday = '1970-01-01' where sid = 3;
 
 -- 同时更新多条数据
 update stu set score = 100 where sid >= 5;
 -- 不加条件,更新全部数据
 -- 不推荐使用,将来工作中数据表中的数据100w以上
 update stu set score = 0;
 
 -- 字段是可以做运算（算数运算：+ - * / 都行）
 -- 更新贝贝的年龄+2岁
 update stu set age = age + 2 where sname = '贝贝';
 update stu set age = age + 100;
 
 update stu set score = 11+50 where birthday = '2000-12-14';

### 7**.3 删除** ###

delete from 表名; -- 删除 全表 数据

delete from 表名 where 字段 = 值; -- 只删除符合条件的数据

-- 删除id为8的学生数据
    delete from stu where sid = 8;
    
    delete from stu where sid >= 5;
    -- 删除全部[慎用]
    delete from stu;
    /*
     一般实际开发中,都是逻辑删除,其实是没有进行物理删除
    执行都是更新.
     在设计表时,会设计一个状态字段,例如status
      status = 1 活跃
        status = 2 不活跃
        status = 3 僵尸号
        status = 1 用户 2 vip用户 3 注销用户
    */

### **7.4 delete和truncate的区别** ###

-- 删除全部[慎用]
    delete from stu;
    delete from stu where xxx条件;
    -- 删除全表
    truncate table stu;

<table style=""> 
 <tbody> 
 <tr> 
 <td style="width:110px;vertical-align:top;height:51px;"></td> 
 <td style="width:300px;vertical-align:top;height:51px;">delete</td> 
 <td style="width:317px;vertical-align:top;height:51px;">truncate</td> 
 </tr> 
 <tr> 
 <td style="width:110px;vertical-align:top;height:51px;">语句类型</td> 
 <td style="width:300px;vertical-align:top;height:51px;">DML(Data Manipulation Language) 数据操作语言,操作内部数据(增,删除,修改)</td> 
 <td style="width:317px;vertical-align:top;height:51px;">DDL(Data Definition Language) 数据定义语言,定义数据库,表,列等</td> 
 </tr> 
 <tr> 
 <td style="width:110px;vertical-align:top;height:51px;">原理</td> 
 <td style="width:300px;vertical-align:top;height:51px;">逐条删除数据</td> 
 <td style="width:317px;vertical-align:top;height:51px;">删除表结构和数据,重建表</td> 
 </tr> 
 <tr> 
 <td style="width:110px;vertical-align:top;height:51px;">空间</td> 
 <td style="width:300px;vertical-align:top;height:51px;">删除数据在内存中有删除后碎片</td> 
 <td style="width:317px;vertical-align:top;height:51px;">很干净,没有空间碎片</td> 
 </tr> 
 <tr> 
 <td style="width:110px;vertical-align:top;height:51px;">效率</td> 
 <td style="width:300px;vertical-align:top;height:51px;">慢</td> 
 <td style="width:317px;vertical-align:top;height:51px;">快</td> 
 </tr> 
 <tr> 
 <td style="width:110px;vertical-align:top;height:51px;">恢复</td> 
 <td style="width:300px;vertical-align:top;height:51px;">可以恢复</td> 
 <td style="width:317px;vertical-align:top;height:51px;">数据不能恢复</td> 
 </tr> 
 <tr> 
 <td style="width:110px;vertical-align:top;height:51px;">自增</td> 
 <td style="width:300px;vertical-align:top;height:51px;">不影响自增顺序</td> 
 <td style="width:317px;vertical-align:top;height:51px;">影响,从1开始</td> 
 </tr> 
 </tbody> 
</table>

## 八、约束 ##

约束,即限制,就是通过设置约束,可以限制对数据表数据的插入,删除,更新

约束设置的语法,大部分是

create table 表名(

字段 数据类型(长度) 约束,

字段 数据类型(长度) 约束

);

### 8.**1 数据类型** ###

其实数据类型也是一种约束,例如设置id列为int类型,那就不能乱给id设置字符串或者日期等数据

### 8.**2 主键约束** ###

主键(**primary key**)约束非常重要,以后开发中基本上每张表都要有主键约束,作用是设置了主键约束的列,有以下效果

*  不能为空

*  不能重复

一般主键是给id设置的

设置主键方式有三种:

*  在**建表时**给列直接指定

*  在**建表语句的最后指定某一列是主键**

*  给以建好的表**修改设置主键**

-- ================= 主键 ================
    -- 方案1,在建表时指定
    create table t1 (
        id int(11) primary key, -- 主键
        name varchar (20)
    );
    -- 主键不能为空,报错 Field 'id' doesn't have a default value
    insert into t1 (name) values('aaa');
    -- 主键不能重复,报错Duplicate entry '1' for key 'PRIMARY' 
    insert into t1 (id,name) values(1,'aaa');
    insert into t1 (id,name) values(1,'aaa');
    -- 方案2,在建表语句的最后指定主键
    create table t2 (
     id int(11),
     name varchar(20),
     age int,
     primary key(id)  -- 指定主键
    );
    insert into t2(name,age) values ('cc',18);
    insert into t2(id,name,age) values (1,'cc',18);
    insert into t2(id,name,age) values (1,'cc',18);
    
    create table tb2(
    id int,
    age int,
    primary key(id),
    name varchar(20)
    );
    
    -- 方案3,对已建成的表设置主键
    create table t3(
     id int,
     name varchar(20)
    );
    insert into t3(name) values ('cc');
    
    -- 修改表,设置主键
    alter table t3 add primary key (id);

### 8**.3 自增约束** ###

自增(**auto\_increment**)约束,主要是配合主键使用,防止主键为空,重复

-- ================= 自增约束 ================
    create table t4(
     id int(11) primary key auto_increment,
     name varchar(20)
    );
    insert into t4(id,name) values (1,'aaa');
    -- 当没有给主键设置值是,自增会自动赋值
    insert into t4(name) values ('bbb');
    -- 还可以主动赋值
    insert into t4(id,name) values (4,'ddd');
    -- 当没有给主键设置值是,自增会自动赋值,自动根据上个id自增1
    insert into t4(name) values ('eee');
    delete from t4 where id = 5;
    -- 删除数据不影响自增的顺序
    insert into t4(name) values ('fff');

### 8**.4 唯一约束** ###

唯一(**unique**)约束,设置了唯一约束的列,的**值不能重复**

-- ================= 唯一约束 ================
    create table t5(
     id int,
     name varchar(20) unique -- 唯一约束
    );
    insert into t5(name) values('aa');
    -- 报错,name列的值重复, Duplicate entry 'aa' for key 'name'
    insert into t5(name) values('aa');

### **8.5 非空约束** ###

非空(**not null**)约束,设置了非空约束的列的值不能为空

-- ================= 非空约束 ================
    create table t6(
     id int,
     name varchar(20) not null -- 非空约束
    );
    -- name不能没有值
    insert into t6 (id) values (1);
    -- 给name赋值
    insert into t6 (id,name) values (1,'aaa');
    -- 只是不能为空,可以重复
    insert into t6 (id,name) values (2,'aaa');

### 8**.6 默认值** ###

默认值(**default**),给列设置默认值约束后,如果该列在插入数据时没有给值,就自动赋值默认值

-- ================= 默认值约束 ================
    create table t7 (
     id int,
     sex char(1) default '男'   -- 默认值
    ); 
    insert into t7 (id) values (1); -- 不给值时,默认填充男
    insert into t7 (id,sex) values (2,'女');

### **8.7 外键约束** ###

外键,是多表之间接的一种关联关系的一种限制.

constraint 外键名 foreign key (当前表中的列名) references 表(主键);

**子表不能乱插入，父表不能乱删除**

eg：设计订单表和商品表,订单表的数据要关联商品表数据

-- 商品表
    create table tb_goods(
     gid int primary key,
     gname varchar(20),
     descr varchar(20)
    );
    
    -- 订单表 (订单表关联了商品表)
    create table tb_order(
     oid int primary key,
     order_time datetime,
     gid int,
     -- 设置外键
     constraint fk_order_goods foreign key(gid) references tb_goods(gid)
    );
    /*
     被引用的表称为父表 parent , tb_goods
     引用别人的表称为子表 child , tb_order
    */
    -- 给父表随便插入数据
    insert into tb_goods values (2,'键盘','敲代码没有bug');
    -- 给子表随便插入数据不行!! 这个数据得是父表中有的才行
    insert into tb_order values (1,'2022-11-11',1);
    -- 子表可以删除数据
    delete from tb_order where oid = 1;
    
    -- 父表被引用的数据不能删除
    delete from tb_goods where gid = 2;
    delete from tb_goods where gid = 1;

*  RESTRICT：如果想要删除父表的记录时，而在子表中有关联该父表的记录， 则不允许删除父表中的记录；

*  NO ACTION：同 RESTRICT，也是首先先检查外键；

*  CASCADE：父表 delete、update 的时候，子表会 delete、update 掉关联记录；

*  SET NULL：父表 delete、update 的时候，子表会将关联记录的外键字段所在 列设为 null，所以注意在设计子表时外键不能设为 not null；