33.MySQL数据库：记录详细操作，单表查询

2021-07-31 11:25:10

记录详细操作
- 增、改、删
- 查：单表查询
  - 单表查询语法
  - 关键字的执行优先级
? ----
记录详细操作
增、删、改

增:
insert t1(字段1,字段2,字段3) values
(值1,值2,值3),
(值1,值2,值3),
(值1,值2,值3);


改:
update t1 set 
字段1 = 值1,
字段2 = 值2,
where 条件;

删:
delete from 表 where 条件;
truncate 表;   -- 清空表用它

查:单表查询
- 单表查询语法
```
select distinct 字段1,字段2,字段3 from 库.表
	where 条件
	group by 分组字段
	having 条件
	order by 排序字段
	limit 限制条数
```
- 关键字的执行优先级
  
  详情参考： https://www.cnblogs.com/linhaifeng/articles/7372774.html
```
重点中的重点：
from
where
group by
having
select
distinct
order by
limit
```
1.找到表:from

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

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

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

5.执行select

6.去重

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

8.限制结果的显示条数

1、简单查询

准备表和记录


company.employee
    员工id      id                  int             
    姓名        emp_name            varchar
    性别        sex                 enum
    年龄        age                 int
    入职日期     hire_date           date
    岗位        post                varchar
    职位描述     post_comment        varchar
    薪水        salary              double
    办公室       office              int
    部门编号     depart_id           int
创建表
create table employee(

id int not null unique auto_increment,

name varchar(20) not null,

sex enum(‘male‘,‘female‘) not null default ‘male‘, #大部分是男的

age int(3) unsigned not null default 28,

hire_date date not null,

post varchar(50),

post_comment varchar(100),

salary double(15,2),

office int, #一个部门一个屋子

depart_id int

);
查看表结构
mysql> desc employee;

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

| Field        | Type                  | Null | Key | Default | Extra          |

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

| id           | int(11)               | NO   | PRI | NULL    | auto_increment |

| name         | varchar(20)           | NO   |     | NULL    |                |

| sex          | enum(‘male‘,‘female‘) | NO   |     | male    |                |

| age          | int(3) unsigned       | NO   |     | 28      |                |

| hire_date    | date                  | NO   |     | NULL    |                |

| post         | varchar(50)           | YES  |     | NULL    |                |

| post_comment | varchar(100)          | YES  |     | NULL    |                |

| salary       | double(15,2)          | YES  |     | NULL    |                |

| office       | int(11)               | YES  |     | NULL    |                |

| depart_id    | int(11)               | YES  |     | NULL    |                |

+--------------+-----------------------+------+-----+---------+----------------+
插入记录
三个部门：教学，销售，运营
insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values

(‘egon‘,‘male‘,18,‘20170301‘,‘老男孩驻沙河办事处外交大使‘,7300.33,401,1), #以下是教学部

(‘alex‘,‘male‘,78,‘20150302‘,‘teacher‘,1000000.31,401,1),

(‘wupeiqi‘,‘male‘,81,‘20130305‘,‘teacher‘,8300,401,1),

(‘yuanhao‘,‘male‘,73,‘20140701‘,‘teacher‘,3500,401,1),

(‘liwenzhou‘,‘male‘,28,‘20121101‘,‘teacher‘,2100,401,1),

(‘jingliyang‘,‘female‘,18,‘20110211‘,‘teacher‘,9000,401,1),

(‘jinxin‘,‘male‘,18,‘19000301‘,‘teacher‘,30000,401,1),

(‘成龙‘,‘male‘,48,‘20101111‘,‘teacher‘,10000,401,1),
(‘歪歪‘,‘female‘,48,‘20150311‘,‘sale‘,3000.13,402,2),#以下是销售部门

(‘丫丫‘,‘female‘,38,‘20101101‘,‘sale‘,2000.35,402,2),

(‘丁丁‘,‘female‘,18,‘20110312‘,‘sale‘,1000.37,402,2),

(‘星星‘,‘female‘,18,‘20160513‘,‘sale‘,3000.29,402,2),

(‘格格‘,‘female‘,28,‘20170127‘,‘sale‘,4000.33,402,2),
(‘张野‘,‘male‘,28,‘20160311‘,‘operation‘,10000.13,403,3), #以下是运营部门

(‘程咬金‘,‘male‘,18,‘19970312‘,‘operation‘,20000,403,3),

(‘程咬银‘,‘female‘,18,‘20130311‘,‘operation‘,19000,403,3),

(‘程咬铜‘,‘male‘,18,‘20150411‘,‘operation‘,18000,403,3),

(‘程咬铁‘,‘female‘,18,‘20140512‘,‘operation‘,17000,403,3)

;
ps：如果在windows系统中，插入中文字符，select的结果为空白，可以将所有字符编码统一设置成gbk

简单查询：
# 查看employee表所有的字段
SELECT id,name,sex,age,hire_date,post,post_comment,salary,office,depart_id 
    FROM employee;
    
select * from employee;  -- *代表所有字段

# 查看employee表name，post的字段（名字和岗位）
select name,post from employee;

# 查看employee表去重后的post的字段。distinct对记录去重
select distinct post from employee;  

# 查看employee表name，salary的字段（名字和薪资）
select name,salary from employee;

# 查看employee表name和salary*12。as对字段取别名（名字和年薪）
select name as 名字,salary*12 as annual_salary from employee;

# concat()函数：对字符串进行拼接
select concat("名字",":",name) as new_name,concat("薪资",":",salary) as new_salary from employee;

# concat_ws() 第一个参数为分隔符
select concat_ws(":",name,post,salary) from employee;

# 结合CASE语句：case就是一种多分枝的if判断（了解）

SELECT
       (
           CASE
           WHEN NAME = ‘egon‘ THEN
               NAME
           WHEN NAME = ‘alex‘ THEN
               CONCAT(name,‘_BIGSB‘)
           ELSE
               concat(NAME, ‘SB‘)
           END
       ) as new_name
   FROM
       employee;

2、关键字where约束

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

注意：没有where条件相当于所有条件永远都为真

where字句中可以使用：

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

between 80 and 100 值在10到20之间

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

like ‘egon%‘
pattern可以是%或_，
%表示任意多字符
_表示一个字符

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

wheret条件筛选：

# 查看employee表id大于等于10的字段
select * from employee where id >= 10;

# 查看employee表id大于等于10和小于等于13的字段
select * from employee where id >= 10 and id <= 13;
select * from employee where id between 10 and 13;

# 查看employee表id等于10或id等于13或id等于15的字段
select * from employee where id = 10 or id = 13 or id = 15;
select * from employee where id in (10,13,15);

# 查看employee表post_comment是空的字段(ps：空字符串不代表空)
select * from employee where post_comment is null;

# 关键字LIKE模糊查询
# 查看employee表name是ji开头的字段
select * from employee where name like "ji%";

# 查看employee表name是ji开头且为三个字符的字段
select * from employee where name like "ji_"; -- 报错，没有该字段

3、关键字group by分组字段与聚合函数

group by就是对where条件筛选出来的一条条记录进行分组，分组后可以以组为单位进行统计

下面五个函数叫聚合函数：
max 最大值
min 最小值
avg 平均值
sum 求和
count 统计个数

在分完组之后，可以以组为单位统一出一个结果，也就是说一个组出一个结果

只要分组你想要的拿到的一定是聚合结果。但凡分组你的需求就不再是取某一条记录，而是通过分组拿到什么结果，并不是追求某一条记录长什么样

# 查看employee表每个部门的最大薪资，按岗位分组（将所有记录分为4个组）
select post,max(salary) from employee group by post;

# 查看employee表每个部门的最小薪资
select post,min(salary) from employee group by post;

# 查看employee表每个部门的平均薪资
select post,avg(salary) from employee group by post;

# 查看employee表每个部门的薪资总和
select post,sum(salary) from employee group by post;

# 查看employee表每个部门有多少名员工
select post,count(id) from employee group by post;

# 查看employee表有多少名男性，多少名女性
select sex,count(id) from employee group by sex;

# 查看每个部门男员工的平均薪资
select post,avg(salary) from employee where sex = "male" group by post;

# 查看每个部门年龄小于20岁的员工的平均薪资
select post,avg(salary) from employee where age > 20 group by post;

了解知识点：
select * from employee group by post; -- 查看只能取到每个组的第一条记录

# 设置或者在sql严格模式下：查看取值只能取分组字段
set sql_mode="only_full_group_by";

select * from employee group by post;  --报错
# 对于group by聚合操作,如果在select中的列没有在group by中出现,那么这个SQL是不合法的,因为列不在
# group by从句中,所以设置了sql_mode=only_full_group_by的数据库,在使用group by时就会报错。

注：不在该模式下即使取到值也只能取到每个组的第一条记录，这样毫无意义，这是由于sql默认宽松模式的影响，
所以分组完之后应该取的是分组字段以及聚合结果。无论在什么sql模式下，这条sql语句肯定不会报错，并且这条
sql语句符合以后的查询需求的。

4、关键字having过滤

在分组之后将分组的结果进行having过滤

===》where与having不一样的地方! ! !

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

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

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

# 查看男员工的平均薪资大于10000的部门
select post,avg(salary) from employee 
where sex = "male" group by post having avg(salary)>10000;

# 查看所有人的最高薪资（是在分组之后运行的聚合函数）
select max(salary) from employee;

根据sql语句关键字的执行优先级：先运行from》》没有where条件相当于条件永远为真
》》没有group by相当于整体为一组》》没有having过滤条件相当于永远为真》》再运
行聚合函数相当于把所有记录当成一个大组

5、关键字order by排序

将结果按条件排序

按单列排序
# 查看employee表所有人按薪资升序排序
select * from employee order by salary;  --默认升序排序
select * from employee order by salary asc;

# 查看employee表所有人按薪资降序排序
select * from employee order by salary desc;

# 查看按多列排序:先按照age升序排序，如果年纪相同，则按照薪资降序排序
select * from employee order by age asc,salary desc;

# 查看按多列排序:先按照age升序排序，如果年纪相同，则按照id降序排序
select * from employee order by age asc,id desc;

# 查看男员工的平均薪资大于10000的部门，并且按照平均薪资降序排序
select post,avg(salary) from employee 
where sex = "male" group by post having avg(salary)>10000
order by avg(salary) desc;

6、关键字limit

限制结果的显示条数

# 对所有记录显示前3条（默认初始位置为0）
select * from employee limit 3;                    

# 从第0开始，即先查询出第一条，然后包含这一条在内往后查5条
select * from employee limit 0,5;

# 从第5开始，即先查询出第6条，然后包含这一条在内往后查5条
select * from employee  limit 5,5;

# 从第10开始，即先查询出第11条，然后包含这一条在内往后查5条
select * from employee  limit 10,5;

7、使用正则表达式查询regexp

# 查看employee表名字以ale开头的字段
select * from employee where name regexp ‘^ale‘;

# 查看employee表名字以on结尾的字段
select * from employee where name regexp ‘on$‘;


小结：对字符串匹配的方式
where name = ‘egon‘;
where name like ‘yua%‘;
where name regexp ‘on$‘;

33.MySQL数据库：记录详细操作，单表查询

码农公寓

记录详细操作

增、改、删

查：单表查询

单表查询语法

关键字的执行优先级

记录详细操作

增、删、改

查:单表查询

单表查询语法

关键字的执行优先级

1、简单查询

创建表

查看表结构

插入记录

三个部门：教学，销售，运营

ps：如果在windows系统中，插入中文字符，select的结果为空白，可以将所有字符编码统一设置成gbk

2、关键字where约束

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

3、关键字group by分组字段与聚合函数

group by就是对where条件筛选出来的一条条记录进行分组，分组后可以以组为单位进行统计

在分完组之后，可以以组为单位统一出一个结果，也就是说一个组出一个结果

4、关键字having过滤

在分组之后将分组的结果进行having过滤

5、关键字order by排序

将结果按条件排序

6、关键字limit

限制结果的显示条数

7、使用正则表达式查询regexp

相关文章