MySQL数据库的使用

MySQL数据库的使用

数据库基础知识

数据库中各个概念之间的关系

数据库基本概念

• 数据
  是指对客观事物进行描述并可以鉴别的符号，这些符号是可识别的、抽象的，不仅指狭义上的数字，有多种表现形式：字母、文字、文本、图像、音频和视频等。
• 数据库
  数据库是数据管理的有效技术，是由一批数据构成的有序集合，这些数据存放在结构化的数据表里。数据表之间相关联，反映客观事物间的本质联系。
• 数据库管理系统
  Database Management System(DBMS),用来定义和管理数据的软件
• 数据库应用程序
  Database Application System(DBAS),在数据库管理系统的基础上，使用数据库管理系统的语法，开发的直接面对最终用户的应用程序。
• 数据库管理员
  Database Administrator(DBA),是指对数据库管理系统进行操作的人员负责数据库的运营和维护。

数据库分类

关系型数据库

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

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

缺点：

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

非关系型数据库

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

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

缺点：

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

MySQL基础知识

MySQL简介

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

MYSQL特点

• MySQL是开源的
• 支持大型系统的数据库，可以处理拥有上千万条记录的大型数据库。MYSQL使用标准的SQL数据语言形式。
• 可以运行在多个系统上，支持多种语言。
• 存储数据量大
  

SQL语言

SQL是数据库标准化语言，所有关系型数据库都适用。

SQL语言简介

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

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

SQL 标准

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

SQL语言分类

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

• SELECT
• FROM
• WHERE
• ORDER BY
• HAVING

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

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

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

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

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

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

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

• COMMIT:提交事物
• ROLLBACK:回滚事物
• SAVEPOINT：设置回滚点

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

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

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

创建和删除数据库

CREATE DATABASE 创建数据库关键字
test 数据库名称
DEFAULT CHARACTER SET 指定默认编码类型关键字
utf8; 指定编码

DROP DATABASE 删除数据库关键字
test 数据库名称

使用DDL语句创建数据库

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

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

创建数据库：

CREATE DATABASE test DEFAULT CHARACTER SET UTF-8;

查看数据库：

SHOW DATABASES;

查看数据库编码：

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

使用DDL语句删除数据库

drop database test;

选择数据库

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

use 数据库名;

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

create database xyy  default character set utf8;

选择该数据库

use xyy;

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不用指定字符集。

创建表与删除表

创建表

CREATE TABLE 创建表关键字
表名（列名 类型，列名 类型 …） 表名、列名与类型

使用DDL语句创建表

CREATE TABLE 表名(列名 类型，列名  类型.....)

创建一个 emp 表包含雇员 ID ，雇员名字，雇员薪水。

create table emp(emp_id int,emp_name varchar(10),salary float(8,2));

查看已创建的表

show tables;

删除表

DROP TABLE 删除表关键字
表名； 表名

使用DDL语句删除表

DROP TABLE 表名;

删除 emp 表。

drop table emp;

修改表

修改表名

ALTER TABLE 修改表关键字
旧表名 旧表名
RENAME 修改表名关键字
新表名； 新表名

使用DDL语句修改表

ALTER TABLE 旧表名 RENAME 新表名;

创建一个 emp 表包含雇员 ID ，雇员名字，雇员薪水。

create table emp(emp_id int,emp_name varchar(10),salary double(8,2));

将 emp表名修改为 employees。

alter table emp rename employees;

修改列名

ALTER TABLE 修改表关键字
表名 表名
CHANGE COLUMN 修改列名关键字
旧列名 旧列名
新列名 类型; 新列名与类型

使用DDL语句修改列名

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

将 emp 表中的 emp_name 修改为 name。

ALTER TABLE emp CHANGE COLUMN emp_name name varchar(10);

修改列类型

ALTER TABLE 修改表关键字
表名 表名
MODIFY 修改列类型关键字
列名 新类型; 列名 新类型

使用DDL语句修改列类型

ALTER TABLE 表名 MODIFY 列名 新类型;

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

ALTER TABLE emp MODIFY name varchar(40);

添加新列

ALTER TABLE 修改表关键字
表名 表名
ADD COLUMN 修改新列关键字
新列名 类型; 新列名 类型

使用DDL语句添加新列

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

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

ALTER TABLE emp ADD COLUMN  commission_pct double(8,2);

删除指定列

ALTER TABLE 修改表关键字
表名 表名
DROP COLUMN 删除列关键字
列名 ; 指定删除列的列名

使用DDL语句删除指定列

ALTER TABLE 表名 DROP COLUMN 列名;

删除 emp 表中的 commission_pct。

ALTER TABLE emp DROP COLUMN  commission_pct;

MySQL中的约束

约束概述

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

主键约束(Primary Key) PK

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

外键约束(Foreign Key) FK

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

唯一性约束(Unique)

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

非空约束(Not Null)

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

检查约束(Check)

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

添加主键约束(Primary Key)

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

修改表添加主键约束

使用DDL语句添加主键约束

ALTER TABLE 表名 ADD PRIMARY KEY(列名)

将 emp 表中的 emp_id 修改为主键。

alter table emp add primary key(emp_id);

主键自增长

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

alter table 表名 modify 主键 类型 auto_increment

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

alter table emp modify emp_id int auto_increment;

删除主键

使用DDL语句删除主键

ALTER TABLE 表名 DROP PRIMARY KEY;

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

删除emp表中的 emp_id 主键约束。
去掉自动增长：

alter table emp modify emp_id int;

删除主键：

ALTER TABLE emp DROP PRIMARY KEY;

添加外键约束(Foreign Key)

使用DDL语句添加外键约束

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

创建 dept 表包含 dept_id 、dept_name ，location_id。

create table dept(dept_id int,dept_name varchar(10),location_id int);

修改dept表，向dept_id列添加主键约束与自动递增。

alter table emp add primary key(dept_id);
alter table emp modif dept_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 
dept(dept_id);

删除外键约束(Foreign Key)

使用DDL语句删除外键约束

ALTER TABLE 表名 DROP FOREIGN KEY 约束名;

删除 dept_id 的外键约束。

alter table emp drop foreign key emp_fk;

添加唯一性约束(Unique)

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

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

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

alter table emp add constraint emp_uk unique(name);

删除唯一性约束(Unique)

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

ALTER TABLE 表名 DROP KEY 约束名;

删除 name 的唯一约束。

alter table emp drop key emp_uk;

非空约束(Not Null)

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

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

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

alter table emp modify salary double(8,2) not null;

删除非空约束

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

ALTER TABLE 表名 MODIFY 列名 类型  NULL;

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

alter table emp modify salary double(8,2)  null;

创建表时添加约束

查询表中的约束信息：

SHOW KEYS FROM 表名;

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

create table depts(deptment_id int primary key auto_increment,deptment_name varchar(10) 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)

1.创建表时指定列的默认值

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");

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

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

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

alter table emp3 add column job_id int default 0;

** 3.插入数据时的默认值处理**

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

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

insert into emp3 values(default,"Amy",default,default);

更新数据(UPDATE)

UPDATE 表名 SET 列名1=值,列名2=值 WHERE 条件;

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

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

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

删除数据(DELETE)

1.DELETE删除数据

DELETE FROM 表名 WHERE 条件;

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

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

delete from emp3 where emp_id=1;

2.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 OR expression	选择指定的字段或表达式
alias	给所有选择的列不同的标题
FROM table	指定包含列的表

查询中的列选择

1.选择所有列

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

select * from departments;

2.选择指定列

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

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

select department_name from departments;

查询中的算术表达式

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

使用算术运算符

查询雇员的年薪，并显示他们的雇员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;

运算符的优先级

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

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

SELECT employee_id,LAST_NAME,salary*12+100
FROM employees;

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

SELECT EMPLOYEE_ID,LAST_NAME,(SALARY+100)*12 FROM employees; 

MySQL中的别名

使用列别名

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

AS可省略
**查询 employees 表将雇员 last_name 列定义别名为 name。
**

SELECT LAST_NAME AS NAME FROM employees;
SELECT LAST_NAME NAME FROM employees;

使用表别名

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

AS可省略

**查询 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=90;

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 SALARY LIKE '_e%;'

使用NULL条件

• NULL 条件，包括 IS NULL 条件和 IS NOT NULL 条件。
• ISNULLL条件用于空值测试。空值的意思就是难以获得、未指定的、未知的或者不适用的。因此，不能用=，因为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%';

优先规则

重点记住：NOT>AND>OR

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

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

• 第一个条件是job_id是AD_PRES或者SA_REP。
• 第二个条件是薪水高于$15000。

使用 ORDER BY排序

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

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

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

SELECT EMPLOYEE_ID,LAST_NAME,SALARY
FROM employees ORDER BY SALARY ASC;

SELECT EMPLOYEE_ID,LAST_NAME,SALARY
FROM employees ORDER BY SALARY;

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

SELECT EMPLOYEE_ID,LAST_NAME
FROM employees ORDER BY LAST_NAME DESC;

使用别名排序

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

SELECT EMPLOYEE_ID,LAST_NAME,SALARY*12 annsal
FROM employees ORDER BY annsal;

多列排序

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

SELECT DEPARTMENT_ID,LAST_NAME,SALARY
FROM employees ORDER BY DEPARTMENT_ID ASC,SALARY DESC;

练习：

1.创建一个查询，显示收入超过 12,000 的雇员的名字和薪水。

SELECT LAST_NAME,SALARY
FROM employees WHERE SALARY>=12000;

2.创建一个查询，显示雇员号为 176 的雇员的名字和部门号。

SELECT DEPARTMENT_ID,LAST_NAME
FROM employees WHERE EMPLOYEE_ID=176;

3.显示所有薪水不在 5000 和 12000 之间的雇员的名字和薪水。

SELECT LAST_NAME,SALARY 
FROM employees 
WHERE SALARY  NOT BETWEEN 5000 AND 12000;

4.显示所有在部门 20 和 50 中的雇员的名字和部门号，并以名字按字母顺序排序。

SELECT LAST_NAME,DEPARTMENT_ID
FROM employees
WHERE  DEPARTMENT_ID IN(20,50)
ORDER BY LAST_NAME ASC;

5.列出收入在 5,000 和 12,000 之间，并且在部门 20 或50 工作的雇员的名字和薪水。将列标题分别显示为 Employee 和 Monthly Salary.

SELECT LAST_NAME Employee,SALARY 'Monthly Salary'
FROM employees
WHERE SALARY BETWEEN 5000 AND 12000 AND DEPARTMENT_ID IN (20,50);

6.显示所有没有主管经理的雇员的名字和工作岗位。

SELECT LAST_NAME,JOB_ID
FROM employees
WHERE MANAGER_ID IS NULL;

7.显示所有有佣金的雇员的名字、薪水和佣金。以薪水和佣金的降序排序数据。

SELECT LAST_NAME,SALARY,COMMISSION_PCT
FROM employees
WHERE COMMISSION_PCT IS NOT NULL
ORDER BY SALARY desc,COMMISSION_PCT desc;

8.显示所有名字中有一个 a 和一个 e 的雇员的名字。

SELECT LAST_NAME
FROM employees
WHERE LAST_NAME LIKE '%a%' AND LAST_NAME LIKE'%e%';

9.显示所有工作岗位是销售代表（SA_REP）或者普通职员(ST_CLERK)，并且薪水不等于 2,500、3,500
或 7,000 的雇员的名字、工作岗位和薪水。

SELECT LAST_NAME,JOB_ID,SALARY
FROM employees
WHERE JOB_ID IN ('SA_REP','ST_CLIERK')
AND SALARY NOT IN(2500,3500,7000);

SQL函数

函数介绍

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

• 执行数据计算
• 修改单个数据项
• 操纵输出进行行分组
• 格式化显示的日期和数字
• 转换列数据类型
  SQL 函数有输入参数，并且总有一个返回值。

函数分类

1. 单行函数
   单行函数仅对单个行进行运算，并且每行返回一个结果。
   常见的函数类型：

• 字符
• 数字
• 日期
• 转换
  2.多行函数
  多行函数能够操纵成组的行，每个行组给出一个结果，这些函数也被称为组函数。

单行函数

单行函数的分类

字符函数

大小写处理函数

函数	描述
LOWER(s) OR LCASE(s)	将字符串 s 转换为小写
UPPER(s) OR UCASE(s)	将字符串s转换为大写

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

SELECT EMPLOYEE_ID,UPPER(LAST_NAME),DEPARTMENT_ID
FROM employees
WHERE LAST_NAME='Davies';

字符处理函数

函数	描述
LENGTH(s)	返回字符串 s 的长度
CONCAT(s1,s2…sn)	字符串 s1,s2 等多个字符串合并为一个字符串
LPAD(s1,len,s2)	在字符串 s1 的开始处填充字符串 s2，使字符串长度达到 len
LTRIM(s)	去掉字符串 s 开始处的空格
REPLACE(s,s1,s2)	将字符串 s2 替代字符串 s中的字符串 s1
REVERSE(s)	将字符串s的顺序反过来
RPAD(s1,len,s2)	在字符串 s1 的结尾处添加字符串 s2，使字符串的长度达到 len
RTRIM(s)	去掉字符串 s 结尾处的空格
SUBSTR(s, start,length)	从字符串 s 的 start 位置截取长度为 length 的子字符串
SUBSTRING(s,start, length)	从字符串 s 的 start 位置截取长度为 length 的子字符串
TRIM(s)	去掉字符串 s 开始和结尾处的空格
INSTR(s,'s1)	判断’s1’是否在s中存在，如果存在则返回’s1’在s中索引即位置

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

SELECT EMPLOYEE_ID,JOB_ID,CONCAT(LAST_NAME,FIRST_NAME) NAME,
LENGTH(FIRST_NAME),INSTR(NAME,'a')
FROM employees
WHERE SUBSTR(JOB_ID,4)='REP'

数字函数

函数名	描述
ABS(x)	返回 x 的绝对值
AVG(expression）	返回一个表达式的平均值，expression是一个字段
CEIL(x) OR CEILING(x)	返回大于或等于 x的最小整数
COUNT(expression)	返回查询的记录总数，expression 参数是一个字段或者 *号
n DIV m	整除，n 为被除数，m 为除数
EXP(x)	返回 e 的 x 次方
FLOOR(x)	返回小于或等于 x的最大整数
GREATEST(expr1,expr2, expr3, …)	返回列表中的最大值
LEAST(expr1,expr2, expr3, …)	返回列表中的最小值
LN	返回数字的自然对数，以 e 为底。
MAX(expression)	返回字段expression 中的最大值
MIN(expression)	返回字段expression 中的最小值
MOD(x,y)	返回 x 除以 y 以后的余数
PI()	返回圆周率(3.141593）
POW(x,y) OR POWER(x,y)	返回 x 的 y 次方
RAND()	返回 0 到 1 的随机数
ROUND(x)	返回离 x 最近的整数
SIGN(x)	返回 x 的符号，x 是负数、0、正数分别返回 -1、0 和 1
SQRT(x)	返回x的平方根
SUM(expression)	返回指定字段的总和
TRUNCATE(x,y)	返回数值 x 保留到小数点后 y 位的值（与 ROUND 最大的区别是不会进行四舍五入）

ROUND(column|expression, n) 函数

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

SELECT ROUND(45.873,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() OR CURRENT_DATE()	返回当前日期	SELECT CURDATE(); -> 2018-09-19
CURTIME() OR CURRENT_TIME()	返回当前时间	SELECT CURTIME(); -> 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-1111: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 SECOND(‘1:2:3’) -> 3
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-0101:01:01’) -> 366
WEEK(d) OR WEEKOFYEAR(d)	计算日期 d 是本年的第几个星期，范围是 0 到 53	SELECT WEEK(‘2011-11-11 11:11:11’) -> 45
WEEKDAY(d)	日期 d 是星期几，0 表示星期一，1 表示星期二	SELECT WEEKDAY(“2017-06-15”); -> 3
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,'Amy','amy@xyyyuhtl.cn','2049-5-1 8:30:30','IT_PROG');

转换函数

隐式数据类型转换
隐式数据类型转换是指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,'Xyy','Xyy@xyyyuhtl.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。	SELECTIFNULL(null,‘HelloWord’) ->HelloWord
NULLIF(expr1, expr2)	比较两个参数是否相同，如果参数expr1 与 expr2 相等 返回 NULL，否则返回 expr1	SELECT NULLIF(25,25); ->NULL
COALESCE(expr1, expr2,…, expr_n)	返回参数中的第一个非空表达式（从左向右）	SELECTCOALESCE(NULL,NULL, NULL,‘bjsxt.com’, NULL,‘google.com’); ->bjsxt.com
CASE expression WHENcondition1 THEN result1WHEN condition2 THENresult2 … WHENconditionN THEN resultNELSE 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 departments_id in (50,80);

**计算雇员的年报酬，你需要用 12 乘以月薪，再加上它的佣金

SELECT LAST_NAME,SALARY,SALARY*12+(SALARY*12*IF(ISNULL(COMMISSION_PCT),0,COMMISSION_PCT)) '年薪'
FROM employees;

SELECT LAST_NAME,SALARY,IFNULL(COMMISSION_PCT,0),SALARY*12+(SALARY*12*IFNULL(COMMISSION_PCT,0)) '年薪'
FROM employees;

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

SELECT LAST_NAME "expr2",FIRST_NAME "expr1",NULLIF(LENGTH(FIRST_NAME),LENGTH(LAST_NAME)) result
FROM employees;

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

SELECT FIRST_NAME,COALESCE(COMMISSION_PCT,SALARY,10) COMM
FROM employees ORDER BY COMMISSION_PCT ASC;

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

SELECT JOB_ID '工作岗位',LAST_NAME '名字',SALARY '工资',
       CASE JOB_ID  WHEN 'IT_PROG'  THEN 1.10*SALARY
                    WHEN 'ST_CLERK' THEN 1.15*SALARY
                    WHEN 'SA_REP'  THEN 1.2*SALARY
       ELSE  SALARY END '最终工资'
FROM employees;

练习

1.显示受雇日期在 1998 年 2 月 20 日 和 2005 年 5 月 1 日 之间的雇员的名字、岗位和受雇日期。按受雇日期顺序排序查询结果。

SELECT HIRE_DATE,LAST_NAME,JOB_ID
FROM employees
WHERE HIRE_DATE BETWEEN '1998-2-20' AND '2005-5-1'
ORDER BY HIRE_DATE;

2.显示每一个在 2002 年受雇的雇员的名字和受雇日期。

SELECT LAST_NAME,HIRE_DATE
FROM employees
WHERE HIRE_DATE LIKE '2002%';

3.对每一个雇员，显示 employee number、last_name、salary 和 salary 增加 15%，并且表示成整数，列标签显示为 New Salary。

SELECT EMPLOYEE_ID,LAST_NAME,SALARY,ROUND(SALARY*1.15,0) 'New Salary'
FROM employees;

4.写一个查询，显示名字的长度，对所有名字开始字母是 J、A 或 M 的雇员。用雇员的 lastname排序结果。

SELECT LAST_NAME "名字",LENGTH(LAST_NAME) "名字的长度"
FROM employees
WHERE LAST_NAME LIKE 'J%'
OR LAST_NAME LIKE 'A%'
OR LAST_NAME LIKE 'M%'
ORDER BY LAST_NAME;

5.创建一个查询显示所有雇员的 last name 和 salary。将薪水格式化为 15 个字符长度，用 $左填充 。

SELECT LAST_NAME,LPAD(SALARY,15,'$')
FROM employees;

6.创建一个查询显示雇员的 last names 和 commission (佣金) 比率。如果雇员没有佣金，显示 “No Commission”，列标签 COMM。

SELECT LAST_NAME,IFNULL(COMMISSION_PCT,'No Commission') COMM
FROM employees;

7.写一个查询，按照下面的数据显示所有雇员的基于 JOB_ID 列值的级别。

SELECT EMPLOYEE_ID,JOB_ID,
CASE JOB_ID WHEN 'AD_PRES' THEN 'A'
            WHEN  'ST_MAN' THEN 'B'
            WHEN   'IT_PROG' THEN 'C'
            WHEN  'SA_REP'  THEN 'D'
            WHEN  'ST_CLERK'  THEN 'E'
ELSE 0 END '级别'
 
FROM employees

多表查询

多表查询简介

笛卡尔乘积

笛卡尔乘积 ：
当一个连接条件无效或被遗漏时，其结果是一个笛卡尔乘积 (Cartesian product)，其中所有行的组合都被显示。第一个表中的所有行连接到第二个表中的所有行。一个笛卡尔乘积会产生大量的行，其结果没有什么用。你应该在 WHERE 子句中始终包含一个有效的连接条件，除非你有特殊的需求，需要从所有表中组合所有的行。

多表查询分类

• sql92标准：内连接(等值连接 、非等值连接 、 自连接)。
• sql99标准：内连接、外连接(左外、右外、全外(MySQL不支持全外连接))、交叉连接。

等值连接

sql99标准：内连接、外连接(左外、右外、全外(MySQL不支持全外连接))、交叉连接。

等值连接

为了确定一个雇员的部门名，需要比较 EMPLOYEES 表中的 DEPARTMENT_ID 列与DEPARTMENTS 表中的 DEPARTMENT_ID 列的值。在 EMPLOYEES 和DEPARTMENTS 表之间的关系是一个相等 (equijoin) 关系，即，两 个 表 中DEPARTMENT_ID 列的值必须相等。

等值连接特点：

1. 多表等值连接的结果为多表的交集部分；
2. n表连接，至少需要n-1个连接条件；
3. 多表不分主次，没有顺序要求；
4. 一般为表起别名，提高阅读性和性能；
5. 可以搭配排序、分组、筛选….等子句使用；

等值连接也被称为简单连接 (simple joins) 或内连接 (inner joins)。

等值连接的使用

SELECT 子句指定要返回的列名：
6. employee.last_name、employee.number 和 department.number，这些是EMPLOYEES 表中的列。
7. department.number、department_name 和 location_ID，这些是 DEPARTMENTS 表中的列。
FROM 子句指定数据库必须访问的两个表：
8. EMPLOYEES 表
9. DEPARTMENTS 表

EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID，因为 DEPARTMENT_ID 列是两个表的同名列，它必须用表名做前缀以避免混淆。

增加搜索条件

添加查询条件
除连接之外，可能还要求用 WHERE 子句在连接中限制一个或多个表中的行。

限制不明确的列

1. 需要在 WHERE 子句中用表的名字限制列的名字以避免含糊不清。没有表前缀，DEPARTMENT_ID 列可能来自 DEPARTMENTS 表，也可能来自 EMPLOYEES 表，这种情况下需要添加表前缀来执行查询。
2. 如果列名在两个表之间不相同，就不需要限定列。但是，使用表前缀可以改善性能，因为MySQL服务器可以根据表前缀找到对应的列。
3. 必须限定不明确的列名也适用于在其它子句中可能引起混淆的那些列，例如 SELECT子句或 ORDER BY 子句。

使用表别名

表别名定义原则

• 表别名不易过长，短一些更好。
• 表别名应该是有意义的。
• 表别名只对当前的 SELECT 语句有效。

多表连接

查询雇员 King 所在的部门名称。

SELECT d.department_name
FROM employees e,departments d
WHERE e.DEPARTMENT_ID=d.DEPARTMENT_ID AND e.LAST_NAME='King';

显示每个雇员的 last name、departmentname 和 city。

SELECT e.LAST_NAME,d.DEPARTMENT_NAME,l.CITY
FROM employees e,departments d,locations l
WHERE e.DEPARTMENT_ID=d.DEPARTMENT_ID AND d.LOCATION_ID=l.LOCATION_ID;

非等值连接

非等值连接
一个非等值连接是一种不同于等值操作的连接条件。 EMPLOYEES 表 和JOB_GRADES A 表之间的关系有一个非等值连接例子。在两个表之间的关系是EMPLOYEES 表中的 SALARY 列必须是 JOB_GRADES 表的 LOWEST_SALARY 和HIGHEST_SALARY 列之间的值。使用不同于等于 (=) 的操作符获得关系。

创建 job_grades 表，包含 lowest_sal ，highest_sal ，grade_level。

CREATE TABLE job_grades(lowest_sal int,highest_sal int,grade_level VARCHAR(10));

插入数据
1000 2999 A
2000 4999 B
5000 7999 C
8000 12000 D

insert into job_grades values(1000,2999,'A');
insert into job_grades values(2000,4999,'B');
insert into job_grades values(5000,7999,'C');
insert into job_grades values(8000,12000,'D');

查询所有雇员的薪水级别

SELECT e.LAST_NAME,j.grade_level
FROM employees e,job_grades j
WHERE e.SALARY BETWEEN j.lowest_sal AND j.highest_sal;

自连接

**自连接 **
连接一个表到它自己。有时需要连接一个表到它自己。为了找到每个雇员的经理的名字，则需要连接EMPLOYEES 表到它自己，或执行一个自连接。

图片中的例子连接 EMPLOYEES 表到它自己。为了在 FROM 子句中模拟两个表，对于相同的表 EMPLOYEES，用两个别名，分别为 worker 和 manager。在该例中，WHERE 子句包含的连接意味着 “一个工人的经理号匹配该经理的雇员号”。

查询每个雇员的经理的名字以及雇员的名字，雇员名字列别名为W，经理列别名为M。

SELECT WORKER.FIRST_NAME W,MANAGER.FIRST_NAME M
FROM employees WORKER,employees MANAGER
WHERE WORKER.MANAGER_ID=MANAGER.EMPLOYEE_ID;

查询Fox的经理是谁？显示他的名字。

SELECT worker.LAST_NAME,manger.LAST_NAME 
FROM employees worker,employees manger
WHERE worker.MANAGER_ID=manger.EMPLOYEE_ID  AND worker.LAST_NAME='Fox';

SQL99标准中的查询

MySQL5.7 支持部分的SQL99 标准。

SQL99中的交叉连接(CROSS JOIN)

使用交叉连接查询 employees 表与 departments 表。

SELECT * FROM employees CROSS JOIN departments;

SQL99中的自然连接(NATURAL JOIN)

自然连接
连接只能发生在两个表中有相同名字和数据类型的列上。如果列有相同的名字，但数据类型不同，NATURAL JOIN 语法会引起错误。
自然连接查询

在图片例子中，LOCATIONS 表被用 LOCATION_ID 列连接到 DEPARTMENT表，这是在两个表中唯一名字相同的列。如果存在其它的同名同类型的列，自然连接会使用等值连接的方式连接他们，连接条件的关系为and。
自然连接也可以被写为等值连接：

SELECT d.DEPARTMENT_ID,d.DEPARTMENT_NAME,d.LOCATION_ID,l.CITY
FROM departments d,locations l
WHERE d.LOCATION_ID=l.LOCATION_ID;

使用自然连接查询所有有部门的雇员的名字以及部门名称。

SELECT e.LAST_NAME,d.DEPARTMENT_NAME
FROM employees e natural join departments d;

SQL99中的内连接(INNER JOIN)

语法：

• SELECT 查询列表;
• FROM 表1 别名;
• INNER JOIN 连接表(INNER关键字可省略);
• ON 连接条件;
  用ON子句指定连接条件
  
  用ON子句指定更多的连接条件
  
  查询雇员名字为 Fox 的雇员 ID ，薪水与部门名称。

SELECT e.EMPLOYEE_ID,e.SALARY,d.DEPARTMENT_NAME
FROM employees e JOIN departments d
ON e.DEPARTMENT_ID=d.DEPARTMENT_ID;

外连接查询(OUTER JOIN)

孤儿数据(Orphan Data)
孤儿数据是指被连接的列的值为空的数据。
左外连接(LEFT OUTER JOIN)


左外连接
左边的表 (EMPLOYEES) 中即使没有与 DEPARTMENTS 表中匹配的行，该查询也会取回 EMPLOYEES 表中所有的行。

查询所有雇员的名字以及他们的部门名称，包含那些没有部门的雇员。

SELECT e.LAST_NAME,d.DEPARTMENT_NAME
FROM employees e LEFT OUTER JOIN
departments d ON e.DEPARTMENT_ID=d.DEPARTMENT_ID;

右外连接(RIGTH OUTER JOIN)

右外连接
右边的表 (DEPARTMENTS ) 中即使没有与 EMPLOYEES 表中匹配的行，该查询也会取回 DEPARTMENTS 表中所有的行。

查询所有雇员的名字以及他们的部门名称，包含那些没有雇员的部门。

SELECT e.LAST_NAME,d.DEPARTMENT_NAME
FROM employees e RIGHT OUTER JOIN departments d
ON e.DEPARTMENT_ID=d.DEPARTMENT_ID;

全外连接(FULL OUTER JOIN)

MySQL 中不支持 FULL OUTER JOIN 连接可以使用 union 实现全完连接。

• UNION: 可以将两个查询结果集合并，返回的行都是唯一的，如同对整个结果集合使用了 DISTINCT。
• UNION ALL: 只是简单的将两个结果合并后就返回。这样，如果返回的两个结果集中有重复的数据， 那么返回的结果集就会包含重复的数据了。
  语法结构：

(SELECT 投影列 FROM 表名 LEFT OUTER JOIN 表名 ON 连接条件)
UNION
(SELECT 投影列 FROM 表名 RIGHT OUTER JOIN 表名 ON 连接条件)

查询所有雇员的名字以及他们的部门名称，包含那些没有雇员的部门以及没有部门的雇 员。

(SELECT  e.LAST_NAME,d.DEPARTMENT_NAME    FROM employees e RIGHT  OUTER JOIN departments d  ON e.DEPARTMENT_ID=d.DEPARTMENT_ID) 
UNION
(SELECT e1.LAST_NAME,d1.DEPARTMENT_NAME  FROM employees e1 LEFT OUTER JOIN departments d1 ON e1.DEPARTMENT_ID=d1.DEPARTMENT_ID)

练习

1.写一个查询显示所有雇员的 last name、department id、and department name。

SELECT e.LAST_NAME,d.DEPARTMENT_NAME,e.DEPARTMENT_ID
FROM employees e,departments d
WHERE e.department_id=d.DEPARTMENT_ID;

2.创建一个在部门 80 中的所有工作岗位的唯一列表，在输出中包括部门的地点。

SELECT DISTINCT e.JOB_ID,d.LOCATION_ID
FROM departments d,employees e
WHERE d.DEPARTMENT_ID=e.DEPARTMENT_ID  AND e.DEPARTMENT_ID=80;

3.写一个查询显示所有有佣金的雇员的 last name、department name、location ID 和城市

SELECT e.LAST_NAME,d.DEPARTMENT_NAME,l.LOCATION_ID,l.CITY
FROM employees e,departments d,locations l
WHERE e.DEPARTMENT_ID=d.DEPARTMENT_ID AND l.LOCATION_ID=d.LOCATION_ID AND e.COMMISSION_PCT IS NOT NULL;

4.显示所有在其 last names 中有一个小写 a 的雇员的 last name 和 department name。

SELECT e.LAST_NAME,d.DEPARTMENT_NAME
FROM employees e,departments d
WHERE e.DEPARTMENT_ID=d.DEPARTMENT_ID AND e.LAST_NAME LIKE '%a%';

5.用sql99的内连接写一个查询显示那些工作在 Toronto 的所有雇员的 last_name、job_ID 和 department_name。

SELECT e.LAST_NAME,e.JOB_ID,d.DEPARTMENT_NAME,l.CITY
FROM employees e INNER JOIN departments d
ON e.DEPARTMENT_ID=d.DEPARTMENT_ID 
INNER JOIN locations l
ON d.LOCATION_ID=l.LOCATION_ID 
WHERE l.CITY='Toronto';

6.显示雇员的 last_name 和 employee_id 连同他们的经理的 last_name 和manager_id。列标签分别为 Employee、Emp#、Manager 和 Mgr#

SELECT worker.LAST_NAME Employee,worker.EMPLOYEE_ID 'Emp#',
manager.LAST_NAME Manager,manager.MANAGER_ID 'Mgr#'
FROM employees worker,employees manager
WHERE worker.MANAGER_ID=manager.EMPLOYEE_ID;

SELECT worker.LAST_NAME Employee,worker.EMPLOYEE_ID 'Emp#',
manager.LAST_NAME Manager,manager.MANAGER_ID 'Mgr#'
FROM employees worker INNER JOIN employees manager
ON worker.MANAGER_ID=manager.EMPLOYEE_ID;

聚合函数

聚合函数介绍

聚合函数也称之为多行函数，组函数或分组函数。聚合函数不像单行函数，聚合函数对行的分组进行操作，对每组给出一个结果。如果在查询中没有指定分组，那么聚合函数则将查询到的结果集视为一组。
聚合函数类型

聚合函数说明：

函数名	描述
AVG(expression)	返回一个表达式的平均值，expression是一个字段
COUNT(expression)	返回查询的记录总数，expression 参数是一个字段或者 *号
MAX(expression)	返回字段expression 中的最大值
MIN(expression)	返回字段expression 中的最小值
SUM(expression)	返回指定字段的总和

聚合函数使用方式

使用聚合函数的原则

• DISTINCT 使得函数只考虑不重复的值；
• 所有聚合函数忽略空值。为了用一个值代替空值，用 IFNULL 或 COALESCE 函数。

AVG 和 SUM 函数

AVG(arg)函数

对分组数据做平均值运算。
arg:参数类型只能是数字类型。

SUM(arg)函数

对分组数据求和。
arg:参数类型只能是数字类型。

计算员工表中工作编号含有REP的工作岗位的平均薪水与薪水总和。

SELECT SUM(SALARY),AVG(SALARY)
FROM employees e
WHERE e.JOB_ID LIKE '%REP%';

MIN 和 MAX 函数

MIN(arg)函数

求分组中最小数据。
arg:参数类型可以是字符、数字、 日期。

MAX(arg)函数

求分组中最大数据。
arg:参数类型可以是字符、数字、日期。

查询员工表中入职时间最短与最长的员工，并显示他们的入职时间。

SELECT MIN(employees.HIRE_DATE),MAX(employees.HIRE_DATE)
FROM employees;

COUNT 函数

返回分组中的总行数。
COUNT 函数有三种格式：

• COUNT(*)：返回表中满足 SELECT 语句的所有列的行数，包括重复行，包括有空值列的行。
• COUNT(expr)：返回在列中的由 expr 指定的非空值的数。
• COUNT(DISTINCT expr)：返回在列中的由 expr 指定的唯一的非空值的数。

使用 DISTINCT 关键字

• COUNT(DISTINCT expr) 返回对于表达式 expr 非空并且值不相同的行数
• 显示 EMPLOYEES 表中不同部门数的值

显示员工表中部门编号是80中有佣金的雇员人数。

SELECT COUNT(COMMISSION_PCT)-- COUNT(Exp)返回一个非空值
FROM employees
WHERE DEPARTMENT_ID=80;

显示员工表中的部门数。（多个人可以一个部门，可重复，要去重）

SELECT COUNT(DISTINCT DEPARTMENT_ID)
FROM employees;

组函数和 Null 值

在组函数中使用 IFNULL 函数

SELECT AVG(IFNULL(COMMISSION_PCT,0))
FROM employees

数据分组(GROUP BY)

创建数据组

**创建数据组 **
在没有进行数据分组之前，所有聚合函数是将结果集作为一个大的信息组进行处理。但是，有时，则需要将表的信息划分为较小的组，可以用 GROUP BY 子句实现。
GROUP BY 子句语法

原则

• 使用 WHERE 子句，可以在划分行成组以前过滤行。
• 如果有WHERE子句，那么GROUP BY 子句必须在WHERE的子句后面。
• 在 GROUP BY 子句中必须包含列。

使用 GROUP BY 子句

GROUP BY 子句
下面是包含一个 GROUP BY 子句 SELECT 语句的求值过程：
1.SELECT 子句指定要返回的列：

• 在 EMPLOYEES 表中的部门号
• GROUP BY 子句中指定分组的所有薪水的平均值
• FROM 子句指定数据库必须访问的表：EMPLOYEES 表。
  2.WHERE 子句指定被返回的行。因为无 WHERE 子句默认情况下所有行被返回。
  3.GROUP BY 子句指定行怎样被分组。行用部门号分组，所以 AVG 函数被应用于薪水列，以计算每个部门的平均薪水。
  计算每个部门的员工总数。

SELECT  DEPARTMENT_ID,COUNT(*)
FROM employees
GROUP BY DEPARTMENT_ID;

在多列上使用分组

**在组中分组 **
可以列出多个 GROUP BY 列返回组和子组的摘要结果。可以用 GROUP BY子句中的列的顺序确定结果的默认排序顺序。下面是图片中的 SELECT 语句中包含一个 GROUP BY 子句时的求值过程：
1.SELECT 子句指定被返回的列：

• 部门号在 EMPLOYEES 表中
• Job ID 在 EMPLOYEES 表中
• 在 GROUP BY 子句中指定的组中所有薪水的合计
  2.FROM 子句指定数据库必须访问的表：EMPLOYEES 表
  3.GROUP BY 子句指定你怎样分组行：
• 首先，用部门号分组行。
• 第二，在部门号的分组中再用 job ID 分组行。
  如此 SUM 函数被用于每个部门号分组中的所有 job ID 的 salary 列。

计算每个部门的不同工作岗位的员工总数。

SELECT DEPARTMENT_ID,JOB_ID,COUNT(*)
FROM employees
GROUP BY DEPARTMENT_ID,JOB_ID;

约束分组结果(HAVING)

HAVING 子句
HAVING 子句是对查询出结果集分组后的结果进行过滤。
约束分组结果
用 WHERE 子句约束选择的行，用 HAVING 子句约束组。为了找到每个部门中的最高薪水，而且只显示最高薪水大于 $10,000 的那些部门，可以象下面这样做：

• 用部门号分组，在每个部门中找最大薪水。
• 返回那些有最高薪水大于 $10,000 的雇员的部门

SELECT DEPARTMENT_ID,MAX(SALARY)
FROM employees
GROUP BY DEPARTMENT_ID
HAVING MAX(SALARY)>10000;

** HAVING子句语法**

显示那些合计薪水超过 13,000 的每个工作岗位的合计薪水。排除那些JOB_ID中含有REP的工作岗位，并且用合计月薪排序列表。

SELECT JOB_ID,SUM(SALARY)
FROM employees
WHERE JOB_ID NOT LIKE '%REP%'
GROUP BY JOB_ID
HAVING SUM(SALARY) >13000
ORDER BY SUM(SALARY);

练习

1.显示所有雇员的最高、最低、合计和平均薪水，列标签分别为：Max、Min、Sum 和 Avg。四舍五入结果为最近的整数。

SELECT EMPLOYEE_ID,ROUND(MAX(SALARY)) Max,ROUND(MIN(SALARY))Min,ROUND(SUM(SALARY)) Sum,ROUND(AVG(SALARY))Avg
FROM employees;

2.写一个查询显示每一工作岗位的人数。

SELECT JOB_ID,COUNT(*)
FROM employees
GROUP BY JOB_ID;

3.确定经理人数，不需要列出他们，列标签是 Number of Managers。提示：用MANAGER_ID列决定经理号。

SELECT COUNT(DISTINCT MANAGER_ID)
FROM employees;

4.写一个查询显示最高和最低薪水之间的差。

SELECT MAX(SALARY)-MIN(SALARY)
FROM employees;

5.显示经理号和经理付给雇员的最低薪水。排除那些经理未知的人。排除最低薪水小于等于 $6,000 的组。按薪水降序排序输出。

SELECT MANAGER_ID,MIN(SALARY)
FROM employees 
WHERE MANAGER_ID IS NOT NULL 
GROUP BY MANAGER_ID
HAVING MIN(SALARY)>6000
ORDER BY MIN(SALARY) DESC;

6.写一个查询显示每个部门的名字、地点、人数和部门中所有雇员的平均薪水。四舍五入薪水到两位小数。

SELECT d.DEPARTMENT_NAME,
d.LOCATION_ID,COUNT(e.EMPLOYEE_ID),ROUND(AVG(e.SALARY),2)
FROM employees e INNER JOIN departments d
ON e.DEPARTMENT_ID=d.DEPARTMENT_ID
GROUP BY d.DEPARTMENT_NAME,d.DEPARTMENT_ID;

子查询

子查询介绍

**用子查询解决问题 **
假如要写一个查询来找出挣钱比 Abel 的薪水还多的人。为了解决这个问题，需要两个查询：一个找出 Abel 的收入，第二个查询找出收入高于 Abel 的人。可以用组合两个查询的方法解决这个问题。内查询或子查询返回一个值给外查询或主查询。使用一个子查询相当于执行两个连续查询并且用第一个查询的结果作为第二个查询的搜索值。

子查询语法

**子查询 **
子查询是一个 SELECT 语句，它是嵌在另一个 SELECT 语句中的子句。使用子查询可以用简单的语句构建功能强大的语句。
可以将子查询放在许多的 SQL 子句中，包括：

• WHERE 子句
• HAVING 子句
• FROM 子句
  使用子查询
  
  使用子查询的原则
• 子查询放在圆括号中。
• 将子查询放在比较条件的右边。
• 在单行子查询中用单行运算符，在多行子查询中用多行运算符。

子查询类型

查询与Fox同一部门的同事，并显示他们的名字与部门ID。

SELECT LAST_NAME,DEPARTMENT_ID
FROM employees 
WHERE DEPARTMENT_ID=(SELECT DEPARTMENT_ID FROM employees WHERE LAST_NAME='Fox')

单行子查询

单行子查询
单行子查询是从内查询返回一行的查询。在该子查询类型中用一个单行操作符。
查询 Fox的同事，但是不包含他自己。

SELECT e.LAST_NAME,e.DEPARTMENT_ID
FROM employees e
WHERE e.DEPARTMENT_ID=(
SELECT e1.DEPARTMENT_ID FROM employees e1
WHERE e1.LAST_NAME = 'Fox'
)
AND e.LAST_NAME <> 'Fox';

多行子查询

多行子查询
子查询返回多行被称为多行子查询。对多行子查询要使用多行运算符而不是单行运算符。
使用ANY运算符

ANY 运算符
ANY 运算符比较一个值与一个子查询返回的任意一个值。

• < ANY 意思是小于最大值。
• 大于> ANY 意思是大于最小值。
• =ANY等同于 IN。
  ** 使用ALL运算符**
  
  ALL 运算符比较一个值与子查询返回的全部值。
  < ALL 意思是小于最小值。> ALL 意思是大于最大值
  NOT 运算符可以与 IN运算符一起使用。
  子查询中的空值
  
  内查询返回的值含有空值，并因此整个查询无返回行，原因是用大于、小于或不等于比较Null值，都返回null。所以，只要空值可能是子查询结果集的一部分，就不能用 NOT IN 运算符。NOT IN 运算符相当于 <> ALL。
  注意，空值作为一个子查询结果集的一部分，如果使用 IN 操作符的话，不是一个问题。IN 操作符相当于 =ANY。

SELECT emp.last_name FROM employees emp WHERE emp.employee_id IN (SELECT mgr.manager_id FROM employees mgr);

查找各部门收入为部门最低的那些雇员。显示他们的名字，薪水以及部门 ID

SELECT e.LAST_NAME,e.SALARY,e.DEPARTMENT_ID
FROM employees e
WHERE e.SALARY IN(SELECT
 MIN(e1.SALARY) FROM employees e1
GROUP BY e1.DEPARTMENT_ID)
GROUP BY e.DEPARTMENT_ID

练习

1.写一个查询显示与 Zlotkey 在同一部门的雇员的 last name 和 hire date，结果中不包括 Zlotkey。

SELECT LAST_NAME,HIRE_DATE
FROM employees
WHERE DEPARTMENT_ID=
(SELECT DEPARTMENT_ID
FROM employees
WHERE LAST_NAME='Zlotkey'
)AND LAST_NAME <>'Zlotkey'

2.创建一个查询显示所有其薪水高于平均薪水的雇员的雇员号和名字。按薪水的升序排序

SELECT e.EMPLOYEE_ID,e.LAST_NAME
FROM employees e
WHERE e.SALARY >
(SELECT  AVG(e.SALARY)
FROM employees e
)ORDER BY SALARY;

3.写一个查询显示所有工作在有任一雇员的名字中包含一个 u 的部门的雇员的雇员号和名字。

SELECT emp.EMPLOYEE_ID,emp.LAST_NAME
FROM employees emp
WHERE emp.DEPARTMENT_ID IN
(SELECT e1.DEPARTMENT_ID
FROM employees e1
WHERE LAST_NAME LIKE '%u%')

4.显示所有部门地点号 (department location ID ) 是 1700 的雇员的 last name、department ID 和 job ID。

SELECT ee.LAST_NAME,ee.JOB_ID,ee.DEPARTMENT_ID
FROM employees ee
WHERE  ee.DEPARTMENT_ID IN (SELECT DEPARTMENT_ID
FROM departments
WHERE LOCATION_ID=1700)

5.显示每个向 King 报告的雇员的名字和薪水。

SELECT w.LAST_NAME,w.SALARY
FROM employees w
WHERE w.MANAGER_ID IN 
(SELECT m.MANAGER_ID 
FROM employees m
WHERE m.LAST_NAME='King')

6.显示在 Executive 部门的每个雇员的 department ID、last name 和 job ID。

SELECT employees.DEPARTMENT_ID,LAST_NAME,JOB_ID
FROM employees
WHERE DEPARTMENT_ID
IN (SELECT
DEPARTMENT_ID FROM departments
WHERE departments.DEPARTMENT_NAME='Executive'
)

MySQL中的索引

索引介绍

索引是对数据库表中的一列或多列值进行排序的一种结构，使用索引可以快速访问数据库表中的特定信息。索引是一种特殊的文件，它们包含着对数据表里所有记录的位置信息。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。MySQL 索引的建立对于MySQL 的高效运行是很重要的，索引可以大大提高 MySQL 的检索速度。
索引的作用：
索引相当于图书上的目录，可以根据目录上的页码快速找到所需的内容，提高性能（查询速度）。
索引优点：

• 通过创建唯一性索引，可以保证数据库表中的每一行数据的唯一性；
• 可以加快数据的检索速度；
• 可以加速表与表之间的连接；
• 在使用分组和排序进行检索的时候，可以减少查询中分组和排序的时间；

索引缺点

• 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加；
• 索引需要占用物理空间，数据量越大，占用空间越大；
• 会降低表的增删改的效率，因为每次增删改索引都需要进行动态维护；

什么时候需要创建索引

• 频繁作为查询条件的字段应该创建索引；
• 查询中排序的字段创建索引将大大提高排序的速度（索引就是排序加快速查找）；
• 查询中统计或者分组的字段；

什么时候不需要创建索引

• 频繁更新的字段不适合创建索引，因为每次更新不单单是更新记录，还会更新索引，保存索引文件；
• where条件里用不到的字段，不创建索引；
• 表记录太少，不需要创建索引；
• 经常增删改的表；
• 数据重复且分布平均的字段，因此为经常查询的和经常排序的字段建立索引。注意某些数据包含大量重复数据，因此他建立索引就没有太大的效果，例如性别字段，只有男女，不适合建立索引；

MySQL中的索引类型

• 普通索引：最基本的索引，它没有任何限制。
• 唯一索引：索引列的值必须唯一，但允许有空值，如果是组合索引，则列值的组合必须唯一。
• 主键索引：特殊的索引，唯一的标识一条记录，不能为空，一般用primary key来约束。
• 联合索引：在多个字段上建立索引，能够加速查询到速度。

普通索引

是最基本的索引，它没有任何限制。在创建索引时，可以指定索引长度。length 为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度，如果是 BLOB 和 TEXT 类型，必须指定 length。
是最基本的索引，它没有任何限制。在创建索引时，可以指定索引长度。length 为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度，如果是 BLOB 和 TEXT 类型，必须指定 length。
查询索引

SHOW INDEX FROM table_name;

直接创建索引

CREATE INDEX index_name ON table(column(length));

为 emp3 表中的 name 创建一个索引，索引名为 emp3_name_index；

CREATE INDEX emp3_name_index ON emp3(name);

修改表添加索引

ALTER TABLE table_name ADD INDEX index_name (column(length));

修改 emp3 表，为 addrees 列添加索引，索引名为 emp3_address_index；

ALTER TABLE emp3 ADD INDEX emp3_address_index(address); 

创建表时指定索引列

CREATE TABLE 'table'(
COLUMN TYPE,
...
INDEX index_name(column(length))
);

创建 emp4 表，包含 emp_id,name,address 列， 同时为 name 列创建索引 ，索引名为 emp4_name_index。

CREATE TABLE emp4(
`emp_id` int PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(10),
address VARCHAR(50),
INDEX emp4_name_index(name)
);

删除索引

DROP INDEX indexname ON tablename;

删除 emp3 表中索引名为emp3_address_index 的索引。

DROP INDEX emp3_address_index on emp3;

唯一索引

唯一索引与普通索引类似，不同的就是： 索引列的值必须唯一，但允许有空值。

创建唯一索引

CREATE UNIQUE INDEX indexName ON table(column(length))

为 emp 表中的 name 创建一个唯一索引，索引名为 emp_name_index。

CREATE UNIQUE INDEX emp_name_index ON emp(name);

修改表添加唯一索引

ALTER TABLE name ADD UNIQUE indexName(column(length)); 

修改 emp 表，为 salary 列添加唯一索引，索引名为 emp_salary_index。

ALTER TABLE emp ADD UNIQUE emp_salary_index(salary);

创建表时指定唯一索引

CREATE TABLE `table`(
COLUMN TYPE,
...
UNIQUE index_name(column(length))
);

创建 emp5 表，包含 emp_id,name,address 列，同时为 name 列创建唯一索引。索引名为 emp5_name_index。

CREATE TABLE emp5
( `emp_id` int PRIMARY key AUTO_INCREMENT,
 name VARCHAR(10),
address VARCHAR(50),
UNIQUE emp5_name_index(name)
)

主键索引

主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引。
修改表添加主键索引

ALTER TABLE 表名 ADD PRIMARY KEY(列名);

修改 emp 表为 employee_id 添加主键索引。

ALTER TABLE emp add PRIMARY KEY(employee_id);

创建表时指定主键索引

CREATE TABLE `table`(
COLUMN TYPE,
...
);

创建 emp6 表，包含 emp_id,name,address 列，同时为 emp_id 列创建主键索引。

CREATE TABLE emp6(
`emp_id` int PRIMARY KEY auto_increment,
name VARCHAR(10),
address VARCHAR(50)
)

组合索引

组合索引是指使用多个字段创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用(最左前缀原则)。
最左前缀原则就是最左优先。
使用表中的 name ，address ，salary 创建组合索引，那么想要组合索引生效， 我们只能使用如下组合：
name/address/salary
name/address
name/
如果使用 addrees/salary 或者是 salary 则索引不会生效
添加组合索引

ALTER TABLE table_name ADD INDEX index_name(column(length),column(length));

修改 emp6 表，为 name ，address 列创建组合索引。

ALTER TABLE emp6 ADD INDEX  emp6_index_n_a(name,address);

创建表时创建组合索引

CREATE TABLE `table`(
COLUMN TYPE,
INDEX index_name(column(length),column(length))
)

创建 emp7 表，包含 emp_id,name,address 列，同时为 name,address 列创建组合索引。

CREATE TABLE emp7(
`emp_id` int PRIMARY key auto_increment,
name VARCHAR(10),
address VARCHAR(50),
INDEX index_name_address(name,address)
)

MySQL事务

事务简介

事务是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。
事物定义（Transaction）

• 最小的不可再分的工作单元；通常一个事务对应一个完整的业务(例如银行账户转账业务，该业务就是一个最小的工作单元)
• 一个完整的业务需要批量的DML(insert、update、delete)语句共同联合完成
• 事务只和DML语句有关，或者说DML语句才有事务。这个和业务逻辑有关，业务逻辑不同，DML语句的个数不同

事务四大特征(ACID)

• 原子性(ATOMICITY)
  事务中的操作要么都不做，要么就全做。
• 一致性(CONSISTENCY)
  一个事务应该保护所有定义在数据上的不变的属性(例如完整性约束)。在完成了一个成功的事务时，数据应处于一致的状态。
• 隔离性(ISOLATION)
  一个事务的执行不能被其他事务干扰。
• 持久性(DURABILITY)
  一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。

事务类型

• 显式事务
  手动的提交或回滚。
• 隐式事务
  数据库自动提交不需要我们做任何处理，同时也不具备回滚性。DDL、DCL 语言都是隐式事务操作

使用事务

TCL语句	描述
start transaction	事务开启
commit	事物提交
rollback	事物回滚

创建account账户表，包含id、卡号、用户名、余额

CREATE TABLE account(
id int PRIMARY KEY auto_increment,
`card_id` VARCHAR(18) not NULL,
`user_name` VARCHAR(10) not NULL,
money DOUBLE
)

向account表中插入两条数据。

INSERT INTO account(`card_id`,`user_name`,money) VALUES('123456','熊大',800);
INSERT INTO account(`card_id`,`user_name`,money) VALUES('987654','熊二',600);

在一个事务中完成转账业务。

START TRANSACTION;
UPDATE account set money=money-200 where `card_id`='123456';
UPDATE account set money=money+200 WHERE `card_id`='987654';
SELECT * FROM account;
-- 提交转账事务
COMMIT;
-- 当关闭数据库重新打开后，张三和李四的账户余额并没发生任何变化。
-- 这是因为当我们使用“START TRANSACTION”开启一个事务后，该事务的提交方式不再是自动的，
-- 而是需要手动提交，而在这里，我们并没有使用事务提交语句COMMIT，  
-- 所以对account表中数据的修改并没有永久的保存到数据库中，也就是说我们的转账事务并没有执行成功  
-- 事务的回滚让数据库恢复到了执行事务操作前的状态。
-- 需要注意的是事务的回滚必须在事务提交之前，因为事务一旦提交就不能再进行回滚操作
rollback;

事务的并发问题

脏读（读取未提交数据）

指一个事务读取了另外一个事务未提交的数据。
A事务读取B事务尚未提交的数据，此时如果B事务发生错误并执行回滚操作，那么A事务读取到的数据就是脏数据。

不可重复读（前后多次读取，数据内容不一致）

在一个事务内读取表中的某一行数据，多次读取结果不同。
事务A在执行读取操作，由整个事务A比较大，前后读取同一条数据需要经历很长的时间 。而在事务A第一次读取数据，比如此时读取了小明的年龄为20岁，事务B执行更改操作，将小明的年龄更改为30岁，此时事务A第二次读取到小明的年龄时，发现其年龄是30岁，和之前的数据不一样了，也就是数据不重复了，系统不可以读取到重复的数据，成为不可重复读。

幻读（前后多次读取，数据总量不一致）

一个事务内读取到了别的事务插入的数据，导致前后读取数量总量不一致。
事务A在执行读取操作，需要两次统计数据的总量，前一次查询数据总量后，此时事务B执行了新增数据的操作并提交后，这个时候事务A读取的数据总量和之前统计的不一样，就像产生了幻觉一样，平白无故的多了几条数据，成为幻读。

事务的隔离级别

**事务的隔离级别用于决定如何控制并发用户读写数据的操作。数据库是允许多用户并发访问的，如果多个用户同时开启事务并对同一数据进行读写操作的话，有可能会出现脏读、不可重复读和幻读问题，所以MySQL中提供了四种隔离级别来解决上述问题。 **
事务的隔离级别从低到高依次为：

• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ
• SERIALIZABLE
  隔离级别越低，越能支持高并发的数据库操作。
  
  ** 查看MySQL默认事务隔离级别**

SELECT @@transaction_isolation;`在这里插入代码片`

设置事务隔离级别
对当前session有效。

set session transaction isolation level read uncommitted;
set session transaction isolation level read committed;
set session transaction isolation level read repeated read;
set session transaction isolation level serializable;

MySQL默认事务隔离级别是repeatable read

MySQL的用户管理

MySQL 是一个多用户的数据库系统，按权限，用户可以分为两种： root 用户，超级管理员，和由 root 用户创建的普通用户。

用户管理

创建用户

CREATE USER username IDENTIFIED BY 'password';

查看用户

SELECT USER,HOST FROM mysql.user;

创建一个 u_xcm 的用户，并查看创建是否成功。

CREATE USER u_xe IDENTIFIED BY 'xcm';
SELECT USER,HOST from mysql.user;

权限管理

新用户创建完后是无法登陆的，需要分配权限。
GRANT 权限 ON 数据库.表 TO 用户名@登录主机 IDENTIFIED BY “密码”
登陆主机：

字段	含义
%	匹配所有主机
localhost	不会解析成IP地址，直接通过UNIXsocket连接
127.0.0.1	会通过TCP/IP协议连接，并且只能在本机访问
:: 1	:: 1兼容支持ipv6，表示同ipv4的127.0.0.1

权限列表

权 限	作用范围	作 用
all [privileges]	服务器	所有权限
select	表、列	选择行
insert	表、列	插入行
update	表、列	更新行
delete	表	删除行
create	数据库、表、索引	创建
drop	数据库、表、视图	删除
reload	服务器	允许使用flush语句
shutdown	服务器	关闭服务
process	服务器	查看线程信息
file	服务器	文件操作
grant option	数据库、表、存储过程	授权
references	数据库、表	外键约束的父表
index	表	创建/删除索引
alter	表	修改表结构
show databases	服务器	查看数据库名称
super	服务器	超级权限
create temporary tables	表	创建临时表
lock tables	数据库	锁表
execute	存储过程	执行
replication client	服务器	允许查看主/从/二进制日志状态
replication slave	服务器	主从复制
create view	视图	创建视图
show view	视图	查看视图
create routine	存储过程	创建存储过程
alter routine	存储过程	修改/删除存储过程
create user	服务器	创建用户
event	数据库	创建/更改/删除/查看事件
trigger	表	触发器
create tablespace	服务器	创建/更改/删除表空间/日志文件
proxy	服务器	代理成为其它用户
usage	服务器	没有权限

为 u_sxt 用户分配只能查询 xcm 库中的 emp 表，并且只能在本机登陆的权限。

GRANT SELECT ON xcm.emp to 'u_xcm' @'localhost' IDENTIFIED BY 'xcm';

刷新权限
每当调整权限后，通常需要执行以下语句刷新权限。

FLUSH PRIVILEGES;

删除用户

DROP USER username@localhost;

MySQL分页查询

MySQL 分页查询原则：

• 在 MySQL 数据库中使用 LIMIT 子句进行分页查询。
• MySQL 分页中开始位置为 0。
• 分页子句在查询语句的最后侧。

LIMIT子句

SELECT 投影列 FROM 表名 WHERE 条件 ORDER BY LIMIT 开始位置,查询数量;

查询雇员表中所有数据按 id 排序，实现分页查询，每次返回两条结果。

SELECT * FROM employees ORDER BY EMPLOYEE_ID LIMIT 0,2;

LIMIT OFFSET子句

SELECT 投影列 FROM 表名 WHERE 条件 ORDER BY LIMIT 查询数量 OFFSER 开始位置;

查询雇员表中所有数据按 id 排序，使用 LIMIT OFFSET 实现分页查询，每次返回两条结果。

-- 从employees表中选择所有列的数据
-- 根据EMPLOYEE_ID进行排序
-- 跳过前4条记录（OFFSET 4），即不返回ID最小的4条记录
-- 然后从第5条记录开始，返回接下来的2条记录（LIMIT 2）
SELECT * FROM employees 
ORDER BY EMPLOYEE_ID 
LIMIT 2 OFFSET 4;

这里的ORDER BY EMPLOYEE_ID确保了结果集是按照EMPLOYEE_ID的值进行排序的，这是执行LIMIT和OFFSET操作的前提，因为LIMIT和OFFSET都是基于排序后的结果集来工作的。

LIMIT 2指定了查询将返回的记录数上限为2。
OFFSET 4指定了在返回记录之前要跳过的记录数。在这个例子中，就是跳过前4条记录。
所以，如果employees表中有足够的记录，并且这些记录的EMPLOYEE_ID是递增的，那么这条SQL语句将返回EMPLOYEE_ID排在第5位和第6位的记录。如果没有足够的记录来满足OFFSET和LIMIT的条件（比如表中总共只有3条记录），那么查询将不会返回任何记录。