必须以管理员身份打开cmd DQL（Data Query Language）：数据查询语言 用作统计使用，又称为聚合函数或统计函数或组函数。传入一组值，经过统计处理得到一个值。 可以使用 连接查询又称为多表查询，当查询的字段来自于多个表时，就会用到连接查询。 笛卡尔乘积现象：表1 有m行，表2 有n行，结果为 m*n 行 发生原因：没有有效的连接条件 如何避免：添加有效的连接条件 仅仅支持标量子查询（一行一列） 注意：将子查询结果充当一张表，要求必须起别名，因为这个表本来不存在，如果不起别名的话，找不到。 DML（Data Manipulate Language）：数据操作语言
一、数据库相关概念
1、DB:数据库(database)，保存了一系列有组织的数据 2、DBMS:数据库管理系统(database management system)，又称为数据库软件（产品），用于管理DB中的数据 3、SQL:结构化查询语言(structure query language)，用于和DBMS通信的语言 二、数据库存储数据的特点
1、将数据放到表中，表再放到库中 2、一个数据库中可以有多个表，每个表都有一个的名字，用来标识自己。表名具有唯一性 3、表具有一些特性，这些特性定义了数据在表中如何存储，类似python中类的设计 4、表由列组成，我们也称为字段。所有表都是由一个或多个列组成的，每一列类似python中的属性 5、表中的数据是按行存储的，每一行类似于python中的对象 三、初识MySQL
1 MySQL的安装
1. 下载压缩包mysql-5.7.26-winx64.zip 2. 解压并进入C:MySQLmysql-5.7.26-winx64 3. 在第2步的目录下，创建data文件夹和my.ini文件 4. 编辑my.ini文件 [mysqld] character-set-server=utf8 bind-address = 0.0.0.0 port = 3306 basedir=C:MySQLmysql-5.7.26-winx64 datadir=C:MySQLmysql-5.7.26-winx64data max_connections=2000 default-storage-engine=INNODB skip-grant-tables [mysql] default-character-set=utf8 [mysql.server] default-character-set=utf8 [client] default-character-set=utf8 5. 在cmd中，进入到bin目录 C:Userslenovo>cd C:MySQLmysql-5.7.26-winx64bin 6. 执行安装命令mysqld --install (卸载命令mysqld --remove) 如果遇到报错Install/Remove of the Service Denied! 解决方案，使用管理员打开cmd，win+X+A就可以以管理员身份打开cmd 执行成功如下 C:MySQLmysql-5.7.26-winx64bin>mysqld --install Service successfully installed. 7. mysql数据库初始化，执行mysqld --initialize --user=root --console 在最后一行可以看到随机密码 8. 启动mysql服务，net start mysql 9. 使用随机密码登录mysql，执行mysql -u -root -p 10. 登录成功后修改密码，执行use mysql; 11. 执行update user set authentication_string=PASSWORD("Mall@960610") where user='root'; 12. 配置mysql的环境变量 在系统变量下，新建变量名MYSQL_HOME，变量值C:MySQLmysql-5.7.26-winx64 在path中编辑新建%MYSQL_HOME%bin 13. 使用自己设置的密码登录mysql，执行mysql -u -root -p 2 服务的启动和停止
# 启动mysql服务 net start 服务名 # 停止mysql服务 net stop 服务名 3 服务的登录和退出
3.1 登录
mysql 【-h主机名 -P端口号】 -u用户名 -p密码 mysql -h localhost -P 3306 -u -root -p mysql -u -root -p 3.2 退出
exit 或 ctrl+C 4 常见命令
1. 查看当前所有的数据库 show databases; 2. 打开指定的库 use 库名; 3. 查看当前库的所有表 show tables; 4. 查看其它库的所有表 show tables from 库名; 5. 查看当前所在的库 select database(); 6. 查看表结构 desc 表名; 7. 查看服务器的版本 方式一：登录到mysql服务端 select version(); 方式二：没有登录到mysql服务端 mysql --version 或 mysql --V 5 语法规范
1. 不区分大小写，但建议关键字大写，表名、列名小写 2. 每条命令最好用分号结尾 3. 每条命令根据需要，可以进行缩进或换行 4. 注释 单行注释：#注释文字 单行注释：-- 注释文字 多行注释：/* 注释文字 */ 四、DQL语言学习
主要涉及：select0 创建库和表
/* Navicat MySQL Data Transfer Source Server : mysql5.7 Source Server Version : 50726 Source Host : localhost:3306 Source Database : myemployees Target Server Type : MYSQL Target Server Version : 50726 File Encoding : 65001 Date: 2020-07-05 18:46:43 */ SET FOREIGN_KEY_CHECKS=0; -- ---------------------------- -- Table structure for departments -- ---------------------------- DROP TABLE IF EXISTS `departments`; CREATE TABLE `departments` ( `department_id` int(4) NOT NULL AUTO_INCREMENT, `department_name` varchar(3) DEFAULT NULL, `manager_id` int(6) DEFAULT NULL, `location_id` int(4) DEFAULT NULL, PRIMARY KEY (`department_id`), KEY `loc_id_fk` (`location_id`), CONSTRAINT `loc_id_fk` FOREIGN KEY (`location_id`) REFERENCES `locations` (`location_id`) ) ENGINE=InnoDB AUTO_INCREMENT=271 DEFAULT CHARSET=gb2312; -- ---------------------------- -- Table structure for employees -- ---------------------------- DROP TABLE IF EXISTS `employees`; CREATE TABLE `employees` ( `employee_id` int(6) NOT NULL AUTO_INCREMENT, `first_name` varchar(20) DEFAULT NULL, `last_name` varchar(25) DEFAULT NULL, `email` varchar(25) DEFAULT NULL, `phone_number` varchar(20) DEFAULT NULL, `job_id` varchar(10) DEFAULT NULL, `salary` double(10,2) DEFAULT NULL, `commission_pct` double(4,2) DEFAULT NULL, `manager_id` int(6) DEFAULT NULL, `department_id` int(4) DEFAULT NULL, `hiredate` datetime DEFAULT NULL, PRIMARY KEY (`employee_id`), KEY `dept_id_fk` (`department_id`), KEY `job_id_fk` (`job_id`), CONSTRAINT `dept_id_fk` FOREIGN KEY (`department_id`) REFERENCES `departments` (`department_id`), CONSTRAINT `job_id_fk` FOREIGN KEY (`job_id`) REFERENCES `jobs` (`job_id`) ) ENGINE=InnoDB AUTO_INCREMENT=207 DEFAULT CHARSET=gb2312; -- ---------------------------- -- Table structure for jobs -- ---------------------------- DROP TABLE IF EXISTS `jobs`; CREATE TABLE `jobs` ( `job_id` varchar(10) NOT NULL, `job_title` varchar(35) DEFAULT NULL, `min_salary` int(6) DEFAULT NULL, `max_salary` int(6) DEFAULT NULL, PRIMARY KEY (`job_id`) ) ENGINE=InnoDB DEFAULT CHARSET=gb2312; -- ---------------------------- -- Table structure for locations -- ---------------------------- DROP TABLE IF EXISTS `locations`; CREATE TABLE `locations` ( `location_id` int(11) NOT NULL AUTO_INCREMENT, `street_address` varchar(40) DEFAULT NULL, `postal_code` varchar(12) DEFAULT NULL, `city` varchar(30) DEFAULT NULL, `state_province` varchar(25) DEFAULT NULL, `country_id` varchar(2) DEFAULT NULL, PRIMARY KEY (`location_id`) ) ENGINE=InnoDB AUTO_INCREMENT=3201 DEFAULT CHARSET=gb2312; CREATE TABLE job_grades ( grade_level VARCHAR (3), lowest_sal INT, highest_sal INT ); INSERT INTO job_grades VALUES ('A', 1000, 2999); INSERT INTO job_grades VALUES ('B', 3000, 5999); INSERT INTO job_grades VALUES ('C', 6000, 9999); INSERT INTO job_grades VALUES ('D', 10000, 14999); INSERT INTO job_grades VALUES ('E', 15000, 24999); INSERT INTO job_grades VALUES ('F', 25000, 40000); /* Navicat MySQL Data Transfer Source Server : mysql5.7 Source Server Version : 50726 Source Host : localhost:3306 Source Database : girls Target Server Type : MYSQL Target Server Version : 50726 File Encoding : 65001 Date: 2020-07-08 20:02:08 */ SET FOREIGN_KEY_CHECKS=0; -- ---------------------------- -- Table structure for admin -- ---------------------------- DROP TABLE IF EXISTS `admin`; CREATE TABLE `admin` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(10) NOT NULL, `password` varchar(10) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8; -- ---------------------------- -- Table structure for beauty -- ---------------------------- DROP TABLE IF EXISTS `beauty`; CREATE TABLE `beauty` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, `sex` char(1) DEFAULT '女', `borndate` datetime DEFAULT '1987-01-01 00:00:00', `phone` varchar(11) NOT NULL, `photo` blob, `boyfriend_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8; -- ---------------------------- -- Table structure for boys -- ---------------------------- DROP TABLE IF EXISTS `boys`; CREATE TABLE `boys` ( `id` int(11) NOT NULL AUTO_INCREMENT, `boyName` varchar(20) DEFAULT NULL, `userCP` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; employees表 员工表 employee_id 员工编号 first_name 名 last_name 姓 email 邮箱 phone_number 电话号码 job_id 工种编号 salary 月薪 commission_pct 奖金率 manager_id 上级领导的员工编号 department_id 部门编号 hiredate 入职日期 departments表 部门表 department_id 部门编号 department_name 部门名称 manager_id 部门领导的员工编号 location_id 位置编号 locations表 位置表 location_id 位置编号 street_address 街道 postal_code 邮编 city 城市 state_province 州/省 country_id 国家编号 jobs表 工种表 job_id 工种编号 job_title 工种名称 min_salary 最低工资 max_salary 最高工资 1 基础查询
select 查询列表 from 表名; 1. 查询列表可以是：表中的字段、常量值、表达式、函数 2. 查询的结果是一个虚拟的表格 1. 查询表中的单个字段 SELECT last_name FROM employees; 2. 查询表中的多个字段 SELECT last_name,email,salary FROM employees; 3. 查询表中的所有字段 SELECT * FROM employees; 4. 查询常量值 SELECT 100; SELECT 'tom'; 5. 查询表达式 SELECT 10*3; 6. 查询函数 SELECT VERSION(); 7. 为字段起别名 便于理解；如果要查询的字段有重名的情况，使用别名可以区分开来 方式一：SELECT last_name AS 姓 FROM employees; 方式二：SELECT last_name 姓 FROM employees; 查询salary，显示结果为 out put SELECT salary AS "out put" FROM employees; 8. 去重 查询员工表中涉及到的所有的部门编号 SELECT DISTINCT department_id FROM employees; 9. +的作用 mysql中的+号：仅仅只有一个功能，运算符 SELECT 100+10; 两个操作数都为数值型，则做加法运算 SELECT "100"+10; 只要其中一方为字符型，视图将字符型转换为数值型，如果转换成功，则继续做加法运算；如果转换失败，则将字符型转换为0 SELECT "tom"+10; 结果为10 SELECT NULL+10; 只要其中一方为null，则结果肯定为null 查询员工名和姓连接成一个字段，并显示为 姓名 SELECT CONCAT(last_name,first_name) AS 姓名 FROM employees; 2 条件查询
select 查询列表 from 表名 where 筛选条件; 执行顺序 from --> where --> select 分类 一、按条件表达式筛选 简单条件运算符：> < = != <>(推荐) >= <= 二、按逻辑表达式筛选 逻辑运算符： && || ! (不推荐) and or not (推荐) 三、模糊查询 like, between and, in, is null, is not null 按条件表达式筛选 1. 查询工资大于12000的员工信息 SELECT * FROM employees WHERE salary > 12000; 2. 查询部门编号不等于90号的员工名和部门编号 SELECT last_name,department_id FROM employees WHERE department_id <> 90; 按逻辑表达式筛选 1. 查询工资在10000到20000之间的员工名、工资、奖金 SELECT last_name, salary, commission_pct FROM employees WHERE salary >= 10000 AND salary <= 20000; 2. 查询部门编号不是在90到110之间，或者工资高于15000的员工信息 SELECT * FROM employees WHERE department_id < 90 OR department_id > 110 OR salary > 15000; 或者 SELECT * FROM employees WHERE NOT(department_id >= 90 AND department_id <= 110) OR salary > 15000; 模糊查询 like, between and, in, is null, is not null 一、like 特点：一般和通配符搭配使用，其中%表示任意多个字符；_表示任意单个字符 1. 查询员工名中包含字符a的员工信息 SELECT * FROM employees WHERE last_name LIKE '%a%'; 2. 查询员工名中第三个字符为n，第五个字符为l的员工名和工资 SELECT last_name,salary FROM employees WHERE last_name LIKE '__n_l%'; 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 BETWEEN 100 AND 120; 2. 查询员工编号在100到120之间的员工信息 SELECT * FROM employees WHERE employee_id NOT BETWEEN 100 AND 120; 三、in 判断某字段的值是否属于in列表中的某一项 特点： 使用in提高语句简洁度 in列表的值类型必须一致或兼容(可以隐式转换) 不能使用通配符 1. 查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个的员工名和工种编号 SELECT last_name,job_id FROM employees WHERE job_id IN('IT_PROT','AD_VP','AD_PRES'); 四、is null = 或 <> 不能用于判断null值 is null 或 is not null 可以判断null值 1. 查询没有奖金的员工名和奖金率 SELECT last_name,commission_pct FROM employees WHERE commission_pct IS NULL; 或 SELECT last_name,commission_pct FROM employees WHERE ISNULL(commission_pct); 五、安全等于 <=> 安全等于可以判断null值，也可以判断普通值 1. 查询没有奖金的员工名和奖金率 SELECT last_name,commission_pct FROM employees WHERE commission_pct <=> NULL; 2. 查询工资为12000的员工信息 SELECT * FROM employees WHERE salary <=> 12000; 2.1 经典面试题
问 SELECT * FROM employees; 和 SELECT * FROM employees WHERE commission_pct LIKE '%%' AND last_name LIKE '%%'; 结果是否一样，说明理由 答：不一样，如果判断的字段有null值，结果不一样；如果判断的字段没有null值，结果一样。 3 排序查询
select 查询列表 from 表 [where 筛选条件] order by 排序列表 [asc|desc] 1. asc表示升序，desc表示降序。默认是升序 2. order by子句中可以支持单个字段、多个字段、表达式、函数、别名 3. order by子句一般是放在查询语句的最后面，limit子句除外 4. 执行顺序from --> where --> select --> order by 1. 查询员工信息，要求工资从高到低排序 SELECT * FROM employees ORDER BY salary DESC; 2. 查询部门编号大于等于90的员工信息，按入职时间的先后进行排序 SELECT * FROM employees WHERE department_id>=90 ORDER BY hiredate; 3. 按年薪的高低显示员工的信息和年薪【按别名排序】 SELECT *, salary * 12 * (1 + IFNULL(commission_pct, 0)) AS 年薪 FROM employees ORDER BY 年薪 DESC; 4. 按姓名的长度显示员工的姓名和工资【按函数排序】 SELECT LENGTH(last_name) AS 字节长度, last_name, salary FROM employees ORDER BY LENGTH(last_name) DESC; 5. 查询员工信息，要求先按工资升序，再按员工编号降序【按多个字段排序】 SELECT * FROM employees ORDER BY salary,employee_id DESC; 4 常见函数
select 函数名(实参列表) [from 表] 分类 单行函数 分组函数(统计函数、聚合函数、组函数) 单行函数 一、字符函数 1. length获取参数值的字节个数 SHOW VARIABLES LIKE '%char%'; -- 用来查看客户端字符集 SELECT LENGTH('lucy'); -- 4 SELECT LENGTH('哈哈'); -- 6 2. concat拼接字符串 SELECT CONCAT(last_name,'_',first_name) AS 姓名 FROM employees; 3. upper、lower SELECT UPPER('lucy'); SELECT LOWER('LuCy'); -- 将姓变大写，名变小写，然后拼接 SELECT CONCAT(UPPER(last_name),LOWER(first_name)) 姓名 FROM employees; 4. substr/substring 注意：索引从 1 开始 # 截取从指定索引处后面所有字符 -- SUBSTR(str,pos) SELECT SUBSTR('李莫愁爱上了陆展元',7) AS out_put; -- 陆展元 # 截取从指定索引处指定字符长度的字符 -- SUBSTR(str,pos,len) SELECT SUBSTR('李莫愁爱上了陆展元',4,3) AS out_put; -- 爱上了 -- SUBSTR(str FROM pos FOR len) -- SUBSTR(str FROM pos) # 案例：姓名中首字符大写，其他字符小写，用_拼接并显示出来 SELECT CONCAT( UPPER(SUBSTR(last_name, 1, 1)), '_', LOWER(SUBSTR(last_name, 2)) ) FROM employees; 5. instr # 返回子串第一次出现的索引，如果找不到返回0 -- INSTR(str,substr) SELECT INSTR('杨不悔爱上了殷六侠','爱上了'); -- 4 6. trim # 去除两端空格或指定字符 -- TRIM([remstr FROM] str) -- TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) SELECT LENGTH(TRIM(' 大陕北 ')); -- 9 SELECT TRIM('a' FROM 'aa大a陕北aaaa'); -- 大a陕北 7. lpad # 用指定的字符实现左填充指定长度，若指定长度小于str长度，则截断 -- LPAD(str,len,padstr) SELECT LPAD('哈哈哈',5,'*'); -- **哈哈哈 SELECT LPAD('哈哈哈',2,'*'); -- 哈哈 8. rpad 同上 9. replace # 替换 -- REPLACE(str,old,new) SELECT REPLACE('周芷若张无忌爱上了周芷若', '周芷若','赵敏'); -- 赵敏张无忌爱上了赵敏 二、数学函数 1. round # 四舍五入 # 如果是负数，先把负号去掉，四舍五入后在添加负号 SELECT ROUND(1.65); -- 2 SELECT ROUND(-1.65); -- -2 SELECT ROUND(1.567,2); -- 1.57 SELECT ROUND(-1.567,2); -- -1.57 2. ceil # 向上取整 # 返回大于等于该参数的最小整数 SELECT CEIL(1.00); -- 1 SELECT CEIL(-1.02); -- -1 3. floor # 向下取整 # 返回小于等于该参数的最小整数 SELECT FLOOR(1.02); -- 1 SELECT FLOOR(-1.02); -- -2 4. truncate # 截断 -- TRUNCATE(X,D) SELECT TRUNCATE(1.699,2); -- 1.69 SELECT TRUNCATE(-1.699,2); -- -1.69 5. mod # 取余 # mod(a,b) 等价于 a - a//b*b # 被除数的正负决定结果的正负 SELECT MOD(-10,3); -- -1 SELECT MOD(-10,-3); -- -1 SELECT MOD(10,-3); -- 1 SELECT MOD(10,3); -- 1 三、日期函数 1. now # 返回当前系统日期+时间 SELECT NOW(); -- 2020-07-06 23:35:50 2. curdate # 返回当前系统日期，不包括时间 SELECT CURDATE(); -- 2020-07-06 3. curtime # 返回当前时间，不包括日期 SELECT CURTIME(); -- 23:38:57 4. 可以获取指定的部分，年、月、日、时、分、秒 SELECT YEAR(NOW()); -- 2020 SELECT MONTH(NOW()) 月; -- 7 SELECT MONTHNAME(NOW()); -- July 5. str_to_date # 将日期格式的字符转换成指定格式的日期 -- STR_TO_DATE(str,format) SELECT STR_TO_DATE('1996-6-10','%Y-%c-%d'); -- 1996-06-10 查询入职日期为1992-4-3的员工信息 SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3 1992','%c-%d %Y'); 6. date_format # 将日期转换成字符 -- DATE_FORMAT(date,format) SELECT DATE_FORMAT(NOW(),'%y年%m月%d日'); -- 20年07月07日 查询有奖金的员工名和入职日期(xx月/xx日 xx年) SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %y年') FROM employees WHERE commission_pct IS NOT NULL; 7. datediff # 计算相差天数，expr1 - expr2 -- DATEDIFF(expr1,expr2) 查询员工表中的最大入职时间和最小入职时间的相差天数 SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) FROM employees; 四、其他函数 SELECT VERSION(); -- 5.7.26 SELECT DATABASE(); -- myemployees SELECT USER(); -- root@ 五、流程控制函数 1. if函数 -- IF(expr1,expr2,expr3) SELECT IF(10>3,'正确','错误'); -- 正确 2. case函数 使用一：等值判断 case 要判断的字段或表达式 when 常量1 then 要显示的值1或语句1 when 常量2 then 要显示的值2或语句2 ...... else 要显示的值n或语句n; end 查询员工的工资，要求：部门号=30，显示的工资为1.1倍；部门号=40，显示的工资为1.2倍；部门号=50，显示的工资为1.3倍；其他部门，显示的工资为原工资。 SELECT salary 原工资, department_id, CASE department_id WHEN 30 THEN salary * 1.1 WHEN 40 THEN salary * 1.2 WHEN 50 THEN salary * 1.3 ELSE salary END AS 新工资 FROM employees; 使用二：区间判断 case when 条件1 then 要显示的值1或语句1 when 条件2 then 要显示的值2或语句2 ...... else 要显示的值n或语句n end 查询员工的工资的情况，如果工资大于20000，显示A级别；如果工资大于15000，显示B级别；如果工资大于10000，显示C级别；否则显示D级别 SELECT salary, CASE WHEN salary > 20000 THEN 'A' WHEN salary > 15000 THEN 'B' WHEN salary > 10000 THEN 'C' ELSE 'D' END AS 工资级别 FROM employees; 5 分组函数
分类 sum求和、avg平均值、max最大值、min最小值、count计算个数 特点 1. sum和avg一般用于处理数值型，max、min、count可以处理任何类型 2. 这五个分组函数都忽略null值 3. 可以和distinct搭配使用，实现去重的运算 4. 和分组函数一同查询的字段要求是group by后的字段 1. 简单的使用 SELECT SUM(salary),AVG(salary),MIN(salary),MAX(salary),COUNT(salary) FROM employees; 2. 参数支持哪些类型 SELECT SUM(last_name),AVG(last_name) FROM employees; SELECT SUM(hiredate),AVG(hiredate) FROM employees; SELECT MAX(last_name),MIN(last_name) FROM employees; SELECT MAX(hiredate),MIN(hiredate) FROM employees; SELECT COUNT(commission_pct) FROM employees; SELECT COUNT(last_name) FROM employees; 3. 是否忽略null SELECT SUM(commission_pct),AVG(commission_pct),SUM(commission_pct)/35,SUM(commission_pct)/107 FROM employees; SELECT MAX(commission_pct),MIN(commission_pct) FROM employees; SELECT COUNT(commission_pct) FROM employees; SELECT commission_pct FROM employees; 4. 和distinct搭配 SELECT SUM(DISTINCT salary),SUM(salary) FROM employees; SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees; count函数的详细介绍 SELECT COUNT(salary) FROM employees; -- 统计行数，只要这一行有一个不为null，结果就加1 SELECT COUNT(*) FROM employees; -- count(常量值)，相当于在表中新加了一列这样的常量值，然后统计常量值的个数 SELECT COUNT(1) FROM employees; 效率 MYISAM存储引擎下，count(*)的效率高 INNODB存储引擎下，count(*)和count(1)的效率差不多，比count(字段)要高 6 分组查询
group by子句将表中的数据分为若干组select 分组函数,列(要求出现在group by的后面) from 表 [where 筛选条件] group by 分组的列表 [order by子句] 注意：查询列表要求是分组函数和group by后出现的字段 特点： 1. 分组查询中的筛选条件分为两类 数据源 位置 关键字 分组前筛选 原始表 group by子句的前面 where 分组后筛选 分组后的结果 group by子句的后面 having Ⅰ 分组函数做条件肯定是放在having子句中 Ⅱ 能用分组前筛选的，就优先考虑使用分组前筛选 2. group by子句支持单个字段分组、多个字段分组(多个字段之间用逗号隔开，没有顺序要求)、表达式或函数(用的较少) 3. 也可以添加排序(排序放在整个分组查询的最后) 简单使用 1. 查询每个工种的最高工资 SELECT MAX(salary),job_id FROM employees GROUP BY job_id; 2. 查询每个位置上的部门个数 SELECT COUNT(*),location_id FROM departments GROUP BY location_id; 添加筛选条件 1. 查询邮箱中包含a字符，每个部门的平均工资 SELECT AVG(salary),department_id FROM employees WHERE email LIKE '%a%' GROUP BY department_id; 2. 查询有奖金的每个领导手下员工的最高工资 SELECT MAX(salary),manager_id FROM employees WHERE commission_pct IS NOT NULL GROUP BY manager_id; 添加分组后的筛选条件 1. 查询哪个部门的员工个数大于2 Ⅰ查询每个部门的员工个数 SELECT COUNT(*) AS 员工个数,department_id FROM employees GROUP BY department_id; Ⅱ 根据Ⅰ的结果进行筛选，查询哪个部门的员工个数大于2 SELECT COUNT(*) AS 员工个数,department_id FROM employees GROUP BY department_id HAVING COUNT(*)>2; 2. 查询每个工种有奖金的员工的最高工资大于12000的工种编号和最高工资 Ⅰ 查询每个工种有奖金的员工的最高工资 SELECT MAX(salary),job_id FROM employees WHERE commission_pct IS NOT NULL GROUP BY job_id; Ⅱ 根据Ⅰ的结果进行筛选，筛选出最高工资大于12000 SELECT MAX(salary),job_id FROM employees WHERE commission_pct IS NOT NULL GROUP BY job_id HAVING MAX(salary)>12000; 3. 查询领导编号大于102的每个领导手下的最低工资大于5000的领导编号是哪个，以及其最低工资 Ⅰ 查询每个领导手下的员工最低工资 SELECT MIN(salary),manager_id FROM employees GROUP BY manager_id; Ⅱ 添加筛选条件：领导编号大于102 SELECT MIN(salary),manager_id FROM employees WHERE manager_id>102 GROUP BY manager_id; Ⅲ 添加筛选条件：最低工资大于5000 SELECT MIN(salary),manager_id FROM employees WHERE manager_id>102 GROUP BY manager_id HAVING MIN(salary)>5000; 按表达式或函数分组 1. 按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数大于5的有哪些 SELECT COUNT(*),LENGTH(last_name) FROM employees GROUP BY LENGTH(last_name) HAVING COUNT(*)>5; 按多个字段分组 1. 查询每个部门每个工种的员工的平均工资 SELECT AVG(salary),department_id,job_id FROM employees GROUP BY department_id,job_id; 添加排序 1. 查询每个部门每个工种的员工的平均工资，并按平均工资的高低显示 SELECT AVG(salary),department_id,job_id FROM employees GROUP BY department_id,job_id ORDER BY AVG(salary) DESC; 7 连接查询
分类 1. 按年代分类 Ⅰsql92标准: 仅仅支持内连接 Ⅱsql99标准【推荐】: 支持内连接+外连接(左外和右外)+交叉连接 2. 按功能分类 Ⅰ内连接 等值连接 非等值连接 自连接 Ⅱ外连接 左外连接 右外连接 全外连接 Ⅲ交叉连接 7.1 sql92标准
7.1.1 等值连接
1. 多表等值连接的结果为多表的交集部分 2. n表连接，至少需要n-1个连接条件 3. 多表的顺序没有要求 4. 一般需要为表起别名 5. 可以搭配前面介绍的所有子句使用，比如排序、分组、筛选 1. 查询女神名和对应的男神名 SELECT name,boyName FROM boys,beauty WHERE boys.id = beauty.boyfriend_id; 2. 查询员工名和对应的部门名 SELECT last_name,department_name FROM employees,departments WHERE employees.department_id=departments.department_id; 3. 查询员工名、工种号、工种名 注意:如果为表起了别名，则查询的字段就不能使用原来的表名去限定 SELECT last_name,e.job_id,job_title FROM employees e,jobs j WHERE e.job_id=j.job_id; 【可以加筛选条件】 4. 查询有奖金的员工名、部门名 SELECT e.last_name,d.department_name FROM employees e,departments d WHERE e.department_id=d.department_id AND e.commission_pct IS NOT NULL; 5. 查询城市名中第二个字符为o的部门名和城市名 SELECT d.department_name, l.city FROM departments d, locations l WHERE d.location_id = l.location_id AND l.city LIKE '_o%'; 【可以加分组】 6. 查询每个城市的部门个数 SELECT city,COUNT(*) 个数 FROM departments d,locations l WHERE d.location_id=l.location_id GROUP BY l.city; 7. 查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资 SELECT d.department_name, d.manager_id, MIN(salary) FROM employees e, departments d WHERE e.department_id = d.department_id AND commission_pct IS NOT NULL GROUP BY e.department_id; 【可以加排序】 8. 查询每个工种的工种名和员工的个数，并且按员工个数降序 SELECT j.job_title, COUNT(*) 员工个数 FROM employees e, jobs j WHERE e.job_id = j.job_id GROUP BY e.job_id ORDER BY COUNT(*) DESC; 【可以实现三表连接】 9. 查询员工名、部门名和所在的城市 SELECT e.last_name, d.department_name, l.city FROM employees e, departments d, locations l WHERE e.department_id = d.department_id AND d.location_id = l.location_id; 7.1.2 非等值连接
1. 查询员工的工资和工资级别 SELECT e.salary, jg.grade_level FROM employees e, job_grades jg WHERE salary BETWEEN jg.lowest_sal AND jg.highest_sal; 7.1.3 自连接
1. 查询员工名和上级的名称 SELECT e.last_name 员工, m.last_name 领导 FROM employees e, employees m WHERE e.manager_id = m.employee_id; 7.2 sql99标准
select 查询列表 from 表1 别名 [连接类型] join 表2 别名 on 连接条件 [where 筛选条件] [group by 分组] [order by 排序列表] 连接类型 内连接:inner (inner可以省略) 用来求多表的交集 外连接 左外:left[outer] 右外:right[outer] 全外:full[outer] mysql不支持 交叉连接:cross 7.2.1 内连接
两表连接，没有顺序要求 多表连接，只要后面的表和前面的表能够连接就行 等值连接 1. 查询员工名、部门名 SELECT last_name, department_name FROM employees e INNER JOIN departments d ON e.department_id = d.department_id; 2. 查询名字中包含e的员工名和工种名【筛选】 SELECT last_name, job_title FROM employees e INNER JOIN jobs j ON e.job_id = j.job_id WHERE last_name LIKE '%e%'; 3. 查询部门个数大于3的城市名和部门个数【添加分组+筛选】 注意:这个题是按城市名分组 SELECT city, COUNT(*) 部门个数 FROM locations l INNER JOIN departments d ON l.location_id = d.location_id GROUP BY city HAVING COUNT(*) > 3; 4. 查询哪个部门的部门员工个数大于3的部门名和员工个数，并按个数降序【排序】 SELECT department_name, COUNT(*) 员工个数 FROM employees e INNER JOIN departments d ON e.department_id = d.department_id GROUP BY department_name HAVING COUNT(*) > 3 ORDER BY COUNT(*) DESC; 5. 查询员工名、部门名、工种名，并按部门名降序【多表查询+排序】 SELECT last_name, department_name, job_title FROM employees e INNER JOIN departments d ON e.department_id = d.department_id INNER JOIN jobs j ON e.job_id = j.job_id ORDER BY department_name DESC; 非等值连接 1. 查询员工的工资级别 SELECT e.employee_id, j.grade_level FROM employees e JOIN job_grades j ON salary BETWEEN j.lowest_sal AND j.highest_sal; 自连接 1. 查询员工的名字以及上级的名字 SELECT e1.last_name 员工名, e2.last_name 领导名 FROM employees e1 JOIN employees e2 ON e1.manager_id = e2.employee_id; 7.2.2 左(右)外连接
应用场景 用于查询一个表中有，另一个表没有的记录 特点 1. 外连接的查询结果为主表中的所有记录 如果从表中有和它匹配的，则显示匹配的值 如果从表中没有和它匹配的，则显示null 所以，外连接查询结果 = 内连接结果 + 主表中有而从表中没有的记录 2. 左外连接，left join左边的是主表 右外连接，right join右边的是主表 3. 左外和右外交换两个表的顺序，可以实现同样的效果 4. 全外连接 = 内连接结果 + 表1中有但表2没有的 + 表2中有但表1没有的 注意:mysql不支持全外连接 1. 查询男朋友 不在男神表的女神名 SELECT b.`name` FROM beauty b LEFT JOIN boys bo ON b.boyfriend_id = bo.id WHERE bo.id IS NULL; 2. 查询哪个部门没有员工 SELECT department_name FROM departments d LEFT JOIN employees e ON d.department_id = e.department_id WHERE e.employee_id IS NULL; 7.2.3 交叉连接
两表进行笛卡尔乘积的结果 SELECT b.*,bo.* FROM beauty b CROSS JOIN boys bo; 8 子查询【相对有难度】
8.1 含义
出现在其他语句中的select语句，称为子查询或内查询 外部的查询语句，称为主查询或外查询 8.2 分类
8.2.1 按子查询出现的位置
select后面: 仅仅支持标量子查询 from后面: 支持表子查询 where或having后面:【重要，用得较多】 标量子查询(单行子查询) 列子查询(多行子查询) 行子查询(用的很少) exists后面(相关子查询): 表子查询 8.2.2 按结果集的行列数不同
标量子查询(结果集只有一行一列) 列子查询(结果集一列，多行) 行子查询(结果集多行，多列，但一般情况下指一行多列) 表子查询(结果集一般为多行多列) 8.2.3 where或having后面
特点 1. 子查询放在小括号内 2. 子查询一般放在条件的右侧 3. 标量子查询，一般搭配单行操作符(> < >= <= = <>)使用 4. 列子查询，一般搭配多行操作符(in any/some all)使用 5. 子查询的执行优先于主查询执行，因为主查询的条件用到了子查询的结果 多行比较操作符 操作符 含义 in/not in 等于列表中的任意一个 any|some 和子查询返回的某一个值比较 all 和子查询返回的所有值比较 IN 等价于 = ANY NOT IN 等价于 <> ALL 标量子查询(单行子查询) 1. 谁的工资比Abel高 Ⅰ 查询Abel的工资 SELECT salary FROM employees WHERE last_name = 'Abel'; Ⅱ 查询员工的信息，满足salary > Ⅰ的结果 SELECT * FROM employees WHERE salary > ( SELECT salary FROM employees WHERE last_name = 'Abel' ); 2. 返回job_id与141号员工相同，salary比143号员工多的员工姓名、job_id、工资 Ⅰ 查询141号员工的job_id SELECT job_id FROM employees WHERE employee_id=141; Ⅱ 查询143号员工的salary SELECT salary FROM employees WHERE employee_id=143; Ⅲ 查询员工的姓名、job_id、工资，要求job_id = Ⅰ并且salary > Ⅱ 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 ); 3. 返回公司工资最少的员工的last_name,job_id和salary Ⅰ 查询工资的最低工资 SELECT MIN(salary) FROM employees; Ⅱ 查询last_name,job_id和salary，要求salary = Ⅰ SELECT last_name, job_id, salary FROM employees WHERE salary = ( SELECT MIN(salary) FROM employees ); 4. 查询最低工资大于50号部门最低工资的部门id和其最低工资 Ⅰ 查询50号部门的最低工资 SELECT MIN(salary) FROM employees WHERE department_id=50; Ⅱ 查询每个部门的最低工资 SELECT MIN(salary),department_id FROM employees GROUP BY department_id; Ⅲ 在Ⅱ的基础上筛选，满足最低工资大于Ⅰ SELECT MIN(salary), department_id FROM employees GROUP BY department_id HAVING MIN(salary) > ( SELECT MIN(salary) FROM employees WHERE department_id = 50 ); 列子查询(多行子查询) 1. 返回location_id是1400或1700的部门中的所有员工姓名 Ⅰ 查询location_id是1400或1700的部门编号 SELECT DISTINCT department_id FROM departments WHERE location_id IN (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. 返回其他工种中比job_id为'IT_PROG'工种任一工资低的员工的员工号、姓名、job_id、salary 方法一: Ⅰ 查询job_id为'IT_PROG'工种任一工资 SELECT DISTINCT salary FROM employees WHERE job_id='IT_PROG'; Ⅱ 查询员工号、姓名、job_id、salary，要求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'; 方法二: Ⅰ 查询job_id为'IT_PROG'工种最高工资 SELECT MAX(salary) FROM employees WHERE job_id='IT_PROG'; Ⅱ 查询员工号、姓名、job_id、salary，要求salary < (Ⅰ)的任意一个 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'; 行子查询(结果集一行多列或多行多列) 要求多个筛选条件都使用一样的操作符。 1. 查询员工编号最小并且工资最高的员工信息 方法一(行子查询): SELECT * FROM employees WHERE (employee_id, salary) = ( SELECT MIN(employee_id), MAX(salary) FROM employees ); 方法二(传统方法): Ⅰ 查询最小的员工编号 SELECT MIN(employee_id) FROM employees; Ⅱ 查询最高工资 SELECT MAX(salary) FROM employees; Ⅲ 查询员工信息 SELECT * FROM employees WHERE employee_id = ( SELECT MIN(employee_id) FROM employees ) AND salary = ( SELECT MAX(salary) FROM employees ); 8.2.4 select后面
1. 查询每个部门的员工个数 SELECT d.*, ( SELECT COUNT(*) FROM employees e WHERE e.department_id = d.department_id ) AS 个数 FROM departments d; 2. 查询员工号=102的部门名 SELECT ( SELECT d.department_name FROM departments d JOIN employees e ON d.department_id = e.department_id WHERE e.employee_id = 102 ) 部门名; 8.2.5 from后面
1. 查询每个部门的平均工资的工资等级 Ⅰ 查询每个部门的平均工资 SELECT AVG(salary),department_id FROM employees GROUP BY department_id; Ⅱ 连接Ⅰ的结果集和job_grades表，筛选条件平均工资 在lowest_sal和highest_sal之间 SELECT av_dep.*, j.grade_level FROM job_grades j JOIN ( SELECT AVG(salary) av, department_id FROM employees GROUP BY department_id ) av_dep ON av_dep.av BETWEEN j.lowest_sal AND j.highest_sal; 8.2.6 exists后面（相关子查询）【用的较少】
语法: exists(完整的查询语句) 结果: 1或0 简单使用: SELECT EXISTS(SELECT employee_id FROM employees); 1. 查询有员工的部门名 方法一(使用exists): SELECT d.department_name FROM departments d WHERE EXISTS ( SELECT * FROM employees e WHERE e.department_id = d.department_id ); 方法二(使用in): SELECT department_name FROM departments d WHERE d.department_id IN ( SELECT e.department_id FROM employees e ); 2. 查询没有女朋友的男神信息 方法一(使用in): SELECT bo.* FROM boys bo WHERE bo.id NOT IN ( SELECT boyfriend_id FROM beauty ); 方法二(使用exists): SELECT bo.* FROM boys bo WHERE NOT EXISTS ( SELECT boyfriend_id FROM beauty b WHERE bo.id = b.boyfriend_id ); 8.2.6 练习题
1. 查询和Zlotkey相同部门的员工姓名和工资 Ⅰ 查询Zlotkey的部门编号 SELECT department_id FROM employees WHERE last_name='Zlotkey'; Ⅱ 查询部门编号 = Ⅰ的员工姓名和工资 SELECT last_name, salary FROM employees WHERE department_id = ( SELECT department_id FROM employees WHERE last_name = 'Zlotkey' ); 2. 查询工资比公司平均工资高的员工的员工号，姓名和工资 Ⅰ 查询平均工资 SELECT AVG(salary) FROM employees; Ⅱ 查询工资 > Ⅰ的员工号，姓名和工资 SELECT employee_id, last_name, salary FROM employees WHERE salary > ( SELECT AVG(salary) FROM employees ); 3. 查询各部门中工资比本部门平均工资高的员工的员工号，姓名和工资 Ⅰ 查询各部门的平均工资 SELECT department_id,AVG(salary) FROM employees GROUP BY department_id; Ⅱ 连接Ⅰ结果集和employees表，进行筛选 SELECT e.department_id, employee_id, last_name, salary FROM employees e JOIN ( SELECT department_id, AVG(salary) ag FROM employees GROUP BY department_id ) ag_dep ON e.department_id = ag_dep.department_id WHERE salary > ag_dep.ag; 4. 查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名 Ⅰ 查询姓名中包含字母u的员工的部门编号 SELECT DISTINCT department_id FROM employees WHERE last_name LIKE '%u%'; Ⅱ 查询部门编号 = Ⅰ中的任意一个的员工号和姓名 SELECT employee_id, last_name FROM employees WHERE department_id IN ( SELECT DISTINCT department_id FROM employees WHERE last_name LIKE '%u%' ); 5. 查询在部门的location_id为1700的部门工作的员工的员工号 Ⅰ 查询location_id为1700的部门编号 SELECT DISTINCT department_id FROM departments WHERE location_id=1700; Ⅱ 查询部门号 = Ⅰ中的任意一个的员工号 SELECT employee_id FROM employees WHERE department_id = ANY ( SELECT DISTINCT department_id FROM departments WHERE location_id = 1700 ); 6. 查询管理者是K_ing的员工姓名和工资 Ⅰ 查询姓名为K_ing的员工编号 SELECT employee_id FROM employees WHERE last_name='K_ing'; Ⅱ 查询哪个员工的manager_id = Ⅰ SELECT last_name, salary FROM employees WHERE manager_id IN ( SELECT employee_id FROM employees WHERE last_name = 'K_ing' ); 7. 查询工资最高的员工的姓名，要求first_name和last_name显示为一列，列名为姓.名 Ⅰ 查询最高工资 SELECT MAX(salary) FROM employees; Ⅱ 查询工资 = Ⅰ的姓.名 SELECT CONCAT(first_name, '.', last_name) FROM employees WHERE salary = ( SELECT MAX(salary) FROM employees ); 8.3 子查询经典案例
1. 查询工资最低的员工信息:last_name,salary SELECT last_name, salary FROM employees WHERE salary = ( SELECT MIN(salary) FROM employees ); 2. 查询平均工资最低的部门信息 方法一: Ⅰ 查询每个部门的平均工资 SELECT department_id,AVG(salary) FROM employees GROUP BY department_id; Ⅱ 查询Ⅰ结果上的最低平均工资 SELECT MIN(ag_dep.ag) FROM (SELECT department_id,AVG(salary) ag FROM employees GROUP BY department_id) ag_dep; Ⅲ 查询哪个部门的平均工资=Ⅱ SELECT department_id FROM employees GROUP BY department_id HAVING AVG(salary) = ( SELECT MIN(ag_dep.ag) FROM ( SELECT department_id, AVG(salary) ag FROM employees GROUP BY department_id ) ag_dep ); Ⅳ 查询部门信息 SELECT d.* FROM departments d WHERE d.department_id = ( SELECT department_id FROM employees GROUP BY department_id HAVING AVG(salary) = ( SELECT MIN(ag_dep.ag) FROM ( SELECT department_id, AVG(salary) ag FROM employees GROUP BY department_id ) ag_dep ) ); 方法二: Ⅰ 查询每个部门的平均工资 SELECT department_id,AVG(salary) FROM employees GROUP BY department_id; Ⅱ 查询最低平均工资的部门编号 SELECT department_id FROM employees GROUP BY department_id ORDER BY AVG(salary) LIMIT 1; Ⅲ 查询部门信息 SELECT * FROM departments WHERE department_id = ( SELECT department_id FROM employees GROUP BY department_id ORDER BY AVG(salary) LIMIT 1 ); 3. 查询平均工资最低的部门信息和该部门的平均工资 Ⅰ 查询每个部门的平均工资 SELECT department_id,AVG(salary) FROM employees GROUP BY department_id; Ⅱ 查询最低平均工资的部门编号 SELECT AVG(salary),department_id FROM employees GROUP BY department_id ORDER BY AVG(salary) LIMIT 1; Ⅲ 查询部门信息 SELECT d.*,ag FROM departments d JOIN ( SELECT AVG(salary) ag,department_id FROM employees GROUP BY department_id ORDER BY ag LIMIT 1 ) ag_dep ON d.department_id=ag_dep.department_id; 4. 查询平均工资最高的job信息 Ⅰ 查询最高的job的平均工资 SELECT AVG(salary),job_id FROM employees GROUP BY job_id ORDER BY AVG(salary) DESC LIMIT 1 Ⅱ 查询job信息 SELECT * FROM jobs WHERE job_id=( SELECT job_id FROM employees GROUP BY job_id ORDER BY AVG(salary) DESC LIMIT 1 ); 5. 查询平均工资高于公司平均工资的部门有哪些 Ⅰ 查询公司的平均工资 SELECT AVG(salary) FROM employees; Ⅱ 查询每个部门的平均工资 SELECT AVG(salary) FROM employees GROUP BY department_id Ⅲ 筛选Ⅱ的结果集，满足平均工资>Ⅰ SELECT AVG(salary),department_id FROM employees GROUP BY department_id HAVING AVG(salary)>( SELECT AVG(salary) FROM employees ); 6. 查询出公司中所有manager的详细信息 Ⅰ 查询所有manager的员工编号 SELECT DISTINCT manager_id FROM employees Ⅱ 查询详细信息，满足employee_id=Ⅰ SELECT * FROM employees WHERE employee_id IN ( SELECT DISTINCT manager_id FROM employees ); 7. 各个部门中，最高工资中最低的那个部门的最低工资是多少 Ⅰ 查询各个部门的最高工资中最低的部门编号 SELECT department_id FROM employees GROUP BY department_id ORDER BY MAX(salary) LIMIT 1 Ⅱ 查询Ⅰ结果的那个部门的最低工资 SELECT MIN(salary) FROM employees WHERE department_id=( SELECT department_id FROM employees GROUP BY department_id ORDER BY MAX(salary) LIMIT 1 ); 8. 查询平均工资最高的部门的manager的详细信息:last_name,department_id,email,salary Ⅰ 查询平均工资最高的部门编号 SELECT department_id FROM employees GROUP BY department_id ORDER BY AVG(salary) DESC LIMIT 1 Ⅱ 将employees和departments连接查询，筛选条件是Ⅰ SELECT last_name, d.department_id, email, salary FROM employees e INNER JOIN departments d ON d.manager_id = e.employee_id WHERE d.department_id = ( SELECT department_id FROM employees GROUP BY department_id ORDER BY AVG(salary) DESC LIMIT 1 ); 9 分页查询
应用场景 当要显示的数据，一页显示不全，需要分页提交sql请求 语法 select 查询列表 from 表1 别名 [ [连接类型] join 表2 别名 ] [on 连接条件] [where 筛选条件] [group by 分组] [having 分组后的筛选] [order by 排序列表] limit [offset,] size; 其中，offset表示要显示条目的起始索引(起始索引从0开始)，size表示要显示的条目个数 执行顺序:from-->join-->on-->where-->group by-->having-->select-->order by-->limit 特点 1. limit语句放在查询语句的最后 2. 公式: 要显示的页数page，每页的条目数size 假如size=10 page 1 0 2 10 3 20 select 查询列表 from 表 limit (page-1)*size,size; 1. 查询前五条员工信息 SELECT * FROM employees LIMIT 0,5; SELECT * FROM employees LIMIT 5; 2. 查询第11条至第25条 SELECT * FROM employees LIMIT 10,15; 3. 查询有奖金的员工信息，并且工资较高的前10名显示出来 SELECT * FROM employees WHERE commission_pct IS NOT NULL ORDER BY salary DESC LIMIT 10; 10 练习题
已知表stuinfo，id学号，name姓名，email邮箱，gradeId年级编号，sex性别，age年龄 已知表grade，id年级编号，gradeName年级名称 一、查询所有学员的邮箱的用户名(注:邮箱中@前面的字符) select substr(email,1,instr(email,'@')-1) 用户名 from stuinfo; 二、查询男生和女生的个数 select sex,count(*) 个数 from stuinfo group by sex; 三、查询年龄>18岁的所有学生的姓名和年级名称 select name,gradeName from stuinfo s join grade g on s.gradeId=g.id where age>18; 四、查询哪个年级的学生最小年龄>20岁 1. 每个年级的最小年龄 select min(age),gradeId from stuinfo group by gradeId; 2. 在1的结果集上筛选 select min(age),gradeId from stuinfo group by gradeId having min(age)>20; 五、试说出查询语句中涉及到的所有关键字，以及执行先后顺序 select 查询列表 from 表1 别名 连接类型 join 表2 别名 on 连接条件 where 筛选条件 group by 分组列表 having 分组后的筛选 order by 排序列表 limit 偏移,条目数; 执行顺序:from-->join-->on-->where-->group by-->having-->select-->order by-->limit 11 union联合查询
union(联合，合并): 将多条查询语句的结果合并成一个结果 语法: 查询语句1 union 查询语句2 union ...... 使用场景: 要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息(查询列表)一致。 特点: 1. 要求多条查询语句的查询列表是一致的 2. 要求多条查询语句的查询的每一列的类型和顺序最好一致 3. union关键字默认去重，union all可以包含重复项 练习: 查询部门编号>90或邮箱中包含a的员工信息 SELECT * FROM employees WHERE department_id>90 UNION SELECT * FROM employees WHERE email LIKE '%a%'; 五、DML语言学习
主要涉及：insert 、update、delete1 插入语句
解决方案: [Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause 执行下面sql语句: select version(),@@sql_mode; SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','')); 方式一: insert into 表名(列名1,...) values(值1,...); 案例: 1. 插入的值的类型要与列的类型一致或兼容 INSERT INTO beauty(id,name,sex,borndate,phone,photo,boyfriend_id) VALUES(13,'唐艺昕','女','1990-04-23','18712344321',NULL,2); 2. 可以为null的列如何插入值 方式一: INSERT INTO beauty(id,name,sex,borndate,phone,photo,boyfriend_id) VALUES(13,'唐艺昕','女','1990-04-23','18712344321',NULL,2); 方式二: INSERT INTO beauty(id,name,sex,borndate,phone,boyfriend_id) VALUES(14,'金星','男','1990-04-23','18712344321',6); 3. 列的顺序可以调换 INSERT INTO beauty(name,id,sex,borndate,phone,boyfriend_id) VALUES('金星',14,'男','1990-04-23','18712344321',6); 4. 列数和值的个数必须一致 5. 可以省略列名，默认所有列，而且列的顺序和表中列的顺序一致 INSERT INTO beauty VALUES(13,'唐艺昕','女','1990-04-23','18712344321',NULL,2); 方式二: insert into 表名 set 列名1=值1,列名2=值2...... 案例: INSERT INTO beauty SET id=20,name='lucy',phone='17609222432'; 两种方式大PK 1. 方式一支持插入多行 INSERT INTO beauty(id,name,sex,borndate,phone,photo,boyfriend_id) VALUES(23,'唐艺昕','女','1990-04-23','18712344321',NULL,2), (24,'唐艺昕','女','1990-04-23','18712344321',NULL,2), (25,'唐艺昕','女','1990-04-23','18712344321',NULL,2); 2. 方式一支持子查询，方式二不支持 INSERT INTO beauty(id,name,phone) SELECT 30,'lucy','17674125896'; 2 修改语句
2.1 修改单表的记录
语法 update 表名 set 列1=新值1,列2=新值2,... where 筛选条件; 执行顺序 update-->where-->set 1. 修改beauty表中姓唐的女神的电话为13899888899 UPDATE beauty SET phone='13899888899' WHERE `name` LIKE '唐%'; 2. 修改boys表中id为2的名称为张飞，魅力值为10 UPDATE boys SET boyName='张飞',userCP=10 WHERE id=2; 2.2 修改多表的记录
语法 sql92语法 update 表1 别名,表2 别名 set 列1=值1,... where 连接条件 and 筛选条件; sql99语法 update 表1 别名 inner|left|right join 表2 别名 on 连接条件 set 列1=值1 where 筛选条件; 1. 修改张无忌的女朋友的手机号为110 UPDATE boys bo JOIN beauty b ON bo.id=b.boyfriend_id SET b.phone=110 WHERE bo.boyName='张无忌'; 2. 修改没有男朋友的女神的男朋友编号都为2号 UPDATE boys bo RIGHT JOIN beauty b ON bo.id = b.boyfriend_id SET b.boyfriend_id=2 WHERE b.boyfriend_id IS NULL; 3 删除语句
方式一:delete 语法: 1. 单表的删除 delete from 表名 [where 筛选条件]; 2. 多表的删除 sql92语法 delete 表1的别名[,表2的别名] from 表1 别名,表2 别名 where 连接条件 and 筛选条件; sql99语法 delete 表1的别名[,表2的别名] from 表1 别名 inner|left|right join 表2 别名 on 连接条件 where 筛选条件; 方式二:truncate 语法: truncate table 表名; 3.1 方式一：delete
3.1.1 单表的删除
1. 删除手机号以9结尾的女神信息 DELETE FROM beauty WHERE phone LIKE '%9'; 3.1.2 多表的删除
1. 删除张无忌的女朋友的信息 DELETE b FROM beauty b INNER JOIN boys bo ON b.boyfriend_id=bo.id WHERE bo.boyName='张无忌'; 2. 删除黄晓明的信息以及他女朋友的信息 DELETE b,bo FROM beauty b INNER JOIN boys bo ON b.boyfriend_id=bo.id WHERE bo.boyName='黄晓明'; 3.2 方式二：truncate
1. 删除beauty表中所有数据 TRUNCATE TABLE beauty; 3.3 delete PK truncate【面试题】
1. delete可以加where条件，truncate不能加 2. truncate删除效率高一点 3. 假如要删除的表中有自增长列。如果用delete删除后，再插入数据，自增长列的值从断点开始，而truncate删除后，再插入数据，自增长列的值从1开始 4. truncate删除没有返回值(显示共0行受到影响)，delete删除有返回值(显示共n行受到影响)。 5. truncate删除不能回滚，delete删除可以回滚
本网页所有视频内容由 imoviebox边看边下-网页视频下载, iurlBox网页地址收藏管理器 下载并得到。
ImovieBox网页视频下载器 下载地址: ImovieBox网页视频下载器-最新版本下载
本文章由: imapbox邮箱云存储,邮箱网盘,ImageBox 图片批量下载器,网页图片批量下载专家,网页图片批量下载器,获取到文章图片,imoviebox网页视频批量下载器,下载视频内容,为您提供.
阅读和此文章类似的: 全球云计算
官方软件产品操作指南 (170)
