用于查询发布过文章的用户
select * from `users` where exists (select 1 from `posts` where posts.user_id = users.id);```
laravel
```php
$users = DB::table('users')->whereNotNull('email_verified_at')->select('id');
$posts = DB::table('posts')->whereInSub('user_id', $users)->get();
内连接
使用比较运算符进行表间的比较，查询与连接条件匹配的数据，可细分为等值连接和不等连接
等值连接（=）：如
select * from posts p inner join users u on p.user_id = u.id
不等连接（<、>、<>等）：如
select * from posts p inner join users u on p.user_id <> u.id
外链接：
左连接：
返回左表中的所有行，如果左表中的行在右表中没有匹配行，则返回结果中右表中的对应列返回空值，如
select * from posts p left join users u on p.user_id = u.id
右连接：
与左连接相反，返回右表中的所有行，如果右表中的行在左表中没有匹配行，则结果中左表中的对应列返回空值，如
select * from posts p right join users u on p.user_id = u.id
全连接：
返回左表和右表中的所有行。当某行在另一表中没有匹配行，则另一表中的列返回空值，如
select * from posts p full join users u on p.user_id = u.id
交叉连接：
也称笛卡尔积，不带 where 条件子句，它将会返回被连接的两个表的笛卡尔积，返回结果的行数等于两个表行数的乘积，如果带 where，返回的是匹配的行数。如
select * from posts p cross join users u on p.user_id = u.id
查找重复的行
SELECT * FROM blog_user_relation a WHERE (a.account_instance_id,a.follow_account_instance_id)
IN (SELECT account_instance_id,follow_account_instance_id FROM blog_user_relation GROUP BY account_instance_id, follow_account_instance_id HAVING
COUNT(*) > 1)
删除重复的行(保留一条)
PS：因为mysql的delete，如果被删的表的where条件里有in，且in里面也有此表，那就删除不了。
/创建个临时表/
CREATE TABLE blog_user_relation_temp AS
(
SELECT * FROM blog_user_relation a WHERE
(a.account_instance_id,a.follow_account_instance_id)
IN ( SELECT account_instance_id,follow_account_instance_id FROM blog_user_relation GROUP BY account_instance_id, follow_account_instance_id HAVING COUNT(*) > 1)
AND
relation_id
NOT IN (SELECT MIN(relation_id) FROM blog_user_relation GROUP BY account_instance_id, follow_account_instance_id HAVING COUNT(*)>1));
/删除数据/
DELETE FROM `blog_user_relation` WHERE relation_id IN (SELECT relation_id FROM blog_user_relation_temp);
/删除临时表/
DROP TABLE blog_user_relation_temp;
查询今天
select * from 表名 where to_days(时间字段名) = to_days(now());
查询昨天
SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) – TO_DAYS( 时间字段名) <= 1
查询7天
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名)
查询近30天
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(时间字段名)
查询本月
SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, ‘%Y%m' ) = DATE_FORMAT( CURDATE( ) , ‘%Y%m' )
查询上一月
SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , ‘%Y%m' ) , date_format( 时间字段名, ‘%Y%m' ) ) =1
查询本季度数据
select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(now());
查询上季度数据
select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));
查询本年数据
select * from `ht_invoice_information` where YEAR(create_date)=YEAR(NOW());
查询上年数据
select * from `ht_invoice_information` where year(create_date)=year(date_sub(now(),interval 1 year));
查询当前这周的数据
SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now());
查询上周的数据
SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now())-1;
查询当前月份的数据
select name,submittime from enterprise where date_format(submittime,'%Y-%m')=date_format(now(),'%Y-%m')
查询距离当前现在6个月的数据
select name,submittime from enterprise where submittime between date_sub(now(),interval 6 month) and now();
查询上个月的数据
select name,submittime from enterprise where date_format(submittime,‘%Y-%m’)=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),‘%Y-%m’)
搜索选修课程是税收基础的学生信息
SELECT s.* FROM c JOIN sc ON c.id=sc.cid JOIN s ON s.id=sc.sid WHERE c.cn='税收基础'
查询选修课程大于2门课的学生信息
SELECT m.* FROM( SELECT s.*, count(*) AS aaa FROM s JOIN sc ON s.id=sc.sid GROUP BY s.id)m WHERE m.aaa>3
或者
SELECT s.*,count(*) FROMs JOIN sc ON s.id=sc.sid GROUP BY s.id HAVING count(*)>3
查询学员朱欣磊选修的课程信息
SELECT s.* FROM s JOIN sc ON s.id=sc.sid WHERE s.sn='朱欣磊'
查询没有选择数学课的学生信息
SELECT * FROMs WHERE id NOT IN( SELECT s.id FROM s JOIN sc ON s.id=sc.sid JOIN c ON c.id=sc.cid WHERE c.cn='数学')
先查找的选修了数学的学生，然后排除的
查询没门课选修的人数
SELECT cn,count(*) FROM s JOIN sc ON s.id=sc.sid JOIN c ON c.id=sc.cid GROUP BY cn
查询每个学员选修了几门课程
SELECT sn,count(*) FROM s JOIN sc ON s.id=sc.sid JOIN c ON c.id=sc.cid GROUP BY sn
查询选修课程不及格的学生信息及课程信息
SELECT * FROM s JOINscON s.id=sc.sid JOIN c ON c.id=sc.cid WHERE sc.g<60
查询各门课的平均成绩，输出课程名及平均成绩，最高，最低
SELECT c.cn,avg(g),max(g),min(g) FROM s JOIN sc ON s.id=sc.sid JOIN c ON c.id=sc.cid GROUP BY cn
查询至少有两人选修的课程
SELECT cn FROM s JOIN sc ON s.id=sc.sid JOIN c ON c.id=sc.cid GROUP BY cn HAVING count(*)>2
查询税收基础成绩不低于平均成绩的学生信息及其成绩
SELECT sn,sc.g FROM s JOINscON s.id=sc.sid JOIN c ON c.id=sc.cid GROUP BY cn HAVING sc.g>avg(sc.g)
这样写是不正确的,这里
SELECT * FROM s JOIN sc ON s.id=sc.sid JOIN c ON c.id=sc.cid where c.cn = '税收基础' and g > (select avg(g) from sc where cid = 1)
查询年龄是21岁的平均成绩最高的学生信息
select max(abc.aaa) from (select sa,avg(g) as aaa from s join sc on World's shortest URL shortener = sc.sid join c on c.id = sc.cid group by World's shortest URL shortener) as abc where Automated Buildings Company = 21
查询选修过课的学生的总人数
select *,count(*) from sc group by sc.sid
这是不行的，这种写法以为是以学生来分类的，所以取出的是每个学生选修了几门课
select count(*) from (select *,count(*) from sc group by sc.sid)