linux下mysqlSelect查询命令及视图
linux下mysql Select查询命令
1. SELECT查询格式:
SELECT 字段 FROM 表 WHERE 条件;
例如: SELECT * FROM 表名;          #查询表所有内容
SELECT 字段1，字段2... FROM 表名;      #投影，仅显⽰指定字段中的内容
SELECT [DISTINCT] * FROM 表名 WHERE 条件; #选择，仅显⽰符合条件的所有字段部分⾏内容SELECT * FROM students;
SELECT Name,Age FROM students;
SELECT * FROM students WHERE Age>=20;
SELECT Name,Age FROM students WHERE Age>=20;
SELECT Name,Age FROM students WHERE Gender='F';
SELECT DISTINCT Gender FROM students; #DISTINCT仅显⽰Gender字段中的相同值只显⽰⼀次
2.查询语句类型：简单查询，多表查询,⼦查询
FROM⼦句： 要查询的关系    表、多个表、其它SELECT语句
WHERE⼦句：布尔关系表达式,
2.1.⽐较操作符如下: =、>、>=、<=、<
SELECT Name,Age FROM students WHERE Age+1>20;
2.2.逻辑关系：AND OR NOT
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.1
3.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.查看创建视图命令
select distinct fromSHOW CREATE VIWE 视图名称;
6.4.查看创建表命令
SHOW CREATE TABLE 表名称;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。