今日内容:
1.完整查询语句
2.多表查询
3.子查询
1.完整查询语句:
首先对于昨天的学习补充一个复制表
示例:首先我在一个库中创建了一个t1表(id 为int类型 设置为主键 并且设置了自增描述auto_increment,然后是一个name字段 类型char(5))
然后我通过复制表的语句来实现了t2的创建:create table t2 select * from t1;
可以看出来我们复制的这个表没有复制到t1的描述和主键,只能赋值t1表的基本结构
接下来我开始总结今天所学的第一个知识点:
1.增:语句
(1)insert into 表名 字段名 字段名... value/values(字段值);
into 可省略,字段名(可选,如果写了字段名后面的值必须与写的字段名匹配起来,不写后面的值必须和表的结构完全匹配)
value 插入一条记录/values 插入多条记录
2.改:语句
update 表名 set 字段名 = 新值,字段名n =新值n where 条件
可以同时修改多个字段 用逗号隔开 (注意:最后一个字段不用加都好)
where 可选(有:满足修改条件的记录,无:全部修改)
3.删:语句
delete from 表名 where 条件
where 可选 (有:删除满足条件的记录,无:全部删除)
如果你想全部删除,建议使用“truncate table 表名” (因为delete关键字是通过逐行对比,然后进行删除,效率低)
4.查:语句
select distinct {看情况选择写:*|字段名|聚合函数|表达式} from 表名
where 条件 group by 字段名 having 条件 order by 字段名 limit 显示的条数
注:关键字的顺序必须与上述语法一致(并非所有都要用到,看情况选择用)
这里我先介绍一下简单查询:
1.*表示所有列都显示,也可以手动指定要显示的列,可以是多个
2.distinct:用于去除重复的记录,只取出完全相同的记录,当然你也可以手动指定要显示的列从而来去重
3.表达式 支持四则运算
然后我们需要记住其执行顺序:
select()
from()#相当于打开文件 ==》where #读取每一行并进行条件判断==》group#对数据进行分组==》having==》having#对分组后的数据再进行过滤==》distinct#去重==》order #排序==》limit #限制显示的记录数
示例:
select * from t1;#查看全部数据
select * from t1 where name = "xxx" or name = "yyy";#设置条件查看到了满足我们条件的数据
select id,distinct(name) from t1;#distintct:去重作用
select * from t1 group by name;#根据名字进行分组(虽然这个例子不太恰当 0.0)
select * from t1 group by name having id >3;#对分组后的数据进行过滤(注:特别注意 having 是用于过滤分组后的数据,也就是说没有gruop by 就没有 having)
select * from t1 group by name having id >3 order by id;#对于分组过滤后的数据再按照其id进行升序(注:降序就在后面加desc)
select * from t1 group by name having id >3 order by id limit 1,2;
select concat("id:",id) id,concat("name:",name) name from t1;
#这里有两个点需要学习
1.首先concat 这个关键字用来字符串拼接
2.concat("id:",id) id :注意这句话我又加了一个id 这个的意思就是给这个字段名取别名,若不加这个显示的字段名会是concat("id:",id)
select (case when id<4 then concat(name,"<4") when id>=4 then concat(name,">=4") end) 了解即可 from t1;
了解即可,自行理解
where 关键字
介绍:从硬盘上读取数据时的一个过滤条件
where的筛选过程:在没有索引的情况下,逐个比较,效率低(应该给表加索引提高其效率)
group by关键字
介绍:用于数据分组
1.更加方便的管理数据
2.更加方便的统计
用一个更加贴切的例子来举例group by 的使用,并介绍一下聚合函数:
表格数据:
create table emp (id int,name char(10),sex char,dept char(10),job char(10),salary double);
insert into emp values (1,"刘备","男","市场","总监",5800),
(2,"张飞","男","市场","员工",3000),
(3,"关羽","男","市场","员工",4000),
(4,"孙权","男","行政","总监",6000),
(5,"周瑜","男","行政","员工",5000),
(6,"小乔","女","行政","员工",4000),
(7,"曹操","男","财务","总监",10000),
(8,"司马懿","男","财务","员工",6000);
select *from emp group by dept;按照部门给数据分组
其实这个语句有两种情况
1.sql_mode中 没有设置 ONLY_FULL_GROUP_BY 显示每个组的第一条记录 没有意义 所以新版中 自带ONLY_FULL_GROUP_BY
2.sql_mode中有设置 ONLY_FULL_GROUP_BY 直接报错
原因是: * 表示所有字段都要显示 但是 分组后 记录的细节被隐藏 只留下了
这意味着:只有出现在group by 后面的字段才能被显示
聚合函数:
将一堆数据经过计算,得到一个数据
sum()求和
avg()求平均数
max()/min() 最大值/最小值
count()计数
示例
select dept,count(*) from emp group by dept;
select dept,avg(salary) from emp group by dept;
select dept,avg(salary) avg_salary from emp group by dept having avg_salary>5000;
having 关键字
介绍:用于对分组后的数据进行过滤
having不会单独出现 都是和group by一起出现
与where比较:
相同点:都用于数据过滤
不同点:
1.where是最先执行的,用于读取硬盘数据,而having 要等到数据读取完后,才能进行过滤,比where晚执行
1.where中不能使用聚合函数,having可以
order by 关键字:
介绍:用于对记录进行排序
desc降序
asc升序
默认为升序
limit 关键字:
介绍:用于限制显示记录数
limit n,m#n指的是从第几行的后一行开始显示,m指的是显示几行
常用于分页显示
2.多表查询
多表查询:
介绍:在多个表中查询需要的数据
例如:有学生表和班级表
给你一个班级名称,让你查询所有学生的数据
首先我们应该先查班级表 得到一个班级id 再根据id去学生表查询对应的学生
多表查询的方式
1.笛卡尔积查询
什么是笛卡尔积,用坐标中的一条记录 去链接另一张表的所有记录
就像是把 两张表的数据做了一个乘法
这将导致 产生大量的无用重复数据
我们要的效果是:员工表中的部门id 与 部门表中的id相同 就拼接在一起
准备实例的数据:
insert emp values(1,"大黄","m",1);
insert emp values(2,"老王","m",2);
insert emp values(3,"老李","w",30);
insert dept values(1,"市场");
insert dept values(2,"财务");
insert dept values(3,"行政");
select * from emp,dept where emp.dept_id = dept.id;
on 关键字:作用于多表查询,为其进行条件限制,它只能用在专门多表查询语句中
inner join:内连接查询
select * from emp inner join dept on emp.dept_id = dept.id;
left join :左连接查询,左边表中的数据完全显示,右边表中的数据匹配上才显示
select * from emp left join dept on emp.dept_id = dept.id;
right join:有链接查询,右边表中的数据完全显示,左边表中的数据匹配上才显示
select * from emp right join dept on emp.dept_id = dept.id;
全外连接:
full join mysql不支持 oracle支持
通过 union 间接实现
union 表示合并查询 意思是把多个查询结果合并在一起显示,要求是被合并的表结构必须相同,默认去除重复
union all 合并但是不去除重复
select * from emp left join dept on emp.dept_id = dept.id
union
select * from emp right join dept on emp.dept_id = dept.id;
总结:多表链接 在书写时 按照填空来书写 如果左边要全部显示 用left join
右边全部显示 用right join
全部显示 把左链接的结果和右链接的结果 合并
当然 也可以更多表一起查 但是 没有意义 并且你要尽量避免 太多表 一起查
最多三张 在多对多的时候
3.子查询
1
:子查询是将一个查询语句嵌套在另一个查询语句中。
2
:内层查询语句的查询结果,可以为外层查询语句提供查询条件。
3
:子查询中可以包含:IN、NOT IN、
ANY
、
ALL
、EXISTS 和 NOT EXISTS等关键字
4
:还可以包含比较运算符:
=
、 !
=
、> 、<等
带IN关键字的子查询
查询平均年龄在25岁以上的部门名 select id,name from department where id in (select dep_id from employee group by dep_id having avg(age) > 25); 查看技术部员工姓名 select name from employee where dep_id in (select id from department where name='技术'); 查看不足1人的部门名(子查询得到的是有人的部门id) select name from department where id not in (select distinct dep_id from employee);
带比较运算符的子查询
比较运算符:=、!=、>、>=、<、<=、<> 查询大于所有人平均年龄的员工名与年龄 mysql> select name,age from emp where age > (select avg(age) from emp); +---------+------+ | name | age | +---------+------+ | alex | 48 | | wupeiqi | 38 | +---------+------+ 2 rows in set (0.00 sec) 查询大于部门内平均年龄的员工名、年龄 select t1.name,t1.age from emp t1 inner join (select dep_id,avg(age) avg_age from emp group by dep_id) t2 on t1.dep_id = t2.dep_id where t1.age > t2.avg_age;
带EXISTS关键字的子查询
EXISTS关字键字表示存在。在使用EXISTS关键字时,内层查询语句不返回查询的记录。而是返回一个真假值。True或False,当返回True时,外层查询语句将进行查询;当返回值为False时,外层查询语句不进行查询。
#department表中存在dept_id=203,Ture mysql> select * from employee -> where exists -> (select id from department where id=200); +----+------------+--------+------+--------+ | id | name | sex | age | dep_id | +----+------------+--------+------+--------+ | 1 | egon | male | 18 | 200 | | 2 | alex | female | 48 | 201 | | 3 | wupeiqi | male | 38 | 201 | | 4 | yuanhao | female | 28 | 202 | | 5 | liwenzhou | male | 18 | 200 | | 6 | jingliyang | female | 18 | 204 | +----+------------+--------+------+--------+ #department表中存在dept_id=205,False mysql> select * from employee -> where exists -> (select id from department where id=204); Empty set (0.00 sec)