##mysql數據庫事務###
##表的創建、更新、修改
28、 創建表
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、 創建索引
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、 觸發器
Create triggle audit_log
After insert into employee_test
Begin insert into audit
Values (NEW.id,NEW.NAME)
END
41、 刪除重複記錄，保留小的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、 創建外檢約束
Alter table audit add foreign key(emp_no)
Reference emplyees_test(id)
46、 選出和視圖一樣的數據
Select em.* from emplyees as em,emp_v as ev
Where em.emp_no = ev.emp_no
Select * from emp_v
47、 獲獎員工當前工資增加
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
只修改列的數據類型的方法:
通常可以寫成 alter table 表名 modify column 列名 新的列的類型
例如:student表中列sname的類型是char(20),現在要修改爲varchar(20),SQL語句如下
alter table student modify column sname varchar(20);
同時修改列名和列的數據類型的方法:
通常可以寫成 alter table 表名 change column 舊列名 新列名 新的列類型
例如:student表中列sname的類型是char(20),現在要修改爲stuname varchar(20),SQL語句如下
alter table student change column sname stuname varchar(20);
###數據庫查詢
1、查詢”01″課程比”02″課程成績高的學生的信息及課程分數
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課程成績的同學#####
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″課程成績高條件的學生 所有功課
Select st.* ,’語文’,’數學’,’英語
’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 ‘數學’, ##選擇temp 02成績作爲數學成績
Sum(case sc.c_id = ‘03’ then sc.score else 0 end) as ‘英語’,
From score sc
Join
###滿足條件的學生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課程成績的同學#####
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″課程成績低的學生的信息及課程分數
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<c.s_score
— 3、查詢平均成績大于等于60分的同學的學生編號和學生姓名和平均成績
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分的同學的學生編號和學生姓名和平均成績
— (包括有成績的和無成績的)
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 版本優化了，可以再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. 分數相同rank相同，rank考慮是有多少人比你分數高
#先進行子查詢，每一條外查詢都跑一輪子查詢
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.分數相同rank相同，rank考慮是有多少分數比你分數高
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、查詢各學生的年齡
— 按照出生日期來算，當前月日 < 出生年月的月日則，年齡減一
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)：如果第一個參數不爲空，則返回第一個參數，否則返回第二個參數。
ISNULL(expr)：判斷是否是空，是空則返回1，否則返回0。
IF(expr1,expr2,expr3)：如果第一個表達式的值爲TRUE（不爲0或null），則返回第二個參數的值，否則返回第三個參數的值。
— 5、查詢所有同學的學生編號、學生姓名、選課總數、所有課程的總成績
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、查詢學過”張三”老師授課的同學的信息
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、查詢學過編號爲”01″並且也學過編號爲”02″的課程的同學的信息
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、查詢學過編號爲”01″並且”02″的同學所選的課程的同學的信息
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、查詢學過編號爲”01″但是沒有學過編號爲”02″的課程的同學的信息
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、查詢沒有學全所有課程的同學的信息
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、查詢至少有一門課與學號爲”01″的同學所學相同的同學的信息
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’)[寫錯了，一個同學出現了很多條記錄]
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″號的同學學習的課程完全相同的其他同學的信息
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 刪除重複郵箱，保留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、查詢學生的總成績並進行排名
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函數
mysql中的函數，字符串拼接的話，可以用concat()，但是此函數是針對一條記錄中，可以將不同的字段拼接，並不適用多條記錄的某一字段。查了一下，mysql中group_concat函數就可以獲得到這樣的結果。
1. group_concat只有與group by語句同時使用才能産生效果。
2. 需要將拼接的結果去重的話，可與DISTINCT結合使用即可。
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、查詢兩門及其以上不及格課程的同學的學號，姓名及其平均成績
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’)
兩句話一樣的查詢，函數不一樣，函數放的位置不一樣
– 16、檢索”01″課程分數小于60，按01分數降序排列的學生信息
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、按平均成績從高到低顯示所有學生的所有課程的成績以及平均成績
(Select s_id ,sum(score) as sum_score ,round(avg(acore),2) as avg_score
From score s
Group by s_id) #有成績的有選課的同學
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 ‘數學’，
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，最高分，最低分，平均分，及格率，中等率，優良率，優秀率
–及格爲>=60，中等爲：70-80，優良爲：80-90，優秀爲：>=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、按各科成績進行排序，並顯示排名(實現不完全)
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
…………….
網易mysql
1、好評率是會員對平台評價的重要指標。現在需要統計2018年1月1日到2018年1月31日，用戶’小明‘提交的母嬰類目”花王”品牌的好評率（好評率=“好評”評價量/總評價量）:
用戶評價詳情表：a
字段：id（評價id，主鍵），create_time（評價創建時間，格式’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、考拉運營”小明”負責多個品牌的銷售業績，請完成：
（1）請統計小明負責的各個品牌，在2017年銷售最高的3天，及對應的銷售額。
銷售表 a:
字段：logday（日期，主鍵組），SKU_ID（商品SKU，主鍵組），sale_amt(銷售額)
商品基礎信息表 b:
字段：SKU_ID（商品SKU，主鍵）,bu_name（商品類目），brand_name(品牌名稱)，user_name（運營負責人名稱）
（2）請統計小明負責的各個品牌，在2017年連續3天增長超過50%的日期，及對應的銷售額。
###通過查詢類容創建表###################################
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表中重名的學生，結果包含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表中重名的學生，保留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
總成績最高的學生，結果列出學生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高的學生，如果第2高的不止一個則列出所有的學生
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)