一、数据库

概述

数据库（DataBase，DB）：指长期保存在计算机的存储设备上，按照一定规则组织起来，可以被各种用户或应用共享的数据集合。

数据库管理系统（DataBase Management System，DBMS）：指一种操作和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制，以保证数据库的安全性和完整性。

用户通过数据库管理系统访问数据库中的数据。

数据库软件应该为数据库管理系统，数据库是通过数据库管理系统创建和操作的。

数据库：存储、维护和管理数据的集合。

三大范式

什么是三大范式：

第一范式：无重复的列。当关系模式R的所有属性都不能在分解为更基本的数据单位时，称R是满足第一范式的，简记为1NF。满足第一范式是关系模式规范化的最低要求，否则，将有很多基本操作在这样的关系模式中实现不了。

第二范式：属性完全依赖于主键 [ 消除部分子函数依赖 ]。如果关系模式R满足第一范式，并且R得所有非主属性都完全依赖于R的每一个候选关键属性，称R满足第二范式，简记为2NF。第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或行必须可以被唯一地区分。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。这个唯一属性列被称为主关键字或主键、主码。

第三范式：属性不依赖于其它非主属性 [ 消除传递依赖 ]。设R是一个满足第一范式条件的关系模式，X 是R的任意属性集，如果X非传递依赖于R的任意一个候选关键字，称R满足第三范式，简记为3NF. 满足第三范式（3NF）必须先满足第二范式（2NF）。第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。

注：关系实质上是一张二维表，其中每一行是一个元组，每一列是一个属性

第二范式（2NF）和第三范式（3NF）的概念很容易混淆，区分它们的关键点在于，2NF：非主键列是否完全依赖于主键，还是依赖于主键的一部分；3NF：非主键列是直接依赖于主键，还是直接依赖于非主键列。

二、SQL语言

1. 概述

SQL：Structure Query Language（结构化查询语言），SQL被美国国家标准局（ANSI）确定为关系型数据库语言的美国标准，后来被国际化标准组织（ISO）采纳为关系数据库语言的国际标准。

各数据库厂商都支持ISO的SQL标准，普通话

各数据库厂商在标准的基础上做了自己的扩展，方言

SQL 是一种标准化的语言，它允许你在数据库上执行操作，如创建项目，查询内容，更新内容，并删除条目等操作。

Create, Read, Update, and Delete 通常称为CRUD操作。

2.SQL语句分类

– DDL（Data Defifinition Language）：数据定义语言，用来定义数据库对象：库、表、列等。

– DML（Data Manipulation Language）：数据操作语言，用来定义数据库记录（数据）增删改。

– DCL（Data Control Language）：数据控制语言，用来定义访问权限和安全级别。

– DQL（Data Query Language）：数据查询语言，用来查询记录（数据）查询。

注意：sql语句以;结尾

mysql中的关键字不区分大小写

3.DDL操作数据库

1 创建

CREATE DATABASE语句用于创建新的数据库：

//create database 数据库名 
CREATE DATABASE mydb1; 

//create database 数据库名 character set 编码方式 
CREATE DATABASE mydb2 character SET GBK; 

//create database 数据库名 set 编码方式 collate 排序规则 
CREATE DATABASE mydb3 character SET GBK COLLATE gbk_chinese_ci;

2 查看数据库

查看当前数据库服务器中的所有数据库

show databases;

查看前面创建的mydb2数据库的定义信息

//show create database 数据库名; 
Show CREATE DATABASE mydb2;

3 修改数据库

查看服务器中的数据库，并把mydb2的字符集修改为utf8;

alter database 数据库名 character set 编码方式
ALTER DATABASE mydb2 character SET utf8;

4 删除数据库

 drop database 数据库名

5 其他语句

查看当前使用的数据库

Select database();

切换数据库:

use  数据库名

4.DDL操作表

创建新表

CREATE TABLE 表名( 
列名1 数据类型 [约束], 
列名2 数据类型 [约束], 
列名n 数据类型 [约束] 
);

说明：表名，列名是自定义，多列之间使用逗号间隔，最后一列的逗号不能写

[约束] 表示可有可无

示例：

CREATE TABLE Employees( 
id INT , 
age INT , 
first VARCHAR(255), 
last VARCHAR(255) 
);

常用数据类型：

int：整型

double：浮点型，例如double(5,2)表示最多5位，其中必须有2位小数，即最大值为

999.99；默认支持四舍五入

char：固定长度字符串类型； char(10) ‘aaa ‘ 占10位

varchar：可变长度字符串类型； varchar(10) ‘aaa’ 占3位

text：字符串类型，比如小说信息；

blob：字节类型，保存文件信息(视频，音频，图片)；

date：日期类型，格式为：yyyy-MM-dd；

time：时间类型，格式为：hh:mm:ss

timestamp：时间戳类型 yyyy-MM-dd hh:mm:ss 会自动赋值

datetime:日期时间类型 yyyy-MM-dd hh:mm:ss

删除表

 drop table 表名;

展示当前数据库中的所有表

 show tables;

查看表的字段信息

 desc 表名；

增加列

 alter table 表名 add 新列名 新的数据类型

修改列

 alter table 表名 change 旧列名 新列名 新的数据类型

删除列，一次只能删一列

 alter table 表名 drop 列名

修改表名

alter table 旧表名 rename 新表名;

查看表格的创建细节

show create table 表名;

修改表的字符集为gbk

 alter table 表名 character set 编码方式

5.DML操作

DML是对表中的数据进行增、删、改的操作。

主要包括：INSERT 、UPDATE、 DELETE

小知识：

在mysql中，字符串类型和日期类型都要用单引号括起来。

空值：null

插入操作：INSERT

insert into 表名(列名) values(数据值);

— 注意:1多列和多个列值之间使用逗号隔开 2.列名要和列值一一对应

— 非数值的列值两侧需要加单引号

— 添加数据的时候可以将列名省略->当给所有列添加数据的时候

— 此时列值的顺序按照数据表中列的顺序执行

— 同时添加多行

insert into 表名(列名) values(第一行数据),(第二行数据),(),();

注意：列名与列值的类型、个数、顺序要一一对应。

参数值不要超出列定义的长度。

如果插入空值，请使用null

插入的日期和字符一样，都使用引号括起来。

sql中的运算符:

（1）算术运算符：+，-，*，/(除法)，求余(%)

示例： 5/2 5%2

（2）赋值运算符：=

注：赋值方向：从右往左赋值

示例： name=’张三’

(3) 逻辑运算符：

and(并且)，or(或者)，not（取非）

作用：用于连接多个条件时使用

(4) 关系运算符：

>，<，>=，<=，!=(不等于)，=(等于)，<>(不等于)

补充:查询所有数据：select * from 表名

修改（更新）操作：UPDATE

语法：UPDATE 表名 SET 列名1=列值1,列名2=列值2 … WHERE 列名=值

删除操作：DELETE

DELETE from 表名 WHERE 列名=值

– DELETE 删除表中的数据，表结构还在。删除后的数据可以找回

– TRUNCATE 删除是把表直接DROP掉，然后再创建一个同样的新表。删除的数据不能找回。执行速度比DELETE快。

小结:

为空的条件：列名 is null or 列名=”

注:两个单引号表示空字符串

日期类型值的区别:

date：yyyy-MM-dd （年月日）

time：hh:mm:ss (时分秒)

datetime:yyyy-MM-dd hh:mm:ss (年月日时分秒)

获取当前系统时间:now()

6.DCL

1、创建用户：

create user 用户名@指定ip identifified by 密码;

create user test123@localhost IDENTIFIED by '123'

指定IP才能登陆

create user 用户名@客户端ip identifified by 密码;

create user test456@10.4.10.18 IDENTIFIED by 'test456'

create user 用户名@‘% ’ identifified by 密码任意IP均可登陆

create user test7@'%' IDENTIFIED by 'test7'

2、用户授权：

grant 权限1,权限2,……..,权限n on 数据库名.* to 用户名@IP;

给指定用户授予指定指定数据库指定权限

grant select,insert,update,delete,create on chaoshi.* to 'test456'@'127.0.0.1';

grant all on . to 用户名@IP 给指定用户授予所有数据库所有权限

grant all on *.* to 'test456'@'127.0.0.1'

3、用户权限查询：

show grants for 用户名@IP;

show grants for 'root'@'%';

4、撤销用户权限：

revoke 权限1,权限2,……..,权限n on 数据库名.* from 用户名@IP;

REVOKE SELECT ON *.* FROM 'root'@'%' ;

5、删除用户：

drop user 用户名@IP;

drop user test123@localhost;

三、DQL数据查询

DQL数据查询语言（重要）

数据库执行DQL语句不会对数据进行改变，而是让数据库发送结果集给客户端。

查询返回的结果集是一张虚拟表。

查询关键字：SELECT

语法： SELECT 列名 FROM 表名【WHERE –> BROUP BY–>HAVING–> ORDER BY】

* 表示所有列

SELECT 要查询的列名称 
FROM 表名称 
WHERE 限定条件 /*行条件*/ 
GROUP BY grouping_columns /*对结果分组*/ 
HAVING condition /*分组后的行条件*/ 
ORDER BY sorting_columns /*对结果分组*/ 
LIMIT offset_start, row_count /*结果限定*/

1.简单查询

查询所有列

SELECT * FROM 表名;

查询指定列

SELECT 列名1,列名2 FROM 表名;

2.条件查询

条件查询就是在查询时给出WHERE子句，在WHERE子句中可以使用如下运算符及关键字：

=、!=、<>、<、<=、>、>=; BETWEEN…AND； IN(set)； IS NULL； AND；OR； NOT；

3.模糊查询

当想查询姓名中包含a字母的学生时就需要使用模糊查询了。

模糊查询需要使用关键字LIKE。

语法: 列名 like ‘表达式’ //表达式必须是字符串

通配符:

_(下划线): 任意一个字符

%：任意0~n个字符,’张%’

4.字段控制查询

(1)去除重复记录

去除重复记录（两行或两行以上记录中系列的上的数据都相同），想去除重复记录，需要使用DISTINCT

SELECT DISTINCT sal FROM emp;

(2)查看雇员的月薪与佣金之和

因为sal和comm两列的类型都是数值类型，所以可以做加运算。如果sal或comm中有一个字段不是数值类型，那么会出错。

SELECT *,sal+comm FROM emp;

comm列有很多记录的值为NULL，因为任何东西与NULL相加结果还是NULL，所以结算结果可能会出现NULL。下面使用了把NULL转换成数值0的函数IFNULL：

SELECT *,sal+IFNULL(comm,0) FROM emp;

(3)给列名添加别名

在上面查询中出现列名为sal+IFNULL(comm,0)，这很不美观，现在我们给这一列给出一个别名，为total

SELECT *, sal+IFNULL(comm,0) AS total FROM emp;

给列起别名时，是可以省略AS关键字的：

5.排序

语法： order by 列名 asc/desc

//asc 升序 desc 降序默认不写的话是升序

多列排序：当前面的列的值相同的时候，才会按照后面的列值进行排序

order by 列名1 asc/desc,列名2 asc/desc

6.聚合函数

聚合函数是用来做纵向运算的函数：

COUNT(列名)：统计指定列不为NULL的记录行数；

MAX(列名)：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；

MIN(列名)：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；

SUM(列名)：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；

AVG(列名)：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

7.分组查询

当需要分组查询时需要使用GROUP BY子句

注意:如果查询语句中有分组操作，则select后面能添加的只能是聚合函数和被分组的列名

SELECT deptno, SUM(sal) FROM emp GROUP BY deptno;

HAVING子句

注：having与where的区别:

1.having是在分组后对数据进行过滤，where是在分组前对数据进行过滤

2.having后面可以使用分组函数(统计函数)，where后面不可以使用分组函数。

WHERE是对分组前记录的条件，如果某行记录没有满足WHERE子句的条件，那么这行记录不会参加分组；而HAVING是对分组后数据的约束。

补充: 多列分组

— 统计出stu表中每个班级的男女生各多少人

 select gradename,gender ,count(*) from stu group by gradename,gender

8.LIMIT

LIMIT用来限定查询结果的起始行，以及总行数。

limit 开始下标,显示条数；//开始下标从0开始

limit 显示条数；//表示默认从0开始获取数据

分页查询

pageIndex 页码值 pageSize 每页显示条数

limit (pageindex-1)*pagesize,pagesize;

查询语句书写顺序：select – from- where- groupby- having- order by-limit

查询语句执行顺序：from – where -group by -having – select – order by-limit

四、数据完整性

1.数据库的完整性

用来保证存放到数据库中的数据是有效的,即数据的有效性和准确性

确保数据的完整性 = 在创建表时给表中添加约束

完整性的分类：

– 实体完整性(行完整性):

– 域完整性(列完整性):

– 引用完整性(关联表完整性):

主键约束：primary key

唯一约束：unique [key]

非空约束：not null

默认约束：default

自动增长：auto_increment

外键约束: foreign key

建议这些约束应该在创建表的时候设置

多个约束条件之间使用空格间隔

示例:

create table student( 
    studentno int primary key auto_increment, 
    loginPwd varchar(20) not null default '123456', 
    studentname varchar(50) not null, 
    sex char(2) not null, 
    gradeid int not null, 
    phone varchar(255) not null, 
    address varchar(255) default '学生宿舍', 
    borndate datetime, 
    email varchar(50) 
);

2.实体完整性

实体：即表中的一行(一条记录)代表一个实体（entity）

实体完整性的作用：标识每一行数据不重复。

约束类型：

主键约束（primary key）

唯一约束(unique)

自动增长列(auto_increment)

2.1 主键约束（primary key）

注：每个表中要有一个主键。

特点：数据唯一，且不能为null

示例：

第一种添加方式：

CREATE TABLE student( id int primary key, name varchar(50) );

第二种添加方式：此种方式优势在于，可以创建联合主键

CREATE TABLE student( id int, name varchar(50), primary key(id) ); 
CREATE TABLE student( classid int, stuid int, name varchar(50), primary key(classid,stuid) );

第三种添加方式：

CREATE TABLE student( id int, name varchar(50) ); 
ALTER TABLE student ADD PRIMARY KEY (id);

2.2 唯一约束(unique)

特点：数据不能重复。

CREATE TABLE student( Id int primary key, Name varchar(50) unique );

2.3 自动增长列(auto_increment)

sqlserver数据库 (identity-标识列)

oracle数据库(sequence-序列)

给主键添加自动增长的数值，列只能是整数类型

CREATE TABLE student( Id int primary key auto_increment, Name varchar(50) ); 
INSERT INTO student(name) values(‘tom’);

3.域完整性

域完整性的作用：限制此单元格的数据正确，不对照此列的其它单元格比较

域代表当前单元格

域完整性约束：数据类型非空约束（not null）默认值约束(default)

check约束（mysql不支持）check(sex=’男’ or sex=’女’)

3.1 数据类型

数值类型：

| 类型 | 大小 | 范围（有符号） | 范围（无符号） | 用途 |

| ————- | —————- | ———————– | ———————- | ———- |

| tinyint | 1 字节 | (-128，127) | (0，255) | 小整数值 |

| smallint | 2 字节 | (-32 768，32 767) | (0，65 535) | 大整数值 |

| mediumint | 3 字节 | (-8 388 608，8 388 607) | (0，16 777 215) | 大整数值 |

| INT| 4 字节 | (-2 147 483 648，2 147 483 647) | (0，4 294 967 295) | 大整数值 |

| bigint | 8 字节 | (-9 233 372 036 854 775 808，9 223 372 036 854 775 807) | (0，18 446 744

073 709 551 615) | 极大整数值 |

| flfloat | 4 字节 | (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402

823 466 351 E+38) | 0，(1.175 494 351 E-38，3.402 823 466 E+38) | 单精度浮点数值 |

| double | 8 字节 | (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，

(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 0，(2.225 073 858 507 201 4

E-308，1.797 693 134 862 315 7 E+308) | 双精度浮点数值 |

日期类型:

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个”零”值，当指定不合法的MySQL不能表示的值时使用”零”值。

TIMESTAMP类型有专有的自动更新特性

| 类型 | 大小(字节) | 范围 | 格式 | 用途 |

| ——— | —— | —————————————- | ——————- | ———— |

| DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |

| YEAR | 1 | 1901/2155 | YYYY | 年份值 |

字符串类型:

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET

| 类型 | 大小 | 用途 |

| ———- | —————– | —————— |

| CHAR | 0-255字节 | 定长字符串 |

| VARCHAR | 0-65535 字节 | 变长字符串 |

| TINYBLOB | 0-255字节 | 不超过 255 个字符的二进制字符串 |

| TINYTEXT | 0-255字节 | 短文本字符串 |

| BLOB | 0-65 535字节 | 二进制形式的长文本数据 |

| TEXT | 0-65 535字节 | 长文本数据 |

| MEDIUMBLOB | 0-16 777 215字节 | 二进制形式的中等长度文本数据 |

| MEDIUMTEXT | 0-16 777 215字节 | 中等长度文本数据 |

| LONGBLOB | 0-4 294 967 295字节 | 二进制形式的极大文本数据 |

| LONGTEXT | 0-4 294 967 295字节 | 极大文本数据 |

CHAR和VARCHAR类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

BINARY和VARBINARY类类似于CHAR和VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

BLOB是一个二进制大对象，可以容纳可变数量的数据。有4种BLOB类型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。

有4种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这些对应4种BLOB类型，有相同的最大长度和存储需求。

3.2 非空约束-not null

3.3 默认值约束-default

4.引用完整性

（参照完整性）

外键约束：FOREIGN KEY

constraint 自定义外键名称 foreign key(外键列名) references 主键表名(主键列名)

CREATE TABLE student(id int primary key, name varchar(50) not null, sex varchar(10) default '男' ); 
create table score( 
    id int primary key, 
    score int, 
    sid int , 
    constraint fk_score_sid foreign key(sid) references student(id) 
);

五、多表查询

多个表之间是有关系的，那么关系靠谁来维护?

多表约束：外键列

1.多表的关系

1.1 一对多/多对一关系

客户和订单，分类和商品，部门和员工. 一对多建表原则：在多的一方创建一个字段，字段作为外键指向一的一方的主键.

1.2 多对多关系

学生和课程多对多关系建表原则：需要创建第三张表,中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键.

1.3 一对一关系

在实际的开发中应用不多。因为一对一可以创建成一张表。两种建表原则：唯一外键对应：假设一对一是一个一对多的关系，在多的一方创建一个外键指向一的一方的主键，将外键设置为unique. 主键对应：让一对一的双方的主键进行建立关系

2.多表查询

多表查询有如下几种：

1.合并结果集:UNION 、 UNION ALL

2.连接查询

2.1内连接 [INNER] JOIN ON

2.2外连接 OUTER JOIN ON

-左外连接 LEFT [OUTER] JOIN

– 右外连接 RIGHT [OUTER] JOIN

– 全外连接（MySQL不支持）FULL JOIN

2.3 自然连接 NATURAL JOIN

3.子查询

2.1 合并结果集

作用：合并结果集就是把两个select语句的查询结果合并到一起！

合并结果集有两种方式：

UNION：去除重复记录，例如：SELECT* FROM t1 UNION SELECT * FROM t2；

UNION ALL：不去除重复记录，例如：SELECT * FROM t1 UNION ALL SELECT * FROM t2。

注意：被合并的两个结果：列数、列类型必须相同。

2.2 连接查询

连接查询就是求出多个表的乘积，例如t1连接t2，那么查询出的结果就是t1*t2。

连接查询会产生笛卡尔积，假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。可以扩展到多个集合的情况。

那么多表查询产生这样的结果并不是我们想要的，那么怎么去除重复的，不想要的记录呢，当然是通过条件过滤。通常要查询的多个表之间都存在关联关系，那么就通过关联关系去除笛卡尔积。

使用主外键关系做为条件来去除无用信息

SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno;

一：内连接

上面的连接语句就是内连接，但它不是SQL标准中的查询方式，可以理解为方言！

语法：

select 列名 
from 表1 
inner join 表2 
on 表1.列名=表2.列名 //外键列的关系 
where.....

等价于：

select 列名 
from 表1,表2 
where 表1.列名=表2.列名 and ...(其他条件)

注:<1>表1和表2的顺序可以互换

<2>找两张表的等值关系时,找表示相同含义的列作为等值关系。

<3>点操作符表示“的”,格式:表名.列名

<4>可以使用as,给表名起别名,注意定义别名之后,统一使用别名

内连接的特点：查询结果必须满足条件。

二：外连接

包括左外连接和右外连接，外连接的特点：查询出的结果存在不满足条件的可能。

— 外联查询

— 1.主表数据全部显示，次表数据匹配显示，能匹配到的显示数据，匹配不成功的显示null

— 2.主表和次表不能随意调换位置

使用场景:一般会作为子查询的语句使用

— 左外联:select 列名 from 主表 left join 次表 on 主表.列名=次表.列名

左连接是先查询出左表（即以左表为主），然后查询右表，右表中满足条件的显示出来，不满足条件的显示NULL。

— 右外联:select 列名 from 次表 right join 主表 on 主表.列名=次表.列名

右连接就是先把右表中所有记录都查询出来，然后左表满足条件的显示，不满足显示NULL。

连接查询心得：

连接不限与两张表，连接查询也可以是三张、四张，甚至N张表的连接查询。通常连接查询不可能需要整个笛卡尔积，而只是需要其中一部分，那么这时就需要使用条件来去除不需要的记录。这个条件大多数情况下都是使用主外键关系去除。

两张表的连接查询一定有一个主外键关系，三张表的连接查询就一定有两个主外键关系，所以在大家不是很熟悉连接查询时，首先要学会去除无用笛卡尔积，那么就是用主外键关系作为条件来处理。如果两张表的查询，那么至少有一个主外键条件，三张表连接至少有两个主外键条件。

三.自然连接

自然连接（NATURAL INNER JOIN）：自然连接是一种特殊的等值连接，他要求两个关系表中进行连接的必须是相同的属性列（名字相同），无须添加连接条件，并且在结果中消除重复的属性列。.下面给出几个例子。

语句：

select * from emp e natural join dept d;

2.3 子查询

一个select语句中包含另一个完整的select语句。

子查询就是嵌套查询，即SELECT中包含SELECT，如果一条语句中存在两个，或两个以上SELECT，那么

就是子查询语句了。

子查询出现的位置：

a. where后，作为条为被查询的一条件的一部分；

b. from后，作表；

当子查询出现在where后作为条件时，还可以使用如下关键字：

a. any

b. all

子查询结果集的形式：

a. 单行单列（用于条件）

b. 单行多列（用于条件）

c. 多行单列（用于条件）

d. 多行多列（用于表）

六、扩展

1.多行新增

insert into 表名(列名) values (列值),(列值),(列值)；

2.多表更新

(1)update 表1,表2 set 列名=列值 where 表1.列名=表2.列名 and 其他限定条件

(2)update 表1

inner join 表2 on 表1.列名=表2.列名

set 列名=列值

where 限定条件

3.多表删除

语法：

delete 被删除数据的表 from 删除操作中使用的表

where 限定条件

注:多张表之间使用逗号间隔

4.日期运算函数

now() 获得当前系统时间

year(日期值) 获得日期值中的年份

date_add(日期,interval 计算值计算的字段);

注：计算值大于0表示往后推日期，小于0表示往前推日期

七、数据库(SQL)优化

1.对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引

2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，

如：select id from t where num is null

最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据库.

备注、描述、评论之类的可以设置为 NULL，其他的，最好不要使用NULL。

3.应尽量避免在 where 子句中使用 != 或 <> 操作符，否则引擎将放弃使用索引而进行全表扫描。

4.应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描，

如：select id from t where num=10 or Name = ‘admin’

可以这样查询：

select id from t where num = 10 union all select id from t where Name = ‘admin’

5.in 和 not in 也要慎用，否则会导致全表扫描，

如：select id from t where num in(1,2,3)

对于连续的数值，能用 between 就不要用 in 了

如：select id from t where num between 1 and 3

很多时候用 exists 代替 in 是一个好的选择

八、事务

事务（Transaction）是由一系列对系统中数据进⾏访问与更新的操作所组成的⼀个程序执行逻辑单元。

1.事务的语法

start transaction; begin;
commit; 使得当前的修改确认
rollback; 使得当前的修改被放弃

2.事务的特性

原⼦性（Atomicity）

事务的原⼦性是指事务必须是⼀个原子的操作序列单元。事务中包含的各项操作在⼀次执⾏过程中，只允许出现两种状态之一。

1.全部执行成功

2.全部执行失败

事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执⾏过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发⽣一样。也就是说事务是⼀个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位。
⼀致性（Consistency）

事务的一致性是指事务的执⾏不能破坏数据库数据的完整性和一致性，一个事务在执⾏之前和执行之后，数据库都必须处以⼀致性状态。

比如：如果从A账户转账到B账户，不可能因为A账户扣了钱，⽽B账户没有加钱。
隔离性（Isolation）

事务的隔离性是指在并发环境中，并发的事务是互相隔离的。也就是说，不同的事务并发操作相同的数据时，每个事务都有各自完整的数据空间。

⼀个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务是不能互相干扰的。隔离性分4个级别。
持久性（Duration）

事务的持久性是指事务⼀旦提交后，数据库中的数据必须被永久的保存下来。即使服务器系统崩溃或服务器宕机等故障。只要数据库重新启动，那么一定能够将其恢复到事务成功结束后的状态

3.事务并发问题

脏读：读取到了没有提交的数据，事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据。

不可重复读：同⼀条命令返回不同的结果集（更新）。事务 A 多次读取同一数据，事务 B 在事务A 多次读取的过程中，对数据做了更新并提交，导致事务A多次读取同一数据时，结果不一致。

幻读：幻读就是指同样的事务操作，在前后两个时间段内执行对同一个数据项的读取，可能出现不一致的结果。重复查询的过程中，数据就发⽣了量的变化（insert， delete）。

4.事务隔离级别

事务隔离级别	脏读	不可重复读	幻读
读未提交（READ_UNCOMMITTED）	允许	允许	允许
读已提交（READ_COMMITTED）	禁止	允许	允许
可重复读（REPEATABLE_READ）	禁止	禁止	可能会
顺序读（SERIALIZABLE）	禁止	禁止	禁止

4种事务隔离级别从上往下，级别越高，并发性越差，安全性就越来越高。⼀般数据默认级别是读已提交或可重复读

查看当前会话中事务的隔离级别

mysql> select @@tx_isolation;

设置当前会话中的事务隔离级别

mysql> set session transaction isolation level read uncommitted;

读未提交（READ_UNCOMMITTED）

读未提交，该隔离级别允许脏读取，其隔离级别是最低的。换句话说，如果一个事务正在处理理某一数据，并对其进⾏了更新，但同时尚未完成事务，因此还没有提交事务；而以此同时，允许另一个事务也能够访问该数据。
读已提交（READ_COMMITTED）

读已提交是不同的事务执行的时候只能获取到已经提交的数据。这样就不会出现上面的脏读的情况了。但是在同一个事务中执行同一个读取，结果不一致不可重复读示例可是解决了脏读问题，但是还是解决不了可重复读问题。
可重复读（REPEATABLE_READ）

可重复读就是保证在事务处理理过程中，多次读取同一个数据时，该数据的值和事务开始时刻是一致的。因此该事务级别限制了不可重复读和脏读，但是有可能出现幻读的数据。

幻读：幻读就是指同样的事务操作，在前后两个时间段内执行对同一个数据项的读取，可能出现不一致的结果。
顺序读（SERIALIZABLE）

顺序读是最严格的事务隔离级别。它要求所有的事务排队顺序执⾏行行，即事务只能一个接一个地处理，不能并发。

5.不同隔离级别的锁的情况(了解)

读未提交（RU）：有行级的锁，没有间隙锁。它与RC的区别是能够查询到未提交的数据。
读已提交（RC）：有行级的锁，没有间隙锁，读不到没有提交的数据。
可重复读（RR）：有行级的锁，也有间隙锁，每次读取的数据都是一样的，并且没有幻读的情况。
序列化（S）：有行级锁，也有间隙锁，读表的时候，就已经上锁了

6.隐式提交(了解)

DQL:查询语句句

DML:写操作(添加,删除,修改)

DDL:定义语句句(建库,建表,修改表,索引操作,存储过程,视图)

DCL: 控制语⾔言(给⽤用户授权,或删除授权)

都是隐式提交。

隐式提交：执⾏行行这种语句句相当于执行 commit;

文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/13442.html

数据库操作