from中的子查询

from后面的子查询,可以将子查询的查询结果当做一张临时表

例:找出每个岗位的平均工资的薪资等级

思路:

  1. 找出每个岗位的平均工资(按照岗位分组求平均工资)
mysql> select job,avg(sal) from emp group by job;
+-----------+-------------+
| job       | avg(sal)    |
+-----------+-------------+
| CLERK     | 1037.500000 |
| SALESMAN  | 1400.000000 |
| MANAGER   | 2758.333333 |
| ANALYST   | 3000.000000 |
| PRESIDENT | 5000.000000 |
+-----------+-------------+
5 rows in set (0.00 sec)
  1. 查看一下工资等级表
mysql> select * from salgrade;
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+
5 rows in set (0.00 sec)
  1. 将查询到的第一张表与第二张工资登记表进行连接
mysql> select
    ->  t.*,s.grade
    -> from
    ->  (select job,avg(sal) as avgsal from emp group by job) t
    -> join
    ->  salgrade s
    -> on
    ->  t.avgsal between s.losal and s.hisal;
+-----------+-------------+-------+
| job       | avgsal      | grade |
+-----------+-------------+-------+
| CLERK     | 1037.500000 |     1 |
| SALESMAN  | 1400.000000 |     2 |
| MANAGER   | 2758.333333 |     4 |
| ANALYST   | 3000.000000 |     4 |
| PRESIDENT | 5000.000000 |     5 |
+-----------+-------------+-------+
5 rows in set (0.00 sec)
select后的子查询

例:找出每个员工的部门名称,要求显示员工名,部门名

mysql> 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 |
+--------+------------+
14 rows in set (0.00 sec)

注:其中select中的子查询必须只能查询到一条记录信息,因为前面的e.ename是只有一条,必须与之相匹配

错误示范:

mysql> select e.ename,e.deptno,(select dname from dept) as dname from emp e;
ERROR 1242 (21000): Subquery returns more than 1 row

注:报错信息子查询返回结果大于1

union合并查询结果集

例:查询工作岗位是MANAGERSALESMAN的员工

方法1:

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

方法2:

mysql> select ename,job from emp where job in('MANAGER','SALESMAN');

方法3:使用union

mysql> 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 |
+--------+----------+
7 rows in set (0.00 sec)

union的效率要高一些,对于表连接来说,每连接一次新表,则匹配的次数满足笛卡尔积,成倍的增加,但是union可以减少匹配的次数,在减少匹配次数的情况下,还可以完成两个结果集的拼接

例:

使用连接:

a连接b连接c,a10条记录,b10条记录,c10条记录,匹配次数:1000

使用union:

a连接b:10*10=100,a连接c:10*10=100,使用union:100+100=200

union注意事项:

  1. union在进行结果集合并的时候,要求两个结果集的列数相同

例:

mysql> select ename,job from emp where job ='MANAGER'
    -> union
    -> select ename from emp where job='SALESMAN';
ERROR 1222 (21000): The used SELECT statements have a different number of columns
  1. 对集合并列与列之间的数据类型要求,MYSQL较松,不做要求,oracle严格,会报错

例:

mysql> select ename,job from emp where job ='MANAGER'
    -> union
    -> select ename,sal from emp where job='SALESMAN';
+--------+---------+
| ename  | job     |
+--------+---------+
| JONES  | MANAGER |
| BLAKE  | MANAGER |
| CLARK  | MANAGER |
| ALLEN  | 1600.00 |
| WARD   | 1250.00 |
| MARTIN | 1250.00 |
| TURNER | 1500.00 |
+--------+---------+
7 rows in set (0.00 sec)
limit关键字

limit的作用就是将查询结果集的一部分取出来,通常使用在分页查询当中,分页的作用是为了提高用户的体验,因为一次全部都查出来,用户体验差

例:按照薪资降序,取出排名在前5的员工的名字与薪资

mysql> select ename,sal from emp order by sal desc limit 5;
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
| SCOTT | 3000.00 |
| FORD  | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+
5 rows in set (0.00 sec)

其实有两种写法:

  1. 默认取前5
mysql> select ename,sal from emp order by sal desc limit 5;
  1. 从下标0开始,取5
mysql> select ename,sal from emp order by sal desc limit 0,5;

注:在mysql当中,limitorder by之后执行

例:按照薪资降序,取出工资排名在[3-5]名的员工名字与薪资

mysql> select ename,sal from emp order by sal desc limit 2,3;
+-------+---------+
| ename | sal     |
+-------+---------+
| FORD  | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+
3 rows in set (0.00 sec)

DDL语句

表的创建

语法格式:

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

注:

  • 表名:建议以t_或者tbl_开始,可读性强,做到见名知意、
  • 字段名:见名知意
数据类型

mysql中有很多数据类型,只需掌握常见的数据类型即可

  • varchar(最长255):可变长度的字符串,比较智能,节约空间,会根据实际的数据长度动态分配空间

    • 优点:节约空间
    • 缺点:需要动态分配空间,速度慢
  • char(最长255):定长字符串,不管实际的数据长度是多少,分配固定长度的空间去存储数据,使用不恰当的时候,可能会导致空间的浪费

    • 优点:不需要动态分配空间,速度快
    • 缺点:使用不当可能会导致空间的浪费
  • int:数字中的整数型,等同于Java中的int
  • bigint:数字中的长整型,等同于Java中的long
  • float:单精度浮点型数据
  • double:双精度浮点型数据
  • date:短日期类型
  • datetime:长日期类型
  • clob:Character Large Object字符大对象,最多可以存储4G的字符串,例如存储一篇文章,一段说明,超过255个字符的都要使用CLOB字符大对象来存储
  • blob:Binary Large Object专门用来存储图片,声音,视频等流媒体数据,往BLOB类型的字段上插入数据的时候,例如插入一个图片,视频等,需要使用IO
insert语句

首先我们先建立一个学生表

mysql> create table t_student(no int,name varchar(32),sex char(1),age int(3),email varchar(255));
Query OK, 0 rows affected, 1 warning (0.08 sec)

mysql> show tables;
+---------------------+
| Tables_in_testmysql |
+---------------------+
| dept                |
| emp                 |
| salgrade            |
| t_student           |
+---------------------+
4 rows in set (0.00 sec)

mysql> desc t_student;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| no    | int          | YES  |     | NULL    |       |
| name  | varchar(32)  | YES  |     | NULL    |       |
| sex   | char(1)      | YES  |     | NULL    |       |
| age   | int          | YES  |     | NULL    |       |
| email | varchar(255) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

insert语法格式:

insert into 表名(字段名1,字段名2,字段名3...) values(值1,值2,值3);

注:字段名和值要一一对应,不仅数量要对应,数据类型也要对应

例:按照顺序插入

mysql> insert t_student(no,name,sex,age,email) values(1,'YuKi','m',20,'YQHP@.com');
Query OK, 1 row affected (0.02 sec)
mysql> select * from t_student;
+------+------+------+------+-----------+
| no   | name | sex  | age  | email     |
+------+------+------+------+-----------+
|    1 | YuKi | m    |   20 | YQHP@.com |
+------+------+------+------+-----------+
1 row in set (0.00 sec)

例:不按照顺序插入

mysql> insert into t_student(email,name,sex,age,no) values('google@.com','google','f',21,2);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t_student;
+------+--------+------+------+-------------+
| no   | name   | sex  | age  | email       |
+------+--------+------+------+-------------+
|    1 | YuKi   | m    |   20 | YQHP@.com   |
|    2 | google | f    |   21 | google@.com |
+------+--------+------+------+-------------+
2 rows in set (0.00 sec)

例:少插入一些数据

mysql> insert into t_student(no) values(3);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t_student;
+------+--------+------+------+-------------+
| no   | name   | sex  | age  | email       |
+------+--------+------+------+-------------+
|    1 | YuKi   | m    |   20 | YQHP@.com   |
|    2 | google | f    |   21 | google@.com |
|    3 | NULL   | NULL | NULL | NULL        |
+------+--------+------+------+-------------+
3 rows in set (0.00 sec)

注:insert语句只是插入数据,不能用作修改数据,每使用insert语句,只会创建一行新的数据

例:假如我看到no3的那行后面的namenull,想使用insert语句修改,但失败了

mysql> insert into t_student (no,name) values(3,'Hello');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t_student;
+------+--------+------+------+-------------+
| no   | name   | sex  | age  | email       |
+------+--------+------+------+-------------+
|    1 | YuKi   | m    |   20 | YQHP@.com   |
|    2 | google | f    |   21 | google@.com |
|    3 | NULL   | NULL | NULL | NULL        |
|    3 | Hello  | NULL | NULL | NULL        |
+------+--------+------+------+-------------+
4 rows in set (0.00 sec)

我们可以发现没有设定值的字段名的默认值都是NULL,那么有没有方法设置其默认值,我们删除t_student重新创建试试

mysql> create table t_student(no int,name varchar(32),sex char(1) default 'm',age int(3),email varchar(255));
Query OK, 0 rows affected, 1 warning (0.06 sec)

mysql> desc t_student;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| no    | int          | YES  |     | NULL    |       |
| name  | varchar(32)  | YES  |     | NULL    |       |
| sex   | char(1)      | YES  |     | m       |       |
| age   | int          | YES  |     | NULL    |       |
| email | varchar(255) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

这样假如我们不插入sex的值,就会默认为m,例:

mysql> insert into t_student(no) values(1);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t_student;
+------+------+------+------+-------+
| no   | name | sex  | age  | email |
+------+------+------+------+-------+
|    1 | NULL | m    | NULL | NULL  |
+------+------+------+------+-------+
1 row in set (0.00 sec)

insert语句中的字段名是可以省略掉的,但是必须注意:前面的字段名省略掉了,相当于都写上了,所以后面的值必须全部都写上,且必须按照字段名顺序写

例:

mysql> insert into t_student values(2,'YQHP','f',20,'YQHP@.com');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t_student;
+------+------+------+------+-----------+
| no   | name | sex  | age  | email     |
+------+------+------+------+-----------+
|    1 | NULL | m    | NULL | NULL      |
|    2 | YQHP | f    |   20 | YQHP@.com |
+------+------+------+------+-----------+
2 rows in set (0.00 sec)
Last modification:February 21st, 2021 at 04:52 pm