MySql学习笔记：一文上手MySql

2023-11-20

MySql学习笔记

\quad
PS：本文整理的笔记来自于B站视频：老杜带你学_mysql入门基础（mysql基础视频+数据库实战)。视频讲的很好，值得大家一看~

\quad

一、MySql安装及概述

1.1 MySQL安装

MySql安装包下载链接：https://dev.mysql.com/downloads/mysql/

下载完后，我们将 zip 包解压到相应的目录，这里我将解压后的文件夹放在 C:\web\mysql-8.0.11 下。

接下来我们需要配置下 MySQL 的配置文件

打开刚刚解压的文件夹 C:\web\mysql-8.0.11 ，在该文件夹下创建 my.ini 配置文件，编辑 my.ini 配置以下基本信息：

[client]
# 设置mysql客户端默认字符集
default-character-set=utf8
 
[mysqld]
# 设置3306端口
port = 3306
# 设置mysql的安装目录
basedir=C:\\web\\mysql-8.0.11
# 设置 mysql数据库的数据的存放目录，MySQL 8+ 不需要以下配置，系统自己生成即可，否则有可能报错
# datadir=C:\\web\\sqldata
# 允许最大连接数
max_connections=20
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB

cd D:\Software\mysql\bin

安装mysql的服务：

mysqld --install

初始化mysql，在这里，初始化会产生一个随机密码：

mysqld --initialize --console

开启mysql的服务：

net start mysql

登录验证，mysql是否安装成功！要注意上面产生的随机密码，不包括前面符号前面的空格，否则会登陆失败

mysql -u root -p

改密码：（这里是改为空密码）

alter user 'root'@'localhost' identified by '';

设置系统的全局变量： 为了方便登录操作mysql，设置一个全局变量。（环境变量）

端口号：3306 服务名字：MySQL

1.2 SQL的分类

数据查询语言（DQL：Data Query Language）

select

数据操纵语言（DML：Data Manipulation Language）

insert   delete   update

数据定义语言（DDL：Data Definition Language）

create   drop   alter

事务控制语言（TCL：Transactional Control Language）

commit   rollback

数据控制语言（DCL：Data Control Language）

grant   revoke

1.3 导入数据

mysql> create database bjpowernode;
mysql> use bjpowernode
mysql> source D:\bjpowernode.sql
mysql> drop database bjpowernode;  # 删除数据库

\quad

二、常用命令

2.1 退出 mysql

可使用\q、QUIT或 EXIT

2.2 查看mysql中有哪些数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sqltest            |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

注意：SQL语句以;结尾！！

2.3 查看表

show tables;

2.4 选择使用某个数据库

used test

在数据库中建立表，因此创建表的时候必须要先选择数据库。

2.5 创建数据库

create database test;

2.6 查看数据库的版本号

select version();

2.7 查询当前使用的数据库

select database();

2.8 终止一条语句

如果想要终止一条正在编写的语句，可键入\c。

2.9 导入数据库

source D:\Software\test.sql

注意：路径不要有中文

2.10 查看其他库中的表

mysql> show tables from mysql;
+------------------------------------------------------+
| Tables_in_mysql                                      |
+------------------------------------------------------+
| columns_priv                                         |
| component                                            |
| db                                                   |
| default_roles                                        |
| engine_cost                                          |
| func                                                 |
| general_log                                          |
| global_grants                                        |
| gtid_executed                                        |
| help_category                                        |
| help_keyword                                         |
| help_relation                                        |
| help_topic                                           |
| innodb_index_stats                                   |
| innodb_table_stats                                   |
| password_history                                     |
| plugin                                               |
| procs_priv                                           |
| proxies_priv                                         |
| replication_asynchronous_connection_failover         |
| replication_asynchronous_connection_failover_managed |
| replication_group_configuration_version              |
| replication_group_member_actions                     |
| role_edges                                           |
| server_cost                                          |
| servers                                              |
| slave_master_info                                    |
| slave_relay_log_info                                 |
| slave_worker_info                                    |
| slow_log                                             |
| tables_priv                                          |
| time_zone                                            |
| time_zone_leap_second                                |
| time_zone_name                                       |
| time_zone_transition                                 |
| time_zone_transition_type                            |
| user                                                 |
+------------------------------------------------------+
37 rows in set (0.01 sec)

注意：SQL语句不区分大小写！！

MySQL 在 Windows 下都不区分大小写。MySQL 在 Linux 下数据库名、表名、列名、别名大小写的规则：

数据库名与表名是严格区分大小写的；
表的别名是严格区分大小写的；
列名与列的别名在所有的情况下均是忽略大小写的；
变量名也是严格区分大小写的。
\quad

三、一般查询

3.1 查询表结构

desc不看表结构。

desc <table name>;

例如：

mysql> desc emp;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| EMPNO    | int         | NO   | PRI | NULL    |       |
| ENAME    | varchar(10) | YES  |     | NULL    |       |
| JOB      | varchar(9)  | YES  |     | NULL    |       |
| MGR      | int         | YES  |     | NULL    |       |
| HIREDATE | date        | YES  |     | NULL    |       |
| SAL      | double(7,2) | YES  |     | NULL    |       |
| COMM     | double(7,2) | YES  |     | NULL    |       |
| DEPTNO   | int         | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
8 rows in set (0.01 sec)

3.2 查询一个字段

select 字段名 from <table name>;

select语句后面跟的是字段名称，select是关键字，select和字段名称之间采用空格隔开，from表示将要查询的表，它和字段之间采用空格隔开。

3.3 查询多个字段

select 字段名1,字段名2 from <table name>;

查询多个字段，select中的字段采用逗号间隔即可。

3.4 查询全部字段

select * from <table name>;

效率低，可读性差，实际开发中不建议。

3.5 给查询的列起别名

as可以省略。

select 字段名 as 名字 from <table name>

注意：select语句永远不会进行修改操作

例如：

mysql> select deptno, dname as "dept name" from dept; 
+--------+------------+
| deptno | dept name  |
+--------+------------+
|     10 | ACCOUNTING |
|     20 | RESEARCH   |
|     30 | SALES      |
|     40 | OPERATIONS |
+--------+------------+
4 rows in set (0.00 sec)

名字有空格的话，需要用引号括起来。

3.6 字段可以使用数学表达式

mysql> SELECT ename, sal * 12 as '年薪' from emp;
+--------+----------+
| ename  | 年薪     |
+--------+----------+
| SMITH  |  9600.00 |
| ALLEN  | 19200.00 |
| WARD   | 15000.00 |
| JONES  | 35700.00 |
| MARTIN | 15000.00 |
| BLAKE  | 34200.00 |
| CLARK  | 29400.00 |
| SCOTT  | 36000.00 |
| KING   | 60000.00 |
| TURNER | 18000.00 |
| ADAMS  | 13200.00 |
| JAMES  | 11400.00 |
| FORD   | 36000.00 |
| MILLER | 15600.00 |
+--------+----------+
14 rows in set (0.00 sec)

注意：中文最好也要用引号括起来。

3.7 查看表的创建语句

show create table <table name>;

例如：

mysql> show create table emp;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| emp   | CREATE TABLE `emp` (
  `EMPNO` int NOT NULL,
  `ENAME` varchar(10) DEFAULT NULL,
  `JOB` varchar(9) DEFAULT NULL,
  `MGR` int DEFAULT NULL,
  `HIREDATE` date DEFAULT NULL,
  `SAL` double(7,2) DEFAULT NULL,
  `COMM` double(7,2) DEFAULT NULL,
  `DEPTNO` int DEFAULT NULL,
  PRIMARY KEY (`EMPNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

四、条件查询

不是讲表中所有数据都查出来。是查询出来符合条件的。

语法格式：

select  字段1,字段2,... from 表名 where 条件;

4.1 =

mysql> select ename, sal from emp where sal=800;
+-------+--------+
| ename | sal    |
+-------+--------+
| SMITH | 800.00 |
+-------+--------+
1 row in set (0.00 sec)
mysql> select empno, ename, sal from emp where ename = 'smith';
+-------+-------+--------+
| empno | ename | sal    |
+-------+-------+--------+
|  7369 | SMITH | 800.00 |
+-------+-------+--------+
1 row in set (0.00 sec)

4.2 !=

mysql> select ename,sal from emp where sal != 800;
+--------+---------+
| ename  | sal     |
+--------+---------+
| ALLEN  | 1600.00 |
| WARD   | 1250.00 |
| JONES  | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| SCOTT  | 3000.00 |
| KING   | 5000.00 |
| TURNER | 1500.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| FORD   | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+
13 rows in set (0.00 sec)

<>也可以表示不等于

4.3 < > >= <=

mysql> select ename,sal from emp where sal < 2000;
+--------+---------+
| ename  | sal     |
+--------+---------+
| SMITH  |  800.00 |
| ALLEN  | 1600.00 |
| WARD   | 1250.00 |
| MARTIN | 1250.00 |
| TURNER | 1500.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| MILLER | 1300.00 |
+--------+---------+
8 rows in set (0.00 sec)

4.4 between … and …

mysql> select empno, ename, sal from emp where sal between 2450 and 3000;
+-------+-------+---------+
| empno | ename | sal     |
+-------+-------+---------+
|  7566 | JONES | 2975.00 |
|  7698 | BLAKE | 2850.00 |
|  7782 | CLARK | 2450.00 |
|  7788 | SCOTT | 3000.00 |
|  7902 | FORD  | 3000.00 |
+-------+-------+---------+
5 rows in set (0.00 sec)

注意：between...and...是包含最大值和最小值的，而且必须左小右大

4.5 is null or is not null

mysql> select empno, ename, comm from emp where comm is null;
+-------+--------+------+
| empno | ename  | comm |
+-------+--------+------+
|  7369 | SMITH  | NULL |
|  7566 | JONES  | NULL |
|  7698 | BLAKE  | NULL |
|  7782 | CLARK  | NULL |
|  7788 | SCOTT  | NULL |
|  7839 | KING   | NULL |
|  7876 | ADAMS  | NULL |
|  7900 | JAMES  | NULL |
|  7902 | FORD   | NULL |
|  7934 | MILLER | NULL |
+-------+--------+------+
10 rows in set (0.00 sec)

注意：数据库中的null代表什么也没有，不能用=衡量

4.6 and or

mysql> select empno, ename, job, sal from emp where job = 'manager' and sal > 2500;

+-------+-------+---------+---------+
| empno | ename | job     | sal     |
+-------+-------+---------+---------+
|  7566 | JONES | MANAGER | 2975.00 |
|  7698 | BLAKE | MANAGER | 2850.00 |
+-------+-------+---------+---------+
2 rows in set (0.00 sec)

mysql> select empno, ename, job, sal from emp where job = 'manager' or job = 'salesman';
+-------+--------+----------+---------+
| empno | ename  | job      | sal     |
+-------+--------+----------+---------+
|  7499 | ALLEN  | SALESMAN | 1600.00 |
|  7521 | WARD   | SALESMAN | 1250.00 |
|  7566 | JONES  | MANAGER  | 2975.00 |
|  7654 | MARTIN | SALESMAN | 1250.00 |
|  7698 | BLAKE  | MANAGER  | 2850.00 |
|  7782 | CLARK  | MANAGER  | 2450.00 |
|  7844 | TURNER | SALESMAN | 1500.00 |
+-------+--------+----------+---------+
7 rows in set (0.00 sec)

需要注意：and和or同时出现的话，有优先级问题，and优先级更高，会先执行and。

mysql> select empno, ename, job, sal, deptno from emp where sal > 2500 and (deptno = 10 or deptno = 20);
+-------+-------+-----------+---------+--------+
| empno | ename | job       | sal     | deptno |
+-------+-------+-----------+---------+--------+
|  7566 | JONES | MANAGER   | 2975.00 |     20 |
|  7788 | SCOTT | ANALYST   | 3000.00 |     20 |
|  7839 | KING  | PRESIDENT | 5000.00 |     10 |
|  7902 | FORD  | ANALYST   | 3000.00 |     20 |
+-------+-------+-----------+---------+--------+
4 rows in set (0.00 sec)

4.7 in

mysql> select empno,ename,job from emp where job = 'manager' or job = 'salesman';
+-------+--------+----------+
| empno | ename  | job      |
+-------+--------+----------+
|  7499 | ALLEN  | SALESMAN |
|  7521 | WARD   | SALESMAN |
|  7566 | JONES  | MANAGER  |
|  7654 | MARTIN | SALESMAN |
|  7698 | BLAKE  | MANAGER  |
|  7782 | CLARK  | MANAGER  |
|  7844 | TURNER | SALESMAN |
+-------+--------+----------+
7 rows in set (0.00 sec)

mysql> select empno,ename,job from emp where job in ('manager', 'salesman');
+-------+--------+----------+
| empno | ename  | job      |
+-------+--------+----------+
|  7499 | ALLEN  | SALESMAN |
|  7521 | WARD   | SALESMAN |
|  7566 | JONES  | MANAGER  |
|  7654 | MARTIN | SALESMAN |
|  7698 | BLAKE  | MANAGER  |
|  7782 | CLARK  | MANAGER  |
|  7844 | TURNER | SALESMAN |
+-------+--------+----------+
7 rows in set (0.00 sec)

可以看到如上两个语句是等价的。

mysql> select ename,sal from emp where sal = 800 or sal = 5000;
+-------+---------+
| ename | sal     |
+-------+---------+
| SMITH |  800.00 |
| KING  | 5000.00 |
+-------+---------+
2 rows in set (0.00 sec)

mysql> select ename,sal from emp where sal in (800, 5000);
+-------+---------+
| ename | sal     |
+-------+---------+
| SMITH |  800.00 |
| KING  | 5000.00 |
+-------+---------+
2 rows in set (0.00 sec)

上面两句同理。

注意：in不是区间。

4.8 模糊查询

like支持%或_匹配：

%匹配任意多个字符
_匹配任意一个字符

下面列举一些例子：

找出名字中含有O的：

mysql> select ename from emp where ename like '%O%';
+-------+
| ename |
+-------+
| JONES |
| SCOTT |
| FORD  |
+-------+
3 rows in set (0.00 sec)

找出名字以T结尾的：

mysql> select ename from emp where ename like '%T';
+-------+
| ename |
+-------+
| SCOTT |
+-------+
1 row in set (0.00 sec)

找出名字以K开始的：

mysql> select ename from emp where ename like 'K%';
+-------+
| ename |
+-------+
| KING  |
+-------+
1 row in set (0.00 sec)

找出第二个字母是A的：

mysql> select ename from emp where ename like '_A%';
+--------+
| ename  |
+--------+
| WARD   |
| MARTIN |
| JAMES  |
+--------+
3 rows in set (0.00 sec)

找出第三个字母是R的：

mysql> select ename from emp where ename like '__R%';
+--------+
| ename  |
+--------+
| WARD   |
| MARTIN |
| TURNER |
| FORD   |
+--------+
4 rows in set (0.00 sec)

\quad

五、排序

排序采用 order by 子句，order by 后面跟上排序字段，排序字段可以放多个，多个采用逗号间隔，order by默认采用升序，如果存在 where子句那么 order by 必须放到 where语句的后面。

5.1 单一字段排序

查询所有员工薪资，排序：（默认升序）

mysql> select ename, sal from emp order by sal;
+--------+---------+
| ename  | sal     |
+--------+---------+
| SMITH  |  800.00 |
| JAMES  |  950.00 |
| ADAMS  | 1100.00 |
| WARD   | 1250.00 |
| MARTIN | 1250.00 |
| MILLER | 1300.00 |
| TURNER | 1500.00 |
| ALLEN  | 1600.00 |
| CLARK  | 2450.00 |
| BLAKE  | 2850.00 |
| JONES  | 2975.00 |
| SCOTT  | 3000.00 |
| FORD   | 3000.00 |
| KING   | 5000.00 |
+--------+---------+
14 rows in set (0.00 sec)

5.2 多个字段排序

按照多个字段排序，如：首先按照 sal 排序，再按照 ename 排序

mysql> select ename, sal from emp order by sal, ename;
+--------+---------+
| ename  | sal     |
+--------+---------+
| SMITH  |  800.00 |
| JAMES  |  950.00 |
| ADAMS  | 1100.00 |
| MARTIN | 1250.00 |
| WARD   | 1250.00 |
| MILLER | 1300.00 |
| TURNER | 1500.00 |
| ALLEN  | 1600.00 |
| CLARK  | 2450.00 |
| BLAKE  | 2850.00 |
| JONES  | 2975.00 |
| FORD   | 3000.00 |
| SCOTT  | 3000.00 |
| KING   | 5000.00 |
+--------+---------+
14 rows in set (0.00 sec)

5.3 手动指定排序顺序

默认是升序，可以指定顺序。

降序：desc
升序：asc

手动指定按照薪水由大到小排序

mysql> select ename, sal from emp order by sal desc;
+--------+---------+
| ename  | sal     |
+--------+---------+
| KING   | 5000.00 |
| SCOTT  | 3000.00 |
| FORD   | 3000.00 |
| JONES  | 2975.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| ALLEN  | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| WARD   | 1250.00 |
| MARTIN | 1250.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| SMITH  |  800.00 |
+--------+---------+
14 rows in set (0.00 sec)

按照 job 和薪水倒序

mysql> select ename, job, sal from emp order by job desc, sal desc;
+--------+-----------+---------+
| ename  | job       | sal     |
+--------+-----------+---------+
| ALLEN  | SALESMAN  | 1600.00 |
| TURNER | SALESMAN  | 1500.00 |
| WARD   | SALESMAN  | 1250.00 |
| MARTIN | SALESMAN  | 1250.00 |
| KING   | PRESIDENT | 5000.00 |
| JONES  | MANAGER   | 2975.00 |
| BLAKE  | MANAGER   | 2850.00 |
| CLARK  | MANAGER   | 2450.00 |
| MILLER | CLERK     | 1300.00 |
| ADAMS  | CLERK     | 1100.00 |
| JAMES  | CLERK     |  950.00 |
| SMITH  | CLERK     |  800.00 |
| SCOTT  | ANALYST   | 3000.00 |
| FORD   | ANALYST   | 3000.00 |
+--------+-----------+---------+
14 rows in set (0.00 sec)
mysql> select ename, job, sal from emp order by sal desc, job desc;
+--------+-----------+---------+
| ename  | job       | sal     |
+--------+-----------+---------+
| KING   | PRESIDENT | 5000.00 |
| SCOTT  | ANALYST   | 3000.00 |
| FORD   | ANALYST   | 3000.00 |
| JONES  | MANAGER   | 2975.00 |
| BLAKE  | MANAGER   | 2850.00 |
| CLARK  | MANAGER   | 2450.00 |
| ALLEN  | SALESMAN  | 1600.00 |
| TURNER | SALESMAN  | 1500.00 |
| MILLER | CLERK     | 1300.00 |
| WARD   | SALESMAN  | 1250.00 |
| MARTIN | SALESMAN  | 1250.00 |
| ADAMS  | CLERK     | 1100.00 |
| JAMES  | CLERK     |  950.00 |
| SMITH  | CLERK     |  800.00 |
+--------+-----------+---------+
14 rows in set (0.00 sec)

排序的字段要注意顺序，先排哪个。注意看上面两个的区别。

根据字段的位置也可以排序：按照第二列排序

mysql> select ename,job,sal from emp order by 2;
+--------+-----------+---------+
| ename  | job       | sal     |
+--------+-----------+---------+
| SCOTT  | ANALYST   | 3000.00 |
| FORD   | ANALYST   | 3000.00 |
| SMITH  | CLERK     |  800.00 |
| ADAMS  | CLERK     | 1100.00 |
| JAMES  | CLERK     |  950.00 |
| MILLER | CLERK     | 1300.00 |
| JONES  | MANAGER   | 2975.00 |
| BLAKE  | MANAGER   | 2850.00 |
| CLARK  | MANAGER   | 2450.00 |
| KING   | PRESIDENT | 5000.00 |
| ALLEN  | SALESMAN  | 1600.00 |
| WARD   | SALESMAN  | 1250.00 |
| MARTIN | SALESMAN  | 1250.00 |
| TURNER | SALESMAN  | 1500.00 |
+--------+-----------+---------+
14 rows in set (0.00 sec)

不建议使用此种方式，采用数字含义不明确，程序不健壮。

工资在1250-300之间的员工信息，要求按照薪资降序排列：

mysql> select ename, sal from emp where sal between 1250 and 3000 order by sal desc;
+--------+---------+
| ename  | sal     |
+--------+---------+
| SCOTT  | 3000.00 |
| FORD   | 3000.00 |
| JONES  | 2975.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| ALLEN  | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| WARD   | 1250.00 |
| MARTIN | 1250.00 |
+--------+---------+
10 rows in set (0.00 sec)

关键字顺序不能变：

select
	...
from
	...
where
	...
order by
	...

执行顺序：

from
where
select
order by

排序总是在最后执行！

\quad

六、数据处理函数

数据处理函数又被称为单行处理函数，其特点是：一个输入对应一个输出。与之对应的是多行处理函数，其特点是：多个输入对应一个输出。

常见的单行处理函数：

6.1 lower upper

lower 转小写： upper 转大写

mysql> select lower(ename) from emp;
+--------------+
| lower(ename) |
+--------------+
| smith        |
| allen        |
| ward         |
| jones        |
| martin       |
| blake        |
| clark        |
| scott        |
| king         |
| turner       |
| adams        |
| james        |
| ford         |
| miller       |
+--------------+
14 rows in set (0.00 sec)

6.2 substr

substr 取子串，格式：

substr(被截取的字符串，起始下标，截取的长度)

起始下标从1开始

mysql> select substr(ename, 1, 3) as ename from emp;
+-------+
| ename |
+-------+
| SMI   |
| ALL   |
| WAR   |
| JON   |
| MAR   |
| BLA   |
| CLA   |
| SCO   |
| KIN   |
| TUR   |
| ADA   |
| JAM   |
| FOR   |
| MIL   |
+-------+
14 rows in set (0.00 sec)

首字母大写：

select concat(upper(substr(ename, 1, 1)),lower(substr(ename, 2, length(ename) - 1))) as result from emp;

6.3 concat

concat 字符串拼接

mysql> select concat(empno, '-', ename) from emp;
+---------------------------+
| concat(empno, '-', ename) |
+---------------------------+
| 7369-SMITH                |
| 7499-ALLEN                |
| 7521-WARD                 |
| 7566-JONES                |
| 7654-MARTIN               |
| 7698-BLAKE                |
| 7782-CLARK                |
| 7788-SCOTT                |
| 7839-KING                 |
| 7844-TURNER               |
| 7876-ADAMS                |
| 7900-JAMES                |
| 7902-FORD                 |
| 7934-MILLER               |
+---------------------------+
14 rows in set (0.00 sec)

进阶：首字母大写。

mysql> select concat(upper(substr(ename, 1, 1)), lower(substr(ename, 2, length(ename) - 1)))  as name from emp;
+--------+
| name   |
+--------+
| Smith  |
| Allen  |
| Ward   |
| Jones  |
| Martin |
| Blake  |
| Clark  |
| Scott  |
| King   |
| Turner |
| Adams  |
| James  |
| Ford   |
| Miller |
+--------+
14 rows in set (0.00 sec)

6.4 length

length 取长度

mysql> select length(ename) as enameLength from emp;
+-------------+
| enameLength |
+-------------+
|           5 |
|           5 |
|           4 |
|           5 |
|           6 |
|           5 |
|           5 |
|           5 |
|           4 |
|           6 |
|           5 |
|           5 |
|           4 |
|           6 |
+-------------+
14 rows in set (0.00 sec)

6.5 trim

trim 去除空白

mysql> select * from emp where ename = trim('     King');
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
+-------+-------+-----------+------+------------+---------+------+--------+
1 row in set (0.00 sec)

trim只会去首尾空格，不会去除中间的空格。

6.6 round

round 四舍五入

mysql> select round(123.56354363, 2);
+------------------------+
| round(123.56354363, 2) |
+------------------------+
|                 123.56 |
+------------------------+
1 row in set (0.00 sec)
mysql> select round(123.56354363, -2);
+-------------------------+
| round(123.56354363, -2) |
+-------------------------+
|                     100 |
+-------------------------+
1 row in set (0.00 sec)

注意：-1保留到十位，-2会保留到百位。

6.7 rand

rand 生成随机数

mysql> select rand();
+---------------------+
| rand()              |
+---------------------+
| 0.12361311458387703 |
+---------------------+
1 row in set (0.00 sec)
mysql> select round(rand()*100);
+-------------------+
| round(rand()*100) |
+-------------------+
|                93 |
+-------------------+
1 row in set (0.00 sec)

随机抽取记录数

mysql> select ename, sal from emp order by rand() limit 5;
+-------+---------+
| ename | sal     |
+-------+---------+
| SMITH |  800.00 |
| FORD  | 3000.00 |
| SCOTT | 3000.00 |
| ADAMS | 1100.00 |
| CLARK | 2450.00 |
+-------+---------+
5 rows in set (0.00 sec)

order by不能省。

6.8 ifnull

ifnull可以将 null 转换成一个具体值。ifnull是空处理函数，专门处理空的。

在所有数据库当中，只要有 null 参与的数学运算，最终结果就是 null。比如下面的代码：

mysql> select ename, (sal+comm) * 12 as yearsal from emp;
+--------+----------+
| ename  | yearsal  |
+--------+----------+
| SMITH  |     NULL |
| ALLEN  | 22800.00 |
| WARD   | 21000.00 |
| JONES  |     NULL |
| MARTIN | 31800.00 |
| BLAKE  |     NULL |
| CLARK  |     NULL |
| SCOTT  |     NULL |
| KING   |     NULL |
| TURNER | 18000.00 |
| ADAMS  |     NULL |
| JAMES  |     NULL |
| FORD   |     NULL |
| MILLER |     NULL |
+--------+----------+
14 rows in set (0.00 sec)

为了避免这个现象，需要使用ifnull。

ifnull 函数用法：ifnull(data, value)

mysql> select ename, (sal+ifnull(comm, 0)) * 12 as yearsal from emp;
+--------+----------+
| ename  | yearsal  |
+--------+----------+
| SMITH  |  9600.00 |
| ALLEN  | 22800.00 |
| WARD   | 21000.00 |
| JONES  | 35700.00 |
| MARTIN | 31800.00 |
| BLAKE  | 34200.00 |
| CLARK  | 29400.00 |
| SCOTT  | 36000.00 |
| KING   | 60000.00 |
| TURNER | 18000.00 |
| ADAMS  | 13200.00 |
| JAMES  | 11400.00 |
| FORD   | 36000.00 |
| MILLER | 15600.00 |
+--------+----------+
14 rows in set (0.00 sec)

6.9 case…when…then…when…then…else…end

如果 job 为 MANAGERG 薪水上涨 10%，如果 job 为 SALESMAN 工资上涨 50%，其他的工资不动。

mysql> select ename, job, (case job when 'manager' then 1.1*sal when 'salesman' then sal*1.5 else sal end) as newsal from emp;
+--------+-----------+---------+
| ename  | job       | newsal  |
+--------+-----------+---------+
| SMITH  | CLERK     |  800.00 |
| ALLEN  | SALESMAN  | 2400.00 |
| WARD   | SALESMAN  | 1875.00 |
| JONES  | MANAGER   | 3272.50 |
| MARTIN | SALESMAN  | 1875.00 |
| BLAKE  | MANAGER   | 3135.00 |
| CLARK  | MANAGER   | 2695.00 |
| SCOTT  | ANALYST   | 3000.00 |
| KING   | PRESIDENT | 5000.00 |
| TURNER | SALESMAN  | 2250.00 |
| ADAMS  | CLERK     | 1100.00 |
| JAMES  | CLERK     |  950.00 |
| FORD   | ANALYST   | 3000.00 |
| MILLER | CLERK     | 1300.00 |
+--------+-----------+---------+
14 rows in set (0.00 sec)

不修改数据库，只是将查询结果调整显示。

6.10 str_to_date

查询 1981-02-20 入职的员工

第一种方法，与数据库的格式匹配上：

mysql> select * from emp where hiredate='1981-02-20';
+-------+-------+----------+------+------------+---------+--------+--------+
| EMPNO | ENAME | JOB      | MGR  | HIREDATE   | SAL     | COMM   | DEPTNO |
+-------+-------+----------+------+------------+---------+--------+--------+
|  7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 |     30 |
+-------+-------+----------+------+------------+---------+--------+--------+
1 row in set (0.00 sec)

第二种方法，将字符串转换成 date类型：

mysql> select * from emp where hiredate=str_to_date('1981-02-20', '%Y-%m-%d');
+-------+-------+----------+------+------------+---------+--------+--------+
| EMPNO | ENAME | JOB      | MGR  | HIREDATE   | SAL     | COMM   | DEPTNO |
+-------+-------+----------+------+------------+---------+--------+--------+
|  7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 |     30 |
+-------+-------+----------+------+------------+---------+--------+--------+
1 row in set (0.00 sec)

mysql> select * from emp where hiredate=str_to_date('02-20-1981', '%m-%d-%Y');
+-------+-------+----------+------+------------+---------+--------+--------+
| EMPNO | ENAME | JOB      | MGR  | HIREDATE   | SAL     | COMM   | DEPTNO |
+-------+-------+----------+------+------------+---------+--------+--------+
|  7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 |     30 |
+-------+-------+----------+------+------------+---------+--------+--------+
1 row in set (0.00 sec)

str_to_date可以将字符串转换成日期，具体格式为str_to_date (字符串, 匹配格式)

6.11 date_format

查询 1981-02-20 以后入职的员工，将入职日期格式化成 yyyy-mm-dd hh:mm:ss

mysql> select empno, ename, date_format(hiredate, '%Y-%m-%d %H:%i:%s') as hiredate
from emp;
+-------+--------+---------------------+
| empno | ename  | hiredate            |
+-------+--------+---------------------+
|  7369 | SMITH  | 1980-12-17 00:00:00 |
|  7499 | ALLEN  | 1981-02-20 00:00:00 |
|  7521 | WARD   | 1981-02-22 00:00:00 |
|  7566 | JONES  | 1981-04-02 00:00:00 |
|  7654 | MARTIN | 1981-09-28 00:00:00 |
|  7698 | BLAKE  | 1981-05-01 00:00:00 |
|  7782 | CLARK  | 1981-06-09 00:00:00 |
|  7788 | SCOTT  | 1987-04-19 00:00:00 |
|  7839 | KING   | 1981-11-17 00:00:00 |
|  7844 | TURNER | 1981-09-08 00:00:00 |
|  7876 | ADAMS  | 1987-05-23 00:00:00 |
|  7900 | JAMES  | 1981-12-03 00:00:00 |
|  7902 | FORD   | 1981-12-03 00:00:00 |
|  7934 | MILLER | 1982-01-23 00:00:00 |
+-------+--------+---------------------+
14 rows in set (0.00 sec)

获取当前时间：

mysql> select date_format(now(),'%Y-%m-%d %H %i %s');
+----------------------------------------+
| date_format(now(),'%Y-%m-%d %H %i %s') |
+----------------------------------------+
| 2022-05-30 15 12 40                    |
+----------------------------------------+
1 row in set (0.00 sec)

now()获得当前时间。

日期格式的说明：

%Y	代表 4 位的年份
%y	代表 2 位的年份
%m	代表月，格式为(01……12)
%c	代表月，格式为(1……12)
%H	代表小时，格式为(00……23)
%h	代表小时，格式为(01……12)
%i	代表分钟，格式为(00……59)
%r	代表时间，格式为 12 小时(hh:mm:ss [AP]M)
%T	代表时间，格式为 24 小时(hh:mm:ss)
%S	代表秒，格式为(00……59)
%s	代表秒，格式为(00……59)

\quad

七、分组函数/聚合函数/多行处理函数

多行处理函数的特点：输入多行，最终输出一行。

count
sum
avg
max
min

分组函数在使用时必须先进行分组才能使用。如果没有对数据进行分组，整张表默认为一组。

注意事项：

分组函数自动忽略 NULL，不需要提前对 NULL 进行处理。
count(*)与count(comm)的区别？

具体字段：表示统计该字段下所有不为 NULL 的元素的总数；
*：统计表当中的总行数。

每一行记录不可能都为 NULL，一行数据中有一列不为 NULL，则这行数据就是有效的。

分组函数不能够直接使用在where子句中。

mysql> select ename,sal from emp where sal > avg(sal);
ERROR 1111 (HY000): Invalid use of group function

所有的分组函数可以组合起来一起用。

7.1 count

取得所有的员工数

mysql> select count(*) from emp;
+----------+
| count(*) |
+----------+
|       14 |
+----------+
1 row in set (0.00 sec)

Count(*)表示取得所有记录，忽略 null，null 的值也会取的。

取得津贴不为 null 员工数

mysql> select count(comm) from emp;
+-------------+
| count(comm) |
+-------------+
|           4 |
+-------------+
1 row in set (0.00 sec)

采用 count(字段名称)，不会取得为null的记录

取得工作岗位的个数

mysql> select count(distinct job) from emp;
+---------------------+
| count(distinct job) |
+---------------------+
|                   5 |
+---------------------+
1 row in set (0.00 sec)

7.2 sum

sum可以取得某一个列的和，null会被忽略。

取得薪水的合计

mysql> select sum(sal) from emp;
+----------+
| sum(sal) |
+----------+
| 29025.00 |
+----------+
1 row in set (0.00 sec)

取得津贴的合计

mysql> select sum(comm) from emp;
+-----------+
| sum(comm) |
+-----------+
|   2200.00 |
+-----------+
1 row in set (0.00 sec)

取得薪水的合计（sal+comm）

mysql> select sum(sal + comm) from emp;
+-----------------+
| sum(sal + comm) |
+-----------------+
|         7800.00 |
+-----------------+
1 row in set (0.00 sec)

上面这个方法是错的，sal+comm会导致null的出现，最终sum不会处理这些null值，可以用下面的方法处理这个问题：

mysql> select sum(sal + ifnull(comm, 0)) from emp;
+----------------------------+
| sum(sal + ifnull(comm, 0)) |
+----------------------------+
|                   31225.00 |
+----------------------------+
1 row in set (0.00 sec)

mysql> select sum(sal) + sum(comm) from emp;
+----------------------+
| sum(sal) + sum(comm) |
+----------------------+
|             31225.00 |
+----------------------+
1 row in set (0.00 sec)

7.3 avg

取得平均薪水

mysql> select avg(sal) from emp;
+-------------+
| avg(sal)    |
+-------------+
| 2073.214286 |
+-------------+

7.4 max

取得最高薪水

mysql> select max(sal) from emp;
+----------+
| max(sal) |
+----------+
|  5000.00 |
+----------+
1 row in set (0.00 sec)

取得最晚入职的员工：

mysql> select empno, ename, hiredate from emp where hiredate = (select max(hiredate) from emp);
+-------+-------+------------+
| empno | ename | hiredate   |
+-------+-------+------------+
|  7876 | ADAMS | 1987-05-23 |
+-------+-------+------------+
1 row in set (0.00 sec)

mysql> select max(str_to_date(hiredate, '%Y-%m-%d')) from emp;
+----------------------------------------+
| max(str_to_date(hiredate, '%Y-%m-%d')) |
+----------------------------------------+
| 1987-05-23                             |
+----------------------------------------+
1 row in set (0.00 sec)

7.5 min

取得最低薪水

mysql> select min(sal) from emp;
+----------+
| min(sal) |
+----------+
|   800.00 |
+----------+
1 row in set (0.00 sec)

取得最早入职得员工

mysql> select empno, ename, hiredate from emp where hiredate = (select min(hiredate) from emp);
+-------+-------+------------+
| empno | ename | hiredate   |
+-------+-------+------------+
|  7369 | SMITH | 1980-12-17 |
+-------+-------+------------+
1 row in set (0.00 sec)

mysql> select min(str_to_date(hiredate, '%Y-%m-%d')) from emp;
+----------------------------------------+
| min(str_to_date(hiredate, '%Y-%m-%d')) |
+----------------------------------------+
| 1980-12-17                             |
+----------------------------------------+
1 row in set (0.00 sec)

7.6 组合聚合函数

可以将这些聚合函数都放到 select 中一起使用

mysql> select sum(sal),min(sal),max(sal),avg(sal),count(*) from emp;
+----------+----------+----------+-------------+----------+
| sum(sal) | min(sal) | max(sal) | avg(sal)    | count(*) |
+----------+----------+----------+-------------+----------+
| 29025.00 |   800.00 |  5000.00 | 2073.214286 |       14 |
+----------+----------+----------+-------------+----------+
1 row in set (0.00 sec)

\quad

八、分组查询

分组查询主要涉及到两个子句，分别是：group by 和 having。

8.1 group by

先进行分组，然后对每一组的数据进行操作。

select
	...
from 
	...
group by
	...

执行顺序：

select
	...
from 
	...
where
	...
group by
	...
order by
	...

执行顺序不能颠倒！执行顺序不能颠倒！执行顺序不能颠倒！

from
where
group by
select
order by

取得每个工作岗位的工资合计，要求显示岗位名称和工资合计

mysql> select job, sum(sal) from emp group by job;
+-----------+----------+
| job       | sum(sal) |
+-----------+----------+
| CLERK     |  4150.00 |
| SALESMAN  |  5600.00 |
| MANAGER   |  8275.00 |
| ANALYST   |  6000.00 |
| PRESIDENT |  5000.00 |
+-----------+----------+
5 rows in set (0.00 sec)

如果使用了 order by，order by 必须放到 group by 后面

mysql> select job, sum(sal) from emp group by job order by sum(sal);
+-----------+----------+
| job       | sum(sal) |
+-----------+----------+
| CLERK     |  4150.00 |
| PRESIDENT |  5000.00 |
| SALESMAN  |  5600.00 |
| ANALYST   |  6000.00 |
| MANAGER   |  8275.00 |
+-----------+----------+
5 rows in set (0.00 sec)

按照工作岗位和部门编码分组，取得的工资合计

mysql> select job, deptno, sum(sal) from emp group by job, deptno;
+-----------+--------+----------+
| job       | deptno | sum(sal) |
+-----------+--------+----------+
| CLERK     |     20 |  1900.00 |
| SALESMAN  |     30 |  5600.00 |
| MANAGER   |     20 |  2975.00 |
| MANAGER   |     30 |  2850.00 |
| MANAGER   |     10 |  2450.00 |
| ANALYST   |     20 |  6000.00 |
| PRESIDENT |     10 |  5000.00 |
| CLERK     |     30 |   950.00 |
| CLERK     |     10 |  1300.00 |
+-----------+--------+----------+
9 rows in set (0.00 sec)

两个字段联合成一个字段看——两个字段联合分组

注意：在 SQL 语句中若有 group by 语句，那么在 select 语句后面只能跟分组函数+参与分组的字段，其他的一律不能跟。

mysql> select empno, deptno, sum(sal) from emp group by deptno;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'sqltest.emp.EMPNO' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

8.2 having

如果想对分组数据再进行过滤需要使用 having 子句。

找出每个部门最高薪资，要求显示最高薪资大于300的？

mysql> select deptno, max(sal) from emp group by deptno having max(sal) > 3000;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
|     10 |  5000.00 |
+--------+----------+
1 row in set (0.00 sec)

mysql> select deptno, max(sal) from emp where sal > 3000 group by deptno;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
|     10 |  5000.00 |
+--------+----------+
1 row in set (0.00 sec)

上面两句是一样的。

找出每个部门平均薪资，要求显示平均薪资高于2500的

mysql> select deptno, avg(sal) from emp group by deptno having avg(sal) > 2500;
+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     10 | 2916.666667 |
+--------+-------------+
1 row in set (0.00 sec)

使用 having 可以对分完组之后的数据进一步过滤。 having 不能单独使用， having 必须和 group by 联合使用。having 不能代替 where。where 和 having，优先选择 where。

8.3 去重

把查询结果去除重复记录（原表数据不会被修改，只是查询结果去重）

去重需要使用一个关键字：distinct。要注意的是：distinct只能出现在所有字段的前方。

mysql> select distinct job from emp;
+-----------+
| job       |
+-----------+
| CLERK     |
| SALESMAN  |
| MANAGER   |
| ANALYST   |
| PRESIDENT |
+-----------+
5 rows in set (0.00 sec)

distinct出现在多个字段之前，表示多个字段联合起来去重

mysql> select distinct job,ename from emp;
+-----------+--------+
| job       | ename  |
+-----------+--------+
| CLERK     | SMITH  |
| SALESMAN  | ALLEN  |
| SALESMAN  | WARD   |
| MANAGER   | JONES  |
| SALESMAN  | MARTIN |
| MANAGER   | BLAKE  |
| MANAGER   | CLARK  |
| ANALYST   | SCOTT  |
| PRESIDENT | KING   |
| SALESMAN  | TURNER |
| CLERK     | ADAMS  |
| CLERK     | JAMES  |
| ANALYST   | FORD   |
| CLERK     | MILLER |
+-----------+--------+
14 rows in set (0.00 sec)

统计工作岗位的数量：

mysql> select count(distinct job) from emp;
+---------------------+
| count(distinct job) |
+---------------------+
|                   5 |
+---------------------+
1 row in set (0.00 sec)

8.4 select 语句总结

一个完整的 select 语句格式如下：

select
	...
from 
	...
where
	...
group by
	...
having
	...
order by
	...

以上语句的执行顺序

首先执行 where 语句过滤原始数据
执行 group by 进行分组
执行 having 对分组数据进行操作
执行 select 选出数据
执行 order by 排序

需要注意：执行顺序不能颠倒！执行顺序不能颠倒！执行顺序不能颠倒！

原则：能在 where 中过滤的数据，尽量在 where 中过滤，效率较高。having 的过滤是专门对分组之后的数据进行过滤的。

看个综合的例子：找出每个岗位的平均薪资，要求显示平均薪资大于1500的，除manager岗位之外，要求按照平均薪资降序排序

mysql> select job, avg(sal) as avgsal from emp where job != 'manager' group by job
having avgsal > 1500 order by avgsal desc;
+-----------+-------------+
| job       | avgsal      |
+-----------+-------------+
| PRESIDENT | 5000.000000 |
| ANALYST   | 3000.000000 |
+-----------+-------------+
2 rows in set (0.00 sec)

\quad

九、连接查询

连接查询：也可以叫跨表查询，需要关联多个表进行查询。

9.1 连接查询的分类

根据语法的年代分类：(SQL92语法，SQL99语法)

根据表连接的方式分类：

内连接：等值连接、非等值连接、自连接（table1 inner join table2 on condition ）
- 做连接查询的时候一定要写上关联条件
- inner 可以省略
外连接：左外连接，右外连接
- 左外连接：table1 left outer join table2 on condition
  - 做连接查询的时候一定要写上关联条件
  - outer 可以省略
- 右外连接：table1 right outer join table2 on condition
  - 做连接查询的时候一定要写上关联条件
  - outer 可以省略
全连接：笛卡尔积

左外连接（左连接）和右外连接（右连接）的区别：左连接以左面的表为准和右边的表比较，和左表相等的不相等都会显示出来，右表符合条件的显示，不符合条件的不显示。右连接相反。

左连接能完成的功能右连接一定可以完成。

9.2 SQL92 语法

显示每个员工信息，并显示所属的部门名称

mysql> select e.ename, d.dname from emp e, dept d where e.deptno = d.deptno;
+--------+------------+
| 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)

以上查询也称为 “内连接”，只查询相等的数据（连接条件相等的数据）。

取得员工和所属的领导的姓名

mysql> select e.ename, o.ename from emp e, emp o where e.mgr = o.empno;
+--------+-------+
| ename  | ename |
+--------+-------+
| SMITH  | FORD  |
| ALLEN  | BLAKE |
| WARD   | BLAKE |
| JONES  | KING  |
| MARTIN | BLAKE |
| BLAKE  | KING  |
| CLARK  | KING  |
| SCOTT  | JONES |
| TURNER | BLAKE |
| ADAMS  | SCOTT |
| JAMES  | BLAKE |
| FORD   | JONES |
| MILLER | CLARK |
+--------+-------+
13 rows in set (0.00 sec)

以上称为“自连接”，只有一张表连接。具体的查询方法，把一张表看作两张表即可，如以上示例：第一个表 emp e 代表员工表，emp o代表领导表，相当于员工表和部门表一样。

9.3 SQL99 语法

（内连接）显示薪水大于 2000 的员工信息，并显示所属的部门名称

方法一：SQL92语法

mysql> select e.ename, e.sal, d.dname from emp e, dept d where e.deptno = d.deptno and e.sal > 2000;
+-------+---------+------------+
| ename | sal     | dname      |
+-------+---------+------------+
| JONES | 2975.00 | RESEARCH   |
| BLAKE | 2850.00 | SALES      |
| CLARK | 2450.00 | ACCOUNTING |
| SCOTT | 3000.00 | RESEARCH   |
| KING  | 5000.00 | ACCOUNTING |
| FORD  | 3000.00 | RESEARCH   |
+-------+---------+------------+
6 rows in set (0.00 sec)

方法二：SQL99语法

mysql> select e.ename, e.sal, d.dname from emp e join  dept d on e.deptno = d.deptn
o where sal > 2000;
+-------+---------+------------+
| ename | sal     | dname      |
+-------+---------+------------+
| JONES | 2975.00 | RESEARCH   |
| BLAKE | 2850.00 | SALES      |
| CLARK | 2450.00 | ACCOUNTING |
| SCOTT | 3000.00 | RESEARCH   |
| KING  | 5000.00 | ACCOUNTING |
| FORD  | 3000.00 | RESEARCH   |
+-------+---------+------------+
6 rows in set (0.00 sec)

注意：内连接在实际中一般不加inner关键字。

SQL92 语法和 SQL99 语法的区别：SQL99 语法可以做到表的连接和查询条件分离，特别是多个表进行连接的时候，会比 SQL92更清晰。

（外连接）显示员工信息，并显示所属的部门名称，如果某一个部门没有员工，那么该部门也必须显示出来

mysql> select e.ename, e.sal, d.dname from dept d left join emp e on e.deptno = d.deptno;
+--------+---------+------------+
| ename  | sal     | dname      |
+--------+---------+------------+
| MILLER | 1300.00 | ACCOUNTING |
| KING   | 5000.00 | ACCOUNTING |
| CLARK  | 2450.00 | ACCOUNTING |
| FORD   | 3000.00 | RESEARCH   |
| ADAMS  | 1100.00 | RESEARCH   |
| SCOTT  | 3000.00 | RESEARCH   |
| JONES  | 2975.00 | RESEARCH   |
| SMITH  |  800.00 | RESEARCH   |
| JAMES  |  950.00 | SALES      |
| TURNER | 1500.00 | SALES      |
| BLAKE  | 2850.00 | SALES      |
| MARTIN | 1250.00 | SALES      |
| WARD   | 1250.00 | SALES      |
| ALLEN  | 1600.00 | SALES      |
| NULL   |    NULL | OPERATIONS |
+--------+---------+------------+
15 rows in set (0.00 sec)

mysql> select e.ename, e.sal, d.dname from emp e right join  dept d on e.deptno = d.deptno;
+--------+---------+------------+
| ename  | sal     | dname      |
+--------+---------+------------+
| MILLER | 1300.00 | ACCOUNTING |
| KING   | 5000.00 | ACCOUNTING |
| CLARK  | 2450.00 | ACCOUNTING |
| FORD   | 3000.00 | RESEARCH   |
| ADAMS  | 1100.00 | RESEARCH   |
| SCOTT  | 3000.00 | RESEARCH   |
| JONES  | 2975.00 | RESEARCH   |
| SMITH  |  800.00 | RESEARCH   |
| JAMES  |  950.00 | SALES      |
| TURNER | 1500.00 | SALES      |
| BLAKE  | 2850.00 | SALES      |
| MARTIN | 1250.00 | SALES      |
| WARD   | 1250.00 | SALES      |
| ALLEN  | 1600.00 | SALES      |
| NULL   |    NULL | OPERATIONS |
+--------+---------+------------+
15 rows in set (0.00 sec)

以上两个查询效果相同。
\quad

十、子查询

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

在where语句中使用子查询，也就是在where语句中加入select语句。

查询员工信息，查询哪些人是管理者，要求显示出其员工编号和员工姓名。

思路：先取得管理者的编号，再查询员工编号包含管理者编号的

mysql> select empno, ename from emp where empno in (select distinct mgr from emp where mgr is not null);
+-------+-------+
| empno | ename |
+-------+-------+
|  7902 | FORD  |
|  7698 | BLAKE |
|  7839 | KING  |
|  7566 | JONES |
|  7788 | SCOTT |
|  7782 | CLARK |
+-------+-------+
6 rows in set (0.00 sec)

查询哪些人的薪水高于员工的平均薪水，需要显示员工编号，员工姓名，薪水。

mysql> select empno, ename, sal from emp where sal > (select avg(sal) from emp);
+-------+-------+---------+
| empno | ename | sal     |
+-------+-------+---------+
|  7566 | JONES | 2975.00 |
|  7698 | BLAKE | 2850.00 |
|  7782 | CLARK | 2450.00 |
|  7788 | SCOTT | 3000.00 |
|  7839 | KING  | 5000.00 |
|  7902 | FORD  | 3000.00 |
+-------+-------+---------+
6 rows in set (0.00 sec)

在 from 语句中使用子查询，可以将该子查询看做一张表

查询员工信息，查询哪些人是管理者，要求显示出其员工编号和员工姓名

mysql> select e.empno, e.ename from emp e join (select distinct mgr from emp where mgr is not null) m on e.empno = m.mgr;
+-------+-------+
| empno | ename |
+-------+-------+
|  7902 | FORD  |
|  7698 | BLAKE |
|  7839 | KING  |
|  7566 | JONES |
|  7788 | SCOTT |
|  7782 | CLARK |
+-------+-------+
6 rows in set (0.00 sec)

查询各个部门的平均薪水所属等级，需要显示部门编号，平均薪水，等级编号

mysql> select deptno, avg(sal) as avgsal from emp group by deptno;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     20 | 2175.000000 |
|     30 | 1566.666667 |
|     10 | 2916.666667 |
+--------+-------------+
3 rows in set (0.00 sec)

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)

mysql> select e.deptno, e.avgsal, g.grade from (select deptno, avg(sal) as avgsal from emp group by deptno) e join salgrade g on e.avgsal between g.losal and g.hisal;
+--------+-------------+-------+
| deptno | avgsal      | grade |
+--------+-------------+-------+
|     20 | 2175.000000 |     4 |
|     30 | 1566.666667 |     3 |
|     10 | 2916.666667 |     4 |
+--------+-------------+-------+
3 rows in set (0.01 sec)

在 select 语句中使用子查询

查询员工信息，并显示出员工所属的部门名称

mysql> select e.ename, d.dname from emp e join dept d where e.deptno = d.deptno;
+--------+------------+
| 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)

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)

上述两种方法等效。
\quad

十一、union

11.1 union

union 可以合并集合（相加）

union更有效率

查询 job 包含 MANAGER 和包含 SALESMAN 的员工

mysql> select * from emp where job = 'manager' union select * from emp where job= 'salesman';
+-------+--------+----------+------+------------+---------+---------+--------+
| 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 |
|  7499 | ALLEN  | SALESMAN | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
+-------+--------+----------+------+------------+---------+---------+--------+
7 rows in set (0.00 sec)

合并结果集的时候，需要查询字段对应个数相同。

在 Oracle 中更严格，不但要求个数相同，而且还要求类型对应相同。

11.2 union all

结果不去重就用union all，去重就用union。

现在要分别查看工作为manager或者工资大于3000的用户的编号、姓名、职业、领导的数据，请取出相应结果，结果不去重。

mysql> select empno, ename, job, mgr, sal from emp where job = 'manager' union all select empno, ename, job, mgr, sal from emp where sal > 2000;
+-------+-------+-----------+------+---------+
| empno | ename | job       | mgr  | sal     |
+-------+-------+-----------+------+---------+
|  7566 | JONES | MANAGER   | 7839 | 2975.00 |
|  7698 | BLAKE | MANAGER   | 7839 | 2850.00 |
|  7782 | CLARK | MANAGER   | 7839 | 2450.00 |
|  7566 | JONES | MANAGER   | 7839 | 2975.00 |
|  7698 | BLAKE | MANAGER   | 7839 | 2850.00 |
|  7782 | CLARK | MANAGER   | 7839 | 2450.00 |
|  7788 | SCOTT | ANALYST   | 7566 | 3000.00 |
|  7839 | KING  | PRESIDENT | NULL | 5000.00 |
|  7902 | FORD  | ANALYST   | 7566 | 3000.00 |
+-------+-------+-----------+------+---------+
9 rows in set (0.00 sec)

注意：分别查看&结果不去重：所以直接使用两个条件的or是不行的，直接用union也不行，要用union all，分别去查满足条件1的和满足条件2的，然后合在一起不去重。
\quad

十二、limit

MySql提供了limit，主要用于提取前几条或者中间某几行数据。

select * from table limit m, n

其中，m是指记录开始的index，索引从0开始，表示第一条记录，n是指从第m+1条开始，取n条。

select * from tablename limit 2,4

即取出第 3 条至第 6 条，一共4 条记录。

取得前 5 条数据

mysql> select * from emp limit 5;
+-------+--------+----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB      | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK    | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER  | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
+-------+--------+----------+------+------------+---------+---------+--------+
5 rows in set (0.00 sec)

从第二条开始取两条数据

mysql> select * from emp limit 1, 2;
+-------+-------+----------+------+------------+---------+--------+--------+
| EMPNO | ENAME | JOB      | MGR  | HIREDATE   | SAL     | COMM   | DEPTNO |
+-------+-------+----------+------+------------+---------+--------+--------+
|  7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 |     30 |
|  7521 | WARD  | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 |     30 |
+-------+-------+----------+------+------------+---------+--------+--------+
2 rows in set (0.00 sec)

取得薪水最高的前 5 名

mysql> select * from emp order by sal desc limit 5;
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
|  7788 | SCOTT | ANALYST   | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
|  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
|  7566 | JONES | MANAGER   | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |
+-------+-------+-----------+------+------------+---------+------+--------+
5 rows in set (0.00 sec)

\quad

十三、表

13.1 创建表

语法格式：

create table tableName(
 columnName dataType(length),
 .....
 columnName dataType(length)
);
set character_set_results='gbk';
show variables like '%char%';

创建表的时候，表中有字段，每一个字段有：

字段名
字段数据类型
字段长度限制
字段约束

MySql 常用数据类型

类型	描述
Char(长度)	定长字符串，存储空间大小固定，适合作为主键或外键
Varchar(长度)	变长字符串，存储空间等于实际数据空间
Double(有效数字位数，小数位)	数值型
Float(有效数字位数，小数位)	数值型
Int(长度)	整型
Bigint(长度)	长整型
Date	日期型
BLOB	Binary Large OBject（二进制大对象）
CLOB	Character Large OBject（字符大对象）

建立学生信息表，字段包括：学号、姓名、性别、出生日期、email、班级标识

create table t_student(
    id int(10),
    name varchar(20),
    sex char(2),
    birthday date,
    email varchar(30),
    class_id int(3)
);

查看表结构：

mysql> desc t_student;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | YES  |     | NULL    |       |
| name     | varchar(20) | YES  |     | NULL    |       |
| sex      | char(2)     | YES  |     | NULL    |       |
| birthday | date        | YES  |     | NULL    |       |
| email    | varchar(30) | YES  |     | NULL    |       |
| class_id | int         | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

向 t_student 表中加入数据：

mysql> insert into t_student(id, name, sex, birthday, email, class_id) values(1001,'zhangsan', 'm', '1988-01-01', 'qqq@163.com', 10);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_student;
+------+----------+------+------------+-------------+----------+
| id   | name     | sex  | birthday   | email       | class_id |
+------+----------+------+------------+-------------+----------+
| 1001 | zhangsan | m    | 1988-01-01 | qqq@163.com |       10 |
+------+----------+------+------------+-------------+----------+
1 row in set (0.00 sec)

向 t_student 表中加入数据（使用默认值）

drop table if exists t_student; 
create table t_student(
    id int(10),
    name varchar(20),
    sex char(2) default 'm',
    birthday date, 
    email varchar(30),
    classes_id int(3)
);
insert into t_student(student_id, student_name, birthday, email, classes_id) values(1002, 'zhangsan', '1988-01-01', 'qqq@163.com', 10)

13.2 增加/删除/修改表结构

采用 alter table 来增加/删除/修改表结构，不影响表中的数据

添加字段

需求发生改变，需要向 t_student 中加入联系电话字段，字段名称为：contatct_tel类型为varchar(40)。

mysql> alter table t_student add contact_tel varchar(40);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t_student;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| id          | int         | YES  |     | NULL    |       |
| name        | varchar(20) | YES  |     | NULL    |       |
| sex         | char(2)     | YES  |     | NULL    |       |
| birthday    | date        | YES  |     | NULL    |       |
| email       | varchar(30) | YES  |     | NULL    |       |
| class_id    | int         | YES  |     | NULL    |       |
| contact_tel | varchar(40) | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

修改字段

name无法满足需求，长度需要更改为100。

mysql> alter table t_student modify name varchar(40);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t_student;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| id          | int         | YES  |     | NULL    |       |
| name        | varchar(40) | YES  |     | NULL    |       |
| sex         | char(2)     | YES  |     | NULL    |       |
| birthday    | date        | YES  |     | NULL    |       |
| email       | varchar(30) | YES  |     | NULL    |       |
| class_id    | int         | YES  |     | NULL    |       |
| contact_tel | varchar(40) | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

如sex字段名称感觉不好，想用gender那么就需要更改列的名称。

mysql> alter table t_student change sex gender char(2) not null;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t_student;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| id          | int         | YES  |     | NULL    |       |
| name        | varchar(40) | YES  |     | NULL    |       |
| gender      | char(2)     | NO   |     | NULL    |       |
| birthday    | date        | YES  |     | NULL    |       |
| email       | varchar(30) | YES  |     | NULL    |       |
| class_id    | int         | YES  |     | NULL    |       |
| contact_tel | varchar(40) | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

删除字段

删除联系电话字段

mysql> alter table t_student drop contact_tel;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t_student;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | YES  |     | NULL    |       |
| name     | varchar(40) | YES  |     | NULL    |       |
| gender   | char(2)     | NO   |     | NULL    |       |
| birthday | date        | YES  |     | NULL    |       |
| email    | varchar(30) | YES  |     | NULL    |       |
| class_id | int         | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

13.3 添加、修改和删除

添加、修改和删除都属于DML，主要包含的语句：insert、update、delete。

13.3.1 `insert`

insert语法格式

insert into 表名(字段,...) values(值,...)

省略字段的插入

mysql> insert into emp values(9999,'zhangsan','MANAGER', null, null, 3000, 500, 10);
Query OK, 1 row affected (0.00 sec)

指定字段的插入（建议使用此种方式）

mysql> insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values(9999, 'zhangsan', 'MANAGER', null, null, 3000, 500, 10);
ERROR 1062 (23000): Duplicate entry '9999' for key 'emp.PRIMARY'

出现了主键重复的错误，主键表示了记录的唯一性，不能重复。

如何插入日期：

第一种方法，插入的日期格式和显示的日期格式一致。

insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) values(9997, 'zhangsan', 'MANAGER', null, '1981-06-12', 3000, 500, 10);

第二种方法，采用str_to_date。

insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) values(9996, 'zhangsan', 'MANAGER', null, str_to_date('1981-06-12','%Y-%m-%d'), 3000, 500, 10);

第三种方法，添加系统日期（now()）。

insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) values(9995, 'zhangsan', 'MANAGER', null, now(), 3000, 500, 10);

表复制

mysql> create table emp_bark as select * from emp where sal = 3000;
Query OK, 3 rows affected, 2 warnings (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 2

mysql> select * from emp_bark;
+-------+----------+---------+------+------------+---------+--------+--------+
| EMPNO | ENAME    | JOB     | MGR  | HIREDATE   | SAL     | COMM   | DEPTNO |
+-------+----------+---------+------+------------+---------+--------+--------+
|  7788 | SCOTT    | ANALYST | 7566 | 1987-04-19 | 3000.00 |   NULL |     20 |
|  7902 | FORD     | ANALYST | 7566 | 1981-12-03 | 3000.00 |   NULL |     20 |
|  9999 | zhangsan | MANAGER | NULL | NULL       | 3000.00 | 500.00 |     10 |
+-------+----------+---------+------+------------+---------+--------+--------+
3 rows in set (0.00 sec)

以上方式，会自动创建表，将符合查询条件的数据自动复制到创建的表中。

如何将查询的数据直接放到已经存在的表中，可以使用条件

mysql> insert into emp_bark select * from emp where sal = 1250;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from emp_bark;
+-------+----------+----------+------+------------+---------+---------+--------+
| EMPNO | ENAME    | JOB      | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+----------+----------+------+------------+---------+---------+--------+
|  7788 | SCOTT    | ANALYST  | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7902 | FORD     | ANALYST  | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  9999 | zhangsan | MANAGER  | NULL | NULL       | 3000.00 |  500.00 |     10 |
|  7521 | WARD     | SALESMAN | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7654 | MARTIN   | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
+-------+----------+----------+------+------------+---------+---------+--------+
5 rows in set (0.00 sec)

13.3.2 update

update可以根据条件修改数据。

语法格式：

update 表名 set 字段名称 1=需要修改的值 1, 字段名称 2=需要修改的值 2 where ...

将job为manager的员工的工资上涨10%。

mysql> update emp_bark set sal = 1.1 * sal where job = 'manager';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from emp_bark;
+-------+----------+----------+------+------------+---------+---------+--------+
| EMPNO | ENAME    | JOB      | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+----------+----------+------+------------+---------+---------+--------+
|  7788 | SCOTT    | ANALYST  | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7902 | FORD     | ANALYST  | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  9999 | zhangsan | MANAGER  | NULL | NULL       | 3300.00 |  500.00 |     10 |
|  7521 | WARD     | SALESMAN | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7654 | MARTIN   | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
+-------+----------+----------+------+------------+---------+---------+--------+
5 rows in set (0.00 sec)

13.3.3 delete

delete可以根据条件删除数据。

语法格式：

delete from 表名 where ...

删除津贴为 500 的员工

mysql> delete from emp_bark where comm = 500;
Query OK, 2 rows affected (0.00 sec)

mysql> select * from emp_bark;
+-------+--------+----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB      | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+----------+------+------------+---------+---------+--------+
|  7788 | SCOTT  | ANALYST  | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7902 | FORD   | ANALYST  | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
+-------+--------+----------+------+------------+---------+---------+--------+
3 rows in set (0.00 sec)

删除津贴为null的员工

mysql> delete from emp_bark where comm is null;
Query OK, 2 rows affected (0.00 sec)

mysql> select * from emp_bark;
+-------+--------+----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB      | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+----------+------+------------+---------+---------+--------+
|  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
+-------+--------+----------+------+------------+---------+---------+--------+
1 row in set (0.00 sec)

13.4 创建表加入约束

常见的约束

非空约束，not null
唯一约束，unique
主键约束，primary key
外键约束，foreign key
自定义检查约束，check（不建议使用）(在 mysql 中现在还不支持)

13.4.1 非空约束，not null

非空约束，针对某个字段设置其值不为空，如：学生的姓名不能为空。

drop table if exists t_student; 
create table t_student(
    student_id int(10),
    student_name varchar(20) not null,
    sex char(2) default 'm',
    birthday date, 
    email varchar(30),
    classes_id int(3)
)
insert into t_student(student_id, birthday, email, classes_id) values(1002, '1988-01-01', 'qqq@163.com', 10)

13.4.2 唯一约束，unique

唯一性约束，它可以使某个字段的值不能重复，如：email 不能重复：

drop table if exists t_student; 
create table t_student(
    student_id int(10),
    student_name varchar(20) not null,
    sex char(2) default 'm',
    birthday date, 
    email varchar(30) unique,
    classes_id int(3)
);
insert into t_student(student_id, student_name , sex, birthday, email, classes_id) values(1001, 'zhangsan', 'm', '1988-01-01', 'qqq@163.com', 10);

查看约束：

mysql>  use information_schema;
Database changed
mysql> select * from table_constraints where table_name = 't_student';
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE | ENFORCED |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| def                | test              | email           | test         | t_student  | UNIQUE          | YES      |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
1 row in set (0.00 sec)

关于约束名称可以到table_constraints中查询

drop table if exists t_student; 
create table t_student(
    student_id int(10),
    student_name varchar(20) not null,
    sex char(2) default 'm',
    birthday date, 
    email varchar(30) ,
    classes_id int(3) ,
    constraint email_unique unique(email)/*表级约束*/
)

复合主键（两个字段联合起来唯一）

drop table if exists t_vip;
create table t_vip(
    id int,
    name varchar(255),
    email varchar(255),
    unique(name,email)  # 约束没有添加在列的后面，这种约束被称为表级约束。
);
insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@123.com');
insert into t_vip(id,name,email) values(2,'zhangsan','zhangsan@sina.com');

Tips：

约束直接添加到列后面的，叫做列级约束；
约束没有添加在列的后面，这种约束被称为表级约束；
需要给多个字段联合起来添加某一个约束的时候，需要使用表级约束。

在mysql当中，如果一个字段同时被not null和unique约束的话，该字段自动变成主键字段。

13.4.3 主键约束，primary key

每个表应该具有主键，主键可以标识记录的唯一性，主键分为单一主键和复合（联合）主键，单一主键是由一个字段构成的，复合（联合）主键是由多个字段构成的

任何一张表都应该有主键，没有主键，表无效！

主键的特征：not null + unique（主键值不能是NULL，同时也不能重复！）

drop table if exists t_student; 
create table t_student()
    student_id int(10) primary key, /*列级约束*/
    student_name varchar(20) not null,
    sex char(2) default 'm',
    birthday date, 
    email varchar(30) ,
    classes_id int(3)
);
insert into t_student(student_id, student_name , sex, birthday, email, classes_id) values(1001,'zhangsan','m', '1988-01-01', 'qqq@163.com', 10);

我们可以通过表级约束为约束起个名称：

drop table if exists t_student; 
create table t_student(
    student_id int(10),
    student_name varchar(20) not null,
    sex char(2) default 'm',
    birthday date, 
    email varchar(30) ,
    classes_id int(3),
    CONSTRAINT p_id PRIMARY key (student_id)
);
insert into t_student(student_id, student_name , sex, birthday, email, classes_id) values(1001,'zhangsan','m', '1988-01-01', 'qqq@163.com', 10);

注意：一张表，主键约束只能添加一个。

主键除了单一主键和复合主键之外，还可以这样进行分类：

自然主键：主键值是一个自然数，和业务没关系；
业务主键：主键值和业务紧密关联，例如拿银行卡账号做主键值。

在mysql当中，有一种机制，可以帮助我们自动维护一个主键值？

drop table if exists t_vip;
create table t_vip(
    id int primary key auto_increment,  /*auto_increment表示自增，从1开始，以1递增！*/
    name varchar(255)
);
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');

13.4.4 外键约束，foreign key

外键是为了维护表之间的关系，保证参照完整性。如果表中的某个字段为外键字段，那么该字段的值必须来源于参照的表的主键，如：emp中的deptno值必须来源于dept表中的deptno字段值。

例子：建立学生和班级表之间的连接。

首先建立班级表t_classes

drop table if exists t_classes;
create table t_classes(
    classes_id int(3),
    classes_name varchar(40),
    constraint pk_classes_id primary key(classes_id)
);

在 t_student 中加入外键约束

drop table if exists t_student;
create table t_student(
    student_id int(10),
    student_name varchar(20),
    sex char(2),
    birthday date,
    email varchar(30),
    classes_id int(3),
    constraint student_id_pk primary key(student_id),
    constraint fk_classes_id foreign key(classes_id) references t_classes(classes_id) 
);

向 t_student 中加入数据

insert into t_student(student_id, student_name, sex, birthday, email, classes_id) values(1001, 'zhangsan', 'm', '1988-01-01', 'qqq@163.com', 10)

出现错误，因为在班级表中不存在班级编号为 10 班级，外键约束起到了作用。

注意：外键值可以为NULL。

存在外键的表就是子表，参照的表就是父表，所以存在一个父子关系，也就是主从关系，主表就是班级表，从表就是学生表。

\quad

十四、存储引擎

14.1 存储引擎的使用

存储引擎是MySQL中特有的一个术语，其它数据库中没有。（Oracle中有，但是不叫这个名字）

存储引擎这个名字高端大气上档次。实际上存储引擎是一个表存储/组织数据的方式。

不同的存储引擎，表存储数据的方式不同。

数据库中的各表均被（在创建表时）指定的存储引擎来处理。

服务器可用的引擎依赖于以下因素：

MySQL 的版本
服务器在开发时如何被配置
启动选项

为了解当前服务器中有哪些存储引擎可用，可使用语句：

mysql> show engines\G
*************************** 1. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
*************************** 5. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 8. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 9. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
9 rows in set (0.01 sec)

在创建表时，可使用ENGINE选项为CREATE TABLE语句显式指定存储引擎。

create table tablename engine=MYISAM;

如果在创建表时没有显式指定存储引擎，则该表使用当前默认的存储引擎。默认的存储引擎可在my.ini配置文件中使用default-storage-engine选项指定。

现有表的存储引擎可使用alter table语句来改变：

alter table tablename engine = INNODB;

14.2 常用的存储引擎

14.2.1 MyISAM 存储引擎

MyISAM 存储引擎是 MySQL 最常用的引擎。它管理的表具有以下特征：

使用三个文件表示每个表：
- 格式文件：存储表结构的定义（mytable.frm）
- 数据文件：存储表行的内容（mytable.MYD）
- 索引文件：存储表上索引（mytable.MYI）
灵活的AUTO_INCREMENT字段处理
可被转换为压缩、只读表来节省空间

14.2.2 InnoDB 存储引擎

InnoDB 存储引擎是 MySQL 的缺省引擎。它管理的表具有下列主要特征：

每个 InnoDB 表在数据库目录中以.frm格式文件表示
InnoDB 表空间 tablespace被用于存储表的内容
提供一组用来记录事务性活动的日志文件
用 COMMIT(提交)、SAVEPOINT 及 ROLLBACK(回滚)支持事务处理
提供全 ACID 兼容
在 MySQL 服务器崩溃后提供自动恢复
多版本（MVCC）和行级锁定
支持外键及引用的完整性，包括级联删除和更新

14.2.3 MEMORY 存储引擎

使用 MEMORY 存储引擎的表，其数据存储在内存中，且行的长度固定，这两个特点使得 MEMORY 存储引擎非常快。 MEMORY 存储引擎管理的表具有下列特征：

在数据库目录内，每个表均以.frm格式的文件表示。
表数据及索引被存储在内存中。
表级锁机制。
不能包含TEXT或BLOB字段。

MEMORY 存储引擎以前被称为 HEAP 引擎。

14.3 选择合适的存储引擎

MyISAM表最适合于大量的数据读而少量数据更新的混合操作。MyISAM 表的另一种适用情形是使用压缩的只读表。
如果查询中包含较多的数据更新操作，应使用InnoDB。其行级锁机制和多版本的支持为数据读取和更新的混合操作提供了良好的并发机制。
可使用MEMORY存储引擎来存储非永久需要的数据，或者是能够从基于磁盘的表中重新生成的数据。
\quad

十五、事务

15.1 概述

事务可以保证多个操作原子性，要么全成功，要么全失败。对于数据库来说事务保证批量的 DML 要么全成功，要么全失败。

事务具有四个特征（ACID）：

原子性（Atomicity）整个事务中的所有操作，必须作为一个单元全部完成（或全部取消）。
一致性（Consistency）在事务开始之前与结束之后，数据库都保持一致状态。
隔离性（Isolation）一个事务不会影响其他事务的运行。
持久性（Durability）在事务完成以后，该事务对数据库所作的更改将持久地保存在数据库之中，并不会被回滚。

事务中存在一些概念：

事务（Transaction）：一批操作（一组 DML）
开启事务（Start Transaction）
回滚事务（rollback）
提交事务（commit）
SET AUTOCOMMIT：禁用或启用事务的自动提交模式

当执行 DML 语句是其实就是开启一个事务。

注意：只能回滚insert、delete和 update语句，不能回滚select（回滚 select 没有任何意义），对于create、drop、alter这些无法回滚。

事务只对 DML 有效果。

15.2 事务的隔离级别

事务的隔离级别决定了事务之间可见的级别。当多个客户端并发地访问同一个表时，可能出现下面的一致性问题：

脏读取（Dirty Read）一个事务开始读取了某行数据，但是另外一个事务已经更新了此数据但没有能够及时提交，这就出现了脏读取。
不可重复读（Non-repeatable Read）在同一个事务中，同一个读操作对同一个数据的前后两次读取产生了不同的结果，这就是不可重复读。
幻像读（Phantom Read）幻像读是指在同一个事务中以前没有的行，由于其他事务的提交而出现的新行。

InnoDB 实现了四个隔离级别，用以控制事务所做的修改，并将修改通告至其它并发的事务：

读未提交（READ UMCOMMITTED）允许一个事务可以看到其他事务未提交的修改。
读已提交（READ COMMITTED）允许一个事务只能看到其他事务已经提交的修改，未提交的修改是不可见的。
可重复读（REPEATABLE READ）确保如果在一个事务中执行两次相同的 SELECT 语句，都能得到相同的结果，不管其他事务是否提交这些修改。（银行总账）该隔离级别为 InnoDB 的缺省设置。
串行化（SERIALIZABLE）【序列化】将一个事务与其他事务完全地隔离。

\quad

十六、索引

索引被用来快速找出在一个列上用一特定值的行。没有索引，MySQL就不得不首先以第一条记录开始，然后读完整个表直到它找出相关的行。表越大，花费时间越多。对于一个有序字段，可以运用二分查找，这就是为什么性能能得到本质上的提高。MYISAM 和 INNODB 都是用 B+Tree 作为索引结构。

如果未使用索引，查询就会执行全表扫描。

什么时候需要给字段添加索引：

表中该字段中的数据量庞大
经常被检索，经常出现在where子句中的字段
经常被 DML 操作的字段不建议添加索引

索引等同于一本书的目录。主键会自动添加索引，所以尽量根据主键查询效率较高。

建立索引的语法：

create unique index 索引名 on 表名(列名);

查看索引：

show index from 表名

删除索引：

drop index index_name on talbe_name

\quad

十七、视图

视图是一种根据查询（也就是 select 表达式）定义的数据库对象，用于获取想要看到和使用的局部数据。视图有时也被成为“虚拟表”，其可以被用来从常规表（称为“基表”）或其他视图中查询数据。

相对于从基表中直接获取数据，视图有以下好处：

访问数据变得简单
可被用来对不同用户显示不同的表的内容
可用来协助适配表的结构以适应前端现有的应用程序

视图作用：

提高检索效率
隐藏表的实现细节【面向视图检索】

为什么使用视图？因为需求决定以上语句需要在多个地方使用，如果频繁的拷贝以上代码，会给维护带来成本，视图可以解决这个问题。

创建视图：

mysql> create view v_emp as select ename,dname,sal,hiredate,e.deptno from emp e,dept d where e.deptno = e.deptno and e.deptno = 10;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from v_emp;
+--------+------------+---------+------------+--------+
| ename  | dname      | sal     | hiredate   | deptno |
+--------+------------+---------+------------+--------+
| MILLER | ACCOUNTING | 1300.00 | 1982-01-23 |     10 |
| KING   | ACCOUNTING | 5000.00 | 1981-11-17 |     10 |
| CLARK  | ACCOUNTING | 2450.00 | 1981-06-09 |     10 |
| MILLER | RESEARCH   | 1300.00 | 1982-01-23 |     10 |
| KING   | RESEARCH   | 5000.00 | 1981-11-17 |     10 |
| CLARK  | RESEARCH   | 2450.00 | 1981-06-09 |     10 |
| MILLER | SALES      | 1300.00 | 1982-01-23 |     10 |
| KING   | SALES      | 5000.00 | 1981-11-17 |     10 |
| CLARK  | SALES      | 2450.00 | 1981-06-09 |     10 |
| MILLER | OPERATIONS | 1300.00 | 1982-01-23 |     10 |
| KING   | OPERATIONS | 5000.00 | 1981-11-17 |     10 |
| CLARK  | OPERATIONS | 2450.00 | 1981-06-09 |     10 |
+--------+------------+---------+------------+--------+
12 rows in set (0.00 sec)

mysql> create view v_dept_avg_sal_grade as select a.deptno, a.avg_sal, b.grade from (select deptno, avg(sal) avg_sal from emp group by deptno) a, salgrade b where a.avg_sal between b.losal and b.hisal;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from v_dept_avg_sal_grade;
+--------+-------------+-------+
| deptno | avg_sal     | grade |
+--------+-------------+-------+
|     20 | 2175.000000 |     4 |
|     30 | 1566.666667 |     3 |
|     10 | 2916.666667 |     4 |
+--------+-------------+-------+
3 rows in set (0.00 sec)

注意：mysql 不支持子查询创建视图。

修改视图：

mysql> alter view v_emp as select ename,dname,sal,hiredate,e.deptno from emp e, dept d where e.deptno = 20;
Query OK, 0 rows affected (0.01 sec)

删除视图：

mysql> drop view if exists v_emp;
Query OK, 0 rows affected (0.00 sec)

\quad

十八、数据库设计的三范式

18.1 第一范式

数据库表中不能出现重复记录，每个字段是原子性的不能再分。

关于第一范式，每一行必须唯一，也就是每个表必须有主键，这是我们数据库设计的最基本要求，主要通常采用数值型或定长字符串表示，关于列不可再分，应该根据具体的情况来决定。如联系方式，为了开发上的便利行可能就采用一个字段了。

18.2 第二范式

第二范式是建立在第一范式基础上的，另外要求所有非主键字段完全依赖主键，不能产生部分依赖。

18.3 第三范式

建立在第二范式基础上的，非主键字段不能传递依赖于主键字段。（不要产生传递依赖）

如果一个表是单一主键，那么它就符合第二范式，部分依赖和主键有关系。

18.4 总结

第一范式：有主键，具有原子性，字段不可分割；
第二范式：完全依赖，没有部分依赖；
第三范式：没有传递依赖。

数据库设计尽量遵循三范式，但是还是根据实际情况进行取舍，有时可能会拿冗余换速度，最终用目的要满足客户需求。

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

MySql学习笔记：一文上手MySql

MySql学习笔记

一、MySql安装及概述

1.1 MySQL安装

1.2 SQL的分类

1.3 导入数据

二、常用命令

2.1 退出 mysql

2.2 查看mysql中有哪些数据库

2.3 查看表

2.4 选择使用某个数据库

2.5 创建数据库

2.6 查看数据库的版本号

2.7 查询当前使用的数据库

2.8 终止一条语句

2.9 导入数据库

2.10 查看其他库中的表

三、一般查询

3.1 查询表结构

3.2 查询一个字段

3.3 查询多个字段

3.4 查询全部字段

3.5 给查询的列起别名

3.6 字段可以使用数学表达式

3.7 查看表的创建语句

四、条件查询

4.1 =

4.2 !=

4.3 < > >= <=

4.4 between … and …

4.5 is null or is not null

4.6 and or

4.7 in

4.8 模糊查询

五、排序

5.1 单一字段排序

5.2 多个字段排序

5.3 手动指定排序顺序

六、数据处理函数

6.1 lower upper

6.2 substr

6.3 concat

6.4 length

6.5 trim

6.6 round

6.7 rand

6.8 ifnull

6.9 case…when…then…when…then…else…end

6.10 str_to_date

6.11 date_format

七、分组函数/聚合函数/多行处理函数

7.1 count

7.2 sum

7.3 avg

7.4 max

7.5 min

7.6 组合聚合函数

八、分组查询

8.1 group by

8.2 having

8.3 去重

8.4 select 语句总结

九、连接查询

9.1 连接查询的分类

9.2 SQL92 语法

9.3 SQL99 语法

十、子查询

十一、union

11.1 union

11.2 union all

十二、limit

十三、表

13.1 创建表

13.2 增加/删除/修改表结构

13.3 添加、修改和删除

13.3.1 insert

13.3.2 update

13.3.3 delete

13.4 创建表加入约束

13.4.1 非空约束，not null

13.3.1 `insert`