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

二、更新数据(update)

语法：

UPDATE 表名 SET

字段1=值1,

字段2=值2,

WHERE CONDITION;#where 有则根据条件修改，若是没有，则叫表中的对应字段的值全部修改

示例：

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

where user=’root’ and host=’localhost’;

三、删除数据(delete)

删除数据时，如果设置过主键自动增长：

部分删除，会保留行号，先修改主键的值，再添加新的数据

全部删除，会保留行号，先修改主键的值，在添加新的数据 =====>  全部修改可以用  truncate table 表名  来清空表中数据

修改添加数据时的自动增长的起始位置： altertable表名 auto_increment = 位置(整型数字)

语法：

DELETE FROM 表名

WHERE CONITION;

示例：

DELETE FROM mysql.user

WHERE password=’’;

四、查询数据

1、单表查询

(1)单表查询的语法

SELECT 字段1,字段2... FROM 表名

WHERE 条件

GROUP BY field

HAVING 筛选

ORDER BY field

LIMIT 限制条数

(2)关键字的优先级(重点)

重点中的重点：关键字的执行优先级from #1.找到表

where #2.根据where指定的约束条件，去表中取出一条条记录

group by #3.将取出的一条条记录分组，若没分组，则视为一个整体

having #4.将分组后的结果进行过滤

select #5.执行select

distinct #6.去重

order by #7.排序，默认是升序

limit #8.限制结果的显示条数

(2.1)关键字定义顺序

SELECT DISTINCT FROM

JOIN ONWHEREGROUP BYHAVINGORDER BYLIMIT

(2.2)关键字的执行顺序

(7) SELECT

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

(2.3)执行顺序详解

SQL语句的执行过程中，都会产生一个虚拟表，用来保存SQL语句的执行结果

执行FROM语句，知道最开始从哪个表开始的

执行ON语句，根据ON后面指定的条件筛选出符合条件的

添加外部行，只有在连接OUTER JOIN类型时才执行，RIGHT OUTER JOIN、LEFT OUTER JOIN、FULL OUTER JOIN  (详细见多表查询)

执行WHERE语句            注意：由于数据还没有分组，因此现在还不能在WHERE过滤器中使用where_condition=MIN(col)这类对分组统计的过滤；

执行GROUP BY分组，对使用WHERE子句得到的虚拟表进行分组操作，默认只显示组内第一条

执行HAVING过滤，对分组后的虚拟表进行过滤

执行SELECT，从虚拟表中筛选出需要的信息

执行DISTINCT语句　　去掉重复的内容

执行ORDER BY语句，对去重后的虚拟表进行排序

执行LIMIT[m,]n 语句，从m行(不包括m)开始显示n行数据

注意点：

#where

1.where是从硬盘众读取数据到内存2.比较运算符：> < >= <= <> !=

2.between 80 and 100 #值在10到20之间

3.in(80,90,100) #值是10或20或30

4.like 'e%' #模糊查询

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

where是一条一条读取数据到内存，根据后面的条件判断是符合，因此不能同聚合函数来对数据进行处理

#group by

1.在MySQL中，如果没有设置sql_mode为 ONLY_FULL_GROUP_BY ，那么结果就会只显示每一组的一条，别的被隐藏2.可以利用聚合函数，按照统计结果进行分组3.分组是在where之后得到的记录进行的4.大前提：可以按照任意字段分组，但是分组完毕后，比如group by post，只能查看post字段，如果想查看组内信息，需要借助于聚合函数5. 注意 ONLY_FULL_GROUP_BY

小窍门：‘每’这个字后面的字段，就是我们分组的依据6.ONLY_FULL_GROUP_BY作用：

没有设置ONLY_FULL_GROUP_BY,于是也可以有结果，默认都是组内的第一条记录，但其实这是没有意义的，因此设置sql_mode为ONLY_FULL_GROUP_BY

设置命令：setglobal sql_mode = 'ONLY_FULL_GROUP_BY'

#having

1.对分组之后的数据进行筛选，必须是分组了以后，才能进行having 筛选。2.并且是在从硬盘中读取完数据以后，才能进行分组，再开始筛选。3.筛选可以利用聚合函数，根据统计结果进行筛选4.Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数

#order by

对记录进行排序

select* fron t order by salary desc; #desc 降序排序

select * from t order by salary asc ; #asc 升序排序

如果不写明 desc/ asc ，默认为asc升序排序

#limit

1.限制显示的条数

select* from emp limit 3; #显示前三条数据

select * from emp limit 2,3; #显示从第3条开始的3条数据

2.常用于数据的分页显示

例如：

select*from emp limit 0,10; #第一页 # 页数 减1 乘以条数 得到起始位置

select *from emp limit 10,10; #第2页

select *from emp limit 20,10; #第3页

(3)聚合函数

聚合函数聚合的是 组 的内容，若是没有分组，则默认一组

sum() #求和

avg() #求平均数

max()/min() #求最大值 / 最小值

count() #统计个数

示例：

SELECT COUNT(sex='男') 男,COUNT(sex='女') 女 FROM employee;

SELECT COUNT(*) FROM employee WHERE depart_id=1;

SELECT MAX(salary) FROM employee;

SELECT MIN(salary) FROM employee;

SELECT AVG(salary) FROM employee;

SELECT SUM(salary) FROM employee;

SELECT SUM(salary) FROM employee WHERE depart_id=3;

(4)使用正则表达式查询

SELECT * FROM employee WHERE name REGEXP '^ale';

SELECT* FROM employee WHERE name REGEXP 'on$';

SELECT* FROM employee WHERE name REGEXP 'm{2}';

小结：对字符串匹配的方式

WHERE name= 'Tom';

WHERE name LIKE'yua%';

WHERE name REGEXP'on$';

2、多表查询

create table emp (id int,name char(10),sex char,dept_id int);

insert emp values(1,"大黄","m",1);

insert emp values(2,"老王","m",2);

insert emp values(3,"老李","w",30);

create table dept (id int,name char(10));

insert dept values(1,"市场");

insert dept values(2,"财务");

insert dept values(3,"行政");

建表

(1)什么是多表查询

多表查询是在多个表中查询数据

(2)查询方式

1. 交叉连接：不适用任何匹配条件。只是用于生成笛卡尔积2. 内连接：只连接匹配的行3. 外链接之左连接：优先显示左表全部记录4. 外链接之右连接：优先显示右表全部记录5. 全外连接：显示左右两个表全部记录6. 子查询：上一次查询的结果，作为本次查询的原始数据

(3)交叉查询

#交叉查询只是用来生成笛卡尔积，不适用于任何匹配

select*from emp,dept;

mysql> select *fromemp,dept ;+------+--------+------+---------+------+--------+

| id | name | sex | dept_id | id | name |

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

| 1 | 大黄 | m | 1 | 1 | 市场 |

| 2 | 老王 | m | 2 | 1 | 市场 |

| 3 | 老李 | w | 30 | 1 | 市场 |

| 1 | 大黄 | m | 1 | 2 | 财务 |

| 2 | 老王 | m | 2 | 2 | 财务 |

| 3 | 老李 | w | 30 | 2 | 财务 |

| 1 | 大黄 | m | 1 | 3 | 行政 |

| 2 | 老王 | m | 2 | 3 | 行政 |

| 3 | 老李 | w | 30 | 3 | 行政 |

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

View Code

(4)内连接查询(inner join)

#找两张表共有的部分，相当于利用条件从笛卡尔积结果中筛选出了正确的结果

select*from emp inner join dept on emp.dept_id =dept.id;

等同于：

select*from emp,dept where emp.dept_id = dept.id;

mysql> select *from emp inner join dept on emp.dept_id =dept.id;+------+--------+------+---------+------+--------+

| id | name | sex | dept_id | id | name |

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

| 1 | 大黄 | m | 1 | 1 | 市场 |

| 2 | 老王 | m | 2 | 2 | 财务 |

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

View Code

(5)外连接查询之左连接(left join   <===>  left outer join)

#以左表为准，左表中记录完全显示，右边匹配才显示#本质就是：在内连接的基础上增加左边有右边没有的结果

select*from emp left join dept on emp.dept_id = dept.id;

mysql> select * from emp left join dept on emp.dept_id =dept.id;+------+--------+------+---------+------+--------+

| id | name | sex | dept_id | id | name |

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

| 1 | 大黄 | m | 1 | 1 | 市场 |

| 2 | 老王 | m | 2 | 2 | 财务 |

| 3 | 老李 | w | 30 | NULL | NULL |

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

View Code

(6)外连接查询之右连接(right join <===> right outer join)

#以右表为准，右表中记录完全显示，左边匹配才显示#本质就是：在内连接的基础上增加右边有左边没有的结果

select*from emp right join dept on emp.dept_id = dept.id;

mysql> select *from emp right join dept on emp.dept_id =dept.id;+------+--------+------+---------+------+--------+

| id | name | sex | dept_id | id | name |

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

| 1 | 大黄 | m | 1 | 1 | 市场 |

| 2 | 老王 | m | 2 | 2 | 财务 |

| NULL | NULL | NULL | NULL | 3 | 行政 |

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

View Code

(7)全外连接(union间接连接)

全外连接：在内连接的基础上增加左边有右边没有的和右边有左边没有的结果，也就是把多个查询结果合并在一起显示#注意：mysql不支持全外连接 full JOIN#强调：mysql可以使用此种方式间接实现全外连接 union

select*from emp right join dept on emp.dept_id =dept.id

union

select*from emp left join dept on emp.dept_id =dept.id;

注意：

union与union all的区别：union会去掉相同的纪录

mysql> select *from emp right join dept on emp.dept_id =dept.id->union-> select *from emp left join dept on emp.dept_id =dept.id;+------+--------+------+---------+------+--------+

| id | name | sex | dept_id | id | name |

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

| 1 | 大黄 | m | 1 | 1 | 市场 |

| 2 | 老王 | m | 2 | 2 | 财务 |

| NULL | NULL | NULL | NULL | 3 | 行政 |

| 3 | 老李 | w | 30 | NULL | NULL |

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

View Code

3、子查询

当一次无法查询到所要的数据，可以利用子查询实现，子查询能实现的，也可以通过多表查询实现

#1：子查询是将一个查询语句嵌套在另一个查询语句中。#2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。#3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字#4：还可以包含比较运算符：= 、 !=、> 、

select * from emp where salary = (select max(salary) from emp);

取别名：给就近一个字段名或者是一个表去别名，as可以省略

select dep_id,avg(age) avg_age from emp group by dep_id t2;

这个语句相当于：select dep_id,avg(age)  as avg_age from emp group by dep_id as t2;