[Python知识库]大厂面试预备篇——《两万字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_id

FROM yitiao

LIMIT 5;

# 查找第6行开始的5行

SELECT yitiao_id

FROM yitiao

LIMIT 6, 5;

# 限定表名的列

SELECT yitiao.yitiao_id

FROM yitiao;

排序查找

排序

# 用yitiao_coding的字母顺序排序

SELECT yitiao_coding FROM yitiao

ORDER BY yitiao_coding;
# 先用价格排序，再用名称排序

SELECT yitiao_id, yitiao_price, yitiao_coding FROM yitiao

ORDER BY yitiao_price, yitiao_coding;

降序

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

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

# 价格降序

SELECT yitiao_id, yitiao_price, yitiao_coding FROM yitiao

ORDER BY yitiao_price DESC;

# 先对价格降序，再用名称排序（无说明默认升序）

SELECT yitiao_id, yitiao_price, yitiao_coding FROM yitiao

ORDER BY yitiao_price DESC, yitiao_coding;

执行顺序

SELECT

FROM

WHERE

GROUP BY

HAVING

ORDER BY

LIMIT

选择查询

WHERE子句操作符

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

选择单个值

# 名字等于fuses

SELECT yitiao_id, yitiao_price, yitiao_coding

FROM yitiao

WHERE yitiao_mane = 'fuses';

# 价格大于10

SELECT yitiao_id, yitiao_price, yitiao_coding

FROM yitiao

WHERE yitiao_price > 10;

#供应商ID不是1003

SELECT yitiao_id, yitiao_price, yitiao_coding

FROM yitiao

WHERE yitiao_id != 1003;

范围值检查

SELECT yitiao_id,yitiao_coding,yitiao_price

FROM yitiao

WHERE yitiao_price BETWEEN 5 AND 10;

空值检查

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

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

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

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

# NULL表示空值，no value

SELECT yitiao_id,yitiao_coding,yitiao_price

FROM yitiao

WHERE yitiao_price IS NULL;

AND & OR

AND表示同时满足所有条件

# id=1003，价格小于等于10

SELECT  yitiao_id, yitiao_coding, yitiao_price

FROM yitiao

WHERE yitiao_id = 1003 AND yitiao_price <= 10;

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

# id是1002或者是价格等于10

SELECT yitiao_id, yitiao_coding, yitiao_price

FROM yitiao

WHERE yitiao_id = 1002 OR yitiao_price = 10;

次序计算

MySql优先处理AND, 后处理OR

# 先满足yitiao_id = 1003 AND yitiao_price = 10

SELECT yitiao_id, yitiao_coding, yitiao_price

FROM yitiao

WHERE yitiao_id = 1002 OR yitiao_id = 1003 AND yitiao_price = 10;

# 要先处理OR，应该加（）

SELECT yitiao_id, yitiao_coding, yitiao_price

FROM yitiao

WHERE (yitiao_id = 1002 OR yitiao_id = 1003) AND yitiao_price = 10;

IN指定条件范围

# 查找1002，1003供应商并用名称排序

SELECT yitiao_id, yitiao_coding, yitiao_price

FROM yitiao

WHERE yitiao_id IN (1002, 1003)

ORDER BY yitiao_coding;

NOT & NOT IN

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

# 查找不是1002，1003供应商并用名称排序

SELECT yitiao_id, yitiao_coding, yitiao_price

FROM yitiao

WHERE yitiao_id NOT IN (1002, 1003)

ORDER BY yitiao_coding;

模糊查询

LIKE子句+通配符 %

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

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

# 查找以jet起头的产品名字（和JET起头不匹配）

SELECT yitiao_coding, yitiao_price

FROM yitiao

WHERE yitiao_coding LIKE 'jet%';
# 查找产品名字中任意位置有care字符

SELECT yitiao_coding, yitiao_price

FROM yitiao

WHERE yitiao_coding LIKE '%care%';

# 查找产品名字中以s开头e结尾的字符，长度不限

SELECT yitiao_coding, yitiao_price

FROM yitiao

WHERE yitiao_coding LIKE 's%e';

正则表达式

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

基本字符匹配

# 查找列yitiao_coding**包含文本1000**的所有行,和LIKE类似

SELECT yitiao_coding, yitiao_price

FROM yitiao

WHERE yitiao_coding **REGEXP '1000'**

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

SELECT yitiao_coding, yitiao_price

FROM yitiao

WHERE yitiao_coding REGEXP '.000'

ORDER BY yitiao_coding;

OR的匹配

# 查找列yitiao_coding包含文本1000，2000的行

SELECT yitiao_coding, yitiao_price

FROM yitiao

WHERE 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_price

FROM yitiao

WHERE yitiao_coding REGEXP '[1|2|3]TON'

ORDER BY yitiao_coding;

匹配范围

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

匹配特殊字符

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

# 匹配带.的特殊字符

SELECT yitiao-name, yitiao_price

FROM yitiao

WHERE 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 yitiao

from test;

使用别名

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

SELECT Contact(vend_name, '(',vend_country,')') **AS** vend_title

FROM vendors

ORDER BY vend_name;

文本字符串处理

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

删除右边空格

SELECT RTim(vend_name), RTrim(vend_country)

FROM vendors

ORDER BY vend_name;

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

SELECT vend_name, Upper(vend_name) AS vend_name_upcase

FROM vendors

ORDER BY vend_name;

日期和时间处理函数

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

查找一个特定日期的订单

SELECT yitiao_id, yitiao_coding

FROM yitiao

WHERE Date(order_date) = '2020-09-02'

ORDER BY yitiao_coding;

查找特定的时间段

SELECT yitiao_id, yitiao_coding

FROM yitiao

WHERE Date(order_date) **BETWEEN** '2021-09-01' **AND** '2021-09-03';

# 查找特定年份和月份

SELECT yitiao_id, yitiao_coding

FROM yitiao

WHERE Year(order_date) = 2021 **AND** Month(order_date) = 9;

算数计算

# 查找订单号是2005的产品数量和价格

SELECT yitiao_id, yitiao_qty, yitiao_price

FROM yitiao

WHERE order_num = 2005;

# 上述 查找后计算总价值（数量*价格）

SELECT yitiao_id, yitiao_qty, yitiao_price, **yitiao_qty\*yitiao_price AS expanded_price**

FROM yitiao

WHERE order_num = 2005;

数值处理函数

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

数据处理

AVG() 平均值

# 求产品平均值

SELECT AVG(yitiao_price) AS avg_price

FROM yitiao;

# AVG(DISTINCT), 相同价格只出现一次，计算平均值

SELECT AVG(DISTINCT yitiao_price) AS avg_price

FROM yitiao;

COUNT() 计数

COUNT(*) AS cust_num, 对所有列进行计数的，但是只返回cust_num里的计数结果

COUNT(*)对所有进行计数，COUNT(column)对除掉NULL的列进行计数

# 求客户数量

SELECT COUNT(*) AS num_cust

FROM customers;

# 求有邮箱的客户数量

SELECT COUNT(cust_email) AS num_cust

FROM customers;

最值

# MAX() 最大值

SELECT MAX(yitiao_price) AS max_price

FROM yitiao;

# MIN() 最小值

SELECT MIN(yitiao_price) AS min_price

FROM yitiao;

SUM() 求和

# 订单中物品为2005的所有数量

SELECT SUM(qty) AS total_items

FROM orderitems

WHERE item_name = 2005;

# 订单中物品为2005的全部金额

SELECT SUM(qty*item_price) AS total_amount

FROM orderitems

WHERE item_name = 2005;

SELECT 可以包含多个聚集函数

SELECT COUNT(*) AS item_num

MIN(yitiao_price) AS min_price

MAX(yitiao_price) AS max_price

AVG(yitiao_price) AS avg_price

FROM yitiao;

分组

创建分组

# 不同供应商包含的产品计数并分组

SELECT yitiao_id, COUNT(*) AS yitiao_num

FROM yitiao

GROUP BY yitiao_id;

过滤分组，

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

# 查找买了2次以上的客户,此处不能用WHERE

SELECT cust_id, COUT(*) AS order_num

FROM orders

GROUP BY cust_id

HAVING COUNT(*) >= 2;

# 价格为10以上，具有2个以上产品的供应商

SELECT yitiao_id, COUNT(*) AS yitiao_num

FROM vendors

WHERE yitiao_price >= 10

GROUP BY yitiao_id

HAVING COUNT(*) >= 2;

语法顺序

SELECT

FROM

WHERE

GROUP BY

HAVING

ORDER BY

LIMIT

子查询

利用子查询过滤

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

# 查找包含物品TNT2的所有订单编号

SELECT order_num

FROM orderitems

WHERE yitiao_id = 'TNT2';

输出结果（2005，2007）

# 查找该订单编号的所有客户ID

SELECT cust_id

FROM orders

WHERE oder_num IN (2005,2007);

输出结果（1001，1004）

# 查找该客户ID的所有客户信息

SELECT cust_info

FROM customers

WHERE cust_id IN (1001, 1004);

输出结果

利用子查询，方式如下：

SELECT cust_info

FROM customers

WHERE cust_id **IN** (

SELECT cust_id

FROM orders

WHERE oder_num **IN**(

SELECT order_num

FROM orderitems

WHERE yitiao_id = 'TNT2'));

联结表

创建联结 JOIN

# 从2个关系表中导出数据

SELECT yitiao_price, yitiao_coding, vend_name

FROM yitiao, vendors

**WHERE yitiao.yitiao_id = vendors.yitiao_id**

ORDER BY vend_name, yitiao_coding;

# INNOR JOIN...ON内部联结-上述的第二种写法

SELECT yitiao_price, yitiao_coding, vend_name

FROM yitiao

**INNER JOIN vendors ON yitiao.yitiao_id = vendors.yitiao_id;**

联结多个表

SELECT yitiao_price, yitiao_coding, vend_name, order_num

FROM yitiao, vendors, orders

WHERE yitiao.yitiao_id = vendors.yitiao_id

AND yitiao.yitiao_id = orders.yitiao_id

AND order_num = 2005； # AND起过滤作用

自联结

# 先找到物品ID是TNT2的供应商，再找到此供应商ID下的其他物品，把1个表别名成2个表，p1输出物品名字和ID，p2用作关联TNT2的语法和结果输出

SELECT p1.yitiao_id, p1.yitiao_coding

FROM 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_price

FROM customers AS c, orders AS o, orderitems AS oi

WHERE c.cust_id = o.cust_id

AND p.yitiao_id = oi.yitiao_id

AND yitiao_id = 'TNT2';

外部联结

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

SELECT customers.cust_id, orders.order_num

FROM customers

LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id;

使用带聚集函数的联结

# 内部联结，表之间相等的行联结

SELECT customers.cust_name, customers.cust_id, COUNT(orders.order_num) AS ord_num

FROM customers INNER JOIN orders

ON customers_cust.id = orders.cust_id

GROUP BY customers_cust.id;

# 外部联结，表之间有不相关联的行联结

SELECT customers.cust_name, customers.cust_id, COUNT(orders.order_num) AS ord_num

FROM customers LEFT OUTER JOIN orders

ON customers_cust.id = orders.cust_id

GROUP 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增加一列addr

Alter 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种语句

# 该语句添加一个主键。意味着索引值必须是唯一的，不能为 NULL

ALTER 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 insect

date DATE NOT NULL, # date collected

origin 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 insect

ADD 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 insect

date DATE NOT NULL, # date collected

origin 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_name

FROM person_tbl GROUP BY last_name, first_name

HAVING repetitions > 1;

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

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

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

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

SELECT DISTINCT last_name, first_name

FROM person_tbl

ORDER BY last_name;

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

SELECT last_name, first_name

FROM person_tbl

GROUP BY (last_name, first_name);

使用表替换去除重复记录

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

CREATE TABLE tmp

SELECT last_name, first_name, sex

FROM 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天

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

关注博主即可阅读全文