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. 关键字执行的优先级
From 1.找到表:from Where 2.拿着where指定的约束条件，去文件/表中取出一条条记录 group by 3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组 having 4.将分组的结果进行having过滤 select 5.执行select distinct 6.去重 order by 7.将结果按条件排序：order by limit 8.限制结果的显示条数
SELECT DISTINCT <select_list> FROM <left_table> <join_type> JOIN <right_table> ON <join_condition> WHERE <where_condition> GROUP BY <group_by_list> HAVING <having_condition> ORDER BY <order_by_condition> LIMIT <limit_number>
(7) SELECT (8) DISTINCT <select_list> (1) FROM <left_table> (3) <join_type> JOIN <right_table> (2) ON <join_condition> (4) WHERE <where_condition> (5) GROUP BY <group_by_list> (6) HAVING <having_condition> (9) ORDER BY <order_by_condition> (10) LIMIT <limit_number>
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出现两次
