mysql常用函数

1.唯一
DISTINCT
2.排序
ORDER BY xxx; 升序
ORDER BY xxx desc; 降序
题目描述

select DISTINCT author_id  as id from Views where author_id = viewer_id ORDER BY author_id;
1

3.length();
字符串长度

select tweet_id from Tweets where length(content) > 15
1

4.左连接
a LEFT JOIN b ON …
left join 是left outer join的简写，它的全称是左外连接，是外连接中的一种。 左(外)连接，左表(a)的记录将会全部表示出来，而右表(b)只会显示符合搜索条件的记录。右表记录不足的地方均为NULL
右连接相反
a RIGHT JOIN b ON

SELECT 
    EmployeeUNI.unique_id, Employees.name
FROM 
    Employees
LEFT JOIN 
    EmployeeUNI 
ON 
    Employees.id = EmployeeUNI.id;
1
2
3
4
5
6
7
8

//统计学生们参加各科测试的次数题目链接

select 
    Students.student_id ,Students.student_name ,Subjects.subject_name,
    (select
        count(e.subject_name)  
    from
        Examinations as e
    where e.student_id = Students.student_id and e.subject_name = Subjects.subject_name)  as  attended_exams

from 
    Students 
join
    Subjects
group by student_id,subject_name
order by student_id,student_name,subject_name;


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16