SQL知识点汇总

select name,sal*12 annual_sal

from emp;

显示的时候会变成annual_sal，若要保持原来的形式可加双引号。

 

标准：sql关键字使用大写，列名和表名使用小写

 

检索

select 列名from 表名

select * from 表名

select distinct 列名 from 表名     只返回不同（具有唯一性）的行

 

select 列名 from 表名

limit 5；         【指示mysql返回不超过5行的数据】

limit 5 offset 5；  【从第5行起的5行数据】

 

注释

行内注释

【1】   selectprod_name  --这是一条行内注释

from products；

【2】   #这是一条行内注释

select prod_name

from products；

 

排序

select 列名

from 表名

order by 列名

注【order by 应该是select语句中最后一条字句，否则会出现错误信息】

 

指定排序方向

asc  升序

desc 降序

 

过滤数据where

select prod_name, prod_price

from products

where prod_price =3.49

 

 

高级过滤数据

操作符：用来联结或改变where字句中的字句的关键字，也称为逻辑操作符

 

操作符

AND

        Select prod_id, prod_price, prod_name

        From products

        Where vend_id = ‘DLL01’ AND prod_price<= 4;

OR

        Select prod_name, prod_price

        From products

        Where vend_id = ‘DLL01’ OR vend_id = ‘BRS01’;

IN : IN操作符用来指定条件范围，范围中的每个条件都可以进行匹配。

   IN取一组有逗号分隔，括在圆括号中的合法值。

       

        Select prod_name, prod_price

        From products

        Where vend_id IN (‘DLL01’, ‘BRS01’)

        Order by prod_name;

NOT : where字句中的NOT操作符有且只有一个功能，

那就是否定其后所跟的任何条件。

        Select prod_name

        From Products

        Where NOT vend_id  =  ‘DLL01’

        Order by prod_name;

 

用通配符进行过滤

LIKE操作符

1.     百分号%通配符

Select prod_id, prod_name

From products

Where prod_name  LIKE  ‘Fish%’;  --检索任意以Fish开头的字符。

2.     Whereprod_name  LIKE  ‘%bean bag%’; 

表示匹配任何位置上包含文本bean bag的值。  

3.     Whereprod_name  LIKE  ‘F%y’;

表示以F开头y结尾的字符。

4.     Whereprod_name LIKE ‘%’；

可以匹配任何东西，除了NULL。

 

下划线 _  :只匹配单个字符，而不是多个字符

Select prod_id, prod_name

From products

Where prod_name  LIKE  ‘_Fish’;   --aFish等字符

 

方括号 [] 通配符

用来指定一个字符集，它必须匹配指定位置（通配符的位置）的一个字符。

例如：找出所有名字以J或M起头的联系人。

Select name

From customers

Where name LIKE ‘[JM]%’

Order bycust_contact;

 

输出：

name

-----------------------

Jim Jones

John Smith

Michelle Green

 

匹配不以J或M起头的任意联系人名

Where name LIKE ‘[^JM]%’

 

 

拼接字段

将值联结到一起（将一个值附加到另一个值）构成单个值。

Select  Concat(vend_name,  ‘ (‘, vend_country,  ‘) ‘ )

From vendors

Order byvend_name;

分析：

上面的语句拼接以下元素：

存储在vend_name列中的名字

包含一个空格和一个左圆括号的字符串

存储在vend_ country列中的国家。

包含一个右圆括号的字符串

 

使用数据处理函数

1.     文本处理函数

UPPER() : 将字符串转换为大写

LOWER() ： 将字符串转换为小写

LTRIM() ： 去掉字符串左边的空格

RTRIM() ：去掉字符串右边的空格

例：select vend_name, UPPER(vend_name) ASvend_name_upcase

        From vendors

        Order by vend_name;

 

2.     日期和时间处理函数

Oracle中

to_char()函数用来提取日期的成分

to_number()用来将提取的成分转化为数值

to_date()函数将字符串转化为日期

例：

select order_num

From orders

Whereto_number(to_char(order_date,’YYYY’)) = 2012;

 

Select order_num

From orders

Where order_date between to_date(’01-01-2012’)

And to_date(’12-31-2012’);

 

Mysql中

YEAR()函数用来提取年份

Where YEAR(order_date)  = 2012;

 

3.     数值处理函数

数值处理函数仅处理数值函数

ABS()绝对值

COS()余弦

SIN()正弦

TAN()正切

SQRT()平方根

PI()圆周率

EXP()返回一个数的指数值

汇总数据

1.     聚集函数 ：对某些行 运行的函数，计算并返回一个值。

COUNT()  : 返回某列的行数

MAX()    : 返回某列的最大值
MIN()    : 返回某列的最小值

AVG()    : 返回某列的平均值

SUM()    : 返回某列值之和

 

AVG()

Select AVG(prod_price)as avg_price

From products;

 

COUNT

1.     COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值（NULL）还是非空值。

2.     COUNT(column)对特定列中的具有的值进行计数，忽略NULL值

Select COUNT(cust_email) asnum_cust

From customers;

 

输出：

num_cust

3

 

这条select语句使用COUNT(cust_email)对cust_mail列中有值的行进行计数。

 

聚集不同值

1.     对所有的行执行计算，指定ALL参数或不指定参数

2.     值包含不同的值，指定distinct参数

 

Select AVG(DISTINCT prod_price) asavg_price   --一样的价格只取一个

From products

Where vend_id = ‘DLL01’;

 

 

 

分组数据

Select vend_id,COUNT(*) as num_prods

From products

Where prod_price>= 4

Group byvend_id;        --创建分组

Having COUNT(*)>= 2;     --过滤分组

 

联结表

联结：在数据查询的执行中联结（join）表

 

内联结

等值联结是基于两个表之间的相等测试。这种联结也称为内联结。

Select vend_name,prod_name, prod_price

From vendors INNERJOIN 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;

 

 

创建高级联结

使用表别名

Selectcust_name,cust_contact

From customers asc, orders as o, orderitems as oi

Where c.cust_id =o.cust_id

And oi.order_num =o.order_num

And prod_id = ‘RGAN01’;

 

使用不同类型的联结

1.内联结（等值联结）

2.自联结

3.自然联结

4.外联结

插入数据

Insert into table(列名1，列名2，…) values(v1,v2,…);

 

从一个表复制到另外一个表

Create tableCustCopy As

Select * fromCustomers;    --任意筛选数据都行

 

更新和删除数据

2中使用update的方式：

1.     更新表中的特定行

2.     更新表中的所有行

 

基本的update语句由3部分组成，

分别是：要更新的表、列名和它们的新值、确定要更新哪些行的过滤条件

Update customers

Setcust_email = ‘[e-mail]’

Where cust_id = ‘100000005’;

 

删除数据

Delete from customers

Where cust_id = ‘100000006’;

 

创建表

CREATE TABLEPersons

(

Id_P int NOT NULL,

LastNamevarchar(255) NOT NULL,

FirstNamevarchar(255),

Addressvarchar(255),

City varchar(255),

PRIMARY KEY (Id_P)

)

 

 

ALTER TABLE语句

ALTER TABLE 语句用于在已有的表中添加、修改或删除列。

SQL ALTER TABLE 语法

如需在表中添加列，请使用下列语法:

ALTER TABLEtable_name

ADD column_namedatatype

要删除表中的列，请使用下列语法：

ALTER TABLEtable_name

DROP COLUMNcolumn_name

 

使用视图

视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。

使用视图的原因：

1.     重用SQL语句。

2.     简化复杂的SQL操作。在编写查询后，可以方便地重用它而不必知道其基本的查询细节。

3.     使用表的一部分而不是整个表

4.     保护数据。可以授予用户访问表的特定部分的权限，而不是整个表的访问权限。

5.     更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

 

创建视图CREATE VIEW

CREATE VIEW ProductCustomers AS

SELECT cust_name, cust_contact,prod_id

FROM Customers,Orders,OrderItems

WHERECustomers.cust_id = Orders.cust_id

ANDOrderItems.order_num = Oders.order_num;

 

使用存储过程

存储过程： 为以后使用而保存的一条或多条SQL语句。

 

事务处理

使用事务处理，通过确保成批的SQL操作要么完全执行，要么完全不执行，来维护数据库的完整性。

事务（transaction）  ： 指一组SQl语句

回退（rollback）： 指撤销指定SQL语句的过程

提交（commit）： 指将未存储的SQL语句结果写入数据库表

保留点（savepoint）： 指事务处理中设置的临时占位符，可以对它发布回退（与回退整个事务处理不同）。

 

使用游标

1.     游标

游标cursor是一个存储在DBMS服务器上的数据库查询，它不是一条select语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。

DECLARE CustCursor CURSOR

FOR

SELECT * FROMCustomers

WHERE cust_emailIS NULL;

 

 

约束（constraint）： 管理如何插入或处理数据库数据的规则。

1.     主键

主键是一种特殊的约束，用来保证一列（或一组列）中的值是唯一的，而且永不改动。即表中的一列的值唯一标识表中的每一行。

 

添加主键

ALTER TABLEVendors

ADD CONSTRAINTPRIMARY KEY (vend_id);

 

2.     外键

外键是表中的一列，其值必须列在另一表的主键中。外键是保证引用完整性的及其重要部分。

 

添加外键

ALTER TABLE Orders

ADD CONSTRAINT

FOREIGN KEY(cust_id)  REFERENCES  Customers (cust_id);

 

3.     唯一约束UNIQUE

4.     检查约束

检查约束在数据类型内又做了进一步的限制，可以确保插入数据库的数据正是你想要的数据。

ADD CONSTRAINTCHECK (gender LIKE ‘[MF]’);

 

索引

用来排序数据以加快搜索的排序操作的速度。

在一个或多个列上定义索引，使DBMS保存其内容的一个排过序的列表。

 

创建索引

CREATE INDEXprod_name_ind

ON Products（prod_name）;

创建一个叫prod_name_ind的索引在表Products的prod_name列上。

 

 

触发器

触发器是特殊的存储过程，它在特定的数据库活动发生时自动执行。

触发器可以与特定表上的INSERT、UPDATE、DELETE操作（或组合）相关联。

触发器与单个表相关联。

 

例（SQL Server）

Create triggercustomer_state

On customers

For insert, update

As

Update customers

Setcust_state  =  upper(cust_state)

Wherecustomers.cust_id = inserted.cust_id;