MySQL语句细解------笔记【更新中】

于 2020-03-29 00:52:25 发布
阅读量1k 收藏 1
点赞数
文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/forest_black/article/details/105171971
版权

文章目录

开篇重点提示

本篇基于某站学习视频和资料总结下来的，个人学习笔记，如果有朋友在看，有错欢迎提出。

有关键字部分的，概括尽量放在了分类开头，不想看细节的可以直接点击目录的关键字。

【代码测试】部分会有特意写错的语句（例如enum限制了中文却输入英文的语句），因为想看错误提示。

【代码测试】正确的语句也不一定能运行出来，因为可能对应的表创建语句没有写出来，所以只当参考，了解语句怎么写的即可。

【语句细解】主要是针对代码测试详细讲解，如果代码测试涵盖所有细解，那语句解释就会放在这部分。



database 库相关

-- 创建数据库
create database mydatabase;

create database mydatabase2 charset gbk;

-- 显示所有数据库
show databases;

-- 查看以my开头的数据库
show databases like 'my%';

-- 查看数据库创建语句
show create database mydatabase;

-- 选择数据库
use mydatabase;

-- 修改数据库字符集
alter database mydatabase charset gbk;

-- 删除数据库
drop database mydatabase;



table 表相关

-- 将数据表挂到数据库下
create table mydatabase2.class(
name varchar(10)
);

-- 进入数据库，创建表
use mydatabase2;
create table teacher(
name varchar(10)
);

-- 使用表选项
create table student(
name varchar(10)
)charset utf8;

-- 在test数据库下创建一个与teacher一样的表
use test;

create table teacher like mydatabase2.teacher;


-- 查看所有表
show tables;


-- 查看匹配数据表
show tables like 'c%';

-- 显示表结构
describe class;

desc teacher;

show columns from student;

-- 查看表创建语句
show create table student;


show create table student\G

-- 修改表选项
alter table student charset gbk;


-- 数据库中数据表名字通常有前缀：取数据库的前两个字母加上下划线
rename table student to my_student;

-- 给学生表增加age字段
alter table my_student add column age int;

-- 增加字段：放到第一个字段
alter table my_student add id int first;

-- 修改字段名
alter table my_student change age nj int;

-- 修改字段类型
alter table my_student modify name varchar(20);

-- 删除字段
alter table my_student drop nj;

-- 删除表名
drop table class;

drop table teacher,my_student;



data 数据相关

-- 插入数据到数据表

create table my_teacher(
name varchar(10),
age int
)charset utf8;


insert into my_teacher (name,age) values('Jack',30);

insert into my_teacher (age,name) values(49,'Tom');

insert into my_teacher (name) values('Han');

insert into my_teacher values('Lilei',28);

-- 获取所有数据
select * from my_teacher;

-- 获取指定字段数据
select name from my_teacher;

-- 获取年龄为30岁的人的名字
select name from my_teacher where age = 30;

-- 删除年龄为30岁的老师
delete from my_teacher where age = 30;

-- 更新年龄Han
update my_teacher set age = 28 where name = 'Han';





DELETE FROM customer WHERE
    cus_name IN (SELECT cus_name FROM
            (SELECT cus_name FROM customer GROUP BY cus_name
                HAVING
                    count(cus_name) > 1
            ) a
    )
AND cid NOT IN (
    SELECT
        min(cid)
    FROM
        (
            SELECT
                min(cid) AS cid
            FROM
                customer
            GROUP BY
                cus_name
            HAVING
                count(cus_name) > 1
        ) b



character 字符

-- 插入中文数据

insert into my_teacher values('张三',34);
insert into my_teacher values('李四',35);

-- mysql.exe告知Mysqld.exe自己的字符集规则
set names gbk;

-- 查看系统保存的三种关系处理字符集
show variables like 'character_set%';

-- 修改变量
set character_set_client = gbk;
set character_set_results = gbk;

create table my_charset(
name varchar(10) charset gbk, -- 为当前字段设定字符集
nickname varchar(1) charset utf8
)charset utf8; -- 表示表中如果字段本身没有字符集那么才有utf8

-- 查看所有字符集
show character set;



列类型

整数型

Int
Tinyint
-- 迷你整形，系统采用一个字节来保存的整形：一个字节 = 8位，最大能表示的数值是0-255
Smallint
-- 小整形，系统采用两个字节来保存的整形：能表示0-65535之间
Mediumint
-- 中整形，采用三个字节来保存数据。
Int
-- 整形（标准整形），采用四个字节来保存数据。
Bigint
-- 大整形，采用八个字节来保存数据。

实际应用中，应该根据对应的数据的范围来选定对应的整形类型：通常使用的比较多的Tinyint和int。

测试代码
-- 创建数据表
create table my_int(
int_1 tinyint,
int_2 smallint,
int_3 mediumint,
int_4 int,
int_5 bigint
)charset utf8;

-- 插入数据
insert into my_int values(10,10000,100000,10000000,1000000000);

insert into my_int values(255,255,255,255,255);

insert into my_int values(-128,255,255,255,255);

alter table my_int add int_6 tinyint unsigned first;

insert into my_int values(255,100,255,255,255,255);
insert into my_int values(-100,100,255,255,255,255);

insert into my_int values(1,1,1,1,1,1,1);

alter table my_int add int_7 tinyint zerofill first;
alter table my_int add int_8 tinyint(2) zerofill first;

insert into my_int values(100,1,1,1,1,1,1,1);
insert into my_int values(1,1,1,1,1,1,1,1);
语句细解

整型储存：

所有位都为1

计算结果：一字节数据长度 0-255

11111111
unsigned 无符号标识设定

表示存储的数据在当前字段中，没有负数（只有正数，区间为0-255）
基本语法：在类型之后加上unsigned

显示长度

指数据（整型）在数据显示的时候，到底可以显示多长位。

Tinyint(3)：表示最长可以显示3位，unsigned说明只能是正数，0-255永远不会超过三个长度

Tinyint(4)：表示最长可以显示4位，-128~127

zerofill 显示长度保持最高位

显示长度只是代表了数据是否可以达到指定的长度，但是不会自动满足到指定长度：如果想要数据显示的时候，保持最高位（显示长度），那么还需要给字段增加一个zerofill属性才可以。

Zerofill：从左侧开始填充0（左侧不会改变数值大小），所以负数的时候就不能使用zerofill，一旦使用zerofill就相当于确定该字段为unsigned




小数型

Float

又称之为单精度类型：系统提供4个字节用来存储数据，但是能表示的数据范围比整型大的多，大概是10^38；
只能保证大概7个左右的精度（如果数据在7位数以内，那么基本是准确的，但是如果超过7位数，那么就是不准确的）

Float
-- 表示不指定小数位的浮点数
Float(M,D)
-- 表示一共存储M个有效数字，其中小数部分占D位
Float(10,2)
-- 整数部分为8位，小数部分为2位
Double
Double
-- 又称之为双精度：系统用8个字节来存储数据，表示的范围更大，10^308次方，但是精度也只有15位左右。
Decimal
Decimal
-- 系统自动根据存储的数据来分配存储空间，每大概9个数就会分配四个字节来进行存储，同时小数和整数部分是分开的。
Decimal(M,D)
-- M表示总长度，最大值不能超过65，D代表小数部分长度，最长不能超过30。
测试代码
-- 创建表
create table my_float(
    f1 float,
    f2 float(10,2)
)charset utf8;

create table my_decimal(
    f1 float(10,2),
    d1 decimal(10,2)
)charset utf8;

-- float
insert into my_float values(123.123,12345678.90);

insert into my_float values(123.1234567,123456789.00);

insert into my_float values(123.1234567,99999999.99);

insert into my_float values(123.123,10e5);


-- decimal
insert into my_decimal values(12345678.90,12345678.90);

insert into my_decimal values(99999999.99,99999999.99);
insert into my_decimal values(99999999.99,99999999.999);

小数型：专门用来存储小数的，在Mysql中将小数类型又分为两类：浮点型和定点型

浮点型 float/Double

  • 浮点型又称之为精度类型：是一种有可能丢失精度的数据类型，数据有可能不那么准确（由其是在超出范围的时候）

  • 浮点型之所以能够存储较大的数值（不精确），原因就是利用存储数据的位来存储指数

  • 浮点数的应用：通常是用来保存一些数量特别大，大到可以不用那么精确的数据。

浮点型储存：

有部分用于存储数据，有部分用于存指数

前三位转换成十进制之后用作10的指数： 10^7 * 数据值

11111111
语句细解
  1. 如果数据精度丢失，那么浮点型是按照四舍五入的方式进行计算

在这里插入图片描述

  1. 数据长度刚好满足条件，但是会超出精度

在这里插入图片描述

说明：用户不能插入数据直接超过指定的整数部分长度，但是如果是系统自动进位导致，系统可以承担。

  1. 浮点数可以采用科学计数法来存储数据

在这里插入图片描述



定点数 Decimal

  • 能够保证数据精确的小数（小数部分可能不精确，超出长度会四舍五入），整数部分一定精确

  • 定点数的应用：如果涉及到钱的时候有可能使用定点数

  1. 插入正常数据

在这里插入图片描述

  1. 插入最大数据

在这里插入图片描述

  1. 尝试定点数进行四舍五入
    在这里插入图片描述


时间型

Date
-- 日期类型：系统使用三个字节来存储数据，对应的格式为：YYYY-mm-dd，能表示的范围是从1000-01-01 到9999-12-12，初始值为0000-00-00
Time
-- 时间类型：能够表示某个指定的时间，但是系统同样是提供3个字节来存储，对应的格式为：HH:ii:ss，但是mysql中的time类型能够表示时间范围要大的多，能表示从-838:59:59~838:59:59，在mysql中具体的用处是用来描述时间段。
Datetime
-- 日期时间类型：就是将前面的date和time合并起来，表示的时间，使用8个字节存储数据，格式为YYYY-mm-dd HH:ii:ss，能表示的区间1000-01-01 00:00:00 到9999-12-12 23:59:59，其可以为0值：0000-00-00 00:00:00
Timestamp
-- 时间戳类型：mysql中的时间戳只是表示从格林威治时间开始，但是其格式依然是：YYYY-mm-dd HH:ii:ss
Year
-- 年类型：占用一个字节来保存，能表示1900~2155年，但是year有两种数据插入方式：0~99和四位数的具体年
测试代码
create table my_date(
d1 date,
d2 time,
d3 datetime,
d4 timestamp,
d5 year
)charset utf8;

insert into my_date values(
'1900-01-01','12:12:12','1900-01-01 12:12:12','1999-01-01 12:12:12',69);
insert into my_date values(
'1900-01-01','12:12:12','1900-01-01 12:12:12','1999-01-01 12:12:12',2020);
insert into my_date values(
'1900-01-01','12:12:12','1900-01-01 12:12:12','1999-01-01 12:12:12',70);


update my_date set d1 = '2000-01-01' where d5 = 2069;

insert into my_date values(
'1900-01-01','512:12:12','1900-01-01 12:12:12','1999-01-01 12:12:12',70);


insert into my_date values(
'1900-01-01','5 12:12:12','1900-01-01 12:12:12','1999-01-01 12:12:12',70);
语句细解
  1. 创建对应的时间日期类型的数据表

在这里插入图片描述

  1. year的特殊性：可以采用两位数的数据插入，也可以采用四位数的年份插入

在这里插入图片描述

  1. year进行两位数插入的时候，有一个区间划分，零界点为69和70：当输入69以下，那么系统时间为20+数字，如果是70以上，那配系统时间为19+数字

在这里插入图片描述
在这里插入图片描述

  1. timestamp当对应的数据被修改的时候，会自动更新（这个被修改的数据不是自己）

在这里插入图片描述

  1. time类型特殊性：本质是用来表示时间区间（当前时间之后的多少个小时），能表示的范围比较大

在这里插入图片描述

  1. 在进行时间类型录入的时候（time）还可以使用一个简单的日期代替时间，在时间格式之前加一个空格，然后指定一个数字（可以是负数）：系统会自动将该数字转换成天数 * 24小时，再加上后面的时间。

在这里插入图片描述

PS：PHP中有着非常强大的时间日期转换函数：date将时间戳转换成想要的格式，strtotime又可以将很多格式转换成对应的时间戳。PHP通常不需要数据库来帮助处理这么复杂的时间日期，所以通常配合PHP的时候，时间的保存通常使用时间戳（真正），从而用整型来保存字符串型。



字符串型

Char
-- 定长字符：指定长度之后，系统一定会分配指定的空间用于存储数据
-- 基本语法：char(L)，L代表字符数（中文与英文字母一样），L长度为0到255
Varchar
-- 变长字符：指定长度之后，系统会根据实际存储的数据来计算长度，分配合适的长度（数据没有超出长度）
-- 基本语法：Varchar(L)，L代表字符数，L的长度理论值位0到65535

/*本质上mysql提供了两种文本类型*/
Text
-- 存储普通的字符文本
Blob
-- 存储二进制文本（图片，文件），一般都不会使用blob来存储文件本身，通常是使用一个链接来指向对应的文件本身。

/*系统中提供的四种text*/
Tinytext
-- 系统使用一个字节来保存，实际能够存储的数据为：2 ^ 8 + 1
Text
-- 使用两个字节保存，实际存储为：2 ^ 16 + 2
Mediumtext
-- 使用三个字节保存，实际存储为：2 ^ 24 + 3
Longtext
-- 使用四个字节保存，实际存储为：2 ^ 32 + 4

Enum
-- 枚举类型：在数据插入之前，先设定几个项，这几个项就是可能最终出现的数据结果。
-- 如果确定某个字段的数据只有那么几个值：如性别，男、女、保密，系统就可以在设定字段的时候规定当前字段只能存放固定的几个值：使用枚举
-- 基本语法：enum(数据值1,数据值2…)

Set
-- 集合：是一种将多个数据选项可以同时保存的数据类型，本质是将指定的项按照对应的二进制位来进行控制：1表示该选项被选中，0表示该选项没有被选中。

-- 基本语法：set(‘值1’,’值2’,’值3’…)
char与varchar

因为varchar要记录数据长度（系统根据数据长度自动分配空间），所以每个varchar数据产生后，系统都会在数据后面增加1-2个字节的额外开销：是用来保存数据所占用的空间长度

如果数据本身小于127个字符：额外开销一个字节；如果大于127个，就开销两个字节

Char和varchar数据存储对比（utf8，一个字符都会占用3个字节）

存储数据Char(2)Varchar(2)Char所占字节Varchar所占字节
AAA2 * 3 = 61 * 3 + 1 = 4
ABABAB2 * 3 = 62 * 3 + 1 = 7

二者区别：

  1. char一定会使用指定的空间，varchar是根据数据来定空间
  2. char的数据查询效率比varchar高：varchar是需要通过后面的记录数来计算
  • 如果确定数据一定是占指定长度，那么使用char类型；
  • 如果不确定数据到底有多少，那么使用varchar类型；
  • 如果数据长度超过255个字符，不论是否固定长度，都会使用text，不再使用char和varchar
Text

注意：

  1. 在选择对应的存储文本的时候，不用刻意去选择text类型，系统会自动根据存储的数据长度来选择合适的文本类型。

  2. 在选择字符存储的时候，如果数据超过255个字符，那么一定选择text存储。

接下来，我们说说这个场景的问题：

当varchar（n）后面的n非常大的时候我们是使用varchar好，还是text好呢？这是个明显的量变引发质变的问题。我们从2个方面考虑，第一是空间，第二是性能。

首先从空间方面：

从官方文档中我们可以得知当varchar大于某些数值的时候，其会自动转换为text，大概规则如下：

  • 大于varchar（255）变为 tinytext
  • 大于varchar（500）变为 text
  • 大于varchar（20000）变为 mediumtext

所以对于过大的内容使用varchar和text没有太多区别。

其次从性能方面：

索引会是影响性能的最关键因素，而对于text来说，只能添加前缀索引，并且前缀索引最大只能达到1000字节。

而貌似varhcar可以添加全部索引，但是经过测试，其实也不是。由于会进行内部的转换，所以long varchar其实也只能添加1000字节的索引，如果超长了会自动截断

在mysql中，有一项规定：mysql的记录长度（record == 行row）总长度不能超过65535个字节。

Varchar能够存储的理论值为65535个字符：字符在不同的字符集下可能占用多个字节。

测试代码 varchar长度测试
-- varchar测试 (Mysql记录长度)

create table my_varchar(
name varchar(65535)
)charset utf8;

-- utf8 65535 / 3 = 21845  如果采用varchar存储：需要2个额外的字节来保存长度
-- gbk  65535 / 2 = 32767 | 1 如果采用varchar存储：需要额外2个字节

create table my_utf(
name varchar(21844)
)charset utf8;

create table my_gbk(
name varchar(32766)
)charset gbk;
语句细解

  1. 证明varchar在mysql中能够达到的理论值（utf8和GBK）

    Varchar除了存储的数据本身要占用空间：还需要额外的空间来保存记录长度

在这里插入图片描述

  1. 计算在utf8和GBK下对应的varchar能够存储的长度

    Utf8 最多只能存储21844个字符

    GBK最多只能存储32766个字符

在这里插入图片描述

Enum

枚举enum的存储原理：实际上字段上所存储的值并不是真正的字符串，而是字符串对应的下标：当系统设定枚举类型的时候，会给枚举中每个元素定义一个下标，这个下标规则从1开始

Enum(1=>‘男’,2=>’女’,3=>’保密’)

特性：在mysql中系统是自动进行类型转换的：如果数据碰到“+、-、*、/”系统就会自动将数据转换成数值：而普通字符串转换成数值为0。

系统提供了1到2个字节来存储枚举数据：通过计算enum列举的具体值来选择实际的存储空间：如果数据值列表在255个以内，那么一个字节就够，如果超过255但是小于65535，那么系统采用两个字节保存。

既然实际enum字段存储的结果是数值：那么在进行数据插入的时候，就可以使用对应的数值来进行。

枚举的意义：

  1. 规范数据本身，限定只能插入规定的数据项
  2. 节省存储空间
Set

系统为set提供了多个字节进行保存，但是系统会自动计算来选择具体的存储单元

1个字节：set只能有8个选项

2个字节：set只能有16个选项

3个字节：set只能表示24个选项

8个字节：set可以表示64个选项

Set和enum一样，最终存储到数据字段中的依然是数字而不是真实的字符串

  1. 插入数据：可以插入多个数据，就是在数据插入的字符串中，使用对应的逗号“,”将选项进行隔开
    在这里插入图片描述

  2. 数据选项所在的数据与数据插入的顺序无关：最终都会变成选项对应的顺序

在这里插入图片描述

分析数据存储的方式

  1. 系统将对应的数据选项（设计）按照顺序进行编排：从第一个开始进行占位，每一个都对应一个二进制位。

在这里插入图片描述

  1. 数据在存储的时候，如果被选中，那么对应的位的值就为1，否则为0
    在这里插入图片描述

  2. 系统在进行存储的时候会自动将得到的最终的二进制颠倒过来，然后再进行转换成十进制存储
    在这里插入图片描述

查看数据

按照自动转换成数值来查看

在这里插入图片描述

插入数值来代替实际插入数据

在这里插入图片描述

注意：数字插入的前提是对应的二进制位上都有对应的数据项

Set集合的意义：

  1. 规范数据
  2. 节省存储空间
  • Enum：单选框
  • Set：复选框
测试代码 enum、set
-- enum测试
create table my_enum(
gender enum('男','女','保密')
)charset utf8;

insert into my_enum values('男');
insert into my_enum values('女');
insert into my_enum values('男');

-- 将字段按照数值输出
select gender + 0 from my_enum;

insert into my_enum values(3);

-- set 测试
create table my_set(
hobby set('篮球','足球','羽毛球','乒乓球','网球','橄榄球','冰球','高俅')
--           1      1      1         1       1       1       1      1
)charset utf8;

insert into my_set values('篮球,乒乓球,冰球');
--                          10010010
--  存储转换		    01001001	===》 1 + 8 + 64 = 73

insert into my_set values('高俅,足球,篮球,乒乓球,冰球');
--                          11010011
--  存储转换		    11001011	===》 1 + 2 + 8 + 64 + 128 = 203

-- 以数值方式查看集合数据
select hobby + 0 from my_set;

insert into my_set values(255);



列属性

Null 属性

代表字段为空，如果对应的值为YES表示该字段可以为NULL。

注意：

  1. 在设计表的时候，尽量不要让数据为空
  2. Mysql的记录长度为65535个字节，如果一个表中有字段允许为NULL，那么系统就会设计保留一个字节来存储NULL，最终有效存储长度为65534个字节。

Default 默认值

当字段被设计的时候，如果允许默认条件下，用户不进行数据的插入，那么就可以使用事先准备好的数据来填充：通常填充的是NULL

Default关键字的另外一层使用：显示的告知字段使用默认值：在进行数据插入的时候，对字段值直接使用default。

Comment 列描述

是专门用于给开发人员进行维护的一个注释说明。

基本语法：

comment ‘字段描述’;

查看Comment：必须通过查看表创建语句

测试代码
-- 创建数据表
create table my_default(
name varchar(10) NOT NULL, -- 不能为空
age int default 18  -- 表示，如果当前字段在进行数据插入的时候没有提供，那么就使用18
)charset utf8;

insert into my_default(name) values('Tom');

insert into my_default values('Jack',default);


-- 创建表，增加字段描述
create table my_comment(
name varchar(10) not null comment '当前是用户名，不能为空', -- 用户名不能为空
pass varchar(50) not null comment '密码不能为空'
)charset utf8;



-- 查看commen
show create table table名;

primary 主键

主要的键，primary key，在一张表中，有且只有一个字段，里面的值具有唯一性

主键约束

主键一旦增加，那么对对应的字段有数据要求

  1. 当前字段对应的数据不能为空；
  2. 当前字段对应的数据不能有任何重复

主键分类

主键分类采用的是主键所对应的字段的业务意义分类

  • 业务主键：主键所在的字段，具有业务意义（学生ID，课程ID）
  • 逻辑主键：自然增长的整型（应用广泛）
测试代码
-- 随表创建
  -- 方案1：直接在需要当做主键的字段之后，增加primary key属性来确定主键
create table my_pri1(
username varchar(10) primary key
)charset utf8;
  -- 方案2：在所有字段之后增加primary key选项：primary key(字段信息)
create table my_pri2(
username varchar(10),
primary key(username)
)charset utf8;

create table my_pri3(
username varchar(10)
)charset utf8;

-- 表后增加主键
alter table my_pri3 add primary key(username);

-- 查看主键
-- 方案1：查看表结构：
Desc  my_pri3;
-- 方案2：查看表的创建语句
show create table my_pri3;


-- 删除主键
alter table my_pri3 drop primary key;

-- 复合主键
create table my_score(
student_no char(10),
course_no char(10),
score tinyint not null,
primary key(student_no,course_no)
)charset utf8;

insert into my_score values('00000001','course001',100);
insert into my_score values('00000002','course001',90);
insert into my_score values('00000001','course002',95);
insert into my_score values('00000002','course001',98);

increment 自增长

给定某个字段该属性之后，该列的数据在没有提供确定数据的时候，系统会根据之前已经存在的数据进行自动增加后，填充数据。

通常自动增长用于逻辑主键。

一张表最多只能拥有一个自增长。

原理

  1. 在系统中有维护一组数据，用来保存当前使用了自动增长属性的字段，记住当前对应的数据值，再给定一个指定的步长
  2. 当用户进行数据插入的时候，如果没有给定值，系统在原始值上再加上步长变成新的数据
  3. 自动增长的触发：给定属性的字段没有提供值
  4. 自动增长只适用于数值

初始设置

在系统中，有一组变量用来维护自增长的初始值和步长

Show variables like ‘auto_increment%’;

细节问题

  1. 一张表只有一个自增长：自增长会上升到表选项中
  2. 如果数据插入中没有触发自增长（给定了数据），那么自增长不会表现
  3. 自增长修改的时候，值可以较大，但是不能比当前已有的自增长字段的值小
测试代码
-- 创建一个带自动增长的表

create table my_auto(
id int primary key auto_increment,
name varchar(10) not null comment '用户名',
pass varchar(50) not null comment '密码'
)charset utf8;

insert into my_auto values(null,'Tom','123456');

-- 修改auto_increment （修改表结构）
alter table my_auto auto_increment  = 10; -- 修改步长
alter table my_auto auto_offset  = 10; -- 修改初始值

-- 删除自增长
-- 在字段属性之后不再保留auto_increment，当用户修改自增长所在字段时，如果没有看到auto_increment属性，系统会自动清除该自增长
-- 切记不要再次增加primary key
alter table my_auto modify id int;


-- 查看自增长初始变量，自增长一旦触发使用之后，会自动的在表选项中增加一个选项
show variables like 'auto_increment%';

-- 增加自增长
alter table my_auto modify id int auto_increment;

insert into my_auto values(3,'jack','123456');
insert into my_auto values(20,'jack','123456');

-- 自增长修改的时候，值可以较大，但是不能比当前已有的自增长字段的值小
alter table my_auto auto_increment  = 10;



unique [key] 唯一键

用来保证对应的字段中的数据唯一的。

主键也可以用来保证字段数据唯一性，但是一张表只有一个主键。

  1. 唯一键在一张表中可以有多个。
  2. 唯一键允许字段数据为NULL，NULL可以有多个（NULL不参与比较）
  3. 唯一键效果：在不为空的情况下，不允许重复。
测试代码
-- 创建唯一键
create table my_unique1(
id int primary key auto_increment,
username varchar(10) unique
)charset utf8;

-- 	在所有的字段之后使用unique key(字段列表);
create table my_unique2(
id int primary key auto_increment,
username varchar(10),
unique key(username)
)charset utf8;

create table my_unique3(
id int primary key auto_increment,
username varchar(10)
)charset utf8;

-- 	在创建完表之后也可以增加唯一键
alter table my_unique3 add unique key(username);

insert into my_unique1 values(null,default);
insert into my_unique1 values(null,default);
insert into my_unique1 values(null,default);

insert into my_unique1 values(null,'army');
insert into my_unique1 values(null,'army');

-- 查看唯一键，系统自动为它创建一个名字，默认字段名
desc my_unique2;

-- 删除唯一键，Index关键字：索引，唯一键是索引一种（提升查询效率）
alter table my_unique2 drop index username;

-- 修改唯一键 == 先删后增

-- 复合唯一键
-- 唯一键与主键一样可以使用多个字段来共同保证唯一性
-- 一般主键都是单一字段（逻辑主键），而其他需要唯一性的内容都是由唯一键来处理。



高级数据操作

新增数据

insert 数据插入

只要写一次insert指令，但是可以直接插入多条记录

insert into 表名 [(字段列表)] values(值列表), (值列表)…;

-- 多数据插入
insert into my_gbk values('张三'),('李四'),('王五');


create table my_student(
stu_id varchar(10) primary key comment '主键，学生ID',
stu_name varchar(10) not null  comment '学生姓名'
)charset utf8;
主键冲突

在有的表中，使用的是业务主键（字段有业务含义），但是往往在进行数据插入的时候，又不确定数据表中是否已经存在对应的主键。

主键冲突的解决方案：

  1. 主键冲突更新：

    类似插入数据语法，如果插入的过程中主键冲突，那么采用更新方法。

    Insert into 表名 [(字段列表)] values(值列表) on duplicate key update 字段 = 新值;

  2. 主键冲突替换：

    当主键冲突之后，干掉原来的数据，重新插入进去。

    Replace into [(字段列表)] values(值列表);

insert into my_student values
('stu0001','张三'),
('stu0002','张四'),
('stu0003','张五'),
('stu0004','张六');

-- 插入数据失败
insert into my_student values('stu0004','小婷');

类似插入数据语法，如果插入的过程中主键冲突，那么采用更新方法。

Insert into 表名 [(字段列表)] values(值列表) on duplicate key update 字段 = 新值;

-- 冲突更新
insert into my_student values('stu0004','小婷') on duplicate key update stu_name = '小婷';

当主键冲突之后，干掉原来的数据，重新插入进去。

Replace into [(字段列表)] values(值列表);

-- 冲突替换
replace into my_student values('stu0001','夏洛');
蠕虫复制

蠕虫复制：一分为二，成倍的增加。从已有的数据中获取数据，并且将获取到的数据插入到数据表中。

Insert into 表名 [(字段列表)] select */字段列表 from 表;

create table my_simple(
name char(1) not null
)charset utf8;

insert into my_simple values('a'),('b'),('c'),('d');

-- 蠕虫复制
insert into my_simple(name) select name from my_simple;

注意：

  1. 蠕虫复制的确通常是重复数据，没有太大业务意义：可以在短期内快速增加表的数据量，从而可以测试表的压力，还可以通过大量数据来测试表的效率（索引）
  2. 蠕虫复制虽好，但是要注意主键冲突

数据更新

在更新数据的时候，特别要注意：通常一定是跟随条件更新

Update 表名 set 字段名 = 新值 where 判断条件;

如果没有条件，是全表更新数据。但是可以使用limit 来限制更新的数量；

Update 表名 set 字段名 = 新值 [where 判断条件] limit 数量;

改变4个a变成e

Update my_simple set name = ‘e’ where name = ‘a’ limit 4;

在这里插入图片描述

数据删除

  • 删除数据的时候尽量不要全部删除，应该使用where进行 判定；
  • 删除数据的时候可以使用limit来限制要删除的具体数量
  • Delete删除数据的时候无法重置auto_increment

在这里插入图片描述

  • Mysql有一个能够重置表选项中的自增长的语法;
  • Truncate 表名; ==è drop -à create

在这里插入图片描述

诡语
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值