##mysql數(shù)據(jù)庫事務(wù)###

數(shù)據(jù)庫事務(wù)(簡稱：事務(wù))是由一系列對系統(tǒng)中數(shù)據(jù)進行訪問與更新的操作所組成的一個程序執(zhí)行邏輯單元。

1. 主鍵 超鍵 候選鍵 外鍵

主 鍵：數(shù)據(jù)庫表中對儲存數(shù)據(jù)對象予以唯一和完整標(biāo)識的數(shù)據(jù)列或?qū)傩缘慕M合。一個數(shù)據(jù)列只能有一個主鍵，且主鍵的取值不能缺失，即不能為空值(Null)。

超 鍵：在關(guān)系中能唯一標(biāo)識元組的屬性集稱為關(guān)系模式的超鍵。一個屬性可以為作為一個超鍵，多個屬性組合在一起也可以作為一個超鍵。超鍵包含候選鍵和主鍵。

候選鍵：是最小超鍵，即沒有冗余元素的超鍵。

外 鍵：在一個表中存在的另一個表的主鍵稱此表的外鍵。

2.數(shù)據(jù)庫事務(wù)的四個特性及含義

數(shù)據(jù)庫事務(wù)transanction正確執(zhí)行的四個基本要素。

ACID,原子性(Atomicity)、一致性(Correspondence)、隔離性(Isolation)、持久性(Durability)。

原子性:整個事務(wù)中的所有操作，要么全部完成，要么全部不完成，不可能停滯在中間某個環(huán)節(jié)。事務(wù)在執(zhí)行過程中發(fā)生錯誤，會被回滾(Rollback)到事務(wù)開始前的狀態(tài)，就像這個事務(wù)從來沒有執(zhí)行過一樣。

一致性:在事務(wù)開始之前和事務(wù)結(jié)束以后，數(shù)據(jù)庫的完整性約束沒有被破壞。

隔離性:隔離狀態(tài)執(zhí)行事務(wù)，使它們好像是系統(tǒng)在給定時間內(nèi)執(zhí)行的唯一操作。如果有兩個事務(wù)，運行在相同的時間內(nèi)，執(zhí)行 相同的功能，事務(wù)的隔離性將確保每一事務(wù)在系統(tǒng)中認(rèn)為只有該事務(wù)在使用系統(tǒng)。這種屬性有時稱為串行化，為了防止事務(wù)操作間的混淆，必須串行化或序列化請 求，使得在同一時間僅有一個請求用于同一數(shù)據(jù)。

持久性:在事務(wù)完成以后，該事務(wù)所對數(shù)據(jù)庫所作的更改便持久的保存在數(shù)據(jù)庫之中，并不會被回滾。

視圖的作用，視圖可以更改么？

1.視圖的創(chuàng)建

第一類：create view v as select * from table;

第二類、基于不同數(shù)據(jù)庫

這種情況只比上面的sql語句多一個數(shù)據(jù)庫的名字，如下：

create view 數(shù)據(jù)庫1.v as (select * from 數(shù)據(jù)庫1.table1) union all (select * from 數(shù)據(jù)庫2.table2);

或

create view 數(shù)據(jù)庫2.v as (select * from 數(shù)據(jù)庫1.table1) union all (select * from 數(shù)據(jù)庫2.table2);

如果執(zhí)行第一個sql將在數(shù)據(jù)庫1下建立視圖，反之亦然；

視圖的作用？

視圖是虛擬的表，與包含數(shù)據(jù)的表不一樣，視圖只包含使用時動態(tài)檢索數(shù)據(jù)的查詢；不包含任何列或數(shù)據(jù)。使用視圖可以簡化復(fù)雜的sql操作，隱藏具體的細(xì)節(jié)，保護數(shù)據(jù)；視圖創(chuàng)建后，可以使用與表相同的方式利用它們。視圖不能被索引，也不能有關(guān)聯(lián)的觸發(fā)器或默認(rèn)值，如果視圖本身內(nèi)有order by 則對視圖再次order by將被覆蓋。

視圖可以更改么？

對于某些視圖比如未使用聯(lián)結(jié)子查詢分組聚集函數(shù)Distinct 、Union等，是可以對其更新的，對視圖的更新將對基表進行更新；但是視圖主要用于簡化檢索，保護數(shù)據(jù)，并不用于更新，而且大部分視圖都不可以更新

4、drop,delete與truncate的區(qū)別

一、不同點

1.truncate table 和 delete只刪除記錄不刪除表的結(jié)構(gòu)，drop語句將刪除表的結(jié)構(gòu)依賴的約束(constrain)，觸發(fā)器(trigger),索引(index);drop語句將刪除表的結(jié)構(gòu)被依賴的約束(constrain)，觸發(fā)器(trigger)，索引(index);依賴于該表的存儲過程/函數(shù)將保留，但是變?yōu)閕nvalid狀態(tài)

2.truncate之后的自增字段從頭開始計數(shù)了，而delete的仍保留原來的最在數(shù)值。

二、總結(jié)：

1.在速度上，一般來說，drop> truncate > delete。

2.在使用drop和truncate時一定要注意，雖然可以恢復(fù)，但為了減少麻煩，還是要慎重。

3.如果想刪除部分?jǐn)?shù)據(jù)用delete，注意帶上where子句，回滾段要足夠大；

如果想刪除表，當(dāng)然用drop；

如果想保留表而將所有數(shù)據(jù)刪除，如果和事務(wù)無關(guān)，用truncate即可；

如果和事務(wù)有關(guān)，或者想觸發(fā)trigger，還是用delete

如果是整理表內(nèi)部的碎片，可以用truncate跟上reuse stroage，再重新導(dǎo)入/插入數(shù)據(jù)

5.索引的工作原理及其種類

數(shù)據(jù)庫索引，是數(shù)據(jù)庫管理系統(tǒng)中一個排序的數(shù)據(jù)結(jié)構(gòu)，以協(xié)助快速查詢、更新數(shù)據(jù)庫表中數(shù)據(jù)。索引的實現(xiàn)通常使用B樹及其變種B+樹

MySQL索引的建立對于MySQL的高效運行是很重要的，索引可以大大提高MySQL的檢索速度。索引也是一張表，該表保存了主鍵與索引字段，并指向?qū)嶓w表的記錄。上面都在說使用索引的好處，但過多的使用索引將會造成濫用。

因此索引也會有它的缺點：雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對表進行INSERT、UPDATE和DELETE。因為更新表時，MySQL不僅要保存數(shù)據(jù)，還要保存一下索引文件。建立索引會占用磁盤空間的索引文件。

ALTER table tableName ADD INDEX indexName(columnName)

##表的創(chuàng)建、更新、修改

28、 創(chuàng)建表

Create table actor

Actor_id smallint(5) notnull Primary key,

First_name varchar(45) not null,

Last_name varchar(45) not null,

Last_update timestamp not null

Default (datetime('now','locatetime'))

33、

insert INTO actor #tablename

ValueS (1,PENELOPE,GUINESS,2006-02-15 12:34:33),

(2,NICK,WAHLBERG,2006-02-15 12:34:33)

34、

Insert ignore into

ValueS (1,PENELOPE,GUINESS,2006-02-15 12:34:33)

35、

Create table actor_name

As

Select first_name,last_name from actor

37、 創(chuàng)建索引

Create unique index unique_idx_firstname on actor(first_name)

Create index index_lastname on actor(last_name)

38、 臨時試圖

Create view actor_name_view

As

Select first_name as first_name_v,last_name as last_name_v

From actor

39、 增加一列

Alter table actor add column

Create_date datetime not null

Default '0000-00000 00:00:00'

40、 觸發(fā)器

Create triggle audit_log

After insert into employee_test

Begin insert into audit

Values (NEW.id,NEW.NAME)

END

41、 刪除重復(fù)記錄，保留小的id

Select emp_no,title,from_date,to_date,min(id) as id

From title_test t

Group by emp_no,title,from_date,to_date

Delete title_test t

Where id not in (select min(id) from title_test group by emp_no)

42 更新

Update title_set set from_date='2001-01-01'and to_date = null

Where to_date = '9999-01-01'

43、

Repalce into titles_test

as

Select 5,10005,title ,from_date,to_date

From title_test where id = 5

44、 更改表明

Alter table titles_test rename to titles_2017

45、 創(chuàng)建外檢約束

Alter table audit add foreign key(emp_no)

Reference emplyees_test(id)

46、 選出和視圖一樣的數(shù)據(jù)

Select em.* from emplyees as em,emp_v as ev

Where em.emp_no = ev.emp_no

Select * from emp_v

47、 獲獎員工當(dāng)前工資增加

Update

Salaries s

Set salary = salary *1.1

Where s.emp_no in (select emp_no from emp_bonus) and s.to_date='9999-01-01'

48

只修改列的數(shù)據(jù)類型的方法:

通常可以寫成 alter table 表名 modify column 列名 新的列的類型

例如:student表中列sname的類型是char(20),現(xiàn)在要修改為varchar(20),SQL語句如下

alter table student modify column sname varchar(20);

同時修改列名和列的數(shù)據(jù)類型的方法:

通常可以寫成 alter table 表名 change column 舊列名 新列名 新的列類型

例如:student表中列sname的類型是char(20),現(xiàn)在要修改為stuname varchar(20),SQL語句如下

alter table student change column sname stuname varchar(20);

###數(shù)據(jù)庫查詢

1、查詢"01"課程比"02"課程成績高的學(xué)生的信息及課程分?jǐn)?shù)

Select t.* , 01_score , 02_score

From student t

Inner join

(Select s1.s_id,s1.score as 01_score ,s2.score as 02_score

From score s1 ,score s2

Where s1.c_id =’01’ and s2.c_id =’02’ and s1.score > s2.score and s1.s_id =s2.s_id ) t1

On t.s_id =t1.s_id

##沒選02課程的人

Select

S.*,s1.score as 01_score , s2.score as 02_score

From

(Student s

Right join

score s1 on s.s_id = s1.s_id and s1.c_id=’01’ )

Left join score s2

On s1.s_id = s2.s_id and s2.c_id=’02’ and s1.score > s2.score)

Union

###沒有選擇02課程成績的同學(xué)#####

Select

S.*,s1.score as 01_score , 0 as 02_score

From

Student s

join

score s1 on s.s_id = s1.s_id and s1.c_id=’01’

join score s2

On s1.s_id = s2.s_id and s2_id not in (select s_id from score where c_id = ‘02’))

如果要的滿足"01"課程比"02"課程成績高條件的學(xué)生 所有功課

Select st.* ,’語文’,’數(shù)學(xué)’,’英語

’from student st

Join

(

Select sc.s_id ,

Sum(case sc.c_id = ‘01’ then sc.score else 0 end) as ‘語文’,

Sum(case sc.c_id = ‘02’ then temp.02_scoreelse 0 end )as ‘?dāng)?shù)學(xué)’, ##選擇temp 02成績作為數(shù)學(xué)成績

Sum(case sc.c_id = ‘03’ then sc.score else 0 end) as ‘英語’,

From score sc

Join

###滿足條件的學(xué)生id#####################

(Select

S.*,s1.score as 01_score , s2.score as 02_score

From

(Student s

Right join

score s1 on s.s_id = s1.s_id and s1.c_id=’01’ )

Left join score s2

On s1.s_id = s2.s_id and s2.c_id=’02’ and s1.score > s2.score)

Union

###沒有選擇02課程成績的同學(xué)#####

Select

S.*,s1.score as 01_score , 0 as 02_score

From

(Student s

join

score s1 on s.s_id = s1.s_id and s1.c_id=’01’ )

join score s2

On s1.s_id = s2.s_id and s2_id not in (select s_id from score where c_id = ‘02’))

) temp

On sc.s_id = temp.s_id

Group by sc.s_id)

-- 2、查詢"01"課程比"02"課程成績低的學(xué)生的信息及課程分?jǐn)?shù)

select a.* ,b.s_score as 01_score,c.s_score as 02_score from

(student a left join score b on a.s_id=b.s_id and b.c_id='01' )

join score c on a.s_id=c.s_id and c.c_id='02' where b.s_score

-- 3、查詢平均成績大于等于60分的同學(xué)的學(xué)生編號和學(xué)生姓名和平均成績

Select s_id ,avg(score) as svg_score

From score

Group by s_id

Having avg(score) >=60

Select s1.name,s2.s_id,round(avg(score),2) as svg_score

from student s1

Left join score s2

On s1.s_id = s2.s_id

Group by s1.s_id

Having avg(score) >=60

-- 4、查詢平均成績小于60分的同學(xué)的學(xué)生編號和學(xué)生姓名和平均成績

-- (包括有成績的和無成績的)

Select s.name ,s.s_id, round(avg(score),2) as avg_score

From student s join score s1 on s.s_id = s1.s_id

Group by s.s_id having avg(score) < 60

Union

Select s.name ,s.s_id, 0 as avg_score

From student s

Where s_id not in (select dinstinct s_id from score)

#mysql 版本優(yōu)化了，可以再having里面使用別名

Select s.name ,s.s_id, round(avg(score),2) as avg_score

From student s

Left join score s1 on s.s_id = s1.s_id

Group by s.s_id

having avg_score < 60 or avg_score is null

--19、按各科成績進行排序，并顯示排名

1. 分?jǐn)?shù)相同rank相同，rank考慮是有多少人比你分?jǐn)?shù)高

#先進行子查詢，每一條外查詢都跑一輪子查詢

Select s1.c_id ,

(Select count(1)+1 from score s2 where s1.c_id =s2.c_id and s1.score < s2.score ) as rank

From score s1

Order by s1.c_id ,rank

2.分?jǐn)?shù)相同rank相同，rank考慮是有多少分?jǐn)?shù)比你分?jǐn)?shù)高

Select s1.c_id ,

(Select count(dinstinct s2.score)+1 from score s2 where s1.c_id =s2.c_id and s1.score < s2.score ) as rank

From score s1

Order by s1.c_id ,rank

--46、查詢各學(xué)生的年齡

-- 按照出生日期來算，當(dāng)前月日 < 出生年月的月日則，年齡減一

Select s.name,s_id,

Date_format(now(),’%Y’)- Date_format(s.birth,’%Y’) -

(Case when Date_format(now(),’%m%d’) < Date_format(s.birth,’%m%d’) then 1 else 0 end) as age

From student s

Leetcode

##########行程################

select t.Request_at as Day,

round((sum(case when t.Status != 'completed' then 1 else 0 end))/count(*),2)

as 'Cancellation Rate' #名字有空格使用引號

from

Trips t

inner join Users u1

on t.Client_Id= u1.Users_Id and u1.Banned ='No'

inner join Users u2

on t.Driver_Id= u2.Users_Id and u2.Banned ='No'

where

convert(t.Request_at,date) between '2013-10-01' and '2013-10-03'

group by t.Request_at

order by Day

Month(t.Request_at) = 10 and Year(t.Request_at) = 2013 and day(t.Request_at) <=3 and day(t.Request_at) >= 1

DATE_FORMAT(t.Request_at,'%Y-%m-%d') between '2013-10-01' and '2013-10-03'

from_unixtime(time,'%Y-%m-%d %H:%i:%s') #unixtime 改成固定格式

Select UNIX_TIMESTAMP(’2006-11-04 12:23:00′);

Datatime = date+time

IFNULL(expr1,expr2)：如果第一個參數(shù)不為空，則返回第一個參數(shù)，否則返回第二個參數(shù)。

ISNULL(expr)：判斷是否是空，是空則返回1，否則返回0。

IF(expr1,expr2,expr3)：如果第一個表達(dá)式的值為TRUE(不為0或null)，則返回第二個參數(shù)的值，否則返回第三個參數(shù)的值。

-- 5、查詢所有同學(xué)的學(xué)生編號、學(xué)生姓名、選課總數(shù)、所有課程的總成績

Select s1.s_id ,s1.name,count(1) as num, sum(score) as sum_score

From student s1 left join score s2

On s1.s_id =s2.s_id

Group by s1.s_id

-- 6、查詢學(xué)過"張三"老師授課的同學(xué)的信息

Select from s.*

Student s join score s1

On s.s_id = s1.s_id

Where s1.c_id in

(

Select Course.C_id

from

Course join Teacher

On Course.t_id = Teacher.t_id

Where Teacher.t_name = ‘張三’)

-- 7、查詢學(xué)過編號為"01"并且也學(xué)過編號為"02"的課程的同學(xué)的信息

Select S.*

From student s, score s1,score s2

Where s.s_id =s1.s_id and s1.s_id = s2.s_id

And s1.c_id = ‘01’and s2.s_id = ‘02’

Select S.*

From student s

Join score s1 on s.s_id =s1.s_id And s1.c_id = ‘01’

Join score s2 on s1.s_id = s2.s_id and s2.s_id = ‘02’

-- 8、查詢學(xué)過編號為"01"并且"02"的同學(xué)所選的課程的同學(xué)的信息

Select s.*

From student s join

(Select * from (

Select distinct s_id

From score t Where t.c_id in

(Select c_id From score s

Where s.s_id = ‘01’) and t.s_id != ‘01’) t1

Where t1.c_id in

(Select c_id

From score s

Where s.s_id = ‘02’) and t1.s_id != ‘02’))t2

On s.s_id = t2.s_id

Select s.*

From student s join

(select distinct s_id From score s where s_id != ‘01’and s_id != ‘02’and (c_id in (Select c_id From score s

Where s.s_id = ‘01’) or c_id in (Select c_id From score s

Where s.s_id = ‘01’ ))

) t

On s.s_id =t.s_id

-- 10、查詢學(xué)過編號為"01"但是沒有學(xué)過編號為"02"的課程的同學(xué)的信息

Select s.*

From student s ,score s1 Where s.s_id = s1.s_id

And s1.c_id = ‘01’ and s1.s_id not in (

Select s_id from score s2 where c_id = ‘02’)

-- 11、查詢沒有學(xué)全所有課程的同學(xué)的信息

Select s.*

from student s ,(select s_id from score s1

Group by s1.s_id

Having count(s1.c_id) < (select count(distinct s2.c_id ) from score s2)

) t

Where s.s_id = t.s_id

-- 12、查詢至少有一門課與學(xué)號為"01"的同學(xué)所學(xué)相同的同學(xué)的信息

select distinct s.* # 這樣對，但是慢

from student s ,

score s1

Where s.s_id = s1.s_id and

S1.s_id != ‘01’ and s1.c_id in

(Select c_id from score where s_id = ‘01’)[寫錯了，一個同學(xué)出現(xiàn)了很多條記錄]

Select * from student where s_id in

(Select distinct s_id from score where c_id in (Select c_id from score where s_id = ‘01’) and s_id != ‘01’)

-- 13、查詢和"01"號的同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)的信息

Select s.* from student s

Join

(Select s_id from score s1 on where s1.c_id in (Select c_id from score where s_id = ‘01’)

and s_id != ‘01’

group by s.s_id

having

count(c_id) = (select count(s2.c_id) from score s2 where s2.s_id = ‘01’)

)

Tmp

On s.s_id = tmp.s_id

Leetcode 刪除重復(fù)郵箱，保留id小的

沒有id直接distinct 就好

Select e1.id ,e1.email

From emails e1,emails e2

Where e1.email = e2.email and e1.id < e2.id

Delete e1.id ,e1.email

from

emails e1,emails e2

Where e1.email = e2.email and e1.id > e2.id

-- 20、查詢學(xué)生的總成績并進行排名

Select

@k := (case when @score = a.sum_score then @k else @k+1 end )as rank,

@score : = a.sum_score as score

From

(Select sum(score) as sum_score from score group by s_id order by sum_score Desc) a , (select @k:=0,@score:=0)s

Leetcode

select a.score as Score, (select count(distinct b.score)+1 from Scores b

where a.score < b.score ) as Rank

from Scores a

order by Rank desc

Group_concat函數(shù)

mysql中的函數(shù)，字符串拼接的話，可以用concat()，但是此函數(shù)是針對一條記錄中，可以將不同的字段拼接，并不適用多條記錄的某一字段。查了一下，mysql中g(shù)roup_concat函數(shù)就可以獲得到這樣的結(jié)果。

1. group_concat只有與group by語句同時使用才能產(chǎn)生效果。

2. 需要將拼接的結(jié)果去重的話，可與DISTINCT結(jié)合使用即可。

SELECT

DISTINCT o.id_,o.order_sn,o.create_time,o.wait_out_storage_total,o.back,group_concat(og.goods_name) AS goods_names ,o.store_title FROM wms_orders o LEFT JOIN wms_orders_goods og ON o.id_=og.order_id WHERE o.wait_out_storage_total>0

GROUP BY o.id_;

15、查詢兩門及其以上不及格課程的同學(xué)的學(xué)號，姓名及其平均成績

Select

s1.s_id,s1.name,round(avg(s2.score),2) as avg_score

Student s1 join score s2

On s1.s_id = s2.s_id

where s2.score < 60

Gruop by s1.s_id,s1.name

Having count(1) >=2

15.查詢比30部門最高薪資的人薪資更高的所有員工信息

SELECT *

FROM emp

WHERE sal >

(SELECT MAX(sal) FROM emp WHERE deptno = '30')

查詢比30部門所有人薪資都高員工信息

SELECT *

FROM emp

WHERE sal >

ALL (SELECT sal FROM emp WHERE deptno = '30')

兩句話一樣的查詢，函數(shù)不一樣，函數(shù)放的位置不一樣

- 16、檢索"01"課程分?jǐn)?shù)小于60，按01分?jǐn)?shù)降序排列的學(xué)生信息

SELECT s.* ,s1.score

from student s ,score s1

Where s.s_id = s1.s_id and s1.c_id = ‘01’and s1.score < 60

Order by s1.score

- 17、按平均成績從高到低顯示所有學(xué)生的所有課程的成績以及平均成績

(Select s_id ,sum(score) as sum_score ,round(avg(acore),2) as avg_score

From score s

Group by s_id) #有成績的有選課的同學(xué)

Union

(Select s_id ,0 as sum_score ,0 as svg_score

From student s1 where s1.s_id not in (select distinct s_id from score)

每門課的成績和平均成績

Select

s.s_id,s.name,

sum(case when s1.c_id = ‘01’then s1.score else 0 end ) as ‘語文’，

sum(case when s1.c_id = ‘02’then s1.score else 0 end ) as ‘?dāng)?shù)學(xué)’，

sum(case when s1.c_id = ‘03’then s1.score else 0 end ) as ‘英語’，

round(avg(acore),2) as avg_score

From student s left join score s1

Group by s1.s_id

Order by avg_score desc

-- 18.查詢各科成績最高分、最低分和平均分：以如下形式顯示：課程ID，課程name，最高分，最低分，平均分，及格率，中等率，優(yōu)良率，優(yōu)秀率

--及格為>=60，中等為：70-80，優(yōu)良為：80-90，優(yōu)秀為：>=90

Select

S.C_id,c.name

Max(score) as max_score,

Min(score) as min_score,

Round(avg(score),2)as avg_score,

Round(Sum(case when score >= 60 then 1 else 0 end )/count(1),2) as jige_rate,

Round(Sum(case when score >=70 and score < 80then 1 else 0 end )/count(1),2) as mid_rate,

Round(Sum(case when score >= 80 and score < 90 then 1 else 0 end )/count(1),2) as lianghao_rate,

Round(Sum(case when score >= 90 then 1 else 0 end )/count(1),2) as youxiu_rate,

From score s left join course c

Group by s.c_id,c.name

-- 19、按各科成績進行排序，并顯示排名(實現(xiàn)不完全)

Select

@i := @i+1 as index,

@k := (case when @score = a.score then @k else @i end ) as rank

@score : = a.score as 01_score

From (select s_id,c_id,s_score from score WHERE c_id='01' GROUP BY s_id,c_id,s_score ORDER BY s_score DESC)

a,(select @k:=0,@i:=0,@score:=0)s

Union

................

網(wǎng)易mysql

1、好評率是會員對平臺評價的重要指標(biāo)。現(xiàn)在需要統(tǒng)計2018年1月1日到2018年1月31日，用戶'小明'提交的母嬰類目"花王"品牌的好評率(好評率=“好評”評價量/總評價量):

用戶評價詳情表：a

字段：id(評價id，主鍵)，create_time(評價創(chuàng)建時間，格式'2017-01-01')， user_name(用戶名稱)，goods_id(商品id，外鍵) ，

sub_time(評價提交時間，格式'2017-01-01 23:10:32')，sat_name(好評率類型，包含：“好評”、“中評”、“差評”)

商品詳情表：

b 字段：good_id(商品id，主鍵)，bu_name(商品類目), brand_name(品牌名稱)

Select

A. user_name ,b.bu_name ,brand_name,

Round(sum(Case when a.sat_name = ‘好評’then 1 esle 0 end) / count(sat_name) ,2) as ‘好評率’

From a left join b on a.goods_id = b.good_id

Where b.bu_name = ‘母嬰’and brand_name = ‘花王’

And a.user_name = ‘小明’and a.sub_time between '2018-01-01' and

'2018-01-31'

2、考拉運營"小明"負(fù)責(zé)多個品牌的銷售業(yè)績，請完成：

(1)請統(tǒng)計小明負(fù)責(zé)的各個品牌，在2017年銷售最高的3天，及對應(yīng)的銷售額。

銷售表 a:

字段：logday(日期，主鍵組)，SKU_ID(商品SKU，主鍵組)，sale_amt(銷售額)

商品基礎(chǔ)信息表 b:

字段：SKU_ID(商品SKU，主鍵),bu_name(商品類目)，brand_name(品牌名稱)，user_name(運營負(fù)責(zé)人名稱)

(2)請統(tǒng)計小明負(fù)責(zé)的各個品牌，在2017年連續(xù)3天增長超過50%的日期，及對應(yīng)的銷售額。

###通過查詢類容創(chuàng)建表###################################

1

Create table if not exists temp

as

(Select a.* ,b.*

From a left join b on a.SKU_ID = b.SKU_ID

Where year(a.logday) = ‘2017’and b.user_name = ‘小明’)

Select a.*

From temp a

Where

(select count(b.sale_amt) from temp b where a.sale_amt < b.sale_amt and a.bu_name = b.bu_name and a.brand_name = b.brand_name ) < 3

Order by a.bu_name, a.brand_name,a.sale_amt

2.

Select

Distinct a.logday ,a.sale_amt ,a.bu_name ,a.brand_name

From temp a,temp b ,temp c

Where

a. bu_name = b.bu_name and a.brand_name = b.brand_name and

b. bu_name = b.bu_name and c.brand_name = b.brand_name

and

(a. sale_amt * 1.5 < b.sale_amt and b.sale_amt * 1.5 < c.sale_amt

Date_add(a.logday,interval 1 day) = b.logday and

Date_add(b.logday,interval 1 day) = c.logday )

Or

(b. sale_amt * 1.5 < a.sale_amt and a.sale_amt * 1.5 < c.sale_amt

And Date_add(b.logday,interval 1 day) = a.logday and

Date_add(a.logday,interval 1 day) = c.logday )

)

or

(b.sale_amt * 1.5 < c.sale_amt and c.sale_amt * 1.5 < a.sale_amt

Date_add(b.logday,interval 1 day) = c.logday and

Date_add(c.logday,interval 1 day) = a.logday )

)

Order by a.bu_name, a.brand_name,a.sale_amt

查詢student表中重名的學(xué)生，結(jié)果包含id和name，按name,id升序

Select s1.name ,s1.id

From student s1 ,student s2

Where s1.name = s2.name and s1.id != s2.id

Order by s1.name ,s1.id

select id,name

from student

where name in (select name from student group by name having(count(*) > 1)

) order by name,id;

查詢student表中重名的學(xué)生，保留id小的

Select s1.name ,s1.id

From student s1 ,student s2

Where s1.name = s2.name and s1.id < s2.id

Order by s1.name ,s1.id

Select s.name ,min(s.id) as id

From student s

Group by s.name

Order by s.name ,s.id

Delete

s1.name ,s1.id

From student s1 ,student s2

Where s1.name = s2.name and s1.id > s2.id

Order by s1.name ,s1.id

總成績最高的學(xué)生，結(jié)果列出學(xué)生id、姓名和總成績

Select s.s_id ,s.name,sum(score) as sum_score

From student s join score s1 on s.s_id = s1.s_id

Group by s.s_id

Having sum(s1.score) >= (select max(sum(score))from score s2 group by s_id )

Select s.s_id ,s.name,sum(score) as sum_score

From student s join score s1 on s.s_id = s1.s_id

Group by s.s_id,s.name

order by sum_score desc limit 1

在student_course表查詢課程1成績第2高的學(xué)生，如果第2高的不止一個則列出所有的學(xué)生

Select a.* from

(Select s.* ,s1.score

From (student s join score s1 on s.s_id = s1.s_id )

join course c on s1.c_id = c.c_Id and c_c_id = ‘01’

###多次的join

##或者 之前的作為表temp，score s1 where temp.s_id = s1.s_Id

)a

Where

(select count(distinct score) from

(Select s.* From student s join course c on s.c_id = c.c_id and c_c_id = ‘01’)b

Where a.score < b.score ) =1

新思路： 對成績分組

Select st.*,sc.score

From student st join score sc on st.s_id = sc.s_id

Where sc.score =

(Select s1.score

From (score s join course c on s.c_id = c.c_id and c.c_id = ‘01’)

join student s1 on s.s_id = s1.s_Id

Group by s1.score

Order by s1.score desc

Limit 1,1)

總結(jié)

以上是生活随笔為你收集整理的mysql 列名能不能写成col1、col2_Mysql 寒假刷题TIPs的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。