2022-08-15 - 初识MySQL

阅读原文时间：2023年07月08日阅读：3

MySQL数据库

数据库,又称为Database，简称DB。数据库就是一个文件集合。

顾名思义：是一个存储数据的仓库，实际上就是一堆文件，这些文件中存储了具有特定格式的数据，可以很方便的对里面的数据进行增删改查等操作。

DataBase Management System，简称DBMS。

数据库管理系统是专门用来管理数据库中的数据的，可以对数据库当中的数据进行增删改查。

常见的DBMS有：MySQL、Oracle、DB2、SQLite 、SqlServer。

程序员主要学习SQL语句，通过编写SQL语句，然后DBMS负责执行SQL语句，最终来完成数据库中数据的增删改查操作。

DBMS--执行-->SQL--操作-->DB

基本操作

启动服务：net start mysql

停止服务：net stop mysql

第一种：

mysql -h 127.0.0.1 -p3306 -uroot -p

第二种：

mysql -uroot -p

create database 数据库名;

create schema 数据库名;

show databases;

MySQL默认自带了4个数据库

+--------------------+

| Database |

+--------------------+

| information_schema |

**| mysql |

| performance_schema |**

| pythonweb |

| questionnaire |

| sys |

+--------------------+

use 数据库名

表

数据库中最基本的单元是：表(table)

用来存储数据的对象，是有结构的数据的集合。

数据表是一个临时保存数据的网格虚拟表(表示内存中数据的一个表)。

数据表是由表名、字段、字段类型、字段长度、数据记录组成。数据表之间的关联通过“键”来实现的，键分为主键和外键两种。

任何一张表都有行和列。

行：一行即为一条数据，数据库一共有多少条数据，实际上就是有几行数据。
列：一列即为一个字段，数据库一共有多少个字段，实际上就是有几列数据。

SQL语言

SQL是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存储数据以及查询、更新和管理关系型数据库系统。

SQL分类

根据功能主要分为四类：DDL、DML、DQL、DCL

DCL（Data Control Language）：数据控制语言，用来创建数据库用户、定义访问权限和安全级别。
DDL（Data Definition Language）：数据定义语言，用来定义数据库对象：库，表，字段（列）。功能：创建、删除、修改库和表结构。
DML（Data Manipulation Language）：数据操作语言，用来定义数据的增删改记录。
DQL（Data Query Language）：数据库查询语言，用来查询数据库中表的记录。
TCL（Transition Control Language）：事务控制语言，用来管理事务。

DDL主要是用在定义或改变表的结构。

查询

查询所有数据库SHOW DATABASES;

查询当前数据库SELECT DATABASE();
创建

create table 表名(

字段名1(列名) 类型(长度) 约束条件,

字段名2(列名) 类型(长度) 约束条件,

字段名3(列名) 类型(长度) 约束条件,

…….

);

在关系型数据库中，我们需要这顶表名和列名，同时设定。

数据类型

整型

MySQL数据类型

含义

tinyint

1字节，范围（-128~127）

smallint

2字节，范围（-3W多~3W多）

mediumint

3字节，范围

int

4字节，范围（-21个亿~21个亿）

bigint

8字节，非常大

在整型中，我们默认使用的是【有符号】的，我们可以使用unsigned关键字，定义成无符号类型，tinyint unsigned的取值范围0~255。

如果长度需要配合zerofill：

int(4) unsigned zerofill;

说明：上述的int长度为4，如果设置了zerofill，如果数据是1，最终存到表格中的数据格式为0001，0010。

浮点型

MySQL数据类型

含义

float(m,d)

4字节，单精度浮点型，m总长度，d小数位。

double(m,d)

8字节，双精度浮点型，m总长度，d小数位。

decimal(m,d)

decimal是存储为字符串的浮点数，对应我们java中的BigDecimal。

比如定义一个float(5,3)：

插入123.45678，最后查询得到的结果就是99.999；
插入12.3456789，最后查询得到的结果就是12.346；

所以，在使用浮点型的时候，要以插入到数据库中的实际结果为准。

字符串类型

MySQL数据类型

含义

char(n)

固定长度，最多255个字符。

varchar(n)

可变长度，最多65535个字符。

tinytext

可变长度，最大255个字节。

text

可变长度，最大65535个字节。

mediumtext

可变长度，最大16MB。

longtext

可变长度，最大4GB。

（1）char和vachar的区别：

char

char表示定长字符串，长度是固定的；

如果插入数据的长度小于char的固定长度时，则用空格填充；

因为长度固定，所以存取速度要比varchar快很多，甚至能快50%，但正因为其长度固定，所以会占据多余的空间，是空间换时间的做法；

对于char来说，最多能存放的字符个数为255，和编码无关

varchar

varchar表示可变长字符串，长度是可变的；

插入的数据是多长，就按照多长来存储；

varchar在存取方面与char相反，它存取慢，因为长度不固定，但正因如此，不占据多余的空间，是时间换空间的做法；

对于varchar来说，最多能存放的字符个数为65532

日常的设计，对于长度相对固定的字符串，可以使用char，对于长度不确定的，使用varchar更合适一些。

（2）varchar和text区别:

text不能设置默认值，varchar可以设置默认值。
text类型，由于单表的最大行宽的限制，支持溢出存储，只会存放768字节在数据页中，剩余的数据存储在溢出段中。
一般我们都是用varchar。

日期类型

MySQL数据类型

含义

date

3字节，日期，格式：2022-08-15

time

3字节，时间，格式：10:54:30

datatime

8字节，日期时间，格式：2022-08-15 10:55:40

timestamp

4字节，时间戳，毫秒数。

year

1字节，年份

建表约束

因为一张表要有多个列，数据库中的表不止有一张，建表约束说的就是我们应该如何规范表中的数据以及表之间的关系。

MySQL约束类型：

约束名称

描述

NOT NULL

非空约束

UNIQUE

唯一约束，取值不允许重复

PRIMARY KEY

主键约束（主关键字），自带非空，唯一、索引

DEFAULT

默认值

FOREIGH KEY

外键约束，表和表之间的约束

（1）NOT NULL约束

CREATE TABLE `student` (
    `stu_id` int,
    `stu_name` VARCHAR(50) NOT NULL,
    `gender` char(1) DEFAULT '男',
    `brithday` datetime,
    PRIMARY KEY(stu_id)
);

（2）UNIQUE约束

create table `book` (
    `id` int PRIMARY KEY auto_increment,
    `name` varchar(50) not null,
    `bar_code` VARCHAR(30) not null,
    `aut_id` int not null,
    UNIQUE(bar_code)
);

（3）主键约束

用多个列来共同当主键：

create table `author`(
    `aut_id` int,
    `aut_name` varchar(50) not null,
    `gender` char(1) default '男',
    `country` varchar(50),
    `birthday` datetime,
    PRIMARY KEY(aut_id,aut_name)
);

（4）外键约束

推荐配合主键去使用。有了这个约束，我们在向表中插入数据时，来源于另外一张表的主键

外键会产生的效果：

删除表的时候，如果不删除引用外键的表，被引用的表是不能直接删除。
外键的值必须来源于引用的表的主键字符。

create table author(
aut_id int,
aut_name varchar(50) not null,
gender char(1) default '男',
country varchar(50),
birthday datetime,
PRIMARY KEY(aut_id)
);

create table book (
id int PRIMARY KEY auto_increment,
name varchar(50) not null,
bar_code VARCHAR(30) not null UNIQUE,
aut_id int not null,
FOREIGN KEY(aut_id) REFERENCES author(aut_id)
);

在创建表的时候，建议字段名使用着重符。

对表的修改操作

查看当前库中的所有表：

show tables;

查看表结构：

desc 表名;

修改表有5个操作，但是前缀都是一样的alter table 表名 ....。

添加列

ALTER table author add (hobby varchar(20),address varchar(50));

修改列数据类型

ALTER table author MODIFY address varchar(100);

修改列名称和数据类型

alter table author change address addr VARCHAR(60);

删除列
```
alter table author drop addr;
```
修改表名
```
ALTER TABLE author RENAME `authors`;
```
删除表
```
drop table if EXISTS `user`;
```

添加主键

alter table 表名 ADD CONSTRAINT 主键名(pk_表名) primary key 表名(字段名);


ALTER TABLE `authors` ADD CONSTRAINT pk_authors PRIMARY KEY `authors` ( aut_id );

添加外键

alter table 从表 add constraint 外键名(fk_从表_主表) foreign key 从表(外键字段) REFERENCES 主表(主键字段;)


ALTER TABLE book ADD CONSTRAINT fk_book_authors FOREIGN KEY book ( aut_id ) REFERENCES `authors` ( aut_id );

DML（数据操作语言）

该语言来对表记录进行操作（增、删、改），不包含查询。

INSERT INTO `authors` ( aut_id, aut_name, gender, country, birthday, hobby ) VALUES (4,'罗曼罗兰','女','漂亮国','1945-8-15','写字');

如果插入的是全字段，字段名可以省略。

INSERT INTO `authors` VALUES (5,'韩寒','男','中国','1984-8-15','赛车');

说明：

在数据库中所有的字符串类型，必须使用引号。
如果部分字段插入，必须列名和值要匹配。如果全字段插入，则列名可以省略。

批量插入。

INSERT INTO `authors` VALUES
(7,"李诞",'男','中国','1985-8-15','脱口秀'),
(8,"史铁生",'男','中国','1967-8-15','绘画');

修改某列的全部的值：

update `authors` set aut_name = '郭小四',country='中国';

修改特定行的数据：

update `authors` set aut_name = '金庸',country='中国' where aut_id = 1;

where是一个关键字，我们可以使用where关键字实现丰富的筛选，它很像我们的if语句，可以使用各种复杂的条件运算：

=（没有==，也没有equals）

!=

>

where aut_id > 1

<

>=

<=

<> 不等于

between…and

where aut_id between 1 and 4

where aut_id > 1 and aut_name='xxx'

in(….)

where aut_id in(1,3,5)

is null

where name is null

not

where name is not null

or

and