SQL常用语句+举例.pdf

《SQL常用语句+举例.pdf》由会员分享，可在线阅读，更多相关《SQL常用语句+举例.pdf（10页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、SQL 常用语句+举例 相关表：Store_information 表 Store_name sales date Los Angeles$1500 Jan-05-1999 San Diego$250 Jan-07-1999 Los Angeles$300 Jan-08-1999 Boston$700 Jan-08-1999 Geography 表 Region_name Store_name East Boston East New York West LOS Angeles West San Diego 1.distinct:剔除重复记录 例：select distinct stroe_n

2、ame from Store_information 结果：2.And/or:并且/或 例：在表中选出所有 sales 高于$1000 或是 sales 在$275 及$500 之间的记录 Select store_name,sales from Store_information Where sales1000 Or(sales275 and sales$1500 结果：11.Alias:别名，字段别名和表格别名 store_name sum(sales)Los Angeles$1800 语法：select 字段名 AS 字段别名 from 表名 AS 表别名 例：select store_

3、name AS store from Store_information AS S1 结果：store Los Angeles San Diego Los Angeles Boston 12.左连接/内部连接 例：查找每一区域的 sales Select ,from Geography AS A2,Store_information AS A1 Where=Group by 结果：13.外部连接:+region sales east$700 west$2050 例：我们需要查找每一个店的营业额，如果我们用一个普通连接，就会漏掉 new york 这个店，这时我们要用外部连接 Select ,s

4、um from eography AS A1,Store_information AS A2 Where=(+)Group by 14.Creat table:创建表 Creat table custome(first_name char(50),Lat_name char(50),Birth_date date)15.Creat view：创建视图 Creat view V_custome AS select first_name,last_name from custome 16.Creat index：创建索引 Creat index idx_custome_last_name on c

5、ustome(last_name)17.Alter table:修改表 加一个字段:Alter table custome add gender char(2)删除一个字段：Alter table custome drop gender 改变字段名称：Alter table custome change first_name f_name char(50)改变字段类型：Alter table custome modify first_name char(30)18.Primary key:主键,不能为空 Alter table custome add primary key(sid)19.外键

6、：指向另一个表主键的字段 Creat table users(u_id int,U_name char(20)Primary key(u_id)Foreign key(u_name)references custome(sid)20.Truncate table 表名:清除表中数据 21.Insert into：向表中插入记录 Insert into stroe_information(store_name,sales,date)values(piny,$900,jan-10-1999)22.Update:更新记录 Update stroe_information Set sales=$600 Where store_name=los angeles And date=jan-08-1999 23.Delete:删除记录 Delete from stroe_information where store_name=los angeles