（一）

登錄Mysql

mysql -uroot -proot

查看數(shù)據(jù)庫

show databases;

數(shù)據(jù)庫:

按照數(shù)據(jù)結(jié)構(gòu)來組織、存儲管理數(shù)據(jù)的倉庫

為什么要使用數(shù)據(jù)庫？

1.查找數(shù)據(jù)方便

2.節(jié)省空間

卸載mysql沒有卸載完成

1.控制面板卸載mysql服務(wù)

2.文件保存目錄刪掉

3.隱藏mysql目錄也要刪

mysql的數(shù)據(jù)類型:

int:整形?

varchar():字符串

boolean:是否

text:大文本

timestamp:時間戳

bolb:二進制

mysql約束: ?

唯一:unique??

自增約束:auto_increments??

主外鍵:foreign key?

外鍵也稱為參照完整性，外鍵表的信息參照著主鍵表的信息進行設(shè)置，主鍵表沒有的信息外鍵表示不能使用的非空約束:notnull??

主鍵約束:primary key

默認值約束:default

on delete cascade:在設(shè)置外鍵的時候設(shè)置級聯(lián)刪除

——————————————————————————————————

mysql增刪改查語法:

————————

增：

增加數(shù)據(jù):insert into 表名(列名1，列名2) values（值1，值2）

————————

刪：

刪除語法:delete from 表名 條件表達式

刪除表:drop table 表名

清空表:truncate table sss

——————————————————————————————

改：

修改語法:update 表名 set 列名1=值1 where 條件表達式

——————————

查:

基本查詢:select * from 表名

查詢指定的內(nèi)容:select 列名 from 表名

列名起別名:select 列名 as 名稱（即自己取的列名的別名） from 表名

或省略as簡寫為：

? ?select 列名? ?名稱（即自己取的列名的別名） from 表名

去除重復(fù)(列名下的內(nèi)容的查詢):select distinct name,typename from sss

簡單多表查詢:select * from 表1,表2 where 表1.id=表2.id

__________________________________________

子查詢分為3中子句: where后當條件

將查詢結(jié)果當做比較條件

select * from? 表名 where 列名>（select 列名 from 表名）

? from后當數(shù)據(jù)源

? exists后當條件

where子句:后跟一個表達式，表達式返回一個true或者false的數(shù)據(jù)

表達式可以是

>,<,=,!=,>=,<=

between...and....

in(內(nèi)容1,內(nèi)容2)

like:%(匹配0到任意個字符數(shù)據(jù))? ? _(代表一個字符)

&&,||,and,or,is not null,is null?

order by:

排序....默認是升序(ASC) 倒序(DESC)

select top 3 from sp

limit后可以跟1個或者兩個數(shù)字

跟1個的時候是從0到第幾條數(shù)據(jù)

跟兩個的時候就是從第一個參數(shù)開始的位置查詢第二個參數(shù)的個數(shù)

——————————

題目：

16.查詢商品編號為為5的商品信息
17.查詢商品價格在100-500之間的商品信息
18.查詢用戶名包含a開頭的用戶信息
19.查詢超出商品保質(zhì)期的商品
20.查詢商品編號為2，5,7的商品信息
21.根據(jù)商品的價格進行排序，要求倒序
22.查詢商品表前3條數(shù)據(jù)
23.查詢商品表銷售價格大于進貨價200的商品
24.查詢商品表所有商品的總價格
25.查詢商品名稱2個字的商品，按照價格倒序排序，取前三條
26.查詢每個人購買的商品總價格
27.查詢編號為3的商品一共賣了多少件
28.查詢銷售量最高的是哪件商品
29.查詢每件商品的平均銷售數(shù)量

30.查詢銷售數(shù)量比平均銷售數(shù)量高的商品有哪些

答案：

16.查詢商品編號為為5的商品信息
select?*?from?sp?where?spid=5
17.查詢商品價格在100-500之間的商品信息
select?*?from?sp?where?sp_chushoujia?between?100?and?500
select?*?from?sp?where?sp_chushoujia>100?and?sp_chushoujia<500
18.查詢用戶名包含a開頭的用戶信息
select?*?from?yonghu?where?yhuname?like?'a%'
19.查詢超出商品保質(zhì)期的商品
select?*?from?sp?where?spbaozhiqi<current_timestamp
20.查詢商品編號為2,5,7的商品信息
select?*?from?sp?where?spid?in(2,5,7)
21.根據(jù)商品的價格進行排序，要求倒序
select?*?from?sp?order?by?sp.sp_chushoujia
22.查詢商品表前3條數(shù)據(jù)
select?*?from?sp?limit?0,3
23.查詢商品表銷售價格大于進貨價200的商品
select?*?from?sp?where?sp_chushoujia-spjinhuojia>20
24.查詢商品表所有商品的總價格
select?sum(sp_chushoujia)?from?sp
25.查詢商品名稱2個字的商品，按照價格倒序排序，取前三條
select?*?from?sp?where?spname?like?'__'?order?by?sp_chushoujia?desc?limit?3

————

【關(guān)鍵詞出現(xiàn)的先后順序：

where????group?by????having???order?by???limit

——————

where子句
order?by子句
limit子句
group?by子句
having子句】

————————

26.查詢每個人購買的商品總價格

————

【5個聚合函數(shù)：
sum()??avg()? ?max()? ?min()? ?count()】

——————
select?sum(sp_chushoujia)?from?sp
select?c.yhid?'每個人',c.yhname,sum(a.spbuynum)*b.sp_chushoujia?as?'每個人買的多少錢'
?from?dingdan?a?inner?join?sp?b?on?a.spid=b.spid?inner?join?yonghu?c?on?a.yhid?=c.yhid
group?by?c.yhid?
27.查詢編號為3的商品一共賣了多少件
select?sum(dingdan.spbuynum)?from?dingdan?where?spid=3
28.查詢銷售量最高的是哪件商品
select??spid,sum(spbuynum)?from?dingdan?group?by?spid??limit?1
29.查詢每件商品的平均銷售數(shù)量
select??spid,avg(spbuynum)?from?dingdan??group?by?spid
select?*?from?dingdan
30.查詢銷售數(shù)量比平均銷售數(shù)量高的商品有哪些
?select?*?from?dingdan?where?spbuynum>(select?avg(spbuynum)?from?dingdan)

————————————————————————————

————————————————————————————————————————

（二）

————

下面是常用的語句：

drop table dingdan

drop table sp

drop table yonghu

select * from? sp

select * from? yonghu

select * from? dingdan

————

1.使用sql語句創(chuàng)建以上三張表

create table sp(

spid int primary key auto_increment,

spname varchar(100) not null unique,

spjinhuojia float default 0,

sp_chushoujia float default 0,

spshengchanriqi timestamp default current_timestamp,

spbaozhiqi datetime,

spshuliang int default 0,

spbeizhu varchar(1000)

);

create table yonghu(

yhid int primary key auto_increment,

yhuname varchar(100) not null unique,

yhpwd varchar(100) not null ,

yhname varchar(100) not null unique

);

create table dingdan(

did int primary key auto_increment,

spid int,

yhid int,

spbuynum int,

foreign key(spid) references sp(spid) on delete cascade,

foreign key(yhid) references yonghu(yhid)on delete cascade

)

2.使用insert語句給以上表分別插入10條數(shù)據(jù)

insert into sp (spname,spjinhuojia,sp_chushoujia,spshengchanriqi,spbaozhiqi,spshuliang,spbeizhu) values('雪碧',3,4,now(),'2020-10-11',10,'hhh'),

('雪花',2,3,now(),'2020-9-8',10,'hhhssss'),('熱狗',2,3,now(),'2020-6-11',15,'hhh防守打法'),('烏龍茶',4,5,now(),'2019-10-11',22,'hhh'),

('菠蘿啤',1,2,now(),'2020-2-11',110,'hh防守打法h'),('白沙',8,10,now(),'2022-10-11',100,'hhh防守打法'),('牛肉',35,42,now(),'2020-8-21',19,'hh防守打法h'),

('紅茶',3,4,now(),'2020-12-11',11,'h大聲道hh')

insert into yonghu(yhuname,yhpwd,yhname)values('admin','admin','GM'),('qwe','qwe','gggg'),('ewq','ewq','sssss'),('asd','asd','fsdfsdf'),

('趙云','zhaoyun','常山趙子龍'),('劉備','liubei','大哥'),('關(guān)羽','guanyu','二弟'),('張飛','zhangfei','三弟')

insert into dingdan(spid,yhid,spbuynum) values(3,1,10),(3,2,4),(3,3,7),(1,2,6),(3,2,8),(5,6,10),

(7,1,9),(6,2,6),(8,4,3),(2,2,10),(7,2,5),(3,4,10),(8,1,10),(6,6,6),(7,7,7)

3.修改商品表商品編號為3的商品名稱為可口可樂

update sp set spname='可口可樂' where spid=3

4.修改用戶表用戶編號為5的用戶姓名為“鐘無艷”

update yonghu set yhname='鐘無艷' where yhid=5

5.修改購物車表商品訂單編號為3的數(shù)量為10

update dingdan set spbuynum=10 where did=3

6.添加鐘無艷購買可口可的記錄，購買數(shù)量為5

insert into dingdan (yhid,spid,spbuynum) values((select yhid from yonghu where yhname='鐘無艷'),

(select spid from sp where spname='可口可樂'),5)

7.修改鐘無艷購買可口可樂的數(shù)量為10

update dingdan set spbuynum=10 where spid=(select spid from sp where spname='可口可樂')

and yhid=(select yhid from yonghu where yhname='鐘無艷')

8.修改購買數(shù)量大于5的商品為可口可樂

update dingdan set spid=(select spid from sp where spname='可口可樂')

where spbuynum>10

9.修改用戶編號為3的用戶購買商品為可口可樂的數(shù)量為5

update dingdan set spbuynum=5 where yhid=3 and?

spid=(select spid from sp where spname='可口可樂')

————————————————————————————

重要部分（和我之前做的答案不一樣部分）:

10.修改商品剩余數(shù)量為0的購買數(shù)量為0

update dingdan inner join sp on dingdan.spid=sp.spid

set dingdan.spbuynum=0 where sp.spshuliang=0

11.刪除商品可口可樂，要求級聯(lián)刪除

delete from sp where spname='可口可樂'

12.刪除商品剩余數(shù)量等于為0的商品購買記錄

先查商品表，看看商品的剩余數(shù)量，如果商品的商品數(shù)量為0，就看誰買了這件商品

如果有人買了這件商品就將這件商品的購買記錄刪除掉

delete dingdan

FROM dingdan inner join sp on dingdan.spid=sp.spid

where sp.spshuliang=0

13.刪除用戶編號為3的用戶信息，購買記錄

delete from yonghu where yhid=3

14.聯(lián)合查詢3張表

select * from sp inner join dingdan on sp.spid=dingdan.spid inner join

?yonghu on dingdan.yhid=yonghu.yhid

15.查詢3張表顯示購買記錄編號，購買的名稱，商品價格，購買的用戶姓名，

購買數(shù)量和單件商品總價格

select did as '訂單編號',sp.spname as '商品名稱',

sp.sp_chushoujia as '商品價格', yonghu.yhname as '用戶名',dingdan.spbuynum as '購買數(shù)量',

(sp.sp_chushoujia*dingdan.spbuynum) as '單件商品總價格'

from sp inner join?

dingdan on sp.spid=dingdan.spid inner join

?yonghu on dingdan.yhid=yonghu.yhid

————

下面是常用的語句：

drop table cj

drop table stu

drop table zy

select * from? cj

select * from? stu

select * from? zy

————

create table stu(

xuehao int primary key auto_increment,

xingming varchar(100) not null unique,

sex varchar(10) default '男',

chushengriqi timestamp default current_timestamp,

beizhu text

);

create table zy(

zhuanyeid int primary key auto_increment,

zhuanyename varchar(100) not null

);

create table cj(

chengjiid int primary key auto_increment,

xuehao int,

zhuanyeid int,

chengji float,

foreign key (xuehao) references stu(xuehao) on delete cascade,

foreign key(zhuanyeid) references zy(zhuanyeid) on delete cascade

);

insert into stu (chushengriqi,xingming,beizhu,sex) values('1990-08-14','張三17','呵呵','女'),('1990-08-14','張三16','呵呵','女')

insert into stu (chushengriqi,xingming,beizhu) values('1990-08-14','張三15','呵呵'),('2000-08-14','張三14','呵呵')

insert into stu (xingming,beizhu) values('張三10','呵呵'),('張三11','呵呵'),('張三12','呵呵'),('張三13','呵呵'),('張三','呵呵'),

('李四','呵呵1'),('王五','呵呵2'),('馬六','呵呵3'),('劉備','呵呵4'),

('關(guān)羽','呵呵5'),('張飛','呵呵6'),('黃忠','呵呵7'),('馬超','呵呵')

insert into zy(zhuanyename) values('計算機'),('Java'),('C#'),

('mysql'),('sqlserver'),('javascript')

insert into cj (xuehao,zhuanyeid,chengji) values(16,1,40),(17,2,60),(1,1,88),(1,1,66),(1,2,99),(11,2,33),(3,2,33),(1,2,33),

(2,3,44),(4,3,55),(2,4,66),(6,2,77),(7,3,88),(6,4,99)

11.查詢編號在3-7之間的所有學(xué)生信息

select * from stu where xuehao>=3? and xuehao<7

12.查詢名稱包含張的學(xué)生的成績

select * from stu where xingming like '%張%'

13.查詢性別為男的學(xué)生的成績

--【不好用子查詢的，就會用多表查詢】

select chengji? as'男的學(xué)生的成績'? ?from cj? inner? join? stu? on? cj.xuehao=stu.xuehao? ?where sex='男'

14.查詢編號在2,5,6的學(xué)生的信息

select *? from stu? ?where xuehao in (2,5,6)

15.查詢名稱包含王的學(xué)生的專業(yè)

select zhuanyename? ?as'包含王的學(xué)生的專業(yè)'? ?from zy? inner? join? cj? on? zy.zhuanyeid=cj.zhuanyeid

?inner? join? stu? on? stu.xuehao=cj.xuehao? where stu.xingming like '%王%'

16.查詢學(xué)生id為11的所有課程的成績,顯示學(xué)生姓名,專業(yè)名稱,成績，使用別名

select xingming? as '學(xué)生姓名' ,zhuanyename? ?as'專業(yè)名稱'? ,chengji as '成績'? from zy? inner? join? cj? on? zy.zhuanyeid=cj.zhuanyeid

?inner? join? stu? on? stu.xuehao=cj.xuehao? where stu.xuehao=11

17.查詢以王開頭的學(xué)生并且名稱長度為2的學(xué)生的信息

select * from stu where xingming like '%王_%'

18.查詢年齡小于20歲的學(xué)生信息

select * from stu? where (year(now())-year(chushengriqi))<20

19.查詢每個學(xué)生的年齡，按照倒序排序

select? ?xingming as '學(xué)生的姓名',(year(now())-year(chushengriqi)) as '年齡'? from stu order by (year(now())-year(chushengriqi)) desc

??

20.查詢每個學(xué)生的總分

select xingming as '每個學(xué)生的姓名', sum(chengji) as '每個學(xué)生的總分' from stu? inner join? cj on stu.xuehao=cj.xuehao group by? xingming

21.查詢張三的所有科目的總成績

select? sum(chengji) as '張三的所有科目的總成績' from stu? inner join? cj on stu.xuehao=cj.xuehao? ?

inner join? zy on zy.zhuanyeid=cj.zhuanyeid where? xingming='張三'

22.查詢張三的所有科目的平均成績

select? ? zhuanyename as'專業(yè)科目名',? avg(chengji) as '張三這門科目的平均成績' from stu? inner join? cj on stu.xuehao=cj.xuehao? ?

inner join? zy on zy.zhuanyeid=cj.zhuanyeid? ? where? xingming='張三'? group by? zhuanyename

23.查詢所有學(xué)生的最高分，最低分

select? ? zhuanyename as'專業(yè)科目名',? avg(chengji) as '張三這門科目的平均成績' from stu? inner join? cj on stu.xuehao=cj.xuehao? ?

inner join? zy on zy.zhuanyeid=cj.zhuanyeid? ? where? xingming='張三'? group by? zhuanyename

24.查詢張三有多少個科目

--去除重復(fù):select distinct name,typename from sss【distinct 放在不想重復(fù)的列名處】

select? count(distinct zhuanyeid) as'張三有的科目數(shù)'? from? cj where xuehao =(select? xuehao from stu where xingming='張三'? )

--select? zhuanyename? as'張三考過的科目', count(zhuanyename) as'張三考過的次數(shù)'? from stu? inner join? cj on stu.xuehao=cj.xuehao? ?

--inner join? zy on zy.zhuanyeid=cj.zhuanyeid? ? where? xingming='張三'? group by? zhuanyename??

25.查詢每個人的最高分,最低分

select? xingming as '姓名',? max(chengji) as'最高分',? min(chengji) as'最低分'? from stu? inner join? cj on stu.xuehao=cj.xuehao? ?

inner join? zy on zy.zhuanyeid=cj.zhuanyeid? ? group by? xingming

26.查詢每個人的總成績，平均成績

【個人總結(jié)：就算別的表中沒對應(yīng)的內(nèi)容,left? join也會顯示stu 這個left? join前（左）表中的所有列名中有的內(nèi)容（right join也類似，

就算別的表中沒對應(yīng)的內(nèi)容,right join也會顯示stu 這個right join后（右）表中的所有列名中有的內(nèi)容），

而inner join只會顯示都有對應(yīng)內(nèi)容的列名中的內(nèi)容等】

select? xingming as '姓名',? sum(chengji) as'總成績',? avg(chengji) as'平均成績'? from stu? left? join? cj on stu.xuehao=cj.xuehao? ?

left join? zy on zy.zhuanyeid=cj.zhuanyeid? ? group by? xingming

27.查詢每個人有多少個科目

select? xingming as '姓名',? count(distinct zhuanyename) as'有的科目數(shù)'? from stu? left join? cj on stu.xuehao=cj.xuehao? ?

left join? zy on zy.zhuanyeid=cj.zhuanyeid? ? group by? xingming

28.查詢平均成績大于60的人的平均成績

【having avg(chengji )>60是group by? xingming后面加的限制條件】

select? xingming as '姓名',? sum(chengji) as'總成績',? avg(chengji ) as'平均成績'? ,??

count(distinct zhuanyename) as'有的科目數(shù)' from stu? inner join? cj on stu.xuehao=cj.xuehao? ?

inner join? zy on zy.zhuanyeid=cj.zhuanyeid? ? ?group by? xingming? having avg(chengji )>60

--select? xingming as '姓名',? avg(chengji) as'平均成績'? from stu? inner join? cj on stu.xuehao=cj.xuehao? ?

--inner join? zy on zy.zhuanyeid=cj.zhuanyeid? where? ?avg(chengji)>60? ?group by? xingming??

29.查詢低于班級平均成績的學(xué)生姓名和成績

select? xingming as '姓名',? sum(chengji) as'總成績',? avg(chengji ) as'平均成績'? ,?

count(distinct zhuanyename) as'有的科目數(shù)'? ?,? ?(select? ?sum(chengji)/count(chengjiid)

from stu? left join? cj on stu.xuehao=cj.xuehao? ? ?

left join? zy on zy.zhuanyeid=cj.zhuanyeid)? as '班級平均成績' from stu? left join? cj on stu.xuehao=cj.xuehao? ?

left join? zy on zy.zhuanyeid=cj.zhuanyeid? ? ?group by? xingming? having avg(chengji )<(select? ?sum(chengji)/count(chengjiid)

from stu? left join? cj on stu.xuehao=cj.xuehao? ? ?

left join? zy on zy.zhuanyeid=cj.zhuanyeid)

【

個人的總結(jié)：

select? ?sum(chengji)/count(chengjiid)

from stu? left join? cj on stu.xuehao=cj.xuehao? ? ?

left join? zy on zy.zhuanyeid=cj.zhuanyeid? 求的是總成績和除以總?cè)藬?shù)=班級總平均成績=68.9333333333333，

如果顯示8.9333333333333，就是顯示不完整，鼠標左鍵選中后，向左移動，就可以看到"8.9333333333333"前面的"6"，

或者出現(xiàn)箭頭，把表格拉長。

】

--select??

--sum(chengji)/(count(distinct? zhuanyename)*count(distinct xingming))? from stu? left? join? cj on stu.xuehao=cj.xuehao? ?

--left join? zy on zy.zhuanyeid=cj.zhuanyeid

30.查詢男同學(xué)的平均成績比女同學(xué)的平均成績多多少

select? ( (select? avg(chengji) from stu? left join? cj on stu.xuehao=cj.xuehao? ?

left join? zy on zy.zhuanyeid=cj.zhuanyeid? where sex='男')-(select? avg(chengji) from stu? left join? cj on stu.xuehao=cj.xuehao? ?

left join? zy on zy.zhuanyeid=cj.zhuanyeid? where sex='女')) as'男同學(xué)的平均成績比女同學(xué)的平均成績多的分數(shù)'?

【

select? ?distinct( (select? avg(chengji) from stu? left join? cj on stu.xuehao=cj.xuehao? ?

left join? zy on zy.zhuanyeid=cj.zhuanyeid? where sex='男')-(select? avg(chengji) from stu? left join? cj on stu.xuehao=cj.xuehao? ?

left join? zy on zy.zhuanyeid=cj.zhuanyeid? where sex='女')) as'男同學(xué)的平均成績比女同學(xué)的平均成績多的分數(shù)'? from stu? left join? cj on stu.xuehao=cj.xuehao? ?

left join? zy on zy.zhuanyeid=cj.zhuanyeid? 】?

31.查詢至少擁有兩門課程及以上的學(xué)生

select? xingming as '姓名',? count(distinct zhuanyename) as'有的科目數(shù)'? from stu? left join? cj on stu.xuehao=cj.xuehao? ?

left join? zy on zy.zhuanyeid=cj.zhuanyeid? ? group by? xingming? having count(distinct zhuanyename)>=2

32.查詢擁有兩門課程以上的學(xué)生的平均分

select? xingming as '姓名',? count(distinct zhuanyename) as'有的科目數(shù)'? ,? avg(chengji) as'平均分'? ?from stu? left join? cj on stu.xuehao=cj.xuehao? ?

left join? zy on zy.zhuanyeid=cj.zhuanyeid? ? group by? xingming? having count(distinct zhuanyename)>=2