中国网站模板免费下载,免费做外贸的网站空间,网站加载慢,网页图片怎么打印出来查询语句mysql中要学习的知识#xff1a;多表关系#xff0c;查询语句#xff0c;索引添加数据补充将一个查询结果插入到另一张表中create table student(name char(10), gender int);insert into student values(nalituo, 1);insert into student values(sasigi, 0);create…查询语句mysql中要学习的知识多表关系查询语句索引添加数据补充将一个查询结果插入到另一张表中create table student(name char(10), gender int);insert into student values(nalituo, 1);insert into student values(sasigi, 0);create table student_man(name char(10), gender int);# 快速将student表中性别为1的记录插入student_man表中insert into student_man select * from student where gender 1;所有select关键字select [distinct] * from table_namewheregroup byhavingorder bylimit a,b必须存在的 select* 可以换成任意一个或者多个字段可以重复但是*必须写最前面# 注意关键字的顺序是固定的不能随意变化# 注意执行顺序distinct函数在having后面其余从select到limit都和定义顺序相同简单查询*表示通配符显示所有字段可以指定任意个字段名可以对字段的数据进行四则运算聚合函数如下取别名当字段名太长获取时不容易理解时可以使用as来取别名也可以不写as直接空格加别名举例(下面查询的都是在这张表的基础上)where 条件# 数据创建create table stu(id int primary key auto_increment,name char(10),math float,english float);insert into stu values(null,赵云,90,30);insert into stu values(null,小乔,90,60);insert into stu values(null,小乔,90,60);insert into stu values(null,大乔,10,70);insert into stu values(null,李清照,100,100);insert into stu values(null,铁拐李,20,55);insert into stu values(null,小李子,20,55);# 查看所有数据select * from stu;# 查看英语成绩select english from stu;# 查看每个人的总分select englishmath as 总分 from stu;# 为每个人的数学都减10分select math-10 数学 from stu;# 不需要加引号包括中文1.whereselect * from table_namewhere 后接1.比较运算符 !2.成员运算符 in not in 后面接一个set3.逻辑运算符 and or not4.模糊查询like% 表示0-任意个数的任意字符_ 表示1个任意字符%李%表示查询所有带有李字的记录5.between and 两者之间 两者都能取到值6.is null 是否为空7.exists 子查询语句# 注意int类型查询的时候加上也可以查到举例# 查询姓大的数学小于80分并且英语大于20分的人的数学成绩select name,math from stu where name like 大% and math 80 and english 20;# 查询英语及格的人的平均分select name, (englishmath)/2 as 平均分 from stu where english 60;2.distinct 去除重复记录# 仅在查询结果中所有字段全部相同时才算重复的记录select distinct * from stu;# 名字相同就重复select distinct name from stu;3.group by分组即将一个整体按照某个特征或依据来分为不同的部分为什么要分组分组是为了统计例如统计男性有几个女性有几个数据准备create table emp(id int primary key auto_increment,name char(10),sex char(10),dept char(10),job char(10),salary float);insert into emp values(1,刘备,男,市场,总监,5800),(2,张飞,男,市场,员工,3000),(3,关羽,男,市场,员工,4000),(4,孙权,男,行政,总监,6000),(5,周瑜,男,行政,员工,5000),(6,小乔,女,行政,员工,4000),(7,曹操,男,财务,总监,10000),(8,司马懿,男,财务,员工,6000);# 语法select xxx from table_name group by 字段名;# 需求按照性别进行分组select * from emp group by sex;# 这样做只会显示该分组的第一行数据这是因为emp有很多行而分组聚合后的sex只有两行两者的行数不匹配逻辑是错误的。# mysql 5.6下查询的结果是name仅显示为该分组的第一个# mysql 5.7以上则会直接报错5.6也可以手动开启这个功能# 把 ONLY_FULL_GROUP_BY 添加到服务器的配置文件中统计函数也称之为聚合函数将一堆数据经过计算得出一个结果sum avg count max min可以用在字段的位置 或是分组的后面求和sum(字段名)平均数 avg(字段名)最大值 max(字段名)最小值 min(字段名)个数 count(字段名) # 字段名称可以使用*代替,因为如果只查一个字段那么当该字段为空时是不计数的# 例如查找所有人的工资总和select sum(salary) from emp;# 例如查询所有人的平均工资select avg(salary) from emp;# 例如查询工资最高的人的姓名select name,max(salary) from emp;# 不是想要的效果因为默认显示的是第一个name因为name有很多行而max(salary)只有一行select name from emp where salary max(salary);# 报错原因伪代码def where(条件):for line in file:if salary max(salary)# 分析 where 读取满足条件的一行 ,max()先要拿到所有数据 才能求最大值。# 但这里由于读取没有完成所有无法求出最大值# 结论 where 后面不能使用聚合函数分组统计# 需求 查询每个性别有几个人select sex, count(*) from emp group by sex;# 需求查询每个性别有几个人并且显示名字select name,sex,count(*) from emp group by sex;# 报错# 我们可以用group_concat 将分组之外的字段 做一个拼接 ,但是这是没有意义# 如果要查询某个性别下的所有信息直接使用where即可# 结论只有出现在了group by 后面得字段才能出现在select的后面4.having用于过滤但是与where不同的是having使用在分组之后案例# 求出平均工资大于5000的部门信息select dept,avg(salary) from emp group by dept having avg(salary) 5000;# 查询部门人数少于3的部门名称 人员名称 人员个数select dept, group_concat(name), count(*) from emp group by dept having count(*) 3;5.order by根据某个字段排序# 语法select * from table_name order by 字段名称;# 默认升序使用desc降序asc升序# 改为降序select * from table_name order by 字段名称 desc;# 多字段先按照第一个排序第一个相同再按照第二个select * from table_name order by 字段名称1 desc, 字段名称2 asc;# 案例工资升序id降序select * from emp order by salary asc, id desc6.limit用于限制要显示的记录数量# 语法1select * from table_name limit 个数;# 语法2select * from table_name limit 起始位置,个数;# 语法3select * from table_name limit 个数 offset 过滤个数;# 案例查询前三条select * from emp limit 3;# 从第3条开始查询3条select * from emp limit 2, 3;# 从第4条开始查询2条select * from emp limit 2 offset 3;# 注意起始位置从0开始# 经典的使用场景分页显示1.每一页显示的条数 a 32.明确当前页数 b 23.计算起始位置 c (b-1) * aselect * from emp limit 0,3;select * from emp limit 3,3;select * from emp limit 6,3;# django中会提供一个分页功能但是它是先查询所有数据丢到列表中再取出数据这样如果数据量太大可能会有问题子查询将一条语句的结果作为另一条语句的条件或者是数据来源当我们一次性查不到想要的数据时就需要使用子查询in 关键字子查询当内层查询(括号内的)结果会有多个结果时不能使用必须使用in另外in查询只能包含一个字段也就是一列数据需求找出平均年龄大于25的部门名称准备数据drop table if exists emp;create table emp (id int primary key ,name char(10),sex char,age int,dept_id int,job char(10),salary double);insert into emp values(1,刘备,男,26,1,总监,5800),(2,张飞,男,24,1,员工,3000),(3,关羽,男,30,1,员工,4000),(4,孙权,男,25,2,总监,6000),(5,周瑜,男,22,2,员工,5000),(6,小乔,女,31,2,员工,4000),(7,曹操,男,19,3,总监,10000),(8,司马懿,男,24,3,员工,6000);create table dept(id int primary key,name char(10));insert into dept values(1,市场),(2,行政),(3,财务);# 1. 查询出平均年龄大于25的部门编号select dept_id from emp group by dept_id having avg(age) 25;#2. 根据编号查询部门的名称select * from dept where id in (1, 2);# 子查询合并select * from dept where id in (select dept_id from emp group by dept_id having avg(age) 25);子查询的思路:1.要分析 查到最终的数据 到底有哪些步骤2.根据步骤写出对应的sql语句3.把上一个步骤的sql语句丢到下一个sql语句中作为条件# 注意如果用子查询那么内部的语句查询的字段必须指定而不能为*否则会报错因为外部得到的结果会乱掉exist关键字子查询当内层查询有结果时外层才会执行# 案例select * from dept where exists (select * from dept where id 1);# 由于内层查询产生了结果所以执行了外层查询dept的所有数据多表查询笛卡尔积查询# 案例select * from table1,table2,...# 笛卡尔积查询在数据关联关系错误时结果会出现大量的错误数据# 这时我们需要添加过滤条件从表外键值等于主表的主键值select * from emp, dept where dept_iddept.id;# 并且会产生重复的字段信息例如员工里的部门编号和部门表里的id字段所以在select 后指出需要查询的字段名称# 案例select dept.name as 部门, dept.id 部门编号, emp.name 姓名, emp.id 员工编号, sex from emp, dept where dept.iddept_id;内连接查询语法本质上就是笛卡尔积查询# 语法select * from table1 inner join table2;# 案例select * from emp inner join dept where dept_id dept.id# 其中inner可以省略emp和dept的顺序也可以调换# 这里的where可以用on替代外连接查询包括没有匹配关系的数据不常用因为一般都会有外键约束对应关系举例# 不存在外键关联的两张表# 存在一些不正确的部门iddrop table if exists emp;create table emp(id int primary key, name char(10), sex char(10), dept_id int );insert into emp values(1,大黄,m,1);insert into emp values(2,老王,m,2);insert into emp values(3,老李,w,30);drop table if exists dept;create table dept(id int primary key, name char(10));insert into dept values(1, 市场);insert into dept values(2, 财务);insert into dept values(3, 行政);左外连接查询左边的表无论是否能够匹配都要完整显示右边的仅展示匹配上的记录# 需求要查询所有员工以及其所属的部门信息select * from emp left join dept on dept_id dept.id;# 注意在外连接查询中不能够使用where关键字必须使用on关键字专门来做表的对应关系右外连接查询右边的表无论是否能够匹配都要完整显示左边的仅展示匹配上的记录# 需求要查询所有部门以及其所属的员工信息select * from emp right join dept on dept.id dept_id;# 注意哪个表放前面就先展示哪个表全外连接查询无论是否匹配成功两边表的数据都要全部显示# 需求查询所有员工与所有部门的对应关系select * from emp full join dept on dept.id dept_id;# 虽然不会报错但也不是我们想要的结果# mysql不支持全外连接# 我们可以将左外连接查询的结果和右外连接查询的结果做一个合并select * from emp left join dept on dept.id dept_idunionselect * from emp right join dept on dept.id dept_id;# union的用法select * from empunion allselect * from emp;# union将自动去除重复的记录# union all 不去除重复select sex,name from empunionselect * from dept;# 注意 union 必须保证两个查询结果列数相同一般用在多个结果结构完全一致时总结外连接查询查到的是没有对应关系的记录但是这样的数据原本就是有问题的所以最常用的还是内连接查询内连接表示 只显示匹配成功的记录外连接表示 没有匹配成功的也要显示多表查询案例create table stu(id int primary key auto_increment, name char(10));create table tea(id int primary key auto_increment, name char(10));create table tsr(id int primary key auto_increment,t_id int,s_id int,foreign key(t_id) references tea(id),foreign key(s_id) references stu(id));insert into stu values(null, 张三), (null, 李四);insert into tea values(null, nick), (null, jerry);insert into tsr values(null,1,1),(null,1,2),(null,2,2);# nick老师教过哪些人select tea.name, stu.name from tea join tsr join stu on tea.id t_id and stu.id s_id where tea.name nick;注意:在查询数据中,应该将上述知识合并使用,特别是子查询和内连接查询,如果面临多表联合查询,应该先理清楚步骤,一步一步实现,可以将一部分的查询结果搭建成一个临时表,再联合其他表进行查询.例如:# 查询平均成绩大于80分的同学的姓名以及平均成绩# 1. 根据学生id分组,查出所有平均成绩大于80的学生idselect student_id, avg(num) from score group by student_id having avg(num) 80;#2. 将查询结果作为一个临时表,如果要使用临时表的数据,就可以写上as a 然后后面就用 a.字段 去使用(select student_id, avg(num) from score group by student_id having avg(num) 80) as a ;#3. 将临时表数据与学生表连接select * from student join (select student_id, avg(num) from score group by student_id having avg(num) 80) as a ;#4. 进行一些筛选select * from student join (select student_id, avg(num) from score group by student_id having avg(num) 80) as a on sid a.student_id;
