linux下mysql Select查询命令及视图

534242

• 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.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 表名称;