MySQL的开发必须会的SQL语句

2025-03-18 技术教程

本篇内容主要讲解“MySQL的开发必须会的SQL语句”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“MySQL的开发必须会的SQL语句”吧!

创建数据库

createdatabasedb1;

删除数据库

dropdatabasedb1;

创建数据表

createtabletb1用户表(idintnotnullauto_incrementprimarykey,namechar(10),department_idint,p_idint,)engine=innodbdefaultcharset=utf8;

主键(primary key)一个表只能有一个主键，主键可以由一列或者多列组成

外键的创建

CREATETABLEt5(nidint(11)NOTNULLAUTO_INCREMENT,pidint(11)notNULL,numint(11),primarykey(nid,pid)--这里就是把两列设置成了主键)ENGINE=InnoDBDEFAULTCHARSET=utf8;createtablet6(idintauto_incrementprimarykey,namechar(10),id1int,id2int,CONSTRAINTfk_t5_t6foreignkey(id1,id2)REFERENCESt1(nid,pid)--这里是设置外键)engine=innodbdefaultcharset=utf8;

数据行的操作

数据的插入

insertintotb1(name,age)values('ax',8);insertintotb12(name,age)selectname,agefromtb11;

表中的数据的删除

deletefromt1;truncatetablet1;droptablet1deletefromtb1whereid>10deletefromtb12whereid>=2orname='alex'

数据的更新

updatetb1setname='root'whereid>10

数据的查询

select*fromtb;selectid,namefromtb;

表结构的查看

showcreatetablet1;desct1;

其他

select*fromtb12whereid!=1select*fromtb12whereidin(1,5,12);select*fromtb12whereidnotin(1,5,12);select*fromtb12whereidin(selectidfromtb11)select*fromtb12whereidbetween5and12;

通配符

select*fromtb12wherenamelike"a%"select*fromtb12wherenamelike"a_"

分页

select*fromtb12limit10;select*fromtb12limit0,10;select*fromtb12limit10,10;select*fromtb12limit20,10;select*fromtb12limit10offset20;#page=input('请输入要查看的页码')#page=int(page)#(page-1)*10#select*fromtb12limit0,10;1#select*fromtb12limit10,10;2

排序

select*fromtb12orderbyiddesc;大到小select*fromtb12orderbyidasc;小到大select*fromtb12orderbyagedesc,iddesc;取后10条数据select*fromtb12orderbyiddesclimit10;

分组

selectcount(id),max(id),part_idfromuserinfo5groupbypart_id;聚合函数有下面几个：countmaxminsumavg****如果对于聚合函数结果进行二次筛选时？必须使用having****selectcount(id),part_idfromuserinfo5groupbypart_idhavingcount(id)>1;selectcount(id),part_idfromuserinfo5whereid>0groupbypart_idhavingcount(id)>1;

自增值设置

表自增值的设置

altertablet1auto_increment=20;--这个就表示从开始20开始算，用上面的showcreatetablet1\G就可以看到当前的值是多少。

基于会话级别

--查看当前的会话值showsessionvariableslike'auto_incre%'--设置会话步长setsessionauto_increment_increment=2;--设置起始值setsessionauto_increment_offset=10;

基于全局设置

--查看全局的设置值showglobalvariableslike'auto_inc%';--设置全局步长值setglobalauto_increment_increment=3;--设置起始值setglobalauto_increment_offset=11;

sql server 是在创建表的时候就可以自己设置，灵活度很高REATE TABLE t5 (nid int(11) NOT NULL AUTO_INCREMENT,pid int(11) NOT NULL,num int(11) DEFAULT NULL,PRIMARY KEY (nid,pid)) ENGINE=InnoDB AUTO_INCREMENT=4, 步长=2 DEFAULT CHARSET=utf8

CREATE TABLE `t6` (

nid int(11) NOT NULL AUTO_INCREMENT,pid int(11) NOT NULL,num int(11) DEFAULT NULL,PRIMARY KEY (nid,pid)) ENGINE=InnoDB AUTO_INCREMENT=4, 步长=20 DEFAULT CHARSET=utf8

唯一索引

createtablet1(idint,numint,xxint,uniquequ1(num,xx)--意思就是这两列在一行上面数据不能相同，例如都是1,1,就不行);

唯一索引：约束不能重复(可以为空)主键索引：约束不能重复(不可以为空)他们的特点都是加速查询

外键一对一

createtableuserinfo1(idintauto_incrementprimarykey,namechar(10),genderchar(10),emailvarchar(64))engine=innodbdefaultcharset=utf8;createtableadmin(idintnotnullauto_incrementprimarykey,usernamevarchar(64)notnull,passwordVARCHAR(64)notnull,user_idintnotnull,uniqueuq_u1(user_id),CONSTRAINTfk_admin_u1FOREIGNkey(user_id)REFERENCESuserinfo1(id))engine=innodbdefaultcharset=utf8;

外键多对多

示例1：用户表相亲表示例2：用户表主机表用户主机关系表===》多对多createtableuserinfo2(idintauto_incrementprimarykey,namechar(10),genderchar(10),emailvarchar(64))engine=innodbdefaultcharset=utf8;createtablehost(idintauto_incrementprimarykey,hostnamechar(64))engine=innodbdefaultcharset=utf8;createtableuser2host(idintauto_incrementprimarykey,useridintnotnull,hostidintnotnull,uniqueuq_user_host(userid,hostid),CONSTRAINTfk_u2h_userFOREIGNkey(userid)REFERENCESuserinfo2(id),CONSTRAINTfk_u2h_hostFOREIGNkey(hostid)REFERENCEShost(id))engine=innodbdefaultcharset=utf8;

连表操作

select*fromuserinfo5,department5select*fromuserinfo5,department5whereuserinfo5.part_id=department5.idselect*fromuserinfo5leftjoindepartment5onuserinfo5.part_id=department5.idselect*fromdepartment5leftjoinuserinfo5onuserinfo5.part_id=department5.id#userinfo5左边全部显示#select*fromuserinfo5rightjoindepartment5onuserinfo5.part_id=department5.id#department5右边全部显示select*fromuserinfo5innderjoindepartment5onuserinfo5.part_id=department5.id将出现null时一行隐藏select*fromdepartment5leftjoinuserinfo5onuserinfo5.part_id=department5.idleftjoinuserinfo6onuserinfo5.part_id=department5.idselectscore.sid,student.sidfromscoreleftjoinstudentonscore.student_id=student.sidleftjoincourseonscore.course_id=course.cidleftjoinclassonstudent.class_id=class.cidleftjointeacheroncourse.teacher_id=teacher.tiselectcount(id)fromuserinfo5;

到此，相信大家对“MySQL的开发必须会的SQL语句”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！