程序员常用SQL语句大全.docx
- 文档编号:5627548
- 上传时间:2022-12-29
- 格式:DOCX
- 页数:15
- 大小:22.09KB
程序员常用SQL语句大全.docx
《程序员常用SQL语句大全.docx》由会员分享,可在线阅读,更多相关《程序员常用SQL语句大全.docx(15页珍藏版)》请在冰豆网上搜索。
程序员常用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在这里称Latin1
character_set_systemutf8
character_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当中的String
char(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>=24ANDage<27;
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'%五%'ANDage>30;
※4、空值查询
//查询无名英雄学生的信息(VARCHAR)
SELECT*FROMstudWHEREsnameIS;
//查询没有年龄信息的学生
//错:
SELECT*FROMstudWHEREage==;
SELECT*FROMstudWHEREageIS;
※5、聚合函数
1)COUNT统计表格的行数
SELECTCOUNT(*)ASTEMPTABLEFROMstud;//astemptable含义:
就是将查询出的结果(表格的行数)另命名为:
temptable
SELECTCOUNT
(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;//不重复排序(即年龄相同的,只显示第一个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='土木学院'WHEREscore<60;
SELECT*FROMstud;
//分组计算
//按学院计算平均分(每个学员的平均分)
SELECTdept,AVG(score)AS'学院平均分'FROMstudGROUPBYdept;
※11、字符串处理函数
SELECT*FROMstudWHEREsname='AAA';
SELECT*FROMstudWHERETRIM(sname)='AAA';//去掉左右的空格
SELECT*FROMstudWHERELTRIM(RTRIM(sname))='AAA';//和上面等价
UPDATEstudSETdept='数计学院'WHEREid=1011;
SELECTLEFT(TRIM(sname),2)FROMstud;//取去掉空格后的左起2个字符
SELECTREVERSE(TRIM(sname))FROMstud;
CREATETABLEperson(
idINT,
snameVARCHAR(30),
ageINT
);
ALTERTABLEpersonADDCONSTRAINTperson_pkPRIMARYKEY(id);//更灵活
DROPTABLEperson;
snameVARCHAR(30)NOT,
ageINT
);//用NOT限制非空输入
※12、性别字段(例如:
数据库存0、1而显示出来为男、女)
一般不定义成BOOLEAN型,因为有的数据库不支持,为考虑兼容,通常用CHAR
(1)
CREATETABLEa(
idINTUNIQUE,
snameVARCHAR(10),
sexCHAR
(1)DEFAULT'0'
);
//显示性别(真实值与显示值之间的转换)
SELECT*FROMa;
SELECTid,sname,(CASEsexWHEN'0'THEN'女'WHEN'1'THEN'男'ELSE''END)xbFROMa;
SELECTid,sname,(CASEsexWHEN'0'THEN'女'WHEN'1'THEN'男'ELSE''END)ASxbFROMa;
SELECTid,sname,(CASEWHENsex='0'THEN'女'WHENsex='1'THEN'男'ELSE''END)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、关系查询
-----------------------------------------
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('P001','Jack','1');
INSERTINTOperson2(id,pname,sex)VALUES('P002','Tom','1');
INSERTINTOperson2(id,pname,sex)VALUES('P003','Rose','0');
INSERTINTOperson2(id,pname,sex)VALUES('P004','Mary','0');
INSERTINTOperson2(id,pname,sex)VALUES('P005','Mike','1');
SELECT*FROMperson2;
////实体表2
INSERTINTOcar(id,cname,price,pid)VALUES('C001','BMW',123.5,'P001');
INSERTINTOcar(id,cname,price,pid)VALUES('C002','Benz',123.5,'P001');
INSERTINTOcar(id,cname,price,pid)VALUES('C003','BMW',223.5,'P001');
INSERTINTOcar(id,cname,price,pid)VALUES('C011','BMW',83.5,'P003');
INSERTINTOcar(id,cname,price,pid)VALUES('C012','Benz',100,'P003');
INSERTINTOcar(id,cname,price,pid)VALUES('C013','Audi',223.5,'P003');
INSERTINTOcar(id,cname,price,pid)VALUES('C021','BMW',88.5,'P004');
INSERTINTOcar(id,cname,price,pid)VALUES('C022','QQ',10,'P004');
INSERTINTOcar(id,cname,price,pid)VALUES('C023','Audi',73,'P005');
INSERTINTOcar(id,cname,price)VALUES('C033','Audi',1000);
//该句代码执行错误,因为编号为P006的人在Person2表中不存在,这就是参照完整性
INSERTINTOcar(id,cname,price,pid)VALUES('C033','Audi',1000,'P006');
SELECT*FROMcar;
//查询:
哪些人有什么样的车(用"表名.列名"的形式访问列,如果列名不重复,可以省略表名)
//利用一方的主键和“多方”的外键进行关联
SELECTperson2.pname,ameFROMperson2,carWHEREperson2.id=car.pid;
//查询Jack有什么车
SELECTperson2.pname,ameFROMperson2,carWHEREperson2.id=car.pidAND
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 程序员 常用 SQL 语句 大全