今日内容回顾

操作表的SQL语句补充

修改表名

alter table 表名 rename 新表名；
新增字段

表尾部追加字段

alter table 表名 add 字段名字段类型（数字）约束条件；

表顶部追加字段

alter table 表名 add 字段名字段类型（数字）约束条件 first；

指定位置追加字段，after后面填入字段名新增字段会追加在该字段名后面。

alter table 表名 add 字段名字段类型（数字）约束条件 after 已存在的字段；
修改字段

修改表中已存在的字段（包括字段类型）

alter table 表名 change 旧字段名新字段名字段类型（数字）约束条件；

修改表中字段类型

alter table 表名 modify 字段名新的字段类型（数字）约束条件；
删除字段

alter table 表名 drop 字段名；

表查询关键字

数据准备

1.数据准备(直接拷贝)
	create table emp(
      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
    );
	#三个部门：教学，销售，运营
insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
    ('jason','male',18,'20170301','浦东第一帅形象代言',7300.33,401,1), #以下是教学部
    ('tom','male',78,'20150302','teacher',1000000.31,401,1),
    ('kevin','male',81,'20130305','teacher',8300,401,1),
    ('tony','male',73,'20140701','teacher',3500,401,1),
    ('owen','male',28,'20121101','teacher',2100,401,1),
    ('jack','female',18,'20110211','teacher',9000,401,1),
    ('jenny','male',18,'19000301','teacher',30000,401,1),
    ('sank','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);

查询关键字之select与from

select：自定义查询表中字段对应的数据

from：指定操作的对象，（具体是哪张表，也可以是多张）

在SQL语句中关键字编写顺序与执行顺序是不一致的！！！

列如： select name from emp;

上述代码在执行是，先执行from确认表，之后执行select确定字段。

编写SQL语句针对select和from可以先写个固定的模

select * from 表名其他操作

select后的字段可能是实际的，也可能是通过SQL动态产生的，所以可以先用* 占位后续根据情况做修改。

查询关键字之where筛选

练习题

查询id大于等于3小于等于6的数据。

between：在什么什么之间

select * from emp where id between 3 and 6；

select * from emp where id>=3 and id<=6;
查询薪资是20000或者18000或者17000的数据。

select * from emp where salary in（20000，18000，17000）；

select * from emp where salary = 20000 or salary=18000 or salary=17000；
查询员工姓名中包含o字母的员工姓名和薪资

在我们刚开始接触mysql查询的时候，可以选择按照查询的优先级顺序拼写出我们的sql语句

模糊查询：like

% 匹配任意个数的任意字符

_ 匹配单个个数的任意字符

先是查询那张表 from emp

再去根据什么条件取查 where name like '%o%';

然后对查询出来的数据筛选展示部分 select name，salary

select name，salary from emp where name like '%o%';
查询员工姓名是由四个字符组成的员工姓名与其薪资。

select name,salary from emp where name like '____';

selcet name,salary from emp where char_length(name)=4;
查询id小于3或者大于6的数据

select * from emp where id not between 3 and 6；

select * from emp where id not in(3,4,5,6);
查询薪资不在20000，18000，17000范围的数据。

select * from emp where salary not in（20000，18000，17000）；
查询岗位描述为空的员工名与岗位名，针对null不能用等号，只能用is

select name，post_comment from emp where post_comment is null；

查询关键字之group by分组

分组指的是按照一些指定的条件将单个的数据分为一个个整体。

需求：按照部门分组。

select * from emp group by post；

使用分组关键字之后我们是没办法直接获取组内单个数据的，应该是以组为操作单位

而不是上述中直接获取到组内的单个数据。

这说明了我们没有在配置中设置严格模式。

需要在命令里输入：set global sql_mode='strict_trans_tables,only_full_group_by';

设置该模式之后服务端不重启该命令旧永久有效。

only_full_group_by，分组以后默认只能拿分组的依据，不能再拿分组单个的依据否则报错！！！

设置了严格模式之后在输入上述命令：select * from emp group by post；，会直接报错。

按照什么分组就只能拿到该分组，其他字段不能直接获取，需要借助一些其他方法。

需要输入完整的 select post from emp group by post；

我们写SQL是否需要使用分组，可以在题目中得到答案，比如：每个、平均、最大、最小。

配合分组常见使用的有聚函数

max（最大值）、min（最小值）、sum（总和）、count（计数）、avg（平均）。

练习题

获取每个部门的最高工资

select post，max（salary） from emp group by post；

在展示的时候也可以给字段名起别名，如下：

select post as '部门'，max（salary）as '最高薪资' from emp group by post；

as也可以省略直接字段名后面跟上别名，但是不推荐因为这样看着寓意不明确。
获取每个部门最低工资。

select post min（salary） from emp group by post；
获取每个部门的平均工资

select post avg（salary） from emp group by post；
获取每个部门工资总和

select post sum（salary） from emp group by post；
获取每个部门的人数

select post count（id） from emp group by post；
查询分组之后的部门名称和每个部门下所有的员工姓名。

group_concat(分组之后使用)不仅可以用来现实除分组外字段还有拼接字符串的作用。

select post，group_concat(name) from emp group by post;

select post,group_concat(salary) from emp group by post;

select post,group_concat(name,"_SB") from emp group by post;

select post,group_concat(name,": ",salary) from emp group by post;

查询关键字之having过滤

where与having的功能其实是一样的，都是用来筛选数据。

只不过where用于分组之前的筛选，而having用于分组之后的筛选

统计各部门年龄在30岁以上的员工平均工资，并且保留平均工资大于10000的部门

select post,avg(salary) from emp

where age>=30

group by

having avg(salary)>=10000;

查询关键字之去重 distinct

去重去除重复的数据，但前提是需要去除的数据集必须是一摸一样的。

去重的使用是需要添加在需要去重对象的前面。

如下：

select distinct age from emp；

查询关键字之order by 排序

asc 升序 desc 降序

select * from emp order by 需要排序的字段名；默认为升序

select * from emp order by salary asc；升序

select * from emp order by salary desc；降序

俩者结合使用

先按照age降序排，在年纪相同的情况下按照薪资升序排。

select * from emp order by age desc，salary asc；

需求：统计各部门年龄在30岁以上的员工平均工资，并保留平均工资大于3000的部门，然后对平均工资进行排序。

select post,avg(salary) from emp

where age>=30

group by

having avg(salary)>=3000

order by avg（salary）asc ;

查询关键字之limit分页

limit限制展示条数

限制展示条数

select * from emp limit 3；

查询工资最高的人的详细信息

select * from emp order by salary desc limit 1;

分页显示

select * from emp limit 0,5; # 第一个参数表示起始位置，第二个参数表示的是条数，不是索引位置
select * from emp limit 5,5;

查询关键字regexp正则

使用关键字regexp可以使用正则筛选出所需要的数据

select * from emp where name regexp '^j.*(n|y)$';

多表查询思路

子查询

将一张表的查询结果括号括起来当作另外一条SQL语句的条件

类似于日常生活中解决问题的方式

第一步做什么

第二部基于第一步的结果在做操作
连表操作

先将所有涉及到结果的表全部拼接到一起形成一张达标，然后从大表中查询数据

#建表
create table dep1(
    id int primary key auto_increment,
    name varchar(20) );

create table emp1(
    id int primary key auto_increment,
    name varchar(20),
    gender enum('male','female') not null default 'male',
    age int,
    dep_id int);

#插入数据
insert into dep1 values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营'),
(205,'安保');

insert into emp1(name,gender,age,dep_id) values
('jason','male',18,200),
('dragon','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);

子查询

查询jason的部门名称

# 第一步先获取jason的部门称号
select dep_id from emp1 where name = 'jason';  # 200
# 第二部基于第一步获取到的部门称号，得到jason的部门名称
select name from dep1 where id = 200;
# 完整的子查询
select name from dep1 where id = (select dep_id from emp1 where name = 'jason');

连表操作

连表操作可以简单的理解为通过SQL语句将俩个表中的数据整合到一个表中便于处理数据。

select：自定义查询表中字段对应的数据

from：指定操作的对象，可以是单个对象，也可以是多个对象一起操作，

多个对象操作会造成笛卡尔积

select * from emp1，dep1；

我们不会使用笛卡尔积来求数据，需要使用大量的内存，效率太低。一旦数据量太大使用笛卡尔积求数据会直接把内存干爆。连表有专门的语法。

inner join 内连接

将俩张表拼接到一起，只拼接衔接条件中字段内俩边都有的数据。
select * from 表名 inner join 表名 on 衔接条件(表名.字段名=表名.字段名)

select * from emp1 inner join dep1 on emp1.dep_id=dep1.id;

left join 左连接

将俩张表拼接到一起，left join左边的表为基准展示所有的数据，没有对应则用NULL填充。

select * from 表名 left join 表名 on 衔接条件(表名.字段名=表名.字段名)

select * from emp1 left join dep1 on emp1.dep_id=dep.id;

right join 右连接

将俩张表拼接到一起，以right join有边的表为基准展示所有的数据，没有对应则用NULL填充。

select * from 表名 right join 表名 on 衔接条件（表名.字段名=表名.字段名）

select * from emp1 right join dep1 on emp1.dep_id=dep.id;

union 全连接

将俩张表拼接到一起，拼接俩张表中所有的数据俩张表中各自没有与对方相对应的数据则用NULL填充。

select * from emp1 left join dep1 on emp1.dep_id = dep1.id
union
select * from emp1 right join dep1 on emp1.dep_id = dep1.id;

posted @ 2022-08-17 19:17 瓮小辉阅读(28) 评论(0) 收藏举报

刷新页面返回顶部

wengxiaohui