数据库SQL语句总结

• 数据库操作

1 创建数据库

语法：

create database <数据库名>;

2 显示所有的数据库

语法：

show databases;

3 删除数据库

语法：

drop database <数据库名>;

4 连接数据库

语法：

use database <数据库名>;

5 查询当前连接的数据库

语法：

select database();

6 显示当前连接的数据库中的表信息

语法：

show tables;

• 基本表操作

1 数据定义：对基本表可作创建、删除、修改操作

1.1 创建基本表

语法：

create table <表名> (
<列名> <数据类型> [列级完整性约束条件],
[,<列名> <数据类型> [列级完整性约束条件]
......
,<表级完整性约束条件>]);

实例：

create table test(
id int(10) unsigned not null auto_increment,
name varchar(20) default null,
primary key (id)
);

1.2 删除基本表

语法：

drop table <表名>;

1.3 修改基本表

语法：

alter table <表名>
[add <新列表> <数据类型> [完整性约束]]        // 添加新列及其完整性约束条件
[drop <完整性约束名>]                                      // 删除指定的完整性约束条件；SQL不提供删除列的语句
[modify <列名> <数据类型>];                            // 修改原有列定义，包括列名和数据类型

1.4 获取表结构

语法：

desc <表名>;

或

show columns from <表名>;

1.5 重命名表名

语法：

rename table <旧表名> to <新表名>;

2 数据更新：对基本表的数据进行插入、修改、删改

2.1 插入数据：

2.1.1 插入单个元祖

语法：

insert
into <表名> [(<属性列1> [,<属性列2>...])]
values (<常量1> [,<常量2>...]);

2.1.2 插入子查询结果

语法：

insert
into <表名> [(<属性列1>[,<属性列2>...])]
子查询;

2.2 修改数据：数据包括一个元组、多个元组、子查询的结果

语法：

update <表名>
set <列名>=<表达式> [,<列名>=<表达式>...]
[where <条件>];

2.3 删除数据：数据包括一个元组、多个元组、子查询的结果

语法：

delete
from <表名>
[where <条件>];

• 索引操作

1 数据定义

1.1 创建索引

语法：

create [unique/cluster] index <索引名>
on <表名> (<列名> [<次序>] [,<列名> [<次序>]...]);

其中，unique表示此索引的每一个索引值只对应唯一的数据记录；cluste表示建立的索引是聚簇索引

1.2 删除索引

语法：

drop index <索引名>;

2 数据更新

这个说法本身就是错误的！

索引：相当于指针，提高查询速度，本身是没有数据项的，也就不存在对数据的更新的说法！

• 视图操作

1 创建视图

语法：

create view <视图名> [<列名> [,<列名>...]]
as <子查询>
[with check option];

其中，with check option是对视图进行update、insert、delete操作时保证更新、插入、删除的行满足视图定义中的子查询的条件表达式

2 删除视图

语法：

drop view <视图名> [cascade];

其中，cascade是级联删除由该视图导出的所有视图

3 更新视图

视图的数据更新语句与基本表的数据更新类似

视图：从一个或几个基本表（或视图）导出的表，是虚表；对视图的定义存放在数据字典中，视图的数据仍存放在原基本表中

• 查询

语法：

select [all/distinct] <目标列表达式> [,<目标列表达式>...]
from <表名或视图名> [,<表名或视图名>...]
[where <条件表达式>]
[group by <列名1> [having <条件表达式>]]
[order by <列名2> [asc/desc]];

根据where子句的条件表达式，从from子句指定的基本表或视图中找出满足条件的元组，再按select子句中的目标表达式，选出元组中的属性值形成结果表。

若有group子句，将结果按<列名1>的值进行分组，该属性值相等的元组为一组，通常在每组中作用集函数；having短语限定只有满足制定条件的组才可输出。

若有order子句，结果还要按<列名2>的值升序或降序排序。

1 单表查询：

1.1 选择表中若干列：

查询指定列

查询全部列

查询经过计算的值

1.2 选择表中的若干元组：

1.2.1 两个本不全相同的元组，投影到指定的某些列上后，可能变成相同的行。若想去除结果中的重复行，必须指定distinct短语；缺省值为all。

1.2.2 查询满足条件的元组：

比较：=, >, >=, <, <=, <>, !=, !<, !>, not+上述比较运算符

确定范围：between and, not between and

确定集合：in, not in

             字符匹配：like, not like （通配符有：_代表单字符，%代表任意长度字符串，[]代表匹配方括号中的任意一个字符，[^]代表不匹配方括号的任意一个字符；escape '<换码字符>'代表对通配符进行转义，适用于查询字符串本身含有_或%）

空值：null, not null

多重条件：and, or

1.3 对结果进行排序：

order by子句对最终查询结果按照一个或多个属性列的升序asc或降序desc排列，缺省为升序。

对于控制，升序排列时，显示在最后；降序排列时，显示在最前。

1.4 使用集函数：

注意：使用集函数时，列名不能与集函数一起使用；集函数不能出现在where子句中。

count ([distinct/all] *)：统计元组个数

count ([distinct/all] <列名>)：统计一列中值得个数

sum ([distinct/all] <列名>)：统计一列值的总和

avg ([distinct/all] <列名>)：计算一列值的平均数

max ([distinct/all] <列名>)：计算一列值的最大值

min ([distinct/all] <列名>)：计算一列值的最小值

1.5 对查询结果分组：

group by子句对查询结果按某一列或多列值分组，值相等的为一组。

若未对查询结果分组，集函数作用于整个查询结果；分组后，集函数作用于每个组。

2 连接查询：

2.1 内连接：

2.1.1 等值连接：使用=比较被连接列的列值

2.1.2 不等值连接：使用除=以外的比较运算符比较被连接列的列值

2.1.3 自然连接：使用等号比较被连接列的列值，使用选择列表指出查询结果集合中所包括的列，并删除链接表中重复列

语法：

select <列名1> [,<列名1>...]
from <表1> inner join <表2>
on <条件表达式>;

2.2 外连接：

2.2.1 右外连接：外连接符*加在连接条件的右边

2.2.2 左外连接：外连接符*加在连接条件的左边

2.2.3 全外连接：外连接符*加在连接条件的两边

语法：

select <列名1> [,<列名1>...]
from <表1> right/left/full join <表2>
on <条件表达式>;

2.3 交叉连接：

2.3.1 不带where子句，返回被连接的两个基本表的笛卡儿积

2.3.2 带where子句，先生成被连接的两个基本表的笛卡儿积，再根据where条件选择

语法：

select <列名1> [,<列名1>...]
from <表1> cross join <表2>
on <条件表达式>;

2.4 自身连接：

给表起别名以示区别，所有属性必须使用别名前缀。

3 嵌套查询：

3.1 带有in谓词的子查询：

在嵌套查询中，子查询结果往往是一个集合。

3.2 带有比较运算符的子查询：

父查询与子查询之间用比较运算符连接。子查询结果往往是单值。

3.3 带有any或all谓词的子查询：

使用any或all必须同时使用比较运算符。

>any

> all

< any

< all

= any

= all

!= any

!= all

3.4 带有exits谓词的子查询：

带有exits的子查询不反悔任何数据，只生成逻辑真值true或逻辑假值false。

目标表达式通常用 *。

4 集合查询：

使用union谓词连接多个查询结果。

• 数据控制

1 授权：

1.1 语法：

grant <权限> [,<权限>...]
[on <数据类型> <对象名>]
to <用户> [,<用户>...]
[with grant option];                // 获得某种权限的用户不仅可以使用该权限，还可以传播改权限

1.2 操作权限：

对象	对象类型	操作权限
属性列	table	select, insert, update, delete, all privileges
视图	table	select, insert, update, delete, all privileges
基本表	table	select, insert, update, delete,alter,modify,all privileges
数据库	database	createtab

2 收回权限：

2.1 语法：

revoke <权限> [,<权限>...]
[on <对象类型> <对象名>]
from <用户> [,<用户>...];