MySQL数据库简基本概念与语法

MySQL数据库简基本概念与语法

基本概念

关系型数据库

MySQL属于关系型数据库

关系型数据库只是一个保存数据的容器，

大多数数据库依靠一个称为

数据库管理系统（Database Management System，简称DBMS）的软件来管理数据库中数据。

管理关系型数据库的软件称为

关系型数据库管理系统（Relational Database Management System，简称RDBMS）。

数据库应用程序通过RDBMS与关系型数据库进行交互。

SQL简介

SQL是结构化查询语言（Structured Query Language）的英文缩写，发音为“see-kwell”。

是一种用于管理关系型数据库，并与数据库中的数据进行通讯的计算机语言。

SQL是一种用于数据库操作的语言，并且已经成为数据库管理的标准语言。

SQL与RDBMS（Relational Database Management System，关系型数据库管理系统的简称为RDBMS

管理关系型数据库的软件称为关系型数据库管理系统）协同工作，

来定义数据库的结构、存储数据、操纵数据、获取数据、控制对数据的访问以及确保数据的完整性。

SQL功能强大、简单易学、使用方便，已经成为了数据库操作的基础，并且现在几乎所有的数据库均支持

语言特点：

1.SQL集数据定义、数据操纵和数据控制于一体，可以完成数据库中的全部工作。

2.使用方式灵活：它具有两种使用方式，即可以直接以命令方式交互使用，也可以嵌入使用，嵌入到C、C++、php、JAVA等主流编程语言中使用。

3.非过程化：使用时只需要告诉计算机“做什么”，而不需要告诉它“怎么做”。

4.语言简洁，语法简单，核心功能只用6个动词，语法接近英语口语。

SQL语句组成

数据定义语言（Data Definition Language-DDL）：

用于定义数据库、定义数据表、定义视图与索引等。

数据操作语言（Data Manipulation Language-DML）：

用于操作关系型数据库对象内部的数据，基本命令插入(Insert)、更新(Update)和删除(Delete)

数据查询语言（Data Query Language-DQL）：

是现代关系型数据库用户最关注的部分，通过Select语句查询数据。

数据控制语言（Data Control Language-DCL）：

分为事务控制语句、安全性控制语句等，用于控制对数据库里数据的访问，通常用于创建与用户访问相关的对象，以及控制用户的权限，如修改密码(Alter password)、权限(Grant)等等。

基础语法

管理数据库

DDL语句

创建数据库：

CREATE DATABASE 库名;

显示数据库：

SHOW DATABASES;

删除数据库：

DROP DATABASE 库名;

数据类型

整型

类型	大小（byte）	范围（有符号）	范围（无符号）	用途
TINTINT	1	（-128，127）	（0，255）	小整数值
SMALLINT	2	-----------------------	（0，65535）	大整数值
MEDIUMINT	3	-----------------------	-----------------	大整数值
INT	4	-----------------------	-----------------	大整数值
BIGINT	8	-----------------------	-----------------	极大整数值

浮点类型

类型	大小（byte）	精度	用途
FLOAT	4	7位小数	单精度浮点数
DOUBLE	8	15位小数	双精度浮点数
DECIMAL(M,D)	17	30位小数	小数值

DECIMAL（M,D） M 表示 精度，数据的总长度， D 表示标度，小数点后的长度，M必须大于D

字符串数据类型

类型	范围	用途
CHAR(n)	0-255	顶长字符串
VARCHAR(n)	0-65535	变长字符串
TINYTEXT	0-255	短文本字符串
TEXT	0-65535	长文本数据
MEDIUMTEXT	-------------	中等长度文本数据
LONGTEXT	-------------	极大文本数据

时间日期数据类型

类型	大小（byte）	格式	用途
DATE	3	YYYY-MM-DD	日期值
TIME	3	HH:MM:SS	时间值或持续时间
YEAR	2	YYYY	年份值
DATETIME	8	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	YYYYMMDDHHMMSS	混合日期和时间值，时间戳

二进制数据类型

类型	大小（byte）	用途
TINYBLOB	0-255	不超过255个字符的二进制字符串
BLOB	0-65536	二进制形式的长文本数据
MEDIUMBLOB	---------	二进制形式的中等长度文本数据
LONGBLOB	---------	二进制形式的极大文本数据

管理表

连接数据库:

USE 库名;

创建表：

create  table  <表名>（  <列名>  <列的数据类型> [<列的约束>],…….);

显示表：

show tables;

修改表：

修改表名：

alter table 原表名 rename 新表名;

添加列：

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

修改列：

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

删除列：

alter table 表名 drop 列名;

查看表结构：

desc 表名;

删除表：

drop table 表名;

实现数据完整性

创建表时添加约束即可

修改数据完整性

添加主键约束：

alter table 表名 add primary key （主键名）;

删除主键约束：

alter table 表名 drop primary key;

添加唯一约束：

alter table 表名 add unique（列名）;

删除唯一约束：

alter table 表名 drop index 索引名;

查看表信息：

show create table 表名;

创建索引：

create index 索引名 on 表名（列名）;

删除索引：

alter index 索引名;

实现非空约束：

alter table 表名 change 原列名 新列名 数据类型 not null;

删除非空约束：

alter table 表名 change 原列名 新列名 数据类型;

实现默认约束：

alter table 表名 change 原列名 新列名 数据类型 default 默认值;

删除默认约束：

alter table 表名 change 原列名 新列名 数据类型;

实现外键约束：

alter table 表名 add constraint 外键约束名 foreign key(外键的列名) references 主表名称（主键列名）;

删除外键约束：

alter table 表名  drop foreign key 外键约束名称;   

DML语句

insert语句

插入单行数据：

insert into 表名(<列名列表....>) values（<值列表.....>）;

update语句

更新表数据：

update 表名 set 列1=新值1 , 列2=新值2 , N列=新值 where 过滤条件;

delete语句

删除表所有数据：

delete from 表名;
truncate table 表名;

delete 语句与 truncate 语句的 区别：

delete会记录日志，意味着删除后的数据还可以恢复，但是效率低。

truncate不会记录日志，删除后的数据不能恢复，但是效率高。truncate不能用于有外键约束引用的表。

delete 删除所有数据时，自动增长的值不会恢复到默认值开始，自动增长会按原值继续增长。

truncate 删除数据时，自动增长的值会恢复到默认值开始。

运算符

运算符是一个保留字或字符，主要用于在SQL语句的where子句来执行，比较和算术运算等操作

算术运算符

操作符	描述	例子
+	加号	a+b=14
—	减号	a-b=8
*	乘号	a*b=33
\	除号	a/b=3.333
%	取模	a%b=2

比较运算符

操作符	描述	例子
=	等于	a=b（假）
!=	不等于	a！=b（真）
<>	不等于	a<>b(真)
>	大于	a>b(真)
<	小于	a<b(假)
>=	大于等于	a>=b(真)
<=	小于等于	a<=b(假)

逻辑运算符

操作符	描述
ALL	和子查询的结果逐一比较，必须全部满足时表达式的值才为真
AND	允许where子句中存在多个条件，并且多个条件一起满足要求。
ANY	和子查询的结果逐一比较，其中一条记录满足条件则表达式的值就为真。
BETWEEN	表示一个范围搜索，大于等于下限，并且小于等于上限.
EXISTS	判断子查询是否存在数据，如果存在则表达式为真，反之为假
IN	允许在 where 子句中规定多个值
LIKE	用于比较相似的值，利用通配符来实现模糊查询。
NOT	与运算符含义相反
OR	允许where子句中存在多个条件，只要满足其中任意个一个条件即可。
ISNULL	NULL操作符用来比较NULL的值。

运算符优先级

优先级	运算符
1:	:=
2	||,or
3	xor
4	&&,and
5	not
6	between and,case,when,zhen,else
7	=,<=>,<,>,<=,>=,<>,!=,in,is null,like,regexp
8	|
9	&
10	<<,>>
11	-，+
12	*,/,%
13	^
14	-,~
15	!

DQL语句

简单查询

select查询语句完整语法：

SELECT 目标表的列名或列表达式集合
　　FROM 基本表或(和)视图集合
　　〔WHERE      条件表达式〕
　　〔GROUP BY   列名集合〕
　　〔HAVING     组条件表达式〕
　　〔ORDER BY   列名〔集合〕〕

投影操作

它将选择对表中哪些列进行操作，这些列将出现在结果中

select 列1,列2,列3,列N from 表名;

查询所有列(尽量不用)

select * from 表名;

显示表前缀

select 表名.列名1，表名.列名2，.......表名.列名N from 表名;
--如显示所有学生姓名
--select  student.name  from student

显示别列名

select 列名A  as A，列名B  as B,………列名N  as B from 表名;
--如显示所有学生姓名和年龄
--select  name  as ‘姓名’, age as ‘年龄’ from student

计算列

--显示所有学生的年龄，并显示他们5年以后的年龄
--select  age as‘年龄’, age+5 as ‘5年以后的年龄’ from student

排除重复数据

select distinct 列名 from 表名  --单列
select distinct 列名1，列名2 from 表名  --多列

返回限定行数

select 列名1,列名2,列...from 表名 limit 开始序列号, 返回的行数
--如显示前三学生的信息
--select * from student limit 3
--select * from  student  limit 0,3
--显示第三个和第四个学生的信息
--select * from  student  limit 2,2

分页操作

select 列名1,列名2,列...from 表名 limit 开始序列号, 返回的行数;
--开始序列号 = （当前页数 - 1）* 每页显示条数
--返回的行数 = 每页显示条数

选择操作

单条件选择查询：

select 列1,列2,...from 表名 where 列 = 值 

多条件选择查询：

and or：

select 列1,列2,...from 表名 where 条件1 （and 或者 or）（条件2） （and 或者 or）(条件3) 

between（大于等于下限，并且小于等于上限）：

select 列1,列2,...from 表名 where 列名  between  下限  and  上限

in not in（规定多个值）：

select 列1,列2,...from 表名 where 列名 in（值集合）

模糊查询（用于比较相似的值，利用通配符来实现模糊查询）：

like：

通配符：

‘_’ 通配符 表示任何单个字符

‘%’ 通配符 表示包含零个或多个任意字符

select *  from 表名 where name like ‘变量’

处理空值数据

在数据库中判断某列是否为空不能用=null，

而应该用 is null 或 i s not null。

使用其他任何比较运算符来匹配null得到的都是false的结果，

比如null=null也是返回FALSE。

排序操作

排序操作是指当查询的结果投影出来后以哪一列的顺序进行排列。如果不指定则以数据库默认顺序排列。

单列排序：

select * from  表名  order by 列A[asc, desc]

多列排序：

select 列1,列2,..from 表名 
order by 列1 [asc,desc], 列2 [asc,desc],… 

聚合函数

聚合函数就是把数据聚合起来的函数。

聚集函数是运行在行组上，计算和返回单个值的函数。

count

COUNT( )函数用来计算表中记录的个数或者列中值的个数。

select count(计算规范)  from  表名 ;

计算规范

*	计数所有选择的行，包括NULL值
all列名	计数指定列的所有的非空值行。如果仅仅是指定列而不带ALL或者DISTINCT，这是默认操作
distinct列名	计数指定列的所有唯一非空值行

count(All 列名)或则count(列名) 会过滤掉null值。

它只统计列中值不为空值的列的个数。

count（distinct 列名）和count（ALL 列名）

唯一区别就是DISTINCT会排除重复。

sum

sum()函数用于统计某列数值的总和

select sum(计算规范) from 表名;

计算规范

all列名	计数指定列的所有的非空值行。如果仅仅是指定列而不带ALL或者DISTINCT，这是默认操作
distinct列名	计数指定列的所有唯一非空值行

avg

avg()函数用于统计某列数值的平均值

select avg(计算规范) from 表名;

计算规范

all列名	计数指定列的所有的非空值行。如果仅仅是指定列而不带ALL或者DISTINCT，这是默认操作
distinct列名	计数指定列的所有唯一非空值行

ifnull

ifnull()返回一个数字或字符串值

ifnull(expr1，expr2)

如果expr1不是null，ifnull()返回expr1，否则它返回expr2。

round

round( ) 函数用于把数值字段舍入为指定的小数位数。

round(A，B)

A 代表是要用到的字段 B 是保留的小数

max

max 函数用于计算某个列的最大值。

select max(列名) from 表名

min

min 函数用于计算某个列的最小值。

select min(列名) from 表名

数据分组

group by子句

“Group By”从字面意义上理解就是根据“By”指定的规则对数据进行分组，所谓的分组就是将一个“数据集”划分成若干个“小区域”，然后针对若干个“小区域”进行数据处理

SELECT 列A, 聚合函数(聚合函数规范) FROM 表名
WHERE 过滤条件
GROUP BY 列A …

分组GROUP BY子句是在WHERE条件过滤后的结果中完成，如果没有WHERE条件则直接对FROM表中的数据进行分组

多列分组

分组可以实现多列分组，比如按班级和年龄分组，那么只有当班级和年龄一样的才分一组

在有分组的SQL语句中，投影的列就会有限制。

要求SELECT中的列必须满足以下两个条件之一，否则就是无效的投影。

1.使用了聚合函数的列 2.该列在GROUP BY 子句中

group by 一般和聚合函数一起使用才有意义

having子句

SQL提供了除了where的另外一个关键字having来实现聚合函数的过滤

having子句是在GROUP BY后执行的

SELECT 列A, 聚合函数(聚合函数规范) FROM 表名
WHERE 过滤条件
GROUP BY 列A
HAVING 过滤条件

Having 和 where的区别

二者都是过滤条件，

where运行在分组前，因此不能执行任何聚合函数。

having是运行在分组后，只能用做聚合函数的过滤。

sql语句的执行顺序：

第一步：执行from

第二步：where条件过滤

第三步：group by分组

第四步：执行select投影列

第五步：having条件过滤

第六步：执行order by排序

子查询

当一个查询是另一个查询的条件时，称之为子查询。

子查询是一个 SELECT 语句。

它可以嵌套在一个 SELECT语句、INSERT…INTO 语句、DELETE 语句、

或 UPDATE 语句或嵌套在另一子查询中。

在select中嵌套

select 列1,列2,（子查询）as 列别名 from 表名; 

嵌套在SELECT语句中SQL语句要求查询的值只能是单行和单列。

在from子句中嵌套

select 列1,列2,列N.. from (子查询) as 表别名;

From里的子查询可以是任意查询语句，然后将其结果作为外部查询的表。

嵌套在from 子句中的子查询，必须给一个表别名。

在where子句中嵌套

select 列1,列2,列N.. from 表名 where 列 = （子查询）;

在where中嵌套的子查询根据不同的运算符有不同的分类：

•比较运算符（>、<、=、>=、<=、!=）

•in 和not in运算符

•子查询运算符（all、any、 exists）

比较运算符

首先需要知道成员的具体值，这个可以作为一个查询，再将外部值与此值比较即可

in 和not in运算符

in 允许我们在where子句中规定多个值

not in 与in相反，表示不在集合值里面

子查询运算符

all 和子查询的结果逐一比较，必须全部满足时表达式的值才为真

any 和子查询的结果逐一比较，其中一条记录满足条件则表达式的值就为真。

all/any的用法中，在作数字比对时，也可以改用子查询的min/max value的方法，某些情况下效率更高。

exists 判断子查询是否存在数据，如果存在则表达式为真，反之为假。not exists 相反。

exists 和 in 两者是可以互换的，exists与in的使用效率的问题，通常情况下采用exists要比in效率高，因为in不走索引，但要看实际情况具体使用。

下面三个位置嵌套子查询实际作用不大，因此很少会用到。

在group by 中嵌套

​ group by中嵌套和select中嵌套类似，要求子查询只能返回单行单列值

在having中嵌套

​ having中嵌套子查询和where中嵌套子查询类似，只能用到聚合函数

在order by中嵌套

​ order by中嵌套子查询和select中嵌套子查询类似,要求子查询只能返回单行单列值

相关子查询

在主查询中，每查询一条记录，需要重新做一次子查询，这种称为相关子查询

执行依赖于外部查询的数据，外部查询返回一行，子查询就执行一次

执行顺序：先执行外部查询，当碰到内部查询时，再做内部查询，当内部查询完毕以后，继续执行外部查询。

非相关子查询

在主查询中，子查询只需要执行一次，子查询结果不再变化，供主查询使用，这种查询方式称为非相关子查询。

联合查询

联合查询指的是将多表中的行数据组合在一个数据集中进行显示

使用UNION运算符

UNION是集合查询中应用最多的一种运算符，

通过使用UNION运算符可以从多个表中将多个查询的结果组合到一起

select 列1,列2,列N... from 表1
union
select 列1,列2,列N... from 表2

使用UNOIN运算符时需要注意

UNION 的结果集列名与 UNION 运算符中第一个SELECT 语句的结果集中的列名相同。

另一个 SELECT 语句的结果集列名将被忽略。

投影列的个数必须要相同

使用UNION ALL运算符

UNION ALL会保留重复行

select 列1,列2,列N... from 表1
union all
select 列1,列2,列N... from 表2

表连接

表连接就是指将多个表联合在一起实现查询。

表连接采用的是笛卡尔乘积，称之为横向连接。

笛卡尔乘积

笛卡尔乘积是指将多张表的所有数据相连，最后连接的结果数为多张表数量的乘积。

在数据库中将多表相连需要使用JOIN关键字

select 列1,列2,..列N from 表1 join 表2 join 表N

表连接过滤错误数据

笛卡尔乘积出来的结果数量太多，其中有不少数据是错误的。

因为我们在建表时为了表示他们的关系，都会建立外键来确定关系， 所以在表连接时就要根据其外键来过滤错误的数据。

使用 on 关键字来确定其是否匹配

select 列1,列2,..列N from 表1 join 表2 on 表1.列 = 表2.列  

表连接分类

内连接

内连接被称为普通连接或者自然连接，

内连接是从结果表中删除与其他被连接表中没有匹配行的所有行。

所以内连接可能会丢失信息。

内连接的标准语法是inner join, inner可以省略

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

内连接的另一种实现

内连接除了用Iinner join on 外还可以使用更为简单的方式

这种方式是直接在from中用逗号来分隔多个表，从而实现连接，过滤条件写在where语句中。

这种方式和inner join是等价的。

外连接

左连接：

用法：left outer join 或 left join （outer 可以省略）

左外连接的结果集包括 left outer join子句中指定的左表的所有行，而不仅仅是连接列所匹配的行。

如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值

右连接：

用法：right outer join 或 right join

右外连接是左外连接的反向连接。将返回右表的所有行。

如果右表的某行在左表中没有匹配行，则将为左表返回空值

全连接：

用法：full outer join或 full join

全外连接返回左表和右表中的所有行。

当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。

如果表之间有匹配行，则整个结果集包含基表的数据值。

注意：mysql不支持全外连接

自连接

自连接是SQL语句中经常要用的连接方式，使用自连接可以将自身表的一个镜像当作另一个表来对待，从而能够得到一些特殊的数据。

索引

索引主要用于数据表中一列或多列的值进行排序，使用他可以有效提高查询效率。

数据量小的表最好不要使用索引

建表时建立索引：

create table 表名[列名1，数据类型]
....
index|key 索引名（列名1（索引长度））[ASC|DESC],
....
index|key 索引名（列名N（索引长度））[ASC|DESC]
);

索引分类

普通索引：INDEX 加快访问速度

组合索引：表在多个字段上组合创建的索引

唯一索引： UNIQUE 索引值必须唯一，主要目的为避免重复不允许为NULL且只能有一个

全文索引：FULLTEXT 可在CHAR VACHAR TEXT上创建，允许列值重复和为NULL，但只有MyISAM存储引擎支持

空间索引： SPATIAL 对空间数据类型创建的索引

查看索引

使用SHOW INDEX语句查看索引

SHOW INDEX 表名 \G;

使用EXPLAIN关键字查看索引使用情况

EXPLAIN SELECT * FROM 表名 where....;

在已有表上创建索引

创建索引

ALTER TABLE 表名 ADD [索引类型][INDEX|KEY][索引名]（列名（索引长度））[ASC|DESC];
CREATE [索引类型] INDEX 索引名 ON 表名 （列名（索引长度））[ASC|DESC];

删除索引

ALTER TABLE 表名 DROP INDEX 索引名;
DROP INDEX 索引名 ON 表名;

视图

视图是从数据库中的一张或多张表中导出的表

创建视图

CREATE VIEW 视图名[(列名)]
AS SELECT.....;

查看视图

查看视图结构

DESC 视图名;

查看视图定义语句

SHOW CREATE VIEW 视图名;

修改视图

CREATE OR REPLACE VIEW 视图名[(列名)]
AS SELECT......;
ALTER VIEW 视图名 [(列名)] 
AS SELECT......;

删除视图

DROP VIEW [IF EXISTS] 视图1，视图2，...，视图n;

存储过程和函数

一组经过编译并保存在数据库中的sql语句集合，可被随时调用

创建并调用存储过程和函数

创建存储过程

CREATE PROCEDURE 存储过程名([参数[,...])
[特性]函数体

特性：

language sql：函数体部分用sql语言编写

[not]deterministic ：表执行结果确定

函数体：

表需要执行的sql语句，以BEGIN开始，END结束。

创建存储函数

CREATE FUNCTION 函数名([参数][,...])
RETURNS TYPE
[特性]函数体

调用存储过程和函数

调用存储过程

CALL 存储过程名 ([变量[,...]])

调用存储函数

SELECT 存储函数名 ([变量[,...]])

存储过程和函数的表达式

变量

分类

用户变量：带前缀@，只能被定义他的用户使用

局部变量：无前缀，作用仅限于该语句块

系统变量：带前缀@@，会影响整个服务器

应用

定义变量

DECLARE 局部变量名[,...] 数据类型 [默认值];

为变量赋值

SET 变量名 = expr;

定义条件和处理程序

定义条件

DECLARE 条件名 CONDITION FOR [条件类型];

定义处理程序

DECLARE 异常处理方式 HANDLER FOR 错误值 [...] 语句段;

游标

当查询语句返回多条记录时，可用游标对结果集进行逐条读取

定义游标

DECLARE 游标名 CURSOR FOR SELECT语句;

打开游标

OPEN 游标名;

使用游标

FETCH 游标名 INTO 变量 [,变量] ...

关闭游标

CLOSE 游标;

流程控制的使用

条件判断语句IF,CASE

循环语句LOOP,LEAVE,REPEAT,WHILE

再次循环ITERATE

查看存储过程和函数

查看存储过程和函数的状态

SHOW {PEOCEDURE|FUNCTION} STATUS [LIKE '--']

查看存储过程和函数的定义

SHOW CREATE {PROCEDURE|FUNCTION} 存储过程名;

查看存储过程和函数的信息

SELECT * FROM information_schema.routines
WHERE 存储过程或函数名 = ''

修改和删除存储过程和函数

修改存储过程和函数

ALTER {PROCDURE|FUNCTION} 存储过程或函数名 [特性];

删除存储过程和函数

DROP {PROCDURE|FUNCTION} [IF EXISTS] 存储过程或函数名

触发器

创建触发器

CREATE TRIGGER 触发器名 触发时间 触发事件
ON 表名 FOR EACH ROW ....

创建AFTER触发器

1.INSERT

2.UPDATE

3.DELETE

创建BEFORE触发器

1.INSERT

2.UPDATE

3.DELETE

查看触发器

查看触发器

SHOW TRIGGERS \G

查看触发器的详细信息

SELECT * FROM information_schema.triggers WHERE 触发器名 = '';

删除触发器

DROP TRIGGER 数据库名.触发器名;