大厂面试预备篇——《两万字MySql基础总结》❤️建议收藏

❤️ 一条独家专栏

⭐️ 搞技术，进大厂，聊人生

?《大厂面试突击》——面试10多家中大厂的万字总结

?《技术专家修炼》——高薪必备，企业真实场景

?《leetcode 300题》——每天一道算法题，进大厂必备

?《糊涂算法》——数据结构+算法全面讲解

?《从实战学python》——python的各种应用

?《程序人生》——听一条聊职场，聊人生

?更多资料点这里

天下难事，必作于易；天下大事，必作于细。 —— 老子

前言

哈喽，大家好，我是一条。

前几天有个粉丝来找我，发生了这样一段有趣的对话：

一条哥 ，请教个问题，我昨天要女神微信被拒绝了怎么办？

我不是教你搞技术吗？ 你小子搞对象去了，leetcode题刷了吗？

要不到微信刷不进去题呀，帮帮忙吧

淡定，急什么，我和你说，要先找到女神的需求，用科技赋能爱情，才能加微信，约吃饭，谈恋爱全链路打通。

太高深了，听不懂，你就告诉我怎么做吧

这些互联网黑话等你毕业就懂了，你现在和我说说女神最近有什么烦恼吗

女神怎么可能会有烦恼，要啥有啥

给我好好想，你得动脑子呀

哎，还真有一个，她最近数据库考试考的不太好，不怎么开心

这样，那就好办了，我这有一份《mysql万字秘籍》，你拿回去好好研读，保你下次班级第一，到时借着发学习资料的名义不就加了微信，然后再约着上自习，完事吃个饭。这就叫全链路打通，懂了吗？

妙啊，秘籍呢，快给我吧

看你那猴急的样，给我一键三连，随后发你

没问题，谢谢一条哥！

下面就是2万字秘籍，免费送给大家，老规矩——一键三连

数据库

选择数据库

USE name;

显示可用数据库

SHOW DATABASES;

显示可用表

SHOW TABLES;

获取表的一个列

SHOW COLUMNS FROM yitiao_coding;

数据类型

数值类型

• 整数类型：tinyint smallint mediumint int bigint
• 浮点类型：float double
• 定点小数类型：decimal

日期类型

year time date datetime timestamp

字符串类型

• 文本字符串：char varchar tinytext text mediumtext longtext enum set
• 二进制字符串：bit binary tinyblob blog mediumblob longblob

表

创建表

CREATE TABLE SalesSummary (yitiaouct_name VARCHAR(50) NOT NULL ,total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00 ,avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00 ,total_units_sold INT UNSIGNED NOT NULL DEFAULT 0 );# 通常设置：ENGINE=InnoDB default CHARSET=utf8；

插入值

INSERT INTO SalesSummary(yitiaouct_name, total_sales, avg_unit_price, total_units_sold)VALUES ("cucumber", 100.25, 90, 2);

查看表

利用 SHOW TABLES 命令显示表时，临时表不会出现在结果列表中。如果退出 MySQL 会话，就会执行 SELECT 命令，那么数据库中将没有任何数据，甚至临时表也不存在了。

SELECT * FROM yitiao_coding;

删除表

默认情况下，当与数据库的连接终止时，临时表就不再存在。不过如果想在数据库处于连接时就删除它们，可以用 DROP TABLE 命令来删除。

DROP TABLE SalesSummary;# 删除程度可从强到弱如下排列：drop table tb;# drop 是直接将表格删除，无法找回。例如删除 user 表：drop table user;truncate （table） tb;# truncate 是删除表中所有数据，但不能与where一起使用；delete from tb (where);# delete 也是删除表中数据，但可以与where连用，删除特定行；# 删除表中所有数据delete from user;# 删除指定行delete from user where username ="Tom";

复制表

可以采用如下步骤来处理这种情况。

使用 SHOW CREATE TABLE 或 CREATE TABLE 语句指定源表的结构、索引以及所有的内容。

调整语句，将表名改为克隆表的名称，执行语句。这样就对表进行了克隆。另外，如果想要克隆表的全部内容，也可以使用 INSERT INTO … SELECT 语句。

• 步骤1：获取表的完整结构

• 步骤2：重新命名该表，创建另一个表

• 步骤3：执行完步骤2后，就在数据库中创建了一个克隆表。如果想从旧表中复制数据，可以使用 INSERT INTO… SELECT 语句。

查找数据

查找列

# 从 yitiuao_coding 表中获取 yitiao 这一列SELECT yitiuaocoding FROM yitiuao_coding;

查找多个列

SELECT yitiao_coding, yitiao_it, yitiao_name FROM yitiao;

查找所有列

SELECT * FROM yitiao;

查找不同的行

# 重复的行只显示一次SELECT DISTINCT yitiao_id FROM yitiao;

限定查找

注意，行0开始数，查找5行是:0,1,2,3,4行，第6行同理从行0开始数

# 只查找5行SELECT yitiao_idFROM yitiaoLIMIT 5;# 查找第6行开始的5行SELECT yitiao_idFROM yitiaoLIMIT 6, 5;# 限定表名的列SELECT yitiao.yitiao_idFROM yitiao;

排序查找

排序

# 用yitiao_coding的字母顺序排序SELECT yitiao_coding FROM yitiaoORDER BY yitiao_coding;# 先用价格排序，再用名称排序SELECT yitiao_id, yitiao_price, yitiao_coding FROM yitiaoORDER BY yitiao_price, yitiao_coding;

降序

无说明默认升序，降序需要用DESC(descrease)加以说明

如果要在多个列上进行降序，需要对每个列都进行DESC说明

# 价格降序SELECT yitiao_id, yitiao_price, yitiao_coding FROM yitiaoORDER BY yitiao_price DESC;# 先对价格降序，再用名称排序（无说明默认升序）SELECT yitiao_id, yitiao_price, yitiao_coding FROM yitiaoORDER BY yitiao_price DESC, yitiao_coding;

执行顺序

SELECT

FROM

WHERE

GROUP BY

HAVING

ORDER BY

LIMIT

选择查询

WHERE子句操作符

= 等于，！= 不等于，< 小于， > 大于， <= 小于等于， >= 大于等于， BETWEEN AND 在指定的两个值之间

选择单个值

# 名字等于fusesSELECT yitiao_id, yitiao_price, yitiao_codingFROM yitiaoWHERE yitiao_mane = "fuses";# 价格大于10SELECT yitiao_id, yitiao_price, yitiao_codingFROM yitiaoWHERE yitiao_price > 10;#供应商ID不是1003SELECT yitiao_id, yitiao_price, yitiao_codingFROM yitiaoWHERE yitiao_id != 1003;

范围值检查

SELECT yitiao_id,yitiao_coding,yitiao_priceFROM yitiaoWHERE yitiao_price BETWEEN 5 AND 10;

空值检查

IS NULL：如果列值为 NULL，则该运算符返回 true。

IS NOT NULL：如果列值不为NULL，则该运算符返回 true。

该运算符用于两个值的对比，当两个值都为 NULL 时（这一点与 = 运算符不同），返回 true。

包含 NULL 的条件都是比较特殊的。不能在列中使用 = NULL 或 ! = NULL 来寻找 NULL 值。这样的比对通常都是失败的，因为不可能得知这样的比对是否为真。

# NULL表示空值，no valueSELECT yitiao_id,yitiao_coding,yitiao_priceFROM yitiaoWHERE yitiao_price IS NULL;

AND & OR

AND表示同时满足所有条件

# id=1003，价格小于等于10SELECT  yitiao_id, yitiao_coding, yitiao_priceFROM yitiaoWHERE yitiao_id = 1003 AND yitiao_price <= 10;

OR表示满足所有的单个条件

# id是1002或者是价格等于10SELECT yitiao_id, yitiao_coding, yitiao_priceFROM yitiaoWHERE yitiao_id = 1002 OR yitiao_price = 10;

次序计算

MySql优先处理AND, 后处理OR

# 先满足yitiao_id = 1003 AND yitiao_price = 10SELECT yitiao_id, yitiao_coding, yitiao_priceFROM yitiaoWHERE yitiao_id = 1002 OR yitiao_id = 1003 AND yitiao_price = 10;# 要先处理OR，应该加（）SELECT yitiao_id, yitiao_coding, yitiao_priceFROM yitiaoWHERE (yitiao_id = 1002 OR yitiao_id = 1003) AND yitiao_price = 10;

IN指定条件范围

# 查找1002，1003供应商并用名称排序SELECT yitiao_id, yitiao_coding, yitiao_priceFROM yitiaoWHERE yitiao_id IN (1002, 1003)ORDER BY yitiao_coding;

NOT & NOT IN

NOT表在WHERE子句中用来否定后跟的条件，NOT IN可以用来取反

# 查找不是1002，1003供应商并用名称排序SELECT yitiao_id, yitiao_coding, yitiao_priceFROM yitiaoWHERE yitiao_id NOT IN (1002, 1003)ORDER BY yitiao_coding;

模糊查询

LIKE子句+通配符 %

%表示任何字符串出现任意次数，区分大小写

_表示任何字符串出现单次，指一个字符，其他功能和%一样

# 查找以jet起头的产品名字（和JET起头不匹配）SELECT yitiao_coding, yitiao_priceFROM yitiaoWHERE yitiao_coding LIKE "jet%";# 查找产品名字中任意位置有care字符SELECT yitiao_coding, yitiao_priceFROM yitiaoWHERE yitiao_coding LIKE "%care%";# 查找产品名字中以s开头e结尾的字符，长度不限SELECT yitiao_coding, yitiao_priceFROM yitiaoWHERE yitiao_coding LIKE "s%e";

正则表达式

正则表达式是用来匹配文本的特殊的串（字符集合）

基本字符匹配

# 查找列yitiao_coding**包含文本1000**的所有行,和LIKE类似SELECT yitiao_coding, yitiao_priceFROM yitiaoWHERE yitiao_coding **REGEXP "1000"**ORDER BY yitiao_coding;# . 是正则表达式中一个特殊的字符，表示匹配任意一个字符，查找列yitiao_coding包含文本.000的所有行（比如1000，2000，3000），和LIKE不同SELECT yitiao_coding, yitiao_priceFROM yitiaoWHERE yitiao_coding REGEXP ".000"ORDER BY yitiao_coding;

OR的匹配

# 查找列yitiao_coding包含文本1000，2000的行SELECT yitiao_coding, yitiao_priceFROM yitiaoWHERE yitiao_coding REGEXP "1000|2000"ORDER BY yitiao_coding;

匹配几个字符之一

# 匹配1TON, 2TON, 3TON的组合方式, [123]TON = [1|2|3]TON**，如果输1|2|3 TON是指1，2，3 TON，不是1TON, 2TON, 3TON.SELECT yitiao-name, yitiao_priceFROM yitiaoWHERE yitiao_coding REGEXP "[1|2|3]TON"ORDER BY yitiao_coding;

匹配范围

[1-3]是一个范围，[a-z]匹配任意字母字符

匹配特殊字符

用/为前导来匹配，如/.

# 匹配带.的特殊字符SELECT yitiao-name, yitiao_priceFROM yitiaoWHERE yitiao_coding REGEXP "//."ORDER BY yitiao_coding;

空白元字符

/f 换页， /n 换行， /r 回车，/t 制表，/v 纵向制表

重复元字符

0个或多个字符，+ 1个或多个字符，？0个或1个字符，{n} 指定n个字符，{n,} 指定不少于 n个字符，{n,m}指定n-m个字符

定位元字符

^xx 以xx开头，xx$ 以xx结束，[[:<:f]] 词的开始，[[:>:]] 词的结尾

#寻找以 "st" 开头的名称，查询如下：mysql> SELECT name FROM person_tbl WHERE name REGEXP "^st";#寻找以 "ok" 结尾的名称，查询如下：mysql> SELECT name FROM person_tbl WHERE name REGEXP "ok$";

文本数值时间计算处理

Concat() 拼接

将值联结到一起构成单个值

# 字符串拼接select concat(str1,str2...)# 把两列合并为一列select concat(vender, country) as yitiaofrom test;

使用别名

AS赋予联结后的单个词一个别名

SELECT Contact(vend_name, "(",vend_country,")") **AS** vend_titleFROM vendorsORDER BY vend_name;

文本字符串处理

Left() 返回左边的字符串， Right() 返回右边的字符串，Length() 返回字符串的长度，Locate() 找出串的一个子串，SubString() 返回子串的字符，Soundex() 返回字符串的SOUNDEX值(类似发音的字符串)，RTrim删除右边多余的空格，LTrim删除左边多余的空格， Upper() 转为大写，Lower() 转为小写，

删除右边空格

SELECT RTim(vend_name), RTrim(vend_country)FROM vendorsORDER BY vend_name;

Upper()转为大写，Lower()转为小写

SELECT vend_name, Upper(vend_name) AS vend_name_upcaseFROM vendorsORDER BY vend_name;

日期和时间处理函数

Year() 返回一个时间的年份，Month() 返回一个时间的月份，Date() 返回一个时间的日期，Day() 返回一个时间的天数，Hour() 返回一个时间的小时，Minute() 返回一个时间的分钟，Second() 返回一个时间的秒钟，Now() 返回当前日期和时间，Time() 返回一个日期的时间，AddDate() 增加一个日期，AddTime() 增加一个时间

查找一个特定日期的订单

SELECT yitiao_id, yitiao_codingFROM yitiaoWHERE Date(order_date) = "2020-09-02"ORDER BY yitiao_coding;

查找特定的时间段

SELECT yitiao_id, yitiao_codingFROM yitiaoWHERE Date(order_date) **BETWEEN** "2021-09-01" **AND** "2021-09-03";# 查找特定年份和月份SELECT yitiao_id, yitiao_codingFROM yitiaoWHERE Year(order_date) = 2021 **AND** Month(order_date) = 9;

算数计算

# 查找订单号是2005的产品数量和价格SELECT yitiao_id, yitiao_qty, yitiao_priceFROM yitiaoWHERE order_num = 2005;# 上述 查找后计算总价值（数量*价格）SELECT yitiao_id, yitiao_qty, yitiao_price, **yitiao_qty/*yitiao_price AS expanded_price**FROM yitiaoWHERE order_num = 2005;

数值处理函数

Abs() 返回一个数的绝对值，Sqrt() 返回一个数的平方根， Rand() 返回一个随机数，Pi() 返回圆周率Exp()，返回一个数的指数值，Mod() 返回除操作的余数，Cos() 返回一个角度的余弦值 Sin() 返回一个角度的正弦值，Tan() 返回一个角度的正切值

数据处理

AVG() 平均值

# 求产品平均值SELECT AVG(yitiao_price) AS avg_priceFROM yitiao;# AVG(DISTINCT), 相同价格只出现一次，计算平均值SELECT AVG(DISTINCT yitiao_price) AS avg_priceFROM yitiao;

COUNT() 计数

COUNT(*) AS cust_num, 对所有列进行计数的，但是只返回cust_num里的计数结果COUNT(*)对所有进行计数，COUNT(column)对除掉NULL的列进行计数# 求客户数量SELECT COUNT(*) AS num_custFROM customers;# 求有邮箱的客户数量SELECT COUNT(cust_email) AS num_custFROM customers;

最值

# MAX() 最大值SELECT MAX(yitiao_price) AS max_priceFROM yitiao;# MIN() 最小值SELECT MIN(yitiao_price) AS min_priceFROM yitiao;

SUM() 求和

# 订单中物品为2005的所有数量SELECT SUM(qty) AS total_itemsFROM orderitemsWHERE item_name = 2005;# 订单中物品为2005的全部金额SELECT SUM(qty*item_price) AS total_amountFROM orderitemsWHERE item_name = 2005;

SELECT 可以包含多个聚集函数

SELECT COUNT(*) AS item_numMIN(yitiao_price) AS min_priceMAX(yitiao_price) AS max_priceAVG(yitiao_price) AS avg_priceFROM yitiao;

分组

创建分组

# 不同供应商包含的产品计数并分组SELECT yitiao_id, COUNT(*) AS yitiao_numFROM yitiaoGROUP BY yitiao_id;

过滤分组，

WHERE针对特定值（每个值，原值），HAVING针对分组过滤后的值**

# 查找买了2次以上的客户,此处不能用WHERESELECT cust_id, COUT(*) AS order_numFROM ordersGROUP BY cust_idHAVING COUNT(*) >= 2;# 价格为10以上，具有2个以上产品的供应商SELECT yitiao_id, COUNT(*) AS yitiao_numFROM vendorsWHERE yitiao_price >= 10GROUP BY yitiao_idHAVING COUNT(*) >= 2;

语法顺序

SELECT

FROM

WHERE

GROUP BY

HAVING

ORDER BY

LIMIT

子查询

利用子查询过滤

在几个关系表中，检索TNT2物品的客户信息，但是没有直接一个表体现这个信息，一步一步过滤，方式如下：

# 查找包含物品TNT2的所有订单编号SELECT order_numFROM orderitemsWHERE yitiao_id = "TNT2";输出结果（2005，2007）# 查找该订单编号的所有客户IDSELECT cust_idFROM ordersWHERE oder_num IN (2005,2007);输出结果（1001，1004）# 查找该客户ID的所有客户信息SELECT cust_infoFROM customersWHERE cust_id IN (1001, 1004);输出结果利用子查询，方式如下：SELECT cust_infoFROM customersWHERE cust_id **IN** (SELECT cust_idFROM ordersWHERE oder_num **IN**(SELECT order_numFROM orderitemsWHERE yitiao_id = "TNT2"));

联结表

创建联结 JOIN

# 从2个关系表中导出数据SELECT yitiao_price, yitiao_coding, vend_nameFROM yitiao, vendors**WHERE yitiao.yitiao_id = vendors.yitiao_id**ORDER BY vend_name, yitiao_coding;# INNOR JOIN...ON内部联结-上述的第二种写法SELECT yitiao_price, yitiao_coding, vend_nameFROM yitiao**INNER JOIN vendors ON yitiao.yitiao_id = vendors.yitiao_id;**

联结多个表

SELECT yitiao_price, yitiao_coding, vend_name, order_numFROM yitiao, vendors, ordersWHERE yitiao.yitiao_id = vendors.yitiao_idAND yitiao.yitiao_id = orders.yitiao_idAND order_num = 2005； # AND起过滤作用

自联结

# 先找到物品ID是TNT2的供应商，再找到此供应商ID下的其他物品，把1个表别名成2个表，p1输出物品名字和ID，p2用作关联TNT2的语法和结果输出SELECT p1.yitiao_id, p1.yitiao_codingFROM yitiao AS p1, yitiao AS p2**WHERE p1.yitiao_id = p2.yitiao_id****AND p2.yitiao_id = "TNT2";**

自然联结

关系表中有一样的列，通过表别名和筛选，使每个列只返回一次# *通配符只对c表使用，其他表的重复列没有被查找出来SELECT c*, o.order_item, o.order_date, oi.yitiao_id, oi.yitiao_qty, oi.yitiao_priceFROM customers AS c, orders AS o, orderitems AS oiWHERE c.cust_id = o.cust_idAND p.yitiao_id = oi.yitiao_idAND yitiao_id = "TNT2";

外部联结

# 为了查找所有客户的下单数量，包括没有订单的客户, LEFT OUTER JOIN...ON**表示从左边的表(customers)中选择所有行SELECT customers.cust_id, orders.order_numFROM customersLEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id;

使用带聚集函数的联结

# 内部联结，表之间相等的行联结SELECT customers.cust_name, customers.cust_id, COUNT(orders.order_num) AS ord_numFROM customers INNER JOIN ordersON customers_cust.id = orders.cust_idGROUP BY customers_cust.id;# 外部联结，表之间有不相关联的行联结SELECT customers.cust_name, customers.cust_id, COUNT(orders.order_num) AS ord_numFROM customers LEFT OUTER JOIN ordersON customers_cust.id = orders.cust_idGROUP BY customers_cust.id;

更新数据

更新查询Update

UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]# 把李四的地址改为上海（默认Beijing)Update employee set city = "Shanghai" where id = 2;

更改表结构

# 在表employee增加一列addrAlter table employee **add** colunm addr varchar(40);

更新表名

Rename Table 表名 to 新表名；

Alter选择

删除、添加列或对其重新定位

# 从表中删除 i这一列ALTER TABLE testalter_tbl DROP i; # 如果表中只有一列，则 DROP 子句不起作用# 下面我们再把 i 这一列恢复到 testalter_tbl 中，使用 ADD 并指定列定义：ALTER TABLE testalter_tbl ADD i INT;# 要想把列放到一个特定位置，可以使用两种方法，第一种方法是使用 FIRST，让指定列成为第一列；第二种则采用 # AFTER 后跟给定列名的方式，指示新列应该放到给定列名的后面。ALTER TABLE testalter_tbl DROP i; ALTER TABLE testalter_tbl ADD i INT FIRST;ALTER TABLE testalter_tbl DROP i; ALTER TABLE testalter_tbl ADD i INT AFTER c;# 标识符 FIRST 和 AFTER 只能和 ADD 子句一起使用。这也意味着，如果要重新定位一列，就必须先用 DROP # 删除它，然后再用 ADD 将它添加到新的位置。

更新列名和数据类型

更改数据类型，把列 c 从 CHAR(1) 变为 CHAR(10)：ALTER TABLE testalter_tbl MODIFY c CHAR(10);# CHANGE 的语法稍有不同。必须把所要改变的列名放到 CHANGE 关键字的后面然后指定新的列定义ALTER TABLE testalter_tbl CHANGE 原列名 新列名 列定义;# 如果想利用 CHANGE 将 j 从 BIGINT 转为 INT，并且不改变列名，则语句如下：ALTER TABLE testalter_tbl CHANGE j j INT;

ALTER TABLE 对 Null 及默认值属性的作用

在利用 MODIFY 或 CHANGE 修改列时，还可以指定该列是否能有 NULL 值，以及它的默认值。如果我们不这样处理，MySQL 会自动为这些属性指定相关值。

# NOT NULL 列默认值为100：MODIFY j BIGINT NOT NULL DEFAULT 100;# 使用 ALTER 命令可以改变任何列的默认值ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;# 使用 DROP 子句与 ALTER 命令，可以去除任何列中的默认限制ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;

改变表类型

结合使用 TYPE 子句与 ALTER 命令，可以使用表类型

ALTER TABLE testalter_tbl TYPE = MYISAM;

对表进行重命名

使用 ALTER TABLE 语句的 RENAME 选项可以对表进行重命名

ALTER TABLE testalter_tbl RENAME TO alter_tbl;

Index索引

简单而唯一的索引

可以为表创建唯一索引，唯一索引要求任意两行的索引值不能相同

CREATE UNIQUE INDEX index_name ON table_name ( column1, column2,...);# 可以使用一或多个列来创建索引CREATE UNIQUE INDEX AUTHOR_INDEX ON tutorials_tbl (tutorial_author)# 降序在列中索引数值，可以在列名后添加保留字 DESC(Descending)CREATE UNIQUE INDEX AUTHOR_INDEX ON tutorials_tbl (tutorial_author DESC)

添加与删除 INDEX 的ALTER 命令

为表添加索引，可以采用4种语句

# 该语句添加一个主键。意味着索引值必须是唯一的，不能为 NULLALTER TABLE tbl_name ADD PRIMARY KEY (column_list)# 该语句为必须唯一的值（除了 NULL 值之外，NULL 值可以多次出现）ALTER TABLE tbl_name ADD UNIQUE index_name (column_list)# 语句为可能多次出现的值创建一般索引ALTER TABLE tbl_name ADD INDEX index_name (column_list)# 语句创建专用于文本搜索的 FULLTEXT 索引ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)

为现有表添加索引

ALTER TABLE testalter_tbl ADD INDEX (c);# 可以使用 DROP 子句以及 ALTER 命令删除索引ALTER TABLE testalter_tbl DROP INDEX (c);

利用 ALTER 命令来添加与删除主键

添加主键也采用类似方式，但要保证主键一定在列上，是 NOT NULL

# 在现有表中添加主键，先使列为 NOT NULL，然后再将其作为主键ALTER TABLE testalter_tbl MODIFY i INT NOT NULL; ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);# 删除一个主键ALTER TABLE testalter_tbl DROP PRIMARY KEY;

序列

使用 AUTO_INCREMENT 列

# 先创建一个表，然后插入一些行，不需要提供记录ID，因为这是由 MySQL 自动增加的CREATE TABLE insect (id INT UNSIGNED NOT NULL AUTO_INCREMENT,PRIMARY KEY (id),name VARCHAR(30) NOT NULL, # type of insectdate DATE NOT NULL, # date collectedorigin VARCHAR(30) NOT NULL # where collected );# 插入值INSERT INTO insect (id,name,date,origin)VALUES (NULL,"housefly","2001-09-10","kitchen"),(NULL,"millipede","2001-09-10","driveway"),(NULL,"grasshopper","2001-09-10","front yard");

对已有序列进行重新编号

如果一定要对 AUTO_INCREMENT 列进行重新排序，那么正确的方式是将该列从表中删除，然后再添加它。下面这个范例中就用了这个技巧，在 insect 表中对 id 值重新排序。

ALTER TABLE insect DROP id;ALTER TABLE insectADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,ADD PRIMARY KEY (id);

以特定值作为序列初始值

MySQL 默认以 1 作为序列初始值，但你也可以在创建表时指定其他的数字，以 100 作为序列初始值

CREATE TABLE insect (id INT UNSIGNED NOT NULL AUTO_INCREMENT = 100,PRIMARY KEY (id),name VARCHAR(30) NOT NULL, # type of insectdate DATE NOT NULL, # date collectedorigin VARCHAR(30) NOT NULL # where collected );

重复处理

防止表中出现重复记录

可以在表中正确的字段内使用 PRIMARY KEY 或 UNIQUE 索引来终止重复记录。比如下面这张表，由于没有这样的索引或主键，因此 first_name与last_name 就被重复记录了下来。

CREATE TABLE person_tbl (first_name CHAR(20),last_name CHAR(20),sex CHAR(10) );

为了防止表中出现同样姓名的值，为其添加一个 PRIMARY KEY。同时要注意将索引列声明为 NOT NULL，这是因为 PRIMARY KEY 不允许出现空值

CREATE TABLE person_tbl (first_name CHAR(20) NOT NULL,last_name CHAR(20) NOT NULL,sex CHAR(10),PRIMARY KEY (last_name, first_name) );

不要使用 INSERT ，使用 INSERT IGNORE。如果该记录与现存的某个记录重复，IGNORE 关键字就会让 MySQL 默默地将其摒弃，不会产生任何错误。

# 下面这个范例不会产生任何错误，不会插入会产生重复的记录。INSERT IGNORE INTO person_tbl (last_name, first_name) VALUES ( "Jay", "Thomas");# 使用 **REPLACE** 而不是 INSERT。如果是一个重复记录，新的记录将会替换旧有记录。REPLACE INTO person_tbl (last_name, first_name) VALUES ( "Ajay", "Kumar");

强制唯一性的另一种办法是为表添加 UNIQUE 索引而不是主键。

CREATE TABLE person_tbl (first_name CHAR(20) NOT NULL,last_name CHAR(20) NOT NULL,sex CHAR(10)UNIQUE (last_name, first_name) );

确认重复记录，并计算重复记录数

# 下面是计算表中姓名记录重复的查询：SELECT COUNT(*) as repetitions, last_name, first_nameFROM person_tbl GROUP BY last_name, first_nameHAVING repetitions > 1;

该查询返回表 person_tbl 中所有的重复记录。一般来说，要想确认重复记录，需要采取以下步骤：

• 确定可能产生重复记录的列。
• 在列选择列表中显示所有列，利用 COUNT(*) 。
• 利用 GROUP BY 子句列出列。
• 加入 HAVING 子句排除唯一值。需要让组计数大于1。

从查询结果中消除重复记录

使用DISTINCT(独的) 和 SELECT 语句来查找表中的重复记录。

SELECT DISTINCT last_name, first_nameFROM person_tblORDER BY last_name;

另一种办法是添加 GROUP BY 子句，命名选择的列。消除重复记录并只选择指定列中的唯一值组合。

SELECT last_name, first_nameFROM person_tblGROUP BY (last_name, first_name);

使用表替换去除重复记录

下面这种技巧也可以消除表中存在的所有重复记录。

CREATE TABLE tmpSELECT last_name, first_name, sexFROM person_tbl;GROUP BY (last_name, first_name);DROP TABLE person_tbl;ALTER TABLE tmp RENAME TO person_tbl;

为表加入 INDEX 或 PRIMARY KEY 。即使该表已经存在，你也可以利用这种技巧消除重复记录，这种做法将来也依然保险。

ALTER IGNORE TABLE person_tbl ADD PRIMARY KEY (last_name, first_name);

⭐今天是坚持刷题更文的第49/100天

⭐各位的点赞、关注、收藏、评论、订阅就是一条创作的最大动力