SQL语句大全
一、创建和删除数据库1、创建用户//创建用户且置密码，在MySQL中行，但在Oracle中行----必须在超级管理员身份下操作
createuserhncuidentifiedby
2、创建数据库//创建数据库并手动指定编码格式//错，因为根据手册的查询，数据库名应该在EXISTS后面CREATEDATABASEhncuIFNOTEXISTSDEFAULTCHARACTERSETutf8;//改正：CREATEDATABASEIFNOTEXISTShncuDEFAULTCHARACTERSETutf8;
3、删除数据库DROPDATABASEmydb2;//删除数据库//查询(显示数据库)SHOWDATABASES;/*注：用Tab键补全代码，类似MyEclipse中的Alt+/*/
二、数据库编码问题1、指定数据库编码方法1：可以在MySQL的配置文件如my.ini中指定：
default-character-set=GBK或
default-character-set=utf8
方法2：在创建数据库时自己指定，如：
CREATEDATABASEIFNOTEXISTShncuDEFAULTCHARACTERSETutf8;
▲相比较而言，对我们来讲，第2种方法更好。一是配置文件是平台，不能随意改，而且通常是由DBA来做的，我们没权设置，甚至MySQL都不在我们的电脑中。二是只
要我们自己在创建数据库时自己指定编码，无论平台默认配置的是什么编码，对我们没有影响，一句话不依赖你的平台----可移置及兼容性好。
2、查看数据库编码SHOWVARIABLES;//查询系统中的所有变量
SHOWVARIABLESWHEREvariable_nameLIKEcharacter%;//查询系统中所有的编码方面的参数
查询结果：
character_set_clientutf8//客户端编码(不同的客户端显示的可能不一样，如cmd窗口和SQLyong)
character_set_connectionutf8//客户端连接数据库时用的编码
character_set_databasegbk//数据库的默认编码(一般来讲，这是由my.ini配置文件定的。这是没进数据库时的通用编码)
character_set_filesystembinary//这是数据库自己存储数据文件时用的编码，跟我们关系不大
character_set_resultsutf8//查询之后的结果集的编码
character_set_servergbk//MySQL数据库服务器自己的编码iso-1在这里称Latin1
character_set_systemutf8
character_set_dirMySQL安装目录\share\charsets
对我们来讲，为了不出现乱码，必须保证client和connection的编码一致，否则就会乱码。
character_set_database数据库的编码必须要能够支持中文，否则输入中文有问题的
3、设置（修改）数据编码//如何设置指定的编码
setcharacter_set_client=gbk;
SETcharacter_set_client=gbk;
SHOWVARIABLESWHEREvariable_nameLIKEcharacter%;
SELECT*FROMstud;//原来的数据显示正常
INSERTINTOstudVALUES(,城院,20,88,数计学院);
SELECT*FROMstud;//刚刚插入的那条记录，是乱码//因为我们这里client是gbk，而connection是utf8，不一致了
SETcharacter_set_connection=gbk;//已经把client和connection设成gbk，一致了
INSERTINTOstudVALUES(,城院2,20,88,数计学院);
SELECT*FROMstud;//显示刚刚插入的那条记录，还是乱码。因为数据库表stud的编码是utf8，而我们客户端与它的连接都是gbk▲综上，client、connection、我们所访问的数据库的编码这三者都要一致且应该是支持中文的编码
上面只能保证添加到数据库中的汉字不会出现乱码。
▲如果读取出来，还要看results、所访问的数据库的编码和我们java代码中的解码是否一致。
三、创建表格//创建表格
CREATETABLEstud(
idINTPRIMARYKEY,
snameVARCHAR(30),
ageINT);
-----------------------------------------------------------------------varchar(20)----可变的char数组，类似Java当中的String
char(20)----固定长度的char数组
//SQL语言全部忽略大小写----大小写不敏感
-----------------------------------------------------------------------
四、对表格操作USEhncu;
SHOWTABLES;//查询(显示)表格
DESCstud;//查看数据表student的表结构
1、往数据表中插入数据INSERTINTOstudVALUES(,Rose,22);
//插入记录，当数据不全时，要指定列名
INSERTINTOstud(id,sname,age,score)VALUES(,李小明,25,90);//未指定列名的方式赋值时，必须要给全，默认值不给也不行
INSERTINTOa(id,sname)VALUES(1,Tom);//错：UNIQUE限定该列的值必须 (可以为NULL，但最多只能有一个)
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(,张三,38,60);
INSERTINTOstudVALUES(,王三,30,60);
INSERTINTOstudVALUES(,王五,30,60);
INSERTINTOstudVALUES(,王五六,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*FROMstudWHEREsnameISNULL;
//查询没有年龄信息的学生
//错：SELECT*FROMstudWHEREage==NULL;
SELECT*FROMstudWHEREageISNULL;
※5、聚合函数1）COUNT统计表格的行数SELECTCOUNT(*)ASTEMPTABLEFROMstud;//astemptable含义：就是将查询出的结果（表格的行数）另命名为：temptable
SELECTCOUNT(1)ASTEMPTABLEFROMstud;
//统计有年龄值的学生人数
SELECTCOUNT(age)ASTEMPTABLEFROMstud;
//统计有年龄值且有分数值的学生人数
SELECTCOUNT(age)ASTEMPTABLEFROMstudWHEREscoreISNOTNULL;
2）AVG统计平均分且取整(注：AVG函数只统计非NULL的数据记录)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;//不重复排序(即年龄相同的，只显示 个0
SELECT*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=60;
UPDATEstudSETdept=土木学院WHEREscore60;
SELECT*FROMstud;
//分组计算
//按学院计算平均分（每个学员的平均分）
SELECTdept,AVG(score)AS学院平均分FROMstudGROUPBYdept;
※11、字符串处理函数SELECT*FROMstudWHEREsname=AAA;
SELECT*FROMstudWHERETRIM(sname)=AAA;//去掉左右的空格
SELECT*FROMstudWHERELTRIM(RTRIM(sname))=AAA;//和上面等价
UPDATEstudSETdept=数计学院WHEREid=;
SELECTLEFT(TRIM(sname),2)FROMstud;//取去掉空格后的左起2个字符
SELECTREVERSE(TRIM(sname))FROMstud;CREATETABLEperson(idINT,snameVARCHAR(30),ageINT);ALTERTABLEpersonADDCONSTRAINTperson_pkPRIMARYKEY(id);//更灵活
DROPTABLEperson;
CREATETABLEperson(
idINTPRIMARYKEY,
snameVARCHAR(30)NOTNULL,
ageINT
);//用NOTNULL限制非空输入
※12、性别字段（例如：数据库存0、1而显示出来为男、女）一般不定义成BOOLEAN型，因为有的数据库不支持，为考虑兼容，通常用CHAR(1)
CREATETABLEa(
idINTUNIQUE,
snameVARCHAR(10),
sexCHAR(1)DEFAULT0
);
//显示性别(真实值与显示值之间的转换)
SELECT*FROMa;
SELECTid,sname,(CASEsexWHEN0THEN女WHEN1THEN男ELSENullEND)xbFROMa;
SELECTid,sname,(CASEsexWHEN0THEN女WHEN1THEN男ELSENullEND)ASxbFROMa;
SELECTid,sname,(CASEWHENsex=0THEN女WHENsex=1THEN男ELSENullEND)ASxbFROMa;
※13、无关子查询//需求：具有同龄人的学生
SELECT*FROMstud;
SELECT*FROMstudWHEREageIN(SELECTageFROMstudGROUPBYageHAVINGCOUNT(age)=2)ORDERBYageDESC;
//练练别名
SELECT*FROMstudASxsWHEREageIN(SELECTageFROMxsGROUPBYageHAVINGCOUNT(age)=2)ORDERBYageDESC;
//需求2：不但具有同龄人，而且年龄大于等于30的学生
//法1
SELECT*FROMstudWHEREageIN(SELECTageFROMstudGROUPBYageHAVINGCOUNT(age)=2ANDage=30)ORDERBYageDESC;
//法2
SELECT*FROMstudWHEREage=30ANDageIN(SELECTageFROMstudGROUPBYageHAVINGCOUNT(age)=2)ORDERBYageDESC;
※14、固定搭配SELECT*FROM+WHERE+ORDERBY(要放在 )
GROUPBY+HAVING
※15、关系查询-----------------------------------------DROPTABLEperson;
CREATETABLEperson(
idINT,
NAMEVARCHAR(10),
sexCHAR(1),
wifeINT,
husbandINT
);
INSERTINTOpersonVALUES(1,小花,0,0,3);
INSERTINTOpersonVALUES(2,玉芬,0,0,4);
INSERTINTOpersonVALUES(3,张三,1,1,0);
INSERTINTOpersonVALUES(4,李四,1,2,0);
INSERTINTOpersonVALUES(5,王五,1,0,0);
1）一对一关系的操作：查出每对夫妻的姓名CREATEVIEWwASSELECT*FROMpersonWHEREsex=0;
CREATEVIEWmASSELECT*FROMpersonWHEREsex=1;
//不利用表与表之间的关系
SELECTw.NAMEAS妻子,m.NAMEAS丈夫FROMw,mWHEREw.husband=m.idANDm.wife=w.id;
//现在更先进的方式：利用表间的关系
SELECTw.NAMEAS妻子,m.NAMEAS丈夫FROMwINNERJOINmONw.husband=m.idANDm.wife=w.id;
SELECT*FROMperson;
2）一对多的关系代码演示//步骤1：画E-R图
//步骤2:分别建实体表，并给多方的表添加外键约束
CREATETABLEperson2(
idVARCHAR(32)PRIMARYKEY,
pnameVARCHAR(30),
sexCHAR(1)
);
CREATETABLEcar(
idVARCHAR(32)PRIMARYKEY,
cnameVARCHAR(30),
priceNUMERIC(10,2),
pidVARCHAR(32),
CONSTRAINTcar_fkFOREIGNKEY(pid)REFERENCESperson2(id)
);
DROPTABLEcar;
//步骤3:为两个表添加测试数据
//实体表1
INSERTINTOperson2(id,pname,sex)VALUES(P,Jack,1);
INSERTINTOperson2(id,pname,sex)VALUES(P,Tom,1);
INSERTINTOperson2(id,pname,sex)VALUES(P,Rose,0);
INSERTINTOperson2(id,pname,sex)VALUES(P,Mary,0);
INSERTINTOperson2(id,pname,sex)VALUES(P,Mike,1);
SELECT*FROMperson2;
////实体表2
INSERTINTOcar(id,cname,price,pid)VALUES(C,BMW,.5,P);
INSERTINTOcar(id,cname,price,pid)VALUES(C,Benz,.5,P);
INSERTINTOcar(id,cname,price,pid)VALUES(C,BMW,.5,P);
INSERTINTOcar(id,cname,price,pid)VALUES(C,BMW,83.5,P);
INSERTINTOcar(id,cname,price,pid)VALUES(C,Benz,,P);
INSERTINTOcar(id,cname,price,pid)VALUES(C,Audi,.5,P);
INSERTINTOcar(id,cname,price,pid)VALUES(C,BMW,88.5,P);
INSERTINTOcar(id,cname,price,pid)VALUES(C,QQ,10,P);
INSERTINTOcar(id,cname,price,pid)VALUES(C,Audi,73,P);
INSERTINTOcar(id,cname,price)VALUES(C,Audi,0);
//该句代码执行错误，因为编号为P的人在Person2表中不存在，这就是参照完整性
INSERTINTOcar(id,cname,price,pid)VALUES(C,Audi,0,P);SELECT*FROMcar;
//查询：哪些人有什么样的车(用表名.列名的形式访问列，如果列名不重复，可以省略表名)
//利用一方的主键和“多方”的外键进行关联
SELECTperson2.pname,car.cnameFROMperson2,carWHEREperson2.id=car.pid;
//查询Jack有什么车
SELECTperson2.pname,car.cnameFROMperson2,carWHEREperson2.id=car.pidANDperson2.pname=Jack;
//查询哪些人有两辆以上的车
SELECTperson2.pname,COUNT(pname)AS车数量FROMperson2,carWHEREperson2.id=car.pidGROUPBYpnameHAVINGCOUNT(pname)=2ORDERBY车
数量;
SELECT*FROMperson2WHEREidIN(SELECTpidFROMcarGROUPBYpidHAVINGCOUNT(pid)=2);
※16、关联查询//查询哪些人没有车
SELECT*FROMperson2WHEREidNOTIN(SELECTpidFROMcar);
//用左关联(LEFTJOIN)来查询：哪些人有什么样的车(没车的也是一种情况，要显示)
SELECTperson2.pname,car.cname,car.priceFROMperson2LEFTJOINcarONperson2.id=car.pidORDERBYperson2.id;
//用内关联(INNERJOIN)来查询：哪些人有什么样的车(没车的不显示)
SELECTperson2.pname,car.cname,car.priceFROMperson2INNERJOINcarONperson2.id=car.pidORDERBYperson2.id;
//查询每辆车的销售情况(如果有主人就显示，没有则显示NULL)
SELECTperson2.pname,car.cname,car.priceFROMperson2RIGHTJOINcarONperson2.id=car.pidORDERBYperson2.id;
（+在左边时右关联，+右边时左关联）
--------------------------------------------------DELETEFROMperson2WHEREid=P;
CREATETABLEstudent(
idVARCHAR(32)PRIMARYKEY,
NAMEVARCHAR(30),
ageINT
);
CREATETABLEject(
idVARCHAR(32)PRIMARYKEY,
NAMEVARCHAR(30),
priceNUMERIC(5,2)
);
CREATETABLEsj(
studIdVARCHAR(32)NOTNULL,
jectIdVARCHAR(32)
);
//建立联合主键
ALTERTABLEsjADDCONSTRAINTsj_pkPRIMARYKEY(studId,jectId);
//添加外键约束1(为sj表的studId字段添加外键student(id)约束)
ALTERTABLEsjADDCONSTRAINTsj_fk1FOREIGNKEY(studId)REFERENCESstudent(id);
//添加外键约束2(为sj表的jectId字段添加外键ject(id)约束)
ALTERTABLEsjADDCONSTRAINTsj_fk2FOREIGNKEY(jectId)REFERENCESject(id);
//添加测试数据
//学生表
INSERTINTOstudent(id,NAME,age)VALUES(S,Jack,25);
INSERTINTOstudent(id,NAME,age)VALUES(S,Tom,24);
INSERTINTOstudent(id,NAME,age)VALUES(S,张三,23);
INSERTINTOstudent(id,NAME,age)VALUES(S,李四,24);
INSERTINTOstudent(id,NAME,age)VALUES(S,Rose,25);
SELECT*FROMstudent;
//课程表
INSERTINTOject(id,NAME,price)VALUES(J,Java,25);
INSERTINTOject(id,NAME,price)VALUES(J,MySQL,30);
INSERTINTOject(id,NAME,price)VALUES(J,Oracle,55.9);
INSERTINTOject(id,NAME,price)VALUES(J,软件工程,20.25);
INSERTINTOject(id,NAME,price)VALUES(J,WEB开发,);
SELECT*FROMject;
//选课表
INSERTINTOsj(studId,jectId)VALUES(S,J);
INSERTINTOsj(studId,jectId)VALUES(S,J);
INSERTINTOsj(studId,jectId)VALUES(S,J);
INSERTINTOsj(studId,jectId)VALUES(S,J);
INSERTINTOsj(studId,jectId)VALUES(S,J);
INSERTINTOsj(studId,jectId)VALUES(S,J);
INSERTINTOsj(studId,jectId)VALUES(S,J);
INSERTINTOsj(studId,jectId)VALUES(S,J);
INSERTINTOsj(studId,jectId)VALUES(S,J);
SELECT*FROMsj;
应用测试
//查询哪些人选了哪些课----要求显示：人名，课程名
//采用的是92标准
SELECTstudent.name,ject.NAMEFROMstudent,ject,sjWHEREstudent.id=sj.studIdANDsj.jectId=ject.id;
//采用96标准
SELECTstudent.name,ject.NAMEFROMstudent
INNERJOINsjONstudent.id=sj.studId
INNERJOINjectONsj.jectId=ject.id;
//查询哪些人没有选课(左关联)
SELECTstudent.name,ject.NAMEFROMstudent
LEFTJOINsjONstudent.id=sj.studId
LEFTJOINjectONsj.jectId=ject.idWHEREject.NAMEISNULL;
//查询哪些课没人选(右关联)
SELECTstudent.name,ject.NAMEFROMstudent
RIGHTJOINsjONstudent.id=sj.studId
RIGHTJOINjectONsj.jectId=ject.idWHEREstudent.NAMEISNULL;
//左右关联可以相互转换，如把上面的代码用左关联
SELECTstudent.name,ject.NAMEFROMjec
LEFTJOINsjONject.id=sj.jectId
LEFTJOINstudentONsj.studId=student.idWHEREstudent.NAMEISNULL
六、存储过程//定义存储过程p1//“DELIMITER
”这句是为了让解析器把“
”当作结束标志(否则默认是把“;“号当作结束标记),这样存储过程中的语句结束符;就不会当作过程的结束
记。
DELIMITER$$
CREATEPROCEDUREp1()
BEGIN
SELECT*FROMstud;
INSERTINTOstud(id,sname,age,score,dept)VALUES(,刘三丰,33,55,通信学院);
END$$
DELIMITER;//把结束标记还原回来CALLp1();//调用存储过程p1
1、带参数的存储过程DELIMITER$$
CREATEPROCEDUREp2(INidINT,INnmVARCHAR(30))
BEGIN
INSERTINTOstud(id,sname)VALUES(id,nm);
END$$
DELIMITER;
DROPPROCEDUREp2;
CALLp2(,吊丝);
2、有返回值的存储过程----参数与变量问题(变量名，一个为用户变量，两个即为全局的系统变量)DELIMITER$$
CREATEPROCEDUREp3(INidINT,INnmVARCHAR(30),OUTnumINT)
BEGIN
INSERTINTOstud(id,sname)VALUES(id,nm);
SELECTCOUNT(*)INTOnumFROMstud;
END$$
DELIMITER;
CALLp3(,无名,
aa);SELECT
aa;//输出变量aa的值七、事务处理STARTTRANSACTION
DELETEFROMstudWHEREid=;
DELETEFROMstudWHEREid=;
SELECT*FROMstud;
ROLLBACK/COMMIT;
◎采用事务的java编程
try{
st.execute(STARTTRANSACTION;);
st.execute(DELETEFROMstudWHEREid=;);
st.execute(DELETEFROMstudWHEREid=;);
....
st.execute(北京看白癜风多少钱治白癜风长春哪家医院好