文章目录
基本语法
select 可以 查询单表 多表(多表记得,逗号)
案例:last_name,salary,email FROM employees;
select可以做运算,常量
案例:SELECT 100*98;
可以查询单个字符 ‘着重号’
案例:SELECT 'a';
select 可以 查询函数(方法)
案例:SELECT VERSION();查询SQL版本号
起别名
AS起别名也可以 不写AS 直接 空格
如果要查询的有重名的情况,使用别名可以区分开来
案例:SELECT salary AS 'out put' FROM employees;
去重:
distinct(关键字:去重)
去重案例::SELECT DISTINCT department_id AS 部门编号 FROM employees;
mysql的"+"号
俩个操作都为数值时,则做加法运算
案例:SELECT 90+100 AS 运算;
其中一方是字符类型,试图将字符数值转换成数值型如果转换成功 则进行加法运算,如果失败则字符类型变成0,在进行运算
案例:SELECT 'jonin'+1;结果为1
如果一方为null值 则直接变成null
案例:SELECT NULL+10;结果为null
字符类型拼接(关键字CONCAT)
案例:SELECT CONCAT('a','b')AS 拼接 FROM employees;
案例:SELECT CONCAT(last_name,first_name)AS 姓名 FROM employees;
注意:但是不能为NULL
判断是否为空(关键字:IFNULL想判断的表,自己设置值)
案例:SELECT IFNULL (commission_pct,0)AS 奖金率,commission_pct FROM employees;
案例:
SELECT CONCAT(``first_name,last_name`,`email`,`phone_number`,`job_id`,`salary`,`IFNULL(commission_pct,0)) FROM employees;
条件查询:
语法:
| select 查询 | 想要的表 | 第三步 |
|---|---|---|
| from 来自 | 表名 | 第一步 |
| where 筛选 | 筛选条件 | 第二步 |
案例:SELECT * FROM employees WHERE salary>12000;
案例:SELECT department_id AS 部门编号,last_name AS 姓名 FROM employees WHERE department_id<>90;
筛选分类分类:
【1】按条件表达式筛选> < = != <> >= <=
【2】按条件表达式筛选:
逻辑表达式:&&(and) ||(or) !(not) and or not
逻辑表达式:
案例:SELECT last_name AS 名字 ,salary AS 工资,commission_pct AS 奖金 FROM employees WHERE salary>=10000 AND salary<=20000;
案例:SELECT * FROM employees WHERE department_id<=90 AND department_id>=110 OR salary>=15000;
模糊查询
【3】 模糊查询:
like 一般和通配符使用,%任意多个字符,包含0个字符, _任意单个字符
案例:SELECT last_name,salary FROM employees WHERE last_name LIKE'__n_l%';
ESCAPE: 转意 ‘KaTeX parse error: Expected group after '_' at position 1: _̲%' ESCAPE '’ $% KaTeX parse error: Expected group after '_' at position 76: …ast_name LIKE '_̲%’ ESCAPE ‘$’;`
between and: (取中间的值)(取多少到多少中间的值)
案例:SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 120;
案例:SELECT last_name AS 姓名,salary AS 工资 FROM employees WHERE salary BETWEEN 5000 AND 12000;
in:(操作符允许我们在 WHERE 子句中规定多个值)
案例:SELECT last_name,job_id AS 工种编号 FROM employees WHERE job_id IN('IT_PROT','AD_VP','AD_PRES');
is null:(为空)
SELECT last_name,commission_pct AS 奖金率 FROM employees WHERE commission_pct IS NULL;
ISNULL: 仅仅可以判断null值 (括号里能放两个值 如果第一个为null 则执行逗号,后面的那个)
案例:SELECT last_name AS 姓名,department_id AS 部门编号,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪 FROM employees WHERE employee_id=176;
<=>: 既可以判断NULL值,又可以判断普通的数值,可读性较低 安全等于 判断是否等于
案例:SELECT last_name,commission_pct AS 奖金率 FROM employees WHERE commission_pct <=> NULL;
排序查询
排序查询:
| select 查询 | 查询列表 | 第三步 |
|---|---|---|
| from 来自 | 表名 | 第一步 |
| where 筛选 | 筛选条件 | 第二步 |
| order by | 排序列表 | 第四步 |
order by 子句支持单个字段 多个字段 表达式 函数 别名
order by 子句一般是放在查询语句的最后面 limit字句除外
asc升序/desc降序 不写默认升序
案例:SELECT * FROM employees ORDER BY salary ASC;//从低到高
案例:SELECT * FROM employees ORDER BY salary DESC;//从高到低
案例:SELECT * FROM employees WHERE department_id>=90 ORDER BY hiredate ASC;
案例:SELECT *,salary*12*(IFNULL(commission_pct,0))AS 年薪 FROM employees ORDER BY 年薪 DESC;
案例:SELECT *,salary*12*(IFNULL(commission_pct,0))AS 年薪 FROM employees ORDER BY salary*12*(IFNULL(commission_pct,0)) DESC;
案例:SELECT last_name,salary,LENGTH(last_name)AS 姓名的长度 FROM employees ORDER BY 姓名的长度 ASC;
案例:SELECT * FROM employees ORDER BY salary ASC,employee_id ASC;
常见函数
常见函数:
概念: 类似于java的方法,将一组逻辑语句封装在方法体,对外暴露方法名
好处: 1.隐藏了实现细节 2.提高代码的重用性,调用:select 函数名(实参列表)[from 表]
特点: 叫什么(函数名),干什么(函数功能)
分类: 1.单行函数,如:concat/length/ifnull等 肯定会有返回值 2.分组函数 功能:做统计使用,又称为统计函数/聚合函数/组函数
案例:SELECT LENGTH('张三丰hahah'); 14个长度 一个英文 一个字节 一个中文 三个字节
案例:SELECT CONCAT(last_name,"_",first_name) AS 姓名 FROM employees;
upper 大写,lower 小写:
案例:SELECT CONCAT(UPPER(last_name),LOWER(last_name)) FROM employees; 可以嵌套调用
substr: 截取字符 SQL索引是从一开始的 所以是7
截取从指定索引处后面所有的字符
案例:SELECT SUBSTR('李莫愁爱上了陆展元',7); 截取从指定索引处指定字符长度的字符
案例:SELECT SUBSTR('李莫愁爱上了陆展元',1,3);
首字母大写用’_'拼接 其余名字小写
案例:SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',(LOWER(SUBSTR(last_name,2))))FROM employees;
instr: 返回子串第一次出现的索引,如果找不到返回0
案例:SELECT INSTR('殷六侠杨不悔爱上了殷六侠','殷六侠') AS 名字
trim: 去掉所有空格 长度也去掉 也能去掉指定字符
案例:SELECT TRIM(' 张翠山 ') AS 名字;
案例:SELECT TRIM('a' FROM 'aaaaaaaaaaaaa张aaa翠山aaaaaaaaaaa');
lpad 填充左
SELECT LPAD(‘殷素素’,10,’’)AS 名字;
rpad 填充右
SELECT RPAD(‘殷素素’,10,’’)AS 名字;
replace 替换
SELECT REPLACE(‘张无忌爱上了周芷若’,‘周芷若’,‘赵敏’) AS 姓名;
数学函数
数学函数
round: 四舍五入 可设置指定小数位
案例:SELECT ROUND(1.459,2);
ceil: 向上取证
案例:SELECT CEIL(2.15);
truncate: 截断
SELECT TRUNCATE(1.68,1);
mod 取余
案例:SELECT MOD(10,3); 10%3=1
日期函数:
now() 返回当前系统日期和时间
案例:SELECT NOW() AS 日期;
curdate() 返回当前系统日期,不含时间
案例:SELECT CURDATE();
curtime() 返回当前系统时间,不含日期
案例:SELECT CURTIME();
year 可以获取当前年份,也可以自己设置
案例:SELECT YEAR(NOW()) AS 年;
案例:SELECT YEAR('1998-1-1');
案例:SELECT YEAR(hiredate) 年 FROM employees;
month 月
案例:SELECT MONTH(NOW())月;
案例:SELECT MONTHNAME(NOW())月;英文版的
str_to_date 将指定的格式转换成日期
案例:SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d') AS 日期;
案例:SELECT *FROM employees WHERE hiredate =STR_TO_DATE('4-3 1992','%c-%d %Y');
sdate_format 将日期转换成字符
案例:SELECT DATE_FORMAT(NOW(),'%y年%m月%d日');
案例:SELECT last_name,commission_pct,DATE_FORMAT(hiredate,'%m月%d日%y年')AS入职日期 FROM employees WHERE commission_pct IS NOT NULL;
其他函数 version 版本号,database,查看当前的库,user 当前用户
流程控制函数
流程控制函数
1.if函数 :if else的效果
案例:SELECT IF(10<5,'大','小');
案例:SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没奖金,嘿嘿','有奖金,呵呵')FROM employees;
2.case函数的使用
case要判断的字段或表达式
when常量1 then 要显示的值1或语句1;
else 要显示的值n 或语句n
end
案例:SELECT last_name AS 姓名,salary AS 原始工资,department_id AS 部门编号 , CASE department_id WHEN 30 THEN salary*1.1 WHEN 40 THEN salary*1.2 WHEN 50 THEN salary*1.3 ELSE salary END 新工资 FROM employees;
case函数的第二种使用
case 什么都不填(为空)
when(判断) 条件1 then要显示的值1或语句1(起的名字加’’)
when(判断) 条件2 then要显示的值2或语句2(起的名字加’’)
…
else 要显示的值n 或语句(判断完剩下的条件都归这里)
end(新名字)
from 表名
案例:SELECT last_name AS 姓名,salary AS 工资, CASEWHEN salary>20000 THEN 'A级别'WHEN salary>15000 THEN 'B级别'WHEN salary>10000 THEN 'C级别'ELSE 'D级别' END 级别划分FROM employees;
特点:
1.sum avg一般用于处理数值型
,max min count一般处理任何类型
2.以上分组函数 都忽略null值
3.可以和distinct搭配实现去重的运算
案例:SELECT SUM(DISTINCT salary),SUM(salary) FROM employees; 俩个值不一样 因为一个去重 一个没去重
案例:SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees; 俩个值不一样 因为一个去重 一个没去重
4.count: 函数的详细
案例:SELECT COUNT(*) FROM employees;
案例:SELECT COUNT(1) FROM employees;
案例:SELECT COUNT('啊') FROM employees;
效率:
myisam 存储引擎下 ,count(*)效率最高
innodb 存储引擎下,count(*)和count(1)的效率差不多,但是要比加字段的高一些
分组查询
分组查询:
select 分组函数,列(要求出现在group by的后面)
from表
where 筛选条件
group by分组列表
order by 子句
注意： 查询列表比较特殊 要求是分组函数和group by后出现的字段
案例:SELECT MAX(salary),job_id AS 工种 FROM employees GROUP BY job_id;
案例:SELECT COUNT(*),location_id FROM departments GROUP BY location_id;
案例:SELECT AVG(salary),department_id,email FROM employees WHERE email LIKE '%a%' GROUP BY department_id ;
案例:SELECT MAX(salary),manager_id AS 领导 FROM employees WHERE NOT ISNULL(commission_pct) GROUP BY manager_id;
having 分组后的筛选:
案例:SELECT COUNT(*),department_id FROM employees GROUP BY department_id HAVING COUNT(*)>2;
案例:SELECT MAX(salary),job_id,commission_pct FROM employees WHERE commission_pct IS NOT NULL GROUP BY job_id HAVING MAX(salary)>12000;
特点:
| 分组前筛选 | 原始表 | group by 子句的前面 | where |
|---|---|---|---|
| 分组后筛选 | 分组后的结果集 | group by 子句的后面 | having |
分组函数做条件肯定是放在having子句中
能用分组前筛选的，就优先考虑使用分组前筛选
group by 子句 支持单个字段分组,多个字段分组(多个字段之间用逗号隔开没有顺序的要求),也可以表达式或函数 用的较少
也可以添加排序(排序放在整个分组查询的最后)按照表达式或函数分组
案例:SELECT COUNT(*),LENGTH(last_name) FROM employees GROUP BY LENGTH(last_name) HAVING COUNT(*)>5;
group by 支持别名
having 支持别名
按多个字段分组
案例:SELECT AVG(salary),job_id,department_id FROM employees GROUP BY job_id,department_id;
**按多个字段分组 然后排序 平均工资 **
案例:SELECT AVG(salary),job_id,department_id FROM employees GROUP BY job_id,department_id ORDER BY AVG(salary) DESC;
案例:SELECT AVG(salary)AS 平均工资,job_id,department_id FROM employees WHERE department_id IS NOT NULLGROUP BY job_id,department_id HAVING AVG(salary)>10000ORDER BY AVG(salary) DESC;
连接查询
连接查询:
含义:又称多表查询,当查询的字段来自于多个表时,就会用到连接查询
笛卡尔乘积现象: 表1 有M行 表2 有N行 结果=m*n行
案例:SELECT NAME,boyName FROM boys,beauty; 笛卡尔现象
发生原因: 没有有效的连接条件
如何避免: 添加有效的连接条件
用表明 加一个点 . 就能避免 笛卡尔现象
案例:SELECT NAME,boyName FROM boys,beauty
案例:WHERE beauty.boyfriend_id= boys.id;
SQL92和SQL95的标准
按年代分类:sq192标准 仅仅支持内连接
sq199标准(推荐):支持内连接/外连接(左外和右外)/交叉连接不支持全外连接
按功能分类: 内连接:( 等值连接/非等值连接/自连接)
外连接:(左外连接/右外连接/全外连接)
交叉连接
等值连接
1.多表等值连接的结果为多表的交集部分
2.n表连接,至少需要n-1个连接条件
3.多表的顺数没有需求
4.一般需要为表起别名
5.可以搭配前面介绍的所有子句使用,比如排序 分组 筛选
1.等值连接 (可以为表 起别名)
注意: 如果为了表起了别名,则查询的字段就不能使用原来的表名去限定
拿着一张表 去匹配另一张表 顺序 可以换
案例:SELECT NAME,boyName FROM boys,beauty WHERE beauty.boyfriend_id = boys.id;
案例:SELECT last_name,department_name FROM employees,departments WHERE departments.department_id=employees.department_id;
案例:SELECT last_name,employees.job_id,job_title FROM employees,jobs WHERE employees.job_id = jobs.job_id;
案例:SELECT last_name,e.job_id,job_title FROM employees AS e,jobs AS j WHERE e.job_id=j.job_id;
等值连接 里面可以在加 判断 需要用and
案例:SELECT last_name,department_name,commission_pct FROM employees,departments WHERE employees.department_id = departments.department_id AND commission_pct IS NOT NULL;
案例: 查询城市名中 第二个字符为o的部门们和城市名 SELECT city ,department_name FROM locations l,departments d WHERE l.location_id=d.location_idAND city LIKE '_o%';
案例: SELECT COUNT(*)个数,city FROM departments d,locations l GROUP BY city;
案例: 查询每个城市的部门个数 SELECT COUNT(*)个数,city FROM departments d,locations l WHERE d.location_id=l.location_id GROUP BY city;
案例: 查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资 SELECT MIN(salary),department_name,e.manager_id,commission_pct FROM employees AS e,departments AS d WHERE e.department_id=d.department_id AND commission_pct IS NOT NULL GROUP BY department_name,d.manager_id;
案例: 查询每个工种的工种名和员工的个数,并且按员工个数降序 SELECT job_title,COUNT(*) FROM employees e, jobs j WHERE e.job_id = j.job_id GROUP BY job_title ORDER BY COUNT(*) DESC;
三表连接
三表连接
案例:查询员工名,部门名和所在城市 SELECT last_name,department_name,city FROM employees AS e,departments AS d,locations AS l WHERE e.department_id=d.department_idAND d.location_id=l.location_idAND city LIKE 's%' ORDER BY department_name DESC;
非等值连接
非等值连接
案例:查询员工的工资和工资级别 SELECT salary,grade_level FROM employees AS e,job_grades AS j WHERE salary BETWEEN j.lowest_salAND j.highest_salGROUP BY salary ASC;
自连接
自连接
把一张表 当成俩张表去使用
案例:SELECT e.employee_id,e.last_name,m.employee_id,m.last_name FROM employees e ,employees m WHERE e.manager_id=m.employee_id;
SQ199语法
语法:
select 查询列表
from 表1 别名 [连接类型]
join 表2 别名
on 连接条件
[where 筛选条件]
[group by 分组]
[having 筛选条件]
[order by排序列表]
分类
内连接 :inner
select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件
等值案例:
案例: 查询员工名,部门名 SELECT last_name,department_name FROM employees e INNER JOIN departments d ON e.department_id=d.department_id;
案例: 查询部门个数>3的城市名和部门个数(添加分组+筛选) SELECT COUNT(*)AS 部门个数,city AS 城市 FROM locations l INNER JOIN departments d ON l.location_id=d.location_id GROUP BY city HAVING COUNT(*)>3
案例: SELECT last_name,department_name,job_title FROM employees AS e INNER JOIN departments d INNER JOIN jobs j ON e.department_id=d.department_id AND j.job_id=e.job_id ORDER BY department_name DESC;
非等值案例:
案例: SELECT salary,grade_level FROM employees AS e INNER JOIN job_grades j ON e.salary BETWEEN j.grade_level AND j.highest_sal;
案例: SELECT salary,grade_level,COUNT(*) FROM employees AS e INNER JOIN job_grades j ON e.salary BETWEEN j.grade_level AND j.highest_sal GROUP BY grade_level HAVING COUNT(*)>2 ORDER BY grade_level DESC;
自连接:
案例: SELECT e.last_name,d.last_name FROM employees e INNER JOIN employees d ON e.employee_id = d.manager_id WHERE e.last_name LIKE '%k%';
外连接(左右外连接)
特点:
1.添加排序/分组/筛选
2.inner可以省略
3.筛选条件放在where后面,连接条件放在on后面,提高分离性,便于阅读
4.inner join连接和sp192语法中的等值连接效果是一样的,都是查询多表的交际
外连接 :左外 left[outer],右外 right[outer],全外 full[outer]
交叉连接cross(笛卡尔乘积)
笛卡尔乘积
案例: SELECT last_name,job_id,salary FROM employees WHERE salary<ANY(SELECT DISTINCT salary FROM employees WHERE job_id='IT_PROG');
行子查询
行子查询(结果集是一行多列或者多行多列)
案例:SELECT * FROM employees WHERE (employee_id,salary)=(SELECT MIN(employee_id),MAX(salary)FROM employees);
量子查询
放在select 后面 里面只支持标量子查询
案例:SELECT d.*,(SELECT COUNT(*)FROM employees e WHERE e.department_id = d.department_id) FROM departments d
案例:SELECT(SELECT department_name FROM departments d INNER JOIN employees e ON e.department_id=d.department_id WHERE e.employee_id=102)部门名;
放在from后面
将子查询结果充当一张表,要求必须起别名
案例:FROM (SELECT AVG(salary) AS ag, department_id FROM employees GROUP BY department_id) AS ag_dep INNER JOIN job_grades g ON ag_dep.ag BETWEEN lowest_sal AND highest_sal ORDER BY ag ASC
相关子查询
EXISTS 后面(相关子查询) 结果为1就是true 结果2就是false
案例: SELECT EXISTS( SELECT employee_id FROM employees); SELECT department_name FROM departments d WHERE EXISTS(SELECT * FROM employees e WHERE d.department_id= e.department_id)
分页查询
分页查询 (重点)
应用场景:当我们要显示的数据一页显示不全,我们要分页提交SQL请求
语法:
SELECT 查询
from 表
join tyep join表2
on链接条件
where筛选条件
group by 分组字段
having 分组后的筛选
order by 排序的字段
limit offset,size
ofrrset要显示的条目索引(起始索引从0开始)
size 要显示的条目个数
特点:
limit 语句放在查询语句的最后
公式
要显示的页数 page,每页的条目数 size
select 查询李彪
from 表
limit,size
size=10
page
案例:SELECT * FROM employees LIMIT 10,15;
案例:SELECT * FROM employees WHERE commission_pct IS NOT NULL ORDER BY salary DESC LIMIT 0,10;
联合查询
联合查询
union 联合 合并 :将多条查询语句的结果合并成一个结果
案例:SELECT*FROM employees WHERE email LIKE'%a%' UNION SELECT*FROM employees WHERE department_id>90
语法:
查询语句1
union
查询语句2
union
…
应用场景
要查询的结果来自于多个表,且多个表没有直接的连接关系,但查询的信息一致时
特点:
1.要求多条查询语句的查询列数是一致的
2.要求多条查询语句的查询的每一列的类型和顺序最好一致
3.untion关键字默认去重,如果使用 union all 可以包含重复项
常见数据类型
常见的数据类型
数值型:
整型
Tinyint 1个字节
Smallint 2个字节
Mediumint 3个字节
Int integer 4个字节
BigInt 8个字节
特点:
1.如果不设置无符号还是有符号,默认是有符号,如果想设置无符号,需要添加UNSIGNED
2.如果插入的数值超出了整型的范围 他会报出out for range异常,并且插入临界值
3.如果不设置长度,会有默认的长度
长度代表显示的最大快读,如果不够就会用0在左边填充,但是必须要搭配 ZEROFILL 填充
案例:CREATE TABLE tab_int(t1 INT(7)); 可以用ZEROFILL 0填充
案例: CREATE TABLE tab_int(t1 INT); 默认长度是11 有符号的
案例: CREATE TABLE tab_int(t2 INT UNSIGNED); 默认长度是10 无符号的
小数:
定点数
float 4个字节
double 8点字节
浮点型
dec(M,D)
decimal(M,D)
特点:
M:整数部位+小数部位
D:小数部位 如果超过范围 则插入临界值
如果是decimal,则M默认为10,D默认为0
如果是float和double 则会根据插入的数值的精度来决定精度
定典型的精度较高,如果求插入数值的精度要求高 如货币运算等则考虑使用定点型
M和D M代表整数部位+小数部位的个数,D代表小数部位 取舍小数后的几位
字符型:
较短的文本:char varchar
char 固定长度的字符,比较消耗空间,效率高,可以省略长度
Varchar可变的字符,比较节省空间,效率低,不可以省略长度
其他:binary和varbinary用于保存较短的二进制
enum 用于保存枚举
set 用于保存集合
较长的文本:text blob(较长的二进制数据)
日期类型
日期型:
date 4个字节 年月日
datetime 8个字节 年月日时间
timestamp
time
year 年
原则:
所选择的类型越简单越好,能保存数值越小越好
970
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
