sql初学者笔记 语法基础

常见注释

• -- 很少支持

• 行内注释

• /**/段落注释

基础语法

SELECT

检索数据

语法	作用	例子	释义
select	查找列，并返回行	select prod_name from products； #可使用,分隔列名来查找多个列。	查找prod_name列，并返回其下的所有行，在products表中。
*	通配符	select * from products；	查找所有列并返回所有行，在products表中。
distinct	返回不重复的值	select distinct vend_id from products； #不可配合通配符使用除非所有列完全相同	查找vend_id列并返回其下所有行中不重复的值，在products表中。
limit	限制	select prod_name from products limit 5,5;	查找prod_name列并返回其下第5行起5行的值，在products表中。

排序检索数据

语法	作用	例子	释义
order by	排序	select prod_id,prod_price,prod_name from Products order by 2; #默认升序（ASC）排列 #指定按多个列排列时：仅当指定的第一列中有重复元素时，才对其（存在重复值的）按指定的下一列进行排序。	即按照查找的第二个列进行排序，也可指定列名（prod_price）
desc	降序	select prod_id,prod_price,prod_name from Products order by 2 DESC,3 desc;	即按照查找的第二个列进行降序排序，desc仅对其前的列有效；

过滤数据

语法	作用	例子	释义
where	在客户端过滤数据	select * from Products where prod_price >= 5.99 order by prod_price desc; #同排序操作一同使用时，不得位于排序操作之前#支持<>=!=等操作，其中<>操作等同于!= 例： select * from Products where prod_id <>'fc' order by prod_price desc; #过滤字符串不区分大小写	1.查找所有列，在Products表中，并返回prod_price >=5.99的所有行 2.查找所有列，在Products表中，并返回除prod_id = "fc"之外的所有行
between	值的范围过滤	select prod_name,prod_price from Products where prod_price between 4 and 10	查找prod_name,prod_price两列在Products表中，并返回prod_price值为4-10范围内的的的所有行
is	可用来检查null（空值）	select prod_name,prod_price from Products where prod_price is null	返回所有没有价格的商品
and,or	逻辑操作符 and且 or与，这里是短路的	select * from Products where vend_vend_id ='1001' and prod_price <=4; #and的优先级比or要高，and，or共同使用时为避免错误应用（）明确分组， #也可使用in代替or，例： select prod_name,prod_price from products where vend_id in('1001','1002') order by prod_name 等同于： select prod_name,prod_price from products where vend_id = '1001' or vend_id = '1002' order by prod_name	返回所有vend_vend_id ='1001' 且 prod_price <=4;的行
not	否定其后的条件	select prod_name,prod_price from products where not vend_id in('1001','1002') order by prod_name	可与in连用，返回vend_id=1001 vend_id=1002外的所有行

通配符搜索

语法	作用	例子
%	匹配0、1或多个字符包含空格。不会匹配到null	select prod_name from products where prod_name like 'f%%'
_	匹配单个字符，包含空格	select prod_name from products where prod_name like 'fuse_'
rtrim（）ltrim（）	去除右边、左边空格

创建计算字段

select prod_id ,quantity,item_price,quantity*item_price as expanded_pricefrom orderitemswhere order_num = 20008;#如上创建了一个expanded_price字段（quantity*item_price的结果的别名），其仅在此时有效而不会存放到表中。

使用函数

select vend_name, upper(vend_name) as vend_name_upcasefrom vendors#将vend_name列下的所有行以大写形式返回select avg(prod_price) as avg_peice from products where vend_id ='1001'#返回平均值select count(*) as num_cust from customers#返回长度（数目），也可对列表中特定值进行计数

分组

select vend_id,count(*) as num_prods from products #对vend_id每行进行计数group by vend_id;#按照vend_id排序并分组select cust_id,count(*) as ordersfrom orders group by cust_id having count(*)>=2#过滤分组中>=2的，having支持where的所有操作select order_num,count(*) as itemsfrom orderitems group by order_numhaving count(*) >=3order by items,order_num desc#对分组依照选定的列进行排序

子句查询

select cust_name,cust_contact from customers where cust_id =(select cust_id from orderswhere order_num = (select order_num from orderitems where prod_id = 'jp2000'));#由内而外，哈哈等效于：select order_num from orderitemswhere prod_id = 'jp2000'；select cust_id from orderswhere order_num =20006select cust_name,cust_contact from customers where cust_id =10003

联结（返回不在同一个表中的行）

/*等值语法*/select vend_name, prod_name,prod_pricefrom vendors,productswhere vendors.vend_id=products.vend_id;#此处过滤联结条件。#如没有联结条件过滤，将检索出"笛卡尔积"：表1行数*表2行数/*规范语法*/select vend_name, prod_name,prod_pricefrom vendors inner join productson vendors.vend_id=products.vend_id

自联结（比子查询更快）

/*子查询*/select cust_id, cust_name, cust_contactfrom customerswhere cust_name = (select cust_name from customers where cust_contact ='jim jones');/*自联结*/select c1.cust_id, c1.cust_name,c1.cust_contactfrom customers as c1,customers as c2#不以别名进行会引发错误where c1.cust_name=c2.cust_name and c2.cust_contact='jim jones'#联结cust_name与c2.cust_name ，并过滤cust_contact='jim jones'的行/*（c1的cust_name同c2相同，找到了c2的cust_contact='jim jones'也就相当于找到了c1cust_contact='jim jones'，知道了cust_contact='jim jones'就可知道cust_id）*/

组合查询

select cust_name,cust_contact,cust_email,cust_statefrom customerswhere cust_state in('il','in','mi')union #组合上下select多个select之间需要多个union分隔，union默认排除重复，union all则不排除select cust_name,cust_contact,cust_email,cust_state#union中每个查询必须包含相同的列、表达式、或聚集函数from customerswhere cust_name ='wascals'order by cust_name;#不能分别对每条union指定不同的排序

INSERT

依赖于次序的插入

/*在得知列的次序后才可使用此方式添加，若发生了列的次序变动此添加方式将不安全*/insert into customersvalues('1000000006','toy land','123 any street','new york','ny', '11111','usa',null, null);#依赖于次序的插入，必须为每一列提供一个值，如某列无添加则应写上null

提供列名的插入

insert into customers(cust_id,cust_contact,cust_email,cust_name,cust_address,cust_city,cust_state,cust_zip)#必须为提供了列名的列给出一个值values(null, null,'1000000006','toy land','123 any street','new york','ny', '11111');

从另一个表插入

insert into 表名（列名）select 列名from 表名where 过滤

复制一个表

/*sql*/select * into custcopy from customers;/* mysql*/create table custcopy asselect * from customers;

UPDATE

更新单个列

update customersset cust_email = 'kim@@thetoystore.com'where cust_id = '100000000005'#如不指定，将更新customers表cust_email列下的所有行

更新多个列

update customersset cust_email = 'kim@@thetoystore.com',cust_contact='sam roberts'where cust_id = '100000000006'

DELLETE

delete from customerswhere cust_id = '1000000006'#删除此行，不过滤则删除所有行#update删除列#truncate删除表

添加删除列&&表

添加表

/*添加表时为防止覆盖，应删除表后再进行添加*/create table orderitems(order_num integer not null,order_item integer not null,prod_id char(10) not null,quantity integer not null default 1,#设置quantity列下的行默认值为1item_price decimal(8,2) not null);#not null即不允许填入null，默认可填入null，只有为 not null的列方可为主键及唯一标识

/*add列*/alter table vendorsadd vend_phone char (20);/*del列*/alter table vendorsdrop column vend_phone;#此操作不可逆/*删除表*/drop table custcopy;#此操作不可逆

视图

create view#创建视图drop view 视图名#删除视图/*以视图简化联结，创建视图*/create view productcustomers asselect cust_name , cust_contact,prod_idfrom customers,orders,orderitemswhere customers.cust_id=orders.cust_idand orderitems.order_num=orders.order_num;/*可对视图采取与表相同的查询操作*/select *from productcustomers;/*一个视图过滤查询例子*/create view customeremaillist asselect cust_id, cust_name,cust_emailfrom customerswhere cust_email is not null;#返回查询中所有cust_email不为空的，并将其添加到视图中/*视图计算字段例子*/create view orderitemsexpanded as select order_num,prod_id,quantity,item_price,quantity*item_price,quantity*item_price asexpanded_pricefrom orderitems

事务管理

/*撤销整体*/start transaction ;-- 标识事务处理块,块中内容未执行完则整体撤销/*撤销部分操作*/savepoint delete1;#标识rollback to delete1;返回标识delete1

sql初学者笔记 语法基础