Mysql 进阶

MySQL 语法

一、MySQL相关术语

1.1. 数据库（Database)

• 按照数据结构方式来组织、存储和管理数据的仓库。根据是否依据关系模型来设计，将其类型分为关系型数据库和非关系型数据库。

• 关系模型：指实体与实体之间的数据关系，常见的关系模型：一对一、一对多、多对多等。

• 关系型数据库：以关系为基础进行组织数据的仓库或者说将现实世界中的实体和关系通过模型表示出来从而形成的一种数据存储关系。

  例子：人是由多个属性组成(如：姓名、性别等)，职业也是由多个属性组成(如：职业名称，编号)，人跟职业之间的关系是一对多的即一个人可以拥有多个职业，将人和职业数据按照这种关系模型存储起的仓库则称为关系型数据库。

• 非关系型数据库：不以关系模型为基础进行组织数据的仓库，它解决了大数据模式下的关系型数据存在的性能、拓展性瓶颈问题。非关系型数据去除了数据间的关系性，因此不能保证关系数据的ACID特征，它在数据库情况下有着更高的读写性能和更容易拓展的优点。

1.2. 数据表（Table）

  用于存储某一个组成某个实体的属性数据，一个数据库存在多个数据表。

1.3. 元组或者记录

  一个数据表由多个属性构成，由多个属性构成的一条数据也叫行或者记录，数据表就是由一行行的记录构成的。

1.4. 列或者字段

  组成数据表的每一个属性又叫做列或者字段，它们存储的相同类型的数据，如：性别字段，存储的值只有男女。

1.5. 主键

  能够唯一标识一个数据表中一条数据的一个或者多个字段(由多个字段构成的主键又称为联合主键)，且组成主键的字段应该是从不变化或极少变化的属性，一般这个主键的选取需要与业务无关，如：常见的自增长id。

1.6. 外键

  用来关联两个实体(数据表)之间一个关系的字段，拥有外键字段的表又叫做主表，使用外键字段作为主键的表则称为从表，现在一般推荐使用逻辑外键而不使用物理外键。

1.7. 索引

  本质上它是一种数据结构，通过对定义成索引的列进行排序好并存储起来，用来快速访问到数据表中某些数据的结果，类似于书本的目录，如MySQL数据库中的索引的实现就是B+Tree数据结构，常见的索引类型有：普通索引、主键索引、唯一索引、组合索引、全文索引、Hash索引等。

1.8. 冗余

  存储某些重复的数据，以达到防止数据丢失、提高查询性能等目的。

1.9. 范式

  定义：设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些规范要求被称为范式，各种范式呈递次规范，越高的范式数据库冗余越小。

  关系型数据库存在六种范式即：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式)。

• 第一范式：表中的每个列都不能再拆分既列有原子性。

• 第二范式：在第一范式的基础上，一个表必须拥有一个主键，同时非主键列完全依赖于主键，而不能依赖于主键的一部分。

• 第二范式举例：

  如一个订单明细表OrderDetail其属性如下：(OrderID，ProductID，UnitPrice，Discount，Quantity，ProductName)，一个订单中可以包含多个产品，所以单单一个OrderID 是不足以成为主键的，主键应该是（OrderID，ProductID）

  从表中可以知道Discount（折扣），Quantity（数量）完全依赖（取决）于主键（OderID，ProductID），而UnitPrice，ProductName 只依赖于 ProductID。所以 OrderDetail 表不符合 2NF。不符合 2NF的设计容易产生冗余数据。

• 第二范式举例解决方案：
  可以把OrderDetail表拆分为以下两个表，来消除原订单表中UnitPrice，ProductName多次重复的情况：

  OrderDetail（OrderID，ProductID，Discount，Quantity）

  Product （ProductID，UnitPrice，ProductName）

• 第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键(不存在传递依赖) 即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况

• 第三范式举例:
  如: 订单表Order：(OrderID，OrderDate，CustomerID，CustomerName，CustomerAddr，CustomerCity）主键是（OrderID）。其中OrderDate，CustomerID，CustomerName，CustomerAddr，CustomerCity 等非主键列都完全依赖于主键（OrderID），所以符合 2NF。

  不过问题是CustomerName，CustomerAddr，CustomerCity 直接依赖的是CustomerID（非主键列），而不是直接依赖于主键，它是通过传递才依赖于主键，所以不符合 3NF。

• 总结：

  第一范式：字段需要具有原子性

  第二范式：主键列与非主键列遵循完全函数依赖关系，不能是部分依赖关系

  第三范式：非主键列之间没有传递函数依赖关系

二、认识SQL

2.1. SQL语言的特点

• SQL语言对大小写不敏感，即SELECT和select其实效果是一样的
• 非过程化，只需要指明需要"做什么"，不需要指明"怎么做"，如：删除一个语句就直接delete from table where xxx
• 简单易学

2.2. SQL语言划分

• 一、DDL(Data Definition Language) 数据定义

  用于数据结构和数据库表定义、修改等操作语言，常用的关键字：CREATE(创建)、DROP(删除)、ALTER(修改)等。

• 二、DML(Data ManipulationLanguage) 数据操作语言

  主要用于对数据表中数据进行增、删、改等操作的语言，常用关键字：Insert、delete、update等，和DDL不同的是，它变动的是具体表中的数据，而DDL是对库和表等结构的变动。

• 三、DQL(Data Query Language) 数据查询语言

  主要负责根据条件查询和筛选数据，常用关键字：select、from、where、group by、having、order by等

• 四、DCL(Data Control Language) 数据控制语言

  主要负责管理用户访问数据权限的控制，它可以控制特定用户账户对数据表、查看表、预存程序、用户自定义函数等数据库对象的控制权。常用关键字：GRANT(授权语句)，REVOKE(撤销授权语句)。

• 五、TPL(Transaction Processing Language) 事务处理语言

  主要用于管理事务，保证数据的一致性。关键字：Start Transaction：开始事务，Commit：已完成的工作提交，Rollback：回滚之前没提交的所有操作。

  savepoint ：在事务中设置回滚点，结合rollback关键字，可以回滚到此处，一个事务可以设置多个回滚点，然后通过回滚到指定的回滚点完成对应事务的回滚销毁。

• 设置回滚点语法：savepoint 回滚点名字。

• 回滚到指定点语法：rollback to 回滚点名字。

2.3. DDL(Data Definition Language) 数据定义语言

Create 关键字

一、创建数据库

//创建名为test的数据库,设置中文字符
create database test default character set utf8;

二、创建数据表

//创建名为user的数据表
drop table if exists user;
create table user(
id int auto_increment,
user_name varchar(50),
sex char(2),
agr int,
primary key(id)
)engine=innodb charset utf8;

三、以另外一个表某些字段映射成新的表

例子：将user的表中的user_name映射成新的user2表

create table user2 as select user_name from user;

四、创建索引

  索引可用于快速地从数据库中检索数据。用户无法看到索引，它们只是用于加速搜索。如：在user表上的user_name字段创建名为idx_user_name的普通索引

create index idx_user_name on user(user_name);

五、创建视图

  视图是基于SQL语句结果集的一个虚拟的表，它的数据还是依赖于SQL语句的基本表，如果数据有变动，则视图查询回来的数据也会变动。如：创建名为user_view的视图

create view user_view as select user_name,sex from user;

  除此之外，还可以通过create or replace view来创建或者更新视图，如下：

create or replace view user_view as select user_name,sex from user;

ALTER关键字

  ALTER关键字主要的功能是用于修改之前使用Create关键字定义好的结构，如数据表中列的添加、删除、数据类型，视图的结构等。

一、往表中添加的新的列

//往user表中添加名为password 的子段
alter table user add column password varchar(255);

二、删除表中指定的列

//将user中password的字段
alter table user drop column password;

三、修改表中列的属性

//将user中的password的字段类型修改为int
alter table user modify column password int;

四、修改视图结构

// 将视图user_view 修改为只包含user_name字段的视图
alter view user_view as select name from user;

DROP关键字

  DROP关键字顾名思义，就是用于删除操作，比如删除表、字段、数据库等

一、删除数据库

//删除名为demo的数据库
drop database demo;

二、删除数据表

// 删除名为user的数据表
drop table user;

三、删除表中的某些字段

// 删除数据表user中的user_name字段
alter table user drop column user_name;

四、删除视图

// 删除名为user_view 的视图
drop view user_view;

五、删除普通索引

// 删除名为user_password的索引
alter table user drop index user_password;

六、删除主键索引

// 删除名为user_password 的主键索引
alter table user drop primary key;

2.4. DML(Data ManipulationLanguage) 数据操作语言

Insert关键字

  Insert关键字主要作用是往表中插入数据。语法：Insert into 表名(插入字段名…) values(字段对应的值)

一、往表中所有字段插入数据

  Insert关键字主要作用是往表中插入数据。语法：Insert into 表名(插入字段名…) values(字段对应的值)

//往user表所有字段插入数据
insert into user values(12,'张三',1,12);

insert into user (id,user_name,sex,age)values(1,'肖战','男',22);

二、从另一张表复制数据到新表

  语法：Insert into select 新表名称(字段名….) select 字段名…. from 另外的表名(注意：复制的字段值类型应该保持一致)

//将user表中的user_name,id两个字段的值复制到表user2中
insert into user(id,user_name) select id,user_name from user2;

Delete关键字

  Delete关键字主要用于数据表数据的删除操作，它只是删除表中的数据，并不会对表的结构和索引造成影响(注意：生产环境慎用该语句，如果要使用该语句时一定要检查仔细是否需要带where条件，不然，只能连夜买票跑路了 )

语法： Delete * from 表名 where 删除的条件

一、删除表中全部数据

// 删除user 表这两个的所有数据
delete * from user;

二、删除表中部分数据(需要结合where关键字)

// 删除user表中id为1的数据
delete * from user where id = 1;

Update关键字

  Update关键字主要用于对表数据的一个更新操作，不会对表结构和索引造成影响(注意：使用时一定要检查是否需要携带where条件，如果不携带，则更新的是表中所有的记录的数据)

一、更新某些记录字段的值(需要结合where条件)

// 更新 user 表中id为1的记录
update user set user_name =  '小明' where id = 1;

2.5. DQL(Data Query Language) 数据查询语言

在数据库的操作中，查询操作占百分之90以上，因此，掌握常用的查询操作至关重要。

Select关键字

  顾名思义，select主要是用于查询数据的，它结合其他的关键字使用，会存在非常多种组合，下面先来看看它的基础使用

一、查询所有数据(一般不推荐查询全部字段，只查询需要的字段即可，不然会降低查询性能)

// 查询表user中的所有数据
select * from user;

二、查询表中某些字段数据

// 查询表user中id,user_name字段
select id,user_name from user;

Distinct 关键字

一、查询表唯一不同的值(也可以说：去重)

// 如user表中user_name中名称很多重复，重复的名称我们只想它显示一个就可以，则可以使用distict
select distinct user_name from user;

  未使用distinct关键字去重之前的数据

  使用distinct关键字去重之后的数据

From 关键字

  From关键字后面接的是查询或者删除数据的来源，数据的来源常见的包括：数据表，子查询返回的数据集(删除的时候from后面需要接对应的数据表，不能是子查询的结果集)。

一、From后面接数据表，查询数据

// 查询user表中所有数据
select * from user;

二、From后面接子查询结果集，查询数据(结果集后面一定要有别名哦)

// 查询子查询返回的结果集
select * from (select * from user where id = 2) as temp_user;

//单行子查询
select * from user where id = (select id from user where id=2);

三、From后面接数据表，删除数据

// 删除user 表中id为1的数据

delete from user where id = 1;

Where关键字

  Where关键字主要是用于查询、删除、修改时做条件限制，过滤仅满足条件的数据返回。

  Where关键字后面常接的运算符：>、<、=、!=(不等于)、>=、<=、BETWEEN…AND(在一定范围内连续的值)、like(模糊匹配)、in(指定多个符合条件的值，不需要连续)

一、>、<、>=、<=运算符

// 查询id大于1的数据
select * from user where id >1

// 查询id大于等于2的数据
select * from user where id >2

// 查询id小于5的数据
select * from user where id < 5

// 查询id不等于1的数据
select * from user where id != 1

二、BETWEEN…AND(运算符)

// 查询id为1到10的数据
select * from user where id between 1 and 10;

三、like运算符

  结合like关键字使用的通配符：%表示匹配零个或者多个字符，_表示匹配任意一个字符

// 查询名字以五开头的数据
select * from user where name like '%五';

// 查询名字以五结尾的数据
select * from user where name like '五%';

// 查询以五开头，名字只包含两个字符的数据
select * from user where name like '五_';

四、in运算符

// 查询id为1、3、5的数据
select * from user where in (1,3,5);

聚合函数

  定义：对一组值进行算计，返回单个值，也被称作组函数，常与Group by 和 Having关键字联合使用，常见的聚合函数如：AVG(计算平均值)，Count(计算总条数)等等。

AVG聚合函数

  定义： 用于返回数值列的平均值，NULL值的列不在计算范围。

  语法： select avg(要统计的数值列名称) from 表名

// 统计user表中所有用户的平均年龄
select AVG(age) from user;

COUNT聚合函数

  定义： 用于返回符合条件的记录数，NULL值的列不在计算范围。

  语法： select count(要统计的列名称) from 表名

//统计user表中总的用户数
select count(*) from user;

延伸： count()、count(字段名)、count(1)有什么差别?
  count()和count(1)一样，返回符合条件的记录总条数，count(字段名)则是返回符合条件中字段名所在的列中非NULL值的记录总条数(即如果字段名对应列的值为NULL，则不会被记到总数中)。COUNT(*)是 SQL92 定义的标准统计行数的语法，并且效率高，所以请直接使用COUNT(*)查询表的行数！

MAX聚合函数

 定义： 用于统计列中的最大值，NULL值的列不在计算范围(注意：MAX 也可用于文本列，以获得按字母顺序排列的最高值)。

 语法： select MAX(要统计的列名称) from 表名

//统计user表中年龄最大为多少岁
select MAX(age) from user;

MIN聚合函数

  定义： 用于统计列中的最小值，NULL值的列不在计算范围(注意：MIN也可用于文本列，以获得按字母顺序排列的最小值)。

  语法： select MIN(要统计的列名称) from 表名

//统计user表中年龄最小为多少岁
select MIN(age) from user;

SUM聚合函数

  定义： 统计返回数值列的累加总数，NULL值的列不在计算范围

  语法： select SUM(要统计的列名称) from 表名

// 统计user表中年龄总数
select SUM(age) from user;

Group by 关键字

  定义： 主要用于数据结果集进行分组，常和聚合函数、以及Having关键字一起使用(注意：使用group by关键字时，select 后面查询的字段必须出现在group by后面或者使用聚合函数包围起来)。

  语法： select 分组字段 from 表名 group by 分组字段

//统计user表中男女的数量
select count(*) ,sex from user group by sex;

Having 关键字

  定义： 如果SQL语句中使用了聚合函数和Group by，此时还想在这基础上对查询出来的结果集进行条件筛选，则可以使用Having关键字，不能使用where关键字。

  语法： select 分组字段 from 表名 group by 分组字段 having xx条件

//统计user表中年龄大于15岁的男女生人数
select count(*),sex from user GROUP BY sex HAVING avg(age) > 15;

Order by 关键字

  定义： 用于按升序或降序对结果集进行排序(注意：Order by命令默认按升序对结果集进行排序。要按降序对记录进行排序，则使用DESC关键字)

  语法： select xxx from 表名 order by xx字段 [asc | desc]

//将user表中的数据按照年龄的顺序排序
select * from user order by age;

2.6. TPL(Transaction Processing Language) 事务处理语言

  定义： 主要用于管理事务，保证数据的一致性。关键字：Start Transaction：开始事务，Commit：已完成的工作提交，Rollback：回滚之前没提交的所有操作。

  事务： 它表示一个操作集合，在集合中的操作要么都执行，要么都不执行，它是一个不可分割的工作单位。例如，银行转帐工作：从一个帐号扣款同时另一个帐号增款，这两个操作要么都执行，要么都不执行。

事务的特性

  数据库的事务必须具有以下四个属性即我们常说的ACID：Atomic（原子性）、Consistency（一致性）、Isolation（隔离性）和Durability（持久性）

  Atomic(原子性)： 一个事务(Transaction)中的所有操作，要么全部完成，要么全部不完成，它是原子性的，不会结束在中间某个环节，即使在执行过程出现异常，那也会回滚到事务开始前的状态。

  Consistency(一致性)： 在事务开始之前和结束之后，数据完整性不被破坏。比如转账行为：A用户有20元，B用户有10元，A用户转了10元给B用户，当转账行为结束后，A和B用户账户的数额总额还是30元。

  Isolation(隔离性)： 在并发环境中，当不同的事务同时操纵相同的数据时，每个事务都有各自的完整数据空间，事务查看数据更新时，数据所处的状态要么是另一事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看到中间状态的数据，可以通过设置不同隔离级别来实现不一样的效果

  Durability(持久性)： 事务成功执行后，它对数据库所做的操作就必须永久保存下来。即使发生系统崩溃，重新启动数据库系统后，数据库还能恢复到事务成功结束时的状态。

事务的运用

  一、开启事务： DML语言的事务默认情况下是自动提交的，如有需要手动控制，可以使用：start Transaction

  二、提交事务： 如果事务集合操作已经执行完成，需要手动提交，使用：commit

  三、回滚事务： 如果在执行过程中，出现错误情况，需要将事务开启后执行的操作都撤销，可以使用：rollback

  四、设置回滚点： 如果需要自定义回滚点，可以使用：savepoint 回滚点名字。

  五、回滚到指定点语法： rollback to 回滚点名字