SQL基础语句
判断字段长度函数length
SELECT name,capital
FROM world
WHERE LENGTH(name)=LENGTH(capital)
求字串,注意分左字串和右字串
LEFT(ARG,LENGTH)、RIGHT(ARG,LENGTH)
LEFT、RIGHT函数返回ARG最左边、右边的LENGTH个字符串,ARG可以是CHAR或BINARY STRING。
SELECT name,capital
FROM world
WHERE LEFT(name,1)=LEFT(capital,1) and name!=capital
保留小数点ROUND() 语法
SELECT ROUND(column_name,count)
FROM table_name
column_name字段,保留count个小数个数
1、表 nobel(yr,subject,winner)
显示1984年获奖者和主题按主题和获胜者名称排序; 但最后列出化学和物理
SELECT winner, subject
FROM nobel
WHERE yr=1984
ORDER BY subject IN ('Physics','Chemistry'),subject,winner
2、表 world(name,continent,area,population,gdp)
PS:下面的四个例子的子查询可以看出是group by continent,子查询where后面的and条件就是对分组里的内容进行筛选。然后主查询就对每一个分组进行操作(目前是这么理解,对不对不知道)
(1)在每一個州中找出最大面積的國家,列出洲份 continent, 國家名字 name 及面積 area。 (有些國家的記錄中,AREA是NULL,沒有填入資料的。)
SELECT continent, name, area FROM world x
WHERE area >= ALL
(SELECT area FROM world y //子查询相当于查找每一个州的所有area
WHERE y.continent=x.continent
AND area>0)
(2)列出洲份名稱,和每個洲份中國家名字按子母順序是排首位的國家名。(即每洲只有列一國)
SELECT continent,name FROM world x
WHERE x.name=(SELECT y.name FROM world y WHERE y.continent=x.continent ORDER BY name LIMIT 1);//找出每一个州的排在最前面的国家名称
/* 其中LIMIT用于SELECT语句中,可以强制返回指定的记录数.如果写作
LIMIT n(补全为 LIMIT 0,n),则表示显示前n条记录
*/
(3)找出洲份,當中全部國家都有少於或等於 25000000 人口. 在這些洲份中,列出國家名字name,continent 洲份和population人口。
SELECT name,continent,population FROM world x
WHERE 25000000>=ALL(SELECT population FROM world y WHERE //子查询相当于找到每个州的所有population
y.continent=x.continent AND population>0
);
(4)有些國家的人口是同洲份的所有其他國的3倍或以上。列出 國家名字name 和 洲份 continent。
SELECT name,continent FROM world x
WHERE x.population / 3 >= ALL(SELECT population FROM world y //子查询相当于找到每一个州的所有population,但是所找的population符合and后面的条件
WHERE y.continent = x.continent AND population >0 AND y.name != x.name
);
2、表Student(S,Sname,Sage,Ssex),表Course(C,Cname,T) ,表Teacher(T,Tname),表SC(S,C,score)
(1)查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息?
select distinct student.*
from sc join student on student.S=sc.S
where C in(
select C
from student join sc on student.S=sc.S
where sc.S='01'
)
and student.S!='01'
(2)查询和" 01 "号的同学学习的课程完全相同的其他同学的信息?(双重肯定为否定)SELECT *
FROM student
WHERE S IN (
SELECT S #查询选修的课程是01号学生选修课程的子集的学生编号:
FROM sc
WHERE S NOT IN (
SELECT S #查询出选修了01号学生没有选修课程的学生编号:
FROM sc
WHERE C NOT IN (
SELECT C #查询出01号学生选修的所有课程的编号:
FROM sc
WHERE S = '01'
)
)
GROUP BY S HAVING count(*) = ( SELECT count(*) FROM sc WHERE S = '01') AND S != '01' #从上述查询结果中,筛选出选修的课程数量与01号学生选修的课程数量相等的其他学生的编号:
);
3、
(1)每个部门工资最高的员工
方法一:
SELECT
Department.name AS 'Department',
Employee.name AS 'Employee',
Salary
FROM
Employee
JOIN
Department ON Employee.DepartmentId = Department.Id
WHERE
(Employee.DepartmentId , Salary) IN
( SELECT
DepartmentId, MAX(Salary)
FROM
Employee
GROUP BY DepartmentId
)
方法二:
select Department.Name Department,e1.Name Employee,e1.Salary Salary
from Employee e1 join Department on DepartmentId=Department.Id
where Salary >=ALL(
select Salary
from Employee e2
where e1.DepartmentId=e2.DepartmentId
)
(2)每个部门工资前三高的员工
SELECT d.Name AS 'Department', e1.Name AS 'Employee', e1.Salary
FROM Employee e1 JOIN Department d ON e1.DepartmentId = d.Id
WHERE 3 > (
SELECT COUNT(DISTINCT e2.Salary)
FROM Employee e2
WHERE e2.Salary > e1.Salary AND e1.DepartmentId = e2.DepartmentId
);
取分组前几的代码
SELECT e1.Salary
FROM Employee AS e1
WHERE 3 >
(SELECT count(DISTINCT e2.Salary)
FROM Employee AS e2
WHERE e2.Salary >e1.Salary AND e2.DepartmentId = e1.DepartmentId
) ;