Mysql基本语句_查询语句__单表、多表、练习题
1、建 表建表语句：
createtable表名(字 段名1字符类型1(字符长度),字段名2 字符类型2(字符长度),字段名3字符类型3(字符长度))defaultcharset=utf8 ;
defaultcharset=utf8 ; 默认编码格式，当插入中文数值显示？？？解决
案例：createtablehz10(idint(10),name char(20),ageint(10))defaultcharset=utf8 ;
desc 表名 查看结构 ；
建表约束：
表中的限制条件
作用：表在的时候加入约束的目的是为了保证表中的记录完整和有效。
（1）非空约束 （not null） 非空，用于保证字段的值不能为空，
（2）唯一约束（unique） 保证字段值具有唯一性（不能重复），可以为空，唯一约束可以有多个
（3）主键约束（primarykey） 保证字段值具有唯一性（不能重复），不能为空 ，一个表中只有一个主键
（4）外键约束 （forrignkey） 两个表之间的主键建立关系
（5）默认 ：（default） 当字段不填数据，默认数据
（6）自增长 ：auto _increment 当添加一条数据，自动增长+1
（6.1)与主键约束一起使用，一般针对id
（6.2）每插入一条数据，指定的字段值+1
E:/noteyoudao/qqC00975CCA04FE5F25136875459BFDA42/25e74c384357479e9b5387d9b79fbd0c/clipboard.png
===============================================================================
drop table 表名 删除表 ；
例如：DROP table aa;
表字段：
添加字段：add
格式：altertable 表名add 字段名 字符类型（字符长度);
altertable hz10addfs int(10);
修改字段：change
格式：altertable 表名 change原字段名 新字段名 字符类型(字符长度);
altertable hz10changefstz int(10);
删除字段：drop
格式：altertable表名 drop 字段名 ;
altertable hz10 drop tz ;
重命名：rename
格式：altertable原表名 rename 新表名;
altertable hz10 rename hzdcs10 ;
调换字段顺序：modify after 在......后面
格式：altertable 表名 MODIFY移动的字段名 字段类型(字符长度)after 字段名 ;
altertable hzdcs10 MODIFYage int(10)after id ;
将字段添加到首行：
格式：altertable 表名 add字段名 字符类型(字符长度) first ;
altertable hzdcs10 addnoint(10) first ;
将字段添加到指定字段的后面：
格式：altertable 表名 add新字段名 字符类型(字符长度) AFTER 原表名 ;
altertable hzdcs10 addtzint(10) AFTER age ;
========================================================
表中插入数据：
（1）表中插入数据：insertinto 表名 values（插入值1，插入值2）
注意：插入的值，只有数值不需要加引号，字符要加上引号
如：INSERTinto hz10 VALUES(1,"文琪",18);
（2）表中插入数据：insertinto 表名 （id） values（插入值1）
INSERTinto hz10(id) VALUES(2);
========================================================
========================================================
2、单 表
查
（1）查询一个表中的所有数据
格式：select * from 表名；（——显示表格结果）
eg：select * from hz13;
注意：*号表示所有，每个语句后面加上分号
（2）查询表中表中具体的字段
格式：select 字段名1，字段名2，字段名3 FROM 表名；
eg：select name,age FROM hz13;
select name,ageFROM hz13 ;
（3）查询表中的具体字段取别名（别名是为了在查看数据是方便）
格式：select字段1as“姓名”字段2 “年龄”from表，名；
selectname as“姓名”age "年龄" fromhz13；
备注：as 可以省略不写，as表示取别名
（4）查询指定内容，用where+条件
格式：select* from 表名 where字段名=字段 ;
select* from emp wheredept2=101 ;
where条件使用注意：
比较运算符
（1）where+条件（=(等于)， ！=（不等于） ，<>（不等于） ,<（小于）,>（大于）,>=（大于等于）,<=（小于等于））
select* from emp wheredept2=101 ;
select* from emp wheredept2 !=101
select* from emp wheredept2 <>101 ;
select* from emp wheredept2 >102 ;
select* from emp wheredept2 <102 ;
select* from emp wheredept2 <=102 ;
select* from emp wheredept2 >=102 ;
（2）and （与）
and同时满足所有条件，比如：条件1和条件2都要满足
案例：select* from emp wheredept2 >=102and age=57 ;
（3）or（或）
or 当有多个条件时满足其中任意1个条件都显示，，条件1和条件同时存在，只要满足条件1就显示，只要满足条件2就显示。
select* from emp wheredept2 =102or age=55 ;
（4）between..... and....在什么范围之间
格式：select* from 表名where表字段 BETWEEN 值的范围 and 值的范围
select* from emp whereage BETWEEN 24 and55 ;
（5）in在一组数据中匹配数据
格式：select* from 表名 where 字段名 in (数组值1，数组值2，数组值3);
select* from emp wheredept2 in (101,102,110);
（6）isnull 为空或isnotnull非空
6.1 isnotnull非空
格式：select* from 表名 where字段名is not null ;
select* from emp whereageis not null ;
6.2 is null为空
格式：
select* from 表名 where字段名is null ;
多行注释：ctrl+/
取消多行注释：ctrl+shift+/
单行注释：直接#号
排序：
对表中的数据进行排序：orderby asc 升序（可以省略） desc 降序
升序：
格式：select* from 表名 orderby字段名 asc ;
案例1：select* from emp orderbyage asc ;
案例2：select* from emp orderbyage asc ;（不填asc）
降序：
格式：select*from 表名 orderby字段名 desc ;
案例：select* from emp orderbyage desc ;
二次排序：
格式：select* from 表名orderby 字段1 desc , 字段2 asc ;
案例：select* from emp orderbydept2 desc , incoming asc ;
模糊匹配查询like
%：表会匹配0个字符或多个字符
_:表示一个字符
select* from empwheredept2 like "1%"#匹配1开头的部门编号数据
select* from empwheredept2 like "%2%" #匹配的部门包含2编号数据
select* from empwheredept2 like "%2" #匹配结尾包含2编号数据
select* from empwheredept2 like "__2" #匹配固定的字符2编号数据
限制查询（limit）后面接连个值，第一个值表示下标（索引），第二值是步长
备注：一个表中索引是从0开始
格式：select* from 表名 limit索引,行数 ;
案例1：select* from emp limit2,3 ;
案例2：select* from emp limit2 ; 直接查询两行
分组查询 groupby
groupby 一般和having 组合
案例：SELECTdept2,max(age) from empgroupbydept2 ;
SELECTdept2,max(age) from empgroupbydept2 ;
SELECTdept2,max(age) from empgroupbydept2 ;
group by一般不会单独使用个，通常都是和函数组合使用。
groupby 后查询出来的结果，在需要的条件下可以接having
案例：SELECTdept2 ,max(age)ass from empgroupbydept2 HAVINGs>50 ;
having +条件和where+条件用法一样，但是场景不一样，一般group by 的后面接having
备注：函数求出以后，需要设置成别名，设置别名的字段，进行语句的操作。
错误案例：
（1）SELECTname,max(age) from emp
（2）SELECTname，max(age) from emp GROUP BYdept2
E:/noteyoudao/qqC00975CCA04FE5F25136875459BFDA42/32c13ff321b04e66a612ad739cbd8196/clipboard.png
sql语句函数：
max最大值
案例：SELECTdept2,max(age) from emp
min最小值
案例：SELECT min(age) from emp
avg 平均值
案例：SELECT avg(age) from emp
count统计总数
案例：SELECT count(age) from emp
sum求和
案例：SELECT sum(age) from emp
distinct去重
案例：SELECTdistinct(dept2) from emp
============================================================================
改：
update ......set 改
格式：update 表名set 修改的字段名=字段新值 where条件 ；
案例：update empset name="成全"wheresid=1789 ;
====================================
删：
delete 删
truncate 快速删除表内数据
drop删除
删除数据速度 ：drop>truncate>delete
NOTE：
1、drop是删除表和数据
2、truncate 删除无法恢复
3、delete删除是可以恢复
delete：
（1）删除表中所有数据
格式：deletefrom表名：
案例：deletefromhz13；
（2）删除表中指定条件的数据
格式： deletefrom表名where条件
案例： deletefromempwhere dept2=103;
（3）快速删除表数据
格式：truncate表名;
案例：truncatehz15 ;
====================================
表取别名：
格式： 表名设置别名，
案例：SELECTs.dept2,s.name from emp as s ;（把emp表设置成临时表s）
====================================
备份表结构：
格式：createtable新表名 like备份原表名 ;
createtablehz110 likeemp ;
备份数据：
insertinto 新表 有表结构 select * from 备份表的原表
案例：INSERTinto hz110select*fromemp ;
备份部分数据：
格式：INSERTinto表名(s字段名1,字段名2)selectsid ,dept2fromemp ;
INSERTinto hz110110(sid,dept2)selectsid ,dept2fromemp ;
备份表结构和数据:
格式：createtable 表名 as(select* from 备份原表 )
案例：createtablehzdcs110 as(select* from emp )
====================================
linux备份数据库：
备份用:>
格式：mysqldump -uroot -p 原数据库>数据库脚本.后缀名
案例：mysqldump -uroot -p hz10>/home/hz10bf.sql
E:/noteyoudao/qqC00975CCA04FE5F25136875459BFDA42/65e49d5bdb6d42a58e00952b6e48eec1/clipboard.png
还原数据库：
1、先建立一个新的空库
2、在linux中还原<
3、还原：mysql -u root-p 新建的空库<数据库备份好的数据脚本
===============================================================================================
3、多表之两表
def——什么是多表关联查询？
定义：查询数据来源于多张表。
主要讲：
1、内连接（基本内连接和隐藏内连接）
2、左连接（左外连接）
3、右连链（右外连接）
4、全链接（全外连接）
============================================================================
E:/noteyoudao/qqC00975CCA04FE5F25136875459BFDA42/1bd20c8d8bbf43738e2e9790042e9e9b/%25@al%5Bdf7o%7B0wn%60%60n48%7Dbbb5.jpg
============================================================================
1、讲解过程先建两个表 ：dept表 和 emp 表
dept表：
dept1部门编号
dept_name部门
E:/noteyoudao/qqC00975CCA04FE5F25136875459BFDA42/ba43113ccdf24029a4d9fe8ceeb205b8/cpj7vtl%60l@2ck%25935%246o8kp.png
emp表：
sid 员工编号
name 员工姓名
age 员工年龄
worktime_start 入职时间
incoming 员工收入
dept2 部门编号
E:/noteyoudao/qqC00975CCA04FE5F25136875459BFDA42/5993f24bc6cb41a28985103dd6e95f7f/clipboard.png
关联关系（即有关联的字段）：dept1 =dept2
============================================================================
1、笛卡尔积查询（了解）
根据两张表所乘得到的结果：比如：左边有4条数据，右边有6条数据，查询出来就是4*6=24条数据，这种查询数据包含大量的错误结果，通常不会使用这种查询。
格式：select * from 表1，表2；
案例：select * fromdept，emp；
2、内连接（普通内连接，隐藏内连接）：查询两个表共有的关联数据。
[*]2.1普通内连接：
格式：select *from表1 inner join 表2 on 表1.关联字段1=表2.关联字段2
案例1：select *fromdeptinner join emp on dept.dept1=emp.dept2 ;
案例2：select name,dept_namefromdeptinner join emp on dept.dept1=emp.dept2where sid=1789 ;
[*]2.2隐藏内连接
格式：select *from 表1，表2 where 表1.关联字段1=表2.关联字段2；
案例：select *fromdept,emp where dept.dept1=emp.dept2 ;
3、左连接：以左表为主（显示完整的左表），右表关联的数据就显示，没有的数据就以null显示
格式：select *from表1 leftjoin表2 on 表1.关联字段1=表2.关联字段2 ;
案例：select *fromdeptleftjoin emp on dept.dept1=emp.dept2 ;
4、右连接：右表是以右表数据为主（显示整个右表）左表有关联的数据就显示，没有就以null值显示。
格式：select *from表1right join表2 on 表1.关联字段1=表2.关联字段2 ;
案例：select*fromdeptright join emp on dept.dept1=emp.dept2 ;
5、左表独有数据：左表中独有的数据显示，（方法，左独有，以右表字段为空查询）
格式：select *from表1leftjoin表2 on 表1.关联字段1=表2.关联字段2 where 右表字段isnull
案例：select *fromdeptleftjoin emp on dept.dept1=emp.dept2 where name isnull;
6、右表独有数据：右表中独有的数据显示，（方法，右独有，以左表字段为空查询）
格式：select *from表1 right join表2 on 表1.关联字段1=表2.关联字段2 where 左表字段isnull
案例：select *fromdeptrightjoin emp on dept.dept1=emp.dept2 wheredept1isnull;
7、左表独有数据+右表独有数据：
union拼接
左独有union右独有
select *fromdeptleftjoin emp on dept.dept1=emp.dept2 where name isnull
union
select *fromdeptrightjoin emp on dept.dept1=emp.dept2 wheredept1isnull;
8、全连接
方法一：左独有+右独有+内连接
select *fromdeptleftjoin emp on dept.dept1=emp.dept2 where name isnull
union
select *fromdeptrightjoin emp on dept.dept1=emp.dept2 wheredept1isnull
union
select *fromdept innerjoinemp on dept.dept1=emp.dept2 ;
方法二：左连接+右独有
select *fromdeptleftjoin emp on dept.dept1=emp.dept2 UNION
select *fromdeptrightjoin emp on dept.dept1=emp.dept2 wheredept1isnull ;
方法三：右连接+左独有
select *fromdeptright join emp on dept.dept1=emp.dept2 UNION
select *fromdeptleftjoin emp on dept.dept1=emp.dept2where name isnull ;
总结：
普通内连接：
select*from表1 innerjoin 表2 on 表1 的关联字段=表2的关联字段 ；
隐藏内连接：
select*from表1 ，表2where 表1 的关联字段=表2的关联字段 ;
左连接:
select*from表1 left join 表2on 表1 的关联字段=表2的关联字段 ；
右连接：
select*from表1rightjoin 表2on 表1 的关联字段=表2的关联字段 ；
左独有数据：
select*from表1left join 表2on 表1 的关联字段=表2的关联字段 where右表字段为空；
右独有数据：
select*from表1right join 表2on 表1 的关联字段=表2的关联字段 where 左表字段为空；
全外连接：
1、左独有+右独有+内连接
2、左连接+右独有
3、右连接+左独有
mysql 之子查询
定义：一个查询嵌套另一个查询
请问：求出IT技术部门的员工姓名和员工编号？
答：IT部门dept中的dept_name
name sid emp表
内连接（先合表在根据条件查询）：select name,sidfromdeptinnerjoin empondept1=dept2where dept_name="IT技术";
子查询案例：selectname,sid from empwhere dept2=(selectdept1from deptwhere dept_name="IT技术" )
子查询分类：
1、标量子查询
2、列子查询
3、行子查询
4、表子查询
=======================================================
1、标量子查询（返回一个值）：
标量子查询 把一个sql执行返回的一个值，作为另一sql执行的一个条件，得到的结果是一行一列，一般出现在where后面
E:/noteyoudao/qqC00975CCA04FE5F25136875459BFDA42/1cc015b7afb9458491aae11f3451a6f7/sz%7D%25kpoh9f7gtk8%28%5Bzvd5f2.png
注意点：标量子查询允许使用符号：=、！=、>、<、>=、<=、<>(不等于)
========================================================================
2、列子查询（返回一个列）
定义：返回的是一列值
注意：通常在where 条件后使用，in或not in ，不能使用=,<，>等比较运算符。
案例： selectdept1from deptwhere dept_name="财务"or dept_name="IT技术" ;
selectname,sid from empwhere dept2in (selectdept1from deptwhere dept_name="财务"or dept_name="IT技术")
E:/noteyoudao/qqC00975CCA04FE5F25136875459BFDA42/cfc59b138f4d4b4aa24c8c7b6aed345c/30289cddac49.png
E:/noteyoudao/qqC00975CCA04FE5F25136875459BFDA42/737a744e75cb482fb3573b5f63519e07/9f329a548d9f.png
========================================================================
3、行子查询：（返回一行多列）
行子查询返回的结果集是一行多列，一般出现在where后面
案例：selectage,incoming fromemp where name="马十二" ;
select* from empwhere (age,incoming)=any(selectage,incoming fromemp where name="马十二" )
或
select* from empwhere (age,incoming)in(selectage,incoming fromemp where name="马十二" )
E:/noteyoudao/qqC00975CCA04FE5F25136875459BFDA42/e5fca752ad3a460a96aa4a2d11be4347/3cc9d0a278d3.png
E:/noteyoudao/qqC00975CCA04FE5F25136875459BFDA42/5d2756a83cf644be82c86d82085804e7/57bcdd52acb9.png
========================================================================
4、表子查询（重点）（返回一个表）
一般都是接from后面
select * fromdept,emp where dept1=dept2 ;
select name,sid from( select * fromdept,emp where dept1=dept2)s where age=64;
临时表：给表取一个别名 聚合函数 最好取别名
===================================================
案例：求财务部门的收入总和；
解法一：
子查询方法：selectsum(incoming) fromempwhere dept2=(selectdept1fromdept where dept_name="财务") ;
解法二：
普通内连接：SELECT SUM(incoming) FROM dept inner joinempondept.dept1=emp.dept2 WHERE dept_name="财务";
解法三：
表子查询：select sum(s.incoming) from(select*fromemp ,deptwhere dept1=dept2)s where dept_name="财务" ;
练习题：
create table dept(
dept1 int(10),
dept_name char(20)
) default charset=utf8;
insert into dept values (101,'财务');
insert into dept values (102,'销售');
insert into dept values (103,'IT技术');
insert into dept values (104,'行政');
SELECT * fromdept;
create table emp(
sid intprimary key, -- 员工编号
namevarchar(10), -- 员工姓名
age int, -- 员工年龄
worktime_start date, -- 入职时间
incoming double, -- 收入
dept2 int,-- 部门号
foreign key (dept2) references dept(dept2)) defaultcharset=utf8;-- 关联dept表
-- alter table emp add foreign key (deptno) references dept(deptno);
insert into emp values(1789,'张三',35,"1980-01-01",4000,101);
insert into emp values(1674,'李四',32,'1983-04-01',3500,101);
insert into emp values(1776,'王五',24,'1990-07-01',2000,101);
insert into emp values(1568,'赵六',57,'1970-10-11',7500,102);
insert into emp values(1564,'荣七',64,'1963-10-11',8500,102);
insert into emp values(1879,'牛八',55,'1971-10-20',7300,103);
select * from emp;
Q1.列出每个部门的平均收入及部门名称;
核心词汇：emp、incoming 、 deptdept_name 、avg 、group by、
selectavg(incoming ),dept_name from dept inner joinempondept.dept1=emp.dept2.
方法一：selectavg(incoming ),dept_name from (select * from dept left joinempondept.dept1=emp.dept2)c group by dept_name;
方法二：select avg(incoming),dept_name from dept leftJOIN emp on dept.dept1=emp.dept2 GROUP BY dept_name ;
Q2.财务部门的收入总和；
dept "财务"
emp incoming
sum
selectsum(incoming ),dept_name from dept inner joinempondept.dept1=emp.dept2where dept_name="财务" ;
方法一：select sum(incoming) from (select *fromdept,emp where dept.dept1=emp.dept2)c where dept_name='财务';
方法二：selectsum(incoming ) from dept inner joinempondept.dept1=emp.dept2where dept_name="财务" ;
方法三：select sum(incoming) fromemp where dept2=(selectdept1fromdeptwheredept_name="财务");
Q3.It技术部入职员工的员工号
emp sid
dept it技术部门
name ，sid
方法一：select emp.sid from dept inner join emp on dept.dept1=emp.dept2 where dept_name="IT技术 ";
方法二：select sid from (select * from dept,emp where dept.dept1=emp.dept2)c where dept_name='IT技术';
方法三：selectsidfromempwhere dept2=(selectdept1fromdept where dept_name="IT技术") ;
方法四：selectsid fromdept,empwhere dept1=dept2anddept_name="IT技术" ;
Q4.财务部门收入超过2000元的员工姓名
emp incoming>2000
dept财务
方法一：SELECT name,incoming FROM Dept INNER JOIN Emp on Dept1=DEPT2 WHERE incoming>2000 AND Dept_name="财务";
方法三：select name from (select * from dept,emp where dept.dept1=emp.dept2)c where incoming>2000 and dept_name= '财务';
方法四：select name from (select*from dept inner join emp on dept1=dept2 where incoming>2000)s where dept_name='财务'
Q5.找出销售部收入最低的员工的入职时间；
emp
dept
max
方法一：select name,woektime_start from (select *,min(incoming) from dept inner join emp on dept1=dept2 group by dept_name)s where dept_name='销售'
方法二：select woektime_start FROM emp INNER JOIN dept on dept.dept1=emp.dept2 whereincoming =(select min(incoming) from emp INNER JOIN dept on emp.dept2=dept.dept1 where dept.dept_name="销售")and dept_name="销售";
方法三：（这种方法可以，弊端在与有重复的数据，只显示一条）
selecta.dept_name,b.sid,b.name,b.woektime_startfrom dept ajoin emp b ona.dept1 =b.dept2 where a.dept_name = "销售"ORDER BY b.incoming asc LIMIT 1;
Q6.找出年龄小于平均年龄的员工的姓名，ID和部门名称
思路：条件n：ame，sid ，dept_name
方法一:SELECT name,sid,dept_name FROM emp INNER JOIN dept ON dept.dept1 = emp.dept2 where age< (SELECT AVG(age) FROM emp);
方法二：
select sid,name,dept_name from (select * from emp,dept where dept1=dept2)s where age<(select avg(age)from emp);
Q7.列出每个部门收入总和高于9000的部门名称
条件： groupby dept_name
sum (incoming) >9000
结果：dept_name
方法一：
select s.dept_name from (select a.dept_name,sum(b.incoming) as k from dept a join emp b on a.dept1 = b.dept2GROUP BYa.dept_name HAVING k >9000)s;
方法二：
SELECT dept_name FROM dept INNER JOIN emp on dept.dept1=emp.dept2GROUP BY dept_name having sum(incoming)>90000;
方法三：
SELECT dept_name FROM ( SELECT sum( incoming ) a, dept_name FROM emp INNER JOIN dept ON dept.dept1 = emp.dept2 GROUP BY dept_name ) b
WHERE a > 9000;
方法四：SELECT dept_name from (SELECT sum(incoming) as s,dept2 from emp GROUP BY dept2 having s>9000)t1on dept.dept1=t1.dept2;
Q8.查出财务部门工资少于3800元的员工姓名
dept_name =财务 dept
incoming<3800 emp
name
方法一：
SELECT name FROM dept INNER JOIN emp on dept.dept1=emp.dept2where incoming<3800and dept_name="财务";
方法二：
select name from emp where incoming<3800 anddept2=(SELECT dept1 from dept WHERE dept_name='财务');
方法三
select name from (select*from dept,emp where dept1=dept2)s where incoming<3800 and dept_name="财务"
Q9.求财务部门最低工资的员工姓名；
条件
min
dept表 dept_name=财务
emp 表 min（ incoming ）
结果：员工姓名name
方法一：
select namefrom dept inner join emp on dept.dept1=emp.dept2 where
dept2=(select dept1 from dept where dept_name="财务" ) and incoming=(select min(incoming) from emp ,dept where emp.dept2=dept.dept1 anddept_name="财务");
方法二：
selectnamefrom emp ,deptwheredept1=dept2anddept_name="财务"ORDER BY incoming ascLIMIT 1
Q10.找出销售部门中年纪最大的员工的姓名
条件：dept表 dept_name="销售"
emp max（age）
结果：name
方法一:select name "姓名", age "年龄" from dept,emp where dept.dept1=emp.dept2 and
age=(select max(age) from dept,emp where dept.dept1=emp.dept2 and dept_name="销售")and dept_name="销售";
方法二：（缺陷是多个同样数据就显示一个）
SELECT name,agefrom emp INNER JOIN dept on emp.dept2=dept.dept1WHERE dept_name="销售"ORDER BYagedescLIMIT 0,1;
Q11.求收入最低的员工姓名及所属部门名称：
条件：emp min（incoming ）
结果：name，dept_name
方法一：
SELECT NAME,dept_name FROM dept inner joinempondept.dept1=emp.dept2 WHERE incoming=(SELECT MIN(incoming) FROM emp );
方法二：
SELECT name ,dept_name FROM(SELECT * FROM emp INNER JOIN dept ON dept.dept1 = emp.dept2)a WHERE incoming =( SELECT MIN(incoming) FROM emp);
方法三：SELECT name,dept_name fromemp left join dept on emp.dept2=dept.dept1 where incoming=(SELECT min(incoming) from emp left join dept on emp.dept2=dept.dept1);
Q12.求李四的收入及部门名称
条件：emp name=“李四”
结果：incoming dept_name
方法一：
SELECT incoming,dept_name FROM dept INNER JOIN emp on dept.dept1=emp.dept2where name="李四"
方法二：
select name,dept_name from(select * from dept,emp where dept.dept1=emp.dept2)c where name='李四';
方法三：select incoming,dept_name from (select*from dept,emp where dept1=dept2)s where name="李四";
Q13.求员工收入小于4000元的员工部门编号及其部门名称
条件：
emp incoming<4000
结果：dept1 ,deptname
方法一：select dept1,dept_name from(select * from dept,emp where dept.dept1=emp.dept2)cwhere incoming<4000;
方法二：select dept1,dept_name from emp left join dept on emp.dept2=dept.dept1 where incoming<4000;
方法三：select dept1,dept_name from deptwheredept1 in (selectdept2fromempwhereincoming<4000) ;
Q14.列出每个部门中收入最高的员工姓名，部门名称，收入，并按照收入降序；
方法一：（缺陷：重复就显示一个）
SELECT name "姓名",incoming "收入",dept_name"部门" from(select * from dept,emp where dept.dept1=emp.dept2 order by incoming desc) b group bydept_nameORDER BYincoming desc;
方法二：
SELECT name "姓名",incoming "收入",dept_name"部门" from (select * from dept,emp where dept.dept1=emp.dept2)a where
方法三:
selectb.name,a.dept_name, b.incomingfrom(select max(incoming) as c,dept_name from dept, empwheredept1=dept2 groupby dept_name)a LEFT JOIN ( select* fromemp ,deptwheredept1=dept2)bona.dept_name=b.dept_nameanda.c =b.incoming orderbyb.incoming desc;
Q15.求出财务部门收益最高的俩位员工的姓名，工号，收益
条件： dept dept_name="财务" max（incoming） limit 2
结果 ：emp name sid incoming
方法一：select name,sid,incoming from emp inner join dept on dept1=dept2 where dept_name='财务' order by incoming desc limit 2;
方法二：select * from (select name,sid,incoming from dept left join emp on dept.dept1=emp.dept2 where dept_name="财务" order by incoming desc)as s where sid limit 0,2
方法三: select a.dept1,a.dept_name,b.name,b.incoming from dept a ,emp b wherea.dept1 = b.dept2and a.dept_name ='财务'ORDER BYb.incoming desc LIMIT 2;
Q16.查询财务部低于平均收入的员工号与员工姓名：（按所有的平均薪资）
条件： dept dept_name=“财务” avg（incoming） >
结果：sid name
方法一：
select sid,name fromempwhere incoming<(select avg(incoming)from emp) and dept2=(select dept1 from dept where dept_name='财务');
方法二：
SELECT sid,name FROM dept INNER JOIN emp on dept.dept1=emp.dept2 WHERE incoming<(SELECT avg(incoming) FROM emp)and dept_name="财务";
备注（在多表中求得平均值）SELECT sid,name fromemp left join dept on emp.dept2=dept.dept1 where dept_name="财务" and incoming<(SELECT avg(incoming) from emp left join dept on emp.dept2=dept.dept1)
方法三：
select sid,namefrom (select * from dept,emp where dept.dept1=emp.dept2)cwhere incoming<(select avg(incoming) from emp) and dept_name='财务';
Q17.列出部门员工数大于1个的部门名称；
条件：dept count（name）
结果：dept_name
方法一：select dept_name from dept where dept1 in(select dept2 from emp group by dept2 having count(dept2)>1)
方法二：SELECT DISTINCT(dept_name) FROM empINNER JOIN dept ON emp.dept2 = dept.dept1WHERE (SELECT count(name) from emp)>1;
方法三：SELECT dept_name from(SELECTdept_name,count(name)s fromdept INNER JOIN emp on dept1=dept2 GROUP BY dept_name having s>1)d;
Q18.列出部门员工收入不超过7500，且大于3000的员工年纪及部门编号；
条件：emp incoming<=750 and incoming>3000
结果：age ,dept_name
方法一：select age, dept2 from emp where incoming >3000 and incoming<=7500;
方法二：
select age,dept2from (select * from dept,emp where dept.dept1=emp.dept2)cwhere 7500>=incoming and incoming>3000;
方法三：SELECT name,age,dept1FROMdept inner JOIN emp on dept.dept1=emp.dept2 WHERE incoming BETWEEN 3000 and 7500andincoming!=3000;
错误写法：
SELECT name,age,dept1FROM dept inner JOIN emp on dept.dept1=emp.dept2 WHERE incoming BETWEEN 3000 and 7500;（包含3000了）
Q19.求入职于20世纪70年代的员工所属部门名称；
条件：20世纪 表示1900-1999内， 70年代 ：大于等于1970~小于1980 woerktime =“197%”
结果： dept dept_name
方法一：select dept_name from emp left join dept on emp.dept2=dept.dept1 wherewoektime_start >="19700101" and woektime_start<"19800101";
方法二：SELECT name,dept_name FROMdept RIGHT JOIN emp on dept.dept1=emp.dept2WHERE worktime BETWEEN "1970-01-01" and "1979-12-31";
方法三：select dept_name "部门"from dept,emp wheredept.dept1=emp.dept2 andwoektime_start like "197%" ;
方法四：select dept_name from emp inner join dept on dept1=dept2 wherewoektime_start >="19700101" and woektime_start<="19791231";
Q20.查找张三所在的部门名称；
条件： emp name=“张三”
结果：dept dept_name
方法一：
select dept_name from emp inner join dept on dept1=dept2 where name='张三';
方法二：
select dept_name from dept where dept1=(select dept2 from emp where name='张三');
方法三：
select dept_name "部门" from dept,emp where dept2=dept1 and name="张三";
Q21.列出每一个部门中年纪最大的员工姓名，部门名称；
条件：group by dept_name max(age)
结果：name，dept_name
方法一：sel ect name "姓名",dept_name "部门名" from dept,emp where dept1=dept2 and
(dept_name,age) in (select dept_name,max(age) from dept,emp where dept1=dept2 group by dept_name )；
方法二：
selectb.name,a.dept_name, b.incomingfrom(select max(age) as c,dept_name from dept, empwheredept1=dept2 groupby dept_name)a LEFT JOIN ( select* fromemp ,deptwheredept1=dept2)bona.dept_name=b.dept_nameanda.c =b.age
方法三：（是一种方法，当出现多条相同数据，就显示一条）
select name,dept_name from (select * from emp inner join dept on dept.dept1=emp.dept2 order by age desc) a group by dept_name ;
Q22.列出每一个部门的员工总收入及部门名称；
条件: groupby dept_name
结果;sum(incoming) dept_name
方法一：SELECT sum(incoming),dept_name FROMdept leftJOIN emp on dept.dept1=emp.dept2 GROUP BY dept_name
方法二：select sum(incoming),dept_name from dept left join emp on dept.dept1=emp.dept2 group by dept1;
方法三：（不合理的写法，答案正确）
SELECT sum(incoming),dept_name FROM dept iNNER JOIN emp on dept.dept1=emp.dept2 WHERE (SELECT sum(incoming) FROM emp ) GROUP by dept_name;
方法四：select dept_name,sum(incoming)from (select * from dept,emp where dept.dept1=emp.dept2)cgroup by dept_name；
Q23.列出部门员工收入大于7000的员工号，部门名称；
条件： dept dept_name
emp incoming>7000 sid
结果： sid dept_name
方法一：SELECT sid,dept_name FROM dept left JOIN emp on dept1=dept2 where incoming>7000
方法二：select a.sid,a.dept_name from(select * from emp inner join dept on emp.dept2=dept.dept1 where incoming>7000) a;
方法三：select sid,dept_namefrom (select * from dept,emp where dept.dept1=emp.dept2)c where incoming>7000;
Q24.找出哪个部门还没有员工入职；
条件：dept_name name或sid 判断
结果：dept_name
方法一：
select dept_namefrom dept left join emp on dept.dept1=emp.dept2 group by dept1 having (count(sid)=0)
方法二：select * from dept where dept1 not in ( select dept2 from emp);
方法三：select dept_name fromemp right join dept on dept1=dept2 where name is null;
方法四：select dept_name from dept left join emp on dept.dept1=emp.dept2where name is NULL;
方法五：SELECT dept_name FROM (SELECT * FROM emp RIGHT JOIN dept ON emp.dept2 = dept.dept1)a WHEREsid is NULL;
Q25.先按部门号大小排序，再依据入职时间由早到晚排序员工信息表 ；
条件：二次排序 orderby desc从大到小 早到晚asc
结果：所有信息
方法一：
SELECT * fromdept left join emp on dept.dept1=emp.dept2 ORDER BY dept1 desc,woektime_start asc ;
方法二：（部门编号104不显示，内连接方法）
select * fromdept a ,emp b where a.dept1 = b.dept2 ORDER BYa.dept1 desc,b.woektime_start asc;
方法三：SELECT * from empORDER BY dept2 DESC ,woektime_start asc;
Q26.求出财务部门工资最高员工的姓名和员工号
条件： dept_name ="财务" max（incoming）
结果：name，sid
方法一：SELECT name,sidfrom dept left join emp on dept.dept1=emp.dept2 where incoming=(SELECT max(incoming) from dept left join emp on dept.dept1=emp.dept2 where dept_name="财务")and dept_name="财务" ;
方法 二：select name, sid from emp inner join dept on dept1= dept2 where dept_name='财务' order by incoming desc limit 1
方法三：SELECT name,sid FROM dept,emp WHERE dept.dept1=emp.dept2 AND (SELECT max(incoming) from (SELECT incoming from dept a ,emp b where a.dept1=b.dept2 and dept_name="财务")s )=incominganddept_name="财务";
方法四：（缺陷有重复数据）
select name,sid FROM dept d INNER JOIN emp e on d.dept1=e.dept2 WHERE dept_name="财务" AND incoming in(SELECT MAX(incoming) FROM emp GROUP BY dept2);
方法五：SELECT name,dept1FROM emp INNER JOIN dept ON emp.dept2 = dept.dept1 WHERE dept_name='财务'ORDER BY incoming DESCLIMIT 0,1;
Q27.求出工资在7500到8500之间，年龄最大的员工的姓名和部门名称。
条件：emp incming max ( age)
结果：name dept_name
方法一：
select name,dept_name from dept left join emp on dept.dept1=emp.dept2 where age=(SELECT max(age) from dept left join emp on dept.dept1=emp.dept2 WHERE incoming between 7500 and 8500 )andincoming between 7500 and 8500 ;
方法二:
SELECT name,dept_name from (SELECT * from emp INNER JOIN dept on emp.dept2=dept.dept1 ORDER BY age desc) a where incoming>=7500 and incoming<=8500 LIMIT 1;
方法三：
SELECT name,dept_name FROM (SELECT * FROM dept inne JOIN emp on dept1=dept2 where incoming between 7500 and 8500)b WHERE age=(SELECT max(age) FROM (SELECT * FROM dept inne JOIN emp on dept1=dept2 where incoming between 7500 and 8500)b)
方法四：
SELECT name,dept_name FROM (SELECT * FROM dept inne JOIN emp on dept1=dept2 where incoming between 7500 and 8500)b ORDER BY age desc LIMIT 1
=================================================================================================================================
4、多表之三表
以练习题为例讲解：
表结构：
表一：Student学生表（学号、姓名、性别、年龄、编辑）
表二：Course课程表（编号、课程名称）
表三：sc选课表（选课编号、学号、课程编号、成绩）
Question：
（1）写一个SQL语句，查询选修了“计算机原理”的学生学号和姓名
（2）写一个SQL语句，查询“小明”同学选修的课程名称
（3）写一个SQL语句，查询选修了5门课程的学生学号和姓名
create table student(
stu_no int,
stu_name varchar(10),
sex char(1),
age int(3),
edit varchar(20) )
DEFAULTcharset=utf8;
insert into student values
(1,'wang','男',21,'hello'),
(2,'小明','女',22,'haha2'),
(3,'hu','女',23,'haha3'),
(4,'li','男',25,'haha4');
create table course(
c_no int,
c_name varchar(10)）
DEFAULTcharset=utf8;
insert into course values
(1,'计算机原理'),
(2,'java'),
(3,'c'),
(4,'php'),
(5,'py');
#roptable sc;
create table sc(
sc_no int,
stu_no int,
c_no int,
score int(3))
DEFAULTcharset=utf8;
insert into sc values
(1,1,1,80),
(2,2,2,90),
(3,2,1,85),
(4,2,3,70),
(5,2,4,95),
(6,2,5,89);
=================================================================================================================================
select * from student ;
select * from course ;
select* from sc ;
=================================================================================================================================
file:///E:/noteyoudao/qqC00975CCA04FE5F25136875459BFDA42/dc2bfb5be6fc4842bf7e6c11813be1e3/clipboard.png
file:///E:/noteyoudao/qqC00975CCA04FE5F25136875459BFDA42/9aacc30c07fd4de59946839bce27f2c8/clipboard.png
file:///E:/noteyoudao/qqC00975CCA04FE5F25136875459BFDA42/acae6802bc05490fbcef63e6e1802127/clipboard.png
Student 学生表（学号、姓名、性别、年龄、编辑） asa
Course 课程表（课程编号、课程名称） as b
sc 选课表（选课编号、学号、课程编号、成绩）as c
=================================================================================================================================
三表连接
方法一：三表隐藏内连接
格式：select*from 表1 , 表2 ，表3 where 表1.关联的字段=表3.关联字段and 表2.关联字段=表3.表3字段
select*fromstudent as a , course as b , scasc wherea.stu_no=c.stu_no and b.c_no=c.c_no
方法二：三表普通内连接
格式：select*from表1 INNER JOIN表3 on 表1.关联的字段=表3.关联字段 inner join表2on 表2.关联字段=表3.表3字段
select*fromstudent as a INNER JOINscasc on a.stu_no=c.stu_noinner joincourse asbon b.c_no=c.c_no
方法三：三表普通左连接
格式：select*from表1 left JOIN表3 on 表1.关联的字段=表3.关联字段 left join 表2on 表2.关联字段=表3.表3字段
select*fromstudent as a left JOINscasc on a.stu_no=c.stu_noleft joincourse asbon b.c_no=c.c_no
方法四：三表普通右连接
格式：select*from表1 rightJOIN表3 on 表1.关联的字段=表3.关联字段 right join 表2on 表2.关联字段=表3.表3字段
select*fromstudent as a right JOINscasc on a.stu_no=c.stu_noright joincourse asbon b.c_no=c.c_no
方法五：先合两表，在两表合并成一表与第三个表合并
selects.stu_no ,s.stu_name from ( selecta.stu_no ,stu_name,c_nofromstudent a,sc c wherea.stu_no=c.stu_no )s,courseas bwheres.c_no=b.c_no
=================================================================================================================================
Question：（1）写一个SQL语句，查询选修了“计算机原理”的学生学号和姓名
条件： c_name =“计算机原理” course表
结果：stu_no ， name student表
方法一： 普通内连接
select a.stu_no, a.stu_name from student a join sc c on a.stu_no = c.stu_no join course b on c.c_no = b.c_nowhere b.c_name = "计算机原理"
Question：（2）写一个SQL语句，查询“小明”同学选修的课程名称
方法一：
selectb.c_namefromstudent1asa , courseasb , scascwherea.stu_no=c.stu_noandb.c_no=c.c_noanda.stu_name='小明';
方法二：
select b.c_namefrom（select c_no,stu_name from student a inner join sc as c on a.stu_no=c.stu_no)s inner join course as b on s.c_no=b.c_nowhere s.stu_name=‘小明’；
方法三：
SELECT c_name from Student as a, course as b, sc as cwherea.stu_no=c.stu_noand b.c_no=c.c_noandstu_name="小明";
Question：（3）写一个SQL语句，查询选修了5门课程的学生学号和姓名
方法一：
select student.stu_no,stu_name from student,course,sc where student.stu_no=sc.stu_no and sc.c_no=course.c_no group by student.stu_no having count(*)=5；
方法二：selecta.stu_no,a.stu_name from student1as a,course as b, scas c wherea.stu_no= c.stu_no and b.c_no=c.c_no group by a.stu_no having count(c_name)=5;
页:
[1]