跳转至

MySQL基础知识01

1.数据库

1.1.创建数据库

语法：

CREATE DATABASE [IF NOT EXISTS] db_name [create_specification[,create_specification]...]
create_specification:

    [DEFAULT]CHARACTER SET charset_name
    [DEFAULT]COLLATE collation_name
  1. CHARACTER SET：指定数据库采用的字符集，如果不指定字符集，默认为utf8
  2. COLLATE：指定数据库字符集的校对规则（常用的uft8_bin[区分大小写]、utf8_general_ci[不区分大小写] 默认是 utf8_general_ci）

练习：

  1. 创建一个名为hsp_db01的数据库[图形和指令演示]
  2. 创建一个utf8字符集的hsp_db02的数据库
  3. 创建一个使用utf8字符集，并带校对队则的hsp_db03数据库

指令创建：

#演示数据库的操作
#1. 创建一个名为hsp_db01的数据库
CREATE DATABASE hsp_db01;

#删除数据库指令
DROP DATABASE hsp_db01;

#2. 创建一个utf8字符集的hsp_db02的数据库
CREATE DATABASE hsp_db02 CHARACTER SET utf8

#3. 创建一个使用utf8字符集，并带校对队则的hsp_db03数据库
CREATE DATABASE hsp_db03 CHARACTER SET utf8 COLLATE utf8_bin
#校对规则 utf8_bin 区分大小写   默认utf8_general_ci 不区分大小写

​ 可以看见在不指定采用字符集和校验规则的情况下，字符集默认为utf8，校验规则默认为utf8_general_ci

image-20221001131246165

关于字符集和校验规则：

如果在创建表的时候没有指定字符集和校验规则，则遵循和数据库一样的规则

如下：在数据库hsp_db02中创建表t1，t1设为默认字符集和校验规则。

在表t1中用select查询可以得到不区分大小写的两条数据：

image-20221001133406513

在数据库hsp_03中创建同样的表t1并插入同样的数据，查询后只得到一条数据：

image-20221001133908482

因为数据库hsp_03在创建时指定校验规则为utf8_bin，区分大小写，查询返回的数据也区分了大小写

数据库hsp_02没有指定大小写，返回的查询没有区分大小写

因此**可知当表设置默认的字符集和校验规则时，其则遵循数据库的字符集和校验规则**

1.2查看、删除数据库

  • 语法：
#显示数据库语句
#显示数据库创建时的语句
#数据库删除语句[一定要慎用]

练习

  1. 查看当前服务器中的所有数据库
SHOW DATABASES

image-20221001135837515

  1. 查看前面创建的hsp_02数据库的定义信息
SHOW CREATE DATABASE hsp_db02

image-20221001135916849

  1. 删除前面创建的数据库

  2. 关键字作名字创建数据库：

  3. 说明1：数据库名用反引号括起来，是为了规避数据名字为关键字

例如：创建一个名为create的数据库，不使用反引号就会报错

image-20221001140802726

使用了反引号之后，就可以成功创建数据库

image-20221001141001393

image-20221001141021681

使用关键字创建，在删除的时候也要在名字旁边反引号

image-20221001141223088

  • 说明2：后面的语句用/*!40100...*/ 说明版本要在4以上执行这条语句

1.3备份恢复

1.3.1备份恢复数据库

  • 语法：

备份数据库 (注意：在DOS执行)

mysqldump -u 用户名 -p -B 数据库1 数据库2 数据库n > 文件名.sql

恢复数据库（注意：进入MySQL命令行再执行）

Source 文件名.sql

练习：备份恢复数据库

备份hsp_db02和hsp_db03库中的数据，并恢复

  1. 备份：

如下，在Dos窗口下输入指令，指明备份的数据库和保存的文件名

image-20221001150557630

在指定的路径下生成了对应的sql文件

image-20221001150802838

这个备份的文件就是对应的sql语句：

image-20221001150909059

  1. 恢复

首先删除掉数据库hsp_db02和hsp_db03

登录账号，进入mysql命令行，输入指令 Source d:\\\bak.sql

image-20221001151406361

可以看到数据库及里面的信息成功恢复：

image-20221001151552728

1.3.2备份恢复数据库表

  • 语法：

备份数据库的表

mysqldump -u 用户名 -p 数据库 表1 表2 表n > 文件名.sql

(注意：在DOS执行)

image-20221001152812485

image-20221001152900535

恢复和数据库同理（注意进入mysql命令行执行）

2.表

2.1创建表

  • 语法：
CREATE TABLE table_name
(
    field1 datatype,
    field2 datatype,
    field3 datatype
)character set 字符集 collate 校对规则 engine 存储引擎

# field：指定列名  datatype：指定列类型（字段类型）
# character set：如不指定则为所在的数据库字符集
# collate：如不指定则为所在的数据库的校验规则
# engine：引擎（这个涉及较多，后面单独讲解）

练习1

在数据库hsp_db02创建一张表，根据需求的数据创建相应的列，并根据数据的类型定义相应的列类型

user表：

id          整形
name        字符串
passwoed    字符串
birthday    日期

#id         整形
#name       字符串
#passwoed   字符串
#birthday   日期
CREATE TABLE `user`(
    id INT,
    `name` VARCHAR(255),
    `password` VARCHAR(255),
    `birthday` DATE)
    CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB;

image-20221001160742953

练习2

创建一个员工表，选用适当的数据类型

image-20221002163609861

#创建表的课堂练习
CREATE TABLE emplyee(
     id INT,
     `name` VARCHAR(32),
     sex CHAR(1),
     birthday DATE,
     entry_date DATE,
     job VARCHAR(32),
     Salary FLOAT,
     `resume` TEXT ) CHARSET utf8 COLLATE utf8_bin ENGINE INNODB;

--  添加一条数据
INSERT INTO emplyee VALUES (
    100,'小妖怪','男','2000-11-11',
    '2010-11-10','巡山的',3000,'大王叫我来巡山');

SELECT * FROM emplyee ;

image-20221002170648401

2.2删除、修改表

使用ALTER TABLE 语句追加，修改，或删除列的语法

  • 语法：

添加列：

ALTER TABLE tablename
ADD (column datatype [DEFAULT expr][,column datatype]...);

修改列：

ALTER TABLE tablename
MODIFY (column datatype [DEFAULT expr][,column datatype]...);

删除列：

ALTER TABLE tablename
DROP (column);
查看表的结构：desc 表名; -- 可以查看表的列

修改表名：

Rename table 表名 to 新表名;

修改表的字符集：

alter table 表名 character set 字符集;

应用案例

  1. 在员工表上增加一个image列，为vachar类型（要求在resume后面）
  2. 修改job列，使其长度为60
  3. 删除sex列
  4. 表名修改为emp
  5. 修改表的字符集为utf8
  6. 列名name修改为user_name
# 修改表的操作练习
-- 1. 在员工表上增加一个image列，为vachar类型（要求在resume后面）
ALTER TABLE emplyee 
    ADD image VARCHAR(32) NOT NULL DEFAULT '' 
    AFTER RESUME;

-- 2. 修改job列，使其长度为60
ALTER TABLE emplyee 
    MODIFY job VARCHAR(60) NOT NULL DEFAULT ''

-- 3. 删除sex列
ALTER TABLE emplyee
    DROP sex;

-- 4. 表名修改为emp
RENAME TABLE emplyee TO emp;

-- 5. 修改表的字符集为utf8
ALTER TABLE emp CHARACTER SET utf8;

-- 6. 列名name修改为user_name
ALTER TABLE emp
    CHANGE `name` user_name VARCHAR(64) NOT NULL DEFAULT ''

DESC emp -- 显示表结构，可以查看表的所有列

image-20221002180939425

3.MySQL常用数据类型（列类型）

image-20221001165855153

Mysql列类型即mysql的数据类型

详见MySQL的参考手册

3.1列类型之整型

  • 说明

数值型（整型）的基本使用

说明：使用规范：在能够满足需求的情况下尽量选择空间小的类型

image-20221001173132855

应用实例

  • 3.1.2.1无符号TINYINT

  • 在数据库hsp_02中创建一个表t2，在表中插入列类型tinyint，列名为id

CREATE TABLE t2(
id TINYINT);
  1. 在表中插入数据 -129，执行后提示错误，可以看到t2为空表

image-20221001174310390 image-20221001174632661

  1. 往表中插入数据-128，执行成功，表中成功插入数据

image-20221001174822054 image-20221001174839813

  1. 同理，分别往表中插入数据128、127，只有127插入成功

说明在有符号情况下，tinyint的范围为-128~127

image-20221001175049183

  • 有符号TINYINT

在列类型后面指定无符号

CREATE TABLE t3(
id TINYINT UNSIGNED);

INSERT INTO t3 VALUES(-1);#错误，超出范围
INSERT INTO t3 VALUES(0);
INSERT INTO t3 VALUES(256);#错误，超出范围
INSERT INTO t3 VALUES(255);

image-20221001175845175

3.2列类型之BIT

  • 说明

  • bit字段显示时，按照位的方式显示（按照指定位数的二进制显示）

  • 查询的时候仍然可以使用 添加时的数值（比如十进制）来查询
  • 如果一个值只有0,1，可以考虑使用bit(1)，可以节省空间
  • 位类型。M指定位数，默认值1，范围1-64

练习

#演示bit类型使用
#说明
#1.bit(M) M在1-64位
#2.添加数据 的范围是按照你给定的M的位数来确定，例如M=8 表示一个字节(8bit) 0~255
create table t5 (num bit(8));
INSERT INTO t5 VALUES(5);
insert into t5 values(3);
select * from t5;
#3.查询时仍然可以按照十进制数来查询
select * from t5 where num = 5;

3.3列类型之小数类型

  • 说明

  • FLOAT/DOUBLE[UNSIDNED]

float 单精度，double 双精度

  1. DECIMAL[M,D] [UNSIGNED]

  2. 可以支持更加精确的小数位。M是小数位数(精度)的总数，D是小数点(标度)后面的位数。

  3. 如果D是0，则值没有小数点或分数部分。M最大65，D最大是30。如果D被省略，默认D是0；如果M被省略，默认M是10.

  4. 建议：如果希望小数点的精度高，推荐使用decimal

练习

#案例演示 float、double、decimal的使用
CREATE TABLE t6(
num1 FLOAT,
num2 DOUBLE,
num3 DECIMAL(30,20));
#添加数据
INSERT INTO t6 VALUES(88.12345678912345,88.12345678912345,88.12345678912345);

#查询
SELECT * FROM t6;

#decimal可以存放很大的数

image-20221001200555093

3.4列类型之字符串(文本类型)

  • 说明

字符串的基本使用：

CHAR(size)

固定**长度字符串 最大255**字符

VARCHAR(size)

可变**长度字符串 最大65532**字节

[ utf8编码最大字符为21844字符，1-3个字节用于记录大小，uft8编码每个字符占用三个字节 ]

如果编码是uft8 则varchar(size) size = (65535-3) / 3 = 21844

如果编码是gbk 则varchar(size) size = (65535-3) / 2 = 32766

错误使用:（uft8）

image-20221001203037160

​ 成功插入：(uft8)

image-20221001203356769

​ gbk字符集下：

image-20221001204145788

  • 字符串使用细节

  • 细节1：

  • 如 char(4) //这个4表示字符数（最大255），不是字节数，不管是中文还是字母都是放四个，按字符计算

  • varchar(4) //这个4表示字符数，(这四个字符占用多少个字节取决于你定义的编码) 不管是字母还是中文都以定义好的表的编码来存放数据

上面的例子不管是中文还是字母都是最多存放4个，是按照字符来存放的

  • 细节2：

  • char(4)是定长（固定的大小），就是说，即时你插入'aa'，也会占用分配的4个字符

  • varchar(4)是可变长，即如果你插入的是'aa'，实际占用的空间大小并不是4个字符，而是按照实际占用空间来分配。VARCHAR值保存时只保存需要的字符数，另加一个字节来记录长度(如果列声明的长度超过255，则使用两个字节) ，因此varchar的实际长度=实际的数据大小+（1-3个字节）

image-20221001212928485

  • 细节3：使用的的时机
  • 如果数据是定长，推荐使用char，比如md5的密码，邮编，手机号，身份证号码等
  • 如果一个字段的长度不定，就使用varchar，比如留言，文章

  • 查询速度 char > varchar

  • 细节4：

  • 在存放文本时，也可以使用text数据类型。可以将text列视为varchar列，注意text不能有默认值，大小 0~2^16字节
  • 如果希望存放更多字符，可以选择mediumtext 0~2^24 或者 longtext 0~2^32

例子：

在下图中，不管插入的是中文还是字母，每一个都按一个字符来算，因此'abcde'相当5个字符长度，插入失败；varchar同理。

失败：

image-20221001210037354

成功：

image-20221001210336635

例子2：关于text，mediumtext 和 longtext的使用

image-20221001214822449

如上图所示：在插入的数据后面显示了实际使用的数值大小

content1有两个中文=2*3=6bit，每个数字占用一个字节，共计9bit

content2也有两个中文=2*3=6bit，有八个数字，共计14bit

3.5列类型之日期类型

  • 日期类型的细节说明

TimeStamp在Insert和Update时，会自动更新

例子：

#演示时间相关的案例
#创建一张表，data ，datatime，timestamp
CREATE TABLE t13(
    birthday DATE,-- 生日
    job_time DATETIME,-- 记录年月日 时分秒
    login_time TIMESTAMP
        NOT NULL DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP);-- 登录时间，如果希望login_time列自动更新，需要配置

INSERT INTO t13(birthday,job_time)
VALUES('2022-11-11','2022-11-11 10:10:10');
#如果我们更新了t13的某条记录，login_time会自动地以当前时间来进行更新

SELECT * FROM t13;

image-20221001225439528