08 MySQL数据库基础

数据库学习 重点

1. Mysql数据库的连接与命令行操作
2. SQL的数据基本查询和复杂查询操作
3. SQL的表对象的创建、删除和修改操作

一、数据库基础

1、数据库的定义

A database is an organized collection of data. It is the collection of schemas, tables, queries, reports, views and other objects. The data are typically organized to model aspects of reality in a way that supports processes requiring information, such as modelling theavailability of rooms in hotels in a way that supports finding a hotel with vacancies.
数据库是数据有组织的集合。这是一个对象模式、表、查询、报表、视图和其他对象的 集合。该数据通常被组织到现实的方面进行建模，支持需要的信息流程，如模拟酒店房 间的可用性的方式，支持查找可用酒店的方式。

数据库特点：

1. 实现数据共享。
   数据共享包含所有用户可同时存取数据库中的数据，也包括用户可以用各种方式通过接 口使用数据库，并提供数据共享。
2. 减少数据的冗余度。
   同文件系统相比，由于数据库实现了数据共享，从而避免了用户各自建立应用文件。减 少了大量重复数据，减少了数据冗余，维护了数据的一致性。
3. 数据的独立性。
   数据的独立性包括数据库中数据库的逻辑结构和应用程序相互独立，也包括数据物理结 构的变化不影响数据的逻辑结构。
4. 数据实现集中控制。
   文件管理方式中，数据处于一种分散的状态，不同的用户或同一用户在不同处理中其文 件之间毫无关系。利用数据库可对数据进行集中控制和管理，并通过数据模型表示各种数据 的组织以及数据间的联系。
5. 数据一致性和可维护性，以确保数据的安全性和可靠性。主要包括：
   ① 安全性控制：以防止数据丢失、错误更新和越权使用；
   ②完整性控制：保证数据的正确性、有效性和相容性；
   ③并发控制：使在同一时间周期内，允许对数据实现多路存取，又能防止用户之间的不 正常交互作用；
   ④故障的发现和恢复：由数据库管理系统提供一套方法，可及时发现故障和修复故障， 从而防止数据被破坏

数据库逻辑结构体系

数据库：Database，简称为DB。是长期存储在计算机内，有组织的，统一管理的相关数 据的集合。
数据库管理系统：Database Management System，简称为DBMS。由相互关联的数据 集合以及一组用于访问这些数据的程序组成。
数据库管理系统是位于用户与操作系统之间的一层数据库管理软件。为用户程序提供访问数据库的方法，包括建立数据库、查询、更新以及各种数据库控制等操作。

DBMS的工作模式如下：

1. 接受应用程序的数据请求和处理请求
2. 将用户的数据请求转换为复杂的机器代码
3. 实现对数据库的操作
4. 从对数据库的操作中接受查询结果
5. 对查询结果进行处理和格式转换
6. 将处理结果返回给用户
   DBMS的主要功能
7. 数据库的定义功能
8. 数据库的操纵功能
9. 数据库的保护功能
10. 数据库的维护功能

数据库系统：Database System，简称为DBS。是实现有组织的、动态的存储大量关联数据、方便多用户访问的计算机硬件、软件和数据资源组成的系统。 数据库系统是一种采用数据库技术的计算机系统。

DBS的组成
数据库：包括两类，一类是应用数据的集合，物理数据库，数据库的主体；另一类是各级 数据结构的描述，描述数据库。
硬件：包括计算机、服务器等硬件设备。
软件：包括数据库管理系统、操作系统、各种主语言
数据库管理员(DBA)

数据库发展历史

• 1950年到1960年，磁带用于数据存储。
• 1968年，美国IBM公司推出层次模型的IMS（Information Management System）系 统，硬盘使用系统。
• 1969年，美国CODASYL（Conference On Data System Language）组织发布了 DBTG（Data Base Task Group）报告，总结了当时各式各样的数据库，提出网状模型。
• 1970年，美国IBM公司的Edgar F.Codd博士连续发表论文，提出关系模型，奠定了关系 型数据库的理论基础。Codd也获得了声望很高的ACM图灵奖。
• 1990年以后，数据库进入了高级应用阶段。面向对象的数据库技术、开放数据库互联技 术、数据仓库（大数据）和数据挖掘技术的应用日益广泛

2、数据库管理系统

数据库管理系统分类

关系型数据库

关系型数据库系统实现了关系模型，并用它来处理数据。关系模型在表中将信息与字段关联 起来（也就是schemas），从而存储数据。
这种数据库管理系统需要结构（例如表）在存储数据之前被定义出来。有了表，每一列（字段）都存储一个不同类型（数据类型）的信息。数据库中的每个记录，都有自己唯一的key，作为属于某一表的一行，行中的每一个信息都对应了表中的一列——所有的关系一起，构成 了关系模型。

非关系型(NoSQL)数据库

• 文档型存储
• 列存储
• 缓存存储

主流数据库介绍

数据库的评价排名平台:http://db-engines.com/en/ranking_trend

• Oracle
  甲骨文公 司推出的 使用最广 泛的关系 型数据库 管理系统 高稳定性、可靠性、优秀的安全机制、优秀的安全机制、 采用标准的SQL结构化查询语言、支持大型数据库，数据类 型支持数字、字符、大至2GB的二进制数据，为数据库的 面向对象存储提供数据支持。
  贵、对硬件要求很高、对数据库管理员的经验要求较高

• Microsoft SQL Server
  微软公司 的关系型 数据库管 理系统 真正的客户机/服务器体系结构，图形化的用户界面，使系 统管理和数据库管理更加直观、简单、丰富的编程接口工 具，为用户进行程序设计提供了更大的选择余地，与 WinNT完全集成，提供数据仓库功能和商业智能分析等功 能。
  价格也高，而且不支持跨平台，安全性也相对不高

• MySQL
  开源的关系型数据库管理系统， 开源软件，版本更新较快、开源软件，版本更新较快、性 能很出色、价格便宜，官方网站：www.mysql.com
  缺乏一些存储程序的功能，技术更新目前进入几乎停滞状态

• IBM DB2
  IBM公司 的大型关 系型数据 库管理系 统 DB2主要应用于大型应用系统，具有较好的可伸缩性，可 支持从大型机到单用户环境，应用于所有常见的服务器操 作系统平台下。 DB2提供了高层次的数据利用性、完整 性、安全性、可恢复性，以及小规模到大规模应用程序的 执行能力，具有与平台无关的基本功能和SQL命令
  管理工具比较简陋、价格贵。

• MongoDB
  开源流行 的文档型 NoSQL数 据库 关系数据库和非关系数据库之间的产物，其功能丰富，能 够支持复杂的数据类型，但仍然保留着关系数据库的一些 属性(查询、索引)
  在国内流行度不是很高、相关技术资料比较缺乏

• Cassandra
  开源流行 的列存储 NoSQL数 据库 它是混合型的非关系数据库，在网络社交云计算方面应用 较理想，读操作比写操作快很多，较适用于银行、金融、 数据分析等领域。 技术推广难度

• Redis
  开源的内 存键值对 型NoSQL 数据库 有各种丰富的数据结构，支持数据持久化 内存优化有待进一步提升

二、SQL语言基础

1、SQL定义

SQL，Structured Query Language，结构化查询语言，是一种编程语言，每个数据库的厂商都提供了 对标准SQL的支持。此外每个厂商也基本上扩展了标准SQL的使用。
SQL编程是指用SQL语言来完成对数据库的逻辑性操作。这个操作可以比较简单，只有一 段SQL来完成最基本的数据库操作；也可以比较复杂，需要多段SQL在一起建立起存储过 程来完成复杂的数据库的操作。

注释

SQL的注释有两种形式：单行注释和多行注释。

• 单行注释： 单行以 ‐‐ 或#开头，该行之后的内容以及SQL代码将会被注释掉， 例如：

‐‐ 该行是注释，无论是文本还是SQL代码都会被注释掉 
# SELECT * FROM Country;

• 多行注释： 多行注释是以 /* 开头，以 */ 结尾的，中间的部分将会被全部注释掉。 例如

  /*该行是注释
   该行还是注释 
   SELECT * FROM Country; */

SQL语言分类

分类	命令
数据查询语言（DQL）	Select
数据操作语言（DML）	Insert、Update、 Delete
事务处理语言（TPL）	BEGIN TRANSACTION、Commit、Rollback
数据控制语言（DCL）	Grant、Revoke
数据定义语言（DDL）	Create、Drop、Alter（table,index etc.）
指针控制语言（CCL）	DECLARE CURSOR，FETCH INTO

2、MYSQL数据类型

1、数值型： int(整数)/float（单精度浮点型，带小数）/decimal（字符的方式存储数字）
float(4,1):总长度4位，小数点允许精度1位
decimal规则：涉及到财务金融的金额型数据，一定只能用decimal

2、字符串类型
char：定长字符串；char(10),永远使用10个单位空间，即使只输入两个字
身份证号码，邮编等定长数据适合采用char
varchar：可变长字符串，按照实际的数据占用存储空间，姓名、住址等

3、日期时间类型
date：日期，YYYY-MM-DD(2021-1-1、2021-01-01、2021/01/01)
datetime:日期时间， YYYY-MM-DD HH:MM:SS(2021-01-01 09:56:56)

4、混合类型
ENUM枚举型：只能单选，并且只能选择合法的预定值
set类型：可以多选，空串也是合法值

ENUM 枚举类型 　　
ENUM类型因为只允许在集合中取得一个值，有点类似于单选项。在处理相互排拆的数据时容易让人理解，比如人类的性别。ENUM类型字段可以从集合中取得一个值或使用 null 值， 除此之外的输入将会使 MySQL 在这个字段中插入一个空字符串。另外如果插入值的大小写与集合中值的大小写不匹配，MySQL 会自动使用插入值的大小写转换成与集合中大小写一致的值。 ENUM 类型在系统内部可以存储为数字，并且从 1 开始用数字做索引。一个
ENUM 类型最多可以包含 65536 个元素，其中一个元素被 MySQL 保留，用来存储错误信息， 这个错误值用索引 0 或者一个空字符串表示。 MySQL 认为 ENUM类型集合中出现的值是合法输入，除此之外其它任何输入都将失败。这说明通过搜索包含空字符串或对应数字索引为 0的行就可以很容易地找到错误记录的位置。
SET 类型
SET 类型与 ENUM 类型相似但不相同。SET 类型可以从预定义的集合中取得任意数量的值。并且与 ENUM 类型相同的是任何试图在 SET 类型字段中插入非预定义的值都会使 MySQL 插入一个空字符串。如果插入一个即有合法的元素又有非法的元素的记录，MySQL 将会保留合法的元素，除去非法的元素。一个 SET 类型最多可以包含 64 项元素。在 SET 元素中值被存储为一个分离的“位”序列，这些“位”表示与它相对应的元素。“位”是创建有序元素集合的一种简单而有效的方式。并且它还去除了重复的元素，所以 SET 类型中不可能包含两个相同的元素。希望从 SET 类型字段中找出非法的记录只需查找包含空字符串或二进制值为 0 的行。

3、数据库的基本操作（补）

我们开始创建一个数据库 testdb

#create DATABASE:创建 数据库
#DEFAULT CHARSET：默认的 字符集
#COLLATE：排序规则 utf8_通用_大小写不敏感
CREATE DATABASE testdb 
DEFAULT CHARSET utf8 
COLLATE utf8_general_ci;

4、表的基本操作

表约束（constraint）

约束，constraint，用于实施数据库中的某些数据完整性。当给某一列增加一个约束，数 据库自动确保不满足此约束的数据是绝对不能被接受的。如果用户试图写一个不满足约 束的数据记录，那么Mysql就会对这个非法的SQL语句产生一个错误。 约束是表级的强制规定， 约束放置在表中删除有关联关系的数据 约束可以再创建或者增加包含某列的表时，与该列进行关联，也可以在表创建以后通过 SQL命令ALTER TABLE来实现与该列的关联。表的约束有四种非空（NOT NULL） 、主键（primary key）、唯一键（unique key）、外键（foreight key）

• NOT NULL
  任何列都可以设置为NOT NULL。如果在SQL操作中将一个NULL值赋给某个有NOT NULL 约束的列，那么Mysql会为这个语句返回一个错误。

注意：
NULL：没有东西， not null：不能不填东西
空字符："" / ‘’：它是个字符，但是没有内容，会占用一个字节的空间
空格：" " / ’ ': 它是一个有效的并且可见的合法字符

• 主键(Primary Key,简称PK)
  每个表最多可以有一个主键约束，但主键约束可以由表中的多个列组成。
  在创建表时，通常要有主键列 主键属于表对象，所以主键有一个名字，若没给主键指定名字，mysql会自动分配一个 唯一的名字，在一个表中只能有一个主键对象

特点：

1. 主键一定是唯一的行标识，即每行的主键都不会重复
2. 主键是不允许为空
3. 系统会为主键默认的创建一个索引
4. 主键可以是一个或多个字段
5. 通常情况下，关系型数据库中每张表都会有主键

• 唯一键（Unique Key，简称UK）
  唯一键在每行中均不会出现重复的数据，

特点：

1. 唯一键的字段数据允许为空
2. 唯一键如果有数据那么必须保证每行都不相同，否则无法存储
3. 系统也会为唯一键默认创建一个索引
4. 唯一键也可以是一个或多个字段组成

• 外键（Foreign Key,简称FK）
  外键约束是为数据库中某个与其他表(称作父表)有关系的表(称作子表)而定义的。外键的 值必须事先出现在某个特定表的唯一键或者主键中。外键可包含一列或者多列，但是其 所参考的键也必须包含相同的列。外键也可以和同一个表的主键相关联。如果没有其他 约束限制，外键可以包含NULL值。

1. 一张表的外键关联字段通常情况下关联的是另外一张表的主键
2. 一张表的外键关联字段必须填写外键表中存在的数据
3. 外键关联表的数据被引用了之后，通常不允许删除，

如果一定要删除，可以级 联删除引用数据

1. CASCADE:级联删除整条引用的关联行数据
2. No Action:不允许修改或删除已被引用的父表数据
3. Restrict：同上
4. Set Null：将关联的数据字段值置空

4. 外键字段允许为空
5. 外键字段可以是一个或多个

创建表

创建表的前提下是登录数据库的用户必须拥有 CREATE TABLE权限与 可用的存储空间。

• 基本语法：

CREATE TABLE [schema.]table --创建 表 库名.表名
(
 	column datatype [DEFAULT expr][, ...] --列名 数据类型 默认值 约束
 	...
);

在创建表的时候，必须制定： 表名、字段名（又叫做列名）、 字段类型、
字段长度等；

表名和列名:
必须以字母开头（可以为中文，但是不推荐用）
必须在 1–30 个字符之间
必须只能包含 A – Z , a – z , 0 – 9 , _ , $ , 和 #
必须不能和用户定义的其他对象重名
必须不能是Mysql 的保留字

• 创建完表后我们可以用desc 表名查看表
• 例，创建一个hometown表

CREATE TABLE hrdb.hometown 
( 
	id CHAR(6) PRIMARY KEY, --主键约束
	city VARCHAR(100) DEFAULT 'shenzhen' NOT NULL,  --非空约束
	province VARCHAR(100) DEFAULT 'guangdong'  --默认值：guangdong
)ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci; ‐‐Table created.

修改表

在创建表的是时候没有增加约束，可以通过修改表结构，来添加约束；也可修改表字段结构。

• 添加约束：

‐‐添加约束
 ALTER TABLE table  #改变 表 表名
 	ADD [CONSTRAINT constraint] type (column); 
 	 #增加 约束 约束名  约束类型 字段 

例：

 ‐‐id添加主键约束 
 ALTER TABLE hrdb.person 
 	ADD constraint person_id_pk primary key (ID);
 
‐‐添加外键，使得person的hometown字段参考hometown这个表的id字段 
ALTER TABLE hrdb.person 
ADD CONSTRAINT person_hometown_fk FOREIGN KEY (hometown) 
REFERENCES hrdb.hometown (id);

• 修改字段

‐‐修改字段 
ALTER TABLE table 
MODIFY (column datatype [DEFAULT expr],
		 [ column datatype]...
		);

例：

‐‐姓名不允许为空 
ALTER TABLE hrdb.person 
MODIFY NAME VARCHAR(100) NOT NULL;

‐‐性别字段名由sex修改为gender 
ALTER TABLE hrdb.person CHANGE sex gender CHAR(1);

‐‐性别设置默认值 
ALTER TABLE hrdb.person 
MODIFY gender CHAR(1) DEFAULT 'F';

‐‐修改身高和体重的格式，使其满足999.9 
alter table hrdb.person modify column height float(4,1); 
alter table hrdb.person modify column weight float(4,1);

• 删除字段
  语法：

ALTER TABLE table 
DROP COLUMN colomnName; 

例：

‐‐删除年龄字段 
alter table hrdb.person drop column age; 

• 添加字段
  语法

ALTER TABLE table 
ADD (column datatype [DEFAULT expr] 
	[, column datatype]...);

例

# 添加字段，手机。并设置为唯一约束 
alter table hrdb.person 
Add phone CHAR(11) UNIQUE;

附：

# 完整的一次性创建表的脚本 
CREATE TABLE hrdb.person2 
( 
	ID CHAR(18) NOT NULL,
	NAME VARCHAR(100) NOT NULL, 
	gender CHAR(1) DEFAULT 'F', 
	birthday DATE NOT NULL, 
	height FLOAT(4,1), 
	weight FLOAT(4,1), 
	hometown CHAR(6), 
	phone CHAR(11) UNIQUE, 
	PRIMARY KEY(id), 
	CONSTRAINT `fk_hometown_id2` FOREIGN KEY (hometown) REFERENCES hometown(ID) 
)ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci;

删除表

• 语法

DROP TABLE table;

例：

# 删除表person 
DROP TABLE person;  # 移除 person表

5、表的查询

基本查询

• 基本语法

SELECT *|{[DISTINCT] column|expression [alias],...} 
FROM table;

SQL的查询有以下特点：
1、SQL 语言大小写不敏感。
2、SQL 可以写在一行或者多行
3、关键字不能被缩写也不能分行
4、各子句一般要分行写。
5、使用缩进提高语句的可读性。

例

‐‐简单基础查询 
SELECT * FROM HRDB.EMPLOYEES; 

SELECT * FROM HRDB.DEPARTMENTS D;  /*表的别名 D*/ 

SELECT E.EMPNO, E.ENAME, E.JOB, E.SAL 
FROM SCOTT.EMP E; # 表的别名E和查询指 定字段

SELECT SG.GRADE AS jibie, # 字段的别名 
		SG.LOSAL AS "Low Salary", # 字段的别名
 		SG.HISAL "High Salary" # 字段的别名 
 FROM SCOTT.SALGRADE SG; 
 
SELECT t.* 
FROM SCOTT.EMP t;

说明：
1、* 代表所有列
2、HRDB 是数据库名
3、 BONUS 、 DEPARTMENTS 、 EMPLOYEES 、 SALGRADE 是表名，代表查询哪个表
4、 D 、 E 、 SG 是表的别名
5、对每个字段都可以进行重命名，可以使用AS，也可以不用AS。
6、PS：请注意，字段（列）的重命名，是在查询结果产生以后进行的。
7、SELECT后面跟的是字段（列）
8、FROM 表名关注的是表，后面只能是表名或者是查询出来的子表

条件查询

语法：

SELECT *|{[DISTINCT] column|expression [alias],...} 
FROM table [WHERE condition(s)];

注：
1、条件查询是用 WHERE 语句进行过滤的，在执行完过滤条件后，查询的结果才会展现 出来。
2、WHERE 条件关注的是字段（列）
3、 使用WHERE 子句，将不符合条件的记录过滤掉。

具体使用示例：

#1、基本条件查询 查询员工编号为200或者201的员工
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME, E.HIRE_DATE, E.JOB_ID FROM HRDB.EMPLOYEES E 
WHERE E.EMPLOYEE_ID = 200 OR E.EMPLOYEE_ID = 201; 
# where后还可以用：E.EMPLOYEE_ID >= 201    "<> <= >= > <"

#2、查询员工编号为(200, 201, 202, 205, 208)范围内的员工 
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME, E.HIRE_DATE, E.JOB_ID FROM HRDB.EMPLOYEES E 
WHERE E.EMPLOYEE_ID IN (200, 201, 202, 205, 208); 

#3、查询员工编号为大于等于200并且小于等于209范围内的员工
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME, E.HIRE_DATE, E.JOB_ID FROM HRDB.EMPLOYEES E 
WHERE E.EMPLOYEE_ID > 200 AND E.EMPLOYEE_ID <= 209; 
#where后也可以写成：WHERE E.EMPLOYEE_ID between 200 AND 209; 

#5、查询员工编号为(200, 201, 202, 205, 208)范围内的员工
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME, E.HIRE_DATE, E.JOB_ID FROM HRDB.EMPLOYEES E 
WHERE E.EMPLOYEE_ID IN (200, 201, 202, 205, 208); 

#6、空值查询 查询所有的有确定经理的员工；
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME, E.JOB_ID, E.MANAGER_ID FROM HRDB.EMPLOYEES E 
WHERE E.MANAGER_ID IS NOT NULL; 

注：
判断一个字段是否为NULL，应该用 IS NULL 或 IS NOT NULL ，而不能用‘=’，对NULL的 任何操作的结果还是NULL。

• 模糊条件查询
  模糊条件查询一般是指字符类型模糊查询，使用 LIKE

• 查询用到了通配符，选择相似值
• % 代表任意字符（零个或者一个或者多个字符）
• _ 代表一个字符（不能匹配零个）
• 查询字符或者数字
• % 和_ 可以同时使用

#模糊条件查询 查询员工编号以“任意一个字符+2”开头的员工
SELECT E.EMPLOYEE_ID,
 		E.FIRST_NAME,
 		E.LAST_NAME, 
 		E.HIRE_DATE, 
  		E.JOB_ID, 
  		E.SALARY 
FROM HRDB.EMPLOYEES E
WHERE E.EMPLOYEE_ID LIKE '_2%' 

• 函数条件查询

函数	描述	示例
UPPER	将字符串变成全部大写	UPPER(‘SmitH’)=‘SMITH’
LOWER	将字符串变成全部小写	LOWER(‘SmitH’)=‘smith’
CONCAT	连接函数	CONCAT(‘SmitH’,’.’, ‘LEE’)=‘Smith.LEE’
CONCAT	连接多个字符成为一个 新的字符	CONCAT(‘Hello’,‘World’)=‘HelloWorld’
SUBSTRING	截取字符串中指定的子 字符串	SUBSTR(‘HelloWorld’,2,5)=‘elloW’
LENGTH	获取字符串的长度	LENGTH(‘HelloWorld’)=10
INSTR	查询字符串中指定字符 的位置	INSTR(‘HelloWorld’, ‘Wor’)=6
LPAD	输出指定位数的字符 串，将左侧全部补指定 字符	LPAD(salary,10,’*’)=’*****24000’
RPAD	输出指定位数的字符 串，将右侧全部补指定 字符	RPAD(salary, 10, ‘*’)=‘24000*****’
TRIM	除去字符串中前后的空格, 也可以去除前后的 某个字符	trim(’ john ')=‘john’ ； TRIM(‘j’ FROM ‘jjjjjjjjohnjjjjj’)='ohn
ROUND	四舍五入并保留指定小数位数	ROUND(45.926, 2)=45.93
TRUNCATE	直接截断舍去并保留指定小数位数	TRUNCATE(45.926, 2)=45.92
MOD	求余数	MOD(1600, 300)=100
SYSDATE()、now()	当前日期时间	SELECT SYSDATE();
CURTIME()	当前时间	select curtime();
WEEKDAY(date)	返回日期date是 星期几(0=星期 一,1=星期二,…… 6= 星期天)	select WEEKDAY(sysdate());
MONTHNAME(date)	返回日期的月份 名	select MONTHNAME(sysdate());
DATE_FORMAT(date,format)	输出指定格式的 日期	select DATE_FORMAT(sysdate(),’%Y‐ %m‐%d %H:%i:%s’);
DATE_ADD	日期增加函数	SELECT DATE_ADD(“1997‐12‐ 31 23:59:59”,INTERVAL 1 SECOND);
（）	增加一天	SELECT DATE_ADD(“1997‐12‐ 31 23:59:59”,INTERVAL 1 DAY);
（）	后退一天10小时	SELECT DATE_ADD(“1998‐01‐ 01 00:00:00”, INTERVAL “‐1 10” DAY_HOUR);

使用示例：

# 查询所有员工的名字和姓名，并直接相连。
SELECT UPPER(E.FIRST_NAME) "Upper Name", #大写显示
		LOWER(E.FIRST_NAME) "Lower Name", #小写显示
		CONCAT(E.FIRST_NAME, '.', E.LAST_NAME) "Full Name"  #拼接
FROM HRDB.EMPLOYEES E; 

#函数查询 
SELECT CONCAT(E.FIRST_NAME, E.LAST_NAME) "Full Name", 
		E.JOB_ID, 
		TRIM(E.first_name), #清空前后字符串
		LENGTH(E.LAST_NAME), 
		LPAD(E.SALARY, 11, '*'), 
		RPAD(E.SALARY, 12, '$') 
FROM HRDB.EMPLOYEES E 
WHERE SUBSTRING(JOB_ID, 4) = 'REP';

SELECT ROUND(45.923, 2), ROUND(45.923, 0), ROUND(45.923, ‐1) , ROUND(45.923, ‐2) 

SELECT TRUNCATE(45.923,2), TRUNCATE(45.923,0), TRUNCATE(45.923,‐1), TRUNCATE(45.923,‐2)

• 查询优先级
  
  可以用括号 () 来改变查询的优先级

分组

其中： COUNT(SALARY) 是求SALARY不为NULL的记录数， COUNT(*) 是求记录总数 分组函数忽略空值NULL

例

#按照职位包含“REP”的员工的职位进行统计，统计每个组的平均工资、最高工资、最低工资 和工资总和。
SELECT E.JOB_ID, 
		AVG(E.SALARY), 
		MAX(E.SALARY), 
		MIN(E.SALARY), 
		SUM(E.SALARY) 
FROM HRDB.EMPLOYEES E 
WHERE E.JOB_ID LIKE '%REP%' 
GROUP BY E.JOB_ID; 

• DISTINCT 关键字

# DISTINCT关键字
SELECT DISTINCT e.job_id 
FROM hrdb.employees e;

• DISTINCT关键字也可以使用在分组函数中。
• COUNT(DISTINCT expr) 返回 expr非空且不重复的记录总数

 #DISTINCT去除了每个组中重复的工资数目计数
SELECT COUNT(DISTINCT E.SALARY), 
		COUNT(E.SALARY) 
FROM HRDB.EMPLOYEES E 
GROUP BY E.JOB_ID; 

GROUP BY子句也是可以多个字段进行的

SELECT E.JOB_ID,
		 E.DEPARTMENT_ID, 
		 COUNT(DISTINCT E.SALARY),
		 COUNT(E.SALARY) 
FROM HRDB.EMPLOYEES E 
GROUP BY E.JOB_ID, E.DEPARTMENT_ID; #所有的数据将会以(JOB_ID + DEPARTMENT_ID)作为依据分组，统计每一个组的计数

• HAVING 过滤分组

• 行已经被分组。
• 使用了组函数。
• 满足HAVING 子句中条件的分组将被显示。

语法：

SELECT column, group_function 
FROM table [WHERE condition] 
[GROUP BY group_by_expression] 
[HAVING group_condition]
[ORDER BY column];

示例：

#分组后进行过滤 
SELECT JOB_ID, 
		SUM(SALARY) PAYROLL 
FROM HRDB.EMPLOYEES 
WHERE JOB_ID NOT LIKE '%REP%'
GROUP BY JOB_ID 
HAVING SUM(SALARY) > 13000 
ORDER BY SUM(SALARY); #先筛选出合适的记录进行分组统计，统计以后再次对分组统计好的数据进行筛选，然后排序

排序

排序语法：

SELECT *|{[DISTINCT] column|expression [alias],...} 
FROM table 
[WHERE condition(s)] 
[ORDER BY {column, expr, alias} [ASC|DESC]];

• 使用 ORDER BY 字句进行排序操作。
• ASC是升序（默认，在一般关系型数据库中）
• DESC是降序
• ORDER BY字句用在SELECT语句的结尾
• 多个字段的排序是支持的。排序的效果是按照字段的优先级进行，若当前字段无法分出 高低，则依靠后续的字段进行排序确认。
• 排序是可以使用不在SELECT 列表中的列排序的。

例：

 #默认排序，查询所有的员工，并按照入职时间从大到小降序排序 
SELECT E.EMPLOYEE_ID, 
E.FIRST_NAME,
 E.JOB_ID,
  E.DEPARTMENT_ID, 
  E.HIRE_DATE 
  FROM HRDB.EMPLOYEES E 
  ORDER BY E.HIRE_DATE DESC; #不加desc就默认为升序

#多个字段排序 查询员工的职位降序，工资降序依次进行排序 
SELECT E.EMPLOYEE_ID, 
		E.FIRST_NAME,
		E.JOB_ID,
		E.SALARY 
FROM HRDB.EMPLOYEES E 
ORDER BY E.JOB_ID DESC, E.SALARY DESC; 

子查询

• 子查询可以理解为查询嵌套，要包含在括号内。
• 将子查询放在比较条件的右侧。
• 一般情况下不要在子查询中使用ORDER BY 子句。
• 单行操作符对应单行子查询，多行操作符对应多行子查询。
• 子查询可以放在select、from、where、having等里

#子查询 
SELECT LAST_NAME, SALARY 
FROM HRDB.EMPLOYEES 
WHERE SALARY > 
	(SELECT SALARY FROM HRDB.EMPLOYEES WHERE LAST_NAME = 'Abel'); #查询工资高于“Abel”的所有员工的名字和工资

多表查询

笛卡尔积是集合的一种，假设A和B都是集合，A和B的笛卡尔积用A X B来表示，是所 有有序偶（a,b）的集合，其中a属于A，b属于B。

SELECT COUNT(*) 
FROM HRDB.EMPLOYEES E; #107行数据 
SELECT COUNT(*) 
FROM HRDB.DEPARTMENTS D; #27行数据 ‐‐笛卡儿积 
SELECT * 
FROM HRDB.EMPLOYEES E, HR.DEPARTMENTS D; #107 * 27 = 2889行数据
#记录数是两个表的记录数相乘 ‐‐所有表中的所有行互相连接

多表查询的连接方式有内连接与外连接，其中外连接分为左外连接、右外连接与全外连接。
内连接只返回满足连接条件的数据。 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条 件的行 ，这种连接称为左（或右）外联接。 两个表在连接过程中除了返回满足连接条件的行以外还返回两个表中不满足条件的行 ，这种连接称为全外联接。

• 内连接
  连接语法有两种：

#语法1：
 	SELECT table1.column, table2.column,... 
 	FROM table1, table2 
	WHERE table1.column1 = table2.column2 AND table.column1 = 'value'; 
#语法2：
	SELECT table1.column, table2.column,... 
	FROM table1 INNER JOIN table2 ON table1.column1 = table2.column2 			
	WHERE table1.column1 = 'value';

• 使用连接在多个表中查询数据。
• 在 WHERE 子句中写入连接条件。
• 在表中有相同列时，在列名之前加上表名前缀。
• 使用表名前缀在多个表中区分相同的列。
• 使用表名可以提高效率。
• 在不同表中具有相同列名的列可以用别名加以区分。

#例：查询每个员工的员工编号、姓名、工资、和部门编号、部门名称
SELECT E.EMPLOYEE_ID, concat(E.FIRST_NAME, '.' , E.LAST_NAME) AS "employee_name", 
		E.SALARY, 
		E.DEPARTMENT_ID DEPARTMENT_ID1, 
		D.DEPARTMENT_ID DEPARTMENT_ID2, 
		D.DEPARTMENT_NAME 
FROM HRDB.EMPLOYEES E INNER JOIN HRDB.DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID; 

• 左外连接
  左连接是以左边的表为基础，左边的表符合过滤条件的都筛选出来，右边的表若能匹配 的记录，直接匹配，否则补NULL。

查询语法 ：

SELECT table1.column, table2.column,... 
FROM table1 LEFT JOIN table2 ON table1.column = table2.column;

查询示例：

#查询所有员工的员工编号、姓名、部门编号、部门名称。确保列出所有的员工。 
SELECT E.EMPLOYEE_ID, 
		concat(E.FIRST_NAME, '.', E.LAST_NAME), 
		E.DEPARTMENT_ID,
		D.DEPARTMENT_ID, 
		D.DEPARTMENT_NAME 
FROM HRDB.EMPLOYEES E LEFT JOIN HRDB.DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID; 

• 右外连接
  查询语法：

SELECT table1.column, table2.column,... 
FROM table1 RIGHT JOIN table2 ON table1.column = table2.column;

查询示例：

#查询所有员工的员工编号、姓名、部门编号、部门名称。确保列出所有的部门。
SELECT E.EMPLOYEE_ID, 
		concat(E.FIRST_NAME, '.', E.LAST_NAME),
		 E.DEPARTMENT_ID, 
		 D.DEPARTMENT_ID, 
		 D.DEPARTMENT_NAME 
FROM HRDB.EMPLOYEES E RIGHT JOIN HRDB.DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID; 

• 全外连接
  查询语法：

# oracle的写法
SELECT table1.column, table2.column,... 
FROM table1 FULL JOIN table2 ON table1.column = table2.column;

# 其他写法
左连接
union
右链接

查询示例：

#查询所有员工的员工编号、姓名、部门编号、部门名称。确保列出所有的员工和部门。
#写法1
SELECT E.EMPLOYEE_ID, 
		CONCAT(E.FIRST_NAME, '.', 
		E.LAST_NAME), 
		E.DEPARTMENT_ID, 
		D.DEPARTMENT_ID, 
		D.DEPARTMENT_NAME 
FROM HRDB.EMPLOYEES E LEFT JOIN HRDB.DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID 
UNION 
SELECT E.EMPLOYEE_ID, 
		CONCAT(E.FIRST_NAME, '.', E.LAST_NAME), 
		E.DEPARTMENT_ID, 
		D.DEPARTMENT_ID, 
		D.DEPARTMENT_NAME 
FROM HRDB.EMPLOYEES E RIGHT JOIN HRDB.DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID; 

#oracle的写法： 
SELECT E.EMPLOYEE_ID, 
	concat(E.FIRST_NAME, '.', E.LAST_NAME),
	 E.DEPARTMENT_ID, 
	 D.DEPARTMENT_ID, 
	 D.DEPARTMENT_NAME 
FROM HRDB.EMPLOYEES E FULL JOIN HRDB.DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID; 

6、表的数据操作

新增（insert）

• 语法：

INSERT INTO table [(column [, column...])] VALUES (value [, value...]); 
#使用这种语法一次只能向表中插入一条数据。

• 为每一列添加一个新值。
• 按列的默认顺序列出各个列的值。
• 在 INSERT 子句中随意列出列名和他们的值。
• 字符和日期型数据应包含在单引号中。

• 插入空值有两种写法：

#指定某些字段插入 插入完后可查询一下检查插入效果
INSERT INTO HRDB.DEPARTMENTS (DEPARTMENT_ID, DEPARTMENT_NAME) VALUES (330, 'Purchasing');

#不指定插入具体字段
INSERT INTO HRDB.DEPARTMENTS VALUES (400, 'Finance', NULL, NULL);
SELECT * FROM HR.DEPARTMENTS D WHERE D.DEPARTMENT_ID=400;#验证插入是否成功

• 若插入的时候不指定字段，则值按照表字段的定义顺序一一对应
• 若插入的时候指定字段，则值必须与字段一一对应
• 主键或非空字段如果不能自增，且没有默认值时必须指明
• 自增的列可以不用管，系统会自动指定值，注意非空，主键、外键、唯一键的规则

• 批量插入：

#把其他表的数据批量插入到新表中
INSERT INTO HRDB.SALES_REPS 
	(ID, NAME, SALARY, COMMISSION_PCT) 
	SELECT EMPLOYEE_ID, LAST_NAME, SALARY, COMMISSION_PCT 
	FROM HRDB.EMPLOYEES 
	WHERE JOB_ID LIKE '%REP%';

注：两张表的结构需要完全一致，否则插入失败

更新（update）

• 语法：

UPDATE table SET column = value [, column = value, ...] 
[WHERE condition]; #可以一次更新多条数据

例：

UPDATE HRDB.EMPLOYEES 
	SET DEPARTMENT_ID = 70 
WHERE EMPLOYEE_ID = 113;
#1 row updated. 
SELECT * FROM HRDB.EMPLOYEES E WHERE E.EMPLOYEE_ID=113;  #更新完数据 检查一下
#创建一张和HRDB.EMPLOYEES表结构一模一样的表 
create table HRDB.EMP_COPY as select * from HRDB.EMPLOYEES;

#更新 114号员工的工作和工资使其与 205号员工相同
UPDATE HR.EMPLOYEES 
	SET JOB_ID = 
		(SELECT JOB_ID FROM HR.EMPLOYEES WHERE EMPLOYEE_ID = 205), 	
		SALARY = 
		(SELECT SALARY FROM HR.EMPLOYEES WHERE EMPLOYEE_ID = 205)
 WHERE EMPLOYEE_ID = 114;
 #检查一下 
 SELECT * FROM HR.EMPLOYEES E WHERE E.EMPLOYEE_ID=114 OR E.EMPLOYEE_ID=205;

删除（delete）

• 语法：

DELETE [FROM] table [WHERE condition];

• 使用实例：

# 从员工表中删除所有部门名称 like '%public%' 的员工 
DELETE FROM HRDB.employees 
WHERE department_id = 
	(SELECT department_id 
	FROM HRDB.departments 
	WHERE department_name LIKE '%Public%');
	#在 DELETE 中使用子查询，使删除基于另一个表中的数据。

7、事务（transaction）和锁（Lock）

我们在操作数据时并不会立马更新到数据库，需要通过commit命令提交修改到数据库，只是SQLyog有一个自动提交的开关默认打开了。故我们在SQLyog操作时会实时更新到数据库
一个事物的过程：

/* 事务的过程
begin transaction  ：开始事务
第一个修改
第二个修改
如果都成功
	commit;  # 提交修改
否则
	rollback;  # 锁，回滚操作（撤销）
end transaction		：结束事务
*/

• 设置SQLyog里是否自动提交：SET autocommit = 'off';
• 则做完增删改后一定需要commit，rollback，否则修改的数据会被锁定（般锁定的是行，其他用户无法操作）
• 提交和回滚只能做一次
• 使用 COMMIT 和 ROLLBACK 语句,可以: 确保数据完整性，数据改变被提交之前预览，将逻辑上相关的操作分组。

8、用户管理

创建用户：

create user ‘用户名'@host  identified by '密码' ;

• host(主机）有3种写法
  localhost ：本机客户端登录本机 mysql
  具体ip地址：只有这台IP的电脑可以登录我的Mysql
  ’ % ’ ：网络中其他电脑可以登录我的Mysql
• 创建的用户可以在mysql数据库的user表最终查询到
• 创建完后需要刷新权限（FLUSH PRIVILEGES）才会生效

#例
CREATE USER 'demo_c'@localhost IDENTIFIED BY '123456';
FLUSH PRIVILEGES;

用户权限管理

授权（grant）：grant 什么权限 on 数据库.对象 to 用户
回收权限（REVOKE）：revoke select on 库名.表名 from 用户

#例如：将testdb中的person表的查询/新增权限给demo_c@localhost
GRANT SELECT, INSERT ON testdb.`person` TO demo_c@localhost;

# 例 将testdb.person表的查询权限从用户收回
REVOKE SELECT ON testdb.`person` FROM demo_c@localhost;
FLUSH PRIVILEGES;

删除用户
语法：drop user 用户

# 例
DROP USER demo_c@localhost;

切换默认数据库
语法：use 数据库

USE testdb;		#切换默认数据库为testdb
SHOW TABLES;		#显示当前默认数据库下的所有表名

9、视图

视图的作用有：控制数据访问 简化查询 数据独立性 避免重复访问相同的数据

• 创建视图
  通过查询的一张表创建一个视图，原表或者视图的数据修改了会相互同步跟新

注：SQLyog里图形化操作修改视图数据不会同步到原表是个BUG

语法：

create or replace view  表名.视图名 AS   #创建和修改视图都是这样子的
SELECT *
from 库名.表名 表别名
where  查询条件;

视图是实际存在的，其操作和查询的操作一样

• 修改视图

#1、将员工表的销售部门员工ID，姓名，薪水，提成比率创建一个视图
CREATE OR REPLACE VIEW hrdb.v_sales AS   
SELECT e.`employee_id`, 
	e.`first_name`,e.`last_name`,
	e.`salary`, e.`commission_pct`
FROM hrdb.`employees` e
WHERE e.`department_id` = 80;
#2、
UPDATE v_sales v SET v.`first_name` ='Karen1234567'
WHERE v.`employee_id`=146;	
#3、
SELECT * FROM hrdb.`v_sales`;

• 删除视图

DROP VIEW view; #删除视图view

• 删除视图只是删除视图的定义，并不会删除基表的数据

10、存储过程

存储过程中可以包含逻辑控制语句和数据操纵语句，它可以接受参数、输出参数、返回 单个或多个结果集以及返回值。 由于存储过程在创建时即在数据库服务器上进行了编译并存储在数据库中，所以存储过 程运行要比单个的SQL语句块要快。同时由于在调用时只需用提供存储过程名和必要的参 数信息，所以在一定程度上也可以减少网络流量、简单网络负担。

# 基本语法
CREATE OR REPLACE PROCEDURE 存储过程名字 
( 
	参数1 IN NUMBER, 
	参数2 IN NUMBER
 ) IS 
 变量1 INTEGER :=0; 
 变量2 DATE; 
 BEGIN 
 [执行语句]
  END

使用示例
创建一个表Student，并插入一百万条记录，在奇数的时候插入Tom记录，偶数的时候插 入Lucy记录。使用存储过程来实现。

#创建表student
CREATE TABLE HRDB.student (
	 s_id INT, 
	 s_name VARCHAR(20), 
	 s_sex CHAR(2) );
# 插入数据 ‐‐创建存储过程
DROP PROCEDURE IF EXISTS hrdb.`p‐2`; 
DELIMITER $$ 
CREATE
	PROCEDURE hrdb.`p‐2`() 
	BEGIN
	  	DECLARE ii INT DEFAULT 0; 
	REPEAT
		IF (MOD(ii,2) = 1) THEN 
			INSERT INTO HRDB.student VALUES(2, 'Tom', '男');
		ELSE
			INSERT INTO HRDB.student VALUES(ii, 'Lucy', '女');
		END IF;
		SET ii = ii + 1; 
		UNTIL ii >= 5 
		END REPEAT; 
		COMMIT; 
		SELECT '插入数据完成!';
	END $$ 
	DELIMITER ; 
#  p‐2调用示例  调用存储
CALL hrdb.`p‐2`;

附

思维导图