00-MySQL数据库的使用-上

一 数据库基础知识

先谈发音

MySQL如何发音？在国内MySQL发音有很多种，Oracle官方文档说 他们念作 My sequal['si:kwəl]。

数据库基本概念

• 数据

  数据（Data）是指对客观事物进行描述并可以鉴别的符号，这 些符号是可识别的、抽象的。它不仅指狭义上的数字，而是有多 种表现形式：字母、文字、文本、图形、音频、视频等。

• 数据库

  数据库是数据管理的有效技术，是由一批数据构成的有序集合， 这些数据被存放在结构化的数据表里。数据表之间相互关联，反 映客观事物间的本质联系。

• 数据库管理系统

  数据库应用程序（Database Application System，DBAS）是在 数据库管理系统基础上，使用数据库管理系统的语法，开发的直 接面对最终用户的应用程序。

• 数据库管理员

  数据库管理员（Database Administrator，DBA）是指对数据库 管理系统进行操作的人员，其主要负责数据库的运营和维护。

数据库分类

关系型数据库

关系型数据库最典型的数据结构是表，由二维表及其之间的联系所组成的一个数据组织。可以采用结构化查询语言（SQL）对数据库进行操作。

优点：

1. 易于维护：都是使用表结构，格式一致；
2. 使用方便：SQL语言通用，可用于复杂查询；
3. 复杂操作：支持SQL，可用于一个表以及多个表之间非常复杂的查询。

缺点：

1. 读写性能比较差，尤其是海量数据的高效率读写；
2. 固定的表结构，灵活度稍欠；
3. 高并发读写需求，传统关系型数据库来说，硬盘I/O是一个很大的瓶颈。

非关系型数据库

非关系型数据库也称之为NoSQL数据库，是一种数据结构化存储方法的集合，可以是文档或者键值对等。

优点：

1. 格式灵活：存储数据的格式可以是key,value形式、文档形式、图片形式等等，文档形式、图片形 式等等，使用灵活，应用场景广泛，而关系型数据库则只支持基础类型。
2. 速度快：nosql可以使用硬盘或者随机存储器作为载体，而关系型数据库只能使用硬盘；
3. 高扩展性；
4. 成本低：nosql数据库部署简单，基本都是开源软件。

缺点：

1. 不提供sql支持，学习和使用成本较高；
2. 无事务处理；
3. 数据结构相对复杂，复杂查询方面稍欠。

二 MySQL基础知识

MySQL简介

MySQL 是一个关系型数据库管理系统， 由瑞典 MySQL AB 公司开 发， 目前属于 Oracle 公司。MySQL 是一种关系型数据库管理系统，关系型数据库将数据保存在不同的表中，而不是将所有数据放 在一个大仓库内，这样就增加了速度并提高了灵活性。

MySQL特点

MySQL 是开源的。

MySQL 支持大型系统的数据库。可以处理拥有上千万条记录的大型 数据库。 MySQL 使用标准的 SQL 数据语言形式。

MySQL 可以运行于多个系统上，并且支持多种语言。这些编程语言 包括 C 、C++、 Python 、Java 、Perl 、PHP 等。

MySQL 存储数据量较大，32 位系统表文件最大可支持 4GB ，64 位系统支持最大的 表文件为 8TB。

MySQL 是可以定制的，采用了 GPL 协议，你可以修改源码来开发 自己的 MySQL 系 统。

MySQL分类

1. MySQL分为社区版

   社区版是完全开源免费的，社区版也支持多种数据类型和标准的 SQL查询语言，能够对数据进行各种查询、增加、删除、修改等 操作，所以一般情况下社区版就可以满足开发需求了。

2. 企业版 企业版是收费的。

即使在开发中需要用到一些付费的附加功能， 价格相对于昂贵的 Oracle、DB2等也是有很大优势的。对数据库可靠性要求比较高的企业可以选择企业版。

三 下载MySQL

MySQL官网地址：mysql.com

四 MySQL的安装与卸载

MySQL安装

MSQL卸载

五 连接MySQL

通过MySQL自带客户端工具

可通过MySQL自带的客户端工具链接MySQL。

通过客户端工具可对MySQL进行操作。

通过MySQL客户端工具的快捷方式连接MySQL

Navicat工具的使用

六 SQL语言

SQL语言简介

结构化查询语言(Structured Query Language)简称 SQL(发音： sequal['si:kwəl])，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。

SQL 能做什么？

• SQL 面向数据库执行查询
• SQL 可在数据库中插入新的记录
• SQL 可更新数据库中的数据
• SQL 可从数据库删除记录
• SQL 可创建新数据库
• SQL 可在数据库中创建新表
• SQL 可在数据库中创建存储过程
• SQL 可在数据库中创建视图
• SQL 可以设置表、存储过程和视图的权限

SQL 标准

SQL 是 1986 年 10 月由美国国家标准局（ANSI）通过的数据库语 言美国标准，接着，国际标准化组织（ISO）颁布了 SQL 正式国际 标准。1989 年 4 月，ISO 提出了具有完整性特征的 SQL89 标准， 1992 年 11 月又公布了 SQL92 标准，在此标准中，把数据库分为 21 三个级别：基本集、标准集和完全集。在 1999 年推出 99 版标准。 最新版本为 SQL2016 版。比较有代表性的几个版本：SQL86、 SQL92、SQL99。

SQL语言分类

• 数据查询语言（DQL：Data Query Language）其语句，也称为 “数据检索语句”，用以从表中获得数据，确定数据怎样在应用程序给出。关键字 SELECT 是 DQL（也是所有 SQL）用得最多的动词。

  SELECT FROM WHERE ORDER BY HAVING

• 数据操作语言（DML：Data Manipulation Language）其语句包括动词 INSERT，UPDATE 和 DELETE。它们分别用于添加， 修改和删除表中的行。

  INSERT：添加数据 UPDATE：更新数据 DELETE：删除数据

• 数据定义语言（DDL：Data Definition Language）定义数据库对象语言，其语句包括动词 CREATE 和 DROP 等。

  CREATE：创建数据库对象 ALTER：修改数据库对象 DROP：删除数据库对象

• 数据控制语言（DCL：Data Control Language）它的语句通过 GRANT 或 REVOKE 获得许可，确定用户对数据库对象的访问。

  GRANT：授予用户某种权限 REVOKE：回收授予的某种权限

• 事务控制语言（TCL ：Transaction Control Language）它的语句能确保被 DML 语句影响的表的所有行及时得以更新。

  COMMIT：提交事务 ROLLBACK：回滚事务 SAVEPOINT：设置回滚点

注意：

数据操纵语言DML（insert、update、delete）针对表中的数据 ；

而数据定义语言DDL（create、alter、drop）针对数据库对象，比如数据库database、表table、索引index、视图view、 存储过程procedure、触发器trigger；

SQL语言语法

1. SQL语句不区分大小写，关键字建议大写。
2. SQL语句可以单行或多行书写，以分号结尾。

七 创建与删除数据库

创建数据库

使用DDL语句创建数据库

CREATE DATABASE 数据库名 DEFAULT CHARACTER SET 字符编码;

示例： 创建一个test 的数据库，并查看该数据库，以及该数据库的编 码。

创建数据库：

create database test default character set utf8;

查看数据库：

show databases;

查看数据库编码：

select
schema_name,default_character_set_name
from information_schema.schemata
where schema_name = 'test';

使用Navicat创建数据库

示例： 创建一个test2 的数据库。

删除数据库

使用DDL语言删除数据库

DROP DATABASE 数据库名称;

示例： 删除 test 数据库

drop database test;

使用Navicat删除数据库

示例： 删除test2数据库

选择数据库

在创建表时，需要先选择数据库。

USE 数据库名;

示例： 创建一个名称为 bjsxt 的数据库，编码为 utf8。

create database bjsxt default character set utf8;

选择该数据库。

USE bjsxt;

八 MySQL中的数据类型

整数类型

MySQL数据类型	含义（有符号）
tinyint(m)	1个字节 范围(-128~127)
smallint(m)	2个字节 范围(-32768~32767)
mediumint(m)	3个字节 范围(-8388608~8388607)
int(m)	4个字节 范围(-2147483648~2147483647)
bigint(m)	8个字节 范围(±9.22*10的18次方)

数值类型中的长度 m 是指显示长度，并不表示存储长度，只有字段指定 zerofill 时有用

例如： int(3) ，如果实际值是 2 ，如果列指定了 zerofill ，查询结果就是 002 ，左边用 0 来 填充

浮点类型

MySQL数据类型	含义
float(m,d)	单精度浮点型 8位精度(4字节) m总个数，d小数位
double(m,d)	双精度浮点型 16位精度(8字节) m总个数，d小数位

字符类型

MySQL数据类型	含义
char(n)	固定长度，最多255个字符
tinytext	可变长度，最多255个字符
varchar(n)	可变长度，最多65535个字符
text	可变长度，最多65535个字符
mediumtext	可变长度，最多2的24次方-1个字符
longtext	可变长度，最多2的32次方-1个字符

char和varchar：

• char长度固定， 即每条数据占用等长字节空间；适合用在身份证号码、手机号码等定长。
• varchar可变长度，可以设置最大长度；适合用在长度可变的属性。
• text不设置长度， 当不知道属性的最大长度时，适合用text。

按照查询速度： char最快， varchar次之，text最慢。

字符串型使用建议：

• 经常变化的字段用varchar
• 知道固定长度的用char
• 尽量用varchar
• 超过255字符的只能用varchar或者text
• 能用varchar的地方不用text

日期类型

MySQL数据类型	含义
date	日期 YYYY-MM-DD
time	时间 HH:MM:SS
datetime	日期时间 YYYY-MM-DD HH:MM:SS
timestamp	时间戳YYYYMMDD HHMMSS

二进制数据(BLOB)

• BLOB和TEXT存储方式不同，TEXT以文本方式存储，英文存储区分大小写，而Blob是以二进制方式 存储，不分大小写。
• BLOB存储的数据只能整体读出。
• TEXT可以指定字符集，BLOB不用指定字符集。

九 创建表与删除表

创建表

使用DDL语句创建表

CREATE TABLE 表名(列名 类型,列名 类型......);

示例： 创建一个 employees 表包含雇员 ID ，雇员名字，雇员薪水。

create table employees
(employee_id int,
 employee_name varchar(10),
 salary float(8,2));

查看已创建的表。

show tables;

使用Navicat创建表

示例： 创建employees2表。

删除表

使用DDL语句删除表

DROP TABLE 表名;

示例： 删除 employees 表。

drop table employees;

使用Navicat删除表

示例： 删除employees2表

十 修改表

修改表名

使用DDL语句修改表

ALTER TABLE 旧表名 RENAME 新表名;

示例一： 创建一个 employees 表包含雇员 ID ，雇员名字，雇员薪水。

create table employees(
    employee_id int,
    employee_name varchar(10),
    salaryfloat(8,2)
);

示例二： 将 employees 表名修改为 emp。

alter table employees rename emp;

使用Navicat修改表名

选择表按F2。

修改列名

使用DDL语句修改列名

ALTER TABLE 表名 CHANGE COLUMN 旧列名 新列名 类型;

示例： 将 emp 表中的 employee_name 修改为 name。

alter table emp change column employee_name name varchar(20);

使用Navicat修改列名

修改列类型

使用DDL语句修改列类型

ALTER TABLE 表名 MODIFY 列名 新类型;

示例： 将 emp 表中的 name 的长度指定为 40。

alter table emp modify name varchar(40);

使用Navicat修改列类型

添加新列

使用DDL语句添加新列

ALTER TABLE 表名 ADD COLUMN 新列名 类型;

示例： 在 emp 表中添加佣金列，列名为 commission_pct。

alter table emp add column commission_pct float(4,2);

使用Navicat添加新列

删除指定列

使用DDL语句删除指定的列

ALTER TABLE 表名 DROP COLUMN 列名;

示例： 删除 emp 表中的 commission_pct。

alter table emp drop column commission_pct;

使用Navicat删除指定的列

十一 MySQL中的约束

约束概述

数据库约束是对表中的数据进行进一步的限制，保证数据的正确性、有效性和完整性。

1. 主键约束(Primary Key) PK

   主键约束是使用最频繁的约束。在设计数据表时，一般情况下，都会要求表中设置一个主键。 主键是表的一个特殊字段，该字段能唯一标识该表中的每条信息。例如，学生信息表中的学号是唯一的。

2. 外键约束(Foreign Key) FK

   外键约束经常和主键约束一起使用，用来确保数据的一致性。

3. 唯一性约束(Unique)

   唯一约束与主键约束有一个相似的地方，就是它们都能够确保列的唯一性。与主键约束不同的是，唯一约束在一个表中可以有多个，并且设置唯一约束的列是允许有空值的。

4. 非空约束(Not Null)

   非空约束用来约束表中的字段不能为空。

5. 检查约束(Check)

   检查约束也叫用户自定义约束，是用来检查数据表中，字段值是否有效的一个手段，但目前 MySQL 数据库不支持检查约束。

添加主键约束(Primary Key)

1. 单一主键 使用一个列作为主键列，当该列的值有重复时，则违反唯一约束。
2. 联合主键 使用多个列作为主键列，当多个列的值都相同时，则违反唯一约束。

修改表添加主键约束

使用DDL语句添加主键约束

ALTER TABLE 表名 ADD PRIMARY KEY(列名)

示例： 将 emp 表中的 employee_id 修改为主键。

alter table emp add primary key(employee_id);

主键自增长

MySQL 中的自动增长类型要求：

• 一个表中只能有一个列为自动增长。
• 自动增长的列的类型必须是整数类型。
• 自动增长只能添加到具备主键约束与唯一性约束的列上。
• 删除主键约束或唯一性约束，如果该列拥有自动增长能力，则需要先去掉自动增长然后在删除约束。

alter table 表名 modify 主键 类型 auto_increment;

示例： 将 emp 表中的 employee_id 主键修改为自增。

alter table emp modify employee_id int auto_increment;

使用Navicat添加主键约束

删除主键

使用DDL语句删除主键

ALTER TABLE 表名 DROP PRIMARY KEY;

注意： 删除主键时，如果主键列具备自动增长能力，需要先去掉自动增长，然后在删除主键。

示例： 删除emp表中的 employee_id 主键约束。

去掉自动增长：

alter table emp modify employee_id int;

删除主键：

alter table emp drop primary key;

使用Navicat删除主键

添加外键约束(Foreign Key)

修改表添加外键约束

使用DDL语句添加外键约束

ALTER  TABLE 表名 ADD CONSTRAINT 约束名 FOREIGN  KEY( 列 名 ) REFERENCES 参照的表名 (参照的列名);

示例一： 创建 departments 表包含 department_id 、 department_name ，location_id。

create table departments(
    department_id int,
    department_name varchar(30),
    location_id int
);

示例二： 修改departments表，向department_id列添加主键约束与自动递增。

alter table departments add primary key(department_id);
alter table departments modify department_id int auto_increment;

示例三： 修改 emp 表，添加 dept_id 列。

 alter table emp add column dept_id int;

示例四： 向 emp 表中的 dept_id 列添加外键约束。

alter table emp add constraint emp_fk foreign key(dept_id) references departments(department_id);

使用Navicat添加外键约束

删除外键约束

使用DDL语句删除外键约束。

ALTER TABLE 表名 DROP FOREIGN KEY 约束名;

示例： 删除 dept_id 的外键约束。

alter table emp drop foreign key emp_fk;

使用Navicat删除外键约束

添加唯一性约束(Unique)

修改表添加唯一性约束

使用DDL语句添加唯一性约束

ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE(列名);

示例： 向 emp 表中的 name 添加唯一约束。

alter table emp add constraint emp_uk unique(name);

使用Navicat添加唯一性约束

删除唯一性约束

使用DDL语句删除唯一性约束。

ALTER TABLE 表名 DROP KEY 约束名;

示例： 删除 name 的唯一约束。

alter table emp drop key emp_uk;

使用Navicat删除唯一性约束。

添加非空约束(Not Null)

修改表添加非空约束

使用DDL语句添加非空约束。

ALTER TABLE 表名 MODIFY 列名 类型 NOT NULL;

示例： 向 emp 表中的 salary 添加非空约束。

alter table emp modify salary float(8,2) not NULL;

使用Navicat添加非空约束。

删除非空约束

使用DDL语句删除非空约束。

ALTER TABLE 表名 MODIFY 列名 类型 NULL;

示例： 删除emp表中salary 的非空约束。

alter table emp modify salary float(8,2) NULL;

使用Navicat删除非空约束。

创建表时添加约束

查询表中的约束信息：

SHOW KEYS FROM 表名;

示例： 创建 depts 表包含 department_id 该列为主键且自动增长， department_name 列不允许重复，location_id 列不允含有空值。

create table depts(
    department_id int primary key auto_increment,
    department_name varchar(30) unique,
    location_id int not null
);

十二 MySQL中DML操作

添加数据(INSERT)

1. 选择插入

 INSERT INTO 表名(列名 1 ，列名 2 ，列名3.....) VALUES(值 1 ，值 2 ，值 3......);
 

示例： 向 departments 表中添加一条数据，部门名称为 market ，工 作地点 ID 为 1。

insert into departments(department_name,location_id) values("market", 1);

2. 完全插入

INSERT INTO 表名 VALUES(值 1 ，值 2 ，值3......);

注意： 如果主键是自动增长，需要使用 default 或者 null 或者 0 占位。

示例一： 向 departments 表中添加一条数据，部门名称为 development ， 工作地点 ID 为 2 。使用 default 占位。

insert into departments values(default,"development",2);

示例二： 向 departments 表中添加一条数据，部门名称为human ，工作地 点 ID 为 3 。使用 null 占 位。

insert into departments values(null,"human",3);

示例三： 向 departments 表中添加一条数据，部门名称为 teaching ，工作 地点 ID 为 4 。使用 0 占 位。

insert into departments values(0,"teaching",4);

默认值处理(DEFAULT)

在 MySQL 中可以使用 DEFAULT 为列设定一个默认值。如果在插 数据时并未指定该列的值，那么 MySQL 会将默认值添加到该列 中。

创建表时指定列的默认值

CREATE TABLE 表名(
    列名 类型 default 默认值,
    ......
);

示例： 创建 emp3 表，该表包含 emp_id 主键且自动增长，包含 name ， 包含 address 该列默认值为”未知”。

create table emp3(
    emp_id int primary key auto_increment,
    name varchar(10),
    address varchar(50) default 'Unknown'
);

修改表添加新列并指定默认值

ALTER TABLE 表名 ADD COLUMN 列名 类型 DEFAULT 默认值;

示例： 修改 emp3 表，添加job_id 该列默认值为 0。

alter table emp3 add column job_id int default 0;

插入数据时的默认值处理

如果在插入数据时并未指定该列的值，那么MySQL 会将默认值添加到该列中。如果是完全项插入需要使用 default 来占位。

示例： 向 emp3 表中添加数据，要求 address 列与job_id 列使用默认值作为该列的值。

insert into emp3(name) values("admin");
insert into emp3 values(default,"oldlu",default,default);

更新数据(UPDATE)

UPDATE 表名 SET 列名=值，列名=值 WHERE 条件;

注意： 更新语句中一定要给定更新条件，否则表中的所有数据都会被更新。

示例： 更新 emp3 表中的 id 为 1 的数据，添加 address 为 BeiJing。

update emp3 set address = "BeiJing" where emp_id = 1;

删除数据(DELETE)

DELETE删除数据

DELETE FROM 表名 WHERE 条件;

注意： 在DELETE语句中，如果没有给定删除条件则会删除表中的所有数据。

示例： 删除 emp3 表中 emp_id 为 1 的雇员信息。

delete from emp3 where emp_id = 1;

TRUNCATE清空表

TRUNCATE TABLE 表名;

示例： 删除 emp3 表中的所有数据。

 truncate table emp3;

清空表时DELETE与 TRUNCATE 区别

• truncate 是整体删除(速度较快)， delete 是逐条删除(速度较慢)；
• truncate 不写服务器 log，delete 写服务器 log，也就是 truncate 效率比 delete 高的原因；
• truncate 是会重置自增值，相当于自增列会被置为初始值，又重新从 1 开始记录，而不是接着原来的值。而 delete 删除以后， 自增值仍然会继续累加。

十三 MySQL查询数据

SELECT基本查询

SELECT语句的功能

SELECT 语句从数据库中返回信息。使用一个 SELECT 语句，可以做下面的事：

• 列选择：能够使用 SELECT 语句的列选择功能选择表中的列，这些列是想要用查询返回的。当查询时，能够返回列中的数据。
• 行选择：能够使用 SELECT 语句的行选择功能选择表中的行，这些行是想要用查询返回的。能够使用不同的标准限制看见的行。
• 连接：能够使用 SELECT 语句的连接功能来集合数据，这些数据被存储在不同的表中，在它们之间可以创建连接，查询出我们所关心的数据。

SELECT基本语法

基本 SELECT 语句

在最简单的形式中，SELECT 语句必须包含下面的内容：

• 一个 SELECT 子句，指定被显示的列
• 一个 FROM 子句，指定表，该表包含 SELECT 子句中的字段列表

在语法中：

语句	含义
SELECT	是一个或多个字段的列表
*	选择所有的列
DISTINCT	禁止重复
column | expression	选择指定的字段或表达式
alias	给所选择的列不同的标题
FROM table	指定包含列的表

添加测试数据

将data.sql文件通过Navicat导入到MySQL中itbz数据库中。该文件包含了该文章中所使用的案例表。

查询中的列选择

选择所有列

用跟在 SELECT 关键字后面的星号 (*)，你能够显示表中数据的所有列。

示例： 查询 departments 表中的所有数据。

select * from departments;

选择指定列

能够用 SELECT 语句来显示表的指定列，指定列名之间用逗号分隔。

示例： 查询 departments 表中所有部门名称。

select department_name from departments;

查询中的算术表达式

需要修改数据显示方式，如执行计算，或者作假定推测，这些都可能用到算 术表达式。一个算术表达式可以包含列名、固定的数字值和算术运算符。

使用算术运算符

示例： 查询雇员的年薪，并显示他们的雇员ID，名字。

select employees_id,last_name, 12*salary from employees;

运算符的优先级

如果算术表达式包含有一个以上的运算，乘法和除法先计算。如果 在一个表达式中的运算符优先级相同，计算从左到右进行。可以用圆括号强制其中的表达式先计算。

示例一： 计算 employees 表中的员工全年薪水加 100 以后的薪水是多少， 并显示他们的员工ID与名字。

select employees_id,last_name, 12*salary+100 from employees;

示例二： 计算 employees 表中的员工薪水加 100 以后的全年薪水是多少， 并显示他们的员工ID与名字。

select employees_id,last_name, 12*(salary+100) from employees;

MySQL中定义空值

如果一行中的某个列缺少数据值，该值被置为 null， 或者说包含一个空。

空是一个难以获得的、未分配的、未知的，或不适用的值。空和 0 或者空格不相同。 0 是一个数字，而空格是一个字符。

算术表达式中的空值

示例： 计算年薪包含佣金。

select 12*salary*commission_pct from employees;

MySQL中的别名

使用列别名

SELECT 列名 AS 列别名 FROM 表名 WHERE 条件;

示例： 查询 employees 表将雇员 last_name 列定义别名为 name。

select last_name as name from employees;
select last_name name from employees;

使用表别名

SELECT 表别名.列名  FROM 表名 as 表别名 WHERE 条件;

示例： 查询 employees 表为表定义别名为emp，将雇员 last_name 列定 义别名为 name。

select emp.last_name name from employees emp;

MySQL中去除重复

除去相同的行

SELECT DISTINCT 列名 FROM 表名;

示例： 查询 employees 表，显示唯一的部门 ID。

select distinct department_id from employees;

查询中的行选择

用 WHERE 子句限制从查询返回的行。一个 WHERE 子句包含一个 必须满足的条件，WHERE 子句紧跟着 FROM 子句。如果条件是 true，返回满足条件的行。

在语法中：

WHERE 限制查询满足条件的行

condition 由列名、表达式、常数和比较操作组成

SELECT * | 投影列 FROM 表名 WHERE 选择条件;

示例： 查询 departments 表中部门 ID 为 90 的部门名称与工作地点 ID。

select department_name,location_id from departments where department_id =4;

MySQL中的比较条件

符号 != 也能够表示不等于条件。

示例一： 查询 employees 表中员工薪水大于等于 3000 的员工的姓名与薪 水。

select last_name,salary from employees where salary >= 3000;

示例二： 查询 employees 表中员工薪水不等于 5000 的员工的姓名与薪水。

select last_name,salary from employees where salary<>5000;

其他比较条件

使用BETWEEN条件

可以用 BETWEEN 范围条件显示基于一个值范围的行。指定的范围包含一个下限和一个上限。

示例： 查询 employees 表，薪水在 3000-8000 之间的雇员ID、名字与薪水。

select employee_id,last_name,salary from employees where salary between 3000 and 8000;

使用IN条件

示例： 查询 employees 表，找出薪水是 5000,6000,8000 的雇员ID、名字 与薪水。

select employee_id,last_name,salary from employees where salary in(5000,6000,8000);

使用LIKE条件

示例： 查询 employees 中雇员名字第二个字母是 e 的雇员名字。

select last_name from employees where last_name like '_e%';

使用NULL条件

NULL 条件，包括 IS NULL 条件和 IS NOT NULL 条件。

IS NULL 条件用于空值测试。空值的意思是难以获得的、未指定的、未知的或者不适用的。因此，你不能用 = ，因为 null 不能等于或不等于任何值

示例一： 找出 emloyees 表中那些没有佣金的雇员雇员ID、名字与佣金。

select employee_id,last_name,commission_pct from employees where commission_pct is null;

示例二： 找出 employees 表中那些有佣金的雇员ID、名字与佣金。

select employee_id,last_name,commission_pct from employees where commission_pct is not
null;

逻辑条件

逻辑条件组合两个比较条件的结果来产生一个基于这些条件的单个的结果，或者逆转一个单个条件的结果。当所有条件的结果为真时，返回行

SQL 的三个逻辑运算符是： AND OR NOT

可以在 WHERE 子句中用 AND 和 OR 运算符使用多个条件。

示例一： 查询 employees 表中雇员薪水是 8000 的并且名字中含有e 的雇员 名字与薪水。

select last_name,salary from employees where
salary = 8000 and last_name like '%e%';

示例二： 查询 employees 表中雇员薪水是 8000 的或者名字中含有e 的雇员 名字与薪水。

select last_name,salary from employees where
salary = 8000 or last_name like '%e%';

示例三： 查询 employees 表中雇员名字中不包含 u 的雇员的名字。

select last_name from employees where
last_name not like '%u%';

优先规则

在图片的例子中，有两个条件：

第一个条件是 job_id 是 AD_PRES 并且薪水高于 15,000。 第二个条件是 job_id 是 SA_REP。

在图片中的例子有两个条件： 第一个条件是 job_id 是 AD_PRES 或者 SA_REP 。 第二个条件是薪水高于$15,000

使用 ORDER BY 排序

在一个不明确的查询结果中排序返回的行。ORDER BY 子句用于排序。如果使用了 ORDER BY 子句，它必须位于 SQL 语句的最后。

SELECT 语句的执行顺序如下：

• FROM 子句
• WHERE 子句
• SELECT 子句
• ORDER BY 子句

示例一： 查询 employees 表中的所有雇员，显示他们的ID、名字与薪水，并按薪水升序排序。

select employee_id,last_name,salary from
employees order by salary;
select employee_id,last_name,salary from
employees order by salary asc;

示例二： 查询 employees 表中的所有雇员，显示他们的ID与名字，并按雇员名字降序排序。

select employee_id,last_name from employees
order by last_name desc;

使用别名排序

示例： 显示雇员ID，名字。计算雇员的年薪，年薪列别名为annsal，并对 该列进行升序排序，

select employee_id,last_name ,12*salary annsal from employees order by annsal;

多列排序

示例： 以升叙排序显示 DEPARTMENT_ID 列，同时以降序排序显示 SALARY 列。

select department_id,salary from employees
order by department_id asc ,salary desc;

十四 SQL函数

函数介绍

函数是 SQL 的一个非常强有力的特性，函数能够用于下面的目的：

• 执行数据计算
• 修改单个数据项
• 操纵输出进行行分组
• 格式化显示的日期和数字
• 转换列数据类型

SQL 函数有输入参数，并且总有一个返回值。

函数分类

单行函数

单行函数仅对单个行进行运算，并且每行返回一个结果。

常见的函数类型：

• 字符
• 数字
• 日期
• 转换

多行函数

多行函数能够操纵成组的行，每个行组给出一个结果，这些函数也被称为组函数。

单行函数

单行函数分类

字符函数

大小写处理函数

函数	描述	实例
LOWER(s)|LCASE(s)	将字符串 s 转换为小写	将字符串 OLDLU转换为小写： SELECT LOWER(“OLDLU”); – oldlu
UPPER(s)|UCASE(s)	将字符串s转换为大写	将字符串 oldlu转换为大写： SELECT UPPER(“oldlu”); – OLDLU

示例： 显示雇员 Davies 的雇员号、姓名和部门号，将姓名转换为大写。

select employee_id,UPPER(last_name),department_id
from employees where last_name = 'davies';

字符处理函数

函数	描述	实例
LENGTH(s)	返回字符串 s 的长度	返回字符串oldlu的字符数 SELECT LENGTH(“oldlu”); --5;
CONCAT(s1,s2…sn)	字符串 s1,s2 等多个字符串合 并为一个字符串	合并多个字符串 SELECT CONCAT("sxt ", "teacher ", “oldlu”); --sxt teacher oldlu;
LPAD(s1,len,s2)	在字符串 s1 的开始处填充字 符串 s2，使字符串长度达到 len	将字符串 x 填充到 oldlu字符串的开始处： SELECT LPAD(‘oldlu’,8,‘x’); – xxxoldlu
LTRIM(s)	去掉字符串 s 开始处的空格	去掉字符串 oldlu开始处的空格： SELECT LTRIM(" oldlu") ;-- oldlu
REPLACE(s,s1,s2)	将字符串 s2 替代字符串 s 中 的字符串 s1	将字符串 oldlu 中的字符 o 替换为字符 O： SELECT REPLACE(‘oldlu’,‘o’,‘O’); --Oldlu
REVERSE(s)	将字符串s的顺序反过来	将字符串 abc 的顺序反过来： SELECT REVERSE(‘abc’); – cba
RPAD(s1,len,s2)	在字符串 s1 的结尾处添加字 符串 s2，使字符串的长度达 到 len	将字符串 xx填充到 oldlu字符串的结尾处： SELECT RPAD(‘oldlu’,8,‘x’); – oldluxxx
RTRIM(s)	去掉字符串 s 结尾处的空格	去掉字符串 oldlu 的末尾空格： SELECT RTRIM("oldlu "); – oldlu
SUBSTR(s, start, length)	从字符串 s 的 start 位置截取 长度为 length 的子字符串	从字符串 OLDLU中的第 2 个位置截取 3个 字符： SELECT SUBSTR(“OLDLU”, 2, 3); – LDL
SUBSTRING(s, start, length)	从字符串 s 的 start 位置截取 长度为 length 的子字符串	从字符串 OLDLU中的第 2 个位置截取 3个 字符： SELECT SUBSTRING(“OLDLU”, 2, 3); – LDL
TRIM(s)	去掉字符串 s 开始和结尾处的 空格	去掉字符串 oldlu 的首尾空格： SELECT TRIM(’ oldlu ');–oldlu

示例： 显示所有工作岗位名称从第 4 个字符位置开始，包含字符串 REP的 雇员的ID信息，将雇员的姓和名连接显示在一起，还显示雇员名的长度，以及名字中字母 a 的位置。

SELECT employee_id, CONCAT(last_name，first_name) NAME,job_id, LENGTH(last_name),INSTR(last_name,'a') "Contains 'a'?" FROM employees WHERE
SUBSTR(job_id, 4) = 'REP';

MySQL中INSTR函数用于在指定的字符串中查找特定的子字符串，并返回其在原字符串中首次出现的位置。其基本语法如下：

INSTR(string, substring)

数字函数

函数名	描述	实例
ABS(x)	返回 x 的绝对值	返回 -1 的绝对值： SELECT ABS(-1) – 返回1
ACOS(x)	求 x 的反余弦值(参数是弧度)	SELECT ACOS(0.25);
ASIN(x)	求反正弦值(参数是弧度)	SELECT ASIN(0.25);
ATAN(x)	求反正切值(参数是弧度)	SELECT ATAN(2.5);
ATAN2(n, m)	求反正切值(参数是弧度)	SELECT ATAN2(-0.8, 2);
AVG(expression)	返回一个表达式的平均值， expression 是一个字段	返回 Products 表中Price 字段的平均值： SELECT AVG(Price) AS AveragePrice FROM Products;
CEIL(x)	返回大于或等于 x 的最小整数	SELECT CEIL(1.5) – 返回2
CEILING(x)	返回大于或等于 x 的最小整数	SELECT CEILING(1.5); – 返回2
COS(x)	求余弦值(参数是弧度)	SELECT COS(2);
COT(x)	求余切值(参数是弧度)	SELECT COT(6);
COUNT(expression)	返回查询的记录总数， expression 参数是一个字段或 者 * 号	返回 Products 表中 products 字段总共有多少条记录： SELECT COUNT(ProductID) AS NumberOfProducts FROM Products;
DEGREES(x)	将弧度转换为角度	SELECT DEGREES(3.1415926535898) – 180
n DIV m	整除，n 为被除数，m 为除数	计算 10 除于 5： SELECT 10 DIV 5; – 2
EXP(x)	返回 e 的 x 次方	计算 e 的三次方： SELECT EXP(3) – 20.085536923188
FLOOR(x)	返回小于或等于 x 的最大整数	小于或等于 1.5 的整数： SELECT FLOOR(1.5) – 返回1
GREATEST(expr1, expr2, expr3, …)	返回列表中的最大值	返回以下数字列表中的最大值： SELECT GREATEST(3, 12, 34, 8, 25); – 34 返回以下字符串列表中的最大值： SELECT GREATEST(“Google”, “Runoob”, “Apple”); – Runoob
LEAST(expr1, expr2, expr3, …)	返回列表中的最小值	返回以下数字列表中的最小值： SELECT LEAST(3, 12, 34, 8, 25); – 3 返回以下字符串列表中的最小值： SELECT LEAST(“Google”, “Runoob”, “Apple”); – Apple
LN	返回数字的自然对数，以 e 为 底。	返回 2 的自然对数： SELECT LN(2); – 0.6931471805599453
LOG(x) 或 LOG(base, x)	返回自然对数(以 e 为底的对 数)，如果带有 base 参数，则 base 为指定带底数。	SELECT LOG(20.085536923188) – 3 SELECT LOG(2, 4); – 2
LOG10(x)	返回以 10 为底的对数	SELECT LOG10(100) – 2
LOG2(x)	返回以 2 为底的对数	返回以 2 为底 6 的对数： SELECT LOG2(6); – 2.584962500721156
MAX(expression)	返回字段 expression 中的最 大值	返回数据表 Products 中字段 Price 的最大值： SELECT MAX(Price) AS LargestPrice FROM Products;
MIN(expression)	返回字段 expression 中的最 小值	返回数据表 Products 中字段 Price 的最小值： SELECT MIN(Price) AS MinPrice FROM Products;
MOD(x,y)	返回 x 除以 y 以后的余数	5 除于 2 的余数： SELECT MOD(5,2) – 1
PI()	返回圆周率(3.141593）	SELECT PI() --3.141593
POW(x,y)	返回 x 的 y 次方	2 的 3 次方： SELECT POW(2,3) – 8
POWER(x,y)	返回 x 的 y 次方	2 的 3 次方： SELECT POWER(2,3) – 8
RADIANS(x)	将角度转换为弧度	180 度转换为弧度： SELECT RADIANS(180) – 3.1415926535898
RAND()	返回 0 到 1 的随机数	SELECT RAND() --0.93099315644334
ROUND(x)	返回离 x 最近的整数	SELECT ROUND(1.23456) --1
SIGN(x)	返回 x 的符号，x 是负数、 0、正数分别返回 -1、0 和 1	SELECT SIGN(-10) – (-1)
SIN(x)	求正弦值(参数是弧度)	SELECT SIN(RADIANS(30)) – 0.5
SQRT(x)	返回x的平方根	25 的平方根： SELECT SQRT(25) – 5
SUM(expression)	返回指定字段的总和	计算 OrderDetails 表中字段 Quantity 的总和： SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails;
TAN(x)	求正切值(参数是弧度)	SELECT TAN(1.75); – -5.52037992250933
TRUNCATE(x,y)	返回数值 x 保留到小数点后 y 位的值（与 ROUND 最大的区 别是不会进行四舍五入）	SELECT TRUNCATE(1.23456,3) – 1.234

ROUND(column|expression, n) 函数

ROUND 函数四舍五入列、表达式或者 n 位小数的值。如果第二个 参数是 0 或者缺少，值被四舍五入为整数。如果第二个参数是 2值 被四舍五入为两位小数。如果第二个参数是–2，值被四舍五入到小数点左边两位。

SELECT ROUND(45.923,2),ROUND(45.923,0),ROUND(45.923,-1);

TRUNCATE(column|expression,n) 函数

TRUNCATE函数的作用类似于 ROUND 函数。如果第二个参数是 0 或者缺少，值被截断为整数。如果第二个参数是 2，值被截断为两 位小数。如果第二个参数是–2，值被截断到小数点左边两位。与 ROUND 最大的区别是不会进行四舍五入。

SELECT TRUNCATE(45.923,2);

使用MOD(m,n) 函数

MOD

函数找出m 除以n的余数。

示例： 所有job_id是SA_REP的雇员的名字，薪水以及薪水被5000除后的余数。

SELECT last_name, salary, MOD(salary, 5000)
FROM employees
WHERE job_id = 'SA_REP';

日期函数

在MySQL中允许直接使用字符串表示日期，但是要求字符串的日期 格式必须为：‘YYYY-MM-DD HH:MI:SS’ 或者‘YYYY/MM/DD HH:MI:SS’;

函数名	描述	实例
CURDATE()	返回当前日期	SELECT CURDATE(); -> 2018-09-19
CURTIME()	返回当前时间	SELECT CURTIME(); -> 19:59:02
CURRENT_DATE()	返回当前日期	SELECT CURRENT_DATE(); -> 2018-09-19
CURRENT_TIME()	返回当前时间	SELECT CURRENT_TIME(); -> 19:59:02
DATE()	从日期或日期时间表达式中提取日期值	SELECT DATE(“2017-06-15”); -> 2017-06-15
DATEDIFF(d1,d2)	计算日期 d1->d2 之间相隔的天数	SELECT DATEDIFF(‘2001-01-01’,‘2001-02-02’) -> -32
DAY(d)	返回日期值 d 的日期部分	SELECT DAY(“2017-06-15”); -> 15
DAYNAME(d)	返回日期 d 是星期几，如 Monday,Tuesday	SELECT DAYNAME(‘2011-11-11 11:11:11’) - >Friday
DAYOFMONTH(d)	计算日期 d 是本月的第几天	SELECT DAYOFMONTH(‘2011-11-11 11:11:11’) - >11
DAYOFWEEK(d)	日期 d 今天是星期几，1 星期日，2 星期 一，以此类推	SELECT DAYOFWEEK(‘2011-11-11 11:11:11’) ->6
DAYOFYEAR(d)	计算日期 d 是本年的第几天	SELECT DAYOFYEAR(‘2011-11-11 11:11:11’) - >315
HOUR(t)	返回 t 中的小时值	SELECT HOUR(‘1:2:3’) -> 1
LAST_DAY(d)	返回给给定日期的那一月份的最后一天	SELECT LAST_DAY(“2017-06-20”); -> 2017-06-30
MONTHNAME(d)	返回日期当中的月份名称，如 November	SELECT MONTHNAME(‘2011-11-11 11:11:11’) - > November
MONTH(d)	返回日期d中的月份值，1 到 12	SELECT MONTH(‘2011-11-11 11:11:11’) ->11
NOW()	返回当前日期和时间	SELECT NOW() -> 2018-09-19 20:57:43
SECOND(t)	返回 t 中的秒钟值	SELECT SECOND(‘1:2:3’) -> 3
SYSDATE()	返回当前日期和时间	SELECT SYSDATE() -> 2018-09-19 20:57:43
TIMEDIFF(time1, time2)	计算时间差值	SELECT TIMEDIFF(“13:10:11”, “13:10:10”); -> 00:00:01
TO_DAYS(d)	计算日期 d 距离 0000 年 1 月 1 日的天数	SELECT TO_DAYS(‘0001-01-01 01:01:01’) -> 366
WEEK(d)	计算日期 d 是本年的第几个星期，范围是 0 到 53	SELECT WEEK(‘2011-11-11 11:11:11’) -> 45
WEEKDAY(d)	日期 d 是星期几，0 表示星期一，1 表示星 期二	SELECT WEEKDAY(“2017-06-15”); -> 3
WEEKOFYEAR(d)	计算日期 d 是本年的第几个星期，范围是 0 到 53	SELECT WEEKOFYEAR(‘2011-11-11 11:11:11’) -> 45
YEAR(d)	返回年份	SELECT YEAR(“2017-06-15”); -> 2017

示例一：

向 employees 表中添加一条数据，雇员ID：300，名字：kevin ， email：kevin@sxt.cn ，入职时间：2049-5-1 8:30:30，工作部 门：‘IT_PROG’。

insert  into
employees(EMPLOYEE_ID,last_name,email,HIRE_DATE,JOB_ID)
values(300,'kevin','kevin@sxt.cn','2049-5-1 8:30:30','IT_PROG');

示例二：

显示所有在部门 90 中的雇员的名字和从业的周数。雇员的总工作 时间以周计算，用当前日期 (SYSDATE) 减去雇员的受顾日期，再除以 7。

SELECT last_name, (SYSDATE()-hire_date)/7 AS WEEKS 
FROM employees WHERE department_id = 90;

转换函数

隐式数据类型转换

隐式数据类型转换是指MySQL服务器能够自动地进行类型转换。 如：可以将标准格式的字串日期自动转换为日期类型。

MySQL字符串日期格式为：‘YYYY-MM-DD HH:MI:SS’ 或 ‘YYYY/MM/DD HH:MI:SS’;

显示数据类型转换

显示数据类型转换是指需要依赖转换函数来完成相关类型的转换。 如：

• DATE_FORMAT(date,format) 将日期转换成字符串;
• STR_TO_DATE(str,format) 将字符串转换成日期;

示例一：

向 employees 表中添加一条数据，雇员ID：400，名字：oldlu ， email：oldlu@sxt.cn ，入职时间：2049 年 5 月 5 日，工作部 门：‘IT_PROG’。

insert  into
employees(EMPLOYEE_ID,last_name,email,HIRE_DATE,JOB_ID)
values(400,'oldlu','oldlu@sxt.cn',
       STR_TO_DATE('2049 年 5 月 5 日','%Y 年%m 月%d日'),
       'IT_PROG');

示例二：

查询 employees 表中雇员名字为 King 的雇员的入职日期，要求显 示格式为 yyyy 年 MM 月 dd 日。

select DATE_FORMAT(hire_date,'%Y 年%m 月%d 日') from employees where last_name = 'King';

通用函数

函数名	描述	实例
IF(expr,v1,v2)	如果表达式 expr 成立，返回结果 v1；否 则，返回结果 v2。	SELECT IF(1 > 0,‘正 确’,‘错误’) ->正确
IFNULL(v1,v2)	如果 v1 的值不为 NULL，则返回 v1，否则返 回 v2。	SELECT IFNULL(null,‘Hello Word’) ->Hello Word
ISNULL(expression)	判断表达式是否为 NULL	SELECT ISNULL(NULL); ->1
NULLIF(expr1, expr2)	比较两个参数是否相同，如果参数 expr1 与 expr2 相等 返回 NULL，否则返回 expr1	SELECT NULLIF(25, 25); ->
COALESCE(expr1, expr2, …, expr_n)	返回参数中的第一个非空表达式（从左向 右）	SELECT COALESCE(NULL, NULL, NULL, ‘bjsxt.com’, NULL, ‘google.com’); -> bjsxt.com
CASE expression WHEN condition1 THEN result1 WHEN condition2 THEN result2 … WHEN conditionN THEN resultN ELSE result END	CASE 表示函数开始，END 表示函数结束。 如果 condition1 成立，则返回 result1, 如果 condition2 成立，则返回 result2，当全部不成立则返回 result，而当有一个成立之后， 后面的就不执行了。	SELECT CASE ‘oldlu’ WHEN ‘oldlu’ THEN ‘OLDLU’ WHEN ‘admin’ THEN ‘ADMIN’ ELSE ‘kevin’ END;

示例一：

查询部门编号是50或者80的员工信息，包含他们的名字、薪水、佣 金。在income列中，如果有佣金则显示‘SAL+COMM’，无佣金则显 示’SAL’。

SELECT last_name, salary, commission_pct,    
    if(ISNULL(commission_pct),'SAL','SAL+COMM') income
FROM employees
WHERE department_id IN (50, 80);

示例二：

计算雇员的年报酬，你需要用 12 乘以月薪，再加上它的佣金 (等于年薪乘以佣金百分比)。

SELECT last_name, salary,
IFNULL(commission_pct, 0), (salary*12) + (salary*12*IFNULL(commission_pct, 0)) AN_SAL
FROM employees;

示例三

查询员工表，显示他们的名字、名字的长度该列名为expr1，姓 氏、姓氏的长度该列名为expr2。在result列中，如果名字与姓氏的 长度相同则显示空，如果不相同则显示名字长度。

SELECT  first_name, LENGTH(first_name) "expr1",
        last_name, LENGTH(last_name) "expr2",
        NULLIF(LENGTH(first_name),LENGTH(last_name)) result
FROM employees;

示例四：

查询员工表，显示他们的名字，如果 COMMISSION_PCT 值是非 空，显示它。如果COMMISSION_PCT 值是空，则显示 SALARY 。 如果 COMMISSION_PCT 和SALARY 值都是空，那么显示 10。在结 果中对佣金列升序排序。

SELECT last_name,
COALESCE(commission_pct, salary, 10) comm
FROM employees
ORDER BY commission_pct;

示例五：

查询员工表，如果 JOB_ID 是 IT_PROG，薪水增加 10%；如果 JOB_ID 是 ST_CLERK，薪水增加 15%；如果 JOB_ID 是 SA_REP， 薪水增加 20%。对于所有其他的工作角色，不增加薪水。

SELECT last_name, job_id, salary,
        CASE job_id WHEN 'IT_PROG' THEN 1.10*salary
                    WHEN 'ST_CLERK' THEN 1.15*salary
                    WHEN 'SA_REP' THEN 1.20*salary
        			ELSE salary 
        END "REVISED_SALARY"
FROM employees;