常用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 view viewname;来查看创建视图的语句。

3删除视图,dorp view viewname;

4更新视图,先drop,再create,也可以直接用create or 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;

 

事务处理(transaction processing)可以用来维护数据库的完整性,它保证成批的MySQL操作要么完全执行,要么完全不执行。

事务处理时一中机制,用来管理必须成批执行的MySQL操作,以保证数据库不包含不完整的操作结果。利用事务处理,可以保证一组操作不会中途停止,它们或者作为整体执行,或者完全不执行。如果没有错误发生,整组语句提交给数据库表。如果发生错误,则进行回退,

以恢复数据库到某个已知且安全的状态。

事务处理的几个术语:

事务(transaction)值一组SQL语句;

回退(rollback)指撤销指定SQL语句的过程;

提交(commit)指将未存储的SQL语句结果写入数据库表;

保留点(savepoint)指事务处理中设置的临时占位符(place-holder),可以对它发布回退(与回退整个事务处理不同)。

管理事务处理的关键在于将SQL语句组分解为逻辑块,并明确规定数据何时应该回退,何时不应该回退。

MySQL标识事务的开始:start transaction

使用rollback(撤销MySQL语句)

select * from ordertotals;

start transaction;

delete from ordertotals;

select * from ordertotals;

rollback;

select * from ordertotals;

rollback只能在一个事务处理内使用(在执行一条start transaction命令之后)。

使用commit

一般的MySQL语句都是直接针对数据库表执行和编写的。这就是所谓的隐含提交(implicit commit),即提交(写或保护)操作是自动进行的。

在事务处理块中,提交不会隐含地进行。为进行明确的提交,使用commit语句,如下所示:

start transaction;

delete from orderitems where order_num = 20010;

delete from orders where order_num = 20010;

commit;

常用SQL操作(MySQL或PostgreSQL)与相关数据库概念,布布扣,bubuko.com

常用SQL操作(MySQL或PostgreSQL)与相关数据库概念

上一篇:Navicat MySQL连接Linux下MySQL的问题解决方案


下一篇:Risk Adaptive Information Flow Based Access Control