##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)