首页 资讯频道 互联频道 智能频道 网络 数据频道 安全频道 服务器频道 存储频道

MySQL的开发必会的sql语句有哪些?

2020-04-16 15:32:03 来源 : 今日头条

本文谈谈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;

droptablet1

deletefromtb1whereid>10

deletefromtb12whereid>=2orname='alex'

数据的更新

updatetb1setname='root'whereid>10

数据的查询

select*fromtb;

selectid,namefromtb;

表结构的查看

showcreatetablet1;

desct1;

其他

select*fromtb12whereid!=1

select*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;

聚合函数有下面几个:

count

max

min

sum

avg

****如果对于聚合函数结果进行二次筛选时?必须使用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,department5

select*fromuserinfo5,department5whereuserinfo5.part_id=department5.id

select*fromuserinfo5leftjoindepartment5onuserinfo5.part_id=department5.id

select*fromdepartment5leftjoinuserinfo5onuserinfo5.part_id=department5.id

#userinfo5左边全部显示

#select*fromuserinfo5rightjoindepartment5onuserinfo5.part_id=department5.id

#department5右边全部显示

select*fromuserinfo5innderjoindepartment5onuserinfo5.part_id=department5.id

将出现null时一行隐藏

select*from

department5

leftjoinuserinfo5onuserinfo5.part_id=department5.id

leftjoinuserinfo6onuserinfo5.part_id=department5.id

select

score.sid,

student.sid

from

score

leftjoinstudentonscore.student_id=student.sid

leftjoincourseonscore.course_id=course.cid

leftjoinclassonstudent.class_id=class.cid

leftjointeacheroncourse.teacher_id=teacher.ti

selectcount(id)fromuserinfo5;

最近更新