所有数据数据示例均为ch3.导入sql脚本
mysql -u 用户名 -p <路径\ch3.sql
数据集导出:mysql -u 用户名 -p 数据集名(DataBase)> 路径\名.sql
查询指定列:
SELECT Sno, Sname FROM Student;
查询所有列:
SELECT * FROM Student;
带条件查询:
比较大小:
SELECT * FROM Student WHERE Sage<20;
等于某个值:
SELECT * FROM Student WHERE Sage=20;
SELECT * FROM Student WHERE Sname="张三";
确定范围:
SELECT * FROM Student WHERE Sage<20 and Sage>10;
SELECT * FROM Student WHERE Sage BETWEEN 11 AND 19;
(上面两条语句执行结果相同,说明BETWEEN ...AND会取两端的值)
确定集合:
SELECT * FROM Student WHERE Sdept IN ('CS', 'MA', 'IS');
SELECT * FROM Student WHERE Sdept NOT IN ('CS', 'MA', 'IS');
字符串匹配:
SELECT * FROM Student WHERE Sno LIKE '200215121';
SELECT * FROM Student WHERE Sname LIKE '刘%'
匹配规则:
“%”匹配任意长度任意字符
“_”匹配一个字符,可以重复使用以匹配指定长度
“\”去除匹配字符的特殊性,如:"\%"即匹配%这个字符,需要匹配\时,可以使用\\以去除\的特殊性,或者使用ESCAPE指定字符达到\的效果
如:SELECT * FROM Student WHERE Sname LIKE "李L_华" ESCAPE 'L';
(LIKE 后接的字符串不区分大小写)
正则匹配
SELECT * FROM Student WHERE Sname REGEXP '刘' ;
. | 匹配一个任意字符 | '.'; 匹配任意长度的任意字符穿 '刘'; 匹配包含刘的任意字符串 |
^ | 匹配以该表达式开头的字符串 | '^刘'匹配所有姓刘的人,长度不限 |
$ | 匹配以该表达式结尾的字符串 | '伟$'匹配所有以伟结尾的字符串 |
+ | 匹配前面的表达式1次或多次 | '^刘.+伟$' 匹配姓刘且以伟结尾名字至少为3个字的人 '刘.+’匹配所有包含刘且刘不为最后一个字符的字符串 |
* | 匹配前面的表达式0次或多次 | '^刘.*伟$'匹配姓刘且以伟结尾名字至少为2个字的人 |
[] | 字符集合 | [abc]在[]里选择字符匹配,即匹配包含abc中任意一个字符的字符串 [^abc]匹配未包含包含abc的任意字符的字符串,即字符串不能只由abc组成,还应有其他字符
|
{} | 匹配次数 | {n}匹配n次,{n,m}至少匹配n次,至多匹配m次 '^刘.{2}'匹配姓刘的且名字为3个字的人=='^刘..'
|
| | 或运算 | 'a|b'匹配包含a或b的任意字符 |
() | 表达式形式 | '(a|b)ood' 匹配包含aood或者bood的任意字符 |
\\ | 取消字符特殊性 | ‘\\^’匹配包含^的字符串 |
涉及空值的查询:
SELECT * FROM Student WHERE Sdept IS NULL;
SELECT * FROM Student WHERE Sdept !=NULL;
(当判断是否为空值时,只能用is, 不能使用等于号)
多条件查询:
多个条件的链接使用 AND 和 OR,作用同意思;
SELECT * FROM Student WHERE Sdept='CS' OR Sdept='MA';
SELECT * FROM Student WHERE Sage>10 AND Sage<20;
查询之后按照某个规则显示:
SELECT * FROM Student WHERE Sage>10 LIMIT N OFFSET M; 取子集中第M+1个到M+N+1个
使用排序之后可以取第n个或者第n~n+m个;
按照某一列进行排序:
SELECT * FROM Student ORDER BY Sage ASC; 升序排列(默认为升序,即不加ASC)
SELECT * FROM Student ORDER BY Sage DESC; 降序排列
SELECT * FROM Student ORDER BY Sage, Sname ASC;当排序有两个及以上列时,先所有行按最左边的列(Sage)排序,再在每一个Sage里的sname排序,以此类推。
聚集函数:
SELECT COUNT(Sno) FROM Student; 获取某一列的元素的个数
类似的还有,MAX, MIN, SUM, AVG
group by子句:
SELECT Cno, COUNT(Cno) FROM Student GROUP BY Cno;
SELECT Cno, COUNT(Cno) FROM Student GROUP BY Cno HAVING COUNT(*) >1;
返回按照某一列分类后的结果,having类似于where 设定某一个条件。通常和聚集函数一起使用!
连接查询
等值与非等值查询
SELECT Student.*, SC.* FROM Student, SC WHERE Student.Sno=SC.Sno;
自身连接
SELECT C1.Cno, C2.Cno FROM Coure c1, Course C2 WHERE C1.Cpno=C2.Cno;
外连接
左外连接C1 LEFT JOIN C2,以C1为标准,将C2连接到C1
SELECT Student.Sno , Sname, Sage, Sdept, Cno, Grade FROM Student LEFT JOIN SC ON(Student.Sno=SC.Sno);
SELECT Student.Sno , Sname, Sage, Sdept, Cno, Grade FROM Student LEFT JOIN SC USING(Sno);
复合条件连接
SELECT Student.Sno, Sname FROM Student, SC, Course WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno;
嵌套查询
带有谓词 IN 的子查询
SELECT Sno, Sname, Sdept FROM Student WHERE Sdept IN (SELECT Sdept FROM Student WHERE Sname='张三');
SELECT Sno, Sname FROM Student WHERE Sno IN (SELECT Sno FROM SC WHERE Cno IN= (SELECT Cno FROM Course WHERE Cname = '信息系统' ) );
带有比较运算符的子查询
SELECT Sno, Cno FROM SC x WHERE Grade >= ( SELECT AVG(Grade) FROM SC y WHERE y.Sno=x.Sno );
注:在嵌套查询里使用的表和主查询的表一样时,为防止查询混乱,需要起别名,自身连接也是一样的
带有 ANY (SOME) 或 ALL 谓词的子查询
SELECT Sname, Sage FROM Student WHERE Sage < ANY ( SELECT Sage FROM Student WHERE Sdept='CS');
SELECT Sname, Sage FROM Student WHERE Sage < ALL ( SELECT Sage FROM Student WHERE Sdept='CS');
带有EXISTS谓词的子查询
判断子查询是否为空
SELECT Sname FROM Student WHERE EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno='1');
SELECT DISTINCT Sno FROM SC SCX WHERE NOT EXISTS (SELECT * FROM SC SCY WHERE SCY.Sno='200215122' AND NOT EXISTS (SELECT * FROM SC SCZ WHERE SCZ.Sno=SCX.Sno AND SCZ.Cno=SCY.Cno));
集合查询
并集
SELECT * FROM Student WHERE Sdept='CS' UNION SELECT * FROM Student WHERE Sage<=19;