mysql中对表数据的增删改查_mysql--对行(表中数据)的增删改查

一.插入数据(增加)insert

1.插入数据(顺序插入)

语法一：

INSERT INTO 表名(字段1,字段2,字段3…字段n) VALUES(值1,值2,值3…值n); #指定字段来插入数据，插入的值要和你前面的字段相匹配

语法二：

INSERT INTO 表名 VALUES (值1,值2,值3…值n); #不指定字段的话，就按照默认的几个字段来插入数据

2.指定字段插入数据

语法：

INSERT INTO 表名(字段1,字段2,字段3…) VALUES (值1,值2,值3…);

3.插入多条记录

语法：#插入多条记录用逗号来分隔

INSERT INTO 表名 VALUES

(值1,值2,值3…值n),

(值1,值2,值3…值n),

(值1,值2,值3…值n);

4.插入查询结果

语法：

INSERT INTO 表名(字段1,字段2,字段3…字段n)

SELECT (字段1,字段2,字段3…字段n) FROM 表2

WHERE …; #将从表2里面查询出来的结果来插入到我们的表中，但是注意查询出来的数据要和我们前面指定的字段要对应好

二.更新(修改)数据update

语法：

UPDATE 表名 SET

字段1=值1, #注意语法，可以同时来修改多个值，用逗号分隔

字段2=值2,

WHERE CONDITION; #更改哪些数据，通过where条件来定位到符合条件的数据

示例：

UPDATE mysql.user SET password=password(‘123’)

where user=’root’ and host=’localhost’; #这句话是对myslq这个库中的user表中的user字段为‘root‘并且host字段为‘localhost‘的这条记录的password字段的数据进行修改，将passord字段的那个数据改为password(‘123‘)这个方法对123加工后的密码数据，password()这个方法是mysql提供的密码进行加密用的方法。

定位到某个记录，并把这个记录中的某项内容更改掉

三.删除数据delete,truncate

在MySQL中有两种方法可以删除数据，一种是DELETE语句，另一种是TRUNCATE TABLE语句。DELETE语句可以通过WHERE

对要删除的记录进行选择。而使用TRUNCATE TABLE将删除表中的所有记录。因此，DELETE语句更灵活。

1.delete

语法：

DELETE FROM 表名 WHERE CONITION; #删除符合条件的一些记录

DELETE FROM 表名；如果不加where条件，意思是将表里面所有的内容都删掉，但是清空所有的内容，一般我们用truncate ，能够将id置为零，delete不能将id置零，再插入数据的时候，会按照之前的数据记录的id数继续递增

示例：

DELETE FROM mysql.user WHERE password=’123’;

2.truncate

TRUNCATE TABLE 表名 # truncate table tb1;

delete 和truncate 的区别

效率方面:drop > truncate > delete

1、delete语句，是DML语句不会自动提交，truncate语句通常被认为是DDL语句能被自动提交。

2、delete语句，后面可以跟where子句，通常指定where子句中的条件表达式，只删除满足条件的部分记录，而truncate语句，只

能用于删除表中的所有记录。

3、truncate语句，删除表中的数据后，向表中添加记录时，自动增加字段的默认初始值重新从1开始，而使用delete语句，删除表

中所有记录后，向表中添加记录时，自动增加字段的值，为删除时该字段的最大值加1，也就是在原来的基础上递增。

4、delete语句，每删除一条记录，都会在日志中记录，而使用truncate语句，不会在日志中记录删除的内容，因此,truncate语句的

执行效率比delete语句高。

5.truncate删除表中的所有行数据,但表结构不变.新行的计数值会归零,(比如在约束了auto_increment时)如果想保留delete

四.查询数据 select (单表查询)

1.单表查询的语法,关键字

查询数据的本质：

mysql会到你本地的硬盘上找到对应的文件，然后打开文件，按照你的查询条件来找出你需要的数据。下面是完整的一个单表查询的语法

select * from，这个select * 指的是要查询所有字段的数据。

SELECT distinct 字段1,字段2... FROM 库名.表名 #from后面是说从库的某个表中去找数据，mysql会去找到这个库对应的文件夹下去找到你表名对应的那个数据文件，找不到就直接报错了，找到了就继续后面的操作

WHERE 条件 #从表中找符合条件的数据记录，where后面跟的是你的查询条件

GROUP BY field(字段) #分组

HAVING 筛选 #过滤，过滤之后执行select后面的字段筛选，就是说我要确定一下需要哪个字段的数据，你查询的字段数据进行去重，然后在进行下面的操作

ORDER BY field(字段) #将结果按照后面的字段进行排序

LIMIT 限制条数 #将最后的结果加一个限制条数，就是说我要过滤或者说限制查询出来的数据记录的条数

2. 关键字执行的优先级

From1.找到表:from

Where2.拿着where指定的约束条件，去文件/表中取出一条条记录

group by3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组

having4.将分组的结果进行having过滤

select5.执行select

distinct6.去重

order by7.将结果按条件排序：order by

limit8.限制结果的显示条数

SELECT DISTINCT

FROM

JOIN

ON

WHERE

GROUP BY

HAVING

ORDER BY LIMIT

关键字的定义顺序

(7) SELECT(8) DISTINCT (1) FROM (3) JOIN (2) ON (4) WHERE (5) GROUP BY (6) HAVING (9) ORDER BY (10) LIMIT

关键字的执行顺序

3.查询操作

简单查询:

# 查询

select 值 from 表名;

select * from 表名; # 查询表中所有数据

避免重复:

#避免重复

select distinct 字段名 from　表名；　　＃　去重复 distinct要写在所有查询字段的前面

distinct:将查询的结果进行去重

有时需要查询出某个字段不重复的记录，这时可以使用mysql提供的distinct这个关键字来过滤重复的记录，但是实际中我们往往用distinct来返回不重复字段的条数(count(distinct id)),其原因是distinct只能返回他的目标字段，而无法返回其他字段，distinct 想写在其他字段后面需要配合聚合函数来写。

mysql> select id,count(distinct post) from employee;

ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

报错了：是因为distinct不能返回其他的字段，只能返回目标字段

mysql> select count(distinct post) from employee;

+----------------------+

| count(distinct post) |

+----------------------+

| 4 |

+----------------------+

1 row in set (0.00 sec)

四则运算查询:

#四则运算查询

select name,age * 12 from tb1;

自定义显示格式concat():

select concat(‘游客编号:‘,customer_id,‘编号:‘,order_id) from table2;

select concat(‘游客编号:‘,customer_id,‘编号:‘,order_id) as 自定义显示字段 from table2;

结果如下:

4.where约束

where语句中可以使用：

1. 比较运算符：> < >= <= <> !=

2. between 80 and 100 值在80到100之间

3. in(80,90,100) 值是80或90或100

4. like ‘王%‘

可以是%或_

%表示任意多字符

_表示一个字符

5. 逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not

5.分组查询group by

1.分组发生在where之后，即分组是基于where之后得到的记录而进行的

2.分组指的是：将所有记录按照某个相同字段进行归类，比如针对员工信息表的职位分组，或者按照性别进行分组等

ONLY_FULL_GROUP_BY

这是mysql中对于group by的一种设置模式,对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中

所以对于设置了这个模式的时候,使用group by 时,要在group by 后加入所查询的字段.

比如: select name from table1 group by id,name  根据性别分组要查询名字时要加上 name

不过,如果我们用设置了unique约束的字段作为分组的依据，则每一条记录自成一组，这种分组没有意义多条记录之间的某个字段值相同，该字段通常用来作为分组的依据

#查看MySQL 5.7默认的sql_mode如下：

mysql> select @@global.sql_mode;

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

#!!! 注意 !!!

ONLY_FULL_GROUP_BY的语义就是确定select target list中的所有列的值都是明确语义，简单的说来，在ONLY_FULL_GROUP_BY模式下，

target list中的值要么是来自于聚集函数的结果，要么是来自于group by list中的表达式的值。

#设置sql_mole如下操作(我们可以去掉ONLY_FULL_GROUP_BY模式)：

mysql> set global sql_mode=‘STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION‘;

6.HAVING过滤

加在 group by 后面

having 与 where 的不同:

having的语法格式和where是一模一样的，只不过having是在分组之后进行的进一步的过滤，where不能使用聚合函数，having是可以使用聚合函数的

！！！执行优先级从高到低：where > group by > having

1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。

2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,having是可以使用聚合函数

7.查询排序:ORDER BY

按单列排序

SELECT * FROM employee ORDER BY salary; #默认是升序排列

SELECT * FROM employee ORDER BY salary ASC; #升序

SELECT * FROM employee ORDER BY salary DESC; #降序

8.限制查询的记录数:LIMIT

示例：

#取出工资表中工资最高的前三位

SELECT * FROM employee ORDER BY salary DESC LIMIT 3;

#默认初始位置为0,从第一条开始顺序取出三条

SELECT * FROM employee ORDER BY salary DESC LIMIT 0,5;

#从第0开始，即先查询出第一条，然后包含这一条在内往后查5条

SELECT * FROM employee ORDER BY salary DESC LIMIT 5,5;

#从第5开始，即先查询出第6条，然后包含这一条在内往后查5条

数据表中数据量很大的时候,可以用limit来进行分页显示

select * from employee limit 0,5; # 显示第0项开始的5行数据

9.使用正则表达式查询

正则表达式是通用的

#之前我们用like做模糊匹配，只有%和_，局限性比较强，所以我们说一个正则

SELECT * FROM employee WHERE name REGEXP ‘^wwe‘; # 以wwe开头的字符串

SELECT * FROM employee WHERE name REGEXP ‘on$‘; # 以on结尾的字符串

SELECT * FROM employee WHERE name REGEXP ‘m{2}‘; # m出现两次

原文：https://www.cnblogs.com/robertx/p/10292907.html