MySQL UPDATE 子查询报错 mysql子查询更新

Mysql——子查询、分页查询、表的增删改

• 子查询

• where语句中使用子查询
• from后嵌套子查询
• select后嵌套子查询

• Union
• limit 分页查询
• 表

• 创建表
• insert 增加数据
• update 更新数据
• delete 删除数据

子查询

子查询就是嵌套的select语句，可以理解为子查询是一张表。
select语句当中嵌套select语句，被嵌套的select语句是子查询。

select
  …(select).
from
  …(select).
where
  …(select).

where语句中使用子查询

案例：找出高于平均薪资的员工信息

#第一步：找出平均薪资
mysql> select avg(sal) from emp;
#第二步：使用where嵌套查询
mysql> select * from emp where sal > (select avg(sal) from emp);

from后嵌套子查询

案例：找出每个部门平均薪水的等级。

#第一步：找出每个部门平均薪水（按照部门编号分组，求sal的平均值）
mysql> select deptno,avg(sal) as avgsal from emp group by deptno;

#第二步：将以上的查询结果当做临时表t，让t表和salgrade s表连接（上一步的语句作为一张表）
mysql> 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 hisal;

案例：找出每个部门平均的薪水等级。

#第一步：找出每个员工的薪水等级。
mysql> select e.ename,e.sal,e.deptno,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;
#第二步：基于以上结果，继续按照deptno分组，求grade平均值。
mysql> 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;

select后嵌套子查询

案例：找出每个员工所在的部门名称，要求显示员工名和部门名。

#不常用
mysql> select 
    -> e.ename,(select d.dname from dept d where e.deptno = d.deptno) as dname 
    -> from 
    -> emp e;

Union

使用union可以将查询的结果集相加。

案例：找出工作岗位是SALESMAN和MANAGER的员工。

mysql> select ename,job from emp where job = 'MANAGER'
    -> union
    -> select ename,job from emp where job = 'SALESMAN';

案例：两张不相干的表中的数据拼接在一起显示。

mysql> select ename from emp
    -> union
    -> select dname from dept;

limit 分页查询

limit取结果集中的部分数据。

limit是mysql特有的，其他数据库中没有，不通用。（Oracle中有一个相同的机制，叫做rownum）

语法机制：

limit startIndex, length
=startIndex表示起始位置，从0开始，0表示第一条数据。
length表示取几个。

案例：取出工资前5名的员工（思路：降序取前5个）

mysql> select ename,sal from emp order by sal desc limit 0,5;
#startindex省略的时候默认是0
mysql> select ename,sal from emp order by sal desc limit 5;

limit是sql语句最后执行的一个环节。

案例：找出工资排名在第4到第9名的员工。

mysql> select ename,sal from emp order by sal desc limit 3,6;

【通用的标准分页sql】
每页显示pageSize条记录：
第pageNo页：(pageNo - 1) * pageSize, pageSize

pageSize：是每页显示多少条记录
pageNo：显示第几页

表

创建表

1.建表语句的语法格式：

create table 表名(
  字段名1 数据类型,
  字段名2 数据类型,
  字段名3 数据类型,
  …
);

2.MySQL当中字段的常见数据类型：
int   整数型
bigint   长整型
float   浮点型
char   定长字符串
varchar   可变长字符串
date   日期类型
BLOB   二进制大对象（存储图片、视频等流媒体信息） Binary Large OBject （
CLOB   字符大对象（存储较大文本，比如，可以存储4G的字符串。） Character Large OBject

3.char和varchar的选择
在实际的开发中，当某个字段中的数据长度不发生改变的时候，是定长的，例如：性别、生日等都是采用char。

当一个字段的数据长度不确定，例如：简介、姓名等都是采用varchar。

4.BLOB和CLOB类型的使用
图片和视频不常存放在数据表当中，占用内存太大。

5.表名在数据库当中一般建议以：t_或者tbl_开始。

6.创建学生表
学生信息包括：
学号、姓名、性别、班级编号、生日
学号：bigint
姓名：varchar
性别：char
班级编号：int
生日：char

mysql> create table t_student(
    -> no bigint,
    -> name varchar(255),
    -> sex char(1),
    -> classno varchar(255),
    -> birth char(10)
    -> );

insert 增加数据

1.insert 插入数据
（1）语法格式：

insert into 表名(字段名1,字段名2,字段名3,…) values(值1,值2,值3,…)
要求：字段的数量和值的数量相同，并且数据类型要对应相同。

mysql> insert into t_student(no,name,sex,classno,birth) values(1,'zhangsan','1','1ban','1950-10-12');

#可以变换位置，但是要对应上
mysql> insert into t_student(name,sex,classno,birth,no) values('lisi','1','1ban','1950-10-12',2);

#也可以插入某一个字段，其他字段默认为NULL
#除name字段之外，剩下的所有字段自动插入NULL。
mysql> insert into t_student(name) values ('wangwu');

当一条insert语句执行成功之后，表格当中必然会多一行记录。即使多的这一行记录当中某些字段是NULL，后期也没有办法在执行insert语句插入数据了，只能使用update进行更新。

（2）改变字段的默认值

#删除某张表
mysql> drop tabel if exists t_student; //当这个表存在的话删除。

#创建表，制定name的默认值为1
mysql> create table t_student(
    -> no bigint,
    -> name varchar(255),
    -> sex char(1) default 1,
    -> classno varchar(255),
    -> birth char(10)
    -> );

#这是插入的数据中，sex的值为1
mysql> insert into t_student(name) values('zhangsan');

（3）简略写法/一次插入多行数据
字段可以省略不写，但是后面的value对数量和顺序都有要求。

mysql> insert into t_student values(1,'rose','0','class1','2010-03-20')
#每个字段都必须指定值

#一次插入多行数据
mysql> insert into t_student
    -> (no,name,sex,classno,birth)
    -> values
    ->  (3,'Alice','1','class2','1998-01-02'),(3,'Jack','1','class2','1998-08-02');

（4）表的复制
语法：

create table 表名 as select语句;
将查询结果当做表创建出来。

#对表emp1的复制
mysql> create table emp1 as select * from emp;

（5）将查询结果插入到一张表中
这张方式对于表的结构有要求，得对应上。

mysql> create table dept1 as select * from dept;
mysql> insert into dept1 select * from dept;
#将表dept的查询结果放到dept1里

update 更新数据

语法格式：

update 表名 set 字段名1=值1,字段名2=值2… where 条件;
PS:没有条件整张表数据全部更新，注意中间字段逗号连接。

案例：将部门10的LOC修改为SHANGHAI，将部门名称修改为RENSHIBU。

mysql> update dept1 set loc = 'SHANGHAI',dname='RENSHIBU' where deptno = 10;

#不加条件更新所有记录
mysql> update dept1 set loc='x',dname='y';

delete 删除数据

语法格式：

delete from 表名 where 条件;
PS：不加条件全部删除。

#删除10部门数据
mysql> delete from dept1 where deptno = 10;
#删除所有记录
mysql> delete from emp1;

truncate table 表名：表被截断，不可回滚。永久丢失。用于删除大表的数据，速度快。

删表：
drop table 表名; //通用写法
frop table if exists 表名; //oracle不支持这种写法

mysql> truncate table dept1;

对于表结构的修改使用工具完成即可，因为在实际开发中表一旦设计好之后，对表结构的修改是很少的，修改表结构就是对之前的设计进行了否定，即使需要修改表结构，我们也可以直接使用工具操作。

增删改查有一个术语：CRUD操作
Create（增） Retrieve（检索） Update（修改） Delete（删除）