时间:2021-07-01 10:21:17 帮助过:72人阅读
SELECT Name,Age,Gender FROM students WHERE Age>20 AND Gender=‘F‘;
SELECT Name,Age,Gender FROM students WHERE Age>20 OR Gender=‘F‘;
SELECT Name,Age,Gender FROM students WHERE NOT Age>20 ‘;
SELECT Name,Age,Gender FROM students WHERE NOT Age>20 AND NOT Gender=‘F‘;
SELECT Name,Age,Gender FROM students WHERE NOT ( Age>20 OR Gender=‘F‘ );
2.3.BETWEEN ... AND ...#在AND两值之间
SELECT Name,Age FROM students WHERE Age>=20 AND Age<=25;
SELECT Name,Age FROM students WHERE Age BETWEEN 20 AND 25;
2.4.LIKE ‘‘
%: 任意长度任意字符
_:任意单个字符
SELECT Name FROM students WHERE Name LIKE ‘Y%‘;
SELECT Name FROM students WHERE Name LIKE ‘Y____‘;
SELECT Name FROM students WHERE Name LIKE ‘%willow%‘;
2.5.REGEXP, RLIKE(正规表达式)
SELECT Name FROM students WHERE Name RLIKE ‘^[LNY].*$‘;
2.6.IN(值1,值2,...)
SELECT Name,Age FROM students WHERE Age IN (20,30,40);
2.7.IS NULL
SELECT Name FROM students WHERE CID2 IS NULL;
2.8.IS NOT NULL
SELECT Name FROM students WHERE CID2 IS NOT NULL;
2.9.ORDER BY 字段名 {ASC|DESC} #排序,默认为升序
SELECT Name FROM students WHERE CID2 IS NULL ORDER BY Name;
SELECT Name FROM students WHERE CID2 IS NULL ORDER BY Name DESC;
2.10.AS,引用字段别名或表别名
SELECT Name AS student_name FROM students;
2.11.LIMIT子句:LIMIT [偏移行,]显示行
SELECT Name AS student_name FROM students LIMIT 2;
SELECT Name AS student_name FROM students LIMIT 2,3;
2.12.聚合:SUM(), MIN(), MAX(), AVG(), COUNT()
SELECT AVG(age) FROM students;
SELECT MAX(age) FROM students;
SELECT MIN(age) FROM students;
SELECT SUM(age) FROM students;
SELECT COUNT(age) FROM students;
2.13.GROUP BY: 分组
SELECT AVG(age) FROM students WHERE Gender=‘M‘;
SELECT AVG(age) FROM students WHERE GROUP BY Gender;
SELECT COUNT(CID1) AS Persons,CID1 FROM students GROUP BY CID1;
2.14.HAVING 条件 (必须与GROUP BY一起使用)
SELECT COUNT(CID1) AS Persons,CID1 FROM students GROUP BY CID1 HAVING Persons>=2;
3.多表查询:
3.1.交叉连接:笛卡尔乘积
SELECT * FROM students,courses;
3.2.自然连接:
SELECT * FROM students,courses WHERE students.CID1 = courses.CID;
SELECT students.Name,courses.Cname FROM students,courses WHERE students.CID1 = courses.CID;
SELECT s.Name,c.Cname FROM students AS s,courses AS c WHERE s.CID1 = c.CID;
3.3.左外连接:... LEFT JOIN ... ON ...#以左表为基准
SELECT s.Name,c.Cname FROM students AS s LEFT JOIN courses AS c ON s.CID1 = c.CID;
3.4.右外连接: ... RIGHT JOIN ... ON ...#以右表为基准
SELECT s.Name,c.Cname FROM students AS s RIGHT JOIN courses AS c ON s.CID1 = c.CID;
3.5.自连接:同一张表中的自连接
SELECT c.Name As student,s.Name AS teacher FROM students AS c,students AS s WHERE c.TID=s.SID;
4.子查询:
4.1.比较操作中使用子查询:子查询只能返回单个值;
SELECT Name FROM students WHERE Age > (SELECT AVG(age) FROM students);
4.2.IN(): 使用子查询;
SELECT Name FROM students WHERE Age IN (SELECT Age FROM tutors);
4.3.在FROM中使用子查询;
SELECT Name,Age FROM (SELECT Name,Age FROM students) AS s WHERE s.Age >= 20;
4.4.联合查询:UNION
(SELECT Name,Age FROM students) UNION (SELECT Tname,Age FROM tutors);
5.综合复杂查询案例:
5.1.找出courses表中没有被students选中的CID2学习的课程的课程名称;
SELECT Cname FROM courses WHERE CID NOT IN (SELECT DISTINCT CID2 FROM students WHERE CID2 IS NOT NULL);
5.2.找出没有教授任何课程的老师
SELECT Tname FROM tutors WHERE TID NOT IN (SELECT DISTINCT TID FROM courses);
找出students表中CID1有两个(含)以上同学学习了的同一个门课程的课程名称
SELECT Cname FROM courses WHERE CID IN (SELECT CID1 FROM students GROUP BY CID1 HAVING COUNT(CID1) >= 2);
5.3.找出每一位老师及其所教授的课程;没有教授的课程的保持为NULL
SELECT t.Tname,c.Cname FROM tutors AS t LEFT JOIN courses AS c ON t.TID=c.TID;
5.4.找出每一个课程及其相关的老师,没有老师教授的课程将其老师显示为NULL
SELECT t.Tname,c.Cname FROM tutors AS t RIGHT JOIN courses AS c ON t.TID=c.TID;
5.5.找出每位同学CID1课程的课程名及其讲授了相关课程的老师的名称
SELECT Name,Cname,Tname FROM students,courses,tutors WHERE students.CID1=courses.CID;
6.视图:就是存储下来的SELECT语句,即基于基表的查询结果;
6.1.创建视图:
CREATE VIEW 视图名称 AS SELECT语句;
CREATE VIEW sct_viewname AS SELECT Name,Cname,Tname FROM students,courses,tutors WHERE students.CID1=courses.CID;
SELECT * FROM sct_viewname;
SHOW TABLES;可查看到此视图当成表
SHOW TABLE STATUS\G
6.2.删除视图:
DROP VIEW 视图名称
6.3.查看创建视图命令
SHOW CREATE VIWE 视图名称;
6.4.查看创建表命令
SHOW CREATE TABLE 表名称;
本文出自 “夏维柳” 博客,请务必保留此出处http://willow.blog.51cto.com/6574604/1795548
linux下mysql Select查询命令
标签:linux下mysql select查询命令及视图