1、SQL 常用语句+举例相关表：Store_information 表Store_name sales dateLos Angeles $1500 Jan-05-1999San Diego $250 Jan-07-1999Los Angeles $300 Jan-08-1999Boston $700 Jan-08-1999Geography 表Region_name Store_nameEast BostonEast New YorkWest LOS AngelesWest San Diego1. distinct: 剔除重复记录例：select distinct stroe_name from
2、 Store_information结果： Store_nameLos AngelesSan DiegoBoston2. And / or: 并且/或例：在表中选出所有 sales 高于$1000 或是 sales 在$275 及$500 之间的记录Select store_name ,sales from Store_information Where sales1000Or (sales275 and sales $1500结果：11. Alias :别名，字段别名和表格别名语法：select 字段名 AS 字段别名 from 表名 AS 表别名3store_name sum(sales)
3、Los Angeles $1800例：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_name结果：13. 外部连接 : +例：我们需要查
4、找每一个店的营业额，如果我们用一个普通连接，就会漏掉 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:创建表region saleseast $700west $2050Creat table custome(first_name char(50),Lat_name char(50),Birth
5、_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 cha
6、nge 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=los angelesAnd date=jan-08-199923. Delete:删除记录Delete from stroe_information where store_name=los angeles
