mysql查询

rwrr322

查询有多种方式：普通查询，连接查询，子查询查询，集合查询

1.select 查询语句具有5个子句，只有from是必须的。
SELECT [ALL|DISTINCT|DISTINCTROW|TOP]{*|table.*|[table.]field(x)[AS alias(x)]}
FROMtable_expression[, ...][IN external_database]：这里可以有一个JOIN ON
[WHEREsearch_condition] ：条件查询
[GROUP BY group_by_expression]：用于需要使用统计函数时
[HAVING search_condition]：限制GROUP BY的返回组
[ORDER BY order_expression[ASC | DESC]]：对最终的查询结果排序
[WITH OWNERACCESS OPTION]
[LIMIT n]：限制查询结果的返回记录数量
解释：where子句的作用是指定查询条件。Having子句是一个用于组的where子句，having限制组,即限制GROUP BY产生的分组，作用是筛选满足条件的组（条件中经常包含聚组函数）。ORDER BY对查询结果按条件排序。With参数主要有CUBE和ROLLUP，主要功能是对数据进行分析汇总。每一个表都有一个隐藏的字段ROWID，它标记记录的唯一性。

当查询包含where，group by，和统计函数（如sum,avg,max,min,count,stddev,variance）时，执行步骤如下：where子句首先查找所需的行，group by子句将这些行构成组，组形成后，SQL计算组的值，所以使用统计函数必须分组，统计是按组统计的。

2.SQL一些运算符

%求余；+字符拼接，AND与，OR或，NOT非；（与>，<等用法相同）

7种字符匹配逻辑运算符(用在WHERE条件的子查询中)：ALL（如果一组的比较都为TRUE，那么就是TRUE）；ANY（如果一组的比较有一个为TRUE，那么就是TRUE）；BETWEEN...AND...（如果操作数在某个范围内，那么就是TRUE，相当于大于且小于）；EXISTS（如果子查询包含一些行，那么就是TRUE）；IN（如果操作是等于列表中的一个，那么就是TRUE）；LIKE(如果操作数与一种模式相匹配，那么就是TRUE)；SOME（如果在一组比较中，有些为TRUE,那么就是TRUE）

3种集合运算符（针对两个查询结果表）：UNION(并集，通过组合其它两个结果表并消去表中任何重复行而派生出一个结果表)，UNION ALL（当ALL随UNION一起使用时，不消除重复行）；INTERSECT（交集，通过只包含两个表中相同的行并消除重复行而派生出一个结果表），INTERSECT ALL（当ALL随INTERSECT一起使用时，不消除重复行）；EXCEPT(差集，通过包含所有的表1中但不在表2中的行并消除重复行而派生出一个结果表)，EXCEPT ALL，当ALL随EXCEPT一起使用时，不消除重复行。例子：SELECT sid FROM student WHERE department =’计算机系’ UNION(SELECT sid FROM learning WHERE cid=’c03’ AND score>80)

3.数据类型类型转换函数CAST() AS 类型 和CONVERT()
  例：CAST（year(‘1966-5-5’)） AS char(4) 将时间类型的1966转换成字符类型的‘1966’，在数据库中的时间格式一般为：YYYY-MM-DD-HH-MM-SS

CONVERT(char,GETDATE())->’1966-5-5’

4.DISTINCT/ALL关键字-》重值筛选(DISTINCTROW)

SELECT COUNT(DISTINCT sal) FROM tableTeacher  sal记录不重值

SELECT COUNT(ALL sal) FROM tableTeacher sal记录可重值

SELECT DISTINCT sal FROM tableTeacher

5.TOP(n)

限制返回查询表中的前n条记录，TOP可用在SUID查询中

例：SELECT TOP 2 * FROM teacher ORDER BY pid

6.SELECT从返回的结果集合中查询数据

SELECT a.fname,a.lname FROM a,b,(SELECT title_id FROM titles WHERE ytd>1000) AS t WHERE a.au_id=b.au_id AND b.title_id=t.title_id

SELECT 从a,b,t三张表中查询结果，表t是SELECT返回的新表

7.LIKE与通配符指定查询条件

LIKE可用于char,varchar,text,ntext,smalldatatime等类型的查询，LIKE中可以使用的匹配符：

？或_：任何一个单一的字符

*或%：任意长度的字符

#：0-9之间的单一数字

[字符列表]：在字符列表中的任一值

[!字符列表]：不在字符列表中的任一值

-：指定字符范围

例子：

SELECT customers FROM t1 WHERE Phone LIKE’418-###’

8.NULL查询

SELECT sno,cno FROM T1 WHERE Score IS NULL

9.使用包含判断词EXISTS,ALL,ANY,SOME

例子：

SELECT * FROM T1 WHERE EXISTS(SELECT* FROM T2 WHERE T1.id=T2.id)

SELECT * FROM T1 WHERE T1.id=ANY(SELECT id FROM T2)

SELECT * FROM T1 WHERE T1.id IN(SELECT id FROM T2)

以上三种方法都是在T1表中查询与T2表中id字段数据相同的记录内容

SELECT field1 FROM T1 WHERE price>=ALL(SELECT price FROM T1)

SELECT title,COUNT(title) as T1 FROM Tm WHERE region=’WA’ GROUP BY title HAVING COUNT(title)>5;HAVING COUNT(title)>5中的条件只针对GROUP BY分组中的记录进行记录进行操作

SELECT dname,sex,COUNT(*) FROM T1 GROUP BY dname,sex 查询各个系中男老师和女老师的人数。

10.使用正则表达式查询
SELECT * FROM tablename WHERE fieldname REGEXP ‘regstr’
只要‘regstr’ 部分匹配 fieldname 则WHERE为TRUE
11.连接（自连接，内连接，外连接，交叉连接）：求两表集合的一种方式

自连接：例子 SELECT t1.tname,t1.sal FROM teacher t1,teacher t2 WHERE t1.sal=t2.sal AND t2.name=’张军’ ：查询与张军工资相同的老师，其中t1和t2是teacher表的别名

内连，接使用比较运算符进行连接,内连接与外连接不同，内连接只显示完全满足条件的记录

等值连接：连接条件中使用=连接列的列值

SELECT emp.*,dept.* FROM emp INTER JOIN dept ON emp.deptno=dept.deptno

不等值连接：使用>,<,!>,!<,<>进行的连接

SELECT a.no,a.name,b.loc FROM a INTER JOIN b ON a.no<>b.no 使用不等建立关系

自然连接：按照两个表中的相同属性进行等值连接后去掉了重复的属性列，保留了所有不重复的属性列

SELECT a.no,sex,con FROM a,b WHERE a.no=b.no

外连接：与内连接不同，将返回匹配时，某表字段出现失配的记录。外连接的查询结果是内连接结果的扩展，包括了内连接的全部结果。作用是在连接操作时避免丢失信息

左外连接：LEFT [OUTER] JOIN,其结果表中保留左表的所有记录

右外连接：RIGHT [OUTER] JOIN,其结果表中保留右关系的所有元组

全外连接：FULL[OUTER] JOIN,其结果表中保留左右关系的所有元组

例子：SELECT stu.sno ,sname,cno FROM stu RIGHT OUT JOIN sc ON stu.sno=sc.sno

以stu.sno=sc.sno为条件连接两表形成新表，并且返回sc表中没有连接上的记录

交叉连接：交叉连接就是笛卡尔积，将左表的每一条记录连接右表的每一条记录形成一个具有m(左表记录数)×n(右表记录数)条记录数的新表，交叉连接不带WHERE子句

SELECT dept.* FROM dept CROSS JOIN room 将返回dept*room条记录

12.集合查询 UNION INTERSECT EXCEPT [ALL] 对查询结果表操作

SELECT * FROM dept UNION SELECT * FROM department  去冗余

SELECT * FROM dept UNION ALL SELECT * FROM department  不去冗余

SELECT cno FROM school WHERE tno=199001 UNION SELECT cno FROM course WHERE credits>5  UNION SELECT cno FROM sc  WHERE grade>80 多表查询

SELECT empno,ename FROM emp WHERE sal>8000 INTERSECT SELECT empno,ename FROM emp WHERE dep=’CLERK’ 交集

SELECT tno，tname FROM teach WHERE sal>8000 EXCEPT [ALL] SELECT  tno，tname FROM teach WHERE SEX=’男’ 排除：表1排除表2中的结果，并去除重复记录

13.子查询（必须放在（）中）

子查询是一个SELECT 语句，可以理解为嵌套查询，它返回单个（字段）值且嵌套在SUID语句中，任何使用表达式的地方都可以使用子查询

子查询的语法格式如下：

SELECT select_list FROM table WHERE expr operator (SELECT select_list FROM table)

子查询有两种基本形式：简单子查询和关联子查询。简单子查询有内层到外层进行，外查询的进行取决于内查询的结果；关联子查询，则与简单子查询相反，外层SQL语句提供内层子查询在计算中要用的值，然后再将子查询的结果返回外查询

简单子查询：

SELECT ename FROM emp WHERE deptno IN (SELECT dept.deptno FROM dept WHERE loc=’DALLAS’) 查询位于‘’DALLAS’’的所有雇员的姓名

子查询分类：单行子查询（只返回一行单列数据），多行子查询（返回多行单列数据）和多列子查询（返回单行多列和多行多列数据）

字符匹配运算IN,ALL,ANY,LIKE,SOME,EXISTS常用在子查询的结果中

单行子查询：可以使用>,<,=等比较

SELECT ename,deptno FROM emp WHERE depno=(SELECT deptno FROM emp WHERE empno=7369)

SELECT ename,deptno,sal FROM emp WHERE sal=(SELECT MIN(sal) FROM emp) 查询工资最少的雇员姓名、部门和工资

多行子查询：可以使用IN，ALL,ANY,SOME,LIKE,EXISTS进行判断

SELECT * FROM emp WHERE deptno IN(SELECT deptno FROM dept WHERE dname LIKE ‘A%’)查询姓名以’A’开头的员工信息

SELECT tname,sal FROM teacher WHERE sal<ANY(SELECT sal FROM teacher WHERE rno=’0101’ ) AND rno<> ‘0101’ 查询教研室中比0101教研室任意老师工资少的老师名单

多列子查询：在使用子查询比较多个列时，可以使用成对比较和非成对比较，而多列子查询适合于成对和非成对比较。成对比较要求多个列的数据必须同时匹配，非成对比较只要求多列数据中的一列数据匹配即可。

成对多列子查询：SELECT tname,titleno,sal,rno FROM teacher WHERE rno=’0201’ AND (titleno,sal) IN (SELECT titleno,sal FROM teacher WHERE rno=’0101’) 查找0201教研室与0101教研室职称和工资完全相同的教师

非成对多列子查询：完全可以用单列子查询代替

EXISTS用法：
SELECT sname FROM student WHERE EXISTS (SELECT *FROM SC WHERE sno=student.sno AND schoolno=’19980201’)查询所有选修了‘’19980201’’号课程的学生姓名。
从这条查询语句可以看出SQL(或数据库)的工作机制：类似于把每一条记录依次送入查询语句中比对判断。

14.将结果表作为子查询的表

SELECT a.ename,a.sal,a.deptno,b.salavg FROM emp a,(SELECT deptno, AVG(sal) salavg FROM emp GROUP BY deptno ) [AS]b WHERE a.sal>b.salavg AND a.deptno=b.deptno 使用了表的别名和字段的别名