MySQL的基本学习(三)——DD/M/QL三类SQL语言和SQLyog

MySQL的基本学习(三)——DD/M/QL三类SQL语言和SQLyog

前言

好久没有学MySQL了,最近因为JavaWeb的原因,让我正好在补习一下SQL语句等知识,SQL语句虽然在这系列的第一篇文章里聊过了,但是这篇文章里我计划重新学一下

 

 

 

 


DDL

Data Definition Language,数据定义语言,用来定义数据库对象

DDL主要用来操作数据库和数据库的表。

所谓操作,主要就是四个:CRUD

  1. C(Create):创建
  2. R(Retrieve):查询
  3. U(Update):修改
  4. D(Delete):删除

操作数据库

R(Retrieve):查询

  • 查询所有数据库的名称: SHOW DATABASES;
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| crazythings        |
| information_schema |
| jsptest            |
| mysql              |
| notenow            |
| performance_schema |
+--------------------+
6 rows in set (0.01 sec)

performance_schema、mysql、test(我删了这个了)、information_schema 这四个数据库是MySQL自带的数据库:

  1. information_schema :你在数据目录下的data目录中找不到这个information_schema 目录,因为这个数据库不是真正的数据库,它只是用来描述MySQL中的一些信息的(例如有哪些表、有哪些库的名字),这里面存放的表并不是真正的表,而是视图(以后学习的玩意儿),该数据库并不存在真正对应的物理文件
  2. mysql:核心数据库,存放了很多核心数据
  3. performance_schema:对性能提升相关的数据库
  4. test:想删就删,测试库而已

 

  • 查询创建数据库的语法:SHOW CREATE DATABASE 库名;
mysql> SHOW CREATE DATABASE notenow;
+----------+---------------------------------------------------- -------------+
| Database | Create Database                                                  |
+----------+------------------------------------------------------------------+
| notenow  | CREATE DATABASE `notenow` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+---------------------------------------------------------- -------+
1 row in set (0.00 sec)

数据库默认使用的是utf-8字符集。

 

C(Create):创建

  • 创建数据库:CREATE DATABASE 数据库名;
mysql> CREATE DATABASE DB1;
Query OK, 1 row affected (0.02 sec)
  • 创建数据库:CREATE DATABASE IF NOT EXISTS 数据库名;
mysql> CREATE DATABASE IF NOT EXISTS DB2;
Query OK, 1 row affected (0.02 sec)

这种创建数据库与上面的区别在于:上面的创建语句,如果已经存在同名数据库,则会报错;该语句,如果存在同名数据库不会报错,也不会去创建,如果没有同名数据库才会创建。

  • 创建数据库并指定字符集:CREATE DATABASE 数据库名 CHARACTER SET 字符集;
mysql> CREATE DATABASE DB3 CHARACTER SET gbk;
Query OK, 1 row affected (0.02 sec)

“SHOW CREATE DATABASE 库名” 查看数据库字符集。

 

综合利用:创建一个数据库且指定字符集为utf-8,且有重名的话不能报错。

CREATE DATABASE IF NOT EXISTS DB4 CHARACTER SET utf8;

 

U(Update):修改

  • 修改数据库字符集:ALTER DATABASE 数据库名称 CHARACTER SET 字符集;
mysql> ALTER DATABASE DB3 CHARACTER SET utf8;
Query OK, 1 row affected, 1 warning (0.00 sec)

 

D(Delete):删除

  • 删除数据库:DROP DATABASE 数据库名称;
mysql> DROP DATABASE DB3;
Query OK, 0 rows affected (0.02 sec)
  • 删除数据库:DROP DATABASE IF EXISTS 数据库名称;
mysql> DROP DATABASE IF EXISTS DB3;
Query OK, 0 rows affected (0.02 sec)

这种删除数据库与上面的区别在于:上面的删除语句,如果没有该数据库,则会报错;该语句,如果不存在该数据库不会报错,当然也不会去删除,如果存在数据库才会删除。这个与CREATE那里的道理类似。

 

使用数据库

  • 查询当前正在使用的数据库的名称:SELECT DATABASE();
mysql> SELECT DATABASE();
+--------------------+
| DATABASE()         |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)
  • 使用数据库:USE 数据库名称;

 

操作数据表

C(Create):创建

  • 创建表:CREATE  TABLE  表名  ( 列名1 数据类型1,列名2 数据类型2,列名3 数据类型3 );
mysql> CREATE TABLE student(
    -> id int,
    -> name varchar(32),
    -> age int,
    -> score double(4,1),
    -> birthday date,
    -> insert_time timestamp
    -> );
Query OK, 0 rows affected, 1 warning (0.04 sec)

常见数据库数据类型:

  1. int:整数类型,示例:age  int
  2. double:小数类型(双精度浮点数),示例:score  double(5,2) ——括号意为小数点后最多5位,我们保留两位
  3. date:日期,只包含年月日,yyyy-MM-dd
  4. datetime:日期,包含年月日时分秒,yyyy-MM-dd HH:mm:ss
  5. timestamp:时间戳类型,包含年月日时分秒,yyyy-MM-dd HH:mm:ss,与datetime区别,如果不给这个timestamp字段赋值或者赋值为null,那么它将会默认使用当前的系统时间,来自动赋值
  6. varchar:字符串类型,示例:name varchar(20)——默认后面最大20个字符

注意,最后一个字段信息末尾不需要加逗号

  • 复制一个数据表:CREATE  TABLE  新表名  LIKE  被复制表名
mysql> CREATE TABLE stu LIKE student;
Query OK, 0 rows affected, 1 warning (0.03 sec)

R(Retrieve):查询

  • 查询某个数据库中所有的表的名称:SHOW TABLES;
mysql> SHOW TABLES;
+-------------------+
| Tables_in_notenow |
+-------------------+
| test              |
| worker            |
+-------------------+
2 rows in set (0.00 sec)
  • 查询表结构:DESC 表名;
mysql> DESC worker;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| 职工号    | int(11)     | NO   | PRI | NULL    |       |
| 姓名      | varchar(20) | YES  |     | NULL    |       |
| 性别      | varchar(2)  | YES  |     | NULL    |       |
| 工资      | int(11)     | YES  |     | NULL    |       |
| 职称      | varchar(20) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

U(Update):修改

  • 修改表名:ALTER  TABLE  表名  RENAME  TO  新表名;
mysql> ALTER TABLE stu RENAME TO student;
Query OK, 0 rows affected (0.03 sec)
  • 修改表的字符集:ALTER  TABLE  student  CHARACTER  SET  utf8;

“SHOW CREATE TABLE student;”查看表以前的字符集

  • 添加一个字段:ALTER  TABLE  表名  ADD  字段名  数据类型;
mysql> ALTER TABLE student ADD sex varchar(10);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
  • 修改字段名称、类型:ALTER  TABLE  表名  CHANGE  字段  新字段  新字段数据类型;
mysql> ALTER TABLE student CHANGE sex gender varchar(20);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
  • 修改字段类型:ALTER  TABLE  表名  MODIFY  字段名  新数据类型
mysql> ALTER TABLE student  MODIFY  gender  varchar(10);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0
  • 删除字段:ALTER  TABLE  表名  DROP  字段名
mysql> ALTER TABLE student DROP gender;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

 D(Delete):删除

  • 删除数据表:DROP  TABLE  表名;
mysql> DROP TABLE student;
Query OK, 0 rows affected (0.02 sec)
  • 删除数据表:DROP  TABLE IF  EXISTS  表名;

存在,则删除、不存在,也不会报错。

 


插曲:SQLyog

为了方便我们使用数据库MySQL,这里我们来使用一个图形化操作的工具SQLyog

分享链接:https://pan.baidu.com/s/1KJarOpABTpIR76yZGHOVTQ
提取码:1d5u

一路下一步安装即可

然后打开后会要求注册序列号,sn.txt中随便写一个即可。

然后就可以成功打开了,然后会弹出一个连接到我的SQL主机,新建,配置用户名、密码,连接即可。

 配置新连接报错:错误号码 2058,分析是 mysql 密码加密方法变了。

解决方式:先登录你的数据库,然后执行

ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘password’;

password是你的登陆密码。

正如workBench、Navicat等图形化工具一样,这类工具非常方便,很多操作可以直接点击实现,不需要输入SQL语句,不过在我们学习阶段,还是建议老老实实使用SQL语句去操作。

 


DML

Data Manipulation Language,数据操作语言,用来对数据库中表的数据进行增删改。

主要用来增删表中的数据,是非常重要的SQL语言。

DML主要是针对数据表中的数据,这个主要分为三个方面:添加数据、删除数据、修改数据

操作数据

添加数据

  • 添加数据:INSERT  INTO  表名(字段1,字段2,……,字段n)  VALUES(值1,值2,……,值n);
mysql> INSERT INTO test(id,name,password) VALUES(5,'张无忌','987654321');
Query OK, 1 row affected (0.02 sec)

注意点:

  1. 列名和值要一一对应
  2. 除了数字类型以外,其他类型需要使用引号(单双都可以)。

 

  • 添加数据:INSERT  INTO  表名  VALUES(值1,值2,……,值n);
mysql> INSERT INTO test VALUES(5,'赵敏','123456789');
Query OK, 1 row affected (0.02 sec)

这条语句和上一句的区别在于:表名后面没有说明字段,则给所有字段依次添加值,如果想要空开,可以使用“NULL”,如果对应字段数据类型是时间戳timestamp,则会自动填入当前时间。

删除数据

  • 删除数据:DELETE  FROM  表名 [WHERE 条件]
mysql> DELETE FROM test WHERE name='张无忌';
Query OK, 1 row affected (0.01 sec)

注意:如果不添加条件,那么会将表中的记录全部删除!!!

  • 删除表,然后再创建一个一模一样的空表:TRUNCATE  TABLE  表名;
mysql> TRUNCATE TABLE test;
Query OK, 0 rows affected (0.03 sec)

如果你想要删除一个表中的所有记录,建议使用TRUNBLE TABLE,而不是DELETE  FROM  表名,因为后者会根据数据记录数量执行同等数量的指令,对性能不友好;而TRUNBLE TABLE,只会执行“DROP”删除表、“CREATE” 创建表,两个指令,性能更佳。

老实说我不知道这个命令应该属于DDL(数据定义语言),还是DML(数据操作语言)

修改数据

  • UPDATE  表名  SET  列名1=值1,列名2=值2,……,列名n=值n   [WHERE  条件];
mysql> UPDATE test SET password=567 WHERE id=3;
Query OK, 1 row affected (0.01 sec)

注意:如果不添加任何条件,则会将表中所有记录根据字段全部修改。

 

 


DQL

Data Query Language,数据查询语言,用来对数据库中表的数据进行查询。

 

基础查询

基本使用

查询数据:SELECT  *  FROM  表名;

语法:

SELECT   字段列表

FROM   表名列表

[WHERE  条件列表]

[GROUP  BY  分组字段 ]

[HAVING  分组之后的条件]

[ORDER  BY  排序]

[LIMIT  分页限定]

mysql> select * from test;
+----+-----------------+-----------+
| id | name            | password  |
+----+-----------------+-----------+
|  1 | 可爱的兔子      | 123456    |
|  2 | 凶恶的老虎      | 654321    |
|  3 | 职工号          | 12345     |
|  5 | 张无忌          | 987654321 |
+----+-----------------+-----------+
4 rows in set (0.00 sec)
//查询指定字段
mysql> SELECT name,sex FROM test;
+--------+------+
| name   | sex  |
+--------+------+
| 小红   | 女   |
| 小明   | 男   |
| 小张   | 男   |
| 小浅   | 女   |
| 小军   | 男   |
| 小候   | 男   |
| 阿炜   | 男   |
| 小丽   | 女   |
+--------+------+
8 rows in set (0.00 sec)

去重

查询结果经常会用来去重,例如男女、地名等,没有必要显示很多相同的。

语法:加入 DISTINCT 关键字

mysql> SELECT DISTINCT sex FROM test;
+------+
| sex  |
+------+
| 女   |
| 男   |
+------+
2 rows in set (0.00 sec)

去重的话要保证结果集完全一样,即多个字段完全一样才能叫做重复。

计算

字段中直接设置一个“和字段”即可

mysql> SELECT name,math,english,math+english FROM test;
+--------+------+---------+--------------+
| name   | math | english | math+english |
+--------+------+---------+--------------+
| 小王   |   56 |      57 |          113 |
| 小李   |   85 |      78 |          163 |
| 小赵   |   25 |      38 |           63 |
+--------+------+---------+--------------+
3 rows in set (0.00 sec)

注意:如果有null,参与的运算,计算的结果都为null,如果不符合实际情况,可以结合函数IFNUll处理,例如上例加成绩:SELECT name,math,english,math+IFNULL(english,0) FROM test;,即如果英语成绩为NULL,则替换为0。

另外,对于字段 math+english,如果觉得难看,可以起一个别名,语法:SELECT name,math,english,math+english  AS   总分    FROM test; 即可,也可以省略AS关键字,其他字段math、english也可以这样修改

mysql> SELECT name,math,english,math+english  AS 总分 FROM test;
+--------+------+---------+--------+
| name   | math | english | 总分   |
+--------+------+---------+--------+
| 小王   |   56 |      57 |    113 |
| 小李   |   85 |      78 |    163 |
| 小赵   |   25 |      38 |     63 |
+--------+------+---------+--------+
3 rows in set (0.00 sec)

不过一般别名都会使用英文的。

 

条件查询

语法: WHERE   条件

使用示例:

mysql> SELECT name,math FROM test WHERE math>=56;
+--------+------+
| name   | math |
+--------+------+
| 小王   |   56 |
| 小李   |   85 |
+--------+------+
2 rows in set (0.00 sec)
//可以使用&&、AND,或者 BETWEEN  AND
mysql> SELECT name,math FROM test WHERE english  BETWEEN 60 AND 100;
+--------+------+
| name   | math |
+--------+------+
| 小李   |   85 |
+--------+------+
1 row in set (0.00 sec)
mysql> SELECT * FROM test WHERE math IN(85,25);
+----+--------+---------+------+
| id | name   | english | math |
+----+--------+---------+------+
|  2 | 小李   |      78 |   85 |
|  3 | 小赵   |      38 |   25 |
+----+--------+---------+------+
2 rows in set (0.00 sec)

 

 

常用SQL运算符:

比较运算符 说明
>、<、<=、>=、=、<> 其中,<>在SQL中表示不等于,在mysql中也可以使用!=,但是SQL中没有==运算符
BETWEEN…AND 在一个范围之内,如BETWEEN 100 AND 200,相当于条件在100到200之间,包头且包尾
IN(集合) 集合表示多个值,使用逗号分隔
LIKE ‘关键字’ 模糊查询
IS NULL 查询某一列为NULL的值(不能写 =NULL)
IS NOT NULL 查询某一列不为NULL的值

 

逻辑运算符 说明
and 或 && 与,SQL中建议使用前者,后者不通用
or 或 ||
not 或 !

 

模糊查询

模糊查询比较牛逼,我们单独来说。

条件查询中的模糊查询使用关键字LIKE,需要注意的点:

  • 占位符
    • _:单个任意字符(一个)
    • %:多个任意字符(0或者多个)
mysql> SELECT * FROM test;
+----+--------------+---------+------+
| id | name         | english | math |
+----+--------------+---------+------+
|  1 | 慕容复       |      56 |   46 |
|  2 | 东方不败     |      47 |   58 |
|  3 | 令狐冲       |      56 |   38 |
|  4 | 张无忌       |      16 |   20 |
|  5 | 慕容博       |      78 |   90 |
|  6 | 孤独求败     |      53 |   49 |
|  7 | 张三丰       |      67 |   66 |
+----+--------------+---------+------+
7 rows in set (0.00 sec)

mysql> SELECT id,name FROM test WHERE name LIKE '张%';
+----+-----------+
| id | name      |
+----+-----------+
|  4 | 张无忌    |
|  7 | 张三丰    |
+----+-----------+
2 rows in set (0.00 sec)

mysql> SELECT id,name FROM test WHERE name LIKE '慕容_';
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 慕容复    |
|  5 | 慕容博    |
+----+-----------+
2 rows in set (0.00 sec)

mysql> SELECT id,name FROM test WHERE name LIKE '%败';
+----+--------------+
| id | name         |
+----+--------------+
|  2 | 东方不败     |
|  6 | 孤独求败     |
+----+--------------+
2 rows in set (0.00 sec)

如果出现异常,可能是数据里存在空字符

 

排序查询

语法:ORDER BY  字句

具体使用:ORDER BY  排序字段1  排序方式1,排序字段2  排序方式2,排序字段3  排序方式3

单一排序条件

mysql> SELECT * FROM test ORDER BY id ASC;
+----+--------+----------+
| id | name   | password |
+----+--------+----------+
|  1 | 乔峰   | 123      |
|  2 | 段誉   | 234      |
|  3 | 虚竹   | 345      |
+----+--------+----------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM test ORDER BY id DESC;
+----+--------+----------+
| id | name   | password |
+----+--------+----------+
|  3 | 虚竹   | 345      |
|  2 | 段誉   | 234      |
|  1 | 乔峰   | 123      |
+----+--------+----------+
3 rows in set (0.00 sec)

 排序方式:

  1. ASC:升序(默认)
  2. DESC:降序

学英语
ascend:上升、升高
descend:下降

多排序条件

如果第一排序条件相同,那么数据库会根据第二排序条件进行排序,例如示例数学成绩相同,根据英语成绩升序排列:

mysql> SELECT * FROM score ORDER BY math ASC,english ASC;
+------+-----------+------+---------+
| id   | name      | math | english |
+------+-----------+------+---------+
|    3 | 郭富城    |   69 |      34 |
|    2 | 刘德华    |   77 |      81 |
|    1 | 张学友    |   77 |      85 |
|    4 | 黎明      |   90 |      87 |
+------+-----------+------+---------+
4 rows in set (0.00 sec)

 

聚合查询

聚合函数:将一列数据作为一个整体,然后进行纵向的计算

例如上例,计算四大天王的math的总成绩,就可以使用聚合函数

可用聚合函数:

  1. count:计算个数
  2. max:计算最大值
  3. min:计算最小值
  4. sum:求和
  5. avg:计算平均值

语法:SELECT  函数(字段)    FROM  表名;

注意:聚合函数会排除NULL的数据,解决方法可以利用IFNULL函数,例如SELECT  COUNT(IFNULL(english,0))  FROM  student,即student表中的english字段中,将NULL替换成0,然后统计english字段下的个数。

mysql> SELECT AVG(math) FROM score;
+-----------+
| AVG(math) |
+-----------+
|     78.25 |
+-----------+
1 row in set (0.01 sec)

 

分组查询

分组查询意为根据字段的值,将数据分组,然后根据分组进行处理。

语法:GROUP BY 分组字段

mysql> SELECT* FROM test;
+----+--------+-------+------+
| id | name   | score | sex  |
+----+--------+-------+------+
|  1 | 小红   |    59 | 女   |
|  2 | 小明   |    15 | 男   |
|  3 | 小张   |    87 | 男   |
|  4 | 小浅   |    79 | 女   |
+----+--------+-------+------+
4 rows in set (0.00 sec)
//示例,将分数按照男女分组
mysql> SELECT  sex,AVG(score)  FROM test GROUP BY sex;
+------+------------+
| sex  | AVG(score) |
+------+------------+
| 女   |    69.0000 |
| 男   |    51.0000 |
+------+------------+
2 rows in set (0.01 sec)

//进阶示例:将分数按照男女分组,且不计入低于60分的同学
mysql> SELECT  sex,AVG(score)  FROM test WHERE score>60 GROUP BY sex;
+------+------------+
| sex  | AVG(score) |
+------+------------+
| 男   |    87.0000 |
| 女   |    79.0000 |
+------+------------+
2 rows in set (0.00 sec)

//进阶示例,分组后,显示多于一个人的组的情况
mysql> SELECT  sex,AVG(score)  FROM test  GROUP BY sex HAVING COUNT(id)>1;
+------+------------+
| sex  | AVG(score) |
+------+------------+
| 女   |    69.0000 |
| 男   |    51.0000 |
+------+------------+
2 rows in set (0.00 sec)

注意:

  1. 分组之后查询的字段:分组字段、聚合函数
  2. WHERE和HAVING的区别:
    1. WHERE在分组之前进行限定,如果不满足条件则不参与分组;HAVING在分组之后进行限定,如果不满足结果则不会被查询出来。
    2. WHERE后面不可以跟聚合函数,HAVING后面可以跟着聚合函数。

 

补充,对于HAVING的使用可以采用“别名”的机制来使命令简洁:

//给聚合函数COUNT(id)起一个别名,在后面HAVING后面可以直接使用别名代替
mysql> SELECT  sex,COUNT(id) num  FROM test  GROUP BY sex HAVING num>1;
+------+-----+
| sex  | num |
+------+-----+
| 女   |   2 |
| 男   |   2 |
+------+-----+
2 rows in set (0.00 sec)

分页查询

语法:LIMIT 开始的索引,每页查询的条数;

mysql> SELECT * FROM test;
+----+--------+-------+------+
| id | name   | score | sex  |
+----+--------+-------+------+
|  1 | 小红   |    59 | 女   |
|  2 | 小明   |    15 | 男   |
|  3 | 小张   |    87 | 男   |
|  4 | 小浅   |    79 | 女   |
|  5 | 小军   |    68 | 男   |
|  6 | 小候   |    77 | 男   |
|  7 | 阿炜   |    59 | 男   |
|  8 | 小丽   |    63 | 女   |
+----+--------+-------+------+
8 rows in set (0.00 sec)

mysql> SELECT * FROM test LIMIT 0,3;
+----+--------+-------+------+
| id | name   | score | sex  |
+----+--------+-------+------+
|  1 | 小红   |    59 | 女   |
|  2 | 小明   |    15 | 男   |
|  3 | 小张   |    87 | 男   |
+----+--------+-------+------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM test LIMIT 3,3;
+----+--------+-------+------+
| id | name   | score | sex  |
+----+--------+-------+------+
|  4 | 小浅   |    79 | 女   |
|  5 | 小军   |    68 | 男   |
|  6 | 小候   |    77 | 男   |
+----+--------+-------+------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM test LIMIT 6,3;
+----+--------+-------+------+
| id | name   | score | sex  |
+----+--------+-------+------+
|  7 | 阿炜   |    59 | 男   |
|  8 | 小丽   |    63 | 女   |
+----+--------+-------+------+
2 rows in set (0.00 sec)

公式:开始的索引=(当前的页码-1)*每页显示的条数

注意:

  1. 数据库中的数据的索引是从0开始的
  2. 最后一页如果不满足要求显示条数,那么将有多少条就显示多少条
  3. 分页操作是一个“方言”,即别的数据库软件可能会有出入,LIMIT这个子句仅限于MySQL

 


多表查询(属于DQL的知识点)

概述

基本语法:SELECT  字段列表  FROM  表名列表  WHERE  条件

 

我们来造两个表,跟着我做:

Dept:部门

 CREATE TABLE dept(
 id INT PRIMARY KEY AUTO_INCREMENT,
 name VARCHAR(20)
);

 INSERT INTO dept (name) VALUES('开发部'),
('市场部'),
('财务部');

emp:员工

CREATE TABLE emp(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(10),
gender CHAR(1),
salary DOUBLE,
join_date DATE,
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES dept(id)
);

INSERT INTO emp(name,gender,salary,join_date,dept_id) 
VALUES('孙悟空','男',7200,'2013-02-24',1);

INSERT INTO emp(name,gender,salary,join_date,dept_id) 
VALUES('猪八戒','男',3600,'2012-07-04',2);

INSERT INTO emp(name,gender,salary,join_date,dept_id) 
VALUES('唐僧','男',9000,'2015-01-29',2);

INSERT INTO emp(name,gender,salary,join_date,dept_id) 
VALUES('白骨精','女',3000,'2010-04-20',3);

INSERT INTO emp(name,gender,salary,join_date,dept_id) 
VALUES('蜘蛛精','女',6500,'2016-07-25',1);

最终两个表的内容:

mysql> SELECT * FROM dept;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 开发部    |
|  2 | 市场部    |
|  3 | 财务部    |
+----+-----------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM emp;
+----+-----------+--------+--------+------------+---------+
| id | name      | gender | salary | join_date  | dept_id |
+----+-----------+--------+--------+------------+---------+
|  1 | 孙悟空    | 男     |   7200 | 2013-02-24 |       1 |
|  2 | 猪八戒    | 男     |   3600 | 2012-07-04 |       2 |
|  3 | 唐僧      | 男     |   9000 | 2015-01-29 |       2 |
|  4 | 白骨精    | 女     |   3000 | 2010-04-20 |       3 |
|  5 | 蜘蛛精    | 女     |   6500 | 2016-07-25 |       1 |
+----+-----------+--------+--------+------------+---------+
5 rows in set (0.00 sec)

操作示例

执行语句SELECT  *  FROM  emp,dept

mysql> SELECT * FROM emp,dept;
+----+-----------+--------+--------+------------+---------+----+-----------+
| id | name      | gender | salary | join_date  | dept_id | id | name      |
+----+-----------+--------+--------+------------+---------+----+-----------+
|  1 | 孙悟空    | 男     |   7200 | 2013-02-24 |       1 |  1 | 开发部    |
|  1 | 孙悟空    | 男     |   7200 | 2013-02-24 |       1 |  2 | 市场部    |
|  1 | 孙悟空    | 男     |   7200 | 2013-02-24 |       1 |  3 | 财务部    |
|  2 | 猪八戒    | 男     |   3600 | 2012-07-04 |       2 |  1 | 开发部    |
|  2 | 猪八戒    | 男     |   3600 | 2012-07-04 |       2 |  2 | 市场部    |
|  2 | 猪八戒    | 男     |   3600 | 2012-07-04 |       2 |  3 | 财务部    |
|  3 | 唐僧      | 男     |   9000 | 2015-01-29 |       2 |  1 | 开发部    |
|  3 | 唐僧      | 男     |   9000 | 2015-01-29 |       2 |  2 | 市场部    |
|  3 | 唐僧      | 男     |   9000 | 2015-01-29 |       2 |  3 | 财务部    |
|  4 | 白骨精    | 女     |   3000 | 2010-04-20 |       3 |  1 | 开发部    |
|  4 | 白骨精    | 女     |   3000 | 2010-04-20 |       3 |  2 | 市场部    |
|  4 | 白骨精    | 女     |   3000 | 2010-04-20 |       3 |  3 | 财务部    |
|  5 | 蜘蛛精    | 女     |   6500 | 2016-07-25 |       1 |  1 | 开发部    |
|  5 | 蜘蛛精    | 女     |   6500 | 2016-07-25 |       1 |  2 | 市场部    |
|  5 | 蜘蛛精    | 女     |   6500 | 2016-07-25 |       1 |  3 | 财务部    |
+----+-----------+--------+--------+------------+---------+----+-----------+
15 rows in set (0.00 sec)

很牛逼,突然就有了这么多的数据。

其实这个结果集,有另外一个称呼“笛卡尔积”,即有两个集合A、B,A、B集合中所有成员的组合情况就称为笛卡尔积。

上面的数据中,我们的emp表中的dept_id 属性代表该员工所属哪个部门,这个值应该与dept表中的id相同才有必要显示,换句话说就是,我们需要消除不必要的查询数据。

这就需要我们进行多表查询的分类,从而消除无用数据:

 

多表查询的分类

内连接查询

  • 隐式内连接:使用WHERE条件来消除无用的数据

以上例为例,我们要查询所有员工的信息和对应部门的信息:

mysql> SELECT * FROM emp,dept WHERE emp.`dept_id`=dept.`id`;
+----+-----------+--------+--------+------------+---------+----+-----------+
| id | name      | gender | salary | join_date  | dept_id | id | name      |
+----+-----------+--------+--------+------------+---------+----+-----------+
|  1 | 孙悟空    | 男     |   7200 | 2013-02-24 |       1 |  1 | 开发部    |
|  5 | 蜘蛛精    | 女     |   6500 | 2016-07-25 |       1 |  1 | 开发部    |
|  2 | 猪八戒    | 男     |   3600 | 2012-07-04 |       2 |  2 | 市场部    |
|  3 | 唐僧      | 男     |   9000 | 2015-01-29 |       2 |  2 | 市场部    |
|  4 | 白骨精    | 女     |   3000 | 2010-04-20 |       3 |  3 | 财务部    |
+----+-----------+--------+--------+------------+---------+----+-----------+
5 rows in set (0.00 sec)

注意:在WHERE后面使用“表名.`字段`”来指出参与比较的字段,字段左右是反单引号,不是单引号,不过其实加不加都可以。

如果我们只是想要得到几个字段的内容,也需要指出字段所属哪个表:

mysql> SELECT emp.`name`,emp.`gender`,dept.`name`  FROM emp,dept WHERE emp.dept_id=dept.id;
+-----------+--------+-----------+
| name      | gender | name      |
+-----------+--------+-----------+
| 孙悟空    | 男     | 开发部    |
| 蜘蛛精    | 女     | 开发部    |
| 猪八戒    | 男     | 市场部    |
| 唐僧      | 男     | 市场部    |
| 白骨精    | 女     | 财务部    |
+-----------+--------+-----------+
5 rows in set (0.00 sec)

重复输好几次表名不免有些繁琐,所以一般我们来进行多表的处理的话,会给表起个别名,并且在写的时候要注意分行,方便我们以后加注释等操作。

mysql> SELECT
    -> t1.name,
    -> t1.gender,
    -> t2.name  -- 部门的名称
    -> FROM
    -> emp t1,
    -> dept t2
    -> WHERE
    -> t1.`dept_id` = t2.`id`;
+-----------+--------+-----------+
| name      | gender | name      |
+-----------+--------+-----------+
| 孙悟空    | 男     | 开发部    |
| 蜘蛛精    | 女     | 开发部    |
| 猪八戒    | 男     | 市场部    |
| 唐僧      | 男     | 市场部    |
| 白骨精    | 女     | 财务部    |
+-----------+--------+-----------+
5 rows in set (0.00 sec)

 

  • 显式内连接:基本语法–  SELECT  字段列表  FROM  表名  [INNER] JOIN  表名2  ON  条件
mysql> SELECT
    -> t1.name,
    -> t1.gender,
    -> t2.name
    -> FROM
    -> emp t1  -- 起别名
    -> INNER JOIN  -- INNER可以省略
    -> dept t2  -- 起别名
    -> ON  -- 限制条件
    -> t1.`dept_id` = t2.`id`;
+-----------+--------+-----------+
| name      | gender | name      |
+-----------+--------+-----------+
| 孙悟空    | 男     | 开发部    |
| 蜘蛛精    | 女     | 开发部    |
| 猪八戒    | 男     | 市场部    |
| 唐僧      | 男     | 市场部    |
| 白骨精    | 女     | 财务部    |
+-----------+--------+-----------+
5 rows in set (0.00 sec)

内连接显式查询中,INNER这个关键字是可以省略的。

总结: 内连接显式查询的是两表其交集的部分(是否交集由条件ON判断)

  • 内连接查询注意事项:
    • 从哪些表中查询数据
    • 查询条件是什么
    • 查询哪些字段

 

外连接查询

  • 左外连接:语法– SELECT  字段列表  FROM  表1  LEFT  [OUTER]  JOIN  表2  ON  条件

我们在之前例子的基础上升级,给emp表添加一个新员工——小白龙,因为刚入职,所以他没有部门。

+----+-----------+--------+--------+------------+---------+
| id | name      | gender | salary | join_date  | dept_id |
+----+-----------+--------+--------+------------+---------+
|  1 | 孙悟空    | 男     |   7200 | 2013-02-24 |       1 |
|  2 | 猪八戒    | 男     |   3600 | 2012-07-04 |       2 |
|  3 | 唐僧      | 男     |   9000 | 2015-01-29 |       2 |
|  4 | 白骨精    | 女     |   3000 | 2010-04-20 |       3 |
|  5 | 蜘蛛精    | 女     |   6500 | 2016-07-25 |       1 |
|  6 | 小白龙    | 男     |   3000 | NULL       |    NULL |
+----+-----------+--------+--------+------------+---------+
6 rows in set (0.00 sec)

需求:查询所有员工信息,如果员工有所属部门,则查询部门的名称;没有部门则不显示部门的名称。

//使用内连接隐式查询,结果没有“小白龙”,不符合需求
mysql> SELECT
    -> t1.*,
    -> t2.`name`
    -> FROM
    -> emp t1,
    -> dept t2
    -> WHERE
    -> t1.`dept_id` = t2.`id`;
+----+-----------+--------+--------+------------+---------+-----------+
| id | name      | gender | salary | join_date  | dept_id | name      |
+----+-----------+--------+--------+------------+---------+-----------+
|  1 | 孙悟空    | 男     |   7200 | 2013-02-24 |       1 | 开发部    |
|  5 | 蜘蛛精    | 女     |   6500 | 2016-07-25 |       1 | 开发部    |
|  2 | 猪八戒    | 男     |   3600 | 2012-07-04 |       2 | 市场部    |
|  3 | 唐僧      | 男     |   9000 | 2015-01-29 |       2 | 市场部    |
|  4 | 白骨精    | 女     |   3000 | 2010-04-20 |       3 | 财务部    |
+----+-----------+--------+--------+------------+---------+-----------+
5 rows in set (0.01 sec)

使用内连接隐式查询,结果没有“小白龙”,不符合需求,因为小白龙的dept_id值是NULL,自然不会有匹配的id值。

接下来我们使用左外连接查询:

mysql> SELECT
    -> t1.*,
    -> t2.`name`
    -> FROM
    -> emp t1
    -> LEFT OUTER JOIN
    -> dept t2
    -> ON
    -> t1.`dept_id` = t2.`id`;
+----+-----------+--------+--------+------------+---------+-----------+
| id | name      | gender | salary | join_date  | dept_id | name      |
+----+-----------+--------+--------+------------+---------+-----------+
|  1 | 孙悟空    | 男     |   7200 | 2013-02-24 |       1 | 开发部    |
|  5 | 蜘蛛精    | 女     |   6500 | 2016-07-25 |       1 | 开发部    |
|  2 | 猪八戒    | 男     |   3600 | 2012-07-04 |       2 | 市场部    |
|  3 | 唐僧      | 男     |   9000 | 2015-01-29 |       2 | 市场部    |
|  4 | 白骨精    | 女     |   3000 | 2010-04-20 |       3 | 财务部    |
|  6 | 小白龙    | 男     |   3000 | NULL       |    NULL | NULL      |
+----+-----------+--------+--------+------------+---------+-----------+
6 rows in set (0.01 sec)

总结:左外连接查询的是左表所有数据以及其交集的部分。

 

  • 右外连接:语法– SELECT  字段列表  FROM  表1  RIGHT  [OUTER]  JOIN  表2  ON  条件

结论:右外连接查询的是右表所有数据以及其交集的部分。

mysql>  SELECT
    ->  t1.*,
    ->  t2.`name`
    -> FROM
    ->  emp t1
    ->  RIGHT OUTER JOIN
    -> dept t2
    -> ON
    -> t1.`dept_id` = t2.`id`;
+------+-----------+--------+--------+------------+---------+-----------+
| id   | name      | gender | salary | join_date  | dept_id | name      |
+------+-----------+--------+--------+------------+---------+-----------+
|    1 | 孙悟空    | 男     |   7200 | 2013-02-24 |       1 | 开发部    |
|    5 | 蜘蛛精    | 女     |   6500 | 2016-07-25 |       1 | 开发部    |
|    2 | 猪八戒    | 男     |   3600 | 2012-07-04 |       2 | 市场部    |
|    3 | 唐僧      | 男     |   9000 | 2015-01-29 |       2 | 市场部    |
|    4 | 白骨精    | 女     |   3000 | 2010-04-20 |       3 | 财务部    |
+------+-----------+--------+--------+------------+---------+-----------+
5 rows in set (0.00 sec)

“小白龙”消失了,因为“小白龙”既不是右表(dept)的内容,也不是两表的交集(不满足ON后面的条件),所以没有查询出来。

 

子查询

概念:子查询就是在查询中嵌套查询,称嵌套查询为子查询。

以上例继续,我们来查询最高工资的员工。

mysql> SELECT
    -> *
    -> FROM
    -> emp t1
    -> WHERE
    -> t1.`salary` = (
    -> SELECT
    -> MAX(salary)
    -> FROM
    -> emp
    -> );
+----+--------+--------+--------+------------+---------+
| id | name   | gender | salary | join_date  | dept_id |
+----+--------+--------+--------+------------+---------+
|  3 | 唐僧   | 男     |   9000 | 2015-01-29 |       2 |
+----+--------+--------+--------+------------+---------+
1 row in set (0.01 sec)

语句并不难理解,其实就是查询中嵌套查询,子查询的结果作为查询条件继续查询。

子查询的难点在于存在不同的情况,我们下面来依次介绍:

  • 子查询的结果是单行单列的:

该情况下,子查询可以作为一个条件,使用运算符去判断。

再看个示例,查看员工中工资小于平均工资的人:

mysql> SELECT
    -> *
    -> FROM
    -> emp t1
    -> WHERE
    -> t1.`salary` < (
    -> SELECT
    -> AVG(salary)
    -> FROM
    -> emp
    -> );
+----+-----------+--------+--------+------------+---------+
| id | name      | gender | salary | join_date  | dept_id |
+----+-----------+--------+--------+------------+---------+
|  2 | 猪八戒    | 男     |   3600 | 2012-07-04 |       2 |
|  4 | 白骨精    | 女     |   3000 | 2010-04-20 |       3 |
|  6 | 小白龙    | 男     |   3000 | NULL       |    NULL |
+----+-----------+--------+--------+------------+---------+
3 rows in set (0.00 sec)

注意,在子查询中,不能使用总查询中定义的别名。

 

  • 子查询的结果是多行单列的:

示例,查询“财务部”和“市场部”所有的员工信息

查询两个部门,就要先获得dept表中两个部门各自的id,然后在emp表中符合id1或者id2的员工,这样我们的子查询得到的会是一个单列多行的结果,我们要怎么应用呢?看示例:

SELECT  name,gender FROM emp   WHERE dept_id IN (
SELECT id FROM  dept  WHERE NAME='财务部' OR NAME='市场部' );

name       gender  
---------  --------
白骨精        女     
猪八戒        男     
唐僧         男     

 

  • 子查询的结果是多行多列的:

该情况下,子查询不再可以作为WHERE后面的条件,而是可以作为一张虚拟表,再结合内连接查询等方法去查询。

示例:查询员工的入职日期是2013-11-11日之后的员工信息和部门信息。

SELECT * FROM 
dept t1,(
SELECT * FROM emp 
WHERE emp.`join_date` > '2013-11-11'
) t2  -- 多行多列的大于规定日期的员工表
WHERE t1.`id`=t2.`dept_id`;



    id  name           id  name       gender  salary  join_date   dept_id  
------  ---------  ------  ---------  ------  ------  ----------  ---------
     2  市场部             3  唐僧         男         9000  2015-01-29          2
     1  开发部             5  蜘蛛精        女         6500  2016-07-25          1

即先从原始员工表中筛选出一张“日期大于‘2013-11-11’”的员工表,然后该升级员工表与部门表进行隐式内连接查询即可。

其实这个我们使用内连接查询也可以实现:

SELECT * FROM 
emp t1,
dept t2
WHERE t1.`dept_id` = t2.`id` 
AND t1.`join_date` > '2013-11-11';


    id  name       gender  salary  join_date   dept_id      id  name       
------  ---------  ------  ------  ----------  -------  ------  -----------
     3  唐僧         男         9000  2015-01-29        2       2  市场部  
     5  蜘蛛精        女         6500  2016-07-25        1       1  开发部  

 


已经写了不少内容了,我们把剩下的部分留给后面的文章

 

 

 

 

商业转载 请联系作者获得授权,非商业转载 请标明出处,谢谢

 

 

发表评论

是的,我就是计算机界的枭雄!