mysql数据库-1-1-基础-表数据curd，单表查询，多表查询

表数据操作curd

1，插入数据：

#语法一: 按字段进行插入
insert into 表(字段1,字段2 ...) values (值1,值2 ...);
#语法二:按字段顺序插入
insert into 表 values (值1,值2 ...);
#语法三: 插入多条记录
insert into 表 values (值1,值2 ...) ,(值1,值2 ...)  ,(值1,值2 ...);
#语法四:插入查询结果
insert into 表(字段1,字段2 ...)  select 字段1,字段2 ... from 表;

使用select结果作为插入内容

背景

在实际使用中会遇到这样的场景，需要根据一个表里的内容在其他表中添加关联的数据，这时候就会用到

insert into {table_name} select x1,x2,x3... from {table_name2} where xxx

使用方法

1、通过select 语句找到在 table1 中需要的内容
select id, name, ... from table1 where id in (1, 2, 3, ...);

2、用select 语句 替换 insert 语句中的 values 
insert into table2 select id,name... from table1 where xxx

3、如果需要插入的值 table1 里面没有 就直接在 select 语句中按照 table2 的字段顺序添加上就可以了
//其中 100 是table2 表中的第三个字段的值
//'张三' 是table2 表中的第四个字段的值
insert into table2 select id,name,100,'张三'... from table1 where xxx

2，更新数据：

#语法一: 更新整表数据
update 表 set 字段1= '值1', 字段2='值2' ... ;
#语法二:更新符合条件字段3的数据
update 表 set 字段1= '值1', 字段2='值2' ... where 字段3 = 值3;

3，删除数据：

#语法一:整表数据删除
delete from 表 ;
#语法二:删除符合 where后条件的数据
delete from 表 where 字段1=值1;

单表数据查询

一.简单查询

#查询所有字段信息
select * from person;
#查询指定字段信息
select id,name,age,sex,salary from person;
#别名查询，使用的as关键字，as可以省略的
select name,age as'年龄',salary '工资' from person;
#直接对列进行运算,查询出所有人工资,并每人增加100块.
select (5/2);
select name, salary+100 from person;
#剔除重复查询
select distinct age from person;

二 条件查询

#比较运算符: > < >= <= = <>(!=)    is null 是否为null
select * from person where age = 23;
select * from person where age <> 23;
select * from person where age is null;
select * from person where age is not null;
#逻辑运算符: 与 and 或 or
select * from person where age = 23 and salary =29000;
select * from person where age = 23 or salary =29000;

三 区间查询

# 使用  between...and  进行区间 查询
select * from person where salary between 4000 and 8000;
ps: between...and 前后包含所指定的值
等价于 select * from person where salary >= 4000 and salary <= 8000;

四 集合查询

#使用 in 集合(多个字段)查询
select * from person where age in(23,32,18);
等价于: select * from person where  age =23 or age = 32 or age =18;
#使用 in 集合 排除指定值查询
select * from person where age not in(23,32,18);

五 模糊查询

#模糊查询  like %:任意多个字符,  _:单个字符
#查询姓名以"张"字开头的
select * from person where name like '张%';
#查询姓名以"张"字结尾的
select * from person where name like '%张';
#查询姓名中含有"张"字的
select * from person where name like '%张%';
#查询 name 名称 是四个字符的人
select * from person where name like '____';
#查询 name 名称 的第二个字符是 'l'的人
select * from person where name like '_l%';
#排除名字带 a的学生
select * from student where name not like 'a%'

六 排序查询

升序:ASC 默认为升序
降序:DESC
PS:排序order by 要写在select语句末尾
#按人员工资正序排列,注意:此处可以省略 ASC关键字
select * from person order by salary ASC;
select * from person order by salary;
#工资大于5000的人,按工资倒序排列
select * from person where salary >5000 order by salary DESC;
#按中文排序
select * from person order by name;
#强制中文排序
select * from person order by CONVERT(name USING gbk);
ps:UTF8 默认校对集是 utf8_general_ci , 它不是按照中文来的。你需要强制让MySQL按中文来排序

七 聚合函数

聚合函数: 对列进行操作,返回的结果是一个单一的值,除了 COUNT 以外，都会忽略空值
COUNT：统计指定列不为NULL的记录行数；
SUM：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
MAX：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
MIN：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
AVG：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；
#格式:
select 聚合函数(字段) from 表名;
#统计人员中最大年龄、最小年龄，平均年龄分别是多少
select max(age),min(age),avg(age) from person;

八 分组查询

#分组查询格式:
select 被分组的字段 from 表名 group by 分组字段 [having 条件字段]
ps: 分组查询可以与 聚合函数 组合使用.
#查询每个部门的平均薪资
select avg(salary),dept from person  GROUP BY dept;
#查询每个部门的平均薪资 并且看看这个部门的员工都有谁?
select avg(salary),dept,GROUP_CONCAT(name) from person GROUP BY dept;
#GROUP_CONCAT(expr):按照分组，将expr字符串按逗号分隔，组合起来
#查询平均薪资大于10000的部门, 并且看看这个部门的员工都有谁?
select avg(salary),dept,GROUP_CONCAT(name) from person GROUP BY dept; having avg(salary)>10000;

九 分页查询

背景1：查询返回的记录太多了，查看起来很不方便，怎么样能够实现分页查询呢？
背景2：表里有 4 条数据，如果只想要显示第 2、3 条数据怎么办呢？
好处:限制查询数据条数,提高查询效率

MySQL中使用 LIMIT 实现分页
格式：
LIMIT [位置偏移量,] 行数
第一个“位置偏移量”参数指示MySQL从哪一行开始显示，是一个可选参数，如果不指定“位置偏移量”，将会从表中的第一条记录开始（第一条记录的位置偏移量是0，第二条记录的位置偏移量是1，以此类推）；第二个参数“行数”指示返回的记录条数。

举例

--前10条记录：
SELECT * FROM 表名 LIMIT 0,10;
或者
SELECT * FROM 表名 LIMIT 10;

--第11至20条记录：
SELECT * FROM 表名 LIMIT 10,10;

--第21至30条记录： 
SELECT * FROM 表名 LIMIT 20,10;

使用limit实现数据的分页显示

需求1：每页显示5条记录，此时显示第1页

SELECT employee_id,last_name
FROM employees
LIMIT 0,5;

需求2：每页显示6条记录，此时显示第2页

SELECT employee_id,last_name
FROM employees
LIMIT 5,6;

需求3：每页显示7条记录，此时显示第3页

SELECT employee_id,last_name
FROM employees
LIMIT 6,7;

需求4：每页显示pageSize条记录，此时显示第pageNo页：

公式：
LIMIT (pageNo-1) * pageSize, pageSize;
分页显式公式：（当前页数-1）* 每页条数，每页条数
SELECT * FROM table 
LIMIT(PageNo - 1)*PageSize, PageSize;
注意：LIMIT 子句必须放在整个SELECT语句的最后！

使用 LIMIT 的好处：

约束返回结果的数量可以减少数据表的网络传输量，也可以提升查询效率。如果我们知道返回结果只有 1 条，就可以使用LIMIT 1，告诉 SELECT 语句只需要返回一条记录即可。这样的好处就是 SELECT 不需要扫描完整的表，只需要检索到一条符合条件的记录即可返回。

WHERE ... ORDER BY ...LIMIT 声明顺序如下：
LIMIT的格式： 严格来说：LIMIT 位置偏移量,条目数
结构"LIMIT 0,条目数" 等价于 “LIMIT 条目数”

SELECT employee_id,last_name,salary
FROM employees
WHERE salary > 6000
ORDER BY salary DESC
#limit 0,10;
LIMIT 10;

练习：表里有107条数据，如果只想要显示第 32、33 条数据怎么办呢？

SELECT employee_id,last_name
FROM employees
LIMIT 31,2;

MySQL 8.0中可以使用“LIMIT 3 OFFSET 4”，意思是获取从第5条记录开始后面的3条记录，和“LIMIT 4,3;”返回的结果相同。
MySQL8.0新特性：LIMIT ... OFFSET ...

练习：表里有107条数据，如果只想要显示第 32、33 条数据怎么办呢？

SELECT employee_id,last_name
FROM employees
LIMIT 2 OFFSET 31;

练习：查询员工表中工资最高的员工信息

SELECT employee_id,last_name,salary
FROM employees
ORDER BY salary DESC
#limit 0,1
LIMIT 1;

十 正则表达式

　MySQL中使用 REGEXP 操作符来进行正则表达式匹配。
# ^  匹配 name 名称 以 "e" 开头的数据
select * from person where name REGEXP '^e';
# $  匹配 name 名称 以 "n" 结尾的数据
select * from person where name REGEXP 'n$';
# . 匹配 name 名称 第二位后包含"x"的人员 "."表示任意单个字符
select * from person where name REGEXP '.x';
# [abci] 匹配 name 名称中含有指定集合内容的人员
select * from person where name REGEXP '[abci]';
# [^alex] 匹配 不符合集合中条件的内容 , ^表示取反
select * from person where name REGEXP '[^alex]';
#注意1：^只有在[]内才是取反的意思，在别的地方都是表示开始处匹配
#注意2 : 简单理解 name  REGEXP '[^alex]' 等价于 name != 'alex'
# 'a|x' 匹配 条件中的任意值
select * from person where name REGEXP 'a|x';　　
#查询以w开头以i结尾的数据
select * from person where name regexp '^w.*i$';
#注意:^w 表示w开头, .*表示中间可以有任意多个字符, i$表示以 i结尾

十一 SQL 语句关键字的执行顺序

select name, max(salary)
from person
where name is not null
group by name
having max(salary) > 5000
order by max(salary)
limit 0,5

多表数据查询

一.多表联合查询

#多表查询语法
select  字段1,字段2... from 表1,表2... [where 条件]
注意: 如果不加条件直接进行查询,则会出现以下效果,这种结果我们称之为 笛卡尔乘积
#查询人员和部门所有信息
select * from person,dept where person.did = dept.did;
#注意: 多表查询时,一定要找到两个表中相互关联的字段,并且作为条件使用

二 多表连接查询

#多表连接查询语法(重点)
SELECT 字段列表
FROM 表1  INNER|LEFT|RIGHT JOIN  表2
ON 表1.字段 = 表2.字段;
1 内连接查询 (只显示符合条件的数据)
#查询人员和部门所有信息
select * from person
inner join dept
on person.did =dept.did;
效果: 大家可能会发现, 内连接查询与多表联合查询的效果是一样的.
2 左外连接查询 (左边表中的数据优先全部显示)
#查询人员和部门所有信息
select * from person
left join  dept
on  person.did =dept.did;
效果:人员表中的数据全部都显示,而 部门表中的数据符合条件的才会显示,不符合条件的会以 null 进行填充.
3 右外连接查询 (右边表中的数据优先全部显示)
#查询人员和部门所有信息
select * from person
right join  dept
on  person.did =dept.did;
效果:正好与[左外连接相反]
4 全连接查询(显示左右表中全部数据)
全连接查询：是在内连接的基础上增加 左右两边没有显示的数据
注意: mysql并不支持全连接 full JOIN 关键字
注意: 但是mysql 提供了 UNION 关键字.使用 UNION 可以间接实现 full JOIN 功能
#查询人员和部门的所有数据
SELECT * FROM person LEFT JOIN dept ON person.did = dept.did
UNION
SELECT * FROM person RIGHT JOIN dept ON person.did = dept.did;

三 复杂条件多表查询

1. 查询出 教学部 年龄大于20岁,并且工资小于40000的员工,按工资倒序排列.(要求:分别使用多表联合查询和内连接查询)
#1.多表联合查询方式:
select * from person p1,dept d2 where p1.did = d2.did
    and d2.dname='python'
    and  age>20
    and salary <40000
ORDER BY salary DESC;
#2.内连接查询方式:
SELECT * FROM person p1 INNER JOIN dept d2 ON p1.did= d2.did
    and d2.dname='python'
    and  age>20
    and salary <40000
ORDER BY salary DESC;
2.查询每个部门中最高工资和最低工资是多少,显示部门名称
select MAX(salary),MIN(salary),dept.dname from
        person LEFT JOIN dept
            ON person.did = dept.did
 GROUP BY person.did;


3，三张表查询，sql如下：,
select a.uid,a.uname,a.upsw,a.urealname,a.utel,a.uremark, b.rid,b.rname,b.rremark,c.deptid,c.deptname,c.deptremark
from table1 a,table2 b,table3 c
where a.sems_role_rid=b.rid and a.udeptid=c.deptid
或者：
select a.uid,a.uname,a.upsw,a.urealname,a.utel,a.uremark, b.rid,b.rname,b.rremark,c.deptid,c.deptname,c.deptremark
from table1 a
left join table2 b on  a.sems_role_rid=b.rid
left join table3 c on a.udeptid=c.deptid
LEFT JOIN 可以实现统一数据库多表联合查询符合条件的数据。

四 子语句查询

1.作为表名使用
select * from (select * from person) as 表名;
2，作为字段的值
select name,salary from person where salary=(select max(salary) from person);

五 SQL逻辑查询语句执行顺序(重点***)

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>