MySQL数据库简基本概念与语法_select a as b from table

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 库名;
1

显示数据库：

SHOW DATABASES;
1

删除数据库：

DROP DATABASE 库名;
1

数据类型

整型

浮点类型

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

字符串数据类型

时间日期数据类型

二进制数据类型

管理表

连接数据库:

USE 库名;
1

创建表：

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

显示表：

show tables;
1

修改表：

修改表名：

alter table 原表名 rename 新表名;
1

添加列：

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

修改列：

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

删除列：

alter table 表名 drop 列名;
1

查看表结构：

desc 表名;
1

删除表：

drop table 表名;
1

实现数据完整性

创建表时添加约束即可

修改数据完整性

添加主键约束：

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

删除主键约束：

alter table 表名 drop primary key;
1

添加唯一约束：

alter table 表名 add unique（列名）;
1

删除唯一约束：

alter table 表名 drop index 索引名;
1

查看表信息：

show create table 表名;
1

创建索引：

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

删除索引：

alter index 索引名;
1

实现非空约束：

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

删除非空约束：

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

实现默认约束：

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

删除默认约束：

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

实现外键约束：

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

删除外键约束：

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

DML语句

insert语句

插入单行数据：

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

update语句

更新表数据：

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

delete语句

删除表所有数据：

delete from 表名;
truncate table 表名;
1
2

delete 语句与 truncate 语句的 区别：

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

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

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

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

运算符

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

算术运算符

比较运算符

逻辑运算符

运算符优先级

DQL语句

简单查询

select查询语句完整语法：

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

投影操作

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

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

查询所有列(尽量不用)

select * from 表名;
1

显示表前缀

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

显示别列名

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

计算列

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

排除重复数据

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

返回限定行数

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

分页操作

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

选择操作

单条件选择查询：

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

多条件选择查询：

and or：

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

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

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

in not in（规定多个值）：

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

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

like：

通配符：

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

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

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

处理空值数据

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

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

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

比如null=null也是返回FALSE。

排序操作

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

单列排序：

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

多列排序：

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

聚合函数

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

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

count

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

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

计算规范

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

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

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

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

sum

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

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

计算规范

avg

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

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

计算规范

ifnull

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

ifnull(expr1，expr2)
1

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

round

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

round(A，B)
1

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

max

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

select max(列名) from 表名
1

min

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

select min(列名) from 表名
1

数据分组

group by子句

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

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

分组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 过滤条件
1
2
3
4

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 表名; 
1

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

在from子句中嵌套

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

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

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

在where子句中嵌套

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

在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
1
2
3

使用UNOIN运算符时需要注意

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

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

投影列的个数必须要相同

使用UNION ALL运算符

UNION ALL会保留重复行

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

表连接

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

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

笛卡尔乘积

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

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

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

表连接过滤错误数据

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

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

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

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

表连接分类

内连接

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

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

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

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

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

内连接的另一种实现

内连接除了用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]
);
1
2
3
4
5
6

索引分类

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

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

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

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

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

查看索引

使用SHOW INDEX语句查看索引

SHOW INDEX 表名 \G;
1

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

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

在已有表上创建索引

创建索引

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

删除索引

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

视图

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

创建视图

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

查看视图

查看视图结构

DESC 视图名;
1

查看视图定义语句

SHOW CREATE VIEW 视图名;
1

修改视图

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

删除视图

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

存储过程和函数

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

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

创建存储过程

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

特性：

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

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

函数体：

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

创建存储函数

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

调用存储过程和函数

调用存储过程

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

调用存储函数

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

存储过程和函数的表达式

变量

分类

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

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

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

应用

定义变量

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

为变量赋值

SET 变量名 = expr;
1

定义条件和处理程序

定义条件

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

定义处理程序

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

游标

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

定义游标

DECLARE 游标名 CURSOR FOR SELECT语句;
1

打开游标

OPEN 游标名;
1

使用游标

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

关闭游标

CLOSE 游标;
1

流程控制的使用

条件判断语句IF,CASE

循环语句LOOP,LEAVE,REPEAT,WHILE

再次循环ITERATE

查看存储过程和函数

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

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

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

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

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

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

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

修改存储过程和函数

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

删除存储过程和函数

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

触发器

创建触发器

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

创建AFTER触发器

1.INSERT

2.UPDATE

3.DELETE

创建BEFORE触发器

1.INSERT

2.UPDATE

3.DELETE

查看触发器

查看触发器

SHOW TRIGGERS \G
1

查看触发器的详细信息

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

删除触发器

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