概述
MySQL学习周报：学习MySQL的子查询语句以及创建表和修改表和删除表
1.子查询
1.1、什么是子查询？子查询都可以出现在哪里？
select语句当中嵌套select语句，被嵌套的select语句是子查询。
子查询可以出现在哪里？
select
…(select).
from
…(select).
where
…(select).
1.2、where子句中使用子查询
案例：找出高于平均薪资的员工信息。
select * from emp where sal > avg(sal); //错误的写法，where后面不能直接使用分组函数。
第一步：找出平均薪资
select avg(sal) from emp;
±------------+
| avg(sal) |
±------------+
| 2073.214286 |
±------------+
第二步：where过滤
select * from emp where sal > 2073.214286;
±------±------±----------±-----±-----------±--------±-----±-------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
±------±------±----------±-----±-----------±--------±-----±-------+
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
±------±------±----------±-----±-----------±--------±-----±-------+
第一步和第二步合并：
select * from emp where sal > (select avg(sal) from emp);
1.3、from后面嵌套子查询
案例：找出每个部门平均薪水的等级。
第一步：找出每个部门平均薪水（按照部门编号分组，求sal的平均值）
select deptno,avg(sal) as avgsal from emp group by deptno;
±-------±------------+
| deptno | avgsal |
±-------±------------+
| 10 | 2916.666667 |
| 20 | 2175.000000 |
| 30 | 1566.666667 |
±-------±------------+
第二步：将以上的查询结果当做临时表t，让t表和salgrade s表连接，条件是：t.avgsal between s.losal and s.hisal
select
t.*,s.grade
from
(select deptno,avg(sal) as avgsal from emp group by deptno) t
join
salgrade s
on
t.avgsal between s.losal and s.hisal;
±-------±------------±------+
| deptno | avgsal | grade |
±-------±------------±------+
| 30 | 1566.666667 | 3 |
| 10 | 2916.666667 | 4 |
| 20 | 2175.000000 | 4 |
±-------±------------±------+
案例：找出每个部门平均的薪水等级。
第一步：找出每个员工的薪水等级。
select e.ename,e.sal,e.deptno,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;
±-------±--------±-------±------+
| ename | sal | deptno | grade |
±-------±--------±-------±------+
| SMITH | 800.00 | 20 | 1 |
| ALLEN | 1600.00 | 30 | 3 |
| WARD | 1250.00 | 30 | 2 |
| JONES | 2975.00 | 20 | 4 |
| MARTIN | 1250.00 | 30 | 2 |
| BLAKE | 2850.00 | 30 | 4 |
| CLARK | 2450.00 | 10 | 4 |
| SCOTT | 3000.00 | 20 | 4 |
| KING | 5000.00 | 10 | 5 |
| TURNER | 1500.00 | 30 | 3 |
| ADAMS | 1100.00 | 20 | 1 |
| JAMES | 950.00 | 30 | 1 |
| FORD | 3000.00 | 20 | 4 |
| MILLER | 1300.00 | 10 | 2 |
±-------±--------±-------±------+
第二步：基于以上结果，继续按照deptno分组，求grade平均值。
select
e.deptno,avg(s.grade)
from
emp e
join
salgrade s
on
e.sal between s.losal and s.hisal
group by
e.deptno;
±-------±-------------+
| deptno | avg(s.grade) |
±-------±-------------+
| 10 | 3.6667 |
| 20 | 2.8000 |
| 30 | 2.5000 |
±-------±-------------+
1.4、在select后面嵌套子查询。
案例：找出每个员工所在的部门名称，要求显示员工名和部门名。
select
e.ename,d.dname
from
emp e
join
dept d
on
e.deptno = d.deptno;
select
e.ename,(select d.dname from dept d where e.deptno = d.deptno) as dname
from
emp e;
±-------±-----------+
| ename | dname |
±-------±-----------+
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCH |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | ACCOUNTING |
| SCOTT | RESEARCH |
| KING | ACCOUNTING |
| TURNER | SALES |
| ADAMS | RESEARCH |
| JAMES | SALES |
| FORD | RESEARCH |
| MILLER | ACCOUNTING |
±-------±-----------+
4、union （可以将查询结果集相加）
案例：找出工作岗位是SALESMAN和MANAGER的员工？
第一种：select ename,job from emp where job = ‘MANAGER’ or job = ‘SALESMAN’;
第二种：select ename,job from emp where job in(‘MANAGER’,‘SALESMAN’);
±-------±---------+
| ename | job |
±-------±---------+
| ALLEN | SALESMAN |
| WARD | SALESMAN |
| JONES | MANAGER |
| MARTIN | SALESMAN |
| BLAKE | MANAGER |
| CLARK | MANAGER |
| TURNER | SALESMAN |
±-------±---------+
第三种：union
select ename,job from emp where job = ‘MANAGER’
union
select ename,job from emp where job = ‘SALESMAN’;
±-------±---------+
| ename | job |
±-------±---------+
| JONES | MANAGER |
| BLAKE | MANAGER |
| CLARK | MANAGER |
| ALLEN | SALESMAN |
| WARD | SALESMAN |
| MARTIN | SALESMAN |
| TURNER | SALESMAN |
±-------±---------+
两张不相干的表中的数据拼接在一起显示？
select ename from emp
union
select dname from dept;
±-----------+
| ename |
±-----------+
| SMITH |
| ALLEN |
| WARD |
| JONES |
| MARTIN |
| BLAKE |
| CLARK |
| SCOTT |
| KING |
| TURNER |
| ADAMS |
| JAMES |
| FORD |
| MILLER |
| ACCOUNTING |
| RESEARCH |
| SALES |
| OPERATIONS |
±-----------+
mysql> select ename,sal from emp
-> union
-> select dname from dept;
ERROR 1222 (21000): The used SELECT statements have a different number of columns
5、limit (重点中的重点，以后分页查询全靠它了。)
5.1、limit是mysql特有的，其他数据库中没有，不通用。（Oracle中有一个相同的机制，叫做rownum）
5.2、limit取结果集中的部分数据，这时它的作用。
5.3、语法机制：
limit startIndex, length
startIndex表示起始位置，从0开始，0表示第一条数据。
length表示取几个
案例：取出工资前5名的员工（思路：降序取前5个）
select ename,sal from emp order by sal desc;
取前5个：
select ename,sal from emp order by sal desc limit 0, 5;
select ename,sal from emp order by sal desc limit 5;
5.4、limit是sql语句最后执行的一个环节：
select 5
…
from 1
…
where 2
…
group by 3
…
having 4
…
order by 6
…
limit 7
…;
5.5、案例：找出工资排名在第4到第9名的员工？
select ename,sal from emp order by sal desc limit 3,6;
±-------±--------+
| ename | sal |
±-------±--------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| ALLEN | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
±-------±--------+
5.6、通用的标准分页sql？
每页显示3条记录：
第1页：0, 3
第2页：3, 3
第3页：6, 3
第4页：9, 3
第5页：12, 3
每页显示pageSize条记录：
第pageNo页：(pageNo - 1) * pageSize, pageSize
pageSize是什么？是每页显示多少条记录
pageNo是什么？显示第几页
java代码{
int pageNo = 2; // 页码是2
int pageSize = 10; // 每页显示10条
limit (pageNo - 1) * pageSize, pageSize
}
6、创建表：
建表语句的语法格式：
create table 表名(
字段名1 数据类型,
字段名2 数据类型,
字段名3 数据类型,
....
);
关于MySQL当中字段的数据类型？以下只说常见的
int 整数型(java中的int)
bigint 长整型(java中的long)
float 浮点型(java中的float double)
char 定长字符串(String)
varchar 可变长字符串(StringBuffer/StringBuilder)
date 日期类型 （对应Java中的java.sql.Date类型）
BLOB 二进制大对象（存储图片、视频等流媒体信息） Binary Large OBject （对应java中的Object）
CLOB 字符大对象（存储较大文本，比如，可以存储4G的字符串。） Character Large OBject（对应java中的Object）
......
char和varchar怎么选择？
在实际的开发中，当某个字段中的数据长度不发生改变的时候，是定长的，例如：性别、生日等都是采用char。
当一个字段的数据长度不确定，例如：简介、姓名等都是采用varchar。
BLOB和CLOB类型的使用？
电影表: t_movie
id(int) name(varchar) playtime(date/char) haibao(BLOB) history(CLOB)
----------------------------------------------------------------------------------------
1 蜘蛛侠
2
3
表名在数据库当中一般建议以：t_或者tbl_开始。
创建学生表：
学生信息包括：
学号、姓名、性别、班级编号、生日
学号：bigint
姓名：varchar
性别：char
班级编号：int
生日：char
create table t_student(
no bigint,
name varchar(255),
sex char(1),
classno varchar(255),
birth char(10)
);
7、insert语句插入数据
语法格式：
insert into 表名(字段名1,字段名2,字段名3,…) values(值1,值2,值3,…)
要求：字段的数量和值的数量相同，并且数据类型要对应相同。
insert into t_student(no,name,sex,classno,birth) values(1,'zhangsan','1','gaosan1ban');
ERROR 1136 (21S01): Column count doesn't match value count at row 1
insert into t_student(no,name,sex,classno,birth) values(1,'zhangsan','1','gaosan1ban', '1950-10-12');
mysql> select * from t_student;
+------+----------+------+------------+------------+
| no | name | sex | classno | birth |
+------+----------+------+------------+------------+
| 1 | zhangsan | 1 | gaosan1ban | 1950-10-12 |
+------+----------+------+------------+------------+
insert into t_student(name,sex,classno,birth,no) values('lisi','1','gaosan1ban', '1950-10-12',2);
mysql> select * from t_student;
+------+----------+------+------------+------------+
| no | name | sex | classno | birth |
+------+----------+------+------------+------------+
| 1 | zhangsan | 1 | gaosan1ban | 1950-10-12 |
| 2 | lisi | 1 | gaosan1ban | 1950-10-12 |
+------+----------+------+------------+------------+
insert into t_student(name) values('wangwu'); // 除name字段之外，剩下的所有字段自动插入NULL。
mysql> select * from t_student;
+------+----------+------+------------+------------+
| no | name | sex | classno | birth |
+------+----------+------+------------+------------+
| 1 | zhangsan | 1 | gaosan1ban | 1950-10-12 |
| 2 | lisi | 1 | gaosan1ban | 1950-10-12 |
| NULL | wangwu | NULL | NULL | NULL |
+------+----------+------+------------+------------+
insert into t_student(no) values(3);
mysql> select * from t_student;
+------+----------+------+------------+------------+
| no | name | sex | classno | birth |
+------+----------+------+------------+------------+
| 1 | zhangsan | 1 | gaosan1ban | 1950-10-12 |
| 2 | lisi | 1 | gaosan1ban | 1950-10-12 |
| NULL | wangwu | NULL | NULL | NULL |
| 3 | NULL | NULL | NULL | NULL |
+------+----------+------+------------+------------+
drop table if exists t_student; // 当这个表存在的话删除。
create table t_student(
no bigint,
name varchar(255),
sex char(1) default 1,
classno varchar(255),
birth char(10)
);
insert into t_student(name) values('zhangsan');
mysql> select * from t_student;
+------+----------+------+---------+-------+
| no | name | sex | classno | birth |
+------+----------+------+---------+-------+
| NULL | zhangsan | 1 | NULL | NULL |
+------+----------+------+---------+-------+
需要注意的地方：
当一条insert语句执行成功之后，表格当中必然会多一行记录。
即使多的这一行记录当中某些字段是NULL，后期也没有办法在执行
insert语句插入数据了，只能使用update进行更新。
// 字段可以省略不写，但是后面的value对数量和顺序都有要求。
insert into t_student values(1,'jack','0','gaosan2ban','1986-10-23');
mysql> select * from t_student;
+------+----------+------+------------+------------+
| no | name | sex | classno | birth |
+------+----------+------+------------+------------+
| NULL | zhangsan | 1 | NULL | NULL |
| 1 | jack | 0 | gaosan2ban | 1986-10-23 |
+------+----------+------+------------+------------+
insert into t_student values(1,'jack','0','gaosan2ban');
ERROR 1136 (21S01): Column count doesn't match value count at row 1
// 一次插入多行数据
insert into t_student
(no,name,sex,classno,birth)
values
(3,'rose','1','gaosi2ban','1952-12-14'),(4,'laotie','1','gaosi2ban','1955-12-14');
mysql> select * from t_student;
+------+----------+------+------------+------------+
| no | name | sex | classno | birth |
+------+----------+------+------------+------------+
| NULL | zhangsan | 1 | NULL | NULL |
| 1 | jack | 0 | gaosan2ban | 1986-10-23 |
| 3 | rose | 1 | gaosi2ban | 1952-12-14 |
| 4 | laotie | 1 | gaosi2ban | 1955-12-14 |
+------+----------+------+------------+------------+
8、表的复制
语法：
create table 表名 as select语句;
将查询结果当做表创建出来。
9、将查询结果插入到一张表中？
mysql> insert into dept1 select * from dept;
mysql> select * from dept1;
±-------±-----------±---------+
| DEPTNO | DNAME | LOC |
±-------±-----------±---------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
±-------±-----------±---------+
10、修改数据：update
语法格式：
update 表名 set 字段名1=值1,字段名2=值2... where 条件;
注意：没有条件整张表数据全部更新。
案例：将部门10的LOC修改为SHANGHAI，将部门名称修改为RENSHIBU
update dept1 set loc = 'SHANGHAI', dname = 'RENSHIBU' where deptno = 10;
mysql> select * from dept1;
+--------+------------+----------+
| DEPTNO | DNAME | LOC |
+--------+------------+----------+
| 10 | RENSHIBU | SHANGHAI |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
| 10 | RENSHIBU | SHANGHAI |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
更新所有记录
update dept1 set loc = 'x', dname = 'y';
mysql> select * from dept1;
+--------+-------+------+
| DEPTNO | DNAME | LOC |
+--------+-------+------+
| 10 | y | x |
| 20 | y | x |
| 30 | y | x |
| 40 | y | x |
| 10 | y | x |
| 20 | y | x |
| 30 | y | x |
| 40 | y | x |
+--------+-------+------+
11、删除数据？
语法格式：
delete from 表名 where 条件;
注意：没有条件全部删除。
删除10部门数据？
delete from dept1 where deptno = 10;
删除所有记录？
delete from dept1;
怎么删除大表中的数据？（重点）
truncate table 表名; // 表被截断，不可回滚。永久丢失。
删除表？
drop table 表名; // 这个通用。
drop table if exists 表名; // oracle不支持这种写法。
最后
以上就是迷路雪碧为你收集整理的2020-10-24MySQL学习周报：学习MySQL的子查询语句以及创建表和修改表和删除表的全部内容，希望文章能够帮你解决2020-10-24MySQL学习周报：学习MySQL的子查询语句以及创建表和修改表和删除表所遇到的程序开发问题。
如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。
发表评论 取消回复