SQL常用语句+举例.doc

SQL常用语句+举例相关表：Store_information表Store_namesalesdateLos Angeles$1500Jan-05-1999San Diego$250Jan-07-1999Los Angeles$300Jan-08-1999Boston$700Jan-08-1999Geography表Region_nameStore_nameEastBostonEastNew YorkWestLOS AngelesWestSan Diego1. distinct: 剔除重复记录例：select distinct stroe_name from Store_informationStore_nameLos AngelesSan DiegoBoston结果：2. And / or: 并且/或 例：在表中选出所有sales 高于$1000或是sales在$275及$500之间的记录Select store_name ,sales from Store_information Where sales1000Or (sales275 and sales $1500store_namesum(sales)Los Angeles$1800结果：11. Alias :别名，字段别名和表格别名语法：select 字段名 AS 字段别名 from 表名 AS 表别名例：select store_name AS store from Store_information AS S1结果 ：storeLos AngelesSan DiegoLos AngelesBoston12. 左连接/内部连接例：查找每一区域的salesSelect A2.Region_name,A1.sales from Geography AS A2,Store_information AS A1Where A1.store_name=A2.store_nameGroup by A2.Region_nameregionsaleseast$700west$2050结果：13. 外部连接 : +例：我们需要查找每一个店的营业额，如果我们用一个普通连接，就会漏掉 new york这个店，这时我们要用外部连接Select A1.store_name ,sum(A2.sales) from eography AS A1,Store_information AS A2Where A1.store_name=A2.store_name(+)Group by A1.store_name14. Creat table:创建表Creat table custome(first_name char(50),Lat_name char(50),Birth_date date)15. Creat view：创建视图Creat view V_customeAS select first_name,last_name from custome16. Creat index：创建索引Creat index idx_custome_last_name on custome(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. 外键：指向另一个表主键的字段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_informationSet sales=$600Where store_name=lo