社区微信群开通啦,扫一扫抢先加入社区官方微信群
社区微信群
学习的是YouTube上面的一个up主,B站也有同步课程,有兴趣可以关注一下,视频讲的更加详细。
YouTube链接:https://www.youtube.com/channel/UCRCqgMdsAHK3yfvW4en6JpA
基础知识:
之后会用到的示例数据库:
语法:
SELECT * FROM table_name; --选择所有列(*为通配符)
SELECT column_name1,column_name2 FROM table_name; --选择两列
SELECT DISTINCT column_name FROM table_name; --提取出该列独一无二的数值,去除重复
SELECT column_name FROM table_name LIMIT 5 OFFSET 4 --检索五行,从第四行开始(不包含第4行)
语句结束之后要加分号:
数据库中很少需要检索所有行,通常只会根据指定操作或报告的需要提取表数据的子集。只检索所需要的条件需要指定搜索条件(过滤条件)。
可以使用的操作符有:“=”、“<>” “ !=” “ <” “>” “BETWEEN” “Like” “IN” “IS NULL”等
例:
SELECT * FROM Employee WHERE Salay>70000 AND Salary<85000;--检索工资大于70000且小于85000的行
SELECT * FROM Employee WHERE Salarry IN (70000,85000);-- 检索工资为70000或者85000的行
SELECT * FROM Employee WHERE Name LIKE ‘%an%’; --检索名字中包含an的行
LIKE后面需要写单引号
SELECT DepartmentID,COUNT(Name) FROM Employee GROUP BY DepartmentId;
--COUNT是按照GROUP BY计数的,并不是按照COUNT内的计数,但是输出的列名会是COUNT(Name),也可以对这列重新命名,语句如下:
SELECT DepartmentID,COUNT(Name) AS Number_Empolyee FROM Employee GROUP BY DepartmentId;
--AS 后面跟的是重命名之后的名字,也可以跟其他函数,比如
SELECT DepartmentID,COUNT(Salary) AS Total,MIN(Salary) AS MinSalary,Max(Salary) AS MaxSalary FROM Employee GROUP BY DepartmentId;
SELECT DepartmentID FROM Employee GROUP BY DepartmentId HAVING COUNT(*) >3;
-- 按照DepartmentID,并且选择数量大于3个的。
SELECT Name,Salary,DepartmentId FROM Employee ORDER BY Salary,DepartmentId;
--从小到大排序,根据Salary排序,如果Salary重复或相等,则按照DepartmentId排序。
SELECT Name,Salary,DepartmentId FROM Employee ORDER BY Salary DESC;
--降序排(默认是升序)
表之间的连接方式:
所有内容都是基于以下表:
示例语句:
SELECT Employee.Id AS Id,Employee.Name AS Name,Employee.Salary AS Salary,Employee.DepartmentId AS DepartmentId,Department.Name AS DepartmentName FROM Employee INNER JOIN Department ON Employee.DepartmentId = Department.Id;
--ON后面跟的是条件,选取符合条件的,然后内连接。
执行之后的结果为:
示例:
SELECT Employee.Id AS Id,Employee.Name AS Name,Employee.Salary AS Salary,Employee.DepartmentId AS DepartmentId,Department.Name AS DepartmentName FROM Employee LEFT JOIN Department ON Employee.DepartmentId = Department.Id;
返回结果:
需要注意的点:在写SELECT时,后面要写成table.column,而不是直接写列的名字。比较好的习惯是利用AS重命名一下列名。
RIGHT JOIN
示例:
SELECT Employee.Id AS Id,Employee.Name AS Name,Employee.Salary AS Salary,Employee.DepartmentId AS DepartmentId,Department.Name AS DepartmentName FROM Employee RIGHT JOIN Department ON Employee.DepartmentId = Department.Id;
输出结果:
**OUTER JOIN **
示例:
SELECT title as Position,Name,Salary FROM professor UNION SELECT title as Position,Name,Salary FROM RTA;
结果:
注意:
注意UNION 和 UNION的区别;
列的顺序和数量要一样;
Update 语句用于修改表中的数据。
语法:
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
如果觉得我的文章对您有用,请随意打赏。你的支持将鼓励我继续创作!