MySQL_基础_DQL数据查询语言
查询语法
语法： SELECT * | { [DISTINCT] column | expression [alias], ... } FROM table [[connect_type] JION table2 ON connect_condition] [WHERE condition] [GROUP BY columns] [HAVING condition] [ORDER BY columns (ASC | DESC)] [LIMIT { [offset,] row_count | row_count OFFSET offset }]; 语句顺序： 查询语句书写顺序：SELECT –> FROM -> JION -> ON -> WHERE -> GROUP BY -> HAVING -> ORDER BY -> LIMIT 查询语句执行顺序：FROM -> JION -> ON -> WHERE -> GROUP BY -> HAVING -> SELECT –> ORDER BY -> LIMIT
基本查询
语法： SELECT * | { [DISTINCT] column | expression [alias], ... } FROM table; 特点： 1、查询列表可以是：表中的字段、常量值、表达式、函数 2、查询的结果是一个虚拟的表格 案例： 1、查询表中的单个字段 SELECT last_name FROM employees; 2、查询表中的多个字段 SELECT last_name,salary,email FROM employees; 3、查询表中的所有字段 SELECT * FROM employees; 4、查询常量值 SELECT 100; SELECT 'john'; 5、查询表达式 SELECT 100%98; 6、查询函数 SELECT VERSION(); 7、起别名，AS SELECT last_name AS 姓,first_name AS 名 FROM employees; SELECT last_name 姓,first_name 名 FROM employees; 8、消除重复数据，DISTINCT SELECT DISTINCT department_id FROM employees; 9、+号的作用 SELECT 100+90; # 两个操作数都为数值型，则做加法运算 SELECT 'john'+90; # 一方为字符型，试图转换成数值型，转换成功则做加法运算，转换失败则转换成0再运算 SELECT null+10; # 一方为null，结果为null 10、字符串连接 SELECT CONCAT('a','b','c') AS 结果;
条件查询
语法： SELECT * | { [DISTINCT] column | expression [alias], ... } FROM table [WHERE condition]; 分类： 1、条件运算符 > < = != <> >= <= # 判断普通值 <=> # 安全等于，即可以判断null值，又可以判断普通值，与is null比较可读较低 2、逻辑运算符 &&或AND # 逻辑并，两个条件都为true，结果为true，反之为false ||或OR # 逻辑或，只要有一个条件为true，结果为true，反之为false !或NOT # 逻辑否，如果连接的条件本身为false，结果为true，反之为false 3、模糊查询 LIKE # 模糊查询，% 任意多个字符，_ 任意单个字符 BETWEEN AND # 在两个值之间(包含边界) IN # 等于值列表中的一个 IS (NOT) NULL # 判断null值 案例： 1、按条件表达式筛选 案例1：查询工资>12000的员工信息 SELECT * FROM employees WHERE salary>12000; 案例2：查询部门编号不等于90号的员工名和部门编号 SELECT last_name,department_id FROM employees WHERE department_id<>90; 案例3：查询没有奖金的员工名和奖金率 SELECT last_name,commission_pct FROM employees WHERE commission_pct <=> NULL; 案例4：查询工资为12000的员工信息 SELECT last_name,salary FROM employees WHERE salary <=> 12000; 2、按逻辑表达式筛选 案例1：查询工资z在10000到20000之间的员工名、工资以及奖金 SELECT last_name,salary,commission_pct FROM employees WHERE salary>=10000 AND salary<=20000; 案例2：查询部门编号不是在90到110之间，或者工资高于15000的员工信息 SELECT * FROM employees WHERE NOT(department_id>=90 AND department_id<=110) OR salary>15000; 3、模糊查询 LIKE： 案例1：查询员工名中包含字符a的员工信息 SELECT * FROM employees WHERE last_name LIKE '%a%'; 案例2：查询员工名中第三个字符为e，第五个字符为a的员工名和工资 SELECT last_name,salary FROM employees WHERE last_name LIKE '__e_a%'; 案例3：查询员工名中第二个字符为_的员工名 SELECT last_name FROM employees WHERE last_name LIKE '_\_%'; SELECT last_name FROM employees WHERE last_name LIKE '_$_%' ESCAPE '$'; BETWEEN AND： 案例1：查询员工编号在100到120之间的员工信息 SELECT * FROM employees WHERE employee_id >= 100 AND employee_id<=120; SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 120; IN： 案例1：查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号 SELECT last_name,job_id FROM employees WHERE job_id = 'IT_PROT' OR job_id = 'AD_VP' OR JOB_ID ='AD_PRES'; SELECT last_name,job_id FROM employees WHERE job_id IN( 'IT_PROT' ,'AD_VP','AD_PRES'); IS (NOT) NULL： 案例1：查询没有奖金的员工名和奖金率 SELECT last_name,commission_pct FROM employees WHERE commission_pct IS NULL; 案例2：查询有奖金的员工名和奖金率 SELECT last_name,commission_pct FROM employees WHERE commission_pct IS NOT NULL;
排序查询
语法： SELECT * | { [DISTINCT] column | expression [alias], ... } FROM table [WHERE condition] [ORDER BY columns (ASC | DESC)]; 特点： 1、ASC升序（默认，可省略），DESC降序 2、ORDER BYy子句可以支持 单个字段、别名、表达式、函数、多个字段 3、ORDER BY子句在查询语句的最后面，除了LIMIT子句 案例： 1、按单个字段排序 案例：查询员工信息，要求先按工资降序 SELECT * FROM employees ORDER BY salary DESC; 2、按多个字段排序 案例：查询员工信息，要求先按工资降序，再按employee_id升序 SELECT * FROM employees ORDER BY salary DESC,employee_id ASC; 3、添加筛选条件再排序 案例：查询部门编号>=90的员工信息，并按员工编号降序 SELECT * FROM employees WHERE department_id>=90 ORDER BY employee_id DESC; 4、按表达式排序 案例：查询员工信息 按年薪降序 SELECT *,salary*12*(1+IFNULL(commission_pct,0)) FROM employees ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC; 5、按别名排序 案例：查询员工信息 按年薪升序 SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪 FROM employees ORDER BY 年薪 ASC; 6、按函数排序 案例：查询员工名，并且按名字的长度降序 SELECT LENGTH(last_name),last_name FROM employees ORDER BY LENGTH(last_name) DESC;
函数查询
常用函数： 字符函数：LENGTH、CONCAT、SUBSTR、REPLACE、INSTR、TRIM、LPAD、RPAD、UPPER、LOWER... 数字函数：ROUND、CEIL、FLOOR、ABS、POWER、TRUNCATE、MOD.. 日期函数：NOW、CURDATE、CURTIME、YEAR、MONTH、DAY、HOUR、MINUTE、SECOND、STR_TO_DATE、DATE_FORMAT.. 流程控制函数：IFNULL、NULLIF、IF、CASE.. 其他函数：VERSION、DATABASE、USER.. 聚合函数：SUM、AVG、MIN、MAX、COUNT.. 案例： 1、字符函数 SELECT LENGTH('hello'); # 取字符串长度，结果：5 SELECT CONCAT('Hello','world'); # 连接字符串，结果：Helloworld SELECT SUBSTR('abcdefg',3,2); # 截取字符串，结果：cd SELECT REPLACE('jack and jue','j','bl'); # 替换字符串，结果：black and blue SELECT INSTR('worldworld','r'); # 查找子串第一次索引，结果：3 SELECT TRIM('*' FROM '***hello***world***'); # 去除前后字符串，结果：hello***world SELECT LPAD('abc',10,'*'); # 左填充，结果：*******abc SELECT RPAD('abc',10,'*'); # 右填充，结果：abc******* SELECT UPPER('sun'); # 转换为大写，结果：SUN SELECT LOWER('FUN'); # 转换为小写，结果：fun 2、数学函数： SELECT ROUND(100.256,2); # 四舍五入，结果：100.26 SELECT CEIL(44.778); # 向上取整，结果：45 SELECT FLOOR(100.2); # 向下取整，结果：100 SELECT ABS(-15); # 取绝对值，结果：15 SELECT POWER(4,2); # m的n次幂，结果：16 SELECT TRUNCATE(100.256,2); # 截断，结果：100.25 SELECT MOD(10,3); # 取余数，结果：1 SELECT RAND(); # 0-1之间随机数，结果：0.341342691650002 3、日期函数： SELECT NOW(); # 返回当前系统日期时间 SELECT CURDATE(); # 返回当前系统日期 SELECT CURTIME(); # 返回当前系统时间 SELECT YEAR(NOW()); # 返回当前系统的年 SELECT MONTH(NOW()); # 返回当前系统的月 SELECT DAY(NOW()); # 返回当前系统的日 SELECT HOUR(NOW()); # 返回当前系统的时 SELECT MINUTE(NOW()); # 返回当前系统的分 SELECT SECOND(NOW()); # 返回当前系统的秒 SELECT STR_TO_DATE('1980-01-01','%Y-%c-%d'); # 将字符转换成日期 SELECT DATE_FORMAT(NOW(),'%y年%m月%d日'); # 将日期转换成字符 SELECT DATEDIFF('2020-01-01', NOW()); # 返回两日期相差天数 4、流程控制函数 SELECT IFNULL(NULL,123); # 如果expr1不是NULL,返回expr1,否则返回expr2 SELECT NULLIF(1,2); # 如果expr1=expr2，返回NULL，否则返回expr1 SELECT IF(1<2,'yes','no'); # 如果expr1是真, 返回expr2, 否则返回expr3 SELECT CASE 1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END; # 枚举这个字段所有可能的值 SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END; # 判断字段范围 5、其他函数： SELECT VERSION(); # 返回MySql版本 SELECT DATABASE(); # 返回当前数据库 SELECT USER(); # 返回当前用户 SELECT PASSWORD('123456'); # 返回该字符的密码形式 SELECT MD5('123456'); # 返回该字符的MD5加密形式 SELECT UUID(); # 返回UUID 6、聚合函数 SELECT SUM(salary) FROM employees; # 求和 SELECT AVG(salary) FROM employees; # 求平均值 SELECT MIN(salary) FROM employees; # 求最小值 SELECT MAX(salary) FROM employees; # 求最大值 SELECT COUNT(salary) FROM employees; # 计算个数
分组查询
语法： SELECT [column,] group function(column), ... FROM table [WHERE condition] [GROUP BY columns] [HAVING condition] [ORDER BY columns (ASC | DESC)]; 案例： 1、简单分组 案例：查询每个工种的员工平均工资 SELECT AVG(salary),job_id FROM employees GROUP BY job_id; 2、分组前筛选 案例：查询邮箱中包含a字符的 每个部门的最高工资 SELECT MAX(salary),department_id FROM employees WHERE email LIKE '%a%' GROUP BY department_id; 3、分组后筛选 案例：查询哪个部门的员工个数>5 SELECT COUNT(*),department_id FROM employees GROUP BY department_id HAVING COUNT(*)>5; 4、多个字段分组 案例：查询每个工种每个部门的最低工资,并按最低工资降序 SELECT MIN(salary),job_id,department_id FROM employees GROUP BY department_id,job_id ORDER BY MIN(salary) DESC;
分页查询
语法： SELECT * | { [DISTINCT] column | expression [alias], ... } FROM table [WHERE condition] [GROUP BY columns] [HAVING condition] [ORDER BY columns (ASC | DESC)] [LIMIT { [offset,] row_count | row_count OFFSET offset }]; 注意： offset代表起始索引，默认0 案例： 1、查询前五条员工信息 SELECT * FROM employees LIMIT 0,5; SELECT * FROM employees LIMIT 5; 2、查询第11条——第25条 SELECT * FROM employees LIMIT 10,15; SELECT * FROM employees LIMIT 15 OFFSET 10;
连接查询
语法： SELECT * | { [DISTINCT] column | expression [alias], ... } FROM table [[connect_type] JION table2 ON connect_condition] [WHERE condition] [GROUP BY columns] [HAVING condition] [ORDER BY columns (ASC | DESC)] [LIMIT { [offset,] row_count | row_count OFFSET offset }]; 含义： 又称多表查询，当查询的字段来自于多个表时，就会用到连接查询 笛卡尔乘积现象： 表1 有m行，表2 有n行，结果=m*n行 发生原因：没有有效的连接条件 如何避免：添加有效的连接条件 分类： 按年代分类： sql92标准：仅仅支持内连接 sql99标准：支持内连接+外连接（左外和右外）+交叉连接 按功能分类： 内连接：[INNER] JOIN ON 等值连接 非等值连接 自连接 外连接： 左外连接：LEFT [OUTER] JOIN 右外连接：RIGHT [OUTER] JOIN 全外连接：FULL [OUTER] JOIN（MySQL不支持） 交叉连接：CROSS JOIN（笛卡尔集） 说明： 内连接： 特点： 1、内连接结果 = 多表的交集部分 2、n表连接至少需要n-1个连接条件 3、多表的顺序没有要求 4、inner可以省略 5、筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读 6、inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集 外连接： 特点： 1、外连接结果 = 内连接结果+主表中有而从表没有的记录 2、left join左边的是主表，right join右边的是主表，full join两边都是主表 3、左外和右外交换两个表的顺序，可以实现同样的效果 4、一般用于查询除了交集部分的剩余的不匹配的行 5、全外连接结果 = 内连接的结果+表1中有但表2没有的+表2中有但表1没有的 案例： 内连接： 1、等值连接 案例1.查询员工名、部门名 SQL92语法：SELECT last_name,department_name FROM departments d,employees e WHERE e.`department_id`=d.`department_id`; SQL99语法：SELECT last_name,department_name FROM departments d JOIN employees e ON e.`department_id`=d.`department_id`; 案例2：查询员工名、部门名和所在的城市 SQL92语法：SELECT last_name,department_name,city FROM employees e,departments d,locations l WHERE e.`department_id`=d.`department_id` AND d.`location_id`=l.`location_id`; SQL99语法：SELECT last_name,department_name,city FROM employees e INNER JOIN departments d ON e.`department_id`=d.`department_id` INNER JOIN locations l ON d.`location_id`=l.`location_id`; 2、非等值连接 案例：查询员工的工资和工资级别 SQL92语法：SELECT salary,grade_level FROM employees e,job_grades g WHERE e.`salary` BETWEEN g.`lowest_sal`
AND g.`highest_sal`; SQL99语法：SELECT salary,grade_level FROM employees e JOIN job_grades g ON e.`salary` BETWEEN g.`lowest_sal`
AND g.`highest_sal`; 3、自连接 案例：查询员工名和上级的名称 SQL92语法：SELECT e.last_name,m.last_name FROM employees e,employees m WHERE e.`manager_id`=m.`employee_id`; SQL99语法：SELECT e.last_name,m.last_name FROM employees e JOIN employees m ON e.`manager_id`=m.`employee_id`; 外连接： 1、左、右外连接 案例：查询哪个部门没有员工 左外连接：SELECT d.*, e.employee_id FROM departments d LEFT OUTER JOIN employees e ON d.`department_id`=e.`department_id` WHERE e.`employee_id` IS NULL; 右外连接：SELECT d.*, e.employee_id FROM employees e RIGHT OUTER JOIN departments d ON d.`department_id`=e.`department_id` WHERE e.`employee_id` IS NULL; 2、交叉连接（笛卡尔集） 案例：查询两表数据 笛卡尔集：SELECT d.*,e.* FROM departments d,employees e; 交叉连接：SELECT d.*,e.* FROM departments d CROSS JOIN employees e;
子查询
含义： 嵌套在其他语句内部的select语句称为子查询或内查询 外部语句可以是insert、update、dalete、select等，一般select作为外部语句较多 外部如果为select语句称为主查询或外查询 分类： 按子查询出现的位置： SELECT后面：支持标量子查询 FROM后面：支持表子查询 WHERE或HAVING后面：支持标量子查询、列子查询、行子查询 EXISTS后面：支持标量子查询、列子查询、行子查询、表子查询 按结果集的行列数不同： 标量子查询（结果集只有一行一列） 列子查询（结果集只有一列多行） 行子查询（结果集有一行多列） 表子查询（结果集一般为多行多列） 特点： 1、子查询放在小括号内 2、子查询一般放在条件的右侧 3、标量子查询，一般搭配着单行操作符使用> < >= <= = <> 4、列子查询，一般搭配着多行操作符使用 (NOT) IN、ANY|SOME、ALL (NOT) IN：等于列表中的任意一个 ANY|SOME：和子查询返回的某一个值比较 ALL：和子查询返回的所有值比较 5、子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果 案例： 1、标量子查询 案例1：查询公司工资最少的员工的last_name,job_id和salary SELECT last_name,job_id,salary FROM employees WHERE salary=( SELECT MIN(salary) FROM employees ); 案例2：查询job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资 SELECT last_name,job_id,salary FROM employees WHERE job_id = ( SELECT job_id FROM employees WHERE employee_id = 141 ) AND salary>( SELECT salary FROM employees WHERE employee_id = 143 ); 2、列子查询 案例1：查询location_id是1400或1700的部门中的所有员工姓名 SELECT last_name FROM employees WHERE department_id IN( SELECT DISTINCT department_id FROM departments WHERE location_id IN(1400,1700) ); # 或 SELECT last_name FROM employees WHERE department_id =ANY( SELECT DISTINCT department_id FROM departments WHERE location_id IN(1400,1700) ); 案例2：查询location_id不是1400或1700的部门中的所有员工姓名 SELECT last_name FROM employees WHERE department_id NOT IN( SELECT DISTINCT department_id FROM departments WHERE location_id IN(1400,1700) ); # 或 SELECT last_name FROM employees WHERE department_id <>ALL( SELECT DISTINCT department_id FROM departments WHERE location_id IN(1400,1700) ); 案例3：查询其它工种中比job_id为‘IT_PROG’工种任一工资低的员工的员工号、姓名、job_id 以及salary SELECT last_name,employee_id,job_id,salary FROM employees WHERE salary<ANY( SELECT DISTINCT salary FROM employees WHERE job_id = 'IT_PROG' ) AND job_id<>'IT_PROG'; # 或 SELECT last_name,employee_id,job_id,salary FROM employees WHERE salary<( SELECT MAX(salary) FROM employees WHERE job_id = 'IT_PROG' ) AND job_id<>'IT_PROG'; 案例4：查询其它工种中比job_id为‘IT_PROG’工种所有工资低的员工的员工号、姓名、job_id 以及salary SELECT last_name,employee_id,job_id,salary FROM employees WHERE salary<ALL( SELECT DISTINCT salary FROM employees WHERE job_id = 'IT_PROG' ) AND job_id<>'IT_PROG'; # 或 SELECT last_name,employee_id,job_id,salary FROM employees WHERE salary<( SELECT MIN(salary) FROM employees WHERE job_id = 'IT_PROG' ) AND job_id<>'IT_PROG'; 3、行子查询 案例：查询员工编号最小并且工资最高的员工信息 SELECT * FROM employees WHERE employee_id=( SELECT MIN(employee_id) FROM employees )AND salary=( SELECT MAX(salary) FROM employees); # 或 SELECT * FROM employees WHERE (employee_id,salary)=( SELECT MIN(employee_id),MAX(salary) FROM employees ); 4、表子查询 案例：查询有员工的部门名 SELECT department_name FROM departments d WHERE EXISTS( SELECT * FROM employees e WHERE d.`department_id`=e.`department_id` );
联合查询
语法： SELECT1 ... UNION [ALL | DISTINCT] SELECT2 ... ... 应用场景： 要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时 特点： 1、要求多条查询语句的查询列数是一致的！ 2、要求多条查询语句的查询的每一列的类型和顺序最好一致 3、union关键字默认去重，如果使用union all 可以包含重复项 4、多个select语句联合查询后的结果字段，都是以第一个select语句的字段为准 案例： 1、查询部门编号>90或邮箱包含a的员工信息 SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90; # 或 SELECT * FROM employees WHERE email LIKE '%a%' UNION SELECT * FROM employees WHERE department_id>90;
