SQL常用语句+举例

1、SQL常用语句+举例相关表:Storenformatio n表Store_ namesalesdateLos An geles$1500Ja n-05-1999San Diego$250Ja n-07-1999Los An geles$300Ja n-08-1999Bost on$700Ja n-08-1999Geography 表Regi on_n ameStore _n ameEastBost onEastNew YorkWestLOS An gelesWestSan Diego1. dist inct:剔除重复记录例: select distinet stroe_name from St

2、ore_information结果:Store_ nameLos An gelesSan DiegoBost on2. And / or:并且 / 或例：在表中选出所有 sales 高于$1000或是sales在$275及$500之间的记录Select store_ name ,sales from Store_ in formati onWhere sales1000Or (sales275 and sales $1500结果:store_ namesum(sales)Los An geles$180011. Alias :别名，字段别名和表格别名语法：select 字段名AS字段别名fro

3、m 表名AS表别名 例：select store_name AS store from Storenformation AS S1结果12. 左连接/内部连接例：查找每一区域的 salesSelect , from Geography AS A2,Store_i nformation AS A1Where =Group byregi onsaleseast$700west$2050果:13. 外部连接:+店，这时我们要用外部连接Select ,sum from eography AS A1,Store_ in formatio n AS A2Where =(+)Group by14. Crea

4、t 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 in dex idx_customeast_ name on custome(last_ name)17. Alter table:修改表加一个字段：Alter table custome add ge

5、nder char(2)删除一个字段:Alter table customedrop gender改变字段名称:Alter table customechange first_name f_name char(50)改变字段类型:Alter table customemodify first_ name char(30)18. Primary key: 主键，不能为空Alter table custome add primary key(sid)19. 外键：指向另一个表主键的字段Creat table users(ud int,U_n ame char(20)Primary key(u_id)Foreig n key( u_n ame) refere nces custome(sid)20. Truncate table 表名：清除表中数据21. Insert into:向表中插入记录Insertintostroe_ in formati on(store_ name,sales,date)values(pi ny , $900 , ja n-10-1999 )22. Update:更新记录Update stroe_ in formati onSet sales=$600And date