【MySQL】子查询、分页查询、表的创建、插入、删除、修改【附源码】

一、子查询

什么是子查询？
select 语句中嵌套select 语句，被嵌套在内部的select语句称为子查询语句。

子查询的注意事项：
① SQL允许多层子查询，即一个子查询还可以嵌套其他子查询；
② 子查询的select子句不能使用order by子句，order by子句只对最终结果进行排序；
③ 子查询根据子查询的查询条件是否依赖于父查询分为相关子查询和不相关子查询。

子查询使用的位置？
select
…(select)…
from
…(select)…
where
…(select)…

1.where子句中的子查询：

（1）带有比较运算符的子查询
例1：查询比最低工资高的员工姓名和工资

select ename,sal from emp where sal > (select min(sal) from emp);

（2）带有in谓词的子查询
例1：查询与“JAMES”相同工作的员工

select empno,ename,job from emp where job in (select job from emp where ename='JAMES');

（3）带有ANY（SOME）或ALL谓词的子查询
子查询返回单值是可以使用比较运算符，但返回多值时要用ANY（SOME）或ALL谓词修饰符，使用ANY（SOME）或ALL谓词时必须同时使用比较运算符。

>  ANY 	大于子查询结果中的某个值
>  ALL  大于子查询结果中的所有值
<  ANY 	小于子查询结果中的某个值
<  ALL 	小于子查询结果中的所有值
>= ANY  大于等于子查询结果中的某个值
>= ALL  大于等于子查询结果中的所有值
<= ANY 	小于等于子查询结果中的某个值
<= ALL 	小于等于子查询结果中的所有值
=  ANY 	等于子查询结果中的某个值
=  ALL 	等于子查询结果中的所有值（通常没有实际意义）
!=(或<>) ANY 不等于子查询结果中的某个值
!=(或<>) ALL 不等于子查询结果中的任何一个值

（4）带有EXISTS谓词的子查询

2.from字句中的子查询：

注意：from后面的子查询，可以将子查询的查询结果当做一张临时表。(技巧)
例：查询每个岗位的平均薪资的薪资等级

 select t.*,s.grade from (select job,avg(sal) as avg_sal from emp group by job) as t join salgrade s on t.avg_sal between s.losal and s.hisal;

3.select后面的子查询：

（了解即可）
对于select后面的子查询，只能返回一行数据，多余一行时报错。
例：查询每个员工对应的部门名称

 select e.ename,(select d.dname from dept d where d.deptno=e.deptno) as dname from emp e;

二、分页查询

1.limit：limit是将查询结果集的一部分取出来，通常使用在分页查询当中。
完整用法：limit startindex,length（startindex是起始下标，length是长度）
缺省用法：limit length（直接取出前length长度的数据，默认下标是0）

注意：limit只能用在order by排序操作的后面！！！否则没有意义。

例1：查询工资排在前五的员工，输出员工名和对应的工资

select ename,sal from emp order by sal desc limit 5;

例2：查询工资排在3-5名的员工的姓名和工资

select ename,sal from emp order by sal desc limit 2,3;

2.通用分页

每页显示3条记录
第1页:limit 0,3	[o 1 2]
第2页:limit 3,3	[3 4 5]
第3页:limit 6,3	[6 7 8]
第4页:limit 9,3	[9 10 11]
...
每页显示pageSize条记录，则第pageNo页: limit (pageNo - 1) * pageSize, pageSize
public static void main (string[] args){
/用户提交过来一个页码pageNo ，以及每页显示的记录条数pageSize
int pageNo = 5; //第5页
int pagesize - 10 ; //每页显示10条
int startIndex =(pageNo - 1) * pageSize;
String sql = "select ...limit " + startIndex + ", " + pageSize;
}

通用分页公式：limit (pageNo-1) * pageSize, pageSize

DQL执行顺序总结：
select结构：select…from…where…group by…having…order by…limit…
以上执行顺序：from—>where—>group by—>having—>select—>order by—>limit

三、MySQL中的数据类型

1.MySQL中的数据类型：
详解MySQL数据类型
此处只是常用的MySQL数据类型

varchar：可变长度的字符串
节省空间，会根据实际的数据长度动态分配空间。
优点:节省空间
缺点:需要动态分配空间，速度慢-
char：定长字符串
不管实际的数据长度是多少，分配固定长度的空间去存储数据。使用不恰当时,可能会导致空间的浪费。
优点:不需要动态分配空间,速度快。
缺点:使用不当可能会导致空间的浪费-
注意：varchar利char我们应该怎么选择?
性别字段你选什么﹖因为性别是固定长度的字符串，所以选择char.姓名字段你选什么﹖每一个人的名字长度不同，所以选择varchar.
int(默认11)：数字中的整数型。等同于java的int.
bigint：数字中的长整型。等同于java中的long
float：单精度浮点型数据
double：双精度浮点型数据
date：短日期类型（年月日，默认格式：%Y-%m-%d）
datetime：长日期类型（年月日时分秒，默认格式：%Y-%m-%d %h:%i:%s）
clob（Character Large OBject,字符大对象）：最多可以存储4G的字符串-
比如：存储一篇文章，存储一个说明。
超过255个字符的都要采用cLOB字符大对象来存储。
blob（Binary Large OBject，二进制大对象）：专门用来存储图片、声音、视频等流媒体数据。
向BLOB类型的字段上插入数据的时候，例如插入一个图片、视频等，你需要使用Io流才行。

四、表的操作

1.表的创建（create）

语法格式：create 表名(列名1 数据类型，列名2 数据类型，列名3 数据类型...);
（最后一个列名的数据类型后不需要“,”）

创建表使用create关键字，属于DDL语句（数据定义语言：create，drop，truncate，alter等）
表名和列名属于sql中的标识符，一律使用小写，多个单词之间使用_连接，应该做到见名知意。

例：创建一张名是t_student的学生表，其中的字段有学号，姓名，性别，年龄，邮箱

create table t_student(
id int primary key not null auto_increment,
name varchar(32),
sex char(1),
age int,
email varchar(50)
);

（1）快速复制表

将查询结果作为新建表的字段名和对应的值

create table t_student1 as select * from t_student;

（2）快速创建表

将查询到的部分表作为新表创建的字段名和值

create table t_student2 as select id,name,sex from t_student;

关于主键的说明

创建表时可以设置id主键，主键不是必须的，可以设置主键不为空，主键是自增；
若设置了主键，则在插入第一条数据时必须指定主键，主键在插入数据时不能重复；
若设置了主键，并且主键是自增的，除了第一条插入语句必须指明主键，后面的插入语句主键可以不写，sql会自增填充主键。

例：查询创建的t_student的结构

desc t_student;

2.表的插入（insert）

（1）插入一条数据

insert into t_student(id,name,sex,age,email) values (1,"赵敏","女",20,"zhaoming@126.com");

向表中插入数据时，若表设置了主键，则主键和前面的字段名中的主键值可以省略：

insert into t_student(name,sex,age,email) values ("张无忌","男",20,"zhangwuji@qq.com");

向表中插入数据时，前面的字段名可以省略（此时所有的列都要写上）：

insert into t_student value(4,"周芷若","女",19,"zhouzhiruo@139.com");

（2）插入多条数据

语法格式：insert into 表名(列名1，列名2...） values (值1，值2...)，(值1，值2...)...;

insert into t_student(id,name,sex,age,email) values 
(5,"张翠山","男",50,"cuishan@126.com"),
(6,"殷素素","女",36,"susu@126.com"),
(7,"珠儿","女",20,"zhuer@126.com");

（3）将查询结果插入表中

前提，查询到的列名和列数与要插入的表相同。
注意：插叙语句可以有where、order by等筛选条件。

insert into t_student select * from t_student1 ;

3.insert与select（date 日期）

（1）先创建一张t_user的表，含有date日期类型字段

create table t_user(id int,name varchar(32),birth date);

（2）向t_user中添加一条记录

insert into t_user values(2,"小吕","2002-5-25");

（3）查询用户的信息，将生日以“年.月.日”格式输出

select id,name,date_format(birth,"%Y.%m.%d") as birth from t_user;

4.insert与select（datetime 日期时间）

mysql提供的now()函数用于获取当前的系统时间，含有年月日时分秒信息

（1）创建一张带有date和datetime字段的表t_user

create table t_user(id int,name varchar(20),birth date,insert_time datetime);

（2）向表中插入一条数据

insert into t_user(id,name,birth,insert_time) values(1,"小王","2000-01-12",now());

（3）查询表中的数据

5.表的修改（update）

语法格式：update 表名 set 字段名1=值1,字段名2=值2... where 筛选条件;
（若没有筛选条件，表中所有的数据都会被修改）

update t_user set name="小何",birth="1999-02-25",insert_time=now() where id = 1;

6.删除（delete）

语法格式：delete from 表名 where 筛选条件;
（若没有筛选条件，整张表的数据都会被删除，只是删除数据，表结构还在）

delete from t_user where id = 1;

7.删除大表

（1）删除表中的数据（可恢复）

语法格式：delete from 表名;

delete from t_user;

delete语句删除数据的原理？（delete属于DM语句）
表中的数据被删除了，但是这个数据在硬盘上的真实存储空间不会被释放！
缺点：删除效率比较低。
优点：支持回滚（rollback;），可以再恢复数据!

（2）删除表中的数据（不可恢复）

语法格式：truncate table 表名;

truncate table t_user;

truncate语句删除数据的原理？（truncate属于DDL）
这种删除效率比较高，表被一次截断，物理删除。
缺点：不支持回滚。
优点：快速、高效。

（3）删除表

语法格式：drop table 表名;

drop table t_user;

8.修改表结构

对于表结构的修改使用alter关键字（属于DDL语句）
（1）删除一列
语法格式：aler table 表名 drop colum 列名;

alter table t_user drop email;

（2）添加一列
语法格式：alter table 表名 add 列名 数据类型;

alter table t_user add email varchar(20);

（3）修改字段的数据类型(表中该列不能有数据)
语法格式：alter table t_user alter column 列名 数据类型;

alter table t_user alter column email char(20);

（4）对表添加主键约束
语法格式：alter table 表名 add constraint primary key(列名）

alter table t_user add constraint  primary key(id);

（5）删除主键：
语法格式：alter table 表名 drop primary key;

alter table t_user drop primary key;

由于对表结构的操作并不多见，想了解全部：SQL语句修改表结构和添加约束