mysql 降序_MySQL基本语句(全)

18年整理了一半,因为文章无法编辑,所以开了新的更全部——2019-12-12,木言

——————————————————————————————————————

一、基本认识

命令在用;或者/g结束,换句话说,仅按ENTER不执行命令

输入help或者h获得帮助

输入quit或exit退出

MySql不区分大小写,但是很多人愿意对重要语句用大写,用表名等用小写,便于阅读

在 MySQL 中,事务通常以 BEGIN WORK 语句开始,以 COMMIT 或 ROLLBACK(只取其一) 语句结束。在开始与结束声明之间的 SQL 命令就构成了事务的主体。

COMMIT 与 ROLLBACK

MySQL事务主要用到两个关键字 COMMITROLLBACK

成功完成一个事务后,就会执行 COMMIT 命令,从而使施加于所涉及的表上的改变生效。如果事务失败,就会执行 ROLLBACK 命令,将事务中所引用的每一个表都回撤到之前的状态。

通过设定会话变量 AUTOCOMMIT 可以控制事务行为。如果 AUTOCOMMIT 被设为1(默认值),则每一个 SQL 语句(无论是否在事务中)都会被认为是一个完成的事务,则默认当它结束时予以提交。当 AUTOCOMMIT 被设为0(通过命令 SET AUTOCOMMIT=0)时,后续一系列语句就像是一个事务,直到 COMMIT 语句执行为止,不再提交任何行为。

有很多种支持事务表可供选择,但其中最常见的是 InnoDB,TYPE = InnoDB

create table tcount_tbl -> ( -> tutorial_author varchar(40) NOT NULL, -> tutorial_count INT -> ) TYPE=InnoDB;

二、数据库

c9daf53108fa73d286f4e581a8096745.png

1. 选择数据库

USE xxx;

2. 调用数据库列表和数据

# 显示可用数据库

SHOW DATABASES;

# 显示可用表

SHOW TABLES;

# 获取customers表的一个列

SHOW COLUMNS FROM customers;

3. 数据类型

1254b689cf5e2221ab83fae98f722994.png

三、检索数据

1. 检索列

# 从products表中获取prod_name这一列

SELECT prod_name

FROM products;

# 检索多个列

SELECT prod_name, prod_id, prod_price

FROM products;

# 检索所有列

SELECT * FROM products;

2. 检索不同的行

# 重复的行只显示一次

SELECT DISTINCT vend_id

FROM products;

3. 限定检索

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

# 只检索5行

SELECT prod_id

FROM products

LIMIT 5;

# 检索第6行开始的5行

SELECT prod_id

FROM products

LIMIT 6, 5;

# 限定表名的列

SELECT products.prod_id

FROM products;

四、排序检索数据

1. 排序

# 用prod_name的字母顺序排序

SELECT prod_name FROM products

ORDER BY prod_name;

# 先用价格排序,再用名称排序

SELECT prod_id, prod_price, prod_name FROM products

ORDER BY prod_price, prod_name;

2. 降序

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

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

# 价格降序

SELECT prod_id, prod_price, prod_name FROM products

ORDER BY prod_price DESC;

# 先对价格降序,再用产品名称排序(无说明默认升序)

SELECT prod_id, prod_price, prod_name FROM products

ORDER BY prod_price DESC, prod_name;

3. 语法顺序

SELECT

FROM

WHERE

GROUP BY

HAVING

ORDER BY

LIMIT

五、过滤数据-使用WHERE子句

1. WHERE子句操作符

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

2. 检查单个值

# 名字等于fuses

SELECT prod_id, prod_price, prod_name

FROM products

WHERE prod_mane = 'fuses';

# 价格大于10

SELECT prod_id, prod_price, prod_name

FROM products

WHERE prod_price > 10;

#供应商ID不是1003

SELECT vend_id, prod_price, prod_name

FROM products

WHERE vend_id != 1003;

# 范围值检查

SELECT vend_id,prod_name,prod_price

FROM products

WHERE prod_price BETWEEN 5 AND 10;

3. 空值检查

# NULL表示空值,no value

SELECT vend_id,prod_name,prod_price

FROM products

WHERE prod_price IS NULL;

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

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

<=>:该运算符用于两个值的对比,当两个值都为 NULL 时(这一点与 = 运算符不同),返回 true。

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

六、 过滤数据-AND&OR

1. AND表示同时满足所有条件

# 供应商1003,价格小于等于10的产品

SELECT vend_id, prod_name, prod_price

FROM prodcuts

WHERE vend_id = 1003 AND prod_price <= 10;

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

# 是1002供应商,或者是价格等于10

SELECT vend_id, prod_name, prod_price

FROM products

WHERE vend_id = 1002 OR prod_price = 10;

3. 次序计算

# MySql优先处理AND, 后处理OR,先满足vend_id = 1003 AND prod_price = 10

SELECT vend_id, prod_name, prod_price

FROM products

WHERE vend_id = 1002 OR vend_id = 1003 AND prod_price = 10;

# 要先处理OR,应该加()

SELECT vend_id, prod_name, prod_price

FROM products

WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price = 10;

4. IN指定条件范围

# 检索1002,1003供应商并用名称排序

SELECT vend_id, prod_name, prod_price

FROM products

WHERE vend_id IN (1002, 1003)

ORDER BY prod_name;

5. NOT表在WHERE子句中用来否定后跟的条件

NOT IN可以用来取反

# 检索不是1002,1003供应商并用名称排序

SELECT vend_id, prod_name, prod_price

FROM products

WHERE vend_id NOT IN (1002, 1003)

ORDER BY prod_name;

七、 数据过滤-LIKE子句+通配符 %, _

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

# 检索以jet起头的产品名字(和JET起头不匹配)

SELECT prod_name, prod_price

FROM products

WHERE prod_name LIKE 'jet%';

# 检索产品名字中任意位置有care字符

SELECT prod_name, prod_price

FROM products

WHERE prod_name LIKE '%care%';

# 检索产品名字中以s开头e结尾的字符,长度不限

SELECT prod_name, prod_price

FROM products

WHERE prod_name LIKE 's%e';

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

八、正则表达式进行搜索REGEXP

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

81e1a40c86814a7bb39b6dc1087588c4.png

1. 基本字符匹配

# 检索列prod_name包含文本1000的所有行,和LIKE类似

SELECT prod_name, prod_price

FROM products

WHERE prod_name REGEXP '1000'

ORDER BY prod_name;

# .是正则表达式中一个特殊的字符,表示匹配任意一个字符,检索列prod_name包含文本.000的所有行(比如1000,2000,3000),和LIKE不同

SELECT prod_name, prod_price

FROM products

WHERE prod_name REGEXP '.000'

ORDER BY prod_name;

2. OR的匹配, |就是正则表达式中的OR

# 检索列prod_name包含文本1000,2000的行

SELECT prod_name, prod_price

FROM products

WHERE prod_name REGEXP '1000|2000'

ORDER BY prod_name;

3. 匹配几个字符之一

# 匹配1TON, 2TON, 3TON的组合方式, [123]TON = [1|2|3]TON,如果输1|2|3 TON是指1,2,3 TON,不是1TON, 2TON, 3TON.

SELECT prod-name, prod_price

FROM products

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

ORDER BY prod_name;

4. 匹配范围

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

5. 匹配特殊字符,用为前导来匹配,如., _

# 匹配带.的特殊字符

SELECT prod-name, prod_price

FROM products

WHERE prod_name REGEXP '.'

ORDER BY prod_name;

6. 空白元字符

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

7. 重复元字符

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

8. 定位元字符

^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$';

九、文本数值时间计算处理

1. Concat() 拼接,将值联结到一起构成单个值

字符串拼接

select concat(str1,str2...)

把两列合并为一列

select concat(vender, country) as vend

from test;

2. 使用别名

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

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

FROM vendors

ORDER BY vend_name;

3. 文本字符串处理

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;

4. 日期和时间处理函数

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

# 检索一个特定日期的订单

SELECT prod_id, prod_name

FROM products

WHERE Date(order_date) = '2018-12-21'

ORDER BY prod_name;

# 检索特定的时间段

SELECT prod_id, prod_name

FROM products

WHERE Date(order_date) BETWEEN '2017-12-01' AND '2018-12-21';

# 检索特定年份和月份

SELECT prod_id, prod_name

FROM products

WHERE Year(order_date) = 2018 AND Month(order_date) = 9;

5. 算数计算 + - * /

# 检索订单号是2005的产品数量和价格

SELECT prod_id, prod_qty, prod_price

FROM products

WHERE order_num = 2005;

# 上述 检索后计算总价值(数量*价格)

SELECT prod_id, prod_qty, prod_price, prod_qty*prod_price AS expanded_price

FROM products

WHERE order_num = 2005;

6. 数值处理函数

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

十、 数据处理

1. AVG() 平均值

# 求产品平均值

SELECT AVG(prod_price) AS avg_price

FROM products;

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

SELECT AVG(DISTINCT prod_price) AS avg_price

FROM products;

2. 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;

3. 最值

# MAX() 最大值

SELECT MAX(prod_price) AS max_price

FROM products;

# MIN() 最小值

SELECT MIN(prod_price) AS min_price

FROM products;

4. 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;

5. SELECT 可以包含多个聚集函数

SELECT COUNT(*) AS item_num

MIN(prod_price) AS min_price

MAX(prod_price) AS max_price

AVG(prod_price) AS avg_price

FROM products;

十一、分组

1. 创建分组

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

SELECT vend_id, COUNT(*) AS prod_num

FROM products

GROUP BY vend_id;

2. 过滤分组,WHERE针对特定值(每个值,原值),HAVING针对分组过滤后的值

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

SELECT cust_id, COUT(*) AS order_num

FROM orders

GROUP BY cust_id

HAVING COUNT(*) >= 2;

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

SELECT vend_id, COUNT(*) AS prod_num

FROM vendors

WHERE prod_price >= 10

GROUP BY vend_id

HAVING COUNT(*) >= 2;

3. 语法顺序

SELECT

FROM

WHERE

GROUP BY

HAVING

ORDER BY

LIMIT

十二、子查询

1. 利用子查询过滤

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

# 检索包含物品TNT2的所有订单编号

SELECT order_num

FROM orderitems

WHERE prod_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 prod_id = 'TNT2'));

个人认为子查询不是最好的方式,看起来很累,所以不推荐使用,也不展开多写

十三、联结表

1. 创建联结 JOIN

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

SELECT prod_price, prod_name, vend_name

FROM products, vendors

WHERE products.vend_id = vendors.vend_id

ORDER BY vend_name, prod_name;

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

SELECT prod_price, prod_name, vend_name

FROM products

INNER JOIN vendors ON products.vend_id = vendors.vend_id;

2. 联结多个表

SELECT prod_price, prod_name, vend_name, order_num

FROM products, vendors, orders

WHERE products.vend_id = vendors.vend_id

AND products.prod_id = orders.prod_id

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

3. 自联结

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

SELECT p1.prod_id, p1.prod_name

FROM products AS p1, products AS p2

WHERE p1.vend_id = p2.vend_id

AND p2.prod_id = 'TNT2';

4. 自然联结

关系表中有一样的列,通过表别名和筛选,使每个列只返回一次

# *通配符只对c表使用,其他表的重复列没有被检索出来

SELECT c*, o.order_item, o.order_date, oi.prod_id, oi.prod_qty, oi.prod_price

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

WHERE c.cust_id = o.cust_id

AND p.prod_id = oi.prod_id

AND prod_id = 'TNT2';

5. 外部联结

# 为了检索所有客户的下单数量,包括没有订单的客户, 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;

6. 使用带聚集函数的联结

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

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;

十四、更新更改

1. 更新查询Update

UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]

# 把李四的地址改为上海(默认Beijing)

Update employee set city = 'Shanghai' where id = 2;

2. 更改表格

在表employee增加一列addr

Alter table employee add colunm addr varchar(40);

更新表名

Rename Table 表名 to 新表名;

十五、Alter选择

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

从表中删除 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 将它添加到新的位置。

2. 更新列名和数据类型

更改数据类型,把列 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;

3.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;

4. 改变表类型

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

ALTER TABLE testalter_tbl TYPE = MYISAM;

5. 对表进行重命名

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

ALTER TABLE testalter_tbl RENAME TO alter_tbl;

十六、Index索引

1.简单而唯一的索引

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

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)

2. 添加与删除 INDEX 的ALTER 命令

(1)为表添加索引,可以采用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)

(2)为现有表添加索引

ALTER TABLE testalter_tbl ADD INDEX (c);

# 可以使用 DROP 子句以及 ALTER 命令删除索引

ALTER TABLE testalter_tbl DROP INDEX (c);

3. 利用 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;

十七、表

1. 创建表

CREATE TABLE SalesSummary (

product_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

(product_name, total_sales, avg_unit_price, total_units_sold)

VALUES ('cucumber', 100.25, 90, 2);

2. 查看表

SELECT * FROM SalesSummary;

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

3. 删除表

默认情况下,当与数据库的连接终止时,临时表就不再存在。不过如果想在数据库处于连接时就删除它们,可以用 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';

4. 复制表

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

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

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

步骤1:获取表的完整结构

步骤2:重新命名该表,创建另一个表

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

十八、 Using Sequences序列

1. 使用 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');

2.对已有序列进行重新编号

如果一定要对 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);

3. 以特定值作为序列初始值

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 );

十九、Handling Duplicates 重复处理

1. 防止表中出现重复记录

# 可以在表中正确的字段内使用 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');

应该根据想要达到的重复处理行为来选择INSERT IGNORE 和 REPLACE。INSERT IGNORE 会保存重复记录的第一个,抛弃其余的记录;REPLACE 保存最后一个记录,去掉在其之前的所有记录。

# 强制唯一性的另一种办法是为表添加 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) );

2. 确认重复记录,并计算重复记录数

# 下面是计算表中姓名记录重复的查询:

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。

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

# 使用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);

4.使用表替换去除重复记录

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

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);

二十、数据导入

1. excel文件数据导入

(1)excel文件另存为 "CSV(逗号分隔)(*.csv)"

只保留需要的数据,不需要表头

默认保存的文件编码是ANSI,如果你的数据库(数据表)使用UTF-8编码,那么一定要将这个csv文件另存为UTF-8格式!

932835feff9461031b45b265df0cef6e.png

(2)在数据库中创建一个表,表头对应excel数据的表头

83ad60dc66c0aa2ab8a313eb10426d97.png

(3)导入

656d182951539ce5f112ac3a2ce23c72.png

导入错误原因:

excel表的格式不对,.csv UTF-8,修改下excel表格式

创建的表头类型和实际数据不符,如文本创建为数字,如长度超出创建的范围,修改下创建的表

注:导出用Data Export就可以

2. 利用 LOAD DATA 导入数据

MySQL 利用 LOAD DATA 语句作为批量数据加载器,从当前目录中读取 dump.txt 文件,然后把它加载进当前数据库的表 mytbl 中。

LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;

# 为了明确指定文件格式,使用 FIELDS 子句来描述行内字段特征LINES 子句指定行末尾序列。下例中的 LOAD DATA 语句表明,数据文件中的值由冒号(:)分隔,每行由换行符及回车符所终止。

LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl FIELDS TERMINATED BY ':' LINES TERMINATED BY 'rn';

# LOAD DATA 假定数据文件中的列的顺序与表中列的顺序相同。如果不为真,可以指定一个列表来指示数据文件中具体表列的加载方式。假如表有3个列:a、b和c,但数据文件中对应的是列b、c与a,则可以这样加载。

LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl (b, c, a);

已标记关键词 清除标记
表情包
插入表情
评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符
相关推荐
©️2020 CSDN 皮肤主题: 深蓝海洋 设计师:CSDN官方博客 返回首页