首页 > 代码库 > 常用SQL操作(MySQL或PostgreSQL)与相关数据库概念
常用SQL操作(MySQL或PostgreSQL)与相关数据库概念
本文对常用数据库操作及相关基本概念进行总结：MySQL和PostgreSQL对SQL的支持有所不同，大部分SQL操作还是一样的。
选择要用的数据库(MySQL)：use database_name;
help show;
显示可用的数据库列表：show databases;
显示一个数据库内可用的表的列表:show tables;
显示表列：show columns from 一个表名; /describe 表名; 示例：show columns from customers; describe customers;
显示广泛的服务器状态信息:show status;
显示创建特定数据库或表的mysql语句：show create databases; show create table;
显示授予用户的安全权限：show grants；
显示服务器错误或警告消息:show errors; show warnings;
help select;
select 从一个或多个表中检索一个或多个数据列；
select 列(字段) from 表名; select prod_name from products;
检索多个列
select prod_id, prod_name, prod_price from products;
检索所有列
select * from products;
select vend_id from products;
检索不同的行
select distinct vend_id from products;
限制结果
select prod_name from products limit 5;
使用完全限定的列名
select products.prod_name from products;
使用完全限定的表名
select products.prod_name from crashcouse.products;
排序检索数据
为了明确地排序用select语句检索出的数据，可使用order by字句；
order by 字句取一个或多个列的名字，据此对输出进行排序。如下：
select prod_name from products order by prod_name;
用非检索的列排序数据是完全合法的。
按多个列排序
为了按多个列排序，只要指定列名，列名之间用逗号分开即可(就像选择多个列是所做的那样)
select prod_id, prod_price, prod_name from products order by prod_price , prod_name;
指定排序方向(默认升序排序从A到Z)，降序排序必须指定desc关键字
desc关键字只应用到直接位于其前面的列名。
select prod_id, prod_price , prod_name from products order by prod_price desc;
用多个列排序
select prod_id, prod_price, prod_name from products order by prod_price desc, prod_name;
在多个列上降序排序：如果想在多个列上进行降序排序,必须对每个列指定desc关键字
与desc相反的关键字是asc（ascending），在排序是可以指定他，升序是默认的
使用order by 和limit的组合，能够找出一个列中最高或最低的值。
order by 子句应位于from子句，limit子句应位于order by 子句后面
select prod_price from products order by prod_price desc limit 1;
过滤数据：使用select 语句的where 子句指定搜索条件(也称为过滤条件filter condition)
在select语句中，数据根据where子句中指定的搜索条件进行过滤。where子句在表名(from子句)之后给出，如下所示：
select prod_name, prod_price from products where prod_price = 2.50;
在同时使用order by 和 where子句时，应该让order by 位于where之后
检查单个值 单引号用来限定字符串
select prod_name, prod_price from products where prod_name = ‘fuses’;
select prod_name , prod_price from products where prod_price < 10;
select prod_name , prod_price from products where prod_price < =10;
不匹配检查<>
select vend_id, prod_name from products where vend_id <> 1003;
select vend_id, prod_name from products where vend_id != 1003;
范围值检查:between
select prod_name,prod_price from products where prod_price between 5 and 10;
空值检查 IS NULL
select prod_name from products where prod_price IS NULL;
select cust_id from customers where cust_email IS NULL;
数据过滤:组合where子句 and指示DBMS只返回满足所有给定条件的行
为了通过不止一个列进行过滤，可使用and操作符给where子句附件条件
select prod_id, prod_price, prod_name from products where vend_id = 1003 and prod_price <=10;
每添加一个过滤条件就使用一个and。
or操作指示检索匹配任一条件的行。
select prod_name, prod_price from products where vend_id =1002 or vend_id =1003
SQL在处理OR操作符前，优先处理AND操作符。
可以使用圆括号明确地分组相应的操作符。
select prod_name,prod_price from products where (vend_id = 1002 OR vend_id =1003) AND prod_price >=10;
IN操作符：用来指定条件范围，范围中的每个条件都可以进行匹配。IN取合法值的由逗号分隔的清单，全都在圆括号中。
select prod_name,prod_price from products where vend_id IN (1002,1003) order by prod_name;
IN操作符完成与OR相同的功能
select prod_name,prod_price from products where vend_id = 1002 OR vend_id = 1003 order by prod_name;
where子句中的not操作符有且只有一个功能：否定它之后所跟的任何条件的关键字。
select prod_name,prod_price from products where vend_id NOT IN (1002,1003) order by prod_name;
用通配符进行过滤
通配符：用来匹配值的一部分的特殊字符。
搜索模式：由字面值、通配符或两者组合构成的搜索条件。
通配符是SQL的where子句中有特殊含义的字符，SQL支持几种通配符
为在搜索子句中使用通配符，必须使用LIKE操作符。
百分号(%)通配符：%表示任何字符出现任意次数。
select prod_id, prod_name from products where prod_name LIKE ‘jet%’;
select prod_id prod_name from products where prod_name LIKE ‘%anvil%’;
select prod_name from products where prod_name LIKE ‘s%s’;
下划线(_)通配符：下划线用途与%一样，下划线只匹配单个字符而不是多个字符。
select prod_id， prod_name from products where prod_name LIKE ‘_ ton anvil’;
select prod_id， prod_name from products where prod_name LIKE ‘% ton anvil’;
用正则表达式进行搜索
正则表达式是用来匹配文本的特殊的串(字符集合)
基本字符匹配
select prod_name from products where prod_name regexp ‘1000’ order by prod_name;
除关键LIKE被regexp替代外，这条语句看上去像使用LIKE的语句，它告诉MySQL：regexp后跟的东西作为正则表达式(与文字正文1000匹配的一个正则表达式)处理。
select prod_name from products where prod_name regexp ‘.1000’ order by prod_name;
这里使用了正则表达式 .1000, .是正则表达式语言中一个特殊的字符。它表示匹配任意一个字符。
|为正则表达式的OR操作符,如下所示：
select prod_name from products where prod_name regexp ‘1000|2000’ order by prod_name;
匹配几个字符之一
匹配特定的字符，可通过指定一组用[ 和 ]括起来的字符来完成。
select prod_name from products where prod_name regexp ‘[123] Ton’ order by prod_name;
[123]定义一组字符，它的意思是匹配1或2或3
为否定一个字符集,在集合的开始处放置一个^即可，[^123]匹配除这些字符外的任何东西。
匹配范围：集合可用来定义要匹配的一个或多个字符。
[0123456789], [0-9]匹配数字0到9
[a-z]匹配任意字母字符
select prod_name from products where prod_name regexp ‘[1-5] Ton’ order by prod_name;
为了匹配特殊字符必须用\\为前导。\\- 表示查找 - ,\\. 表示查找 . 。
select vend_name from vendors where vend_name regexp ‘\\.’ order by vend_name;
创建计算字段
计算字段并不实际存在于数据库表中，是运行时在select语句内创建的。
拼接字段：
拼接：将值联接到一起构成单个值。解决办法：把两个列拼接起来，在MySQL的select语句中，可以使用Concat()函数来拼接两个列。多数DBMS使用+或||来实现拼接。
Concat()拼接串,即把多个串连接起来形成一个较长的串。Concat()需要一个或多个指定的串,各个串之间用逗号分隔。
select Concat(vend_name, ‘ (‘, vend_conntry, ‘) ‘ ) from vendors order by vend_name;
通过删除数据右侧多余的空格来整理数据,可以使用MySQL的RTrim()函数来完成，如下：
select Concat(RTrim(vend_name), ‘ (‘, RTrim(vend_country), ‘)’) from vendors order by vend_name;
RTrim()函数去掉值右边的所有空格。通过使用RTrim(),各个列都进行了整理。
MySQL还支持LTrim()去掉左边的空格以及Trim()去掉串左右两边的空格。
使用别名：
SQL支持别名。别名(alias)是一个字段或值的替换名。别名用AS关键字赋予。
select Concat(RTrim(vend_name), ‘ (‘, RTrim(vend_country), ‘)’) AS vend_title from vendors order by vend_name;
执行算术计算
select prod_id, quantity, item_price from orderitems where order_num = 20005;
select prod_id,
quantity,
item_price, quantity * item_price AS expended_price
from orderitems
where order_num = 20005;
如何测试计算：
select提供了测试和试验函数与计算的一个很好的办法。
select 可以省略from子句以便简单地访问和处理表达式。
如:select 3 * 2;将返回6，select Trim(‘abc’);将返回abc.
select Now()利用Now()函数返回当前日期和时间。
使用数据处理函数
文本处理函数 Upper()函数(将文本转换为大写)
select vend_name, Upper(vend_name) AS vend_name_upcase
from vendors
order by vend_name;
常用的文本处理函数
Left( ) 返回串左边的字符
Length( ) 返回串的长度
Locate( ) 找出串的一个子串
Lower( ) 将串转换为小写
LTrim( ) 去掉串左边的空格
Right( ) 返回串右边的字符
RTrim( ) 去掉串右边的空格
Soundex( ) 返回串的SOUNDEX值：将任何文本串转换为描述其语音表示的字母数字模式，
使得能对串进行发音比较二不是字母比较。
SubString( ) 返回子串的字符
Upper( ) 将串转换为大写
select cust_name, cust_contact from customers where Soundex(cust_contact) = Soundex(‘Y Lie’);
时间和日期处理函数
常用日期和时间处理函数
AddDate( ) 增加一个日期(天，周等)
AddTime( ) 增加一个时间(时，分等)
CurDate( ) 返回当前日期
CurTime( ) 返回当前时间
Date( ) 返回当前日期时间的日期部分
DateDiff( ) 计算两个日期之差
Date_Add( ) 高度灵活的日期运算函数
Date_Format( ) 返回一个格式化的日期或时间串
Day( ) 返回一个日期的天数部分
DayOfWeek( ) 对于一个日期，返回对应的星期几
Hour( ) 返回一个时间的小时部分
Minute( ) 返回一个时间的分钟部分
Month( ) 返回一个时间的月份部分
Now( ) 返回当前日期和时间
Second( ) 返回一个时间的秒部分
Time( ) 返回一个日期时间的时间部分
Year( ) 返回一个日期的年份部分
select cust_id, order_num from orders
where order_date = ‘2005-09-01’;
Date(order_date)指示MySQL仅提取列的日期部分，更可靠的select语句为：
select cust_id, order_num from orders
where Date(order_date) = ‘2005-09-01’;
select cust_id, order_num from orders
where Date(order_date) between ‘2005-09-01’ and ‘2005-09-30’;
select cust_id, order_num from orders
where Year(order_date) = 2005 and Month(order_date) = 9 ;
数值处理函数：
Abs( ) 返回一个数的绝对值
Cos( ) 返回一个角度的余弦
Exp( ) 返回一个数的指数值
Mod( ) 返回除操作的余数
Pi( ) 返回圆周率
Rand( ) 返回一个随机数
Sin( ) 返回一个角度的正弦
Sqrt( ) 返回一个数的平方根
Tan( ) 返回一个数的正切
汇总数据
聚集函数：检索数据，以便分析和报表生成 产生 汇总信息
确定表中行数(或者满足某个条件或包含某个特定值的行数)
获得表中行组的和
找出表列(或所有行或某些特定的行)的最大值,最小值和平均值
MySQL的5个聚集函数
聚集函数(aggregate function) 运行在行组上,计算和返回单个值的函数
SQL聚集函数
AVG( ) 返回某列的平均值
COUNT( ) 返回某列的行数 确定表中行的数目或符合特定条件的行的数目
MAX( ) 返回某列的最大值
MIN( ) 返回某列的最小值
SUM( ) 返回某列值之和
AVG( )(只用于单个列) 通过对表中行数计算并计算特定列值之和，求得该列的平均值。
AVG( )可用来返回所有列的平均值，也可以用来返回特定列或行的平均值。
select AVG(prod_price) AS avg_price from products;
select AVG(prod_price) AS avg_price from products where vend_id = 1003;
COUNT(*)对表中行的数目进行计数，不管列表中包含的是空值(NULL)还是非空值。
COUNT(column)对特定列中具有值的行进行计数，忽略NULL值。
下面的例子返回customers表中客户的总数：COUNT(*)对所有行计数
select COUNT(*) AS num_cust from customers;
select COUNT(cust_email) AS num_cust from customers;
select MAX(prod_price) AS max_price from products;
select MIN(prod_price) AS min_price from products;
select SUM(quantity) AS items_ordered from orderitems where order_num = 20005;
select SUM(item_price * quantity) AS total_price from orderitems where order_name = 20005;
聚集不同值
对所有的行执行计算，指定ALL参数或不给参数(因为ALL是默认行为)；
只包含不同的值，指定DISTINCT参数。
平均值只考虑各个不同的价格：
select AVG(DISTINCT prod_price) AS avg_price from products where vend_id = 1003;
组合聚集函数：select 语句可根据需要包含多个聚集函数。
select COUNT(*) AS num_items,
MIN(prod_price) AS price_min,
MAX(prod_price) AS price_max,
AVG(prod_price) AS price_avg,
from products;
联结(join):是一种机制,用来在一条select语句中关联表，称之为联结;使用特殊的语法,可以联结多个表返回一组输出,联结在运行时关联表中正确的行。
借助联结(join)可以用单条select语句检索出存储在多个表中的数据
联结不是物理实体,它在实际的数据库表中不存在；联结有MySQL根据需要建立，它存在于查询的执行当中。
创建联结
联结的创建非常简单，规定要联结的所有表以及它们如何关联即可。如下：
select vend_name, prod_name, prod_price
from vendors, products
where vendors.vend_id = products.vend_id order by vend_name, prod_name;
完全限定列名：用一个点分隔的表名和列名。(如vendors.vend_id)；
如果饮用一个没有用表名限制的具有二义性的列名，MySQL将返回错误。
笛卡尔积：由没有联结条件的表关系返回的结果为笛卡尔积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。
select vend_name, prod_name, prod_price
from vendors,products order by vend_name,prod_name;
应该保证所有联结都有where子句，否则MySQL将返回比想要的数据多的多的数据。
内联结
select vend_name,prod_name,prod_price
from vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;
与
select vend_name, prod_name, prod_price
from vendors, products
where vendors.vend_id = products.vend_id order by vend_name, prod_name;的功能相同
联结多个表：首先列出所有表，然后定义表之间的关系。例如：
select prod_name, vend_name, prod_price, quantity
from orderitems, products, vendors
where products.vend_id = vendors.vend_id
AND orderitems.prod_id = products.prod_id
AND order_num = 2005;
select cust_name, cust_contact
from customers, orders, orderitems
where customers.cust_id = orders.cust_id
AND orderitems.order_num = orders.order_num
AND prod_id = ‘TNT2’;
创建高级联结(16)
给表起别名的语法如下：
select Concat(RTrim(vend_name), ‘ (‘, RTrim(vend_country), ‘)’ ) AS
vend_title
from vendors
order by vend_name;
给表名起别名：可以缩短SQL语句，允许在单条select语句中多次使用相同的表。例如：
select cust_name, cust_contact
from customers AS c, orders AS o, orderitems AS oi
where c.cust_id = o.cust_id
AND oi.order_num = o.order_num
AND prod_id = ‘TNT2’;
表别名只在查询执行中使用。表别名不返回到客户机。
自联结
select prod_id, prod_name
from products
where vend_id = (select vend_id
from products
where prod_id = ‘DTNTR’);
使用联结的相同查询：
select p1.prod_id, p1.prod_name
from products AS p1, products AS p2
where p1.vend_id = p2.vend_id
AND p2.prod_id = ‘DTNTR’;
21创建和操纵表
一般有两种创建表的方法：
使用具有交互式创建和管理表的工具
也可以直接使用MySQL语句操作。
下面的MySQL语句创建本书中所用的customers表:
create table customers IF NOT EXISTS
(
cust_id int not null auto_increment,
cust_name char(50) not null,
cust_address char(50) null,
cust_city char(50) null,
cust_state char(5) null,
cust_zip char(10) null,
cust_country char(50) null,
cust_contact char(50) null,
cust_email char(255) null,
primary key (cust_id)
)ENGINE = InnoDB;
create table orders
(
order_num int not null auto_increment,
order_date datetime not null,
cust_id int not null,
primary key (order_num)
)ENGINE = InnorDB;
create table venders
( vend_id int not null auto_increment,
vend_name char(50) not null,
vend_address char(50) null,
vend_city char(50) null,
vend_state char(5) null,
vend_zip char(10) null,
vend_country char(50) null,
primary key (vend_id)
)ENGINE = InnoDB;
由多个列组成的主键：
create table orderitems
(
order_num int not null,
order_item int not null,
prod_id char(10) not null,
quantity int not null,
item_price decimal(8,2) not null,
primary key (order_num, order_item)
)ENGINE =InnoDB;
每个表只允许一个auto_increment列，而且它必须被索引(如，通过使它成为主键)。
可使用last_insert_id( )函数获得这个值，如下所示：
select last_insert_id( ); 此语句返回最后一个auto_increment值，然后可以将它用于后续的MySQL语句。
指定默认值(如果在插入行是没有给出值，MySQL允许指定此时使用的默认值。默认值用create table 语句的列定义中的default关键字自定。)
create table orderitems
(
order_num int not null,
order_item int not null,
prod_id char(10) not null,
quantity int not null default 1,
item_price decimal(8,2) not null,
primary key (order_num, order_item)
)ENGINE=InnoDB;
引擎类型
MySQL有一个具体管理和处理数据的内部引擎。
在你使用create table 语句时，该引擎具体创建表，二在你使用select语句或进行其他数据库处理时，该引擎在内部处理你的请求。多说时候，此引擎都隐藏在DBMS内，不需要过多关注他。
InnoDB是一个可靠的事务处理引擎，它不支持全文本搜索；
MEMORY在功能等同于MyISAM，但由于数据存储在内存（不是磁盘）中，速度很快（特别适用于临时表）；
MyISAM是一个性能极高的引擎，它支持全文本搜索，但不支持事务处理。
更新表：alter table语句。
为了使用alter table 更改表结构，必须给出下面的信息；
1.在alter table之后给出要更改的表名(该表必须存在，否则将出错 )；
2.所做更改的列表。
下面的例子给表添加一个列：
alter table vendors
add vend_phone char(20)；
删除刚刚添加的列，可以这样做：
alter table vendors
drop column vend_phone;
alter table的一种常见用途是定义外键。
alter table orderitems
add constraint fk_orderitems_orders
foreign key (order_num) references orders (order_num);
alter table orderitems
add constraint fk_orderitems_products foreign key (prod_id)
references products (prod_id);
alter table orders
add constraint fk_orders_customers foreign key (cust_id)
references customers (cust_id);
alter table products
add constraint fk_products_vendors
foreign key (vend_id) references vendors (vend_id);
alter table前应该备份
删除表
drop table customers2;
重命名表:rename table;
rename table customers2 to customers;
对多个表重命名：
rename table backup_customers to customers,
backup_vendors to vendors,
backup_products to products;
22使用视图
视图是虚拟的表，与包含数据的表不一样，视图只包含使用时动态检索数据的查询。
视图不包含表中应该有的任何列或数据，它包含的是一个SQL查询。
为什么使用视图？
1 重用SQL语句
2 简化复杂的SQL操作
3 使用表的组成部分而不是整个表
4 保护数据
5 更改数据格式和表示。视图可以返回与底层表的表示和格式不同的数据。
在视图创建之后，可以用于表基本相同的方式利用它们。可以对视图执行select，过滤，排序数据，将视图联结到其他视图或表，甚至能添加和更新数据。
重点：视图仅仅是用来查看存储在别处的数据的一种设施。视图本身不包含数据。因此它们返回的数据是从其他表中检索出来的。在添加或更改这些表中的数据时，视图将返回改变过的数据。
使用视图：
1创建视图create view语句
2使用show create viewviewname;来查看创建视图的语句。
3删除视图,dorp viewviewname;
4更新视图，先drop,再create，也可以直接用createor replace view;如果要更新的视图不存在，则第二条更新语句会创建一个视图；如果要更新的视图存在，则第二条更新语句会替换原有视图。
create view productcustomers AS
select cust_name, cust_contact, prod_id
from customers ,orders , orderitems
where customers.cust_id= orders.cust_id
AND orderitems.order_num= orders.order_num;
这条语句创建一个名为productcustomers的视图，它联结三个表，返回已订购了任意产品的所有客户的列表。
select * from productcustomers;将列出订购了任意产品的客户。
select cust_name, cust_contack
from productcustomers
where prod_id = ‘TNT2’;
用视图重新格式化检索出的数据
select Concat(RTrim(vend_name), ‘ (),RTrim(vend_country),’)’) AS vend_title
from vendors
order by vend_name;
将以上语句转换为视图
create view vendorlocations AS
select Concat(RTrim(vend_name), ‘ (‘,RTrim(vend_country), ‘) ’ )
AS vend_title
from vendors
order by vend_name;
select * from vendorlocations;
create view customeremaillist AS
select cust_id, cust_name,cust_email
from customers
where cust_email IS NOT NULL;
select * from custmeremaillist;
select prod_id,
quantity,
item_price,
quantity*item_price AS expanded_price
from orderitems
where order_num = 20005;
将其转换为一个视图；
create view orderitemsexpanded AS
select order_num,
prod_id,
quantity,
item_price,
quantity*item_price AS expanded_price
from orderitems;
select * from orderitermsexpanded
where order_num = 2005;
事务处理(transactionprocessing)可以用来维护数据库的完整性，它保证成批的MySQL操作要么完全执行，要么完全不执行。
事务处理时一中机制，用来管理必须成批执行的MySQL操作，以保证数据库不包含不完整的操作结果。利用事务处理，可以保证一组操作不会中途停止，它们或者作为整体执行，或者完全不执行。如果没有错误发生，整组语句提交给数据库表。如果发生错误，则进行回退，
以恢复数据库到某个已知且安全的状态。
事务处理的几个术语：
事务(transaction)值一组SQL语句；
回退(rollback)指撤销指定SQL语句的过程;
提交(commit)指将未存储的SQL语句结果写入数据库表；
保留点(savepoint)指事务处理中设置的临时占位符(place-holder),可以对它发布回退(与回退整个事务处理不同)。
管理事务处理的关键在于将SQL语句组分解为逻辑块，并明确规定数据何时应该回退，何时不应该回退。
MySQL标识事务的开始：starttransaction
使用rollback(撤销MySQL语句)
select * from ordertotals;
start transaction;
delete from ordertotals;
select * from ordertotals;
rollback;
select * from ordertotals;
rollback只能在一个事务处理内使用(在执行一条starttransaction命令之后)。
使用commit
一般的MySQL语句都是直接针对数据库表执行和编写的。这就是所谓的隐含提交(implicit commit),即提交(写或保护)操作是自动进行的。
在事务处理块中，提交不会隐含地进行。为进行明确的提交，使用commit语句，如下所示：
start transaction;
delete from orderitems where order_num =20010;
delete from orders where order_num = 20010;
commit;