程序员常用SQL语句大全

SQL语句大全一、创建和删除数据库1、创建用户//创建用户且置密码，在MySQL中行，但在Oracle中行----必须在超级管理员身份下操作createuserhncuidentifiedby'1234'2、创建数据库//创建数据库并手动指定编码格式//错，因为根据手册的查询，数据库名应该在EXISTS后面CREATEDATABASEhncuIFNOTEXISTSDEFAULTCHARACTERSET'utf8';//改正：CREATEDATABASEIFNOTEXISTShncuDEFAULTCHARACTERSET'utf8';3、删除数据库DROPDATABASEmydb2;//删除数据库//查询(显示数据库)SHOWDATABASES;/*注：用Tab键补全代码，类似MyEclipse中的Alt+/*/二、数据库编码问题1、指定数据库编码方法1：可以在MySQL的配置文件如my.ini中指定：default-character-set=GBK或default-character-set=utf8方法2：在创建数据库时自己指定，如：CREATEDATABASEIFNOTEXISTShncuDEFAULTCHARACTERSET'utf8';相比较而言，对我们来讲，第2种方法更好。一是配置文件是平台，不能随意改，而且通常是由DBA来做的，我们没权设置，甚至MySQL都不在我们的电脑中。二是只要我们自己在创建数据库时自己指定编码，无论平台默认配置的是什么编码，对我们没有影响，一句话不依赖你的平台----可移置及兼容性好。2、查看数据库编码SHOWVARIABLES;//查询系统中的所有变量SHOWVARIABLESWHEREvariable_nameLIKE'character%';//查询系统中所有的编码方面的参数查询结果：character_set_clientutf8//客户端编码(不同的客户端显示的可能不一样，如cmd窗口和SQLyong)character_set_connectionutf8//客户端连接数据库时用的编码character_set_databasegbk//数据库的默认编码(一般来讲，这是由my.ini配置文件定的。这是没进数据库时的通用编码)character_set_filesystembinary//这是数据库自己存储数据文件时用的编码，跟我们关系不大character_set_resultsutf8//查询之后的结果集的编码character_set_servergbk//MySQL数据库服务器自己的编码iso8859-1在这里称Latin1character_set_systemutf8character_set_dirMySQL安装目录\share\charsets对我们来讲，为了不出现乱码，必须保证client和connection的编码一致，否则就会乱码。character_set_database数据库的编码必须要能够支持中文，否则输入中文有问题的3、设置（修改）数据编码//如何设置指定的编码setcharacter_set_client=gbk;SETcharacter_set_client=gbk;SHOWVARIABLESWHEREvariable_nameLIKE'character%';SELECT*FROMstud;//原来的数据显示正常INSERTINTOstudVALUES(1011,'城院',20,88,'数计学院');SELECT*FROMstud;//刚刚插入的那条记录，是乱码//因为我们这里client是gbk，而connection是utf8，不一致了SETcharacter_set_connection=gbk;//已经把client和connection设成gbk，一致了INSERTINTOstudVALUES(1012,'城院2',20,88,'数计学院');SELECT*FROMstud;//显示刚刚插入的那条记录，还是乱码。因为数据库表stud的编码是utf8，而我们客户端与它的连接都是gbk综上，client、connection、我们所访问的数据库的编码这三者都要一致且应该是支持中文的编码上面只能保证添加到数据库中的汉字不会出现乱码。如果读取出来，还要看results、所访问的数据库的编码和我们java代码中的解码是否一致。三、创建表格//创建表格CREATETABLEstud(idINTPRIMARYKEY,snameVARCHAR(30),ageINT);-----------------------------------------------------------------------varchar(20)----可变的char数组，类似Java当中的Stringchar(20)----固定长度的char数组//SQL语言全部忽略大小写----大小写不敏感-----------------------------------------------------------------------四、对表格操作USEhncu;SHOWTABLES;//查询(显示)表格DESCstud;//查看数据表student的表结构1、往数据表中插入数据INSERTINTOstudVALUES(1003,'Rose',22);//插入记录，当数据不全时，要指定列名INSERTINTOstud(id,sname,age,score)VALUES(1010,'李小明',25,90);//未指定列名的方式赋值时，必须要给全，默认值不给也不行INSERTINTOa(id,sname)VALUES(1,'Tom');//错：UNIQUE限定该列的值必须唯一(可以为，但最多只能有一个)INSERTINTOa(id,sname)VALUES(2,'Tom');//对，性别未赋，则用默认//性能优化：指定列名的方式性能更好！2、查询数据SELECT*FROMstud;//查询表中所有的数据SELECTsname,ageFROMstud;//只查询(显示)表中的指定列3、删除数据DELETEFROMstudWHEREage=30;//删除年龄为30的表记录4、更新数据UPDATEstudSETsname='杰克'WHEREsname='Jack';//更改数据5、更改表结构ALTERTABLE(添加一列)ALTERTABLEstudADDCOLUMNscoreNUMERIC(4,2);※※※※※建立联合主键※※※※※ALTERTABLEsjADDCONSTRAINTsj_pkPRIMARYKEY(studId,jectId);//添加外键约束1(为sj表的studId字段添加外键student(id)约束)ALTERTABLEsjADDCONSTRAINTsj_fk1FOREIGNKEY(studId)REFERENCESstudent(id);//添加外键约束2(为sj表的jectId字段添加外键ject(id)约束)ALTERTABLEsjADDCONSTRAINTsj_fk2FOREIGNKEY(jectId)REFERENCESject(id);6、创建视图CREATEVIEWstudViewASSELECT*FROMstudWHEREscore=60;SELECT*FROMstudview;五、对表查询操作中的那些事INSERTINTOstudVALUES(1004,'张三',38,60);INSERTINTOstudVALUES(1005,'王三',30,60);INSERTINTOstudVALUES(1006,'王五',30,60);INSERTINTOstudVALUES(1007,'王五六',30,80);※1、范围查询//查询年龄在24-26之间的学生信息1）连续区间内的查询SELECT*FROMstudWHEREage=24ANDage27;2）用BETWEEN是左右都包含，如下功能是[24,26]SELECT*FROMstudWHEREageBETWEEN24AND26;3）离散的多个SELECT*FROMstudWHEREage=24ORage=38;SELECT*FROMstudWHEREageIN(24,26,30);※2、模糊查询LIKE参数：%(任意匹配),_(匹配一个)//查询姓“王”的学生信息SELECT*FROMstudWHEREsnameLIKE'王%'//查询姓“王”且名为单字的学生信息SELECT*FROMstudWHEREsnameLIKE'王_';//查询姓“王”且名为双字的学生信息SELECT*FROMstudWHEREsnameLIKE'王__';//查询姓名中包含“五”字的学生信息SELECT*FROMstudWHEREsnameLIKE'%五%';※3、范围查询和模糊查询联合//查询姓名中包含“五”字且年龄大于30的学生信息SELECT*FROMstudWHEREsnameLIKE'%五%'ANDage30;※4、空值查询//查询无名英雄学生的信息(VARCHAR)SELECT*FROMstudWHEREsnameIS;//查询没有年龄信息的学生//错：SELECT*FROMstudWHEREage==;SELECT*FROMstudWHEREageIS;※5、聚合函数1）COUNT统计表格的行数SELECTCOUNT(*)ASTEMPTABLEFROMstud;//astemptable含义：就是将查询出的结果（表格的行数）另命名为：temptableSELECTCOUNT(1)ASTEMPTABLEFROMstud;//统计有年龄值的学生人数SELECTCOUNT(age)ASTEMPTABLEFROMstud;//统计有年龄值且有分数值的学生人数SELECTCOUNT(age)ASTEMPTABLEFROMstudWHEREscoreISNOT;2）AVG统计平均分且取整(注：AVG函数只统计非的数据记录)SELECTROUND(AVG(score))FROMstud;//这种方式一般不用，因为列名是自动生成的，我们在程序中不好访问SELECTROUND(AVG(score))ASaverageScoreFROMstud;3）SUM分数求和SELECTSUM(score)ASssFROMstud;4）MAX年龄最大值SELECTMAX(age)ASmaxAgeFROMstud;※6、WHERE子句+IN子句//查询年龄最小的那个人的名字SELECTsnameFROMstudWHEREage=(SELECTMIN(age)FROMstud);SELECTsnameFROMstudWHEREageIN(SELECTMIN(age)FROMstud);※7、排序SELECT*FROMstudGROUPBYageASC;//不重复排序(即年龄相同的，只显示第一个0SELECT*FROMstudGROUPBYageASC;//显示出所有年龄段SELECT*FROMstudORDERBYageASC;//普通排序--升序SELECT*FROMstudORDERBYageDESC;//普通排序--降序※8、distinct（不重复的值）SELECTDISTINCTsname,ageFROMstudGROUPBYageDESC;※9、EXISTS判断括号内的内容是否存在----注意，下面的例子，只要存在年龄为26的学生，就会输出所有数据SELECT*FROMstudWHEREEXISTS(SELECT*FROMstudWHEREage=26);※10、演示分组ALTERTABLEstudADDCOLUMNdeptVARCHAR(20);UPDATEstudSETdept='信息学院'WHEREscore=65;UPDATEstudSETdept='通信学院'WHEREscore=6